Queries · q070 · Head of Risk and Payments · week 1, night 2
“Which permission categories represent real elevated/admin-type access, how many staff users hold each, and do any non-active (invited/suspended/deactivated) staff still hold them?”
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 2, request 2 of 3
Reading night 1 critically before spending more slots:
Report 1 (DSAR): Volumes are tiny (0-4/month) but several months show "still_open_overdue" = 1, including one from as far back as 2023-05 and 2024-07, still unresolved as of 2025-12-31. That is a serious red flag for the exact claim Aylin expects to be pressed on — unless it's a data-definition artifact. The engineer's note says "completed" = completed_at IS NOT NULL, bypassing the status column entirely. If a request was closed as e.g. "rejected" or "cancelled" without completed_at being populated, it would wrongly show as "still open and overdue" forever. I need to reconcile against the actual status column before we can honestly tell Aylin whether these are real breaches or a timestamp/status mismatch (this is exactly Rosa's "timestamp trust" concern).
Report 2 (Access): My own request had a flawed proxy — I treated roles.is_system (="built-in, cannot be deleted role") as "elevated/admin access," but 100% of active staff (548/548) hold a system role, which only makes sense if is_system marks a default baseline role every user gets, not a privilege tier. I need to re-derive "elevated access" from actual permissions/role_permissions data instead. Also notable: only 2 staff users total are deactivated and zero are suspended/invited across 120 tenants and 3 years — worth separately checking whether access is actively managed at all (grants/revocations happening over time) rather than static.
Report 3 (Audit log continuity): Mostly reassuring — audit_log volume is steady (~3-5k/month) after the Jan 2024 migration from archive-only logging, actor_user_id nulls track almost exactly with system/api actor_type (so nulls aren't unexplained — they're mostly non-human actors, which is a decent signal for Rosa's "one actor one identity" concern). One thing to flag: all of 2023's audit activity lives only in audit_log_archive (live table has 0 rows pre-Dec-2023) — that's an artifact of the archival job's cutoff, not a gap, but I should keep unioning both tables going forward rather than relying on audit_log alone in any future report.
Tonight (3 slots), I'm staying on Aylin's top two priorities to fix these data-quality issues before we write anything down: (1) reconcile the DSAR "open/overdue" numbers against the actual status column and check SLA-window (due_at) consistency; (2) replace the flawed is_system access proxy with a real permission-category-based view of elevated access by staff status; (3) check whether role grants/revocations are actively happening over time (evidence access is "reviewed/current") and whether those changes are attributable to identifiable actors. I'm holding off on priority #2 (MFA/login security) and #4 (retention) until night 3, and will sync with Aylin before Thursday as agreed.
The engineer · 1 dry run on the empty schema, then the submission
WITH staff_totals AS ( SELECT count(*) AS total_staff FROM users WHERE is_staff = true AND deleted_at IS NULL ), cat_users AS ( SELECT DISTINCT p.category, u.id AS user_id, u.status FROM permissions p JOIN role_permissions rp ON rp.permission_id = p.id JOIN user_roles ur ON ur.role_id = rp.role_id JOIN users u ON u.id = ur.user_id WHERE u.is_staff = true AND u.deleted_at IS NULL ), cat_perm_counts AS ( SELECT category, count(DISTINCT code) AS distinct_permission_codes FROM permissions GROUP BY category ) SELECT cu.category, count(*) AS staff_users_with_category, round(100.0 * count(*) / st.total_staff, 2) AS pct_of_all_staff, count(*) FILTER (WHERE cu.status = 'active') AS active_count, count(*) FILTER (WHERE cu.status IN ('invited','suspended','deactivated')) AS non_active_count, cpc.distinct_permission_codes FROM cat_users cu CROSS JOIN staff_totals st JOIN cat_perm_counts cpc ON cpc.category = cu.category GROUP BY cu.category, st.total_staff, cpc.distinct_permission_codes ORDER BY staff_users_with_category DESC, cu.category
“Query compiles and returns the expected columns. Submitting.”
“Submitted. The report gives one row per permission category with the distinct staff headcount holding it, its share of all staff, the active vs. non-active (invited/suspended/deactivated) split, and the category's total distinct permission codes for context — ordered by staff_users_with_category descending, which should make it easy for Claire/Aylin to spot sensitive categories (billing, security, admin, user-management) with meaningful non_active_count as the stale-access signal.”
As Claire Dubois saw it, from the small dataset the agents worked on
category | staff_users_with_category | pct_of_all_staff | active_count | non_active_count | distinct_permission_codes -----------+---------------------------+------------------+--------------+------------------+-------------------------- catalog | 546 | 99.27 | 544 | 2 | 2 orders | 524 | 95.27 | 522 | 2 | 3 customers | 506 | 92.0 | 504 | 2 | 3 support | 479 | 87.09 | 477 | 2 | 2 inventory | 290 | 52.73 | 290 | 0 | 2 analytics | 282 | 51.27 | 282 | 0 | 1 marketing | 264 | 48.0 | 264 | 0 | 2 billing | 249 | 45.27 | 249 | 0 | 2 crm | 248 | 45.09 | 248 | 0 | 2 compliance | 245 | 44.55 | 245 | 0 | 2 settings | 245 | 44.55 | 245 | 0 | 5 (11 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH staff_totals AS ( SELECT count(*) AS total_staff FROM users WHERE is_staff = true AND deleted_at IS NULL),cat_users AS ( SELECT DISTINCT p.category, u.id AS user_id, u.status SELECT DISTINCT p.category AS category, u.id AS user_id, u.status AS status FROM permissions p JOIN role_permissions rp ON rp.permission_id = p.id JOIN user_roles ur ON ur.role_id = rp.role_id JOIN users u ON u.id = ur.user_id WHERE u.is_staff = true AND u.deleted_at IS NULL),cat_perm_counts AS ( SELECT category, count(DISTINCT code) AS distinct_permission_codes FROM permissions GROUP BY category)SELECT cu.category, cu.category AS category, count(*) AS staff_users_with_category, round(100.0 * count(*) / st.total_staff, 2) AS pct_of_all_staff, 100.0 * count(*) / st.total_staff AS pct_of_all_staff, count(*) FILTER (WHERE cu.status = 'active') AS active_count, count(*) FILTER (WHERE cu.status IN ('invited','suspended','deactivated')) AS non_active_count, cpc.distinct_permission_codes cpc.distinct_permission_codes AS distinct_permission_codesFROM cat_users cuCROSS JOIN staff_totals stJOIN cat_perm_counts cpc ON cpc.category = cu.categoryGROUP BY cu.category, st.total_staff, cpc.distinct_permission_codesORDER BY staff_users_with_category DESC, cu.categoryORDER BY staff_users_with_category DESC NULLS FIRST, cu.category NULLS LAST
WITH staff_totals AS ( SELECT count(*) AS total_staff FROM users WHERE is_staff = true AND deleted_at IS NULL ), cat_users AS ( SELECT DISTINCT p.category AS category, u.id AS user_id, u.status AS status FROM permissions p JOIN role_permissions rp ON rp.permission_id = p.id JOIN user_roles ur ON ur.role_id = rp.role_id JOIN users u ON u.id = ur.user_id WHERE u.is_staff = true AND u.deleted_at IS NULL ), cat_perm_counts AS ( SELECT category, count(DISTINCT code) AS distinct_permission_codes FROM permissions GROUP BY category ) SELECT cu.category AS category, count(*) AS staff_users_with_category, 100.0 * count(*) / st.total_staff AS pct_of_all_staff, count(*) FILTER (WHERE cu.status = 'active') AS active_count, count(*) FILTER (WHERE cu.status IN ('invited','suspended','deactivated')) AS non_active_count, cpc.distinct_permission_codes AS distinct_permission_codes FROM cat_users cu CROSS JOIN staff_totals st JOIN cat_perm_counts cpc ON cpc.category = cu.category GROUP BY cu.category, st.total_staff, cpc.distinct_permission_codes ORDER BY staff_users_with_category DESC NULLS FIRST, cu.category 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: Elevated access by permission category and staff status Question: Which permission categories represent real elevated/admin-type access, how many staff users hold each, and do any non-active (invited/suspended/deactivated) staff still hold them? Grain: one row per permissions.category value, computed over users where is_staff = true across all tenants, as a current-state snapshot as of 2025-12-31 Columns: - category - staff_users_with_category (distinct is_staff=true users holding >=1 role granting a permission in this category, via user_roles -> role_permissions -> permissions) - pct_of_all_staff (that count divided by total is_staff=true, non-deleted user count) - active_count (of those users, status = 'active') - non_active_count (status in invited/suspended/deactivated) - distinct_permission_codes (count of distinct permissions.code in this category, for context) Details: Sources: permissions (category, code), role_permissions, user_roles, users (is_staff=true, exclude deleted_at not null). This replaces last night's is_system proxy, which turned out not to distinguish privilege level (100% of staff held an is_system role). The goal is to find which categories look sensitive (e.g. billing, security, admin, user-management) and whether non_active_count is non-trivial for those — that would be the stale-access signal Aylin is asking about. Order by staff_users_with_category descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH staff_totals AS ( SELECT count(*) AS total_staff FROM users WHERE is_staff = true AND deleted_at IS NULL ), cat_users AS ( SELECT DISTINCT p.category, u.id AS user_id, u.status FROM permissions p JOIN role_permissions rp ON rp.permission_id = p.id JOIN user_roles ur ON ur.role_id = rp.role_id JOIN users u ON u.id = ur.user_id WHERE u.is_staff = true AND u.deleted_at IS NULL ), cat_perm_counts AS ( SELECT category, count(DISTINCT code) AS distinct_permission_codes FROM permissions GROUP BY category ) SELECT cu.category, count(*) AS staff_users_with_category, round(100.0 * count(*) / st.total_staff, 2) AS pct_of_all_staff, count(*) FILTER (WHERE cu.status = 'active') AS active_count, count(*) FILTER (WHERE cu.status IN ('invited','suspended','deactivated')) AS non_active_count, cpc.distinct_permission_codes FROM cat_users cu CROSS JOIN staff_totals st JOIN cat_perm_counts cpc ON cpc.category = cu.category GROUP BY cu.category, st.total_staff, cpc.distinct_permission_codes ORDER BY staff_users_with_category DESC, cu.category
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: category, staff_users_with_category, pct_of_all_staff, active_count, non_active_count, distinct_permission_codes
submitWITH staff_totals AS ( SELECT count(*) AS total_staff FROM users WHERE is_staff = true AND deleted_at IS NULL ), cat_users AS ( SELECT DISTINCT p.category, u.id AS user_id, u.status FROM permissions p JOIN role_permissions rp ON rp.permission_id = p.id JOIN user_roles ur ON ur.role_id = rp.role_id JOIN users u ON u.id = ur.user_id WHERE u.is_staff = true AND u.deleted_at IS NULL ), cat_perm_counts AS ( SELECT category, count(DISTINCT code) AS distinct_permission_codes FROM permissions GROUP BY category ) SELECT cu.category, count(*) AS staff_users_with_category, round(100.0 * count(*) / st.total_staff, 2) AS pct_of_all_staff, count(*) FILTER (WHERE cu.status = 'active') AS active_count, count(*) FILTER (WHERE cu.status IN ('invited','suspended','deactivated')) AS non_active_count, cpc.distinct_permission_codes FROM cat_users cu CROSS JOIN staff_totals st JOIN cat_perm_counts cpc ON cpc.category = cu.category GROUP BY cu.category, st.total_staff, cpc.distinct_permission_codes ORDER BY staff_users_with_category DESC, cu.category
Submitted to tonight's batch. Your work on this request is done.