with redirected(sid, redirected_at, viewer) as (values
 ('session_xxxxxxxx', timestamptz '2026-08-14 13:30:42+00', 'user_xxxxxxxx')
 -- , ('session_yyyyyyyy', timestamptz '...', 'user_yyyyyyyy')
),
rows_for as (
  select r.sid,
         r.redirected_at,
         count(l.id) as live_rows,
         min(l.created_at) as first_live_row_at
  from redirected r
  left join ai_chat_logs l
    on l.session_id = r.sid
   and l.clerk_id = r.viewer
   and coalesce(l.deleted, false) = false
  group by r.sid, r.redirected_at
)
select sid,
       live_rows,
       round(extract(epoch from (first_live_row_at - redirected_at))::numeric, 1)
         as first_row_delta_s,
       case
         when live_rows = 0
           then 'CORRECT: truly dead'
         when first_live_row_at between redirected_at - interval '5 s'
                                    and redirected_at + interval '30 s'
           then 'WRONG: bounced a LIVE turn'
         else 'REVIEW: rows predate redirect'
       end as verdict
from rows_for
order by case
           when live_rows = 0 then 3
           when first_live_row_at between redirected_at - interval '5 s'
                                      and redirected_at + interval '30 s'
             then 1
           else 2
         end,
       sid;

select session_id, clerk_id, count(*) as rows
from ai_chat_logs where session_id = 'session_xxxxxxxx'
group by session_id, clerk_id;

SELECT toDate(timestamp) AS d,
       event,
       properties.recheck_outcome AS outcome,
       count() AS n,
       uniq(distinct_id) AS users,
       -- Under roughly 5000ms means a turn was dispatched from this tab
       -- moments before the redirect, the signature of a live chat.
       median(toFloat64OrNull(properties.ms_since_last_send_this_tab))
         AS p50_ms_since_send,
       countIf(properties.was_streaming_this_session = true) AS while_streaming,
       median(toFloat64OrNull(properties.recheck_attempts)) AS p50_recheck_attempts,
       -- distinct_id is needed for the DB audit's ownership scoping
       arrayStringConcat(arraySlice(groupArray(
         concat(toString(properties.session_id), '|', toString(distinct_id))
       ), 1, 20), ', ') AS sessions_and_viewers
FROM events
WHERE event IN (
        'chat_session_not_found_fresh_fallback',    -- redirected, confirmed empty
        'chat_session_recheck_failed_fallback',     -- redirected, could not confirm
        'chat_session_not_found_recheck_recovered', -- race caught, NO redirect
        'chat_session_load_skipped_in_flight'       -- load skipped, NO redirect
      )
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY d, event, outcome
ORDER BY d DESC, n DESC