SQL Durability: Transactions, Receipts, and Audit Truth
SQLite transactions support local durable authority state, while PostgreSQL provides a migration foundation for broader audit workloads; neither database makes a workflow replay-proof alone.
Why SQLite fits local authority state
SQLite transactions group database changes so they commit together or roll back together. Its transaction model is a practical fit for local identity, session, one-time-consumption, attempt, receipt, and recovery records where a small service needs durable state without a separate database server.
A one-time consumption record can prevent the same local authority record from being accepted twice when the state transition and its constraints are implemented correctly. That is a design property to test, not a claim that every caller or side effect becomes exactly once. Busy errors, process interruption, backup state, and adapter behavior still matter.
PostgreSQL gives audit migration room
PostgreSQL offers transaction isolation levels with different concurrency guarantees. Its default Read Committed isolation gives each command a current committed snapshot, while Repeatable Read and Serializable provide stronger guarantees with different costs and retry requirements. Those semantics make PostgreSQL a useful foundation when audit records, reconciliation queries, and concurrent operators outgrow a local store.
Migration should preserve contract meaning, not only copy tables. Map authority IDs, request fingerprints, receipt states, timestamps, actor context, policy revision, adapter result, and unknown-outcome reconciliation explicitly. Validate indexes, uniqueness, isolation assumptions, retry behavior, backup recovery, and append semantics before treating a PostgreSQL target as equivalent.
SQL is not replay-proof by itself
A transaction protects the database work inside its boundary. It cannot prove that a remote API call happened once, that a worker did not lose a response after committing, or that a retry will not repeat an external side effect. The system needs an idempotency key or one-time authority identity, a durable attempt record, a receipt, and reconciliation against the receiving system when the outcome is unknown.
LNSAT's public source describes SQLite durable authority, one-time consumption, attempts, receipts, integrity, backup, recovery, and a separate audit layer. It remains pre-release 0.1.0 source: no supported V1 artifact, hosted service, or finished real Docker proof follows from these contracts. Read LNSAT, API and Event Contracts, and Testing and Observability together when evaluating the evidence.