Change the knowledge schema

Triggers

• Changing tables, columns, CHECKs, indexes, views, or triggers in db/schema.sql
• Changing connection roles (the authorizers in server/src/sqlite.ts and server/src/db-write.ts)
• Changing the full-text search index (FTS5, sphica_terms, terms() / queryTerms() in server/src/text.ts, server/src/search.ts)
• Changing the vocabulary in server/src/knowledge.ts (unit kinds, stances, lifecycles, option outcomes, evidence roles, source kinds)
• Adding an ingestion source, or changing how capture, trace, harvest, or glean write

Does not trigger

• Work that only creates a DB
• Changing only the text of a Skill or of MCP replies (plugin-release)

Source of truth and versions

The DB is a single node:sqlite file (~/.sphica/sphica.db). The only source of truth is db/schema.sql. Do not add an ORM schema as a second source (Drizzle was rejected: it cannot express FTS5 virtual tables and triggers). sphica init applies schema.sql to a temporary file and renames it.

Two numbers version it:

• Generation (sphica_generation, SCHEMA_GENERATION in server/src/sqlite.ts): the record model. 0.5.0 is generation 2. A database of another generation is refused without being changed; the owner moves it aside and runs sphica init (0.4 records are not carried over)
• Revision (pragma user_version at the end of schema.sql, SCHEMA_REVISION): changes within a generation. Keep the two equal; the reader and ingest connections stop on a mismatch. Capture checks only the generation, so recording keeps working between a DB change and a plugin update

A revision change ships with a migration. db/migrations/<revision>.sql (4 digits, 0002.sql moves 1 → 2) holds the same statements as schema.sql's new state; sphica init runs each in one transaction with foreign keys off and checks foreign_key_check before committing (migrate() in server/src/admin.ts). Rebuilding a table that other triggers name needs those triggers dropped first, or the rename fails. server/test/migrate.test.ts compares a migrated DB with a fresh one; keep the previous revision's schema in server/test/fixtures/. Keep the capture views' columns unchanged across a revision, since capture writes across the change

Rows the new revision refuses are handled in three ways, and none is left to fail the rebuild:

• Repaired: take the nearest value the new rules accept, or the row goes when it is derived (a duplicate, an observation). Write each into the temp table sphica_migration_note (rule, item, action); migrate() prints every row after the step commits. A lifecycle repair adds a state from a run with origin migration (target revision:<n>) and sets unit.lifecycle and revision by hand, since the triggers are dropped during the rebuild
• Stopped: a row no release wrote, or a source the migration may not remove (only the owner's forget removes sources). <revision>.check.sql runs first in the same transaction and fills sphica_migration_stop (rule, item); any row stops the step with every row listed and nothing changed
• Rebuilt tables lose their sqlite_sequence row: save the counters first and put back the larger of old and copied, before anything inserts into the table

When changing the schema

1. Regenerate server/src/db-types.ts with bun run codegen. Do not edit it by hand. CI's codegen:check fails on drift
2. Make every table strict, and write not null on every primary key
3. Give time columns check (strftime('%Y-%m-%dT%H:%M:%fZ', column) is column) (with =, an invalid string passes as NULL). Writers use iso() in server/src/db.ts
4. Enforce cross-table consistency (same project, spans inside the source) with triggers, not only in code; server/test/schema.test.ts tries each refusal on a real DB
5. A value set lives in a CHECK and in server/src/knowledge.ts; scripts/check-pairs.mjs compares them. Add a pair there when you add a set
6. Lead every foreign key with an index on its own columns (schema.test.ts checks it). A row goes with what it belongs to or with a source it cites (cascade); a column naming where a row came from (run_id, forget_id, edit_observation_id) takes no action
7. Keep one path rule: source.path, edit_observation.path, and unit_anchor.path share one CHECK expression (bun run pairs compares them)

The record model

Four boundaries (the header of schema.sql):

• State is judged from facts. Writes (saves, glean, forget, migration) change only facts: intents (unit_link supersedes, one per record), evidence, adoption, explicit withdrawals. Then reconcile() in server/src/reconcile.ts loads every record the change reaches through supersedes, runs the pure judge() in server/src/judge.ts, and writes only the difference: ended replacement rows, opened ones, then unit_state rows. It judges again and throws if anything is left over. It is the only writer of unit_state, unit_replacement, and unit_answer (bun run architecture checks it); triggers only check that what was written follows the rules
• A question an answer closed keeps the record that answered. When a trace's answers withdraws a question, reconcile() writes a unit_answer row (the withdrawal's state row, the answering record) with that state row, also for an older question closed through the one named. A withdrawal the owner asked for has none. A reader tells who answered from this row (server/src/timeline.ts), never from the reason's words; withdrawals from before revision 13 got their row from the migration, when one record of their save had the key
• Lifecycle moves through unit_state along a fixed table: the first state is candidate; candidate → active, superseded, or withdrawn; active → candidate, superseded, or withdrawn; superseded → active or candidate; withdrawn is final. Its trigger sets unit.lifecycle
• Support is one view, unit_support: a decision or constraint needs unit-level unretracted evidence and adoption (an agent adoption counts only with its paired decides evidence); an implementation needs code or commit evidence (or an evidence anchor on a path its session edited); a finding, dead end, or question needs unit-level evidence. Activating, retracting, and retiring an anchor all read it, so they never disagree. Quarantined and unsourced units never become active
• Replacement: unit_link supersedes is the intent; unit_replacement holds when a replacement was in effect (start, end, and the run or forget that caused each; a row ends once and is never deleted). At most one open row per replaced record: its holder is the successor in effect. A successor holds the place while it is eligible (not withdrawn, supported, sourced, and for a decision or constraint with an intent, adopted by the owner or a maintainer); when the place is free, the earliest intent takes it. A record superseded before revision 10 whose history was not recorded carries a mark in unit_replacement_gap, so read does not show it as a proposal never carried out
• Evidence is a byte span of retained text (span_start, span_end into the UTF-8 bytes of source.text). Quotes are located by the save path, never trusted
• Adoption routes: owner_statement (an owner-kind source), explicit (the owner, or OWNER / MEMBER / COLLABORATOR), or agent: the AI's own reply in a session, paired with decides evidence on the same span, saved by a run whose begin call was interactive, from a reply with a turn that ran no record tool (the view agent_ineligible_source). The unit_adoption_route trigger enforces this; agentRefusal in record.ts also checks AskUserQuestion questions, rule and CI file anchors, and a same-turn edit for a do with applies_to anchors. A merge or a resolved thread never adopts. authorityOf in server/src/authority.ts says whose decision a record is as of any time: the owner's, an AI's, or no one's
• Aliases are search words bound to the unit's content_hash; only the newest matching set is indexed. They are never evidence
• Anchors hold a path, symbol, and the lines where the symbol was when saved. They are checked against the working tree when read (server/src/anchors.ts), never cached
• Runs bind writes. extraction_run.draft_id is the run id the record server's begin tools issue; check and save take it, and the record never names a project or target
• Record tool calls are logged. The record server logs every tool call in record_call before it runs (host, caller session and turn, mode read by callerOf in server/src/caller.ts), and a run keeps its begin call. Claude Code puts no turn in a call, so a synchronous PreToolUse hook writes tool_call_observation through the capture view capture_tool_call, joined by tool use id. The hook first keeps the same observation in spool/calls/ and removes it once written; one it could not write (the lock held past its wait) is written by the next send, which lifts the stop on later replies but never on the calling turn's. A call with no hook row, no tool use id, or before install stays unjoined for good: nothing guesses its turn from sessions or times

Writers

A new ingestion source writes through the record server's run-bound tools or capture, never a bulk import.

Status edits and turn starts

Capture writes via: "status" edits from git status at a turn's start and end (openTurn and closeTurn in server/src/capture.ts). Hooks of different turns can run out of order, so the starts follow these rules. Keep them when changing capture:

• Each turn's start is its own file under ~/.sphica/worktree/<session>/, numbered seq. No hook rewrites or deletes another turn's start
• A Stop gives status edits to its turn only if, read again after its end snapshot, that turn is the single newest one and its own start is unchanged. A tie, a null seq, or an unreadable file drops them: a lost edit is better than one given to the wrong turn
• A Stop leaves its end snapshot, so another Stop with no prompt in between (a Stop hook kept the turn going) compares from there. A prompt that reuses the id takes a new snapshot first. An Interrupt, or a Stop whose start has no snapshot, ends the turn and keeps only the number
• SessionStart writes no start. Prune removes a session's directory only when every file in it is older than HOLD_DAYS, never an empty one or single files
• The owner's prompt is queued before the start is taken, and a failed start never stops the messages, the send, or the session notice

Gaps left open: a turn started by a notice can reuse an interrupted turn's id (nothing the hooks receive tells an interrupt apart); edits made before the end snapshot runs; a start that could not be written; a hook still running past its timeout; a session resumed after HOLD_DAYS while its old starts are being pruned.

Connection roles

Processes of the same OS user can rewrite the file directly, so this is not an OS boundary. It guards the path where Sphica's code writes by mistake.

• Write connections live only in server/src/db-write.ts; bun run architecture checks the read MCP server cannot reach them
• Enable enableDefensive(true) on every connection (it stops direct writes to FTS5 shadow tables)
• Initialization order is fixed: open → defensive and pragmas → sphica_terms → authorizer
• Before adding a function to READER_FUNCTIONS because a read fails with not authorized, see whether the code can compute it instead (the read-delivery budget sums chars in JS rather than widening the reader)
• Do not count rows by affected rows through a view (an insert into a view reports 0). Count by what exists before and after
• Judge permissions by running them: server/test/db.test.ts tries each role's allowed and forbidden operations on real connections. A trigger function the authorizer denies fails only at run time (the delivery log once failed this way unnoticed)

How to write SQL

Write queries with kysely and let it infer types. openReader() returns kysely's ReadonlyKysely<DB>, and a function that only reads takes Reads (in db.ts), which both the reader and a writer fit and which cannot write. Only sqlite.ts, db-write.ts, db.ts, admin.ts, and kysely-node-sqlite.ts may use node:sqlite directly (bun run sql). Name a node:sqlite connection raw, and keep raw.prepare( on one line (the SQL ledger finds call sites by line).

Full-text search

unit_fts (columns body, ident, alias; filled from the view unit_search_text) and source_fts (owner words and third-party text; assistant replies are not indexed). Both are contentless and filled by triggers calling sphica_terms, which is terms() in server/src/text.ts.

• terms() splits with Intl.Segmenter, drops hiragana-only words and English stop words, keeps identifiers whole, keeps a kanji word's kanji (conjugations meet), and makes English plurals singular. The index and queries go through the same function
• Changing terms() leaves existing indexes old. Raise the revision and end its migration with the statements reindex() in admin.ts runs, so sphica init rebuilds both indexes. server/test/terms-golden.test.ts pins the output for fixed inputs in server/src/terms-golden.json; it cannot stop only its expected values being updated. The file ships, and sphica doctor checks the running Node splits the same inputs the same way (an ICU difference)
• queryTerms() drops question framing (why, which, and their Japanese counterparts) from queries only. Search keeps a candidate only when it holds more than half of the question's content terms, and reports how many weaker matches it left out, so a question with no answer returns nothing
• Always quote query terms (ftsQuery); unquoted, AND, NEAR, :, and - become operators

Verification

• Tests run SQL on a real SQLite database in a temporary directory (server/test/temp-db.ts). Do not touch ~/.sphica
• bun run verify includes sql:reach (tests ran every SQL call site in server/src, except LIVE_FILES), sql:live (the CLI and capture as child processes), and the acceptance cases
• The acceptance cases are the contract. server/evals/acceptance/cases.json holds the bilingual cases over capture, status, retrieval, injection, review, and glean (server/test/acceptance-cases.test.ts fixes the count per layer); run.ts plays them through driver.ts. For a new behavior, add a case first and confirm it fails for the intended reason