Payments & Billing

Stripe integration, subscriptions, usage tracking, referrals, and fee management.

13 tables in this group.

customers

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()
`user_id`	`uuid`	No
`email`	`text`	No
`stripe_customer_id`	`text`	Yes
`paypal_customer_id`	`text`	Yes
`metadata`	`jsonb`	Yes	'{}'::jsonb

RLS Policies:

Users can view their own customer data — ALL for {public}

Indexes:

customers_paypal_customer_id_key
customers_pkey
customers_stripe_customer_id_key
idx_customers_user_id

subscriptions

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()
`customer_id`	`uuid`	No
`tier`	`subscription_tier`	No	'free'::subscription_tier
`status`	`subscription_status`	No	'active'::subscription_status
`provider`	`payment_provider`	No
`provider_subscription_id`	`text`	No
`provider_product_id`	`text`	Yes
`provider_price_id`	`text`	Yes
`current_period_start`	`timestamptz`	Yes
`current_period_end`	`timestamptz`	Yes
`trial_start`	`timestamptz`	Yes
`trial_end`	`timestamptz`	Yes
`cancel_at_period_end`	`bool`	Yes	false
`canceled_at`	`timestamptz`	Yes
`metadata`	`jsonb`	Yes	'{}'::jsonb

RLS Policies:

Users can view their own subscription data — ALL for {public}

Indexes:

idx_subscriptions_customer_id
subscriptions_pkey
subscriptions_provider_provider_subscription_id_key

subscription_history

Audit trail for all subscription changes

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`tier`	`text`	No
`status`	`text`	No
`stripe_customer_id`	`text`	Yes
`stripe_subscription_id`	`text`	Yes
`stripe_price_id`	`text`	Yes
`amount_cents`	`int4`	Yes
`currency`	`text`	Yes	'usd'::text
`billing_interval`	`text`	Yes
`started_at`	`timestamptz`	No	now()
`ended_at`	`timestamptz`	Yes
`cancellation_reason`	`text`	Yes
`created_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage subscription history — ALL for {public}
Users can view own subscription history — SELECT for {public}

Indexes:

idx_subscription_history_active
idx_subscription_history_dates
idx_subscription_history_stripe_customer
idx_subscription_history_user_id
subscription_history_pkey

payment_events

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`created_at`	`timestamptz`	Yes	now()
`provider`	`payment_provider`	No
`provider_event_id`	`text`	No
`event_type`	`text`	No
`processed`	`bool`	Yes	false
`subscription_id`	`uuid`	Yes
`customer_id`	`uuid`	Yes
`data`	`jsonb`	No
`error_message`	`text`	Yes

RLS Policies:

Users can view own payment events — SELECT for {public}

Indexes:

payment_events_pkey
payment_events_provider_provider_event_id_key

stripe_events

Stripe webhook events for idempotency and debugging

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`event_id`	`text`	No
`event_type`	`text`	No
`user_id`	`uuid`	Yes
`stripe_customer_id`	`text`	Yes
`stripe_subscription_id`	`text`	Yes
`payload`	`jsonb`	No
`processed`	`bool`	Yes	false
`processed_at`	`timestamptz`	Yes
`error_message`	`text`	Yes
`created_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage stripe events — ALL for {public}

Indexes:

idx_stripe_events_customer
idx_stripe_events_event_id
idx_stripe_events_processed
idx_stripe_events_user_id
stripe_events_event_id_key
stripe_events_pkey

usage_limits

Daily usage tracking for API calls and trades

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`date`	`date`	No	CURRENT_DATE
`api_calls`	`int4`	Yes	0
`trades`	`int4`	Yes	0
`portfolio_value_usd`	`numeric`	Yes	0
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage usage limits — ALL for {public}
Users can view own usage limits — SELECT for {public}

Indexes:

idx_usage_limits_date
idx_usage_limits_user_date
unique_user_date
usage_limits_pkey

feature_usage

Column	Type	Nullable	Default
`id`	`uuid`	No	uuid_generate_v4()
`created_at`	`timestamptz`	Yes	now()
`updated_at`	`timestamptz`	Yes	now()
`customer_id`	`uuid`	No
`feature_type`	`text`	No
`usage_count`	`int4`	Yes	0
`usage_limit`	`int4`	Yes
`reset_period`	`text`	Yes	'monthly'::text
`last_reset`	`timestamptz`	Yes	now()
`metadata`	`jsonb`	Yes	'{}'::jsonb

RLS Policies:

Users can view their own feature usage — ALL for {public}

Indexes:

feature_usage_customer_id_feature_type_key
feature_usage_pkey

fee_history

Historical fee records for completed trades

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`trade_id`	`uuid`	Yes
`exchange`	`varchar(50)`	No
`symbol`	`varchar(20)`	No
`side`	`varchar(10)`	No
`maker_fee`	`numeric`	No	0
`taker_fee`	`numeric`	No	0
`network_fee`	`numeric`	No	0
`spread_cost`	`numeric`	No	0
`total_fee`	`numeric`	No	0
`fee_currency`	`varchar(10)`	No
`trade_value`	`numeric`	No
`effective_fee_percentage`	`numeric`	No	0
`executed_at`	`timestamptz`	No
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	Yes	now()

RLS Policies:

fee_history_service_all — ALL for {service_role}
fee_history_user_insert — INSERT for {public}
fee_history_user_select — SELECT for {public}

Indexes:

fee_history_pkey
idx_fee_history_executed_at
idx_fee_history_user_executed
idx_fee_history_user_id

referral_codes

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`code`	`varchar(20)`	No
`is_active`	`bool`	No	true
`expires_at`	`timestamptz`	Yes
`max_uses`	`int4`	Yes
`current_uses`	`int4`	No	0
`metadata`	`jsonb`	Yes	'{}'::jsonb
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage codes — ALL for {service_role}
Users can view own referral codes — SELECT for {public}

Indexes:

idx_referral_codes_user_id
referral_codes_code_key
referral_codes_pkey
unique_user_active_code

referral_rewards

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`referral_id`	`uuid`	No
`referrer_id`	`uuid`	No
`referred_id`	`uuid`	No
`tier_id`	`uuid`	Yes
`tier_name`	`varchar(50)`	Yes
`reward_type`	`varchar(30)`	No
`amount`	`numeric`	No
`currency`	`varchar(10)`	No	'USD'::character varying
`commission_rate`	`numeric`	Yes
`status`	`varchar(20)`	No	'pending'::character varying
`paid_at`	`timestamptz`	Yes
`payout_id`	`uuid`	Yes
`transaction_reference`	`varchar(100)`	Yes
`metadata`	`jsonb`	Yes	'{}'::jsonb
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage rewards — ALL for {service_role}
Users can view own rewards — SELECT for {public}

Indexes:

idx_referral_rewards_referrer_id
referral_rewards_pkey

referrals

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`referrer_id`	`uuid`	No
`referred_id`	`uuid`	No
`referral_code_id`	`uuid`	No
`referral_code`	`varchar(20)`	No
`status`	`varchar(20)`	No	'pending'::character varying
`verified_at`	`timestamptz`	Yes
`verification_metadata`	`jsonb`	Yes	'{}'::jsonb
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage referrals — ALL for {service_role}
Users can view referrals they created — SELECT for {public}

Indexes:

idx_referrals_referrer_id
referrals_pkey
unique_referral

referral_payouts

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`user_id`	`uuid`	No
`payout_schedule`	`varchar(20)`	No
`amount`	`numeric`	No
`currency`	`varchar(10)`	No	'USD'::character varying
`status`	`varchar(20)`	No	'pending'::character varying
`payment_method`	`varchar(50)`	Yes
`payment_details`	`jsonb`	Yes	'{}'::jsonb
`transaction_id`	`varchar(100)`	Yes
`transaction_hash`	`varchar(100)`	Yes
`processed_at`	`timestamptz`	Yes
`failed_reason`	`text`	Yes
`retry_count`	`int4`	No	0
`metadata`	`jsonb`	Yes	'{}'::jsonb
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Service role can manage payouts — ALL for {service_role}
Users can view own payouts — SELECT for {public}

Indexes:

idx_referral_payouts_user_id
referral_payouts_pkey

referral_tiers

Column	Type	Nullable	Default
`id`	`uuid`	No	gen_random_uuid()
`tier_name`	`varchar(50)`	No
`tier_level`	`int4`	No
`min_referrals`	`int4`	No
`max_referrals`	`int4`	Yes
`commission_rate`	`numeric`	No
`description`	`text`	Yes
`benefits`	`jsonb`	Yes	'[]'::jsonb
`is_active`	`bool`	No	true
`created_at`	`timestamptz`	No	now()
`updated_at`	`timestamptz`	No	now()

RLS Policies:

Anyone can view active tiers — SELECT for {public}
Service role can manage tiers — ALL for {service_role}

Indexes:

referral_tiers_pkey
referral_tiers_tier_level_key
referral_tiers_tier_name_key

Payments & Billing

On this page