Database Design with Claude Code: A Practical Review Workflow

Database design is not just naming tables. You decide which facts belong together, which columns must never be empty, which values must be unique, how rows relate to each other, and whether the application can still query the data quickly six months later. A weak schema can look fine during the first demo and then fail through duplicate accounts, broken billing state, slow dashboards, or a migration that cannot be rolled back.

Claude Code is useful here, but not as an unquestioned source of truth. Treat it as a reviewer and schema design assistant. Let it draft tables, list risks, write test SQL, and challenge your assumptions. Then verify the result against official documentation, real queries, production constraints, and human ownership. The goal is not to let AI “design the database”; the goal is to make the design review more explicit and harder to skip.

For adjacent ClaudeCodeLab workflows, pair this article with database migration automation and Prisma ORM with Claude Code. For official references, keep the PostgreSQL constraints documentation, PostgreSQL indexes documentation, and OWASP Top 10 open while reviewing anything that stores sensitive data.

The Basic Pieces

A beginner-friendly way to read a schema is to map each database term to a real product question.

Term	Plain meaning	Design question
Table	A collection of one kind of record	Should users, teams, and projects be separate concepts?
Column	One field on a record	Can email, status, or display_name ever be empty?
Primary key	The stable identity of one row	Is id enough, or does a join table need a composite key?
Foreign key	A promise that a row points to a real parent row	Can a project exist without a team?
Unique constraint	A rule that prevents duplicate business values	Must email, team slug, or billing customer ID be unique?
Index	A lookup structure for common reads	Which WHERE, JOIN, and ORDER BY clauses happen often?
Migration	The repeatable script that changes the schema	Can this change run on existing production data?
Rollback	The way back when a release is wrong	Does the rollback restore data, or only the shape of tables?

Claude Code can explain these concepts, but the stronger use is review. Ask it to find missing constraints, unsafe nullable fields, unnecessary indexes, destructive migrations, and queries that will cause N+1 behavior. Do not ask for a magical final schema without describing the product rules.

claude -p "Read docs/database/schema.sql and migrations/*.sql.
Act as a database design reviewer, not as the owner of truth.

Check for:
- Missing primary keys, foreign keys, unique constraints, and NOT NULL rules
- status or role columns that are too loose
- Indexes needed for WHERE, JOIN, and ORDER BY patterns
- Destructive migrations and rollback gaps
- PII leaking into audit logs or search/reporting tables
- API shapes that may trigger N+1 queries

Return findings first. Include why it matters, how to verify it, and only then suggest a minimal patch."

Four Concrete Use Cases

The running example covers four practical cases because real database problems rarely live in one table.

The first case is a SaaS user, team, and project schema. Users can belong to multiple teams, and each team owns projects. A project slug should be unique inside the team, not necessarily across the whole product. That distinction is exactly where a generic AI answer often needs review.

The second case is an audit log. You want to know who changed what and when, but audit tables easily become a dumping ground for personally identifiable information. Logging an email address inside a JSON metadata blob may feel convenient today and become a retention, access-control, or deletion problem later.

The third case is billing and subscriptions. Provider customer IDs must not collide, and a team should not have two active subscriptions. The schema should allow canceled history while still preventing conflicting current billing state.

The fourth case is a search or reporting table. Sometimes it is cleaner to create a read-optimized table instead of joining every operational table for every search. The tradeoff is that you must design refresh timing, indexes, and verification so search results do not silently become stale.

Runnable PostgreSQL DDL

The following SQL is designed for a local PostgreSQL database. It recreates only a dedicated demo schema, so it is easy to paste into psql without touching other schemas.

DROP SCHEMA IF EXISTS claude_db_design_demo CASCADE;
CREATE SCHEMA claude_db_design_demo;
SET search_path = claude_db_design_demo;

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT users_email_unique UNIQUE (email),
  CONSTRAINT users_email_has_at CHECK (position('@' in email) > 1)
);

CREATE TABLE teams (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT teams_slug_unique UNIQUE (slug)
);

CREATE TABLE team_members (
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (team_id, user_id)
);

CREATE TABLE projects (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
  created_by BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT projects_team_slug_unique UNIQUE (team_id, slug)
);

CREATE TABLE audit_logs (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  actor_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT audit_action_not_blank CHECK (length(action) > 0),
  CONSTRAINT audit_metadata_no_email CHECK (
    metadata::text !~* '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}'
  )
);

CREATE TABLE subscriptions (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('stripe', 'manual')),
  provider_customer_id TEXT NOT NULL,
  plan_code TEXT NOT NULL CHECK (plan_code IN ('free', 'pro', 'business')),
  status TEXT NOT NULL CHECK (status IN ('trialing', 'active', 'past_due', 'canceled')),
  current_period_end DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT subscriptions_provider_customer_unique UNIQUE (provider, provider_customer_id)
);

CREATE TABLE project_search_documents (
  project_id BIGINT PRIMARY KEY REFERENCES projects(id) ON DELETE CASCADE,
  team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  search_text TEXT NOT NULL,
  refreshed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX subscriptions_one_open_per_team
  ON subscriptions(team_id)
  WHERE status IN ('trialing', 'active', 'past_due');

CREATE INDEX idx_team_members_user_id ON team_members(user_id);
CREATE INDEX idx_projects_team_status_created ON projects(team_id, status, created_at DESC);
CREATE INDEX idx_audit_logs_team_created ON audit_logs(team_id, created_at DESC);
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_project_search_documents_search
  ON project_search_documents USING GIN (to_tsvector('simple', search_text));

Notice the difference between UNIQUE (team_id, slug) and a global slug rule. The business rule is “one slug per team”, so the constraint follows the product model. The partial unique index on subscriptions allows old canceled rows while blocking two open billing states for the same team.

Seed Data and Constraint Tests

Good schema review includes cases that should fail. If a duplicate email insert succeeds, the schema is not protecting the application. If audit metadata accepts an email address, the log policy is too weak for this demo.

SET search_path = claude_db_design_demo;

INSERT INTO users (email, display_name, password_hash) VALUES
  ('masa@example.com', 'Masa', '$2b$demo-hash-1'),
  ('aiko@example.com', 'Aiko', '$2b$demo-hash-2'),
  ('ren@example.com', 'Ren', '$2b$demo-hash-3');

INSERT INTO teams (name, slug) VALUES
  ('ClaudeCodeLab', 'clab'),
  ('Northstar Apps', 'northstar');

INSERT INTO team_members (team_id, user_id, role) VALUES
  (1, 1, 'owner'),
  (1, 2, 'admin'),
  (2, 3, 'owner');

INSERT INTO projects (team_id, name, slug, status, created_by) VALUES
  (1, 'Schema Review', 'schema-review', 'active', 1),
  (1, 'Billing Console', 'billing-console', 'active', 2),
  (2, 'Customer Portal', 'customer-portal', 'active', 3);

INSERT INTO audit_logs (team_id, actor_user_id, action, entity_type, entity_id, metadata) VALUES
  (1, 1, 'project.created', 'project', '1', '{"project_slug":"schema-review"}'::jsonb),
  (1, 2, 'billing.plan_changed', 'subscription', '1', '{"from":"free","to":"pro"}'::jsonb);

INSERT INTO subscriptions (team_id, provider, provider_customer_id, plan_code, status, current_period_end) VALUES
  (1, 'stripe', 'cus_demo_1', 'pro', 'active', DATE '2026-07-31'),
  (2, 'manual', 'manual_northstar_1', 'free', 'canceled', NULL);

INSERT INTO project_search_documents (project_id, team_id, title, search_text)
SELECT
  id,
  team_id,
  name,
  name || ' project notes for database design, billing, audit, and reporting.'
FROM projects;

SET search_path = claude_db_design_demo;

DO $$
BEGIN
  INSERT INTO users (email, display_name, password_hash)
  VALUES ('masa@example.com', 'Duplicate Masa', '$2b$demo-hash-x');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK duplicate email blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO projects (team_id, name, slug, created_by)
  VALUES (1, NULL, 'bad-null', 1);
EXCEPTION WHEN not_null_violation THEN
  RAISE NOTICE 'OK NULL project name blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO team_members (team_id, user_id, role)
  VALUES (999, 1, 'member');
EXCEPTION WHEN foreign_key_violation THEN
  RAISE NOTICE 'OK missing team blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO projects (team_id, name, slug, created_by)
  VALUES (1, 'Duplicate Slug', 'schema-review', 1);
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK duplicate team slug blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO audit_logs (team_id, actor_user_id, action, entity_type, entity_id, metadata)
  VALUES (1, 1, 'project.invited', 'project', '1', '{"email":"customer@example.com"}'::jsonb);
EXCEPTION WHEN check_violation THEN
  RAISE NOTICE 'OK email in audit metadata blocked: %', SQLERRM;
END $$;

DO $$
BEGIN
  INSERT INTO subscriptions (team_id, provider, provider_customer_id, plan_code, status, current_period_end)
  VALUES (1, 'stripe', 'cus_demo_2', 'business', 'active', DATE '2026-08-31');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'OK second open subscription blocked: %', SQLERRM;
END $$;

SELECT p.id, p.name, count(a.id) AS audit_events
FROM projects p
LEFT JOIN audit_logs a
  ON a.entity_type = 'project'
 AND a.entity_id = p.id::text
WHERE p.team_id = 1
GROUP BY p.id, p.name
ORDER BY audit_events DESC, p.id;

SELECT project_id, title
FROM project_search_documents
WHERE to_tsvector('simple', search_text) @@ plainto_tsquery('simple', 'billing');

EXPLAIN (COSTS OFF)
SELECT *
FROM audit_logs
WHERE team_id = 1
ORDER BY created_at DESC
LIMIT 20;

The DO blocks turn expected failures into visible notices instead of leaving the session in an aborted transaction. This is a practical pattern when you want Claude Code to generate safety checks that a teammate can paste into a local database.

Pitfalls to Ask Claude Code About

Missing unique constraints are the classic failure. Email, provider account IDs, customer IDs, and team-local slugs usually need database-level protection. Application validation is useful for user experience, but it is not enough under concurrent requests.

Nullable fields are another quiet bug source. projects.name, team_members.role, and subscriptions.status should not be nullable here because the app cannot make a sensible decision without them. A nullable deleted_at or current_period_end may be valid, but only when the empty value has a clear meaning.

Destructive migrations need extra suspicion. DROP COLUMN, type changes, adding NOT NULL to existing data, and adding defaults on huge tables can cause locks or data loss. Ask Claude Code to propose an expand-and-contract plan, then have a human owner verify it.

N+1 queries also begin at design time. If an API lists projects and then fetches audit counts one project at a time, 100 projects can become 101 queries. Sometimes the fix is a single aggregate query; sometimes it is a reporting table. Either way, the query shape belongs in the schema review.

Finally, watch PII in logs. Audit tables, search documents, and reporting tables are often retained longer and viewed by more roles than operational tables. Do not store emails, tokens, phone numbers, or addresses in metadata just because it is easy.

Migration and Rollback Checklist

Ask Claude Code to put a checklist like this into the PR. It keeps the discussion concrete and prevents a migration from being reviewed only as “SQL that runs”.

# Migration checklist: add projects.archived_at

## Design
- [ ] Why this column is needed is written in the issue or PR.
- [ ] Existing rows and backfill behavior are defined.
- [ ] NOT NULL, default value, and CHECK constraint choices are explained.
- [ ] Index impact was checked against real WHERE / ORDER BY queries.

## Safety
- [ ] This migration does not drop data in the first deploy.
- [ ] Large-table locks were considered.
- [ ] The application can run during the expand-and-contract period.
- [ ] Backfill can be retried safely.

## Up SQL
    ALTER TABLE projects ADD COLUMN archived_at TIMESTAMPTZ;
    UPDATE projects
    SET archived_at = now()
    WHERE status = 'archived' AND archived_at IS NULL;
    ALTER TABLE projects
      ADD CONSTRAINT projects_archived_at_matches_status
      CHECK (
        (status = 'archived' AND archived_at IS NOT NULL)
        OR (status = 'active' AND archived_at IS NULL)
      ) NOT VALID;
    ALTER TABLE projects VALIDATE CONSTRAINT projects_archived_at_matches_status;

## Down SQL
    ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_archived_at_matches_status;
    ALTER TABLE projects DROP COLUMN IF EXISTS archived_at;

## Verification
- [ ] Constraint test SQL was run locally.
- [ ] Read queries still use expected indexes.
- [ ] Rollback was reviewed, including data-loss notes.
- [ ] Claude Code findings were reviewed by a human owner.

Rollback is the part to read most critically. A generated down migration may restore the table shape but not the deleted data. If the rollback is partial, say so in the PR and plan backup, staged release, or data recovery separately.

Share the Design Visually

An ER diagram helps reviewers see the relationships quickly. It is not a substitute for DDL, but it makes a PR easier to discuss.

erDiagram
    users ||--o{ team_members : joins
    teams ||--o{ team_members : has
    teams ||--o{ projects : owns
    users ||--o{ projects : creates
    teams ||--o{ audit_logs : records
    users ||--o{ audit_logs : acts
    teams ||--o{ subscriptions : pays
    projects ||--|| project_search_documents : indexes

CTA: Make Review Rules a Team Habit

Claude Code becomes much safer when the review rules are written down. Put database guardrails in CLAUDE.md: no direct production DB access, no destructive migration without staged rollout, no schema change without constraint tests, and no PII in logs unless the data owner approves the retention policy.

ClaudeCodeLab helps teams turn those rules into practical workflows: Claude Code onboarding, CLAUDE.md, migration review templates, PR checklists, and training sessions using a real repository. If your team wants help applying this to an existing product, start from the Claude Code training and consultation page. Individual readers can also use the free cheatsheet and product templates.

Result From Masa

When Masa tried this workflow on a real schema review, the most useful step was not the ER diagram or the first AI-generated schema. It was the set of inserts that were supposed to fail: duplicate email, null project name, missing parent team, duplicate team slug, PII in audit metadata, and a second active subscription. Those tests made the design review concrete. Claude Code sped up the checklist, but the confidence came from seeing the database reject bad data.