skills/brancha/app/src/server/db/schema.ts

import { database } from './connection.js';

export function initializeSchema() {
  database.exec(`
    PRAGMA journal_mode = WAL;
    PRAGMA busy_timeout = 5000;
    PRAGMA foreign_keys = ON;

    CREATE TABLE IF NOT EXISTS projects (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      prompt TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS nodes (
      id TEXT PRIMARY KEY,
      project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
      parent_id TEXT REFERENCES nodes(id) ON DELETE CASCADE,
      position INTEGER NOT NULL DEFAULT 0,
      type TEXT NOT NULL DEFAULT 'text' CHECK (type IN ('text', 'html', 'markdown', 'svg', 'image', 'video', 'audio', 'error')),
      title TEXT NOT NULL,
      content TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      CHECK (type IS NOT 'error' OR content IS NOT NULL)
    );

    CREATE INDEX IF NOT EXISTS idx_nodes_project ON nodes(project_id, parent_id, position);
    CREATE INDEX IF NOT EXISTS idx_nodes_parent ON nodes(parent_id, position);
  `);
}