packages/core/src/utils/supabase.py

# ============================================================================
# agent verf - Supabase Client
# Version: 0.1.0
# Last Updated: 2026-01-19
#
# Database client for Supabase (PostgreSQL + real-time + auth).
#
# Purpose:
#   - Store and retrieve verification receipts
#   - Store evidence linked to receipts
#   - Query recent verifications for the feed
#   - Handle all database operations with proper error handling
#
# Usage:
#   client = get_supabase_client()
#   receipt = await client.store_receipt(receipt_data)
#   receipt = await client.get_receipt(receipt_id)
#
# Setup:
#   1. Create Supabase project at https://supabase.com
#   2. Run supabase_migration.sql in SQL Editor
#   3. Copy URL and keys to .env.local
# ============================================================================

import os
import structlog
from datetime import datetime
from typing import Optional, List
from uuid import UUID

from supabase import create_client, Client
from pydantic import BaseModel

logger = structlog.get_logger()


# ============================================================================
# Data Models (matching database schema)
# ============================================================================

class ReceiptInsert(BaseModel):
    """Data for inserting a receipt into the database."""
    id: Optional[UUID] = None
    source_url: Optional[str] = None
    source_platform: Optional[str] = None
    content_type: Optional[str] = None
    content_text: Optional[str] = None
    verdict_status: str
    verdict_confidence: Optional[float] = None
    verdict_summary: Optional[str] = None
    verdict_reasoning: Optional[str] = None
    key_findings: Optional[list] = None
    primary_lens: Optional[str] = None
    sources_checked: int = 0
    processing_time_ms: Optional[int] = None
    model_used: Optional[str] = None
    tools_used: Optional[List[str]] = None
    cost_usd: float = 0.0
    request_platform: Optional[str] = None
    scan_mode: str = "quick_scan"


class ReceiptRow(BaseModel):
    """Receipt row from the database."""
    id: UUID
    created_at: datetime
    source_url: Optional[str]
    source_platform: Optional[str]
    content_type: Optional[str]
    content_text: Optional[str]
    verdict_status: str
    verdict_confidence: Optional[float]
    verdict_summary: Optional[str]
    verdict_reasoning: Optional[str]
    key_findings: Optional[list]
    primary_lens: Optional[str]
    sources_checked: int
    processing_time_ms: Optional[int]
    model_used: Optional[str]
    tools_used: Optional[List[str]]
    cost_usd: float
    request_platform: Optional[str]
    scan_mode: str


class EvidenceInsert(BaseModel):
    """Data for inserting evidence into the database."""
    receipt_id: UUID
    claim: Optional[str] = None
    finding: Optional[str] = None
    supports_verdict: Optional[bool] = None
    source_url: Optional[str] = None
    source_domain: Optional[str] = None
    source_title: Optional[str] = None
    authority_score: Optional[float] = None


class EvidenceRow(BaseModel):
    """Evidence row from the database."""
    id: UUID
    receipt_id: UUID
    claim: Optional[str]
    finding: Optional[str]
    supports_verdict: Optional[bool]
    source_url: Optional[str]
    source_domain: Optional[str]
    source_title: Optional[str]
    authority_score: Optional[float]
    created_at: datetime


# ============================================================================
# Supabase Client
# ============================================================================

class SupabaseClient:
    """
    Client for Supabase database operations.

    Handles all database interactions for receipts and evidence.
    Uses the service role key for server-side operations.

    Example:
        client = SupabaseClient()

        # Store a receipt
        receipt = await client.store_receipt(ReceiptInsert(
            verdict_status="TRUE",
            verdict_confidence=0.95,
            verdict_summary="This claim is accurate.",
        ))

        # Get a receipt
        receipt = await client.get_receipt(receipt_id)

        # Get recent verifications
        recent = await client.get_recent_verifications(limit=10)
    """

    def __init__(self):
        """Initialize Supabase client from environment variables."""
        self._url = os.getenv("SUPABASE_URL")
        self._service_key = os.getenv("SUPABASE_SERVICE_KEY")
        self._anon_key = os.getenv("SUPABASE_ANON_KEY")

        if not self._url:
            logger.warning("SUPABASE_URL not set - database operations will fail")
            self._client = None
            return

        # Use service key for server-side operations (bypasses RLS)
        key = self._service_key or self._anon_key
        if not key:
            logger.warning("No Supabase key set - database operations will fail")
            self._client = None
            return

        try:
            self._client: Client = create_client(self._url, key)
            logger.info(
                "Supabase client initialized",
                url=self._url[:30] + "...",
                using_service_key=bool(self._service_key),
            )
        except Exception as e:
            logger.error("Failed to initialize Supabase client", error=str(e))
            self._client = None

    @property
    def is_configured(self) -> bool:
        """Check if Supabase is properly configured."""
        return self._client is not None

    # -------------------------------------------------------------------------
    # Receipt Operations
    # -------------------------------------------------------------------------

    async def store_receipt(self, data: ReceiptInsert) -> Optional[ReceiptRow]:
        """
        Store a verification receipt in the database.

        Args:
            data: Receipt data to store

        Returns:
            The stored receipt with generated ID and timestamp, or None on failure
        """
        if not self._client:
            logger.error("Cannot store receipt - Supabase not configured")
            return None

        try:
            # Convert to dict, excluding None values for optional fields
            insert_data = data.model_dump(exclude_none=True)

            # Insert and return the created row
            result = self._client.table("receipts").insert(insert_data).execute()

            if result.data and len(result.data) > 0:
                row = ReceiptRow(**result.data[0])
                logger.info(
                    "Receipt stored",
                    receipt_id=str(row.id),
                    verdict=row.verdict_status,
                )
                return row
            else:
                logger.error("Receipt insert returned no data")
                return None

        except Exception as e:
            logger.error("Failed to store receipt", error=str(e))
            return None

    async def get_receipt(self, receipt_id: UUID) -> Optional[ReceiptRow]:
        """
        Get a receipt by ID.

        Args:
            receipt_id: The receipt UUID

        Returns:
            The receipt if found, None otherwise
        """
        if not self._client:
            logger.error("Cannot get receipt - Supabase not configured")
            return None

        try:
            result = (
                self._client.table("receipts")
                .select("*")
                .eq("id", str(receipt_id))
                .execute()
            )

            if result.data and len(result.data) > 0:
                return ReceiptRow(**result.data[0])
            return None

        except Exception as e:
            logger.error("Failed to get receipt", receipt_id=str(receipt_id), error=str(e))
            return None

    async def get_recent_verifications(
        self,
        limit: int = 20,
        offset: int = 0,
    ) -> List[ReceiptRow]:
        """
        Get recent verification receipts.

        Args:
            limit: Maximum number of receipts to return
            offset: Number of receipts to skip (for pagination)

        Returns:
            List of receipts ordered by creation time (newest first)
        """
        if not self._client:
            logger.error("Cannot get recent verifications - Supabase not configured")
            return []

        try:
            result = (
                self._client.table("receipts")
                .select("*")
                .order("created_at", desc=True)
                .range(offset, offset + limit - 1)
                .execute()
            )

            return [ReceiptRow(**row) for row in result.data]

        except Exception as e:
            logger.error("Failed to get recent verifications", error=str(e))
            return []

    async def count_receipts(self) -> int:
        """Get total number of receipts."""
        if not self._client:
            return 0

        try:
            result = (
                self._client.table("receipts")
                .select("id", count="exact")
                .execute()
            )
            return result.count or 0
        except Exception as e:
            logger.error("Failed to count receipts", error=str(e))
            return 0

    # -------------------------------------------------------------------------
    # Evidence Operations
    # -------------------------------------------------------------------------

    async def store_evidence(self, data: EvidenceInsert) -> Optional[EvidenceRow]:
        """
        Store evidence linked to a receipt.

        Args:
            data: Evidence data to store

        Returns:
            The stored evidence with generated ID, or None on failure
        """
        if not self._client:
            logger.error("Cannot store evidence - Supabase not configured")
            return None

        try:
            insert_data = data.model_dump(exclude_none=True)
            result = self._client.table("evidence").insert(insert_data).execute()

            if result.data and len(result.data) > 0:
                return EvidenceRow(**result.data[0])
            return None

        except Exception as e:
            logger.error(
                "Failed to store evidence",
                receipt_id=str(data.receipt_id),
                error=str(e),
            )
            return None

    async def store_evidence_batch(
        self,
        evidence_list: List[EvidenceInsert],
    ) -> List[EvidenceRow]:
        """
        Store multiple evidence items in a single batch.

        Args:
            evidence_list: List of evidence items to store

        Returns:
            List of stored evidence items
        """
        if not self._client:
            logger.error("Cannot store evidence batch - Supabase not configured")
            return []

        if not evidence_list:
            return []

        try:
            insert_data = [e.model_dump(exclude_none=True) for e in evidence_list]
            result = self._client.table("evidence").insert(insert_data).execute()

            return [EvidenceRow(**row) for row in result.data]

        except Exception as e:
            logger.error("Failed to store evidence batch", error=str(e))
            return []

    async def get_evidence_for_receipt(self, receipt_id: UUID) -> List[EvidenceRow]:
        """
        Get all evidence linked to a receipt.

        Args:
            receipt_id: The receipt UUID

        Returns:
            List of evidence items
        """
        if not self._client:
            return []

        try:
            result = (
                self._client.table("evidence")
                .select("*")
                .eq("receipt_id", str(receipt_id))
                .execute()
            )

            return [EvidenceRow(**row) for row in result.data]

        except Exception as e:
            logger.error(
                "Failed to get evidence",
                receipt_id=str(receipt_id),
                error=str(e),
            )
            return []

    # -------------------------------------------------------------------------
    # Combined Operations
    # -------------------------------------------------------------------------

    async def get_receipt_with_evidence(
        self,
        receipt_id: UUID,
    ) -> Optional[tuple[ReceiptRow, List[EvidenceRow]]]:
        """
        Get a receipt with all its evidence.

        Args:
            receipt_id: The receipt UUID

        Returns:
            Tuple of (receipt, evidence_list) or None if not found
        """
        receipt = await self.get_receipt(receipt_id)
        if not receipt:
            return None

        evidence = await self.get_evidence_for_receipt(receipt_id)
        return (receipt, evidence)


# ============================================================================
# Singleton Instance
# ============================================================================

_supabase_client: Optional[SupabaseClient] = None


def get_supabase_client() -> SupabaseClient:
    """
    Get the singleton Supabase client instance.

    Usage:
        client = get_supabase_client()
        receipt = await client.store_receipt(...)
    """
    global _supabase_client
    if _supabase_client is None:
        _supabase_client = SupabaseClient()
    return _supabase_client