Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
-- GBrain Postgres + pgvector schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- gen_random_uuid() is core in Postgres 13+; enable pgcrypto as fallback for older versions
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- ============================================================
-- sources: multi-repo / multi-brain tenancy (v0.18.0)
-- ============================================================
-- A source is a logical brain-within-the-DB: wiki, gstack, yc-media, etc.
-- Every page/file/ingest_log row carries source_id.
--
-- id: immutable citation key. [a-z0-9-]{1,32} enforced at app layer.
-- Used in [source:slug] citations, --source flag, wikilink syntax.
-- name: mutable display label. Rename via `gbrain sources rename`.
-- local_path: optional git checkout root for filesystem-backed sources.
-- config: forward-compat JSONB. Currently used for federation + ACL slot.
-- { "federated": bool, "access_policy": {...} }
-- - federated=true (or missing-but-explicit on 'default'):
-- participates in cross-source default search.
-- - federated=false (default for new sources):
-- only searched when explicitly named via --source.
-- - access_policy: forward-compat slot, no enforcement in v0.17.
-- Write-side lockdown: mutated only when ctx.remote=false.
CREATE TABLE IF NOT EXISTS sources (
id TEXT PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
local_path TEXT,
last_commit TEXT,
last_sync_at TIMESTAMPTZ,
config JSONB NOT NULL DEFAULT '{}'::jsonb,
-- v0.20.0 Cathedral II (SP-1): chunker version last used to sync this source.
-- performSync forces a full walk when this mismatches CURRENT_CHUNKER_VERSION,
-- bypassing the git-HEAD up_to_date early-return so CHUNKER_VERSION bumps
-- actually trigger re-chunking on upgrade.
chunker_version TEXT,
-- v0.26.5: soft-delete + recovery window. `archive` flips archived=true and
-- sets archive_expires_at = now() + 72h. The autopilot purge phase
-- hard-deletes rows where archive_expires_at <= now(). Promoted from a
-- JSONB key to real columns to avoid reserved-key footguns and to make the
-- search visibility filter (`NOT s.archived`) a column lookup.
archived BOOLEAN NOT NULL DEFAULT false,
archived_at TIMESTAMPTZ,
archive_expires_at TIMESTAMPTZ,
-- v0.40.3.0: per-source CR mode override + mount-frontmatter trust gate.
-- contextual_retrieval_mode NULL = fall through to global mode bundle.
-- trust_frontmatter_overrides FALSE for mounts by default; host source
-- (id='default') is always trusted regardless of this column.
contextual_retrieval_mode TEXT,
trust_frontmatter_overrides BOOLEAN NOT NULL DEFAULT false,
-- v0.41.32.0 (supersedes #1623): newest COMMIT timestamp (HEAD committer
-- time) recorded at last sync. The REMOTE staleness path reads this instead
-- of shelling out to git on a DB-supplied local_path, preserving the
-- v0.41.27.0 trust boundary. NULL → reader falls back to wall-clock.
newest_content_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- #7112: text -> jsonb, NULL when the text is not valid jsonb. The portable
-- (Postgres 15+) guard for casting a double-encoded config or payload string;
-- IS JSON and pg_input_is_valid() are Postgres 16+. Migration v263 installs it
-- on existing brains.
CREATE OR REPLACE FUNCTION gbrain_try_jsonb(input text) RETURNS jsonb
LANGUAGE plpgsql IMMUTABLE STRICT SET search_path = pg_catalog, public, pg_temp AS $fn$
BEGIN
RETURN input::jsonb;
EXCEPTION WHEN data_exception THEN
RETURN NULL;
END $fn$;
-- Seed the default source. 'default' is federated=true for backward compat
-- (pre-v0.17 brains behave exactly as before — every page appears in search).
-- Pre-existing sync.repo_path / sync.last_commit are copied in by the v16
-- migration, not here; fresh installs have no local_path until `sources add`
-- or the first `sync`.
-- Avoid firing managed BEFORE INSERT guards for an existing seed on restart.
INSERT INTO sources (id, name, config)
SELECT 'default', 'default', '{"federated": true}'::jsonb
WHERE NOT EXISTS (SELECT 1 FROM sources WHERE id = 'default')
ON CONFLICT (id) DO NOTHING;
-- v0.40 Federated Sync v2: partial expression index on config->>'github_repo'
-- so POST /webhooks/github's source-by-repo lookup hits an index. Only rows
-- with a configured webhook actually take up index entries. Both Postgres and
-- PGLite support partial expression indexes. Migration v87 installs the same
-- index on legacy brains (idempotent IF NOT EXISTS).
CREATE INDEX IF NOT EXISTS sources_github_repo_idx
ON sources ((config->>'github_repo'))
WHERE config ? 'github_repo';
-- ============================================================
-- pages: the core content table
-- ============================================================
-- v0.18.0 (Step 2): pages.source_id scopes each row to a sources(id) row.
-- Slugs are unique per source, NOT globally. The default source is
-- seeded in the sources block above so the DEFAULT 'default' FK is
-- always valid at INSERT time.
CREATE TABLE IF NOT EXISTS pages (
id SERIAL PRIMARY KEY,
source_id TEXT NOT NULL DEFAULT 'default'
REFERENCES sources(id) ON DELETE CASCADE,
slug TEXT NOT NULL,
type TEXT NOT NULL,
-- v0.19.0: distinguishes markdown vs code pages at the DB level.
-- Drives orphans filter, auto-link bypass, and `query --lang`.
page_kind TEXT NOT NULL DEFAULT 'markdown'
CHECK (page_kind IN ('markdown','code','image')),
title TEXT NOT NULL,
compiled_truth TEXT NOT NULL DEFAULT '',
timeline TEXT NOT NULL DEFAULT '',
frontmatter JSONB NOT NULL DEFAULT '{}',
content_hash TEXT,
-- v0.29: deterministic 0..1 score (tag emotion + take density + Garry-as-holder ratio).
-- Populated by the `recompute_emotional_weight` cycle phase. Default 0.0 so freshly
-- imported pages don't pollute salience ranking before the cycle has run.
emotional_weight REAL NOT NULL DEFAULT 0.0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- v0.26.5: soft-delete + recovery window. `delete_page` sets deleted_at = now()
-- instead of issuing DELETE. The autopilot purge phase hard-deletes pages
-- where deleted_at < now() - 72h. Search and `get_page` filter
-- `WHERE deleted_at IS NULL` by default; `include_deleted: true` opts in.
deleted_at TIMESTAMPTZ,
-- v0.29.1: salience-and-recency, additive opt-in. All NULL by default;
-- only consulted when a caller passes `salience='on'` / `recency='on'` or
-- the new `since`/`until` filter. effective_date_source is a sentinel for
-- the doctor's effective_date_health check (values: 'event_date' | 'date'
-- | 'published' | 'filename' | 'fallback'). salience_touched_at is bumped
-- by recompute_emotional_weight when emotional_weight changes so the
-- salience window picks up newly-salient old pages.
effective_date TIMESTAMPTZ,
effective_date_source TEXT,
import_filename TEXT,
salience_touched_at TIMESTAMPTZ,
-- v0.37.0 (migration v79): real stale-page signal for `gbrain lsd`. Bumped
-- by op-layer write-back inside `search`/`query`/`get_page` op handlers
-- (NOT inside engine methods — internal callers must not pollute the
-- signal). NULL = never retrieved (LSD prioritizes these first).
last_retrieved_at TIMESTAMPTZ,
-- v0.42.7 (migration v112): link-extraction freshness watermark. Set when
-- link/timeline extraction last ran for this page (inline sync, `extract
-- --source db`, or `extract --stale`). A page is stale for extraction when
-- this is NULL, older than LINK_EXTRACTOR_VERSION_TS, or older than
-- updated_at (edited-since-extract — the MCP put_page / sync --no-extract
-- path). Powers `gbrain extract --stale` + the `links_extraction_lag` doctor
-- check. NULL = never extracted.
links_extracted_at TIMESTAMPTZ,
-- #5761: links_attendance_blocked_revision / _at (the attendance marker) are
-- added by migration v180 on every install, after the migration-added pages
-- columns, so fresh and upgraded brains share their ordinals.
-- #5254 (migration v177): 'unbound_source' marks a page written database-only
-- while its filesystem source had no canonical owner. Writes and sync after
-- binding keep it database-only. NULL for every other page.
database_only_reason TEXT,
-- v0.40.3.0 contextual retrieval (renumbered from v81 to v90 on master
-- merge). contextual_retrieval_mode is what tier the page was last embedded
-- under (NULL = pre-v90 = treated as 'none' for drift detection).
-- corpus_generation is the composite hash of (synopsis_prompt_version,
-- haiku_model, title_wrapper_version, embedding_model) — document-side
-- provenance for query_cache invalidation per D27 P1-5. NULL means pre-v90;
-- the page_generations JSONB check correctly invalidates pre-v90 cache
-- rows against any current generation.
contextual_retrieval_mode TEXT,
corpus_generation TEXT,
-- v0.40.3.0 cache invalidation gate (migration v91). Monotonic per-page
-- counter bumped by bump_page_generation_trg on INSERT (initial value =
-- MAX(generation) + 1 so the bookmark fires for any cache row stored
-- before this page existed — codex #4) and on UPDATE when any column in
-- the content allow-list IS DISTINCT FROM. Read by the per-page snapshot
-- check in query-cache-gate.ts.
generation BIGINT NOT NULL DEFAULT 1,
-- Wave B (migration v248): body_storage, body_bytes, truth_bytes, body_lines,
-- body_generation, segment_count, segmenter_version, body_flags, truth_lead and
-- timeline_lead, the NULL-able compiled_truth/timeline and pages_body_storage_chk
-- come from the migration on every install, after the migration-added pages
-- columns, so fresh and upgraded brains share their ordinals.
CONSTRAINT pages_source_slug_key UNIQUE (source_id, slug)
);
-- BEGIN GENERATED from src/core/page-body/schema.ts (PAGE_GENERATION_BUMP_FN_SQL). Edit that file, then run: bun run build:schema
CREATE OR REPLACE FUNCTION bump_page_generation_fn() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $func$
BEGIN
IF (TG_OP = 'INSERT') THEN
NEW.generation := COALESCE((SELECT MAX(generation) FROM pages), 0) + 1;
ELSIF (OLD.compiled_truth IS DISTINCT FROM NEW.compiled_truth)
OR (OLD.timeline IS DISTINCT FROM NEW.timeline)
OR (OLD.frontmatter IS DISTINCT FROM NEW.frontmatter)
OR (OLD.deleted_at IS DISTINCT FROM NEW.deleted_at)
OR (OLD.contextual_retrieval_mode IS DISTINCT FROM NEW.contextual_retrieval_mode)
OR (OLD.title IS DISTINCT FROM NEW.title)
OR (OLD.type IS DISTINCT FROM NEW.type)
OR (OLD.page_kind IS DISTINCT FROM NEW.page_kind)
OR (OLD.corpus_generation IS DISTINCT FROM NEW.corpus_generation)
OR (OLD.content_hash IS DISTINCT FROM NEW.content_hash)
THEN
NEW.generation := OLD.generation + 1;
ELSIF OLD.compiled_truth IS NULL AND NEW.compiled_truth IS NULL THEN
IF OLD.body_generation IS DISTINCT FROM NEW.body_generation THEN
NEW.generation := OLD.generation + 1;
END IF;
END IF;
RETURN NEW;
END;
$func$ LANGUAGE plpgsql;
-- END GENERATED from src/core/page-body/schema.ts (PAGE_GENERATION_BUMP_FN_SQL)
DROP TRIGGER IF EXISTS bump_page_generation_trg ON pages;
CREATE TRIGGER bump_page_generation_trg
BEFORE INSERT OR UPDATE ON pages
FOR EACH ROW
EXECUTE FUNCTION bump_page_generation_fn();
-- v0.40.3.0 supports O(log N) MAX(generation) for the Layer 1 bookmark
-- check in query-cache-gate.ts. Plain btree (DESC unnecessary; Postgres
-- backward-scans plain btrees for MAX per codex #8). CONCURRENTLY would
-- be used inside a migration; in the schema-bootstrap path it's a plain
-- CREATE INDEX since the table is empty.
CREATE INDEX IF NOT EXISTS pages_generation_idx ON pages (generation);
-- v0.41.19.0 (D18/D19, codex outside-voice): global page-generation clock.
-- The pre-v0.41.19.0 Layer 1 bookmark read `MAX(generation) FROM pages` to
-- detect "writes happened since cache-store". Two bugs in that contract:
-- 1. The row-level trigger above sets `NEW.generation = OLD.generation + 1`
-- on UPDATE. Updating a NON-MAX page didn't advance MAX(generation),
-- silently serving stale cached results.
-- 2. The trigger is `BEFORE INSERT OR UPDATE` so DELETE doesn't fire it
-- at all — and even if it did, DELETE doesn't touch surviving rows,
-- so MAX(generation) wouldn't budge.
--
-- The fix: a single-row counter, bumped per-statement (FOR EACH STATEMENT
-- — codex CDX-4: per-row would turn 73K-row batch DELETE into 73K UPDATEs
-- on the same counter, recreating the bottleneck this PR is fixing). Layer
-- 1 reads `page_generation_clock.value` directly. The per-row
-- `pages.generation` column above stays as the Layer 2 (per-page snapshot)
-- substrate.
--
-- Seeded with COALESCE(MAX(pages.generation), 0) so existing query_cache
-- rows stored under the old MAX semantics aren't all instantly invalidated
-- on upgrade (their max_generation_at_store stamp compares cleanly against
-- the seeded clock; future writes bump the clock, bookmark fires correctly).
CREATE TABLE IF NOT EXISTS page_generation_clock (
id INTEGER PRIMARY KEY CHECK (id = 1),
value BIGINT NOT NULL DEFAULT 0
);
INSERT INTO page_generation_clock (id, value)
VALUES (1, COALESCE((SELECT MAX(generation) FROM pages), 0))
ON CONFLICT (id) DO NOTHING;
-- v0.42.x: contention-free clock. nextval() takes a microsecond LWLock, not a
-- transaction-length row lock, so concurrent page writers no longer serialize
-- on one tuple's COMMIT. Load-bearing setval (2-arg -> is_called=true) so the
-- first write strictly exceeds the seed; floor 1 (sequence MINVALUE). Layer-1
-- reads last_value. The table + trigger names are retained.
CREATE SEQUENCE IF NOT EXISTS page_generation_clock_seq;
-- Monotonic seed: GREATEST over the sequence's OWN last_value too, so replaying
-- this blob on an already-upgraded brain (initSchema is re-runnable) can never
-- move last_value BACKWARD below a stored query_cache bookmark (which would let
-- Layer 1 serve stale rows). Mirrors the old table's ON CONFLICT DO NOTHING.
SELECT setval('page_generation_clock_seq', GREATEST(
1,
COALESCE((SELECT last_value FROM page_generation_clock_seq), 0),
COALESCE((SELECT value FROM page_generation_clock WHERE id = 1), 0),
COALESCE((SELECT MAX(generation) FROM pages), 0)
));
CREATE OR REPLACE FUNCTION bump_page_generation_clock_fn() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $func$
BEGIN
PERFORM nextval('page_generation_clock_seq');
RETURN NULL;
END;
$func$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS bump_page_generation_clock_trg ON pages;
CREATE TRIGGER bump_page_generation_clock_trg
AFTER INSERT OR UPDATE OR DELETE ON pages
FOR EACH STATEMENT
EXECUTE FUNCTION bump_page_generation_clock_fn();
CREATE INDEX IF NOT EXISTS idx_pages_type ON pages(type);
CREATE INDEX IF NOT EXISTS idx_pages_frontmatter ON pages USING GIN(frontmatter);
CREATE INDEX IF NOT EXISTS idx_pages_trgm ON pages USING GIN(title gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_pages_slug_basename ON pages (source_id, (regexp_replace(slug, '^.*/', '')));
-- v240: the entity name lookups (exact title, alias title check, slug suffix) as index scans.
CREATE INDEX IF NOT EXISTS idx_pages_source_title_lower ON pages (source_id, lower(title));
CREATE INDEX IF NOT EXISTS idx_pages_slug_trgm ON pages USING GIN (slug gin_trgm_ops);
-- v0.13.1 #170: avoids 14.6s seqscan on large brains when listing pages newest-first.
CREATE INDEX IF NOT EXISTS idx_pages_updated_at_desc ON pages (updated_at DESC);
-- v0.18.0: source-scoped scans (per /plan-eng-review Section 4).
CREATE INDEX IF NOT EXISTS idx_pages_source_id ON pages(source_id);
-- v0.26.5: partial index supports the autopilot purge sweep
-- (`WHERE deleted_at IS NOT NULL AND deleted_at < now() - INTERVAL '72 hours'`).
-- Search filters (`WHERE deleted_at IS NULL`) do not benefit from this index
-- (predicate doesn't match) and don't need their own — soft-deleted cardinality
-- stays low. Don't add a regular `(deleted_at)` index without measuring.
CREATE INDEX IF NOT EXISTS pages_deleted_at_purge_idx
ON pages (deleted_at) WHERE deleted_at IS NOT NULL;
-- Retrieval telemetry (src/core/last-retrieved.ts, migration v224): when a
-- user-facing read last surfaced a page, written at most once per page per 5
-- minutes. Kept off `pages` so the per-read upsert is a HOT update of a narrow
-- row: an UPDATE of pages fires its triggers, rewrites a wide tuple with an
-- entry in every pages index and advances page_generation_clock_seq, which
-- expires the query-cache bookmark. Readers take GREATEST of this and the
-- legacy pages.last_retrieved_at column (copied here by v224; v224 also drops
-- its unused index). No foreign key: its KEY SHARE lock would dirty the pages
-- row on every bump; the statement trigger below removes rows of hard-deleted
-- pages instead.
CREATE TABLE IF NOT EXISTS page_retrievals (
page_id INTEGER PRIMARY KEY,
last_retrieved_at TIMESTAMPTZ NOT NULL
);
-- BEGIN GENERATED from src/core/derived-state-maintainers.ts (PAGE_RETRIEVALS_FORGET_FUNCTION_SQL). Edit that file, then run: bun run build:schema
CREATE OR REPLACE FUNCTION gbrain_forget_page_retrievals() RETURNS trigger
LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $$
BEGIN
IF EXISTS (SELECT 1 FROM gbrain_deleted_pages) THEN
IF pg_catalog.row_security_active('page_retrievals') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r','d']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_retrievals'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_retrievals')::text;
END IF;
END IF;
DELETE FROM page_retrievals r USING gbrain_deleted_pages d WHERE r.page_id = d.id;
END IF;
RETURN NULL;
END $$;
-- END GENERATED from src/core/derived-state-maintainers.ts (PAGE_RETRIEVALS_FORGET_FUNCTION_SQL)
DROP TRIGGER IF EXISTS pages_forget_retrievals ON pages;
CREATE TRIGGER pages_forget_retrievals AFTER DELETE ON pages
REFERENCING OLD TABLE AS gbrain_deleted_pages
FOR EACH STATEMENT EXECUTE FUNCTION gbrain_forget_page_retrievals();
-- v0.42.7 (migration v112): composite B-tree backing `extract --stale` and the
-- `links_extraction_lag` doctor check. source_id leads so source-scoped staleness
-- scans (`extract --stale --source X`, `gbrain doctor --source X`) are indexed;
-- the brain-wide COUNT still uses it via the leading column. NOT partial-NULL —
-- the staleness predicate has a NULL arm AND a `< $versionTs` arm (B-tree sorts
-- NULLs to one end, covering both). The `updated_at > links_extracted_at` arm is
-- a cross-column filter no index covers; acceptable for a watermark COUNT.
CREATE INDEX IF NOT EXISTS pages_links_extracted_at_idx
ON pages (source_id, links_extracted_at);
-- v0.29.1: expression index used by since/until date-range filters that read
-- COALESCE(effective_date, updated_at). A partial index on effective_date
-- alone would NOT help — the planner can't use it for the negative side of
-- the COALESCE. Expression index is what actually accelerates the filter.
CREATE INDEX IF NOT EXISTS pages_coalesce_date_idx
ON pages ((COALESCE(effective_date, updated_at)));
-- ============================================================
-- content_chunks: chunked content with embeddings
-- ============================================================
CREATE TABLE IF NOT EXISTS content_chunks (
id SERIAL PRIMARY KEY,
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
chunk_index INTEGER NOT NULL,
chunk_text TEXT NOT NULL,
chunk_source TEXT NOT NULL DEFAULT 'compiled_truth',
embedding vector(1536),
model TEXT NOT NULL DEFAULT 'text-embedding-3-large',
token_count INTEGER,
embedded_at TIMESTAMPTZ,
-- v221: embedding_pending_since (when the stored vector last became missing;
-- doctor's embeddings check ages the backlog from it, never from created_at)
-- is added by migration v221 on every install, after the migration-added
-- content_chunks columns, so fresh and upgraded brains share their ordinals.
-- v244: projected_byte_start / projected_byte_end (a prose chunk's UTF-8
-- span in its source's read projection) are added the same way.
-- v248: occurrence_id, local_ordinal, raw_byte_start/end and body_generation
-- (segmented-page chunks; NULL for inline pages) and
-- content_chunks_occurrence_idx are added the same way, and so is v248's
-- retired_generation, which nothing writes or reads but released chunk readers
-- still select (v252 keeps it and adds content_chunks_generation_idx; v256
-- adds content_chunks_occurrence_generation_idx for chunk retirement).
-- #4246 (v133): md5(chunk_text) at embed time. NULL = no embedding or
-- pre-v133 row (grandfathered by invalidateContentDriftEmbeddings).
embedded_text_hash TEXT,
-- #5553 (v171): embedding-input provenance written with the vector.
embedding_input_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- v0.19.0: code chunk metadata. Nullable — markdown chunks leave these NULL.
-- Powers `query --lang`, `code-def <symbol>`, and `code-refs <symbol>`.
language TEXT,
symbol_name TEXT,
symbol_type TEXT,
start_line INTEGER,
end_line INTEGER,
-- v0.20.0 Cathedral II: qualified symbol identity + parent scope + doc-comment
-- + chunk-grain FTS. All nullable — markdown chunks leave these NULL.
parent_symbol_path TEXT[],
doc_comment TEXT,
symbol_name_qualified TEXT,
search_vector TSVECTOR,
-- v0.27.1 multimodal. modality discriminates text vs image rows for search
-- filtering. embedding_image holds 1024-dim Voyage multimodal vectors;
-- mixed-provider brains (e.g. OpenAI 1536 text + Voyage 1024 images) keep
-- both columns populated with distinct dim spaces.
modality TEXT NOT NULL DEFAULT 'text',
embedding_image vector(1024),
-- v0.36 Phase 3 cross-modal: unified column populated by reindex.
-- Migration v75 also adds it for upgrade paths.
embedding_multimodal vector(1024)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_chunks_page_index ON content_chunks(page_id, chunk_index);
CREATE INDEX IF NOT EXISTS idx_chunks_page ON content_chunks(page_id);
CREATE INDEX IF NOT EXISTS idx_chunks_embedding ON content_chunks USING hnsw (embedding vector_cosine_ops);
-- v0.19.0: partial indexes — only code chunks populate these columns.
CREATE INDEX IF NOT EXISTS idx_chunks_symbol_name ON content_chunks(symbol_name) WHERE symbol_name IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_chunks_language ON content_chunks(language) WHERE language IS NOT NULL;
-- v0.27.1: partial HNSW for multimodal images. Footprint stays proportional
-- to image-chunk count, not table size.
CREATE INDEX IF NOT EXISTS idx_chunks_embedding_image
ON content_chunks USING hnsw (embedding_image vector_cosine_ops)
WHERE embedding_image IS NOT NULL;
-- v0.20.0 Cathedral II: GIN index on the new chunk-grain FTS vector.
CREATE INDEX IF NOT EXISTS idx_chunks_search_vector ON content_chunks USING GIN(search_vector);
CREATE INDEX IF NOT EXISTS idx_chunks_symbol_qualified
ON content_chunks(symbol_name_qualified) WHERE symbol_name_qualified IS NOT NULL;
-- v0.41.18.0 (codex finding #9): partial index for `gbrain embed --stale`
-- + `--priority recent`. content_chunks has no updated_at column (chunks
-- are re-INSERTed on page change, not UPDATEd), so the "recent-first"
-- ORDER BY happens at the JOIN site: outer ORDER BY p.updated_at DESC
-- uses idx_pages_updated_at_desc; inner partial uses this index.
CREATE INDEX IF NOT EXISTS content_chunks_stale_idx
ON content_chunks(page_id, chunk_index) WHERE embedding IS NULL;
-- #6370: indexing and the title query read text through gbrain_fts_input(),
-- which splits CJK/ASCII-alnum boundaries (a no-op on text without CJK).
-- BEGIN GENERATED from src/core/fts-language.ts (FTS_INPUT_FUNCTION_SQL). Edit that file, then run: bun run build:schema
CREATE OR REPLACE FUNCTION gbrain_fts_input(t text) RETURNS text
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE SET search_path = pg_catalog, pg_temp
AS $fn$
SELECT regexp_replace(regexp_replace(t, '([一-鿿-ゟ゠-ヿ가-])([A-Za-z0-9])', '\1 \2', 'g'), '([A-Za-z0-9])([一-鿿-ゟ゠-ヿ가-])', '\1 \2', 'g')
$fn$;
-- END GENERATED from src/core/fts-language.ts (FTS_INPUT_FUNCTION_SQL)
-- v0.20.0 Cathedral II: chunk-grain FTS trigger.
-- Weight 'A' on doc_comment + symbol_name_qualified; weight 'B' on chunk_text.
-- NL queries ("how do we handle errors") rank doc-comment hits above body text.
-- BEFORE INSERT OR UPDATE OF specific columns — only refires when those change,
-- not on every chunk update (e.g., embedding refresh doesn't trigger rebuild).
CREATE OR REPLACE FUNCTION update_chunk_search_vector() RETURNS TRIGGER SET search_path = pg_catalog, public, pg_temp AS $fn$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('english', gbrain_fts_input(COALESCE(NEW.doc_comment, ''))), 'A') ||
setweight(to_tsvector('english', gbrain_fts_input(COALESCE(NEW.symbol_name_qualified, ''))), 'A') ||
setweight(to_tsvector('english', gbrain_fts_input(COALESCE(NEW.chunk_text, ''))), 'B');
RETURN NEW;
END;
$fn$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS chunk_search_vector_trigger ON content_chunks;
CREATE TRIGGER chunk_search_vector_trigger
BEFORE INSERT OR UPDATE OF chunk_text, doc_comment, symbol_name_qualified
ON content_chunks
FOR EACH ROW EXECUTE FUNCTION update_chunk_search_vector();
-- ============================================================
-- code_edges_chunk + code_edges_symbol: v0.20.0 Cathedral II structural edges
-- ============================================================
-- Two-table design (codex F4 + SP-7):
-- - code_edges_chunk: resolved edges (both endpoints = known chunk IDs)
-- - code_edges_symbol: unresolved refs (target known by qualified name,
-- defining chunk not yet imported)
-- Readers UNION both tables; no promotion step.
-- Source scoping: from_chunk_id -> content_chunks -> pages.source_id
-- determines the source. Resolution logic MUST scope on source (codex SP-3);
-- only --all-sources callers bypass this. UNIQUE keys don't include source_id
-- because from_chunk_id already pins it.
CREATE TABLE IF NOT EXISTS code_edges_chunk (
id SERIAL PRIMARY KEY,
from_chunk_id INTEGER NOT NULL REFERENCES content_chunks(id) ON DELETE CASCADE,
to_chunk_id INTEGER NOT NULL REFERENCES 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 REFERENCES sources(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT code_edges_chunk_unique UNIQUE (from_chunk_id, to_chunk_id, edge_type)
);
CREATE INDEX IF NOT EXISTS idx_code_edges_chunk_from
ON code_edges_chunk(from_chunk_id, edge_type);
CREATE INDEX IF NOT EXISTS idx_code_edges_chunk_to
ON code_edges_chunk(to_chunk_id, edge_type);
CREATE INDEX IF NOT EXISTS idx_code_edges_chunk_to_symbol
ON code_edges_chunk(to_symbol_qualified, edge_type);
-- getCalleesOf filters on from_symbol_qualified; without this index every
-- callee lookup is a sequential scan, amplified per-BFS-node by the
-- recursive code walk. Mirrors migration v116.
CREATE INDEX IF NOT EXISTS idx_code_edges_chunk_from_symbol
ON code_edges_chunk(from_symbol_qualified);
CREATE TABLE IF NOT EXISTS code_edges_symbol (
id SERIAL PRIMARY KEY,
from_chunk_id INTEGER NOT NULL REFERENCES 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 REFERENCES sources(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT code_edges_symbol_unique UNIQUE (from_chunk_id, to_symbol_qualified, edge_type)
);
CREATE INDEX IF NOT EXISTS idx_code_edges_symbol_from
ON code_edges_symbol(from_chunk_id, edge_type);
CREATE INDEX IF NOT EXISTS idx_code_edges_symbol_to
ON code_edges_symbol(to_symbol_qualified, edge_type);
-- getCalleesOf companion to idx_code_edges_chunk_from_symbol above.
-- Mirrors migration v116.
CREATE INDEX IF NOT EXISTS idx_code_edges_symbol_from_symbol
ON code_edges_symbol(from_symbol_qualified);
-- ============================================================
-- links: cross-references between pages
-- ============================================================
-- Provenance model (v0.13; opened to kebab provenance in v114 / issue #1941):
-- link_source — open kebab-case provenance tag, NOT a closed allowlist.
-- Format gate (CHECK): ^[a-z][a-z0-9]*(-[a-z0-9]+)*$ and
-- char_length <= 64. NULL = legacy row (pre-v0.13).
-- Reconciliation-managed built-ins written internally:
-- 'markdown' — body markdown links
-- 'frontmatter' — YAML frontmatter edges (see origin_*)
-- 'mentions' — auto-linked body-text mentions
-- 'wikilink-resolved'— opt-in global-basename [[name]] (#972)
-- User/tool-facing:
-- 'manual' — hand- or tool-created edges (the
-- add_link op default + CLI link-add)
-- '<your-tag>' — external derivers, e.g. 'citation-graph',
-- stamp their own kebab tag (no migration).
-- The add_link OP forbids callers from passing the four
-- managed built-ins (they imply reconciliation semantics a
-- hand-created row can't honor); the DB CHECK still admits
-- them because internal writers use them. See operations.ts.
-- origin_page_id — for link_source='frontmatter', the page whose YAML
-- frontmatter created this edge; scopes reconciliation
-- origin_field — the frontmatter field name (e.g. 'key_people')
--
-- The unique constraint includes link_source + origin_page_id so a manual edge
-- and a frontmatter-derived edge with the same (from, to, type) tuple coexist.
-- Reconciliation on put_page filters by (link_source='frontmatter' AND
-- origin_page_id = written_page) — never touches other pages' edges. (This is
-- exactly why a CLI-forged 'frontmatter' row with NULL origin would be a phantom
-- edge reconciliation never cleans — hence the op-layer guard.)
CREATE TABLE IF NOT EXISTS links (
id SERIAL PRIMARY KEY,
from_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
to_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
link_type TEXT NOT NULL DEFAULT '',
context TEXT NOT NULL DEFAULT '',
-- v0.41.18.0: 'mentions' added for auto-linked body-text mentions
-- (gbrain extract links --by-mention). Filtered OUT of backlink-count
-- for search ranking; only counts toward orphan-ratio + graph traversal.
-- v0.40.8.2 (#972): 'wikilink-resolved' added for opt-in global-basename
-- wikilink resolution (bare [[name]] resolved by slug tail).
link_source TEXT CHECK (link_source IS NULL OR (link_source ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$' AND char_length(link_source) <= 64)),
-- v0.41.18.0: nullable link_kind distinguishes "plain body mention" from
-- "verb-pattern-derived typed link" within link_source='mentions'.
-- Codex finding #12 design: keep link_source stable; add link_kind
-- so callers can distinguish without breaking existing mentions queries.
-- NULL = legacy / unknown / pre-v98 row (semantically 'plain').
link_kind TEXT CHECK (link_kind IS NULL OR link_kind IN ('plain', 'typed_ner')),
origin_page_id INTEGER REFERENCES pages(id) ON DELETE SET NULL,
origin_field TEXT,
-- v0.18.0 Step 4: 'qualified' when the link was written as
-- [[source:slug]] (target source pinned). 'unqualified' when written
-- as bare [[slug]] and resolved via local-first fallback at
-- extraction time. NULL for legacy/manual/frontmatter edges.
resolution_type TEXT CHECK (resolution_type IS NULL OR resolution_type IN ('qualified', 'unqualified')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- Temporal typed edges: tense of the assertion on its origin page ('past' for
-- "previously at", "former CTO of"); NULL = present. Temporal state lives in
-- link_transitions / link_relationships (src/core/link-temporal-schema.ts).
assertion_tense TEXT CONSTRAINT links_assertion_tense_check CHECK (assertion_tense IS NULL OR assertion_tense IN ('present', 'past')),
-- NULLS NOT DISTINCT (PG15+) so two rows with link_source IS NULL or
-- origin_page_id IS NULL collide as expected. Without this, every row with
-- NULL origin_page_id (markdown/manual edges) would be treated as unique.
CONSTRAINT links_from_to_type_source_origin_unique
UNIQUE NULLS NOT DISTINCT (from_page_id, to_page_id, link_type, link_source, origin_page_id)
);
CREATE INDEX IF NOT EXISTS idx_links_from ON links(from_page_id);
CREATE INDEX IF NOT EXISTS idx_links_to ON links(to_page_id);
CREATE INDEX IF NOT EXISTS idx_links_source ON links(link_source);
CREATE INDEX IF NOT EXISTS idx_links_origin ON links(origin_page_id);
CREATE INDEX IF NOT EXISTS idx_links_supersedes_to ON links (to_page_id) WHERE link_type = 'supersedes';
-- Wanted pages (migration wanted_links): authored links whose target page does
-- not exist. A live target updated after checked_at makes the origin stale for
-- link extraction (src/core/wanted-links.ts).
CREATE TABLE IF NOT EXISTS wanted_links (
id BIGSERIAL PRIMARY KEY,
origin_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
producer TEXT NOT NULL CHECK (producer IN ('body','frontmatter')),
ref_kind TEXT NOT NULL CHECK (ref_kind IN ('slug','name')),
target_source_id TEXT NOT NULL,
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(),
CONSTRAINT wanted_links_reference_unique
UNIQUE (origin_page_id, producer, ref_kind, target_source_id, target_ref)
);
CREATE INDEX IF NOT EXISTS wanted_links_target_idx ON wanted_links (target_source_id, target_ref);
CREATE INDEX IF NOT EXISTS wanted_links_source_idx ON wanted_links (source_id);
-- ============================================================
-- tags
-- ============================================================
CREATE TABLE IF NOT EXISTS tags (
id SERIAL PRIMARY KEY,
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
tag TEXT NOT NULL,
-- 'frontmatter' (import-owned, deleted when it leaves the frontmatter), 'added', or NULL (legacy).
tag_source TEXT,
UNIQUE(page_id, tag)
);
CREATE INDEX IF NOT EXISTS idx_tags_tag ON tags(tag);
CREATE INDEX IF NOT EXISTS idx_tags_page_id ON tags(page_id);
-- ============================================================
-- raw_data: sidecar data (replaces .raw/ JSON files)
-- ============================================================
CREATE TABLE IF NOT EXISTS raw_data (
id SERIAL PRIMARY KEY,
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
source TEXT NOT NULL,
data JSONB NOT NULL,
fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE(page_id, source)
);
CREATE INDEX IF NOT EXISTS idx_raw_data_page ON raw_data(page_id);
-- ============================================================
-- timeline_entries: structured timeline
-- ============================================================
CREATE TABLE IF NOT EXISTS timeline_entries (
id SERIAL PRIMARY KEY,
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
date DATE NOT NULL,
source TEXT NOT NULL DEFAULT '',
summary TEXT NOT NULL,
detail TEXT NOT NULL DEFAULT '',
-- v0.42.x (Life Chronicle #2390): when this row is the date-index projection
-- of a `type:event` page, event_page_id points at that event page; page_id
-- stays the depth/meeting page. NULL for ordinary timeline entries. Reads
-- hide rows whose event page is soft-deleted; the partial unique index below
-- keys dedup on (event_page_id, date) so re-extraction with a changed summary
-- updates the row instead of double-inserting.
event_page_id INTEGER REFERENCES pages(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_timeline_page ON timeline_entries(page_id);
CREATE INDEX IF NOT EXISTS idx_timeline_date ON timeline_entries(date);
-- v0.41.18.0 (codex finding #11): widened from (page_id, date, summary) to
-- include `source` so distinct meeting provenance survives. Legacy rows
-- have source='' (schema default) so legacy dedup behavior is preserved.
-- #3737: keyed on md5(summary) — a raw long/incompressible summary overflowed
-- the btree v4 row cap (~2704 bytes) and aborted the whole timeline insert.
-- Both insert sites infer ON CONFLICT (page_id, date, md5(summary), source).
CREATE UNIQUE INDEX IF NOT EXISTS idx_timeline_dedup ON timeline_entries(page_id, date, md5(summary), source);
-- v0.42.x (Life Chronicle): event-projection lookup + dedup. Partial
-- (event_page_id IS NOT NULL) so ordinary timeline rows are unaffected.
CREATE INDEX IF NOT EXISTS idx_timeline_event_page ON timeline_entries(event_page_id) WHERE event_page_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_timeline_event_dedup ON timeline_entries(event_page_id, date) WHERE event_page_id IS NOT NULL;
-- ============================================================
-- page_versions: snapshot history for compiled_truth
-- ============================================================
CREATE TABLE IF NOT EXISTS page_versions (
id SERIAL PRIMARY KEY,
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
compiled_truth TEXT NOT NULL,
frontmatter JSONB NOT NULL DEFAULT '{}',
snapshot_at TIMESTAMPTZ NOT NULL DEFAULT now()
-- v248: body_storage, body_generation, body_bytes, content_hash, the NULL-able
-- compiled_truth and page_versions_body_chk come from the migration on every
-- install (a segmented snapshot references a generation, not a body copy).
);
CREATE INDEX IF NOT EXISTS idx_versions_page ON page_versions(page_id);
-- ============================================================
-- ingest_log
-- ============================================================
-- v0.31.2 (codex P1 #3): source_id added so facts:absorb logging
-- (runFactsBackstop / writeFactsAbsorbLog) and doctor's
-- facts_extraction_health check can scope failure counts per source.
-- Migration v47 ALTERs existing brains; this inline definition covers
-- fresh installs.
CREATE TABLE IF NOT EXISTS ingest_log (
id SERIAL PRIMARY KEY,
source_id TEXT NOT NULL DEFAULT 'default',
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(),
-- Migration v254: the writing binary's version, host and segment-reader mark (NULL from a binary older than v254).
writer_version TEXT,
writer_host_id UUID,
writer_segment_reader BOOLEAN
);
CREATE INDEX IF NOT EXISTS idx_ingest_log_source_type_created
ON ingest_log (source_id, source_type, created_at DESC);
-- ============================================================
-- config: brain-level settings
-- ============================================================
CREATE TABLE IF NOT EXISTS config (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
INSERT INTO config (key, value) VALUES
('version', '1'),
('embedding_model', 'text-embedding-3-large'),
('embedding_dimensions', '1536'),
('chunk_strategy', 'semantic')
ON CONFLICT (key) DO NOTHING;
-- ============================================================
-- access_tokens: bearer tokens for remote MCP access
-- ============================================================
CREATE TABLE IF NOT EXISTS access_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
token_hash TEXT NOT NULL UNIQUE,
scopes TEXT[],
created_at TIMESTAMPTZ DEFAULT now(),
last_used_at TIMESTAMPTZ,
revoked_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_access_tokens_hash ON access_tokens (token_hash) WHERE revoked_at IS NULL;
-- ============================================================
-- mcp_request_log: usage logging for remote MCP requests
-- ============================================================
CREATE TABLE IF NOT EXISTS mcp_request_log (
id SERIAL PRIMARY KEY,
token_name TEXT,
agent_name TEXT,
operation TEXT NOT NULL,
latency_ms INTEGER,
status TEXT NOT NULL DEFAULT 'success',
params JSONB,
error_message TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- ============================================================
-- OAuth 2.1: clients, tokens, authorization codes
-- ============================================================
CREATE TABLE IF NOT EXISTS oauth_clients (
client_id TEXT PRIMARY KEY,
client_secret_hash TEXT,
client_name TEXT NOT NULL,
redirect_uris TEXT[],
grant_types TEXT[] DEFAULT '{"client_credentials"}',
scope TEXT,
token_endpoint_auth_method TEXT,
client_id_issued_at BIGINT,
client_secret_expires_at BIGINT,
token_ttl INTEGER,
deleted_at TIMESTAMPTZ,
source_id TEXT REFERENCES sources(id) ON DELETE RESTRICT,
federated_read TEXT[] NOT NULL DEFAULT '{}',
-- v0.38 Slice 2 + 3: per-client daily budget cap (v84) + agent binding (v85).
budget_usd_per_day NUMERIC(10, 2) NULL,
bound_tools TEXT[] NULL,
bound_source_id TEXT NULL,
bound_brain_id TEXT NULL,
bound_slug_prefixes TEXT[] NULL,
bound_max_concurrent INTEGER NOT NULL DEFAULT 1,
-- WP4 (v127): per-client MCP tool surface + who set it ('operator' |
-- 'self' | 'dcr_default'). Value space is OPEN (future client tiers write
-- tier names into surface); NULL = server/config surface resolution.
surface TEXT NULL,
surface_set_by TEXT NULL,
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(),
-- Explicit no-source grant ('none') and per-client takes holders (NULL =
-- ['world']); upgrades add both through src/core/grants/oauth-client-axes-schema.ts.
source_grant TEXT NULL CHECK (source_grant IN ('none')),
takes_holders TEXT[] NULL
);
-- v0.34.1 (#861, D13 + #876): source_id is the write-source scope;
-- federated_read is the read-source array. Migrations v60-v65 land both
-- columns on upgrade; fresh installs include them inline above.
CREATE INDEX IF NOT EXISTS idx_oauth_clients_source_id
ON oauth_clients(source_id) WHERE source_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_oauth_clients_federated_read
ON oauth_clients USING GIN (federated_read);
-- BEGIN GENERATED from src/core/grants/schema.ts (GRANT_AUDIT_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS oauth_grant_audit (
id BIGSERIAL PRIMARY KEY,
client_id TEXT NOT NULL,
actor TEXT NOT NULL,
action TEXT NOT NULL,
revision INTEGER NOT NULL,
before_grant JSONB,
after_grant JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_oauth_grant_audit_client ON oauth_grant_audit(client_id, created_at);
-- END GENERATED from src/core/grants/schema.ts (GRANT_AUDIT_SCHEMA_SQL)
-- The facts index and withdrawal trigger are installed by migrations 60/148.
-- BEGIN GENERATED from src/core/facts/withdrawal-schema.ts (FACT_WITHDRAWAL_SCHEMA_STATEMENTS[0]). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS fact_withdrawals (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
visibility TEXT NOT NULL CHECK (visibility IN ('private','world')),
subject TEXT NOT NULL DEFAULT '*',
fact_hash TEXT NOT NULL,
withdrawn_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, visibility, subject, fact_hash)
);
-- END GENERATED from src/core/facts/withdrawal-schema.ts (FACT_WITHDRAWAL_SCHEMA_STATEMENTS[0])
CREATE TABLE IF NOT EXISTS oauth_tokens (
token_hash TEXT PRIMARY KEY,
token_type TEXT NOT NULL,
client_id TEXT NOT NULL REFERENCES oauth_clients(client_id) ON DELETE CASCADE,
scopes TEXT[],
expires_at BIGINT,
resource TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_oauth_tokens_expiry ON oauth_tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_oauth_tokens_client ON oauth_tokens(client_id);
CREATE TABLE IF NOT EXISTS oauth_codes (
code_hash TEXT PRIMARY KEY,
client_id TEXT NOT NULL REFERENCES oauth_clients(client_id) ON DELETE CASCADE,
scopes TEXT[],
code_challenge TEXT NOT NULL,
code_challenge_method TEXT NOT NULL DEFAULT 'S256',
redirect_uri TEXT NOT NULL,
state TEXT,
resource TEXT,
expires_at BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Composite indexes for admin dashboard request log queries
CREATE INDEX IF NOT EXISTS idx_mcp_log_time_agent ON mcp_request_log(created_at, token_name);
CREATE INDEX IF NOT EXISTS idx_mcp_log_agent_time ON mcp_request_log(agent_name, created_at DESC);
-- ============================================================
-- op_checkpoints: shared checkpoint table for long-running ops
-- ============================================================
-- v0.36+ autonomous-remediation wave (migration v67). Pre-fix each op
-- carried its own file-backed checkpoint (or none); that broke on
-- Postgres multi-worker hosts and fingerprint-collided across param
-- variations. Fingerprint = sha8 of canonical-JSON of relevant params
-- per op (mode, source, chunker_version, embedding_model+dims, etc.).
-- completed_keys = op-defined string array. GC: cycle purge phase
-- drops rows older than 7 days.
CREATE TABLE IF NOT EXISTS op_checkpoints (
op TEXT NOT NULL,
fingerprint TEXT NOT NULL,
-- v0.42.x: must be a JSONB array. The loader runs jsonb_array_elements_text
-- over it; a scalar would throw and wipe the whole checkpoint load. CHECK is
-- the DB-enforced always-on guard (mirrors migration v119).
completed_keys JSONB NOT NULL DEFAULT '[]'::jsonb
CONSTRAINT op_checkpoints_completed_keys_array CHECK (jsonb_typeof(completed_keys) = 'array'),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (op, fingerprint)
);
CREATE INDEX IF NOT EXISTS op_checkpoints_updated_at_idx
ON op_checkpoints (updated_at);
-- #5988: read paths find the Git hold of a page by its id (migration v200).
CREATE INDEX IF NOT EXISTS op_checkpoints_sync_hold_page_idx
ON op_checkpoints ((completed_keys->0->>'page_id')) WHERE op = 'sync-hold';
-- #1794: append-only delta storage. One row per completed path; sync's
-- appendCompleted INSERTs only the delta instead of rewriting the whole
-- completed_keys JSONB array each flush (O(N^2) -> O(delta)). FK cascade drops
-- children with the parent (clearOpCheckpoint + 7-day purge). PK prefix
-- (op,fingerprint) serves all reads; no separate index. Mirrors migration v115.
CREATE TABLE IF NOT EXISTS op_checkpoint_paths (
op TEXT NOT NULL,
fingerprint TEXT NOT NULL,
path TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (op, fingerprint, path),
CONSTRAINT op_checkpoint_paths_parent_fk
FOREIGN KEY (op, fingerprint) REFERENCES op_checkpoints (op, fingerprint) ON DELETE CASCADE
);
-- #2095 push-based context: feedback-loop log of volunteered pages
-- (volunteer_context op / retrieval-reflex pointers / gbrain watch).
-- "Used" derives from pages.last_retrieved_at > volunteered_at; rationale is
-- a deterministic template string, never raw conversation text. 90-day prune
-- in the dream cycle's purge phase. Mirrors migration v117.
CREATE TABLE IF NOT EXISTS context_volunteer_events (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
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,
turn INTEGER,
volunteered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS context_volunteer_events_src_time_idx
ON context_volunteer_events (source_id, volunteered_at DESC);
CREATE INDEX IF NOT EXISTS context_volunteer_events_src_slug_idx
ON context_volunteer_events (source_id, slug);
-- Use-attributed retrieval feedback (src/core/feedback/): answers record the
-- pages (with the retrieved revision's content_hash) and relational-path edges
-- they used; ratings move per-element weights (neutral 0.5) that the search
-- ranking stage reads. No query text is stored. Mirrors migration v207.
CREATE TABLE IF NOT EXISTS retrieval_events (
id TEXT PRIMARY KEY,
client_id TEXT NOT NULL DEFAULT 'local',
op TEXT NOT NULL CHECK (op IN ('query','search','think','synthesize','recall')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS retrieval_events_time_idx ON retrieval_events (created_at);
CREATE TABLE IF NOT EXISTS retrieval_event_pages (
event_id TEXT NOT NULL REFERENCES retrieval_events(id) ON DELETE CASCADE,
source_id TEXT NOT NULL,
slug TEXT NOT NULL,
content_hash TEXT,
rank INTEGER NOT NULL,
cited BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (event_id, source_id, slug)
);
CREATE TABLE IF NOT EXISTS retrieval_event_links (
event_id TEXT NOT NULL REFERENCES retrieval_events(id) ON DELETE CASCADE,
source_id TEXT NOT NULL,
edge_key TEXT NOT NULL,
to_slug TEXT NOT NULL,
PRIMARY KEY (event_id, source_id, edge_key, to_slug)
);
CREATE TABLE IF NOT EXISTS retrieval_feedback (
event_id TEXT NOT NULL REFERENCES retrieval_events(id) ON DELETE CASCADE,
signal TEXT NOT NULL CHECK (signal IN ('explicit','cited')),
element_kind TEXT NOT NULL CHECK (element_kind IN ('page','link')),
source_id TEXT NOT NULL,
element_key TEXT NOT NULL,
client_id TEXT NOT NULL DEFAULT 'local',
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
weight_before REAL NOT NULL,
weight_after REAL NOT NULL,
applied_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (event_id, signal, element_kind, source_id, element_key)
);
CREATE INDEX IF NOT EXISTS retrieval_feedback_client_time_idx
ON retrieval_feedback (client_id, applied_at DESC);
CREATE TABLE IF NOT EXISTS retrieval_weights (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
element_kind TEXT NOT NULL CHECK (element_kind IN ('page','link')),
element_key TEXT NOT NULL,
weight REAL NOT NULL DEFAULT 0.5 CHECK (weight >= 0 AND weight <= 1),
content_hash TEXT,
updates INTEGER NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, element_kind, element_key)
);
-- session_context_state (v0.45.7 / migration v126 — ambient recall issue #1):
-- per-session cursor + boundary-tie dedup for the `delta` verb + heartbeat
-- runtime. Key (source_id, client_id, session_id); client_id 'local' sentinel
-- for CLI/hook, remote auth client id otherwise. jsonb DDL-literal defaults.
-- facts_cursor_at/_id + degraded_wakes (migration v208): delta's per-arm facts
-- keyset and its consecutive incomplete-wake counter.
CREATE TABLE IF NOT EXISTS session_context_state (
source_id TEXT NOT NULL,
client_id TEXT NOT NULL DEFAULT 'local',
session_id TEXT NOT NULL,
standing_entities JSONB NOT NULL DEFAULT '[]'::jsonb,
surfaced_slugs JSONB NOT NULL DEFAULT '[]'::jsonb,
checkpoint_manifest JSONB NOT NULL DEFAULT '[]'::jsonb,
last_wake_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
facts_cursor_at TIMESTAMPTZ,
facts_cursor_id BIGINT,
degraded_wakes INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (source_id, client_id, session_id)
);
CREATE INDEX IF NOT EXISTS session_context_state_updated_idx
ON session_context_state (updated_at);
-- extract_atoms_transcript_state (migration v146 — #4148 follow-on): failure
-- streak + tombstone for TRANSCRIPT extraction items. Pages carry this in
-- frontmatter (atoms_fail_count / atoms_fail_hash / atoms_scan_hash), but
-- transcripts are files with no frontmatter, so pre-fix a transcript that
-- deterministically produced malformed output — or that honestly yielded zero
-- atoms — re-entered discovery and re-spent LLM budget on every cycle forever.
--
-- Why its own table. raw_data is page-scoped (page_id NOT NULL REFERENCES
-- pages(id)) and transcripts aren't pages, exactly as dream_verdicts' own
-- comment notes. And NOT columns on dream_verdicts: that cache is documented
-- rebuildable via `gbrain dream retriage --force`, which clears it wholesale,
-- so extraction state stored there would be swept away as collateral.
--
-- Why source_id is in the key, unlike dream_verdicts. That table caches a
-- content-level judgment ("is this transcript worth processing"), which does
-- not vary by source. A failure streak and a tombstone GATE EXTRACTION, and
-- extraction is source-scoped throughout the phase — discovery SQL, the NOT
-- EXISTS idempotency subquery, and every putPage take sourceId. An unscoped
-- tombstone would let one source permanently suppress another source's
-- extraction of the same file.
--
-- content_hash is the 16-char prefix, matching atoms.frontmatter->>'source_hash'
-- and the page-side atoms_fail_hash. Having it in the PK is what makes "a
-- content edit resets the streak" fall out for free: an edited transcript is a
-- different row, mirroring the page-side hash-keyed reset.
--
-- RLS: covered by the v35 auto_rls_on_create_table event trigger, same posture
-- as session_context_state (v126) and chat_usage_log (v140).
CREATE TABLE IF NOT EXISTS extract_atoms_transcript_state (
source_id TEXT NOT NULL DEFAULT 'default',
file_path TEXT NOT NULL,
content_hash TEXT NOT NULL,
fail_count INTEGER NOT NULL DEFAULT 0,
tombstoned BOOLEAN NOT NULL DEFAULT FALSE,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- #6503 (migration v240): the max_input_chars cap a cut transcript was read
-- under; discovery skips it until the cap rises. NULL when not partial.
partial_input_chars INTEGER,
PRIMARY KEY (source_id, file_path, content_hash)
);
CREATE INDEX IF NOT EXISTS extract_atoms_transcript_state_tombstoned_idx
ON extract_atoms_transcript_state (source_id, content_hash)
WHERE tombstoned;
-- chat_usage_log (#4218 / migration v140): durable per-call chat usage
-- ledger. One row per SUCCESSFUL gateway.chat() call, written fire-and-forget
-- by the chat-usage sink (src/core/ai/chat-usage.ts). cost_usd is a
-- canonical-table estimate; NULL when the model has no pricing (never a fake
-- 0). Read back by the `get_usage` op with explicit coverage fields.
CREATE TABLE IF NOT EXISTS chat_usage_log (
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
model TEXT NOT NULL,
provider TEXT,
phase TEXT,
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
);
CREATE INDEX IF NOT EXISTS idx_chat_usage_log_created
ON chat_usage_log (created_at);
CREATE INDEX IF NOT EXISTS idx_chat_usage_log_model
ON chat_usage_log (model, created_at);
-- open_loops (migration v144): the Gmail-first open-loop engine's structured
-- record — "who is waiting on you, what you promised". Deduped per source on
-- dedup_key; loops close by state transition, never delete. fact_id projects
-- LLM-extracted commitments into the facts table so entity cards see them.
CREATE TABLE IF NOT EXISTS open_loops (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
dedup_key TEXT NOT NULL,
loop_type TEXT NOT NULL CHECK (loop_type IN (
'commitment_owed_by_me','commitment_owed_to_me',
'unanswered_inbound','unanswered_outbound','decision_pending')),
counterparty_slug TEXT,
counterparty_email TEXT,
summary TEXT NOT NULL,
evidence JSONB NOT NULL DEFAULT '[]'::jsonb,
thread_id TEXT,
page_slug TEXT,
due_at TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','done','dropped','stale')),
detector TEXT NOT NULL CHECK (detector IN ('deterministic_thread','llm_extract','manual')),
confidence REAL NOT NULL DEFAULT 1.0,
fact_id BIGINT,
opened_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now(),
closed_at TIMESTAMPTZ,
closed_by TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT open_loops_dedup UNIQUE (source_id, dedup_key)
);
CREATE INDEX IF NOT EXISTS open_loops_status_idx
ON open_loops (source_id, status, last_activity_at DESC);
CREATE INDEX IF NOT EXISTS open_loops_counterparty_idx
ON open_loops (source_id, counterparty_slug) WHERE status = 'open';
CREATE INDEX IF NOT EXISTS open_loops_thread_idx
ON open_loops (source_id, thread_id) WHERE status = 'open';
-- loop_suppressions (migration v144): `gbrain loops mute <sender|thread>` —
-- the detector's user feedback loop. Suppressed senders/threads never open
-- new loops (existing loops keep their state).
CREATE TABLE IF NOT EXISTS loop_suppressions (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
kind TEXT NOT NULL CHECK (kind IN ('sender','thread')),
value TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT loop_suppressions_uniq UNIQUE (source_id, kind, value)
);
-- migration_impact_log moved BELOW minion_jobs (was here, lines 645-676)
-- because its `job_id BIGINT REFERENCES minion_jobs(id)` FK requires
-- minion_jobs to exist FIRST during SCHEMA_SQL replay. v0.41.25.0 fix.
-- ============================================================
-- files: binary attachments stored in Supabase Storage
-- ============================================================
-- v0.18.0 Step 7: files gains source_id + page_id alongside the
-- legacy page_slug (kept for backward compat until a later release).
-- The file_migration_ledger below drives the storage object rewrite.
-- page_slug FK had ON UPDATE CASCADE — removed because slugs are no
-- longer global (composite UNIQUE) so CASCADE on-update is ambiguous.
-- ON DELETE SET NULL is preserved via both page_slug and page_id.
CREATE TABLE IF NOT EXISTS files (
id SERIAL PRIMARY KEY,
source_id TEXT NOT NULL DEFAULT 'default'
REFERENCES sources(id) ON DELETE CASCADE,
page_slug TEXT,
page_id INTEGER REFERENCES pages(id) ON DELETE SET NULL,
filename TEXT NOT NULL,
storage_path TEXT NOT NULL,
mime_type TEXT,
size_bytes BIGINT,
content_hash TEXT NOT NULL,
metadata JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE(storage_path)
);
-- Migration: drop storage_url if it exists (renamed to storage_path only)
ALTER TABLE files DROP COLUMN IF EXISTS storage_url;
CREATE INDEX IF NOT EXISTS idx_files_page ON files(page_slug);
CREATE INDEX IF NOT EXISTS idx_files_page_id ON files(page_id);
CREATE INDEX IF NOT EXISTS idx_files_source_id ON files(source_id);
CREATE INDEX IF NOT EXISTS idx_files_hash ON files(content_hash);
-- ============================================================
-- file_migration_ledger (v0.18.0 Step 7)
-- Drives the storage-object rewrite performed by the v0_18_0
-- orchestrator's phase B. Keyed on file_id so two sources can share
-- an old path during migration without PK collision (Codex second-
-- pass caught this).
-- Status state machine: pending → copy_done → db_updated → complete
-- ============================================================
CREATE TABLE IF NOT EXISTS file_migration_ledger (
file_id INTEGER PRIMARY KEY REFERENCES files(id) ON DELETE CASCADE,
storage_path_old TEXT NOT NULL,
storage_path_new TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
error TEXT,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT chk_ledger_status CHECK (status IN ('pending','copy_done','db_updated','complete','failed'))
);
CREATE INDEX IF NOT EXISTS idx_file_migration_ledger_status
ON file_migration_ledger(status) WHERE status != 'complete';
-- ============================================================
-- Trigger-based search_vector (spans pages + timeline_entries)
-- ============================================================
ALTER TABLE pages ADD COLUMN IF NOT EXISTS search_vector tsvector;
CREATE INDEX IF NOT EXISTS idx_pages_search ON pages USING GIN(search_vector);
-- GBRA-156 (v267): the title arm's title-only predicate reads the weight-A slice (the title alone).
CREATE INDEX IF NOT EXISTS idx_pages_title_search ON pages USING GIN (ts_filter(search_vector, '{a}'));
-- Function to rebuild search_vector for a page
-- #2704: compiled_truth (unbounded whole-page body) deliberately NOT
-- indexed — overflows Postgres's 1MB tsvector cap on large pages.
-- content_chunks.search_vector (chunk-grain, populated separately) is
-- what searchKeyword() actually queries. See migrate.ts's v124 migration
-- for the full rationale; keep in sync with that + reindex-search-vector.ts
-- + pglite-schema.ts.
CREATE OR REPLACE FUNCTION update_page_search_vector() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $$
DECLARE
timeline_text TEXT;
timeline_lead_text TEXT;
BEGIN
IF TG_OP = 'UPDATE' THEN
IF pg_catalog.row_security_active('timeline_entries') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'timeline_entries'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','timeline_entries')::text;
END IF;
END IF;
END IF;
SELECT coalesce(string_agg(summary || ' ' || detail, ' ' ORDER BY id), '')
INTO timeline_text
FROM timeline_entries
WHERE page_id = NEW.id;
IF NEW.timeline IS NULL THEN
timeline_lead_text := NEW.timeline_lead;
END IF;
NEW.search_vector :=
setweight(to_tsvector('english', gbrain_fts_input(coalesce(NEW.title, ''))), 'A') ||
setweight(to_tsvector('english', gbrain_fts_input(left(coalesce(NEW.timeline, timeline_lead_text, ''), 65536))), 'C') ||
setweight(to_tsvector('english', gbrain_fts_input(left(coalesce(timeline_text, ''), 32768))), 'C');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_pages_search_vector ON pages;
CREATE TRIGGER trg_pages_search_vector
BEFORE INSERT OR UPDATE OF title,timeline ON pages
FOR EACH ROW
EXECUTE FUNCTION update_page_search_vector();
-- Note: timeline_entries trigger removed (v0.10.1).
-- Structured timeline_entries power temporal queries (graph layer).
-- The markdown timeline section in pages.timeline still feeds search_vector via
-- the trg_pages_search_vector trigger above. Removing the timeline_entries
-- trigger avoids double-weighting the same content in search and prevents
-- mutation-induced reordering during timeline-extract pagination.
DROP TRIGGER IF EXISTS trg_timeline_search_vector ON timeline_entries;
DROP FUNCTION IF EXISTS update_page_search_vector_from_timeline();
-- ============================================================
-- Minion Jobs: BullMQ-inspired Postgres-native job queue
-- ============================================================
CREATE TABLE IF NOT EXISTS minion_jobs (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
queue TEXT NOT NULL DEFAULT 'default',
status TEXT NOT NULL DEFAULT 'waiting',
priority INTEGER NOT NULL DEFAULT 0,
submission_authority JSONB, -- NULL legacy rows require local authorization before workers start
claim_generation BIGINT NOT NULL DEFAULT 0, -- advanced atomically by protocol-aware claims
data JSONB NOT NULL DEFAULT '{}',
max_attempts INTEGER NOT NULL DEFAULT 3,
attempts_made INTEGER NOT NULL DEFAULT 0,
attempts_started INTEGER NOT NULL DEFAULT 0,
backoff_type TEXT NOT NULL DEFAULT 'exponential',
backoff_delay INTEGER NOT NULL DEFAULT 1000,
backoff_jitter REAL NOT NULL DEFAULT 0.2,
stalled_counter INTEGER NOT NULL DEFAULT 0,
max_stalled INTEGER NOT NULL DEFAULT 5,
lock_token TEXT,
lock_until TIMESTAMPTZ,
delay_until TIMESTAMPTZ,
parent_job_id INTEGER REFERENCES minion_jobs(id) ON DELETE SET NULL,
on_child_fail TEXT NOT NULL DEFAULT 'fail_parent',
tokens_input INTEGER NOT NULL DEFAULT 0,
tokens_output INTEGER NOT NULL DEFAULT 0,
tokens_cache_read INTEGER NOT NULL DEFAULT 0,
result JSONB,
progress JSONB,
error_text TEXT,
stacktrace JSONB DEFAULT '[]',
depth INTEGER NOT NULL DEFAULT 0,
max_children INTEGER,
timeout_ms INTEGER,
lock_duration_ms INTEGER,
timeout_at TIMESTAMPTZ,
remove_on_complete BOOLEAN NOT NULL DEFAULT FALSE,
remove_on_fail BOOLEAN NOT NULL DEFAULT FALSE,
idempotency_key TEXT,
private_queue_owner_job_id INTEGER REFERENCES minion_jobs(id) ON DELETE SET NULL,
private_queue_owner_token TEXT,
private_queue_lease_until TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
started_at TIMESTAMPTZ,
finished_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT chk_status CHECK (status IN ('waiting','active','completed','failed','delayed','dead','cancelled','waiting-children','paused')),
CONSTRAINT chk_backoff_type CHECK (backoff_type IN ('fixed','exponential')),
CONSTRAINT chk_on_child_fail CHECK (on_child_fail IN ('fail_parent','remove_dep','ignore','continue')),
CONSTRAINT chk_jitter_range CHECK (backoff_jitter >= 0.0 AND backoff_jitter <= 1.0),
CONSTRAINT chk_attempts_order CHECK (attempts_made <= attempts_started),
CONSTRAINT chk_nonnegative CHECK (attempts_made >= 0 AND attempts_started >= 0 AND stalled_counter >= 0 AND max_attempts >= 1 AND max_stalled >= 0),
CONSTRAINT chk_depth_nonnegative CHECK (depth >= 0),
CONSTRAINT chk_max_children_positive CHECK (max_children IS NULL OR max_children > 0),
CONSTRAINT chk_timeout_positive CHECK (timeout_ms IS NULL OR timeout_ms > 0),
CONSTRAINT chk_lock_duration_positive CHECK (lock_duration_ms IS NULL OR (lock_duration_ms >= 5000 AND lock_duration_ms <= 3600000))
);
CREATE INDEX IF NOT EXISTS idx_minion_jobs_claim ON minion_jobs (queue, priority ASC, created_at ASC) WHERE status = 'waiting';
CREATE INDEX IF NOT EXISTS idx_minion_jobs_status ON minion_jobs(status);
CREATE INDEX IF NOT EXISTS idx_minion_jobs_stalled ON minion_jobs (lock_until) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS idx_minion_jobs_delayed ON minion_jobs (delay_until) WHERE status = 'delayed';
CREATE INDEX IF NOT EXISTS idx_minion_jobs_parent ON minion_jobs(parent_job_id);
CREATE INDEX IF NOT EXISTS idx_minion_jobs_timeout ON minion_jobs (timeout_at) WHERE status = 'active' AND timeout_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_minion_jobs_parent_status ON minion_jobs (parent_job_id, status) WHERE parent_job_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uniq_minion_jobs_idempotency ON minion_jobs (idempotency_key) WHERE idempotency_key IS NOT NULL;
-- WP4/WP5 (v127, ENG-10): wedge-signal index — covers the queue-health count
-- FILTERs and max(updated_at) reads in queryWedgeSignals (supervisor.ts).
CREATE INDEX IF NOT EXISTS idx_minion_jobs_queue_status_updated ON minion_jobs (queue, status, updated_at);
CREATE INDEX IF NOT EXISTS idx_minion_jobs_private_queue_recovery
ON minion_jobs (queue, private_queue_lease_until)
WHERE queue LIKE 'dream-inline-%'
AND status IN ('waiting','active','delayed','waiting-children','paused');
CREATE INDEX IF NOT EXISTS idx_minion_jobs_private_queue_owner
ON minion_jobs (private_queue_owner_job_id)
WHERE private_queue_owner_job_id IS NOT NULL;
-- Inbox table for sidechannel messaging
CREATE TABLE IF NOT EXISTS minion_inbox (
id SERIAL PRIMARY KEY,
job_id INTEGER NOT NULL REFERENCES minion_jobs(id) ON DELETE CASCADE,
sender TEXT NOT NULL,
payload JSONB NOT NULL,
sent_at TIMESTAMPTZ NOT NULL DEFAULT now(),
read_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_minion_inbox_unread ON minion_inbox (job_id) WHERE read_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_minion_inbox_child_done ON minion_inbox (job_id, sent_at) WHERE payload->>'type' = 'child_done';
-- Attachments table: per-job binary blobs (manifests, agent outputs, files)
CREATE TABLE IF NOT EXISTS minion_attachments (
id SERIAL PRIMARY KEY,
job_id INTEGER NOT NULL REFERENCES minion_jobs(id) ON DELETE CASCADE,
filename TEXT NOT NULL,
content_type TEXT NOT NULL,
content BYTEA,
storage_uri TEXT,
size_bytes INTEGER NOT NULL,
sha256 TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT uniq_minion_attachments_job_filename UNIQUE (job_id, filename),
CONSTRAINT chk_attachment_storage CHECK (content IS NOT NULL OR storage_uri IS NOT NULL),
CONSTRAINT chk_attachment_size CHECK (size_bytes >= 0)
);
CREATE INDEX IF NOT EXISTS idx_minion_attachments_job ON minion_attachments (job_id);
ALTER TABLE minion_attachments ALTER COLUMN content SET STORAGE EXTERNAL;
-- ============================================================
-- migration_impact_log: before/after metric stats per onboard remediation
-- ============================================================
-- v0.41.18.0 (gbrain onboard wave). Every completion captured by the
-- onboard remediation pipeline records before/after metric stats so
-- `gbrain onboard --history --json` can show "you reduced orphans 47%".
-- delta computed at read time (NOT a stored GENERATED column —
-- zero PGLite parity risk per eng-review D2).
--
-- Attribution columns (job_id, source_id, brain_id, started_at,
-- idempotency_key) per codex finding #10 so concurrent onboard /
-- autopilot / manual runs can't misattribute deltas to the wrong
-- migration when overlapping runs change the same metric.
--
-- v0.41.25.0 SCHEMA_SQL ordering fix: this block lives AFTER the
-- minion_jobs CREATE TABLE so the `job_id REFERENCES minion_jobs(id)`
-- FK can resolve on fresh-install schema replay. Originally placed above
-- minion_jobs in v0.41.18.0; that fired ERROR: relation "minion_jobs"
-- does not exist on every fresh-install initSchema() (silent on master
-- because postgres-js's unsafe() continued past the error, but the
-- table never got created so any later query on migration_impact_log
-- threw 42P01 — which cascaded as "relation minion_jobs does not exist"
-- whenever subsequent statements that referenced minion_jobs ran AFTER
-- the failed CREATE TABLE statement, aborting the entire SCHEMA_SQL batch).
CREATE TABLE IF NOT EXISTS migration_impact_log (
id BIGSERIAL PRIMARY KEY,
remediation_id TEXT NOT NULL,
metric_name TEXT NOT NULL,
metric_before NUMERIC,
metric_after NUMERIC,
job_id BIGINT REFERENCES minion_jobs(id) ON DELETE SET NULL,
source_id TEXT,
brain_id TEXT,
started_at TIMESTAMPTZ,
idempotency_key TEXT,
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
applied_by TEXT,
details JSONB DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS migration_impact_log_remediation_idx
ON migration_impact_log(remediation_id, applied_at DESC);
CREATE INDEX IF NOT EXISTS migration_impact_log_attribution_idx
ON migration_impact_log(job_id, source_id) WHERE job_id IS NOT NULL;
-- ============================================================
-- Subagent runtime (v0.16.0) — durable LLM loops
-- ============================================================
-- Anthropic-native message blocks, one row per Messages API message. Parallel
-- tool_use blocks in one assistant message live in content_blocks JSONB,
-- not across rows.
CREATE TABLE IF NOT EXISTS subagent_messages (
id BIGSERIAL PRIMARY KEY,
job_id BIGINT NOT NULL REFERENCES minion_jobs(id) ON DELETE CASCADE,
message_idx INTEGER NOT NULL,
role TEXT NOT NULL,
-- v0.27+ stores provider-neutral ChatBlock[] when schema_version=2; legacy
-- Anthropic-shape blocks when schema_version=1 (pre-v0.27 jobs replay).
content_blocks JSONB NOT NULL,
schema_version INTEGER NOT NULL DEFAULT 1,
-- Recipe id of the provider that produced this turn (e.g. 'anthropic',
-- 'openai', 'deepseek'). NULL on legacy v1 rows; set on v2.
provider_id TEXT,
tokens_in INTEGER,
tokens_out INTEGER,
tokens_cache_read INTEGER,
tokens_cache_create INTEGER,
model TEXT,
ended_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT uniq_subagent_messages_idx UNIQUE (job_id, message_idx),
CONSTRAINT chk_subagent_messages_role CHECK (role IN ('user','assistant'))
);
CREATE INDEX IF NOT EXISTS idx_subagent_messages_job ON subagent_messages (job_id, message_idx);
CREATE INDEX IF NOT EXISTS idx_subagent_messages_provider ON subagent_messages (job_id, provider_id);
-- Two-phase tool execution ledger. Before tool call: INSERT status='pending'.
-- After success: UPDATE to 'complete' + output. On failure: 'failed' + error.
-- Replay re-runs 'pending' rows only if the tool is idempotent.
--
-- tool_use_id holds the RAW provider id and is deliberately NOT unique per
-- job (#4155): replay-style providers (claude-cli spawns a fresh subprocess
-- per turn from an id-stripped transcript) reuse the same short id on every
-- turn. Row identity is (job_id, message_idx, ordinal); readers that resolve
-- a tool_use block to its execution row must key by (message_idx, tool_use_id),
-- never by tool_use_id alone. The former job-wide unique constraint
-- uniq_subagent_tools_use_id was dropped in migration v131.
CREATE TABLE IF NOT EXISTS subagent_tool_executions (
id BIGSERIAL PRIMARY KEY,
job_id BIGINT NOT NULL REFERENCES minion_jobs(id) ON DELETE CASCADE,
message_idx INTEGER NOT NULL,
tool_use_id TEXT NOT NULL,
tool_name TEXT NOT NULL,
input JSONB NOT NULL,
status TEXT NOT NULL,
output JSONB,
error TEXT,
schema_version INTEGER NOT NULL DEFAULT 1,
provider_id TEXT,
-- v0.38 D11: gbrain-owned stable IDs (ordinal assigned at first
-- observation of a tool call; gbrain_tool_use_id is uuid v7). Reconciliation
-- on crash-replay uses (job_id, message_idx, ordinal) as the unique key.
-- Legacy rows (pre-v82) have ordinal=NULL + gbrain_tool_use_id=NULL and
-- are resolved by the read-time D5 shim that recomputes the key from
-- (job_id, message_idx, content_blocks index, tool_name).
ordinal INTEGER,
gbrain_tool_use_id UUID,
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
ended_at TIMESTAMPTZ,
CONSTRAINT subagent_tool_executions_stable_id UNIQUE (job_id, message_idx, ordinal),
CONSTRAINT chk_subagent_tools_status CHECK (status IN ('pending','complete','failed'))
);
CREATE INDEX IF NOT EXISTS idx_subagent_tools_job ON subagent_tool_executions (job_id, status);
-- Rate-lease table — concurrency cap on outbound providers (e.g.
-- anthropic:messages). Acquire: INSERT if active < max_concurrent under
-- advisory lock. Release: DELETE. Stale leases (expires_at past) auto-prune
-- on next acquire so crashed workers can't strand capacity.
CREATE TABLE IF NOT EXISTS subagent_rate_leases (
id BIGSERIAL PRIMARY KEY,
key TEXT NOT NULL,
owner_job_id BIGINT NOT NULL REFERENCES minion_jobs(id) ON DELETE CASCADE,
acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
expires_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_rate_leases_key_expires ON subagent_rate_leases (key, expires_at);
-- ============================================================
-- Dream-cycle significance verdict cache — v0.21 synthesize phase
-- ============================================================
-- Caches the cheap Haiku "is this transcript worth processing?" verdict
-- per (file_path, content_hash) so backfill re-runs skip already-judged
-- files. Distinct from raw_data (which is page-scoped); transcripts
-- aren't pages.
CREATE TABLE IF NOT EXISTS dream_verdicts (
file_path TEXT NOT NULL,
content_hash TEXT NOT NULL,
worth_processing BOOLEAN NOT NULL,
reasons JSONB,
judged_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- #4152 triage-v1 (migration v129): ordinal salience score in [0,1],
-- content type, candidate segments/entities, judging model + prompt
-- version. NULL on boolean-era rows — readers treat those as cache misses.
score DOUBLE PRECISION,
content_type TEXT,
segments JSONB,
entities JSONB,
model TEXT,
triage_version INT,
-- #4069 (migration v138): 30-day verdict TTL. Reads treat expired rows as
-- misses; the synthesize phase sweeps them best-effort.
expires_at TIMESTAMPTZ NOT NULL DEFAULT (now() + interval '30 days'),
PRIMARY KEY (file_path, content_hash)
);
CREATE INDEX IF NOT EXISTS dream_verdicts_expires_idx ON dream_verdicts (expires_at);
-- ============================================================
-- Cycle coordination lock — v0.17 runCycle primitive
-- ============================================================
-- One row per active cycle. Any caller (autopilot daemon, Minions
-- autopilot-cycle handler, gbrain dream CLI) tries to acquire this
-- row before running a DB-write phase. Holders refresh ttl_expires_at
-- between phases; crashed holders auto-release once TTL expires.
-- Works through PgBouncer transaction pooling, unlike session-scoped
-- pg_try_advisory_lock.
CREATE TABLE IF NOT EXISTS gbrain_cycle_locks (
id TEXT PRIMARY KEY,
holder_pid INT NOT NULL,
holder_host TEXT,
acquired_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
ttl_expires_at TIMESTAMPTZ NOT NULL,
-- v0.41.13.0 (migration v97 + D-V3-4): bumped on every withRefreshingLock
-- refresh tick. Used by `gbrain sync --break-lock --max-age <s>` to
-- identify wedged-but-alive holders without stealing healthy long-running
-- holders that are actively refreshing.
last_refreshed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_cycle_locks_ttl ON gbrain_cycle_locks(ttl_expires_at);
-- BEGIN GENERATED from src/core/lease-schema.ts (LEASE_TOKEN_SCHEMA_SQL). Edit that file, then run: bun run build:schema
ALTER TABLE gbrain_cycle_locks
ADD COLUMN IF NOT EXISTS acquisition_token UUID NOT NULL DEFAULT gen_random_uuid();
-- END GENERATED from src/core/lease-schema.ts (LEASE_TOKEN_SCHEMA_SQL)
-- ============================================================
-- Eval capture (v0.25.0 — BrainBench-Real substrate)
-- ============================================================
-- eval_candidates: captured query/search calls from the op-layer wrapper
-- in src/core/operations.ts. PII is scrubbed before insert by
-- src/core/eval-capture-scrub.ts. query is CHECK-capped at 50KB.
-- eval_capture_failures: cross-process audit of insert failures, surfaced
-- by `gbrain doctor` (in-process counters can't bridge MCP server + doctor
-- CLI process boundaries).
CREATE TABLE IF NOT EXISTS eval_candidates (
id SERIAL PRIMARY KEY,
tool_name TEXT NOT NULL CHECK (tool_name IN ('query', 'search')),
query TEXT NOT NULL CHECK (length(query) <= 51200),
retrieved_slugs TEXT[] NOT NULL DEFAULT '{}',
retrieved_chunk_ids INTEGER[] NOT NULL DEFAULT '{}',
source_ids TEXT[] NOT NULL DEFAULT '{}',
expand_enabled BOOLEAN,
detail TEXT CHECK (detail IS NULL OR detail IN ('low', 'medium', 'high')),
detail_resolved TEXT CHECK (detail_resolved IS NULL OR detail_resolved 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,
subagent_id INTEGER,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- v0.29.1 — agent-explicit recency + salience capture for replay reproducibility.
-- All nullable + additive. NDJSON schema_version stays at 1; consumers ignore unknown fields.
as_of_ts TIMESTAMPTZ,
salience_param TEXT,
recency_param TEXT,
salience_resolved TEXT,
recency_resolved TEXT,
salience_source TEXT,
recency_source TEXT,
-- v0.36.3.0 (D16 / CDX-10) — embedding column resolved at capture time so
-- `gbrain eval replay` reproduces the same column the capture ran against.
-- Nullable; pre-v0.36 rows have NULL and replay falls back to current
-- default. Migration v68 (src/core/migrate.ts) adds the same column on
-- upgrade brains.
embedding_column TEXT
);
CREATE INDEX IF NOT EXISTS idx_eval_candidates_created_at ON eval_candidates(created_at DESC);
CREATE TABLE IF NOT EXISTS eval_capture_failures (
id SERIAL PRIMARY KEY,
ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
reason TEXT NOT NULL CHECK (reason IN ('db_down', 'rls_reject', 'check_violation', 'scrubber_exception', 'other'))
);
CREATE INDEX IF NOT EXISTS idx_eval_capture_failures_ts ON eval_capture_failures(ts DESC);
-- eval_takes_quality_runs (v0.32 — EXP-5): DB-authoritative receipts for the
-- takes-quality eval CLI. 4-sha unique key (corpus, prompt, models, rubric)
-- so re-running the same run is a no-op (ON CONFLICT DO NOTHING) and a
-- future rubric tweak segregates trend rows cleanly. receipt_json carries
-- the full receipt blob so `replay` can reconstruct when the disk artifact
-- is missing. Mirrored in src/core/pglite-schema.ts + migration v49.
CREATE TABLE IF NOT EXISTS eval_takes_quality_runs (
id BIGSERIAL PRIMARY KEY,
receipt_sha8_corpus TEXT NOT NULL,
receipt_sha8_prompt TEXT NOT NULL,
receipt_sha8_models TEXT NOT NULL,
receipt_sha8_rubric TEXT NOT NULL,
rubric_version TEXT NOT NULL,
verdict TEXT NOT NULL CHECK (verdict IN ('pass','fail','inconclusive')),
overall_score REAL NOT NULL,
dim_scores JSONB NOT NULL,
cost_usd REAL NOT NULL,
receipt_json JSONB NOT NULL,
receipt_disk_path TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (receipt_sha8_corpus, receipt_sha8_prompt, receipt_sha8_models, receipt_sha8_rubric)
);
CREATE INDEX IF NOT EXISTS eval_takes_quality_runs_trend_idx
ON eval_takes_quality_runs (rubric_version, created_at DESC);
-- eval_contradictions_cache (v0.32.6): persistent judge verdicts for the
-- contradiction probe. Composite primary key includes prompt_version +
-- truncation_policy so any prompt edit cleanly invalidates prior verdicts
-- (Codex outside-voice fix). TTL via expires_at; cache.ts sweeps periodically.
CREATE TABLE IF NOT EXISTS eval_contradictions_cache (
chunk_a_hash TEXT NOT NULL,
chunk_b_hash TEXT NOT NULL,
model_id TEXT NOT NULL,
prompt_version TEXT NOT NULL,
truncation_policy TEXT NOT NULL,
verdict JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
expires_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (chunk_a_hash, chunk_b_hash, model_id, prompt_version, truncation_policy)
);
CREATE INDEX IF NOT EXISTS eval_contradictions_cache_expires_idx
ON eval_contradictions_cache (expires_at);
-- eval_contradictions_runs (v0.32.6): time-series tracking for the probe.
-- One row per run; source for the `trend` sub-subcommand and the doctor
-- `contradictions` check. report_json carries the full ProbeReport for replay.
CREATE TABLE IF NOT EXISTS eval_contradictions_runs (
run_id TEXT PRIMARY KEY,
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
);
CREATE INDEX IF NOT EXISTS eval_contradictions_runs_ran_at_idx
ON eval_contradictions_runs (ran_at DESC);
-- ============================================================
-- v0.36.1.0 Hindsight calibration wave (migrations v67-v71)
-- ============================================================
-- See src/core/migrate.ts for full design notes per table.
--
-- calibration_profiles: per-holder LLM-narrative aggregation of
-- TakesScorecard data. source_id-scoped per v0.34.1 isolation discipline.
-- published flag gates E8 cross-brain mount sharing (D15 asymmetric opt-in).
CREATE TABLE IF NOT EXISTS calibration_profiles (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES 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,
accuracy REAL,
partial_rate REAL,
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),
judge_model_agreement REAL
);
CREATE INDEX IF NOT EXISTS calibration_profiles_holder_recent_idx
ON calibration_profiles (source_id, holder, generated_at DESC);
CREATE INDEX IF NOT EXISTS calibration_profiles_bias_tags_gin
ON calibration_profiles USING GIN (active_bias_tags);
CREATE INDEX IF NOT EXISTS calibration_profiles_published_idx
ON calibration_profiles (source_id, published, holder)
WHERE published = true;
-- take_proposals: per-claim idempotency via source/page/content/prompt plus
-- md5(claim_text). The old per-page key silently dropped claim #2+ (#2138).
CREATE TABLE IF NOT EXISTS take_proposals (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
page_slug TEXT NOT NULL,
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 (status IN ('pending','accepted','rejected','superseded')),
claim_text TEXT NOT NULL,
kind TEXT NOT NULL,
holder TEXT NOT NULL,
weight REAL NOT NULL,
domain TEXT,
dedup_against_fence_rows JSONB,
model_id TEXT NOT NULL,
acted_at TIMESTAMPTZ,
acted_by TEXT,
promoted_row_num INTEGER,
predicted_brier REAL,
predicted_brier_bucket_n INTEGER
);
CREATE UNIQUE INDEX IF NOT EXISTS take_proposals_idempotency_idx
ON take_proposals (source_id, page_slug, content_hash, prompt_version, md5(claim_text));
CREATE INDEX IF NOT EXISTS take_proposals_pending_idx
ON take_proposals (source_id, status, proposed_at DESC)
WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS take_proposals_run_id_idx
ON take_proposals (proposal_run_id);
-- take_grade_cache: grade_takes verdict cache. Composite PK on
-- (take_id, prompt_version, judge_model_id, evidence_signature) means
-- prompt edits OR evidence changes cleanly invalidate prior verdicts.
-- applied=false default + D17 auto-resolve-off-by-default = every fresh
-- install needs operator opt-in before grade verdicts mutate takes table.
CREATE TABLE IF NOT EXISTS take_grade_cache (
take_id BIGINT NOT NULL,
prompt_version TEXT NOT NULL,
judge_model_id TEXT NOT NULL,
evidence_signature TEXT NOT NULL,
wave_version TEXT NOT NULL DEFAULT 'v0.36.1.0',
graded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
verdict TEXT NOT NULL
CHECK (verdict IN ('correct','incorrect','partial','unresolvable')),
confidence REAL NOT NULL,
applied BOOLEAN NOT NULL DEFAULT false,
cost_usd NUMERIC(10,4),
PRIMARY KEY (take_id, prompt_version, judge_model_id, evidence_signature)
);
CREATE INDEX IF NOT EXISTS take_grade_cache_applied_idx
ON take_grade_cache (take_id, applied);
CREATE INDEX IF NOT EXISTS take_grade_cache_wave_idx
ON take_grade_cache (wave_version, graded_at DESC);
-- take_nudge_log: E7 nudge cooldown state. Polymorphic FK — a nudge fires
-- on either a canonical take OR a pending proposal (CDX-5). CHECK enforces
-- exactly one is set.
CREATE TABLE IF NOT EXISTS take_nudge_log (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
take_id BIGINT,
proposal_id BIGINT REFERENCES 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',
CONSTRAINT take_nudge_log_target_xor
CHECK ((take_id IS NOT NULL) <> (proposal_id IS NOT NULL))
);
CREATE INDEX IF NOT EXISTS take_nudge_log_take_cooldown_idx
ON take_nudge_log (take_id, nudge_pattern, fired_at DESC)
WHERE take_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS take_nudge_log_proposal_cooldown_idx
ON take_nudge_log (proposal_id, nudge_pattern, fired_at DESC)
WHERE proposal_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS take_nudge_log_wave_idx
ON take_nudge_log (wave_version, fired_at DESC);
-- think_ab_results (v0.36.1.0 T18 / D19): A/B harness data for the think
-- A/B harness (runAbTrial). One row per side-by-side comparison.
CREATE TABLE IF NOT EXISTS think_ab_results (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES 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 (preferred IN ('baseline','with_calibration','neither','tie')),
model_id TEXT,
notes TEXT
);
CREATE INDEX IF NOT EXISTS think_ab_results_recent_idx
ON think_ab_results (source_id, ran_at DESC);
-- Reject pre-upgrade producers/claims; old reapers must still be stopped during cutover.
CREATE OR REPLACE FUNCTION enforce_minion_queue_protocol() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $protocol$
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.submission_authority IS NULL OR NEW.claim_generation <> 0 THEN
RAISE EXCEPTION 'Minion queue protocol 1 required: upgrade every producer and worker before restart';
END IF;
ELSIF NEW.status = 'active' AND (OLD.status <> 'active' OR NEW.lock_token IS DISTINCT FROM OLD.lock_token) THEN
IF NEW.submission_authority IS NULL OR NEW.claim_generation IS DISTINCT FROM OLD.claim_generation + 1 THEN
RAISE EXCEPTION 'Minion queue protocol 1 required: old workers cannot claim upgraded queue jobs';
END IF;
IF NEW.spend_authorization IS NOT NULL AND NEW.spend_claim_token IS DISTINCT FROM NEW.claim_generation THEN
RAISE EXCEPTION 'Minion spend protocol 1 required: only upgraded workers can claim spend-authorized jobs; restart workers on the upgraded binary';
END IF;
ELSIF NEW.claim_generation IS DISTINCT FROM OLD.claim_generation THEN
RAISE EXCEPTION 'Minion queue claim generation may advance only with a claim';
END IF;
RETURN NEW;
END;
$protocol$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS minion_queue_protocol ON minion_jobs;
CREATE TRIGGER minion_queue_protocol BEFORE INSERT OR UPDATE ON minion_jobs
FOR EACH ROW EXECUTE FUNCTION enforce_minion_queue_protocol();
-- NOTIFY trigger for real-time job events (Postgres only, not PGLite)
CREATE OR REPLACE FUNCTION notify_minion_job_change() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $$
BEGIN
PERFORM pg_notify('minion_jobs', json_build_object(
'id', NEW.id, 'status', NEW.status, 'name', NEW.name,
'queue', NEW.queue, 'prev_status', COALESCE(OLD.status, 'new')
)::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS minion_job_notify ON minion_jobs;
CREATE TRIGGER minion_job_notify AFTER INSERT OR UPDATE OF status ON minion_jobs
FOR EACH ROW EXECUTE FUNCTION notify_minion_job_change();
-- ============================================================
-- Row Level Security: block anon access, postgres role bypasses
-- ============================================================
-- The postgres role (used by gbrain via pooler) has BYPASSRLS.
-- Enabling RLS with no policies means the anon key can't read anything.
-- Only enable if the current role actually has BYPASSRLS privilege,
-- otherwise we'd lock ourselves out.
DO $$
DECLARE
has_bypass BOOLEAN;
target TEXT;
BEGIN
-- #1385: recognize superuser + inherited-role BYPASSRLS, not just the role's
-- own rolbypassrls (alias `pr` avoids any plpgsql record-variable collision).
SELECT EXISTS (SELECT 1 FROM pg_roles pr WHERE pg_has_role(current_user, pr.oid, 'USAGE') AND (pr.rolbypassrls OR pr.rolsuper)) INTO has_bypass;
IF has_bypass THEN
-- #5227 (W14 P1.6): conditional enables. ALTER TABLE ... ENABLE ROW LEVEL SECURITY takes an
-- ACCESS EXCLUSIVE lock even when RLS is already on, so re-running this block on a live brain
-- queued behind every open transaction; a table whose relrowsecurity is already set is skipped.
FOREACH target IN ARRAY ARRAY[
'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'] LOOP
IF NOT EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = current_schema() AND c.relname = target AND c.relrowsecurity) THEN
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', target);
END IF;
END LOOP;
RAISE NOTICE 'RLS enabled on all tables (role % has BYPASSRLS)', current_user;
ELSE
RAISE WARNING 'Skipping RLS: role % does not have BYPASSRLS privilege. Run as postgres role to enable.', current_user;
END IF;
END $$;
-- Canonical page state (migration 150).
-- BEGIN GENERATED from src/core/page-state/schema.ts (PAGE_STATE_SCHEMA_SQL). Edit that file, then run: bun run build:schema
DO $do$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'sources'::regclass AND attname = 'incarnation' AND NOT attisdropped) THEN
ALTER TABLE sources ADD COLUMN IF NOT EXISTS incarnation UUID;
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'sources'::regclass AND attname = 'incarnation' AND atthasdef) THEN
ALTER TABLE sources ALTER COLUMN incarnation SET DEFAULT gen_random_uuid();
END IF;
IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'sources'::regclass AND attname = 'incarnation' AND NOT attnotnull) THEN
UPDATE sources SET incarnation = gen_random_uuid() WHERE incarnation IS NULL;
ALTER TABLE sources ALTER COLUMN incarnation SET NOT NULL;
END IF;
END $do$;
CREATE UNIQUE INDEX IF NOT EXISTS sources_incarnation_key ON sources(incarnation);
DO $do$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'pages'::regclass AND attname = 'knowledge_revision' AND NOT attisdropped) THEN
ALTER TABLE pages ADD COLUMN IF NOT EXISTS knowledge_revision UUID;
END IF;
IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'pages'::regclass AND attname = 'knowledge_revision' AND atthasdef) THEN
ALTER TABLE pages ALTER COLUMN knowledge_revision SET DEFAULT gen_random_uuid();
END IF;
IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'pages'::regclass AND attname = 'knowledge_revision' AND NOT attnotnull)
AND NOT EXISTS (SELECT 1 FROM pages) THEN
ALTER TABLE pages ALTER COLUMN knowledge_revision SET NOT NULL;
END IF;
END $do$;
ALTER TABLE pages ADD COLUMN IF NOT EXISTS text_projection_revision UUID;
ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS knowledge_revision UUID;
ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS timeline TEXT;
ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS title TEXT;
ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS type TEXT;
ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS tags JSONB;
ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS is_deleted BOOLEAN;;
CREATE TABLE IF NOT EXISTS page_write_guards (
source_incarnation UUID NOT NULL REFERENCES sources(incarnation) ON DELETE CASCADE,
slug TEXT NOT NULL,
PRIMARY KEY (source_incarnation, slug)
);
CREATE OR REPLACE FUNCTION gbrain_advance_page_revision() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $fn$
DECLARE body_moved boolean := false;
BEGIN
IF OLD.knowledge_revision IS NULL THEN
NEW.knowledge_revision := COALESCE(NEW.knowledge_revision, gen_random_uuid());
NEW.text_projection_revision := NULL;
RETURN NEW;
END IF;
IF NEW.compiled_truth IS NULL AND OLD.compiled_truth IS NULL THEN
body_moved := NEW.body_generation IS DISTINCT FROM OLD.body_generation;
END IF;
IF (NEW.source_id, NEW.slug, NEW.type, NEW.page_kind, NEW.title, NEW.compiled_truth,
NEW.timeline, NEW.frontmatter, NEW.deleted_at)
IS DISTINCT FROM
(OLD.source_id, OLD.slug, OLD.type, OLD.page_kind, OLD.title, OLD.compiled_truth,
OLD.timeline, OLD.frontmatter, OLD.deleted_at) OR body_moved THEN
IF NEW.knowledge_revision = OLD.knowledge_revision AND NOT (
COALESCE(current_setting('gbrain.materializing_revision', true), '') = OLD.knowledge_revision::text
AND (NEW.source_id, NEW.slug, NEW.type, NEW.page_kind, NEW.title, NEW.frontmatter, NEW.deleted_at)
IS NOT DISTINCT FROM
(OLD.source_id, OLD.slug, OLD.type, OLD.page_kind, OLD.title, OLD.frontmatter, OLD.deleted_at)
) THEN
NEW.knowledge_revision := gen_random_uuid();
END IF;
END IF;
IF NEW.knowledge_revision IS DISTINCT FROM OLD.knowledge_revision THEN
NEW.text_projection_revision := NULL;
END IF;
RETURN NEW;
END $fn$;
DROP TRIGGER IF EXISTS pages_knowledge_revision ON pages;
CREATE TRIGGER pages_knowledge_revision BEFORE UPDATE ON pages
FOR EACH ROW EXECUTE FUNCTION gbrain_advance_page_revision();
CREATE OR REPLACE FUNCTION gbrain_advance_tag_revision() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $fn$
BEGIN
IF TG_OP = 'UPDATE' AND (NEW.page_id, NEW.tag) IS NOT DISTINCT FROM (OLD.page_id, OLD.tag) THEN
RETURN NULL;
END IF;
IF pg_catalog.row_security_active('pages') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r','w']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'pages'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','pages')::text;
END IF;
END IF;
IF TG_OP <> 'INSERT' THEN
UPDATE pages SET knowledge_revision = gen_random_uuid() WHERE id = OLD.page_id;
END IF;
IF TG_OP <> 'DELETE' AND (TG_OP = 'INSERT' OR (NEW.page_id, NEW.tag) IS DISTINCT FROM (OLD.page_id, OLD.tag)) THEN
UPDATE pages SET knowledge_revision = gen_random_uuid() WHERE id = NEW.page_id;
END IF;
RETURN NULL;
END $fn$;
DROP TRIGGER IF EXISTS tags_knowledge_revision ON tags;
CREATE TRIGGER tags_knowledge_revision AFTER INSERT OR DELETE OR UPDATE ON tags
FOR EACH ROW EXECUTE FUNCTION gbrain_advance_tag_revision();
-- END GENERATED from src/core/page-state/schema.ts (PAGE_STATE_SCHEMA_SQL)
-- #5393 (migration v182): the page's recorded canonical file when a version
-- was taken, so a revert is judged on the version it writes. After the
-- page-state columns so fresh and upgraded brains share its ordinal.
ALTER TABLE page_versions ADD COLUMN IF NOT EXISTS source_path TEXT;
CREATE TABLE IF NOT EXISTS extract_atoms_page_state (
source_incarnation UUID NOT NULL REFERENCES sources(incarnation) ON DELETE CASCADE,
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
content_hash TEXT NOT NULL,
fail_count INTEGER NOT NULL DEFAULT 0 CHECK (fail_count >= 0),
tombstoned BOOLEAN NOT NULL DEFAULT false,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- #6503 (migration v240): the max_input_chars cap a cut page was read under;
-- discovery skips it until the cap rises. NULL when not partial.
partial_input_chars INTEGER,
PRIMARY KEY (source_incarnation, page_id, content_hash)
);
CREATE INDEX IF NOT EXISTS extract_atoms_page_state_tombstoned_idx
ON extract_atoms_page_state (source_incarnation, content_hash, page_id) WHERE tombstoned;
CREATE INDEX IF NOT EXISTS extract_atoms_page_state_page_idx ON extract_atoms_page_state (page_id);
-- GBRA-161: the generation ledger (src/core/generations/, docs/architecture/generations.md).
CREATE TABLE IF NOT EXISTS derived_generations (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
kind TEXT NOT NULL,
owner_key TEXT NOT NULL,
generation INTEGER NOT NULL DEFAULT 0,
state TEXT NOT NULL CHECK (state IN ('live','held','superseded')),
run_id TEXT,
operation_id TEXT NOT NULL,
hold_id TEXT,
input_pin TEXT,
input_size BIGINT,
completeness JSONB NOT NULL DEFAULT '{}',
item_set_hash TEXT NOT NULL,
held_reason TEXT CHECK (held_reason IS NULL OR held_reason IN ('shrunk','oversize','valve','no_proof','fence_absent','attempts_exhausted','segmented_unsupported','tie')),
held_context JSONB,
proposal JSONB,
proposal_bytes INTEGER,
attempts INTEGER NOT NULL DEFAULT 0,
superseded_by BIGINT,
restore_of BIGINT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
locator TEXT,
activation_id TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS derived_generations_one_live ON derived_generations (source_id, kind, owner_key) WHERE state = 'live';
CREATE UNIQUE INDEX IF NOT EXISTS derived_generations_one_hold ON derived_generations (source_id, kind, owner_key) WHERE state = 'held';
CREATE INDEX IF NOT EXISTS derived_generations_operation ON derived_generations (operation_id);
CREATE INDEX IF NOT EXISTS derived_generations_state_created ON derived_generations (state, created_at);
CREATE TABLE IF NOT EXISTS derived_generation_items (
generation_id BIGINT NOT NULL REFERENCES derived_generations(id) ON DELETE CASCADE,
item_key TEXT NOT NULL,
identity_hash TEXT NOT NULL,
action TEXT NOT NULL CHECK (action IN ('published','retired','revived','kept','adopted')),
preimage JSONB,
preimage_bytes INTEGER,
pending BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (generation_id, item_key)
);
CREATE INDEX IF NOT EXISTS derived_generation_items_key ON derived_generation_items (item_key);
CREATE INDEX IF NOT EXISTS derived_generation_items_pending ON derived_generation_items (generation_id) WHERE pending;
CREATE TABLE IF NOT EXISTS derived_generation_valve (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
kind TEXT NOT NULL,
window_key TEXT NOT NULL,
retired INTEGER NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, kind, window_key)
);
CREATE TABLE IF NOT EXISTS derived_generation_attempts (
hold_id TEXT PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
kind TEXT NOT NULL,
owner_key TEXT NOT NULL,
attempts INTEGER NOT NULL DEFAULT 0,
codes JSONB NOT NULL DEFAULT '[]',
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS derived_generation_attempts_key ON derived_generation_attempts (source_id, kind, owner_key);
CREATE TABLE IF NOT EXISTS derived_generation_activations (
id TEXT PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
kind TEXT NOT NULL,
owner_key TEXT NOT NULL,
expected_generation INTEGER NOT NULL,
input_pin TEXT,
item_set_hash TEXT NOT NULL,
state TEXT NOT NULL CHECK (state IN ('pending','activated')),
generation_id BIGINT REFERENCES derived_generations(id) ON DELETE SET NULL,
members JSONB NOT NULL DEFAULT '[]',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS derived_generation_activations_key ON derived_generation_activations (source_id, kind, owner_key, state);
-- (migration v246) Per-page embedding spend ledger for large pages
-- (page-embedding-budget.ts): held pages wait for approval, capped pages are
-- partially embedded up to embed.max_usd_per_page.
CREATE TABLE IF NOT EXISTS page_embedding_budget (
page_id INTEGER PRIMARY KEY REFERENCES pages(id) ON DELETE CASCADE,
state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'held', 'capped')),
reason TEXT,
embedding_signature TEXT,
spent_usd DOUBLE PRECISION NOT NULL DEFAULT 0 CHECK (spent_usd >= 0),
reserved_usd DOUBLE PRECISION NOT NULL DEFAULT 0 CHECK (reserved_usd >= 0),
reserved_at TIMESTAMPTZ,
unpriced_calls INTEGER NOT NULL DEFAULT 0 CHECK (unpriced_calls >= 0),
cap_usd DOUBLE PRECISION CHECK (cap_usd IS NULL OR cap_usd >= 0),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS page_embedding_budget_state_idx ON page_embedding_budget (state) WHERE state <> 'open';
-- #5876 (migration chronicle_page_state): Life Chronicle ledger, the durable
-- record of each page content's automatic-extraction decision and outcome.
CREATE TABLE IF NOT EXISTS chronicle_page_state (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
content_hash TEXT NOT NULL,
extractor_version INTEGER NOT NULL,
slug TEXT NOT NULL,
state TEXT NOT NULL CHECK (state IN ('pending','skipped','extracted','failed')),
reason TEXT,
trigger TEXT NOT NULL CHECK (trigger IN ('auto','backfill')),
principal_kind TEXT,
principal_id TEXT,
request_id UUID,
no_extract BOOLEAN NOT NULL DEFAULT false,
attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),
next_attempt_at TIMESTAMPTZ,
cost_usd NUMERIC,
unpriced BOOLEAN NOT NULL DEFAULT false,
event_slugs TEXT[] NOT NULL DEFAULT '{}',
event_hashes TEXT[] NOT NULL DEFAULT '{}',
decided_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- #6199 (migration chronicle_campaign_stamps): the --max-usd campaign stamp.
campaign_id TEXT,
attempt_cap_usd NUMERIC,
max_attempts INTEGER CHECK (max_attempts > 0),
pricing_policy TEXT,
campaign_max_usd NUMERIC,
cost_attempts INTEGER[] NOT NULL DEFAULT '{}',
-- #8972 (migration chronicle_judge_window): what the judge read.
judge_input_hash TEXT,
judge_window_chars INTEGER CHECK (judge_window_chars >= 0),
judge_window_hash TEXT,
judge_input_failures INTEGER NOT NULL DEFAULT 0 CHECK (judge_input_failures >= 0),
PRIMARY KEY (source_id, page_id, content_hash, extractor_version)
);
CREATE INDEX IF NOT EXISTS chronicle_page_state_work_idx
ON chronicle_page_state (source_id, state, next_attempt_at);
CREATE INDEX IF NOT EXISTS chronicle_page_state_page_idx ON chronicle_page_state (page_id);
CREATE TABLE IF NOT EXISTS chronicle_judge_reservations (
id BIGSERIAL PRIMARY KEY,
reserved_at TIMESTAMPTZ NOT NULL DEFAULT now(),
source_id TEXT NOT NULL,
page_id INTEGER NOT NULL,
content_hash TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS chronicle_judge_reservations_at_idx ON chronicle_judge_reservations (reserved_at);
-- Durable record that a transcript was synthesized; survives minion_jobs pruning.
CREATE TABLE IF NOT EXISTS dream_synthesis_completions (
source_id TEXT NOT NULL,
idempotency_key TEXT NOT NULL,
completed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, idempotency_key)
);
-- Large pages A7 (migration v247): per-page extraction watermark and the
-- durable receipt of a partial-input extraction (src/core/extraction-watermark.ts).
CREATE TABLE IF NOT EXISTS page_extraction_state (
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
pipeline TEXT NOT NULL CHECK (pipeline IN ('facts_backstop','extract_atoms','conversation_facts')),
pipeline_version INTEGER NOT NULL,
watermark_bytes BIGINT CHECK (watermark_bytes >= 0),
watermark_sha256 TEXT,
input_truncated JSONB,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (page_id, pipeline)
);
CREATE INDEX IF NOT EXISTS page_extraction_state_truncated_idx
ON page_extraction_state (pipeline, page_id) WHERE input_truncated IS NOT NULL;
-- Wave B segmented pages (migration v253): get_page view sizes and digests per
-- page revision and audience (src/core/page-window/segmented.ts).
CREATE TABLE IF NOT EXISTS page_view_digests (
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
revision TEXT NOT NULL,
view TEXT NOT NULL CHECK (view IN ('read','markdown')),
audience TEXT NOT NULL CHECK (audience IN ('trusted','remote')),
total_bytes BIGINT NOT NULL CHECK (total_bytes >= 0),
total_lines BIGINT NOT NULL CHECK (total_lines >= 0),
sha256 TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (page_id, revision, view, audience)
);
-- Wave B segmented page storage (migration v248): segment texts, the
-- generation-ranged body manifest and body stagings (src/core/page-body/).
-- Before the persistence region so the managed-writer guard covers them.
-- BEGIN GENERATED from src/core/page-body/schema.ts (PAGE_BODY_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE SEQUENCE IF NOT EXISTS page_body_occurrence_id_seq;
CREATE TABLE IF NOT EXISTS page_segment_texts (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
sha256 BYTEA NOT NULL CHECK (octet_length(sha256) = 32),
byte_len INTEGER NOT NULL CHECK (byte_len BETWEEN 1 AND 1048576),
line_count INTEGER NOT NULL CHECK (line_count >= 0),
body BYTEA NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, sha256)
);
CREATE TABLE IF NOT EXISTS page_body_manifest (
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
occurrence_id BIGINT NOT NULL,
gen_from BIGINT NOT NULL,
gen_to BIGINT,
seq INTEGER NOT NULL,
region TEXT NOT NULL CHECK (region IN ('truth','timeline')),
source_id TEXT NOT NULL,
sha256 BYTEA NOT NULL,
byte_start BIGINT NOT NULL,
byte_len INTEGER NOT NULL,
line_start BIGINT NOT NULL,
line_count INTEGER NOT NULL,
cut_kind TEXT NOT NULL CHECK (cut_kind IN ('heading','day','message','blank','forced','region_end','eof')),
entry_state JSONB NOT NULL,
protected_regions JSONB NOT NULL DEFAULT '[]',
facts_slots JSONB NOT NULL DEFAULT '[]',
static_proj_bytes BIGINT NOT NULL,
static_proj_lines INTEGER NOT NULL,
outline JSONB NOT NULL DEFAULT '[]',
date_min DATE,
date_max DATE,
hash_state BYTEA NOT NULL,
staging_id UUID,
staging_key UUID GENERATED ALWAYS AS (COALESCE(staging_id, '00000000-0000-0000-0000-000000000000'::uuid)) STORED,
PRIMARY KEY (page_id, occurrence_id, gen_from, staging_key),
FOREIGN KEY (source_id, sha256) REFERENCES page_segment_texts(source_id, sha256)
);
CREATE UNIQUE INDEX IF NOT EXISTS page_body_manifest_live_seq ON page_body_manifest (page_id, seq) WHERE gen_to IS NULL AND staging_id IS NULL;
CREATE INDEX IF NOT EXISTS page_body_manifest_live_bytes ON page_body_manifest (page_id, byte_start) WHERE gen_to IS NULL;
CREATE INDEX IF NOT EXISTS page_body_manifest_text ON page_body_manifest (source_id, sha256);
CREATE INDEX IF NOT EXISTS page_body_manifest_staging ON page_body_manifest (staging_id) WHERE staging_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS page_body_stagings (
id UUID PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN ('upload','import','sync','put','append','edit','resegment')),
principal_kind TEXT NOT NULL,
principal_id TEXT NOT NULL,
client_request_id UUID NOT NULL,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
source_incarnation UUID NOT NULL,
slug TEXT NOT NULL,
page_id INTEGER REFERENCES pages(id) ON DELETE CASCADE,
mode TEXT NOT NULL CHECK (mode IN ('create','replace','append')),
base_revision UUID,
base_generation BIGINT,
declared_bytes BIGINT NOT NULL CHECK (declared_bytes >= 0),
declared_sha256 BYTEA,
received_bytes BIGINT NOT NULL DEFAULT 0,
next_part INTEGER NOT NULL DEFAULT 0,
reserved_bytes BIGINT NOT NULL,
state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open','sealed','prepared','committed','aborted','expired')),
commit_request_id UUID,
idle_expires_at TIMESTAMPTZ NOT NULL,
hard_expires_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (principal_kind, principal_id, client_request_id)
);
CREATE INDEX IF NOT EXISTS page_body_stagings_expiry ON page_body_stagings (hard_expires_at) WHERE state IN ('open','sealed','prepared');
CREATE TABLE IF NOT EXISTS page_body_staging_parts (
staging_id UUID NOT NULL REFERENCES page_body_stagings(id) ON DELETE CASCADE,
part_no INTEGER NOT NULL CHECK (part_no >= 0),
byte_start BIGINT NOT NULL,
byte_len INTEGER NOT NULL CHECK (byte_len BETWEEN 1 AND 4194304),
sha256 BYTEA NOT NULL,
body BYTEA NOT NULL,
PRIMARY KEY (staging_id, part_no)
);
CREATE TABLE IF NOT EXISTS page_body_staging_texts (
staging_id UUID NOT NULL REFERENCES page_body_stagings(id) ON DELETE CASCADE,
source_id TEXT NOT NULL,
sha256 BYTEA NOT NULL,
PRIMARY KEY (staging_id, sha256)
);
CREATE INDEX IF NOT EXISTS page_body_staging_texts_text ON page_body_staging_texts (source_id, sha256);
-- END GENERATED from src/core/page-body/schema.ts (PAGE_BODY_SCHEMA_SQL)
-- Wave B per-segment derivation watermarks (migration v273; src/core/page-body/derivations.ts).
-- BEGIN GENERATED from src/core/page-body/schema.ts (PAGE_SEGMENT_DERIVATIONS_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS page_segment_derivations (
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
pipeline TEXT NOT NULL CHECK (pipeline IN ('links','mentions','timeline','fences','conversation_facts','facts_backstop','synopsis')),
occurrence_id BIGINT NOT NULL,
pipeline_version INTEGER NOT NULL CHECK (pipeline_version >= 0),
segment_sha256 BYTEA NOT NULL CHECK (octet_length(segment_sha256) = 32),
context_hash BYTEA NOT NULL CHECK (octet_length(context_hash) = 32),
state TEXT NOT NULL CHECK (state IN ('done','partial','failed','skipped')),
output JSONB,
receipt JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (page_id, pipeline, occurrence_id)
);
CREATE INDEX IF NOT EXISTS page_segment_derivations_unsettled ON page_segment_derivations (pipeline, page_id) WHERE state IN ('partial','failed');
CREATE TABLE IF NOT EXISTS page_derivation_spend (
page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
family TEXT NOT NULL CHECK (family IN ('facts','synopsis')),
spent_usd DOUBLE PRECISION NOT NULL DEFAULT 0 CHECK (spent_usd >= 0),
reserved_usd DOUBLE PRECISION NOT NULL DEFAULT 0 CHECK (reserved_usd >= 0),
reserved_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (page_id, family)
);
CREATE TABLE IF NOT EXISTS page_segment_derivation_purges (
request_id UUID PRIMARY KEY,
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
needles TEXT[] NOT NULL,
patterns TEXT[] NOT NULL,
page_ids INTEGER[] NOT NULL DEFAULT '{}',
phase TEXT NOT NULL DEFAULT 'sweep' CHECK (phase IN ('sweep','verify','recheck')),
recheck INTEGER[],
found INTEGER NOT NULL DEFAULT 0 CHECK (found >= 0),
cursor_page INTEGER,
cursor_pipeline TEXT,
cursor_occurrence BIGINT,
batch_rows INTEGER CHECK (batch_rows >= 1),
stalled_at TIMESTAMPTZ,
stalled_reason TEXT CHECK (stalled_reason IN ('statement_timeout','paid_row_unreadable')),
pending_reason TEXT CHECK (pending_reason IN ('scope_lock_busy')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS page_segment_derivation_purges_source ON page_segment_derivation_purges (source_id, created_at);
CREATE TABLE IF NOT EXISTS page_segment_derivation_purge_pages (
request_id UUID NOT NULL,
page_id INTEGER NOT NULL,
PRIMARY KEY (request_id, page_id)
);
CREATE OR REPLACE FUNCTION gbrain_mark_derivation_scope_entry() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $func$
DECLARE
src TEXT;
page INTEGER;
BEGIN
IF pg_catalog.row_security_active('page_segment_derivation_purges') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_segment_derivation_purges'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_segment_derivation_purges')::text,
HINT='Give this database role a policy on page_segment_derivation_purges allowing SELECT on the rows of the sources it writes (docs/ENGINES.md#purge-and-withdrawal-guards), or connect as the table owner.';
END IF;
END IF;
IF pg_catalog.row_security_active('page_segment_derivation_purge_pages') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['a']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_segment_derivation_purge_pages'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_segment_derivation_purge_pages')::text,
HINT='Give this database role a policy on page_segment_derivation_purge_pages allowing INSERT on the rows of the sources it writes (docs/ENGINES.md#purge-and-withdrawal-guards), or connect as the table owner.';
END IF;
END IF;
IF TG_TABLE_NAME = 'pages' THEN
src := NEW.source_id;
page := NEW.id;
ELSE
src := OLD.source_id;
page := OLD.page_id;
END IF;
PERFORM pg_advisory_xact_lock_shared(hashtextextended('gbrain:derivation-scope:' || src, 0));
INSERT INTO page_segment_derivation_purge_pages (request_id, page_id)
SELECT x.request_id, page FROM page_segment_derivation_purges x WHERE x.source_id = src ON CONFLICT DO NOTHING;
RETURN NULL;
END
$func$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS page_body_manifest_derivation_scope_retire ON page_body_manifest;
CREATE TRIGGER page_body_manifest_derivation_scope_retire AFTER UPDATE OF gen_to ON page_body_manifest
FOR EACH ROW WHEN (OLD.gen_to IS NULL AND NEW.gen_to IS NOT NULL AND OLD.staging_id IS NULL) EXECUTE FUNCTION gbrain_mark_derivation_scope_entry();
DROP TRIGGER IF EXISTS page_body_manifest_derivation_scope_delete ON page_body_manifest;
CREATE TRIGGER page_body_manifest_derivation_scope_delete AFTER DELETE ON page_body_manifest
FOR EACH ROW WHEN (OLD.gen_to IS NULL AND OLD.staging_id IS NULL) EXECUTE FUNCTION gbrain_mark_derivation_scope_entry();
DROP TRIGGER IF EXISTS pages_derivation_scope_delete ON pages;
CREATE TRIGGER pages_derivation_scope_delete AFTER UPDATE OF deleted_at ON pages
FOR EACH ROW WHEN (OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL) EXECUTE FUNCTION gbrain_mark_derivation_scope_entry();
CREATE OR REPLACE FUNCTION gbrain_refuse_purging_derivation() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $func$
BEGIN
IF pg_catalog.row_security_active('page_segment_derivation_purges') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_segment_derivation_purges'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_segment_derivation_purges')::text,
HINT='Give this database role a policy on page_segment_derivation_purges allowing SELECT on the rows of the sources it writes (docs/ENGINES.md#purge-and-withdrawal-guards), or connect as the table owner.';
END IF;
END IF;
IF pg_catalog.row_security_active('pages') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'pages'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','pages')::text,
HINT='Give this database role a policy on pages allowing SELECT on the rows of the sources it writes (docs/ENGINES.md#purge-and-withdrawal-guards), or connect as the table owner.';
END IF;
END IF;
IF EXISTS (SELECT 1 FROM page_segment_derivation_purges x JOIN pages p ON p.source_id = x.source_id
WHERE p.id = NEW.page_id AND (NEW.page_id = ANY(x.page_ids::int[]) OR p.deleted_at IS NOT NULL OR NOT EXISTS (SELECT 1 FROM page_body_manifest m
WHERE m.page_id = NEW.page_id AND m.occurrence_id = NEW.occurrence_id AND m.gen_to IS NULL AND m.staging_id IS NULL AND m.sha256 = NEW.segment_sha256))
AND CASE WHEN replace(translate(lower(COALESCE(NEW.output::text, '') || ' ' || COALESCE(NEW.receipt::text, '')), 'ς', 'σ'), 'i̇', 'i') LIKE ANY (x.patterns::text[]) THEN EXISTS (SELECT 1 FROM (VALUES (NEW.output), (NEW.receipt)) doc(j),
LATERAL (SELECT jsonb_path_query(doc.j, 'strict $.** ? (@.type() == "string")') #>> '{}' AS t
UNION ALL SELECT jsonb_path_query(doc.j, 'strict $.** ? (@.type() == "object").keyvalue().key') #>> '{}') s
WHERE CASE WHEN replace(translate(lower(s.t), 'ς', 'σ'), 'i̇', 'i') LIKE ANY (x.patterns::text[]) THEN EXISTS (SELECT 1 FROM unnest(x.needles::text[]) k(n)
WHERE strpos(' ' || replace(translate(gbrain_fact_normalize(s.t), 'ς', 'σ'), 'i̇', 'i') || ' ', k.n) > 0) ELSE false END) ELSE false END) THEN
RETURN NULL;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS page_segment_derivations_refuse_purging ON page_segment_derivations;
CREATE TRIGGER page_segment_derivations_refuse_purging BEFORE INSERT OR UPDATE OF output, receipt ON page_segment_derivations
FOR EACH ROW EXECUTE FUNCTION gbrain_refuse_purging_derivation();
-- END GENERATED from src/core/page-body/schema.ts (PAGE_SEGMENT_DERIVATIONS_SQL)
-- BEGIN GENERATED from src/core/page-body/schema.ts (PAGE_BODY_RLS_SQL). Edit that file, then run: bun run build:schema
DO $rls$
DECLARE target TEXT;
BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
FOREACH target IN ARRAY ARRAY['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'] LOOP
IF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = current_schema() AND c.relname = target AND c.relkind = 'r' AND NOT c.relrowsecurity) THEN
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', target);
END IF;
END LOOP;
END IF;
END $rls$;
-- END GENERATED from src/core/page-body/schema.ts (PAGE_BODY_RLS_SQL)
-- Durable concurrent persistence (migration 151).
-- BEGIN GENERATED from src/core/persistence/schema.ts (PERSISTENCE_SCHEMA_STATEMENTS). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS persistence_brain (
singleton integer PRIMARY KEY CHECK (singleton = 1),
brain_id uuid NOT NULL DEFAULT gen_random_uuid(),
enabled boolean NOT NULL DEFAULT false,
activated_at timestamptz
);
INSERT INTO persistence_brain(singleton) VALUES (1) ON CONFLICT DO NOTHING;
CREATE TABLE IF NOT EXISTS persistence_worktrees (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_host_id uuid,
owner_epoch bigint NOT NULL DEFAULT 0,
topology_generation bigint NOT NULL DEFAULT 1,
state text NOT NULL DEFAULT 'active' CHECK (state IN ('active','draining','recovering')),
manifest jsonb,
heartbeat_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS persistence_source_bindings (
source_id text PRIMARY KEY,
source_incarnation uuid NOT NULL,
worktree_id uuid NOT NULL REFERENCES persistence_worktrees(id),
relative_path text NOT NULL DEFAULT '',
topology_generation bigint NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS persistence_host_bindings (
worktree_id uuid NOT NULL REFERENCES persistence_worktrees(id),
host_id uuid NOT NULL,
local_path text NOT NULL,
coordination_path text NOT NULL,
git_durability text CHECK (git_durability IN ('enabled','disabled')),
PRIMARY KEY(worktree_id,host_id)
);
CREATE TABLE IF NOT EXISTS persistence_local_writers (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
lane text NOT NULL CHECK (lane IN ('cli','stdio')),
credential_hash text NOT NULL UNIQUE,
grant_ceiling jsonb NOT NULL,
revoked_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS persistence_counters (
key text PRIMARY KEY,
outstanding_count bigint NOT NULL DEFAULT 0 CHECK (outstanding_count >= 0),
intent_bytes bigint NOT NULL DEFAULT 0 CHECK (intent_bytes >= 0),
lifetime_ids bigint NOT NULL DEFAULT 0 CHECK (lifetime_ids >= 0),
terminal_bytes bigint NOT NULL DEFAULT 0 CHECK (terminal_bytes >= 0),
recovery_bytes bigint NOT NULL DEFAULT 0 CHECK (recovery_bytes >= 0)
);
CREATE TABLE IF NOT EXISTS persistence_requests (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
principal_kind text NOT NULL CHECK (principal_kind IN ('oauth_client','legacy_token','local_cli','local_stdio','application')),
principal_id text NOT NULL,
request_id uuid NOT NULL,
operation text NOT NULL,
source_id text NOT NULL,
source_incarnation uuid NOT NULL,
page_id integer,
slug text NOT NULL,
worktree_id uuid REFERENCES persistence_worktrees(id),
topology_generation bigint,
digest text NOT NULL,
intent jsonb,
authority jsonb NOT NULL,
sequence bigserial NOT NULL UNIQUE,
state text NOT NULL DEFAULT 'queued' CHECK (state IN ('queued','running','recovering','committed','conflict','failed','cancelled')),
execution_token uuid,
claim_expires_at timestamptz,
recovery jsonb,
recovery_bytes bigint NOT NULL DEFAULT 0,
intent_bytes bigint NOT NULL,
terminal_reservation bigint NOT NULL,
outcome jsonb,
error_code text,
error_message text,
blocked_reason text,
compacted boolean NOT NULL DEFAULT false,
publication_started boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
completed_at timestamptz,
UNIQUE(principal_kind,principal_id,request_id)
);
CREATE INDEX IF NOT EXISTS persistence_requests_pending ON persistence_requests(worktree_id,sequence)
WHERE state IN ('queued','running','recovering');
CREATE INDEX IF NOT EXISTS persistence_requests_recovery
ON persistence_requests(worktree_id,sequence) WHERE recovery IS NOT NULL;
CREATE INDEX IF NOT EXISTS persistence_requests_principal ON persistence_requests(principal_kind,principal_id,sequence DESC);
CREATE TABLE IF NOT EXISTS persistence_effects (
id bigserial PRIMARY KEY,
request_id uuid NOT NULL REFERENCES persistence_requests(id),
kind text NOT NULL,
revision uuid,
data jsonb NOT NULL,
state text NOT NULL DEFAULT 'queued' CHECK (state IN ('queued','running','committed','failed')),
execution_token uuid,
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE(request_id,kind)
);
CREATE TABLE IF NOT EXISTS persistence_consumers (
host_id uuid NOT NULL,
pid integer NOT NULL,
nonce text NOT NULL,
pid_ns text,
kind text NOT NULL,
mode text NOT NULL CHECK (mode IN ('probing','full','waiter_only','promoted')),
started_at timestamptz NOT NULL DEFAULT now(),
renewed_at timestamptz NOT NULL DEFAULT now(),
restart_required boolean NOT NULL DEFAULT false,
root_barrier_age_ms integer,
pool jsonb,
host_json_path text NOT NULL,
persistence_home text NOT NULL,
minted_under jsonb,
version text NOT NULL,
boot_id text,
process_start text,
segment_reader boolean,
PRIMARY KEY(host_id,pid,nonce)
);
CREATE OR REPLACE FUNCTION gbrain_require_managed_writer() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $fn$
DECLARE managed boolean; strict_count integer; seen integer; git boolean; unreadable text; target_source text; old_source text; row_data jsonb; old_data jsonb; allowed jsonb;
BEGIN
-- #7636: init always writes the singleton, so a missing row means this role cannot read it (RLS), never "unmanaged".
SELECT enabled INTO managed FROM persistence_brain WHERE singleton=1;
IF NOT FOUND THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','persistence_brain')::text;
END IF;
IF NOT managed THEN
IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
END IF;
IF TG_TABLE_NAME='page_segment_texts' THEN
-- Only the source decides; the segment body (up to 1 MiB of bytea) never becomes JSON.
IF TG_OP='DELETE' THEN row_data := jsonb_build_object('source_id',OLD.source_id); ELSE row_data := jsonb_build_object('source_id',NEW.source_id); END IF;
IF TG_OP='UPDATE' THEN old_data := jsonb_build_object('source_id',OLD.source_id); END IF;
ELSE
row_data := CASE WHEN TG_OP='DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END;
IF TG_OP='UPDATE' THEN old_data := to_jsonb(OLD); END IF;
END IF;
IF TG_TABLE_NAME='pages' AND TG_OP='UPDATE' THEN
IF (NEW.source_id,NEW.slug,NEW.type,NEW.page_kind,NEW.title,NEW.compiled_truth,NEW.timeline,NEW.frontmatter,NEW.deleted_at,NEW.knowledge_revision)
IS NOT DISTINCT FROM
(OLD.source_id,OLD.slug,OLD.type,OLD.page_kind,OLD.title,OLD.compiled_truth,OLD.timeline,OLD.frontmatter,OLD.deleted_at,OLD.knowledge_revision)
-- The trust tier is guarded content (trust/schema.ts); read by key so brains before the column keep working.
AND row_data->'trust_tier' IS NOT DISTINCT FROM old_data->'trust_tier'
-- A segmented page's body identity (page-body/schema.ts); read by key for brains before the column.
AND row_data->'body_generation' IS NOT DISTINCT FROM old_data->'body_generation' THEN RETURN NEW; END IF;
ELSIF TG_TABLE_NAME='sources' THEN
IF TG_OP='UPDATE' AND (NEW.id,NEW.incarnation,NEW.local_path,NEW.archived)
IS NOT DISTINCT FROM (OLD.id,OLD.incarnation,OLD.local_path,OLD.archived) THEN
IF (NEW.last_commit,NEW.last_sync_at,NEW.newest_content_at)
IS NOT DISTINCT FROM (OLD.last_commit,OLD.last_sync_at,OLD.newest_content_at) THEN RETURN NEW; END IF;
allowed := COALESCE(NULLIF(current_setting('gbrain.write_sources',true),''),'[]')::jsonb;
IF NOT (allowed ? NEW.id) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: source checkpoints require canonical owner publication',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:checkpoint',
DETAIL=jsonb_build_object('op',TG_OP,'relationship','checkpoint_outside_owner','target_source',NEW.id,'old_source',OLD.id,'allowed',allowed)::text;
END IF;
RETURN NEW;
END IF;
IF COALESCE(current_setting('gbrain.topology_change',true),'') <> 'on' THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: source topology must be drained and changed through writer administration',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:topology',
DETAIL=jsonb_build_object('op',TG_OP,'relationship','topology_outside_administration','target_source',row_data->>'id','old_source',old_data->>'id')::text;
END IF;
IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
ELSIF TG_TABLE_NAME IN ('facts','takes') AND TG_OP='UPDATE' THEN
row_data := row_data - ARRAY['embedding_model','embedded_text_hash','write_origin'] - ARRAY['write_request_id','write_principal_kind','write_principal_id','last_write_request_id','last_write_principal_kind','last_write_principal_id','last_written_at'];
old_data := old_data - ARRAY['embedding_model','embedded_text_hash','write_origin'] - ARRAY['write_request_id','write_principal_kind','write_principal_id','last_write_request_id','last_write_principal_kind','last_write_principal_id','last_written_at'];
-- Embedding completion, retrieval telemetry, write attribution
-- (attribution-schema.ts; a journal backfill fills it) and the write origin
-- record are physical projections. The trust tier is not: it stays guarded.
IF (row_data - ARRAY['embedding','embedded_at','last_retrieved_at','retrieval_count','updated_at'])
= (old_data - ARRAY['embedding','embedded_at','last_retrieved_at','retrieval_count','updated_at']) THEN RETURN NEW; END IF;
END IF;
IF TG_TABLE_NAME NOT IN ('tags','timeline_entries','takes') THEN
target_source := row_data->>'source_id'; old_source := old_data->>'source_id';
END IF;
IF target_source IS NULL THEN
SELECT source_id INTO target_source FROM pages WHERE id=(row_data->>'page_id')::integer;
IF NOT FOUND AND row_security_active('pages') THEN unreadable := 'pages'; END IF;
END IF;
IF TG_OP='UPDATE' AND old_source IS NULL THEN
SELECT source_id INTO old_source FROM pages WHERE id=(old_data->>'page_id')::integer;
IF NOT FOUND AND row_security_active('pages') THEN unreadable := 'pages'; END IF;
END IF;
-- A page row-level security hides is not a deleted parent (a cascade runs as the table owner, outside RLS).
IF unreadable IS NOT NULL THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state',unreadable)::text;
END IF;
-- Cascaded projection removal after the already-guarded parent deletion.
IF target_source IS NULL AND TG_OP='DELETE' THEN RETURN OLD; END IF;
allowed := COALESCE(NULLIF(current_setting('gbrain.write_sources',true),''),'[]')::jsonb;
IF target_source IS NULL OR NOT (allowed ? target_source) OR (old_source IS NOT NULL AND NOT (allowed ? old_source)) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: canonical writer must use the persistence coordinator',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:allowlist',
DETAIL=jsonb_build_object('op',TG_OP,'relationship',CASE WHEN target_source IS NULL THEN 'missing_source'
WHEN NOT (allowed ? target_source) THEN 'different_source' ELSE 'old_source_outside' END,
'target_source',target_source,'old_source',old_source,'allowed',allowed)::text;
END IF;
-- G7a (git-canonical-schema.ts): a git-canonical source takes rows only from a writer that declares the git protocol.
IF COALESCE(current_setting('gbrain.git_protocol',true),'') <> '1' THEN
SELECT strict_sources INTO strict_count FROM git_canonical_brain WHERE singleton=1;
IF NOT FOUND THEN unreadable := 'git_canonical_brain';
ELSIF strict_count>0 THEN
SELECT count(*), COALESCE(bool_or(config->>'canonical'='git'),false) INTO seen, git FROM sources WHERE id IN (target_source, old_source);
IF seen < (CASE WHEN old_source IS NULL OR old_source=target_source THEN 1 ELSE 2 END) AND row_security_active('sources') THEN
unreadable := 'sources';
ELSIF git THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='git_protocol_required: this source is git-canonical and the writer does not declare the git protocol',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='git_canonical_fence:mutation',
DETAIL=jsonb_build_object('op',TG_OP,'target_source',target_source,'old_source',old_source)::text;
END IF;
END IF;
IF unreadable IS NOT NULL THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state',unreadable)::text;
END IF;
END IF;
IF TG_OP='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
END $fn$;
DO $body$
DECLARE target text;
BEGIN
FOREACH target IN ARRAY ARRAY['pages','tags','slug_aliases','page_aliases','facts','takes','timeline_entries','sources','page_segment_texts','page_body_manifest'] LOOP
IF to_regclass(target) IS NOT NULL THEN
EXECUTE format('DROP TRIGGER IF EXISTS managed_writer_guard ON %I',target);
EXECUTE format('CREATE TRIGGER managed_writer_guard BEFORE INSERT OR UPDATE OR DELETE ON %I FOR EACH ROW EXECUTE FUNCTION gbrain_require_managed_writer()',target);
END IF;
END LOOP;
END $body$;
;
-- END GENERATED from src/core/persistence/schema.ts (PERSISTENCE_SCHEMA_STATEMENTS)
-- BEGIN GENERATED from src/core/persistence/git-canonical-schema.ts (GIT_CANONICAL_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS git_canonical_brain (
singleton INTEGER PRIMARY KEY DEFAULT 1 CHECK (singleton = 1),
strict_sources INTEGER NOT NULL DEFAULT 0 CHECK (strict_sources >= 0)
);
CREATE OR REPLACE FUNCTION gbrain_count_git_canonical_sources() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $fn$
BEGIN
-- #7636: a count over the rows row-level security lets this role see would undercount and could switch G7a off.
IF row_security_active('sources') THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','sources')::text;
END IF;
INSERT INTO git_canonical_brain(singleton, strict_sources)
VALUES (1, (SELECT count(*) FROM sources WHERE config->>'canonical' = 'git'))
ON CONFLICT (singleton) DO UPDATE SET strict_sources = EXCLUDED.strict_sources;
RETURN NULL;
END $fn$;
DROP TRIGGER IF EXISTS git_canonical_sources_count_insert ON sources;
CREATE TRIGGER git_canonical_sources_count_insert AFTER INSERT ON sources
FOR EACH ROW WHEN (NEW.config->>'canonical' = 'git') EXECUTE FUNCTION gbrain_count_git_canonical_sources();
DROP TRIGGER IF EXISTS git_canonical_sources_count_update ON sources;
CREATE TRIGGER git_canonical_sources_count_update AFTER UPDATE ON sources
FOR EACH ROW WHEN (OLD.config->>'canonical' IS DISTINCT FROM NEW.config->>'canonical') EXECUTE FUNCTION gbrain_count_git_canonical_sources();
DROP TRIGGER IF EXISTS git_canonical_sources_count_delete ON sources;
CREATE TRIGGER git_canonical_sources_count_delete AFTER DELETE ON sources
FOR EACH ROW WHEN (OLD.config->>'canonical' = 'git') EXECUTE FUNCTION gbrain_count_git_canonical_sources();
INSERT INTO git_canonical_brain(singleton, strict_sources)
VALUES (1, (SELECT count(*) FROM sources WHERE config->>'canonical' = 'git'))
ON CONFLICT (singleton) DO UPDATE SET strict_sources = EXCLUDED.strict_sources;
DO $rls$
BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper))
AND EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = current_schema() AND c.relname = 'git_canonical_brain' AND c.relkind = 'r' AND NOT c.relrowsecurity) THEN
ALTER TABLE git_canonical_brain ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/persistence/git-canonical-schema.ts (GIT_CANONICAL_SCHEMA_SQL)
-- Verified text projection work (migration 153).
-- BEGIN GENERATED from src/core/page-state/projection-schema.ts (PAGE_PROJECTION_SCHEMA_SQL). Edit that file, then run: bun run build:schema
DROP TRIGGER IF EXISTS trg_pages_search_vector ON pages;
DO $trg$ BEGIN
EXECUTE format('CREATE TRIGGER trg_pages_search_vector BEFORE INSERT OR UPDATE OF %s ON pages FOR EACH ROW EXECUTE FUNCTION update_page_search_vector()',
CASE WHEN EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'pages'::regclass AND attname = 'timeline_lead' AND NOT attisdropped)
THEN 'title,timeline,timeline_lead' ELSE 'title,timeline' END);
END $trg$;
CREATE TABLE IF NOT EXISTS page_projection_jobs (
source_incarnation UUID NOT NULL REFERENCES sources(incarnation) ON DELETE CASCADE,
slug TEXT NOT NULL,
revision UUID NOT NULL,
reason TEXT NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY(source_incarnation,slug)
);
CREATE OR REPLACE FUNCTION gbrain_queue_page_projection() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $fn$
DECLARE incarnation UUID;
BEGIN
IF pg_catalog.row_security_active('sources') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'sources'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','sources')::text;
END IF;
END IF;
IF TG_OP='DELETE' THEN
IF pg_catalog.row_security_active('page_projection_jobs') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r','d']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_projection_jobs'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_projection_jobs')::text;
END IF;
END IF;
DELETE FROM page_projection_jobs j USING sources s
WHERE s.id=OLD.source_id AND j.source_incarnation=s.incarnation AND j.slug=OLD.slug;
RETURN NULL;
END IF;
IF TG_OP='UPDATE' AND (OLD.source_id,OLD.slug) IS DISTINCT FROM (NEW.source_id,NEW.slug) THEN
IF pg_catalog.row_security_active('page_projection_jobs') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r','d']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_projection_jobs'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_projection_jobs')::text;
END IF;
END IF;
DELETE FROM page_projection_jobs j USING sources s
WHERE s.id=OLD.source_id AND j.source_incarnation=s.incarnation AND j.slug=OLD.slug;
END IF;
SELECT s.incarnation INTO incarnation FROM sources s WHERE s.id=NEW.source_id;
IF NEW.deleted_at IS NOT NULL OR NEW.text_projection_revision=NEW.knowledge_revision THEN
IF pg_catalog.row_security_active('page_projection_jobs') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r','d']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_projection_jobs'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_projection_jobs')::text;
END IF;
END IF;
DELETE FROM page_projection_jobs j WHERE j.source_incarnation=incarnation AND j.slug=NEW.slug;
ELSIF TG_OP='INSERT' OR NEW.knowledge_revision IS DISTINCT FROM OLD.knowledge_revision THEN
IF pg_catalog.row_security_active('page_projection_jobs') THEN
IF EXISTS (SELECT 1 FROM pg_catalog.unnest(ARRAY['r','a','w']) AS needed(cmd) WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_policy p WHERE p.polrelid = 'page_projection_jobs'::regclass::oid AND p.polpermissive AND p.polcmd::text IN ('*', needed.cmd)
AND EXISTS (SELECT 1 FROM pg_catalog.unnest(p.polroles) AS r(id) WHERE CASE WHEN r.id = 0::oid THEN true ELSE pg_catalog.pg_has_role(r.id, 'USAGE') END))) THEN
RAISE EXCEPTION USING ERRCODE='P0001', MESSAGE='writer_coordinator_required: writer state is unreadable to this database role',
TABLE=TG_TABLE_NAME, SCHEMA=TG_TABLE_SCHEMA, CONSTRAINT='managed_writer_guard:unreadable_state',
DETAIL=pg_catalog.jsonb_build_object('op',TG_OP,'relationship','unreadable_state','state','page_projection_jobs')::text;
END IF;
END IF;
INSERT INTO page_projection_jobs(source_incarnation,slug,revision,reason)
VALUES (incarnation,NEW.slug,NEW.knowledge_revision,'canonical_change')
ON CONFLICT(source_incarnation,slug) DO UPDATE SET revision=EXCLUDED.revision,reason=EXCLUDED.reason,updated_at=now();
END IF;
RETURN NULL;
END $fn$;
DROP TRIGGER IF EXISTS pages_projection_queue ON pages;
CREATE TRIGGER pages_projection_queue AFTER INSERT OR UPDATE OR DELETE ON pages
FOR EACH ROW EXECUTE FUNCTION gbrain_queue_page_projection();
-- END GENERATED from src/core/page-state/projection-schema.ts (PAGE_PROJECTION_SCHEMA_SQL)
-- BEGIN GENERATED from src/core/persistence/topology-schema.ts (PERSISTENCE_TOPOLOGY_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS persistence_topology_changes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
principal_id uuid NOT NULL,
request_id uuid NOT NULL,
digest text NOT NULL,
operation text NOT NULL,
source_id text NOT NULL,
source_incarnation uuid,
worktree_ids uuid[] NOT NULL DEFAULT '{}',
state text NOT NULL CHECK(state IN ('recovering','committed','failed')),
recovery jsonb,
recovery_bytes bigint NOT NULL DEFAULT 0 CHECK(recovery_bytes>=0),
intent_bytes bigint NOT NULL DEFAULT 0 CHECK(intent_bytes>=0),
terminal_bytes bigint NOT NULL DEFAULT 2048 CHECK(terminal_bytes>=0),
outcome jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE(principal_id,request_id)
);
ALTER TABLE persistence_topology_changes ADD COLUMN IF NOT EXISTS intent_bytes bigint NOT NULL DEFAULT 0 CHECK(intent_bytes>=0);
CREATE INDEX IF NOT EXISTS persistence_topology_recovering ON persistence_topology_changes(created_at) WHERE state='recovering';
-- END GENERATED from src/core/persistence/topology-schema.ts (PERSISTENCE_TOPOLOGY_SCHEMA_SQL)
-- BEGIN GENERATED from src/core/persistence/worktree-refresh-schema.ts (WORKTREE_REFRESH_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS persistence_worktree_refreshes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
worktree_id uuid NOT NULL REFERENCES persistence_worktrees(id),
source_ids text[] NOT NULL,
principal_id uuid NOT NULL,
owner_epoch bigint NOT NULL,
topology_generation bigint NOT NULL,
state text NOT NULL CHECK (state IN ('draining','fenced','merged','syncing','completed','aborted','recovery_required')),
old_head text NOT NULL,
target_head text NOT NULL,
upstream_ref text NOT NULL,
preserved_uncommitted text[] NOT NULL DEFAULT '{}',
outcome jsonb NOT NULL DEFAULT '{}',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
completed_at timestamptz
);
CREATE UNIQUE INDEX IF NOT EXISTS persistence_worktree_refreshes_active
ON persistence_worktree_refreshes(worktree_id)
WHERE state IN ('draining','fenced','merged','syncing','recovery_required');
CREATE INDEX IF NOT EXISTS persistence_worktree_refreshes_completed
ON persistence_worktree_refreshes(worktree_id, completed_at DESC) WHERE state='completed';
-- END GENERATED from src/core/persistence/worktree-refresh-schema.ts (WORKTREE_REFRESH_SCHEMA_SQL)
-- BEGIN GENERATED from src/core/company-brain/receipt-schema.ts (SOURCE_INGESTION_RECEIPTS_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS source_ingestion_receipts (
id uuid PRIMARY KEY,
source_id text NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
source_incarnation uuid NOT NULL REFERENCES sources(incarnation) ON DELETE CASCADE,
approved_revision text NOT NULL CHECK (approved_revision ~ '^([a-f0-9]{40}|[a-f0-9]{64})$'),
profile text NOT NULL CHECK (length(profile) BETWEEN 1 AND 128),
schema_fingerprint text NOT NULL CHECK (schema_fingerprint ~ '^[a-f0-9]{64}$'),
extractor_version text NOT NULL CHECK (length(extractor_version) BETWEEN 1 AND 128),
revision integer NOT NULL DEFAULT 1 CHECK (revision > 0),
phase text NOT NULL DEFAULT 'ADMITTED' CHECK (phase IN ('ADMITTED','CONTENT','GRAPH','VERIFY','COMPLETE')),
outcome text NOT NULL DEFAULT 'incomplete' CHECK (outcome IN ('incomplete','complete','discarded')),
counts jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(counts) = 'object' AND octet_length(counts::text) <= 4096),
lifecycle_request_ids uuid[] NOT NULL DEFAULT '{}' CHECK (cardinality(lifecycle_request_ids) <= 128),
checkpoint_refs jsonb NOT NULL DEFAULT '[]'::jsonb CHECK (jsonb_typeof(checkpoint_refs) = 'array' AND jsonb_array_length(checkpoint_refs) <= 32 AND octet_length(checkpoint_refs::text) <= 16384),
diagnostic text CHECK (diagnostic IN ('interrupted','content_incomplete','graph_incomplete','verification_failed','pending_writes','source_changed','checkpoint_missing','operation_failed')),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
completed_at timestamptz,
discarded_at timestamptz,
CHECK ((phase = 'COMPLETE') = (outcome = 'complete')),
CHECK ((completed_at IS NOT NULL) = (outcome = 'complete')),
CHECK ((discarded_at IS NOT NULL) = (outcome = 'discarded'))
);
ALTER TABLE source_ingestion_receipts ADD COLUMN IF NOT EXISTS policy_fingerprint text
CHECK (policy_fingerprint ~ '^[a-f0-9]{64}$');
CREATE INDEX IF NOT EXISTS source_ingestion_receipts_source
ON source_ingestion_receipts(source_id, source_incarnation, created_at DESC);
CREATE INDEX IF NOT EXISTS source_ingestion_receipts_retention
ON source_ingestion_receipts(source_id, source_incarnation, completed_at DESC, id DESC) WHERE outcome = 'complete';
CREATE INDEX IF NOT EXISTS source_ingestion_receipts_active
ON source_ingestion_receipts(id) WHERE outcome = 'incomplete';
-- END GENERATED from src/core/company-brain/receipt-schema.ts (SOURCE_INGESTION_RECEIPTS_SCHEMA_SQL)
-- BEGIN GENERATED from src/core/shared-skills/schema-all.ts (SHARED_SKILLS_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS shared_skill_state (
singleton INTEGER PRIMARY KEY CHECK(singleton=1),
token_secret TEXT NOT NULL DEFAULT (replace(gen_random_uuid()::text,'-','') || replace(gen_random_uuid()::text,'-','')),
serving_epoch UUID NOT NULL DEFAULT gen_random_uuid()
);
INSERT INTO shared_skill_state(singleton) VALUES(1) ON CONFLICT DO NOTHING;
CREATE TABLE IF NOT EXISTS shared_skill_policies (
source_id TEXT NOT NULL, source_incarnation UUID NOT NULL,
epoch UUID NOT NULL DEFAULT gen_random_uuid(), policy JSONB NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY(source_id,source_incarnation)
);
CREATE TABLE IF NOT EXISTS shared_skill_packs (
source_id TEXT NOT NULL, source_incarnation UUID NOT NULL, pack_id TEXT NOT NULL,
revision UUID NOT NULL, manifest JSONB NOT NULL, manifest_hash TEXT NOT NULL,
PRIMARY KEY(source_id,source_incarnation),
UNIQUE(source_id,source_incarnation,pack_id)
);
CREATE TABLE IF NOT EXISTS shared_skill_policy_audit (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), source_id TEXT NOT NULL, source_incarnation UUID NOT NULL,
principal_kind TEXT NOT NULL, principal_id TEXT NOT NULL, previous_epoch TEXT NOT NULL,
epoch UUID NOT NULL, policy JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS shared_skill_policy_audit_source_idx ON shared_skill_policy_audit(source_id,source_incarnation,created_at);
CREATE TABLE IF NOT EXISTS shared_skill_heads (
source_id TEXT NOT NULL, source_incarnation UUID NOT NULL, pack_id TEXT NOT NULL, name TEXT NOT NULL,
revision UUID NOT NULL, metadata JSONB NOT NULL, deleted BOOLEAN NOT NULL DEFAULT false,
policy_epoch TEXT NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY(source_id,source_incarnation,pack_id,name)
);
CREATE TABLE IF NOT EXISTS shared_skill_revisions (
source_id TEXT NOT NULL, source_incarnation UUID NOT NULL, pack_id TEXT NOT NULL, name TEXT NOT NULL,
revision UUID NOT NULL, metadata JSONB NOT NULL, files JSONB NOT NULL,
deleted BOOLEAN NOT NULL DEFAULT false, policy_epoch TEXT NOT NULL,
request_id UUID NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
stored_bytes BIGINT GENERATED ALWAYS AS (octet_length(files::text)+octet_length(metadata::text)) STORED,
PRIMARY KEY(source_id,source_incarnation,pack_id,name,revision)
);
CREATE INDEX IF NOT EXISTS shared_skill_revision_request_idx ON shared_skill_revisions(request_id);
CREATE INDEX IF NOT EXISTS shared_skill_heads_active_idx ON shared_skill_heads(source_id,source_incarnation,pack_id,name) WHERE NOT deleted;
CREATE INDEX IF NOT EXISTS shared_skill_revision_retention_idx ON shared_skill_revisions(source_id,source_incarnation,name,created_at DESC);
CREATE INDEX IF NOT EXISTS shared_skill_revision_uuid_idx ON shared_skill_revisions(revision);
CREATE TABLE IF NOT EXISTS shared_skill_revision_leases (
lease_kind TEXT NOT NULL CHECK(lease_kind IN ('delivery','pin')), lease_id UUID NOT NULL,
source_id TEXT NOT NULL, source_incarnation UUID NOT NULL, pack_id TEXT NOT NULL, name TEXT NOT NULL, revision UUID NOT NULL,
principal_kind TEXT NOT NULL, principal_id TEXT NOT NULL, expires_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY(lease_kind,lease_id,source_id,source_incarnation,pack_id,name,revision),
FOREIGN KEY(source_id,source_incarnation,pack_id,name,revision)
REFERENCES shared_skill_revisions(source_id,source_incarnation,pack_id,name,revision) ON DELETE RESTRICT
);
CREATE INDEX IF NOT EXISTS shared_skill_revision_lease_expiry_idx ON shared_skill_revision_leases(source_id,source_incarnation,expires_at);
CREATE INDEX IF NOT EXISTS shared_skill_revision_lease_target_idx ON shared_skill_revision_leases(source_id,source_incarnation,pack_id,name,revision,expires_at);
CREATE TABLE IF NOT EXISTS shared_skill_members (
installation_id UUID PRIMARY KEY,
principal_kind TEXT NOT NULL,
principal_id TEXT NOT NULL,
adapter TEXT NOT NULL,
brain_id UUID NOT NULL,
epoch BIGINT NOT NULL DEFAULT 1,
active BOOLEAN NOT NULL DEFAULT true,
follow_policy JSONB NOT NULL,
issued_sequence BIGINT NOT NULL DEFAULT 0,
acknowledged_sequence BIGINT NOT NULL DEFAULT 0,
desired_view TEXT,
acknowledged_view TEXT,
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
join_window TIMESTAMPTZ NOT NULL DEFAULT now(),
join_count INTEGER NOT NULL DEFAULT 1,
UNIQUE(principal_kind, principal_id, adapter)
);
CREATE TABLE IF NOT EXISTS shared_skill_delivery_batches (
token UUID PRIMARY KEY,
installation_id UUID NOT NULL REFERENCES shared_skill_members(installation_id) ON DELETE CASCADE,
epoch BIGINT NOT NULL,
sequence BIGINT NOT NULL,
view_token TEXT NOT NULL,
authority_digest TEXT NOT NULL,
revisions JSONB NOT NULL,
issued_at TIMESTAMPTZ NOT NULL DEFAULT now(),
acknowledged_at TIMESTAMPTZ,
evidence JSONB,
UNIQUE(installation_id, epoch, sequence)
);
CREATE INDEX IF NOT EXISTS shared_skill_delivery_member_idx ON shared_skill_delivery_batches(installation_id, epoch, issued_at);
ALTER TABLE persistence_brain ADD COLUMN IF NOT EXISTS writer_protocol_floor integer NOT NULL DEFAULT 1 CHECK (writer_protocol_floor IN (1,2));
ALTER TABLE persistence_brain ADD COLUMN IF NOT EXISTS skill_bundles_enabled boolean NOT NULL DEFAULT false;
ALTER TABLE persistence_requests ADD COLUMN IF NOT EXISTS target_kind text NOT NULL DEFAULT 'page' CHECK (target_kind IN ('page','skill_bundle'));
ALTER TABLE persistence_requests ADD COLUMN IF NOT EXISTS protocol_version integer NOT NULL DEFAULT 1 CHECK (protocol_version IN (1,2));
CREATE TABLE IF NOT EXISTS persistence_writer_protocols (
worktree_id uuid NOT NULL REFERENCES persistence_worktrees(id),
host_id uuid NOT NULL,
owner_epoch bigint NOT NULL,
protocol_version integer NOT NULL CHECK (protocol_version=2),
registered_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY(worktree_id,host_id)
);
CREATE OR REPLACE FUNCTION gbrain_require_persistence_protocol(required integer) RETURNS void LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $$
BEGIN
IF required >= 2 AND COALESCE(current_setting('gbrain.persistence_protocol',true),'') <> '2' THEN
RAISE EXCEPTION 'writer_upgrade_required: this mutation requires persistence protocol 2' USING ERRCODE='42501';
END IF;
END $$;
CREATE OR REPLACE FUNCTION gbrain_guard_request_protocol() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $$
DECLARE target text; version integer; floor integer; active boolean; record jsonb;
BEGIN
IF TG_OP='DELETE' THEN target:=OLD.target_kind; version:=OLD.protocol_version;
ELSE target:=NEW.target_kind; version:=NEW.protocol_version; END IF;
SELECT writer_protocol_floor,skill_bundles_enabled INTO floor,active FROM persistence_brain WHERE singleton=1 FOR SHARE;
IF NOT FOUND THEN RAISE EXCEPTION 'writer_coordinator_required: writer state is unreadable to this database role' USING ERRCODE='42501'; END IF;
PERFORM gbrain_require_persistence_protocol(GREATEST(floor,version,CASE WHEN target='skill_bundle' THEN 2 ELSE 1 END));
IF TG_OP='DELETE' THEN RETURN OLD; END IF;
IF TG_OP='UPDATE' AND (NEW.target_kind<>OLD.target_kind OR NEW.protocol_version<>OLD.protocol_version) THEN
RAISE EXCEPTION 'unsupported_mutation_protocol: a request target is immutable' USING ERRCODE='42501';
END IF;
IF NEW.operation IN ('put_skill','delete_skill','adopt_skillpack') AND target<>'skill_bundle' THEN
RAISE EXCEPTION 'unsupported_mutation_protocol: skill operations require a typed target' USING ERRCODE='42501';
END IF;
IF target='skill_bundle' THEN
IF version<>2 OR NEW.page_id IS NOT NULL OR NEW.worktree_id IS NULL THEN
RAISE EXCEPTION 'unsupported_mutation_protocol: invalid skill target' USING ERRCODE='42501';
END IF;
IF TG_OP='INSERT' AND NOT active THEN
RAISE EXCEPTION 'writer_not_quiesced: shared publication is disabled' USING ERRCODE='42501';
END IF;
IF (TG_OP='INSERT' OR (NEW.state='running' AND OLD.state IS DISTINCT FROM 'running')) AND NOT EXISTS (SELECT 1 FROM persistence_worktrees w JOIN persistence_writer_protocols p
ON p.worktree_id=w.id AND p.host_id=w.owner_host_id AND p.owner_epoch=w.owner_epoch AND p.protocol_version=2
WHERE w.id=NEW.worktree_id AND w.state='active') THEN
RAISE EXCEPTION 'writer_not_quiesced: canonical owner capability must be revalidated' USING ERRCODE='42501';
END IF;
ELSIF version<>1 THEN
RAISE EXCEPTION 'unsupported_mutation_protocol: invalid page target' USING ERRCODE='42501';
END IF;
record:=NEW.recovery;
IF record IS NOT NULL AND ((target='page' AND record->>'version' IS DISTINCT FROM '1'
AND NOT (record->>'version'='3' AND record->>'target'='pages' AND jsonb_typeof(record->'files')='array'))
OR (target='skill_bundle' AND (record->>'version' IS DISTINCT FROM '2' OR record->>'target' IS DISTINCT FROM 'skill_bundle'
OR jsonb_typeof(record->'files') IS DISTINCT FROM 'array'))) THEN
RAISE EXCEPTION 'unsupported_mutation_protocol: recovery target mismatch' USING ERRCODE='42501';
END IF;
RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS gbrain_request_protocol ON persistence_requests;
CREATE TRIGGER gbrain_request_protocol BEFORE INSERT OR UPDATE OR DELETE ON persistence_requests
FOR EACH ROW EXECUTE FUNCTION gbrain_guard_request_protocol();
CREATE OR REPLACE FUNCTION gbrain_guard_effect_protocol() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $$
DECLARE floor integer;
BEGIN
SELECT writer_protocol_floor INTO floor FROM persistence_brain WHERE singleton=1 FOR SHARE;
IF NOT FOUND THEN RAISE EXCEPTION 'writer_coordinator_required: writer state is unreadable to this database role' USING ERRCODE='42501'; END IF;
PERFORM gbrain_require_persistence_protocol(floor);
IF TG_OP='DELETE' THEN RETURN OLD; END IF;
RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS gbrain_effect_protocol ON persistence_effects;
CREATE TRIGGER gbrain_effect_protocol BEFORE INSERT OR UPDATE OR DELETE ON persistence_effects
FOR EACH ROW EXECUTE FUNCTION gbrain_guard_effect_protocol();
CREATE OR REPLACE FUNCTION gbrain_guard_protocol_activation() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $$
BEGIN
IF NEW.writer_protocol_floor<OLD.writer_protocol_floor THEN
RAISE EXCEPTION 'writer_upgrade_required: the protocol floor cannot be lowered' USING ERRCODE='42501';
END IF;
IF NEW.writer_protocol_floor>OLD.writer_protocol_floor OR (NEW.skill_bundles_enabled AND NOT OLD.skill_bundles_enabled) THEN
PERFORM gbrain_require_persistence_protocol(2);
IF row_security_active('persistence_requests') OR row_security_active('persistence_effects') OR row_security_active('persistence_worktrees')
OR row_security_active('persistence_source_bindings') OR row_security_active('sources') THEN
RAISE EXCEPTION 'writer_coordinator_required: writer state is unreadable to this database role' USING ERRCODE='42501';
END IF;
IF COALESCE(current_setting('gbrain.writer_quiesced',true),'')<>'true' OR NOT NEW.enabled OR NEW.writer_protocol_floor<>2
OR EXISTS (SELECT 1 FROM persistence_requests WHERE state IN ('queued','deferred','running','recovering') OR recovery IS NOT NULL)
OR EXISTS (SELECT 1 FROM persistence_effects WHERE state='running' OR recovery IS NOT NULL)
OR EXISTS (SELECT 1 FROM persistence_worktrees w WHERE EXISTS
(SELECT 1 FROM persistence_source_bindings b JOIN sources s ON s.id=b.source_id AND s.incarnation=b.source_incarnation
WHERE b.worktree_id=w.id AND NOT s.archived) AND (w.state<>'active' OR NOT EXISTS
(SELECT 1 FROM persistence_writer_protocols p WHERE p.worktree_id=w.id AND p.host_id=w.owner_host_id
AND p.owner_epoch=w.owner_epoch AND p.protocol_version=2))) THEN
RAISE EXCEPTION 'writer_not_quiesced: drain and verify all canonical owners before activation' USING ERRCODE='42501';
END IF;
END IF;
RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS gbrain_protocol_activation ON persistence_brain;
CREATE TRIGGER gbrain_protocol_activation BEFORE UPDATE ON persistence_brain
FOR EACH ROW EXECUTE FUNCTION gbrain_guard_protocol_activation();
CREATE OR REPLACE FUNCTION gbrain_guard_skill_publication() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $$
DECLARE permitted jsonb;
BEGIN
PERFORM gbrain_require_persistence_protocol(2);
IF NOT EXISTS (SELECT 1 FROM persistence_brain WHERE singleton=1 AND enabled AND skill_bundles_enabled AND writer_protocol_floor=2) THEN
RAISE EXCEPTION 'writer_not_quiesced: shared publication is disabled' USING ERRCODE='42501';
END IF;
BEGIN permitted:=COALESCE(NULLIF(current_setting('gbrain.write_sources',true),''),'[]')::jsonb;
EXCEPTION WHEN OTHERS THEN permitted:='[]'::jsonb; END;
IF jsonb_typeof(permitted)<>'array'
OR (TG_OP<>'INSERT' AND NOT permitted @> jsonb_build_array(OLD.source_id))
OR (TG_OP<>'DELETE' AND NOT permitted @> jsonb_build_array(NEW.source_id)) THEN
RAISE EXCEPTION 'writer_coordinator_required: canonical skill writes require a coordinated source capability' USING ERRCODE='42501';
END IF;
IF TG_OP='DELETE' THEN RETURN OLD; END IF;
RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS gbrain_skill_publication ON shared_skill_packs;
CREATE TRIGGER gbrain_skill_publication BEFORE INSERT OR UPDATE OR DELETE ON shared_skill_packs
FOR EACH ROW EXECUTE FUNCTION gbrain_guard_skill_publication();
DROP TRIGGER IF EXISTS gbrain_skill_publication ON shared_skill_heads;
CREATE TRIGGER gbrain_skill_publication BEFORE INSERT OR UPDATE OR DELETE ON shared_skill_heads
FOR EACH ROW EXECUTE FUNCTION gbrain_guard_skill_publication();
DROP TRIGGER IF EXISTS gbrain_skill_publication ON shared_skill_revisions;
CREATE TRIGGER gbrain_skill_publication BEFORE INSERT OR UPDATE OR DELETE ON shared_skill_revisions
FOR EACH ROW EXECUTE FUNCTION gbrain_guard_skill_publication();
CREATE OR REPLACE FUNCTION gbrain_lease_shared_skill_delivery() RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public, pg_temp AS $$
DECLARE item text; found_revision shared_skill_revisions%ROWTYPE; brain text;
BEGIN
SELECT brain_id::text INTO brain FROM persistence_brain WHERE singleton=1;
FOR item IN SELECT value FROM jsonb_array_elements_text(NEW.revisions) ORDER BY value LOOP
SELECT r.* INTO found_revision FROM shared_skill_revisions r
WHERE r.revision::text=substring(item from '@([^@]+)$')
AND item=brain || '/' || r.source_id || '/' || r.source_incarnation::text || '/' || r.pack_id || '/' || r.name || '@' || r.revision::text
FOR KEY SHARE;
IF NOT FOUND THEN RAISE EXCEPTION 'revision_unavailable: delivery revision is no longer retained' USING ERRCODE='23503'; END IF;
INSERT INTO shared_skill_revision_leases(lease_kind,lease_id,source_id,source_incarnation,pack_id,name,revision,principal_kind,principal_id,expires_at)
VALUES('delivery',found_revision.revision,found_revision.source_id,found_revision.source_incarnation,found_revision.pack_id,found_revision.name,found_revision.revision,
'application','delivery',LEAST(NEW.issued_at+interval '24 hours',now()+interval '24 hours'))
ON CONFLICT(lease_kind,lease_id,source_id,source_incarnation,pack_id,name,revision)
DO UPDATE SET expires_at=GREATEST(shared_skill_revision_leases.expires_at,excluded.expires_at);
END LOOP;
RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS shared_skill_delivery_lease ON shared_skill_delivery_batches;
CREATE TRIGGER shared_skill_delivery_lease AFTER INSERT OR UPDATE OF revisions ON shared_skill_delivery_batches
FOR EACH ROW EXECUTE FUNCTION gbrain_lease_shared_skill_delivery();
DO $$
DECLARE has_bypass boolean; target text;
BEGIN
SELECT EXISTS(SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user,r.oid,'USAGE')
AND (r.rolbypassrls OR r.rolsuper)) INTO has_bypass;
IF has_bypass THEN
FOREACH target IN ARRAY ARRAY['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'] LOOP
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY',target);
END LOOP;
END IF;
END $$;
-- END GENERATED from src/core/shared-skills/schema-all.ts (SHARED_SKILLS_SCHEMA_SQL)
-- System One decide storage (decision receipts, spend ledger, calibrations, proposals).
-- BEGIN GENERATED from src/core/ai/decide/schema.ts (DECIDE_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS decision_receipts (
id BIGSERIAL PRIMARY KEY,
decision_id TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
source_id TEXT,
slot TEXT NOT NULL,
mode TEXT NOT NULL,
provider TEXT NOT NULL,
model_alias TEXT,
model_resolved TEXT,
question_kind TEXT,
state_hash TEXT,
question_hash TEXT,
answer_value REAL,
answer_choice TEXT,
confidence REAL,
threshold REAL,
outcome TEXT NOT NULL,
subject_ref TEXT,
call_site TEXT NOT NULL,
lane TEXT NOT NULL,
policy_fingerprint TEXT,
calibration_ref TEXT,
latency_ms INTEGER,
input_tokens INTEGER,
error_reason TEXT,
protected BOOLEAN NOT NULL DEFAULT false,
min_keep INTEGER,
rank INTEGER,
k_used INTEGER,
remote BOOLEAN NOT NULL DEFAULT false,
run_meta TEXT
);
CREATE INDEX IF NOT EXISTS decision_receipts_slot_created_idx ON decision_receipts (slot, created_at);
CREATE INDEX IF NOT EXISTS decision_receipts_model_slot_idx ON decision_receipts (model_resolved, slot);
CREATE INDEX IF NOT EXISTS decision_receipts_decision_idx ON decision_receipts (decision_id);
CREATE TABLE IF NOT EXISTS decide_spend (
request_id TEXT PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
source_id TEXT,
slot TEXT NOT NULL,
provider TEXT NOT NULL,
model_resolved TEXT,
lane TEXT NOT NULL,
remote BOOLEAN NOT NULL DEFAULT false,
input_tokens INTEGER NOT NULL DEFAULT 0,
cost_usd DOUBLE PRECISION NOT NULL DEFAULT 0,
outcome TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS decide_spend_created_idx ON decide_spend (created_at);
CREATE TABLE IF NOT EXISTS decide_state (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decision_receipts ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decide_spend ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decide_state ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS decide_calibrations (
id BIGSERIAL PRIMARY KEY,
slot TEXT NOT NULL,
call_site TEXT NOT NULL,
provider TEXT NOT NULL,
model_resolved TEXT NOT NULL,
threshold REAL NOT NULL,
min_keep INTEGER,
metric TEXT NOT NULL,
metric_value REAL,
ece REAL,
retest_sd REAL,
repack_sd REAL,
action_precision_lb REAL,
qualification TEXT,
qualified_at TIMESTAMPTZ,
policy_fingerprint TEXT,
n INTEGER NOT NULL,
dataset_hash TEXT,
split_hash TEXT,
calibrate_ids_hash TEXT,
calibrate_only BOOLEAN NOT NULL DEFAULT true,
pack_shape TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
retired_at TIMESTAMPTZ,
notes TEXT
);
CREATE INDEX IF NOT EXISTS decide_calibrations_lookup_idx ON decide_calibrations (slot, provider, model_resolved, created_at DESC);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decide_calibrations ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS decide_proposals (
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
source_id TEXT NOT NULL,
sweep_id TEXT NOT NULL,
pair_index INTEGER NOT NULL,
new_fact_id BIGINT NOT NULL,
old_fact_id BIGINT NOT NULL,
direction TEXT NOT NULL DEFAULT 'new_supersedes_old',
p_supersede REAL NOT NULL,
threshold REAL,
proposal_floor REAL NOT NULL,
model_resolved TEXT,
status TEXT NOT NULL DEFAULT 'pending',
decided_at TIMESTAMPTZ,
before_state TEXT,
after_state TEXT,
UNIQUE (sweep_id, pair_index)
);
CREATE INDEX IF NOT EXISTS decide_proposals_status_created_idx ON decide_proposals (status, created_at);
CREATE TABLE IF NOT EXISTS decide_sweep_deferred (
source_id TEXT NOT NULL,
fact_id BIGINT NOT NULL,
slot TEXT NOT NULL,
reason TEXT NOT NULL,
attempts INTEGER NOT NULL DEFAULT 0,
next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (slot, source_id, fact_id)
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decide_proposals ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decide_sweep_deferred ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE UNIQUE INDEX IF NOT EXISTS decide_proposals_claim_pair_uidx ON decide_proposals (source_id, LEAST(new_fact_id, old_fact_id), GREATEST(new_fact_id, old_fact_id)) WHERE sweep_id LIKE 'claim_conflicts:%';
CREATE INDEX IF NOT EXISTS decide_proposals_pending_new_idx ON decide_proposals (new_fact_id) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS decide_proposals_pending_old_idx ON decide_proposals (old_fact_id) WHERE status = 'pending';
CREATE TABLE IF NOT EXISTS decide_review_queue (
kind TEXT NOT NULL,
source_id TEXT NOT NULL,
a_ref TEXT NOT NULL,
b_ref TEXT NOT NULL DEFAULT '*',
evidence TEXT,
reason TEXT NOT NULL DEFAULT 'queued',
attempts INTEGER NOT NULL DEFAULT 0,
next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (kind, source_id, a_ref, b_ref)
);
CREATE TABLE IF NOT EXISTS decide_review_proposals (
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
kind TEXT NOT NULL,
source_id TEXT NOT NULL,
sweep_id TEXT NOT NULL,
a_ref TEXT NOT NULL,
b_ref TEXT NOT NULL,
subject TEXT NOT NULL DEFAULT '*',
visibility TEXT,
p_action REAL NOT NULL,
threshold REAL,
model_resolved TEXT,
question_version TEXT,
status TEXT NOT NULL DEFAULT 'pending',
decided_at TIMESTAMPTZ,
receipt TEXT,
UNIQUE (kind, source_id, a_ref, b_ref, subject)
);
CREATE INDEX IF NOT EXISTS decide_review_proposals_status_idx ON decide_review_proposals (status, created_at);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decide_review_queue ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE decide_review_proposals ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/ai/decide/schema.ts (DECIDE_SCHEMA_SQL)
-- gbrain facts relink attempts and journal (#5836).
-- BEGIN GENERATED from src/core/facts/relink-schema.ts (FACT_RELINK_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS fact_relink_attempts (
source_id TEXT NOT NULL,
fact_id BIGINT NOT NULL,
outcome TEXT NOT NULL,
reason TEXT,
tier TEXT,
model TEXT,
target_slug TEXT,
run_id TEXT,
attempted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, fact_id)
);
CREATE INDEX IF NOT EXISTS fact_relink_attempts_outcome_idx ON fact_relink_attempts (source_id, outcome, attempted_at);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE fact_relink_attempts ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/facts/relink-schema.ts (FACT_RELINK_SCHEMA_SQL)
-- Temporal typed edges: dated evidence, derived relationship state, proposals.
-- BEGIN GENERATED from src/core/link-temporal-schema.ts (LINK_TEMPORAL_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS link_transitions (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
from_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
to_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
link_type TEXT NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('start','end')),
occurred_on DATE NOT NULL,
date_precision TEXT NOT NULL DEFAULT 'day' CHECK (date_precision IN ('day','month','year')),
producer TEXT NOT NULL CHECK (producer IN ('timeline','explicit','frontmatter','manual','inline','dream')),
origin_page_id INTEGER REFERENCES pages(id) ON DELETE CASCADE,
line_hash TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
link_source TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS link_transitions_identity_idx
ON link_transitions (origin_page_id, from_page_id, to_page_id, link_type, link_source, kind, occurred_on, producer) NULLS NOT DISTINCT;
CREATE INDEX IF NOT EXISTS link_transitions_relationship_idx ON link_transitions (from_page_id, to_page_id, link_type);
CREATE INDEX IF NOT EXISTS link_transitions_origin_idx ON link_transitions (origin_page_id);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE link_transitions ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS link_relationships (
from_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
to_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
link_type TEXT NOT NULL,
scope TEXT NOT NULL CHECK (scope IN ('all','world')),
source_id TEXT NOT NULL,
semantics TEXT NOT NULL CHECK (semantics IN ('state','event')),
valid_ranges DATEMULTIRANGE NOT NULL,
status_now TEXT NOT NULL CHECK (status_now IN ('live','ended','ended_unknown_date','not_started','disputed','event')),
first_start DATE,
last_start DATE,
last_end DATE,
undated_present INTEGER NOT NULL DEFAULT 0,
undated_past INTEGER NOT NULL DEFAULT 0,
disputed BOOLEAN NOT NULL DEFAULT false,
recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
retired_at TIMESTAMPTZ,
evidence_hash TEXT NOT NULL,
refreshed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (from_page_id, to_page_id, link_type, scope)
);
CREATE INDEX IF NOT EXISTS link_relationships_to_idx ON link_relationships (to_page_id, link_type, scope);
CREATE INDEX IF NOT EXISTS link_relationships_source_idx ON link_relationships (source_id, status_now);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE link_relationships ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS link_edge_proposals (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
from_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
a_to_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
b_to_page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
link_type TEXT NOT NULL,
evidence_hash TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('proposed','applied','rejected','undone','stale','reverted_by_user','undated_unresolved','ambiguous_same_date','compatible','error')),
ending_to_page_id INTEGER REFERENCES pages(id) ON DELETE SET NULL,
close_date DATE,
born_closed BOOLEAN NOT NULL DEFAULT false,
model TEXT,
confidence REAL,
cost_usd REAL,
generated_line TEXT,
detail TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (from_page_id, a_to_page_id, b_to_page_id, link_type, evidence_hash)
);
CREATE INDEX IF NOT EXISTS link_edge_proposals_status_idx ON link_edge_proposals (source_id, status, created_at);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE link_edge_proposals ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE SEQUENCE IF NOT EXISTS graph_generation_seq;
-- END GENERATED from src/core/link-temporal-schema.ts (LINK_TEMPORAL_SCHEMA_SQL)
-- Always-loaded core memory: remote-edit notices.
-- BEGIN GENERATED from src/core/core-memory-schema.ts (CORE_EDIT_NOTICES_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS core_edit_notices (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
slug TEXT NOT NULL,
page_id BIGINT,
revision TEXT,
base_revision TEXT,
base_text TEXT,
actor TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
acked_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS core_edit_notices_pending_idx ON core_edit_notices (source_id, slug, id) WHERE acked_at IS NULL;
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE core_edit_notices ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/core-memory-schema.ts (CORE_EDIT_NOTICES_SCHEMA_SQL)
-- The purge guards (facts, takes, pages triggers) are installed by the memory_purge migration.
-- BEGIN GENERATED from src/core/facts/purge-schema.ts (MEMORY_PURGE_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS fact_purges (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
visibility TEXT NOT NULL CHECK (visibility IN ('private','world')),
subject TEXT NOT NULL DEFAULT '*',
fact_hash TEXT NOT NULL,
request_id UUID,
actor TEXT,
reason TEXT,
purged_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, visibility, subject, fact_hash)
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE fact_purges ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS take_purges (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
subject TEXT NOT NULL DEFAULT '*',
claim_hash TEXT NOT NULL,
request_id UUID,
purged_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, subject, claim_hash)
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE take_purges ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS page_purges (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
content_hash TEXT NOT NULL,
slug TEXT NOT NULL,
request_id UUID,
purged_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (source_id, content_hash)
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE page_purges ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS derivation_inputs (
derived_table TEXT NOT NULL,
derived_id TEXT NOT NULL,
input_table TEXT NOT NULL,
input_id TEXT NOT NULL,
source_id TEXT REFERENCES sources(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (derived_table, derived_id, input_table, input_id)
);
CREATE INDEX IF NOT EXISTS idx_derivation_inputs_input ON derivation_inputs (input_table, input_id);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE derivation_inputs ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS needs_rederive (
derived_table TEXT NOT NULL,
derived_id TEXT NOT NULL,
source_id TEXT REFERENCES sources(id) ON DELETE CASCADE,
request_id UUID,
reason TEXT NOT NULL,
marked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (derived_table, derived_id)
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE needs_rederive ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/facts/purge-schema.ts (MEMORY_PURGE_SCHEMA_SQL)
-- BEGIN GENERATED from src/core/facts/purge-schema.ts (FACT_PURGE_LOOKUP_INDEX_SQL). Edit that file, then run: bun run build:schema
CREATE INDEX IF NOT EXISTS fact_purges_subject_idx ON fact_purges (source_id, subject, fact_hash) INCLUDE (visibility, purged_at);
CREATE INDEX IF NOT EXISTS fact_purges_all_subjects_idx ON fact_purges (source_id, purged_at) WHERE subject = '*';
-- END GENERATED from src/core/facts/purge-schema.ts (FACT_PURGE_LOOKUP_INDEX_SQL)
-- #5575 blocking write gate: verdict receipts and held facts/takes.
-- BEGIN GENERATED from src/core/write-gate-schema.ts (WRITE_GATE_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS write_gate_receipts (
id BIGSERIAL PRIMARY KEY,
target_table TEXT NOT NULL CHECK (target_table IN ('pages','facts','takes','timeline_entries','write_gate_holds')),
target_id TEXT NOT NULL,
source_id TEXT,
content_hash TEXT NOT NULL,
tier TEXT NOT NULL,
detector_version INTEGER NOT NULL,
verdict TEXT NOT NULL CHECK (verdict IN ('flag','quarantine')),
reason_families TEXT[] NOT NULL DEFAULT '{}',
reasons TEXT[] NOT NULL DEFAULT '{}',
detector_error BOOLEAN NOT NULL DEFAULT false,
request_id TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS write_gate_receipts_target_idx ON write_gate_receipts (target_table, target_id, content_hash, detector_version);
CREATE INDEX IF NOT EXISTS write_gate_receipts_seen_idx ON write_gate_receipts (last_seen_at);
CREATE TABLE IF NOT EXISTS write_gate_holds (
id BIGSERIAL PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN ('fact','take')),
source_id TEXT NOT NULL,
slug TEXT NOT NULL DEFAULT '',
fingerprint TEXT NOT NULL,
detector_version INTEGER NOT NULL,
tier TEXT NOT NULL,
reason_families TEXT[] NOT NULL DEFAULT '{}',
reasons TEXT[] NOT NULL DEFAULT '{}',
detector_error BOOLEAN NOT NULL DEFAULT false,
payload JSONB NOT NULL,
write_origin JSONB,
request_id TEXT,
status TEXT NOT NULL DEFAULT 'held' CHECK (status IN ('held','released','dropped')),
seen_count INTEGER NOT NULL DEFAULT 1,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
decided_at TIMESTAMPTZ,
decided_by TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS write_gate_holds_dedupe_idx ON write_gate_holds (source_id, slug, fingerprint, detector_version);
CREATE INDEX IF NOT EXISTS write_gate_holds_status_idx ON write_gate_holds (status, source_id, id);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE write_gate_receipts ENABLE ROW LEVEL SECURITY;
ALTER TABLE write_gate_holds ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/write-gate-schema.ts (WRITE_GATE_SCHEMA_SQL)
-- #5255/#5176 (O-DX-8): last upstream observation per source, recorded by sync
-- from the checkout's Git state (upstream ref, its last fetch/push time, commits
-- the synced commit lacks); doctor sync_freshness reads it with no subprocess.
-- Added last so a fresh install has the column order an upgraded brain gets.
ALTER TABLE sources ADD COLUMN IF NOT EXISTS upstream_checked_at TIMESTAMPTZ;
ALTER TABLE sources ADD COLUMN IF NOT EXISTS upstream_commit TEXT;
ALTER TABLE sources ADD COLUMN IF NOT EXISTS upstream_behind INTEGER;
-- Pinned questions (C4): answers and their evidence pointers live outside pages.
-- BEGIN GENERATED from src/core/questions/schema.ts (PINNED_QUESTIONS_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS pinned_questions (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
slug TEXT NOT NULL,
question TEXT NOT NULL,
scope_slug_prefix TEXT,
scope_entity TEXT,
state TEXT NOT NULL DEFAULT 'active',
inactive_reason TEXT,
publish_mode TEXT NOT NULL DEFAULT 'publish',
model TEXT,
cooldown_days REAL,
origin TEXT NOT NULL DEFAULT 'pin',
created_by TEXT NOT NULL,
revision BIGINT NOT NULL DEFAULT 1,
answer_revision INTEGER NOT NULL DEFAULT 0,
answer TEXT,
answer_model TEXT,
last_refresh_at TIMESTAMPTZ,
last_attempt_at TIMESTAMPTZ,
last_error TEXT,
watermark_generation BIGINT,
watermark_fact_id BIGINT,
watermark_at TIMESTAMPTZ,
lease_token TEXT,
lease_owner TEXT,
lease_expires_at TIMESTAMPTZ,
refresh_attempts INTEGER NOT NULL DEFAULT 0,
spend_usd DOUBLE PRECISION NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
archived_at TIMESTAMPTZ,
CONSTRAINT pinned_questions_source_slug_key UNIQUE (source_id, slug)
);
CREATE INDEX IF NOT EXISTS pinned_questions_state_idx ON pinned_questions (state, source_id);
CREATE TABLE IF NOT EXISTS question_evidence (
id BIGSERIAL PRIMARY KEY,
question_id BIGINT NOT NULL,
answer_revision INTEGER NOT NULL,
sentence_id TEXT NOT NULL,
kind TEXT NOT NULL,
source_id TEXT NOT NULL,
page_id INTEGER,
page_slug TEXT,
page_generation BIGINT,
page_revision TEXT,
item_id BIGINT,
content_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS question_evidence_question_idx ON question_evidence (question_id, answer_revision);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE pinned_questions ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE question_evidence ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/questions/schema.ts (PINNED_QUESTIONS_SCHEMA_SQL)
-- Belief ledger (W1, docs/designs/BELIEF_LEDGER.md): append-only canonical audit state.
-- BEGIN GENERATED from src/core/belief/schema.ts (BELIEF_LEDGER_SCHEMA_SQL). Edit that file, then run: bun run build:schema
CREATE TABLE IF NOT EXISTS belief_events (
id BIGSERIAL PRIMARY KEY,
event_key BYTEA NOT NULL UNIQUE,
source_id TEXT NOT NULL,
source_incarnation UUID NOT NULL,
page_lifetime UUID,
slug TEXT NOT NULL,
subject_kind TEXT NOT NULL CHECK (subject_kind IN ('page','fact','take','link','link_transition','coverage')),
subject_key TEXT NOT NULL,
event_type TEXT NOT NULL,
origin TEXT NOT NULL DEFAULT 'live' CHECK (origin IN ('live','unmanaged','baseline','backfill','reconcile')),
trust_tier TEXT NOT NULL,
writer_tier TEXT,
tier_basis TEXT NOT NULL DEFAULT 'row' CHECK (tier_basis IN ('row','writer','unknown')),
visibility TEXT NOT NULL,
publication_id UUID NOT NULL,
ord INTEGER NOT NULL,
recorded_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
source_time TIMESTAMPTZ,
valid_from TIMESTAMPTZ,
valid_until TIMESTAMPTZ,
value JSONB,
value_hash TEXT,
prior_hash TEXT,
state_hash TEXT NOT NULL,
cause JSONB NOT NULL,
claim_fp TEXT,
purged BOOLEAN NOT NULL DEFAULT false,
redacted_fields TEXT[]
);
CREATE INDEX IF NOT EXISTS belief_events_page ON belief_events (source_id, slug, id);
CREATE INDEX IF NOT EXISTS belief_events_subject ON belief_events (source_id, subject_kind, subject_key, id);
CREATE INDEX IF NOT EXISTS belief_events_recorded ON belief_events (recorded_at);
CREATE INDEX IF NOT EXISTS belief_events_claim_fp ON belief_events (source_id, claim_fp) WHERE claim_fp IS NOT NULL;
CREATE INDEX IF NOT EXISTS belief_events_lifetime ON belief_events (page_lifetime, id) WHERE page_lifetime IS NOT NULL;
CREATE INDEX IF NOT EXISTS belief_events_publication ON belief_events (publication_id);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE belief_events ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS belief_page_lifetimes (
page_lifetime UUID PRIMARY KEY,
source_id TEXT NOT NULL,
source_incarnation UUID NOT NULL,
slug TEXT NOT NULL,
page_id INTEGER,
started_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
ended_at TIMESTAMPTZ
);
CREATE UNIQUE INDEX IF NOT EXISTS belief_page_lifetimes_open ON belief_page_lifetimes (source_incarnation, slug) WHERE ended_at IS NULL;
CREATE INDEX IF NOT EXISTS belief_page_lifetimes_slug ON belief_page_lifetimes (source_id, slug);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE belief_page_lifetimes ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS belief_commit_marks (
publication_id UUID PRIMARY KEY,
observed_at TIMESTAMPTZ NOT NULL
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE belief_commit_marks ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS belief_retier_manifest (
summary_event BIGINT NOT NULL,
source_id TEXT NOT NULL,
subject_kind TEXT NOT NULL,
subject_key TEXT NOT NULL,
from_tier TEXT,
to_tier TEXT NOT NULL,
PRIMARY KEY (summary_event, subject_kind, subject_key)
);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE belief_retier_manifest ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
CREATE TABLE IF NOT EXISTS belief_redactions (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
subject_key TEXT NOT NULL,
field TEXT NOT NULL,
purge_request UUID NOT NULL,
field_hash TEXT NOT NULL,
released_by UUID,
released_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS belief_redactions_subject ON belief_redactions (source_id, subject_key, field);
CREATE INDEX IF NOT EXISTS belief_redactions_field_hash ON belief_redactions (source_id, field_hash);
DO $rls$ BEGIN
IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
ALTER TABLE belief_redactions ENABLE ROW LEVEL SECURITY;
END IF;
END $rls$;
-- END GENERATED from src/core/belief/schema.ts (BELIEF_LEDGER_SCHEMA_SQL)
-- GBRA-133 PR 2a (migration v262): commit notifier wanted markers, the
-- per-host notifier lease and per-source notifier state + coverage record
-- (src/core/notifier/store.ts).
CREATE TABLE IF NOT EXISTS sync_wanted_markers (
source_id TEXT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
intent TEXT NOT NULL CHECK (intent IN ('freshness','pull','full')),
source_incarnation TEXT,
wanted_head TEXT,
generation BIGINT NOT NULL DEFAULT 1,
first_due_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
requested_by TEXT NOT NULL,
job_id BIGINT,
PRIMARY KEY (source_id, intent)
);
CREATE TABLE IF NOT EXISTS sync_notifier_leases (
host_id TEXT PRIMARY KEY,
owner_id TEXT NOT NULL,
fence BIGINT NOT NULL DEFAULT 1,
resident_kind TEXT NOT NULL,
pid INTEGER,
renewed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
expires_at TIMESTAMPTZ NOT NULL
);
CREATE TABLE IF NOT EXISTS sync_notifier_sources (
source_id TEXT PRIMARY KEY REFERENCES sources(id) ON DELETE CASCADE,
source_incarnation TEXT,
syncer_host_id TEXT,
syncer_recorded_at TIMESTAMPTZ,
state TEXT NOT NULL DEFAULT 'idle' CHECK (state IN ('idle','due','running','retry','operator_blocked')),
reason TEXT,
first_due_at TIMESTAMPTZ,
attempts INTEGER NOT NULL DEFAULT 0,
next_retry_at TIMESTAMPTZ,
last_progress_at TIMESTAMPTZ,
last_error_code TEXT,
last_error TEXT,
last_error_at TIMESTAMPTZ,
trace_path TEXT,
observed_head TEXT,
observed_at TIMESTAMPTZ,
detection_mode TEXT CHECK (detection_mode IN ('watch','poll')),
owner_host_id TEXT,
owner_kind TEXT,
owner_pid INTEGER,
owner_seen_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Last statement: pg_temp ends every gbrain function's pinned search_path (#7632), after any CREATE OR REPLACE above reset it.
-- BEGIN GENERATED from src/core/function-search-path.ts (FUNCTION_SEARCH_PATH_HARDENING_SQL). Edit that file, then run: bun run build:schema
DO $harden$
DECLARE f RECORD;
BEGIN
FOR f IN SELECT p.oid::regprocedure AS sig, substr(c.setting, 13) AS path
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace CROSS JOIN LATERAL unnest(p.proconfig) AS c(setting)
WHERE n.nspname = 'public' AND p.prokind = 'f' AND c.setting IN ('search_path=pg_catalog, public', 'search_path=pg_catalog')
AND p.proname IN ('auto_enable_rls', 'bump_page_generation_clock_fn', 'bump_page_generation_fn', 'enforce_minion_queue_protocol', 'gbrain_access_derive_page_readers', 'gbrain_access_folder_rules', 'gbrain_access_lineage_rules', 'gbrain_access_mark_restricted', 'gbrain_access_norm_readers', 'gbrain_access_origin_closed_for', 'gbrain_access_origin_hidden_for', 'gbrain_access_origin_labelled_for', 'gbrain_access_origin_restricted', 'gbrain_access_pending_covers', 'gbrain_access_readers', 'gbrain_access_refresh_serves', 'gbrain_access_refresh_serves_trigger', 'gbrain_access_stamp_folder_rules', 'gbrain_access_state_unreadable', 'gbrain_advance_page_revision', 'gbrain_advance_tag_revision', 'gbrain_bump_trust_generation', 'gbrain_clear_atom_page_state', 'gbrain_concept_inputs_private', 'gbrain_count_git_canonical_sources', 'gbrain_count_modifications', 'gbrain_forget_page_retrievals', 'gbrain_fts_input', 'gbrain_graduation_fence', 'gbrain_guard_deferred_request', 'gbrain_guard_effect_protocol', 'gbrain_guard_guest_records', 'gbrain_guard_inserted_facts', 'gbrain_guard_protocol_activation', 'gbrain_guard_request_protocol', 'gbrain_guard_skill_publication', 'gbrain_lease_shared_skill_delivery', 'gbrain_mark_derivation_scope_entry', 'gbrain_owner_only_lineage', 'gbrain_preserve_fact_withdrawal', 'gbrain_queue_page_projection', 'gbrain_refuse_purged_fact', 'gbrain_refuse_purged_page', 'gbrain_refuse_purged_take', 'gbrain_refuse_purging_derivation', 'gbrain_release_refresh_barrier', 'gbrain_require_managed_writer', 'gbrain_require_persistence_protocol', 'gbrain_snapshot_version_trust', 'gbrain_stamp_revision_attribution', 'gbrain_stamp_trust_tier', 'gbrain_stamp_version_attribution', 'gbrain_stamp_write_attribution', 'gbrain_try_jsonb', 'notify_minion_job_change', 'update_chunk_search_vector', 'update_page_search_vector')
AND pg_has_role(current_user, p.proowner, 'USAGE')
ORDER BY 1
LOOP
EXECUTE format('ALTER FUNCTION %s SET search_path = %s, pg_temp', f.sig, f.path);
END LOOP;
END $harden$;
-- END GENERATED from src/core/function-search-path.ts (FUNCTION_SEARCH_PATH_HARDENING_SQL)