# Pathrule Pattern: SQLite in Production (1.0.0) # ::pathrule:package:sqlite-production ### [RULE] Keep write transactions short and deterministic (path: /src/db) SQLite serializes writers. A transaction that waits on HTTP, user input, a queue, or slow computation blocks unrelated writes even when WAL mode allows readers to continue. - Load external inputs and perform remote calls before opening the write transaction, then revalidate database-dependent assumptions once inside. - Use one explicit transaction for the complete local invariant and commit or roll back on every path; do not scatter auto-committed statements across a multi-row transition. - Apply a bounded busy strategy and surface exhaustion as a retryable capacity failure instead of spinning indefinitely or returning a misleading generic error. - Measure transaction duration and lock waits in the application because query timing alone does not reveal time spent waiting for the writer. See /src/db for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Initialize every connection with the same database contract (path: /src/db) SQLite settings can be connection-specific or database-persistent with nuanced behavior. Assuming one setup call configured every future connection leads to missing foreign-key enforcement or inconsistent contention handling. - Enable and verify foreign-key enforcement for every connection before executing application statements. - Centralize connection creation and apply the approved busy timeout, synchronous policy, and other required settings from one adapter. - Treat journal-mode changes as deployment operations and verify the effective mode; do not toggle modes per request or let two processes disagree about setup. - Reject database files on unsupported filesystems or sharing arrangements where required locking and durability guarantees are not reliable. See /ops/backups for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Make SQLite migrations resumable and verified (path: /migrations) SQLite migrations often rebuild tables to change constraints or column behavior. An interrupted copy or rename sequence can leave a schema that looks present but has lost indexes, triggers, foreign keys, or data. - Record each migration exactly once and fail startup on an unknown or partially applied version instead of guessing the next schema. - Use a transaction where the operation supports it, and stage table rebuilds with explicit column mapping, row-count checks, index recreation, and foreign-key validation. - Back up or checkpoint according to the deployment contract before an irreversible migration, and define how the prior application version behaves with the new schema. - Run integrity and foreign-key checks after migration and before accepting normal writes; preserve failure details for recovery rather than continuing on a suspect file. See /ops/backups for the adjacent decision or procedure that completes this constraint. --- ### [MEMORY] WAL improves reader concurrency, not writer concurrency (path: /src/db) Write-ahead logging lets readers continue while a writer appends, but commits still serialize and the WAL must eventually be checkpointed into the main database. A long reader can prevent checkpoint progress and allow the WAL to grow. - Choose WAL only where all participating processes share a supported local filesystem and use compatible SQLite implementations. - Monitor WAL size, checkpoint results, write latency, and long-running readers so growth is detected before disk pressure becomes an outage. - Schedule or trigger checkpoints through the database API with awareness of active readers; do not delete WAL or shared-memory files manually. - Scale write-heavy workloads by shortening and batching transactions first, then move to a client-server database when serialized writer capacity no longer fits. See /ops/backups for the rule or workflow that puts this decision into practice. --- ### [MEMORY] A SQLite backup is a consistent database snapshot (path: /ops/backups) The visible database file may not contain the newest committed pages while journaling is active. A filesystem copy taken without coordination can omit state or capture files from different moments. - Prefer the SQLite backup mechanism or a storage snapshot whose consistency guarantees cover the database and active journal state. - Record application version, schema version, database checksum, encryption context, and snapshot time with the backup artifact. - Restore into an isolated location, run integrity and foreign-key checks, then exercise critical reads and a controlled write before declaring the backup usable. - Protect backups as production data and ensure retention, encryption, and deletion policies cover copies outside the application's ordinary data directory. See /migrations for the rule or workflow that puts this decision into practice.