Queries · q108 · Chief Information Security Officer · week 1, night 3
“Is the audit trail (audit_log) consistently capturing sensitive actions across all merchants and over time, or are some action types thin, recent-only, or concentrated in a handful of tenants — could we credibly reconstruct 'who did what, when' if asked?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 1
Subject: Where I'd like your help first — account security snapshot
Hi Noah,
Good to have you working with me this week. I'm still building my mental map of the business, so let's start broad and sharpen later. Here's what's on my mind, roughly in priority order:
What I need this for: I'm walking into enterprise deals where security questionnaires will ask us point-blank about authentication strength, access governance, credential hygiene, and auditability. I need an honest, evidence-based picture — good or bad — so I can brief the CEO and sales on what we can credibly claim today versus what needs fixing before the next review cycle.
Given the slot budget, let's pick whichever 3 of these will give us the most defensible starting picture this week, and we can go deeper on the rest next week.
Thanks, Henrik
The analyst · week 1, night 3, request 3 of 3
Night 2 findings: MFA is flat and low — staff MFA has hovered at 54-57% all year (not improving, not declining — a plateau, which itself is a finding: nobody is pushing adoption), and shopper MFA is a literal 0.0% in every single month across ~11,200 shopper accounts, confirming it isn't sampling noise from report 1 — shoppers simply have no MFA option in use at all. The Nov 24 week spike is now explained: it's broad-based across ~110 tenants with the largest tenant (Cragsley Outfitters) naturally taking the biggest share, distinct-IPs tracking distinct-emails roughly 1:1, and failure rates in line with baseline — looks like ordinary Black Friday/Cyber Monday traffic, not a credential-stuffing event, so I'm closing that thread. The role-landscape report revealed something important report 3 (night 1) didn't show: every tenant also has an 'Administrator' system role carrying almost as many permissions as Owner (25 vs 26), with 122 holders platform-wide and a 6.6% dormancy rate — higher than what we saw for Owner — so Administrator, not just Owner, is the second pool of truly powerful access worth naming individuals in.
With two nights left before Friday's memo, I'm using tonight to (1) get the concrete, named list of dormant Owner/Administrator accounts for the access-governance finding, and open the two untouched topics Henrik raised — (2) API key/service account hygiene and (3) audit trail completeness — so night 4 can go deeper on whichever of those looks worst rather than starting cold.
The engineer · 1 dry run on the empty schema, then the submission
SELECT a.action AS action_type, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE a.occurred_at >= TIMESTAMP '2025-10-05' AND a.occurred_at < TIMESTAMP '2026-01-01') AS events_last_90_days, COUNT(DISTINCT a.tenant_id) AS distinct_tenants, ROUND(100.0 * COUNT(DISTINCT a.tenant_id) / t.tenant_count, 1) AS pct_of_tenants, COUNT(DISTINCT a.actor_user_id) AS distinct_actor_users, MIN(a.occurred_at) AS earliest_occurred_at, MAX(a.occurred_at) AS most_recent_occurred_at FROM audit_log a CROSS JOIN (SELECT COUNT(*) AS tenant_count FROM tenants) t WHERE a.occurred_at >= TIMESTAMP '2023-01-01' AND a.occurred_at < TIMESTAMP '2026-01-01' GROUP BY a.action, t.tenant_count ORDER BY total_events DESC, action_type ASC
“Query compiles and returns the expected columns. Submitting.”
“Submitted. The report groups audit_log by action type over the full 2023-01-01 to 2025-12-31 history, with a trailing-90-day window (2025-10-05 to 2025-12-31) for recency, distinct tenant and actor counts, and earliest/latest timestamps. One deviation worth noting: I computed the tenant-share denominator dynamically from COUNT(*) FROM tenants instead of hardcoding 120, per the request's own caveat to check that assumption — flagged this in the submit note for Noah.”
As Noah Fischer saw it, from the small dataset the agents worked on
action_type | total_events | events_last_90_days | distinct_tenants | pct_of_tenants | distinct_actor_users | earliest_occurred_at | most_recent_occurred_at --------------+--------------+---------------------+------------------+----------------+----------------------+----------------------------+--------------------------- login | 55388 | 7573 | 120 | 100.0 | 547 | 2023-12-02 06:12:17.421046 | 2025-12-31 18:11:47.560027 update | 26334 | 1818 | 113 | 94.2 | 364 | 2023-12-02 03:00:48.488426 | 2025-12-31 21:58:38.049239 logout | 16788 | 2314 | 120 | 100.0 | 546 | 2023-12-02 12:05:27.458972 | 2025-12-31 22:19:26.215605 create | 13020 | 1420 | 120 | 100.0 | 147 | 2023-12-02 00:00:00 | 2025-12-31 16:50:59.022726 export | 2289 | 220 | 120 | 100.0 | 124 | 2023-12-02 08:05:05 | 2025-12-02 18:49:32 access_denied | 116 | 15 | 33 | 27.5 | 36 | 2023-12-08 09:17:38.51453 | 2025-12-30 08:03:58.36715 delete | 27 | 1 | 16 | 13.3 | 16 | 2024-01-05 12:34:16.549504 | 2025-11-10 15:18:24.435098 (7 rows)
The memo and the queries that followed; links found by title, so inferred
Noah Fischer's memo to Henrik Aalto at the end of the week:
5. Audit trail: real, specific gap on deletions. Login/logout/create/export are logged for 100% of tenants, update for 94.2% (Audit log coverage by action type). But 'delete' events are thin — only 27 ever, 16/120 tenants. I tested this directly against real soft-deletions (Audit-trail coverage of soft-deletions): 615 products were soft-deleted across 91 tenants, and 0 of those show up as a 'delete' audit_log row. Users fared better (27 of 39 logged, 69.2%). We could not credibly reconstruct "who deleted this product and when" today. Compliance monitoring is also thin: only 9 compliance_events total in 3 years (all resolved) — too sparse a sample to call it a proven control (Compliance event resolution health).
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
SELECT a.action AS action_type, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE a.occurred_at >= TIMESTAMP '2025-10-05' AND a.occurred_at < TIMESTAMP '2026-01-01') AS events_last_90_days, COUNT(DISTINCT a.tenant_id) AS distinct_tenants, ROUND(100.0 * COUNT(DISTINCT a.tenant_id) / t.tenant_count, 1) AS pct_of_tenants, 100.0 * COUNT(DISTINCT a.tenant_id) / t.tenant_count AS pct_of_tenants, COUNT(DISTINCT a.actor_user_id) AS distinct_actor_users, MIN(a.occurred_at) AS earliest_occurred_at, MAX(a.occurred_at) AS most_recent_occurred_atFROM audit_log aCROSS JOIN (SELECT COUNT(*) AS tenant_count FROM tenants) tWHERE a.occurred_at >= TIMESTAMP '2023-01-01' AND a.occurred_at < TIMESTAMP '2026-01-01'GROUP BY a.action, t.tenant_countORDER BY total_events DESC, action_type ASCORDER BY total_events DESC NULLS FIRST, action_type ASC NULLS LAST
SELECT a.action AS action_type, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE a.occurred_at >= TIMESTAMP '2025-10-05' AND a.occurred_at < TIMESTAMP '2026-01-01') AS events_last_90_days, COUNT(DISTINCT a.tenant_id) AS distinct_tenants, 100.0 * COUNT(DISTINCT a.tenant_id) / t.tenant_count AS pct_of_tenants, COUNT(DISTINCT a.actor_user_id) AS distinct_actor_users, MIN(a.occurred_at) AS earliest_occurred_at, MAX(a.occurred_at) AS most_recent_occurred_at FROM audit_log a CROSS JOIN (SELECT COUNT(*) AS tenant_count FROM tenants) t WHERE a.occurred_at >= TIMESTAMP '2023-01-01' AND a.occurred_at < TIMESTAMP '2026-01-01' GROUP BY a.action, t.tenant_count ORDER BY total_events DESC NULLS FIRST, action_type ASC NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Noah Fischer (analyst for the Chief Information Security Officer): Title: Audit log coverage by action type Question: Is the audit trail (audit_log) consistently capturing sensitive actions across all merchants and over time, or are some action types thin, recent-only, or concentrated in a handful of tenants — could we credibly reconstruct 'who did what, when' if asked? Grain: one row per audit_log action type (the 7 enum values: create, update, delete, login, logout, export, access_denied) Columns: - action type - total events (2023-01-01 through 2025-12-31) - events in the last 90 days (2025-10-05 through 2025-12-31) - distinct tenants with at least one such event - share of all 120 tenants represented (%) - distinct actor users recorded - earliest occurred_at - most recent occurred_at Details: Source: audit_log (tenant_id, action, actor_user_id, occurred_at), full history available (2023-01-01 to 2025-12-31). Group by action. 'Share of all 120 tenants' = distinct tenants with that action / 120, rounded to 1 decimal percent (note if the tenant count assumption of 120 needs adjusting based on what's actually in the tenants table). Order by total events descending, action type as tie-break (only 7 rows expected, well under the cap). Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT a.action AS action_type, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE a.occurred_at >= TIMESTAMP '2025-10-05' AND a.occurred_at < TIMESTAMP '2026-01-01') AS events_last_90_days, COUNT(DISTINCT a.tenant_id) AS distinct_tenants, ROUND(100.0 * COUNT(DISTINCT a.tenant_id) / t.tenant_count, 1) AS pct_of_tenants, COUNT(DISTINCT a.actor_user_id) AS distinct_actor_users, MIN(a.occurred_at) AS earliest_occurred_at, MAX(a.occurred_at) AS most_recent_occurred_at FROM audit_log a CROSS JOIN (SELECT COUNT(*) AS tenant_count FROM tenants) t WHERE a.occurred_at >= TIMESTAMP '2023-01-01' AND a.occurred_at < TIMESTAMP '2026-01-01' GROUP BY a.action, t.tenant_count ORDER BY total_events DESC, action_type ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: action_type, total_events, events_last_90_days, distinct_tenants, pct_of_tenants, distinct_actor_users, earliest_occurred_at, most_recent_occurred_at
submitSELECT a.action AS action_type, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE a.occurred_at >= TIMESTAMP '2025-10-05' AND a.occurred_at < TIMESTAMP '2026-01-01') AS events_last_90_days, COUNT(DISTINCT a.tenant_id) AS distinct_tenants, ROUND(100.0 * COUNT(DISTINCT a.tenant_id) / t.tenant_count, 1) AS pct_of_tenants, COUNT(DISTINCT a.actor_user_id) AS distinct_actor_users, MIN(a.occurred_at) AS earliest_occurred_at, MAX(a.occurred_at) AS most_recent_occurred_at FROM audit_log a CROSS JOIN (SELECT COUNT(*) AS tenant_count FROM tenants) t WHERE a.occurred_at >= TIMESTAMP '2023-01-01' AND a.occurred_at < TIMESTAMP '2026-01-01' GROUP BY a.action, t.tenant_count ORDER BY total_events DESC, action_type ASC
Submitted to tonight's batch. Your work on this request is done.