Group
  • Database

    Add database-level validation and constraints

    Adds NOT NULL, CHECK constraints, and correct column types so bad data can't be written no matter what the app does.

    • Improve quality
    • Plan & validate
    • Intermediate
    • General

Free Prompt

Harden my schema with database-level validation. Go through each table or collection and add the constraints that make bad data unwritable. Apply: NOT NULL on every field that should never be empty (emails, foreign keys, amounts, statuses); CHECK constraints for invariants (amounts non-negative, end_date after start_date, status within an allowed set of values); correct column types (money as integer cents or decimal, never float; timestamps as real timestamp types, not strings; booleans not 0/1 text); and sensible defaults (created_at defaults, empty arrays over nulls where the app expects a list). In a schemaless database, add the equivalent with schema validation rules the database enforces (MongoDB has $jsonSchema validators) rather than only validating in app code. Before adding constraints, scan existing data for violations and report them; constraints will fail against dirty data. Fix the type mistakes carefully: changing a column type on live data needs a migration plan, which you should write for me rather than winging it. Deliver: constraints added per table, the data-quality violations found in existing rows, the type-fix migrations, and verification: attempt to write invalid rows directly (null email, negative amount, bad status) and confirm the database refuses them.

What This Does / How This Helps

Adds constraints to the schema itself so invalid data gets refused at write time, no matter which code path or bug tries to write it. Application validation has holes: a new endpoint forgets to check, an import script skips it, a quick admin query writes by hand. Database constraints are the floor that holds regardless. They also turn data bugs into loud write-time errors instead of quiet corruption you discover three months later in a report. The type audit matters as much as the constraints: money stored as float drifts by pennies, and dates stored as strings can't be compared reliably. You get the violations report on existing data before anything is enforced.

Want to skip doing this by hand?

Fortivibe audits your app for all of the areas these prompts cover (and more).

See What We Check

Related Prompts