Subscription audit

Keeps fin_subscriptions accurate by periodically reconciling it against real email evidence — new subscriptions, price changes, renewals, cancellations, trials converting, and failed payments.

Requirements

This skill requires Gmail to be connected in Claude. If it isn't available:

1. Log a row to fin_sync_log with ok: false and a note that Gmail wasn't connected.
2. Stop — do not attempt any reconciliation without email evidence.

Database access

Read .env.local for NEXT_PUBLIC_SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY. Talk to Supabase's PostgREST API directly via curl, sending both headers on every request:

curl "$NEXT_PUBLIC_SUPABASE_URL/rest/v1/fin_subscriptions?select=*" \
  -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY"

Read (select):

curl "$NEXT_PUBLIC_SUPABASE_URL/rest/v1/fin_subscriptions?select=*&status=eq.active" \
  -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY"

Update (patch) a subscription by id:

curl -X PATCH "$NEXT_PUBLIC_SUPABASE_URL/rest/v1/fin_subscriptions?id=eq.<ID>" \
  -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"price": 12.99, "renews_on": "2026-08-01"}'

Insert a new subscription row:

curl -X POST "$NEXT_PUBLIC_SUPABASE_URL/rest/v1/fin_subscriptions" \
  -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"name": "Example Co", "status": "needs-review", "price": 9.99, "currency": "USD", "billing_interval": "monthly"}'

Insert a fin_sync_log row the same way, against /rest/v1/fin_sync_log.

Procedure

1. Determine the window. Query fin_sync_log for the most recent row with kind = 'audit'. If found, use its timestamp as the window start. If none exists, default to the last 14 days.
2. Fetch current state. Pull all rows from fin_subscriptions.

3. Sweep Gmail, scoped to the window, with these subject/body searches:

   • receipt / invoice / payment confirmation
   • renewal / renews / your subscription
   • free trial / trial ending
   • payment failed / payment unsuccessful / update your payment
   • price change / price increase
   • from:stripe.com receipt
   • from:paypal.com

   Exclude anything that's money the user is receiving (e.g. their own Stripe payout notifications, invoices they issued) — this audit is about subscriptions they pay for, not their revenue.

4. Reconcile each finding against fin_subscriptions:
   • Price-change email → PATCH the matching row's price.
   • Renewal receipt → PATCH renews_on to the new date.
   • Evidence of a new recurring charge not in the table → POST a new row with status: 'needs-review'. Never guess a billing_url — leave it blank if you don't have a confirmed one.
   • Cancellation confirmation → PATCH status to 'cancelled'.
   • New trial → POST a new row with trial_ends_on set and status: 'trial'.
   • Payment failure → append a note to the row (don't overwrite existing notes) flagging it, e.g. prefix with "⚠ payment failing as of <date>".
   • Never delete a row.
   • Never change a row without direct email evidence backing the change.
5. Log the run. Insert a fin_sync_log row with kind: 'audit', ok: true, and counts of each reconciliation type performed.
6. Report, under 10 lines:
   • Counts of what changed (new / updated / flagged / cancelled).
   • Anything now needs-review that needs a human look.
   • Any payment failures found.
   • Total estimated monthly spend in the home currency: read the home currency from src/lib/config.ts (CONFIG.currency.code), then for each active subscription — weekly price × 52 ÷ 12, annual price ÷ 12, monthly price as-is — convert any USD-priced subscription to the home currency using the fx_usd_home rate stored in fin_settings, then sum.