In a SaaS product, the data model is the product. The tables, relationships, and constraints you define in the first few months will shape what's easy, what's hard, and what's impossible for years. Here are the decisions we think through carefully on every SaaS engagement.
Multi-Tenancy: Shared Database vs Separate Schemas vs Separate Databases
The tenancy model is the foundational architecture decision for any SaaS database. Shared database with a tenant_id column everywhere is simplest to operate and cheapest to run, but requires discipline to avoid cross-tenant data leaks and limits isolation for large tenants. Separate schemas per tenant (Postgres supports this cleanly) give strong logical isolation and make tenant-specific customisations tractable. Separate databases per tenant give the strongest isolation and enable per-tenant backups and compliance boundaries, at the cost of operational complexity. We typically default to shared database with row-level security for early-stage products, with a clear path to separate schemas when tenant isolation requirements demand it.
Soft Deletes: Use Them Carefully
Soft deletes — marking records as deleted rather than removing them — seem like an obvious safety net. They are, but they come with costs. Every query must include a WHERE deleted_at IS NULL filter, and omitting it even once leaks deleted data. Unique constraints stop working as expected. Referential integrity becomes your responsibility rather than the database's. If you add soft deletes, enforce them at the ORM layer so no query can forget the filter, and think carefully about what uniqueness guarantees mean for soft-deleted records.
Audit Logging: Build It In, Not On
Every SaaS product eventually needs to answer "who changed what, and when?" Bolting audit logging onto an existing schema is painful. Design it in: either a generic audit log table that captures old and new values for any entity change, or event-sourced tables for your highest-value entities. The audit log is also a debugging superpower — being able to replay the exact sequence of changes that produced a corrupted state is worth the upfront investment.
Index Strategy Is Not an Afterthought
Foreign keys don't automatically create indexes in PostgreSQL (unlike MySQL). Every foreign key column that will be used in a JOIN or WHERE clause needs an explicit index. Start with your most common query patterns and work outwards. Use EXPLAIN ANALYSE in development with realistic data volumes — query plans on a 100-row development table look nothing like query plans on a 10-million-row production table. Partial indexes and covering indexes are often the difference between a query that runs in 2ms and one that runs in 2 seconds.
