Skip to main content

khive_db/
migrations.rs

1//! Schema migration system for the SQLite storage layer.
2//!
3//! Two APIs coexist:
4//! - **Legacy per-service migrations** (`ServiceSchemaPlan` / `apply_schema_plan`):
5//!   used by pack-scoped schemas.
6//! - **Versioned migrations** (`MIGRATIONS` / `run_migrations`): the forward-only
7//!   migration pipeline for the core tables.
8//!
9//! Raw migration entry points require a connection opened by rusqlite;
10//! externally owned handles wrapped with `Connection::from_handle` are not
11//! supported. They reject inherited transactions. If rollback and close cannot
12//! establish an outcome, the original connection is retired before its volume
13//! lease is released and the caller receives `WriterSettlementUnknown`.
14
15use khive_storage::blob::ContentRef;
16use rusqlite::{Connection, OptionalExtension};
17use std::path::{Path, PathBuf};
18
19use crate::error::SqliteError;
20use crate::pool::WriteAdmission;
21use crate::stores::blob::{try_acquire_database_gc_owner_for_path, DatabaseGcOwnerGuard};
22
23#[path = "raw_migration_settlement.rs"]
24mod raw_migration_settlement;
25use raw_migration_settlement::{RawMigrationTransactions, RawMigrationWriteUnit};
26
27/// The write side of a migration run.
28///
29/// ADR-154 section 4 admits each migration transaction on its own: the volume
30/// lease is taken before the transaction begins and released once it has
31/// settled, so a multi-version upgrade never holds the volume for the whole
32/// run. Implementations own that per-call lease and settlement.
33pub(crate) trait MigrationTransactions {
34    /// Run one write transaction, or one autocommit write, under a fresh
35    /// admission.
36    fn admitted<T>(
37        &mut self,
38        operation: impl FnOnce(&mut Connection) -> Result<T, SqliteError>,
39    ) -> Result<T, SqliteError>;
40}
41
42/// Captured disk-guard settings and shared volume-lock directory for a SQLite
43/// writer.
44///
45/// Construction validates configuration without reading or writing a database.
46/// Cooperating callers must choose the same absolute volume-lock directory.
47#[derive(Clone, Debug)]
48pub struct MigrationWritePolicy {
49    disk_guard: crate::EffectiveDiskGuardConfig,
50    volume_lock_dir: PathBuf,
51}
52
53impl MigrationWritePolicy {
54    pub fn new(
55        disk_guard: crate::EffectiveDiskGuardConfig,
56        volume_lock_dir: impl Into<PathBuf>,
57    ) -> Result<Self, SqliteError> {
58        disk_guard.validate()?;
59        let volume_lock_dir = volume_lock_dir.into();
60        if !volume_lock_dir.is_absolute() {
61            return Err(SqliteError::InvalidConfig(
62                "migration volume-lock directory must be absolute".to_string(),
63            ));
64        }
65        Ok(Self {
66            disk_guard,
67            volume_lock_dir,
68        })
69    }
70
71    /// Resolve both settings from the process environment. The lock directory
72    /// follows [`crate::default_volume_lock_dir`], the same per-user default the
73    /// public SQLite constructors use.
74    pub fn from_environment() -> Result<Self, SqliteError> {
75        let disk_guard = crate::DiskGuardEnvironment::capture().resolve(None, None)?;
76        Self::new(disk_guard, crate::default_volume_lock_dir()?)
77    }
78
79    pub fn disk_guard_config(&self) -> crate::EffectiveDiskGuardConfig {
80        self.disk_guard
81    }
82
83    pub fn volume_lock_dir(&self) -> &Path {
84        &self.volume_lock_dir
85    }
86}
87
88#[path = "session_identity_migration.rs"]
89mod session_identity_migration;
90
91mod memory_visibility;
92
93// =============================================================================
94// Legacy per-service migration API (preserved for backward compatibility)
95// =============================================================================
96
97/// A single legacy migration step within a `ServiceSchemaPlan`.
98pub struct Migration {
99    /// Unique identifier for this migration.
100    pub id: &'static str,
101    /// SQL to apply (forward direction).
102    pub up_sql: &'static str,
103    /// SQL to revert (optional).
104    pub down_sql: Option<&'static str>,
105    /// Optional read-only predicate: returns true if migration was already
106    /// applied through a mechanism other than the migration tracker. It runs
107    /// while this connection holds the SQLite write lock.
108    pub is_already_applied: Option<fn(&Connection) -> bool>,
109}
110
111/// A pack-scoped schema plan containing migrations for SQLite and Postgres.
112pub struct ServiceSchemaPlan {
113    /// Service name used as a key in the `_schema_versions` tracking table.
114    pub service: &'static str,
115    /// SQLite-specific migration steps, applied in order.
116    pub sqlite: &'static [Migration],
117    /// Postgres-specific migration steps (reserved for future use).
118    pub postgres: &'static [Migration],
119}
120
121const SCHEMA_VERSION_TABLE: &str = include_str!("../sql/schema-version-table.sql");
122
123/// Apply a pack-scoped schema plan, tracking each migration in `_schema_versions`.
124/// A mutable connection lets the wrapper retire an unsettled original handle.
125pub fn apply_schema_plan(
126    conn: &mut Connection,
127    plan: &ServiceSchemaPlan,
128) -> Result<(), SqliteError> {
129    let admission = WriteAdmission::for_canonical_path(canonical_connection_database_path(conn)?)?;
130    apply_schema_plan_with_admission(
131        &mut RawMigrationTransactions::new(conn, &admission),
132        plan,
133        &admission,
134    )
135}
136
137/// Apply a raw connection's schema plan with a captured policy and lock path.
138pub fn apply_schema_plan_with_policy(
139    conn: &mut Connection,
140    plan: &ServiceSchemaPlan,
141    policy: &MigrationWritePolicy,
142) -> Result<(), SqliteError> {
143    let admission =
144        WriteAdmission::for_migration_policy(canonical_connection_database_path(conn)?, policy)?;
145    apply_schema_plan_with_admission(
146        &mut RawMigrationTransactions::new(conn, &admission),
147        plan,
148        &admission,
149    )
150}
151
152/// Apply a service schema plan. The tracking-table bootstrap and each
153/// migration are separate admitted write units (ADR-154 section 4).
154pub(crate) fn apply_schema_plan_with_admission(
155    writes: &mut impl MigrationTransactions,
156    plan: &ServiceSchemaPlan,
157    admission: &WriteAdmission,
158) -> Result<(), SqliteError> {
159    writes.admitted(|conn| {
160        admission.check()?;
161        conn.execute_batch(SCHEMA_VERSION_TABLE)?;
162        require_autocommit(conn, "schema-version bootstrap")
163    })?;
164
165    for migration in plan.sqlite {
166        writes.admitted(|conn| apply_service_migration(conn, plan, migration, admission))?;
167    }
168
169    Ok(())
170}
171
172/// Apply one service migration in its own IMMEDIATE transaction unless its
173/// predicate or the ledger says it has already been applied.
174fn apply_service_migration(
175    conn: &Connection,
176    plan: &ServiceSchemaPlan,
177    migration: &Migration,
178    admission: &WriteAdmission,
179) -> Result<(), SqliteError> {
180    // Serialize the admission decision with other writers. Checking the
181    // predicate or ledger before BEGIN IMMEDIATE lets a second opener see
182    // stale state and replay a migration after the first one commits.
183    let tx = rusqlite::Transaction::new_unchecked(conn, rusqlite::TransactionBehavior::Immediate)?;
184    if let Err(error) = admission.check() {
185        let rollback = tx.rollback();
186        return Err(capacity_refusal_after_rollback(
187            conn,
188            rollback,
189            error,
190            "service schema migration",
191        ));
192    }
193
194    // Check if custom predicate says it's already applied
195    if let Some(check) = migration.is_already_applied {
196        if check(&tx) {
197            return Ok(());
198        }
199    }
200
201    // Check if tracked as applied
202    let already: bool = tx.query_row(
203        "SELECT COUNT(*) > 0 FROM _schema_versions WHERE service = ?1 AND migration_id = ?2",
204        rusqlite::params![plan.service, migration.id],
205        |row| row.get(0),
206    )?;
207
208    if already {
209        return Ok(());
210    }
211
212    tx.execute_batch(migration.up_sql)?;
213
214    tx.execute(
215        "INSERT INTO _schema_versions (service, migration_id, applied_at) VALUES (?1, ?2, ?3)",
216        rusqlite::params![
217            plan.service,
218            migration.id,
219            chrono::Utc::now().timestamp_micros(),
220        ],
221    )?;
222    tx.commit()?;
223    Ok(())
224}
225
226fn require_autocommit(conn: &Connection, operation: &str) -> Result<(), SqliteError> {
227    if conn.is_autocommit() {
228        Ok(())
229    } else {
230        Err(SqliteError::InvalidData(format!(
231            "{operation} did not return the SQLite connection to autocommit"
232        )))
233    }
234}
235
236pub(crate) fn capacity_refusal_after_rollback(
237    conn: &Connection,
238    rollback: rusqlite::Result<()>,
239    refusal: SqliteError,
240    operation: &str,
241) -> SqliteError {
242    if let Err(error) = rollback {
243        return SqliteError::InvalidData(format!(
244            "{operation} capacity refusal could not roll back: {error}; \
245             initial refusal: {refusal}"
246        ));
247    }
248    if !conn.is_autocommit() {
249        return SqliteError::InvalidData(format!(
250            "{operation} capacity refusal rolled back without restoring autocommit; \
251             initial refusal: {refusal}"
252        ));
253    }
254    refusal
255}
256
257// =============================================================================
258// Versioned migration system
259// =============================================================================
260
261/// A single forward-only schema migration.
262///
263/// Migrations are applied in order from the current DB version to the target
264/// version. Each migration runs in its own transaction; a failure rolls back
265/// that migration and leaves the DB at the prior version.
266pub struct VersionedMigration {
267    /// Monotonically increasing version number, starting at 1.
268    pub version: u32,
269    /// Short human-readable name for the migration (used in the audit table).
270    pub name: &'static str,
271    /// SQL to apply this migration. May contain multiple statements separated
272    /// by semicolons; `execute_batch` runs them all.
273    pub up: &'static str,
274}
275
276// V1: complete schema, loaded from sql/schema.sql.
277// Fresh-start repo (v0.2.8) — all schema in one migration, no incremental versions.
278const V1_UP: &str = include_str!("../sql/schema.sql");
279
280const V2_UP: &str = include_str!("../sql/002-narrow-fts-sections-update-trigger.sql");
281
282const V3_UP: &str = include_str!("../sql/003-backfill-domain-mirror-atoms.sql");
283
284const V4_UP: &str = include_str!("../sql/004-fts-consolidation.sql");
285
286const V5_UP: &str = include_str!("../sql/005-unique-comm-external-id.sql");
287
288const V6_UP: &str = include_str!("../sql/006-brain-retune-driver.sql");
289
290const V7_UP: &str = include_str!("../sql/007-notes-seq.sql");
291
292const V8_UP: &str = include_str!("../sql/008-notes-seq-repair.sql");
293
294const V9_UP: &str = include_str!("../sql/009-entities-name-ci-index.sql");
295
296const V10_UP: &str = include_str!("../sql/010-entities-content-ref.sql");
297
298const V11_UP: &str = include_str!("../sql/011-ann-write-log.sql");
299
300const V12_UP: &str = include_str!("../sql/012-ann-write-log-model-seq-index.sql");
301
302const V13_UP: &str = include_str!("../sql/013-list-cursor-sequences.sql");
303
304const V14_UP: &str = include_str!("../sql/014-graph-edges-id-unique.sql");
305
306const V15_UP: &str = include_str!("../sql/015-serve-ledger-attribution.sql");
307
308const V16_UP: &str = include_str!("../sql/016-gtd-dependency-cycle-guards.sql");
309
310const V17_UP: &str = include_str!("../sql/017-agents-ddl.sql");
311
312const V18_UP: &str = include_str!("../sql/018-ann-consumer-pending.sql");
313
314const V19_UP: &str = include_str!("../sql/019-list-cursor-backfill-repair.sql");
315
316const V20_UP: &str = include_str!("../sql/020-blob-gc-claims.sql");
317
318const V22_UP: &str = include_str!("../sql/022-notes-unread-probe-recipient.sql");
319
320const V23_UP: &str = include_str!("../sql/023-fts-record-kind.sql");
321
322const V24_UP: &str = include_str!("../sql/024-fts-rowid-map.sql");
323
324const V25_UP: &str = include_str!("../sql/025-notes-unread-probe-recipient-direction.sql");
325
326const V26_UP: &str = include_str!("../sql/026-knowledge-fts-repair.sql");
327
328const V27_UP: &str = include_str!("../sql/027-notes-hot-property-indexes.sql");
329const V28_UP: &str = include_str!("../sql/028-notes-key.sql");
330
331const V29_UP: &str = include_str!("../sql/029-note-streams.sql");
332const V30_UP: &str = include_str!("../sql/030-tool-source-mounts.sql");
333const V31_UP: &str = include_str!("../sql/031-note-versions.sql");
334const V32_UP: &str = include_str!("../sql/032-knowledge-count-indexes.sql");
335const V33_UP: &str = include_str!("../sql/033-notes-message-recipient-direction.sql");
336const V34_UP: &str = include_str!("../sql/034-notes-namespace-created.sql");
337const V35_UP: &str = include_str!("../sql/035-notes-unread-probe-recipient-type-direction.sql");
338const V36_UP: &str = include_str!("../sql/036-events-operation-attribution.sql");
339const V37_UP: &str = include_str!("../sql/037-entity-versions.sql");
340const V38_UP: &str = include_str!("../sql/038-entities-legacy-type-index.sql");
341const V39_UP: &str = include_str!("../sql/039-knowledge-cursor-indexes.sql");
342const SESSION_IDENTITY_UP: &str = include_str!("../sql/040-session-source-scope.sql");
343const SESSION_IDENTITY_MIGRATION_NAME: &str = "session_source_scoped_identity";
344const V41_UP: &str = include_str!("../sql/041-sender-transport.sql");
345const V42_UP: &str = include_str!("../sql/042-comm-external-id-channel-scope.sql");
346const V43_UP: &str = include_str!("../sql/043-vector-provenance.sql");
347const V44_COLUMNS: &str = include_str!("../sql/044-comm-outbound-due-a-columns.sql");
348const V44_UP: &str = include_str!("../sql/044-comm-outbound-due-b-index.sql");
349
350/// V44 may follow a direct-store bootstrap, whose idempotent notes DDL
351/// already supplies the two columns. Backfill before building the index so
352/// existing future retries are excluded by its deadline range immediately.
353pub(crate) fn migrate_outbound_due_key(tx: &rusqlite::Transaction<'_>) -> rusqlite::Result<()> {
354    let has_column = |name: &str| -> rusqlite::Result<bool> {
355        tx.query_row(
356            "SELECT EXISTS(SELECT 1 FROM pragma_table_info('notes') WHERE name = ?1)",
357            [name],
358            |row| row.get(0),
359        )
360    };
361    match (has_column("strict_due_key")?, has_column("due_source")?) {
362        (false, false) => tx.execute_batch(V44_COLUMNS)?,
363        (true, true) => {}
364        _ => return Err(rusqlite::Error::InvalidQuery),
365    }
366
367    let mut after_id = String::new();
368    let mut first_page = true;
369    loop {
370        let rows: Vec<(String, String)> = {
371            let comparator = if first_page { ">=" } else { ">" };
372            let mut stmt = tx.prepare(&format!(
373                "SELECT id, json_extract(properties, '$.next_attempt_at') FROM notes \
374                 WHERE id {comparator} ?1 AND json_type(properties, '$.next_attempt_at') = 'text' \
375                 ORDER BY id LIMIT 500"
376            ))?;
377            let collected = stmt
378                .query_map([&after_id], |row| Ok((row.get(0)?, row.get(1)?)))?
379                .collect::<rusqlite::Result<_>>()?;
380            collected
381        };
382        if rows.is_empty() {
383            break;
384        }
385        after_id = rows.last().expect("nonempty V44 page").0.clone();
386        first_page = false;
387        for (id, source) in rows {
388            if let Some(key) = crate::pool::strict_rfc3339_key(&source) {
389                tx.execute(
390                    "UPDATE notes SET strict_due_key = ?1, due_source = ?2 \
391                     WHERE id = ?3 AND (strict_due_key IS NOT ?1 OR due_source IS NOT ?2)",
392                    rusqlite::params![key, source, id],
393                )?;
394            }
395        }
396    }
397    tx.execute_batch(V44_UP)
398}
399
400const RECIPIENT_TRANSPORT_VERSION: u32 = 45;
401const V45_UP: &str = include_str!("../sql/045-recipient-transport.sql");
402const V46_UP: &str = include_str!("../sql/046-memory-visibility-receipts.sql");
403const V47_UP: &str = include_str!("../sql/047-attachment-role-quarantine.sql");
404const V49_UP: &str = include_str!("../sql/049-git-note-property-indexes.sql");
405
406const V50_UP: &str = include_str!("../sql/050-entity-list-plans.sql");
407const V51_UP: &str = include_str!("../sql/051-schedule-core-indexes.sql");
408const V52_UP: &str = include_str!("../sql/052-comm-core-indexes.sql");
409const V53_UP: &str = include_str!("../sql/053-entity-kind-list-order.sql");
410const MEMORY_VISIBILITY_CUTOVER_VERSION: u32 = 54;
411const V54_UP: &str = include_str!("../sql/054-memory-visibility-epochs.sql");
412
413const V55_UP: &str = include_str!("../sql/055-knowledge-cursor-domain-tags.sql");
414const V56_UP: &str = include_str!("../sql/056-atom-weights.sql");
415const V57_UP: &str = include_str!("../sql/057-retrieval-snapshots.sql");
416const V58_UP: &str = include_str!("../sql/058-proposal-archive.sql");
417const V59_UP: &str = include_str!("../sql/059-comm-legacy-quarantine-index.sql");
418const V60_UP: &str = include_str!("../sql/060-note-stream-truncation.sql");
419const V61_UP: &str = include_str!("../sql/061-edge-governance.sql");
420const V62_UP: &str = include_str!("../sql/062-authority-store.sql");
421const V63_UP: &str = include_str!("../sql/063-pending-entity-indexes.sql");
422const V64_UP: &str = include_str!("../sql/064-node-confirmed-pins.sql");
423
424// Ledger-tail replay may retain the physical column; accept only its exact shape.
425fn migrate_proposal_archive(tx: &rusqlite::Transaction<'_>) -> Result<(), SqliteError> {
426    let migration_error = |error: rusqlite::Error| SqliteError::Migration {
427        version: 58,
428        error: format!("proposals_open.archived_at migration failed: {error}"),
429    };
430    let column = tx
431        .query_row(
432            include_str!("../sql/proposal-archive-column.sql"),
433            [],
434            |row| {
435                Ok((
436                    row.get::<_, String>(0)?,
437                    row.get::<_, i64>(1)?,
438                    row.get::<_, Option<String>>(2)?,
439                    row.get::<_, i64>(3)?,
440                    row.get::<_, i64>(4)?,
441                ))
442            },
443        )
444        .optional()
445        .map_err(migration_error)?;
446    match column {
447        None => tx.execute_batch(V58_UP).map_err(migration_error),
448        Some((declared_type, not_null, default, primary_key, hidden))
449            if declared_type.eq_ignore_ascii_case("INTEGER")
450                && not_null == 0
451                && default.is_none()
452                && primary_key == 0
453                && hidden == 0 =>
454        {
455            Ok(())
456        }
457        Some(_) => Err(SqliteError::Migration {
458            version: 58,
459            error: "incompatible proposals_open.archived_at: expected an ordinary nullable INTEGER column without a default or primary key".into(),
460        }),
461    }
462}
463
464const V48_UP: &str = include_str!("../sql/048-acknowledgement-journal-a-table.sql");
465const ACKNOWLEDGEMENT_JOURNAL_INDEX: &str =
466    include_str!("../sql/048-acknowledgement-journal-b-index.sql");
467
468/// A ledger-tail replay keeps already-current journal state and retry metadata.
469pub(crate) fn migrate_acknowledgement_journal(
470    tx: &rusqlite::Transaction<'_>,
471) -> rusqlite::Result<()> {
472    let columns: i64 = tx.query_row(
473        "SELECT count(*) FROM pragma_table_info('comm_ack_work') \
474         WHERE name IN ('attempt_count','not_before','retirement_reason')",
475        [],
476        |row| row.get(0),
477    )?;
478    match columns {
479        0 => tx.execute_batch(V48_UP)?,
480        3 => {}
481        _ => return Err(rusqlite::Error::InvalidQuery),
482    }
483    tx.execute_batch(ACKNOWLEDGEMENT_JOURNAL_INDEX)
484}
485
486const V21_STAGE_UP: &str = include_str!("../sql/021-attachments-a-stage.sql");
487
488const V21_ATTACHMENT_FENCES_UP: &str = include_str!("../sql/021-attachments-b-claim-fences.sql");
489
490/// Core schema version reserved for ADR-121's attachments-first cutover.
491pub const ATTACHMENT_CUTOVER_VERSION: u32 = 21;
492
493/// The latest schema version this build's migration chain produces.
494///
495/// Terminal-version assertions belong on this, not on a hardcoded number:
496/// a literal decays into a wrong claim the next time a migration is added.
497pub fn latest_schema_version() -> u32 {
498    MIGRATIONS.last().map(|m| m.version).unwrap_or(0)
499}
500
501/// DDL for the `ann_write_log` delta table.
502///
503/// Shared between migration V11 and the belt-and-suspenders creation in
504/// `StorageBackend::vectors_for_namespace` (same pattern as
505/// [`EMBEDDING_MODELS_DDL`]): every database that hosts `vec_*` tables must
506/// also have the write log, or vector writes would fail on databases opened
507/// without `run_migrations()`. The `.sql` file is `IF NOT EXISTS`-idempotent.
508pub const ANN_WRITE_LOG_DDL: &str = V11_UP;
509
510/// DDL for the `ann_write_log` model/kind/field-leading index (ADR-118 §"Cost
511/// bound"), shared between migration V12 and the belt-and-suspenders creation
512/// in `StorageBackend::vectors_for_namespace` for the same reason as
513/// [`ANN_WRITE_LOG_DDL`].
514pub const ANN_WRITE_LOG_MODEL_SEQ_INDEX_DDL: &str = V12_UP;
515
516/// Idempotent DDL for pending ANN-consumer lifecycle metadata (#1479).
517///
518/// The V18 migration additionally translates legacy zero-watermark rows once.
519/// This constant deliberately contains only idempotent DDL: vector-store open
520/// paths may execute it repeatedly and must never demote a valid active
521/// checkpoint at sequence zero back to pending.
522pub const ANN_CONSUMER_PENDING_DDL: &str = include_str!("../sql/ann-consumer-pending-ddl.sql");
523
524/// Sidecar DDL registered in the migration ledger. Direct vector-store
525/// construction leaves this schema to the versioned migration.
526pub const VECTOR_PROVENANCE_DDL: &str = V43_UP;
527
528/// DDL for the `_embedding_models` registry table.
529///
530/// Shared between the V1 schema and the belt-and-suspenders creation in
531/// `StorageBackend::vectors_for_namespace`. Both sites reference this constant so
532/// the schema cannot silently diverge if the registry evolves.
533pub const EMBEDDING_MODELS_DDL: &str = include_str!("../sql/embedding-models-ddl.sql");
534
535/// Canonical versioned migration ledger in ascending order.
536///
537/// [`run_migrations`] applies the ordinary prefix and may complete V21 through
538/// its zero-legacy-reference fast path. A legacy V20 database records V21 only
539/// when [`finalize_attachment_cutover`] commits the application-assisted
540/// cutover.
541pub const MIGRATIONS: &[VersionedMigration] = &[
542    VersionedMigration {
543        version: 1,
544        name: "initial_schema",
545        up: V1_UP,
546    },
547    VersionedMigration {
548        version: 2,
549        name: "narrow_fts_sections_update_trigger",
550        up: V2_UP,
551    },
552    VersionedMigration {
553        version: 3,
554        name: "backfill_domain_mirror_atoms",
555        up: V3_UP,
556    },
557    VersionedMigration {
558        version: 4,
559        name: "fts_consolidation",
560        up: V4_UP,
561    },
562    VersionedMigration {
563        version: 5,
564        name: "unique_comm_message_external_id",
565        up: V5_UP,
566    },
567    VersionedMigration {
568        version: 6,
569        name: "brain_retune_driver",
570        up: V6_UP,
571    },
572    VersionedMigration {
573        version: 7,
574        name: "notes_seq",
575        up: V7_UP,
576    },
577    VersionedMigration {
578        version: 8,
579        name: "notes_seq_repair",
580        up: V8_UP,
581    },
582    VersionedMigration {
583        version: 9,
584        name: "entities_name_ci_index",
585        up: V9_UP,
586    },
587    VersionedMigration {
588        version: 10,
589        name: "entities_content_ref",
590        up: V10_UP,
591    },
592    VersionedMigration {
593        version: 11,
594        name: "ann_write_log",
595        up: V11_UP,
596    },
597    VersionedMigration {
598        version: 12,
599        name: "ann_write_log_model_seq_index",
600        up: V12_UP,
601    },
602    VersionedMigration {
603        version: 13,
604        name: "list_cursor_sequences",
605        up: V13_UP,
606    },
607    VersionedMigration {
608        version: 14,
609        name: "graph_edges_id_unique",
610        up: V14_UP,
611    },
612    VersionedMigration {
613        version: 15,
614        name: "serve_ledger_attribution",
615        up: V15_UP,
616    },
617    VersionedMigration {
618        version: 16,
619        name: "gtd_dependency_cycle_guards",
620        up: V16_UP,
621    },
622    VersionedMigration {
623        version: 17,
624        name: "agents_ddl",
625        up: V17_UP,
626    },
627    VersionedMigration {
628        version: 18,
629        name: "ann_consumer_pending",
630        up: V18_UP,
631    },
632    VersionedMigration {
633        version: 19,
634        name: "list_cursor_backfill_repair",
635        up: V19_UP,
636    },
637    VersionedMigration {
638        version: 20,
639        name: "blob_gc_claims",
640        up: V20_UP,
641    },
642    VersionedMigration {
643        version: ATTACHMENT_CUTOVER_VERSION,
644        name: "attachments_first_class",
645        // V21 is coordinated rather than an unconditional SQL migration.
646        // The runner special-cases it below; exposing the stage DDL here keeps
647        // the ledger entry self-describing for migration inspection tooling.
648        up: V21_STAGE_UP,
649    },
650    VersionedMigration {
651        version: 22,
652        name: "notes_unread_probe_recipient",
653        up: V22_UP,
654    },
655    VersionedMigration {
656        version: 23,
657        name: "fts_record_kind",
658        up: V23_UP,
659    },
660    VersionedMigration {
661        version: 24,
662        name: "fts_rowid_map",
663        up: V24_UP,
664    },
665    VersionedMigration {
666        version: 25,
667        name: "notes_unread_probe_recipient_direction",
668        up: V25_UP,
669    },
670    VersionedMigration {
671        version: 26,
672        name: "knowledge_fts_repair",
673        up: V26_UP,
674    },
675    VersionedMigration {
676        version: 27,
677        name: "notes_hot_property_indexes",
678        up: V27_UP,
679    },
680    VersionedMigration {
681        version: 28,
682        name: "notes_key",
683        up: V28_UP,
684    },
685    VersionedMigration {
686        version: 29,
687        name: "note_streams",
688        up: V29_UP,
689    },
690    VersionedMigration {
691        version: 30,
692        name: "tool_source_mounts",
693        up: V30_UP,
694    },
695    VersionedMigration {
696        version: 31,
697        name: "note_versions",
698        up: V31_UP,
699    },
700    VersionedMigration {
701        version: 32,
702        name: "knowledge_count_indexes",
703        up: V32_UP,
704    },
705    VersionedMigration {
706        version: 33,
707        name: "notes_message_recipient_direction",
708        up: V33_UP,
709    },
710    VersionedMigration {
711        version: 34,
712        name: "notes_namespace_created",
713        up: V34_UP,
714    },
715    VersionedMigration {
716        version: 35,
717        name: "notes_unread_probe_recipient_type_direction",
718        up: V35_UP,
719    },
720    VersionedMigration {
721        version: 36,
722        name: "events_operation_attribution",
723        up: V36_UP,
724    },
725    VersionedMigration {
726        version: 37,
727        name: "entity_versions",
728        up: V37_UP,
729    },
730    VersionedMigration {
731        version: 38,
732        name: "entities_legacy_type_index",
733        up: V38_UP,
734    },
735    VersionedMigration {
736        version: 39,
737        name: "knowledge_cursor_indexes",
738        up: V39_UP,
739    },
740    VersionedMigration {
741        version: 40,
742        name: SESSION_IDENTITY_MIGRATION_NAME,
743        up: SESSION_IDENTITY_UP,
744    },
745    VersionedMigration {
746        version: 41,
747        name: "sender_transport",
748        up: V41_UP,
749    },
750    VersionedMigration {
751        version: 42,
752        name: "comm_external_id_channel_scope",
753        up: V42_UP,
754    },
755    VersionedMigration {
756        version: 43,
757        name: "vector_provenance",
758        up: V43_UP,
759    },
760    VersionedMigration {
761        version: 44,
762        name: "comm_outbound_due",
763        up: V44_UP,
764    },
765    VersionedMigration {
766        version: RECIPIENT_TRANSPORT_VERSION,
767        name: "recipient_transport",
768        up: V45_UP,
769    },
770    VersionedMigration {
771        version: 46,
772        name: "memory_visibility_receipts",
773        up: V46_UP,
774    },
775    VersionedMigration {
776        version: 47,
777        name: "attachment_role_quarantine",
778        up: V47_UP,
779    },
780    VersionedMigration {
781        version: 48,
782        name: "acknowledgement_journal",
783        up: V48_UP,
784    },
785    VersionedMigration {
786        version: 49,
787        name: "git_note_property_indexes",
788        up: V49_UP,
789    },
790    VersionedMigration {
791        version: 50,
792        name: "entity_list_plans",
793        up: V50_UP,
794    },
795    VersionedMigration {
796        version: 51,
797        name: "schedule_core_indexes",
798        up: V51_UP,
799    },
800    VersionedMigration {
801        version: 52,
802        name: "comm_core_indexes",
803        up: V52_UP,
804    },
805    VersionedMigration {
806        version: 53,
807        name: "entity_kind_list_order",
808        up: V53_UP,
809    },
810    VersionedMigration {
811        version: MEMORY_VISIBILITY_CUTOVER_VERSION,
812        name: "memory_visibility_epochs",
813        up: V54_UP,
814    },
815    VersionedMigration {
816        version: 55,
817        name: "knowledge_atom_cursor_all_live_tags",
818        up: V55_UP,
819    },
820    VersionedMigration {
821        version: 56,
822        name: "atom_weight_tables",
823        up: V56_UP,
824    },
825    VersionedMigration {
826        version: 57,
827        name: "retrieval_snapshot_tables",
828        up: V57_UP,
829    },
830    VersionedMigration {
831        version: 58,
832        name: "proposal_archive",
833        up: V58_UP,
834    },
835    VersionedMigration {
836        version: 59,
837        name: "comm_legacy_quarantine_index",
838        up: V59_UP,
839    },
840    VersionedMigration {
841        version: 60,
842        name: "note_stream_truncation",
843        up: V60_UP,
844    },
845    VersionedMigration {
846        version: 61,
847        name: "edge_governance",
848        up: V61_UP,
849    },
850    VersionedMigration {
851        version: 62,
852        name: "authority_store",
853        up: V62_UP,
854    },
855    VersionedMigration {
856        version: 63,
857        name: "pending_entity_indexes",
858        up: V63_UP,
859    },
860    VersionedMigration {
861        version: 64,
862        name: "node_confirmed_pins",
863        up: V64_UP,
864    },
865];
866
867/// Durable state of ADR-121's boot-gated, two-stage attachment cutover.
868#[derive(Clone, Copy, Debug, Eq, PartialEq)]
869pub enum AttachmentCutoverStatus {
870    /// V20 is current and no stage marker has been committed.
871    Pending,
872    /// Stage 1 committed; boot must finish verified pack-owned attachments.
873    Incomplete,
874    /// V21, the attachment fences, and the attachment-only schema committed.
875    Complete,
876}
877
878fn schema_object_exists(
879    conn: &Connection,
880    object_type: &str,
881    name: &str,
882) -> Result<bool, SqliteError> {
883    conn.query_row(
884        "SELECT COUNT(*) > 0 FROM sqlite_master WHERE type = ?1 AND name = ?2",
885        rusqlite::params![object_type, name],
886        |row| row.get(0),
887    )
888    .map_err(Into::into)
889}
890
891fn schema_column_exists(conn: &Connection, table: &str, column: &str) -> Result<bool, SqliteError> {
892    conn.query_row(
893        "SELECT COUNT(*) > 0 FROM pragma_table_info(?1) WHERE name = ?2",
894        rusqlite::params![table, column],
895        |row| row.get(0),
896    )
897    .map_err(Into::into)
898}
899
900fn require_attachment_schema_objects(
901    conn: &Connection,
902    objects: &[(&str, &str)],
903    phase: &str,
904) -> Result<(), SqliteError> {
905    for (object_type, name) in objects {
906        if !schema_object_exists(conn, object_type, name)? {
907            return Err(SqliteError::InvalidData(format!(
908                "attachment cutover {phase} state is missing {object_type} {name:?}"
909            )));
910        }
911    }
912    Ok(())
913}
914
915fn validate_incomplete_attachment_schema(conn: &Connection) -> Result<(), SqliteError> {
916    require_attachment_schema_objects(
917        conn,
918        &[
919            ("table", "attachments"),
920            ("index", "idx_attachments_content_ref"),
921        ],
922        "incomplete",
923    )?;
924    require_legacy_attachment_fences(conn)
925}
926
927fn validate_complete_attachment_schema(conn: &Connection) -> Result<(), SqliteError> {
928    require_attachment_schema_objects(
929        conn,
930        &[
931            ("table", "attachments"),
932            ("table", "blob_gc_claims"),
933            ("index", "idx_attachments_content_ref"),
934            ("index", "idx_blob_gc_claims_content_ref"),
935            ("trigger", "attachments_reject_claimed_blob_insert"),
936            ("trigger", "attachments_reject_claimed_blob_update"),
937        ],
938        "complete",
939    )?;
940    if schema_column_exists(conn, "entities", "content_ref")? {
941        return Err(SqliteError::InvalidData(
942            "attachment cutover is complete but entities.content_ref still exists".into(),
943        ));
944    }
945    for (object_type, name) in [
946        ("index", "idx_entities_content_ref"),
947        ("trigger", "entities_reject_claimed_blob_insert"),
948        ("trigger", "entities_reject_claimed_blob_update"),
949    ] {
950        if schema_object_exists(conn, object_type, name)? {
951            return Err(SqliteError::InvalidData(format!(
952                "attachment cutover is complete but legacy {object_type} {name:?} still exists"
953            )));
954        }
955    }
956    Ok(())
957}
958
959/// Inspect the coordinated V21 state without mutating the connection.
960///
961/// The marker and migration ledger form one state machine. Impossible pairs
962/// fail closed instead of being guessed into a resumable state.
963pub fn attachment_cutover_status(
964    conn: &Connection,
965) -> Result<AttachmentCutoverStatus, SqliteError> {
966    let version = read_schema_version(conn)?;
967    let marker_table = schema_object_exists(conn, "table", "attachment_cutover_state")?;
968    if !marker_table {
969        if version >= ATTACHMENT_CUTOVER_VERSION {
970            return Err(SqliteError::InvalidData(format!(
971                "migration V{ATTACHMENT_CUTOVER_VERSION} is recorded but its attachment cutover marker is absent"
972            )));
973        }
974        if schema_object_exists(conn, "table", "attachments")? {
975            return Err(SqliteError::InvalidData(
976                "attachments table exists without the durable attachment cutover marker".into(),
977            ));
978        }
979        return Ok(AttachmentCutoverStatus::Pending);
980    }
981
982    let marker: Option<(String, Option<i64>)> = conn
983        .query_row(
984            "SELECT state, completed_at FROM attachment_cutover_state WHERE singleton = 1",
985            [],
986            |row| Ok((row.get(0)?, row.get(1)?)),
987        )
988        .optional()?;
989    match marker {
990        Some((state, None)) if state == "incomplete" => {
991            if version >= ATTACHMENT_CUTOVER_VERSION {
992                Err(SqliteError::InvalidData(format!(
993                    "attachment cutover is incomplete but migration V{ATTACHMENT_CUTOVER_VERSION} is already recorded"
994                )))
995            } else {
996                validate_incomplete_attachment_schema(conn)?;
997                Ok(AttachmentCutoverStatus::Incomplete)
998            }
999        }
1000        Some((state, Some(_))) if state == "complete" => {
1001            // Later migrations (V22+) are recorded on top of a completed
1002            // cutover in the normal course; only a ledger BELOW V21 beside a
1003            // complete marker is an impossible pair.
1004            if version >= ATTACHMENT_CUTOVER_VERSION {
1005                validate_complete_attachment_schema(conn)?;
1006                Ok(AttachmentCutoverStatus::Complete)
1007            } else {
1008                Err(SqliteError::InvalidData(format!(
1009                    "attachment cutover is complete but schema ledger is at V{version}, below V{ATTACHMENT_CUTOVER_VERSION}"
1010                )))
1011            }
1012        }
1013        Some((state, completed_at)) => Err(SqliteError::InvalidData(format!(
1014            "invalid attachment cutover marker state {state:?} with completed_at={completed_at:?}"
1015        ))),
1016        None => Err(SqliteError::InvalidData(
1017            "attachment cutover marker table exists without its singleton row".into(),
1018        )),
1019    }
1020}
1021
1022fn require_legacy_attachment_fences(conn: &Connection) -> Result<(), SqliteError> {
1023    if !schema_column_exists(conn, "entities", "content_ref")? {
1024        return Err(SqliteError::InvalidData(
1025            "attachment cutover requires legacy entities.content_ref until finalization".into(),
1026        ));
1027    }
1028    for (object_type, name) in [
1029        ("table", "blob_gc_claims"),
1030        ("index", "idx_blob_gc_claims_content_ref"),
1031        ("index", "idx_entities_content_ref"),
1032        ("trigger", "entities_reject_claimed_blob_insert"),
1033        ("trigger", "entities_reject_claimed_blob_update"),
1034    ] {
1035        if !schema_object_exists(conn, object_type, name)? {
1036            return Err(SqliteError::InvalidData(format!(
1037                "attachment cutover requires legacy {object_type} {name:?} until finalization"
1038            )));
1039        }
1040    }
1041    Ok(())
1042}
1043
1044// length() and GLOB both stop scanning at an embedded NUL, so a value of 64
1045// hex characters followed by a NUL and arbitrary trailing bytes would pass
1046// both. Deriving the canonical byte width from the connection's own text
1047// encoding (rather than assuming UTF-8) keeps this arm correct on a database
1048// pinned to UTF-16 and fails closed if the probe returns something else,
1049// matching the pattern already used by `validate_blob_gc_evidence`.
1050fn canonical_content_ref_byte_width(conn: &Connection) -> Result<i64, SqliteError> {
1051    let width: i64 = conn.query_row("SELECT length(CAST('x' AS BLOB))", [], |row| row.get(0))?;
1052    if !(1..=4).contains(&width) {
1053        return Err(SqliteError::InvalidData(format!(
1054            "the text-encoding width probe returned {width}; refusing canonicality validation"
1055        )));
1056    }
1057    Ok(width * 64)
1058}
1059
1060fn validate_canonical_legacy_refs(conn: &Connection) -> Result<(), SqliteError> {
1061    let canonical_bytes = canonical_content_ref_byte_width(conn)?;
1062    let invalid: Option<String> = conn
1063        .query_row(
1064            "SELECT id FROM entities \
1065             WHERE content_ref IS NOT NULL \
1066               AND (typeof(content_ref) <> 'text' \
1067                 OR length(content_ref) <> 64 \
1068                 OR length(CAST(content_ref AS BLOB)) <> ?1 \
1069                 OR content_ref GLOB '*[^0-9a-f]*') \
1070             LIMIT 1",
1071            [canonical_bytes],
1072            |row| row.get(0),
1073        )
1074        .optional()?;
1075    if let Some(id) = invalid {
1076        return Err(SqliteError::InvalidData(format!(
1077            "entities.content_ref for record {id:?} is not a canonical 64-character lowercase hexadecimal ContentRef"
1078        )));
1079    }
1080    Ok(())
1081}
1082
1083fn validate_canonical_attachment_and_claim_refs(conn: &Connection) -> Result<(), SqliteError> {
1084    let canonical_bytes = canonical_content_ref_byte_width(conn)?;
1085    for (table, identity) in [
1086        ("attachments", "record_uuid"),
1087        ("blob_gc_claims", "root_key"),
1088    ] {
1089        let sql = format!(
1090            "SELECT {identity} FROM {table} \
1091             WHERE typeof(content_ref) <> 'text' \
1092                OR length(content_ref) <> 64 \
1093                OR length(CAST(content_ref AS BLOB)) <> ?1 \
1094                OR content_ref GLOB '*[^0-9a-f]*' \
1095             LIMIT 1"
1096        );
1097        let invalid: Option<String> = conn
1098            .query_row(&sql, [canonical_bytes], |row| row.get(0))
1099            .optional()?;
1100        if let Some(owner) = invalid {
1101            return Err(SqliteError::InvalidData(format!(
1102                "{table}.content_ref for {identity} {owner:?} is not canonical"
1103            )));
1104        }
1105    }
1106    Ok(())
1107}
1108
1109fn validate_attachment_record_owners(conn: &Connection) -> Result<(), SqliteError> {
1110    let dangling: Option<(String, String)> = conn
1111        .query_row(
1112            "SELECT record_uuid, substrate FROM attachments AS attachment \
1113             WHERE (substrate = 'entity' AND NOT EXISTS ( \
1114                       SELECT 1 FROM entities WHERE id = attachment.record_uuid \
1115                   )) \
1116                OR (substrate = 'note' AND NOT EXISTS ( \
1117                       SELECT 1 FROM notes WHERE id = attachment.record_uuid \
1118                   )) \
1119             LIMIT 1",
1120            [],
1121            |row| Ok((row.get(0)?, row.get(1)?)),
1122        )
1123        .optional()?;
1124    if let Some((record_uuid, substrate)) = dangling {
1125        return Err(SqliteError::InvalidData(format!(
1126            "attachment role references absent {substrate} record {record_uuid:?}"
1127        )));
1128    }
1129    Ok(())
1130}
1131
1132fn validate_legacy_content_backfill(conn: &Connection) -> Result<(), SqliteError> {
1133    let conflict: Option<String> = conn
1134        .query_row(
1135            "SELECT entity.id FROM entities AS entity \
1136             LEFT JOIN attachments AS attachment \
1137               ON attachment.record_uuid = entity.id AND attachment.role = 'content' \
1138             WHERE entity.content_ref IS NOT NULL \
1139               AND (attachment.record_uuid IS NULL \
1140                 OR attachment.substrate <> 'entity' \
1141                 OR attachment.content_ref <> entity.content_ref) \
1142             LIMIT 1",
1143            [],
1144            |row| row.get(0),
1145        )
1146        .optional()?;
1147    if let Some(record_uuid) = conflict {
1148        return Err(SqliteError::InvalidData(format!(
1149            "legacy content attachment for entity {record_uuid:?} is missing or conflicts with entities.content_ref"
1150        )));
1151    }
1152    Ok(())
1153}
1154
1155fn stage_attachment_cutover_on_connection(conn: &Connection, now: i64) -> Result<(), SqliteError> {
1156    require_legacy_attachment_fences(conn)?;
1157    conn.execute_batch(V21_STAGE_UP)?;
1158    validate_canonical_legacy_refs(conn)?;
1159    validate_canonical_attachment_and_claim_refs(conn)?;
1160
1161    let conflict: Option<String> = conn
1162        .query_row(
1163            "SELECT entity.id FROM entities AS entity \
1164             JOIN attachments AS attachment \
1165               ON attachment.record_uuid = entity.id AND attachment.role = 'content' \
1166             WHERE entity.content_ref IS NOT NULL \
1167               AND (attachment.substrate <> 'entity' \
1168                 OR attachment.content_ref <> entity.content_ref) \
1169             LIMIT 1",
1170            [],
1171            |row| row.get(0),
1172        )
1173        .optional()?;
1174    if let Some(record_uuid) = conflict {
1175        return Err(SqliteError::InvalidData(format!(
1176            "existing content attachment for entity {record_uuid:?} conflicts with entities.content_ref"
1177        )));
1178    }
1179
1180    conn.execute(
1181        "INSERT INTO attachments \
1182         (record_uuid, substrate, role, content_ref, media_type, size_bytes, created_at) \
1183         SELECT id, 'entity', 'content', content_ref, NULL, NULL, created_at \
1184         FROM entities WHERE content_ref IS NOT NULL \
1185         ON CONFLICT(record_uuid, role) DO NOTHING",
1186        [],
1187    )?;
1188    validate_legacy_content_backfill(conn)?;
1189
1190    // The caller holds the canonical database GC owner, so every preexisting
1191    // claim is abandoned. Clearing happens before the durable incomplete
1192    // marker is exposed and remains inside this one transaction.
1193    conn.execute("DELETE FROM blob_gc_claims", [])?;
1194    conn.execute(
1195        "INSERT INTO attachment_cutover_state \
1196         (singleton, state, started_at, completed_at) \
1197         VALUES (1, 'incomplete', ?1, NULL) \
1198         ON CONFLICT(singleton) DO NOTHING",
1199        [now],
1200    )?;
1201    Ok(())
1202}
1203
1204/// Commit stage 1 of the coordinated V21 migration.
1205///
1206/// The caller must hold [`crate::stores::blob::DatabaseGcOwnerGuard`] for the
1207/// canonical database before entering this function and retain it through
1208/// application backfill and finalization. This function owns one IMMEDIATE
1209/// SQLite transaction; a failure leaves neither its DDL nor marker visible.
1210pub fn stage_attachment_cutover(conn: &mut Connection) -> Result<(), SqliteError> {
1211    let admission = WriteAdmission::for_canonical_path(canonical_connection_database_path(conn)?)?;
1212    RawMigrationWriteUnit::new(conn, &admission)?
1213        .run(|conn| stage_attachment_cutover_with_admission(conn, &admission))
1214}
1215
1216/// Stage attachment cutover with an explicit raw-connection policy.
1217pub fn stage_attachment_cutover_with_policy(
1218    conn: &mut Connection,
1219    policy: &MigrationWritePolicy,
1220) -> Result<(), SqliteError> {
1221    let admission =
1222        WriteAdmission::for_migration_policy(canonical_connection_database_path(conn)?, policy)?;
1223    RawMigrationWriteUnit::new(conn, &admission)?
1224        .run(|conn| stage_attachment_cutover_with_admission(conn, &admission))
1225}
1226
1227/// Stage attachment cutover using admission already held by a pooled caller.
1228pub(crate) fn stage_attachment_cutover_with_admission(
1229    conn: &mut Connection,
1230    admission: &WriteAdmission,
1231) -> Result<(), SqliteError> {
1232    match attachment_cutover_status(conn)? {
1233        AttachmentCutoverStatus::Complete => return Ok(()),
1234        AttachmentCutoverStatus::Pending | AttachmentCutoverStatus::Incomplete => {}
1235    }
1236    if read_schema_version(conn)? != ATTACHMENT_CUTOVER_VERSION - 1 {
1237        return Err(SqliteError::InvalidData(format!(
1238            "attachment cutover stage requires canonical V{} schema",
1239            ATTACHMENT_CUTOVER_VERSION - 1
1240        )));
1241    }
1242
1243    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
1244    if let Err(error) = admission.check() {
1245        let rollback = tx.rollback();
1246        return Err(capacity_refusal_after_rollback(
1247            conn,
1248            rollback,
1249            error,
1250            "attachment cutover stage",
1251        ));
1252    }
1253    let status = attachment_cutover_status(&tx)?;
1254    if status == AttachmentCutoverStatus::Complete {
1255        return Ok(());
1256    }
1257    stage_attachment_cutover_on_connection(&tx, chrono::Utc::now().timestamp_micros())?;
1258    tx.commit()?;
1259    Ok(())
1260}
1261
1262/// Add one host-verified pack-owned attachment during V21 stage 2.
1263///
1264/// This helper is deliberately transaction-neutral: it neither begins nor
1265/// commits a transaction. The boot coordinator can therefore apply the full
1266/// verified vector in one caller-owned IMMEDIATE transaction while retaining
1267/// the canonical database GC owner. Reapplying the same role and content is
1268/// idempotent; a different substrate or digest for that role fails closed.
1269#[allow(clippy::too_many_arguments)]
1270pub fn apply_generic_verified_attachment(
1271    conn: &Connection,
1272    record_uuid: &str,
1273    substrate: &str,
1274    role: &str,
1275    content_ref: &ContentRef,
1276    media_type: Option<&str>,
1277    size_bytes: Option<u64>,
1278    created_at: i64,
1279) -> Result<(), SqliteError> {
1280    if attachment_cutover_status(conn)? != AttachmentCutoverStatus::Incomplete {
1281        return Err(SqliteError::InvalidData(
1282            "verified application attachments may only be applied while V21 cutover is incomplete"
1283                .into(),
1284        ));
1285    }
1286    if role.is_empty() || role.chars().any(char::is_control) {
1287        return Err(SqliteError::InvalidData(
1288            "attachment role must be non-empty and contain no control characters".into(),
1289        ));
1290    }
1291    let size_bytes = size_bytes.map(i64::try_from).transpose().map_err(|_| {
1292        SqliteError::InvalidData("attachment size_bytes exceeds SQLite INTEGER".into())
1293    })?;
1294    let owner_table = match substrate {
1295        "entity" => "entities",
1296        "note" => "notes",
1297        other => {
1298            return Err(SqliteError::InvalidData(format!(
1299                "attachment substrate must be 'entity' or 'note', got {other:?}"
1300            )))
1301        }
1302    };
1303    let owner_sql = format!("SELECT COUNT(*) > 0 FROM {owner_table} WHERE id = ?1");
1304    let owner_exists: bool = conn.query_row(&owner_sql, [record_uuid], |row| row.get(0))?;
1305    if !owner_exists {
1306        return Err(SqliteError::InvalidData(format!(
1307            "cannot attach role {role:?}: {substrate} record {record_uuid:?} does not exist"
1308        )));
1309    }
1310    let claimed: bool = conn.query_row(
1311        "SELECT COUNT(*) > 0 FROM blob_gc_claims WHERE content_ref = ?1",
1312        [content_ref.as_str()],
1313        |row| row.get(0),
1314    )?;
1315    if claimed {
1316        return Err(SqliteError::InvalidData(format!(
1317            "cannot attach claimed content_ref {} during V21 cutover",
1318            content_ref.as_str()
1319        )));
1320    }
1321
1322    let changed = conn.execute(
1323        "INSERT INTO attachments \
1324         (record_uuid, substrate, role, content_ref, media_type, size_bytes, created_at) \
1325         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7) \
1326         ON CONFLICT(record_uuid, role) DO UPDATE SET \
1327             media_type = excluded.media_type, \
1328             size_bytes = excluded.size_bytes, \
1329             created_at = excluded.created_at \
1330         WHERE attachments.substrate = excluded.substrate \
1331           AND attachments.content_ref = excluded.content_ref",
1332        rusqlite::params![
1333            record_uuid,
1334            substrate,
1335            role,
1336            content_ref.as_str(),
1337            media_type,
1338            size_bytes,
1339            created_at,
1340        ],
1341    )?;
1342    if changed == 0 {
1343        return Err(SqliteError::InvalidData(format!(
1344            "attachment role {role:?} for record {record_uuid:?} conflicts with an existing substrate or content_ref"
1345        )));
1346    }
1347    Ok(())
1348}
1349
1350fn finalize_attachment_cutover_on_connection(
1351    conn: &Connection,
1352    now: i64,
1353) -> Result<(), SqliteError> {
1354    require_legacy_attachment_fences(conn)?;
1355    validate_canonical_legacy_refs(conn)?;
1356    validate_canonical_attachment_and_claim_refs(conn)?;
1357    validate_attachment_record_owners(conn)?;
1358    validate_legacy_content_backfill(conn)?;
1359
1360    let remaining_claims: i64 =
1361        conn.query_row("SELECT COUNT(*) FROM blob_gc_claims", [], |row| row.get(0))?;
1362    if remaining_claims != 0 {
1363        return Err(SqliteError::InvalidData(format!(
1364            "attachment cutover cannot finalize while {remaining_claims} blob GC claim rows remain"
1365        )));
1366    }
1367
1368    let uncovered_model: Option<String> = conn
1369        .query_row(
1370            "SELECT model.id FROM entities AS model \
1371             WHERE model.entity_type = 'moodboard_model' \
1372               AND model.content_ref IS NOT NULL \
1373               AND NOT EXISTS ( \
1374                   SELECT 1 FROM attachments AS attachment \
1375                   WHERE attachment.record_uuid = model.id \
1376                     AND attachment.substrate = 'entity' \
1377                     AND attachment.role = 'fann-network' \
1378               ) \
1379             LIMIT 1",
1380            [],
1381            |row| row.get(0),
1382        )
1383        .optional()?;
1384    if let Some(record_uuid) = uncovered_model {
1385        return Err(SqliteError::InvalidData(format!(
1386            "moodboard_model {record_uuid:?} has legacy content but no verified 'fann-network' attachment"
1387        )));
1388    }
1389
1390    conn.execute_batch(V21_ATTACHMENT_FENCES_UP)?;
1391    conn.execute_batch(
1392        "DROP TRIGGER entities_reject_claimed_blob_insert; \
1393         DROP TRIGGER entities_reject_claimed_blob_update; \
1394         DROP INDEX idx_entities_content_ref; \
1395         ALTER TABLE entities DROP COLUMN content_ref;",
1396    )?;
1397    conn.execute(
1398        "UPDATE attachment_cutover_state \
1399         SET state = 'complete', completed_at = ?1 \
1400         WHERE singleton = 1 AND state = 'incomplete'",
1401        [now],
1402    )?;
1403    Ok(())
1404}
1405
1406fn record_attachment_cutover_migration(conn: &Connection, now: i64) -> Result<(), SqliteError> {
1407    let migration = MIGRATIONS
1408        .iter()
1409        .find(|migration| migration.version == ATTACHMENT_CUTOVER_VERSION)
1410        .expect("V21 migration must be registered");
1411    conn.execute(
1412        "INSERT INTO _schema_migrations (version, name, applied_at) VALUES (?1, ?2, ?3)",
1413        rusqlite::params![migration.version, migration.name, now],
1414    )?;
1415    Ok(())
1416}
1417
1418/// Atomically switch an explicitly staged database to attachment-only V21.
1419///
1420/// The caller must still hold the canonical database GC owner. The exclusive
1421/// transition revalidates every legacy and attachment reference, verifies
1422/// moodboard model role coverage, replaces the claim fences, removes the old
1423/// column, marks the cutover complete, and records V21 in one transaction.
1424pub fn finalize_attachment_cutover(conn: &mut Connection) -> Result<(), SqliteError> {
1425    let admission = WriteAdmission::for_canonical_path(canonical_connection_database_path(conn)?)?;
1426    RawMigrationWriteUnit::new(conn, &admission)?
1427        .run(|conn| finalize_attachment_cutover_with_admission(conn, &admission))
1428}
1429
1430/// Finalize attachment cutover with an explicit raw-connection policy.
1431pub fn finalize_attachment_cutover_with_policy(
1432    conn: &mut Connection,
1433    policy: &MigrationWritePolicy,
1434) -> Result<(), SqliteError> {
1435    let admission =
1436        WriteAdmission::for_migration_policy(canonical_connection_database_path(conn)?, policy)?;
1437    RawMigrationWriteUnit::new(conn, &admission)?
1438        .run(|conn| finalize_attachment_cutover_with_admission(conn, &admission))
1439}
1440
1441/// Finalize attachment cutover using admission already held by a pooled caller.
1442pub(crate) fn finalize_attachment_cutover_with_admission(
1443    conn: &mut Connection,
1444    admission: &WriteAdmission,
1445) -> Result<(), SqliteError> {
1446    if attachment_cutover_status(conn)? == AttachmentCutoverStatus::Complete {
1447        return Ok(());
1448    }
1449    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Exclusive)?;
1450    if let Err(error) = admission.check() {
1451        let rollback = tx.rollback();
1452        return Err(capacity_refusal_after_rollback(
1453            conn,
1454            rollback,
1455            error,
1456            "attachment cutover finalization",
1457        ));
1458    }
1459    match attachment_cutover_status(&tx)? {
1460        AttachmentCutoverStatus::Complete => return Ok(()),
1461        AttachmentCutoverStatus::Pending => {
1462            return Err(SqliteError::InvalidData(
1463                "attachment cutover must complete stage 1 before finalization".into(),
1464            ))
1465        }
1466        AttachmentCutoverStatus::Incomplete => {}
1467    }
1468    let now = chrono::Utc::now().timestamp_micros();
1469    finalize_attachment_cutover_on_connection(&tx, now)?;
1470    record_attachment_cutover_migration(&tx, now)?;
1471    tx.commit()?;
1472    Ok(())
1473}
1474
1475/// Read the ordered migration ledger prefix without interpreting its rows.
1476fn read_applied_migration_ledger(
1477    conn: &Connection,
1478    through_version: u32,
1479) -> Result<Vec<(u32, String)>, SqliteError> {
1480    let mut stmt = conn.prepare(
1481        "SELECT version, name FROM _schema_migrations \
1482         WHERE version <= ?1 ORDER BY version ASC",
1483    )?;
1484    let rows = stmt
1485        .query_map([through_version], |row| {
1486            Ok((row.get::<_, u32>(0)?, row.get::<_, String>(1)?))
1487        })?
1488        .collect::<Result<Vec<_>, _>>()?;
1489    Ok(rows)
1490}
1491
1492/// Require the applied versions through `through_version` to be the exact
1493/// contiguous canonical prefix of [`MIGRATIONS`]. A matching `MAX(version)` is
1494/// insufficient: a missing middle row or foreign version can expose a
1495/// materially different schema while retaining the same maximum.
1496fn validate_applied_migration_versions(
1497    applied: &[(u32, String)],
1498    through_version: u32,
1499) -> Result<(), SqliteError> {
1500    let expected: Vec<&VersionedMigration> = MIGRATIONS
1501        .iter()
1502        .filter(|migration| migration.version <= through_version)
1503        .collect();
1504    let mut applied_index = 0;
1505
1506    for migration in expected {
1507        let Some((version, applied_name)) = applied.get(applied_index) else {
1508            return Err(SqliteError::InvalidData(format!(
1509                "migration history is missing version {} ('{}'); the applied ledger must be \
1510                 the exact contiguous canonical sequence through version {through_version}",
1511                migration.version, migration.name,
1512            )));
1513        };
1514        if *version < migration.version {
1515            return Err(SqliteError::InvalidData(format!(
1516                "migration history contains unknown version {version} recorded as \
1517                 '{applied_name}'; the applied ledger must contain only canonical versions"
1518            )));
1519        }
1520        if *version > migration.version {
1521            return Err(SqliteError::InvalidData(format!(
1522                "migration history is missing version {} ('{}'); found version {version} \
1523                 next instead",
1524                migration.version, migration.name,
1525            )));
1526        }
1527        applied_index += 1;
1528    }
1529
1530    if let Some((version, name)) = applied.get(applied_index) {
1531        return Err(SqliteError::InvalidData(format!(
1532            "migration history contains unknown version {version} recorded as '{name}'; \
1533             the applied ledger must contain only canonical versions"
1534        )));
1535    }
1536
1537    Ok(())
1538}
1539
1540fn validate_applied_migration_names(
1541    applied: &[(u32, String)],
1542    through_version: u32,
1543    allow_known_v19_repairs: bool,
1544) -> Result<(), SqliteError> {
1545    for ((version, applied_name), migration) in applied.iter().zip(
1546        MIGRATIONS
1547            .iter()
1548            .filter(|migration| migration.version <= through_version),
1549    ) {
1550        debug_assert_eq!(*version, migration.version);
1551        if migration.name != applied_name.as_str() {
1552            if allow_known_v19_repairs && matches!(*version, 13 | 14) {
1553                continue;
1554            }
1555            return Err(SqliteError::InvalidData(format!(
1556                "migration version {version} is recorded under name '{applied_name}', \
1557                 expected '{expected}'. This database's migration history does not match \
1558                 the current binary; recreate it from the current schema or repair the \
1559                 specific known divergence via a dedicated migration.",
1560                expected = migration.name,
1561            )));
1562        }
1563    }
1564
1565    Ok(())
1566}
1567
1568/// Confirm the complete applied ledger is the canonical prefix, including
1569/// names. The only historical V13/V14 name divergence is repaired by V19
1570/// before this validator runs for a pre-V19 database.
1571fn validate_applied_migration_ledger(
1572    conn: &Connection,
1573    through_version: u32,
1574) -> Result<(), SqliteError> {
1575    let applied = read_applied_migration_ledger(conn, through_version)?;
1576    validate_applied_migration_versions(&applied, through_version)?;
1577    validate_applied_migration_names(&applied, through_version, false)
1578}
1579
1580const MIGRATION_TRACKING_TABLE: &str = include_str!("../sql/schema-migrations-table.sql");
1581
1582const SCHEMA_VERSION_PROBE_SQL: &str = "SELECT COALESCE(MAX(version), 0) FROM _schema_migrations";
1583
1584/// Build the schema-ledger version query without opening a reader or decoding its result.
1585pub fn schema_version_probe() -> khive_storage::types::SqlStatement {
1586    khive_storage::types::SqlStatement::new(SCHEMA_VERSION_PROBE_SQL, Vec::new())
1587}
1588
1589/// Read the applied schema version from an open connection **without** running
1590/// migrations. Returns 0 when the `_schema_migrations` ledger is absent (an
1591/// un-migrated or empty database); any other failure (BUSY, IO) propagates —
1592/// collapsing it to 0 would misreport a live database as un-migrated. Never
1593/// writes.
1594pub fn read_schema_version(conn: &Connection) -> Result<u32, SqliteError> {
1595    match conn.query_row(SCHEMA_VERSION_PROBE_SQL, [], |row| row.get(0)) {
1596        Ok(version) => Ok(version),
1597        Err(rusqlite::Error::SqliteFailure(_, Some(ref msg)))
1598            if msg.contains("no such table: _schema_migrations") =>
1599        {
1600            Ok(0)
1601        }
1602        Err(e) => Err(e.into()),
1603    }
1604}
1605
1606/// Open `path` read-only and report its applied schema version without creating
1607/// or migrating the file. The caller must ensure `path` exists — opening a
1608/// missing file read-only errors rather than creating it. This is the path used
1609/// by schema-inspection commands that must not mutate the database.
1610pub fn inspect_schema_version(path: &std::path::Path) -> Result<u32, SqliteError> {
1611    let conn = crate::pool::open_read_only_snapshot_connection(path)?;
1612    read_schema_version(&conn)
1613}
1614
1615/// Open `path` read-only and require the exact canonical current ledger and
1616/// physical cutover state, without creating files or applying migrations.
1617pub fn inspect_schema_is_current(path: &std::path::Path) -> Result<u32, SqliteError> {
1618    let conn = crate::pool::open_read_only_snapshot_connection(path)?;
1619    validate_schema_is_current(&conn)
1620}
1621
1622/// Page accounting for one database file. Each value is a single pragma read,
1623/// so the cost does not grow with the file.
1624#[derive(Debug, Clone, Copy, PartialEq, Eq)]
1625pub struct PageAccounting {
1626    /// `PRAGMA page_size`, in bytes.
1627    pub page_size: u64,
1628    /// `PRAGMA page_count`: every page in the database, free pages included.
1629    pub page_count: u64,
1630    /// `PRAGMA freelist_count`: pages that later writes can reuse but that stay
1631    /// in the file until a `VACUUM`.
1632    pub freelist_count: u64,
1633}
1634
1635/// Open `path` read-only and report its page accounting without creating,
1636/// migrating or walking the file. The caller must ensure `path` exists.
1637pub fn inspect_page_accounting(path: &std::path::Path) -> Result<PageAccounting, SqliteError> {
1638    let conn = crate::pool::open_read_only_snapshot_connection(path)?;
1639    let read = |pragma: &str| -> Result<u64, SqliteError> {
1640        let value: i64 = conn.query_row(pragma, [], |row| row.get(0))?;
1641        u64::try_from(value)
1642            .map_err(|_| SqliteError::InvalidData(format!("{pragma} returned {value}")))
1643    };
1644    Ok(PageAccounting {
1645        page_size: read("PRAGMA page_size")?,
1646        page_count: read("PRAGMA page_count")?,
1647        freelist_count: read("PRAGMA freelist_count")?,
1648    })
1649}
1650
1651/// Open `path` read-only and run `PRAGMA integrity_check` over the whole file,
1652/// returning every row SQLite reports: exactly `["ok"]` for a sound file. The
1653/// check reads every page, so its cost grows with the file. No error limit is
1654/// applied: the pragma's default stops at 100 rows, so the largest limit it
1655/// accepts is passed instead.
1656pub fn inspect_integrity(path: &std::path::Path) -> Result<Vec<String>, SqliteError> {
1657    let conn = crate::pool::open_read_only_snapshot_connection(path)?;
1658    let mut statement = conn.prepare(&format!("PRAGMA integrity_check({})", i32::MAX))?;
1659    let rows = statement
1660        .query_map([], |row| row.get::<_, String>(0))?
1661        .collect::<Result<Vec<_>, _>>()?;
1662    Ok(rows)
1663}
1664
1665/// Require an already-open database to match this build's latest core schema
1666/// without applying migrations.
1667///
1668/// A read-only snapshot behind the current migration set cannot be repaired in
1669/// place, while a snapshot ahead of the binary may contain schema this build
1670/// does not understand. Both directions fail with an actionable diagnostic; an
1671/// exact match performs no writes.
1672pub fn validate_schema_is_current(conn: &Connection) -> Result<u32, SqliteError> {
1673    let current_version = read_schema_version(conn)?;
1674    let latest_version = latest_schema_version();
1675
1676    if current_version < latest_version {
1677        return Err(SqliteError::InvalidData(format!(
1678            "read-only database schema version {current_version} is behind the latest known \
1679             migration {latest_version}; migrate a writable copy with this build before opening \
1680             the snapshot read-only"
1681        )));
1682    }
1683    if current_version > latest_version {
1684        return Err(SqliteError::InvalidData(format!(
1685            "read-only database schema version {current_version} is ahead of the latest known \
1686             migration {latest_version}; use a compatible newer build or recreate the snapshot"
1687        )));
1688    }
1689
1690    // Numeric equality alone is not enough: a database can carry the current
1691    // maximum version under renamed or foreign migration ledger entries while
1692    // exposing a materially different schema. Writable boot runs this same
1693    // closed-name validation in `bootstrap_migration_ledger`; snapshot inspection
1694    // must not accept a history that ordinary boot would reject merely because
1695    // it cannot repair it in place.
1696    validate_applied_migration_ledger(conn, current_version)?;
1697    // `>=`, not `==`: later migrations (V22+) record on top of a completed
1698    // cutover, and a ledger at the latest version must not exempt the
1699    // physical cutover state from validation.
1700    if current_version >= ATTACHMENT_CUTOVER_VERSION
1701        && attachment_cutover_status(conn)? != AttachmentCutoverStatus::Complete
1702    {
1703        return Err(SqliteError::InvalidData(
1704            "read-only database has not completed the V21 attachment cutover".into(),
1705        ));
1706    }
1707
1708    if current_version >= MEMORY_VISIBILITY_CUTOVER_VERSION {
1709        memory_visibility::validate_cutover(conn)?;
1710    }
1711
1712    Ok(current_version)
1713}
1714
1715/// Require the complete core migration ledger and readable memory provenance
1716/// schema without applying migrations or inferring an individual note's epoch.
1717pub fn validate_memory_visibility_cutover(conn: &Connection) -> Result<(), SqliteError> {
1718    validate_schema_is_current(conn).map(|_| ())
1719}
1720
1721#[cfg(test)]
1722pub(crate) mod test_sync {
1723    use std::sync::atomic::AtomicU32;
1724    use std::sync::{Arc, Barrier, Mutex};
1725
1726    /// When set, `run_versioned_migrations` parks after its initial (stale)
1727    /// ledger read until every racing thread has arrived — forcing the
1728    /// contended interleaving the concurrent-boot test asserts on.
1729    pub(crate) static STALE_READ_BARRIER: Mutex<Option<Arc<Barrier>>> = Mutex::new(None);
1730    /// Counts entries into the under-lock sibling fast-forward branch.
1731    pub(crate) static LOCKED_FAST_FORWARDS: AtomicU32 = AtomicU32::new(0);
1732    /// Set by the SQLite busy handler installed on participating connections:
1733    /// `true` means SQLite itself reported a blocked lock acquisition to the
1734    /// loser — actual contention, not merely an intended attempt.
1735    pub(crate) static BUSY_OBSERVED: std::sync::atomic::AtomicBool =
1736        std::sync::atomic::AtomicBool::new(false);
1737
1738    /// Busy handler for participating test connections: records that SQLite
1739    /// observed a busy acquisition, then keeps retrying.
1740    pub(crate) fn record_busy(_count: i32) -> bool {
1741        BUSY_OBSERVED.store(true, std::sync::atomic::Ordering::SeqCst);
1742        std::thread::sleep(std::time::Duration::from_millis(1));
1743        true
1744    }
1745
1746    /// Set by the winner immediately before committing its first migration
1747    /// transaction — i.e. before the write lock is first released.
1748    pub(crate) static WINNER_COMMITTED: std::sync::atomic::AtomicBool =
1749        std::sync::atomic::AtomicBool::new(false);
1750    /// Recorded by the loser when its first `BEGIN IMMEDIATE` returns: whether
1751    /// the winner had already committed at that moment. `true` is direct
1752    /// evidence the loser's lock acquisition blocked across the winner's held
1753    /// write lock rather than the two calls serializing by scheduler accident.
1754    pub(crate) static LOSER_SAW_WINNER_COMMIT: std::sync::atomic::AtomicBool =
1755        std::sync::atomic::AtomicBool::new(false);
1756
1757    std::thread_local! {
1758        /// Opt-in flag: only threads that set this participate in the barrier,
1759        /// so unrelated tests migrating in parallel are never parked.
1760        pub(crate) static PARTICIPATE: std::cell::Cell<bool> =
1761            const { std::cell::Cell::new(false) };
1762        /// Whether this thread has already instrumented its first BEGIN.
1763        pub(crate) static FIRST_BEGIN_DONE: std::cell::Cell<bool> =
1764            const { std::cell::Cell::new(false) };
1765    }
1766}
1767
1768/// Apply the ordinary unapplied migration prefix in order.
1769///
1770/// The operation is idempotent and each ordinary migration runs in its own
1771/// transaction. V21 is the application-assisted exception: a database with no
1772/// legacy content references may complete V21 atomically here, while a legacy
1773/// database stops successfully at V20 so the async host can stage, verify, and
1774/// finalize the attachment cutover. Errors on a non-contiguous migration array,
1775/// a non-canonical applied ledger, or a failed migration.
1776fn canonical_connection_database_path(conn: &Connection) -> Result<Option<PathBuf>, SqliteError> {
1777    let configured = conn.path().unwrap_or_default();
1778    let raw_path = if configured.is_empty() {
1779        conn.query_row(
1780            "SELECT file FROM pragma_database_list WHERE name = 'main'",
1781            [],
1782            |row| row.get::<_, String>(0),
1783        )?
1784    } else {
1785        configured.to_string()
1786    };
1787
1788    if raw_path.is_empty() {
1789        return Ok(None);
1790    }
1791    let canonical = std::fs::canonicalize(&raw_path).map_err(SqliteError::Io)?;
1792    #[cfg(any(unix, windows))]
1793    crate::pool::opened_sqlite_file_identity(conn, &canonical)?;
1794    Ok(Some(canonical))
1795}
1796
1797fn validate_database_gc_owner(
1798    conn: &Connection,
1799    owner: &DatabaseGcOwnerGuard,
1800) -> Result<(), SqliteError> {
1801    let connection_path = canonical_connection_database_path(conn)?;
1802    if owner.database_path() != connection_path.as_deref() {
1803        return Err(SqliteError::InvalidData(format!(
1804            "database GC owner targets {:?}, but migration connection targets {:?}",
1805            owner.database_path(),
1806            connection_path.as_deref(),
1807        )));
1808    }
1809    Ok(())
1810}
1811
1812pub fn run_migrations(conn: &mut Connection) -> Result<u32, SqliteError> {
1813    let database_path = canonical_connection_database_path(conn)?;
1814    let admission = WriteAdmission::for_canonical_path(database_path.clone())?;
1815    run_raw_migrations_with_admission(conn, database_path, &admission)
1816}
1817
1818/// Run migrations on a raw connection with a captured admission policy.
1819pub fn run_migrations_with_policy(
1820    conn: &mut Connection,
1821    policy: &MigrationWritePolicy,
1822) -> Result<u32, SqliteError> {
1823    let database_path = canonical_connection_database_path(conn)?;
1824    let admission = WriteAdmission::for_migration_policy(database_path.clone(), policy)?;
1825    run_raw_migrations_with_admission(conn, database_path, &admission)
1826}
1827
1828fn run_raw_migrations_with_admission(
1829    conn: &mut Connection,
1830    database_path: Option<PathBuf>,
1831    admission: &WriteAdmission,
1832) -> Result<u32, SqliteError> {
1833    if let Some(database_path) = database_path {
1834        // This raw API may have been handed a connection behind an opaque pool
1835        // writer guard. Never wait here and invert the canonical
1836        // owner-before-writer order; fail closed and direct production callers
1837        // to `StorageBackend::prepare_core_schema` instead.
1838        let owner = try_acquire_database_gc_owner_for_path(database_path).map_err(|error| {
1839            SqliteError::InvalidData(format!(
1840                "failed to acquire database GC owner before schema migration: {error}"
1841            ))
1842        })?;
1843        return run_migrations_with_database_gc_owner(
1844            &mut RawMigrationTransactions::new(conn, admission),
1845            &owner,
1846            admission,
1847        );
1848    }
1849
1850    // A raw in-memory connection has no durable/cross-process GC domain. The
1851    // production in-memory backend still uses the owner-aware path below.
1852    run_versioned_migrations(
1853        &mut RawMigrationTransactions::new(conn, admission),
1854        None,
1855        admission,
1856    )
1857}
1858
1859pub(crate) fn run_migrations_with_database_gc_owner(
1860    writes: &mut impl MigrationTransactions,
1861    owner: &DatabaseGcOwnerGuard,
1862    admission: &WriteAdmission,
1863) -> Result<u32, SqliteError> {
1864    run_versioned_migrations(writes, Some(owner), admission)
1865}
1866
1867/// Raise-only to a 5s busy_timeout floor for one admitted migration unit and
1868/// restore the caller's value after it. Concurrent boots (multiple processes
1869/// migrating the same file) contend on SQLite's write lock; a short hot-path
1870/// busy_timeout cannot wait out a sibling's migration, and a caller whose
1871/// configured timeout is already longer is never reduced.
1872fn with_migration_busy_timeout<T>(
1873    conn: &mut Connection,
1874    operation: impl FnOnce(&mut Connection) -> Result<T, SqliteError>,
1875) -> Result<T, SqliteError> {
1876    let prior_busy_ms: i64 = conn.query_row("PRAGMA busy_timeout", [], |row| row.get(0))?;
1877    let raised = prior_busy_ms < 5_000;
1878    if raised {
1879        conn.busy_timeout(std::time::Duration::from_secs(5))?;
1880    }
1881    let result = operation(conn);
1882    if raised {
1883        let _ = conn.busy_timeout(std::time::Duration::from_millis(prior_busy_ms.max(0) as u64));
1884    }
1885    result
1886}
1887
1888/// What one admitted versioned-migration transaction did.
1889enum MigrationStep {
1890    /// This transaction applied the migration and committed.
1891    Applied,
1892    /// A sibling had already recorded this version or later; the value is the
1893    /// ledger maximum read under the write lock.
1894    AppliedBySibling(u32),
1895    /// V21 on a legacy database: left for the application-assisted cutover.
1896    Deferred,
1897}
1898
1899/// Bring the core schema to the latest version.
1900///
1901/// ADR-154 section 4 admits each migration transaction on its own, so the
1902/// ledger bootstrap with the pre-lock ledger read is one admitted unit and
1903/// every migration that still has to run is another, re-reading the ledger
1904/// under the write lock. No volume lease is held from one version to the next.
1905fn run_versioned_migrations(
1906    writes: &mut impl MigrationTransactions,
1907    owner: Option<&DatabaseGcOwnerGuard>,
1908    admission: &WriteAdmission,
1909) -> Result<u32, SqliteError> {
1910    let current_version = writes.admitted(|conn| {
1911        if let Some(owner) = owner {
1912            validate_database_gc_owner(conn, owner)?;
1913        }
1914        with_migration_busy_timeout(conn, |conn| bootstrap_migration_ledger(conn, admission))
1915    })?;
1916
1917    // Deterministic-contention hook: parks every caller after the stale ledger
1918    // read (no SQLite lock and no volume lease held) until all racing test
1919    // threads have observed it, so they are then released to compete for the
1920    // IMMEDIATE write lock of their first migration.
1921    #[cfg(test)]
1922    if test_sync::PARTICIPATE.with(|p| p.get()) {
1923        let barrier = test_sync::STALE_READ_BARRIER.lock().unwrap().clone();
1924        if let Some(barrier) = barrier {
1925            barrier.wait();
1926        }
1927    }
1928    let latest_version = latest_schema_version();
1929
1930    let mut applied_version = current_version;
1931    // Floor advanced when a sibling's work is observed under the write lock,
1932    // so a losing process skips the remaining already-applied migrations
1933    // without opening a transaction for each.
1934    let mut skip_through = current_version;
1935
1936    for migration in MIGRATIONS {
1937        if migration.version <= skip_through {
1938            applied_version = applied_version.max(migration.version);
1939            continue;
1940        }
1941        let step = writes.admitted(|conn| {
1942            with_migration_busy_timeout(conn, |conn| {
1943                apply_versioned_migration(conn, migration, admission, latest_version)
1944            })
1945        })?;
1946        match step {
1947            MigrationStep::Applied => applied_version = migration.version,
1948            MigrationStep::AppliedBySibling(sibling_version) => {
1949                skip_through = sibling_version.min(latest_version);
1950                applied_version = applied_version.max(migration.version);
1951            }
1952            MigrationStep::Deferred => break,
1953        }
1954    }
1955
1956    // Validate again after the loop: our own commits and any under-lock
1957    // sibling fast-forward must both leave the exact canonical ledger, not
1958    // merely advance its maximum version.
1959    writes.admitted(|conn| validate_applied_migration_ledger(conn, applied_version))?;
1960
1961    Ok(applied_version)
1962}
1963
1964/// Create the migration ledger if it is missing and validate what it records,
1965/// returning the recorded version. The read is taken before any migration
1966/// write lock; each migration re-reads the ledger under that lock.
1967fn bootstrap_migration_ledger(
1968    conn: &mut Connection,
1969    admission: &WriteAdmission,
1970) -> Result<u32, SqliteError> {
1971    admission.check()?;
1972    conn.execute_batch(MIGRATION_TRACKING_TABLE)?;
1973    require_autocommit(conn, "migration-tracking bootstrap")?;
1974
1975    let current_version: u32 = read_schema_version(conn)?;
1976
1977    // A database whose recorded version is ahead of the latest known migration
1978    // predates the consolidated V1 baseline (ADR-015) — e.g. it still carries the
1979    // pre-consolidation V2..V22 ledger — or was written by a newer build. Either
1980    // way the baseline schema would be silently skipped, leaving the process on a
1981    // stale schema. Fail loudly instead of corrupting silently.
1982    let latest_version = latest_schema_version();
1983    if current_version > latest_version {
1984        return Err(SqliteError::InvalidData(format!(
1985            "database schema version {current_version} is ahead of the latest known migration \
1986             {latest_version}. This database predates the consolidated baseline (ADR-015) or was \
1987             written by a newer build. Recreate it from the current schema; in-place downgrade is \
1988             not supported."
1989        )));
1990    }
1991
1992    // Every writable upgrade starts from an exact canonical version sequence;
1993    // fail before applying new migrations if MAX(version) hides a missing or
1994    // foreign row. A pre-V19 database may still carry the V13/V14 name
1995    // divergence that V19 exists to repair. Name validation therefore permits
1996    // exactly those two rows before V19, while every unrelated mismatch still
1997    // fails before any new migration is applied.
1998    let applied = read_applied_migration_ledger(conn, current_version)?;
1999    validate_applied_migration_versions(&applied, current_version)?;
2000    validate_applied_migration_names(&applied, current_version, current_version < 19)?;
2001
2002    Ok(current_version)
2003}
2004
2005/// Apply one versioned migration in its own IMMEDIATE transaction, probing
2006/// capacity after BEGIN and before its first statement (ADR-154 section 4).
2007fn apply_versioned_migration(
2008    conn: &mut Connection,
2009    migration: &VersionedMigration,
2010    admission: &WriteAdmission,
2011    latest_version: u32,
2012) -> Result<MigrationStep, SqliteError> {
2013    // IMMEDIATE: take the write lock up front so concurrent boots serialize
2014    // here instead of failing mid-migration when a DEFERRED transaction
2015    // upgrades to a write.
2016    #[cfg(test)]
2017    let instrumented_first_begin =
2018        test_sync::PARTICIPATE.with(|p| p.get()) && !test_sync::FIRST_BEGIN_DONE.with(|f| f.get());
2019    #[cfg(test)]
2020    if instrumented_first_begin {
2021        test_sync::FIRST_BEGIN_DONE.with(|f| f.set(true));
2022    }
2023    // Replaces the busy_timeout raised for this unit on a participating test
2024    // connection: records SQLite-observed contention, then keeps retrying.
2025    #[cfg(test)]
2026    if test_sync::PARTICIPATE.with(|p| p.get()) {
2027        conn.busy_handler(Some(test_sync::record_busy))?;
2028    }
2029    let tx = conn
2030        .transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)
2031        .map_err(|e| SqliteError::Migration {
2032            version: migration.version,
2033            error: e.to_string(),
2034        })?;
2035    if let Err(error) = admission.check() {
2036        let rollback = tx.rollback();
2037        return Err(capacity_refusal_after_rollback(
2038            conn,
2039            rollback,
2040            error,
2041            "core schema migration",
2042        ));
2043    }
2044
2045    // Re-check under the write lock: a sibling process may have applied
2046    // this migration (and possibly later ones) while we waited. Running
2047    // its DDL again would fail; fast-forward past everything it applied.
2048    let sibling_version: u32 = tx
2049        .query_row(SCHEMA_VERSION_PROBE_SQL, [], |row| row.get(0))
2050        .map_err(|e| SqliteError::Migration {
2051            version: migration.version,
2052            error: e.to_string(),
2053        })?;
2054    #[cfg(test)]
2055    if instrumented_first_begin {
2056        use std::sync::atomic::Ordering::SeqCst;
2057        if sibling_version == 0 {
2058            // Winner: hold the write lock until SQLite has reported a
2059            // busy acquisition to the loser (its busy handler fired) —
2060            // proof the loser's BEGIN is actually blocked on this held
2061            // lock, not merely intended. Bounded so a regression fails
2062            // the assertion instead of hanging the test.
2063            let deadline = std::time::Instant::now() + std::time::Duration::from_secs(5);
2064            while !test_sync::BUSY_OBSERVED.load(SeqCst) && std::time::Instant::now() < deadline {
2065                std::thread::yield_now();
2066            }
2067        } else {
2068            // Loser: our first BEGIN just returned. Record whether the
2069            // winner had already committed — true means we blocked across
2070            // its held lock.
2071            test_sync::LOSER_SAW_WINNER_COMMIT
2072                .store(test_sync::WINNER_COMMITTED.load(SeqCst), SeqCst);
2073        }
2074    }
2075
2076    // The ahead-of-latest guard above ran on a pre-lock read; a newer
2077    // build may have committed a version past ours while we waited for
2078    // the write lock. Accepting it (clamped) would return Ok on a schema
2079    // this binary does not understand — reject it the same way.
2080    if sibling_version > latest_version {
2081        return Err(SqliteError::InvalidData(format!(
2082            "database schema version {sibling_version} is ahead of the latest known \
2083             migration {latest_version} (committed by a concurrent process while this \
2084             one waited for the migration write lock). This build cannot run against \
2085             the newer schema; upgrade the binary or recreate the database."
2086        )));
2087    }
2088
2089    if sibling_version >= migration.version {
2090        #[cfg(test)]
2091        test_sync::LOCKED_FAST_FORWARDS.fetch_add(1, std::sync::atomic::Ordering::Relaxed);
2092        return Ok(MigrationStep::AppliedBySibling(sibling_version));
2093    }
2094
2095    if migration.version == 46 {
2096        memory_visibility::capture_pre_v46(&tx).map_err(|error| SqliteError::Migration {
2097            version: migration.version,
2098            error: error.to_string(),
2099        })?;
2100        #[cfg(test)]
2101        memory_visibility::test_state::stop_at(memory_visibility::test_state::Stop::AfterCapture)?;
2102    }
2103
2104    if migration.version == ATTACHMENT_CUTOVER_VERSION {
2105        let status = attachment_cutover_status(&tx).map_err(|e| SqliteError::Migration {
2106            version: migration.version,
2107            error: e.to_string(),
2108        })?;
2109        let legacy_refs: i64 = tx
2110            .query_row(
2111                "SELECT COUNT(*) FROM entities WHERE content_ref IS NOT NULL",
2112                [],
2113                |row| row.get(0),
2114            )
2115            .map_err(|e| SqliteError::Migration {
2116                version: migration.version,
2117                error: e.to_string(),
2118            })?;
2119
2120        // V21 belongs to the boot coordinator. Ordinary backend open may
2121        // finish the degenerate zero-ref case atomically, but it must not
2122        // expose a dual-source interval or eagerly stage a legacy DB.
2123        if status == AttachmentCutoverStatus::Incomplete || legacy_refs != 0 {
2124            drop(tx);
2125            return Ok(MigrationStep::Deferred);
2126        }
2127        if status != AttachmentCutoverStatus::Pending {
2128            return Err(SqliteError::Migration {
2129                version: migration.version,
2130                error: format!("unexpected attachment cutover state {status:?}"),
2131            });
2132        }
2133
2134        let now = chrono::Utc::now().timestamp_micros();
2135        stage_attachment_cutover_on_connection(&tx, now).map_err(|e| SqliteError::Migration {
2136            version: migration.version,
2137            error: e.to_string(),
2138        })?;
2139        finalize_attachment_cutover_on_connection(&tx, now).map_err(|e| {
2140            SqliteError::Migration {
2141                version: migration.version,
2142                error: e.to_string(),
2143            }
2144        })?;
2145    } else if migration.version == 36 {
2146        // The events store DDL may already have added these columns.
2147        crate::stores::event::ensure_operation_attribution_columns(&tx).map_err(|error| {
2148            SqliteError::Migration {
2149                version: migration.version,
2150                error: error.to_string(),
2151            }
2152        })?;
2153    } else if migration.version == 44 {
2154        migrate_outbound_due_key(&tx).map_err(|error| SqliteError::Migration {
2155            version: migration.version,
2156            error: error.to_string(),
2157        })?;
2158    } else if migration.version == 48 {
2159        migrate_acknowledgement_journal(&tx).map_err(|error| SqliteError::Migration {
2160            version: migration.version,
2161            error: error.to_string(),
2162        })?;
2163    } else if migration.version == 58 {
2164        migrate_proposal_archive(&tx)?;
2165    } else if migration.name == SESSION_IDENTITY_MIGRATION_NAME {
2166        tx.execute_batch(migration.up)
2167            .map_err(|error| SqliteError::Migration {
2168                version: migration.version,
2169                error: error.to_string(),
2170            })?;
2171        session_identity_migration::apply(&tx).map_err(|error| SqliteError::Migration {
2172            version: migration.version,
2173            error: error.to_string(),
2174        })?;
2175    } else {
2176        tx.execute_batch(migration.up)
2177            .map_err(|e| SqliteError::Migration {
2178                version: migration.version,
2179                error: e.to_string(),
2180            })?;
2181    }
2182
2183    let visibility_counts = if migration.version == MEMORY_VISIBILITY_CUTOVER_VERSION {
2184        Some(
2185            memory_visibility::cutover_counts(&tx).map_err(|error| SqliteError::Migration {
2186                version: migration.version,
2187                error: error.to_string(),
2188            })?,
2189        )
2190    } else {
2191        None
2192    };
2193
2194    // V19's repair contract includes normalizing the two known-divergent
2195    // recorded names. `_schema_migrations` is created and owned by this
2196    // runner (not by any migration file), so the normalization lives
2197    // here, in the same transaction that applies V19's SQL. Exact,
2198    // closed set — versions 13 and 14 only; any other (version, name)
2199    // mismatch still fails startup via validate_applied_migration_ledger.
2200    if migration.version == 19 {
2201        tx.execute_batch(
2202            "UPDATE _schema_migrations SET name = 'list_cursor_sequences' WHERE version = 13;\n\
2203             UPDATE _schema_migrations SET name = 'graph_edges_id_unique' WHERE version = 14;",
2204        )
2205        .map_err(|e| SqliteError::Migration {
2206            version: migration.version,
2207            error: e.to_string(),
2208        })?;
2209    }
2210
2211    let now = chrono::Utc::now().timestamp_micros();
2212    tx.execute(
2213        "INSERT INTO _schema_migrations (version, name, applied_at) VALUES (?1, ?2, ?3) \
2214         ON CONFLICT(version) DO NOTHING",
2215        rusqlite::params![migration.version, migration.name, now],
2216    )
2217    .map_err(|e| SqliteError::Migration {
2218        version: migration.version,
2219        error: e.to_string(),
2220    })?;
2221
2222    #[cfg(test)]
2223    if instrumented_first_begin {
2224        test_sync::WINNER_COMMITTED.store(true, std::sync::atomic::Ordering::SeqCst);
2225    }
2226
2227    #[cfg(test)]
2228    if migration.version == MEMORY_VISIBILITY_CUTOVER_VERSION {
2229        memory_visibility::test_state::stop_at(
2230            memory_visibility::test_state::Stop::BeforeCutoverCommit,
2231        )?;
2232    }
2233    tx.commit().map_err(|e| SqliteError::Migration {
2234        version: migration.version,
2235        error: e.to_string(),
2236    })?;
2237    if let Some(counts) = visibility_counts {
2238        memory_visibility::log_counts(&counts, conn.path().unwrap_or(":memory:"));
2239    }
2240    #[cfg(test)]
2241    if migration.version == 46 {
2242        memory_visibility::test_state::stop_at(
2243            memory_visibility::test_state::Stop::AfterV46Commit,
2244        )?;
2245    }
2246
2247    Ok(MigrationStep::Applied)
2248}
2249
2250#[derive(Debug)]
2251pub struct EmbeddingModelRegistryRecord {
2252    /// Vector engine name (e.g. `"paraphrase"`).
2253    pub engine_name: String,
2254    /// Model identifier (e.g. `"all-minilm-l6-v2"`).
2255    pub model_id: String,
2256    /// Canonical deduplication key combining engine and model.
2257    pub key_version: String,
2258    /// Embedding dimensionality.
2259    pub dimensions: u32,
2260    /// Lifecycle status (`"active"` or `"superseded"`).
2261    pub status: String,
2262    /// Epoch timestamp when the model was activated.
2263    pub activated_at: Option<i64>,
2264    /// Epoch timestamp when the model was superseded.
2265    pub superseded_at: Option<i64>,
2266}
2267
2268/// Query the `_embedding_models` registry.
2269///
2270/// Opens the database at `db` (defaults to `~/.khive/khive.db`) and
2271/// returns all registry rows, optionally filtered by `engine_name`.
2272/// Returns an empty vec if the database or table does not exist.
2273pub fn query_embedding_models(
2274    db: Option<&std::path::Path>,
2275    engine_filter: Option<&str>,
2276) -> Result<Vec<EmbeddingModelRegistryRecord>, SqliteError> {
2277    let path = db.map(std::path::Path::to_path_buf).unwrap_or_else(|| {
2278        std::env::var("HOME")
2279            .map(std::path::PathBuf::from)
2280            .unwrap_or_else(|_| std::path::PathBuf::from("."))
2281            .join(".khive/khive.db")
2282    });
2283    if !path.exists() {
2284        return Ok(Vec::new());
2285    }
2286    let conn = Connection::open_with_flags(
2287        path,
2288        rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY
2289            | rusqlite::OpenFlags::SQLITE_OPEN_NO_MUTEX
2290            | rusqlite::OpenFlags::SQLITE_OPEN_URI,
2291    )?;
2292    query_embedding_models_conn(&conn, engine_filter)
2293}
2294
2295/// Query `_embedding_models` from an existing connection (testable without a file).
2296///
2297/// Returns an empty vec if the table does not exist.
2298pub(crate) fn query_embedding_models_conn(
2299    conn: &Connection,
2300    engine_filter: Option<&str>,
2301) -> Result<Vec<EmbeddingModelRegistryRecord>, SqliteError> {
2302    let exists: bool = conn.query_row(
2303        "SELECT COUNT(*) > 0 FROM sqlite_master \
2304         WHERE type='table' AND name='_embedding_models'",
2305        [],
2306        |row| row.get(0),
2307    )?;
2308    if !exists {
2309        return Ok(Vec::new());
2310    }
2311
2312    let sql = if engine_filter.is_some() {
2313        "SELECT engine_name, model_id, key_version, dim, status, activated_at, superseded_at \
2314         FROM _embedding_models WHERE engine_name = ?1 \
2315         ORDER BY engine_name, activated_at IS NULL, activated_at"
2316    } else {
2317        "SELECT engine_name, model_id, key_version, dim, status, activated_at, superseded_at \
2318         FROM _embedding_models \
2319         ORDER BY engine_name, activated_at IS NULL, activated_at"
2320    };
2321    let mut stmt = conn.prepare(sql)?;
2322    let map_row = |row: &rusqlite::Row<'_>| {
2323        let dim_raw: i64 = row.get(3)?;
2324        let dimensions = u32::try_from(dim_raw).map_err(|_| {
2325            rusqlite::Error::FromSqlConversionFailure(
2326                3,
2327                rusqlite::types::Type::Integer,
2328                Box::new(std::io::Error::other(format!(
2329                    "_embedding_models.dim value {dim_raw} is outside the valid u32 range [0, {}]",
2330                    u32::MAX,
2331                ))),
2332            )
2333        })?;
2334        Ok(EmbeddingModelRegistryRecord {
2335            engine_name: row.get(0)?,
2336            model_id: row.get(1)?,
2337            key_version: row.get(2)?,
2338            dimensions,
2339            status: row.get(4)?,
2340            activated_at: row.get(5)?,
2341            superseded_at: row.get(6)?,
2342        })
2343    };
2344
2345    if let Some(engine) = engine_filter {
2346        stmt.query_map([engine], map_row)?
2347            .collect::<Result<Vec<_>, _>>()
2348            .map_err(Into::into)
2349    } else {
2350        stmt.query_map([], map_row)?
2351            .collect::<Result<Vec<_>, _>>()
2352            .map_err(Into::into)
2353    }
2354}
2355
2356// Test fixtures use the public policy constructor and a private lock namespace.
2357#[cfg(test)]
2358pub(crate) fn migration_test_policy() -> MigrationWritePolicy {
2359    MigrationWritePolicy::new(
2360        crate::DiskGuardEnvironment::capture()
2361            .resolve(None, None)
2362            .expect("test disk policy"),
2363        crate::PoolConfig::for_test()
2364            .volume_lock_dir
2365            .expect("test volume-lock directory"),
2366    )
2367    .expect("valid test migration policy")
2368}
2369
2370#[cfg(test)]
2371pub(crate) fn run_migrations_for_test(conn: &mut Connection) -> Result<u32, SqliteError> {
2372    run_migrations_with_policy(conn, &migration_test_policy())
2373}
2374
2375#[cfg(test)]
2376fn apply_schema_plan_for_test(
2377    conn: &mut Connection,
2378    plan: &ServiceSchemaPlan,
2379) -> Result<(), SqliteError> {
2380    apply_schema_plan_with_policy(conn, plan, &migration_test_policy())
2381}
2382
2383#[cfg(test)]
2384fn stage_attachment_cutover_for_test(conn: &mut Connection) -> Result<(), SqliteError> {
2385    stage_attachment_cutover_with_policy(conn, &migration_test_policy())
2386}
2387
2388#[cfg(test)]
2389fn finalize_attachment_cutover_for_test(conn: &mut Connection) -> Result<(), SqliteError> {
2390    finalize_attachment_cutover_with_policy(conn, &migration_test_policy())
2391}
2392
2393// =============================================================================
2394// Tests
2395// =============================================================================
2396
2397#[cfg(test)]
2398#[path = "entity_version_migration_measurement.rs"]
2399mod entity_version_measurement;
2400
2401#[cfg(test)]
2402#[path = "migrations_tests.rs"]
2403mod tests;
2404
2405#[cfg(test)]
2406#[path = "raw_migration_settlement_tests.rs"]
2407mod raw_settlement_tests;
2408
2409#[cfg(test)]
2410#[path = "git_note_index_migration_tests.rs"]
2411mod git_note_indexes;
2412
2413#[cfg(test)]
2414#[path = "entity_list_index_migration_tests.rs"]
2415mod entity_list_indexes;
2416
2417#[cfg(test)]
2418#[path = "schedule_core_index_migration_tests.rs"]
2419mod schedule_core_index_migration_tests;
2420
2421#[cfg(test)]
2422#[path = "comm_core_index_migration_tests.rs"]
2423mod comm_core_index_migration_tests;
2424
2425#[cfg(test)]
2426#[path = "knowledge_cursor_tag_migration_tests.rs"]
2427mod knowledge_cursor_tag_migration_tests;