# Merchant-Rollup Grid Playbook

A portable write-up of a specific fix applied to this app's Settlement MIS Details
grid, generalized so it can be replayed in a different application with a similar
grid. Hand this file to Claude in the other codebase along with the prompt at the
bottom — it's written so Claude re-derives the equivalent files/fields in *that*
app rather than blindly copy-pasting names from this one.

---

## The problem this solves

A grid shows one row per fine-grained item (e.g. `merchant × scheme × card_type`,
or any similar `parent × sub-dimension` grain) instead of one row per the entity
a human actually cares about (e.g. "what will this merchant receive"). Symptoms
that indicate this fix applies to your grid too:

- The viewer has to mentally sum several scattered rows to answer "what does
  entity X actually get/owe/total".
- The grid's totals don't include some adjacent adjustment/correction data
  (credits, debits, corrections) that legitimately changes the real total.
- Some displayed columns are always blank or `0.00` even though the record
  clearly isn't empty — often because the display code reads a *different*
  field than the one the generator actually populated (a serializer/field
  mismatch, not a data problem).
- Loading the grid pulls every child row for every parent eagerly, which is
  fine for a handful of test records and will not scale to production volume.

## Non-negotiable constraint — read this first

**Do not change the underlying item table's schema or grain.** Before writing
any code, grep for every other consumer of that table/grain (a payout
generator, a report dispatcher, a downstream export, anything with a foreign
key to it) and confirm the merchant-level view can be built **read-time-only**,
by aggregating the existing rows, not by restructuring them. In this app that
meant: zero changes to `settlement_mis_generator.ex`, the `SettlementMisItem`
schema, or `Context.list_settlement_mis_items/1` — `payout_generator.ex`,
`payout_item_breakdown.ex`, `mpr_dispatch_worker.ex`, and
`payout_transmission_worker.ex` all still read the original per-item grain,
untouched. If your app's downstream consumers can't tolerate the item grain
staying as-is, this playbook doesn't apply as written — that's a bigger
migration, not a display fix.

---

## Architecture (5 pieces)

1. **SQL-aggregated parent totals** — one query, `GROUP BY` the parent key,
   summing the amount columns in the database, not in application code. Never
   pull every child row into memory just to sum them in `Enum.group_by`/a loop
   — that's the part that doesn't scale.
2. **Lazy child loading** — the grid initially receives *only* parent rows
   (cheap: one row per parent). A parent's children are fetched only when the
   user expands it, and cached client-side so re-expanding is instant.
3. **Adjustment injection as synthetic rows** — if there's a side-table of
   corrections/adjustments keyed to the same parent, don't blend it invisibly
   into the total. Inject it as its own labeled row under the parent (e.g.
   "Adjustment (Credit)" / "Adjustment (Debit)") so the viewer can see *why*
   the parent's total differs from the raw sum of its children, not just a
   number that mysteriously doesn't match. Source this from one shared
   function, not a copy — if a payout/report generator elsewhere in the app
   computes "real" totals including the same adjustments, reuse *that* query
   so the grid can never drift out of sync with what actually gets paid/used.
4. **Manual expand/collapse, not a grid library's native "row grouping"
   feature** — see the landmines section below for exactly why, if your grid
   library is AG Grid specifically. The safe pattern: keep the full
   parent+children dataset in client-side memory, and on every expand/collapse
   toggle, recompute the *visible* row list from scratch and explicitly hand
   it back to the grid (`setGridOption("rowData", ...)` in AG Grid terms) —
   never rely on the grid's own filter/visibility mechanics to hide/show rows.
5. **Custom pagination by parent, not by row count** — if the grid's built-in
   pagination just counts flattened rows, expanding a parent can push some of
   its own children onto the next page, splitting one entity's data across
   pages. Paginate by parent count instead (e.g. 10 parents per page) and
   build the prev/next controls by hand.

---

## Step-by-step

### Step 1 — Confirm the grain and downstream consumers (read-only investigation)
Grep for every reader of the item-grain table. List them. Confirm none of them
need to change. This is the single most important step — skipping it is how
you accidentally break a payout run while fixing a display grid.

### Step 2 — Add a "parent totals" query
One query, aggregated in SQL. Reference implementation
(`apps/settlement_core/lib/settlement_core/context.ex`, `list_merchant_totals_for_mis/1`):

```elixir
def list_merchant_totals_for_mis(settlement_mis_id) do
  from(i in SettlementCore.SettlementMisItem,
    where: i.settlement_mis_id == ^settlement_mis_id,
    group_by: [i.merchant_mid, i.merchant_id],
    select: %{
      merchant_mid:          i.merchant_mid,
      merchant_id:           i.merchant_id,
      merchant_name:         fragment("MAX(?)", i.merchant_name),
      transaction_count:     sum(i.transaction_count),
      total_gross_amount:    sum(i.total_gross_amount),
      total_mdr_amount:      sum(i.total_mdr_amount),
      total_vat_amount:      sum(i.total_vat_amount),
      total_interchange_fee: sum(i.total_interchange_fee),
      total_net_payable:     sum(i.net_payable),
      schemes:               fragment("GROUP_CONCAT(DISTINCT ? SEPARATOR ', ')", i.scheme_name),
      card_types:            fragment("GROUP_CONCAT(DISTINCT ? SEPARATOR ', ')", i.card_type_code)
    }
  )
  |> Repo.all()
end
```
The `GROUP_CONCAT`-style columns (`schemes`, `card_types`) exist so text filters
typed against the collapsed parent row can still match on values that actually
live in its (not-yet-loaded) children.

### Step 3 — Add a "children for one parent" query
```elixir
def list_settlement_mis_items_for_merchant(settlement_mis_id, merchant_mid) do
  from(i in SettlementCore.SettlementMisItem,
    where: i.settlement_mis_id == ^settlement_mis_id and i.merchant_mid == ^merchant_mid,
    order_by: [asc: i.card_type_code]
  )
  |> Repo.all()
end
```

### Step 4 — Fix the serializer field mapping (check this even if you think it's fine)
The actual bug found in this app: the generator persisted only aggregate
fields (`total_gross_amount`, `total_mdr_amount`, ...) on each item row, but
the grid's serializer read the *singular* per-transaction fields
(`gross_amount`, `mdr_amount`, ...) — which were always `NULL` at this grain,
so the grid silently showed `0.00` everywhere despite the database having real
data. **Print/inspect one raw DB row for the table your grid reads, and
compare every field name in the serializer against what's actually
non-null.** This class of bug is invisible in code review because both field
names look equally plausible; only the data disproves the wrong one.

### Step 5 — Resolve any missing display-only field at read time, not by writing it back
If a display field (e.g. a name) isn't stored on the item row but is
resolvable via a lookup table by key, resolve it once at read time and don't
retrofit the generator to store it — that's a second migration you probably
don't need. Batch the lookup (one query for all needed keys, not N+1):
```elixir
def list_tid_masters_by_mids([]), do: []
def list_tid_masters_by_mids(mids) when is_list(mids) do
  from(t in SettlementCore.TidMaster, where: t.mid in ^mids) |> Repo.all()
end
```

### Step 6 — Share the adjustment query with whatever else computes "real" totals
```elixir
def list_approved_adjustments_by_date(settlement_date) do
  from(a in SettlementCore.MerchantAdjustment,
    where: a.adjustment_date == ^settlement_date,
    where: a.approval_status == "approved"
  )
  |> Repo.all()
  |> Enum.group_by(& &1.merchant_mid)
end
```
This exact function is called by both the grid's expand handler *and* the
payout generator elsewhere in this app — one source of truth, so the grid can
never show a different adjustment total than what actually gets paid.

### Step 7 — Frontend: parent rows only, lazy children, explicit visible-row recompute
Server sends only parent rows on initial load. On expand, fetch that parent's
children (cache the result client-side keyed by parent id) and recompute the
full visible row list:
```javascript
const computeVisibleRows = () => {
  const visible = [];
  for (const parent of currentPageParents()) {
    visible.push(parent);
    if (this.expandedMids.has(parent.merchant_mid)) {
      visible.push(...(this.loadedChildren.get(parent.merchant_mid) || [LOADING_PLACEHOLDER_ROW]));
    }
  }
  return visible;
};
// ...on every toggle:
this.grid.setGridOption("rowData", computeVisibleRows());
```
Click handling for the expand/collapse toggle must be bound at the grid level
(AG Grid: `onCellClicked`), never inside a per-row cell renderer — grid
libraries commonly recycle row DOM elements, so a listener attached inside a
cell renderer can end up bound to stale data after a re-render.

### Step 8 — Custom pagination by parent
```javascript
const PARENTS_PER_PAGE = 10;
const totalPages = () => Math.max(1, Math.ceil(this.parentRows.length / PARENTS_PER_PAGE));
const currentPageParents = () => {
  const start = this.currentPage * PARENTS_PER_PAGE;
  return this.parentRows.slice(start, start + PARENTS_PER_PAGE);
};
```
Build prev/next controls by hand; do not turn on the grid library's own
row-count-based pagination alongside this.

### Step 9 — Filter-driven auto-expand, with state preserved correctly
If your grid has text/column filters, a nice-to-have is: typing a filter that
matches something inside a collapsed parent's children should auto-expand that
parent. The naive version has two real bugs, both worth avoiding on purpose:
- Don't force-expand *every* parent whenever the filter is cleared (an empty
  filter technically "matches everything", which would fight your default
  collapsed state). Track manually-toggled expand/collapse state separately
  from filter-driven auto-expand, and restore the user's own choice when the
  filter clears.
- If you evaluate "does this row match the current filter" inside a callback
  that runs *during* the grid's own initial construction, don't reference
  anything the grid library only assigns *after* construction returns (e.g. a
  `this.grid` reference set post-`createGrid()`) — it'll throw or silently
  produce zero visible rows on first render. Track filter-active state in your
  own plain flag instead of depending on grid-internal references mid-build.

---

## Landmines already hit in this app (check if your stack shares them)

- **AG Grid Enterprise's native `rowGroup` feature**: avoided entirely in this
  app after it crashed with `TypeError: s.getDisplayedChildren is not a
  function`, traced to the vendored `ag-grid-community` and `ag-grid-enterprise`
  bundle files each containing two mixed internal version strings — an
  inconsistent vendoring, not a usage bug. If you're on AG Grid and vendoring
  the JS files locally (not via a clean npm-versioned install), check for this
  before reaching for `rowGroup`/native grouping; the manual approach in this
  playbook (steps 7–9) was adopted specifically to sidestep it, and turned out
  more reliable anyway (no grouping-library filter/redraw interplay to fight).
- **`aggFunc: "sum"` silently skipping string values**: if you serialize
  numeric amount fields as strings for display formatting, a grid library's
  built-in group-sum aggregation will typically skip anything that isn't a
  JS `number` — group totals render blank with no error. Serialize amounts as
  actual numbers for the grid; format them for display in a cell renderer
  instead, not by pre-stringifying the underlying value.
- **`domLayout: "autoHeight"` / percentage-height containers collapsing to
  0px**: giving a grid's container a `height: 100%` inside a flex/grid parent
  that itself has no definite (non-percentage) height resolves to zero height,
  and the grid silently renders nothing. Fix: give the grid container an
  explicit, ancestor-independent height (this app used `height: 60vh`), not a
  percentage.

---

## Testing checklist (adapt names to your app)

- [ ] Parent rows load fast even with a large number of parents (confirm the
      totals query is a single aggregated SQL query, not N+1 or an in-memory sum).
- [ ] Expanding a parent shows the correct children, matching a manual DB check.
- [ ] Re-expanding a previously-expanded parent doesn't re-fetch (check network
      calls, not just visual result).
- [ ] A parent with a real adjustment shows the adjustment as its own labeled
      row, and the parent's total reconciles to (sum of children) ± adjustments.
- [ ] A parent with **no** adjustment shows no extra rows (no clutter in the
      common case).
- [ ] Expanding/collapsing never splits across a page boundary.
- [ ] Filtering auto-expands matching parents; clearing the filter restores
      each parent to whatever the user last manually set (not force-collapsed,
      not force-expanded).
- [ ] Group-level sums in the grid show real numbers, not blank, for every
      amount column.
- [ ] Cross-check: sum of all parent-row totals equals the same grand total
      shown elsewhere in the app for the same record (e.g. a summary
      strip/header) — this is the single best regression check, since it
      would have caught the original serializer bug immediately.

---

## Prompt to paste into Claude in the other application

```
I have a grid in this app that shows one row per [describe your fine-grained
item, e.g. "invoice line × tax code"] instead of one row per [the entity users
actually care about, e.g. "invoice"]. I want the same fix documented in
MERCHANT_ROLLUP_GRID_PLAYBOOK.md (attached) applied here: roll it up to one
row per [entity], expandable to reveal the original line-item rows, without
changing the underlying item table's schema or grain.

Before writing any code:
1. Find this app's equivalent of the "item" table/schema and grep every other
   consumer of it (reports, downstream jobs, exports) — confirm none of them
   need the grain to change. Tell me what you find before proceeding.
2. Find the grid's current LiveView/controller/component and its serializer —
   check whether the fields it reads actually match what's populated in the
   database for a real record (the playbook's Step 4 describes a real bug
   class to watch for here).
3. Tell me what grid library this app uses and whether it's AG Grid — if so,
   check for the same vendoring/version landmine described in the playbook
   before considering native row-grouping.

Then propose a plan mapping each of the playbook's 9 steps to this app's
actual files, and implement it.
```