Building sbbt.uk: a family groups directory on top of a Google Sheet

sbbt.uk is now live. It’s a searchable directory of baby, toddler and family groups across Stockport — filter by distance from your postcode, child age, activity, day, time of day, term-time policy and whether you need to book.

It exists because the Stockport Bumps, Babies and Toddlers Facebook group maintains a genuinely excellent spreadsheet of local groups, and a spreadsheet is a miserable thing to use one-handed while holding a baby. So the goal was never to replace the sheet — it was to put a decent front end on it.

The sheet stays the source of truth

That constraint shaped everything. No CMS, no database, no admin panel. The volunteers who maintain the data keep editing the same Google Sheet they always have, and the site follows.

There are two refresh paths:

• At build time, scripts/build-data.ts fetches the sheet’s CSV export, normalises it, geocodes any new postcodes, and writes data/groups.json into the image.
• At runtime, the page re-fetches the CSV through Next.js ISR every six hours (next: { revalidate: 21600 }) and merges in the cached coordinates. If the sheet is unreachable, it falls back to the committed snapshot.

Roughly 414 groups at the time of writing, and the whole thing serves as static-ish content off a small Alpine container.

Parsing human-written data

Community spreadsheets are free text, and that’s the interesting engineering problem. The Age column contains things like 0 - 4 Years, Non-mobile Babies, Under 5s, 6m+. lib/parse-age.ts turns all of it into a min/max in months, which is what actually makes “show me groups my 9-month-old can attend” work.

Postcodes get extracted from free-text addresses with a regex, then geocoded via postcodes.io — free, keyless, and generous. New postcodes are the only ones that hit the API; the results land in a committed data/postcodes.json cache, so a rebuild geocodes a handful of rows rather than four hundred.

One genuinely annoying discovery: Google Sheets’ CSV export drops hyperlinks. The Website column is full of cells where the URL lives in the hyperlink rather than the text, and CSV gives you back the link text only. lib/links.ts works around it by also pulling the .xlsx export and reading the hyperlink relationships out of that.

Stable IDs without an ID column

The sheet has a “Unique ref” column, but it’s a hand-maintained positional sequence — MOM1…MOM49 for Monday Morning. Inserting a group in the right day/time block would mean renumbering every row beneath it, so contributors sensibly leave the cell blank, and those rows used to have no ID at all.

Instead each group gets a deterministic v5 UUID, hashed from the fields that identify a session: name, day, time, address and age. All five are needed — on the live sheet, dropping time collides 135 rows and dropping age collides another 11. Genuine duplicates (identical in all five) get separated by an occurrence counter.

Two properties matter here:

• Deterministic — the parser runs again on every ISR revalidation and on every server instance. A random ID would disagree with the routes pre-rendered at build time and 404.
• Position-independent — inserting a row shifts nothing else.

The trade-off is that editing a hashed field changes that group’s ID, and therefore its /g/[id] URL. findGroupById falls back to the committed snapshot so the old route keeps resolving until the next deploy, and a generated redirect map pairs the legacy MOM1-style refs onto current IDs.

Stack and deployment

Next.js 15 (App Router) with React 19, Tailwind v4, TypeScript, Leaflet for the map view. Logic lives in pure functions under lib/ with Vitest tests sitting next to them — CSV parsing, age parsing, filtering and distance sort, URL filter serialisation. That’s the part worth testing; the components mostly aren’t.

Deployment is a multi-stage Dockerfile onto self-hosted Coolify. Releases are cut by release-please from Conventional Commits, and merging the release PR is what triggers a deploy — ordinary commits to main don’t. A nightly GitHub Action pokes the Coolify deploy API at 03:17 so new postcodes get geocoded and website links re-extracted, which ISR alone can’t do.

There’s also a small piece of content negotiation in middleware.ts: request a page with an Accept header asking for markdown and you get markdown back instead of HTML, with the /md/* routes staying directly reachable for anything that would rather not rely on headers. Agents read the site more comfortably than they read a React app.

The failure that didn’t look like one

Worth recording, because it took a while to spot. Coolify was set to the Nixpacks build pack rather than Dockerfile. Nixpacks runs pnpm build and nothing else — build-data is an explicit RUN in the Dockerfile, not a prebuild hook, so it simply never executed.

Nothing broke. The site kept serving current groups, because runtime ISR fetches the sheet independently of the build. The nightly refresh workflow stayed green, because Coolify returns 200 for a deploy that skipped the data step. What actually happened was slow decay: new postcodes never got coordinates, and website links — keyed by content-derived ID — were orphaned every time a sheet edit changed a row’s ID, with nothing to re-extract them.

Comparing production against a local Dockerfile build made it obvious:

The lesson isn’t “use the right build pack”. It’s that a build step whose output degrades gradually needs something that fails loudly when it doesn’t run. build-data now logs its counts, and /api/health has thresholds behind it.

If you’re in or around Stockport, have a look: sbbt.uk. If a group is missing or wrong, the fix goes in the sheet — the site catches up on its own.