Queries · q110 · Chief Information Security Officer · week 1, night 4

Active API key age distribution (rotation hygiene)

“Platform-wide, how old are our currently active API keys — do we have a meaningful population of long-lived, apparently never-rotated credentials, even though usage patterns look healthy?”

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 1

   Subject: Where I'd like your help first — account security snapshot

   Hi Noah,

   Good to have you working with me this week. I'm still building my mental map of the business, so let's start broad and sharpen later. Here's what's on my mind, roughly in priority order:

   1. How exposed are we on sign-in security today? I want to understand how merchant staff and shoppers are authenticating — are we seeing risky patterns like repeated failed logins, credential-stuffing-type behavior, or logins from unusual places? I need a feel for the baseline before I can say whether we have a problem.
   2. How strong is our multi-factor authentication posture? I'd like to know roughly how many of our merchants' staff (and ideally shoppers, if meaningful) actually have MFA set up and active, versus how many are relying on password-only access. This matters because it's one of the first things enterprise security reviewers ask about, and I need to know whether we can answer confidently or whether it's a gap we need to close.
   3. Who holds powerful access, and is it still appropriate? I'm interested in how permissions and roles are granted and whether there's good hygiene around it — e.g., are there dormant privileged accounts, or grants that nobody seems to be reviewing. This feeds directly into access-control questions in security audits.
   4. How healthy are our API keys and service accounts? These are often the weakest link — long-lived tokens that nobody rotates. I'd like a sense of how many are active, how old they are, and whether any look abandoned or over-privileged.
   5. Can we actually reconstruct "who did what, when" if asked? I want to understand how complete and reliable our audit trail is in practice — are sensitive actions consistently logged, and would we be able to produce a credible record if a merchant or regulator asked us to investigate an incident?

   What I need this for: I'm walking into enterprise deals where security questionnaires will ask us point-blank about authentication strength, access governance, credential hygiene, and auditability. I need an honest, evidence-based picture — good or bad — so I can brief the CEO and sales on what we can credibly claim today versus what needs fixing before the next review cycle.

   Given the slot budget, let's pick whichever 3 of these will give us the most defensible starting picture this week, and we can go deeper on the rest next week.

   Thanks, Henrik

   Show the whole note

2. Noah Fischer plans the night

   The analyst · week 1, night 4, request 2 of 3

   Night 3 brought the access-governance story to a clean, actionable finding: all 16 dormant Owner/Administrator accounts sit on CHURNED tenants only (9 tenants), with one as stale as 599 days since last login — meaning the real gap isn't "someone's not reviewing active-merchant access," it's that user accounts simply never get deactivated when a tenant churns (status still reads 'active'). That's a crisp, fixable finding for the memo. The API-key report came back almost too clean — zero active keys anywhere show as never-used or unused 90+ days, even though the same report shows oldest-active-key ages running into the thousands of days (one at 2,743 days, ~7.5 years, which predates Tidewick's 2023 warehouse cutover and even stretches toward the 2018 founding). That combination — heavily used, never rotated — is exactly the "long-lived token nobody rotates" risk Henrik named, so I want a platform-wide age/rotation distribution rather than relying on a handful of per-tenant data points. The audit-log report surfaced the most actionable gap of the week: 'delete' actions show only 27 events company-wide across three years, across just 16 of 120 tenants (13.3%) — implausibly low for an e-commerce platform this size, and worth testing directly against actual soft-deletions in the database rather than assuming it's a true absence of deletes.

   Tonight (final night) I'm spending all 3 slots closing out the two topics we hadn't quantified yet (API-key rotation hygiene, audit-trail completeness) plus checking whether existing compliance monitoring is a real control or just theoretical, since that's the credibility test for "can we reconstruct who did what" claims in the memo.

   Read the whole plan
   Active API key age distribution (rotation hygiene)

   Question

   Platform-wide, how old are our currently active API keys — do we have a meaningful population of long-lived, apparently never-rotated credentials, even though usage patterns look healthy?

   One row per

   age bucket of currently active api_keys

   Columns

   • age bucket (fixed labels: 'under 90 days', '90 days-1 year', '1-2 years', '2-4 years', '4+ years', based on 2026-01-05 minus created_at)
   • active key count in bucket
   • share of all active keys (%)
   • distinct tenants represented in bucket
   • oldest key age in days within bucket
   • count with last_used_at is null (never used) in bucket
   • count with last_used_at before 2025-10-05 (unused 90+ days) in bucket

3. Omar 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)

   with active_keys as (
     select
       id,
       tenant_id,
       created_at,
       last_used_at,
       date '2026-01-05' - created_at::date as age_days
     from api_keys
     where status = 'active'
   ),
   bucketed as (
     select
       *,
       case
         when age_days < 90 then '1 - under 90 days'
         when age_days < 365 then '2 - 90 days-1 year'
         when age_days < 730 then '3 - 1-2 years'
         when age_days < 1460 then '4 - 2-4 years'
         else '5 - 4+ years'
       end as bucket_sort
     from active_keys
   ),
   totals as (
     select count(*) as total_active from bucketed
   )
   select
     case b.bucket_sort
       when '1 - under 90 days' then 'under 90 days'
       when '2 - 90 days-1 year' then '90 days-1 year'
       when '3 - 1-2 years' then '1-2 years'
       when '4 - 2-4 years' then '2-4 years'
       when '5 - 4+ years' then '4+ years'
     end as age_bucket,
     count(*) as active_key_count,
     round(100.0 * count(*) / t.total_active, 1) as share_pct,
     count(distinct b.tenant_id) as distinct_tenants,
     max(b.age_days) as oldest_key_age_days,
     count(*) filter (where b.last_used_at is null) as never_used_count,
     count(*) filter (where b.last_used_at is not null and b.last_used_at < timestamp '2025-10-05') as unused_90d_plus_count
   from bucketed b
   cross join totals t
   group by b.bucket_sort, t.total_active
   order by b.bucket_sort

   “Query compiles and returns the expected 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 buckets currently-active API keys into the five fixed age ranges (youngest to oldest), with count, share of total, distinct tenants, oldest age in the bucket, never-used count, and count unused 90+ days — computed against the fixed reference date 2026-01-05 as requested.”

4. The report comes back overnight

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

   age_bucket     | active_key_count | share_pct | distinct_tenants | oldest_key_age_days | never_used_count | unused_90d_plus_count
   ---------------+------------------+-----------+------------------+---------------------+------------------+----------------------
   under 90 days  |                1 |       0.9 |                1 |                  68 |                0 |                     0
   90 days-1 year |               11 |       9.7 |               11 |                 327 |                0 |                     0
   1-2 years      |               14 |      12.4 |                9 |                 699 |                0 |                     0
   2-4 years      |               11 |       9.7 |               10 |                1346 |                0 |                     0
   4+ years       |               76 |      67.3 |               32 |                2743 |                0 |                     0
   (5 rows)

5. What came of it

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

   Noah Fischer's memo to Henrik Aalto at the end of the week:

   • 4. API keys: well-used, but poorly rotated. No tenant shows an active key that's unused or abandoned (API key hygiene). But age tells a different story: 67.3% of active keys (76 of ~113) are 4+ years old, including one 2,743 days old (Active API key age distribution). There's no rotation field or process for api_keys in the schema — usage looks healthy, rotation hygiene does not.

   Later queries that build on this report:

   • q319Detail list of the oldest active API keys (4+ years), by tenant
   • q321Is admin_api scope concentrated in old keys, or standard across all ages?

   It builds on:

   • q107API key hygiene by merchant

6. It becomes benchmark query q110

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

   • spellingcast
   • 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
   The benchmark text, against the submitted SQL

   with active_keys as (  select    id,    tenant_id,    created_at,    last_used_at,    date '2026-01-05' - created_at::date as age_days    date '2026-01-05' - CAST(created_at AS date) as age_days  from api_keys  where status = 'active'),bucketed as (  select    *,    case      when age_days < 90 then '1 - under 90 days'      when age_days < 365 then '2 - 90 days-1 year'      when age_days < 730 then '3 - 1-2 years'      when age_days < 1460 then '4 - 2-4 years'      else '5 - 4+ years'    end as bucket_sort  from active_keys),totals as (  select count(*) as total_active from bucketed)select  case b.bucket_sort    when '1 - under 90 days' then 'under 90 days'    when '2 - 90 days-1 year' then '90 days-1 year'    when '3 - 1-2 years' then '1-2 years'    when '4 - 2-4 years' then '2-4 years'    when '5 - 4+ years' then '4+ years'  end as age_bucket,  count(*) as active_key_count,  round(100.0 * count(*) / t.total_active, 1) as share_pct,  100.0 * count(*) / t.total_active as share_pct,  count(distinct b.tenant_id) as distinct_tenants,  max(b.age_days) as oldest_key_age_days,  count(*) filter (where b.last_used_at is null) as never_used_count,  count(*) filter (where b.last_used_at is not null and b.last_used_at < timestamp '2025-10-05') as unused_90d_plus_countfrom bucketed bcross join totals tgroup by b.bucket_sort, t.total_activeorder by b.bucket_sortorder by b.bucket_sort 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

   with active_keys as (
     select
       id,
       tenant_id,
       created_at,
       last_used_at,
       date '2026-01-05' - CAST(created_at AS date) as age_days
     from api_keys
     where status = 'active'
   ),
   bucketed as (
     select
       *,
       case
         when age_days < 90 then '1 - under 90 days'
         when age_days < 365 then '2 - 90 days-1 year'
         when age_days < 730 then '3 - 1-2 years'
         when age_days < 1460 then '4 - 2-4 years'
         else '5 - 4+ years'
       end as bucket_sort
     from active_keys
   ),
   totals as (
     select count(*) as total_active from bucketed
   )
   select
     case b.bucket_sort
       when '1 - under 90 days' then 'under 90 days'
       when '2 - 90 days-1 year' then '90 days-1 year'
       when '3 - 1-2 years' then '1-2 years'
       when '4 - 2-4 years' then '2-4 years'
       when '5 - 4+ years' then '4+ years'
     end as age_bucket,
     count(*) as active_key_count,
     100.0 * count(*) / t.total_active as share_pct,
     count(distinct b.tenant_id) as distinct_tenants,
     max(b.age_days) as oldest_key_age_days,
     count(*) filter (where b.last_used_at is null) as never_used_count,
     count(*) filter (where b.last_used_at is not null and b.last_used_at < timestamp '2025-10-05') as unused_90d_plus_count
   from bucketed b
   cross join totals t
   group by b.bucket_sort, t.total_active
   order by b.bucket_sort NULLS LAST

   Scale factor 1000, c8a.24xlarge, after the last load. q110 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

   Omar's whole session, 7 turns

   • The request, as Omar received it

     Report request from Noah Fischer (analyst for the Chief Information Security Officer):
     
     Title: Active API key age distribution (rotation hygiene)
     Question: Platform-wide, how old are our currently active API keys — do we have a meaningful population of long-lived, apparently never-rotated credentials, even though usage patterns look healthy?
     Grain: one row per age bucket of currently active api_keys
     Columns:
     - age bucket (fixed labels: 'under 90 days', '90 days-1 year', '1-2 years', '2-4 years', '4+ years', based on 2026-01-05 minus created_at)
     - active key count in bucket
     - share of all active keys (%)
     - distinct tenants represented in bucket
     - oldest key age in days within bucket
     - count with last_used_at is null (never used) in bucket
     - count with last_used_at before 2025-10-05 (unused 90+ days) in bucket
     Details: Source: api_keys where status = 'active'. Compute age_days = 2026-01-05 minus created_at (fixed reference date per house standards, no CURRENT_DATE). Bucket into the 5 fixed ranges above. Share of all active keys = bucket count / total active keys across all buckets, rounded to 1 decimal percent. Order rows by bucket from youngest to oldest in the fixed sequence listed (not alphabetical) so the trend from new to old is readable top to bottom.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Omar calls dry_run

     with active_keys as (
       select
         id,
         tenant_id,
         created_at,
         last_used_at,
         date '2026-01-05' - created_at::date as age_days
       from api_keys
       where status = 'active'
     ),
     bucketed as (
       select
         *,
         case
           when age_days < 90 then '1 - under 90 days'
           when age_days < 365 then '2 - 90 days-1 year'
           when age_days < 730 then '3 - 1-2 years'
           when age_days < 1460 then '4 - 2-4 years'
           else '5 - 4+ years'
         end as bucket_sort
       from active_keys
     ),
     totals as (
       select count(*) as total_active from bucketed
     )
     select
       case b.bucket_sort
         when '1 - under 90 days' then 'under 90 days'
         when '2 - 90 days-1 year' then '90 days-1 year'
         when '3 - 1-2 years' then '1-2 years'
         when '4 - 2-4 years' then '2-4 years'
         when '5 - 4+ years' then '4+ years'
       end as age_bucket,
       count(*) as active_key_count,
       round(100.0 * count(*) / t.total_active, 1) as share_pct,
       count(distinct b.tenant_id) as distinct_tenants,
       max(b.age_days) as oldest_key_age_days,
       count(*) filter (where b.last_used_at is null) as never_used_count,
       count(*) filter (where b.last_used_at is not null and b.last_used_at < timestamp '2025-10-05') as unused_90d_plus_count
     from bucketed b
     cross join totals t
     group by b.bucket_sort, t.total_active
     order by b.bucket_sort

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: age_bucket, active_key_count, share_pct, distinct_tenants, oldest_key_age_days, never_used_count, unused_90d_plus_count

   • OmarQuery compiles and returns the expected columns. Submitting.
   • Omar calls submit

     with active_keys as (
       select
         id,
         tenant_id,
         created_at,
         last_used_at,
         date '2026-01-05' - created_at::date as age_days
       from api_keys
       where status = 'active'
     ),
     bucketed as (
       select
         *,
         case
           when age_days < 90 then '1 - under 90 days'
           when age_days < 365 then '2 - 90 days-1 year'
           when age_days < 730 then '3 - 1-2 years'
           when age_days < 1460 then '4 - 2-4 years'
           else '5 - 4+ years'
         end as bucket_sort
       from active_keys
     ),
     totals as (
       select count(*) as total_active from bucketed
     )
     select
       case b.bucket_sort
         when '1 - under 90 days' then 'under 90 days'
         when '2 - 90 days-1 year' then '90 days-1 year'
         when '3 - 1-2 years' then '1-2 years'
         when '4 - 2-4 years' then '2-4 years'
         when '5 - 4+ years' then '4+ years'
       end as age_bucket,
       count(*) as active_key_count,
       round(100.0 * count(*) / t.total_active, 1) as share_pct,
       count(distinct b.tenant_id) as distinct_tenants,
       max(b.age_days) as oldest_key_age_days,
       count(*) filter (where b.last_used_at is null) as never_used_count,
       count(*) filter (where b.last_used_at is not null and b.last_used_at < timestamp '2025-10-05') as unused_90d_plus_count
     from bucketed b
     cross join totals t
     group by b.bucket_sort, t.total_active
     order by b.bucket_sort

   • The warehouse answers

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

   • OmarSubmitted. The report buckets currently-active API keys into the five fixed age ranges (youngest to oldest), with count, share of total, distinct tenants, oldest age in the bucket, never-used count, and count unused 90+ days — computed against the fixed reference date 2026-01-05 as requested.

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

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