Wallet Service – Technical Architecture ======================================= Purpose ------- Define the end-to-end wallet service contract, data model, and operational rules that developers rely on to implement and operate wallet balance changes (deposit, withdrawal, rewards, commissions, internal transfers) with strong balance guarantees and a single ledger of record. Goals ----- - Single ledger of truth using the existing `Transaction` model. - Atomic balance updates with sufficient-funds enforcement for deductions. - Clear contract for `add_point` / `deduct_point` (and composable transfer) with required fields. - DB-portable SQL approach (MySQL now; Postgres later; Mongo adapter possible). - Auditable trail with initiator and metadata captured on every operation. Core Decisions -------------- - Single ledger: use `Transaction` as the only ledger table; no separate wallet ledger. - Point types: every transaction **must** specify `point_type` (indexed). Point types are project-specific and confirmed by business rules. - Transcodes: replace `descr_code` and `trans_type` with a single `transcode` (transaction code, indexed). - Always record: remove `save_record`; every successful balance change writes a ledger row. - SQL guard: use raw SQL with a guarded `WHERE` clause and `rowcount` check for deductions. - Balance store: **Option A (chosen)** — normalized `user_wallet_balances` table with `UNIQUE(user_id, point_type)`; no column churn on `User`. Data Model (Transaction) ------------------------ - Required fields: - `user_id` (FK) - `transaction_type` (existing enum/choice; include wallet values: `wallet_credit`, `wallet_debit`, `wallet_transfer_out`, `wallet_transfer_in`) - `point_type` (varchar) — target wallet/balance column; **indexed**. - `transcode` (varchar) — classification code for reconstruction/reporting; **indexed**. - `amount` (Decimal, 8 dp recommended) - `balance_before`, `balance_after` (Decimal) - `status` (enum; reuse existing) - `created_at`, `updated_at` - Metadata: - `data` (JSON) for remarks, initiator info, params/extra fields, allow_negative flag, request ids, channel, etc. - Optional `reference_id`/`external_id` (if already present) for idempotency or linkage. - Indexes: - `point_type` - `(user_id, point_type, transaction_type, created_at)` - `(transcode, created_at)` Point Type Registry (Option A only) ----------------------------------- - Maintain an allowlist mapping `point_type` → `user_wallet_balances` row. - Storage: `user_wallet_balances(user_id FK, point_type, balance, UNIQUE(user_id, point_type))` with index on `(user_id, point_type)`. - Adding new point types: seed rows per `user_id` and `point_type`; no schema change required. - Validation: reject any `point_type` not in the registry before SQL. - Location: keep registry centrally (settings constant or small table) so services/ingest stay consistent per project. - SQL targets `user_wallet_balances` rows (guarded `UPDATE ... WHERE user_id=%s AND point_type=%s AND balance >= %s`). Service Contract ---------------- - `add_point(point_type, amount, remarks, transcode, params=None)` - `deduct_point(point_type, amount, remarks, transcode, allow_negative=False, params=None)` - Optional higher-level: `transfer(user_from, user_to, point_type, amount, remarks, transcode)` implemented as deduct+add within one `transaction.atomic()`. - Inputs must supply `point_type` and `transcode`; no `save_record` argument. - Outputs: returns the created `Transaction` id (or a result object containing balances if desired). Balance Update Algorithm ------------------------ - Wrap public methods in `transaction.atomic()`. - Use raw SQL with identifier quoting (`connection.ops.quote_name`): - Add: `UPDATE user_wallet_balances SET balance=balance+%s WHERE user_id=%s AND point_type=%s` - Deduct (guard): `UPDATE user_wallet_balances SET balance=balance-%s WHERE user_id=%s AND point_type=%s AND balance >= %s` - If `allow_negative=True`, omit the balance guard (use sparingly). - Execute with parameters; inspect `cursor.rowcount`: - Deduct: `rowcount == 0` ⇒ insufficient funds/race → raise `WALLET_INSUFFICIENT`. - Add: expect `rowcount == 1`; otherwise raise `WALLET_SQL_ERROR`. - Reload balance (or return balance_after if fetched via `RETURNING` when on Postgres). Validation Rules ---------------- - `point_type` must be in the registry and mapped to `user_wallet_balances`. - `amount` > 0, Decimal, quantized to 8 dp; reject floats. - `remarks` optional but stored in `data`. - `transcode` required; string/enum identifying business action (e.g., `wallet-topup`, `commission-distribution`). - `params["data"]` (extra metadata) must not collide with reserved ledger fields; store inside `data`. Ledger Write ------------ - Always insert a `Transaction` row after a successful balance mutation, inside the same atomic block. - Required fields on insert: `user_id`, `transaction_type`, `point_type`, `transcode`, `amount`, `balance_before`, `balance_after`, `status`, plus `data` (with remarks, initiator, allow_negative, params). - For transfers, create two rows: debit for sender (`wallet_transfer_out`), credit for receiver (`wallet_transfer_in`), both sharing a correlation id in `data`. Error Codes (suggested) ----------------------- | Code | Condition | Client handling | Notes | |------------------------|-------------------------------------------------------|------------------------------------|-------------------------------------------------| | WALLET_INVALID_POINT | `point_type` not allowed/not mapped to a balance col | Show configuration error | Validate before SQL | | WALLET_INVALID_AMOUNT | Amount <= 0 or not Decimal-scale compliant | Ask for a valid amount | Quantize to 8 dp | | WALLET_INSUFFICIENT | Deduct UPDATE rowcount == 0 (balance too low/race) | Inform insufficient balance | Guarded WHERE clause | | WALLET_ALLOW_NEG_BLOCK | allow_negative=False but negative would result | Inform insufficient balance | Same surface as insufficient | | WALLET_SQL_ERROR | SQL execution failure (connectivity/constraint) | Retry or show generic failure | Log SQL + params (redact PII) | | WALLET_LEDGER_ERROR | Failed to insert `Transaction` row after balance move | Retry or manual reconciliation | Keep in same atomic block to avoid partials | Concurrency & Isolation ----------------------- - Prefer single-statement guarded UPDATEs; avoid pre-read unless necessary. - Keep operations short-lived inside `transaction.atomic()`. - For Postgres at higher isolation, optionally retry once on serialization failures. - Transfer ordering: deduct first, then credit, to minimize overdraft risk. Observability & Audit --------------------- - Log structured events: user_id, point_type, amount, transcode, initiator, rowcount, allow_negative. - Emit metrics: successes/failures by operation type and point_type; latency histograms. - Capture initiator (system/user/admin) in `data`. API/DRF Integration ------------------- - Require `point_type`, `transcode`, `amount`, `remarks` in request DTOs. - Map business actions to `transcode` values centrally. - Serialize `Transaction` with `point_type`, `transcode`, `balance_before/after`, and `data`. DB Portability -------------- - Use parameterized SQL and quoted identifiers. - MySQL: optional `LIMIT 1` can be appended. - Postgres: can use `RETURNING ` to avoid an extra reload; still check `rowcount`. - Mongo (future): implement equivalent atomic update with `$inc` and balance guard via conditional update. Migration/Implementation Steps ------------------------------ - Add fields to `Transaction`: `point_type` (indexed) and `transcode` (indexed). - Add indexes `(user_id, point_type, transaction_type, created_at)` and `(transcode, created_at)`. - Create `user_wallet_balances` table with `user_id FK`, `point_type`, `balance`, unique `(user_id, point_type)`, and index on `(user_id, point_type)`. - Seed `user_wallet_balances` rows per supported `point_type` for all existing users (or on-demand with defaults). - Update service layer to enforce required params and to always write a ledger row. - Update serializers/ingest pipelines to provide `point_type` and `transcode`. - Document `transcode` registry and `point_type` registry (Option A) in code/constants. Open Items (to confirm) ----------------------- - Set of supported `point_type` values and their semantics (seed `user_wallet_balances` accordingly). - Canonical list of `transcode` values per business flow (see registry below; extend as needed). - Whether balance_after is returned to callers or just the transaction id. Transcode Registry (defaults + suggestions) ------------------------------------------- - Defaults (provided): - `wallet-add` = `1000` - `wallet-topup` = `1001` - `wallet-deduct` = `1002` - `wallet-transfer` = `1003` - `wallet-withdraw` = `1004` - `wallet-withdraw-cancel` = `1005` - `wallet-crypto-topup` = `1006` - `wallet-adjust-manual` = `1007` (admin/manual adjustment) - `wallet-promo-credit` = `1008` (promo/bonus credit) - `package-activation` = `1100` - `package-upgrade` = `1101` - `package-cancel` = `1102` - `commission-distribution` = `1200` - `commission-distribution-reverse` = `1201` - `roi-distribution` = `1300` - `roi-distribution-reverse` = `1301` - `product-order` = `1400` - `product-order-shipping-fee` = `1401` - `product-order-cancel` = `1402` - `product-order-refund` = `1403` - `product-order-partial-refund` = `1404` - `product-order-fee-adjust` = `1405` - `product-redemption` = `1500` - `product-redemption-cancel` = `1501` - Additional suggested transcodes (general-purpose; use as needed): - `wallet-fee` = `1009` (wallet-level fee/charge) - `wallet-rollback` = `1010` (technical rollback of prior wallet move) - `deposit-forfeit` = `1011` (deposit below minimum; forfeited per business rule) - `deposit-reversal` = `1012` (gateway failure reversal) - `package-renewal` = `1103` - `commission-bonus` = `1202` (special bonus commission) - `commission-chargeback` = `1203` (chargeback of commission) - `roi-bonus` = `1302` - `roi-adjust` = `1303` (correction/true-up) - `kyc-bonus` = `1700` - `referral-bonus` = `1701` - `referral-bonus-reverse` = `1702` - `airdrop-credit` = `1703` - `migration-credit` = `1800` (data migration/import credit) - `migration-debit` = `1801` (data migration/import debit) Project-specific transcodes (Telegram Mini App; extend here) ----------------------------------------------------------- - Baseline (fixed): - `invest` = `1600` (user creates/funds an investment) - `invest-yield` = `1601` (daily yield/accrual credited to wallet) - Suggested additions for this project (align to `requirements-analysis`; extend as needed): - `invest-activate` = `1602` (investment moves from pending to active) - `invest-complete` = `1603` (contract duration completed) - `invest-cancel` = `1604` (pre-activation cancellation or rollback) - `daily-reward-hold` = `1610` (calculated reward held during 24h window) - `daily-reward-distribute` = `1611` (reward released after hold period) - `referral-commission-l1` = `1620` (level-1 commission payout) - `referral-commission-l2` = `1621` (level-2 commission payout) - `referral-commission-l3` = `1622` (level-3 commission payout) - `withdraw-fee` = `1630` (1% + fixed fee debit applied to withdrawal request) - `withdraw-refund` = `1631` (refund of withdrawal + fees on cancellation) - Teams should append more project-specific transcodes in this block as new flows emerge (e.g., promotional campaigns, seasonal bonuses, ad-hoc true-ups). Keep numbers unique and document meaning centrally.