Datenbankschema-Design: Muster, die gut altern

Ein Schema ist ein Vertrag mit deinem zukünftigen Ich. Jede Spalte, die du hinzufügst, jede Constraint, die du weglässt, jedes „das räumen wir später auf" — alles summiert sich. Ich habe an Systemen gearbeitet, wo eine einzige schlechte Schemaentscheidung vor drei Jahren das Team jedes Quartal einen vollen Sprint an Workarounds kostet.

Die Datenbank überlebt alles. Dein Frontend-Framework wird sich ändern. Deine API-Schicht wird neu geschrieben. Deine Deployment-Strategie wird sich weiterentwickeln. Aber die Daten? Die Daten bleiben. Und die Form, die du ihnen am ersten Tag gegeben hast, folgt dir für immer, denn eine Tabelle mit 200 Millionen Zeilen zu migrieren ist nicht dasselbe wie eine React-Komponente zu refaktorieren.

Das ist, was ich über Schemaentscheidungen gelernt habe, die dich nicht verfolgen. PostgreSQL-fokussiert, weil ich das verwende und dem vertraue, aber die meisten dieser Muster gelten für jede relationale Datenbank.

Namenskonventionen: Die langweilige Sache, die am meisten zählt#

Ich habe mehr Diskussionen über Namenskonventionen als über tatsächliche Architekturentscheidungen erlebt. Hier ist, worauf ich mich nach Jahren des Kontextwechselns zwischen Projekten festgelegt habe:

Snake_case für alles. Tabellen, Spalten, Indizes, Constraints. Kein camelCase, kein PascalCase. PostgreSQL faltet Bezeichner ohne Anführungszeichen sowieso auf Kleinbuchstaben, also wird createdAt zu createdat, es sei denn, du setzt es überall in doppelte Anführungszeichen. Kämpfe nicht gegen die Datenbank.

Singular für Tabellennamen. user, nicht users. order, nicht orders. Dafür gibt es einen einfachen Grund: Wenn du eine Fremdschlüsselspalte benennst, wird es natürlich user_id, nicht users_id. Die Tabelle beschreibt, was eine einzelne Zeile repräsentiert.

...Allerdings nutze ich in der Praxis Plural, weil jeder ORM der Welt Plural erwartet. Prisma generiert users. Drizzle erwartet users. Rails erwartet users. Wähle Konsistenz mit deinen Werkzeugen über theoretische Reinheit.

Englische Namen, immer. Auch wenn dein Team türkisch, japanisch oder deutsch spricht. Die Datenbank sollte für jeden Entwickler lesbar sein, der in Zukunft daran arbeiten könnte. Und SQL-Schlüsselwörter sind Englisch — SELECT * FROM siparisler WHERE durum = 'aktif' liest sich für niemanden natürlich.

Konsistenz gewinnt über Cleverness. Wenn du dich auf ein Muster festgelegt hast, wende es überall an. Schlimmer als eine schlechte Konvention ist eine inkonsistente.

sql

-- ✅ So sieht Konsistenz aus
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    display_name    TEXT NOT NULL,
    password_hash   TEXT NOT NULL,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE posts (
    id              BIGSERIAL PRIMARY KEY,
    author_id       BIGINT NOT NULL REFERENCES users(id),
    title           TEXT NOT NULL,
    slug            TEXT NOT NULL UNIQUE,
    content         TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'published', 'archived')),
    published_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Beachte: TIMESTAMPTZ, nicht TIMESTAMP. Immer. TIMESTAMP ohne Zeitzone bedeutet „Ich habe beschlossen, Zeitzonen-Bugs erst in der Produktion zu entdecken." TIMESTAMPTZ speichert alles in UTC und konvertiert beim Abrufen. Verwende immer TIMESTAMPTZ.

Normalisierung: Die Regeln und wann man sie bricht#

Die Normalisierung existiert nicht, um dein Schema „sauber" zu machen. Sie existiert, um Update-Anomalien zu verhindern — Situationen, in denen das Ändern von Daten an einer Stelle die Daten an einer anderen Stelle inkonsistent macht.

Dritte Normalform: Der Sweet Spot#

Für die meisten Anwendungen ist die Dritte Normalform (3NF) der richtige Grad. Sie sagt: jede Nicht-Schlüssel-Spalte hängt vom Schlüssel ab, vom ganzen Schlüssel und von nichts außer dem Schlüssel.

sql

-- ❌ Nicht normalisiert: Kundendaten in der Bestelltabelle wiederholt
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    customer_name   TEXT NOT NULL,
    customer_email  TEXT NOT NULL,
    product_name    TEXT NOT NULL,
    product_price   INTEGER NOT NULL,
    quantity        INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Problem: Wenn ein Kunde seinen Namen ändert, musst du JEDE Bestellung aktualisieren
 
-- ✅ Normalisiert: Jede Entität in ihrer eigenen Tabelle
CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    email           TEXT NOT NULL UNIQUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customers(id),
    total_cents     INTEGER NOT NULL CHECK (total_cents >= 0),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    created_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()
);

Beachte unit_price_cents auf order_items. Das ist beabsichtigte Denormalisierung. Wir speichern den Preis zum Zeitpunkt der Bestellung, weil sich der Produktpreis später ändern könnte. Das ist einer der häufigsten und korrektesten Gründe für Denormalisierung.

Wann Denormalisierung die richtige Entscheidung ist#

Reporting-Tabellen. Wenn dein Analytics-Dashboard 8 Tabellen joinen muss, um zu rendern, erstelle eine denormalisierte Reporting-Tabelle und befülle sie mit einem Hintergrundjob. Dein transaktionales Schema bleibt sauber, und deine Reporting-Queries bleiben schnell.

sql

-- Denormalisierte Reporting-Tabelle, befüllt durch einen 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 ist hier in Ordnung
    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. PostgreSQLs MATERIALIZED VIEW ist unterschätzt. Es ist ein denormalisierter Snapshot, den du bei Bedarf aktualisierst. 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;
 
-- Nightly aktualisieren
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

JSON-Spalten für dynamische Attribute. Wenn verschiedene Zeilen verschiedene Felder brauchen — Produktvarianten, Formulareinreichungen, Nutzereinstellungen — ist eine JSONB-Spalte oft besser als eine alptraumhafte EAV-Tabelle (Entity-Attribute-Value). Dazu später mehr.

Die eine Regel, die ich nie breche#

Denormalisiere nie deine Source-of-Truth-Tabellen. Denormalisiere Kopien, Snapshots, Berichte und Caches. Die kanonischen Daten bleiben normalisiert. Wenn die denormalisierte Kopie veraltet oder beschädigt wird (und das wird sie), baust du sie aus der normalisierten Quelle neu auf.

Fremdschlüssel und Constraints: Der beste Code, den du nie schreiben wirst#

Ich habe jede Ausrede gehört, Fremdschlüssel wegzulassen. „Sie verlangsamen Schreibvorgänge." „Wir erzwingen das in der Anwendung." „Wir brauchen Flexibilität."

Das ist alles falsch.

Fremdschlüssel sind die wirkungsvollste Sache, die du einem Schema hinzufügen kannst. Sie verhindern ganze Kategorien von Bugs, die kein noch so guter Anwendungscode abfangen kann — Race Conditions, partielle Fehler, verwaiste Zeilen aus fehlgeschlagenen Transaktionen. Ein Fremdschlüssel ist eine Garantie der Datenbank-Engine selbst, auf Speicherebene durchgesetzt. Der beforeDelete-Hook deines ORMs ist ein Vorschlag.

sql

-- Mach immer das
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- Die "wir regeln das im Code"-Alternative:
-- Hoffnung. Hoffnung ist keine Strategie.

ON DELETE-Strategien#

Hier wird es nuanciert. Was passiert, wenn du eine Elternzeile löschst?

RESTRICT (Standard): Das Löschen schlägt fehl, wenn Kindzeilen existieren. Verwende das für die meisten Beziehungen. Du kannst keinen Kunden löschen, der Bestellungen hat — das ist Geschäftslogik, im Schema kodiert.

sql

-- Kunde kann nicht gelöscht werden, solange er Bestellungen hat
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Kindzeilen werden automatisch gelöscht. Verwende das sparsam und bewusst. Gut für „Teil-von"-Beziehungen, wo das Kind ohne den Elternteil keine Bedeutung hat.

sql

-- Das Löschen einer Bestellung löscht ihre Positionen — allein sind sie bedeutungslos
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Das Löschen eines Projekts löscht seine Mitgliedschaften
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: Die Fremdschlüsselspalte wird auf NULL gesetzt. Verwende das, wenn die Beziehung optional ist und die Kindzeile allein noch sinnvoll ist.

sql

-- Wenn ein Manager geht, existieren seine Berichte weiterhin — nur nicht zugewiesen
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Meine Faustregel: Standardmäßig RESTRICT, CASCADE für Kompositionsbeziehungen, SET NULL für optionale Assoziationen. Im Zweifel ist RESTRICT immer die sichere Wahl — es ist einfacher, eine Einschränkung zu lockern, als gelöschte Daten wiederherzustellen.

Check Constraints: Günstige Versicherung#

Check Constraints kosten beim Schreiben fast nichts und verhindern für immer, dass Müll-Daten in dein System gelangen:

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

Jede Check Constraint, die du hinzufügst, ist ein Validierungsbug weniger, den du jemals in der Produktion debuggen musst. Die Datenbank ist die letzte Verteidigungslinie. Nutze sie.

Unique Constraints und partielle Unique-Indizes#

Unique Constraints sind für einfache Fälle unkompliziert:

sql

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Aber sie werden interessant mit partiellen Indizes — Unique Constraints, die nur für bestimmte Zeilen gelten:

sql

-- Nur ein aktives Abonnement pro Nutzer (aber er kann viele gekündigte haben)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Nur eine primäre Adresse pro Nutzer
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Das ist eines von PostgreSQLs Killer-Features. Nutze es.

Soft Deletes: Das Muster, das jeder hasst und liebt#

Soft Deletes sind im Konzept einfach: Statt DELETE FROM users WHERE id = 42 machst du UPDATE users SET deleted_at = NOW() WHERE id = 42. Die Zeile bleibt in der Datenbank, wird aber aus normalen Abfragen herausgefiltert.

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()
);
 
-- Der partielle Index: gelöschte Zeilen effizient herausfiltern
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Jede Abfrage in deiner Anwendung braucht jetzt WHERE deleted_at IS NULL. Jede. Einzelne. Abfrage. ORMs helfen dabei (Prisma hat Middleware, Drizzle hat .where(isNull(deletedAt))), aber es ist eine Steuer auf jede Leseoperation. Vergiss es einmal und du zeigst „gelöschte" Daten den Nutzern.

Das Unique-Constraint-Problem#

Hier werden Soft Deletes hässlich. Wenn du UNIQUE (email) hast und ein Nutzer seinen Account soft-löscht, ist seine E-Mail immer noch in der Tabelle. Er kann sich nicht mit derselben E-Mail neu registrieren. Ein neuer Nutzer mit dieser E-Mail kann sich auch nicht anmelden.

Die Lösung ist ein partieller Unique-Index:

sql

-- E-Mail muss eindeutig sein, aber nur unter nicht-gelöschten Nutzern
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Das funktioniert, aber jetzt musst du dieses Muster für jede eindeutige Spalte auf jeder soft-löschbaren Tabelle im Kopf behalten. Es ist mit Disziplin handhabbar, aber es ist Komplexität, die du bewusst übernommen hast.

Das Fremdschlüsselproblem#

Soft Deletes interagieren schlecht mit Fremdschlüsseln. Wenn orders.user_id auf users.id mit ON DELETE RESTRICT verweist und du einen Nutzer soft-löschst... passiert nichts. Der FK feuert nicht, weil du die Zeile nicht wirklich gelöscht hast. Der Nutzer ist aus Sicht der Anwendung „weg", aber in der Datenbank immer noch sehr präsent.

Das bedeutet, dein Anwendungscode muss den Fall behandeln, dass eine referenzierte Entität soft-gelöscht ist. Jeder Join, jeder Lookup, jedes Mal wenn du einem Fremdschlüssel folgst — du musst deleted_at IS NULL auch auf der referenzierten Tabelle prüfen. Oder du tust es nicht, und deine Anwendung zeigt „Bestellung von [gelöschter Nutzer]", was je nach Betrachtung entweder ein Bug oder ein Feature ist.

Mein Ansatz#

Ich verwende Soft Deletes nur für nutzergerichtete Entitäten, bei denen Wiederherstellung eine Geschäftsanforderung ist — Nutzerkonten, Projekte, Dokumente. Dinge, bei denen ein Support-Agent eine Löschung rückgängig machen muss. Für alles andere verwende ich Hard Deletes mit einem Audit Trail (dazu gleich mehr).

sql

-- Soft Delete: nutzergerichtet, wiederherstellbar
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, nicht von der UI wiederherstellbar (aber auditiert)
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()
);
-- Wenn gelöscht, ist es weg. Das audit_log protokolliert, dass es existierte.

Audit Trails: Wissen, was sich geändert hat und wer es getan hat#

Jede nicht-triviale Anwendung braucht irgendwann eine Antwort auf „Was ist mit diesem Datensatz passiert?". Audit Trails sind, wie du diese Antwort lieferst, ohne Anwendungslogs durchzuwühlen.

Das Muster mit separater Audit-Tabelle#

Der einfachste Ansatz: Eine einzelne audit_log-Tabelle, die jede Änderung aufzeichnet.

sql

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- Text, um sowohl UUID als auch BIGINT PKs zu handhaben
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL bei INSERT
    new_values      JSONB,                  -- NULL bei DELETE
    changed_fields  TEXT[],                 -- welche Spalten sich geändert haben (bei 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);

Die Herausforderung ist das Befüllen. Du kannst es im Anwendungscode machen (explizit, aber leicht zu vergessen) oder mit Triggern (automatisch, aber schwieriger, Kontext wie changed_by zu übergeben).

Der Trigger-basierte Ansatz#

Trigger erfassen jede Änderung automatisch, sogar aus Raw SQL oder Datenbank-Admin-Operationen:

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);
        -- Geänderte Felder finden
        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;
        -- Nur loggen, wenn sich tatsächlich etwas geändert hat
        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;
 
-- Auf zu auditierende Tabellen anwenden
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();

Der Nachteil: Trigger wissen nicht, welcher Anwendungsnutzer die Änderung vorgenommen hat. Du kannst das mit Session-Variablen umgehen:

sql

-- In deiner Anwendung, vor der Abfrage:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- In der Trigger-Funktion:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Das funktioniert, fühlt sich aber fragil an. In der Praxis verwende ich einen hybriden Ansatz: Trigger für die Datenerfassung und Anwendungscode zum Setzen des Session-Kontexts.

Das Historientabellen-Muster#

Für Tabellen, bei denen du die vollständige Versionshistorie brauchst (nicht nur „was hat sich geändert", sondern „wie war der Zustand zum Zeitpunkt T"), ist eine dedizierte Historientabelle sauberer:

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

Vor jedem Update auf documents den aktuellen Zustand in document_history kopieren und die Version hochzählen. Jetzt kannst du das Dokument zu jedem Zeitpunkt rekonstruieren, Diffs zwischen Versionen anzeigen und sogar alte Versionen wiederherstellen.

Der Kompromiss ist Speicherplatz. Wenn deine content-Spalte groß ist und sich häufig ändert, kann die Historientabelle schnell wachsen. Für die meisten Anwendungen ist das kein Problem — Speicher ist günstig und du kannst alte Versionen bei Bedarf in Cold Storage archivieren.

Multi-Tenancy: Drei Ansätze, wähle deinen Schmerz#

Multi-Tenancy ist eine dieser Sachen, die am Anfang einfach hinzuzufügen und später nahezu unmöglich nachzurüsten ist. Wenn es auch nur eine Chance gibt, dass deine Anwendung mehrere Organisationen bedient, baue es von Tag eins ein.

Zeilenbasiert: tenant_id auf jeder Tabelle#

Der häufigste Ansatz. Jede Tabelle hat eine tenant_id-Spalte, und jede Abfrage filtert danach.

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()
);
 
-- Jeder Index sollte tenant_id für Abfrageleistung enthalten
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

Das Risiko ist Datenleckage — ein vergessenes WHERE tenant_id = ... und du zeigst Tenant As Daten an Tenant B. PostgreSQLs Row-Level Security (RLS) eliminiert diese Fehlerklasse:

sql

-- RLS auf der Tabelle aktivieren
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Eine Policy basierend auf einer Session-Variable erstellen
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- RLS auch für Tabellenbesitzer erzwingen
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Jetzt, selbst wenn dein Anwendungscode die WHERE tenant_id = ...-Klausel vergisst, fügt PostgreSQL sie automatisch hinzu. Das ist Tiefenverteidigung, und es ist eines der stärksten Argumente für PostgreSQL in Multi-Tenant-Systemen.

sql

-- In der Connection-Middleware deiner Anwendung:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Jetzt werden alle Abfragen auf RLS-aktivierten Tabellen automatisch gefiltert
SELECT * FROM projects;
-- PostgreSQL fügt intern hinzu: WHERE tenant_id = 'tenant-uuid-here'

Vorteile: Einzelne Datenbank, einfache Ops, effiziente Ressourcennutzung, einfache mandantenübergreifende Abfragen für Admin. Nachteile: Erfordert Disziplin (oder RLS), jede Abfrage berührt tenant_id, schwieriger jedem Mandanten eigenes Backup/Restore zu geben.

Schema-pro-Mandant#

Jeder Mandant bekommt sein eigenes PostgreSQL-Schema. Alle Schemas teilen sich dieselbe Datenbank, aber die Tabellen sind durch Namespaces isoliert.

sql

-- Ein Schema für jeden Mandanten erstellen
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tabellen leben im Schema des Mandanten
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Den search_path für den aktuellen Mandanten setzen
SET search_path TO tenant_acme, public;
 
-- Jetzt treffen unqualifizierte Abfragen das Schema des Mandanten
SELECT * FROM projects;  -- fragt tenant_acme.projects ab

Vorteile: Starke Isolation, kein Risiko mandantenübergreifender Datenleckage, einfaches Backup/Restore pro Mandant, Mandanten können Schemavariation haben. Nachteile: Migrationskomplexität (du musst N Schemas migrieren), Connection-Pool-Management, PostgreSQL hat praktische Grenzen bei ~10.000 Schemas.

Datenbank-pro-Mandant#

Jeder Mandant bekommt seine eigene Datenbank. Maximale Isolation.

Vorteile: Vollständige Isolation, unabhängige Skalierung, einfaches Backup/Restore, große Mandanten können auf dedizierter Hardware platziert werden. Nachteile: Connection-Management-Albtraum, mandantenübergreifende Abfragen unmöglich, Migration muss N-mal ausgeführt werden, erheblicher Betriebsaufwand.

Welchen wählen?#

Für die meisten SaaS-Anwendungen: Starte mit zeilenbasiert + RLS. Es ist am einfachsten zu betreiben, und RLS gibt dir ausreichend starke Isolation für die überwiegende Mehrheit der Anwendungsfälle. Wechsle nur zu Schema-pro-Mandant, wenn du vertragliche Isolationsanforderungen hast (Enterprise-Kunden, regulierte Branchen). Datenbank-pro-Mandant ist für den Fall, dass du physische Isolation absolut garantieren musst — und selbst dann, erwäge Managed Databases, wo der Betriebsaufwand für dich übernommen wird.

JSON/JSONB-Spalten: Die Notluke#

PostgreSQLs JSONB ist bemerkenswert. Es gibt dir Dokumentendatenbank-Flexibilität innerhalb eines relationalen Systems. Aber wie jedes mächtige Werkzeug ist es leicht zu missbrauchen.

Wann JSONB die richtige Wahl ist#

Dynamische benutzerdefinierte Attribute. Eine E-Commerce-Plattform, bei der jede Produktkategorie unterschiedliche Attribute hat — Schuhe haben size und color, Elektronik hat voltage und wattage. Statt einer EAV-Tabelle oder einer Spalte für jedes mögliche 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()
);
 
-- Beispieldaten:
-- Schuh: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Anwendungseinstellungen und Konfiguration. Nutzereinstellungen, Feature Flags, Benachrichtigungseinstellungen — Dinge, die häufig ihre Form ändern und keine relationale Integrität brauchen.

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, Event-Daten. Alles, wo das Schema von einem externen System kontrolliert wird und sich ohne Vorwarnung ändern kann.

GIN-Indizes auf JSONB#

Ohne Indizes erfordert das Abfragen innerhalb von JSONB einen Full Table Scan. GIN-Indizes machen es schnell:

sql

-- Die gesamte JSONB-Spalte indizieren (gut für @> Containment-Abfragen)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Abfrage: Alle Produkte mit color = "black" finden
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Abfrage: Produkte mit einem dieser Attribute finden
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Für spezifische Pfade, die du oft abfragst, ist ein gezielter Index effizienter:

sql

-- Einen bestimmten Pfad indizieren
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Jetzt ist das ein regulärer B-Tree-Lookup
SELECT * FROM products
WHERE attributes->>'color' = 'black';

Die Migrationsfalle#

Hier beißt dich JSONB: Du kannst nicht einfach NOT-NULL-Constraints oder Standardwerte für Felder innerhalb von JSON hinzufügen. Wenn du ein neues Pflichtfeld zu deinen Produktattributen hinzufügst, musst du jede bestehende Zeile backfillen. Mit einer regulären Spalte erledigt eine Migration das atomar. Mit JSONB schreibst du ein UPDATE, das jede Zeile berührt, und hoffst, dass dein Anwendungscode fehlende Felder elegant handhabt, bis das Backfill abgeschlossen ist.

sql

-- Eine neue Spalte hinzufügen: sauber, atomar, ein Statement
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Ein neues JSONB-Feld hinzufügen: chaotisch, erfordert ein Backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Plus Anwendungscode aktualisieren, um fehlendes weight_kg zu handhaben
-- Plus Validierungslogik aktualisieren
-- Plus jede API-Response aktualisieren, die Attribute enthält

Meine Regel: Wenn du ein JSONB-Feld mehr als zweimal pro Woche in einer WHERE-Klausel abfragst, sollte es wahrscheinlich eine Spalte sein. JSONB ist eine großartige Notluke. Es ist ein schrecklicher Standard.

Schemamigrationen: Produktionsabstürze um 3 Uhr nachts vermeiden#

Schemamigrationen sind der Punkt, an dem Theorie auf Realität trifft. Dein Schema sieht auf dem Papier großartig aus, aber jetzt musst du eine Tabelle mit 50 Millionen Zeilen während der Geschäftszeiten ohne Downtime ändern.

Migrationswerkzeuge#

Ich habe die meisten davon verwendet. Kurze Einschätzungen:

Drizzle (TypeScript): Mein aktueller Favorit. Schema-as-Code, typsichere Abfragen aus dem Schema generiert, sauberes Migrations-SQL. Der Push-Befehl für die Entwicklung ist schnell.

Prisma (TypeScript): Tolle DX für einfache Schemas. Tut sich schwer mit fortgeschrittenen PostgreSQL-Features (partielle Indizes, benutzerdefinierte Typen, RLS). Die Migrations-Engine kann überraschende Entscheidungen treffen.

Flyway (Java/CLI): Rock-solid, kampferprobt, SQL-first. Wenn du Raw-SQL-Migrationen schreibst, verfolgt Flyway sie zuverlässig. Keine Magie, keine Überraschungen.

golang-migrate (Go/CLI): Ähnlich wie Flyway, aber leichter. Großartig für Go-Projekte oder wenn du einfach nur einen simplen Up/Down-Migrations-Runner willst.

Das Zero-Downtime-Problem#

Die gefährlichsten Schemaänderungen sind die, die die Tabelle sperren. In PostgreSQL hat ALTER TABLE ... ADD COLUMN mit einem Standardwert früher die gesamte Tabelle für die Dauer des Rewrites gesperrt. Seit PostgreSQL 11 sind einfache Standardwerte (Konstanten) nur Metadaten und sofort. Aber andere Operationen sperren immer noch:

sql

-- SICHER: Nur Metadaten, sofort (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- GEFÄHRLICH: Schreibt die gesamte Tabelle um, volle Tabellensperre
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- GEFÄHRLICH: Scannt die gesamte Tabelle zur Validierung
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

NOT-NULL-Spalten sicher hinzufügen#

Du kannst nicht einfach NOT NULL zu einer bestehenden Tabelle mit Daten hinzufügen — es schlägt fehl, weil bestehende Zeilen keinen Wert haben. Der naive Ansatz:

sql

-- Das sperrt die Tabelle und schreibt sie um. Mach das nicht auf einer großen Tabelle.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Seit PostgreSQL 11 ist das für konstante Standardwerte tatsächlich sicher — es sind nur Metadaten. Aber wenn dein Standardwert eine Funktion ist oder du mit berechneten Werten backfillen musst, verwende das Expand-Contract-Muster.

Das Expand-Contract-Muster#

Das ist der Goldstandard für Zero-Downtime-Schemaänderungen. Drei Phasen:

Phase 1: Expand — Füge die neue Spalte als nullable hinzu. Deploye Anwendungscode, der in beide Spalten (alt und neu) schreibt.

sql

-- Migration 1: Die nullable Spalte hinzufügen
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Phase 2: Migrate — Bestehende Zeilen in Batches backfillen. Deine Anwendung schreibt bereits für neue Daten in die neue Spalte.

sql

-- Migration 2: In Batches backfillen (mach das nicht in einem Statement für große Tabellen)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Wiederholen, bis alle Zeilen backgefüllt sind

Phase 3: Contract — Sobald alle Zeilen backgefüllt sind, füge die NOT-NULL-Constraint hinzu und entferne die alte Spalte (falls zutreffend).

sql

-- Migration 3: Constraint hinzufügen (NOT VALID verwenden, um Full Table Scan zu vermeiden, dann separat validieren)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migration 4: Die Constraint validieren (nimmt ein ShareUpdateExclusiveLock, kein AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

Der NOT VALID + VALIDATE CONSTRAINT-Trick ist entscheidend. Das Hinzufügen einer Check Constraint scannt normalerweise die gesamte Tabelle und hält dabei eine schwere Sperre. NOT VALID fügt die Constraint ohne Scan hinzu (sie gilt nur für neue Schreibvorgänge), und VALIDATE CONSTRAINT scannt mit einer leichteren Sperre, die weder Lese- noch Schreibvorgänge blockiert.

Indexerstellung#

Das Erstellen von Indizes auf großen Tabellen blockiert standardmäßig Schreibvorgänge. Verwende immer CONCURRENTLY:

sql

-- BLOCKIERT SCHREIBVORGÄNGE: mach das nicht auf einer Live-Tabelle
CREATE INDEX idx_users_email ON users (email);
 
-- NICHT-BLOCKIEREND: verwende stattdessen das
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY dauert länger und kann nicht innerhalb einer Transaktion laufen, aber es sperrt die Tabelle nicht. Der Kompromiss lohnt sich in der Produktion immer.

Die Entscheidungen, die ich bereut habe#

Jedes Schema hat seine Reue. Hier sind meine.

SERIAL statt UUID für externe IDs#

Früh in einem Projekt verwendete ich SERIAL-Primärschlüssel und stellte sie direkt in URLs bereit: /users/42, /orders/1337. Das ließ Informationen durchsickern (Konkurrenten konnten unsere Nutzerzahl erraten), machte Paginierung vorhersagbar und brach, als wir Datenbanken aus zwei Regionen zusammenführen mussten. Der Wechsel zu UUIDs für extern sichtbare IDs erforderte eine mehrmonatige Migration.

Lektion: Verwende UUIDs für alles, was außerhalb deiner Datenbank sichtbar ist. Verwende SERIAL/BIGSERIAL für interne Join-Tabellen wenn du willst, aber lass nie eine auto-inkrementierende Ganzzahl in einer URL auftauchen.

Keine Constraints („Die fügen wir später hinzu")#

Wir starteten eine Tabelle ohne Check Constraints, weil „wir sind schnell unterwegs und fügen die später hinzu." Innerhalb von zwei Wochen hatten die Daten negative Preise, leere Namen und einen order_status-Wert von "oof", den jemand beim Testen eingetippt und nie bereinigt hatte.

Constraints nachträglich hinzuzufügen erforderte:

Alle ungültigen Daten finden
Entscheiden, was damit geschehen soll (korrigieren, löschen oder Bestandsschutz)
Eine Migration schreiben, die die Daten backfüllt/korrigiert UND die Constraint hinzufügt

Das dauerte länger als die Constraint am ersten Tag hinzuzufügen. Starte mit Constraints. Lockere sie, wenn du musst. Nie umgekehrt.

Kommagetrennte Strings statt Arrays oder Join-Tabellen#

Ich habe einmal Tags als kommagetrennten String gespeichert: "javascript,react,nextjs". Abfragen waren ein Albtraum:

sql

-- So fragt man kommagetrennte Werte ab. Mach das nicht.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- Das matcht auch "react-native" und "preact"
 
-- Was ich hätte tun sollen:
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)
);

Oder verwende mindestens PostgreSQLs nativen 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);
 
-- Saubere Abfragen
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Arrays sind akzeptabel für einfache Listen, die keine eigenen Attribute brauchen. In dem Moment, wo du Metadaten über die Beziehung brauchst (wie „wer hat dieses Tag hinzugefügt" oder „wann wurde es hinzugefügt"), brauchst du eine Join-Tabelle.

„type" als Spaltenname verwenden#

sql

-- Schien damals in Ordnung
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Dann versuchst du es in buchstäblich jedem ORM abzufragen:
-- notification.type  ← kollidiert mit dem Typsystem jeder Sprache
-- "type" ist ein reserviertes Wort in den meisten SQL-Dialekten
-- Du musst es überall quoten: SELECT "type" FROM notifications

Verwende kind, category oder notification_type. Alles außer type. Vermeide ebenso order (verwende sort_order oder position), user (verwende account oder setze ein Präfix), und group (verwende team oder group_name).

Kein created_at von Anfang an#

Eine Tabelle bekam kein created_at, weil „wir es nicht brauchen." Drei Monate später mussten wir ein Problem debuggen und hatten keine Ahnung, wann Datensätze erstellt wurden. Es nachträglich hinzuzufügen bedeutete, dass alle bestehenden Zeilen denselben Zeitstempel bekamen (den Migrationszeitstempel), was die historischen Daten nutzlos machte.

Jede Tabelle bekommt created_at. Keine Ausnahmen. Die Kosten sind eine Spalte. Die Kosten, es nicht zu haben, sind unbekannt, bis du es brauchst.

Geschäftslogik in Datenbank-Views#

Ich habe einmal eine Kette von Views erstellt — active_users filterte users, premium_active_users filterte active_users, und eine Reporting-View jointe alle. Es funktionierte großartig, bis jemand die users-Tabelle änderte und alle drei Views stillschweigend brachen. Der Query Planner hatte auch Schwierigkeiten, durch mehrere View-Ebenen zu optimieren.

Views sind großartig für Komfort und Read-Only-Zugriffsmuster. Sie sind schrecklich als Ort, um Geschäftslogik zu kodieren, die sich ändert. Halte Geschäftsregeln im Anwendungscode, wo sie versioniert, getestet und deploybar sind. Verwende Views für Reporting-Abkürzungen, nicht als architektonische Bausteine.

Alles zusammensetzen#

Hier ist, wie ein gut designtes Schema aussieht, wenn du diese Muster kombinierst. Ein vereinfachtes Projektmanagementsystem:

sql

-- Extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- für gen_random_uuid()
 
-- Updated_at Trigger-Funktion (wiederverwendbar)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Mandanten
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();
 
-- Nutzer
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();
 
-- RLS aktivieren
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Projekte
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);
 
-- Aufgaben
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);
 
-- Aufgabenkommentare
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 (kein RLS — Admin-only Tabelle)
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);

Beachte die Muster:

Jede Tabelle hat tenant_id und RLS aktiviert (außer tenants und audit_log).
Jede Tabelle hat created_at und updated_at mit Triggern.
UUIDs für alle Primärschlüssel (extern sichtbare Entitäten).
Check Constraints auf Status-Enums, Längen und Bereichen.
Fremdschlüssel mit passendem ON DELETE-Verhalten.
Indizes für die Zugriffsmuster entworfen (Mandant + Projekt, Mandant + Status).
Soft Deletes nur auf users (wo Kontowiederherstellung benötigt wird), statusbasierte Archivierung auf projects.

Abschließende Gedanken#

Schemadesign ist nicht glamourös. Niemand hat jemals einen Konferenzvortrag mit dem Titel „Ich habe Check Constraints auf jede Tabelle gesetzt und es hat uns sechs Monate Debugging erspart" gehalten. Aber genau das tut gutes Schemadesign — es verhindert Probleme so leise, dass du nie erfährst, dass sie existiert hätten.

Die Muster in diesem Beitrag sind nicht neuartig. Sie sind das Ergebnis jahrelangen Schreibens von Migrationen, Debuggens von Datenkorruption und Refaktorierens von Schemas unter Produktionslast. Jedes einzelne existiert, weil ich oder jemand, mit dem ich gearbeitet habe, es zuerst anders gemacht und den Preis dafür bezahlt hat.

Starte mit Constraints. Verwende Fremdschlüssel. Füge created_at zu allem hinzu. Wähle eine Namenskonvention und setze sie gnadenlos durch. Verwende RLS für Multi-Tenancy. Sei vorsichtig mit JSONB. Teste deine Migrationen gegen produktionsgroße Daten, bevor du sie deployest.

Die Datenbank ist das Fundament. Mach es richtig, und alles, was darauf aufgebaut wird, wird einfacher. Mach es falsch, und kein noch so cleverer Anwendungscode kann dich retten.