Databases With AI

AI can generate a schema in seconds. The question is whether it survives contact with real data and real load. Schema design is where AI makes its biggest unforced errors and where the human review pays the most. A model with a good prompt will produce a CREATE TABLE statement that compiles, indexes that look studious, and migration files that pass dry-run. None of that proves the design will hold up after six months of inserts, three product pivots, and one analytics team that wants to join everything to everything.

Code is rewritten constantly. Schemas are not. Once data exists, the schema is partially frozen by gravity. Every column you regret carries production rows. Every missing constraint has already let bad data in. Every misnamed table has been hardcoded into seventeen analytics queries that nobody owns. AI accelerates the part you were going to do anyway. The thinking is still yours.

Schema Design as the Load-Bearing Decision

Application code can be deleted and rewritten on a Friday afternoon. Database schema cannot. The reason is simple: code has no state, and a schema is the shape of state itself. If you rename a function, you change call sites. If you rename a column, you change call sites and run a migration that touches every row, with downtime risk and replication lag and the usual list of operational hazards. The two operations live in different universes of effort.

This is the asymmetry that makes schema the load-bearing decision in a system. You can change the API. You can change the framework. You can swap React for Svelte and Express for Fastify. None of those rewrites care about your data. A bad column name, a missing foreign key, a denormalized field that drifts: those follow you for years. You inherit them. Future engineers inherit them. The cost compounds.

AI is happy to design a schema in 30 seconds. The schema will look reasonable. The columns will be plausibly named. The types will be roughly correct. There will be an updated_at column because the model has seen one a million times. What the model will not do reliably is interrogate the assumptions: how this entity gets read, how it gets written, what queries it serves, what queries it will need to serve in two years, where the high-cardinality joins live, what fields will need indexes that nobody will think to add later. Those questions are where schema design lives. The CREATE TABLE statement is the easy part.

The failure mode worth naming: AI-generated schemas tend to look reasonable in isolation and pile up technical debt under real usage. The model lacks visibility into your access patterns, your read-write ratio, your reporting requirements, the analytics queries that some intern will write next quarter, and the multi-tenant isolation you forgot to mention. It defaults to the median pattern from its training data, which is fine for tutorials and harmful for production.

Common AI schema mistakes show up in clusters. Over-normalization: a model trained on textbooks splits a perfectly reasonable address blob into seven tables, generating five joins per profile lookup. Under-normalization: it crams everything into a single users table with 47 columns because it inferred a quick prototype. Missing constraints: it adds a status column with a comment saying "active|paused|deleted" and never writes the CHECK constraint that would actually enforce that. Denormalized fields that drift: it adds a user_full_name to orders for query speed, then never wires up the trigger that keeps it in sync, so half your records are stale.

The honest take: when AI generates a schema, treat it the way you would treat a junior engineer's first PR. The structure may be right. The choices need a senior review. Ask: what queries will I run against this every day? What constraints prevent the obvious bug class? What happens when this table has 10 million rows? What happens when a second product feature needs to read it? Those answers shape the schema. Without them, you have a CREATE TABLE statement, not a design.

One subtler failure mode: the AI optimizes for what the prompt mentions and ignores the unstated requirements. You asked for a users table; the model produces one. You did not mention that you have 12 different product surfaces that all need to read users with different join requirements, that there is an analytics pipeline that exports nightly to BigQuery, that the marketing team has a CRM integration that bidirectionally syncs a subset of fields, or that the support team accesses user data through a separate admin tool with audit-trail requirements. The model produced a reasonable users table. The reasonable users table is wrong because the requirements were never on the table.

This is not the model's failure. It is the failure to bring real requirements to the conversation. The discipline that helps: write the access-pattern document before the schema. List the queries. List the join shapes. List the integrations. List the read-write ratios. Now the schema can be designed against those requirements, with the model as a fast typist, instead of designed against the absence of them, with the model as a guesser.

SQL vs NoSQL Choices

SQL is the right default for almost every application. This is not a fashion statement. It is the result of forty years of database research producing a model that gets transactions, schemas, joins, and ad-hoc queries correct simultaneously, with mature tools and well-understood failure modes. Postgres in particular has eaten most of the niches that NoSQL was supposed to own: it does JSON well, it does full-text search, it does geographic data, it scales further than most teams will ever need, and it has an enormous ecosystem of operational tooling.

The pitch for NoSQL was usually about scale or developer experience. Both pitches have aged poorly. Postgres handles tens of thousands of writes per second on commodity hardware with no exotic configuration. Developer experience is now equally good across SQL and NoSQL because ORMs and migration tools have caught up. The remaining honest reasons to pick a NoSQL store are specific: you have a workload that genuinely fits one model, and you accept the tradeoffs that come with it.

Document stores like MongoDB make sense when your data is hierarchical, unstructured per-document, and rarely joined. Configuration data, content with optional fields, audit logs, telemetry from devices that emit different shapes: these can fit. The cost is that joins become application-side, transactions are weaker, and ad-hoc analytical queries get harder. Once you start joining documents, you have rebuilt SQL with worse tools.

Key-value stores like Redis are not a database in the traditional sense. They are a cache, a session store, a queue, a counter, and a leaderboard. The right framing is: Redis is in front of your real database. It holds hot data, ephemeral state, rate-limit counters, and pub-sub channels. Treating Redis as a primary store works for a small number of patterns and breaks for everything else.

Wide-column stores like Cassandra exist for write-heavy workloads at multi-region scale, where the query patterns are known in advance and the analytical side is handled separately. The honest tradeoff: you give up flexible querying and you give up traditional transactions. The price is paid every time someone says "wait, we need to look up by a different field."

Graph databases like Neo4j shine on traversal-heavy workloads: social graphs, recommendations, fraud detection. If your problem is "find all entities within four hops of this entity that share property X", a graph database is the right tool. If your problem is "store a list of users", you do not need a graph database, no matter how the model insists.

The "easier" claim about NoSQL falls apart at the second feature. The first feature, adding a user, looks easy. The second feature, listing users with their orders, runs into N+1 queries against the document store and your application code grows a join layer. The third feature, a dashboard with filters, runs into the lack of secondary indexes or the cost of maintaining them. By the fifth feature, you have rebuilt half of SQL in your application code, and you have done it badly. SQL was easy; you skipped past it because someone said schemas were hard.

SQLite deserves a special note. It is the most under-rated database for small to medium production workloads. It runs in-process, has zero operational overhead, and is faster than any networked database for read-heavy workloads. Combined with Litestream for replication, it covers a surprising number of use cases that teams reach for Postgres on instinct. The case for SQLite is "your service does not need a separate database server."

The pragmatic recommendation: pick Postgres unless you have a specific reason not to. Pick Redis as a companion for caching and ephemeral state. Pick SQLite if you are running a single-machine service or an embedded application. Reach for MongoDB or Cassandra or Neo4j when you can articulate the exact problem they solve and why Postgres cannot solve it. AI will happily recommend MongoDB because it has been over-marketed in training data. The recommendation should not be the end of the conversation.

A note on the modern Postgres ecosystem: the database has acquired capabilities that used to require separate systems. JSONB columns make Postgres a competent document store, with GIN indexes that make queries against nested JSON fast. Full-text search via tsvector and tsquery handles workloads that used to demand Elasticsearch for many teams. PostGIS adds first-class geographic types and queries. Logical replication enables pub-sub patterns and cross-region setups. Extensions like pgvector add vector similarity search for the new generation of AI-adjacent workloads. The Postgres of 2026 is not the Postgres of 2010; the boundary of what it does well has moved outward.

The cost of this expansion: more complexity in the database itself, more knobs to tune, more ways to misuse it. The benefit: one operational story instead of three. A team running Postgres plus Redis covers a remarkable range of production workloads with a small operational surface, well-understood failure modes, and tooling that the entire industry knows how to use. Adding a fourth or fifth datastore is where complexity costs start to dominate; the burden of proof should be high before you do it.

Migrations With AI Assistance

Migrations are the place where AI assistance is most useful and most dangerous. Useful because writing a migration file by hand is tedious and error-prone, and AI is good at converting intent into syntactically correct DDL. Dangerous because a wrong migration can destroy data in seconds, and the model has no understanding of the operational state of your production system.

The single most important distinction in migration work: destructive vs additive. Adding a column is safe. Adding an index concurrently is safe. Renaming or dropping is not safe. Changing a column type is not safe. Anything that touches data, locks tables, or breaks running application instances is in the dangerous category, and the model will produce the SQL for both kinds with the same casual confidence.

Additive migrations follow a pattern: ALTER TABLE ... ADD COLUMN ... with a default that does not require rewriting every row. In Postgres 11 and later, adding a column with a default is fast because the default is stored in metadata. In older versions or with a non-constant default, you back-fill in batches. The model knows the syntax. It does not always know which database version you are on or whether your traffic can survive a long-running ALTER.

Destructive migrations need a multi-step pattern: deploy code that handles both old and new schema, then migrate data, then deploy code that uses only the new schema, then drop the old. Skipping any step risks downtime. The AI will happily generate "ALTER TABLE users DROP COLUMN legacy_field" as a one-liner. Running that against production while v1 of your code is still in flight will cause errors. The model will not warn you. You have to know.

The down migration discipline is worth its own paragraph. Most teams insist on writing reversible migrations. Most production migrations never get reversed. The honest practice: write down migrations for development convenience, accept that production reversibility is mostly fictional, and rely on backup-and-restore for catastrophic recovery. The case for skipping a down migration is when reversing the change would lose data anyway. Dropping a column has no honest reverse: the data is gone.

Tools matter, and the field is mature. Prisma migrate produces declarative migrations from a schema file: you change the schema, it generates the SQL, you review and apply. Drizzle Kit gives you the same workflow with an SQL-shaped abstraction and a slightly tighter relationship to the underlying database. Flyway and Liquibase are battle-tested across language ecosystems and integrate cleanly with CI pipelines. Plain SQL files in a numbered directory, run by a small migration runner, is still a perfectly valid approach for teams that prefer minimum tooling.

The pattern that works regardless of tool: AI proposes, human reviews, CI runs against staging, then prod. The AI is never the operator. Even with extremely capable agents, the act of running migrations in production should be initiated by a human and observed by a human. The cost of getting this wrong is too high to delegate.

One more pattern worth noting: backwards-compatible deploys. A two-phase deploy where the new code can read both old and new schema, the migration runs, then a follow-up deploy removes the legacy path. This adds work but eliminates the failure mode where the deploy and the migration get out of order. AI is excellent at writing the both-paths code if you ask for it explicitly.

The expand-contract pattern deserves its own callout. To rename a column safely: add the new column, write code that writes to both, backfill the old data into the new column, switch reads to the new column, stop writing to the old, then in a separate deploy, drop the old. Five steps to rename a column without downtime. The AI will produce all five if you ask for "an expand-contract migration to rename users.full_name to users.display_name." It will produce a one-line ALTER if you ask for a column rename. The framing matters; the same intent produces wildly different code depending on how you describe it.

Long-running migrations have their own discipline. ALTER TABLE on a large table can hold a lock for hours and effectively take the table offline. Online schema change tools like pg_repack for Postgres, or pt-online-schema-change for MySQL, do the work in the background by creating a copy of the table, applying changes, and atomically swapping. The AI knows these tools exist; it does not always reach for them unprompted. For tables larger than a few million rows, ask explicitly: "produce this migration as a pg_repack-compatible operation" or "use pt-online-schema-change for this." The output is different and the production behavior is dramatically safer.

Indexing Strategies

AI defaults on indexes are usually wrong in opposite directions. The first failure: it adds an index on every column in the table, including columns nobody filters by, on the theory that indexes make things fast. The second failure: it adds no indexes at all, because the prompt did not mention them. Both produce production problems.

The truth: index columns you actually filter on, sort by, or join on. Every other index is dead weight. Each index slows down INSERT, UPDATE, and DELETE operations because the database has to keep all of them in sync. Each index takes storage. Each index is a write-amplification cost paid every time a row changes. The tradeoff is real, and the right answer is the smallest set of indexes that makes the queries you care about fast.

Composite indexes are where the model most often gets it backwards. The order of columns in a composite index matters enormously. An index on (user_id, created_at) lets you filter by user_id alone and by user_id plus created_at, but it does not help a query that filters only by created_at. The rule of thumb: put the most selective filter first if you also filter by the second column, but the access pattern is what really determines the order.

Partial indexes are underused. They let you index only the rows you care about. Soft-deleted records? Index where deleted_at IS NULL. Active subscriptions? Index where status = 'active'. The benefit is double: smaller index, and the index is only updated when rows enter or leave the partial set. The model rarely suggests partial indexes unprompted; you have to ask for them or recognize the opportunity yourself.

Covering indexes (also called INCLUDE indexes in Postgres 11+) let a query be answered entirely from the index without touching the table. For a hot read path, this can be the difference between a 5ms query and a 50ms query. The cost: bigger index, more write amplification. Use them where reads dominate writes by a large factor.

-- Before adding an index
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 4287 AND created_at > now() - interval '30 days'
ORDER BY created_at DESC LIMIT 50;

-- Seq Scan on orders  (cost=0.00..184722.00 rows=2400 width=124)
--   Filter: ((user_id = 4287) AND (created_at > now() - interval '30 days'))
--   Rows Removed by Filter: 4197600
-- Planning Time: 0.234 ms
-- Execution Time: 1483.992 ms

-- After adding a composite index with the right column order
CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC);

EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 4287 AND created_at > now() - interval '30 days'
ORDER BY created_at DESC LIMIT 50;

-- Index Scan using idx_orders_user_created on orders
--   Index Cond: ((user_id = 4287) AND (created_at > now() - interval '30 days'))
-- Planning Time: 0.291 ms
-- Execution Time: 1.847 ms

The 800x speedup in that example is not theoretical. It is what happens when a query that was doing a sequential scan over 4 million rows starts using an index. The same query, the same database, a one-line schema change. This is the kind of win that AI is good at suggesting once you give it the EXPLAIN output and ask why the query is slow.

Reading EXPLAIN is a skill. The AI can interpret an EXPLAIN plan, and it does this well, but you should learn to read them yourself for two reasons. First, you will run into EXPLAIN output every time something is slow, and asking the model is slower than reading. Second, the model will sometimes hallucinate optimizations that do not match the actual plan. The check on hallucination is your own ability to read the plan.

The cost side of indexes deserves its own paragraph. A table with 12 indexes is a table where every INSERT does 13 writes (one to the heap, twelve to indexes). At high write volume, this matters. At low write volume, it does not. The decision is workload-dependent. The default that fails: adding indexes proactively across the schema and never removing the unused ones. Postgres has pg_stat_user_indexes; check what is actually being used and remove what is not.

Performance Tuning

Performance tuning is where the gap between AI fluency and AI judgment is widest. The model knows every technique. It knows about EXPLAIN, about query plans, about connection pooling, about read replicas, about caching. What it does not know is which one of those is the right intervention for your specific problem. That is a diagnostic skill, and it relies on data you have not yet shown the model.

The first move is always measurement. Slow query logs in MySQL and Postgres are the foundation: turn on log_min_duration_statement (Postgres) or long_query_time (MySQL), set it to 100 milliseconds, and let it run for a day. The output is a list of every query that exceeded the threshold, with frequency. You now have a prioritized list of things to fix, ordered by total time spent. This is real data. Optimize against this, not against intuition.

EXPLAIN and EXPLAIN ANALYZE turn a slow query into an action plan. The plan tells you whether the query is doing a sequential scan when it should be doing an index scan, whether the join order is wrong, whether the row estimates are off (often a hint that statistics are stale and you need to ANALYZE the table), or whether the planner is making a bad decision based on outdated statistics. The model can read these. So can you.

Connection pooling is the next intervention. Postgres in particular suffers when application servers open many connections; each connection has nontrivial memory cost, and lots of connections degrades performance even when they are idle. PgBouncer in transaction-pooling mode is the standard answer. It sits between your application and Postgres, multiplexing many client connections onto a smaller pool of real database connections. The win is real and consistent: you go from "Postgres is dying" to "Postgres is fine" by adding a 50MB process. Built-in pool managers in your application framework also help, but they do not solve the fundamental Postgres-connection-cost problem; only a real pooler does.

Read replicas are the next big lever. Most production databases are dramatically read-heavy: 10:1 or 100:1 reads per write is typical. Adding one or more read replicas, with the application routing read traffic to them, scales the read side linearly. The catches: replication lag (a write may not appear on a replica for some milliseconds), read-after-write consistency (the user just submitted a comment and now does not see it), and the operational complexity of keeping replicas healthy. None of these are show-stoppers; all of them require thought.

Caching layers are where Redis lives. The pattern is simple: before hitting the database, check Redis. If hit, return. If miss, query the database and populate the cache with a TTL. The cache invalidation problem is famously hard; the practical answer for most workloads is a short TTL (30 seconds to 5 minutes) plus explicit invalidation on write. This covers 90% of cases without serious correctness risk. For workloads that need cache to be perfectly consistent with the database, the real pattern involves write-through caching and is more complex than it looks.

The order of operations matters. Indexes first; they are cheap and the wins are huge. Query rewrites second; the N+1 problem alone is responsible for half the slow pages on the internet. Connection pooling and caching third; they are infrastructure-level wins that pay off for the whole system. Read replicas and vertical scaling last; they cost real money and are the right answer only after the cheaper interventions are exhausted.

One pattern worth naming: the death spiral. Slow queries cause connections to pile up. Connection exhaustion causes more queries to hang. Hanging queries cause replication lag. Replication lag causes failovers to be unsafe. The cure: address the root cause (a single slow query, usually) before the secondary effects start cascading. AI is useful here as a co-pilot, but the diagnostic loop is human-driven; the model needs to be told what is happening before it can suggest a fix.

A practical tip on N+1 queries, since they account for so much production slowness. The pattern is loading a list of items, then for each item loading a related record, in a loop. ORMs make this easy to write accidentally. The fix is some form of batched fetch: a single query that loads all the related records at once, then groups them in memory. Prisma's include, Drizzle's relational queries, SQLAlchemy's joinedload, Django's select_related and prefetch_related: every ORM has the tool. The model can spot N+1 patterns in code review reliably; ask for it. A 30-line application function with two implicit N+1 loops can become a 35-line function with the same behavior and a 20x speedup.

Backups and Disaster Recovery

Backups are the boring critical step that every AI-generated setup script skips. Ask the model to scaffold a Postgres deployment and you get a docker-compose file with credentials, a connection string, and the application code. Ask it about backups and you get a polite essay about pg_dump. The disconnect is operational: backups are not part of the dev experience, so they are not in the default scaffold, and they are the single most consequential omission you can make.

The unironic rule: if you have not restored from a backup, you do not have a backup. You have a file. The file may or may not represent a recoverable state. The only test is restoring it into a fresh database and confirming the application can start, the data is consistent, and the indexes are intact. Most teams discover their backup process is broken on the day they need it.

The minimum useful backup setup has three properties. Daily snapshots, kept for at least 30 days. Off-site copies, on different infrastructure than the primary database. Tested restores, run on a schedule (monthly is reasonable for most teams). Anything less than this is a backup theatre exercise.

For Postgres specifically: pg_dump is the simplest tool, and it works. For larger databases or hot-restore requirements, switch to pg_basebackup with WAL archiving for point-in-time recovery. Managed providers like AWS RDS, Google Cloud SQL, Crunchy Bridge, Neon, and Supabase offer automated backups with point-in-time recovery built in; using them well is a matter of confirming the retention period and testing the restore path. The cost of using a managed provider is mostly worth it for the operational simplicity of backups alone.

For SQLite, Litestream is the standard answer. It streams the SQLite WAL to S3 (or compatible storage) in near-real-time, providing both replication and point-in-time recovery. The setup is small, the cost is low, and the recovery story is good. SQLite plus Litestream covers a wide range of production workloads with minimal operational overhead.

For MySQL, mysqldump for small databases, Percona XtraBackup for larger ones with hot backup needs. Replication setups give you a hot standby; combine with binary log archiving for point-in-time recovery.

For MongoDB, mongodump for small databases, replica set snapshots for larger ones. Atlas (the managed offering) handles this automatically. Do not run a single-node MongoDB in production without backups; the storage engine has historically been less forgiving than Postgres about partial corruption.

The AI's role in backups is mostly to write the scripts, document the runbook, and remind you to test. None of these are exotic. All of them are commonly skipped. A team that asks the model "write me a daily backup script with retention and an offsite copy" gets a working setup. A team that does not ask gets nothing, because the default scaffold does not include backups. The skill is asking.

ORMs (Prisma, Drizzle, etc.)

The ORM debate is as old as databases themselves. The honest position: ORMs help in the boring 80% of queries and hurt in the spicy 20%. Knowing which is which is the skill. Picking an ORM that lets you escape into raw SQL gracefully is the practical move.

Where ORMs help: type safety end-to-end, especially when the schema is the source of truth and the types flow into your application code automatically. This is a real productivity win. Migrations integrated with the schema definition. Less boilerplate for simple CRUD. Query builders that catch obvious errors at compile time. For a team building features quickly, these benefits compound.

Where ORMs hurt: complex queries become harder to express, sometimes much harder. The escape hatch (raw SQL inside the ORM) is always available, but using it loses some of the type safety that motivated the ORM in the first place. Performance on complex queries is often worse than hand-written SQL because the ORM's query planner makes safer-than-optimal choices. Debugging "why is this query slow" is harder when the SQL is generated.

Prisma is the dominant choice in the Node.js and TypeScript ecosystem for a reason. It has the best developer experience in the field: a clean schema language, strong type generation, well-thought-out migrations, and a query API that is genuinely pleasant. The tradeoff: Prisma generates SQL that is sometimes less efficient than what you would write by hand, and the relational query layer has rough edges. For most applications, the productivity win outweighs the perf cost. For applications where database performance is critical, you escape into raw SQL on hot paths.

Drizzle is the SQL-first alternative. The query builder mirrors SQL almost line-for-line, the types flow directly from your schema, and there is no hidden magic. The learning curve is gentler if you already know SQL. The tradeoff: less abstraction means more SQL to write for common operations, which is good for control and bad for speed-of-implementation on simple CRUD.

Kysely sits in a similar place to Drizzle: a type-safe query builder that stays close to SQL. The strength is precise type inference; the weakness is that it is less opinionated than Prisma or Drizzle, so you bring more decisions about migrations and tooling.

TypeORM is the Java-flavored ActiveRecord pattern in Node. It works, it has a large ecosystem, and it is showing its age. New projects rarely pick it. Existing projects on TypeORM keep using it because migrating away is expensive.

For Python, SQLAlchemy is the classic choice and remains excellent. Django's ORM is fine for Django apps. For raw control, modern libraries like asyncpg with psycopg-style query helpers are common in performance-sensitive services.

The pragmatic recommendation: Prisma for most TypeScript apps. Drizzle when you want SQL-shaped types and a tighter relationship to the database. Raw SQL on hot paths regardless of which ORM you picked. AI is fluent in all of these, and the choice should not be driven by what the model defaults to. It defaults to the popular thing, which is sometimes also the right thing and sometimes not.

One thing worth noting: AI is unusually good at translating between ORMs. Migrating from TypeORM to Drizzle, or from one query builder to another, is the kind of mechanical-but-tedious work the model does well, especially when you give it the source code and the target API. Treat this as an accelerant for migrations you have been putting off.

Data Integrity and Constraints

The most under-appreciated way AI generates technical debt: it skips constraints. The schema works, the types compile, the application runs. Eventually, bad data gets in. Eventually, the bad data causes a bug. Eventually, you discover the bug, trace it back, and find that a NOT NULL or a CHECK or a UNIQUE constraint would have prevented it at insert time. The hours spent debugging would have been zero if the constraint had been there.

The "constraint at the database, not the app" rule is one of the most valuable patterns in database design. The application layer believes it is the source of truth for what data is valid. The application layer is wrong. There are always other writers: scripts, migrations, ad-hoc inserts during incidents, ETL jobs, the analytics team's import process, the admin tool that bypasses the API. Every constraint enforced only in the application is a constraint that some path will eventually skip.

The database is the chokepoint. Every write goes through it. A constraint at the database level is enforced for every writer, forever. The cost is small: a CHECK constraint adds nanoseconds to an INSERT. The benefit is a class of bugs that simply cannot happen.

NOT NULL is the easiest constraint and the most commonly missed. The model defaults to nullable columns because nullable is "safer" in the sense that any value works. The cost is paid every time you have to handle a NULL in application code that should never have been NULL in the first place. Default to NOT NULL; allow NULL only when there is a real reason, and document the reason.

UNIQUE constraints prevent the duplicate-record class of bug. Email addresses, slugs, idempotency keys, external IDs from third-party services: all of these benefit from a UNIQUE constraint. The model often skips this because adding it after data is already in the table is harder. The right time to add a UNIQUE constraint is at table creation.

FOREIGN KEY constraints ensure referential integrity. The model often skips them, especially in NoSQL-influenced patterns. The skipping reasons are usually "performance" (a small concern, mostly resolved by modern databases) or "we will enforce this in the application" (already addressed above; the application is not the only writer). The cost of dangling references is paid in mysterious bugs forever.

CHECK constraints catch domain-specific invariants. Status values that must be in a fixed set. Numeric ranges that must be positive. String lengths that must be reasonable. Dates that must not be in the future for created_at fields. Each CHECK is a tiny piece of documentation that is also enforcement. The model skips these by default; you should add them.

-- Constraint-light schema: looks fine, fails in practice
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  status VARCHAR(50),
  total NUMERIC,
  email VARCHAR(255),
  created_at TIMESTAMP,
  notes TEXT
);

-- Constraint-rich schema: bugs are rejected at insert time
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled', 'refunded');

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status order_status NOT NULL DEFAULT 'pending',
  total NUMERIC(12, 2) NOT NULL CHECK (total >= 0),
  email CITEXT NOT NULL CHECK (email LIKE '%@%'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  notes TEXT,

  CONSTRAINT orders_email_length CHECK (length(email) <= 254),
  CONSTRAINT orders_notes_length CHECK (length(notes) <= 10000)
);

CREATE INDEX idx_orders_user_status ON orders (user_id, status);
CREATE INDEX idx_orders_created ON orders (created_at DESC);
CREATE INDEX idx_orders_status_pending
  ON orders (created_at) WHERE status = 'pending';

The constraint-rich version takes 30 seconds longer to write. It catches bugs that would otherwise reach production. It catches bugs from sources you did not anticipate (the import script, the admin override, the migration that skipped a step). It documents intent for future engineers; the schema itself tells you what valid data looks like. The first version compiles. The second version holds up.

One subtler point: constraints can be added later, but the migration is more expensive when there is already data. Adding a UNIQUE constraint to a table with existing duplicates fails until the duplicates are resolved. Adding a NOT NULL to a column with existing NULLs fails until they are filled. The cost compounds the longer you wait. The right time is at creation; the second-best time is now.

Closing

The schema you ship in week one is the schema you live with for years. Every shortcut taken at creation time becomes a tax paid forever. Every missing constraint becomes a class of bug. Every poorly chosen index becomes either a performance problem or an unused write-amplification cost. Every denormalized field that drifts becomes a data-quality nightmare that nobody owns.

AI accelerates the typing. It writes CREATE TABLE statements in seconds, generates migrations from intent, suggests indexes, and translates between ORMs. None of that is the part that matters. The part that matters is judgment: about access patterns, about constraints, about what queries you will run a year from now, about what failure modes you can tolerate, about what you can recover from when the backup turns out not to work.

The investment in schema review pays back every quarter for the lifetime of the application. A senior engineer spending an hour reading the AI-generated schema and asking the awkward questions is the highest-yield use of time in the development process. The questions are mostly the same: where are the constraints, what queries does this serve, what happens when the table has 100 million rows, what happens when a second feature needs to read it, where are the indexes, what is the backup strategy, what happens when this column needs to change.

The model will not ask these questions. It will produce something that looks reasonable. The difference between a database that holds up and one that becomes a tax is whether someone, eventually, asks the questions. With AI in the loop, that someone has to be a human, and the time to ask is before the schema ships.

Anthropic's Claude is the recommendation when AI sits in this part of the workflow. The reason is specific to schema work: Claude is unusually willing to push back on ambiguous requirements, ask clarifying questions before writing code, and produce conservative defaults (NOT NULL, foreign keys, CHECK constraints) rather than minimal-viable schemas. For pair-programming on the data model, where the cost of an unforced error compounds for years, that posture matters. The right workflow: describe the access patterns and constraints, let the model produce the first draft, read every line, and push back on anything that smells off. The model is a fast typist with good defaults; the senior judgment is yours.