Add a Database Without Regret

The project

Take an app whose database “just works” and make it a database you can operate: a schema you understand, changes that ship as versioned migrations, indexes chosen from measured queries, and a backup that has been restored at least once.

By the end you will have: a documented schema, migrations in Git, at least one index proved by a before-and-after measurement, and a restore drill you have completed.

Before you start

• The app’s data-access layer, wherever the queries actually live.
• A way to run SQL against the database directly — psql, a GUI client, or the host’s console.
• Databases for AI Builders as the map. It is a hub, and this journey is the order to walk it in.

Milestone — Choose (or validate) the database

Build: a written decision about the database engine and why.

Assistant-generated apps default to whichever database the model saw most recently. That is not a decision, and it is worth making explicitly before you build on top of it.

Do it

1. Answer the three questions from How to Choose a Database: relational or not, how much structure, how much scale.
2. Write the engine and the reason into NOTES.md.
3. If the answer is “keep what I have,” note the trade-off you are accepting.

Done when the choice is written down, not inherited. If you are weighing engine families, What Are the Different Types of Databases? lays them out.

Milestone — Document the schema you already have

Build: a single file describing every table, column, and relationship.

You cannot safely change a schema you cannot describe. Do this before writing any migration.

Do it

1. Ask your agent to dump the current schema from the live database, not from the ORM models — the two are often out of sync.
2. Have it write a SCHEMA.md with tables, types, nullability, foreign keys, and indexes.
3. Read it and mark anything you cannot explain.

Done when SCHEMA.md matches the live database exactly. How to Design a Database Schema gives the naming and normalisation conventions to fix while you are in there.

Milestone — Put every change under migrations

Build: a migration per schema change, applied automatically on deploy.

Hand-edited production tables are how environments drift apart. Migrations make each change reviewable, reversible, and identical everywhere.

Prompt your agent

Set up database migrations for this project.

- Create an initial migration capturing the current schema
  exactly as it is in production.
- Add a command to run pending migrations, and to roll the
  last one back.
- Run migrations as a separate deploy step, before the app
  starts.
- Do not make destructive changes in a single migration:
  add-then-backfill-then-drop, in three steps.

Show me the migration files and the run command.

Done when a fresh environment reaches the production schema with one command. What Are Database Migrations? explains why the three-step rule for destructive changes matters, and Common Database Mistakes in AI-Generated Apps lists the patterns to avoid.

Milestone — Index the queries that are actually slow

Build: at least one index added in response to a measured slow query.

Indexes are the difference between a query that is fine at 500 rows and one that is fine at 500,000. Adding them by guesswork creates write overhead; adding them from evidence does not.

Do it

1. Turn on slow-query logging, or use the database’s built-in statistics to find the slowest queries.
2. Read the query plan (EXPLAIN ANALYZE in Postgres) and look for a sequential scan on a filtered column.
3. Add the index, re-run the plan, and record the before-and-after time.

Done when one query drops from noticeably slow to fast, and you can point at the plan that changed. What Is Database Indexing? and How to Add an Index to a Slow SQL Query walk through it; How to Debug AI-Generated Database Queries covers the generated-code patterns that cause the slowness.

Milestone — Find and fix the N+1 queries

Build: the loop that issues one query per row turned into a single query.

Generated ORM code loves N+1: list 50 items, fire 51 queries. It works in development and collapses with real data.

Do it

1. Watch the database log while loading a list page.
2. Count the queries. If the count scales with the rows, you have an N+1.
3. Ask your agent to replace it with a join or a batched fetch, then re-count.

Done when loading 50 rows issues a constant number of queries, not 51. What Is the N+1 Query Problem? has the fix patterns for both raw SQL and ORMs.

Milestone — Make multi-step writes atomic

Build: transactions around every operation that touches more than one row.

“Charge the card, then create the order” is two writes. If the app dies between them, the data lies. A transaction makes them one.

Do it

1. Ask your agent to list operations that write to more than one table.
2. Wrap each in a transaction, with a defined isolation level.
3. Test the failure path: force an error after the first write and confirm nothing persisted.

Done when an injected failure mid-operation leaves the database unchanged. See What Is a Database Transaction?.

Milestone — Stop running out of connections

Build: a connection pool sized to the database’s actual limit.

The classic small-app failure is “too many connections” at the moment traffic rises, because every request opens its own connection.

Do it

1. Find the database’s connection limit.
2. Configure a pool smaller than that limit, shared across the app — including any workers or cron jobs.
3. Load-test enough to confirm you no longer see connection errors.

Done when a burst of concurrent requests does not produce “too many connections.” What Is a Connection Pool? and How to Fix ‘Too Many Connections’ in Postgres have the numbers.

Milestone — Prove a backup actually restores

Build: a scheduled backup, and a restore you have performed.

An untested backup is a file, not a backup. This is the milestone that turns a bad day into a slow afternoon.

Do it

1. Schedule an automated backup to storage that is not the database’s own disk.
2. Set a retention window.
3. Restore into a scratch database from the backup and run a real query against it.

Done when you have restored the data yourself and can state how long it took. How to Set Up Automated Database Backups is the step-by-step.

Ship checklist

• The engine was chosen deliberately and the reason is written down.
• SCHEMA.md matches production.
• Every schema change ships as a migration, run as a deploy step.
• Destructive changes are staged across multiple migrations.
• At least one index was added from a measured slow query.
• No list page scales its query count with row count.
• Multi-row writes are inside transactions.
• Connection pooling is configured below the database limit.
• A backup has been restored, and the restore time is known.

Where this journey goes wrong

• Editing production by hand. Environments drift and nobody can reproduce the state.
• Dropping a column in the same migration that adds its replacement. Deploys overlap; the old code breaks.
• Indexing everything. Writes slow down and the planner gets worse.
• Trusting the ORM’s generated SQL. Check the query count before and after.
• Treating backups as done once scheduled. The restore is the test.

FAQ

Can I do this on a database my app already uses?

Yes — that is the intended path. These are audit-and-upgrade steps: document the live schema, put existing tables under migrations, index the measured slow queries, and add the missing backup. Nothing requires starting over.

When should I not add an index?

On columns you rarely filter or sort by, and on write-heavy tables with light read volume. Every index speeds reads and slows writes, so add them in response to a specific measured query rather than preemptively.

Related guides

• What Is an ORM (and Should You Use One)? — understand the layer generating your SQL.
• Debugging AI-Generated API Logic — when the bug is in the handler, not the query.
• Journey: Debug and Refactor the Code You Didn’t Write — the general get-unstuck method.
• Journey: Make Slow Work Reliable — moving expensive writes off the request path.