#3236 Datastore rework Phase 3: the commits.db commit store, dormant (no behaviour change)

Background (read this first)

swamp is reworking its datastore layer (tracking: swamp-club#2865). In the target design, v3, every change is an explicit commit recorded in commits.db, a single-writer SQLite database:

• Litestream replicates it continuously to object storage;
• content lives in immutable, content-addressed chunks;
• the writer is the CLI's embedded datastore service in solo mode (Phase 4), and the elected swamp serve leader in team mode (Phase 5).

Phase 3 builds the v3 engine dormant: nothing reaches it until a user opts a new, empty datastore into v3, which is a later issue.

What's on main already (1b5a2958):

• The domain model (swamp-club#3233), in src/domain/datastore_v3/. Its design is in design/enablers/datastores.md under "v3 domain model".
  • PartitionKey and partitionOf().
  • RecordKey, BlobRef, the Intent union and the ops (put, tombstone, data-version, lease).
  • Commit, with create, toJSON, Commit.fromJSON (async; it recomputes inline digests) and MAX_COMMIT_OPS = 10,000.
  • The Partition aggregate. Partition.append(commit) enforces the rules:
    • the partition must match, and a duplicate txnId is a no-op;
    • seq has no gaps;
    • fences never go down, and once a partition has seen a fence, unfenced commits are refused;
    • expect preconditions must hold;
    • data versions stay above each name's high-water mark.
  • It refuses with CommitRejectedError (partition-mismatch, seq-gap, stale-fence, unfenced, expect-failed, version-not-increasing).
  • integration/datastore_v3_rules_test.ts keeps the model dormant: no production code outside it may import it.
• The Litestream decision (swamp-club#3234), in design/enablers/datastore-litestream.md.
  • Litestream runs as a supervised sidecar, and the outbox confirms replication with POST /sync {wait:true}.
  • commits.db keeps SQLite's default checkpointing and never forces a checkpoint. Do not copy catalog_store.ts's wal_checkpoint(TRUNCATE): it made the replica 11 times larger.
  • The replica path is per fence.
  • It left one question to this issue: whether namespaces get their own commits.db.
• The SQLite binding. _catalog.db uses node:sqlite (DatabaseSync, src/infrastructure/persistence/catalog_store.ts). Use the same binding.

Goal

Build the commit store: commits.db holds commits and enforces the partition rules in storage. It is dormant: nothing in production uses it yet.

Design

1. Port and implementation.
   • Define a core port, e.g. CommitStore in src/domain/datastore_v3/, with append, read a partition from a seq, the current seq and highest fence of a partition, and a change cursor.
   • Implement it over SQLite in infrastructure, e.g. src/infrastructure/datastore_v3/sqlite_commit_store.ts.
   • This is core's own store, not an extension contract. commits/1, the contract for backends that are themselves the commit store such as MongoDB, is a Phase 8 item and out of scope.
2. Schema.
   • Commits are keyed by (namespace, partition, seq), unique.
   • Keep the txnId unique per partition, so a retry is detected in SQL as well as by the aggregate.
   • Store each commit's serialized JSON (Commit.toJSON), plus the columns needed for lookups: txnId, fence and at.
   • Keep a per-partition head row (seq, highest fence) so append doesn't scan.
   • Version the schema (PRAGMA user_version) and document migrations.
3. One transaction per commit.
   • An append runs in one SQLite transaction. It must start the write transaction up front (BEGIN IMMEDIATE). A deferred BEGIN in WAL mode can fail on a stale snapshot even with busy_timeout, which swamp has hit before with _catalog.db.
   • Inside the transaction, load the partition's state, apply the commit through Partition.append so the domain rules decide, then write the row and the head.
   • A refused commit writes nothing. A duplicate txnId returns the existing seq and writes nothing.
   • Decide how the store rebuilds a Partition cheaply: replay from seq 1, or keep a snapshot of the aggregate's state in a table. Measure replay on a partition with 10,000 commits, and record the choice.
4. Change cursor. Add a store-wide, monotonically increasing cursor, e.g. an autoincrement rowid or a global sequence, so the read model (a later issue) and the Phase 5 commit feed can ask "what changed since X". Rows must never be reordered or reused.
5. SQLite settings:
   • WAL mode, synchronous=FULL (a commit is durable when append returns), busy_timeout, and foreign keys if used;
   • no forced checkpoint, and auto-checkpoint left at its default, per the Litestream record;
   • one writer connection per process. Document that a second writing process is a bug the Phase 4 writer lock prevents.
6. Namespaces. Decide whether each namespace gets its own commits.db or one database holds every namespace keyed by a namespace column. Weigh the Litestream replica layout (_litestream/<fence>/ against _litestream/<namespace>/<fence>/), restore cost, and how serve, which handles several namespaces, would hold them. Record the decision and its reasons in design/enablers/datastores.md.
7. Large changes. MAX_COMMIT_OPS is 10,000. Document that GC and prune must split large tombstone sets across commits. They are later issues, so no code is needed here.

Tests

• Integration tests (integration/) on a real temp directory:
  • append and read back, and seq continuity;
  • every refusal reason surfaces as the domain error and leaves the database unchanged;
  • a duplicate txnId writes nothing;
  • concurrent appends to one partition from one process serialize correctly;
  • a reopened store keeps its state;
  • the change cursor is complete and ordered from any position.
• A property test (fast-check): random command sequences applied through the store give the same partition state as Partition.append alone, using the existing reference model in partition_property_test.ts as the oracle.
• Crash safety: a writer killed mid-append, simulated with a transaction that throws before commit, leaves no partial row.
• Checkpoints: prove no code path issues wal_checkpoint. Add a fitness or unit check.
• Dormancy: the store stays inside the v3 rules. If it lives under src/infrastructure/datastore_v3/, extend datastore_v3_rules_test.ts so no production code outside the v3 directories imports it, with an empty pinned list.

Out of scope

• The read model (materialised files and the v3 catalog) and the commit-log unit-of-work adapter (CommitLogUnitOfWork, the binding registry, refusing unscoped writes on v3). Each is a later issue.
• Litestream, the outbox, chunks, serve, leases and the opt-in.
• commits/1 for extensions.

Done when

• The store and its tests are on main, dormant, with no production importers.
• The namespace and partition-rebuild decisions are recorded in datastores.md.
• All existing suites pass unchanged.

Blocked by: nothing (#3233 has merged). It runs in parallel with the chunk store and storage/1 issue.