Section 5: The Mirror Is the Asset - Data Modeling
Executive Summary: This section persists Section 4's contracts into one small relational store (D5): the verdict mirror the scheduler fills at 120 req/s, expiring human overrides that beat the vendor (D11), country rules joined to licensed GeoIP ranges, and a snapshots table whose status column is where D10's gates live. The centerpiece is the compiler — four tables in, one signed deny set out — and the section ends on the fact interviewers rarely hear said aloud: this mirror is not a cache you can shrug off losing. The vendor can only be re-asked at 150 req/s, so the mirror is the real archive, and backups are a first-class requirement.
The interviewer looks at Section 4's surfaces: "You've got verdicts trickling in at 120 a second and a 0.7 MB artifact at the edge. What's actually stored in between — and which copy is the truth?" The honest answer sets up everything else: the relational mirror is authoritative, the edge snapshot is derived and disposable (D5). But unlike most derived-data designs, the source here is rate-limited to the point of being nearly write-only from your side — which quietly inverts where the durability money goes.
Six Tables, One Authoritative Store
Capacity first, because it explains the technology choice. The mirror holds ~8M verdicts at steady state; at roughly 100 bytes a row that's under a gigabyte. Retention is selective, and that's what keeps the number there: a one-hit first-look row expires after ~72 hours, and only IPs that keep showing up in traffic — about 250K a day — earn the full 30-day window. Without that pruning, 100M daily uniques would balloon the mirror into billions of rows; with it, "under a gigabyte" and the ~1-day rebuild math both stay true. Writes arrive at the scheduler's pace — 120 req/s, about 10M checks/day — and the compiler reads the whole thing a few times an hour. This is a small, transactional, join-heavy workload: a boring relational database, chosen on purpose.
Four tables are simple enough to state as a table:
| Table | Key fields | Why it is shaped this way |
|---|---|---|
overrides |
range (IP/CIDR), action ALLOW | DENY, reason (required), actor, created_at, expires_at |
Human judgment, audited and expiring: an allow-override wins over a vendor listing until a human re-reviews (D11) — appeals land here, and expires_at forces the re-review instead of hoping someone remembers |
country_rules |
country, action, legal_ref, effective_at |
The China case is a policy row, not 100M IP rows; legal's intent stays legible and auditable, separate from the data that implements it |
geoip_ranges |
range → country, dataset_version, refreshed_at |
Licensed data (~400K ranges, IPv4+IPv6), replaced wholesale weekly plus daily deltas; dataset_version is pinned into every snapshot so "why was this IP Chinese on Tuesday?" has an answer |
audit_log |
actor, action, table, before/after, snapshot_version, timestamp |
Append-only. Every override, policy change and snapshot release lands here; compliance's "prove it" query from D11 reads this table and nothing else |
Note what country_rules and geoip_ranges do together: the rule is one row per country, the data is 400K ranges, and only the compiler ever joins them. Nobody enumerates a country through the vendor's 150-rps API — that split is D2 made into schema.
The two remaining tables 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