Settlements and payout reconciliation

Settlement reconciliation is the financial spine of ecommerce ingestion. Order posting without payout and bank reconciliation is incomplete.

Published settlement, payout, clearing, and exception metrics follow the grains and control gates in the warehouse marts contract.

Control equation

For one source account, settlement, and currency:

captured sales
+ shipping collected
+ tax collected
- discounts funded by merchant
- customer refunds
- chargebacks and dispute losses
- provider and marketplace fees
- reserves and withholdings
+ reserve releases and other credits
+/- explicit adjustments
= expected payout

expected payout must equal the provider payout and, after timing differences, the matched bank amount. Each term is the sum of typed settlement lines. Unclassified differences are exceptions, not rounding.

Data model

create table settlement_lines (
  tenant_id uuid not null,
  id uuid not null default gen_random_uuid(),
  settlement_id uuid not null,
  source_connection_id uuid not null,
  source_line_id text not null,
  line_type text not null check (line_type in (
    'sale','shipping','tax','discount','refund','chargeback','fee',
    'reserve_hold','reserve_release','withholding','adjustment','rounding'
  )),
  amount numeric(20,6) not null,
  currency_code char(3) not null,
  occurred_at timestamptz not null,
  source_order_id text,
  source_payment_id text,
  source_refund_id text,
  source_dispute_id text,
  accounting_mapping_id uuid,
  allocation_state text not null check (allocation_state in ('unallocated','allocated','exception')),
  payload_sha256 char(64) not null,
  primary key (tenant_id, id),
  unique (tenant_id, source_connection_id, source_line_id)
);

create table payout_matches (
  tenant_id uuid not null,
  id uuid not null default gen_random_uuid(),
  payout_id uuid not null,
  bank_transaction_id uuid,
  odoo_payment_id bigint,
  expected_amount numeric(20,6) not null,
  provider_amount numeric(20,6) not null,
  bank_amount numeric(20,6),
  currency_code char(3) not null,
  difference_amount numeric(20,6) not null,
  match_rule_version text,
  state text not null check (state in (
    'unmatched','suggested','approved','odoo_posting','reconciled','exception'
  )),
  resource_version bigint not null default 1,
  approved_by uuid,
  approved_at timestamptz,
  primary key (tenant_id, id),
  unique (tenant_id, payout_id)
);

These logical tables use the platform's composite tenant foreign keys, forced RLS, audit, and immutable approval controls.

Allocation rules

Allocation follows this order:

1. Provider-supplied order, transaction, refund, dispute, or payout relation.
2. Exact provider reference already mapped to a canonical record.
3. Exact amount, currency, account, and bounded date window under an approved deterministic rule.
4. Reviewer-approved manual allocation.

One settlement line may allocate to multiple canonical objects only when the provider explicitly aggregates them or the approved rule emits balanced allocation rows. Allocation rows sum exactly to the settlement-line amount.

Payout and bank matching

A payout match requires:

• same tenant and source account;
• exact currency;
• exact provider/bank reference where available;
• exact amount, except an approved explicit rounding line;
• bank value date within the policy window;
• an unreconciled bank transaction in the allowed Odoo company and journal;
• no conflicting active match or operation.

Amount-and-date-only matching may create a suggestion but cannot auto-approve.

State machine

stateDiagram-v2 [*] --> imported imported --> normalized normalized --> allocating allocating --> allocated allocating --> exception allocated --> balanced allocated --> exception balanced --> payout_matched payout_matched --> bank_matched bank_matched --> odoo_posting odoo_posting --> reconciled odoo_posting --> verification_required verification_required --> reconciled verification_required --> repair_required reconciled --> closed

closed requires immutable source totals, all lines classified, the control equation balanced, provider payout matched, bank transaction matched, Odoo clearing reconciliation read back, and no unresolved exception.

Odoo accounting pattern

The tenant policy defines one clearing account per provider and currency. The normal flow is:

1. Customer payments debit provider clearing and credit receivable, then reconcile with customer invoices.
2. Refunds and credit notes reverse receivable and provider clearing as approved.
3. Fees, reserves, withholdings, disputes, and adjustments post from settlement evidence to mapped accounts against provider clearing.
4. The bank payout debits bank and credits provider clearing.
5. Odoo reconciliation clears the payout-side entries against the bank transaction.

The platform validates expected account and currency balances before posting. Odoo remains authoritative for resulting journal entries, exchange differences, residuals, and reconciliation links.

Exceptions

Code	Meaning	Required action
SETTLEMENT_TOTAL_MISMATCH	Typed lines do not equal provider total	Re-extract or inspect unclassified lines
SETTLEMENT_LINE_UNKNOWN	New provider line type	Add and approve a mapping rule
SETTLEMENT_CURRENCY_MISMATCH	Mixed/incorrect currency	Manual accounting review
PAYOUT_NOT_FOUND	Provider payout has no eligible bank item	Wait or investigate destination account
PAYOUT_MULTIPLE_BANK_MATCHES	Match is ambiguous	Reviewer selects evidence-backed item
PAYOUT_AMOUNT_MISMATCH	Expected, provider, and bank amounts differ	Classify fee/FX/withholding or repair source data
ODOO_CLEARING_NOT_BALANCED	Odoo readback leaves unexplained balance	Repair task; no auto-close
ODOO_RECONCILIATION_UNKNOWN	Request timed out without final evidence	Verification before any retry

API

GET  /api/v1/tenants/{tenant_id}/settlements
GET  /api/v1/tenants/{tenant_id}/settlements/{settlement_id}
GET  /api/v1/tenants/{tenant_id}/settlements/{settlement_id}/lines
POST /api/v1/tenants/{tenant_id}/settlements/{settlement_id}/recalculate
POST /api/v1/tenants/{tenant_id}/payout-matches/{match_id}/approve
POST /api/v1/tenants/{tenant_id}/payout-matches/{match_id}/post
GET  /api/v1/tenants/{tenant_id}/payout-matches/{match_id}

Commands require Idempotency-Key, optimistic If-Match, accounting-role authorization, and 202 Accepted operation responses under the common platform API contract.

Acceptance examples

Golden cases include one sale payout, multi-order payout, partial refund, refund in later settlement, fee with tax, reserve hold/release, dispute won/lost, payout split across bank dates, duplicate settlement line, late correction, missing bank transaction, wrong currency, and timeout after Odoo mutation.