# TASK: Build the POS Transactions screen in TMS_UAT Implement the **Payments → Transactions → POS** payment channel in TMS_UAT (currently marked "SOON" in the left nav). It must display POS switch transaction data with the same grid, tabs, filters and CSV export as the existing Core Transactions / Refunds screen. --- ## 1. Data source POS data is **not** in the TMS database. It lives in the switch database: - **Database:** `shukria_transactions` (use the UAT instance of this schema) - **Driver:** MySQL 8, `utf8mb4` - Existing app reference: Laravel connection named `shukria_transactions`, DB name from env `SHUKRIA_TRANS_DB` > **Decision needed up front:** TMS already has a `pos_transactions` table, but its shape is much thinner > (`transaction_id, merchant_id, terminal_id, amount, currency, card_type, card_number_masked, mcc_code, > status, transaction_time, location, country_code, settlement_date, batch_number, authorization_code, > reference_number, response_code, additional_data, inserted_at, updated_at`). > It cannot represent the switch fields below. Either **(a)** add a read-only second Repo pointing at > `shukria_transactions` and query it directly, or **(b)** extend/replace the local table and sync. > Option (a) is recommended — the switch DB is the system of record and already carries full history. --- ## 2. Primary table ### `pos_transaction` — the main POS switch transaction table (~5,639 rows in UAT) `s_txn_type` is the transaction-type discriminator. Current distribution: | s_txn_type | rows | |---|---| | SALE | 4795 | | REFUND | 446 | | VOID_SALE | 297 | | PREAUTH | 41 | | BALANCE_INQUIRY | 17 | | PREAUTH_COMPLETE | 5 | **Full column list:** ``` id (bigint PK) s_txn_type s_tid s_mid s_tid_stan s_tid_invoiceno s_tid_batchno b_tid b_mid b_tid_stan b_tid_invoiceno b_tid_batchno b_tid_date b_tid_time acquirer_id entry_mode condition_code currency_code mti proc_code total_amount auth_amount cash_amount tip_amount approval_code reference_no response_code response_message closure_status mcc_code masked_card_no pan_seq emv_data acquirer_reference_no scheme_reference_no created_dateTime updated_dateTime completed_dateTime metadata order_number parent_txn_id encrypted_track2 encrypted_pan hash_pan encrypted_expiry <-- NEVER expose (see §7) ``` Prefix convention: `s_*` = **source/store side** (the merchant's own TID/MID as issued by us), `b_*` = **bank/acquirer side** (the acquirer's TID/MID and their STAN/batch/date/time). **Display the `s_*` values in the grid** — those are what merchants recognise. --- ## 3. Tab → table mapping The RECORDS tab strip must be: `Core Transactions | Refund | Void | Reversal | Failed` | Tab | Query | |---|---| | **Core Transactions** | `pos_transaction` — all rows, no type filter | | **Refund** | `pos_transaction WHERE s_txn_type = 'REFUND'` → 446 rows | | **Void** | `pos_transaction WHERE s_txn_type = 'VOID_SALE'` → 297 rows. Also surface `pos_void_requests` (31 rows) as the void attempt/response log — join `pos_void_requests.original_pos_transaction_id = pos_transaction.id` | | **Reversal** | `pos_transaction_reversal` → 480 rows (separate table, different shape — see §4) | | **Failed** | `pos_failed_transaction` → 1,058 rows (same shape as `pos_transaction` minus `closure_status`, `hash_pan`, `completed_dateTime`) | --- ## 4. Sibling tables ### `pos_transaction_reversal` (480 rows) Different schema — it stores the reversal request plus a snapshot of the original transaction: ``` id s_txn_type original_temp_txn_id reversal_reason reversal_status s_tid s_mid s_tid_stan b_tid b_mid b_tid_stan acquirer_id reversal_mti original_mti original_proc_code original_amount original_stan original_time original_date original_entry_mode original_pan_seq original_reference_no original_currency_code original_batch_no original_pan_seq reversal_request reversal_response reversal_response_code reversal_reference_no retry_count max_retry_attempts next_retry_time error_message created_dateTime initiated_dateTime completed_dateTime updated_dateTime metadata original_encrypted_pan original_encrypted_expiry_date <-- NEVER expose ``` `reversal_status` values: `COMPLETED` (445), `MAX_RETRIES_EXCEEDED` (26), `RETRY_SCHEDULED` (6), `FAILED` (3). For the grid, map `original_*` fields into the shared columns (`original_reference_no` → RRN, `original_amount` → Amount, `original_stan` → STAN, `original_batch_no` → Batch #). ### `pos_void_requests` (31 rows) ``` id original_pos_transaction_id request_stan status attempt_count response_code approval_code response_rrn error_message created_at updated_at ``` `status` values: `APPROVED` (27), `FAILED` (3), `DECLINED` (1). Note this table uses `created_at`/`updated_at`, **not** the `*_dateTime` convention. ### `pos_failed_transaction` (1,058 rows) Same columns as `pos_transaction` except no `closure_status`, `hash_pan`, or `completed_dateTime`. Dominant failure reason: `response_code = '96'`, `response_message = 'Manual cleanup invoked'` (815 rows). --- ## 5. Grid column mapping (verified against live rows) Render these columns, in this order: | UI column | Source | Notes | |---|---|---| | **#** | `pos_transaction.id` | bigint, e.g. `1786080748970`. Truncate with ellipsis in the cell, full value in tooltip/detail | | **Source** | literal `'POS'` | Constant for this channel. Keep the column so the grid stays shape-compatible with QR/E-Comm channels later | | **Txn Type** | `s_txn_type` | Render as a coloured chip | | **RRN** | `TRIM(reference_no)` | **Must TRIM** — values carry trailing spaces, e.g. `'81933366324 '` | | **TID** | `s_tid` | 8 chars | | **Auth #** | `approval_code` | 6 chars | | **STAN** | `s_tid_stan` | 6 chars, zero-padded | | **Invoice #** | `s_tid_invoiceno` | **NULL on almost every row today** — column will render empty. Keep it (data is expected later) but don't treat blank as a bug | | **Batch #** | `s_tid_batchno` | 6 chars, zero-padded | | **Merchant MID** | `s_mid` | 15 chars | | **Merchant Ref** | `order_number` | Nearest equivalent in this table; e.g. `ORD1786047921862`. May be null | | **Amount** | `total_amount` | `decimal(12,2)`. Format with `currency_code` — it is **ISO 4217 numeric**, not alpha: `784` = AED. Map numeric→alpha for display | | **Risk Hold Action** | `risk_rule_hits.action_taken` | Join `risk_rule_hits.transaction_id = pos_transaction.id` (and `transaction_type`). 394 rows in that table. Also exposes `rule_id`, `category`, `status`, `triggered_at`, `supervisor_id` | | **Status** | derived | See below | **Status derivation:** - Approved when `response_code = '00'`; otherwise declined/failed — surface `response_message`. Known codes: `00` Approved, `03` Invalid Merchant, `78` Checksum validation failed, `96` Switch communication failed. - `closure_status` is a separate axis: `OPEN` (2,977) vs `SHIFT_CLOSED` (2,662). Show it as its own badge or a secondary line — do **not** conflate it with the approval status. - Reversal tab uses `reversal_status`; Void tab uses `pos_void_requests.status`. **Reference row** (top row of the Refunds tab — use this to verify your mapping): ``` id 1786080748970 | s_txn_type REFUND | s_tid 57873811 | s_mid 607578730000000 s_tid_stan 000354 | s_tid_invoiceno NULL | s_tid_batchno 000021 approval_code 169650 | reference_no '81933366324 ' | total_amount 1.00 currency_code 784 | response_code 00 | closure_status SHIFT_CLOSED order_number ORD1786047921862 | created_dateTime 2026-08-07 06:40:04 ``` --- ## 6. Lookup joins | Need | Join | |---|---| | Terminal details | `pos_transaction.s_tid` → `pos_terminals.terminalid` (607 rows: `name, serial_number, device_type, terminal_id, store_id, provider_id, status, latitude, longitude, assigned_date`). Legacy thinner table `pos_terminal` (334 rows: `id, terminalid, serial_number, pos_merchant_id`) also exists — prefer `pos_terminals` | | Merchant name | `pos_transaction.s_mid` → `pos_merchant.merchantid` (254 rows: `merchantid, merchant_name, address_id`) | | Settlement | `pos_settlements.pos_transaction_id` → `pos_transaction.id` (41 rows: `settlement_batch_id, settlement_date, settlement_status, rejection_reason, card_type_id, scheme_name, synced_to_core, synced_at`) | | Store | `pos_terminals.store_id` → `stores` (458 rows) | | Terminal history | `pos_log_terminal` (serial-number change log), `pos_terminal_version`, `pos_terminal_acquirer_terminal`, `pos_terminal_data` (STAN/batch counters) | --- ## 7. Hard requirements **PCI — never select, log, export or return these columns:** `encrypted_pan`, `encrypted_track2`, `encrypted_expiry`, `hash_pan`, `emv_data`, `original_encrypted_pan`, `original_encrypted_expiry_date`. Use `masked_card_no` only. Enforce this in the query layer, not just the view — the CSV export must be built from the same whitelist. **Do NOT source the grid from `core_transactions`.** That table is the normalised cross-channel view and its columns map 1:1 to the UI, which makes it tempting — but it holds only 151 rows total, 95 of them POS, and only 29 of those actually join back to `pos_transaction`. The POS rows currently in it are seeded test data (`rrn = 'MT806000011'`, `auth_number = 'A00011'`). `pos_transaction` is the system of record. If a normalised layer is wanted later, backfill `core_transactions` from `pos_transaction` (`source_type = 'POS'`, `source_ref_id = pos_transaction.id`) as a separate task. **Column naming:** `pos_transaction` uses `created_dateTime` / `updated_dateTime` (camelCase, mixed case in MySQL) — this breaks the `inserted_at` / `updated_at` convention used everywhere else in TMS. If you are mapping with Ecto, declare explicit `field :created_datetime, :naive_datetime, source: :created_dateTime` and quote the identifiers in raw SQL. --- ## 8. UI requirements - **Filters:** Merchant MID (prefix search on `s_mid`), Date From, Date To (range on `created_dateTime`), plus Apply / Reset. Keep the per-column filter inputs in the header row. - **Result count:** `Showing X of Y` in the header. - **Export CSV:** must respect the active tab + all filters. **Stream the response** — do not buffer the full result set into memory, and do not build it client-side. Large exports have previously caused 500s on this data. - **Pagination:** server-side, mandatory. 5.6k rows today and growing continuously from live terminals. - **Sorting:** default `created_dateTime DESC`. - **Empty states:** distinguish "no data" from "no match for filters". **Indexes already present on `pos_transaction`** (design queries around these): `s_tid`, `b_tid`, `mti`, `total_amount`, `approval_code`, `response_code`, `created_dateTime`. Note `s_mid` is **not** indexed — add an index if MID filtering is slow. --- ## 9. Optional: transaction detail view If you build a row detail/drill-down, an existing cloud-layer API already serves it: - Base URL: `CLOUD_LAYER_API_BASE_URL` (UAT: `http://demo.ctrmv.com:6001/api`) - `GET /getCardTransactionById/{id}?action=getById&id={id}&status={status}` — standard POS transaction - `GET /getReversalCardTransactionById/{id}?action=getById&id={id}` — use when the source table is `pos_transaction_reversal` - `GET /getCustomLogFromTxnId?transaction_id={id}` — event log for the transaction Reusing this API keeps the detail view consistent with the existing admin panel and avoids duplicating the PCI-masking logic. The list grid should still read the DB directly for performance. --- ## 10. Acceptance criteria 1. POS entry in the left nav is live (no "SOON" badge) and routes to the transactions grid. 2. All five tabs render with correct counts: Core Transactions 5,639 · Refund **446** · Void 297 · Reversal 480 · Failed 1,058. 3. The first Refunds row matches the reference row in §5 field-for-field, including `RRN 81933366324` with no trailing whitespace and a blank Invoice #. 4. MID + date-range filters change the `Showing X of Y` count correctly and carry into the CSV export. 5. No PAN, track2, expiry, PAN hash or EMV data appears in any response, export, or log line. 6. Grid loads within normal page budget at 5k+ rows with server-side pagination.