Garry's Opinionated OpenClaw/Hermes Agent Brain
Schema map
This map lists every table, column, index, view, function, trigger and migration in the gbrain database, with the code that reads and writes each object.
- Reference:
garrytan/gbrainat commit8bbfd0b5e8(master, 2026-11-16). Everypath:linecitation is relative to the gbrain repo root at that commit. - Fresh-install DDL:
src/schema.sql(3,794 lines). It is hand-edited, except theBEGIN/END GENERATEDregions, whichscripts/build-schema.tscopies from TS fragment modules. The same script builds the two engine blobs:src/core/schema-embedded.generated.ts(Postgres) andsrc/core/pglite-schema.generated.ts(PGLite). - Migrations: 313 files,
src/core/schema-migrations/v002-*.tsthroughv318-*.ts(no v017-v019, no v100), run bysrc/core/migrate.ts. The schema version is theconfig.versionrow. - Method: four research passes, split by line range and migration range. Reader and writer lists come from a grep of
src/for SQL keywords next to each table name. They name the main files, not every call site.
Contents
| Section | Scope | Count |
|---|---|---|
| Tables and objects, part 1 | schema.sql 1-1898 |
56 tables, 114 indexes, 8 functions, 7 triggers, 1 sequence, 3 extensions |
| Tables and objects, part 2 | schema.sql 1899-3794 |
76 tables, 2 sequences, 13 functions, 17 triggers (28 attachments), 32 DO blocks, 19 generated regions |
| Migration system and migrations v002-v160 | runner, blobs, v002-v160, runtime DDL | 155 migrations |
| Migrations v161-v318 | v161-v318 | 158 migrations |
Totals: 132 tables in schema.sql. There are no views, CREATE POLICY, GRANT, REVOKE or SECURITY DEFINER statements. RLS is turned on for about 37 tables by a DO block only when the connected role has BYPASSRLS; authorization is in application code and in GUC-based trigger guards.
Headline findings
- Most correctness rules live in the database, not in TypeScript: about 25 plpgsql trigger functions maintain derived state (search vectors, revision counters, projection queues, generation clocks) and refuse writes with typed messages (
writer_coordinator_required,git_protocol_required). A Rust engine must port each trigger as an explicit write-path step, with tests that compare it to the trigger. - About 20 columns are added only by migrations or referenced only inside trigger bodies. A fresh Rust schema must declare them to match an upgraded brain (listed in part 1).
- Many functions and triggers are redefined several times across migrations (for example
gbrain_access_origin_restrictedin v282, v283, v285 and v305). A fresh Rust schema needs only the final definitions. - About 17 migrations in v161-v318 (and more in v002-v160) backfill data. Those matter only if the Rust engine imports an existing PGLite or Postgres brain in place; an export-and-import path avoids them.
fileshasUNIQUE(storage_path), whilegetFilelooks up by(source_id, storage_path). Some unique keys useNULLS NOT DISTINCT, which SQLite does not have.
Tables and objects, part 1 (schema.sql lines 1-1898)
Source: src/schema.sql lines 1-1898. Part 2 starts at line 1899 (CREATE TABLE IF NOT EXISTS page_write_guards). Citations are schema.sql:line. Reader/writer paths are relative to src/. Engine SQL lives in core/engine-sql/*.ts; the engines are core/postgres-engine.ts and core/pglite-engine.ts. Migrations are core/schema-migrations/vNNN-*.ts and were excluded from the reader/writer lists below (they are listed separately where they matter).
Scope totals
| Object kind | Count in range | Where |
|---|---|---|
| Extensions | 3 (vector, pg_trgm, pgcrypto) |
3, 4, 6 |
Tables (CREATE TABLE IF NOT EXISTS) |
56 | 26 to 1772 |
Indexes (CREATE [UNIQUE] INDEX) |
114 (incl. partial, GIN, HNSW, trigram, expression, unique-expression) | 88 to 1784 |
| Functions | 8 CREATE OR REPLACE FUNCTION; 1 DROP FUNCTION IF EXISTS (1243) |
64, 182, 267, 313, 432, 444, 1196, 1789, 1813 |
| Triggers | 7 CREATE TRIGGER (+ DROP TRIGGER IF EXISTS before each) |
209, 275, 332, 455, 1231, 1809, 1824 |
| Sequences | 1 (page_generation_clock_seq) |
255 |
| Views / materialized views | 0 | none |
CREATE POLICY / GRANT / COMMENT ON |
0 / 0 / 0 | none |
Seed INSERTs |
3 (sources default 78, page_generation_clock 246, config 725) |
|
DO $$ blocks |
3 (RLS enablement 1834; sources.incarnation 1867; pages.knowledge_revision 1880) |
|
| RLS enablement | Conditional ENABLE ROW LEVEL SECURITY on 35 named tables, only if the current role has BYPASSRLS or superuser (1834-1865). No policies are created in this range. |
1834 |
Columns added by ALTER TABLE ... ADD COLUMN IF NOT EXISTS in range: files drop of storage_url (1153); pages.search_vector (1183); minion_attachments.content SET STORAGE EXTERNAL (1351); gbrain_cycle_locks.acquisition_token (1530, inside a GENERATED block); sources.incarnation (1867-1879); pages.knowledge_revision and pages.text_projection_revision, page_versions.knowledge_revision/timeline/title/type/tags/is_deleted (1880-1898).
Out-of-range ALTERs that belong to these tables (for part 2 readers): page_versions.source_path (about schema.sql:1966), sources.upstream_checked_at, upstream_commit, upstream_behind (about schema.sql:3559-3561).
Migration-added columns the fresh DDL in range does not declare
Several columns are only mentioned in comments in lines 1-1898 and are added by migrations on every install. Their ordinals are shared with upgraded brains, so a Rust fresh-install must declare them explicitly to match.
| Table | Columns (comment only, not in CREATE TABLE) | Evidence |
|---|---|---|
pages |
body_storage, body_bytes, truth_bytes, body_lines, body_generation, segment_count, segmenter_version, body_flags, truth_lead, timeline_lead, links_attendance_blocked_revision/_at; compiled_truth and timeline become NULL-able; pages_body_storage_chk |
comment 173-176; body_generation is read by bump_page_generation_fn at 199 |
content_chunks |
occurrence_id, local_ordinal, raw_byte_start/end, body_generation, retired_generation, embedding_pending_since, projected_byte_start/end |
comments 370-376 (retired_generation is read by released readers but written by nothing) |
page_versions |
body_storage, body_generation, body_bytes, content_hash; compiled_truth NULL-able; page_versions_body_chk |
comment 685-687 (v248, v254 and others) |
minion_jobs |
spend_authorization, spend_claim_token |
trigger reads them at 1799; added by core/schema-migrations/v205-minion-spend-authorization.ts |
ingest_log |
writer_version, writer_host_id, writer_segment_reader |
declared inline at 706-708 (v254), so these ARE in range |
Global objects
Extensions (schema.sql:3-6)
vector(pgvector):vector(1536)andvector(1024)columns, HNSW indexes withvector_cosine_ops.pg_trgm:gin_trgm_opsonpages.titleandpages.slug.pgcrypto: fallback forgen_random_uuid()(also core in PG13+).
Functions (all in range)
| Function | Lines | Kind / attributes | Purpose |
|---|---|---|---|
gbrain_try_jsonb(text) -> jsonb |
64-72 | plpgsql IMMUTABLE STRICT; catches data_exception and returns NULL |
Safe text-to-jsonb cast for double-encoded config/payload (#7112, v263). |
bump_page_generation_fn() |
182-205 | trigger fn, plpgsql | On INSERT: generation = MAX(generation)+1. On UPDATE: generation = OLD+1 when any of compiled_truth, timeline, frontmatter, deleted_at, contextual_retrieval_mode, title, type, page_kind, corpus_generation or content_hash changes (IS DISTINCT FROM). Also bumps when body_generation changes and compiled_truth is NULL on both sides. |
bump_page_generation_clock_fn() |
267-272 | trigger fn, plpgsql | PERFORM nextval('page_generation_clock_seq'); returns NULL (statement trigger). |
gbrain_forget_page_retrievals() |
313-330 | trigger fn, plpgsql | On DELETE from pages (statement level): DELETE FROM page_retrievals USING gbrain_deleted_pages (transition table). Raises writer_coordinator_required (P0001) if RLS is active and the role cannot read the needed pg_policy rows for r and d. |
gbrain_fts_input(t text) -> text |
432-436 | SQL IMMUTABLE STRICT PARALLEL SAFE | Inserts a space at CJK/Kana/Hangul vs ASCII-alnum boundaries (two regexp_replace passes) before tsvector. No-op on non-CJK text (#6370). |
update_chunk_search_vector() |
444-452 | trigger fn | `search_vector = setweight(to_tsvector('english', gbrain_fts_input(doc_comment)),'A') |
update_page_search_vector() |
1196-1228 | trigger fn, plpgsql | On UPDATE, raises writer_coordinator_required if timeline_entries is RLS-protected and the role cannot read it. Builds pages.search_vector: title at weight A; COALESCE(NEW.timeline, timeline_lead) capped at 65536 chars at weight C; joined timeline_entries (summary |
enforce_minion_queue_protocol() |
1789-1807 | trigger fn, plpgsql | Protocol gate for minion_jobs. INSERT requires submission_authority IS NOT NULL and claim_generation = 0. A transition to active (or a lock_token change) requires claim_generation = OLD+1 and, if spend_authorization is set, spend_claim_token = claim_generation. Any other claim_generation change raises. |
notify_minion_job_change() |
1813-1821 | trigger fn, plpgsql | pg_notify('minion_jobs', json{id,status,name,queue,prev_status}). Comment at 1819 says Postgres only, not PGLite. |
Also in range: DROP FUNCTION IF EXISTS update_page_search_vector_from_timeline() (1243) removes an old function. ts_filter(tsvector, '{a}') at 1187 is a Postgres built-in (tsvector filter, PG 14+); it is not defined in this repo.
Triggers (all in range)
| Trigger | On | Timing / scope | Fn | Lines |
|---|---|---|---|---|
bump_page_generation_trg |
pages |
BEFORE INSERT OR UPDATE, FOR EACH ROW | bump_page_generation_fn |
209-212 |
bump_page_generation_clock_trg |
pages |
AFTER INSERT OR UPDATE OR DELETE, FOR EACH STATEMENT | bump_page_generation_clock_fn |
275-278 |
pages_forget_retrievals |
pages |
AFTER DELETE, REFERENCING OLD TABLE AS gbrain_deleted_pages, FOR EACH STATEMENT |
gbrain_forget_page_retrievals |
332-334 |
chunk_search_vector_trigger |
content_chunks |
BEFORE INSERT OR UPDATE OF chunk_text, doc_comment, symbol_name_qualified, FOR EACH ROW | update_chunk_search_vector |
455-457 |
trg_pages_search_vector |
pages |
BEFORE INSERT OR UPDATE OF title, timeline, FOR EACH ROW | update_page_search_vector |
1231-1233 |
minion_queue_protocol |
minion_jobs |
BEFORE INSERT OR UPDATE, FOR EACH ROW | enforce_minion_queue_protocol |
1809-1810 |
minion_job_notify |
minion_jobs |
AFTER INSERT OR UPDATE OF status, FOR EACH ROW | notify_minion_job_change |
1824-1825 |
Dropped in range: trg_timeline_search_vector on timeline_entries (1242) and its function (1243).
Sequence and clock (schema.sql:242-265)
page_generation_clocktable (242-245): single rowid=1 CHECK (id=1),value BIGINT DEFAULT 0. Seeded fromMAX(pages.generation)(246-248). Kept for compatibility; the live clock is the sequence.page_generation_clock_seq(255): created withCREATE SEQUENCE IF NOT EXISTS. Re-runnable seed at 260-265:setval(seq, GREATEST(1, seq.last_value, clock.value, MAX(pages.generation))). Comment says the 2-arg setval makes the first write strictly exceed the seed, and the GREATEST prevents a replay from movinglast_valuebackward (stale query_cache bookmark risk).
Row-level and statement-level bump contract (for Rust parity)
pages.generation(per-row, Layer 2) is written bybump_page_generation_fn.page_generation_clock_seq(global, Layer 1) is advanced bynextvalin a statement trigger on every INSERT/UPDATE/DELETE statement onpages. Readers readlast_value(seecore/health-memo.ts:83).
Sources and tenancy
sources (schema.sql:26-90)
Purpose: logical brain-within-the-DB registry. Every page/file/ingest row carries source_id. The default row is seeded (78-82) with config={"federated": true}.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | TEXT | PK, NOT NULL | Immutable citation key [a-z0-9-]{1,32} (app-enforced) |
| name | TEXT | NOT NULL, UNIQUE | Mutable display label |
| local_path | TEXT | NULL | Git checkout root for filesystem sources |
| last_commit | TEXT | NULL | |
| last_sync_at | TIMESTAMPTZ | NULL | |
| config | JSONB | NOT NULL DEFAULT '{}' | federated, access_policy, github_repo |
| chunker_version | TEXT | NULL | Forces full walk when mismatched (v0.20) |
| archived | BOOLEAN | NOT NULL DEFAULT false | Soft-delete |
| archived_at | TIMESTAMPTZ | NULL | |
| archive_expires_at | TIMESTAMPTZ | NULL | Purge when <= now() |
| contextual_retrieval_mode | TEXT | NULL | Per-source override; NULL = global |
| trust_frontmatter_overrides | BOOLEAN | NOT NULL DEFAULT false | Mount trust gate |
| newest_content_at | TIMESTAMPTZ | NULL | Newest HEAD commit time at last sync (v0.41.32) |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| incarnation | UUID | NOT NULL DEFAULT gen_random_uuid() | Added by DO block 1867-1878, not inline; unique index 1879 |
Indexes:
sources_github_repo_idx(88-90): btree on(config->>'github_repo')WHEREconfig ? 'github_repo'. Partial expression index. The?(JSONB key-exists) operator is used in the predicate.sources_incarnation_keyUNIQUE (incarnation) (1879).- PK and UNIQUE(name) are implicit.
Functions used by sources: gbrain_try_jsonb (64) is defined here and used by source-config SQL.
Readers/writers:
- Writers:
core/sources-ops.ts,core/persistence/source-lifecycle.ts,core/persistence/managed-sync-orphans.ts,core/engine-sql/sources.ts. - Readers:
core/source-resolver.ts,commands/sources.ts,core/sync-anchor.ts,core/source-config-sql.ts. - Engines use the shared
core/engine-sql/*for both PGLite and Postgres (core/postgres-engine.ts,core/pglite-engine.ts).
Pages and content
pages (schema.sql:99-206)
Purpose: core content table. One row per page, unique by (source_id, slug). Holds compiled truth, timeline text, frontmatter, and the soft-delete, salience, retrieval and extraction watermarks.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| source_id | TEXT | NOT NULL DEFAULT 'default' | FK sources(id) ON DELETE CASCADE |
| slug | TEXT | NOT NULL | |
| type | TEXT | NOT NULL | Page type (also drives event projections) |
| page_kind | TEXT | NOT NULL DEFAULT 'markdown' | CHECK in (markdown,code,image) |
| title | TEXT | NOT NULL | |
| compiled_truth | TEXT | NOT NULL DEFAULT '' | Becomes NULL-able in v248 (migration) |
| timeline | TEXT | NOT NULL DEFAULT '' | Becomes NULL-able in v248 (migration) |
| frontmatter | JSONB | NOT NULL DEFAULT '{}' | |
| content_hash | TEXT | NULL | |
| emotional_weight | REAL | NOT NULL DEFAULT 0.0 | Deterministic 0..1 salience |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| deleted_at | TIMESTAMPTZ | NULL | Soft delete; purge after 72h |
| effective_date | TIMESTAMPTZ | NULL | Salience/recency |
| effective_date_source | TEXT | NULL | 'event_date' / 'date' / 'published' / 'filename' / 'fallback' |
| import_filename | TEXT | NULL | |
| salience_touched_at | TIMESTAMPTZ | NULL | |
| last_retrieved_at | TIMESTAMPTZ | NULL | Legacy; readers take GREATEST with page_retrievals |
| links_extracted_at | TIMESTAMPTZ | NULL | Extraction watermark (v112) |
| database_only_reason | TEXT | NULL | 'unbound_source' marks DB-only pages (v177) |
| contextual_retrieval_mode | TEXT | NULL | Tier last embedded under |
| corpus_generation | TEXT | NULL | Composite hash for cache invalidation |
| generation | BIGINT | NOT NULL DEFAULT 1 | Per-row counter, see trigger |
| search_vector | TSVECTOR | NULL | ALTER TABLE ADD COLUMN IF NOT EXISTS at 1183 |
| knowledge_revision | UUID | DEFAULT gen_random_uuid(); NOT NULL only when table empty | DO block 1880-1891 |
| text_projection_revision | UUID | NULL | 1892 |
Constraints: CONSTRAINT pages_source_slug_key UNIQUE (source_id, slug) (line 178). page_kind CHECK as above.
Indexes:
pages_generation_idx(219): btree (generation).idx_pages_type(280): btree (type).idx_pages_frontmatter(281): GIN (frontmatter).idx_pages_trgm(282): GIN (title gin_trgm_ops).idx_pages_slug_basename(283): btree (source_id,regexp_replace(slug,'^.*/','')). Expression index for basename lookup.idx_pages_source_title_lower(285): btree (source_id,lower(title)) (v240).idx_pages_slug_trgm(286): GIN (slug gin_trgm_ops).idx_pages_updated_at_desc(288): btree (updated_at DESC).idx_pages_source_id(290): btree (source_id).pages_deleted_at_purge_idx(296): partial btree (deleted_at) WHEREdeleted_at IS NOT NULL.pages_links_extracted_at_idx(342): btree (source_id,links_extracted_at). No partial predicate (NULL and<arms are covered).pages_coalesce_date_idx(348): expression btree onCOALESCE(effective_date, updated_at).idx_pages_search(1185): GIN (search_vector).idx_pages_title_search(1187): GIN (ts_filter(search_vector,'{a}')). Title-only slice (v267).
Triggers: bump_page_generation_trg (209), bump_page_generation_clock_trg (275), pages_forget_retrievals (332, AFTER DELETE), trg_pages_search_vector (1231).
Readers/writers:
- Main engine SQL:
core/engine-sql/pages.ts(45 SQL-keyword hits),core/engine-sql/links.ts(joins),core/engine-sql/chunks.ts,core/engine-sql/bootstrap.ts. - Other readers:
core/search/read-enrichment.ts,core/facts/purge-verify.ts,core/page-state/*. - Engines:
core/postgres-engine.ts,core/pglite-engine.ts(both delegate to engine-sql).
page_generation_clock (schema.sql:242-248)
Purpose: legacy single-row global clock. Kept for upgrade compatibility; the live clock is page_generation_clock_seq.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | INTEGER | PK, CHECK (id = 1) | |
| value | BIGINT | NOT NULL DEFAULT 0 | Seeded from MAX(pages.generation) |
Indexes: none beyond PK.
Readers/writers: no live writer in range. Only core/schema-migrations/v107-*.ts and v118-*.ts touch it. Live readers use the sequence (core/health-memo.ts:83, last_value).
page_retrievals (schema.sql:308-334)
Purpose: retrieval telemetry, split out of pages so the per-read upsert is a HOT update of a narrow row (not an UPDATE of pages, which would advance the generation clock). Written at most once per page per 5 minutes.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| page_id | INTEGER | PK | No FK (comment at 300-307: KEY SHARE lock would dirty pages) |
| last_retrieved_at | TIMESTAMPTZ | NOT NULL |
Indexes: PK only.
Triggers: pages_forget_retrievals (331-334) removes rows whose page_id was hard-deleted (statement trigger on DELETE).
Readers/writers:
- Writer:
core/last-retrieved.ts. - Readers:
core/engine-sql/pages.ts(2 hits),core/verbs/entity-card.ts. - Trigger SQL source:
core/derived-state-maintainers.ts(GENERATED block at 310-312).
page_versions (schema.sql:679-691)
Purpose: snapshot history for compiled truth and frontmatter (v248 adds segmented snapshots that reference a generation, not a copy).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE |
| compiled_truth | TEXT | NOT NULL | NULL-able after v248 (migration) |
| frontmatter | JSONB | NOT NULL DEFAULT '{}' | |
| snapshot_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| knowledge_revision | UUID | NULL | ALTER at 1893 |
| timeline | TEXT | NULL | ALTER at 1894 |
| title | TEXT | NULL | ALTER at 1895 |
| type | TEXT | NULL | ALTER at 1896 |
| tags | JSONB | NULL | ALTER at 1897 |
| is_deleted | BOOLEAN | NULL | ALTER at 1898 |
Indexes: idx_versions_page (690): btree (page_id).
Readers/writers: writers and readers are core/page-state/versions.ts (main), core/page-state/schema.ts, core/engine-sql/pages.ts, core/facts/purge.ts. Migration core/schema-migrations/v248-segmented-page-storage.ts also writes.
raw_data (schema.sql:631-642)
Purpose: sidecar JSON per page and source (replaces .raw/ files).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE |
| source | TEXT | NOT NULL | Sidecar kind |
| data | JSONB | NOT NULL | |
| fetched_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: UNIQUE (page_id, source). Indexes: idx_raw_data_page (640) btree (page_id).
Readers/writers: core/postgres-engine.ts (8 hits), core/pglite-engine.ts (3), core/repair/secret-spans.ts, core/persistence/twin-merge.ts, commands/export.ts.
tags (schema.sql:616-627)
Purpose: per-page tags.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE |
| tag | TEXT | NOT NULL | |
| tag_source | TEXT | NULL | 'frontmatter' (import-owned, deleted when it leaves frontmatter), 'added', or NULL legacy |
Constraints: UNIQUE (page_id, tag). Indexes: idx_tags_tag (625) btree (tag); idx_tags_page_id (626) btree (page_id).
Readers/writers: core/page-state/tags.ts (main), core/engine-sql/salience.ts, core/persistence/twin-merge.ts, core/repair/segmented-tiling.ts.
timeline_entries (schema.sql:645-674)
Purpose: structured timeline. Also the date-index projection for type:event pages (Life Chronicle).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE (the depth/meeting page) |
| date | DATE | NOT NULL | |
| source | TEXT | NOT NULL DEFAULT '' | Meeting provenance; part of dedup key |
| summary | TEXT | NOT NULL | |
| detail | TEXT | NOT NULL DEFAULT '' | |
| event_page_id | INTEGER | NULL | FK pages(id) ON DELETE CASCADE; set for event projections |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes:
idx_timeline_page(662): btree (page_id).idx_timeline_date(663): btree (date).idx_timeline_dedup(670): UNIQUE btree (page_id,date,md5(summary),source). Keyed on md5 to avoid the btree row-size cap (#3737). Used forON CONFLICTinference at both insert sites.idx_timeline_event_page(673): partial btree (event_page_id) WHEREevent_page_id IS NOT NULL.idx_timeline_event_dedup(674): partial UNIQUE (event_page_id,date) WHEREevent_page_id IS NOT NULL.
Triggers: none in range (the timeline_entries search trigger was removed at 1242).
Readers/writers: core/engine-sql/timeline.ts (main, 8 hits), core/timeline-dedup-repair.ts, core/repair/timeline.ts, core/persistence/twin-merge.ts, core/timeline-extract.ts. update_page_search_vector reads it (1196-1228).
files (schema.sql:1136-1166)
Purpose: binary attachments (originally Supabase Storage).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| source_id | TEXT | NOT NULL DEFAULT 'default' | FK sources(id) ON DELETE CASCADE |
| page_slug | TEXT | NULL | Legacy, FK dropped (no ON UPDATE CASCADE) |
| page_id | INTEGER | NULL | FK pages(id) ON DELETE SET NULL |
| filename | TEXT | NOT NULL | |
| storage_path | TEXT | NOT NULL, UNIQUE | storage_url dropped at 1153 |
| mime_type | TEXT | NULL | |
| size_bytes | BIGINT | NULL | |
| content_hash | TEXT | NOT NULL | |
| metadata | JSONB | NOT NULL DEFAULT '{}' | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: idx_files_page (1155) btree (page_slug); idx_files_page_id (1156); idx_files_source_id (1157); idx_files_hash (1158) btree (content_hash). Plus UNIQUE(storage_path).
Readers/writers: core/ops/files.ts (10 hits), commands/files.ts (9), core/engine-sql/files.ts. Backfill and migration: core/schema-migrations/v023-*.ts, v039-*.ts.
file_migration_ledger (schema.sql:1168-1181)
Purpose: state machine for the v0.18.0 storage-object rewrite. Keyed by file_id so two sources can share an old path.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| file_id | INTEGER | PK | FK files(id) ON DELETE CASCADE |
| storage_path_old | TEXT | NOT NULL | |
| storage_path_new | TEXT | NOT NULL | |
| status | TEXT | NOT NULL DEFAULT 'pending' | CHECK in (pending,copy_done,db_updated,complete,failed) |
| error | TEXT | NULL | |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: idx_file_migration_ledger_status (1177) partial btree (status) WHERE status != 'complete'.
Readers/writers: commands/migrations/v0_18_0-storage-backfill.ts (5 hits), core/schema-migrations/v023-files-source-id-page-id-ledger.ts.
ingest_log (schema.sql:700-718)
Purpose: ingest/facts-absorb audit log. source_id scopes failure counts per source.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| source_id | TEXT | NOT NULL DEFAULT 'default' | (no FK in DDL) |
| source_type | TEXT | NOT NULL | |
| source_ref | TEXT | NOT NULL | |
| pages_updated | JSONB | NOT NULL DEFAULT '[]' | |
| summary | TEXT | NOT NULL DEFAULT '' | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| writer_version | TEXT | NULL | v254 |
| writer_host_id | UUID | NULL | v254 |
| writer_segment_reader | BOOLEAN | NULL | v254; NULL from older binaries |
Indexes: idx_ingest_log_source_type_created (714) btree (source_id, source_type, created_at DESC).
Readers/writers: core/postgres-engine.ts (2 hits), core/pglite-engine.ts (3), core/persistence/writer-versions.ts, core/facts/absorb-log.ts. Migration core/schema-migrations/v050-ingest-log-source-id.ts and v254-segment-reader-marks.ts.
config (schema.sql:720-732)
Purpose: brain-level key/value settings.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| key | TEXT | PK | |
| value | TEXT | NOT NULL |
Seed (725-731, ON CONFLICT DO NOTHING): version='1', embedding_model='text-embedding-3-large', embedding_dimensions='1536', chunk_strategy='semantic'.
Indexes: PK only.
Readers/writers: core/postgres-engine.ts (5), core/pglite-engine.ts (5), core/persistence/limits.ts, core/repair/content-receipt-root.ts, core/cycle/dream-breaker.ts, core/backup/quarantine.ts. Broadest-used table in this set (104 files, mostly migration-driven).
Pages domain: parity note
pages plus its triggers is the Postgres-specific core of the generation and search system: nextval sequence, plpgsql triggers, statement-level transition tables (REFERENCING OLD TABLE), ts_filter, tsvector weights and GIN. See the parity list at the end of this file.
Chunks and embeddings
content_chunks (schema.sql:354-427)
Purpose: chunked page content with vector embeddings (text, image, multimodal) and code-chunk metadata. Chunks are re-INSERTed on page change; there is no updated_at.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE |
| chunk_index | INTEGER | NOT NULL | Unique per page (see index) |
| chunk_text | TEXT | NOT NULL | |
| chunk_source | TEXT | NOT NULL DEFAULT 'compiled_truth' | |
| embedding | vector(1536) | NULL | Text embedding, default model text-embedding-3-large |
| model | TEXT | NOT NULL DEFAULT 'text-embedding-3-large' | |
| token_count | INTEGER | NULL | |
| embedded_at | TIMESTAMPTZ | NULL | |
| embedded_text_hash | TEXT | NULL | md5(chunk_text) at embed time (v133); NULL = pre-v133 |
| embedding_input_hash | TEXT | NULL | Provenance of embedding input (v171) |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| language | TEXT | NULL | Code chunks only |
| symbol_name | TEXT | NULL | Code chunks only |
| symbol_type | TEXT | NULL | Code chunks only |
| start_line | INTEGER | NULL | Code chunks only |
| end_line | INTEGER | NULL | Code chunks only |
| parent_symbol_path | TEXT[] | NULL | v0.20 |
| doc_comment | TEXT | NULL | v0.20 |
| symbol_name_qualified | TEXT | NULL | v0.20 |
| search_vector | TSVECTOR | NULL | Weighted A (doc_comment, qualified name), B (chunk_text) |
| modality | TEXT | NOT NULL DEFAULT 'text' | text vs image rows |
| embedding_image | vector(1024) | NULL | Voyage multimodal image vectors (v0.27.1) |
| embedding_multimodal | vector(1024) | NULL | Unified cross-modal column (v0.36, v75). No index in range. |
Migration-added, not declared in range (see the top-level table): occurrence_id, local_ordinal, raw_byte_start/end, body_generation, retired_generation, embedding_pending_since, projected_byte_start/end.
Indexes:
idx_chunks_page_index(406): UNIQUE btree (page_id,chunk_index).idx_chunks_page(407): btree (page_id).idx_chunks_embedding(408): HNSW (embedding vector_cosine_ops), no WHERE.idx_chunks_symbol_name(410): partial btree (symbol_name) WHEREsymbol_name IS NOT NULL.idx_chunks_language(411): partial btree (language) WHERElanguage IS NOT NULL.idx_chunks_embedding_image(414-415): partial HNSW (embedding_image vector_cosine_ops) WHEREembedding_image IS NOT NULL.idx_chunks_search_vector(418): GIN (search_vector).idx_chunks_symbol_qualified(419-420): partial btree (symbol_name_qualified) WHERE NOT NULL.content_chunks_stale_idx(426-427): partial btree (page_id,chunk_index) WHEREembedding IS NULL. Servesembed --stale.
Triggers: chunk_search_vector_trigger (454-457). Its function is update_chunk_search_vector (444-452). It fires only when chunk_text, doc_comment or symbol_name_qualified change. gbrain_fts_input (432-436) is applied inside it.
Readers/writers:
- Main:
core/engine-sql/chunks.ts(34 hits, reads and writes). - Others:
core/embedding-migration.ts,core/page-state/projections.ts,core/engine-sql/bootstrap.ts,core/page-body/projection.ts,core/schema-migrations/v248-segmented-page-storage.ts. - Searches:
searchKeyword()queriescontent_chunks.search_vector, notpages.search_vector(comment at 1190-1194, #2704).
code_edges_chunk (schema.sql:472-494)
Purpose: resolved structural code edges where both endpoints are known chunk IDs. Readers UNION this with code_edges_symbol.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| from_chunk_id | INTEGER | NOT NULL | FK content_chunks(id) ON DELETE CASCADE |
| to_chunk_id | INTEGER | NOT NULL | FK content_chunks(id) ON DELETE CASCADE |
| from_symbol_qualified | TEXT | NOT NULL | |
| to_symbol_qualified | TEXT | NOT NULL | |
| edge_type | TEXT | NOT NULL | |
| edge_metadata | JSONB | NOT NULL DEFAULT '{}' | |
| source_id | TEXT | NULL | FK sources(id) ON DELETE CASCADE |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: code_edges_chunk_unique UNIQUE (from_chunk_id, to_chunk_id, edge_type). source_id is not in the key; from_chunk_id pins the source.
Indexes:
idx_code_edges_chunk_from(485): btree (from_chunk_id,edge_type).idx_code_edges_chunk_to(487): btree (to_chunk_id,edge_type).idx_code_edges_chunk_to_symbol(489): btree (to_symbol_qualified,edge_type).idx_code_edges_chunk_from_symbol(494): btree (from_symbol_qualified). Comment:getCalleesOfwas a seq scan without it (v116).
Readers/writers: core/engine-sql/code-edges.ts (main), core/pglite-engine/code-edges.ts, core/chunkers/symbol-resolver.ts.
code_edges_symbol (schema.sql:497-515)
Purpose: unresolved references. The target is known by qualified name, but its defining chunk is not imported yet. No promotion step; readers UNION it with code_edges_chunk.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| from_chunk_id | INTEGER | NOT NULL | FK content_chunks(id) ON DELETE CASCADE |
| from_symbol_qualified | TEXT | NOT NULL | |
| to_symbol_qualified | TEXT | NOT NULL | |
| edge_type | TEXT | NOT NULL | |
| edge_metadata | JSONB | NOT NULL DEFAULT '{}' | |
| source_id | TEXT | NULL | FK sources(id) ON DELETE CASCADE |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: code_edges_symbol_unique UNIQUE (from_chunk_id, to_symbol_qualified, edge_type).
Indexes:
idx_code_edges_symbol_from(509): btree (from_chunk_id,edge_type).idx_code_edges_symbol_to(511): btree (to_symbol_qualified,edge_type).idx_code_edges_symbol_from_symbol(515): btree (from_symbol_qualified). Companion to the chunk-table index (v116).
Readers/writers: core/engine-sql/code-edges.ts (main), core/chunkers/symbol-resolver.ts (writer, 5 hits), migrations v027, v058, v116.
Links and graph
links (schema.sql:549-592)
Purpose: typed cross-references between pages. Provenance is an open kebab-case tag, not a closed enum (v114 / #1941).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| from_page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE |
| to_page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE |
| link_type | TEXT | NOT NULL DEFAULT '' | |
| context | TEXT | NOT NULL DEFAULT '' | |
| link_source | TEXT | NULL | CHECK NULL or (~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$' AND char_length <= 64). Managed built-ins: markdown, frontmatter, mentions, wikilink-resolved. User: manual, or any kebab tag. |
| link_kind | TEXT | NULL | CHECK NULL or in (plain,typed_ner) (v98) |
| origin_page_id | INTEGER | NULL | FK pages(id) ON DELETE SET NULL. Set for frontmatter edges. |
| origin_field | TEXT | NULL | Frontmatter field name, e.g. key_people |
| resolution_type | TEXT | NULL | CHECK NULL or in (qualified,unqualified) (v0.18 Step 4) |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| assertion_tense | TEXT | NULL | CONSTRAINT links_assertion_tense_check: NULL or in (present,past) |
Constraints:
links_from_to_type_source_origin_unique: UNIQUE NULLS NOT DISTINCT (from_page_id,to_page_id,link_type,link_source,origin_page_id) (PG15+ syntax). Manual and frontmatter edges with the same tuple coexist; NULL origin values collide as expected.
Indexes:
idx_links_from(586): btree (from_page_id).idx_links_to(587): btree (to_page_id).idx_links_source(588): btree (link_source).idx_links_origin(589): btree (origin_page_id).idx_links_supersedes_to(590): partial btree (to_page_id) WHERElink_type = 'supersedes'.
Readers/writers:
- Main:
core/engine-sql/links.ts(25 hits, plus joins onpages). - Others:
core/search/read-enrichment.ts,core/persistence/manual-links.ts(writer,add_linkpath),core/engine-sql/health.ts,core/page-state/rename-alias.ts. - Migration
core/schema-migrations/v011-links-provenance-columns.ts.
wanted_links (schema.sql:595-611)
Purpose: authored links whose target does not exist yet. A live target updated after checked_at marks the origin stale for extraction (core/wanted-links.ts).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| origin_page_id | INTEGER | NOT NULL | FK pages(id) ON DELETE CASCADE |
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| producer | TEXT | NOT NULL | CHECK in (body,frontmatter) |
| ref_kind | TEXT | NOT NULL | CHECK in (slug,name) |
| target_source_id | TEXT | NOT NULL | No FK |
| target_ref | TEXT | NOT NULL | |
| link_type | TEXT | NOT NULL DEFAULT '' | |
| context | TEXT | NOT NULL DEFAULT '' | |
| checked_at | TIMESTAMPTZ | NOT NULL | |
| first_seen_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: wanted_links_reference_unique UNIQUE (origin_page_id, producer, ref_kind, target_source_id, target_ref).
Indexes: wanted_links_target_idx (610) btree (target_source_id, target_ref); wanted_links_source_idx (611) btree (source_id).
Readers/writers: core/wanted-links-store.ts (main, 3 hits), core/engine-sql/pages.ts (2), migration core/schema-migrations/v214-wanted-links.ts.
Auth, access and OAuth
access_tokens (schema.sql:735-748)
Purpose: bearer tokens for remote MCP access (hashed).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | UUID | PK DEFAULT gen_random_uuid() | |
| name | TEXT | NOT NULL | |
| token_hash | TEXT | NOT NULL, UNIQUE | |
| scopes | TEXT[] | NULL | |
| created_at | TIMESTAMPTZ | DEFAULT now() | Nullable (no NOT NULL in DDL) |
| last_used_at | TIMESTAMPTZ | NULL | |
| revoked_at | TIMESTAMPTZ | NULL |
Indexes: idx_access_tokens_hash (745): partial btree (token_hash) WHERE revoked_at IS NULL. The UNIQUE on token_hash also creates an index.
Readers/writers: core/grants/legacy-token.ts (12 hits), core/grants/access-token-schema.ts, core/token-mint.ts, core/persistence/authority.ts, core/access/members.ts.
mcp_request_log (schema.sql:750-762)
Purpose: usage log for remote MCP requests; feeds the admin dashboard.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| token_name | TEXT | NULL | |
| agent_name | TEXT | NULL | |
| operation | TEXT | NOT NULL | |
| latency_ms | INTEGER | NULL | |
| status | TEXT | NOT NULL DEFAULT 'success' | |
| params | JSONB | NULL | |
| error_message | TEXT | NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: idx_mcp_log_time_agent (865) btree (created_at, token_name); idx_mcp_log_agent_time (866) btree (agent_name, created_at DESC).
Readers/writers: writer commands/serve-http-mcp.ts (6 hits); reader commands/serve-http-admin-api.ts (8 hits); retention core/mcp-request-log-retention.ts; core/engine-sql/bootstrap.ts (4). RLS listed at 1834-1865.
oauth_clients (schema.sql:765-811)
Purpose: OAuth 2.1 registered clients, including per-client grant axes: source scope, federated read, budget, agent binding, tool surface, and takes holders.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| client_id | TEXT | PK | |
| client_secret_hash | TEXT | NULL | |
| client_name | TEXT | NOT NULL | |
| redirect_uris | TEXT[] | NULL | |
| grant_types | TEXT[] | DEFAULT '{"client_credentials"}' | |
| scope | TEXT | NULL | |
| token_endpoint_auth_method | TEXT | NULL | |
| client_id_issued_at | BIGINT | NULL | |
| client_secret_expires_at | BIGINT | NULL | |
| token_ttl | INTEGER | NULL | |
| deleted_at | TIMESTAMPTZ | NULL | |
| source_id | TEXT | NULL | FK sources(id) ON DELETE RESTRICT (not CASCADE) |
| federated_read | TEXT[] | NOT NULL DEFAULT '{}' | |
| budget_usd_per_day | NUMERIC(10,2) | NULL | v84 |
| bound_tools | TEXT[] | NULL | v85 agent binding |
| bound_source_id | TEXT | NULL | v85 |
| bound_brain_id | TEXT | NULL | v85 |
| bound_slug_prefixes | TEXT[] | NULL | v85 |
| bound_max_concurrent | INTEGER | NOT NULL DEFAULT 1 | |
| surface | TEXT | NULL | v127 tool surface; open value space |
| surface_set_by | TEXT | NULL | operator, self, dcr_default |
| allowed_operations | TEXT[] | NULL | |
| delegated_slug_prefixes | TEXT[] | NULL | |
| delegated_namespace | TEXT | NOT NULL DEFAULT 'prefixes' | |
| grant_profile | TEXT | NULL | |
| grant_revision | INTEGER | NOT NULL DEFAULT 0 | |
| grant_repair_reasons | TEXT[] | NOT NULL DEFAULT '{}' | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| source_grant | TEXT | NULL | CHECK (source_grant IN ('none')). Added by grants/oauth-client-axes-schema.ts. |
| takes_holders | TEXT[] | NULL | NULL = ['world'] |
Indexes: idx_oauth_clients_source_id (806-807) partial btree (source_id) WHERE NOT NULL; idx_oauth_clients_federated_read (808-809) GIN (federated_read).
Readers/writers: core/oauth-provider.ts (19 hits, main), core/grants/service.ts (writer), core/grants/schema.ts, commands/auth.ts (7), migration v085-oauth-clients-agent-binding.ts. RLS listed at 1834-1865.
oauth_grant_audit (schema.sql:813-825, GENERATED from core/grants/schema.ts)
Purpose: append-only audit of grant changes per client.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| client_id | TEXT | NOT NULL | No FK |
| actor | TEXT | NOT NULL | |
| action | TEXT | NOT NULL | |
| revision | INTEGER | NOT NULL | |
| before_grant | JSONB | NULL | |
| after_grant | JSONB | NOT NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: idx_oauth_grant_audit_client (823) btree (client_id, created_at).
Readers/writers: writer core/grants/service.ts (3 hits); core/grants/lifecycle.ts, core/grants/migration.ts, core/grants/schema.ts. Migration v210-clamp-oauth-token-ttl.ts.
fact_withdrawals (schema.sql:828-836, GENERATED from core/facts/withdrawal-schema.ts)
Purpose: withdrawal tombstones for facts, scoped by source and visibility (private or world).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| visibility | TEXT | NOT NULL | CHECK in (private,world) |
| subject | TEXT | NOT NULL DEFAULT '*' | |
| fact_hash | TEXT | NOT NULL | |
| withdrawn_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: PK (source_id, visibility, subject, fact_hash). Indexes: PK only in range. Facts index and withdrawal trigger are installed by migrations 60 and 148 (comment at 826, outside range).
Readers/writers: core/facts/withdrawal.ts (5 hits, main), core/facts/withdrawal-schema.ts (11), core/facts/single-prepare.ts, core/persistence/effect-targets.ts, core/repair/conversation-labels.ts.
oauth_tokens (schema.sql:838-849)
Purpose: issued OAuth access and refresh tokens (hashed).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| token_hash | TEXT | PK | |
| token_type | TEXT | NOT NULL | |
| client_id | TEXT | NOT NULL | FK oauth_clients(client_id) ON DELETE CASCADE |
| scopes | TEXT[] | NULL | |
| expires_at | BIGINT | NULL | Epoch seconds |
| resource | TEXT | NULL | RFC 8707 resource |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: idx_oauth_tokens_expiry (848) btree (expires_at); idx_oauth_tokens_client (849) btree (client_id).
Readers/writers: core/oauth-provider.ts (6), core/oauth-grants.ts (6), commands/serve-http-admin-api.ts (2), core/shared-skills/restore.ts. RLS listed at 1834-1865.
oauth_codes (schema.sql:851-863)
Purpose: short-lived PKCE authorization codes (hashed).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| code_hash | TEXT | PK | |
| client_id | TEXT | NOT NULL | FK oauth_clients(client_id) ON DELETE CASCADE |
| scopes | TEXT[] | NULL | |
| code_challenge | TEXT | NOT NULL | PKCE |
| code_challenge_method | TEXT | NOT NULL DEFAULT 'S256' | |
| redirect_uri | TEXT | NOT NULL | |
| state | TEXT | NULL | |
| resource | TEXT | NULL | |
| expires_at | BIGINT | NOT NULL | Epoch seconds |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: none in range (no index on expires_at).
Readers/writers: core/oauth-grants.ts (4 hits, main), core/oauth-provider.ts (2), core/grants/service.ts (1), core/shared-skills/restore.ts. RLS listed at 1834-1865.
Jobs, minions, checkpoints and locks
minion_jobs (schema.sql:1248-1321)
Purpose: Postgres-native job queue (BullMQ-inspired). Holds queue state, retry and backoff, parent and child trees, locks and timeouts, token accounting, and the protocol-1 claim generation.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| name | TEXT | NOT NULL | Job kind |
| queue | TEXT | NOT NULL DEFAULT 'default' | |
| status | TEXT | NOT NULL DEFAULT 'waiting' | CHECK in (waiting, active, completed, failed, delayed, dead, cancelled, waiting-children, paused) |
| priority | INTEGER | NOT NULL DEFAULT 0 | |
| submission_authority | JSONB | NULL | NULL = legacy row; local authorization needed before workers start |
| claim_generation | BIGINT | NOT NULL DEFAULT 0 | Advanced only by protocol-aware claims |
| data | JSONB | NOT NULL DEFAULT '{}' | |
| max_attempts | INTEGER | NOT NULL DEFAULT 3 | CHECK >= 1 |
| attempts_made | INTEGER | NOT NULL DEFAULT 0 | CHECK attempts_made <= attempts_started |
| attempts_started | INTEGER | NOT NULL DEFAULT 0 | |
| backoff_type | TEXT | NOT NULL DEFAULT 'exponential' | CHECK in (fixed, exponential) |
| backoff_delay | INTEGER | NOT NULL DEFAULT 1000 | ms |
| backoff_jitter | REAL | NOT NULL DEFAULT 0.2 | CHECK 0.0..1.0 |
| stalled_counter | INTEGER | NOT NULL DEFAULT 0 | CHECK >= 0 |
| max_stalled | INTEGER | NOT NULL DEFAULT 5 | CHECK >= 0 |
| lock_token | TEXT | NULL | |
| lock_until | TIMESTAMPTZ | NULL | |
| delay_until | TIMESTAMPTZ | NULL | |
| parent_job_id | INTEGER | NULL | FK minion_jobs(id) ON DELETE SET NULL |
| on_child_fail | TEXT | NOT NULL DEFAULT 'fail_parent' | CHECK in (fail_parent, remove_dep, ignore, continue) |
| tokens_input | INTEGER | NOT NULL DEFAULT 0 | |
| tokens_output | INTEGER | NOT NULL DEFAULT 0 | |
| tokens_cache_read | INTEGER | NOT NULL DEFAULT 0 | |
| result | JSONB | NULL | |
| progress | JSONB | NULL | |
| error_text | TEXT | NULL | |
| stacktrace | JSONB | DEFAULT '[]' | |
| depth | INTEGER | NOT NULL DEFAULT 0 | CHECK >= 0 |
| max_children | INTEGER | NULL | CHECK > 0 |
| timeout_ms | INTEGER | NULL | CHECK > 0 |
| lock_duration_ms | INTEGER | NULL | CHECK NULL or 5000..3600000 |
| timeout_at | TIMESTAMPTZ | NULL | |
| remove_on_complete | BOOLEAN | NOT NULL DEFAULT FALSE | |
| remove_on_fail | BOOLEAN | NOT NULL DEFAULT FALSE | |
| idempotency_key | TEXT | NULL | Unique when not NULL |
| private_queue_owner_job_id | INTEGER | NULL | FK minion_jobs(id) ON DELETE SET NULL |
| private_queue_owner_token | TEXT | NULL | |
| private_queue_lease_until | TIMESTAMPTZ | NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| started_at | TIMESTAMPTZ | NULL | |
| finished_at | TIMESTAMPTZ | NULL | |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints (named): chk_status, chk_backoff_type, chk_on_child_fail, chk_jitter_range, chk_attempts_order, chk_nonnegative (attempts, stalled, max_attempts >= 1, max_stalled >= 0), chk_depth_nonnegative, chk_max_children_positive, chk_timeout_positive, chk_lock_duration_positive (lines 1292-1302).
Migration-added (not in range): spend_authorization, spend_claim_token (v205), read by trigger 1799.
Indexes:
idx_minion_jobs_claim(1304): btree (queue,priority ASC,created_at ASC) WHEREstatus='waiting'. Claim path.idx_minion_jobs_status(1305): btree (status).idx_minion_jobs_stalled(1306): partial btree (lock_until) WHEREstatus='active'.idx_minion_jobs_delayed(1307): partial btree (delay_until) WHEREstatus='delayed'.idx_minion_jobs_parent(1308): btree (parent_job_id).idx_minion_jobs_timeout(1309): partial btree (timeout_at) WHEREstatus='active' AND timeout_at IS NOT NULL.idx_minion_jobs_parent_status(1310): partial btree (parent_job_id,status) WHEREparent_job_id IS NOT NULL.uniq_minion_jobs_idempotency(1311): UNIQUE partial btree (idempotency_key) WHERE NOT NULL.idx_minion_jobs_queue_status_updated(1314): btree (queue,status,updated_at). Wedge signal (v127).idx_minion_jobs_private_queue_recovery(1315-1317): partial btree (queue,private_queue_lease_until) WHEREqueue LIKE 'dream-inline-%' AND status IN (waiting, active, delayed, waiting-children, paused).idx_minion_jobs_private_queue_owner(1319-1320): partial btree (private_queue_owner_job_id) WHERE NOT NULL.
Triggers: minion_queue_protocol (1808-1810, BEFORE INSERT OR UPDATE, FOR EACH ROW) runs enforce_minion_queue_protocol. minion_job_notify (1823-1825, AFTER INSERT OR UPDATE OF status) runs notify_minion_job_change (pg_notify, Postgres only).
RLS: listed in the DO block at 1834-1865.
Readers/writers:
- Main:
core/minions/queue.ts(93 hits, claim, insert, update, and reads). - Others:
commands/doctor/checks/queue-jobs.ts(13),core/minions/job-insight.ts(10),core/minions/authorize-legacy.ts(8),core/minions/supervisor.ts(wedge queries),core/ops/jobs.ts. - Migration
core/schema-migrations/v007-agent-parity-layer.tsand v024-v205 family.
minion_inbox (schema.sql:1324-1333)
Purpose: sidechannel messages to a job (child_done notices, etc.).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| job_id | INTEGER | NOT NULL | FK minion_jobs(id) ON DELETE CASCADE |
| sender | TEXT | NOT NULL | |
| payload | JSONB | NOT NULL | |
| sent_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| read_at | TIMESTAMPTZ | NULL |
Indexes: idx_minion_inbox_unread (1332): partial btree (job_id) WHERE read_at IS NULL. idx_minion_inbox_child_done (1333): partial btree (job_id, sent_at) WHERE payload->>'type' = 'child_done'.
RLS listed at 1834-1865.
Readers/writers: core/minions/queue.ts (7), core/ops/jobs.ts (1). Migrations v006, v007, v024.
minion_attachments (schema.sql:1336-1351)
Purpose: per-job binary blobs (manifests, agent outputs). Either inline bytes or an external URI.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| job_id | INTEGER | NOT NULL | FK minion_jobs(id) ON DELETE CASCADE |
| filename | TEXT | NOT NULL | |
| content_type | TEXT | NOT NULL | |
| content | BYTEA | NULL | STORAGE EXTERNAL (1351, TOAST setting, not semantic) |
| storage_uri | TEXT | NULL | |
| size_bytes | INTEGER | NOT NULL | CHECK >= 0 |
| sha256 | TEXT | NOT NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: uniq_minion_attachments_job_filename UNIQUE (job_id, filename); chk_attachment_storage CHECK (content IS NOT NULL OR storage_uri IS NOT NULL).
Indexes: idx_minion_attachments_job (1350) btree (job_id).
Readers/writers: core/minions/queue.ts (5). Migrations v007, v024.
migration_impact_log (schema.sql:1377-1399)
Purpose: before/after metric stats per onboard remediation. Delta is computed at read time (no stored generated column).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| remediation_id | TEXT | NOT NULL | |
| metric_name | TEXT | NOT NULL | |
| metric_before | NUMERIC | NULL | |
| metric_after | NUMERIC | NULL | |
| job_id | BIGINT | NULL | FK minion_jobs(id) ON DELETE SET NULL |
| source_id | TEXT | NULL | No FK |
| brain_id | TEXT | NULL | |
| started_at | TIMESTAMPTZ | NULL | |
| idempotency_key | TEXT | NULL | |
| applied_at | TIMESTAMPTZ | NOT NULL DEFAULT NOW() | |
| applied_by | TEXT | NULL | |
| details | JSONB | DEFAULT '{}' |
Indexes: migration_impact_log_remediation_idx (1392) btree (remediation_id, applied_at DESC); migration_impact_log_attribution_idx (1394) partial btree (job_id, source_id) WHERE job_id IS NOT NULL.
Readers/writers: core/onboard/impact-capture.ts (writer), commands/onboard.ts (reader), migration core/schema-migrations/v103-migration-impact-log-and-priority-recent-idx.ts. Note the table is placed after minion_jobs on purpose (comment at 1354-1372, v0.41.25.0 ordering fix).
subagent_messages (schema.sql:1403-1425)
Purpose: durable Anthropic-shape or provider-neutral message blocks, one row per message in a subagent loop.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| job_id | BIGINT | NOT NULL | FK minion_jobs(id) ON DELETE CASCADE |
| message_idx | INTEGER | NOT NULL | UNIQUE with job_id |
| role | TEXT | NOT NULL | CHECK in (user, assistant) |
| content_blocks | JSONB | NOT NULL | ChatBlock[] (schema_version 2) or legacy blocks (1) |
| schema_version | INTEGER | NOT NULL DEFAULT 1 | |
| provider_id | TEXT | NULL | e.g. anthropic, openai, deepseek |
| tokens_in | INTEGER | NULL | |
| tokens_out | INTEGER | NULL | |
| tokens_cache_read | INTEGER | NULL | |
| tokens_cache_create | INTEGER | NULL | |
| model | TEXT | NULL | |
| ended_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: uniq_subagent_messages_idx UNIQUE (job_id, message_idx); chk_subagent_messages_role.
Indexes: idx_subagent_messages_job (1424) btree (job_id, message_idx); idx_subagent_messages_provider (1425) btree (job_id, provider_id).
Readers/writers: core/minions/handlers/subagent.ts (2), core/minions/handlers/subagent-persistence.ts (2), core/minions/transcript.ts (1). Migration v036 (provider-neutral persistence).
subagent_tool_executions (schema.sql:1438-1463)
Purpose: two-phase tool-call ledger (pending, then complete or failed). Identity is (job_id, message_idx, ordinal); tool_use_id is not unique per job (#4155, uniq dropped in v131).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| job_id | BIGINT | NOT NULL | FK minion_jobs(id) ON DELETE CASCADE |
| message_idx | INTEGER | NOT NULL | |
| tool_use_id | TEXT | NOT NULL | Raw provider id, NOT unique |
| tool_name | TEXT | NOT NULL | |
| input | JSONB | NOT NULL | |
| status | TEXT | NOT NULL | CHECK in (pending, complete, failed) |
| output | JSONB | NULL | |
| error | TEXT | NULL | |
| schema_version | INTEGER | NOT NULL DEFAULT 1 | |
| provider_id | TEXT | NULL | |
| ordinal | INTEGER | NULL | v0.38 D11; NULL on pre-v82 rows |
| gbrain_tool_use_id | UUID | NULL | uuid v7, NULL on legacy rows |
| started_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| ended_at | TIMESTAMPTZ | NULL |
Constraints: subagent_tool_executions_stable_id UNIQUE (job_id, message_idx, ordinal).
Indexes: idx_subagent_tools_job (1463) btree (job_id, status).
Readers/writers: core/minions/handlers/subagent-persistence.ts (13, main), core/minions/handlers/subagent.ts (4), core/minions/handlers/subagent-oneshot.ts (3), core/cycle/patterns.ts (4).
subagent_rate_leases (schema.sql:1469-1476)
Purpose: concurrency cap on outbound providers. Acquire = insert if active < max under advisory lock; release = delete. Expired leases are pruned on next acquire.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| key | TEXT | NOT NULL | e.g. anthropic:messages |
| owner_job_id | BIGINT | NOT NULL | FK minion_jobs(id) ON DELETE CASCADE |
| acquired_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| expires_at | TIMESTAMPTZ | NOT NULL |
Indexes: idx_rate_leases_key_expires (1476) btree (key, expires_at).
Readers/writers: core/minions/rate-leases.ts (5, main). RLS listed at 1834-1865.
op_checkpoints (schema.sql:878-894)
Purpose: shared checkpoint table for long-running ops. Fingerprint is a sha8 over canonical JSON of op-relevant params.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| op | TEXT | NOT NULL | PK part |
| fingerprint | TEXT | NOT NULL | PK part |
| completed_keys | JSONB | NOT NULL DEFAULT '[]' | CHECK jsonb_typeof = 'array' (constraint op_checkpoints_completed_keys_array) |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | GC after 7 days in cycle purge |
Constraints: PK (op, fingerprint).
Indexes: op_checkpoints_updated_at_idx (889) btree (updated_at); op_checkpoints_sync_hold_page_idx (892-893) partial expression btree on (completed_keys->0->>'page_id') WHERE op = 'sync-hold' (#5988, v200).
Readers/writers: core/persistence/sync-holds.ts (29, main), core/persistence/managed-sync-orphans.ts (16), core/persistence/sync-run.ts (13), core/persistence/sync-failures.ts (12), core/persistence/atom-maintenance.ts (12), core/op-checkpoint.ts (generic loader).
op_checkpoint_paths (schema.sql:900-912)
Purpose: append-only delta rows for completed_keys (#1794). Replaces rewriting the whole JSON array on each flush.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| op | TEXT | NOT NULL | PK part; FK part |
| fingerprint | TEXT | NOT NULL | PK part; FK part |
| path | TEXT | NOT NULL | PK part |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: PK (op, fingerprint, path); FK (op, fingerprint) REFERENCES op_checkpoints ON DELETE CASCADE (op_checkpoint_paths_parent_fk).
Indexes: none beyond PK.
Readers/writers: core/op-checkpoint.ts (3), migration v115-op-checkpoint-paths-append-table.ts.
dream_verdicts (schema.sql:1485-1506)
Purpose: cache of the cheap "is this transcript worth processing" verdict per (file_path, content_hash). Triage v1 fields added by v129; 30-day TTL (v138).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| file_path | TEXT | NOT NULL | PK part |
| content_hash | TEXT | NOT NULL | PK part |
| worth_processing | BOOLEAN | NOT NULL | |
| reasons | JSONB | NULL | |
| judged_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| score | DOUBLE PRECISION | NULL | Triage v1, ordinal in [0,1] |
| content_type | TEXT | NULL | |
| segments | JSONB | NULL | |
| entities | JSONB | NULL | |
| model | TEXT | NULL | |
| triage_version | INT | NULL | |
| expires_at | TIMESTAMPTZ | NOT NULL DEFAULT (now() + interval '30 days') |
Constraints: PK (file_path, content_hash).
Indexes: dream_verdicts_expires_idx (1505) btree (expires_at).
Readers/writers: core/postgres-engine.ts (3), core/pglite-engine.ts (3), core/engine-sql/bootstrap.ts (in DDL), core/deletion-inventory.ts. Migrations v030, v129, v143. Rebuildable cache (gbrain dream retriage --force). Not page-scoped, so it has no page_id (comment at 1487-1491 contrasts it with raw_data).
gbrain_cycle_locks (schema.sql:1516-1532)
Purpose: cycle coordination lock (runCycle primitive). Holders refresh ttl_expires_at; crashed holders auto-release at TTL. Chosen over session advisory locks so it works through PgBouncer transaction pooling.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | TEXT | PK | Lock name |
| holder_pid | INT | NOT NULL | |
| holder_host | TEXT | NULL | |
| acquired_at | TIMESTAMPTZ | NOT NULL DEFAULT NOW() | |
| ttl_expires_at | TIMESTAMPTZ | NOT NULL | |
| last_refreshed_at | TIMESTAMPTZ | NULL | v97; --break-lock --max-age |
| acquisition_token | UUID | NOT NULL DEFAULT gen_random_uuid() | ALTER at 1530-1531 (lease token, generated block) |
Indexes: idx_cycle_locks_ttl (1528) btree (ttl_expires_at).
Readers/writers: core/db-lock.ts (22, main), core/page-embedding-lease.ts (3), core/persistence/skill-activation.ts (2), core/migration-orchestration-lock.ts, commands/apply-migrations.ts, commands/embed.ts. RLS listed at 1834-1865.
Retrieval feedback and session context
context_volunteer_events (schema.sql:915-931)
Purpose: feedback log for pages volunteered by push-based context (volunteer_context op, retrieval reflex, gbrain watch). Rationale is a deterministic template, not raw text. 90-day prune in the dream purge phase.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NOT NULL | No FK |
| slug | TEXT | NOT NULL | |
| confidence | DOUBLE PRECISION | NOT NULL | |
| match_arm | TEXT | NOT NULL | |
| rationale | TEXT | NOT NULL DEFAULT '' | |
| channel | TEXT | NOT NULL DEFAULT 'op' | |
| session_id | TEXT | NULL | |
| turn | INTEGER | NULL | |
| volunteered_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: context_volunteer_events_src_time_idx (927) btree (source_id, volunteered_at DESC); context_volunteer_events_src_slug_idx (929) btree (source_id, slug).
Readers/writers: core/context/volunteer-events.ts (2, writer), commands/doctor/checks/core-health.ts (2), core/persistence/purge-name-stores.ts, core/facts/purge-verify.ts. Migration v117.
retrieval_events (schema.sql:936-942)
Purpose: one row per answer-producing retrieval (query, search, think, synthesize, recall). Feeds use-attributed feedback. No query text stored.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | TEXT | PK | |
| client_id | TEXT | NOT NULL DEFAULT 'local' | |
| op | TEXT | NOT NULL | CHECK in (query, search, think, synthesize, recall) |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Indexes: retrieval_events_time_idx (942) btree (created_at).
Readers/writers: core/feedback/store.ts (4, writer and reader). Migration v207.
retrieval_event_pages (schema.sql:943-951)
Purpose: pages returned by a retrieval event, with the revision hash used.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| event_id | TEXT | NOT NULL | FK retrieval_events(id) ON DELETE CASCADE |
| source_id | TEXT | NOT NULL | No FK |
| slug | TEXT | NOT NULL | |
| content_hash | TEXT | NULL | Revision that was retrieved |
| rank | INTEGER | NOT NULL | |
| cited | BOOLEAN | NOT NULL DEFAULT false |
Constraints: PK (event_id, source_id, slug). Indexes: PK only.
Readers/writers: core/feedback/store.ts (3). core/facts/purge-verify.ts (1).
retrieval_event_links (schema.sql:952-958)
Purpose: relational-path edges used by an answer.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| event_id | TEXT | NOT NULL | FK retrieval_events(id) ON DELETE CASCADE |
| source_id | TEXT | NOT NULL | No FK |
| edge_key | TEXT | NOT NULL | |
| to_slug | TEXT | NOT NULL |
Constraints: PK (event_id, source_id, edge_key, to_slug). Indexes: PK only.
Readers/writers: core/feedback/store.ts (3). core/facts/purge-verify.ts (1).
retrieval_feedback (schema.sql:959-973)
Purpose: explicit or cited ratings that move per-element weights (neutral 0.5).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| event_id | TEXT | NOT NULL | FK retrieval_events(id) ON DELETE CASCADE |
| signal | TEXT | NOT NULL | CHECK in (explicit, cited) |
| element_kind | TEXT | NOT NULL | CHECK in (page, link) |
| source_id | TEXT | NOT NULL | No FK |
| element_key | TEXT | NOT NULL | |
| client_id | TEXT | NOT NULL DEFAULT 'local' | |
| rating | SMALLINT | NOT NULL | CHECK 1..5 |
| weight_before | REAL | NOT NULL | |
| weight_after | REAL | NOT NULL | |
| applied_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: PK (event_id, signal, element_kind, source_id, element_key).
Indexes: retrieval_feedback_client_time_idx (972) btree (client_id, applied_at DESC).
Readers/writers: core/feedback/store.ts (9, main writer).
retrieval_weights (schema.sql:974-989)
Purpose: learned per-element weight (page or link) per source. The search ranking stage reads it.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| element_kind | TEXT | NOT NULL | CHECK in (page, link) |
| element_key | TEXT | NOT NULL | |
| weight | REAL | NOT NULL DEFAULT 0.5 | CHECK 0 <= w <= 1 |
| content_hash | TEXT | NULL | |
| updates | INTEGER | NOT NULL DEFAULT 0 | |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: PK (source_id, element_kind, element_key). Indexes: PK only.
Readers/writers: core/feedback/store.ts (10, writer), core/search/feedback-boost.ts (LEFT JOIN, reader), migration v207.
session_context_state (schema.sql:991-1007)
Purpose: per-session cursor and dedup for the delta verb and heartbeat (ambient recall).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| source_id | TEXT | NOT NULL | No FK |
| client_id | TEXT | NOT NULL DEFAULT 'local' | |
| session_id | TEXT | NOT NULL | |
| standing_entities | JSONB | NOT NULL DEFAULT '[]' | |
| surfaced_slugs | JSONB | NOT NULL DEFAULT '[]' | |
| checkpoint_manifest | JSONB | NOT NULL DEFAULT '[]' | |
| last_wake_at | TIMESTAMPTZ | NULL | |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| facts_cursor_at | TIMESTAMPTZ | NULL | Facts keyset (v208) |
| facts_cursor_id | BIGINT | NULL | Facts keyset (v208) |
| degraded_wakes | INTEGER | NOT NULL DEFAULT 0 | Consecutive incomplete wakes |
Constraints: PK (source_id, client_id, session_id).
Indexes: session_context_state_updated_idx (1005) btree (updated_at).
Readers/writers: core/context/session-state.ts (8, main), migrations v126, v132, v208.
extract_atoms_transcript_state (schema.sql:1036-1050)
Purpose: failure streak and tombstone for transcript extraction items. Transcripts are files, not pages, so raw_data cannot hold this. Keyed on source_id because extraction is source-scoped.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| source_id | TEXT | NOT NULL DEFAULT 'default' | No FK |
| file_path | TEXT | NOT NULL | PK part |
| content_hash | TEXT | NOT NULL | PK part (16-char prefix); an edit resets the streak |
| fail_count | INTEGER | NOT NULL DEFAULT 0 | |
| tombstoned | BOOLEAN | NOT NULL DEFAULT FALSE | |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| partial_input_chars | INTEGER | NULL | Input cap a cut transcript was read under (v240, #6503) |
Constraints: PK (source_id, file_path, content_hash).
Indexes: extract_atoms_transcript_state_tombstoned_idx (1048) partial btree (source_id, content_hash) WHERE tombstoned.
Readers/writers: core/cycle/extract-atoms-input.ts (3, reader), core/cycle/extract-atoms.ts (2, writer). Migrations v146, v242. RLS: covered by the v35 auto RLS event trigger (comment at 1034-1035), not the DO block.
Facts, loops and usage
open_loops (schema.sql:1078-1112)
Purpose: Gmail-first open-loop engine. Records who is waiting on you and what you promised. Deduped per source on dedup_key. Loops close by state transition, never delete.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| dedup_key | TEXT | NOT NULL | |
| loop_type | TEXT | NOT NULL | CHECK in (commitment_owed_by_me, commitment_owed_to_me, unanswered_inbound, unanswered_outbound, decision_pending) |
| counterparty_slug | TEXT | NULL | |
| counterparty_email | TEXT | NULL | |
| summary | TEXT | NOT NULL | |
| evidence | JSONB | NOT NULL DEFAULT '[]' | |
| thread_id | TEXT | NULL | |
| page_slug | TEXT | NULL | |
| due_at | TIMESTAMPTZ | NULL | |
| status | TEXT | NOT NULL DEFAULT 'open' | CHECK in (open, done, dropped, stale) |
| detector | TEXT | NOT NULL | CHECK in (deterministic_thread, llm_extract, manual) |
| confidence | REAL | NOT NULL DEFAULT 1.0 | |
| fact_id | BIGINT | NULL | Projection into facts (no FK in DDL) |
| opened_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| last_activity_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| closed_at | TIMESTAMPTZ | NULL | |
| closed_by | TEXT | NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| updated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: open_loops_dedup UNIQUE (source_id, dedup_key).
Indexes: open_loops_status_idx (1104) btree (source_id, status, last_activity_at DESC); open_loops_counterparty_idx (1106) partial btree (source_id, counterparty_slug) WHERE status='open'; open_loops_thread_idx (1108) partial btree (source_id, thread_id) WHERE status='open'.
Readers/writers: core/loops/loops-store.ts (13, main), core/persistence/loop-fact-retirement.ts (5), core/repair/conversation-labels.ts (2), core/loops/loops-schema.ts (2). Migration v144. RLS: not in the DO list at 1834-1865.
loop_suppressions (schema.sql:1114-1122)
Purpose: gbrain loops mute <sender|thread>. Suppressed senders or threads never open new loops.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| kind | TEXT | NOT NULL | CHECK in (sender, thread) |
| value | TEXT | NOT NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() |
Constraints: loop_suppressions_uniq UNIQUE (source_id, kind, value). Indexes: none beyond the unique.
Readers/writers: core/loops/loops-store.ts (4). Migration v144.
chat_usage_log (schema.sql:1057-1076)
Purpose: durable per-call usage ledger for successful gateway chat calls. cost_usd is NULL when the model has no pricing (never a fake 0).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| model | TEXT | NOT NULL | |
| provider | TEXT | NULL | |
| phase | TEXT | NULL | |
| input_tokens | INTEGER | NOT NULL DEFAULT 0 | |
| output_tokens | INTEGER | NOT NULL DEFAULT 0 | |
| cache_read_tokens | INTEGER | NOT NULL DEFAULT 0 | |
| cache_write_tokens | INTEGER | NOT NULL DEFAULT 0 | |
| cost_usd | DOUBLE PRECISION | NULL |
Indexes: idx_chat_usage_log_created (1069) btree (created_at); idx_chat_usage_log_model (1071) btree (model, created_at).
Readers/writers: writer core/ai/chat-usage.ts (1), reader core/ops/usage.ts (2, the get_usage op). Migration v140. RLS: covered by the v35 auto event trigger (comment at 1035).
Evals, takes and calibration
eval_candidates (schema.sql:1543-1576)
Purpose: captured query and search calls from the op-layer wrapper, PII-scrubbed before insert, for BrainBench-Real replay.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| tool_name | TEXT | NOT NULL | CHECK in (query, search) |
| query | TEXT | NOT NULL | CHECK length(query) <= 51200 (characters in PG) |
| retrieved_slugs | TEXT[] | NOT NULL DEFAULT '{}' | |
| retrieved_chunk_ids | INTEGER[] | NOT NULL DEFAULT '{}' | |
| source_ids | TEXT[] | NOT NULL DEFAULT '{}' | |
| expand_enabled | BOOLEAN | NULL | |
| detail | TEXT | NULL | CHECK in (low, medium, high) |
| detail_resolved | TEXT | NULL | CHECK in (low, medium, high) |
| vector_enabled | BOOLEAN | NOT NULL | |
| expansion_applied | BOOLEAN | NOT NULL | |
| latency_ms | INTEGER | NOT NULL | |
| remote | BOOLEAN | NOT NULL | |
| job_id | INTEGER | NULL | No FK |
| subagent_id | INTEGER | NULL | No FK |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT NOW() | |
| as_of_ts | TIMESTAMPTZ | NULL | v0.29.1 |
| salience_param | TEXT | NULL | v0.29.1 |
| recency_param | TEXT | NULL | v0.29.1 |
| salience_resolved | TEXT | NULL | v0.29.1 |
| recency_resolved | TEXT | NULL | v0.29.1 |
| salience_source | TEXT | NULL | v0.29.1 |
| recency_source | TEXT | NULL | v0.29.1 |
| embedding_column | TEXT | NULL | v68 / v0.36.3; replay uses it |
Indexes: idx_eval_candidates_created_at (1576) btree (created_at DESC).
Readers/writers: writers core/postgres-engine.ts (3), core/pglite-engine.ts (3), core/eval-capture-scrub.ts (scrub before insert); pruning commands/eval-prune.ts; reads by core/eval-contradictions/runner.ts, core/eval-contradictions/cost-tracker.ts. Migrations v031, v042, v074. RLS: in DO list (1834-1865).
eval_capture_failures (schema.sql:1578-1584)
Purpose: cross-process audit of eval-capture insert failures, surfaced by gbrain doctor (in-process counters cannot cross the MCP/CLI boundary).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | SERIAL | PK | |
| ts | TIMESTAMPTZ | NOT NULL DEFAULT NOW() | |
| reason | TEXT | NOT NULL | CHECK in (db_down, rls_reject, check_violation, scrubber_exception, other) |
Indexes: idx_eval_capture_failures_ts (1583) btree (ts DESC).
Readers/writers: writers core/postgres-engine.ts (2), core/pglite-engine.ts (2); reader commands/doctor/checks/knowledge-health.ts. Migration v031. RLS: in DO list.
eval_takes_quality_runs (schema.sql:1591-1612)
Purpose: DB-authoritative receipts for the takes-quality eval. Re-running the same run is a no-op via the 4-sha unique key.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| receipt_sha8_corpus | TEXT | NOT NULL | UNIQUE part |
| receipt_sha8_prompt | TEXT | NOT NULL | UNIQUE part |
| receipt_sha8_models | TEXT | NOT NULL | UNIQUE part |
| receipt_sha8_rubric | TEXT | NOT NULL | UNIQUE part |
| rubric_version | TEXT | NOT NULL | |
| verdict | TEXT | NOT NULL | CHECK in (pass, fail, inconclusive) |
| overall_score | REAL | NOT NULL | |
| dim_scores | JSONB | NOT NULL | |
| cost_usd | REAL | NOT NULL | |
| receipt_json | JSONB | NOT NULL | Full receipt for replay |
| receipt_disk_path | TEXT | NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT NOW() |
Constraints: UNIQUE (receipt_sha8_corpus, receipt_sha8_prompt, receipt_sha8_models, receipt_sha8_rubric).
Indexes: eval_takes_quality_runs_trend_idx (1607) btree (rubric_version, created_at DESC).
Readers/writers: writer core/takes-quality-eval/receipt-write.ts; readers core/takes-quality-eval/trend.ts, core/takes-quality-eval/replay.ts. Migration v049.
eval_contradictions_cache (schema.sql:1614-1628)
Purpose: persistent judge verdicts for the contradiction probe. Prompt version and truncation policy are in the PK, so a prompt edit invalidates old verdicts. TTL via expires_at, swept periodically.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| chunk_a_hash | TEXT | NOT NULL | PK part |
| chunk_b_hash | TEXT | NOT NULL | PK part |
| model_id | TEXT | NOT NULL | PK part |
| prompt_version | TEXT | NOT NULL | PK part |
| truncation_policy | TEXT | NOT NULL | PK part |
| verdict | JSONB | NOT NULL | |
| created_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| expires_at | TIMESTAMPTZ | NOT NULL |
Constraints: PK (chunk_a_hash, chunk_b_hash, model_id, prompt_version, truncation_policy).
Indexes: eval_contradictions_cache_expires_idx (1625) btree (expires_at).
Readers/writers: core/engine-sql/takes.ts (3), commands/eval.ts, core/eval-contradictions/types.ts. Migrations v052, v055, v070. RLS: in DO list.
eval_contradictions_runs (schema.sql:1631-1648)
Purpose: time-series of contradiction-probe runs, for the trend subcommand and the doctor contradictions check. report_json holds the full ProbeReport.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| run_id | TEXT | PK | |
| ran_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| schema_version | INTEGER | NOT NULL DEFAULT 1 | |
| judge_model | TEXT | NOT NULL | |
| prompt_version | TEXT | NOT NULL | |
| queries_evaluated | INTEGER | NOT NULL | |
| queries_with_contradiction | INTEGER | NOT NULL | |
| total_contradictions_flagged | INTEGER | NOT NULL | |
| wilson_ci_lower | REAL | NOT NULL | |
| wilson_ci_upper | REAL | NOT NULL | |
| judge_errors_total | INTEGER | NOT NULL | |
| cost_usd_total | REAL | NOT NULL | |
| duration_ms | INTEGER | NOT NULL | |
| source_tier_breakdown | JSONB | NOT NULL | |
| report_json | JSONB | NOT NULL |
Indexes: eval_contradictions_runs_ran_at_idx (1648) btree (ran_at DESC).
Readers/writers: core/eval-contradictions/cost-prompt.ts, core/eval-contradictions/run-health.ts, core/ops/insights.ts, core/engine-sql/takes.ts (2), commands/eval-suspected-contradictions.ts, commands/doctor/checks/knowledge-health.ts. Migration v053. RLS: in DO list.
calibration_profiles (schema.sql:1659-1688)
Purpose: per-holder LLM narrative aggregation of takes scorecard data. published gates cross-brain sharing (D15).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| holder | TEXT | NOT NULL | |
| wave_version | TEXT | NOT NULL DEFAULT 'v0.36.1.0' | |
| generated_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| published | BOOLEAN | NOT NULL DEFAULT false | |
| total_resolved | INTEGER | NOT NULL | |
| brier | REAL | NULL | |
| accuracy | REAL | NULL | |
| partial_rate | REAL | NULL | |
| grade_completion | REAL | NOT NULL DEFAULT 1.0 | |
| domain_scorecards | JSONB | NOT NULL | |
| pattern_statements | TEXT[] | NOT NULL | |
| voice_gate_passed | BOOLEAN | NOT NULL | |
| voice_gate_attempts | SMALLINT | NOT NULL | |
| active_bias_tags | TEXT[] | NOT NULL | |
| model_id | TEXT | NOT NULL | |
| cost_usd | NUMERIC(10,4) | NULL | |
| judge_model_agreement | REAL | NULL |
Indexes: calibration_profiles_holder_recent_idx (1680) btree (source_id, holder, generated_at DESC); calibration_profiles_bias_tags_gin (1682) GIN (active_bias_tags); calibration_profiles_published_idx (1684) partial btree (source_id, published, holder) WHERE published = true.
Readers/writers: writer core/cycle/calibration-profile.ts (2); core/calibration/undo-wave.ts (2), core/calibration/voice-gate.ts, core/calibration/domain-aggregators.ts, core/ops/calibration.ts, commands/doctor/checks/calibration.ts (2), core/brainstorm/orchestrator.ts (1). Migration v068. RLS: in DO list.
take_proposals (schema.sql:1690-1725)
Purpose: candidate takes proposed by the cycle, awaiting accept or reject. Per-claim idempotency (v125) via md5(claim_text).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| page_slug | TEXT | NOT NULL | No FK to pages (slug-keyed) |
| content_hash | TEXT | NOT NULL | |
| prompt_version | TEXT | NOT NULL | |
| wave_version | TEXT | NOT NULL DEFAULT 'v0.36.1.0' | |
| proposed_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| proposal_run_id | TEXT | NOT NULL | |
| status | TEXT | NOT NULL DEFAULT 'pending' | CHECK in (pending, accepted, rejected, superseded) |
| claim_text | TEXT | NOT NULL | |
| kind | TEXT | NOT NULL | |
| holder | TEXT | NOT NULL | |
| weight | REAL | NOT NULL | |
| domain | TEXT | NULL | |
| dedup_against_fence_rows | JSONB | NULL | |
| model_id | TEXT | NOT NULL | |
| acted_at | TIMESTAMPTZ | NULL | |
| acted_by | TEXT | NULL | |
| promoted_row_num | INTEGER | NULL | |
| predicted_brier | REAL | NULL | |
| predicted_brier_bucket_n | INTEGER | NULL |
Indexes:
take_proposals_idempotency_idx(1714): UNIQUE btree (source_id,page_slug,content_hash,prompt_version,md5(claim_text)).take_proposals_pending_idx(1716): partial btree (source_id,status,proposed_at DESC) WHEREstatus='pending'.take_proposals_run_id_idx(1719): btree (proposal_run_id).
Readers/writers: core/take-proposals.ts (7, main), core/cycle/propose-takes.ts (4), core/cycle/propose-takes-failures.ts (3), core/facts/purge.ts, core/facts/purge-verify.ts. Migrations v069, v125. RLS: in DO list.
take_grade_cache (schema.sql:1727-1747)
Purpose: grade_takes verdict cache. Composite PK means prompt, judge model or evidence change invalidates cleanly. applied=false by default (D17): grades do not mutate takes until an operator opts in.
| Column | Type | Null / default | Notes |
|---|---|---|---|
| take_id | BIGINT | NOT NULL | PK part; no FK in range |
| prompt_version | TEXT | NOT NULL | PK part |
| judge_model_id | TEXT | NOT NULL | PK part |
| evidence_signature | TEXT | NOT NULL | PK part |
| wave_version | TEXT | NOT NULL DEFAULT 'v0.36.1.0' | |
| graded_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| verdict | TEXT | NOT NULL | CHECK in (correct, incorrect, partial, unresolvable) |
| confidence | REAL | NOT NULL | |
| applied | BOOLEAN | NOT NULL DEFAULT false | |
| cost_usd | NUMERIC(10,4) | NULL |
Constraints: PK (take_id, prompt_version, judge_model_id, evidence_signature).
Indexes: take_grade_cache_applied_idx (1741) btree (take_id, applied); take_grade_cache_wave_idx (1743) btree (wave_version, graded_at DESC).
Readers/writers: core/cycle/grade-takes.ts (3), core/calibration/undo-wave.ts (3), core/engine-sql/takes.ts (1), commands/takes.ts, core/forecast-journal.ts, commands/doctor/checks/calibration.ts, core/cycle/phase-table.ts. Migration v070. RLS: in DO list.
take_nudge_log (schema.sql:1749-1770)
Purpose: E7 nudge cooldown state. A nudge targets either a take or a pending proposal, never both (CDX-5).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| take_id | BIGINT | NULL | |
| proposal_id | BIGINT | NULL | FK take_proposals(id) ON DELETE CASCADE |
| nudge_pattern | TEXT | NOT NULL | |
| fired_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| channel | TEXT | NOT NULL DEFAULT 'stderr' | |
| wave_version | TEXT | NOT NULL DEFAULT 'v0.36.1.0' |
Constraints: take_nudge_log_target_xor CHECK ((take_id IS NOT NULL) <> (proposal_id IS NOT NULL)).
Indexes: take_nudge_log_take_cooldown_idx (1761) partial btree (take_id, nudge_pattern, fired_at DESC) WHERE take_id IS NOT NULL; take_nudge_log_proposal_cooldown_idx (1764) partial btree (proposal_id, nudge_pattern, fired_at DESC) WHERE proposal_id IS NOT NULL; take_nudge_log_wave_idx (1767) btree (wave_version, fired_at DESC).
Readers/writers: core/calibration/undo-wave.ts (2), core/calibration/templates.ts. Migration v071. RLS: in DO list.
think_ab_results (schema.sql:1772-1786)
Purpose: A/B harness rows for think (baseline vs with-calibration answers).
| Column | Type | Null / default | Notes |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NOT NULL | FK sources(id) ON DELETE CASCADE |
| wave_version | TEXT | NOT NULL DEFAULT 'v0.36.1.0' | |
| ran_at | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| question | TEXT | NOT NULL | |
| baseline_answer | TEXT | NOT NULL | |
| with_calibration_answer | TEXT | NOT NULL | |
| preferred | TEXT | NOT NULL | CHECK in (baseline, with_calibration, neither, tie) |
| model_id | TEXT | NULL | |
| notes | TEXT | NULL |
Indexes: think_ab_results_recent_idx (1784) btree (source_id, ran_at DESC).
Readers/writers: core/calibration/think-ab.ts (2). Migration v073. RLS: not in DO list at 1834-1865 (grep did not list it there).
Row-Level Security coverage in range (schema.sql:1834-1865)
RLS is enabled (conditionally, only when the role has BYPASSRLS or superuser; comment at 1829-1833 says a role without it would be locked out) on these 35 tables. No policies are created here.
pages, content_chunks, links, wanted_links, tags, raw_data, timeline_entries, page_versions, ingest_log, config, files, minion_jobs, sources, file_migration_ledger, access_tokens, mcp_request_log, minion_inbox, minion_attachments, subagent_messages, subagent_tool_executions, subagent_rate_leases, gbrain_cycle_locks, dream_verdicts, eval_candidates, eval_capture_failures, eval_takes_quality_runs, eval_contradictions_cache, eval_contradictions_runs, calibration_profiles, take_proposals, take_grade_cache, take_nudge_log, oauth_clients, oauth_tokens, oauth_codes.
Not in the DO list (RLS not enabled by this block): page_retrievals, code_edges_chunk, code_edges_symbol, op_checkpoints, op_checkpoint_paths, context_volunteer_events, retrieval_events, retrieval_event_pages, retrieval_event_links, retrieval_feedback, retrieval_weights, session_context_state, extract_atoms_transcript_state, chat_usage_log, open_loops, loop_suppressions, fact_withdrawals, oauth_grant_audit, migration_impact_log, think_ab_results. Comments at 1034-1035 say session_context_state (v126), extract_atoms_transcript_state and chat_usage_log (v140) get RLS from the v35 auto_rls_on_create_table event trigger, which is outside this range. The later backfill is core/schema-migrations/v275-rls-backfill-unguarded-tables.ts.
Handoff notes for part 2
- Part 2 begins at schema.sql:1899 with
page_write_guardsand continues to the end of the file (out of scope here). - Out-of-range ALTERs on tables covered above:
page_versions.source_path(about schema.sql:1966);sources.upstream_checked_at,upstream_commit,upstream_behind(about schema.sql:3559-3561). - Comments reference out-of-range objects: the
auto_rls_on_create_tableevent trigger (v35), the facts index and withdrawal trigger (migrations 60 and 148, comment at 826), andpage_write_guards.
Verification notes
- Object counts were derived by
rgover lines 1-1898 (56CREATE TABLE, 114CREATE [UNIQUE] INDEX, 8CREATE OR REPLACE FUNCTION, 7CREATE TRIGGER, 1CREATE SEQUENCE, 3 extensions, 0 views, 0 policies, 0 grants, 0COMMENT ON). - Reader and writer lists are from SQL-keyword pattern counts (
FROM|INTO|UPDATE|JOIN|TABLE|ON <table>) acrosssrc/, excludingschema-migrations/and the*.generated.tscopies. Counts are approximate and the lists show the main files, not every call site. - Not checked: whether
src/core/pglite-schema.generated.tsandsrc/core/schema-embedded.generated.tsmatch this file line for line. The*.generated.tscopies were excluded from reader and writer counts. - Not checked: the migration ordinal order of migration-added columns in
core/migrate.ts. The Rust fresh-install column order is taken from the comments in the schema.
Parity risks noted
- Global generation clock is a sequence.
page_generation_clock_seqis advanced bynextvalin a statement trigger on every INSERT, UPDATE or DELETE onpages(schema.sql:267-278). Itssetvalseed uses GREATEST over its ownlast_value, the legacy table andMAX(pages.generation)(260-265), so replays never move it backward. An embedded store needs a persisted, monotonic counter that never regresses across restarts, and it must bump once per statement rather than per row. - Per-row generation rule.
bump_page_generation_fn(182-205) setsgeneration = MAX+1on insert and bumps only when a specific column allow-list changes (IS DISTINCT FROM). Any Rust port must reproduce the allow-list exactly, or query-cache invalidation will be wrong. - Search vector depends on another table, updated only on page write.
update_page_search_vector(1196-1228) readstimeline_entriesinside the trigger, but the trigger fires only onpagesUPDATE OF title, timeline. A timeline_entries change does not refreshpages.search_vectoruntil the page is written. Caps are 65536 and 32768 characters. - Full-text uses Postgres
to_tsvector('english'),setweight,||and GIN.gbrain_fts_input(432-436) inserts spaces at CJK/ASCII boundaries before tokenizing. Stemming and stop words are Postgres-specific; ranking parity needs a test.ts_filter(tsvector, '{a}')(1187) is a Postgres built-in (PG 14+) with no repo definition. - Chunk-grain FTS vs page-grain FTS.
content_chunks.search_vector(weights A/B) is the onesearchKeyword()queries.pages.search_vectordeliberately excludescompiled_truthbecause of the 1MB tsvector cap (comment near 1190-1194, #2704). - pgvector HNSW.
vector(1536)onembeddingwithvector_cosine_ops(408); partial HNSW onembedding_imagevector(1024)(414-415).embedding_multimodal vector(1024)(no index in range). Dimensions are enforced per column; a Rust ANN index must do the same and handle partial coverage. - Trigram indexes.
gin_trgm_opsonpages.title(282) andpages.slug(286) back fuzzy entity lookup. Needs an equivalent fuzzy-match path. NULLS NOT DISTINCTunique constraint onlinks(PG15+, 579-583): NULLlink_sourceand NULLorigin_page_idcollide as equal. Rust must treat NULLs as equal for this dedup key.- Expression-keyed unique indexes.
timeline_entriesdedup onmd5(summary)(670) andtake_proposalsidempotency onmd5(claim_text)(1714). TheON CONFLICTinference depends on these exact expressions, and the note says the md5 exists to avoid the btree row-size cap (#3737). - JSONB partial and expression indexes.
config->>'github_repo'WHEREconfig ? 'github_repo'(88-90);payload->>'type'WHERE= 'child_done'(1333);completed_keys->0->>'page_id'WHEREop='sync-hold'(892-893). Alsojsonb_typeof(completed_keys)='array'(885-886) andgbrain_try_jsonb(64-72) swallowingdata_exception. - Regex CHECK in the database.
links.link_sourceuses~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'pluschar_length <= 64(line 560). Rust must enforce the same predicate in code. - ON DELETE semantics differ by table. CASCADE on most child tables (pages children, chunks, code edges, sub-agent tables); SET NULL on
files.page_id,links.origin_page_id,migration_impact_log.job_id,minion_jobs.parent_job_id,minion_jobs.private_queue_owner_job_id; RESTRICT onoauth_clients.source_id(line 777). Source deletion and purge flows rely on these, so an embedded store must reproduce them or move them into code. - Deliberate FK omissions.
page_retrievalshas no FK (comment 300-307) and is cleaned by a statement trigger with a transition table (REFERENCING OLD TABLE, 331-334).retrieval_*,context_volunteer_events,session_context_state,extract_atoms_transcript_state,ingest_log,open_loops.fact_idand severalsource_idcolumns have no FK. Rust should keep this exact set, not add FKs. - Minion queue protocol enforced by trigger.
enforce_minion_queue_protocol(1789-1807) raises on protocol violations: an insert with NULLsubmission_authorityor non-zeroclaim_generation, a claim withoutclaim_generation = OLD+1, and spend-authorized claims from old workers. Claim atomicity and rejection of old workers must be reproduced in Rust code. pg_notifyon job changes.minion_job_notify(1813-1825) is Postgres-only (comment at 1812 says not PGLite). Rust needs an in-process or polling equivalent for supervisor wake-ups.- Lease with UUID token and TTL.
gbrain_cycle_locks(1516-1532) avoids session advisory locks for PgBouncer transaction pooling. An embedded store has no pooler, but must keep the TTL and token semantics (last_refreshed_at,acquisition_token). - RLS is a Postgres security layer. The DO block (1834-1865) enables RLS on 35 tables with no policies, conditionally on BYPASSRLS/superuser. The trigger guards (317-330, 1202-1212) raise
writer_coordinator_requiredwhen RLS state is unreadable. Rust has no RLS; it needs an equivalent access check or an explicit decision to drop it. - Serial and bigserial ids cross the boundary. Most tables use
SERIAL(int4) ids.links,content_chunks,retrieval_events(TEXT id, not serial),eval_*andminion_jobsids are referenced by other tables and by exports. Rust must keep compatible id types and sequences for migration and export. - Defaults that call the database.
now()/NOW()for timestamps,gen_random_uuid()foraccess_tokens.id,sources.incarnation,pages.knowledge_revisionandgbrain_cycle_locks.acquisition_token, andnow() + interval '30 days'fordream_verdicts.expires_at. Rust must generate these in code with the same semantics. knowledge_revisionNOT NULL is conditional. The DO block (1880-1891) sets NOT NULL only whenpagesis empty. Existing rows may be NULL. Rust fresh-install and upgraded brains must accept both.- Float width mismatch. Many scores use
REAL(float4) whilecontext_volunteer_events.confidence,dream_verdicts.scoreandchat_usage_log.cost_usduseDOUBLE PRECISION.NUMERIC(10,2)budgets andNUMERIC(10,4)costs elsewhere. Rounding parity must be tested per column. eval_candidates.querylength CHECK (1546):length(query) <= 51200counts characters in Postgres. A Rust byte-length check would differ for multi-byte text.- Array columns and GIN.
TEXT[]andINTEGER[](scopes, slugs,federated_read,active_bias_tags,parent_symbol_path) with GIN onoauth_clients.federated_read(808) andcalibration_profiles.active_bias_tags(1682). Array containment semantics must be reproduced. - Migration-added columns not in the fresh DDL. About 20 columns across
pages,content_chunks,page_versions,minion_jobsare declared only in comments or by migrations (see the top-level table). A Rust fresh-install must declare them with the same ordinals, or upgraded and fresh brains will disagree. - Trigger-driven writes that the engine must not duplicate. The generation bump and
search_vectorare triggers, so the engine writes only the content columns. A Rust port must do these in the write path, and must do them within the same transaction. - Dropped trigger cleanup.
trg_timeline_search_vectorandupdate_page_search_vector_from_timeline()are dropped (1242-1243), and the code comments warn against double-weighting timeline text. Rust must not reintroduce the timeline_entries weighting. - Soft-delete and purge windows are app-enforced.
pages.deleted_at(purge after 72h, 292-296),sources.archive_expires_at,dream_verdicts.expires_at(30 days),eval_contradictions_cache.expires_at,context_volunteer_events(90-day prune, comment at 913),op_checkpoints(7-day GC). No DB-side job; the Rust port must replicate the sweeps.
Tables and objects, part 2 (schema.sql lines 1899-3794)
Scope: src/schema.sql lines 1899-3794 (end of file). Line numbers below are schema.sql lines. Readers and writers come from a grep of src/ (TS, excluding *.generated.ts and tests) for FROM|JOIN (readers) and INSERT INTO|UPDATE|DELETE FROM|ON CONFLICT (writers). Each entry lists the first few matching files, not every call site. Treat the lists as a map to start from, not an exhaustive census.
Column notation: NN = NOT NULL, D= = default, PK = primary key, FK-> = foreign key, CK = CHECK constraint, UQ = unique.
Generated regions in this range
The file marks generated regions with -- BEGIN GENERATED from <module> (<CONST>) and -- END GENERATED .... scripts/build-schema.ts (lines 1-40 and the import block) rewrites these regions from the TS fragment modules. Edit the module, then run bun run build:schema. Hand-written text goes outside the markers.
| schema.sql lines | Source module (constant) | Objects in region |
|---|---|---|
| 1866-1962 | src/core/page-state/schema.ts (PAGE_STATE_SCHEMA_SQL) |
table page_write_guards (1899), functions gbrain_advance_page_revision (1904), gbrain_advance_tag_revision (1937), triggers pages_knowledge_revision (1934), tags_knowledge_revision (1959) |
| 2160-2246 | src/core/page-body/schema.ts (PAGE_BODY_SCHEMA_SQL) |
sequence page_body_occurrence_id_seq, tables page_segment_texts, page_body_manifest, page_body_stagings, page_body_staging_parts, page_body_staging_texts |
| 2249-2383 | src/core/page-body/schema.ts (PAGE_SEGMENT_DERIVATIONS_SQL) |
tables page_segment_derivations, page_derivation_spend, page_segment_derivation_purges, page_segment_derivation_purge_pages; functions gbrain_mark_derivation_scope_entry, gbrain_refuse_purging_derivation; triggers on page_body_manifest and pages |
| 2385-2398 | src/core/page-body/schema.ts (PAGE_BODY_RLS_SQL) |
DO $rls$ block enabling RLS on the page-body tables |
| 2401-2640 | src/core/persistence/schema.ts (PERSISTENCE_SCHEMA_STATEMENTS) |
tables persistence_brain, persistence_worktrees, persistence_source_bindings, persistence_host_bindings, persistence_local_writers, persistence_counters, persistence_requests, persistence_effects, persistence_consumers; function gbrain_require_managed_writer; DO $body$ attaching trigger managed_writer_guard to 10 tables |
| 2642-2680 | src/core/persistence/git-canonical-schema.ts (GIT_CANONICAL_SCHEMA_SQL) |
table git_canonical_brain, function gbrain_count_git_canonical_sources, 3 triggers on sources, DO $rls$ |
| 2682-2767 | src/core/page-state/projection-schema.ts (PAGE_PROJECTION_SCHEMA_SQL) |
table page_projection_jobs, function gbrain_queue_page_projection, triggers trg_pages_search_vector (a DO block picks the column list), pages_projection_queue |
| 2770-2792 | src/core/persistence/topology-schema.ts (PERSISTENCE_TOPOLOGY_SCHEMA_SQL) |
table persistence_topology_changes (plus an ALTER ADD COLUMN) |
| 2794-2817 | src/core/persistence/worktree-refresh-schema.ts (WORKTREE_REFRESH_SCHEMA_SQL) |
table persistence_worktree_refreshes |
| 2819-2851 | src/core/company-brain/receipt-schema.ts (SOURCE_INGESTION_RECEIPTS_SCHEMA_SQL) |
table source_ingestion_receipts (plus an ALTER ADD COLUMN) |
| 2853-3098 | src/core/shared-skills/schema-all.ts (SHARED_SKILLS_SCHEMA_SQL) |
tables shared_skill_state, shared_skill_policies, shared_skill_packs, shared_skill_policy_audit, shared_skill_heads, shared_skill_revisions, shared_skill_revision_leases, shared_skill_members, shared_skill_delivery_batches, persistence_writer_protocols; ALTERs on persistence_brain and persistence_requests; functions gbrain_require_persistence_protocol, gbrain_guard_request_protocol, gbrain_guard_effect_protocol, gbrain_guard_protocol_activation, gbrain_guard_skill_publication, gbrain_lease_shared_skill_delivery; DO $$ RLS block |
| 3101-3294 | src/core/ai/decide/schema.ts (DECIDE_SCHEMA_SQL) |
tables decision_receipts, decide_spend, decide_state, decide_calibrations, decide_proposals, decide_sweep_deferred, decide_review_queue, decide_review_proposals |
| 3297-3316 | src/core/facts/relink-schema.ts (FACT_RELINK_SCHEMA_SQL) |
table fact_relink_attempts |
| 3319-3400 | src/core/link-temporal-schema.ts (LINK_TEMPORAL_SCHEMA_SQL) |
tables link_transitions, link_relationships, link_edge_proposals; sequence graph_generation_seq |
| 3403-3422 | src/core/core-memory-schema.ts (CORE_EDIT_NOTICES_SCHEMA_SQL) |
table core_edit_notices |
| 3425-3497 | src/core/facts/purge-schema.ts (MEMORY_PURGE_SCHEMA_SQL) |
tables fact_purges, take_purges, page_purges, derivation_inputs, needs_rederive |
| 3499-3502 | src/core/facts/purge-schema.ts (FACT_PURGE_LOOKUP_INDEX_SQL) |
two indexes on fact_purges |
| 3505-3553 | src/core/write-gate-schema.ts (WRITE_GATE_SCHEMA_SQL) |
tables write_gate_receipts, write_gate_holds |
| 3564-3626 | src/core/questions/schema.ts (PINNED_QUESTIONS_SCHEMA_SQL) |
tables pinned_questions, question_evidence |
| 3629-3728 | src/core/belief/schema.ts (BELIEF_LEDGER_SCHEMA_SQL) |
tables belief_events, belief_page_lifetimes, belief_commit_marks, belief_retier_manifest, belief_redactions |
| 3780-3794 | src/core/function-search-path.ts (FUNCTION_SEARCH_PATH_HARDENING_SQL) |
DO $harden$ that runs ALTER FUNCTION ... SET search_path on about 60 named gbrain_* and trigger functions |
Hand-written (outside any marker) in this range: lines 1966-2158 (ALTER page_versions, extract_atoms_page_state, derived_generation* tables, page_embedding_budget, chronicle_*, dream_synthesis_completions, page_extraction_state, page_view_digests); lines 3559-3561 (ALTER sources upstream columns); lines 3730-3777 (sync_wanted_markers, sync_notifier_leases, sync_notifier_sources). The ordering comments say that the page-state region must come before the persistence region, and that the search_path hardening DO block must run last (3779).
Page state and revision guards
page_write_guards
Source: schema.sql:1899 (generated, page-state/schema.ts). Presence row that serializes page-key writes per source incarnation, used by the write-lock helpers.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_incarnation | UUID | NN | FK -> sources(incarnation) ON DELETE CASCADE; PK part |
| slug | TEXT | NN | PK part |
- Indexes: PK
(source_incarnation, slug). - Readers:
src/core/page-state/guards.ts(SELECT ... FOR UPDATE OF g, joined tosources). - Writers:
src/core/page-state/guards.ts(INSERT ... ON CONFLICT DO NOTHING, line 46, 64, 75).
Objects in the page-state region (generated)
- Function
gbrain_advance_page_revision()(1904-1933, trigger fn, plpgsql). Onpagesupdate, setsknowledge_revisionto a newgen_random_uuid()when identity columns change (source_id, slug, type, page_kind, title, compiled_truth, timeline, frontmatter, deleted_at, orbody_generationmoves). It also clearstext_projection_revision. Agbrain.materializing_revisionGUC can suppress the bump. - Trigger
pages_knowledge_revisionBEFORE UPDATE ONpages(1934-1936). - Function
gbrain_advance_tag_revision()(1937-1958). Ontagsinsert/update/delete, it bumpspages.knowledge_revisionfor the affected page. Before that, it checks RLS: ifrow_security_active('pages')and the role lacks a permissive policy forrandw, it raiseswriter_coordinator_required. - Trigger
tags_knowledge_revisionAFTER INSERT OR DELETE OR UPDATE ONtags(1959-1961).
Derived state, extraction and budgets (hand-written)
page_versions (ALTER only)
Source: schema.sql:1966. ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS source_path TEXT (#5393, migration v182). The table itself is defined earlier in the file. Stores the canonical file path recorded when a version was taken.
extract_atoms_page_state
Source: schema.sql:1967-1981. Per-(source incarnation, page, content hash) fail counter and tombstone for the atom-extraction pipeline. partial_input_chars was added by #6503 (v240).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_incarnation | UUID | NN | FK -> sources(incarnation) ON DELETE CASCADE; PK |
| page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK |
| content_hash | TEXT | NN | PK |
| fail_count | INTEGER | NN, D=0 | CK >= 0 |
| tombstoned | BOOLEAN | NN, D=false | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| partial_input_chars | INTEGER | null | cap a cut page was read under (#6503) |
- Indexes: PK
(source_incarnation, page_id, content_hash); partialextract_atoms_page_state_tombstoned_idx (source_incarnation, content_hash, page_id) WHERE tombstoned;extract_atoms_page_state_page_idx (page_id). - Readers:
src/core/cycle/extract-atoms.ts,src/core/persistence/atom-maintenance.ts,src/core/derived-state-maintainers.ts,src/core/cycle/extract-atoms-page-state.ts. - Writers:
src/core/cycle/extract-atoms-page-state.ts,src/core/cycle/extract-atoms-input.ts,src/core/persistence/atom-maintenance.ts,src/core/derived-state-maintainers.ts.
derived_generations
Source: schema.sql:1984-2013 (GBRA-161 generation ledger, see src/core/generations/). One row per generation of a derived output set for (source_id, kind, owner_key).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE |
| kind | TEXT | NN | |
| owner_key | TEXT | NN | |
| generation | INTEGER | NN, D=0 | |
| state | TEXT | NN | CK in (live,held,superseded) |
| run_id | TEXT | null | |
| operation_id | TEXT | NN | |
| hold_id | TEXT | null | |
| input_pin | TEXT | null | |
| input_size | BIGINT | null | |
| completeness | JSONB | NN, D='{}' | |
| item_set_hash | TEXT | NN | |
| held_reason | TEXT | null | CK in (shrunk,oversize,valve,no_proof,fence_absent,attempts_exhausted,segmented_unsupported,tie) |
| held_context | JSONB | null | |
| proposal | JSONB | null | |
| proposal_bytes | INTEGER | null | |
| attempts | INTEGER | NN, D=0 | |
| superseded_by | BIGINT | null | |
| restore_of | BIGINT | null | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| locator | TEXT | null | |
| activation_id | TEXT | null |
- Indexes: UNIQUE partial
derived_generations_one_live (source_id, kind, owner_key) WHERE state='live'; UNIQUE partialderived_generations_one_hold (...) WHERE state='held';derived_generations_operation (operation_id);derived_generations_state_created (state, created_at). - Readers:
src/core/repair/unwithdrawn-facts.ts,src/core/repair/lost-fence-rows.ts,src/core/generations/ledger-scrub.ts,src/core/generations/fence-restore.ts. - Writers:
src/core/generations/publish.ts,src/core/generations/fence.ts,src/core/generations/ledger-scrub.ts,src/core/generations/adapters/fact-rows.ts.
derived_generation_items
Source: schema.sql:2014-2025. Per-item action record inside a generation (published, retired, revived, kept, adopted), with a preimage for rollback.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| generation_id | BIGINT | NN | FK -> derived_generations(id) ON DELETE CASCADE; PK part |
| item_key | TEXT | NN | PK part |
| identity_hash | TEXT | NN | |
| action | TEXT | NN | CK in (published,retired,revived,kept,adopted) |
| preimage | JSONB | null | |
| preimage_bytes | INTEGER | null | |
| pending | BOOLEAN | NN, D=false |
- Indexes: PK
(generation_id, item_key);derived_generation_items_key (item_key); partialderived_generation_items_pending (generation_id) WHERE pending. - Readers:
src/core/generations/repair.ts,src/core/generations/fence-evidence.ts,src/core/repair/unwithdrawn-facts.ts,src/core/repair/lost-fence-rows.ts. - Writers:
src/core/generations/managed.ts,src/core/generations/publish.ts,src/core/generations/ledger-scrub.ts.
derived_generation_valve
Source: schema.sql:2026-2033. Per-(source, kind, window) retired counter, used as a rate valve.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE; PK part |
| kind | TEXT | NN | PK part |
| window_key | TEXT | NN | PK part |
| retired | INTEGER | NN, D=0 | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: PK
(source_id, kind, window_key). - Readers and writers:
src/core/generations/publish.ts(both).
derived_generation_attempts
Source: schema.sql:2034-2043. Attempt counter and reason codes per held generation.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| hold_id | TEXT | PK | |
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE |
| kind | TEXT | NN | |
| owner_key | TEXT | NN | |
| attempts | INTEGER | NN, D=0 | |
| codes | JSONB | NN, D='[]' | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: PK
(hold_id);derived_generation_attempts_key (source_id, kind, owner_key). - Readers and writers:
src/core/generations/publish.ts(both).
derived_generation_activations
Source: schema.sql:2044-2058. Pending or activated handoff between an expected generation and the generation row it became.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | TEXT | PK | |
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE |
| kind | TEXT | NN | |
| owner_key | TEXT | NN | |
| expected_generation | INTEGER | NN | |
| input_pin | TEXT | null | |
| item_set_hash | TEXT | NN | |
| state | TEXT | NN | CK in (pending,activated) |
| generation_id | BIGINT | null | FK -> derived_generations(id) ON DELETE SET NULL |
| members | JSONB | NN, D='[]' | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Indexes:
derived_generation_activations_key (source_id, kind, owner_key, state). - Readers:
src/core/generations/managed.ts,src/core/generations/adapters/atoms.ts,src/core/persistence/atom-maintenance.ts. - Writers:
src/core/generations/managed.ts.
page_embedding_budget
Source: schema.sql:2059-2074 (migration v246). Per-page embedding spend ledger. Held pages wait for approval. Capped pages are embedded partially up to embed.max_usd_per_page.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| page_id | INTEGER | PK | FK -> pages(id) ON DELETE CASCADE |
| state | TEXT | NN, D='open' | CK in (open,held,capped) |
| reason | TEXT | null | |
| embedding_signature | TEXT | null | |
| spent_usd | DOUBLE PRECISION | NN, D=0 | CK >= 0 |
| reserved_usd | DOUBLE PRECISION | NN, D=0 | CK >= 0 |
| reserved_at | TIMESTAMPTZ | null | |
| unpriced_calls | INTEGER | NN, D=0 | CK >= 0 |
| cap_usd | DOUBLE PRECISION | null | CK null or >= 0 |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: partial
page_embedding_budget_state_idx (state) WHERE state <> 'open'. - Readers:
src/core/held-embedding.ts,src/core/page-size-policy.ts,src/commands/pages-status.ts,src/core/search/retrieval-coverage.ts. - Writers:
src/core/page-embedding-budget.ts,src/core/transcripts/archive.ts,src/core/oversized-backfill.ts.
chronicle_page_state
Source: schema.sql:2077-2114 (#5876, plus #6199 and #8972 columns). Life Chronicle ledger: one row per page content hash and extractor version, recording the automatic-extraction decision and outcome.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE; PK part |
| page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| content_hash | TEXT | NN | PK part |
| extractor_version | INTEGER | NN | PK part |
| slug | TEXT | NN | |
| state | TEXT | NN | CK in (pending,skipped,extracted,failed) |
| reason | TEXT | null | |
| trigger | TEXT | NN | CK in (auto,backfill) |
| principal_kind | TEXT | null | |
| principal_id | TEXT | null | |
| request_id | UUID | null | |
| no_extract | BOOLEAN | NN, D=false | |
| attempts | INTEGER | NN, D=0 | CK >= 0 |
| next_attempt_at | TIMESTAMPTZ | null | |
| cost_usd | NUMERIC | null | |
| unpriced | BOOLEAN | NN, D=false | |
| event_slugs | TEXT[] | NN, D='{}' | |
| event_hashes | TEXT[] | NN, D='{}' | |
| decided_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| campaign_id | TEXT | null | |
| attempt_cap_usd | NUMERIC | null | |
| max_attempts | INTEGER | null | CK > 0 |
| pricing_policy | TEXT | null | |
| campaign_max_usd | NUMERIC | null | |
| cost_attempts | INTEGER[] | NN, D='{}' | |
| judge_input_hash | TEXT | null | |
| judge_window_chars | INTEGER | null | CK >= 0 |
| judge_window_hash | TEXT | null | |
| judge_input_failures | INTEGER | NN, D=0 | CK >= 0 |
- PK:
(source_id, page_id, content_hash, extractor_version). - Indexes:
chronicle_page_state_work_idx (source_id, state, next_attempt_at);chronicle_page_state_page_idx (page_id). - Readers:
src/core/chronicle/ledger.ts,src/core/chronicle/ledger-stats.ts,src/core/chronicle/campaign.ts,src/core/chronicle/execute.ts. - Writers:
src/core/chronicle/ledger.ts,src/core/chronicle/campaign.ts,src/core/chronicle/execute.ts,src/core/cycle/chronicle.ts.
chronicle_judge_reservations
Source: schema.sql:2115-2122. Reservation log for judge calls, one row per reservation.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| reserved_at | TIMESTAMPTZ | NN, D=now() | |
| source_id | TEXT | NN | no FK |
| page_id | INTEGER | NN | no FK |
| content_hash | TEXT | NN |
- Indexes:
chronicle_judge_reservations_at_idx (reserved_at). - Readers:
src/core/chronicle/ledger-stats.ts,src/core/chronicle/ledger.ts. - Writers:
src/core/chronicle/ledger.ts.
dream_synthesis_completions
Source: schema.sql:2124-2129. Durable record that a transcript was synthesized. It survives minion_jobs pruning.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | PK part; no FK |
| idempotency_key | TEXT | NN | PK part |
| completed_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: PK
(source_id, idempotency_key). - Readers:
src/core/cycle/synthesize-completion.ts. - Writers:
src/core/minions/queue.ts.
page_extraction_state
Source: schema.sql:2132-2143 (v247, Large pages A7). Per-page, per-pipeline extraction watermark and receipt for a partial-input extraction.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| pipeline | TEXT | NN | PK part; CK in (facts_backstop,extract_atoms,conversation_facts) |
| pipeline_version | INTEGER | NN | |
| watermark_bytes | BIGINT | null | CK >= 0 |
| watermark_sha256 | TEXT | null | |
| input_truncated | JSONB | null | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: PK
(page_id, pipeline); partialpage_extraction_state_truncated_idx (pipeline, page_id) WHERE input_truncated IS NOT NULL. - Readers:
src/core/extraction-watermark.ts,src/commands/doctor/checks/extraction-input.ts. - Writers:
src/core/extraction-watermark.ts.
page_view_digests
Source: schema.sql:2146-2156 (v253, Wave B segmented pages). Size and digest of get_page views per page revision and audience.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| revision | TEXT | NN | PK part |
| view | TEXT | NN | PK part; CK in (read,markdown) |
| audience | TEXT | NN | PK part; CK in (trusted,remote) |
| total_bytes | BIGINT | NN | CK >= 0 |
| total_lines | BIGINT | NN | CK >= 0 |
| sha256 | TEXT | null | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: PK
(page_id, revision, view, audience). - Readers:
src/core/page-window/segmented.ts,src/core/facts/purge.ts,src/core/repair/secret-spans.ts. - Writers:
src/core/page-window/segmented.ts,src/core/repair/secret-spans.ts,src/core/facts/purge.ts.
Segmented page body (generated, page-body/schema.ts)
page_segment_texts
Source: schema.sql:2162-2170. Content-addressed segment text store, keyed by (source_id, sha256).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE; PK part |
| sha256 | BYTEA | NN | PK part; CK octet_length = 32 |
| byte_len | INTEGER | NN | CK BETWEEN 1 AND 1048576 |
| line_count | INTEGER | NN | CK >= 0 |
| body | BYTEA | NN | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: PK
(source_id, sha256). - Readers:
src/core/engine-sql/pages.ts,src/core/engine-sql/page-body.ts,src/core/facts/withdrawal-discovery.ts,src/core/facts/purge.ts. - Writers:
src/core/engine-sql/pages.ts,src/core/engine-sql/page-body.ts,src/core/persistence/page-purge.ts,src/core/facts/purge.ts.
page_body_manifest
Source: schema.sql:2171-2202. Generation-ranged manifest: each row is a cut of a page body (one segment) valid for [gen_from, gen_to). A staged row carries staging_id.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| occurrence_id | BIGINT | NN | PK part (from page_body_occurrence_id_seq) |
| gen_from | BIGINT | NN | PK part |
| gen_to | BIGINT | null | open when null |
| seq | INTEGER | NN | |
| region | TEXT | NN | CK in (truth,timeline) |
| source_id | TEXT | NN | FK (composite) -> page_segment_texts(source_id, sha256) |
| sha256 | BYTEA | NN | |
| byte_start | BIGINT | NN | |
| byte_len | INTEGER | NN | |
| line_start | BIGINT | NN | |
| line_count | INTEGER | NN | |
| cut_kind | TEXT | NN | CK in (heading,day,message,blank,forced,region_end,eof) |
| entry_state | JSONB | NN | |
| protected_regions | JSONB | NN, D='[]' | |
| facts_slots | JSONB | NN, D='[]' | |
| static_proj_bytes | BIGINT | NN | |
| static_proj_lines | INTEGER | NN | |
| outline | JSONB | NN, D='[]' | |
| date_min | DATE | null | |
| date_max | DATE | null | |
| hash_state | BYTEA | NN | |
| staging_id | UUID | null | |
| staging_key | UUID | GENERATED ALWAYS AS (COALESCE(staging_id, nil-uuid)) STORED | used in the PK so NULL staging is comparable |
- PK:
(page_id, occurrence_id, gen_from, staging_key). - Indexes: UNIQUE partial
page_body_manifest_live_seq (page_id, seq) WHERE gen_to IS NULL AND staging_id IS NULL; partialpage_body_manifest_live_bytes (page_id, byte_start) WHERE gen_to IS NULL;page_body_manifest_text (source_id, sha256); partialpage_body_manifest_staging (staging_id) WHERE staging_id IS NOT NULL. - Readers:
src/core/engine-sql/pages.ts,src/core/engine-sql/page-body.ts,src/core/sweep.ts,src/core/repair/split-facts.ts. - Writers:
src/core/engine-sql/page-body.ts,src/core/page-body/segment-rewrite.ts,src/core/persistence/segmented-apply.ts. - Trigger hook:
page_body_manifest_derivation_scope_retireand_delete(see below), plus the managed-writer guard.
page_body_stagings
Source: schema.sql:2203-2229. Upload or edit staging session: a declared size and sha, parts accumulate, then a commit moves it to the manifest.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK | |
| kind | TEXT | NN | CK in (upload,import,sync,put,append,edit,resegment) |
| principal_kind | TEXT | NN | |
| principal_id | TEXT | NN | |
| client_request_id | UUID | NN | UQ with principal |
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE |
| source_incarnation | UUID | NN | no FK |
| slug | TEXT | NN | |
| page_id | INTEGER | null | FK -> pages(id) ON DELETE CASCADE |
| mode | TEXT | NN | CK in (create,replace,append) |
| base_revision | UUID | null | |
| base_generation | BIGINT | null | |
| declared_bytes | BIGINT | NN | CK >= 0 |
| declared_sha256 | BYTEA | null | |
| received_bytes | BIGINT | NN, D=0 | |
| next_part | INTEGER | NN, D=0 | |
| reserved_bytes | BIGINT | NN | |
| state | TEXT | NN, D='open' | CK in (open,sealed,prepared,committed,aborted,expired) |
| commit_request_id | UUID | null | |
| idle_expires_at | TIMESTAMPTZ | NN | |
| hard_expires_at | TIMESTAMPTZ | NN | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- UNIQUE
(principal_kind, principal_id, client_request_id). - Indexes: partial
page_body_stagings_expiry (hard_expires_at) WHERE state IN ('open','sealed','prepared'). - Readers:
src/core/engine-sql/pages.ts,src/core/engine-sql/page-body.ts,src/core/page-body/schema.ts,src/core/facts/purge.ts. - Writers:
src/core/ops/page-upload.ts,src/core/engine-sql/pages.ts,src/core/engine-sql/page-body.ts,src/core/facts/purge.ts.
page_body_staging_parts
Source: schema.sql:2230-2238. Byte parts of a staging upload.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| staging_id | UUID | NN | FK -> page_body_stagings(id) ON DELETE CASCADE; PK part |
| part_no | INTEGER | NN | PK part; CK >= 0 |
| byte_start | BIGINT | NN | |
| byte_len | INTEGER | NN | CK BETWEEN 1 AND 4194304 |
| sha256 | BYTEA | NN | |
| body | BYTEA | NN |
- Indexes: PK
(staging_id, part_no). - Readers:
src/core/engine-sql/page-body.ts,src/core/persistence/limits.ts,src/core/persistence/page-upload.ts,src/core/facts/purge.ts. - Writers:
src/core/engine-sql/page-body.ts,src/core/persistence/page-upload.ts,src/core/persistence/limits.ts,src/core/facts/purge.ts.
page_body_staging_texts
Source: schema.sql:2239-2245. Segment texts that a staging will reference.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| staging_id | UUID | NN | FK -> page_body_stagings(id) ON DELETE CASCADE; PK part |
| source_id | TEXT | NN | |
| sha256 | BYTEA | NN | PK part |
- Indexes: PK
(staging_id, sha256);page_body_staging_texts_text (source_id, sha256). - Readers:
src/core/facts/purge.ts,src/core/page-body/schema.ts,src/core/persistence/page-purge.ts. - Writers:
src/core/engine-sql/page-body.ts.
Sequence page_body_occurrence_id_seq
Source: schema.sql:2161. Supplies occurrence_id for page_body_manifest. Readers and writers: src/core/engine-sql/page-body.ts.
Segment derivations and purge (generated, page-body/schema.ts)
page_segment_derivations
Source: schema.sql:2250-2366. Per-segment derivation watermark (links, mentions, timeline, fences, conversation_facts, facts_backstop, synopsis). Versions and context hashes decide whether a segment must re-derive.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| pipeline | TEXT | NN | PK part; CK in (links,mentions,timeline,fences,conversation_facts,facts_backstop,synopsis) |
| occurrence_id | BIGINT | NN | PK part |
| pipeline_version | INTEGER | NN | CK >= 0 |
| segment_sha256 | BYTEA | NN | CK octet_length = 32 |
| context_hash | BYTEA | NN | CK octet_length = 32 |
| state | TEXT | NN | CK in (done,partial,failed,skipped) |
| output | JSONB | null | |
| receipt | JSONB | null | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- PK:
(page_id, pipeline, occurrence_id). - Indexes: partial
page_segment_derivations_unsettled (pipeline, page_id) WHERE state IN ('partial','failed'). - Trigger:
page_segment_derivations_refuse_purgingBEFORE INSERT OR UPDATE OFoutput, receipt(callsgbrain_refuse_purging_derivation). - Readers:
src/core/engine-sql/page-body.ts,src/core/facts/purge-verify.ts,src/core/page-body/segment-derive.ts,src/core/repair/secret-spans.ts. - Writers:
src/core/page-body/segment-derive.ts,src/core/persistence/segmented-apply.ts,src/core/engine-sql/page-body.ts,src/core/repair/secret-spans.ts.
page_derivation_spend
Source: schema.sql:2265-2273. Spend and reservation per page and family (facts or synopsis).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| family | TEXT | NN | PK part; CK in (facts,synopsis) |
| spent_usd | DOUBLE PRECISION | NN, D=0 | CK >= 0 |
| reserved_usd | DOUBLE PRECISION | NN, D=0 | CK >= 0 |
| reserved_at | TIMESTAMPTZ | null | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: PK
(page_id, family). - Readers and writers:
src/core/page-spend.ts.
page_segment_derivation_purges
Source: schema.sql:2274-2292. A purge request for derived segment outputs that contain given needles or patterns. It is swept in phases.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| request_id | UUID | PK | |
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE |
| needles | TEXT[] | NN | |
| patterns | TEXT[] | NN | |
| page_ids | INTEGER[] | NN, D='{}' | |
| phase | TEXT | NN, D='sweep' | CK in (sweep,verify,recheck) |
| recheck | INTEGER[] | null | |
| found | INTEGER | NN, D=0 | CK >= 0 |
| cursor_page | INTEGER | null | |
| cursor_pipeline | TEXT | null | |
| cursor_occurrence | BIGINT | null | |
| batch_rows | INTEGER | null | CK >= 1 |
| stalled_at | TIMESTAMPTZ | null | |
| stalled_reason | TEXT | null | CK in (statement_timeout,paid_row_unreadable) |
| pending_reason | TEXT | null | CK in (scope_lock_busy) |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Indexes:
page_segment_derivation_purges_source (source_id, created_at). - Readers:
src/core/page-body/segment-derive.ts,src/core/page-body/schema.ts,src/commands/doctor/checks/derivation-purge-sweeps.ts. - Writers:
src/core/page-body/segment-derive.ts.
page_segment_derivation_purge_pages
Source: schema.sql:2293-2297. Pages collected for a purge request.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| request_id | UUID | NN | PK part; no FK |
| page_id | INTEGER | NN | PK part; no FK |
- Indexes: PK
(request_id, page_id). - Readers:
src/core/page-body/segment-derive.ts. - Writers:
src/core/page-body/segment-derive.ts,src/core/page-body/schema.ts(trigger insert, see below).
Functions and triggers in the derivation region
gbrain_mark_derivation_scope_entry()(2298-2335). Trigger fn forpage_body_manifest(retire and delete) and forpages(soft delete). Checks RLS policies onpage_segment_derivation_purgesandpage_segment_derivation_purge_pages. Takespg_advisory_xact_lock_shared(hashtextextended('gbrain:derivation-scope:' || src, 0)). Then inserts(request_id, page)intopage_segment_derivation_purge_pagesfor every open purge on that source (ON CONFLICT DO NOTHING).- Triggers:
page_body_manifest_derivation_scope_retireAFTER UPDATE OFgen_toWHEN the old row was open and not staged (2336-2338).page_body_manifest_derivation_scope_deleteAFTER DELETE WHEN the row was open (2339-2341).pages_derivation_scope_deleteAFTER UPDATE OFdeleted_atWHEN the page becomes deleted (2342-2344). gbrain_refuse_purging_derivation()(2345-2379). BEFORE trigger onpage_segment_derivations. If the row matches an open purge (by page id, page deletion, or missing live manifest row) and the text ofoutputorreceiptmatches a purge pattern (afterlower,translateof Greek final sigma and Turkish dotted i, thengbrain_fact_normalize), the trigger suppresses the row (RETURN NULL). Otherwise it passes it through. It also checks RLS visibility onpagesand the purge table, raisingwriter_coordinator_requiredif unreadable.
Page-body RLS block (generated, PAGE_BODY_RLS_SQL)
DO $rls$ at schema.sql:2386-2397. If the current role is a superuser or has BYPASSRLS (checked via pg_roles and pg_has_role), it enables RLS on: page_segment_texts, page_body_manifest, page_body_stagings, page_body_staging_parts, page_view_digests, page_body_staging_texts, page_segment_derivations, page_derivation_spend, page_segment_derivation_purges, page_segment_derivation_purge_pages. The block skips tables that are already RLS-enabled.
Persistence and managed writer (generated, persistence/schema.ts)
The persistence tables hold the durable write-queue state for the coordinator. The managed-writer trigger (below) makes direct writes to the canonical tables fail unless the session declares its capability through GUCs.
persistence_brain
Source: schema.sql:2402-2408, altered at 2939-2940. Singleton row for the managed-writer state.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| singleton | INTEGER | PK | CK = 1 |
| brain_id | UUID | NN, D=gen_random_uuid() | |
| enabled | BOOLEAN | NN, D=false | |
| activated_at | TIMESTAMPTZ | null | |
| writer_protocol_floor | INTEGER | NN, D=1 | CK in (1,2) (ALTER, shared-skills region) |
| skill_bundles_enabled | BOOLEAN | NN, D=false | (ALTER, shared-skills region) |
- Seeded with
INSERT ... (1) ON CONFLICT DO NOTHINGat 2408. - Readers:
src/core/agent-install/shared-skills.ts,src/core/health-memo.ts,src/core/repair/rotation.ts,src/core/repair/agent-session-facts.ts. - Writers:
src/core/persistence/activation.ts,src/core/persistence/engine-graduation.ts,src/core/shared-skills/restore.ts,src/eval/brainbench/trust-scenario.ts.
persistence_worktrees
Source: schema.sql:2409-2418.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK, D=gen_random_uuid() | |
| owner_host_id | UUID | null | |
| owner_epoch | BIGINT | NN, D=0 | |
| topology_generation | BIGINT | NN, D=1 | |
| state | TEXT | NN, D='active' | CK in (active,draining,recovering) |
| manifest | JSONB | null | |
| heartbeat_at | TIMESTAMPTZ | null | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Readers:
src/core/notifier/nudge.ts,src/core/notifier/resident.ts,src/core/repair/content-receipt-root.ts,src/core/connectors/item-holds-store.ts. - Writers:
src/core/persistence/ownership.ts,src/core/persistence/topology-locks.ts,src/core/persistence/topology-recovery.ts,src/core/shared-skills/restore.ts.
persistence_source_bindings
Source: schema.sql:2419-2425.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | PK | |
| source_incarnation | UUID | NN | |
| worktree_id | UUID | NN | FK -> persistence_worktrees(id) |
| relative_path | TEXT | NN, D='' | |
| topology_generation | BIGINT | NN, D=1 |
- Readers:
src/core/notifier/nudge.ts,src/core/notifier/resident.ts,src/core/repair/segmented-mirror.ts,src/core/facts/withdrawal.ts. - Writers:
src/core/persistence/topology-recovery.ts,src/core/persistence/topology-locks.ts,src/core/source-delete.ts,src/core/shared-skills/restore.ts.
persistence_host_bindings
Source: schema.sql:2426-2433.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| worktree_id | UUID | NN | FK -> persistence_worktrees(id); PK part |
| host_id | UUID | NN | PK part |
| local_path | TEXT | NN | |
| coordination_path | TEXT | NN | |
| git_durability | TEXT | null | CK in (enabled,disabled) |
- PK:
(worktree_id, host_id). - Readers:
src/core/notifier/nudge.ts,src/core/notifier/resident.ts,src/core/repair/content-receipt-root.ts,src/core/shared-skills/content-freshness.ts. - Writers:
src/core/persistence/git-durability-administration.ts,src/core/persistence/ownership.ts,src/core/persistence/deactivation.ts,src/core/shared-skills/restore.ts.
persistence_local_writers
Source: schema.sql:2434-2441. Credential-hash registry for local writer lanes.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK, D=gen_random_uuid() | |
| lane | TEXT | NN | CK in (cli,stdio) |
| credential_hash | TEXT | NN | UQ |
| grant_ceiling | JSONB | NN | |
| revoked_at | TIMESTAMPTZ | null | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Readers:
src/core/ops/persistence.ts,src/core/ops/attribution.ts,src/core/embed-facts.ts,src/core/shared-skills/policy.ts. - Writers:
src/core/persistence/identity.ts,src/core/shared-skills/restore.ts.
persistence_counters
Source: schema.sql:2442-2449. Outstanding-request and byte counters for admission control. One row per key.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| key | TEXT | PK | |
| outstanding_count | BIGINT | NN, D=0 | CK >= 0 |
| intent_bytes | BIGINT | NN, D=0 | CK >= 0 |
| lifetime_ids | BIGINT | NN, D=0 | CK >= 0 |
| terminal_bytes | BIGINT | NN, D=0 | CK >= 0 |
| recovery_bytes | BIGINT | NN, D=0 | CK >= 0 |
- Readers:
src/commands/doctor/checks/persistence-compaction.ts,src/commands/doctor/checks/persistence-capacity.ts,src/commands/doctor/checks/persistence-requests.ts,src/core/repair/core.ts. - Writers:
src/core/persistence/journal.ts,src/core/persistence/limits.ts,src/core/persistence/effect-recovery.ts,src/core/persistence/intent-redaction.ts.
persistence_requests
Source: schema.sql:2450-2488, with ALTERs at 2941-2942 (shared-skills region). The write-request journal. Each row is one queued, running or finished mutation, with its authority and recovery payload.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK, D=gen_random_uuid() | |
| principal_kind | TEXT | NN | CK in (oauth_client,legacy_token,local_cli,local_stdio,application) |
| principal_id | TEXT | NN | UQ with principal_kind and request_id |
| request_id | UUID | NN | UQ part |
| operation | TEXT | NN | |
| source_id | TEXT | NN | no FK |
| source_incarnation | UUID | NN | no FK |
| page_id | INTEGER | null | |
| slug | TEXT | NN | |
| worktree_id | UUID | null | FK -> persistence_worktrees(id) |
| topology_generation | BIGINT | null | |
| digest | TEXT | NN | |
| intent | JSONB | null | |
| authority | JSONB | NN | |
| sequence | BIGSERIAL | NN, UQ | |
| state | TEXT | NN, D='queued' | CK in (queued,running,recovering,committed,conflict,failed,cancelled) |
| execution_token | UUID | null | |
| claim_expires_at | TIMESTAMPTZ | null | |
| recovery | JSONB | null | |
| recovery_bytes | BIGINT | NN, D=0 | |
| intent_bytes | BIGINT | NN | |
| terminal_reservation | BIGINT | NN | |
| outcome | JSONB | null | |
| error_code | TEXT | null | |
| error_message | TEXT | null | |
| blocked_reason | TEXT | null | |
| compacted | BOOLEAN | NN, D=false | |
| publication_started | BOOLEAN | NN, D=false | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| completed_at | TIMESTAMPTZ | null | |
| target_kind | TEXT | NN, D='page' | CK in (page,skill_bundle) (ALTER) |
| protocol_version | INTEGER | NN, D=1 | CK in (1,2) (ALTER) |
- Indexes: partial
persistence_requests_pending (worktree_id, sequence) WHERE state IN ('queued','running','recovering'); partialpersistence_requests_recovery (worktree_id, sequence) WHERE recovery IS NOT NULL;persistence_requests_principal (principal_kind, principal_id, sequence DESC). - Trigger:
gbrain_request_protocolBEFORE INSERT OR UPDATE OR DELETE (callsgbrain_guard_request_protocol). - Readers:
src/core/ops/persistence.ts,src/core/ops/page-upload.ts,src/core/ops/attribution.ts,src/core/repair/segmented-mirror.ts. - Writers:
src/core/persistence/journal.ts,src/core/persistence/import-deferral.ts,src/core/persistence/transient-retry.ts,src/core/shared-skills/restore.ts.
persistence_effects
Source: schema.sql:2489-2499. Side-effect rows per request (e.g., a revision or data write), one per (request_id, kind).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| request_id | UUID | NN | FK -> persistence_requests(id); UQ part |
| kind | TEXT | NN | UQ part |
| revision | UUID | null | |
| data | JSONB | NN | |
| state | TEXT | NN, D='queued' | CK in (queued,running,committed,failed) |
| execution_token | UUID | null | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Trigger:
gbrain_effect_protocolBEFORE INSERT OR UPDATE OR DELETE (callsgbrain_guard_effect_protocol). - Readers:
src/core/repair/segmented-mirror.ts,src/core/repair/receipt-compaction.ts,src/core/repair/readers.ts,src/core/facts/purge-verify.ts. - Writers:
src/core/repair/segmented-mirror.ts,src/core/facts/purge-reasons.ts,src/core/facts/withdrawal.ts,src/core/facts/purge.ts.
persistence_consumers
Source: schema.sql:2500-2520. Heartbeat registry of consumer processes.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| host_id | UUID | NN | PK part |
| pid | INTEGER | NN | PK part |
| nonce | TEXT | NN | PK part |
| pid_ns | TEXT | null | |
| kind | TEXT | NN | |
| mode | TEXT | NN | CK in (probing,full,waiter_only,promoted) |
| started_at | TIMESTAMPTZ | NN, D=now() | |
| renewed_at | TIMESTAMPTZ | NN, D=now() | |
| restart_required | BOOLEAN | NN, D=false | |
| root_barrier_age_ms | INTEGER | null | |
| pool | JSONB | null | |
| host_json_path | TEXT | NN | |
| persistence_home | TEXT | NN | |
| minted_under | JSONB | null | |
| version | TEXT | NN | |
| boot_id | TEXT | null | |
| process_start | TEXT | null | |
| segment_reader | BOOLEAN | null |
- PK:
(host_id, pid, nonce). - Readers:
src/core/session-lane.ts,src/core/persistence/journal.ts,src/core/persistence/writer-versions.ts,src/core/persistence/consumer-heartbeat.ts. - Writers:
src/core/persistence/consumer-heartbeat.ts.
persistence_topology_changes
Source: schema.sql:2771-2792 (generated, persistence/topology-schema.ts). Topology-change journal (source moves, clones, etc.) with recovery state.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK, D=gen_random_uuid() | |
| principal_id | UUID | NN | UQ with request_id |
| request_id | UUID | NN | UQ part |
| digest | TEXT | NN | |
| operation | TEXT | NN | |
| source_id | TEXT | NN | no FK |
| source_incarnation | UUID | null | |
| worktree_ids | UUID[] | NN, D='{}' | |
| state | TEXT | NN | CK in (recovering,committed,failed) |
| recovery | JSONB | null | |
| recovery_bytes | BIGINT | NN, D=0 | CK >= 0 |
| intent_bytes | BIGINT | NN, D=0 | CK >= 0 (added by ALTER at 2790) |
| terminal_bytes | BIGINT | NN, D=2048 | CK >= 0 |
| outcome | JSONB | null | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Indexes: partial
persistence_topology_recovering (created_at) WHERE state='recovering'. - Readers:
src/core/shared-skills/restore.ts,src/core/shared-skills/migration-export.ts,src/core/source-default-search.ts,src/core/persistence/journal.ts. - Writers:
src/core/persistence/topology-recovery.ts,src/core/persistence/physical-root.ts,src/core/persistence/topology-receipts.ts,src/core/persistence/topology-clone.ts.
persistence_worktree_refreshes
Source: schema.sql:2795-2816 (generated, persistence/worktree-refresh-schema.ts). Worktree refresh (fetch and merge) state machine.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK, D=gen_random_uuid() | |
| worktree_id | UUID | NN | FK -> persistence_worktrees(id) |
| source_ids | TEXT[] | NN | |
| principal_id | UUID | NN | |
| owner_epoch | BIGINT | NN | |
| topology_generation | BIGINT | NN | |
| state | TEXT | NN | CK in (draining,fenced,merged,syncing,completed,aborted,recovery_required) |
| old_head | TEXT | NN | |
| target_head | TEXT | NN | |
| upstream_ref | TEXT | NN | |
| preserved_uncommitted | TEXT[] | NN, D='{}' | |
| outcome | JSONB | NN, D='{}' | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| completed_at | TIMESTAMPTZ | null |
- Indexes: UNIQUE partial
persistence_worktree_refreshes_active (worktree_id) WHERE state IN ('draining','fenced','merged','syncing','recovery_required'); partialpersistence_worktree_refreshes_completed (worktree_id, completed_at DESC) WHERE state='completed'. - Readers:
src/core/persistence/journal.ts,src/core/persistence/refresh-barrier.ts,src/core/persistence/head-blocker.ts,src/core/persistence/topology-locks.ts. - Writers:
src/core/persistence/worktree-refresh.ts,src/core/persistence/journal.ts,src/core/schema-migrations/v292-refresh-write-barrier.ts.
Git canonical (generated, persistence/git-canonical-schema.ts)
git_canonical_brain
Source: schema.sql:2643-2646, with its trigger and RLS block at 2647-2679. Singleton count of sources whose config.canonical = 'git'. Used to fence writes to git-canonical sources.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| singleton | INTEGER | PK, D=1 | CK = 1 |
| strict_sources | INTEGER | NN, D=0 | CK >= 0 |
- Triggers:
git_canonical_sources_count_insertAFTER INSERT ONsourcesWHEN the new row is git-canonical;_updateAFTER UPDATE WHEN thecanonicalvalue changes;_deleteAFTER DELETE WHEN the old row is git-canonical. All callgbrain_count_git_canonical_sources()(2647-2668). That function raiseswriter_coordinator_requiredif RLS hidessources, then recounts and upserts the singleton. - Seeded by an
INSERT ... ON CONFLICTat 2669-2671. - RLS:
DO $rls$at 2672-2679 enables RLS when the role bypasses RLS. - Readers:
src/core/persistence/writer-guard-schema.ts. - Writers:
src/core/persistence/git-canonical-schema.ts(trigger function and seed; the managed-writer trigger reads it).
Managed-writer guard (generated, persistence/schema.ts)
Function gbrain_require_managed_writer() and trigger managed_writer_guard
- Function at
schema.sql:2522-2627(plpgsql,RETURNS trigger). Readspersistence_brain.enabled. If the row is missing, it raiseswriter_coordinator_required(RLS hid it). If the brain is not managed, it passes the write. If managed, it allows the write only when the session hasgbrain.write_sources(a JSON array of allowed source ids) containing the target source. Source topology changes needgbrain.topology_change = 'on'. Git-canonical sources needgbrain.git_protocol = '1'. Row-level checks strip the physical-projection columns (embedding_model,embedded_text_hash,write_origin, and write-attribution columns) before comparing rows, so those changes pass. - Trigger
managed_writer_guardis attached by theDO $body$at 2629-2638 to:pages,tags,slug_aliases,page_aliases,facts,takes,timeline_entries,sources,page_segment_texts,page_body_manifest. Each attachment is skipped when the table does not exist.
Page projection (generated, page-state/projection-schema.ts)
page_projection_jobs
Source: schema.sql:2689-2696. Work queue of pages that need a text projection rebuilt, keyed by (source_incarnation, slug).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_incarnation | UUID | NN | FK -> sources(incarnation) ON DELETE CASCADE; PK part |
| slug | TEXT | NN | PK part |
| revision | UUID | NN | |
| reason | TEXT | NN | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- PK:
(source_incarnation, slug). - Readers:
src/core/page-body/projection.ts,src/core/search/retrieval-coverage.ts,src/core/persistence/consumer.ts,src/core/page-state/projection-status.ts. - Writers:
src/core/page-body/projection.ts,src/core/page-state/projections.ts,src/core/schema-migrations/v189-pages-credential-projection-pending.ts, plus the trigger function below.
Objects in the projection region
gbrain_queue_page_projection()(2697-2763). Trigger fn forpagesinsert, update, and delete. On delete it removes the page's queued job. On a source change or a changedknowledge_revisionit upserts a job withreason = 'canonical_change'(ON CONFLICT (source_incarnation, slug) DO UPDATE). When the page is deleted or its text projection is already current, it removes the job instead.- Trigger
pages_projection_queueAFTER INSERT OR UPDATE OR DELETE ONpages(2764-2766). - Trigger
trg_pages_search_vector(2683-2688): dropped and recreated asBEFORE INSERT OR UPDATE OF <cols> ON pagescallingupdate_page_search_vector(). The column list istitle,timeline,timeline_leadif thetimeline_leadcolumn exists, elsetitle,timeline.update_page_search_vectoris defined outside this range.
Source ingestion receipts (generated, company-brain/receipt-schema.ts)
source_ingestion_receipts
Source: schema.sql:2820-2850. Receipt for one ingestion run of a source at an approved revision. Phase goes ADMITTED to COMPLETE. An outcome is complete or discarded.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK | |
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE |
| source_incarnation | UUID | NN | FK -> sources(incarnation) ON DELETE CASCADE |
| approved_revision | TEXT | NN | CK regex 40 or 64 lowercase hex |
| profile | TEXT | NN | CK length 1-128 |
| schema_fingerprint | TEXT | NN | CK regex 64 lowercase hex |
| extractor_version | TEXT | NN | CK length 1-128 |
| revision | INTEGER | NN, D=1 | CK > 0 |
| phase | TEXT | NN, D='ADMITTED' | CK in (ADMITTED,CONTENT,GRAPH,VERIFY,COMPLETE) |
| outcome | TEXT | NN, D='incomplete' | CK in (incomplete,complete,discarded) |
| counts | JSONB | NN, D='{}' | CK object and <= 4096 bytes |
| lifecycle_request_ids | UUID[] | NN, D='{}' | CK cardinality <= 128 |
| checkpoint_refs | JSONB | NN, D='[]' | CK array, <= 32 items, <= 16384 bytes |
| diagnostic | TEXT | null | CK in (interrupted,content_incomplete,graph_incomplete,verification_failed,pending_writes,source_changed,checkpoint_missing,operation_failed) |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| completed_at | TIMESTAMPTZ | null | |
| discarded_at | TIMESTAMPTZ | null | |
| policy_fingerprint | TEXT | null | CK regex 64 lowercase hex (ALTER at 2843) |
- Table CHECKs:
(phase='COMPLETE') = (outcome='complete');(completed_at IS NOT NULL) = (outcome='complete');(discarded_at IS NOT NULL) = (outcome='discarded'). - Indexes:
source_ingestion_receipts_source (source_id, source_incarnation, created_at DESC); partialsource_ingestion_receipts_retention (source_id, source_incarnation, completed_at DESC, id DESC) WHERE outcome='complete'; partialsource_ingestion_receipts_active (id) WHERE outcome='incomplete'. - Readers:
src/core/shared-skills/content-freshness.ts,src/core/sync-dispatch-read.ts,src/core/persistence/page-mutations.ts,src/core/company-brain/receipts.ts. - Writers:
src/core/company-brain/receipts.ts.
Shared skills (generated, shared-skills/schema-all.ts)
shared_skill_state
Source: schema.sql:2854-2859. Singleton with a token secret and a serving epoch.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| singleton | INTEGER | PK | CK = 1 |
| token_secret | TEXT | NN, D=two gen_random_uuid() values with dashes removed (64 hex chars) |
|
| serving_epoch | UUID | NN, D=gen_random_uuid() |
- Seeded by
INSERT ... (1) ON CONFLICT DO NOTHING(2859). - Readers:
src/core/shared-skills/retention.ts,src/core/shared-skills/membership-authority.ts,src/core/shared-skills/catalog.ts. - Writers:
src/core/shared-skills/schema.ts,src/core/shared-skills/restore.ts.
shared_skill_policies
Source: schema.sql:2860-2865.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | PK part |
| source_incarnation | UUID | NN | PK part |
| epoch | UUID | NN, D=gen_random_uuid() | |
| policy | JSONB | NN | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- PK:
(source_id, source_incarnation). - Readers:
src/core/shared-skills/setup.ts,src/core/shared-skills/policy.ts,src/core/shared-skills/restore.ts,src/core/shared-skills/catalog.ts. - Writers:
src/core/shared-skills/policy.ts,src/core/shared-skills/restore.ts.
shared_skill_packs
Source: schema.sql:2866-2871. One pack per source incarnation.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | PK part |
| source_incarnation | UUID | NN | PK part |
| pack_id | TEXT | NN | UQ with source |
| revision | UUID | NN | |
| manifest | JSONB | NN | |
| manifest_hash | TEXT | NN |
- PK
(source_id, source_incarnation); UQ(source_id, source_incarnation, pack_id). - Trigger:
gbrain_skill_publication(see below). - Readers:
src/core/shared-skills/publication.ts,src/core/shared-skills/compatibility.ts,src/core/shared-skills/adopted-packs.ts,src/core/shared-skills/migration.ts. - Writers:
src/core/shared-skills/publication.ts.
shared_skill_policy_audit
Source: schema.sql:2872-2879. Append-only audit of policy epoch changes.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | UUID | PK, D=gen_random_uuid() | |
| source_id | TEXT | NN | |
| source_incarnation | UUID | NN | |
| principal_kind | TEXT | NN | |
| principal_id | TEXT | NN | |
| previous_epoch | TEXT | NN | |
| epoch | UUID | NN | |
| policy | JSONB | NN | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Indexes:
shared_skill_policy_audit_source_idx (source_id, source_incarnation, created_at). - Readers: none in
src/TS outside the generated files (only inventory lists:src/core/deletion-inventory.ts,src/core/persistence/graduation-inventory.ts). - Writers:
src/core/shared-skills/policy.ts.
shared_skill_heads
Source: schema.sql:2878-2895, lines 2878-2883 for the table. Current head per skill name.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | PK part |
| source_incarnation | UUID | NN | PK part |
| pack_id | TEXT | NN | PK part |
| name | TEXT | NN | PK part |
| revision | UUID | NN | |
| metadata | JSONB | NN | |
| deleted | BOOLEAN | NN, D=false | |
| policy_epoch | TEXT | NN | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- PK
(source_id, source_incarnation, pack_id, name). - Indexes: partial
shared_skill_heads_active_idx (source_id, source_incarnation, pack_id, name) WHERE NOT deleted. - Trigger:
gbrain_skill_publication. - Readers:
src/core/shared-skills/publication.ts,src/core/shared-skills/retention.ts,src/core/shared-skills/adopted-packs.ts,src/core/shared-skills/migration.ts. - Writers:
src/core/shared-skills/publication.ts.
shared_skill_revisions
Source: schema.sql:2884-2895. Immutable revision of a skill. stored_bytes is computed.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | PK part |
| source_incarnation | UUID | NN | PK part |
| pack_id | TEXT | NN | PK part |
| name | TEXT | NN | PK part |
| revision | UUID | NN | PK part |
| metadata | JSONB | NN | |
| files | JSONB | NN | |
| deleted | BOOLEAN | NN, D=false | |
| policy_epoch | TEXT | NN | |
| request_id | UUID | NN | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| stored_bytes | BIGINT | GENERATED ALWAYS AS (octet_length(files::text) + octet_length(metadata::text)) STORED |
- PK
(source_id, source_incarnation, pack_id, name, revision). - Indexes:
shared_skill_revision_request_idx (request_id);shared_skill_revision_retention_idx (source_id, source_incarnation, name, created_at DESC);shared_skill_revision_uuid_idx (revision). - Trigger:
gbrain_skill_publication. - Readers:
src/core/shared-skills/optimizer.ts,src/core/shared-skills/publication.ts,src/core/shared-skills/retention.ts,src/core/shared-skills/migration.ts. - Writers:
src/core/shared-skills/publication.ts,src/core/shared-skills/retention.ts.
shared_skill_revision_leases
Source: schema.sql:2896-2907. Lease that pins a revision (delivery or pin) so retention does not delete it.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| lease_kind | TEXT | NN | PK part; CK in (delivery,pin) |
| lease_id | UUID | NN | PK part |
| source_id | TEXT | NN | PK part; FK (composite) -> shared_skill_revisions(...) ON DELETE RESTRICT |
| source_incarnation | UUID | NN | PK part |
| pack_id | TEXT | NN | PK part |
| name | TEXT | NN | PK part |
| revision | UUID | NN | PK part |
| principal_kind | TEXT | NN | |
| principal_id | TEXT | NN | |
| expires_at | TIMESTAMPTZ | NN |
- Indexes:
shared_skill_revision_lease_expiry_idx (source_id, source_incarnation, expires_at);shared_skill_revision_lease_target_idx (source_id, source_incarnation, pack_id, name, revision, expires_at). - Trigger:
shared_skill_delivery_leaseonshared_skill_delivery_batchescallsgbrain_lease_shared_skill_delivery()and inserts or extends delivery leases (24-hour TTL). - Readers:
src/core/shared-skills/retention.ts. - Writers:
src/core/shared-skills/retention.ts,src/core/shared-skills/restore.ts,src/core/shared-skills/schema.ts, and the lease trigger.
shared_skill_members
Source: schema.sql:2906-2924. Member installation registry (one per principal and adapter).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| installation_id | UUID | PK | |
| principal_kind | TEXT | NN | UQ part |
| principal_id | TEXT | NN | UQ part |
| adapter | TEXT | NN | UQ part |
| brain_id | UUID | NN | |
| epoch | BIGINT | NN, D=1 | |
| active | BOOLEAN | NN, D=true | |
| follow_policy | JSONB | NN | |
| issued_sequence | BIGINT | NN, D=0 | |
| acknowledged_sequence | BIGINT | NN, D=0 | |
| desired_view | TEXT | null | |
| acknowledged_view | TEXT | null | |
| joined_at | TIMESTAMPTZ | NN, D=now() | |
| last_seen_at | TIMESTAMPTZ | NN, D=now() | |
| join_window | TIMESTAMPTZ | NN, D=now() | |
| join_count | INTEGER | NN, D=1 |
- UNIQUE
(principal_kind, principal_id, adapter). - Readers:
src/core/shared-skills/migration-members.ts,src/core/shared-skills/membership.ts,src/core/skillpack/shared-brain-bridge.ts,src/core/bootstrap/harness-skills.ts. - Writers:
src/core/shared-skills/membership.ts,src/core/shared-skills/restore.ts.
shared_skill_delivery_batches
Source: schema.sql:2925-2940. One issued delivery batch per member (token, epoch, sequence).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| token | UUID | PK | |
| installation_id | UUID | NN | FK -> shared_skill_members(installation_id) ON DELETE CASCADE; UQ part |
| epoch | BIGINT | NN | UQ part |
| sequence | BIGINT | NN | UQ part |
| view_token | TEXT | NN | |
| authority_digest | TEXT | NN | |
| revisions | JSONB | NN | |
| issued_at | TIMESTAMPTZ | NN, D=now() | |
| acknowledged_at | TIMESTAMPTZ | null | |
| evidence | JSONB | null |
- UQ
(installation_id, epoch, sequence); indexshared_skill_delivery_member_idx (installation_id, epoch, issued_at). - Trigger:
shared_skill_delivery_leaseAFTER INSERT OR UPDATE OFrevisions. - Readers:
src/core/shared-skills/membership.ts,src/core/shared-skills/migration-members.ts. - Writers:
src/core/shared-skills/membership.ts.
persistence_writer_protocols
Source: schema.sql:2943-2950. Registers which owner hosts speak writer protocol 2.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| worktree_id | UUID | NN | FK -> persistence_worktrees(id); PK part |
| host_id | UUID | NN | PK part |
| owner_epoch | BIGINT | NN | |
| protocol_version | INTEGER | NN | CK = 2 |
| registered_at | TIMESTAMPTZ | NN, D=now() |
- PK
(worktree_id, host_id). - Readers:
src/core/shared-skills/restore.ts,src/core/shared-skills/persistence-schema.ts,src/core/persistence/protocol.ts,src/core/persistence/deactivation.ts. - Writers:
src/core/shared-skills/restore.ts,src/core/persistence/skill-activation.ts.
Functions and triggers in the shared-skills region
gbrain_require_persistence_protocol(required int)(2951-2956). Raiseswriter_upgrade_required(SQLSTATE 42501) unlessgbrain.persistence_protocol = '2'whenrequired >= 2.gbrain_guard_request_protocol()(2957-2995), trigger fn forpersistence_requests(2996-2998). It checks the floor frompersistence_brain, requires protocol 2 forskill_bundletargets, checks that the target kind and version are immutable, requiresput_skill,delete_skillandadopt_skillpackoperations to use a skill target, and validates the recovery JSON shape.gbrain_guard_effect_protocol()(2999-3010), trigger fn forpersistence_effects(3008-3010).gbrain_guard_protocol_activation()(3011-3034), BEFORE UPDATE trigger onpersistence_brain(3035-3037). Blocks lowering the floor. Raising the floor or enabling skill bundles requires quiesced writers (gbrain.writer_quiesced), no queued or running requests, no unreleased recovery, and drained owners.gbrain_guard_skill_publication()(3038-3054). Trigger fn onshared_skill_packs,shared_skill_heads,shared_skill_revisions(3055-3063). Requires an enabled brain withskill_bundles_enabledand floor 2, andgbrain.write_sourcesto include the row's source.gbrain_lease_shared_skill_delivery()(3064-3081). Trigger fn for the delivery-lease trigger (3082-3084). Validates each delivery revision is retained, takesFOR KEY SHARE, and upserts a lease with a 24-hour expiry.DO $$at 3085-3097: if the role bypasses RLS or is superuser, enables RLS on the 10 shared-skill and writer-protocol tables.
Decide storage (generated, ai/decide/schema.ts)
decision_receipts
Source: schema.sql:3102-3134. One receipt per decision call (slot, provider, model, outcome, calibration reference).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| decision_id | TEXT | NN | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| source_id | TEXT | null | |
| slot | TEXT | NN | |
| mode | TEXT | NN | |
| provider | TEXT | NN | |
| model_alias | TEXT | null | |
| model_resolved | TEXT | null | |
| question_kind | TEXT | null | |
| state_hash | TEXT | null | |
| question_hash | TEXT | null | |
| answer_value | REAL | null | |
| answer_choice | TEXT | null | |
| confidence | REAL | null | |
| threshold | REAL | null | |
| outcome | TEXT | NN | |
| subject_ref | TEXT | null | |
| call_site | TEXT | NN | |
| lane | TEXT | NN | |
| policy_fingerprint | TEXT | null | |
| calibration_ref | TEXT | null | |
| latency_ms | INTEGER | null | |
| input_tokens | INTEGER | null | |
| error_reason | TEXT | null | |
| protected | BOOLEAN | NN, D=false | |
| min_keep | INTEGER | null | |
| rank | INTEGER | null | |
| k_used | INTEGER | null | |
| remote | BOOLEAN | NN, D=false | |
| run_meta | TEXT | null |
- Indexes:
decision_receipts_slot_created_idx (slot, created_at);decision_receipts_model_slot_idx (model_resolved, slot);decision_receipts_decision_idx (decision_id). - Readers:
src/core/ai/decide/proposals-store.ts,src/core/ai/decide/store.ts,src/core/ai/decide/review-lane.ts. - Writers:
src/core/ai/decide/store.ts.
decide_spend
Source: schema.sql:3138-3151. Per-request spend ledger.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| request_id | TEXT | PK | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| source_id | TEXT | null | |
| slot | TEXT | NN | |
| provider | TEXT | NN | |
| model_resolved | TEXT | null | |
| lane | TEXT | NN | |
| remote | BOOLEAN | NN, D=false | |
| input_tokens | INTEGER | NN, D=0 | |
| cost_usd | DOUBLE PRECISION | NN, D=0 | |
| outcome | TEXT | NN |
- Indexes:
decide_spend_created_idx (created_at). - Readers:
src/eval/decide-eval-flags.ts,src/core/ai/decide/store.ts. - Writers:
src/core/ai/decide/store.ts.
decide_state
Source: schema.sql:3152-3156. Key/value state store.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| key | TEXT | PK | |
| value | TEXT | NN | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Readers and writers:
src/core/ai/decide/store.ts.
decide_calibrations
Source: schema.sql:3173-3205. Calibration run per slot and model: threshold, metric, qualification and retest or repack stability.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| slot | TEXT | NN | |
| call_site | TEXT | NN | |
| provider | TEXT | NN | |
| model_resolved | TEXT | NN | |
| threshold | REAL | NN | |
| min_keep | INTEGER | null | |
| metric | TEXT | NN | |
| metric_value | REAL | null | |
| ece | REAL | null | |
| retest_sd | REAL | null | |
| repack_sd | REAL | null | |
| action_precision_lb | REAL | null | |
| qualification | TEXT | null | |
| qualified_at | TIMESTAMPTZ | null | |
| policy_fingerprint | TEXT | null | |
| n | INTEGER | NN | |
| dataset_hash | TEXT | null | |
| split_hash | TEXT | null | |
| calibrate_ids_hash | TEXT | null | |
| calibrate_only | BOOLEAN | NN, D=true | |
| pack_shape | TEXT | NN | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| retired_at | TIMESTAMPTZ | null | |
| notes | TEXT | null |
- Indexes:
decide_calibrations_lookup_idx (slot, provider, model_resolved, created_at DESC). - Readers and writers:
src/core/ai/decide/store.ts.
decide_proposals
Source: schema.sql:3207-3250. Proposed supersede or claim-conflict pairs between two facts, with a pending or decided status.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| source_id | TEXT | NN | |
| sweep_id | TEXT | NN | UQ part |
| pair_index | INTEGER | NN | UQ part |
| new_fact_id | BIGINT | NN | |
| old_fact_id | BIGINT | NN | |
| direction | TEXT | NN, D='new_supersedes_old' | |
| p_supersede | REAL | NN | |
| threshold | REAL | null | |
| proposal_floor | REAL | NN | |
| model_resolved | TEXT | null | |
| status | TEXT | NN, D='pending' | |
| decided_at | TIMESTAMPTZ | null | |
| before_state | TEXT | null | |
| after_state | TEXT | null |
- UQ
(sweep_id, pair_index). - Indexes:
decide_proposals_status_created_idx (status, created_at); UNIQUE partialdecide_proposals_claim_pair_uidx (source_id, LEAST(new,old), GREATEST(new,old)) WHERE sweep_id LIKE 'claim_conflicts:%'; partialdecide_proposals_pending_new_idx (new_fact_id) WHERE status='pending'; partialdecide_proposals_pending_old_idx (old_fact_id) WHERE status='pending'. - Readers:
src/core/facts/claim-conflicts.ts,src/core/facts/purge-verify.ts,src/core/facts/contested-claims.ts,src/core/facts/purge.ts. - Writers:
src/core/ai/decide/proposals-store.ts,src/core/facts/purge.ts,src/core/persistence/page-purge.ts.
decide_sweep_deferred
Source: schema.sql:3227-3236. Deferred sweep work per fact.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | PK part |
| fact_id | BIGINT | NN | PK part |
| slot | TEXT | NN | PK part |
| reason | TEXT | NN | |
| attempts | INTEGER | NN, D=0 | |
| next_attempt_at | TIMESTAMPTZ | NN, D=now() | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- PK
(slot, source_id, fact_id). - Readers and writers:
src/core/ai/decide/proposals-store.ts.
decide_review_queue
Source: schema.sql:3252-3263. Queue of pairs waiting for review.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| kind | TEXT | NN | PK part |
| source_id | TEXT | NN | PK part |
| a_ref | TEXT | NN | PK part |
| b_ref | TEXT | NN, D='*' | PK part |
| evidence | TEXT | null | |
| reason | TEXT | NN, D='queued' | |
| attempts | INTEGER | NN, D=0 | |
| next_attempt_at | TIMESTAMPTZ | NN, D=now() | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- PK
(kind, source_id, a_ref, b_ref). - Readers:
src/core/facts/purge.ts,src/core/ai/decide/review-lane.ts,src/core/persistence/page-purge.ts. - Writers:
src/core/ai/decide/review-lane.ts,src/core/facts/purge.ts,src/core/facts/withdrawal.ts,src/core/persistence/page-purge.ts.
decide_review_proposals
Source: schema.sql:3264-3282. Review-lane proposal with an action probability.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| kind | TEXT | NN | UQ part |
| source_id | TEXT | NN | UQ part |
| sweep_id | TEXT | NN | |
| a_ref | TEXT | NN | UQ part |
| b_ref | TEXT | NN | UQ part |
| subject | TEXT | NN, D='*' | UQ part |
| visibility | TEXT | null | |
| p_action | REAL | NN | |
| threshold | REAL | null | |
| model_resolved | TEXT | null | |
| question_version | TEXT | null | |
| status | TEXT | NN, D='pending' | |
| decided_at | TIMESTAMPTZ | null | |
| receipt | TEXT | null |
- UQ
(kind, source_id, a_ref, b_ref, subject); indexdecide_review_proposals_status_idx (status, created_at). - Readers:
src/core/facts/purge.ts,src/core/ai/decide/review-lane.ts,src/core/persistence/page-purge.ts. - Writers:
src/core/ai/decide/review-lane.ts,src/core/persistence/page-purge.ts,src/core/facts/purge.ts.
RLS in the decide region
Each of decision_receipts, decide_spend, decide_state, decide_calibrations, decide_proposals, decide_sweep_deferred, decide_review_queue, decide_review_proposals gets its own DO $rls$ block (3157-3293). The block enables RLS when the role bypasses RLS or is a superuser.
Fact relink (generated, facts/relink-schema.ts)
fact_relink_attempts
Source: schema.sql:3298-3315. One attempt per fact to relink it to a page.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | PK part |
| fact_id | BIGINT | NN | PK part |
| outcome | TEXT | NN | |
| reason | TEXT | null | |
| tier | TEXT | null | |
| model | TEXT | null | |
| target_slug | TEXT | null | |
| run_id | TEXT | null | |
| attempted_at | TIMESTAMPTZ | NN, D=now() |
- PK
(source_id, fact_id); indexfact_relink_attempts_outcome_idx (source_id, outcome, attempted_at). - Readers:
src/core/facts/relink.ts,src/core/facts/conversation-memo.ts,src/commands/doctor/checks/unlinked-facts.ts. - Writers:
src/core/facts/relink.ts,src/core/facts/conversation-relink.ts,src/core/facts/conversation-memo.ts,src/core/facts/relink-publish.ts.
Link temporal (generated, link-temporal-schema.ts)
link_transitions
Source: schema.sql:3320-3343. Dated start or end transition for a typed link (from_page, to_page, link_type).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NN | |
| from_page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE |
| to_page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE |
| link_type | TEXT | NN | |
| kind | TEXT | NN | CK in (start,end) |
| occurred_on | DATE | NN | |
| date_precision | TEXT | NN, D='day' | CK in (day,month,year) |
| producer | TEXT | NN | CK in (timeline,explicit,frontmatter,manual,inline,dream) |
| origin_page_id | INTEGER | null | FK -> pages(id) ON DELETE CASCADE |
| line_hash | TEXT | NN, D='' | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| link_source | TEXT | null |
- Indexes: UNIQUE
link_transitions_identity_idx (origin_page_id, from_page_id, to_page_id, link_type, link_source, kind, occurred_on, producer) NULLS NOT DISTINCT;link_transitions_relationship_idx (from_page_id, to_page_id, link_type);link_transitions_origin_idx (origin_page_id). - RLS:
DO $rls$at 3339-3343. - Readers:
src/core/link-relationship-notes.ts,src/core/cycle/edge-contradictions.ts,src/commands/migrate-engine.ts,src/core/link-temporal-apply.ts. - Writers:
src/core/link-temporal-apply.ts,src/core/persistence/manual-links.ts,src/core/persistence/twin-merge.ts,src/core/links-fence-projection.ts.
link_relationships
Source: schema.sql:3344-3371. Derived state per (from, to, link_type, scope): validity ranges and status.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| from_page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| to_page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; PK part |
| link_type | TEXT | NN | PK part |
| scope | TEXT | NN | PK part; CK in (all,world) |
| source_id | TEXT | NN | |
| semantics | TEXT | NN | CK in (state,event) |
| valid_ranges | DATEMULTIRANGE | NN | |
| status_now | TEXT | NN | CK in (live,ended,ended_unknown_date,not_started,disputed,event) |
| first_start | DATE | null | |
| last_start | DATE | null | |
| last_end | DATE | null | |
| undated_present | INTEGER | NN, D=0 | |
| undated_past | INTEGER | NN, D=0 | |
| disputed | BOOLEAN | NN, D=false | |
| recorded_at | TIMESTAMPTZ | NN, D=now() | |
| retired_at | TIMESTAMPTZ | null | |
| evidence_hash | TEXT | NN | |
| refreshed_at | TIMESTAMPTZ | NN, D=now() |
- Indexes:
link_relationships_to_idx (to_page_id, link_type, scope);link_relationships_source_idx (source_id, status_now). - RLS:
DO $rls$at 3367-3371. - Readers:
src/core/mentions/referrers.ts,src/core/ops/valid-time-read.ts,src/core/cycle/edge-contradictions.ts,src/core/link-temporal-apply.ts. - Writers:
src/core/link-relationships.ts.
link_edge_proposals
Source: schema.sql:3372-3398. Proposed typed edge with status and cost, one per (from, a_to, b_to, link_type, evidence_hash).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NN | |
| from_page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; UQ part |
| a_to_page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; UQ part |
| b_to_page_id | INTEGER | NN | FK -> pages(id) ON DELETE CASCADE; UQ part |
| link_type | TEXT | NN | UQ part |
| evidence_hash | TEXT | NN | UQ part |
| status | TEXT | NN | CK in (proposed,applied,rejected,undone,stale,reverted_by_user,undated_unresolved,ambiguous_same_date,compatible,error) |
| ending_to_page_id | INTEGER | null | FK -> pages(id) ON DELETE SET NULL |
| close_date | DATE | null | |
| born_closed | BOOLEAN | NN, D=false | |
| model | TEXT | null | |
| confidence | REAL | null | |
| cost_usd | REAL | null | |
| generated_line | TEXT | null | |
| detail | TEXT | null | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- UQ
(from_page_id, a_to_page_id, b_to_page_id, link_type, evidence_hash). - Indexes:
link_edge_proposals_status_idx (source_id, status, created_at). - RLS:
DO $rls$at 3394-3398. - Readers:
src/commands/doctor/checks/edge-validity.ts,src/commands/edge-proposals.ts,src/core/cycle/edge-contradictions.ts. - Writers:
src/core/cycle/edge-contradictions.ts,src/commands/edge-proposals.ts.
graph_generation_seq
Source: schema.sql:3399. Sequence graph_generation_seq (link-temporal-schema.ts:18, :96). Bumped with SELECT nextval('graph_generation_seq') in src/core/link-relationships.ts:164 whenever relationship state changes, so cached graph queries can detect a change. Created by migration v204 (src/core/schema-migrations/v204-links-temporal-state.ts).
Core edit notices (generated, core-memory-schema.ts)
core_edit_notices
Source: schema.sql:3404-3421. Remote-edit notice for always-loaded core memory, cleared when acknowledged.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NN | |
| slug | TEXT | NN | |
| page_id | BIGINT | null | |
| revision | TEXT | null | |
| base_revision | TEXT | null | |
| base_text | TEXT | null | |
| actor | TEXT | NN | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| acked_at | TIMESTAMPTZ | null |
- Indexes: partial
core_edit_notices_pending_idx (source_id, slug, id) WHERE acked_at IS NULL. - RLS:
DO $rls$at 3417-3421. - Readers:
src/core/core-memory.ts,src/core/facts/purge-verify.ts,src/commands/core.ts. - Writers:
src/commands/core.ts,src/core/persistence/core-guard.ts,src/core/facts/purge.ts.
Purge tables (generated, facts/purge-schema.ts)
The purge tables record that a fact, take, or page was purged. Their guard triggers are installed by the memory_purge migration (comment at 3424).
fact_purges
Source: schema.sql:3426-3441. Tombstone for a purged fact, by hash and visibility.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE; PK part |
| visibility | TEXT | NN | PK part; CK in (private,world) |
| subject | TEXT | NN, D='*' | PK part |
| fact_hash | TEXT | NN | PK part |
| request_id | UUID | null | |
| actor | TEXT | null | |
| reason | TEXT | null | |
| purged_at | TIMESTAMPTZ | NN, D=now() |
- PK
(source_id, visibility, subject, fact_hash). - Extra indexes (3500-3501):
fact_purges_subject_idx (source_id, subject, fact_hash) INCLUDE (visibility, purged_at); partialfact_purges_all_subjects_idx (source_id, purged_at) WHERE subject = '*'. - RLS:
DO $rls$at 3437-3441. - Readers:
src/core/search-vector-backfill.ts,src/core/facts/purge-overlay.ts,src/core/facts/purge-schema.ts,src/core/facts/withdrawal.ts. - Writers:
src/core/facts/purge.ts,src/core/persistence/page-purge.ts,src/core/belief/purge-mirror.ts.
take_purges
Source: schema.sql:3442-3454. Tombstone for a purged take (claim).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE; PK part |
| subject | TEXT | NN, D='*' | PK part |
| claim_hash | TEXT | NN | PK part |
| request_id | UUID | null | |
| purged_at | TIMESTAMPTZ | NN, D=now() |
- PK
(source_id, subject, claim_hash). - RLS:
DO $rls$at 3450-3454. - Readers:
src/core/facts/purge-overlay.ts,src/core/facts/purge-schema.ts,src/core/persistence/page-purge.ts,src/core/belief/append.ts. - Writers:
src/core/facts/purge.ts,src/core/belief/purge-mirror.ts,src/core/persistence/page-purge.ts.
page_purges
Source: schema.sql:3455-3467. Tombstone for a purged page by content hash.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE; PK part |
| content_hash | TEXT | NN | PK part |
| slug | TEXT | NN | |
| request_id | UUID | null | |
| purged_at | TIMESTAMPTZ | NN, D=now() |
- PK
(source_id, content_hash). - RLS:
DO $rls$at 3463-3467. - Readers:
src/core/repair/purged-page-facts.ts,src/core/facts/purge-schema.ts,src/core/facts/purge-verify.ts,src/core/persistence/purged-intent.ts. - Writers:
src/core/persistence/page-purge.ts,src/core/belief/purge-mirror.ts.
derivation_inputs
Source: schema.sql:3468-3482. Lineage: which input rows fed which derived row.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| derived_table | TEXT | NN | PK part |
| derived_id | TEXT | NN | PK part |
| input_table | TEXT | NN | PK part |
| input_id | TEXT | NN | PK part |
| source_id | TEXT | null | FK -> sources(id) ON DELETE CASCADE |
| created_at | TIMESTAMPTZ | NN, D=now() |
- PK
(derived_table, derived_id, input_table, input_id). - Indexes:
idx_derivation_inputs_input (input_table, input_id). - RLS:
DO $rls$at 3478-3482. - Readers:
src/core/facts/derivation-inputs.ts,src/core/facts/purge-verify.ts,src/core/trust/propagation.ts,src/core/search/transcript-quote.ts. - Writers:
src/core/facts/derivation-inputs.ts.
needs_rederive
Source: schema.sql:3483-3496. Derived rows that must be recomputed.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| derived_table | TEXT | NN | PK part |
| derived_id | TEXT | NN | PK part |
| source_id | TEXT | null | FK -> sources(id) ON DELETE CASCADE |
| request_id | UUID | null | |
| reason | TEXT | NN | |
| marked_at | TIMESTAMPTZ | NN, D=now() |
- PK
(derived_table, derived_id). - RLS:
DO $rls$at 3492-3496. - Readers:
src/core/eligibility/sql.ts,src/core/eligibility/explain.ts. - Writers:
src/core/facts/derivation-inputs.ts,src/core/trust/propagation.ts.
Write gate (generated, write-gate-schema.ts)
write_gate_receipts
Source: schema.sql:3506-3523. Verdict receipt for a write-gate check on a page, fact, take or timeline entry.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| target_table | TEXT | NN | CK in (pages,facts,takes,timeline_entries,write_gate_holds) |
| target_id | TEXT | NN | UQ part |
| source_id | TEXT | null | |
| content_hash | TEXT | NN | UQ part |
| tier | TEXT | NN | |
| detector_version | INTEGER | NN | UQ part |
| verdict | TEXT | NN | CK in (flag,quarantine) |
| reason_families | TEXT[] | NN, D='{}' | |
| reasons | TEXT[] | NN, D='{}' | |
| detector_error | BOOLEAN | NN, D=false | |
| request_id | TEXT | null | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| last_seen_at | TIMESTAMPTZ | NN, D=now() |
- UQ
write_gate_receipts_target_idx (target_table, target_id, content_hash, detector_version); indexwrite_gate_receipts_seen_idx (last_seen_at). - RLS:
DO $rls$at 3547-3552 (withwrite_gate_holds). - Readers:
src/core/write-gate-store.ts,src/core/facts/purge.ts,src/core/trust/review.ts,src/eval/brainbench/metrics/trust-reads.ts. - Writers:
src/core/write-gate-store.ts,src/core/facts/purge.ts,src/core/persistence/page-purge.ts,src/core/persistence/purge-slug-stores.ts.
write_gate_holds
Source: schema.sql:3524-3552. Held fact or take awaiting release or drop.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| kind | TEXT | NN | CK in (fact,take) |
| source_id | TEXT | NN | UQ part |
| slug | TEXT | NN, D='' | UQ part |
| fingerprint | TEXT | NN | UQ part |
| detector_version | INTEGER | NN | UQ part |
| tier | TEXT | NN | |
| reason_families | TEXT[] | NN, D='{}' | |
| reasons | TEXT[] | NN, D='{}' | |
| detector_error | BOOLEAN | NN, D=false | |
| payload | JSONB | NN | |
| write_origin | JSONB | null | |
| request_id | TEXT | null | |
| status | TEXT | NN, D='held' | CK in (held,released,dropped) |
| seen_count | INTEGER | NN, D=1 | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| last_seen_at | TIMESTAMPTZ | NN, D=now() | |
| decided_at | TIMESTAMPTZ | null | |
| decided_by | TEXT | null |
- Indexes: UNIQUE
write_gate_holds_dedupe_idx (source_id, slug, fingerprint, detector_version);write_gate_holds_status_idx (status, source_id, id). - RLS: as above (3547-3552).
- Readers:
src/core/write-gate-store.ts,src/eval/brainbench/metrics/trust-reads.ts,src/core/trust/release-publish.ts,src/core/trust/owner-actions.ts. - Writers:
src/core/write-gate-store.ts,src/core/facts/purge.ts,src/core/trust/owner-actions.ts,src/core/source-delete.ts.
Sources upstream columns (hand-written, lines 3559-3561)
ALTER TABLE sources ADD COLUMN IF NOT EXISTS adds three columns to the sources table, which is defined earlier in the file. Columns: upstream_checked_at TIMESTAMPTZ (no default), upstream_commit TEXT, upstream_behind INTEGER. They record the last upstream observation per source (#5255/#5176; migration v190).
- Writers:
src/core/sync-upstream.ts(line 73,UPDATE sources SET upstream_checked_at=..., upstream_commit=..., upstream_behind=...). - Readers:
src/core/source-health.ts,src/core/sync-upstream.ts(line 103-107),src/core/deletion-inventory.ts,src/commands/doctor/checks/managed-guard.ts,src/commands/doctor/checks/extraction-sync.ts,src/core/persistence/worktree-refresh.ts.
Pinned questions (generated, questions/schema.ts)
pinned_questions
Source: schema.sql:3565-3599. A standing question per source and slug, with its answer, refresh lease and watermark (C4).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NN | UQ part |
| slug | TEXT | NN | UQ part |
| question | TEXT | NN | |
| scope_slug_prefix | TEXT | null | |
| scope_entity | TEXT | null | |
| state | TEXT | NN, D='active' | |
| inactive_reason | TEXT | null | |
| publish_mode | TEXT | NN, D='publish' | |
| model | TEXT | null | |
| cooldown_days | REAL | null | |
| origin | TEXT | NN, D='pin' | |
| created_by | TEXT | NN | |
| revision | BIGINT | NN, D=1 | |
| answer_revision | INTEGER | NN, D=0 | |
| answer | TEXT | null | |
| answer_model | TEXT | null | |
| last_refresh_at | TIMESTAMPTZ | null | |
| last_attempt_at | TIMESTAMPTZ | null | |
| last_error | TEXT | null | |
| watermark_generation | BIGINT | null | |
| watermark_fact_id | BIGINT | null | |
| watermark_at | TIMESTAMPTZ | null | |
| lease_token | TEXT | null | |
| lease_owner | TEXT | null | |
| lease_expires_at | TIMESTAMPTZ | null | |
| refresh_attempts | INTEGER | NN, D=0 | |
| spend_usd | DOUBLE PRECISION | NN, D=0 | |
| created_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| archived_at | TIMESTAMPTZ | null |
- UQ constraint
pinned_questions_source_slug_key (source_id, slug). No FK tosources. - Indexes:
pinned_questions_state_idx (state, source_id). - RLS:
DO $rls$at 3617-3619. - Readers:
src/core/questions/refresh.ts,src/core/questions/store.ts. - Writers:
src/core/questions/refresh.ts,src/core/questions/store.ts,src/core/questions/auto-think-migration.ts.
question_evidence
Source: schema.sql:3600-3625. Evidence pointers for each sentence of a pinned answer, pinned to an answer revision.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| question_id | BIGINT | NN | no FK |
| answer_revision | INTEGER | NN | |
| sentence_id | TEXT | NN | |
| kind | TEXT | NN | |
| source_id | TEXT | NN | |
| page_id | INTEGER | null | |
| page_slug | TEXT | null | |
| page_generation | BIGINT | null | |
| page_revision | TEXT | null | |
| item_id | BIGINT | null | |
| content_hash | TEXT | null | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Indexes:
question_evidence_question_idx (question_id, answer_revision). - RLS:
DO $rls$at 3621-3625. - Readers:
src/core/questions/refresh.ts,src/core/questions/freshness.ts. - Writers:
src/core/questions/refresh.ts.
Belief ledger (generated, belief/schema.ts)
Append-only audit state. The design is in docs/designs/BELIEF_LEDGER.md (comment at 3628).
belief_events
Source: schema.sql:3630-3670. One row per belief event (page, fact, take, link, link transition, or coverage). Each row carries a hash chain (prior_hash to state_hash) and a publication id.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| event_key | BYTEA | NN, UQ | |
| source_id | TEXT | NN | |
| source_incarnation | UUID | NN | |
| page_lifetime | UUID | null | |
| slug | TEXT | NN | |
| subject_kind | TEXT | NN | CK in (page,fact,take,link,link_transition,coverage) |
| subject_key | TEXT | NN | |
| event_type | TEXT | NN | |
| origin | TEXT | NN, D='live' | CK in (live,unmanaged,baseline,backfill,reconcile) |
| trust_tier | TEXT | NN | |
| writer_tier | TEXT | null | |
| tier_basis | TEXT | NN, D='row' | CK in (row,writer,unknown) |
| visibility | TEXT | NN | |
| publication_id | UUID | NN | |
| ord | INTEGER | NN | |
| recorded_at | TIMESTAMPTZ | NN, D=clock_timestamp() | |
| source_time | TIMESTAMPTZ | null | |
| valid_from | TIMESTAMPTZ | null | |
| valid_until | TIMESTAMPTZ | null | |
| value | JSONB | null | |
| value_hash | TEXT | null | |
| prior_hash | TEXT | null | |
| state_hash | TEXT | NN | |
| cause | JSONB | NN | |
| claim_fp | TEXT | null | |
| purged | BOOLEAN | NN, D=false | |
| redacted_fields | TEXT[] | null |
- Indexes:
belief_events_page (source_id, slug, id);belief_events_subject (source_id, subject_kind, subject_key, id);belief_events_recorded (recorded_at); partialbelief_events_claim_fp (source_id, claim_fp) WHERE claim_fp IS NOT NULL; partialbelief_events_lifetime (page_lifetime, id) WHERE page_lifetime IS NOT NULL;belief_events_publication (publication_id). - RLS:
DO $rls$at 3666-3670. - Readers:
src/core/ops/known-as-of.ts,src/core/facts/purge-verify.ts,src/core/forecast-journal.ts,src/core/belief/preimages.ts. - Writers:
src/core/belief/append.ts,src/core/belief/purge.ts.
belief_page_lifetimes
Source: schema.sql:3671-3686. A page's lifetime (its identity across renames or recreation), open until ended_at.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| page_lifetime | UUID | PK | |
| source_id | TEXT | NN | |
| source_incarnation | UUID | NN | |
| slug | TEXT | NN | |
| page_id | INTEGER | null | no FK |
| started_at | TIMESTAMPTZ | NN, D=clock_timestamp() | |
| ended_at | TIMESTAMPTZ | null |
- Indexes: UNIQUE partial
belief_page_lifetimes_open (source_incarnation, slug) WHERE ended_at IS NULL;belief_page_lifetimes_slug (source_id, slug). - RLS:
DO $rls$at 3682-3686. - Readers:
src/core/forecast-journal.ts,src/core/belief/preimages.ts,src/core/belief/coverage-report.ts,src/core/belief/purge.ts. - Writers:
src/core/belief/lifetimes.ts.
belief_commit_marks
Source: schema.sql:3687-3695. Observed commit time per publication.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| publication_id | UUID | PK | |
| observed_at | TIMESTAMPTZ | NN |
- RLS:
DO $rls$at 3691-3695. - Readers:
src/core/belief/coverage-report.ts,src/core/belief/page-at-time.ts. - Writers:
src/core/belief/append.ts.
belief_retier_manifest
Source: schema.sql:3696-3709. Manifest of tier changes applied by a summary event.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| summary_event | BIGINT | NN | PK part |
| source_id | TEXT | NN | |
| subject_kind | TEXT | NN | PK part |
| subject_key | TEXT | NN | PK part |
| from_tier | TEXT | null | |
| to_tier | TEXT | NN |
- PK
(summary_event, subject_kind, subject_key). - RLS:
DO $rls$at 3705-3709. - Readers and writers:
src/core/belief/retier.ts.
belief_redactions
Source: schema.sql:3710-3727. Redacted field per subject, linked to the purge request that redacted it, with an optional release.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| id | BIGSERIAL | PK | |
| source_id | TEXT | NN | |
| subject_key | TEXT | NN | |
| field | TEXT | NN | |
| purge_request | UUID | NN | |
| field_hash | TEXT | NN | |
| released_by | UUID | null | |
| released_hash | TEXT | null | |
| created_at | TIMESTAMPTZ | NN, D=now() |
- Indexes:
belief_redactions_subject (source_id, subject_key, field);belief_redactions_field_hash (source_id, field_hash). - RLS:
DO $rls$at 3723-3727. - Readers and writers:
src/core/belief/append.ts,src/core/belief/purge.ts.
Sync notifier (hand-written, lines 3730-3777)
sync_wanted_markers
Source: schema.sql:3733-3744 (GBRA-133 PR 2a, migration v262). Pending sync request per source and intent (freshness, pull, full).
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | NN | FK -> sources(id) ON DELETE CASCADE; PK part |
| intent | TEXT | NN | PK part; CK in (freshness,pull,full) |
| source_incarnation | TEXT | null | stored as TEXT, not UUID |
| wanted_head | TEXT | null | |
| generation | BIGINT | NN, D=1 | |
| first_due_at | TIMESTAMPTZ | NN, D=now() | |
| updated_at | TIMESTAMPTZ | NN, D=now() | |
| requested_by | TEXT | NN | |
| job_id | BIGINT | null |
- PK
(source_id, intent). - Readers:
src/core/notifier/store.ts,src/core/notifier/status.ts,src/core/sync-pending.ts. - Writers:
src/core/notifier/store.ts.
sync_notifier_leases
Source: schema.sql:3745-3753. Per-host lease for the notifier process, with a fence counter.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| host_id | TEXT | PK | |
| owner_id | TEXT | NN | |
| fence | BIGINT | NN, D=1 | |
| resident_kind | TEXT | NN | |
| pid | INTEGER | null | |
| renewed_at | TIMESTAMPTZ | NN, D=now() | |
| expires_at | TIMESTAMPTZ | NN |
- Readers:
src/core/notifier/store.ts,src/core/notifier/status.ts. - Writers:
src/core/notifier/store.ts.
sync_notifier_sources
Source: schema.sql:3754-3777. Per-source notifier state: due or running or retry, last error, observed head and owner.
| Column | Type | Null / default | Constraints, FK |
|---|---|---|---|
| source_id | TEXT | PK; FK -> sources(id) ON DELETE CASCADE |
|
| source_incarnation | TEXT | null | stored as TEXT |
| syncer_host_id | TEXT | null | |
| syncer_recorded_at | TIMESTAMPTZ | null | |
| state | TEXT | NN, D='idle' | CK in (idle,due,running,retry,operator_blocked) |
| reason | TEXT | null | |
| first_due_at | TIMESTAMPTZ | null | |
| attempts | INTEGER | NN, D=0 | |
| next_retry_at | TIMESTAMPTZ | null | |
| last_progress_at | TIMESTAMPTZ | null | |
| last_error_code | TEXT | null | |
| last_error | TEXT | null | |
| last_error_at | TIMESTAMPTZ | null | |
| trace_path | TEXT | null | |
| observed_head | TEXT | null | |
| observed_at | TIMESTAMPTZ | null | |
| detection_mode | TEXT | null | CK in (watch,poll) |
| owner_host_id | TEXT | null | |
| owner_kind | TEXT | null | |
| owner_pid | INTEGER | null | |
| owner_seen_at | TIMESTAMPTZ | null | |
| updated_at | TIMESTAMPTZ | NN, D=now() |
- Readers:
src/core/notifier/store.ts,src/core/notifier/resident.ts,src/core/notifier/status.ts,src/core/sync-pending.ts. - Writers:
src/core/notifier/store.ts.
Search-path hardening (generated, function-search-path.ts)
DO $harden$ at schema.sql:3781-3793 runs last in the file (comment at 3779). For each function in public whose proconfig search_path is pg_catalog, public or pg_catalog, and whose name is in a fixed list of about 60 names (gbrain_* functions, auto_enable_rls, bump_page_generation_fn, enforce_minion_queue_protocol, notify_minion_job_change, update_page_search_vector, update_chunk_search_vector, and others), it runs ALTER FUNCTION ... SET search_path = <path>, pg_temp. The owner check is pg_has_role(current_user, p.proowner, 'USAGE'). The block also changes functions defined outside this range.
Other objects in range
- No
GRANT,REVOKEorCOMMENT ONstatements appear in lines 1899-3794 (checked with a line-start grep and by reading the range). - Sequences:
page_body_occurrence_id_seq(2161),graph_generation_seq(3399). - Functions (13 in range, all plpgsql, all with
SET search_path = pg_catalog, public, pg_temp):gbrain_advance_page_revision,gbrain_advance_tag_revision,gbrain_mark_derivation_scope_entry,gbrain_refuse_purging_derivation,gbrain_require_managed_writer,gbrain_count_git_canonical_sources,gbrain_queue_page_projection,gbrain_require_persistence_protocol(RETURNS void, a helper the other guards call),gbrain_guard_request_protocol,gbrain_guard_effect_protocol,gbrain_guard_protocol_activation,gbrain_guard_skill_publication,gbrain_lease_shared_skill_delivery. - Distinct trigger names (17):
pages_knowledge_revision,tags_knowledge_revision,page_body_manifest_derivation_scope_retire,page_body_manifest_derivation_scope_delete,pages_derivation_scope_delete,page_segment_derivations_refuse_purging,managed_writer_guard(10 tables),git_canonical_sources_count_insert,git_canonical_sources_count_update,git_canonical_sources_count_delete,trg_pages_search_vector,pages_projection_queue,gbrain_request_protocol,gbrain_effect_protocol,gbrain_protocol_activation,gbrain_skill_publication(3 tables),shared_skill_delivery_lease. - Session GUCs read by these functions (set by the writer, not by DDL):
gbrain.materializing_revision,gbrain.write_sources,gbrain.topology_change,gbrain.git_protocol,gbrain.persistence_protocol,gbrain.writer_quiesced. Grep for their setters is out of scope for this part.
Table count in this range
Tables created in lines 1899-3794: 76 (every CREATE TABLE in the range has a section above). Tables altered but not created here: page_versions, sources. Sequences: 2. Functions: 13 (CREATE OR REPLACE FUNCTION). Triggers: 19 CREATE TRIGGER statements, 17 distinct names, 28 table attachments (the managed_writer_guard DO block attaches 10). DO blocks: 32, most of them RLS enablement.
Parity risks noted
- Session GUC capability model.
gbrain_require_managed_writer,gbrain_guard_skill_publication,gbrain_guard_protocol_activationand the persistence protocol functions readgbrain.write_sources,gbrain.topology_change,gbrain.git_protocol,gbrain.persistence_protocolandgbrain.writer_quiesced. An embedded Rust store needs an equivalent per-connection capability context, or these guards silently disappear. - RLS-dependent logic. Several triggers call
row_security_active()and introspectpg_policy,pg_rolesandpg_has_role. Table RLS is enabled only for superuser or BYPASSRLS roles. None of this maps to PGLite or an embedded engine. The "writer state is unreadable to this role" error path has no clear analogue. - Trigger cascades that write other tables. Revision bumps (
pages.knowledge_revisionfromtags), the projection queue (page_projection_jobs), derivation-purge fan-out (page_segment_derivation_purge_pages), delivery leases (shared_skill_revision_leases), and the git-canonical counter (git_canonical_brain) all run inside the DB. Each Rust write path must replicate them, or the invariants break. - Trigger-side text normalization.
gbrain_refuse_purging_derivationlowercases text, maps Greek final sigma and Turkish dotted i withtranslate/replace, callsgbrain_fact_normalize(), pads with spaces, and matches withLIKE ANYandstrpos. Purge matching in Rust must give identical results for these inputs. - JSON path semantics. The same trigger walks JSON with
jsonb_path_query(doc, 'strict $.** ? (@.type() == "string")')andkeyvalue(). Lease trigger uses the JSONB?operator. Rust JSON walking must match these in strict mode and for key/value enumeration. - Advisory locks.
pg_advisory_xact_lock_shared(hashtextextended('gbrain:derivation-scope:' || src, 0))serializes purge fan-out per source. An embedded store needs an equivalent lock per source, with the same shared/exclusive semantics. - Clock and randomness defaults.
gen_random_uuid()(many tables and triggers),now()(transaction time) andclock_timestamp()(wall clock;belief_events.recorded_at,belief_page_lifetimes.started_at) differ in meaning. Rust must pick the right one per column. - Generated columns in the PK.
page_body_manifest.staging_keyisCOALESCE(staging_id, nil-uuid) STOREDand is part of the PK, so NULL staging ids compare equal.shared_skill_revisions.stored_bytesisoctet_length(files::text)+octet_length(metadata::text), which depends on JSON text serialization. - Null-distinct semantics.
link_transitions_identity_idxisUNIQUE ... NULLS NOT DISTINCT(requires PG 15+). Itsline_hashdefaults to'', not NULL. Several partial unique indexes depend onWHEREpredicates (derived_generations_one_live,belief_page_lifetimes_open,persistence_worktree_refreshes_active,decide_proposals_claim_pair_uidxonLEAST/GREATEST). - Numeric types.
REAL(4-byte) is used for decide probabilities and link confidences;DOUBLE PRECISIONfor spend;NUMERICfor chronicle costs. Rounding parity matters for threshold comparisons.DATEMULTIRANGEinlink_relationships.valid_rangeshas no direct embedded equivalent. - Regex and binary CHECKs.
source_ingestion_receiptsuses POSIX~regex on SHA strings;shared_skilllease trigger usessubstring(item from '@([^@]+)$'); several columns useoctet_length(...) = 32for raw digests (BYTEA). - Sequences as ordering keys.
persistence_requests.sequence(BIGSERIAL, UNIQUE) is the queue order;graph_generation_seqis bumped bynextvalto invalidate caches. Gaps and rollback behavior of sequences differ from a monotonic counter. - Foreign-key actions and targets. Mixed
ON DELETE CASCADE,SET NULLandRESTRICT.shared_skill_revision_leases->shared_skill_revisionsis a composite FK withRESTRICT. Several FKs targetsources(incarnation), which needs a UNIQUE constraint defined earlier in the file. Some columns that look like keys have no FK (belief_page_lifetimes.page_id,question_evidence.question_id,chronicle_judge_reservations.page_id). - Inconsistent identifier types.
source_incarnationis UUID in most tables but TEXT insync_wanted_markersandsync_notifier_sources.page_segment_derivation_purges.page_idsisINTEGER[]and cast toint[]in triggers.persistence_brain.brain_idis UUID, butshared_skill_delivery_batches.authority_digestis TEXT. - Error contract. Triggers raise
P0001or42501with message prefixes (writer_coordinator_required:,writer_upgrade_required:,writer_not_quiesced:,git_protocol_required:,unsupported_mutation_protocol:,revision_unavailable:) and a JSONDETAILpayload. These strings can surface in the CLI and MCP JSON output and must be preserved. - Column order is significant. Comments at 1963-1965 and 3558 say
ALTER ADD COLUMNplacement exists so a fresh install has the same ordinal positions as an upgraded brain. Any positional row mapping in Rust must use names, not ordinals. - Schema-DDL-as-code. The generated regions come from TS modules and are checked by
scripts/build-schema.ts. Any Rust port that copies DDL fromschema.sqlmust also copy the generated-region edits, or the two sources will drift (the build script exits non-zero on unknown statement kinds).
Migration system and migrations v002-v160
Source: garrytan/gbrain at commit 8bbfd0b5e8 (read-only). Scope: runner, schema blobs, build scripts, and migration files v002-v160 (155 files; there are no files for v017-v019 or v100). Migrations v161-v318 are out of scope and are not covered.
Legend used below: "PG" = Postgres engine, "PGL" = PGLite engine, "tx" = wrapped in engine.transaction() by the runner, "NOTX" = transaction: false, "handler-only" = sql is empty and only a TS handler runs.
(a1) Runner and version tracking
| Item | Where | Facts |
|---|---|---|
| Version key | config table, key version |
Read by getConfig('version') (src/core/migrate.ts:381-382, src/core/migrate-pending.ts:44-54). Written with setConfig('version', N) after each migration succeeds (src/core/migrate.ts:513). Missing value is treated as 1. |
| Seed | src/schema.sql:726 and PGLite template (scripts/build-schema.ts:426-429) |
Fresh blob inserts ('version','1'); PGLite also inserts ('engine','pglite'). Blob also seeds embedding_model, embedding_dimensions, chunk_strategy. |
| Latest | src/core/migrate.ts:84-88, src/core/schema-migrations/latest.generated.ts:7 |
LATEST_VERSION = max registry version (value 318 in the generated file, outside this part's range). registeredLatestVersion() re-reads the live registry. |
| Registry | src/core/schema-migrations/registry.generated.ts (639 lines) |
Static imports of every vNNN export; MIGRATIONS array keeps a frozen historical order (scripts/build-schema-migrations.ts:48-52). The runner sorts by version before running (src/core/migrate.ts:389), so array order has no effect on execution. |
| Pending check | src/core/migrate-pending.ts:44-54 |
hasPendingMigrations reads version and compares to LATEST_SCHEMA_VERSION; on error it returns true. Used by connectEngine to skip initSchema() when nothing is pending. |
| Main entry | src/core/migrate.ts:376-378 runMigrations -> runMigrationsLocked (:380-542) |
Takes schema lock, re-reads version, sorts, runs each pending migration in order. |
| Fresh vs upgrade | src/core/migrate.ts:485-498 |
freshInstall = current <= 1 && pending = all. Fresh installs print one summary line; upgrades print each [N] name. GBRAIN_MIGRATE_VERBOSE=1 forces the verbose output. |
| Self-heal passes (run every call, before pending loop) | src/core/migrate.ts:401-449 |
repairTimelineDedupIndex (#2038, timeline-dedup-repair.ts:116), repairPagesUpsertArbiter (#550, pages-upsert-arbiter.ts:115: restores UNIQUE(source_id,slug)), repairLinkSourceCheck (#4613, only if current >= 114, link-source-check-repair.ts:103). All best-effort, log to stderr. |
| Function search_path | src/core/migrate.ts:457-469 |
hardenFunctionSearchPaths (function-search-path.ts:100) puts pg_temp back on gbrain functions after any run, and in a finally (#7632). |
| Page revision backfill | src/core/migrate.ts:451, :472, :540 |
resumePageRevisionBackfill runs after the migration loop. |
| Pre-flight | src/core/migrate.ts:133-145, :504 |
Warns about PG idle in transaction sessions older than 5 minutes (getIdleBlockers, :113-127). |
| Owned-table guard | src/core/migrate.ts:392 |
assertOwnedTableShapes runs before any repair or migration (#9190). |
Per-migration execution (applyOneMigration, src/core/migrate.ts:546-618):
- SQL choice:
const sql = m.sqlFor?.[engine.kind] ?? m.sql(:548).??falls back tosqlonly when the engine key is absent; an explicit''(for examplev024pglite,v076pglite) means "run nothing on that engine". - Retry wrapper
runMigrationSQLWithRetry(:157-207): up to 3 attempts, backoff 5 s / 15 s / 45 s (GBRAIN_MIGRATE_BACKOFF_MSoverrides for tests), retries only on statement timeout (57014) or connection-reset errors. Final retryable failure throwsMigrationRetryExhausted(:59-76,:190-197). Migration preludes (runMigrationPrelude,:184) run before each attempt. - Execution (
runMigrationSQL,:214-244):useTransaction = m.transaction !== false. If true, or the engine is PGLite, it runs insideengine.transaction. On PG the transaction first setsSET LOCAL statement_timeout(frommigrationStatementTimeoutMs()) andSET LOCAL lock_timeout(:226-234, #5227). A PG lock timeout is rethrown asschemaLockBlockedError(:238). Iftransaction: falseon PG, it goes throughrunMigrationStatement(src/core/schema-migrations/helpers.ts:90-104): a reserved backend with sessionstatement_timeout, restored afterwards. - Handler (
:590-592): runs after the SQL, outside the SQL transaction. A failure after the SQL committed leaves the schema changed but the version not stamped; the next run re-runs the migration. - Verify hook (
:598-616): optionalverify(engine). On failure it re-runs an idempotent migration once, else throwsMigrationDriftError(:43-52). No migration in v002-v160 declaresverify(v091's only match is a comment; v160 useshandler). - Stamp:
setConfig('version', m.version)after the migration (:513). Timeouts are recorded viarecordMigrationTimeout(:521) and cleared at the end (:539). - Preludes:
src/core/migration-preludes.ts:233-234maps version -> batched backfill prelude for11, 28, 33, 116(in range) and161, 221(out of range).v011batcheslinks.link_source;v028batchescontent_chunkssearch vectors;v033batchesmcp_request_logagent names;v116batchessource_id. Each batch runs before the migration's SQL.
Lock (src/core/schema-migration-lock.ts:15-38):
- Postgres:
engine.holdSchemaMigrationLock(key 42 insrc/core/postgres-engine/init-schema-lock.ts:29,INIT_SCHEMA_LOCK_KEY = 42), held across processes and connections. - PGLite (no such method): serialized in-process by a per-engine promise chain (
inProcessSchemaLocks,:12,:23-32). assertSchemaMigrationLockHeld()runs before each migration (src/core/migrate.ts:507).- Nested calls on the same engine reuse the lock through
AsyncLocalStorage(:11,:16-17).
Retry on deadlock: tryRunPendingMigrations (src/core/migrate.ts:303-356) retries initSchema once after 250 ms on SQLSTATE 40P01, then polls hasPendingMigrations up to 5 s and returns race_resolved if another runner finished.
(a2) initSchema entry points (what replays before migrations)
| Engine | Entry | Sequence |
|---|---|---|
| PGLite | PGLiteEngine.initSchema (src/core/pglite-engine.ts:1147-1180) |
Snapshot short-circuit (:1150-1152). Embedding dims/model from settings, else gateway, else stored identity (:1152-1171). withSchemaMigrationLock -> assertSchemaNotAhead (#7776) -> applyForwardReferenceBootstrap -> db.exec(getPGLiteSchema(dims, model)) -> runMigrations. |
| Postgres | PostgresEngine.initSchema (src/core/postgres-engine.ts:576-660) |
Uses the DDL pool when dual-pool. Dims/model as PGLite. withSchemaMigrationLock -> assertSchemaNotAhead -> reserved conn -> applyForwardReferenceBootstrap(conn) -> retrySchemaReplayOnDeadlock(withSchemaLockTimeout(conn.unsafe(getPostgresSchema))) (:642) -> runMigrations (:652) -> verifySchema (:656) -> dropZombieIndexes sweep of invalid HNSW indexes (:663-668). |
| Standalone PG | src/core/db.ts:596-612 (initSchema) |
Session pg_advisory_lock(42), applyPostgresForwardReferenceBootstrap, conn.unsafe(SCHEMA_SQL), unlock. Does NOT call runMigrations. |
(a3) Forward-reference bootstrap, schema verify, missing tables
src/core/engine-sql/bootstrap.ts(756 lines). Single implementation for both engines. Runs before the blob replay so that older brains gain forward-referenced columns that the blob'sCREATE INDEX/FK statements need.- Probes:
FORWARD_REFERENCE_PROBES(:116), one round trip, e.g.pages_exists,source_id_exists,deleted_at_exists,links_exists,link_source_exists,content_chunks,search_vector_exists,embedding_image_exists,mcp_log_exists,ingest_log_source_id_exists,files_page_id_exists,oauth_clients_*_exists, plus later probes. - DDL:
applyCoreTableGaps(:394) andapplyLaterColumnGaps(:601) runALTER TABLE ... ADD COLUMN IF NOT EXISTSand aCREATE TABLE IF NOT EXISTS sources(:419) for the gaps found. - Dialects (
:49-68):PGLITE_BOOTSTRAP_DIALECT(probe schema'public', nodream_verdictsforward reference,chunksEmbeddingImageStep: 'after-pages-deleted-at') andPOSTGRES_BOOTSTRAP_DIALECT(current_schema(),dreamVerdictsForwardReference: true,'after-subagent-provider-id'). - Pre-DDL guard:
assertOwnedTableShapes(:345, #9190) refuses when a gbrain table name is owned by another application.
- Probes:
src/core/schema-verify.ts(307 lines), Postgres only.verifySchema(:243) parsesCREATE TABLEandALTER TABLE ADD COLUMNfromSCHEMA_SQL(:16, imported fromschema-embedded.generated.ts), diffs withinformation_schema.columns(getActualColumns,:154), and self-heals withALTER TABLE x ADD COLUMN IF NOT EXISTS(:282).detectMissingColumns(:207) is the read-only variant used by doctor. Called once at the end of PGinitSchema(postgres-engine.ts:656). Rationale in its header (:4-11): PgBouncer transaction mode can drop ALTERs silently. PGLite does not call it.src/core/schema-missing-tables.ts(100 lines).readMissingTables(:87) checksto_regclassfor everyGRADUATION_INVENTORYtable that the current version should have (CREATED_BY=query_cache: 55,code_traversal_cache: 59,:60). At head it checks all tables; below head only the rebuildable caches.RECREATE(:45-60) recreatesquery_cache(dims from the livefactscolumn) andcode_traversal_cacheby calling the v055/v056/v090/v091/v059 SQL.
(a4) Schema text pipeline: src/schema.sql -> blobs
Canonical sources: src/schema.sql (3794 lines) plus TS "fragment" modules (scripts/build-schema.ts:82-126, FRAGMENTS), which are spliced into BEGIN/END GENERATED regions of schema.sql. Outputs written by bun run build:schema (scripts/build-schema.ts:318-325, :645-660):
| Output | Lines | Produced by | Use |
|---|---|---|---|
src/core/schema-embedded.generated.ts (SCHEMA_SQL, Postgres blob) |
3799 | renderEmbedded (scripts/build-schema.ts:319-321) |
postgres-engine.ts:75, db.ts:8, schema-verify.ts:16, doctor schema-function-bodies. |
src/core/pglite-schema.generated.ts (PGLITE_SCHEMA_SQL_TEMPLATE, getPGLiteSchema(dims, model)) |
3030 | renderPgliteTemplateSql (scripts/build-schema.ts:523) and renderPgliteModule (:582), statement-by-statement rules PGLITE_RULES (:398-449), DO-block classes PGLITE_DO_BLOCKS (:451-453), additions PGLITE_ADDITIONS (:462-490) |
src/core/pglite-schema.ts:8 re-exports; pglite-engine.ts initSchema. Placeholders __EMBEDDING_DIMS__ / __EMBEDDING_MODEL__ replaced at runtime, then applyChunkEmbeddingIndexPolicy and applyFtsLanguagePolicy. |
src/schema.sql (regenerated regions) |
3794 | renderSchemaSql (:307) |
Canonical text. |
The generator fails (exit 1) on unknown statement kinds, unclassified DO blocks, or stale PGLite rules (:328, :397-411, :436-443), so a schema edit cannot silently diverge the two blobs.
Diff of CREATE object names between the two blobs (computed for this part; name-level only, a few comment-text false positives such as "CREATE INDEX since" are ignored):
- Postgres blob only:
pgcryptoextension; tablesdream_verdicts,code_edges_chunk,code_edges_symbol,file_migration_ledger; indexesdream_verdicts_expires_idx,idx_chunks_search_vector,idx_chunks_symbol_qualified,idx_code_edges_*(7),idx_file_migration_ledger_status,idx_pages_updated_at_desc,pages_generation_idx; functionsnotify_minion_job_change,update_chunk_search_vector; triggerschunk_search_vector_trigger,minion_job_notify. Also the persistence indexes inPOSTGRES_CONCURRENT_PERSISTENCE_INDEXES(src/core/persistence/schema.ts:134-138, 9 names) are filtered out of the PG blob (scripts/build-schema.ts:104-107) and built by migrations with CONCURRENTLY. - PGLite blob only: view
page_links(CREATE OR REPLACE VIEW page_links AS SELECT id, from_page_id, to_page_id FROM links,build-schema.ts:466-472); tablesslug_aliases,page_aliases(:482-511) with their indexes; the 9 persistence indexes are created inline. - Column-level PGLite omissions (from
PGLITE_RULES):sources.chunker_version,sources.upstream_checked_at/upstream_commit/upstream_behind;content_chunks.parent_symbol_path/doc_comment/symbol_name_qualified/search_vector(:412-420);dream_verdictstable dropped (:436-437);files.storage_url,minion_attachments.contentSTORAGE EXTERNAL dropped;minion_jobscolumn order moved (:441);page_links/pages_generation_idx/idx_pages_updated_at_descdropped. PGLite gets these from migrations. - Removed from PGLite blob by
PGLITE_DO_BLOCKS(:452): the RLS enablement DO block (146e456b365a), because PGLite has no role system. - PGLite
update_page_search_vectorbody has comment lines stripped (:438-440).
(a5) Engine-specific ordering in the bootstrap and blobs
- Postgres blob contains
dream_verdicts; PGLite creates it by migration v030 (src/core/engine-sql/bootstrap.ts:26and the dialect constants at:58-62). - Fresh PGLite brains keep master's column order by using migrations for the missing columns (
scripts/build-schema.ts:392).
(a6) Build and scaffolding scripts
scripts/build-schema.ts(660 lines):bun run build:schema. Evaluates FRAGMENTS, rewrites schema.sql regions, writes the Postgres blob and PGLite template.--out-dir <dir>writes elsewhere for a freshness guard (:645-660).buildAllat:623.scripts/build-schema-migrations.ts(321 lines):bun run build:schema-migrations. Scanssrc/core/schema-migrations/v<NNN>-<name>.ts, requires exactly oneexport const vNNN: Migration = { version, name, ... }per file whose version/name match the filename (:78-107), no duplicate versions (:164). Writesregistry.generated.ts(static imports in historical order) andlatest.generated.ts(renderLatest,:220-230).--checkexits 1 when stale (:297).bun run new:migration <name>scaffoldsv<max+1>(scaffold,:233-260).src/core/schema-migrations/types.ts:20-61:Migrationinterface. Fields:version,name,sql,sqlFor?: {postgres?, pglite?},transaction?(default true, PG-only effect),handler?,idempotent?(default true; only the verify path reads it),verify?.src/core/schema-migrations/helpers.ts:migrationNotice(:28, suppressed on fresh-install replay),dropInvalidConcurrentIndex(:53-73, PG-only, probespg_index.indisvalid, drops invalid leftovers from a failed CONCURRENTLY build,to_regclass-based),runMigrationStatement(:90-104),buildIndexOnline(:119-156: PG reserves a connection, runsCREATE [UNIQUE] INDEX CONCURRENTLY IF NOT EXISTS, checks validity, thenanalyzeAfterIndexBuild; PGLite runs the plainCREATE INDEX IF NOT EXISTS,:125-132),analyzeYieldingToVacuum(:172-184,lockTableForAnalyze, 60 s statement cap on PG),analyzeAfterIndexBuild(:196-207).
(a7) How PGLite vs Postgres variants are chosen
| Mechanism | Where | Behavior |
|---|---|---|
sqlFor.{postgres,pglite} |
src/core/migrate.ts:548 |
Engine-specific SQL overrides sql. Used in about 30 migrations in range (see table). |
Handler branch on engine.kind |
e.g. v014, v034, v039, v041, v055, v066, v072, v091, v096, v097, v103, v104, v112, v134 |
Postgres uses CREATE INDEX CONCURRENTLY with an invalid-index pre-drop; PGLite uses plain CREATE INDEX. v023 returns immediately on PGLite (handler, if (engine.kind === 'pglite') return). |
transaction: false |
src/core/migrate.ts:219-243, src/core/schema-migrations/types.ts:31-35 |
Postgres only. PGLite always wraps in a transaction (`if (useTransaction |
| Postgres-only timeouts | src/core/migrate.ts:225-234, helpers.ts:90-104 |
SET LOCAL statement_timeout/lock_timeout inside a PG transaction; PGLite skips it (non-fatal try/catch). |
| Engine-specific blobs | postgres-engine.ts:608, pglite-engine.ts:1177 |
getPostgresSchema vs getPGLiteSchema with the dims/model resolved from settings or stored identity. |
| Dims-gated HNSW | src/core/vector-index.ts (hnswMaxDimsForType, readExistingEmbeddingShape), used in v045, v055, v142, v144 |
Index created only when dims fit the pgvector cap; halfvec used above the cap. |
| Postgres-only feature gates in migrations | v011 (server_version_num >= 150000), v039 (pgvector >= 0.5 preflight throws), v024/v029/v030/v031/v032/v094 (BYPASSRLS guard, RAISE EXCEPTION or WARNING), v035 (event trigger), v120 (ALTER FUNCTION ... SET search_path) |
Each is inside DO $$ blocks or sqlFor.postgres. |
(a8) Orchestrated version migrations (src/commands/migrations/)
These are version-upgrade orchestrators run by gbrain apply-migrations, not schema DDL. Registry in src/commands/migrations/index.ts (imports listed at :18-37).
index.ts: TS registry compiled into the binary;getMigration(version)(:62).types.ts:Migrationshape for orchestrators (phases, status, featurePitch, optOut).fresh-install.ts: stamps a brain created bygbrain initas already at the current schema (no replay).in-process.ts: runs migration phases in-process (no child process shell-out, #1605).run-engine.ts: one PGLite engine shared by consecutive migrations in one run.opt-out.ts:optOut: { key }lets the owner decline a host migration via a DB config key (#6466).shared-content.ts:shared_skills.migration.enabledopt-out migration (#6466).v0_11_0.ts: Minions adoption orchestrator, resumable phases.v0_11_0-install.ts: autopilot install; upgrade on a brain with no repo (#6755).v0_12_0.ts: knowledge-graph auto-wire on every install.v0_12_2.ts: repairs JSONB double-encoded by pre-0.12 writers.v0_13_0.ts: frontmatter relationship indexing (typed edges from YAML).v0_13_1.ts: retires thevalidate: falsegrandfather writer (#7814).v0_14_0.ts: shell-jobs adoption and autopilot cooperative fix.v0_16_0.ts: subagent runtime. Per its header (lines 9-13), no DDL here; it only verifiessubagent_messages,subagent_tool_executions,subagent_rate_leasesexist after schema init.v0_18_0.ts: multi-source brains orchestrator (sub-versions split for safety).v0_18_0-storage-backfill.ts: phase-B storage backfill; drives thefile_migration_ledgerstate machine.v0_18_1.ts: RLS hardening; points at schema migration v024.v0_21_0.ts: Code Cathedral II orchestrator (14 bisectable layers).v0_22_4.ts: frontmatter guard adoption (shared validator,parseMarkdown(..., {validate: true})).v0_28_0.ts: Takes + Think + unified model config.v0_29_1.ts: backfill ofeffective_datefor existing pages.v0_31_0.ts: Hot Memory (cross-session facts).v0_32_2.ts: facts join the system-of-record invariant (fence columns from schema v051).v0_43_0.ts: pitch-only (MEMORY_VERBS v1); no schema or data change.v0_46_3.ts: retired notice; orchestrator returnsskipped, no data change.v0_60_31.ts: security fix wave: credential-safe re-chunk of existing pages (ENG-2), plus later phases.v0_60_31-google-file-modes.ts: one-time notice for Google sources whose directory is outside~/.gbrain.
(b) Schema migrations v002-v160
Columns: Ver | name | objects touched | kind (DDL, DATA = backfill/DML, IDX = index build, RLS, FN = function/trigger/view, HANDLER = TS logic) | engine branches | runner mode (tx / NOTX / handler-only).
| Ver | name | objects touched | kind | engine branches | runner mode |
|---|---|---|---|---|---|
| v002 | slugify_existing_pages | page slugs and wikilinks rewritten via engine.updateSlug / rewriteLinks, using slugifyPath from sync.ts |
DATA, HANDLER | none | handler-only |
| v003 | unique_chunk_index | DELETE duplicate content_chunks; UNIQUE INDEX idx_chunks_page_index(page_id, chunk_index) |
DATA, IDX | none | tx |
| v004 | access_tokens_and_mcp_log | TABLE access_tokens + partial IDX idx_access_tokens_hash; TABLE mcp_request_log |
DDL | none | tx |
| v005 | minion_jobs_table | TABLE minion_jobs; IDX claim, status, stalled, delayed, parent |
DDL, IDX | none | tx |
| v006 | agent_orchestration_primitives | ALTER minion_jobs ADD tokens_input/output/cache_read; replace chk_status (adds paused); TABLE minion_inbox + IDX unread |
DDL | none | tx |
| v007 | agent_parity_layer | ALTER minion_jobs (depth, max_children, timeout_ms, timeout_at, remove_on_complete/fail, idempotency_key) + CHECKs; IDX timeout, parent_status; UNIQUE uniq_minion_jobs_idempotency; TABLE minion_attachments; IDX inbox_child_done, attachments_job |
DDL, IDX | none | tx |
| v008 | multi_type_links_constraint | DROP links_from_page_id_to_page_id_key, links_from_to_type_unique; DELETE dup links; ADD UNIQUE links_from_to_type_unique(from,to,type) |
DATA, DDL | none | tx |
| v009 | timeline_dedup_index | DELETE dup timeline_entries; UNIQUE IDX idx_timeline_dedup(page_id,date,summary) |
DATA, IDX | none | tx |
| v010 | drop_timeline_search_trigger | DROP TRIGGER trg_timeline_search_vector; DROP FUNCTION update_page_search_vector_from_timeline |
FN | none | tx |
| v011 | links_provenance_columns | DO-block PG >= 15 gate; ALTER links ADD link_source (+CHECK links_link_source_check), origin_page_id, origin_field; UPDATE backfill; UNIQUE links_from_to_type_source_origin_unique; IDX idx_links_source, idx_links_origin |
DDL, DATA, IDX | SQL version gate only; prelude v011 batches link_source |
tx |
| v012 | budget_ledger | TABLE budget_ledger, budget_reservations; IDX idx_budget_reservations_expires |
DDL, IDX | none | tx |
| v013 | minion_quiet_hours_stagger | ALTER minion_jobs ADD quiet_hours JSONB, stagger_key; partial IDX idx_minion_jobs_stagger_key |
DDL, IDX | none | tx |
| v014 | pages_updated_at_index | IDX idx_pages_updated_at_desc on pages(updated_at DESC) |
IDX | kind: PG CREATE INDEX CONCURRENTLY after dropInvalidConcurrentIndex; PGL plain |
handler-only |
| v015 | minion_jobs_max_stalled_default_5 | ALTER minion_jobs.max_stalled DEFAULT 5; UPDATE non-terminal rows with max_stalled < 5 |
DDL, DATA | none | tx |
| v016 | cycle_locks_table | TABLE gbrain_cycle_locks (TTL lock); IDX idx_cycle_locks_ttl |
DDL, IDX | none | tx |
| v020 | sources_table_additive | TABLE sources; INSERT seed default source from config sync.repo_path / last_commit |
DDL, DATA | none | tx |
| v021 | pages_source_id_composite_unique | ALTER pages ADD source_id (FK sources, ON DELETE CASCADE); IDX idx_pages_source_id; DROP pages_slug_key; UNIQUE pages_source_slug_key(source_id, slug) |
DDL, IDX | sqlFor PG and PGL (separate bodies) |
tx |
| v022 | links_resolution_type | ALTER links ADD resolution_type; CHECK links_resolution_type_check (DO-guarded) |
DDL | none | tx |
| v023 | files_source_id_page_id_ledger | files: drop files_page_slug_fkey, ADD source_id, page_id, IDX; pages: swap to pages_source_slug_key; TABLE file_migration_ledger + IDX idx_file_migration_ledger_status; INSERT ledger rows |
DDL, DATA, IDX | handler returns immediately on PGL | handler (wrapped in engine.transaction) |
| v024 | rls_backfill_missing_tables | RLS ENABLE on access_tokens, mcp_request_log, minion_inbox, minion_attachments, subagent_messages, subagent_tool_executions, subagent_rate_leases, gbrain_cycle_locks, budget_ledger, budget_reservations; fails if role lacks BYPASSRLS |
RLS | sqlFor.pglite = '' (no-op) |
tx |
| v025 | pages_page_kind | ALTER pages ADD page_kind TEXT DEFAULT 'markdown'; CHECK pages_page_kind_check; PG: NOT VALID then VALIDATE |
DDL | PG two-step; PGL inline CHECK | tx |
| v026 | content_chunks_code_metadata | ALTER content_chunks ADD language, symbol_name, symbol_type, start_line, end_line; IDX idx_chunks_symbol_name, idx_chunks_language (partial) |
DDL, IDX | none | tx |
| v027 | cathedral_ii_foundation | ALTER content_chunks ADD parent_symbol_path, doc_comment, symbol_name_qualified, search_vector TSVECTOR; IDX idx_chunks_search_vector (GIN), idx_chunks_symbol_qualified; TABLE code_edges_chunk (+3 IDX), code_edges_symbol (+2 IDX); FN update_chunk_search_vector; TRIGGER chunk_search_vector_trigger |
DDL, IDX, FN | none | tx |
| v028 | cathedral_ii_chunk_fts_backfill | UPDATE content_chunks.search_vector (weight A doc_comment and symbol_name_qualified, B chunk_text) WHERE NULL; prelude v028 batches it |
DATA | none | tx |
| v029 | cathedral_ii_code_edges_rls | ENABLE RLS on code_edges_chunk, code_edges_symbol (BYPASSRLS guard) |
RLS | sqlFor.postgres (RLS); PGL branch without RLS |
tx |
| v030 | dream_verdicts_table | TABLE dream_verdicts (PK file_path, content_hash); ENABLE RLS (BYPASSRLS guard) |
DDL, RLS | PG-guarded RLS | tx |
| v031 | eval_capture_tables | TABLE eval_candidates (+IDX created_at), eval_capture_failures (+IDX ts); RLS on both (PG) |
DDL, IDX, RLS | sqlFor PG (BYPASSRLS guard, RLS) and PGL |
tx |
| v032 | oauth_infrastructure | TABLE oauth_clients, oauth_tokens (+2 IDX), oauth_codes; IDX idx_mcp_log_time_agent; ENABLE RLS on the three oauth tables if BYPASSRLS, else WARNING |
DDL, IDX, RLS | PG-only RLS DO block | tx |
| v033 | admin_dashboard_columns_v0_26_3 | ALTER oauth_clients ADD token_ttl, deleted_at; ALTER mcp_request_log ADD agent_name, params JSONB, error_message; backfill agent_name; UPDATE oauth_clients (2 statements); IDX idx_mcp_log_agent_time |
DDL, DATA, IDX | prelude v033 batches agent names |
tx |
| v034 | destructive_guard_columns | ALTER pages ADD deleted_at; ALTER sources ADD archived, archived_at, archive_expires_at; DATA from sources.config JSON; partial IDX pages_deleted_at_purge_idx |
DDL, DATA, IDX | kind: PG CREATE INDEX CONCURRENTLY; PGL plain |
handler (SQL run in-line), NOTX |
| v035 | auto_rls_event_trigger | FN auto_enable_rls(); EVENT TRIGGER auto_rls_on_create_table (ddl_command_end: CREATE TABLE, CREATE TABLE AS, SELECT INTO); ENABLE RLS on existing public tables; BYPASSRLS/superuser guard |
FN, RLS | sqlFor PG and PGL (PGL body without RLS) |
tx |
| v036 | subagent_provider_neutral_persistence_v0_27 | ALTER subagent_messages, subagent_tool_executions ADD schema_version (DEFAULT 1); subagent_messages ADD provider_id; IDX idx_subagent_messages_provider |
DDL, IDX | none | tx |
| v037 | takes_and_synthesis_evidence | TABLE takes (+6 IDX incl. HNSW idx_takes_embedding_hnsw); TABLE synthesis_evidence (+IDX), RLS on it |
DDL, IDX, RLS | sqlFor.pglite (no RLS); PG default SQL has RLS |
tx |
| v038 | access_tokens_permissions | ALTER access_tokens ADD permissions JSONB DEFAULT {"takes_holders":["world"]}; UPDATE existing rows |
DDL, DATA | none | tx |
| v039 | multimodal_dual_column_v0_27_1 | PG preflight: pgvector >= 0.5 (throws before DDL); PG: ALTER content_chunks ADD embedding_image vector(1024); partial HNSW IDX idx_chunks_embedding_image; CHECK pages_page_kind_check widened to include image. PGL: TABLE files + IDX idx_files_page, idx_files_page_id, idx_files_source_id, idx_files_hash |
DDL, IDX | kind branches (PG vs PGL) |
handler-only |
| v040 | pages_emotional_weight | ALTER pages ADD emotional_weight REAL NOT NULL DEFAULT 0 |
DDL | none | tx |
| v041 | pages_recency_columns | ALTER pages ADD effective_date, effective_date_source, import_filename, salience_touched_at; IDX pages_coalesce_date_idx |
DDL, IDX | kind: PG CONCURRENTLY; PGL plain |
handler, NOTX |
| v042 | eval_candidates_recency_capture | ALTER eval_candidates ADD as_of_ts, salience_param, recency_param, salience_resolved, recency_resolved, salience_source, recency_source |
DDL | none | tx |
| v043 | takes_resolved_quality_and_drift_decisions | UPDATE takes SET resolved_quality (backfill); DROP/ADD takes_resolution_consistency; IDX idx_takes_scorecard; TABLE drift_decisions (+2 IDX), RLS (PG) |
DDL, DATA, IDX, RLS | sqlFor.pglite (no RLS) |
tx |
| v044 | pages_emotional_weight_recomputed_at | ALTER pages ADD emotional_weight_recomputed_at (its index is built by the backfill registry, not here) |
DDL | none | tx |
| v045 | facts_hot_memory_v0_31 | TABLE facts (+IDX entity_active, session, since, unconsolidated); HNSW idx_facts_embedding_hnsw (dims from config, halfvec above cap); RLS on facts (PG) |
DDL, IDX, RLS | kind branch (PG pgvector check, RLS, HNSW) |
handler |
| v046 | mcp_request_log_params_jsonb_normalize | UPDATE mcp_request_log SET params = unwrapped JSON where stored as a JSON string |
DATA | none | tx |
| v047 | facts_notability_alter | ALTER facts ADD notability TEXT NOT NULL DEFAULT 'medium'; CHECK facts_notability_check (high/medium/low) |
DDL | none | tx |
| v048 | takes_weight_round_to_grid | UPDATE takes SET weight rounded to the 0.05 grid where off by more than 0.001 |
DATA | none | NOTX |
| v049 | eval_takes_quality_runs | TABLE eval_takes_quality_runs + IDX eval_takes_quality_runs_trend_idx |
DDL, IDX | none | tx |
| v050 | ingest_log_source_id | ALTER ingest_log ADD source_id; IDX idx_ingest_log_source_type_created |
DDL, IDX | none | tx |
| v051 | facts_fence_columns | ALTER facts ADD row_num, source_markdown_slug; UNIQUE IDX idx_facts_fence_key |
DDL, IDX | none | tx |
| v052 | eval_contradictions_cache | TABLE eval_contradictions_cache + IDX eval_contradictions_cache_expires_idx |
DDL, IDX | none | tx |
| v053 | eval_contradictions_runs | TABLE eval_contradictions_runs + IDX eval_contradictions_runs_ran_at_idx |
DDL, IDX | none | tx |
| v054 | cjk_wave_pages_chunker_version_and_source_path | ALTER pages ADD chunker_version, source_path; IDX pages_chunker_version_idx, pages_source_path_idx |
DDL, IDX | none | tx |
| v055 | query_cache_search_lite | TABLE query_cache + IDX idx_query_cache_source_created; HNSW idx_query_cache_embedding_hnsw (dims-gated) |
DDL, IDX | kind branch: PG pgvector version check and HNSW |
handler |
| v056 | query_cache_knobs_hash | ALTER query_cache ADD knobs_hash; IDX idx_query_cache_source_knobs_created |
DDL, IDX | none | tx |
| v057 | search_telemetry_rollup | TABLE search_telemetry + IDX idx_search_telemetry_date |
DDL, IDX | none | tx |
| v058 | edges_backfilled_at_v0_33_2 | ALTER content_chunks ADD edges_backfilled_at; IDX idx_code_edges_symbol_resolver, idx_content_chunks_symbol_lookup, idx_content_chunks_edges_backfill |
DDL, IDX | none | tx |
| v059 | code_traversal_cache_v0_34 | TABLE code_traversal_cache; UNIQUE IDX code_traversal_cache_key_idx; IDX code_traversal_cache_source_idx |
DDL, IDX | none | tx |
| v060 | oauth_clients_source_id_fk | ALTER oauth_clients ADD source_id; UPDATE NULL -> 'default'; DO-block FK oauth_clients_source_id_fkey (ON DELETE SET NULL); IDX idx_oauth_clients_source_id |
DDL, DATA, IDX | none | tx |
| v061 | oauth_clients_federated_read_column | ALTER oauth_clients ADD federated_read TEXT[] NOT NULL DEFAULT '{}' |
DDL | none | tx |
| v062 | oauth_clients_federated_read_backfill | UPDATE oauth_clients SET federated_read = ARRAY[source_id] (or '{}' when source_id NULL) where empty |
DATA | none | tx |
| v063 | oauth_clients_federated_read_validate | DO-block raises if any row has source_id missing from federated_read (no DDL) | DATA (check) | none | tx |
| v064 | oauth_clients_source_id_fk_restrict | DO-block: drop oauth_clients_source_id_fkey if present, re-add FK ON DELETE RESTRICT |
DDL | none | tx |
| v065 | oauth_clients_federated_read_gin_index | IDX idx_oauth_clients_federated_read (GIN) |
IDX | none | tx |
| v066 | embed_stale_partial_index | partial IDX idx_chunks_embedding_null on content_chunks |
IDX | kind: PG CONCURRENTLY with invalid pre-drop; PGL plain |
handler |
| v067 | facts_typed_claim_columns | ALTER facts ADD claim_metric and typed-claim columns; IDX facts_typed_claim_idx |
DDL, IDX | none | tx |
| v068 | calibration_profiles_v0_36 | TABLE calibration_profiles + IDX holder_recent, bias_tags (GIN), published |
DDL, IDX | none | tx |
| v069 | take_proposals_v0_36 | TABLE take_proposals; UNIQUE IDX take_proposals_idempotency_idx; IDX pending, run_id |
DDL, IDX | none | tx |
| v070 | take_grade_cache_v0_36 | TABLE take_grade_cache + IDX applied, wave |
DDL, IDX | none | tx |
| v071 | take_nudge_log_v0_36 | TABLE take_nudge_log + IDX take_cooldown, proposal_cooldown, wave |
DDL, IDX | none | tx |
| v072 | takes_resolved_at_trend_idx_v0_36 | IDX takes_resolved_at_idx |
IDX | kind: PG CONCURRENTLY with invalid pre-drop; PGL plain |
handler, NOTX |
| v073 | think_ab_results_v0_36 | TABLE think_ab_results + IDX recent |
DDL, IDX | none | tx |
| v074 | eval_candidates_embedding_column | ALTER eval_candidates ADD embedding_column |
DDL | sqlFor.pglite (PG uses default SQL, same statement) |
tx |
| v075 | op_checkpoints_table | TABLE op_checkpoints + IDX op_checkpoints_updated_at_idx |
DDL, IDX | none | tx |
| v076 | minion_jobs_doctor_run_id_index | GIN IDX minion_jobs_doctor_run_id_idx (data jsonb_path_ops, partial on doctor_run_id) |
IDX | sqlFor.postgres GIN; sqlFor.pglite = '' (no-op) |
tx |
| v077 | mcp_spend_log | TABLE mcp_spend_log + IDX token_time, client_time |
DDL, IDX | sqlFor.pglite (PG default SQL) |
tx |
| v078 | embedding_multimodal_column | ALTER content_chunks ADD embedding_multimodal |
DDL | sqlFor.pglite |
tx |
| v079 | pages_last_retrieved_at | ALTER pages ADD last_retrieved_at; IDX pages_last_retrieved_at_idx (plain, not CONCURRENTLY) |
DDL, IDX | none | tx |
| v080 | takes_unresolvable_quality_v0_37_2_0 | DROP/ADD CHECKs takes_resolved_quality_values and takes_resolution_consistency (allow unresolvable) |
DDL | none | tx |
| v081 | pages_provenance_columns | ALTER pages ADD ingested_via, ingested_at, source_uri, source_kind |
DDL | none | tx |
| v082 | subagent_tool_executions_stable_id | ALTER subagent_tool_executions ADD ordinal, gbrain_tool_use_id; DROP/ADD constraint subagent_tool_executions_stable_id |
DDL | sqlFor.pglite |
tx |
| v083 | mcp_spend_reservations | TABLE mcp_spend_reservations + IDX client_time, pending_expires |
DDL, IDX | none | tx |
| v084 | oauth_clients_budget_usd_per_day | ALTER oauth_clients ADD budget_usd_per_day NUMERIC(10,2) |
DDL | none | tx |
| v085 | oauth_clients_agent_binding | ALTER oauth_clients ADD bound_tools, bound_source_id, bound_brain_id, bound_slug_prefixes, bound_max_concurrent; FK fk_oauth_clients_bound_source (ON DELETE SET NULL, exception-guarded) |
DDL | sqlFor.pglite |
tx |
| v086 | page_links_view_alias | VIEW page_links = SELECT id, from_page_id, to_page_id FROM links |
FN (view) | none (PGL bootstrap also creates the view) | tx |
| v087 | takes_kind_drop_check | DROP CONSTRAINT takes_kind_check |
DDL | none | tx |
| v088 | eval_candidates_schema_pack_per_source | ALTER eval_candidates ADD schema_pack_per_source |
DDL | none | tx |
| v089 | facts_event_type_column | ALTER facts ADD event_type |
DDL | none | tx |
| v090 | contextual_retrieval_columns | ALTER pages ADD contextual_retrieval_mode, corpus_generation; sources ADD contextual_retrieval_mode, trust_frontmatter_overrides; query_cache ADD page_generations |
DDL | none | tx |
| v091 | pages_generation_trigger_and_bookmark | ALTER pages ADD generation BIGINT DEFAULT 1; query_cache ADD max_generation_at_store; FN bump_page_generation_fn + row TRIGGER bump_page_generation_trg (BEFORE INSERT/UPDATE); IDX pages_generation_idx |
DDL, FN, IDX | kind: PG CONCURRENTLY; PGL plain |
handler-only (no verify hook) |
| v092 | sources_github_repo_index | IDX sources_github_repo_idx |
IDX | none | tx |
| v093 | minions_v0_41_audit_and_budget | TABLE minion_lease_pressure_log, minion_budget_log, minion_self_fix_log (+2 IDX each); ALTER minion_jobs ADD budget_* columns |
DDL, IDX | none | tx |
| v094 | take_domain_assignments | TABLE take_domain_assignments (FK takes ON DELETE CASCADE, confidence CHECK 0..1) + IDX domain; RLS enable with BYPASSRLS guard |
DDL, IDX, RLS | sqlFor.pglite (differs; RLS only in default SQL) |
tx |
| v095 | links_link_source_check_includes_mentions | DROP/ADD links_link_source_check (adds mentions) |
DDL | sqlFor PG and PGL |
tx |
| v096 | facts_extract_conversation_session_index | IDX idx_facts_extract_conversation_session on facts(source_id, source_session) (partial) |
IDX | kind: PG CONCURRENTLY; PGL plain |
handler, NOTX |
| v097 | pages_dedup_partial_index | partial IDX pages_dedup_idx |
IDX | sqlFor.pglite plain CREATE; PG CONCURRENTLY in handler |
handler, NOTX |
| v098 | gbrain_cycle_locks_last_refreshed_at | ALTER gbrain_cycle_locks ADD last_refreshed_at; UPDATE backfill |
DDL, DATA | none | tx |
| v099 | conversation_parser_llm_cache_table | TABLE conversation_parser_llm_cache + IDX created |
DDL, IDX | sqlFor.pglite |
tx |
| v101 | links_link_kind_column | ALTER links ADD link_kind TEXT CHECK (plain, typed_ner) |
DDL | sqlFor.pglite (same text) |
tx |
| v102 | timeline_entries_source_in_dedup | DROP + UNIQUE IDX idx_timeline_dedup(page_id, date, summary, source) |
IDX | sqlFor.pglite (same text) |
tx |
| v103 | migration_impact_log_and_priority_recent_idx | TABLE migration_impact_log + 2 IDX; IDX content_chunks_stale_idx |
DDL, IDX | kind: PG CONCURRENTLY; PGL plain |
handler, NOTX |
| v104 | pages_atom_source_hash_idx | IDX pages_atom_source_hash_idx |
IDX | kind: PG CONCURRENTLY; PGL plain |
handler, NOTX |
| v105 | slug_aliases | TABLE slug_aliases + IDX slug_aliases_canonical_idx (plain, no CONCURRENTLY) |
DDL, IDX | none (PGL bootstrap also creates it) | handler-only |
| v106 | extract_rollup_7d_table | TABLE extract_rollup_7d + IDX day |
DDL, IDX | none | tx |
| v107 | page_generation_clock_and_statement_trigger | TABLE page_generation_clock (singleton id=1); seed value = MAX(pages.generation); FN bump_page_generation_clock_fn; statement-level TRIGGER bump_page_generation_clock_trg AFTER INSERT/UPDATE/DELETE on pages (DROP TRIGGER IF EXISTS first, :60) |
DDL, DATA, FN | none | tx |
| v108 | pages_embedding_signature | ALTER pages ADD embedding_signature |
DDL | none | tx |
| v109 | sources_newest_content_at | ALTER sources ADD newest_content_at |
DDL | none | tx |
| v110 | page_aliases | TABLE page_aliases (UNIQUE source_id, alias_norm, slug) + IDX lookup, slug |
DDL, IDX | none (PGL bootstrap also creates it) | tx |
| v111 | search_telemetry_rank1_columns | ALTER search_telemetry ADD sum_rank, count_rank, rank-1 columns |
DDL | none | tx |
| v112 | pages_links_extracted_at | ALTER pages ADD links_extracted_at; IDX pages_links_extracted_at_idx |
DDL, IDX | kind: PG CONCURRENTLY; PGL plain |
handler, NOTX |
| v113 | links_link_source_widen_for_wikilink_basename | DROP/ADD links_link_source_check (adds wikilink-resolved) |
DDL | sqlFor.pglite (same text) |
tx |
| v114 | links_link_source_check_kebab_regex | CHECK links_link_source_check replaced with kebab-case regex and <= 64 chars; PG: NOT VALID then VALIDATE |
DDL | sqlFor PG two-phase vs PGL one-shot |
NOTX |
| v115 | op_checkpoint_paths_append_table | TABLE op_checkpoint_paths |
DDL | none (comment mentions NOTX, flag not set) | tx |
| v116 | code_edges_source_backfill_and_callee_index | UPDATE code_edges_symbol and code_edges_chunk SET source_id from pages; IDX idx_code_edges_symbol_from_symbol, idx_code_edges_chunk_from_symbol; prelude v116 batches |
DATA, IDX | none | tx |
| v117 | context_volunteer_events_table | TABLE context_volunteer_events + IDX src_time, src_slug |
DDL, IDX | none | tx |
| v118 | page_generation_clock_sequence_swap | CREATE SEQUENCE page_generation_clock_seq; FN bump_page_generation_clock_fn uses nextval(); TRIGGER swapped; DELETE FROM query_cache |
DDL, FN, DATA | none | tx |
| v119 | op_checkpoints_completed_keys_array_check | UPDATE op_checkpoints.completed_keys to array; CHECK op_checkpoints_completed_keys_array |
DATA, DDL | none | tx |
| v120 | schema_lint_hardening_search_path_security_invoker | VIEW page_links SET (security_invoker = on); ALTER FUNCTION SET search_path for bump_page_generation_fn, bump_page_generation_clock_fn, update_chunk_search_vector, update_page_search_vector, notify_minion_job_change, auto_enable_rls (existing only) |
FN, VIEW | sqlFor PG and PGL (different bodies) |
tx |
| v121 | timeline_entries_event_page_id | ALTER timeline_entries ADD event_page_id + FK; IDX idx_timeline_event_page; UNIQUE IDX idx_timeline_event_dedup |
DDL, IDX | none | tx |
| v122 | facts_ontology_dimension | ALTER facts ADD dimension, value, value_hash, dim_status; IDX idx_facts_dimension; UNIQUE IDX idx_facts_ontology_dedup |
DDL, IDX | none | tx |
| v123 | configurable_fts_language | handler: CREATE OR REPLACE update_page_search_vector and update_chunk_search_vector with the configured language (getFtsLanguage); DATA re-derive pages (UPDATE pages SET id = id) and content_chunks.search_vector |
FN, DATA | language setting, not engine | handler-only |
| v124 | page_search_vector_drop_compiled_truth | handler: CREATE OR REPLACE update_page_search_vector without compiled_truth |
FN | none | handler-only |
| v125 | take_proposals_per_claim_idempotency | DROP and re-CREATE UNIQUE IDX take_proposals_idempotency_idx including md5(claim_text) |
IDX | none | tx |
| v126 | session_context_state | TABLE session_context_state + IDX updated |
DDL, IDX | none | tx |
| v127 | oauth_client_surface_and_minion_queue_index | ALTER oauth_clients ADD surface, surface_set_by; IDX idx_minion_jobs_queue_status_updated |
DDL, IDX | none | tx |
| v128 | minion_jobs_timeout_backfill_and_duplicate_cycle_cleanup | UPDATE minion_jobs SET timeout_ms by job name; UPDATE duplicate cycle jobs to status cancelled |
DATA | none | tx |
| v129 | dream_verdicts_triage_v1_columns | ALTER dream_verdicts ADD score, content_type, segments, entities, model, triage_version |
DDL | none | tx |
| v130 | minion_jobs_lock_duration_ms | ALTER minion_jobs ADD lock_duration_ms; CHECK chk_lock_duration_positive |
DDL | none | tx |
| v131 | drop_job_wide_subagent_tool_use_id_unique | DROP CONSTRAINT uniq_subagent_tools_use_id |
DDL | none | tx |
| v132 | session_context_state_checkpoint_manifest | ALTER session_context_state ADD checkpoint_manifest |
DDL | none | tx |
| v133 | content_chunks_embedded_text_hash | ALTER content_chunks ADD embedded_text_hash |
DDL | none | tx |
| v134 | restore_chunks_embedding_null_partial_indexes | re-creates embedding-null partial indexes on content_chunks (names built in handler) |
IDX | kind: PG CONCURRENTLY; PGL plain |
handler, NOTX |
| v135 | facts_event_time_index | IDX idx_facts_since_event_time on facts |
IDX | none | tx |
| v136 | minion_private_queue_owner_metadata | ALTER minion_jobs ADD private_queue_owner_job_id (self FK, ON DELETE SET NULL), private_queue_owner_token, private_queue_lease_until; IDX idx_minion_jobs_private_queue_recovery, idx_minion_jobs_private_queue_owner |
DDL, IDX | none | tx |
| v137 | entity_identities | TABLE entity_identities + IDX entity_identities_entity_idx + UNIQUE entity_identities_canonical_uniq |
DDL, IDX | none | tx |
| v138 | timeline_dedup_md5_summary | DROP idx_timeline_dedup; UNIQUE IDX idx_timeline_dedup(page_id, date, md5(summary), source) (plain build) |
IDX | none | tx |
| v139 | timeline_legacy_source_split_repair | handler repairLegacyTimelineSourceRows (timeline-dedup-repair.ts): rewrites legacy rows to (source, summary) shape, deletes duplicates |
DATA | none | handler-only |
| v140 | chat_usage_log | TABLE chat_usage_log + IDX created, model |
DDL, IDX | none | tx |
| v141 | extract_rollup_expected_limit | ALTER extract_rollup_7d ADD expected_limit_count INT DEFAULT 0 |
DDL | none | tx |
| v142 | takes_embedding_dimension_matches_config | handler: if takes.embedding dims differ from config embedding_dimensions: DROP HNSW idx_takes_embedding_hnsw; UPDATE takes SET embedding = NULL, embedded_at = NULL; DROP COLUMN embedding; ADD embedding VECTOR(dims); recreate HNSW. Destructive to takes embeddings |
DDL, DATA, IDX | kind (pgvector) |
handler-only |
| v143 | dream_verdicts_ttl | ALTER dream_verdicts ADD expires_at; SET DEFAULT now() + 30 days; UPDATE backfill; IDX dream_verdicts_expires_idx |
DDL, DATA, IDX | none | tx |
| v144 | open_loops | handler: re-runs the takes embedding resize from v142 (dims-checked first) and the dream_verdicts expiry backfill; TABLE open_loops (+3 IDX); TABLE loop_suppressions |
DDL, DATA, IDX | kind (pgvector) |
handler-only |
| v145 | facts_kind_idea_alter | re-creates CHECK facts_kind_check (adds idea) |
DDL | none | tx |
| v146 | extract_atoms_transcript_state_table | TABLE extract_atoms_transcript_state; DROP IDX extract_atoms_transcript_state_live_idx; IDX ..._tombstoned_idx |
DDL, IDX | none | tx |
| v147 | oauth_client_capability_grants | SQL: GRANT_COLUMNS_SQL (ALTER oauth_clients ADD allowed_operations, delegated_slug_prefixes, delegated_namespace, grant_profile, grant_revision, grant_repair_reasons) + GRANT_AUDIT_SCHEMA_SQL (TABLE oauth_grant_audit + IDX) + GRANT_SPEND_COLUMNS_SQL (ALTER mcp_spend_reservations ADD estimate_known, usage_unknown_reason); handler repairLegacyClientGrants |
DDL, HANDLER | none | tx + handler |
| v148 | durable_fact_withdrawals | SQL: FACT_WITHDRAWAL_SCHEMA_SQL (TABLE fact_withdrawals; FN gbrain_fact_fingerprint*, gbrain_preserve_fact_withdrawal; TRIGGER facts_preserve_withdrawal) + FACT_WITHDRAWAL_BACKFILL_SQL (DATA) |
DDL, FN, DATA | none | tx |
| v149 | minion_submission_authority | ALTER minion_jobs ADD submission_authority JSONB, claim_generation BIGINT DEFAULT 0; FN enforce_minion_queue_protocol; TRIGGER minion_queue_protocol (BEFORE INSERT/UPDATE) |
DDL, FN | none | tx |
| v150 | canonical_page_revisions_and_guards | SQL: PAGE_STATE_SCHEMA_SQL (ALTER sources ADD incarnation + UNIQUE sources_incarnation_key; ALTER pages ADD knowledge_revision, text_projection_revision; ALTER page_versions ADD knowledge_revision, timeline, title, type, tags; TABLE page_write_guards; FN gbrain_advance_page_revision, gbrain_advance_tag_revision; TRIGGERs pages_knowledge_revision, tags_knowledge_revision) |
DDL, FN | none | tx |
| v151 | durable_concurrent_persistence | SQL: all of PERSISTENCE_SCHEMA_STATEMENTS (TABLE persistence_brain and the persistence_* tables; plain CREATE INDEX for every index in the array, including the 9 that the PG blob omits) |
DDL, IDX | none | tx |
| v152 | unique_lock_acquisition_tokens | ALTER gbrain_cycle_locks ADD acquisition_token UUID NOT NULL DEFAULT gen_random_uuid() |
DDL | none | tx |
| v153 | verified_text_projection_activation | SQL: PAGE_PROJECTION_SCHEMA_SQL (TABLE page_projection_jobs; FN gbrain_queue_page_projection; TRIGGERs trg_pages_search_vector, pages_projection_queue) + PAGE_PROJECTION_ACTIVATION_SQL (DML); handler counts served vs text projection rows and prints |
DDL, FN, DATA, HANDLER | none | tx + handler |
| v154 | preserve_sanitized_page_search_vectors | SQL: PAGE_PROJECTION_SCHEMA_SQL (restates the v153 objects without activation) |
FN, DDL (idempotent restate) | none | tx |
| v155 | recoverable_postcommit_persistence_effects | ALTER persistence_effects ADD source_id, source_incarnation, worktree_id, claim_expires_at, next_attempt_at, attempts, error_code, outcome, recovery, recovery_bytes; IDX persistence_effects_pending, persistence_effects_recovery |
DDL, IDX | none | tx |
| v156 | managed_alias_and_source_checkpoint_guards | FN gbrain_require_managed_writer; TRIGGER managed_writer_guard on each guarded table (page child tables) |
FN | none | tx |
| v157 | recoverable_source_topology | TABLE persistence_topology_changes (ALTER intent_bytes); IDX persistence_topology_recovering |
DDL, IDX | none | tx |
| v158 | canonical_version_deletion_state | ALTER page_versions ADD is_deleted |
DDL | none | tx |
| v159 | index_retained_publication_recovery | plain IDX persistence_requests_recovery(worktree_id, sequence) WHERE recovery IS NOT NULL |
IDX | none | tx |
| v160 | current_text_projection_planner_statistics | SQL PROJECTION_STATISTICS_SQL: CREATE STATISTICS pages_text_projection_current_stats ON ((text_projection_revision = knowledge_revision)) FROM pages; ANALYZE pages and content_chunks (columns in SEARCH_STATISTICS_COLUMNS); PG sqlFor prefixes SET LOCAL statement_timeout = '30s', lock_timeout = '2s'; handler verifyProjectionStatistics |
STATS, DATA (ANALYZE), HANDLER | sqlFor.postgres (timeouts); PGL uses the plain SQL |
tx + handler |
Notes on the table:
- Migrations v011, v028, v033 and v116 also have a batched prelude (
src/core/migration-preludes.ts:126-166) that runs before their SQL. sqlForappears on about 30 migrations in range; "sqlFor.pglite(same text)" means the two bodies are identical in the first statements I compared.- Migrations with both
sqlandhandlerrun the SQL first inside a transaction, then the handler outside it (src/core/migrate.ts:590). v147, v153 and v160 use this split.
(c) DB objects created at runtime outside src/schema.sql and the migrations
Brain database (PG and PGLite):
| Object(s) | Where created | When | Notes |
|---|---|---|---|
Forward-reference ALTERs and CREATE TABLE IF NOT EXISTS sources |
src/core/engine-sql/bootstrap.ts:344-756 |
Every initSchema before the blob replay |
Adds columns the blob's CREATE INDEX/FK needs on old brains. |
timeline_entries unique index idx_timeline_dedup (rebuild) |
src/core/timeline-dedup-repair.ts:116-160 (DROP INDEX, CREATE UNIQUE INDEX IF NOT EXISTS ... md5(summary)) |
Every runMigrations call (#2038) |
Also runs DELETE of duplicate rows (:116-146). |
UNIQUE pages_source_slug_key(source_id, slug) (restore) |
src/core/pages-upsert-arbiter.ts:115 |
Every runMigrations call (#550) |
ADD-only; refuses on duplicates. |
links_link_source_check CHECK (rewrite) |
src/core/link-source-check-repair.ts:103 |
Every call when current >= 114 (#4613) |
Rewrites the constraint to the kebab-case allowlist. |
ALTER FUNCTION ... SET search_path ... pg_temp |
src/core/function-search-path.ts:100 (called from src/core/migrate.ts:459) |
Every call (#7632) | Owner-role functions are only named, not altered. |
Missing-column ALTER ADD COLUMN IF NOT EXISTS |
src/core/schema-verify.ts:282 |
End of PG initSchema (postgres-engine.ts:656) |
Self-heal from blob parse. |
| Invalid HNSW index sweep | src/core/vector-index.ts dropZombieIndexes, called at postgres-engine.ts:663-668 |
After PG initSchema |
DROP of indisvalid = false indexes. |
query_cache, code_traversal_cache (re-create) |
src/core/schema-missing-tables.ts:45-60 |
gbrain repair missing-tables |
Reruns v055/v056/v090/v091/v059 SQL. |
idx_facts_fts (+ _next, _old, rename swap) |
src/core/facts/fts-index.ts:38-49 via buildIndexOnline (src/core/schema-migrations/helpers.ts:119) |
Called by migration v237 and gbrain reindex-search-vector (src/commands/reindex-search-vector.ts:53) |
Built and swapped without blocking writers. |
persistence_requests_* indexes (rebuild) |
src/core/repair/request-indexes.ts:14, 32 |
gbrain repair request-indexes |
Via buildIndexOnline. |
pages_live_origin_key (UNIQUE partial) |
src/core/persistence/origin-index.ts:12-15, :51 |
Migration v257 and gbrain repair origin-twins --apply (src/core/repair/origin-twins.ts:54, 223) |
Pending while twins exist. |
open_loops.counterparty_source_id + open_loops_counterparty_source_idx |
src/core/loops/loops-schema.ts:8-14 |
Shared by migration v236 and the store | Header says the store and a migration share it. |
idx_pages_emotional_weight_pending (CONCURRENTLY) |
src/core/backfill-registry.ts:132 |
Backfill runner, not a migration | Migration v044's comment says this index is built by the backfill. |
embedding column DROP + ADD, idx_chunks_embedding*, idx_facts_embedding_hnsw, idx_takes_embedding_hnsw, idx_query_cache_embedding_hnsw |
src/core/embedding-migration.ts:137-139, 169, 252-268, 312-313; src/core/embedding-dim-check.ts:252, 620-635 |
gbrain migrate embeddings and dim-change flow |
Full DROP INDEX + ALTER ... USING + CREATE INDEX. |
| Deferred HNSW builds | src/core/embedding-ann-build.ts:103 (CONCURRENTLY on PG), :119 (plain) |
gbrain migrate embeddings after drain |
Index definitions recorded in migration marker. |
idx_chunks_embedding (temp index then swap) |
src/core/vector-index.ts:24, 253-290 |
Vector index rebuild path | CREATE INDEX CONCURRENTLY on a temp name. |
planner_stats_deltas, planner_stats_state, FN gbrain_count_modifications, triggers planner_stats_ins/upd/del on six hot tables |
src/core/planner-stats-schema.ts:17-44 |
PGLite only, applied by migration v196 (src/core/schema-migrations/v196-f4-planner-stats.ts:2), outside this part's range |
Not in either blob. |
auto_enable_rls FN + event trigger auto_rls_on_create_table (graduation target) |
src/core/persistence/graduation-target.ts:296-298 |
Persistence graduation to a Postgres target | Same DDL as migration v035; graduation runs it on the target DB. |
Indexes copied from source definitions (CREATE INDEX IF NOT EXISTS) |
src/core/persistence/graduation-copy.ts:292, 360 |
Graduation copy | Index defs come from the source DB. |
| Persistence/guard schema modules used by migrations, not by the blob | src/core/facts/insert-guard-schema.ts, src/core/persistence/attribution-schema.ts, src/core/persistence/graduation-schema.ts, src/core/eligibility/generation-schema.ts, src/core/shared-skills/persistence-schema.ts |
Applied only by migrations (for example v193, v201, v230, v264, v278, v287) | Not in build-schema.ts FRAGMENTS, so they are migration-only text. |
PG extension vector |
src/commands/init.ts:1490; src/commands/db-repair.ts:581, 802; src/core/pg-access-classify.ts:204-205 |
gbrain init and db-repair --yes |
Manual remediation text for others. |
PG extension pg_prewarm |
src/core/vector-prewarm.ts:120-125 |
gbrain embeddings prewarm remediation |
Printed or run as psql -c argv. |
scratch_probe table |
src/core/pglite-engine.ts:709 |
PGLite install/probe in a scratch store directory | Not the brain DB. |
Non-brain stores (separate SQLite files, not in the brain schema):
| Object | Where | Notes |
|---|---|---|
paths table in manifest.sqlite |
src/core/export-stage.ts:106 |
Export staging manifest, journal off. |
embed_cache table (WITHOUT ROWID) |
src/eval/shared/embed-cache.ts:107-115 |
Eval embedding cache file, WAL. |
Checked and not runtime DDL:
src/core/sync.tshas no DDL; its only match is a comment (:124).src/commands/migrations/v0_16_0.tsruns no DDL; it verifies the subagent tables exist (its header lines 9-13).src/core/schema-migrations/files are excluded from this search; they are part (b).
Parity risks noted
- Version key semantics: the runner reads and writes one
configrowversionas an integer and refuses to run an older binary over a newer schema (assertSchemaNotAhead, #7776,pglite-engine.ts:1176). An embedded Rust store needs the same monotonic version or a migration path for brains that carryversionin config. - Postgres-specific DDL in migrations: RLS plus BYPASSRLS checks (v024, v029, v030, v031, v032, v035, v094), event triggers (v035), CREATE STATISTICS and ANALYZE (v160),
ALTER VIEW ... security_invoker(v120),ALTER FUNCTION ... search_path(v120, v277 per the search-path self-heal), regex CHECK with~(v114), PG 15UNIQUE NULLS NOT DISTINCT(v011), pgvector HNSW and halfvec (v039, v045, v055, v142, v144). PGLite drops several of these viasqlFor; the Rust engine has to choose one shape. - Non-transactional builds:
transaction: falseon PG (NOTX list: v034, v041, v048, v072, v096, v097, v103, v104, v112, v114, v134) allow concurrent writes during the build; an embedded store that runs everything in one transaction would take a different lock profile. PG CONCURRENTLY fallbacks use an invalid-index recovery path (dropInvalidConcurrentIndex,helpers.ts:53-73) that has no Rust analogue. - SQL and handler are not atomic (
src/core/migrate.ts:590): the handler runs after the SQL transaction commits. A crash between them leaves new schema with the version still behind. Any Rust port of v123, v139, v142, v144, v147, v153, v160 needs idempotent re-runs. - Destructive data changes inside migrations: v142 and v144 DROP COLUMN
takes.embeddingand NULL every take's embedding when dims differ from config (src/core/schema-migrations/v142-takes-embedding-dimension-matches-config.ts,v144-open-loops.ts); v008 and v009 DELETE duplicates; v119 and v128 rewrite rows; v107 and v118 rewrite the clock trigger. The embedded Rust store has no equivalent history and must decide what a fresh store does with these. - Engine-divergent blob shape: the PGLite blob lacks
dream_verdicts,code_edges_*,file_migration_ledger, RLS,pgcrypto, and the chunk search_vector trigger; the PG blob lackspage_linksview,slug_aliases,page_aliases. Parity tests have to compare resulting catalogs, not blobs. - Runtime self-heal on every start (migrate.ts:401-449):
idx_timeline_deduprebuild,pages_source_slug_keyUNIQUE restore (needed byputPageON CONFLICT upserts),links_link_source_checkrewrite,search_pathreset. A Rust store needs equivalent invariants enforced by construction, or these checks have no target. - Lock: Postgres uses advisory lock key 42 (
postgres-engine/init-schema-lock.ts:29) and PGLite is single-process in-process serialization (schema-migration-lock.ts:12-32). A Rust store needs a process-wide exclusive lock file that covers both init andapply-migrations. sqlFor''semantics: an explicit empty string means "skip on this engine" (v024 pglite, v076 pglite), and??only falls back when the key is absent (migrate.ts:548). A Rust port must reproduce per-engine no-ops, not just the PG SQL.- Verify and drift path:
verifySchema(PG only,schema-verify.ts:243) self-heals columns that PgBouncer dropped. A Rust store without a pooler does not need this, but any column the blob declares must be created exactly once. - Dims policy: embedding dims come from settings, then stored identity, then gateway defaults (
pglite-engine.ts:1152-1171,postgres-engine.ts:586-607), and HNSW presence depends on dims (hnswMaxDimsForType). Migrations v045, v055, v142, v144 bake this in; the Rust store has to reproduce the dims-gated index choice. - Missing versions 17-19 and 100 are gaps in the registry, not failures; a port that assumes contiguous numbering would break the
version > currentloop. - Migration-only modules (
facts/insert-guard-schema.ts,persistence/attribution-schema.ts,persistence/graduation-schema.ts,eligibility/generation-schema.ts,shared-skills/persistence-schema.ts) are not in the blob. A Rust schema has to include their objects in its fresh-install shape (or they are missing on fresh installs that skip migrations). - Out-of-range but relevant:
planner_stats_*(PGLite-only accounting, v196) and PGLitepage_linksview alias (v086) are objects a Rust store must provide because engine queries reference them (scripts/build-schema.ts:462-490).
Migrations v161-v318
Scope: the 158 migration files src/core/schema-migrations/v161-*.ts through v318-*.ts in garrytan/gbrain (commit 8bbfd0b5e8). Paths below are relative to src/core/schema-migrations/ unless prefixed. Where a migration imports its SQL from a schema module (most of them), the objects listed come from that module; the module path is given in brackets.
How the runner applies a migration (shared by all 158)
src/core/migrate.ts:216-240applyOneMigration:useTransaction = m.transaction !== false. SQL runs insideengine.transaction()when the flag is on, and always on PGLite. On Postgres with a transaction, it first setsSET LOCAL statement_timeoutandSET LOCAL lock_timeout(resolveSchemaLockTimeoutMs()).src/core/migrate.ts:589-591: thehandlerruns after the SQL and outside the SQL transaction, whatever thetransactionflag says. A handler-only migration therefore never shares a transaction with its own DDL.migrate.tsstampsconfig.versiononly after SQL, handler and verify all succeed (around line 511). A crash mid-handler re-runs the whole migration.verifyhooks exist on only three migrations in scope: v164 (shared_skill_heads/shared_skill_members/persistence_requests.target_kindexist), v191 (both alias FKs validated), v234 (persistence_host_bindings.git_durabilityexists).- Every migration in scope declares
idempotent: true. helpers.ts:119buildIndexOnline: on Postgres it skips a valid index, reportsbuildingif another session is building it, drops an INVALID leftover, then runsCREATE INDEX CONCURRENTLYon a reserved connection and checksindisvalid. On PGLite it creates the index inline (if absent) and runs ANALYZE. Index migrations that call it are therefore not transactional on Postgres.helpers.tsdropInvalidConcurrentIndex(Postgres only) is called directly by v165, v170, v172, v173 and v187, and runs insidebuildIndexOnlinefor the other IDX-ON migrations.- Row-level security is enabled in most table migrations only behind the guard
IF EXISTS (SELECT 1 FROM pg_roles ... rolbypassrls OR rolsuper). The fresh-install and PGLite paths do not depend on it.
Migration table
Kind codes: DDL = table/column/constraint/index DDL; FN = function or trigger (re)definition; IDX-ON = index built online through buildIndexOnline; DATA = a data change in the migration SQL or handler; RLS = row-level security enable; TX-F = transaction: false.
| Ver | Name (file) | Objects touched | Kind | Engine branch / transaction |
|---|---|---|---|---|
| 161 | index_pending_text_projections (v161-index-pending-text-projections.ts) |
index idx_pages_projection_pending on pages(source_id, page_kind, slug), partial on live rows whose text projection revision differs from knowledge revision |
DDL index (plain) | sqlFor.postgres adds SET LOCAL statement_timeout 30s, lock_timeout 2s; tx on |
| 162 | source_ingestion_receipts_with_policy (v162) |
[company-brain/receipt-schema.ts] table source_ingestion_receipts, indexes _active, _retention, _source, column policy_fingerprint |
DDL | tx on |
| 163 | derived_atom_page_scan_state (v163) |
[sql in file] table extract_atoms_page_state (FK sources.incarnation, pages.id), indexes _tombstoned_idx, _page_idx; FN gbrain_clear_atom_page_state; TRIGGER pages_clear_atom_scan_state (AFTER UPDATE ON pages); RLS |
DDL + FN + DATA (seed rows from live pages with a valid content_hash) | tx on; RLS only when role bypasses RLS |
| 164 | shared_brain_skills_and_membership (v164) |
[shared-skills/schema-all.ts] SHARED_SKILLS_SCHEMA_SQL: shared_skill_heads, shared_skill_members, persistence_requests.target_kind |
DDL | tx on; verify hook |
| 165 | index_database_only_pending_writes (v165) |
index persistence_requests_database_pending [persistence/schema.ts] |
IDX-ON | handler; TX-F; Postgres drops invalid then CONCURRENTLY |
| 166 | fact_embedding_identity (v166) |
facts.embedding_model, facts.embedded_text_hash |
DDL | tx on |
| 167 | facts_superseded_by_set_null (v167) |
FK facts_superseded_by_fkey re-created REFERENCES facts(id) ON DELETE SET NULL NOT VALID, then VALIDATE |
DDL constraint | tx on |
| 168 | dream_synthesis_completions (v168) |
table dream_synthesis_completions |
DDL | tx on |
| 169 | fact_withdrawal_subject (v169) |
[facts/withdrawal-schema.ts] fact_withdrawals.subject column; pkey change. Existing rows keep the source-wide '*' subject (file comment) |
DDL | tx on |
| 170 | index_parked_persistence_effects (v170) |
index persistence_effects_parked [persistence/effect-schema.ts] |
IDX-ON | handler; TX-F |
| 171 | content_chunks_embedding_input_hash (v171) |
content_chunks.embedding_input_hash |
DDL | tx on |
| 172 | pages_safe_chunk_pending_index (v172) |
index pages_safe_chunk_pending_idx ON pages(source_id) WHERE chunker_version < 4 |
IDX-ON | handler; TX-F |
| 173 | minion_jobs_dead_subagent_finished_index (v173) |
index idx_minion_jobs_dead_subagent_finished ON minion_jobs(finished_at) WHERE name='subagent' AND status='dead' |
IDX-ON | handler; TX-F |
| 174 | fact_withdrawal_normalized_fingerprint (v174) |
[facts/withdrawal-schema.ts] FN gbrain_fact_normalize, gbrain_fact_fingerprint, gbrain_fact_fingerprint_v1; index idx_facts_withdrawal_fingerprint; (file comment: legacy exact rows keep matching) |
FN + DDL index | tx on |
| 175 | tags_tag_source (v175) |
tags.tag_source |
DDL | tx on |
| 176 | connector_checkpoint_stable_identity (v176) |
op_checkpoints rows with op managed-connector / managed-connector-retry re-keyed to stable connector identity [persistence/connector-checkpoint-migration.ts:127 migrateConnectorCheckpoints] |
DATA (handler, no DDL) | handler; outside tx |
| 177 | pages_database_only_reason (v177) |
pages.database_only_reason |
DDL | tx on |
| 178 | persistence_writer_version_stamps (v178) |
persistence_requests: admitter_version, admitter_host_id, consumer_version, consumer_host_id, published_at; persistence_brain.writer_version_cutoff then UPDATE ... SET writer_version_cutoff = now() WHERE NULL |
DDL + DATA (stamp) | tx on |
| 179 | persistence_request_sync_run_indexes (v179) |
indexes from PERSISTENCE_SYNC_RUN_INDEXES [persistence/schema.ts] on persistence_requests |
IDX-ON | handler; TX-F |
| 180 | pages_links_attendance_blocked (v180) |
pages.links_attendance_blocked_revision (uuid), pages.links_attendance_blocked_at |
DDL | tx on |
| 181 | connector_dispatch_attempts (v181) |
handler seedConnectorDispatchAttempts [persistence/connector-state.ts:165]: for each source with local_path, not archived, kind google or github, syncEnabled not false, records a sync attempt in op_checkpoints |
DATA (handler) | handler |
| 182 | page_versions_source_path (v182) |
page_versions.source_path |
DDL | tx on |
| 183 | persistence_mode_epoch (v183) |
persistence_brain.mode_epoch bigint NOT NULL DEFAULT 1; persistence_worktrees_state_check dropped and re-added |
DDL constraint | tx on |
| 184 | decision_receipts (v184) |
[ai/decide/schema.ts] decision_receipts, decide_spend, decide_state; indexes decision_receipts_decision_idx, _model_slot_idx, _slot_created_idx, decide_spend_created_idx; RLS |
DDL | tx on |
| 185 | decide_calibrations (v185) |
[ai/decide/schema.ts] decide_calibrations, index decide_calibrations_lookup_idx; RLS |
DDL | tx on |
| 186 | decide_proposals (v186) |
[ai/decide/schema.ts] decide_proposals, decide_sweep_deferred; indexes decide_proposals_pending_new_idx, _pending_old_idx, _status_created_idx; RLS |
DDL | tx on |
| 187 | fact_relink_attempts (v187) |
[facts/relink-schema.ts] table fact_relink_attempts + fact_relink_attempts_outcome_idx; index idx_facts_unlinked_active ON facts(source_id, id) WHERE entity_slug IS NULL |
DDL + IDX-ON | handler; TX-F; Postgres CONCURRENTLY |
| 188 | facts_ontology_stint_dedup (v188) |
UNIQUE index idx_facts_ontology_stint_dedup (source_id, entity_slug, dimension, value_hash, source_markdown_slug, valid_from) WHERE dimension NOT NULL; DROP idx_facts_ontology_dedup |
DDL index (plain, in tx) | tx on |
| 189 | pages_credential_projection_pending (v189) |
UPDATE pages SET text_projection_revision = NULL for markdown/code pages whose body or timeline contains a PEM private-key block; INSERT page_projection_jobs (reason credential_projection) ON CONFLICT upsert |
DATA | tx on |
| 190 | sources_upstream_observation (v190) |
sources.upstream_checked_at, upstream_commit, upstream_behind |
DDL | tx on |
| 191 | alias_source_cascade (v191) |
DELETE orphan rows in page_aliases and slug_aliases whose source_id has no sources row; FK page_aliases_source_fk, slug_aliases_source_fk ON DELETE CASCADE NOT VALID; handler VALIDATE both (handler labels its runMigration call as 190, not 191) |
DATA + DDL constraint | sql tx on; handler; verify hook |
| 192 | take_embedding_identity (v192) |
takes.embedding_model, takes.embedded_text_hash |
DDL | tx on |
| 193 | f1_write_attribution (v193) |
[persistence/attribution-schema.ts] FN gbrain_stamp_write_attribution, gbrain_stamp_revision_attribution, gbrain_stamp_version_attribution; TRIGGERs gbrain_write_attribution, pages_revision_attribution, page_versions_write_attribution; [persistence/writer-guard-schema.ts] FN gbrain_require_managed_writer, TRIGGER managed_writer_guard |
FN + TRIGGER | tx on |
| 194 | f0_worktree_refreshes (v194) |
[persistence/worktree-refresh-schema.ts] table persistence_worktree_refreshes; UNIQUE index persistence_worktree_refreshes_active; index _completed |
DDL | tx on |
| 195 | f3_access_token_grants (v195) |
[grants/access-token-schema.ts] access_tokens: allowed_operations, federated_read, grant_revision, source_grant, source_id, takes_holders |
DDL | tx on |
| 196 | f4_planner_stats (v196) |
[planner-stats-schema.ts] PGLite only: tables planner_stats_state, planner_stats_deltas; FN gbrain_count_modifications |
DDL (PGLite only) | sqlFor.pglite only; Postgres is a no-op (sql: '') |
| 197 | managed_guard_null_source_fallback (v197) |
FN gbrain_require_managed_writer redefined (null source fallback) [persistence/writer-guard-schema.ts] |
FN redefine | tx on |
| 198 | publication_failure_detail (v198) |
persistence_requests.error_detail jsonb; FN gbrain_require_managed_writer redefined |
DDL + FN | tx on |
| 199 | chronicle_page_state (v199) |
tables chronicle_page_state (FK sources, pages), chronicle_judge_reservations; indexes chronicle_page_state_work_idx, _page_idx, chronicle_judge_reservations_at_idx; RLS on both |
DDL | tx on |
| 200 | sync_hold_page_index (v200) |
index op_checkpoints_sync_hold_page_idx on expression completed_keys->0->>'page_id', WHERE op='sync-hold' |
DDL index (plain) | sqlFor.postgres adds SET LOCAL timeouts; tx on |
| 201 | persistence_graduation (v201) |
[persistence/graduation-schema.ts] table persistence_graduation; FN gbrain_graduation_fence |
DDL + FN | tx on |
| 202 | legacy_token_grant_conversion (v202) |
[grants/legacy-token.ts:334 migrateLegacyTokens, dryRun false] converts legacy token grants into the unified grant columns; malformed permissions stay denied and are reported by doctor |
DATA (handler) | handler; outside tx |
| 203 | oauth_client_grant_axes (v203) |
[grants/oauth-client-axes-schema.ts] oauth_clients.source_grant, takes_holders |
DDL | tx on |
| 204 | links_temporal_state (v204) |
[link-temporal-schema.ts] links.assertion_tense + CHECK; tables link_relationships, link_edge_proposals, link_transitions; UNIQUE link_transitions_identity_idx; indexes link_relationships_source_idx, _to_idx, link_transitions_origin_idx, _relationship_idx, link_edge_proposals_status_idx; RLS |
DDL | tx on |
| 205 | minion_spend_authorization (v205) |
minion_jobs.spend_authorization jsonb, spend_claim_token bigint; index idx_minion_jobs_spend_group; mcp_spend_reservations.budget_key, client_id DROP NOT NULL, CONSTRAINT mcp_spend_reservations_one_budget_owner, index idx_mcp_spend_reservations_budget_key; mcp_spend_log.budget_key, CONSTRAINT mcp_spend_log_one_budget_owner, index idx_mcp_spend_log_budget_key; FN enforce_minion_queue_protocol (SET search_path) |
DDL + FN | tx on |
| 206 | entity_mention_index (v206) |
page_aliases: origin (default 'frontmatter'), case_sensitive, alias_text; UNIQUE page_aliases_origin_uniq (source_id, alias_norm, slug, origin); DROP page_aliases_uniq; tables page_mention_state, mention_gazetteer_entries, mention_index_status + index page_mention_state_source_idx; RLS x3 |
DDL | tx on |
| 207 | retrieval_feedback (v207) |
tables retrieval_events, retrieval_event_pages, retrieval_event_links, retrieval_feedback (CHECK element_kind page/link), retrieval_weights; indexes retrieval_events_time_idx, retrieval_feedback_client_time_idx |
DDL | tx on |
| 208 | delta_per_arm_cursor (v208) |
session_context_state: facts_cursor_at, facts_cursor_id bigint, degraded_wakes int NOT NULL DEFAULT 0; UPDATE session_context_state SET facts_cursor_at = last_wake_at, facts_cursor_id = 0 WHERE facts_cursor_at IS NULL |
DDL + DATA | tx on |
| 209 | page_facts_reconcile (v209) |
table page_facts_reconcile (PK page_id, FK pages) |
DDL + RLS | tx on |
| 210 | clamp_oauth_token_ttl (v210) |
handler clampOAuthTokenTtls (v210 file:27): one transaction, SELECT ... FROM oauth_clients FOR UPDATE, sets token_ttl and grant_revision; UPDATE oauth_tokens SET expires_at for access tokens beyond the max TTL; INSERT oauth_grant_audit rows |
DATA (handler, own transaction) | handler |
| 211 | function_search_path (v211) |
[facts/withdrawal-schema.ts] FACT_FINGERPRINT_FUNCTION_STATEMENTS (FN re-create); then loop over every public plpgsql function with NULL proconfig and not extension-owned: ALTER FUNCTION ... SET search_path = pg_catalog, public |
FN hardening | tx on |
| 212 | decide_review_proposals (v212) |
[ai/decide/schema.ts] decide_review_queue, decide_review_proposals; index decide_review_proposals_status_idx; RLS |
DDL | tx on |
| 213 | core_edit_notices (v213) |
[core-memory-schema.ts] core_edit_notices + core_edit_notices_pending_idx; RLS |
DDL | tx on |
| 214 | wanted_links (v214) |
table wanted_links (ref_kind CHECK slug/name; UNIQUE wanted_links_reference_unique); indexes wanted_links_target_idx, _source_idx, idx_pages_slug_basename; RLS |
DDL | tx on; RLS conditional |
| 215 | facts_attributed_to (v215) |
facts.attributed_to TEXT CHECK in (user, assistant, other); [persistence/attribution-schema.ts] attribution triggers |
DDL + FN | tx on |
| 216 | chronicle_campaign_stamps (v216) |
chronicle_page_state: campaign_id, attempt_cap_usd, max_attempts (CHECK > 0), pricing_policy, campaign_max_usd, cost_attempts int[] DEFAULT '{}' |
DDL | tx on |
| 217 | persistence_request_claim_phase (v217) |
persistence_requests.claim_phase jsonb |
DDL | tx on |
| 218 | purge_legacy_worktree_manifest_files (v218) |
`UPDATE persistence_worktrees SET manifest = (manifest - 'files') | {file_count}where afilesobject exists;UPDATE persistence_topology_changes SET recoverythe same way underrecovery.manifest` |
|
| 219 | pages_reconcile_name_indexes (v219) |
indexes pages_source_path_name_idx (source_id, basename of source_path, WHERE source_path NOT NULL), pages_file_uri_name_idx (source_id, basename of source_uri, WHERE file: uri) |
IDX-ON | handler; TX-F |
| 220 | persistence_request_preparation_attempts (v220) |
persistence_requests.preparation_attempts int NOT NULL DEFAULT 0 CHECK >= 0 |
DDL | tx on |
| 221 | chunk_embedding_pending_since (v221) |
content_chunks.embedding_pending_since; DO block: UPDATE content_chunks SET embedding_pending_since = created_at WHERE <active embedding column> IS NULL AND embedding_pending_since IS NULL (active column from config.search_embedding_column); config marker embedding_pending_since_backfilled_at |
DDL + DATA | tx on |
| 222 | persistence_consumers (v222) |
[persistence/schema.ts] table persistence_consumers; index PERSISTENCE_COMMITTED_WATERMARK_INDEX via IDX-ON |
DDL + IDX-ON | handler |
| 223 | persistence_serve_loop_indexes (v223) |
IDX-ON PERSISTENCE_SYNC_WATERMARK_INDEX, PERSISTENCE_COMPACTABLE_INDEX; DROP persistence_requests_committed_watermark (CONCURRENTLY on Postgres) |
IDX-ON + index drop | handler |
| 224 | page_retrievals (v224) |
table page_retrievals (page_id PK); FN gbrain_forget_page_retrievals and statement-level TRIGGER pages_forget_retrievals (AFTER DELETE ON pages, REFERENCING OLD TABLE); INSERT ... SELECT from pages.last_retrieved_at with GREATEST upsert; handler DROP pages_last_retrieved_at_idx (CONCURRENTLY on Postgres) |
DDL + FN + DATA + index drop | sql tx on; handler |
| 225 | drop_duplicate_embedding_null_index (v225) |
handler: DROP idx_chunks_embedding_null only when its definition duplicates another index (compares pg_get_indexdef with the name stripped) |
index drop | handler; kind branch (CONCURRENTLY on Postgres) |
| 226 | trust_tiers (v226) |
[trust/schema.ts] TRUST_SCHEMA_SQL: table trust_proposals + indexes trust_proposals_pending_unique, _pending_idx, _target_idx; page_versions.trust_tier; FN gbrain_stamp_trust_tier, gbrain_snapshot_version_trust; FN gbrain_require_managed_writer; config trust.backfill_completed_at inserted only if pages, facts, takes and timeline_entries are all empty |
DDL + FN + config seed | tx on |
| 227 | write_gate (v227) |
[write-gate-schema.ts] tables write_gate_holds, write_gate_receipts; UNIQUE indexes write_gate_holds_dedupe_idx, write_gate_receipts_target_idx; indexes _status_idx, _seen_idx; RLS; config write_gate.scan_baseline seeded with max id of pages, facts, takes, timeline_entries |
DDL + config seed | tx on |
| 228 | memory_purge (v228) |
[facts/purge-schema.ts] tables fact_purges, page_purges, take_purges, derivation_inputs, needs_rederive; index idx_derivation_inputs_input; FN gbrain_refuse_purged_fact/page/take; TRIGGERs facts_refuse_purged, pages_refuse_purged, takes_refuse_purged; RLS |
DDL + FN + TRIGGER | tx on |
| 229 | trust_allow_rules (v229) |
[trust/allow-rules.ts] trust_allow_rules, UNIQUE trust_allow_rules_active_unique, index trust_allow_rules_active_idx |
DDL | tx on |
| 230 | trust_generation (v230) |
[eligibility/generation-schema.ts] BUMP_FUNCTION: FN gbrain_bump_trust_generation (line 30 TRUST_GENERATION_FUNCTION) and its per-table triggers (line 88); RLS on one table |
DDL + FN + TRIGGER | tx on |
| 231 | fact_purge_lookup_indexes (v231) |
IDX-ON fact_purges_subject_idx, fact_purges_all_subjects_idx [facts/purge-schema.ts] |
IDX-ON | handler |
| 232 | fts_cjk_boundary (v232) |
FN re-create FTS_INPUT_FUNCTION_SQL, page and chunk search-vector trigger functions for getFtsLanguage(); handler backfillPageVectors and backfillChunkVectors for rows matching the CJK-boundary regex (checkpointed); unsetConfig of both checkpoints |
FN + DATA (search-vector rebuild) | handler; batches outside tx |
| 233 | oauth_client_registered_via (v233) |
oauth_clients.registered_via TEXT; CONSTRAINT oauth_clients_registered_via_check CHECK ('dcr') NOT VALID then VALIDATE |
DDL constraint | tx on |
| 234 | persistence_git_durability (v234) |
[persistence/schema.ts] persistence_host_bindings.git_durability |
DDL | sqlFor.postgres adds SET LOCAL timeouts; verify hook; tx on |
| 235 | pinned_questions (v235) |
[questions/schema.ts] tables pinned_questions, question_evidence; indexes pinned_questions_state_idx, question_evidence_question_idx; RLS; handler migrateAutoThinkToPins [questions/auto-think-migration.ts:76] |
DDL + DATA (handler) | sql tx on; handler |
| 236 | open_loops_counterparty_source (v236) |
[loops/loops-schema.ts] open_loops.counterparty_source_id; IDX-ON open_loops_counterparty_source_idx |
DDL + IDX-ON | sql tx on; handler |
| 237 | facts_question_recall_indexes (v237) |
IDX-ON idx_facts_unembedded (WHERE expired_at NULL AND (embedding NULL OR hash mismatch)), idx_facts_embedding_model; ensureFactsFtsIndex [facts/fts-index.ts] builds idx_facts_fts |
IDX-ON | handler |
| 238 | persistence_client_request_id (v238) |
persistence_requests.client_request_id text |
DDL | tx on |
| 239 | extract_rollup_halt_reasons (v239) |
extract_rollup_7d columns for halt reasons |
DDL | tx on |
| 240 | pages_name_lookup_indexes (v240) |
IDX-ON idx_pages_source_title_lower (source_id, lower(title)); IDX-ON GIN idx_pages_slug_trgm (slug gin_trgm_ops, needs pg_trgm) |
IDX-ON | handler; TX-F |
| 241 | persistence_request_slug_intent_index (v241) |
IDX-ON PERSISTENCE_REQUEST_SLUG_INTENT_INDEX |
IDX-ON | handler; TX-F |
| 242 | extract_atoms_partial_input (v242) |
extract_atoms_page_state and extract_atoms_transcript_state columns for partial input |
DDL | tx on |
| 243 | persistence_consumer_process_start (v243) |
persistence_consumers.boot_id, process_start |
DDL | tx on |
| 244 | chunk_projected_spans (v244) |
content_chunks.projected_byte_start, projected_byte_end (bigint) |
DDL | tx on |
| 245 | page_search_vector_bounded_timeline (v245) |
FN page search-vector trigger re-create with PAGE_TIMELINE_FTS_MAX_CHARS; handler backfillPageVectors for pages with timeline over half the cap (checkpointed) |
FN + DATA (search-vector rebuild) | handler |
| 246 | page_embedding_budget (v246) |
table page_embedding_budget (PK page_id); index page_embedding_budget_state_idx WHERE state <> 'open' |
DDL | tx on |
| 247 | page_extraction_state (v247) |
table page_extraction_state (page_id, pipeline); index page_extraction_state_truncated_idx WHERE input_truncated NOT NULL |
DDL | tx on |
| 248 | segmented_page_storage (v248) |
[page-body/schema.ts] PAGE_BODY_SCHEMA_SQL (page_body_manifest, page_body_stagings, page_segment_texts, page_body_staging_parts, etc.) and PAGE_GENERATION_BUMP_FN_SQL; pages: body_storage (default 'inline'), body_bytes, truth_bytes, body_lines, body_generation (default 0), segment_count, segmenter_version, body_flags, truth_lead, timeline_lead; compiled_truth and timeline DROP NOT NULL; CONSTRAINT pages_body_storage_chk. content_chunks: occurrence_id, local_ordinal, raw_byte_start, raw_byte_end, body_generation, retired_generation. page_versions: compiled_truth DROP NOT NULL, body_storage, body_generation; CONSTRAINT page_versions_body_chk. Handler: VALIDATE both CHECKs, IDX-ON content_chunks_occurrence_idx, Postgres ANALYZE of pages(body_generation) and content_chunks |
DDL + constraints + IDX-ON + ANALYZE. No UPDATE or INSERT in the file (grep), so existing pages are not rewritten here | sqlFor.postgres and sqlFor.pglite (both COMMON_SQL plus a branch); TX-F; handler |
| 249 | belief_ledger (v249) |
[belief/schema.ts] tables belief_events, belief_page_lifetimes (UNIQUE belief_page_lifetimes_open), belief_redactions, belief_commit_marks, belief_retier_manifest + indexes (belief_events_*, belief_redactions_*, belief_page_lifetimes_slug); RLS; sources.purge_epoch; handler IDX-ON idx_facts_belief_stream |
DDL + IDX-ON | sql tx on; handler |
| 250 | persistence_request_id_index (v250) |
IDX-ON PERSISTENCE_REQUEST_ID_INDEX |
IDX-ON | handler; TX-F |
| 251 | take_horizons (v251) |
table take_horizons (take_id PK FK takes); index take_horizons_date_idx |
DDL | tx on |
| 252 | segmented_chunk_gc_index (v252) |
IDX-ON content_chunks_generation_idx (page_id, body_generation WHERE NOT NULL) |
IDX-ON | handler; TX-F |
| 253 | page_view_digests (v253) |
table page_view_digests (page_id FK) |
DDL | tx on |
| 254 | segment_reader_marks (v254) |
persistence_requests.admitter_segment_reader, consumer_segment_reader; persistence_consumers.segment_reader; ingest_log.writer_version, writer_host_id, writer_segment_reader; IDX-ON ingest_log_unmarked_idx |
DDL + IDX-ON | handler; TX-F |
| 255 | page_body_staging_texts (v255) |
[page-body/schema.ts] PAGE_BODY_STAGING_TEXTS_SQL (table page_body_staging_texts, index page_body_staging_texts_text); content_chunks.retired_generation |
DDL | tx on |
| 256 | chunk_retirement_index (v256) |
IDX-ON content_chunks_occurrence_generation_idx (page_id, occurrence_id, body_generation) |
IDX-ON | handler; TX-F |
| 257 | pages_live_origin_key (v257) |
handler buildOriginIndexIfClean [persistence/origin-index.ts]: builds the live-origin unique index on pages only when no source has duplicate-origin groups; otherwise defers with a notice and changes nothing |
IDX-ON (conditional) | handler; TX-F |
| 258 | pages_links_extract_failure (v258) |
pages.links_extract_failure jsonb |
DDL | tx on |
| 259 | ingest_log_marked_index (v259) |
IDX-ON ingest_log_marked_idx (created_at WHERE writer_segment_reader IS TRUE) |
IDX-ON | handler; TX-F |
| 260 | segmented_page_size_index (v260) |
IDX-ON pages_segmented_body_bytes_idx (body_bytes WHERE body_storage='segmented') |
IDX-ON | handler; TX-F |
| 261 | persistence_request_transient_retries (v261) |
persistence_requests.transient_interruptions int NOT NULL DEFAULT 0, next_attempt_at timestamptz |
DDL | tx on |
| 262 | commit_notifier (v262) |
tables sync_wanted_markers, sync_notifier_leases, sync_notifier_sources |
DDL | tx on |
| 263 | try_jsonb_function (v263) |
FN gbrain_try_jsonb(text) RETURNS jsonb (IMMUTABLE STRICT, SET search_path) |
FN | tx on |
| 264 | set_based_fact_insert_guards (v264) |
[persistence/attribution-schema.ts] STAMP_ROW_FUNCTION; [facts/insert-guard-schema.ts] FN gbrain_guard_inserted_facts; TRIGGERs facts_guard_inserted, facts_refuse_purged, facts_preserve_withdrawal_insert |
FN + TRIGGER | tx on |
| 265 | derived_generations (v265) |
tables derived_generations, derived_generation_items, derived_generation_valve, derived_generation_attempts; UNIQUE partial derived_generations_one_live, derived_generations_one_hold; indexes _operation, _state_created, derived_generation_items_key, derived_generation_attempts_key |
DDL | tx on |
| 266 | derived_generation_activations (v266) |
derived_generations.locator, activation_id; derived_generation_items.pending bool NOT NULL DEFAULT false + partial index derived_generation_items_pending; table derived_generation_activations + index derived_generation_activations_key |
DDL | tx on |
| 267 | pages_title_search_index (v267) |
IDX-ON GIN idx_pages_title_search on ts_filter(search_vector, '{a}') |
IDX-ON | handler; TX-F |
| 268 | effect_claim_owner (v268) |
persistence_effects.claim_owner jsonb |
DDL | tx on |
| 269 | links_supersedes_index (v269) |
IDX-ON idx_links_supersedes_to (to_page_id WHERE link_type='supersedes') |
IDX-ON | handler; TX-F |
| 270 | pages_source_created_index (v270) |
IDX-ON pages_source_created_idx (source_id, created_at WHERE deleted_at NULL) |
IDX-ON | handler; TX-F |
| 271 | facts_superseded_by_index (v271) |
IDX-ON idx_facts_superseded_by |
IDX-ON | handler; TX-F |
| 272 | git_canonical_declared (v272) |
[persistence/git-canonical-schema.ts] table git_canonical_brain + RLS; FN gbrain_count_git_canonical_sources; TRIGGERs git_canonical_sources_count_insert/update/delete on sources; FN gbrain_require_managed_writer |
DDL + FN + TRIGGER | tx on |
| 273 | segment_derivations (v273) |
[page-body/schema.ts] PAGE_SEGMENT_DERIVATIONS_SQL (page_segment_derivations, page_segment_derivation_purges, page_segment_derivation_purge_pages, index page_segment_derivations_unsettled); facts.source_window jsonb; CHECK facts_source_window_chk NOT VALID, then handler VALIDATE |
DDL + constraint | sql via runMigrationStatement; TX-F |
| 274 | pages_withheld_source_path (v274) |
pages.withheld_source_path text |
DDL | tx on |
| 275 | rls_backfill_unguarded_tables (v275) |
handler protectTables (v275 file:111): ENABLE ROW LEVEL SECURITY on each listed gbrain table where it is off, the current role owns it, and the table comment is not exempt; one short transaction per table with SET LOCAL timeouts |
RLS | handler; TX-F; Postgres only (no-op on PGLite) |
| 276 | persistence_foreground_committed_index (v276) |
IDX-ON PERSISTENCE_FOREGROUND_COMMITTED_INDEX |
IDX-ON | handler; TX-F |
| 277 | function_search_path_pg_temp (v277) |
handler hardenFunctionSearchPaths [function-search-path.ts:100]: adds pg_temp to function search_path for gbrain functions |
FN hardening | handler; TX-F |
| 278 | writer_fence_fail_closed (v278) |
FN re-create: gbrain_require_managed_writer, gbrain_count_git_canonical_sources, gbrain_guard_request_protocol, gbrain_guard_effect_protocol, gbrain_guard_protocol_activation (shared-skills and persistence guard bodies) |
FN redefine | tx on |
| 279 | outcome_markers_skip_purge_guard (v279) |
FN gbrain_guard_inserted_facts re-create (FACT_INSERT_GUARD_FUNCTION_SQL) |
FN redefine | tx on |
| 280 | principal_private_pages (v280) |
access_tokens.private_pages and oauth_clients.private_pages TEXT NOT NULL DEFAULT 'none' |
DDL | tx on |
| 281 | derived_state_maintainers_fail_closed (v281) |
FN re-create (7 functions): gbrain_forget_page_retrievals, gbrain_clear_atom_page_state, tag revision, snapshot version, stamp version, page projection queue, gbrain_bump_trust_generation (BUMP_FUNCTION); handler re-runs the page search-vector trigger function |
FN redefine | sql tx on; handler |
| 282 | member_read_access (v282) |
tables access_members, access_groups, access_group_members (+ index access_group_members_member_idx); pages.page_readers TEXT[]; access_tokens and oauth_clients: member_id, may_act_for TEXT[]; FN gbrain_access_norm_readers, gbrain_access_readers, gbrain_access_origin_restricted, gbrain_access_mark_restricted, gbrain_access_derive_page_readers; TRIGGERs pages_access_derive_readers, pages_access_mark_restricted, access_members_mark_restricted; config access.restrictions_present = '1' when any page has readable_by or any member exists; RLS. The file says nothing is rewritten on upgrade (comment at v282 lines 9-16) |
DDL + FN + TRIGGER + config seed | tx on |
| 283 | folder_access_rules (v283) |
pages.folder_rule_ids TEXT[]; tables access_policy_state, access_rules, access_policy_revisions (UNIQUE partial access_policy_revisions_operation_idx), access_pending_coverage, access_policy_migrations; FN gbrain_access_folder_rules, gbrain_access_state_unreadable, gbrain_access_pending_covers, gbrain_access_stamp_folder_rules; TRIGGER pages_access_stamp_folder_rules (BEFORE INSERT/UPDATE OF slug, source_id, folder_rule_ids); FN gbrain_access_origin_restricted re-create |
DDL + FN + TRIGGER | tx on |
| 284 | access_page_indexes (v284) |
IDX-ON GIN pages_page_readers_gin (WHERE page_readers NOT NULL), GIN pages_folder_rule_ids_gin |
IDX-ON | handler; TX-F |
| 285 | slug_rename_history (v285) |
table slug_rename_history (source_id FK) + index slug_rename_history_new_idx; FN gbrain_access_state_unreadable and gbrain_access_origin_restricted re-create |
DDL + FN | tx on |
| 286 | qm_actor_links (v286) |
table access_member_links + index access_member_links_member_idx, RLS; access_tokens and oauth_clients: attest_actor, attest_ops TEXT[]; oauth_tokens.grant_type |
DDL | tx on |
| 287 | multi_page_recovery_record (v287) |
[shared-skills/persistence-schema.ts] REQUEST_PROTOCOL_GUARD_V287_SQL (persistence request protocol guard FN redefinition) |
FN redefine | tx on |
| 288 | decide_proposals_claim_pair_index (v288) |
[ai/decide/schema.ts] UNIQUE index decide_proposals_claim_pair_uidx |
DDL index (plain) | tx on |
| 289 | page_change_reasons (v289) |
page_versions.change_reason, successor_pruned; pages.version_history_gap_at; table reason_purge_tombstones (FK sources), RLS |
DDL | tx on |
| 290 | wake_subscriptions (v290) |
table wake_subscriptions (principal_kind CHECK oauth_client/legacy_token/local_stdio); indexes wake_subscriptions_open_idx (partial), _principal_idx, UNIQUE partial wake_subscriptions_open_unique; RLS |
DDL | tx on |
| 291 | persistence_request_replay_indexes (v291) |
IDX-ON PERSISTENCE_REQUEST_REPLAY_ID_INDEX, PERSISTENCE_REQUEST_SLUG_SEQUENCE_INDEX |
IDX-ON | handler; TX-F |
| 292 | refresh_write_barrier (v292) |
persistence_worktree_refreshes.barrier_sequence bigint, released_at timestamptz; UPDATE ... SET released_at = COALESCE(completed_at, updated_at) WHERE state IN (completed, aborted) AND released_at IS NULL; persistence_requests.deferred_refresh_id uuid + FK persistence_requests_deferred_refresh_id_fkey; persistence_consumers.capabilities text[] DEFAULT '{}'; FN gbrain_release_refresh_barrier + TRIGGER gbrain_refresh_release (BEFORE UPDATE OF state); FN gbrain_guard_deferred_request + TRIGGER gbrain_request_deferred (BEFORE UPDATE OF state on persistence_requests); DROP/re-add persistence_requests_state_check; IDX-ON persistence_requests_open |
DDL + DATA + FN + TRIGGER + index | sqlFor.postgres and sqlFor.pglite; TX-F; handler |
| 293 | qm_attest_sources (v293) |
access_tokens.attest_sources, oauth_clients.attest_sources (TEXT[]) |
DDL | tx on |
| 294 | refresh_barrier_import_deferral (v294) |
persistence_requests.deferred_import_from text; FN gbrain_guard_deferred_request re-create |
DDL + FN | tx on |
| 295 | refresh_retry_hint_history_index (v295) |
index persistence_worktree_refreshes_completed (worktree_id, completed_at DESC WHERE state='completed') |
DDL index (plain) | tx on |
| 296 | ingest_applied_captures (v296) |
table ingest_applied_captures (idempotency_key PK) + index ingest_applied_captures_applied_at_idx; INSERT ... SELECT DISTINCT ON (idempotency_key) from completed ingest_capture jobs (non-tombstone), ON CONFLICT DO NOTHING; RLS |
DDL + DATA | tx on |
| 297 | chronicle_judge_window (v297) |
chronicle_page_state: judge_input_hash, judge_window_chars (CHECK >= 0), judge_window_hash, judge_input_failures int NOT NULL DEFAULT 0 (CHECK >= 0) |
DDL | tx on |
| 298 | attest_read (v298) |
access_tokens.attest_read, oauth_clients.attest_read (TEXT) |
DDL | tx on |
| 299 | access_member_sources (v299) |
table access_member_sources (member_id FK) + index access_member_sources_source_idx; RLS |
DDL | tx on |
| 300 | memory_proposals (v300) |
table memory_proposals (PK principal_kind, principal_id, request_id; kind CHECK timeline/link); indexes memory_proposals_request_idx, _pending_actor_idx, _pending_source_idx (partial awaiting_owner), _closed_idx; RLS |
DDL | tx on |
| 301 | member_email (v301) |
access_members.email TEXT; UNIQUE index access_members_email_key |
DDL | tx on |
| 302 | link_transitions_source (v302) |
link_transitions.link_source TEXT; IDX-ON link_transitions_identity_next_idx (UNIQUE ... NULLS NOT DISTINCT over origin_page_id, from/to, link_type, link_source, kind, occurred_on, producer); then one transaction: DROP link_transitions_identity_idx, RENAME next index to link_transitions_identity_idx; lock-timeout handling; throws if another session is building it |
DDL + IDX-ON + index rename | TX-F; handler; kind branches (Postgres timeouts) |
| 303 | access_page_rules (v303) |
CONSTRAINT access_rules_prefix_check and access_pending_coverage_prefix_check replaced with folder-or-page check; FN gbrain_access_folder_rules re-create (trailing '/' or exact match); FN gbrain_access_pending_covers re-create |
DDL constraint + FN | tx on |
| 304 | access_owner_origin (v304) |
FN gbrain_access_origin_closed_for (new), gbrain_access_origin_hidden_for (new) (plpgsql); pending-coverage and alias/rename resolution |
FN | tx on |
| 305 | access_lineage_designs (v305) |
access_rules.covers_derived BOOLEAN NOT NULL DEFAULT true; access_members.kind TEXT NOT NULL DEFAULT 'person' + CHECK in (person, audience); FN gbrain_access_lineage_rules (new); FN gbrain_access_origin_restricted re-create |
DDL + FN | tx on |
| 306 | member_page_withdrawal_scope (v306) |
[facts/withdrawal-schema.ts] FACT_WITHDRAWAL_PRESERVE_FUNCTION_SQL; [facts/insert-guard-schema.ts] FACT_INSERT_GUARD_FUNCTION_SQL (FN re-creates) |
FN redefine | tx on |
| 307 | page_label_guards (v307) |
table page_label_guards (page_id PK FK pages); RLS |
DDL | tx on |
| 308 | access_labelled_only (v308) |
access_members.labelled_only BOOLEAN NOT NULL DEFAULT false; FN gbrain_access_origin_labelled_for (new) |
DDL + FN | tx on |
| 309 | requester_scope (v309) |
access_tokens.reads_unscoped, oauth_clients.reads_unscoped (TIMESTAMPTZ); FN gbrain_access_refresh_serves (new, sets config access.serves_derived) and gbrain_access_refresh_serves_trigger; TRIGGERs access_members_refresh_serves (AFTER INSERT/DELETE/UPDATE OF disabled_at, tombstoned_at), config_serves_refresh and config_serves_refresh_delete on config; config access.serves_since inserted; UPDATE config SET value = derived |
DDL + FN + TRIGGER + config seed | tx on |
| 310 | mcp_request_log_rollup (v310) |
table mcp_request_log_rollup; RLS |
DDL | tx on |
| 311 | concept_inputs_private (v311) |
FN gbrain_concept_inputs_private(page_id) RETURNS boolean (new) |
FN | tx on |
| 312 | guest_notes (v312) |
[guests/schema.ts] GUEST_NOTES_SCHEMA_SQL: tables guest_marks, guest_promotions, guest_sources; indexes guest_marks_audience_saved_idx, guest_promotions_guest_page_idx; TRIGGERs guest_marks_immutable, guest_source_database_only; FN gbrain_guard_guest_records; RLS; [trust/schema.ts] GUEST_AWARE_STAMP_TIER_FUNCTION |
DDL + FN + TRIGGER | tx on |
| 313 | wake_events (v313) |
wake_subscriptions.target_ref TEXT; tables wake_crons, wake_cron_runs (+ index wake_cron_runs_name_idx), wake_terms_acceptances, wake_forgets (UNIQUE partial wake_forgets_pending_unique, index wake_forgets_due_idx); RLS x4 |
DDL | tx on |
| 314 | guest_notes_fail_closed (v314) |
FN gbrain_guard_guest_records and GUEST_FAIL_CLOSED_STAMP_TIER_FUNCTION re-create; DO block: UNIQUE index guest_promotions_link_key (guest_page_id, target_source_id, target_slug) if no duplicates, else RAISE WARNING and no index |
FN + conditional index | tx on |
| 315 | facts_context_head_index (v315) |
IDX-ON idx_facts_context_head (source_id, md5(split_part(context, ' — ', 1)) WHERE source_markdown_slug IS NULL) |
IDX-ON | handler |
| 316 | pages_source_type_index (v316) |
IDX-ON idx_pages_source_type (source_id, type WHERE deleted_at NULL) |
IDX-ON | handler |
| 317 | pages_projection_waiting_index (v317) |
IDX-ON idx_pages_projection_waiting (partial: text projection revision differs from knowledge revision and from ACTIVATION_SERVED_REVISION) |
IDX-ON | handler |
| 318 | guard_ledgers_fail_closed (v318) |
handler failClosedLedgerGuards [guard-ledger-checks.ts:32]: finds purge and withdrawal guard functions that read their ledgers as empty for a write role without ledger policy; writes a stderr instruction to ALTER FUNCTION ... OWNER TO the role; no data change |
FN ownership (handler only) | handler |
Patterns
Data backfills a Rust port must reproduce for upgrade-from-existing-brain
These change existing rows or seed state. A fresh Rust schema has none of them, so each needs an upgrade-path equivalent (or a one-shot upgrade step):
| Ver | What it changes on an existing brain |
|---|---|
| 163 | Seed extract_atoms_page_state from live pages with valid content_hash |
| 176 | Re-key managed-connector and managed-connector-retry op_checkpoints to stable identity (TS, connector-checkpoint-migration.ts:127) |
| 178 | Set persistence_brain.writer_version_cutoff = now() where null |
| 181 | Record a connector sync attempt for each active google/github source with a local_path (connector-state.ts:165) |
| 189 | Clear text_projection_revision and enqueue credential_projection jobs for pages containing PEM private-key text |
| 191 | Delete orphan page_aliases/slug_aliases rows; add and validate source FKs with ON DELETE CASCADE |
| 202 | Convert legacy token grants into unified grant columns (grants/legacy-token.ts:334) |
| 208 | Seed facts_cursor_at / facts_cursor_id from last_wake_at |
| 210 | Clamp oauth_clients.token_ttl and shorten over-long access-token expires_at; write oauth_grant_audit rows (own transaction) |
| 218 | Rewrite persistence_worktrees.manifest and persistence_topology_changes.recovery.manifest: drop files, set file_count |
| 221 | Set content_chunks.embedding_pending_since = created_at where the active embedding is NULL; config marker |
| 224 | Seed page_retrievals from pages.last_retrieved_at (GREATEST upsert) |
| 226, 227, 282, 309 | Config seeds: trust.backfill_completed_at, write_gate.scan_baseline (max ids), access.restrictions_present, access.serves_derived / access.serves_since |
| 232, 245 | Rebuild page and chunk keyword search vectors with new FTS input functions (CJK boundary; bounded timeline), checkpointed |
| 235 | migrateAutoThinkToPins (questions/auto-think-migration.ts:76) |
| 292 | Backfill persistence_worktree_refreshes.released_at for completed or aborted rows |
| 296 | Seed ingest_applied_captures from completed ingest_capture jobs |
Not in migrations, but a related port risk: v282 says nothing is rewritten on upgrade. Existing pages get page_readers and folder_rule_ids only through the repair paths (core/repair/access.ts:20-35, core/access/policy.ts:200-213). A Rust upgrade must either run the same derivation or accept NULL until repair.
Pure DDL that a fresh Rust schema can collapse
Most of the 158 migrations are DDL only. A fresh Rust schema can take the final state directly:
- Plain add-column, add-table, and add-index migrations (most rows above, e.g. 166, 171, 175, 177, 180, 182, 190, 192, 220, 238, 243, 244, 246, 247, 251, 253, 258, 261, 262, 268, 274, 280, 289, 293, 298, 301, 307, 310, 313 and similar). Their output is simply part of the final table definition.
- Function and trigger redefinitions: keep only the last definition. Many functions are redefined several times across the range:
gbrain_access_origin_restricted: 282, 283, 285, 305.gbrain_access_folder_rules: 283, 303.gbrain_access_state_unreadable: 283, 285.gbrain_require_managed_writer: 193, 197, 198, 226, 272, 278.gbrain_guard_deferred_request: 292, 294.gbrain_guard_inserted_facts: 264, 279.gbrain_guard_guest_records: 312, 314.gbrain_fact_*andFACT_INSERT_GUARD/FACT_WITHDRAWAL_PRESERVE: 169/174, 211, 306.- The search-vector trigger function: 232, 245, 281.
- Constraints added as NOT VALID and then VALIDATE: 167, 191, 233, 273. Keep only the validated end state.
- Index build and drop sequences that exist only for online rebuild on Postgres: 165, 170, 172, 173, 179, 187, 219, 222, 223, 224, 225, 231, 240, 241, 250, 252, 254, 256, 257, 259, 260, 267, 269, 270, 271, 276, 284, 291, 302, 315, 316, 317. The final index set is all a fresh schema needs. Dropped indexes need no entry:
idx_facts_ontology_dedup(188),persistence_requests_committed_watermark(223),pages_last_retrieved_at_idx(224),idx_chunks_embedding_null(225), and the oldlink_transitions_identity_idx(302, replaced by the NULLS NOT DISTINCT version). - RLS enables: collapse to enabled-on-every-gbrain-table (v275 is the backfill). The conditional
rolbypassrlsguard exists only for Postgres roles, and RLS does not exist in an embedded engine. - PGLite-only (196) and Postgres-only (275, 248 ANALYZE) branches: drop them.
Keep the handler-only migrations' effect (non-transactional, after SQL) in mind: a migration whose SQL is empty and whose effect is in the handler will run that effect again on a retry.
Parity risks noted
- Access-control state is enforced by SQL functions and triggers (v282 through v318 define
gbrain_access_*,gbrain_guard_*,gbrain_refuse_purged_*,*_refresh_serves). Embedded storage without RLS must reproduce these checks in Rust or the access model silently changes. - Row-level security is enabled conditionally on
rolbypassrls/rolsuper, and v275 and v318 depend on the database role's ownership of functions. Neither concept exists in an embedded engine. Their intent (deny-by-default on write roles) must be ported explicitly. v282relies onconfig.access.restrictions_present(sticky flag set by trigger) to keep pre-upgrade read paths. Losing the sticky semantics changes read behavior for brains without restrictions.v282andv283do not backfill existing rows. Pages carry NULLpage_readersandfolder_rule_idsuntilcore/repair/access.tsorcore/access/policy.tsruns. A Rust upgrade that skips these repairs leaves restricted pages readable.v210runs its own transaction withFOR UPDATEon oauth_clients and shortens token lifetimes. Behavior is observable to clients.- Handler-only work (v165, v170, v176, v181, v202, v210, v223, v231, v232, v235, v245, v257, v275, v277, v302 index rename step, v318) runs outside the SQL transaction. The runner calls the handler after the SQL (
migrate.ts:589-591). A crash partway leaves partial effects, and the version is not stamped until the whole migration succeeds; Rust needs idempotent re-runs. v302builds a NULLS NOT DISTINCT unique index and renames it while the old one is dropped;v257builds a unique live-origin index only when duplicates are absent. Rust needs the same uniqueness semantics or it will accept duplicate origin rows.v188andv302change unique-index definitions in place;v188adds a unique index that fails on existing duplicates (no pre-check). Upgrade paths can fail where a fresh schema would not.v232andv245rebuild search vectors from a PL/pgSQL trigger that usesgetFtsLanguage()and CJK regexes. Rust's FTS tokenizer must match on existing brains or search results drift.v248changespages.compiled_truthandtimelineto NULL-able and adds segmented-body storage withbody_storagedefault 'inline'. The migration file has no UPDATE or INSERT (grep), so existing pages are not rewritten. Confirm the Rust port keeps inline bodies valid.v249belief ledger,v262commit notifier,v265/v266derived generations, andv273segment derivations use long-lived lock and generation state. Their semantics are not in the SQL alone; the Rust port must model the same leases.v226seedstrust.backfill_completed_atonly when pages, facts, takes and timeline_entries are all empty;v227seedswrite_gate.scan_baselinefrom current max ids. A Rust fresh install and an upgrade must set these markers the same way or the upgrade gate changes.v221andv224backfill fromcreated_atandlast_retrieved_atat upgrade time, so exact timestamps are a parity point.- Several migrations depend on Postgres
pg_trgm(v240gin_trgm_ops),NULLS NOT DISTINCT(v302),md5()expression indexes (v315), and PL/pgSQLpg_procintrospection (v211, v277). An embedded replacement needs equivalent indexes or a different lookup path. v313andv290depend on wake subscriptions and cron state withFOR UPDATE-style semantics that the Rust engine must reproduce for correctness.