Wallet Data Model

Database Schema

Core Tables

wallet_account

Primary wallet entity linking member to prepaid balance.

Column	Type	Description
id	UUID	Primary key
member_id	UUID	FK to member
tenant_id	UUID	Multi-tenant isolation
currency	VARCHAR(3)	Currency code (e.g., "ZAR")
balance	INTEGER	Current balance in cents
status	ENUM	PENDING, ACTIVE, SUSPENDED, CLOSED
created_at	TIMESTAMP	Creation time
updated_at	TIMESTAMP	Last modification

Indexes:

• idx_wallet_member on (tenant_id, member_id) - unique
• idx_wallet_status on (tenant_id, status)

wallet_transaction

Immutable ledger of all balance-affecting operations.

Column	Type	Description
id	UUID	Primary key
wallet_id	UUID	FK to wallet_account
type	ENUM	CREDIT, DEBIT, ADJUSTMENT
amount	INTEGER	Amount in cents (positive=credit)
balance_after	INTEGER	Running balance after this txn
reference	VARCHAR	External reference (payment ID, etc)
description	VARCHAR	Human-readable description
merchant_code	VARCHAR	POS/merchant identifier
source_type	VARCHAR	Origin (TOP_UP, PURCHASE, REFUND, etc)
posted_at	TIMESTAMP	When transaction was recorded

Indexes:

• idx_txn_wallet_posted on (wallet_id, posted_at DESC)
• idx_txn_reference on (reference) - unique when not null
• idx_txn_merchant on (tenant_id, merchant_code, posted_at)

auto_topup_config

Configuration for automatic balance replenishment.

Column	Type	Description
id	UUID	Primary key
wallet_id	UUID	FK to wallet_account (unique)
enabled	BOOLEAN	Whether auto top-up is active
type	ENUM	THRESHOLD, SCHEDULED
threshold_cents	INTEGER	Trigger when balance below this
amount_cents	INTEGER	Amount to add each time
schedule_cron	VARCHAR	Cron expression for scheduled type
mandate_ref	VARCHAR	Stored payment method reference
last_run_at	TIMESTAMP	Last successful execution
next_run_at	TIMESTAMP	Next scheduled execution
failure_count	INTEGER	Consecutive failures

wallet_pass

Digital card tracking for Apple/Google Wallet.

Column	Type	Description
id	UUID	Primary key
wallet_id	UUID	FK to wallet_account
provider	ENUM	APPLE, GOOGLE
pass_type_id	VARCHAR	Provider-specific type ID
serial_number	VARCHAR	Unique pass identifier
push_token	VARCHAR	Push notification token
device_id	VARCHAR	Device identifier
last_synced_at	TIMESTAMP	Last balance sync
installed_at	TIMESTAMP	When added to phone
uninstalled_at	TIMESTAMP	When removed (soft delete)

Indexes:

• idx_pass_serial on (provider, pass_type_id, serial_number) - unique

Event Tables

wallet_outbox

Transactional outbox for reliable event publishing.

Column	Type	Description
id	UUID	Primary key
tenant_id	UUID	Multi-tenant isolation
event_type	VARCHAR	Event category
event_key	VARCHAR	Specific event name
payload	JSONB	Event data
status	ENUM	PENDING, CLAIMED, COMPLETED, FAILED
claimed_by	VARCHAR	Worker instance ID
claimed_at	TIMESTAMP	When claimed for processing
completed_at	TIMESTAMP	When processing finished
error	TEXT	Error message if failed
retry_count	INTEGER	Number of retries
created_at	TIMESTAMP	Creation time

Indexes:

• idx_outbox_pending on (tenant_id, status, created_at) where status = 'PENDING'
• idx_outbox_claimed on (claimed_by, claimed_at) where status = 'CLAIMED'

Preference Tables

member_notification_preference

Member preferences for wallet notifications.

Column	Type	Description
id	UUID	Primary key
member_id	UUID	FK to member
tenant_id	UUID	Multi-tenant isolation
wallet_low_balance_enabled	BOOLEAN	Enable low balance alerts
wallet_credit_enabled	BOOLEAN	Enable top-up notifications
wallet_debit_enabled	BOOLEAN	Enable purchase notifications
wallet_auto_topup_enabled	BOOLEAN	Enable auto top-up alerts
email_enabled	BOOLEAN	Email channel enabled
whatsapp_enabled	BOOLEAN	WhatsApp channel enabled
sms_enabled	BOOLEAN	SMS channel enabled
push_enabled	BOOLEAN	Push channel enabled
low_balance_threshold	INTEGER	Alert threshold in cents

Data Relationships

Constraints & Rules

Balance Integrity

• Balance is always recalculated from transaction history on reconciliation
• Transactions are immutable (corrections via new adjustment transactions)
• Balance cannot go negative (enforced at application layer)

Transaction Idempotency

• External references must be unique
• Duplicate transaction attempts are rejected

Multi-Tenancy

• All queries include tenant_id filter
• Cross-tenant data access is prevented at application layer