Flag inventory

1-- core.launchdarkly.flag_inventory
2-- Every feature flag with its age, lifecycle state and owner: the flag list in
3-- LaunchDarkly, with the columns you need to find flags that should be cleaned up.
4--
5-- Grain: one row per project x flag key (current state as of the last sync).
6-- Depends on: staging.launchdarkly.feature_flag, staging.launchdarkly.project
7--
8-- `is_cleanup_candidate` is a heuristic, not a LaunchDarkly status: a temporary flag,
9-- still live, more than 90 days old. LaunchDarkly's own "ready to archive" signal
10-- depends on evaluation data that the connector does not sync. Change the threshold
11-- to match your team's policy; it lives in one place below.
12
13SELECT
14    f.project_id,
15    COALESCE(p.project_key, f.project_key)              AS project_key,
16    p.project_name,
17    f.flag_key,
18    f.flag_name,
19    f.flag_kind,
20    f.tags,
21
22    CASE
23        WHEN f.is_archived   THEN 'archived'
24        WHEN f.is_deprecated THEN 'deprecated'
25        WHEN f.is_temporary  THEN 'live_temporary'
26        ELSE 'live_permanent'
27    END                                                 AS lifecycle_status,
28    f.is_temporary,
29    f.is_archived,
30    f.is_deprecated,
31
32    f.created_at,
33    DATE(f.created_at)                                  AS created_date,
34    DATE_DIFF(CURRENT_DATE(), DATE(f.created_at), DAY)  AS age_days,
35    CASE
36        WHEN DATE_DIFF(CURRENT_DATE(), DATE(f.created_at), DAY) <= 30  THEN '0-30 days'
37        WHEN DATE_DIFF(CURRENT_DATE(), DATE(f.created_at), DAY) <= 90  THEN '31-90 days'
38        WHEN DATE_DIFF(CURRENT_DATE(), DATE(f.created_at), DAY) <= 365 THEN '91-365 days'
39        ELSE 'over 1 year'
40    END                                                 AS age_bucket,