Payments Academy
Payment instruction table PAYMENT OPERATIONS STARTED LIVE 01 Payment instruction table - core record. ACTIVE 02 Payment event table - significant thing that happens to a payment. QUEUED 03 Event table - append-only, meaning rows are never updated QUEUED 04 System failure interrupts a posting halfway - If a system failure interrupts a QUEUED 05 Seven-year message retention - logs are kept for at least seven years QUEUED 06 Transition - Each transition is recorded in the event QUEUED The payment instruction table is the core record. Step 1 of 6 Payment instruction table STARTED Payment instruction table core record. ACTIVE Payment event table significant thing that happens to a payment. QUEUED Event table append-only, meaning rows are never QUEUED System failure interrupts a posting If a system failure interrupts a posting QUEUED Seven-year message retention logs are kept for at least seven years QUEUED Transition Each transition is recorded in the event QUEUED The payment instruction table is the core record.
Payment event table records every PAYMENT OPERATIONS IN PROGRESS LIVE 01 Payment instruction table - core record. DONE 02 Payment event table - significant thing that happens to a payment. ACTIVE 03 Event table - append-only, meaning rows are never updated QUEUED 04 System failure interrupts a posting halfway - If a system failure interrupts a QUEUED 05 Seven-year message retention - logs are kept for at least seven years QUEUED 06 Transition - Each transition is recorded in the event QUEUED The payment event table records every significant thing that happens to a payment. Step 2 of 6 Payment event table records every IN PROGRESS Payment instruction table core record. DONE Payment event table significant thing that happens to a payment. ACTIVE Event table append-only, meaning rows are never QUEUED System failure interrupts a posting If a system failure interrupts a posting QUEUED Seven-year message retention logs are kept for at least seven years QUEUED Transition Each transition is recorded in the event QUEUED The payment event table records every significant thing that happens to a payment.
Event table PAYMENT OPERATIONS IN PROGRESS LIVE 01 Payment instruction table - core record. DONE 02 Payment event table - significant thing that happens to a payment. DONE 03 Event table - append-only, meaning rows are never updated ACTIVE 04 System failure interrupts a posting halfway - If a system failure interrupts a QUEUED 05 Seven-year message retention - logs are kept for at least seven years QUEUED 06 Transition - Each transition is recorded in the event QUEUED The event table is append-only, meaning rows are never updated or deleted, so the history cannot be rewritten. Step 3 of 6 Event table IN PROGRESS Payment instruction table core record. DONE Payment event table significant thing that happens to a payment. DONE Event table append-only, meaning rows are never ACTIVE System failure interrupts a posting If a system failure interrupts a posting QUEUED Seven-year message retention logs are kept for at least seven years QUEUED Transition Each transition is recorded in the event QUEUED The event table is append-only, meaning rows are never updated or deleted, so the history cannot be rewritten.
System failure interrupts a posting halfway PAYMENT OPERATIONS IN PROGRESS LIVE 01 Payment instruction table - core record. DONE 02 Payment event table - significant thing that happens to a payment. DONE 03 Event table - append-only, meaning rows are never updated DONE 04 System failure interrupts a posting halfway - If a system failure interrupts a ACTIVE 05 Seven-year message retention - logs are kept for at least seven years QUEUED 06 Transition - Each transition is recorded in the event QUEUED If a system failure interrupts a posting halfway Step 4 of 6 System failure interrupts a posting halfway IN PROGRESS Payment instruction table core record. DONE Payment event table significant thing that happens to a payment. DONE Event table append-only, meaning rows are never DONE System failure interrupts a posting If a system failure interrupts a posting ACTIVE Seven-year message retention logs are kept for at least seven years QUEUED Transition Each transition is recorded in the event QUEUED If a system failure interrupts a posting halfway
Seven-year message retention PAYMENT OPERATIONS IN PROGRESS LIVE 01 Payment instruction table - core record. DONE 02 Payment event table - significant thing that happens to a payment. DONE 03 Event table - append-only, meaning rows are never updated DONE 04 System failure interrupts a posting halfway - If a system failure interrupts a DONE 05 Seven-year message retention - logs are kept for at least seven years ACTIVE 06 Transition - Each transition is recorded in the event QUEUED Message logs are kept for at least seven years in most countries to meet regulatory requirements. Step 5 of 6 Seven-year message retention IN PROGRESS Payment instruction table core record. DONE Payment event table significant thing that happens to a payment. DONE Event table append-only, meaning rows are never DONE System failure interrupts a posting If a system failure interrupts a posting DONE Seven-year message retention logs are kept for at least seven years ACTIVE Transition Each transition is recorded in the event QUEUED Message logs are kept for at least seven years in most countries to meet regulatory requirements.
Transition: recorded in event table PAYMENT OPERATIONS COMPLETE LIVE 01 Payment instruction table - core record. DONE 02 Payment event table - significant thing that happens to a payment. DONE 03 Event table - append-only, meaning rows are never updated DONE 04 System failure interrupts a posting halfway - If a system failure interrupts a DONE 05 Seven-year message retention - logs are kept for at least seven years DONE 06 Transition - Each transition is recorded in the event ACTIVE Each transition is recorded in the event table with the user or system that triggered it, and the database rejects any transition that is not on the approved list. Step 6 of 6 Transition: recorded in event table COMPLETE Payment instruction table core record. DONE Payment event table significant thing that happens to a payment. DONE Event table append-only, meaning rows are never DONE System failure interrupts a posting If a system failure interrupts a posting DONE Seven-year message retention logs are kept for at least seven years DONE Transition Each transition is recorded in the event ACTIVE Each transition is recorded in the event table with the user or system that triggered it, and the database rejects any transition that is not on the approved list.

Key points of this lesson, in order.

0:00 / 4:49 Step 1 of 6

Database Design for Payments

System Design Deep Dive, Lesson 7 of 7, Narration 4:49, D09 Payment architecture and technology

Value dateSettlementpacs.008

What you will learn

Your objective. By the end of this lesson, you will be able to explain database design for payments. The payment instruction table is the core record.

Why it matters. This matters because the architecture decides what the bank can build, how fast it can change, and where a payment can fail. Most payment requirements live in the architecture.

Chapter objective: Learner applies system design to payments, covering throughput, latency, idempotency, exactly-once processing, and failure recovery, and designs a system for a stated load. Learner practises this skill on realistic banking cases, explaining the reasoning clearly and using correct terminology throughout the work.

Key learning points

  • Payment instruction table
  • Payment event table records every
  • Event table
  • System failure interrupts a posting halfway
  • Seven-year message retention
  • Transition: recorded in event table

Key terms

Value date: The date on which funds become available to the beneficiary and on which interest starts to accrue. It is the date used for settlement and for nostro posting.

Settlement: The final movement of funds between banks to discharge obligations created by cleared payments. Settlement is usually made in central bank money or through correspondent accounts.

pacs.008: An ISO 20022 message for a financial institution to financial institution customer credit transfer. It replaces MT103 in cross border payments and carries structured debtor, creditor, charges and...

Banking architecture diagram showing Database Design for Payments.

Practise this lesson

Exercise

Map the payment in this lesson through the hub components.

Use the hub components and their order in this lesson.

  1. What to do. List each component the payment passes through, what it does, and what happens if it fails.

Correct result. The payment enters through a channel, passes the gateway, validation, fraud and sanctions checks, routing, and the ledger, then leaves through the right clearing system. A failure at any component stops or holds the payment, and the hub must record where it stopped.

Why. The hub is where most payment requirements live. If you cannot map a payment through it, you cannot specify, test or fix it.

Check your understanding

Practice question

Which of the following best describes database design for payments?

Takeaway and next step

What to remember

The event table is append-only, meaning rows are never updated or deleted, so the history cannot be rewritten.

Send a payment through the hub and watch it pass each component named in this lesson.

Open the Payment Hub Architecture Simulator →

Builds on "How Systems Connect".

Transcript and full text

Show transcript

The payment instruction table is the core record. It holds one row per payment instruction, with the unique reference number, the debit account, the beneficiary name and account, the amount and currency, the value date, the payment type, and the channel it came from. A bank processing 2 million payments a day adds 2 million rows a day to this table, so it is partitioned by date and indexed on the reference number and the account number. Without those indexes, finding a single payment among billions of rows would take minutes instead of milliseconds. The table also holds the current status of the payment, which is updated as the payment moves through its lifecycle. The payment event table records every significant thing that happens to a payment. When a payment is received, validated, screened, queued, released, settled, confirmed, rejected, or returned, a new event row is written with a timestamp, the old status, the new status, and the system or user who made the change. This event history is the audit trail. If a customer asks why a payment of 80,000 dollars took three days, the bank can show the exact sequence, received at 12 minutes past 9 in the morning, validated at 13 minutes past 9 in the morning, held for sanctions review at 14 minutes past 9 in the morning, released by compliance at 40 minutes past 11 in the morning, settled at 5 minutes past 12 in the afternoon. The event table is append-only, meaning rows are never updated or deleted, so the history cannot be rewritten. The account entry table holds the accounting postings for each payment. Every payment creates at least two entries, a debit to the sender account and a credit to the internal settlement account, followed by a debit to the settlement account and a credit to the beneficiary or the nostro account. Each entry records the account number, the amount, the currency, the value date, and the reference to the payment instruction. These entries must balance exactly, and the database enforces this with a rule that the total debits equal the total credits for every payment. If a system failure interrupts a posting halfway, the database transaction is rolled back so that no partial posting survives, which is why payment databases use strict transactional guarantees. The message log table stores the actual messages sent and received for each payment. For a cross-border payment this includes the pacs.008 message sent to the correspondent bank, the pacs.002 status report received back, and the MT103 equivalent if the payment went through the older SWIFT format. The log holds the full message text, the sender and receiver, and the time of transmission. This table is essential for investigations, because when a beneficiary bank says it never received a payment, the bank can produce the exact message, the exact time, and the acknowledgement reference. Message logs are kept for at least seven years in most countries to meet regulatory requirements. A payment moves through a defined set of states, and the database enforces the allowed transitions. The states are received, validated, queued, released, settled, confirmed, rejected, and returned. A payment in the received state can move to validated or rejected, but it cannot jump directly to settled. A payment in the queued state can move to released or returned, but not back to received. A payment in the settled state can move to confirmed when the clearing system acknowledges it, or to returned if the beneficiary bank rejects it. Each transition is recorded in the event table with the user or system that triggered it, and the database rejects any transition that is not on the approved list. This state machine is what stops a payment from being settled twice or confirmed before it has been released.

The payment instruction table is the core record. It holds one row per payment instruction, with the unique reference number, the debit account, the beneficiary name and account, the amount and currency, the value date, the payment type, and the channel it came from. A bank processing 2 million payments a day adds 2 million rows a day to this table, so it is partitioned by date and indexed on the reference number and the account number. Without those indexes, finding a single payment among billions of rows would take minutes instead of milliseconds. The table also holds the current status of the payment, which is updated as the payment moves through its lifecycle.

The payment event table records every significant thing that happens to a payment. When a payment is received, validated, screened, queued, released, settled, confirmed, rejected, or returned, a new event row is written with a timestamp, the old status, the new status, and the system or user who made the change. This event history is the audit trail. If a customer asks why a payment of 80,000 dollars took three days, the bank can show the exact sequence, received at 12 minutes past 9 in the morning, validated at 13 minutes past 9 in the morning, held for sanctions review at 14 minutes past 9 in the morning, released by compliance at 40 minutes past 11 in the morning, settled at 5 minutes past 12 in the afternoon. The event table is append-only, meaning rows are never updated or deleted, so the history cannot be rewritten.

The account entry table holds the accounting postings for each payment. Every payment creates at least two entries, a debit to the sender account and a credit to the internal settlement account, followed by a debit to the settlement account and a credit to the beneficiary or the nostro account. Each entry records the account number, the amount, the currency, the value date, and the reference to the payment instruction. These entries must balance exactly, and the database enforces this with a rule that the total debits equal the total credits for every payment. If a system failure interrupts a posting halfway, the database transaction is rolled back so that no partial posting survives, which is why payment databases use strict transactional guarantees.

The message log table stores the actual messages sent and received for each payment. For a cross-border payment this includes the pacs.008 message sent to the correspondent bank, the pacs.002 status report received back, and the MT103 equivalent if the payment went through the older SWIFT format. The log holds the full message text, the sender and receiver, and the time of transmission. This table is essential for investigations, because when a beneficiary bank says it never received a payment, the bank can produce the exact message, the exact time, and the acknowledgement reference. Message logs are kept for at least seven years in most countries to meet regulatory requirements.

A payment moves through a defined set of states, and the database enforces the allowed transitions. The states are received, validated, queued, released, settled, confirmed, rejected, and returned. A payment in the received state can move to validated or rejected, but it cannot jump directly to settled. A payment in the queued state can move to released or returned, but not back to received. A payment in the settled state can move to confirmed when the clearing system acknowledges it, or to returned if the beneficiary bank rejects it. Each transition is recorded in the event table with the user or system that triggered it, and the database rejects any transition that is not on the approved list. This state machine is what stops a payment from being settled twice or confirmed before it has been released.

Full lesson text (all scenes)

Completing a lesson updates your progress dashboard on the homepage.

Scene timestamps are estimated by allocating the narration text proportionally across the real audio length. The audio playback position is the source of truth: pause freezes the visual, seeking restores the correct scene, replay restarts the scene animation.