Database Schema

RLS ENABLEDALL TABLES

Engine

Supabase (hosted PostgreSQL). All tables use Row Level Security (RLS). Server-side operations use the service role key which bypasses RLS. Client-side operations use the anon key with auth.uid() policies.

Tables

User profiles, auto-created on signup via trigger on auth.users.

Column	Type	Constraints	Description
id	UUID	PK, FK -> auth.users(id) ON DELETE CASCADE	User ID (matches auth user)
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Account creation time
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Last profile update (auto-trigger)
stripe_customer_id	TEXT		Stripe customer ID (set on first checkout)
stripe_subscription_id	TEXT		Stripe subscription ID
subscription_status	TEXT	CHECK (free, trial, trialing, active, cancelled, expired, past_due), DEFAULT 'free'	Current subscription tier
subscription_plan	TEXT		Plan name from Stripe (e.g., price ID or nickname)
subscription_period_end	TIMESTAMPTZ		Current billing period end date
trial_ends_at	TIMESTAMPTZ		Trial expiration date
free_audits_remaining	INTEGER	DEFAULT 3	Remaining free audits this month
prop_firm	TEXT		Selected prop firm name
firm_id	TEXT		Firm identifier
account_size	INTEGER		Challenge account size
challenge_phase	TEXT		Current challenge phase
daily_loss_limit_pct	DECIMAL		Daily loss limit percentage
daily_loss_type	TEXT		Type of daily loss calculation
max_drawdown_pct	DECIMAL		Max drawdown percentage
max_drawdown_type	TEXT		Type of drawdown calculation
profit_target_pct	DECIMAL		Profit target percentage
max_daily_trades	INTEGER		Maximum daily trade count
ai_provider	TEXT	DEFAULT 'openai'	AI provider preference
has_byok_key	BOOLEAN	DEFAULT false	Whether user has BYOK API key
current_streak_days	INTEGER	DEFAULT 0	Current consecutive audit days
longest_streak_days	INTEGER	DEFAULT 0	All-time longest streak
last_audit_date	DATE		Date of most recent audit
onboarding_complete	BOOLEAN	DEFAULT false	Whether onboarding wizard was completed
trading_rules	TEXT		Legacy rules text field
telegram_chat_id	TEXT	UNIQUE (partial, WHERE NOT NULL)	Linked Telegram chat ID
discord_user_id	TEXT	UNIQUE (partial, WHERE NOT NULL)	Linked Discord user ID
whatsapp_phone	TEXT	UNIQUE (partial, WHERE NOT NULL)	Linked WhatsApp phone number
gmail_journal_address	TEXT		Linked email for journal
journal_preferences	JSONB	DEFAULT '{}'	Journal config (summary time, digest day, etc.)
utm_source_first	TEXT		First-touch UTM source
utm_medium_first	TEXT		First-touch UTM medium
utm_campaign_first	TEXT		First-touch UTM campaign
utm_source_last	TEXT		Last-touch UTM source
utm_medium_last	TEXT		Last-touch UTM medium
utm_campaign_last	TEXT		Last-touch UTM campaign

Triggers:

on_auth_user_created -- Inserts a profile row when a new auth user is created
profiles_updated_at -- Auto-updates updated_at on every UPDATE

RLS: Users can SELECT and UPDATE their own row only. No INSERT policy (handled by trigger). No DELETE policy.

User-defined trading rules used for AI audit evaluation.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()	Rule ID
user_id	UUID	NOT NULL, FK -> profiles(id) ON DELETE CASCADE	Owner
rule_text	TEXT	NOT NULL	Rule description in plain English
category	TEXT	CHECK (entry, exit, risk, timing), DEFAULT 'entry'	Rule category
is_active	BOOLEAN	DEFAULT true	Whether rule is currently active
sort_order	INTEGER	DEFAULT 0	Display order
version	INTEGER	DEFAULT 1	Rule version number
previous_version_id	UUID	FK -> trading_rules(id) ON DELETE SET NULL	Previous version for history
quality_grade	TEXT		Quality grade (A-F) from rule grading
win_rate	DECIMAL		Compliance win rate (0-1)
avg_pnl	DECIMAL		Average P&L when rule is followed
sample_count	INTEGER	DEFAULT 0	Number of verdicts used for grading
last_graded_at	TIMESTAMPTZ		When grade was last calculated
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Auto-updated

Triggers: trading_rules_updated_at -- Auto-updates updated_at.

RLS: Full CRUD for own rules (auth.uid() = user_id).

Individual trades with audit results and source tracking.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()	Trade ID
user_id	UUID	NOT NULL, FK -> profiles(id) ON DELETE CASCADE	Owner
daily_tracking_id	UUID	FK -> daily_tracking(id) ON DELETE SET NULL	Associated daily tracking record
symbol	TEXT		Trading instrument (NQ, ES, EURUSD, etc.)
direction	TEXT		'long' or 'short'
entry_price	DECIMAL		Entry price
exit_price	DECIMAL		Exit price
entry_time	TIMESTAMPTZ		Entry timestamp
exit_time	TIMESTAMPTZ		Exit timestamp
position_size	DECIMAL		Position size (lots/contracts)
stop_loss	DECIMAL		Stop loss price
take_profit	DECIMAL		Take profit price
pnl	DECIMAL		Realized P&L
commissions	DECIMAL		Commission costs
screenshot_url	TEXT		Supabase Storage URL for chart screenshot
screenshot_quality	TEXT		Screenshot quality assessment
audit_status	TEXT	CHECK (pending, audited, skipped), DEFAULT 'pending'	Audit processing status
audit_result	JSONB		Full AI audit result (verdicts, compliance score, cost)
audit_provider	TEXT		AI provider used for audit
compliance_score	DECIMAL		Overall compliance score (0-100)
rules_followed	INTEGER		Count of rules followed
rules_violated	INTEGER		Count of rules violated
rules_unclear	INTEGER		Count of unclear verdicts
has_overrides	BOOLEAN	DEFAULT false	Whether any verdict was manually overridden
source_type	TEXT	CHECK (csv_import, broker_sync, manual, journal), DEFAULT 'csv_import'	How the trade was imported
source_connection_id	UUID	FK -> broker_connections(id) ON DELETE SET NULL	Broker connection that synced this trade
external_id	TEXT		External trade ID from broker (for dedup)
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()

Indexes:

idx_trades_user_created -- (user_id, created_at DESC) for chronological listing
idx_trades_external_dedup -- UNIQUE (user_id, source_connection_id, external_id) WHERE external_id IS NOT NULL for deduplication

RLS: Full CRUD for own trades (auth.uid() = user_id).

Per-rule verdict on each trade, created during AI audit.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()	Verdict ID
trade_id	UUID	NOT NULL, FK -> trades(id) ON DELETE CASCADE	Associated trade
rule_id	UUID	NOT NULL, FK -> trading_rules(id) ON DELETE CASCADE	Associated rule
verdict	TEXT	CHECK (followed, violated, unclear)	AI determination
evidence	TEXT		Specific explanation with data points
severity	TEXT	DEFAULT 'minor'	minor, major, or critical
ai_confidence	INTEGER	CHECK (1-5)	AI confidence in the verdict
is_overridden	BOOLEAN	DEFAULT false	Whether user manually overrode
original_verdict	TEXT		Original AI verdict before override
override_reason	TEXT		User's reason for overriding
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()

Indexes:

idx_trade_rule_verdicts_trade -- (trade_id) for joining with trades

RLS: Access controlled via trade ownership (EXISTS (SELECT 1 FROM trades WHERE trades.id = trade_id AND trades.user_id = auth.uid())).

Daily P&L, balance, and compliance tracking.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
user_id	UUID	NOT NULL, FK -> profiles(id) ON DELETE CASCADE	Owner
trade_date	DATE	NOT NULL, UNIQUE(user_id, trade_date)	Calendar date
starting_balance	DECIMAL		Day's starting balance
ending_balance	DECIMAL		Day's ending balance
daily_pnl	DECIMAL		Total P&L for the day
max_equity_high	DECIMAL		Intraday equity high
trades_taken	INTEGER	DEFAULT 0	Number of trades
daily_loss_used	DECIMAL		Daily loss consumed
drawdown_used	DECIMAL		Drawdown consumed
compliance_score	DECIMAL		Average compliance score for the day
day_status	TEXT	CHECK (trading, rest_day, gap), DEFAULT 'trading'	Day classification

Indexes:

idx_daily_tracking_user_date -- (user_id, trade_date DESC) for chronological queries

RLS: Full CRUD for own tracking (auth.uid() = user_id).

Stores broker connection state, encrypted credentials, and sync configuration.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()	Connection ID
user_id	UUID	NOT NULL, FK -> profiles(id) ON DELETE CASCADE	Owner
broker_type	TEXT	NOT NULL, CHECK (metatrader4, metatrader5, tradovate, ctrader, rithmic, dxtrade, match_trader, ninjatrader, tradingview)	Broker platform
auth_type	TEXT	NOT NULL, CHECK (oauth, credentials, api_key, file_upload)	Authentication method
display_name	TEXT		User-chosen label
encrypted_credentials	TEXT		AES-256-GCM encrypted credentials (base64)
status	TEXT	NOT NULL, CHECK (pending, active, error, disconnected, token_expired), DEFAULT 'pending'	Connection lifecycle state
error_message	TEXT		Last error message
consecutive_errors	INTEGER	DEFAULT 0	Count of consecutive sync failures (resets on success)
last_sync_at	TIMESTAMPTZ		Timestamp of last successful sync
sync_interval_minutes	INTEGER	DEFAULT 15	Sync interval (used by cron)
trades_synced_total	INTEGER	DEFAULT 0	Total trades synced via this connection
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Auto-updated

Unique constraint: (user_id, broker_type, display_name) -- prevents duplicate connections.

Indexes:

idx_broker_connections_user -- (user_id) for listing user's connections
idx_broker_connections_sync -- (status, last_sync_at) WHERE status = 'active' for cron sync queries

Triggers: broker_connections_updated_at -- Auto-updates updated_at.

RLS: Full CRUD for own connections (auth.uid() = user_id).

Per-sync audit trail, one row per sync attempt.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()	Log ID
connection_id	UUID	NOT NULL, FK -> broker_connections(id) ON DELETE CASCADE	Associated connection
started_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Sync start time
completed_at	TIMESTAMPTZ		Sync completion time
status	TEXT	NOT NULL, CHECK (running, success, partial, error), DEFAULT 'running'	Sync outcome
trades_fetched	INTEGER	DEFAULT 0	Total trades returned by adapter
trades_new	INTEGER	DEFAULT 0	New trades inserted
trades_duplicate	INTEGER	DEFAULT 0	Duplicate trades skipped
error_message	TEXT		Error details if failed
duration_ms	INTEGER		Sync duration in milliseconds

Indexes:

idx_sync_logs_connection -- (connection_id, started_at DESC) for chronological log retrieval

RLS: Read-only access via connection ownership.

Email captures from quiz, simulator, or waitlist.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
email	TEXT	NOT NULL, UNIQUE	Email address
source	TEXT	DEFAULT 'quiz'	Capture source (quiz, simulator, waitlist)
quiz_result	JSONB		Quiz answers and recommendation
created_at	TIMESTAMPTZ	DEFAULT now()
subscribed	BOOLEAN	DEFAULT true	Newsletter subscription status

RLS: Enabled, no client policies (service role only).

Quiz answer tracking with attribution.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
created_at	TIMESTAMPTZ	DEFAULT now()
market	TEXT	NOT NULL	futures, forex, or both
style	TEXT	NOT NULL	scalp, daytrade, or swing
budget	TEXT	NOT NULL	under100, 100-300, 300-500, 500plus
priority	TEXT	NOT NULL	cheapest, relaxed, split, payout
trailing_dd_ok	TEXT	NOT NULL	yes or no
recommended_firm	TEXT	NOT NULL	Top recommendation
recommended_score	INTEGER	NOT NULL	Recommendation score
all_scores	JSONB	NOT NULL	Full scoring breakdown
utm_source	TEXT		UTM attribution
utm_medium	TEXT
utm_campaign	TEXT
referrer	TEXT		Referrer URL

RLS: Enabled, no client policies (service role only).

Unclaimed audit results for PLG funnel and rate limiting.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
ip_hash	TEXT	NOT NULL	SHA-256 hashed IP for rate limiting
result_hash	TEXT	UNIQUE, NOT NULL	Unique result identifier
firm_id	TEXT	NOT NULL	Source identifier (firm name or 'strategy-analyzer')
audit_result	JSONB	NOT NULL	Full audit/analysis result
claimed_by	UUID		User who claimed this result after signup
created_at	TIMESTAMPTZ	DEFAULT now()
expires_at	TIMESTAMPTZ	NOT NULL	Auto-cleanup date

Indexes:

idx_anon_audits_ip -- (ip_hash, created_at DESC) for rate limit queries
idx_anon_audits_hash -- (result_hash) for lookup by hash

RLS: Enabled, no client policies (service role only).

Referral tracking between users.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
referrer_id	UUID	FK -> profiles(id) ON DELETE CASCADE	User who referred
referred_id	UUID	FK -> profiles(id) ON DELETE CASCADE, UNIQUE	User who was referred
status	TEXT	CHECK (pending, completed, rewarded), DEFAULT 'pending'	Referral lifecycle
reward_applied	BOOLEAN	DEFAULT false	Whether reward was given
created_at	TIMESTAMPTZ	DEFAULT now()

RLS: Users can SELECT where they are referrer or referred. Users can INSERT where referred_id = auth.uid().

System announcements (feature launches, warnings).

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
title	TEXT	NOT NULL	Announcement title
body	TEXT	NOT NULL	Announcement content
type	TEXT	CHECK (info, feature, warning), DEFAULT 'info'	Category
active	BOOLEAN	DEFAULT true	Whether currently displayed
created_at	TIMESTAMPTZ	DEFAULT now()

RLS: Anyone can SELECT active announcements.

Tracks which users dismissed which announcements.

Column	Type	Constraints
id	UUID	PK, DEFAULT gen_random_uuid()
user_id	UUID	FK -> profiles(id) ON DELETE CASCADE
announcement_id	UUID	FK -> announcements(id) ON DELETE CASCADE
dismissed_at	TIMESTAMPTZ	DEFAULT now()

Unique constraint: (user_id, announcement_id).

RLS: Users manage their own dismissals.

Strategy analysis results from the screenshot analyzer.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
user_id	UUID	FK -> profiles(id) ON DELETE CASCADE	Owner
screenshot_url	TEXT		Source screenshot URL
user_description	TEXT		User's strategy description
extracted_rules	JSONB		AI-extracted rules
overall_backtestability	DECIMAL		Backtestability score
pine_script	TEXT		Generated Pine Script v5 code
pine_script_version	INTEGER	DEFAULT 1	Pine Script version number
backtest_imported	BOOLEAN	DEFAULT false	Whether backtest was imported
backtest_results	JSONB		Imported backtest statistics
fitness_analysis	JSONB		Strategy fitness analysis
parent_analysis_id	UUID	FK -> strategy_analyses(id)	Parent for iteration chains
iteration_number	INTEGER	DEFAULT 1	Iteration count in optimization chain
created_at	TIMESTAMPTZ	DEFAULT now()
updated_at	TIMESTAMPTZ	DEFAULT now()

RLS: Users can SELECT, INSERT, UPDATE their own analyses.

Shareable audit result links with OG image data.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
result_hash	TEXT	UNIQUE, NOT NULL	URL-safe hash for sharing
strategy_summary	TEXT		Strategy description for display
pnl_percent	DECIMAL		P&L percentage for OG image
stats	JSONB		Full statistics object
og_image_url	TEXT		Generated OG image URL
views	INTEGER	DEFAULT 0	View counter
created_at	TIMESTAMPTZ	DEFAULT now()

Indexes: idx_shared_results_hash -- (result_hash) for hash-based lookup.

Time-limited, single-use codes for connecting messaging platforms.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
user_id	UUID	NOT NULL, FK -> profiles(id) ON DELETE CASCADE	Code owner
platform	TEXT	NOT NULL, CHECK (telegram, discord, whatsapp, email)	Target platform
code	TEXT	NOT NULL, UNIQUE	Link code (VGL-XXXX format)
used	BOOLEAN	DEFAULT false	Whether code has been consumed
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()
expires_at	TIMESTAMPTZ	NOT NULL	10-minute expiry

Indexes: idx_link_codes_lookup -- (code, used, expires_at) for fast validation.

RLS: Users can SELECT and INSERT their own codes.

State machine for multi-turn journal interactions per platform.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
user_id	UUID	NOT NULL, FK -> profiles(id) ON DELETE CASCADE
platform	TEXT	NOT NULL, CHECK (telegram, discord, whatsapp, email)
platform_chat_id	TEXT	NOT NULL	Platform-specific chat identifier
state	TEXT	NOT NULL, CHECK (idle, awaiting_confirmation, awaiting_screenshot, awaiting_details), DEFAULT 'idle'	Conversation state
pending_trade	JSONB		Trade data awaiting confirmation
context	JSONB	DEFAULT '{}'	Additional conversation context
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Auto-updated

Unique constraint: (user_id, platform) -- one conversation per platform per user.

RLS: Users manage their own conversations.

Audit log for journal message debugging.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT gen_random_uuid()
user_id	UUID	NOT NULL, FK -> profiles(id) ON DELETE CASCADE
platform	TEXT	NOT NULL, CHECK (telegram, discord, whatsapp, email)
direction	TEXT	NOT NULL, CHECK (inbound, outbound)	Message direction
content_type	TEXT	NOT NULL, CHECK (text, image, command, system), DEFAULT 'text'	Message type
content_preview	TEXT		Truncated content for debugging
trade_id	UUID	FK -> trades(id) ON DELETE SET NULL	Linked trade if applicable
metadata	JSONB	DEFAULT '{}'	Platform-specific metadata
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()

Indexes:

idx_journal_messages_user -- (user_id, created_at DESC) for message history
idx_journal_messages_trade -- (trade_id) WHERE trade_id IS NOT NULL

RLS: Users can SELECT their own messages.

Storage Buckets

Private bucket for trade chart screenshots.

Setting	Value
Public	No
Max file size	5MB
Allowed MIME types	image/png, image/jpeg, image/webp
Path convention	`{userId}/{tradeId}-{timestamp}.{ext}`
Retention	90 days (cleaned by cron)

RLS Policies:

Upload: bucket_id = 'screenshots' AND folder = auth.uid()
Read: Same ownership check
Delete: Same ownership check

Relationships

auth.users
  |-- profiles (1:1, auto-created via trigger)
        |-- trading_rules (1:many)
        |-- trades (1:many)
        |     |-- trade_rule_verdicts (1:many, joins to trading_rules)
        |-- daily_tracking (1:many, unique on user_id + trade_date)
        |-- broker_connections (1:many)
        |     |-- sync_logs (1:many)
        |     |-- trades.source_connection_id (1:many, backref)
        |-- referrals (as referrer or referred)
        |-- announcement_dismissals (1:many)
        |-- strategy_analyses (1:many, self-referencing parent chain)
        |-- platform_link_codes (1:many)
        |-- journal_conversations (1:many, unique on user_id + platform)
        |-- journal_messages (1:many)

Functions

Trigger function on auth.users INSERT. Creates a profiles row with the same UUID. SECURITY DEFINER.

Generic trigger function that sets NEW.updated_at = now(). Used by: profiles, trading_rules, broker_connections, journal_conversations.

RPC function to atomically increment free_audits_remaining. SECURITY DEFINER.

Migrations

15 migration files in supabase/migrations/, applied in timestamp order:

Migration	Description
`20260321094255_create_tradingaudit_tables.sql`	Initial tables: email_leads, quiz_completions, anonymous_audits
`20260322000000_fix_rls_security.sql`	Enable RLS on initial tables
`20260322010000_referrals.sql`	Referrals, announcements, announcement_dismissals
`20260322020000_storage_bucket.sql`	Screenshots storage bucket with RLS
`20260322030000_phase2_core_tables.sql`	Core tables: profiles, trading_rules, daily_tracking, trades, trade_rule_verdicts + triggers + RLS
`20260322040000_fix_profile_columns.sql`	Add onboarding_complete, trading_rules text, firm_id to profiles
`20260322050000_increment_free_audits_rpc.sql`	RPC function for atomic free audit increment
`20260323000000_add_subscription_columns.sql`	Add subscription_plan, subscription_period_end, stripe_subscription_id; expand status CHECK
`20260323022104_add_utm_columns_to_profiles.sql`	Add UTM attribution columns (first/last touch)
`20260323100000_add_rule_quality_columns.sql`	Add quality_grade, win_rate, avg_pnl, sample_count, last_graded_at to trading_rules
`20260323120000_add_strategy_analyses_table.sql`	Strategy analyses table with RLS
`20260323140000_add_shared_results_table.sql`	Shared results table with hash index
`20260324000000_journal_platform_support.sql`	Platform IDs on profiles, link codes, conversations, messages tables; expand trades.source_type
`20260325000000_broker_connections.sql`	Broker connections, sync_logs; extend trades with source_type, source_connection_id, external_id + dedup index
`20260325100000_broker_check_constraints.sql`	Add CHECK constraints on broker_type and auth_type enums

bash

# Apply locally (requires Supabase CLI and local Supabase running)
supabase db push

# Apply to remote (requires linked project)
supabase db push --linked

# Create a new migration
supabase migration new description_here

Architecture ALL DOCS Strategy Copilot