Penn 的 Web3 金融实验室

-- Supabase Schema & Security Rules for Penn's Finance Lab

-- Table for user portfolios
CREATE TABLE IF NOT EXISTS public.portfolios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_address TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    total_balance NUMERIC DEFAULT 0.00,
    defi_yield_annualized NUMERIC DEFAULT 12.5
);

-- Table for trade execution logs synced by users
CREATE TABLE IF NOT EXISTS public.trading_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID REFERENCES public.portfolios(id) ON DELETE CASCADE,
    pair TEXT NOT NULL,
    direction TEXT CHECK (direction IN ('LONG', 'SHORT')) NOT NULL,
    entry_price NUMERIC NOT NULL,
    exit_price NUMERIC,
    size NUMERIC NOT NULL,
    pnl NUMERIC,
    status TEXT DEFAULT 'OPEN' CHECK (status IN ('OPEN', 'CLOSED')) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Enable Row Level Security (RLS)
ALTER TABLE public.portfolios ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.trading_logs ENABLE ROW LEVEL SECURITY;

-- Dynamic Policies for decentralized access
CREATE POLICY "Public Read Permission" 
    ON public.portfolios FOR SELECT 
    USING (true);

CREATE POLICY "Wallet Authed Modification" 
    ON public.portfolios FOR ALL 
    USING (auth.uid()::text = wallet_address);