Prisma and PostgreSQL: Schema Design Patterns That Prevent Future Pain
A schema is the one part of a codebase that genuinely resists refactoring. Renaming a function is a five-minute job with a find-and-replace and a quick test run. Restructuring a production table with millions of live rows — rows tied to real customers, real invoices, real history — is a project, sometimes a risky one with real downtime on the line. That asymmetry is exactly why we spend real time on schema design before writing application code, with Prisma and PostgreSQL as the default stack for anything relational.
Model Ownership Matters More Than Table Names
Before adding a new model, the question we actually ask is who owns this data and who's allowed to write to it — not just what fields it needs. In multi-tenant or marketplace systems, that usually resolves into a few concrete rules: an explicit `tenantId` or `organizationId` on every core model, set directly rather than inferred through a chain of joins that someone will eventually get wrong; a clear line between fields a user can edit and fields the system manages on its own; and enum-driven status fields instead of a scatter of loose booleans. `status: PENDING | ACTIVE | SUSPENDED` can only ever be one thing at a time. Three separate boolean flags for the same concept can quietly contradict each other, and eventually will, usually discovered by a confused support agent rather than a test.
Nullable Fields Are a Decision, Not a Default
Every nullable column is a decision someone downstream has to get right, forever, every time they touch that model — is this field null because it's genuinely optional, or only because it hasn't been filled in yet during onboarding? Those are different problems with different correct handling, and treating them the same is how a codebase accumulates a hundred scattered null-checks nobody fully trusts. Before adding a nullable column, we ask whether it's honestly optional for the life of the record, whether it actually belongs in a separate related table instead of bolted onto the main one, and whether a sensible default would be more truthful than null in the first place. A table with fifteen nullable columns is usually a sign that two or three genuinely different entities got merged into one model somewhere along the way, out of convenience rather than design.
Indexes Are Planned, Not Discovered
We define indexes against the query patterns we actually expect from day one, not after a dashboard starts timing out in production. That means composite indexes shaped to match the real `WHERE` and `ORDER BY` clauses of the hot queries a feature will run, unique constraints enforced at the database level — never trusted to application code alone, which will eventually have a race condition that slips one through — and partial indexes for common filtered queries, like "only active records," wherever Postgres supports them cleanly. Adding an index to a table that's already grown to millions of rows is a migration with genuine downtime risk attached. Planning the index at design time, before the data exists, avoids that risk entirely rather than managing around it later.
Migrations Are a Message to Your Future Team
With Prisma Migrate, every migration file gets treated as something a teammate — or a future version of the person who wrote it — needs to understand at a glance, without spelunking through git blame to reconstruct the reasoning. That means descriptive migration names instead of a timestamp with no context, data migrations kept separate from schema migrations wherever that's practical, and backward-compatible migrations for anything that needs a zero-downtime deploy: add the new column, backfill it, switch reads over to it, and only drop the old column in a later, separate deploy — never all four of those steps compressed into one migration that either fully succeeds or leaves the database in a half-migrated state.
Model Relationships the Way They Actually Work, Not the Fast Way
A few patterns we default to without much debate at this point: explicit join tables for many-to-many relationships even in cases where Prisma could manage the relation implicitly, because an explicit join table can carry metadata later — a `role` field on a user-organization join, for instance — that an implicit relation simply can't accommodate without a painful migration. `onDelete` behavior gets decided intentionally, relation by relation, rather than left at whatever the framework defaults to; cascading deletes are a genuinely dangerous default for anything financial or user-generated, where one accidental cascade can erase far more than intended. And soft deletes, via a `deletedAt` field, are reserved for anything a user might reasonably expect to undo or anything with audit requirements attached — hard deletes stay limited to data that's truly, permanently disposable.
Let Postgres Enforce What Your Application Code Might Forget
Application-level validation is necessary, but it's not sufficient on its own — every rule enforced only in TypeScript is a rule that a background job, a script run directly against the database, or a future engineer unfamiliar with the codebase can quietly bypass. Postgres has real tools for exactly this, and we underuse them less than most teams do: check constraints for invariants that should never be false, like a `quantity` column that should never go negative, or a `startDate` that should never fall after `endDate`. Unique constraints spanning multiple columns, for rules like "one active subscription per organization" that are easy to accidentally violate under concurrent requests if the only guard is an application-level check-then-insert.
The concrete example that comes up often in multi-tenant systems: a status field that's meant to have exactly one row in the "active" state per tenant at a time. Enforcing that with a partial unique index — unique on the tenant and status columns, filtered to only the active status — makes the invalid state literally impossible to insert, rather than merely "checked for" in application code that a retry, a race condition, or a direct database write could still slip past.
A Refactor We've Actually Done
A pattern we see often enough to have a standard fix for it: a table that started with a single boolean, `isActive`, then grew a second boolean, `isArchived`, then a third, `isPendingReview`, as new states got bolted on over time — and eventually someone discovers a row where `isActive` and `isArchived` are both true, a state the business logic never intended to allow but the schema never prevented. The fix is a single enum column, `status: ACTIVE | ARCHIVED | PENDING_REVIEW | ...`, which makes the impossible combination genuinely impossible rather than merely unexpected.
Migrating this safely in a live system follows the backward-compatible pattern already mentioned: add the new `status` enum column alongside the old booleans, backfill it from their current combination in a data migration, switch all read paths over to the new column, verify in production for a deploy cycle, and only then drop the old boolean columns in a later, separate migration. Doing all of that in one migration is how a schema fix turns into an incident instead of a quiet improvement nobody outside the team even notices.
Test the Schema Against Real Volume Before Launch, Not After
A schema that performs well with the two hundred rows in a development database can behave very differently once a table holds two million rows in production, and the first time most teams discover this is when a query that used to feel instant suddenly doesn't. We seed development and staging databases with realistic data volume and distribution before launch specifically to catch this — not just row count, but the actual skew a real dataset will have, since an index that performs fine against evenly distributed data can still struggle against a realistic long tail, like the handful of tenants who inevitably have ten times the average amount of data.
Running `EXPLAIN ANALYZE` against the actual hot queries at that realistic scale, before launch, is a cheap habit that catches missing indexes and inefficient query patterns while they're still a five-minute fix rather than a production incident during your busiest week.
Final Thoughts
Schema design is the highest-leverage, lowest-glamour work in a SaaS codebase. None of it shows up in a demo — nobody's impressed by a well-considered index — but it's the entire difference between a codebase that bends comfortably as requirements evolve and one that needs a genuine rewrite eighteen months in, once the original shortcuts have compounded into something nobody wants to touch. Design the schema as though you'll personally be the one migrating it under pressure at two in the morning. Eventually, someone will be — and there's a real chance it's you.
