Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
// AUTO-GENERATED — do not edit. Run: bun run build:schema
// Sources: src/schema.sql + the TS fragment modules, transformed by the PGLite
// capability rules in scripts/build-schema.ts (docs/ENGINES.md#canonical-schema-sources).
// A template: __EMBEDDING_DIMS__ / __EMBEDDING_MODEL__ and the chunk-index and
// FTS-language policies are applied at runtime by getPGLiteSchema(dims, model).
import { applyChunkEmbeddingIndexPolicy } from './vector-index.ts';
import { applyFtsLanguagePolicy } from './fts-language.ts';
import { DEFAULT_EMBEDDING_MODEL, DEFAULT_EMBEDDING_DIMENSIONS } from './ai/defaults.ts';
export const PGLITE_SCHEMA_SQL_TEMPLATE = `
-- GBrain PGLite schema (local embedded Postgres), generated from src/schema.sql
-- and the TS schema fragments by scripts/build-schema.ts.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
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,
archived BOOLEAN NOT NULL DEFAULT false,
archived_at TIMESTAMPTZ,
archive_expires_at TIMESTAMPTZ,
contextual_retrieval_mode TEXT,
trust_frontmatter_overrides BOOLEAN NOT NULL DEFAULT false,
newest_content_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
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\$;
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;
CREATE INDEX IF NOT EXISTS sources_github_repo_idx
ON sources ((config->>'github_repo'))
WHERE config ? 'github_repo';
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,
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,
emotional_weight REAL NOT NULL DEFAULT 0.0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ,
effective_date TIMESTAMPTZ,
effective_date_source TEXT,
import_filename TEXT,
salience_touched_at TIMESTAMPTZ,
last_retrieved_at TIMESTAMPTZ,
links_extracted_at TIMESTAMPTZ,
database_only_reason TEXT,
contextual_retrieval_mode TEXT,
corpus_generation TEXT,
generation BIGINT NOT NULL DEFAULT 1,
CONSTRAINT pages_source_slug_key UNIQUE (source_id, slug)
);
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;
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();
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;
CREATE SEQUENCE IF NOT EXISTS page_generation_clock_seq;
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, '^.*/', '')));
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);
CREATE INDEX IF NOT EXISTS idx_pages_source_id ON pages(source_id);
CREATE INDEX IF NOT EXISTS pages_deleted_at_purge_idx
ON pages (deleted_at) WHERE deleted_at IS NOT NULL;
CREATE TABLE IF NOT EXISTS page_retrievals (
page_id INTEGER PRIMARY KEY,
last_retrieved_at TIMESTAMPTZ NOT NULL
);
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 \$\$;
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();
CREATE INDEX IF NOT EXISTS pages_links_extracted_at_idx
ON pages (source_id, links_extracted_at);
CREATE INDEX IF NOT EXISTS pages_coalesce_date_idx
ON pages ((COALESCE(effective_date, updated_at)));
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(__EMBEDDING_DIMS__),
model TEXT NOT NULL DEFAULT '__EMBEDDING_MODEL__',
token_count INTEGER,
embedded_at TIMESTAMPTZ,
embedded_text_hash TEXT,
embedding_input_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
language TEXT,
symbol_name TEXT,
symbol_type TEXT,
start_line INTEGER,
end_line INTEGER,
modality TEXT NOT NULL DEFAULT 'text',
embedding_image vector(1024),
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);
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;
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;
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).
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\$;
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 '',
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)),
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,
resolution_type TEXT CHECK (resolution_type IS NULL OR resolution_type IN ('qualified', 'unqualified')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
assertion_tense TEXT CONSTRAINT links_assertion_tense_check CHECK (assertion_tense IS NULL OR assertion_tense IN ('present', 'past')),
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 OR REPLACE VIEW page_links AS
SELECT id, from_page_id, to_page_id FROM links;
CREATE INDEX IF NOT EXISTS idx_links_supersedes_to ON links (to_page_id) WHERE link_type = 'supersedes';
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);
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,
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);
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);
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 '',
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);
CREATE UNIQUE INDEX IF NOT EXISTS idx_timeline_dedup ON timeline_entries(page_id, date, md5(summary), source);
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;
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()
);
CREATE INDEX IF NOT EXISTS idx_versions_page ON page_versions(page_id);
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(),
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);
CREATE TABLE IF NOT EXISTS config (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
INSERT INTO config (key, value) VALUES
('version', '1'),
('engine', 'pglite'),
('embedding_model', '__EMBEDDING_MODEL__'),
('embedding_dimensions', '__EMBEDDING_DIMS__'),
('chunk_strategy', 'semantic')
ON CONFLICT (key) DO NOTHING;
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;
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()
);
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 '{}',
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,
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(),
source_grant TEXT NULL CHECK (source_grant IN ('none')),
takes_holders TEXT[] NULL
);
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);
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);
-- The facts index and withdrawal trigger are installed by migrations 60/148.
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)
);
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()
);
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);
CREATE TABLE IF NOT EXISTS op_checkpoints (
op TEXT NOT NULL,
fingerprint TEXT NOT NULL,
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);
CREATE INDEX IF NOT EXISTS op_checkpoints_sync_hold_page_idx
ON op_checkpoints ((completed_keys->0->>'page_id')) WHERE op = 'sync-hold';
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
);
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);
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)
);
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);
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(),
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;
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);
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';
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)
);
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)
);
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);
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);
CREATE INDEX IF NOT EXISTS idx_pages_title_search ON pages USING GIN (ts_filter(search_vector, '{a}'));
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();
DROP TRIGGER IF EXISTS trg_timeline_search_vector ON timeline_entries;
DROP FUNCTION IF EXISTS update_page_search_vector_from_timeline();
CREATE TABLE IF NOT EXISTS slug_aliases (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
alias_slug TEXT NOT NULL,
canonical_slug TEXT NOT NULL,
notes TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT slug_aliases_no_self CHECK (alias_slug <> canonical_slug),
CONSTRAINT slug_aliases_uniq UNIQUE (source_id, alias_slug)
);
CREATE INDEX IF NOT EXISTS slug_aliases_canonical_idx
ON slug_aliases (source_id, canonical_slug);
CREATE TABLE IF NOT EXISTS page_aliases (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
alias_norm TEXT NOT NULL,
slug TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT page_aliases_uniq UNIQUE (source_id, alias_norm, slug)
);
CREATE INDEX IF NOT EXISTS page_aliases_lookup_idx
ON page_aliases (source_id, alias_norm);
CREATE INDEX IF NOT EXISTS page_aliases_slug_idx
ON page_aliases (source_id, slug);
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,
claim_generation BIGINT NOT NULL DEFAULT 0,
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,
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,
result JSONB,
progress JSONB,
error_text TEXT,
stacktrace JSONB DEFAULT '[]',
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;
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;
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';
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);
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;
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,
content_blocks JSONB NOT NULL,
schema_version INTEGER NOT NULL DEFAULT 1,
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);
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,
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);
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);
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,
last_refreshed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_cycle_locks_ttl ON gbrain_cycle_locks(ttl_expires_at);
ALTER TABLE gbrain_cycle_locks
ADD COLUMN IF NOT EXISTS acquisition_token UUID NOT NULL DEFAULT gen_random_uuid();
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(),
as_of_ts TIMESTAMPTZ,
salience_param TEXT,
recency_param TEXT,
salience_resolved TEXT,
recency_resolved TEXT,
salience_source TEXT,
recency_source TEXT,
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);
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);
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);
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);
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;
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);
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);
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);
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);
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();
-- Canonical page state (migration 150).
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();
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(),
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);
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);
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';
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(),
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 '{}',
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);
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)
);
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;
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.
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);
-- Wave B per-segment derivation watermarks (migration v273; src/core/page-body/derivations.ts).
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();
-- Durable concurrent persistence (migration 151).
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_database_pending
ON persistence_requests(source_incarnation,sequence) WHERE worktree_id IS NULL AND state IN ('queued','running','recovering');
CREATE INDEX IF NOT EXISTS persistence_requests_sync_run_open
ON persistence_requests(worktree_id,(intent->>'runId')) WHERE state<>'committed' AND intent->>'kind' IN ('managed_sync_import','managed_sync_delete');
CREATE INDEX IF NOT EXISTS persistence_requests_sync_run_committed
ON persistence_requests(source_id,(intent->>'runId'),(intent->>'index')) WHERE state='committed' AND intent ? 'runId';
CREATE INDEX IF NOT EXISTS persistence_requests_sync_watermark
ON persistence_requests(worktree_id,source_incarnation,completed_at DESC) WHERE state='committed' AND COALESCE(intent->>'kind','') LIKE 'managed_sync_%';
CREATE INDEX IF NOT EXISTS persistence_requests_compactable
ON persistence_requests(completed_at) WHERE recovery IS NULL AND NOT compacted AND state IN ('committed','conflict','failed','cancelled');
CREATE INDEX IF NOT EXISTS persistence_requests_slug_intent
ON persistence_requests(source_id,slug,sequence) WHERE intent IS NOT NULL AND NOT compacted;
CREATE INDEX IF NOT EXISTS persistence_requests_request_id
ON persistence_requests(source_id,request_id);
CREATE INDEX IF NOT EXISTS persistence_requests_foreground_committed
ON persistence_requests(worktree_id,sequence) WHERE state='committed' AND NOT COALESCE(intent->>'kind','') LIKE 'managed_sync_%';
CREATE INDEX IF NOT EXISTS persistence_requests_replay_id
ON persistence_requests(request_id);
CREATE INDEX IF NOT EXISTS persistence_requests_slug_sequence
ON persistence_requests(source_id,slug,sequence);
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\$;
;
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\$;
-- Verified text projection work (migration 153).
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();
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';
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';
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';
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 \$\$;
-- System One decide storage (decision receipts, spend ledger, calibrations, proposals).
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\$;
-- gbrain facts relink attempts and journal (#5836).
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\$;
-- Temporal typed edges: dated evidence, derived relationship state, proposals.
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;
-- Always-loaded core memory: remote-edit notices.
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\$;
-- The purge guards (facts, takes, pages triggers) are installed by the memory_purge migration.
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\$;
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 = '*';
-- #5575 blocking write gate: verdict receipts and held facts/takes.
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\$;
-- Pinned questions (C4): answers and their evidence pointers live outside pages.
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\$;
-- Belief ledger (W1, docs/designs/BELIEF_LEDGER.md): append-only canonical audit state.
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\$;
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.
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\$;
`;
export function getPGLiteSchema(
dims: number = DEFAULT_EMBEDDING_DIMENSIONS,
model: string = DEFAULT_EMBEDDING_MODEL,
): string {
const parsedDims = Number(dims);
if (!Number.isInteger(parsedDims) || parsedDims <= 0) {
throw new Error(`Invalid embedding dimensions: ${dims}`);
}
const sanitizedModel = String(model).replace(/'/g, "''");
return applyFtsLanguagePolicy(applyChunkEmbeddingIndexPolicy(PGLITE_SCHEMA_SQL_TEMPLATE, parsedDims))
.replace(/__EMBEDDING_DIMS__/g, String(parsedDims))
.replace(/__EMBEDDING_MODEL__/g, sanitizedModel);
}
/** Back-compat: pre-computed default-1536 schema for existing callers. */
export const PGLITE_SCHEMA_SQL = getPGLiteSchema();