From AI Prototype to Production Schema: Data Modeling and Migration Strategy

Prototype schemas optimize for learning. Production schemas must protect ownership, relations, invariants and recovery. The migration between those worlds should be treated as an operational protocol rather than a one-off SQL script.

1. Model the domain, not the current tables

I identify entities, owners, required relationships, uniqueness rules and deletion semantics before redesigning tables. Existing schema is evidence, not the specification.

1type DomainInvariant = {2  entity: string;3  owner: "user" | "workspace" | "system";4  uniqueBy: string[];5  requiredRelations: string[];6};

2. Move assumptions into database guarantees

Critical truths belong in NOT NULL, UNIQUE, FOREIGN KEY, CHECK, RLS or server-side policy. A UI that happens not to create invalid data is not a data-integrity strategy.

3. Use expand → migrate → contract

Add the new structure first, make the application compatible with both versions, backfill and validate, switch reads and writes, then remove the old structure after the compatibility window closes.

1EXPAND   -> add new schema2MIGRATE  -> backfill and validate3CUTOVER  -> move reads/writes4CONTRACT -> remove legacy fields

4. Treat backfills as operations

Large backfills need batching, idempotency, retry rules and observability. They should be safe to resume and make progress measurable.

• Measure nulls and distributions before migration.
• Batch large updates.
• Make every batch idempotent.
• Delay strict constraints until backfill is complete.
• Compare old and new read paths before cutover.
• Plan application and schema rollback together.

5. Design indexes from real queries

Normalization does not guarantee performance. Indexes should follow measured filters, sort patterns and tenancy boundaries rather than intuition alone.

6. Rollback is a compatibility problem

Once a new application version writes data the old version cannot understand, deployment rollback may become unsafe. Some migrations require forward-fix rather than down migration; that choice should be explicit before release.