Amazon Athena: Query Coding-Agent Tool Audit Logs Without a Warehouse

{
  "ts": "2026-10-04T20:15:03.441Z",
  "account_id": "111122223333",
  "tenant_id": "acme",
  "session_id": "sess_9f3a",
  "agent_role": "patcher",
  "prompt_version": "3",
  "tool": "apply_patch",
  "ok": true,
  "latency_ms": 842,
  "input_bytes": 12044,
  "output_bytes": 220,
  "bedrock_input_tokens": 4100,
  "bedrock_output_tokens": 900,
  "model_id": "anthropic.claude-sonnet-4-20250514-v1:0",
  "repo": "acme/payments",
  "pr_number": 1882,
  "error_code": null,
  "actor_arn": "arn:aws:sts::111122223333:assumed-role/batch-agent-job/job-1"
}

s3://coding-agent-audit-prod/
  tool_invocations/
    dt=2026-10-04/
      tenant=acme/
        part-000.gz

# ✅ agent host: append NDJSON to dated prefix (or Kinesis Firehose)
import json, gzip, io, boto3
from datetime import datetime, timezone

s3 = boto3.client("s3")

def emit_tool_audit(record: dict):
    dt = datetime.now(timezone.utc).strftime("%Y-%m-%d")
    tenant = record["tenant_id"]
    key = f"tool_invocations/dt={dt}/tenant={tenant}/{record['session_id']}-{record['ts']}.json.gz"
    buf = io.BytesIO()
    with gzip.GzipFile(fileobj=buf, mode="wb") as gz:
        gz.write((json.dumps(record) + "\n").encode())
    s3.put_object(
        Bucket="coding-agent-audit-prod",
        Key=key,
        Body=buf.getvalue(),
        ServerSideEncryption="aws:kms",
        ContentType="application/json",
    )

-- ✅ external table over JSON (simplify types as needed)
CREATE EXTERNAL TABLE IF NOT EXISTS coding_agent.tool_invocations (
  ts string,
  account_id string,
  tenant_id string,
  session_id string,
  agent_role string,
  prompt_version string,
  tool string,
  ok boolean,
  latency_ms int,
  input_bytes bigint,
  output_bytes bigint,
  bedrock_input_tokens int,
  bedrock_output_tokens int,
  model_id string,
  repo string,
  pr_number int,
  error_code string,
  actor_arn string
)
PARTITIONED BY (dt string, tenant string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://coding-agent-audit-prod/tool_invocations/'
TBLPROPERTIES ('has_encrypted_data'='true');

MSCK REPAIR TABLE coding_agent.tool_invocations;
-- or ALTER TABLE ... ADD PARTITION for tighter control

# ✅ Athena workgroup with enforce output location + bytes scanned cutoff
aws athena create-work-group \
  --name coding-agent-forensics \
  --configuration ResultConfigurationUpdates={OutputLocation=s3://athena-results-prod/coding-agent/},EnforceWorkGroupConfiguration=true,BytesScannedCutoffPerQuery=10737418240

-- ✅ Bedrock token burn by tenant + prompt_version (yesterday IST window ≈ UTC day)
SELECT tenant_id,
       prompt_version,
       COUNT(*) AS tool_calls,
       SUM(bedrock_input_tokens + bedrock_output_tokens) AS tokens,
       SUM(CASE WHEN ok THEN 0 ELSE 1 END) AS failures
FROM coding_agent.tool_invocations
WHERE dt = '2026-10-03'
GROUP BY 1, 2
ORDER BY tokens DESC
LIMIT 50;

-- ✅ suspicious: apply_patch spikes for one actor
SELECT actor_arn, COUNT(*) AS patches, COUNT(DISTINCT repo) AS repos
FROM coding_agent.tool_invocations
WHERE dt = '2026-10-04'
  AND tool = 'apply_patch'
GROUP BY 1
HAVING COUNT(*) > 200
ORDER BY patches DESC;

-- ✅ join-quality: tool error rate by agent_role
SELECT agent_role, tool,
       AVG(latency_ms) AS p_avg_ms,
       AVG(CASE WHEN ok THEN 0.0 ELSE 1.0 END) AS err_rate
FROM coding_agent.tool_invocations
WHERE dt BETWEEN '2026-10-01' AND '2026-10-04'
GROUP BY 1, 2
ORDER BY err_rate DESC;