Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

4.8 KB
Raw Blame
// Universe DDL — universes, local character voice profiles, + universe run history. Extracted verbatim from
// ensureSchemaImpl() in server/lib/db.js (#2832); idempotent, runs on every boot.
export const universesDdl = [
    // Universe Builder records (Phase 3 Create migration, issue #1014). One row
    // per universe, the full sanitized record (canon bibles, categories,
    // compositeSheets, locks, influences) in `data` JSONB, moved out of
    // data/universes/{id}/index.json (collectionStore). Only the fields the
    // service/federation query, join, or sort on are mirrored into columns:
    // `name` (rename-cascade + delete-guard + list sort), `schema_version` (the
    // RECORD-shape version sanitizeTemplate stamps — a column so a future
    // migration can find unmigrated rows without parsing JSONB), `ephemeral`
    // (the snapshot loop filters local-only records), and the LWW/tombstone
    // trio (updated_at/deleted/deleted_at). NO sync_sequence: universes
    // federate via the EXISTING dataSync snapshot/push model (LWW on the body's
    // updatedAt), NOT catalog-style pull cursors — the storage swap is invisible
    // to peers (no schema-version bump). The mirror columns are populated FROM
    // the record body (mirrorTimestamp), not a DB trigger. Mirrors the universes
    // block in init-db.sql.
    `CREATE TABLE IF NOT EXISTS universes (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      data JSONB NOT NULL DEFAULT '{}'::jsonb,
      schema_version INTEGER NOT NULL DEFAULT 4,
      ephemeral BOOLEAN DEFAULT FALSE,
      created_at TIMESTAMPTZ DEFAULT NOW(),
      updated_at TIMESTAMPTZ DEFAULT NOW(),
      deleted BOOLEAN DEFAULT FALSE,
      deleted_at TIMESTAMPTZ
    )`,
    // Partial index on the live set — the common list/scan path is "non-deleted
    // universes". updated_at supports LWW-staleness scans.
    `CREATE INDEX IF NOT EXISTS idx_universes_live ON universes (deleted) WHERE deleted = FALSE`,
    `CREATE INDEX IF NOT EXISTS idx_universes_updated ON universes (updated_at)`,

    // Machine-local character voice profiles (#5380). The portable character
    // canon remains inside the federated universe JSON; only a local binding
    // plus preset/artifact provenance live here. There is one approved profile
    // per universe/character pair, while the record's version tracks explicit
    // re-promotions of a preset.
    `CREATE TABLE IF NOT EXISTS voice_profiles (
      id TEXT PRIMARY KEY,
      universe_id TEXT NOT NULL,
      character_id TEXT NOT NULL,
      approval_status TEXT NOT NULL DEFAULT 'draft' CHECK (approval_status IN ('draft', 'approved', 'retired')),
      data JSONB NOT NULL DEFAULT '{}'::jsonb,
      created_at TIMESTAMPTZ DEFAULT NOW(),
      updated_at TIMESTAMPTZ DEFAULT NOW()
    )`,
    `ALTER TABLE voice_profiles ALTER COLUMN universe_id DROP NOT NULL`,
    `ALTER TABLE voice_profiles ALTER COLUMN character_id DROP NOT NULL`,
    `CREATE INDEX IF NOT EXISTS idx_voice_profiles_binding ON voice_profiles (universe_id, character_id, updated_at DESC)`,
    `CREATE UNIQUE INDEX IF NOT EXISTS idx_voice_profiles_approved_binding ON voice_profiles (universe_id, character_id) WHERE approval_status = 'approved'`,

    // Render provenance is deliberately separate from the federated pipeline
    // issue record. It keeps the local profile revision and precise delivery
    // data attached to a rendered dialogue line without exporting a
    // machine-local profile id to a peer.
    `CREATE TABLE IF NOT EXISTS voice_profile_renders (
      issue_id TEXT NOT NULL,
      line_id TEXT NOT NULL,
      profile_id TEXT NOT NULL,
      profile_revision INTEGER NOT NULL,
      data JSONB NOT NULL DEFAULT '{}'::jsonb,
      created_at TIMESTAMPTZ DEFAULT NOW(),
      updated_at TIMESTAMPTZ DEFAULT NOW(),
      PRIMARY KEY (issue_id, line_id)
    )`,
    `CREATE INDEX IF NOT EXISTS idx_voice_profile_renders_profile ON voice_profile_renders (profile_id, profile_revision, updated_at DESC)`,

    // Universe render-history log (issue #1014). The type-level `config.runs[]`
    // array collectionStore kept in data/universes/index.json (capped 200,
    // NEVER federated — per-peer local) becomes its own table. `universe_id` is
    // a soft ref (no FK): the cascade-clean on universe delete is handled in the
    // service exactly as the file backend did, and a soft ref keeps the table
    // independent of universe-row insert ordering during the one-time import.
    // `data` holds jobIds[]/promptCount/collectionId. Mirrors init-db.sql.
    `CREATE TABLE IF NOT EXISTS universe_runs (
      id TEXT PRIMARY KEY,
      universe_id TEXT NOT NULL,
      collection_id TEXT,
      data JSONB NOT NULL DEFAULT '{}'::jsonb,
      created_at TIMESTAMPTZ DEFAULT NOW()
    )`,
    `CREATE INDEX IF NOT EXISTS idx_universe_runs_universe ON universe_runs (universe_id, created_at DESC)`,

];