-- =============================================================================

-- SUPABASE DATABASE SETUP - Version 5.0.1

-- =============================================================================

-- Run this SQL in the Supabase SQL Editor to set up all required tables,

-- functions, indexes, and RLS policies for the application.

-- =============================================================================

-- =============================================================================

-- STEP 1: ENABLE REQUIRED EXTENSIONS

-- =============================================================================

-- Enable UUID generation

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA extensions;

-- Enable vector extension for document embeddings

-- Note: PostgreSQL does not support indexing vectors with more than 2,000 dimensions

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;

-- =============================================================================

-- STEP 2: CREATE USERS TABLE

-- =============================================================================

CREATE TABLE IF NOT EXISTS public.users (

id uuid REFERENCES auth.users NOT NULL PRIMARY KEY,

full_name text,

email text

);

-- Enable Row Level Security

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

-- RLS Policies for users table

DROP POLICY IF EXISTS "Users can insert own data" ON public.users;

CREATE POLICY "Users can insert own data"

ON public.users

FOR INSERT

TO public

WITH CHECK (id = (SELECT auth.uid()));

DROP POLICY IF EXISTS "Users can update own data" ON public.users;

CREATE POLICY "Users can update own data"

ON public.users

FOR UPDATE

TO public

USING (id = (SELECT auth.uid()))

WITH CHECK (id = (SELECT auth.uid()));

DROP POLICY IF EXISTS "Users can view own data" ON public.users;

CREATE POLICY "Users can view own data"

ON public.users

FOR SELECT

TO public

USING (id = (SELECT auth.uid()));

-- =============================================================================

-- STEP 3: CREATE TRIGGER FOR NEW USER REGISTRATION

-- =============================================================================

-- Trigger function to auto-create user record on signup.

-- SECURITY DEFINER functions resolve unqualified names through the CALLER's

-- search_path, which lets a same-named object in another schema hijack them.

-- Pinning search_path to '' and schema-qualifying every reference closes that.

CREATE OR REPLACE FUNCTION public.handle_new_user()

RETURNS trigger

LANGUAGE plpgsql

SECURITY DEFINER

SET search_path = ''

AS $$

BEGIN

INSERT INTO public.users (id, full_name, email)

VALUES (

new.id,

new.raw_user_meta_data->>'full_name',

new.email

);

RETURN new;

END;

$$;

-- Trigger to execute function on new auth user

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 PROCEDURE public.handle_new_user();

-- =============================================================================

-- STEP 4: CREATE CHAT SESSIONS TABLE

-- =============================================================================

CREATE TABLE IF NOT EXISTS public.chat_sessions (

id uuid NOT NULL DEFAULT extensions.uuid_generate_v4(),

user_id uuid NOT NULL,

created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

chat_title text NULL,

-- Per-chat flags used by the chat sidebar menu. A chat can be opened publicly

-- (via /shared-chat/[id]) only while is_public = true.

is_favorite boolean NOT NULL DEFAULT false,

is_public boolean NOT NULL DEFAULT false,

-- Settings the conversation ran with, e.g. { "model": "claude-sonnet-5" }.

-- Written by the chat route on every generation (last-used model wins) and

-- used to restore the model picker when reopening the conversation.

settings jsonb NULL,

CONSTRAINT chat_sessions_pkey PRIMARY KEY (id),

CONSTRAINT chat_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

) TABLESPACE pg_default;

-- Indexes for chat_sessions

CREATE INDEX IF NOT EXISTS idx_chat_sessions_user_id

ON public.chat_sessions USING btree (user_id) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS chat_sessions_created_at_idx

ON public.chat_sessions USING btree (created_at) TABLESPACE pg_default;

-- Partial indexes keep the "favorites" sidebar group and public lookups cheap

CREATE INDEX IF NOT EXISTS chat_sessions_user_favorite_idx

ON public.chat_sessions (user_id, is_favorite)

WHERE is_favorite = true;

CREATE INDEX IF NOT EXISTS chat_sessions_public_idx

ON public.chat_sessions (id)

WHERE is_public = true;

-- Enable RLS for chat_sessions

ALTER TABLE public.chat_sessions ENABLE ROW LEVEL SECURITY;

-- RLS Policy for chat_sessions

DROP POLICY IF EXISTS "Users can view own chat sessions" ON public.chat_sessions;

CREATE POLICY "Users can view own chat sessions"

ON public.chat_sessions

AS PERMISSIVE

FOR ALL

TO public

USING (user_id = (SELECT auth.uid()));

-- =============================================================================

-- STEP 5: CREATE MESSAGE PARTS TABLE (Incremental Message Saving)

-- =============================================================================

-- This table stores individual message parts (text, tools, reasoning, etc.)

-- allowing for incremental saving and proper ordering of AI responses

CREATE TABLE IF NOT EXISTS public.message_parts (

id uuid NOT NULL DEFAULT gen_random_uuid(),

chat_session_id uuid NOT NULL,

message_id text NOT NULL,

role text NOT NULL,

type text NOT NULL,

"order" integer NOT NULL DEFAULT 0,

created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

-- Text part fields

text_text text NULL,

text_state text NULL DEFAULT 'done',

-- Reasoning part fields

reasoning_text text NULL,

reasoning_state text NULL DEFAULT 'done',

-- File part fields

file_mediatype text NULL,

file_filename text NULL,

file_url text NULL,

-- Source URL part fields

source_url_id text NULL,

source_url_url text NULL,

source_url_title text NULL,

-- Source Document part fields

source_document_id text NULL,

source_document_mediatype text NULL,

source_document_title text NULL,

source_document_filename text NULL,

-- Tool fields (generic — shared by ALL tools). The `type` column identifies

-- which tool a row belongs to (e.g. 'tool-searchUserDocument')

tool_toolcallid text NULL,

tool_state text NULL,

tool_input jsonb NULL,

tool_output jsonb NULL,

tool_errortext text NULL,

tool_providerexecuted boolean NULL,

tool_approval jsonb NULL,

-- Provider metadata

providermetadata jsonb NULL,

-- Per-step token usage, attached to the first part row saved for each

-- generation step: { model_id, input_tokens, cache_read_tokens,

-- cache_write_tokens, output_tokens }. Aggregated by the /usage and /admin

-- dashboards (WHERE usage IS NOT NULL).

usage jsonb NULL,

-- Constraints

CONSTRAINT message_parts_pkey PRIMARY KEY (id),

CONSTRAINT message_parts_chat_session_id_fkey FOREIGN KEY (chat_session_id)

REFERENCES chat_sessions (id) ON DELETE CASCADE,

CONSTRAINT message_parts_role_check CHECK (

role = ANY (ARRAY['user'::text, 'assistant'::text, 'system'::text])

)

) TABLESPACE pg_default;

-- Indexes for message_parts

CREATE INDEX IF NOT EXISTS idx_message_parts_chat_session_id

ON public.message_parts USING btree (chat_session_id) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_message_parts_message_id

ON public.message_parts USING btree (message_id) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_message_parts_chat_session_message_order

ON public.message_parts USING btree (chat_session_id, message_id, "order") TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_message_parts_created_at

ON public.message_parts USING btree (created_at) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_message_parts_type

ON public.message_parts USING btree (type) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_message_parts_message_order

ON public.message_parts USING btree (message_id, "order") TABLESPACE pg_default;

-- Partial index: the usage dashboards only scan rows that carry usage data

CREATE INDEX IF NOT EXISTS idx_message_parts_usage

ON public.message_parts USING btree (created_at)

WHERE usage IS NOT NULL;

-- Enable RLS for message_parts

ALTER TABLE public.message_parts ENABLE ROW LEVEL SECURITY;

-- RLS Policy for message_parts

DROP POLICY IF EXISTS "Users can view messages from their sessions" ON public.message_parts;

CREATE POLICY "Users can view messages from their sessions"

ON public.message_parts

AS PERMISSIVE

FOR ALL

TO public

USING (

chat_session_id IN (

SELECT chat_sessions.id

FROM chat_sessions

WHERE chat_sessions.user_id = (SELECT auth.uid())

)

);

-- =============================================================================

-- STEP 6: CREATE USER DOCUMENTS TABLE (Document Metadata)

-- =============================================================================

CREATE TABLE IF NOT EXISTS public.user_documents (

id uuid NOT NULL DEFAULT gen_random_uuid(),

user_id uuid NOT NULL,

title text NOT NULL,

total_pages integer NOT NULL,

ai_description text NULL,

ai_keyentities text[] NULL,

ai_maintopics text[] NULL,

ai_title text NULL,

file_path text NOT NULL,

created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at timestamp with time zone NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT user_documents_pkey PRIMARY KEY (id),

CONSTRAINT user_documents_user_title_unique UNIQUE (user_id, title),

CONSTRAINT user_documents_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE

) TABLESPACE pg_default;

-- Index for user_documents

CREATE INDEX IF NOT EXISTS idx_user_documents_user_id

ON public.user_documents USING btree (user_id) TABLESPACE pg_default;

-- Enable RLS for user_documents

ALTER TABLE public.user_documents ENABLE ROW LEVEL SECURITY;

-- RLS Policy for user_documents

DROP POLICY IF EXISTS "Users can only access their own documents" ON public.user_documents;

CREATE POLICY "Users can only access their own documents"

ON public.user_documents

FOR ALL

TO public

USING ((SELECT auth.uid()) = user_id);

-- =============================================================================

-- STEP 7: CREATE USER DOCUMENTS VECTORS TABLE (Document Embeddings)

-- =============================================================================

CREATE TABLE IF NOT EXISTS public.user_documents_vec (

id uuid NOT NULL DEFAULT gen_random_uuid(),

document_id uuid NOT NULL,

text_content text NOT NULL,

page_number integer NOT NULL,

embedding extensions.vector(1024) NULL,

-- Timestamp set automatically when the row is inserted

created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT user_documents_vec_pkey PRIMARY KEY (id),

CONSTRAINT user_documents_vec_document_page_unique UNIQUE (document_id, page_number),

CONSTRAINT user_documents_vec_document_id_fkey FOREIGN KEY (document_id) REFERENCES user_documents (id) ON DELETE CASCADE

) TABLESPACE pg_default;

-- Index for user_documents_vec

CREATE INDEX IF NOT EXISTS idx_user_documents_vec_document_id

ON public.user_documents_vec USING btree (document_id) TABLESPACE pg_default;

-- HNSW index for vector similarity search

-- Parameters: m=16 (connections per layer), ef_construction=200 (build candidate list size)

-- - m=16 is the pgvector default and is fine in almost all cases. Raising it to

-- m=32 roughly DOUBLES the index size for little recall gain on most datasets.

-- - The whole index should fit in Postgres' buffer cache (~25% of the instance RAM)

-- to stay fast. If the index grows larger than that, query latency can degrade.

--

-- NOTE: This index is ONLY used by unfiltered queries (vector ORDER BY + LIMIT,

-- like match_documents below). Adding a WHERE filter on another column disables

-- it -- for that you need a separate partial HNSW index per filter value.

--

-- TODO (only when you grow past ~100k rows of dense text): switch this index to

-- halfvec to roughly halve its size at ~1% recall loss, e.g.

-- USING hnsw ((embedding::halfvec(1024)) halfvec_l2_ops)

-- If you do, also cast to halfvec inside match_documents() so the planner uses it.

-- See the "Tuning the HNSW vector index" section in README.md for details.

CREATE INDEX IF NOT EXISTS user_documents_vec_embedding_idx

ON public.user_documents_vec

USING hnsw (embedding extensions.vector_l2_ops)

WITH (m = '16', ef_construction = '200')

TABLESPACE pg_default;

-- Enable RLS for user_documents_vec

ALTER TABLE public.user_documents_vec ENABLE ROW LEVEL SECURITY;

-- RLS Policy for user_documents_vec

DROP POLICY IF EXISTS "Users can only access their own document vectors" ON public.user_documents_vec;

CREATE POLICY "Users can only access their own document vectors"

ON public.user_documents_vec

FOR ALL

TO public

USING (

EXISTS (

SELECT 1 FROM user_documents

WHERE user_documents.id = user_documents_vec.document_id

AND user_documents.user_id = (SELECT auth.uid())

)

);

-- =============================================================================

-- STEP 8: CREATE HYBRID SEARCH FUNCTION (vector + keyword, RRF-fused)

-- =============================================================================

-- Used by the autonomous document search tool (searchUserDocument). The

-- vector arm catches meaning ("payment terms" matches synonymous phrasing);

-- the keyword arm catches exact tokens (invoice numbers, names, codes) that

-- embeddings blur. Reciprocal Rank Fusion merges the two rankings without

-- score normalization: each result contributes weight / (k + rank) from

-- every list it appears in.

-- Drop the legacy vector-only signature if it exists — leaving it in place

-- creates an overload PostgREST cannot resolve (PGRST202).

DROP FUNCTION IF EXISTS public.match_documents(

vector(1024), int, uuid, uuid[], float

);

CREATE OR REPLACE FUNCTION public.match_documents(

query_embedding vector(1024),

query_text text,

match_count int,

filter_user_id uuid,

file_ids uuid[],

vector_weight float DEFAULT 0.6,

k_rrf int DEFAULT 60

)

RETURNS TABLE (

id uuid,

document_id uuid,

text_content text,

title text,

ai_title text,

ai_description text,

page_number integer,

total_pages integer,

similarity float

)

LANGUAGE sql

-- SECURITY INVOKER (the default): RLS on the underlying tables still applies,

-- so filter_user_id can never widen what the caller may see. The pinned

-- search_path keeps the vector operators resolving to the extensions schema.

SET search_path = public, extensions

AS $$

WITH scoped AS (

-- Filtered candidate set: this user's documents, optionally restricted to

-- specific files. Filtered vector queries can't use the global HNSW index

-- anyway (see the index notes above), so both arms scan this set.

SELECT vec.id, vec.document_id, vec.text_content, vec.page_number,

vec.embedding, doc.title, doc.ai_title, doc.ai_description,

doc.total_pages

FROM user_documents_vec vec

JOIN user_documents doc ON doc.id = vec.document_id

WHERE doc.user_id = filter_user_id

AND doc.id = ANY(file_ids)

),

vector_hits AS (

SELECT s.id,

ROW_NUMBER() OVER (ORDER BY s.embedding <=> query_embedding) AS rank

FROM scoped s

WHERE s.embedding IS NOT NULL

ORDER BY s.embedding <=> query_embedding

LIMIT LEAST(GREATEST(match_count, 1) * 4, 200)

),

keyword_hits AS (

SELECT s.id,

ROW_NUMBER() OVER (

ORDER BY ts_rank_cd(

to_tsvector('simple', s.text_content),

websearch_to_tsquery('simple', query_text)

) DESC

) AS rank

FROM scoped s

WHERE to_tsvector('simple', s.text_content)

@@ websearch_to_tsquery('simple', query_text)

LIMIT LEAST(GREATEST(match_count, 1) * 4, 200)

),

fused AS (

SELECT COALESCE(v.id, k.id) AS id,

COALESCE(vector_weight / (k_rrf + v.rank), 0)

+ COALESCE((1 - vector_weight) / (k_rrf + k.rank), 0) AS score

FROM vector_hits v

FULL OUTER JOIN keyword_hits k ON v.id = k.id

)

SELECT s.id, s.document_id, s.text_content, s.title, s.ai_title,

s.ai_description, s.page_number, s.total_pages, f.score AS similarity

FROM fused f

JOIN scoped s ON s.id = f.id

ORDER BY f.score DESC

LIMIT LEAST(GREATEST(match_count, 1), 50);

$$;

-- =============================================================================

-- STEP 9: AI MODELS CATALOG + PER-USER SELECTION

-- =============================================================================

-- Reference table of the models the app can use. The primary key is an

-- auto-incrementing id; `model_id` is the slug the chat API route switches on

-- and is what users.selected_model points at, so each user's chosen model is

-- visible directly on the users table. Costs are per 1M tokens in USD.

CREATE TABLE IF NOT EXISTS public.ai_models (

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

model_id text NOT NULL UNIQUE,

display_name text NOT NULL,

provider text NOT NULL,

input_cost_per_million_usd numeric(10, 4) NOT NULL,

output_cost_per_million_usd numeric(10, 4) NOT NULL,

active boolean NOT NULL DEFAULT true,

updated_at timestamp with time zone NOT NULL DEFAULT now(),

description text NOT NULL DEFAULT '',

source_url text NOT NULL DEFAULT '',

cost_tier text NOT NULL DEFAULT 'medium',

cost_note text NOT NULL DEFAULT '',

display_order integer NOT NULL DEFAULT 0,

selectable boolean NOT NULL DEFAULT true,

CONSTRAINT ai_models_cost_tier_check CHECK (

cost_tier = ANY (ARRAY['low'::text, 'medium'::text, 'high'::text])

)

);

CREATE INDEX IF NOT EXISTS ai_models_display_order_idx

ON public.ai_models USING btree (display_order);

-- Reference data: readable by any authenticated user, not writable from the app

ALTER TABLE public.ai_models ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Authenticated users can read ai models" ON public.ai_models;

CREATE POLICY "Authenticated users can read ai models"

ON public.ai_models

FOR SELECT

TO authenticated

USING (true);

-- Seed models. logo_url is intentionally omitted: the UI maps `provider` to a

-- local image in public/images/ai-providers/.

-- The chat API route serves two providers: 'anthropic' (Claude API, prompt

-- cached, adaptive thinking) and 'vllm' — the local OpenAI-compatible vLLM

-- server on the GPU box (VLLM_BASE_URL), where model_id must equal the model

-- tag vLLM was launched with. Costs are the official API sticker prices per

-- 1M tokens (they feed the /usage and /admin cost estimates); the local model

-- is free. Note: Sonnet 5 has introductory pricing of $2/$10 per MTok

-- through 2026-08-31 — the sticker price is stored so estimates stay valid

-- after the intro period.

INSERT INTO public.ai_models

(model_id, display_name, provider, input_cost_per_million_usd, output_cost_per_million_usd, active, description, source_url, cost_tier, cost_note, display_order, selectable)

VALUES

('claude-sonnet-5', 'Sonnet 5', 'anthropic', 3.0000, 15.0000, true, 'The best combination of speed and intelligence — near-Opus quality on coding and agentic work. Intro pricing ($2/$10 per MTok) until Aug 31, 2026.', 'https://www.anthropic.com/claude', 'medium', '~$0.45/answer', 1, true),

('claude-opus-4-8', 'Opus 4.8', 'anthropic', 5.0000, 25.0000, true, 'Anthropic''s most capable Opus-tier model — complex agentic coding and enterprise work.', 'https://www.anthropic.com/claude', 'high', '~$1.25/answer', 2, true),

('claude-fable-5', 'Fable 5', 'anthropic', 10.0000, 50.0000, true, 'Anthropic''s most capable widely released model — next-generation intelligence for the most demanding reasoning and long-running agents. Requires 30-day data retention on your Anthropic org.', 'https://www.anthropic.com/claude', 'high', '~$2.50/answer', 3, true),

('orcarouter/Qwen3.8-27B-Uncensored-FP8', 'Qwen3.8 27B (local)', 'vllm', 0.0000, 0.0000, true, 'Uncensored Qwen3.8-27B (FP8) served by vLLM on your own GPU box — no API cost. Thinking on at medium effort with previous turns'' reasoning preserved.', 'https://huggingface.co/orcarouter/Qwen3.8-27B-Uncensored-FP8', 'low', 'free (local)', 4, true)

ON CONFLICT (model_id) DO UPDATE SET

display_name = EXCLUDED.display_name,

input_cost_per_million_usd = EXCLUDED.input_cost_per_million_usd,

output_cost_per_million_usd = EXCLUDED.output_cost_per_million_usd,

description = EXCLUDED.description,

cost_tier = EXCLUDED.cost_tier,

cost_note = EXCLUDED.cost_note,

display_order = EXCLUDED.display_order,

active = true,

selectable = true;

-- Per-user selected model. Nullable + ON DELETE SET NULL so removing a model

-- doesn't break users; the app falls back to the default when null.

ALTER TABLE public.users

ADD COLUMN IF NOT EXISTS selected_model text DEFAULT 'claude-sonnet-5'

REFERENCES public.ai_models (model_id) ON DELETE SET NULL;

-- =============================================================================

-- STEP 10: STORAGE BUCKET SETUP

-- =============================================================================

-- Note: Create a storage bucket named 'userfiles' in the Supabase dashboard first

-- Then run these policies:

-- Policy 1: Allow users to select their own files

DROP POLICY IF EXISTS "User can select own files" ON storage.objects;

CREATE POLICY "User can select own files"

ON storage.objects FOR SELECT

USING (

(bucket_id = 'userfiles'::text) AND

((auth.uid())::text = (storage.foldername(name))[1])

);

-- Policy 2: Allow users to insert their own files

DROP POLICY IF EXISTS "User can insert own files" ON storage.objects;

CREATE POLICY "User can insert own files"

ON storage.objects FOR INSERT

WITH CHECK (

(bucket_id = 'userfiles'::text) AND

((auth.uid())::text = (storage.foldername(name))[1])

);

-- Policy 3: Allow users to update their own files

DROP POLICY IF EXISTS "User can update own files" ON storage.objects;

CREATE POLICY "User can update own files"

ON storage.objects FOR UPDATE

USING (

(bucket_id = 'userfiles'::text) AND

((auth.uid())::text = (storage.foldername(name))[1])

);

-- Policy 4: Allow users to delete their own files

DROP POLICY IF EXISTS "User can delete own files" ON storage.objects;

CREATE POLICY "User can delete own files"

ON storage.objects FOR DELETE

USING (

(bucket_id = 'userfiles'::text) AND

((auth.uid())::text = (storage.foldername(name))[1])

);

-- =============================================================================

-- STEP 11: CREATE USER MEMORIES TABLE (saveMemory chat tool)

-- =============================================================================

-- Discrete long-term memories the assistant stores when the user explicitly

-- asks it to remember something ("remember that I prefer short answers").

-- Each memory is one row; the chat API injects all of a user's memories into

-- the system prompt on every request, and the saveMemory tool can list and

-- delete them again ("forget that ...").

CREATE TABLE IF NOT EXISTS public.user_memories (

id uuid NOT NULL DEFAULT gen_random_uuid(),

user_id uuid NOT NULL,

content text NOT NULL,

created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT user_memories_pkey PRIMARY KEY (id),

CONSTRAINT user_memories_user_id_fkey FOREIGN KEY (user_id)

REFERENCES public.users (id) ON DELETE CASCADE,

-- Keep single memories small; the whole set is injected into every prompt

CONSTRAINT user_memories_content_length CHECK (char_length(content) <= 500)

);

CREATE INDEX IF NOT EXISTS idx_user_memories_user_id

ON public.user_memories USING btree (user_id);

-- Enable RLS for user_memories

ALTER TABLE public.user_memories ENABLE ROW LEVEL SECURITY;

-- RLS Policy for user_memories

DROP POLICY IF EXISTS "Users can manage own memories" ON public.user_memories;

CREATE POLICY "Users can manage own memories"

ON public.user_memories

FOR ALL

TO public

USING (user_id = (SELECT auth.uid()))

WITH CHECK (user_id = (SELECT auth.uid()));

-- =============================================================================

-- STEP 12: ADMIN FLAG ON USERS

-- =============================================================================

-- Gates the /admin dashboard (user management + org-wide usage overview).

-- Grant yourself access after signing up:

-- UPDATE public.users SET is_admin = true WHERE email = 'you@example.com';

ALTER TABLE public.users

ADD COLUMN IF NOT EXISTS is_admin boolean NOT NULL DEFAULT false;

-- =============================================================================

-- STEP 13: COLUMN-LEVEL WRITE PRIVILEGES ON USERS

-- =============================================================================

-- RLS decides WHICH ROWS a user may touch; it cannot restrict WHICH COLUMNS.

-- Without this step any signed-in user holding the (publishable) anon key

-- could flip their own is_admin flag straight through PostgREST. Table-level

-- write privileges are therefore revoked from the API roles and re-granted

-- only for the columns the app lets users edit about themselves.

--

-- Unaffected: the signup trigger (runs as its SECURITY DEFINER owner) and the

-- admin dashboard (service role). Idempotent — safe to re-run.

REVOKE INSERT, UPDATE, DELETE ON public.users FROM anon, authenticated;

GRANT UPDATE (full_name, selected_model) ON public.users TO authenticated;

-- =============================================================================

-- STEP 14: STORAGE BUCKET LIMITS

-- =============================================================================

-- The app only accepts PDFs up to 50 MB (file manager + /api/upload). Enforce

-- the same at the bucket so a hand-crafted PUT to a signed upload URL cannot

-- bypass the application checks. This is a no-op until the 'userfiles' bucket

-- exists (Quickstart step 2) — re-run this file after creating it.

UPDATE storage.buckets

SET public = false,

file_size_limit = 52428800, -- 50 MB

allowed_mime_types = ARRAY['application/pdf']

WHERE id = 'userfiles';

-- =============================================================================

-- STEP 15: CREATE GENERATED IMAGES TABLE (generateImage chat tool)

-- =============================================================================

-- Images produced by the generateImage tool (local Qwen-Image server,

-- IMAGE_GEN_BASE_URL, default http://localhost:8004/v1). The raw PNG is

-- re-encoded to a lossy WebP with sharp before insert (~150-400 KB), so the

-- bytes live directly in the row — no storage bucket. The tool part in message_parts only carries the image id;

-- the browser loads the bytes from GET /api/images/[id].

-- Deleting the chat (or the user) removes its images via the cascades.

CREATE TABLE IF NOT EXISTS public.generated_images (

id uuid NOT NULL DEFAULT gen_random_uuid(),

user_id uuid NOT NULL,

chat_session_id uuid NOT NULL,

prompt text NOT NULL,

width integer NOT NULL,

height integer NOT NULL,

media_type text NOT NULL DEFAULT 'image/webp',

size_bytes integer NOT NULL,

-- Base64 of the encoded image (text survives PostgREST round-trips as-is;

-- bytea would come back hex-encoded at twice the size)

data_base64 text NOT NULL,

created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT generated_images_pkey PRIMARY KEY (id),

CONSTRAINT generated_images_user_id_fkey FOREIGN KEY (user_id)

REFERENCES public.users (id) ON DELETE CASCADE,

CONSTRAINT generated_images_chat_session_id_fkey FOREIGN KEY (chat_session_id)

REFERENCES public.chat_sessions (id) ON DELETE CASCADE,

-- Hard ceiling (~6 MB of base64) so a misbehaving encoder can't bloat rows

CONSTRAINT generated_images_data_length CHECK (char_length(data_base64) <= 8000000)

);

CREATE INDEX IF NOT EXISTS idx_generated_images_user_id

ON public.generated_images USING btree (user_id);

CREATE INDEX IF NOT EXISTS idx_generated_images_chat_session_id

ON public.generated_images USING btree (chat_session_id);

-- Enable RLS for generated_images

ALTER TABLE public.generated_images ENABLE ROW LEVEL SECURITY;

-- RLS Policy for generated_images

DROP POLICY IF EXISTS "Users can manage own generated images" ON public.generated_images;

CREATE POLICY "Users can manage own generated images"

ON public.generated_images

FOR ALL

TO public

USING (user_id = (SELECT auth.uid()))

WITH CHECK (user_id = (SELECT auth.uid()));

-- =============================================================================

-- SETUP COMPLETE

-- =============================================================================

--

-- After running this SQL:

-- 1. Create a storage bucket named 'userfiles' (set to private), then re-run

-- this file once so STEP 14 applies the size/MIME limits to it

-- 2. Configure your environment variables in .env.local

-- 3. Set up email templates in Supabase Auth settings

-- 4. (Optional) Grant yourself admin access to the /admin dashboard:

-- UPDATE public.users SET is_admin = true WHERE email = 'you@example.com';

--

-- NOTE: this file is the complete, current schema — and it is idempotent

-- (IF NOT EXISTS / CREATE OR REPLACE throughout), so it is safe to re-run on

-- an existing install to pick up new columns, tables and functions.

--

-- For more information, see the README.md file.

-- =============================================================================