michal/tit

Browse tree · Show commit · Download archive

Diff

b1bc500b09b7 → 7d8762c45fe0

@@ -72,6 +72,8 @@
   code, command, configuration, database, executable, feed, and file.
 - framework, instance, interface, key, log, module, package, repository,
   role, route, server, session, token, username, Web UI, and workflow.
+- connection, constraint, index, parameter, query, query plan, row, schema,
+  statement, and table.
 - behavior, boundary, build, compatibility, content, crate, dependency,
   environment, feature, host, identity, implementation, and invariant.
 - license, metadata, milestone, model, owner, permission, platform, portability,
@@ -83,7 +85,7 @@
   encoding, federation, filesystem, frontend, installation, link, organization,
   product, redirect, registry, requirement, secret, setting, and view.
 - API, CDE, CLI, CSS, DNS, HTML, HTTP, HTTPS, JavaScript, JSON, Markdown, RSS,
-  SSH, TOML, URL, and the names of operating systems.
+  SQL, SSH, TOML, URL, WAL, and the names of operating systems.
 - branch, clone, commit, diff, fetch, Git object, issue, merge, pack, pull
   request, push, ref, tag, and tree.
 - Cargo, Git, OpenSSH, Rust, SQLite, and the exact names of crates, commands,
@@ -100,6 +102,8 @@
 - dependency audit, external test driver, fixture repository, loopback address,
   quality gate, Rust toolchain, standard error, and standard output.
 - advertised hostname, hostname, onion hostname, and proxy command.
+- database constraint, foreign key, online backup, prepared statement, and
+  schema migration.
 
 Use the technical verbs in this list:
 
@@ -109,7 +113,7 @@
   validate.
 - build, conform, create, discuss, implement, own, pass, preserve, rewrite,
   spawn, and store.
-- blame, download, publish, search, and watch.
+- bind, blame, download, publish, query, search, and watch.
 
 Regular plural forms of the technical nouns are permitted. Use the technical
 verbs only in the forms that STE permits. A compound technical noun must use
@@ -147,6 +151,18 @@
   invariants with explanatory messages.
 - Do not write unsafe Rust or custom cryptographic primitives without an
   explicit plan amendment and review.
+
+## SQL
+
+- Keep all SQL inside the `store` module. Return domain types instead of rows or
+  `rusqlite` types.
+- Bind all values as parameters. Do not construct SQL from user input.
+- Use explicit column lists. Add a constraint or index only for a specified
+  invariant or access pattern.
+- Keep each committed schema migration unchanged. Test each supported migration
+  path with a committed database fixture.
+- Enable and verify foreign keys on every connection. Use SQLite integrity and
+  foreign-key checks in tests and `tit doctor`.
 
 ## Dependencies
 

@@ -15,6 +15,16 @@
 checksum = "b588b76d00fde79687d7646a9b5bdf3cc0f655e0bbd080335a95d7e96f3587da"
 
 [[package]]
+name = "cc"
+version = "1.3.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "c89588d05638b5b4594a3348a2d6c20277e43a7f5c5202b05cc56888475a47b8"
+dependencies = [
+ "find-msvc-tools",
+ "shlex",
+]
+
+[[package]]
 name = "cfg-if"
 version = "1.0.4"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -86,10 +96,28 @@
 ]
 
 [[package]]
+name = "fallible-iterator"
+version = "0.3.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "2acce4a10f12dc2fb14a218589d4f1f62ef011b2d0cc4b3cb1bba8e94da14649"
+
+[[package]]
+name = "fallible-streaming-iterator"
+version = "0.1.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7360491ce676a36bf9bb3c56c1aa791658183a54d2744120f27285738d90465a"
+
+[[package]]
 name = "fastrand"
 version = "2.5.0"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "da7c62ceae207dd37ea5b845da6a0696c799f85e97da1ab5b7910be3c1c80223"
+
+[[package]]
+name = "find-msvc-tools"
+version = "0.1.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "5baebc0774151f905a1a2cc41989300b1e6fbb29aff0ceffa1064fdd3088d582"
 
 [[package]]
 name = "form_urlencoded"
@@ -243,6 +271,17 @@
 checksum = "3eaf3ede3fee6db1a4c2ee091bf8a8b4dccdc6d17f656fb07896ee72867612f2"
 
 [[package]]
+name = "libsqlite3-sys"
+version = "0.38.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "f6c19a05435c21ac299d71b6a9c13db3e3f47c520517d58990a462a1397a61db"
+dependencies = [
+ "cc",
+ "pkg-config",
+ "vcpkg",
+]
+
+[[package]]
 name = "linux-raw-sys"
 version = "0.12.1"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -265,6 +304,12 @@
 version = "2.3.2"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "9b4f627cb1b25917193a259e49bdad08f671f8d9708acfd5fe0a8c1455d87220"
+
+[[package]]
+name = "pkg-config"
+version = "0.3.33"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "19f132c84eca552bf34cab8ec81f1c1dcc229b811638f9d283dceabe58c5569e"
 
 [[package]]
 name = "potential_utf"
@@ -298,6 +343,19 @@
 version = "6.0.0"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "f8dcc9c7d52a811697d2151c701e0d08956f92b0e24136cf4cf27b57a6a0d9bf"
+
+[[package]]
+name = "rusqlite"
+version = "0.40.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "11438310b19e3109b6446c33d1ed5e889428cf2e278407bc7896bc4aaea43323"
+dependencies = [
+ "bitflags",
+ "fallible-iterator",
+ "fallible-streaming-iterator",
+ "libsqlite3-sys",
+ "smallvec",
+]
 
 [[package]]
 name = "rustix"
@@ -350,6 +408,12 @@
 dependencies = [
  "serde_core",
 ]
+
+[[package]]
+name = "shlex"
+version = "2.0.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "f8fadd59c855ef2080decdef8ff161eb6661b86933c9d82e5ba29dc602a55aba"
 
 [[package]]
 name = "smallvec"
@@ -444,6 +508,7 @@
 version = "0.1.0"
 dependencies = [
  "clap",
+ "rusqlite",
  "serde",
  "tempfile",
  "thiserror",
@@ -514,6 +579,12 @@
 version = "1.0.4"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "b6c140620e7ffbb22c2dee59cafe6084a59b5ffc27a8859a5f0d494b5d52b6be"
+
+[[package]]
+name = "vcpkg"
+version = "0.2.15"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "accd4ea62f7bb7a82fe23066fb0957d48ef677f6eeb8215f372f52e48bb32426"
 
 [[package]]
 name = "windows-link"

@@ -12,6 +12,7 @@
 
 [dependencies]
 clap = { version = "4.6", default-features = false, features = ["derive", "error-context", "help", "std", "usage"] }
+rusqlite = { version = "0.40", default-features = false, features = ["backup", "bundled"] }
 serde = { version = "1.0", features = ["derive"] }
 thiserror = "2.0"
 toml = { version = "1.1", default-features = false, features = ["parse", "serde", "std"] }

@@ -183,7 +183,7 @@
 are clear.
 
 Metadata mutations append their domain event in the same database transaction.
-Git mutations use the durable intent protocol because refs and `native_db`
+Git mutations use the durable intent protocol because Git refs and SQLite
 cannot share a transaction. Feeds, audit history, notifications, and subsequent
 webhooks derive from completed events rather than each feature inventing a
 second history.
@@ -212,70 +212,69 @@
 
 ## Storage
 
-Application metadata belongs in one `native_db` database. Source code belongs
-in bare Git repositories. Do not store issues and reviews as Git refs or
-commits. Their transactions, indexes, permissions, and stable IDs belong to the
-CDE.
+Application metadata belongs in one SQLite database. `tit` accesses it through
+`rusqlite`. Source code belongs in bare Git repositories. Do not store issues
+and reviews as Git refs or commits. Their transactions, indexes, permissions,
+and stable IDs belong to the CDE.
 
 Application-level IDs must not expose reusable storage keys. Public objects
 receive stable, non-reassignable identifiers. Relations such as issue comments,
-repository collaborators, and pull-request reviews use explicit IDs and
-secondary indexes. Because the database does not enforce foreign keys or joins,
-the storage layer must validate references and do related mutations in one
-write transaction. Orphan and index-consistency checks belong in `tit doctor`.
+repository collaborators, and pull-request reviews use explicit IDs. SQLite
+foreign keys enforce record relationships.
+
+`UNIQUE`, `CHECK`, and `NOT NULL` constraints enforce applicable record rules.
+Related mutations use one write transaction. `tit` enables foreign keys on
+every database connection.
 
 Metadata search starts with bounded scans or explicit, derived term-index
 records. If that stops being adequate, add an embedded full-text index that can
-be rebuilt entirely from canonical `native_db` records. Source search remains an
+be rebuilt entirely from canonical SQLite tables. Source search remains an
 isolated bounded traversal of Git objects. Treat a subsequent index as
 derivable state.
 
 ### Rust persistence layer
 
-Use **`native_db` with `redb`** for the first implementation. Each CDE record
-is a versioned Rust type with specified primary and secondary indexes. Rust
-conversions define the model upgrades. Database transactions, models, and
-generated key types must stay behind repository-specific application
-interfaces rather than leaking into HTTP, SSH, feed, or Git protocol handlers.
+Use **`rusqlite` with bundled SQLite**. Enable only the necessary `bundled` and
+`backup` crate features. This choice gives each supported operating system the
+same SQLite version without a runtime shared-library dependency.
 
-This choice accepts a new API and ecosystem to get a pure-Rust, SQL-free
-persistence model. Pin the exact crate versions for each `tit` release. Treat
-the database file format as private. Do not support copies of a database file
-between arbitrary `tit` versions. Before the data model expands, the first
-feasibility spike must prove these properties:
+Do not add an ORM, query builder, connection pool, or migration framework at
+this time. Keep SQL, row conversion, transactions, and connection setup inside
+the `store` module. Use prepared statements and explicit column lists. Return
+domain types from the module instead of database rows or `rusqlite` types.
+
+Use WAL mode, `synchronous=FULL`, a bounded busy timeout, and foreign-key
+enforcement. Apply these settings to each connection and verify their effective
+values. The first feasibility spike must prove these properties:
 
 - crash recovery.
 - concurrent reads and writes.
-- snapshots.
-- model upgrades across historical schemas.
-- index consistency.
+- online backup and restore.
+- transactional migrations across historical schemas.
+- constraint and index consistency.
 - restoration into the next application version.
 
 Alternatives considered:
 
-- **Diesel** supplies compile-time query checks and SQLite support,
-  but its type-heavy query DSL and synchronous SQLite path add friction to a
-  small async server. Reconsider it if compile-time SQL correctness becomes
-  more valuable than concise persistence code.
-- **rusqlite** is a small and mature SQLite driver. It is not an ORM. If `tit`
-  uses it, SQL is necessary for almost all database operations.
-- **SQLx** has query checks and good async integration. Developers must write
-  SQL directly. Thus, it does not satisfy the primary database
-  requirement.
-- **redb directly** supplies a stable pure-Rust transactional key-value store.
-  In this design, `tit` must implement model serialization, secondary indexes,
-  and schema changes. `native_db` already supplies these functions.
-- **Turso's in-process Rust database** aims for SQLite compatibility without C,
-  but it is currently beta. At this time, it documents important compatibility gaps.
-  Revisit it once its production and file-format guarantees match SQLite's.
+- **`native_db` with `redb`** avoids SQL, but it adds a young abstraction and a
+  private data format. It also makes relationship checks and external inspection
+  application responsibilities.
+- **Diesel** supplies compile-time query checks, but its query DSL and generated
+  types add a second persistence model.
+- **SQLx** supplies asynchronous integration and query checks, but SQLite is
+  synchronous and the application must still own its SQL and migrations.
+- **System SQLite** decreases binary size, but behavior and enabled features can
+  differ between supported operating systems. Bundled SQLite prevents this drift.
 
-Model upgrades run automatically on startup only after taking an application
-lock and making a recoverable pre-upgrade snapshot. Destructive or long
-upgrades must instead be explicit `tit admin migrate` operations with a
-dry-run or status view. Each stable release keeps the historical model
-definitions and conversions that it needs. These items must support an upgrade
-from the oldest supported release. An unsupported gap stops the upgrade and
-gives a clear diagnostic.
+Schema migrations are numbered SQL files embedded in the executable. Apply all
+pending migrations in one `BEGIN EXCLUSIVE` transaction. Update
+`PRAGMA user_version` in that transaction. Keep historical migration files
+unchanged. An unsupported migration gap stops startup and gives a clear
+diagnostic.
+
+Before an automatic migration, use the SQLite online backup API to create a
+recoverable copy. Destructive or long migrations are explicit
+`tit admin migrate` operations with a status view and a dry-run where possible.
 
 ## Packaging and operation
 
@@ -295,7 +294,7 @@
 
 Administrative commands that must mutate a running instance, such as
 `tit invite-code`, use an owner-only Unix control socket beneath `/srv/tit`.
-They never open the live `native_db` database from a second process.
+They never open the live SQLite database from a second process.
 An offline command must first acquire the exclusive instance lock. If
 `tit serve` owns that lock, the command uses the control socket. If this is not
 possible, the command stops and gives a clear instruction.
@@ -307,11 +306,11 @@
 instance data. Command-line overrides and environment variables for secrets are
 supported, and precedence must be deterministic.
 
-`backup` briefly establishes a consistent boundary. It creates a `native_db` hot
-snapshot. It copies or bundles all repositories and necessary configuration
+`backup` briefly establishes a consistent boundary. It uses the SQLite online
+backup API. It copies or bundles all repositories and necessary configuration
 into an archive with a specified format. `restore` verifies archive format, database
 compatibility, and repository integrity before replacing live state. `doctor`
-does checks of database records and indexes, model versions, repository object
+does checks of database records and indexes, schema versions, repository object
 integrity, filesystem permissions, key configuration, and listener readiness.
 
 The application produces structured logs without external infrastructure. It
@@ -331,7 +330,7 @@
 preceding gate passes. If a feasibility gate fails, change the design before
 you continue the feature work.
 
-**Current milestone:** Milestone 1A — `native_db` durability.
+**Current milestone:** Milestone 1A — SQLite durability.
 Update this marker only after the current milestone gate passes.
 
 ### Architecture
@@ -345,7 +344,7 @@
   cli.rs              command-line contract
   config.rs           configuration loading and validation
   domain/             IDs, entities, invariants, and domain operations
-  store/              native_db models, indexes, upgrades, and repositories
+  store/              SQLite schema, queries, migrations, and repositories
   git/                object access, refs, wire protocol, and quarantine
   auth/               SSH keys, SSHSIG challenges, sessions, and recovery
   http/               routes, handlers, templates, and response policy
@@ -360,11 +359,11 @@
 ```
 
 HTTP handlers, SSH handlers, and CLI commands call application services. They
-must not open `native_db`, manipulate repository paths, or update refs directly.
+must not open SQLite, manipulate repository paths, or update refs directly.
 The `store` and `git` modules expose narrow interfaces so neither their concrete
 crates nor generated types become part of the rest of the application.
 
-`native_db` is synchronous. Thus, storage operations run as bounded blocking
+`rusqlite` is synchronous. Thus, storage operations run as bounded blocking
 jobs. A database transaction cannot stay active across an `.await`.
 Begin with `tokio::task::spawn_blocking` behind a semaphore. Replace it with a
 dedicated storage executor only if measurements show scheduling overhead or
@@ -372,8 +371,8 @@
 
 #### Cross-store mutations
 
-Git refs and `native_db` cannot participate in one atomic transaction. Each
-operation that changes Git refs and `native_db` uses a durable intent record:
+Git refs and SQLite cannot participate in one atomic transaction. Each
+operation that changes Git refs and SQLite uses a durable intent record:
 
 1. Validate authorization and write an operation intent containing repository,
    initial refs, proposed refs, actor, and event payload.
@@ -393,7 +392,7 @@
 
 ### Technical choices to prove, not assume
 
-Tentative dependencies are Tokio, Axum, `native_db`, `ssh-key`, Russh, Askama,
+Tentative dependencies are Tokio, Axum, `rusqlite`, `ssh-key`, Russh, Askama,
 `pulldown-cmark`, an HTML sanitizer, and selected `gix-*` crates. Pin the exact
 version in `Cargo.lock` when you add a dependency.
 
@@ -415,11 +414,11 @@
 
 ### Core data model
 
-All persistent records carry a model version, creation time, and stable typed
-ID. Use random, non-reassignable IDs internally and per-repository monotonically
-increasing issue and pull-request numbers for human-facing URLs. Normalize
-usernames and repository slugs once at the domain boundary. Store the canonical
-form and display form where necessary.
+All persistent records carry a creation time and stable typed ID. Serialized
+event payloads also carry a version. Use random, non-reassignable IDs internally.
+Use per-repository increasing issue and pull-request numbers for human-facing
+URLs. Normalize usernames and repository slugs once at the domain boundary.
+Store the canonical form and display form where necessary.
 
 Initial record families are:
 
@@ -451,16 +450,18 @@
 incomplete operation intents. Invite-code hashes are unique and indexed. The
 store does not keep the clear text.
 
-The store layer enforces relationships because `native_db` has no foreign keys.
-Initially, do not delete an account or repository. Suspend an account. Archive
-a repository. Do not reassign comments, events, usernames, or public numbers.
+SQLite foreign keys use restrictive delete actions unless the schema specifies
+a different action. Initially, do not delete an account or repository. Suspend
+an account. Archive a repository. Do not reassign comments, events, usernames,
+or public numbers.
 
-`tit doctor` scans each relation and rebuildable index. It reports the exact
-record IDs in each inconsistency.
+`tit doctor` runs `PRAGMA integrity_check` and `PRAGMA foreign_key_check`. It
+also checks domain invariants that SQL constraints cannot express. It reports
+the exact record IDs in each inconsistency.
 
-Model upgrades live beside all historical models necessary for the supported
-upgrade window. Each release fixture includes a database created by the prior
-stable version, and CI restores and upgrades it before running integrity checks.
+Schema migrations stay in source control. Each release fixture includes a
+database created by the prior stable version. CI restores and migrates it before
+running integrity checks.
 
 ### Milestones
 
@@ -512,23 +513,28 @@
 
 Goal: remove the risks that can make the single-binary design invalid.
 
-##### M1A — native_db durability
+##### M1A — SQLite durability
 
-- Define two small versioned fixture models with unique and non-unique indexes.
-- Prove insert, update, delete, indexed range scan, concurrent reads, serialized
-  writes, rollback, hot snapshot, restore, and compaction behavior.
-- Generate fixtures for earlier database versions with committed fixture programs. Then,
-  upgrade through a minimum of two schema versions with conversion checks.
-- Kill a child process during representative writes and upgrades. Open the
+- Add `rusqlite` with only the `bundled` and `backup` features.
+- Define two small fixture tables with primary keys, foreign keys, constraints,
+  and unique and non-unique indexes.
+- Prove insert, update, delete, indexed range scans, concurrent reads,
+  serialized writes, busy handling, rollback, WAL checkpoint, and vacuum behavior.
+- Prove online backup and restore while reads and writes continue.
+- Generate earlier database fixtures with committed fixture programs. Migrate
+  through a minimum of two schema versions with transactional SQL migrations.
+- Kill a child process during representative writes and migrations. Open the
   database again. Make sure that it has the initial or new full state.
-- Verify exclusive-open, file-locking, `fsync`, atomic rename, snapshot, and
-  recovery behavior separately on each supported filesystem and operating
-  system. Do not infer BSD behavior from Linux or macOS results.
+- Verify locking, `synchronous=FULL`, WAL recovery, and backup behavior on each
+  supported filesystem and operating system. Do not infer BSD behavior from
+  Linux or macOS results.
+- Require a local filesystem unless a platform gate proves a different
+  filesystem has correct WAL locking and shared-memory behavior.
 - Use a workload of 10,000 issues and 1,000,000 events. Measure database growth,
-  snapshot time, and request latency.
+  backup time, migration time, and request latency.
 
-Gate: restore and each supported upgrade fixture pass `doctor`. No handler
-needs direct access to a `native_db` type.
+Gate: restored and migrated fixtures pass SQLite integrity checks and `doctor`.
+No handler needs direct access to a `rusqlite` type or SQL statement.
 
 ##### M1B — SSH identity
 
@@ -738,18 +744,18 @@
   writer.
 - **M6.2 Backup and restore.** First, give an offline backup procedure. Then
   add online backup. Take the global write and maintenance gate. Make a
-  `native_db` snapshot. Pause ref mutations and repacking while you copy the
+  SQLite online backup. Pause ref mutations and repacking while you copy the
   repositories. Copy the configuration, keys, and secrets into a checksummed
   manifest.
   Create archives with owner-only permissions and state plainly that they
   contain credentials. Restore always targets an empty instance directory
   before an explicit activation step.
-- **M6.3 Doctor.** Do checks of configuration, permissions, model versions,
+- **M6.3 Doctor.** Do checks of configuration, permissions, schema versions,
   record relations, indexes, and incomplete intents. Do checks of Git refs, reachable objects,
   quarantine debris, host keys, and backup manifests. Repair is a different,
   explicit command and never the default behavior. Add typed read-only inspect
   commands. Add a deterministic JSON Lines dump. These tools let operators
-  examine `native_db` records without its private file format.
+  examine SQLite records without application-specific decoding.
 - **M6.4 Limits and abuse resistance.** Enforce request sizes, timeouts,
   concurrency, and login and SSH attempt rates. Enforce pack, diff, archive, and
   Markdown limits. Use safe defaults for slow clients.
@@ -816,7 +822,7 @@
 The first implementation cycle must complete only these tasks:
 
 1. M0.1 through M0.6.
-2. M1A. It gives the first `native_db` decision record and fixtures.
+2. M1A. It gives the first SQLite decision record and fixtures.
 3. M1B. It gives real OpenSSH and SSHSIG interoperability tests.
 4. M1C before any HTML repository browser is built.
 5. M1D before account, issue, or pull-request work begins.