How to Auto-Categorize and Reconcile Bank-Style Stripe Payouts with AI

Run a scheduled workflow that pulls your recent Stripe charges and invoices, lets an Agent-mode Connector node assign accounting categories and match each line to expected revenue as structured JSON, then writes the reconciled results into a MySQL ledger table.

What This Integration Does

Finance teams that take payments through Stripe usually still have to read each charge by hand, decide which revenue category it belongs to (subscription, one-off sale, refund offset, fee), and tick it off against what they expected to bill that period. That manual reconciliation is slow and easy to get wrong once volume grows. This Spojit workflow does the reading and the matching for you: every morning it gathers the latest Stripe activity, asks an Agent-mode Connector node to categorize and reconcile each line into a fixed JSON shape, and lands a tidy, queryable ledger in MySQL that your reporting tools can read directly.

The workflow is started by a Schedule trigger on a cron expression, so no one has to press a button. On each run it lists charges and invoices created since the last window, hands that data to a Connector node in Agent mode with a Response Schema so the output is always valid JSON, and inserts the categorized rows into a MySQL table with an idempotency key on the Stripe object id. Because the insert keys on that id, re-running the workflow over an overlapping window updates or skips rows you already have instead of duplicating them, which keeps the ledger clean if a run retries or you widen the date range.

Prerequisites

A Stripe connection in Spojit (an API key connection with read access to charges and invoices). See the Stripe connector article for setup.
A MySQL connection pointing at the database that will hold your ledger. See the MySQL connector article.
A destination table, for example stripe_ledger, with columns such as stripe_id (unique), object_type, amount, currency, category, expected_match, confidence, charge_created, and reconciled_at.
A short, agreed list of accounting categories (for example subscription, one_time_sale, fee, refund, uncategorized) so the AI output stays consistent.
Enough AI credits in your workspace for the Agent-mode step. See Managing Credits.

Step 1: Start with a Schedule trigger

Add a Trigger node and set its type to Schedule. Enter a 5-field Unix cron expression and an IANA timezone so the window aligns with your accounting day. For a run every weekday at 6am Sydney time, use:

0 6 * * 1-5
Australia/Sydney

The trigger output is { scheduledAt }, which you will use to compute the lookback window. A single Schedule trigger can hold more than one schedule if you want, for example, a mid-day catch-up run as well. For a full walkthrough of cron and timezone fields, see Setting Up a Schedule Trigger.

Step 2: Compute the lookback window

Add a Connector node in Direct mode on the date connector and pick the subtract tool to derive the start of your window from {{ trigger.scheduledAt }}. Subtract one day (or your run interval) to get a since timestamp. Stripe list endpoints filter on Unix seconds, so add a second date Direct-mode step with the unix tool to convert that since value into an epoch integer. Store it as {{ since_unix }} so the next steps can pass it as a created-after filter.

Step 3: List recent Stripe charges and invoices

Add a Connector node on the Stripe connector in Direct mode and select the list-charges tool. Map a created filter to your {{ since_unix }} value and set a sensible page limit (Stripe caps page size at 100). Save the result as {{ charges }}.

Add a second Stripe Direct-mode Connector node using the list-invoices tool, filtered the same way, and save it as {{ invoices }}. Listing both gives the AI the invoice context (what you expected to bill) alongside the actual charge that settled, which is what makes the reconciliation meaningful. If a window can return more than one page, wrap each list call in a Loop node that follows Stripe's pagination cursor until there are no more results.

Step 4: Categorize and reconcile with Agent mode

Add a Connector node and switch it to Agent mode. This is where the agent reads each charge, decides its accounting category, and tries to match it to a corresponding invoice line so you can see whether actual revenue lines up with expected revenue. Paste both lists into the prompt as variables and constrain the categories explicitly:

You are reconciling Stripe activity into an accounting ledger.
Charges: {{ charges }}
Invoices: {{ invoices }}

For each charge, assign exactly one category from:
subscription, one_time_sale, fee, refund, uncategorized.
Where possible, match the charge to an invoice by amount,
currency, and customer, and report whether the actual
amount matches the expected invoice amount.

Turn on the Response Schema so the node always returns structured JSON instead of prose. Define one object per ledger row, for example:

{
  "type": "object",
  "properties": {
    "rows": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "stripe_id":      { "type": "string" },
          "object_type":    { "type": "string" },
          "amount":         { "type": "number" },
          "currency":       { "type": "string" },
          "category":       { "type": "string" },
          "expected_match": { "type": "boolean" },
          "confidence":     { "type": "number" }
        },
        "required": ["stripe_id", "category", "amount", "currency"]
      }
    }
  },
  "required": ["rows"]
}

Save the node output as {{ reconciled }}. Because the schema forces a fixed shape, the next step can rely on {{ reconciled.rows }} without defensive parsing. For more on why a schema makes AI output safe to write straight into a database, see How to Use Structured Output for Reliable AI Data Extraction and Using Connector Nodes in Agent Mode.

Step 5: Write the ledger rows to MySQL

Add a Connector node on the MySQL connector in Direct mode. The cleanest approach is the insert-rows tool: point it at stripe_ledger and map the row fields from {{ reconciled.rows }}. If your reconciled output contains a list, wrap this insert in a Loop node set to ForEach over {{ reconciled.rows }} so each row is inserted in turn, mapping {{ row.stripe_id }}, {{ row.category }}, {{ row.amount }} and so on.

To make re-runs safe, prefer an upsert keyed on stripe_id. Use the execute-query tool with a parameterized statement so an existing Stripe object updates rather than duplicates:

INSERT INTO stripe_ledger
  (stripe_id, object_type, amount, currency, category, expected_match, confidence, reconciled_at)
VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
  category = VALUES(category),
  expected_match = VALUES(expected_match),
  confidence = VALUES(confidence),
  reconciled_at = NOW();

Always pass values as query parameters rather than building the SQL string by hand, so amounts and customer text cannot break the statement.

Step 6: Flag low-confidence matches for review

Add a Condition node after the insert that checks the AI's {{ row.confidence }} or {{ row.category }}. When a row comes back as uncategorized or below your confidence threshold, route the true branch to a Send Email node that emails your finance inbox a short summary of the rows that need a human eye. The Send Email node uses Spojit's built-in mail service, so no extra connection is needed; just make sure your finance address is on the org allowlist under Settings → General → Email recipients. This keeps the ledger trustworthy: clean lines flow straight through, and only the genuinely ambiguous ones land in someone's inbox.

Step 7: Save, enable, and let it run

Save the workflow and enable it so the Schedule trigger becomes active. From here, each scheduled run pulls the latest Stripe activity, categorizes and reconciles it, and grows your MySQL ledger automatically. You can ask Miraxa to extend the canvas at any time, for example: "Add a Condition node that checks if {{ row.expected_match }} is false and connect the true branch to a Send Email node." Miraxa knows the workflow you are editing and will wire the nodes for you.

Tips

Make the categories an explicit, closed list in your prompt. A short fixed vocabulary keeps the AI output stable and your GROUP BY category reports clean.
Keep your Schedule interval and your date lookback in sync (a daily cron with a one-day subtract). Add a small overlap, say two days, and rely on the ON DUPLICATE KEY UPDATE upsert to absorb the overlap without duplicates.
Set a sensible limit on list-charges and list-invoices and paginate with a Loop. Large unpaginated windows make the Agent-mode prompt longer and more expensive.
Filter charges to settled or succeeded status before sending them to the AI so you are not reconciling failed or pending payments.

Common Pitfalls

Stripe list filters expect Unix seconds, not an ISO date string. If you skip the date unix conversion in Step 2, the created filter will not behave as you expect.
Amounts in Stripe are in the currency's smallest unit (for example cents). Store the raw integer or divide consistently, and never mix the two in the same column.
Without the Response Schema, an Agent-mode node can return prose or slightly different field names between runs, which breaks the MySQL mapping. Always enable the schema for anything that writes to a table.
If your stripe_id column is not actually unique in MySQL, the upsert silently degrades into plain inserts and you will get duplicate ledger rows on overlapping windows. Add the unique key first.
Timezone drift: the cron timezone and your accounting day must match, or a run near midnight can pull the wrong calendar day's charges.

Testing

Before enabling the schedule, point the MySQL connection at a scratch table and run the workflow once over a narrow window (a single past day with a handful of charges) using the Run button. Inspect the execution log to confirm the Agent-mode node returned valid JSON in {{ reconciled.rows }} and that each row landed in MySQL with a sensible category. Run it a second time over an overlapping window to prove the upsert updates rather than duplicates. Once the categories and matches look right, swap the connection to your real ledger table and enable the Schedule trigger.