The Rewards Ledger — turning a Google Sheets pivot into a daily editorial
I was running everything from a Google Sheets pivot table
For years my entire travel-rewards strategy lived in a single spreadsheet — a pivot table cross-referencing 19 loyalty programs, a list of certs with expiry columns, a tab for each open signup bonus with conditional formatting on minimum-spend progress, and a column on every row for whose: Carl / Rachel / Joint.
It worked. But it was ugly, it was slow on mobile (which is when I actually needed it — at a check-in counter, at a hotel desk), and the spreadsheet was a dumb pile of data. It didn't tell me anything I didn't already know. It couldn't say what changed this week. It couldn't say this Walmart+ credit expires in 4 days. It couldn't write me an editorial.
So I rebuilt it as The Rewards Ledger — a daily editorial, not a spreadsheet.
The editorial reframe
The first decision was tonal. A rewards tracker is usually built like a finance app: tables, totals, sparklines, the occasional pie chart. Useful, sterile, forgettable.
The Ledger is built like a daily newspaper — gold-on-charcoal masthead, Newsreader serif display type, mono-spaced numerals via tabular-nums, masked-edge video banners under each section header, weekly Anthropic-written editorial blurbs cached in Turso.
The same data, but presented as something you'd want to read with morning coffee instead of a spreadsheet you check at tax time.

Seven sections
The Ledger has seven tabs, each its own mini-newsroom:
💳 Programs

19 loyalty currencies across hotels (Marriott Bonvoy, Hilton, IHG, World of Hyatt, Best Western), airlines (United, Delta, American, Alaska, JetBlue, Air Canada, Cathay), and credit-card points (Amex MR, Chase UR, Citi TY, Capital One miles). Each row has owner badge, status tier, sparkline of recent value history, and a USD-value estimate via current redemption rates so the leaderboard is comparable across currencies.
Tile-filter by type (hotels / airlines / credit-card points / all). Inline edit form on tap.
🎟️ Certificates

Free-night certs (Marriott 35k/50k, IHG anniversary, Hilton free-night), free-flight vouchers, status-bonus rewards. Each cert tracks expiry date and redemption value. The dashboard surfaces expiring in next 60 days in red on the front page so nothing slips.
🎫 Credits

The Amex Platinum / Gold / CSR statement-credit menagerie. Dell, Saks, Walmart+, dining, hotel, airline incidentals, equinox. Each row has status (open · pending · done) and a due date. The dashboard's red-flag block surfaces open credits with < 14 days left so I actually use them before they expire.
This is where the project pays for itself — I was losing ~$300/year of Platinum credits to forgetting.
💵 Cash credits

The cash sitting in non-dollar wallets — airline travel-bank dollars from cancelled flights, gift cards, hotel resort-credit balances. Easy to forget, easy to lose.
✈️ Signup bonuses

A four-lane kanban — Ready to apply · Coming soon · Just applied · Waiting (spending in progress). Each card in the Waiting lane shows progress bar against the minimum-spend target, days remaining in the window, and projected points-on-completion.
Tracks 5/24 windows and Amex pop-up jail cooldown periods so I never apply for a card I'd be denied for.
📖 Apply

The pipeline-feeder. A curated catalog of cards I'm researching for the next signup slot, organized by category (hotel cobrand, airline cobrand, premium MR earner, flat-rate cashback, etc.).
Every card has an issuer "Apply" link plus chips linking to community/review sources — NerdWallet, The Points Guy, Doctor of Credit, MilesTalk, r/churning, 北美華人哩程交流討論, TripPlus 部落格 — so research is one click from the catalog row.
📜 Activity — The Chronicle

The Ledger's signature page. Every day a snapshot row gets written per owner capturing portfolio value, points value, cert value, cash value, and a JSON blob of per-program balances.
The Chronicle groups day-over-day diffs by month — October: +12,400 MR, −1 Marriott 50k cert (redeemed Tokyo), +$200 Saks credit used — and renders it like a magazine timeline.
One app, three perspectives
Every page accepts ?owner=Carl | Rachel | Joint | All. Snapshots are written per-owner with composite PK (snapshot_date, owner), so each person's portfolio has its own independent history and sparklines.
This was the refactor that mattered most. The first version had owner as a column on a single shared snapshot table, and every chronicle query became a layer of WHERE owner = ? plus complicated join logic. Splitting it into per-owner rows on a composite PK made the queries trivial and the data model honest.
The pieces I'm proud of
A dashboard like this is only useful if you actually open it. Four things help:
- Title-bar balance ticker — the top balances I care about (current MR balance, certs expiring this quarter, days until the next credit expires) play as a looping video at the top of every page. Always there, no click required.
- 3D dashboard tilt on scroll — as you scroll the homepage, the cards tilt subtly into perspective via Framer Motion. Pure dopamine — but it makes me want to scroll, and scrolling means seeing the credits before they expire.
- Weekly Anthropic editorial commentary — every Sunday, Claude writes a short editorial blurb on what moved that week. Cached in Turso so it costs ~$0 per week. "The portfolio added 14,200 MR this week, mostly from the Dell credit on the Business Platinum. Two Marriott 50k certs are now within the 60-day expiry window — Tokyo or Kyoto?"
- Veo cinematic section banners — each section header has a masked-edge looping video underneath (gold-dust falling, vintage world-map drifting, ornate vault rotating, pocket-watch ticking). Generated via Vertex AI Veo, served as MP4 from
/public/loops/. The same visual language I used for the portfolio site itself.
Stack
- Next.js 16 App Router, Server Components, Turbopack
- Tailwind v4 editorial design system — Newsreader serif display, Geist sans, gold
#d4a574accent, mono-spaced numbers viatabular-nums - Turso (libsql) — SQLite-on-the-edge for primary storage + daily snapshots
- Anthropic API — weekly editorial commentary on portfolio movements
- Vertex AI Veo — cinematic loop videos for section banners
- Playwright + ffmpeg — README screenshot + GIF pipeline that auto-blurs
.font-monospans on/programsso account numbers don't leak into checked-in images - Basic-auth middleware fronting the whole site — one password, gated at the edge, done
Deployed at rewards.carlfung.dev on the *.carlfung.dev Vercel subdomain pattern.
Data model
Seven core tables in Turso:
programs— loyalty currencies (id, owner, name, type, status_tier, points_balance, account_number, …)certificates— free-night and free-flight vouchers (linked toprogram_id)credits— statement credits (Amex / CSR / etc.)cash_credits— travel-bank balances, gift cardssignup_bonuses— kanban-pipelined card applicationssnapshots— daily portfolio rollups, per-owner since the multi-owner refactor (composite PK(snapshot_date, owner))commentary— cached weekly Anthropic editorial blurbs
What I learned
- The visual matters more than the data model. I'd built rewards trackers twice before — sensible tables, the same underlying data. They never stuck. The editorial reframe + the tilt + the ticker is what makes me open it.
- Per-owner needs to be on the schema, not the query. Faking it with a column required complicated joins everywhere. Splitting into a composite PK was honest, fast, and obvious.
- Anthropic blurbs cost ~$0/week and feel like $100/month. One LLM call every Sunday, cached. Feels like a paid newsletter wrote me an editorial.
- Veo banners are absurdly good for branded ambient motion. A single 4s generation, masked edges, looped — and every section header now has a custom cinematic backdrop. Vibes carry the design.
What's next
- Push notifications when a credit is in the 14-day expiry window (Tiger can already see the data via an internal API endpoint — wiring the push is the rest)
- Auto-snapshot from card-issuer APIs where one exists (most don't, but Amex and a few others do)
- Tax export of redemption history (CSV with redemption date, cert burned, points value, cash value)
- Anniversary tracking — annual fees, retention call windows, downgrade timing
Source
github.com/carlfung1003/rewards-tracker — MIT-licensed, fork freely. The screenshots in this post are mine but every member ID is blurred by the Playwright pipeline before commit.