supabase_schema.sql

-- Create a table for storing user holdings (OPTIONAL - only used if Cloud Sync is enabled)
-- In Hybrid Model, this is mostly unused by default
create table if not exists holdings (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users not null,
  symbol text not null,
  name text,
  asset_type text not null,
  quantity numeric not null check (quantity >= 0),
  buy_price numeric check (buy_price >= 0),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- User Settings (Risk Profile, Max Caps)
create table if not exists user_settings (
  user_id uuid references auth.users primary key,
  risk_profile text default 'MODERATE', -- 'CONSERVATIVE', 'MODERATE', 'AGGRESSIVE'
  max_sector_cap numeric default 25.0,
  max_stock_cap numeric default 15.0,
  updated_at timestamp with time zone default timezone('utc'::text, now())
);

-- Risk Reports (Metadata only - Privacy Safe)
create table if not exists risk_reports (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users not null,
  risk_score numeric,
  volatility_score numeric,
  diversification_score numeric,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable RLS
alter table holdings enable row level security;
alter table user_settings enable row level security;
alter table risk_reports enable row level security;

-- Policies for Holdings
create policy "Users can view their own holdings" on holdings for select using (auth.uid() = user_id);
create policy "Users can insert their own holdings" on holdings for insert with check (auth.uid() = user_id);
create policy "Users can update their own holdings" on holdings for update using (auth.uid() = user_id);
create policy "Users can delete their own holdings" on holdings for delete using (auth.uid() = user_id);

-- Policies for User Settings
create policy "Users can view their own settings" on user_settings for select using (auth.uid() = user_id);
create policy "Users can update their own settings" on user_settings for update using (auth.uid() = user_id);
create policy "Users can insert their own settings" on user_settings for insert with check (auth.uid() = user_id);

-- Policies for Risk Reports
create policy "Users can view their own reports" on risk_reports for select using (auth.uid() = user_id);
create policy "Users can insert their own reports" on risk_reports for insert with check (auth.uid() = user_id);



-- Encrypted Backups (For Hybrid Model Sync)

create table if not exists encrypted_portfolios (

  user_id uuid references auth.users primary key,

  encrypted_data text not null, -- Stores the full JSON.stringify(portfolio) or ciphertext

  updated_at timestamp with time zone default timezone('utc'::text, now())

);



alter table encrypted_portfolios enable row level security;

create policy "Users can view their own backup" on encrypted_portfolios for select using (auth.uid() = user_id);

create policy "Users can update their own backup" on encrypted_portfolios for update using (auth.uid() = user_id);

create policy "Users can insert their own backup" on encrypted_portfolios for insert with check (auth.uid() = user_id);