Data Model

Database table schema for the payment module. Phase 0 artifact.

Phase 0SpecPostgreSQL

5 tables of the payment module. All tables are created in Phase 1 (even if some columns/features are used later). PostgreSQL. Monetary amounts -- always bigint in the smallest unit of the respective currency (cents for USD/EUR, yen for JPY, etc.). The currency is stored alongside in the currency column (ISO 4217). Multi-currency is supported: balances and transactions can be in different currencies.

brands and users tables are not described here -- they belong to the auth module. The tables below only reference them via FK.

Tables

transactions

25 columnsPK3 FK5 IDX

Central table. Every deposit or withdrawal attempt creates one row. Never deleted -- audit trail.

Column	Type	Nullable	Description
PK`id`	`uuid`	No	Primary key, generated by backend (UUID v4)
FK`brand_id`	`uuid`	No	References brands.id -- which tenant owns this transaction
FK`user_id`	`uuid`	No	References users.id -- which player initiated it
FK`psp_config_id`	`uuid`	Yes	References psp_configs.id -- which PSP handled it. NULL until PSP is selected by orchestrator.
`direction`	`enum('deposit','withdrawal')`	No	Direction of funds flow
IDX`status`	`varchar(32)`	No	Current UnifiedStatus: INITIATED \| PROCESSING \| PENDING_CONFIRMATION \| PENDING_PARTIAL \| COMPLETED \| FAILED \| TIMED_OUT \| CANCELLED
`method`	`varchar(32)`	No	Payment method slug (e.g. "btc", "usdt_trc20")
`amount_requested`	`bigint`	No	Amount requested by player, in the smallest unit of currency (e.g. cents for USD/EUR)
`amount_credited`	`bigint`	Yes	Final amount credited to player balance, in the smallest unit of currency. NULL until COMPLETED.
`amount_crypto`	`varchar(64)`	Yes	Raw crypto amount as returned by PSP (string to preserve precision)
`currency`	`varchar(8)`	No	ISO 4217 currency code (e.g. "USD")
`fee_total`	`bigint`	Yes	Total fees in the smallest unit of currency (feeService + feeNetwork from PSP). NULL until settled.
`exchange_rate`	`numeric(18,8)`	Yes	USD/crypto rate used at time of conversion. Store for audit.
IDX`psp_payment_id`	`varchar(128)`	Yes	PSP-internal transaction ID (e.g. PassimPay transactionId). Used for idempotency and reconciliation.
IDX`psp_order_id`	`varchar(128)`	Yes	Merchant order ID sent to PSP (derived from id, max 64 chars, PSP-safe charset)
`blockchain_tx_id`	`varchar(128)`	Yes	On-chain transaction hash. Set on COMPLETED.
`wallet_address`	`varchar(256)`	Yes	Crypto address shown to player (deposit) or sent to (withdrawal)
`destination_tag`	`varchar(64)`	Yes	Destination tag / memo for XRP, TON networks. NULL for other networks.
IDX`session_id`	`varchar(128)`	Yes	Player session ID from JWT at time of creation. Used for fraud correlation.
`geo`	`varchar(2)`	Yes	ISO 3166-1 alpha-2 country code at time of transaction
`metadata`	`jsonb`	Yes	Arbitrary key-value pairs passed from frontend. Not queried -- store only.
`expires_at`	`timestamptz`	Yes	Invoice / address expiry time. Used by TTL job to mark TIMED_OUT.
IDX`created_at`	`timestamptz`	No	Row creation timestamp (UTC)
`updated_at`	`timestamptz`	No	Last status change timestamp (UTC). Updated on every status transition.
`completed_at`	`timestamptz`	Yes	Set when status becomes COMPLETED. Used for settlement reporting.

Indexes

UNIQUE (psp_payment_id) -- idempotency guard

UNIQUE (psp_order_id) -- no duplicate PSP orders

INDEX (user_id, created_at DESC) -- player history

INDEX (brand_id, status, created_at) -- ops dashboard

INDEX (status, expires_at) -- TTL job query

INDEX (psp_config_id, status) -- reconciliation job

All monetary amounts stored as bigint in the smallest unit of currency (cents for USD/EUR, yen for JPY, etc.). Never use float for money. Always pair amount with its currency column.

status column uses application-level enum (varchar), not DB enum -- easier to add new values without migrations.

Rows are never deleted. Hard deletes are prohibited. Use status=CANCELLED for cancellations.

transaction_events

8 columnsPK1 FK2 IDX

Immutable event log. Every status transition and webhook is appended here. Enables full audit trail and debugging.

Column	Type	Nullable	Description
PK`id`	`uuid`	No	Primary key
FK`transaction_id`	`uuid`	No	References transactions.id
IDX`event_type`	`varchar(64)`	No	e.g. status_changed \| webhook_received \| reconciliation_check \| ttl_expired
`status_from`	`varchar(32)`	Yes	Previous status (NULL for the first event)
`status_to`	`varchar(32)`	Yes	New status after this event
`payload`	`jsonb`	Yes	Raw webhook body or internal event data
`psp_raw`	`jsonb`	Yes	Raw PSP response (for debugging). Strip sensitive fields before storing.
IDX`created_at`	`timestamptz`	No	Event timestamp (UTC)

Append-only. Never update or delete rows.

Every webhook received must be logged here before processing, even duplicates.

psp_configs

14 columnsPK1 FK3 IDX

One row per PSP per brand per geo. The orchestrator reads this table to select which PSP to use for a given request. Managed via Admin Panel (Phase 4) -- no code deploys needed to add or disable a PSP.

Column	Type	Nullable	Description
PK`id`	`uuid`	No	Primary key
FK`brand_id`	`uuid`	No	References brands.id
IDX`psp_name`	`varchar(64)`	No	Adapter identifier (e.g. "passimpay"). Must match the adapter registry key.
IDX`is_active`	`boolean`	No	Toggle to enable/disable PSP without deleting config. Checked by orchestrator.
`priority`	`smallint`	No	Cascade priority order. Lower number = tried first. Must be unique per brand+geo+direction.
IDX`geo`	`varchar(2)`	Yes	ISO 3166-1 alpha-2 country filter. NULL = all geos.
`direction`	`enum('deposit','withdrawal','both')`	No	Which direction this config applies to
`methods`	`varchar[]`	No	Array of supported method slugs for this config (e.g. ["btc","usdt_trc20"])
`credentials`	`jsonb`	No	Encrypted PSP credentials (platform_id, api_secret etc.). Encrypted at rest -- never returned in API responses.
`min_amount`	`bigint`	Yes	Override minimum amount in the smallest unit of the config currency. NULL = use PSP default.
`max_amount`	`bigint`	Yes	Override maximum amount in the smallest unit of the config currency. NULL = use PSP default.
`health_score`	`smallint`	No	PSP health 0--100. Updated by reconciliation job based on success rate. Used for smart routing in Phase 6.
`created_at`	`timestamptz`	No	Row creation timestamp
`updated_at`	`timestamptz`	No	Last update timestamp

Indexes

INDEX (brand_id, is_active, geo, direction) -- orchestrator routing query

UNIQUE (brand_id, psp_name, geo, direction) -- no duplicate configs

credentials column must be encrypted at rest (e.g. pgcrypto or application-level AES-256). Never log credentials.

health_score starts at 100 and decays on failures. Phase 6 feature -- populated but not used in routing until Phase 6.

routing_rules

11 columnsPK2 FK1 IDX

Explicit overrides on top of psp_configs priority. Allows ops to pin specific PSPs for specific conditions without changing priority order. Evaluated by orchestrator after base PSP selection.

Column	Type	Nullable	Description
PK`id`	`uuid`	No	Primary key
FK`brand_id`	`uuid`	No	References brands.id. NULL = applies to all brands.
FK`psp_config_id`	`uuid`	No	References psp_configs.id -- which PSP this rule forces
`condition_geo`	`varchar(2)`	Yes	ISO 3166-1 alpha-2. NULL = match any geo.
`condition_method`	`varchar(32)`	Yes	Method slug filter. NULL = match any method.
`condition_currency`	`varchar(8)`	Yes	Currency code filter. NULL = match any currency.
`condition_min_amount`	`bigint`	Yes	Rule applies only when transaction amount >= this value (smallest unit of transaction currency)
`condition_max_amount`	`bigint`	Yes	Rule applies only when transaction amount <= this value (smallest unit of transaction currency)
IDX`is_active`	`boolean`	No	Toggle rule on/off without deleting
`priority`	`smallint`	No	Rule evaluation order. Lower = evaluated first. First matching rule wins.
`created_at`	`timestamptz`	No	Row creation timestamp

Phase 2 feature -- table is created in Phase 1 but the orchestrator only reads it from Phase 2 onwards.

All NULL conditions are treated as wildcards (match anything). A rule with all NULLs matches every transaction.

payment_methods

12 columnsPK1 FK1 IDX

Cache of available payment methods per PSP config. Populated by the getSupportedMethods() adapter call. Refreshed every 5 minutes. Frontend reads this to build the method selector.

Column	Type	Nullable	Description
PK`id`	`uuid`	No	Primary key
FK`psp_config_id`	`uuid`	No	References psp_configs.id
IDX`slug`	`varchar(32)`	No	Method identifier (e.g. "btc", "usdt_trc20"). Used in transaction.method column.
`name`	`varchar(64)`	No	Display name for UI (e.g. "Bitcoin", "USDT TRC20")
`network`	`varchar(64)`	Yes	Blockchain network name (e.g. "TRC20", "ERC20")
`min_amount`	`bigint`	No	Minimum amount in the smallest unit of the method currency
`max_amount`	`bigint`	No	Maximum amount in the smallest unit of the method currency
`fee_pct`	`numeric(5,2)`	Yes	Estimated fee percentage 0.00--100.00
`logo_url`	`varchar(512)`	Yes	URL to method logo image
`requires_tag`	`boolean`	No	True for XRP, TON and other networks requiring destination tag / memo
`is_active`	`boolean`	No	Soft toggle. False = hide from player UI without deleting cache row.
`cached_at`	`timestamptz`	No	When this row was last refreshed from PSP

Indexes

UNIQUE (psp_config_id, slug) -- one row per method per PSP config

INDEX (psp_config_id, is_active) -- frontend method list query

Table Relationships

Table	Column	References	Description
`transactions`	`brand_id`	`brands`	Each transaction belongs to a brand (tenant)
`transactions`	`user_id`	`users`	Each transaction belongs to a player
`transactions`	`psp_config_id`	`psp_configs`	Which PSP handled this transaction (set by orchestrator)
`transaction_events`	`transaction_id`	`transactions`	Event log entries belong to a transaction
`psp_configs`	`brand_id`	`brands`	PSP configs are scoped to a brand
`routing_rules`	`brand_id`	`brands`	Routing rules are scoped to a brand (nullable = all brands)
`routing_rules`	`psp_config_id`	`psp_configs`	Rule forces a specific PSP config
`payment_methods`	`psp_config_id`	`psp_configs`	Methods are cached per PSP config

DEPO44 | DATA MODEL v1 | PHASE 0 ARCHITECTURE