Vai al contenuto
·33 min di lettura

Progettazione di schema database: pattern che invecchiano bene

Regole di normalizzazione, convenzioni di naming, soft delete, audit trail, pattern multi-tenancy, strategie di versionamento e le decisioni sullo schema di cui mi sono pentito. Focalizzato su PostgreSQL.

databasepostgresqlarchitecturebackendsql
Condividi:X / TwitterLinkedIn

Uno schema è un contratto con il tuo futuro te stesso. Ogni colonna che aggiungi, ogni vincolo che salti, ogni "lo sistemiamo dopo" — tutto si accumula. Ho lavorato su sistemi dove una singola decisione sbagliata sullo schema fatta tre anni fa costa al team uno sprint intero ogni trimestre in workaround.

Il database sopravvive a tutto. Il tuo framework frontend cambierà. Il tuo livello API verrà riscritto. La tua strategia di deploy evolverà. Ma i dati? I dati restano. E la forma che gli hai dato il primo giorno ti segue per sempre, perché migrare una tabella con 200 milioni di righe non è come rifattorizzare un componente React.

Questo è ciò che ho imparato sul prendere decisioni sullo schema che non ti perseguitino. Focalizzato su PostgreSQL, perché è quello che uso e di cui mi fido, ma la maggior parte di questi pattern si applica a qualsiasi database relazionale.

Convenzioni di naming: la cosa noiosa che conta di più#

Ho visto più discussioni sulle convenzioni di naming che sulle decisioni architetturali vere e proprie. Ecco a cosa sono arrivato dopo anni di context-switching tra progetti:

Snake_case per tutto. Tabelle, colonne, indici, vincoli. Niente camelCase, niente PascalCase. PostgreSQL converte comunque gli identificatori senza virgolette in minuscolo, quindi createdAt diventa createdat a meno che non lo metti tra virgolette doppie ovunque. Non lottare contro il database.

Nomi di tabelle al plurale. Una tabella contiene molte righe. users non user. orders non order. order_items non order_item. Si legge naturalmente nelle query: SELECT * FROM users WHERE ... — stai selezionando da una collezione.

Suffisso _id per le chiavi esterne. user_id, order_id, tenant_id. La chiave primaria è semplicemente id. È inequivocabile. Quando fai join tra tabelle, users.id = orders.user_id si legge come l'italiano.

Suffisso _at per i timestamp. created_at, updated_at, deleted_at, published_at, expires_at. Sai sempre che si tratta di un momento nel tempo.

Prefisso is_ per i booleani. is_active, is_verified, is_published. Alcuni usano has_ per i booleani di possesso (has_mfa_enabled), ma io tengo le cose semplici e uso is_ per tutto.

Suffisso _count per i contatori denormalizzati. comment_count, follower_count. Rende chiaro che è un numero in cache, non un calcolo in tempo reale.

La cosa importante: la consistenza batte la perfezione. Ho visto team passare settimane a dibattere se dovesse essere email_address o email o email_addr. Scegline uno e imponilo ovunque. La peggior convenzione di naming è quella applicata in modo inconsistente.

sql
-- Buono: consistente, leggibile, nessuna sorpresa
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()
);
 
-- Cattivo: casing inconsistente, convenzioni miste, nomi ambigui
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- è 0/1? un livello di verifica?
    lastLogin       TIMESTAMP,       -- camelCase in un mondo snake_case
    created         TIMESTAMP        -- creato cosa? quando?
);

Un'altra cosa: non usare mai parole riservate come nomi di colonne. type, order, user, group, table — funzionano tutte se le metti tra virgolette, ma ti bruceranno negli ORM, nei query builder e in ogni strumento di generazione SQL dinamica. Usa kind invece di type, sort_order invece di order. Il tuo futuro te stesso ti ringrazierà.

Le colonne standard: cosa ottiene ogni tabella#

Ogni tabella nei miei schema inizia con lo stesso scheletro. Nessuna eccezione.

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

Il dibattito UUID vs BIGSERIAL#

Questa è una di quelle decisioni che genera molto più calore che luce. Ecco il vero trade-off:

BIGSERIAL (intero auto-incrementante):

  • 8 byte. Compatto. Veloce da indicizzare e joinare.
  • Ordinato — puoi ordinare per id per ottenere l'ordine di inserimento.
  • Prevedibile — un utente può indovinare altri ID incrementando il suo.
  • Non funziona bene nei sistemi distribuiti (richiede coordinamento).

UUID v4 (casuale):

  • 16 byte. Indici più grandi, join più lenti (ma raramente il bottleneck).
  • Imprevedibile — nessun information leakage.
  • Funziona nei sistemi distribuiti senza coordinamento.
  • Terribile località degli indici — UUID casuali frammentano gli indici B-tree.

UUID v7 (ordinato per tempo, RFC 9562):

  • 16 byte, ma ordinato per tempo quindi la località B-tree è eccellente.
  • Sufficientemente imprevedibile per l'uso esterno.
  • Relativamente nuovo, ma PostgreSQL 17+ ha gen_random_uuid() e puoi usare uuid_generate_v7() con le estensioni.

La mia posizione attuale: BIGSERIAL per tabelle interne, UUID v7 per qualsiasi cosa esposta al mondo esterno. Se un ID appare mai in un URL, una risposta API o un payload webhook, usa UUID. Se è una pura tabella di join che gli utenti non vedono mai, BIGSERIAL va bene.

sql
-- Per una risorsa esposta via API
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()
);
 
-- Per una tabella di mappatura interna
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)
);

Timestamp: sempre TIMESTAMPTZ#

Usa TIMESTAMPTZ, non TIMESTAMP. La versione "TZ" salva il valore in UTC e lo converte in lettura in base al fuso orario della sessione. La versione non-TZ salva qualsiasi cosa gli dai senza contesto di fuso orario — il che significa che se due server in fusi orari diversi scrivono sulla stessa tabella, ottieni una corruzione silenziosa dei dati.

sql
-- Sempre questo
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Mai questo
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

Il trigger updated_at#

PostgreSQL non ha l'ON UPDATE CURRENT_TIMESTAMP di MySQL. Ti serve un trigger:

sql
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Applica a ogni tabella che ha 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();

Sì, ti serve un trigger per tabella. Sì, è noioso. Scrivi un helper per le migrazioni che crea il trigger automaticamente quando aggiungi una tabella. Ne vale la pena, perché l'alternativa è ricordarsi di impostare updated_at = NOW() in ogni query UPDATE dell'intera applicazione — e te lo dimenticherai.

Normalizzazione: quando infrangere le regole#

Ogni corso di informatica insegna la normalizzazione fino alla 3NF (Terza Forma Normale). Le regole sono:

  • 1NF: Ogni colonna contiene un singolo valore atomico. Niente array, niente liste separate da virgola.
  • 2NF: Ogni colonna non-chiave dipende dall'intera chiave primaria (rilevante per le chiavi composte).
  • 3NF: Nessuna dipendenza transitiva. Se la colonna A determina la colonna B, e B determina C, allora C non dovrebbe essere nella stessa tabella di A.

In pratica, la 3NF è il punto ottimale per le tabelle transazionali. Dovresti partire da lì e deviare solo quando hai una ragione specifica e misurabile.

Ecco un sistema ordini correttamente normalizzato:

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

Nota unit_price_cents su order_items. Questa è denormalizzazione intenzionale. Facciamo uno snapshot del prezzo al momento dell'ordine, perché il prezzo del prodotto potrebbe cambiare dopo. Questa è una delle ragioni più comuni e corrette per denormalizzare.

Quando la denormalizzazione è la scelta giusta#

Tabelle di reporting. Se la tua dashboard di analytics ha bisogno di joinare 8 tabelle per renderizzare, crea una tabella di reporting denormalizzata e popolala con un job in background. Il tuo schema transazionale resta pulito, e le tue query di reporting restano veloci.

sql
-- Tabella di reporting denormalizzata, popolata da un 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,        -- gli array vanno bene qui
    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 view. La MATERIALIZED VIEW di PostgreSQL è sottovalutata. È uno snapshot denormalizzato che aggiorni on demand. Perfetto per le dashboard.

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;
 
-- Aggiornala ogni notte
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Colonne JSON per attributi dinamici. Quando righe diverse necessitano di campi diversi — varianti di prodotto, invii di form, preferenze utente — una colonna JSONB è spesso meglio di una terribile tabella EAV (Entity-Attribute-Value). Ne parleremo più avanti.

La regola che non infrango mai#

Non denormalizzare mai le tue tabelle source-of-truth. Denormalizza copie, snapshot, report e cache. I dati canonici restano normalizzati. Quando la copia denormalizzata diventa obsoleta o corrotta (e succederà), la ricostruisci dalla fonte normalizzata.

Chiavi esterne e vincoli: il miglior codice che non scriverai mai#

Ho sentito ogni scusa per saltare le chiavi esterne. "Rallentano le scritture." "Le gestiamo nell'applicazione." "Abbiamo bisogno di flessibilità."

Sono tutte sbagliate.

Le chiavi esterne sono la singola cosa più impattante che puoi aggiungere a uno schema. Prevengono intere categorie di bug che nessuna quantità di codice applicativo può intercettare — race condition, fallimenti parziali, righe orfane da transazioni fallite. Una chiave esterna è una garanzia dal motore del database stesso, applicata a livello di storage. L'hook beforeDelete del tuo ORM è un suggerimento.

sql
-- Fai sempre questo
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- L'alternativa "lo gestiremo nel codice":
-- Speranza. La speranza non è una strategia.

Strategie ON DELETE#

Qui la cosa si fa sfumata. Cosa succede quando elimini una riga genitore?

RESTRICT (default): Il delete fallisce se esistono righe figlie. Usalo per la maggior parte delle relazioni. Non puoi eliminare un cliente che ha ordini — è logica di business codificata nello schema.

sql
-- Il cliente non può essere eliminato finché ha ordini
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Le righe figlie vengono automaticamente eliminate. Usalo con parsimonia e deliberatamente. Adatto per le relazioni "parte-di" dove il figlio non ha significato senza il genitore.

sql
-- Eliminare un ordine elimina le sue righe — non hanno significato da sole
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Eliminare un progetto elimina le sue membership
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: La colonna chiave esterna viene impostata a NULL. Usalo quando la relazione è opzionale e la riga figlia ha ancora significato da sola.

sql
-- Se un manager se ne va, i suoi report esistono ancora — solo non assegnati
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

La mia regola: default a RESTRICT, usa CASCADE per le relazioni di composizione, usa SET NULL per le associazioni opzionali. Se sei in dubbio, RESTRICT è sempre la scelta sicura — è più facile allentare un vincolo che recuperare dati eliminati.

Check constraint: assicurazione a basso costo#

I check constraint costano quasi nulla in fase di scrittura e impediscono per sempre l'ingresso di dati spazzatura nel tuo sistema:

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

Ogni check constraint che aggiungi è un bug di validazione in meno che dovrai mai debuggare in produzione. Il database è l'ultima linea di difesa. Usalo.

Unique constraint e indici parziali#

Gli unique constraint sono semplici per i casi base:

sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Ma diventano interessanti con gli indici parziali — unique constraint che si applicano solo a determinate righe:

sql
-- Solo una sottoscrizione attiva per utente (ma possono averne molte cancellate)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Solo un indirizzo primario per utente
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Questa è una delle feature killer di PostgreSQL. Usala.

Soft delete: il pattern che tutti amano odiare#

I soft delete sono semplici nel concetto: invece di DELETE FROM users WHERE id = 42, fai UPDATE users SET deleted_at = NOW() WHERE id = 42. La riga resta nel database ma viene filtrata dalle query normali.

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()
);
 
-- L'indice parziale: filtra le righe eliminate in modo efficiente
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Ogni query nella tua applicazione ora ha bisogno di WHERE deleted_at IS NULL. Ogni. Singola. Query. Gli ORM aiutano con questo (Prisma ha i middleware, Drizzle ha .where(isNull(deletedAt))), ma è una tassa su ogni operazione di lettura. Dimenticalo una volta e stai mostrando dati "eliminati" agli utenti.

Il problema dell'unique constraint#

Ecco dove i soft delete diventano brutti. Se hai UNIQUE (email) e un utente fa soft delete del suo account, la sua email è ancora nella tabella. Non può ri-registrarsi con la stessa email. Un nuovo utente con quell'email non può nemmeno iscriversi.

Il fix è un indice unico parziale:

sql
-- L'email deve essere unica, ma solo tra gli utenti non eliminati
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Funziona, ma ora devi ricordarti questo pattern per ogni colonna unica su ogni tabella con soft delete. È gestibile con disciplina, ma è complessità che hai scelto di assumerti.

Il problema delle chiavi esterne#

I soft delete interagiscono male con le chiavi esterne. Se orders.user_id referenzia users.id con ON DELETE RESTRICT, e fai soft delete di un utente... non succede nulla. La FK non scatta perché non hai effettivamente eliminato la riga. L'utente è "sparito" dalla prospettiva dell'applicazione ma è ancora molto presente nel database.

Questo significa che il codice della tua applicazione deve gestire il caso in cui un'entità referenziata è stata soft-deleted. Ogni join, ogni lookup, ogni volta che segui una chiave esterna — devi controllare deleted_at IS NULL anche sulla tabella referenziata. Oppure non lo fai, e la tua applicazione mostra "Ordine di [utente eliminato]" che è un bug o una feature a seconda di chi lo chiedi.

Il mio approccio#

Uso i soft delete solo per entità rivolte all'utente dove il recupero è un requisito di business — account utente, progetti, documenti. Cose dove un agente di supporto potrebbe dover ripristinare un'eliminazione. Per tutto il resto, uso hard delete con un audit trail (ne parleremo nella prossima sezione).

sql
-- Soft delete: rivolto all'utente, recuperabile
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: interno, non recuperabile dalla UI (ma auditato)
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()
);
-- Quando viene eliminata, è sparita. L'audit_log registra che esisteva.

Audit trail: sapere cosa è cambiato e chi l'ha fatto#

Ogni applicazione non banale ha bisogno di una risposta a "cosa è successo a questo record?" a un certo punto. Gli audit trail sono il modo in cui fornisci quella risposta senza scavare nei log dell'applicazione.

Il pattern della tabella di audit separata#

L'approccio più semplice: una singola tabella audit_log che registra ogni modifica.

sql
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text per gestire sia UUID che BIGINT come PK
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL per INSERT
    new_values      JSONB,                  -- NULL per DELETE
    changed_fields  TEXT[],                 -- quali colonne sono cambiate (per 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);

La sfida è popolarla. Puoi farlo nel codice applicativo (esplicito, ma facile da dimenticare) o con i trigger (automatico, ma più difficile passare il contesto come changed_by).

L'approccio basato sui trigger#

I trigger catturano ogni modifica automaticamente, anche da SQL raw o operazioni di amministrazione del database:

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);
        -- Trova i campi modificati
        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;
        -- Registra solo se qualcosa è effettivamente cambiato
        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;
 
-- Applica alle tabelle che vuoi auditare
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();

Lo svantaggio: i trigger non sanno quale utente dell'applicazione ha effettuato la modifica. Puoi aggirare il problema con le variabili di sessione:

sql
-- Nella tua applicazione, prima della query:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- Nella funzione trigger:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Funziona ma sembra fragile. In pratica, uso un approccio ibrido: trigger per la cattura dei dati, e codice applicativo per impostare il contesto della sessione.

Il pattern della tabella storica#

Per tabelle dove hai bisogno della cronologia completa delle versioni (non solo "cosa è cambiato" ma "qual era lo stato al tempo T"), una tabella storica dedicata è più pulita:

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

Prima di ogni aggiornamento a documents, copia lo stato corrente in document_history e incrementa la versione. Ora puoi ricostruire il documento in qualsiasi momento, mostrare le differenze tra le versioni e persino ripristinare versioni precedenti.

Il trade-off è lo storage. Se la colonna content è grande e cambia frequentemente, la tabella storica può crescere velocemente. Per la maggior parte delle applicazioni, questo va bene — lo storage è economico e puoi archiviare le vecchie versioni su cold storage se necessario.

Multi-tenancy: tre approcci, scegli il tuo dolore#

La multi-tenancy è una di quelle cose facili da aggiungere all'inizio e quasi impossibili da aggiungere dopo. Se c'è una qualsiasi possibilità che la tua applicazione serva più organizzazioni, integrala fin dal primo giorno.

Row-level: tenant_id su ogni tabella#

L'approccio più comune. Ogni tabella ha una colonna tenant_id, e ogni query filtra per essa.

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()
);
 
-- Ogni indice dovrebbe includere tenant_id per le performance delle query
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

Il rischio è il data leakage — un WHERE tenant_id = ... dimenticato e stai mostrando i dati del Tenant A al Tenant B. La Row-Level Security (RLS) di PostgreSQL elimina questa classe di bug:

sql
-- Abilita RLS sulla tabella
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Crea una policy basata su una variabile di sessione
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Forza RLS anche per i proprietari della tabella
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Ora, anche se il codice della tua applicazione dimentica la clausola WHERE tenant_id = ..., PostgreSQL la aggiunge automaticamente. Questa è difesa in profondità, ed è uno degli argomenti più forti a favore di PostgreSQL nei sistemi multi-tenant.

sql
-- Nella middleware di connessione della tua applicazione:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Ora tutte le query su tabelle con RLS abilitata sono filtrate automaticamente
SELECT * FROM projects;
-- PostgreSQL aggiunge internamente: WHERE tenant_id = 'tenant-uuid-here'

Pro: Database singolo, operazioni semplici, uso efficiente delle risorse, query cross-tenant facili per l'admin. Contro: Richiede disciplina (o RLS), ogni query tocca tenant_id, più difficile dare ai tenant il proprio backup/restore.

Schema per tenant#

Ogni tenant ottiene il proprio schema PostgreSQL. Tutti gli schema condividono lo stesso database, ma le tabelle sono isolate per namespace.

sql
-- Crea uno schema per ogni tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Le tabelle vivono nello schema del tenant
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Imposta il search_path per il tenant corrente
SET search_path TO tenant_acme, public;
 
-- Ora le query non qualificate colpiscono lo schema del tenant
SELECT * FROM projects;  -- interroga tenant_acme.projects

Pro: Forte isolamento, nessun rischio di data leakage cross-tenant, facile backup/restore per tenant, i tenant possono avere variazioni dello schema. Contro: Complessità nelle migrazioni dello schema (devi migrare N schema), gestione del connection pool, PostgreSQL ha limiti pratici intorno ai ~10.000 schema.

Database per tenant#

Ogni tenant ottiene il proprio database. Massimo isolamento.

Pro: Isolamento completo, scaling indipendente, facile backup/restore, possibilità di posizionare i grandi tenant su hardware dedicato. Contro: Incubo nella gestione delle connessioni, query cross-tenant impossibili, le migrazioni devono essere eseguite N volte, overhead operativo significativo.

Quale scegliere?#

Per la maggior parte delle applicazioni SaaS: inizia con row-level + RLS. È il più semplice da gestire, e RLS ti dà un isolamento sufficientemente forte per la stragrande maggioranza dei casi d'uso. Passa a schema-per-tenant solo se hai requisiti contrattuali di isolamento (clienti enterprise, industrie regolamentate). Database-per-tenant è per quando devi assolutamente garantire l'isolamento fisico — e anche in quel caso, considera i database gestiti dove l'onere operativo è gestito per te.

Colonne JSON/JSONB: la via d'uscita#

Il JSONB di PostgreSQL è notevole. Ti dà la flessibilità di un document database dentro un sistema relazionale. Ma come ogni strumento potente, è facile abusarne.

Quando JSONB è la scelta giusta#

Attributi dinamici definiti dall'utente. Una piattaforma e-commerce dove ogni categoria di prodotto ha attributi diversi — le scarpe hanno size e color, l'elettronica ha voltage e wattage. Piuttosto che una tabella EAV o una colonna per ogni possibile attributo:

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

Impostazioni e configurazione dell'applicazione. Preferenze utente, feature flag, impostazioni delle notifiche — cose che cambiano forma frequentemente e non necessitano di integrità relazionale.

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

Payload webhook, risposte API, dati di eventi. Qualsiasi cosa dove lo schema è controllato da un sistema esterno e potrebbe cambiare senza preavviso.

Indici GIN su JSONB#

Senza indici, interrogare all'interno di JSONB richiede una scansione completa della tabella. Gli indici GIN lo rendono veloce:

sql
-- Indicizza l'intera colonna JSONB (buono per le query di contenimento @>)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Query: trova tutti i prodotti con color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Query: trova prodotti con uno qualsiasi di questi attributi
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Per percorsi specifici che interroghi spesso, un indice mirato è più efficiente:

sql
-- Indicizza un percorso specifico
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Ora è un normale lookup B-tree
SELECT * FROM products
WHERE attributes->>'color' = 'black';

La trappola della migrazione#

Ecco dove JSONB ti morde: non puoi facilmente aggiungere vincoli NOT NULL o valori di default ai campi dentro JSON. Se aggiungi un nuovo campo obbligatorio agli attributi del prodotto, devi fare backfill di ogni riga esistente. Con una colonna regolare, una migrazione gestisce questo atomicamente. Con JSONB, stai scrivendo un UPDATE che tocca ogni riga e sperando che il codice della tua applicazione gestisca i campi mancanti con grazia finché il backfill non è completato.

sql
-- Aggiungere una nuova colonna: pulito, atomico, un'unica istruzione
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Aggiungere un nuovo campo JSONB: disordinato, richiede un backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Più aggiornare il codice applicativo per gestire weight_kg mancante
-- Più aggiornare la logica di validazione
-- Più aggiornare ogni risposta API che include attributes

La mia regola: se stai facendo query su un campo JSONB in una clausola WHERE più di due volte a settimana, dovrebbe probabilmente essere una colonna. JSONB è un'ottima via d'uscita. È un pessimo default.

Migrazioni dello schema: non rompere la produzione alle 3 di notte#

Le migrazioni dello schema sono dove la teoria incontra la realtà. Il tuo schema sembra perfetto sulla carta, ma ora devi alterare una tabella con 50 milioni di righe durante l'orario lavorativo senza alcun downtime.

Strumenti di migrazione#

Li ho usati quasi tutti. Pareri brevi:

Drizzle (TypeScript): Il mio preferito attuale. Schema-as-code, query type-safe generate dallo schema, SQL delle migrazioni pulito. Il comando push per lo sviluppo è veloce.

Prisma (TypeScript): Ottima DX per schema semplici. Fa fatica con le feature avanzate di PostgreSQL (indici parziali, tipi custom, RLS). Il motore di migrazione può prendere decisioni sorprendenti.

Flyway (Java/CLI): Solido come una roccia, collaudato in battaglia, SQL-first. Se scrivi migrazioni SQL raw, Flyway le traccia in modo affidabile. Niente magia, niente sorprese.

golang-migrate (Go/CLI): Simile a Flyway ma più leggero. Ottimo per progetti Go o quando vuoi semplicemente un runner di migrazioni up/down semplice.

Il problema del zero-downtime#

Le modifiche allo schema più pericolose sono quelle che bloccano la tabella. In PostgreSQL, ALTER TABLE ... ADD COLUMN con un valore di default bloccava l'intera tabella per la durata della riscrittura. Da PostgreSQL 11, i default semplici (costanti) sono solo metadati e istantanei. Ma altre operazioni bloccano ancora:

sql
-- SICURO: solo metadati, istantaneo (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- PERICOLOSO: riscrive l'intera tabella, blocco completo della tabella
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- PERICOLOSO: scansiona l'intera tabella per validare
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

Aggiungere colonne NOT NULL in sicurezza#

Non puoi semplicemente aggiungere NOT NULL a una tabella esistente con dati — fallisce perché le righe esistenti non hanno un valore. L'approccio ingenuo:

sql
-- Questo blocca la tabella e la riscrive. Non farlo su una tabella grande.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Da PostgreSQL 11, questo è in realtà sicuro per i default costanti — è solo metadati. Ma se il tuo default è una funzione o devi fare backfill con valori calcolati, usa il pattern expand-contract.

Il pattern expand-contract#

Questo è il gold standard per le modifiche allo schema senza downtime. Tre fasi:

Fase 1: Expand — Aggiungi la nuova colonna come nullable. Fai deploy del codice applicativo che scrive sia nella vecchia che nella nuova colonna.

sql
-- Migrazione 1: Aggiungi la colonna nullable
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Fase 2: Migrate — Fai backfill delle righe esistenti in batch. La tua applicazione sta già scrivendo nella nuova colonna per i nuovi dati.

sql
-- Migrazione 2: Backfill in batch (non farlo in un'unica istruzione per tabelle grandi)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Ripeti finché tutte le righe non sono backfillate

Fase 3: Contract — Una volta che tutte le righe sono backfillate, aggiungi il vincolo NOT NULL e rimuovi la vecchia colonna (se applicabile).

sql
-- Migrazione 3: Aggiungi il vincolo (usa NOT VALID per evitare la scansione completa della tabella, poi valida separatamente)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migrazione 4: Valida il vincolo (prende uno ShareUpdateExclusiveLock, non un AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

Il trucco NOT VALID + VALIDATE CONSTRAINT è cruciale. Aggiungere un check constraint normalmente scansiona l'intera tabella mantenendo un lock pesante. NOT VALID aggiunge il vincolo senza scansionare (si applica solo alle nuove scritture), e VALIDATE CONSTRAINT scansiona con un lock più leggero che non blocca letture o scritture.

Creazione degli indici#

Creare indici su tabelle grandi blocca le scritture di default. Usa sempre CONCURRENTLY:

sql
-- BLOCCA LE SCRITTURE: non farlo su una tabella live
CREATE INDEX idx_users_email ON users (email);
 
-- NON-BLOCCANTE: usa sempre questo
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY impiega più tempo e non può essere eseguito dentro una transazione, ma non blocca la tabella. Il trade-off vale sempre in produzione.

Le decisioni di cui mi sono pentito#

Ogni schema ha i suoi rimpianti. Ecco i miei.

Usare SERIAL invece di UUID per gli ID esterni#

All'inizio di un progetto, ho usato chiavi primarie SERIAL e le ho esposte direttamente negli URL: /users/42, /orders/1337. Questo perdeva informazioni (i competitor potevano indovinare il nostro numero di utenti), rendeva la paginazione prevedibile e si è rotto quando abbiamo dovuto unire database da due regioni. Passare agli UUID per gli ID esposti esternamente ha richiesto una migrazione di più mesi.

Lezione: usa UUID per qualsiasi cosa esposta fuori dal tuo database. Usa SERIAL/BIGSERIAL per le tabelle di join interne se vuoi, ma non lasciare mai che un intero auto-incrementante appaia in un URL.

Nessun vincolo ("Li aggiungeremo dopo")#

Abbiamo lanciato una tabella senza check constraint perché "stiamo andando veloci e li aggiungeremo dopo." Entro due settimane, i dati avevano prezzi negativi, nomi vuoti e un valore order_status di "oof" che qualcuno aveva digitato durante il testing e non aveva mai pulito.

Aggiungere vincoli dopo il fatto ha richiesto:

  1. Trovare tutti i dati non validi
  2. Decidere cosa farne (correggere, eliminare o mantenerli come eccezione)
  3. Scrivere una migrazione che fa backfill/corregge i dati E aggiunge il vincolo

Questo ha richiesto più tempo di quanto ci sarebbe voluto per aggiungere il vincolo il primo giorno. Inizia con i vincoli. Allentali se devi. Mai il contrario.

Stringhe separate da virgola invece di array o tabelle di join#

Ho una volta salvato i tag come stringa separata da virgola: "javascript,react,nextjs". Le query erano un incubo:

sql
-- Così si interrogano valori separati da virgola. Non farlo.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- Questo intercetta anche "react-native" e "preact"
 
-- Quello che avrei dovuto fare:
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)
);

O come minimo, usa il tipo array nativo di PostgreSQL:

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

Gli array vanno bene per liste semplici che non necessitano dei propri attributi. Nel momento in cui hai bisogno di metadati sulla relazione (come "chi ha aggiunto questo tag" o "quando è stato aggiunto"), ti serve una tabella di join.

Usare "type" come nome di colonna#

sql
-- Sembrava ok al momento
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Poi provi a interrogarlo in letteralmente qualsiasi ORM:
-- notification.type  ← conflitto con il sistema di tipi di ogni linguaggio
-- "type" è una parola riservata nella maggior parte dei dialetti SQL
-- Finisci per metterlo tra virgolette ovunque: SELECT "type" FROM notifications

Usa kind, category o notification_type. Qualsiasi cosa tranne type. Allo stesso modo, evita order (usa sort_order o position), user (usa account o metti un prefisso) e group (usa team o group_name).

Non aggiungere created_at dall'inizio#

Una tabella non ha avuto created_at perché "non ne abbiamo bisogno." Tre mesi dopo, avevamo bisogno di debuggare un problema e non avevamo idea di quando i record fossero stati creati. Aggiungerlo retroattivamente significava che tutte le righe esistenti ottenevano lo stesso timestamp (quello della migrazione), rendendo i dati storici inutili.

Ogni tabella ottiene created_at. Nessuna eccezione. Il costo è una colonna. Il costo di non averla è inconoscibile finché non ne hai bisogno.

Mettere la logica di business nelle viste del database#

Una volta ho creato una catena di viste — active_users filtrava users, premium_active_users filtrava active_users, e una vista di reporting le joinava tutte. Funzionava benissimo finché qualcuno non ha modificato la tabella users e tutte e tre le viste si sono rotte silenziosamente. Il query planner faceva anche fatica a ottimizzare attraverso più livelli di viste.

Le viste sono ottime per comodità e pattern di accesso in sola lettura. Sono terribili come posto dove codificare logica di business che cambia. Mantieni le regole di business nel codice applicativo dove sono versionate, testate e deployabili. Usa le viste per scorciatoie di reporting, non come blocchi architetturali.

Mettere tutto insieme#

Ecco come appare uno schema ben progettato quando combini questi pattern. Un sistema di gestione progetti semplificato:

sql
-- Estensioni
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- per gen_random_uuid()
 
-- Funzione trigger updated_at (riutilizzabile)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Tenant
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();
 
-- Utenti
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();
 
-- Abilita RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Progetti
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);
 
-- Task
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);
 
-- Commenti ai task
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 (niente RLS — tabella solo per admin)
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);

Nota i pattern:

  • Ogni tabella ha tenant_id e RLS abilitata (eccetto tenants e audit_log).
  • Ogni tabella ha created_at e updated_at con trigger.
  • UUID per tutte le chiavi primarie (entità esposte esternamente).
  • Check constraint su enum di stato, lunghezze e range.
  • Chiavi esterne con comportamento ON DELETE appropriato.
  • Indici progettati per i pattern di accesso (tenant + progetto, tenant + stato).
  • Soft delete solo su users (dove il recupero dell'account è necessario), archiviazione basata sullo stato su projects.

Considerazioni finali#

La progettazione dello schema non è glamour. Nessuno ha mai fatto un talk a una conferenza intitolato "Ho aggiunto check constraint a ogni tabella e ci ha risparmiato sei mesi di debugging." Ma è esattamente quello che fa una buona progettazione dello schema — previene problemi così silenziosamente che non saprai mai che sarebbero esistiti.

I pattern in questo post non sono originali. Sono il risultato di anni di scrittura di migrazioni, debugging di corruzione dei dati e rifattorizzazione di schema sotto carico di produzione. Ognuno di essi esiste perché io, o qualcuno con cui ho lavorato, l'ha fatto nell'altro modo prima e ne ha pagato il prezzo.

Inizia con i vincoli. Usa le chiavi esterne. Aggiungi created_at a tutto. Scegli una convenzione di naming e imponila senza pietà. Usa RLS per la multi-tenancy. Stai attento con JSONB. Testa le tue migrazioni su dati di dimensione produzione prima di fare deploy.

Il database è la fondazione. Fallo bene, e tutto ciò che ci costruisci sopra diventa più semplice. Fallo male, e nessuna quantità di codice applicativo ingegnoso potrà salvarti.

Articoli correlati