Database Schema Health Check: 7 Things to Audit Before You Ship

The short version

Most schema problems are boring and preventable: a table with no primary key, a foreign key nobody indexed, userId in one table and user_id in the next. A 60-second health check catches them before they become production incidents.

dbdiagramr's free schema analyzer grades your PostgreSQL schema 0-100 against seven checks and gives you a one-line fix for each issue. Paste SQL, get the report. Nothing leaves your browser.

The 7 checks, in priority order

1. Every table needs a primary key

No primary key means no reliable row identity, painful replication, and ORMs that misbehave. This is the one error-level check — fix it before anything else.

-- bad: no way to address a single row
CREATE TABLE events (
  name TEXT,
  happened_at TIMESTAMPTZ
);

-- good
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  happened_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Index your foreign keys

PostgreSQL does not automatically index foreign key columns. Without an index, joins against the child table and deletes on the parent table degrade into sequential scans. The fix is one line per FK:

CREATE INDEX idx_posts_user_id ON posts (user_id);

3. Make foreign keys NOT NULL unless optional

A nullable user_id says "this row may belong to nobody." Sometimes that is true (a draft, an anonymous session). Usually it is an accident that later produces orphan-handling code everywhere.

4. Don't name a non-PK column "id"

Every reader assumes id is the primary key. A table with both a UUID pk and an integer id guarantees confusion in every query written against it.

5. One naming convention

snake_case is the Postgres norm. A schema that mixes userId and user_id forces every query author to guess. Pick one and rename.

6. Watch wide tables

Past ~30 columns, a table is usually two tables wearing a trench coat. Split rarely-used or repeated column groups into a related table.

7. Add created_at

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() costs nothing and pays for itself the first time you debug a data issue at 2am.

How to run the check

1. Export your schema: pg_dump --schema-only yourdb > schema.sql
2. Paste it into the schema analyzer
3. Fix errors first, then warnings, then notes
4. Copy the Markdown report into your PR or design doc
5. Visualize the clean schema with dbdiagramr to confirm the relationships look right

FAQ

How often should I audit my schema?
On every migration that adds tables or foreign keys, plus a full pass quarterly. The automated check takes a minute.

Does the analyzer see my data?
No. It reads table and column definitions only, and it runs entirely in your browser — your SQL is never uploaded.

What score should I aim for?
90+ (grade A) for production schemas. Anything below 60 almost always means missing primary keys — fix those first.