Let the DB Reject Bad Data

"We had validation, yet an empty value still ended up in the production database." Trace it back and you usually find a path that never went through the app's validation. This article is about the design phase: how to split responsibility between application validation and database constraints before implementation begins.

Why validation alone is not enough

Application validation only protects input that passes through that code. These paths bypass it:

• Concurrent requests: two "create if missing" calls run at once, both existence checks pass, and a duplicate slips in (a check-then-insert race)
• Scripts and consoles: data migrations, update_all, and save(validate: false) skip validation
• Other apps and batch jobs: another service touching the same database knows nothing about your validation

A responsibility table: who owns what

The rule of thumb: the app explains problems clearly to the user, and the database refuses bad values no matter what. The database is the last line of defense.

Example: the app rejected it, but bad data got in anyway

Suppose a coupon may be used once per user. If the app checks "was it used?" before creating the order, a double click or a retry can send two requests at once. Both see "unused", and two orders are created.

With constraints in the database, the second request is rejected every time.

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  status      TEXT NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending','paid','shipped','canceled')),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  coupon_code TEXT
);

-- A user cannot use the same coupon twice (NULL is ignored)
CREATE UNIQUE INDEX uniq_orders_user_coupon
  ON orders (user_id, coupon_code)
  WHERE coupon_code IS NOT NULL;

The app catches the violation and turns it into a user-facing message. A Rails example:

def redeem!
  Order.create!(user: user, coupon_code: code)
rescue ActiveRecord::RecordNotUnique
  raise CouponAlreadyUsed, "This coupon has already been used"
end

The point is not to delete the validation but to keep both. Validation gives friendly errors; the constraint catches whatever slips past it.

Adding constraints to an existing table

1. Count violations: SELECT count(*) FROM orders WHERE quantity <= 0;
2. Fix the data: decide with the business side whether to correct or delete it
3. Add with short locks: in PostgreSQL, add as NOT VALID, then validate
4. Handle errors first: a violation should not surface as a bare 500

ALTER TABLE orders
  ADD CONSTRAINT quantity_positive CHECK (quantity > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT quantity_positive;

-- Build UNIQUE indexes without blocking writes
CREATE UNIQUE INDEX CONCURRENTLY uniq_orders_user_coupon ...;

A checklist for schema design reviews

• Are required fields NOT NULL (and are NULL and empty string distinguished)?
• Does every combination that must be unique have a UNIQUE index?
• Do status and type columns have a CHECK or an enum?
• Do quantities, amounts, and periods have CHECKs (at least 0, start <= end)?
• Is the message and HTTP status for a violation decided?

Putting it into practice with Bugoon

To a reporter, bad-data bugs just look like "the screen is wrong". With the Bugoon widget embedded in your site, a reporter can annotate a screenshot and send it, and the operation steps, console logs, and network errors are attached automatically. If a constraint violation made the API return an error, that request is preserved as evidence.

Reports become GitHub Issues and can be tracked on the kanban board. Hand them to Claude Code or Cursor through the MCP server, and they can investigate the affected data and draft a fix that adds the missing constraint. When you find bad data, don't stop at cleaning it up. Ask which constraint would have prevented it, and you stop the same bug from returning at the design level.