-- ==========================================
-- CV Privacy Platform - Initial Supabase Schema
-- ==========================================
-- 1. Enable pgcrypto for any potential database-level encryption (though app-level AES is preferred per spec)
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- 2. Custom Enumeration Types
CREATE TYPE document_status AS ENUM ('pending', 'scanning', 'redacting', 'processing', 'ready', 'failed');
CREATE TYPE document_type AS ENUM ('cv', 'jd');
CREATE TYPE plan_tier_enum AS ENUM ('free', 'pro', 'enterprise');
CREATE TYPE usage_event_type AS ENUM ('upload_cv', 'generate_resume', 'pdf_export');
-- 3. Updated_at Trigger Function
CREATE OR REPLACE FUNCTION handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- ==========================================
-- TABLES
-- ==========================================
-- 4. USERS (Tenant root, mirroring auth.users securely)
CREATE TABLE public.users (
id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 5. PROFILES (Public/App facing user data)
CREATE TABLE public.profiles (
id UUID REFERENCES public.users(id) ON DELETE CASCADE PRIMARY KEY,
first_name TEXT,
last_name TEXT,
encrypted_pii_vault JSONB, -- Stores AES encrypted PII data via application layer
privacy_mode_enabled BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 6. SOURCE_DOCUMENTS
CREATE TABLE public.source_documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
file_name TEXT NOT NULL,
storage_path TEXT NOT NULL, -- Path in Supabase Storage
doc_type document_type NOT NULL,
status document_status DEFAULT 'pending',
extracted_json JSONB, -- The raw extraction prior to generation
metadata JSONB DEFAULT '{}'::jsonb, -- Store scan results, confidence scores, etc.
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 7. GENERATED_DOCUMENTS
CREATE TABLE public.generated_documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
source_cv_id UUID NOT NULL REFERENCES public.source_documents(id) ON DELETE CASCADE,
source_jd_id UUID REFERENCES public.source_documents(id) ON DELETE SET NULL,
storage_path TEXT NOT NULL, -- Encrypted PDF/DOCX storage path
match_score INT CHECK (match_score >= 0 AND match_score <= 100),
ai_confidence_metrics JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 8. SUBSCRIPTIONS
CREATE TABLE public.subscriptions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE UNIQUE,
stripe_customer_id TEXT,
stripe_subscription_id TEXT,
plan_tier plan_tier_enum DEFAULT 'free',
status TEXT NOT NULL, -- e.g., 'active', 'canceled', 'past_due'
current_period_end TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 9. USAGE_EVENTS
CREATE TABLE public.usage_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
event_type usage_event_type NOT NULL,
credits_used INT DEFAULT 1,
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 10. AUDIT_LOGS (Immutable)
CREATE TABLE public.audit_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES public.users(id) ON DELETE SET NULL,
action TEXT NOT NULL, -- e.g., 'PII_REDACTED', 'DOCUMENT_DELETED'
ip_address TEXT, -- Handled defensively by the API (hashed or masked)
details JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ==========================================
-- INDEXES FOR PERFORMANCE
-- ==========================================
CREATE INDEX idx_source_docs_user_id ON public.source_documents(user_id);
CREATE INDEX idx_generated_docs_user_id ON public.generated_documents(user_id);
CREATE INDEX idx_usage_events_user_id ON public.usage_events(user_id);
CREATE INDEX idx_audit_logs_user_id ON public.audit_logs(user_id);
-- ==========================================
-- TRIGGERS
-- ==========================================
CREATE TRIGGER set_updated_at_users BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION handle_updated_at();
CREATE TRIGGER set_updated_at_profiles BEFORE UPDATE ON public.profiles FOR EACH ROW EXECUTE FUNCTION handle_updated_at();
CREATE TRIGGER set_updated_at_source_documents BEFORE UPDATE ON public.source_documents FOR EACH ROW EXECUTE FUNCTION handle_updated_at();
CREATE TRIGGER set_updated_at_generated_documents BEFORE UPDATE ON public.generated_documents FOR EACH ROW EXECUTE FUNCTION handle_updated_at();
CREATE TRIGGER set_updated_at_subscriptions BEFORE UPDATE ON public.subscriptions FOR EACH ROW EXECUTE FUNCTION handle_updated_at();
-- ==========================================
-- ROW LEVEL SECURITY (RLS) POLICIES
-- ==========================================
-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.source_documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.generated_documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.usage_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.audit_logs ENABLE ROW LEVEL SECURITY;
-- USERS
CREATE POLICY "Users can only view their own user record" ON public.users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can only update their own user record" ON public.users FOR UPDATE USING (auth.uid() = id);
-- PROFILES
CREATE POLICY "Users can only view their own profile" ON public.profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can install their own profile" ON public.profiles FOR INSERT WITH CHECK (auth.uid() = id);
CREATE POLICY "Users can only update their own profile" ON public.profiles FOR UPDATE USING (auth.uid() = id);
-- SOURCE_DOCUMENTS
CREATE POLICY "Users can view own source documents" ON public.source_documents FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own source documents" ON public.source_documents FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own source documents" ON public.source_documents FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own source documents" ON public.source_documents FOR DELETE USING (auth.uid() = user_id);
-- GENERATED_DOCUMENTS
CREATE POLICY "Users can view own generated documents" ON public.generated_documents FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own generated documents" ON public.generated_documents FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own generated documents" ON public.generated_documents FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own generated documents" ON public.generated_documents FOR DELETE USING (auth.uid() = user_id);
-- SUBSCRIPTIONS
CREATE POLICY "Users can view own subscription" ON public.subscriptions FOR SELECT USING (auth.uid() = user_id);
-- Note: Subscriptions are usually inserted/updated via Supabase Service Role and webhooks, so users should only SELECT.
-- USAGE_EVENTS
CREATE POLICY "Users can view own usage events" ON public.usage_events FOR SELECT USING (auth.uid() = user_id);
-- Note: Usage events usually inserted by backend. If client-side needs insert:
CREATE POLICY "Users can insert own usage events" ON public.usage_events FOR INSERT WITH CHECK (auth.uid() = user_id);
-- AUDIT_LOGS
CREATE POLICY "Users can view own audit logs" ON public.audit_logs FOR SELECT USING (auth.uid() = user_id);
-- Insert is strictly backend-only via service role, so no user insert policy.