221 lines
8.7 KiB
PL/PgSQL
221 lines
8.7 KiB
PL/PgSQL
-- The Disclosure Bureau — chat schema
|
|
-- Apply via Supabase Studio SQL editor OR psql on the production DB.
|
|
-- Safe to re-run (uses IF NOT EXISTS guards).
|
|
|
|
-- 1. profiles — 1:1 with auth.users, holds budget + role
|
|
CREATE TABLE IF NOT EXISTS public.profiles (
|
|
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
display_name TEXT,
|
|
avatar_url TEXT,
|
|
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user','admin','suspended')),
|
|
budget_cap_usd NUMERIC(10,4) NOT NULL DEFAULT 5.0,
|
|
total_cost_usd NUMERIC(10,4) NOT NULL DEFAULT 0,
|
|
daily_quota INT NOT NULL DEFAULT 100,
|
|
daily_used INT NOT NULL DEFAULT 0,
|
|
quota_reset_at TIMESTAMPTZ NOT NULL DEFAULT (DATE_TRUNC('day', NOW()) + INTERVAL '1 day'),
|
|
preferred_locale TEXT DEFAULT 'pt-BR',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Auto-create profile on user signup
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
|
|
BEGIN
|
|
INSERT INTO public.profiles (id, display_name)
|
|
VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.email));
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
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();
|
|
|
|
-- 2. chat_sessions — one conversation per row
|
|
CREATE TABLE IF NOT EXISTS public.chat_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
title TEXT,
|
|
summary TEXT,
|
|
context_doc_id TEXT,
|
|
context_page_id TEXT,
|
|
is_public BOOLEAN NOT NULL DEFAULT FALSE,
|
|
share_token TEXT UNIQUE,
|
|
archived BOOLEAN NOT NULL DEFAULT FALSE,
|
|
message_count INT NOT NULL DEFAULT 0,
|
|
total_tokens INT NOT NULL DEFAULT 0,
|
|
total_cost_usd NUMERIC(10,4) NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_user_updated
|
|
ON public.chat_sessions(user_id, updated_at DESC) WHERE NOT archived;
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_share
|
|
ON public.chat_sessions(share_token) WHERE share_token IS NOT NULL;
|
|
|
|
-- 3. messages — one row per turn
|
|
CREATE TABLE IF NOT EXISTS public.messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
session_id UUID NOT NULL REFERENCES public.chat_sessions(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL CHECK (role IN ('user','assistant','tool','system')),
|
|
content TEXT NOT NULL,
|
|
tool_calls JSONB,
|
|
tool_results JSONB,
|
|
citations JSONB,
|
|
model TEXT,
|
|
tokens_in INT,
|
|
tokens_out INT,
|
|
cost_usd NUMERIC(10,6),
|
|
duration_ms INT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_session
|
|
ON public.messages(session_id, created_at);
|
|
|
|
-- 4. usage_events — audit log of every billable action (optional but cheap)
|
|
CREATE TABLE IF NOT EXISTS public.usage_events (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
session_id UUID REFERENCES public.chat_sessions(id) ON DELETE SET NULL,
|
|
event_type TEXT NOT NULL, -- 'message','tool_call','enrichment',...
|
|
cost_usd NUMERIC(10,6) NOT NULL DEFAULT 0,
|
|
metadata JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_usage_user_created
|
|
ON public.usage_events(user_id, created_at DESC);
|
|
|
|
-- 5. updated_at trigger
|
|
CREATE OR REPLACE FUNCTION public.touch_updated_at()
|
|
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS profiles_touch ON public.profiles;
|
|
CREATE TRIGGER profiles_touch BEFORE UPDATE ON public.profiles
|
|
FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
|
|
|
|
DROP TRIGGER IF EXISTS sessions_touch ON public.chat_sessions;
|
|
CREATE TRIGGER sessions_touch BEFORE UPDATE ON public.chat_sessions
|
|
FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
|
|
|
|
-- 6. message-count + cost rollup trigger
|
|
CREATE OR REPLACE FUNCTION public.rollup_session_stats()
|
|
RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
UPDATE public.chat_sessions
|
|
SET
|
|
message_count = message_count + 1,
|
|
total_tokens = total_tokens + COALESCE(NEW.tokens_in, 0) + COALESCE(NEW.tokens_out, 0),
|
|
total_cost_usd = total_cost_usd + COALESCE(NEW.cost_usd, 0),
|
|
updated_at = NOW()
|
|
WHERE id = NEW.session_id;
|
|
|
|
-- Also bump the user's total
|
|
UPDATE public.profiles p
|
|
SET total_cost_usd = total_cost_usd + COALESCE(NEW.cost_usd, 0),
|
|
daily_used = daily_used + 1
|
|
FROM public.chat_sessions s
|
|
WHERE s.id = NEW.session_id AND p.id = s.user_id;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
DROP TRIGGER IF EXISTS messages_rollup ON public.messages;
|
|
CREATE TRIGGER messages_rollup AFTER INSERT ON public.messages
|
|
FOR EACH ROW EXECUTE FUNCTION public.rollup_session_stats();
|
|
|
|
-- 7. ROW LEVEL SECURITY — defense in depth
|
|
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.chat_sessions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.messages ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.usage_events ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- profiles: user sees/updates only their own
|
|
DROP POLICY IF EXISTS "own_profile_select" ON public.profiles;
|
|
CREATE POLICY "own_profile_select" ON public.profiles
|
|
FOR SELECT USING (auth.uid() = id);
|
|
DROP POLICY IF EXISTS "own_profile_update" ON public.profiles;
|
|
CREATE POLICY "own_profile_update" ON public.profiles
|
|
FOR UPDATE USING (auth.uid() = id);
|
|
|
|
-- chat_sessions: user's own + anyone can read public-shared ones
|
|
DROP POLICY IF EXISTS "own_sessions_select" ON public.chat_sessions;
|
|
CREATE POLICY "own_sessions_select" ON public.chat_sessions
|
|
FOR SELECT USING (auth.uid() = user_id OR is_public = TRUE);
|
|
DROP POLICY IF EXISTS "own_sessions_modify" ON public.chat_sessions;
|
|
CREATE POLICY "own_sessions_modify" ON public.chat_sessions
|
|
FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
|
|
|
|
-- messages: only owners of the parent session (or public if session is_public)
|
|
DROP POLICY IF EXISTS "session_messages_select" ON public.messages;
|
|
CREATE POLICY "session_messages_select" ON public.messages
|
|
FOR SELECT USING (
|
|
EXISTS (SELECT 1 FROM public.chat_sessions s
|
|
WHERE s.id = messages.session_id
|
|
AND (s.user_id = auth.uid() OR s.is_public = TRUE))
|
|
);
|
|
DROP POLICY IF EXISTS "session_messages_insert" ON public.messages;
|
|
CREATE POLICY "session_messages_insert" ON public.messages
|
|
FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.chat_sessions s
|
|
WHERE s.id = messages.session_id AND s.user_id = auth.uid())
|
|
);
|
|
DROP POLICY IF EXISTS "session_messages_delete" ON public.messages;
|
|
CREATE POLICY "session_messages_delete" ON public.messages
|
|
FOR DELETE USING (
|
|
EXISTS (SELECT 1 FROM public.chat_sessions s
|
|
WHERE s.id = messages.session_id AND s.user_id = auth.uid())
|
|
);
|
|
|
|
-- usage_events: insert by service role only; user can read their own
|
|
DROP POLICY IF EXISTS "own_usage_select" ON public.usage_events;
|
|
CREATE POLICY "own_usage_select" ON public.usage_events
|
|
FOR SELECT USING (auth.uid() = user_id);
|
|
|
|
-- 8. Helper RPC: get_or_create_session
|
|
CREATE OR REPLACE FUNCTION public.get_or_create_session(
|
|
p_context_doc_id TEXT DEFAULT NULL,
|
|
p_context_page_id TEXT DEFAULT NULL,
|
|
p_title TEXT DEFAULT NULL
|
|
) RETURNS UUID LANGUAGE plpgsql SECURITY INVOKER SET search_path = public AS $$
|
|
DECLARE
|
|
new_id UUID;
|
|
BEGIN
|
|
IF auth.uid() IS NULL THEN
|
|
RAISE EXCEPTION 'unauthenticated';
|
|
END IF;
|
|
new_id := gen_random_uuid();
|
|
INSERT INTO chat_sessions (id, user_id, title, context_doc_id, context_page_id)
|
|
VALUES (new_id, auth.uid(), p_title, p_context_doc_id, p_context_page_id);
|
|
RETURN new_id;
|
|
END;
|
|
$$;
|
|
GRANT EXECUTE ON FUNCTION public.get_or_create_session TO authenticated;
|
|
|
|
-- 9. Helper RPC: enforce budget cap
|
|
CREATE OR REPLACE FUNCTION public.check_budget(p_user_id UUID)
|
|
RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
|
|
DECLARE
|
|
prof RECORD;
|
|
BEGIN
|
|
SELECT * INTO prof FROM profiles WHERE id = p_user_id;
|
|
IF prof.role = 'suspended' THEN RETURN FALSE; END IF;
|
|
IF prof.total_cost_usd >= prof.budget_cap_usd THEN RETURN FALSE; END IF;
|
|
-- Reset daily counter if past midnight
|
|
IF prof.quota_reset_at <= NOW() THEN
|
|
UPDATE profiles
|
|
SET daily_used = 0,
|
|
quota_reset_at = DATE_TRUNC('day', NOW()) + INTERVAL '1 day'
|
|
WHERE id = p_user_id;
|
|
RETURN TRUE;
|
|
END IF;
|
|
IF prof.daily_used >= prof.daily_quota THEN RETURN FALSE; END IF;
|
|
RETURN TRUE;
|
|
END;
|
|
$$;
|
|
GRANT EXECUTE ON FUNCTION public.check_budget TO authenticated;
|