Hoopy by Michael Rork Get Pockets by HoopyGet Pockets

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.

Role
Database design and full-stack build
Built with
Astro and TypeScript, PostgreSQL on Supabase, Vercel
Status
Live
See it
snarkindustriespod.com ↗

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.

The live site, built on the new database.
The character database: 308 characters, filterable by reality, affiliation, species and powers.
The timeline: every piece of Marvel media in in-universe order.

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.
The schema blueprint: planned in writing, revised 29 times, before the tables existed.
The records: what pages are built from.
The join tables, which show up in the CMS as multi-select chips.

Stack

  • Astro
  • TypeScript
  • PostgreSQL 17
  • Supabase auth, storage and row-level security
  • SQL tests
  • Quill
  • Vercel
  • Python for the migration

Have something that needs building, or a role I'd fit?

michael@byhoopy.com

Burlington, Vermont · LinkedIn