iSpotly · data architecture · as-built 2026-07-17

What in your Sheet is sent where

Nothing the player sees reads the Google Sheet live. Every tab flows through one service — sheetCache — that holds it in memory, serves it instantly, and refreshes from the Sheet in the background. So a Sheet edit is never live on the spot: it lands when the cache rolls or an admin busts it. This is the whole map.

1 · Your Sheetthe tab you edit — the source of truth

2 · sheetCachememory + snapshot, serve-staleserver/services/sheetCache.ts

3 · API endpointwhat the app calls

4 · Client surfacewhere the player sees it

Endpoints are live — click one to open the real production JSON in a new tab. Parameterized routes use working example ids (pack 4, song 1, lang=en, terms 3/4).

Sheet tab	Server cache · TTL	Endpoint(s)	Client surface	Bust it now?
SongsDBthe song catalog + stems	songs · 15 min	/api/launch /api/song /api/freeplay/songs /api/songs/random · sign-in only	Daily game, Free Play, QuickPlay pool + client cache 5 min	yesinvalidate("songs")
Packspack defs, price, FreeSongs, section	packs · 15 min	/api/packs /api/packs/:id · eg 4 /api/packs/:id/songs/:n · eg 4/1	Play More, pack page, pack songs client: list 10 min / pack page 24 HRS	yesinvalidate("packs") — but a DB pack row overrides the Sheet
TOTD-Pickerthe daily-song schedule	totd · 5 min	drives selection → DB → /api/launch (resolveSong)	Today's daily song + enrichedSong 15 min + client 5 min	yesinvalidate("totd") — but the hourly DB sync must run for it to take effect
Genresfather genres + genre picks	genres · 1 hr	/api/songs/random/metadata	QuickPlay genre picker	yesinvalidate("genres")
Translationsall app copy, 15 languages	translations · 5 min	/api/translations?lang · eg en	Every piece of text in the app merged over the baked bundle, once per session	no bustwaits out TTL / hourly sync job only
Termsimage URLs, config values, lists	terms · 1 hr	/api/terms/:t/:s · eg 3/4 + OG image inject	Stat tiles, survival cover, socials, share image client snapshot persists across sessions	no bustTTL only — no invalidation path
IntroGamesonboarding intro config	introgames · 30 min	/api/intro	First-visit intro game	no bustTTL only + no empty-guard (can cache blank)
Country&Languagegeo → language / intro targeting	countryLang · 6 hrs	/api/intro (targeting)	Which intro/lang a country gets	no bustTTL only + no empty-guard
TOTD-Archiveformula table — the schedule ledger	job read/write	jobs only (not a live endpoint)	Records the planned daily songs written by the selection + sync jobs	n/aformula table — never append rows

The service · sheetCache

How a Sheet value reaches the app

Boot floor. At startup every tab is seeded from a committed snapshot (server/data/*-fallback.json) so a cold server never shows a blank screen — even if Google is down.
Serve instantly. A request always gets the in-memory value right away. It never waits on Google.
Refresh behind the scenes. If the value is older than its TTL, the app serves the old one and quietly re-fetches the Sheet for next time (8 retries for network blips).
Never cache blank. An empty Sheet read is treated as a failure for songs, genres & translations so a glitch can't wipe them — not yet guarded for IntroGames or Country&Language.

Why "I edited the Sheet and nothing changed"

The staleness stack

1Server TTL — up to 5 min–6 hr depending on the tab (column 2).
2Client cache on top — the pack page holds for 24 hours on an open app.
3DB override — for a DB-backed pack (today: World Cup), the Sheet is ignored entirely.
4No bust button for Translations, Terms, IntroGames, Country&Language — TTL only.
5Per-copy — if prod runs more than one server copy, busting one leaves the others stale.

The one-line model: Sheet → sheetCache → API → client. The Sheet is where you author; sheetCache is the middle-man that makes it survivable and fast; the edit goes live when the cache rolls (or an admin busts it) — plus whatever the client is still holding. The only exception to "Sheet is truth" is packs, where a stray song_packs DB row can shadow the Sheet.