PostgreSQL schema verification before the first migration

An AI builder can produce valid PostgreSQL and still infer the wrong database. Syntax is the easy part. The dangerous mistakes are plausible: an optional relationship becomes required, a status string receives an incomplete CHECK constraint, a deletion cascades through records that should survive, or a migration recreates a table and quietly loses a column.

PostgreSQL schema verification must therefore test meaning, migration behavior, and recovery as separate concerns. I approve an inferred schema only after it survives a known dataset, explicit invariants, representative queries, destructive change review, and a restore rehearsal. If any one of those is missing, the migration is still a proposal.

The first migration deserves this scrutiny even when the production database is empty. Early schema errors harden quickly because application code, seed data, reports, and later migrations begin depending on them. A fifteen minute review before the first run is usually cheaper than explaining six months later why two different concepts share one nullable text column.

An inferred schema is an untrusted specification

Treat the inferred schema as a draft specification, not executable truth. The builder saw prompts, sample screens, imported records, or generated application code. It did not witness every business exception, retention rule, bulk import, support repair, and failed payment that the database will eventually hold.

Start by separating three questions that teams routinely blur. Schema correctness asks whether tables and constraints model the domain. Migration safety asks whether the proposed operations preserve existing data and keep the database usable while they run. Recovery readiness asks whether you can return to a known state after a partial or semantically wrong change. Passing one says little about the other two.

A CREATE TABLE statement can describe the intended final structure and still arrive through unsafe operations. Suppose the builder changes customer_name text into customer_id bigint. The final foreign key may be sensible, but a migration that drops the name column before matching historical names to customers destroys the only evidence needed for that match. Schema review approves the destination; migration review examines the journey.

Read the proposed model aloud in domain language. Say that each invoice belongs to exactly one legal customer, rather than saying invoices.customer_id references customers.id. The first sentence invites useful objections: drafts may exist before customer selection, imported invoices may refer to archived customers, and legal records may need the customer name frozen at issue time. SQL vocabulary can conceal those disagreements.

I require an assumption note beside every inferred table. It should state what one row means, how the row is identified, who owns it, whether it may exist without its apparent parent, and what deletion means. If the team cannot answer those points, the builder has guessed at a database that the team has not designed.

Known records expose wrong table mappings

A known dataset should contain records chosen for semantic coverage, because a large random sample often repeats the same easy case. Ten carefully selected records can reveal more than ten thousand nearly identical happy path rows.

Build a mapping matrix before running DDL. One row in the matrix should trace each source concept through the proposed destination and record the expected count or value. For an order application, the artifact might look like this:

This catches table mapping errors before constraint details distract the review. AI builders often normalize repeated objects into separate tables, which is usually sensible, but repetition does not prove identity. Two shipping addresses with the same text may be historical snapshots rather than references to one editable address row. Merging them means a later address edit rewrites history.

The opposite error also occurs. A builder may copy customer fields into every order because the screen displays them together. Some values belong to the customer, while other values must remain an order snapshot. The right design can contain both customer_id and issued document fields such as billing_name. Calling that duplication and deleting one side loses either current identity or historical truth.

Load the known dataset into a disposable database using the same import or seed path the application will use. Then write assertions against facts, not merely row counts:

SELECT
    (SELECT count(*) FROM orders WHERE external_id = 'ORDER-A') AS order_a,
    (SELECT count(*) FROM order_items i
       JOIN orders o ON o.id = i.order_id
      WHERE o.external_id = 'ORDER-A') AS order_a_items,
    (SELECT account_id IS NULL FROM orders
      WHERE external_id = 'ORDER-B') AS order_b_unassigned;

A passing shape should be explicit:

 order_a | order_a_items | order_b_unassigned
---------+---------------+---------------------
       1 |             2 | t

Do not accept an unexplained difference because the generated application still renders. A UI can hide duplicate parents, dropped children, truncated codes, and invented defaults. Reconcile every deliberate fixture before discussing production rollout.

Constraints must encode domain truths

A database constraint should reject a state that is always invalid, regardless of which screen, API, import, or repair script writes the row. If a rule has exceptions or depends on mutable external facts, forcing it into a simple constraint often produces either blocked work or dishonest data.

Primary keys identify rows; they do not automatically provide a meaningful business identity. An internal bigint ID can coexist with a unique tenant scoped order number. If the business says order numbers are unique per tenant, UNIQUE (tenant_id, order_number) expresses that rule. A global unique constraint would reject legitimate records, while no constraint would permit ambiguity during retries.

CHECK constraints suit stable row facts such as quantity > 0 or finished_at >= started_at. PostgreSQL's manual explains that the database assumes a CHECK expression is immutable for the life of the constraint. That is why a CHECK calling a function whose behavior later changes can leave old rows violating the apparent rule. Use a fixed expression for fixed truth. Put changing policy, such as a currently allowed set controlled by administrators, in a referenced table or application workflow.

Generated status constraints need suspicion. A builder may inspect current examples and emit:

status text NOT NULL
    CHECK (status IN ('draft', 'active', 'closed'))

That is correct only if those are the complete durable states. Ask about failed, cancelled, suspended, imported, and unknown legacy records. If the state machine is still changing, a lookup table can make additions explicit, but it does not replace transition validation. A row allowed to contain closed says nothing about whether it may move directly from draft to closed.

Use uniqueness deliberately. PostgreSQL implements a unique constraint with a unique B-tree index, but a partial unique index expresses a different rule. Soft deletion commonly needs uniqueness only among live rows:

CREATE UNIQUE INDEX users_tenant_email_live_uq
    ON users (tenant_id, lower(email))
    WHERE deleted_at IS NULL;

This is not interchangeable with UNIQUE (tenant_id, email, deleted_at). PostgreSQL treats NULL values according to its uniqueness rules, and adding the deletion timestamp changes the identity being enforced. Review the exact duplicate cases with fixtures rather than inferring behavior from the column list.

Nullability is a business decision

Set a column to NOT NULL only when the domain requires a value for every legitimate row and every write path can supply it. Screen design is weak evidence. A required field in the current form says nothing about imports, drafts, system generated rows, or historical records.

Review four states separately: the source omitted the field, the source explicitly sent null, the source sent an empty value, and the source supplied a meaningful value. JSON APIs, forms, CSV imports, and PostgreSQL can handle these states differently. If the application collapses all four before insertion, the schema review should expose that decision rather than pretending the database resolved it.

Defaults deserve the same attention. A default supplies a value when an INSERT omits the column; it does not repair an explicit NULL, and it does not prove that the value is true. country_code DEFAULT 'US' is dangerous if an unknown country is possible. The row now contains a confident lie that reports and compliance logic may trust.

A common generated migration adds a required column in one statement:

ALTER TABLE customers
    ADD COLUMN account_type text NOT NULL DEFAULT 'standard';

The statement may execute, but every historical customer becomes standard without evidence. A safer sequence adds the nullable column, derives values from known data, measures the unresolved rows, blocks new omissions in application writes, and only then adds NOT NULL if the domain supports it. If unknown remains legitimate, retain NULL and define how queries and interfaces display it.

PostgreSQL provides a useful separation for some constraints. A CHECK or foreign key can be added as NOT VALID, which avoids validating all existing rows during creation, and later checked with VALIDATE CONSTRAINT. The manual documents this as a way to postpone the initial table scan. It is not permission to ignore old violations: new writes face enforcement, while the validation step must still pass before approval.

Before tightening nullability, run a distribution query that shows the actual categories:

SELECT
    count(*) AS total,
    count(*) FILTER (WHERE account_type IS NULL) AS nulls,
    count(*) FILTER (WHERE account_type = '') AS empty_strings,
    count(*) FILTER (WHERE account_type NOT IN
        ('standard', 'partner', 'internal')) AS unexpected
FROM customers;

An AI generated default can make this query look clean after the migration. Run it before the backfill as well, and preserve that result. Otherwise you lose the evidence needed to distinguish derived values from invented ones.

Indexes should answer observed access patterns

Approve an index when it supports a known query, enforces a stated uniqueness rule, or enables an operational requirement. Indexing every identifier shaped column wastes storage and write work, while missing one important composite index can turn a routine list page into a growing scan.

Start with the queries the generated application actually issues. Record the filter columns, tenant boundary, join columns, ordering, and expected result size. For a recent orders page, this query shape matters more than the table diagram:

SELECT id, order_number, status, created_at
FROM orders
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

An index on tenant_id alone may still inspect many tenant rows and sort them. An index on (tenant_id, status, created_at DESC) matches this access pattern more closely. Column order is not a popularity contest; it follows equality conditions, range conditions, ordering, and selectivity in the actual query.

Run EXPLAIN (ANALYZE, BUFFERS) against representative data, but do not treat one small fixture as performance proof. PostgreSQL may correctly prefer a sequential scan on a tiny table. Verification should confirm that the intended index exists and that a production sized rehearsal gives the planner a realistic choice. Never disable sequential scans to manufacture an index scan for approval.

Foreign keys create another routine surprise: PostgreSQL indexes the referenced primary or unique columns, but it does not automatically create an index on the referencing child columns. Deleting or updating a parent may therefore scan the child table to check references. Joins from child to parent may also need that child index. Inspect each relationship based on expected reads and parent changes.

Reject duplicate and unused indexes in the initial proposal. (tenant_id, status) may be redundant when a suitable (tenant_id, status, created_at) index already exists, though workload details can change that judgment. Compare definitions, not names. AI builders often create one index per feature and fail to notice that several features requested the same leading columns.

For existing busy databases, remember that CREATE INDEX CONCURRENTLY cannot run inside a transaction block, takes more work, and can leave an invalid index after failure. The PostgreSQL manual spells out those operational differences. A migration framework that wraps every migration in a transaction needs an explicit exception and cleanup procedure rather than a hopeful keyword substitution.

Foreign keys need ownership and deletion rules

A foreign key is correct only after the team decides whether the relationship expresses ownership, reference, optional context, or historical attribution. Similar looking columns can require opposite deletion behavior.

Consider projects.owner_user_id, invoices.customer_id, and audit_events.actor_user_id. A project may transfer ownership. An invoice may have to survive customer account closure. An audit event may preserve the actor's former identifier even after identity data is removed. Applying ON DELETE CASCADE to all three because they reference users would encode a destructive fiction.

Use CASCADE when the child has no meaning without the parent and deletion of the parent genuinely means deletion of the whole aggregate. Order line items often fit. Payment records, issued documents, imports, logs, and moderation evidence often do not. For those, rejection, archival, controlled anonymization, or a nullable reference paired with retained snapshot fields may fit better.

SET NULL also requires a semantic review. It preserves the child row but erases the direct relationship. If staff later need to explain which account created a report, a null reference may be insufficient. Retaining a nonidentifying historical token or snapshot can preserve accountability without retaining all personal data, but the exact retention choice belongs to the product's policy, not an AI guess.

Check cardinality in both directions. A builder can model one to one by placing a foreign key without a unique constraint, silently allowing many child rows. It can also enforce uniqueness where history needs several versions. Write fixture cases for a parent with zero, one, and several children, then state which inserts should pass.

Deferrable constraints need a specific reason. They can help when a transaction must temporarily violate reference order or update mutually dependent rows, but defaulting every foreign key to deferred moves errors to commit and makes failures harder to locate. Keep immediate enforcement unless a real transaction sequence requires postponement.

Inspect the catalog after applying the migration to the rehearsal database:

SELECT
    conname,
    contype,
    convalidated,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
ORDER BY conname;

Representative output has a shape like this:

       conname        | contype | convalidated | definition
----------------------+---------+--------------+---------------------------------------
 orders_pkey          | p       | t            | PRIMARY KEY (id)
 orders_customer_fk   | f       | t            | FOREIGN KEY (customer_id) REFERENCES customers(id)
 orders_total_check   | c       | t            | CHECK ((total_cents >= 0))

Compare the returned definitions with the approved ownership rules. Migration success alone does not reveal a missing action, unexpected deferral, or unvalidated constraint.

Destructive changes hide inside reasonable SQL

Review the migration as a data transformation, because apparently tidy DDL can discard meaning without using an obvious DROP TABLE. Search for direct destruction first, then inspect casts, backfills, rewrites, renames, and constraint replacement.

A rename and a drop are operationally different even when the final schema looks identical. If surname becomes family_name, a rename preserves data and dependencies more faithfully. Dropping the old column and adding the new one yields the same diagram but empties every value. Generated migrations often infer final state without understanding continuity.

Type changes need sample conversions and rejection cases. Changing text identifiers to integers can remove leading zeroes or reject mixed identifiers. Reducing numeric precision can round values. Converting timestamps requires a stated time zone assumption. Test the actual USING expression with minimum, maximum, null, malformed, and historically odd values before altering the column.

Treat these operations as requiring written justification: dropping a table or column, changing a type through a lossy cast, replacing a populated column, adding CASCADE, setting NOT NULL after a generated backfill, and rebuilding uniqueness with different columns. Also inspect raw SQL embedded in generated functions or migration callbacks. Text search is a starting filter, not the whole review.

One failure pattern appears repeatedly. The known data contains contacts with optional companies, but the sample screen shows only business contacts. The builder makes contacts.company_id NOT NULL and inserts a generated company named Unknown for unmatched rows. The migration passes, counts reconcile, and every foreign key validates. The data is still wrong: individual contacts now appear to belong to a company, reports group unrelated people together, and deleting the placeholder may cascade into real contacts.

The repair is not another default. Restore the source state, make the relationship nullable, migrate only evidence backed matches, and add an assertion that the unmatched set equals the known individual contacts. This is why semantic fixtures must record expected relationships, not just expected row counts.

Schema diff tools help, but I argue against approving from a diff alone. The recommendation is popular because a diff is compact and easy to review. It is wrong as the sole gate because it shows structural change, not the provenance of backfilled values, transaction boundaries, lock behavior, or postmigration truth.

A rehearsal must prove results and failure behavior

Run the complete migration on a disposable restoration of the known dataset, then test both the intended result and an interrupted or rejected path. A fresh empty database is useful for catching ordering errors, but it cannot expose lossy conversion, invalid historical rows, or slow validation.

Use this rehearsal sequence as a release artifact:

1. Restore the prechange dataset into an isolated database and record row counts plus semantic assertions.
2. Capture the current schema, apply the exact migration artifact, and save all output with timing and transaction boundaries.
3. Run catalog checks, mapping assertions, constraint rejection tests, and representative application queries.
4. Compare important values with the recorded expectations, including unmatched and null categories.
5. Exercise the documented recovery method, then rerun the premigration assertions against the recovered database.

Capture a schema only dump before and after:

pg_dump --schema-only --no-owner --no-privileges \
  --dbname "$DATABASE_URL" > schema.sql

Review tables, sequences, indexes, constraints, functions, triggers, extensions, and privileges relevant to the application. An ORM model diff may omit database objects that the application does not model, especially triggers, expression indexes, partial indexes, and manually installed functions.

Add negative tests that prove constraints reject bad states. A test transaction can attempt an invalid insert and roll back regardless of outcome:

BEGIN;

INSERT INTO order_items (order_id, quantity, unit_price_cents)
VALUES (1001, 0, 2500);

ROLLBACK;

The expected output should name the violated constraint, for example:

ERROR:  new row for relation "order_items" violates check constraint "order_items_quantity_check"
DETAIL:  Failing row contains (..., 0, 2500, ...).

Do not compare the entire error text across every environment because details can vary. Assert the SQLSTATE or constraint identity in automated tests, and retain readable output for the reviewer.

Measure locks and duration on a dataset large enough to resemble the intended deployment. An operation that finishes instantly on fifty rows can block writes when it validates millions. For an initial empty production database, the immediate risk is lower, but rehearsal still tests imported seed data and creates a baseline for later changes.

Recovery needs more than a reverse migration

Recovery is credible only when it restores data and application compatibility within the time the service can tolerate. A reverse migration that recreates dropped columns does not recover their former values.

Choose the recovery unit before execution. For an empty initial database, dropping and recreating the database may be acceptable if no user writes have begun. Once real writes exist, recovery may require a database snapshot, a logical backup, retained old columns, or a forward repair. The correct method depends on how much new data can arrive during and after migration.

Test restore commands and credentials before relying on them. A backup that exists but cannot be restored by the deployment operator is not a recovery plan. Restore into a separate database, verify ownership and extensions, then run the same known assertions used before migration.

Snapshots and transaction rollback solve different failures. A transaction can undo statements when a migration fails before commit, provided every operation participates in that transaction. A snapshot can return the whole database to an earlier state, but doing so may discard legitimate writes made after the snapshot. Neither mechanism automatically reconciles those writes.

Prefer additive changes when uncertainty remains. Add the new column or table, copy data with measurable rules, run both code paths during a controlled period if needed, and remove the old structure only after verification. This expand and contract approach costs extra work, but it preserves evidence. Keeping a renamed old column for one release is often cheaper than reconstructing it from logs.

Write recovery triggers in advance. Examples include any failed semantic assertion, unexpected unmatched records, an invalid constraint, a migration exceeding its approved lock window, or application errors caused by version mismatch. The operator should not invent the decision while users are waiting.

Record the point after which restoring the old database also requires restoring the old application. A new application may depend on a new column, while an old application may reject a new enum value or write the old shape. Database and application recovery must use compatible versions.

Approval requires evidence, not confidence

Approve the first migration only when another person can reproduce why it is safe from the stored artifacts. Confidence from a clean code review or a polished generated interface does not survive the first unexplained data discrepancy.

The approval record should contain the inferred assumptions, table mapping matrix, known dataset identity, schema diff, exact migration, validation queries and results, rejected input tests, index reasoning, destructive operation justifications, and tested recovery procedure. Name the reviewer and preserve unresolved decisions as blockers rather than burying them in chat history.

When an application is generated in Koder.ai, use planning mode to write down these schema decisions before allowing the migration, export the source for review, and treat snapshots and rollback as recovery tools that still need a rehearsal against the known dataset.

Do not let the builder approve its own inference by regenerating code until tests pass. That loop can make the application conform to a mistaken schema instead of correcting the model. A human must decide whether the database matches the domain, especially around identity, deletion, retention, and unknown values.

The final approval query should be boring. Every known fact maps to one expected result, every constraint rejects the intended counterexample, every destructive operation has a reason, and the restore reproduces the premigration assertions. If the evidence needs a persuasive explanation to excuse a mismatch, stop the migration. PostgreSQL will enforce the schema precisely, including the parts the builder guessed wrong.