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