Database code review — kioskgaming_backend
| Field | Value |
|---|---|
| Repo | kioskgaming_backend/src/database/ |
| Review date | 2026-06-20 |
| Stack | PostgreSQL · Sequelize ORM · custom migration runner |
| Scale | 76 models · 207 migrations · 8 partitioned tables (+ gamify logs) |
| Related docs | Finance System · Backend code review (full) · Migration guide: kioskgaming_backend/src/database/migrations/MIGRATION_GUIDE.md |
1. Summary
| Criterion | Assessment |
|---|---|
| Schema design (wallet, payment, hierarchy) | ✅ Strong — clear dual wallet, ledger with balance snapshots |
| Migration discipline | ✅ Good — migration-first, no sync() in production |
| Partitioning high-volume tables | ✅ Intentional + CLI tooling |
| Optimistic locking / idempotency | ✅ Wallet + USD ledger + payment status |
| Config & secrets | ❌ Config duplicated across 4 files; hardcoded password fallback |
| Model ↔ DB alignment | ⚠️ Some unique/index declared in model only |
| Actual rollback | ⚠️ Many data-repair migrations are irreversible |
| JSONB metadata | ⚠️ Flexible but lacks schema/version |
| Currency precision | ⚠️ DECIMAL(18,8) vs (15,2) mixed across domains |
| Test DB isolation | ⚠️ config.js test database expression has potential bug |
Verdict: Production-grade foundation for fintech kiosk (ledger, partition, idempotency), but operations and governance need tightening: centralized config, partition automation, DB ↔ model constraint alignment, separate data repair from schema migration.
2. Schema overview — table groups
| Group | Representative tables | Notes |
|---|---|---|
| Users & auth | users, email_codes, phone_codes, user_sessions, account_otps | bcrypt hooks on User |
| Payment & gateway | payment_transactions, callback_logs, payment_api_logs, transaction_events, transaction_fraud_flags | High volume, 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 on payment |
| Cashier | cashiers, cashier_shifts, cashier_transactions, cashier_shift_adjustments | Careful FK/CASCADE design (Jun 2026) |
| Admin & governance | admins, admin_action_logs, admin_broadcasts, permissions | Action log partitioned |
| Reconciliation | reconciliation_reports, reconciliation_discrepancies | ⚠️ Model + repo exist, not yet loaded in connection.js |
| Config / limits | payment_provider_fee_configs, transaction_limits_configs, vfx_configurations | Fee snapshot at payment creation |
Naming conventions
- Tables:
snake_case, plural,freezeTableName: true - DB columns:
snake_case· JS fields:camelCase+field: 'snake_case' - Timestamps:
created_at,updated_at· timezone UTC (+00:00) - PK: mixed —
INTEGERauto-increment (payment, legacy logs) ·UUID(wallet, cashier, new CDN) - PostgreSQL ENUM:
DataTypes.ENUM(...)— common forstatus,type,owner_type - Soft delete: not used — lifecycle via
status(terminated,closed,cancelled)
3. Connection & runtime
File: src/database/connection.js
| Aspect | Details |
|---|---|
| Pool (development) | max 20, min 2, acquire 15s |
| Pool (production) | max 8 (more conservative than dev) |
| Query timeout | SEQUELIZE_QUERY_TIMEOUT_MS, default 8000ms |
| Schema | No sequelize.sync() — migrations only |
| Associations | ~330 lines declared centrally in connection.js |
global.db | Exports ~74 models + sequelize — legacy pattern, high coupling |
P0 risk — credential fallback
// connection.js, migrate.js, config/config.js (development/test)
password: process.env.DB_PASSWORD || '@Linux121314'
Hardcoded password appears when env is missing — must fail fast in production/staging.
Duplicate config (4 locations)
| File | Used for |
|---|---|
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 between files → deploy/migrate/partition may point to different DBs if one location is updated and others are not.
Potential bug — test database name
// config/config.js — wrong operator precedence
database: process.env.DB_NAME + '_test' || 'kiosk_gaming_test'
// Actual: (process.env.DB_NAME + '_test') — if DB_NAME undefined → 'undefined_test'
4. Models — patterns Sequelize
Factory: every model module.exports = (sequelize) => sequelize.define(...).
Associations: centralized in connection.js, not in individual model files. Many relationships use constraints: false — e.g. WalletTransaction → PaymentTransaction via polymorphic reference_type / reference_id (no physical FK).
Hooks: primarily auth (User bcrypt, OTP/session lifecycle). No raw SQL in model files.
JSONB metadata (~29 models): heaviest on PaymentTransaction — 6 fields: metadata, callbackData, paymentResult, providerRequest, providerResponse, syncMetadata. Also on UsdWalletTransaction, CashierTransaction, API/callback logs.
Optimistic locking (version)
| Entity | Mechanism |
|---|---|
PaymentTransaction | _updateStatusWithValidation — UPDATE WHERE id AND version |
Wallet, UsdWallet | updateByIdAndVersion + retry loop in service (max 5 attempts) |
| Config entities | VfxConfiguration, TransactionLimitsConfig |
Models not registered in global.db
ReconciliationReport,ReconciliationDiscrepancy— have model + repository + core service, not imported inconnection.js- Calling
global.db.ReconciliationReportreturns undefined — potential bug if worker/service not wired via bootstrap repo
5. Migrations
| Metric | Value |
|---|---|
| File count | 207 (YYYYMMDDHHMMSS-kebab-description.js) |
| Runner | Custom MigrationManager in migrate.js — does not use sequelize-cli runtime |
| Tracking | Self-managed "SequelizeMeta" table |
| CLI | npm run migrate · migrate:status · migrate:rollback · migrate:reset |
| Docs | src/database/migrations/MIGRATION_GUIDE.md |
| Generator | npm run migrate:generate |
Strengths
- Migration-first, no production schema sync
- Clear status/rollback CLI
- Partial indexes for worker queries (
add-worker-query-performance-indexes) - Seed config uses
ON CONFLICTin migration (fee configs, zerox static wallet)
Risks & anti-patterns
| Pattern | Example | Risk |
|---|---|---|
| Data repair in migration | repair-zerox-*, dedupe-*, delete-legacy-* | down usually no-op / irreversible |
| Bulk backfill | wallet-transactions-type-credit-debit, CDN cutover | Hard to rollback, long-running on large prod |
migrate:reset | DROP SCHEMA public CASCADE | Local/dev only — dangerous if wrong env |
| Continuous ENUM value additions | withdrawal OTP, admin action log enums | PostgreSQL cannot remove ENUM values — tech debt |
Recent themes (202604–202606)
- Cashier full schema (
cashier-management-full-schema) - USD wallet ledger (gross/net, reservations,
purchase_creditstype) - Online debt settlement refactor + payout wallet/network
- Payment provider fee configs + ZeroX/Meld repair
- CDN cutover
user_wallets→wallets - Portal 2FA, withdrawal OTP, player terminate/ban
- Worker performance indexes
Migration governance recommendations
- Classify header:
reversible·data-only·irreversible - Data repair → separate idempotent script (
src/database/scripts/) instead of DELETE/UPDATE in migration - Migration PR: must describe actual rollback (not just stub
down)
6. Partitioning
Tooling: src/database/scripts/partitionManager.js + npm scripts partition:list, partition:create-year, partition:validate
8 partitioned tables (monthly RANGE on created_at):
payment_transactionstransaction_eventscallback_logstransaction_stepsgame_provider_api_logswallet_transactionsgame_wallet_transactionsadmin_action_logs
Also: gamify_reward_webhook_logs (separate migration).
Reason: append-only tables, high volume — pre-create partitions 2025-01 → 2030-12 (~576 child partition tables).
Important architectural consequences
- PostgreSQL requires UNIQUE/PK on referenced columns including partition key → cannot FK directly to
wallet_transactions(id)if PK is only(id) - Clear code comment in migration dropping
credit_transactions— polymorphic reference + app-level integrity instead of FK
Operations
- Partitions are not auto-created on app boot — cron/ops must create partitions before the new year (2031+)
- Insert into month without partition → runtime failure
7. Idempotency & constraints (DB level)
| Table | Constraint | Source |
|---|---|---|
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 in model index | ⚠️ No migration found creating corresponding constraint — verify on production DB |
usd_wallet_reservations.reservation_key | UNIQUE | Idempotent hold USD |
payment_provider_fee_configs | UNIQUE scope+provider+operation+method | Migration |
Application-level idempotency
(supplement when DB lacks constraint)
walletService.credit— checkfindByTransactionIdAndTypebefore insertusdWalletService.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: one open shift per cashier (
WHERE closed_at IS NULL)
8. Data integrity & currency
Foreign keys
- Clear FKs: cashier schema (CASCADE/RESTRICT/SET NULL),
usd_wallet_transactions→usd_wallets, online debt lines - Sequelize FK disabled (
constraints: false): log tables, polymorphic joins, admin permissions - Polymorphic refs:
wallet_transactions.reference_type+reference_id— no FK
Nullable / polymorphic
payment_transactions.user_idnullable — CDN buyer viabuyer_type/buyer_id- Dual reference pattern common for ledger ↔ payment
DECIMAL precision — inconsistent
| 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 |
Same monetary system but different precision → rounding risk on convert/cross-ledger; document invariant or standardize gradually.
Dual wallet systems
wallets(CDN credits) +usd_wallets(USD gross/net) — not merged in DB- Integrity depends on service layer discipline — all money movement must be within clear transaction boundaries
9. Anti-patterns
| Anti-pattern | Details |
|---|---|
global.db singleton | 122 controller-direct-db — direct queries, hard to test |
| Config DB ×4 | Drift between connection / migrate / partition / config.js |
| Model ↔ migration drift | Unique index in model but missing migration (wallet_transactions) |
| Model orphan | ReconciliationReport not in connection.js |
| JSONB without schema | Provider payload, sync state — hard to query/audit/version |
| Partition ops manual | Forgotten partition → insert outage |
| Irreversible migrations labeled rollback-able | migrate:rollback misleading for data repair |
| Repository business logic | 267 baseline repository-conditional-logic |
| ENUM proliferation | Cannot remove old values |
| Test DB name bug | config.js test database expression |
| N+1 | Deep include chains payment + game + user |
10. Strengths
- Migration-first schema — production safe
- Partitioning with tooling + validation scripts
- Optimistic locking on wallet and payment status
- Idempotency design USD ledger
(transaction_id, type) - Fee/amount snapshot at payment creation — audit trail
- UTC enforced everywhere
- Production pool conservative (max 8)
- Carefully designed cashier schema FKs
- State machine payment in model (
_updateStatusWithValidation) - Worker-oriented partial indexes
11. Recommendations (priority)
| # | Priority | Action |
|---|---|---|
| DB-1 | P0 | Remove hardcoded DB_PASSWORD — fail fast production/staging |
| DB-2 | P0 | Register ReconciliationReport + ReconciliationDiscrepancy in connection.js |
| DB-3 | P0 | Verify + migration UNIQUE (transaction_id, type) on wallet_transactions if DB lacks it |
| DB-4 | P0 | Automate partition provisioning (cron create 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 | Separate data repair from schema migration |
| DB-8 | P1 | Standardize DECIMAL or 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 — consider STRING + check constraint for values that may change |
| DB-12 | P2 | Reduce constraints: false for stable relationships (non-partitioned refs) |
| DB-13 | P3 | Migration class tag: reversible / data-only / irreversible |
| DB-14 | P3 | Repository refactor — pure query, logic up to service/core |
12. PR checklist (database)
- New migration has actual rollback tag (
reversible/data-only/irreversible) - Data repair not in migration DDL if script can be separated
- New model registered in
connection.js+bootstrap/repositories.js - Unique/index in model has corresponding migration
- Money field uses correct precision domain
- JSONB metadata has rationale; consider schema version
- Partition impact considered (insert into partitioned table)
- Do not add hardcoded DB credentials
13. Definition of Done — database health
- DB config single source; zero hardcoded credentials
-
ReconciliationReportmodels registered inconnection.js -
wallet_transactionsUNIQUE(transaction_id, type)verified on production - Partition validate pass; year N+1 partition provisioned
- Dual-wallet invariant documented (link Finance System)
- Test database name fixed in
config.js
14. Quick reference files
| File | Role |
|---|---|
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 | Migration guide |
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 | Split from Backend code review §8 |