db.ts

/**
 * Database connection manager for Drizzle ORM.
 *
 * Supports both SQLite (via better-sqlite3) and PostgreSQL (via postgres.js).
 * Use initDb() for SQLite and initPostgresDb() for Supabase/PostgreSQL.
 */

import Database from "better-sqlite3";
import { mkdirSync } from "node:fs";
import { dirname } from "node:path";
import {
  drizzle as drizzleSqlite,
  type BetterSQLite3Database
} from "drizzle-orm/better-sqlite3";
import type { PostgresJsDatabase } from "drizzle-orm/postgres-js";
import type { Sql } from "postgres";
import { is } from "drizzle-orm";
import {
  SQLiteTable,
  getTableConfig,
  type SQLiteColumn
} from "drizzle-orm/sqlite-core";
import * as schema from "./schema/index.js";
import * as pgSchema from "./schema-pg/index.js";
import {
  MigrationRunner,
  SQLiteMigrationAdapter
} from "./migrations/index.js";

/**
 * better-sqlite3 refuses to create a database file whose parent directory
 * does not exist yet ("Cannot open database because the directory does not
 * exist") — a first run on a fresh machine (a new CI container, a fresh
 * install) hits this before anything else has had a chance to create the
 * data directory. `:memory:` has no parent directory to create.
 */
function ensureDbDirExists(dbPath: string): void {
  if (dbPath === ":memory:") return;
  mkdirSync(dirname(dbPath), { recursive: true });
}

export type DbDialect = "sqlite" | "postgres";

/**
 * A Drizzle database instance backed by either SQLite (better-sqlite3) or
 * PostgreSQL (postgres.js). The two dialects expose the same query-builder
 * surface (`select`/`insert`/`update`/`delete`), so callers can work with
 * either transparently.
 */
export type NodetoolDatabase =
  | BetterSQLite3Database<typeof schema>
  | PostgresJsDatabase<typeof pgSchema>;

let _db: NodetoolDatabase | null = null;
let _sqlite: Database.Database | null = null;
let _pgClient: Sql | null = null;
let _dbType: DbDialect = "sqlite";

/**
 * Initialize a SQLite database connection with a file path.
 * Configures WAL mode, busy timeout, and synchronous mode.
 */
export function initDb(dbPath: string): BetterSQLite3Database<typeof schema> {
  if (_db && _dbType === "sqlite") return _db as BetterSQLite3Database<typeof schema>;
  if (_db && _dbType === "postgres") {
    throw new Error(
      "A PostgreSQL connection is already active. Call closeDb() before switching to SQLite."
    );
  }
  ensureDbDirExists(dbPath);
  const sqlite = new Database(dbPath);
  sqlite.pragma("journal_mode = WAL");
  sqlite.pragma("busy_timeout = 30000");
  sqlite.pragma("synchronous = NORMAL");
  _sqlite = sqlite;
  _db = drizzleSqlite(sqlite, { schema });
  _dbType = "sqlite";

  sqlite.exec(getCreateTableStatementsSql());
  addMissingColumns(sqlite);
  repairApplicationConstraintDuplicates(sqlite);
  sqlite.exec(getCreateIndexStatementsSql());

  return _db;
}

/**
 * Initialize a PostgreSQL database connection.
 * Accepts a connection string (e.g. Supabase DATABASE_URL or DIRECT_URL).
 *
 * For Supabase, use the connection pooler URL (port 6543, transaction mode)
 * for the application, and the direct URL (port 5432) for migrations.
 * Migrations must be run separately via MigrationRunner + PostgresJsMigrationAdapter.
 */
export async function initPostgresDb(connectionString: string): Promise<void> {
  if (_db && _dbType === "postgres") return;
  if (_db && _dbType === "sqlite") {
    throw new Error(
      "A SQLite connection is already active. Call closeDb() before switching to PostgreSQL."
    );
  }

  // Dynamic import so that the `postgres` package is only loaded when needed,
  // keeping the SQLite-only path free of the extra dependency at runtime.
  const { default: postgres } = await import("postgres");
  const { drizzle: drizzlePg } = await import("drizzle-orm/postgres-js");

  const client = postgres(connectionString, {
    max: 10,
    idle_timeout: 20,
    connect_timeout: 10
  });

  _pgClient = client;
  _db = drizzlePg(client, { schema: pgSchema });
  _dbType = "postgres";
}

/**
 * Initialize an in-memory SQLite database for testing.
 * Creates all tables from the Drizzle schema.
 */
export function initTestDb(): BetterSQLite3Database<typeof schema> {
  if (_sqlite) {
    try {
      _sqlite.close();
    } catch {
      /* ignore */
    }
  }
  const sqlite = new Database(":memory:");
  _sqlite = sqlite;
  _db = drizzleSqlite(sqlite, { schema });
  _dbType = "sqlite";

  sqlite.exec(getCreateTableStatementsSql());
  sqlite.exec(getCreateIndexStatementsSql());

  return _db;
}

/**
 * Get the current database instance.
 *
 * Typed as the SQLite query builder because the two dialects expose the same
 * `select`/`insert`/`update`/`delete` surface and the model layer is written
 * against it (a PostgreSQL connection returns the same API, with promises that
 * the existing `await`s resolve transparently). Throws if not initialized.
 */
export function getDb(): BetterSQLite3Database<typeof schema> {
  if (!_db)
    throw new Error(
      "Database not initialized. Call initDb() or initPostgresDb() first."
    );
  return _db as BetterSQLite3Database<typeof schema>;
}


/**
 * Get the current database dialect.
 */
/**
 * The transaction handle a `db.transaction` callback receives.
 *
 * Derived from the driver's own signature rather than written out, so it keeps
 * up with the schema. Both dialects run through it — the SQLite branch takes a
 * synchronous callback and the Postgres branch an async one — because the two
 * expose the same query builders. The one capability they do not share is
 * Postgres row locking; see {@link forUpdate}.
 */
export type DbTransaction = Parameters<
  Parameters<BetterSQLite3Database<typeof schema>["transaction"]>[0]
>[0];

/**
 * Adds `FOR UPDATE` row locking to a select, on the Postgres branch.
 *
 * The connection is typed as the SQLite driver throughout (see {@link getDb}),
 * whose select builder has no `.for()` — postgres.js's does. Call sites used to
 * annotate their transaction handle `any` to reach it, which switched off
 * checking for the whole callback. This names the single difference instead,
 * and throws rather than silently returning an unlocked query if it is ever
 * called on a connection that cannot lock.
 */
export function forUpdate<Q>(query: Q): Q {
  const lockable = query as { for?: (mode: "update") => Q };
  if (!lockable.for) {
    throw new Error(
      "forUpdate() requires a Postgres query builder; branch on getDbType()."
    );
  }
  return lockable.for("update");
}

export function getDbType(): DbDialect {
  return _dbType;
}

/**
 * Get the underlying better-sqlite3 Database instance for raw queries.
 * Only available when using SQLite.
 */
export function getRawDb(): Database.Database {
  if (!_sqlite)
    throw new Error(
      "SQLite database not initialized. Raw access is only available for SQLite."
    );
  return _sqlite;
}

/** Execute dynamic SQL against the active database connection. */
export async function executeRaw(sql: string): Promise<{ rows: unknown[] }> {
  if (_dbType === "postgres") {
    if (!_pgClient) throw new Error("PostgreSQL database not initialized.");
    return { rows: await _pgClient.unsafe(sql) };
  }
  if (!_sqlite) throw new Error("SQLite database not initialized.");
  return { rows: _sqlite.prepare(sql).all() as unknown[] };
}

/**
 * Verify the database connection is alive with a lightweight query.
 * Dialect-aware: runs `select 1` over the active client. Throws if the
 * database is not initialized or the query fails.
 *
 * This is called by the process watchdog every 30 seconds. Do not use an
 * SQLite integrity pragma here: `quick_check` scans the database and can hold
 * the synchronous connection for several seconds on a large local database.
 */
export async function pingDb(): Promise<void> {
  if (!_db)
    throw new Error(
      "Database not initialized. Call initDb() or initPostgresDb() first."
    );
  if (_dbType === "postgres") {
    if (!_pgClient) throw new Error("PostgreSQL client not initialized.");
    await _pgClient`select 1`;
    return;
  }
  if (!_sqlite) throw new Error("SQLite database not initialized.");
  _sqlite.prepare("select 1").get();
}

/**
 * Apply pending SQLite migrations to a database file without initializing the
 * global Drizzle connection. Used by local backend startup before initDb().
 */
export async function migrateSqliteDb(dbPath: string): Promise<string[]> {
  ensureDbDirExists(dbPath);
  const sqlite = new Database(dbPath);
  sqlite.pragma("journal_mode = WAL");
  sqlite.pragma("busy_timeout = 30000");
  sqlite.pragma("synchronous = NORMAL");

  try {
    const adapter = new SQLiteMigrationAdapter(sqlite);
    const runner = new MigrationRunner(adapter);
    return await runner.migrate();
  } finally {
    sqlite.close();
  }
}

/**
 * Close the database connection and reset state.
 * For PostgreSQL, returns a Promise that resolves once the connection pool is drained.
 */
export async function closeDb(): Promise<void> {
  if (_sqlite) {
    try {
      _sqlite.close();
    } catch {
      /* ignore */
    }
    _sqlite = null;
  }
  if (_pgClient) {
    try {
      await _pgClient.end();
    } catch {
      /* ignore */
    }
    _pgClient = null;
  }
  _db = null;
  _dbType = "sqlite";
}

/**
 * The DDL fragment `ALTER TABLE … ADD COLUMN "x" <fragment>` needs for one
 * Drizzle column.
 *
 * SQLite rejects `ADD COLUMN … NOT NULL` without a constant default, so a
 * NOT NULL column that declares no default is added nullable — the same
 * concession the hand-written map this replaces made.
 */
function addColumnDdl(column: SQLiteColumn): string {
  const type = column.getSQLType().toLowerCase();
  if (!column.notNull || !column.hasDefault) return type;
  const value = column.default;
  if (typeof value === "string") {
    return `${type} NOT NULL DEFAULT '${value.replace(/'/g, "''")}'`;
  }
  if (typeof value === "number") return `${type} NOT NULL DEFAULT ${value}`;
  if (typeof value === "boolean") {
    return `${type} NOT NULL DEFAULT ${value ? 1 : 0}`;
  }
  // A default the generator cannot render as a SQL literal (a `$defaultFn`,
  // an SQL expression). Adding the column nullable still repairs the install;
  // asserting a literal we cannot produce would break it.
  return type;
}

/**
 * Expected columns per table, used for additive migration on existing SQLite
 * DBs. Derived from the Drizzle tables rather than restated, so a new column
 * or table reaches `addMissingColumns` the moment it reaches `src/schema/`.
 * `tests/schema-parity.test.ts` pins the derivation against the bootstrap DDL.
 */
export const TABLE_COLUMNS: Record<string, Record<string, string>> =
  Object.fromEntries(
    Object.values(schema)
      .filter((table) => is(table, SQLiteTable))
      .map((table) => {
        const config = getTableConfig(table as SQLiteTable);
        return [
          config.name,
          Object.fromEntries(
            config.columns.map((column) => [column.name, addColumnDdl(column)])
          )
        ];
      })
  );

function addMissingColumns(sqlite: Database.Database): void {
  for (const [tableName, expectedCols] of Object.entries(TABLE_COLUMNS)) {
    const existingCols = new Set(
      (
        sqlite.pragma(`table_info("${tableName}")`) as Array<{ name: string }>
      ).map((row) => row.name)
    );

    for (const [colName, colType] of Object.entries(expectedCols)) {
      if (!existingCols.has(colName)) {
        sqlite.exec(
          `ALTER TABLE "${tableName}" ADD COLUMN "${colName}" ${colType}`
        );
      }
    }
  }
}

/**
 * The schema bootstrap runs before the migration runner. Repair rows that
 * predate the application identity constraints before adding their indexes,
 * otherwise a legacy database with duplicates cannot even open far enough for
 * migration 20260829_000004 to repair it.
 */
function repairApplicationConstraintDuplicates(sqlite: Database.Database): void {
  // The indexes are what the repair exists for, so their presence is the
  // record that it already ran. Without this the two scans below run on every
  // start, forever, over a table that grows with every app run.
  const existing = sqlite
    .prepare(
      `SELECT name FROM sqlite_master
        WHERE type = 'index'
          AND name IN (
            'idx_application_deployment_one_live',
            'idx_application_invocation_app_invocation'
          )`
    )
    .all() as { name: string }[];
  if (existing.length === 2) return;

  const revokedAt = new Date().toISOString();
  const repair = sqlite.transaction(() => {
    sqlite
      .prepare(
        `UPDATE application_deployments AS deployment
            SET revoked_at = ?
          WHERE deployment.revoked_at IS NULL
            AND EXISTS (
              SELECT 1
                FROM application_deployments AS newer
               WHERE newer.application_id = deployment.application_id
                 AND newer.revoked_at IS NULL
                 AND (
                   newer.created_at > deployment.created_at
                   OR (
                     newer.created_at = deployment.created_at
                     AND newer.id > deployment.id
                   )
                 )
            )`
      )
      .run(revokedAt);
    sqlite
      .prepare(
        `UPDATE application_invocations AS invocation
            SET invocation_id = 'legacy:' || invocation.id
          WHERE EXISTS (
            SELECT 1
              FROM application_invocations AS newer
             WHERE newer.application_id = invocation.application_id
               AND newer.invocation_id = invocation.invocation_id
               AND (
                 newer.created_at > invocation.created_at
                 OR (
                   newer.created_at = invocation.created_at
                   AND newer.id > invocation.id
                 )
               )
          )`
      )
      .run();
  });
  repair();
}

export function getCreateSchemaSql(): string {
  return `
    CREATE TABLE IF NOT EXISTS "nodetool_workflows" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "name" text NOT NULL DEFAULT '',
      "tool_name" text,
      "description" text DEFAULT '',
      "tags" text,
      "thumbnail" text,
      "thumbnail_url" text,
      "graph" text NOT NULL,
      "settings" text,
      "package_name" text,
      "path" text,
      "run_mode" text,
      "workspace_id" text,
      "project_id" text NOT NULL DEFAULT 'default',
      "html_app" text,
      "app_doc" text,
      "receive_clipboard" integer,
      "access" text NOT NULL DEFAULT 'private',
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_workflows_user_id" ON "nodetool_workflows" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_workflows_access" ON "nodetool_workflows" ("access");
    CREATE INDEX IF NOT EXISTS "idx_workflows_user_project" ON "nodetool_workflows" ("user_id", "project_id");

    CREATE TABLE IF NOT EXISTS "nodetool_jobs" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "job_type" text NOT NULL DEFAULT '',
      "workflow_id" text NOT NULL,
      "project_id" text NOT NULL DEFAULT 'default',
      "status" text NOT NULL DEFAULT 'scheduled',
      "name" text DEFAULT '',
      "graph" text,
      "params" text,
      "worker_id" text,
      "heartbeat_at" text,
      "started_at" text,
      "finished_at" text,
      "completed_at" text,
      "failed_at" text,
      "error" text,
      "error_message" text,
      "cost" real,
      "logs" text,
      "retry_count" integer NOT NULL DEFAULT 0,
      "max_retries" integer NOT NULL DEFAULT 3,
      "version" integer NOT NULL DEFAULT 0,
      "execution_strategy" text,
      "execution_id" text,
      "runner_instance" text,
      "metadata_json" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_jobs_status" ON "nodetool_jobs" ("status");
    CREATE INDEX IF NOT EXISTS "idx_jobs_updated_at" ON "nodetool_jobs" ("updated_at");
    CREATE INDEX IF NOT EXISTS "idx_jobs_worker_id" ON "nodetool_jobs" ("worker_id");
    CREATE INDEX IF NOT EXISTS "idx_jobs_heartbeat_at" ON "nodetool_jobs" ("heartbeat_at");
    CREATE INDEX IF NOT EXISTS "idx_jobs_recovery" ON "nodetool_jobs" ("status", "heartbeat_at");
    CREATE INDEX IF NOT EXISTS "idx_jobs_user_project" ON "nodetool_jobs" ("user_id", "project_id");

    CREATE TABLE IF NOT EXISTS "nodetool_messages" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "thread_id" text NOT NULL,
      "role" text NOT NULL DEFAULT 'user',
      "name" text,
      "content" text,
      "tool_calls" text,
      "tool_call_id" text,
      "input_files" text,
      "output_files" text,
      "provider" text,
      "model" text,
      "cost" real,
      "workflow_id" text,
      "graph" text,
      "tools" text,
      "collections" text,
      "agent_mode" integer,
      "help_mode" integer,
      "agent_execution_id" text,
      "execution_event_type" text,
      "workflow_target" text,
      "media_generation" text,
      "provider_session" text,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_messages_thread_id" ON "nodetool_messages" ("thread_id");

    CREATE TABLE IF NOT EXISTS "nodetool_threads" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "workflow_id" text,
      "project_id" text NOT NULL DEFAULT 'default',
      "title" text NOT NULL DEFAULT '',
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_threads_user_id" ON "nodetool_threads" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_threads_user_workflow" ON "nodetool_threads" ("user_id", "workflow_id");
    CREATE INDEX IF NOT EXISTS "idx_threads_user_project" ON "nodetool_threads" ("user_id", "project_id");

    CREATE TABLE IF NOT EXISTS "nodetool_assets" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "parent_id" text,
      "file_id" text,
      "name" text NOT NULL DEFAULT '',
      "content_type" text NOT NULL DEFAULT 'application/octet-stream',
      "size" real,
      "duration" real,
      "metadata" text,
      "sketch_document_id" text,
      "workflow_id" text,
      "node_id" text,
      "job_id" text,
      "timeline_id" text,
      "project_id" text NOT NULL DEFAULT 'default',
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_assets_user_parent" ON "nodetool_assets" ("user_id", "parent_id");
    CREATE INDEX IF NOT EXISTS "idx_assets_user_project" ON "nodetool_assets" ("user_id", "project_id");

    CREATE TABLE IF NOT EXISTS "nodetool_secrets" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "key" text NOT NULL,
      "encrypted_value" text NOT NULL,
      "description" text DEFAULT '',
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_secrets_user_key" ON "nodetool_secrets" ("user_id", "key");
    CREATE INDEX IF NOT EXISTS "idx_secrets_user_id" ON "nodetool_secrets" ("user_id");

    CREATE TABLE IF NOT EXISTS "nodetool_workspaces" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "name" text NOT NULL DEFAULT '',
      "path" text NOT NULL DEFAULT '',
      "project_id" text NOT NULL DEFAULT 'default',
      "is_default" integer DEFAULT 0,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_workspaces_user_id" ON "nodetool_workspaces" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_workspaces_user_project" ON "nodetool_workspaces" ("user_id", "project_id");

    CREATE TABLE IF NOT EXISTS "nodetool_workflow_versions" (
      "id" text PRIMARY KEY NOT NULL,
      "workflow_id" text NOT NULL,
      "user_id" text NOT NULL,
      "name" text,
      "description" text,
      "graph" text NOT NULL,
      "version" integer NOT NULL DEFAULT 1,
      "save_type" text NOT NULL DEFAULT 'manual',
      "autosave_metadata" text,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_wv_workflow_id" ON "nodetool_workflow_versions" ("workflow_id");
    CREATE INDEX IF NOT EXISTS "idx_wv_user_id" ON "nodetool_workflow_versions" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_nodetool_workflow_versions_workflow_id_save_type_created_at" ON "nodetool_workflow_versions" ("workflow_id", "save_type", "created_at");

    CREATE TABLE IF NOT EXISTS "nodetool_oauth_credentials" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "provider" text NOT NULL,
      "account_id" text NOT NULL,
      "encrypted_access_token" text NOT NULL,
      "encrypted_refresh_token" text,
      "username" text,
      "token_type" text NOT NULL DEFAULT 'Bearer',
      "scope" text,
      "received_at" text NOT NULL,
      "expires_at" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_oauth_user_id" ON "nodetool_oauth_credentials" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_oauth_user_provider" ON "nodetool_oauth_credentials" ("user_id", "provider");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_oauth_user_provider_account" ON "nodetool_oauth_credentials" ("user_id", "provider", "account_id");

    CREATE TABLE IF NOT EXISTS "nodetool_predictions" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "node_id" text NOT NULL DEFAULT '',
      "node_type" text NOT NULL DEFAULT '',
      "provider" text NOT NULL DEFAULT '',
      "model" text NOT NULL DEFAULT '',
      "workflow_id" text,
      "project_id" text,
      "document_id" text,
      "error" text,
      "logs" text,
      "status" text NOT NULL DEFAULT 'pending',
      "cost" real,
      "input_tokens" integer,
      "output_tokens" integer,
      "total_tokens" integer,
      "cached_tokens" integer,
      "reasoning_tokens" integer,
      "billing_unit" text,
      "quantity" real,
      "unit_price" real,
      "currency" text,
      "provider_request_id" text,
      "capability" text,
      "surface" text,
      "thread_id" text,
      "tool_call_id" text,
      "request_id" text,
      "job_id" text,
      "asset_ids" text,
      "reconciled_at" text,
      "reconcile_attempts" integer NOT NULL DEFAULT 0,
      "created_at" text,
      "started_at" text,
      "completed_at" text,
      "duration" real,
      "hardware" text,
      "input_size" integer,
      "output_size" integer,
      "parameters" text,
      "metadata" text
    );
    CREATE INDEX IF NOT EXISTS "idx_prediction_user_status" ON "nodetool_predictions" ("user_id", "status");
    CREATE INDEX IF NOT EXISTS "idx_prediction_user_thread" ON "nodetool_predictions" ("user_id", "thread_id");
    CREATE INDEX IF NOT EXISTS "idx_prediction_job" ON "nodetool_predictions" ("job_id");
    CREATE INDEX IF NOT EXISTS "idx_prediction_user_request" ON "nodetool_predictions" ("user_id", "request_id");
    CREATE INDEX IF NOT EXISTS "idx_predictions_user_id" ON "nodetool_predictions" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_predictions_user_provider" ON "nodetool_predictions" ("user_id", "provider");
    CREATE INDEX IF NOT EXISTS "idx_prediction_created_at" ON "nodetool_predictions" ("created_at");
    CREATE INDEX IF NOT EXISTS "idx_prediction_user_model" ON "nodetool_predictions" ("user_id", "model");
    CREATE INDEX IF NOT EXISTS "idx_prediction_user_project" ON "nodetool_predictions" ("user_id", "project_id");

    CREATE TABLE IF NOT EXISTS "run_events" (
      "id" text PRIMARY KEY NOT NULL,
      "run_id" text NOT NULL,
      "seq" integer NOT NULL,
      "event_type" text NOT NULL,
      "event_time" text NOT NULL,
      "node_id" text,
      "payload" text
    );
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_run_events_run_seq" ON "run_events" ("run_id", "seq");
    CREATE INDEX IF NOT EXISTS "idx_run_events_run_node" ON "run_events" ("run_id", "node_id");
    CREATE INDEX IF NOT EXISTS "idx_run_events_run_type" ON "run_events" ("run_id", "event_type");

    CREATE TABLE IF NOT EXISTS "nodetool_team_tasks" (
      "id" text PRIMARY KEY NOT NULL,
      "team_id" text NOT NULL,
      "title" text NOT NULL,
      "description" text NOT NULL DEFAULT '',
      "status" text NOT NULL DEFAULT 'open',
      "created_by" text NOT NULL,
      "claimed_by" text,
      "depends_on" text NOT NULL,
      "required_skills" text NOT NULL,
      "priority" integer NOT NULL DEFAULT 5,
      "artifacts" text NOT NULL,
      "parent_task_id" text,
      "result" text,
      "failure_reason" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_team_tasks_team_id" ON "nodetool_team_tasks" ("team_id");
    CREATE INDEX IF NOT EXISTS "idx_team_tasks_status" ON "nodetool_team_tasks" ("status");
    CREATE INDEX IF NOT EXISTS "idx_team_tasks_team_status" ON "nodetool_team_tasks" ("team_id", "status");
    CREATE INDEX IF NOT EXISTS "idx_team_tasks_parent" ON "nodetool_team_tasks" ("parent_task_id");

    CREATE TABLE IF NOT EXISTS "nodetool_settings" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "key" text NOT NULL,
      "value" text NOT NULL,
      "description" text DEFAULT '',
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_settings_user_key" ON "nodetool_settings" ("user_id", "key");
    CREATE INDEX IF NOT EXISTS "idx_settings_user_id" ON "nodetool_settings" ("user_id");

    CREATE TABLE IF NOT EXISTS "timeline_sequences" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "project_id" text NOT NULL,
      "workflow_id" text,
      "name" text NOT NULL,
      "fps" integer NOT NULL DEFAULT 30,
      "width" integer NOT NULL DEFAULT 1920,
      "height" integer NOT NULL DEFAULT 1080,
      "duration_ms" integer NOT NULL DEFAULT 0,
      "document" text NOT NULL,
      "revision" integer NOT NULL DEFAULT 0,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_timeline_sequence_user" ON "timeline_sequences" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_timeline_sequence_project" ON "timeline_sequences" ("project_id");
    CREATE INDEX IF NOT EXISTS "idx_timeline_sequence_updated" ON "timeline_sequences" ("updated_at");

    CREATE TABLE IF NOT EXISTS "timeline_sequence_versions" (
      "id" text PRIMARY KEY NOT NULL,
      "timeline_id" text NOT NULL REFERENCES "timeline_sequences" ("id") ON DELETE CASCADE,
      "user_id" text NOT NULL,
      "name" text,
      "version" integer NOT NULL DEFAULT 1,
      "save_type" text NOT NULL DEFAULT 'manual',
      "fps" integer NOT NULL DEFAULT 30,
      "width" integer NOT NULL DEFAULT 1920,
      "height" integer NOT NULL DEFAULT 1080,
      "duration_ms" integer NOT NULL DEFAULT 0,
      "document" text NOT NULL,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_tsv_timeline" ON "timeline_sequence_versions" ("timeline_id");
    CREATE INDEX IF NOT EXISTS "idx_tsv_user" ON "timeline_sequence_versions" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_tsv_timeline_save_type_created" ON "timeline_sequence_versions" ("timeline_id", "save_type", "created_at");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_tsv_timeline_version" ON "timeline_sequence_versions" ("timeline_id", "version");

    CREATE TABLE IF NOT EXISTS "image_documents" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "project_id" text NOT NULL,
      "workflow_id" text,
      "name" text NOT NULL,
      "width" integer NOT NULL DEFAULT 1024,
      "height" integer NOT NULL DEFAULT 1024,
      "background_color" text NOT NULL DEFAULT '#ffffff',
      "document" text NOT NULL,
      "thumbnail_asset_id" text,
      "revision" integer NOT NULL DEFAULT 0,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_image_document_user" ON "image_documents" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_image_document_project" ON "image_documents" ("project_id");
    CREATE INDEX IF NOT EXISTS "idx_image_document_updated" ON "image_documents" ("updated_at");

    CREATE TABLE IF NOT EXISTS "image_document_versions" (
      "id" text PRIMARY KEY NOT NULL,
      "image_document_id" text NOT NULL REFERENCES "image_documents" ("id") ON DELETE CASCADE,
      "user_id" text NOT NULL,
      "name" text,
      "version" integer NOT NULL DEFAULT 1,
      "save_type" text NOT NULL DEFAULT 'manual',
      "width" integer NOT NULL DEFAULT 1024,
      "height" integer NOT NULL DEFAULT 1024,
      "background_color" text NOT NULL DEFAULT '#ffffff',
      "document" text NOT NULL,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_idv_document" ON "image_document_versions" ("image_document_id");
    CREATE INDEX IF NOT EXISTS "idx_idv_user" ON "image_document_versions" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_idv_document_save_type_created" ON "image_document_versions" ("image_document_id", "save_type", "created_at");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_idv_document_version" ON "image_document_versions" ("image_document_id", "version");

    CREATE TABLE IF NOT EXISTS "worker_profiles" (
      "id" text PRIMARY KEY NOT NULL,
      "name" text NOT NULL,
      "target" text NOT NULL,
      "image" text NOT NULL,
      "spec" text NOT NULL,
      "token_policy" text NOT NULL,
      "idle_timeout_minutes" integer,
      "max_lifetime_minutes" integer,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_worker_profiles_name" ON "worker_profiles" ("name");
    CREATE TABLE IF NOT EXISTS "worker_instances" (
      "id" text PRIMARY KEY NOT NULL,
      "profile_name" text NOT NULL,
      "target" text NOT NULL,
      "provider_ref" text NOT NULL,
      "ws_url" text NOT NULL,
      "encrypted_token" text,
      "status" text NOT NULL,
      "attached_to" text,
      "created_at" text NOT NULL,
      "last_activity_at" text NOT NULL,
      "estimated_cost_usd" real
    );
    CREATE INDEX IF NOT EXISTS "idx_worker_instances_status" ON "worker_instances" ("status");
    CREATE INDEX IF NOT EXISTS "idx_worker_instances_profile_name" ON "worker_instances" ("profile_name");

    CREATE TABLE IF NOT EXISTS "run_inbox_messages" (
      "id" text PRIMARY KEY NOT NULL,
      "message_id" text NOT NULL,
      "run_id" text NOT NULL,
      "node_id" text NOT NULL,
      "handle" text NOT NULL,
      "msg_seq" integer NOT NULL,
      "payload_json" text,
      "payload_ref" text,
      "status" text NOT NULL,
      "claim_worker_id" text,
      "claim_expires_at" text,
      "consumed_at" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_inbox_run_node_handle_seq" ON "run_inbox_messages" ("run_id", "node_id", "handle", "msg_seq");
    CREATE INDEX IF NOT EXISTS "idx_inbox_run_node_handle_status" ON "run_inbox_messages" ("run_id", "node_id", "handle", "status");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_inbox_message_id" ON "run_inbox_messages" ("message_id");

    CREATE TABLE IF NOT EXISTS "trigger_inputs" (
      "id" text PRIMARY KEY NOT NULL,
      "input_id" text NOT NULL,
      "run_id" text NOT NULL,
      "node_id" text NOT NULL,
      "payload_json" text,
      "processed" integer NOT NULL DEFAULT 0,
      "processed_at" text,
      "cursor" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_trigger_input_run_node_processed" ON "trigger_inputs" ("run_id", "node_id", "processed");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_trigger_input_id" ON "trigger_inputs" ("input_id");

    CREATE TABLE IF NOT EXISTS "trigger_registrations" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "workflow_id" text NOT NULL,
      "node_id" text NOT NULL,
      "kind" text NOT NULL,
      "config_json" text,
      "enabled" integer NOT NULL DEFAULT 1,
      "cursor" text,
      "last_fired_at" text,
      "last_error" text,
      "disabled_reason" text,
      "consecutive_failures" integer NOT NULL DEFAULT 0,
      "run_count" integer NOT NULL DEFAULT 0,
      "expires_at" text,
      "max_runs" integer,
      "supervise" integer NOT NULL DEFAULT 0,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_trigger_reg_workflow" ON "trigger_registrations" ("workflow_id");
    CREATE INDEX IF NOT EXISTS "idx_trigger_reg_kind_enabled" ON "trigger_registrations" ("kind", "enabled");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_trigger_reg_workflow_node" ON "trigger_registrations" ("workflow_id", "node_id");

    CREATE TABLE IF NOT EXISTS "nodetool_workflow_collaborators" (
      "id" text PRIMARY KEY NOT NULL,
      "workflow_id" text NOT NULL,
      "user_id" text NOT NULL,
      "role" text NOT NULL DEFAULT 'viewer',
      "invited_by" text NOT NULL,
      "created_at" text NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_wcol_workflow_user" ON "nodetool_workflow_collaborators" ("workflow_id", "user_id");
    CREATE INDEX IF NOT EXISTS "idx_wcol_user_id" ON "nodetool_workflow_collaborators" ("user_id");

    CREATE TABLE IF NOT EXISTS "nodetool_workflow_shares" (
      "id" text PRIMARY KEY NOT NULL,
      "workflow_id" text NOT NULL,
      "token" text NOT NULL,
      "role" text NOT NULL DEFAULT 'viewer',
      "created_by" text NOT NULL,
      "created_at" text NOT NULL,
      "revoked_at" text
    );
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_wshare_token" ON "nodetool_workflow_shares" ("token");
    CREATE INDEX IF NOT EXISTS "idx_wshare_workflow_id" ON "nodetool_workflow_shares" ("workflow_id");

    CREATE TABLE IF NOT EXISTS "storyboards" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "project_id" text NOT NULL,
      "name" text NOT NULL,
      "document" text NOT NULL,
      "timeline_id" text,
      "revision" integer NOT NULL DEFAULT 0,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_storyboard_user" ON "storyboards" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_storyboard_project" ON "storyboards" ("project_id");
    CREATE INDEX IF NOT EXISTS "idx_storyboard_updated" ON "storyboards" ("updated_at");

    CREATE TABLE IF NOT EXISTS "applications" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "project_id" text NOT NULL,
      "name" text NOT NULL,
      "description" text NOT NULL DEFAULT '',
      "document" text NOT NULL,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_application_user" ON "applications" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_application_project" ON "applications" ("project_id");
    CREATE INDEX IF NOT EXISTS "idx_application_updated" ON "applications" ("updated_at");

    CREATE TABLE IF NOT EXISTS "application_versions" (
      "id" text PRIMARY KEY NOT NULL,
      "application_id" text NOT NULL REFERENCES "applications" ("id") ON DELETE CASCADE,
      "user_id" text,
      "version" integer NOT NULL,
      "document" text NOT NULL,
      "capabilities" text NOT NULL,
      "workflow_graphs" text,
      "released" integer NOT NULL DEFAULT 0,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_application_version_app" ON "application_versions" ("application_id");
    CREATE INDEX IF NOT EXISTS "idx_application_version_released" ON "application_versions" ("released");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_application_version_app_version" ON "application_versions" ("application_id", "version");

    CREATE TABLE IF NOT EXISTS "application_deployments" (
      "id" text PRIMARY KEY NOT NULL,
      "application_id" text NOT NULL REFERENCES "applications" ("id") ON DELETE CASCADE,
      "user_id" text NOT NULL,
      "token" text NOT NULL,
      "created_at" text NOT NULL,
      "revoked_at" text
    );
    CREATE INDEX IF NOT EXISTS "idx_application_deployment_app" ON "application_deployments" ("application_id");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_application_deployment_token" ON "application_deployments" ("token");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_application_deployment_one_live" ON "application_deployments" ("application_id") WHERE "revoked_at" IS NULL;

    CREATE TABLE IF NOT EXISTS "application_budgets" (
      "application_id" text PRIMARY KEY NOT NULL REFERENCES "applications" ("id") ON DELETE CASCADE,
      "period" text NOT NULL DEFAULT 'month',
      "max_usd" real,
      "max_invocations" integer,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );

    CREATE TABLE IF NOT EXISTS "application_invocations" (
      "id" text PRIMARY KEY NOT NULL,
      "application_id" text NOT NULL REFERENCES "applications" ("id") ON DELETE CASCADE,
      "user_id" text,
      "version" integer,
      "invocation_id" text NOT NULL,
      "operation_id" text NOT NULL DEFAULT '',
      "estimated_usd" real NOT NULL DEFAULT 0,
      "actual_usd" real,
      "status" text NOT NULL DEFAULT 'running',
      "created_at" text NOT NULL,
      "settled_at" text
    );
    CREATE INDEX IF NOT EXISTS "idx_application_invocation_app" ON "application_invocations" ("application_id");
    CREATE INDEX IF NOT EXISTS "idx_application_invocation_created" ON "application_invocations" ("created_at");
    CREATE INDEX IF NOT EXISTS "idx_application_invocation_invocation" ON "application_invocations" ("invocation_id");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_application_invocation_app_invocation" ON "application_invocations" ("application_id", "invocation_id");

    CREATE TABLE IF NOT EXISTS "nodetool_credit_ledger" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "delta" integer NOT NULL,
      "kind" text NOT NULL,
      "description" text,
      "period_key" text,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_credit_ledger_user" ON "nodetool_credit_ledger" ("user_id");

    CREATE TABLE IF NOT EXISTS "nodetool_user_subscriptions" (
      "user_id" text PRIMARY KEY NOT NULL,
      "plan_id" text NOT NULL DEFAULT 'free',
      "status" text NOT NULL DEFAULT 'active',
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );

    CREATE TABLE IF NOT EXISTS "projects" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "name" text NOT NULL,
      "kind" text NOT NULL DEFAULT '',
      "thread_id" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_project_user" ON "projects" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_project_updated" ON "projects" ("updated_at");

    CREATE TABLE IF NOT EXISTS "scripts" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "project_id" text NOT NULL,
      "name" text NOT NULL,
      "document" text NOT NULL,
      "timeline_id" text,
      "storyboard_id" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_script_user" ON "scripts" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_script_project" ON "scripts" ("project_id");
    CREATE INDEX IF NOT EXISTS "idx_script_updated" ON "scripts" ("updated_at");

    CREATE TABLE IF NOT EXISTS "js_scripts" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "project_id" text NOT NULL,
      "name" text NOT NULL,
      "document" text NOT NULL,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_js_script_user" ON "js_scripts" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_js_script_project" ON "js_scripts" ("project_id");
    CREATE INDEX IF NOT EXISTS "idx_js_script_updated" ON "js_scripts" ("updated_at");

    CREATE TABLE IF NOT EXISTS "js_script_versions" (
      "id" text PRIMARY KEY NOT NULL,
      "js_script_id" text NOT NULL REFERENCES "js_scripts" ("id") ON DELETE CASCADE,
      "user_id" text NOT NULL,
      "name" text,
      "version" integer NOT NULL DEFAULT 1,
      "save_type" text NOT NULL DEFAULT 'manual',
      "document" text NOT NULL,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_jsv_script" ON "js_script_versions" ("js_script_id");
    CREATE INDEX IF NOT EXISTS "idx_jsv_user" ON "js_script_versions" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_jsv_script_save_type_created" ON "js_script_versions" ("js_script_id", "save_type", "created_at");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_jsv_script_version" ON "js_script_versions" ("js_script_id", "version");

    CREATE TABLE IF NOT EXISTS "skills" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "name" text NOT NULL,
      "description" text NOT NULL DEFAULT '',
      "content" text NOT NULL DEFAULT '',
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_skills_user" ON "skills" ("user_id");
    CREATE INDEX IF NOT EXISTS "idx_skills_user_name" ON "skills" ("user_id", "name");
    CREATE INDEX IF NOT EXISTS "idx_skills_updated" ON "skills" ("updated_at");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_skills_user_name_unique" ON "skills" ("user_id", "name");

    CREATE TABLE IF NOT EXISTS "nodetool_memories" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "thread_id" text NOT NULL DEFAULT '',
      "kind" text NOT NULL DEFAULT 'note',
      "title" text NOT NULL DEFAULT '',
      "content" text NOT NULL DEFAULT '',
      "resources" text,
      "metadata" text,
      "created_at" text NOT NULL,
      "updated_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_memory_user_created" ON "nodetool_memories" ("user_id", "created_at");
    CREATE INDEX IF NOT EXISTS "idx_memory_thread_created" ON "nodetool_memories" ("thread_id", "created_at");

    CREATE TABLE IF NOT EXISTS "external_identities" (
      "id" text PRIMARY KEY NOT NULL,
      "provider" text NOT NULL,
      "external_id" text NOT NULL,
      "user_id" text NOT NULL,
      "linked_at" text NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_external_identity_provider_external" ON "external_identities" ("provider", "external_id");
    CREATE INDEX IF NOT EXISTS "idx_external_identity_user" ON "external_identities" ("user_id");

    CREATE TABLE IF NOT EXISTS "access_tokens" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "name" text NOT NULL,
      "secret_hash" text NOT NULL,
      "created_at" text NOT NULL,
      "expires_at" text,
      "last_used_at" text
    );
    CREATE INDEX IF NOT EXISTS "idx_access_token_user" ON "access_tokens" ("user_id");

    CREATE TABLE IF NOT EXISTS "mcp_oauth_clients" (
      "id" text PRIMARY KEY NOT NULL,
      "client_name" text NOT NULL,
      "redirect_uris" text NOT NULL,
      "created_at" text NOT NULL,
      "last_used_at" text
    );

    CREATE TABLE IF NOT EXISTS "mcp_oauth_grants" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "client_id" text NOT NULL,
      "client_name" text NOT NULL,
      "scope" text NOT NULL,
      "resource" text NOT NULL,
      "created_at" text NOT NULL,
      "revoked_at" text
    );
    CREATE INDEX IF NOT EXISTS "idx_mcp_oauth_grant_user" ON "mcp_oauth_grants" ("user_id");

    CREATE TABLE IF NOT EXISTS "mcp_oauth_tokens" (
      "id" text PRIMARY KEY NOT NULL,
      "grant_id" text NOT NULL,
      "kind" text NOT NULL,
      "secret_hash" text NOT NULL,
      "expires_at" text NOT NULL,
      "rotated_from" text,
      "last_used_at" text
    );
    CREATE INDEX IF NOT EXISTS "idx_mcp_oauth_token_grant" ON "mcp_oauth_tokens" ("grant_id");
    CREATE UNIQUE INDEX IF NOT EXISTS "idx_mcp_oauth_token_rotated_from" ON "mcp_oauth_tokens" ("rotated_from");

    CREATE TABLE IF NOT EXISTS "nodetool_user_events" (
      "id" text PRIMARY KEY NOT NULL,
      "user_id" text NOT NULL,
      "event_type" text NOT NULL,
      "subject_type" text,
      "subject_id" text,
      "metadata" text,
      "created_at" text NOT NULL
    );
    CREATE INDEX IF NOT EXISTS "idx_user_event_user_created" ON "nodetool_user_events" ("user_id", "created_at");
    CREATE INDEX IF NOT EXISTS "idx_user_event_type_created" ON "nodetool_user_events" ("event_type", "created_at");
  `;
}

function getCreateTableStatementsSql(): string {
  return getCreateSchemaSql()
    .split(";")
    .map((statement) => statement.trim())
    .filter((statement) => statement.startsWith("CREATE TABLE"))
    .join(";\n");
}

function getCreateIndexStatementsSql(): string {
  return getCreateSchemaSql()
    .split(";")
    .map((statement) => statement.trim())
    .filter(
      (statement) =>
        statement.startsWith("CREATE INDEX") ||
        statement.startsWith("CREATE UNIQUE INDEX")
    )
    .join(";\n");
}