Database code review — kioskgaming_backend

Field	Value
Repo	kioskgaming_backend/src/database/
Review date	2026-06-20
Stack	PostgreSQL · Sequelize ORM · custom migration runner
Quy mô	76 models · 207 migrations · 8 bảng partitioned (+ gamify logs)
Related docs	Finance System · Backend code review (full) · Migration guide: kioskgaming_backend/src/database/migrations/MIGRATION_GUIDE.md

---

1. Tóm tắt

Tiêu chí	Đánh giá
Schema design (wallet, payment, hierarchy)	✅ Mạnh — dual wallet rõ, ledger có snapshot balance
Migration discipline	✅ Tốt — migration-first, không sync() production
Partitioning high-volume tables	✅ Có chủ đích + tooling CLI
Optimistic locking / idempotency	✅ Wallet + USD ledger + payment status
Config & secrets	❌ Config trùng 4 file; password fallback hardcoded
Model ↔ DB alignment	⚠️ Một số unique/index chỉ khai báo trong model
Rollback thực tế	⚠️ Nhiều migration data repair irreversible
JSONB metadata	⚠️ Linh hoạt nhưng thiếu schema/version
Precision tiền tệ	⚠️ DECIMAL(18,8) vs (15,2) lẫn lộn giữa domain
Test DB isolation	⚠️ config.js test database expression có bug tiềm ẩn

Verdict: Nền tảng production-grade cho fintech kiosk (ledger, partition, idempotency), nhưng vận hành và governance cần siết: config tập trung, partition automation, align constraint DB ↔ model, tách data repair khỏi schema migration.

---

2. Schema overview — nhóm bảng

Nhóm	Bảng tiêu biểu	Ghi chú
Users & auth	users, email_codes, phone_codes, user_sessions, account_otps	Hooks bcrypt trên User
Payment & gateway	payment_transactions, callback_logs, payment_api_logs, transaction_events, transaction_fraud_flags	Volume cao, partitioned
CDN credits wallet	wallets, wallet_transactions	ownerType: player/agent/shop/super_agent/platform
USD wallet	usd_wallets, usd_wallet_transactions, usd_wallet_reservations	gross/net + reserved; type ENUM
Online debt	online_debt_settlements, online_debt_settlement_lines	Payout network/wallet metadata
Game	game_providers, user_game_wallets, game_wallet_transactions	Per-provider balance
CDN hierarchy	super_agents, agents, shops, commission_logs	buyer_type trên payment
Cashier	cashiers, cashier_shifts, cashier_transactions, cashier_shift_adjustments	FK/CASCADE thiết kế kỹ (Jun 2026)
Admin & governance	admins, admin_action_logs, admin_broadcasts, permissions	Action log partitioned
Reconciliation	reconciliation_reports, reconciliation_discrepancies	⚠️ Model + repo có, chưa load vào connection.js
Config / limits	payment_provider_fee_configs, transaction_limits_configs, vfx_configurations	Fee snapshot tại payment creation

Quy ước đặt tên

• Bảng: snake_case, số nhiều, freezeTableName: true
• Cột DB: snake_case · field JS: camelCase + field: 'snake_case'
• Timestamps: created_at, updated_at · timezone UTC (+00:00)
• PK: hỗn hợp — INTEGER auto-increment (payment, logs cũ) · UUID (wallet, cashier, CDN mới)
• ENUM PostgreSQL: DataTypes.ENUM(...) — phổ biến cho status, type, owner_type
• Soft delete: không dùng — lifecycle qua status (terminated, closed, cancelled)

---

3. Connection & runtime

File: src/database/connection.js

Aspect	Chi tiết
Pool (development)	max 20, min 2, acquire 15s
Pool (production)	max 8 (conservative hơn dev)
Query timeout	SEQUELIZE_QUERY_TIMEOUT_MS, mặc định 8000ms
Schema	Không sequelize.sync() — chỉ migration
Associations	~330 dòng khai báo tập trung trong connection.js
global.db	Export ~74 model + sequelize — pattern legacy, coupling cao

Rủi ro P0 — credential fallback

// connection.js, migrate.js, config/config.js (development/test)
password: process.env.DB_PASSWORD || '@Linux121314'

Mật khẩu hardcoded xuất hiện khi thiếu env — phải fail fast ở production/staging.

Config trùng lặp (4 nơi)

File	Dùng cho
src/database/connection.js	Runtime app
src/database/migrate.js	CLI migrate up/down/status/reset
src/database/scripts/partitionManager.js	Partition ops
src/database/config/config.js	Legacy sequelize-cli / docs

Drift giữa các file → deploy/migrate/partition có thể trỏ DB khác nhau nếu sửa một chỗ quên chỗ kia.

Bug tiềm ẩn — test database name

// config/config.js — operator precedence sai
database: process.env.DB_NAME + '_test' || 'kiosk_gaming_test'
// Thực tế: (process.env.DB_NAME + '_test') — nếu DB_NAME undefined → 'undefined_test'

---

4. Models — patterns Sequelize

Factory: mọi model module.exports = (sequelize) => sequelize.define(...).

Associations: tập trung connection.js, không trong từng model file. Nhiều quan hệ dùng constraints: false — ví dụ WalletTransaction → PaymentTransaction qua polymorphic reference_type / reference_id (không FK vật lý).

Hooks: chủ yếu auth (User bcrypt, OTP/session lifecycle). Không raw SQL trong model files.

JSONB metadata (~29 model): nặng nhất ở PaymentTransaction — 6 field: metadata, callbackData, paymentResult, providerRequest, providerResponse, syncMetadata. Cũng có trên UsdWalletTransaction, CashierTransaction, API/callback logs.

Optimistic locking (version)

Entity	Cơ chế
PaymentTransaction	_updateStatusWithValidation — UPDATE WHERE id AND version
Wallet, UsdWallet	updateByIdAndVersion + retry loop trong service (max 5 lần)
Config entities	VfxConfiguration, TransactionLimitsConfig

Model chưa đăng ký global.db

• ReconciliationReport, ReconciliationDiscrepancy — có model + repository + core service, không import trong connection.js
• Gọi global.db.ReconciliationReport sẽ undefined — bug tiềm ẩn nếu worker/service chưa wire qua bootstrap repo

---

5. Migrations

Metric	Value
Số file	207 (YYYYMMDDHHMMSS-kebab-description.js)
Runner	Custom MigrationManager trong migrate.js — không dùng sequelize-cli runtime
Tracking	Bảng "SequelizeMeta" tự quản
CLI	npm run migrate · migrate:status · migrate:rollback · migrate:reset
Docs	src/database/migrations/MIGRATION_GUIDE.md
Generator	npm run migrate:generate

Điểm mạnh

• Migration-first, không sync schema production
• Status/rollback CLI rõ ràng
• Partial indexes cho worker queries (add-worker-query-performance-indexes)
• Seed config dùng ON CONFLICT trong migration (fee configs, zerox static wallet)

Rủi ro & anti-patterns

Pattern	Ví dụ	Rủi ro
Data repair trong migration	repair-zerox-*, dedupe-*, delete-legacy-*	down thường no-op / irreversible
Backfill hàng loạt	wallet-transactions-type-credit-debit, CDN cutover	Khó rollback, chạy lâu trên prod lớn
migrate:reset	DROP SCHEMA public CASCADE	Chỉ local/dev — nguy hiểm nếu nhầm env
ENUM thêm value liên tục	withdrawal OTP, admin action log enums	PostgreSQL không xóa ENUM value — tech debt

Themes gần đây (202604–202606)

1. Cashier full schema (cashier-management-full-schema)
2. USD wallet ledger (gross/net, reservations, purchase_credits type)
3. Online debt settlement refactor + payout wallet/network
4. Payment provider fee configs + ZeroX/Meld repair
5. CDN cutover user_wallets → wallets
6. Portal 2FA, withdrawal OTP, player terminate/ban
7. Worker performance indexes

Khuyến nghị migration governance

• Phân loại header: reversible · data-only · irreversible
• Data repair → script idempotent riêng (src/database/scripts/) thay vì DELETE/UPDATE trong migration
• PR migration: bắt buộc mô tả rollback thực tế (không chỉ stub down)

---

6. Partitioning

Tooling: src/database/scripts/partitionManager.js + npm scripts partition:list, partition:create-year, partition:validate

8 bảng partitioned (monthly RANGE trên created_at):

• payment_transactions
• transaction_events
• callback_logs
• transaction_steps
• game_provider_api_logs
• wallet_transactions
• game_wallet_transactions
• admin_action_logs

Thêm: gamify_reward_webhook_logs (migration riêng).

Lý do: bảng append-only, volume cao — pre-create partition 2025-01 → 2030-12 (~576 partition child tables).

Hệ quả kiến trúc quan trọng

• PostgreSQL yêu cầu UNIQUE/PK trên cột referenced bao gồm partition key → không thể FK trực tiếp tới wallet_transactions(id) nếu chỉ PK (id)
• Code comment rõ trong migration drop credit_transactions — polymorphic reference + app-level integrity thay FK

Vận hành

• Partition không tự tạo khi app boot — cần cron/ops tạo partition trước năm mới (2031+)
• Insert vào tháng chưa có partition → fail runtime

---

7. Idempotency & constraints (DB level)

Bảng	Constraint	Nguồn
payment_transactions.transaction_id	UNIQUE	Model + migration
usd_wallet_transactions (transaction_id, type)	UNIQUE	Migration create-usd-wallet-transactions ✅
wallet_transactions (transaction_id, type)	UNIQUE trong model index	⚠️ Không tìm thấy migration tạo constraint tương ứng — cần verify trên DB production
usd_wallet_reservations.reservation_key	UNIQUE	Idempotent hold USD
payment_provider_fee_configs	UNIQUE scope+provider+operation+method	Migration

Application-level idempotency

(bổ sung khi DB chưa có constraint)

• walletService.credit — check findByTransactionIdAndType trước insert
• usdWalletService.recordAdjustment/recordReversal — unique (transactionId, type)

Partial indexes (worker)

Migration 20260414153000-add-worker-query-performance-indexes.js — pending deposits, retry queue, zerox inflight withdrawals.

Cashier integrity

• Global unique cashiers.username
• Partial unique: một open shift per cashier (WHERE closed_at IS NULL)

---

8. Data integrity & tiền tệ

Foreign keys

• Có FK rõ: cashier schema (CASCADE/RESTRICT/SET NULL), usd_wallet_transactions → usd_wallets, online debt lines
• Tắt FK Sequelize (constraints: false): log tables, polymorphic joins, admin permissions
• Polymorphic refs: wallet_transactions.reference_type + reference_id — không FK

Nullable / polymorphic

• payment_transactions.user_id nullable — CDN buyer qua buyer_type / buyer_id
• Dual reference pattern phổ biến cho ledger ↔ payment

DECIMAL precision — không thống nhất

Context	Precision
CDN wallet balance/ledger	DECIMAL(18, 8)
USD wallet gross/net	DECIMAL(18, 8)
Payment amount	DECIMAL(15, 2) — legacy
Game wallet transaction	DECIMAL(15, 2)
Cashier physical cash	DECIMAL(18, 2)
Fee rates	DECIMAL(5,4) – DECIMAL(8,6) snapshot

Cùng hệ monetary nhưng precision khác → risk rounding khi convert/cross-ledger; nên document invariant hoặc chuẩn hóa dần.

Dual wallet systems

• wallets (CDN credits) + usd_wallets (USD gross/net) — không merge DB
• Integrity phụ thuộc service layer discipline — mọi money movement phải nằm trong transaction boundary rõ ràng

---

9. Anti-patterns

Anti-pattern	Chi tiết
global.db singleton	122 controller-direct-db — query trực tiếp, khó test
Config DB ×4	Drift giữa connection / migrate / partition / config.js
Model ↔ migration drift	Unique index trong model nhưng thiếu migration (wallet_transactions)
Model orphan	ReconciliationReport không trong connection.js
JSONB không schema	Provider payload, sync state — khó query/audit/version
Partition ops manual	Quên tạo partition → outage insert
Irreversible migrations labeled rollback-able	migrate:rollback misleading cho data repair
Repository business logic	267 baseline repository-conditional-logic
ENUM proliferation	Không remove được value cũ
Test DB name bug	config.js test database expression
N+1	Deep include chains payment + game + user

---

10. Điểm mạnh

1. Migration-first schema — production an toàn
2. Partitioning có tooling + validation scripts
3. Optimistic locking trên wallet và payment status
4. Idempotency design USD ledger (transaction_id, type)
5. Fee/amount snapshot tại payment creation — audit trail
6. UTC enforced everywhere
7. Production pool conservative (max 8)
8. Cashier schema FK thiết kế cẩn thận
9. State machine payment trong model (_updateStatusWithValidation)
10. Worker-oriented partial indexes

---

11. Khuyến nghị (ưu tiên)

#	Priority	Hành động
DB-1	P0	Gỡ hardcoded DB_PASSWORD — fail fast production/staging
DB-2	P0	Đăng ký ReconciliationReport + ReconciliationDiscrepancy vào connection.js
DB-3	P0	Verify + migration UNIQUE (transaction_id, type) trên wallet_transactions nếu DB thiếu
DB-4	P0	Automate partition provisioning (cron tạo partition N+1; alert partition:validate)
DB-5	P1	Centralize DB config → databaseConfig.js (1 source)
DB-6	P1	Document dual-wallet invariant (wallets vs usd_wallets)
DB-7	P1	Tách data repair khỏi schema migration
DB-8	P1	Chuẩn hóa DECIMAL hoặc document precision per domain
DB-9	P2	JSONB schema version (metadata_schema_version) + validate service layer
DB-10	P2	Fix config.js test database expression
DB-11	P2	ENUM governance — cân nhắc STRING + check constraint cho value hay đổi
DB-12	P2	Giảm constraints: false cho quan hệ ổn định (non-partitioned refs)
DB-13	P3	Migration class tag: reversible / data-only / irreversible
DB-14	P3	Repository refactor — pure query, logic lên service/core

---

12. Checklist PR (database)

• Migration mới có tag rollback thực tế (reversible / data-only / irreversible)
• Data repair không nằm trong migration DDL nếu có thể tách script
• Model mới đăng ký connection.js + bootstrap/repositories.js
• Unique/index trong model có migration tương ứng
• Money field dùng đúng precision domain
• JSONB metadata có lý do; cân nhắc schema version
• Partition impact đã xem xét (insert vào bảng partitioned)
• Không thêm hardcoded DB credentials

---

13. Definition of Done — database health

• DB config single source; zero hardcoded credentials
• ReconciliationReport models registered in connection.js
• wallet_transactions UNIQUE (transaction_id, type) verified on production
• Partition validate pass; partition năm N+1 đã provision
• Dual-wallet invariant documented (link Finance System)
• Test database name fixed in config.js

---

14. File tham chiếu nhanh

File	Vai trò
src/database/connection.js	Sequelize, pool, global.db, associations
src/database/migrate.js	Custom migration runner
src/database/scripts/partitionManager.js	Partition CRUD CLI
src/database/config/config.js	Legacy config (duplicate)
src/database/migrations/MIGRATION_GUIDE.md	Hướng dẫn migration
src/database/migrations/20260121224500-convert-tables-to-partitioned.js	Partition conversion
src/database/migrations/20260608140000-cashier-management-full-schema.js	Cashier schema
src/database/migrations/20260427070100-create-usd-wallet-transactions.js	USD ledger + unique constraint
src/bootstrap/repositories.js	Repository wiring

---

15. Changelog

Date	Author	Notes
2026-06-20	Code review	Tách từ Backend code review §8