Observability

// src/db/ai.schema.ts (excerpt)
ai_call {
  id              text PRIMARY KEY
  userId          text NULL                            // FK to user
  provider        text NOT NULL                        // 'openai' | 'mock' | ...
  model           text NOT NULL                        // 'gpt-4o-mini'
  operation       text NOT NULL                        // 'chat' | 'chat_stream' | 'image'
  status          text NOT NULL DEFAULT 'pending'      // 'success'|'error'|'cancelled'|...
  streamed        boolean NOT NULL DEFAULT false
  cached          boolean NOT NULL DEFAULT false
  inputTokens     integer NULL                         // present for text APIs only
  outputTokens    integer NULL                         // present for text APIs only
  ttftMs          integer NULL                         // streaming only
  totalMs         integer NULL
  errorMessage    text NULL
  metadata        jsonb DEFAULT '{}'
  createdAt       timestamp NOT NULL DEFAULT now()
}

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*)                       AS calls,
  COUNT(*) FILTER (WHERE status = 'success') AS successes
FROM ai_call
WHERE created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1 DESC;

SELECT
  provider || '/' || model AS model,
  COUNT(*) AS calls,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'error') / COUNT(*), 2)
    AS error_pct
FROM ai_call
WHERE created_at >= now() - interval '7 days'
GROUP BY model
HAVING COUNT(*) >= 10
ORDER BY error_pct DESC;

SELECT
  provider || '/' || model AS model,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY total_ms) AS p50_ms,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY total_ms) AS p95_ms
FROM ai_call
WHERE created_at >= now() - interval '7 days'
  AND total_ms IS NOT NULL
GROUP BY model
ORDER BY p95_ms DESC;