Databasschemadesign: Mönster som åldras väl

Ett schema är ett kontrakt med ditt framtida jag. Varje kolumn du lägger till, varje constraint du hoppar över, varje "vi fixar det senare" — allt ackumuleras. Jag har arbetat med system där ett enda dåligt schemabeslut för tre år sedan nu kostar teamet en hel sprint varje kvartal i workarounds.

Databasen överlever allt. Ditt frontend-ramverk kommer att ändras. Ditt API-lager kommer att skrivas om. Din deploymentstrategi kommer att utvecklas. Men datan? Datan stannar. Och formen du gav den dag ett följer dig för evigt, eftersom att migrera en tabell med 200 miljoner rader inte är samma sak som att refaktorera en React-komponent.

Det här är vad jag har lärt mig om att fatta schemabeslut som inte hemsöker dig. PostgreSQL-fokuserat, eftersom det är vad jag använder och litar på, men de flesta av dessa mönster gäller för alla relationsdatabaser.

Namnkonventioner: Den tråkiga saken som spelar mest roll#

Jag har sett fler diskussioner om namnkonventioner än om faktiska arkitekturbeslut. Här är vad jag har landat i efter år av kontextväxling mellan projekt:

Snake_case för allting. Tabeller, kolumner, index, constraints. Ingen camelCase, ingen PascalCase. PostgreSQL foldar okvalificerade identifierare till gemener ändå, så createdAt blir createdat om du inte omger det med dubbla citattecken överallt. Slåss inte mot databasen.

Plurala tabellnamn. En tabell innehåller många rader. users inte user. orders inte order. order_items inte order_item. Det läses naturligt i frågor: SELECT * FROM users WHERE ... — du väljer från en samling.

_id-suffix för foreign keys. user_id, order_id, tenant_id. Primärnyckeln är bara id. Det är entydigt. När du joinar tabeller läser users.id = orders.user_id som vanlig engelska.

_at-suffix för tidsstämplar. created_at, updated_at, deleted_at, published_at, expires_at. Du vet alltid att det är en tidpunkt.

is_-prefix för booleans. is_active, is_verified, is_published. Vissa använder has_ för ägandebooleans (has_mfa_enabled), men jag håller det enkelt och använder is_ för allt.

_count-suffix för denormaliserade räknare. comment_count, follower_count. Gör det tydligt att det är ett cachat nummer, inte en liveberäkning.

Här är grejen: konsekvens slår perfektion. Jag har sett team spendera veckor på att debattera om det ska vara email_address eller email eller email_addr. Välj bara ett mönster och tillämpa det överallt. Den sämsta namnkonventionen är den som tillämpas inkonsekvent.

sql

-- Bra: konsekvent, läsbart, inga överraskningar
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()
);
 
-- Dåligt: inkonsekvent casing, blandade konventioner, tvetydiga namn
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- är det 0/1? en verifieringsnivå?
    lastLogin       TIMESTAMP,       -- camelCase i en snake_case-värld
    created         TIMESTAMP        -- skapade vad? när?
);

En sak till: använd aldrig reserverade ord som kolumnnamn. type, order, user, group, table — de fungerar alla om du citerar dem, men de kommer att bränna dig i ORM:er, query builders och varje dynamiskt SQL-genereringsverktyg. Använd kind istället för type, sort_order istället för order. Ditt framtida jag kommer att tacka dig.

Standardkolumner: Vad varje tabell får#

Varje tabell i mina scheman börjar med samma skelett. Inga undantag.

sql

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

Debatten UUID vs BIGSERIAL#

Det här är ett av de beslut som genererar mycket mer hetta än ljus. Här är den faktiska avvägningen:

BIGSERIAL (autoinkrementande heltal):

8 bytes. Kompakt. Snabb att indexera och joina.
Ordnad — du kan sortera på id för att få insättningsordning.
Förutsägbar — en användare kan gissa andra ID:n genom att inkrementera sitt.
Fungerar inte bra i distribuerade system (kräver koordination).

UUID v4 (slumpmässig):

16 bytes. Större index, långsammare joins (men sällan flaskhalsen).
Oförutsägbar — ingen informationsläckage.
Fungerar i distribuerade system utan koordination.
Fruktansvärd indexlokalitet — slumpmässiga UUID:er fragmenterar B-tree-index.

UUID v7 (tidssorterad, RFC 9562):

16 bytes, men tidsordnad så B-tree-lokaliteten är utmärkt.
Tillräckligt oförutsägbar för extern användning.
Relativt ny, men PostgreSQL 17+ har gen_random_uuid() och du kan använda uuid_generate_v7() med tillägg.

Min nuvarande ståndpunkt: BIGSERIAL för interna tabeller, UUID v7 för allt som exponeras mot omvärlden. Om ett ID någonsin dyker upp i en URL, ett API-svar eller en webhook-payload, använd UUID:er. Om det är en ren join-tabell som användare aldrig ser, duger BIGSERIAL.

sql

-- För en API-exponerad resurs
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()
);
 
-- För en intern mappningstabell
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)
);

Tidsstämplar: Alltid TIMESTAMPTZ#

Använd TIMESTAMPTZ, inte TIMESTAMP. "TZ"-versionen lagrar värdet i UTC och konverterar vid läsning baserat på sessionens tidszon. Versionen utan TZ lagrar vad du än ger den utan tidszonskontext — vilket innebär att om två servrar i olika tidszoner skriver till samma tabell, får du tyst datakorruption.

sql

-- Alltid detta
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Aldrig detta
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

Triggern för updated_at#

PostgreSQL har inte MySQLs ON UPDATE CURRENT_TIMESTAMP. Du behöver en trigger:

sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Tillämpa på varje tabell som har 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();

Ja, du behöver en trigger per tabell. Ja, det är tröttsamt. Skriv en migrationshjälpare som skapar triggern automatiskt när du lägger till en tabell. Det är värt det, eftersom alternativet är att komma ihåg att sätta updated_at = NOW() i varje UPDATE-fråga i hela din applikation — och du kommer att glömma.

Normalisering: När man bryter reglerna#

Varje CS-kurs lär ut normalisering upp till 3NF (Tredje normalformen). Reglerna är:

1NF: Varje kolumn innehåller ett enda atomärt värde. Inga arrayer, inga kommaseparerade listor.
2NF: Varje icke-nyckelkolumn beror på hela primärnyckeln (relevant för sammansatta nycklar).
3NF: Inga transitiva beroenden. Om kolumn A bestämmer kolumn B, och B bestämmer C, bör C inte vara i samma tabell som A.

I praktiken är 3NF den optimala punkten för transaktionella tabeller. Du bör börja där och avvika bara när du har en specifik, mätbar anledning.

Här är ett korrekt normaliserat ordersystem:

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

Lägg märke till unit_price_cents på order_items. Det är avsiktlig denormalisering. Vi tar en ögonblicksbild av priset vid ordertidpunkten, eftersom produktens pris kan ändras senare. Det här är en av de vanligaste och mest korrekta anledningarna att denormalisera.

När denormalisering är rätt val#

Rapporteringstabeller. Om din analysdashboard behöver joina 8 tabeller för att rendera, skapa en denormaliserad rapporteringstabell och populera den med ett bakgrundsjobb. Ditt transaktionella schema förblir rent, och dina rapporteringsfrågor förblir snabba.

sql

-- Denormaliserad rapporteringstabell, populerad av ett cronjobb
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 är ok här
    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()
);

Materialiserade vyer. PostgreSQLs MATERIALIZED VIEW är underskattad. Det är en denormaliserad ögonblicksbild som du uppdaterar på begäran. Perfekt för 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;
 
-- Uppdatera den nattligt
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

JSON-kolumner för dynamiska attribut. När olika rader behöver olika fält — produktvarianter, formulärinlämningar, användarpreferenser — är en JSONB-kolumn ofta bättre än en mardrömslik EAV-tabell (Entity-Attribute-Value). Mer om detta senare.

Den enda regeln jag aldrig bryter#

Denormalisera aldrig dina källtabeller. Denormalisera kopior, ögonblicksbilder, rapporter och cachar. Den kanoniska datan förblir normaliserad. När den denormaliserade kopian blir inaktuell eller korrupt (och det kommer den att bli), bygger du om den från den normaliserade källan.

Foreign Keys & Constraints: Den bästa koden du aldrig kommer skriva#

Jag har hört alla ursäkter för att skippa foreign keys. "De saktar ner skrivningar." "Vi hanterar det i applikationen." "Vi behöver flexibilitet."

Alla dessa är fel.

Foreign keys är det enskilt mest effektfulla du kan lägga till i ett schema. De förhindrar hela kategorier av buggar som ingen mängd applikationskod kan fånga — race conditions, partiella misslyckanden, föräldralösa rader från misslyckade transaktioner. En foreign key är en garanti från databasmotorn själv, upprätthållen på lagringsnivå. Din ORM:s beforeDelete-hook är ett förslag.

sql

-- Gör alltid detta
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- Alternativet "vi hanterar det i kod":
-- Hopp. Hopp är inte en strategi.

ON DELETE-strategier#

Det är här det blir nyanserat. Vad händer när du tar bort en föräldrarad?

RESTRICT (standard): Borttagningen misslyckas om barnrader finns. Använd detta för de flesta relationer. Du kan inte ta bort en kund som har ordrar — det är affärslogik kodad i schemat.

sql

-- Kunden kan inte tas bort medan de har ordrar
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Barnrader tas automatiskt bort. Använd detta sparsamt och medvetet. Bra för "del-av"-relationer där barnet inte har någon mening utan föräldern.

sql

-- Att ta bort en order tar bort dess radartiklar — de är meningslösa ensamma
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Att ta bort ett projekt tar bort dess medlemskap
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: Foreign key-kolumnen sätts till NULL. Använd detta när relationen är valfri och barnraden fortfarande är meningsfull på egen hand.

sql

-- Om en chef slutar finns deras underställda fortfarande kvar — bara otilldelade
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Min tumregel: standardvalet är RESTRICT, använd CASCADE för kompositionsrelationer, använd SET NULL för valfria associationer. Om du är osäker är RESTRICT alltid det säkra valet — det är lättare att lätta på en begränsning än att återställa borttagen data.

Check Constraints: Billig försäkring#

Check constraints kostar nästan ingenting vid skrivning och förhindrar skräpdata från att komma in i ditt system för evigt:

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

Varje check constraint du lägger till är en färre valideringsbugg du någonsin kommer behöva felsöka i produktion. Databasen är sista försvarslinjen. Använd den.

Unika begränsningar och partiella unika index#

Unika begränsningar är enkla för simpla fall:

sql

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Men de blir intressanta med partiella index — unika begränsningar som bara gäller för vissa rader:

sql

-- Bara en aktiv prenumeration per användare (men de kan ha många avbrutna)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Bara en primär adress per användare
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Det här är en av PostgreSQLs viktigaste funktioner. Använd den.

Soft Deletes: Mönstret alla älskar att hata#

Soft deletes är enkla i konceptet: istället för DELETE FROM users WHERE id = 42, gör du UPDATE users SET deleted_at = NOW() WHERE id = 42. Raden stannar i databasen men filtreras bort från normala frågor.

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()
);
 
-- Det partiella indexet: filtrera bort borttagna rader effektivt
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Varje fråga i din applikation behöver nu WHERE deleted_at IS NULL. Varje. Enda. Fråga. ORM:er hjälper med detta (Prisma har middleware, Drizzle har .where(isNull(deletedAt))), men det är en skatt på varje läsoperation. Missa det en gång och du visar "borttagen" data för användare.

Problemet med unika begränsningar#

Här blir soft deletes fula. Om du har UNIQUE (email) och en användare soft-deletar sitt konto, finns deras e-post fortfarande i tabellen. De kan inte registrera sig igen med samma e-post. En ny användare med den e-posten kan inte heller registrera sig.

Lösningen är ett partiellt unikt index:

sql

-- E-post måste vara unik, men bara bland icke-borttagna användare
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Det fungerar, men nu behöver du komma ihåg detta mönster för varje unik kolumn på varje soft-deletebar tabell. Det är hanterbart med disciplin, men det är komplexitet du har valt att ta på dig.

Problemet med foreign keys#

Soft deletes interagerar dåligt med foreign keys. Om orders.user_id refererar till users.id med ON DELETE RESTRICT, och du soft-deletar en användare... händer ingenting. FK:n triggas inte eftersom du faktiskt inte tog bort raden. Användaren är "borta" ur applikationens perspektiv men fortfarande väldigt närvarande i databasen.

Det innebär att din applikationskod måste hantera fallet där en refererad entitet är soft-deletad. Varje join, varje uppslagning, varje gång du följer en foreign key — du behöver kontrollera deleted_at IS NULL på den refererade tabellen också. Eller så gör du inte det, och din applikation visar "Order av [borttagen användare]" vilket antingen är en bugg eller en feature beroende på vem du frågar.

Min approach#

Jag använder soft deletes bara för användarriktade entiteter där återställning är ett affärskrav — användarkonton, projekt, dokument. Saker där en supportagent kan behöva återställa en borttagning. För allt annat använder jag hard deletes med ett granskningsspår (mer om detta härnäst).

sql

-- Soft delete: användarriktat, återställningsbart
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: internt, inte återställningsbart från UI (men granskat)
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()
);
-- När den tas bort är den borta. audit_log registrerar att den existerade.

Granskningsspår: Vet vad som ändrades och vem som gjorde det#

Varje icke-trivial applikation behöver ett svar på "vad hände med den här posten?" vid någon punkt. Granskningsspår är hur du tillhandahåller det svaret utan att gräva genom applikationsloggar.

Mönstret med separat granskningstabell#

Den enklaste approachen: en enda audit_log-tabell som registrerar varje ändring.

sql

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text för att hantera både UUID och BIGINT PK:er
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL för INSERT
    new_values      JSONB,                  -- NULL för DELETE
    changed_fields  TEXT[],                 -- vilka kolumner som ändrades (för 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);

Utmaningen är att populera den. Du kan göra det i applikationskod (explicit, men lätt att glömma) eller med triggers (automatiskt, men svårare att skicka kontext som changed_by).

Den triggerbaserade approachen#

Triggers fångar varje ändring automatiskt, även från rå SQL eller databasadminoperationer:

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);
        -- Hitta ändrade fält
        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;
        -- Logga bara om något faktiskt ändrades
        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;
 
-- Tillämpa på tabeller du vill granska
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();

Nackdelen: triggers vet inte vilken applikationsanvändare som gjorde ändringen. Du kan lösa det med sessionsvariabler:

sql

-- I din applikation, före frågan:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- I triggerfunktionen:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Det fungerar men känns bräckligt. I praktiken använder jag en hybridapproach: triggers för datafångsten, och applikationskod för att sätta sessionskontexten.

Mönstret med historiktabell#

För tabeller där du behöver den fullständiga versionshistoriken (inte bara "vad ändrades" utan "vad var tillståndet vid tidpunkt T"), är en dedikerad historiktabell renare:

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

Innan varje uppdatering av documents, kopiera det nuvarande tillståndet till document_history och inkrementera versionen. Nu kan du rekonstruera dokumentet vid vilken tidpunkt som helst, visa diffar mellan versioner och till och med återställa gamla versioner.

Avvägningen är lagring. Om din content-kolumn är stor och ändras ofta kan historiktabellen växa snabbt. För de flesta applikationer är det här okej — lagring är billigt och du kan arkivera gamla versioner till kalllagring vid behov.

Multi-Tenancy: Tre tillvägagångssätt, välj din smärta#

Multi-tenancy är en av de saker som är lätta att lägga till i början och nästan omöjliga att lägga till senare. Om det finns minsta chans att din applikation kommer att tjäna flera organisationer, bygg in det från dag ett.

Radnivå: tenant_id på varje tabell#

Den vanligaste approachen. Varje tabell har en tenant_id-kolumn, och varje fråga filtrerar efter den.

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()
);
 
-- Varje index bör inkludera tenant_id för frågeprestanda
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

Risken är dataläckage — ett missat WHERE tenant_id = ... och du visar Tenant A:s data för Tenant B. PostgreSQLs Row-Level Security (RLS) eliminerar denna buggkategori:

sql

-- Aktivera RLS på tabellen
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Skapa en policy baserad på en sessionsvariabel
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Tvinga RLS även för tabellägare
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Nu, även om din applikationskod glömmer WHERE tenant_id = ...-klausulen, lägger PostgreSQL till den automatiskt. Det här är försvar på djupet, och det är ett av de starkaste argumenten för PostgreSQL i multi-tenant-system.

sql

-- I din applikations anslutnings-middleware:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Nu filtreras alla frågor på RLS-aktiverade tabeller automatiskt
SELECT * FROM projects;
-- PostgreSQL lägger internt till: WHERE tenant_id = 'tenant-uuid-here'

Fördelar: Enda databas, enkel drift, effektivt resursanvändande, enkla cross-tenant-frågor för admin. Nackdelar: Kräver disciplin (eller RLS), varje fråga berör tenant_id, svårare att ge tenants egen backup/restore.

Schema-per-tenant#

Varje tenant får sitt eget PostgreSQL-schema. Alla scheman delar samma databas, men tabellerna är isolerade med namnrymd.

sql

-- Skapa ett schema för varje tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tabeller lever i tenantens 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()
);
 
-- Sätt search_path för den aktuella tenanten
SET search_path TO tenant_acme, public;
 
-- Nu träffar okvalificerade frågor tenantens schema
SELECT * FROM projects;  -- frågar tenant_acme.projects

Fördelar: Stark isolering, ingen risk för cross-tenant-dataläckage, enkel per-tenant backup/restore, tenants kan ha schemavariationer. Nackdelar: Komplexitet vid schemamigrering (du behöver migrera N scheman), hantering av anslutningspooler, PostgreSQL har praktiska gränser runt ~10 000 scheman.

Databas-per-tenant#

Varje tenant får sin egen databas. Maximal isolering.

Fördelar: Fullständig isolering, oberoende skalning, enkel backup/restore, kan placera stora tenants på dedikerad hårdvara. Nackdelar: Mardröm med anslutningshantering, cross-tenant-frågor omöjliga, migrering måste köras N gånger, betydande driftöverhead.

Vilken ska man välja?#

För de flesta SaaS-applikationer: börja med radnivå + RLS. Det är enklast att drifta, och RLS ger dig tillräckligt stark isolering för de allra flesta användningsfall. Byt till schema-per-tenant bara om du har avtalade isoleringskrav (företagskunder, reglerade branscher). Databas-per-tenant är för när du absolut måste garantera fysisk isolering — och även då, överväg managed databases där driftbördan hanteras åt dig.

JSON/JSONB-kolumner: Nödutgången#

PostgreSQLs JSONB är anmärkningsvärd. Den ger dig dokumentdatabasflexibilitet inuti ett relationellt system. Men som alla kraftfulla verktyg är den lätt att missbruka.

När JSONB är rätt val#

Dynamiska användardefinerade attribut. En e-handelsplattform där varje produktkategori har olika attribut — skor har size och color, elektronik har voltage och wattage. Istället för en EAV-tabell eller en kolumn för varje möjligt attribut:

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

Applikationsinställningar och konfiguration. Användarpreferenser, feature flags, notifikationsinställningar — saker som ändrar form ofta och inte behöver relationell integritet.

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-svar, händelsedata. Allt där schemat styrs av ett externt system och kan ändras utan förvarning.

GIN-index på JSONB#

Utan index kräver frågor inuti JSONB en full tabellsökning. GIN-index gör det snabbt:

sql

-- Indexera hela JSONB-kolumnen (bra för @> containment-frågor)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Fråga: hitta alla produkter med color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Fråga: hitta produkter med något av dessa attribut
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

För specifika sökvägar du frågar ofta är ett riktat index mer effektivt:

sql

-- Indexera en specifik sökväg
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Nu är det en vanlig B-tree-uppslagning
SELECT * FROM products
WHERE attributes->>'color' = 'black';

Migreringsfällan#

Här biter JSONB dig: du kan inte enkelt lägga till NOT NULL-begränsningar eller standardvärden på fält inuti JSON. Om du lägger till ett nytt obligatoriskt fält i dina produktattribut behöver du backfilla varje befintlig rad. Med en vanlig kolumn hanterar en migrering detta atomärt. Med JSONB skriver du en UPDATE som berör varje rad och hoppas att din applikationskod hanterar saknade fält graciöst tills backfillen är klar.

sql

-- Lägga till en ny kolumn: rent, atomärt, ett uttalande
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Lägga till ett nytt JSONB-fält: rörigt, kräver en backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Plus uppdatera applikationskod för att hantera saknat weight_kg
-- Plus uppdatera valideringslogik
-- Plus uppdatera varje API-svar som inkluderar attribut

Min regel: om du frågar ett JSONB-fält i en WHERE-klausul mer än två gånger i veckan, borde det förmodligen vara en kolumn. JSONB är en utmärkt nödutgång. Den är en fruktansvärd standard.

Schemamigrering: Gå inte sönder produktion klockan 3 på natten#

Schemamigrering är där teori möter verklighet. Ditt schema ser bra ut på papper, men nu behöver du ändra en tabell med 50 miljoner rader under kontorstid utan något driftstopp.

Migreringsverktyg#

Jag har använt de flesta. Korta omdömen:

Drizzle (TypeScript): Min nuvarande favorit. Schema-as-code, typsäkra frågor genererade från schemat, ren migrations-SQL. Push-kommandot för utveckling är snabbt.

Prisma (TypeScript): Bra DX för enkla scheman. Har svårt med avancerade PostgreSQL-funktioner (partiella index, anpassade typer, RLS). Migreringsmotorn kan fatta överraskande beslut.

Flyway (Java/CLI): Stabil som berget, stridstestad, SQL-first. Om du skriver rå SQL-migreringar spårar Flyway dem pålitligt. Ingen magi, inga överraskningar.

golang-migrate (Go/CLI): Liknande Flyway men lättare. Utmärkt för Go-projekt eller när du bara vill ha en enkel up/down-migreringshanterare.

Problemet med noll driftstopp#

De farligaste schemaändringarna är de som låser tabellen. I PostgreSQL brukade ALTER TABLE ... ADD COLUMN med ett standardvärde låsa hela tabellen under hela omskrivningen. Sedan PostgreSQL 11 är enkla standardvärden (konstanter) bara metadata och omedelbara. Men andra operationer låser fortfarande:

sql

-- SÄKERT: bara metadata, omedelbart (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- FARLIGT: skriver om hela tabellen, fullständigt tabelllås
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- FARLIGT: skannar hela tabellen för att validera
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

Lägga till NOT NULL-kolumner säkert#

Du kan inte bara lägga till NOT NULL på en befintlig tabell med data — det misslyckas eftersom befintliga rader inte har ett värde. Den naiva approachen:

sql

-- Detta låser tabellen och skriver om den. Gör inte det på en stor tabell.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Sedan PostgreSQL 11 är detta faktiskt säkert för konstanta standardvärden — det är bara metadata. Men om ditt standardvärde är en funktion eller du behöver backfilla med beräknade värden, använd expand-contract-mönstret.

Expand-Contract-mönstret#

Det här är guldstandarden för schemaändringar utan driftstopp. Tre faser:

Fas 1: Expandera — Lägg till den nya kolumnen som nullable. Deploya applikationskod som skriver till både gamla och nya kolumner.

sql

-- Migrering 1: Lägg till den nullable kolumnen
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Fas 2: Migrera — Backfilla befintliga rader i batcher. Din applikation skriver redan till den nya kolumnen för ny data.

sql

-- Migrering 2: Backfilla i batcher (gör inte det i ett enda uttalande för stora tabeller)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Upprepa tills alla rader är backfillade

Fas 3: Kontraktera — När alla rader är backfillade, lägg till NOT NULL-begränsningen och ta bort den gamla kolumnen (om tillämpligt).

sql

-- Migrering 3: Lägg till begränsning (använd NOT VALID för att undvika full tabellsökning, validera sedan separat)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migrering 4: Validera begränsningen (tar ett ShareUpdateExclusiveLock, inte ett AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

Tricket med NOT VALID + VALIDATE CONSTRAINT är avgörande. Att lägga till en check constraint normalt skannar hela tabellen medan den håller ett tungt lås. NOT VALID lägger till begränsningen utan att skanna (den gäller bara för nya skrivningar), och VALIDATE CONSTRAINT skannar med ett lättare lås som inte blockerar läsningar eller skrivningar.

Skapande av index#

Att skapa index på stora tabeller blockerar skrivningar som standard. Använd alltid CONCURRENTLY:

sql

-- BLOCKERAR SKRIVNINGAR: gör inte det på en levande tabell
CREATE INDEX idx_users_email ON users (email);
 
-- ICKE-BLOCKERANDE: använd detta istället
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY tar längre tid och kan inte köras i en transaktion, men det låser inte tabellen. Avvägningen är alltid värt det i produktion.

Besluten jag har ångrat#

Varje schema har ånger. Här är mina.

Att använda SERIAL istället för UUID för externa ID:n#

Tidigt i ett projekt använde jag SERIAL-primärnycklar och exponerade dem direkt i URL:er: /users/42, /orders/1337. Det läckte information (konkurrenter kunde gissa vårt användarantal), gjorde paginering gissbar och gick sönder när vi behövde slå samman databaser från två regioner. Att byta till UUID:er för externt riktade ID:n krävde en fleramånadersmigrering.

Lärdom: använd UUID:er för allt som exponeras utanför din databas. Använd SERIAL/BIGSERIAL för interna join-tabeller om du vill, men låt aldrig ett autoinkrementande heltal dyka upp i en URL.

Inga begränsningar ("Vi lägger till dem senare")#

Vi lanserade en tabell utan check constraints eftersom "vi rör oss snabbt och lägger till dem senare." Inom två veckor hade datan negativa priser, tomma namn och ett order_status-värde på "oof" som någon skrev under testning och aldrig städade upp.

Att lägga till begränsningar i efterhand krävde:

Hitta all ogiltig data
Bestämma vad man ska göra med den (fixa, ta bort eller göra undantag)
Skriva en migrering som backfillar/fixar datan OCH lägger till begränsningen

Det här tog längre tid än att lägga till begränsningen dag ett hade gjort. Börja med begränsningar. Lätta på dem om du måste. Aldrig tvärtom.

Kommaseparerade strängar istället för arrayer eller join-tabeller#

Jag lagrade en gång taggar som en kommaseparerad sträng: "javascript,react,nextjs". Att fråga var en mardröm:

sql

-- Så här frågar du kommaseparerade värden. Gör inte det.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- Det matchar också "react-native" och "preact"
 
-- Vad jag borde ha gjort:
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)
);

Eller åtminstone, använd PostgreSQLs inbyggda array-typ:

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

Arrayer är acceptabla för enkla listor som inte behöver egna attribut. I det ögonblick du behöver metadata på relationen (som "vem lade till den här taggen" eller "när lades den till"), behöver du en join-tabell.

Att använda "type" som kolumnnamn#

sql

-- Verkade okej vid tillfället
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Sen försöker du fråga det i bokstavligen vilken ORM som helst:
-- notification.type  ← kolliderar med varje språks typsystem
-- "type" är ett reserverat ord i de flesta SQL-dialekter
-- Du hamnar med att citera det överallt: SELECT "type" FROM notifications

Använd kind, category eller notification_type. Vad som helst utom type. Undvik på liknande sätt order (använd sort_order eller position), user (använd account eller lägg till ett prefix) och group (använd team eller group_name).

Att inte lägga till created_at från start#

En tabell fick inte created_at eftersom "vi behöver det inte." Tre månader senare behövde vi felsöka ett problem och hade ingen aning om när poster skapades. Att lägga till det i efterhand innebar att alla befintliga rader fick samma tidsstämpel (migreringstidsstämpeln), vilket gjorde den historiska datan oanvändbar.

Varje tabell får created_at. Inga undantag. Kostnaden är en kolumn. Kostnaden för att inte ha det är okänd tills du behöver det.

Att lägga affärslogik i databasvyer#

Jag skapade en gång en kedja av vyer — active_users filtrerade users, premium_active_users filtrerade active_users, och en rapportvy joinade alla. Det fungerade utmärkt tills någon ändrade users-tabellen och alla tre vyer gick sönder tyst. Frågeoptimeraren hade också svårt att optimera genom flera vylager.

Vyer är utmärkta för bekvämlighet och skrivskyddade åtkomstmönster. De är fruktansvärda som plats att koda affärslogik som ändras. Håll affärsregler i applikationskod där de är versionshanterade, testade och deployabara. Använd vyer för rapporteringsgenvägar, inte som arkitekturella byggstenar.

Att sätta ihop allt#

Så här ser ett väldesignat schema ut när du kombinerar dessa mönster. Ett förenklat projekthanteringssystem:

sql

-- Tillägg
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- för gen_random_uuid()
 
-- updated_at triggerfunktion (återanvändbar)
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();
 
-- Användare
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();
 
-- Aktivera RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Projekt
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);
 
-- Uppgifter
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);
 
-- Uppgiftskommentarer
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);
 
-- Granskningslogg (ingen RLS — tabell bara för administratörer)
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);

Lägg märke till mönstren:

Varje tabell har tenant_id och RLS aktiverat (utom tenants och audit_log).
Varje tabell har created_at och updated_at med triggers.
UUID:er för alla primärnycklar (externt riktade entiteter).
Check constraints på statusenums, längder och intervall.
Foreign keys med lämpligt ON DELETE-beteende.
Index designade för åtkomstmönstren (tenant + project, tenant + status).
Soft deletes bara på users (där kontoåterställning behövs), statusbaserad arkivering på projects.

Avslutande tankar#

Schemadesign är inte glamoröst. Ingen har någonsin hållit en konferensföreläsning med titeln "Jag lade till check constraints på varje tabell och det sparade oss sex månaders felsökning." Men det är exakt vad bra schemadesign gör — det förhindrar problem så tyst att du aldrig ens vet att de skulle ha funnits.

Mönstren i det här inlägget är inte nya. De är resultatet av år av att skriva migreringar, felsöka datakorruption och refaktorera scheman under produktionslast. Var och en av dem finns för att jag, eller någon jag arbetat med, gjorde det på det andra sättet först och betalade priset.

Börja med begränsningar. Använd foreign keys. Lägg till created_at på allt. Välj en namnkonvention och tillämpa den skoningslöst. Använd RLS för multi-tenancy. Var försiktig med JSONB. Testa dina migreringar mot produktionsstora data innan du deployar dem.

Databasen är grunden. Gör den rätt, och allt som byggs ovanpå blir enklare. Gör den fel, och ingen mängd smart applikationskod kan rädda dig.