[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_341":342,"_343":344},"routes/_app.x.$handle.$name",{"_100":99,"_31":44,"_109":90,"_26":110,"_13":14,"_111":-5,"_11":112,"_113":114,"_115":116,"_117":118,"_119":120,"_20":-5,"_121":-5,"_122":-5,"_123":24,"_124":125,"_126":59,"_127":-5,"_128":-5,"_129":130},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_42":43,"_31":44,"_37":45,"_46":-5,"_47":24,"_48":49,"_50":49,"_51":52,"_53":54,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":24},"version",32,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":-7,"_23":24,"_25":24,"_26":27,"_28":27},"file",{"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":14,"_40":41,"_23":24},"previous","deleted",false,"deletedHere","path","lib/admin.tsx","latestPath","fileId","fadba818-6448-11f1-9695-1607ee4eb77e","name","admin.tsx","fileType","script","content","/** @jsxImportSource npm:hono/jsx */\n// Status page: the live state of this mirror — revenue metrics computed with\n// SQL over the local tables (no Stripe API calls), sync job health, and\n// buttons to drive syncs on demand.\nimport type { EventState, MrrRow, SyncRow } from \"./metrics.ts\";\nimport { Card, Layout, money, Section, StatusBadge } from \"./views.tsx\";\n\ntype AdminProps = {\n mrr: MrrRow[];\n counts: { table: string; count: number }[];\n sync: SyncRow[];\n events: EventState;\n pending: boolean;\n hasKey: boolean;\n};\n\nexport function Admin({ mrr, counts, sync, events, pending, hasKey }: AdminProps) {\n const totals = new Map();\n for (const row of mrr) totals.set(row.currency, (totals.get(row.currency) ?? 0) + row.mrrCents);\n return (\n \n

Mirror status

\n Everything below is computed from the local SQLite mirror — no Stripe API calls on this page.\n

\n {!hasKey && (\n

\n STRIPE_API_KEY is not set, so nothing can sync yet. See the README for one-click setup.\n

\n )}\n

\n {[...totals.entries()].map(([currency, cents]) => (\n <>\n \n \n \n ))}\n {totals.size === 0 && }\n

\n {mrr.length > 0 && (\n

\n \n \n \n \n \n \n \n \n \n {mrr.map((row) => (\n \n \n \n \n \n ))}\n \n

Product	Subscribers	MRR
{row.product}	{row.subscribers}	{money(row.mrrCents, row.currency)}

\n )}\n

\n Each table backfills once (status goes pending → done), then the 5-minute cron keeps everything fresh from\n the Events API. The events cursor shows how far that incremental sync has read.\n

\n \n \n {sync.map((row) => (\n \n \n \n \n \n ))}\n \n \n \n \n \n \n

{row.resource}	\n \n	{row.lastError ?? row.lastRunAt ?? \"never run\"}
events cursor	\n \n	\n {events.lastError ?? (events.lastEventCreated\n ? `caught up to ${new Date(events.lastEventCreated * 1000).toISOString()}`\n : \"not started\")}\n

\n {counts.map((c) => (\n <>\n \n {c.table.replace(\"stripe_\", \"\")} {c.count}\n \n {\" · \"}\n \n ))}\n browse all\n

\n \n \n

\n {pending\n ? \"Backfill in progress: each click runs a ~45s slice; the cron also advances it every 5 minutes.\"\n : \"Sync now pulls recent changes from the Events API. Re-sync re-walks all of Stripe history with upserts.\"}\n

\n \n );\n}\n","createdAt",["D",1781040003397],"branchVersion","branchId","41ab6bde-e273-4dba-99ae-7e8f9d01de9f","id","aca55a8a-72ee-4b8b-84aa-0a0762b4d677","stripe-mirror",["D",1781035360700],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Mirror your Stripe account into SQLite to power ARR/MRR charts","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/522abc55-169d-4249-6a64-9e9382e58300/square512?exp=1789577999&sig=d0934f21d8a609fe7eb893b34d66bea4f70824521137bf82ae95d9ce5944d9a5","a0bf3b31-15a5-4d5c-880e-4b1e22c9bc18","pinnedFileId","e70f460e-6444-11f1-b2c1-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",2,"orgId","019aa807-52c2-709c-928b-2029bae9a558","tursoName","aca55a8a72ee4b8b84aa0a0762b4d677","tursoHostname","aca55a8a72ee4b8b84aa0a0762b4d677-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789501878689],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_100":99,"_108":104},"org",{"_100":99,"_101":102,"_103":104,"_18":-5,"_105":106,"_107":59},"branch",{"_13":89,"_31":90,"_42":41,"_91":43,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":44,"_98":99},"isOwned",78,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","templates","handle","displayName","Templates","avatar","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/129cab32-3bd7-4c3e-f39b-f12900c76100/square512?exp=1789577999&sig=aa7aec25480c67f41f9650e8445afc21cf1a6c5fd3969752b90fbd3f6a604c46","tier","business","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_42":43,"_31":44,"_37":336,"_46":-5,"_47":24,"_48":49,"_50":49,"_51":52,"_53":54,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":337,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":338,"_84":339,"_86":340,"_88":24},"tree",[210,222,223,224,225,226,227,228],"files",[210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228],"counts",{"_200":146,"_201":161,"_202":146,"_203":204,"_205":206},"contents",[131,132,133,134,135,136,137,138],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_42":194,"_31":195,"_29":196,"_142":-5,"_40":41,"_39":161,"_144":59,"_37":197,"_13":146,"_33":198,"_35":-5,"_23":24,"_148":-5,"_26":199},{"_42":186,"_31":187,"_29":188,"_142":-5,"_40":41,"_39":189,"_144":24,"_37":190,"_13":191,"_33":20,"_35":192,"_23":24,"_148":-5,"_26":193},{"_42":179,"_31":180,"_29":181,"_142":-5,"_40":41,"_39":182,"_144":24,"_37":183,"_13":146,"_33":34,"_35":184,"_23":24,"_148":-5,"_26":185},{"_42":173,"_31":174,"_29":175,"_142":-5,"_40":41,"_39":63,"_144":59,"_37":176,"_13":146,"_33":34,"_35":177,"_23":24,"_148":-5,"_26":178},{"_42":165,"_31":166,"_29":167,"_142":-5,"_40":41,"_39":168,"_144":59,"_37":169,"_13":146,"_33":170,"_35":171,"_23":24,"_148":-5,"_26":172},{"_42":157,"_31":158,"_29":57,"_142":-5,"_40":41,"_39":159,"_144":24,"_37":160,"_13":161,"_33":162,"_35":163,"_23":24,"_148":44,"_26":164},{"_42":150,"_31":151,"_29":152,"_142":-5,"_40":41,"_39":153,"_144":24,"_37":154,"_13":146,"_33":34,"_35":155,"_23":24,"_148":-5,"_26":156},{"_42":139,"_31":140,"_29":141,"_142":-5,"_40":41,"_39":143,"_144":24,"_37":145,"_13":146,"_33":34,"_35":147,"_23":24,"_148":-5,"_26":149},"25811622-4894-41af-ae6b-fa3abe725eda","status.ts","847a127c-643e-11f1-9777-1607ee4eb77e","parentId",13,"latest",["D",1781035509818],0,"// Prints row counts and sync progress.\nimport { sqlite } from \"./lib/db.ts\";\nimport { ensureSchema } from \"./lib/schema.ts\";\n\nawait ensureSchema();\n\nconst tables = [\n \"stripe_customers\",\n \"stripe_products\",\n \"stripe_prices\",\n \"stripe_subscriptions\",\n \"stripe_subscription_items\",\n \"stripe_invoices\",\n \"stripe_invoice_lines\",\n \"stripe_credit_notes\",\n];\nfor (const table of tables) {\n const result = await sqlite.execute(`SELECT COUNT(*) AS n FROM ${table}`);\n console.log(`${table}: ${result.rows[0].n}`);\n}\n\nconsole.log(\"\\nbackfill state:\");\nconst state = await sqlite.execute(\n `SELECT resource, status, cursor, last_run_at, last_error FROM sync_state ORDER BY resource`,\n);\nfor (const row of state.rows) console.log(JSON.stringify(row));\n\nconst events = await sqlite.execute(`SELECT * FROM event_state WHERE key = 'events'`);\nconsole.log(\"\\nevents cursor:\", JSON.stringify(events.rows[0] ?? null));\n","label",[140],"df5e6272-e8ad-47fa-8917-ec0b4adf788a","resync.ts","82759d20-643e-11f1-a2c4-1607ee4eb77e",12,["D",1781035506449],"// Queue a full re-sync: every enabled table gets re-walked from the beginning\n// of Stripe history with upserts. Nothing is deleted first, so the mirror\n// stays queryable throughout. Use after changing config.ts, recovering from\n// a long outage (> EVENT_RETENTION_DAYS), or whenever you want to be sure.\nimport { ensureSchema } from \"./lib/schema.ts\";\nimport { resetForResync } from \"./lib/state.ts\";\n\nawait ensureSchema();\nawait resetForResync();\nconsole.log(\"All enabled tables queued for re-sync. Run backfill.ts to drive it now, or let the cron work through it.\");\n",[151],"f9140aae-9b80-4096-bab2-3b23e2721a70","index.tsx",29,["D",1781039940470],1,"http","/** @jsxImportSource npm:hono/jsx */\n// ARR dashboard + sync controls, server-rendered with Hono JSX.\n// Optional: set DASHBOARD_PASSWORD to put the dashboard behind basic auth\n// (username \"admin\") — recommended, since revenue numbers are sensitive.\nimport { Hono } from \"npm:hono\";\nimport { basicAuth } from \"npm:hono/basic-auth\";\nimport { hasPendingBackfill, runBackfill } from \"./lib/backfill.ts\";\nimport { runEventSync } from \"./lib/events.ts\";\nimport { eventState, mrrByProduct, syncStatus, tableCounts } from \"./lib/metrics.ts\";\nimport { ensureSchema } from \"./lib/schema.ts\";\nimport { resetForResync } from \"./lib/state.ts\";\nimport { Page } from \"./lib/views.tsx\";\n\nconst app = new Hono();\n\nconst password = Deno.env.get(\"DASHBOARD_PASSWORD\");\nif (password) app.use(\"*\", basicAuth({ username: \"admin\", password }));\n\napp.get(\"/\", async (c) => {\n await ensureSchema();\n const [mrr, counts, sync, events, pending] = await Promise.all([\n mrrByProduct(),\n tableCounts(),\n syncStatus(),\n eventState(),\n hasPendingBackfill(),\n ]);\n const hasKey = Boolean(Deno.env.get(\"STRIPE_API_KEY\"));\n return c.html();\n});\n\n// One bounded slice of whatever the cron would do next: a ~45s backfill\n// chunk if one is pending, otherwise an Events API pass. (HTTP requests are\n// capped at 1 minute, hence the smaller budget than the cron's.)\napp.post(\"/sync\", async (c) => {\n await ensureSchema();\n if (await hasPendingBackfill()) await runBackfill(45_000);\n else await runEventSync();\n return c.redirect(\"./\");\n});\n\napp.post(\"/resync\", async (c) => {\n await ensureSchema();\n await resetForResync();\n return c.redirect(\"./\");\n});\n\nexport default app.fetch;\n",[158],"65affa8b-8bd5-438b-9619-3d66d53e2321","cron.ts","7d8f4162-643e-11f1-93e9-1607ee4eb77e",10,["D",1781035498220],"interval","// Runs every 5 minutes. While a backfill is pending it advances the backfill\n// (time-boxed); otherwise it does a cheap incremental sync from the Stripe\n// Events API. See README.md.\nimport { RUN_BUDGET_MS } from \"./config.ts\";\nimport { hasPendingBackfill, runBackfill } from \"./lib/backfill.ts\";\nimport { runEventSync } from \"./lib/events.ts\";\nimport { ensureSchema } from \"./lib/schema.ts\";\n\nexport default async function (interval: Interval) {\n if (!Deno.env.get(\"STRIPE_API_KEY\")) {\n console.log(\"STRIPE_API_KEY is not set yet — see README.md for setup.\");\n return { ok: false, missing: \"STRIPE_API_KEY\" };\n }\n await ensureSchema();\n const summary = (await hasPendingBackfill())\n ? await runBackfill(RUN_BUDGET_MS)\n : await runEventSync();\n console.log(JSON.stringify(summary));\n return summary;\n}\n",[166],"2f79552b-7912-49d9-95ea-21471b48334c","config.ts","3569068e-643e-11f1-af85-1607ee4eb77e",["D",1781035377171],"// Configuration for the Stripe → SQLite mirror.\n\n// Which Stripe resources to mirror. Set one to false and the cron stops\n// touching it (existing rows stay put). Set a new one to true and the next\n// cron run automatically backfills it from the beginning of time.\nexport const TABLES = {\n customers: true,\n products: true,\n prices: true,\n subscriptions: true, // also fills stripe_subscription_items\n invoices: true, // also fills stripe_invoice_lines\n credit_notes: true,\n} as const;\n\nexport type Resource = keyof typeof TABLES;\n\nexport function enabledResources(): Resource[] {\n return (Object.keys(TABLES) as Resource[]).filter((resource) => TABLES[resource]);\n}\n\n// --- Rate limiting ----------------------------------------------------------\n// Stripe's default API limits are 100 read requests/sec in live mode and\n// 25/sec in test mode. All requests in this val flow through one token bucket\n// pinned to RATE_LIMIT_FRACTION of that, so the backfill can fan out\n// aggressively while staying safely under the limit and leaving headroom for\n// your production traffic.\nexport const RATE_LIMIT_FRACTION = 0.8;\nexport const LIVE_READS_PER_SECOND = 100;\nexport const TEST_READS_PER_SECOND = 25;\n\n// Upper bound on simultaneous in-flight requests.\nexport const MAX_CONCURRENCY = 25;\n\n// Stripe's max list page size.\nexport const PAGE_SIZE = 100;\n\n// The events sync re-reads this far behind its cursor, so a crashed or\n// overlapping run can never skip an event (already-seen ids are deduped).\nexport const EVENT_OVERLAP_SECONDS = 10 * 60;\n\n// Processed event ids are kept this long for dedupe, then pruned.\nexport const EVENT_RETENTION_DAYS = 30;\n\n// Wall-clock budget for backfill work inside one run.\nexport const RUN_BUDGET_MS = 3.5 * 60 * 1000;\n",[174],"edb00123-6562-4d27-8ef2-0acecbfd6d87","backfill.ts","80508aa0-643e-11f1-9112-1607ee4eb77e",11,["D",1781035502829],"// Run this manually to drive the initial backfill (or a queued re-sync)\n// without waiting for the cron's 5-minute slices. Run it again if it reports\n// work remaining. Don't run two copies at once — the rate limiter is\n// per-process, so parallel copies would overshoot the 80% budget (the data\n// stays correct either way; every write is an upsert).\nimport { RUN_BUDGET_MS } from \"./config.ts\";\nimport { hasPendingBackfill, runBackfill } from \"./lib/backfill.ts\";\nimport { ensureSchema } from \"./lib/schema.ts\";\n\nawait ensureSchema();\nif (!(await hasPendingBackfill())) {\n console.log(\"Nothing pending — the backfill is complete. Run resync.ts first if you want a full re-sync.\");\n} else {\n const summary = await runBackfill(RUN_BUDGET_MS);\n console.log(JSON.stringify(summary, null, 2));\n if (!summary.done) {\n console.log(\"Time budget used up with work remaining — run this again, or let the cron finish it.\");\n }\n}\n",[180],"242c5147-aff9-4da5-b409-79abc1eda3d3","README.md","9954b224-643e-11f1-a5d7-1607ee4eb77e",28,["D",1781039683800],9,"# stripe-mirror\n\nYour whole Stripe account, mirrored into a SQLite database you can query.\n\n```sql\nSELECT p.name, SUM(l.amount) / 100.0 AS revenue\nFROM stripe_invoice_lines l\nJOIN stripe_invoices i ON i.id = l.invoice_id\nJOIN stripe_products p ON p.id = l.product_id\nWHERE i.status = 'paid'\nGROUP BY p.name ORDER BY revenue DESC;\n```\n\n## The problem this solves\n\nSubscription billing data is **mutable history**. Subscriptions get upgraded,\ndowngraded, prorated, and canceled; invoices get voided and credited. To answer\neven basic questions — MRR by product, revenue by month, churn — you need _all\ninvoices for all time_, not just recent ones.\n\nThe Stripe API fights you on this. Lists are cursor-paginated 100 at a time,\nline items are a nested sub-list with their own pagination, everything is\nrate-limited, and you can't join or aggregate across objects at all. Recomputing\nanything from the API takes thousands of sequential requests, every time.\n\nThe fix (same model as\n[Supabase's Stripe Sync Engine](https://supabase.com/blog/stripe-sync-engine-integration))\nis to maintain a local mirror and query it with SQL. This val is that,\nself-contained on Val Town: no Postgres to run, no webhook endpoint to secure —\njust a Stripe API key.\n\n## How it works\n\nThree mechanisms, all writing with idempotent upserts keyed on Stripe ids:\n\n1. **Backfill** — walks every enabled resource's list endpoint through all of\n history. All resources run in parallel and child fetches fan out further; a\n shared token bucket holds total throughput at **80% of Stripe's rate limit**\n (100 reads/sec live, 25/sec test), so years of data land in minutes without\n starving your production traffic. Progress is checkpointed page by page, so\n it resumes cleanly across runs.\n2. **Incremental cron (every 5 minutes)** — `cron.ts` polls the\n [Events API](https://docs.stripe.com/api/events) for what changed, refetches\n those objects from Stripe (the event payload is never trusted as the source\n of truth), and upserts just those rows. A typical run touches zero or a\n handful of objects.\n3. **Re-sync** — run `resync.ts` to queue a fresh full walk at any time. It\n upserts over existing rows, so the mirror stays queryable while it runs.\n\nThe event cursor starts when the schema is first created, so anything that\nchanges _during_ the backfill is replayed by the cron afterward — no gap between\nthe two.\n\n## Setup\n\n1. **Get a Stripe API key** —\n [use this link to create a key with exact read-only permissions to Customers, Products, Prices, Subscriptions, Invoices, Credit Notes, Events](https://dashboard.stripe.com/apikeys/create?name=stripe-mirror&permissions%5B%5D=rak_customer_read&permissions%5B%5D=rak_product_read&permissions%5B%5D=rak_price_read&permissions%5B%5D=rak_plan_read&permissions%5B%5D=rak_subscription_read&permissions%5B%5D=rak_invoice_read&permissions%5B%5D=rak_credit_note_read&permissions%5B%5D=rak_event_read)\n\n2. **Paste it as an env var in this val** -\n [use this link to add `STRIPE_API_KEY`](https://www.val.town/x/templates/stripe-mirror/environment-variables?key=STRIPE_API_KEY)\n\n3. **Open the dashboard** (`dashboard.tsx`'s HTTP endpoint) and click **Run\n backfill slice** — or do nothing; the cron advances the backfill\n automatically in 5-minute slices.\n\n## Dashboard\n\n`dashboard.tsx` is a server-rendered (Hono JSX, no client JS) page with the\nheadline MRR/ARR numbers, MRR by product, the status of every sync job, and\nbuttons for \"Sync now\" and \"Queue full re-sync\".\n\nThe endpoint is public by default and shows revenue numbers, so set a password\nto put it behind basic auth (username `admin`):\n\n👉 Add DASHBOARD_PASSWORD here: https://www.val.town/x/templates/stripe-mirror/environment-variables?key=DASHBOARD_PASSWORD\n\nPrefer scripts to buttons? `backfill.ts`, `resync.ts`, and `status.ts` do the\nsame jobs from the editor.\n\n## Tables\n\n| Table | Source |\n| ---------------------------------------------------- | ------------------------------ |\n| `stripe_customers` | `/v1/customers` |\n| `stripe_products` | `/v1/products` |\n| `stripe_prices` | `/v1/prices` |\n| `stripe_subscriptions` + `stripe_subscription_items` | `/v1/subscriptions?status=all` |\n| `stripe_invoices` + `stripe_invoice_lines` | `/v1/invoices` |\n| `stripe_credit_notes` | `/v1/credit_notes` |\n\nEach table promotes a few columns for joins and indexing, and keeps the\n**complete Stripe object** as JSON in `raw`. Anything not promoted is one\n`json_extract` away:\n\n```sql\nSELECT json_extract(raw, '$.metadata.plan_tier') AS tier, COUNT(*)\nFROM stripe_subscriptions WHERE status = 'active' GROUP BY tier;\n```\n\n## Configuring what syncs\n\nEverything tunable lives in `config.ts`: which tables to mirror, the rate-limit\nfraction, concurrency, and retention. Turn a resource off and the sync leaves it\nalone; turn a new one on and the next cron run backfills it from the beginning,\nautomatically.\n\n## Example queries\n\nMonthly recognized revenue (invoice line periods, prorations included):\n\n```sql\nSELECT strftime('%Y-%m', period_start, 'unixepoch') AS month,\n SUM(amount) / 100.0 AS revenue\nFROM stripe_invoice_lines l\nJOIN stripe_invoices i ON i.id = l.invoice_id\nWHERE i.status = 'paid'\nGROUP BY month ORDER BY month;\n```\n\nActive MRR by product:\n\n```sql\nSELECT pr.name, SUM(p.unit_amount * si.quantity) / 100.0 AS mrr\nFROM stripe_subscription_items si\nJOIN stripe_subscriptions s ON s.id = si.subscription_id\nJOIN stripe_prices p ON p.id = si.price_id\nJOIN stripe_products pr ON pr.id = p.product_id\nWHERE s.status = 'active' AND p.recurring_interval = 'month'\nGROUP BY pr.name ORDER BY mrr DESC;\n```\n\n## Files\n\n- `cron.ts` — 5-minute interval: advances a pending backfill, otherwise events\n sync\n- `dashboard.tsx` — ARR dashboard + sync controls (HTTP)\n- `backfill.ts` / `resync.ts` / `status.ts` — script equivalents, run manually\n- `config.ts` — tables, rate limits, tuning knobs\n- `lib/stripe.ts` — rate-limited Stripe client (token bucket + concurrency\n gate + 429/5xx retry)\n- `lib/backfill.ts`, `lib/events.ts` — the two sync engines\n- `lib/schema.ts`, `lib/rows.ts`, `lib/state.ts`, `lib/db.ts` — tables,\n object→row mapping, cursors, upsert helper\n- `lib/metrics.ts`, `lib/views.tsx` — dashboard queries and markup\n\n## Limits and notes\n\n- Stripe only retains **30 days** of events. If the cron is ever paused longer\n than that, run `resync.ts` to catch up.\n- Deletions arrive via events (the refetch 404s and the row is dropped). Objects\n deleted while the cron was paused past retention are only cleaned up by a\n re-sync.\n- Charges, payment intents, refunds, and disputes aren't mirrored by default. To\n add a resource: a toggle in `config.ts`, a list entry in `lib/backfill.ts`, a\n row mapper in `lib/rows.ts`, a table in `lib/schema.ts`, and (for live\n updates) a case in `lib/events.ts`.\n- The rate limiter is per-process: don't run `backfill.ts` twice simultaneously,\n or lower `RATE_LIMIT_FRACTION` if other systems share the same Stripe key's\n budget.\n- Sandbox quirk: objects attached to [test clocks](https://docs.stripe.com/billing/testing/test-clocks)\n don't appear in Stripe's list endpoints, so the backfill can't see them —\n they arrive via the events sync instead. Production data is unaffected.",[187],"b527262a-1d83-40fe-8ce4-b71503f5711f","lib","2e7297be-643e-11f1-812a-1607ee4eb77e",["D",1781035365512],"directory",[195],"pulls","branches","forks","envVars",{"_207":161,"_209":146},"connections",{"_207":146,"_208":146},"count","unfilledCount","emptyCount",{"_42":194,"_31":195,"_29":196,"_142":-5,"_40":41,"_39":161,"_144":59,"_37":333,"_13":146,"_33":198,"_35":-5,"_23":24,"_148":-5,"_230":334,"_26":335},{"_42":329,"_31":32,"_29":30,"_142":196,"_40":41,"_39":14,"_144":59,"_37":330,"_13":146,"_33":34,"_35":36,"_23":24,"_148":-5,"_230":331,"_26":332},{"_42":322,"_31":180,"_29":323,"_142":196,"_40":41,"_39":324,"_144":59,"_37":325,"_13":146,"_33":34,"_35":326,"_23":24,"_148":-5,"_230":327,"_26":328},{"_42":314,"_31":315,"_29":316,"_142":196,"_40":41,"_39":317,"_144":59,"_37":318,"_13":146,"_33":34,"_35":319,"_23":24,"_148":-5,"_230":320,"_26":321},{"_42":307,"_31":308,"_29":309,"_142":196,"_40":41,"_39":191,"_144":59,"_37":310,"_13":146,"_33":34,"_35":311,"_23":24,"_148":-5,"_230":312,"_26":313},{"_42":299,"_31":300,"_29":301,"_142":196,"_40":41,"_39":302,"_144":24,"_37":303,"_13":146,"_33":34,"_35":304,"_23":24,"_148":-5,"_230":305,"_26":306},{"_42":291,"_31":292,"_29":293,"_142":196,"_40":41,"_39":294,"_144":59,"_37":295,"_13":146,"_33":34,"_35":296,"_23":24,"_148":-5,"_230":297,"_26":298},{"_42":283,"_31":284,"_29":285,"_142":196,"_40":41,"_39":286,"_144":59,"_37":287,"_13":146,"_33":34,"_35":288,"_23":24,"_148":-5,"_230":289,"_26":290},{"_42":275,"_31":276,"_29":277,"_142":196,"_40":41,"_39":278,"_144":59,"_37":279,"_13":146,"_33":34,"_35":280,"_23":24,"_148":-5,"_230":281,"_26":282},{"_42":267,"_31":268,"_29":269,"_142":196,"_40":41,"_39":270,"_144":59,"_37":271,"_13":146,"_33":34,"_35":272,"_23":24,"_148":-5,"_230":273,"_26":274},{"_42":259,"_31":260,"_29":261,"_142":196,"_40":41,"_39":262,"_144":59,"_37":263,"_13":146,"_33":34,"_35":264,"_23":24,"_148":-5,"_230":265,"_26":266},{"_42":251,"_31":252,"_29":253,"_142":196,"_40":41,"_39":254,"_144":24,"_37":255,"_13":161,"_33":34,"_35":256,"_23":24,"_148":-5,"_230":257,"_26":258},{"_42":186,"_31":187,"_29":188,"_142":-5,"_40":41,"_39":189,"_144":24,"_37":248,"_13":191,"_33":20,"_35":192,"_23":24,"_148":-5,"_230":249,"_26":250},{"_42":179,"_31":180,"_29":181,"_142":-5,"_40":41,"_39":182,"_144":24,"_37":245,"_13":146,"_33":34,"_35":184,"_23":24,"_148":-5,"_230":246,"_26":247},{"_42":173,"_31":174,"_29":175,"_142":-5,"_40":41,"_39":63,"_144":59,"_37":242,"_13":146,"_33":34,"_35":177,"_23":24,"_148":-5,"_230":243,"_26":244},{"_42":165,"_31":166,"_29":167,"_142":-5,"_40":41,"_39":168,"_144":59,"_37":239,"_13":146,"_33":170,"_35":171,"_23":24,"_148":-5,"_230":240,"_26":241},{"_42":157,"_31":158,"_29":57,"_142":-5,"_40":41,"_39":159,"_144":24,"_37":236,"_13":161,"_33":162,"_35":163,"_23":24,"_148":44,"_230":237,"_26":238},{"_42":150,"_31":151,"_29":152,"_142":-5,"_40":41,"_39":153,"_144":24,"_37":233,"_13":146,"_33":34,"_35":155,"_23":24,"_148":-5,"_230":234,"_26":235},{"_42":139,"_31":140,"_29":141,"_142":-5,"_40":41,"_39":143,"_144":24,"_37":229,"_13":146,"_33":34,"_35":147,"_23":24,"_148":-5,"_230":231,"_26":232},["D",1781035509818],"children",[],[140],["D",1781035506449],[],[151],["D",1781039940470],[],[158],["D",1781035498220],[],[166],["D",1781035377171],[],[174],["D",1781035502829],[],[180],["D",1781039683800],[],[187],"02414297-f1bc-409f-8b14-b153b47173a6","views.tsx","e2a61548-6444-11f1-a056-1607ee4eb77e",30,["D",1781039955753],"/** @jsxImportSource npm:hono/jsx */\n// Shared layout and UI bits for all pages. No client JS anywhere — buttons\n// are plain HTML forms.\n\nexport function Layout({ title, children }: { title: string; children?: unknown }) {\n return (\n \n \n {title}\n \n \n \n \n

\n \n {children}\n

\n \n \n );\n}\n\nexport function Card({ label, value, note }: { label: string; value: string; note?: string }) {\n return (\n

{label}

{value}

\n {note &&

{note}

}\n

\n );\n}\n\nexport function Section({ title, children }: { title: string; children?: unknown }) {\n return (\n

{title}

\n {children}\n

\n );\n}\n\nexport function StatusBadge({ status }: { status: string }) {\n const styles: Record = {\n done: \"bg-emerald-500/10 text-emerald-300\",\n pending: \"bg-amber-500/10 text-amber-300\",\n error: \"bg-red-500/10 text-red-300\",\n };\n return (\n \n {status}\n \n );\n}\n\nexport const money = (cents: number, currency: string) =>\n new Intl.NumberFormat(\"en-US\", {\n style: \"currency\",\n currency: currency.toUpperCase(),\n maximumFractionDigits: 0,\n }).format(cents / 100);\n",[],[195,252],"97f397f8-e8fe-49f5-b2fd-57bfb5d02499","stripe.ts","40aca0e6-643e-11f1-af79-1607ee4eb77e",4,["D",1781035396088],"// Rate-limited Stripe REST client. Every request flows through a token\n// bucket pinned to RATE_LIMIT_FRACTION of Stripe's documented read limit,\n// plus a concurrency cap — so callers can fire requests in parallel freely\n// and throughput lands at ~80% of what Stripe allows.\nimport {\n LIVE_READS_PER_SECOND,\n MAX_CONCURRENCY,\n PAGE_SIZE,\n RATE_LIMIT_FRACTION,\n TEST_READS_PER_SECOND,\n} from \"../config.ts\";\n\nconst API_BASE = \"https://api.stripe.com\";\nconst MAX_RETRIES = 5;\n\nexport type StripeList = { data?: T[]; has_more?: boolean };\n\nexport function apiKey(): string {\n const key = Deno.env.get(\"STRIPE_API_KEY\");\n if (!key) throw new Error(\"STRIPE_API_KEY is not set — see README.md for setup\");\n return key;\n}\n\nfunction readsPerSecond(): number {\n const limit = apiKey().includes(\"_test_\") ? TEST_READS_PER_SECOND : LIVE_READS_PER_SECOND;\n return Math.max(1, Math.floor(limit * RATE_LIMIT_FRACTION));\n}\n\n// --- token bucket: refills once per second ---\nlet tokens = 0;\nlet windowStart = 0;\n\nasync function acquireToken() {\n while (true) {\n const now = Date.now();\n if (now - windowStart >= 1000) {\n windowStart = now;\n tokens = readsPerSecond();\n }\n if (tokens > 0) {\n tokens--;\n return;\n }\n await sleep(windowStart + 1000 - now + 5);\n }\n}\n\n// --- concurrency gate ---\nlet inFlight = 0;\nconst waiting: Array<() => void> = [];\n\nasync function acquireSlot() {\n if (inFlight >= MAX_CONCURRENCY) await new Promise((resolve) => waiting.push(resolve));\n inFlight++;\n}\n\nfunction releaseSlot() {\n inFlight--;\n waiting.shift()?.();\n}\n\nexport class NotFound extends Error {}\n\nexport async function stripeGet(path: string, params: Record = {}): Promise {\n const url = new URL(API_BASE + path);\n for (const [key, value] of Object.entries(params)) {\n for (const v of Array.isArray(value) ? value : [value]) url.searchParams.append(key, v);\n }\n await acquireSlot();\n try {\n for (let attempt = 0; ; attempt++) {\n await acquireToken();\n const res = await fetch(url, { headers: { Authorization: `Bearer ${apiKey()}` } });\n if (res.ok) return await res.json();\n const body = await res.text();\n if (res.status === 404) throw new NotFound(`Stripe GET ${path} 404`);\n const retryable = res.status === 429 || res.status >= 500;\n if (!retryable || attempt >= MAX_RETRIES) {\n throw new Error(`Stripe GET ${path} ${res.status}: ${body.slice(0, 300)}`);\n }\n const retryAfter = Number(res.headers.get(\"retry-after\"));\n await sleep((retryAfter > 0 ? retryAfter : 2 ** attempt) * 1000);\n }\n } finally {\n releaseSlot();\n }\n}\n\n// Returns null instead of throwing when the object no longer exists.\nexport async function stripeGetMaybe(path: string, params: Record = {}): Promise {\n try {\n return await stripeGet(path, params);\n } catch (error) {\n if (error instanceof NotFound) return null;\n throw error;\n }\n}\n\n// Fetch every page of a list endpoint (used for child lists like the rare\n// invoice with >100 lines).\nexport async function listAll(path: string, params: Record = {}): Promise {\n const all: any[] = [];\n let startingAfter: string | undefined;\n while (true) {\n const page = await stripeGet(path, {\n ...params,\n limit: String(PAGE_SIZE),\n ...(startingAfter ? { starting_after: startingAfter } : {}),\n }) as StripeList;\n const data = page.data ?? [];\n all.push(...data);\n if (!page.has_more || data.length === 0) return all;\n startingAfter = String(data.at(-1).id);\n }\n}\n\nfunction sleep(ms: number) {\n return new Promise((resolve) => setTimeout(resolve, Math.max(ms, 0)));\n}\n",[],[195,260],"3999f387-4a61-4a70-a40a-51c60c39cafc","state.ts","602e97c6-643e-11f1-9a79-1607ee4eb77e",7,["D",1781035448949],"// Sync bookkeeping: backfill cursors per resource and the events cursor.\nimport { enabledResources, EVENT_RETENTION_DAYS, Resource } from \"../config.ts\";\nimport { batchWrite, sqlite } from \"./db.ts\";\n\nexport type PendingResource = { resource: Resource; cursor: string | null };\n\nexport async function pendingResources(): Promise {\n const enabled = enabledResources();\n const result = await sqlite.execute(`SELECT resource, cursor FROM sync_state WHERE status = 'pending'`);\n return result.rows\n .map((row) => ({\n resource: String(row.resource) as Resource,\n cursor: row.cursor == null ? null : String(row.cursor),\n }))\n .filter((row) => enabled.includes(row.resource));\n}\n\nexport async function saveCursor(resource: string, cursor: string | null) {\n await sqlite.execute({\n sql: `UPDATE sync_state SET cursor = ?, last_run_at = ?, last_error = NULL WHERE resource = ?`,\n args: [cursor, new Date().toISOString(), resource],\n });\n}\n\nexport async function markDone(resource: string) {\n await sqlite.execute({\n sql: `UPDATE sync_state SET status = 'done', cursor = NULL, last_run_at = ?, last_error = NULL WHERE resource = ?`,\n args: [new Date().toISOString(), resource],\n });\n}\n\nexport async function markError(resource: string, message: string) {\n await sqlite.execute({\n sql: `UPDATE sync_state SET last_run_at = ?, last_error = ? WHERE resource = ?`,\n args: [new Date().toISOString(), message, resource],\n });\n}\n\n// Queue every enabled resource for a fresh full walk (keeps existing rows;\n// the walk upserts over them).\nexport async function resetForResync() {\n await sqlite.batch(enabledResources().map((resource) => ({\n sql: `UPDATE sync_state SET status = 'pending', cursor = NULL, last_error = NULL WHERE resource = ?`,\n args: [resource],\n })));\n}\n\n// --- events cursor ---\n\nexport async function eventCursor(): Promise {\n const result = await sqlite.execute(`SELECT last_event_created FROM event_state WHERE key = 'events'`);\n return Number(result.rows[0]?.last_event_created ?? 0);\n}\n\nexport async function saveEventCursor(created: number) {\n await sqlite.execute({\n sql: `UPDATE event_state SET last_event_created = ?, updated_at = ?, last_error = NULL WHERE key = 'events'`,\n args: [created, new Date().toISOString()],\n });\n}\n\nexport async function markEventError(message: string) {\n await sqlite.execute({\n sql: `UPDATE event_state SET updated_at = ?, last_error = ? WHERE key = 'events'`,\n args: [new Date().toISOString(), message],\n });\n}\n\nexport async function knownEventIds(ids: string[]): Promise> {\n const known = new Set();\n for (let i = 0; i < ids.length; i += 100) {\n const chunk = ids.slice(i, i + 100);\n const placeholders = chunk.map(() => \"?\").join(\", \");\n const result = await sqlite.execute({\n sql: `SELECT id FROM stripe_events WHERE id IN (${placeholders})`,\n args: chunk,\n });\n for (const row of result.rows) known.add(String(row.id));\n }\n return known;\n}\n\nexport async function recordEvents(events: { id: string; type: string; created: number }[]) {\n const processedAt = new Date().toISOString();\n await batchWrite(events.map((event) => ({\n sql: `INSERT INTO stripe_events (id, type, created, processed_at) VALUES (?, ?, ?, ?)\n ON CONFLICT(id) DO NOTHING`,\n args: [event.id, event.type, Number(event.created ?? 0), processedAt],\n })));\n const cutoff = Math.floor(Date.now() / 1000) - EVENT_RETENTION_DAYS * 24 * 60 * 60;\n await sqlite.execute({ sql: `DELETE FROM stripe_events WHERE created < ?`, args: [cutoff] });\n}\n",[],[195,268],"65ddb154-8b84-498b-bb0b-1f660d40c464","schema.ts","4bb211ce-643e-11f1-afcf-1607ee4eb77e",5,["D",1781035414579],"import { enabledResources } from \"../config.ts\";\nimport { sqlite } from \"./db.ts\";\n\n// Every mirror table promotes a few columns for indexing/joins and keeps the\n// complete Stripe object as JSON in `raw` — anything not promoted is still\n// queryable with json_extract(raw, '$.field').\nconst TABLES = [\n `CREATE TABLE IF NOT EXISTS stripe_customers (\n id TEXT PRIMARY KEY,\n name TEXT,\n email TEXT,\n created INTEGER,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_products (\n id TEXT PRIMARY KEY,\n name TEXT,\n active INTEGER,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_prices (\n id TEXT PRIMARY KEY,\n product_id TEXT,\n currency TEXT,\n unit_amount INTEGER,\n recurring_interval TEXT,\n recurring_interval_count INTEGER,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_subscriptions (\n id TEXT PRIMARY KEY,\n customer_id TEXT,\n status TEXT,\n created INTEGER,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_subscription_items (\n id TEXT PRIMARY KEY,\n subscription_id TEXT NOT NULL,\n price_id TEXT,\n quantity INTEGER NOT NULL DEFAULT 1,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_invoices (\n id TEXT PRIMARY KEY,\n customer_id TEXT,\n subscription_id TEXT,\n status TEXT,\n total INTEGER,\n currency TEXT,\n created INTEGER,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_invoice_lines (\n id TEXT PRIMARY KEY,\n invoice_id TEXT NOT NULL,\n subscription_id TEXT,\n price_id TEXT,\n product_id TEXT,\n amount INTEGER,\n period_start INTEGER,\n period_end INTEGER,\n proration INTEGER NOT NULL DEFAULT 0,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_credit_notes (\n id TEXT PRIMARY KEY,\n invoice_id TEXT,\n customer_id TEXT,\n amount INTEGER,\n status TEXT,\n created INTEGER,\n raw TEXT NOT NULL,\n synced_at TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS sync_state (\n resource TEXT PRIMARY KEY,\n status TEXT NOT NULL DEFAULT 'pending',\n cursor TEXT,\n last_run_at TEXT,\n last_error TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS event_state (\n key TEXT PRIMARY KEY,\n last_event_created INTEGER NOT NULL DEFAULT 0,\n updated_at TEXT,\n last_error TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS stripe_events (\n id TEXT PRIMARY KEY,\n type TEXT NOT NULL,\n created INTEGER NOT NULL,\n processed_at TEXT NOT NULL\n )`,\n];\n\nconst INDEXES = [\n `CREATE INDEX IF NOT EXISTS idx_invoices_customer ON stripe_invoices(customer_id)`,\n `CREATE INDEX IF NOT EXISTS idx_invoices_subscription ON stripe_invoices(subscription_id)`,\n `CREATE INDEX IF NOT EXISTS idx_invoices_created ON stripe_invoices(created)`,\n `CREATE INDEX IF NOT EXISTS idx_invoice_lines_invoice ON stripe_invoice_lines(invoice_id)`,\n `CREATE INDEX IF NOT EXISTS idx_invoice_lines_price ON stripe_invoice_lines(price_id)`,\n `CREATE INDEX IF NOT EXISTS idx_invoice_lines_subscription ON stripe_invoice_lines(subscription_id)`,\n `CREATE INDEX IF NOT EXISTS idx_subscriptions_customer ON stripe_subscriptions(customer_id)`,\n `CREATE INDEX IF NOT EXISTS idx_subscription_items_subscription ON stripe_subscription_items(subscription_id)`,\n `CREATE INDEX IF NOT EXISTS idx_prices_product ON stripe_prices(product_id)`,\n `CREATE INDEX IF NOT EXISTS idx_credit_notes_invoice ON stripe_credit_notes(invoice_id)`,\n `CREATE INDEX IF NOT EXISTS idx_events_created ON stripe_events(created)`,\n];\n\nexport async function ensureSchema() {\n await sqlite.batch([...TABLES, ...INDEXES].map((sql) => ({ sql, args: [] })));\n\n // Seed state rows. A resource newly enabled in config.ts starts 'pending',\n // so the next cron run backfills it automatically.\n await sqlite.batch(enabledResources().map((resource) => ({\n sql: `INSERT INTO sync_state (resource, status) VALUES (?, 'pending') ON CONFLICT(resource) DO NOTHING`,\n args: [resource],\n })));\n\n // The event cursor starts at \"now\": history before this moment is the\n // backfill's job, and changes made during the backfill replay through events.\n await sqlite.execute({\n sql: `INSERT INTO event_state (key, last_event_created) VALUES ('events', ?) ON CONFLICT(key) DO NOTHING`,\n args: [Math.floor(Date.now() / 1000)],\n });\n}\n",[],[195,276],"c0eea8b4-8382-48f4-97be-f32bd71f540d","rows.ts","58f57420-643e-11f1-a315-1607ee4eb77e",6,["D",1781035436828],"// Maps raw Stripe objects to mirror-table upsert statements.\nimport { Statement, upsert } from \"./db.ts\";\n\n// Stripe fields can be an id string or an expanded object; normalize to id.\nexport function objectId(value: unknown): string | null {\n if (!value) return null;\n if (typeof value === \"string\") return value || null;\n return String((value as { id?: unknown }).id ?? \"\") || null;\n}\n\nexport function customerRow(customer: any, syncedAt: string): Statement {\n return upsert(\"stripe_customers\", {\n id: customer.id,\n name: customer.name,\n email: customer.email,\n created: customer.created,\n raw: JSON.stringify(customer),\n synced_at: syncedAt,\n });\n}\n\nexport function productRow(product: any, syncedAt: string): Statement {\n return upsert(\"stripe_products\", {\n id: product.id,\n name: product.name,\n active: product.active ? 1 : 0,\n raw: JSON.stringify(product),\n synced_at: syncedAt,\n });\n}\n\nexport function priceRow(price: any, syncedAt: string): Statement {\n return upsert(\"stripe_prices\", {\n id: price.id,\n product_id: objectId(price.product),\n currency: price.currency,\n unit_amount: price.unit_amount,\n recurring_interval: price.recurring?.interval,\n recurring_interval_count: price.recurring?.interval_count,\n raw: JSON.stringify(price),\n synced_at: syncedAt,\n });\n}\n\nexport function creditNoteRow(note: any, syncedAt: string): Statement {\n return upsert(\"stripe_credit_notes\", {\n id: note.id,\n invoice_id: objectId(note.invoice),\n customer_id: objectId(note.customer),\n amount: note.amount,\n status: note.status,\n created: note.created,\n raw: JSON.stringify(note),\n synced_at: syncedAt,\n });\n}\n\n// An invoice and its line items are written together: stale lines are deleted\n// first so removed or regenerated lines never linger.\nexport function invoiceRows(invoice: any, lines: any[], syncedAt: string): Statement[] {\n const invoiceId = String(invoice.id);\n return [\n { sql: `DELETE FROM stripe_invoice_lines WHERE invoice_id = ?`, args: [invoiceId] },\n upsert(\"stripe_invoices\", {\n id: invoiceId,\n customer_id: objectId(invoice.customer),\n subscription_id: invoiceSubscriptionId(invoice),\n status: invoice.status,\n total: invoice.total,\n currency: invoice.currency,\n created: invoice.created,\n raw: JSON.stringify(invoice),\n synced_at: syncedAt,\n }),\n ...lines.map((line) =>\n upsert(\"stripe_invoice_lines\", {\n id: line.id,\n invoice_id: invoiceId,\n subscription_id: lineSubscriptionId(invoice, line),\n price_id: linePriceId(line),\n product_id: lineProductId(line),\n amount: line.amount,\n period_start: line.period?.start,\n period_end: line.period?.end,\n proration: lineProration(line) ? 1 : 0,\n raw: JSON.stringify(line),\n synced_at: syncedAt,\n })\n ),\n ];\n}\n\nexport function subscriptionRows(sub: any, items: any[], syncedAt: string): Statement[] {\n const subId = String(sub.id);\n return [\n { sql: `DELETE FROM stripe_subscription_items WHERE subscription_id = ?`, args: [subId] },\n upsert(\"stripe_subscriptions\", {\n id: subId,\n customer_id: objectId(sub.customer),\n status: sub.status,\n created: sub.created,\n raw: JSON.stringify(sub),\n synced_at: syncedAt,\n }),\n ...items.map((item) =>\n upsert(\"stripe_subscription_items\", {\n id: item.id,\n subscription_id: subId,\n price_id: objectId(item.price),\n quantity: item.quantity ?? 1,\n raw: JSON.stringify(item),\n synced_at: syncedAt,\n })\n ),\n ];\n}\n\nexport function deleteInvoice(invoiceId: string): Statement[] {\n return [\n { sql: `DELETE FROM stripe_invoice_lines WHERE invoice_id = ?`, args: [invoiceId] },\n { sql: `DELETE FROM stripe_invoices WHERE id = ?`, args: [invoiceId] },\n ];\n}\n\nexport function deleteSubscription(subId: string): Statement[] {\n return [\n { sql: `DELETE FROM stripe_subscription_items WHERE subscription_id = ?`, args: [subId] },\n { sql: `DELETE FROM stripe_subscriptions WHERE id = ?`, args: [subId] },\n ];\n}\n\nexport function deleteRow(table: string, id: string): Statement {\n return { sql: `DELETE FROM ${table} WHERE id = ?`, args: [id] };\n}\n\n// Newer Stripe API versions put line price/product under pricing.price_details\n// and the subscription under parent.*; older versions used price/plan and\n// top-level subscription fields. Check both shapes.\nfunction linePriceId(line: any): string | null {\n return line.pricing?.price_details?.price ?? objectId(line.price) ?? objectId(line.plan);\n}\n\nfunction lineProductId(line: any): string | null {\n return line.pricing?.price_details?.product ?? objectId(line.price?.product) ?? objectId(line.plan?.product);\n}\n\nfunction lineSubscriptionId(invoice: any, line: any): string | null {\n return objectId(line.parent?.subscription_item_details?.subscription)\n ?? objectId(line.subscription)\n ?? invoiceSubscriptionId(invoice);\n}\n\nfunction invoiceSubscriptionId(invoice: any): string | null {\n return objectId(invoice.subscription) ?? objectId(invoice.parent?.subscription_details?.subscription);\n}\n\nfunction lineProration(line: any): boolean {\n return Boolean(line.parent?.subscription_item_details?.proration ?? line.proration ?? false);\n}\n",[],[195,284],"a79deded-3ae5-4f37-8087-046d2f80b508","metrics.ts","ce6de98e-6444-11f1-a4dc-1607ee4eb77e",16,["D",1781038210894],"// SQL behind the dashboard: MRR/ARR rollups and sync status reads.\nimport { sqlite } from \"./db.ts\";\n\nexport type MrrRow = { product: string; currency: string; mrrCents: number; subscribers: number };\n\n// Normalizes month/year recurring prices to monthly amounts (yearly ÷ 12,\n// interval_count respected). Day/week intervals are rare and ignored.\nexport async function mrrByProduct(): Promise {\n const result = await sqlite.execute(`\n SELECT COALESCE(pr.name, p.product_id, 'unknown') AS product,\n COALESCE(p.currency, 'usd') AS currency,\n CAST(SUM(p.unit_amount * si.quantity /\n (CASE p.recurring_interval WHEN 'year' THEN 12.0 ELSE 1.0 END\n * COALESCE(p.recurring_interval_count, 1))) AS INTEGER) AS mrr_cents,\n COUNT(DISTINCT s.id) AS subscribers\n FROM stripe_subscription_items si\n JOIN stripe_subscriptions s ON s.id = si.subscription_id\n JOIN stripe_prices p ON p.id = si.price_id\n LEFT JOIN stripe_products pr ON pr.id = p.product_id\n WHERE s.status = 'active'\n AND p.recurring_interval IN ('month', 'year')\n AND p.unit_amount IS NOT NULL\n GROUP BY product, p.currency\n ORDER BY mrr_cents DESC\n `);\n return result.rows.map((row) => ({\n product: String(row.product),\n currency: String(row.currency),\n mrrCents: Number(row.mrr_cents ?? 0),\n subscribers: Number(row.subscribers ?? 0),\n }));\n}\n\nexport type SyncRow = {\n resource: string;\n status: string;\n lastRunAt: string | null;\n lastError: string | null;\n};\n\nexport async function syncStatus(): Promise {\n const result = await sqlite.execute(\n `SELECT resource, status, last_run_at, last_error FROM sync_state ORDER BY resource`,\n );\n return result.rows.map((row) => ({\n resource: String(row.resource),\n status: String(row.status),\n lastRunAt: row.last_run_at == null ? null : String(row.last_run_at),\n lastError: row.last_error == null ? null : String(row.last_error),\n }));\n}\n\nexport type EventState = { lastEventCreated: number; updatedAt: string | null; lastError: string | null };\n\nexport async function eventState(): Promise {\n const result = await sqlite.execute(`SELECT * FROM event_state WHERE key = 'events'`);\n const row = result.rows[0] ?? {};\n return {\n lastEventCreated: Number(row.last_event_created ?? 0),\n updatedAt: row.updated_at == null ? null : String(row.updated_at),\n lastError: row.last_error == null ? null : String(row.last_error),\n };\n}\n\nconst COUNT_TABLES = [\n \"stripe_customers\",\n \"stripe_products\",\n \"stripe_prices\",\n \"stripe_subscriptions\",\n \"stripe_subscription_items\",\n \"stripe_invoices\",\n \"stripe_invoice_lines\",\n \"stripe_credit_notes\",\n];\n\nexport async function tableCounts(): Promise<{ table: string; count: number }[]> {\n return await Promise.all(COUNT_TABLES.map(async (table) => {\n const result = await sqlite.execute(`SELECT COUNT(*) AS n FROM ${table}`);\n return { table, count: Number(result.rows[0]?.n ?? 0) };\n }));\n}\n",[],[195,292],"2f8b2d4a-82fe-4f26-bcaf-6ed89c83c331","landing.tsx","ea8a299e-6448-11f1-a213-1607ee4eb77e",31,["D",1781039976043],"/** @jsxImportSource npm:hono/jsx */\n// Public landing page: what stripe-mirror is, how it works, how to get one.\nimport type { EventState } from \"./metrics.ts\";\nimport { Layout, Section } from \"./views.tsx\";\n\nconst KEY_LINK =\n \"https://dashboard.stripe.com/apikeys/create?name=stripe-mirror&permissions%5B%5D=rak_customer_read&permissions%5B%5D=rak_product_read&permissions%5B%5D=rak_price_read&permissions%5B%5D=rak_plan_read&permissions%5B%5D=rak_subscription_read&permissions%5B%5D=rak_invoice_read&permissions%5B%5D=rak_credit_note_read&permissions%5B%5D=rak_event_read\";\n\nconst EXAMPLE_SQL = `SELECT strftime('%Y-%m', period_start, 'unixepoch') AS month,\n SUM(amount) / 100.0 AS revenue\nFROM stripe_invoice_lines l\nJOIN stripe_invoices i ON i.id = l.invoice_id\nWHERE i.status = 'paid'\nGROUP BY month ORDER BY month;`;\n\ntype LandingProps = {\n counts: { table: string; count: number }[];\n events: EventState;\n hasKey: boolean;\n};\n\nexport function Landing({ counts, events, hasKey }: LandingProps) {\n const total = counts.reduce((sum, c) => sum + c.count, 0);\n return (\n \n

Your whole Stripe account, in a SQLite database you can query.

\n stripe-mirror keeps a local, always-fresh copy of your Stripe data — customers, products, prices,\n subscriptions, invoices, credit notes — so revenue questions become SQL queries instead of API archaeology.\n

\n\n

\n Subscription billing data is mutable history: subscriptions get upgraded, prorated, and canceled; invoices\n get voided and credited. Answering basic questions — MRR by product, revenue by month, churn — takes every\n invoice you've ever issued, not just recent ones.\n

\n The Stripe API fights you on this: cursor pagination 100 rows at a time, nested line items, rate limits,\n and no joins. The fix (same model as Supabase's Stripe Sync Engine) is a local mirror you query with SQL.\n This one is self-contained on Val Town — no Postgres to run, no webhooks to secure, just one API key.\n

\n\n

\n Backfill — walks all of Stripe history in parallel, rate\n limited to 80% of your API budget, checkpointed page by page.\n
\n Events cron — every 5 minutes, pulls what changed from\n the Events API and refetches just those objects.\n
\n Re-sync — one click re-walks everything with upserts,\n without taking the mirror offline.\n

\n\n

\n {hasKey\n ? `${total.toLocaleString()} rows mirrored — ${\n counts.filter((c) => c.count > 0).map((c) => `${c.count} ${c.table.replace(\"stripe_\", \"\")}`).join(\", \")\n }. Events caught up to ${\n events.lastEventCreated ? new Date(events.lastEventCreated * 1000).toISOString() : \"n/a\"\n }.`\n : \"Not connected to a Stripe account yet — this is a fresh copy. Follow the steps below.\"}\n

\n See the status page for MRR/ARR and sync\n controls, or browse the raw data.\n

\n\n

\n \n Remix this val\n {\" \"}\n on Val Town.\n
\n \n Create a read-only restricted Stripe key\n {\" \"}\n — the link prefills the exact permissions.\n
\n Add it as STRIPE_API_KEY in your val's environment variables. The\n cron does the rest; the README covers details.\n

\n\n

{EXAMPLE_SQL}

Monthly recognized revenue, straight from your mirrored invoice lines.

\n \n );\n}\n",[],[195,300],"d3f018cc-077c-4bf6-a95a-d302512915c4","events.ts","77a2ac4e-643e-11f1-8747-1607ee4eb77e",["D",1781035488296],"// Incremental sync driven by the Stripe Events API. Reads events since the\n// stored cursor (minus an overlap window), dedupes already-processed event\n// ids, then refetches the canonical objects that changed — in parallel — and\n// upserts them. Event payloads are never trusted as the source of truth; the\n// fresh GET is, so out-of-order events can't write stale data.\nimport { EVENT_OVERLAP_SECONDS, PAGE_SIZE, TABLES } from \"../config.ts\";\nimport { batchWrite, Statement } from \"./db.ts\";\nimport { invoiceLines, subscriptionItems } from \"./backfill.ts\";\nimport {\n creditNoteRow,\n customerRow,\n deleteInvoice,\n deleteRow,\n deleteSubscription,\n invoiceRows,\n objectId,\n priceRow,\n productRow,\n subscriptionRows,\n} from \"./rows.ts\";\nimport { stripeGet, stripeGetMaybe, StripeList } from \"./stripe.ts\";\nimport { eventCursor, knownEventIds, markEventError, recordEvents, saveEventCursor } from \"./state.ts\";\n\nconst MAX_EVENT_PAGES = 20;\n\ntype StripeEvent = { id: string; type: string; created: number; data?: { object?: any } };\n\nexport async function runEventSync() {\n try {\n const cursor = await eventCursor();\n const since = Math.max(0, cursor - EVENT_OVERLAP_SECONDS);\n const events = await fetchEventsSince(since);\n const known = await knownEventIds(events.map((event) => event.id));\n const unseen = events.filter((event) => !known.has(event.id));\n const targets = collectTargets(unseen);\n const counts = await syncTargets(targets);\n await recordEvents(unseen);\n const maxCreated = events.reduce((max, event) => Math.max(max, Number(event.created ?? 0)), cursor);\n await saveEventCursor(maxCreated);\n return { mode: \"events\", events: events.length, unseen: unseen.length, ...counts };\n } catch (error) {\n await markEventError(error instanceof Error ? error.message : String(error));\n throw error;\n }\n}\n\ntype Targets = {\n invoices: Set;\n credit_notes: Set;\n subscriptions: Set;\n customers: Set;\n prices: Set;\n products: Set;\n ignored: Set;\n};\n\nfunction collectTargets(events: StripeEvent[]): Targets {\n const targets: Targets = {\n invoices: new Set(),\n credit_notes: new Set(),\n subscriptions: new Set(),\n customers: new Set(),\n prices: new Set(),\n products: new Set(),\n ignored: new Set(),\n };\n for (const event of events) {\n const object = event.data?.object;\n const id = objectId(object);\n if (!object || !id) continue; // e.g. invoice.upcoming has no id\n switch (object.object) {\n case \"invoice\":\n targets.invoices.add(id);\n break;\n case \"credit_note\":\n targets.credit_notes.add(id);\n break;\n case \"subscription\":\n targets.subscriptions.add(id);\n break;\n case \"subscription_item\":\n addIf(targets.subscriptions, objectId(object.subscription));\n break;\n case \"invoiceitem\":\n addIf(targets.invoices, objectId(object.invoice));\n break;\n case \"customer\":\n targets.customers.add(id);\n break;\n case \"price\":\n targets.prices.add(id);\n break;\n case \"product\":\n targets.products.add(id);\n break;\n default:\n targets.ignored.add(event.type);\n }\n }\n return targets;\n}\n\n// Refetch every changed object concurrently (the rate limiter does the\n// pacing). A 404 or deleted flag means the object is gone — drop our row.\nasync function syncTargets(targets: Targets) {\n const syncedAt = new Date().toISOString();\n const refetch = (enabled: boolean, ids: Set, fn: (id: string) => Promise) =>\n enabled ? Promise.all([...ids].map(fn)) : Promise.resolve([]);\n\n const groups = await Promise.all([\n refetch(TABLES.invoices, targets.invoices, async (id) => {\n const invoice = await stripeGetMaybe(`/v1/invoices/${id}`);\n if (!invoice) return deleteInvoice(id);\n return invoiceRows(invoice, await invoiceLines(invoice), syncedAt);\n }),\n refetch(TABLES.credit_notes, targets.credit_notes, async (id) => {\n const note = await stripeGetMaybe(`/v1/credit_notes/${id}`);\n return note ? [creditNoteRow(note, syncedAt)] : [deleteRow(\"stripe_credit_notes\", id)];\n }),\n refetch(TABLES.subscriptions, targets.subscriptions, async (id) => {\n const sub = await stripeGetMaybe(`/v1/subscriptions/${id}`);\n if (!sub) return deleteSubscription(id);\n return subscriptionRows(sub, await subscriptionItems(sub), syncedAt);\n }),\n refetch(TABLES.customers, targets.customers, async (id) => {\n const customer = await stripeGetMaybe(`/v1/customers/${id}`);\n if (!customer || customer.deleted) return [deleteRow(\"stripe_customers\", id)];\n return [customerRow(customer, syncedAt)];\n }),\n refetch(TABLES.prices, targets.prices, async (id) => {\n const price = await stripeGetMaybe(`/v1/prices/${id}`);\n return price ? [priceRow(price, syncedAt)] : [deleteRow(\"stripe_prices\", id)];\n }),\n refetch(TABLES.products, targets.products, async (id) => {\n const product = await stripeGetMaybe(`/v1/products/${id}`);\n if (!product || product.deleted) return [deleteRow(\"stripe_products\", id)];\n return [productRow(product, syncedAt)];\n }),\n ]);\n\n const stmts = groups.flat(2);\n await batchWrite(stmts);\n return {\n changed: {\n invoices: targets.invoices.size,\n credit_notes: targets.credit_notes.size,\n subscriptions: targets.subscriptions.size,\n customers: targets.customers.size,\n prices: targets.prices.size,\n products: targets.products.size,\n },\n upserts: stmts.length,\n ignored: [...targets.ignored].sort(),\n };\n}\n\nasync function fetchEventsSince(createdGte: number): Promise {\n const events: StripeEvent[] = [];\n let startingAfter: string | undefined;\n for (let i = 0; i < MAX_EVENT_PAGES; i++) {\n const page = await stripeGet(\"/v1/events\", {\n limit: String(PAGE_SIZE),\n \"created[gte]\": String(createdGte),\n ...(startingAfter ? { starting_after: startingAfter } : {}),\n }) as StripeList;\n const data = page.data ?? [];\n events.push(...data);\n if (!page.has_more || data.length === 0) break;\n startingAfter = String(data.at(-1)!.id);\n }\n return events;\n}\n\nfunction addIf(target: Set, id: string | null) {\n if (id) target.add(id);\n}\n",[],[195,308],"0369a34c-077d-4445-8ca6-462dc82ea33e","db.ts","38241008-643e-11f1-822e-1607ee4eb77e",3,["D",1781035381773],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nexport { sqlite };\n\nexport type Statement = { sql: string; args: unknown[] };\n\n// sqlite.batch is transactional; chunk large write sets to keep batches sane.\nexport async function batchWrite(stmts: Statement[]) {\n for (let i = 0; i < stmts.length; i += 200) {\n await sqlite.batch(stmts.slice(i, i + 200) as any);\n }\n}\n\n// Build an idempotent upsert from a column map. Every mirror table keys on\n// `id`, which is what makes backfill, re-sync, and event replay all safe to\n// run repeatedly and concurrently.\nexport function upsert(table: string, cols: Record): Statement {\n const keys = Object.keys(cols);\n const updates = keys.filter((k) => k !== \"id\").map((k) => `${k} = excluded.${k}`).join(\", \");\n return {\n sql: `INSERT INTO ${table} (${keys.join(\", \")}) VALUES (${keys.map(() => \"?\").join(\", \")})\n ON CONFLICT(id) DO UPDATE SET ${updates}`,\n args: keys.map((k) => cols[k] ?? null),\n };\n}\n",[],[195,315],"8e985576-9329-43c3-b40a-f80973e1bb73","6b1d76ac-643e-11f1-85c4-1607ee4eb77e",8,["D",1781035467271],"// Full historical sync. Each enabled resource walks its Stripe list endpoint\n// with a resumable cursor; all resources run in parallel, and within a page\n// the per-invoice/per-subscription child fetches fan out too. The shared\n// limiter in stripe.ts keeps total throughput at ~80% of Stripe's rate limit.\n// Safe to interrupt anywhere: progress is saved page by page and every write\n// is an upsert.\nimport { PAGE_SIZE, Resource } from \"../config.ts\";\nimport { batchWrite, Statement } from \"./db.ts\";\nimport { creditNoteRow, customerRow, invoiceRows, priceRow, productRow, subscriptionRows } from \"./rows.ts\";\nimport { listAll, stripeGet, StripeList } from \"./stripe.ts\";\nimport { markDone, markError, pendingResources, saveCursor } from \"./state.ts\";\n\nconst LIST: Record }> = {\n customers: { path: \"/v1/customers\" },\n products: { path: \"/v1/products\" },\n prices: { path: \"/v1/prices\" },\n subscriptions: { path: \"/v1/subscriptions\", params: { status: \"all\" } },\n invoices: { path: \"/v1/invoices\" },\n credit_notes: { path: \"/v1/credit_notes\" },\n};\n\nexport async function hasPendingBackfill(): Promise {\n return (await pendingResources()).length > 0;\n}\n\nexport async function runBackfill(budgetMs: number) {\n const deadline = Date.now() + budgetMs;\n const pending = await pendingResources();\n const results = await Promise.all(pending.map(async ({ resource, cursor }) => {\n try {\n return await syncResource(resource, cursor, deadline);\n } catch (error) {\n const message = error instanceof Error ? error.message : String(error);\n await markError(resource, message);\n return { resource, error: message };\n }\n }));\n return { mode: \"backfill\", results, done: !(await hasPendingBackfill()) };\n}\n\nasync function syncResource(resource: Resource, cursor: string | null, deadline: number) {\n let pages = 0;\n let objects = 0;\n while (Date.now() < deadline) {\n const page = await stripeGet(LIST[resource].path, {\n limit: String(PAGE_SIZE),\n ...LIST[resource].params,\n ...(cursor ? { starting_after: cursor } : {}),\n }) as StripeList;\n const data = page.data ?? [];\n await batchWrite(await pageStatements(resource, data));\n pages += 1;\n objects += data.length;\n if (!page.has_more || data.length === 0) {\n await markDone(resource);\n return { resource, pages, objects, done: true };\n }\n cursor = String(data.at(-1).id);\n await saveCursor(resource, cursor);\n }\n return { resource, pages, objects, done: false };\n}\n\nasync function pageStatements(resource: Resource, data: any[]): Promise {\n const syncedAt = new Date().toISOString();\n switch (resource) {\n case \"customers\":\n return data.map((customer) => customerRow(customer, syncedAt));\n case \"products\":\n return data.map((product) => productRow(product, syncedAt));\n case \"prices\":\n return data.map((price) => priceRow(price, syncedAt));\n case \"credit_notes\":\n return data.map((note) => creditNoteRow(note, syncedAt));\n case \"invoices\":\n return (await Promise.all(data.map(async (invoice) =>\n invoiceRows(invoice, await invoiceLines(invoice), syncedAt)\n ))).flat();\n case \"subscriptions\":\n return (await Promise.all(data.map(async (sub) =>\n subscriptionRows(sub, await subscriptionItems(sub), syncedAt)\n ))).flat();\n }\n}\n\n// List responses embed the first page of lines/items; only the rare object\n// with more than that costs an extra fetch.\nexport async function invoiceLines(invoice: any): Promise {\n if (!invoice.lines?.has_more) return invoice.lines?.data ?? [];\n return await listAll(`/v1/invoices/${invoice.id}/lines`);\n}\n\nexport async function subscriptionItems(sub: any): Promise {\n if (!sub.items?.has_more) return sub.items?.data ?? [];\n return await listAll(\"/v1/subscription_items\", { subscription: String(sub.id) });\n}\n",[],[195,180],"e6a647de-4804-4c63-9589-10869b34dbae",["D",1781040003397],[],[195,32],["D",1781035365512],[211,212,213,214,215,216,217,218,219,220,221],[195],["D",1781035360700],["D",1789501878689],{"_100":99,"_108":104},{"_100":99,"_101":102,"_103":104,"_18":-5,"_105":106,"_107":59},{"_13":89,"_31":90,"_42":41,"_91":43,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":44,"_98":99},"ctx",{"_82":-5,"_128":374,"_375":376,"_377":378,"_379":24,"_380":24,"_381":24,"_382":59,"_383":-5,"_384":-7,"_385":-7,"_386":146,"_387":24,"_388":24,"_389":-5,"_390":24,"_391":24,"_392":24,"_393":-5},"ENV",{"_345":346,"_347":348,"_349":90,"_350":351,"_352":353,"_354":355,"_356":357,"_358":359,"_360":361,"_362":363,"_364":365,"_366":367,"_368":369,"_370":371,"_372":373},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v32