Audit API-Key Lifecycle Events

SELECT
  key_id,
  owner_type,
  scope_class,
  MIN(CASE WHEN event_name = 'key_created' THEN timestamp_utc END) AS created_at,
  MAX(CASE WHEN event_name = 'key_used' THEN timestamp_utc END) AS last_used_at,
  MAX(CASE WHEN event_name = 'key_rotated' THEN timestamp_utc END) AS rotated_at,
  MAX(CASE WHEN event_name = 'key_revoked' THEN timestamp_utc END) AS revoked_at,
  SUM(CASE WHEN outcome <> 'success' THEN 1 ELSE 0 END) AS failed_events
FROM api_key_audit_events
WHERE timestamp_utc >= now() - INTERVAL '90 days'
GROUP BY key_id, owner_type, scope_class
ORDER BY created_at;