Metti i vincoli PostgreSQL prima: ferma presto i bug edge-case dell'AI

-- 1) Totals should never be negative ALTER TABLE invoices ADD CONSTRAINT invoices_total_nonnegative CHECK (total_cents \u003e= 0);

-- 2) Enum-like allowed values without adding a custom type ALTER TABLE tickets ADD CONSTRAINT tickets_status_allowed CHECK (status IN ('new', 'open', 'waiting', 'closed'));

-- 3) Date order rules ALTER TABLE subscriptions ADD CONSTRAINT subscriptions_date_order CHECK (end_date IS NULL OR end_date \u003e= start_date); \n\nUn buon CHECK è leggibile a colpo d'occhio. Trattalo come documentazione per i tuoi dati. Preferisci espressioni brevi, nomi di vincoli chiari e pattern prevedibili.\n\nCHECK non è lo strumento giusto per tutto. Se una regola ha bisogno di cercare altre righe, aggregare dati o confrontare più tabelle (per esempio, “un account non può superare il limite del suo piano”), mantieni quella logica nel codice applicativo, trigger o in un job di background controllato.\n\n## UNIQUE: prevenire duplicati di cui ti pentirai dopo\n\nUna regola UNIQUE è semplice: il database rifiuta di memorizzare due righe che hanno lo stesso valore nella colonna vincolata (o la stessa combinazione di valori su più colonne). Questo elimina una classe intera di bug dove un percorso di “create” viene eseguito due volte, avviene un retry o due utenti inviano la stessa cosa contemporaneamente.\n\nUNIQUE garantisce assenza di duplicati per i valori esatti che definisci. Non garantisce che il valore sia presente (`NOT NULL`), che segua un formato (`CHECK`) o che corrisponda alla tua idea di uguaglianza (maiuscole, spazi, punteggiatura) a meno che tu non lo definisca.\n\nLuoghi comuni dove vuoi di solito l'unicità includono l'email nella tabella utenti, `external_id` da un sistema esterno o un nome che deve essere unico all'interno di un account come `(account_id, name)`.\n\nUn trucco da considerare: NULL e UNIQUE. In PostgreSQL, NULL è trattato come “sconosciuto”, quindi più valori NULL sono permessi sotto una UNIQUE. Se intendi “il valore deve esistere ed essere unico”, combina UNIQUE con `NOT NULL`.\n\nUn pattern pratico per identificatori visibili agli utenti è l'unicità case-insensitive. Le persone scriveranno “[email protected]” e poi “[email protected]” e si aspettano che siano la stessa cosa.\n\nsql -- Case-insensitive unique email CREATE UNIQUE INDEX users_email_unique_ci ON users (lower(email));

-- Unique contact name per account ALTER TABLE contacts ADD CONSTRAINT contacts_account_name_unique UNIQUE (account_id, name); \n\nDefinisci cosa significa “duplicato” per i tuoi utenti (maiuscole, spazi, per-account vs globale), poi codificalo una volta così ogni percorso di codice segue la stessa regola.\n\n## FOREIGN KEY: mantenere le relazioni consistenti\n\nUna FOREIGN KEY dice: “questa riga deve puntare a una riga reale là”. Senza di essa il codice può creare silenziosamente record orfani che sembrano validi isolatamente ma rompono l'app più tardi. Per esempio: una nota che riferisce un cliente eliminato o una fattura che punta a un user id che non è mai esistito.\n\nLe chiavi esterne contano di più quando due azioni avvengono vicino nel tempo: una cancellazione e una creazione, un retry dopo un timeout o un job in background che lavora con dati obsoleti. Il database è migliore nel far rispettare la consistenza rispetto a ogni percorso dell'app che deve ricordare di controllare.\n\n### Scegli il comportamento ON DELETE giusto\n\nL'opzione `ON DELETE` dovrebbe corrispondere al significato reale della relazione. Chiediti: “Se il genitore scompare, il figlio dovrebbe esistere ancora?”\n\n- `RESTRICT` (o `NO ACTION`): blocca la cancellazione del genitore se esistono figli.\n- `CASCADE`: cancellando il genitore si cancellano anche i figli.\n- `SET NULL`: conserva il figlio ma rimuove il collegamento.\n\nStai attento con `CASCADE`. Può essere corretto, ma può anche cancellare più di quanto ti aspetti quando un bug o un'azione amministrativa elimina un record genitore.\n\n### Schemi multi-tenant: modella esplicitamente la proprietà\n\nNelle app multi-tenant, le chiavi esterne non sono solo una questione di correttezza. Prevengono anche la perdita di dati tra account. Un pattern comune è includere `account_id` in ogni tabella posseduta dal tenant e collegare le relazioni attraverso di esso.\n\nsql CREATE TABLE contacts ( account_id bigint NOT NULL, id bigint GENERATED ALWAYS AS IDENTITY, PRIMARY KEY (account_id, id) );

CREATE TABLE notes ( account_id bigint NOT NULL, id bigint GENERATED ALWAYS AS IDENTITY, contact_id bigint NOT NULL, body text NOT NULL, PRIMARY KEY (account_id, id), FOREIGN KEY (account_id, contact_id) REFERENCES contacts (account_id, id) ON DELETE RESTRICT ); \n\nQuesto impone “chi possiede cosa” nello schema: una nota non può puntare a un contatto in un account diverso, anche se il codice dell'app (o una query generata da LLM) ci prova.\n\n## Passo dopo passo: aggiungere vincoli senza rompere la produzione\n\nInizia scrivendo una breve lista di invarianti: fatti che devono essere sempre veri. Tienili semplici. “Ogni contatto ha bisogno di un'email.” “Uno status deve essere uno di pochi valori consentiti.” “Una fattura deve appartenere a un cliente reale.” Queste sono le regole che vuoi che il database faccia rispettare ogni volta.\n\nApplica le modifiche con piccole migration così la produzione non si sorprende:\n\n- Aggiungi la nuova colonna o regola in modo non distruttivo prima.\n- Backfilla le righe esistenti in batch.\n- Ripulisci i dati cattivi (deduplica, correggi valori invalidi) o mettili in quarantena per revisione.\n- Applica la regola (`NOT NULL`, `UNIQUE`, `CHECK`, `FOREIGN KEY`).\n- Restringi il comportamento dell'app in modo che gli errori siano gestiti chiaramente.\n\nLa parte complicata è il dato cattivo esistente. Pianificalo. Per i duplicati, scegli una riga “vincente”, unisci le altre e tieni una piccola nota di audit. Per campi richiesti mancanti, scegli un default sicuro solo se è davvero sicuro; altrimenti metti in quarantena. Per relazioni rotte, riassegna i figli al genitore corretto o rimuovi le righe errate.\n\nDopo ogni migration, valida con poche scritture che dovrebbero fallire: inserisci una riga con un valore richiesto mancante, inserisci una chiave duplicata, inserisci un valore fuori range e fai riferimento a un genitore mancante. Le scritture che falliscono sono segnali utili. Ti mostrano dove l'app si affidava silenziosamente a un comportamento “best effort”.\n\n## Un esempio realistico: un piccolo CRM che resta pulito\n\nImmagina un piccolo CRM: account (ogni cliente della tua SaaS), aziende con cui lavorano, contatti in quelle aziende e trattative legate a un'azienda.\n\nQuesto è esattamente il tipo di app che le persone generano rapidamente con uno strumento di chat. Sembra ok nelle demo, ma i dati reali diventano disordinati in fretta. Due bug tendono ad apparire presto: contatti duplicati (la stessa email inserita due volte in modi leggermente diversi) e trattative create senza company perché un percorso di codice ha dimenticato di impostare `company_id`. Un altro classico è un valore della trattativa negativo dopo un refactor o un errore di parsing.\n\nLa soluzione non è più if-statement. Sono pochi vincoli ben scelti che rendono impossibile memorizzare dati errati.\n\n### I vincoli che mantengono pulito il CRM\n\nsql -- Contacts: prevent duplicates per account ALTER TABLE contacts ADD CONSTRAINT contacts_account_email_uniq UNIQUE (account_id, email);

-- Deals: require a company and keep the relationship valid ALTER TABLE deals ALTER COLUMN company_id SET NOT NULL, ADD CONSTRAINT deals_company_fk FOREIGN KEY (company_id) REFERENCES companies(id);

-- Deals: deal value cannot be negative ALTER TABLE deals ADD CONSTRAINT deals_value_nonneg CHECK (deal_value \u003e= 0);

-- A few obvious required fields ALTER TABLE companies ALTER COLUMN name SET NOT NULL; ALTER TABLE contacts ALTER COLUMN email SET NOT NULL; ```\n\nNon si tratta di essere rigorosi per partito preso. Stai trasformando aspettative vaghe in regole che il database può applicare ogni volta, qualunque parte dell'app scriva i dati.\n\n### Cosa cambia nell'app dopo\n\nUna volta che questi vincoli sono in atto, l'app diventa più semplice. Puoi rimuovere molti controlli difensivi che provano a individuare duplicati dopo il fatto. I fallimenti diventano chiari e azionabili (per esempio, “email già esistente per questo account” invece di comportamenti strani a valle). E quando una route API generata dimentica un campo o gestisce male un valore, la scrittura fallisce immediatamente invece di corrompere silenziosamente il database.\n\n## Errori comuni che rendono i vincoli dolorosi\n\nI vincoli funzionano meglio quando rispecchiano il modo in cui il business realmente opera. La maggior parte del dolore nasce dall'aggiungere regole che sembrano “sicure” al momento ma che diventano sorprese dopo.\n\nUn trabocchetto comune è usare ON DELETE CASCADE ovunque. Sembra pulito fino a quando qualcuno cancella un genitore e il database rimuove metà del sistema. Le cascade possono essere giuste per dati veramente posseduti (come voci di riga di bozze che non dovrebbero esistere da sole), ma sono rischiose per record che le persone considerano importanti (clienti, fatture, ticket). Se non sei sicuro, preferisci RESTRICT e gestisci le cancellazioni intenzionalmente.\n\nUn altro problema è scrivere CHECK troppo stretti. “Status deve essere 'new', 'won' o 'lost'” suona ok fino a quando non servono “paused” o “archived”. Un buon vincolo CHECK descrive una verità stabile, non una scelta temporanea di UI. “amount \u003e= 0” invecchia bene. “country in (...)” spesso no.\n\nAlcuni problemi ricorrenti quando i team aggiungono vincoli dopo che il codice generato è già in produzione:\n\n- Trattare CASCADE come strumento di pulizia e poi cancellare più dati del previsto.\n- Fare CHECK così restrittivi da bloccare casi futuri validi.\n- Assumere che UNIQUE impedisca duplicati quando è coinvolto NULL.\n- Cambiare regole senza un piano per sistemare le righe che già le violano.\n\nSulle performance: PostgreSQL crea automaticamente un indice per UNIQUE, ma le foreign key non indicizzano automaticamente la colonna referenziante. Senza quell'indice, gli update e le delete sul genitore possono diventare lenti perché Postgres deve scansionare la tabella figlia per controllare i riferimenti.\n\nPrima di stringere una regola, trova le righe esistenti che la violerebbero, decidi se correggerle o metterle in quarantena e applica la modifica a step.\n\n## Checklist rapida e prossimi passi per il tuo prossimo build\n\nPrima di pubblicare, dedica cinque minuti per tabella e scrivi cosa deve essere sempre vero. Se lo puoi dire in inglese semplice, di solito puoi farlo rispettare con un vincolo.\n\nChiediti per ogni tabella:\n\n- Cosa non dovrebbe mai essere NULL?\n- Cosa non dovrebbe mai essere duplicato?\n- Cosa non dovrebbe mai essere negativo o fuori range?\n- Cosa non dovrebbe esistere senza un genitore (niente righe orfane)?\n- Cosa dovrebbe sempre seguire una regola semplice (stati permessi, inizio prima della fine)?\n\nSe usi uno strumento di build guidato da chat, considera quelle invarianti come criteri di accettazione per i dati, non note opzionali. Per esempio: “Un importo della trattativa deve essere non negativo”, “L'email di un contatto è unica per workspace”, “Un task deve fare riferimento a un contatto reale”. Più le regole sono esplicite, meno spazio c'è per casi limite accidentali.\n\nKoder.ai (koder.ai) include funzionalità come modalità di pianificazione, snapshot e rollback e export del codice sorgente, che possono rendere più semplice iterare in sicurezza sulle modifiche di schema mentre rafforzi i vincoli nel tempo.\n\nUn pattern di rollout semplice che funziona nei team reali: scegli una tabella ad alto valore (users, orders, invoices, contacts), aggiungi 1-2 vincoli che prevengono i peggiori guasti (spesso NOT NULL e UNIQUE), correggi le scritture che falliscono, poi ripeti. Restringere le regole nel tempo batte una singola migration rischiosa.