Schema — cash_transactions

Bảng mới ghi nhận bán credit bằng cash offline trong hierarchy. Tách khỏi payment_transactions (audit gateway/online) nhưng vẫn liên kết tới payment + wallet ledger khi tạo giao dịch.

Liên quan: Spec Admin → SA · Tasks

---

Vai trò trong hệ thống

┌─────────────────────┐     ┌──────────────────────┐     ┌─────────────────────┐
│  cash_transactions  │────▶│ payment_transactions │     │ wallet_transactions │
│  (sổ cash — source  │     │ (audit provider      │     │ (credit/debit CDN   │
│   of truth UI)      │     │  admin_cash, etc.)   │     │  wallet)            │
└─────────────────────┘     └──────────────────────┘     └─────────────────────┘
         │
         ▼
┌─────────────────────┐
│ cash_transaction_   │
│ events (audit)      │
└─────────────────────┘

Bảng	Vai trò
cash_transactions	Bản ghi chính cho list/detail UI, filter, export CSV
payment_transactions	Giữ luồng cũ / báo cáo; link qua payment_transaction_id
wallet_transactions	Bút toán credit/debit wallet; link qua credit_wallet_transaction_id / debit_wallet_transaction_id
cash_transaction_events	Audit timeline (tạo, đổi status sau này)

---

Partitioning (theo tháng)

Hai bảng partitioned giống các bảng volume cao khác trong backend (payment_transactions, wallet_transactions, …):

Bảng	Partition key	Range
cash_transactions	created_at	Monthly RANGE
cash_transaction_events	created_at	Monthly RANGE

Pre-create: 2025-01 → 2030-12 (72 partition / bảng), qua partitionManager.js hoặc inline trong migration (pattern gamify_reward_webhook_logs).

PK & UNIQUE trên bảng partitioned

PostgreSQL yêu cầu PK / UNIQUE phải gồm partition key:

Bảng	Primary key
cash_transactions	(id, created_at)
cash_transaction_events	(id, created_at)

transaction_code — unique toàn cục ở tầng app; DB dùng UNIQUE (transaction_code, created_at) để thỏa ràng buộc PG (mỗi code chỉ insert một lần cùng created_at).

Không dùng FK cross-partitioned

Giống credit_transactions / wallet_transactions:

• Không REFERENCES wallet_transactions(id) — wallet đã partition theo created_at
• Không REFERENCES payment_transactions(id) — cùng lý do
• Không REFERENCES cash_transactions(id) từ events — dùng cash_transaction_id + cash_transaction_created_at (denormalize partition key của parent) + validate ở biz layer

cash_transaction_events.cash_transaction_created_at = snapshot cash_transactions.created_at lúc insert event → join có partition pruning:

SELECT e.*
FROM cash_transaction_events e
JOIN cash_transactions t
  ON t.id = e.cash_transaction_id
 AND t.created_at = e.cash_transaction_created_at
WHERE e.cash_transaction_id = $1;

Vận hành

• Thêm 2 bảng vào PARTITIONED_TABLES trong partitionManager.js
• Cron/ops: partition:create-year trước năm mới — insert tháng chưa có partition → fail runtime
• List/filter UI mặc định theo created_at / received_at → partition pruning tốt

---

Enum

cash_transaction_flow

Value	Luồng
admin_super_agent	Admin → Super Agent
super_agent_agent	Super Agent → Agent
agent_shop	Agent → Shop
shop_player_deposit	Shop/Cashier → Player (counter deposit) — Phase 2
shop_player_redemption	Player → Shop/Cashier (counter redemption) — Phase 3

hierarchy_owner_type (seller / buyer / performer)

Value	Ý nghĩa
platform	Admin / platform (seller khi Admin bán)
super_agent	Super Agent
agent	Agent
shop	Shop
player	Player (end user) — Phase 2 counter

cash_transaction_status

Value	UI label	V1
paid_full	Paid in full — credits issued	✅ Default khi tạo
pending_payment	Pending payment — credits issued	Phase sau
partially_paid	Partially paid	Phase sau
cancelled	Cancelled	Phase sau

cash_payment_method

Value	UI
cash	Cash
bank_transfer	Bank transfer
check	Check
other	Other

cash_transaction_event_type

Value	Khi nào
created	Giao dịch tạo xong
status_changed	Đổi status (phase sau)
amount_updated	Đổi cash received (phase sau)
note_added	Thêm ghi chú (phase sau)
cancelled	Hủy giao dịch (phase sau)

---

Bảng cash_transactions

Cột

Cột	Kiểu	Null	Mô tả
id	UUID	NO	PK (composite với created_at)
transaction_code	VARCHAR(32)	NO	Mã hiển thị, vd CTX-20260620-001 — unique app + UNIQUE (transaction_code, created_at)
flow	ENUM(cash_transaction_flow)	NO	Luồng hierarchy
seller_type	ENUM(hierarchy_owner_type)	NO	platform | super_agent | agent
seller_id	UUID	YES	NULL khi seller_type = platform
buyer_type	ENUM(hierarchy_owner_type)	NO	super_agent | agent | shop
buyer_id	UUID	NO	Entity nhận credit
credit_amount	DECIMAL(18, 4)	NO	Số credit cấp cho buyer (> 0)
buyer_cost_rate	DECIMAL(5, 4)	NO	Snapshot cost rate buyer tại thời điểm bán
expected_cash	DECIMAL(15, 2)	NO	credit_amount × buyer_cost_rate
cash_received	DECIMAL(15, 2)	NO	Tiền thực thu
currency	VARCHAR(3)	NO	Default USD
payment_method	ENUM(cash_payment_method)	NO	Hình thức nhận
received_at	TIMESTAMPTZ	NO	Thời điểm xác nhận đã nhận tiền
status	ENUM(cash_transaction_status)	NO	Default paid_full (V1)
external_reference	VARCHAR(128)	YES	Mã biên lai / CK ngoài
note	TEXT	YES	Ghi chú
performed_by_type	VARCHAR(32)	NO	admin | super_agent | agent | cashier | shop
performed_by_id	UUID	NO	User/admin thực hiện
payment_transaction_id	INTEGER	YES	→ payment_transactions.id (app-level, không FK)
credit_wallet_transaction_id	UUID	YES	→ wallet_transactions.id (app-level, không FK)
debit_wallet_transaction_id	UUID	YES	→ wallet_transactions.id (app-level, không FK)
buyer_balance_after	DECIMAL(18, 4)	YES	Snapshot số dư CDN buyer sau cấp
cashier_transaction_id	UUID	YES	→ cashier_transactions.id (counter path) — Phase 2
shift_id	UUID	YES	Denormalize ca làm việc — Phase 2
metadata	JSONB	NO	Default {} — tên buyer snapshot, IP, grossMargin, v.v.
created_at	TIMESTAMPTZ	NO
updated_at	TIMESTAMPTZ	NO

Ràng buộc (CHECK)

-- credit_amount > 0
-- buyer_cost_rate > 0 AND buyer_cost_rate <= 1
-- expected_cash > 0
-- cash_received >= 0
-- V1: status = paid_full AND cash_received = expected_cash (enforce app layer hoặc CHECK tạm)
-- seller_id IS NULL IFF seller_type = 'platform'
-- buyer_type phải khớp flow (app validation)

Quy tắc theo flow

Flow	seller_type	seller_id	buyer_type	debit_wallet_tx
admin_super_agent	platform	NULL	super_agent	NULL
super_agent_agent	super_agent	SA uuid	agent	có
agent_shop	agent	agent uuid	shop	có
shop_player_deposit	shop	shop uuid	player	có (shop debit)
shop_player_redemption	player	player uuid	shop	có (player debit)

Indexes

Index	Cột	Mục đích
PK	(id, created_at)	Partitioned PK
UNIQUE	(transaction_code, created_at)	Tra cứu mã (PG partition rule)
	(flow, created_at DESC)	List filter theo luồng
	(buyer_type, buyer_id, created_at DESC)	Lịch sử theo buyer
	(seller_type, seller_id, created_at DESC)	Lịch sử theo seller
	(status, created_at DESC)	Filter status
	(received_at DESC)	Filter date range
	(payment_transaction_id)	Join payment
	(credit_wallet_transaction_id)	Join wallet credit

---

Bảng cash_transaction_events

Cột	Kiểu	Null	Mô tả
id	UUID	NO	PK (composite với created_at)
cash_transaction_id	UUID	NO	→ cash_transactions.id (app-level, không FK)
cash_transaction_created_at	TIMESTAMPTZ	NO	Snapshot cash_transactions.created_at — join + partition pruning
event_type	ENUM(cash_transaction_event_type)	NO
actor_type	VARCHAR(32)	NO	admin / super_agent / agent / system
actor_id	UUID	YES	NULL nếu system
payload	JSONB	NO	Default {} — before/after values
created_at	TIMESTAMPTZ	NO	Partition key (thời điểm event)

Index:

Index	Cột	Mục đích
PK	(id, created_at)	Partitioned PK
	(cash_transaction_id, cash_transaction_created_at, created_at ASC)	Timeline theo giao dịch

V1: chỉ insert 1 row created khi tạo giao dịch (cash_transaction_created_at = created_at của parent).

---

DDL tham khảo (PostgreSQL, partitioned)

CREATE TYPE cash_transaction_flow AS ENUM (
  'admin_super_agent',
  'super_agent_agent',
  'agent_shop'
);

CREATE TYPE hierarchy_owner_type AS ENUM (
  'platform',
  'super_agent',
  'agent',
  'shop'
);

CREATE TYPE cash_transaction_status AS ENUM (
  'paid_full',
  'pending_payment',
  'partially_paid',
  'cancelled'
);

CREATE TYPE cash_payment_method AS ENUM (
  'cash',
  'bank_transfer',
  'check',
  'other'
);

CREATE TYPE cash_transaction_event_type AS ENUM (
  'created',
  'status_changed',
  'amount_updated',
  'note_added',
  'cancelled'
);

-- Parent: monthly RANGE on created_at
CREATE TABLE cash_transactions (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  transaction_code VARCHAR(32) NOT NULL,
  flow cash_transaction_flow NOT NULL,
  seller_type hierarchy_owner_type NOT NULL,
  seller_id UUID NULL,
  buyer_type hierarchy_owner_type NOT NULL,
  buyer_id UUID NOT NULL,
  credit_amount DECIMAL(18, 4) NOT NULL,
  buyer_cost_rate DECIMAL(5, 4) NOT NULL,
  expected_cash DECIMAL(15, 2) NOT NULL,
  cash_received DECIMAL(15, 2) NOT NULL,
  currency VARCHAR(3) NOT NULL DEFAULT 'USD',
  payment_method cash_payment_method NOT NULL,
  received_at TIMESTAMPTZ NOT NULL,
  status cash_transaction_status NOT NULL DEFAULT 'paid_full',
  external_reference VARCHAR(128) NULL,
  note TEXT NULL,
  performed_by_type VARCHAR(32) NOT NULL,
  performed_by_id UUID NOT NULL,
  payment_transaction_id INTEGER NULL,
  credit_wallet_transaction_id UUID NULL,
  debit_wallet_transaction_id UUID NULL,
  buyer_balance_after DECIMAL(18, 4) NULL,
  metadata JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, created_at),
  CONSTRAINT cash_tx_transaction_code_created_at_unique UNIQUE (transaction_code, created_at),
  CONSTRAINT cash_tx_credit_amount_positive CHECK (credit_amount > 0),
  CONSTRAINT cash_tx_cost_rate_range CHECK (buyer_cost_rate > 0 AND buyer_cost_rate <= 1),
  CONSTRAINT cash_tx_expected_cash_positive CHECK (expected_cash > 0),
  CONSTRAINT cash_tx_cash_received_non_negative CHECK (cash_received >= 0),
  CONSTRAINT cash_tx_platform_seller CHECK (
    (seller_type = 'platform' AND seller_id IS NULL)
    OR (seller_type <> 'platform' AND seller_id IS NOT NULL)
  )
) PARTITION BY RANGE (created_at);

-- Partitions 2025-01 .. 2030-12 (repeat per month)
CREATE TABLE cash_transactions_2025_01 PARTITION OF cash_transactions
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ... cash_transactions_YYYY_MM ...

CREATE INDEX cash_transactions_flow_created_at_idx
  ON cash_transactions (flow, created_at DESC);

CREATE INDEX cash_transactions_buyer_idx
  ON cash_transactions (buyer_type, buyer_id, created_at DESC);

CREATE INDEX cash_transactions_seller_idx
  ON cash_transactions (seller_type, seller_id, created_at DESC);

CREATE INDEX cash_transactions_status_created_at_idx
  ON cash_transactions (status, created_at DESC);

CREATE INDEX cash_transactions_received_at_idx
  ON cash_transactions (received_at DESC);

CREATE INDEX cash_transactions_payment_transaction_id_idx
  ON cash_transactions (payment_transaction_id);

CREATE INDEX cash_transactions_credit_wallet_tx_id_idx
  ON cash_transactions (credit_wallet_transaction_id);

CREATE TABLE cash_transaction_events (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  cash_transaction_id UUID NOT NULL,
  cash_transaction_created_at TIMESTAMPTZ NOT NULL,
  event_type cash_transaction_event_type NOT NULL,
  actor_type VARCHAR(32) NOT NULL,
  actor_id UUID NULL,
  payload JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE cash_transaction_events_2025_01 PARTITION OF cash_transaction_events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ... cash_transaction_events_YYYY_MM ...

CREATE INDEX cash_transaction_events_tx_timeline_idx
  ON cash_transaction_events (cash_transaction_id, cash_transaction_created_at, created_at ASC);

Provision partitions: thêm cash_transactions và cash_transaction_events vào PARTITIONED_TABLES trong kioskgaming_backend/src/database/scripts/partitionManager.js, rồi chạy npm run partition:create-year -- 2031 khi cần mở rộng.

---

Sinh transaction_code

Format đề xuất: CTX-YYYYMMDD-NNNN

• NNNN = sequence trong ngày (pad 4) hoặc random suffix
• Unique constraint trên cột transaction_code
• Generate trong biz layer khi INSERT

---

Ví dụ dữ liệu

Admin → Super Agent

Field	Value
flow	admin_super_agent
seller	platform / NULL
buyer	super_agent / {sa-alpha-uuid}
credit_amount	10000
buyer_cost_rate	0.10
expected_cash	1000.00
cash_received	1000.00
payment_method	cash
status	paid_full
debit_wallet_transaction_id	NULL

Super Agent → Agent

Field	Value
flow	super_agent_agent
seller	super_agent / {sa-uuid}
buyer	agent / {agent-uuid}
buyer_cost_rate	0.15 (Agent rate)
debit_wallet_transaction_id	{debit-sa-wallet-tx}

Agent → Shop

Field	Value
flow	agent_shop
seller	agent / {agent-uuid}
buyer	shop / {shop-uuid}
buyer_cost_rate	0.80 (Shop rate)

Shop → Player (counter deposit) — Phase 2

Field	Value
flow	shop_player_deposit
seller	shop / {shop-uuid}
buyer	player / {player-uuid}
credit_amount	50
buyer_cost_rate	N/A hoặc snapshot shop.costRate cho margin metadata
expected_cash	50.00 (V1: 1:1 face value)
cash_received	50.00
payment_method	cash (cashier) hoặc card / bank_transfer (shop portal)
metadata.grossMargin	50 × (1 − 0.80) = 10.00
cashier_transaction_id	{cashier-tx-uuid} nếu qua Cashier portal

Player → Shop (counter redemption) — Phase 2

Field	Value
flow	shop_player_redemption
seller	player / {player-uuid}
buyer	shop / {shop-uuid}
credit_amount	30
expected_cash	30.00 (V1: 1:1)
cash_received	30.00
payment_method	cash
debit_wallet_transaction_id	player wallet debit

---

Counter flows — ghi chú thiết kế

Phase 2 — Deposit (shop_player_deposit)

1. Auto-settled: V1 chỉ status = paid_full.
2. Margin: metadata.grossMargin = credit_amount × (1 − shop.costRate).
3. Entry points: Cashier (có cashier_transaction_id) vs Shop Manager manual.

Spec: shop-cashier-to-player · Tasks: tasks-shop-cashier-to-player

Phase 3 — Redemption (shop_player_redemption)

1. Auto-settled khi wallet transfer completed.
2. Không margin — 1:1 face value cash paid.
3. Approval: Không tạo cash_transactions ở pending_approval; chỉ sau approve + transfer.
4. Shift: cashDelta âm → Cash Out trong reconciliation.

Spec: player-to-shop-cashier · Tasks: tasks-player-to-shop-cashier

---

metadata JSON (gợi ý)

{
  "buyerName": "SA-Alpha",
  "sellerName": "Platform",
  "playerId": "uuid",
  "playerUsername": "player_john88",
  "cashierId": "uuid",
  "shiftId": "uuid",
  "grossMargin": 10.0,
  "ipAddress": "203.0.113.1",
  "userAgent": "Mozilla/5.0 …",
  "legacyProvider": "admin_cash"
}

Không lưu file ảnh / bằng chứng (out of scope V1).

---

Migration & Sequelize

Task	File gợi ý
Migration	kioskgaming_backend/src/database/migrations/YYYYMMDDHHMMSS-create-cash-transactions-partitioned.js
Partition ops	Thêm vào partitionManager.js → PARTITIONED_TABLES
Model	CashTransaction.js, CashTransactionEvent.js
Constants	src/constants/cashTransaction.js

Thứ tự triển khai:

1. Migration tạo ENUM + 2 bảng partitioned + pre-create partitions 2025-01 → 2030-12
2. Register trong partitionManager.js
3. Sequelize models + associations (constraints: false cho ref wallet/payment)
4. Biz service tạo row + event created (kèm cash_transaction_created_at) trong cùng DB transaction với wallet transfer
5. Backfill không bắt buộc V1 — giao dịch cũ vẫn tra qua payment_transactions

---

Quan hệ Sequelize (draft)

CashTransaction.belongsTo(PaymentTransaction, {
  foreignKey: 'paymentTransactionId',
  constraints: false,
});
CashTransaction.belongsTo(WalletTransaction, {
  as: 'creditWalletTransaction',
  foreignKey: 'creditWalletTransactionId',
  constraints: false,
});
CashTransaction.belongsTo(WalletTransaction, {
  as: 'debitWalletTransaction',
  foreignKey: 'debitWalletTransactionId',
  constraints: false,
});
CashTransaction.hasMany(CashTransactionEvent, {
  foreignKey: 'cashTransactionId',
  as: 'events',
  constraints: false,
});
// Join events: WHERE cash_transaction_id + cash_transaction_created_at

---

Ghi chú thiết kế

1. Không thay thế payment_transactions — vẫn tạo row audit (admin_cash, super_agent_cash, agent_cash) và link payment_transaction_id.
2. expected_cash lưu snapshot — không tính lại từ cost rate hiện tại của buyer.
3. V1 chỉ create với status = paid_full; cột status + events chuẩn bị cho phase công nợ / hủy sau.
4. Detail view-only đọc từ cash_transactions + events + join wallet/payment tùy cần.
5. Partition theo tháng trên created_at — cùng pattern 8 bảng hiện có; không FK sang bảng partitioned khác.
6. Tra cứu theo id nên kèm created_at (hoặc dùng transaction_code) để partition pruning.