supabase-agent Skill | Agent Skills

supabase-agent

Specialized agent for connecting to Supabase AI Agent database and querying AI Agent chats, messages, and analytics.

Thesis

This skill provides standardized access to the Supabase AI Agent PostgreSQL database (qwxyzmewanpgcrievqjh) with pre-configured connection methods, table schemas, and common query patterns for analyzing AI Agent conversations, user activity, and chat metrics.

Overview

The supabase-agent skill enables:

Database Connection (§1.0): Two connection methods - Python client (ea_agent_client.py) and direct PostgreSQL (psycopg2)
Table Structure (§2.0): Key tables (chats, messages, chats_extended_view, agency_repo) with field descriptions
Common Queries (§3.0): Pre-built queries for finding chats, extracting messages, analyzing conversations
URL Generation (§4.0): Correct AI Agent chat URL formats with required parameters

1.0 Database Connection

1.1 Credentials Setup

Required Environment Variables:

The skill supports two methods for loading credentials:

Method 1: Local .env file (recommended for single repo)

Add to .env in your project root:

# EA-Agent Supabase PostgreSQL Connection
EA_AGENT_POSTGRES_HOST=aws-0-us-west-1.pooler.supabase.com
EA_AGENT_POSTGRES_PORT=5432
EA_AGENT_POSTGRES_POOL_PORT=6543
EA_AGENT_POSTGRES_DATABASE=postgres
EA_AGENT_POSTGRES_USER=postgres.qwxyzmewanpgcrievqjh
EA_AGENT_POSTGRES_PASSWORD=<see .env file>

# EA-Agent Supabase API Keys (for Python client)
EA_AGENT_SUPABASE_URL=https://qwxyzmewanpgcrievqjh.supabase.co
EA_AGENT_SUPABASE_ANON_KEY=<see .env file>
EA_AGENT_SUPABASE_SERVICE_KEY=<see .env file>

Method 2: Shared .env file (recommended for multi-repo access)

Set environment variable pointing to shared .env:

# Add to ~/.zshrc or ~/.bashrc
export SUPABASE_AGENT_ENV_PATH="$PROJECT_ROOT/.env"

Or set inline:

export SUPABASE_AGENT_ENV_PATH="/path/to/chrome-extension-tcs/.env"
python your_script.py

Credential loading priority:

Environment variables (if already set)
Local .env in current directory
Shared .env from SUPABASE_AGENT_ENV_PATH

Where to find keys:

Notion: https://www.notion.so/improvado-home/Supabase-env-1e99aec6212580feaae0c9f9c0c64634
Or use Management API with SUPABASE_API_TOKEN (see 00_SUPABASE_AI_AGENT_QUERY_GUIDE.md)

1.2 Connection Method 1: Universal Supabase Client (Recommended)

Use universal client from this skill: Works from any repository, automatically loads credentials from chrome-extension-tcs/.env

Key features:

Works from ANY repository (auto-loads credentials)
Connection pooling for better performance
Context managers for safe connection handling
Built-in query helpers

Example - Use from any repository:

import sys
from pathlib import Path

# Add skill to path (works from any repo)
SKILL_PATH = Path('$PROJECT_ROOT/.claude/skills/supabase-agent')
sys.path.insert(0, str(SKILL_PATH))

from supabase_client import get_supabase_client

# Create client (automatically loads credentials from chrome-extension-tcs/.env)
client = get_supabase_client()

# Execute query
results = client.execute_query("""
    SELECT id, name, created_at, workspace_id
    FROM chats
    WHERE DATE(created_at) = CURRENT_DATE
    LIMIT 10
""")

# Results are list of dictionaries
for chat in results:
    print(f"Chat: {chat['name']} ({chat['id']})")

# List all tables
tables = client.list_tables()

# Describe table schema
schema = client.describe_table('chats')

# Close when done
client.close()

1.3 Connection Method 2: Direct PostgreSQL

Use for: Ad-hoc queries, terminal operations, debugging

Example:

import psycopg2
from psycopg2.extras import RealDictCursor
import os
from dotenv import load_dotenv

load_dotenv()

# Connect
conn = psycopg2.connect(
    host=os.getenv('EA_AGENT_POSTGRES_HOST'),
    port=int(os.getenv('EA_AGENT_POSTGRES_PORT', 5432)),
    database=os.getenv('EA_AGENT_POSTGRES_DATABASE'),
    user=os.getenv('EA_AGENT_POSTGRES_USER'),
    password=os.getenv('EA_AGENT_POSTGRES_PASSWORD')
)

# Query with dictionary cursor
cursor = conn.cursor(cursor_factory=RealDictCursor)
cursor.execute("SELECT * FROM chats WHERE DATE(created_at) = CURRENT_DATE")
results = cursor.fetchall()

cursor.close()
conn.close()

2.0 Database Structure

2.1 Main Tables

Table: chats

Chat sessions with metadata.

| Column | Type | Description | |--------|------|-------------| | id | uuid | Unique chat identifier (PRIMARY KEY) | | created_at | timestamptz | Chat creation timestamp (PST timezone) | | name | text | Chat name/title | | is_shared | boolean | Whether chat is shared | | user_id | int8 | User ID (foreign key to users table) | | workspace_id | uuid | Workspace ID | | type | text | Chat type: "chat", "schedule-agent", etc. | | metadata | jsonb | Additional metadata (scheduled_chat_id, etc.) |

Important metadata fields:

scheduled_chat_id - ID of parent scheduled chat
is_scheduled_result - true if this is a scheduled run result
summary_generated_at - timestamp of summary generation

Table: messages

Chat messages with content stored in parts array.

| Column | Type | Description | |--------|------|-------------| | id | int4 | Auto-increment message ID | | created_at | timestamptz | Message creation timestamp | | message_id | text | UUID message identifier | | chat_id | uuid | Chat ID (foreign key to chats) | | sender | text | "user" or "assistant" | | message_text | text | Message text (often NULL, see parts) | | is_accurate | boolean | Message accuracy flag | | metadata | jsonb | Message metadata (usage, model, etc.) | | parts | jsonb | Main content storage (array of message parts) | | error | text | Error message if any | | type | text | Message type: "chat", etc. | | feedback_label | text | User feedback label |

CRITICAL: Content is stored in parts array, NOT in message_text field!

Part types:

"text" - Plain text content
"tool-invocation" - Tool calls (clickhouseTool, etc.)
"step-start" - Step markers
"code" - Code blocks

Table: chats_extended_view

View that joins chats with user and agency information.

Additional fields beyond chats table:

agency_id - Agency ID from users table
user_email - User email address
user_name - User full name

Use this view for queries that need agency_id or user_email.

Table: agency_repo

Agency registry with repository folder mappings.

| Column | Type | Description | |--------|------|-------------| | id | bigint | Auto-increment ID (PRIMARY KEY) | | agency_id | bigint | Agency ID (maps to ClickHouse agency_id) | | repo_folder_url | text | Git repository folder path | | sandbox_template_id | text | Sandbox template identifier | | created_at | timestamptz | Registration timestamp |

NOTE: Agency names are NOT stored in this table. To get agency names, query ClickHouse internal_analytics.src_dts_dsas_extraction_agency table.

Common Agency IDs:

ExampleClient: 10836
ExampleClient: 7665
Improvado: 1

2.2 Message Parts Structure

Parsing message content:

# Extract text from message parts
for message in messages:
    for part in message.get('parts', []):
        if part.get('type') == 'text':
            print(part.get('text'))

        elif part.get('type') == 'tool-invocation':
            tool_name = part.get('toolInvocation', {}).get('toolName')
            tool_result = part.get('toolInvocation', {}).get('result', {}).get('data')

            if tool_name == 'clickhouseTool':
                # Parse SQL results from tool_result
                print(f"SQL Result: {tool_result}")

3.0 Common Queries

3.1 Find Chats by Agency

from data_sources.supabase.ea_agent_client import EAAgentClient

client = EAAgentClient(use_pool=True)

# NOTE: Agency names are in ClickHouse, not Supabase
# Use known agency_id or query ClickHouse first
agency_id = XXXX  # ExampleClient

# Get today's chats for agency
chats = client.execute_query("""
    SELECT
        c.id as chat_id,
        c.created_at,
        c.workspace_id,
        c.name as chat_name,
        ce.agency_id,
        ce.user_email
    FROM chats c
    LEFT JOIN chats_extended_view ce ON c.id = ce.id
    WHERE ce.agency_id = %s
    AND DATE(c.created_at) = CURRENT_DATE
    ORDER BY c.created_at DESC
""", (agency_id,))

client.close()

3.2 Extract Messages from Chat

from data_sources.supabase.ea_agent_client import EAAgentClient

client = EAAgentClient(use_pool=True)

chat_id = "your-chat-uuid"

# Get all messages with parts
messages = client.execute_query("""
    SELECT
        id,
        message_id,
        sender,
        parts,
        created_at
    FROM messages
    WHERE chat_id = %s
    ORDER BY created_at ASC
""", (chat_id,))

# Parse conversation
conversation = []
for msg in messages:
    sender = msg['sender']

    # Extract text from parts
    text_parts = []
    for part in msg.get('parts', []):
        if part.get('type') == 'text':
            text_parts.append(part.get('text'))

    if text_parts:
        conversation.append({
            'sender': sender,
            'text': '\n'.join(text_parts),
            'timestamp': msg['created_at']
        })

client.close()

3.3 Find Scheduled Chat Runs

from data_sources.supabase.ea_agent_client import EAAgentClient

client = EAAgentClient(use_pool=True)

# Find all scheduled chat results for a specific scheduled_chat_id
scheduled_chat_id = "your-scheduled-chat-id"

results = client.execute_query("""
    SELECT
        id as chat_id,
        created_at,
        name,
        metadata->>'scheduled_chat_id' as scheduled_chat_id,
        workspace_id
    FROM chats
    WHERE metadata->>'scheduled_chat_id' = %s
    ORDER BY created_at DESC
""", (scheduled_chat_id,))

client.close()

3.4 Get User Activity

from data_sources.supabase.ea_agent_client import EAAgentClient

client = EAAgentClient(use_pool=True)

# Find all chats by user email
user_email = "user@example.com"

chats = client.execute_query("""
    SELECT
        c.id,
        c.name,
        c.created_at,
        c.workspace_id,
        ce.agency_id,
        ce.user_email
    FROM chats c
    JOIN chats_extended_view ce ON c.id = ce.id
    WHERE ce.user_email = %s
    ORDER BY c.created_at DESC
    LIMIT 20
""", (user_email,))

client.close()

3.5 Analyze Tool Usage

from data_sources.supabase.ea_agent_client import EAAgentClient

client = EAAgentClient(use_pool=True)

# Count tool invocations by type
results = client.execute_query("""
    SELECT
        part->>'toolInvocation'->>'toolName' as tool_name,
        COUNT(*) as invocation_count
    FROM messages,
    LATERAL jsonb_array_elements(parts) as part
    WHERE part->>'type' = 'tool-invocation'
    AND DATE(created_at) >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY tool_name
    ORDER BY invocation_count DESC
""")

client.close()

4.0 Chat URL Generation

4.1 Correct URL Format

CRITICAL: Chat URLs require BOTH agency_id and workspace_id parameters.

# Generate chat URL
def generate_chat_url(chat_id: str, agency_id: int, workspace_id: str) -> str:
    """Generate AI Agent chat URL with required parameters."""
    return f"https://report.improvado.io/experimental/agent/assistant/{chat_id}/?agency_id={agency_id}&workspace_id={workspace_id}"

# Example
chat_url = generate_chat_url(
    chat_id="550e8400-e29b-41d4-a716-446655440000",
    agency_id=XXXX,
    workspace_id="9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d"
)

5.0 Using From Other Repositories

5.1 Simple Template Script

Copy this template to ANY project - it will automatically load credentials:

#!/usr/bin/env python3
"""
Query Supabase AI Agent from any repository.
Automatically loads credentials from chrome-extension-tcs/.env
"""

import sys
from pathlib import Path

# Add supabase-agent skill to path
SKILL_PATH = Path('$PROJECT_ROOT/.claude/skills/supabase-agent')
sys.path.insert(0, str(SKILL_PATH))

from supabase_client import get_supabase_client


def main():
    # Create client (auto-loads credentials)
    client = get_supabase_client()

    try:
        # Your queries here
        results = client.execute_query("""
            SELECT * FROM chats
            WHERE DATE(created_at) = CURRENT_DATE
            LIMIT 10
        """)

        for row in results:
            print(row)

    finally:
        client.close()


if __name__ == "__main__":
    main()

Copy pre-made example:

# Copy simple example to your project
cp $PROJECT_ROOT/.claude/skills/supabase-agent/example_simple.py ./

# Run from your project
python example_simple.py

5.2 Usage Without Adding to sys.path

Option: Use Python environment variable:

# Set PYTHONPATH to include skill directory
export PYTHONPATH="$PROJECT_ROOT/.claude/skills/supabase-agent:$PYTHONPATH"

# Now you can import directly
python -c "from supabase_client import get_supabase_client; print('✅ Works!')"

Add to ~/.zshrc or ~/.bashrc for permanent access:

# Add to shell config
echo 'export PYTHONPATH="$PROJECT_ROOT/.claude/skills/supabase-agent:$PYTHONPATH"' >> ~/.zshrc

6.0 Complete Example Scripts

6.1 Find Today's Chats for Client

#!/usr/bin/env python3
"""
Find and display today's AI Agent chats for a specific client.
Usage: python find_client_chats.py "ExampleClient"
"""

import sys
from data_sources.supabase.ea_agent_client import EAAgentClient

def find_client_chats(client_name: str):
    """Find today's chats for a client by name."""

    client = EAAgentClient(use_pool=True)

    try:
        # Step 1: Find agency
        print(f"🔍 Searching for agency: {client_name}")
        agencies = client.execute_query("""
            SELECT agency_id, agency_name
            FROM agency_repo
            WHERE LOWER(agency_name) LIKE %s
        """, (f'%{client_name.lower()}%',))

        if not agencies:
            print(f"❌ No agency found matching '{client_name}'")
            return

        agency = agencies[0]
        print(f"✅ Found agency: {agency['agency_name']} (ID: {agency['agency_id']})")

        # Step 2: Get today's chats
        print(f"\n📅 Fetching today's chats...")
        chats = client.execute_query("""
            SELECT
                c.id as chat_id,
                c.created_at,
                c.workspace_id,
                c.name as chat_name,
                ce.user_email
            FROM chats c
            LEFT JOIN chats_extended_view ce ON c.id = ce.id
            WHERE ce.agency_id = %s
            AND DATE(c.created_at) = CURRENT_DATE
            ORDER BY c.created_at DESC
        """, (agency['agency_id'],))

        if not chats:
            print(f"❌ No chats found for today")
            return

        print(f"\n✅ Found {len(chats)} chat(s):\n")

        # Display chats with URLs
        for i, chat in enumerate(chats, 1):
            chat_url = f"https://report.improvado.io/experimental/agent/assistant/{chat['chat_id']}/?agency_id={agency['agency_id']}&workspace_id={chat['workspace_id']}"

            print(f"{i}. {chat['chat_name']}")
            print(f"   Created: {chat['created_at']}")
            print(f"   User: {chat['user_email']}")
            print(f"   URL: {chat_url}\n")

    finally:
        client.close()

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python find_client_chats.py 'Client Name'")
        sys.exit(1)

    find_client_chats(sys.argv[1])

6.2 Extract Full Conversation

#!/usr/bin/env python3
"""
Extract full conversation from AI Agent chat.
Usage: python extract_conversation.py <chat_id>
"""

import sys
from data_sources.supabase.ea_agent_client import EAAgentClient

def extract_conversation(chat_id: str):
    """Extract and display full conversation from chat."""

    client = EAAgentClient(use_pool=True)

    try:
        # Get chat info
        chat_info = client.execute_query("""
            SELECT name, created_at, workspace_id
            FROM chats
            WHERE id = %s
        """, (chat_id,))

        if not chat_info:
            print(f"❌ Chat not found: {chat_id}")
            return

        chat = chat_info[0]
        print(f"💬 Chat: {chat['name']}")
        print(f"📅 Created: {chat['created_at']}\n")
        print("=" * 80 + "\n")

        # Get messages
        messages = client.execute_query("""
            SELECT
                sender,
                parts,
                created_at
            FROM messages
            WHERE chat_id = %s
            ORDER BY created_at ASC
        """, (chat_id,))

        # Display conversation
        for msg in messages:
            sender = "👤 USER" if msg['sender'] == 'user' else "🤖 ASSISTANT"
            print(f"{sender} [{msg['created_at']}]")
            print("-" * 80)

            # Extract text from parts
            for part in msg.get('parts', []):
                if part.get('type') == 'text':
                    print(part.get('text'))
                elif part.get('type') == 'tool-invocation':
                    tool_name = part.get('toolInvocation', {}).get('toolName')
                    print(f"\n[Tool: {tool_name}]")

            print("\n")

    finally:
        client.close()

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python extract_conversation.py <chat_id>")
        sys.exit(1)

    extract_conversation(sys.argv[1])

7.0 Related Documentation

Complete Supabase guide: $PROJECT_ROOT/00_SUPABASE_AI_AGENT_QUERY_GUIDE.md
Search guide: $PROJECT_ROOT/data_sources/supabase/01_search_ai_agent_chats_guide.md
Agency repo guide: $PROJECT_ROOT/data_sources/supabase/00_agency_repo_guide.md
Python client: $PROJECT_ROOT/data_sources/supabase/ea_agent_client.py

Author Checklist

[x] Thesis states specific outcome (standardized Supabase access)
[x] Overview introduces all MECE sections (1.0-6.0)
[x] Sections follow ordering principle (connection → structure → queries → examples)
[x] Numbered paragraphs (¶) for cross-referencing
[x] Code examples tested and functional
[x] Referenced documentation linked
[x] MECE structure maintained (mutually exclusive sections)

Agent Skills: supabase-agent

Install this agent skill to your local

Skill Files