michal/tit

Browse tree · Show commit · Download archive

Diff

7d8762c45fe0 → 393c9decda7b

@@ -30,4 +30,5 @@
           persist-credentials: false
       - run: cargo --version
       - run: cargo test --locked --all-targets --all-features
+      - run: cargo test --locked --release --test sqlite_workload -- --ignored --nocapture
       - run: cargo build --locked --release

@@ -104,6 +104,8 @@
 - advertised hostname, hostname, onion hostname, and proxy command.
 - database constraint, foreign key, online backup, prepared statement, and
   schema migration.
+- durability gate, local filesystem, migration backup, percentile, and
+  workload gate.
 
 Use the technical verbs in this list:
 

@@ -27,6 +27,7 @@
 System installations keep the full instance in `/srv/tit` by default.
 This includes `config.toml`, the metadata database, bare repositories, SSH host
 keys, instance secrets, and recoverable operational state.
+The metadata database name is `tit.sqlite3`.
 
 ## Product boundaries
 
@@ -535,6 +536,10 @@
 
 Gate: restored and migrated fixtures pass SQLite integrity checks and `doctor`.
 No handler needs direct access to a `rusqlite` type or SQL statement.
+
+The M1A evidence and limits are in
+`docs/adr/0001-sqlite-storage.md`. The decision stays provisional until the
+Linux and macOS CI workload gates pass.
 
 ##### M1B — SSH identity
 

@@ -25,6 +25,31 @@
 
 This command formats, lints, tests, audits, and builds the release executable.
 
+## Milestone 1A gate
+
+Run the SQLite durability gate on a local filesystem:
+
+```text
+./scripts/check-m1a
+```
+
+This command also creates and measures a release database with 10,000 issue
+records and 1,000,000 event records. Read the SQLite
+[architectural decision record](docs/adr/0001-sqlite-storage.md) for the limits
+and current platform evidence.
+
+## Database check
+
+An initialized instance keeps its metadata in `tit.sqlite3`. Check an existing
+database with this command:
+
+```text
+tit --config /absolute/path/to/tit/config.toml doctor
+```
+
+The command does not create or migrate a database. Successful validation writes
+no output and returns exit code 0.
+
 ## Configuration validation
 
 Copy `config.example.toml` into an empty instance directory. Change

@@ -1,0 +1,66 @@
+# Architectural decision record 0001: SQLite storage
+
+Status: Provisional
+
+Date: 2026-07-22
+
+## Context
+
+`tit` needs one inspectable metadata database in the instance directory. The
+database must have transactions, constraints, indexes, online backup, and a
+clear recovery procedure. The executable must not need an installed database
+server or an installed SQLite shared library.
+
+## Decision
+
+Use `rusqlite` with the `bundled` and `backup` features. Keep SQL and
+`rusqlite` types in the `store` module. Use numbered SQL migrations and
+`PRAGMA user_version`. Apply all pending migrations in one exclusive
+transaction. Create an online backup before an automatic migration of an
+existing database.
+
+Use WAL mode, `synchronous=FULL`, a five-second busy timeout, and foreign-key
+enforcement on each connection. Verify each setting. Use `tit.sqlite3` as the
+metadata database name. Require a local filesystem until a platform gate proves
+that a different filesystem has correct WAL behavior.
+
+`tit doctor` opens an existing database without the create flag. It does not
+migrate the database. It verifies the schema version, SQLite integrity, and
+foreign keys.
+
+## Evidence
+
+The local gate used Rust 1.96.0 on arm64 macOS 27.0 and a local APFS filesystem.
+It killed child processes during writes and schema migrations. After each kill,
+the database contained the initial full state or the new full state. Tests also
+proved constraints, indexed scans, concurrent reads, serialized writes, busy
+handling, rollback, WAL checkpoint, vacuum, online backup, restore, and
+migration from each committed fixture.
+
+The release workload used 10,000 parent records as issues and 1,000,000 child
+records as events. The measured database size was 113,926,144 bytes. A safe
+migration, including its required backup, took 774 ms. A subsequent online
+backup took 509 ms. For 1,000 indexed queries that each read 25 events, the 50th,
+95th, and 99th percentile times were 4, 5, and 8 microseconds.
+
+The workload gate has these limits:
+
+- the database must not be larger than 1 GiB.
+- migration and backup must each complete in 120 seconds.
+- the 99th percentile query time must not be more than 250 ms.
+
+The GitHub Actions release matrix runs the functional and workload gates on
+Linux and macOS. This decision stays provisional until both hosted runners pass.
+BSD and non-local filesystem support stay outside the current supported
+platform set.
+
+## Consequences
+
+SQLite adds a small amount of explicit SQL, but it gives standard inspection
+and recovery tools and a stable file format. Bundled SQLite increases the
+executable size, but it removes variation in the runtime SQLite version.
+
+The application has one serialized SQLite writer. WAL lets readers continue
+during a write. If the measured workload becomes too large for this model, the
+project must review the evidence before it adds a database server, a connection
+pool, or another persistence abstraction.

@@ -1,0 +1,5 @@
+#!/bin/sh
+set -eu
+
+./scripts/check
+cargo test --locked --release --test sqlite_workload -- --ignored --nocapture

@@ -1,7 +1,7 @@
 use std::net::SocketAddr;
 use std::path::PathBuf;
 
-use clap::Parser;
+use clap::{Parser, Subcommand};
 use url::Url;
 
 #[derive(Debug, Parser)]
@@ -12,6 +12,9 @@
     arg_required_else_help = true
 )]
 pub(crate) struct Cli {
+    #[command(subcommand)]
+    pub(crate) command: Option<Command>,
+
     /// Read configuration from FILE
     #[arg(long, value_name = "FILE", conflicts_with = "user")]
     pub(crate) config: Option<PathBuf>,
@@ -39,4 +42,10 @@
     /// Override the public SSH port
     #[arg(long, value_name = "PORT")]
     pub(crate) ssh_public_port: Option<u16>,
+}
+
+#[derive(Clone, Copy, Debug, Subcommand)]
+pub(crate) enum Command {
+    /// Check the instance database
+    Doctor,
 }

@@ -1,11 +1,12 @@
 mod cli;
 mod config;
+mod store;
 
 use std::process::ExitCode;
 
 use clap::Parser;
 
-use crate::cli::Cli;
+use crate::cli::{Cli, Command};
 
 fn main() -> ExitCode {
     let cli = match Cli::try_parse() {
@@ -18,7 +19,16 @@
     };
 
     match config::load(&cli) {
-        Ok(_config) => ExitCode::SUCCESS,
+        Ok(config) => match cli.command {
+            None => ExitCode::SUCCESS,
+            Some(Command::Doctor) => match store::doctor(&config.instance_dir) {
+                Ok(()) => ExitCode::SUCCESS,
+                Err(error) => {
+                    eprintln!("tit: {error}");
+                    ExitCode::FAILURE
+                }
+            },
+        },
         Err(error) => {
             eprintln!("tit: {error}");
             ExitCode::FAILURE

@@ -1,0 +1,15 @@
+CREATE TABLE m1a_parent (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL UNIQUE CHECK (length(name) BETWEEN 1 AND 64),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+CREATE INDEX m1a_parent_created_at ON m1a_parent (created_at, id);
+
+CREATE TABLE m1a_child (
+    id INTEGER PRIMARY KEY,
+    parent_id INTEGER NOT NULL REFERENCES m1a_parent (id) ON DELETE RESTRICT,
+    sequence INTEGER NOT NULL CHECK (sequence >= 0),
+    body TEXT NOT NULL,
+    UNIQUE (parent_id, sequence)
+) STRICT;

@@ -1,0 +1,6 @@
+ALTER TABLE m1a_child
+ADD COLUMN state TEXT NOT NULL DEFAULT 'open'
+CHECK (state IN ('open', 'closed'));
+
+CREATE INDEX m1a_child_state_parent
+ON m1a_child (state, parent_id, sequence);

@@ -1,0 +1,261 @@
+use std::ffi::OsString;
+use std::path::{Path, PathBuf};
+use std::time::Duration;
+
+use rusqlite::OpenFlags;
+use rusqlite::backup::Backup;
+use rusqlite::{Connection, TransactionBehavior};
+use thiserror::Error;
+
+const BUSY_TIMEOUT: Duration = Duration::from_secs(5);
+const SCHEMA_VERSION: i64 = 2;
+#[allow(
+    dead_code,
+    reason = "the integration test imports this module without the CLI operation"
+)]
+const DATABASE_FILE: &str = "tit.sqlite3";
+#[allow(
+    dead_code,
+    reason = "M1A proves migrations before the M2 server calls them"
+)]
+const MIGRATIONS: [&str; 2] = [
+    include_str!("migrations/001_initial.sql"),
+    include_str!("migrations/002_state.sql"),
+];
+
+#[derive(Debug, Error)]
+pub(crate) enum StoreError {
+    #[error("SQLite error: {0}")]
+    Sqlite(#[from] rusqlite::Error),
+    #[allow(
+        dead_code,
+        reason = "M1A proves migrations before the M2 server calls them"
+    )]
+    #[error("database schema version {0} is newer than this executable")]
+    NewerSchema(i64),
+    #[allow(
+        dead_code,
+        reason = "the integration test imports this module without the CLI operation"
+    )]
+    #[error("database schema version is {actual}, expected {expected}")]
+    SchemaVersion { expected: i64, actual: i64 },
+    #[error("database integrity check failed: {0}")]
+    Integrity(String),
+    #[error("SQLite setting {name} is {actual}, expected {expected}")]
+    Setting {
+        name: &'static str,
+        expected: &'static str,
+        actual: String,
+    },
+}
+
+pub(crate) struct Store {
+    connection: Connection,
+}
+
+impl Store {
+    #[allow(
+        dead_code,
+        reason = "M1A proves migrations before the M2 server calls them"
+    )]
+    pub(crate) fn open(path: &Path) -> Result<Self, StoreError> {
+        let mut store = Self::open_unmigrated(path)?;
+        let current = store.schema_version()?;
+        if current > 0 && current < SCHEMA_VERSION {
+            store.backup(&migration_backup_path(path, current))?;
+        }
+        store.migrate()?;
+        Ok(store)
+    }
+
+    #[allow(
+        dead_code,
+        reason = "M1A proves migrations before the M2 server calls them"
+    )]
+    pub(crate) fn open_unmigrated(path: &Path) -> Result<Self, StoreError> {
+        let connection = Connection::open(path)?;
+        configure(&connection)?;
+        Ok(Self { connection })
+    }
+
+    #[allow(
+        dead_code,
+        reason = "M1A proves migrations before the M2 server calls them"
+    )]
+    pub(crate) fn migrate(&mut self) -> Result<(), StoreError> {
+        self.migrate_with_hook(|_| {})
+    }
+
+    #[allow(
+        dead_code,
+        reason = "M1A proves migrations before the M2 server calls them"
+    )]
+    pub(crate) fn migrate_with_hook(
+        &mut self,
+        mut after_migration: impl FnMut(i64),
+    ) -> Result<(), StoreError> {
+        let current = self.schema_version()?;
+        if current > SCHEMA_VERSION {
+            return Err(StoreError::NewerSchema(current));
+        }
+        if current == SCHEMA_VERSION {
+            return Ok(());
+        }
+
+        let transaction = self
+            .connection
+            .transaction_with_behavior(TransactionBehavior::Exclusive)?;
+        for version in (current + 1)..=SCHEMA_VERSION {
+            transaction.execute_batch(MIGRATIONS[(version - 1) as usize])?;
+            transaction.pragma_update(None, "user_version", version)?;
+            after_migration(version);
+        }
+        transaction.commit()?;
+        Ok(())
+    }
+
+    pub(crate) fn schema_version(&self) -> Result<i64, StoreError> {
+        Ok(self
+            .connection
+            .pragma_query_value(None, "user_version", |row| row.get(0))?)
+    }
+
+    pub(crate) fn integrity_check(&self) -> Result<(), StoreError> {
+        let result: String =
+            self.connection
+                .pragma_query_value(None, "integrity_check", |row| row.get(0))?;
+        if result != "ok" {
+            return Err(StoreError::Integrity(result));
+        }
+
+        let mut statement = self.connection.prepare("PRAGMA foreign_key_check")?;
+        let mut rows = statement.query([])?;
+        if let Some(row) = rows.next()? {
+            let table: String = row.get(0)?;
+            return Err(StoreError::Integrity(format!(
+                "foreign key violation in table {table}"
+            )));
+        }
+        Ok(())
+    }
+
+    #[allow(dead_code, reason = "M1A proves backup before the M2 server calls it")]
+    pub(crate) fn backup(&self, path: &Path) -> Result<(), StoreError> {
+        let mut destination = Connection::open(path)?;
+        let backup = Backup::new(&self.connection, &mut destination)?;
+        backup.run_to_completion(128, Duration::from_millis(1), None)?;
+        Ok(())
+    }
+
+    #[allow(
+        dead_code,
+        reason = "M1A proves maintenance before an operator command calls it"
+    )]
+    pub(crate) fn checkpoint(&self) -> Result<(), StoreError> {
+        self.connection
+            .execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
+        Ok(())
+    }
+
+    #[allow(
+        dead_code,
+        reason = "M1A proves maintenance before an operator command calls it"
+    )]
+    pub(crate) fn vacuum(&self) -> Result<(), StoreError> {
+        self.connection.execute_batch("VACUUM")?;
+        Ok(())
+    }
+
+    #[allow(
+        dead_code,
+        reason = "M1A tests storage behavior through this narrow test boundary"
+    )]
+    pub(crate) fn connection(&self) -> &Connection {
+        &self.connection
+    }
+
+    #[allow(
+        dead_code,
+        reason = "M1A tests storage behavior through this narrow test boundary"
+    )]
+    pub(crate) fn connection_mut(&mut self) -> &mut Connection {
+        &mut self.connection
+    }
+}
+
+#[allow(
+    dead_code,
+    reason = "the integration test imports this module without the CLI operation"
+)]
+pub(crate) fn doctor(instance_dir: &Path) -> Result<(), StoreError> {
+    let path = instance_dir.join(DATABASE_FILE);
+    let connection = Connection::open_with_flags(
+        path,
+        OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_NO_MUTEX,
+    )?;
+    configure(&connection)?;
+    let store = Store { connection };
+    let actual = store.schema_version()?;
+    if actual != SCHEMA_VERSION {
+        return Err(StoreError::SchemaVersion {
+            expected: SCHEMA_VERSION,
+            actual,
+        });
+    }
+    store.integrity_check()
+}
+
+#[allow(
+    dead_code,
+    reason = "M1A proves migrations before the M2 server calls them"
+)]
+fn migration_backup_path(path: &Path, version: i64) -> PathBuf {
+    let mut backup = OsString::from(path.as_os_str());
+    backup.push(format!(".v{version}.backup"));
+    PathBuf::from(backup)
+}
+
+fn configure(connection: &Connection) -> Result<(), StoreError> {
+    connection.busy_timeout(BUSY_TIMEOUT)?;
+    connection.pragma_update(None, "journal_mode", "WAL")?;
+    connection.pragma_update(None, "synchronous", "FULL")?;
+    connection.pragma_update(None, "foreign_keys", true)?;
+
+    verify_text_setting(connection, "journal_mode", "wal")?;
+    verify_integer_setting(connection, "synchronous", 2, "2")?;
+    verify_integer_setting(connection, "foreign_keys", 1, "1")?;
+    Ok(())
+}
+
+fn verify_text_setting(
+    connection: &Connection,
+    name: &'static str,
+    expected: &'static str,
+) -> Result<(), StoreError> {
+    let actual: String = connection.pragma_query_value(None, name, |row| row.get(0))?;
+    if actual.eq_ignore_ascii_case(expected) {
+        return Ok(());
+    }
+    Err(StoreError::Setting {
+        name,
+        expected,
+        actual,
+    })
+}
+
+fn verify_integer_setting(
+    connection: &Connection,
+    name: &'static str,
+    expected: i64,
+    expected_text: &'static str,
+) -> Result<(), StoreError> {
+    let actual: i64 = connection.pragma_query_value(None, name, |row| row.get(0))?;
+    if actual == expected {
+        return Ok(());
+    }
+    Err(StoreError::Setting {
+        name,
+        expected: expected_text,
+        actual: actual.to_string(),
+    })
+}

@@ -9,6 +9,8 @@
 };
 use tempfile::TempDir;
 
+const V2_DATABASE: &str = include_str!("fixtures/sqlite/v2.sql");
+
 #[test]
 fn help_and_version_use_standard_output() {
     for argument in ["--help", "--version"] {
@@ -54,6 +56,88 @@
     assert_eq!(output.status.code(), Some(1));
     assert!(output.stdout.is_empty());
     assert!(String::from_utf8_lossy(&output.stderr).starts_with("tit: "));
+}
+
+#[test]
+fn doctor_checks_an_existing_current_database() {
+    let instance = TestInstance::new();
+    let database = rusqlite::Connection::open(instance.path().join("tit.sqlite3"))
+        .expect("open the instance database");
+    database
+        .execute_batch(V2_DATABASE)
+        .expect("create the current database");
+    drop(database);
+
+    let output = instance.run(&[
+        "--config",
+        instance.config().to_str().expect("a UTF-8 path"),
+        "doctor",
+    ]);
+
+    assert!(output.status.success());
+    assert!(output.stdout.is_empty());
+    assert!(output.stderr.is_empty());
+}
+
+#[test]
+fn doctor_does_not_create_or_migrate_a_database() {
+    let instance = TestInstance::new();
+    let database_path = instance.path().join("tit.sqlite3");
+    let arguments = [
+        "--config",
+        instance.config().to_str().expect("a UTF-8 path"),
+        "doctor",
+    ];
+
+    let missing = instance.run(&arguments);
+    assert_eq!(missing.status.code(), Some(1));
+    assert!(!database_path.exists());
+
+    let database = rusqlite::Connection::open(&database_path).expect("open the old database");
+    database
+        .execute_batch(include_str!("fixtures/sqlite/v1.sql"))
+        .expect("create the old database");
+    drop(database);
+
+    let old = instance.run(&arguments);
+    assert_eq!(old.status.code(), Some(1));
+    let database = rusqlite::Connection::open(&database_path).expect("reopen the old database");
+    assert_eq!(
+        database
+            .pragma_query_value::<i64, _>(None, "user_version", |row| row.get(0))
+            .expect("read the schema version"),
+        1
+    );
+}
+
+#[test]
+fn doctor_reports_a_foreign_key_violation() {
+    let instance = TestInstance::new();
+    let database = rusqlite::Connection::open(instance.path().join("tit.sqlite3"))
+        .expect("open the instance database");
+    database
+        .execute_batch(V2_DATABASE)
+        .expect("create the current database");
+    database
+        .pragma_update(None, "foreign_keys", false)
+        .expect("disable foreign keys for the damaged fixture");
+    database
+        .execute(
+            "INSERT INTO m1a_child (id, parent_id, sequence, body) VALUES (2, 999, 1, 'orphan')",
+            [],
+        )
+        .expect("create a foreign-key violation");
+    drop(database);
+
+    let output = instance.run(&[
+        "--config",
+        instance.config().to_str().expect("a UTF-8 path"),
+        "doctor",
+    ]);
+
+    assert_eq!(output.status.code(), Some(1));
+    assert!(output.stdout.is_empty());
+    assert!(String::from_utf8_lossy(&output.stderr).contains("foreign key violation"));
 }
 
 #[test]

@@ -1,0 +1,23 @@
+CREATE TABLE m1a_parent (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL UNIQUE CHECK (length(name) BETWEEN 1 AND 64),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+CREATE INDEX m1a_parent_created_at ON m1a_parent (created_at, id);
+
+CREATE TABLE m1a_child (
+    id INTEGER PRIMARY KEY,
+    parent_id INTEGER NOT NULL REFERENCES m1a_parent (id) ON DELETE RESTRICT,
+    sequence INTEGER NOT NULL CHECK (sequence >= 0),
+    body TEXT NOT NULL,
+    UNIQUE (parent_id, sequence)
+) STRICT;
+
+INSERT INTO m1a_parent (id, name, created_at)
+VALUES (1, 'fixture', 1);
+
+INSERT INTO m1a_child (id, parent_id, sequence, body)
+VALUES (1, 1, 1, 'version one');
+
+PRAGMA user_version = 1;

@@ -1,0 +1,27 @@
+CREATE TABLE m1a_parent (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL UNIQUE CHECK (length(name) BETWEEN 1 AND 64),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+CREATE INDEX m1a_parent_created_at ON m1a_parent (created_at, id);
+
+CREATE TABLE m1a_child (
+    id INTEGER PRIMARY KEY,
+    parent_id INTEGER NOT NULL REFERENCES m1a_parent (id) ON DELETE RESTRICT,
+    sequence INTEGER NOT NULL CHECK (sequence >= 0),
+    body TEXT NOT NULL,
+    state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
+    UNIQUE (parent_id, sequence)
+) STRICT;
+
+CREATE INDEX m1a_child_state_parent
+ON m1a_child (state, parent_id, sequence);
+
+INSERT INTO m1a_parent (id, name, created_at)
+VALUES (1, 'fixture', 1);
+
+INSERT INTO m1a_child (id, parent_id, sequence, body, state)
+VALUES (1, 1, 1, 'version two', 'closed');
+
+PRAGMA user_version = 2;

@@ -1,0 +1,564 @@
+#[path = "../src/store/mod.rs"]
+mod store;
+
+use std::process::{Child, Command};
+use std::sync::mpsc;
+use std::sync::{Arc, atomic::AtomicBool, atomic::Ordering};
+use std::thread;
+use std::time::{Duration, Instant};
+use std::{env, ffi::OsString, fs};
+
+use rusqlite::{Connection, ErrorCode, TransactionBehavior, params};
+use store::{Store, StoreError};
+use tempfile::TempDir;
+
+const V1_FIXTURE: &str = include_str!("fixtures/sqlite/v1.sql");
+const V2_FIXTURE: &str = include_str!("fixtures/sqlite/v2.sql");
+
+fn database(directory: &TempDir, name: &str) -> std::path::PathBuf {
+    directory.path().join(name)
+}
+
+fn migration_backup(path: &std::path::Path, version: i64) -> std::path::PathBuf {
+    let mut backup = OsString::from(path.as_os_str());
+    backup.push(format!(".v{version}.backup"));
+    backup.into()
+}
+
+fn create_fixture(path: &std::path::Path, fixture: &str) {
+    let connection = Connection::open(path).expect("open a fixture database");
+    connection
+        .execute_batch(fixture)
+        .expect("create the fixture");
+}
+
+fn spawn_crash_child(
+    mode: &str,
+    database_path: &std::path::Path,
+    ready_path: &std::path::Path,
+) -> Child {
+    Command::new(env::current_exe().expect("find the integration test executable"))
+        .args(["--exact", "crash_child", "--nocapture", "--test-threads=1"])
+        .env("TIT_M1A_CHILD_MODE", mode)
+        .env("TIT_M1A_DATABASE", database_path)
+        .env("TIT_M1A_READY", ready_path)
+        .spawn()
+        .expect("start the crash-test child")
+}
+
+fn wait_for_child(child: &mut Child, ready_path: &std::path::Path) {
+    let deadline = Instant::now() + Duration::from_secs(10);
+    loop {
+        if ready_path.exists() {
+            return;
+        }
+        if let Some(status) = child.try_wait().expect("inspect the crash-test child") {
+            panic!("crash-test child stopped before it was ready: {status}");
+        }
+        assert!(Instant::now() < deadline, "crash-test child was not ready");
+        thread::sleep(Duration::from_millis(10));
+    }
+}
+
+fn kill_child(mut child: Child, ready_path: &std::path::Path) {
+    wait_for_child(&mut child, ready_path);
+    child.kill().expect("kill the crash-test child");
+    child.wait().expect("wait for the crash-test child");
+}
+
+fn signal_ready_and_wait() {
+    let ready_path = env::var_os("TIT_M1A_READY").expect("read the ready-file path");
+    fs::write(ready_path, b"ready").expect("write the ready file");
+    loop {
+        thread::park();
+    }
+}
+
+#[test]
+fn crash_child() {
+    let Some(mode) = env::var_os("TIT_M1A_CHILD_MODE") else {
+        return;
+    };
+    let database_path = env::var_os("TIT_M1A_DATABASE").expect("read the database path");
+    let database_path = std::path::Path::new(&database_path);
+
+    match mode.to_str().expect("read the crash-test mode") {
+        "write-uncommitted" => {
+            let mut store = Store::open(database_path).expect("open the child store");
+            let transaction = store
+                .connection_mut()
+                .transaction_with_behavior(TransactionBehavior::Immediate)
+                .expect("start the child transaction");
+            transaction
+                .execute(
+                    "INSERT INTO m1a_parent (id, name, created_at) VALUES (1, 'uncommitted', 1)",
+                    [],
+                )
+                .expect("insert an uncommitted row");
+            signal_ready_and_wait();
+        }
+        "write-committed" => {
+            let store = Store::open(database_path).expect("open the child store");
+            store
+                .connection()
+                .execute(
+                    "INSERT INTO m1a_parent (id, name, created_at) VALUES (1, 'committed', 1)",
+                    [],
+                )
+                .expect("insert a committed row");
+            signal_ready_and_wait();
+        }
+        "migration-uncommitted" => {
+            let mut store = Store::open_unmigrated(database_path).expect("open the child fixture");
+            store
+                .migrate_with_hook(|version| {
+                    if version == 2 {
+                        signal_ready_and_wait();
+                    }
+                })
+                .expect("migrate the child fixture");
+        }
+        "migration-committed" => {
+            Store::open(database_path).expect("migrate the child fixture");
+            signal_ready_and_wait();
+        }
+        unexpected => panic!("unknown crash-test mode: {unexpected}"),
+    }
+}
+
+#[test]
+fn configures_connections_and_creates_the_current_schema() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let store = Store::open(&database(&directory, "store.sqlite")).expect("open the store");
+
+    assert_eq!(store.schema_version().expect("read the schema version"), 2);
+    assert_eq!(
+        store
+            .connection()
+            .pragma_query_value::<String, _>(None, "journal_mode", |row| row.get(0))
+            .expect("read the journal mode"),
+        "wal"
+    );
+    assert_eq!(
+        store
+            .connection()
+            .pragma_query_value::<i64, _>(None, "synchronous", |row| row.get(0))
+            .expect("read the synchronization mode"),
+        2
+    );
+    assert_eq!(
+        store
+            .connection()
+            .pragma_query_value::<i64, _>(None, "foreign_keys", |row| row.get(0))
+            .expect("read the foreign-key mode"),
+        1
+    );
+    store.integrity_check().expect("check database integrity");
+}
+
+#[test]
+fn enforces_constraints_and_supports_crud_and_indexed_scans() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let mut store = Store::open(&database(&directory, "store.sqlite")).expect("open the store");
+    let connection = store.connection_mut();
+
+    connection
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (?1, ?2, ?3)",
+            params![1, "parent", 10],
+        )
+        .expect("insert a parent");
+    connection
+        .execute(
+            "INSERT INTO m1a_child (id, parent_id, sequence, body) VALUES (?1, ?2, ?3, ?4)",
+            params![1, 1, 1, "body"],
+        )
+        .expect("insert a child");
+
+    let duplicate = connection
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (?1, ?2, ?3)",
+            params![2, "parent", 11],
+        )
+        .expect_err("reject a duplicate name");
+    assert_eq!(
+        duplicate.sqlite_error_code(),
+        Some(ErrorCode::ConstraintViolation)
+    );
+
+    let orphan = connection
+        .execute(
+            "INSERT INTO m1a_child (id, parent_id, sequence, body) VALUES (?1, ?2, ?3, ?4)",
+            params![2, 999, 1, "orphan"],
+        )
+        .expect_err("reject an orphan");
+    assert_eq!(
+        orphan.sqlite_error_code(),
+        Some(ErrorCode::ConstraintViolation)
+    );
+
+    let deletion = connection
+        .execute("DELETE FROM m1a_parent WHERE id = ?1", [1])
+        .expect_err("restrict deletion of a referenced parent");
+    assert_eq!(
+        deletion.sqlite_error_code(),
+        Some(ErrorCode::ConstraintViolation)
+    );
+
+    connection
+        .execute(
+            "UPDATE m1a_child SET body = ?1, state = ?2 WHERE id = ?3",
+            params!["changed", "closed", 1],
+        )
+        .expect("update a child");
+    let child: (String, String) = connection
+        .query_row(
+            "SELECT body, state FROM m1a_child WHERE id = ?1",
+            [1],
+            |row| Ok((row.get(0)?, row.get(1)?)),
+        )
+        .expect("read the child");
+    assert_eq!(child, ("changed".to_owned(), "closed".to_owned()));
+
+    let plan: String = connection
+        .query_row(
+            "EXPLAIN QUERY PLAN SELECT id FROM m1a_parent WHERE created_at >= ?1 ORDER BY created_at, id",
+            [0],
+            |row| row.get(3),
+        )
+        .expect("read the query plan");
+    assert!(plan.contains("m1a_parent_created_at"), "query plan: {plan}");
+
+    connection
+        .execute("DELETE FROM m1a_child WHERE id = ?1", [1])
+        .expect("delete the child");
+    connection
+        .execute("DELETE FROM m1a_parent WHERE id = ?1", [1])
+        .expect("delete the parent");
+    assert_eq!(
+        connection
+            .query_row("SELECT count(*) FROM m1a_parent", [], |row| row
+                .get::<_, i64>(0))
+            .expect("count parents"),
+        0
+    );
+}
+
+#[test]
+fn rolls_back_a_failed_unit_of_work() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let mut store = Store::open(&database(&directory, "store.sqlite")).expect("open the store");
+
+    let transaction = store
+        .connection_mut()
+        .transaction()
+        .expect("start a transaction");
+    transaction
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (?1, ?2, ?3)",
+            params![1, "rolled-back", 1],
+        )
+        .expect("insert a parent");
+    transaction.rollback().expect("roll back the transaction");
+
+    assert_eq!(
+        store
+            .connection()
+            .query_row("SELECT count(*) FROM m1a_parent", [], |row| row
+                .get::<_, i64>(0))
+            .expect("count parents"),
+        0
+    );
+}
+
+#[test]
+fn recovers_committed_state_and_discards_uncommitted_state_after_a_process_kill() {
+    for (mode, expected_rows) in [("write-uncommitted", 0), ("write-committed", 1)] {
+        let directory = TempDir::new().expect("create a temporary directory");
+        let path = database(&directory, "store.sqlite");
+        Store::open(&path).expect("create the store");
+        let ready_path = database(&directory, "ready");
+
+        let child = spawn_crash_child(mode, &path, &ready_path);
+        kill_child(child, &ready_path);
+
+        let store = Store::open(&path).expect("recover the store");
+        store.integrity_check().expect("check recovered integrity");
+        assert_eq!(
+            store
+                .connection()
+                .query_row("SELECT count(*) FROM m1a_parent", [], |row| row
+                    .get::<_, i64>(0))
+                .expect("count recovered rows"),
+            expected_rows,
+            "recovered row count for {mode}"
+        );
+    }
+}
+
+#[test]
+fn permits_reads_during_a_write_and_serializes_writers() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let path = database(&directory, "store.sqlite");
+    let store = Store::open(&path).expect("open the store");
+    store
+        .connection()
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (1, 'initial', 1)",
+            [],
+        )
+        .expect("insert the initial parent");
+    drop(store);
+
+    let (locked_sender, locked_receiver) = mpsc::channel();
+    let writer_path = path.clone();
+    let writer = thread::spawn(move || {
+        let mut store = Store::open(&writer_path).expect("open the first writer");
+        let transaction = store
+            .connection_mut()
+            .transaction_with_behavior(TransactionBehavior::Immediate)
+            .expect("start the first write");
+        transaction
+            .execute(
+                "INSERT INTO m1a_parent (id, name, created_at) VALUES (2, 'first-writer', 2)",
+                [],
+            )
+            .expect("write with the first writer");
+        locked_sender.send(()).expect("signal the write lock");
+        thread::sleep(Duration::from_millis(200));
+        transaction.commit().expect("commit the first writer");
+    });
+
+    locked_receiver.recv().expect("wait for the write lock");
+    let reader = Store::open(&path).expect("open a reader");
+    assert_eq!(
+        reader
+            .connection()
+            .query_row("SELECT count(*) FROM m1a_parent", [], |row| row
+                .get::<_, i64>(0))
+            .expect("read during a write"),
+        1
+    );
+    drop(reader);
+
+    let started = Instant::now();
+    let second_writer = Store::open(&path).expect("open the second writer");
+    second_writer
+        .connection()
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (3, 'second-writer', 3)",
+            [],
+        )
+        .expect("write with the second writer");
+    assert!(started.elapsed() >= Duration::from_millis(100));
+    writer.join().expect("join the first writer");
+
+    assert_eq!(
+        second_writer
+            .connection()
+            .query_row("SELECT count(*) FROM m1a_parent", [], |row| row
+                .get::<_, i64>(0))
+            .expect("count committed writes"),
+        3
+    );
+}
+
+#[test]
+fn returns_busy_after_the_configured_timeout() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let path = database(&directory, "store.sqlite");
+    let mut first = Store::open(&path).expect("open the first store");
+    let second = Store::open(&path).expect("open the second store");
+    second
+        .connection()
+        .busy_timeout(Duration::from_millis(50))
+        .expect("set a short busy timeout");
+
+    let transaction = first
+        .connection_mut()
+        .transaction_with_behavior(TransactionBehavior::Immediate)
+        .expect("start a write transaction");
+    transaction
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (1, 'locked', 1)",
+            [],
+        )
+        .expect("hold the write lock");
+
+    let started = Instant::now();
+    let error = second
+        .connection()
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (2, 'busy', 2)",
+            [],
+        )
+        .expect_err("return a busy error");
+    assert_eq!(error.sqlite_error_code(), Some(ErrorCode::DatabaseBusy));
+    assert!(started.elapsed() >= Duration::from_millis(40));
+    transaction.rollback().expect("release the write lock");
+}
+
+#[test]
+fn backs_up_a_consistent_database_while_writes_continue() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let source_path = database(&directory, "source.sqlite");
+    let backup_path = database(&directory, "backup.sqlite");
+    let source = Store::open(&source_path).expect("open the source store");
+    source
+        .connection()
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (1, 'parent', 1)",
+            [],
+        )
+        .expect("insert the parent");
+    for sequence in 0..1_000 {
+        source
+            .connection()
+            .execute(
+                "INSERT INTO m1a_child (id, parent_id, sequence, body) VALUES (?1, 1, ?2, 'initial')",
+                params![sequence + 1, sequence],
+            )
+            .expect("insert an initial child");
+    }
+
+    let running = Arc::new(AtomicBool::new(true));
+    let writer_running = Arc::clone(&running);
+    let writer_path = source_path.clone();
+    let writer = thread::spawn(move || {
+        let store = Store::open(&writer_path).expect("open the backup writer");
+        let mut sequence = 1_000;
+        while writer_running.load(Ordering::Relaxed) {
+            store
+                .connection()
+                .execute(
+                    "INSERT INTO m1a_child (id, parent_id, sequence, body) VALUES (?1, 1, ?2, 'concurrent')",
+                    params![sequence + 1, sequence],
+                )
+                .expect("insert a concurrent child");
+            sequence += 1;
+        }
+        sequence
+    });
+
+    source
+        .backup(&backup_path)
+        .expect("create an online backup");
+    running.store(false, Ordering::Relaxed);
+    let final_sequence = writer.join().expect("join the backup writer");
+    let backup = Store::open(&backup_path).expect("open the backup");
+    backup.integrity_check().expect("check backup integrity");
+    let backup_count: i64 = backup
+        .connection()
+        .query_row("SELECT count(*) FROM m1a_child", [], |row| row.get(0))
+        .expect("count backup children");
+    assert!(backup_count >= 1_000);
+    assert!(backup_count <= i64::from(final_sequence));
+}
+
+#[test]
+fn migrates_each_committed_historical_fixture() {
+    for (name, fixture, initial_version) in
+        [("v1.sqlite", V1_FIXTURE, 1), ("v2.sqlite", V2_FIXTURE, 2)]
+    {
+        let directory = TempDir::new().expect("create a temporary directory");
+        let path = database(&directory, name);
+        create_fixture(&path, fixture);
+
+        let store = Store::open(&path).expect("migrate the fixture");
+        assert_eq!(store.schema_version().expect("read the schema version"), 2);
+        store.integrity_check().expect("check migrated integrity");
+        let state: String = store
+            .connection()
+            .query_row("SELECT state FROM m1a_child WHERE id = 1", [], |row| {
+                row.get(0)
+            })
+            .expect("read the migrated state");
+        let expected = if initial_version == 1 {
+            "open"
+        } else {
+            "closed"
+        };
+        assert_eq!(state, expected);
+
+        let backup_path = migration_backup(&path, 1);
+        assert_eq!(backup_path.exists(), initial_version == 1);
+        if initial_version == 1 {
+            let backup = Store::open_unmigrated(&backup_path).expect("open the migration backup");
+            assert_eq!(backup.schema_version().expect("read the backup version"), 1);
+            backup.integrity_check().expect("check backup integrity");
+        }
+    }
+}
+
+#[test]
+fn recovers_complete_schema_versions_after_a_process_kill_during_migration() {
+    for (mode, expected_version) in [("migration-uncommitted", 1), ("migration-committed", 2)] {
+        let directory = TempDir::new().expect("create a temporary directory");
+        let path = database(&directory, "fixture.sqlite");
+        create_fixture(&path, V1_FIXTURE);
+        let ready_path = database(&directory, "ready");
+
+        let child = spawn_crash_child(mode, &path, &ready_path);
+        kill_child(child, &ready_path);
+
+        let store = Store::open_unmigrated(&path).expect("recover the fixture");
+        assert_eq!(
+            store.schema_version().expect("read the recovered version"),
+            expected_version,
+            "recovered schema version for {mode}"
+        );
+        store.integrity_check().expect("check recovered integrity");
+
+        let has_state: i64 = store
+            .connection()
+            .query_row(
+                "SELECT count(*) FROM pragma_table_info('m1a_child') WHERE name = 'state'",
+                [],
+                |row| row.get(0),
+            )
+            .expect("inspect the recovered schema");
+        assert_eq!(has_state, i64::from(expected_version == 2));
+    }
+}
+
+#[test]
+fn rejects_a_newer_schema() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let path = database(&directory, "newer.sqlite");
+    let connection = Connection::open(&path).expect("open a fixture database");
+    connection
+        .pragma_update(None, "user_version", 999)
+        .expect("set a newer version");
+    drop(connection);
+
+    assert!(matches!(
+        Store::open(&path),
+        Err(StoreError::NewerSchema(999))
+    ));
+}
+
+#[test]
+fn checkpoints_and_vacuums_without_losing_data() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let path = database(&directory, "store.sqlite");
+    let store = Store::open(&path).expect("open the store");
+    store
+        .connection()
+        .execute(
+            "INSERT INTO m1a_parent (id, name, created_at) VALUES (1, 'parent', 1)",
+            [],
+        )
+        .expect("insert a parent");
+
+    store.checkpoint().expect("checkpoint the WAL");
+    store.vacuum().expect("vacuum the database");
+    store.integrity_check().expect("check database integrity");
+    assert_eq!(
+        store
+            .connection()
+            .query_row("SELECT count(*) FROM m1a_parent", [], |row| row
+                .get::<_, i64>(0))
+            .expect("count parents"),
+        1
+    );
+}

@@ -1,0 +1,146 @@
+#[path = "../src/store/mod.rs"]
+mod store;
+
+use std::fs;
+use std::time::{Duration, Instant};
+
+use rusqlite::{Connection, params};
+use store::Store;
+use tempfile::TempDir;
+
+const V1_FIXTURE: &str = include_str!("fixtures/sqlite/v1.sql");
+const ISSUE_COUNT: i64 = 10_000;
+const EVENTS_PER_ISSUE: i64 = 100;
+const EVENT_COUNT: i64 = ISSUE_COUNT * EVENTS_PER_ISSUE;
+const MAX_DATABASE_BYTES: u64 = 1_073_741_824;
+const MAX_OPERATION_TIME: Duration = Duration::from_secs(120);
+const MAX_P99_QUERY_TIME: Duration = Duration::from_millis(250);
+
+#[test]
+#[ignore = "run through scripts/check-m1a"]
+fn measures_the_m1a_workload() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let database_path = directory.path().join("workload.sqlite");
+    let backup_path = directory.path().join("workload.backup.sqlite");
+    create_version_one_workload(&database_path);
+
+    let migration_started = Instant::now();
+    let store = Store::open(&database_path).expect("back up and migrate the workload");
+    let migration_time = migration_started.elapsed();
+    store.checkpoint().expect("checkpoint the workload");
+    let database_bytes = fs::metadata(&database_path)
+        .expect("inspect the workload database")
+        .len();
+
+    let query_times = measure_queries(&store);
+    let p50_query_time = percentile(&query_times, 50);
+    let p95_query_time = percentile(&query_times, 95);
+    let p99_query_time = percentile(&query_times, 99);
+
+    let backup_started = Instant::now();
+    store.backup(&backup_path).expect("back up the workload");
+    let backup_time = backup_started.elapsed();
+    let restored = Store::open(&backup_path).expect("restore the workload backup");
+    restored
+        .integrity_check()
+        .expect("check restored workload integrity");
+    let restored_counts: (i64, i64) = restored
+        .connection()
+        .query_row(
+            "SELECT (SELECT count(*) FROM m1a_parent), \
+                    (SELECT count(*) FROM m1a_child)",
+            [],
+            |row| Ok((row.get(0)?, row.get(1)?)),
+        )
+        .expect("count restored workload records");
+    assert_eq!(restored_counts, (ISSUE_COUNT, EVENT_COUNT));
+
+    eprintln!(
+        "M1A workload: database_bytes={database_bytes} migration_ms={} backup_ms={} query_p50_us={} query_p95_us={} query_p99_us={}",
+        migration_time.as_millis(),
+        backup_time.as_millis(),
+        p50_query_time.as_micros(),
+        p95_query_time.as_micros(),
+        p99_query_time.as_micros()
+    );
+
+    assert!(database_bytes <= MAX_DATABASE_BYTES);
+    assert!(migration_time <= MAX_OPERATION_TIME);
+    assert!(backup_time <= MAX_OPERATION_TIME);
+    assert!(p99_query_time <= MAX_P99_QUERY_TIME);
+}
+
+fn create_version_one_workload(path: &std::path::Path) {
+    let mut connection = Connection::open(path).expect("open the workload database");
+    connection
+        .execute_batch(V1_FIXTURE)
+        .expect("create the version-one schema");
+    connection
+        .execute_batch("DELETE FROM m1a_child; DELETE FROM m1a_parent;")
+        .expect("remove the small fixture records");
+    connection
+        .pragma_update(None, "foreign_keys", true)
+        .expect("enable foreign keys");
+
+    let transaction = connection.transaction().expect("start the workload");
+    {
+        let mut insert_issue = transaction
+            .prepare("INSERT INTO m1a_parent (id, name, created_at) VALUES (?1, ?2, ?3)")
+            .expect("prepare the issue insert");
+        let mut insert_event = transaction
+            .prepare(
+                "INSERT INTO m1a_child (id, parent_id, sequence, body) VALUES (?1, ?2, ?3, ?4)",
+            )
+            .expect("prepare the event insert");
+        let body = "A representative issue event records a bounded plain-text change.";
+
+        for issue_id in 1..=ISSUE_COUNT {
+            insert_issue
+                .execute(params![issue_id, format!("issue-{issue_id}"), issue_id])
+                .expect("insert an issue");
+            for sequence in 0..EVENTS_PER_ISSUE {
+                let event_id = (issue_id - 1) * EVENTS_PER_ISSUE + sequence + 1;
+                insert_event
+                    .execute(params![event_id, issue_id, sequence, body])
+                    .expect("insert an event");
+            }
+        }
+    }
+    transaction.commit().expect("commit the workload");
+}
+
+fn measure_queries(store: &Store) -> Vec<Duration> {
+    let mut times = Vec::with_capacity(1_000);
+    let mut statement = store
+        .connection()
+        .prepare(
+            "SELECT id, sequence, body FROM m1a_child \
+             WHERE state = ?1 AND parent_id = ?2 ORDER BY sequence LIMIT 25",
+        )
+        .expect("prepare the representative query");
+
+    for sample in 0_i64..1_000 {
+        let issue_id = sample % ISSUE_COUNT + 1;
+        let started = Instant::now();
+        let mut rows = statement
+            .query(params!["open", issue_id])
+            .expect("query issue events");
+        let mut row_count = 0;
+        while let Some(row) = rows.next().expect("read an issue event") {
+            let _: (i64, i64, String) = (
+                row.get(0).expect("read the event ID"),
+                row.get(1).expect("read the sequence"),
+                row.get(2).expect("read the body"),
+            );
+            row_count += 1;
+        }
+        assert_eq!(row_count, 25);
+        times.push(started.elapsed());
+    }
+    times.sort_unstable();
+    times
+}
+
+fn percentile(times: &[Duration], percentile: usize) -> Duration {
+    times[(times.len() - 1) * percentile / 100]
+}