After building dozens of business applications, the schema requirements converge on the same patterns. Here are the ones we use for the most common cases, with the reasoning that led to them.
Multi-tenant data isolation
Every table that contains tenant-specific data gets an organisation_id column. Every query includes a WHERE organisation_id = $current_org clause. Row-level security policies enforce this at the database level — not just in application code. This is the pattern that prevents the most common data leak: a query that is correct in single-tenant context becoming catastrophically incorrect when shared infrastructure is introduced.
Soft deletes
We use deleted_at TIMESTAMP rather than is_deleted BOOLEAN. The timestamp tells you when the deletion happened, which is often important for auditing and debugging. All queries add WHERE deleted_at IS NULL by default. Indexes include this condition. Deleted records are periodically archived, not truly deleted, unless regulatory requirements demand it.
Audit logging
Any table representing business-critical state gets a shadow audit table. Insert, update, and delete operations write the full row — before and after — to the audit table, along with the timestamp and the actor (user or agent) that made the change. This is implemented as a trigger, not application code, so it fires for all changes regardless of how they originate.
Status columns
Status columns use a string enum rather than an integer enum. 'pending', 'active', 'completed' is debuggable in a database console without a lookup table. Add a check constraint to enforce valid values. Add an index because status columns are almost always in WHERE clauses.
The mistakes these patterns avoid
The patterns above exist because the alternatives create specific production problems: data leaks between tenants, inability to recover accidentally deleted records, inability to debug what changed and when, and query performance problems from unindexed filter columns. Each pattern is a response to a failure we have seen in production.