Skip to content

Local-first 🧠 memory for Hermes Agent that lives in your Obsidian vault and remembers project context. Nightly 😴 dream passes turn repeat corrections into confirmed preferences with measurable confidence. Adapters ship for Claude Code, Codex, and OpenClaw, with an MCP server for anything else.

TypeScript
63.2 KB
Raw Blame
/**
 * DDL and migrations for the search index. The SQLite database itself
 * is managed by `store.ts`; this module is data + pure functions over
 * a connection so future minor versions can append migrations without
 * touching the store class.
 *
 * Anchored in docs/plans/2026-05-16-brain-search-design.md §5.
 */

import { Database } from "bun:sqlite";

import { SearchError } from "./types.ts";
import { keepWriterLeasesFresh, WRITER_LEASE_BLOCKING_BUDGET_MS } from "./store/writer-lock.ts";
import { hasPrivacyMarkerKey, remoteDenyVisibilityTokens } from "../graph/privacy-markers.ts";
import { parseFrontmatterText } from "../vault.ts";
import { closeDatabase } from "../sqlite-close.ts";
import { BRAIN_ROOT_REL } from "../brain/path-constants.ts";
import { newUlid } from "../store/ulid.ts";
import {
  DOCUMENT_SOURCE,
  STORE_INDEX_GENERATION_KEY,
  STORE_RECORD_PATH_PREFIX,
} from "./brain-store/constants.ts";

/**
 * Latest schema version this code understands. A DB with a value above
 * this raises `SCHEMA_MISMATCH` on open — the operator must reindex
 * with a newer binary.
 */
export const LATEST_SCHEMA_VERSION = 17;

/**
 * The one command that rebuilds an index this binary cannot read. Every
 * refusal on the open path names it, and names it identically.
 */
export const REINDEX_COMMAND = "o2b search reindex";

/**
 * The wikilink-resolution basename of a stored document path: the final
 * `/`-segment with a trailing `.md` stripped. `notes/alpha.md` → `alpha`,
 * top-level `alpha.md` → `alpha`. Persisted in `documents.basename` so
 * dangling-link resolution can equality-join an index instead of
 * `SUBSTR`-scanning every path. Must stay in lockstep with the resolution
 * ladder in `store.ts` (`<target>.md` exact match, then basename suffix).
 */
export function documentBasename(path: string): string {
  const slash = path.lastIndexOf("/");
  const name = slash >= 0 ? path.slice(slash + 1) : path;
  return name.endsWith(".md") ? name.slice(0, -".md".length) : name;
}

/**
 * The historical, byte-stable FTS5 tokenizer clause. When no tokenizer
 * config is set, {@link buildFtsTokenize} returns exactly this string so
 * the generated `chunk_fts` schema is identical to every prior release.
 */
export const DEFAULT_FTS_TOKENIZE = "unicode61 remove_diacritics 2";

/** Allowed `remove_diacritics` values for the unicode61 base tokenizer. */
export const FTS_DIACRITICS_ALLOWED: ReadonlyArray<string> = Object.freeze(["0", "1", "2"]);

/** Allowed stemmer selectors layered over the unicode61 base tokenizer. */
export const FTS_STEMMER_ALLOWED: ReadonlyArray<string> = Object.freeze(["none", "porter"]);

/** Validated inputs for {@link buildFtsTokenize}. A null/absent value uses the default. */
export interface FtsTokenizerOptions {
  /** `remove_diacritics` rule: "0" | "1" | "2". Absent/null = "2". */
  readonly diacritics?: string | null;
  /** Language stemmer: "none" | "porter". Absent/null = "none". */
  readonly stemmer?: string | null;
}

/**
 * Assemble the FTS5 tokenizer clause from validated config options. The
 * clause is composed structurally from a fixed allow-list of FTS5
 * tokenizer options (never a natural-language word list), so an operator
 * can only ever produce a valid SQLite tokenizer directive; an
 * out-of-range value rejects with a typed `SearchError` listing the
 * allowed values rather than reaching the DDL.
 *
 * With no options set the return value is byte-identical to
 * {@link DEFAULT_FTS_TOKENIZE}. Changing it takes effect only on the next
 * `o2b search reindex`; there is no implicit reindex.
 */
export function buildFtsTokenize(opts: FtsTokenizerOptions): string {
  const diacritics = opts.diacritics ?? "2";
  if (!FTS_DIACRITICS_ALLOWED.includes(diacritics)) {
    throw new SearchError(
      "INVALID_INPUT",
      `search_fts_diacritics must be one of ${FTS_DIACRITICS_ALLOWED.join(", ")}; got '${diacritics}'`,
    );
  }
  const stemmer = opts.stemmer ?? "none";
  if (!FTS_STEMMER_ALLOWED.includes(stemmer)) {
    throw new SearchError(
      "INVALID_INPUT",
      `search_fts_stemmer must be one of ${FTS_STEMMER_ALLOWED.join(", ")}; got '${stemmer}'`,
    );
  }
  const base = `unicode61 remove_diacritics ${diacritics}`;
  // The porter stemmer WRAPS a base tokenizer in FTS5 syntax, so it
  // prefixes the unicode61 directive rather than replacing it.
  return stemmer === "porter" ? `porter ${base}` : base;
}

/** Context threaded into every migration's `up`. */
export interface MigrationContext {
  /** FTS5 tokenizer clause for the searchable `chunk_fts` table. */
  readonly ftsTokenize: string;
}

function ddlV1(ftsTokenize: string): string {
  return `
CREATE TABLE IF NOT EXISTS documents (
  id            INTEGER PRIMARY KEY,
  path          TEXT NOT NULL UNIQUE,
  title         TEXT,
  content_hash  TEXT NOT NULL,
  mtime         INTEGER NOT NULL,
  size          INTEGER NOT NULL,
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL,
  indexed_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_documents_path ON documents(path);
CREATE INDEX IF NOT EXISTS idx_documents_mtime ON documents(mtime);

CREATE TABLE IF NOT EXISTS chunks (
  id            INTEGER PRIMARY KEY,
  document_id   INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  chunk_index   INTEGER NOT NULL,
  content       TEXT NOT NULL,
  content_hash  TEXT NOT NULL,
  start_line    INTEGER NOT NULL,
  end_line      INTEGER NOT NULL,
  token_count   INTEGER NOT NULL,
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL,
  UNIQUE(document_id, chunk_index)
);
CREATE INDEX IF NOT EXISTS idx_chunks_document ON chunks(document_id);

CREATE VIRTUAL TABLE IF NOT EXISTS chunk_fts USING fts5(
  content,
  content='chunks',
  content_rowid='id',
  tokenize='${ftsTokenize}'
);

CREATE TRIGGER IF NOT EXISTS chunks_ai AFTER INSERT ON chunks BEGIN
  INSERT INTO chunk_fts(rowid, content) VALUES (new.id, new.content);
END;
CREATE TRIGGER IF NOT EXISTS chunks_ad AFTER DELETE ON chunks BEGIN
  INSERT INTO chunk_fts(chunk_fts, rowid, content) VALUES('delete', old.id, old.content);
END;
CREATE TRIGGER IF NOT EXISTS chunks_au AFTER UPDATE ON chunks BEGIN
  INSERT INTO chunk_fts(chunk_fts, rowid, content) VALUES('delete', old.id, old.content);
  INSERT INTO chunk_fts(rowid, content) VALUES (new.id, new.content);
END;

CREATE TABLE IF NOT EXISTS embeddings (
  chunk_id        INTEGER PRIMARY KEY REFERENCES chunks(id) ON DELETE CASCADE,
  model           TEXT NOT NULL,
  dimension       INTEGER NOT NULL,
  embedding_hash  TEXT NOT NULL,
  created_at      TEXT NOT NULL,
  updated_at      TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_embeddings_model ON embeddings(model);

CREATE TABLE IF NOT EXISTS chunk_vec_map (
  chunk_id   INTEGER PRIMARY KEY REFERENCES chunks(id) ON DELETE CASCADE,
  vec_rowid  INTEGER NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS links (
  id                  INTEGER PRIMARY KEY,
  source_document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  source_chunk_id     INTEGER REFERENCES chunks(id) ON DELETE CASCADE,
  target_path         TEXT,
  target_document_id  INTEGER REFERENCES documents(id) ON DELETE SET NULL,
  link_text           TEXT,
  link_type           TEXT NOT NULL CHECK(link_type IN ('wikilink','markdown_link','tag')),
  created_at          TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_links_source ON links(source_document_id);
CREATE INDEX IF NOT EXISTS idx_links_target_doc ON links(target_document_id);
CREATE INDEX IF NOT EXISTS idx_links_target_path ON links(target_path);

CREATE TABLE IF NOT EXISTS index_state (
  key         TEXT PRIMARY KEY,
  value       TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
`;
}

interface Migration {
  readonly version: number;
  readonly up: (db: Database, ctx: MigrationContext) => void;
}

/**
 * v2 (v0.13.0) - recall-quality backing store:
 *
 *   - parallel `chunk_entities` table feeding entity-boosted retrieval;
 *   - `chunks.heading_path` column plus a rebuilt two-column
 *     `chunk_fts` (content, heading_path) so a chunk's heading
 *     breadcrumb is searchable without polluting the display content.
 *
 * Existing rows carry no entities and an empty heading_path until a
 * reindex repopulates them, so those two layers contribute nothing to
 * scoring until then. (The MMR and traversal recall layers are enabled
 * by default and do change ordering versus prior versions - v0.13.0 is
 * a deliberate ranking change and requires `o2b search reindex`.) The
 * FTS rebuild reindexes whatever chunks already exist against the new
 * column layout.
 */
const DDL_V2_ENTITIES = `
CREATE TABLE IF NOT EXISTS chunk_entities (
  chunk_id   INTEGER NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  entity     TEXT NOT NULL,
  PRIMARY KEY (chunk_id, entity)
);
CREATE INDEX IF NOT EXISTS idx_chunk_entities_entity ON chunk_entities(entity);
`;

// FTS rebuild is split from the column add because `ALTER TABLE ADD
// COLUMN` is not idempotent; the column add is guarded in `up()`.
function ddlV2Fts(ftsTokenize: string): string {
  return `
DROP TRIGGER IF EXISTS chunks_ai;
DROP TRIGGER IF EXISTS chunks_ad;
DROP TRIGGER IF EXISTS chunks_au;
DROP TABLE IF EXISTS chunk_fts;

CREATE VIRTUAL TABLE chunk_fts USING fts5(
  content,
  heading_path,
  content='chunks',
  content_rowid='id',
  tokenize='${ftsTokenize}'
);

CREATE TRIGGER chunks_ai AFTER INSERT ON chunks BEGIN
  INSERT INTO chunk_fts(rowid, content, heading_path)
    VALUES (new.id, new.content, new.heading_path);
END;
CREATE TRIGGER chunks_ad AFTER DELETE ON chunks BEGIN
  INSERT INTO chunk_fts(chunk_fts, rowid, content, heading_path)
    VALUES('delete', old.id, old.content, old.heading_path);
END;
CREATE TRIGGER chunks_au AFTER UPDATE ON chunks BEGIN
  INSERT INTO chunk_fts(chunk_fts, rowid, content, heading_path)
    VALUES('delete', old.id, old.content, old.heading_path);
  INSERT INTO chunk_fts(rowid, content, heading_path)
    VALUES (new.id, new.content, new.heading_path);
END;

INSERT INTO chunk_fts(chunk_fts) VALUES('rebuild');
`;
}

export const MIGRATIONS: ReadonlyArray<Migration> = Object.freeze([
  {
    version: 1,
    up(db, ctx) {
      db.exec(ddlV1(ctx.ftsTokenize));
    },
  },
  {
    version: 2,
    up(db, ctx) {
      db.exec(DDL_V2_ENTITIES);
      const cols = db.query<{ name: string }, []>("PRAGMA table_info(chunks)").all();
      if (!cols.some((c) => c.name === "heading_path")) {
        db.exec("ALTER TABLE chunks ADD COLUMN heading_path TEXT NOT NULL DEFAULT ''");
      }
      db.exec(ddlV2Fts(ctx.ftsTokenize));
    },
  },
  {
    // v3 (typed graph semantics) - a nullable `relation` column on
    // `links` so an edge can carry a semantic relation type orthogonal
    // to its syntactic `link_type`. No CHECK constraint: the relation
    // vocabulary lives in the application layer
    // (src/core/graph/relation-vocab.ts), so adding a relation type is a
    // one-line change, never a migration. Existing rows keep a NULL
    // relation until a reindex repopulates frontmatter-derived edges.
    version: 3,
    up(db) {
      const cols = db.query<{ name: string }, []>("PRAGMA table_info(links)").all();
      if (!cols.some((c) => c.name === "relation")) {
        db.exec("ALTER TABLE links ADD COLUMN relation TEXT");
      }
    },
  },
  {
    // v4 (v0.20.0) - persistent query cache. `query_cache` stores a
    // serialized search result keyed by a hash of the result-affecting
    // request, tagged with the corpus generation it was computed under
    // and a creation timestamp for TTL. The generation tag is what makes
    // the cache self-invalidating: a row whose generation no longer
    // matches the current one is never served and is swept. Additive and
    // reindex-safe; existing data is untouched.
    version: 4,
    up(db) {
      db.exec(`
        CREATE TABLE IF NOT EXISTS query_cache (
          cache_key   TEXT PRIMARY KEY,
          generation  TEXT NOT NULL,
          payload     TEXT NOT NULL,
          created_at  INTEGER NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_query_cache_generation ON query_cache(generation);
      `);
    },
  },
  {
    // v5 (CJK search) - keep display content untouched while indexing
    // an expanded FTS shadow column. Existing rows default to their
    // current content until a reindex computes CJK token expansions.
    version: 5,
    up(db, ctx) {
      const cols = db.query<{ name: string }, []>("PRAGMA table_info(chunks)").all();
      if (!cols.some((c) => c.name === "fts_content")) {
        db.exec("ALTER TABLE chunks ADD COLUMN fts_content TEXT NOT NULL DEFAULT ''");
        db.exec("UPDATE chunks SET fts_content = content WHERE fts_content = ''");
      }
      db.exec(`
        DROP TRIGGER IF EXISTS chunks_ai;
        DROP TRIGGER IF EXISTS chunks_ad;
        DROP TRIGGER IF EXISTS chunks_au;
        DROP TABLE IF EXISTS chunk_fts;

        CREATE VIRTUAL TABLE chunk_fts USING fts5(
          fts_content,
          heading_path,
          content='chunks',
          content_rowid='id',
          tokenize='${ctx.ftsTokenize}'
        );

        CREATE TRIGGER chunks_ai AFTER INSERT ON chunks BEGIN
          INSERT INTO chunk_fts(rowid, fts_content, heading_path)
            VALUES (new.id, new.fts_content, new.heading_path);
        END;
        CREATE TRIGGER chunks_ad AFTER DELETE ON chunks BEGIN
          INSERT INTO chunk_fts(chunk_fts, rowid, fts_content, heading_path)
            VALUES('delete', old.id, old.fts_content, old.heading_path);
        END;
        CREATE TRIGGER chunks_au AFTER UPDATE ON chunks BEGIN
          INSERT INTO chunk_fts(chunk_fts, rowid, fts_content, heading_path)
            VALUES('delete', old.id, old.fts_content, old.heading_path);
          INSERT INTO chunk_fts(rowid, fts_content, heading_path)
            VALUES (new.id, new.fts_content, new.heading_path);
        END;

        INSERT INTO chunk_fts(chunk_fts) VALUES('rebuild');
      `);
    },
  },
  {
    // v6 (write-time-integrity-governance) - link-constraint enforcement
    // needs both endpoint page types at materialization time, so the
    // document's declared frontmatter `type` is persisted alongside it,
    // and a typed edge gains a `relation_blocked` flag the indexer's
    // post-pass recomputes from the current schema pack on every run.
    // The tier guard additionally snapshots a framework file's tiered
    // frontmatter fields (`tier_snapshot`) and stages identity-field
    // hand-edits in `tier_drift` for `o2b brain tiers check|restore`.
    // All additive and reindex-safe: existing rows default to NULL
    // page type (constraints cannot evaluate - allowed), unblocked
    // edges, and no snapshot (first reindex seeds it).
    version: 6,
    up(db) {
      const docCols = db.query<{ name: string }, []>("PRAGMA table_info(documents)").all();
      if (!docCols.some((c) => c.name === "page_type")) {
        db.exec("ALTER TABLE documents ADD COLUMN page_type TEXT");
      }
      if (!docCols.some((c) => c.name === "tier_snapshot")) {
        db.exec("ALTER TABLE documents ADD COLUMN tier_snapshot TEXT");
      }
      const linkCols = db.query<{ name: string }, []>("PRAGMA table_info(links)").all();
      if (!linkCols.some((c) => c.name === "relation_blocked")) {
        db.exec("ALTER TABLE links ADD COLUMN relation_blocked INTEGER NOT NULL DEFAULT 0");
      }
      db.exec(`
        CREATE TABLE IF NOT EXISTS tier_drift (
          id           INTEGER PRIMARY KEY,
          document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
          field        TEXT NOT NULL,
          expected     TEXT NOT NULL,
          actual       TEXT NOT NULL,
          detected_at  TEXT NOT NULL,
          UNIQUE(document_id, field)
        );
        CREATE INDEX IF NOT EXISTS idx_tier_drift_document ON tier_drift(document_id);
      `);
    },
  },
  {
    // v7 (link-recall-intelligence) - vault-wide frontmatter alias
    // resolution at materialization time. The indexer extracts a
    // note's `aliases:` array (NFC-normalised, lower-cased) into
    // `doc_aliases`; `resolveAliasTargets` then materializes
    // `target_document_id` for unresolved slash-free wikilink
    // targets that match an alias. Additive and reindex-safe:
    // existing rows simply have no aliases until a reindex
    // repopulates them.
    version: 7,
    up(db) {
      db.exec(`
        CREATE TABLE IF NOT EXISTS doc_aliases (
          document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
          alias        TEXT NOT NULL,
          UNIQUE(document_id, alias)
        );
        CREATE INDEX IF NOT EXISTS idx_doc_aliases_alias ON doc_aliases(alias);
      `);
    },
  },
  {
    // v8 (hot-path-performance) - a persisted `documents.basename` column
    // (final path segment, `.md` stripped) with an index, so
    // dangling-wikilink resolution (`resolvedDocLinkPairs`,
    // `resolveAliasTargets`) can equality-join `idx_documents_basename`
    // instead of `SUBSTR`-scanning every `documents.path` twice per
    // unresolved link. The column is backfilled from the existing paths
    // in-place, so a migrated (not-yet-reindexed) index resolves links
    // identically at once; a full reindex repopulates it via
    // `upsertDocument`.
    version: 8,
    up(db) {
      const docCols = db.query<{ name: string }, []>("PRAGMA table_info(documents)").all();
      if (!docCols.some((c) => c.name === "basename")) {
        db.exec("ALTER TABLE documents ADD COLUMN basename TEXT");
      }
      db.exec("CREATE INDEX IF NOT EXISTS idx_documents_basename ON documents(basename)");
      const rows = db
        .query<{ id: number; path: string }, []>(
          "SELECT id, path FROM documents WHERE basename IS NULL",
        )
        .all();
      const update = db.prepare<undefined, [string, number]>(
        "UPDATE documents SET basename = ? WHERE id = ?",
      );
      for (const row of rows) update.run(documentBasename(row.path), row.id);
    },
  },
  {
    // v9 (Retrieval & Ranking Quality) - opt-in trigram candidate index.
    // A second FTS5 shadow over the same `fts_content` column, tokenized
    // with the built-in `trigram` tokenizer, so a query can gather
    // substring / partial-token candidates that the `unicode61` word
    // tokenizer misses. Read only when `search_trigram_prefilter_enabled`
    // is on; otherwise it is inert extra index data and result ordering is
    // byte-identical. Additive and reindex-safe: `rebuild` populates it
    // from existing chunks in-place, and dedicated triggers keep it in
    // sync. No new dependency - the trigram tokenizer ships with SQLite.
    version: 9,
    up(db) {
      db.exec(`
        DROP TRIGGER IF EXISTS chunks_tri_ai;
        DROP TRIGGER IF EXISTS chunks_tri_ad;
        DROP TRIGGER IF EXISTS chunks_tri_au;
        DROP TABLE IF EXISTS chunk_trigram;

        CREATE VIRTUAL TABLE chunk_trigram USING fts5(
          fts_content,
          content='chunks',
          content_rowid='id',
          tokenize='trigram'
        );

        CREATE TRIGGER chunks_tri_ai AFTER INSERT ON chunks BEGIN
          INSERT INTO chunk_trigram(rowid, fts_content) VALUES (new.id, new.fts_content);
        END;
        CREATE TRIGGER chunks_tri_ad AFTER DELETE ON chunks BEGIN
          INSERT INTO chunk_trigram(chunk_trigram, rowid, fts_content)
            VALUES('delete', old.id, old.fts_content);
        END;
        CREATE TRIGGER chunks_tri_au AFTER UPDATE ON chunks BEGIN
          INSERT INTO chunk_trigram(chunk_trigram, rowid, fts_content)
            VALUES('delete', old.id, old.fts_content);
          INSERT INTO chunk_trigram(rowid, fts_content) VALUES (new.id, new.fts_content);
        END;

        INSERT INTO chunk_trigram(chunk_trigram) VALUES('rebuild');
      `);
    },
  },
  {
    // v10 (conversation chronology, t_347e8224) - a nullable
    // `documents.authored_at` column (unix seconds) carrying the
    // transcript turn instant a session-imported note was authored at.
    // Surfaced on search results and used to break EXACT hybrid-score
    // ties toward more recent statements. Additive and reindex-safe:
    // existing rows default to NULL (no turn instant) until a reindex
    // repopulates it from the `authored_at` frontmatter field, so a vault
    // with no transcript-authored notes ranks byte-identically.
    version: 10,
    up(db) {
      const docCols = db.query<{ name: string }, []>("PRAGMA table_info(documents)").all();
      if (!docCols.some((c) => c.name === "authored_at")) {
        db.exec("ALTER TABLE documents ADD COLUMN authored_at INTEGER");
      }
    },
  },
  {
    // v11 (provenance at the boundary, t_ac1c4176) - the materialised
    // EVENT ANCHOR: the interval a note is ABOUT, resolved from its own
    // frontmatter or body by `event-anchor.ts`, plus the registered token
    // naming which rung of that ladder produced it. Materialised here so
    // the query side never re-scans a candidate's body.
    //
    // Naming: `event_anchor_*`, deliberately NOT `created_at`-adjacent.
    // `documents.created_at` on this same table is the row-INSERT
    // timestamp and has nothing to do with the `created_at` FRONTMATTER
    // key that is one rung of the anchor ladder; a column named after
    // either would be read as the other. The `_ms` suffix is likewise
    // load-bearing: `mtime` and `authored_at` beside it are unix SECONDS,
    // and a day-end bound truncated to whole seconds would move every
    // interval comparison by almost a second.
    //
    // Additive and reindex-safe: existing rows default to NULL on all
    // three until a reindex repopulates them, so a vault whose notes
    // declare no dates ranks byte-identically.
    //
    // `event_anchor_examined` is the fourth column and the one that
    // makes the other three honest. This migration runs IN PLACE - a
    // schema bump reindexes nothing, and the indexer resolves an anchor
    // only below its two content-identity fastpaths, so a document
    // carried over from v10 never has one computed: its content does not
    // change, so it is never re-read. Without this marker that row's
    // three NULLs are indistinguishable from a note that was examined
    // and declares no date, and the column now gates a hard `since` /
    // `until` filter. `0` means "no anchor-aware binary has ever looked
    // at this document", which is what every pre-existing row is and
    // what `o2b search event-anchor-backfill` finds.
    version: 11,
    up(db) {
      const docCols = db.query<{ name: string }, []>("PRAGMA table_info(documents)").all();
      if (!docCols.some((c) => c.name === "event_anchor_start_ms")) {
        db.exec("ALTER TABLE documents ADD COLUMN event_anchor_start_ms INTEGER");
      }
      if (!docCols.some((c) => c.name === "event_anchor_end_ms")) {
        db.exec("ALTER TABLE documents ADD COLUMN event_anchor_end_ms INTEGER");
      }
      if (!docCols.some((c) => c.name === "event_anchor_source")) {
        db.exec("ALTER TABLE documents ADD COLUMN event_anchor_source TEXT");
      }
      if (!docCols.some((c) => c.name === "event_anchor_examined")) {
        db.exec(
          "ALTER TABLE documents ADD COLUMN event_anchor_examined INTEGER NOT NULL DEFAULT 0",
        );
      }
    },
  },
  {
    // v12 (private-is-not-a-suggestion) - what the INDEX could measure of
    // each page's `visibility:` frontmatter, materialised so a diagnostic
    // does not have to scan every chunk body to answer it.
    //
    // Three states, all three distinct:
    //
    //   - a JSON array of the page's normalised tokens - measured;
    //   - `[]` - measured, and the page declares none;
    //   - NULL - this document has no chunk zero at all, so the index
    //     holds nothing to measure. Not a synonym for `[]`.
    //
    // The BACKFILL is the whole of why this migration is more than an
    // `ALTER TABLE`. A column added without one is NULL for every
    // existing row, and treating NULL as reserved - which is the only
    // fail-closed reading of "unmeasured" - would withhold every document
    // in every vault on upgrade, including the overwhelming majority that
    // never used the field. The frontmatter is already IN the index:
    // `chunker.ts`'s `packBlocks` emits a page's frontmatter block as its
    // own chunk, verbatim, at `chunk_index = 0`. So the backfill is one
    // table scan and a parse per document, with no vault walk and no
    // operator action.
    //
    // It runs INSIDE this migration's transaction, and the reason is
    // atomicity rather than speed: a half-filled column leaves measurable
    // rows reading as unmeasured, which is the blackout this backfill
    // exists to prevent, and a crash mid-pass would leave exactly that.
    //
    // RECORDED LIMIT: an UNTERMINATED frontmatter block is measured as
    // `[]` rather than as NULL. The design expected NULL, and the index
    // cannot produce it: `chunker.ts`'s `readFrontmatter` drops the
    // opening `---` and keeps the rest as body, so chunk zero of a page
    // with an unterminated block is byte-indistinguishable from chunk
    // zero of a page with no frontmatter at all. It does not matter,
    // because `parseFrontmatterText` reads an unterminated block on the
    // REAL FILE as declaring nothing too - so the column and the live
    // boundary at the three read roots agree, rather than one of them
    // being wrong in private.
    version: 12,
    up(db) {
      const docCols = db.query<{ name: string }, []>("PRAGMA table_info(documents)").all();
      if (!docCols.some((c) => c.name === DOCUMENT_VISIBILITY_COLUMN)) {
        db.exec(`ALTER TABLE documents ADD COLUMN ${DOCUMENT_VISIBILITY_COLUMN} TEXT`);
      }
      backfillDocumentVisibility(db);
    },
  },
  {
    // v13 (persisted, countable event-time bounds + pinned, t_9e1a4b3f /
    // t_f7bef96a schema half) - three nullable columns on `documents`:
    //
    //   - `event_time_min` / `event_time_max`: the document's RESOLVED
    //     event-time window in unix ms, as the shared rung order
    //     (frontmatter validity window > event anchor - admitted rungs
    //     only) resolves it at index time via
    //     `resolveDocumentEventTimeWindow`. Distinct from the
    //     `event_anchor_*` columns beside them: those are the raw
    //     materialised anchor (which rungs are recorded but never
    //     judged), these are the bounds a time-ranged query actually
    //     judges by, persisted so the store can aggregate them instead
    //     of the query side re-deriving them per query.
    //
    //     One side NULL is a DECLARED open window (open start / open
    //     end). Both NULL is the unmeasured state, and it is deliberately
    //     ambiguous: either the row predates this migration and the lazy
    //     backfill has not refreshed it, or the document declares nothing
    //     the resolver can use. Both fall back to storage mtime at query
    //     time, and the window census names that bucket by count rather
    //     than pretending it is zero - unlike v11/v12, no examined-marker
    //     column is spent on separating the two, because nothing reads
    //     them differently.
    //
    //   - `pinned`: the page's per-note `pinned` frontmatter flag,
    //     persisted at index time so ranking can consume it without
    //     re-reading files. 1 = pinned, 0 = not pinned, NULL = not yet
    //     persisted (same lazy story as the bounds).
    //
    // Additive and reindex-safe: existing rows keep NULLs until their
    // next content change refreshes them through the indexer's document
    // loop, so a vault whose notes declare no dates and pin nothing
    // behaves byte-identically.
    version: 13,
    up(db) {
      const docCols = db.query<{ name: string }, []>("PRAGMA table_info(documents)").all();
      if (!docCols.some((c) => c.name === EVENT_TIME_MIN_COLUMN)) {
        db.exec(`ALTER TABLE documents ADD COLUMN ${EVENT_TIME_MIN_COLUMN} INTEGER`);
      }
      if (!docCols.some((c) => c.name === EVENT_TIME_MAX_COLUMN)) {
        db.exec(`ALTER TABLE documents ADD COLUMN ${EVENT_TIME_MAX_COLUMN} INTEGER`);
      }
      if (!docCols.some((c) => c.name === PINNED_COLUMN)) {
        db.exec(`ALTER TABLE documents ADD COLUMN ${PINNED_COLUMN} INTEGER`);
      }
    },
  },
  {
    // v14 (recall-quality, t_71cbf5bf + t_82851eda) - the query-path
    // taxonomy: a kind discriminator on chunks, two indexes the pushed
    // predicates read, and the bounded document-attributes side table.
    //
    //   - `chunks.chunk_kind`: a plain column - 'page' for the page-HEAD
    //     chunk (the first content chunk: chunk 1 when the document has a
    //     frontmatter block at chunk 0, chunk 0 itself otherwise), 'section'
    //     for everything else. A VIRTUAL expression over chunk_index alone
    //     (the first draft) would have made `kind: page` match the YAML
    //     block and miss the note's intro, so the writer now decides from
    //     structure (the same head rule {@link pageHeadIndex} encodes) and
    //     the migration backfills existing rows by measuring chunk 0's
    //     content; a re-index rewrites the column authoritatively. Indexed
    //     by `idx_chunks_kind` for the `kind` filter pushdown.
    //
    //   - `idx_documents_page_type`: the `type` frontmatter key has been
    //     materialized in `documents.page_type` since v6; the index lets
    //     a promoted `type` property filter become a b-tree range instead
    //     of a per-row judgement.
    //
    //   - `document_attributes(document_id, key, value)`: the bounded,
    //     sanitized frontmatter snapshot (t_82851eda). Backfilled from
    //     chunk 0 by the v12 recipe - the frontmatter is already IN the
    //     index, so the backfill is one table scan and a parse per
    //     document, with no vault walk. The PROMOTED keys (`type`,
    //     `tags`, `pinned`, `visibility`, `aliases`) are skipped: each
    //     keeps its dedicated column or table as the single source of
    //     truth, and a second copy of the same fact is drift waiting to
    //     happen. Bounds are mandatory or a hostile frontmatter bloats
    //     the index; exceeding one DECREMENTS the attribute set and never
    //     fails the index (see
    //     {@link documentAttributesFromFrontmatter}).
    //
    // Reindex-safe: existing rows keep every pre-v14 answer; the new
    // predicates only sharpen reads that ask for them. The migration runs
    // inside `applyMigrations`' single open transaction, so a crash
    // mid-backfill rolls the whole entry back atomically.
    version: 14,
    up(db) {
      // A generated column is invisible to `PRAGMA table_info` on some
      // SQLite builds; `table_xinfo` lists every column including the
      // generated ones, so the guard cannot double-ALTER.
      const chunkCols = db.query<{ name: string }, []>("PRAGMA table_xinfo(chunks)").all();
      if (!chunkCols.some((c) => c.name === CHUNK_KIND_COLUMN)) {
        db.exec(`ALTER TABLE chunks ADD COLUMN ${CHUNK_KIND_COLUMN} TEXT`);
      }
      backfillChunkKind(db);
      db.exec("CREATE INDEX IF NOT EXISTS idx_chunks_kind ON chunks(chunk_kind)");
      db.exec("CREATE INDEX IF NOT EXISTS idx_documents_page_type ON documents(page_type)");
      // Child-table shape per the doc_aliases (v7) precedent; the FK
      // cascade removes a document's attributes with the document, and
      // `replaceDocumentAttributes` replaces them wholesale on reindex.
      db.exec(`
        CREATE TABLE IF NOT EXISTS document_attributes (
          document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
          key          TEXT NOT NULL,
          value        TEXT NOT NULL,
          UNIQUE(document_id, key, value)
        );
      `);
      db.exec(
        "CREATE INDEX IF NOT EXISTS idx_document_attributes_key_value " +
          "ON document_attributes(key, value)",
      );
      backfillDocumentAttributes(db);
    },
  },
  {
    // v15 (chunk skip-and-hold, t_f029b413) - the persistent hold
    // record: one row per (chunk, hold-decision key) for chunks the
    // embedding phase PROVED over a model's declared input window.
    //
    // The key is the whole design. `PENDING_ANTI_JOIN` excludes a chunk
    // only while a row matches the CURRENT (model, window, signature)
    // triple, so a config change releases holds back into pending with
    // no migration and no delete pass, while an unchanged config keeps
    // them held without a second provider contact. The FK cascade is
    // the garbage collector: re-indexing a document replaces its chunk
    // rows, and a hold against a chunk that no longer exists dies with
    // it rather than keying a ghost.
    //
    // No backfill, deliberately: a hold is a decision about a CONFIG -
    // which window which model declares - and a migration has no config
    // to consult. An index predating this version keeps its oversize
    // chunks pending until the next embedding pass decides, and records,
    // for them.
    version: 15,
    up(db) {
      db.exec(`
        CREATE TABLE IF NOT EXISTS ${CHUNK_HOLDS_TABLE} (
          chunk_id            INTEGER NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
          model               TEXT NOT NULL,
          window_tokens       INTEGER NOT NULL,
          embedding_signature TEXT NOT NULL,
          estimated_tokens    INTEGER NOT NULL,
          held_at             TEXT NOT NULL,
          PRIMARY KEY (chunk_id, model, window_tokens, embedding_signature)
        ) WITHOUT ROWID;
      `);
    },
  },
  {
    // v16 (privacy-marker visibility restamp) - re-measure the
    // {@link DOCUMENT_VISIBILITY_COLUMN} of every row whose chunk-zero
    // frontmatter carries an informal privacy marker key (`access`,
    // `confidentiality`, `sensitivity`).
    //
    // WHY A RE-STAMP, NOT A BACKFILL: the column has existed since v12,
    // and the v12 backfill ran once, for `IS NULL` rows, under whatever
    // measurement its binary knew. An index migrated to v12-v14 by a
    // pre-marker binary holds rows stamped from the declared `visibility:`
    // alone, and those rows are NOT NULL anymore - the v12 gate never
    // opens for them again. The marker-aware measurement
    // ({@link remoteDenyVisibilityTokens}) arrived after, and the
    // incremental indexer skips an unchanged file, so a marker page's
    // column can keep saying `[]` - measured, and declaring nothing -
    // while the live read roots deny the page. Every consumer that reads
    // the column alone (the belief-semantic lane, the visibility-honesty
    // census) then undercounts the very pages this column exists to make
    // countable. A new migration is the only frame that runs on those
    // indexes: editing v15 in place would rewrite released migration
    // history and never execute on an index already at 15.
    //
    // The gate is {@link hasPrivacyMarkerKey}, not the deny predicate:
    // any marker key at all marks a row whose pre-marker stamp may
    // understate it, and the update writes the full marker-aware token
    // list, so a row already carrying the reserved token re-derives the
    // same value and a non-marker row is not touched. Re-running the v12
    // recipe over EVERY row would be equivalent but would rewrite rows
    // this migration has no reason to touch; a NULL row (no chunk zero)
    // stays NULL - there is still nothing to measure. No DDL: the
    // manifest this migration produces is v15's, unchanged.
    //
    // It runs INSIDE `applyMigrations`' single open transaction, like the
    // v12 backfill: a half-restamped column would leave sibling rows
    // disagreeing about the same kind of claim, and a crash mid-pass
    // would leave exactly that.
    version: 16,
    up(db) {
      restampMarkerVisibility(db);
    },
  },
  {
    // v17 (brain store index, design D5, task 1.8) - what the index needs
    // to read the brain store beside the vault:
    //
    //   - `store_watermarks`: one row per shard - its file identity, size,
    //     mtime, the byte offset read so far and a hash of the verified
    //     prefix - so catch-up reads only what was appended and re-reads a
    //     replaced, shrunk or rewritten shard from the start;
    //   - `records`: one row per store record (envelope fields, the shard
    //     it was last read from, an ingest sequence the change probe pages
    //     by, and the document its text was indexed under, if any);
    //   - `documents.source`: a VIRTUAL generated column deriving `note` or
    //     `brain` from the path alone (store records and the legacy
    //     `Brain/` tree are brain), so no writer can forget to set it and
    //     no backfill is needed;
    //   - the index generation: a fresh ULID per index file, so a watermark
    //     from a replaced index is recognised as stale.
    //
    // In place and reindex-safe: no existing row changes. The empty
    // watermark table makes the next catch-up read every shard from offset
    // 0, which is also what picks up records an older binary skipped for
    // an unknown format version.
    version: 17,
    up(db) {
      const docCols = db.query<{ name: string }, []>("PRAGMA table_xinfo(documents)").all();
      if (!docCols.some((c) => c.name === DOCUMENT_SOURCE_COLUMN)) {
        db.exec(
          `ALTER TABLE documents ADD COLUMN ${DOCUMENT_SOURCE_COLUMN} TEXT ` +
            `GENERATED ALWAYS AS (${documentSourceExpression()}) VIRTUAL`,
        );
      }
      db.exec(DDL_V17_STORE);
      db.query("INSERT OR IGNORE INTO index_state(key, value, updated_at) VALUES (?, ?, ?)").run(
        STORE_INDEX_GENERATION_KEY,
        newUlid(),
        new Date().toISOString(),
      );
    },
  },
]);

/** The `documents` column naming where a document came from (v17). */
export const DOCUMENT_SOURCE_COLUMN = "source";

/** One row per store shard the index has read (v17). */
export const STORE_WATERMARKS_TABLE = "store_watermarks";

/** One row per store record the index has ingested (v17). */
export const RECORDS_TABLE = "records";

const DDL_V17_STORE = `
CREATE TABLE IF NOT EXISTS ${STORE_WATERMARKS_TABLE} (
  shard             TEXT PRIMARY KEY,
  partition         TEXT NOT NULL,
  identity          TEXT NOT NULL,
  size              INTEGER NOT NULL,
  mtime_ms          INTEGER NOT NULL,
  byte_offset       INTEGER NOT NULL,
  prefix_hash       TEXT NOT NULL,
  unknown_versions  INTEGER NOT NULL DEFAULT 0,
  corrupt_lines     INTEGER NOT NULL DEFAULT 0,
  updated_at        TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS ${RECORDS_TABLE} (
  id            TEXT PRIMARY KEY,
  seq           INTEGER NOT NULL UNIQUE,
  document_id   INTEGER REFERENCES documents(id) ON DELETE SET NULL,
  partition     TEXT NOT NULL,
  stream        TEXT NOT NULL,
  ledger        TEXT,
  kind          TEXT NOT NULL,
  scope         TEXT,
  topic         TEXT,
  hlc           TEXT NOT NULL,
  device        TEXT NOT NULL,
  agent         TEXT NOT NULL,
  authority     TEXT,
  sum           TEXT NOT NULL,
  shard         TEXT NOT NULL,
  body_pending  INTEGER NOT NULL DEFAULT 0,
  indexed_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_records_shard ON ${RECORDS_TABLE}(shard);
CREATE INDEX IF NOT EXISTS idx_records_document ON ${RECORDS_TABLE}(document_id);
CREATE INDEX IF NOT EXISTS idx_records_pending ON ${RECORDS_TABLE}(body_pending) WHERE body_pending = 1;
`;

/** SQL string literal of `value` (single quotes doubled). */
function sqlLiteral(value: string): string {
  return `'${value.replaceAll("'", "''")}'`;
}

/**
 * The generated-column expression of {@link DOCUMENT_SOURCE_COLUMN}: a
 * store record (`.brain-store/...`) or a page of the legacy brain tree
 * (`Brain` or `Brain/...`, compared case-sensitively) is `brain`, every
 * other path is a user `note`.
 */
function documentSourceExpression(): string {
  const storePrefix = `${STORE_RECORD_PATH_PREFIX}/`;
  const brainPrefix = `${BRAIN_ROOT_REL}/`;
  return (
    `CASE WHEN substr(path, 1, ${storePrefix.length}) = ${sqlLiteral(storePrefix)} ` +
    `OR path = ${sqlLiteral(BRAIN_ROOT_REL)} ` +
    `OR substr(path, 1, ${brainPrefix.length}) = ${sqlLiteral(brainPrefix)} ` +
    `THEN ${sqlLiteral(DOCUMENT_SOURCE.brain)} ELSE ${sqlLiteral(DOCUMENT_SOURCE.note)} END`
  );
}

/**
 * The `documents` column holding what the index measured of a page's
 * `visibility:` frontmatter. Named once: the migration, the store, the
 * indexer and the diagnostics all spell it from here.
 */
export const DOCUMENT_VISIBILITY_COLUMN = "visibility";

/** The measured-and-empty value, so `[]` is never written as a literal. */
export const DOCUMENT_VISIBILITY_NONE = "[]";

/**
 * The `documents` columns holding a document's RESOLVED event-time
 * window bounds (v13, t_9e1a4b3f), and the flag holding its per-note
 * `pinned` frontmatter state (schema half of t_f7bef96a). Named once:
 * the migration, the store and the window census all spell them from
 * here.
 *
 * The `_ms` suffix is load-bearing, as it is on `event_anchor_*`: mtime
 * and `authored_at` on this same table are unix SECONDS, and a window
 * bound read as seconds would move every interval comparison by three
 * orders of magnitude.
 */
export const EVENT_TIME_MIN_COLUMN = "event_time_min";
export const EVENT_TIME_MAX_COLUMN = "event_time_max";
export const PINNED_COLUMN = "pinned";

/**
 * The `chunks` kind discriminator (v14): a VIRTUAL generated column that
 * names the page-level chunk ('page' - the frontmatter/head chunk at index
 * 0) versus every section chunk ('section'). Named once: the migration,
 * the indexer-side constants and the query-path pushdown all spell it from
 * here.
 */
export const CHUNK_KIND_COLUMN = "chunk_kind";
export const CHUNK_KIND_PAGE = "page";
export const CHUNK_KIND_SECTION = "section";

/** The `document_attributes` table (v14), named once for the same reason. */
export const DOCUMENT_ATTRIBUTES_TABLE = "document_attributes";

/**
 * The `chunk_embedding_holds` table (v15): the persistent record of the
 * embedding phase's skip-and-hold decisions, keyed by the chunk and the
 * (model, window, signature) triple that made the proof. Named once: the
 * migration, the store's anti-join exclusion and the hold report all
 * spell it from here.
 */
export const CHUNK_HOLDS_TABLE = "chunk_embedding_holds";

/**
 * The frontmatter keys the attributes extractor never copies: each keeps a
 * dedicated column or table as the single source of truth (`type` ->
 * `documents.page_type`, `tags` -> the tag `links`, `pinned` ->
 * `documents.pinned`, `visibility` -> `documents.visibility`, `aliases` ->
 * `doc_aliases`), and a second stored copy of the same fact is drift
 * waiting to happen.
 */
export const PROMOTED_ATTRIBUTE_KEYS: ReadonlySet<string> = Object.freeze(
  new Set(["type", "tags", "pinned", "visibility", "aliases"]),
);

/**
 * Hard bounds for {@link DOCUMENT_ATTRIBUTES_TABLE}. They exist because a
 * hostile frontmatter must not be able to bloat the index; exceeding one
 * DECREMENTS the stored attribute set and never fails the index - a page
 * with too many keys still indexes, it just filters on fewer of them.
 * Named once so the extractor and every test spell the same numbers.
 */
export const ATTRIBUTE_MAX_KEYS_PER_DOCUMENT = 64;
export const ATTRIBUTE_MAX_VALUES_PER_KEY = 16;
export const ATTRIBUTE_MAX_VALUE_BYTES = 256;

/** One stored frontmatter attribute: the NFC key spelling and its scalar value. */
export interface DocumentAttribute {
  readonly key: string;
  readonly value: string;
}

/**
 * The bounded attribute set a parsed frontmatter map yields, in
 * frontmatter key order.
 *
 * Mirrors the property filter's value semantics exactly, so a stored row
 * answers only what the live filter would have answered:
 *
 *   - scalar strings store verbatim; numbers and booleans store
 *     `String(value)` - the same string form `matchesAny` compares;
 *   - arrays store their STRING elements one row each. Non-string array
 *     elements are skipped because the live filter skips them too, so
 *     storing their string form would fabricate a match;
 *   - null, undefined and non-array objects store nothing - the live
 *     filter drops such rows unconditionally;
 *   - keys keep their exact NFC spelling (round-trips the filter's
 *     `hasOwnProperty` semantics; they are NOT lower-cased);
 *   - the bounds above truncate: at most
 *     {@link ATTRIBUTE_MAX_KEYS_PER_DOCUMENT} distinct keys per document,
 *     at most {@link ATTRIBUTE_MAX_VALUES_PER_KEY} values per key, and a
 *     value over {@link ATTRIBUTE_MAX_VALUE_BYTES} UTF-8 bytes is dropped
 *     (a truncated value would equal a string the true value never was);
 *   - a key with ANY dropped value stores NO rows at all. A partly stored
 *     key is a lie the attribute narrowing would weaponize: rows for the
 *     kept values read as proof of a mismatch for the dropped ones, and
 *     the narrowing would exclude a document the live filter accepts.
 *     Zero rows leaves the key silent, and silence never excludes (the
 *     narrowing's `NOT EXISTS` arm). A silenced key spends none of the
 *     per-document key budget, so a hostile frontmatter cannot push
 *     honest keys out by over-running its own bounds.
 */
export function documentAttributesFromFrontmatter(
  meta: Record<string, unknown>,
): DocumentAttribute[] {
  const out: DocumentAttribute[] = [];
  let keyCount = 0;
  for (const rawKey of Object.keys(meta)) {
    if (keyCount >= ATTRIBUTE_MAX_KEYS_PER_DOCUMENT) break;
    const key = rawKey.normalize("NFC");
    if (PROMOTED_ATTRIBUTE_KEYS.has(key)) continue;
    const value = meta[rawKey];
    const values: string[] = [];
    let dropped = false;
    if (typeof value === "string") {
      values.push(value);
    } else if (typeof value === "number" || typeof value === "boolean") {
      // The scalar string form the property filter compares.
      values.push(String(value));
    } else if (Array.isArray(value)) {
      for (const element of value) {
        if (values.length >= ATTRIBUTE_MAX_VALUES_PER_KEY) {
          dropped = true;
          break;
        }
        if (typeof element !== "string") continue;
        values.push(element);
      }
    }
    const stored: string[] = [];
    for (const v of values) {
      if (Buffer.byteLength(v, "utf8") > ATTRIBUTE_MAX_VALUE_BYTES) {
        dropped = true;
        continue;
      }
      stored.push(v);
    }
    if (dropped || stored.length === 0) continue;
    keyCount++;
    for (const value of stored) out.push({ key, value });
  }
  return out;
}

/** The `chunk_index` a page's frontmatter block lands at, when it has one. */
const FRONTMATTER_CHUNK_INDEX = 0;

/**
 * The chunk a document's page head lives at, measured from the indexed
 * chunks: chunk 0 carries the frontmatter block when it starts with the
 * `---` fence, and the head is then the first content chunk - unless the
 * document has no content chunk at all, in which case the frontmatter
 * block is all the page there is. A document without frontmatter starts
 * its content at chunk 0. The chunks store applies the same rule at
 * insert time; this backfill exists for rows written before v14, and a
 * re-index rewrites the column authoritatively either way.
 */
function backfillChunkKind(db: Database): void {
  const rows = db
    .query<
      { document_id: number; has_frontmatter: number; first_content_index: number | null },
      []
    >(
      `SELECT d.id AS document_id,
         CASE WHEN c0.content LIKE '---%' THEN 1 ELSE 0 END AS has_frontmatter,
         (SELECT MIN(c1.chunk_index) FROM chunks c1
            WHERE c1.document_id = d.id AND c1.chunk_index > ${FRONTMATTER_CHUNK_INDEX}
         ) AS first_content_index
       FROM documents d
       LEFT JOIN chunks c0 ON c0.document_id = d.id AND c0.chunk_index = ${FRONTMATTER_CHUNK_INDEX}`,
    )
    .all();
  if (rows.length === 0) return;
  const page = db.query<never, [string, number, number]>(
    `UPDATE chunks SET ${CHUNK_KIND_COLUMN} = ?1 ` +
      `WHERE document_id = ?2 AND chunk_index = ?3 AND ${CHUNK_KIND_COLUMN} IS NULL`,
  );
  const section = db.query<never, [string, number]>(
    `UPDATE chunks SET ${CHUNK_KIND_COLUMN} = ?1 ` +
      `WHERE document_id = ?2 AND ${CHUNK_KIND_COLUMN} IS NULL`,
  );
  for (const row of rows) {
    // Head = the first content chunk when the document has frontmatter and
    // content, chunk 0 otherwise (no frontmatter, or frontmatter is all
    // the document holds).
    const headIndex =
      row.has_frontmatter === 1 && row.first_content_index !== null ? row.first_content_index : 0;
    page.run(CHUNK_KIND_PAGE, row.document_id, headIndex);
    section.run(CHUNK_KIND_SECTION, row.document_id);
  }
}

/**
 * Encode a page's normalised visibility tokens for the column.
 *
 * JSON rather than a delimited list, because a visibility token is an
 * opaque string from a page's frontmatter and any delimiter this module
 * chose could appear inside one - which would turn one token into two and
 * silently change what the page declared.
 */
export function encodeDocumentVisibility(tokens: ReadonlyArray<string>): string {
  return JSON.stringify(tokens);
}

/**
 * Fill {@link DOCUMENT_VISIBILITY_COLUMN} for every row that has no value
 * yet, from the frontmatter chunk the index already holds.
 *
 * The tokens come from {@link remoteDenyVisibilityTokens} - the declared
 * `visibility:` plus any informal privacy marker's reservation - because
 * that is exactly what the indexer stamps on a live write. Backfilling
 * from the declaration alone would silently UN-reserve every marker page
 * this migration touches: the row would answer differently after a
 * migration than the same file answers after a re-index.
 *
 * A document with no chunk zero is left NULL - the index holds nothing to
 * measure, and saying "no tokens" about it would be a claim nobody made.
 */
function backfillDocumentVisibility(db: Database): void {
  const rows = db
    .query<{ id: number; content: string | null }, [number]>(
      `SELECT d.id AS id, c.content AS content FROM documents d ` +
        `LEFT JOIN chunks c ON c.document_id = d.id AND c.chunk_index = ?1 ` +
        `WHERE d.${DOCUMENT_VISIBILITY_COLUMN} IS NULL`,
    )
    .all(FRONTMATTER_CHUNK_INDEX);
  if (rows.length === 0) return;
  const update = db.query<never, [string, number]>(
    `UPDATE documents SET ${DOCUMENT_VISIBILITY_COLUMN} = ?1 WHERE id = ?2`,
  );
  for (const row of rows) {
    if (row.content === null) continue;
    const [meta] = parseFrontmatterText(row.content);
    update.run(encodeDocumentVisibility(remoteDenyVisibilityTokens(meta)), row.id);
  }
}

/**
 * Re-measure {@link DOCUMENT_VISIBILITY_COLUMN} for every row whose
 * chunk-zero frontmatter carries an informal privacy marker key, from that
 * same chunk, through {@link remoteDenyVisibilityTokens} - the v16
 * migration's whole body.
 *
 * The rows it targets are exactly the ones a pre-marker stamp can
 * understate: a marker page's column reads `[]` (measured, declares
 * nothing) while the live read roots deny the page, and the incremental
 * indexer never revisits an unchanged file to correct it. A row already
 * carrying the marker-aware value re-derives that value and is written
 * with what it already was, so the pass is idempotent; a row whose column
 * disagrees with its frontmatter for any other reason is left to a
 * re-index, which remains the authoritative rewrite.
 */
function restampMarkerVisibility(db: Database): void {
  const rows = db
    .query<{ id: number; content: string | null; current: string | null }, [number]>(
      `SELECT d.id AS id, c.content AS content, d.${DOCUMENT_VISIBILITY_COLUMN} AS current ` +
        `FROM documents d ` +
        `LEFT JOIN chunks c ON c.document_id = d.id AND c.chunk_index = ?1`,
    )
    .all(FRONTMATTER_CHUNK_INDEX);
  if (rows.length === 0) return;
  const update = db.query<never, [string, number]>(
    `UPDATE documents SET ${DOCUMENT_VISIBILITY_COLUMN} = ?1 WHERE id = ?2`,
  );
  for (const row of rows) {
    if (row.content === null) continue;
    const [meta] = parseFrontmatterText(row.content);
    if (!hasPrivacyMarkerKey(meta)) continue;
    const next = encodeDocumentVisibility(remoteDenyVisibilityTokens(meta));
    if (row.current !== next) update.run(next, row.id);
  }
}

/**
 * Fill {@link DOCUMENT_ATTRIBUTES_TABLE} for every document that has no
 * attribute rows yet, from the frontmatter chunk the index already holds -
 * the v12 recipe exactly: one table scan, one parse per document, no vault
 * walk. A document with no chunk zero gets no rows, which is the honest
 * "the index holds nothing to read".
 *
 * The "no rows yet" gate is what makes the backfill re-runnable inside its
 * transaction without duplicating: a document already extracted (including
 * one whose frontmatter legitimately yields zero attributes) is skipped.
 */
function backfillDocumentAttributes(db: Database): void {
  const rows = db
    .query<{ id: number; content: string | null }, [number]>(
      `SELECT d.id AS id, c.content AS content FROM documents d ` +
        `LEFT JOIN chunks c ON c.document_id = d.id AND c.chunk_index = ?1 ` +
        `WHERE NOT EXISTS (SELECT 1 FROM document_attributes a WHERE a.document_id = d.id)`,
    )
    .all(FRONTMATTER_CHUNK_INDEX);
  if (rows.length === 0) return;
  const insert = db.query<never, [number, string, string]>(
    `INSERT OR IGNORE INTO ${DOCUMENT_ATTRIBUTES_TABLE}(document_id, key, value) VALUES (?1, ?2, ?3)`,
  );
  for (const row of rows) {
    if (row.content === null) continue;
    const [meta] = parseFrontmatterText(row.content);
    for (const attr of documentAttributesFromFrontmatter(meta)) {
      insert.run(row.id, attr.key, attr.value);
    }
  }
}

// ─────────────────────────────────────────────────────────────────────────────
// The expected-object manifest
// ─────────────────────────────────────────────────────────────────────────────

/**
 * A table, or a column of a table, that the schema declares and an opened
 * database does not have. `column: null` means the whole table is absent.
 */
export interface SchemaGap {
  /** Table the gap is in. */
  readonly table: string;
  /** Missing column, or null when the table itself is missing. */
  readonly column: string | null;
}

/**
 * Table names present in a database. Internal `sqlite_*` tables are
 * excluded: they are SQLite's own bookkeeping (`sqlite_stat1` appears only
 * after `ANALYZE`), never something a query of ours reads.
 */
const TABLE_NAMES_SQL =
  "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'";

/** Column names of one table. */
const TABLE_COLUMNS_SQL = "SELECT name FROM pragma_table_info(?)";

function tableNames(db: Database): Set<string> {
  return new Set(
    db
      .query<{ name: string }, []>(TABLE_NAMES_SQL)
      .all()
      .map((r) => r.name),
  );
}

function columnNames(db: Database, table: string): Set<string> {
  return new Set(
    db
      .query<{ name: string }, [string]>(TABLE_COLUMNS_SQL)
      .all(table)
      .map((r) => r.name),
  );
}

/**
 * Virtual tables the query path can run WITHOUT, and therefore must not be
 * refused for. The manifest requires what a query cannot survive the
 * absence of; a lane that already classifies its own table as missing and
 * degrades is not that.
 *
 * `chunk_trigram` is the opt-in substring broadener from v9. `trigram.ts`
 * detects its absence (`isMissingTrigramTable`) and returns no candidates
 * silently, which is a documented, tested behaviour. Demanding it here
 * would push a legitimately readable index into a reindex that drops every
 * embedding, to restore a table the query path never needed.
 *
 * `chunk_vec` needs no entry: it is created by `ensureVecTable` only when
 * sqlite-vec is loaded, so the reference database never has one to begin
 * with.
 */
const OPTIONAL_VIRTUAL_TABLES: ReadonlyArray<string> = Object.freeze(["chunk_trigram"]);

/** The shadow tables FTS5 creates alongside a virtual table it owns. */
const FTS5_SHADOW_SUFFIXES: ReadonlyArray<string> = Object.freeze([
  "_data",
  "_idx",
  "_docsize",
  "_content",
  "_config",
]);

function isOptionalTable(name: string): boolean {
  return OPTIONAL_VIRTUAL_TABLES.some(
    (base) => name === base || FTS5_SHADOW_SUFFIXES.some((suffix) => name === base + suffix),
  );
}

/**
 * Every required table and its columns. Used to build the reference
 * manifest from a database this module has just migrated itself, so every
 * table in it is one of ours and introspecting all of them is safe.
 *
 * It is NOT how a live index is inspected: `PRAGMA table_info` on a virtual
 * table whose module is not loaded raises (`no such module: vec0`), and a
 * live index legitimately carries a `chunk_vec` table that the read open
 * has not loaded sqlite-vec for yet. {@link findSchemaGaps} therefore asks
 * only about the tables the manifest actually names.
 */
function schemaCensus(db: Database): Map<string, Set<string>> {
  const census = new Map<string, Set<string>>();
  for (const table of tableNames(db)) {
    if (isOptionalTable(table)) continue;
    census.set(table, columnNames(db, table));
  }
  return census;
}

/** Memoised {@link expectedSchemaObjects}; the reference build is not free. */
let expectedObjects: ReadonlyMap<string, ReadonlySet<string>> | null = null;

/**
 * The tables and columns a current index must have, DERIVED by running the
 * frozen {@link MIGRATIONS} against a fresh in-memory database and asking
 * SQLite what that produced.
 *
 * Deriving it is the whole point. A second hand-maintained list of tables
 * and columns would drift from the migrations that create them, and a
 * manifest that has drifted is worse than no manifest: it either refuses a
 * healthy index or blesses a broken one. Running the migrations cannot
 * drift from the migrations.
 *
 * It includes the FTS5 shadow tables of `chunk_fts` (`chunk_fts_data` and
 * friends) because a keyword query reads straight through them, and
 * excludes {@link OPTIONAL_VIRTUAL_TABLES} because the query path is
 * written to survive their absence.
 *
 * The tokenizer clause does not appear: it changes how `chunk_fts` tokenizes,
 * never what it or its shadows are CALLED, so the reference is built at the
 * default and matches an index built under any configured tokenizer.
 *
 * Cost is one in-memory migration run (tens of milliseconds), paid lazily
 * once per process and memoised, then nothing.
 */
export function expectedSchemaObjects(): ReadonlyMap<string, ReadonlySet<string>> {
  if (expectedObjects !== null) return expectedObjects;
  let reference: Database;
  try {
    reference = new Database(":memory:");
  } catch (e) {
    const msg = e instanceof Error ? e.message : String(e);
    throw new SearchError(
      "INDEX_UNREADABLE",
      `cannot build the reference schema for the index presence check: ${msg}`,
    );
  }
  try {
    applyMigrations(reference);
    expectedObjects = schemaCensus(reference);
    return expectedObjects;
  } catch (e) {
    const msg = e instanceof Error ? e.message : String(e);
    throw new SearchError(
      "INDEX_UNREADABLE",
      `cannot build the reference schema for the index presence check: ${msg}`,
    );
  } finally {
    closeDatabase(reference);
  }
}

/**
 * Tables and columns {@link expectedSchemaObjects} declares that `db` does
 * not have. Empty for a healthy current index. Extra tables are NOT a gap:
 * `chunk_vec`, `sqlite_stat1` and a future version's additions are all
 * legitimately present in a database this binary can still read.
 */
export function findSchemaGaps(db: Database): ReadonlyArray<SchemaGap> {
  const present = tableNames(db);
  const gaps: SchemaGap[] = [];
  for (const [table, columns] of expectedSchemaObjects()) {
    if (!present.has(table)) {
      gaps.push({ table, column: null });
      continue;
    }
    const actual = columnNames(db, table);
    for (const column of columns) {
      if (!actual.has(column)) gaps.push({ table, column });
    }
  }
  return gaps;
}

/** Render {@link SchemaGap}s as `table` / `table.column`, for an error message. */
export function formatSchemaGaps(gaps: ReadonlyArray<SchemaGap>): string {
  return gaps.map((g) => (g.column === null ? g.table : `${g.table}.${g.column}`)).join(", ");
}

/** Read the current schema version from `index_state`. Returns 0 if not yet recorded. */
export function readSchemaVersion(db: Database): number {
  const row = db
    .query<{ value: string }, []>(
      "SELECT value FROM index_state WHERE key = 'schema_version' LIMIT 1",
    )
    .get();
  if (!row) return 0;
  const n = Number(row.value);
  if (!Number.isFinite(n) || !Number.isInteger(n) || n < 0) {
    throw new SearchError(
      "INDEX_UNREADABLE",
      `index_state.schema_version is not an integer: '${row.value}'`,
    );
  }
  return n;
}

function setSchemaVersion(db: Database, version: number): void {
  const now = new Date().toISOString();
  db.run(
    "INSERT INTO index_state(key, value, updated_at) VALUES('schema_version', ?, ?) " +
      "ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at",
    [String(version), now],
  );
}

/** Options for {@link applyMigrations}. */
export interface ApplyMigrationsOptions {
  /**
   * FTS5 tokenizer clause for the searchable `chunk_fts` table. Absent =
   * {@link DEFAULT_FTS_TOKENIZE}, which keeps the generated schema
   * byte-identical to prior releases. Only takes effect on a fresh build
   * (reindex); an already-migrated index is not rewritten.
   */
  readonly ftsTokenize?: string;
}

/**
 * Apply every pending migration in a single transaction. On a fresh
 * database (no `index_state`), v1 creates the entire schema. On an
 * existing one we run only those with `version > current`.
 *
 * Returns the version after migration.
 */
export function applyMigrations(db: Database, opts: ApplyMigrationsOptions = {}): number {
  const ctx: MigrationContext = { ftsTokenize: opts.ftsTokenize ?? DEFAULT_FTS_TOKENIZE };
  const hasIndexState = db
    .query<{ name: string }, []>(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='index_state'",
    )
    .get();

  const current = hasIndexState ? readSchemaVersion(db) : 0;

  if (current > LATEST_SCHEMA_VERSION) {
    throw new SearchError(
      "SCHEMA_MISMATCH",
      `index schema version ${current} is newer than this binary supports (${LATEST_SCHEMA_VERSION}). ` +
        `Run: ${REINDEX_COMMAND}`,
    );
  }

  if (current === LATEST_SCHEMA_VERSION) return current;

  // A migration can rewrite a large table in one synchronous run; the
  // write open that calls this holds the writer lock, kept fresh off the
  // blocked thread meanwhile.
  keepWriterLeasesFresh(WRITER_LEASE_BLOCKING_BUDGET_MS, () => {
    db.exec("BEGIN");
    try {
      for (const migration of MIGRATIONS) {
        if (migration.version <= current) continue;
        migration.up(db, ctx);
        setSchemaVersion(db, migration.version);
      }
      db.exec("COMMIT");
    } catch (e) {
      db.exec("ROLLBACK");
      throw e;
    }
  });
  return LATEST_SCHEMA_VERSION;
}

/**
 * Create the `chunk_vec` virtual table at the given dimension. Caller
 * must have already loaded sqlite-vec; this only issues the DDL.
 *
 * No-op if the table already exists.
 */
export function ensureVecTable(db: Database, dimension: number): void {
  if (!Number.isInteger(dimension) || dimension <= 0) {
    throw new SearchError(
      "INVALID_INPUT",
      `vec dimension must be a positive integer, got ${dimension}`,
    );
  }
  const exists = db
    .query<{ name: string }, []>(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='chunk_vec'",
    )
    .get();
  if (exists) return;
  db.exec(`CREATE VIRTUAL TABLE chunk_vec USING vec0(embedding float[${dimension}])`);
}

/** Drop the `chunk_vec` virtual table if it exists. */
export function dropVecTable(db: Database): void {
  db.exec("DROP TABLE IF EXISTS chunk_vec");
}