Databases and Data Modeling

Your data model outlives your code. You'll rewrite the UI three times and refactor the backend twice, and the schema decisions you made in week one will still be shaping what's easy and what's impossible.

The concept

A data model is a set of claims about what exists in your product and how those things relate. Get it approximately right and features fall out easily. Get it wrong and every feature fights the schema.

Relational vs document: decide honestly

Most products are relational and choose document stores for the wrong reasons, usually "flexibility" early on, which becomes "we have six shapes of the same object in production" later.

Default to Postgres. It handles relational data, JSON columns when you genuinely need flexibility, full-text search, geospatial, and it's the most portable choice. Reach for something else when you have a specific reason you can articulate.

⭐ Model facts, not positions

This is the single most valuable modelling principle in the chapter.

A fact is something that happened. "User 42 completed item 7 at 14:03." A position is a summary of facts. "User 42 is on item 8."

Positions are smaller and feel efficient. They also encode assumptions, usually that things happen in strict order, that reality violates:

Sets survive out-of-order events, concurrency, multi-device sync, partial restores, and analytics questions you haven't thought of. Positions survive none of them.

The general rule: store the events, derive the summary.

Derive, don't store

Anything computable from source-of-truth data should be computed:

Stored duplicates drift. You fix a bug in the calculation and the stale value lives on in a row you'll never revisit, or worse, on a device you can't reach.

The legitimate exception is caching for performance, and when you do it, label it in the schema:

points_total  int default 0,   -- CACHE; recompute from source. Not truth.

That comment is what stops someone later treating it as authoritative.

Never let one field answer two questions

A subtle failure worth naming: a single scalar that means both "how many" and "which one." It works perfectly until the first out-of-order event, then fails silently. If a variable's name could plausibly be either a count or an index, split it into two.

Keys and identity

• Every row needs a stable ID. UUIDs (globally unique, client-generatable, no collision on merge) or bigint sequences (smaller, ordered). For anything that syncs, prefer UUIDs.
• Never key on array position or display order. Reordering invalidates every stored reference.
• Content needs stable IDs too. If lesson 5 becomes lesson 7, references keyed on position now point at the wrong thing, silently.

Constraints are free correctness

Use the database to make invalid states impossible:

unique (user_id, item_id)              -- no duplicates, ever
foreign key … on delete cascade        -- no orphans; deletion actually deletes
not null                               -- no half-rows
check (rating between 1 and 5)         -- no impossible values

Every constraint is a class of bug that can't reach production, enforced somewhere your application code can't bypass.

Row-level security

If clients talk to your database directly (Supabase, Firebase), RLS is the only thing standing between users and each other's data. Your publishable key is public by design; that's fine only if row rules are correct.

alter table items enable row level security;
create policy "own items" on items
  for all using (auth.uid() = user_id)      -- which rows you can READ
       with check (auth.uid() = user_id);   -- which rows you can WRITE

Both clauses. Omit with check and a user can insert rows owned by someone else.

Anything that grants access must not be client-writable. A subscriptions table should be for select only, written exclusively by a server-side webhook.

Migrations

• Every schema change is a migration file, committed to the repo, applied in order.
• Additive first. Add a nullable column, backfill, then make it required. Never a breaking change in one step.
• Expand → migrate → contract for renames: add new, write both, backfill, switch reads, drop old.
• Test migrations on a copy of production data, not an empty database.

Indexes

Add an index when a query is slow, on the columns in WHERE, JOIN and ORDER BY. Every index costs write speed and storage, so don't add them speculatively. Use EXPLAIN ANALYZE to see what the planner actually does.

Foreign keys are the exception, index them proactively; they're almost always queried.

📐 Best practice

Model facts; derive summaries.

Stable IDs on everything, including content.

Constraints in the database, not just in application code.

RLS with both using and with check, on every user table.

Access-granting tables are read-only to clients.

Label caches in the schema.

Migrations in the repo, additive first, tested against real-shaped data.

on delete cascade where deletion should genuinely propagate, it's also what makes account deletion actually delete (Chapter 40).

Pin search_path on security definer functions. Omitting it is a known privilege-escalation vector.

Run your platform's security advisor.

💀 Common mistakes

Storing positions instead of facts. Breaks on the first out-of-order event, silently.

One field answering two questions.

Storing derived values. They drift and you can't reach them to fix.

Keying on array index or display order. Reordering silently corrupts references.

No stable content IDs. Same failure, one layer up.

Constraints only in app code. Bypassed by scripts, migrations, and other clients.

RLS with only using. Users can write rows they don't own.

Client-writable entitlement tables. Your paywall becomes a suggestion.

Breaking migrations in one step. Deploy order becomes a race.

Testing migrations on an empty database. The one that breaks is the one with ten million rows and a null you didn't expect.

Indexing everything speculatively. Slower writes, wasted storage, no benefit.

security definer without a pinned search_path.

The professional workflow

 1. LIST THE ENTITIES and their relationships. On paper first.

 2. FOR EACH: what FACTS are recorded? What is DERIVED?

 3. STABLE IDs everywhere — including content

 4. WRITE THE SCHEMA with constraints:
    unique · foreign keys + cascade · not null · check

 5. ROW-LEVEL SECURITY on every user table
    using AND with check · access-granting tables read-only

 6. WRITE MIGRATIONS as files, in the repo

 7. SEED realistic data — including the awkward cases

 8. INDEX where queries are actually slow (EXPLAIN ANALYZE)

 9. RUN THE SECURITY ADVISOR

10. TEST: another user's data unreachable · cascade deletes work ·
    replay is a no-op · restore reconstructs correctly

Tools, websites & costs

Alternatives & trade-offs

ORM vs raw SQL. ORMs give type safety and speed for CRUD, and hide the query plan. Raw SQL gives control and costs boilerplate. Query builders (Drizzle, Kysely) are a good middle, typed, close to SQL.

Normalised vs denormalised. Normalise until a query is genuinely slow, then denormalise deliberately with a labelled cache and a documented refresh path.

Soft delete vs hard delete. Soft delete (deleted_at) preserves history and complicates every query and your privacy obligations. Hard delete with cascade is simpler and required for genuine "delete my data" compliance. Many products need both, soft for recoverable user actions, hard for account deletion.

UUID vs sequential IDs. UUIDs: no enumeration, client-generatable, merge-safe, larger and unordered. Sequential: compact, ordered, and they leak how many customers you have. UUIDv7 gives you both, unique and time-ordered.

Multi-tenancy (B2B). Shared tables with a tenant_id and RLS is simplest and scales well. Schema-per-tenant gives stronger isolation and painful migrations. Database-per-tenant is strongest and heaviest. Start with tenant_id + RLS.

Checklist

• I store facts, and derive summaries
• No field answers two questions
• Everything has a stable ID, including content
• Nothing references array position or display order
• Constraints in the DB: unique, FK + cascade, not null, check
• RLS on every user table, using AND with check
• Access-granting tables are read-only to clients
• Caches are labelled as caches in the schema
• Migrations are files in the repo, additive first
• Migrations tested against realistic data
• Indexes added where queries are measurably slow
• security definer functions pin search_path
• I ran the security advisor
• Verified: cross-user access fails, cascade works, replay is a no-op

📓 Case Study: one integer that answered two questions

Project: SOLIS.

The schema got the core principle right and wrote it down:

Pillars and streak are DERIVED from completions (never stored, so they can't drift).

A completions table was the source of truth; four progress statistics, a streak, a rank and a day number were all computed from it. Nothing to drift, nothing to migrate when a calculation changed. The one deliberate cache carried a comment marking it as such, points_total int default 0, -- CACHE; recompute from sources, which is exactly the discipline that stops someone later treating it as authoritative.

Row-level security was applied properly, with both clauses on every user table, and one table treated differently for a good reason:

create policy "own completions" on lesson_completions
  for all using (auth.uid() = user_id) with check (auth.uid() = user_id);

-- entitlement mirror: written ONLY by the payment webhook (service role)
create policy "read own sub" on subscriptions for select using (auth.uid() = user_id);

for select only. A user can read their subscription; they cannot write it. Anything that grants access must not be client-writable, or your paywall is a suggestion.

A trigger auto-created a profile row for every new auth user, including anonymous ones, which removed a whole class of "orphaned auth user with no profile" bugs. And the platform's security advisor caught two real issues, including a security definer function that hadn't pinned its search_path, a genuine privilege-escalation vector that's easy to miss.

💀 The bug: a prefix pointer that couldn't count.

Progress was stored as a single integer, "how many items completed from the start." Compact, obvious, and it encodes an assumption: items are completed in strict sequential order.

Reality violated it. A development flag unlocked all content for review, and real users could reach items out of order too. The report, on day 22:

"if i dont click on the 1st lesson and complete the 2nd or 3rd lesson i do get the rewards but the tick icon is not shown on that lesson, so i went back and did the lesson 1 first and then i got the tick on lesson 1"

Two failures from one field:

• The completion mark rendered for index < position, so completing item 3 with position = 0 marked nothing, or the wrong row.
• The completion only counted when the item happened to equal the current position, so out-of-order work silently vanished.

The root cause is the deeper lesson. That single integer was doing two jobs:

Those are different questions, and they diverge the moment order isn't guaranteed.

The fix replaced the position with a set of facts:

isDone(id)        // any item, any order — a fact
completedCount()  // "how many"  → rank, points
currentIdx()      // "which one" → first incomplete

And the change rippled outward, which is the part worth anticipating: sync had to be rewritten too. Backfill now uploaded the whole set rather than a number, and the restore path had to reconstruct every completion by mapping stored identifiers back to items, not just restore "the longest prefix." Old data was migrated once on load.

📐 Migrating your data model means migrating your sync. Budget for it.

⚠️ Deviation: content had no stable IDs.

Lessons were addressed by array position. Reordering the curriculum would invalidate every stored progress reference and every cross-file link between lessons and their associated questions, silently, with no error, just quietly wrong content.

A stable id: "dawn-hour" field costs nothing on day one and saves a migration later. It was identified as a known gap and never added, because by the time it was noticed there was already data keyed on positions.

The general shape: the schema was well-designed where it had been designed, and the failures were all in places where a convenient shortcut encoded an assumption nobody had written down.

Lessons

1. ⭐ Model facts, not positions. Sets survive out-of-order events, sync, concurrency and partial restores. Pointers survive none of them.
2. Never let one field answer two questions. If it could be a count or an index, split it.
3. Derive summaries; never store them. And when you must cache, label it in the schema.
4. Stable IDs on everything, including content. Positional references corrupt silently on reorder.
5. Constraints in the database. Every one is a bug class that can't reach production.
6. RLS needs both using and with check.
7. Access-granting tables must be read-only to clients.
8. Pin search_path on security definer functions.
9. Run the platform's security advisor, it finds things you won't.
10. Changing the data model means changing sync. Anticipate the ripple.

Next: Chapter 21: Authentication and Identity →