Database Design That Survives Version Two

Features are cheap to replace. A screen nobody likes gets rebuilt in a week and the rest of the system does not notice. That is why cutting features to hit a deadline works.

The schema is not like that. Every query, every type, every migration and every integration is written against it, so changing a fundamental decision means touching all of them at once, usually under pressure, usually with production data that has to survive the process.

This is which decisions are in that category, and how to make them well without spending a fortnight on a design document nobody reads.

Why is the schema the expensive thing?#

Because it is the only part of the system everything else depends on, and the only part that holds state you cannot regenerate.

Delete a component and rewrite it: nothing else changes. Rename a database column and you have touched every query selecting it, every type derived from it, every serialiser, every report, and any integration reading it — plus a migration that has to run against live rows without losing anything.

The asymmetry compounds with time. A schema decision made badly on day three is contained; the same decision discovered on month six has a year of data shaped by it and a codebase of assumptions built on it. The cost of changing it is roughly proportional to how long it has been wrong.

Which decisions are actually hard to reverse?#

Fewer than people fear, and they are worth naming so the rest can be made quickly.

The bottom two rows matter as much as the top. A great deal of design paralysis goes into naming and into columns that might be needed, both of which are cheap. Spending that attention on the top three instead is the entire trick.

What primary key should you use?#

A collision-resistant string generated by the application, not an auto-incrementing integer.

Sequential integers leak information — a customer with id 47 can see the business has fewer than fifty customers, and an id in a URL invites walking the range. They also make merging data from two environments painful, and they force a database round trip before you know what id a record will have.

CUID or UUID v7 both work. UUID v7 sorts by creation time, which keeps index locality good and makes "newest first" queries efficient — that property matters more than it sounds once a table is large, because random UUIDs scatter writes across the index.

model Order {
  // Sortable by creation time, no round trip to generate, no information leaked.
  id        String   @id @default(uuid(7))
  createdAt DateTime @default(now())
}

Changing this later means rewriting every foreign key in the database plus every stored reference in application code and any external system that saved an id. It is the single most expensive schema change available, which is why it belongs in the first hour.

How should money be stored?#

As an integer number of the smallest unit, with the currency alongside it. Never as a float.

Floating point cannot represent most decimal fractions exactly. 0.1 + 0.2 is famously not 0.3, and in a financial context that becomes an invoice that is a penny out, then a reconciliation that does not balance, then an afternoon nobody enjoys.

Store amountCents: 1999 and currency: "USD". Do arithmetic in integers and format for display only at the edge. Postgres numeric is also correct and slower; integers are simpler and sufficient for anything that is not doing genuine financial modelling.

The currency column matters even for a single-currency product. Adding it costs one column now; adding it after two years of rows means backfilling a value you have to infer, and inferring is exactly what you cannot do reliably about historical money.

What about the tenancy model?#

The most consequential decision on the list, and the one most often deferred because the product has one customer at the time.

If a customer is an organisation containing several users, every table holding customer data needs an organisation reference from the beginning, and every query needs to filter on it. Retrofitting that means adding a column to every table, backfilling it, auditing every query and adding row-level policies — realistically two to four weeks, as the org model test post covers in more detail.

The cheap insurance is an orgId on customer-owned tables from day one, even when every organisation currently has exactly one member. The column costs nothing, the filter costs nothing, and it converts a multi-week migration into a feature you enable.

The honest counter-argument is that this is speculative complexity, and for a product that will genuinely never have teams it is. The test is whether two people from the same customer could ever need to see the same records. If the answer is anything other than a confident no, add the column.

Should you use soft deletes?#

Decide deliberately, because both choices have costs and drifting into one is worse than either.

The case for#

Users delete things by mistake, support needs to restore them, and some data is legally required to persist. A deletedAt timestamp makes recovery trivial and preserves referential integrity for records pointing at the deleted row.

The cost nobody mentions#

Every single query must exclude deleted rows, forever. Miss one and deleted records reappear in a report, an export or a count. Unique constraints stop working as expected — a deleted user holding an email address blocks re-registration unless the constraint is made partial.

What I do#

Soft delete on things users create and might want back — documents, projects, bookings. Hard delete on join tables, sessions and anything with a genuine deletion obligation. And where the ORM supports a global filter, use it, so excluding deleted rows is the default rather than something to remember.

The worst outcome is soft-deleting everything by reflex and then writing queries that forget. That produces a system where deletion sometimes works, which is harder to reason about than either alternative.

Enums or lookup tables?#

Lookup tables for anything a human might add to; a database enum only for genuinely fixed sets.

An enum is a schema change to extend. Adding a status value means a migration, a deploy and a coordinated release. That is fine for something like light | dark, and painful for an order status that grows a new state every quarter.

A lookup table makes adding a value an insert. It also gives you somewhere to put the display label, the sort order and whether the value is still selectable — all of which otherwise end up hardcoded in the client, which is where they drift from the database.

How should timestamps work?#

Store UTC, always, in a timestamp-with-time-zone column, and convert at the edge.

The bug this prevents is subtle and common: a booking made at 9am local time, stored without a zone, read back in a different environment, and rendered an hour out during daylight saving. It only manifests twice a year, which is exactly when nobody is looking for it.

Give every table createdAt and updatedAt from the beginning. They are trivially cheap and they are the first thing you want when investigating anything — and adding them later means every existing row has a null where the answer should be.

One genuine exception: a date with no time, like a birthday or an invoice date, should be a date rather than a timestamp. Storing it as a timestamp means it shifts across zones, and a birthday that changes when the user travels is a bug that is very hard to explain.

When do you denormalise?#

Later than instinct suggests, and always with a mechanism to keep the copy correct.

Normalise first. A properly related schema is easier to change, and Postgres handles joins well enough that most performance concerns are imaginary at the scale most products operate at. Denormalising early optimises a problem you have not measured.

When you do denormalise — a cached count, a copied display name, a materialised total — the question is what keeps it accurate. A trigger, a scheduled recompute, or an application-layer invalidation. A denormalised column with no maintenance mechanism is not an optimisation, it is a bug with a schedule.

The one case worth denormalising early is genuinely immutable historical data. An invoice should store the price and the customer name as they were at the time, not join to a products table whose prices have since changed. That is not caching; it is recording what happened.

What indexes should exist from the start?#

Foreign keys, anything you filter by routinely, and the composite for your most common query pattern.

Postgres does not automatically index foreign keys, which surprises people. A missing index there makes cascading deletes slow and makes any join on that column a sequential scan.

Beyond that, resist adding indexes speculatively. Each one costs write performance and storage, and an unused index is pure overhead. Add them when a query is slow and you have looked at the plan, not because a column feels important.

The exception is a composite index on the pattern you know you will run constantly — usually (orgId, createdAt DESC) for a tenant-scoped list. Column order matters: the filter column first, the sort column second, or the index will not be used for the sort.

How do you change a schema safely once it is live?#

Expand and contract, in two or three deploys, never one.

1. Expand. Add the new column, nullable, alongside the old one. Deploy. Nothing reads it yet, so nothing can break.
2. Migrate. Backfill in batches, and write to both columns. Deploy. Both shapes work simultaneously.
3. Contract. Switch reads to the new column, confirm, then drop the old one in a later deploy.

This is slower and it is the difference between a rename that nobody notices and an outage. The single-deploy version — rename the column and update the code together — fails for every request served between the migration completing and the new code being live, and fails worse if you need to roll back.

Test it on a branch with production-shaped data rather than on staging with fixtures. This is where database branching earns its place: a migration reviewed against a real copy catches the lock that fixtures never surface.

What does a good schema review look like?#

Twenty minutes, before any code, with three questions.

What are the nouns, and which owns which?#

List the entities and the relationships. Most schema problems are visible here as a relationship that is genuinely many-to-many being modelled as one-to-many, which surfaces months later as a feature request that cannot be built.

What is immutable history versus current state?#

Orders, invoices and audit records are history and should record values as they were. Profiles and settings are current state and should reference. Conflating them is why customer name changes retroactively alter old invoices.

What happens when this is deleted?#

For every relationship, decide: cascade, restrict, or set null. Deciding by default means the database decides, and the default is usually restrict, which surfaces as a confusing foreign key error the first time someone tries to remove an account.

What about the columns you always regret not having?#

A short list that costs almost nothing on day one and is genuinely awkward to backfill later, because the information no longer exists.

Who did this#

A createdBy on anything a user creates. The first support question about a record is nearly always who made it, and reconstructing that from logs — if the logs still exist — is an afternoon that a column would have saved.

Where it came from#

A source on records that can arrive by more than one route: signup form, admin creation, import, API. This becomes the first thing you group by when a number looks wrong, and it is unrecoverable after the fact.

A stable external reference#

Where records correspond to something in another system — a Stripe customer, a calendar event — store their identifier explicitly rather than matching on email or name. Matching on a mutable field works until somebody changes their email, and then it silently matches the wrong thing.

Version or schema marker on flexible data#

Any JSON column or embedded document should carry a version. The shape will change, old rows will keep the old shape, and a marker is what lets code handle both deliberately rather than defensively.

None of these are structural decisions, which is why they belong here rather than in the expensive list. They are simply cheap now and impossible later, and that asymmetry is enough to justify them.

How do you handle data you cannot model yet?#

A JSON column, used narrowly and with a plan to graduate out of it.

Postgres jsonb is genuinely good, and it is the right answer for things whose shape is legitimately variable — per-customer settings, form responses where the form is user-defined, metadata from an integration you do not control. It can be indexed and queried, so it is not an escape hatch that costs you querying.

The failure is using it because the shape has not been decided. That produces a column where each row is a different shape, no constraint prevents nonsense, and every read is defensive. Six months later nobody knows which keys are real, and finding out means scanning the whole table.

Two rules keep it useful. Validate on write with the same Zod schema the application uses, so the column has a shape even though the database is not enforcing it. And promote fields out when they become load-bearing: the moment you filter or sort by something inside the JSON regularly, it wants to be a real column.

How much design is too much?#

Past about half a day on a first version, you are guessing rather than designing.

The decisions in this post are worth deliberating because they are expensive to reverse. Everything else is cheaper to change than to perfect in advance, and time spent modelling features that may never be built is time not spent learning whether anyone wants them.

On a 19-day MVP, three days went to scoping and data modelling combined — the highest-return time in the project, and still only three days. The goal was not a complete model of the eventual product. It was getting the identity, tenancy and money decisions right so everything after them stayed cheap.

Design the decisions you cannot reverse. Discover the rest, because you are going to be wrong about them anyway and finding out is faster than guessing.

Conclusion#

Spend the design effort on four things: the primary key type, the tenancy model, how money is represented, and whether deletion is soft or hard. Those are the decisions that touch everything and resist change once data exists.

Store money as integers with a currency. Use sortable string ids. Put an organisation reference on customer data unless you are confident there will never be teams. Give every table created and updated timestamps in UTC. Prefer lookup tables to enums for anything that might grow.

Then stop designing and start building. Change live schemas with expand-and-contract across separate deploys, review migrations against production-shaped data rather than fixtures, and accept that the parts you got wrong will mostly be cheap — because you spent the attention on the parts that would not have been.