The technical detail behind "once a day, plus a weekly renewal rate, plus on demand." Read this if you want to know why the split exists, what runs where, and what fails when.
Reads and writes run on different paths. The dashboard never waits on live APIs to render.
When the dashboard loads, the client makes two reads in parallel: one for all historical weeks (/api/supabase-snapshots) and one for the current week's row (GET /api/windsor). Both read from the weekly_snapshots table in Supabase. No live API calls happen in the request path.
Result: pages render in 50–200 ms even though the underlying pipeline takes 60–90 seconds to gather fresh numbers end-to-end.
Two separate pipelines write to Supabase; neither blocks page render. They run on four triggers:
Every 6 hours. Runs the full pipeline for the current fiscal week (Klaviyo, Recharge, LoyaltyLion, Shopify sessions+units direct, Shopify revenue + Google Ads + Meta + Instagram via Windsor) and upserts the row into weekly_snapshots. Path: /api/cron/weekly-snapshot.
Every 6 hours (offset 30 min so it doesn't race the weekly one). Pulls today + yesterday from Shopify direct and upserts into daily_snapshots (powers the Daily Revenue chart on /revenue). Path: /api/cron/daily-shopify.
Sunday 00:01 ET. Computes the rolling 12-month renewal rate for the just-closed fiscal week and PATCHes the renewal_rate column. Runs on the Hetzner VPS, ~25 min.
The Refresh Now button fires POST /api/refresh/weekly and POST /api/refresh/daily in parallel — same pipelines as the crons, wrapped for authenticated client calls. Progress counter stays up until the slower leg (weekly, ~60-90s) finishes.
When the dashboard loads, the client checks the updated_at timestamp on the current-week row. If the row is more than 6 hours old, it fires POST /api/refresh/weekly in the background and reloads the client state when the pipeline finishes. Since the cron now runs every 6 hours too, the auto-refresh rarely needs to kick in — it's a safety net for long stretches where nobody visits the page.
Short jobs live on Vercel; long jobs live on Hetzner. The split is structural, not cosmetic.
Pro plan caps every serverless function at 300 seconds (configured in the route). The weekly-snapshot pipeline run takes ~60–90 seconds; the daily-Shopify run takes ~3–5 seconds. Both are well under the cap. Vercel's cron scheduler is reliable, integrated with GitHub auto-deploy, and posts invocation logs in the Vercel dashboard.
Schedules: 0 */6 * * * for the weekly snapshot, 30 */6 * * * for daily Shopify. Defined in vercel.json.
The rolling 12-month renewal rate scans ~144,000 cancelled and ~49,000 active Recharge subscriptions and does a customer-level churn check across all of them. Real runtime is ~25 minutes — comfortably over Vercel's 800 s cap. It runs on our Hetzner VPS with no timeout limit.
Schedule: TZ=America/New_York · 1 0 * * 0 (Sunday 00:01 ET). Script: scripts/hetzner-weekly-renewal-rate.sh. Logs: /home/deploy/logs/renewal-rate-YYYY-MM-DD.log, 90-day retention.
Fixing one metric across historical weeks should never re-run every connector.
Seven surgical endpoints, each updating only its own source's columns:
POST /api/backfill/<source> where source ∈ sessions · shopify-orders · klaviyo · recharge · ads · loyalty · instagram
Body: { fromWeek, toWeek, fiscalYear }. Query: ?secret=<CRON_SECRET>. Derived fields (CVR, AOV, CPA, combined_spend) get recomputed from the existing row whenever their inputs change, so the math stays consistent.
Full re-runs of every connector across a range of weeks still exist at POST /api/backfill/full-pipeline, but the endpoint refuses to execute unless the request body includes "confirm": "RUN_FULL_PIPELINE".
The guard exists to prevent muscle-memory "run backfill" reflexes from kicking off a 30-minute all-columns overwrite when one column needed fixing. The older /api/windsor/backfill URL returns 410 Gone with pointers to the replacement endpoints.
What can go wrong and how the system surfaces it.
Happened for ~2 days in April 2026: the Basic Auth proxy was rejecting Vercel's cron GET requests before the route's CRON_SECRET check could run, because the cron path wasn't in the exclusion list. Fix was a one-line addition to the matcher in src/proxy.ts. Now covered by explicit wildcard exclusions: /api/cron/*, /api/refresh/*, /api/backfill/*, /api/diag/*.
If Windsor or a direct API slows down enough that the full pipeline exceeds 300 s (the current cron's maxDuration), the function dies mid-run and the current-week row won't update. The next cron run (6 hours later) picks up the gap. The manual Refresh Now button runs with the same 300 s cap.
Individual source failures never zero-out the row. When a source's fetch returns null (throttle, network, parse error), the write layer OMITS that source's columns from the Supabase upsert — existing historical values are preserved. This is explicit for Klaviyo (via the fetched.klaviyo flag on WeeklySnapshotResult) because Klaviyo's Campaign/Flow Values rate limits are tight. Learned the hard way in April 2026 when a botched backfill wrote 0 to 25 of 28 weeks; the preserve-on-null rule is now pinned in CLAUDE.md.