Section 5: Owning the Edge, Not the Ends - Data Modeling

Executive Summary: This section designs the five tables behind Section 4's endpoints. A tag gets a tenant-scoped surrogate id, so a rename is a one-row update. Content is referenced by immutable ids, never by keys like PAY-4312 that change when an issue moves. One tag_assignment table carries a forward primary key and two reverse indexes, and every row stores the container and restricted flag that the permission funnel in Section 8 depends on. Per-container counts and an outbox row are written in the same transaction, and every key starts with tenant_id, so each tenant lives on exactly one of 64 Postgres shards, one of them dedicated to Meridian. The lesson: we own only the edge between a tag and a piece of content, so the model has to carry just enough about the ends to filter safely and stop there.

The interviewer erases the API from the whiteboard and leaves one line in the corner: PUT /v1/content/{contentRef}/tags/{tagName}.

"Okay. What's actually stored when Dana hits that?"

The tempting answer is a two-column join table, (content, tag). It's the right shape at the wrong level of detail. Three questions sit underneath it: what is a tag, what is a piece of content, and what must we know about content we don't own? Answer those and the tables almost write themselves.

A Tag Is an Id With a Name, Not a Name

If the tag's name is the key, every assignment row stores "checkout-outage". Renaming a 1.2M-assignment tag like Meridian's frontend would rewrite 1.2M rows and every index entry pointing at them. So a tag gets a tenant-scoped surrogate tag_id (Dana's is 90017), and assignments store only that id. Rename becomes O(1): one update to one tag row. The rejected alternative, name-as-key, pays for every rename with a bulk job.

Uniqueness lives on normalized_name, and the normalization rule has to be written down, because it decides which two strings count as "the same tag" forever:

The rule is Unicode's NFKC_Casefold, then trimming and collapsing whitespace, with a limit of 64 characters after normalization. It's NFKC plus full case folding, but defined to be idempotent, which a uniqueness key needs: plain NFKC followed by a fold can produce a string that changes again on a second pass. The display name keeps whatever the creator typed. Plain ASCII lowercasing is rejected because it leaves ß alone and mishandles the Turkish dotted İ. Accent folding is rejected because "café" and "cafe" can be genuinely different terms, and folding them would silently merge vocabularies. The flip trigger: if users drown in near-duplicates, the fix is synonyms and suggested merges (Section 11), not more aggressive folding.

Normalization runs in one place, the Tag API, and the database enforces the result with UNIQUE (tenant_id, normalized_name). A rename that collides with an existing name is really a merge, and the API says so with a 409. Deleting a tag has to free its name while the deleted row lives on until the purge finishes, so delete rewrites that row's normalized_name to a unique sentinel, chr(1) || 'deleted:' || tag_id. The full unique constraint and Section 7's ON CONFLICT (tenant_id, normalized_name) stay valid as written, and autocomplete reads only state = 'active' rows (Section 9 has the delete flow).