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

6.1 KB
Raw Blame

server/lib/db/schema/ — per-domain boot DDL

The idempotent CREATE TABLE / CREATE INDEX / trigger DDL that ensureSchemaImpl() (in server/lib/db.js) runs on every boot, split out of that formerly ~1265-line function into one module per domain (#2832). Each module exports a plain statement array; index.js re-exports them and composes the two ordered lists the composer runs.

Byte-identical, order-preserving. These modules were extracted verbatim — statement text and ordering are unchanged, so the composed schema is identical to the pre-split inline version. Order is load-bearing (FK references, the record_audit_log() function preceding its triggers, and catalog_user_types' original post-media position), so if you add a table, append it in the domain module and keep the composer order in index.js intact.

Parity with server/scripts/init-db.sql (the fresh-install path) is locked by server/lib/db.ddlParity.test.js, which auto-discovers every table declared in both sources and compares its columns, index definitions (UNIQUE, access method, column list, partial predicate), non-audit triggers and CHECK literal sets. Two tolerance lists hold the one-sided tables: SQL_ONLY_TOLERATED for tables in init-db.sql but not JS (the memory system, #1337), and BOOT_BACKFILL_ONLY for tables in JS but not init-db.sql, each with a one-line reason.

Module Export(s) Domain
core.js coreDdl, memorySearchDdl, restoreReceiptsDdl Memory search lexemes and sync columns + the schema_migrations version tracker + machine-local restore_receipts (snapshot restore replay receipts, #9725)
tribe.js tribeDdl Tribe CRM — people, network-scoped identities (tribe_identities, the durable-handle truth table behind Beeper linking), touchpoints, memory links (machine-local)
humanActivity.js humanActivityDdl Human-activity timeline event store (machine-local)
post.js postDdl MeatSpace POST normalized runs and attempts (machine-local)
commissions.js commissionsDdl Creative Commissions + feedback (machine-local)
userActions.js userActionsDdl Operator-action ledger — what the human did in the UI (machine-local)
reviewQueueTriage.js reviewQueueTriageDdl Machine-local Review Hub presentation markers — snooze, optional dismissal, and delivery generation keyed by canonical action identity
cosTasks.js cosTasksDdl The CoS task queues, one row per task (stored form, queue order, completed-at window) plus a per-queue write version
cosAgents.js cosAgentsDdl The CoS agent working set (running + not-yet-archived), one row per agent (exact JSON text, query projections, insertion order) plus a write version
cosAgentFeedback.js cosAgentFeedbackDdl Durable machine-local references to completed manual CoS runs awaiting a rating (agent id + archive locator only)
aiGraph.js aiGraphDdl AI provider connection graph — connections, harness bindings, executable route bindings (machine-local)
peerExecution.js peerExecutionDdl Receiver-local permanent execution consumption and generation floors; no grants or federation
catalog.js catalogDdl, catalogUserTypesDdl, catalogPendingAppliesDdl Catalog scraps/ingredients/tags/media + user-defined types + receiver-local dependency inbox
catalogIdentity.js catalogIdentityDdl Owner-coordinated identity receipts, durable mutation fences and canonical lookup functions
catalogIdentityViews.js buildCatalogIdentityViewsDdl() Read-only association and revision unions retaining authoritative owner evidence
media.js mediaDdl Creative-director / music-video projects, mood boards, media assets, and Code Animation jobs
universes.js universesDdl Universes, machine-local character voice profiles/renders, + universe run history
library.js libraryDdl Authors, artists, albums, tracks
pipeline.js pipelineDdl Pipeline series, issues, story-builder sessions
writersRoom.js writersRoomDdl Writers-Room folders, works, draft versions, exercises
lora.js loraDdl LoRA training runs
privacy.js privacyDdl Privacy suite — vault, consents, orgs, brokers, change events
stackerNews.js stackerNewsDdl Stacker News accounts, territories, untrusted-content analyses, and review-gated actions
x.js xDdl X account diagnostics, public post metrics, and review-gated drafts
beeper.js beeperDdl Beeper conversation mirror — accounts, the vault-encrypted access credential, conversations, messages, participants, attachment metadata, sync cursors, and the outbound send outbox (machine-local, never federated)
githubNotifications.js githubNotificationsDdl GitHub notifications inbox — per-account notification threads (metadata only) and the fetch cursor (machine-local, never federated)
audit.js auditDdl, auditedTables, buildAuditTriggers() record_audit table/function + per-table audit triggers
syncFeed.js syncFeedDdl, syncFeedTables, buildSyncFeedTriggers() Commit-ordered federation change feed (sync_feed) + deferred capture triggers on memories and the catalog tables (#8315)

Composer (index.js)

  • buildUpgradeDdl() → phase-1 list (core → tribe → humanActivity → post → commissions → userActions → reviewQueueTriage → cosAgentFeedback → cosTasks → cosAgents → aiGraph → peerExecution).
  • buildCatalogDdl() → phase-2 list (catalog → media → catalogUserTypes → universes → library → pipeline → writersRoom → lora → privacy → stackerNews → x → beeper → githubNotifications → audit DDL → audit triggers → syncFeed DDL → sync-feed triggers).

ensureSchemaImpl() calls these two builders and runs each list through pool.query in order.

| mindToolRecipes.js | mindToolRecipesDdl: machine-local recipe definitions and immutable revisions. | | decks.js | decksDdl: machine-local deck design projects (decks) and their per-card rows (deck_cards). |