> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Supabase

\-- 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;

$$$LANGUAGE plpgsql;

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';
$$$


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.