Queries · q320 · Chief Information Security Officer · week 2, night 4

Scope tally across all 76 long-lived (4+ year) active API keys

“Of all 76 active API keys that are 4+ years old, how many actually carry the broad 'admin_api' rate-limit scope versus no scope recorded at all — the full count, not just the 50 printed last night?”

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

1. Henrik Aalto writes to Noah Fischer

   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.

   1. The audit trail gap is bigger than I initially feared. If we can't show who deleted something and when for one of our most common objects, I have to assume the same is true for the things a security reviewer or a merchant's own auditor would actually ask about — customer records, orders, discounts, financial documents. Can you check whether this is a product-wide blind spot or specific to a few areas? I need to know how wide the hole is before I can tell leadership whether this is "fix one thing" or "fix how deletion logging works everywhere."
   2. Shopper MFA at zero is either a non-issue or a real liability — I need to know which. Before this goes anywhere near a customer-facing security questionnaire, I want to understand what actually protects a shopper's account today if MFA genuinely isn't offered to them — is there anything else standing in for it (unusual-device checks, step-up verification at checkout, that sort of thing)? I don't want to either overstate a gap that doesn't matter or understate one that does.
   3. The churn-deactivation problem — how far does it go? You found it through privileged roles, but I suspect it's broader: if accounts aren't deprovisioned when a tenant churns, that likely applies to their sessions, their API access, everything tied to that tenant, not just role holders. I'd like a sense of the overall scale of "access that should have been turned off but wasn't" so I can size the fix properly.
   4. On the old API keys — are any of them doing something sensitive? Age alone isn't the risk; age combined with broad access is. Before I recommend a rotation mandate, I'd like to know whether those oldest keys are concentrated among a few tenants or integrations, and whether any of them look like they carry elevated access rather than routine, narrow use.

   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

   Show the whole note

2. Noah Fischer plans the night

   The analyst · week 2, night 4, request 1 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.

   Read the whole plan
   Scope tally across all 76 long-lived (4+ year) active API keys

   Question

   Of all 76 active API keys that are 4+ years old, how many actually carry the broad 'admin_api' rate-limit scope versus no scope recorded at all — the full count, not just the 50 printed last night?

   One row per

   distinct limit_scope value (including a 'no scope recorded' category for keys with no matching rate_limits row)

   Columns

   • scope category ('admin_api', any other distinct limit_scope values found, or 'no scope recorded')
   • distinct key count (count distinct api_keys.id, not rate_limits rows)
   • share of the 76 keys (%)
   • distinct tenants represented

3. Felix writes the SQL

   The engineer · 1 dry run on the empty schema, then the submission

   Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

   SELECT
     COALESCE(rl.limit_scope, 'no scope recorded') AS scope_category,
     COUNT(DISTINCT ak.id) AS distinct_key_count,
     ROUND(COUNT(DISTINCT ak.id) * 100.0 / 76, 1) AS share_pct,
     COUNT(DISTINCT ak.tenant_id) AS distinct_tenants
   FROM (
     SELECT id, tenant_id
     FROM api_keys
     WHERE status = 'active'
       AND created_at < DATE '2022-01-05'
   ) ak
   LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id
   GROUP BY COALESCE(rl.limit_scope, 'no scope recorded')
   ORDER BY distinct_key_count DESC, scope_category ASC

   “Query compiles and matches the requested grain and columns. Submitting.”

   Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

   “Submitted. The report aggregates the same 76-key population (active API keys with created_at before 2022-01-05) by rate_limits.limit_scope, counting distinct keys and tenants per scope category, with a "no scope recorded" bucket for keys lacking any matching rate_limits row, ordered by key count descending.”

4. The report comes back overnight

   As Noah Fischer saw it, from the small dataset the agents worked on

   scope_category    | distinct_key_count | share_pct | distinct_tenants
   ------------------+--------------------+-----------+-----------------
   admin_api         |                 53 |      69.7 |               25
   no scope recorded |                 23 |      30.3 |               23
   (2 rows)

5. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q321Is admin_api scope concentrated in old keys, or standard across all ages?

   It builds on:

   • q319Detail list of the oldest active API keys (4+ years), by tenant

6. It becomes benchmark query q320

   Changed only where the benchmark needs the same result on every system

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
   The benchmark text, against the submitted SQL

   SELECT  COALESCE(rl.limit_scope, 'no scope recorded') AS scope_category,  COUNT(DISTINCT ak.id) AS distinct_key_count,  ROUND(COUNT(DISTINCT ak.id) * 100.0 / 76, 1) AS share_pct,  COUNT(DISTINCT ak.id) * 100.0 / 76 AS share_pct,  COUNT(DISTINCT ak.tenant_id) AS distinct_tenantsFROM (  SELECT id, tenant_id  FROM api_keys  WHERE status = 'active'    AND created_at < DATE '2022-01-05') akLEFT JOIN rate_limits rl ON rl.api_key_id = ak.idGROUP BY COALESCE(rl.limit_scope, 'no scope recorded')ORDER BY distinct_key_count DESC, scope_category ASCORDER BY distinct_key_count DESC NULLS FIRST, scope_category ASC NULLS LAST

   As each system received it; highlighted, the parts spelled for that system

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   SELECT
     COALESCE(rl.limit_scope, 'no scope recorded') AS scope_category,
     COUNT(DISTINCT ak.id) AS distinct_key_count,
     COUNT(DISTINCT ak.id) * 100.0 / 76 AS share_pct,
     COUNT(DISTINCT ak.tenant_id) AS distinct_tenants
   FROM (
     SELECT id, tenant_id
     FROM api_keys
     WHERE status = 'active'
       AND created_at < DATE '2022-01-05'
   ) ak
   LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id
   GROUP BY COALESCE(rl.limit_scope, 'no scope recorded')
   ORDER BY distinct_key_count DESC NULLS FIRST, scope_category ASC NULLS LAST

   Scale factor 1000, c8a.24xlarge, after the last load. q320 on the start page, with every configuration.

7. Everything, as recorded

   The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

   Felix's whole session, 7 turns

   • The request, as Felix received it

     Report request from Noah Fischer (analyst for the Chief Information Security Officer):
     
     Title: Scope tally across all 76 long-lived (4+ year) active API keys
     Question: Of all 76 active API keys that are 4+ years old, how many actually carry the broad 'admin_api' rate-limit scope versus no scope recorded at all — the full count, not just the 50 printed last night?
     Grain: one row per distinct limit_scope value (including a 'no scope recorded' category for keys with no matching rate_limits row)
     Columns:
     - scope category ('admin_api', any other distinct limit_scope values found, or 'no scope recorded')
     - distinct key count (count distinct api_keys.id, not rate_limits rows)
     - share of the 76 keys (%)
     - distinct tenants represented
     Details: Source: api_keys where status = 'active' and created_at < (2026-01-05 minus 4 years) — the same 76-key population as last night's detail report — left-joined to rate_limits on api_key_id. Group by rate_limits.limit_scope (treat null, i.e. no matching rate_limits row, as its own category 'no scope recorded'). Count DISTINCT api_keys.id per group so a key is not double-counted if it somehow has more than one rate_limits row. Order by distinct key count descending, scope category as tie-break.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Felix calls dry_run

     SELECT
       COALESCE(rl.limit_scope, 'no scope recorded') AS scope_category,
       COUNT(DISTINCT ak.id) AS distinct_key_count,
       ROUND(COUNT(DISTINCT ak.id) * 100.0 / 76, 1) AS share_pct,
       COUNT(DISTINCT ak.tenant_id) AS distinct_tenants
     FROM (
       SELECT id, tenant_id
       FROM api_keys
       WHERE status = 'active'
         AND created_at < DATE '2022-01-05'
     ) ak
     LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id
     GROUP BY COALESCE(rl.limit_scope, 'no scope recorded')
     ORDER BY distinct_key_count DESC, scope_category ASC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: scope_category, distinct_key_count, share_pct, distinct_tenants

   • FelixQuery compiles and matches the requested grain and columns. Submitting.
   • Felix calls submit

     SELECT
       COALESCE(rl.limit_scope, 'no scope recorded') AS scope_category,
       COUNT(DISTINCT ak.id) AS distinct_key_count,
       ROUND(COUNT(DISTINCT ak.id) * 100.0 / 76, 1) AS share_pct,
       COUNT(DISTINCT ak.tenant_id) AS distinct_tenants
     FROM (
       SELECT id, tenant_id
       FROM api_keys
       WHERE status = 'active'
         AND created_at < DATE '2022-01-05'
     ) ak
     LEFT JOIN rate_limits rl ON rl.api_key_id = ak.id
     GROUP BY COALESCE(rl.limit_scope, 'no scope recorded')
     ORDER BY distinct_key_count DESC, scope_category ASC

   • The warehouse answers

     Submitted to tonight's batch. Your work on this request is done.

   • FelixSubmitted. The report aggregates the same 76-key population (active API keys with `created_at` before 2022-01-05) by `rate_limits.limit_scope`, counting distinct keys and tenants per scope category, with a "no scope recorded" bucket for keys lacking any matching `rate_limits` row, ordered by key count descending.

   Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

   Felix's transcript (260 KB) · Noah Fischer's transcript (875 KB) · Henrik Aalto's transcript (262 KB) · the night's plan (5 KB) · the night's reports (3 KB) · the week's memo (3 KB)