Queries · q318 · Chief Information Security Officer · week 2, night 3

Shopper authentication method mix: OAuth-linked vs password-only

“Since shopper accounts have 0% MFA, what actually stands in for a second factor today — are most shoppers authenticating through an OAuth provider (Google/Apple/etc., which carries its own security), or are they overwhelmingly raw password-only accounts with no backup at all?”

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

   Read the whole plan
   Shopper authentication method mix: OAuth-linked vs password-only

   Question

   Since shopper accounts have 0% MFA, what actually stands in for a second factor today — are most shoppers authenticating through an OAuth provider (Google/Apple/etc., which carries its own security), or are they overwhelmingly raw password-only accounts with no backup at all?

   One row per

   authentication method category among shopper (is_staff = false) users

   Columns

   • method category ('OAuth-linked' = has ≥1 oauth_accounts row, 'password-only' = no oauth_accounts row)
   • shopper user count
   • share of all shoppers (%)
   • of those, confirmed MFA count (expected near-zero, included to double-check for any overlap)
   • of those, users with at least one login_attempt in the last 90 days (2025-10-05 to 2025-12-31), i.e. recently active

3. Grace 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 shoppers as (
       select id, tenant_id, email
       from users
       where is_staff = false
         and deleted_at is null
   ),
   oauth_linked_users as (
       select distinct user_id
       from oauth_accounts
   ),
   confirmed_mfa_users as (
       select distinct user_id
       from mfa_devices
       where confirmed_at is not null
   ),
   recent_login_users as (
       select distinct s.id as user_id
       from shoppers s
       join login_attempts la
         on la.tenant_id = s.tenant_id
        and (la.user_id = s.id or la.email = s.email)
       where la.occurred_at >= timestamp '2025-10-05'
         and la.occurred_at <  timestamp '2026-01-01'
   ),
   classified_shoppers as (
       select
           s.id,
           case when o.user_id is not null then 'OAuth-linked' else 'password-only' end as method_category
       from shoppers s
       left join oauth_linked_users o on o.user_id = s.id
   ),
   total_shoppers as (
       select count(*) as total_count from shoppers
   )
   select
       cs.method_category,
       count(*) as shopper_user_count,
       round(100.0 * count(*) / t.total_count, 1) as share_of_shoppers_pct,
       count(mfa.user_id) as confirmed_mfa_count,
       count(rl.user_id) as recently_active_count
   from classified_shoppers cs
   cross join total_shoppers t
   left join confirmed_mfa_users mfa on mfa.user_id = cs.id
   left join recent_login_users rl on rl.user_id = cs.id
   group by cs.method_category, t.total_count
   order by shopper_user_count desc, cs.method_category

   “Looks correct 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 classifies all non-staff, non-deleted users into OAuth-linked vs. password-only (based on presence of an oauth_accounts row), with confirmed-MFA counts and 90-day recent-activity counts for each group, ordered by shopper count descending — exactly the two-row comparison Noah asked for.”

4. The report comes back overnight

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

   method_category | shopper_user_count | share_of_shoppers_pct | confirmed_mfa_count | recently_active_count
   ----------------+--------------------+-----------------------+---------------------+----------------------
   password-only   |               9910 |                  88.3 |                   0 |                  4743
   OAuth-linked    |               1309 |                  11.7 |                   0 |                   614
   (2 rows)

5. What came of it

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

   It builds on:

   • q103Platform-wide MFA adoption trend by month, staff vs. shoppers

6. It becomes benchmark query q318

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • made explicita selected t.col gets AS col, its standard name made explicit (2×)
   • 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

   with shoppers as (    select id, tenant_id, email    from users    where is_staff = false      and deleted_at is null),oauth_linked_users as (    select distinct user_id    from oauth_accounts),confirmed_mfa_users as (    select distinct user_id    from mfa_devices    where confirmed_at is not null),recent_login_users as (    select distinct s.id as user_id    from shoppers s    join login_attempts la      on la.tenant_id = s.tenant_id     and (la.user_id = s.id or la.email = s.email)    where la.occurred_at >= timestamp '2025-10-05'      and la.occurred_at <  timestamp '2026-01-01'),classified_shoppers as (    select        s.id,        s.id AS id,        case when o.user_id is not null then 'OAuth-linked' else 'password-only' end as method_category    from shoppers s    left join oauth_linked_users o on o.user_id = s.id),total_shoppers as (    select count(*) as total_count from shoppers)select    cs.method_category,    cs.method_category AS method_category,    count(*) as shopper_user_count,    round(100.0 * count(*) / t.total_count, 1) as share_of_shoppers_pct,    100.0 * count(*) / t.total_count as share_of_shoppers_pct,    count(mfa.user_id) as confirmed_mfa_count,    count(rl.user_id) as recently_active_countfrom classified_shoppers cscross join total_shoppers tleft join confirmed_mfa_users mfa on mfa.user_id = cs.idleft join recent_login_users rl on rl.user_id = cs.idgroup by cs.method_category, t.total_countorder by shopper_user_count desc, cs.method_categoryorder by shopper_user_count desc NULLS FIRST, cs.method_category 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 shoppers as (
       select id, tenant_id, email
       from users
       where is_staff = false
         and deleted_at is null
   ),
   oauth_linked_users as (
       select distinct user_id
       from oauth_accounts
   ),
   confirmed_mfa_users as (
       select distinct user_id
       from mfa_devices
       where confirmed_at is not null
   ),
   recent_login_users as (
       select distinct s.id as user_id
       from shoppers s
       join login_attempts la
         on la.tenant_id = s.tenant_id
        and (la.user_id = s.id or la.email = s.email)
       where la.occurred_at >= timestamp '2025-10-05'
         and la.occurred_at <  timestamp '2026-01-01'
   ),
   classified_shoppers as (
       select
           s.id AS id,
           case when o.user_id is not null then 'OAuth-linked' else 'password-only' end as method_category
       from shoppers s
       left join oauth_linked_users o on o.user_id = s.id
   ),
   total_shoppers as (
       select count(*) as total_count from shoppers
   )
   select
       cs.method_category AS method_category,
       count(*) as shopper_user_count,
       100.0 * count(*) / t.total_count as share_of_shoppers_pct,
       count(mfa.user_id) as confirmed_mfa_count,
       count(rl.user_id) as recently_active_count
   from classified_shoppers cs
   cross join total_shoppers t
   left join confirmed_mfa_users mfa on mfa.user_id = cs.id
   left join recent_login_users rl on rl.user_id = cs.id
   group by cs.method_category, t.total_count
   order by shopper_user_count desc NULLS FIRST, cs.method_category NULLS LAST

   • Hyperno result within 30 s
   • DuckDBno result within 30 s
   • GizmoSQLno result within 30 s
   • DataFusionno result within 30 s

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

   Grace's whole session, 7 turns

   • The request, as Grace received it

     Report request from Noah Fischer (analyst for the Chief Information Security Officer):
     
     Title: Shopper authentication method mix: OAuth-linked vs password-only
     Question: Since shopper accounts have 0% MFA, what actually stands in for a second factor today — are most shoppers authenticating through an OAuth provider (Google/Apple/etc., which carries its own security), or are they overwhelmingly raw password-only accounts with no backup at all?
     Grain: one row per authentication method category among shopper (is_staff = false) users
     Columns:
     - method category ('OAuth-linked' = has ≥1 oauth_accounts row, 'password-only' = no oauth_accounts row)
     - shopper user count
     - share of all shoppers (%)
     - of those, confirmed MFA count (expected near-zero, included to double-check for any overlap)
     - of those, users with at least one login_attempt in the last 90 days (2025-10-05 to 2025-12-31), i.e. recently active
     Details: Source: users where is_staff = false and deleted_at is null, left-joined to oauth_accounts (user_id) to classify into the two categories, and to mfa_devices (confirmed_at is not null) and login_attempts (occurred_at in the window, matched by email or user_id) for the other two measures. Share of all shoppers = category count / total shoppers, rounded to 1 decimal percent. Only 2 rows expected (OAuth-linked, password-only); order by shopper user count descending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Grace calls dry_run

     with shoppers as (
         select id, tenant_id, email
         from users
         where is_staff = false
           and deleted_at is null
     ),
     oauth_linked_users as (
         select distinct user_id
         from oauth_accounts
     ),
     confirmed_mfa_users as (
         select distinct user_id
         from mfa_devices
         where confirmed_at is not null
     ),
     recent_login_users as (
         select distinct s.id as user_id
         from shoppers s
         join login_attempts la
           on la.tenant_id = s.tenant_id
          and (la.user_id = s.id or la.email = s.email)
         where la.occurred_at >= timestamp '2025-10-05'
           and la.occurred_at <  timestamp '2026-01-01'
     ),
     classified_shoppers as (
         select
             s.id,
             case when o.user_id is not null then 'OAuth-linked' else 'password-only' end as method_category
         from shoppers s
         left join oauth_linked_users o on o.user_id = s.id
     ),
     total_shoppers as (
         select count(*) as total_count from shoppers
     )
     select
         cs.method_category,
         count(*) as shopper_user_count,
         round(100.0 * count(*) / t.total_count, 1) as share_of_shoppers_pct,
         count(mfa.user_id) as confirmed_mfa_count,
         count(rl.user_id) as recently_active_count
     from classified_shoppers cs
     cross join total_shoppers t
     left join confirmed_mfa_users mfa on mfa.user_id = cs.id
     left join recent_login_users rl on rl.user_id = cs.id
     group by cs.method_category, t.total_count
     order by shopper_user_count desc, cs.method_category

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: method_category, shopper_user_count, share_of_shoppers_pct, confirmed_mfa_count, recently_active_count

   • GraceLooks correct and matches the requested grain and columns. Submitting.
   • Grace calls submit

     with shoppers as (
         select id, tenant_id, email
         from users
         where is_staff = false
           and deleted_at is null
     ),
     oauth_linked_users as (
         select distinct user_id
         from oauth_accounts
     ),
     confirmed_mfa_users as (
         select distinct user_id
         from mfa_devices
         where confirmed_at is not null
     ),
     recent_login_users as (
         select distinct s.id as user_id
         from shoppers s
         join login_attempts la
           on la.tenant_id = s.tenant_id
          and (la.user_id = s.id or la.email = s.email)
         where la.occurred_at >= timestamp '2025-10-05'
           and la.occurred_at <  timestamp '2026-01-01'
     ),
     classified_shoppers as (
         select
             s.id,
             case when o.user_id is not null then 'OAuth-linked' else 'password-only' end as method_category
         from shoppers s
         left join oauth_linked_users o on o.user_id = s.id
     ),
     total_shoppers as (
         select count(*) as total_count from shoppers
     )
     select
         cs.method_category,
         count(*) as shopper_user_count,
         round(100.0 * count(*) / t.total_count, 1) as share_of_shoppers_pct,
         count(mfa.user_id) as confirmed_mfa_count,
         count(rl.user_id) as recently_active_count
     from classified_shoppers cs
     cross join total_shoppers t
     left join confirmed_mfa_users mfa on mfa.user_id = cs.id
     left join recent_login_users rl on rl.user_id = cs.id
     group by cs.method_category, t.total_count
     order by shopper_user_count desc, cs.method_category

   • The warehouse answers

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

   • GraceSubmitted. The report classifies all non-staff, non-deleted users into OAuth-linked vs. password-only (based on presence of an `oauth_accounts` row), with confirmed-MFA counts and 90-day recent-activity counts for each group, ordered by shopper count descending — exactly the two-row comparison Noah asked for.

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

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