Epic: Data-freshness platform — scheduled background refresh, cadence/prioritization & historical snapshots

[peetzweg]

Right now all entity data refreshes lazily on lookup (TTL-gated) or via manually-run scripts/. Nothing refreshes on a schedule. That's fine for seeding the DB, but it doesn't scale: leaderboard rank, follower counts and the commit tail drift for anyone nobody visits; org contribution totals never refresh on the request path at all; and we keep only the latest value for everything, so month-over-month trends are impossible for orgs and lossy for historical profile scalars.

This epic is the umbrella for getting the refresh / backfill / history platform right — how we schedule and prioritize background refresh, what history we snapshot, and where the worker runs — so we stop hand-spinning scripts. Scripts stay great for one-off seeding; recurring work should be automatic.

Current state (so we decide from reality, not memory)

• Users — src/lib/cache.ts: TAIL_TTL = 60s; all six contribution types stored per completed month in monthly_commits; the in-progress month is excluded (monthlyWindows uses a strict <). Refresh happens only on lookup.
• Orgs — src/lib/org-cache.ts: ORG_TTL = 7 days; MAX_ORG_MEMBERS = 25 (bigger orgs are recorded and filled by scripts/backfill-orgs.ts). Org numbers are lifetime sums on entities, rolled up from org_members — no monthly / time-series storage on main. On the request path, only profile metadata refreshes when stale; contribution totals never do — they wait for a script.
• In-progress month is dropped for both users and orgs (yearlyWindows/monthlyWindows stop at the first of the current month). So every headline number is "last completed month", not "today". (Worth ratifying — see Show the current in-progress month as a dashed 'trend' segment #35.)
• Scripts (all manual, bun scripts/…): refresh.ts (user profile metadata), backfill-contributions.ts (user full rebuild, rate-limited), backfill-orgs.ts (org lifetime totals, rate-limited), suspend.ts (moderation). No cron / scheduled mechanism exists anywhere. Deploy is TanStack Start → Nitro node-server, Dockerized, on Coolify.

Goals

• Proactive, politely rate-limited refresh on a cadence, prioritized so what people actually see stays fresh.
• Snapshot enough history to power trend indicators (e.g. "+50% vs last month") for both users and orgs (they're two different data shapes).
• A durable place for the worker to run — not a laptop running bun scripts/….

Key decisions (need a human call)

1. Cadence — daily vs weekly vs monthly, per tier. Contributions are monthly-resolution, so sub-weekly refresh buys little for the curve; profile/follower/rank drift faster. Starting proposal: hot tier (recent lookups + top-250) weekly; long-tail monthly; profile-metadata (followers/rank) possibly more frequent. → Refresh cadence & prioritization policy (background updates) #107.
2. Prioritization tiers — recent lookups (last N days) → top-250 leaderboard → long-tail. Define ordering and how the hourly GitHub budget is split. → Refresh cadence & prioritization policy (background updates) #107.
3. In-progress-month semantics — ratify "last completed month" everywhere, or adopt Show the current in-progress month as a dashed 'trend' segment #35's dashed in-progress segment; decide whether orgs must match users.
4. Historical snapshots — what to persist over time so trends work and totals stay stable across refreshes: org monthly series (C), user rank / follower / profile scalars (Keep historical data of non historical retrievable data #83), org membership history (Keep historical org memberships — departed members silently shrink company totals #97).
5. Where the worker runs — Nitro in-process scheduled task vs a dedicated Coolify worker. → Worker runtime: Nitro scheduled task vs dedicated Coolify worker (decide + spike) #108 (research summary below).

Deployment research (feeds decision #5 / #108)

• Myth-buster: Nitro does fire scheduledTasks on the node-server preset — it uses an in-process croner engine, no external trigger needed. (The "doesn't fire on Node" belief comes from the Vercel/Cloudflare presets, which don't apply here.)
• But: a long, rate-limited job in the web process risks memory contention, and — the sharp edge — gets killed with no resume on every web redeploy (Coolify replaces the container; croner does no catch-up).
• Coolify has built-in Scheduled Tasks (container-level cron). A dedicated worker service (own container, own resource limits, own deploy) triggered by Coolify survives web deploys and isolates the load.
• Leaning: dedicated Coolify worker for the heavy refresh; keep the job code as a shared script/package in this repo so either runner can call it. Nitro tasks stay viable for light/idempotent jobs. Whatever we pick, jobs must be idempotent and checkpointed (our scripts already are). Full write-up in Worker runtime: Nitro scheduled task vs dedicated Coolify worker (decide + spike) #108.

Sub-issues

New (this epic):

• A — Refresh cadence & prioritization policy — Refresh cadence & prioritization policy (background updates) #107
• B — Worker runtime: decide & spike (Nitro scheduled task vs dedicated Coolify worker) — Worker runtime: Nitro scheduled task vs dedicated Coolify worker (decide + spike) #108
• C — Org-level monthly time-series storage (productionize org monthly data) — Org-level monthly time-series storage (productionize org monthly data) #109
• D — Org month-over-month contribution-change indicator — Org month-over-month contribution-change indicator #110

Existing (fold in / organize):

• Monthly background refresh of cached entities #6 — Monthly background refresh of cached entities (users) — generalized by A
• Reconcile retroactive GitHub contribution changes (background refresh) #58 — Reconcile retroactive GitHub contribution changes (background refresh)
• Build cold-fetch history off the request path (background fill) #53 — Build cold-fetch history off the request path (background fill)
• Month-over-month contribution-change indicator (CoinGecko-style) #41 — Month-over-month change indicator (users) — ships on existing user monthly data
• 24h / day-over-day contribution-change indicator (CoinGecko-style) #43 — 24h / day-over-day change indicator
• Show the current in-progress month as a dashed 'trend' segment #35 — Show the in-progress month as a dashed 'trend' segment
• Fetch daily-resolution contribution data (design decision required) #40 — Fetch daily-resolution contribution data (design decision)
• Backfill DB with daily contribution data #42 — Backfill DB with daily contribution data
• Keep historical data of non historical retrievable data #83 — Keep historical time-series of non-retrievable profile data (followers/company/location)
• Keep historical org memberships — departed members silently shrink company totals #97 — Keep historical org memberships (departed members)

Related infra: #75 (healthcheck / Coolify).

Non-goals

The per-type / daily fetching mechanics (#39 closed, #40/#42) — this epic is about scheduling, prioritization, history, and where it runs, not how a single fetch is shaped.

[peetzweg]

Progress / findings from building the org tooling this cycle:

• Fix: allow looking up legacy GitHub usernames (trailing/double hyphens) #115 merged — legacy hyphen-ending GitHub usernames (e.g. Link-, gil--, p-, real accounts from before ~2013) now look up; they were 400ing site-wide. This also unblocked org backfill, which had been aborting whenever an org had such a member.
• Org backfill + discovery scripts (seed and fill the org leaderboard) #104 (org backfill + discovery scripts) is the current manual seed/fill mechanism that the scheduled worker (Refresh cadence & prioritization policy (background updates) #107/Worker runtime: Nitro scheduled task vs dedicated Coolify worker (decide + spike) #108) will eventually automate:
  • scripts/discover-orgs.ts — finds orgs to track and records them (entities, builtAt null).
  • scripts/backfill-orgs.ts — fills them with each public member's lifetime org-scoped totals, smallest-first, resumable/idempotent.
• Seeding/prioritization finding (feeds Refresh cadence & prioritization policy (background updates) #107): GitHub has no endpoint that ranks orgs by public-member count — the Search API returns only login/id, caps at 1,000 results, and can't sort or filter by members. discover-orgs.ts works around it by searching type:org sorted by followers (best prominence proxy; repos-sort returns education/spam) then enriching each candidate with a profile fetch for the real count. Any "top orgs" tier will need this enrich-then-sort pattern, not a direct query.
• Org contribution totals still refresh only via these manual scripts (never on the request path) — productionizing that onto a cadence is the core of Refresh cadence & prioritization policy (background updates) #107/Worker runtime: Nitro scheduled task vs dedicated Coolify worker (decide + spike) #108; org monthly resolution (for trends) is Org-level monthly time-series storage (productionize org monthly data) #109.