Athena + S3: Query Coding-Agent Eval Traces Without a Warehouse

# ✅ One immutable line per eval case — easy for Athena SerDe
import json, datetime
from pathlib import Path

def emit_eval_trace(row: dict, out_dir: Path):
    dt = datetime.date.today().isoformat()
    path = out_dir / f"dt={dt}" / "traces.jsonl"
    path.parent.mkdir(parents=True, exist_ok=True)
    required = {
        "eval_id", "suite", "prompt_version", "model_id",
        "pass", "latency_ms", "tokens_in", "tokens_out", "cost_usd",
        "tenant_id", "ts",
    }
    missing = required - row.keys()
    if missing:
        raise ValueError(f"missing {missing}")
    with path.open("a") as f:
        f.write(json.dumps(row, separators=(",", ":")) + "\n")

# Sync nightly (or from the Batch eval job) — hive-style partitions
aws s3 sync ./evals/out/ s3://acme-agent-evals/evals/ \
  --exclude "*" --include "dt=*/*"

-- Explicit external table over JSONL
CREATE EXTERNAL TABLE agent_evals (
  eval_id string,
  suite string,
  prompt_version string,
  model_id string,
  pass boolean,
  latency_ms int,
  tokens_in int,
  tokens_out int,
  cost_usd double,
  tenant_id string,
  ts string
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://acme-agent-evals/evals/'
TBLPROPERTIES ('has_encrypted_data'='false');

MSCK REPAIR TABLE agent_evals;
-- or ALTER TABLE agent_evals ADD PARTITION (dt='2026-09-21')
--   LOCATION 's3://acme-agent-evals/evals/dt=2026-09-21/';

-- Compact hot partitions to Parquet for repeated dashboards
CREATE TABLE agent_evals_parquet
WITH (
  format = 'PARQUET',
  parquet_compression = 'SNAPPY',
  external_location = 's3://acme-agent-evals/evals_parquet/',
  partitioned_by = ARRAY['dt']
) AS
SELECT * FROM agent_evals WHERE dt >= '2026-09-01';

-- Pass rate and cost by prompt version (last 7 days)
SELECT prompt_version,
       count(*) AS n,
       avg(CASE WHEN pass THEN 1.0 ELSE 0.0 END) AS pass_rate,
       approx_percentile(latency_ms, 0.95) AS p95_ms,
       sum(cost_usd) AS spend_usd
FROM agent_evals
WHERE dt BETWEEN '2026-09-14' AND '2026-09-21'
GROUP BY 1
ORDER BY pass_rate ASC, spend_usd DESC;

-- Regressions: versions that got slower at same suite
WITH base AS (
  SELECT suite, prompt_version,
         approx_percentile(latency_ms, 0.95) AS p95
  FROM agent_evals
  WHERE dt = '2026-09-21'
  GROUP BY 1, 2
)
SELECT * FROM base ORDER BY p95 DESC LIMIT 20;

-- Tenant cost outliers (eval farm abuse / misconfig)
SELECT tenant_id, sum(cost_usd) AS spend, count(*) AS cases
FROM agent_evals
WHERE dt = '2026-09-21'
GROUP BY 1
HAVING sum(cost_usd) > 50
ORDER BY spend DESC;

// Minimal TypeScript emitter (same contract)
type EvalTrace = {
  eval_id: string;
  suite: string;
  prompt_version: string;
  model_id: string;
  pass: boolean;
  latency_ms: number;
  tokens_in: number;
  tokens_out: number;
  cost_usd: number;
  tenant_id: string;
  ts: string;
  guardrail_intervened?: boolean;
};

export function line(t: EvalTrace): string {
  return JSON.stringify(t) + "\n";
}