Relational database design — your schemas as the case study

The craft of relational design: entities → relationships → constraints such that invalid states are unrepresentable.

• Normalization: 3NF — non-key attributes depend on the key. Denormalize deliberately (read-path copies with defined refresh), never accidentally.
• Primary/foreign keys: UUID vs bigserial (Day 41), FK actions encode lifecycle (CASCADE / SET NULL / RESTRICT).
• Join tables for M:N, with composite PKs that are the uniqueness constraint.
• Enums for closed vocabularies (zoth's DB enums), TEXT for evolving ones (traverse).
• Indexes follow queries, not guesses (Day 27–29).

Zoth's 11 tables — the design logic

tenants ──< users >── groups (via user_groups PK(user_id,group_id))
   │         │ self-FK manager_id (ON DELETE SET NULL)
   │         │
   └──< integrations ──< resources (external_id, sensitivity)
   ├──< policies (priority, match/access/approval JSONB)
   ├──< access_requests >── approvals
   ├──< audit_logs (prev_hash, hash — append-only)
   ├──< api_keys (key_hash UNIQUE)
   ├──< notifications
   ├──< policy_documents (embedding vector(768))
   └──< outbox_events / processed_events

Design decisions to narrate:

1. Tenant as the root of every FK chain — cascading deletes from tenants clean the whole org; every query path carries tenant_id for scoping (Day 40).
2. resources has NO tenant_id — ownership flows through integrations (INNER JOIN). A deliberate denormalization away: single source for ownership; resources can't exist without an integration. The cost: every resource fetch is a join — indexed and fine at this scale.
3. access_requests.policy_id ON DELETE SET NULL — historical requests must survive policy deletion; the decision is frozen in the request row, not re-derived. Audit-grade thinking: current state tables + immutable history (audit chain).
4. JSONB for policy rules (match_conditions, access_rules, approval_config) — semi-structured config that evolved in-place without migrations; validated at the service layer (ParseAccessRules etc.). The tradeoff: the DB can't enforce policy shape — the price of schema flexibility.
5. Enums: platform_type, sensitivity, access_request_status, approval_decision, decision_source, audit_action — closed security vocabularies.

Traverse's model — the design logic (30+ models, 742 lines)

Highlights worth narrating:

• Submission/Solve split: Submission = the raw event (every attempt, write-heavy, mutable analytics fields); Solve = the first-acceptance fact (@@unique([userId, problemId]), one per user-problem, holds XP). Event/fact separation — the same distinction as zoth's request/audit.
• Revision + RevisionAttempt: scheduled reviews vs observed attempts — a prediction/observation pair, which is what makes the calibration harness (Day 72) possible.
• RingDay snapshots goals (solveGoal/revisionGoal copied per-day): "did they close rings on the 4th" keeps the answer it had on the 4th — temporal denormalization done right.
• Friendship as double-row adjacency (A→B and B→A): "my friends" is one indexed scan, no OR — a modeling choice that trades write redundancy for read simplicity; symmetry maintained only through upsertFriendshipPair (the one writer).
• StreakDay @@id([userId, date]) — existence is the fact; the streak walk is a date-contiguity scan.
• String state machines + CHECK constraints (FriendStreak userId1 < userId2): flexibility where product iterates, rigor where math demands it.

The drill

Model "collaborative document editing": documents, snapshots (append-only), presence (ephemeral — belongs in Redis, not Postgres!), permissions. The takeaway rule your schemas embody: operational data (Postgres), ephemeral state (memory/Redis), immutable history (append-only tables) — three lifetimes, three stores.