d1_client.py

"""
Thought Engine Node — Cloudflare D1 Client
===========================================
Contract: C-THOUGHT-NODE-001 (Phase 1 Sovereign Substrate)

Raw HTTPS wrapper for executing SQLite queries against the thought-vault D1.
Reuses the proven pattern from the Federation Embassy.
"""
import os
import httpx
from typing import Any, Optional

ACCOUNT_ID = os.getenv("CLOUDFLARE_ACCOUNT_ID")
DATABASE_ID = os.getenv("CLOUDFLARE_D1_DATABASE_ID")
API_TOKEN  = os.getenv("CLOUDFLARE_D1_API_TOKEN")


async def execute_sql(sql: str, params: Optional[list[Any]] = None) -> list[dict]:
    """Execute a single SQL query against the thought-vault D1."""
    if not (ACCOUNT_ID and DATABASE_ID and API_TOKEN):
        print("⚠️  D1 credentials missing — SQL execution skipped.")
        return []

    url = (
        f"https://api.cloudflare.com/client/v4/accounts/{ACCOUNT_ID}"
        f"/d1/database/{DATABASE_ID}/query"
    )
    headers = {
        "Authorization": f"Bearer {API_TOKEN}",
        "Content-Type": "application/json",
    }
    payload: dict[str, Any] = {"sql": sql}
    if params:
        payload["params"] = params

    async with httpx.AsyncClient(timeout=10.0) as client:
        try:
            r = await client.post(url, headers=headers, json=payload)
            r.raise_for_status()
            data = r.json()
            if not data.get("success"):
                print(f"🔴 D1 Query Error: {data.get('errors')}")
                return []
            return data["result"][0].get("results", [])
        except Exception as e:
            print(f"🔴 D1 Connection Error [{type(e).__name__}]: {e}")
            return []