Exchange & Vault

Encrypted API key storage, exchange connections, and secure credential management.

15 tables in this group.

api_keys

Encrypted exchange API keys with user-level RLS. Keys encrypted with per-user encryption keys (zero-friction UX - no master password required).

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`exchange`	`text`	No
`encrypted_api_key`	`text`	No
`encrypted_secret`	`text`	No
`encrypted_passphrase`	`text`	Yes
`key_iv`	`text`	No
`secret_iv`	`text`	No
`passphrase_iv`	`text`	Yes
`salt`	`text`	Yes
`is_active`	`bool`	Yes	true
`last_used`	`timestamptz`	Yes
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()
`environment`	`text`	Yes	'production'::text
`name`	`text`	Yes
`permissions`	`_text`	Yes	ARRAY['read'::text]
`rotation_generation`	`int4`	Yes	1
`is_rotation_active`	`bool`	Yes	true
`rotated_from_key_id`	`uuid`	Yes
`rotation_grace_period_ends_at`	`timestamptz`	Yes
`auto_rotation_enabled`	`bool`	Yes	true

RLS Policies:

Users can delete own API keys — DELETE for {public}
Users can insert own API keys — INSERT for {public}
Users can update own API keys — UPDATE for {public}
Users can view own API keys — SELECT for {public}

Indexes:

api_keys_pkey
idx_api_keys_auto_rotation
idx_api_keys_exchange
idx_api_keys_grace_period
idx_api_keys_rotated_from_key_id
idx_api_keys_user_id

encrypted_api_keys

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`user_id`	`uuid`	No
`exchange`	`text`	No
`key_name`	`text`	No
`encrypted_api_key`	`text`	No
`encrypted_secret`	`text`	No
`encrypted_passphrase`	`text`	Yes
`permissions`	`jsonb`	Yes	'{}'::jsonb
`is_active`	`bool`	Yes	true
`is_testnet`	`bool`	Yes	false
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()
`last_used_at`	`timestamptz`	Yes

RLS Policies:

Users can manage their own API keys — ALL for {public}

Indexes:

encrypted_api_keys_pkey
encrypted_api_keys_user_id_exchange_key_name_key

encrypted_exchange_credentials

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`user_id`	`uuid`	No
`exchange_name`	`varchar(100)`	No
`exchange_account_label`	`varchar(255)`	Yes	'Primary Account'::character varying
`is_testnet`	`bool`	Yes	false
`encrypted_api_key`	`text`	No
`encrypted_api_secret`	`text`	No
`encrypted_passphrase`	`text`	Yes
`iv`	`text`	No
`encryption_algorithm`	`varchar(50)`	No	'AES-256-GCM'::character varying
`auth_tag`	`text`	Yes
`permissions`	`jsonb`	No	'{"read": true, "trade": false, "withdraw": false}'::jsonb
`rate_limits`	`jsonb`	Yes	'{}'::jsonb
`active`	`bool`	No	true
`key_version`	`int4`	No	1
`rotated_from_id`	`uuid`	Yes
`rotation_scheduled_at`	`timestamptz`	Yes
`expires_at`	`timestamptz`	Yes
`last_used_at`	`timestamptz`	Yes
`usage_count`	`int4`	Yes	0
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

rls_exchange_credentials_delete — DELETE for {public}
rls_exchange_credentials_insert — INSERT for {public}
rls_exchange_credentials_select — SELECT for {public}
rls_exchange_credentials_update — UPDATE for {public}

Indexes:

encrypted_exchange_credential_user_id_exchange_name_exchang_key
encrypted_exchange_credentials_pkey
idx_encrypted_exchange_credentials_rotated_from_id

Per-user encryption keys for API credentials. Each user gets a unique key derived from user_id + PLATFORM_MASTER_KEY. Keys are encrypted with platform master key. Protects against single-user breaches.

Column	Type	Nullable	Default
`user_id`	`uuid`	No
`encrypted_key`	`text`	No
`key_iv`	`text`	No
`version`	`int4`	Yes	1
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()

RLS Policies:

Users can access own encryption key — SELECT for {public}

Indexes:

user_encryption_keys_pkey

vault_metadata

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`user_id`	`uuid`	No
`salt`	`text`	No
`kdf_algorithm`	`varchar(50)`	No	'PBKDF2-SHA256'::character varying
`kdf_iterations`	`int4`	No	100000
`kdf_memory`	`int4`	Yes
`kdf_parallelism`	`int4`	Yes
`auto_lock_timeout_minutes`	`int4`	Yes	15
`require_password_on_decrypt`	`bool`	Yes	true
`biometric_unlock_enabled`	`bool`	Yes	false
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()
`last_unlocked_at`	`timestamptz`	Yes
`failed_unlock_attempts`	`int4`	Yes	0
`last_failed_unlock_at`	`timestamptz`	Yes

RLS Policies:

rls_vault_metadata_insert — INSERT for {public}
rls_vault_metadata_select — SELECT for {public}
rls_vault_metadata_update — UPDATE for {public}

Indexes:

vault_metadata_pkey
vault_metadata_user_id_key

vault_access_log

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`user_id`	`uuid`	No
`event_type`	`varchar(50)`	No
`exchange_credential_id`	`uuid`	Yes
`success`	`bool`	No	true
`failure_reason`	`text`	Yes
`ip_address`	`inet`	Yes
`user_agent`	`text`	Yes
`session_id`	`uuid`	Yes
`device_fingerprint`	`text`	Yes
`geo_country`	`varchar(2)`	Yes
`geo_city`	`varchar(100)`	Yes
`metadata`	`jsonb`	Yes	'{}'::jsonb
`created_at`	`timestamptz`	No	now()

RLS Policies:

rls_vault_access_log_insert — INSERT for {public}
rls_vault_access_log_select — SELECT for {public}

Indexes:

idx_vault_access_log_exchange_credential_id
idx_vault_access_log_user_id
vault_access_log_pkey

exchange_connections

Column	Type	Nullable
`id`	`uuid`	No
`user_id`	`uuid`	No
`name`	`varchar(100)`	No
`exchange_type`	`varchar(50)`	No
`environment`	`varchar(20)`	Yes
`status`	`varchar(20)`	Yes
`is_default`	`bool`	Yes
`is_enabled`	`bool`	Yes
`encrypted_api_key`	`bytea`	No
`encrypted_secret_key`	`bytea`	No
`encrypted_passphrase`	`bytea`	Yes
`key_encryption_salt`	`bytea`	No
`api_key_label`	`varchar(100)`	Yes
`api_key_permissions`	`json`	Yes
`api_key_created_at`	`timestamp`	Yes
`api_key_expires_at`	`timestamp`	Yes
`api_key_last_used`	`timestamp`	Yes
`base_url`	`varchar(500)`	Yes
`rate_limit`	`int4`	Yes
`timeout_seconds`	`int4`	Yes
`retry_attempts`	`int4`	Yes
`daily_withdraw_limit`	`numeric`	Yes
`monthly_withdraw_limit`	`numeric`	Yes
`max_position_size`	`numeric`	Yes
`allowed_symbols`	`json`	Yes
`blocked_symbols`	`json`	Yes
`max_daily_loss`	`numeric`	Yes
`stop_loss_percentage`	`numeric`	Yes
`max_leverage`	`numeric`	Yes
`last_successful_ping`	`timestamp`	Yes
`last_error_message`	`text`	Yes
`error_count`	`int4`	Yes
`connection_warnings`	`json`	Yes
`created_at`	`timestamp`	No
`updated_at`	`timestamp`	Yes
`last_verified_at`	`timestamp`	Yes
`verification_status`	`varchar(20)`	Yes
`connection_metadata`	`json`	Yes
`tags`	`json`	Yes

RLS Policies:

exchange_connections_delete_own — DELETE for {public}
exchange_connections_insert_own — INSERT for {public}
exchange_connections_select_own — SELECT for {public}
exchange_connections_update_own — UPDATE for {public}

Indexes:

exchange_connections_pkey
ix_exchange_connections_exchange_type
ix_exchange_connections_status
ix_exchange_connections_user_id

exchange_balances

Column	Type	Nullable
`id`	`uuid`	No
`connection_id`	`uuid`	No
`user_id`	`uuid`	No
`symbol`	`varchar(20)`	No
`total_balance`	`numeric`	No
`available_balance`	`numeric`	No
`locked_balance`	`numeric`	No
`account_type`	`varchar(20)`	Yes
`usd_value`	`numeric`	Yes
`last_price`	`numeric`	Yes
`last_updated`	`timestamp`	Yes
`synced_at`	`timestamp`	No

RLS Policies:

exchange_balances_delete_own — DELETE for {public}
exchange_balances_insert_own — INSERT for {public}
exchange_balances_select_own — SELECT for {public}
exchange_balances_update_own — UPDATE for {public}

Indexes:

exchange_balances_pkey
idx_exchange_balances_connection_id
idx_exchange_balances_user_id
ix_exchange_balances_symbol
ix_exchange_balances_synced_at

exchange_api_calls

Column	Type	Nullable
`id`	`uuid`	No
`connection_id`	`uuid`	No
`user_id`	`uuid`	No
`endpoint`	`varchar(500)`	No
`method`	`varchar(10)`	No
`parameters`	`json`	Yes
`status_code`	`int4`	Yes
`response_time_ms`	`int4`	Yes
`success`	`bool`	Yes
`error_message`	`text`	Yes
`rate_limit_remaining`	`int4`	Yes
`rate_limit_reset`	`timestamp`	Yes
`ip_address`	`varchar(45)`	Yes
`user_agent`	`varchar(500)`	Yes
`session_id`	`uuid`	Yes
`created_at`	`timestamp`	No

RLS Policies:

exchange_api_calls_delete_own — DELETE for {public}
exchange_api_calls_insert_own — INSERT for {public}
exchange_api_calls_select_own — SELECT for {public}
exchange_api_calls_update_own — UPDATE for {public}

Indexes:

exchange_api_calls_pkey
idx_exchange_api_calls_connection_id
idx_exchange_api_calls_user_id
ix_exchange_api_calls_created_at

site_api_keys

Encrypted API keys for third-party AI providers. Admin-only access via RLS. Keys encrypted with GCP KMS before storage.

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`provider`	`varchar(50)`	No
`encrypted_key`	`text`	No
`key_prefix`	`varchar(20)`	Yes
`is_active`	`bool`	No	true
`last_used_at`	`timestamptz`	Yes
`usage_count`	`int4`	No	0
`last_rotated_at`	`timestamptz`	Yes
`rotation_reminder_days`	`int4`	No	90
`next_rotation_date`	`timestamptz`	Yes
`notes`	`text`	Yes
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()
`created_by`	`uuid`	Yes
`updated_by`	`uuid`	Yes

RLS Policies:

Admin full access to site_api_keys — ALL for {authenticated}

Indexes:

idx_site_api_keys_active
idx_site_api_keys_provider
idx_site_api_keys_provider_active
idx_site_api_keys_provider_unique_active
idx_site_api_keys_rotation
site_api_keys_pkey

api_key_permissions

Defines permission scopes and rate limits for each API key

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`api_key_id`	`uuid`	No
`can_read`	`bool`	Yes	true
`can_trade`	`bool`	Yes	false
`can_withdraw`	`bool`	Yes	false
`can_transfer`	`bool`	Yes	false
`can_manage_sub_accounts`	`bool`	Yes	false
`max_requests_per_minute`	`int4`	Yes	60
`max_requests_per_hour`	`int4`	Yes	3600
`max_requests_per_day`	`int4`	Yes	100000
`max_trade_amount_usd`	`numeric`	Yes
`max_daily_trade_volume_usd`	`numeric`	Yes
`max_withdrawal_amount_usd`	`numeric`	Yes
`max_daily_withdrawal_usd`	`numeric`	Yes
`allowed_ip_addresses`	`_inet`	Yes
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Service role can insert api key permissions — INSERT for {public}
Users can update own api key permissions — UPDATE for {public}
Users can view own api key permissions — SELECT for {public}

Indexes:

api_key_permissions_pkey
idx_api_key_permissions_key_id
unique_api_key_permissions

api_key_rotation_schedule

Tracks API key rotation schedules and sends proactive reminders

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`api_key_id`	`uuid`	No
`user_id`	`uuid`	No
`rotation_interval_days`	`int4`	No	90
`last_rotated_at`	`timestamptz`	No	now()
`next_rotation_due`	`timestamptz`	No
`reminder_sent_30_days`	`bool`	Yes	false
`reminder_sent_14_days`	`bool`	Yes	false
`reminder_sent_7_days`	`bool`	Yes	false
`reminder_sent_1_day`	`bool`	Yes	false
`is_overdue`	`bool`	Yes	false
`days_overdue`	`int4`	Yes	0
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage rotation schedules — ALL for {public}
Users can update own rotation schedules — UPDATE for {public}
Users can view own rotation schedules — SELECT for {public}

Indexes:

api_key_rotation_schedule_pkey
idx_api_key_rotation_due
idx_api_key_rotation_overdue
idx_api_key_rotation_schedule_api_key_id

api_key_usage_audit

Comprehensive audit log of all API key operations for security monitoring

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`api_key_id`	`uuid`	No
`user_id`	`uuid`	No
`operation_type`	`text`	No
`endpoint`	`text`	No
`http_method`	`text`	Yes
`ip_address`	`inet`	Yes
`user_agent`	`text`	Yes
`device_fingerprint`	`text`	Yes
`status_code`	`int4`	Yes
`success`	`bool`	Yes	true
`error_message`	`text`	Yes
`daily_usage_count`	`int4`	Yes	0
`monthly_usage_count`	`int4`	Yes	0
`is_suspicious`	`bool`	Yes	false
`suspicious_reasons`	`_text`	Yes
`risk_score`	`int4`	Yes	0
`created_at`	`timestamptz`	No	now()

RLS Policies:

Service role can insert usage logs — INSERT for {public}
Users can view own api key usage — SELECT for {public}

Indexes:

api_key_usage_audit_pkey
idx_api_key_usage_audit_key_id
idx_api_key_usage_audit_operation
idx_api_key_usage_audit_suspicious
idx_api_key_usage_audit_user_id

integration_tokens

Stores encrypted OAuth tokens for third-party integrations

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`integration_type`	`varchar(50)`	No
`access_token`	`text`	No
`refresh_token`	`text`	Yes
`token_type`	`varchar(20)`	Yes	'Bearer'::character varying
`expires_at`	`timestamptz`	Yes
`scopes`	`_text`	Yes
`metadata`	`jsonb`	Yes	'{}'::jsonb
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()

RLS Policies:

Service role can manage all integration tokens — ALL for {service_role}
Users can delete their own integration tokens — DELETE for {public}
Users can insert their own integration tokens — INSERT for {public}
Users can update their own integration tokens — UPDATE for {public}
Users can view their own integration tokens — SELECT for {public}

Indexes:

idx_integration_tokens_expires
idx_integration_tokens_type
idx_integration_tokens_user_id
integration_tokens_pkey
integration_tokens_user_id_integration_type_key

integrations

User integration configuration and enablement status

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`integration_type`	`varchar(50)`	No
`enabled`	`bool`	Yes	false
`configuration`	`jsonb`	Yes	'{}'::jsonb
`last_sync_at`	`timestamptz`	Yes
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()

RLS Policies:

Service role can manage all integrations — ALL for {service_role}
Users can delete their own integrations — DELETE for {public}
Users can insert their own integrations — INSERT for {public}
Users can update their own integrations — UPDATE for {public}
Users can view their own integrations — SELECT for {public}

Indexes:

idx_integrations_type
idx_integrations_user_enabled
integrations_pkey
integrations_user_id_integration_type_key

Exchange & Vault

On this page