Queries · q075 · Head of Risk and Payments · week 1, night 4
“Do we have retention policies actually defined, active, and consistently enforced for the tables that matter most to this questionnaire (audit log, access log, change history, DSARs, compliance events, login attempts), and could any of them be purging data sooner than the history we'd want to cite?”
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 4, request 3 of 3
Night 3 take-aways — one genuinely important finding, one bug, one solid result:
Report 1 (audit coverage sweep): decisive and concerning. Across all 13 identity/access-control tables I checked (roles, user_roles, role_permissions, permissions, teams, team_members, api_keys, invitations, mfa_devices, sessions, service_accounts, personal_access_tokens — plus singular/plural variants), only the users table shows ANY audit_log/audit_log_archive activity at all (delete, export, login, logout, update — no create, oddly). Role and permission grant/revocation events are simply not captured in the audit trail, at any point in the 3-year history. This confirms last night's all-zero result wasn't a query bug — it's a real gap. This has to go into the memo plainly for both Aylin (we cannot evidence who granted/revoked a given access right, only today's snapshot) and Rosa (this is exactly the kind of audit-trail incompleteness she asked me to flag before it goes into a customer-facing answer).
Report 2 (DSAR rejection timing) failed on execution: a cast error pulling in an unrelated product_variant row's after_data, which tells me the query matched change_history on entity_id alone without also constraining entity_table — entity_id isn't globally unique across tables, so it collided with an unrelated row. This is a fixable query bug, not a data problem, and worth one retry since it's the last loose end on our top priority (#3) — the only thing standing between us and a clean, fully-evidenced DSAR story.
Report 3 (MFA adoption): first real evidence for priority #2. Confirmed-MFA coverage for active staff is 54.9% (57.8% any device) — material but not universal, worth citing with the caveat that ~45% of merchant admin accounts are not MFA-protected. Customer-facing accounts show 0% MFA entirely (not even attempted) — expected if MFA is currently a staff-only feature, but worth stating plainly rather than silently.
No sync reply from Aylin arrived before lock-in, so I'm proceeding on her original stated priority order: #1 and #3 are solid for the memo (with the audit-gap and rejection-timestamp caveats to close out), so tonight's last 3 slots go to: (1) a defensively-rewritten retry of the DSAR rejection-timing query, (2) the other half of priority #2 — failed-login and suspicious-access patterns over time, since we've only covered MFA adoption so far, and (3) a quick retention-policy inventory for the audit/compliance tables, which gives us a toehold on priority #4 (ahead of #5, which Aylin said to drop first) and directly tests Rosa's "are we quietly losing history early" concern given what we now know about the audit-trail gap.
The engineer · 1 dry run on the empty schema, then the submission
select entity_table, count(distinct tenant_id) as tenants_with_policy, round(100.0 * count(distinct tenant_id) / 120, 1) as pct_tenants_with_policy, round(avg(retention_days), 1) as avg_retention_days, min(retention_days) as min_retention_days, max(retention_days) as max_retention_days, round(100.0 * sum(case when is_active then 1 else 0 end) / count(*), 1) as pct_policies_active, round(100.0 * sum(case when last_run_at >= date '2025-10-03' then 1 else 0 end) / count(*), 1) as pct_policies_run_in_last_90_days from retention_policies where entity_table in ('audit_log', 'access_log', 'change_history', 'data_subject_requests', 'compliance_events', 'login_attempts') group by entity_table order by entity_table
“Query compiles cleanly. Submitting.”
“Submitted. One note for Claire: the report will only show rows for entity_table values that have at least one retention_policies row somewhere — if any of the six tables (e.g. access_log or login_attempts) has zero tenants with a defined policy, it simply won't appear in the output, which itself is a strong signal for Rosa's concern (no stated retention commitment at all, rather than a short one).”
As Claire Dubois saw it, from the small dataset the agents worked on
entity_table | tenants_with_policy | pct_tenants_with_policy | avg_retention_days | min_retention_days | max_retention_days | pct_policies_active | pct_policies_run_in_last_90_days -------------+---------------------+-------------------------+--------------------+--------------------+--------------------+---------------------+--------------------------------- audit_log | 120 | 100.0 | 730.0 | 730 | 730 | 100.0 | 92.5 (1 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
select entity_table, count(distinct tenant_id) as tenants_with_policy, round(100.0 * count(distinct tenant_id) / 120, 1) as pct_tenants_with_policy, round(avg(retention_days), 1) as avg_retention_days, 100.0 * count(distinct tenant_id) / 120 as pct_tenants_with_policy, avg(cast(retention_days as numeric(18,6))) as avg_retention_days, min(retention_days) as min_retention_days, max(retention_days) as max_retention_days, round(100.0 * sum(case when is_active then 1 else 0 end) / count(*), 1) as pct_policies_active, round(100.0 * sum(case when last_run_at >= date '2025-10-03' then 1 else 0 end) / count(*), 1) as pct_policies_run_in_last_90_days 100.0 * sum(case when is_active then 1 else 0 end) / count(*) as pct_policies_active, 100.0 * sum(case when last_run_at >= date '2025-10-03' then 1 else 0 end) / count(*) as pct_policies_run_in_last_90_daysfrom retention_policieswhere entity_table in ('audit_log', 'access_log', 'change_history', 'data_subject_requests', 'compliance_events', 'login_attempts')group by entity_tableorder by entity_tableorder by entity_table NULLS LAST
select entity_table, count(distinct tenant_id) as tenants_with_policy, 100.0 * count(distinct tenant_id) / 120 as pct_tenants_with_policy, avg(cast(retention_days as numeric(18,6))) as avg_retention_days, min(retention_days) as min_retention_days, max(retention_days) as max_retention_days, 100.0 * sum(case when is_active then 1 else 0 end) / count(*) as pct_policies_active, 100.0 * sum(case when last_run_at >= date '2025-10-03' then 1 else 0 end) / count(*) as pct_policies_run_in_last_90_days from retention_policies where entity_table in ('audit_log', 'access_log', 'change_history', 'data_subject_requests', 'compliance_events', 'login_attempts') group by entity_table order by entity_table 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: Retention policy coverage for audit and compliance-relevant tables Question: Do we have retention policies actually defined, active, and consistently enforced for the tables that matter most to this questionnaire (audit log, access log, change history, DSARs, compliance events, login attempts), and could any of them be purging data sooner than the history we'd want to cite? Grain: one row per entity_table value, aggregated across all tenants' retention_policies rows, for entity_table in (audit_log, access_log, change_history, data_subject_requests, compliance_events, login_attempts) Columns: - entity_table - tenants_with_policy (count of distinct tenant_id with a retention_policies row for this table) - pct_tenants_with_policy (out of 120 tenants) - avg_retention_days - min_retention_days - max_retention_days - pct_policies_active (is_active = true) - pct_policies_run_in_last_90_days (last_run_at >= 2025-10-03) Details: Source: retention_policies, grouped by entity_table, counting distinct tenant_id and aggregating retention_days/is_active/last_run_at. Purpose: sanity-check Rosa's concern that we might be 'quietly losing history before our stated retention period is up' — e.g. if audit_log's min_retention_days turns out to be shorter than the ~3 years of history we're already citing in this week's other reports, that's a contradiction worth flagging before we commit to any retention claim in the questionnaire. Order by entity_table. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect entity_table, count(distinct tenant_id) as tenants_with_policy, round(100.0 * count(distinct tenant_id) / 120, 1) as pct_tenants_with_policy, round(avg(retention_days), 1) as avg_retention_days, min(retention_days) as min_retention_days, max(retention_days) as max_retention_days, round(100.0 * sum(case when is_active then 1 else 0 end) / count(*), 1) as pct_policies_active, round(100.0 * sum(case when last_run_at >= date '2025-10-03' then 1 else 0 end) / count(*), 1) as pct_policies_run_in_last_90_days from retention_policies where entity_table in ('audit_log', 'access_log', 'change_history', 'data_subject_requests', 'compliance_events', 'login_attempts') group by entity_table order by entity_table
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: entity_table, tenants_with_policy, pct_tenants_with_policy, avg_retention_days, min_retention_days, max_retention_days, pct_policies_active, pct_policies_run_in_last_90_days
submitselect entity_table, count(distinct tenant_id) as tenants_with_policy, round(100.0 * count(distinct tenant_id) / 120, 1) as pct_tenants_with_policy, round(avg(retention_days), 1) as avg_retention_days, min(retention_days) as min_retention_days, max(retention_days) as max_retention_days, round(100.0 * sum(case when is_active then 1 else 0 end) / count(*), 1) as pct_policies_active, round(100.0 * sum(case when last_run_at >= date '2025-10-03' then 1 else 0 end) / count(*), 1) as pct_policies_run_in_last_90_days from retention_policies where entity_table in ('audit_log', 'access_log', 'change_history', 'data_subject_requests', 'compliance_events', 'login_attempts') group by entity_table order by entity_table
Submitted to tonight's batch. Your work on this request is done.