Data integrity and safety averages 51.6 out of 100 across the 20 third-party apps scored on it in my June and July 2026 audits. The schema is where to start: nulls nobody chose, emails that repeat, deletes that cascade too far. A database review checklist asks 6 questions of every table, then draws the diagram a reviewer reads.
What these controls are, together: the database review checklist and the data model diagram
A database schema review checklist works through 6 questions for every table: are the types right, which columns may be null, does every reference have a foreign key, what must be unique, what happens to children on delete, and which column names the owner or tenant. The data model diagram is the seventh item.
Those 20 apps are the third-party apps I audited in June and July 2026 and could score on this pillar, a selected set rather than a random sample, so the average is not a rate for AI-built apps in general. The six questions are the schema part of the data consistency checklist for SaaS, the wider list for keeping a small app’s data correct and recoverable.
| # | Question | A bad answer looks like | The fix |
|---|---|---|---|
| 1 | Are the types right? | Money in a floating-point column, dates stored as text, two kinds of id across tables | Money as integer cents or numeric, which the PostgreSQL manual recommends for monetary amounts (real and double precision are inexact); timestamps as timestamp with time zone, stored as UTC; one kind of id throughout |
| 2 | Which columns may be null? | Every column nullable, because nullable is the default | NOT NULL on every column the code reads as always present; nullable only where someone decided it |
| 3 | Does every reference have a foreign key? | An org_id column with no constraint behind it | A foreign key on every column that points at another table’s row |
| 4 | What must be unique? | Email, slug or external id with a plain index or no index | A unique index; on lower(email) where case must not matter |
| 5 | What happens to children on delete? | The same rule on every relationship, chosen by nobody | An ON DELETE rule chosen per relationship |
| 6 | Which column names the owner or tenant? | Tenant-owned tables with no org_id or workspace_id | A tenant column on every tenant-owned table, NOT NULL and foreign-keyed |
| 7 | Can someone who was not there read it? | No diagram, or one drawn once and never updated | A data model diagram showing entities, relationships and ownership boundaries |
The review is of a schema that already exists and already holds data, and that changes every fix. When a constraint is added, Postgres normally scans the table to check that the rows already there satisfy it, so a rule the current data breaks will not go on. The order is always the same: find the violations, clean them, then add the rule. In the Production Hardening Sprint this review is deliverable 4.1, Schema integrity review, and the diagram is deliverable 4.13, Data model diagram. Indexes and slow queries are a separate review, which starts with how to analyze a Postgres query.
What goes wrong without them
Four symptoms show up long before anyone opens the schema, and each has a query that finds it in the data you already hold.
| Symptom | The missing rule | The query that finds it |
|---|---|---|
| Two accounts share one email | A unique index on the email column | SELECT lower(email), count(*) FROM users WHERE email IS NOT NULL GROUP BY lower(email) HAVING count(*) > 1; |
| A page crashes for a few users only | NOT NULL on a column the code treats as always present | SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = 'public' AND is_nullable = 'YES'; lists the columns that accept nulls, then SELECT count(*) FROM users WHERE display_name IS NULL; counts the rows one of them breaks |
| Reports count rows whose parent is gone | A foreign key on the child column | SELECT count(*) FROM invoices i LEFT JOIN orgs o ON o.id = i.org_id WHERE i.org_id IS NOT NULL AND o.id IS NULL; |
| Deleting one workspace took the invoices with it | An ON DELETE rule chosen per relationship | The foreign-key listing below, read down its on_delete column |
The column listing in row two also returns the columns of views, which information_schema.columns describes alongside tables; skip any name that information_schema.tables marks as VIEW rather than BASE TABLE. In row three, the i.org_id IS NOT NULL condition matters: without it the count also takes invoices that never had an org, which is a question about nullability, not an orphan. The columns worth running it on are the _id columns that do not appear in the listing below.
-- Every foreign key in the app's own schema, with its delete rule decoded
SELECT conrelid::regclass AS child_table,
confrelid::regclass AS parent_table,
conname AS constraint_name,
CASE confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END AS on_delete
FROM pg_constraint
WHERE contype = 'f'
AND connamespace = 'public'::regnamespace
ORDER BY on_delete, child_table;The listing reads pg_constraint, where contype = 'f' marks a foreign key and confdeltype holds the delete rule as a single letter, which the CASE turns back into words. Filtering on connamespace keeps it to the public schema, so a hosted database’s managed schemas, such as Supabase’s auth and storage, stay out, while a public table that references auth.users stays in. Read it down the on_delete column. Every CASCADE row whose child table holds records the business must keep is a finding, and chains count: if workspaces cascade to projects and projects cascade to invoices, deleting a workspace reaches the invoices.
The database allows duplicate email rows
Two accounts with one email split one person in two. A password reset goes to one account, billing attaches to one and usage to the other, and support cannot tell which is real. It happens when the only uniqueness check lives in application code: the app looked for an existing row, found none, and two requests passed that check at the same moment. Case makes it worse, because A@example.com and a@example.com are different strings to an index on the raw column. The first row of the symptom table finds both kinds, since it groups on lower(email).
In a point-of-sale app I audited in July 2026, the users table had a primary key, one foreign key and three plain indexes, none of them unique. Two rows could share an email and one sign-up could bind both to a single login, leaving the person’s store and role to whichever row a LIMIT 1 returned. An index that is not unique only makes the lookup fast; only a unique index makes the database refuse the second row.
Null values breaking app queries
A nullable column the code treats as always present fails quietly. The page crashes only for the users whose row holds a null, which is why it looks like a bug nobody can reproduce. A filter such as WHERE status != 'cancelled' skips every row where status is null, because a comparison with null yields null rather than true, and WHERE keeps only true rows. Most aggregates, sum and avg among them, discard null inputs, so totals and averages leave those rows out. Nobody chose nullable: it is what Postgres gives a column when nothing else is said. The second row of the symptom table lists the candidates.
Orphans and over-eager deletes
Without a foreign key, child rows can point at parents that no longer exist. An invoice list joined to organizations then drops every invoice whose org is gone, and the report stops adding up to what was billed. The opposite failure is ON DELETE CASCADE on the wrong relationship, where deleting one workspace removes invoices the business must keep. The third row of the symptom table finds the orphans, and the foreign-key listing finds the cascades. How deletes reach each kind of data, class by class, is set out in the data-loss bugs hiding in an AI-built app.
No one understands the database structure
Picture the schema after a year of prompting: forty tables, a third of them unused, column names from three different sessions (userId, user_id, owner). A new developer cannot read the schema, so every change is a guess, and when a reviewer asks for the data model there is nothing to send. The fix is the seventh question: a diagram generated from the schema and kept beside it. It belongs with the rest of documentation for a vibe-coded app, the set a new engineer reads on day one.
How to set them up
One rule covers every fix below: find the existing violations, clean them, add the constraint in a migration that follows the database migration checklist, then test that the database rejects a bad write. The Postgres behavior in this section comes from the PostgreSQL manual (version 18, the current release), and I quote it wherever a lock, a scan or a version matters.
What is a foreign key constraint, and what unique constraints guarantee that code cannot
A foreign key constraint makes the database refuse a row that references a missing parent. A unique index makes it refuse the second copy, even when two requests arrive at the same instant. Application checks cannot promise either: Rails’ uniqueness validation queries for an existing record right before the save, and its own guide says duplicates can still occur.
In the words of PostgreSQL’s constraints chapter, a foreign key “specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table”, and its delete action decides whether removing a parent is refused or carried through to the children. A unique constraint ensures a value “is unique among all the rows in the table”, and adding one “will automatically create a unique B-tree index”. Both hold however many requests race, because the database checks every write.
The Rails validation of uniqueness is the clearest case of a check that loses the race. The Rails guide’s uniqueness section says it “does not create a uniqueness constraint in the database”, that two database connections can “create two records with the same value”, and that “you must create a unique index on that column in your database.” The same holds for any look-then-insert written with Prisma, Drizzle or the Supabase client. The fix is the unique index, plus code that treats SQLSTATE 23505, unique_violation, as “already exists” instead of a crash.
For email, index lower(email): the manual notes that a unique index on lower(col1) prevents rows that “differ only in case”, and a query can use that index when it filters on lower(email) as well. On a large live table, CREATE UNIQUE INDEX CONCURRENTLY builds without the locks that “prevent concurrent inserts, updates, or deletes”, takes longer, and cannot run inside a transaction block; if it fails on a duplicate, it leaves an invalid index to drop before trying again. Uniqueness within a workspace, such as one project slug per workspace, is a two-column unique index on (workspace_id, slug).
-- 1. Emails held by more than one row, in any case
SELECT lower(email) AS email, count(*)
FROM users
WHERE email IS NOT NULL
GROUP BY lower(email)
HAVING count(*) > 1;
-- 2. After merging those rows, refuse the next duplicate
CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_key ON users (lower(email));How to add a NOT NULL constraint in Postgres without breaking the app
Adding a NOT NULL constraint in Postgres to a live table takes 5 steps: count the nulls, agree the backfill value, backfill in batches, add and validate a NOT VALID check constraint on a large table, then set NOT NULL.
- 01 Count the rows where the column is null, for example display_name on users, so the size of the backfill is known.
- 02 Agree the backfill value with whoever owns the data: a default, a value derived from another column, or deleting the row.
- 03 Backfill in batches, so no single update runs for long or touches the whole table at once.
- 04 On a large table, add CHECK (display_name IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT. On a small table, skip this step.
- 05 Run SET NOT NULL, drop the helper check in a separate statement, add a column default for new rows where one makes sense, and update the ORM schema so its types match.
ALTER TABLE users ADD CONSTRAINT users_display_name_nn
CHECK (display_name IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_display_name_nn;
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_display_name_nn;The sequence works because of one sentence in PostgreSQL’s ALTER TABLE reference: “if a valid CHECK constraint exists (and is not dropped in the same command) which proves no NULL can exist, then the table scan is skipped.” That skip is documented from version 12; the version 11 page has no such line. VALIDATE CONSTRAINT “acquires a SHARE UPDATE EXCLUSIVE lock”, so writes carry on while it scans. SET NOT NULL still takes the default ACCESS EXCLUSIVE lock, so the helper check removes the scan, not the lock. From version 18 the manual also allows a not-null constraint to be added NOT VALID directly and validated later, which does the same job without the helper check; on older servers, keep the CHECK path. On a small table, ALTER TABLE ... SET NOT NULL alone is fine.
Set up cascade delete correctly
Postgres offers 5 delete actions on a foreign key, and the data class chooses between them, never the ORM default. CASCADE suits rows with no meaning without their parent, such as sessions and draft items. RESTRICT suits invoices and audit rows. SET NULL suits authorship on content that outlives its author.
The middle column below follows the manual. The last column is my working rule, not a Postgres statement: I choose each relationship’s action by what the child rows mean to the business.
| ON DELETE option | What Postgres does | Typical use |
|---|---|---|
NO ACTION | The default. The delete proceeds, but the foreign key must still hold, so it usually ends in an error; on a deferrable constraint the check can wait until later in the transaction | Whatever nobody chose; treat it as an unreviewed RESTRICT |
RESTRICT | Prevents deletion of a referenced row; the check cannot be deferred | Invoices, payments, ledger entries, audit rows |
CASCADE | Deletes the referencing rows as well | Sessions, reset tokens, draft items, a profile row that exists only for its user |
SET NULL | Sets the referencing columns to null | The author column on comments or documents that outlive the author |
SET DEFAULT | Sets the referencing columns to their default values; the operation fails if the default would not satisfy the foreign key | Reassigning rows to a placeholder owner row |
On Supabase, the common case is a table that references auth.users. Supabase’s guide to managing user data says to “Specify on delete cascade in the reference” and shows a profiles table built that way. That is right for a profile row. It is wrong for an invoices table, and users can be deleted straight from the dashboard under Authentication > Users, so decide the rule for every table that points at a user before anyone deletes an account. Records that should come back after a mistake call for a flag instead of any delete action, which is what soft delete is.
SaaS database architecture: one database, many customers
SaaS database architecture comes in 3 shapes: shared tables with a tenant column, a separate schema or database per tenant, or a separate Postgres instance per tenant. A small SaaS wants the first, with the tenant id NOT NULL, foreign-keyed and leading the composite indexes on every tenant-owned table.
AWS’s partitioning models for multi-tenant PostgreSQL call these pool, bridge and silo. Microsoft’s SaaS tenancy patterns state the trade-off of the first shape plainly: a multitenant database “necessarily sacrifices tenant isolation”, in general has “the lowest per-tenant cost”, and “must have one or more tenant identifier columns”. In the table, the shape and cost columns use their words; the middle column is my reading for a small SaaS.
| Shape | Fits when | What it costs |
|---|---|---|
| Pool: all tenants in shared tables in one schema, separated by row-level security | Almost every small SaaS | Significantly more cost-effective, less operational overhead; the noisy neighbor cannot be completely eliminated |
| Bridge: a separate schema or database per tenant, as logical divisions inside PostgreSQL | A few tenants need their own copy of the schema | Extra provisioning for every tenant; the same noisy neighbor concerns as the pool |
| Silo: a Postgres instance per tenant | A customer contract demands isolation | Often difficult to run cost-effectively; onboarding is more complicated and time-consuming |
For a small SaaS the answer is shared tables. My working rule for them: every tenant-owned table gets a tenant id that is NOT NULL, has a foreign key to the tenant table, and comes first in the composite indexes, so each query for one tenant’s rows starts from that column. The review walks every table, checks for that column, and lists the tables without it; that list is the finding. A database per tenant earns its cost only when a customer contract demands the isolation. How the column is enforced, with row-level security, scoped queries and a two-tenant test, belongs to multi-tenant data isolation.
DDL scripts: exporting a schema a reviewer can read
DDL scripts are the schema written as CREATE and ALTER statements. Generate them from the live Postgres database with pg_dump --schema-only, commit the file, and diff it against a dump of an empty database built from the migrations: a difference means production was changed outside the migrations or a migration never ran.
DDL is data definition language: the CREATE, ALTER and DROP statements that define structure rather than rows, the subject of PostgreSQL’s data definition chapter. A DDL script is the whole schema as text, which is what a reviewer wants to read.
To generate DDL from a live database, run pg_dump against it with the three flags below; pg_dump’s documentation says the tool “does not block other users accessing the database (readers or writers)”. For a Postgres schema export a reviewer can read, --schema-only dumps “only the object definitions (schema), not data or statistics”, --no-owner leaves out the commands that set ownership, and --no-privileges leaves out the grant and revoke commands. Commit the output, so every later review starts from a baseline.
pg_dump --schema-only --no-owner --no-privileges --restrict-key=schemadiff -f prod.sql "$PROD_DATABASE_URL"
pg_dump --schema-only --no-owner --no-privileges --restrict-key=schemadiff -f fresh.sql "$FRESH_DATABASE_URL"
diff prod.sql fresh.sqlThe diff that means something compares production with an empty database built from the migrations folder, dumped with the same flags. Without --restrict-key, pg_dump writes a random key into every plain-text dump, so two dumps never match; the manual names this option for “comparing dump files” and warns it is “not recommended for general use”, so keep those two files for the comparison and restore only from dumps made without it. The option exists only in pg_dump releases from August 2025 on (18, 17.6, 16.10, 15.14, 14.19, 13.22); an older pg_dump rejects it but also writes no random key, so drop the flag there. Every line of that diff is a finding to explain.
On Supabase, supabase db dump “Runs pg_dump in a container with additional flags to exclude Supabase managed schemas”, which keeps auth, storage and extension schemas out of the diff; after supabase db reset rebuilds the local database from the migrations, supabase db dump --local dumps that copy. The ORMs can do the same comparison from their side: Prisma’s db pull rewrites schema.prisma from the live database (it overwrites the file, so commit first) and drizzle-kit pull generates a schema.ts from it, and a git diff shows what drifted. Without a terminal, pgAdmin’s documentation gives the click path: right-click the database in the tree, choose Backup…, pick the Plain format, open the Data Options tab and move the Only schemas switch on, then click Backup.
In the file, a reviewer reads the constraints first, then the indexes, then the row-level security policies.
The data model diagram
A data model diagram for a reviewer fits on one page: tables grouped by domain, the tenant boundary drawn, personal-data tables marked, and the delete rule written on each relationship. Generate it from the schema, then edit it for reading.
The diagram shows entities, relationships and ownership boundaries, and someone who was not there when the app was built can follow it. Generating it from the schema, and again after each schema change, keeps it from drifting away from what the database holds. Editing it for reading takes four passes: group the tables by domain (accounts, billing, content); hide join tables and audit tables; mark the tenant boundary and every table that holds personal data; write the ON DELETE rule on each line that has one. The one-page version goes in the data room. The full generated version, in Mermaid’s entity relationship diagram syntax, lives in the repository beside the schema dump, where a pull request can update both. Generating an ERD from Postgres is part of how to create an architecture diagram.
How to verify each one
A schema review is verified by trying to break it. Test that the database constraints reject bad data with 4 writes that must fail, delete a parent and check each child table followed its rule, then compare the diagram against the actual schema and the schema against the migrations.
Run the checks against a copy of the database loaded with representative data, never against production.
- 01 Insert a second user whose email differs from an existing one only in case. Expect a unique violation, SQLSTATE 23505.
- 02 Insert a child row whose parent id does not exist. Expect a foreign key violation, 23503.
- 03 Insert a row with a required column set to null. Expect a not-null violation, 23502.
- 04 Insert a tenant-owned row with no tenant id. Expect 23502.
- 05 Delete a parent row and confirm each child table did what its rule says: cascaded, refused with 23503, or set its column to null.
- 06 Check that every table and foreign key in the exported DDL appears on the diagram, and that nothing on the diagram is missing from the DDL.
- 07 Diff the production schema dump against the dump of an empty database built from the migrations. Expect no difference.
Keep each error message and SQLSTATE code with the date you ran it: that transcript is the evidence. In the Production Hardening Sprint, these checks close both deliverables. Deliverable 4.1 is verified this way: test rejected invalid records and expected relationship behavior using representative data. Deliverable 4.13 is verified this way: compare the diagram against the delivered schema and migrations. Re-run the checks after every migration that touches a constraint. Pool sizes and connection limits need a different test: connection pooling in Postgres.
Where the sprint does this
In the Production Hardening Sprint, deliverable 4.1 reviews and corrects types, nullability, foreign keys, uniqueness rules and cascade behavior, and deliverable 4.13 delivers a readable diagram of entities, relationships and ownership boundaries for the data room, each closed by the checks in the section before this one. Both are recorded in the production readiness report, deliverable 13.1, which is verified this way: account for all 123 IDs; keep failures visible until resolved and explain genuine non-applicable items. We work on the schema the app already has: its current framework and hosting setup are the starting point, and components are refactored or replaced where the production work requires it. Building new product features or modules, completing unfinished core features or business workflows, and rebuilding core functionality that does not yet perform its intended job are outside the sprint. Every item is listed in the published scope.
Common questions about reviewing a database schema
How to get duplicate email in SQL?
Group on the lowercased email and keep the groups with more than one row: SELECT lower(email) AS email, count(*) AS copies FROM users WHERE email IS NOT NULL GROUP BY lower(email) HAVING count(*) > 1; returns each email held by more than one row, whatever the case. Merge or remove the extra rows with the account owner, then run CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email)); so the database refuses the next duplicate.
To choose which row survives, keep the one that billing and the most recent sign-ins point at, move the other row’s child records onto it, and only then delete the extra.
How to add a constraint to an existing table in SQL?
Use ALTER TABLE ... ADD CONSTRAINT after cleaning out the rows that break the rule, because Postgres checks the existing rows when the constraint is added. On a big table, add a foreign key or check constraint (and, from PostgreSQL 18, a not-null constraint) with NOT VALID, then run VALIDATE CONSTRAINT, which takes a SHARE UPDATE EXCLUSIVE lock instead of blocking writes. A unique rule cannot be added NOT VALID; on a live table, build it with CREATE UNIQUE INDEX CONCURRENTLY instead.
How to resolve foreign key constraint error?
Fix the data, not the rule: insert or restore the parent first, correct the id, or delete the children before the parent. SQLSTATE 23503, foreign_key_violation, means the rule is working: a write points at a parent that does not exist, or a delete would leave children pointing at nothing. Set CASCADE only where the data class calls for it, and never drop the constraint to make the error go away.
What does DDL stand for?
DDL stands for data definition language: the statements, such as CREATE, ALTER and DROP, that define a database’s structure rather than its data. In Postgres, pg_dump --schema-only writes a database’s DDL out as a script.
Is create a DDL or DML?
CREATE is DDL, along with ALTER and DROP, because it defines structure. INSERT, UPDATE and DELETE are DML, the statements that change rows, and the PostgreSQL manual splits them the same way: creating tables is in its Data Definition chapter, and inserting, updating and deleting rows is in its Data Manipulation chapter.
The checks in this guide show you where the app is open. The sprint below closes those gaps, tests the result and writes the evidence down.
Built it with AI. Now it has to hold up for real customers.
The Production Hardening Sprint takes the app you already have and builds the production foundation underneath it. Authentication and access rules, payments that stay consistent, error handling, monitoring, backups, automated tests and a documented handover. Our engineers work inside your existing codebase for ten working days. All 123 deliverables are included, and you get the evidence for each one.
See the Production Hardening Sprint → 
$2,500 fixed price · 10 working days · One codebase