Canonical: https://hypler.com/wiki/sql-durability-audit

[Hypler](https://hypler.com/) / [Capabilities](https://hypler.com/services) / [Wiki](https://hypler.com/wiki) / [SI news](https://hypler.com/ai-news)

# 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.

## Sources

- [SQLite transaction documentation](https://www.sqlite.org/lang_transaction.html)
- [PostgreSQL transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html)
- [LNSAT public source](https://github.com/hypler-dev/LNSAT)
- [LNSAT project](https://hypler.com/lnsat)
- [API and event contracts](https://hypler.com/wiki/api-event-contracts)
- [Testing and observability](https://hypler.com/wiki/testing-observability)
- [Rust systems security](https://hypler.com/wiki/rust-systems-security)
- [TypeScript contracts and interfaces](https://hypler.com/wiki/typescript-contracts-interfaces)