Please add a debug summary helper in app/db.py and expose it via a new FastAPI endpoint in app/main.py.

1) Update app/db.py

Open app/db.py and add a new helper function near the other debug helpers (below get_conversation_with_messages and list_conversations_for_customer is fine):

async def get_conversation_summary() -> dict:
    """
    Return a summary of conversations by stage, status, and campaign.
    """
    conn = await get_conn()
    try:
        # Total conversations
        total_row = await conn.fetchrow(
            "SELECT COUNT(*) AS total FROM dp_conversations"
        )
        total = total_row["total"] if total_row else 0

        # By status
        status_rows = await conn.fetch(
            """
            SELECT status, COUNT(*) AS count
            FROM dp_conversations
            GROUP BY status
            ORDER BY status
            """
        )
        by_status = {row["status"] or "unknown": row["count"] for row in status_rows}

        # By stage
        stage_rows = await conn.fetch(
            """
            SELECT stage, COUNT(*) AS count
            FROM dp_conversations
            GROUP BY stage
            ORDER BY stage
            """
        )
        by_stage = {row["stage"] or "unknown": row["count"] for row in stage_rows}

        # By campaign
        campaign_rows = await conn.fetch(
            """
            SELECT campaign, COUNT(*) AS count
            FROM dp_conversations
            GROUP BY campaign
            ORDER BY campaign
            """
        )
        by_campaign = {row["campaign"] or "unknown": row["count"] for row in campaign_rows}

        return {
            "total_conversations": total,
            "by_status": by_status,
            "by_stage": by_stage,
            "by_campaign": by_campaign,
        }
    finally:
        await conn.close()

2) Update app/main.py

Make sure get_conversation_summary is imported at the top alongside the other db helpers. Extend the existing db import line like this (adjusting to whatever is already there):

from app.db import (
    ...,
    get_conversation_with_messages,
    list_conversations_for_customer,
    get_conversation_summary,
)

Then add a new endpoint near the other /api/debug/* endpoints:

@app.get("/api/debug/summary", dependencies=[Depends(require_api_key)])
async def debug_conversation_summary():
    """
    Debug endpoint: return aggregate counts of conversations by status, stage, and campaign.
    """
    summary = await get_conversation_summary()
    return summary

3) Save app/db.py and app/main.py, restart the server, and ensure there are no syntax errors.

4) (Optional sanity test for you, the human, not the agent):

curl -s -X GET "http://localhost:8000/api/debug/summary" \
  -H "x-api-key: lia_renewal_2025_super_secret_1" | python3 -m json.tool