TL;DR (aka future GT, read this first next time): After the scraper started working, I had 2,000 raw product records and nowhere to put them. I went through 5 database schemas — flat table → normalized → canon tables → over-normalized mess → the hybrid that stuck. I learned what a foreign key is at 2AM, built a thesaurus for coffee flavors, accidentally cascade-deleted half my roasters, and spent an entire weekend on a three-line Supabase RLS policy. The frontend was the easy part.
The scraper was working.
Data was flowing. New products appearing in the output folder every night. I had names, prices, descriptions, images — the raw ingredients of a coffee directory.
Now I needed somewhere to put it.
I opened Supabase and stared at an empty schema designer. A blank canvas. A database with zero tables. Two thousand records sitting in a JSON file, waiting for a home.
What could go wrong?
Version 1: The Flat Table
One table. Everything in products. Name, roaster, price, description, image. Five columns. Beautiful.
It worked for 10 roasters. By the time I hit 20, the problems were obvious.
"Blue Tokai" was stored as "Blue Tokai Coffee Roasters" in one row and "Blue Tokai Roasters" in another. The same coffee from the same roaster appeared twice because the scraper ran twice and I hadn't built dedup logic. A single roaster with 40 products had 40 rows, and changing the roaster's name meant updating 40 rows.
I don't know what a normalized database is at this point. I know what a headache is. And I have one.
Version 2: I Learn What a Foreign Key Is
I split it into two tables: roasters and products. The roaster table has one row per roaster. The product table has a roaster_id that points back. Foreign key. Referential integrity. If I change the roaster's name, every product sees the update.
I learned this at 2AM. Not because I wanted to. Because the data was lying to me and I had no other way to fix it.
This felt like a breakthrough. I was a little proud. I was also about to discover that I had no idea what a "product" actually is.
The Question That Broke Version 2
Blue Tokai sells Silver Oak.
They sell it in 250g. They sell it in 1kg. Different prices. Different bag sizes. Same name, same roast, same origin.
Is that one product or two?
If it's one product, how do you handle two prices? If it's two products, how do you show that they're the same coffee? What about a roaster who lists each origin as a separate product but offers a subscription box that bundles them?
I spent a week on this question. There is no right answer. There are only tradeoffs.
I ended up storing variants on the product row as a JSON field. Is that elegant? No. Does it work? Yes. The LLM in the scraper handles the variant detection, and the frontend renders them from the JSON. It's a pragmatic hack that I've never had to undo. I'll take that as a win.
Version 3: The Canon Tables
The next problem snuck up on me.
"Chikmagalur" and "Chikkamagaluru" — same place, different spelling. "Fruity" from one roaster means berry notes. "Fruity" from another means citrus. The same estate name spelled differently across three roasters.
I needed a thesaurus. So I built one.
Canon tables. A controlled vocabulary for everything that should be consistent:
canon_regions— so Karnataka, Chikmagalur, Araku Valley each live in exactly one placecanon_estates— so "S. V. Pattabi" is spelled the same everywherecanon_sensory_nodes— so "fruity" actually means something
The scraper's LLM doesn't just transform JSON anymore. It maps incoming data to the canon. New roaster mentions a region I've never heard of? It goes into a review queue. I approve it, add it to the canon, and every future product from that region maps automatically.
I never thought I'd build a thesaurus for coffee. But here we are.
Version 4: The Over-Normalization Trap
I got carried away.
More tables. Finer granularity. Every piece of data in its smallest possible unit. It was beautiful in theory. In practice, every product card needed 7 JOINs. The frontend was simple. The queries were monsters.
I learned about ON DELETE CASCADE by accidentally deleting half the roasters.
The fix was ugly but necessary: selective denormalization. Origin and roast level live directly on the product row now. It's repeated data. That's fine. The canon tables are still the source of truth for labels and lookups, but the product table keeps the fields the frontend needs every single time.
V4 taught me something that no SQL tutorial ever will: normalization is for data integrity. Denormalization is for performance. Both are right. You just have to know which one you're optimizing for.
The RLS Weekend
Supabase Row Level Security.
Three lines of SQL. Two days of my life.
I wanted user reviews. Anybody can read a review. Only authenticated users can write one. Only the review author can edit it. Simple, right?
The first policy I wrote blocked everyone — including me. The second policy let everyone write on everyone else's behalf. The third policy worked in the Supabase dashboard but failed in production. The fourth policy worked but had a performance issue that made the reviews page load for 8 seconds.
On day two, I realized RLS is just SQL WHERE clauses that run automatically. Nobody tells you that. Every tutorial makes it sound like magic. It's not magic. It's WHERE clauses.
I fixed the policy in 15 minutes after that realization. The 2 days were spent not understanding what I was looking at.
Version 5: The Schema That Stuck
It's a hybrid. Some things normalized (roasters, regions, estates). Some things denormalized (roast level on product row). Variants as JSON blobs. Canon tables as a maintenance layer.
1,400 products. 90 roasters. 5 schema versions behind us.
The schema still evolves. A new roaster shows up with a data shape nobody anticipated — a subscription model, a bundle deal, a blend of blends. The schema flexes. But slowly now. Most changes are additions, not migrations.
I spent weeks on this. Nobody sees it. The frontend gets the compliments. The database gets the blame when something breaks.
That's fine. The frontend's job is to look good. The database's job is to not lie. And after 5 versions, it finally doesn't.
But once the data was clean and the schema was stable, I had a new problem: how do you show 1,400 products to someone without making them want to leave? A directory UI with filters for roast, brew method, origin, and price — on mobile, on desktop, in under 2 seconds. That's when the frontend became the problem.
[Next post: Building a directory UI that doesn't look like a spreadsheet]