Module 03Term 1 · Foundations8 poster items

Data Foundations

Sign in to save your progress across devices. Until then it is kept in this browser.

Why this matters

Code can be redeployed. Data cannot be un-corrupted. The database is the part of the system you can least afford to get wrong, and the part AI tools treat most casually.

Learning outcomes

  1. Model data with keys, constraints and multi-tenancy from day one.
  2. Find and fix slow queries with EXPLAIN and indexes.
  3. Eliminate N+1 queries.
  4. Configure connection pooling for serverless.
  5. Use transactions and locking to keep data correct under concurrency.
  6. Ship schema changes with zero downtime.

Lessons

3.1 Modelling for correctness

  • Let the database enforce rules: NOT NULL, UNIQUE, CHECK, foreign keys. Application checks race; constraints do not.
  • Money: integer minor units (cents) or numeric, never float.
  • Time: store UTC (timestamptz), convert at display.
  • IDs: UUIDv7 or similar for public IDs; never expose sequential IDs that let attackers enumerate records.
  • Multi-tenancy: every row belongs to an org_id / user_id, and every query is scoped by it. Enforce it in the database with Row-Level Security (Postgres RLS), not only in app code.

3.2 Indexing and query optimisation (25, 26)

  • An index is a sorted lookup structure; without one, the DB scans every row.
  • Index columns you filter, join and sort by. Composite index order matters (equality columns first, then range).
  • Every index slows writes and costs storage; do not index everything.
  • Read EXPLAIN ANALYZE: look for sequential scans on large tables, high row estimates, sorts spilling to disk.
  • Turn on slow-query logging (pg_stat_statements) and review weekly.

3.3 N+1 queries (27)

// N+1: 1 query for orders + 1 per order for its customer
const orders = await db.order.findMany();
for (const o of orders) o.customer = await db.customer.findUnique({ where: { id: o.customerId } });

// Fixed: 2 queries total (or a join)
const orders = await db.order.findMany({ include: { customer: true } });

Detect with query logging in tests: assert the number of queries per request.

3.4 Connection pooling (28)

Each Postgres connection uses memory; databases cap connections (often around 100). Serverless functions open one per invocation and exhaust it under load. Use a pooler (PgBouncer, Supavisor, Neon pooler, Prisma Accelerate) in transaction mode for serverless.

3.5 Transactions and locking (36, 37)

  • Wrap multi-step writes in a transaction: all or nothing.
  • Optimistic locking: version column; UPDATE ... SET version = version + 1 WHERE id = $1 AND version = $2; zero rows updated means someone else changed it, so retry or report.
  • Pessimistic locking: SELECT ... FOR UPDATE locks the row inside a transaction. Use for contended resources like balances and inventory.
  • Example bug: two simultaneous "approve loan" clicks both read status = pending and both disburse. Fix with a conditional update (WHERE status = 'pending') and check the affected row count.

3.6 Migrations and schema versioning (81, 82)

  • Every schema change is a versioned migration file in git, applied by CI, never by hand in a dashboard.
  • Expand and contract for zero downtime: (1) add new column nullable, (2) deploy code writing both, (3) backfill in batches, (4) switch reads, (5) drop the old column in a later release.
  • Dangerous operations: adding NOT NULL without default on big tables, renaming columns in use, creating indexes without CONCURRENTLY.
  • Never let an AI agent run migrations against production directly.

Lab 3: Break and fix your data layer

  1. Seed 1 million rows. Find the three slowest queries; fix with indexes; show before/after EXPLAIN.
  2. Add a query-count assertion to an API test; find and fix an N+1.
  3. Write a test that fires 20 concurrent "redeem voucher" requests; prove the voucher is redeemed exactly once.
  4. Enable RLS on every table; write a test that user A cannot read user B's rows using the public anon key.
  5. Perform an expand-and-contract column rename without downtime.

Definition of Done

AI prompt pack

  • "List every query this endpoint executes and flag any inside a loop."
  • "Write the RLS policies for these tables so users only access rows of orgs they belong to. Then write tests trying to bypass them."
  • "Plan this schema change as an expand-and-contract migration across separate deploys."