# Why Starting with a Production-Ready Database Schema Matters > Avoid migration headaches by using a pre-built, production-shaped database schema that anticipates real-world scaling challenges. > By Dave · 2026-07-19 > Source: https://otf-kit.dev/blog/a-database-schema-you-did-not-design-alone A first-draft schema fits on one screen. Two tables, maybe a JSON column for the things you "haven't figured out yet", and a `created_at` to look responsible. Ship that to a demo, and everything is fine. Ship it to production, and six weeks in you'll be writing a migration that touches every row. That's the gap. A kit's schema starts you on the other side of it. ## What "production-shaped" actually means Not a clever naming convention. Not a preference file. A production schema is one that has been loaded with real query patterns, real multi-tenant boundaries, and real failure modes — and shaped by them. Concrete shape, not philosophy. For a SaaS kit, that shape looks roughly like: ```sql organizations -- the tenant boundary users -- identities, not tenants organization_members -- users × orgs × roles sessions -- auth state subscriptions -- billing state per org plans -- the catalog of plans invoices -- ledger api_keys -- programmatic access audit_logs -- who did what, when ``` Nine tables. None of them are the `posts` table you sketched in hour one. Each exists because of a query you will write, or a constraint you will need, or an audit log your enterprise customer will demand by month four. `organization_members` is the giveaway. Every flat schema treats the user-org relationship as "users have an `org_id`". That works until someone belongs to two orgs, or someone is invited but hasn't accepted yet, or someone is a `viewer` instead of an `admin`. The join table is the answer to a question you didn't know to ask on day one. ![first-draft schema vs production-shaped schema](https://cdn.otf-kit.dev/blog/a-database-schema-you-did-not-design-alone/inline-1.png) ## The joins a flat table can't do The reason those nine tables exist is not aesthetic. It's because the queries you actually need don't fit on one table. "Show me every member of this org with their role" is a join, not a `WHERE`: ```sql SELECT u.email, m.role, m.joined_at FROM users u JOIN organization_members m ON m.user_id = u.id WHERE m.organization_id = $1 AND m.accepted_at IS NOT NULL ORDER BY m.joined_at DESC; ``` That query has a composite index waiting for it: ```sql CREATE INDEX members_org_accepted_idx ON organization_members (organization_id, accepted_at); ``` In a flat schema, you'd either denormalise `role` and `org_id` onto `users` (and accept that a user can only be in one org), or you'd cram the membership data into a JSON column and write a query that scans every row to filter by `role`. Both work for the demo. Neither works at 10k users. ## Indexes that match the hot path Most first-draft schemas have one index per table: the primary key. Maybe a unique on `email`. That's not enough, and the missing indexes are not visible until the slow query log tells you about them. A reviewed schema ships with composite indexes for the queries it knows you'll write: ```sql -- "List this org's active subscriptions, newest first" CREATE INDEX subs_org_status_created_idx ON subscriptions (organization_id, status, created_at DESC) WHERE status IN ('active', 'trialing'); -- "Look up an API key by its hashed prefix" CREATE UNIQUE INDEX api_keys_prefix_idx ON api_keys (key_prefix); -- "Find sessions that haven't expired" CREATE INDEX sessions_user_active_idx ON sessions (user_id, expires_at) WHERE revoked_at IS NULL; ``` Three things to notice. First, every index is justified by a query, not by a guess. Second, two of them are partial — they skip rows the query will never ask for, which keeps them small. Third, the column ordering matches the `WHERE` then `ORDER BY` of the query that needs them. That ordering is the difference between an index scan and a sort. A flat schema doesn't have these. You add them after the slow query log yells at you, which is after the demo, which is after the customer, which is too late. ## Tenant scoping baked in, not bolted on Multi-tenancy is the migration that hurts the most to retrofit. Adding a `tenant_id` column to twelve tables, backfilling it, then remembering the three queries that forgot to filter by it — that's a week of careful work, and a forever-after footgun. A production schema scopes the data from the first migration: ```sql ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY; CREATE POLICY subscriptions_tenant_isolation ON subscriptions USING (organization_id = current_setting('app.current_org')::uuid); ``` Every query hits the policy. Every query is correct by default. A future engineer can't accidentally write a query that crosses tenants — the database refuses it. This is the pattern that makes "we added a big customer with their own VPC" possible in week 50, not week 5. The flat schema doesn't have it. The retrofit is brutal. ## Audit columns and soft delete as a pattern, not an afterthought Every table in a production schema has the same four columns, with the same names, with the same semantics: ```sql created_at TIMESTAMPTZ NOT NULL DEFAULT now() updated_at TIMESTAMPTZ NOT NULL DEFAULT now() deleted_at TIMESTAMPTZ -- soft delete created_by UUID REFERENCES users(id) ``` That consistency is what lets you write generic code on top of it. A repository that returns "all rows where `deleted_at IS NULL`, ordered by `created_at DESC`, paginated by cursor" works on every table without per-table special cases. The flat schema has none of this. `created_at` shows up on three of nine tables, `deleted_at` shows up wherever someone remembered, and the "show me inactive records" admin view is a per-table investigation. ## Enums that don't drift The other landmine in a flat schema is the string column with a comment: ```sql status TEXT -- 'active', 'trialing', 'past_due', 'canceled' ``` Three months in, that comment is wrong. Someone wrote `'cancelled'` (two L's). Someone wrote `'inactive'`. Someone wrote `'ACTIVE'` from a stale test fixture. Now your `WHERE status = 'active'` query returns half the rows it should. A production schema uses a real enum: ```sql CREATE TYPE subscription_status AS ENUM ( 'active', 'trialing', 'past_due', 'canceled' ); ALTER TABLE subscriptions ALTER COLUMN status TYPE subscription_status USING status::subscription_status; ``` Adding a new value is a migration. Removing one is a migration you think very hard about. The query planner knows the cardinality. The application code compiles against a typed surface. Drift stops being possible. ## What this actually saves you The migration math, for a nine-table SaaS schema: | Retrofit | Rows to scan | Window to schedule | Queries to re-audit | | --- | --- | --- | --- | | Add `organization_id` to 9 tables | ~10M | 2h maintenance window | every query | | Add composite indexes for hot paths | ~30M | online, but slow during build | n/a | | Convert `status TEXT` to enum | ~5M | per-column rewrite | every `WHERE status =` | | Add soft-delete columns + indexes | ~10M | online | every repo | | Enable RLS + write policies | 0 rows | per-table lock contention | every query | That's a quarter, not a weekend. And every one of those migrations has a "we missed one" failure mode that shows up in the next sprint. Starting from a reviewed schema is what gets you to the first paying customer without scheduling one of these. ## The part that doesn't change The UI changes. The framework churns. The auth library gets rewritten, the ORM gets a new major, the plan for "we'll just store it in JSON for now" becomes a quarter of cleanup work. The schema is the part underneath all of that. It is what your billing queries read from in year three. It is what your SOC2 auditor asks about in year two. It is what you cannot easily change after year one. That is what the SaaS Dashboard kit ships. Nine tables, the composite indexes, the RLS policies, the enums, the audit columns — the reviewed shape, applied to your project, before you've written the first feature. The same files exist on the repo you own at the end of the install, and they stay there through every model and framework churn on top. Ship the demo in an hour. Ship the schema that survives it in a kit.