The Patterns That Separate Toy Schemas from Production Systems
Theory gets you to 3NF. Real-world requirements push you into a different set of challenges: how do you model money without losing it, how do you delete records that cannot be deleted, how do you give 50 different user roles 50 different sets of permissions without maintaining 50 separate access control systems. These are the patterns where schema design meets business requirements, and getting them right the first time saves months of painful refactoring.
Double-Entry Bookkeeping
Luca Pacioli codified double-entry bookkeeping in 1494. Five hundred years later it is still the only correct way to model financial data. Every transaction creates two entries: a debit in one account and a credit in another. Total debits must always equal total credits. This is not a database concept. It is an accounting invariant that your schema must enforce.
The naive financial schema stores a balance column and modifies it directly: UPDATE accounts SET balance = balance - 100 WHERE id = 1. This seems simple. It fails when you need to answer 'what was this account's balance on March 15th?' or 'which transactions contributed to this current balance?' or 'did the system lose any money due to a crash?' The balance column stores current state and nothing else. History is gone.
The correct schema is a ledger: an append-only table where each transaction creates at least two rows, a debit and a credit, that sum to zero. Account balances are never stored. They are computed by summing the ledger entries for that account. Stripe, every bank, and every financial system that has survived audits uses this model. The ledger is immutable. The balance is derived. Any time the derived balance disagrees with the source ledger, something has gone wrong.
The single most important financial schema rule: never store a balance you can derive from a ledger. Stored balances diverge from ledger sums during concurrent writes and partial failures. Derived balances cannot lie.
Soft Deletes and Temporal Patterns
Most business domains need records to remain queryable after they are nominally deleted. Canceled orders still appear in financial reports. Deactivated users might reactivate. Archived content must be restorable. Soft deletes add a deleted_at column: NULL means active, a timestamp means deleted. Every query filters WHERE deleted_at IS NULL. Deleted records exist in the database but are invisible to normal queries.
The operational tax is constant: every query, every index, every ORM scope must account for soft deletes. A developer who writes SELECT * FROM users without the WHERE clause returns deleted users. The discipline required to consistently apply the filter across an entire codebase is significant. An alternative: PostgreSQL's native temporal tables automatically maintain version history. You query the current state normally; historical queries specify a point in time. The database handles version management without any application logic.
Role-Based Access Control
Hard-coded permissions (is_admin boolean, user_type enum with three values) break the moment your product needs 'editors who can publish but not delete' or 'managers who can approve expenses under $5,000'. RBAC models permissions as data rather than code: Users have Roles, Roles have Permissions. The schema is five tables: users, roles, permissions, user_roles (junction), and role_permissions (junction).
Checking access becomes a query: does any of this user's roles include this permission? Adding a new role requires an INSERT, not a code deployment. Changing what a role can do requires updating the role_permissions junction table. The application logic that checks permissions is fixed and minimal. The permission rules are flexible and data-driven.
- Double-entry: append-only ledger, derived balances, two entries per transaction summing to zero.
- Soft deletes: a deleted_at timestamp column, a WHERE filter on every query, and a clear strategy for unique constraints on deleted records.
- RBAC: five tables, two junction tables, one query pattern for permission checks. Scales from 10 users to 10 million without schema changes.
- The Wolf of Wall Street act in the LLD Lab covers all three: the Broker Registry is RBAC, the Trade Engine is double-entry, the audit trail is append-only. Production financial patterns, not academic exercises.
Implementing Double-Entry in PostgreSQL
A minimal double-entry ledger schema in PostgreSQL starts with three tables. An accounts table stores each account with its type (asset, liability, equity, revenue, expense) and a display name. A transactions table stores each transaction with a description, amount, and timestamp. A ledger_entries table stores individual debit and credit entries, each referencing a transaction and an account, with a signed amount (positive for debits, negative for credits by convention, or use a direction column). A CHECK constraint on the transactions table verifies that the sum of ledger_entries for each transaction equals zero, enforcing the double-entry invariant.
Account balances are computed as SELECT SUM(amount) FROM ledger_entries WHERE account_id = X. For performance, PostgreSQL materialized views can precompute balances and refresh periodically. For real-time balance requirements, maintain a balance column on the accounts table using a trigger that updates it on every ledger_entries INSERT. The trigger approach requires careful transaction isolation to prevent lost updates when two concurrent transactions post to the same account simultaneously.
RBAC Schema in Detail
The canonical RBAC schema: users table (id, email, created_at), roles table (id, name, description), permissions table (id, resource, action), user_roles junction (user_id FK, role_id FK, granted_at, granted_by FK), role_permissions junction (role_id FK, permission_id FK). Checking whether a user can perform an action becomes a JOIN query: SELECT 1 FROM user_roles ur JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE ur.user_id = $1 AND p.resource = $2 AND p.action = $3 LIMIT 1. Index user_roles on user_id and role_permissions on role_id for performance.
Attribute-Based Access Control (ABAC) extends RBAC with contextual conditions: a manager can approve expenses up to their approval limit, a user can only see records in their department, a feature is enabled only in production environments. ABAC schemas add attribute columns to users and resources and add condition expressions to permissions. PostgreSQL's Row-Level Security is essentially built-in ABAC: policies can reference any column in the table and any session variable to determine which rows are visible.
Soft Deletes: The Full Implementation Checklist
- Add deleted_at TIMESTAMPTZ DEFAULT NULL to every table that needs soft deletes. NULL means active.
- Create a partial unique index on natural keys WHERE deleted_at IS NULL to allow reuse of unique values by new records after soft deletion.
- Add a default scope in your ORM or base query builder that appends WHERE deleted_at IS NULL to prevent accidental inclusion of deleted records.
- Create a separate, clearly named endpoint or query path for accessing deleted records. Never mix active and deleted queries in the same code path.
- Document the retention policy: how long are soft-deleted records kept before hard deletion? GDPR's right-to-erasure may require permanent deletion of user data even if soft-delete patterns preserve it.
- Test the soft-delete filter in every data access path. One missing WHERE clause in a service that should never see deleted records is a subtle bug.
Implementing Double-Entry in PostgreSQL
A minimal double-entry ledger schema in PostgreSQL starts with three tables. An accounts table stores each account with its type (asset, liability, equity, revenue, expense) and a display name. A transactions table stores each transaction with a description, amount, and timestamp. A ledger_entries table stores individual debit and credit entries, each referencing a transaction and an account, with a signed amount (positive for debits, negative for credits by convention, or use a direction column). A CHECK constraint on the transactions table verifies that the sum of ledger_entries for each transaction equals zero, enforcing the double-entry invariant.
Account balances are computed as SELECT SUM(amount) FROM ledger_entries WHERE account_id = X. For performance, PostgreSQL materialized views can precompute balances and refresh periodically. For real-time balance requirements, maintain a balance column on the accounts table using a trigger that updates it on every ledger_entries INSERT. The trigger approach requires careful transaction isolation to prevent lost updates when two concurrent transactions post to the same account simultaneously.
RBAC Schema in Detail
The canonical RBAC schema: users table (id, email, created_at), roles table (id, name, description), permissions table (id, resource, action), user_roles junction (user_id FK, role_id FK, granted_at, granted_by FK), role_permissions junction (role_id FK, permission_id FK). Checking whether a user can perform an action becomes a JOIN query: SELECT 1 FROM user_roles ur JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE ur.user_id = $1 AND p.resource = $2 AND p.action = $3 LIMIT 1. Index user_roles on user_id and role_permissions on role_id for performance.
Attribute-Based Access Control (ABAC) extends RBAC with contextual conditions: a manager can approve expenses up to their approval limit, a user can only see records in their department, a feature is enabled only in production environments. ABAC schemas add attribute columns to users and resources and add condition expressions to permissions. PostgreSQL's Row-Level Security is essentially built-in ABAC: policies can reference any column in the table and any session variable to determine which rows are visible.
Soft Deletes: The Full Implementation Checklist
- Add deleted_at TIMESTAMPTZ DEFAULT NULL to every table that needs soft deletes. NULL means active.
- Create a partial unique index on natural keys WHERE deleted_at IS NULL to allow reuse of unique values by new records after soft deletion.
- Add a default scope in your ORM or base query builder that appends WHERE deleted_at IS NULL to prevent accidental inclusion of deleted records.
- Create a separate, clearly named endpoint or query path for accessing deleted records. Never mix active and deleted queries in the same code path.
- Document the retention policy: how long are soft-deleted records kept before hard deletion? GDPR's right-to-erasure may require permanent deletion of user data even if soft-delete patterns preserve it.
- Test the soft-delete filter in every data access path. One missing WHERE clause in a service that should never see deleted records is a subtle bug.