Supabase Session Tracking Guide

This guide explains how to track research session progress using the research_sessions table in Supabase, enabling live progress updates and session history in your Next.js frontend.

Overview

The Deep Research API logs session progress to Supabase’s research_sessions table:

Live progress tracking - See current phase and tool call counts
Resume after disconnect - Reconnect and see where the analysis is
Session history - View past research sessions
Supabase Realtime - Subscribe to live updates without polling

Table Schema

CREATE TABLE research_sessions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  clerk_id TEXT NOT NULL,
  session_id TEXT NOT NULL UNIQUE,      -- Matches S3 session_id
  question TEXT NOT NULL,                -- Original research question
  status TEXT NOT NULL DEFAULT 'running' 
    CHECK (status IN ('running', 'completed', 'failed', 'cancelled')),
  
  -- Progress tracking
  current_phase VARCHAR(20),             -- planner, executor, web_report, report
  current_tool VARCHAR(100),             -- Currently executing tool
  planner_tool_calls INTEGER DEFAULT 0,
  executor_tool_calls INTEGER DEFAULT 0,
  web_report_tool_calls INTEGER DEFAULT 0,
  report_tool_calls INTEGER DEFAULT 0,
  total_tool_calls INTEGER DEFAULT 0,
  
  -- Completion data
  s3_key TEXT,                           -- S3 path to report_web.md
  duration_seconds INTEGER,
  error_message TEXT,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  last_activity_at TIMESTAMPTZ DEFAULT NOW()
);

Frontend Integration (Next.js)

1. Install Supabase Client

npm install @supabase/supabase-js

2. Create Supabase Client

// lib/supabase.ts
import { createClient } from "@supabase/supabase-js";

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;

export const supabase = createClient(supabaseUrl, supabaseAnonKey);

3. Session Types

// types/research.ts
export interface ResearchSession {
  id: string;
  clerk_id: string;
  session_id: string;
  question: string;
  status: "running" | "completed" | "failed" | "cancelled";
  current_phase: string | null;
  current_tool: string | null;
  planner_tool_calls: number;
  executor_tool_calls: number;
  web_report_tool_calls: number;
  report_tool_calls: number;
  total_tool_calls: number;
  s3_key: string | null;
  duration_seconds: number | null;
  error_message: string | null;
  created_at: string;
  updated_at: string;
  completed_at: string | null;
  last_activity_at: string;
}

Option 1: Polling (Simple)

Use polling to check session status at regular intervals:

// hooks/useSessionPolling.ts
import { useState, useEffect, useCallback } from "react";
import { supabase } from "@/lib/supabase";
import type { ResearchSession } from "@/types/research";

export function useSessionPolling(sessionId: string, intervalMs = 2000) {
  const [session, setSession] = useState<ResearchSession | null>(null);
  const [loading, setLoading] = useState(true);
  const [error, setError] = useState<string | null>(null);

  const fetchSession = useCallback(async () => {
    try {
      const { data, error } = await supabase
        .from("research_sessions")
        .select("*")
        .eq("session_id", sessionId)
        .single();

      if (error) throw error;
      setSession(data);
      return data;
    } catch (err) {
      setError(err instanceof Error ? err.message : "Failed to fetch session");
      return null;
    } finally {
      setLoading(false);
    }
  }, [sessionId]);

  useEffect(() => {
    // Initial fetch
    fetchSession();

    // Set up polling
    const interval = setInterval(async () => {
      const data = await fetchSession();
      // Stop polling when session is complete
      if (data?.status && data.status !== "running") {
        clearInterval(interval);
      }
    }, intervalMs);

    return () => clearInterval(interval);
  }, [fetchSession, intervalMs]);

  return { session, loading, error, refetch: fetchSession };
}

Usage in Component

// components/SessionProgress.tsx
"use client";

import { useSessionPolling } from "@/hooks/useSessionPolling";

interface SessionProgressProps {
  sessionId: string;
}

export function SessionProgress({ sessionId }: SessionProgressProps) {
  const { session, loading, error } = useSessionPolling(sessionId);

  if (loading) return <div>Loading...</div>;
  if (error) return <div>Error: {error}</div>;
  if (!session) return <div>Session not found</div>;

  const totalCalls = 
    session.planner_tool_calls + 
    session.executor_tool_calls + 
    session.web_report_tool_calls + 
    session.report_tool_calls;

  return (
    <div className="space-y-4 p-4 border rounded-lg">
      <div className="flex justify-between items-center">
        <h3 className="font-semibold">Research Progress</h3>
        <StatusBadge status={session.status} />
      </div>

      <div className="text-sm text-gray-600">
        <p><strong>Question:</strong> {session.question}</p>
        <p><strong>Phase:</strong> {session.current_phase || "Initializing..."}</p>
        {session.current_tool && (
          <p><strong>Current Tool:</strong> {session.current_tool}</p>
        )}
      </div>

      <div className="grid grid-cols-4 gap-2 text-center">
        <PhaseBox 
          label="Planner" 
          count={session.planner_tool_calls} 
          active={session.current_phase === "planner"}
        />
        <PhaseBox 
          label="Executor" 
          count={session.executor_tool_calls}
          active={session.current_phase === "executor"}
        />
        <PhaseBox 
          label="Web Report" 
          count={session.web_report_tool_calls}
          active={session.current_phase === "web_report"}
        />
        <PhaseBox 
          label="PDF Report" 
          count={session.report_tool_calls}
          active={session.current_phase === "report"}
        />
      </div>

      <div className="text-sm">
        <strong>Total Tool Calls:</strong> {totalCalls}
      </div>

      {session.status === "completed" && session.s3_key && (
        <div className="mt-4 p-3 bg-green-50 rounded">
          ✅ Report ready! <a href={session.s3_key} className="text-blue-600">View Report</a>
        </div>
      )}

      {session.status === "failed" && session.error_message && (
        <div className="mt-4 p-3 bg-red-50 rounded text-red-700">
          ❌ {session.error_message}
        </div>
      )}
    </div>
  );
}

function StatusBadge({ status }: { status: string }) {
  const colors = {
    running: "bg-blue-100 text-blue-800",
    completed: "bg-green-100 text-green-800",
    failed: "bg-red-100 text-red-800",
    cancelled: "bg-gray-100 text-gray-800",
  };
  return (
    <span className={`px-2 py-1 rounded text-xs ${colors[status] || colors.running}`}>
      {status.toUpperCase()}
    </span>
  );
}

function PhaseBox({ label, count, active }: { label: string; count: number; active: boolean }) {
  return (
    <div className={`p-2 rounded ${active ? "bg-blue-100 border-2 border-blue-500" : "bg-gray-100"}`}>
      <div className="text-xs text-gray-500">{label}</div>
      <div className="font-bold">{count}</div>
    </div>
  );
}

Option 2: Supabase Realtime (Recommended)

Subscribe to real-time updates for instant progress tracking:

// hooks/useSessionRealtime.ts
import { useState, useEffect } from "react";
import { supabase } from "@/lib/supabase";
import type { ResearchSession } from "@/types/research";
import type { RealtimeChannel } from "@supabase/supabase-js";

export function useSessionRealtime(sessionId: string) {
  const [session, setSession] = useState<ResearchSession | null>(null);
  const [loading, setLoading] = useState(true);
  const [error, setError] = useState<string | null>(null);

  useEffect(() => {
    let channel: RealtimeChannel;

    async function setup() {
      // 1. Initial fetch
      const { data, error: fetchError } = await supabase
        .from("research_sessions")
        .select("*")
        .eq("session_id", sessionId)
        .single();

      if (fetchError) {
        setError(fetchError.message);
        setLoading(false);
        return;
      }

      setSession(data);
      setLoading(false);

      // 2. Subscribe to realtime updates
      channel = supabase
        .channel(`session:${sessionId}`)
        .on(
          "postgres_changes",
          {
            event: "UPDATE",
            schema: "public",
            table: "research_sessions",
            filter: `session_id=eq.${sessionId}`,
          },
          (payload) => {
            console.log("Realtime update:", payload);
            setSession(payload.new as ResearchSession);
          }
        )
        .subscribe((status) => {
          console.log("Subscription status:", status);
        });
    }

    setup();

    return () => {
      if (channel) {
        supabase.removeChannel(channel);
      }
    };
  }, [sessionId]);

  return { session, loading, error };
}

Enable Realtime in Supabase

Run this SQL in Supabase Dashboard:

-- Enable Realtime for research_sessions table
ALTER PUBLICATION supabase_realtime ADD TABLE research_sessions;

Fetching Session History

Get past sessions for a user:

// hooks/useSessionHistory.ts
import { useState, useEffect } from "react";
import { supabase } from "@/lib/supabase";
import type { ResearchSession } from "@/types/research";

export function useSessionHistory(clerkId: string, limit = 10) {
  const [sessions, setSessions] = useState<ResearchSession[]>([]);
  const [loading, setLoading] = useState(true);
  const [error, setError] = useState<string | null>(null);

  useEffect(() => {
    async function fetchSessions() {
      try {
        const { data, error } = await supabase
          .from("research_sessions")
          .select("*")
          .eq("clerk_id", clerkId)
          .order("created_at", { ascending: false })
          .limit(limit);

        if (error) throw error;
        setSessions(data || []);
      } catch (err) {
        setError(err instanceof Error ? err.message : "Failed to fetch sessions");
      } finally {
        setLoading(false);
      }
    }

    fetchSessions();
  }, [clerkId, limit]);

  return { sessions, loading, error };
}

Session History Component

// components/SessionHistory.tsx
"use client";

import { useSessionHistory } from "@/hooks/useSessionHistory";
import { formatDistanceToNow } from "date-fns";

interface SessionHistoryProps {
  clerkId: string;
  onSelectSession: (sessionId: string) => void;
}

export function SessionHistory({ clerkId, onSelectSession }: SessionHistoryProps) {
  const { sessions, loading, error } = useSessionHistory(clerkId);

  if (loading) return <div>Loading history...</div>;
  if (error) return <div>Error: {error}</div>;

  return (
    <div className="space-y-2">
      <h3 className="font-semibold">Recent Research Sessions</h3>
      {sessions.length === 0 ? (
        <p className="text-gray-500">No sessions yet</p>
      ) : (
        <ul className="space-y-2">
          {sessions.map((session) => (
            <li
              key={session.id}
              className="p-3 border rounded hover:bg-gray-50 cursor-pointer"
              onClick={() => onSelectSession(session.session_id)}
            >
              <div className="flex justify-between items-start">
                <div className="flex-1">
                  <p className="font-medium truncate">{session.question}</p>
                  <p className="text-sm text-gray-500">
                    {formatDistanceToNow(new Date(session.created_at), { addSuffix: true })}
                  </p>
                </div>
                <StatusBadge status={session.status} />
              </div>
              {session.status === "completed" && (
                <p className="text-sm text-gray-600 mt-1">
                  {session.total_tool_calls} tool calls • {session.duration_seconds}s
                </p>
              )}
            </li>
          ))}
        </ul>
      )}
    </div>
  );
}

Check for Active Sessions

Before starting a new analysis, check if user has one running:

// lib/sessions.ts
import { supabase } from "@/lib/supabase";

export async function getActiveSession(clerkId: string) {
  const { data, error } = await supabase
    .from("research_sessions")
    .select("*")
    .eq("clerk_id", clerkId)
    .eq("status", "running")
    .order("created_at", { ascending: false })
    .limit(1)
    .single();

  if (error && error.code !== "PGRST116") {
    // PGRST116 = no rows returned
    throw error;
  }

  return data;
}

Resume Active Session

// components/AnalysisStarter.tsx
"use client";

import { useState, useEffect } from "react";
import { getActiveSession } from "@/lib/sessions";
import { SessionProgress } from "./SessionProgress";

export function AnalysisStarter({ clerkId }: { clerkId: string }) {
  const [activeSessionId, setActiveSessionId] = useState<string | null>(null);
  const [checking, setChecking] = useState(true);

  useEffect(() => {
    async function checkActive() {
      const session = await getActiveSession(clerkId);
      if (session) {
        setActiveSessionId(session.session_id);
      }
      setChecking(false);
    }
    checkActive();
  }, [clerkId]);

  if (checking) return <div>Checking for active sessions...</div>;

  if (activeSessionId) {
    return (
      <div>
        <div className="mb-4 p-3 bg-yellow-50 rounded">
          ⏳ You have an analysis in progress
        </div>
        <SessionProgress sessionId={activeSessionId} />
      </div>
    );
  }

  return <AnalysisForm clerkId={clerkId} />;
}

Best Practices

Use Realtime for active sessions - Polling wastes resources
Stop polling when complete - Check status before next poll
Handle disconnects gracefully - Show last known state
Cache session history - Use React Query or SWR
Implement timeout detection - Check last_activity_at for stale sessions

Troubleshooting

Issue	Solution
Realtime not updating	Ensure table is in `supabase_realtime` publication
”permission denied”	Check RLS policies allow reading sessions
Session not found	Verify session_id matches exactly
Stale data	Increase polling frequency or use Realtime

Product Guide

Account

Help

Supabase session tracking

Supabase Session Tracking Guide

Overview

Table Schema

Frontend Integration (Next.js)

1. Install Supabase Client

2. Create Supabase Client

3. Session Types

Option 1: Polling (Simple)

Usage in Component

Option 2: Supabase Realtime (Recommended)

Enable Realtime in Supabase

Fetching Session History

Session History Component

Check for Active Sessions

Resume Active Session

Best Practices

Troubleshooting

​Supabase Session Tracking Guide

​Overview

​Table Schema

​Frontend Integration (Next.js)

​1. Install Supabase Client

​2. Create Supabase Client

​3. Session Types

​Option 1: Polling (Simple)

​Usage in Component

​Option 2: Supabase Realtime (Recommended)

​Enable Realtime in Supabase

​Fetching Session History

​Session History Component

​Check for Active Sessions

​Resume Active Session

​Best Practices

​Troubleshooting

Supabase Session Tracking Guide

Overview

Table Schema

Frontend Integration (Next.js)

1. Install Supabase Client

2. Create Supabase Client

3. Session Types

Option 1: Polling (Simple)

Usage in Component

Option 2: Supabase Realtime (Recommended)

Enable Realtime in Supabase

Fetching Session History

Session History Component

Check for Active Sessions

Resume Active Session

Best Practices

Troubleshooting