supabase/migrations/001_initial_schema.sql

-- ============================================================================
-- OpticParse & PhishVision — Supabase Database Schema
-- Run this migration in your Supabase SQL Editor (Database → SQL Editor)
-- ============================================================================

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ---------------------------------------------------------------------------
-- users: linked to Supabase Auth, stores subscription tier + billing info
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL UNIQUE,
  tier TEXT NOT NULL DEFAULT 'free'
    CHECK (tier IN ('free', 'pro', 'enterprise')),
  monthly_limit INTEGER NOT NULL DEFAULT 100,
  current_usage INTEGER NOT NULL DEFAULT 0,
  usage_reset_at TIMESTAMPTZ NOT NULL DEFAULT (date_trunc('month', now()) + interval '1 month'),
  lemon_customer_id TEXT,
  lemon_subscription_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Auto-create a user row when someone signs up via Supabase Auth
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (id, email)
  VALUES (NEW.id, NEW.email);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

-- ---------------------------------------------------------------------------
-- api_keys: SHA-256 hashed keys bound to user profiles
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  key_hash TEXT NOT NULL UNIQUE,
  key_prefix TEXT NOT NULL,    -- e.g. 'op_live_a1b2c3d4'
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON public.api_keys (key_hash) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_api_keys_user ON public.api_keys (user_id);

-- ---------------------------------------------------------------------------
-- usage_logs: per-request audit trail
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.usage_logs (
  id BIGSERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES public.users(id),
  api_key_id UUID REFERENCES public.api_keys(id),
  endpoint TEXT NOT NULL,
  service TEXT NOT NULL CHECK (service IN ('opticparse', 'phishvision')),
  status_code INTEGER,
  response_time_ms INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_usage_user_month
  ON public.usage_logs (user_id, created_at);

-- ---------------------------------------------------------------------------
-- security_events: fraud detection and key suspension log
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.security_events (
  id BIGSERIAL PRIMARY KEY,
  api_key_id UUID REFERENCES public.api_keys(id),
  user_id UUID REFERENCES public.users(id),
  event_type TEXT NOT NULL
    CHECK (event_type IN ('abuse_detected', 'key_suspended', 'key_revoked', 'key_regenerated', 'tier_upgraded', 'tier_downgraded')),
  details JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- Row Level Security (RLS) policies
-- ---------------------------------------------------------------------------

-- Users can only read their own row
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users read own" ON public.users
  FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users update own" ON public.users
  FOR UPDATE USING (auth.uid() = id);

-- API keys: users can only see their own
ALTER TABLE public.api_keys ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Keys read own" ON public.api_keys
  FOR SELECT USING (auth.uid() = user_id);

-- Usage logs: users can only see their own
ALTER TABLE public.usage_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Usage read own" ON public.usage_logs
  FOR SELECT USING (auth.uid() = user_id);

-- Security events: users can only see their own
ALTER TABLE public.security_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Events read own" ON public.security_events
  FOR SELECT USING (auth.uid() = user_id);

-- ---------------------------------------------------------------------------
-- Monthly usage reset function (call via Supabase cron or pg_cron)
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.reset_monthly_usage()
RETURNS void AS $$
BEGIN
  UPDATE public.users
  SET current_usage = 0,
      usage_reset_at = date_trunc('month', now()) + interval '1 month',
      updated_at = now()
  WHERE usage_reset_at <= now();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;