Open-source agentic AI Search Execution Platform. Track AI visibility, find opportunities, and let agents turn signals into prioritized actions and drafted fixes across ChatGPT, Claude, Gemini, Google AI Overviews, Google AI Mode, Perplexity, Grok, and Microsoft Copilot.
TypeScript aeoai-agentai-searchanswer-engine-optimizationbrand-monitoringchatgptclaudecopilotdockergeminigeogoogle-ai-overviewsgrokmcpnextjsopen-sourceperplexityseosupabasetypescript
-- In-product AI agent: schema for conversations, messages, and per-user
-- monthly token usage (#94 Phase 2).
--
-- Three tables, each scoped to a user inside an organization:
--
-- agent_conversations one chat thread; "+ new chat" creates a row
-- agent_messages ordered messages within a thread; tool calls
-- stored alongside text content as jsonb
-- agent_token_usage monthly bucket per user so the cost-guard can
-- enforce plan-level quotas without scanning the
-- messages table on every request
--
-- RLS scopes everything to auth.uid() — users can only see their own
-- conversations / messages / usage. The chat API runs server-side with
-- supabaseAdmin so it can write tool results that the user didn't author;
-- service_role bypasses RLS naturally.
-- ── agent_conversations ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.agent_conversations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
-- Optional default brand context: a new chat opened from a brand-specific
-- page can pin the conversation to that brand so subsequent tool calls
-- don't need to re-resolve the brand id every turn. Null = no pin.
brand_id uuid REFERENCES public.brands(id) ON DELETE SET NULL,
title text NOT NULL DEFAULT 'New conversation',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
-- Recency-sorted lookup per user is the hot path (sidebar list).
CREATE INDEX IF NOT EXISTS idx_agent_conversations_user_updated
ON public.agent_conversations (user_id, updated_at DESC);
-- ── agent_messages ──────────────────────────────────────────────────────
-- Roles match the Vercel AI SDK / OpenAI shape so we can hydrate the chat
-- panel and feed the SDK's tool-calling loop without re-mapping:
--
-- user regular user message; `content` holds the text
-- assistant model output; `content` may be empty if the model only
-- emitted tool calls; `tool_calls` holds the array
-- tool result of a tool call; `tool_call_id` joins back to the
-- assistant message's tool_calls entry; `tool_result` holds
-- the JSON payload returned by the tool
CREATE TABLE IF NOT EXISTS public.agent_messages (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
conversation_id uuid NOT NULL REFERENCES public.agent_conversations(id) ON DELETE CASCADE,
role text NOT NULL CHECK (role IN ('user', 'assistant', 'tool')),
content text NOT NULL DEFAULT '',
tool_calls jsonb,
tool_call_id text,
tool_name text,
tool_result jsonb,
-- Token usage attributed to this message for analytics + the monthly
-- bucket below. Null on user/tool rows; filled on the assistant
-- response once the SDK stream finishes.
prompt_tokens int,
completion_tokens int,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_agent_messages_conv_created
ON public.agent_messages (conversation_id, created_at);
-- ── agent_token_usage ───────────────────────────────────────────────────
-- One row per (user, organization, year_month). The chat API upserts on
-- this row at the end of every streamed response, so quota enforcement is
-- one SELECT instead of an aggregate scan over agent_messages.
CREATE TABLE IF NOT EXISTS public.agent_token_usage (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
-- 'YYYY-MM' (UTC). Stored as text so it's portable + lex-sortable.
year_month text NOT NULL,
prompt_tokens bigint NOT NULL DEFAULT 0,
completion_tokens bigint NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, organization_id, year_month)
);
CREATE INDEX IF NOT EXISTS idx_agent_token_usage_user_month
ON public.agent_token_usage (user_id, year_month);
-- ── RLS ────────────────────────────────────────────────────────────────
ALTER TABLE public.agent_conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_token_usage ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users read own conversations" ON public.agent_conversations
FOR SELECT USING (user_id = auth.uid());
CREATE POLICY "Users write own conversations" ON public.agent_conversations
FOR ALL USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());
CREATE POLICY "Users read own messages" ON public.agent_messages
FOR SELECT USING (conversation_id IN (
SELECT id FROM public.agent_conversations WHERE user_id = auth.uid()
));
CREATE POLICY "Users write own messages" ON public.agent_messages
FOR ALL USING (conversation_id IN (
SELECT id FROM public.agent_conversations WHERE user_id = auth.uid()
)) WITH CHECK (conversation_id IN (
SELECT id FROM public.agent_conversations WHERE user_id = auth.uid()
));
-- Read-only via RLS so the dashboard can display the user's own usage.
-- The chat API writes via supabaseAdmin (service_role bypasses RLS).
CREATE POLICY "Users read own usage" ON public.agent_token_usage
FOR SELECT USING (user_id = auth.uid());
-- ── updated_at trigger ──────────────────────────────────────────────────
-- Conversations: touch updated_at when title or brand_id changes (sidebar
-- sort key) and when a new message is inserted (handled at the API layer).
CREATE OR REPLACE FUNCTION public.touch_agent_conversation_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_agent_conversations_touch_updated_at
BEFORE UPDATE ON public.agent_conversations
FOR EACH ROW EXECUTE FUNCTION public.touch_agent_conversation_updated_at();
CREATE OR REPLACE FUNCTION public.touch_agent_token_usage_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_agent_token_usage_touch_updated_at
BEFORE UPDATE ON public.agent_token_usage
FOR EACH ROW EXECUTE FUNCTION public.touch_agent_token_usage_updated_at();