Know the Unit Cost of Every Artifact

-- Unit cost per artifact, broken down by model
SELECT
  a.kind,
  d.model_id,
  AVG(i.cost_usd) AS avg_cost,
  AVG(a.confidence) AS avg_confidence,
  COUNT(*) AS artifact_count
FROM hd_runtime.artifact_instances a
JOIN hd_runtime.task_attempts att ON a.attempt_id = att.id
JOIN hd_runtime.model_decisions d ON att.id = d.attempt_id
JOIN hd_runtime.llm_invocations i ON d.id = i.decision_id
WHERE a.created_at > NOW() - INTERVAL '30 days'
GROUP BY a.kind, d.model_id
ORDER BY avg_cost DESC;

-- Models with high confidence but high override rate
SELECT
  d.model_id,
  AVG(a.confidence) AS avg_confidence,
  COUNT(*) FILTER (WHERE r.decision = 'rejected') AS overrides,
  COUNT(*) AS total_reviews,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE r.decision = 'rejected') / COUNT(*),
    1
  ) AS override_pct
FROM hd_runtime.model_decisions d
JOIN hd_runtime.task_attempts att ON d.attempt_id = att.id
JOIN hd_runtime.artifact_instances a ON att.id = a.attempt_id
LEFT JOIN hd_runtime.review_requests rev ON a.id = rev.artifact_id
LEFT JOIN hd_runtime.release_decisions r ON rev.id = r.review_id
WHERE d.created_at > NOW() - INTERVAL '30 days'
GROUP BY d.model_id
HAVING COUNT(*) > 10
ORDER BY override_pct DESC;

-- Shadow policy comparison: production vs alternative routing
SELECT
  s.policy_name,
  SUM(s.production_cost) AS actual_cost,
  SUM(s.shadow_cost) AS shadow_cost,
  SUM(s.production_cost - s.shadow_cost) AS savings,
  AVG(s.production_confidence) AS prod_confidence,
  AVG(s.shadow_confidence) AS shadow_confidence
FROM hd_runtime.shadow_policy_outcomes s
WHERE s.created_at > NOW() - INTERVAL '30 days'
GROUP BY s.policy_name
ORDER BY savings DESC;

-- 30-day cost summary by model and day
SELECT
  model_id,
  day,
  invocation_count,
  total_cost,
  avg_cost,
  total_tokens,
  avg_latency_ms
FROM hd_runtime.v_invocation_costs
WHERE day > CURRENT_DATE - 30
ORDER BY day DESC, total_cost DESC;