How to Sync Stripe Invoices into a MySQL Finance Table

Build a Spojit workflow that runs on a schedule, lists your recent Stripe invoices, flattens their line items, and upserts each one into a MySQL finance table so your reconciliation reporting always stays current.

What This Integration Does

Finance and operations teams often need a copy of Stripe invoice data inside their own database, where it can be joined against orders, customers, and ledger entries for reconciliation. Manually exporting invoices from the Stripe dashboard and importing them into a database is slow and easy to forget. This workflow keeps a MySQL finance_invoices table continuously in step with Stripe by pulling invoices on a fixed schedule and writing them into rows your reporting tools can query directly.

A Schedule trigger fires the run on a cron expression you set (for example every hour on weekdays). The workflow uses the Stripe connector to list invoices, the JSON connector to reshape each invoice into a flat row, and the MySQL connector to upsert those rows keyed on the Stripe invoice ID. Because the write is an upsert, re-runs are safe: an invoice that has already been stored is updated in place (its status, amounts, and timestamps refresh) rather than duplicated, so the table never accumulates duplicate invoice records no matter how often the schedule fires.

Prerequisites

A Stripe connection in Spojit, using an API key with read access to invoices. See the Stripe connector article for setup.
A MySQL connection in Spojit pointed at the database that holds your finance table. See the MySQL connector article.

A destination table with a unique key on the Stripe invoice ID. A minimal schema:

CREATE TABLE finance_invoices (
  invoice_id     VARCHAR(64) PRIMARY KEY,
  customer_id    VARCHAR(64),
  number         VARCHAR(64),
  status         VARCHAR(32),
  currency       CHAR(3),
  amount_due     INT,
  amount_paid    INT,
  total          INT,
  created_at     DATETIME,
  synced_at      DATETIME
);

Monetary fields are stored as integers because Stripe reports amounts in the smallest currency unit (for example cents).

Knowledge of which Stripe invoice statuses you want to sync (for example open and paid).

Step 1: Add a Schedule trigger

Open the Workflow Designer and add a Trigger node, then set its type to Schedule. Enter a 5-field Unix cron expression and an IANA timezone. To run every hour from 6am to 8pm on weekdays in Sydney time, use:

0 6-20 * * 1-5
Australia/Sydney

A single Schedule trigger can hold more than one schedule if you want different cadences. The trigger output is {{ scheduledAt }}, the timestamp the run started, which you can store on each row to record when the sync last touched it.

Step 2: List invoices from Stripe

Add a Connector node in Direct mode, choose the Stripe connector, and select the list-invoices tool. Map its inputs:

status: open (one of draft, open, paid, uncollectible, void). Run a second branch or a separate node for each status you need.
limit: a page size up to 100 (default 10).
starting_after: leave blank for the first page; pass the last invoice ID to page further (see Tips).

Direct mode is deterministic and spends no AI credits, which is what you want for a predictable list-and-load job. The node returns a list of invoice objects under its output variable, for example {{ stripe_invoices }}.

Step 3: Loop over each invoice

Add a Loop node set to ForEach and point it at the invoice list, for example {{ stripe_invoices.items }}. Each iteration exposes the current invoice (referenced below as {{ invoice }}). Everything in Steps 4 and 5 lives inside the loop body so it runs once per invoice. See the guide on using Loop nodes for iteration details.

Step 4: Flatten the invoice into a row with the JSON connector

Inside the loop, add a Connector node in Direct mode using the JSON connector with the pick tool to keep only the fields your table needs, dropping Stripe's large nested payload. Select keys such as id, customer, number, status, currency, amount_due, amount_paid, total, and created from {{ invoice }}.

If your reporting needs per-line detail instead of the invoice summary, use the JSON get tool to pull {{ invoice.lines }} and a second Loop over those line items. For a finance reconciliation table the invoice-level summary in this tutorial is usually enough. Call the cleaned output {{ row }}.

Step 5: Upsert the row into MySQL

Still inside the loop, add a Connector node in Direct mode, choose the MySQL connector, and select the execute-query tool. Use a parameterized statement so values bind safely through the params array rather than being concatenated into the SQL. An upsert keyed on invoice_id makes re-runs idempotent:

INSERT INTO finance_invoices
  (invoice_id, customer_id, number, status, currency,
   amount_due, amount_paid, total, created_at, synced_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(?), NOW())
ON DUPLICATE KEY UPDATE
  status      = VALUES(status),
  amount_due  = VALUES(amount_due),
  amount_paid = VALUES(amount_paid),
  total       = VALUES(total),
  synced_at   = NOW();

Map the params array to the row fields in the same order as the ? placeholders:

[
  "{{ row.id }}",
  "{{ row.customer }}",
  "{{ row.number }}",
  "{{ row.status }}",
  "{{ row.currency }}",
  "{{ row.amount_due }}",
  "{{ row.amount_paid }}",
  "{{ row.total }}",
  "{{ row.created }}"
]

Stripe's created field is a Unix timestamp, so FROM_UNIXTIME(?) converts it to a DATETIME. The ON DUPLICATE KEY UPDATE clause is what makes the sync safe to repeat on every scheduled run.

Step 6: Confirm the load and notify on completion (optional)

After the loop, add a Send Email node to post a short confirmation from Spojit's built-in mail service. Set Recipients to your finance address, a templated Subject such as Stripe invoice sync at {{ scheduledAt }}, and a Body noting how many invoices were processed. Only upstream variables resolve in the email, and external recipients must be on your org allowlist under Settings > General > Email recipients. See the guide on Send Email nodes. Save the workflow to make the schedule active.

Tips

Stripe returns at most 100 invoices per call. To cover more, read the last invoice ID from the page and feed it into starting_after on a follow-up list-invoices call, repeating until a page comes back short.
Keep monetary values as integers end to end. Convert to a decimal only in your reporting query, not during the sync, so you never lose precision.
Add a synced_at timestamp (shown above) so you can spot rows that have not refreshed and audit how fresh the table is.
If you need judgment about which invoices to include rather than a flat status filter, switch the Stripe node to Agent mode and describe the criteria in a prompt; for a deterministic load, Direct mode is cheaper and faster.

Common Pitfalls

Missing unique key. Without a PRIMARY KEY or UNIQUE index on invoice_id, the ON DUPLICATE KEY UPDATE clause never fires and every run inserts duplicate rows.
Treating amounts as dollars. Stripe amount fields are in the smallest currency unit. Storing them as decimals or dividing too early causes rounding errors in reconciliation.
Timezone confusion. The Schedule cron runs in the IANA timezone you set, but Stripe's created is UTC. Decide whether your created_at column should be UTC and keep it consistent.
Unparameterized SQL. Always pass values through the params array with ? placeholders. Building the query string by hand from invoice fields is fragile and unsafe.
Status drift. An invoice you stored as open may later become paid or void. The upsert refreshes status only if your run also lists those statuses, so include every status you care about across your branches.

Testing

Before relying on the schedule, validate on a small scope. Set the Stripe limit to a low number such as 3 and point the MySQL connection at a staging copy of the table. Use the Run button to trigger the workflow manually instead of waiting for the cron, then open the run in execution history and confirm each loop iteration produced an upsert. Query the table to verify the rows, run the workflow a second time, and confirm the row count did not grow (only synced_at changed). Once duplicates are ruled out, raise the limit, point the connection at production, and let the schedule take over. You can ask Miraxa, the intelligent layer across your automation, questions like "Why did my last run fail?" if an iteration errors.