Razorpay Settlements + Supabase Revenue Table: Daily Reconciliation with Anomaly Alerts to Slack
Your Razorpay settled amount and your Supabase revenue table are not the same number — reconcile them every morning or you're reporting fiction.
The stack in the order it runs — data flows from the source through to where it lands.
Razorpay settles on T+2 or T+3 depending on your plan. The settled amount is never equal to gross transaction volume — refunds, disputes, and platform fees all come out before the money hits your account. If you're not pulling the actual settlement data daily, you're either over-counting revenue in Supabase or missing funds entirely. Both errors compound fast when you're in investor reporting or GST filing season.
The fix is in Razorpay's Settlements API — specifically `GET /v1/settlements` and `GET /v1/settlements/{id}/recons`. That second endpoint gives you the itemized payment_id list for each settlement and every deduction. The dashboard never shows you this. Most founders never pull it. The API is the only path to real reconciliation.
Supabase is the right persistence layer because most technical founders already run it as their application database. Your `payments` or `orders` table with internal revenue records is already there. The reconciliation logic is just a diff: payment_ids Razorpay says settled versus payment_ids in your Supabase rows. Anything that appears in one place but not the other is a flag.
Pipedream wires this up cleanly — one step for Razorpay, one step for Supabase via the PostgreSQL connector, one code step for the diff, one step for Slack. The whole workflow runs in under 30 seconds for most settlement volumes. n8n works equally well; use whatever you already have running. One hard constraint: skip this entirely if your Razorpay settlement volume is below ₹50,000/day. Rounding differences from platform fees will generate constant false alerts. And do not treat this as your accounting system — it's a flag-and-investigate tool, not a replacement for your accountant or Tally.
The stack (5)
How it runs
- 1
Pull yesterday's Razorpay settlements via API
In Pipedream, create a workflow with a daily cron trigger at 8 AM IST. Add a Node.js code step calling `GET https://api.razorpay.com/v1/settlements` with Basic Auth using your key_id and key_secret. Set query params `from` and `to` to yesterday's Unix timestamps — midnight to midnight IST. The response gives you settlement objects. For each one, call `GET /v1/settlements/{settlement_id}/recons?count=100` to get the itemized payment_id list. Collect every payment_id into a single flat array. That array is your source of truth for what Razorpay actually settled yesterday.
- 2
Query your Supabase payments table for the same window
Add a Supabase step or a PostgreSQL step with your Supabase connection string. Run: `SELECT payment_id, amount, status, created_at FROM payments WHERE created_at >= '[yesterday_start]' AND created_at < '[today_start]' AND status = 'captured'`. Then run a second query for refunds: `SELECT original_payment_id FROM refunds WHERE created_at >= '[yesterday_start]'`. Store both result sets as arrays. Your Supabase `payment_id` column must store the Razorpay payment ID directly. If it stores an internal order ID instead, add a join step before proceeding — otherwise the diff is meaningless.
- 3
Run the reconciliation diff in a Code step
Add a Node.js code step. Compare the two payment_id arrays using Set operations: `onlyInRazorpay = razorpayIds.filter(id => !supabaseIds.includes(id))` and `onlyInSupabase = supabaseIds.filter(id => !razorpayIds.includes(id))`. Also compare the total settled amount from Razorpay against the total captured amount in Supabase for the same window — any delta over 2% is a flag. Store the output as: `{onlyInRazorpay, onlyInSupabase, razorpayTotal, supabaseTotal, delta_pct, status}`.
- 4
Classify the reconciliation status
In the same code step, add classification logic. If `onlyInRazorpay.length === 0 && onlyInSupabase.length === 0 && delta_pct < 0.5`, status = 'CLEAN'. If mismatches exist but delta_pct < 2%, status = 'MINOR' — this usually means payments captured near midnight are sitting in a timing gap. If delta_pct >= 2% or mismatch count > 5, status = 'INVESTIGATE'. This classification drives both the Slack message format and which channel gets the alert.
- 5
Generate a one-line anomaly note with Claude for INVESTIGATE cases
Add a conditional step that only fires when status = 'INVESTIGATE'. POST to the Anthropic API with model `claude-3-5-haiku-20241022`. Use this prompt: 'A payment reconciliation flagged these anomalies: [inject onlyInRazorpay, onlyInSupabase, delta_pct]. In one sentence, state the most likely cause from this list: refund timing lag, webhook failure, duplicate payment record, or settlement date mismatch. Be specific.' Inject Claude's output into the Slack message as a 'Likely cause' field. Skip this step entirely for CLEAN and MINOR — you don't need AI commentary on a clean run.
- 6
Post the Slack reconciliation digest
Use the Slack API step to post to #finance-ops. Build the message with Block Kit: header showing status emoji (✅ CLEAN / 🟡 MINOR / 🔴 INVESTIGATE) plus the settlement date, then fields for Razorpay settled total (₹), Supabase captured total (₹), delta amount, delta percentage, and mismatch count. For INVESTIGATE, add a section listing the first 5 mismatched payment_ids with their amounts. For MINOR, add a plain note: 'Likely timing differences — check tomorrow's reconciliation.' Always include the settlement date in the header — don't make someone dig for which day this covers.
- 7
Write reconciliation results to Supabase audit table
Add a Supabase insert step at the end of the workflow. Write to a `reconciliation_log` table with columns: `date`, `razorpay_total`, `supabase_total`, `delta_pct`, `mismatch_count`, `status`, `mismatched_ids` (JSON array), `claude_note`. This table is your 30-day trend view. If the same payment_ids keep showing up as MINOR mismatches day after day, that's not a timing lag — that's a webhook delivery failure. Pull this table monthly with your accountant.
- 8
Set up a secondary alert for unresolved INVESTIGATE flags
Create a second Pipedream workflow on cron, triggering at 6 PM IST. It queries the `reconciliation_log` table for any records from today where `status = 'INVESTIGATE'` and the `resolved` boolean is still `false`. If found, post a reminder to #finance-ops tagging the finance lead: 'Unresolved reconciliation flag from this morning — [delta_pct]% gap, [mismatch_count] payment IDs. Needs resolution before day end.' Without this second trigger, INVESTIGATE flags silently roll into the next morning and nobody acts on them.
Want me to build this for you instead?
Product Audit and CTO Mode run out of this same thinking. If you’re reading this thinking “I want this, but in my product” — let’s talk.
See servicesMore like this
Google Analytics 4 + HubSpot Lifecycle Stage: Weekly Acquisition-Quality Digest to Slack via Pipedream with Claude Narrative
Stop reporting traffic numbers. Report whether the traffic you paid for last week actually became pipeline — with a one-paragraph executive summary written by Claude.
Gmail Thread Aging + Stripe Invoice Overdue: Unified AR Follow-Up Digest to Slack via Zapier
Surface overdue Stripe invoices and the exact age of your last Gmail thread with that customer — every morning at 8:30, automatically — so AR follow-up stops living in someone's head.
Outlook Calendar Load + HubSpot Deal Velocity: Weekly Ops Digest to Microsoft Teams
Every Monday at 07:30, your revenue team gets one Teams card: last week's deal pipeline movement next to each rep's actual meeting load — so you stop guessing whether low close rates are a pipeline problem or a capacity problem.