metabuilder/old/DATABASE.md

Database Architecture

Overview

MetaBuilder uses a comprehensive database abstraction layer built on top of the Spark KV persistence API. All data is stored persistently across sessions with proper security measures including SHA-512 password hashing.

Database Entities

The database manages the following entity types:

Core Entities

Users

• Key: db_users
• Type: User[]
• Description: All registered users in the system
• Fields:
  • id: Unique identifier
  • username: Login username
  • email: User email address
  • role: Access level (public, user, admin, god)
  • bio: Optional user biography
  • profilePicture: Optional avatar URL
  • createdAt: Registration timestamp

Credentials

• Key: db_credentials
• Type: Record<string, string>
• Description: SHA-512 hashed passwords mapped to usernames
• Security: Passwords are NEVER stored in plain text
• Format: { username: sha512Hash }

Application Data

Workflows

• Key: db_workflows
• Type: Workflow[]
• Description: Visual workflow definitions with nodes and edges
• Fields:
  • id: Workflow identifier
  • name: Workflow name
  • description: Optional description
  • nodes: Array of workflow nodes (triggers, actions, conditions, Lua scripts)
  • edges: Array of connections between nodes
  • enabled: Whether workflow is active

Lua Scripts

• Key: db_lua_scripts
• Type: LuaScript[]
• Description: Reusable Lua lambda functions
• Fields:
  • id: Script identifier
  • name: Script name
  • description: Optional description
  • code: Lua source code
  • parameters: Array of parameter definitions
  • returnType: Expected return type

Pages

• Key: db_pages
• Type: PageConfig[]
• Description: Page configurations for each application level
• Fields:
  • id: Page identifier
  • path: URL path
  • title: Page title
  • level: Application level (1-4)
  • componentTree: Hierarchical component structure
  • requiresAuth: Whether authentication is required
  • requiredRole: Minimum role to access

Data Schemas

• Key: db_schemas
• Type: ModelSchema[]
• Description: Data model definitions for Level 3 admin panel
• Fields:
  • name: Model name
  • label: Display label
  • labelPlural: Plural form
  • icon: Icon identifier
  • fields: Array of field definitions with types, validation, relations
  • listDisplay: Fields to show in list view
  • searchFields: Searchable fields

User-Generated Content

Comments

• Key: db_comments
• Type: Comment[]
• Description: User comments and discussions
• Fields:
  • id: Comment identifier
  • userId: Author user ID
  • content: Comment text
  • createdAt: Creation timestamp
  • updatedAt: Last edit timestamp
  • parentId: Optional parent comment for threading

UI Configuration

Component Hierarchy

• Key: db_component_hierarchy
• Type: Record<string, ComponentNode>
• Description: Tree structure of UI components
• Fields:
  • id: Node identifier
  • type: Component type
  • parentId: Parent node ID
  • childIds: Array of child node IDs
  • order: Display order
  • pageId: Associated page

Component Configs

• Key: db_component_configs
• Type: Record<string, ComponentConfig>
• Description: Component properties and styling
• Fields:
  • id: Config identifier
  • componentId: Associated component
  • props: Component properties
  • styles: CSS styling
  • events: Event handlers
  • conditionalRendering: Rendering conditions

Application Configuration

• Key: db_app_config
• Type: AppConfiguration
• Description: Global application settings
• Fields:
  • id: Config identifier
  • name: Application name
  • schemas: Array of data schemas
  • workflows: Array of workflows
  • luaScripts: Array of Lua scripts
  • pages: Array of page configs
  • theme: Theme configuration (colors, fonts)

Database API

The Database class provides a comprehensive API for all data operations:

User Management

Database.getUsers(): Promise<User[]>
Database.setUsers(users: User[]): Promise<void>
Database.addUser(user: User): Promise<void>
Database.updateUser(userId: string, updates: Partial<User>): Promise<void>
Database.deleteUser(userId: string): Promise<void>

Credential Management

Database.getCredentials(): Promise<Record<string, string>>
Database.setCredential(username: string, passwordHash: string): Promise<void>
Database.verifyCredentials(username: string, password: string): Promise<boolean>

Workflow Management

Database.getWorkflows(): Promise<Workflow[]>
Database.setWorkflows(workflows: Workflow[]): Promise<void>
Database.addWorkflow(workflow: Workflow): Promise<void>
Database.updateWorkflow(workflowId: string, updates: Partial<Workflow>): Promise<void>
Database.deleteWorkflow(workflowId: string): Promise<void>

Lua Script Management

Database.getLuaScripts(): Promise<LuaScript[]>
Database.setLuaScripts(scripts: LuaScript[]): Promise<void>
Database.addLuaScript(script: LuaScript): Promise<void>
Database.updateLuaScript(scriptId: string, updates: Partial<LuaScript>): Promise<void>
Database.deleteLuaScript(scriptId: string): Promise<void>

Page Management

Database.getPages(): Promise<PageConfig[]>
Database.setPages(pages: PageConfig[]): Promise<void>
Database.addPage(page: PageConfig): Promise<void>
Database.updatePage(pageId: string, updates: Partial<PageConfig>): Promise<void>
Database.deletePage(pageId: string): Promise<void>

Schema Management

Database.getSchemas(): Promise<ModelSchema[]>
Database.setSchemas(schemas: ModelSchema[]): Promise<void>
Database.addSchema(schema: ModelSchema): Promise<void>
Database.updateSchema(schemaName: string, updates: Partial<ModelSchema>): Promise<void>
Database.deleteSchema(schemaName: string): Promise<void>

Comment Management

Database.getComments(): Promise<Comment[]>
Database.setComments(comments: Comment[]): Promise<void>
Database.addComment(comment: Comment): Promise<void>
Database.updateComment(commentId: string, updates: Partial<Comment>): Promise<void>
Database.deleteComment(commentId: string): Promise<void>

Component Management

Database.getComponentHierarchy(): Promise<Record<string, ComponentNode>>
Database.setComponentHierarchy(hierarchy: Record<string, ComponentNode>): Promise<void>
Database.addComponentNode(node: ComponentNode): Promise<void>
Database.updateComponentNode(nodeId: string, updates: Partial<ComponentNode>): Promise<void>
Database.deleteComponentNode(nodeId: string): Promise<void>

Database.getComponentConfigs(): Promise<Record<string, ComponentConfig>>
Database.setComponentConfigs(configs: Record<string, ComponentConfig>): Promise<void>
Database.addComponentConfig(config: ComponentConfig): Promise<void>
Database.updateComponentConfig(configId: string, updates: Partial<ComponentConfig>): Promise<void>
Database.deleteComponentConfig(configId: string): Promise<void>

Global Configuration

Database.getAppConfig(): Promise<AppConfiguration | null>
Database.setAppConfig(config: AppConfiguration): Promise<void>

Database Operations

Database.initializeDatabase(): Promise<void>
Database.exportDatabase(): Promise<string>
Database.importDatabase(jsonData: string): Promise<void>
Database.clearDatabase(): Promise<void>

Password Security

SHA-512 Hashing

All passwords are hashed using SHA-512 before storage:

import { hashPassword, verifyPassword } from '@/lib/database'

// Register new user
const passwordHash = await hashPassword('userPassword123')
await Database.setCredential('username', passwordHash)

// Verify login
const isValid = await Database.verifyCredentials('username', 'userPassword123')

Security Features

1. No Plaintext Storage: Passwords are never stored in plain text
2. One-Way Hashing: SHA-512 is cryptographically secure and cannot be reversed
3. Credential Separation: Password hashes stored separately from user data
4. Secure Verification: Password verification uses constant-time comparison

Default Credentials

The system initializes with three default users:

Username	Password	Role	Access Level
god	god123	god	Level 4
admin	admin	admin	Level 3
demo	demo	user	Level 2

Important: Change these passwords in production!

Data Import/Export

Export Format

Database exports are JSON files containing all entities:

{
  "users": [...],
  "workflows": [...],
  "luaScripts": [...],
  "pages": [...],
  "schemas": [...],
  "comments": [...],
  "componentHierarchy": {...},
  "componentConfigs": {...},
  "appConfig": {...}
}

Note: Credentials (password hashes) are NOT included in exports for security.

Export Database

const jsonData = await Database.exportDatabase()
// Save to file or send to backup service

Import Database

const jsonData = /* load from file */
await Database.importDatabase(jsonData)

Clear Database

await Database.clearDatabase()
await Database.initializeDatabase() // Restore defaults

Database Manager UI

The Database Manager component (Level 4 → Database tab) provides:

• Real-time Statistics: View record counts for all entities
• Visual Overview: Cards showing data distribution
• Export/Import: Backup and restore database
• Clear Database: Reset to defaults (with confirmation)
• Key Inspector: View all KV storage keys
• Security Info: Documentation of password hashing

Migration from useKV

Previous versions used useKV hooks directly. The new Database layer provides:

1. Centralized Logic: All database operations in one place
2. Consistent API: Uniform CRUD operations across entities
3. Enhanced Security: Built-in password hashing
4. Better Types: Comprehensive TypeScript types
5. Easier Testing: Mockable database layer
6. Data Portability: Export/import functionality

Migration Example

Before:

const [users, setUsers] = useKV<User[]>('app_users', [])
setUsers((current) => [...(current || []), newUser])

After:

await Database.addUser(newUser)
const users = await Database.getUsers()

Best Practices

1. Use Database methods: Always use Database.* methods instead of direct KV access
2. Handle async: All Database operations are async
3. Error handling: Wrap Database calls in try-catch
4. Type safety: Use provided TypeScript types
5. Password security: Never log or expose password hashes
6. Regular backups: Export database periodically
7. Test imports: Validate JSON before importing
8. Monitor storage: Check database statistics regularly

Performance Considerations

• Batch operations: Use set methods for multiple records
• Lazy loading: Load data only when needed
• Caching: Store frequently accessed data in component state
• Pagination: Limit large lists (comments, users)
• Debouncing: Debounce rapid updates
• Indexing: Use Map/Set for lookups instead of array.find()

Troubleshooting

Database not persisting

• Check that KV API is available
• Verify async operations complete
• Look for runtime errors in console

Password verification failing

• Ensure password is hashed before storing
• Check username is correct
• Verify credentials initialized

Import fails

• Validate JSON structure
• Check all required fields present
• Ensure compatible version

Storage quota exceeded

• Export and clear old data
• Optimize large fields (compress JSON)
• Implement data cleanup policies