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:

1. 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.
2. 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.
3. 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?"
4. 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 #d4a574 accent, mono-spaced numbers via tabular-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-mono spans on /programs so 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 to program_id)
• credits — statement credits (Amex / CSR / etc.)
• cash_credits — travel-bank balances, gift cards
• signup_bonuses — kanban-pipelined card applications
• snapshots — 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.