Table Catalog

The exhaustive per-table reference (~95 live tables) grouped by domain, the core ER diagram, the ID/soft-delete/tenancy conventions, the six native enums vs VARCHAR+CHECK, the commented-out FKs, and the dead/renamed tables.

The exhaustive table catalog — complements the higher-level Database page. ~130 CREATE TABLE statements exist across migration history; ~95 tables are live today (dropped/dead ones in § Dead tables). Legend: SD = has deleted_at; client_id = tenant-scoped.

Core ER (the spine)

Not shown (grouped in text): all admin_*/client_* auth tables, the prefetch_* chain, vendor_catalog_*, job_execution_*, and the shopify_*/g2a_* integration islands.

Identity / auth / tenant

Table	Purpose	Key FKs	SD
`clients`	root tenant	currency→currencies	Y
`client_users`	dashboard users	client_id, invited_by	Y
`client_credentials`	API key/secret	client_id (CASCADE)	—
`auth_tokens`	access/refresh tokens	client_id, user_id→client_users	—
`admin_users`	back-office users (no client_id, global)	—	—
`admin_sessions`	admin sessions	admin_user_id (CASCADE)	—
`admin_backup_codes` / `client_backup_codes`	2FA recovery	admin/client_user_id	—
`client_password_reset_tokens`	reset tokens	user_id (CASCADE)	Y
`passkeys` / `webauthn_challenges`	WebAuthn creds/challenges	user_id, client_id	—
`ip_whitelist_entries`	per-client CIDRs	client_id (CASCADE)	Y
`login_attempts`	auth throttling audit	—	Y
`sub_clients`	client hierarchy	client_id, parent_id	Y
`users`	legacy internal users (superseded)	client_id	Y

Reference & catalog

Table	Purpose	SD
`countries` / `currencies` / `timezones` / `forex_values`	reference data (unique `currencies.currency` is the natural key clients reference)	Y
`categories` / `sub_categories`	product grouping	Y
`products`	master catalog (category/sub_category are VARCHAR, denormalized)	Y
`vendors`	suppliers (unique name; code-unique later dropped)	Y
`vendor_products`	vendor↔product mapping + pricing (unique `(product_id, vendor_id)` dropped)	Y
`vendor_attributes`	KV vendor config (plaintext)	—
`vendor_wallets`	vendor-side balance	Y

See Catalog Schema for the field-by-field breakdown. Top-up/eSIM catalog tables: topup_products, topup_variants (+ _variant_fields/_regions), the two input-field table sets (topup_variant_input_fields/_options and product-level topup_product_input_fields/_options), client_topup_products; esim_products, esim_variants (+ _variant_fields/_regions), client_esim_variants.

Orders / fulfilment

Table	Purpose	Key FKs / natural key	SD
`orders`	order header	client_id, product_id; `reference_code` UNIQUE; `transaction_id`/`wallet_id` (no FK)	Y
`order_items` (was `order_vendors`)	per-vendor line + voucher payload	vendor_id, vendor_product_id, order_id; `vendor_reference_code`; encrypted code/pin	Y
`order_item_responses`	raw vendor responses	order_item_id	Y
`voucher_links` (was `gv_links`)	claimable link	order_id (no enforced FK); token+hash, expires_at	Y
`inventories`	pre-loaded voucher stock	product_id, client_id, import_id; `order_item_id` FK commented out	Y
`inwards`	vendor stock-in batch log	vendor/product	—
`import_jobs` / `import_stage_logs`	bulk import job + progress	created_by→admin_users	—
`carts` / `cart_items`	legacy B2C cart (cart_items FKs all commented out)	—	Y

Recharges / eSIM

Table	Purpose	Natural key	SD
`recharges`	mobile/top-up txn	`reference_number` UNIQUE; `vendor_id` (renamed from `vendor`), topup_variant_id	Y
`recharge_input_values`	captured input fields	recharge_id (CASCADE)	—
`recharge_logs` / `recharge_responses`	state log / raw responses	recharge_id	Y
`esim_orders`	eSIM order	`reference_number` UNIQUE; esim_variant_id; iccid, activation_code	Y
`esim_order_responses`	raw vendor responses	esim_order_id	Y

Money

Table	Purpose	Notes	SD
`wallets`	balance per currency	`UNIQUE(client_id, currency_id)`, `amount NUMERIC(24,2)`; mutated via the `update_wallet_balance` stored proc	Y
`transactions`	debit/credit	ENUM `transaction_status_enum`/`transaction_type_enum`	Y
`ledgers`	ledger row	`transaction_id → transactions` FK commented out, later made nullable	Y

See Wallet & Ledger — it's single-entry despite the "ledger" name.

Payouts

payout_providers (+ _attributes with an is_secret col, _currencies, _countries), payout_beneficiaries (KSUID public_id), beneficiary_payout_providers, payouts (KSUID public_id, UNIQUE(client_id, reference_id), ENUM payout_status, FKs incl. refund_transaction_id→transactions and scheduled_payout_id), payout_events, scheduled_payouts + scheduled_payout_executions, client_payout_configs + _audit, client_payout_webhooks + payout_webhook_deliveries. See Payouts.

Webhooks / vendor sync / integrations

Webhooks: web_hook_urls, web_hooks (subscription config), webhook_deliveries (was order_webhook_deliveries, unified 20260325000002 to also carry recharge deliveries via recharge_id). See Webhook Payloads.
Vendor sync: vendor_catalog_snapshots, vendor_catalog_products (self-referential matched), product_match_reviews (embedding schema dropped, recreated without vectors), vendor_discount_audits, vendor_webhook_logs (inbound).
Shopify: shopify_customers, shopify_orders, shopify_order_items, shopify_product_mappings (client_id retrofitted onto the first three). See Shopify.
G2A: g2a_credentials, g2a_product_mappings, g2a_reservations (UUID VARCHAR(36) PK), g2a_reservation_items, g2a_orders, g2a_order_items (inventory_uuid UNIQUE), g2a_notifications. See G2A.

Jobs / prefetch / exports / misc

Prefetch: prefetch_jobs → prefetch_batches → prefetch_batch_items / prefetch_inventory_items / prefetch_events / prefetch_api_logs.
Job execution: job_execution_records (UUID execution_id), _events, _metrics, _daily_summaries. Plus exports (UUID export_id).
Misc: files, tenant_connections (multi-DB registry). ⚠️ website_leads has no migration/table in this repo despite the admin handler — treat as external or not-yet-migrated.

Conventions

ID schemes — three coexist:

Scheme	Where
`BIGSERIAL` integer `id`	default PK on nearly every table
KSUID `public_id VARCHAR(30)`	`payouts`, `payout_beneficiaries`, `client_payout_webhooks`, `scheduled_payouts` — the externally-exposed payout ids (widened from 20 to fit `prefix_`+22)
UUID	`exports.export_id`, `job_execution_records.execution_id`, `g2a_reservations.id`, `g2a_order_items.inventory_uuid`
`reference_code`/`reference_number` (VARCHAR(255))	idempotency natural keys: `orders.reference_code`, `recharges`/`esim_orders.reference_number`, `payouts.reference_id`, `order_items.vendor_reference_code`

Soft-delete: deleted_at IS NULL filter on most business tables. Absent on append-only/log/link tables (admin_users, sessions, backup_codes, passkeys, most prefetch/job_execution, payout_events, webhook_deliveries, import_jobs, inwards, all g2a_*, shopify mirrors). Some unique indexes are partial (UNIQUE(col) WHERE deleted_at IS NULL).

Audit: created_at DEFAULT CURRENT_TIMESTAMP, updated_at (app-set, no trigger), often created_by/updated_by→admin_users/client_users. is_active is a separate flag, distinct from soft-delete.

Tenant scoping via client_id (the multi-tenancy anchor) on all money/order/payout/integration tables; retrofitted onto shopify_. Global (un-scoped): reference data, catalog masters, all admin_, vendor_catalog_, job_execution_. A physical multi-DB layer exists via tenant_connections + tenant_manager.go.

Only 6 native Postgres enums — everything else is VARCHAR + CHECK

Native CREATE TYPE enums: token_type, transaction_status_enum, transaction_type_enum, payout_status, client_user_role, client_user_status. Every other status (orders.status, order_items.status/sub_status, recharges.status, esim_orders.status/activation_status, clients.status, wallets.type) is VARCHAR + CHECK IN (…) — so adding a status requires an ALTER-CHECK migration, not ALTER TYPE … ADD VALUE. Cross-reference the State Machines dead-value catalog.

FKs that exist logically but are NOT enforced

Deliberately commented-out in migrations — no referential integrity:

ledgers.transaction_id → transactions(id) (later made nullable)
inventories.order_item_id → order_items(id) — the constraint block is commented out (relevant to the inventory leak: a deleted order_item leaves a dangling ref)
orders.transaction_id/wallet_id, recharges.transaction_id, esim_orders.transaction_id — plain BIGINT
all cart_items.*, vendor_attributes.created_by

Dead & renamed tables

Dropped (via up-migration) — gone: the entire legacy B2C/customer stack (customers, awards, campaigns, bill_payments, payments, customer_*, product_update_requests, prefetch_voucher_cache), plus rewards/customer_rewards, otps/gv_otps, customer_recharges, admin_notifications, admin_activity_logs, application_configs, client_feature_flags, email_templates, incoming_api_logs, job_configurations, bamboo_forex_rates.

Deprecated / superseded (table or model lingers): direct_topup_webhooks (deprecated; webhooks persist to vendor_webhook_logs), client_topup_variants (replaced by client_topup_products), shopify_order_item_vouchers (dead — Shopify).

Renamed (watch for old names in old code): order_vendors → order_items (FK constraints still read fk_order_vendors_*), gv_links → voucher_links, order_webhook_deliveries → webhook_deliveries, and the is_gv* → is_link* / recharges.vendor → vendor_id column renames.

Stale Go models with no live table: direct_topup_webhook.go, shopify_order_item_voucher.go, client_topup_variant.go, product_update_request.go, user.go.

Corrective backfills worth knowing during data work: fix_products_foreign_key_data_types, fix_currency_id_constraint, fix_import_jobs_created_by, fix_payout_public_id_sizes, fix_order_items_constraint_names, add_client_id_to_shopify_tables. Several columns were dropped then re-added (products.internal_reference_id, recharges/esim_orders.client_reference) — schema archaeology to expect.

Key files

Migrations: database/migration/*.sql (goose -- +goose Up/Down)
Models: database/models/*.go
Multi-DB: database/database.go, tenant_manager.go; stored proc …update_wallet_balance…sql

Table Catalog

On this page