Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
39.6 KB
Raw Blame
/**
 * Forward-reference bootstrap: one implementation for both engines (refactor
 * wave 1, E1), so the probes and DDL exist once (per-probe inventory:
 * docs/designs/refactor-wave-1/w1-inventory.md, "Forward-reference
 * bootstrap (E1)").
 *
 * Every schema-replay entrypoint runs it right before the embedded blob:
 *   - `PGLiteEngine.initSchema()` (PGLITE_SCHEMA_SQL) via `pgliteBootstrapTarget`.
 *   - `PostgresEngine.initSchema()` and the standalone `db.initSchema()`
 *     (SCHEMA_SQL) via `applyPostgresForwardReferenceBootstrap(conn)`, on the
 *     caller's connection, which MUST hold the initSchema advisory lock (key
 *     42) so concurrent bootstraps can't race on a transaction pooler.
 *
 * Before any DDL it refuses (`foreign_table`, owned-table-shapes.ts) when a table gbrain owns by name was
 * created by another application, so the blob never adopts and alters it (#9190).
 *
 * The blob forward-references state that older brains don't have yet (a
 * column-with-index or FK target that a migration adds later). One probe
 * round-trip finds every gap; fresh installs and modern brains no-op.
 *
 * Engine differences are explicit dialect hooks, never `dialect ===` checks:
 *   - `probeSchema`: master's probe predicate (`current_schema()` on
 *     Postgres, the literal `'public'` on PGLite).
 *   - `dreamVerdictsForwardReference`: only the Postgres blob carries
 *     `dream_verdicts` + `dream_verdicts_expires_idx`; PGLite creates the
 *     table by migration (v30), so it neither probes nor ALTERs it.
 *   - `chunksEmbeddingImageStep`: the v39 ALTER ran at a different position
 *     in each engine's master sequence; each keeps its own order.
 *
 * **Maintenance contract:** when a migration adds a column-with-index or a
 * new-table-with-FK referenced by either schema blob, add the probe to
 * FORWARD_REFERENCE_PROBES, the gap to `forwardReferenceGaps`, and the DDL
 * below, plus `REQUIRED_BOOTSTRAP_COVERAGE` in
 * `test/schema-bootstrap-coverage.test.ts`. That test (PGLite A2 static
 * check, MIGRATIONS column-only check, Postgres-blob CREATE-INDEX gate) parses
 * THIS file; `test/bootstrap.test.ts` and `test/e2e/postgres-bootstrap.test.ts`
 * are the live convergence halves. DDL that only one blob needs sits between
 * `dialect-only:<engine>` markers so the other engine's coverage check does
 * not count it.
 */

import type { PGlite } from '@electric-sql/pglite';
import type postgres from '#postgres';
import { GRANT_COLUMNS_SQL } from '../grants/schema.ts';
import { assertOwnedTableShapes } from '../owned-table-shapes.ts';

type ProbeRow = Record<string, boolean>;

export interface ForwardReferenceBootstrapDialect {
  /** SQL expression the probe compares `information_schema.*.table_schema` against. */
  readonly probeSchema: string;
  /** Whether this engine's blob forward-references `dream_verdicts.expires_at` (probe + ALTER). */
  readonly dreamVerdictsForwardReference: boolean;
  /** Where the v39 `content_chunks` ALTER runs in this engine's DDL sequence. */
  readonly chunksEmbeddingImageStep: 'after-pages-deleted-at' | 'after-subagent-provider-id';
}

export const PGLITE_BOOTSTRAP_DIALECT: ForwardReferenceBootstrapDialect = {
  probeSchema: `'public'`,
  dreamVerdictsForwardReference: false,
  chunksEmbeddingImageStep: 'after-pages-deleted-at',
};

export const POSTGRES_BOOTSTRAP_DIALECT: ForwardReferenceBootstrapDialect = {
  probeSchema: 'current_schema()',
  dreamVerdictsForwardReference: true,
  chunksEmbeddingImageStep: 'after-subagent-provider-id',
};

/** One connection handle the bootstrap runs on. */
export interface ForwardReferenceBootstrapTarget {
  readonly dialect: ForwardReferenceBootstrapDialect;
  /** Runs the single probe statement and returns its one row. */
  probe(sql: string): Promise<ProbeRow>;
  /** Runs one multi-statement DDL batch. */
  exec(sql: string): Promise<void>;
}

/** PGLite: `db.query` for the probe, `db.exec` for DDL batches (master's calls). */
export function pgliteBootstrapTarget(db: Pick<PGlite, 'query' | 'exec'>): ForwardReferenceBootstrapTarget {
  return {
    dialect: PGLITE_BOOTSTRAP_DIALECT,
    probe: async (sql) => (await db.query<ProbeRow>(sql)).rows[0] as ProbeRow,
    exec: async (sql) => { await db.exec(sql); },
  };
}

/**
 * Master ran the Postgres probe as a zero-parameter tagged template: prepared
 * when the connection allows it (direct Postgres; PgBouncer's connection-level
 * `prepare: false` wins) on the extended protocol. These are the equivalent
 * `unsafe` options (the engine-sql adapter's EO2 conversion).
 */
const TAGGED_TEMPLATE_OPTS = { prepare: true, simple: false };

/** Postgres: the probe on the caller's connection as master's tagged template; DDL through `conn.unsafe`. */
export function postgresBootstrapTarget(conn: ReturnType<typeof postgres>): ForwardReferenceBootstrapTarget {
  return {
    dialect: POSTGRES_BOOTSTRAP_DIALECT,
    probe: async (sql) => (await conn.unsafe<ProbeRow[]>(sql, [], TAGGED_TEMPLATE_OPTS))[0]!,
    exec: async (sql) => { await conn.unsafe(sql); },
  };
}

/**
 * Probe + patch every forward-reference target SCHEMA_SQL needs, on the
 * caller-provided connection (see the module header for the lock contract).
 */
export async function applyPostgresForwardReferenceBootstrap(conn: ReturnType<typeof postgres>): Promise<void> {
  await applyForwardReferenceBootstrap(postgresBootstrapTarget(conn));
}

type Probe = readonly [alias: string, table: string, column?: string];

// Every probe both engines run. Result order is irrelevant (read by alias).
const FORWARD_REFERENCE_PROBES: readonly Probe[] = [
  ['pages_exists', 'pages'],
  ['source_id_exists', 'pages', 'source_id'],
  ['deleted_at_exists', 'pages', 'deleted_at'],
  ['effective_date_exists', 'pages', 'effective_date'],
  ['links_exists', 'links'],
  ['link_source_exists', 'links', 'link_source'],
  ['origin_page_id_exists', 'links', 'origin_page_id'],
  ['chunks_exists', 'content_chunks'],
  ['symbol_name_exists', 'content_chunks', 'symbol_name'],
  ['language_exists', 'content_chunks', 'language'],
  ['search_vector_exists', 'content_chunks', 'search_vector'],
  ['embedding_image_exists', 'content_chunks', 'embedding_image'],
  ['mcp_log_exists', 'mcp_request_log'],
  ['agent_name_exists', 'mcp_request_log', 'agent_name'],
  ['subagent_messages_exists', 'subagent_messages'],
  ['subagent_provider_id_exists', 'subagent_messages', 'provider_id'],
  ['ingest_log_exists', 'ingest_log'],
  ['ingest_log_source_id_exists', 'ingest_log', 'source_id'],
  ['files_exists', 'files'],
  ['files_source_id_exists', 'files', 'source_id'],
  ['files_page_id_exists', 'files', 'page_id'],
  ['oauth_clients_exists', 'oauth_clients'],
  ['oauth_clients_source_id_exists', 'oauth_clients', 'source_id'],
  ['oauth_clients_federated_read_exists', 'oauth_clients', 'federated_read'],
  ['oauth_clients_surface_exists', 'oauth_clients', 'surface'],
  ['oauth_clients_surface_set_by_exists', 'oauth_clients', 'surface_set_by'],
  ['sources_exists', 'sources'],
  ['sources_archived_exists', 'sources', 'archived'],
  ['sources_archived_at_exists', 'sources', 'archived_at'],
  ['sources_archive_expires_at_exists', 'sources', 'archive_expires_at'],
  ['pages_last_retrieved_at_exists', 'pages', 'last_retrieved_at'],
  ['pages_ingested_via_exists', 'pages', 'ingested_via'],
  ['pages_ingested_at_exists', 'pages', 'ingested_at'],
  ['pages_source_uri_exists', 'pages', 'source_uri'],
  ['pages_source_kind_exists', 'pages', 'source_kind'],
  ['pages_cr_mode_exists', 'pages', 'contextual_retrieval_mode'],
  ['pages_corpus_generation_exists', 'pages', 'corpus_generation'],
  ['sources_cr_mode_exists', 'sources', 'contextual_retrieval_mode'],
  ['sources_trust_fm_exists', 'sources', 'trust_frontmatter_overrides'],
  ['pages_generation_exists', 'pages', 'generation'],
  ['pages_embedding_signature_exists', 'pages', 'embedding_signature'],
  ['pages_links_extracted_at_exists', 'pages', 'links_extracted_at'],
  ['timeline_entries_exists', 'timeline_entries'],
  ['timeline_event_page_id_exists', 'timeline_entries', 'event_page_id'],
  ['minion_jobs_exists', 'minion_jobs'],
  ['minion_jobs_timeout_at_exists', 'minion_jobs', 'timeout_at'],
  ['minion_jobs_idempotency_key_exists', 'minion_jobs', 'idempotency_key'],
  ['minion_jobs_pq_owner_exists', 'minion_jobs', 'private_queue_owner_job_id'],
  ['minion_jobs_pq_token_exists', 'minion_jobs', 'private_queue_owner_token'],
  ['minion_jobs_pq_lease_exists', 'minion_jobs', 'private_queue_lease_until'],
  ['minion_jobs_submission_authority_exists', 'minion_jobs', 'submission_authority'],
  ['minion_jobs_claim_generation_exists', 'minion_jobs', 'claim_generation'],
  ['facts_exists', 'facts'],
  ['facts_embedding_model_exists', 'facts', 'embedding_model'],
  ['facts_embedded_text_hash_exists', 'facts', 'embedded_text_hash'],
  ['derived_generation_items_exists', 'derived_generation_items'],
  ['derived_generation_items_pending_exists', 'derived_generation_items', 'pending'],
  ['link_transitions_exists', 'link_transitions'],
  ['link_transitions_link_source_exists', 'link_transitions', 'link_source'],
];

// Probed only where `dreamVerdictsForwardReference` (the Postgres blob).
const DREAM_VERDICTS_PROBES: readonly Probe[] = [
  ['dream_verdicts_exists', 'dream_verdicts'],
  ['dream_verdicts_expires_at_exists', 'dream_verdicts', 'expires_at'],
];

/** The single-round-trip probe statement for one dialect. */
export function forwardReferenceProbeSql(dialect: ForwardReferenceBootstrapDialect): string {
  const schema = dialect.probeSchema;
  const exists = ([alias, table, column]: Probe) => column
    ? `EXISTS (SELECT 1 FROM information_schema.columns
            WHERE table_schema = ${schema} AND table_name = '${table}' AND column_name = '${column}') AS ${alias}`
    : `EXISTS (SELECT 1 FROM information_schema.tables
            WHERE table_schema = ${schema} AND table_name = '${table}') AS ${alias}`;
  const grants = `(SELECT COUNT(*) = 6 FROM information_schema.columns
      WHERE table_schema = ${schema} AND table_name = 'oauth_clients'
        AND column_name IN ('allowed_operations', 'delegated_slug_prefixes', 'delegated_namespace', 'grant_profile', 'grant_revision', 'grant_repair_reasons')) AS oauth_client_grants_exist`;
  const probes = dialect.dreamVerdictsForwardReference
    ? [...FORWARD_REFERENCE_PROBES, ...DREAM_VERDICTS_PROBES]
    : FORWARD_REFERENCE_PROBES;
  return `
  SELECT
    ${[...probes.map(exists), grants].join(',\n    ')}
`;
}

/** Which forward references are missing. Fresh installs (no tables) and modern brains report none. */
export function forwardReferenceGaps(probe: ProbeRow, dialect: ForwardReferenceBootstrapDialect) {
  const needsPagesBootstrap = probe.pages_exists && !probe.source_id_exists;
  const needsLinksBootstrap = probe.links_exists
    && (!probe.link_source_exists || !probe.origin_page_id_exists);
  const needsChunksBootstrap = probe.chunks_exists
    && (!probe.symbol_name_exists || !probe.language_exists || !probe.search_vector_exists);
  // v0.26.5: pages_deleted_at_purge_idx in the blob crashes if the column
  // doesn't exist yet. Migration v34 also adds it, but bootstrap runs first.
  const needsPagesDeletedAt = probe.pages_exists && !probe.deleted_at_exists;
  // v0.26.3 (v33): idx_mcp_log_agent_time in the blob needs agent_name col.
  const needsMcpLogBootstrap = probe.mcp_log_exists && !probe.agent_name_exists;
  // v0.27 (v36): idx_subagent_messages_provider in the blob needs provider_id
  // (the SECOND column in the composite index `(job_id, provider_id)`).
  const needsSubagentProviderId = probe.subagent_messages_exists && !probe.subagent_provider_id_exists;
  // v0.27.1 (v39): idx_chunks_embedding_image partial HNSW in the blob
  // references embedding_image. Use embedding_image_exists as the proxy for
  // both v39 columns; modality is added in the same migration.
  const needsChunksEmbeddingImage = probe.chunks_exists && !probe.embedding_image_exists;
  // v0.29.1 (v40 + v41): pages_coalesce_date_idx expression index in the
  // blob references effective_date. Use effective_date_exists as the proxy
  // for the five v40 + v41 pages columns (emotional_weight, effective_date,
  // effective_date_source, import_filename, salience_touched_at).
  const needsPagesRecency = probe.pages_exists && !probe.effective_date_exists;
  // v0.31.2 (v50): idx_ingest_log_source_type_created in the blob references
  // source_id. Old brains have ingest_log without source_id; bootstrap adds
  // the column before blob replay creates the index.
  const needsIngestLogSourceId = probe.ingest_log_exists && !probe.ingest_log_source_id_exists;
  // v0.18 (v18): files.source_id + files.page_id added; idx_files_source_id
  // and idx_files_page_id in the blob crash without them.
  const needsFilesBootstrap = probe.files_exists
    && (!probe.files_source_id_exists || !probe.files_page_id_exists);
  // v0.34.1 (v60+v61+v65): oauth_clients.source_id + federated_read added;
  // FK to sources(id) + GIN index idx_oauth_clients_federated_read in the
  // blob crash without them.
  const needsOauthClientsBootstrap = probe.oauth_clients_exists
    && (!probe.oauth_clients_source_id_exists || !probe.oauth_clients_federated_read_exists);
  // WP4 (v127): oauth_clients.surface + surface_set_by. No blob index
  // references them, but the columns are migration-added AND in the blob's
  // CREATE TABLE — the exact v121 mask class — so the bootstrap adds them
  // defense-in-depth (and satisfies the MIGRATIONS ADD COLUMN coverage
  // gate). They ship in one migration and go missing together.
  const needsOauthClientsSurface = probe.oauth_clients_exists
    && (!probe.oauth_clients_surface_exists || !probe.oauth_clients_surface_set_by_exists);
  const needsOauthClientGrants = probe.oauth_clients_exists && !probe.oauth_client_grants_exist;
  // v0.26.5 (v34): sources.archived + archived_at + archive_expires_at added
  // for soft-delete lifecycle. The blob's `CREATE TABLE IF NOT EXISTS sources`
  // is a no-op on pre-existing sources tables (won't add columns), so the
  // visibility filters in search/list_pages trip on old brains. Bootstrap
  // closes the gap before any visibility-filter SQL runs.
  const needsSourcesArchive = probe.sources_exists
    && (!probe.sources_archived_exists
        || !probe.sources_archived_at_exists
        || !probe.sources_archive_expires_at_exists);
  // v0.37.0 (v79): pages_last_retrieved_at_idx in the blob references
  // last_retrieved_at. Pre-v79 brains crash without the column; bootstrap
  // adds it before blob replay creates the index. v79 runs later via
  // runMigrations and is idempotent.
  const needsPagesLastRetrievedAt = probe.pages_exists && !probe.pages_last_retrieved_at_exists;
  // v0.38.0 (v80): provenance columns on pages. Not referenced by any blob
  // index or FK today; bootstrap exists for the column-only forward-
  // reference class defense-in-depth.
  const needsPagesProvenance = probe.pages_exists
    && (!probe.pages_ingested_via_exists
        || !probe.pages_ingested_at_exists
        || !probe.pages_source_uri_exists
        || !probe.pages_source_kind_exists);
  // v0.40.3.0 (v90, renumbered from v0.40.3.0 v81 on master merge):
  // contextual retrieval columns on pages + sources. Defense-in-depth.
  const needsContextualRetrievalColumns = (probe.pages_exists
      && (!probe.pages_cr_mode_exists || !probe.pages_corpus_generation_exists))
    || (probe.sources_exists
        && (!probe.sources_cr_mode_exists || !probe.sources_trust_fm_exists));
  // v0.40.3.0 (v91): pages.generation BIGINT bumped by
  // bump_page_generation_trg. pages_generation_idx in the blob references
  // it. Pre-v91 brains crash without the column; bootstrap adds it before
  // blob replay creates the index.
  const needsPagesGeneration = probe.pages_exists && !probe.pages_generation_exists;
  // v0.41.31 (v108): pages.embedding_signature for real stale semantics.
  // No blob index references it; bootstrap is defense-in-depth.
  const needsPagesEmbeddingSignature = probe.pages_exists && !probe.pages_embedding_signature_exists;
  // v0.42.7 (v112): pages.links_extracted_at link-extraction freshness
  // watermark. pages_links_extracted_at_idx in the blob references it;
  // pre-v112 brains crash without the column, so bootstrap adds it before
  // blob replay creates the index. v112 runs later via runMigrations and is
  // idempotent.
  const needsPagesLinksExtractedAt = probe.pages_exists && !probe.pages_links_extracted_at_exists;
  // v121: schema-blob indexes reference event_page_id before migrations run.
  const needsTimelineEventPageId = probe.timeline_entries_exists && !probe.timeline_event_page_id_exists;
  // v7-era (#2626 class sweep): minion_jobs.timeout_at + idempotency_key are
  // migration-added AND referenced by blob indexes (idx_minion_jobs_timeout,
  // uniq_minion_jobs_idempotency) — a pre-v7 minion_jobs wedges blob replay
  // exactly like the v121 incident.
  const needsMinionJobsTimeoutAt = probe.minion_jobs_exists && !probe.minion_jobs_timeout_at_exists;
  const needsMinionJobsIdempotencyKey = probe.minion_jobs_exists && !probe.minion_jobs_idempotency_key_exists;
  // Token rides the probe too: a token-only-missing brain (partial upgrade)
  // would otherwise be unrepairable — the ALTER block adds all three.
  const needsMinionJobsPrivateQueue = probe.minion_jobs_exists
    && (!probe.minion_jobs_pq_owner_exists || !probe.minion_jobs_pq_token_exists
        || !probe.minion_jobs_pq_lease_exists);
  // v149: the schema-blob queue protocol references both fields. Repair either
  // missing field without assigning authority to historical work.
  const needsMinionJobsAuthority = probe.minion_jobs_exists
    && (!probe.minion_jobs_submission_authority_exists || !probe.minion_jobs_claim_generation_exists);
  // v143 (dream_verdicts_ttl, #4657): the Postgres blob index
  // dream_verdicts_expires_idx references expires_at, but the column only
  // lands via migration v143 — a Postgres brain at schema v30-v142
  // (dream_verdicts exists since v30) wedges on the blob's CREATE INDEX
  // before any migration runs. Same class as v121/v7; PGLite is unaffected
  // (its blob carries no dream_verdicts).
  const needsDreamVerdictsExpiresAt = dialect.dreamVerdictsForwardReference
    && probe.dream_verdicts_exists && !probe.dream_verdicts_expires_at_exists;
  const needsFactEmbeddingIdentity = probe.facts_exists
    && (!probe.facts_embedding_model_exists || !probe.facts_embedded_text_hash_exists);
  // v266 (#7645): the blob's partial index derived_generation_items_pending
  // (`WHERE pending`) references a column only v266 adds, so a brain at v265
  // (the table exists since v265) wedged on the replay before any migration ran.
  const needsDerivedGenerationItemsPending = probe.derived_generation_items_exists
    && !probe.derived_generation_items_pending_exists;
  // v302 (GST-2228): the blob's link_transitions_identity_idx includes
  // link_source, a column only v302 adds, so a brain at v204-v301 would wedge on
  // the replay (CREATE INDEX IF NOT EXISTS checks columns before the name).
  const needsLinkTransitionsLinkSource = probe.link_transitions_exists
    && !probe.link_transitions_link_source_exists;
  return {
    needsPagesBootstrap, needsLinksBootstrap, needsChunksBootstrap, needsPagesDeletedAt,
    needsMcpLogBootstrap, needsSubagentProviderId, needsChunksEmbeddingImage, needsPagesRecency,
    needsIngestLogSourceId, needsFilesBootstrap, needsOauthClientsBootstrap, needsOauthClientsSurface,
    needsOauthClientGrants, needsSourcesArchive, needsPagesLastRetrievedAt, needsPagesProvenance,
    needsContextualRetrievalColumns, needsPagesGeneration, needsPagesEmbeddingSignature,
    needsPagesLinksExtractedAt, needsTimelineEventPageId, needsMinionJobsTimeoutAt,
    needsMinionJobsIdempotencyKey, needsMinionJobsPrivateQueue, needsMinionJobsAuthority,
    needsDreamVerdictsExpiresAt, needsFactEmbeddingIdentity, needsDerivedGenerationItemsPending,
    needsLinkTransitionsLinkSource,
  };
}

type ForwardReferenceGaps = ReturnType<typeof forwardReferenceGaps>;

/** Probe once, then add every missing forward-referenced piece of state in master's order. */
export async function applyForwardReferenceBootstrap(target: ForwardReferenceBootstrapTarget): Promise<void> {
  await assertOwnedTableShapes(sql => target.probe(sql));
  const gaps = forwardReferenceGaps(await target.probe(forwardReferenceProbeSql(target.dialect)), target.dialect);
  const {
    needsPagesBootstrap, needsLinksBootstrap, needsChunksBootstrap, needsPagesDeletedAt,
    needsMcpLogBootstrap, needsSubagentProviderId, needsChunksEmbeddingImage, needsPagesRecency,
    needsIngestLogSourceId, needsFilesBootstrap, needsOauthClientsBootstrap, needsOauthClientsSurface,
    needsOauthClientGrants, needsSourcesArchive, needsPagesLastRetrievedAt, needsPagesProvenance,
    needsContextualRetrievalColumns, needsPagesGeneration, needsPagesEmbeddingSignature,
    needsPagesLinksExtractedAt, needsTimelineEventPageId, needsMinionJobsTimeoutAt,
    needsMinionJobsIdempotencyKey, needsMinionJobsPrivateQueue, needsMinionJobsAuthority,
    needsDreamVerdictsExpiresAt, needsFactEmbeddingIdentity, needsDerivedGenerationItemsPending,
    needsLinkTransitionsLinkSource,
  } = gaps;

  if (!needsPagesBootstrap && !needsLinksBootstrap && !needsChunksBootstrap
      && !needsPagesDeletedAt && !needsMcpLogBootstrap && !needsSubagentProviderId
      && !needsChunksEmbeddingImage && !needsPagesRecency
      && !needsIngestLogSourceId && !needsFilesBootstrap
      && !needsOauthClientsBootstrap && !needsOauthClientsSurface && !needsOauthClientGrants
      && !needsSourcesArchive
      && !needsPagesLastRetrievedAt
      && !needsPagesProvenance
      && !needsContextualRetrievalColumns && !needsPagesGeneration
      && !needsPagesEmbeddingSignature
      && !needsPagesLinksExtractedAt
      && !needsTimelineEventPageId
      && !needsMinionJobsTimeoutAt && !needsMinionJobsIdempotencyKey
      && !needsMinionJobsPrivateQueue && !needsMinionJobsAuthority
      && !needsDreamVerdictsExpiresAt && !needsFactEmbeddingIdentity
      && !needsDerivedGenerationItemsPending
      && !needsLinkTransitionsLinkSource) return;

  process.stderr.write('  Schema forward-reference gap detected, applying bootstrap\n');

  await applyCoreTableGaps(target, gaps);
  await applyLaterColumnGaps(target, gaps);
}

// v39 (multimodal_dual_column_v0_27_1) adds modality + embedding_image
// columns to content_chunks plus a partial HNSW index that references
// embedding_image. Bootstrap mirrors enough state for the blob's
// `CREATE INDEX idx_chunks_embedding_image ... WHERE embedding_image IS NOT NULL`
// not to crash. v39 runs later via runMigrations and is idempotent.
const CHUNKS_EMBEDDING_IMAGE_SQL = `
    ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS modality TEXT NOT NULL DEFAULT 'text';
    ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS embedding_image vector(1024);
  `;

/** facts through sources archive: the first half of master's DDL sequence. */
async function applyCoreTableGaps(target: ForwardReferenceBootstrapTarget, gaps: ForwardReferenceGaps): Promise<void> {
  const exec = (sql: string) => target.exec(sql);
  const { dialect } = target;
  const {
    needsFactEmbeddingIdentity, needsPagesBootstrap, needsLinksBootstrap, needsChunksBootstrap,
    needsPagesDeletedAt, needsChunksEmbeddingImage, needsMcpLogBootstrap, needsSubagentProviderId,
    needsPagesRecency, needsIngestLogSourceId, needsFilesBootstrap, needsOauthClientsBootstrap,
    needsOauthClientGrants, needsOauthClientsSurface, needsSourcesArchive,
  } = gaps;

  if (needsFactEmbeddingIdentity) {
    await exec(`
      ALTER TABLE facts ADD COLUMN IF NOT EXISTS embedding_model TEXT;
      ALTER TABLE facts ADD COLUMN IF NOT EXISTS embedded_text_hash TEXT;
    `);
  }

  if (needsPagesBootstrap) {
    // Mirror the blob's `sources` shape so the subsequent blob
    // CREATE TABLE IF NOT EXISTS is a true no-op.
    // Archive columns (v34) are folded in here so a pre-v18 brain doesn't
    // need needsSourcesArchive to also fire — bootstrap creates a complete
    // v34-shape sources in one go. needsSourcesArchive then only fires on
    // the pre-v34 case (sources exists, archive cols don't).
    await exec(`
      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,
        created_at         TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      INSERT INTO sources (id, name, config)
        VALUES ('default', 'default', '{"federated": true}'::jsonb)
        ON CONFLICT (id) DO NOTHING;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS source_id TEXT
        NOT NULL DEFAULT 'default' REFERENCES sources(id) ON DELETE CASCADE;
    `);
  }

  if (needsLinksBootstrap) {
    // v11 (links_provenance_columns) handles the CHECK constraint, the
    // UNIQUE swap, and the backfill. The bootstrap only adds enough state
    // for the blob's `CREATE INDEX idx_links_source/origin` not to crash.
    // v11 runs later via runMigrations and is idempotent.
    await exec(`
      ALTER TABLE links ADD COLUMN IF NOT EXISTS link_source TEXT;
      ALTER TABLE links ADD COLUMN IF NOT EXISTS origin_page_id INTEGER
        REFERENCES pages(id) ON DELETE SET NULL;
    `);
  }

  if (needsChunksBootstrap) {
    // v26 (content_chunks_code_metadata) adds symbol_name + language; v27
    // (Cathedral II) adds parent_symbol_path + doc_comment +
    // symbol_name_qualified + search_vector. The blob has indexes
    // (idx_chunks_search_vector, idx_chunks_symbol_qualified) that need the
    // v27 columns to exist before they run. v26 + v27 run later via
    // runMigrations and are idempotent.
    await exec(`
      ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS language TEXT;
      ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS symbol_name TEXT;
      ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS parent_symbol_path TEXT[];
      ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS doc_comment TEXT;
      ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS symbol_name_qualified TEXT;
      ALTER TABLE content_chunks ADD COLUMN IF NOT EXISTS search_vector TSVECTOR;
    `);
  }

  if (needsPagesDeletedAt) {
    // v34 (destructive_guard_columns) adds the column + sources columns +
    // partial purge index. Bootstrap only adds enough for the blob's
    // `CREATE INDEX pages_deleted_at_purge_idx ... WHERE deleted_at IS NOT NULL`
    // not to crash. v34 runs later via runMigrations and is idempotent.
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
    `);
  }

  if (needsChunksEmbeddingImage && dialect.chunksEmbeddingImageStep === 'after-pages-deleted-at') {
    await exec(CHUNKS_EMBEDDING_IMAGE_SQL);
  }

  if (needsMcpLogBootstrap) {
    // v33 (admin_dashboard_columns_v0_26_3) adds agent_name + params +
    // error_message to mcp_request_log. The blob's
    // `CREATE INDEX idx_mcp_log_agent_time ON mcp_request_log(agent_name,...)`
    // crashes without agent_name. v33 runs later via runMigrations and is
    // idempotent (and also handles backfill).
    await exec(`
      ALTER TABLE mcp_request_log ADD COLUMN IF NOT EXISTS agent_name TEXT;
      ALTER TABLE mcp_request_log ADD COLUMN IF NOT EXISTS params JSONB;
      ALTER TABLE mcp_request_log ADD COLUMN IF NOT EXISTS error_message TEXT;
    `);
  }

  if (needsSubagentProviderId) {
    // v36 (subagent_provider_neutral_persistence_v0_27) adds provider_id +
    // schema_version on subagent_messages and subagent_tool_executions.
    // The blob's `CREATE INDEX idx_subagent_messages_provider ON
    // subagent_messages (job_id, provider_id)` crashes without provider_id
    // (composite-index second column). v36 runs later via runMigrations and
    // is idempotent.
    await exec(`
      ALTER TABLE subagent_messages ADD COLUMN IF NOT EXISTS provider_id TEXT;
    `);
  }

  if (needsChunksEmbeddingImage && dialect.chunksEmbeddingImageStep === 'after-subagent-provider-id') {
    await exec(CHUNKS_EMBEDDING_IMAGE_SQL);
  }

  if (needsPagesRecency) {
    // v40 (pages_emotional_weight) adds emotional_weight; v41
    // (pages_recency_columns) adds effective_date + effective_date_source +
    // import_filename + salience_touched_at and the
    // `pages_coalesce_date_idx ON pages ((COALESCE(effective_date, updated_at)))`
    // expression index. The blob's CREATE INDEX for that expression crashes
    // before v41 runs. Bootstrap adds all five additive columns; v40 + v41
    // run later via runMigrations and are idempotent.
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS emotional_weight      REAL NOT NULL DEFAULT 0.0;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS effective_date        TIMESTAMPTZ;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS effective_date_source TEXT;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS import_filename       TEXT;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS salience_touched_at   TIMESTAMPTZ;
    `);
  }

  if (needsIngestLogSourceId) {
    // v50 (ingest_log_source_id) adds source_id + the
    // idx_ingest_log_source_type_created composite index. The blob's
    // CREATE INDEX (source_id, source_type, created_at) crashes without
    // source_id. Bootstrap adds the column with NOT NULL DEFAULT 'default'
    // so the index can build cleanly.
    await exec(`
      ALTER TABLE ingest_log ADD COLUMN IF NOT EXISTS source_id TEXT NOT NULL DEFAULT 'default';
    `);
  }

  if (needsFilesBootstrap) {
    // v18 (files_provenance_columns) adds source_id + page_id to files plus
    // idx_files_source_id and idx_files_page_id in the blob. Pre-v18 brains
    // crash on the CREATE INDEX. Bootstrap adds both columns; v18 runs later
    // via runMigrations and is idempotent.
    await exec(`
      ALTER TABLE files ADD COLUMN IF NOT EXISTS source_id TEXT
        NOT NULL DEFAULT 'default' REFERENCES sources(id) ON DELETE CASCADE;
      ALTER TABLE files ADD COLUMN IF NOT EXISTS page_id INTEGER
        REFERENCES pages(id) ON DELETE SET NULL;
    `);
  }

  if (needsOauthClientsBootstrap) {
    // v60+v61+v65 (oauth_clients_source_id_fk, oauth_clients_federated_read_column,
    // oauth_clients_federated_read_gin_index) add source_id + federated_read
    // and the GIN index idx_oauth_clients_federated_read. The blob's
    // FK + index references crash on pre-v60 brains. Bootstrap mirrors the
    // v60+v61 column shape; v60-v65 run later via runMigrations and are
    // idempotent (and handle backfill + the v64 RESTRICT-flip).
    await exec(`
      ALTER TABLE oauth_clients ADD COLUMN IF NOT EXISTS source_id TEXT
        DEFAULT 'default' REFERENCES sources(id) ON DELETE SET NULL;
      ALTER TABLE oauth_clients ADD COLUMN IF NOT EXISTS federated_read TEXT[]
        NOT NULL DEFAULT '{}';
    `);
  }

  // Includes partially installed grant columns. Numbered migration 147 owns
  // policy repair and its constraints.
  if (needsOauthClientGrants) await exec(GRANT_COLUMNS_SQL);

  if (needsOauthClientsSurface) {
    // WP4 (v127): per-client MCP tool surface + operator-lock marker.
    // Nullable TEXT, no index — bootstrap mirrors the v127 column shape so
    // the blob's CREATE TABLE presence can't mask the forward reference on
    // pre-v127 brains (the v121 wedge class). v127 runs later via
    // runMigrations and is idempotent.
    await exec(`
      ALTER TABLE oauth_clients ADD COLUMN IF NOT EXISTS surface TEXT;
      ALTER TABLE oauth_clients ADD COLUMN IF NOT EXISTS surface_set_by TEXT;
    `);
  }

  if (needsSourcesArchive) {
    // v34 (destructive_guard_columns) promotes archive lifecycle from JSONB
    // config to real columns on sources. The blob's `CREATE TABLE IF NOT
    // EXISTS sources` is a no-op against an existing pre-v34 sources table,
    // so the column-add never lands until the v34 migration runs. v34's
    // UPDATE statements + downstream visibility filters (search/query/
    // list_pages) need the columns to exist on the table schema. Bootstrap
    // adds the three columns; v34 runs later via runMigrations and is
    // idempotent (and handles JSONB → column backfill).
    await exec(`
      ALTER TABLE sources ADD COLUMN IF NOT EXISTS archived BOOLEAN NOT NULL DEFAULT FALSE;
      ALTER TABLE sources ADD COLUMN IF NOT EXISTS archived_at TIMESTAMPTZ;
      ALTER TABLE sources ADD COLUMN IF NOT EXISTS archive_expires_at TIMESTAMPTZ;
    `);
  }
}

/** pages v79+ columns through minion_jobs authority: the second half of master's DDL sequence. */
async function applyLaterColumnGaps(target: ForwardReferenceBootstrapTarget, gaps: ForwardReferenceGaps): Promise<void> {
  const exec = (sql: string) => target.exec(sql);
  const {
    needsPagesLastRetrievedAt, needsPagesProvenance, needsContextualRetrievalColumns,
    needsPagesGeneration, needsPagesEmbeddingSignature, needsPagesLinksExtractedAt,
    needsTimelineEventPageId, needsMinionJobsTimeoutAt, needsMinionJobsIdempotencyKey,
    needsDreamVerdictsExpiresAt, needsMinionJobsPrivateQueue, needsMinionJobsAuthority,
    needsDerivedGenerationItemsPending, needsLinkTransitionsLinkSource,
  } = gaps;

  if (needsPagesLastRetrievedAt) {
    // v79 (pages_last_retrieved_at): adds the stale-page signal column +
    // full B-tree index. The blob's CREATE INDEX pages_last_retrieved_at_idx
    // crashes without the column. v79 runs later via runMigrations and is
    // idempotent.
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS last_retrieved_at TIMESTAMPTZ;
    `);
  }

  if (needsPagesProvenance) {
    // v81 (pages_provenance_columns): four nullable columns added by the
    // v0.38 ingestion cathedral. No blob index/FK references them today;
    // bootstrap exists defense-in-depth so future schema work that does
    // reference them doesn't wedge pre-v81 brains.
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS ingested_via TEXT;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS ingested_at TIMESTAMPTZ;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS source_uri TEXT;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS source_kind TEXT;
    `);
  }

  if (needsContextualRetrievalColumns) {
    // v0.40.3.0 v90 (contextual_retrieval_columns, renumbered from
    // v0.40.3.0 v81 on master merge). Five additive columns wiring the
    // three-tier wrapper ladder. Defense-in-depth probes; v90 runs later
    // via runMigrations and is idempotent (ADD COLUMN IF NOT EXISTS).
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS contextual_retrieval_mode TEXT;
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS corpus_generation TEXT;
      ALTER TABLE sources ADD COLUMN IF NOT EXISTS contextual_retrieval_mode TEXT;
      ALTER TABLE sources ADD COLUMN IF NOT EXISTS trust_frontmatter_overrides BOOLEAN NOT NULL DEFAULT FALSE;
    `);
  }

  if (needsPagesGeneration) {
    // v0.40.3.0 v91 (pages_generation_trigger_and_bookmark):
    // pages.generation BIGINT. The blob's CREATE INDEX pages_generation_idx
    // ON pages (generation) crashes on pre-v91 brains without this. The
    // trigger and index land via v91 migration run later; bootstrap only
    // adds the column. v91 is idempotent.
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS generation BIGINT NOT NULL DEFAULT 1;
    `);
  }

  if (needsPagesEmbeddingSignature) {
    // v108 (pages_embedding_signature): embedding provenance for real stale
    // semantics. NULL grandfathered (never stale). v108 runs later via
    // runMigrations and is idempotent.
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS embedding_signature TEXT;
    `);
  }

  if (needsPagesLinksExtractedAt) {
    // v112 (pages_links_extracted_at): link-extraction freshness watermark.
    // pages_links_extracted_at_idx in the blob references it, so bootstrap
    // adds the column before the blob's CREATE INDEX runs. The index itself
    // lands via the blob (CREATE INDEX IF NOT EXISTS) and v112; bootstrap
    // only adds the column. v112 runs later via runMigrations and is
    // idempotent.
    await exec(`
      ALTER TABLE pages ADD COLUMN IF NOT EXISTS links_extracted_at TIMESTAMPTZ;
    `);
  }

  if (needsTimelineEventPageId) {
    // Add only the forward-referenced column. Migration v121 remains the
    // source of truth for the FK and indexes and runs idempotently afterward.
    await exec(`
      ALTER TABLE timeline_entries ADD COLUMN IF NOT EXISTS event_page_id INTEGER;
    `);
  }

  if (needsMinionJobsTimeoutAt) {
    // v7: blob index idx_minion_jobs_timeout references timeout_at; a
    // pre-v7 minion_jobs wedges blob replay without it (same class as v121).
    await exec(`
      ALTER TABLE minion_jobs ADD COLUMN IF NOT EXISTS timeout_at TIMESTAMPTZ;
    `);
  }
  if (needsMinionJobsIdempotencyKey) {
    // v7: blob index uniq_minion_jobs_idempotency references idempotency_key.
    await exec(`
      ALTER TABLE minion_jobs ADD COLUMN IF NOT EXISTS idempotency_key TEXT;
    `);
  }
  // dialect-only:postgres begin
  if (needsDreamVerdictsExpiresAt) {
    // Nullable column + DEFAULT as TWO statements, deliberately:
    //  - a single `ADD COLUMN ... DEFAULT` would stamp EXISTING rows via
    //    PG11 fast-defaults, destroying v143's judged_at-derived backfill
    //    (pre-TTL rows must keep their original age);
    //  - SET DEFAULT after ADD affects only NEW rows, so a legacy writer
    //    racing the upgrade window can't insert NULLs (and getDreamVerdict's
    //    read predicate is NULL-tolerant for rows older than the default).
    // Migration v143 stays the source of truth for the backfill, SET NOT
    // NULL, and the index. On the engine path it runs right after bootstrap
    // under the same advisory lock; on the standalone db.ts:initSchema path
    // migrations do NOT run — the column stays nullable there until the next
    // full engine init, which the NULL-tolerant read predicate makes safe.
    await exec(`
      ALTER TABLE dream_verdicts ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ;
      ALTER TABLE dream_verdicts ALTER COLUMN expires_at SET DEFAULT (now() + interval '30 days');
    `);
  }
  // dialect-only:postgres end
  if (needsMinionJobsPrivateQueue) {
    // v0.46.26: blob indexes idx_minion_jobs_private_queue_recovery /
    // idx_minion_jobs_private_queue_owner reference the private-queue
    // owner/lease columns; a pre-upgrade minion_jobs wedges blob replay
    // without them (same class as v121). The token column is not indexed
    // but rides along so upgraded rows carry the full lifecycle shape.
    await exec(`
      ALTER TABLE minion_jobs ADD COLUMN IF NOT EXISTS private_queue_owner_job_id INTEGER REFERENCES minion_jobs(id) ON DELETE SET NULL;
      ALTER TABLE minion_jobs ADD COLUMN IF NOT EXISTS private_queue_owner_token TEXT;
      ALTER TABLE minion_jobs ADD COLUMN IF NOT EXISTS private_queue_lease_until TIMESTAMPTZ;
    `);
  }
  if (needsMinionJobsAuthority) {
    // Metadata only. Migration v149 owns the cutover guard; local explicit
    // review is the only path that may assign authority to historical rows.
    await exec(`
      ALTER TABLE minion_jobs ADD COLUMN IF NOT EXISTS submission_authority JSONB;
      ALTER TABLE minion_jobs ADD COLUMN IF NOT EXISTS claim_generation BIGINT NOT NULL DEFAULT 0;
    `);
  }

  if (needsDerivedGenerationItemsPending) {
    // Column only, exactly as v266 adds it (existing items are not pending);
    // v266 still runs and owns the index and the activations table.
    await exec(`
      ALTER TABLE derived_generation_items ADD COLUMN IF NOT EXISTS pending BOOLEAN NOT NULL DEFAULT false;
    `);
  }

  if (needsLinkTransitionsLinkSource) {
    // Column only, exactly as v302 adds it (nullable, no default: metadata only);
    // v302 still runs and swaps the identity index online.
    await exec(`
      ALTER TABLE link_transitions ADD COLUMN IF NOT EXISTS link_source TEXT;
    `);
  }
}