> **⚠️ SUPERSEDED (2026-07-12).** The per-type matcher architecture tracked
> here was replaced by the generic rule-engine design — see
> **[RECON_GENERIC_RULE_ENGINE_PLAN.md](RECON_GENERIC_RULE_ENGINE_PLAN.md)**.
> The legacy matchers (`matchers/*.py`), `MATCHER_MAP`, and the `recon_type`
> enum were removed at the R6 cutover after golden-parity tests passed.
> This document is kept for historical context only.

# MW-Core Recon Feature — Phase Tracker

**Branch:** `feature/recon`
**Requirement Source:** Sahyadri Python Files (reference implementation)
**Started:** 2026-06-19
**Last Updated:** 2026-06-19
**Target:** Non-technical users can upload files, run reconciliation, and download an Excel report from the admin UI — no Python scripts, no manual file handling.

---

## Architecture Summary

```
┌─────────────────────────────────────────────────────────────────┐
│  gateway_web  (Phoenix LiveView — 4-step Recon Wizard UI)       │
│  ┌──────────┐ ┌──────────┐ ┌──────────────┐ ┌───────────────┐  │
│  │ Step 1   │→│ Step 2   │→│  Step 3      │→│  Step 4       │  │
│  │ Select   │ │ Upload   │ │  Reconcile   │ │  Download     │  │
│  │ Hospital │ │ Files    │ │  (live job)  │ │  Report       │  │
│  │ + Type   │ │ (2–3)    │ │              │ │               │  │
│  └──────────┘ └──────────┘ └──────────────┘ └───────────────┘  │
└───────────────────────────┬─────────────────────────────────────┘
                            │  LiveView events + PubSub job status
┌───────────────────────────▼─────────────────────────────────────┐
│  mw_recon  (new umbrella app — Elixir orchestration layer)      │
│  ReconSession  ReconJob  ReconConfig  ReconResults context       │
└──────┬────────────────────────────────┬──────────────────────────┘
       │ CloudI: /+/recon/match/        │ CloudI: /+/recon/report/
┌──────▼──────────────────┐  ┌──────────▼────────────────────────┐
│ recon_engine_py         │  │ recon_report_py                   │
│ (CloudI Python service) │  │ (CloudI Python service)           │
│ - Column normalisation  │  │ - Write matched/unmatched sheets  │
│ - 4-key → 3-key match   │  │ - Summary sheet by Unit/Location  │
│ - Multi-type: CARD/UPI/ │  │ - Blue header formatting          │
│   AMEX/HIS              │  │ - Return base64 XLSX              │
└─────────────────────────┘  └───────────────────────────────────┘
       │
┌──────▼──────────────────────────────────────────────────────────┐
│  infra_repo  (MySQL)                                            │
│  recon_sessions  recon_results  recon_configs  recon_run_files  │
└─────────────────────────────────────────────────────────────────┘
```

**Technology decisions:**
- Matching engine stays in Python (CloudI service) — ports existing logic directly, avoids rewriting complex fuzzy matching in Elixir
- Report generator is also Python (openpyxl already in `services/file_parser_py/requirements.txt`)
- All orchestration, state, and UI in Elixir/Phoenix LiveView — no Python for web layer
- Reuses existing `infra_queue.AsyncJobStore`, `mw_auth`, `mw_audit`, tenant system

---

## Phase Overview

| Phase | Name | Duration | Status |
|-------|------|----------|--------|
| **0** | Foundation — `mw_recon` app + DB schema | Weeks 1–2 | ⬜ Pending |
| **1** | Python Matching Engine (CloudI service) | Weeks 3–4 | ⬜ Pending |
| **2** | Python Report Generator (CloudI service) | Weeks 4–5 | ⬜ Pending |
| **3** | File Upload API + Async Job Pipeline | Weeks 5–6 | ⬜ Pending |
| **4** | LiveView Recon Wizard UI | Weeks 7–8 | ⬜ Pending |
| **5** | Hospital Config + Seed Data | Weeks 8–9 | ⬜ Pending |
| **6** | Hardening, Testing + Audit | Weeks 9–10 | ⬜ Pending |

---

## Phase 0 — Foundation (Weeks 1–2)

**Goal:** New `mw_recon` umbrella app exists with DB schema. Nothing runs yet but the scaffold is correct.

| Task | File / Location | Status | Notes |
|------|----------------|--------|-------|
| Create `apps/mw_recon/` umbrella app | `apps/mw_recon/mix.exs` + `lib/` | ⬜ Pending | |
| Define `ReconSession` schema | `infra_repo/schemas/recon_session.ex` | ⬜ Pending | |
| Define `ReconRunFile` schema | `infra_repo/schemas/recon_run_file.ex` | ⬜ Pending | |
| Define `ReconResult` schema | `infra_repo/schemas/recon_result.ex` | ⬜ Pending | |
| Define `ReconConfig` schema | `infra_repo/schemas/recon_config.ex` | ⬜ Pending | |
| Define `ReconLocationMap` schema | `infra_repo/schemas/recon_location_map.ex` | ⬜ Pending | |
| Write DB migration 20260619000001 | `infra_repo/priv/repo/migrations/` | ⬜ Pending | All 4 recon tables |
| Add `ReconSessions` context to `infra_repo` | `infra_repo/lib/infra_repo/recon_sessions.ex` | ⬜ Pending | CRUD + list/filter |
| Add `ReconConfigs` context to `infra_repo` | `infra_repo/lib/infra_repo/recon_configs.ex` | ⬜ Pending | |
| Add `mw_recon` to umbrella `mix.exs` deps | `mix.exs` | ⬜ Pending | |
| All existing tests pass | `mix test` | ⬜ Pending | |

**Phase 0 Acceptance Criteria:**
- [ ] `mix ecto.migrate` runs cleanly with all 4 new tables created
- [ ] `InfraRepo.ReconSessions.create_session/1` and `get_session/1` work in IEx
- [ ] `mw_recon` application starts as part of the umbrella with no errors
- [ ] All existing 86+ tests continue to pass

---

## Phase 1 — Python Matching Engine (Weeks 3–4)

**Goal:** A CloudI Python service that accepts two or three file payloads and returns matched/unmatched rows. Covers all reconciliation types in the Python reference scripts.

| Task | File / Location | Status | Notes |
|------|----------------|--------|-------|
| Create `services/recon_engine_py/` | `services/recon_engine_py/main.py` | ⬜ Pending | CloudI service |
| Implement column normalisation | `recon_engine_py/normaliser.py` | ⬜ Pending | Maps bank-specific column names to canonical set |
| Implement 4-key match (CARD type) | `recon_engine_py/matchers/card.py` | ⬜ Pending | last4, terminal_id, amount, approval_code |
| Implement 4→3-key fallback | `recon_engine_py/matchers/card.py` | ⬜ Pending | Retry on last4+terminal+amount if 4-key fails |
| Implement UPI match | `recon_engine_py/matchers/upi.py` | ⬜ Pending | card_num (VPA), terminal_id, amount, rrn_no |
| Implement HIS-Bank CARD match | `recon_engine_py/matchers/his_bank_card.py` | ⬜ Pending | last4, amount, approval_no + date filter |
| Implement HIS-Bank UPI match | `recon_engine_py/matchers/his_bank_upi.py` | ⬜ Pending | amount match + payment mode filter |
| Implement HIS-MomentsPay match | `recon_engine_py/matchers/his_momentspay.py` | ⬜ Pending | From Sahyadri MSBAC/MSBU/MSBC scripts |
| Implement AMEX match | `recon_engine_py/matchers/amex.py` | ⬜ Pending | From Samex1.py reference |
| Add `requirements.txt` | `services/recon_engine_py/requirements.txt` | ⬜ Pending | pandas, openpyxl, cloudi |
| Register in `config/cloudi.conf` | `/+/recon/match/` prefix | ⬜ Pending | count_process: 2 |
| Add `MwRecon.EngineClient` in Elixir | `apps/mw_recon/lib/mw_recon/engine_client.ex` | ⬜ Pending | Calls CloudI `/+/recon/match/` |
| Unit tests for each matcher | `services/recon_engine_py/tests/` | ⬜ Pending | Use sample data extracted from Python scripts |

**Phase 1 Acceptance Criteria:**
- [ ] `POST /+/recon/match/` with a CARD payload returns `{matched: [...], unmatched: [...], summary: {...}}`
- [ ] All 5 matcher types (CARD, UPI, HIS-Card, HIS-UPI, AMEX) return correct results against reference sample data
- [ ] 4-key match falls back to 3-key correctly for CARD type
- [ ] Column normalisation handles all bank column-name variants seen in Python scripts
- [ ] Unit tests cover each matcher with ≥ 3 sample cases each
- [ ] Service restarts within 3s if killed (CloudI MaxR/MaxT)

---

## Phase 2 — Python Report Generator (Weeks 4–5)

**Goal:** A CloudI Python service that accepts match results and writes a formatted Excel workbook (matched sheet, unmatched sheet, Summary sheet by location), returning it as base64.

| Task | File / Location | Status | Notes |
|------|----------------|--------|-------|
| Create `services/recon_report_py/` | `services/recon_report_py/main.py` | ⬜ Pending | CloudI service |
| Implement sheet writer — matched | `recon_report_py/writer.py` | ⬜ Pending | openpyxl — blue header `#1274bd` |
| Implement sheet writer — unmatched | `recon_report_py/writer.py` | ⬜ Pending | Same formatting |
| Implement Summary sheet by location | `recon_report_py/summary.py` | ⬜ Pending | Count + amount per Unit/Location, matched vs unmatched |
| Handle all recon type sheet names | `recon_report_py/sheet_names.py` | ⬜ Pending | e.g. `M-HISBankcard-Matched`, `M-BankCard-Matched` |
| Return base64 XLSX in response | `main.py` | ⬜ Pending | |
| Register in `config/cloudi.conf` | `/+/recon/report/` prefix | ⬜ Pending | count_process: 2 |
| Add `MwRecon.ReportClient` in Elixir | `apps/mw_recon/lib/mw_recon/report_client.ex` | ⬜ Pending | Calls CloudI `/+/recon/report/` |
| Unit tests | `services/recon_report_py/tests/` | ⬜ Pending | Verify Excel output matches reference |

**Phase 2 Acceptance Criteria:**
- [ ] `POST /+/recon/report/` returns a valid base64 XLSX
- [ ] Output XLSX contains matched sheet, unmatched sheet, and Summary sheet
- [ ] Summary sheet rows are grouped by Unit/Location with count and total amount
- [ ] Header row is blue `#1274bd` on both matched and unmatched sheets
- [ ] Sheet names match the convention from the Python reference scripts

---

## Phase 3 — File Upload API + Async Job Pipeline (Weeks 5–6)

**Goal:** The Elixir layer can accept multi-file uploads, parse them using `file_parser_py`, and orchestrate a full recon run as an async job tracked in the DB.

| Task | File / Location | Status | Notes |
|------|----------------|--------|-------|
| Add `recon_upload` route to `gateway_api` | `gateway_api/router.ex` | ⬜ Pending | `POST /api/v1/recon/sessions/:id/upload` |
| Create `ReconController` | `gateway_api/controllers/recon_controller.ex` | ⬜ Pending | upload, run, status, download |
| Extend `file_parser_py` for multi-sheet read | `services/file_parser_py/main.py` | ⬜ Pending | Add `all_sheets` option to `__parse_xlsx` |
| Create `MwRecon.Orchestrator` | `apps/mw_recon/lib/mw_recon/orchestrator.ex` | ⬜ Pending | GenServer: parse → match → report → store |
| Wire `AsyncJobStore` to recon jobs | `apps/mw_recon/lib/mw_recon/orchestrator.ex` | ⬜ Pending | Reuse `infra_queue.AsyncJobStore` |
| Publish job progress via PubSub | `apps/mw_recon/lib/mw_recon/orchestrator.ex` | ⬜ Pending | Topic: `recon:job:{session_id}` |
| Store matched/unmatched rows to DB | `infra_repo/recon_sessions.ex` | ⬜ Pending | Via `ReconResult` schema |
| Store report XLSX to DB (base64) | `infra_repo/recon_sessions.ex` | ⬜ Pending | On `recon_sessions.report_data` |
| `GET /api/v1/recon/sessions/:id/report` | `gateway_api/controllers/recon_controller.ex` | ⬜ Pending | Download final XLSX |

**Phase 3 Acceptance Criteria:**
- [ ] Upload 2–3 files via API → session created, files stored, job starts
- [ ] Job status transitions: `pending → parsing → matching → reporting → completed`
- [ ] PubSub events fire at each transition (LiveView will consume these)
- [ ] Completed session has downloadable report at GET endpoint
- [ ] Failed job records error reason in `async_jobs.error`

---

## Phase 4 — LiveView Recon Wizard UI (Weeks 7–8)

**Goal:** Non-technical user can complete a full reconciliation end-to-end through the admin UI with zero command-line interaction.

| Task | File / Location | Status | Notes |
|------|----------------|--------|-------|
| Add `/admin/recon` route to router | `gateway_web/router.ex` | ⬜ Pending | Under `:admin` live_session |
| Create `ReconWizardLive` — Step 1 | `gateway_web/live/recon_wizard_live.ex` | ⬜ Pending | Select hospital (tenant) + recon type dropdown |
| Create `ReconWizardLive` — Step 2 | `gateway_web/live/recon_wizard_live.ex` | ⬜ Pending | Dynamic file upload slots based on recon type |
| Create `ReconWizardLive` — Step 3 | `gateway_web/live/recon_wizard_live.ex` | ⬜ Pending | Live progress bar from PubSub `recon:job:{id}` |
| Create `ReconWizardLive` — Step 4 | `gateway_web/live/recon_wizard_live.ex` | ⬜ Pending | Summary stats + Download Report button |
| Create `ReconHistoryLive` | `gateway_web/live/recon_history_live.ex` | ⬜ Pending | Paginated list of past recon sessions |
| Add Recon nav item to sidebar | `gateway_web/layouts/app.html.heex` | ⬜ Pending | Icon + "Reconciliation" label |
| Add `/admin/recon/history` route | `gateway_web/router.ex` | ⬜ Pending | |
| Handle upload errors gracefully | `recon_wizard_live.ex` | ⬜ Pending | Wrong file type, missing columns, etc. |
| Responsive layout (tablet-friendly) | template `.heex` files | ⬜ Pending | Finance teams often use iPad |

**Step 2 — Dynamic file slots per recon type:**

| Recon Type | File Slot 1 | File Slot 2 | File Slot 3 |
|------------|-------------|-------------|-------------|
| Bank Card vs MomentsPay | Bank Card Statement (XLSX) | MomentsPay Export (CSV) | — |
| Bank UPI vs MomentsPay | Bank UPI Statement (XLSX) | MomentsPay Export (CSV) | — |
| HIS vs Bank Card | HIS Export (XLSX) | Bank Card Statement (XLSX) | — |
| HIS vs Bank UPI | HIS Export (XLSX) | Bank UPI Statement (XLSX) | — |
| HIS vs MomentsPay (Card) | HIS Export (XLSX) | MomentsPay Export (CSV) | Bank Card (XLSX) |
| HIS vs MomentsPay (UPI) | HIS Export (XLSX) | MomentsPay Export (CSV) | Bank UPI (XLSX) |
| AMEX | HIS Export (XLSX) | AMEX Statement (CSV) | — |

**Phase 4 Acceptance Criteria:**
- [ ] Non-technical user can complete a recon run without any documentation (intuitive UI)
- [ ] File slots show expected format and column hints per slot
- [ ] Live progress updates without page refresh
- [ ] Download button appears only when job is `completed`
- [ ] History page shows past sessions with date, hospital, type, matched %, download link
- [ ] Error states show human-readable messages (not stack traces)

---

## Phase 5 — Hospital Config + Seed Data (Weeks 8–9)

**Goal:** Each hospital's column mapping, bank names, location-to-code mapping, and recon type availability is stored in DB and configurable via admin UI — not hardcoded.

| Task | File / Location | Status | Notes |
|------|----------------|--------|-------|
| Create `ReconConfigLive` admin page | `gateway_web/live/recon_config_live.ex` | ⬜ Pending | View/edit per-hospital recon config |
| Seed Sahyadri hospital config | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | Column mappings from Python scripts |
| Seed Sahyadri location map | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | Full unit_mapping dict from MSBC.py / MSBU.py |
| Seed Anderson config | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | From Anderson python files |
| Seed Continental config | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | From Continental python files |
| Seed Rela config | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | |
| Seed Srikara config | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | |
| Seed Trustwell config | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | |
| Seed DRMOHAN config | `infra_repo/priv/repo/seeds.exs` | ⬜ Pending | Cluster/branch/entity logic |
| Map hospital configs to tenants | `infra_repo/recon_configs.ex` | ⬜ Pending | Each config belongs to a tenant |
| `GET /admin/recon/config` route | `gateway_web/router.ex` | ⬜ Pending | Config editor UI |

**Sahyadri Location Map (seed reference):**

```elixir
%{
  "SHL Bibwewadi-Hospital" => "BBW",
  "SSL CC Sinhgad Road" => "Lab",
  "SSL CC Wakad" => "Lab",
  "SSL Labs" => "Lab",
  "Sahyadri Super Speciality Hospital, Deccan" => "Deccan",
  "Sahyadri Super Speciality Hospital, Hadapsar" => "HDP",
  "Sahyadri Super Speciality Hospital, Nagar Road" => "NGR",
  "Sahyadri Super Speciality Hospital, Nashik" => "NSK",
  "SHL Kothrud-Hospital" => "Koth",
  "Sahyadri Super Speciality Hospital, Shivajinagar" => "Shiv",
  # ... (full list in phase-recon-5.md)
}
```

**Phase 5 Acceptance Criteria:**
- [ ] All 7 hospitals available in Step 1 hospital dropdown
- [ ] Selecting a hospital shows only the recon types configured for that hospital
- [ ] Location map applies correctly in Summary sheet grouping
- [ ] Admin can add a new location mapping via UI without code change
- [ ] Column mapping editor allows override of bank column names per hospital

---

## Phase 6 — Hardening, Testing + Audit (Weeks 9–10)

**Goal:** Production-ready. Every recon run is audited, errors are informative, and the system handles bad input gracefully.

| Task | File / Location | Status | Notes |
|------|----------------|--------|-------|
| Audit every recon run via `mw_audit` | `apps/mw_recon/lib/mw_recon/orchestrator.ex` | ⬜ Pending | `recon.session.started`, `recon.session.completed`, `recon.session.failed` |
| ExUnit integration tests — CARD match | `apps/mw_recon/test/` | ⬜ Pending | Uses sample XLSX files as fixtures |
| ExUnit integration tests — UPI match | `apps/mw_recon/test/` | ⬜ Pending | |
| ExUnit integration tests — HIS-Bank | `apps/mw_recon/test/` | ⬜ Pending | |
| Graceful handling: missing columns | `recon_engine_py/normaliser.py` | ⬜ Pending | Return `{status: error, missing_columns: [...]}` |
| Graceful handling: empty file | `orchestrator.ex` | ⬜ Pending | Short-circuit before CloudI call |
| Graceful handling: wrong recon type | `recon_wizard_live.ex` | ⬜ Pending | Detect and show warning before running |
| Rate limiting: max 1 active job per tenant | `orchestrator.ex` | ⬜ Pending | Reject if tenant already has running job |
| File size limit: 20MB per upload | `gateway_api/recon_controller.ex` | ⬜ Pending | Return 413 with message |
| Load test: 5 concurrent recon runs | k6 / manual | ⬜ Pending | P99 < 60s for typical file sizes |
| Recon audit visible in Audit Log UI | `gateway_web/live/audit_log_live.ex` | ⬜ Pending | Filter by event type `recon.*` |

**Phase 6 Acceptance Criteria:**
- [ ] All recon audit events visible in `/admin/audit` with correct tenant scoping
- [ ] Uploading a file with missing required columns shows a clear error message naming the missing columns
- [ ] Uploading an empty file is rejected before job creation
- [ ] 5 simultaneous recon runs from different tenants complete without interference
- [ ] A single tenant cannot queue more than 1 recon job at a time
- [ ] All ExUnit tests pass (`mix test`)

---

| # | Question | Status | Answer |
|---|----------|--------|----------|
| OQ-01 | Should report XLSX be stored permanently in DB or only for N days? | ⬜ Open | Only for 30 days |
| OQ-02 | Should the recon feature be gated to a specific admin role (e.g. `finance_admin`)? | ⬜ Open | Yes and Super Admin |
| OQ-03 | DRMOHAN uses entity/cluster/branch grouping — is this a separate recon type or config? | ⬜ Open | Not sure, need to study to map |
| OQ-04 | Should the Summary sheet cell positions be configurable or fixed per recon type? | ⬜ Open | It has to be configurable |
| OQ-05 | Date filtering: user selects date in UI (Step 1) or auto-detected from file? | ⬜ Open | Detect from File, if not selected , and if there is mismatch then alert about wrong date or file |
| OQ-06 | Should recon history be visible cross-tenant to a superadmin? | ⬜ Open | Yes |
| OQ-07 | Razorpay (DRMOHAN) reconciliation — same engine or separate CloudI service? | ⬜ Open | Only file format for razorpay will be different but I am feeling, we can manage with existing engine |

---

## Key File Inventory (new files to be created)

### New Elixir apps / modules
```
apps/mw_recon/
  mix.exs
  lib/mw_recon.ex
  lib/mw_recon/application.ex
  lib/mw_recon/orchestrator.ex          # GenServer — full recon run lifecycle
  lib/mw_recon/engine_client.ex         # CloudI call → recon_engine_py
  lib/mw_recon/report_client.ex         # CloudI call → recon_report_py
  test/mw_recon/orchestrator_test.exs
  test/mw_recon/fixtures/               # sample XLSX/CSV test files
```

### New infra_repo schemas + migrations
```
apps/infra_repo/lib/infra_repo/schemas/
  recon_session.ex
  recon_run_file.ex
  recon_result.ex
  recon_config.ex
  recon_location_map.ex
apps/infra_repo/lib/infra_repo/
  recon_sessions.ex
  recon_configs.ex
apps/infra_repo/priv/repo/migrations/
  20260619000001_create_recon_tables.exs
```

### New gateway_api controllers
```
apps/gateway_api/lib/gateway_api_web/controllers/
  recon_controller.ex
```

### New gateway_web LiveViews
```
apps/gateway_web/lib/gateway_web_web/live/
  recon_wizard_live.ex
  recon_wizard_live.html.heex
  recon_history_live.ex
  recon_history_live.html.heex
  recon_config_live.ex
  recon_config_live.html.heex
```

### New Python CloudI services
```
services/recon_engine_py/
  main.py                   # CloudI service entry point
  requirements.txt
  Dockerfile
  docker-compose.yml
  normaliser.py             # Column name normalisation for each bank
  matchers/
    __init__.py
    card.py                 # Bank Card vs MomentsPay (4-key → 3-key fallback)
    upi.py                  # Bank UPI vs MomentsPay
    his_bank_card.py        # HIS vs Bank Card
    his_bank_upi.py         # HIS vs Bank UPI
    his_momentspay.py       # HIS vs MomentsPay (3-way)
    amex.py                 # AMEX
  tests/
    test_card.py
    test_upi.py
    test_his_bank.py
    fixtures/               # Sample XLSX/CSV rows for tests

services/recon_report_py/
  main.py
  requirements.txt
  Dockerfile
  docker-compose.yml
  writer.py                 # openpyxl sheet writer
  summary.py                # Location-grouped summary
  sheet_names.py            # Naming convention per recon type
  tests/
    test_writer.py
```

### Config changes
```
config/cloudi.conf          # Add recon_engine_py and recon_report_py entries
```