Schema — cash_transactions

New table recording offline cash credit sales in the hierarchy. Separate from payment_transactions (gateway/online audit) but still linked to payment + wallet ledger when creating transactions.

Related: Spec Admin → SA · Tasks

---

Role in the system

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

Table	Role
cash_transactions	Primary record for list/detail UI, filter, export CSV
payment_transactions	Keeps legacy flow / reporting; link via payment_transaction_id
wallet_transactions	Credit/debit wallet entries; link via credit_wallet_transaction_id / debit_wallet_transaction_id
cash_transaction_events	Audit timeline (create, status change later)

---

Partitioning (monthly)

Both tables are partitioned like other high-volume backend tables (payment_transactions, wallet_transactions, …):

Table	Partition key	Range
cash_transactions	created_at	Monthly RANGE
cash_transaction_events	created_at	Monthly RANGE

Pre-create: 2025-01 → 2030-12 (72 partitions per table), via partitionManager.js or inline in migration (pattern gamify_reward_webhook_logs).

PK & UNIQUE on partitioned tables

PostgreSQL requires PK / UNIQUE to include partition key:

Table	Primary key
cash_transactions	(id, created_at)
cash_transaction_events	(id, created_at)

transaction_code — globally unique at app layer; DB uses UNIQUE (transaction_code, created_at) to satisfy PG constraint (each code inserted once with same created_at).

No FK across partitioned tables

Like credit_transactions / wallet_transactions:

• No REFERENCES wallet_transactions(id) — wallet already partitioned by created_at
• No REFERENCES payment_transactions(id) — same reason
• No REFERENCES cash_transactions(id) from events — use cash_transaction_id + cash_transaction_created_at (denormalize parent partition key) + validate in biz layer

cash_transaction_events.cash_transaction_created_at = snapshot of cash_transactions.created_at at event insert → join with 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;

Operations

• Add both tables to PARTITIONED_TABLES in partitionManager.js
• Cron/ops: partition:create-year before new year — insert into month without partition → runtime fail
• List/filter UI defaults to created_at / received_at → good partition pruning

---

Enums

cash_transaction_flow

Value	Flow
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	Meaning
platform	Admin / platform (seller when Admin sells)
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 on create
pending_payment	Pending payment — credits issued	Later phase
partially_paid	Partially paid	Later phase
cancelled	Cancelled	Later phase

cash_payment_method

Value	UI
cash	Cash
bank_transfer	Bank transfer
check	Check
other	Other

cash_transaction_event_type

Value	When
created	Transaction created
status_changed	Status changed (later phase)
amount_updated	Cash received changed (later phase)
note_added	Note added (later phase)
cancelled	Transaction cancelled (later phase)

---

Table cash_transactions

Columns

Column	Type	Null	Description
id	UUID	NO	PK (composite with created_at)
transaction_code	VARCHAR(32)	NO	Display code, e.g. CTX-20260620-001 — app unique + UNIQUE (transaction_code, created_at)
flow	ENUM(cash_transaction_flow)	NO	Hierarchy flow
seller_type	ENUM(hierarchy_owner_type)	NO	platform | super_agent | agent
seller_id	UUID	YES	NULL when seller_type = platform
buyer_type	ENUM(hierarchy_owner_type)	NO	super_agent | agent | shop
buyer_id	UUID	NO	Entity receiving credits
credit_amount	DECIMAL(18, 4)	NO	Credits issued to buyer (> 0)
buyer_cost_rate	DECIMAL(5, 4)	NO	Buyer cost rate snapshot at time of sale
expected_cash	DECIMAL(15, 2)	NO	credit_amount × buyer_cost_rate
cash_received	DECIMAL(15, 2)	NO	Actual amount received
currency	VARCHAR(3)	NO	Default USD
payment_method	ENUM(cash_payment_method)	NO	Receipt method
received_at	TIMESTAMPTZ	NO	When payment received was confirmed
status	ENUM(cash_transaction_status)	NO	Default paid_full (V1)
external_reference	VARCHAR(128)	YES	External receipt / transfer code
note	TEXT	YES	Note
performed_by_type	VARCHAR(32)	NO	admin | super_agent | agent | cashier | shop
performed_by_id	UUID	NO	User/admin who performed action
payment_transaction_id	INTEGER	YES	→ payment_transactions.id (app-level, no FK)
credit_wallet_transaction_id	UUID	YES	→ wallet_transactions.id (app-level, no FK)
debit_wallet_transaction_id	UUID	YES	→ wallet_transactions.id (app-level, no FK)
buyer_balance_after	DECIMAL(18, 4)	YES	Buyer CDN balance snapshot after issuance
cashier_transaction_id	UUID	YES	→ cashier_transactions.id (counter path) — Phase 2
shift_id	UUID	YES	Denormalized shift — Phase 2
metadata	JSONB	NO	Default {} — buyer name snapshot, IP, grossMargin, etc.
created_at	TIMESTAMPTZ	NO
updated_at	TIMESTAMPTZ	NO

Constraints (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 or temporary CHECK)
-- seller_id IS NULL IFF seller_type = 'platform'
-- buyer_type must match flow (app validation)

Rules by 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	yes
agent_shop	agent	agent uuid	shop	yes
shop_player_deposit	shop	shop uuid	player	yes (shop debit)
shop_player_redemption	player	player uuid	shop	yes (player debit)

Indexes

Index	Columns	Purpose
PK	(id, created_at)	Partitioned PK
UNIQUE	(transaction_code, created_at)	Code lookup (PG partition rule)
	(flow, created_at DESC)	List filter by flow
	(buyer_type, buyer_id, created_at DESC)	History by buyer
	(seller_type, seller_id, created_at DESC)	History by 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

---

Table cash_transaction_events

Column	Type	Null	Description
id	UUID	NO	PK (composite with created_at)
cash_transaction_id	UUID	NO	→ cash_transactions.id (app-level, no FK)
cash_transaction_created_at	TIMESTAMPTZ	NO	Snapshot of 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 if system
payload	JSONB	NO	Default {} — before/after values
created_at	TIMESTAMPTZ	NO	Partition key (event time)

Index:

Index	Columns	Purpose
PK	(id, created_at)	Partitioned PK
	(cash_transaction_id, cash_transaction_created_at, created_at ASC)	Timeline by transaction

V1: insert only 1 created row when creating transaction (cash_transaction_created_at = parent created_at).

---

Reference DDL (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: add cash_transactions and cash_transaction_events to PARTITIONED_TABLES in kioskgaming_backend/src/database/scripts/partitionManager.js, then run npm run partition:create-year -- 2031 when extending.

---

Generating transaction_code

Suggested format: CTX-YYYYMMDD-NNNN

• NNNN = daily sequence (pad 4) or random suffix
• Unique constraint on transaction_code column
• Generate in biz layer on INSERT

---

Sample data

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 or snapshot shop.costRate for margin metadata
expected_cash	50.00 (V1: 1:1 face value)
cash_received	50.00
payment_method	cash (cashier) or card / bank_transfer (shop portal)
metadata.grossMargin	50 × (1 − 0.80) = 10.00
cashier_transaction_id	{cashier-tx-uuid} if via 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 — design notes

Phase 2 — Deposit (shop_player_deposit)

1. Auto-settled: V1 only status = paid_full.
2. Margin: metadata.grossMargin = credit_amount × (1 − shop.costRate).
3. Entry points: Cashier (has 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 when wallet transfer completed.
2. No margin — 1:1 face value cash paid.
3. Approval: Do not create cash_transactions at pending_approval; only after approve + transfer.
4. Shift: negative cashDelta → Cash Out in reconciliation.

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

---

metadata JSON (suggested)

{
  "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"
}

Do not store image files / proof attachments (out of scope V1).

---

Migration & Sequelize

Task	Suggested file
Migration	kioskgaming_backend/src/database/migrations/YYYYMMDDHHMMSS-create-cash-transactions-partitioned.js
Partition ops	Add to partitionManager.js → PARTITIONED_TABLES
Model	CashTransaction.js, CashTransactionEvent.js
Constants	src/constants/cashTransaction.js

Implementation order:

1. Migration creates ENUM + 2 partitioned tables + pre-create partitions 2025-01 → 2030-12
2. Register in partitionManager.js
3. Sequelize models + associations (constraints: false for wallet/payment refs)
4. Biz service creates row + created event (with cash_transaction_created_at) in same DB transaction as wallet transfer
5. Backfill not required V1 — old transactions still queried via payment_transactions

---

Sequelize relationships (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

---

Design notes

1. Does not replace payment_transactions — still creates audit row (admin_cash, super_agent_cash, agent_cash) and links payment_transaction_id.
2. expected_cash stored as snapshot — not recalculated from buyer's current cost rate.
3. V1 only creates with status = paid_full; status column + events prepare for later receivables / cancel phases.
4. Detail view-only reads from cash_transactions + events + join wallet/payment as needed.
5. Monthly partition on created_at — same pattern as 8 existing tables; no FK to other partitioned tables.
6. Lookup by id should include created_at (or use transaction_code) for partition pruning.