Database Schema-ontwerp: Patronen Die Goed Ouder Worden

Een schema is een contract met je toekomstige zelf. Elke kolom die je toevoegt, elke constraint die je overslaat, elk "we ruimen dit later op" — het stapelt zich allemaal op. Ik heb gewerkt aan systemen waar één slechte schemabeslissing van drie jaar geleden het team nu elk kwartaal een volledige sprint kost aan workarounds.

De database overleeft alles. Je frontend-framework zal veranderen. Je API-laag wordt herschreven. Je deployment-strategie zal evolueren. Maar de data? De data blijft. En de vorm die je het op dag één gaf, volgt je voor altijd, want het migreren van een tabel met 200 miljoen rijen is niet hetzelfde als het refactoren van een React-component.

Dit is wat ik heb geleerd over het nemen van schemabeslissingen die je niet achtervolgen. PostgreSQL-gericht, omdat dat is wat ik gebruik en vertrouw, maar de meeste van deze patronen zijn toepasbaar op elke relationele database.

Naamconventies: Het Saaie Dat Er Het Meest Toe Doet#

Ik heb meer discussies gezien over naamconventies dan over daadwerkelijke architectuurbeslissingen. Dit is waar ik op uitgekomen ben na jaren van context-switching tussen projecten:

Snake_case voor alles. Tabellen, kolommen, indexes, constraints. Geen camelCase, geen PascalCase. PostgreSQL vouwt unquoted identifiers sowieso naar lowercase, dus createdAt wordt createdat tenzij je het overal dubbel-quoted. Vecht niet tegen de database.

Meervoudige tabelnamen. Een tabel bevat meerdere rijen. users niet user. orders niet order. order_items niet order_item. Dit leest natuurlijk in queries: SELECT * FROM users WHERE ... — je selecteert uit een collectie.

_id suffix voor foreign keys. user_id, order_id, tenant_id. De primary key is gewoon id. Dit is ondubbelzinnig. Als je tabellen joint, leest users.id = orders.user_id als een zin.

_at suffix voor timestamps. created_at, updated_at, deleted_at, published_at, expires_at. Je weet altijd dat het een moment in de tijd is.

is_ prefix voor booleans. is_active, is_verified, is_published. Sommige mensen gebruiken has_ voor ownership-booleans (has_mfa_enabled), maar ik houd het simpel en gebruik is_ voor alles.

_count suffix voor gedenormaliseerde tellers. comment_count, follower_count. Maakt duidelijk dat dit een gecached getal is, geen live berekening.

En dan het belangrijkste: consistentie verslaat perfectie. Ik heb teams weken zien debatteren of het email_address of email of email_addr zou moeten zijn. Kies gewoon één patroon en dwing het overal af. De slechtste naamconventie is degene die inconsistent wordt toegepast.

sql

-- Goed: consistent, leesbaar, geen verrassingen
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()
);
 
-- Slecht: inconsistente casing, gemengde conventies, dubbelzinnige namen
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- is dit 0/1? een verificatieniveau?
    lastLogin       TIMESTAMP,       -- camelCase in een snake_case wereld
    created         TIMESTAMP        -- created wat? wanneer?
);

Nog één ding: gebruik nooit gereserveerde woorden als kolomnamen. type, order, user, group, table — ze werken allemaal als je ze quoted, maar ze bijten je in ORMs, query builders en elke tool voor dynamische SQL-generatie. Gebruik kind in plaats van type, sort_order in plaats van order. Je toekomstige zelf zal je dankbaar zijn.

De Standaardkolommen: Wat Elke Tabel Krijgt#

Elke tabel in mijn schema's begint met hetzelfde skelet. Geen uitzonderingen.

sql

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

Het UUID vs BIGSERIAL Debat#

Dit is een van die beslissingen die veel meer hitte dan licht genereert. Dit is de daadwerkelijke afweging:

BIGSERIAL (auto-incrementerend integer):

8 bytes. Compact. Snel om te indexeren en te joinen.
Geordend — je kunt sorteren op id om invoervolgorde te krijgen.
Voorspelbaar — een gebruiker kan andere IDs raden door de zijne te verhogen.
Werkt niet goed in gedistribueerde systemen (vereist coördinatie).

UUID v4 (willekeurig):

16 bytes. Grotere indexes, langzamere joins (maar zelden de bottleneck).
Onvoorspelbaar — geen informatielekkage.
Werkt in gedistribueerde systemen zonder coördinatie.
Verschrikkelijke index-lokaliteit — willekeurige UUIDs fragmenteren B-tree indexes.

UUID v7 (tijdgesorteerd, RFC 9562):

16 bytes, maar tijdsgeordend dus B-tree lokaliteit is uitstekend.
Onvoorspelbaar genoeg voor extern gebruik.
Relatief nieuw, maar PostgreSQL 17+ heeft gen_random_uuid() en je kunt uuid_generate_v7() gebruiken met extensions.

Mijn huidige standpunt: BIGSERIAL voor interne tabellen, UUID v7 voor alles dat aan de buitenwereld wordt blootgesteld. Als een ID ooit verschijnt in een URL, een API-response of een webhook-payload, gebruik UUIDs. Als het een puur join-tabel is die gebruikers nooit zien, is BIGSERIAL prima.

sql

-- Voor een API-gerichte 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()
);
 
-- Voor een interne mapping-tabel
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: Altijd TIMESTAMPTZ#

Gebruik TIMESTAMPTZ, niet TIMESTAMP. De "TZ"-versie slaat de waarde op in UTC en converteert bij het lezen op basis van de sessie-tijdzone. De non-TZ versie slaat op wat je erin stopt zonder tijdzone-context — wat betekent dat als twee servers in verschillende tijdzones naar dezelfde tabel schrijven, je stille datacorruptie krijgt.

sql

-- Altijd dit
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Nooit dit
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

De updated_at Trigger#

PostgreSQL heeft niet MySQL's ON UPDATE CURRENT_TIMESTAMP. Je hebt een trigger nodig:

sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Pas toe op elke tabel die updated_at heeft
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();

Ja, je hebt één trigger per tabel nodig. Ja, het is vervelend. Schrijf een migratie-helper die de trigger automatisch aanmaakt wanneer je een tabel toevoegt. Het is het waard, want het alternatief is onthouden om updated_at = NOW() te zetten in elke UPDATE-query door je hele applicatie — en je zult het vergeten.

Normalisatie: Wanneer de Regels Breken#

Elke informatica-opleiding leert normalisatie tot 3NF (Derde Normaalvorm). De regels zijn:

1NF: Elke kolom bevat een enkele atomaire waarde. Geen arrays, geen komma-gescheiden lijsten.
2NF: Elke niet-sleutelkolom hangt af van de volledige primary key (relevant voor samengestelde sleutels).
3NF: Geen transitieve afhankelijkheden. Als kolom A kolom B bepaalt, en B bepaalt C, dan hoort C niet in dezelfde tabel als A.

In de praktijk is 3NF de sweet spot voor transactionele tabellen. Je zou daar moeten beginnen en alleen afwijken als je een specifieke, meetbare reden hebt.

Hier is een correct genormaliseerd ordersysteem:

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()
);

Let op unit_price_cents op order_items. Dit is opzettelijke denormalisatie. We maken een snapshot van de prijs op het moment van bestelling, omdat de productprijs later kan veranderen. Dit is een van de meest voorkomende en correcte redenen om te denormaliseren.

Wanneer Denormalisatie de Juiste Keuze Is#

Rapportagetabellen. Als je analytics-dashboard 8 tabellen moet joinen om te renderen, maak dan een gedenormaliseerde rapportagetabel aan en vul die met een achtergrondtaak. Je transactionele schema blijft schoon en je rapportage-queries blijven snel.

sql

-- Gedenormaliseerde rapportagetabel, gevuld door een 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 hier prima
    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()
);

Materialized views. PostgreSQL's MATERIALIZED VIEW wordt onderschat. Het is een gedenormaliseerde snapshot die je op aanvraag ververst. Perfect voor dashboards.

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;
 
-- Ververs het elke nacht
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

JSON-kolommen voor dynamische attributen. Wanneer verschillende rijen verschillende velden nodig hebben — productvarianten, formulierinzendingen, gebruikersvoorkeuren — is een JSONB-kolom vaak beter dan een nachtmerrie-EAV (Entity-Attribute-Value) tabel. Hierover later meer.

De Ene Regel Die Ik Nooit Breek#

Denormaliseer nooit je source-of-truth tabellen. Denormaliseer kopieën, snapshots, rapporten en caches. De canonieke data blijft genormaliseerd. Wanneer de gedenormaliseerde kopie verouderd of corrupt raakt (en dat zal het), herbouw je het vanuit de genormaliseerde bron.

Foreign Keys & Constraints: De Beste Code Die Je Nooit Zult Schrijven#

Ik heb elk excuus gehoord om foreign keys over te slaan. "Ze vertragen schrijfoperaties." "We handhaven het in de applicatie." "We hebben flexibiliteit nodig."

Deze zijn allemaal fout.

Foreign keys zijn het meest impactvolle dat je aan een schema kunt toevoegen. Ze voorkomen hele categorieën bugs die geen enkele hoeveelheid applicatiecode kan opvangen — race conditions, gedeeltelijke failures, verweesde rijen van mislukte transacties. Een foreign key is een garantie van de database-engine zelf, afgedwongen op opslaglaagniveau. De beforeDelete hook van je ORM is een suggestie.

sql

-- Doe dit altijd
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- Het "we lossen het op in code" alternatief:
-- Hoop. Hoop is geen strategie.

ON DELETE Strategieën#

Hier wordt het genuanceerd. Wat gebeurt er als je een parent-rij verwijdert?

RESTRICT (standaard): Het verwijderen faalt als er child-rijen bestaan. Gebruik dit voor de meeste relaties. Je kunt geen klant verwijderen die bestellingen heeft — dat is bedrijfslogica vastgelegd in het schema.

sql

-- Klant kan niet worden verwijderd zolang ze bestellingen hebben
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Child-rijen worden automatisch verwijderd. Gebruik dit spaarzaam en bewust. Goed voor "onderdeel-van" relaties waar de child geen betekenis heeft zonder de parent.

sql

-- Een bestelling verwijderen verwijdert ook de regelitems — ze zijn alleen zinloos
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Een project verwijderen verwijdert de lidmaatschappen
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: De foreign key-kolom wordt op NULL gezet. Gebruik dit wanneer de relatie optioneel is en de child-rij op zichzelf nog steeds zinvol is.

sql

-- Als een manager vertrekt, bestaan hun medewerkers nog steeds — alleen niet-toegewezen
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Mijn vuistregel: standaard RESTRICT, gebruik CASCADE voor compositierelaties, gebruik SET NULL voor optionele associaties. Als je twijfelt, is RESTRICT altijd de veilige keuze — het is makkelijker om een constraint te versoepelen dan om verwijderde data te herstellen.

Check Constraints: Goedkope Verzekering#

Check constraints kosten bijna niets bij schrijfoperaties en voorkomen dat rommeldata voor altijd je systeem binnenkomt:

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()
);

Elke check constraint die je toevoegt is één validatie-bug minder die je ooit in productie zult moeten debuggen. De database is de laatste verdedigingslinie. Gebruik het.

Unique Constraints en Partial Unique Indexes#

Unique constraints zijn eenvoudig voor simpele gevallen:

sql

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Maar ze worden interessant met partial indexes — unique constraints die alleen gelden voor bepaalde rijen:

sql

-- Slechts één actief abonnement per gebruiker (maar ze kunnen veel geannuleerde hebben)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Slechts één primair adres per gebruiker
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Dit is een van de killer features van PostgreSQL. Gebruik het.

Soft Deletes: Het Patroon Dat Iedereen Graag Haat#

Soft deletes zijn simpel in concept: in plaats van DELETE FROM users WHERE id = 42, doe je UPDATE users SET deleted_at = NOW() WHERE id = 42. De rij blijft in de database maar wordt weggefilterd uit normale queries.

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()
);
 
-- De partial index: filter verwijderde rijen efficiënt weg
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Elke query in je applicatie heeft nu WHERE deleted_at IS NULL nodig. Elke. Enkele. Query. ORMs helpen hierbij (Prisma heeft middleware, Drizzle heeft .where(isNull(deletedAt))), maar het is een belasting op elke leesoperatie. Vergeet het één keer en je toont "verwijderde" data aan gebruikers.

Het Unique Constraint Probleem#

Hier worden soft deletes lelijk. Als je UNIQUE (email) hebt en een gebruiker soft-deletet hun account, staat hun e-mail nog in de tabel. Ze kunnen zich niet opnieuw registreren met hetzelfde e-mailadres. Een nieuwe gebruiker met dat e-mailadres kan zich ook niet aanmelden.

De oplossing is een partial unique index:

sql

-- E-mail moet uniek zijn, maar alleen onder niet-verwijderde gebruikers
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Dit werkt, maar nu moet je dit patroon onthouden voor elke unieke kolom op elke soft-deletable tabel. Het is beheersbaar met discipline, maar het is complexiteit die je hebt gekozen om op je te nemen.

Het Foreign Key Probleem#

Soft deletes werken slecht samen met foreign keys. Als orders.user_id verwijst naar users.id met ON DELETE RESTRICT, en je soft-deletet een gebruiker... gebeurt er niets. De FK vuurt niet omdat je de rij niet echt hebt verwijderd. De gebruiker is "weg" vanuit het perspectief van de applicatie maar nog zeer aanwezig in de database.

Dit betekent dat je applicatiecode het geval moet afhandelen waarbij een gerefereerde entiteit soft-deleted is. Elke join, elke lookup, elke keer dat je een foreign key volgt — je moet deleted_at IS NULL controleren op de gerefereerde tabel ook. Of je doet dat niet, en je applicatie toont "Bestelling door [verwijderde gebruiker]" wat ofwel een bug of een feature is, afhankelijk van wie je het vraagt.

Mijn Aanpak#

Ik gebruik soft deletes alleen voor gebruikersgerichte entiteiten waar herstel een bedrijfseis is — gebruikersaccounts, projecten, documenten. Dingen waar een support-medewerker misschien een verwijdering moet terugdraaien. Voor al het andere gebruik ik hard deletes met een audit trail (hierover zo meer).

sql

-- Soft delete: gebruikersgericht, herstelbaar
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: intern, niet herstelbaar vanuit UI (maar geaudit)
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()
);
-- Bij verwijdering is het weg. De audit_log registreert dat het bestond.

Audit Trails: Weet Wat Er Veranderd Is en Wie Het Deed#

Elke niet-triviale applicatie heeft op een gegeven moment een antwoord nodig op "wat is er met dit record gebeurd?" Audit trails zijn hoe je dat antwoord geeft zonder door applicatielogs te spitten.

Het Aparte Audit Tabel Patroon#

De simpelste aanpak: een enkele audit_log tabel die elke wijziging vastlegt.

sql

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text om zowel UUID als BIGINT PKs aan te kunnen
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL voor INSERT
    new_values      JSONB,                  -- NULL voor DELETE
    changed_fields  TEXT[],                 -- welke kolommen veranderden (voor 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);

De uitdaging is het vullen ervan. Je kunt het doen in applicatiecode (expliciet, maar makkelijk te vergeten) of met triggers (automatisch, maar moeilijker om context zoals changed_by door te geven).

De Trigger-gebaseerde Aanpak#

Triggers vangen elke wijziging automatisch op, zelfs van rauwe SQL of database-adminoperaties:

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);
        -- Vind gewijzigde velden
        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;
        -- Log alleen als er daadwerkelijk iets veranderd is
        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;
 
-- Pas toe op tabellen die je wilt auditen
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();

Het nadeel: triggers weten niet welke applicatiegebruiker de wijziging maakte. Je kunt dit omzeilen met sessievariabelen:

sql

-- In je applicatie, voor de query:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- In de trigger-functie:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Dit werkt maar voelt kwetsbaar. In de praktijk gebruik ik een hybride aanpak: triggers voor de datavastlegging, en applicatiecode om de sessiecontext in te stellen.

Het Geschiedenistabel Patroon#

Voor tabellen waar je de volledige versiegeschiedenis nodig hebt (niet alleen "wat veranderde" maar "wat was de staat op tijdstip T"), is een dedicated geschiedenistabel schoner:

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)
);

Kopieer voor elke update van documents de huidige staat naar document_history en verhoog de versie. Nu kun je het document op elk willekeurig moment reconstrueren, diffs tussen versies tonen, en zelfs oude versies herstellen.

De afweging is opslag. Als je content-kolom groot is en vaak verandert, kan de geschiedenistabel snel groeien. Voor de meeste applicaties is dit prima — opslag is goedkoop en je kunt oude versies archiveren naar koude opslag indien nodig.

Multi-Tenancy: Drie Benaderingen, Kies Je Pijn#

Multi-tenancy is een van die dingen die makkelijk toe te voegen is aan het begin en bijna onmogelijk later. Als er enige kans is dat je applicatie meerdere organisaties gaat bedienen, bouw het dan in vanaf dag één.

Rijniveau: tenant_id op Elke Tabel#

De meest voorkomende aanpak. Elke tabel heeft een tenant_id-kolom en elke query filtert erop.

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()
);
 
-- Elke index zou tenant_id moeten bevatten voor query-performance
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

Het risico is datalekkage — één gemiste WHERE tenant_id = ... en je toont data van Tenant A aan Tenant B. PostgreSQL's Row-Level Security (RLS) elimineert deze klasse van bugs:

sql

-- Activeer RLS op de tabel
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Maak een policy gebaseerd op een sessievariabele
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Dwing RLS af zelfs voor table owners
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Nu, zelfs als je applicatiecode de WHERE tenant_id = ... clausule vergeet, voegt PostgreSQL die automatisch toe. Dit is defense in depth, en het is een van de sterkste argumenten voor PostgreSQL in multi-tenant systemen.

sql

-- In de connection-middleware van je applicatie:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Nu worden alle queries op RLS-ingeschakelde tabellen automatisch gefilterd
SELECT * FROM projects;
-- PostgreSQL voegt intern toe: WHERE tenant_id = 'tenant-uuid-here'

Voordelen: Enkele database, simpele ops, efficiënt resourcegebruik, makkelijke cross-tenant queries voor admin. Nadelen: Vereist discipline (of RLS), elke query raakt tenant_id, moeilijker om tenants hun eigen backup/restore te geven.

Schema-Per-Tenant#

Elke tenant krijgt zijn eigen PostgreSQL-schema. Alle schema's delen dezelfde database, maar de tabellen zijn geïsoleerd per namespace.

sql

-- Maak een schema aan voor elke tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tabellen leven in het schema van de tenant
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Stel het search_path in voor de huidige tenant
SET search_path TO tenant_acme, public;
 
-- Nu raken ongekwalificeerde queries het schema van de tenant
SELECT * FROM projects;  -- bevraagt tenant_acme.projects

Voordelen: Sterke isolatie, geen risico op cross-tenant datalekkage, makkelijke per-tenant backup/restore, tenants kunnen schemavariaties hebben. Nadelen: Complexiteit bij schemamigratie (je moet N schema's migreren), connection pool management, PostgreSQL heeft praktische limieten rond ~10.000 schema's.

Database-Per-Tenant#

Elke tenant krijgt zijn eigen database. Maximale isolatie.

Voordelen: Complete isolatie, onafhankelijke schaling, makkelijke backup/restore, kan grote tenants op dedicated hardware plaatsen. Nadelen: Nachtmerrie voor connection management, cross-tenant queries onmogelijk, migratie moet N keer draaien, aanzienlijke operationele overhead.

Welke Kies Je?#

Voor de meeste SaaS-applicaties: begin met rijniveau + RLS. Het is het simpelst om te beheren, en RLS geeft je sterke-genoeg isolatie voor het overgrote deel van de use cases. Stap alleen over naar schema-per-tenant als je contractuele isolatie-eisen hebt (enterprise-klanten, gereguleerde sectoren). Database-per-tenant is voor wanneer je absoluut fysieke isolatie moet garanderen — en zelfs dan, overweeg managed databases waar de operationele last voor je wordt afgehandeld.

JSON/JSONB Kolommen: Het Noodluik#

PostgreSQL's JSONB is opmerkelijk. Het geeft je document-database flexibiliteit binnen een relationeel systeem. Maar zoals elk krachtig gereedschap is het makkelijk om te misbruiken.

Wanneer JSONB de Juiste Keuze Is#

Dynamische door gebruikers gedefinieerde attributen. Een e-commerce platform waar elke productcategorie verschillende attributen heeft — schoenen hebben size en color, elektronica heeft voltage en wattage. In plaats van een EAV-tabel of een kolom voor elk mogelijk attribuut:

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()
);
 
-- Voorbeelddata:
-- Schoen: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Applicatie-instellingen en configuratie. Gebruikersvoorkeuren, feature flags, notificatie-instellingen — dingen die regelmatig van vorm veranderen en geen relationele integriteit nodig hebben.

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()
);

Webhook-payloads, API-responses, eventdata. Alles waar het schema wordt bepaald door een extern systeem en zonder waarschuwing kan veranderen.

GIN Indexes op JSONB#

Zonder indexes vereist het doorzoeken van JSONB een full table scan. GIN-indexes maken het snel:

sql

-- Index de hele JSONB-kolom (goed voor @> containment queries)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Query: vind alle producten met color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Query: vind producten met een van deze attributen
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Voor specifieke paden die je vaak bevraagt, is een gerichte index efficiënter:

sql

-- Index een specifiek pad
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Nu is dit een gewone B-tree lookup
SELECT * FROM products
WHERE attributes->>'color' = 'black';

De Migratieval#

Hier bijt JSONB je: je kunt niet makkelijk NOT NULL constraints of standaardwaarden toevoegen aan velden in JSON. Als je een nieuw verplicht veld toevoegt aan je productattributen, moet je elke bestaande rij backfillen. Met een gewone kolom handelt een migratie dit atomair af. Met JSONB schrijf je een UPDATE die elke rij raakt en hoop je dat je applicatiecode ontbrekende velden netjes afhandelt totdat de backfill compleet is.

sql

-- Een nieuwe kolom toevoegen: schoon, atomair, één statement
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Een nieuw JSONB-veld toevoegen: rommelig, vereist een backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Plus applicatiecode updaten om ontbrekende weight_kg af te handelen
-- Plus validatielogica updaten
-- Plus elke API-response die attributes bevat updaten

Mijn regel: als je een JSONB-veld meer dan twee keer per week bevraagt in een WHERE-clausule, zou het waarschijnlijk een kolom moeten zijn. JSONB is een geweldig noodluik. Het is een verschrikkelijke standaard.

Schemamigraties: Breek de Productie Niet om 3 Uur 's Nachts#

Schemamigraties zijn waar theorie de realiteit ontmoet. Je schema ziet er geweldig uit op papier, maar nu moet je een tabel met 50 miljoen rijen wijzigen tijdens kantooruren zonder enige downtime.

Migratietools#

Ik heb de meeste gebruikt. Korte indrukken:

Drizzle (TypeScript): Momenteel mijn favoriet. Schema-als-code, type-safe queries gegenereerd vanuit het schema, schone migratie-SQL. Het push-commando voor development is snel.

Prisma (TypeScript): Geweldige DX voor simpele schema's. Worstelt met geavanceerde PostgreSQL-features (partial indexes, custom types, RLS). De migratie-engine kan verrassende beslissingen nemen.

Flyway (Java/CLI): Rotsvast, beproefd, SQL-eerst. Als je rauwe SQL-migraties schrijft, volgt Flyway ze betrouwbaar. Geen magie, geen verrassingen.

golang-migrate (Go/CLI): Vergelijkbaar met Flyway maar lichter. Geweldig voor Go-projecten of wanneer je gewoon een simpele up/down migratie-runner wilt.

Het Zero-Downtime Probleem#

De gevaarlijkste schemawijzigingen zijn de wijzigingen die de tabel locken. In PostgreSQL lockte ALTER TABLE ... ADD COLUMN met een standaardwaarde vroeger de hele tabel voor de duur van de herschrijving. Sinds PostgreSQL 11 zijn simpele standaardwaarden (constanten) alleen metadata en instant. Maar andere operaties locken nog steeds:

sql

-- VEILIG: alleen metadata, instant (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- GEVAARLIJK: herschrijft de hele tabel, volledige tabellock
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- GEVAARLIJK: scant de hele tabel om te valideren
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

NOT NULL Kolommen Veilig Toevoegen#

Je kunt niet zomaar NOT NULL toevoegen aan een bestaande tabel met data — het faalt omdat bestaande rijen geen waarde hebben. De naïeve aanpak:

sql

-- Dit lockt de tabel en herschrijft het. Doe dit niet op een grote tabel.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Sinds PostgreSQL 11 is dit eigenlijk veilig voor constante standaardwaarden — het is alleen metadata. Maar als je standaardwaarde een functie is of je moet backfillen met berekende waarden, gebruik dan het expand-contract patroon.

Het Expand-Contract Patroon#

Dit is de gouden standaard voor zero-downtime schemawijzigingen. Drie fases:

Fase 1: Expand — Voeg de nieuwe kolom toe als nullable. Deploy applicatiecode die naar zowel oude als nieuwe kolommen schrijft.

sql

-- Migratie 1: Voeg de nullable kolom toe
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Fase 2: Migrate — Backfill bestaande rijen in batches. Je applicatie schrijft al naar de nieuwe kolom voor nieuwe data.

sql

-- Migratie 2: Backfill in batches (doe dit niet in één statement voor grote tabellen)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Herhaal totdat alle rijen zijn bijgewerkt

Fase 3: Contract — Zodra alle rijen zijn bijgewerkt, voeg de NOT NULL constraint toe en verwijder de oude kolom (indien van toepassing).

sql

-- Migratie 3: Voeg constraint toe (gebruik NOT VALID om full table scan te vermijden, valideer daarna apart)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migratie 4: Valideer de constraint (neemt een ShareUpdateExclusiveLock, geen AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

De NOT VALID + VALIDATE CONSTRAINT truc is cruciaal. Een check constraint normaal toevoegen scant de hele tabel terwijl het een zware lock vasthoudt. NOT VALID voegt de constraint toe zonder te scannen (het geldt alleen voor nieuwe schrijfoperaties), en VALIDATE CONSTRAINT scant met een lichtere lock die lees- en schrijfoperaties niet blokkeert.

Index Aanmaken#

Indexes aanmaken op grote tabellen blokkeert standaard schrijfoperaties. Gebruik altijd CONCURRENTLY:

sql

-- BLOKKEERT SCHRIJFOPERATIES: doe dit niet op een live tabel
CREATE INDEX idx_users_email ON users (email);
 
-- NIET-BLOKKEREND: gebruik dit in plaats daarvan
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY duurt langer en kan niet binnen een transactie draaien, maar het lockt de tabel niet. De afweging is altijd de moeite waard in productie.

De Beslissingen Waar Ik Spijt Van Heb#

Elk schema heeft spijt. Hier zijn de mijne.

SERIAL Gebruiken in Plaats van UUID voor Externe IDs#

Vroeg in een project gebruikte ik SERIAL primary keys en stelde ze direct bloot in URLs: /users/42, /orders/1337. Dit lekte informatie (concurrenten konden ons gebruikersaantal raden), maakte paginering raadbaar, en brak toen we databases uit twee regio's moesten samenvoegen. Overschakelen naar UUIDs voor extern-gerichte IDs vereiste een migratie van meerdere maanden.

Les: gebruik UUIDs voor alles dat buiten je database wordt blootgesteld. Gebruik SERIAL/BIGSERIAL voor interne join-tabellen als je wilt, maar laat nooit een auto-incrementerend integer in een URL verschijnen.

Geen Constraints ("We Voegen Ze Later Toe")#

We lanceerden een tabel zonder check constraints omdat "we snel bewegen en we voegen ze later toe." Binnen twee weken had de data negatieve prijzen, lege namen en een order_status waarde van "oof" die iemand tijdens het testen had ingetypt en nooit had opgeruimd.

Constraints achteraf toevoegen vereiste:

Alle ongeldige data vinden
Beslissen wat ermee te doen (repareren, verwijderen, of grandfatheren)
Een migratie schrijven die de data backfilt/repareert EN de constraint toevoegt

Dit duurde langer dan de constraint op dag één toevoegen zou hebben geduurd. Begin met constraints. Versoepel ze als het moet. Nooit andersom.

Komma-gescheiden Strings in Plaats van Arrays of Join-tabellen#

Ik heb ooit tags opgeslagen als komma-gescheiden string: "javascript,react,nextjs". Doorzoeken was een nachtmerrie:

sql

-- Dit is hoe je komma-gescheiden waarden bevraagt. Doe dit niet.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- Dit matcht ook "react-native" en "preact"
 
-- Wat ik had moeten doen:
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)
);

Of als minimum, gebruik PostgreSQL's native array-type:

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);
 
-- Schone queries
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Arrays zijn acceptabel voor simpele lijsten die geen eigen attributen nodig hebben. Op het moment dat je metadata nodig hebt over de relatie (zoals "wie heeft deze tag toegevoegd" of "wanneer is het toegevoegd"), heb je een join-tabel nodig.

"type" Gebruiken als Kolomnaam#

sql

-- Leek prima op dat moment
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Dan probeer je het te bevragen in letterlijk elke ORM:
-- notification.type  <- conflicteert met het type-systeem van elke taal
-- "type" is een gereserveerd woord in de meeste SQL-dialecten
-- Je eindigt met het overal quoten: SELECT "type" FROM notifications

Gebruik kind, category of notification_type. Alles behalve type. Vermijd op dezelfde manier order (gebruik sort_order of position), user (gebruik account of prefix het) en group (gebruik team of group_name).

Geen created_at Toevoegen Vanaf het Begin#

Eén tabel kreeg geen created_at omdat "we het niet nodig hebben." Drie maanden later moesten we een probleem debuggen en hadden we geen idee wanneer records waren aangemaakt. Het achteraf toevoegen betekende dat alle bestaande rijen dezelfde timestamp kregen (de migratietimestamp), waardoor de historische data nutteloos was.

Elke tabel krijgt created_at. Geen uitzonderingen. De kosten zijn één kolom. De kosten van het niet hebben ervan zijn onkenbaar totdat je het nodig hebt.

Bedrijfslogica in Database Views Plaatsen#

Ik heb ooit een keten van views gemaakt — active_users filterde users, premium_active_users filterde active_users, en een rapportage-view joinede ze allemaal. Het werkte geweldig totdat iemand de users-tabel wijzigde en alle drie de views stilletjes braken. De query planner had ook moeite om te optimaliseren door meerdere view-lagen heen.

Views zijn geweldig voor gemak en read-only access patterns. Ze zijn verschrikkelijk als plek om bedrijfslogica te coderen die verandert. Houd bedrijfsregels in applicatiecode waar ze geversioneerd, getest en deploybaar zijn. Gebruik views voor rapportagesnelkoppelingen, niet als architectonische bouwstenen.

Het Geheel Samenvoegen#

Hier is hoe een goed ontworpen schema eruitziet wanneer je deze patronen combineert. Een vereenvoudigd projectmanagementsysteem:

sql

-- Extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- voor gen_random_uuid()
 
-- Updated_at trigger-functie (herbruikbaar)
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();
 
-- Activeer 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 (geen RLS — alleen-admin tabel)
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);

Let op de patronen:

Elke tabel heeft tenant_id en RLS ingeschakeld (behalve tenants en audit_log).
Elke tabel heeft created_at en updated_at met triggers.
UUIDs voor alle primary keys (extern-gerichte entiteiten).
Check constraints op status-enums, lengtes en bereiken.
Foreign keys met passend ON DELETE-gedrag.
Indexes ontworpen voor de access patterns (tenant + project, tenant + status).
Soft deletes alleen op users (waar accountherstel nodig is), status-gebaseerde archivering op projects.

Afsluitende Gedachten#

Schema-ontwerp is niet glamoureus. Niemand heeft ooit een conferentielezing gegeven met de titel "Ik Heb Check Constraints Toegevoegd aan Elke Tabel en Het Bespaarde Ons Zes Maanden aan Debugging." Maar dat is precies wat goed schema-ontwerp doet — het voorkomt problemen zo stilletjes dat je nooit weet dat ze hadden kunnen bestaan.

De patronen in dit artikel zijn niet nieuw. Ze zijn het resultaat van jarenlang migraties schrijven, datacorruptie debuggen en schema's refactoren onder productiebelasting. Elk ervan bestaat omdat ik, of iemand met wie ik werkte, het eerst op de andere manier deed en de prijs betaalde.

Begin met constraints. Gebruik foreign keys. Voeg created_at toe aan alles. Kies een naamconventie en dwing die meedogenloos af. Gebruik RLS voor multi-tenancy. Wees voorzichtig met JSONB. Test je migraties tegen productie-omvang data voordat je ze deployt.

De database is het fundament. Doe het goed, en alles wat erop gebouwd wordt, wordt simpeler. Doe het fout, en geen enkele hoeveelheid slimme applicatiecode kan je redden.