Queries · q322 · Chief Information Security Officer · week 2, night 4
“The 2 churned tenants found earlier with a still-active API key (Dewdrop Studio, Aurum & Ash) — do those specific keys carry elevated (admin_api) scope, and how recently were they actually used?”
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 4, request 3 of 3
Night 3 sharpened every thread nicely. Report 1 confirms the "no actor" gap in order_status_history is NOT cancellation-specific — it's 0.0% across every single status transition (pending, paid, fulfilled, cancelled, refunded; 150k+ rows, ~118 tenants) — a systemic table-wide property, not a narrow bug. Report 2 gives the clean #2 number: 88.3% of shoppers (9,910) are password-only with literally no backup factor, vs. 11.7% who at least inherit some protection from an OAuth provider — and recent-activity rates are nearly identical between the two groups (47.9% vs 46.9%), so OAuth adoption isn't just an artifact of inactive accounts. Report 3 is the most consequential: the long-lived (4+ year) active keys are dominated by integration-type keys ("ERP sync", "Accounting sync", "Warehouse management sync", "Marketplace product feed sync") nearly all carrying limit_scope = admin_api, used daily (every row shows ~5 days since last use, clustered right at month-end — looks like a nightly batch job), and they belong to our largest, most revenue-concentrated merchants (Cragsley Outfitters, Marrowby & Vane both appear with 4-5 such keys each). That's "old + broad + important account" together, exactly what Henrik flagged as the real risk combination.
Last night of the cycle — I want three closing numbers rather than opening new threads: (1) the full scope tally across all 76 old keys (the printed list only showed 50), (2) whether admin_api scope is concentrated in old keys specifically or is just how all integration keys are scoped regardless of age (tells us if "old+broad" is a rotation failure or a design norm), and (3) closing the loop between the churn and API-key findings by checking whether the 2 active keys sitting on churned tenants carry that same elevated scope.
The engineer · 2 dry runs on the empty schema, then the submission
select t.name as tenant_name, ak.name as key_name, (date '2026-01-05' - cast(ak.created_at as date)) as age_days, ak.last_used_at, (date '2026-01-05' - cast(ak.last_used_at as date)) as days_since_last_use, rl.limit_scope, rl.max_requests from api_keys ak join tenants t on t.id = ak.tenant_id and t.status = 'churned' left join rate_limits rl on rl.api_key_id = ak.id where ak.status = 'active' order by tenant_name, key_name
select t.name as tenant_name, ak.name as key_name, (date '2026-01-05' - cast(ak.created_at as date)) as age_days, ak.last_used_at, (date '2026-01-05' - cast(ak.last_used_at as date)) as days_since_last_use, rl.limit_scope, rl.max_requestsfrom api_keys akjoin tenants t on t.id = ak.tenant_id and t.status = 'churned'left join rate_limits rl on rl.api_key_id = ak.idwhere ak.status = 'active'order by tenant_name, key_nameorder by tenant_name, key_name, ak.id, rl.limit_scope
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. The query joins active api_keys to churned tenants, left-joins rate_limits on api_key_id, and computes age and recency in days relative to 2026-01-05, ordered by tenant then key name as requested.”
As Noah Fischer saw it, from the small dataset the agents worked on
tenant_name | key_name | age_days | last_used_at | days_since_last_use | limit_scope | max_requests ---------------+----------+----------+----------------------------+---------------------+-------------+------------- Aurum & Ash | ERP sync | 554 | 2025-12-31 23:53:43.999999 | 5 | admin_api | 120 Dewdrop Studio | ERP sync | 456 | 2025-12-31 22:05:15.999999 | 5 | admin_api | 120 (2 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
select t.name as tenant_name, ak.name as key_name, (date '2026-01-05' - cast(ak.created_at as date)) as age_days, ak.last_used_at, ak.last_used_at AS last_used_at, (date '2026-01-05' - cast(ak.last_used_at as date)) as days_since_last_use, rl.limit_scope, rl.max_requests rl.limit_scope AS limit_scope, rl.max_requests AS max_requestsfrom api_keys akjoin tenants t on t.id = ak.tenant_id and t.status = 'churned'left join rate_limits rl on rl.api_key_id = ak.idwhere ak.status = 'active'order by tenant_name, key_name, ak.id, rl.limit_scopeorder by tenant_name NULLS LAST, key_name NULLS LAST, ak.id NULLS LAST, rl.limit_scope NULLS LAST, rl.id NULLS LAST
select t.name as tenant_name, 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, (date '2026-01-05' - cast(ak.last_used_at as date)) as days_since_last_use, rl.limit_scope AS limit_scope, rl.max_requests AS max_requests from api_keys ak join tenants t on t.id = ak.tenant_id and t.status = 'churned' left join rate_limits rl on rl.api_key_id = ak.id where ak.status = 'active' order by tenant_name NULLS LAST, key_name NULLS LAST, ak.id NULLS LAST, rl.limit_scope NULLS LAST, rl.id 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: Active API keys on churned tenants: scope and recent use Question: The 2 churned tenants found earlier with a still-active API key (Dewdrop Studio, Aurum & Ash) — do those specific keys carry elevated (admin_api) scope, and how recently were they actually used? Grain: one row per active api_keys row belonging to a tenant with tenants.status = 'churned' Columns: - tenant name - key name - age in days (2026-01-05 minus created_at) - last_used_at - days since last use - associated rate_limits.limit_scope, if any - associated rate_limits.max_requests, if any Details: Source: api_keys where status = 'active', joined to tenants where tenants.status = 'churned', left-joined to rate_limits on api_key_id. Expect a small result (2 rows based on the 'Access still open on churned tenants' report from two nights ago, which found 1 active key each for Dewdrop Studio and Aurum & Ash). Order by tenant name, then key name. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect t.name as tenant_name, ak.name as key_name, (date '2026-01-05' - cast(ak.created_at as date)) as age_days, ak.last_used_at, (date '2026-01-05' - cast(ak.last_used_at as date)) as days_since_last_use, rl.limit_scope, rl.max_requests from api_keys ak join tenants t on t.id = ak.tenant_id and t.status = 'churned' left join rate_limits rl on rl.api_key_id = ak.id where ak.status = 'active' order by tenant_name, key_name
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, key_name, age_days, last_used_at, days_since_last_use, limit_scope, max_requests
dry_runselect t.name as tenant_name, ak.name as key_name, (date '2026-01-05' - cast(ak.created_at as date)) as age_days, ak.last_used_at, (date '2026-01-05' - cast(ak.last_used_at as date)) as days_since_last_use, rl.limit_scope, rl.max_requests from api_keys ak join tenants t on t.id = ak.tenant_id and t.status = 'churned' left join rate_limits rl on rl.api_key_id = ak.id where ak.status = 'active' order by tenant_name, key_name, ak.id, rl.limit_scope
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, key_name, age_days, last_used_at, days_since_last_use, limit_scope, max_requests
submitselect t.name as tenant_name, ak.name as key_name, (date '2026-01-05' - cast(ak.created_at as date)) as age_days, ak.last_used_at, (date '2026-01-05' - cast(ak.last_used_at as date)) as days_since_last_use, rl.limit_scope, rl.max_requests from api_keys ak join tenants t on t.id = ak.tenant_id and t.status = 'churned' left join rate_limits rl on rl.api_key_id = ak.id where ak.status = 'active' order by tenant_name, key_name, ak.id, rl.limit_scope
Submitted to tonight's batch. Your work on this request is done.