# MPGS Hosted Batch / Settlement — Implementation Plan & Tracker

Status legend: `[ ]` not started · `[~]` in progress · `[x]` done · `[!]` blocked (see Notes)

Scope for this pass: **PAY (archive-only) + AUTHORIZE + CAPTURE**. Refund/Void/Tokenize
are deferred to a later phase. Credentials for the MPGS test gateway are not available
yet — everything here is built and unit-tested without them; live verification against
the MTF gateway is a separate, final step once credentials arrive.

Also added, 2026-09-05: **Close Batch** (`PaymentGatewayApp.Mpgs.Batch.close/2`) —
manual REST-API closure of the *acquirer's* daily settlement batch (a different, older
concept from MPGS Hosted Batch above; same REST transport as every other MPGS operation
in `payment_gateway_app`, no relation to `pg_settlement`). See "Close Batch" section
below.

## Critical finding: this database is shared with another live system

While wiring up Oban, inspecting the dev database (`shukria_transactions`) surfaced
something that changed how Oban had to be configured, and that the team should be aware
of independent of this feature:

- `oban_peers` already had a row: `name="Oban"`, `node="demo"`, with a lease that was
  actively renewing at the time (i.e. a currently-running process, not stale data).
- `oban_jobs` already had thousands of rows across queues named `mastercom`,
  `risk_evaluation`, `vat_mis`, `alert_evaluation`, `disputes`, `low`, `mailers`,
  `payouts`, `settlements`, `verification_api`, `clearing`, `default`,
  `installed_apps_sync`, `reconciliation`, `reports`, `scheme_compliance` — with workers
  like `DisputeCore.Workers.MastercomOutcomeReconciliationWorker`,
  `TmsCore.AlertsCore.Workers.EscalationWorker`, `RiskCore.Workers.*`,
  `SettlementCore.Workers.*`, `SchemeCore.Workers.*`, `ClearingCore.Mastercard.Ipm.*`.
  None of these modules (`DisputeCore`, `TmsCore`, `RiskCore`, `SettlementCore`,
  `SchemeCore`, `ClearingCore`) exist anywhere in this repository.

This means `shukria_transactions` is shared with at least one other, separately deployed
application (a larger platform this repo is only one part of) that already runs its own
Oban instance against it. The commented-out Oban config this task activated had generic
queue names (`default`, `mailers`, `high`, `low`) that directly overlap with that other
system's real, currently-active queues. Starting Oban here under the default instance
name with those queues would have: raced the other system for the `oban_peers` "Oban"
leadership row, started claiming its `default`/`mailers`/`low` jobs (and failing them,
since their worker modules aren't loaded here), and — had `Oban.Plugins.Pruner` been left
enabled, since it has no per-queue scoping in open-source Oban — begun deleting that
other system's job history on its 24-hour age plugin sweep.

None of that was exercised (Oban was never started during this session — see
Verification Notes), but it would have happened on the next real deploy/restart of this
app with the originally-planned config. Fixed by giving this feature's Oban instance its
own name (`PgSettlement.Oban`) and its own queue (`settlement`), and dropping the pruner
plugin entirely. This is safe for `pg_settlement`'s own purposes but is worth flagging to
whoever owns the other system, independent of this feature — the same collision would
recur for any other future generic Oban config change in this repo.

## Architecture decisions (locked in from discussion)

- New OTP app `pg_settlement`, path-dependency in the umbrella, compiled in only when
  `INCLUDE_PG_SETTLEMENT=true` (mirrors `INCLUDE_PAYMENT_GATEWAY`).
- **Same database** as `payment_gateway_app` (`DaProductApp.Repo`), separate tables.
  The move from `pg_transactions` to `pg_settlement_transactions` is a single atomic
  DB transaction (`INSERT ... ; DELETE ...`), not a cross-database copy.
- `pg_settlement_transactions` is a **shared ledger table**: `pg_settlement` owns it
  end-to-end (the mover writes new rows, batch processing updates them in place).
  `payment_gateway_app`'s live request path is not modified — the move runs as a
  periodic sweep inside `pg_settlement`, not inline in the checkout/webhook code, so
  archival work never sits on the critical path of a live payment.
- `pg_settlement` reuses `PaymentGatewayApp.Mpgs.Credentials.resolve/1` and
  `PaymentGatewayApp.Mpgs.Vault.decrypt/1` (path dependency on `payment_gateway_app`)
  rather than duplicating credential storage or crypto — one place to rotate a
  password, one AES-GCM implementation.
- Batch's own HTTP surface (`/batch/version/<v>/merchant/<id>/batch/<name>`) is
  genuinely different from the REST API MPGS client already built, so `pg_settlement`
  gets its own HTTP client rather than extending `Mpgs.Client`. Basic-Auth, however,
  is the same `merchant.<id>` / password convention as REST — confirmed with the team
  2026-09-05 (the batch docs' own sample script showed an empty username, which
  turned out not to match how this gateway is actually configured).
- No push notification for batch outcomes — status is polled. All batch work runs as
  Oban jobs against a dedicated `PgSettlement.Oban` instance on its own `settlement`
  queue — **not** the umbrella's pre-existing (dead, unstarted) Oban config, which
  turned out to have generic queue names colliding with another live system sharing
  this database. See "Critical finding" below.

## Phase 0 — Foundations

- [x] `oban` added as a real dependency (was configured but never declared/started).
- [x] Oban started in `DaProductApp.Application`'s supervision tree — **as its own
      named instance (`PgSettlement.Oban`), listening only on a `settlement` queue,
      with no pruner plugin.** This was not the original plan; see "Critical finding"
      below for why.
- [x] `pg_settlement` app scaffolded, wired into root `mix.exs` behind `INCLUDE_PG_SETTLEMENT`.
- [x] Migration: `pg_settlement_transactions` (moved-transaction ledger + settlement/batch tracking fields).
- [x] Migration: `pg_settlement_batches` (one row per batch upload; status, counts, MIC, timestamps).
- [x] Migration: `mpgs_merchant_credentials.batch_enabled` (default `false` — no merchant is
      swept into batch processing until explicitly turned on; this is also where MPGS-side
      Batch-service enablement gets confirmed before flipping it).

## Phase 1 — Move completed MPGS transactions out of `pg_transactions`

- [x] `PgSettlement.SettlementTransaction` schema.
- [x] `PgSettlement.Mover`: selects terminal (`closure_status IN ('CLOSED','FAILED')`,
      `gateway = 'mpgs'`) rows from `pg_transactions`, classifies each as
      `settled` (PAY/CAPTURE), `awaiting_capture` (AUTHORIZE), or `failed` from the
      transaction type recorded in `raw_payload`, and moves it — insert + delete in one
      DB transaction, per row, so a failure on one row never blocks the rest of the sweep.
- [x] `PgSettlement.Workers.MoverWorker` — Oban cron, runs the sweep periodically.
- [x] Unit tests for the classification logic (pure function, no DB needed).
- [ ] DB-backed test of the move itself (insert a fake `pg_transactions` row, run the
      mover, assert it landed correctly and the source row is gone) — needs a reachable
      MySQL test database; see Verification Notes.

## Phase 2 — Build and send batches

- [x] `PgSettlement.Csv` — CSV encoder matching the doc's Native Format rules (comma
      delimiter, quote-on-embedded-comma/quote/newline, no trimming, no comments).
- [x] `PgSettlement.Mic` — SHA-1 hex digest of the exact uploaded body.
- [x] `PgSettlement.BatchName` — atomic unique batch name allocator, reusing the
      existing `mpgs_order_sequences` counter table with a `settlement-batch:<merchant>`
      scope (no new counter table needed).
- [x] `PgSettlement.Credentials` — thin wrapper delegating to
      `PaymentGatewayApp.Mpgs.Credentials.resolve/1`, adding batch's own URL shape
      (Basic-Auth reuses the REST convention — confirmed with the team 2026-09-05).
- [x] `PgSettlement.Client` — PUT upload, POST validate, GET status, GET response.
- [x] `PgSettlement.BatchBuilder` — for each `batch_enabled` merchant with
      `awaiting_capture` rows: groups them (one order per batch — the doc's hard rule
      against mixing operations on the same order id is enforced here), chunks to stay
      under 3 MB / 18,000 records, builds the CSV (including the response-field columns
      we want populated back), uploads, computes + submits the MIC, records a
      `pg_settlement_batches` row, marks the moved rows `batch_sent`.
- [x] `PgSettlement.Workers.BatchBuilderWorker` — Oban cron.
- [x] Unit tests: CSV encoding against the doc's own sample rows, chunking boundaries,
      same-order-id exclusion.

## Phase 3 — Poll status and process the response

- [x] `PgSettlement.Workers.StatusPollerWorker` — Oban cron; GETs status for every
      batch not yet `Complete`, advances the stored `pg_settlement_batches.status`.
- [x] `PgSettlement.CsvParser` — parses the response CSV (handles the same quoting
      rules in reverse).
- [x] `PgSettlement.ResponseProcessor` — on `Complete`, downloads the response file,
      matches rows back to `pg_settlement_transactions` by `order.id` + `transaction.id`
      (per the doc's explicit warning that response row order is not guaranteed to match
      the request), updates each row's `settlement_status` (`settled`/`capture_failed`)
      and stores `response_gateway_code` / `error_cause` / `error_explanation`, and
      reconciles the returned amount against what was submitted.
- [x] `PgSettlement.Workers.ResponseProcessorWorker` — Oban worker, enqueued when a
      batch reaches `Complete`.
- [x] Unit tests: response CSV parsing against the doc's sample shape, reconciliation
      mismatch detection.

## Manual operation (added 2026-09-05)

Before the Oban schedule is trusted hands-off, or for on-demand testing, both pipelines
have a mix task rather than requiring an `iex -S mix` session:

- `mix pg_settlement.run [--mover] [--build] [--poll] [--response BATCH_NAME] [--force]`
  — runs mover/build/poll in that order if no flags given; `--mover`/`--build` refuse to
  run without `--force` since they move real data / send real batches to MPGS. See
  `apps/pg_settlement/lib/mix/tasks/pg_settlement.run.ex`.
- `mix mpgs.close_batch --user-id ID [--acquirer-id ID] [--correlation-id ID]` — the
  Close Batch REST call. See `apps/payment_gateway_app/lib/mix/tasks/mpgs.close_batch.ex`.

Both require `INCLUDE_PAYMENT_GATEWAY=true INCLUDE_PG_SETTLEMENT=true` at compile time
(same as the app itself) and were verified against the real dev database: `--poll` runs
and correctly reports zero outstanding batches; `--response` with a nonexistent batch
name raises cleanly; `--mover`/bare (all-phases) invocation without `--force` is
correctly refused.

## Phase 4 — Compile & verify

- [x] Whole umbrella compiles clean (`INCLUDE_PAYMENT_GATEWAY=true INCLUDE_PG_SETTLEMENT=true`),
      zero warnings from any `pg_settlement` or changed `payment_gateway_app` file.
- [x] `mix test` for `pg_settlement` (CSV encode/decode, MIC, mover classification) —
      23/23 passing, run standalone (`cd apps/pg_settlement && mix test`).
- [x] All four new migrations applied against the real dev database
      (`shukria_transactions`) via `MIX_ENV=dev mix ecto.migrate` — `pg_settlement_transactions`,
      `pg_settlement_batches`, and `mpgs_merchant_credentials.batch_enabled` were created
      directly by the migrator and verified with `DESCRIBE`. The Oban jobs migration found
      `oban_jobs`/`oban_peers` already present and structurally identical (someone had run
      `Oban.Migrations` before) and was marked applied rather than re-run.
- [ ] **Not run**: a live `PgSettlement.Mover.sweep/1` against real data. The dev database
      already has 64 real rows that satisfy the mover's eligibility query
      (`gateway = 'mpgs' AND closure_status IN ('CLOSED','FAILED')`). Since the mover's
      query has no test-data filter, actually invoking it would move real existing
      transactions out of `pg_transactions` — a visible, non-trivial side effect on data
      that isn't part of this change. Left unexercised deliberately; the move logic itself
      (atomic `Repo.transaction` wrapping an insert + a conditional delete) is unit-tested
      for its classification branch and otherwise follows the same `Repo.transaction` +
      raw-SQL pattern already proven in this codebase by `Mpgs.OrderSequence`. Exercise it
      first against a small number of real or deliberately-seeded rows before relying on it.

## Close Batch (added 2026-09-05, separate from everything else in this document)

`PaymentGatewayApp.Mpgs.Batch.close/2` — `PUT .../api/rest/version/100/merchant/{id}/batch`,
same `Mpgs.Client`/`Mpgs.Credentials.Config` REST transport as every other operation in
`payment_gateway_app` (pay/capture/refund/void). Forces early closure of the *acquirer's*
own daily settlement batch — an older, distinct concept from MPGS Hosted Batch (the
CSV/file mechanism this whole document is otherwise about). No relation to
`pg_settlement`; lives in `payment_gateway_app` because it's a plain REST call.

**Schema gap resolved 2026-09-05**: the doc was updated with the full `acquirer` object —
`id`, `currency`, `cardType` are all individually optional (the `acquirer` object itself
is required, but `%{}` is valid — matches what `close/2` already defaulted to). Also
added top-level, optional `correlationId` (transient, echoed back on the response, not
validated or persisted) — `close/3` now accepts it. Not otherwise called from anywhere
yet; this pass added only the function itself, per explicit scope (no controller/route/
admin UI requested). No further live-gateway verification needed for the request shape
itself — what's still unverified is only the operational question of when/whether this
should ever actually be invoked (see "Deliberately out of scope" — nothing in the
current PAY/AUTHORIZE/CAPTURE flow calls it).

## Deliberately out of scope for this pass

- Refund / Void / Tokenize batch operations.
- Batch-submitted PAY (raw card data straight through batch, no prior real-time leg) —
  phase-1 scope is archive-only for PAY.
- Cross-database deployment for `pg_settlement` (same-DB was the explicit decision).
- `payment_gateway_app` reading settlement outcomes back into `pg_transactions` — since
  moved rows no longer exist there, this is superseded: `pg_settlement_transactions`
  *is* the record from the moment of move onward.

## Open items that need real credentials / MPGS access before go-live

1. ~~**Basic-Auth format for batch**~~ — **Resolved 2026-09-05.** Team confirmed batch
   uses the same `merchant.<id>` / password Basic-Auth convention as the REST API — the
   batch docs' own sample script (empty username) did not match how this gateway is
   actually configured. `PgSettlement.Credentials.basic_auth/1` updated accordingly.
2. ~~**Batch API version**~~ — **Resolved 2026-09-05.** Confirmed `version/100`, same as
   REST — the batch docs' sample script's `VERSION_NUM=72` did not apply here either.
   `PgSettlement.Credentials` no longer keeps a separate batch-version setting; it reuses
   `PaymentGatewayApp.Mpgs.Credentials.Config.api_version` directly, so the two can never
   drift apart.
3. **Per-merchant Batch-service enablement** — confirm with MPGS/the acquirer which
   merchant(s) have Batch turned on before setting `batch_enabled = true` for them.

## Verification Notes

This environment had network access (used to fetch `oban`) and a reachable local MySQL
instance already running as this project's dev database
(`shukria_transactions`), so both compilation and migrations were verified for real
rather than only reviewed. What was and wasn't exercised:

- **Compiles clean** end to end with both `INCLUDE_PAYMENT_GATEWAY=true` and
  `INCLUDE_PG_SETTLEMENT=true`.
- **Migrations applied** against the real dev database and inspected with `DESCRIBE`
  (see Phase 4).
- **Pure-logic unit tests** (CSV Native Format encode/decode against the docs' own
  sample rows, SHA-1 MIC computation, PAY/AUTHORIZE/FAILED classification) run and
  pass — 23/23, via `cd apps/pg_settlement && mix test`. These don't touch the
  database (`payment_gateway_app`/`da_product_app` are `runtime: false` path
  dependencies, so `DaProductApp.Repo` isn't started), mirroring how
  `payment_gateway_app`'s own existing pure tests already run standalone.
- **Deliberately not run**: `PgSettlement.Mover.sweep/1` against the real dev database —
  see Phase 4 for why (64 pre-existing real rows would have been moved). Also not run:
  `PgSettlement.BatchBuilder.run/0` / anything hitting the actual MPGS batch endpoints,
  since no merchant has real Batch-service credentials yet and none has
  `batch_enabled = true` (the migration defaults it to `false` for every existing row).

Nothing here needed the merchant's Batch credentials — those are still required before
any live upload/validate/status/response call against the real MPGS test gateway can be
tried, per Open Items #1–#3.

------------
Command to run and check
INCLUDE_PAYMENT_GATEWAY=true INCLUDE_PAYMENT_GATEWAY=true INCLUDE_PG_SETTLEMENT=true MPGS_CREDENTIAL_KEY='AvZiJ/1TR2+ElOZrbSJOjtZCAK6J9sbdqS65/j4upqA=' mix mpgs.close_batch --user-id 1628 --acquirer-id ACQ1