# Database Schema Reference

**Engine:** MySQL · **Migration tool:** Flyway · **ORM:** JPA / Hibernate (Spring Data)

Migration files: `jpos/src/main/resources/db/migration/`
Entity classes: `jpos/src/main/java/org/jpos/tcpay/db/entity/`
Repositories: `jpos/src/main/java/org/jpos/tcpay/db/repository/`
DB config: `jpos/src/dist/cfg/application.properties`

---

## Table of Contents

1. [Schema Overview](#schema-overview)
2. [Migration History](#migration-history)
3. [Master / Configuration Tables](#master--configuration-tables)
   - [address](#address)
   - [acquirer](#acquirer)
   - [acquirer_connection](#acquirer_connection)
   - [acquirer_merchant](#acquirer_merchant)
   - [acquirer_terminal](#acquirer_terminal)
   - [pos_merchant](#pos_merchant)
   - [pos_terminal](#pos_terminal)
   - [pos_terminal_acquirer_terminal](#pos_terminal_acquirer_terminal)
4. [Counter / State Tables](#counter--state-tables)
   - [acquirer_terminal_stan](#acquirer_terminal_stan)
   - [acquirer_terminal_batch](#acquirer_terminal_batch)
   - [acquirer_terminal_state](#acquirer_terminal_state)
   - [acquirer_response](#acquirer_response)
5. [Audit Table](#audit-table)
   - [pos_terminal_data](#pos_terminal_data)
6. [Transaction Lifecycle Tables](#transaction-lifecycle-tables)
   - [pos_temp_transaction](#pos_temp_transaction)
   - [pos_transaction](#pos_transaction)
   - [pos_failed_transaction](#pos_failed_transaction)
   - [pos_transaction_reversal](#pos_transaction_reversal)
7. [Entity Relationships](#entity-relationships)
8. [Transaction Lifecycle Flow](#transaction-lifecycle-flow)
9. [Field Naming Conventions](#field-naming-conventions)
10. [Key Enum Values](#key-enum-values)

---

## Schema Overview

| Category | Tables | Purpose |
|----------|--------|---------|
| Master / Config | `address`, `acquirer`, `acquirer_connection`, `acquirer_merchant`, `acquirer_terminal`, `pos_merchant`, `pos_terminal`, `pos_terminal_acquirer_terminal` | Static configuration — terminals, merchants, acquirer connections |
| Counter / State | `acquirer_terminal_stan`, `acquirer_terminal_batch`, `acquirer_terminal_state`, `acquirer_response` | Running counters and terminal state tracking |
| Audit | `pos_terminal_data` | ISO message field audit trail |
| Transaction Lifecycle | `pos_temp_transaction`, `pos_transaction`, `pos_failed_transaction`, `pos_transaction_reversal` | Full transaction lifecycle from in-flight to completion |

**Total: 17 tables**

---

## Migration History

| File | Description |
|------|-------------|
| `000_fix_pos_failed_transaction_id.sql` | Adds `AUTO_INCREMENT` to `pos_failed_transaction.id` (retroactive fix) |
| `001_create_acquirer_terminal_stan_table.sql` | Creates STAN counter table per acquirer terminal |
| `002_create_acquirer_terminal_batch_table.sql` | Creates batch number counter table per acquirer terminal |
| `003_add_order_number_col_to_transaction_tables.sql` | Adds `order_number` column (from DE-47) to all three transaction tables |
| `003_create_acquirer_response_table.sql` | Creates response code mapping table |
| `004_create_pos_terminal_data_table.sql` | Creates audit table for ISO message fields |
| `005_create_transaction_lifecycle_tables.sql` | Creates `pos_temp_transaction`, `pos_transaction`, `pos_failed_transaction`, `pos_transaction_reversal` |
| `007_add_parent_txn_id.sql` | Adds `parent_txn_id` column to all three transaction tables (soft FK to voided SALE) |
| `008_widen_response_message.sql` | Widens `response_message` from VARCHAR(100) to VARCHAR(256) on all three transaction tables |

---

## Master / Configuration Tables

### address

Stores physical address records shared by merchants and acquirers.

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `line1` | VARCHAR | YES | Address line 1 |
| `line2` | VARCHAR | YES | Address line 2 |
| `line3` | VARCHAR | YES | Address line 3 |
| `line4` | VARCHAR | YES | Address line 4 |
| `city` | VARCHAR | YES | |
| `state` | VARCHAR | YES | |
| `country` | VARCHAR | YES | |
| `postalCode` | VARCHAR | YES | |

---

### acquirer

One row per payment acquirer / bank.

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `name` | VARCHAR | YES | Human-readable acquirer name |
| `description` | VARCHAR | YES | |
| `nii` | VARCHAR | YES | Network Identification Number (DE-24) |
| `acquirerInstitutionCode` | VARCHAR | YES | Institution code used in messages |

---

### acquirer_connection

Network and SSL connection details for each acquirer. Referenced at runtime to open sockets.

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `acquirer_id` | BIGINT | YES | FK → `acquirer.id` |
| `ip_address` | VARCHAR | YES | Acquirer host IP |
| `port` | INT | YES | TCP port |
| `is_ssl` | BOOLEAN | NO | Whether TLS/SSL is used |
| `keystore_path` | VARCHAR | YES | Path to client keystore |
| `keystore_password` | VARCHAR | YES | Keystore password |
| `truststore_path` | VARCHAR | YES | Path to truststore |
| `truststore_password` | VARCHAR | YES | Truststore password |
| `tpdu` | VARCHAR | YES | TPDU header bytes |
| `length_mode` | VARCHAR | YES | Length encoding mode (ENUM-like) |

---

### acquirer_merchant

Merchant accounts registered at a specific acquirer. Holds the acquirer-assigned MID (DE-42).

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `acquirer_id` | BIGINT | YES | FK → `acquirer.id` |
| `merchantid` | VARCHAR | YES | Acquirer-assigned MID (DE-42) |
| `mcc` | VARCHAR | YES | Merchant Category Code |
| `address_id` | BIGINT | YES | FK → `address.id` |

---

### acquirer_terminal

Terminal accounts registered at a specific acquirer. Holds the acquirer-assigned TID (DE-41).

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `acquirer_id` | BIGINT | YES | FK → `acquirer.id` |
| `terminalid` | VARCHAR | YES | Acquirer-assigned TID (DE-41) |
| `acquirer_merchant_id` | BIGINT | YES | FK → `acquirer_merchant.id` |

**Relationships:** Many-to-many with `pos_terminal` via `pos_terminal_acquirer_terminal`.

---

### pos_merchant

POS-side merchant record. Holds the device/network MID (DE-42 as seen by the POS).

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `merchantid` | VARCHAR | YES | POS MID |
| `merchant_name` | VARCHAR | YES | Merchant display name |
| `address_id` | BIGINT | YES | FK → `address.id` |

---

### pos_terminal

POS terminal record. Holds the device TID (DE-41 as sent by the POS).

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `terminalid` | VARCHAR | YES | POS TID (DE-41) |
| `serial_number` | VARCHAR | YES | Device serial number |
| `pos_merchant_id` | BIGINT | YES | FK → `pos_merchant.id` |

**Relationships:** Many-to-many with `acquirer_terminal` via `pos_terminal_acquirer_terminal`.

---

### pos_terminal_acquirer_terminal

Junction table mapping POS terminals to their acquirer terminals.

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `pos_terminal_id` | BIGINT | NO | PK (composite), FK → `pos_terminal.id` |
| `acquirer_terminal_id` | BIGINT | NO | PK (composite), FK → `acquirer_terminal.id` |

---

## Counter / State Tables

### acquirer_terminal_stan

Tracks the rolling STAN counter (DE-11) per acquirer terminal. Resets daily.

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| `id` | BIGINT | NO | AUTO_INCREMENT | PK |
| `acquirer_terminal_id` | BIGINT | NO | | FK → `acquirer_terminal.id` ON DELETE CASCADE |
| `current_stan` | INT | NO | 1 | Current STAN value |
| `max_stan` | INT | NO | 999999 | Wraps at this value |
| `last_reset_date` | DATE | NO | | Date of last reset |
| `created_at` | TIMESTAMP | NO | CURRENT_TIMESTAMP | |
| `updated_at` | TIMESTAMP | NO | CURRENT_TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP |

**Constraints:** UNIQUE(`acquirer_terminal_id`), CHECK(`current_stan` BETWEEN 1 AND `max_stan`), CHECK(`max_stan` > 0)
**Indexes:** `idx_acquirer_terminal_stan_terminal_id`, `idx_acquirer_terminal_stan_reset_date`

---

### acquirer_terminal_batch

Tracks the rolling batch number (DE-62) per acquirer terminal. One record per terminal.

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| `id` | BIGINT | NO | AUTO_INCREMENT | PK |
| `acquirer_terminal_id` | BIGINT | NO | | FK → `acquirer_terminal.id` ON DELETE CASCADE |
| `current_batch` | INT | NO | 1 | Current batch number |
| `max_batch` | INT | NO | 999999 | Wraps at this value |
| `last_reset_date` | DATE | NO | | Date of last reset |
| `created_at` | TIMESTAMP | NO | CURRENT_TIMESTAMP | |
| `updated_at` | TIMESTAMP | NO | CURRENT_TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP |

**Constraints:** UNIQUE(`acquirer_terminal_id`), CHECK(`current_batch` BETWEEN 1 AND `max_batch`)
**Indexes:** `idx_acquirer_terminal_batch_terminal_id`, `idx_acquirer_terminal_batch_reset_date`

---

### acquirer_terminal_state

Tracks whether an acquirer terminal is currently busy processing a transaction.

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `acquirer_terminal_id` | BIGINT | YES | FK → `acquirer_terminal.id` |
| `is_busy` | BOOLEAN | NO | `true` = terminal busy, `false` = idle |

---

### acquirer_response

Reference table mapping acquirer-specific response codes to standard jPOS response codes.

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| `id` | INT | NO | AUTO_INCREMENT | PK |
| `acquirer_id` | INT | YES | | Acquirer ID (informational) |
| `acquirer_name` | VARCHAR(100) | NO | | Acquirer name key |
| `response_code` | VARCHAR(10) | NO | | Raw code from acquirer |
| `response_message` | VARCHAR(255) | NO | | Human-readable message |
| `jpos_response_code` | VARCHAR(10) | YES | | Mapped standard code |
| `created_at` | TIMESTAMP | NO | CURRENT_TIMESTAMP | |
| `updated_at` | TIMESTAMP | NO | CURRENT_TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP |

**Constraints:** UNIQUE KEY `uk_acquirer_response` (`acquirer_name`, `response_code`)
**Indexes:** `idx_acquirer_response` (`acquirer_name`, `response_code`)
**Character set:** utf8mb4 / utf8mb4_unicode_ci

---

## Audit Table

### pos_terminal_data

Audit record of ISO message fields per transaction. Used for reconciliation. Keyed by POS terminal ID (one row per terminal, updated per transaction).

| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| `id` | BIGINT | NO | PK, AUTO_INCREMENT |
| `pos_terminalId` | VARCHAR(8) | NO | POS TID (DE-41) |
| `stan` | VARCHAR(6) | NO | STAN (DE-11) |
| `batch_number` | VARCHAR(6) | NO | Batch number (DE-62) |
| `transaction_date` | VARCHAR(4) | NO | MMDD (DE-13) |
| `transaction_time` | VARCHAR(6) | NO | HHMMSS (DE-12) |
| `transaction_id` | BIGINT | YES | Soft ref to `pos_transaction.id` |
| `created_at` | TIMESTAMP | NO | CURRENT_TIMESTAMP |
| `updated_at` | TIMESTAMP | NO | ON UPDATE CURRENT_TIMESTAMP |

**Constraints:** UNIQUE(`pos_terminalId`)
**Indexes:** `idx_pos_terminal_data_terminal_id`, `idx_pos_terminal_data_stan`, `idx_pos_terminal_data_batch`, `idx_pos_terminal_data_transaction_id`, `idx_pos_terminal_data_composite` (`pos_terminalId`, `stan`, `batch_number`), `idx_pos_terminal_data_date_time` (`transaction_date`, `transaction_time`)

---

## Transaction Lifecycle Tables

These three tables form a pipeline. A transaction starts in `pos_temp_transaction`, then moves atomically to either `pos_transaction` (success) or `pos_failed_transaction` (failure). Reversals are tracked separately in `pos_transaction_reversal`.

### Common Transaction Columns

All three transaction tables share this core column set:

| Column | Type | Notes |
|--------|------|-------|
| `id` | BIGINT AUTO_INCREMENT | PK |
| `s_txn_type` | VARCHAR(25) | Transaction type (SALE, REFUND, VOID, etc.) |
| `s_tid` | VARCHAR(8) | POS terminal ID (DE-41) |
| `s_mid` | VARCHAR(15) | POS merchant ID (DE-42) |
| `s_tid_stan` | VARCHAR(6) | POS STAN (DE-11) |
| `s_tid_invoiceno` | VARCHAR(6) | POS invoice number |
| `s_tid_batchno` | VARCHAR(6) | POS batch number |
| `b_tid` | VARCHAR(8) | Bank/acquirer terminal ID |
| `b_mid` | VARCHAR(15) | Bank/acquirer merchant ID |
| `acquirer_id` | BIGINT | Acquirer ID |
| `b_tid_stan` | VARCHAR(6) | Bank STAN |
| `b_tid_invoiceno` | VARCHAR(6) | Bank invoice number |
| `b_tid_batchno` | VARCHAR(6) | Bank batch number |
| `b_tid_date` | VARCHAR(8) | Bank date |
| `b_tid_time` | VARCHAR(6) | Bank time |
| `entry_mode` | VARCHAR(3) | Card entry mode (DE-22) |
| `condition_code` | VARCHAR(2) | Condition code |
| `currency_code` | VARCHAR(3) | Currency code (DE-49) |
| `mti` | VARCHAR(4) | ISO MTI (0100, 0200, etc.) |
| `proc_code` | VARCHAR(6) | Processing code (DE-3) |
| `total_amount` | DECIMAL(12,2) | Total amount (DE-4) |
| `auth_amount` | DECIMAL(12,2) | Authorization amount |
| `cash_amount` | DECIMAL(12,2) | Cash back amount |
| `tip_amount` | DECIMAL(12,2) | Tip amount (DE-54) |
| `approval_code` | VARCHAR(6) | Auth code (DE-38) |
| `reference_no` | VARCHAR(12) | RRN (DE-37) |
| `response_code` | VARCHAR(2) | Response code (DE-39) |
| `response_message` | VARCHAR(256) | Response description |
| `mcc_code` | VARCHAR(4) | Merchant category code |
| `encrypted_track2` | VARCHAR(256) | Encrypted Track 2 data |
| `encrypted_expiry` | VARCHAR(4) | Encrypted expiry date |
| `encrypted_pan` | VARCHAR(256) | Encrypted PAN |
| `masked_card_no` | VARCHAR(19) | Masked card number |
| `pan_seq` | VARCHAR(3) | PAN sequence number |
| `emv_data` | TEXT | EMV data (DE-55 hex) |
| `acquirer_reference_no` | VARCHAR(23) | Acquirer reference |
| `scheme_reference_no` | VARCHAR(23) | Scheme reference |
| `order_number` | VARCHAR(50) | External order number (DE-47) |
| `parent_txn_id` | BIGINT | Soft ref to `pos_transaction.id` of the voided SALE (populated for VOID transactions only, NULL for all others) |
| `metadata` | TEXT | JSON metadata from processing |
| `created_dateTime` | DATETIME | Record creation time |
| `updated_dateTime` | DATETIME | Last update time |

---

### pos_temp_transaction

Holds transactions currently in-flight. A record exists here for the entire duration of processing. Deleted (moved) on completion.

**Additional columns (not in pos_transaction / pos_failed_transaction):**

| Column | Type | Default | Notes |
|--------|------|---------|-------|
| `status` | VARCHAR(30) | `ACTIVE` | See [status values](#pos_temp_transaction-status-values) below |

**Indexes:** `idx_temp_transaction_btid_bmid` (`b_tid`, `b_mid`), `idx_temp_transaction_stid` (`s_tid`), `idx_temp_transaction_created` (`created_dateTime`)

#### pos_temp_transaction status values

| Status | Meaning |
|--------|---------|
| `ACTIVE` | Transaction is being processed normally |
| `RESPONSE_TIMEOUT` | Bank did not respond in time |
| `CONNECTION_LOST` | Socket connection to bank was lost |
| `REVERSAL_PENDING` | Reversal queued but not yet sent |
| `REVERSAL_SENT` | Reversal request sent to bank |
| `REVERSAL_COMPLETED` | Reversal confirmed by bank |
| `REVERSAL_FAILED` | Reversal attempt failed |
| `REVERSAL_ERROR` | Unexpected error during reversal |
| `PENDING_MANUAL_REVIEW` | Requires human intervention |

---

### pos_transaction

Permanent record of **approved / successfully processed** transactions. Identical columns to `pos_temp_transaction` minus `status`.

**Indexes:** `idx_transaction_btid_bmid` (`b_tid`, `b_mid`), `idx_transaction_stid` (`s_tid`), `idx_transaction_created` (`created_dateTime`), `idx_transaction_response_code`, `idx_transaction_approval_code`, `idx_transaction_amount`, `idx_transaction_mti`

---

### pos_failed_transaction

Permanent record of **declined or errored** transactions. Identical columns to `pos_transaction`.

> `id` column was retroactively given `AUTO_INCREMENT` by migration `000_fix_pos_failed_transaction_id.sql`.

**Indexes:** `idx_failed_transaction_btid_bmid` (`b_tid`, `b_mid`), `idx_failed_transaction_stid` (`s_tid`), `idx_failed_transaction_created` (`created_dateTime`), `idx_failed_transaction_response_code`, `idx_failed_transaction_mti`

---

### pos_transaction_reversal

Tracks every reversal attempt for a transaction, including retry state and full request/response payloads.

| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| `id` | BIGINT | NO | AUTO_INCREMENT | PK |
| `s_txn_type` | VARCHAR(25) | YES | | Original transaction type |
| `original_temp_txn_id` | BIGINT | NO | | Soft ref to `pos_temp_transaction.id` |
| `reversal_reason` | VARCHAR(50) | NO | | Why reversal was triggered |
| `reversal_status` | VARCHAR(20) | NO | `PENDING` | See [reversal status values](#reversal-status-values) |
| `s_tid` | VARCHAR(8) | YES | | POS terminal ID |
| `s_mid` | VARCHAR(15) | YES | | POS merchant ID |
| `s_tid_stan` | VARCHAR(6) | YES | | POS STAN |
| `b_tid` | VARCHAR(8) | YES | | Bank terminal ID |
| `b_mid` | VARCHAR(15) | YES | | Bank merchant ID |
| `acquirer_id` | BIGINT | YES | | Acquirer ID |
| `b_tid_stan` | VARCHAR(6) | YES | | Bank STAN |
| `original_mti` | VARCHAR(4) | YES | | MTI of original transaction |
| `reversal_mti` | VARCHAR(4) | YES | | MTI used for reversal (usually `0400`) |
| `original_encrypted_pan` | VARCHAR(40) | YES | | |
| `original_encrypted_expiry_date` | VARCHAR(40) | YES | | |
| `original_proc_code` | VARCHAR(6) | YES | | Original processing code |
| `original_amount` | DECIMAL(12,2) | YES | | Original transaction amount |
| `original_stan` | VARCHAR(6) | YES | | |
| `original_time` | VARCHAR(6) | YES | | |
| `original_date` | VARCHAR(8) | YES | | |
| `original_entry_mode` | VARCHAR(3) | YES | | |
| `original_pan_seq` | VARCHAR(3) | YES | | |
| `original_reference_no` | VARCHAR(12) | YES | | |
| `original_currency_code` | VARCHAR(3) | YES | | |
| `original_batch_no` | VARCHAR(3) | YES | | |
| `reversal_request` | TEXT | YES | | Full reversal ISO request (serialized) |
| `reversal_response` | TEXT | YES | | Full reversal ISO response (serialized) |
| `reversal_response_code` | VARCHAR(2) | YES | | Bank response to reversal |
| `reversal_reference_no` | VARCHAR(12) | YES | | RRN from reversal response |
| `retry_count` | INT | NO | 0 | Attempts so far |
| `max_retry_attempts` | INT | NO | 3 | Maximum allowed retries |
| `next_retry_time` | DATETIME | YES | | Scheduled next retry |
| `created_dateTime` | DATETIME | NO | | |
| `initiated_dateTime` | DATETIME | YES | | When first sent to bank |
| `completed_dateTime` | DATETIME | YES | | When bank confirmed |
| `updated_dateTime` | DATETIME | YES | | |
| `error_message` | VARCHAR(255) | YES | | Last error |
| `metadata` | TEXT | YES | | JSON metadata |

**Indexes:** `idx_reversal_btid_bmid` (`b_tid`, `b_mid`), `idx_reversal_original_txn` (`original_temp_txn_id`), `idx_reversal_status` (`reversal_status`), `idx_reversal_created` (`created_dateTime`)

#### Reversal status values

| Status | Meaning |
|--------|---------|
| `PENDING` | Queued, not yet sent |
| `SENT` | Sent to bank, awaiting response |
| `COMPLETED` | Bank confirmed reversal |
| `FAILED` | Bank rejected reversal |
| `RETRY_SCHEDULED` | Will retry at `next_retry_time` |
| `MAX_RETRIES_EXCEEDED` | Retry limit reached |
| `MANUAL_REVIEW` | Requires human intervention |

---

## Entity Relationships

```
address
  ←── pos_merchant.address_id
  ←── acquirer_merchant.address_id

acquirer
  ←── acquirer_connection.acquirer_id
  ←── acquirer_merchant.acquirer_id
  ←── acquirer_terminal.acquirer_id

acquirer_merchant
  ←── acquirer_terminal.acquirer_merchant_id

acquirer_terminal
  ←── acquirer_terminal_stan.acquirer_terminal_id  (CASCADE DELETE)
  ←── acquirer_terminal_batch.acquirer_terminal_id (CASCADE DELETE)
  ←── acquirer_terminal_state.acquirer_terminal_id
  ←── pos_terminal_acquirer_terminal.acquirer_terminal_id

pos_merchant
  ←── pos_terminal.pos_merchant_id

pos_terminal
  ←── pos_terminal_acquirer_terminal.pos_terminal_id

pos_terminal_acquirer_terminal  [junction]
  pos_terminal ──< pos_terminal_acquirer_terminal >── acquirer_terminal
```

---

## Transaction Lifecycle Flow

```
Incoming ISO request
        │
        ▼
pos_temp_transaction  (status = ACTIVE)
        │
        ├─ Bank unreachable / timeout ──► status = RESPONSE_TIMEOUT / CONNECTION_LOST
        │                                         │
        │                              pos_transaction_reversal (status = PENDING → SENT → COMPLETED/FAILED)
        │
        ├─ Bank declines (RC ≠ 00) ────► DELETE from pos_temp_transaction
        │                                INSERT into pos_failed_transaction
        │
        └─ Bank approves (RC = 00) ────► DELETE from pos_temp_transaction
                                         INSERT into pos_transaction
```

---

## Field Naming Conventions

| Prefix | Meaning | Example |
|--------|---------|---------|
| `s_` | POS / source side | `s_tid`, `s_mid`, `s_tid_stan` |
| `b_` | Bank / acquirer side | `b_tid`, `b_mid`, `b_tid_stan` |
| `original_` | Data from the original transaction (in reversal table) | `original_mti`, `original_amount` |
| `reversal_` | Data specific to the reversal attempt | `reversal_status`, `reversal_response_code` |

---

## Key Enum Values

### Transaction types (`s_txn_type`)

`SALE`, `SALE_TIP`, `REFUND`, `VOID`, `VOID_SALE`, `VOID_REFUND`, `VOID_COMPLETION`, `PREAUTH`, `PREAUTH_COMPLETE`, `PREAUTH_VOID`, `PREAUTH_TOPUP`, `BALANCE_INQUIRY`, `SETTLEMENT`, `LOGON`, `BATCH_UPLOAD`

### MTI values

| MTI | Type |
|-----|------|
| `0100` | Pre-Authorization request |
| `0200` | Financial transaction request |
| `0220` | Financial transaction advice (Auth-Completion / Refund) |
| `0400` / `0420` | Reversal request |
| `0500` | Reconciliation / Settlement |
| `0800` | Network management (Key Exchange, Echo) |

### Processing codes (`proc_code`, DE-3 — first 2 digits)

| Code | Type |
|------|------|
| `00` | Sale / Pre-Auth |
| `02` | Void |
| `20` | Refund |
| `30` | Pre-Auth |
| `60` | Auth-Completion |