Database Design
Wallet Module
WALLET
1. Introduction
1.1 Purpose
This document defines the database schema for the Wallet module. It includes table definitions, relationships, indexes, and migration scripts.
1.2 Database Overview
| Aspect | Value |
|---|
| Database | wallet_db |
| Engine | PostgreSQL 15+ |
| ORM | Drizzle ORM |
| Tables | 5 tables |
2. Entity Relationship Diagram
Figure 1: Wallet Database ERD
3. Table Definitions
3.1 wallet_wallets
| Column | Type | Constraints | Description |
|---|
| id | BIGSERIAL | PRIMARY KEY | Unique wallet identifier |
| user_id | BIGINT | UNIQUE, NOT NULL | Reference to auth_users.id |
| currency | VARCHAR(3) | NOT NULL, DEFAULT 'VND' | Currency code (ISO 4217) |
| balance | DECIMAL(15,2) | NOT NULL, DEFAULT 0 | Current wallet balance |
| frozen_amount | DECIMAL(15,2) | NOT NULL, DEFAULT 0 | Amount frozen for pending withdrawals |
| status | VARCHAR(20) | NOT NULL, DEFAULT 'active' | active, frozen, closed |
| pin_hash | VARCHAR(255) | | Hashed 6-digit PIN |
| daily_limit | DECIMAL(15,2) | NOT NULL, DEFAULT 50000000 | Daily transaction limit (VND) |
| monthly_limit | DECIMAL(15,2) | NOT NULL, DEFAULT 500000000 | Monthly transaction limit (VND) |
| version | INTEGER | NOT NULL, DEFAULT 1 | Optimistic locking version |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Last update timestamp |
3.2 wallet_transactions
| Column | Type | Constraints | Description |
|---|
| id | BIGSERIAL | PRIMARY KEY | Unique transaction identifier |
| wallet_id | BIGINT | NOT NULL, FK -> wallet_wallets.id | Associated wallet |
| type | VARCHAR(20) | NOT NULL | deposit, withdrawal, payment, refund, transfer_in, transfer_out |
| amount | DECIMAL(15,2) | NOT NULL | Transaction amount |
| currency | VARCHAR(3) | NOT NULL | Currency code |
| balance_before | DECIMAL(15,2) | NOT NULL | Balance before transaction |
| balance_after | DECIMAL(15,2) | NOT NULL | Balance after transaction |
| status | VARCHAR(20) | NOT NULL | pending, processing, completed, failed, cancelled |
| reference_type | VARCHAR(50) | | Related entity type |
| reference_id | BIGINT | | Related entity ID |
| description | TEXT | | Transaction description |
| metadata | JSONB | | Additional context data |
| failure_reason | TEXT | | Reason for failed transaction |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Transaction timestamp |
| updated_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Last update timestamp |
3.3 wallet_payment_methods
| Column | Type | Constraints | Description |
|---|
| id | BIGSERIAL | PRIMARY KEY | Unique payment method identifier |
| wallet_id | BIGINT | NOT NULL, FK -> wallet_wallets.id | Associated wallet |
| type | VARCHAR(20) | NOT NULL | bank_account, credit_card, debit_card, e_wallet |
| provider | VARCHAR(50) | NOT NULL | Bank or card provider name |
| account_number | VARCHAR(100) | NOT NULL | Encrypted account/card number |
| account_name | VARCHAR(100) | NOT NULL | Account holder name |
| is_default | BOOLEAN | NOT NULL, DEFAULT false | Default payment method |
| is_verified | BOOLEAN | NOT NULL, DEFAULT false | Verification status |
| metadata | JSONB | | Additional provider data |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Last update timestamp |
3.4 wallet_payment_orders
| Column | Type | Constraints | Description |
|---|
| id | BIGSERIAL | PRIMARY KEY | Unique order identifier |
| wallet_id | BIGINT | NOT NULL, FK -> wallet_wallets.id | Associated wallet |
| order_type | VARCHAR(30) | NOT NULL | job_posting, featured_job, premium_subscription, recruitment_service |
| amount | DECIMAL(15,2) | NOT NULL | Order amount |
| currency | VARCHAR(3) | NOT NULL | Currency code |
| status | VARCHAR(20) | NOT NULL | pending, paid, refunded, cancelled |
| related_module | VARCHAR(20) | | Related module code (RECR) |
| related_id | BIGINT | | Related entity ID in external module |
| description | TEXT | | Order description |
| paid_at | TIMESTAMP | | Payment completion timestamp |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Last update timestamp |
3.5 wallet_payout_requests
| Column | Type | Constraints | Description |
|---|
| id | BIGSERIAL | PRIMARY KEY | Unique request identifier |
| wallet_id | BIGINT | NOT NULL, FK -> wallet_wallets.id | Associated wallet |
| payment_method_id | BIGINT | NOT NULL, FK -> wallet_payment_methods.id | Target payment method |
| amount | DECIMAL(15,2) | NOT NULL | Withdrawal amount |
| status | VARCHAR(20) | NOT NULL | pending, approved, processing, completed, rejected |
| admin_notes | TEXT | | Admin comments |
| processed_by | BIGINT | | Admin user ID who processed |
| processed_at | TIMESTAMP | | Processing completion timestamp |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Request creation timestamp |
| updated_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | Last update timestamp |
4. Indexes
| Table | Index | Columns | Type |
|---|
| wallet_wallets | wallet_wallets_user_id_key | user_id | UNIQUE |
| wallet_wallets | wallet_wallets_status_idx | status | BTREE |
| wallet_transactions | wallet_transactions_wallet_id_idx | wallet_id | BTREE |
| wallet_transactions | wallet_transactions_type_idx | type | BTREE |
| wallet_transactions | wallet_transactions_status_idx | status | BTREE |
| wallet_transactions | wallet_transactions_created_at_idx | created_at | BTREE |
| wallet_payment_methods | wallet_payment_methods_wallet_id_idx | wallet_id | BTREE |
| wallet_payment_orders | wallet_payment_orders_wallet_id_idx | wallet_id | BTREE |
| wallet_payment_orders | wallet_payment_orders_status_idx | status | BTREE |
| wallet_payout_requests | wallet_payout_requests_wallet_id_idx | wallet_id | BTREE |
| wallet_payout_requests | wallet_payout_requests_status_idx | status | BTREE |
5. Migration Scripts
5.1 Initial Migration
CREATE TABLE wallet_wallets (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT UNIQUE NOT NULL,
currency VARCHAR(3) NOT NULL DEFAULT 'VND',
balance DECIMAL(15,2) NOT NULL DEFAULT 0,
frozen_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
status VARCHAR(20) NOT NULL DEFAULT 'active',
pin_hash VARCHAR(255),
daily_limit DECIMAL(15,2) NOT NULL DEFAULT 50000000,
monthly_limit DECIMAL(15,2) NOT NULL DEFAULT 500000000,
version INTEGER NOT NULL DEFAULT 1,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE wallet_transactions (
id BIGSERIAL PRIMARY KEY,
wallet_id BIGINT NOT NULL REFERENCES wallet_wallets(id),
type VARCHAR(20) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
currency VARCHAR(3) NOT NULL,
balance_before DECIMAL(15,2) NOT NULL,
balance_after DECIMAL(15,2) NOT NULL,
status VARCHAR(20) NOT NULL,
reference_type VARCHAR(50),
reference_id BIGINT,
description TEXT,
metadata JSONB,
failure_reason TEXT,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE wallet_payment_methods (
id BIGSERIAL PRIMARY KEY,
wallet_id BIGINT NOT NULL REFERENCES wallet_wallets(id),
type VARCHAR(20) NOT NULL,
provider VARCHAR(50) NOT NULL,
account_number VARCHAR(100) NOT NULL,
account_name VARCHAR(100) NOT NULL,
is_default BOOLEAN NOT NULL DEFAULT false,
is_verified BOOLEAN NOT NULL DEFAULT false,
metadata JSONB,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE wallet_payment_orders (
id BIGSERIAL PRIMARY KEY,
wallet_id BIGINT NOT NULL REFERENCES wallet_wallets(id),
order_type VARCHAR(30) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
currency VARCHAR(3) NOT NULL,
status VARCHAR(20) NOT NULL,
related_module VARCHAR(20),
related_id BIGINT,
description TEXT,
paid_at TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE wallet_payout_requests (
id BIGSERIAL PRIMARY KEY,
wallet_id BIGINT NOT NULL REFERENCES wallet_wallets(id),
payment_method_id BIGINT NOT NULL REFERENCES wallet_payment_methods(id),
amount DECIMAL(15,2) NOT NULL,
status VARCHAR(20) NOT NULL,
admin_notes TEXT,
processed_by BIGINT,
processed_at TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
6. Data Requirements Traceability
| Req ID |
Table |
Description |
Related FR |
| DR-001 | wallet_wallets | User wallet accounts with balance and limits | FR-001, FR-002 |
| DR-002 | wallet_transactions | Complete transaction history with audit trail | FR-004, FR-009 |
| DR-003 | wallet_payment_methods | Linked bank accounts and payment cards | FR-005 |
| DR-004 | wallet_payment_orders | Payment orders for services | FR-011, FR-012, FR-013 |
| DR-005 | wallet_payout_requests | Withdrawal requests for admin approval | FR-007, FR-015 |
