Payment Data Models
Predicting settlement outcomes before funds clear — using open banking data, behavioural analysis, and structured transaction history.
Payment Company (PayCo)'s challenge was to approve fast, low-cost payments while reducing the chance of a transaction failing later when cleared through the Bulk Electronic Clearing System (BECS). The solution combined real-time account data, transaction history, and risk scoring to predict whether a customer would have sufficient balance when the payment actually settled.
The second model focused on understanding the underlying open banking data — mapping APIs to database tables, identifying anomalies, and defining a usable data foundation for risk models and analytics. Together, the data anatomy model builds the trusted data foundation, and the clearance risk model uses that foundation to predict future balance sufficiency and payment success.
Clearance Risk Minimisation
Stop otherwise valid transactions from failing because the customer's balance changes before settlement.
The Problem
The clearance risk model addresses the settlement delay between approval and actual clearing, which can be anywhere from a few hours to several days depending on the settlement clearing window. A transaction approved now may fail later if the customer's balance is debited before settlement.
Settlement Clearing Windows
Maximum window lengths vary by day type. <6h medium · 6h+ high risk
| Window | Mon–Thurs | Friday–Monday | Weekday Public Holiday | 3 Day Weekend | 4 Day Weekend |
|---|---|---|---|---|---|
| 1 | — | — | 5h30 | — | — |
| 2 | — | — | 4h00 | — | — |
| 3 | — | — | 3h45 | — | — |
| 4 | 15h00 | 63h00 | 39h00 | 87h00 | 111h00 |
Transaction Flow vs. Declined Payment Risk
Authorised payments that deplete bank balances before settlement processing can cause a default.
Normal Transaction Flow
Timeline of a Declined Payment
Consequences of Declined Payments
- Retailer pays $25 decline fee or customer pays overdraw fee (~$25) and interest on balance
- Retailer loses the value of any goods/services already exchanged
- Platform's relationship with the merchant/s negatively impacted
Note: The settlement window can be anywhere from 3h45 to 111h00.
Core Idea: Expected Balance
The model estimates an expected balance at the time clearing occurs, not just the balance at the moment of payment initiation.
Expected Balance Formula
Expected Balance =
Current Available Balance
− Pending PayCo Payments
+ Expected Receipts
− Expected Future Spending
± Risk Adjustment Buffer
Where expected spend = h × s × v × w (h = hours remaining, s = mean spend/hour, v = seasonal weight, w = short-term trend weight).
Processing Flow
Payment Request
Customer initiates a transaction through PayCo.
Balance Check
Retrieve live account balance via open banking API.
Pending Payments
Sum all PayCo transactions already in-flight in the same settlement window.
Expected Receipts
Estimate likely incoming credits such as salary or recurring deposits.
Expected Spending
Forecast remaining spending for the settlement window using historical patterns.
Risk Modifiers
Adjust for unusual or suspicious behaviour — time-of-day, velocity, manipulation.
Decision Engine
Compare expected balance against transaction amount. Approve, divert, or block.
Processing Flow Detail
| Step | Input | Logic | Output |
|---|---|---|---|
| 1 | Payment request | Customer initiates transaction | New payment event |
| 2 | Account balance | Retrieve live balance via API | Available balance |
| 3 | Pending PayCo payments | Sum in-flight transactions | Pending outflows |
| 4 | Expected receipts | Estimate incoming credits | Expected inflows |
| 5 | Expected spending | Forecast spending for window | Expected future outflows |
| 6 | Risk modifiers | Adjust for anomalies | Risk-adjusted value |
| 7 | Decision engine | Compare expected balance vs amount | Approve / divert / block |
Three Risk Layers
Model Components
| Component | What It Does | Example Fields | Output |
|---|---|---|---|
| Expected Payments | Predicts ordinary future spend | hour, weekday, season, historical spend | Forecast spend |
| Unexpected Payments | Flags unusual activity | time, velocity, location, merchant type | Risk flag |
| Manipulation Model | Scores account trust and intent | account age, history, DD usage, declines | Risk multiplier |
| Decision Engine | Converts model output into action | expected balance, buffer, transaction amount | approve / divert / block |
Decision Outcomes
| Outcome | When It Happens | User Experience |
|---|---|---|
| ✅ Allow via BECS | Expected balance covers payment | Standard approval |
| ⚡ Route to RTP | Payment is risky but still valid | Faster settlement option |
| 🚫 Block | Expected balance is too low | Transaction declined |
Architecture
The architecture uses operational tables for balances, transactions, windows, payment requests, risk modifiers, and expected balances. Some calculations happen in batch; the final decision happens in real time at transaction request time.
Operational Database Schema
| Table | Purpose |
|---|---|
| transactionhistory | Stores transaction records from API calls for aggregation |
| transactionwindows | Aggregates spending by PayCo window, weekday, month, season |
| paymentrequests | Stores payment requests for pending-payment calculations |
| accountbalances | Stores historical balance snapshots |
| expectedbalances | Stores expected balance results and all input variables |
| risktimemods | Stores time-based risk modifiers |
| accountscores | Stores customer/account trust and manipulation risk scores |
| problematicpayments | Stores transactions needing special handling |
Data Anatomy & Data Model
Turning raw open banking APIs into a structured platform for risk models and analytics.
The Problem
Open banking APIs provide data in varying structures across different banks. The challenge was to map these raw API responses into a consistent, queryable database design that supports clearance-risk modelling, exploratory analytics, and future machine learning.
Core Idea: API-to-Database Mapping
Different APIs provide different slices of data — accounts, transactions, balances, customers, scheduled payments, direct debits, payees, and products. These are combined into account-level, customer-level, merchant-level, and transaction-level datasets.
Processing Flow
API Ingestion
Pull account, transaction, balance, customer, and payment data from Open Banking APIs.
Data Normalisation
Unpack JSON responses and standardise fields into a consistent internal format.
Database Storage
Store cleaned records in structured, canonical tables.
Aggregation
Build windows, balance snapshots, metrics, and history into feature tables.
Data Quality Audit
Detect anomalies and discrepancies across banks and data sources.
Analytics Output
Produce decision-ready datasets for risk models and business intelligence.
Processing Flow Detail
| Step | Source | Processing | Output |
|---|---|---|---|
| 1 | Open Banking APIs | Pull account, transaction, balance, customer data | Raw API data |
| 2 | Ingestion layer | Unpack JSON and standardise fields | Cleaned records |
| 3 | Database layer | Store in structured tables | Canonical data model |
| 4 | Aggregation layer | Build windows, balances, metrics, history | Feature tables |
| 5 | Audit layer | Detect anomalies and discrepancies | Data quality log |
| 6 | Analytics layer | Produce metrics and model inputs | Decision-ready dataset |
Core API Groups
| API Group | What It Provides | Notes |
|---|---|---|
| Accounts | Account details, balances, product context | Anchor entity for the model |
| Transactions | Merchant, amount, datetime, status | Primary behavioural source |
| Customers | Identity and demographic-linked data | Linked via arrangement ID |
| Direct Debits | Outbound recurring payment behaviour | Useful for trust and cash-flow inference |
| Scheduled Payments | Planned recurring outflows | Important for expected receipts/spend |
| Payees | Recipient details | Useful for non-scheduled payments |
| Products | Product constraints and eligibility | Useful for limits and account type analysis |
Key Derived Tables
| Table | Purpose |
|---|---|
| accountbalances | Historical balance snapshots |
| transactionhistory | Full transaction record store |
| transactionwindows | Aggregated spend by window and period |
| accounts | Core account master data |
| customers | Customer master data |
| payees | Payee and merchant attributes |
| scheduledpayments | Recurring outgoing commitments |
| directdebits | Direct debit patterns and recurring outflows |
Dynamic Customer Level Data
A selection of potential reporting metrics
Balances
- Average account balance
- Median account balance
- Max and min account balance
- % of year with zero or negative balances
- Average monthly inflow
- Average monthly outflow
Transactions
- Number of incoming transactions
- Number of outgoing transactions
- Counts of transaction types
- Average amount for direct debits
- Number of recurrent direct debits
- Total amount for recurrent direct debits
- Number of scheduled payments
- Total for recurrent scheduled payments
Payees
- Number of payees
- Number of recurrent payees
- List of payees, transaction volume and totals
- Average number of payees per customer*
- Average number of payees per account*
Sources
- Transaction Detail
- Scheduled Payments
- Payee Detail
- Direct Debits
Note
* Most of the above metrics are at the account level, but metrics can easily be constructed at the customer level if required.
Static Data
Account creation date, product type, ownership, customer identity, and other relatively fixed attributes. Helps determine account age, product type, and baseline trust.
Dynamic Data
Transaction frequency, inflow/outflow volumes, balance trends, recurring payment behaviour, and merchant relationships. Most likely to change a customer's risk profile over time.
Anomalies & Caveats
Banks vary in how they populate fields like execution datetime, merchant name, transaction detail, and payee data. The production model needs a robust anomaly list, fallback logic, and bank-by-bank validation to handle inconsistencies.
PayCo Platform Architecture
How data ingestion, modelling, and decisioning work together end-to-end.
End-to-End Flow
Layer 1 — Data Sources
Layer 2 — Data Anatomy Model
Transforms raw API responses into a consistent, queryable data platform.
Canonical Tables
- accounts
- customers
- transactionhistory
- directdebits
Feature Tables
- accountbalances
- transactionwindows
- scheduledpayments
- payees
Data Quality
- Anomaly detection
- Bank-by-bank validation
- Fallback logic
- Field consistency audit
Layer 3 — Clearance Risk Model
Predicts balance sufficiency at settlement time using expected balance calculations.
Risk Layers
- Expected payments model
- Unexpected payments model
- Manipulation detection
Calculations
- Current balance
- + Expected inflows
- − Known outflows
- − Risk buffer
Scoring
- Balance sufficiency check
- Settlement window timing
- Behavioural trust signals
- Historical default rate
Layer 4 — Payment Decision
✅ Allow via BECS
Expected balance covers payment
⚡ Route to RTP
Payment is risky but valid
🚫 Block
Expected balance too low
Layer 5 — Operational Infrastructure
Settlement outcomes are recorded and fed back into the model to continuously improve accuracy.
Monitoring
- Settlement success/failure tracking
- Default rate by settlement window
- Model accuracy over time
Optimisation
- Risk buffer calibration
- Threshold tuning per merchant
- Seasonal pattern adjustment
Cross-Cutting Concerns
Data Freshness
Open banking data must be ingested in near-real-time. Balance snapshots older than a few hours degrade prediction accuracy, especially for high-risk settlement windows (3h45 – 111h00).
Bank Variability
Each bank provides data differently — field naming, optional fields, timestamp formats, and transaction categorisation all vary. The data anatomy layer absorbs this complexity so risk models see a single consistent schema.
Settlement Window Timing
The gap between transaction approval and BECS settlement ranges from 3h45 to 111h00. Longer windows carry more risk as customers have more time to make other debits and withdrawals before posting.
Merchant Context
Different merchants have different risk profiles. High-value goods with delivery delays carry more exposure than instant digital services. Risk thresholds adapt accordingly.
Assumptions, Limitations & Next Steps
Assumptions
- Open banking data is available in real time
- Transaction history spans at least 90 days
- BECS settlement windows are predictable
Limitations
- Bank data quality varies significantly
- New accounts have limited history
- Seasonal models need calibration time
Next Steps
- ML-based spending prediction
- Real-time model retraining pipeline
- Cross-bank anomaly standardisation
Case study prepared for PayCo — a risk-aware payment platform built on open banking infrastructure.