Queries · q073 · Head of Risk and Payments · week 1, night 3
“What share of staff and customer accounts are protected by a confirmed MFA device, and does that differ by account status?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Rosa Van den Berg, Head of Data
An enterprise prospect sent a security and compliance questionnaire covering account security, access control, privacy requests, data retention and audit trails. Sales wants answers backed by evidence from our own systems, not by policy documents.
The manager, at the start of week 1
Subject: Evidence we need for the security/compliance questionnaire (this week's priority)
Hi Claire,
This questionnaire is going to a serious enterprise prospect, and Sales wants it backed by evidence from our own systems, not by our policy wording. Rosa and I are co-owning this, so your reports will feed both the questionnaire answers themselves and a broader readiness review Rosa's side is doing. Given you only get a handful of report slots a night, let's be selective and prioritize what a security reviewer will actually probe on. In my own words, here's what I need to know:
Given the slot constraints, please treat questions 3 and 1 as the top two for this week — those are the ones Sales says the prospect calls out by name. If we only get through three of the five, drop 5 first and fold it into next week.
One more thing: since this report touches account security and personal data, please keep the output at a safe aggregate level — no need to name individual customers or staff unless I specifically ask.
Let's sync briefly before you lock in Thursday night's batch so nothing gets wasted on a bounced or bundled request.
Thanks, Aylin
Hi Claire,
Before we hand evidence to this prospect, I'd want us to be confident the underlying records are actually solid enough to stand behind in writing. A few things from my side:
If any of these raise flags on your end, I'd rather we know before it goes into a customer-facing answer than after.
Thanks, Rosa
The analyst · week 1, night 3, request 3 of 3
Night 2 take-aways:
Report 1 (DSAR reconciliation) resolved the mystery well: the "overdue" cases from report 1 night-1 are exactly 3 rejected erasure requests (oldest from 2023-05-26) that were decided but never got a completed_at timestamp written — a record-keeping gap on rejections, not evidence of 2.5-year-old unresolved requests. The 2 remaining open ones (in_progress/received) are from Dec 2025 and not yet due. Good news, but I can't yet tell Aylin when those 3 rejections were actually decided, which is exactly the kind of timestamp gap Rosa warned about — I want to check whether the audit/change-history trail can supply a real decision timestamp before we write anything definitive.
Report 2 (elevated access by category): reassuring nuance — the 2 deactivated staff retain only the broad baseline categories (catalog/orders/customers/support, ~87-99% of all staff), not the more sensitive ones (billing, compliance, settings, crm — all 0 non-active holders). Combined with last night's avg_roles_per_user ≈ 1.02, this points to a roughly two-tier model: a broad base role nearly everyone has, and a second, more powerful role held by ~45-52% of staff. That's useful signal on "how tightly is access controlled" for the memo.
Report 3 (role grant/revocation activity) came back as all zeros for all 36 months — either a genuine, serious audit-trail gap on user_roles changes (bad news for both Aylin's "is access reviewed" question and Rosa's audit-completeness concern), or my filter (entity_table = 'user_roles') simply doesn't match how these events are actually logged. I need to find out which before concluding anything, since this is too important a finding to assert on a guess.
Tonight (3 slots): (1) a diagnostic sweep of audit_log/audit_log_archive entity_table × action for the identity/access-control tables, to find out how (or whether) role and permission changes are actually logged — this resolves last night's zero-result mystery and is foundational before I can answer Aylin's "is access reviewed" question honestly; (2) cross-reference the 3 rejected DSARs against change_history/audit_log to recover a real decision timestamp, closing Rosa's timestamp-trust gap on the one finding most likely to be pressed on; (3) open priority #2 (login security) with real MFA adoption evidence by user segment, since #2 hasn't been touched yet and only one night remains after tonight before the Thursday sync.
The engineer · 1 dry run on the empty schema, then the submission
WITH user_mfa AS ( SELECT user_id, tenant_id, max(case when confirmed_at is not null then 1 else 0 end) as has_confirmed, 1 as has_any FROM mfa_devices GROUP BY user_id, tenant_id ) SELECT u.is_staff, u.status, count(*) as total_users, sum(case when um.has_confirmed = 1 then 1 else 0 end) as users_with_confirmed_mfa, round(100.0 * sum(case when um.has_confirmed = 1 then 1 else 0 end) / count(*), 1) as pct_with_confirmed_mfa, sum(case when um.has_any = 1 then 1 else 0 end) as users_with_any_mfa_device, round(100.0 * sum(case when um.has_any = 1 then 1 else 0 end) / count(*), 1) as pct_with_any_mfa_device FROM users u LEFT JOIN user_mfa um ON um.user_id = u.id AND um.tenant_id = u.tenant_id WHERE u.deleted_at is null GROUP BY u.is_staff, u.status ORDER BY u.is_staff desc, u.status
“Query compiles and matches the requested grain and columns. Submitting now.”
“Submitted. The report groups non-deleted users by is_staff and status, computing total users, confirmed-MFA coverage, and any-MFA-device coverage (with percentages), ordered by is_staff descending then status — ready for tonight's batch.”
As Claire Dubois saw it, from the small dataset the agents worked on
is_staff | status | total_users | users_with_confirmed_mfa | pct_with_confirmed_mfa | users_with_any_mfa_device | pct_with_any_mfa_device ---------+-------------+-------------+--------------------------+------------------------+---------------------------+------------------------ true | active | 548 | 301 | 54.9 | 317 | 57.8 true | deactivated | 2 | 1 | 50.0 | 1 | 50.0 false | active | 11183 | 0 | 0.0 | 0 | 0.0 false | suspended | 36 | 0 | 0.0 | 0 | 0.0 (4 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
WITH user_mfa AS ( SELECT user_id, tenant_id, max(case when confirmed_at is not null then 1 else 0 end) as has_confirmed, 1 as has_any FROM mfa_devices GROUP BY user_id, tenant_id)SELECT u.is_staff, u.status, u.is_staff AS is_staff, u.status AS status, count(*) as total_users, sum(case when um.has_confirmed = 1 then 1 else 0 end) as users_with_confirmed_mfa, round(100.0 * sum(case when um.has_confirmed = 1 then 1 else 0 end) / count(*), 1) as pct_with_confirmed_mfa, 100.0 * sum(case when um.has_confirmed = 1 then 1 else 0 end) / count(*) as pct_with_confirmed_mfa, sum(case when um.has_any = 1 then 1 else 0 end) as users_with_any_mfa_device, round(100.0 * sum(case when um.has_any = 1 then 1 else 0 end) / count(*), 1) as pct_with_any_mfa_device 100.0 * sum(case when um.has_any = 1 then 1 else 0 end) / count(*) as pct_with_any_mfa_deviceFROM users uLEFT JOIN user_mfa um ON um.user_id = u.id AND um.tenant_id = u.tenant_idWHERE u.deleted_at is nullGROUP BY u.is_staff, u.statusORDER BY u.is_staff desc, u.statusORDER BY u.is_staff desc NULLS FIRST, u.status NULLS LAST
WITH user_mfa AS ( SELECT user_id, tenant_id, max(case when confirmed_at is not null then 1 else 0 end) as has_confirmed, 1 as has_any FROM mfa_devices GROUP BY user_id, tenant_id ) SELECT u.is_staff AS is_staff, u.status AS status, count(*) as total_users, sum(case when um.has_confirmed = 1 then 1 else 0 end) as users_with_confirmed_mfa, 100.0 * sum(case when um.has_confirmed = 1 then 1 else 0 end) / count(*) as pct_with_confirmed_mfa, sum(case when um.has_any = 1 then 1 else 0 end) as users_with_any_mfa_device, 100.0 * sum(case when um.has_any = 1 then 1 else 0 end) / count(*) as pct_with_any_mfa_device FROM users u LEFT JOIN user_mfa um ON um.user_id = u.id AND um.tenant_id = u.tenant_id WHERE u.deleted_at is null GROUP BY u.is_staff, u.status ORDER BY u.is_staff desc NULLS FIRST, u.status 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 Claire Dubois (analyst for the Head of Risk and Payments): Title: MFA device adoption by user segment and status Question: What share of staff and customer accounts are protected by a confirmed MFA device, and does that differ by account status? Grain: one row per combination of users.is_staff (true/false) and users.status, across all tenants, current-state snapshot as of 2025-12-31 Columns: - is_staff - status - total_users (non-deleted) - users_with_confirmed_mfa (distinct user_id in mfa_devices where confirmed_at is not null) - pct_with_confirmed_mfa - users_with_any_mfa_device (confirmed or not) - pct_with_any_mfa_device Details: Sources: users (exclude deleted_at not null), mfa_devices. This is the first evidence report on login/authentication security (priority #2): I want a real, current number for 'what fraction of staff vs. customer accounts have MFA' broken out by account status, to see whether MFA coverage is meaningfully different for active staff (who hold tenant access) versus end customers, and whether any non-active accounts still show confirmed MFA (expected to be irrelevant but worth seeing). Order by is_staff descending, then status. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH user_mfa AS ( SELECT user_id, tenant_id, max(case when confirmed_at is not null then 1 else 0 end) as has_confirmed, 1 as has_any FROM mfa_devices GROUP BY user_id, tenant_id ) SELECT u.is_staff, u.status, count(*) as total_users, sum(case when um.has_confirmed = 1 then 1 else 0 end) as users_with_confirmed_mfa, round(100.0 * sum(case when um.has_confirmed = 1 then 1 else 0 end) / count(*), 1) as pct_with_confirmed_mfa, sum(case when um.has_any = 1 then 1 else 0 end) as users_with_any_mfa_device, round(100.0 * sum(case when um.has_any = 1 then 1 else 0 end) / count(*), 1) as pct_with_any_mfa_device FROM users u LEFT JOIN user_mfa um ON um.user_id = u.id AND um.tenant_id = u.tenant_id WHERE u.deleted_at is null GROUP BY u.is_staff, u.status ORDER BY u.is_staff desc, u.status
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: is_staff, status, total_users, users_with_confirmed_mfa, pct_with_confirmed_mfa, users_with_any_mfa_device, pct_with_any_mfa_device
submitWITH user_mfa AS ( SELECT user_id, tenant_id, max(case when confirmed_at is not null then 1 else 0 end) as has_confirmed, 1 as has_any FROM mfa_devices GROUP BY user_id, tenant_id ) SELECT u.is_staff, u.status, count(*) as total_users, sum(case when um.has_confirmed = 1 then 1 else 0 end) as users_with_confirmed_mfa, round(100.0 * sum(case when um.has_confirmed = 1 then 1 else 0 end) / count(*), 1) as pct_with_confirmed_mfa, sum(case when um.has_any = 1 then 1 else 0 end) as users_with_any_mfa_device, round(100.0 * sum(case when um.has_any = 1 then 1 else 0 end) / count(*), 1) as pct_with_any_mfa_device FROM users u LEFT JOIN user_mfa um ON um.user_id = u.id AND um.tenant_id = u.tenant_id WHERE u.deleted_at is null GROUP BY u.is_staff, u.status ORDER BY u.is_staff desc, u.status
Submitted to tonight's batch. Your work on this request is done.