SQLite WAL Connection Pool, Worker & Baseline Schema #4
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Summary
Implement the dedicated blocking SQLite database worker thread, initialize SQLite Write-Ahead Logging (WAL) with strict durability pragmas, acquire an exclusive single-writer file lock, and establish the baseline schema migrations for
operations,life_events,proposals,item_projection,sync_state, andoutboxwith append-only triggers.Background & Technical Rationale
The Life service requires transactional durability and consistency across operations, historical timeline events, and outbox delivery states. SQLite serves as the node-local journal and projection store. To maintain absolute consistency and prevent concurrency deadlocks or data corruption:
rusqlitemust not block Tokio async executor threads. All write transactions are dispatched through a bounded channel (tokio::sync::mpsc) to a dedicated OS thread that serializes database writes sequentially.PRAGMA journal_mode=WAL;,PRAGMA synchronous=FULL;,PRAGMA foreign_keys=ON;, andPRAGMA busy_timeout=5000;.FULLsynchronous ensures that transactions are committed to durable media before acknowledgment.fs2). If another process owns the lock, startup must halt immediately. SQLite WAL over NFS is strictly forbidden.life_events: Timeline events represent immutable historical facts. To guarantee that events cannot be tampered with by buggy application code, SQL triggers (life_events_no_updateandlife_events_no_delete) must abort anyUPDATEorDELETEstatements onlife_events.Exact Technical References & Specifications
Local Durability Contract
From
docs/technical/timeline-consistency.md#local-durability:Illustrative Baseline Schema
From
docs/technical/timeline-consistency.md#illustrative-schema:Worker Architecture & Network Boundary
From
docs/technical/architecture.md#implementation-boundaries:Process Lock & Deployment Constraints
From
docs/technical/operations-security.md#deployment-topology:Formal Section Citations
docs/technical/timeline-consistency.md#local-durability: Establishes the pragma settings, worker queue, and transaction boundaries.docs/technical/timeline-consistency.md#illustrative-schema: Provides the normative schema definitions, check constraints, indexes, and trigger logic.docs/technical/architecture.md#implementation-boundaries: Mandates offloading blocking SQLite writes from Tokio worker threads and separating network I/O from database transactions.docs/technical/operations-security.md#deployment-topology: Mandates exclusive local file locking and prohibits NFS deployments.docs/technical/operations-security.md#health-migrations-and-shutdown: Governs startup integrity checks and pre-serving migration execution.What to Build (Step-by-Step)
Implement Single-Writer Process Lock (
src/history/lock.rs):fs2::FileExt::try_lock_exclusive) on a lockfile (life.lock) in the configured storage directory.Implement Database Worker Thread (
src/history/worker.rs):std::threadowning arusqlite::Connection.tokio::sync::mpsc::channel<DbRequest>(queue_capacity)and a cloneable handleDatabaseHandle.DbRequest:Execute { sql, params, response_tx: oneshot::Sender<Result<usize>> }Query { sql, params, response_tx: oneshot::Sender<Result<Vec<RowData>>> }Transaction { job: Box<dyn FnOnce(&mut rusqlite::Transaction) -> Result<T> + Send>, response_tx: oneshot::Sender<Result<T>> }IntegrityCheck { response_tx: oneshot::Sender<Result<bool>> }Implement Baseline Schema Migration & Rollback Protection (
src/history/migrations.rs):schema_migrationstable tracking applied migration versions.001_baseline.sqlcreating:operationswithstatecheck constraint.life_eventswithevidence_classcheck constraint.life_events_no_updateandlife_events_no_deleteraisingRAISE(ABORT, 'append-only events').proposalswithstatecheck constraint.item_projectionwith unique constraint on(collection_id, uid, recurrence_key).sync_state.outboxwith unique constraint on(destination, document_id).life_events_item_time.PRAGMA integrity_check;andPRAGMA foreign_key_check;after migration.Database Worker Health Verification:
DatabaseHandlewith/health/readycheck insrc/api.rs.Acceptance Criteria
PRAGMA synchronousreturns2(FULL) andPRAGMA foreign_keysreturns1(ON).DatabaseHandleserializes them through the worker thread without database lock errors.UPDATEonlife_eventsfails with an SQLite abort error containing message'append-only events'.DELETEonlife_eventsfails with an SQLite abort error containing message'append-only events'.life_eventsreferencing a nonexistentoperation_idfails with a foreign key violation error.PRAGMA integrity_checkreturningok.Blocked by
Issue #004: SQLite WAL Connection Pool, Worker & Baseline Schemato SQLite WAL Connection Pool, Worker & Baseline Schema