db/sql/supa.sql

-- WARNING: This schema is for context only and is not meant to be run.
-- Table order and constraints may not be valid for execution.

CREATE TABLE public.api_logs (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  endpoint character varying NOT NULL,
  method character varying NOT NULL,
  user_id uuid,
  status_code integer,
  response_time double precision,
  error_message text,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT api_logs_pkey PRIMARY KEY (id),
  CONSTRAINT api_logs_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
);
CREATE TABLE public.document_chunks (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  document_id uuid NOT NULL,
  chunk_index integer NOT NULL,
  content text NOT NULL,
  page_number integer,
  section_title character varying,
  char_count integer,
  word_count integer,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT document_chunks_pkey PRIMARY KEY (id),
  CONSTRAINT document_chunks_document_id_fkey FOREIGN KEY (document_id) REFERENCES public.documents(id)
);
CREATE TABLE public.documents (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  filename character varying NOT NULL,
  original_filename character varying NOT NULL,
  file_path character varying NOT NULL,
  file_size integer,
  mime_type character varying DEFAULT 'application/pdf'::character varying,
  page_count integer,
  language character varying DEFAULT 'fr'::character varying,
  document_type character varying,
  raw_text text,
  processed_at timestamp with time zone,
  processing_status character varying DEFAULT 'pending'::character varying,
  processing_error text,
  tags jsonb DEFAULT '[]'::jsonb,
  is_favorite boolean DEFAULT false,
  is_archived boolean DEFAULT false,
  uploaded_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  contenu text,
  CONSTRAINT documents_pkey PRIMARY KEY (id),
  CONSTRAINT documents_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
);
CREATE TABLE public.embeddings (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  document_id uuid NOT NULL,
  chunk_id uuid NOT NULL UNIQUE,
  vector jsonb NOT NULL,
  vector_dimension integer DEFAULT 384,
  model_name character varying DEFAULT 'all-MiniLM-L6-v2'::character varying,
  model_version character varying,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT embeddings_pkey PRIMARY KEY (id),
  CONSTRAINT embeddings_document_id_fkey FOREIGN KEY (document_id) REFERENCES public.documents(id),
  CONSTRAINT embeddings_chunk_id_fkey FOREIGN KEY (chunk_id) REFERENCES public.document_chunks(id)
);
CREATE TABLE public.faiss_indexes (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  index_name character varying NOT NULL UNIQUE,
  index_path character varying NOT NULL,
  index_type character varying DEFAULT 'IndexFlatIP'::character varying,
  dimension integer NOT NULL,
  total_vectors integer DEFAULT 0,
  model_name character varying,
  last_updated timestamp with time zone DEFAULT now(),
  update_count integer DEFAULT 0,
  is_active boolean DEFAULT true,
  is_corrupted boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT faiss_indexes_pkey PRIMARY KEY (id)
);
CREATE TABLE public.query_history (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  document_id uuid,
  query text NOT NULL,
  response text NOT NULL,
  retrieved_chunks jsonb,
  similarity_scores jsonb,
  context_used text,
  response_time double precision,
  faithfulness_score double precision,
  relevance_score double precision,
  model_name character varying,
  model_version character varying,
  user_rating integer CHECK (user_rating >= 1 AND user_rating <= 5),
  user_feedback text,
  is_helpful boolean,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT query_history_pkey PRIMARY KEY (id),
  CONSTRAINT query_history_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id),
  CONSTRAINT query_history_document_id_fkey FOREIGN KEY (document_id) REFERENCES public.documents(id)
);
CREATE TABLE public.search_history (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  search_query text NOT NULL,
  search_type character varying,
  results_count integer,
  results_ids jsonb,
  top_score double precision,
  filters_applied jsonb,
  response_time double precision,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT search_history_pkey PRIMARY KEY (id),
  CONSTRAINT search_history_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
);
CREATE TABLE public.summaries (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  document_id uuid NOT NULL,
  summary_type character varying NOT NULL,
  summary_level character varying DEFAULT 'medium'::character varying,
  content text NOT NULL,
  content_json jsonb,
  rouge_1_score double precision,
  rouge_2_score double precision,
  rouge_l_score double precision,
  compression_ratio double precision,
  model_name character varying,
  model_version character varying,
  user_rating integer CHECK (user_rating >= 1 AND user_rating <= 5),
  user_feedback text,
  generated_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone,
  CONSTRAINT summaries_pkey PRIMARY KEY (id),
  CONSTRAINT summaries_document_id_fkey FOREIGN KEY (document_id) REFERENCES public.documents(id)
);
CREATE TABLE public.system_metrics (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  metric_type character varying NOT NULL,
  metric_name character varying NOT NULL,
  value double precision NOT NULL,
  unit character varying,
  context jsonb,
  recorded_at timestamp with time zone DEFAULT now(),
  CONSTRAINT system_metrics_pkey PRIMARY KEY (id)
);
CREATE TABLE public.translations (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  document_id uuid NOT NULL,
  source_text text NOT NULL,
  translated_text text NOT NULL,
  source_language character varying NOT NULL,
  target_language character varying NOT NULL,
  page_number integer,
  chunk_id uuid,
  model_name character varying,
  translation_engine character varying,
  bleu_score double precision,
  confidence_score double precision,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT translations_pkey PRIMARY KEY (id),
  CONSTRAINT translations_document_id_fkey FOREIGN KEY (document_id) REFERENCES public.documents(id),
  CONSTRAINT translations_chunk_id_fkey FOREIGN KEY (chunk_id) REFERENCES public.document_chunks(id)
);
CREATE TABLE public.user_analytics (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL UNIQUE,
  total_documents integer DEFAULT 0,
  total_queries integer DEFAULT 0,
  total_summaries integer DEFAULT 0,
  total_translations integer DEFAULT 0,
  total_searches integer DEFAULT 0,
  avg_summary_rating double precision,
  avg_query_rating double precision,
  total_feedback_count integer DEFAULT 0,
  favorite_document_type character varying,
  most_used_language character varying,
  preferred_summary_type character varying,
  last_active timestamp with time zone,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT user_analytics_pkey PRIMARY KEY (id),
  CONSTRAINT user_analytics_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
);
CREATE TABLE public.users (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  email character varying NOT NULL UNIQUE,
  username character varying NOT NULL UNIQUE,
  password_hash character varying NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  last_login timestamp with time zone,
  is_active boolean DEFAULT true,
  preferences jsonb DEFAULT '{}'::jsonb,
  CONSTRAINT users_pkey PRIMARY KEY (id)
);