Database Schema Guide

Postgres tables for profiles, chats, documents, vectors, billing, plus the RLS policies that keep each user’s files out of someone else’s queries.

Prerequisites

• Supabase project with PostgreSQL
• pgvector extension enabled

Tables Overview

profiles

Stores user profile information linked to Supabase Auth.

create table profiles (
  id uuid references auth.users not null primary key,
  email text,
  tier text default 'free',        -- 'free', 'pro', 'enterprise'
  credits int default 10,
  stripe_customer_id text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

Key Fields:

• id: Links to auth.users - automatically populated
• tier: Controls feature access and allowed models
• credits: Current credit balance
• stripe_customer_id: Links to Stripe for billing

chats

Stores chat threads/conversations.

create table chats (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  title text not null,
  document_ids uuid[] default '{}',  -- Attached document IDs
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

Key Fields:

• document_ids: Array of document UUIDs for RAG context
• title: Auto-generated from first message

messages

Stores individual messages within chats.

create table messages (
  id uuid default gen_random_uuid() primary key,
  chat_id uuid references chats(id) on delete cascade not null,
  role text not null,              -- 'user', 'assistant', 'system'
  content text not null,
  model_used text,                 -- Which AI model generated this
  tokens_used int,                 -- Token count for analytics
  created_at timestamptz default now()
);

documents

Stores uploaded document metadata.

create table documents (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  filename text not null,
  file_path text not null,         -- Path in Supabase Storage
  mime_type text not null,
  file_size bigint,
  status text default 'processing', -- 'processing', 'completed', 'failed'
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

document_embeddings

Stores vector embeddings for RAG retrieval.

create table document_embeddings (
  id uuid default gen_random_uuid() primary key,
  document_id uuid references documents(id) on delete cascade not null,
  chunk_index int not null,
  content text not null,
  embedding vector(1536) not null,  -- OpenAI embedding dimension
  created_at timestamptz default now()
);

-- Index for similarity search
create index document_embeddings_embedding_idx on document_embeddings
using ivfflat (embedding vector_cosine_ops)
with (lists = 100);

Row Level Security (RLS)

All tables have RLS enabled with the following policies:

Profile Policies

-- Users can view their own profile
create policy "Users can view own profile" on profiles
  for select using (auth.uid() = id);

-- Users can create their own profile
create policy "Users can create own profile" on profiles
  for insert with check (auth.uid() = id);

-- Users can update their own profile
create policy "Users can update own profile" on profiles
  for update using (auth.uid() = id);

Chat & Message Policies

-- Users can view their own chats
create policy "Users can view own chats" on chats
  for select using (auth.uid() = user_id);

-- Messages: Users can view messages from their own chats
create policy "Users can view own messages" on messages
  for select using (
    exists (
      select 1 from chats
      where chats.id = messages.chat_id
      and chats.user_id = auth.uid()
    )
  );

Database Functions

match_documents

Similarity search function for RAG retrieval:

create function match_documents(
  query_embedding vector(1536),
  match_threshold float,
  match_count int,
  filter_document_ids uuid[]
)
returns table (
  id uuid,
  content text,
  similarity float,
  document_id uuid,
  chunk_index int
)

Usage: Called by the RAG retriever to find relevant document chunks.

handle_new_user

Trigger function to auto-create profile on signup:

create function handle_new_user()
returns trigger as $$
begin
  insert into profiles (id, email, tier, credits)
  values (new.id, new.email, 'free', 10);
  return new;
end;
$$ language plpgsql;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure handle_new_user();

Storage Setup

Create a bucket named uploads in Supabase Storage with these policies:

-- Users can upload to their own folder
create policy "Users can upload files" on storage.objects
  for insert to authenticated
  with check (
    bucket_id = 'uploads'
    and (storage.foldername(name))[1] = auth.uid()::text
  );

-- Users can read their own files
create policy "Users can read own files" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'uploads'
    and (storage.foldername(name))[1] = auth.uid()::text
  );

Running Migrations

1. Copy src/db/schema.sql content
2. Open Supabase SQL Editor
3. Paste and run the SQL
4. Verify tables are created in Table Editor