# Cross-System Entity Resolution — Verified, Not Assumed

**Status:** Draft for review
**Parent doc:** [Proposal_arch.md](Proposal_arch.md) §1.8
**Scope:** How merchant / device / terminal / MID-TID identity actually correlates across TMS, Cloud Layer, the jPOS switch, and the Merchant Management System — checked against code, not the RFP feature docs.

---

## 1. Why this exists

Transaction lineage (§1.5/§1.6 of the parent doc — RRN/STAN → settlement → payout) answers *"what happened to this transaction."* It doesn't answer a different, more foundational question an investigation or a risk agent will also need: *"which merchant is this, and what is their identity in every other system?"* — e.g. *MMS merchant → jPOS MID/TID → live transaction*, or *MMS merchant → TID → device serial number → TMS terminal fleet*.

This document was written by grepping and reading the actual schemas rather than trusting the RFP feature docs in [connected-apps/](connected-apps/). That distinction matters — see §3.

---

## 2. What is verified

### 2.1 MMS merchant → transaction (verified, working, in `settlement_core`)

```
SettlementCore.Mms.MerchantMetadata          -- table: user_metadata (via ShukriaMmsRepo)
  user_id, merchant_id, mc_code, merchant_refrence_number, currency_code,
  transaction_currency, country_code, group_code, bank_code, bankUser_id

SettlementCore.Mms.MmsUser                   -- table: users (via ShukriaMmsRepo)
  name, email, phone, status, contract_approval_status, mcc_id, products

SettlementCore.CoreTransactionSync.get_merchant_metadata/1
  (settlement_core/lib/settlement_core/core_transaction_sync.ex:586-596)

  defp get_merchant_metadata(merchant_mid) do
    query = from meta in MerchantMetadata,
            where: meta.merchant_refrence_number == ^merchant_mid, limit: 1
    ShukriaMmsRepo.one(query)
  end

Also a raw-SQL join at core_transaction_sync.ex:315:
  LEFT JOIN {mms_db}.user_metadata u ON t.merchant_id = u.merchant_id
```

This is real, tested, in-production code. **This is the only verified path from a `core_transactions` row to MMS merchant identity**, and it's the one A5/A6 tools should call through — not a new direct query against `ShukriaMmsRepo`.

Other verified MMS-side schemas, all living in `settlement_core/lib/settlement_core/mms/`, all reachable the same way: `MerchantStore` (`merchant_stores`), `MdrTemplate`/`MdrRate`/`MccMdrRate`, `CardType`, `Mcc`, `KycRequest` (`kyc_requests`), `VirtualIban`, `Merchant` (`merchants` — a separate, older table from `MmsUser`/`users`).

**Correction to Proposal_arch.md §1.6:** that section named `merchant_configuration` as an MMS table, sourced from the Cloud Layer RFP doc's text. No `merchant_configuration` schema exists anywhere in this codebase. The verified table for per-merchant settings is `merchant_stores`, and the verified field for the correlation key is `merchant_refrence_number` (misspelled, no "e" — this is the real column name, not a typo to fix in queries).

**Correction to `PlatformCore.ShukriaMmsRepo`'s protection level:** the investigation doc (§6) described the read-only guarantee as something to "enforce independently... not trusted to the connection config." That undersold it. The actual Repo definition is:

```elixir
# apps/platform_core/lib/platform_core/shukria_mms_repo.ex
use Ecto.Repo, otp_app: :platform_core, adapter: Ecto.Adapters.MyXQL, read_only: true
```

`read_only: true` is an Ecto adapter-level guarantee — Ecto refuses to issue non-`SELECT` statements through this Repo at all. That's a real driver-level boundary, not just a naming convention. Still worth a defensive check in `AgentCore.ReadTool` (belt and suspenders), but the underlying protection is stronger than previously stated.

### 2.2 jPOS TID/MID mapping (verified, exists, differently named than the RFP doc claims)

```
DaProductApp.PosTransactions.PosTerminal              -- table: pos_terminal (singular)
  terminalid, serial_number, pos_merchant_id (integer FK)

DaProductApp.PosTransactions.PosTerminalAcquirerTerminal   -- table: pos_terminal_acquirer_terminal
  pos_terminal_id, acquirer_terminal_id

DaProductApp.Acquirer.AcquirerTerminal                 -- table: acquirer_terminal
  terminalid, acquirer_id, acquirer_merchant_id

DaProductApp.Acquirer.AcquirerMerchant                 -- table: acquirer_merchant
  acquirer_id, merchantid, mcc, address_id
```

Chain: `pos_terminal` → `pos_terminal_acquirer_terminal` → `acquirer_terminal` → `acquirer_merchant`. This is a real, code-backed path from a POS terminal to its bank-assigned TID/MID and acquirer merchant record.

**Correction to Proposal_arch.md §1.6 and the Cloud Layer RFP doc:** the RFP doc's named endpoints — `checkDuplicateTidMid`, `updateShukriaProviderMidTid`, `updateShukriaYspMidTid` — have **zero code backing them anywhere in `apps/da_product_app`.** Grep for the literal strings returns nothing outside the RFP doc itself. Either these endpoints exist in a different codebase version, are planned/aspirational, or are named differently than documented. Treat them as unverified until someone finds the actual implementation.

---

## 3. The important finding: the RFP feature docs are not ground truth for tool design

The [connected-apps/](connected-apps/) RFP feature docs (`cloud-layer.md`, `device-middle-layer.md`, `merchant-management.md`) describe a target feature set — the kind of document written for an RFP submission, not a code audit. Two concrete discrepancies found while building this map:

1. **The "group → brand → store → device" merchant hierarchy** (Cloud Layer RFP §7, "Merchant Hierarchy API," `GET /api/merchant/hierarchy`) has **no corresponding Ecto schema anywhere in `apps/da_product_app`.** No `stores`, `brands`, or `groups` table/migration exists. The closest thing in the codebase — `terminal_groups`/`terminal_group_memberships` — is a TMS *terminal-grouping* feature, unrelated to a merchant org-chart.
2. **`checkDuplicateTidMid` and friends** (§2.2 above) — documented, not implemented, or implemented somewhere this grep didn't reach.

This isn't a criticism of the RFP docs — they're doing their job (describing what the product does or will do for a proposal). It **is** a reason to apply the same discipline going forward: **before wiring any agent tool that depends on a claim from those docs, verify it against the actual schema/module, the way this document did.** An agent tool built against a documented-but-nonexistent endpoint fails at runtime, or worse, silently returns nothing and the agent reasons from an empty result set without knowing why.

---

## 4. The gap: what does NOT verifiably join today

This is the part worth being honest about before promising the user's example (*MMS → merchant → TID → device serial number → TMS devices*) as a working capability.

```
TMS side:                          Cloud Layer / jPOS side:
tms_terminals.serial_number        pos_terminal.serial_number
tms_terminals.merchant_id (string) pos_terminal.pos_merchant_id (integer FK)
                                       ↓
                                    pos_terminal_acquirer_terminal
                                       ↓
                                    acquirer_terminal → acquirer_merchant
```

Both `serial_number` columns exist, are the same name, and are plausibly the same physical identifier. **But no code anywhere joins them.** No foreign key, no shared lookup function, no query in either app that compares `tms_terminals.serial_number` to `pos_terminal.serial_number`. Same problem one level up: `tms_terminals.merchant_id` is a bare string with no code path connecting it to `pos_terminal.pos_merchant_id` (an integer) or to `MerchantMetadata.merchant_refrence_number` (verified in §2.1).

**This means the walk the user described — device → merchant → MID/TID → live transaction — is not a single verified query today. It's a 3-hop chain with one confirmed link (§2.1, MMS↔core_transactions) and one unconfirmed link (TMS↔Cloud Layer identity).**

Two ways this resolves, and it needs a decision before any tool is built on top of it:

- **If the values genuinely correspond 1:1 in production data** (e.g. `tms_terminals.merchant_id` always equals `MerchantMetadata.merchant_refrence_number` for the same merchant, and `pos_terminal.serial_number` always equals `tms_terminals.serial_number` for the same physical device), then the fix is cheap: write and test a resolver function once, add it to `settlement_core` or a shared location, and every agent tool calls through it.
- **If they don't reliably correspond** (different ID spaces, stale data, manual mismatches from onboarding), then no tool should silently assume the join — A6's `trace_transaction/1`-style walker (parent doc, investigation design) needs to report this hop as `:unresolved`, the same way it reports a broken settlement hop as `:absent`, rather than guessing.

**Recommended action (Phase 0, before any tool depends on this):** a short, targeted data-quality spike — run a read-only query joining `tms_terminals.merchant_id` against `MerchantMetadata.merchant_refrence_number` and `pos_terminal.serial_number` against `tms_terminals.serial_number` on a representative slice of production/staging data, and report the actual match rate. This is a half-day check that prevents building an agent capability on a join that turns out to be unreliable.

### Spike results (run against the local UAT dataset — re-run against staging/production-shaped data before finalizing thresholds)

Three concrete findings, all with numbers:

**1. `tms_terminals.merchant_id` is 100% unpopulated.** `SELECT COUNT(DISTINCT merchant_id) FROM tms_terminals WHERE merchant_id IS NOT NULL AND merchant_id != ''` → **0**, across all 44 rows. This isn't a mismatch problem, it's a data-population problem — the field exists in the schema but nothing writes to it today. The device→merchant hop cannot work from this field as-is, regardless of what it's supposed to join to. This needs either a backfill or a different resolution path before any tool relies on it.

**2. `tms_terminals.serial_number` ↔ `pos_terminal.serial_number` works when populated, but `pos_terminal.serial_number` is sparse.** 15 of 44 TMS terminals (34%) have a matching `pos_terminal` row by serial number. But `pos_terminal.serial_number` itself is `NULL` on 387 of 417 rows (only 23 distinct non-null values, and 8 of those are the placeholder `"123-123-123"`) — so the real match rate against *populated* Cloud Layer records is 15/23 (65%), not 15/417. Read as: **the join is structurally sound, but the data feeding it is incomplete on the Cloud Layer side.**

**3. `core_transactions.merchant_mid` ↔ MMS `user_metadata.merchant_refrence_number` (the verified §2.1 resolution path) genuinely works.** 4 of 17 distinct `merchant_mid` values in `core_transactions` resolve to a real MMS merchant record (`Mercury_CB6F6A5`, and three numeric-format IDs); the rest are UAT seed/test values (`TESTMID_FULL`, `TESTMID_SEED`, ...) that were never expected to resolve. This is the strongest positive finding in this spike — `SettlementCore.CoreTransactionSync.get_merchant_metadata/1` is resolving against real, matching data, not just plausible-looking code.

**A bug the spike surfaced, worth fixing regardless of anything else:** every one of these joins failed on a **collation mismatch** until explicitly cast (`COLLATE utf8mb4_general_ci` / `COLLATE utf8mb4_unicode_ci`). The tables involved were created with three different default collations:

| Table | Collation |
|---|---|
| `tms_terminals` | `utf8mb4_general_ci` (column-level) |
| `pos_terminal`, `core_transactions` | `utf8mb4_0900_ai_ci` (table default) |
| `user_metadata` (MMS) | `utf8mb4_unicode_ci` (column-level) |

A plain Ecto `join`/`where` clause across any of these pairs will raise `Illegal mix of collations` at query time unless it explicitly casts one side. This likely already affects the raw-SQL join in `CoreTransactionSync` (`core_transaction_sync.ex:315`, `LEFT JOIN {mms_db}.user_metadata u ON t.merchant_id = u.merchant_id`) — worth checking whether that specific join has ever actually returned rows in production, or whether it's been silently failing/erroring the same way. **Any new `AgentCore` tool that joins across these tables must cast collation explicitly** — this is now a concrete implementation requirement, not a hypothetical one.

**Net read for tool design:** the merchant↔MMS resolution path (§2.1) is safe to build on now. The device↔Cloud Layer serial-number join is usable but will under-report until Cloud Layer's data completeness improves. The device↔merchant hop via `tms_terminals.merchant_id` cannot be built at all until that field is populated — `trace_transaction`-style tools should treat it as `:unresolved` unconditionally today, not conditionally.

---

## 5. What this means for agent design

- **A6 (Investigation) and A5 (Risk Triage)** should call merchant-identity resolution through `SettlementCore.Mms.MerchantMetadata` / `SettlementCore.CoreTransactionSync.get_merchant_metadata/1` — the one verified, already-tested path — not through a new direct query invented against `ShukriaMmsRepo`.
- **The TID/MID chain** (`pos_terminal` → `pos_terminal_acquirer_terminal` → `acquirer_terminal` → `acquirer_merchant`) is real and can be exposed as a read tool today, scoped to `apps/da_product_app`.
- **The TMS-side device→merchant identity link is not yet provably correct** — see §4. No tool should assume it silently. This is a concrete, scoped precondition for the "device serial number → TMS devices" half of the user's example, and it should be resolved (or explicitly marked unreliable) before A1/A6 build any tool that depends on it.
- **Every future claim sourced from the RFP docs** in `connected-apps/` needs the same verify-before-build treatment applied here — grep and read, don't cite the feature doc as if it were the schema.