Card collection through Stripe and bank payment files from NetSuite

#The situation

Money in and money out both depended on someone copying numbers between systems. On the receivables side, a screening company collected by card and ACH through Stripe. Staff charged recurring invoices by hand and matched each Stripe payout back to the invoices, refunds and fees inside it. On the payables side, companies paying vendors in the US and Europe keyed payments into bank portals, and check numbers and positive pay files drifted from what NetSuite recorded.

#What we built

Stripe charging. A Map/Reduce finds invoices eligible for automatic payment and groups them by customer. It checks that each customer has a default payment method in Stripe, charges the invoice, and records the NetSuite customer payment when the charge succeeds.

Stripe reconciliation. A second Map/Reduce reads Stripe balance transactions, meaning charges, refunds, fees and payouts. It creates the matching customer payments, refunds and credit memos, and groups everything in one payout into one bank deposit. Before it processes a transaction, the job checks whether a payment with that Stripe charge id already exists in NetSuite.

Bank payment files. Electronic Bank Payments templates, written in FreeMarker, produce ISO 20022 payment files to one bank's specification for ACH, checks, foreign-currency wires, real-time payments, SEPA and UK Faster Payments. For a second company, buttons on the payment file record send a combined ACH and wire file and a positive pay file to the bank over SFTP and track each file's status, and a scheduled job pulls bank statements back. A third company has domestic and international wire templates with field validation and the bank's formatting rules.

#What it does now

The Stripe jobs poll the Stripe API on a schedule, with no webhooks. Charges and deposits appear in NetSuite already matched, and each run ends with an email that groups the errors for the people who need to act on them. Payment runs leave NetSuite in the bank's own format. A positive pay view with a CSV download, and a tool that updates check numbers on the payment file, keep the bank's records in step with the ledger.

#Under the hood

• Stripe transactions are checked against existing NetSuite payments by charge id, so a rerun does not post twice.
• Grouping by payout makes each NetSuite deposit equal the amount that reached the bank.
• Accounts, payout handling and flows come from a configuration record.
• Payment templates follow ISO 20022 pain.001 where the bank uses it, and the bank's own layout where it does not.
• Field validation inside the wire templates catches values the bank would refuse before the file is sent.
• Where an SFTP delivery bundle was already in place, we adapted it instead of replacing it.