Database Design for Audit Receipt Chains

Audit receipt chains provide cryptographic proof that safety decisions have not been altered after the fact. Designing the database schema correctly is critical for both integrity guarantees and query performance. This guide covers the schema design used by Authensor's control plane.

Core Receipt Schema

Each receipt contains the safety decision, the context that led to it, and a cryptographic link to the previous receipt.

The essential fields are: receipt ID (UUID), timestamp, agent ID, action type, policy version, decision (allow, deny, or escalate), the hash of this receipt's content, and the hash of the previous receipt. This chain of hashes makes any modification to a historical receipt detectable.

Hash Chain Design

The receipt hash is computed over the concatenation of all fields except the hash itself. Use SHA-256 for the hash function. Each new receipt includes the hash of the most recent previous receipt for the same agent, forming a per-agent chain.

Per-agent chains are preferable to a single global chain because they allow parallel writes without locking. Two agents can create receipts simultaneously without contending for the same chain head.

Indexing Strategy

Create indexes on: agent ID plus timestamp (for querying an agent's history), timestamp alone (for time-range queries), decision type (for filtering denied actions), and the receipt hash (for chain verification).

Partial indexes on decision = 'deny' speed up incident investigation queries that focus on blocked actions.

Partitioning

Partition the receipts table by time range. Monthly partitions work well for most deployments. This keeps individual partition sizes manageable and enables efficient data lifecycle management.

Drop or archive partitions older than your retention policy requires. With proper partitioning, this is an instant metadata operation rather than a slow delete.

Verification Queries

Build a verification query that walks the chain for a given agent, recomputing each hash and comparing it against the stored value. Run this periodically as an integrity check. Any mismatch indicates tampering.

Authensor's control plane includes a built-in verification endpoint that performs this check on demand. Schedule it as a cron job for continuous integrity monitoring.

Performance at Scale

At 10,000 receipts per second, the table grows by roughly 850 million rows per year. With proper partitioning and indexing, PostgreSQL handles this volume well. Use connection pooling (PgBouncer) and write batching to maintain throughput under sustained load.