supabase_schema.sql

-- ============================================================
-- Options Flow Dashboard — Supabase Schema
-- Run this in the Supabase SQL editor
-- ============================================================

CREATE TABLE IF NOT EXISTS flow_alerts (
    id                  UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at          TIMESTAMPTZ DEFAULT NOW(),

    -- Trade identity
    trade_date          DATE        NOT NULL,
    ticker              TEXT        NOT NULL,   -- underlying (e.g. AAPL)
    contract_ticker     TEXT        NOT NULL,   -- options contract ticker

    -- Contract details
    contract_type       TEXT        NOT NULL CHECK (contract_type IN ('call', 'put')),
    strike_price        NUMERIC(12, 2) NOT NULL,
    expiration_date     DATE        NOT NULL,
    dte                 INTEGER     NOT NULL,

    -- Flow metrics
    premium             NUMERIC(16, 2),         -- total premium (price * vol * 100)
    price               NUMERIC(12, 4),         -- per-contract price
    volume              INTEGER,
    open_interest       INTEGER,
    vol_oi_ratio        NUMERIC(8, 4),

    -- Greeks & market context
    implied_volatility  NUMERIC(8, 4),
    underlying_price    NUMERIC(12, 2),
    moneyness           TEXT CHECK (moneyness IN ('ITM', 'ATM', 'OTM')),
    delta               NUMERIC(8, 6),
    gamma               NUMERIC(10, 8),
    vega                NUMERIC(10, 6),        -- $ change per 1% IV move
    theta               NUMERIC(10, 6),        -- $ decay per calendar day

    -- Raw Polygon.io response for auditability
    raw_data            JSONB,

    -- Intelligence layer (Phase 2)
    flow_score          NUMERIC(5, 1),         -- 0–100 composite signal quality
    repeat_count        INTEGER DEFAULT 0,     -- same strike/expiry seen in last 3 days
    order_type          TEXT CHECK (order_type IN ('sweep', 'block', 'split', 'unknown')),
    side                TEXT CHECK (side IN ('ask', 'mid', 'bid', 'unknown')),
    iv_rank             NUMERIC(5, 4),         -- 0.0–1.0 (low = buying cheap IV)
    days_to_earnings    INTEGER,               -- calendar days to next earnings event

    -- Claude enrichment (Phase 4)
    ai_summary          TEXT,
    ai_sentiment        TEXT CHECK (ai_sentiment IN ('bullish', 'bearish', 'neutral')),
    ai_confidence       TEXT CHECK (ai_confidence IN ('high', 'medium', 'low'))
);

-- Unique constraint used by upsert to prevent duplicate pipeline runs
ALTER TABLE flow_alerts
    ADD CONSTRAINT flow_alerts_unique_daily_contract
    UNIQUE (trade_date, contract_ticker);

-- ============================================================
-- Indexes
-- ============================================================

-- Primary access pattern: latest alerts sorted by premium
CREATE INDEX idx_flow_alerts_trade_date_premium
    ON flow_alerts (trade_date DESC, premium DESC);

-- Filter by ticker
CREATE INDEX idx_flow_alerts_ticker
    ON flow_alerts (ticker);

-- Filter by sentiment
CREATE INDEX idx_flow_alerts_sentiment
    ON flow_alerts (ai_sentiment);

-- Primary sort: flow_score replaces premium as default sort (highest signal first)
CREATE INDEX IF NOT EXISTS idx_flow_alerts_flow_score
    ON flow_alerts (trade_date DESC, flow_score DESC NULLS LAST);

-- Filter by order_type (sweep-only mode)
CREATE INDEX IF NOT EXISTS idx_flow_alerts_order_type
    ON flow_alerts (trade_date DESC, order_type);

-- Earnings proximity filter
CREATE INDEX IF NOT EXISTS idx_flow_alerts_earnings
    ON flow_alerts (trade_date DESC, days_to_earnings);

-- Repeat flow lookup: find same (ticker, strike, expiry) across dates
CREATE INDEX IF NOT EXISTS idx_flow_alerts_repeat_lookup
    ON flow_alerts (ticker, strike_price, expiration_date, trade_date DESC);

-- ============================================================
-- Row Level Security
-- ============================================================

ALTER TABLE flow_alerts ENABLE ROW LEVEL SECURITY;

-- Public read — add auth checks here when you add user accounts
CREATE POLICY "allow_public_read" ON flow_alerts
    FOR SELECT USING (true);

-- Only service role can write (ingestion pipeline uses service key)
CREATE POLICY "allow_service_write" ON flow_alerts
    FOR INSERT WITH CHECK (true);

CREATE POLICY "allow_service_upsert" ON flow_alerts
    FOR UPDATE USING (true);

-- ============================================================
-- Realtime (enables the Supabase JS client to subscribe to inserts)
-- ============================================================

ALTER PUBLICATION supabase_realtime ADD TABLE flow_alerts;


-- ============================================================
-- User Profiles (linked to Supabase Auth)
-- ============================================================

CREATE TABLE IF NOT EXISTS profiles (
    id                      UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email                   TEXT NOT NULL,
    tier                    TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free', 'pro')),
    stripe_customer_id      TEXT UNIQUE,
    stripe_subscription_id  TEXT UNIQUE,
    subscription_status     TEXT DEFAULT 'none' CHECK (subscription_status IN ('none', 'active', 'past_due', 'canceled', 'trialing')),
    subscription_period_end TIMESTAMPTZ,
    created_at              TIMESTAMPTZ DEFAULT NOW(),
    updated_at              TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Users can read their own profile
CREATE POLICY "users_read_own_profile" ON profiles
    FOR SELECT USING (auth.uid() = id);

-- Service role can manage all profiles (Stripe webhooks, admin)
CREATE POLICY "service_manage_profiles" ON profiles
    FOR ALL USING (true) WITH CHECK (true);

CREATE INDEX idx_profiles_stripe_customer ON profiles (stripe_customer_id);
CREATE INDEX idx_profiles_stripe_subscription ON profiles (stripe_subscription_id);

-- Auto-create a free profile when a new user signs up
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO profiles (id, email, tier)
    VALUES (NEW.id, NEW.email, 'free');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION handle_new_user();


-- ============================================================
-- Alert Rules (Priority 4.1 — Pro tier custom notifications)
-- ============================================================

CREATE TABLE IF NOT EXISTS alert_rules (
    id                  UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at          TIMESTAMPTZ DEFAULT NOW(),
    updated_at          TIMESTAMPTZ DEFAULT NOW(),

    user_id             UUID        NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    name                TEXT        NOT NULL DEFAULT 'My Rule',
    active              BOOLEAN     NOT NULL DEFAULT true,
    notify_email        BOOLEAN     NOT NULL DEFAULT true,

    -- Flexible JSONB conditions:
    -- { "min_premium": 500000, "contract_type": "call", "order_type": "sweep",
    --   "max_dte": 30, "min_flow_score": 70, "tickers": ["AAPL","NVDA"] }
    conditions          JSONB       NOT NULL DEFAULT '{}',

    last_triggered_at   TIMESTAMPTZ,
    trigger_count       INTEGER     NOT NULL DEFAULT 0
);

ALTER TABLE alert_rules ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_manage_own_rules" ON alert_rules
    FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- Service role can read all active rules (for pipeline evaluation)
CREATE POLICY "service_read_rules" ON alert_rules
    FOR SELECT USING (true);

CREATE INDEX idx_alert_rules_user_id ON alert_rules (user_id);
CREATE INDEX idx_alert_rules_active ON alert_rules (active) WHERE active = true;


-- ============================================================
-- Alert Rule Matches (tracks which alerts triggered which rules)
-- ============================================================

CREATE TABLE IF NOT EXISTS alert_rule_matches (
    id          UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    rule_id     UUID        NOT NULL REFERENCES alert_rules(id) ON DELETE CASCADE,
    alert_id    UUID        NOT NULL REFERENCES flow_alerts(id) ON DELETE CASCADE,
    notified    BOOLEAN     NOT NULL DEFAULT false,
    UNIQUE (rule_id, alert_id)
);

ALTER TABLE alert_rule_matches ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_see_own_matches" ON alert_rule_matches
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM alert_rules ar
            WHERE ar.id = rule_id AND ar.user_id = auth.uid()
        )
    );

CREATE INDEX idx_alert_rule_matches_rule ON alert_rule_matches (rule_id, created_at DESC);