PostgreSQL constraints first: stop AI edge-case bugs early

Why AI-written code still breaks on messy real data

AI-written code often looks correct because it handles the happy path. Real apps fail in the messy middle: a form submits an empty string instead of null, a background job retries and creates the same record twice, or a delete removes a parent row and leaves children behind. These aren’t exotic bugs. They show up as blank required fields, duplicate “unique” values, and orphan rows that point to nothing.

They also slip through code review and basic tests for a simple reason: reviewers read intent, not every edge case. Tests usually cover a few typical examples, not weeks of real user behavior, imports from CSVs, flaky network retries, or concurrent requests. If an assistant generated the code, it can miss small but critical checks like trimming whitespace, validating ranges, or guarding against race conditions.

“Constraints first, code second” means you put non-negotiable rules in the database so bad data can’t be stored, no matter which code path tries to write it. Your app should still validate input for better error messages, but the database enforces the truth. That’s where PostgreSQL constraints shine: they protect you from entire categories of mistakes.

A quick example: imagine a small CRM. An AI-generated import script creates contacts. One row has an email of "" (empty), two rows repeat the same email with different casing, and one contact references an account_id that doesn’t exist because the account was deleted in another process. Without constraints, all of that can land in production and break reports later.

With the right database rules, those writes fail immediately, close to the source. Required fields can’t be missing, duplicates can’t sneak in during retries, relationships can’t point to deleted or nonexistent records, and values can’t fall outside allowed ranges.

Constraints don’t prevent every bug. They won’t fix a confusing UI, a wrong discount calculation, or a slow query. But they do stop bad data from quietly accumulating, which is often where “AI-generated edge-case bugs” become expensive.

Why the database should be your last line of defense

Your app is rarely one codebase talking to one user. A typical product has a web UI, a mobile app, admin screens, background jobs, imports from CSV, and sometimes third-party integrations. Each path can create or change data. If every path has to remember the same rules, one will forget.

The database is the one place they all share. When you treat it as the final gatekeeper, the rules apply to everything automatically. PostgreSQL constraints turn “we assume this is always true” into “this must be true, or the write fails.”

AI-generated code makes this even more important. A model might add form validation in a React UI but miss a corner case in a background job. Or it might handle happy-path data well, then break when a real customer enters something unexpected. Constraints catch issues at the moment bad data tries to enter, not weeks later when you’re debugging strange reports.

When you skip constraints, bad data is often silent. The save succeeds, the app moves on, and the problem shows up later as a support ticket, a billing mismatch, or a dashboard nobody trusts. Cleanup is expensive because you’re fixing history, not one request.

Bad data usually sneaks in through everyday situations: a new client app version sends a field as empty instead of missing, a retry creates duplicates, an admin edit bypasses UI checks, an import file has inconsistent formatting, or two users update related records at the same time.

A useful mental model: accept data only if it’s valid at the boundary. In practice, that boundary should include the database, because the database sees all writes.

NOT NULL: stop missing required data early

NOT NULL is the simplest PostgreSQL constraint, and it prevents a surprisingly large class of bugs. If a value must exist for the row to make sense, make the database enforce it.

NOT NULL is usually right for identifiers, required names, and timestamps. If you can’t create a valid record without it, don’t allow it to be empty. In a small CRM, a lead without an owner or a created time isn’t a “partial lead.” It’s broken data that will cause weird behavior later.

NULL sneaks in more often with AI-generated code because it’s easy to create “optional” paths without noticing. A form field might be optional in the UI, an API might accept a missing key, and one branch of a create function might skip assigning a value. Everything still compiles and the happy-path test passes. Then real users import a CSV with empty cells, or a mobile client sends a different payload, and NULL lands in the database.

A good pattern is to combine NOT NULL with a sensible default for fields the system owns:

• created_at TIMESTAMP NOT NULL DEFAULT now()
• status TEXT NOT NULL DEFAULT 'new'
• is_active BOOLEAN NOT NULL DEFAULT true

Defaults aren’t always a win. Don’t default user-provided fields like email or company_name just to satisfy NOT NULL. An empty string isn’t “more valid” than NULL. It just hides the problem.

When you’re unsure, decide whether the value is truly unknown, or whether it represents a different state. If “not provided yet” is meaningful, consider a separate state column instead of allowing NULL everywhere. For example, keep phone nullable, but add phone_status like missing, requested, or verified. That keeps the meaning consistent across your code.

CHECK: encode business rules close to the data

A CHECK constraint is a promise your table makes: every row must satisfy a rule, every time. It’s one of the easiest ways to prevent edge cases from quietly creating records that look fine in code but don’t make sense in real life.

CHECK constraints work best for rules that depend only on values in the same row: numeric ranges, allowed values, and simple relationships between columns.

-- 1) Totals should never be negative
ALTER TABLE invoices
  ADD CONSTRAINT invoices_total_nonnegative
  CHECK (total_cents >= 0);

-- 2) Enum-like allowed values without adding a custom type
ALTER TABLE tickets
  ADD CONSTRAINT tickets_status_allowed
  CHECK (status IN ('new', 'open', 'waiting', 'closed'));

-- 3) Date order rules
ALTER TABLE subscriptions
  ADD CONSTRAINT subscriptions_date_order
  CHECK (end_date IS NULL OR end_date >= start_date);

A good CHECK is readable at a glance. Treat it like documentation for your data. Prefer short expressions, clear constraint names, and predictable patterns.

CHECK isn’t the right tool for everything. If a rule needs to look up other rows, aggregate data, or compare across tables (for example, “an account can’t exceed its plan limit”), keep that logic in application code, triggers, or a controlled background job.

UNIQUE: prevent duplicates you will regret later

A UNIQUE rule is simple: the database refuses to store two rows that have the same value in the constrained column (or the same combination of values across multiple columns). This wipes out a whole class of bugs where a “create” path runs twice, a retry happens, or two users submit the same thing at the same time.

UNIQUE guarantees no duplicates for the exact values you define. It does not guarantee that the value is present (NOT NULL), that it follows a format (CHECK), or that it matches your idea of equality (case, spaces, punctuation) unless you define it.

Common places you usually want uniqueness include email on a user table, external_id from another system, or a name that must be unique within an account like (account_id, name).

One gotcha: NULL and UNIQUE. In PostgreSQL, NULL is treated as “unknown,” so multiple NULL values are allowed under a UNIQUE constraint. If you mean “the value must exist and must be unique,” combine UNIQUE with NOT NULL.

A practical pattern for user-facing identifiers is case-insensitive uniqueness. People will type “[email protected]” and later “[email protected]” and expect them to be the same.

-- Case-insensitive unique email
CREATE UNIQUE INDEX users_email_unique_ci
ON users (lower(email));

-- Unique contact name per account
ALTER TABLE contacts
ADD CONSTRAINT contacts_account_name_unique UNIQUE (account_id, name);

Define what “duplicate” means for your users (case, whitespace, per-account vs global), then encode it once so every code path follows the same rule.

FOREIGN KEY: keep relationships consistent

A FOREIGN KEY says, “this row must point to a real row over there.” Without it, code can quietly create orphan records that look valid in isolation but break the app later. For example: a note that references a customer that was deleted, or an invoice that points to a user ID that never existed.

Foreign keys matter most when two actions happen close together: a delete and a create, a retry after a timeout, or a background job running with stale data. The database is better at enforcing consistency than every app path remembering to check.

Choose the right ON DELETE behavior

The ON DELETE option should match the real-world meaning of the relationship. Ask: “If the parent disappears, should the child still exist?”

• RESTRICT (or NO ACTION): block deleting the parent if children exist.
• CASCADE: deleting the parent deletes the children too.
• SET NULL: keep the child but remove the link.

Be careful with CASCADE. It can be correct, but it can also erase more than you expected when a bug or admin action deletes a parent record.

Multi-tenant schemas: model ownership explicitly

In multi-tenant apps, foreign keys aren’t just about correctness. They also prevent cross-account leakage. A common pattern is to include account_id on every tenant-owned table and tie relationships through it.

CREATE TABLE contacts (
  account_id bigint NOT NULL,
  id bigint GENERATED ALWAYS AS IDENTITY,
  PRIMARY KEY (account_id, id)
);

CREATE TABLE notes (
  account_id bigint NOT NULL,
  id bigint GENERATED ALWAYS AS IDENTITY,
  contact_id bigint NOT NULL,
  body text NOT NULL,
  PRIMARY KEY (account_id, id),
  FOREIGN KEY (account_id, contact_id)
    REFERENCES contacts (account_id, id)
    ON DELETE RESTRICT
);

This enforces “who owns what” in the schema: a note can’t point to a contact in a different account, even if the app code (or an LLM-generated query) tries.

Step by step: add constraints without breaking production

Start by writing a short list of invariants: facts that must always be true. Keep them plain. “Every contact needs an email.” “A status must be one of a few allowed values.” “An invoice must belong to a real customer.” These are the rules you want the database to enforce every time.

Roll changes out in small migrations so production doesn’t get surprised:

• Add the new column or rule in a non-breaking way first.
• Backfill existing rows in batches.
• Clean up bad data (dedupe, fix invalid values) or quarantine it for review.
• Enforce the rule (NOT NULL, UNIQUE, CHECK, FOREIGN KEY).
• Tighten the app behavior so errors are handled clearly.

The messy part is existing bad data. Plan for it. For duplicates, pick a winner row, merge the rest, and keep a small audit note. For missing required fields, choose a safe default only if it’s truly safe; otherwise quarantine. For broken relationships, either reassign the child rows to the correct parent or remove the bad rows.

After each migration, validate with a few writes that should fail: insert a row with a missing required value, insert a duplicate key, insert an out-of-range value, and reference a missing parent row. Failed writes are useful signals. They show you where the app was silently relying on “best effort” behavior.

A realistic example: a small CRM that stays clean

Picture a small CRM: accounts (each customer of your SaaS), companies they work with, contacts at those companies, and deals tied to a company.

This is exactly the kind of app people generate quickly with a chat tool. It looks fine in demos, but real data gets messy fast. Two bugs tend to appear early: duplicate contacts (the same email entered twice in slightly different ways), and deals created without a company because one code path forgot to set company_id. Another classic is a negative deal value after a refactor or a parsing mistake.

The fix isn’t more if-statements. It’s a few well-chosen constraints that make bad data impossible to store.

The constraints that keep the CRM clean

-- Contacts: prevent duplicates per account
ALTER TABLE contacts
  ADD CONSTRAINT contacts_account_email_uniq UNIQUE (account_id, email);

-- Deals: require a company and keep the relationship valid
ALTER TABLE deals
  ALTER COLUMN company_id SET NOT NULL,
  ADD CONSTRAINT deals_company_fk
    FOREIGN KEY (company_id) REFERENCES companies(id);

-- Deals: deal value cannot be negative
ALTER TABLE deals
  ADD CONSTRAINT deals_value_nonneg CHECK (deal_value >= 0);

-- A few obvious required fields
ALTER TABLE companies
  ALTER COLUMN name SET NOT NULL;
ALTER TABLE contacts
  ALTER COLUMN email SET NOT NULL;

This isn’t about being strict for the sake of it. You’re turning vague expectations into rules the database can enforce every time, no matter which part of the app writes data.

What changes in the app after that

Once these constraints are in place, the app gets simpler. You can remove a lot of defensive checks that try to detect duplicates after the fact. Failures become clear and actionable (for example, “email already exists for this account” instead of weird downstream behavior). And when a generated API route forgets a field or mishandles a value, the write fails immediately instead of silently corrupting the database.

Common mistakes that make constraints painful

Constraints work best when they match how the business actually works. Most pain comes from adding rules that feel “safe” in the moment but turn into surprises later.

A common foot-gun is using ON DELETE CASCADE everywhere. It looks tidy until someone deletes a parent row and the database removes half the system. Cascades can be right for truly owned data (like draft line items that should never exist alone), but they’re risky for records people consider important (customers, invoices, tickets). If you’re not sure, prefer RESTRICT and handle deletes intentionally.

Another problem is writing CHECK rules that are too narrow. “Status must be ‘new’, ‘won’, or ‘lost’” sounds fine until you need “paused” or “archived.” A good CHECK constraint describes a stable truth, not a temporary UI choice. “amount >= 0” ages well. “country in (...)” often doesn’t.

A few issues show up repeatedly when teams add constraints after generated code is already shipping:

• Treating CASCADE as a cleanup tool, then deleting more data than intended.
• Making CHECK rules so narrow they block valid future cases.
• Assuming UNIQUE stops duplicates when NULL is involved.
• Changing rules without a plan to fix rows that already break them.

On performance: PostgreSQL automatically creates an index for UNIQUE, but foreign keys don’t automatically index the referencing column. Without that index, updates and deletes on the parent can get slow because Postgres has to scan the child table to check references.

Before tightening a rule, find existing rows that would fail it, decide whether to fix or quarantine them, and roll out the change in steps.

Quick checklist and next steps for your next build

Before you ship, take five minutes per table and write down what must always be true. If you can say it in plain English, you can usually enforce it with a constraint.

Ask these questions for each table:

• What should never be NULL?
• What should never be duplicated?
• What should never be negative or out of range?
• What should never exist without a parent (no orphan rows)?
• What should always follow a simple rule (allowed states, start before end)?

If you’re using a chat-driven build tool, treat those invariants as acceptance criteria for the data, not optional notes. For example: “A deal amount must be non-negative,” “A contact email is unique per workspace,” “A task must reference a real contact.” The more explicit the rules are, the less room there is for accidental edge cases.

Koder.ai (koder.ai) includes features like planning mode, snapshots and rollback, and source code export, which can make it easier to iterate on schema changes safely while you tighten constraints over time.

A simple rollout pattern that works in real teams: pick one high-value table (users, orders, invoices, contacts), add 1-2 constraints that prevent the worst failures (often NOT NULL and UNIQUE), fix the writes that fail, then repeat. Tightening the rules over time beats one big risky migration.