Today's Topic

Database Tables

The database is where every transaction leaves a footprint. Today you learn the exact tables that record everything in the MPG system — and how to use them to find stuck or failed transactions.

Audit Logs Transaction Tables Stuck Transactions DB Validation

01 The Simple Idea First

Real-life Analogy

Think of the database tables like the different registers in a post office.

One register records every parcel that came in. Another records where each parcel is right now. Another records parcels that got stuck or returned. And one master register logs every single action any staff member performed.

When a parcel goes missing, you check the registers. You find where it was last recorded, what happened to it, and why it stopped moving. That's exactly what you do as L2 when a transaction gets stuck — you check the database tables.

02 The 6 MPG Tables — Every Column Explained

📋

MPG_AUDIT_LOG

"The master diary — records every action the system performed"

Audit

Column	What it stores	Type
LOG_ID	Unique ID for each log entry — every action gets its own number	PK
REQUEST_ID	Links this log to a specific payment request or transaction	FK
ACTION_TYPE	What action happened — e.g. PAYMENT_SENT, VALIDATION_DONE, RESPONSE_RECEIVED	Important
STATUS	Result of the action — SUCCESS, FAILED, or PENDING	Important
SERVICE_NAME	Which component performed the action — e.g. MPG-Sender, SAF-Job
CREATED_AT	Exact timestamp of when the action happened	Important
ERROR_CODE	If it failed — what error code was returned

Query example

-- Find all failed actions for a specific request
SELECT * FROM MPG_AUDIT_LOG
WHERE STATUS = 'FAILED'
AND REQUEST_ID = 'REQ-4421'
ORDER BY CREATED_AT ASC;

💡 L2 Use: First table to check when a client reports an issue. Find the exact action that failed and when it happened.

🔍

MPG_AUDIT_LOG_DETAILS

"The full message body — what was sent and what came back"

Audit Detail

Column	What it stores	Type
DETAIL_ID	Unique ID for this detail record	PK
LOG_ID	Links to the parent record in MPG_AUDIT_LOG	FK
REQUEST_BODY	The full raw message that was sent — the complete XML or JSON payload	Important
RESPONSE_BODY	The full raw reply that came back — contains the actual error detail or success message	Important
CREATED_AT	When this detail was recorded

Query example

-- Get full request and response for a failed log entry
SELECT REQUEST_BODY, RESPONSE_BODY
FROM MPG_AUDIT_LOG_DETAILS
WHERE LOG_ID = 'LOG-8821';

💡 L2 Use: When you find a failed log in AUDIT_LOG, come here to read the exact error message SBP or the gateway returned. The RESPONSE_BODY tells you the real reason.

📦

SENDER_BATCH

"One record per bulk payment file submitted by a client"

Batch

Column	What it stores	Type
BATCH_ID	Unique ID for the entire batch — your main reference number	PK
CLIENT_ID	Which bank or client submitted this batch	FK
BATCH_STATUS	Overall status — RECEIVED, PRE-VALIDATING, PROCESSING, COMPLETED, FAILED	Important
TOTAL_TXN	Total number of transactions in this batch
SUCCESS_COUNT	How many transactions succeeded so far
FAILED_COUNT	How many transactions failed	Important
SUBMITTED_AT	When the client submitted this batch
COMPLETED_AT	When the entire batch finished processing — NULL if still running	Important

Query example

-- Check status of a specific batch
SELECT BATCH_ID, BATCH_STATUS, TOTAL_TXN,
SUCCESS_COUNT, FAILED_COUNT, COMPLETED_AT
FROM SENDER_BATCH
WHERE BATCH_ID = 'BATCH-2024-001';

-- Find all batches still stuck in PROCESSING
SELECT * FROM SENDER_BATCH
WHERE BATCH_STATUS = 'PROCESSING'
AND COMPLETED_AT IS NULL;

💡 L2 Use: Client says "our batch submitted 2 hours ago is still not complete." You query SENDER_BATCH — if COMPLETED_AT is NULL and status is still PROCESSING, the batch is stuck.

📄

SENDER_BATCH_DETAIL

"One row per transaction inside a batch — the individual records"

Transaction

Column	What it stores	Type
DETAIL_ID	Unique ID for this individual transaction record	PK
BATCH_ID	Links back to the parent batch in SENDER_BATCH	FK
TXN_ID	The individual transaction ID — what the client uses to track a single payment	Important
AMOUNT	How much money this transaction is for
BENEFICIARY_IBAN	The receiver's account number — checked during pre-validation
TXN_STATUS	Status of this specific transaction — PENDING, SUCCESS, FAILED, REJECTED	Important
REJECT_REASON	If rejected — why. Contains the rejection code from SBP	Important
PROCESSED_AT	When this specific transaction was processed — NULL if still waiting

Query example

-- Find all failed transactions in a specific batch
SELECT TXN_ID, AMOUNT, TXN_STATUS, REJECT_REASON
FROM SENDER_BATCH_DETAIL
WHERE BATCH_ID = 'BATCH-2024-001'
AND TXN_STATUS = 'FAILED';

-- Find transactions still PENDING (stuck) in a batch
SELECT TXN_ID, AMOUNT, PROCESSED_AT
FROM SENDER_BATCH_DETAIL
WHERE BATCH_ID = 'BATCH-2024-001'
AND TXN_STATUS = 'PENDING'
AND PROCESSED_AT IS NULL;

💡 L2 Use: Client asks "which specific transactions in our batch failed?" You query this table filtered by BATCH_ID and TXN_STATUS = FAILED. You also see the reject reason — so you know exactly why each one failed.

📨

MPG_SAF_RESPONSE

"Stores SBP's replies — ACSP or RJCT — for each transaction"

SBP Reply

Column	What it stores	Type
RESPONSE_ID	Unique ID for this response record	PK
BATCH_ID	Which batch this response belongs to	FK
TXN_ID	Which specific transaction SBP is replying about	Important
RESPONSE_CODE	ACSP = accepted in progress / RJCT = rejected by SBP	Important
REASON_CODE	If RJCT — the specific reason code SBP sent back (e.g. AC01 = invalid account)	Important
RAW_RESPONSE	The full Pacs002 XML message from SBP — complete raw reply
RECEIVED_AT	When SBP sent this reply
FORWARDED	Was this response successfully forwarded to the channel? Y or N	Important

Query example

-- Check SBP's reply for a specific transaction
SELECT TXN_ID, RESPONSE_CODE, REASON_CODE, FORWARDED
FROM MPG_SAF_RESPONSE
WHERE TXN_ID = 'TXN-9823';

-- Find responses received but NOT yet forwarded to channel
SELECT * FROM MPG_SAF_RESPONSE
WHERE FORWARDED = 'N';

💡 L2 Use: Client says "SBP rejected our transaction but we don't know why." You come here, find the REASON_CODE — that's the exact rejection reason from SBP. Also check FORWARDED = N to find replies stuck and not delivered back to the client.

🔄

MPG_SAF_TRANSACTIONS

"The Store and Forward queue — transactions waiting to be forwarded"

SAF Queue

Column	What it stores	Type
SAF_ID	Unique ID for this SAF queue entry	PK
TXN_ID	Which transaction is being held for forwarding	Important
BATCH_ID	Which batch this transaction belongs to	FK
PAYLOAD	The actual message to be sent — full XML or JSON
SAF_STATUS	PENDING = waiting to be sent / SENT = already forwarded / FAILED = gave up after retries	Important
RETRY_COUNT	How many times the SAF Job has tried to send this — if high, something is wrong	Important
LAST_RETRY_AT	When was the last attempt made to forward this
CREATED_AT	When this entry was added to the queue

Query example

-- Find all transactions stuck in SAF queue (PENDING)
SELECT TXN_ID, SAF_STATUS, RETRY_COUNT, CREATED_AT
FROM MPG_SAF_TRANSACTIONS
WHERE SAF_STATUS = 'PENDING'
ORDER BY CREATED_AT ASC;

-- Find transactions with high retry count — repeatedly failing
SELECT * FROM MPG_SAF_TRANSACTIONS
WHERE RETRY_COUNT > 3;

💡 L2 Use: Client says "transaction was processed by SBP but we never received the reply." You check here — if SAF_STATUS = PENDING and RETRY_COUNT is high, the SAF Job is struggling to forward it. Escalate to L3 to manually reprocess.

03 How All 6 Tables Connect

The Full Picture — One Batch, Six Tables

SENDER_BATCH
batch submitted

→

SENDER_BATCH_DETAIL
individual transactions

→

MPG_SAF_TRANSACTIONS
queued for forwarding

↓

MPG_AUDIT_LOG
every action logged

→

MPG_AUDIT_LOG_DETAILS
full message bodies

→

MPG_SAF_RESPONSE
SBP reply stored

04 Hands-on Lab — Find a Stuck Transaction

Scenario: Client reports a batch submitted 3 hours ago is still not complete

Your job is to go into the database, find the batch, identify which transactions are stuck, understand why, and validate whether this is a DB issue or an external issue. Here is the exact investigation path.

🔬 Lab: Trace a Stuck Transaction Through the DB

MPG Schema · SQL Investigation

Step 1 — Check the batch status in SENDER_BATCH

First confirm the batch exists and check its overall status.

query

SELECT BATCH_ID, BATCH_STATUS, TOTAL_TXN,
SUCCESS_COUNT, FAILED_COUNT, COMPLETED_AT
FROM SENDER_BATCH
WHERE BATCH_ID = 'BATCH-2024-001';

⚠️ Result: BATCH_STATUS = 'PROCESSING', COMPLETED_AT = NULL, TOTAL_TXN = 500, SUCCESS_COUNT = 487, FAILED_COUNT = 0 — 13 transactions unaccounted for.

Step 2 — Find the stuck transactions in SENDER_BATCH_DETAIL

Drill into the individual transactions — find which ones are still PENDING.

query

SELECT TXN_ID, AMOUNT, TXN_STATUS, PROCESSED_AT
FROM SENDER_BATCH_DETAIL
WHERE BATCH_ID = 'BATCH-2024-001'
AND TXN_STATUS = 'PENDING'
AND PROCESSED_AT IS NULL;

🔴 Result: 13 transactions returned — all PENDING, all with PROCESSED_AT = NULL. These are your stuck transactions.

Step 3 — Check if SBP already replied for these transactions

Check MPG_SAF_RESPONSE — did SBP send back a reply that was not forwarded?

query

SELECT TXN_ID, RESPONSE_CODE, FORWARDED, RECEIVED_AT
FROM MPG_SAF_RESPONSE
WHERE BATCH_ID = 'BATCH-2024-001'
AND FORWARDED = 'N';

⚠️ Result: 13 rows found — RESPONSE_CODE = ACSP, FORWARDED = N. SBP already replied hours ago but the replies were never forwarded to the channel.

Step 4 — Check the SAF queue for these transactions

Check MPG_SAF_TRANSACTIONS to see if the SAF Job is stuck trying to forward them.

query

SELECT TXN_ID, SAF_STATUS, RETRY_COUNT, LAST_RETRY_AT
FROM MPG_SAF_TRANSACTIONS
WHERE BATCH_ID = 'BATCH-2024-001'
AND SAF_STATUS = 'PENDING';

🔴 Result: 13 rows, all PENDING, RETRY_COUNT between 5 and 8, LAST_RETRY_AT = 2 hours ago. The SAF Job stopped retrying.

Step 5 — Check the audit log to see what error the SAF Job is hitting

Check MPG_AUDIT_LOG for failed actions related to these transactions.

query

SELECT ACTION_TYPE, STATUS, ERROR_CODE, CREATED_AT
FROM MPG_AUDIT_LOG
WHERE REQUEST_ID = 'BATCH-2024-001'
AND STATUS = 'FAILED'
ORDER BY CREATED_AT DESC;

✅ Root Cause Found: Audit log shows ERROR_CODE = 'CHANNEL_ENDPOINT_UNREACHABLE'. The client's endpoint (where we send the reply) is down. SAF Job tried 8 times, all failed. Issue is on the client side — their receiving endpoint is not responding.

05 DB Validation Result — What to Report

📊 Transaction Validation Summary — BATCH-2024-001

Check	Table Checked	Finding	Result
Batch exists and was received	SENDER_BATCH	Batch found, submitted 3 hours ago	✓ OK
All transactions submitted	SENDER_BATCH_DETAIL	500 transactions present in DB	✓ OK
SBP processed and replied	MPG_SAF_RESPONSE	All 500 replies received from SBP	✓ OK
Replies forwarded to channel	MPG_SAF_RESPONSE	13 replies not forwarded — FORWARDED = N	⚠ STUCK
SAF Job processing replies	MPG_SAF_TRANSACTIONS	13 entries PENDING, retry count 5-8	⚠ STUCK
Root cause identified	MPG_AUDIT_LOG	CHANNEL_ENDPOINT_UNREACHABLE — client's endpoint is down	✗ CLIENT SIDE

06 Real L2 Scenarios

Client: "Transaction TXN-5541 shows PENDING since morning." Check SENDER_BATCH_DETAIL — TXN_STATUS = PENDING, PROCESSED_AT = NULL. Then check MPG_SAF_TRANSACTIONS — RETRY_COUNT = 6. Then AUDIT_LOG — ERROR: DB_CONNECTION_TIMEOUT. Your system's DB is struggling. Escalate to DBA.

Client: "SBP rejected some transactions but we don't know which ones or why." Query MPG_SAF_RESPONSE with RESPONSE_CODE = 'RJCT'. You get a list with REASON_CODEs — e.g. AC01 (invalid account), AM04 (insufficient funds). You give the client the exact list with reasons in minutes.

Manager: "How many batches are still stuck in PROCESSING right now?" Query SENDER_BATCH WHERE BATCH_STATUS = 'PROCESSING' AND COMPLETED_AT IS NULL. You give the exact count and list of stuck batches immediately — no guessing.

Client: "We received some replies but not all — 13 are missing." Query MPG_SAF_RESPONSE WHERE FORWARDED = 'N'. You find the exact 13 — SBP replied but the SAF Job couldn't deliver them. You identify the issue and coordinate with the team to manually reprocess those 13 entries.

✅ Week 2 · Day 4 Outcomes — Can You Do This?

Identify the role of each of the 6 MPG database tables and what data each one holds
Query SENDER_BATCH to check the overall status and completion of a bulk payment batch
Query SENDER_BATCH_DETAIL to find individual stuck or failed transactions inside a batch
Query MPG_SAF_RESPONSE to find SBP's reply and identify rejection reasons using REASON_CODE
Query MPG_SAF_TRANSACTIONS to find transactions stuck in the SAF queue with high retry counts
Use MPG_AUDIT_LOG and AUDIT_LOG_DETAILS to trace the exact error and its root cause
Complete the full lab — trace a stuck batch through all 6 tables and produce a validation summary report