Four Regulatory Reports, One Read Path: Transactions, Large-Value, Exceptions and Settlement

A payments platform is only as trustworthy as the reports it can produce when a regulator, an auditor, or your own risk team comes asking. KibiPay's reporting service answers four standing questions out of the box, and it answers all four the same disciplined way: read-only queries against a Postgres read-replica, never the live payments primary.

The four reports

The service exposes exactly four named reports, listed at GET /v1/reports and fetched at GET /v1/reports/{name}:

• transactions — an aggregate roll-up grouped by scheme, status and currency, with a count and summed amount per group. This is your day-to-day volume-and-value picture across every rail.
• large-value — a row-level population of payments at or above a threshold (default 10,000, adjustable per request), ordered by amount descending. This is the feed for threshold and structuring analysis.
• exceptions — the SAR (suspicious-activity) investigation candidates: every payment whose status landed in REJECTED, RETURNED, EXPIRED, PENDING_APPROVAL, RECALLED or REVERSED, newest first.
• settlement — settlement finality, counting and summing only SETTLED payments, grouped by scheme and currency.

One window, two formats

Every report takes the same shape of request: a date window via ?from=YYYY-MM-DD&to=YYYY-MM-DD (default: the previous day if omitted), an optional tenant filter, and a format of json or csv. Ask for CSV and you get a text/csv response with a sensible download filename like large-value-2026-08-01-to-2026-08-06.csv — the format a compliance analyst can open in a spreadsheet without a developer in the loop.

The JSON envelope is deliberately boring: {report, from, to, tenant, row_count, rows}. Decimals serialize as strings and timestamps as ISO-8601, so nothing is lost to floating-point rounding on the way out.

Portable SQL on purpose

Each report is a single hand-written SQL statement against the payments table, filtered on created_at >= :start AND created_at < :end. Crucially, that SQL is deliberately ANSI — no Postgres-only functions — so the exact same queries run against SQLite in the test suite. That means the report logic is unit-tested for real, not mocked, and the tests catch a broken GROUP BY before it ever reaches a regulator's inbox.

Why aggregate for some, rows for others

The split between aggregate and row-level reports is intentional. transactions and settlement answer "how much moved, and how much is truly final?" — questions that want totals, not individual records. large-value and exceptions answer "which specific payments do I need to look at?" — questions where you need the id, reference, tenant, amount and timestamp of each row so an investigator can pull the thread. Both row-level reports cap at 5,000 rows so a runaway window can't try to stream the whole ledger.

Fronted by the console, gated by scope

Operators never hit the service directly. The console back-end proxies these under /v1/admin/reporting/*, gated behind a REPORTING permission scope, so only authorized staff can pull them and every pull is an authenticated, logged action. The reporting service itself sits on the read replica with no write credentials at all — it physically cannot mutate a payment even if it wanted to.

Four questions a payments regulator always asks; four reports that answer them from one safe read path. For the deeper dives, see large-value threshold reporting and settlement-finality reporting by scheme and currency.

Windowed, tenant-aware, and testable

A few deliberate defaults make the suite pleasant to operate. Every report is windowed: pass a from and to, or omit them and get the previous day, so a quick pull needs no parameters at all. Every report accepts a tenant filter, defaulting to "all", so a platform operator can look across every tenant or narrow to one. And because the report SQL is written in portable ANSI rather than Postgres-specific dialect, the exact same queries run against SQLite in the test suite — which means the grouping logic behind the transactions and settlement roll-ups, and the filters behind large-value and exceptions, are genuinely unit-tested rather than mocked. That testability matters more than it sounds: a regulatory report that silently miscounts is worse than no report, and the only way to trust the numbers is to test the queries that produce them. The console layer adds the last mile of governance — every pull is an authenticated, scope-gated, logged action — so you always know who ran which report over which window.