Queries · q316 · Chief Information Security Officer · week 2, night 2

Validity of never-revoked sessions on churned tenants

“The 9 churned tenants show 46-300 sessions per tenant that were never explicitly revoked — but are those sessions actually still usable today (expiry in the future), or did they just naturally expire without anyone bothering to revoke them?”

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 2, request 3 of 3

   Night 1 reshapes the picture a lot. The 30-table census shows that products (615 rows, 91 tenants, 0% audit coverage) and users (39 rows, 69.2% coverage) are the ONLY two tables where a soft-delete has ever happened in three years — every other one of the 28 tables shows zero soft-deleted rows ever, so I can't test coverage there at all; this isn't evidence those tables are "fine," just that the question doesn't apply yet. That reframes #1 from "is it everywhere" to "it's concentrated in products, and separately, orders look terrible" — order cancellations matched an audit_log row only 0.2% of the time (3 of 1,712). But before I report that as a hard gap, I need to check the table built specifically for this — order_status_history — since generic audit_log missing it doesn't mean Tidewick has no record at all; that's tonight's top priority, since it could flip the finding. I also need to pin down the invoice-void result, which came back as "0 events" only because the engineer excluded void invoices with a null voided_at timestamp — I don't yet know if that's because no invoice has ever been voided, or because voided invoices exist but aren't timestamped (itself a data-quality finding). On #3, report 3 showed 100% of churned tenants' users are still "active" status and sessions were never explicitly revoked (46-300 per tenant) — but "never revoked" isn't the same as "still valid"; I need to check whether those sessions' expiry dates have actually lapsed before I can size the real exposure.

   Read the whole plan
   Validity of never-revoked sessions on churned tenants

   Question

   The 9 churned tenants show 46-300 sessions per tenant that were never explicitly revoked — but are those sessions actually still usable today (expiry in the future), or did they just naturally expire without anyone bothering to revoke them?

   One row per

   churned tenant

   Columns

   • tenant name
   • sessions never revoked (revoked_at is null)
   • of those, still within expiry as of 2026-01-05 (expires_at > 2026-01-05, i.e. genuinely usable today)
   • of those, already naturally expired (expires_at <= 2026-01-05)
   • latest expires_at among never-revoked sessions
   • latest last_seen_at among never-revoked sessions (most recent real use)

3. Ines 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
     t.name as tenant_name,
     count(*) as never_revoked_sessions,
     count(*) filter (where s.expires_at > timestamp '2026-01-05 00:00:00') as still_within_expiry,
     count(*) filter (where s.expires_at <= timestamp '2026-01-05 00:00:00') as already_expired,
     max(s.expires_at) as latest_expires_at,
     max(s.last_seen_at) as latest_last_seen_at
   from sessions s
   join tenants t on t.id = s.tenant_id
   where t.status = 'churned'
     and s.revoked_at is null
   group by t.name
   order by still_within_expiry desc, tenant_name asc

   “Query compiles cleanly with the right grain and columns.”

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

   “Submitted. The report gives, per churned tenant: total never-revoked sessions, how many are still within their expiry window (the live risk) versus already expired on their own, plus the latest expires_at and last_seen_at among them — directly answering whether this is an exploitable risk or just stale bookkeeping.”

4. The report comes back overnight

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

   tenant_name        | never_revoked_sessions | still_within_expiry | already_expired | latest_expires_at          | latest_last_seen_at       
   -------------------+------------------------+---------------------+-----------------+----------------------------+---------------------------
   Aurum & Ash        |                     73 |                   0 |              73 | 2025-01-10 07:20:13.755243 | 2024-12-27 15:40:13.755243
   Dewdrop Studio     |                    171 |                   0 |             171 | 2025-10-23 09:44:11.78363  | 2025-10-09 17:45:11.78363 
   Halden Boots       |                    120 |                   0 |             120 | 2025-10-17 08:05:13.116482 | 2025-10-03 09:55:13.116482
   Klara Beauty       |                    110 |                   0 |             110 | 2025-05-05 06:00:35.543369 | 2025-04-21 07:19:35.543369
   Marginalia         |                     80 |                   0 |              80 | 2025-06-27 06:55:24.418769 | 2025-06-13 11:25:24.418769
   Northwire Audio    |                    191 |                   0 |             191 | 2024-10-18 09:15:34.531675 | 2024-10-04 16:38:43.724015
   Paddock & Pine     |                     46 |                   0 |              46 | 2024-06-13 05:55:25.699322 | 2024-05-30 06:29:25.699322
   Petal & Pestle     |                    223 |                   0 |             223 | 2024-10-25 06:04:30.128982 | 2024-10-11 14:46:30.128982
   Saltmarsh Knitwear |                    300 |                   0 |             300 | 2024-12-06 07:15:36.203341 | 2024-11-22 13:57:36.203341
   (9 rows)

5. What came of it

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

   It builds on:

   • q313Access still open on churned tenants, by surface

6. It becomes benchmark query q316

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

   • 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  t.name as tenant_name,  count(*) as never_revoked_sessions,  count(*) filter (where s.expires_at > timestamp '2026-01-05 00:00:00') as still_within_expiry,  count(*) filter (where s.expires_at <= timestamp '2026-01-05 00:00:00') as already_expired,  max(s.expires_at) as latest_expires_at,  max(s.last_seen_at) as latest_last_seen_atfrom sessions sjoin tenants t on t.id = s.tenant_idwhere t.status = 'churned'  and s.revoked_at is nullgroup by t.nameorder by still_within_expiry desc, tenant_name ascorder by still_within_expiry desc NULLS FIRST, tenant_name 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
     t.name as tenant_name,
     count(*) as never_revoked_sessions,
     count(*) filter (where s.expires_at > timestamp '2026-01-05 00:00:00') as still_within_expiry,
     count(*) filter (where s.expires_at <= timestamp '2026-01-05 00:00:00') as already_expired,
     max(s.expires_at) as latest_expires_at,
     max(s.last_seen_at) as latest_last_seen_at
   from sessions s
   join tenants t on t.id = s.tenant_id
   where t.status = 'churned'
     and s.revoked_at is null
   group by t.name
   order by still_within_expiry desc NULLS FIRST, tenant_name asc NULLS LAST

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

   Ines's whole session, 7 turns

   • The request, as Ines received it

     Report request from Noah Fischer (analyst for the Chief Information Security Officer):
     
     Title: Validity of never-revoked sessions on churned tenants
     Question: The 9 churned tenants show 46-300 sessions per tenant that were never explicitly revoked — but are those sessions actually still usable today (expiry in the future), or did they just naturally expire without anyone bothering to revoke them?
     Grain: one row per churned tenant
     Columns:
     - tenant name
     - sessions never revoked (revoked_at is null)
     - of those, still within expiry as of 2026-01-05 (expires_at > 2026-01-05, i.e. genuinely usable today)
     - of those, already naturally expired (expires_at <= 2026-01-05)
     - latest expires_at among never-revoked sessions
     - latest last_seen_at among never-revoked sessions (most recent real use)
     Details: Source: sessions joined to tenants where tenants.status = 'churned', filtered to revoked_at is null. Split by expires_at compared to the fixed reference date 2026-01-05 (no CURRENT_DATE per house standards). This tells us whether the 'never revoked' finding from last night is a live, exploitable risk (sessions still valid today) or a historical bookkeeping gap (sessions long since expired on their own). Order by 'still within expiry' descending, tenant name as tie-break. Expect 9 rows (one per churned tenant from the prior report).
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Ines calls dry_run

     select
       t.name as tenant_name,
       count(*) as never_revoked_sessions,
       count(*) filter (where s.expires_at > timestamp '2026-01-05 00:00:00') as still_within_expiry,
       count(*) filter (where s.expires_at <= timestamp '2026-01-05 00:00:00') as already_expired,
       max(s.expires_at) as latest_expires_at,
       max(s.last_seen_at) as latest_last_seen_at
     from sessions s
     join tenants t on t.id = s.tenant_id
     where t.status = 'churned'
       and s.revoked_at is null
     group by t.name
     order by still_within_expiry desc, tenant_name asc

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: tenant_name, never_revoked_sessions, still_within_expiry, already_expired, latest_expires_at, latest_last_seen_at

   • InesQuery compiles cleanly with the right grain and columns.
   • Ines calls submit

     select
       t.name as tenant_name,
       count(*) as never_revoked_sessions,
       count(*) filter (where s.expires_at > timestamp '2026-01-05 00:00:00') as still_within_expiry,
       count(*) filter (where s.expires_at <= timestamp '2026-01-05 00:00:00') as already_expired,
       max(s.expires_at) as latest_expires_at,
       max(s.last_seen_at) as latest_last_seen_at
     from sessions s
     join tenants t on t.id = s.tenant_id
     where t.status = 'churned'
       and s.revoked_at is null
     group by t.name
     order by still_within_expiry desc, tenant_name asc

   • The warehouse answers

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

   • InesSubmitted. The report gives, per churned tenant: total never-revoked sessions, how many are still within their expiry window (the live risk) versus already expired on their own, plus the latest `expires_at` and `last_seen_at` among them — directly answering whether this is an exploitable risk or just stale bookkeeping.

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

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