supabase_migration.sql

-- ============================================================================
-- agent verf - Supabase Database Migration (MVP)
-- Version: 0.1.0
-- Last Updated: 2026-01-19
--
-- Run this in Supabase SQL Editor:
-- Dashboard → SQL Editor → New Query → Paste this → Run
-- ============================================================================

-- Enable UUID extension (should already be enabled in Supabase)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================================
-- Table: receipts - Core verification results
-- ============================================================================
CREATE TABLE IF NOT EXISTS receipts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  created_at TIMESTAMPTZ DEFAULT now(),

  -- Source content info
  source_url TEXT,
  source_platform TEXT,  -- twitter, tiktok, youtube, web, etc.
  content_type TEXT,     -- text, image, video, audio, mixed
  content_text TEXT,     -- Original text or transcript

  -- Verdict
  verdict_status TEXT NOT NULL,  -- TRUE, FALSE, MISLEADING, UNVERIFIABLE, etc.
  verdict_confidence FLOAT CHECK (verdict_confidence >= 0 AND verdict_confidence <= 1),
  verdict_summary TEXT,
  verdict_reasoning TEXT,

  -- Analysis metadata
  key_findings JSONB,    -- Array of key findings
  primary_lens TEXT,     -- factual, satire, viral, ai_detection
  sources_checked INTEGER DEFAULT 0,

  -- Processing metadata
  processing_time_ms INTEGER,
  model_used TEXT,
  tools_used TEXT[],
  cost_usd FLOAT DEFAULT 0,

  -- Request metadata
  request_platform TEXT,  -- web, telegram, discord, api
  scan_mode TEXT DEFAULT 'quick_scan'  -- quick_scan or deep_dive
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_receipts_created ON receipts(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_receipts_source_url ON receipts(source_url);
CREATE INDEX IF NOT EXISTS idx_receipts_verdict ON receipts(verdict_status);

-- ============================================================================
-- Table: evidence - Evidence linked to receipts
-- ============================================================================
CREATE TABLE IF NOT EXISTS evidence (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  receipt_id UUID REFERENCES receipts(id) ON DELETE CASCADE,

  -- Evidence content
  claim TEXT,
  finding TEXT,
  supports_verdict BOOLEAN,

  -- Source info
  source_url TEXT,
  source_domain TEXT,
  source_title TEXT,
  authority_score FLOAT CHECK (authority_score >= 0 AND authority_score <= 1),

  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_evidence_receipt ON evidence(receipt_id);

-- ============================================================================
-- View: recent_verifications - For the "Recently Verified" feed
-- ============================================================================
CREATE OR REPLACE VIEW recent_verifications AS
SELECT
  id,
  created_at,
  source_url,
  source_platform,
  verdict_status,
  verdict_confidence,
  verdict_summary,
  LEFT(content_text, 200) as content_preview,
  primary_lens
FROM receipts
ORDER BY created_at DESC
LIMIT 20;

-- ============================================================================
-- Row Level Security (RLS) - MVP: Public read, service insert
-- ============================================================================
ALTER TABLE receipts ENABLE ROW LEVEL SECURITY;
ALTER TABLE evidence ENABLE ROW LEVEL SECURITY;

-- Receipts are publicly readable
CREATE POLICY "Receipts are publicly readable"
  ON receipts FOR SELECT
  USING (true);

-- Receipts can be inserted by service role
CREATE POLICY "Receipts are insertable by service"
  ON receipts FOR INSERT
  WITH CHECK (true);

-- Evidence is publicly readable
CREATE POLICY "Evidence is publicly readable"
  ON evidence FOR SELECT
  USING (true);

-- Evidence can be inserted by service role
CREATE POLICY "Evidence is insertable by service"
  ON evidence FOR INSERT
  WITH CHECK (true);

-- ============================================================================
-- Table: users - User accounts with mode preferences
-- ============================================================================
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- Authentication (Google OAuth via Supabase Auth)
  google_id TEXT UNIQUE NOT NULL,
  email TEXT,
  display_name TEXT,
  avatar_url TEXT,

  -- Premium features
  is_premium BOOLEAN DEFAULT FALSE,
  preferred_mode TEXT DEFAULT 'free' CHECK (preferred_mode IN ('free', 'venice')),

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Index for quick premium user lookups
CREATE INDEX IF NOT EXISTS idx_users_google_id ON users(google_id);
CREATE INDEX IF NOT EXISTS idx_users_is_premium ON users(is_premium) WHERE is_premium = TRUE;

-- Add user_id column to receipts to track who submitted
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES users(id);
CREATE INDEX IF NOT EXISTS idx_receipts_user_id ON receipts(user_id);

-- ============================================================================
-- RLS for users table
-- ============================================================================
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Users can view their own data
CREATE POLICY "Users can view own data"
  ON users FOR SELECT
  USING (auth.uid()::text = google_id OR auth.uid() = id);

-- Users can update their own preferred_mode
CREATE POLICY "Users can update own mode"
  ON users FOR UPDATE
  USING (auth.uid()::text = google_id OR auth.uid() = id)
  WITH CHECK (auth.uid()::text = google_id OR auth.uid() = id);

-- Service role can insert new users
CREATE POLICY "Service can insert users"
  ON users FOR INSERT
  WITH CHECK (true);

-- ============================================================================
-- Function: Update updated_at timestamp
-- ============================================================================
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================================================
-- Done! Verify by running:
-- SELECT * FROM receipts LIMIT 1;
-- SELECT * FROM users LIMIT 1;
-- ============================================================================