Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
25.7 KB
Raw Blame
import { requireLedgerPoliciesSql } from '../derived-state-maintainers.ts';

/**
 * Segmented page storage (Wave B) DDL shared by migration v248 and both schema
 * blobs (src/schema.sql regions; `bun run build:schema`).
 *
 * The new tables live in the blob; the columns v248 adds to pages,
 * content_chunks and page_versions do not. Those are migration-only, like the
 * trust-tier columns, so a fresh install (blob, then every migration) and an
 * upgraded brain end with the same column order. Blob triggers therefore read
 * `pages.body_generation` / `timeline_lead` only on a row whose body columns
 * are NULL, which v248's DROP NOT NULL is the first to permit.
 */

/**
 * Query-cache generation bump (v0.40.3.0, migration v91's trigger function).
 * BEFORE INSERT OR UPDATE so every write path bumps generation (D6 / codex #4).
 * INSERT: pages get COALESCE(MAX(generation), 0) + 1 so the bookmark gate
 * fires for any cache row stored before the new page existed. UPDATE: bumps
 * only when a content column IS DISTINCT FROM (allow-list widened per D6 +
 * codex #3 to include title/type/page_kind/corpus_generation/content_hash) so
 * read-time mutations don't invalidate every cache row. A segmented page's
 * body identity is `body_generation`: its body columns are NULL on both sides
 * of a flip, so the generation moves when body_generation does.
 */
export const PAGE_GENERATION_BUMP_FN_SQL = `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;`;

/**
 * The segment texts each staging staged or deduped onto (migration v255). A create keeps its manifest rows in memory
 * until it publishes and a replace stages its texts before its manifest rows, so this is the only durable record of
 * which texts a live staging still needs: a page purge keeps exactly those of the texts no manifest row references.
 * Rows go with their staging; they hold hashes, never text.
 */
export const PAGE_BODY_STAGING_TEXTS_SQL = `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);`;

/**
 * A live staging (open, sealed or prepared, or named by a non-terminal persistence request's body_ref, the segment
 * GC's roots) recorded the text `t` (a caller alias exposing source_id and sha256) in page_body_staging_texts: a write
 * that has not published still needs it. Constant SQL over the alias, no values, for the purge statements that keep
 * exactly those texts.
 */
export function stagingNeedsTextSql(t: string): string {
  return `EXISTS (SELECT 1 FROM page_body_staging_texts r JOIN page_body_stagings rs ON rs.id = r.staging_id
      WHERE r.source_id = ${t}.source_id AND r.sha256 = ${t}.sha256 AND ${liveStagingSql('rs')})`;
}

/**
 * GST-1209: the tiling of page `p`'s live generation (an alias of pages), as a LATERAL subquery: its manifest rows as every
 * reader selects them, their count (`n`), the bytes they cover (`covered`) and whether each starts where the previous one
 * ends, the first at byte 0 (`gapless`). Constant SQL over the alias, no values.
 */
export function liveTilingSql(p: string): string {
  return `SELECT count(*)::bigint AS n, COALESCE(sum(r.byte_len), 0)::bigint AS covered, COALESCE(bool_and(r.byte_start = r.prior), true) AS gapless
      FROM (SELECT m.byte_start, m.byte_len, COALESCE(sum(m.byte_len) OVER (ORDER BY m.seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prior
              FROM page_body_manifest m WHERE m.page_id = ${p}.id AND m.staging_id IS NULL AND m.gen_from <= ${p}.body_generation
               AND (m.gen_to IS NULL OR m.gen_to > ${p}.body_generation)) r`;
}

/** Page `p`'s live generation, tiled as `t` (liveTilingSql), does not cover its body_bytes gapless from 0: no reader can read it. */
export function untiledSql(p: string, t: string): string {
  return `(NOT ${t}.gapless OR ${t}.covered <> ${p}.body_bytes)`;
}

/** The staging `s` (an alias of page_body_stagings) is live in stagingNeedsTextSql's sense: a write that may still publish it. */
export function liveStagingSql(s: string): string {
  return `(${s}.state IN ('open','sealed','prepared')
          OR EXISTS (SELECT 1 FROM persistence_requests rq WHERE rq.state IN ('queued','deferred','running','recovering')
                      AND rq.intent->'body_ref'->>'staging_id' = ${s}.id::text))`;
}

/**
 * Segment texts (stored once per source and sha256), the generation-ranged
 * manifest, body stagings and their raw upload parts, and the occurrence id
 * sequence. The manifest's text FK is NO ACTION: a referenced text can't be
 * deleted, and a source delete cascades through pages and texts in one
 * statement before the check runs. The manifest key carries the staging
 * (`staging_key`: the staging id, or the nil uuid once published), so published
 * rows are unique per (page, occurrence, gen_from) and the rows of a staging
 * that never flipped can't collide with the next write's.
 */
export const PAGE_BODY_SCHEMA_SQL = `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)
);
${PAGE_BODY_STAGING_TEXTS_SQL}`;

/**
 * The derivation pipelines whose per-segment results page_segment_derivations records (wave-b spec 8.1-8.6, PR 4), and
 * the states a row can settle in. One list feeds the table's CHECKs and the TS types (page-body/derivations.ts).
 */
export const SEGMENT_DERIVATION_KINDS = ['links', 'mentions', 'timeline', 'fences', 'conversation_facts', 'facts_backstop', 'synopsis'] as const;
export const SEGMENT_DERIVATION_STATES = ['done', 'partial', 'failed', 'skipped'] as const;
const sqlList = (values: readonly string[]) => values.map(v => `'${v}'`).join(',');

/**
 * Folds the lowercase forms a database writes depending on context or locale, applied after lowering on both sides:
 * Greek final sigma (lower('ΚΩΣΤΑΣ') is 'κωστασ' while stored text keeps 'κωστας') and the dot JavaScript and ICU keep
 * after a lowered Turkish İ ('i̇'). gbrain_fact_normalize itself stays as it is: fact fingerprints are stored.
 */
export const foldSql = (expr: string) => `replace(translate(${expr}, 'ς', 'σ'), 'i\u0307', 'i')`;

/**
 * SQL predicate, the prefilter: `text`, lowered and folded, matches one of the patterns in the text[] parameter
 * `patterns` (from derivationNeedles): has a claim's words in the claim's order. Normalization only lowers, folds
 * characters to spaces and collapses spaces, keeping order, so the words of a claim a text holds occur in it in that
 * order (in a row's JSON text too: JSON escapes only quotes, backslashes and control characters, which no word holds);
 * a text this rejects never holds the claim.
 */
const hasWordsSql = (text: string, patterns: string) => `${foldSql(`lower(${text})`)} LIKE ANY (${patterns}::text[])`;

/** SQL predicate: derivation row `d`'s output and receipt JSON text passes the prefilter (see hasWordsSql). */
export function derivationClaimCandidateSql(d: string, patterns: string): string {
  return hasWordsSql(`COALESCE(${d}.output::text, '') || ' ' || COALESCE(${d}.receipt::text, '')`, patterns);
}

/**
 * SQL predicate: derivation row `d` holds one of the claims (the text[] parameters `needles` and `patterns`, from
 * derivationNeedles). The row goes through the needle's own normalization (gbrain_fact_normalize, the fact fingerprint's:
 * lowercase, punctuation folded, whitespace collapsed; then foldSql) in the database, so it matches whatever case,
 * spacing or locale lowercasing the claim was written in. The claim must fill whole words of one string value or object
 * key of the row's output or receipt (link excerpts carry it inside a longer window, so equality would miss them): "plays
 * oboe" never matches "plays oboes", nor a claim straddling two fields. Only a row, and then a value, passing the
 * prefilter is normalized.
 */
export function derivationHoldsClaimSql(d: string, needles: string, patterns: string): string {
  return `CASE WHEN ${derivationClaimCandidateSql(d, patterns)} THEN EXISTS (SELECT 1 FROM (VALUES (${d}.output), (${d}.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 ${hasWordsSql('s.t', patterns)} THEN EXISTS (SELECT 1 FROM unnest(${needles}::text[]) k(n)
      WHERE strpos(' ' || ${foldSql('gbrain_fact_normalize(s.t)')} || ' ', k.n) > 0) ELSE false END) ELSE false END`;
}

/**
 * SQL predicate: derivation row `d` of page `p` is one a fact purge sweeps: a row of a page the purge rewrote (the
 * int[] parameter `pageIds`), or a row no live segment backs (its occurrence retired, its page demoted to inline or
 * soft-deleted). Any other row was derived from a live segment of a page the purge left alone, so it holds only what
 * that page's prose still says, and the next derivation would write it back.
 */
export function derivationSweepScopeSql(d: string, p: string, pageIds: string): string {
  return `(${d}.page_id = ANY(${pageIds}::int[]) OR ${p}.deleted_at IS NOT NULL OR NOT EXISTS (SELECT 1 FROM page_body_manifest m
    WHERE m.page_id = ${d}.page_id AND m.occurrence_id = ${d}.occurrence_id AND m.gen_to IS NULL AND m.staging_id IS NULL AND m.sha256 = ${d}.segment_sha256))`;
}

/**
 * SQL: the advisory lock key that serializes a write bringing a row into a derivation sweep's scope with the end of the
 * source's sweeps. The scope-entry trigger takes it shared in the writer's transaction every time it fires, a sweep
 * recorded or not. A sweep moves from its first walk to its verify walks, and ends (deletes its record), only under it
 * held exclusively: so every writer that fired before the sweep was recorded, and left no note, has committed before a
 * verify walk starts, and every later writer's note is visible when the record ends. The sweep tries it without waiting
 * and otherwise waits a bounded time in a transaction that holds no other lock (segment-derive.ts continueDerivationPurges).
 */
export function derivationScopeLockSql(sourceId: string): string {
  return `hashtextextended('gbrain:derivation-scope:' || ${sourceId}, 0)`;
}

/**
 * The derivation purge triggers (see PAGE_SEGMENT_DERIVATIONS_SQL below) read page_segment_derivation_purges, and the
 * scope-entry note writes page_segment_derivation_purge_pages, as the writing role. #7632/#7659: a role that RLS keeps
 * from them is refused (derived-state-maintainers.ts requireLedgerPoliciesSql) instead of reading no sweep, which let a
 * purged claim be stored again and a page leave a sweep's scope unnoted. Migration v318 re-creates the functions alone.
 */
export const DERIVATION_SCOPE_ENTRY_FUNCTION_SQL = `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
  ${requireLedgerPoliciesSql('page_segment_derivation_purges', ['SELECT'])}
  ${requireLedgerPoliciesSql('page_segment_derivation_purge_pages', ['INSERT'])}
  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(${derivationScopeLockSql('src')});
  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`;
export const REFUSE_PURGING_DERIVATION_FUNCTION_SQL = `CREATE OR REPLACE FUNCTION gbrain_refuse_purging_derivation() RETURNS trigger SET search_path = pg_catalog, public, pg_temp AS $func$
BEGIN
  ${requireLedgerPoliciesSql('page_segment_derivation_purges', ['SELECT'])}
  ${requireLedgerPoliciesSql('pages', ['SELECT'])}
  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 ${derivationSweepScopeSql('NEW', 'p', 'x.page_ids')}
      AND ${derivationHoldsClaimSql('NEW', 'x.needles', 'x.patterns')}) THEN
    RETURN NULL;
  END IF;
  RETURN NEW;
END
$func$ LANGUAGE plpgsql`;

/**
 * Per-segment derivation watermarks (migration v273): one row per page, pipeline and manifest occurrence, recording
 * the segment text and the context (`context_hash`: entry state plus the page-level inputs the pipeline reads) it was
 * derived from, the pipeline version, the outcome and a bounded per-segment output. A pipeline's work on a page is the
 * live occurrences without a settled row whose segment sha, context hash and version match, found from metadata alone.
 * Rows of an occurrence that left the live generation are deleted by their pipeline (retireSegmentDerivations) or by a
 * fence rewrite; demotion to inline deletes the page's rows, a fact purge the rows holding the claim, and a page
 * delete cascades. The output holds link context and window anchors, so the table gets the segment tables' RLS posture.
 *
 * page_segment_derivation_purges holds a fact purge's derivation sweep from the purge's commit until it is verified
 * (segment-derive.ts continueDerivationPurges): the claim's normalized forms, the pages the purge rewrote, the walk's
 * phase and cursor, its adaptive batch size, once a row alone outran the statement budget, when it stalled and, while a
 * write still open in the source keeps it from ending past its bounded wait, that it waits on one (`pending_reason`). While it
 * is there, the trigger on page_segment_derivations drops a write of a row of the source that the sweep would delete (in
 * its scope, derivationSweepScopeSql, and holding the claim; returns NULL: the row is not stored and the pipeline derives
 * it again after the sweep; a paid pipeline makes no call on such a page meanwhile, page-embedding-budget.ts
 * meterPaidCall), so a row the walk has passed cannot come back behind it. A derivation write that read a purged page
 * before the purge committed is refused by its own generation guard instead. A row can also enter the scope without a
 * write, when its occurrence is retired or its page soft-deleted: the triggers on page_body_manifest and pages note the
 * page in page_segment_derivation_purge_pages for every sweep of the source, and the sweep checks those pages again
 * before it ends.
 *
 * PR 4 (Wave B incremental derivation, transcripts): page_derivation_spend is the per-page lifetime spend ledger of each
 * paid derivation family (`facts`, `synopsis`; page-spend.ts), each capped by `derive.max_usd_per_page` on its own,
 * apart from the embedding ledger (page_embedding_budget), so derivation spend never starves a page's embeddings.
 */
export const PAGE_SEGMENT_DERIVATIONS_SQL = `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 (${sqlList(SEGMENT_DERIVATION_KINDS)})),
  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 (${sqlList(SEGMENT_DERIVATION_STATES)})),
  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)
);
${DERIVATION_SCOPE_ENTRY_FUNCTION_SQL};
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();
${REFUSE_PURGING_DERIVATION_FUNCTION_SQL};
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();`;

/**
 * The segment tables hold page text (`page_segment_texts`, `page_body_staging_parts`) or a page's shape and
 * headings (`page_body_manifest`, `page_body_stagings`, `page_view_digests`) or derived link context
 * (`page_segment_derivations`, and the purged claims `page_segment_derivation_purges` holds) or a page's derivation
 * spend (`page_derivation_spend`), so they get the posture of `pages`
 * and `content_chunks`: RLS on, re-asserted on every schema replay like the core list in src/schema.sql, so a brain
 * the auto-RLS event trigger (v35) missed, or one where it was dropped or disabled, never serves segment text to a
 * role `pages` returns no rows to. Only under a role that bypasses RLS (else the runtime role would lock itself out),
 * and only for a table still without it (ENABLE takes an ACCESS EXCLUSIVE lock even when RLS is on, #5227).
 * `page_view_digests` is absent while migration v248 runs. Postgres only: PGLite has no role system, and its
 * bootstrap enables RLS on no core table either. Source-scoped policies are operator SQL (docs/ENGINES.md).
 * Only a full initSchema replays this block: a migration that creates one of these tables enables RLS itself
 * (v275 backfills the v273 tables), since `gbrain apply-migrations --yes` runs migrations without the blob.
 */
export const PAGE_BODY_RLS_TABLES = ['page_segment_texts', 'page_body_manifest', 'page_body_stagings', 'page_body_staging_parts', 'page_view_digests', 'page_body_staging_texts', 'page_segment_derivations',
  'page_derivation_spend', 'page_segment_derivation_purges', 'page_segment_derivation_purge_pages'] as const;
export const PAGE_BODY_RLS_SQL = `DO $rls$
DECLARE target TEXT;
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'USAGE') AND (r.rolbypassrls OR r.rolsuper)) THEN
    FOREACH target IN ARRAY ARRAY[${PAGE_BODY_RLS_TABLES.map(t => `'${t}'`).join(',')}] LOOP
      IF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
                 WHERE n.nspname = current_schema() AND c.relname = target AND c.relkind = 'r' AND NOT c.relrowsecurity) THEN
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', target);
      END IF;
    END LOOP;
  END IF;
END $rls$;`;