Skip to main content
— Create research_sessions table for storing research session metadata CREATE TABLE IF NOT EXISTS research_sessions ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, clerk_id TEXT NOT NULL REFERENCES all_users(clerk_id) ON DELETE CASCADE, session_id TEXT NOT NULL UNIQUE, — matches S3 session_id from API question TEXT NOT NULL, — the original research question status TEXT NOT NULL DEFAULT ‘running’ CHECK (status IN (‘running’, ‘completed’, ‘failed’, ‘cancelled’)), s3_key TEXT, — full S3 path once report is ready total_tool_calls INTEGER DEFAULT 0, duration_seconds INTEGER, error_message TEXT, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), completed_at TIMESTAMP WITH TIME ZONE ); — Indexes CREATE INDEX idx_research_sessions_clerk_id ON research_sessions(clerk_id); CREATE INDEX idx_research_sessions_session_id ON research_sessions(session_id); CREATE INDEX idx_research_sessions_status ON research_sessions(status); CREATE INDEX idx_research_sessions_created_at ON research_sessions(created_at DESC); — Auto-update updated_at trigger (same pattern as your other tables) CREATE OR REPLACE FUNCTION update_research_sessions_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; CREATE TRIGGER research_sessions_updated_at BEFORE UPDATE ON research_sessions FOR EACH ROW EXECUTE FUNCTION update_research_sessions_updated_at(); -- Enable RLS ALTER TABLE research_sessions ENABLE ROW LEVEL SECURITY; -- Policies (matching your existing pattern) CREATE POLICY "Users can read own research sessions" ON research_sessions FOR SELECT USING (clerk_id = auth.jwt()->>'clerk_id'); CREATE POLICY "Users can insert own research sessions" ON research_sessions FOR INSERT WITH CHECK (clerk_id = auth.jwt()->>'clerk_id'); CREATE POLICY "Users can update own research sessions" ON research_sessions FOR UPDATE USING (clerk_id = auth.jwt()->>'clerk_id') WITH CHECK (clerk_id = auth.jwt()->>'clerk_id'); CREATE POLICY "Users can delete own research sessions" ON research_sessions FOR DELETE USING (clerk_id = auth.jwt()->>'clerk_id'); -- Add progress tracking columns to research_sessions table -- These columns are updated by the deep-research backend during processing -- and read by the frontend via Supabase Realtime for live progress updates after page refresh -- Current phase being processed ALTER TABLE research_sessions ADD COLUMN IF NOT EXISTS current_phase VARCHAR(20); -- Tool call counts per phase ALTER TABLE research_sessions ADD COLUMN IF NOT EXISTS planner_tool_calls INTEGER DEFAULT 0; ALTER TABLE research_sessions ADD COLUMN IF NOT EXISTS executor_tool_calls INTEGER DEFAULT 0; ALTER TABLE research_sessions ADD COLUMN IF NOT EXISTS report_tool_calls INTEGER DEFAULT 0; -- Current tool being executed (for detailed progress) ALTER TABLE research_sessions ADD COLUMN IF NOT EXISTS current_tool VARCHAR(100); -- Last activity timestamp (for timeout detection) ALTER TABLE research_sessions ADD COLUMN IF NOT EXISTS last_activity_at TIMESTAMPTZ DEFAULT NOW(); -- Update the updated_at trigger if not exists CREATE OR REPLACE FUNCTION update_research_sessions_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS research_sessions_updated_at ON research_sessions; CREATE TRIGGER research_sessions_updated_at BEFORE UPDATE ON research_sessions FOR EACH ROW EXECUTE FUNCTION update_research_sessions_updated_at(); -- Enable Realtime for research_sessions table -- Note: This may already be enabled. Run in Supabase dashboard if needed. ALTER PUBLICATION supabase_realtime ADD TABLE research_sessions; COMMENT ON COLUMN research_sessions.current_phase IS 'Current phase: planner, executor, or report'; COMMENT ON COLUMN research_sessions.planner_tool_calls IS 'Number of tool calls made by planner agent'; COMMENT ON COLUMN research_sessions.executor_tool_calls IS 'Number of tool calls made by executor agent'; COMMENT ON COLUMN research_sessions.report_tool_calls IS 'Number of tool calls made by report agent'; COMMENT ON COLUMN research_sessions.current_tool IS 'Name of the tool currently being executed'; COMMENT ON COLUMN research_sessions.last_activity_at IS 'Last time progress was updated, for timeout detection'; ALTER TABLE research_sessions ADD COLUMN IF NOT EXISTS web_report_tool_calls INTEGER DEFAULT 0; COMMENT ON COLUMN research_sessions.web_report_tool_calls IS 'Number of tool calls made by web report agent';