Encoding Business Rules in the Schema Layer
Some of the most valuable schema patterns are not about data structure. They are about encoding process rules directly in the database. State machines capture what transitions are valid. Idempotency keys make operations safe to retry. Audit trails make changes provable and irreversible. Together, they form the backbone of schemas that are not just correct under normal conditions but correct under failure conditions too.
State Machines in Database Schemas
An order can be PENDING, CONFIRMED, SHIPPED, DELIVERED, or CANCELED. But not every transition is valid: a DELIVERED order cannot go back to PENDING. A CANCELED order cannot become SHIPPED. Without enforcement, application bugs create impossible states. One service marks an order DELIVERED while another marks it CANCELED simultaneously, and the database accepts both because there is no rule preventing the transition.
The state machine pattern makes valid transitions explicit. A state_transitions table defines allowed (from_state, to_state) pairs. A trigger validates each status change against this table before committing. A status_history table logs every transition with timestamps and the actor who triggered it. The current state, the allowed transitions, and the historical record are three separate concerns in three separate tables.
In financial and healthcare systems, invalid state transitions are not just bugs. They are compliance violations. A KYC verification process has states mandated by regulators. An insurance claim has states audited by government agencies. CHECK constraints on valid state values and trigger validation of transitions enforce these requirements unconditionally, even when the application code has bugs.
Idempotency Keys
A user clicks Pay and the request times out. Did the payment process? They click again. Without idempotency, they just paid twice. Network retries, duplicate webhook deliveries, and user double-clicks all create duplicate requests. An idempotency key is a unique token the client generates and sends with every operation. The server stores it in a UNIQUE column. If the same key arrives again, the server returns the original result without re-executing the operation.
The schema change is minimal: add an idempotency_key VARCHAR UNIQUE column to the relevant table. The design implication is significant: your schema now guarantees that any operation with a matching key can be retried safely. Stripe popularized this pattern. Every payment API that handles network reliability correctly implements it. In interviews: mention idempotency keys for any schema involving writes with financial consequences.
Append-Only Audit Trails
Financial systems, healthcare records, and regulatory compliance require complete, tamper-proof records of every data change. The audit trail pattern is simple: never UPDATE or DELETE business records. INSERT new versions. Each record has a version number, a created_at timestamp, and stores the user who created it. The current state is always the latest version. Historical state is always queryable by timestamp.
The SEC audit trail requirement pushes further: the audit log must prove that records were not modified after the fact. Cryptographic hash chains accomplish this. Each audit entry contains a hash of the previous entry. Any modification to a historical record breaks the hash chain from that point forward, making tampering detectable. This is event sourcing applied at the database level.
State machine schemas require three tables: the entity with its current status, the valid_transitions reference table, and the status_history audit table.
Idempotency keys go on any table representing an operation with side effects: payments, transfers, notifications, external API calls.
Append-only audit tables are almost always the right choice for financial data, healthcare records, and any domain with regulatory requirements.
Hash chain integrity in audit logs is the database equivalent of blockchain's tamper evidence. For high-stakes audit requirements, it is worth the complexity.
State Machine Implementation in PostgreSQL
Implementing a state machine in PostgreSQL requires three schema components. A status column with CHECK constraint limiting values to the valid set: CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'canceled')). A valid_transitions reference table defining allowed state changes: columns (from_state, to_state), primary key on both. A BEFORE UPDATE trigger that queries valid_transitions to validate each status change: IF NEW.status != OLD.status, query valid_transitions for (OLD.status, NEW.status) and raise an exception if no row exists.
The status_history table captures each transition: (id, entity_id FK, from_status, to_status, changed_by FK to users, changed_at TIMESTAMPTZ, reason TEXT). Insert a row into status_history from the same AFTER UPDATE trigger that fires after a successful status change. This gives you complete transition provenance: who changed what, from what state, to what state, when, and why.
Idempotency Key Patterns in Financial Systems
The idempotency key schema is simple but the implementation contract around it requires careful thought. The key is usually a UUID generated by the client (never the server) before the request is sent. The server stores it as UNIQUE in the relevant table. On a duplicate request, the server checks for an existing row with that key and returns the original result. The question is: how long should idempotency keys be retained? Stripe retains them for 24 hours. Some financial systems retain them indefinitely for audit purposes.
Idempotency with partial failures requires additional thought. If the payment processor returns success but the idempotency key INSERT fails (race condition between two concurrent retries), one payment has been processed with no record in your system. The solution is a two-phase commit pattern: INSERT the idempotency key row first, then call the payment processor. If the payment processor call fails, the idempotency key row exists but the payment never happened. On retry, the server sees the existing idempotency key but no completed transaction, and retries the payment processor call.
Audit Trail Integrity Levels
Basic audit trail: a changes table with (table_name, record_id, field_name, old_value, new_value, changed_by, changed_at). Good for debugging, not sufficient for legal compliance.
Immutable audit log: append-only table, no updates or deletes possible (enforce with row-level security or revoke DELETE permission from the application role). Meets most compliance requirements.
Hash-chained audit log: each entry contains a hash of the previous entry. Tamper-evident: any modification breaks the chain from that point forward. Required for SEC-level audit compliance.
Cryptographically signed entries: each entry is signed with a private key. Tamper-proof even if the signing key remains on the same server (an attacker would need the private key to forge entries).
The right level depends on your regulatory environment. Internal debugging needs basic. Financial regulatory compliance needs immutable. Public audit requirements need hash-chained or signed.
State Machine Implementation in PostgreSQL
Implementing a state machine in PostgreSQL requires three schema components. A status column with CHECK constraint limiting values to the valid set: CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'canceled')). A valid_transitions reference table defining allowed state changes: columns (from_state, to_state), primary key on both. A BEFORE UPDATE trigger that queries valid_transitions to validate each status change: IF NEW.status != OLD.status, query valid_transitions for (OLD.status, NEW.status) and raise an exception if no row exists.
The status_history table captures each transition: (id, entity_id FK, from_status, to_status, changed_by FK to users, changed_at TIMESTAMPTZ, reason TEXT). Insert a row into status_history from the same AFTER UPDATE trigger that fires after a successful status change. This gives you complete transition provenance: who changed what, from what state, to what state, when, and why.
Idempotency Key Patterns in Financial Systems
The idempotency key schema is simple but the implementation contract around it requires careful thought. The key is usually a UUID generated by the client (never the server) before the request is sent. The server stores it as UNIQUE in the relevant table. On a duplicate request, the server checks for an existing row with that key and returns the original result. The question is: how long should idempotency keys be retained? Stripe retains them for 24 hours. Some financial systems retain them indefinitely for audit purposes.
Idempotency with partial failures requires additional thought. If the payment processor returns success but the idempotency key INSERT fails (race condition between two concurrent retries), one payment has been processed with no record in your system. The solution is a two-phase commit pattern: INSERT the idempotency key row first, then call the payment processor. If the payment processor call fails, the idempotency key row exists but the payment never happened. On retry, the server sees the existing idempotency key but no completed transaction, and retries the payment processor call.
Audit Trail Integrity Levels
Basic audit trail: a changes table with (table_name, record_id, field_name, old_value, new_value, changed_by, changed_at). Good for debugging, not sufficient for legal compliance.
Immutable audit log: append-only table, no updates or deletes possible (enforce with row-level security or revoke DELETE permission from the application role). Meets most compliance requirements.
Hash-chained audit log: each entry contains a hash of the previous entry. Tamper-evident: any modification breaks the chain from that point forward. Required for SEC-level audit compliance.
Cryptographically signed entries: each entry is signed with a private key. Tamper-proof even if the signing key remains on the same server (an attacker would need the private key to forge entries).
The right level depends on your regulatory environment. Internal debugging needs basic. Financial regulatory compliance needs immutable. Public audit requirements need hash-chained or signed.
State Machine Implementation in PostgreSQL
Implementing a state machine in PostgreSQL requires three schema components. A status column with CHECK constraint limiting values to the valid set: CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'canceled')). A valid_transitions reference table defining allowed state changes: columns (from_state, to_state), primary key on both. A BEFORE UPDATE trigger that queries valid_transitions to validate each status change: IF NEW.status != OLD.status, query valid_transitions for (OLD.status, NEW.status) and raise an exception if no row exists.
The status_history table captures each transition: (id, entity_id FK, from_status, to_status, changed_by FK to users, changed_at TIMESTAMPTZ, reason TEXT). Insert a row into status_history from the same AFTER UPDATE trigger that fires after a successful status change. This gives you complete transition provenance: who changed what, from what state, to what state, when, and why.
Idempotency Key Patterns in Financial Systems
The idempotency key schema is simple but the implementation contract around it requires careful thought. The key is usually a UUID generated by the client (never the server) before the request is sent. The server stores it as UNIQUE in the relevant table. On a duplicate request, the server checks for an existing row with that key and returns the original result. The question is: how long should idempotency keys be retained? Stripe retains them for 24 hours. Some financial systems retain them indefinitely for audit purposes.
Idempotency with partial failures requires additional thought. If the payment processor returns success but the idempotency key INSERT fails (race condition between two concurrent retries), one payment has been processed with no record in your system. The solution is a two-phase commit pattern: INSERT the idempotency key row first, then call the payment processor. If the payment processor call fails, the idempotency key row exists but the payment never happened. On retry, the server sees the existing idempotency key but no completed transaction, and retries the payment processor call.
Audit Trail Integrity Levels
- Basic audit trail: a changes table with (table_name, record_id, field_name, old_value, new_value, changed_by, changed_at). Good for debugging, not sufficient for legal compliance.
- Immutable audit log: append-only table, no updates or deletes possible (enforce with row-level security or revoke DELETE permission from the application role). Meets most compliance requirements.
- Hash-chained audit log: each entry contains a hash of the previous entry. Tamper-evident: any modification breaks the chain from that point forward. Required for SEC-level audit compliance.
- Cryptographically signed entries: each entry is signed with a private key. Tamper-proof even if the signing key remains on the same server (an attacker would need the private key to forge entries).
- The right level depends on your regulatory environment. Internal debugging needs basic. Financial regulatory compliance needs immutable. Public audit requirements need hash-chained or signed.
