Schema & Constraints¶

All Brandvelia data lives in the brandvelia schema of the shared Supabase project panoptes. The app touches nothing outside that schema. Table names use the bv_ prefix and the domain is encoded in the nominal TypeScript ID (BV_Campaign, etc.).

Access model¶

Access is controlled in three layers (see INFRA.md for the operational rules):

RLS — every brandvelia table has Row-Level Security on; isolation is by workspace_id via workspace_members.
Self-updating views public.bv_* — the REST API reads auto-refreshing views with security_invoker = true, so the base-table RLS applies transparently (views "freeze" the column list).
SQL wrappers — public functions like bv_create_workspace, bv_get_dashboard_summary, bv_create_product_score.

Roles¶

Role	Scope
`anon`	Only `published` landing pages (specific policy).
`authenticated`	Data in workspaces where the user is a member.
`service_role`	Public server-side endpoints only (`/api/track`, `/api/leads`).

Table inventory¶

`bv_*` table	Domain
`bv_workspaces`	Workspaces (multi-tenancy root).
`bv_workspace_members`	Membership; drives RLS `workspace_id` isolation.
`bv_workspaces_api_credentials`	Generated API credentials (Vision Board).
`bv_offers`	The product catalog (network + own products).
`bv_marketplaces`	Inbound marketplace products feed (`offers_src`).
`bv_snapshots` / `bv_marketplace_snapshots`	Radar opportunity snapshots.
`bv_marketplace` / `bv_ai_models`	Offer scores; AI model registry (`bv_ai_models`).
`bv_visits`	Attributed visits (`click_id` → landing page).
`bv_transactions`	Ledger (orders, refunds, chargebacks, fees).
`bv_campaigns`, `bv_metrics`	Traffic runs + spend metric rows.
`bv_creatives`	AI-generated creative.
`bv_landing_pages`	Operator landing destinations.
`bv_leads` / `bv_lead_events`	CRM capture + pipeline.
`bv_experiments`	A/B tests + metric breakdown.
`bv_ai_runs`, `bv_ai_usage`	AI call logs + aggregate usage.
`bv_ai_results`	Persisted AI output.
`bv_finance`	Consolidated ledger / P&L.
`bv_vision_board`	Product ideas + offer spreadsheets.
`bv_integrations`	Connected apps, webhooks, triggers, refresh.
`bv_settings`	Workspace + operator settings.

Representative columns¶

// bv_offers (OfferRow)
affiliates, product_name, category, subcategory, price, price_notes, payout_model,
commission_amount, commission_type, payout_frequency, max_payout, currency,
affiliate_network, affiliate_id, affiliate_id_type, target_audience,
compliance_risk, country, language, status, source, source_url

// bv_marketplace_snapshots (MarketplaceSnapshotRow)
workspace_id, vendor, category, gravity, initial_payout, recurring_payout, rebill_rate,
avg_percent_per_sale, opportunity_score, score_breakdown { economics, gravityFit,
funnelQuality, trafficFit, creativePotential, complianceScore, total }, compliance_risk,
recommended_traffic_source, captured_at

// bv_transactions (TransactionRow)
type, subtype, amount, currency, tx_date, description, external_ref,
offer_id, campaign_id, account_id

Key constraints¶

Idempotency (replay-safe). Raw ClickBank events use a unique dedup_key; the same event can be replayed without creating duplicate transactions.
Amount normalization. Values are normalized to USD; raw currencies are normalized via ClickBank (MonetaryAmount has a 3-letter ISO 4217 currency).
Integrity. Fallback/default values are set so a row always has a sensible currency (default USD), an ordering rank, and required status strings.

Status enumerations¶

Workspace: BV_Unknown · BV_M · BV_S.
Landing page: landing_status (landing_status enum); original status tracked.
Marketplace snapshot: BV_Snapshots.
AI model: seeded registry in bv_ai_models (is_active, task).
Subscription detail (analytics): active, completed, canceled, retry_payment, request_new_card.