src/db.py

"""Durable decision store on Postgres (Neon), used only when DATABASE_URL is set.

Kept deliberately tiny and schema-light: a moderator decision is a flat dict, stored verbatim as a
JSONB row, so its shape can evolve without migrations. This is the durable seam behind
`src/audit.py` — the deployed Hugging Face Space has an ephemeral filesystem, so the audit log lives
here instead of in a local file.

A fresh connection is opened per operation (and closed via the context manager). Traffic is low and
Neon's pooled endpoint handles this cheaply; it avoids the stale-idle-connection failures a cached
long-lived connection would hit on a demo that sits quiet for hours.
"""
from __future__ import annotations

from .config import CONFIG

_schema_ready = False


def _connect():
    import psycopg  # lazy: only imported when a database is actually configured
    return psycopg.connect(CONFIG.database_url, autocommit=True)


def _ensure_schema(conn) -> None:
    global _schema_ready
    if _schema_ready:
        return
    with conn.cursor() as cur:
        cur.execute(
            "CREATE TABLE IF NOT EXISTS audit_log ("
            "  id BIGSERIAL PRIMARY KEY,"
            "  ts TIMESTAMPTZ NOT NULL DEFAULT now(),"
            "  record JSONB NOT NULL"
            ")"
        )
    _schema_ready = True


def insert_decision(record: dict) -> None:
    """Append one decision record (stored as a JSONB row)."""
    from psycopg.types.json import Json
    with _connect() as conn:
        _ensure_schema(conn)
        with conn.cursor() as cur:
            cur.execute("INSERT INTO audit_log (record) VALUES (%s)", (Json(record),))


def fetch_decisions() -> list[dict]:
    """All decision records in write order (oldest first). psycopg adapts JSONB to dict."""
    with _connect() as conn:
        _ensure_schema(conn)
        with conn.cursor() as cur:
            cur.execute("SELECT record FROM audit_log ORDER BY id")
            return [row[0] for row in cur.fetchall()]