# Wallet Management System — Implementation Specification

**Version:** 1.0  
**Audience:** Engineering team  
**Status:** Ready for implementation

---

## 1. Overview

This document defines the data model, business rules, and implementation guidance for a multi-product, multi-currency wallet management system. It incorporates the following confirmed product decisions:

| Decision | Answer |
|---|---|
| Can a customer hold the same wallet type twice? | **Yes** — multiple instances allowed |
| Is a sub-wallet a first-class entity? | **No** — always a child of a parent wallet |
| Can sub-wallets be transferred? | **Yes** — to other wallet products and other customers (as transactions) |
| Currency classification | **Config-driven** — per wallet product, each currency marked as `primary` or `display_only` |
| Wallet closure behaviour | **Freeze** — balances are frozen, not cleared or transferred |

---

## 2. Data Model

### 2.1 Entity hierarchy

```
Customer
└── WalletProduct (many per customer, same type allowed multiple times)
    ├── CurrencyConfig (per product — defines which currencies are active and their class)
    └── SubWallet (child of WalletProduct, never standalone)
        └── BalanceLedger (one row per currency, per sub-wallet)
```

### 2.2 Core tables

#### `customers`

```sql
CREATE TABLE customers (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  external_ref    VARCHAR(128) UNIQUE NOT NULL,   -- your CRM or KYC ref
  kyc_tier        SMALLINT NOT NULL DEFAULT 0,    -- 0 = unverified, 1 = basic, 2 = full
  status          VARCHAR(32) NOT NULL DEFAULT 'active',  -- active | suspended | closed
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

#### `wallet_product_types`

Defines the catalogue of wallet products your platform offers.

```sql
CREATE TABLE wallet_product_types (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code            VARCHAR(64) UNIQUE NOT NULL,   -- e.g. 'personal', 'business', 'crypto'
  label           VARCHAR(128) NOT NULL,
  min_kyc_tier    SMALLINT NOT NULL DEFAULT 0,   -- minimum KYC level to open this product
  is_active       BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Seed data
INSERT INTO wallet_product_types (code, label, min_kyc_tier) VALUES
  ('personal',  'Personal Wallet',            1),
  ('business',  'Business Wallet',            2),
  ('crypto',    'Crypto / Digital Asset',     1);
```

#### `wallet_products`

One row per wallet instance owned by a customer. A customer may have multiple rows with the same `product_type_id`.

```sql
CREATE TABLE wallet_products (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id         UUID NOT NULL REFERENCES customers(id),
  product_type_id     UUID NOT NULL REFERENCES wallet_product_types(id),
  display_name        VARCHAR(128),                    -- customer-facing label e.g. "My Travel Wallet"
  status              VARCHAR(32) NOT NULL DEFAULT 'active',
  -- status values: active | frozen | closed
  -- NOTE: 'frozen' is the only permitted state on closure (see section 5)
  frozen_at           TIMESTAMPTZ,
  frozen_reason       TEXT,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- A customer can hold many wallets of the same type — no unique constraint on (customer_id, product_type_id)
CREATE INDEX idx_wallet_products_customer ON wallet_products(customer_id);
```

#### `wallet_currency_configs`

Controls which currencies are available per wallet product instance, and their classification. This is the config layer for first-class vs display-only currencies.

```sql
CREATE TABLE wallet_currency_configs (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  wallet_product_id   UUID NOT NULL REFERENCES wallet_products(id),
  currency_code       VARCHAR(10) NOT NULL,    -- ISO 4217 for fiat (USD, EUR, INR), ticker for crypto (BTC, ETH)
  currency_type       VARCHAR(16) NOT NULL,    -- 'fiat' | 'crypto' | 'stablecoin'
  classification      VARCHAR(16) NOT NULL,    -- 'primary' | 'display_only'
  -- primary: full deposit, withdrawal, and balance support
  -- display_only: balance shown converted to primary; no direct deposits or withdrawals
  is_enabled          BOOLEAN NOT NULL DEFAULT true,
  display_order       SMALLINT NOT NULL DEFAULT 0,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (wallet_product_id, currency_code)
);
```

**Business rule:** Every `wallet_product` must have exactly one `primary` currency at all times. Enforcement via application layer check before insert/update.

#### `sub_wallets`

Sub-wallets are always children of a `wallet_product`. They cannot exist independently.

```sql
CREATE TABLE sub_wallets (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  wallet_product_id   UUID NOT NULL REFERENCES wallet_products(id),
  sub_type            VARCHAR(64) NOT NULL,
  -- personal types:  'savings' | 'spending' | 'rewards'
  -- business types:  'payroll' | 'expense' | 'tax_reserve'
  -- crypto types:    'hot_wallet' | 'staking' | 'vault'
  display_name        VARCHAR(128),
  status              VARCHAR(32) NOT NULL DEFAULT 'active',
  -- status values: active | frozen | closed
  owner_customer_id   UUID NOT NULL REFERENCES customers(id),
  -- owner_customer_id tracks the current owner after transfers
  -- original parent wallet_product_id is preserved for audit
  originating_wallet_product_id UUID REFERENCES wallet_products(id),
  frozen_at           TIMESTAMPTZ,
  frozen_reason       TEXT,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_sub_wallets_wallet_product ON sub_wallets(wallet_product_id);
CREATE INDEX idx_sub_wallets_owner ON sub_wallets(owner_customer_id);
```

#### `balance_ledger`

One row per sub-wallet per currency. This is the source of truth for balances. Never update `amount` directly — always write a transaction and update atomically.

```sql
CREATE TABLE balance_ledger (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sub_wallet_id   UUID NOT NULL REFERENCES sub_wallets(id),
  currency_code   VARCHAR(10) NOT NULL,
  amount          NUMERIC(28, 10) NOT NULL DEFAULT 0,
  -- 28 digits of precision supports crypto (e.g. satoshi-level BTC) and fiat cents
  last_txn_id     UUID,    -- FK to transactions, set on each update
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (sub_wallet_id, currency_code),
  CONSTRAINT positive_balance CHECK (amount >= 0)
);
```

---

## 3. Transaction Model

All money movement — credits, debits, transfers, FX conversions — is recorded as a transaction. This is the immutable audit trail.

```sql
CREATE TABLE transactions (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  txn_type                VARCHAR(64) NOT NULL,
  -- credit | debit | transfer_out | transfer_in | fx_conversion
  -- sub_wallet_transfer_out | sub_wallet_transfer_in  (for sub-wallet transfers)

  sub_wallet_id           UUID NOT NULL REFERENCES sub_wallets(id),
  currency_code           VARCHAR(10) NOT NULL,
  amount                  NUMERIC(28, 10) NOT NULL,
  balance_before          NUMERIC(28, 10) NOT NULL,
  balance_after           NUMERIC(28, 10) NOT NULL,

  -- For transfers between sub-wallets or customers
  counterpart_sub_wallet_id   UUID REFERENCES sub_wallets(id),
  counterpart_customer_id     UUID REFERENCES customers(id),

  -- For FX conversions
  fx_from_currency        VARCHAR(10),
  fx_to_currency          VARCHAR(10),
  fx_rate                 NUMERIC(18, 8),
  fx_fee                  NUMERIC(18, 8),

  -- Grouping: all legs of one transfer share a reference_id
  reference_id            UUID NOT NULL,
  idempotency_key         VARCHAR(256) UNIQUE,   -- caller supplies this to prevent duplicates
  status                  VARCHAR(32) NOT NULL DEFAULT 'completed',
  -- completed | pending | failed | reversed
  metadata                JSONB,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_txn_sub_wallet ON transactions(sub_wallet_id, created_at DESC);
CREATE INDEX idx_txn_reference ON transactions(reference_id);
CREATE INDEX idx_txn_idempotency ON transactions(idempotency_key);
```

---

## 4. Sub-wallet Transfer Rules

A sub-wallet can be transferred to a different parent wallet product (within the same customer) or to a completely different customer. Both are modelled as transactions, not as data mutations.

### 4.1 Transfer to another wallet product (same customer)

1. Validate that the target `wallet_product` belongs to the same `customer_id`.
2. Validate that the target wallet product's `currency_config` supports all currencies in the sub-wallet's `balance_ledger`.
3. Update `sub_wallets.wallet_product_id` to the new parent.
4. Write a `sub_wallet_transfer_out` transaction on the source and `sub_wallet_transfer_in` on the destination, sharing a `reference_id`.
5. `originating_wallet_product_id` is never changed — it preserves the original home for audit.

### 4.2 Transfer to another customer

1. Validate target customer's `kyc_tier` meets the minimum required for the sub-wallet's `sub_type`.
2. Validate target customer has an active `wallet_product` of a compatible type (or create one if your product rules allow it).
3. Update `sub_wallets.owner_customer_id` to the new customer.
4. Update `sub_wallets.wallet_product_id` to the target wallet product.
5. Write transfer transactions on both source and destination sub-wallets with a shared `reference_id`.
6. The original `customer_id` trail is available via `transactions` — do not rely on `sub_wallets` for historical ownership.

### 4.3 Validation checks (both transfer types)

| Check | Rule |
|---|---|
| Source sub-wallet status | Must be `active` |
| Source wallet product status | Must be `active` |
| Target wallet product status | Must be `active` |
| Target customer status | Must be `active` |
| Currency compatibility | All currency codes in `balance_ledger` must exist in target's `wallet_currency_configs` as `primary` |
| Frozen balance | Transfer blocked if any currency balance is 0 and sub-wallet has a lock |
| Idempotency | Caller must supply `idempotency_key`; duplicate requests return the original transaction result |

---

## 5. Currency Configuration

### 5.1 Classification behaviour

| Classification | Deposits | Withdrawals | Balance display | FX conversion |
|---|---|---|---|---|
| `primary` | Yes | Yes | Native amount | Source or target |
| `display_only` | No | No | Converted to primary | Auto-converted on receipt |

### 5.2 Changing classification at runtime

A currency's classification can be updated in `wallet_currency_configs`. When a `primary` currency is changed to `display_only`:

1. Block the update if it would leave the wallet product with zero `primary` currencies.
2. Trigger a balance sweep job: convert any outstanding balance to the remaining primary currency using the FX engine.
3. Record the conversion as an `fx_conversion` transaction.

### 5.3 Adding a new currency to an existing wallet product

```
POST /wallet-products/{id}/currency-configs
{
  "currency_code": "SGD",
  "currency_type": "fiat",
  "classification": "primary",
  "is_enabled": true
}
```

This creates a row in `wallet_currency_configs` and a `balance_ledger` row with `amount = 0` for each sub-wallet under this product.

---

## 6. Wallet Closure & Freeze

**Policy: On closure, wallet status is set to `frozen`. Balances are never automatically cleared.**

### 6.1 Freeze cascade

When a `wallet_product` is frozen:

1. Set `wallet_products.status = 'frozen'` and record `frozen_at`, `frozen_reason`.
2. Cascade: set `sub_wallets.status = 'frozen'` for all child sub-wallets of this product.
3. No new transactions are permitted against frozen sub-wallets.
4. Read access to balances and transaction history remains open.

```sql
-- Freeze a wallet product and cascade to sub-wallets
UPDATE wallet_products
SET status = 'frozen', frozen_at = now(), frozen_reason = $reason, updated_at = now()
WHERE id = $wallet_product_id;

UPDATE sub_wallets
SET status = 'frozen', frozen_at = now(), frozen_reason = 'parent_wallet_frozen', updated_at = now()
WHERE wallet_product_id = $wallet_product_id
  AND status = 'active';
```

### 6.2 Unfreeze

Unfreezing a wallet product does not automatically unfreeze sub-wallets that were individually frozen before the parent freeze. Only sub-wallets with `frozen_reason = 'parent_wallet_frozen'` are unfrozen on parent unfreeze.

### 6.3 Individually frozen sub-wallets

Sub-wallets can be frozen independently (e.g. fraud hold) without freezing the parent wallet product.

---

## 7. Key Business Rules Summary

1. **Multiple same-type wallets** — no uniqueness constraint on `(customer_id, product_type_id)`. Each wallet product gets its own UUID.

2. **Sub-wallet is always a child** — `sub_wallets.wallet_product_id` is non-nullable and always points to a valid `wallet_products` row.

3. **Balance is ledger-first** — never update `balance_ledger.amount` directly. All changes go through the transaction writer which updates atomically using `SELECT ... FOR UPDATE`.

4. **Idempotency is mandatory** — every transaction creation endpoint requires an `idempotency_key`. Duplicate calls with the same key return the existing result without re-processing.

5. **Currency config is per wallet product** — a customer with two personal wallets can configure different currencies on each. Currency configs are not inherited from the product type template.

6. **FX always explicit** — cross-currency operations never happen silently. Every conversion writes an `fx_conversion` transaction with the rate, fee, and both currency amounts recorded.

7. **Freeze, never auto-clear** — when a wallet or sub-wallet is frozen, balances stay. There is no automatic settlement, sweep, or expiry at this stage.

---

## 8. API Endpoints (recommended surface)

| Method | Path | Description |
|---|---|---|
| POST | `/customers/{id}/wallet-products` | Open a new wallet product |
| GET | `/customers/{id}/wallet-products` | List all wallet products for a customer |
| PATCH | `/wallet-products/{id}` | Update display name or status |
| POST | `/wallet-products/{id}/currency-configs` | Add a currency to a wallet product |
| PATCH | `/wallet-products/{id}/currency-configs/{currency}` | Change classification or toggle enabled |
| POST | `/wallet-products/{id}/sub-wallets` | Create a sub-wallet under a product |
| GET | `/wallet-products/{id}/sub-wallets` | List sub-wallets under a product |
| POST | `/sub-wallets/{id}/transfer` | Transfer a sub-wallet to another product or customer |
| GET | `/sub-wallets/{id}/balances` | Get all currency balances for a sub-wallet |
| POST | `/sub-wallets/{id}/transactions` | Credit or debit a sub-wallet |
| GET | `/sub-wallets/{id}/transactions` | Transaction history for a sub-wallet |
| POST | `/sub-wallets/{id}/freeze` | Freeze a specific sub-wallet |
| POST | `/wallet-products/{id}/freeze` | Freeze a wallet product and cascade |

---

## 9. Open Items for Next Sprint

- [ ] Define maximum number of sub-wallets per wallet product (business rule, not data model constraint)
- [ ] Define which `sub_type` values are permitted per `product_type` (enforce in application layer or via a config table)
- [ ] FX engine integration: rate source, spread policy, refresh frequency
- [ ] KYC tier enforcement on sub-wallet transfer to another customer
- [ ] Notification events: freeze, transfer, large transaction thresholds
- [ ] Reconciliation job design for `balance_ledger` vs `transactions` sum audit

---

*Document prepared by Product. Queries: raise in the #wallet-platform Slack channel.*