The Settlement GapSQL investigation

Settlement reconciliation SQL, DuckDB and PostgreSQL

The Settlement Gap

Why do so many EUR purchases from the 2024-11-12 close still carry a fee-mismatch exception?

Snapshot
Synthetic demo snapshotsettlement-gap-v5.0.0
As of
10 Jan 20251 Jan 2023 to 31 Dec 2024
Population
456,398eligible purchases

Agree on what a clean close means

A status report calls a purchase done once the customer pays. Operations can only close the day when the money that arrives later matches the expected amount, currency, fee and deadline. These four metrics define that check. Refunds and transfers stay in the data but sit outside it.

Exception countexception_queueDistinct eligible payments with at least one independent exception flag.payment
Population
Completed merchant purchases only.
Currency
Payment currency retained
Model
mart_exception_queue
Fee deltasegment_isolationRecorded processing fee minus the effective merchant-term fee.payment, then date and currency
Population
Eligible purchases with settlement evidence.
Currency
Never aggregated across currencies
Model
int_settlement_reconciliation
Overdue valueclose_summaryGross value missing past its effective SLA or actually settled after that SLA.close date and currency
Population
Completed merchant purchases only.
Currency
Never aggregated across currencies
Model
mart_daily_close
Settlement coverageclose_summaryPurchases with evidence, matching currency, and gross identity within 0.01 divided by eligible purchases.close date and currency
Population
Completed merchant purchases only; refunds and transfers are excluded.
Currency
Never aggregated across currencies
Model
mart_daily_close
Match tolerance
€0.01

Two kinds of evidence, kept apart until the join

The merchant contract says what a payment should cost. The settlement record says what it did cost. Effective dates pick the contract that applied on the purchase day, and a left join keeps a missing settlement visible instead of dropping the payment.

Ownership context

customers

Grain
One customer
Key
customer_id

Currency boundary

accounts

Grain
One account
Key
account_id

Event spine

transactions

Grain
One payment event
Key
transaction_id

Operating segment

merchants

Grain
One merchant
Key
merchant_id

Expected fee and SLA

merchant_terms

Grain
One merchant and validity interval
Key
merchant_id + valid_from

Recorded money evidence

settlements

Grain
One settlement record
Key
settlement_id

Review context

fraud_flags

Grain
One review record
Key
flag_id

customersaccountsone to manyAn account belongs to one customer.

accountstransactionsone to manyEvery transaction retains its account currency.

transactionsmerchantsmany to zero or oneMerchant is required for the flagship purchase population.

merchantsmerchant_termsone to many over timeValidity dates select exactly one effective term.

transactionssettlementsone to zero or oneA left join keeps missing evidence visible.

transactionsfraud_flagsone to zero or oneReview context can coexist with settlement flags.

Payments, the contract that applied to them, and the money that arrived stay in separate tables until the reconciliation models join them.

Four scripted closes, one cause each

One ordinary day and three incidents, each with its own cause and reaching merchants in several categories. The walkthrough follows the fee mismatch incident.

  1. Normal daily closeFood & Beverage · EUR

    No incident. The close still carries the everyday mix of exceptions any day has.

  2. Settlement partner outage / GBPEntertainment · GBP

    37 payments on this GBP close classify as late, most of them from the incident, spread across categories and led by Entertainment.

  3. Stale fee schedule / EURRetail · EUR

    47 payments on this EUR close classify as fee mismatch, most of them from the incident, spread across categories and led by Retail.

    Followed below
  4. Lost settlement file / CADRetail · CAD

    46 payments on this CAD close classify as missing, most of them from the incident, spread across categories and led by Retail.

Scripted incidents from data/scenarios.json, applied to generated traffic. None of them is a real incident.

Follow one close from symptom to payment

Three queries, each answering the question the last one raised. The chart stays beside its explanation while you read.

Baseline daily close

Did the close balance inside its own currency?

At the 2024-11-15 observation cut, the selected close was 310/326 matched. Coverage recovers, yet the exceptions stay in the final snapshot.

Query close_summaryModel mart_daily_close
Show the SQL
-- analytics_context.as_of_date = :investigation_as_of_date
SELECT
  close_date, currency, eligible_count, matched_count,
  coverage_rate, overdue_minor_units, fee_delta_minor_units
FROM mart_daily_close
WHERE close_date = :scenario_date
  AND currency = :currency
ORDER BY close_date, currency;
Coverage for the 12 Nov 2024 close in EUR

Same purchases, read again at later dates as settlements arrive.

Show the result table
Purchase closeRead onCurrencyEligibleMatchedCoverageOverdue valueFee delta
12 Nov 202412 Nov 2024EUR32600.00%€0.00€0.00
12 Nov 202415 Nov 2024EUR32631095.09%€0.00€20.10
12 Nov 202418 Nov 2024EUR32631195.40%€621.26€20.10
12 Nov 202410 Jan 2025EUR32631897.55%€621.26€20.10

Segment isolation

Which merchant segment explains the EUR gap?

Retail carries 13 of the 47 fee mismatches, then Electronics (12), Entertainment (8), Healthcare (5). The fee mismatches reach 8 of 8 categories, so this is one cause reaching many merchants, and no category is at fault. 7 of 8 categories also carry everyday exceptions of other kinds.

Query segment_isolationModel mart_category_health
Show the SQL
SELECT
  merchant_category, currency, eligible_count,
  exception_count, exception_rate, primary_reason,
  overdue_minor_units, fee_delta_minor_units
FROM mart_category_health
WHERE close_date = :scenario_date
  AND currency = :currency
ORDER BY exception_count DESC, eligible_count DESC, merchant_category;
Exceptions by merchant category, EUR
Retail17 of 47Fee Mismatch
Electronics13 of 27Fee Mismatch
Entertainment10 of 46Fee Mismatch
Food & Beverage7 of 86Missing
Healthcare6 of 30Fee Mismatch
Services6 of 30Late
Travel4 of 27Fee Mismatch
Utilities2 of 33Fee Mismatch

Bar length is the share of that category's payments with an exception.

Exception classification

What does operations look at first?

47 fee mismatches lead the close. The rest is the everyday mix any close carries: missing (2), currency mismatch (2), amount mismatch (4), late (8), disputed (2). Each payment keeps every flag that fired; the primary label only decides where it sits in the queue.

Query exception_queueModel mart_exception_queue
Show the SQL
SELECT
  payment_id, primary_reason, exception_reasons, currency,
  gross_minor_units, expected_settlement_date, actual_settlement_date
FROM mart_exception_queue
WHERE CAST(transaction_date AS DATE) = :scenario_date
  AND currency = :currency
ORDER BY priority_rank, gross_minor_units DESC, payment_id;
Reasons behind the 65 flags
Missing2€14.76
Currency mismatch2€148.84
Amount mismatch4€207.82
Fee mismatch47€4,912.00
Late8€606.50
Disputed2€187.93
  1. Missing
  2. Currency Mismatch
  3. Amount Mismatch
  4. Fee Mismatch
  5. Late
  6. Disputed

A payment can sit in several rows. The queue order above only sorts; it never drops a flag.

One payment, end to end

536062

Fee Mismatch
Completed
12 Nov 2024
Scope
Electronics in EUR
Gross
€637.36
Status
Completed
Contract
1 Oct 2024 to open ended
Fee rate
1.10%
Expected fee
€7.01
Recorded fee
€9.56
Deadline
3 days
Due
15 Nov 2024
Settled
13 Nov 2024
Primary label
Fee Mismatch

The recorded fee was EUR 9.56. The contract in force that day sets 110 bps, an expected fee of EUR 7.01. Amount and currency still match. Fee mismatch is the only flag.

Query payment_traceModel mart_payment_trace

What to do, and why the number holds

The finding turns into one action with an owner. The same SQL runs on DuckDB and PostgreSQL, and quality checks run before anything is published.

Finding

47 EUR payments on the 2024-11-12 close carry fee-mismatch exceptions, across 8 categories. Most belong to merchants repriced on 1 Oct whose new rate never reached the processor's fee table, and the rest are everyday cross-border fee pass-throughs. Amounts and currency still agree.

Action

Update the processor's fee table, recompute the fee on every repriced merchant's payments since the repricing, and export the filtered evidence for operations.

Decision
Fix the cause once, for every merchant it reached. Anything left over goes through the exception queue in its usual order.
Owner
Settlement operations
Watch
Settlement coverage
Both engines run the same SQL files. The site, the workbench and the MCP server only format what those models return.

12 of 12quality checks pass on this snapshot

Show every check
  • passSource currencies use the accepted ISO setaccepted_currencies
  • passEvery reconciled payment has scoring featuresanomaly_features_complete
  • passEvery currency screens all nine leading digitsbenford_digit_coverage
  • passDaily close is unique by date and currencydaily_close_unique
  • passDaily-close rows remain unique inside one currency partitiondaily_currency_grain
  • passEvery eligible purchase selects exactly one termeffective_term_join
  • passEligible purchases are unique at payment graineligible_purchase_unique
  • passMatched payments satisfy currency and gross identitymatched_monetary_identity
  • passOnly transfers are merchantlessmerchant_nullability
  • passEvery daily close has a control-limit screenquality_screens_populated
  • passReconciliation remains unique at payment grainreconciliation_unique
  • passReview resolution dates match resolution stateresolved_date_consistency
Show the query plan target
Query exception_queueModel mart_exception_queue
EXPLAIN ANALYZE
SELECT
  payment_id, primary_reason, exception_reasons, currency,
  gross_minor_units, expected_settlement_date, actual_settlement_date
FROM mart_exception_queue
WHERE CAST(transaction_date AS DATE) = DATE '2024-11-12'
  AND currency = 'EUR'
ORDER BY priority_rank, gross_minor_units DESC, payment_id;
  1. Filter completed merchant purchases at the expected-settlement grain.
  2. Resolve the one merchant term effective on each purchase date.
  3. Left join only settlement and review evidence visible at the as-of date.
  4. Classify independent flags, then apply deterministic queue precedence.

Reproduce

  1. python data/generate_data.py
  2. python scripts/generate_artifacts.py
  3. python scripts/check_sql_parity.py
  4. cd site && npm ci && npm run build

Limits

  • All records and scenarios are deterministic synthetic examples; they are not real incidents or business-impact estimates.
  • The snapshot demonstrates batch analysis, not streaming ingestion, predictive fraud, chargeback management, or regulatory compliance.
  • Money is compared only within its recorded currency. No foreign-exchange conversion or cross-currency total is produced.
  • PostgreSQL compatibility is checked separately by scripts/check_sql_parity.py; this payload does not claim a parity result.

Trace it yourself, or ask Claude

The analysis above ran once. The same query registry also powers a live workbench and a read-only MCP server, so you can check any payment in the four scripted closes.

Workbench

Open payment 536062 in the workbench

Filter the queue, open a payment and read the rule that flagged it. Review notes are session-only and never change the snapshot.

Trace this payment The free Streamlit Community Cloud app may need to wake after inactivity; that pause is normal for the public demo tier.

Ask Claude

Plain-English questions, answered through the same queries

https://settlement-gap-mcp.onrender.com/mcp

Three read-only tools: list_queries, run_query, trace_payment. None of them accepts SQL.

How to connect
Claude.ai and Claude Desktop
Settings, Connectors, Add custom connector, then paste the endpoint.
Claude Code
claude mcp add --transport http settlement-gap https://settlement-gap-mcp.onrender.com/mcp
Local, no network
cd mcp_server && uv run settlement-gap-mcp
ToolWhat it returns
list_queriesLists the registered queries and the parameters each accepts.
run_queryRuns one registered query with validated parameters, at most 200 rows.
trace_paymentExplains one payment: terms, settlement, and every rule with whether it fired.

Tested live with openai/gpt-oss-120b: through these tools it answered 12 of 15 questions correctly. Writing its own SQL against the raw tables, it got 1 of 31. How it was measured

The free server sleeps when idle, so the first call can take 30 to 60 seconds.

  1. Question

    Why was payment 536062 flagged?

  2. Tool call
    trace_payment({"payment_id": 536062})
  3. Result excerpt
    {
  "scenario": "stale_electronics_eur_fee",
  "as_of_date": "2025-01-10",
  "primary_reason": "fee_mismatch",
  "exception_reasons": ["fee_mismatch"],
  "days_overdue": 0,
  "settlement_status": "settled",
  "fee_delta_minor_units": 255
}
  4. Answer

    Payment 536062 is a EUR 637.36 Electronics purchase from the 2024-11-12 close. The settlement arrived on 2024-11-13, but the recorded fee was EUR 9.56 against EUR 7.01 under the 110 bps term, a EUR 2.55 difference, so fee_mismatch fired. No other rule applies.

One trace_payment call against this snapshot, with the answer written from the fields it returned. CI makes the same call on every push and fails if the result changes.
The server has no query logic of its own. It reaches the data only through the registry the workbench already uses, so Claude can only run queries that already exist.