Queries · q319 · Chief Information Security Officer · week 2, night 3
“Henrik wants to know if the oldest, longest-unrotated API keys are concentrated among a few tenants/integrations and whether any show signs of broad/elevated access rather than narrow, routine use — here's the actual list.”
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 2
Subject: Re: Account security snapshot — good start, three gaps worry me more now
Noah,
This is a strong first week — thank you. The sign-in and access-governance findings give me confidence I can defend in a questionnaire. Three things genuinely surprised me and I want to understand them better before Friday's memo, plus one follow-up.
If we can only chase a couple of these deeply this week, I'd prioritize the deletion-audit scope (#1) and the churn-deactivation scope (#3) — those are the two a due-diligence team is most likely to ask us to prove, one way or the other.
Thanks, Henrik
The analyst · week 2, night 3, request 3 of 3
Big reversal on #1 tonight's results refine, not confirm, last week's alarm: order_status_history shows 100% coverage of cancellations across every one of the 50 tenants printed — Tidewick DOES have a timestamped record of every cancellation, just not in generic audit_log. But matching_rows_with_actor is 0 for every tenant — the WHO is missing even though the WHEN is solid. That's a much narrower, more precise finding than "we can't reconstruct order history." Invoice voids turn out to not exist at all (0 ever), so that's a non-issue; but 85 "uncollectible" write-offs across 11 tenants have genuinely 0% audit trail and no purpose-built history table standing in — that's the real, confirmed financial-document gap. And the churn-session risk mostly evaporates: all never-revoked sessions on all 9 churned tenants are already naturally expired (0 still valid) — so the real residual churn risk is just account status (100% of users, all 9 tenants) plus the 2 tenants with a live API key/webhook found last week.
With #1 and #3 now reasonably well-characterized, tonight I'm clearing Henrik's remaining two questions: whether the "no actor" gap in order_status_history is specific to cancellations or systemic across all transitions (quick, sharpens the #1 story), what actually stands in for shopper MFA (oauth-linked login vs raw password), and who specifically holds the oldest active API keys so we can judge concentration/sensitivity for #4.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS tenant_name, t.status AS tenant_status, ak.name AS key_name, (DATE '2026-01-05' - ak.created_at::date) AS age_days, ak.last_used_at, CASE WHEN ak.last_used_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - ak.last_used_at::date) END AS days_since_last_use, rl.limit_scope, rl.max_requests, COUNT(DISTINCT ak.id) OVER () AS total_matching_keys FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' AND ak.created_at < DATE '2022-01-05' ORDER BY age_days DESC, tenant_name ASC, key_name ASC, rl.id ASC LIMIT 50
“Submitted. Summary of what the query does:
- Grain: one row per api_keys row matching the 4+ year active bucket, with an extra row per additional rate_limits entry when a key has more than one scope (via LEFT JOIN).
- Filter: status = 'active' and created_at < 2022-01-05 (4 years before the reporting date).
- Columns: tenant name/status, key name, age in days, last_used_at, days since last use, rate_limit scope, rate_limit max_requests, and a total_matching_keys window count so the analyst can see the true total even though only 50 rows print.
- Order: oldest first (age_days desc), tenant name as tie-break, then key name and rate_limit id for full determinism.
Noted in the submission: tenant-wide rate limits (null api_key_id) are excluded since they aren't scoped to a specific key, and the total count reflects distinct keys before the 50-row cap.”
As Noah Fischer saw it, from the small dataset the agents worked on
tenant_name | tenant_status | key_name | age_days | last_used_at | days_since_last_use | limit_scope | max_requests | total_matching_keys ------------------------------+---------------+-------------------------------+----------+----------------------------+---------------------+-------------+--------------+-------------------- Grind Collective | past_due | Storefront app | 2743 | 2025-12-31 22:35:59.999999 | 5 | NULL | NULL | 76 Grind Collective | past_due | ERP sync | 2737 | 2025-12-31 22:58:22.999999 | 5 | admin_api | 120 | 76 Grind Collective | past_due | Marketplace product feed sync | 2706 | 2025-12-31 22:08:33.999999 | 5 | admin_api | 120 | 76 Marrowby & Vane | active | Storefront app | 2666 | 2025-12-31 23:14:49.999999 | 5 | NULL | NULL | 76 Marrowby & Vane | active | Marketplace product feed sync | 2663 | 2025-12-31 23:23:30.999999 | 5 | admin_api | 120 | 76 Marrowby & Vane | active | Accounting sync | 2653 | 2025-12-31 22:45:37.999999 | 5 | admin_api | 120 | 76 Marrowby & Vane | active | ERP sync | 2651 | 2025-12-31 23:48:45.999999 | 5 | admin_api | 120 | 76 Marrowby & Vane | active | Warehouse management sync | 2648 | 2025-12-31 22:42:37.999999 | 5 | admin_api | 120 | 76 Juniper Apothecary | active | Storefront app | 2630 | 2025-12-31 23:09:28.999999 | 5 | NULL | NULL | 76 Fennel & Flint | active | Storefront app | 2516 | 2025-12-31 22:46:28.999999 | 5 | NULL | NULL | 76 Fennel & Flint | active | ERP sync | 2515 | 2025-12-31 22:19:05.999999 | 5 | admin_api | 120 | 76 Fennel & Flint | active | Warehouse management sync | 2515 | 2025-12-31 22:47:16.999999 | 5 | admin_api | 120 | 76 Fennel & Flint | active | Marketplace product feed sync | 2504 | 2025-12-31 22:03:22.999999 | 5 | admin_api | 120 | 76 Willow & Wren | active | Storefront app | 2489 | 2025-12-31 23:12:29.999999 | 5 | NULL | NULL | 76 Cragsley Outfitters | active | Storefront app | 2469 | 2025-12-31 23:04:46.999999 | 5 | NULL | NULL | 76 Cragsley Outfitters | active | Marketplace product feed sync | 2460 | 2025-12-31 22:59:59.999999 | 5 | admin_api | 120 | 76 Cragsley Outfitters | active | ERP sync | 2442 | 2025-12-31 22:36:41.999999 | 5 | admin_api | 120 | 76 Cragsley Outfitters | active | Accounting sync | 2438 | 2025-12-31 22:15:35.999999 | 5 | admin_api | 120 | 76 Cragsley Outfitters | active | Warehouse management sync | 2433 | 2025-12-31 22:24:12.999999 | 5 | admin_api | 120 | 76 Honeycomb Larder | active | Storefront app | 2388 | 2025-12-31 23:36:03.999999 | 5 | NULL | NULL | 76 Wohnglück | active | ERP sync | 2377 | 2025-12-31 23:21:04.999999 | 5 | admin_api | 120 | 76 Ballycorran Industrial Supply | active | Storefront app | 2372 | 2025-12-31 23:32:14.999999 | 5 | NULL | NULL | 76 Spice Route Box | active | Storefront app | 2371 | 2025-12-31 22:34:03.999999 | 5 | NULL | NULL | 76 Wohnglück | active | Warehouse management sync | 2357 | 2025-12-31 22:19:26.999999 | 5 | admin_api | 120 | 76 Ballycorran Industrial Supply | active | Marketplace product feed sync | 2349 | 2025-12-31 22:08:50.999999 | 5 | admin_api | 120 | 76 Spice Route Box | active | ERP sync | 2346 | 2025-12-31 23:24:45.999999 | 5 | admin_api | 120 | 76 Ballycorran Industrial Supply | active | ERP sync | 2341 | 2025-12-31 22:34:42.999999 | 5 | admin_api | 120 | 76 Rue Lafayette Studio | active | Storefront app | 2325 | 2025-12-31 23:14:44.999999 | 5 | NULL | NULL | 76 Rue Lafayette Studio | active | ERP sync | 2289 | 2025-12-31 22:23:51.999999 | 5 | admin_api | 120 | 76 OfficeHub Nordic | active | ERP sync | 2275 | 2025-12-31 23:35:33.999999 | 5 | admin_api | 120 | 76 Velo Noord | active | ERP sync | 2271 | 2025-12-31 23:51:26.999999 | 5 | admin_api | 120 | 76 Ostwind Mode | active | ERP sync | 2259 | 2025-12-31 23:27:02.999999 | 5 | admin_api | 120 | 76 Ampfeld Elektronik | active | Storefront app | 2252 | 2025-12-31 22:56:54.999999 | 5 | NULL | NULL | 76 Ampfeld Elektronik | active | Warehouse management sync | 2252 | 2025-12-31 22:48:30.999999 | 5 | admin_api | 120 | 76 Ampfeld Elektronik | active | Accounting sync | 2236 | 2025-12-31 22:16:21.999999 | 5 | admin_api | 120 | 76 Ampfeld Elektronik | active | ERP sync | 2216 | 2025-12-31 23:09:07.999999 | 5 | admin_api | 120 | 76 Ampfeld Elektronik | active | Marketplace product feed sync | 2214 | 2025-12-31 22:04:43.999999 | 5 | admin_api | 120 | 76 Brennquell Kaffee | active | Storefront app | 2155 | 2025-12-31 23:53:03.999999 | 5 | NULL | NULL | 76 Brennquell Kaffee | active | Marketplace product feed sync | 2147 | 2025-12-31 22:04:12.999999 | 5 | admin_api | 120 | 76 Brennquell Kaffee | active | Warehouse management sync | 2145 | 2025-12-31 23:46:20.999999 | 5 | admin_api | 120 | 76 Brennquell Kaffee | active | Accounting sync | 2139 | 2025-12-31 22:49:48.999999 | 5 | admin_api | 120 | 76 Kontor24 Bürobedarf | active | Storefront app | 2106 | 2025-12-31 23:20:17.999999 | 5 | NULL | NULL | 76 Kontor24 Bürobedarf | active | Warehouse management sync | 2103 | 2025-12-31 22:04:31.999999 | 5 | admin_api | 120 | 76 Kontor24 Bürobedarf | active | ERP sync | 2099 | 2025-12-31 22:00:32.999999 | 5 | admin_api | 120 | 76 Sourdough Club | active | Storefront app | 2096 | 2025-12-31 23:00:34.999999 | 5 | NULL | NULL | 76 Kettle & Key | active | ERP sync | 2057 | 2025-12-31 22:49:44.999999 | 5 | admin_api | 120 | 76 Mirelle | active | ERP sync | 2026 | 2025-12-31 23:56:22.999999 | 5 | admin_api | 120 | 76 Studio Vrij | active | Storefront app | 2022 | 2025-12-31 22:35:57.999999 | 5 | NULL | NULL | 76 Studio Vrij | active | Accounting sync | 2015 | 2025-12-31 22:44:41.999999 | 5 | admin_api | 120 | 76 Studio Vrij | active | ERP sync | 2012 | 2025-12-31 23:12:49.999999 | 5 | admin_api | 120 | 76 (50 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 t.name AS tenant_name, t.status AS tenant_status, ak.name AS key_name, (DATE '2026-01-05' - ak.created_at::date) AS age_days, ak.last_used_at, (DATE '2026-01-05' - CAST(ak.created_at AS date)) AS age_days, ak.last_used_at AS last_used_at, CASE WHEN ak.last_used_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - ak.last_used_at::date) ELSE (DATE '2026-01-05' - CAST(ak.last_used_at AS date)) END AS days_since_last_use, rl.limit_scope, rl.max_requests, COUNT(DISTINCT ak.id) OVER () AS total_matching_keys rl.limit_scope AS limit_scope, rl.max_requests AS max_requests, {{count_distinct_over(ak.id, ())}} AS total_matching_keysFROM api_keys akJOIN tenants t ON t.id = ak.tenant_idLEFT JOIN rate_limits rl ON rl.api_key_id = ak.idWHERE ak.status = 'active' AND ak.created_at < DATE '2022-01-05'ORDER BY age_days DESC, tenant_name ASC, key_name ASC, rl.id ASCLIMIT 50ORDER BY age_days DESC NULLS FIRST, tenant_name ASC NULLS LAST, key_name ASC NULLS LAST, rl.id ASC NULLS LAST, ak.id ASC NULLS LAST{{fetch_first(50)}}
SELECT t.name AS tenant_name, t.status AS tenant_status, ak.name AS key_name, (DATE '2026-01-05' - CAST(ak.created_at AS date)) AS age_days, ak.last_used_at AS last_used_at, CASE WHEN ak.last_used_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(ak.last_used_at AS date)) END AS days_since_last_use, rl.limit_scope AS limit_scope, rl.max_requests AS max_requests, COUNT(DISTINCT ak.id) OVER () AS total_matching_keys FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' AND ak.created_at < DATE '2022-01-05' ORDER BY age_days DESC NULLS FIRST, tenant_name ASC NULLS LAST, key_name ASC NULLS LAST, rl.id ASC NULLS LAST, ak.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS tenant_name, t.status AS tenant_status, ak.name AS key_name, (DATE '2026-01-05' - CAST(ak.created_at AS date)) AS age_days, ak.last_used_at AS last_used_at, CASE WHEN ak.last_used_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(ak.last_used_at AS date)) END AS days_since_last_use, rl.limit_scope AS limit_scope, rl.max_requests AS max_requests, (DENSE_RANK() OVER ( ORDER BY ak.id ASC NULLS FIRST) + DENSE_RANK() OVER ( ORDER BY ak.id DESC NULLS LAST) - 1 - MAX(CASE WHEN ak.id IS NULL THEN 1 ELSE 0 END) OVER ()) AS total_matching_keys FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' AND ak.created_at < DATE '2022-01-05' ORDER BY age_days DESC NULLS FIRST, tenant_name ASC NULLS LAST, key_name ASC NULLS LAST, rl.id ASC NULLS LAST, ak.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS tenant_name, t.status AS tenant_status, ak.name AS key_name, (DATE '2026-01-05' - CAST(ak.created_at AS date)) AS age_days, ak.last_used_at AS last_used_at, CASE WHEN ak.last_used_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - CAST(ak.last_used_at AS date)) END AS days_since_last_use, rl.limit_scope AS limit_scope, rl.max_requests AS max_requests, COUNT(DISTINCT ak.id) OVER () AS total_matching_keys FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' AND ak.created_at < DATE '2022-01-05' ORDER BY age_days DESC NULLS FIRST, tenant_name ASC NULLS LAST, key_name ASC NULLS LAST, rl.id ASC NULLS LAST, ak.id ASC NULLS LAST LIMIT 50
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: Detail list of the oldest active API keys (4+ years), by tenant Question: Henrik wants to know if the oldest, longest-unrotated API keys are concentrated among a few tenants/integrations and whether any show signs of broad/elevated access rather than narrow, routine use — here's the actual list. Grain: one row per api_keys row in the '4+ years' active age bucket (created_at more than 4 years before 2026-01-05, status = 'active') Columns: - tenant name - tenant status - key name - age in days (2026-01-05 minus created_at) - last_used_at - days since last use - associated rate_limits scope, if any (limit_scope from rate_limits where api_key_id matches) - associated rate_limits max_requests per window, if any Details: Source: api_keys where status = 'active' and created_at < (2026-01-05 minus 4 years), left-joined to tenants and to rate_limits (api_key_id = api_keys.id) for scope context — if a key has multiple rate_limits rows, include all of them as additional rows for that key (or note in the report if a key has more than one scope). Order by age in days descending (oldest first), tenant name as tie-break. Cap at 50 rows; note total matching keys (expect ~76 based on last week's age-bucket report). Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name AS tenant_name, t.status AS tenant_status, ak.name AS key_name, (DATE '2026-01-05' - ak.created_at::date) AS age_days, ak.last_used_at, CASE WHEN ak.last_used_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - ak.last_used_at::date) END AS days_since_last_use, rl.limit_scope, rl.max_requests, COUNT(DISTINCT ak.id) OVER () AS total_matching_keys FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' AND ak.created_at < DATE '2022-01-05' ORDER BY age_days DESC, tenant_name ASC, key_name ASC, rl.id ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, tenant_status, key_name, age_days, last_used_at, days_since_last_use, limit_scope, max_requests, total_matching_keys
submitSELECT t.name AS tenant_name, t.status AS tenant_status, ak.name AS key_name, (DATE '2026-01-05' - ak.created_at::date) AS age_days, ak.last_used_at, CASE WHEN ak.last_used_at IS NULL THEN NULL ELSE (DATE '2026-01-05' - ak.last_used_at::date) END AS days_since_last_use, rl.limit_scope, rl.max_requests, COUNT(DISTINCT ak.id) OVER () AS total_matching_keys FROM api_keys ak JOIN tenants t ON t.id = ak.tenant_id LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' AND ak.created_at < DATE '2022-01-05' ORDER BY age_days DESC, tenant_name ASC, key_name ASC, rl.id ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.