How I design a database now: rules from five production schemas

I have designed the schema for five systems that people rely on: a multi-tenant construction platform with 85+ models on Postgres, the services around a self-hosted Keycloak identity platform, a staking backend on Nest.js and Postgres, a Web3 marketplace on Express and Prisma, and, from the frontend side, a GraphQL marketplace API on MySQL that pushed 10,000 orders a month. Before that, at a part-time gig, I led a database redesign that cut query response time by roughly 87%.

None of those taught me database theory. I have been going back through the theory this year, and what struck me is how much of it I had already paid for in production without knowing the name of the thing that bit me. So this is not a tutorial. It is the list of rules I actually follow now, each with the scar behind it.

Start from the queries, then draw the tables

The construction platform, KYDA, has a deep domain: a work request becomes a project, a project has sites, a site has jobs, a job breaks into classifications like Scope of Works, Work Orders, Quotes and Invoices, and each of those holds assets and costed tasks. Eighty-five models later, the thing that kept the schema sane was not clever normalization. It was that I wrote down the ten screens the product opens most and designed for those reads first.

That sounds obvious. The trap is that entity-first modelling feels productive: you list the nouns, you give each a table, you connect them with foreign keys, and it all looks right. Then the job detail page needs seven joins and the estimation view needs an aggregate over every task in a job, and both are slow the moment a real project lands.

So my order is now: queries, then tables, then indexes, in that sequence. I still normalize by default on Postgres, because a single source of truth is the cheapest consistency you will ever buy. But I decide where the read pressure lands before I decide the shape, and for the two or three views that aggregate across the whole tree I add a purpose-built endpoint backed by a query I have read the plan for, rather than letting the ORM assemble it.

The 87% at that earlier job came from exactly this: not new hardware, but rewriting the hot queries against the access pattern and adding the indexes the plan was begging for.

Money is a snapshot, not a join

The most expensive bug I caught before a customer did was in invoicing. KYDA's invoices are GST-regulated and customer-visible, so a quote or invoice is generated as a draft and then published, and a published document is immutable. The line items on a published invoice are snapshots: they copy the task's description, quantity, unit cost, margin and tax treatment at publish time, so that editing the task later cannot change what the customer was billed.

My end-to-end harness, which provisions a full realistic project and asserts the money, flagged a GST-free task being charged $240 of GST on a published invoice. The cause was a single field: the line-item snapshot was not carrying the task's gst_free flag, so the publish-time rounding code, seeing no flag, applied the standard 10%. I fixed it in every code path that creates or updates a line item, and the suite went green.

Two rules fell out of that:

• A financial document snapshots everything it needs. Not a foreign key to the live task. The whole row of facts that produced the number, so the document stays correct when the catalog moves on.
• Every field in the snapshot needs a test that turns it on. The snapshot copied nine fields correctly and missed the tenth. Fixtures that only use default values will never find the tenth.

It also settled an old argument I used to have with myself about denormalization. Copying data is fine when the copy is supposed to diverge from the source. An invoice is meant to freeze. An employee's department name embedded in forty documents is not, and every one of those copies is a liability the next time someone renames the department.

Make the invariant the database's problem

Postgres and MySQL both default to Read Committed. Almost nobody changes it, and almost every backend has at least one check-then-act: read a balance, decide, write. Two of those running at once can both read the same value and both decide. Neither wrote the same row, so nothing conflicts, and you end up with a state the code swore was impossible. The textbook name is write skew. The production name is "how did this user get two rewards".

I no longer try to fix this in application code. Three tools, in the order I reach for them:

• A unique constraint. If a thing must happen once per key, the key goes in a unique index and the second attempt fails at the database. At TreeDots we integrated Stripe, PayNow and Xero for invoice settlement, and every callback path had to be idempotent, because gateways retry. The idempotency key stored with a uniqueness guarantee is what makes a retry a no-op instead of a double charge.
• A row lock. When the decision depends on other rows, SELECT ... FOR UPDATE on the row that owns the invariant serializes the two transactions on purpose. Slower, and correct.
• Serializable, for the rare case. Postgres implements it optimistically: transactions run on snapshots and the second committer aborts if its reads went stale. That means the application has to retry. I use it for the small number of transactions where the invariant spans a range of rows and a lock would have to cover too much.

The rule underneath all three: if a sentence in the requirements starts with "there must never be", that sentence is a constraint or a lock, not an if statement.

Audit belongs in triggers, not in the service layer

KYDA keeps an automatic audit trail with pghistory and pgtrigger, which means Postgres triggers write a history row on every insert, update and delete. I chose that over an application-level audit log for one reason: every code path. Serializers, admin actions, Celery jobs, the data patch someone runs at midnight to fix a bad import. An audit log implemented in the ORM covers the paths you remembered. A trigger covers the paths you did not.

The cost is real. Triggers add write amplification and they make the history tables the largest ones in the database. For a system where an auditor may ask who changed a quote and when, that is a trade I take every time.

Indexes: read the plan, then add exactly one

I used to add indexes the way you add salt, a little everywhere. Now the process is dull and works:

• Run the slow query with EXPLAIN ANALYZE on realistic data. Not the seed data, which fits in one page and makes every plan look fine.
• Look for the sequential scan or the sort that should not be there.
• Add one index shaped like the query: equality columns first, then the range or sort column. For a status column that is almost always active, a partial index over just the active rows is a fraction of the size.
• Run it again. Keep the index only if the plan changed.

The reason for restraint is that every index is a second B-tree that has to be updated on every write. On a write-heavy table, ten indexes turn one insert into eleven. Prisma and Django will happily create indexes for you; neither will tell you which ones are never used. Postgres will, through the statistics views, and I check them before adding more.

Cache the hottest reads, and remember the cache is not the truth

The heaviest reads in KYDA are the job, estimation and timeline views, which aggregate across a project's task tree. I put an application-level Redis cache over those, keyed per tenant and per object, invalidated on write. Hot-path latency dropped roughly 40%, and because the cache also took load off the database the same work contributed to a roughly 35% cut in cloud spend after right-sizing.

Two habits keep this from becoming a source of bugs:

• The cache holds derived data only. Nothing is written to Redis that cannot be rebuilt from Postgres. Redis replicates asynchronously and a failover can lose the last writes, which is acceptable for a cache and unacceptable for a ledger.
• Invalidation is keyed to the write, not to time. A time-to-live is a backstop, not a strategy. When a task is saved, the job and estimation keys for that job are deleted in the same request.

Replicas are a design decision, not a checkbox

The moment a database gets a read replica, the application has a new question to answer: which reads are allowed to be stale? Replication is asynchronous by default in Postgres and MySQL, and the classic failure is a user saving something and then not seeing it, because their next request was load-balanced to a follower that had not caught up.

The fix is boring and has to be deliberate: reads that follow a user's own write go to the primary for a short window, or the user is pinned to one replica so their view never goes backwards. I would rather make that decision in the data access layer once than debug "the upload seemed to fail" reports one at a time.

Own your data, keep your hands off theirs

The identity platform I work on at VIA is a monorepo of about eight backend services around a self-hosted Keycloak: a login-session coordinator, an issuer and a verifier for verifiable credentials, a DID and key registry, a realm-configuration service, and the Keycloak plugin itself. Two boundaries matter there.

First, Keycloak's relational schema is off limits. We extend it through its SPI, not by writing to its tables, because the schema is Keycloak's to migrate and a direct write is a bug waiting for the next upgrade. Second, each of our services owns its own data. A service that needs another service's data asks for it over the API. It is slower to build and dramatically easier to change, because a schema migration in one service cannot break a query hidden in another.

Migrations: expand, migrate, contract

TreeDots ran data-patch pipelines alongside schema migrations, and doing that on a live marketplace taught me the only migration pattern I trust. Never change a column in place. Add the new column, backfill it in batches, switch the code to read from it, and only then drop the old one. The same rule applies to a field's type in an API or an event: changing an integer to a string breaks every reader that has not deployed yet. Add a new field and deprecate the old one.

Rolling deploys mean old and new code always overlap for a while. The schema has to be compatible with both at every step, or the deploy has a window where something is wrong.

Postgres until a query proves otherwise

I have shipped on MongoDB, MySQL and Redis, and I keep coming back to Postgres as the default. It has JSONB for the parts of a schema that genuinely are documents, it has the strongest Serializable implementation I have used, and its tooling for reading a query plan is the best of the lot. On Alice Fans, a marketplace I built solo in two months, the decision that kept it shippable was keeping the Prisma schema deliberately small and relational, and pushing the Web3 complexity to the edges.

Leaving Postgres is a decision I make for a specific access pattern with a number attached: a write rate the single primary cannot sustain, a hot key that needs consistent hashing, or a time-series table whose newest partition is taking every write. Until one of those shows up in a plan or a graph, the relational model plus a cache is the right amount of machinery.

The checklist before a new table

• Which three queries hit this table most, and what do their plans look like on realistic data?
• Which invariants can be a unique constraint or a foreign key instead of code?
• Is anything here a financial or legal document? If so, it is a snapshot, and it is immutable after publish.
• Does this need history? If so, triggers, not the service layer.
• Which reads may be stale? Say so, in the data access layer, on purpose.
• How does this table change six months from now, with old and new code running at the same time?

None of this is novel. All of it is in the books. What changed is that I now have a specific afternoon attached to each rule, and that is the only kind of knowledge that survives a deadline.