Lesson 2 of 8 · 52 min

Data modeling, transactions, and when not to normalize

Entities and invariants, normalization tradeoffs, transactions and isolation, indexes, migrations, and modeling LLM runs for production.

Lesson 2 · Data models

Entities, transactions, isolation, when to denormalize

Schema is product policy in disguise

Tables encode what can be true. Bad models create impossible states, race bugs, and “we will fix it in the app” validation that two writers bypass. AI features add runs, artifacts, token usage, and prompt versions that must be queryable for billing and evals. This lesson is relational thinking for production backends — not ORMs as religion.

Start from entities and invariants: what must never happen even under concurrent writers? “A run cannot complete without a model id.” “A user cannot own two active subscriptions.” Invariants either live in the schema (constraints, unique indexes) or they are optional suggestions. Interviewers listen for whether you put the critical ones in the database.

Entities, keys, and ownership

Name the aggregate root (Account, Project, Run). Child rows get foreign keys with explicit ON DELETE behavior. Prefer stable surrogate ids (ULID/UUID) for public APIs; keep human-readable slugs separate. Multi-tenant systems need tenant_id on every tenant-owned row and indexes that start with it — forgotten tenant filters are data leaks.

sql

1-- Core LLM run model (sketch)2CREATE TABLE llm_runs (3	id              UUID PRIMARY KEY,4	tenant_id       UUID NOT NULL REFERENCES tenants(id),5	project_id      UUID NOT NULL,6	status          TEXT NOT NULL CHECK (status IN7		('queued','running','succeeded','failed','cancelled')),8	model           TEXT NOT NULL,9	prompt_version  TEXT NOT NULL,10	input_tokens    INT,11	output_tokens   INT,12	error_code      TEXT,13	created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),14	updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()15);16CREATE INDEX llm_runs_tenant_created_idx17	ON llm_runs (tenant_id, created_at DESC);18-- status transitions enforced in app + optional state table

Normalization vs denormalization

3NF reduces update anomalies: store user email once. Denormalize when a read path’s latency or fan-out dominates and you accept sync cost: counters on posts, last_message_at on threads, cached model display names on runs. Senior answers name the write path that keeps the copy honest (trigger, app transaction, async worker) and the staleness budget.

Transactions — the unit of truth

A transaction groups writes that must succeed or fail together: create run + enqueue outbox row; debit balance + insert ledger line. If you “commit then publish to queue,” you will lose messages or process ghosts. Prefer transactional outbox or dual-write patterns you can defend.

sql

1-- Transactional outbox (same TX as business write)2BEGIN;3  INSERT INTO llm_runs (id, tenant_id, status, ...)4    VALUES ($1, $2, 'queued', ...);5  INSERT INTO outbox (id, topic, payload, created_at)6    VALUES ($3, 'llm_run.queued', $payload, now());7COMMIT;8-- Publisher polls outbox / CDC and emits to queue at-least-once.9-- Consumer is idempotent on run id.

Isolation levels (what actually bites)

Read Committed (Postgres default) prevents dirty reads but allows non-repeatable reads and anomalies under concurrency. Repeatable Read / Snapshot reduces phantoms for many workloads. Serializable is safest and most expensive — use for money and scarce inventory. Name the anomaly you fear (lost update on status, double spend) and pick the cheapest level that closes it — often with SELECT FOR UPDATE or optimistic version columns.

sql

1-- Optimistic concurrency on status transitions2UPDATE llm_runs3SET status = 'running', version = version + 1, updated_at = now()4WHERE id = $1 AND status = 'queued' AND version = $2;5-- 0 rows → conflict; reload and decide (409 to client or retry)

Indexes that match access paths

Index for the query you run in production: (tenant_id, created_at DESC) for lists; unique (tenant_id, external_id) for upserts. Avoid indexing every column “just in case.” Write amplification and bloat are real. For LLM usage billing, plan aggregate tables or periodic rollups — do not SUM tokens across raw runs on every dashboard load forever.

When not to force everything into Postgres

Hot ephemeral tokens → Redis. Huge artifacts → object storage with pointers in SQL. Analytical token trends → warehouse via CDC. Full-text over prompts → search index as derived view. Senior signal: system of record vs derived view said out loud.

Migrations as production changes

Expand/contract: add nullable column → backfill → switch reads → enforce constraints. Never lock a huge table with a naive ALTER in peak traffic without a plan. Dual-write periods need metrics. Treat migrations like deploys: smoke, rollback story, and “what if half the fleet is on old code.”

LLM-specific modeling

Version prompts and tools as immutable rows (prompt_version). Store token counts and cost estimates on the run for finance. Keep raw prompts out of logs if they contain PII — store redacted copies or encrypted side tables. Eval sets are data products: freeze fixtures so regressions are comparable.

Treat model id + prompt version + tool schema version as part of the run’s reproducibility tuple. When a customer disputes an output six weeks later, you need to know what code path produced it. Mutable “edit the prompt in place” rows destroy that audit trail — append new versions and point runs at them.

Referential integrity under deletes

Decide ON DELETE for every FK: RESTRICT (block delete of tenants with runs), CASCADE (delete children with parent — dangerous for audit), or SET NULL. Soft-delete (deleted_at) keeps history but complicates unique indexes — use partial uniques on active rows. Interview signal: you name the delete policy before drawing the ERD polish.

Events as facts, projections as views

If you emit domain events (run.completed), keep a clear line: the OLTP row is truth for product state; search indexes, analytics warehouses, and notification fan-out are projections. Rebuilding a projection from events is a feature; rebuilding billing from “maybe the queue had it” is an incident. Link this to the outbox pattern from earlier in the lesson.

Capacity notes that change schema

A runs table at 10 inserts/s is trivial. At 5k inserts/s you think about partitioning by time, archiving cold runs to cheaper storage, and not indexing unboundedly. Schema design includes lifecycle: how long raw prompts live, when artifacts move to cold storage, and which columns are needed for list vs detail queries (covering indexes vs wide rows).

Interview answers — data modeling

01Q: Normalize or denormalize? Normalize for write integrity; denormalize when a measured read path needs it and you name the sync writer and staleness.
02Q: Where do invariants live? Critical ones in DB constraints/unique indexes/transactions; app checks are UX, not the last line of defense.
03Q: Lost update on status? Optimistic version column or SELECT FOR UPDATE; return 409 on conflict; never blind UPDATE status.
04Q: Dual write DB + queue? Transactional outbox or CDC — not commit-then-publish without a recovery story.
05Q: Multi-tenant isolation? tenant_id on rows, composite indexes, forced filters, optional RLS; test with cross-tenant queries in CI.
06Q: UUID vs serial? UUID/ULID for distributed ids and API opacity; serial can bottleneck or leak volume; either needs a unique business key when humans care.
07Q: Isolation level pick? Default read committed + targeted locks; raise for money/inventory; justify cost with the anomaly prevented.
08Q: Store embeddings where? Vector store or pgvector for ANN; relational metadata in OLTP; do not pretend a pure SQL JOIN replaces ANN search at scale.
09Q: Soft delete? Useful for undo/audit; every query must filter; unique constraints get harder — partial unique indexes help.
10Q: Migration strategy? Expand/contract, dual read if needed, backfill online, then constrain — avoid long exclusive locks on hot tables.

docsPostgreSQL Documentation — Transaction IsolationPostgreSQL docsPostgres.fm / design patterns — use official constraints docsPostgreSQL articleTransactional Outbox pattern (Microservices.io)microservices.io docsDesigning Data-Intensive Applications — overview (book site)Kleppmann

Checkpoint

Two workers concurrently try to move the same llm_run from queued → running. How do you prevent double start?

AAdd a sleep(random) before update so collisions are unlikely in practice.BConditional UPDATE … WHERE status='queued' (and/or version match); only one transaction affects a row; the other handles 0-rows as conflict.COnly check status in application memory before updating; skip WHERE on status.

Checkpoint

You need “unread count” on every project list row. Counts are read 1000× more than incremented. Best modeling instinct?

AAlways COUNT(*) from events on every list request for perfect accuracy.BDenormalized counter updated in the same transaction as the event write (or via reliable consumer), accept rare reconcile jobs.CStore counts only in the client localStorage so the server stays pure.

Checkpoint

Why is commit-then-publish-to-Kafka a footgun for “run created” events?

AKafka cannot store JSON payloads from SQL databases at all.BProcess can crash after COMMIT and before produce — consumers never see the run; or produce succeeds and DB rolls back in other orderings depending on code.CTransactions are obsolete once you use microservices.

Checkpoint

A unique constraint on (tenant_id, external_id) fails on insert. What API behavior is usually right?

AReturn 500 and page on-call because unique violations are server bugs only.BTreat as conflict/idempotent replay: return 409 or the existing resource if the insert was a safe retry with the same payload.CDrop the unique constraint so inserts always succeed.

Checkpoint

Where should multi-megabyte model outputs live by default?

AAlways inline in a Postgres TEXT column so joins are easy forever.BObject storage for bytes; Postgres holds metadata, hashes, sizes, and pointers for transactions and listing.COnly in the LLM provider’s dashboard — never store outputs yourself.

Can you sketch entities, invariants, and a transaction boundary for an LLM run + billing line?

New to itGetting thereConfident

Takeaways

Invariants belong in constraints and transactions, not only app ifs.
Denormalize with a named writer and staleness budget — not vibes.
Outbox/CDC beats commit-then-publish for reliable events.
Model prompts, versions, tokens, and artifacts as first-class product data.

Next: sessions vs JWT, RBAC, and service-to-service identity.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free