Architecture · SQLite migrations

Schema migrations and deferred backfill

How schema_version, pending_backfill, backfill_revision, and index_cache_key interact — for migration authors and agents debugging upgrade paths.

Three axes (do not conflate)

Axis	Where	Purpose
Schema version	`nci_meta.schema_version`	DDL head applied by `nci db migrate`
Backfill revision	`packages.backfill_revision`	Per-package symbol SQL migrations completed
Index cache key	`packages.index_cache_key`	Crawl/build fingerprint (`INDEXER_OUTPUT_REVISION`)

Migration kinds

Instant — additive DDL always runs; optional package_sql for bulk packages updates (skipped when empty or when a Rebuild is in the batch).
Backfill — DDL at migrate time; symbol transforms deferred. Sets one pending_backfill target (latest Backfill version in the batch).
Rebuild — purges all packages rows; re-index required.

Backfill chain (agent contract)

Migrate stores a single nci_meta.pending_backfill target. When draining, the engine runs every step in PACKAGE_BACKFILL_STEPS where:

backfill_revision < step_version <= pending_backfill

Steps run in ascending version order; backfill_revision updates after each step. Instant-only schema versions register no step. Fresh nci index writes set backfill_revision to the current SCHEMA_VERSION.

While pending_backfill is set, cache hits require backfill_revision >= pending_backfill in addition to index_cache_key match. Foreground backfill runs before cache probes during nci index, but only for packages whose stored index_cache_key already matches the current engine key — stale-key rows are skipped (recrawl + save_package fixes them).

Canonical copy for agents also lives in the repo at docs/nci-sqlite-migrations.md (referenced from storage_migrations.rs and package_backfill.rs).