Conception de schéma de base de données : des patterns qui vieillissent bien

Un schéma est un contrat avec votre futur vous. Chaque colonne que vous ajoutez, chaque contrainte que vous sautez, chaque « on nettoiera ça plus tard » — tout s'accumule. J'ai travaillé sur des systèmes où une seule mauvaise décision de schéma prise il y a trois ans coûte maintenant à l'équipe un sprint complet chaque trimestre en contournements.

La base de données survit à tout. Votre framework frontend changera. Votre couche API sera réécrite. Votre stratégie de déploiement évoluera. Mais les données ? Les données restent. Et la forme que vous leur avez donnée au jour un vous suit pour toujours, parce que migrer une table de 200 millions de lignes n'est pas la même chose que refactorer un composant React.

Voici ce que j'ai appris sur les décisions de schéma qui ne vous hantent pas. Orienté PostgreSQL, parce que c'est ce que j'utilise et en quoi j'ai confiance, mais la plupart de ces patterns s'appliquent à n'importe quelle base de données relationnelle.

Conventions de nommage : la chose ennuyeuse qui compte le plus#

J'ai vu plus de débats sur les conventions de nommage que sur de vraies décisions d'architecture. Voici ce sur quoi je me suis arrêté après des années de changement de contexte entre projets :

Snake_case pour tout. Tables, colonnes, index, contraintes. Pas de camelCase, pas de PascalCase. PostgreSQL convertit les identifiants non quotés en minuscules de toute façon, donc createdAt devient createdat à moins que vous ne le mettiez entre guillemets partout. Ne combattez pas la base de données.

Noms de tables au pluriel. Une table contient de nombreuses lignes. users et non user. orders et non order. order_items et non order_item. Cela se lit naturellement dans les requêtes : SELECT * FROM users WHERE ... — vous sélectionnez depuis une collection.

Suffixe _id pour les clés étrangères. user_id, order_id, tenant_id. La clé primaire est simplement id. C'est sans ambiguïté. Quand vous faites des jointures, users.id = orders.user_id se lit comme du français.

Suffixe _at pour les timestamps. created_at, updated_at, deleted_at, published_at, expires_at. Vous savez toujours que c'est un point dans le temps.

Préfixe is_ pour les booléens. is_active, is_verified, is_published. Certains utilisent has_ pour les booléens de possession (has_mfa_enabled), mais je garde les choses simples et utilise is_ pour tout.

Suffixe _count pour les compteurs dénormalisés. comment_count, follower_count. Cela indique clairement que c'est un nombre mis en cache, pas un calcul en direct.

Voici le point important : la cohérence bat la perfection. J'ai vu des équipes passer des semaines à débattre si ça devrait être email_address ou email ou email_addr. Choisissez simplement un pattern et appliquez-le partout. La pire convention de nommage est celle qui est appliquée de manière incohérente.

sql

-- Good: consistent, readable, no surprises
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL,
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    login_count     INTEGER NOT NULL DEFAULT 0,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Bad: inconsistent casing, mixed conventions, ambiguous names
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- is this 0/1? a verification level?
    lastLogin       TIMESTAMP,       -- camelCase in a snake_case world
    created         TIMESTAMP        -- created what? when?
);

Une dernière chose : n'utilisez jamais de mots réservés comme noms de colonnes. type, order, user, group, table — ils fonctionnent tous si vous les quotez, mais ils vous brûleront dans les ORM, les constructeurs de requêtes et chaque outil de génération SQL dynamique. Utilisez kind au lieu de type, sort_order au lieu de order. Votre futur vous vous remerciera.

Les colonnes standard : ce que chaque table reçoit#

Chaque table dans mes schémas commence avec le même squelette. Sans exception.

sql

CREATE TABLE things (
    id          BIGSERIAL PRIMARY KEY,
    -- ... domain columns ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Le débat UUID vs BIGSERIAL#

C'est l'une de ces décisions qui génère bien plus de chaleur que de lumière. Voici le vrai compromis :

BIGSERIAL (entier auto-incrémenté) :

8 octets. Compact. Rapide à indexer et joindre.
Ordonné — vous pouvez trier par id pour obtenir l'ordre d'insertion.
Prévisible — un utilisateur peut deviner d'autres ID en incrémentant le sien.
Ne fonctionne pas bien dans les systèmes distribués (nécessite une coordination).

UUID v4 (aléatoire) :

16 octets. Index plus grands, jointures plus lentes (mais rarement le goulot d'étranglement).
Imprévisible — pas de fuite d'information.
Fonctionne dans les systèmes distribués sans coordination.
Terrible localité d'index — les UUID aléatoires fragmentent les index B-tree.

UUID v7 (trié par le temps, RFC 9562) :

16 octets, mais ordonné par le temps donc la localité B-tree est excellente.
Suffisamment imprévisible pour un usage externe.
Relativement nouveau, mais PostgreSQL 17+ a gen_random_uuid() et vous pouvez utiliser uuid_generate_v7() avec des extensions.

Ma position actuelle : BIGSERIAL pour les tables internes, UUID v7 pour tout ce qui est exposé au monde extérieur. Si un ID apparaît un jour dans une URL, une réponse API ou un payload de webhook, utilisez des UUID. Si c'est une pure table de jointure que les utilisateurs ne voient jamais, BIGSERIAL convient.

sql

-- For an API-facing resource
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- For an internal mapping table
CREATE TABLE project_members (
    id          BIGSERIAL PRIMARY KEY,
    project_id  UUID NOT NULL REFERENCES projects(id),
    user_id     UUID NOT NULL REFERENCES users(id),
    role        TEXT NOT NULL DEFAULT 'member',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, user_id)
);

Timestamps : toujours TIMESTAMPTZ#

Utilisez TIMESTAMPTZ, pas TIMESTAMP. La version « TZ » stocke la valeur en UTC et convertit à la lecture en fonction du fuseau horaire de la session. La version sans TZ stocke ce que vous lui donnez sans contexte de fuseau horaire — ce qui signifie que si deux serveurs dans des fuseaux horaires différents écrivent dans la même table, vous obtenez une corruption silencieuse des données.

sql

-- Always this
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Never this
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

Le trigger updated_at#

PostgreSQL n'a pas le ON UPDATE CURRENT_TIMESTAMP de MySQL. Vous avez besoin d'un trigger :

sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Apply to every table that has updated_at
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
 
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Oui, vous avez besoin d'un trigger par table. Oui, c'est fastidieux. Écrivez un helper de migration qui crée le trigger automatiquement quand vous ajoutez une table. Ça en vaut la peine, car l'alternative est de se souvenir de mettre updated_at = NOW() dans chaque requête UPDATE à travers toute votre application — et vous allez oublier.

Normalisation : quand enfreindre les règles#

Chaque cours d'informatique enseigne la normalisation jusqu'à la 3NF (troisième forme normale). Les règles sont :

1NF : Chaque colonne contient une seule valeur atomique. Pas de tableaux, pas de listes séparées par des virgules.
2NF : Chaque colonne non-clé dépend de la totalité de la clé primaire (pertinent pour les clés composites).
3NF : Pas de dépendances transitives. Si la colonne A détermine la colonne B, et B détermine C, alors C ne devrait pas être dans la même table que A.

En pratique, la 3NF est le point optimal pour les tables transactionnelles. Vous devriez commencer par là et ne dévier que quand vous avez une raison spécifique et mesurable.

Voici un système de commandes correctement normalisé :

sql

CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    display_name    TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    sku             TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    price_cents     INTEGER NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customers(id),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_cents     INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE order_items (
    id              BIGSERIAL PRIMARY KEY,
    order_id        BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id      BIGINT NOT NULL REFERENCES products(id),
    quantity        INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Remarquez unit_price_cents sur order_items. C'est une dénormalisation intentionnelle. Nous capturons le prix au moment de la commande, car le prix du produit pourrait changer plus tard. C'est l'une des raisons les plus courantes et correctes de dénormaliser.

Quand la dénormalisation est le bon choix#

Tables de reporting. Si votre tableau de bord analytique doit joindre 8 tables pour s'afficher, créez une table de reporting dénormalisée et remplissez-la avec un job en arrière-plan. Votre schéma transactionnel reste propre, et vos requêtes de reporting restent rapides.

sql

-- Denormalized reporting table, populated by a cron job
CREATE TABLE order_reports (
    id                  BIGSERIAL PRIMARY KEY,
    order_id            BIGINT NOT NULL,
    customer_email      TEXT NOT NULL,
    customer_name       TEXT NOT NULL,
    product_names       TEXT[] NOT NULL,        -- array is fine here
    item_count          INTEGER NOT NULL,
    total_cents         INTEGER NOT NULL,
    order_status        TEXT NOT NULL,
    ordered_at          TIMESTAMPTZ NOT NULL,
    report_generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Vues matérialisées. La MATERIALIZED VIEW de PostgreSQL est sous-estimée. C'est un instantané dénormalisé que vous rafraîchissez à la demande. Parfait pour les tableaux de bord.

sql

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_cents) AS revenue_cents,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.status != 'cancelled'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;
 
-- Refresh it nightly
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Colonnes JSON pour les attributs dynamiques. Quand différentes lignes ont besoin de champs différents — variantes de produits, soumissions de formulaires, préférences utilisateur — une colonne JSONB est souvent meilleure qu'un cauchemardesque table EAV (Entity-Attribute-Value). Plus de détails ci-dessous.

La seule règle que je ne brise jamais#

Ne dénormalisez jamais vos tables source de vérité. Dénormalisez les copies, les instantanés, les rapports et les caches. Les données canoniques restent normalisées. Quand la copie dénormalisée devient périmée ou corrompue (et ça arrivera), vous la reconstruisez depuis la source normalisée.

Clés étrangères et contraintes : le meilleur code que vous n'écrirez jamais#

J'ai entendu toutes les excuses pour sauter les clés étrangères. « Elles ralentissent les écritures. » « On l'applique dans l'application. » « On a besoin de flexibilité. »

Elles sont toutes fausses.

Les clés étrangères sont la chose la plus impactante que vous puissiez ajouter à un schéma. Elles empêchent des catégories entières de bugs qu'aucune quantité de code applicatif ne peut attraper — conditions de course, échecs partiels, lignes orphelines de transactions échouées. Une clé étrangère est une garantie du moteur de base de données lui-même, appliquée au niveau du stockage. Le hook beforeDelete de votre ORM est une suggestion.

sql

-- Always do this
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- The "we'll handle it in code" alternative:
-- Hope. Hope is not a strategy.

Stratégies ON DELETE#

C'est là que ça devient nuancé. Que se passe-t-il quand vous supprimez une ligne parente ?

RESTRICT (par défaut) : La suppression échoue si des lignes enfants existent. Utilisez cela pour la plupart des relations. Vous ne pouvez pas supprimer un client qui a des commandes — c'est la logique métier encodée dans le schéma.

sql

-- Customer can't be deleted while they have orders
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE : Les lignes enfants sont automatiquement supprimées. Utilisez cela avec parcimonie et délibération. Bon pour les relations « fait partie de » où l'enfant n'a pas de sens sans le parent.

sql

-- Deleting an order deletes its line items — they're meaningless alone
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Deleting a project deletes its memberships
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL : La colonne de clé étrangère est mise à NULL. Utilisez cela quand la relation est optionnelle et que la ligne enfant a toujours un sens par elle-même.

sql

-- If a manager leaves, their reports still exist — just unassigned
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Ma règle générale : par défaut RESTRICT, utilisez CASCADE pour les relations de composition, utilisez SET NULL pour les associations optionnelles. Si vous n'êtes pas sûr, RESTRICT est toujours le choix sûr — il est plus facile d'assouplir une contrainte que de récupérer des données supprimées.

Contraintes Check : une assurance bon marché#

Les contraintes check ne coûtent presque rien en écriture et empêchent les données erronées d'entrer dans votre système pour toujours :

sql

CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    discount_pct    NUMERIC(5,2) CHECK (discount_pct BETWEEN 0 AND 100),
    weight_kg       NUMERIC(10,3) CHECK (weight_kg > 0),
    status          TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Chaque contrainte check que vous ajoutez est un bug de validation de moins que vous n'aurez jamais à déboguer en production. La base de données est la dernière ligne de défense. Utilisez-la.

Contraintes d'unicité et index partiels uniques#

Les contraintes d'unicité sont simples pour les cas basiques :

sql

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Mais elles deviennent intéressantes avec les index partiels — des contraintes d'unicité qui ne s'appliquent qu'à certaines lignes :

sql

-- Only one active subscription per user (but they can have many cancelled ones)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Only one primary address per user
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

C'est l'une des fonctionnalités phares de PostgreSQL. Utilisez-la.

Soft deletes : le pattern que tout le monde adore détester#

Les soft deletes sont simples en concept : au lieu de DELETE FROM users WHERE id = 42, vous faites UPDATE users SET deleted_at = NOW() WHERE id = 42. La ligne reste dans la base de données mais est filtrée des requêtes normales.

sql

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    display_name TEXT NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- The partial index: filter out deleted rows efficiently
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Chaque requête dans votre application a maintenant besoin de WHERE deleted_at IS NULL. Chaque. Requête. Les ORM aident avec cela (Prisma a du middleware, Drizzle a .where(isNull(deletedAt))), mais c'est une taxe sur chaque opération de lecture. Ratez-la une fois et vous montrez des données « supprimées » aux utilisateurs.

Le problème de la contrainte d'unicité#

C'est là que les soft deletes deviennent moches. Si vous avez UNIQUE (email) et qu'un utilisateur supprime son compte en soft delete, son email est toujours dans la table. Il ne peut pas se réinscrire avec le même email. Un nouvel utilisateur avec cet email ne peut pas s'inscrire non plus.

La solution est un index partiel unique :

sql

-- Email must be unique, but only among non-deleted users
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Ça fonctionne, mais maintenant vous devez vous souvenir de ce pattern pour chaque colonne unique sur chaque table soft-deletable. C'est gérable avec de la discipline, mais c'est de la complexité que vous avez choisi de prendre en charge.

Le problème des clés étrangères#

Les soft deletes interagissent mal avec les clés étrangères. Si orders.user_id référence users.id avec ON DELETE RESTRICT, et que vous soft-delete un utilisateur... rien ne se passe. La FK ne se déclenche pas parce que vous n'avez pas réellement supprimé la ligne. L'utilisateur est « parti » du point de vue de l'application mais toujours très présent dans la base de données.

Cela signifie que votre code applicatif doit gérer le cas où une entité référencée est soft-deleted. Chaque jointure, chaque recherche, chaque fois que vous suivez une clé étrangère — vous devez vérifier deleted_at IS NULL sur la table référencée aussi. Ou vous ne le faites pas, et votre application affiche « Commande par [utilisateur supprimé] » ce qui est soit un bug soit une fonctionnalité selon à qui vous demandez.

Mon approche#

J'utilise les soft deletes uniquement pour les entités exposées aux utilisateurs où la récupération est une exigence métier — comptes utilisateurs, projets, documents. Les choses où un agent de support pourrait avoir besoin de restaurer une suppression. Pour tout le reste, j'utilise les hard deletes avec un audit trail (plus de détails dans la section suivante).

sql

-- Soft delete: user-facing, recoverable
CREATE TABLE documents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_documents_active ON documents (owner_id) WHERE deleted_at IS NULL;
 
-- Hard delete: internal, not recoverable from UI (but audited)
CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_hash    TEXT NOT NULL,
    last_used_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- When deleted, it's gone. The audit_log records that it existed.

Audit trails : savoir ce qui a changé et qui l'a fait#

Chaque application non-triviale a besoin d'une réponse à « que s'est-il passé avec cet enregistrement ? » à un moment donné. Les audit trails sont la façon dont vous fournissez cette réponse sans fouiller dans les logs applicatifs.

Le pattern de la table d'audit séparée#

L'approche la plus simple : une seule table audit_log qui enregistre chaque changement.

sql

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text to handle both UUID and BIGINT PKs
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL for INSERT
    new_values      JSONB,                  -- NULL for DELETE
    changed_fields  TEXT[],                 -- which columns changed (for UPDATE)
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address      INET,
    user_agent      TEXT
);
 
CREATE INDEX idx_audit_log_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_log_changed_by ON audit_log (changed_by);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Le défi est de la remplir. Vous pouvez le faire dans le code applicatif (explicite, mais facile à oublier) ou avec des triggers (automatique, mais plus difficile de passer le contexte comme changed_by).

L'approche par triggers#

Les triggers capturent chaque changement automatiquement, même depuis du SQL brut ou des opérations d'administration de base de données :

sql

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
    changed  TEXT[];
    col      TEXT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        INSERT INTO audit_log (table_name, record_id, operation, old_values, changed_at)
        VALUES (TG_TABLE_NAME, OLD.id::TEXT, 'DELETE', old_data, NOW());
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);
        -- Find changed fields
        FOR col IN SELECT key FROM jsonb_each(new_data)
        LOOP
            IF old_data->col IS DISTINCT FROM new_data->col THEN
                changed := array_append(changed, col);
            END IF;
        END LOOP;
        -- Only log if something actually changed
        IF array_length(changed, 1) > 0 THEN
            INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_fields, changed_at)
            VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'UPDATE', old_data, new_data, changed, NOW());
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        new_data := to_jsonb(NEW);
        INSERT INTO audit_log (table_name, record_id, operation, new_values, changed_at)
        VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'INSERT', new_data, NOW());
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- Apply to tables you want audited
CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
 
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

L'inconvénient : les triggers ne savent pas quel utilisateur de l'application a fait le changement. Vous pouvez contourner cela avec des variables de session :

sql

-- In your application, before the query:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- In the trigger function:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Ça fonctionne mais paraît fragile. En pratique, j'utilise une approche hybride : des triggers pour la capture des données, et du code applicatif pour définir le contexte de session.

Le pattern de la table d'historique#

Pour les tables où vous avez besoin de l'historique complet des versions (pas seulement « qu'est-ce qui a changé » mais « quel était l'état au temps T »), une table d'historique dédiée est plus propre :

sql

CREATE TABLE documents (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL DEFAULT 1,
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE document_history (
    id              BIGSERIAL PRIMARY KEY,
    document_id     UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL,
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (document_id, version)
);

Avant chaque mise à jour de documents, copiez l'état actuel dans document_history et incrémentez la version. Maintenant vous pouvez reconstruire le document à n'importe quel point dans le temps, montrer les diffs entre versions, et même restaurer d'anciennes versions.

Le compromis est le stockage. Si votre colonne content est volumineuse et change fréquemment, la table d'historique peut grossir vite. Pour la plupart des applications, c'est acceptable — le stockage est bon marché et vous pouvez archiver les anciennes versions vers un stockage froid si nécessaire.

Multi-tenancy : trois approches, choisissez votre souffrance#

La multi-tenancy est l'une de ces choses faciles à ajouter au début et quasi impossibles à ajouter plus tard. S'il y a la moindre chance que votre application serve plusieurs organisations, intégrez-la dès le premier jour.

Row-Level : tenant_id sur chaque table#

L'approche la plus courante. Chaque table a une colonne tenant_id, et chaque requête filtre par celle-ci.

sql

CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL UNIQUE,
    plan        TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    project_id  UUID NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'todo',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Every index should include tenant_id for query performance
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

Le risque est la fuite de données — un WHERE tenant_id = ... oublié et vous montrez les données du Tenant A au Tenant B. Le Row-Level Security (RLS) de PostgreSQL élimine cette classe de bugs :

sql

-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Create a policy based on a session variable
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Force RLS even for table owners
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Maintenant, même si votre code applicatif oublie la clause WHERE tenant_id = ..., PostgreSQL l'ajoute automatiquement. C'est de la défense en profondeur, et c'est l'un des arguments les plus forts pour PostgreSQL dans les systèmes multi-tenants.

sql

-- In your application's connection middleware:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Now all queries on RLS-enabled tables are automatically filtered
SELECT * FROM projects;
-- PostgreSQL internally adds: WHERE tenant_id = 'tenant-uuid-here'

Avantages : Base de données unique, opérations simples, utilisation efficace des ressources, requêtes cross-tenant faciles pour l'admin. Inconvénients : Nécessite de la discipline (ou le RLS), chaque requête touche tenant_id, plus difficile de donner aux tenants leur propre backup/restore.

Schéma par tenant#

Chaque tenant obtient son propre schéma PostgreSQL. Tous les schémas partagent la même base de données, mais les tables sont isolées par namespace.

sql

-- Create a schema for each tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tables live in the tenant's schema
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Set the search_path for the current tenant
SET search_path TO tenant_acme, public;
 
-- Now unqualified queries hit the tenant's schema
SELECT * FROM projects;  -- queries tenant_acme.projects

Avantages : Forte isolation, pas de risque de fuite de données cross-tenant, backup/restore facile par tenant, les tenants peuvent avoir des variations de schéma. Inconvénients : Complexité de migration de schéma (vous devez migrer N schémas), gestion du pool de connexions, PostgreSQL a des limites pratiques autour de ~10 000 schémas.

Base de données par tenant#

Chaque tenant obtient sa propre base de données. Isolation maximale.

Avantages : Isolation complète, scaling indépendant, backup/restore facile, possibilité de placer les gros tenants sur du matériel dédié. Inconvénients : Cauchemar de gestion des connexions, requêtes cross-tenant impossibles, la migration doit s'exécuter N fois, surcharge opérationnelle significative.

Lequel choisir ?#

Pour la plupart des applications SaaS : commencez avec le row-level + RLS. C'est le plus simple à opérer, et le RLS vous donne une isolation suffisamment forte pour la grande majorité des cas d'usage. Passez au schéma par tenant uniquement si vous avez des exigences contractuelles d'isolation (clients entreprise, industries réglementées). Base de données par tenant, c'est pour quand vous devez absolument garantir une isolation physique — et même dans ce cas, envisagez des bases de données gérées où la charge opérationnelle est prise en charge pour vous.

Colonnes JSON/JSONB : la porte de sortie#

Le JSONB de PostgreSQL est remarquable. Il vous donne la flexibilité d'une base de données documentaire à l'intérieur d'un système relationnel. Mais comme tout outil puissant, il est facile d'en abuser.

Quand JSONB est le bon choix#

Attributs dynamiques définis par l'utilisateur. Une plateforme e-commerce où chaque catégorie de produit a des attributs différents — les chaussures ont taille et couleur, l'électronique a voltage et puissance. Plutôt qu'une table EAV ou une colonne pour chaque attribut possible :

sql

CREATE TABLE products (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    category_id     UUID NOT NULL REFERENCES categories(id),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    attributes      JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Example data:
-- Shoe: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Paramètres et configuration d'application. Préférences utilisateur, feature flags, paramètres de notification — des choses qui changent de forme fréquemment et n'ont pas besoin d'intégrité relationnelle.

sql

CREATE TABLE user_settings (
    user_id     UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    preferences JSONB NOT NULL DEFAULT '{
        "theme": "system",
        "notifications": {"email": true, "push": false},
        "locale": "en",
        "timezone": "UTC"
    }',
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Payloads de webhook, réponses API, données d'événements. Tout ce dont le schéma est contrôlé par un système externe et pourrait changer sans préavis.

Index GIN sur JSONB#

Sans index, interroger à l'intérieur du JSONB nécessite un scan complet de table. Les index GIN le rendent rapide :

sql

-- Index the entire JSONB column (good for @> containment queries)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Query: find all products with color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Query: find products with any of these attributes
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Pour des chemins spécifiques que vous interrogez souvent, un index ciblé est plus efficace :

sql

-- Index a specific path
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Now this is a regular B-tree lookup
SELECT * FROM products
WHERE attributes->>'color' = 'black';

Le piège de la migration#

C'est là que JSONB vous mord : vous ne pouvez pas facilement ajouter des contraintes NOT NULL ou des valeurs par défaut à des champs à l'intérieur du JSON. Si vous ajoutez un nouveau champ obligatoire à vos attributs de produit, vous devez remplir rétroactivement chaque ligne existante. Avec une colonne normale, une migration gère cela atomiquement. Avec JSONB, vous écrivez un UPDATE qui touche chaque ligne et espérez que votre code applicatif gère les champs manquants gracieusement jusqu'à ce que le remplissage soit terminé.

sql

-- Adding a new column: clean, atomic, one statement
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Adding a new JSONB field: messy, requires a backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Plus update application code to handle missing weight_kg
-- Plus update validation logic
-- Plus update every API response that includes attributes

Ma règle : si vous interrogez un champ JSONB dans une clause WHERE plus de deux fois par semaine, il devrait probablement être une colonne. JSONB est une excellente porte de sortie. C'est un terrible choix par défaut.

Migrations de schéma : ne pas casser la production à 3h du matin#

Les migrations de schéma sont là où la théorie rencontre la réalité. Votre schéma est parfait sur le papier, mais maintenant vous devez modifier une table de 50 millions de lignes pendant les heures ouvrées sans aucun temps d'arrêt.

Outils de migration#

J'en ai utilisé la plupart. Avis rapides :

Drizzle (TypeScript) : Mon favori actuel. Schéma en code, requêtes type-safe générées depuis le schéma, SQL de migration propre. La commande push pour le développement est rapide.

Prisma (TypeScript) : Excellente DX pour les schémas simples. Difficultés avec les fonctionnalités PostgreSQL avancées (index partiels, types personnalisés, RLS). Le moteur de migration peut prendre des décisions surprenantes.

Flyway (Java/CLI) : Solide comme un roc, éprouvé, SQL-first. Si vous écrivez des migrations SQL brutes, Flyway les suit de manière fiable. Pas de magie, pas de surprises.

golang-migrate (Go/CLI) : Similaire à Flyway mais plus léger. Excellent pour les projets Go ou quand vous voulez juste un simple lanceur de migrations up/down.

Le problème du zéro temps d'arrêt#

Les changements de schéma les plus dangereux sont ceux qui verrouillent la table. Dans PostgreSQL, ALTER TABLE ... ADD COLUMN avec une valeur par défaut verrouillait autrefois la table entière pendant la durée de la réécriture. Depuis PostgreSQL 11, les valeurs par défaut simples (constantes) sont des opérations metadata-only et instantanées. Mais d'autres opérations verrouillent encore :

sql

-- SAFE: metadata-only, instant (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- DANGEROUS: rewrites the entire table, full table lock
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- DANGEROUS: scans the entire table to validate
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

Ajouter des colonnes NOT NULL en toute sécurité#

Vous ne pouvez pas simplement ajouter NOT NULL à une table existante avec des données — ça échoue car les lignes existantes n'ont pas de valeur. L'approche naïve :

sql

-- This locks the table and rewrites it. Don't do this on a big table.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Depuis PostgreSQL 11, c'est en fait sûr pour les valeurs par défaut constantes — c'est metadata-only. Mais si votre valeur par défaut est une fonction ou que vous devez remplir avec des valeurs calculées, utilisez le pattern expand-contract.

Le pattern Expand-Contract#

C'est le standard d'or pour les changements de schéma sans temps d'arrêt. Trois phases :

Phase 1 : Expand — Ajoutez la nouvelle colonne comme nullable. Déployez le code applicatif qui écrit dans les deux colonnes, ancienne et nouvelle.

sql

-- Migration 1: Add the nullable column
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Phase 2 : Migrate — Remplissez les lignes existantes par lots. Votre application écrit déjà dans la nouvelle colonne pour les nouvelles données.

sql

-- Migration 2: Backfill in batches (don't do this in one statement for large tables)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Repeat until all rows are backfilled

Phase 3 : Contract — Une fois que toutes les lignes sont remplies, ajoutez la contrainte NOT NULL et supprimez l'ancienne colonne (si applicable).

sql

-- Migration 3: Add constraint (use NOT VALID to avoid full table scan, then validate separately)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migration 4: Validate the constraint (takes a ShareUpdateExclusiveLock, not an AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

L'astuce NOT VALID + VALIDATE CONSTRAINT est cruciale. Ajouter une contrainte check scanne normalement la table entière en maintenant un verrou lourd. NOT VALID ajoute la contrainte sans scanner (elle ne s'applique qu'aux nouvelles écritures), et VALIDATE CONSTRAINT scanne avec un verrou plus léger qui ne bloque ni les lectures ni les écritures.

Création d'index#

Créer des index sur de grandes tables bloque les écritures par défaut. Utilisez toujours CONCURRENTLY :

sql

-- BLOCKS WRITES: don't do this on a live table
CREATE INDEX idx_users_email ON users (email);
 
-- NON-BLOCKING: use this instead
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY prend plus de temps et ne peut pas s'exécuter dans une transaction, mais il ne verrouille pas la table. Le compromis en vaut toujours la peine en production.

Les décisions que j'ai regrettées#

Chaque schéma a ses regrets. Voici les miens.

Utiliser SERIAL au lieu d'UUID pour les ID externes#

Au début d'un projet, j'ai utilisé des clés primaires SERIAL et les ai exposées directement dans les URL : /users/42, /orders/1337. Cela fuitait des informations (les concurrents pouvaient deviner notre nombre d'utilisateurs), rendait la pagination devinable, et a cassé quand nous avons eu besoin de fusionner des bases de données de deux régions. Passer aux UUID pour les ID exposés à l'extérieur a nécessité une migration de plusieurs mois.

Leçon : utilisez des UUID pour tout ce qui est exposé en dehors de votre base de données. Utilisez SERIAL/BIGSERIAL pour les tables de jointure internes si vous voulez, mais ne laissez jamais un entier auto-incrémenté apparaître dans une URL.

Pas de contraintes (« on les ajoutera plus tard »)#

Nous avons lancé une table sans contraintes check parce que « on avance vite et on les ajoutera plus tard ». En deux semaines, les données contenaient des prix négatifs, des noms vides, et une valeur order_status de "oof" que quelqu'un avait tapée pendant les tests et jamais nettoyée.

Ajouter les contraintes après coup a nécessité :

Trouver toutes les données invalides
Décider quoi en faire (corriger, supprimer ou les conserver en l'état)
Écrire une migration qui corrige/remplit les données ET ajoute la contrainte

Cela a pris plus de temps que d'ajouter la contrainte au jour un. Commencez avec les contraintes. Assouplissez-les si nécessaire. Jamais l'inverse.

Des chaînes séparées par des virgules au lieu de tableaux ou tables de jointure#

J'ai un jour stocké des tags sous forme de chaîne séparée par des virgules : "javascript,react,nextjs". L'interrogation était un cauchemar :

sql

-- This is how you query comma-separated values. Don't do this.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- This also matches "react-native" and "preact"
 
-- What I should have done:
CREATE TABLE post_tags (
    post_id     UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id      UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Ou au minimum, utilisez le type tableau natif de PostgreSQL :

sql

CREATE TABLE posts (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title   TEXT NOT NULL,
    tags    TEXT[] NOT NULL DEFAULT '{}',
    -- ...
);
 
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
 
-- Clean queries
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Les tableaux sont acceptables pour des listes simples qui n'ont pas besoin de leurs propres attributs. Dès que vous avez besoin de métadonnées sur la relation (comme « qui a ajouté ce tag » ou « quand a-t-il été ajouté »), vous avez besoin d'une table de jointure.

Utiliser « type » comme nom de colonne#

sql

-- Seemed fine at the time
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Then you try to query it in literally any ORM:
-- notification.type  ← conflicts with every language's type system
-- "type" is a reserved word in most SQL dialects
-- You end up quoting it everywhere: SELECT "type" FROM notifications

Utilisez kind, category ou notification_type. N'importe quoi sauf type. De même, évitez order (utilisez sort_order ou position), user (utilisez account ou préfixez-le) et group (utilisez team ou group_name).

Ne pas ajouter created_at dès le début#

Une table n'a pas reçu de created_at parce que « on n'en a pas besoin ». Trois mois plus tard, nous avons eu besoin de déboguer un problème et n'avions aucune idée de quand les enregistrements avaient été créés. L'ajouter rétroactivement signifiait que toutes les lignes existantes recevaient le même timestamp (celui de la migration), rendant les données historiques inutiles.

Chaque table reçoit created_at. Sans exception. Le coût est une colonne. Le coût de ne pas l'avoir est inconnaissable jusqu'à ce que vous en ayez besoin.

Mettre la logique métier dans les vues de base de données#

J'ai un jour créé une chaîne de vues — active_users filtrait users, premium_active_users filtrait active_users, et une vue de reporting joignait tout. Ça fonctionnait très bien jusqu'à ce que quelqu'un modifie la table users et que les trois vues cassent silencieusement. Le planificateur de requêtes avait aussi du mal à optimiser à travers plusieurs couches de vues.

Les vues sont excellentes pour la commodité et les patterns d'accès en lecture seule. Elles sont terribles comme endroit pour encoder la logique métier qui change. Gardez les règles métier dans le code applicatif où elles sont versionnées, testées et déployables. Utilisez les vues pour les raccourcis de reporting, pas comme des blocs de construction architecturaux.

Tout assembler#

Voici à quoi ressemble un schéma bien conçu quand vous combinez ces patterns. Un système de gestion de projet simplifié :

sql

-- Extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- for gen_random_uuid()
 
-- Updated_at trigger function (reusable)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Tenants
CREATE TABLE tenants (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    slug            TEXT NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9-]+$'),
    plan            TEXT NOT NULL DEFAULT 'free'
                    CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings        JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tenants
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Users
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL CHECK (LENGTH(display_name) > 0),
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    last_login_at   TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE UNIQUE INDEX uq_users_email_per_tenant
    ON users (tenant_id, email)
    WHERE deleted_at IS NULL;
 
CREATE INDEX idx_users_tenant ON users (tenant_id) WHERE deleted_at IS NULL;
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Projects
CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'archived', 'deleted')),
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_projects_owner ON projects (owner_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Tasks
CREATE TABLE tasks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title           TEXT NOT NULL CHECK (LENGTH(title) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'todo'
                    CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
    priority        INTEGER NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 4),
    assignee_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    due_at          TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);
CREATE INDEX idx_tasks_assignee ON tasks (assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_tasks_status ON tasks (tenant_id, status) WHERE status != 'done';
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Task comments
CREATE TABLE task_comments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    task_id         UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    author_id       UUID NOT NULL REFERENCES users(id),
    body            TEXT NOT NULL CHECK (LENGTH(body) > 0),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_task_comments_task ON task_comments (task_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON task_comments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE task_comments ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON task_comments
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Audit log (no RLS — admin-only table)
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,
    new_values      JSONB,
    changed_fields  TEXT[],
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_audit_log_tenant_table ON audit_log (tenant_id, table_name, record_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Remarquez les patterns :

Chaque table a tenant_id et le RLS activé (sauf tenants et audit_log).
Chaque table a created_at et updated_at avec des triggers.
Des UUID pour toutes les clés primaires (entités exposées à l'extérieur).
Des contraintes check sur les enums de statut, les longueurs et les intervalles.
Des clés étrangères avec un comportement ON DELETE approprié.
Des index conçus pour les patterns d'accès (tenant + project, tenant + status).
Des soft deletes uniquement sur users (où la récupération de compte est nécessaire), archivage basé sur le statut pour projects.

Réflexions finales#

La conception de schéma n'est pas glamour. Personne n'a jamais donné une conférence intitulée « J'ai ajouté des contraintes check à chaque table et ça nous a épargné six mois de débogage ». Mais c'est exactement ce que fait une bonne conception de schéma — elle prévient des problèmes si silencieusement que vous ne savez même jamais qu'ils auraient existé.

Les patterns de cet article ne sont pas nouveaux. Ils sont le résultat d'années de rédaction de migrations, de débogage de corruption de données et de refactoring de schémas sous charge de production. Chacun d'entre eux existe parce que moi, ou quelqu'un avec qui j'ai travaillé, l'avons fait autrement d'abord et en avons payé le prix.

Commencez avec les contraintes. Utilisez les clés étrangères. Ajoutez created_at à tout. Choisissez une convention de nommage et appliquez-la impitoyablement. Utilisez le RLS pour la multi-tenancy. Soyez prudent avec JSONB. Testez vos migrations contre des données de taille production avant de les déployer.

La base de données est le fondement. Faites-le bien, et tout ce qui est construit dessus devient plus simple. Faites-le mal, et aucune quantité de code applicatif astucieux ne peut vous sauver.