Phần 7 — Thực hành: Xây dựng Mini Core Banking từ đầu

-- ============================================================
-- 1. CUSTOMERS (CIF)
-- ============================================================
CREATE TABLE customers (
    cif_number      VARCHAR(20)  PRIMARY KEY,
    customer_type   VARCHAR(15)  NOT NULL CHECK (customer_type IN ('INDIVIDUAL','CORPORATE')),
    full_name       VARCHAR(255) NOT NULL,
    id_number       VARCHAR(30)  UNIQUE NOT NULL,
    kyc_status      VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 2. ACCOUNTS (CASA)
-- ============================================================
CREATE TABLE accounts (
    account_number    VARCHAR(20)  PRIMARY KEY,
    cif_number        VARCHAR(20)  NOT NULL REFERENCES customers(cif_number),
    account_type      VARCHAR(30)  NOT NULL,
    currency          CHAR(3)      NOT NULL DEFAULT 'VND',
    status            VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    current_balance   BIGINT       NOT NULL DEFAULT 0,
    available_balance BIGINT       NOT NULL DEFAULT 0,
    version           BIGINT       NOT NULL DEFAULT 1,
    created_at        TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 3. LEDGER ENTRIES (Double-Entry Bookkeeping)
-- ============================================================
CREATE TABLE ledger_entries (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id  UUID        NOT NULL,
    account_number  VARCHAR(20) NOT NULL REFERENCES accounts(account_number),
    entry_type      CHAR(6)     NOT NULL CHECK (entry_type IN ('DEBIT','CREDIT')),
    amount          BIGINT      NOT NULL CHECK (amount > 0),
    currency        CHAR(3)     NOT NULL,
    balance_after   BIGINT      NOT NULL,
    description     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Ngăn sửa/xóa ledger
CREATE RULE no_update_ledger AS ON UPDATE TO ledger_entries DO INSTEAD NOTHING;
CREATE RULE no_delete_ledger AS ON DELETE TO ledger_entries DO INSTEAD NOTHING;

-- ============================================================
-- 4. TRANSACTIONS (Idempotency Control)
-- ============================================================
CREATE TABLE financial_transactions (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key VARCHAR(64) UNIQUE NOT NULL,
    type            VARCHAR(30) NOT NULL, -- 'DEPOSIT','WITHDRAWAL','TRANSFER','FEE'
    status          VARCHAR(20) NOT NULL DEFAULT 'PROCESSING',
    from_account    VARCHAR(20),
    to_account      VARCHAR(20),
    amount          BIGINT      NOT NULL,
    currency        CHAR(3)     NOT NULL,
    description     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at    TIMESTAMPTZ
);

-- ============================================================
-- 5. OUTBOX (Event Publishing đảm bảo At-Least-Once)
-- ============================================================
CREATE TABLE outbox_events (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    topic        VARCHAR(100) NOT NULL,
    payload      JSONB        NOT NULL,
    status       VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    published_at TIMESTAMPTZ
);

-- ============================================================
-- 6. AUDIT LOG
-- ============================================================
CREATE TABLE audit_logs (
    id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(50) NOT NULL,
    entity_id   VARCHAR(50) NOT NULL,
    action      VARCHAR(50) NOT NULL,
    actor_id    VARCHAR(50) NOT NULL,
    before_data JSONB,
    after_data  JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Chạy cái này hàng ngày, alert nếu imbalance != 0
SELECT
    currency,
    SUM(CASE WHEN entry_type = 'DEBIT'  THEN amount ELSE 0 END) AS total_debits,
    SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END) AS total_credits,
    SUM(CASE WHEN entry_type = 'DEBIT'  THEN amount ELSE 0 END) -
    SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END) AS imbalance
FROM ledger_entries
GROUP BY currency
HAVING SUM(CASE WHEN entry_type = 'DEBIT'  THEN amount ELSE 0 END) !=
       SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END);

# Dùng k6 (JavaScript load testing tool)
# Kịch bản: 100 user cùng chuyển tiền đồng thời từ tài khoản A sang B

k6 run --vus 100 --duration 30s transfer_test.js

# Sau khi test xong, kiểm tra:
# 1. Tổng tiền trong hệ thống có bị thay đổi không?
# 2. Sổ cái có cân bằng không? (DEBIT = CREDIT)
# 3. Có giao dịch nào bị trùng lặp không?
# 4. Có tài khoản nào bị âm không?

// transfer_test.js
import http from 'k6/http';
import { uuidv4 } from 'https://jslib.k6.io/k6-utils/1.4.0/index.js';

export default function () {
    http.post('http://localhost:8080/v1/transfers', JSON.stringify({
        from_account: 'ACC001',
        to_account:   'ACC002',
        amount:       1000,   // 1.000 đồng
        currency:     'VND',
        description:  'Stress test transfer'
    }), {
        headers: {
            'Content-Type': 'application/json',
            'Idempotency-Key': uuidv4(),  // Mỗi request có key riêng
        }
    });
}