Skip to main content

khive_db/
migrations.rs

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