Conception de base de données avec Claude Code : guide de revue

Concevoir une base de données ne revient pas à aligner des noms de tables. Il faut décider quelles informations vivent ensemble, quelles colonnes doivent être obligatoires, quelles valeurs ne doivent jamais se répéter, comment les lignes se référencent et quelles requêtes devront rester rapides lorsque le produit aura grossi. Un mauvais schéma peut sembler correct au premier écran, puis créer des comptes en double, des états de facturation incohérents, des tableaux de bord lents ou une migration impossible à annuler.

Claude Code est utile dans ce travail, mais il ne doit pas devenir une source de vérité automatique. Utilise-le comme assistant de conception et comme relecteur. Il peut proposer une première version, lister les risques, écrire des tests SQL et poser les bonnes questions. La décision finale doit rester humaine, fondée sur les règles métier, les requêtes réelles, la documentation officielle et les contraintes d’exploitation.

Pour compléter ce sujet, lis aussi l’automatisation des migrations de base de données et Prisma ORM avec Claude Code. Pour les références officielles, garde à portée la documentation PostgreSQL sur les contraintes et les index. Dès que des données sensibles peuvent entrer dans les logs ou les tables de reporting, ajoute aussi la grille OWASP Top 10.

Les briques de base

Une table regroupe un type de données, par exemple users, teams ou projects. Une colonne est un champ, comme email, status ou created_at. La clé primaire identifie une ligne. La clé étrangère garantit qu’une ligne pointe vers un parent existant. Une contrainte unique empêche les doublons métier, comme un email, un slug dans une équipe ou un ID client de paiement. Un index accélère les lectures fréquentes. Une migration est le script qui change le schéma, et le rollback est le chemin de retour si la livraison se passe mal.

Le piège classique consiste à laisser ces règles seulement dans le code applicatif. Une validation de formulaire est nécessaire pour l’expérience utilisateur, mais elle ne suffit pas face aux requêtes concurrentes ni aux jobs batch. La base de données doit garder la dernière barrière. Claude Code est surtout utile quand tu lui demandes de chercher cette barrière manquante.

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."

Quatre cas d’usage concrets

Le premier cas est un SaaS avec utilisateurs, équipes et projets. Un utilisateur peut appartenir à plusieurs équipes, et chaque équipe possède plusieurs projets. Le slug d’un projet doit souvent être unique dans l’équipe, pas dans tout le produit. La bonne règle devient donc UNIQUE (team_id, slug).

Le deuxième cas est le journal d’audit. On veut savoir qui a modifié quoi et quand, mais une table d’audit ne doit pas devenir un fourre-tout JSON. Y stocker des emails, numéros de téléphone ou adresses peut compliquer la conservation, les droits d’accès et les demandes de suppression.

Le troisième cas est la facturation. L’ID client du fournisseur ne doit pas se répéter, et une équipe ne devrait pas avoir deux abonnements actifs. En même temps, il faut garder l’historique annulé. Une contrainte simple ne suffit pas toujours ; un index unique partiel peut exprimer cette règle.

Le quatrième cas est la recherche ou le reporting. Une table optimisée pour la lecture peut éviter des JOIN complexes à chaque affichage. Mais il faut alors concevoir le rafraîchissement, les index et la vérification pour ne pas afficher des données obsolètes.

DDL PostgreSQL exécutable

Ce SQL recrée seulement un schema de démonstration. Tu peux le coller dans une base PostgreSQL locale sans toucher aux autres 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));

Données de test et contraintes

Un schéma fiable doit prouver que les bonnes insertions passent et que les mauvaises échouent. Les blocs DO ci-dessous transforment les erreurs attendues en messages lisibles.

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;

Pièges fréquents

Le premier piège est l’absence de contrainte unique. Email, compte OAuth, ID client de paiement et slug local à une équipe doivent souvent être protégés dans la base, pas seulement dans le backend. Le deuxième piège est le NULL trop permissif. Si projects.name, team_members.role ou subscriptions.status sont vides, l’interface et les permissions deviennent ambiguës.

Le troisième piège est la migration destructive. DROP COLUMN, changement de type, ajout de NOT NULL sur des données existantes ou valeur par défaut sur une grande table peuvent bloquer la production. Demande à Claude Code une stratégie expand-and-contract, puis fais-la relire. Le quatrième piège est le N+1 : une liste de 100 projets qui charge ensuite les compteurs d’audit un par un devient 101 requêtes. Le cinquième est la fuite de PII dans les logs, les tables de recherche ou les exports de reporting.

Checklist migration et rollback

# 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.

Le rollback doit être lu avec méfiance. Un script généré peut remettre les colonnes comme avant sans restaurer les données supprimées. Si le retour arrière n’est pas complet, écris-le clairement et prépare sauvegarde, déploiement progressif ou procédure de récupération.

Diagramme de partage

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

Le diagramme accélère la discussion, mais il ne remplace pas le DDL, les migrations et les tests SQL. Une jolie relation sans contrainte unique laisse quand même entrer des données cassées.

CTA et résultat terrain

Si ton équipe utilise Claude Code pour la conception de base de données, écris les règles dans CLAUDE.md : pas d’accès direct à la production, migration destructive en plusieurs étapes, tests de contraintes obligatoires, pas de PII dans les logs sans validation. ClaudeCodeLab accompagne les équipes sur l’adoption de Claude Code, les modèles de revue, les checklists PR et la formation sur dépôt réel. Pour l’appliquer à un produit existant, commence par la page formation et consultation Claude Code. Les lecteurs individuels peuvent aussi utiliser la cheatsheet gratuite et les templates produits.

Quand Masa a testé ce flux, l’élément le plus utile n’était pas le premier diagramme ER, mais les INSERT qui devaient échouer : email dupliqué, nom de projet nul, équipe inexistante, slug dupliqué, email dans l’audit et deuxième abonnement actif. Claude Code a accéléré la revue, mais la confiance est venue du fait que PostgreSQL rejetait vraiment les mauvaises données.