Integrating Payment, Commerce, and Finance Data

In plain terms

An order, a successful payment, a bank deposit, and recognized revenue describe different facts. A 120-dollar annual subscription paid today may fund twelve months of service; the processor may deduct a fee and transfer cash later. Integrating these sources means retaining the identifiers and timing needed to explain each difference, not forcing all four totals to be equal.

This article connects processor events, orders, wallet movements, transfers, and accounting reports. It builds on the earlier ingestion and event-contract articles. The examples distinguish payment movements, wallet balances, reconciliation, cutoff views, and exchange-rate rules. It does not implement a general ledger or determine an entity’s accounting policy.

Payment lifecycle and ingestion contracts

Authorization reserves funds or confirms an approved amount; capture requests collection. Fees, refunds, disputes, availability of funds, and payouts have their own records and timing. A payout may batch many payments, and an order may have several payment attempts or partial captures. Payment status, refund status, and dispute status often need separate dimensions instead of one universal linear state.

Authorization failures have their own event type. A missing authorization or excessive cumulative refunds under a defined contract mean the observed history requires investigation: they do not prove the processor created an impossible transaction. An incomplete export or delayed event can explain missing history. Stripe documents disputes on refunded transactions, so a restricted linear state machine must not be treated as a universal production rule.

Persist provider, account, environment, event ID, object ID, source time, observed time, and payload version. Verify webhook signatures against raw request bytes, store durably before acknowledging, and process asynchronously. Deduplicate delivery retries; conflicting payloads for one event ID need investigation. Distinct events may describe the same business object, so movement identity must also be defined. Recover gaps through paginated API reads with stored cursors and an overlap window. Reconcile against independent source reports rather than assuming webhooks or a single API page are complete. Stripe webhook documentation describes retries, duplicates, and ordering constraints.

Exact amounts and a movement ledger

Use integer minor units or fixed-precision decimal amounts with an explicit currency and scale. Binary floating point is the problem; decimal arithmetic is not an anti-pattern. USD and EUR use cents, but not every currency has two decimal places, and processor API representations can have exceptions. Check range limits when storing integers. Rates require decimal precision too: construct Decimal values from strings, then specify rounding explicitly. Decimal operations still use a finite context precision; choose enough significant digits for the largest amount, rate, and aggregate. See the Python Decimal documentation and Stripe currency representations.

Define the perspective of a signed movement. Here a capture increases the processor balance; fees, refunds, and dispute debits decrease it. Authorization and authorization failure are status events and must not enter cash totals. Never add EUR cents to USD cents. A sum of movements is a net change; a balance also requires an opening balance and a consistent account, currency, and time boundary.

Keep immutable raw records and a deduplicated movement view. This is an operational subledger, not double-entry bookkeeping. A general-ledger journal needs accounts and balanced debit/credit entries, plus mappings for taxes, fees, receivables, liabilities, and revenue. For example, a prepaid receipt can debit cash or processor receivable and credit a contract liability; performance of the service can later debit that liability and credit revenue under the approved policy. A materialized current-state table can coexist with the history if its derivation and reconciliation are controlled.

Preserve occurrence time, processor posting/availability time, ingestion time, bank value date, and accounting period where available. A cutoff report needs both when a transaction happened and when it became known. Append-only storage alone does not freeze a query: a late row dated last month can change last month’s result. Save the input snapshot or knowledge cutoff and the transformation version used for the report.

Gift cards and revenue recognition

For an issuer promising its own goods or services, selling prepaid value generally creates an obligation before the related performance is complete. Redemption does not automatically establish the timing of revenue: redeeming a card for future subscription service can leave an obligation to deliver that service. Expiry is not an automatic instruction to book revenue either. Breakage and any obligation to remit unclaimed balances require the applicable accounting and legal policy. The IFRS 15 overview links revenue recognition to satisfying performance obligations; this article uses it for that distinction, not as a complete accounting treatment.

The operational roll-forward is: opening balance + issued value + signed adjustments − redeemed value − expired value − refunded value = closing balance. Fix the population, currency, and cutoff on every term. Reconcile the calculated result to an independently supplied closing snapshot, ideally per card; defining closing balance as the residual and testing the same equation proves nothing about completeness.

A balance-only feed cannot distinguish redemptions, expiry, and adjustments without movement history. Missing information is an explicit data gap with an owner, not permission to fabricate an accounting entry. Test accounts should carry reviewed environment and transaction classifications. Excluding a transaction from commercial analytics does not erase real funds from bank or processor reconciliation; those amounts still need an accounting disposition.

Reconcile along the transaction chain

Orders to captures: match order, payment attempt, currency, and authorized capture allocation. Sum partial captures under the source contract rather than overwriting them in a dictionary keyed only by payment. Refunds and disputes to originals: retain original payment IDs, cumulative amounts, and outcome or reversal records. Entitlements to orders: match each grant to a purchase or an approved reason such as a promotion, and investigate a paid order whose service was not granted.

Processor movements to payouts: use batch membership or reconcile opening balance + net movements − transfers = ending balance, including reserves, holds, and adjustments. A day’s net movement is not generally that day’s payout. Stripe payout reconciliation distinguishes settlement-batch reconciliation from balance reporting. Payouts to bank: match transfer identifiers, currency, amount, status, and expected arrival range; use both sides of the join to find missing records. In-transit funds and failed transfers need explicit states.

Wallet movements to balances form another layer. Warehouse to accounting then bridges the operational measures to approved journal accounts and periods, including deferred revenue, taxes, fees, foreign exchange, and other adjustments. A capture-minus-refund metric is not automatically recognized revenue, and a dispute debit need not have the same classification as a sales refund. Compare amounts after documenting these mappings; row counts alone are insufficient.

An exception record needs layer, source IDs and versions, currency, expected and observed amounts, difference, age, owner, status, evidence, and resolution. Set thresholds with finance for individual and aggregate differences, alongside qualitative risks. Escalate unresolved items before close. The authorized finance owner decides whether to delay, approve a documented exception, or make a supported adjustment; the pipeline must not silently insert a balancing plug.

Period close and reproducible revisions

Preserve an issued report and its input and code versions. A later operational view can include new refunds or disputes without overwriting that original artifact. Keep the original period, posting period, observed time, correction reason, and approval on each adjustment. Version a revised report explicitly so users can distinguish as-issued from restated results.

Do not impose a rule that every historical error is booked only in the discovery month. Applicable standards and materiality determine whether retrospective correction is needed; the IAS 8 overview describes retrospective correction of material prior-period errors, subject to its conditions. Finance owns that judgment. The engineering requirement is to preserve the original and the approved correction trail.

Currency policy and access boundaries

Retain original and settlement currencies, the reporting currency, rate direction, source, date, precision, and rounding policy. Here both currencies have two decimal places, and a rate means USD per EUR, so EUR cents times the rate yields USD cents before rounding. Other currency scales need an explicit scale conversion. Accounting may require different rates for different purposes. Record the selected decimal precision, rounding mode, and whether rounding occurs per row or after aggregation.

Prefer hosted payment collection and processor references so the analytics pipeline does not ingest raw card numbers or sensitive authentication data. Exclude sensitive fields from raw landing zones, logs, and error payloads, not just final tables. Tokens do not automatically place an entire system outside PCI DSS scope; token type, connectivity, and access to cardholder data matter. The PCI SSC tokenization guidance explains that scope reduction is conditional. Determine the actual boundary with the responsible security and compliance owners.

Restrict payment datasets and service accounts to their purposes, encrypt transport and storage, manage secrets separately, and audit access. Customer IDs can remain linkable even when pseudonymous. Retention and deletion must respect both applicable obligations and the need to reproduce approved financial records.