Hugging Face's logo

Spaces:
HydraBolt
/
SanadLLM
Running

App Files Community
SanadLLM / sql /create_tables.sql
Hydra-Bolt
added
8de5b21
Raw History Blame Contribute Delete
8.42 kB
-- Supabase Database Schema for SanadCheck LLM API
-- Execute these scripts in your Supabase SQL Editor
-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Create custom user roles enum
CREATE TYPE user_role AS ENUM ('user', 'admin', 'researcher');
-- Create users table (extends Supabase auth.users)
CREATE TABLE IF NOT EXISTS public.users (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
email TEXT UNIQUE NOT NULL,
username TEXT UNIQUE,
full_name TEXT,
role user_role DEFAULT 'user'::user_role,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_login TIMESTAMP WITH TIME ZONE,
-- Constraints
CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT username_length CHECK (length(username) >= 3 AND length(username) <= 50),
CONSTRAINT full_name_length CHECK (length(full_name) <= 100)
);
-- Create user_sessions table for JWT session management
CREATE TABLE IF NOT EXISTS public.user_sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
access_token TEXT NOT NULL,
refresh_token TEXT,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_used TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
ended_at TIMESTAMP WITH TIME ZONE,
user_agent TEXT,
ip_address INET,
-- Indexes for performance
CONSTRAINT unique_access_token UNIQUE (access_token)
);
-- Create narrator_extractions table for storing extraction results
CREATE TABLE IF NOT EXISTS public.narrator_extractions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
session_id UUID REFERENCES public.user_sessions(id) ON DELETE SET NULL,
hadith_text TEXT NOT NULL,
extracted_narrators TEXT[] NOT NULL DEFAULT '{}',
sanad_chain TEXT NOT NULL,
success BOOLEAN NOT NULL DEFAULT FALSE,
error_message TEXT,
processing_time_ms INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
ip_address INET,
-- Constraints
CONSTRAINT hadith_text_not_empty CHECK (length(trim(hadith_text)) > 0),
CONSTRAINT processing_time_positive CHECK (processing_time_ms IS NULL OR processing_time_ms >= 0)
);
-- Create narrator_analyses table for storing individual narrator analysis results
CREATE TABLE IF NOT EXISTS public.narrator_analyses (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
extraction_id UUID REFERENCES public.narrator_extractions(id) ON DELETE SET NULL,
narrator_name TEXT NOT NULL,
reliability_grade TEXT NOT NULL,
confidence_level TEXT NOT NULL,
reasoning TEXT NOT NULL,
scholarly_consensus TEXT NOT NULL,
known_issues TEXT,
biographical_info TEXT NOT NULL,
recommendation TEXT NOT NULL,
success BOOLEAN NOT NULL DEFAULT FALSE,
error_message TEXT,
processing_time_ms INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
-- Constraints
CONSTRAINT narrator_name_not_empty CHECK (length(trim(narrator_name)) > 0),
CONSTRAINT reliability_grade_valid CHECK (reliability_grade IN ('Thiqah', 'Saduq', 'Da''if', 'Matruk', 'Majhul')),
CONSTRAINT confidence_level_valid CHECK (confidence_level IN ('High', 'Medium', 'Low')),
CONSTRAINT processing_time_positive CHECK (processing_time_ms IS NULL OR processing_time_ms >= 0)
);
-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON public.user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_access_token ON public.user_sessions(access_token);
CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_at ON public.user_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_user_sessions_active ON public.user_sessions(user_id) WHERE ended_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_narrator_extractions_user_id ON public.narrator_extractions(user_id);
CREATE INDEX IF NOT EXISTS idx_narrator_extractions_created_at ON public.narrator_extractions(created_at);
CREATE INDEX IF NOT EXISTS idx_narrator_extractions_success ON public.narrator_extractions(success);
CREATE INDEX IF NOT EXISTS idx_narrator_analyses_user_id ON public.narrator_analyses(user_id);
CREATE INDEX IF NOT EXISTS idx_narrator_analyses_extraction_id ON public.narrator_analyses(extraction_id);
CREATE INDEX IF NOT EXISTS idx_narrator_analyses_narrator_name ON public.narrator_analyses(narrator_name);
CREATE INDEX IF NOT EXISTS idx_narrator_analyses_created_at ON public.narrator_analyses(created_at);
-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
-- Add updated_at trigger to users table
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON public.users
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at_column();
-- Row Level Security (RLS) Policies
-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.narrator_extractions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.narrator_analyses ENABLE ROW LEVEL SECURITY;
-- Users table policies
CREATE POLICY "Users can view own profile" ON public.users
FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own profile" ON public.users
FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "Service role can manage all users" ON public.users
FOR ALL USING (auth.role() = 'service_role');
-- User sessions policies
CREATE POLICY "Users can view own sessions" ON public.user_sessions
FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Service role can manage all sessions" ON public.user_sessions
FOR ALL USING (auth.role() = 'service_role');
-- Narrator extractions policies
CREATE POLICY "Users can view own extractions" ON public.narrator_extractions
FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own extractions" ON public.narrator_extractions
FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Service role can manage all extractions" ON public.narrator_extractions
FOR ALL USING (auth.role() = 'service_role');
-- Narrator analyses policies
CREATE POLICY "Users can view own analyses" ON public.narrator_analyses
FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own analyses" ON public.narrator_analyses
FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Service role can manage all analyses" ON public.narrator_analyses
FOR ALL USING (auth.role() = 'service_role');
-- Grant necessary permissions
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO anon, authenticated;
-- Create a function to automatically create user profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.users (id, email, username, full_name)
VALUES (
NEW.id,
NEW.email,
COALESCE(NEW.raw_user_meta_data->>'username', NULL),
COALESCE(NEW.raw_user_meta_data->>'full_name', NULL)
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Create trigger to run the function on new user signup
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
-- Create a view for user analytics
CREATE VIEW public.user_analytics AS
SELECT
u.id,
u.email,
u.role,
u.created_at,
u.last_login,
COUNT(ne.id) as total_extractions,
COUNT(na.id) as total_analyses,
COUNT(us.id) as total_sessions
FROM public.users u
LEFT JOIN public.narrator_extractions ne ON u.id = ne.user_id
LEFT JOIN public.narrator_analyses na ON u.id = na.user_id
LEFT JOIN public.user_sessions us ON u.id = us.user_id
GROUP BY u.id, u.email, u.role, u.created_at, u.last_login;
-- Grant select on the view
GRANT SELECT ON public.user_analytics TO authenticated;