Daily Stripe reconciliation to Postgres with a Slack alert

Trigger

Schedule Trigger, daily at 02:00

Nodes

7

Requires

Stripe (HTTP Header Auth), Postgres, Slack

Category

Payments

From the course

Stripe and Payment Automations

Published

2026-09-10

What it does

Node by node

1. 1

   Schedule Trigger Fires once a day at hour 2.

2. 2

   Get balance transactions GET https://api.stripe.com/v1/balance_transactions with created[gte] set to the start of yesterday UTC, created[lt] set to the start of today UTC, and limit 100. Pagination is on: each request sets starting_after to the last id of the previous response and stops when $response.body.has_more is false. Authenticates with a generic HTTP Header Auth credential.

3. 3

   Collect matchable txn ids Flattens every page into one list, keeps only transactions whose type is charge, payment, refund or payment_refund, groups their ids by currency, and returns one item per currency with run_date, currency, the id array and the count. If nothing matched it returns a single zero-count row so the day still gets a log entry.

4. 4

   Find unmatched txn ids Runs SELECT count(*) AS missing_in_events and array_agg of the ids over unnest($1::text[]) LEFT JOIN stripe_events ON e.balance_transaction = bt.txn_id WHERE e.id IS NULL. The id array is passed as a Postgres array literal built in the query replacement expression.

5. 5

   Write reconciliation log Inserts run_date, currency, stripe_txn_count, matched_count (the Stripe count minus the missing count), missing_in_events, a hardcoded 0 for unmatched_in_events, and the missing ids joined into a notes string, into stripe_reconciliation_log.

6. 6

   Any gap? An If node that tests missing_in_events greater than 0. The false output is empty, so a clean day ends silently with the log row already written.

7. 7

   Slack: reconciliation gap Posts to the channel named in the node saying how many of the day balance transactions, in which currency, have no row in stripe_events.

Setup after import

1. 1

   Create an HTTP Header Auth credential holding Authorization: Bearer plus your Stripe secret key, and select it on Get balance transactions.

2. 2

   Create a Postgres credential and select it on both Postgres nodes.

3. 3

   Create stripe_reconciliation_log with run_date (date), currency, stripe_txn_count, matched_count, missing_in_events, unmatched_in_events and notes.

4. 4

   Make sure stripe_events exists and its balance_transaction column is populated. The webhook signature verification template writes it.

5. 5

   Create a Slack credential and change the channel on Slack: reconciliation gap from the default #alerts to your own.

6. 6

   Check the 02:00 hour against your n8n timezone. The query window is calculated in UTC, so a large offset means the run covers a day that is not the one you expect.

Limits

• One direction only. It finds Stripe transactions missing from your table, not rows in your table missing from Stripe.

• unmatched_in_events is written as a literal 0 and is never calculated.

• Only four balance transaction types are compared. Payouts, fees, transfers and adjustments are ignored on purpose.

• It alerts on a gap. It does not backfill the missing events.

More templates