> ## 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 session tracking

# 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

```sql theme={null}
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

```bash theme={null}
npm install @supabase/supabase-js
```

### 2. Create Supabase Client

```typescript theme={null}
// 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

```typescript theme={null}
// 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:

```typescript theme={null}
// 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

```tsx theme={null}
// 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:

```typescript theme={null}
// 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:

```sql theme={null}
-- Enable Realtime for research_sessions table
ALTER PUBLICATION supabase_realtime ADD TABLE research_sessions;
```

***

## Fetching Session History

Get past sessions for a user:

```typescript theme={null}
// 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

```tsx theme={null}
// 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:

```typescript theme={null}
// 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

```tsx theme={null}
// 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

1. **Use Realtime for active sessions** - Polling wastes resources
2. **Stop polling when complete** - Check status before next poll
3. **Handle disconnects gracefully** - Show last known state
4. **Cache session history** - Use React Query or SWR
5. **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 |


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