Section 5: The Constraint Is the Promise - Data Modeling
Executive Summary: This section persists Section 4's contracts into one relational database (D3): canonical runs, a hot health row per pipeline, nightly baselines, incidents with an append-only timeline, escalation policies, and a transactional outbox. The centerpiece is a single partial unique index that makes "exactly one open incident per outage" a property of the schema rather than a hope about the code (D6). We walk INC-4312's actual rows through the Tuesday timeline and defend the two choices interviewers always probe: constraint-versus-lock, and outbox-versus-calling-Slack-in-the-transaction.
The interviewer leans back from Section 4's API sketch: "Fine, the contracts are clean. What's actually on disk, and how do you stop two of these services from opening two incidents for the same outage?" The second half of that question is the whole section. At 23 events per second, storage design is not a capacity problem — Section 3 already told us a normalized run row is ~1 KB, so 100K runs/day is 100 MB/day and the full 13 months of history the baselines need is ~40 GB, a size one Postgres instance yawns at. What the schema must earn is something else: every promise this system makes to a human has to survive retries, crashes, and two processes doing the same work at once.
One Database, Eight Tables
You say it plainly: "Everything lives in one relational database (D3) — runs, health, incidents, policies, audit. Not because it's fashionable, but because the guarantees I'm about to make are transactions and constraints, and those are the two things a streaming stack doesn't sell." Health and incidents get separate tables on purpose (D5): health is derived and recomputable with no human in it; an incident is a durable record of response — who was paged, who acked, how long it hurt — and you can never recompute that.
Five tables are straightforward enough to state as a table:
| Table | Key fields | Why it is shaped this way |
|---|---|---|
pipelines |
pipeline_id, provider + external ref, owner_team, default_branch, timeout_budget, tags (e.g. release-blocking), overrides JSONB |
Ownership routing and per-pipeline thresholds live with the pipeline, so detection reads one row to know the rules |
runs |
pipeline_id, unique (provider, external_run_id), attempt, branch, trigger, conclusion enum, started_at, finished_at |
The unique key makes webhook + reconciliation upserts converge on one row (~1 KB; 13-month retention — our baselines outlive provider retention) |
baselines |
pipeline_id, window_days = 30, failure_rate, computed_at |
Recomputed nightly from runs; per-pipeline, never global — no single bar fits both a chronically flaky suite and a deploy that never fails |
incident_events |
incident_id, event_type (created, severity_changed, paged, acked, child_linked, resolved), actor, at, detail JSONB |
Append-only. The timeline and the audit trail are the same table; nothing here is ever updated |
escalation_policies |
policy_id, steps JSONB (validated monotonic in time and severity), repage_interval |
A policy edit creates a new row; open incidents keep evaluating the steps they started with |
One relationship in that table is worth saying out loud: raw events upsert by the four-field key (provider, external_run_id, attempt, event_type); the run row they fold into is unique by (provider, external_run_id) and carries the final attempt's conclusion.
The remaining three carry the teaching weight, so they get real DDL.
Quick check before moving on:
Sign in to continue reading
The rest of this lesson is available with a free account. Signing in with Google or Microsoft is free.
Sign in to read the full lesson