# Implementation Plan — Transaction & Merchant Monitoring Reports

**Source requirement:** `docs/reports/reports.txt` (v1.54, ACI-inspired, 19 reports)
**Status:** **All nineteen specified reports built, plus #20.** Phases 0–7 complete — see §9–§17. Remaining work: retrieval intake paths, tracked in `TASKS_RETRIEVAL_INTAKE.md`.
**Revised:** 2026-08-24 — AG Grid / AG Charts adopted as the rendering layer (§4.5); phasing re-cut accordingly (§7).
**Date:** 2026-08-24
**Owning app:** `apps/reporting_core/` + `PlatformWeb.ReportsLive`

---

## 1. Summary

The product team's 19 reports were traced back to the actual tables in
`shukria_transactions` and `shukria_mms_new_local`. Result:

- **14 reports** run directly against data we already hold.
- **3 reports** are correct code that returns thin or empty results until an
  upstream feed improves (#2 deposits, #13/#14 merchant status).
- **2 reports** needed a source decision, both now resolved (§3.1, §3.2), as
  was the deposit definition behind #2 (§3.6).

Nothing in the requirement is unbuildable. One new register
(`retrieval_requests`) and one new MMS read path (`transaction_rules`) are
the only genuinely new data work.

---

## 2. Report → source mapping

| # | Report | Primary source | Notes |
|---|---|---|---|
| 1 | Gross Daily Sales Volume | `pos_transaction` (SALE, rc=`00`) + `transactions` (QR) | group by date, `s_mid`, currency |
| 2 | Avg Daily Deposit Volume | `payout_items.payout_amount` by `settlement_date` | 36 rows today; grows with payouts |
| 3 | Avg Ticket Size | derived from #1 | value / count |
| 4 | Avg Txns per Batch | `pos_transaction` grouped `(s_tid, s_tid_batchno, DATE)` | 548 batches / 6209 txns |
| 5 | Avg Batch Frequency | same grouping, `MIN/MAX(created_dateTime)` per batch | see §3.4 |
| 6 | Txns same PAN per day | `pos_transaction.masked_card_no` | see §3.7 |
| 7 | Txn → Settlement lag | `core_transactions.transaction_datetime` vs `switch_settled_date` | 134/202 rows have a settled date |
| 8 | Individual Txn Value | `pos_transaction` + QR + ecom, one row per txn | SSRM pilot — see §4.5 |
| 9 | Keyed Txn count/value | `pos_transaction.entry_mode LIKE '01%'` | codebook confirmed — §3.8 |
| 10 | Below Floor Limit | MMS `transaction_rules` `MIN_AMOUNT` | see §3.1 |
| 11 | Same Issuer BIN | `LEFT(masked_card_no,6)` + `scheme_bin_ranges` | 715 rows yield no BIN — §3.7 |
| 12 | Credit Refunds Deposited | `s_txn_type='REFUND'` + `core_transactions.refund_status` | |
| 13 | Inactive Merchant Txns | `tid_masters.status` + unknown-MID rule | see §3.3 |
| 14 | Terminated Merchant Txns | `tid_masters.status` | see §3.3 |
| 15 | Retrieval Requests & Chargebacks | `chargeback_cases` + **new** `retrieval_requests` | see §3.2 |
| 16 | Authorisation Requests | `pos_transaction WHERE mti='0100'` | 627 rows |
| 17 | Keyed Authorisations | `mti='0100'` + `entry_mode LIKE '01%'` | |
| 18 | Repeat Auths same amount/PAN | `hash_pan` + `total_amount` | |
| 19 | Declined Auth Requests | `mti='0100' AND response_code<>'00'` + `pos_failed_transaction` | |

---

## 3. Resolved design decisions

### 3.1 Floor limit (#10) — use MMS `transaction_rules`

`transaction_rules` in `shukria_mms_new_local` is the floor-limit source. It
holds 24 rules today, of which `MIN_AMOUNT` at scope `terminal` / `merchant` /
`store` is the floor limit for report #10.

```
rule_type: MIN_AMOUNT
scope:     enum('global','merchant','terminal','user','store')
params:    {"currency": "AED", "min_amount": "10"}
```

A transaction is **below floor limit** when its amount is less than the
`min_amount` of the highest-precedence enabled `MIN_AMOUNT` rule that applies
to its terminal/merchant, in the same currency.

**Resolution order:** `terminal` → `store` → `merchant` → `global`, then by
`priority` ascending. Rules with `enabled = 0` or `deleted_at IS NOT NULL` are
excluded.

**Four traps that must be handled — see §5.1 for detail:**

1. `transaction_rules` lives in a **different database** to `pos_transaction`.
   `PlatformCore.ShukriaMmsRepo` is a separate, read-only Ecto repo — there is
   no cross-repo join. Rules must be loaded into memory and applied in Elixir.
2. `params` JSON keys are **not standardised** (`max_amount` vs
   `max_total_amount`; `max_count`/`time_window_minutes` vs
   `count`/`window_seconds`). Parsing must be tolerant and log unknown shapes.
3. `terminal_id` is **format-inconsistent** — observed values include
   `S1234567`, `07418813` (real TIDs matching `pos_transaction.s_tid`) and
   `11`, `29325508` (internal PKs). Only rules whose `terminal_id` matches a
   known `s_tid` can be applied; the rest must be counted and surfaced, not
   silently dropped.
4. There are **two scoping mechanisms**: the inline
   `merchant_id`/`store_id`/`terminal_id` columns, and a separate
   `transaction_rule_scope` table (`rule_id`, `scope_type`, `scope_id`,
   `is_enabled`). Precedence between the two is unconfirmed — see §5.1.

**Build:** new `MerchantCore.TransactionRules` module (nothing in the Elixir
codebase reads this table today) exposing `list_amount_rules/0` and
`resolve_floor_limit/3`, following the read-only live-query pattern already
used by `MerchantCore.MccCatalog` and `MerchantCore.Locations`.

### 3.2 Retrieval requests (#15) — build a real register

Confirmed as required, with **both manual and API intake**. There is no
retrieval-request table today and `DisputeCore.CardScheme.MastercomClient`
exposes no retrieval endpoint (only `list_queues`, `get_claim_detail`,
`create_second_presentment`, `create_case_filing`, `create_fee`).

**New table `retrieval_requests`** in `shukria_transactions`, owned by
`dispute_core`, modelled on `chargeback_cases`:

```
id, request_number (unique), merchant_mid, merchant_id, core_transaction_id,
rrn, auth_number, transaction_amount, transaction_currency, transaction_date,
scheme_name, card_type_code, reason_code, reason_description,
scheme_case_reference, request_date, response_due_date,
status, fulfilled_at, fulfilment_method, document_ref,
source (manual | csv | api | mastercom), created_by, updated_by,
inserted_at, updated_at
```

**Status lifecycle:** `received → in_progress → fulfilled | expired | withdrawn`.
A retrieval request that later becomes a chargeback links forward via
`chargeback_cases.rrn` — do **not** model it as a chargeback status, they are
separate scheme events with separate SLAs.

**Three intake paths:**

- **Manual** — LiveView form at `/admin/chargebacks/retrieval-requests`,
  same shape as the existing manual dispute-case creation.
- **CSV** — extend the pattern in `DisputeCore.CsvImporter`. Do **not** add
  retrieval parsing into that module; create `DisputeCore.RetrievalImporter`
  reusing its `@column_aliases` normalisation approach.
- **API** — new `PlatformWeb.RetrievalRequestController` intake endpoint,
  plus a Mastercom fetch path when the scheme endpoint is confirmed
  (see §5.2 — the Mastercom retrieval API is not yet identified).

**ADR required:** dependency direction. Per
`docs/adr/0002-dependency-direction.md`, `dispute_core` has no umbrella
dependency on `settlement_core`, so linking a retrieval request to
`core_transactions` must use raw parameterised SQL, exactly as
`CsvImporter` does. This is deliberate — do not "clean it up" into an
`Ecto.Query`.

### 3.3 Merchant status (#13, #14) — unknown MIDs count as non-active

Confirmed rule: **a MID appearing in transactions but absent from
`tid_masters` is treated as non-active.**

Today that is **44 of 47 MIDs** in `pos_transaction`, so report #13 will be
large on first run. That is the intended behaviour — an unrecognised MID
transacting is exactly the exception these reports exist to surface.

Report split:

- **#13 Inactive** — `tid_masters.status IN ('suspended')` **OR** MID not
  present in `tid_masters` at all. The report must show *which* of the two
  applies per row (`Unknown MID` vs `Suspended`), because the operational
  response differs: one is a data-sync gap, the other is a business decision.
- **#14 Terminated** — `tid_masters.status = 'terminated'` only. Never
  includes unknown MIDs; termination is an explicit, recorded act.

`tid_masters` currently holds 14 rows, all `active` — #14 will legitimately
return zero until a merchant is terminated.

### 3.4 Batch reports (#4, #5) — derive from transaction grouping

Confirmed: **`closure_status = 'SHIFT_CLOSED'` is a marker only, not a
financial closure**, and `pos_shift_closure` (the purpose-built register with
`txn_count`/`net_amount`/`closed_at`) is **empty — 0 rows**.

Batches are therefore derived by grouping on `(s_tid, s_tid_batchno, DATE(created_dateTime))`.

- **#4** — count per derived batch, averaged over the filter window.
- **#5** — batch frequency from `MIN`/`MAX(created_dateTime)` per derived
  batch. This gives batch *span* and inter-batch gap, which is a close proxy
  for "how often batches occur daily", but is **not** the real close time.

Both reports must carry a visible note stating the figures are derived from
transaction grouping, not from a closure register. If the `pos_shift_closure`
feed is ever populated, both reports switch source with no UI change — this is
one of the reasons for the pluggable report behaviour in §4.

### 3.5 Currency — a first-class filter

Confirmed. `pos_transaction.currency_code` holds 784 AED (5484), 840 USD
(167), 356 INR (104), 554 NZD (87), NULL (266).

Rules:

- Currency is a **standard filter** on every report, alongside the six filters
  in the requirement doc.
- Every value-bearing report **groups by currency** when no currency filter is
  applied. Summing across currencies is never permitted.
- Currency is displayed as an ISO alpha code (AED/USD/INR/NZD), not a symbol.
  The requirement doc's `₹` examples are illustrative only — this platform is
  AED-primary.
- `NULL` currency rows are reported under an explicit `Unknown` bucket, not
  silently folded into AED.

### 3.6 Deposits (#2) — `core_transactions.net_settlement_amount`

Confirmed: a "deposit" is **money owed to the merchant**
(`core_transactions.net_settlement_amount`), not money the bank has actually
sent (`payout_items.payout_amount`). The two differ by adjustments, chargeback
recovery and rejected payouts.

This also makes reports #1, #2 and #3 a coherent set on one axis — gross taken,
net owed, and average ticket, all per merchant per day — which the payout
reading would not, since a payout batch covers many days of trading at once.

**Day axis:** `transaction_datetime`, matching #1 and #3. Not
`switch_settled_date` or `bank_transfer_date` — those answer "when was it
settled/paid", which is report #7's question, and using them here would put #2
on a different axis from the two reports it sits beside.

**Three data findings that shape the report:**

1. **`net_settlement_amount` is NULL on 59 of 202 rows** (143 populated). Those
   rows have entered settlement but not had MDR/VAT calculated. They must be
   reported as an explicit "not yet calculated" count, never coerced to zero —
   a zero deposit and an uncalculated one are different facts, and averaging
   the second as the first understates what is owed.
2. **`transaction_currency` holds both `"784"` and `"AED"`** in the same
   column, despite being declared alpha with `default: "AED"`. Already handled
   — `ReportingCore.Currency.normalise/1` maps both to `AED` — but it confirms
   §3.5's rule is needed on this table too, not just on the switch registers.
3. **`core_transactions` holds 202 rows against `pos_transaction`'s 6209.**
   Only transactions that have been synced into settlement appear, so #2
   measures a subset of what #1 measures. That is correct — money is only owed
   once it enters settlement — but the report must say so, or the gap between
   #1 and #2 will read as lost money rather than as pipeline lag.

Negative values are legitimate (`-150.00` on an unmatched row): refunds and
reversals net against the day. The report must not clamp them.

### 3.7 PAN and BIN handling (#6, #11, #18)

Confirmed by the product team: **the 6-digit BIN may be displayed**, including
in exported reports.

#### Grouping is on `masked_card_no`, not `hash_pan`

This **corrects the proposal in the original §5.4**, which said to group on
`hash_pan` and display `masked_card_no`. The data does not support it:

| Column | Populated | Distinct |
|---|---|---|
| `hash_pan` | **301 of 6260 rows (4.8%)** | 36 |
| `masked_card_no` | 6040 of 6260 (96.5%) | 165 |

Grouping on `hash_pan` would silently discard 92% of the register — for reports
whose entire purpose is counting repeat card activity, that is not a
conservative choice, it is a wrong answer that looks careful.

`masked_card_no` is safe to group on here: across every row where both columns
are present, **no masked value maps to more than one `hash_pan`**. It is a
BIN + last-four, so two cards from the same issuer sharing a last-four would
collide in principle; there are no such collisions in this data, and the
reports say what they group on so a collision would be interpretable rather
than invisible.

#### What can and cannot yield a BIN

Of 6260 POS rows: **5519 carry a readable 6-digit BIN**, 496 are fully masked
(`************4729`), and 219 have no PAN at all. The 715 that cannot yield a
BIN go into an explicit `Unknown BIN` bucket in report #11 — never dropped, or
the distribution would describe a register that does not exist.

#### Issuer attribution

`scheme_bin_ranges` (324 rows) is joined in to name the scheme behind each BIN.
The ranges are loaded into memory and matched in Elixir rather than joined in
SQL — 324 rows against ~101 distinct BINs is free, and it avoids the
cross-table string comparison that is a collation minefield in this schema
(errors 1253/1267). Same reasoning as §3.1's treatment of `transaction_rules`.

Most matching ranges are scheme-level ("Visa (all issuers)", "Mastercard
2-series"), not true issuer names, so the column is labelled **Scheme / Issuer
range** rather than claiming to identify a bank. Narrowest active range wins;
`draft` ranges are ignored.

### 3.8 `entry_mode` codebook (#9, #17) — and a data correction

Confirmed: `051`, `071`, `021`, `011` are standard ISO 8583 DE22 PAN entry
modes, and the filter reads the **first two digits**:

| Prefix | Meaning | Filter |
|---|---|---|
| `01` | manual keyed | **Keyed** |
| `02` | magstripe | Magstripe |
| `05` | chip | Chip |
| `07` | contactless chip | Contactless |

#### The shifted values were a recording fault, now corrected

`510`, `710` and `720` were not a third format — they were `051`, `071` and
`072` with the digits shifted one place left, from a 4-digit DE22 losing its
leading zero (`0510` through a numeric conversion becomes `510`; every other
terminal's reaches the `varchar(3)` column as `051`).

The evidence was terminal-shaped rather than statistical: **only two terminals
ever emitted them** — `90080001` and `90080005`, both on MID
`425590250000000`, neither registered in `pos_terminals` — and no other
terminal emitted them at all. All three shifted values carried EMV data on 100%
of rows, exactly like their correctly-formed twins.

Confirmed by the product team as a mistake and corrected by migration
`20260824000003`: 108 rows in `pos_transaction` and 31 in
`pos_failed_transaction`. The migration keys on the value rather than the
terminal, so it also repairs any later row arriving in the same shape before
the terminals are fixed at source. Its `down` deliberately does nothing — after
correction a converted `051` is indistinguishable from a native one, and
terminal `90080005` already had one genuine `051`.

**Still outstanding at source:** the two terminals should be fixed to send a
correct DE22, and registered in `pos_terminals`.

#### What remains outside the codebook

`001` (115 rows, 17 terminals) and `801` (4 rows, 3 terminals) were **not**
touched. Neither carries EMV data on any row, so neither fits the shifted-chip
pattern — they read as a genuine "entry mode unknown" rather than a formatting
fault, and guessing at them would corrupt data rather than repair it. With
`NULL` (125 rows) that leaves 3.9% of the register outside every entry-mode
filter, which the reports state rather than absorb.

### 3.9 `transaction_rules` scoping (#10) — merchant scope, inline column

Confirmed: **the inline `merchant_id` column is authoritative**, and only
merchant-scope rules are read today. Store and terminal scoping arrive with the
next version of the rules engine.

That settles both halves of the original question. The `transaction_rule_scope`
table is ignored entirely, and so are the ambiguous `terminal_id` values (`11`,
`29325508`) that prompted it — a half-specified terminal rule would produce a
floor limit nobody configured.

`ReportingCore.FloorLimits` is written so the precedence chain
(terminal → store → merchant → global) drops in when those scopes land, with no
change to report #10.

**The bridge is the constraint, not the rule.** `transaction_rules.merchant_id`
is an MMS `users.id`; the registers key on an acquiring MID. `tid_masters`
carries both and is the only mapping. Today that bridge is narrow enough to be
empty in practice: there is exactly one merchant-scope `MIN_AMOUNT` rule, and
its merchant (MMS user 1402, "AM's Cafe") has no `tid_masters` row because it
was never approved through `Mms.ApprovalSync`. Report #10 therefore examines
nothing — and says so, rather than rendering a blank screen that reads as "no
breaches".

---

## 4. Architecture

### 4.1 New app: `apps/reporting_core/`

These reports read across payments, settlement, merchant and disputes, so they
belong in no single existing domain app. Precedent: `ledger_core` was created
the same way for the same reason.

`reporting_core` reads from `PlatformCore.Repo` and `PlatformCore.ShukriaMmsRepo`
and depends on no other domain app, keeping `docs/adr/0002-dependency-direction.md`
intact. Where it needs a domain concept it re-queries the table directly rather
than importing the owning app's schema.

### 4.2 Report behaviour

19 reports must not mean 19 LiveViews. Each report is a module implementing:

```elixir
@callback meta() :: %{
  id: atom(),
  title: String.t(),
  description: String.t(),
  format: :table | :bar | :line | :pie,
  row_model: :client | :server,          # see §4.5
  filters: [atom()],
  columns: [column_spec()],
  caveat: String.t() | nil
}

@callback run(Filters.t(), keyword()) ::
  %{rows: [map()], totals: map(), chart: map() | nil, meta: map()}
```

`caveat` is what renders the derived-batch and unknown-MID notes described
above directly on the report, rather than leaving them in this document.

### 4.3 Shared pieces

- **`ReportingCore.Registry`** — lists reports, resolves by id, applies
  permissions.
- **`ReportingCore.Filters`** — struct for the seven filters: date range
  (daily/weekly/monthly/custom), merchant ID/name, issuer BIN, PAN,
  transaction type, channel, **currency**.
- **`PlatformWeb.ReportsLive.Index`** — catalogue at `/admin/reports`.
- **`PlatformWeb.ReportsLive.Show`** — one generic runner at
  `/admin/reports/:id`, rendering an AG Grid or an AG Chart from `meta.format`
  and `meta.row_model` — see §4.5.
- **`ReportsAgGridHook` / `ReportsChartHook`** — one hook each, driven entirely
  by the report's declared meta, so a new report needs no new JavaScript.
- **`PlatformWeb.ReportExportController`** — CSV / XLSX / PDF, reusing
  `PlatformWeb.EmailPdfGenerator` (the proven wkhtmltopdf shellout) and
  `send_download/2`. Binary must not go through the LiveView socket — see the
  note in `PlatformWeb.AnalyticsExportController`.

### 4.4 Navigation and permissions

New group **"Reports"** under the `:overview` module, beside the existing
Analytics item. New permission `reports.view`, plus `reports.export` gating
the download actions. Per-report permissions ride on `meta()` so a sensitive
report (#6, #11, #18 — all PAN-adjacent) can be gated separately.

### 4.5 Rendering: AG Grid and AG Charts

Reports render through **AG Grid** (tables) and **AG Charts** (charts) rather
than hand-written markup. Both are already vendored and wired in this
codebase — this is reuse, not a new dependency:

- `assets/vendor/ag-grid-community.min.js` + `ag-grid-enterprise.js`, **v32.1.0**.
  `assets/js/app.js` registers `RangeSelectionModule`, `RowGroupingModule`,
  `ExcelExportModule`, `ServerSideRowModelModule`, `SideBarModule` and
  `ColumnsToolPanelModule`.
- `assets/vendor/ag-charts-enterprise.js`, with light/dark handled by
  `assets/js/hooks/base/chartTheme.js` (`chartTheme/0`, `surfaceFill/0`,
  `labelColour/1`, `watchTheme/1`). Charts paint to a canvas and read their
  colours once at construction, so they rebuild on `tms:theme-changed` rather
  than restyling.
- Roughly 19 AG Grid hooks already exist, extending
  `assets/js/hooks/base/BaseAgGridHook.js`.

**Licence:** AG Grid Enterprise is licensed for production; development use is
under evaluation terms. Enterprise features are already load-bearing elsewhere
in the app (row grouping, Excel export), so the reports module deepens an
existing commitment rather than creating one. No `LicenseManager.setLicenseKey`
call exists in the assets today — that is a deployment task for whoever ships
to production, not a blocker for these phases.

#### The row model is a per-report decision

This is the part worth getting right, because it is easy to get backwards.

AG Grid's ability to handle 100K rows is about **rendering** — DOM
virtualisation — not about **transport**. It only helps once the rows are
already in the browser. Phase 0's server-side pagination already scales to any
table size precisely because the server never sends more than a page; moving
naively to client-side `rowData` would make scale *worse*, not better.

The existing code shows where that leads. `SettlementCore.CoreTransactionQueries`
carries:

```elixir
# Load up to 1000 rows at once; AG Grid handles client-side pagination.
@page_size 1000
```

Every one of the ~19 existing hooks uses client-side `rowData`, so they all cap
rather than scale. `ServerSideRowModelModule` is registered in `app.js` and
**used by nothing**.

So `Meta.row_model` decides it per report, because the reports genuinely differ:

| Kind | Reports | Result size | `row_model` |
|---|---|---|---|
| Aggregate — grouped by day/merchant/currency | #1–#5, #9, #11, #12, #15–#17, #19 | bounded by days × merchants × currencies | `:client` |
| Detail — one row per transaction | #6, #7, #8, #10, #13, #14, #18 | unbounded | `:server` |

Report #1 returns 77 rows over 30 days against today's data; even a year of
500 merchants across 4 currencies stays in the low thousands. Client-side is
right for it, and AG Grid's row grouping and pivoting are genuinely useful on
that shape. Report #8 (Individual Transaction Value) is one row per
transaction with no aggregation and is unbounded by construction — it is
unsafe as anything but `:server`.

#### What `:server` means concretely

A `:server` report is backed by an SSRM datasource: AG Grid requests blocks as
the user scrolls and sends its sort, filter and group state with each request,
so all three are executed by MySQL against the full set rather than by the
browser against a truncated one. `Report.run/2` already takes `:limit` and
`:offset`; SSRM adds sort and filter descriptors to that same call, which is
why the behaviour does not change shape.

Built once, in the phase the first `:server` report lands. Every later detail
report inherits it by declaring `row_model: :server`.

#### Exports stay server-side

`ExcelExportModule` is registered and AG Grid can export what the grid holds.
That is *not* a substitute for `PlatformWeb.ReportExportController`: the grid
can only export rows it has loaded, which for a `:server` report is whatever
was scrolled through. The controller runs the report with `all: true` and
exports the whole filtered set, which is the guarantee §4.3 exists to make.
Grid-side Excel export may be offered as a convenience, clearly labelled as
"what is on screen".

---

## 5. Open items — all resolved

Every question this plan raised has been answered by the product team and
recorded as a decision in §3:

| Was | Now |
|---|---|
| Floor-limit source | §3.1 — MMS `transaction_rules` |
| `transaction_rules` scoping precedence | §3.9 — inline `merchant_id`, merchant scope only |
| Retrieval-request source | §3.2 — new register; intake tracked separately |
| Mastercom retrieval endpoint | `TASKS_RETRIEVAL_INTAKE.md` T4 — the one piece still blocked |
| Merchant status rule | §3.3 — unknown MIDs count as non-active |
| Batch derivation | §3.4 — grouped from transactions |
| Currency handling | §3.5 — a filter, and never summed across |
| Deposit definition | §3.6 — `net_settlement_amount` |
| PAN / BIN handling | §3.7 — 6-digit BIN displayable, group on masked PAN |
| `entry_mode` codebook | §3.8 — DE22 prefix, and the shifted values corrected |

---

## 6. Performance

Every report currently scans `pos_transaction` live. Fine at 6209 rows; not
fine at production volume. Three things carry the load, in the order they
matter:

**1. Indexes — done in Phase 0.** `pos_transaction.s_mid` was **unindexed**
and there was no `(created_dateTime, s_mid)` composite, yet nearly every
report in this document groups on exactly that pair. Worse,
`transactions.inserted_at` (QR) was unindexed entirely, making every QR date
filter a full scan. Migration `20260824000001` adds six indexes covering all
three registers.

**2. The row model — §4.5.** A `:client` report's cost is bounded by its
grouping, so it is safe by construction. A `:server` report pushes sort,
filter and paging into MySQL, so its cost is bounded by the block size the
grid asks for rather than by the size of the result. Neither shape ships an
unbounded payload to the browser, which is the failure the existing
`@page_size 1000` caps were papering over.

**3. Rollups — deferred, deliberately.** The daily-aggregate reports (#1–#5)
recompute from the raw register on every run. At production volume they should
read a nightly rollup table instead. The `Report` behaviour exists partly so
this is a swap inside `run/2` with no change to the runner, the exports or the
UI. Not built yet because a rollup built before the reports are in real use
would be optimising a query pattern nobody has confirmed.

## 7. Phasing

| Phase | Scope | Reports | Blocked by |
|---|---|---|---|
| **0** | ✅ **Done** — app scaffold, behaviour, registry, `Filters`, currency handling, runner LiveView, export controller, menu + permissions, **indexes**. Pilot report #1 end-to-end on a plain server-paginated table. | 1 | — |
| **1** | ✅ **Done** — `Meta.row_model`; `ReportsAgGridHook` + `ReportsChartHook`; report #1 migrated to the grid; #2 (bar), #3, #12, #16 built. | 2, 3, 12, 16 | — |
| **2** | ✅ **Done** — `GridRequest`/`ServerQuery` + the AG Grid server-side datasource over the LiveView socket; #7 and #8 built on it. | 7, 8 | — |
| **3** | ✅ **Done** — `BinDirectory`; #6, #11, #18 built. | 6, 11, 18 | — |
| **4** | ✅ **Done** — `ResponseCodes`; #9, #17, #19 built; entry-mode data corrected. | 9, 17, 19 | — |
| **5** | ✅ **Done** — #4, #5 built. | 4, 5 | — |
| **6** | ✅ **Done** — `MerchantStatus`; #13, #14 built. | 13, 14 | — |
| **7a** | ✅ **Done** — `FloorLimits`; #10 built at merchant scope. | 10 | — |
| **7b** | ✅ **Report done** — `retrieval_requests` register + #15. Intake paths in `TASKS_RETRIEVAL_INTAKE.md`. | 15 | — |
| **8** | Scheduling & email delivery — reuse `TmsCore.Analytics.ScheduledReport` and its Oban worker | all | — |

Two changes from the original phasing, both from the AG Grid decision:

- **Phase 1 now leads with the rendering layer.** Building four more reports
  against a plain table and then migrating five is worse than paying for the
  grid once, with report #1 as the pilot that proves it.
- **SSRM was pulled forward to Phase 2**, ahead of the card and entry-mode
  reports. #7 and #8 are the reports that genuinely cannot be done safely
  client-side, and building the `:server` path early means every later detail
  report (#6, #10, #13, #14, #18) inherits it instead of being written twice.

Phases 3–6 are independent of every open item except where noted, so they can
proceed in parallel with the confirmations in §5.

---

## 8. Deliberate non-goals

- **No second transaction *search*.** `SettlementCore.TransactionSearchQueries`
  answers "where did this payment end up" — one identifier, six registers — and
  nothing here duplicates that; the runner cross-links to it instead.

  Report #8 (Individual Transaction Value) is a different question: every
  transaction in a filtered period, as an exportable list. It is built (Phase
  2) rather than cross-linked, and it is the SSRM pilot precisely because it is
  the first report that is unbounded by construction.
- **No new alerting.** These are reports. Thresholds and escalation belong in
  the existing `TmsCore.AlertsCore` engine via `MetricEvaluator`, the same
  extension point `PaymentsCore.TransactionAlertMetrics` uses.
- **No editable rules.** `transaction_rules` is read-only from here. It is
  owned and edited in MMS.
- **No currency conversion.** Reports group by currency; they never convert.
- **No hand-written chart code.** AG Charts is vendored and already themed for
  light/dark (§4.5). Hand-rolling SVG beside it would mean re-solving the
  canvas theme-swap problem `chartTheme.js` already solves.
- **No client-side grid for an unbounded report.** A detail report is
  `row_model: :server` or it does not ship. The `@page_size 1000` caps
  elsewhere in the app are the failure mode this rule exists to avoid.
- **No grid-side Excel export as the authoritative export.** The grid can only
  export what it has loaded; `ReportExportController` exports the filtered
  set.

---

## 9. Phase 0 — what was built

Delivered and verified against the live dev database and under test
(42 `reporting_core` tests, 14 `platform_web` tests, all passing).

### Modules

| File | Role |
|---|---|
| [`apps/reporting_core/`](../../apps/reporting_core/) | New umbrella app, depends on `platform_core` only |
| `reporting_core/report.ex` | The `Report` behaviour + `Meta`, `Column`, `Result` structs |
| `reporting_core/registry.ex` | All 19 reports; 1 live, 18 listed with their phase |
| `reporting_core/filters.ex` | The seven shared filters, parsed from URL params |
| `reporting_core/currency.ex` | ISO 4217 numeric ↔ alpha; the "never guess a currency" rule |
| `reporting_core/format.ex` | Cell formatting shared by screen, CSV and PDF |
| `reporting_core/reports/gross_daily_sales_volume.ex` | Report #1 |
| `PlatformWeb.ReportsLive.Index` | The catalogue |
| `PlatformWeb.ReportsLive.Show` | The generic runner — one LiveView for all 19 |
| `PlatformWeb.ReportExportController` | CSV export for any report |

### Migrations and seeds

- `20260824000001_add_reporting_indexes.exs` — the six indexes from §6.
- `20260824000002_add_shukria_mid_to_transactions.exs` — see the finding below.
- `priv/repo/seeds/reporting_permissions_seed.exs` — creates `reports.view`
  and `reports.export`, grants neither.

### Report #1 verified against production data

The report's totals were cross-checked against raw SQL over the last 30 days
and agree exactly: AED 1,145,923.66 across 964 transactions, being POS 912
(1,135,936.08) plus QR 52 (9,987.58); INR 37,565.00 across 34.

### Two findings that came out of the build

**1. `transactions.shukria_mid` had no migration.** The column exists in the
dev database and is read by both `SettlementCore.TransactionSearchQueries` and
report #1, but was added out of band — it was absent from the test database and
would be absent from any database built from migrations alone, failing with
`(1054) Unknown column`. Now covered by migration `20260824000002`, which
guards on `information_schema` because MySQL supports neither
`ADD COLUMN IF NOT EXISTS` nor `create_if_not_exists index`.

`pos_transaction.shift_no` has the same out-of-band history and is
deliberately left alone — nothing in the Elixir codebase reads it, so bringing
it under migration control is a separate decision.

**2. `String.to_existing_atom/1` is the wrong guard for a report id.** The
registry originally resolved a URL id that way — the right instinct (never mint
an atom from a URL), the wrong mechanism: whether `:gross_daily_sales_volume`
exists depends on whether its module has been *loaded*, since the atom enters
the table with the module. A cold-boot request straight to a bookmarked export
URL therefore redirected with "No such report" for a report that plainly
exists. `Registry.fetch/1` now compares strings against the loaded registry
and converts nothing.

### Deliberately not built in Phase 0

**Chart rendering.** `Meta.format` allows `:bar`, `:line` and `:pie`, and
report #1 already returns `Result.chart` data, but nothing renders it yet.
Every Phase 0 report is a table — the specification's first chart is report #2
(bar), so the renderer lands with it in Phase 1. A chart-format report falls
back to its table until then, which is why `Meta.columns` is required even for
one.

### Superseded by the §4.5 decision

Phase 0's table is a **plain server-paginated HTML table**, 50 rows a page.
That was the right thing to build first — it proved the behaviour, the
registry, the filters, the totals and the export end-to-end without taking a
front-end dependency on trust — but it is interim. Phase 1 migrates report #1
to AG Grid as the pilot for the rendering layer.

Nothing in `reporting_core` changes for that migration. `Meta` already declares
the columns and their types, `Result` already separates the page from the
filtered totals, and `Format` already renders a cell for screen and for CSV.
The migration is `Meta.row_model`, two hooks, and a template swap in
`ReportsLive.Show` — which is the payoff for having put the column contract in
the report rather than in the template.

---

## 10. Phase 1 — what was built

Five reports now live (#1, #2, #3, #12, #16), rendering through AG Grid and AG
Charts. 62 `reporting_core` tests and 18 `platform_web` tests pass.

### The shared layer that came out of it

Report #1 alone did not justify abstraction; four reports over the same
registers did. Three modules were extracted, and each of the four new reports
is now thin enough to read in one screen:

| Module | Role |
|---|---|
| `reporting_core/registers.ex` | The three registers and every filter rule — BIN prefix, LIKE escaping, entry-mode DE22 prefixes, the currency `:none` case, and the single `include_qr?/1` that decides the card-data exclusion |
| `reporting_core/totals.ex` | Currency-grouped totals, so no report hand-rolls a reduce that could sum AED into INR |
| `reporting_core/chart.ex` | Pivots rows into AG Charts' shape, zero-filled across the x-axis |

`Report.paginate/3` centralised the page/limit/offset handling so `total_rows`
is always the filtered set and never the page.

### Rendering

- `ReportsAgGridHook` builds its column definitions from `Meta.columns` sent as
  data, so a new report needs no new JavaScript. Row grouping is enabled
  (`rowGroupPanelShow: "always"`) — an operator can regroup a daily breakdown
  by merchant or currency without a second report.
- `ReportsChartHook` renders `Result.chart` as-is. The pivot is done in Elixir
  so the hook stays generic.
- Both use `ag-theme-alpine`, **not** quartz: dark mode in this app is CSS
  overrides on `.theme-dark .ag-theme-alpine` in `app.css`, so any other theme
  class renders unthemed in dark mode.
- Values go to the grid **raw**, not formatted. AG Grid must sort and filter on
  numbers — a column of `"1,234.00"` strings sorts lexically, putting 9.00
  after 1,234.00. The grid formats for display; `ReportingCore.Format` formats
  the same values for CSV. That duplication is deliberate and both sides carry
  a note pointing at the other.

### The client row cap

`Report.client_row_cap/0` is 5000. The runner asks for that many, and
`total_rows` is the true count, so an over-cap run renders the cap with a
visible banner telling the user to narrow the filters or export — the export is
uncapped. Truncating silently would be the worse failure: a report quietly
showing 5000 of 8000 rows is wrong in a way nobody can see.

### Verified against production data

Every figure cross-checked against raw SQL:

| Report | Result | Check |
|---|---|---|
| #2 Deposits | 197 txns, net 60,578.48, 59 uncalculated | exact |
| #16 Authorisations | 627 requests, 2,888,153.73 | POS 623 + E-Commerce 4 |
| #1, #3, #12 | same population as #1's verified figures | exact |

### Two findings

**1. MySQL returns `SUM(CASE ... END)` as a Decimal, not an integer.** Report
#2's "not yet calculated" count raised
`ArithmeticError: Decimal.new("7") + 0` the first time it ran against real
data, because `Enum.sum/1` was applied to it. Every conditional count now goes
through `Registers.to_integer/1`.

**2. The UNKNOWN currency bucket earned itself.** Over the full window, report
#1 shows 211 transactions worth **110,000,011,886.69** under `UNKNOWN` — real
rows in `pos_transaction` with a NULL currency and `total_amount` of
`9999999999.99`, from test merchant `123451234512345`. §3.5's rule that a
missing currency is never folded into AED is what made that visible instead of
adding 110 billion of junk to the AED total. The data itself wants cleaning up;
that is a separate question from the report, which is behaving correctly.

---

## 11. Phase 2 — what was built

Reports #7 and #8 are live on AG Grid's **server-side row model**. Seven of
nineteen reports now built. 100 `reporting_core` tests and 21 `platform_web`
tests pass.

### The server-side path

| Module | Role |
|---|---|
| `reporting_core/grid_request.ex` | Validates AG Grid's request against the report's declared `Meta.columns` — **the security boundary** |
| `reporting_core/server_query.ex` | Turns a validated request into Ecto `where`/`order_by`/`limit`, plus the separate count |
| `reports/settlement_lag.ex` | #7 |
| `reports/individual_transaction_value.ex` | #8 — a raw-SQL `UNION ALL` across all three registers |
| `ReportsAgGridHook` | Registers a `serverSideDatasource` when `row_model: :server` |

**Transport is the LiveView socket**, not a JSON endpoint: `pushEvent` with a
reply, handled by `handle_event("reports:rows", …)`. The LiveView already holds
the report's filters and the user's session, so a separate endpoint would have
to re-parse the filters and re-check the permission — two places to disagree
about what a user may see.

### Why validation is not `String.to_existing_atom/1`

AG Grid sends the column to sort and the columns to filter as strings chosen by
the browser, and they end up in an `ORDER BY` and a `WHERE`. `GridRequest`
resolves every `colId` against the report's own declared columns and drops
anything else — it never converts input to an atom at all. As §9 records,
whether an atom exists depends on what has been loaded, which is not a security
property.

Covered by tests: a `colId` of `"amount; DROP TABLE pos_transaction--"` is
dropped, an undeclared filter column is dropped, an unknown operator is dropped
rather than guessed, and a request for 5,000,000 rows clamps to 500.

### Report #8 is raw SQL, deliberately

The three registers name the same business fields differently, so the union has
to alias them, and Ecto cannot `UNION ALL` dissimilar sources and then sort and
page the *combined* result. Paging outside SQL would defeat the row model.

The union was tested against the real schema before being built on: it pages
and sorts cleanly with no collation error, which is not a given here. String
columns are read, never compared against each other, which is what keeps it
clean. Every value is a bound parameter; the only interpolated text is the
ORDER BY column, resolved through a literal map keyed by an atom the report
declared.

The ORDER BY always ends `u.ts DESC, u.row_id DESC`. Without a stable
tiebreaker MySQL may order rows differently between blocks, so a scrolling grid
repeats and skips rows — covered by a test that pages ten rows in three blocks
and asserts each appears exactly once.

### Verified against production data

| Report | Result | Check |
|---|---|---|
| #7 lag | 131 settled, 115 same-day | exact |
| #8 values | 7,415 rows | POS 6,178 + ecom 4 + QR 1,233 |

### Two findings

**1. A declared column is not always a real column.** Sorting #7 on `lag_days`
raised `Unknown column 'c0.lag_days' in 'order clause'` — it is a `DATEDIFF`,
not a column. `ServerQuery.paginate/4` now takes a `:sort_expr` resolver so a
report can map a key to an `Ecto.Query.dynamic`. The resolver is the report's
own code, so a computed sort stays as safe as a field sort.

**2. The settlement data contains impossible lags.** Report #7 shows a fastest
lag of **-653 days** — transactions whose `switch_settled_date` precedes their
`transaction_datetime`. It drags the average lag to -51.2 days, making the
headline useless. The report does not clamp them: it counts them and says so in
a note telling the operator to sort ascending to find them. **This is a data
defect in `core_transactions.switch_settled_date` worth investigating** —
separate from the report, which is behaving correctly.

---

## 12. Phase 3 — what was built

Reports #6, #11 and #18 — the card-behaviour set. **Ten of nineteen reports now
live.** 125 `reporting_core` tests and 21 `platform_web` tests pass.

| Module | Role |
|---|---|
| `reporting_core/bin_directory.ex` | Resolves a 6-digit BIN to its scheme/issuer range from `scheme_bin_ranges`, matched in memory |
| `reports/same_pan_per_day.ex` | #6 |
| `reports/issuer_bin_distribution.ex` | #11 (pie) |
| `reports/repeat_authorisations.ex` | #18 |

### The §3.7 correction

The original §5.4 proposed grouping on `hash_pan`. **The data contradicted it**
and the plan was corrected before building: `hash_pan` is populated on 301 of
6260 rows (4.8%), so grouping on it would have discarded 92% of the register
for three reports whose entire purpose is counting repeat card activity.
`masked_card_no` covers 96.5%, and no masked value maps to more than one hash
where both are present.

### Interpretation calls, stated on the reports

- **#18 reads "same amount *or* PAN" as "same amount *and* PAN."** Taken
  literally, *or* means every transaction sharing a PAN with any other (which
  is report #6) unioned with every transaction sharing an amount with any other
  (at 200.00 on a test estate, most of the register). Neither is duplicate
  detection. The caveat says so on the page, and cross-links to #6.
- **#6 applies no minimum-count threshold.** A threshold would bake an
  analyst's judgement into the data layer; the grid filters instead.
  #18 *does* apply `HAVING COUNT(*) > 1`, because a repeat of one is not a
  repeat — definitional, not a judgement.
- **#11 caps the pie at 8 slices plus "Others."** 99 BINs is a colour wheel,
  not a distribution — and "Others" is what the requirement's own example
  shows. The table below still lists every BIN.

### Three data findings

**1. One card is recorded under several masking formats.** The same card
appears as `************4729` (216 rows), `5186XXXXXXXX4729` (25) and
`518615XXXXXX4729` (51). Grouping on the masked number therefore splits one
card across three rows and understates its repeat count.

They are **not** merged — two cards from one issuer can genuinely share a last
four, so merging on it would invent activity, the worse error. Instead
`Registers.mask_shape_note/2` detects the condition and both #6 and #18 report
it, so a count that looks low can be recognised as split. **Worth fixing at
source**: the switch should mask consistently.

**2. 575 of 627 authorisation rows carry no merchant ID.** `s_mid` is NULL on
most MTI 0100 messages, so #18's merchant column is genuinely 0 rather than
miscounted. Said out loud in a note, because a "0 merchants" column otherwise
reads as a bug.

**3. 12% of transactions have no readable BIN.** 496 rows are fully masked and
219 have no PAN. They form an explicit `Unknown BIN` slice rather than being
dropped — the size of that slice measures how much traffic arrives without a
readable card number, which is itself the finding.

### A test-hygiene note

`scheme_bin_ranges` is reference data seeded per environment: 324 rows in dev,
**zero in the test database**. The first version of these tests asserted
against the dev seed and passed only on this machine. They now insert their own
ranges — including the `scheme_masters` parent row, since `scheme_id` is NOT
NULL with a foreign key.

---

## 13. Phase 4 — what was built

Reports #9, #17 and #19. **Thirteen of nineteen live.** 144 `reporting_core`
tests and 21 `platform_web` tests pass.

| Module | Role |
|---|---|
| `reporting_core/response_codes.ex` | Names an ISO 8583 response code from `acquirer_response` (52 rows, matched in memory) |
| `reports/key_entered_transactions.ex` | #9 |
| `reports/keyed_authorisations.ex` | #17 |
| `reports/declined_authorisations.ex` | #19 |

### The data correction came first

Migration `20260824000003` repaired the shifted entry modes described in §3.8
before these reports were built — 108 rows in `pos_transaction`, 31 in
`pos_failed_transaction`. Post-correction distribution:

| entry_mode | before | after |
|---|---:|---:|
| `051` chip | 3,106 | **3,173** |
| `071` contactless | 1,613 | **1,622** |
| `072` contactless+PIN | 20 | **52** |
| `510`/`710`/`720` | 108 | **0** |

`011` keyed is unchanged at 352 — none of the shifted values were keyed, so the
correction moved no row into or out of report #9's population.

### Three interpretation calls

- **#9 carries the keyed *share*, not just the count.** Forty keyed
  transactions is unremarkable for a hotel and alarming for a supermarket, so
  each row also carries that merchant's total for the same day. The denominator
  is queried separately — computed from the keyed rows alone it would always be
  100%.
- **#17 exists because the intersection is the signal.** A keyed *sale* is a
  liability question; a keyed *authorisation* is a probing one. #9 and #16 each
  show one axis; only their intersection shows card testing.
- **#19 never sums issuer declines with switch failures.** A decline is the
  issuer's decision; a failure is ours and no issuer saw it. One number would
  let neither team act on it.

### Two findings

**1. #17 is legitimately empty.** There are **zero** keyed authorisations in
this estate — authorisations arrive as chip (586), magstripe (39) and
contactless (2), none keyed. The report says so in its caveat so an empty
result reads as a finding rather than a broken page.

**2. Switch failures carry a response code that looks like a decline reason.**
23 of the 24 never-answered authorisations say `96` — SYSTEM MALFUNCTION.
That is the switch recording its own giving-up, not an issuer refusing.
Treating it as a decline reason would attribute a platform outage to the card
issuers, which is exactly why #19 keeps the two registers in separate columns.
An earlier draft of that module claimed these rows had no response code at all;
the data disproved it and the moduledoc was corrected.

### Test-hygiene, again

`acquirer_response` is reference data: 52 rows in dev, **zero in the test
database** — the same trap `scheme_bin_ranges` sprang in Phase 3. The tests now
insert the codes they need. Worth remembering for every future report that
reads a reference table.

---

## 14. Phases 5 and 6 — what was built

Reports #4, #5 (batch) and #13, #14 (merchant status). **Seventeen of nineteen
live.** 168 `reporting_core` tests and 21 `platform_web` tests pass.

| Module | Role |
|---|---|
| `reports/transactions_per_batch.ex` | #4 — derives batches, and owns the definition #5 reuses |
| `reports/batch_frequency.ex` | #5 — built on #4's rows, so the two cannot disagree about what a batch is |
| `reporting_core/merchant_status.ex` | Classifies a MID against `tid_masters` (14 rows, loaded not joined) |
| `reports/merchant_status_transactions.ex` | Shared query for #13 and #14 |
| `reports/inactive_merchant_transactions.ex` | #13 |
| `reports/terminated_merchant_transactions.ex` | #14 |

### The batch key, and a bug it caught

A batch is `(s_tid, s_tid_batchno, DATE(created_dateTime))` — §3.4. The date is
part of the key because batch numbers recycle: `acquirer_terminal_batch` rolls
`current_batch` back to 1 at `max_batch`.

The first implementation also grouped by merchant and currency. That split any
batch spanning either into several rows — **11 of them in this register** —
reporting 540 batches where there are 529 and an average of 8.65 where it is
8.83. Caught by cross-checking against SQL before the tests were written, and
now pinned by a test that asserts a two-currency batch stays one batch.

**Mixed-currency batches report no value.** 10 batches contain two currencies;
adding across them is what §3.5 forbids, so the value cell is blank and a note
says how many. The transaction *count* is still exact — counting is
currency-agnostic in a way that adding is not.

### #5 builds on #4 rather than re-deriving

Two reports that disagree about what a batch is would be worse than one report,
so #5 consumes #4's rows. Its interval is measured between consecutive batches'
**last transactions**, standing in for a close time no register records — the
caveat says so on the page.

A single-batch day reports **no** interval, not a zero one: zero would drag
every average towards "batches constantly". 358 of 426 terminal-days are
single-batch, so this is the common case, not an edge one.

### #13 and #14 differ on purpose

Both read the same rows; they differ only in which classifications they accept,
and that difference is the whole point:

- **#13** takes `suspended` **and** `unknown` — the rule the product team set
  (§3.3). Against current data that is 5,994 transactions across 45 MIDs,
  because only merchants approved through `Mms.ApprovalSync` get a
  `tid_masters` row. The Status column says which of the two applies on every
  row, and a note says an unknown MID may be a sync gap rather than a business
  decision.
- **#14** takes `terminated` only, **never** `unknown`. Termination is an
  explicit recorded act; inferring it from a missing row would accuse a
  merchant of something nobody decided — on the one report whose purpose is to
  escalate.

**#14 currently returns nothing, and that is correct**: all 14 `tid_masters`
rows are `active`. Its caveat says empty is the expected result, so it reads as
"the check ran and found nothing" rather than "nothing is being checked".

### One more consistency fix

`average_gap_hours` was produced two ways — `Float.round/2` on the row,
`Totals.divide/2` in the summary — giving `4.0` in one place and `4.00` in the
other for the same figure. Two spellings of one number in a single report reads
as two different numbers. Both now round to a fixed scale.

---

## 15. Phase 7 — what was built

Reports #10 and #15. **All nineteen reports in `docs/reports/reports.txt` are
now built.** 189 `reporting_core` tests and 21 `platform_web` tests pass.

| Module | Role |
|---|---|
| `reporting_core/floor_limits.ex` | Merchant floor limits from MMS `transaction_rules`, bridged to a MID via `tid_masters` |
| `reports/below_floor_limit.ex` | #10 |
| `20260824000004_create_retrieval_requests.exs` | The retrieval register |
| `dispute_core/retrieval_request.ex` | Its schema, lifecycle and `overdue?/1` |
| `reports/retrievals_and_chargebacks.ex` | #15 |

### Both reports are usually empty — and say why

This is the theme of Phase 7. #10 examines no merchants because the only
floor-limit rule belongs to a merchant with no TID Master record; #15's
retrieval half is empty because nothing writes to the register yet.

Neither renders a blank screen. #10 reports how many merchants were examined
and names the unresolvable rule; #15 says the register is fed by CSV or the
intake API and nothing is reaching it. **A report that is empty because nothing
was checked must not look like one that is empty because nothing was wrong.**

### Crossing two databases without a join

`transaction_rules` is in `shukria_mms_new_local`, the transactions are in
`shukria_transactions`, and they are separate Ecto repos — so there is no join.
The 24 rules are loaded into memory and matched in Elixir, the same approach as
`BinDirectory`, `ResponseCodes` and `MerchantStatus`.

This also required the MMS **test** database to gain the table: it is a
structure-only clone and `transaction_rules` was not in it. Cloned, and
`ReportingCore.DataCase` now checks out `ShukriaMmsRepo` and seeds it through
raw SQL — `read_only: true` means `insert_all/2` is not defined at all, and a
write through `PlatformCore.Repo` would be invisible to a read through the
other repo's separate sandboxed transaction.

### A grouping bug, caught the same way as Phase 5's

Report #15 first grouped by merchant as well as month, producing **99,490
rows** — `chargeback_cases` carries 95,246 distinct MIDs of Mastercom sandbox
traffic. The requirement's own example is month-level; merchant is a filter,
not a dimension. Fixed to 23 rows.

The same fix surfaced a second error: `chargeback_rate` was computed against
`max(retrievals, 1)`, so a month with 2 chargebacks and no retrievals read as
**200%**. A ratio with a zero denominator is not a ratio — it is now `nil`.

### Not built: retrieval intake

The register has no writer. CSV import, the intake API, the manual-entry UI and
the Mastercom fetch are specified in **`TASKS_RETRIEVAL_INTAKE.md`**, with the
Mastercom endpoint identified as the only piece still externally blocked.

---

## 16. `pos_failed_transaction` — what it actually holds

Checked at the product team's prompt, after report #19 was built. Two
corrections came out of it.

### The register

**1,161 rows**, 2026-04-11 to 2026-08-24, 28 MIDs, 41 terminals.

| MTI | rows | share |
|---|---:|---:|
| `0200` financial | 740 | 63.7% |
| `0220` financial advice | 322 | 27.7% |
| `(NULL)` | 75 | 6.5% |
| **`0100` authorisation** | **24** | **2.1%** |

By transaction type: SALE 656, REFUND 328, VOID_SALE 78, NULL 75, PREAUTH 17,
BALANCE_INQUIRY 7.

By response code: `96` "Manual cleanup invoked" **896 rows (77%)**, NULL 151,
`96` with no message 29, then genuine ISO decline codes in ones and tens —
`63`, `43`, `59`, `04`, `67`, `69`, `07`, `65`, `91`.

### Correction 1 — "no switch answer" was wrong

Report #19 labelled these rows *"POS · no switch answer"* and described them as
requests that timed out. They are not. All 24 authorisation rows are
`96 / "Manual cleanup invoked"` (16 PREAUTH, 7 BALANCE_INQUIRY) or a NULL code
— an **operational sweep of stuck transactions**, not the switch giving up.

The label is now the neutral *"POS · failed register"*, and the Reason column
carries the register's own explanation. What has not changed is that these stay
out of the issuer-decline count: however they failed, no issuer refused them.

### Correction 2 — the register's own message beats the code book

`acquirer_response` maps code `96` to "SYSTEM MALFUNCTION", which is what an
*issuer* means by it. The register's own `response_message` says "Manual
cleanup invoked". Report #19 was overriding the row with the book and reporting
an operator's housekeeping as a platform outage.

`ReportingCore.Reports.DeclinedAuthorisations` now prefers
`response_message` whenever the row has one and falls back to the code book
otherwise. The register turns out to be more specific than the book in general
— "Invalid Merchant" and "Checksum validation failed: CHECKSUM_MISMATCH" rather
than the book's generic wording.

### A gap in the specification, not the implementation

**1,137 of the 1,161 rows are failed *financial* messages** — 656 SALE, 328
REFUND, 78 VOID_SALE — and **no report in this set surfaces them.** Report #19
is scoped to authorisations because that is what §19 of the requirement asks
for, and none of the other eighteen asks for failed sales.

A merchant whose sales are failing had no report that showed it. Raised with
the product team, approved, and built as **report #20** — see §17. Not folded
into #19, which would then answer two questions and neither cleanly.

---

## 17. Report #20 — Failed Transactions

The first report beyond the specification's nineteen, added to close the gap
§16 found: `pos_failed_transaction` holds 1,161 rows and only 24 are
authorisations, so 1,137 failed sales, refunds and voids were surfaced nowhere.

`reporting_core/reports/failed_transactions.ex`. 206 `reporting_core` tests and
21 `platform_web` tests pass.

### Scope

The **whole** register — one row per day, merchant, failure reason and
currency. Against current data: 244 rows covering all 1,161 failures, 15
distinct reasons, 28 merchants, 81 days.

| message_type | failures |
|---|---:|
| Financial (`0200`/`0220`) | 1,062 |
| Authorisation (`0100`) | 24 |
| Unknown (NULL MTI) | 75 |

### Three decisions

**It is not #19 widened.** #19 answers "which authorisations did issuers
decline, and why"; its approved/declined split stops meaning anything once
never-completed financial messages are mixed in. Two reports, each answering
one question.

**Authorisations are included, not excluded.** Leaving them out would create a
blind spot — a failed PREAUTH visible in no register view. They appear in both
reports through different lenses, and a `message_type` column plus a note make
that visible rather than surprising.

**The reason is the register's own message.** Same rule as #19: `96` carries
"Manual cleanup invoked" on 896 rows, while the code book maps it to "SYSTEM
MALFUNCTION". The book's meaning is what an *issuer* means by the code.

### What it says first

**896 of 1,161 failures — 77% — are an operational cleanup marker, not a
payment anyone refused.** The report leads with that, because a "failed
transactions" count that is three-quarters housekeeping would otherwise be read
as a platform problem.

### One number deliberately absent

There is no terminal total. The per-row count is distinct terminals *within
that group*, and grouped data cannot be combined into a distinct count across
the report. A max presented as a total would read as "3 terminals affected"
when the true figure is not derivable — so the column carries the signal and
the summary stays silent.