Investigate a cross-org audit trail

Symptom

You need to answer a question like “Did platform admin X act inside org Y on date Z?”, “Which orgs did impersonation ID 7b2c… touch?”, or “What account locked itself out three times in the last hour across the fleet?”. The normal per-org audit log cannot answer these; you need the cross-org view.

Likely causes

1. Compliance review. A customer or Novalien Legal asks for the full trail of platform-staff access inside a specific org over a date range.
2. Incident investigation. Something was deleted or changed and no customer user owns the action — you are checking whether a platform admin did it.
3. User support escalation. A user reports “I was logged out suddenly across every device” and you are correlating that with an Owner action, a platform disable, or a session-eviction event.

Fix

Pick the right log first

Audit entries live in two places (prd-platform-admin.md §3):

Question	Log to query	Retention
What did platform admins do?	platform_audit_log	365 days
What happened inside one org?	audit_log filtered by org_id	90 days
What did a platform admin do inside a specific org?	Both — they dual-write by design	90 / 365 days

Columns that matter

Per docs/arch-data-models.md and cloud/internal/platform/models.go:

Column	When it helps
actor_type	'user' vs 'platform_admin_impersonating' vs 'service_account' — tells you which identity class acted.
platform_user_id / user_id	The actor. Only one is non-null per row.
target_org_id	Which customer was touched (platform side).
impersonation_id	Correlate platform-side row with org-side row for the same session.
ip_address / user_agent	De-duplicate simultaneous sessions; detect unexpected geographies.
details_json	The before/after diff of the changed entity.

Search pattern — by user

-- Find everything a specific org user did in the last 30 days
SELECT created_at, action, entity_type, entity_id, ip_address
FROM audit_log
WHERE org_id = :org_id AND user_id = :user_id
  AND created_at > now() - INTERVAL '30 days'
ORDER BY created_at DESC;

Search pattern — by impersonation session

-- All actions correlated with one impersonation token
SELECT 'platform' AS src, created_at, action, target_org_id, details_json
  FROM platform_audit_log WHERE impersonation_id = :id
UNION ALL
SELECT 'org' AS src, created_at, action, org_id AS target_org_id, details_json
  FROM audit_log WHERE impersonation_id = :id
ORDER BY created_at;

Search pattern — cross-org by timestamp + IP

-- Did this IP appear in multiple orgs around this time?
SELECT org_id, user_id, action, created_at
FROM audit_log
WHERE ip_address = :ip
  AND created_at BETWEEN :start AND :end
ORDER BY created_at;

Verify

1. The impersonation_id (if relevant) correlates exactly one platform-side row per action to one org-side row — any mismatch is a bug, flag it.
2. Timestamps are UTC, not server-local.
3. Retention hits its bound: no row older than 365 days in platform_audit_log, none older than 90 days in audit_log.

If none of this worked

• If rows are missing that you expect to exist, the dual-write is broken for that action type. Open an engineering ticket with the action name and the impersonation_id.
• If you need to preserve evidence beyond retention, snapshot the rows into an incident ticket immediately — retention jobs delete silently.
• Related: Per-customer data export when the audit trail itself is part of the deliverable.