Recoup Weekly Usage Review

Five independent data pulls plus one synthesis. Run the pulls in parallel (subagents work well — each section is self-contained), then write the week-in-review. First validated end-to-end 2026-07-20.

Define the window up front as UTC [FROM, TO) — normally the last 7 days — and compute every section against the same window plus the prior 7 days for comparison.

Prerequisites

• PRIVYAPPID + PRIVYPROJECTSECRET — Privy Management API (Basic auth).
• Stripe: an authenticated Stripe CLI or a live restricted key. Caveat: the

CLI stores its key in the macOS keychain, which sandboxed shells often cannot read — the CLI then falls back to an interactive stripe login pairing flow. Prefer passing the key explicitly (stripe ... --api-key "$STRIPERESTRICTEDKEY").

• Supabase access to the production project (MCP execute_sql or psql),

read-only.

• AIGATEWAYAPIKEY (vck...) — AI Gateway reporting.
• A fresh Vercel session token for Web Analytics: run vercel login, then

read the token from ~/Library/Application Support/com.vercel.cli/auth.json.

• The Vercel team id and the chat project name/id.

Never mutate anything: every call in this skill is a GET or a SELECT.

1. Logins & signups (Privy)

node scripts/privylogins.mjs --from 2026-07-13 --to 2026-07-20 (script ships alongside this skill; needs only PRIVYAPPID/PRIVYPROJECTSECRET in the environment). It paginates GET https://api.privy.io/v1/users and reports every user whose max latestverified_at across linked accounts falls in the window, split into new signups vs returning.

Gotchas learned the hard way:

• GET /api/admins/privy on the Recoup API needs an admin token; a normal

recoupsk API key authenticates but returns isAdmin: false → 403. The direct Privy route needs no Recoup admin and returns identical data.

• latestverifiedat moves only on a fresh auth verification. A returning

user riding a long-lived session never bumps it, so this undercounts actives. Always cross-check with the interactive-chats SQL (query 5b in references/sql.md) — users with chats but no login event are session-riders.

• Sanity-check a quiet week by widening to 14 days; if the wider window shows

returning users, the field is working and the quiet week is real.

Signup-cohort scorecard: grade every new signup in the window against the activation funnel in references/onboarding-checkpoints.md (one column per account, one row per checkpoint, using the internal checks). The story is where the cohort stalls — historically nobody reaches step 7 (first scheduled task), which is the step that predicts retention — and which warm leads (measured-but-unclaimed valuations, enterprise domains) need human outreach.

2. Web UI traffic (Vercel Web Analytics)

There is no documented query API, but the dashboard's internal one accepts a plain bearer token (the fresh CLI token) — the v2 prefix is mandatory:

GET https://vercel.com/api/web-analytics/v2/{overview|timeseries|stats}
  ?environment=production&filter=%7B%7D&projectId=<project>&teamId=<team>
  &tz=UTC&from=<ISO>&to=<ISO>
  # stats also: &type={path|referrer|country|event_name}&limit=250

devices = unique visitors. Interpret paths before quoting the visitor count: visitors whose only path is / bounced at the login screen (marketing clickthrough), so report "reached the app" and "got past the homepage" separately. If type=event_name is empty, no custom events are instrumented and analytics cannot attribute identity — say so.

3. Stripe billing changes

Read-only pulls, live mode, window-scoped by --created / created[gte]:

1. Subscriptions created, canceled, or plan-changed in the window; trials

started/converted (stripe subscriptions list --limit 100, inspect created, canceledat, trialstart, trial_end).

1. Invoices and charges in the window; failed payments and disputes

(stripe events list --created ... for invoice.payment_failed, charge.dispute.created).

1. The full current roster, split into actually collecting vs

pausecollection set (keepas_draft / void subs read as "active" but collect $0 — report their nominal MRR separately, with resume dates).

1. Expired Checkout Sessions with metadata.purpose = credits_topup. A pile of

identical small sessions from one account is a programmatic loop, not abandoned carts — report per-account counts and the daily spike shape.

A restricted key may be denied on /v1/account, /v1/prices, /v1/payouts; product names and pause_collection detail may need a full secret key. Report which calls were denied rather than guessing.

4. Credits consumption (Supabase)

Run the SQL pack in references/sql.md (queries 1–10). Schema facts that make or break it:

• The ledger is usageevents; creditsdeducted_cents is the amount and

1 credit = 1 cent (UNITAMOUNTCENTSPERCREDIT = 1 in api/lib/stripe/createCreditsStripeSession.ts).

• creditsusage is one balance-snapshot row per account (remainingcredits);

its timestamp is last-update, often stale.

• Account email lives in account_emails (1-to-many — LATERAL LIMIT 1).
• Chats join accounts only via sessions (`chats.session_id → sessions.id →

sessions.account_id`).

• Scheduled-task runs are the chats titled 'Scheduled generation'; report the

scheduled-vs-interactive split — it is the character of the business.

• scheduledactions.lastrun is not written by the Vercel Workflows

runner; infer task activity from chats + emailsendlog + usage events.

Always report: per-account burn with emails, what the top burners ran, accounts with negative balances that kept burning (nothing stops them at zero — a standing risk), prior-week actives that went silent (churn signal), and the daily curve (Mondays spike from weekly crons).

5. AI Gateway spend vs credits (reconciliation)

Gateway side — the beta Custom Reporting API (account-scoped, uses AIGATEWAYAPI_KEY; billed ~$5 per 1,000 queries, so batch — a full weekly pull needs ≤ 10 queries):

GET https://ai-gateway.vercel.sh/v1/report   # group by day, model, apiKey, user
GET https://ai-gateway.vercel.sh/v1/credits  # current balance — flag if < ~3 days of runway

(The api.vercel.com/v1/ai-gateway/* paths do not exist.)

Reconcile: gateway totalcost for the window vs SUM(creditsdeducted_cents) (query 2), remembering that flat-fee charges (5¢ research, scrapes) are credits without gateway cost — subtract them (query 3 splits by model attribution) before naming the leakage number. Decompose any gap by API key first (a non-app key = spend that bypasses billing entirely), then by day. Known leakage mechanisms in the app code: handleChatCredits skips silently when accountId is absent and swallows billing errors, and errored turns that produce no responseMessage are never charged. If most gateway rows carry an empty user tag, per-account attribution is impossible — recommend setting providerOptions.gateway.user = accountId in the app.

6. Catalog value ranking

Recoup stores no dollar values — only Spotify lifetime play counts in song_measurements; the /valuation tool computes dollars client-side. Rank with queries 11–14 in references/sql.md, then convert streams to a central value with the marketing formula (canonical implementation: marketing/lib/valuation/computeCatalogValuation.ts in the monorepo — re-derive from there if precision matters):

annual_streams ≈ lifetime_streams / catalog_age_years   (age from earliest release)
central_value ≈ annual_streams × $0.0035 × 1.4 × 0.6375 × 13

Report the top catalogs with owner emails and paying status, valuations run in the window, and measured-but-unclaimed runs (warm leads). Caveats to carry: exclude internal/test rows (sweetmantech*, sidney@, [TEST]); a catalog with no earliest-release link falls back to a default age and over-values; the Supabase subscriptions mirror may be empty — verify paying status against Stripe, not the DB.

7. Synthesis

Write the week-in-review in this shape, leading with the single most consequential finding (not necessarily revenue):

1. One-paragraph TLDR — what changed, what's at risk, the one urgent item.
2. Dimension table — logins/visitors, paid roster delta, credit burn +

scheduled/interactive split, gateway spend vs credits recorded, top catalogs/leads, broken plumbing found along the way.

1. Prioritized actions — stop-the-bleeding today, revenue this week,

product/integrity next. Name owners where obvious.

Cross-reference the pulls against each other before publishing: signups with no chats = bounced onboarding; credit-active accounts with no login = scheduled autopilot; gateway spend with no credits = billing leakage; a valuation run with no claim = an unworked lead.