SQL is the contract that outlives your ORM

Every product I have shipped outlived at least one framework decision. The database outlived all of them. Next.js versions change, the ORM gets swapped, the team rotates - the rows stay, and so does every assumption baked into the schema. That is why I treat SQL as the real contract of a product, not an implementation detail behind a repository class.

Prisma covers most of my day: typed CRUD, migrations in the repo, a schema a new hire can read. But I read the SQL it generates. When a list page fires one query per row, when a report needs a window function, or when a dashboard needs a GROUP BY across three joins, I write the query by hand with $queryRaw or put it in a view. Before anyone adds Redis, I open EXPLAIN ANALYZE. Most “we need a cache” tickets are a missing index.

Constraints are the cheapest QA you will ever get. NOT NULL, UNIQUE, FOREIGN KEY, and CHECK run on every write, from every service, including the script someone runs by hand on a Friday. Zod guards the API; the database guards the truth. Money goes in integer minor units or NUMERIC - never a float. Payment webhooks arrive twice, so the idempotency key gets a unique index, not a comment in the handler.

Concurrency is where FinTech bugs live. Two requests read a balance, both decide there is enough, both write - that is a lost update, and no amount of frontend validation fixes it. I prefer one atomic statement - UPDATE ... SET balance = balance - $1 WHERE id = $2 AND balance >= $1, then check that exactly one row changed - or a transaction with SELECT ... FOR UPDATE, plus an append-only ledger table so every movement can be explained later. On systems with $1.5M+ in processed volume, “explained later” is the whole job.

Indexes follow the queries you actually run, not the columns that look important. Composite indexes with equality columns first and the range or sort column last, partial indexes for “active” rows, keyset pagination instead of OFFSET 50000. Migrations on a live table go in steps: add the column as nullable, backfill in batches, then add the constraint; build the index CONCURRENTLY. A migration that locks the orders table at noon is an outage with a pull request attached.

What I refuse: a “flexible” entity-attribute-value table instead of a schema, JSONB as a place to hide core fields nobody wants to model, soft delete everywhere without thinking about unique constraints, and a cache in front of a query nobody has EXPLAINed. If you send me a slow page or a schema you are unsure about, send the query and the row counts. I will reply with the plan, the index, and the one constraint I would add first.