Live-pipeline leak — empirical validation

-- Leak validation: is the live-pipeline gap still active 20 days post-audit?
-- Mirrors identify_users.sql entitlement logic (omits students_pg).
-- See: TA-4634 (Done 2026-05-06), basirah-lab/jobs/orphan_audio_audit/identify_users.sql

WITH
    now64() AS run_now,
    toDateTime64('2026-05-05 04:19:00', 6, 'UTC') AS audit_cutoff,

    entitled_users AS (
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND status IN ('ACTIVE', 'GRACE', 'CANCELLED')
        UNION DISTINCT
        SELECT id AS user_id FROM tarteeldb.public_profiles_user FINAL
        WHERE _peerdb_is_deleted = 0 AND subscription_type = 'PREMIUM'
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_profiles_freetrial FINAL
        WHERE _peerdb_is_deleted = 0 AND started_at <= run_now AND ended_at > run_now
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_profiles_familymember FINAL
        WHERE _peerdb_is_deleted = 0
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_alim_beneficiary FINAL
        WHERE _peerdb_is_deleted = 0 AND access_revoked >= run_now
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND start_date <= run_now - INTERVAL 2 YEAR
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND status = 'EXPIRED' AND expired_at > run_now - INTERVAL 7 DAY
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscriptionevent FINAL
        WHERE _peerdb_is_deleted = 0
          AND type IN ('EXPIRED', 'CANCELLED')
          AND created_at > run_now - INTERVAL 7 DAY
    )

SELECT
    toDate(created_at) AS day,
    count() AS new_has_audio_sessions,
    countIf(user_id NOT IN (SELECT user_id FROM entitled_users)) AS likely_leaked,
    round(
        countIf(user_id NOT IN (SELECT user_id FROM entitled_users))
        / nullIf(count(), 0) * 100,
    1) AS leak_pct
FROM tarteeldb.public_profiles_quransession FINAL
WHERE _peerdb_is_deleted = 0
  AND created_at >= audit_cutoff
  AND has_audio = TRUE
  AND session_type = 'RECITE'
  AND is_deleted = FALSE
GROUP BY day
ORDER BY day DESC
FORMAT TabSeparatedWithNames

-- Composition of the leak: per "lapse path", how many leaked USERS have that history?
-- For each leaked user (currently non-entitled, has_audio=TRUE post-cutoff session),
-- classify what their last-known entitlement signal was.

WITH
    now64() AS run_now,
    toDateTime64('2026-05-05 04:19:00', 6, 'UTC') AS audit_cutoff,

    entitled_users AS (
        -- (same 8-branch UNION as Q1 — see above)
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND status IN ('ACTIVE', 'GRACE', 'CANCELLED')
        UNION DISTINCT
        SELECT id AS user_id FROM tarteeldb.public_profiles_user FINAL
        WHERE _peerdb_is_deleted = 0 AND subscription_type = 'PREMIUM'
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_profiles_freetrial FINAL
        WHERE _peerdb_is_deleted = 0 AND started_at <= run_now AND ended_at > run_now
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_profiles_familymember FINAL
        WHERE _peerdb_is_deleted = 0
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_alim_beneficiary FINAL
        WHERE _peerdb_is_deleted = 0 AND access_revoked >= run_now
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND start_date <= run_now - INTERVAL 2 YEAR
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND status = 'EXPIRED' AND expired_at > run_now - INTERVAL 7 DAY
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscriptionevent FINAL
        WHERE _peerdb_is_deleted = 0
          AND type IN ('EXPIRED', 'CANCELLED')
          AND created_at > run_now - INTERVAL 7 DAY
    ),

    leaked_users AS (
        SELECT DISTINCT user_id
        FROM tarteeldb.public_profiles_quransession FINAL
        WHERE _peerdb_is_deleted = 0
          AND created_at >= audit_cutoff
          AND has_audio = TRUE
          AND session_type = 'RECITE'
          AND is_deleted = FALSE
          AND user_id NOT IN (SELECT user_id FROM entitled_users)
    ),

    -- Per leaked user: do they have a record in each source?
    sub_history AS (
        SELECT DISTINCT user_id, max(expired_at) AS last_expired
        FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0
          AND user_id IN (SELECT user_id FROM leaked_users)
        GROUP BY user_id
    ),
    trial_history AS (
        SELECT DISTINCT user_id, max(ended_at) AS last_ended
        FROM tarteeldb.public_profiles_freetrial FINAL
        WHERE _peerdb_is_deleted = 0
          AND user_id IN (SELECT user_id FROM leaked_users)
        GROUP BY user_id
    ),
    family_history AS (
        SELECT DISTINCT user_id
        FROM tarteeldb.public_profiles_familymember FINAL
        WHERE user_id IN (SELECT user_id FROM leaked_users)
    ),
    alim_history AS (
        SELECT DISTINCT user_id
        FROM tarteeldb.public_alim_beneficiary FINAL
        WHERE user_id IN (SELECT user_id FROM leaked_users)
    )

SELECT
    countDistinct(l.user_id) AS total_leaked_users,
    countDistinctIf(l.user_id, sh.user_id != 0) AS ever_had_subscription,
    countDistinctIf(l.user_id, sh.last_expired > run_now - INTERVAL 30 DAY) AS sub_expired_last_30d,
    countDistinctIf(l.user_id, sh.last_expired BETWEEN run_now - INTERVAL 90 DAY AND run_now - INTERVAL 30 DAY) AS sub_expired_30d_90d,
    countDistinctIf(l.user_id, sh.last_expired < run_now - INTERVAL 90 DAY) AS sub_expired_over_90d,
    countDistinctIf(l.user_id, th.user_id != 0) AS ever_had_trial,
    countDistinctIf(l.user_id, fh.user_id != 0) AS ever_had_family,
    countDistinctIf(l.user_id, ah.user_id != 0) AS ever_had_alim,
    countDistinctIf(l.user_id,
        sh.user_id = 0 AND th.user_id = 0 AND fh.user_id = 0 AND ah.user_id = 0
    ) AS never_had_any_entitlement
FROM leaked_users l
LEFT JOIN sub_history sh ON sh.user_id = l.user_id
LEFT JOIN trial_history th ON th.user_id = l.user_id
LEFT JOIN family_history fh ON fh.user_id = l.user_id
LEFT JOIN alim_history ah ON ah.user_id = l.user_id
FORMAT Vertical

-- Hypothesis: the 2026-05-18 leak cliff is driven by a batch of subscription
-- EXPIRED events around 2026-05-11 (7 days earlier — grace period rolls off).
-- PR #1621 (Yūnus, 2026-05-25) fixed "Stripe renewal transactions being silently
-- dropped" + ships scripts/backfill_stripe_renewals.py. If that's the cause,
-- we should see an anomalous spike of EXPIRED events around 2026-05-11.

WITH
    now64() AS run_now

SELECT
    toDate(expired_at) AS expired_day,
    count() AS expired_subscriptions,
    -- Compare to active count for context
    countIf(status = 'EXPIRED') AS now_status_expired,
    countIf(status = 'ACTIVE') AS now_status_active,
    countIf(status = 'GRACE') AS now_status_grace,
    countIf(status = 'CANCELLED') AS now_status_cancelled
FROM tarteeldb.public_billing_subscription FINAL
WHERE _peerdb_is_deleted = 0
  AND expired_at >= run_now - INTERVAL 30 DAY
  AND expired_at <= run_now
GROUP BY expired_day
ORDER BY expired_day DESC
FORMAT TabSeparatedWithNames

-- Free trials ending by day for last 30 days. Looking for cohort spike that
-- explains the 2026-05-18 leak cliff. Trials have no grace period per the audit
-- (profiles/tasks/user_management.py:92), so trial-end → immediate "non-entitled".

WITH
    now64() AS run_now

SELECT
    toDate(ended_at) AS trial_ended_day,
    count() AS trials_ending,
    -- For these trials, were they actually consumed (started_at < ended_at)?
    countIf(started_at < ended_at) AS started_before_end,
    -- Average trial length in days
    round(avg(dateDiff('day', started_at, ended_at)), 1) AS avg_trial_days
FROM tarteeldb.public_profiles_freetrial FINAL
WHERE _peerdb_is_deleted = 0
  AND ended_at >= run_now - INTERVAL 30 DAY
  AND ended_at <= run_now
GROUP BY trial_ended_day
ORDER BY trial_ended_day DESC
FORMAT TabSeparatedWithNames

WITH
    now64() AS run_now,
    toDateTime64('2026-05-05 04:19:00', 6, 'UTC') AS audit_cutoff,

    entitled_users AS (
        -- (same 8-branch UNION as Q1 — see above)
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND status IN ('ACTIVE', 'GRACE', 'CANCELLED')
        UNION DISTINCT
        SELECT id AS user_id FROM tarteeldb.public_profiles_user FINAL
        WHERE _peerdb_is_deleted = 0 AND subscription_type = 'PREMIUM'
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_profiles_freetrial FINAL
        WHERE _peerdb_is_deleted = 0 AND started_at <= run_now AND ended_at > run_now
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_profiles_familymember FINAL
        WHERE _peerdb_is_deleted = 0
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_alim_beneficiary FINAL
        WHERE _peerdb_is_deleted = 0 AND access_revoked >= run_now
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND start_date <= run_now - INTERVAL 2 YEAR
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscription FINAL
        WHERE _peerdb_is_deleted = 0 AND status = 'EXPIRED' AND expired_at > run_now - INTERVAL 7 DAY
        UNION DISTINCT
        SELECT DISTINCT user_id FROM tarteeldb.public_billing_subscriptionevent FINAL
        WHERE _peerdb_is_deleted = 0
          AND type IN ('EXPIRED', 'CANCELLED')
          AND created_at > run_now - INTERVAL 7 DAY
    )

SELECT
    toDate(created_at) AS day,
    count() AS leaked_sessions,
    countDistinct(user_id) AS leaked_users,
    round(count() / nullIf(countDistinct(user_id), 0), 2) AS avg_sessions_per_user
FROM tarteeldb.public_profiles_quransession FINAL
WHERE _peerdb_is_deleted = 0
  AND created_at >= audit_cutoff
  AND has_audio = TRUE
  AND session_type = 'RECITE'
  AND is_deleted = FALSE
  AND user_id NOT IN (SELECT user_id FROM entitled_users)
GROUP BY day
ORDER BY day DESC
FORMAT TabSeparatedWithNames