Data Model

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

Phase 0

Spec

PostgreSQL

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 USD cents.

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 columns

3 FK

5 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 USD cents
`amount_credited`	`bigint`	Yes	Final amount credited to player balance, in USD cents. 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 USD cents (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 (integer cents). Never use float for money.

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 columns

1 FK

2 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 columns

1 FK

3 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 USD cents. NULL = use PSP default.
`max_amount`	`bigint`	Yes	Override maximum amount in USD cents. 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 columns

2 FK

1 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 (USD cents)
`condition_max_amount`	`bigint`	Yes	Rule applies only when transaction amount <= this value (USD cents)
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 columns

1 FK

1 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 USD cents
`max_amount`	`bigint`	No	Maximum amount in USD cents
`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