SQLite WAL Connection Pool, Worker & Baseline Schema #4

Closed
opened 2026-09-22 17:58:53 +00:00 by yuris · 0 comments
Owner

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, and outbox with 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:

  1. Dedicated Blocking Database Worker: SQLite connections using rusqlite must 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.
  2. Network/Database Isolation Invariant: Network I/O must NEVER occur while an SQLite transaction is open. Holding an SQLite transaction across network calls starves the worker and risks deadlocks or indefinite locks.
  3. Strict Durability Pragmas: Every SQLite connection must enforce PRAGMA journal_mode=WAL;, PRAGMA synchronous=FULL;, PRAGMA foreign_keys=ON;, and PRAGMA busy_timeout=5000;. FULL synchronous ensures that transactions are committed to durable media before acknowledgment.
  4. Single Writer Process Lock: Container replica count alone is insufficient to protect WAL state. On startup, the service must acquire an exclusive advisory file lock on the database directory or database file (e.g. via fs2). If another process owns the lock, startup must halt immediately. SQLite WAL over NFS is strictly forbidden.
  5. Append-Only Enforcement on 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_update and life_events_no_delete) must abort any UPDATE or DELETE statements on life_events.

Exact Technical References & Specifications

Local Durability Contract

From docs/technical/timeline-consistency.md#local-durability:

"Use SQLite WAL, synchronous=FULL, foreign_keys=ON and busy_timeout=5000 on every connection as applicable. One dedicated blocking database worker owns writes. WAL is SQLite's recovery mechanism, not the product timeline. The append-only application table is not tamper-proof against an administrator or compromised process. FULL durability still depends on the filesystem/device honoring flushes.

Appended facts and associated bookkeeping transitions share one local transaction. No network operation occurs while that transaction is open. Prepared intent must commit before a remote mutation; unavailable journal storage prevents new remote writes. Read-only live agenda may remain available with explicit history degradation, while readiness signals write unavailability."

Illustrative Baseline Schema

From docs/technical/timeline-consistency.md#illustrative-schema:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=FULL;
PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;

CREATE TABLE operations (
  operation_id TEXT PRIMARY KEY,
  request_hash TEXT NOT NULL,
  schema_version INTEGER NOT NULL,
  state TEXT NOT NULL CHECK(state IN
    ('prepared','applied','failed','unknown','needs_reconciliation')),
  actor TEXT NOT NULL,
  prepared_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  intent_json TEXT NOT NULL,
  baseline_json TEXT,
  result_json TEXT,
  parent_operation_id TEXT REFERENCES operations(operation_id),
  step_id TEXT,
  UNIQUE(parent_operation_id, step_id)
);
CREATE TABLE life_events (
  sequence INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id TEXT NOT NULL UNIQUE,
  dedupe_key TEXT NOT NULL UNIQUE,
  schema_version INTEGER NOT NULL,
  kind TEXT NOT NULL,
  evidence_class TEXT NOT NULL CHECK(evidence_class IN
    ('confirmed_service','observed_external','user_report','derived')),
  recorded_at TEXT NOT NULL,
  occurred_at TEXT,
  actor TEXT,
  source TEXT NOT NULL,
  collection_id TEXT,
  uid TEXT,
  recurrence_key TEXT,
  before_json TEXT,
  after_json TEXT,
  reason TEXT,
  provenance_json TEXT NOT NULL,
  operation_id TEXT REFERENCES operations(operation_id),
  correction_target TEXT REFERENCES life_events(event_id)
);
CREATE TRIGGER life_events_no_update BEFORE UPDATE ON life_events
BEGIN SELECT RAISE(ABORT, 'append-only events'); END;
CREATE TRIGGER life_events_no_delete BEFORE DELETE ON life_events
BEGIN SELECT RAISE(ABORT, 'append-only events'); END;
CREATE TABLE proposals (
  proposal_id TEXT PRIMARY KEY,
  schema_version INTEGER NOT NULL,
  state TEXT NOT NULL CHECK(state IN
    ('pending','applied','rejected','expired','partial')),
  digest TEXT NOT NULL,
  created_at TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  steps_json TEXT NOT NULL,
  policy_json TEXT NOT NULL,
  operation_id TEXT REFERENCES operations(operation_id),
  approval_json TEXT
);
CREATE TABLE item_projection (
  item_id TEXT PRIMARY KEY,
  collection_id TEXT NOT NULL,
  uid TEXT NOT NULL,
  recurrence_key TEXT NOT NULL,
  href TEXT NOT NULL,
  etag TEXT NOT NULL,
  normalized_json TEXT NOT NULL,
  raw_resource BLOB NOT NULL,
  observed_at TEXT NOT NULL,
  last_event_id TEXT NOT NULL REFERENCES life_events(event_id),
  UNIQUE(collection_id, uid, recurrence_key)
);
CREATE TABLE sync_state (
  collection_id TEXT PRIMARY KEY,
  cursor TEXT,
  generation INTEGER NOT NULL,
  complete INTEGER NOT NULL CHECK(complete IN (0,1)),
  observed_at TEXT,
  coverage_json TEXT NOT NULL
);
CREATE TABLE outbox (
  delivery_id TEXT PRIMARY KEY,
  source_event_id TEXT NOT NULL REFERENCES life_events(event_id),
  destination TEXT NOT NULL,
  document_id TEXT NOT NULL,
  schema_version INTEGER NOT NULL,
  payload_json TEXT NOT NULL,
  state TEXT NOT NULL CHECK(state IN
    ('pending','accepted','completed','retry_wait','needs_reconciliation','failed')),
  attempts INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TEXT,
  upstream_operation_id TEXT,
  upstream_ids_json TEXT,
  last_error_code TEXT,
  UNIQUE(destination, document_id)
);
CREATE INDEX life_events_item_time
  ON life_events(collection_id, uid, recurrence_key, recorded_at);

Worker Architecture & Network Boundary

From docs/technical/architecture.md#implementation-boundaries:

"The database worker serializes local writes with a bounded request queue. Network I/O never runs inside a SQLite transaction. Request cancellation after remote dispatch must not erase recovery state; service execution continues journaling or leaves a recoverable operation. Network calls and expansion have finite time/range/body limits. Readiness requires validated configuration, successful migrations and writable journal storage; backend degradation is reported separately."

Process Lock & Deployment Constraints

From docs/technical/operations-security.md#deployment-topology:

"Run one non-root Life service replica with node-local persistent storage on an explicitly selected Swarm node. Pin the service with placement constraints and use stop-first updates so old/new replicas cannot overlap. Container replica count alone is insufficient protection: startup must acquire an exclusive local service lock and fail if another writer process owns the data directory. Do not use SQLite WAL over NFS, active-active replicas or a volume transparently mounted on multiple hosts."

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)

  1. Implement Single-Writer Process Lock (src/history/lock.rs):

    • Acquire an exclusive advisory file lock (fs2::FileExt::try_lock_exclusive) on a lockfile (life.lock) in the configured storage directory.
    • If lock acquisition fails (indicating another instance is running), abort startup with an explicit error and exit code 1.
    • Retain the open file handle for the duration of the process lifecycle.
  2. Implement Database Worker Thread (src/history/worker.rs):

    • Spawn a dedicated std::thread owning a rusqlite::Connection.
    • On connection open, execute:
      PRAGMA journal_mode=WAL;
      PRAGMA synchronous=FULL;
      PRAGMA foreign_keys=ON;
      PRAGMA busy_timeout=5000;
      
    • Set up an async channel tokio::sync::mpsc::channel<DbRequest>(queue_capacity) and a cloneable handle DatabaseHandle.
    • Request enum 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>> }
  3. Implement Baseline Schema Migration & Rollback Protection (src/history/migrations.rs):

    • Create a schema_migrations table tracking applied migration versions.
    • Enforce migration-before-serving: verify schema version upon opening the database connection. If the database file has a newer or unsupported migration version than the binary supports, refuse to start/serve and exit with fatal schema error (verifying Scenario D09).
    • Execute migration 001_baseline.sql creating:
      • operations with state check constraint.
      • life_events with evidence_class check constraint.
      • Triggers life_events_no_update and life_events_no_delete raising RAISE(ABORT, 'append-only events').
      • proposals with state check constraint.
      • item_projection with unique constraint on (collection_id, uid, recurrence_key).
      • sync_state.
      • outbox with unique constraint on (destination, document_id).
      • Index life_events_item_time.
    • Execute PRAGMA integrity_check; and PRAGMA foreign_key_check; after migration.
  4. Database Worker Health Verification:

    • Integrate DatabaseHandle with /health/ready check in src/api.rs.
    • Provide a ping method confirming channel responsiveness and storage writability.

Acceptance Criteria

  • SQLite connection initializes in WAL mode and verifies PRAGMA synchronous returns 2 (FULL) and PRAGMA foreign_keys returns 1 (ON).
  • Concurrently sending multiple write transactions via DatabaseHandle serializes them through the worker thread without database lock errors.
  • Executing an UPDATE on life_events fails with an SQLite abort error containing message 'append-only events'.
  • Executing a DELETE on life_events fails with an SQLite abort error containing message 'append-only events'.
  • Inserting a row into life_events referencing a nonexistent operation_id fails with a foreign key violation error.
  • Starting a second service instance pointing to the same data directory fails immediately due to the exclusive lock.
  • Migrations are idempotent and run cleanly against a fresh database, with PRAGMA integrity_check returning ok.
  • Schema Rollback Safety (Scenario D09): Attempting to start the service against a database with a newer or unsupported migration version fails fast before serving, requiring compatible restore rather than running against an unsupported schema.

Blocked by

  • #003: Configuration Loader, Secret File Access & Health Checks
## 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`, and `outbox` with 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: 1. **Dedicated Blocking Database Worker**: SQLite connections using `rusqlite` must 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. 2. **Network/Database Isolation Invariant**: Network I/O must NEVER occur while an SQLite transaction is open. Holding an SQLite transaction across network calls starves the worker and risks deadlocks or indefinite locks. 3. **Strict Durability Pragmas**: Every SQLite connection must enforce `PRAGMA journal_mode=WAL;`, `PRAGMA synchronous=FULL;`, `PRAGMA foreign_keys=ON;`, and `PRAGMA busy_timeout=5000;`. `FULL` synchronous ensures that transactions are committed to durable media before acknowledgment. 4. **Single Writer Process Lock**: Container replica count alone is insufficient to protect WAL state. On startup, the service must acquire an exclusive advisory file lock on the database directory or database file (e.g. via `fs2`). If another process owns the lock, startup must halt immediately. SQLite WAL over NFS is strictly forbidden. 5. **Append-Only Enforcement on `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_update` and `life_events_no_delete`) must abort any `UPDATE` or `DELETE` statements on `life_events`. ## Exact Technical References & Specifications ### Local Durability Contract From `docs/technical/timeline-consistency.md#local-durability`: > "Use SQLite WAL, `synchronous=FULL`, `foreign_keys=ON` and `busy_timeout=5000` on every connection as applicable. One dedicated blocking database worker owns writes. WAL is SQLite's recovery mechanism, not the product timeline. The append-only application table is not tamper-proof against an administrator or compromised process. FULL durability still depends on the filesystem/device honoring flushes. > > Appended facts and associated bookkeeping transitions share one local transaction. No network operation occurs while that transaction is open. Prepared intent must commit before a remote mutation; unavailable journal storage prevents new remote writes. Read-only live agenda may remain available with explicit history degradation, while readiness signals write unavailability." ### Illustrative Baseline Schema From `docs/technical/timeline-consistency.md#illustrative-schema`: ```sql PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000; CREATE TABLE operations ( operation_id TEXT PRIMARY KEY, request_hash TEXT NOT NULL, schema_version INTEGER NOT NULL, state TEXT NOT NULL CHECK(state IN ('prepared','applied','failed','unknown','needs_reconciliation')), actor TEXT NOT NULL, prepared_at TEXT NOT NULL, updated_at TEXT NOT NULL, intent_json TEXT NOT NULL, baseline_json TEXT, result_json TEXT, parent_operation_id TEXT REFERENCES operations(operation_id), step_id TEXT, UNIQUE(parent_operation_id, step_id) ); CREATE TABLE life_events ( sequence INTEGER PRIMARY KEY AUTOINCREMENT, event_id TEXT NOT NULL UNIQUE, dedupe_key TEXT NOT NULL UNIQUE, schema_version INTEGER NOT NULL, kind TEXT NOT NULL, evidence_class TEXT NOT NULL CHECK(evidence_class IN ('confirmed_service','observed_external','user_report','derived')), recorded_at TEXT NOT NULL, occurred_at TEXT, actor TEXT, source TEXT NOT NULL, collection_id TEXT, uid TEXT, recurrence_key TEXT, before_json TEXT, after_json TEXT, reason TEXT, provenance_json TEXT NOT NULL, operation_id TEXT REFERENCES operations(operation_id), correction_target TEXT REFERENCES life_events(event_id) ); CREATE TRIGGER life_events_no_update BEFORE UPDATE ON life_events BEGIN SELECT RAISE(ABORT, 'append-only events'); END; CREATE TRIGGER life_events_no_delete BEFORE DELETE ON life_events BEGIN SELECT RAISE(ABORT, 'append-only events'); END; CREATE TABLE proposals ( proposal_id TEXT PRIMARY KEY, schema_version INTEGER NOT NULL, state TEXT NOT NULL CHECK(state IN ('pending','applied','rejected','expired','partial')), digest TEXT NOT NULL, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, steps_json TEXT NOT NULL, policy_json TEXT NOT NULL, operation_id TEXT REFERENCES operations(operation_id), approval_json TEXT ); CREATE TABLE item_projection ( item_id TEXT PRIMARY KEY, collection_id TEXT NOT NULL, uid TEXT NOT NULL, recurrence_key TEXT NOT NULL, href TEXT NOT NULL, etag TEXT NOT NULL, normalized_json TEXT NOT NULL, raw_resource BLOB NOT NULL, observed_at TEXT NOT NULL, last_event_id TEXT NOT NULL REFERENCES life_events(event_id), UNIQUE(collection_id, uid, recurrence_key) ); CREATE TABLE sync_state ( collection_id TEXT PRIMARY KEY, cursor TEXT, generation INTEGER NOT NULL, complete INTEGER NOT NULL CHECK(complete IN (0,1)), observed_at TEXT, coverage_json TEXT NOT NULL ); CREATE TABLE outbox ( delivery_id TEXT PRIMARY KEY, source_event_id TEXT NOT NULL REFERENCES life_events(event_id), destination TEXT NOT NULL, document_id TEXT NOT NULL, schema_version INTEGER NOT NULL, payload_json TEXT NOT NULL, state TEXT NOT NULL CHECK(state IN ('pending','accepted','completed','retry_wait','needs_reconciliation','failed')), attempts INTEGER NOT NULL DEFAULT 0, next_attempt_at TEXT, upstream_operation_id TEXT, upstream_ids_json TEXT, last_error_code TEXT, UNIQUE(destination, document_id) ); CREATE INDEX life_events_item_time ON life_events(collection_id, uid, recurrence_key, recorded_at); ``` ### Worker Architecture & Network Boundary From `docs/technical/architecture.md#implementation-boundaries`: > "The database worker serializes local writes with a bounded request queue. Network I/O never runs inside a SQLite transaction. Request cancellation after remote dispatch must not erase recovery state; service execution continues journaling or leaves a recoverable operation. Network calls and expansion have finite time/range/body limits. Readiness requires validated configuration, successful migrations and writable journal storage; backend degradation is reported separately." ### Process Lock & Deployment Constraints From `docs/technical/operations-security.md#deployment-topology`: > "Run one non-root Life service replica with node-local persistent storage on an explicitly selected Swarm node. Pin the service with placement constraints and use stop-first updates so old/new replicas cannot overlap. Container replica count alone is insufficient protection: startup must acquire an exclusive local service lock and fail if another writer process owns the data directory. Do not use SQLite WAL over NFS, active-active replicas or a volume transparently mounted on multiple hosts." ### 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) 1. **Implement Single-Writer Process Lock (`src/history/lock.rs`)**: - Acquire an exclusive advisory file lock (`fs2::FileExt::try_lock_exclusive`) on a lockfile (`life.lock`) in the configured storage directory. - If lock acquisition fails (indicating another instance is running), abort startup with an explicit error and exit code 1. - Retain the open file handle for the duration of the process lifecycle. 2. **Implement Database Worker Thread (`src/history/worker.rs`)**: - Spawn a dedicated `std::thread` owning a `rusqlite::Connection`. - On connection open, execute: ```sql PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000; ``` - Set up an async channel `tokio::sync::mpsc::channel<DbRequest>(queue_capacity)` and a cloneable handle `DatabaseHandle`. - Request enum `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>> }` 3. **Implement Baseline Schema Migration & Rollback Protection (`src/history/migrations.rs`)**: - Create a `schema_migrations` table tracking applied migration versions. - Enforce migration-before-serving: verify schema version upon opening the database connection. If the database file has a newer or unsupported migration version than the binary supports, refuse to start/serve and exit with fatal schema error (verifying Scenario D09). - Execute migration `001_baseline.sql` creating: - `operations` with `state` check constraint. - `life_events` with `evidence_class` check constraint. - Triggers `life_events_no_update` and `life_events_no_delete` raising `RAISE(ABORT, 'append-only events')`. - `proposals` with `state` check constraint. - `item_projection` with unique constraint on `(collection_id, uid, recurrence_key)`. - `sync_state`. - `outbox` with unique constraint on `(destination, document_id)`. - Index `life_events_item_time`. - Execute `PRAGMA integrity_check;` and `PRAGMA foreign_key_check;` after migration. 4. **Database Worker Health Verification**: - Integrate `DatabaseHandle` with `/health/ready` check in `src/api.rs`. - Provide a ping method confirming channel responsiveness and storage writability. ## Acceptance Criteria - [x] SQLite connection initializes in WAL mode and verifies `PRAGMA synchronous` returns `2` (`FULL`) and `PRAGMA foreign_keys` returns `1` (`ON`). - [x] Concurrently sending multiple write transactions via `DatabaseHandle` serializes them through the worker thread without database lock errors. - [x] Executing an `UPDATE` on `life_events` fails with an SQLite abort error containing message `'append-only events'`. - [x] Executing a `DELETE` on `life_events` fails with an SQLite abort error containing message `'append-only events'`. - [x] Inserting a row into `life_events` referencing a nonexistent `operation_id` fails with a foreign key violation error. - [x] Starting a second service instance pointing to the same data directory fails immediately due to the exclusive lock. - [x] Migrations are idempotent and run cleanly against a fresh database, with `PRAGMA integrity_check` returning `ok`. - [x] Schema Rollback Safety (Scenario D09): Attempting to start the service against a database with a newer or unsupported migration version fails fast before serving, requiring compatible restore rather than running against an unsupported schema. ## Blocked by - #003: Configuration Loader, Secret File Access & Health Checks
yuris closed this issue 2026-09-22 18:58:29 +00:00
yuris changed title from Issue #004: SQLite WAL Connection Pool, Worker & Baseline Schema to SQLite WAL Connection Pool, Worker & Baseline Schema 2026-09-22 20:02:40 +00:00
Sign in to join this conversation.
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
yuris/life-service#4
No description provided.