-- ============================================================================ -- VAIDYA AI - COMPLETE DATABASE SCHEMA -- Medical AI Platform - Supabase SQL Setup -- Generated: 2026-01-16 -- ============================================================================ -- -- INSTRUCTIONS: -- 1. Run this ENTIRE file in Supabase SQL Editor -- 2. Run in a SINGLE transaction for consistency -- 3. This combines ALL tables from backend/database and backend/database/migrations -- 4. Safe to run multiple times (uses IF NOT EXISTS) -- -- ============================================================================ -- ============================================================================ -- SECTION 1: ENABLE REQUIRED EXTENSIONS -- ============================================================================ CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION IF NOT EXISTS "vector"; CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- ============================================================================ -- SECTION 2: CORE TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Users Table -- Core user table for all platform users -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.users ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), email TEXT UNIQUE NOT NULL, name TEXT, plan TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'student', 'pro', 'admin')), role TEXT CHECK (role IN ('super_admin', 'admin', 'ops', 'support', 'viewer')), personal_api_key TEXT, fallback_locks JSONB DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), disabled BOOLEAN NOT NULL DEFAULT FALSE ); -- ----------------------------------------------------------------------------- -- Admin Allowlist Table -- Controls which users have admin access -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.admin_allowlist ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), email TEXT UNIQUE NOT NULL, role TEXT NOT NULL CHECK (role IN ('super_admin', 'admin', 'ops', 'support', 'viewer')), added_by UUID REFERENCES public.users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Usage Counters Table -- Tracks daily usage metrics per user -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.usage_counters ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, date DATE NOT NULL DEFAULT CURRENT_DATE, tokens_used INTEGER NOT NULL DEFAULT 0, requests_count INTEGER NOT NULL DEFAULT 0, pdf_uploads INTEGER NOT NULL DEFAULT 0, mcqs_generated INTEGER NOT NULL DEFAULT 0, images_used INTEGER NOT NULL DEFAULT 0, flashcards_generated INTEGER NOT NULL DEFAULT 0, -- Per-feature document upload counters chat_uploads_per_day INTEGER NOT NULL DEFAULT 0, mcq_uploads_per_day INTEGER NOT NULL DEFAULT 0, flashcard_uploads_per_day INTEGER NOT NULL DEFAULT 0, explain_uploads_per_day INTEGER NOT NULL DEFAULT 0, highyield_uploads_per_day INTEGER NOT NULL DEFAULT 0, UNIQUE(user_id, date) ); -- ----------------------------------------------------------------------------- -- API Keys Table -- Stores encrypted API keys for various providers -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.api_keys ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), provider TEXT NOT NULL, feature TEXT NOT NULL, key_value TEXT NOT NULL, priority INTEGER NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'degraded', 'disabled')), failure_count INTEGER NOT NULL DEFAULT 0, last_used_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Provider Health Table -- Tracks health status of API providers -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.provider_health ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), api_key_id UUID NOT NULL REFERENCES public.api_keys(id) ON DELETE CASCADE, checked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), status TEXT NOT NULL CHECK (status IN ('healthy', 'degraded', 'failed')), response_time_ms INTEGER, error_message TEXT, quota_remaining INTEGER ); -- ----------------------------------------------------------------------------- -- System Flags Table -- Stores system-wide configuration flags -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.system_flags ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), flag_name TEXT UNIQUE NOT NULL, flag_value TEXT NOT NULL, updated_by UUID REFERENCES public.users(id), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Audit Logs Table -- Tracks all admin actions for compliance -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.audit_logs ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), admin_id UUID REFERENCES public.users(id), action_type TEXT NOT NULL, target_type TEXT, target_id TEXT, details JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 3: CHAT & MESSAGING TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Chat Sessions Table -- Stores user chat sessions -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.chat_sessions ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, title TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Messages Table -- Stores individual messages in chat sessions -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.messages ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), session_id UUID NOT NULL REFERENCES public.chat_sessions(id) ON DELETE CASCADE, role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')), content TEXT NOT NULL, tokens_used INTEGER, citations JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 4: DOCUMENT & RAG TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Documents Table -- Stores uploaded document metadata -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.documents ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, filename TEXT NOT NULL, file_type TEXT, file_size INTEGER, storage_path TEXT, processing_status TEXT DEFAULT 'pending' CHECK (processing_status IN ('pending', 'processing', 'completed', 'failed')), processing_progress INTEGER DEFAULT 0, processing_stage TEXT DEFAULT 'Pending', error_message TEXT, expires_at TIMESTAMPTZ, processed_at TIMESTAMPTZ, feature TEXT CHECK (feature IN ('chat', 'mcq', 'flashcard', 'explain', 'highyield')), file_hash TEXT, total_chunks INTEGER DEFAULT 0, chunks_with_embeddings INTEGER DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW(), CONSTRAINT documents_file_type_check CHECK ( file_type IN ( 'application/pdf', 'image/jpeg', 'image/jpg', 'image/png', 'image/gif', 'image/webp', 'text/plain', 'application/msword', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' ) OR file_type IS NULL ) ); -- ----------------------------------------------------------------------------- -- Document Chunks Table -- Stores text chunks from documents for RAG -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.document_chunks ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), document_id UUID NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE, chunk_index INTEGER NOT NULL, content TEXT NOT NULL, embedding VECTOR(4096), created_at TIMESTAMPTZ DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Embeddings Table (Legacy/Alternative) -- Stores document embeddings (768-dimensional) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.embeddings ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), document_id UUID NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE, chunk_text TEXT NOT NULL, chunk_index INTEGER NOT NULL, embedding VECTOR(768), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- RAG Usage Logs Table -- Tracks RAG usage for monitoring and analytics -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.rag_usage_logs ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, feature TEXT NOT NULL CHECK (feature IN ('chat', 'mcq', 'flashcard', 'explain', 'highyield', 'unknown')), query_preview TEXT, document_id UUID REFERENCES public.documents(id) ON DELETE SET NULL, success BOOLEAN NOT NULL DEFAULT true, results_count INTEGER DEFAULT 0, grounding_score FLOAT, timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 5: STUDY TOOLS TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Study Tool Sessions Table -- Independent sessions for study tools (flashcards, MCQs, etc.) -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.study_tool_sessions ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, feature TEXT NOT NULL CHECK (feature IN ('flashcard', 'mcq', 'highyield', 'explain', 'map', 'conceptmap')), title TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Study Materials Table -- Generated study materials linked to sessions -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.study_materials ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), session_id UUID NOT NULL REFERENCES public.study_tool_sessions(id) ON DELETE CASCADE, feature TEXT NOT NULL CHECK (feature IN ('flashcard', 'mcq', 'highyield', 'explain', 'map', 'conceptmap')), topic TEXT NOT NULL, content TEXT NOT NULL, tokens_used INTEGER NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Study Sessions Table (Basic Study Planner) -- Basic study session scheduling -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.study_sessions ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, topic TEXT NOT NULL, duration INTEGER NOT NULL, scheduled_date TIMESTAMPTZ, notes TEXT, status TEXT NOT NULL DEFAULT 'planned' CHECK (status IN ('planned', 'in_progress', 'completed', 'cancelled')), completed_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 6: ENHANCED STUDY PLANNER TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Study Plan Entries Table -- Core table for storing individual study plan items -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.study_plan_entries ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Subject & Topic Details subject TEXT NOT NULL, topic TEXT, study_type TEXT NOT NULL CHECK (study_type IN ('mcqs', 'flashcards', 'clinical_cases', 'revision', 'osce', 'reading', 'practice', 'conceptmap')), -- Scheduling scheduled_date DATE NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, duration_minutes INTEGER GENERATED ALWAYS AS (EXTRACT(EPOCH FROM (end_time - start_time)) / 60) STORED, -- Priority & Status priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')), status TEXT NOT NULL DEFAULT 'planned' CHECK (status IN ('planned', 'in_progress', 'completed', 'skipped', 'rescheduled')), -- Performance Tracking completion_percentage INTEGER DEFAULT 0 CHECK (completion_percentage >= 0 AND completion_percentage <= 100), performance_score INTEGER CHECK (performance_score >= 0 AND performance_score <= 100), accuracy_percentage DECIMAL(5,2), -- Notes & Tags notes TEXT, tags TEXT[], color_code TEXT DEFAULT '#5C67F2', -- Recurrence is_recurring BOOLEAN DEFAULT FALSE, recurrence_pattern TEXT CHECK (recurrence_pattern IN ('daily', 'weekly', 'biweekly', 'monthly')), parent_entry_id UUID REFERENCES public.study_plan_entries(id) ON DELETE SET NULL, -- AI & Automation ai_suggested BOOLEAN DEFAULT FALSE, ai_rescheduled BOOLEAN DEFAULT FALSE, original_scheduled_date DATE, reschedule_reason TEXT, -- Timestamps started_at TIMESTAMPTZ, completed_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Study Goals Table -- Monthly/Weekly goals and milestones -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.study_goals ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Goal Details title TEXT NOT NULL, description TEXT, goal_type TEXT NOT NULL CHECK (goal_type IN ('daily', 'weekly', 'monthly', 'custom')), -- Target Metrics target_hours DECIMAL(5,2), target_sessions INTEGER, target_topics INTEGER, target_accuracy DECIMAL(5,2), -- Progress current_hours DECIMAL(5,2) DEFAULT 0, current_sessions INTEGER DEFAULT 0, current_topics INTEGER DEFAULT 0, current_accuracy DECIMAL(5,2) DEFAULT 0, -- Period start_date DATE NOT NULL, end_date DATE NOT NULL, -- Status status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'completed', 'failed', 'paused')), achieved_at TIMESTAMPTZ, -- Timestamps created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Performance Metrics Table -- Track daily performance for analytics and AI recommendations -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.performance_metrics ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Date Tracking metric_date DATE NOT NULL, -- Study Time total_study_minutes INTEGER DEFAULT 0, planned_study_minutes INTEGER DEFAULT 0, -- Session Stats sessions_planned INTEGER DEFAULT 0, sessions_completed INTEGER DEFAULT 0, sessions_skipped INTEGER DEFAULT 0, -- Performance average_accuracy DECIMAL(5,2), mcqs_attempted INTEGER DEFAULT 0, mcqs_correct INTEGER DEFAULT 0, flashcards_reviewed INTEGER DEFAULT 0, -- Topics topics_covered TEXT[], weak_topics TEXT[], strong_topics TEXT[], -- Streak & Consistency is_streak_day BOOLEAN DEFAULT FALSE, consistency_score DECIMAL(5,2), -- Timestamps created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE(user_id, metric_date) ); -- ----------------------------------------------------------------------------- -- AI Recommendations Table -- Store AI-generated study suggestions -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.ai_recommendations ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Recommendation Details recommendation_type TEXT NOT NULL CHECK (recommendation_type IN ('optimal_time', 'weak_topic', 'revision', 'break', 'reschedule', 'goal_adjustment')), title TEXT NOT NULL, description TEXT NOT NULL, -- Suggested Action suggested_subject TEXT, suggested_study_type TEXT, suggested_start_time TIME, suggested_duration_minutes INTEGER, -- Context reasoning TEXT, confidence_score DECIMAL(5,2) CHECK (confidence_score >= 0 AND confidence_score <= 100), based_on_data JSONB, -- Status status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'declined', 'expired')), expires_at TIMESTAMPTZ, -- Timestamps acted_upon_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Study Streaks Table -- Track user study streaks -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.study_streaks ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID UNIQUE NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Current Streak current_streak INTEGER DEFAULT 0, longest_streak INTEGER DEFAULT 0, -- Streak Tracking last_study_date DATE, streak_start_date DATE, -- Weekly Stats days_studied_this_week INTEGER DEFAULT 0, days_studied_this_month INTEGER DEFAULT 0, -- Timestamps updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Study Templates Table -- Reusable study plan templates -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.study_templates ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID REFERENCES public.users(id) ON DELETE CASCADE, -- Template Details name TEXT NOT NULL, description TEXT, is_public BOOLEAN DEFAULT FALSE, -- Template Structure template_data JSONB NOT NULL, -- Usage Stats times_used INTEGER DEFAULT 0, -- Timestamps created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 7: CLINICAL REASONING & OSCE TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Clinical Cases Table -- Stores structured patient cases with progressive information disclosure -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.clinical_cases ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Case Metadata case_type TEXT NOT NULL DEFAULT 'clinical_reasoning' CHECK (case_type IN ('clinical_reasoning', 'osce', 'diagnostic_challenge')), specialty TEXT NOT NULL DEFAULT 'general_medicine', difficulty TEXT NOT NULL DEFAULT 'intermediate' CHECK (difficulty IN ('beginner', 'intermediate', 'advanced', 'expert')), status TEXT NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress', 'completed', 'abandoned')), -- Patient Demographics patient_demographics JSONB NOT NULL DEFAULT '{}', -- Case Content (Progressive Disclosure) chief_complaint TEXT NOT NULL, history_of_present_illness JSONB DEFAULT '{}', past_medical_history JSONB DEFAULT '{}', family_history JSONB DEFAULT '{}', social_history JSONB DEFAULT '{}', review_of_systems JSONB DEFAULT '{}', physical_examination JSONB DEFAULT '{}', vital_signs JSONB DEFAULT '{}', initial_investigations JSONB DEFAULT '{}', imaging_results JSONB DEFAULT '{}', additional_investigations JSONB DEFAULT '{}', -- Case Stages and Progression stages JSONB NOT NULL DEFAULT '[]', current_stage INTEGER NOT NULL DEFAULT 0, stage_unlock_history JSONB DEFAULT '[]', -- Correct Answers (Hidden from user) differential_diagnoses JSONB DEFAULT '[]', final_diagnosis TEXT, diagnosis_explanation TEXT, management_plan JSONB DEFAULT '{}', red_flags TEXT[], clinical_pearls TEXT[], -- Timing time_started TIMESTAMPTZ DEFAULT NOW(), time_completed TIMESTAMPTZ, total_time_seconds INTEGER DEFAULT 0, -- Metadata created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Clinical Reasoning Steps Table -- Tracks each step of user's reasoning process -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.clinical_reasoning_steps ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), case_id UUID NOT NULL REFERENCES public.clinical_cases(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Step Information step_type TEXT NOT NULL CHECK (step_type IN ( 'problem_representation', 'differential_generation', 'diagnostic_justification', 'investigation_planning', 'final_diagnosis', 'management_plan', 'history_question', 'examination_request', 'investigation_request', 'clarification_request' )), step_number INTEGER NOT NULL, -- User Input user_input TEXT NOT NULL, user_notes TEXT, -- AI Evaluation ai_evaluation JSONB DEFAULT '{}', score DECIMAL(5,2), -- Timing time_taken_seconds INTEGER, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- OSCE Scenarios Table -- Structured OSCE examination scenarios -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.osce_scenarios ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Scenario Metadata scenario_type TEXT NOT NULL CHECK (scenario_type IN ( 'history_taking', 'physical_examination', 'communication_skills', 'clinical_procedure', 'data_interpretation', 'counseling', 'emergency_management' )), specialty TEXT NOT NULL DEFAULT 'general_medicine', difficulty TEXT NOT NULL DEFAULT 'intermediate' CHECK (difficulty IN ('beginner', 'intermediate', 'advanced', 'expert')), status TEXT NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress', 'completed', 'abandoned')), -- Candidate Instructions candidate_instructions TEXT NOT NULL, time_limit_seconds INTEGER NOT NULL DEFAULT 480, -- Patient Information patient_info JSONB NOT NULL DEFAULT '{}', patient_script JSONB NOT NULL DEFAULT '{}', -- Examiner Configuration examiner_checklist JSONB NOT NULL DEFAULT '[]', expected_actions JSONB DEFAULT '[]', -- Interaction History interaction_history JSONB DEFAULT '[]', -- Scoring global_rating_score DECIMAL(5,2), checklist_score DECIMAL(5,2), communication_score DECIMAL(5,2), clinical_competence_score DECIMAL(5,2), -- Timing time_started TIMESTAMPTZ DEFAULT NOW(), time_completed TIMESTAMPTZ, actual_time_seconds INTEGER DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Clinical Performance Table -- Aggregated performance metrics for each user -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.clinical_performance ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, -- Aggregate Metrics total_cases_attempted INTEGER NOT NULL DEFAULT 0, total_cases_completed INTEGER NOT NULL DEFAULT 0, total_osce_attempted INTEGER NOT NULL DEFAULT 0, total_osce_completed INTEGER NOT NULL DEFAULT 0, -- Average Scores avg_diagnostic_accuracy DECIMAL(5,2) DEFAULT 0, avg_clinical_reasoning DECIMAL(5,2) DEFAULT 0, avg_data_gathering DECIMAL(5,2) DEFAULT 0, avg_communication DECIMAL(5,2) DEFAULT 0, avg_time_efficiency DECIMAL(5,2) DEFAULT 0, -- Performance by Specialty specialty_scores JSONB DEFAULT '{}', -- Cognitive Bias Tracking cognitive_biases_detected JSONB DEFAULT '[]', -- Skill Progression skill_progression JSONB DEFAULT '{}', -- Weakness Analysis identified_weaknesses TEXT[], recommended_topics TEXT[], -- Streaks and Achievements current_streak INTEGER DEFAULT 0, longest_streak INTEGER DEFAULT 0, last_activity_date DATE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE(user_id) ); -- ----------------------------------------------------------------------------- -- Clinical Rubrics Table -- Evaluation rubrics for scoring -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.clinical_rubrics ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), -- Rubric Metadata rubric_name TEXT NOT NULL, rubric_type TEXT NOT NULL CHECK (rubric_type IN ('clinical_reasoning', 'osce', 'communication', 'procedure')), specialty TEXT, -- Scoring Criteria criteria JSONB NOT NULL DEFAULT '[]', -- Weighting total_weight DECIMAL(5,2) DEFAULT 100, is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Case Templates Table -- Pre-built case templates for quick generation -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.case_templates ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), -- Template Metadata template_name TEXT NOT NULL, specialty TEXT NOT NULL, difficulty TEXT NOT NULL CHECK (difficulty IN ('beginner', 'intermediate', 'advanced', 'expert')), case_type TEXT NOT NULL CHECK (case_type IN ('clinical_reasoning', 'osce', 'diagnostic_challenge')), -- Template Content template_data JSONB NOT NULL DEFAULT '{}', -- Learning Objectives learning_objectives TEXT[], target_skills TEXT[], -- Metadata times_used INTEGER DEFAULT 0, avg_completion_rate DECIMAL(5,2) DEFAULT 0, avg_score DECIMAL(5,2) DEFAULT 0, is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 8: MEDICAL IMAGES & SESSIONS TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Medical Images Table -- Stores medical images for AI-powered analysis and classification -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.medical_images ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, filename TEXT NOT NULL, file_type TEXT NOT NULL, file_size BIGINT NOT NULL, storage_path TEXT NOT NULL, width INTEGER, height INTEGER, format TEXT, category TEXT, image_type TEXT, body_region TEXT, analysis_status TEXT NOT NULL DEFAULT 'pending', analysis_text TEXT, findings TEXT[], clinical_impression TEXT, error_message TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), analyzed_at TIMESTAMPTZ, CONSTRAINT valid_analysis_status CHECK (analysis_status IN ('pending', 'analyzing', 'completed', 'failed')) ); CREATE OR REPLACE VIEW public.medical_image_stats AS SELECT user_id, COUNT(*) as total_images, COUNT(CASE WHEN analysis_status = 'completed' THEN 1 END) as analyzed_images, COUNT(CASE WHEN analysis_status = 'pending' THEN 1 END) as pending_images, COUNT(CASE WHEN analysis_status = 'failed' THEN 1 END) as failed_images, COUNT(DISTINCT category) as unique_categories, SUM(file_size) as total_storage_bytes, MAX(created_at) as last_upload_at FROM public.medical_images GROUP BY user_id; -- ----------------------------------------------------------------------------- -- Image Analysis Sessions Table -- Stores history of all image analyses with clinical context -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.image_analysis_sessions ( id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE, image_filename TEXT NOT NULL, analysis_result JSONB NOT NULL, context TEXT, image_preview TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); -- ============================================================================ -- SECTION 9: SUBSCRIPTION & PAYMENT TABLES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Subscriptions Table -- User subscription management -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.subscriptions ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, plan TEXT NOT NULL CHECK (plan IN ('free', 'student', 'pro', 'admin')), razorpay_subscription_id TEXT UNIQUE, status TEXT NOT NULL CHECK (status IN ('active', 'cancelled', 'expired')), current_period_start TIMESTAMPTZ, current_period_end TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ----------------------------------------------------------------------------- -- Payments Table -- Payment transaction records -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.payments ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE, subscription_id UUID REFERENCES public.subscriptions(id), razorpay_payment_id TEXT UNIQUE, amount INTEGER NOT NULL, currency TEXT NOT NULL DEFAULT 'INR', status TEXT NOT NULL CHECK (status IN ('success', 'failed', 'pending')), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 9: MODEL USAGE LOGGING -- ============================================================================ -- ----------------------------------------------------------------------------- -- Model Usage Logs Table -- Track all model API calls for monitoring, reporting, and cost analysis -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.model_usage_logs ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID REFERENCES public.users(id) ON DELETE SET NULL, provider TEXT NOT NULL, model TEXT NOT NULL, feature TEXT NOT NULL, success BOOLEAN NOT NULL DEFAULT false, tokens_used INTEGER NOT NULL DEFAULT 0, error TEXT, key_id UUID REFERENCES public.api_keys(id) ON DELETE SET NULL, was_fallback BOOLEAN NOT NULL DEFAULT false, attempt_number INTEGER NOT NULL DEFAULT 1, response_time_ms INTEGER, timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- ============================================================================ -- SECTION 10: CREATE ALL INDEXES -- ============================================================================ -- Core tables indexes CREATE INDEX IF NOT EXISTS idx_users_email ON public.users(email); CREATE INDEX IF NOT EXISTS idx_admin_allowlist_email ON public.admin_allowlist(email); CREATE INDEX IF NOT EXISTS idx_usage_counters_user_date ON public.usage_counters(user_id, date); CREATE INDEX IF NOT EXISTS idx_api_keys_provider_feature ON public.api_keys(provider, feature); -- Chat indexes CREATE INDEX IF NOT EXISTS idx_chat_sessions_user_id ON public.chat_sessions(user_id); CREATE INDEX IF NOT EXISTS idx_messages_session_created ON public.messages(session_id, created_at); -- Document indexes CREATE INDEX IF NOT EXISTS idx_documents_user_id ON public.documents(user_id); CREATE INDEX IF NOT EXISTS idx_documents_status ON public.documents(processing_status); CREATE INDEX IF NOT EXISTS idx_documents_feature ON public.documents(feature); CREATE INDEX IF NOT EXISTS idx_documents_expires_at ON public.documents(expires_at); CREATE INDEX IF NOT EXISTS idx_documents_file_hash ON public.documents(file_hash); CREATE INDEX IF NOT EXISTS idx_documents_processing_status ON public.documents(processing_status); CREATE INDEX IF NOT EXISTS idx_documents_user_feature ON public.documents(user_id, feature); CREATE INDEX IF NOT EXISTS idx_document_chunks_document_id ON public.document_chunks(document_id); CREATE INDEX IF NOT EXISTS idx_users_fallback_locks ON public.users USING GIN (fallback_locks); -- Medical images & sessions indexes CREATE INDEX IF NOT EXISTS idx_medical_images_user_id ON public.medical_images(user_id); CREATE INDEX IF NOT EXISTS idx_medical_images_category ON public.medical_images(category); CREATE INDEX IF NOT EXISTS idx_medical_images_analysis_status ON public.medical_images(analysis_status); CREATE INDEX IF NOT EXISTS idx_medical_images_created_at ON public.medical_images(created_at DESC); CREATE INDEX IF NOT EXISTS idx_medical_images_image_type ON public.medical_images(image_type); CREATE INDEX IF NOT EXISTS idx_medical_images_body_region ON public.medical_images(body_region); CREATE INDEX IF NOT EXISTS idx_medical_images_analysis_text ON public.medical_images USING gin(to_tsvector('english', COALESCE(analysis_text, ''))); CREATE INDEX IF NOT EXISTS idx_image_analysis_sessions_user_id ON public.image_analysis_sessions(user_id); -- RAG indexes CREATE INDEX IF NOT EXISTS idx_rag_logs_user_id ON public.rag_usage_logs(user_id); CREATE INDEX IF NOT EXISTS idx_rag_logs_feature ON public.rag_usage_logs(feature); CREATE INDEX IF NOT EXISTS idx_rag_logs_timestamp ON public.rag_usage_logs(timestamp DESC); CREATE INDEX IF NOT EXISTS idx_rag_logs_document_id ON public.rag_usage_logs(document_id); -- Study tools indexes CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_user_id ON public.study_tool_sessions(user_id); CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_feature ON public.study_tool_sessions(feature); CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_created_at ON public.study_tool_sessions(created_at DESC); CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_updated ON public.study_tool_sessions(updated_at DESC); CREATE INDEX IF NOT EXISTS idx_study_tool_sessions_user_feature ON public.study_tool_sessions(user_id, feature); CREATE INDEX IF NOT EXISTS idx_study_materials_session ON public.study_materials(session_id, created_at); CREATE INDEX IF NOT EXISTS idx_study_materials_session_id ON public.study_materials(session_id); CREATE INDEX IF NOT EXISTS idx_study_materials_feature ON public.study_materials(feature); CREATE INDEX IF NOT EXISTS idx_study_materials_created_at ON public.study_materials(created_at DESC); -- Enhanced study planner indexes CREATE INDEX IF NOT EXISTS idx_study_plan_entries_user_date ON public.study_plan_entries(user_id, scheduled_date); CREATE INDEX IF NOT EXISTS idx_study_plan_entries_status ON public.study_plan_entries(status); CREATE INDEX IF NOT EXISTS idx_study_plan_entries_user_status ON public.study_plan_entries(user_id, status); CREATE INDEX IF NOT EXISTS idx_study_goals_user_id ON public.study_goals(user_id); CREATE INDEX IF NOT EXISTS idx_study_goals_active ON public.study_goals(user_id, status) WHERE status = 'active'; CREATE INDEX IF NOT EXISTS idx_performance_metrics_user_date ON public.performance_metrics(user_id, metric_date); CREATE INDEX IF NOT EXISTS idx_ai_recommendations_user_pending ON public.ai_recommendations(user_id, status) WHERE status = 'pending'; CREATE INDEX IF NOT EXISTS idx_study_streaks_user_id ON public.study_streaks(user_id); -- Clinical reasoning indexes CREATE INDEX IF NOT EXISTS idx_clinical_cases_user_id ON public.clinical_cases(user_id); CREATE INDEX IF NOT EXISTS idx_clinical_cases_status ON public.clinical_cases(status); CREATE INDEX IF NOT EXISTS idx_clinical_cases_specialty ON public.clinical_cases(specialty); CREATE INDEX IF NOT EXISTS idx_clinical_cases_created_at ON public.clinical_cases(created_at DESC); CREATE INDEX IF NOT EXISTS idx_reasoning_steps_case_id ON public.clinical_reasoning_steps(case_id); CREATE INDEX IF NOT EXISTS idx_reasoning_steps_user_id ON public.clinical_reasoning_steps(user_id); CREATE INDEX IF NOT EXISTS idx_reasoning_steps_step_type ON public.clinical_reasoning_steps(step_type); CREATE INDEX IF NOT EXISTS idx_osce_scenarios_user_id ON public.osce_scenarios(user_id); CREATE INDEX IF NOT EXISTS idx_osce_scenarios_status ON public.osce_scenarios(status); CREATE INDEX IF NOT EXISTS idx_osce_scenarios_type ON public.osce_scenarios(scenario_type); CREATE INDEX IF NOT EXISTS idx_clinical_performance_user_id ON public.clinical_performance(user_id); CREATE INDEX IF NOT EXISTS idx_case_templates_specialty ON public.case_templates(specialty); CREATE INDEX IF NOT EXISTS idx_case_templates_difficulty ON public.case_templates(difficulty); -- Audit logs indexes CREATE INDEX IF NOT EXISTS idx_audit_logs_admin_created ON public.audit_logs(admin_id, created_at DESC); -- Model usage logs indexes CREATE INDEX IF NOT EXISTS idx_model_usage_logs_timestamp ON public.model_usage_logs(timestamp DESC); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_user_id ON public.model_usage_logs(user_id); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_provider ON public.model_usage_logs(provider); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_feature ON public.model_usage_logs(feature); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_success ON public.model_usage_logs(success); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_was_fallback ON public.model_usage_logs(was_fallback); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_key_id ON public.model_usage_logs(key_id); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_provider_feature ON public.model_usage_logs(provider, feature); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_timestamp_provider ON public.model_usage_logs(timestamp DESC, provider); CREATE INDEX IF NOT EXISTS idx_model_usage_logs_timestamp_feature ON public.model_usage_logs(timestamp DESC, feature); -- Vector similarity index for document chunks -- Note: ivfflat index requires data to exist. Using HNSW for 4096 dims CREATE INDEX IF NOT EXISTS idx_document_chunks_embedding ON public.document_chunks USING hnsw (embedding vector_cosine_ops); -- ============================================================================ -- SECTION 11: CREATE FUNCTIONS -- ============================================================================ -- ----------------------------------------------------------------------------- -- updated_at Trigger Function -- Automatically updates the updated_at column on row update -- ----------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -- ----------------------------------------------------------------------------- -- Admin Check Function -- Helper function to check if user is admin -- ----------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION is_admin() RETURNS BOOLEAN AS $$ BEGIN RETURN EXISTS ( SELECT 1 FROM public.admin_allowlist WHERE email = (SELECT email FROM public.users WHERE id = auth.uid()) ); END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- ----------------------------------------------------------------------------- -- Handle New User Function -- Auto-sync trigger for new Supabase Auth users -- ----------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS TRIGGER AS $$ BEGIN INSERT INTO public.users (id, email, name, plan, created_at, updated_at) VALUES ( NEW.id, NEW.email, COALESCE(NEW.raw_user_meta_data->>'name', NEW.email), 'free', NEW.created_at, NEW.updated_at ) ON CONFLICT (id) DO NOTHING; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- ----------------------------------------------------------------------------- -- Vector Similarity Search Function -- RPC function for vector similarity search on document chunks -- ----------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION match_document_chunks( query_embedding VECTOR(4096), match_count INT DEFAULT 5, filter_doc_ids UUID[] DEFAULT NULL ) RETURNS TABLE ( id UUID, document_id UUID, content TEXT, chunk_index INTEGER, similarity FLOAT ) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT document_chunks.id, document_chunks.document_id, document_chunks.content, document_chunks.chunk_index, 1 - (document_chunks.embedding <=> query_embedding) AS similarity FROM document_chunks WHERE (filter_doc_ids IS NULL OR document_chunks.document_id = ANY(filter_doc_ids)) AND document_chunks.embedding IS NOT NULL ORDER BY document_chunks.embedding <=> query_embedding LIMIT match_count; END; $$; -- ============================================================================ -- SECTION 12: CREATE TRIGGERS -- ============================================================================ -- Users table updated_at trigger DROP TRIGGER IF EXISTS update_users_updated_at ON public.users; CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Chat sessions updated_at trigger DROP TRIGGER IF EXISTS update_chat_sessions_updated_at ON public.chat_sessions; CREATE TRIGGER update_chat_sessions_updated_at BEFORE UPDATE ON public.chat_sessions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Study tool sessions updated_at trigger DROP TRIGGER IF EXISTS update_study_tool_sessions_updated_at ON public.study_tool_sessions; CREATE TRIGGER update_study_tool_sessions_updated_at BEFORE UPDATE ON public.study_tool_sessions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Clinical cases updated_at trigger DROP TRIGGER IF EXISTS update_clinical_cases_updated_at ON public.clinical_cases; CREATE TRIGGER update_clinical_cases_updated_at BEFORE UPDATE ON public.clinical_cases FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- OSCE scenarios updated_at trigger DROP TRIGGER IF EXISTS update_osce_scenarios_updated_at ON public.osce_scenarios; CREATE TRIGGER update_osce_scenarios_updated_at BEFORE UPDATE ON public.osce_scenarios FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Clinical performance updated_at trigger DROP TRIGGER IF EXISTS update_clinical_performance_updated_at ON public.clinical_performance; CREATE TRIGGER update_clinical_performance_updated_at BEFORE UPDATE ON public.clinical_performance FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Clinical rubrics updated_at trigger DROP TRIGGER IF EXISTS update_clinical_rubrics_updated_at ON public.clinical_rubrics; CREATE TRIGGER update_clinical_rubrics_updated_at BEFORE UPDATE ON public.clinical_rubrics FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Case templates updated_at trigger DROP TRIGGER IF EXISTS update_case_templates_updated_at ON public.case_templates; CREATE TRIGGER update_case_templates_updated_at BEFORE UPDATE ON public.case_templates FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Study plan entries updated_at trigger DROP TRIGGER IF EXISTS update_study_plan_entries_updated_at ON public.study_plan_entries; CREATE TRIGGER update_study_plan_entries_updated_at BEFORE UPDATE ON public.study_plan_entries FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Study goals updated_at trigger DROP TRIGGER IF EXISTS update_study_goals_updated_at ON public.study_goals; CREATE TRIGGER update_study_goals_updated_at BEFORE UPDATE ON public.study_goals FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Performance metrics updated_at trigger DROP TRIGGER IF EXISTS update_performance_metrics_updated_at ON public.performance_metrics; CREATE TRIGGER update_performance_metrics_updated_at BEFORE UPDATE ON public.performance_metrics FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Study streaks updated_at trigger DROP TRIGGER IF EXISTS update_study_streaks_updated_at ON public.study_streaks; CREATE TRIGGER update_study_streaks_updated_at BEFORE UPDATE ON public.study_streaks FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Study sessions updated_at trigger DROP TRIGGER IF EXISTS update_study_sessions_updated_at ON public.study_sessions; CREATE TRIGGER update_study_sessions_updated_at BEFORE UPDATE ON public.study_sessions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Subscriptions updated_at trigger DROP TRIGGER IF EXISTS update_subscriptions_updated_at ON public.subscriptions; CREATE TRIGGER update_subscriptions_updated_at BEFORE UPDATE ON public.subscriptions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Study templates updated_at trigger DROP TRIGGER IF EXISTS update_study_templates_updated_at ON public.study_templates; CREATE TRIGGER update_study_templates_updated_at BEFORE UPDATE ON public.study_templates FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Documents updated_at trigger DROP TRIGGER IF EXISTS update_documents_updated_at ON public.documents; CREATE TRIGGER update_documents_updated_at BEFORE UPDATE ON public.documents FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- API Keys updated_at trigger DROP TRIGGER IF EXISTS update_api_keys_updated_at ON public.api_keys; CREATE TRIGGER update_api_keys_updated_at BEFORE UPDATE ON public.api_keys FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Image Analysis Sessions updated_at trigger DROP TRIGGER IF EXISTS update_image_analysis_sessions_updated_at ON public.image_analysis_sessions; CREATE TRIGGER update_image_analysis_sessions_updated_at BEFORE UPDATE ON public.image_analysis_sessions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Auth user sync trigger 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(); -- ============================================================================ -- SECTION 13: ENABLE ROW LEVEL SECURITY (RLS) -- ============================================================================ ALTER TABLE public.users ENABLE ROW LEVEL SECURITY; ALTER TABLE public.admin_allowlist ENABLE ROW LEVEL SECURITY; ALTER TABLE public.usage_counters ENABLE ROW LEVEL SECURITY; ALTER TABLE public.api_keys ENABLE ROW LEVEL SECURITY; ALTER TABLE public.provider_health ENABLE ROW LEVEL SECURITY; ALTER TABLE public.system_flags ENABLE ROW LEVEL SECURITY; ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY; ALTER TABLE public.document_chunks ENABLE ROW LEVEL SECURITY; ALTER TABLE public.embeddings ENABLE ROW LEVEL SECURITY; ALTER TABLE public.chat_sessions ENABLE ROW LEVEL SECURITY; ALTER TABLE public.messages ENABLE ROW LEVEL SECURITY; ALTER TABLE public.study_sessions ENABLE ROW LEVEL SECURITY; ALTER TABLE public.study_tool_sessions ENABLE ROW LEVEL SECURITY; ALTER TABLE public.study_materials ENABLE ROW LEVEL SECURITY; ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY; ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY; ALTER TABLE public.audit_logs ENABLE ROW LEVEL SECURITY; ALTER TABLE public.model_usage_logs ENABLE ROW LEVEL SECURITY; ALTER TABLE public.rag_usage_logs ENABLE ROW LEVEL SECURITY; ALTER TABLE public.clinical_cases ENABLE ROW LEVEL SECURITY; ALTER TABLE public.clinical_reasoning_steps ENABLE ROW LEVEL SECURITY; ALTER TABLE public.osce_scenarios ENABLE ROW LEVEL SECURITY; ALTER TABLE public.clinical_performance ENABLE ROW LEVEL SECURITY; ALTER TABLE public.study_plan_entries ENABLE ROW LEVEL SECURITY; ALTER TABLE public.study_goals ENABLE ROW LEVEL SECURITY; ALTER TABLE public.performance_metrics ENABLE ROW LEVEL SECURITY; ALTER TABLE public.ai_recommendations ENABLE ROW LEVEL SECURITY; ALTER TABLE public.study_streaks ENABLE ROW LEVEL SECURITY; ALTER TABLE public.study_templates ENABLE ROW LEVEL SECURITY; ALTER TABLE public.medical_images ENABLE ROW LEVEL SECURITY; ALTER TABLE public.image_analysis_sessions ENABLE ROW LEVEL SECURITY; -- ============================================================================ -- SECTION 14: RLS POLICIES -- ============================================================================ -- ----------------------------------------------------------------------------- -- Users Table Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS users_select_own ON public.users; CREATE POLICY users_select_own ON public.users FOR SELECT USING (auth.uid() = id); DROP POLICY IF EXISTS users_select_admin ON public.users; CREATE POLICY users_select_admin ON public.users FOR SELECT USING (is_admin()); -- ----------------------------------------------------------------------------- -- Chat Sessions Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS chat_sessions_select_own ON public.chat_sessions; CREATE POLICY chat_sessions_select_own ON public.chat_sessions FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS chat_sessions_insert_own ON public.chat_sessions; CREATE POLICY chat_sessions_insert_own ON public.chat_sessions FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS chat_sessions_update_own ON public.chat_sessions; CREATE POLICY chat_sessions_update_own ON public.chat_sessions FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS chat_sessions_delete_own ON public.chat_sessions; CREATE POLICY chat_sessions_delete_own ON public.chat_sessions FOR DELETE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Messages Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS messages_select_own ON public.messages; CREATE POLICY messages_select_own ON public.messages FOR SELECT USING ( EXISTS (SELECT 1 FROM public.chat_sessions WHERE chat_sessions.id = messages.session_id AND chat_sessions.user_id = auth.uid()) ); DROP POLICY IF EXISTS messages_insert_own ON public.messages; CREATE POLICY messages_insert_own ON public.messages FOR INSERT WITH CHECK ( EXISTS (SELECT 1 FROM public.chat_sessions WHERE chat_sessions.id = messages.session_id AND chat_sessions.user_id = auth.uid()) ); -- ----------------------------------------------------------------------------- -- Documents Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS "Users can view own documents" ON public.documents; CREATE POLICY "Users can view own documents" ON public.documents FOR SELECT USING (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can insert own documents" ON public.documents; CREATE POLICY "Users can insert own documents" ON public.documents FOR INSERT WITH CHECK (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can update own documents" ON public.documents; CREATE POLICY "Users can update own documents" ON public.documents FOR UPDATE USING (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can delete own documents" ON public.documents; CREATE POLICY "Users can delete own documents" ON public.documents FOR DELETE USING (auth.uid() = user_id); -- ----------------------------------------------------------------------------- -- Document Chunks Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS "Users can view chunks of own documents" ON public.document_chunks; CREATE POLICY "Users can view chunks of own documents" ON public.document_chunks FOR SELECT USING ( EXISTS (SELECT 1 FROM public.documents WHERE documents.id = document_chunks.document_id AND documents.user_id = auth.uid()) ); DROP POLICY IF EXISTS "System can insert chunks" ON public.document_chunks; CREATE POLICY "System can insert chunks" ON public.document_chunks FOR INSERT WITH CHECK (true); DROP POLICY IF EXISTS "System can delete chunks" ON public.document_chunks; CREATE POLICY "System can delete chunks" ON public.document_chunks FOR DELETE USING (true); -- ----------------------------------------------------------------------------- -- Study Tool Sessions Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS study_tool_sessions_select_policy ON public.study_tool_sessions; CREATE POLICY study_tool_sessions_select_policy ON public.study_tool_sessions FOR SELECT USING (auth.uid() = user_id); DROP POLICY IF EXISTS study_tool_sessions_insert_policy ON public.study_tool_sessions; CREATE POLICY study_tool_sessions_insert_policy ON public.study_tool_sessions FOR INSERT WITH CHECK (auth.uid() = user_id); DROP POLICY IF EXISTS study_tool_sessions_update_policy ON public.study_tool_sessions; CREATE POLICY study_tool_sessions_update_policy ON public.study_tool_sessions FOR UPDATE USING (auth.uid() = user_id); DROP POLICY IF EXISTS study_tool_sessions_delete_policy ON public.study_tool_sessions; CREATE POLICY study_tool_sessions_delete_policy ON public.study_tool_sessions FOR DELETE USING (auth.uid() = user_id); -- ----------------------------------------------------------------------------- -- Study Materials Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS study_tool_materials_select_policy ON public.study_materials; CREATE POLICY study_tool_materials_select_policy ON public.study_materials FOR SELECT USING ( EXISTS (SELECT 1 FROM public.study_tool_sessions WHERE study_tool_sessions.id = study_materials.session_id AND study_tool_sessions.user_id = auth.uid()) ); DROP POLICY IF EXISTS study_tool_materials_insert_policy ON public.study_materials; CREATE POLICY study_tool_materials_insert_policy ON public.study_materials FOR INSERT WITH CHECK ( EXISTS (SELECT 1 FROM public.study_tool_sessions WHERE study_tool_sessions.id = study_materials.session_id AND study_tool_sessions.user_id = auth.uid()) ); DROP POLICY IF EXISTS study_tool_materials_delete_policy ON public.study_materials; CREATE POLICY study_tool_materials_delete_policy ON public.study_materials FOR DELETE USING ( EXISTS (SELECT 1 FROM public.study_tool_sessions WHERE study_tool_sessions.id = study_materials.session_id AND study_tool_sessions.user_id = auth.uid()) ); -- ----------------------------------------------------------------------------- -- Usage Counters Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS usage_counters_select_own ON public.usage_counters; CREATE POLICY usage_counters_select_own ON public.usage_counters FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS usage_counters_insert_own ON public.usage_counters; CREATE POLICY usage_counters_insert_own ON public.usage_counters FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS usage_counters_update_own ON public.usage_counters; CREATE POLICY usage_counters_update_own ON public.usage_counters FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS usage_counters_admin ON public.usage_counters; CREATE POLICY usage_counters_admin ON public.usage_counters FOR ALL USING (is_admin()); -- ----------------------------------------------------------------------------- -- Study Sessions Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS study_sessions_select_own ON public.study_sessions; CREATE POLICY study_sessions_select_own ON public.study_sessions FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_sessions_insert_own ON public.study_sessions; CREATE POLICY study_sessions_insert_own ON public.study_sessions FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS study_sessions_update_own ON public.study_sessions; CREATE POLICY study_sessions_update_own ON public.study_sessions FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_sessions_delete_own ON public.study_sessions; CREATE POLICY study_sessions_delete_own ON public.study_sessions FOR DELETE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Study Plan Entries Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS study_plan_entries_select_own ON public.study_plan_entries; CREATE POLICY study_plan_entries_select_own ON public.study_plan_entries FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_plan_entries_insert_own ON public.study_plan_entries; CREATE POLICY study_plan_entries_insert_own ON public.study_plan_entries FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS study_plan_entries_update_own ON public.study_plan_entries; CREATE POLICY study_plan_entries_update_own ON public.study_plan_entries FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_plan_entries_delete_own ON public.study_plan_entries; CREATE POLICY study_plan_entries_delete_own ON public.study_plan_entries FOR DELETE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Study Goals Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS study_goals_select_own ON public.study_goals; CREATE POLICY study_goals_select_own ON public.study_goals FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_goals_insert_own ON public.study_goals; CREATE POLICY study_goals_insert_own ON public.study_goals FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS study_goals_update_own ON public.study_goals; CREATE POLICY study_goals_update_own ON public.study_goals FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_goals_delete_own ON public.study_goals; CREATE POLICY study_goals_delete_own ON public.study_goals FOR DELETE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Performance Metrics Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS performance_metrics_select_own ON public.performance_metrics; CREATE POLICY performance_metrics_select_own ON public.performance_metrics FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS performance_metrics_insert_own ON public.performance_metrics; CREATE POLICY performance_metrics_insert_own ON public.performance_metrics FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS performance_metrics_update_own ON public.performance_metrics; CREATE POLICY performance_metrics_update_own ON public.performance_metrics FOR UPDATE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- AI Recommendations Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS ai_recommendations_select_own ON public.ai_recommendations; CREATE POLICY ai_recommendations_select_own ON public.ai_recommendations FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS ai_recommendations_update_own ON public.ai_recommendations; CREATE POLICY ai_recommendations_update_own ON public.ai_recommendations FOR UPDATE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Study Streaks Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS study_streaks_select_own ON public.study_streaks; CREATE POLICY study_streaks_select_own ON public.study_streaks FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_streaks_insert_own ON public.study_streaks; CREATE POLICY study_streaks_insert_own ON public.study_streaks FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS study_streaks_update_own ON public.study_streaks; CREATE POLICY study_streaks_update_own ON public.study_streaks FOR UPDATE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Study Templates Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS study_templates_select ON public.study_templates; CREATE POLICY study_templates_select ON public.study_templates FOR SELECT USING ( user_id = auth.uid() OR is_public = true ); DROP POLICY IF EXISTS study_templates_insert_own ON public.study_templates; CREATE POLICY study_templates_insert_own ON public.study_templates FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS study_templates_update_own ON public.study_templates; CREATE POLICY study_templates_update_own ON public.study_templates FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS study_templates_delete_own ON public.study_templates; CREATE POLICY study_templates_delete_own ON public.study_templates FOR DELETE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Clinical Cases Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS clinical_cases_select_own ON public.clinical_cases; CREATE POLICY clinical_cases_select_own ON public.clinical_cases FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS clinical_cases_insert_own ON public.clinical_cases; CREATE POLICY clinical_cases_insert_own ON public.clinical_cases FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS clinical_cases_update_own ON public.clinical_cases; CREATE POLICY clinical_cases_update_own ON public.clinical_cases FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS clinical_cases_delete_own ON public.clinical_cases; CREATE POLICY clinical_cases_delete_own ON public.clinical_cases FOR DELETE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Clinical Reasoning Steps Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS clinical_reasoning_steps_select_own ON public.clinical_reasoning_steps; CREATE POLICY clinical_reasoning_steps_select_own ON public.clinical_reasoning_steps FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS clinical_reasoning_steps_insert_own ON public.clinical_reasoning_steps; CREATE POLICY clinical_reasoning_steps_insert_own ON public.clinical_reasoning_steps FOR INSERT WITH CHECK (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- OSCE Scenarios Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS osce_scenarios_select_own ON public.osce_scenarios; CREATE POLICY osce_scenarios_select_own ON public.osce_scenarios FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS osce_scenarios_insert_own ON public.osce_scenarios; CREATE POLICY osce_scenarios_insert_own ON public.osce_scenarios FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS osce_scenarios_update_own ON public.osce_scenarios; CREATE POLICY osce_scenarios_update_own ON public.osce_scenarios FOR UPDATE USING (user_id = auth.uid()); DROP POLICY IF EXISTS osce_scenarios_delete_own ON public.osce_scenarios; CREATE POLICY osce_scenarios_delete_own ON public.osce_scenarios FOR DELETE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Clinical Performance Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS clinical_performance_select_own ON public.clinical_performance; CREATE POLICY clinical_performance_select_own ON public.clinical_performance FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS clinical_performance_insert_own ON public.clinical_performance; CREATE POLICY clinical_performance_insert_own ON public.clinical_performance FOR INSERT WITH CHECK (user_id = auth.uid()); DROP POLICY IF EXISTS clinical_performance_update_own ON public.clinical_performance; CREATE POLICY clinical_performance_update_own ON public.clinical_performance FOR UPDATE USING (user_id = auth.uid()); -- ----------------------------------------------------------------------------- -- Subscriptions Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS subscriptions_select_own ON public.subscriptions; CREATE POLICY subscriptions_select_own ON public.subscriptions FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS subscriptions_admin ON public.subscriptions; CREATE POLICY subscriptions_admin ON public.subscriptions FOR ALL USING (is_admin()); -- ----------------------------------------------------------------------------- -- Payments Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS payments_select_own ON public.payments; CREATE POLICY payments_select_own ON public.payments FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS payments_admin ON public.payments; CREATE POLICY payments_admin ON public.payments FOR ALL USING (is_admin()); -- ----------------------------------------------------------------------------- -- RAG Usage Logs Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS rag_usage_logs_select_own ON public.rag_usage_logs; CREATE POLICY rag_usage_logs_select_own ON public.rag_usage_logs FOR SELECT USING (user_id = auth.uid()); DROP POLICY IF EXISTS rag_usage_logs_admin ON public.rag_usage_logs; CREATE POLICY rag_usage_logs_admin ON public.rag_usage_logs FOR ALL USING (is_admin()); -- ----------------------------------------------------------------------------- -- System Flags & Provider Health Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS system_flags_select_all ON public.system_flags; CREATE POLICY system_flags_select_all ON public.system_flags FOR SELECT USING (true); DROP POLICY IF EXISTS system_flags_modify_admin ON public.system_flags; CREATE POLICY system_flags_modify_admin ON public.system_flags FOR ALL USING (is_admin()); DROP POLICY IF EXISTS provider_health_admin ON public.provider_health; CREATE POLICY provider_health_admin ON public.provider_health FOR ALL USING (is_admin()); -- ----------------------------------------------------------------------------- -- Embeddings Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS embeddings_select_own ON public.embeddings; CREATE POLICY embeddings_select_own ON public.embeddings FOR SELECT USING ( EXISTS (SELECT 1 FROM public.documents WHERE documents.id = embeddings.document_id AND documents.user_id = auth.uid()) ); -- ----------------------------------------------------------------------------- -- Admin-only Tables Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS api_keys_admin ON public.api_keys; CREATE POLICY api_keys_admin ON public.api_keys FOR ALL USING (is_admin()); DROP POLICY IF EXISTS admin_allowlist_admin ON public.admin_allowlist; CREATE POLICY admin_allowlist_admin ON public.admin_allowlist FOR ALL USING (is_admin()); DROP POLICY IF EXISTS audit_logs_insert_admin ON public.audit_logs; CREATE POLICY audit_logs_insert_admin ON public.audit_logs FOR INSERT WITH CHECK (is_admin()); DROP POLICY IF EXISTS audit_logs_select_admin ON public.audit_logs; CREATE POLICY audit_logs_select_admin ON public.audit_logs FOR SELECT USING (is_admin()); -- ----------------------------------------------------------------------------- -- Medical Images & Sessions Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS "Users can view their own medical images" ON public.medical_images; CREATE POLICY "Users can view their own medical images" ON public.medical_images FOR SELECT USING (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can insert their own medical images" ON public.medical_images; CREATE POLICY "Users can insert their own medical images" ON public.medical_images FOR INSERT WITH CHECK (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can update their own medical images" ON public.medical_images; CREATE POLICY "Users can update their own medical images" ON public.medical_images FOR UPDATE USING (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can delete their own medical images" ON public.medical_images; CREATE POLICY "Users can delete their own medical images" ON public.medical_images FOR DELETE USING (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can view their own sessions" ON public.image_analysis_sessions; CREATE POLICY "Users can view their own sessions" ON public.image_analysis_sessions FOR SELECT USING (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can insert their own sessions" ON public.image_analysis_sessions; CREATE POLICY "Users can insert their own sessions" ON public.image_analysis_sessions FOR INSERT WITH CHECK (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can update their own sessions" ON public.image_analysis_sessions; CREATE POLICY "Users can update their own sessions" ON public.image_analysis_sessions FOR UPDATE USING (auth.uid() = user_id); DROP POLICY IF EXISTS "Users can delete their own sessions" ON public.image_analysis_sessions; CREATE POLICY "Users can delete their own sessions" ON public.image_analysis_sessions FOR DELETE USING (auth.uid() = user_id); -- ----------------------------------------------------------------------------- -- Model Usage Logs Policies -- ----------------------------------------------------------------------------- DROP POLICY IF EXISTS "Admins can view all model usage logs" ON public.model_usage_logs; CREATE POLICY "Admins can view all model usage logs" ON public.model_usage_logs FOR SELECT TO authenticated USING ( EXISTS (SELECT 1 FROM public.users WHERE users.id = auth.uid() AND users.role IN ('super_admin', 'admin')) ); DROP POLICY IF EXISTS "Users can view their own model usage logs" ON public.model_usage_logs; CREATE POLICY "Users can view their own model usage logs" ON public.model_usage_logs FOR SELECT TO authenticated USING (user_id = auth.uid()); DROP POLICY IF EXISTS "Service role can insert model usage logs" ON public.model_usage_logs; CREATE POLICY "Service role can insert model usage logs" ON public.model_usage_logs FOR INSERT WITH CHECK (true); -- ----------------------------------------------------------------------------- -- Service Role Bypass Policies (for backend operations) -- These allow the service role to perform operations on behalf of users -- ----------------------------------------------------------------------------- -- Note: Service role (SUPABASE_SERVICE_KEY) bypasses RLS by default -- These policies are for additional safety when using user tokens with elevated privileges -- ============================================================================ -- SECTION 15: INSERT DEFAULT DATA -- ============================================================================ -- ----------------------------------------------------------------------------- -- Default Clinical Rubrics -- ----------------------------------------------------------------------------- INSERT INTO public.clinical_rubrics (rubric_name, rubric_type, criteria) VALUES ( 'Clinical Reasoning Rubric', 'clinical_reasoning', '[ {"name": "Data Gathering", "description": "Systematic collection of relevant clinical information", "max_score": 5, "weight": 20, "levels": [ {"score": 1, "description": "Minimal data gathering, missed critical information"}, {"score": 2, "description": "Basic data gathering with significant gaps"}, {"score": 3, "description": "Adequate data gathering, some gaps"}, {"score": 4, "description": "Thorough data gathering with minor gaps"}, {"score": 5, "description": "Comprehensive and systematic data gathering"} ]}, {"name": "Problem Representation", "description": "Accurate synthesis of clinical findings", "max_score": 5, "weight": 20, "levels": [ {"score": 1, "description": "Inaccurate or missing problem representation"}, {"score": 2, "description": "Partial synthesis with major inaccuracies"}, {"score": 3, "description": "Adequate synthesis with minor inaccuracies"}, {"score": 4, "description": "Good synthesis capturing key elements"}, {"score": 5, "description": "Excellent synthesis integrating all relevant findings"} ]}, {"name": "Differential Diagnosis", "description": "Appropriate generation and prioritization of differentials", "max_score": 5, "weight": 25, "levels": [ {"score": 1, "description": "Inappropriate or missing differentials"}, {"score": 2, "description": "Limited differentials, poor prioritization"}, {"score": 3, "description": "Reasonable differentials with some prioritization issues"}, {"score": 4, "description": "Good differential list with appropriate prioritization"}, {"score": 5, "description": "Comprehensive differentials with excellent reasoning"} ]}, {"name": "Diagnostic Reasoning", "description": "Logical justification for diagnostic decisions", "max_score": 5, "weight": 20, "levels": [ {"score": 1, "description": "No logical reasoning demonstrated"}, {"score": 2, "description": "Weak reasoning with significant flaws"}, {"score": 3, "description": "Adequate reasoning with minor gaps"}, {"score": 4, "description": "Strong reasoning well-supported by evidence"}, {"score": 5, "description": "Excellent reasoning demonstrating clinical expertise"} ]}, {"name": "Management Planning", "description": "Appropriate and comprehensive management approach", "max_score": 5, "weight": 15, "levels": [ {"score": 1, "description": "Inappropriate or dangerous management"}, {"score": 2, "description": "Basic management with significant gaps"}, {"score": 3, "description": "Adequate management plan"}, {"score": 4, "description": "Good comprehensive management"}, {"score": 5, "description": "Excellent evidence-based management plan"} ]} ]' ), ( 'OSCE Communication Rubric', 'osce', '[ {"name": "Introduction & Rapport", "description": "Professional introduction and establishing rapport", "max_score": 5, "weight": 15, "levels": [ {"score": 1, "description": "No introduction, poor rapport"}, {"score": 2, "description": "Minimal introduction, limited rapport"}, {"score": 3, "description": "Adequate introduction and rapport"}, {"score": 4, "description": "Good professional introduction, effective rapport"}, {"score": 5, "description": "Excellent introduction with outstanding rapport"} ]}, {"name": "Communication Clarity", "description": "Clear, appropriate language for patient understanding", "max_score": 5, "weight": 20, "levels": [ {"score": 1, "description": "Unclear communication, excessive jargon"}, {"score": 2, "description": "Often unclear or inappropriate language"}, {"score": 3, "description": "Generally clear communication"}, {"score": 4, "description": "Clear and appropriate communication"}, {"score": 5, "description": "Excellent clarity adapting to patient level"} ]}, {"name": "Active Listening", "description": "Demonstrating attention and understanding", "max_score": 5, "weight": 15, "levels": [ {"score": 1, "description": "No evidence of listening"}, {"score": 2, "description": "Poor listening, frequent interruptions"}, {"score": 3, "description": "Adequate listening"}, {"score": 4, "description": "Good active listening with appropriate responses"}, {"score": 5, "description": "Excellent listening with empathetic responses"} ]}, {"name": "Clinical Competence", "description": "Appropriate clinical approach and knowledge", "max_score": 5, "weight": 30, "levels": [ {"score": 1, "description": "Unsafe or inappropriate clinical approach"}, {"score": 2, "description": "Basic approach with significant gaps"}, {"score": 3, "description": "Adequate clinical competence"}, {"score": 4, "description": "Good clinical competence and systematic approach"}, {"score": 5, "description": "Excellent clinical expertise demonstrated"} ]}, {"name": "Time Management", "description": "Efficient use of allocated time", "max_score": 5, "weight": 10, "levels": [ {"score": 1, "description": "Severe time management issues"}, {"score": 2, "description": "Poor time management"}, {"score": 3, "description": "Adequate time management"}, {"score": 4, "description": "Good time efficiency"}, {"score": 5, "description": "Excellent time management"} ]}, {"name": "Professionalism", "description": "Professional behavior and ethical conduct", "max_score": 5, "weight": 10, "levels": [ {"score": 1, "description": "Unprofessional conduct"}, {"score": 2, "description": "Lapses in professionalism"}, {"score": 3, "description": "Adequate professionalism"}, {"score": 4, "description": "Good professional behavior"}, {"score": 5, "description": "Exemplary professionalism throughout"} ]} ]' ) ON CONFLICT DO NOTHING; -- ============================================================================ -- SECTION 16: ADD TABLE COMMENTS -- ============================================================================ COMMENT ON TABLE public.users IS 'Core user table for all platform users'; COMMENT ON TABLE public.admin_allowlist IS 'Controls which users have admin access'; COMMENT ON TABLE public.usage_counters IS 'Tracks daily usage metrics per user'; COMMENT ON TABLE public.api_keys IS 'Stores encrypted API keys for various providers'; COMMENT ON TABLE public.provider_health IS 'Tracks health status of API providers'; COMMENT ON TABLE public.system_flags IS 'Stores system-wide configuration flags'; COMMENT ON TABLE public.audit_logs IS 'Tracks all admin actions for compliance'; COMMENT ON TABLE public.chat_sessions IS 'Stores user chat sessions'; COMMENT ON TABLE public.messages IS 'Stores individual messages in chat sessions'; COMMENT ON TABLE public.documents IS 'Stores uploaded document metadata'; COMMENT ON TABLE public.document_chunks IS 'Stores text chunks from documents for RAG'; COMMENT ON TABLE public.embeddings IS 'Stores document embeddings for vector search'; COMMENT ON TABLE public.rag_usage_logs IS 'Tracks RAG usage for monitoring and analytics'; COMMENT ON TABLE public.study_tool_sessions IS 'Independent sessions for study tools (flashcards, MCQs, etc.)'; COMMENT ON TABLE public.study_materials IS 'Generated study materials linked to sessions'; COMMENT ON TABLE public.study_sessions IS 'Basic study session scheduling'; COMMENT ON TABLE public.study_plan_entries IS 'Core table for storing individual study plan items'; COMMENT ON TABLE public.study_goals IS 'Monthly/Weekly goals and milestones'; COMMENT ON TABLE public.performance_metrics IS 'Track daily performance for analytics and AI recommendations'; COMMENT ON TABLE public.ai_recommendations IS 'Store AI-generated study suggestions'; COMMENT ON TABLE public.study_streaks IS 'Track user study streaks'; COMMENT ON TABLE public.study_templates IS 'Reusable study plan templates'; COMMENT ON TABLE public.clinical_cases IS 'Stores structured patient cases with progressive information disclosure'; COMMENT ON TABLE public.clinical_reasoning_steps IS 'Tracks each step of users reasoning process'; COMMENT ON TABLE public.osce_scenarios IS 'Structured OSCE examination scenarios'; COMMENT ON TABLE public.clinical_performance IS 'Aggregated performance metrics for each user'; COMMENT ON TABLE public.clinical_rubrics IS 'Evaluation rubrics for scoring'; COMMENT ON TABLE public.case_templates IS 'Pre-built case templates for quick generation'; COMMENT ON TABLE public.subscriptions IS 'User subscription management'; COMMENT ON TABLE public.payments IS 'Payment transaction records'; COMMENT ON TABLE public.model_usage_logs IS 'Logs all model API calls for monitoring and cost tracking'; COMMENT ON COLUMN public.usage_counters.chat_uploads_per_day IS 'Number of documents uploaded for chat feature today'; COMMENT ON COLUMN public.usage_counters.mcq_uploads_per_day IS 'Number of documents uploaded for MCQ feature today'; COMMENT ON COLUMN public.usage_counters.flashcard_uploads_per_day IS 'Number of documents uploaded for flashcard feature today'; COMMENT ON COLUMN public.usage_counters.explain_uploads_per_day IS 'Number of documents uploaded for explain feature today'; COMMENT ON COLUMN public.usage_counters.highyield_uploads_per_day IS 'Number of documents uploaded for high yield feature today'; COMMENT ON COLUMN public.rag_usage_logs.grounding_score IS 'Score indicating how well the response was grounded in document context (0-1)'; COMMENT ON COLUMN public.model_usage_logs.provider IS 'Provider name (openrouter, huggingface, etc.)'; COMMENT ON COLUMN public.model_usage_logs.model IS 'Specific model used (e.g., claude-sonnet-4.5, meditron-7b)'; COMMENT ON COLUMN public.model_usage_logs.feature IS 'Feature that triggered the call (chat, flashcard, etc.)'; COMMENT ON COLUMN public.model_usage_logs.was_fallback IS 'True if this was a fallback attempt after primary key failed'; COMMENT ON COLUMN public.model_usage_logs.attempt_number IS 'Attempt number in the fallback chain (1 = first try)'; COMMENT ON COLUMN public.model_usage_logs.key_id IS 'API key ID that was used for this call'; COMMENT ON FUNCTION match_document_chunks IS 'Vector similarity search for document chunks using cosine distance'; -- ============================================================================ -- SECTION 17: VERIFICATION -- ============================================================================ -- Show all created tables SELECT 'Tables created successfully!' as status, (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public') as table_count; -- ============================================================================ -- COMPLETE DATABASE SCHEMA - END -- ============================================================================