Case study · Snark Industries
A podcast outgrew its page builder. I gave it a real database, and a CMS to run it.
Snark Industries is a Marvel podcast whose hosts track an intersecting-timeline project: episodes, characters, realities, comics, who killed whom and when. I designed the database from scratch, built the site on top of it, and built SnarkCMS so the hosts enter and link everything themselves.
The problem
Remy, one of the hosts, had a genuinely ambitious idea for a show archive and had taken it as far as Wix would go. A page builder’s collections are flat lists, so the same fact had to be typed onto every page that needed it, and the copies drifted apart. A character’s affiliations, a kill, the reality an episode visits. None of it could be filtered or counted, because none of it was really data. The design was whatever the template allowed.
Why I built it
He had thought every detail through. He just didn’t have tools that could express them. That’s the kind of problem I like best: the person knows exactly what they need, and the software is what’s in the way.
How I built it
- Schema first. Every kind of thing the show tracks gets its own table, about fifty in all: episodes, characters, appearances, realities, mantles, powers, species, comics, artifacts, locations, kills and milestones, linked through join tables wherever one thing can belong to many others. A fact is typed once and every page that needs it reads it. A character’s kill count is derived from the kills, never stored, so it can’t go stale.
- Drafts that can’t leak. Editing a published episode or character edits a working copy, so a half-finished thought never reaches the public site. Publishing writes the copy back over the live record in place, keeping its id, its URL and its history; discarding just deletes the copy. One registry decides what gets copied, what moves on publish and what goes on discard, down to grandchildren like a milestone under an appearance.
- Tests that guard the quiet failures. Forgetting a table in that registry doesn’t error, it silently drops data at publish. So there are SQL tests that compare the registry against every foreign key, check that a copy is faithful, and check that a publish keeps what was added in the draft.
- Two editors, no collisions. An open edit beats a heartbeat every 15 seconds. A record someone else is editing won’t open, and taking it over is an explicit choice. A tab that has lost the record is refused when it tries to save, so it can’t publish over the person who holds it.
- Links that survive a rename. Episode and character URLs are generated once and frozen. Renaming a record keeps the old URL as a redirect, so links and search rankings keep working.
- Images at the size they’re shown. The library keeps full-size originals, about three quarters of a megabyte each, and the site asks storage for each image at the exact size and crop it’s displayed at.
- A one-way move off Wix. Python scripts pulled the old site’s content apart into the new tables, so nothing had to be retyped by hand.
Stack
- Astro
- TypeScript
- PostgreSQL 17
- Supabase auth, storage and row-level security
- SQL tests
- Quill
- Vercel
- Python for the migration