# _column_catalog

table_name: app_channel_weekly_letter
column_name: body_markdown
type: TEXT
unit: text
definition: Human-facing display text or categorical label.
example_value:
  This week recorded 186 views and 7 hours of watch time. Subscribers changed by +2. 7 videos were published.

  State transitions observed this week: 2026-04-13: crowded launch (v1.new_uploads_dominate).


table_name: app_channel_weekly_letter
column_name: evidence_json
type: TEXT
unit: json
definition: JSON-encoded payload. See `_json_schema` for shape.
example_value:
  {"metrics":{"week_views":186,"week_watch_minutes":425.54738333333336,"week_subs_net":2,"week_impressions":5990,"videos_published":7,"prior_week_views":116},"state_close":{"surface_state":"crowded_laun


table_name: app_dashboard_briefing_daily
column_name: evidence_json
type: TEXT
unit: json
definition: JSON-encoded payload. See `_json_schema` for shape.
example_value:
  {"recent_video_id":"v003","first_week_candidate_views":41,"first_week_candidate_impressions":1657,"first_week_baseline_median_views":9}


table_name: app_pool_cell_stat
column_name: cell_key
type: TEXT
unit: text
example_value: s2|27|a1|long
definition:
  The cohort cell that decided this metric's row — size_band|category|age_band|format, with '*' marking axes dropped by the deterministic coarsening ladder (e.g. 's2|27|*|*'). Always the channel's own axes at this snapshot.


table_name: app_pool_cell_stat
column_name: metric
type: TEXT
unit: enum
enum_values: views_28d,subs_28d,uploads_28d,video_day7_views
example_value: views_28d
definition:
  The pool metric this row aggregates, computed per member channel over data at or before snapshot_date.


table_name: app_pool_cell_stat
column_name: snapshot_date
type: TEXT
unit: iso_date
definition:
  The as-of date this row was computed for (the reporting-cutoff walker date) — the snapshot dimension; the PK ends with it.


table_name: app_pool_cell_stat
column_name: gate_state
type: TEXT
unit: enum
enum_values: lit,withheld
example_value: withheld
definition:
  Whether the cell's aggregate may be shown ('lit') or stays silent ('withheld'). A withheld row carries NULL in every value column — silence is the honest fallback, never an error.


table_name: app_pool_cell_stat
column_name: rung
type: TEXT
unit: enum
nullable_reason: NULL when gate_state = 'withheld'.
enum_values: median,quartile
example_value: median
definition:
  The display rung the gate cleared — 'median' (one typical value) or 'quartile' (median plus inner quartiles).


table_name: app_pool_cell_stat
column_name: member_band
type: TEXT
unit: enum
nullable_reason: NULL when gate_state = 'withheld'.
enum_values: 5-9,10-24,25-99,100plus
example_value: 5-9
definition:
  Floored band of the cell's member count. The exact member count is never stored — the band floors what an observer can infer about who occupies the cell.


table_name: app_pool_cell_stat
column_name: cell_median
type: REAL
unit: score_dimensionless
nullable_reason: NULL when gate_state = 'withheld'.
definition:
  The cell's median member value for this metric (units follow the metric — views, net subscribers, or uploads). Carries small deterministic noise near the privacy floor or when the cell holds a non-public pool member.


table_name: app_pool_cell_stat
column_name: pooled_estimate
type: REAL
unit: score_dimensionless
definition:
  This channel's partial-pooling (shrinkage) estimate for the metric — its own value pulled toward the cell mean in proportion to its noise.

nullable_reason:
  Stored only where publication is exact (large cell, every member already public); NULL when withheld, near the privacy floor, or whenever the cell holds a non-public member.


table_name: app_pool_cell_stat
column_name: computed_at
type: TEXT
unit: iso_date
definition:
  Equals snapshot_date — the as-of date the row was computed for (never a build wall-clock timestamp).


table_name: app_pool_named_peer
column_name: peer_contributor_id
type: TEXT
unit: text
example_value: contributor-001
definition:
  Stable contributor id of an eligible pool channel (currently public AND pool-consented). Includes this DB's own channel when eligible — the table stores the full eligible set, identical in every channel's DB.


table_name: app_pool_named_peer
column_name: peer_handle
type: TEXT
unit: text
example_value: @examplechannel
definition:
  The peer's YouTube handle, canonical lowercase, as captured at pool-extract time from the live registry.


table_name: app_pool_named_peer
column_name: total_views
type: INTEGER
unit: count
nullable_reason: NULL when the pool holds no channel-stats reading at or before snapshot_date.
definition:
  The peer's lifetime view total as of snapshot_date (latest channel-stats reading, not a sum of per-day rows). Can decrease across snapshots (deleted/privated videos).


table_name: app_pool_named_peer
column_name: total_subscribers
type: INTEGER
unit: count
nullable_reason: NULL when the pool holds no channel-stats reading at or before snapshot_date.
definition:
  The peer's lifetime subscriber total as of snapshot_date (latest channel-stats reading). Can decrease across snapshots (unsubscribes).


table_name: app_pool_named_peer
column_name: views_28d
type: INTEGER
unit: count
definition: The peer's views over the trailing 28 days ending at snapshot_date.
nullable_reason:
  NULL unless every day in the window is covered by the pool's analytics history (recent dates sit inside the reporting lag; early dates can predate the pool's coverage).


table_name: app_pool_named_peer
column_name: video_day7_views_median
type: REAL
unit: count
nullable_reason: NULL until the peer has at least 3 videos old enough for a day-7 reading.
definition:
  Median day-7 views across the peer's videos old enough for a day-7 reading at snapshot_date (published at least 7 days earlier).


table_name: app_insight_snapshot
column_name: signal_family
type: TEXT
unit: enum
enum_values: momentum,traffic,lifecycle,geography,engagement,forecast,milestone,catalog_shape,data_quality
definition:
  Coarse signal family for this insight's logical detector — what the signal is about, not its maturity gate. Stamped via `signalFamilyOf` over the durable nine-member taxonomy in `dashboard/src/lib/insights/signal-family.ts`. Later surfaces (episode grouping, the what-changed digest) key off this instead of re-deriving families from `insight_key` prefixes.

nullable_reason:
  NULL on synthetic non-detector rows (e.g. the channel_shape fingerprint row), which carry no logical detector to classify.


table_name: owner_read_daily
column_name: subs_gained_7d_prior
type: INTEGER
unit: count
nullable_reason: NULL before a full prior 7-day window exists.
definition:
  Subscribers-gained total over the 7-day window immediately prior — the comparison basis for `subs_trend`.


table_name: owner_read_daily
column_name: impressions_7d_prior
type: INTEGER
unit: count
nullable_reason: NULL before a full prior 7-day window exists.
definition:
  Impressions total over the 7-day window immediately prior — the comparison basis for `impressions_trend`.


table_name: owner_read_daily
column_name: source_mix_direction
type: TEXT
unit: enum
nullable_reason: NULL when the prior window can't be honestly computed.
enum_values: up,flat,down
definition:
  Direction of the source-diversity score vs the prior window — 'up' (diversifying) / 'down' (consolidating) / 'flat'.


table_name: owner_read_daily
column_name: quiet_video_count
type: INTEGER
unit: count
definition:
  Public videos with zero views in the trailing 7 days. A quiet video is an honest read, never "weak".


table_name: owner_read_daily
column_name: forecast_usability
type: TEXT
unit: enum
nullable_reason: NULL when no forecast is surfaceable on this date.
enum_values: confident,directional,unusable
definition:
  The best (most confident) forecast tier across every surfaced (metric, horizon) on this date — mirrors `overallForecastTier(assessForecastUsability(...))`.


table_name: owner_read_daily
column_name: thin_read
type: INTEGER
unit: enum
enum_values: 0,1
definition:
  Sample-of-one honesty flag (0/1). Set to 1 when the day is too thin to read as anything but quiet — no insights and a silent week, a complete 7d window of zero views, or a not-yet-computable window on a young channel. A quiet day is an honest read, never a shortfall.


table_name: owner_read_daily
column_name: computed_at
type: TEXT
unit: iso_datetime
definition:
  Build stamp bound to `snapshot_date` (midnight UTC of that date), not wall-clock — keeps the build byte-idempotent.


table_name: app_wisdom_canon
column_name: calculation_doc
type: TEXT
unit: text
definition: Human-facing display text or categorical label.
example_value:
  For each video pair (N−1, N) where N−1 is still in days 1–7 of its
  lifecycle when N publishes, compare N−1's view trajectory in the 3 days
  after N's publish to N−1's trajectory in the 3 days before. C


table_name: channel_phase_log
column_name: evidence_json
type: TEXT
unit: json
definition: JSON-encoded payload. See `_json_schema` for shape.
example_value:
  {"phase":"suggested_dominant","browsePct":6.2,"suggestedPct":80.2,"searchPct":0,"channelPct":4.9,"total_views":81}


table_name: channel_timeline_event
column_name: event_key
type: TEXT
unit: text
example_value: a1b2c3d4e5f60718
definition:
  Stable 16-hex-char SHA-1 prefix of event_type|date|detector|video_id|headline. The same logical event keeps the same event_key across snapshot rebuilds. Identity in the table is (event_key, snapshot_date).


table_name: channel_timeline_event
column_name: snapshot_date
type: TEXT
unit: iso_date
example_value: 2026-05-11
definition:
  Reporting cutoff this row was computed against. Single value per build today (V64 / Backfill Pass Phase 2); Phase 3 will let multiple snapshot_date values coexist so the public DB can render the channel as it would have looked on any prior reporting cutoff.


table_name: channel_timeline_event
column_name: event_type
type: TEXT
unit: enum
example_value: step_change
definition:
  Discriminator for the event row. Drives `evidence_json` shape — see `_json_schema` for per-type keys.

enum_values:
  publish,milestone,insight,stat_snapshot,rank_change,first_external,note,subscriber_loss_event,stage_transition,era_start,audience_expansion,source_quiet,creator_event,cadence_break,edit_burst,multi_video_activation,cohort_lift,emerging_audience,wisdom_flip,country_first_view


table_name: channel_timeline_event
column_name: evidence_json
type: TEXT
unit: json
definition: Structured evidence payload. Shape varies by `event_type` — see `_json_schema`.
nullable_reason: Some event types carry no structured evidence.
example_value:
  {"metric":"subs_gained_rolling","metric_label":"subscribers gained (7-day mean)","split_date":"2026-04-18","before_mean":0.6524,"after_mean":0.1364,"magnitude_pct":-79}


table_name: channel_timeline_event
column_name: significance
type: INTEGER
unit: score_dimensionless
nullable_reason: Not every event_type has a significance score.
example_value: 59
definition:
  Magnitude × confidence × recency score. Higher values indicate the event was more notable for the channel.


table_name: data_status
column_name: metric_type
type: TEXT
unit: text
example_value: views
definition:
  Domain key (e.g. `views`, `impressions_ctr`, `traffic_sources`, `channel_snapshots`, `reporting_timezone`).


table_name: data_status
column_name: source
type: TEXT
unit: text
example_value: reporting+analytics
definition:
  Source description (e.g. `reporting+analytics`, `data-api`). For the `reporting_timezone` row, holds the IANA timezone string.


table_name: event_channel_state_change
column_name: reason_text
type: TEXT
unit: text
definition: Human-facing display text or categorical label.
example_value:
  One video holds more than 40% of trailing 7-day impressions — catalog reach is anchored to a single title.


table_name: event_creator_edit
column_name: snapshot_date
type: TEXT
unit: iso_date
example_value: 2026-05-11
definition:
  Reporting cutoff this row was computed against. Single value per build today (V64 / Backfill Pass Phase 2); Phase 3 will let multiple snapshot_date values coexist.


table_name: metric_metadata
column_name: computation_doc
type: TEXT
unit: text
definition: Human-facing display text or categorical label.
example_value:
  Per-day weighted blend of trailing CTR, AVD, sub-rate, source diversity, and library-activity z-scores against the prior 90-day baseline. Below 35 days of baseline the column stays NULL. 50 reads as "


table_name: summary_channel
column_name: library_half_mass_video_count
type: INTEGER
unit: count
example_value: 2
deprecated_alias: library_half_life_days
definition:
  How many videos, ordered by lifetime views descending, account for half of total channel views. A lower value typically indicates concentration in a small number of videos.


table_name: summary_channel
column_name: funnel_impressions_total
type: INTEGER
unit: count
nullable_reason: NULL only when summary_video has no rows.
example_value: 5482910
definition:
  Lifetime channel impressions, summed across summary_video.total_impressions. Provides the top of the channel-wide funnel (impressions → clicks → views → subscribers) on a single row.


table_name: summary_channel
column_name: funnel_clicks_total
type: INTEGER
unit: count
nullable_reason: NULL when no video has both impressions and a weighted CTR.
example_value: 124318
definition:
  Lifetime clicks across the catalog, derived from SUM(total_impressions × weighted_ctr) over summary_video. Computed from impression-weighted per-video CTRs rather than the unweighted summary_channel.avg_ctr (which would understate clicks on channels with a few high-impression videos).


table_name: summary_channel
column_name: funnel_subs_total
type: INTEGER
unit: count
example_value: 3142
definition:
  Lifetime subscribers gained, mirroring summary_channel.total_subs_gained on a dedicated funnel surface so the impressions → clicks → subs chain reads as one shape.


table_name: summary_channel
column_name: funnel_impression_to_sub_pct
type: REAL
unit: pct_0_to_100
nullable_reason: NULL when funnel_impressions_total is 0.
example_value: 0.057
definition:
  Share of lifetime impressions that ended in a subscriber, expressed as a percentage (0..100). Typically a small fraction of a percent. Useful as a channel-internal comparison over rebuilds; not a benchmark.


table_name: summary_channel_metrics_daily
column_name: source_diversity_score
type: REAL
unit: score_dimensionless
definition:
  Effective number of traffic sources (exp of entropy). Higher values indicate more diverse source mix.


table_name: summary_channel_metrics_daily
column_name: videos_in_debut
type: INTEGER
unit: count
example_value: 1
definition:
  Public videos in their 'debut' lifecycle stage on this date (days_since_publish 0..2). Sourced from summary_video_stage_daily; stage names mirror lifecycle-classifier.js.

nullable_reason:
  NULL on dates where the stage classifier produced no rows (distinct from 0, which means "no videos in this stage on this date").


table_name: summary_channel_metrics_daily
column_name: videos_in_growth
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 3
definition:
  Public videos in their 'growth' lifecycle stage on this date (early lifetime, views rising vs prior day).


table_name: summary_channel_metrics_daily
column_name: videos_in_tail
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 12
definition:
  Public videos classified as 'tail' on this date — the catch-all post-peak stage when none of the other stages apply.


table_name: summary_channel_metrics_daily
column_name: videos_in_re_emerged
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 0
definition:
  Public videos whose prior stage was 'quiet' and whose views ≥3 on this date — a typically rare re-activation signal.


table_name: summary_channel_metrics_daily
column_name: videos_in_zombie
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 2
definition:
  Public videos with trailing-7-day impressions ≥50 and views ≤1 on this date — typically a stuck pattern where the algorithm keeps showing the video but nobody is clicking.


table_name: summary_channel_metrics_daily
column_name: videos_in_dormant
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 5
definition:
  Public videos with zero trailing-14-day impressions on this date (after day 14) — typically the algorithm has stopped showing the video at all.


table_name: summary_channel_traffic
column_name: subs_per_1k_views
type: REAL
unit: score_dimensionless
nullable_reason: NULL when the source had zero attributed views.
example_value: 4.21
definition:
  Subscribers gained per 1000 views attributed to this traffic source. Aggregated from summary_video_traffic.subs_gained (itself a proportional allocation of total_subs_gained by view share — YouTube does not break out subscribers gained by source in the public reporting feed), so this value is typically a modeled share rather than a measured count. Often useful for comparing the relative subscriber-conversion lift across sources.


table_name: summary_video
column_name: weighted_ctr
type: REAL
unit: share_0_to_1
confidence_column: ctr_confidence
example_value: 0.0215
definition:
  Click rate weighted by impressions, stored 0..1. Often noisy on small samples — paired with `ctr_confidence`.


table_name: summary_video
column_name: ctr_confidence
type: TEXT
unit: enum
enum_values: noise,low,medium,high
example_value: medium
definition:
  Sample-size confidence tier for `weighted_ctr`. UI typically mutes or strikes through CTR values when this is `noise`.


table_name: summary_video
column_name: imp_to_view_ratio
type: REAL
unit: share_0_to_1
example_value: 33
definition:
  Lifetime impressions divided by views, stored 0..1. Often interpreted as "how many impressions per view this video typically needed."


table_name: summary_video
column_name: source_hhi
type: REAL
unit: score_dimensionless
example_value: 4128.0599999999995
definition:
  Herfindahl-Hirschman index of source mix (0..10000). Higher values indicate more concentration in a single source.


table_name: summary_video
column_name: ctr_quadrant
type: TEXT
unit: enum
enum_values: winner,clicked-not-watched,watched-not-clicked,quiet,noise
example_value: noise
definition:
  Channel-internal CTR×retention quadrant. `noise` rows have inconclusive samples and never render a badge.


table_name: summary_video_country_lifetime
column_name: subs_per_1k_views
type: REAL
unit: score_dimensionless
nullable_reason: NULL when total_views is 0 for this (video, country) row.
example_value: 2.715
definition:
  Subscribers gained per 1000 views for this (video, country) pair. Computed at INSERT time as `subs_gained * 1000 / total_views`. Unlike the per-source aggregate on summary_channel_traffic this is a measured value (the source reporting feed exposes subscribers_gained by country), so it typically reflects actual conversion lift rather than a modeled share.


table_name: traffic_daily
column_name: pre_publish_stub
type: INTEGER
unit: enum
enum_values: 0,1
example_value: 0
definition:
  When 1, the row predates the video publish date and carries NULL/0 values; filter on pre_publish_stub = 0 or use `traffic_daily_clean`.


table_name: traffic_source
column_name: category
type: TEXT
unit: text
example_value: direct
definition:
  High-level grouping (e.g. "browse", "search", "external", "subscriber"). Drives source-mix visualisations.


table_name: v_event_ledger
column_name: source_table
type: TEXT
unit: enum
example_value: channel_timeline_event
definition:
  Which event_* table this row was drawn from. Use to disambiguate when joining back to the source for richer columns.

enum_values:
  channel_timeline_event,event_creator_edit,event_country_first_view,event_source_first_view,event_channel_state_change,event_channel_annotation


table_name: v_event_ledger
column_name: event_type
type: TEXT
unit: text
example_value: step_change
definition:
  Event-type discriminator. From channel_timeline_event.event_type when source_table = channel_timeline_event; otherwise a synthetic per-source-table label.


table_name: video
column_name: duration_seconds
type: INTEGER
unit: seconds
example_value: 180
definition:
  Video duration in seconds. Coarsened to bucket lower bounds (60/180/360/600/1200/1800/3600) for anonymity.


table_name: video_current_state
column_name: evidence_json
type: TEXT
unit: json
definition: JSON-encoded payload. See `_json_schema` for shape.
example_value:
  {"days_since_publish":0,"views_to_date":9,"impressions_to_date":208,"views_7d":9,"impressions_7d":208,"day7_views_to_date":9,"late_views_to_date":0}


table_name: video_daily
column_name: avg_view_pct
type: REAL
unit: pct_0_to_100
example_value: 18.333389830508473
definition:
  Average view percentage (avg_view_seconds / duration_seconds), stored 0..100. Clamped at build time.


table_name: video_daily
column_name: pre_publish_stub
type: INTEGER
unit: enum
enum_values: 0,1
example_value: 0
definition:
  When 1, the row predates the video publish date and carries NULL/0 values; filter on pre_publish_stub = 0 or use `video_daily_clean`.


table_name: _about
column_name: value
type: TEXT
unit: text
example_value: ytpool / Open Channel Stats
definition:
  Free-text value for the matching key. Typically short — multi-line voice rules and licence strings are the longest entries.


table_name: _table_catalog
column_name: table_name
type: TEXT
unit: text
example_value: summary_video
definition:
  Name of a real table or view in the public DB. The Phase 10.2 drift guard enforces a 1:1 correspondence with `sqlite_master`.


table_name: _table_catalog
column_name: layer
type: TEXT
unit: enum
enum_values: fact, summary, cohort, event, application, metadata, catalog, reference, view
example_value: summary
definition:
  Architectural layer this table sits in. `fact` rows describe raw events; `summary_*` and `cohort_*` precompute aggregates; `event_*` is a discrete event ledger; `app_*` powers the dashboard; `metadata` and `catalog` describe the DB itself.


table_name: _table_catalog
column_name: grain
type: TEXT
unit: text
example_value: (video_id)
definition:
  The primary-key tuple shorthand (`(video_id)`, `(video_id, date)`, etc.). Identifies what one row represents.


table_name: _table_catalog
column_name: row_count
type: INTEGER
unit: count
example_value: 31
definition:
  Row count at build time. Snapshot only — useful for sanity-checking but expected to change on every refresh.


table_name: _table_catalog
column_name: description
type: TEXT
unit: text
example_value: Per-video lifetime aggregates.
definition:
  Human-readable summary of what the table holds, how it's computed, and when a reader should query it.


table_name: _table_catalog
column_name: query_first
type: INTEGER
unit: enum
enum_values: 0, 1
example_value: 1
definition:
  0/1 flag — 1 when a new reader should typically read this table early (broad orientation), 0 for narrow specialty tables.


table_name: _table_catalog
column_name: caveats
type: TEXT
unit: text
example_value: Filter `pre_publish_stub = 0` for analytics; otherwise pre-publish stub rows distort impressions.
definition:
  Free-text caveats — sampling gates, partial-day cutoffs, units, known artifacts a query author often needs to know.


table_name: _column_catalog
column_name: unit
type: TEXT
unit: enum
example_value: share_0_to_1
definition:
  Semantic unit. Drives display formatting and tells a reader whether a value is `0..1`, `0..100`, seconds, minutes, etc.

enum_values:
  count, pct_0_to_100, share_0_to_1, seconds, minutes, hours, days, iso_date, iso_datetime, iso_week, enum, json, text, video_id_anon, country_code, score_dimensionless


table_name: _column_catalog
column_name: definition
type: TEXT
unit: text
example_value: Total lifetime views across all dates.
definition:
  Short human description of what the column holds — typically hedged ("often", "usually") when describing typical interpretation rather than a hard guarantee.


table_name: _column_catalog
column_name: nullable_reason
type: TEXT
unit: text
example_value: NULL when impressions = 0 for the date.
definition:
  Free-text explanation of when the column can legitimately be NULL. Useful to distinguish "missing data" from "structurally absent".


table_name: _column_catalog
column_name: confidence_column
type: TEXT
unit: text
example_value: ctr_confidence
definition:
  Optional pointer to a sibling column that quantifies the confidence of this column's value (e.g. CTR's `ctr_confidence` tier).


table_name: _column_catalog
column_name: example_value
type: TEXT
unit: text
example_value: 294
definition:
  One sample value pulled from the live DB at build time, truncated to ~200 chars. Useful as a sanity check on type and range.


table_name: _json_schema
column_name: variant_key
type: TEXT
unit: text
example_value: event_type
definition:
  Sibling column whose value selects the JSON shape (e.g. `event_type` on `channel_timeline_event`). Null when the column carries a single shape.


table_name: _query_examples
column_name: category
type: TEXT
unit: enum
enum_values: channel_overview, video_catalog, traffic, geographic, events, lifecycle, wisdom, data_quality
example_value: channel_overview
definition:
  Topical grouping (channel_overview, video_catalog, traffic, geographic, events, lifecycle, wisdom, data_quality). Used by sibling SCHEMA.md to organize examples.


table_name: _query_examples
column_name: sql
type: TEXT
unit: text
definition:
  Executable SQL string. The Phase 10.3 CI guard runs each row against the live DB and asserts it parses, executes, and returns >= expected_min_rows.

example_value:
  SELECT current_subscribers, total_views, total_public_videos, latest_data_date FROM summary_channel;


table_name: _query_examples
column_name: expected_min_rows
type: INTEGER
unit: count
enum_values: 0, 1
example_value: 1
definition:
  Minimum row count the query is expected to return on a healthy build. Values are 0 (legitimately empty on quiet days / small catalogs) or 1 (always expect at least one row).


table_name: event_video_phase_transition
column_name: snapshot_date
type: TEXT
unit: iso_date
example_value: 2026-05-11
definition:
  Reporting cutoff this row was computed against. V64 / Backfill Pass Phase 2 — single value per build today; Phase 3 lets multiple snapshot dates coexist.


table_name: event_video_phase_transition
column_name: event_date
type: TEXT
unit: iso_date
example_value: 2026-04-12
definition:
  Calendar date in PT (America/Los_Angeles) when the video crossed from one lifecycle phase to another.


table_name: event_video_phase_transition
column_name: days_in_from_phase
type: INTEGER
unit: days
nullable_reason: NULL on the first transition for videos with no measured prior-phase duration.
example_value: 3
definition:
  How long the video typically held the prior phase before transitioning. Useful for distinguishing fast pivots from slow drifts.


table_name: event_video_phase_transition
column_name: magnitude
type: REAL
unit: score_dimensionless
example_value: 77.9220779220779
definition:
  Combined magnitude score capturing how unusual the transition was for this channel. Sorts the largest pivots to the top.


table_name: summary_channel_cohort
column_name: baseline_avg_day30_views
type: REAL
unit: count
nullable_reason: NULL on early cohorts where no comparable baseline exists.
definition:
  Channel-internal baseline for day-30 views, computed across earlier cohorts. Useful as a comparison point for vs_baseline_pct.


table_name: summary_channel_cohort
column_name: vs_baseline_pct
type: REAL
unit: pct_0_to_100
nullable_reason: NULL when baseline_avg_day30_views is NULL or zero.
definition:
  Percent change between avg_day30_views and baseline_avg_day30_views. Positive = cohort outperformed channel baseline.


table_name: summary_channel_cohort
column_name: cohort_label
type: TEXT
unit: enum
enum_values: outperformed, on_pace, underperformed, mixed, insufficient_baseline
definition:
  Short categorical summary of how the cohort typically performed against baseline. Used for narrative chapters.


table_name: summary_channel_country_cohort_stability
column_name: avd_vs_baseline_sec
type: REAL
unit: seconds
example_value: 104.233333333333
definition:
  Difference between this country's AVD and the channel's baseline AVD on the same date. Useful for spotting weak-retention emergences.


table_name: summary_channel_country_cohort_stability
column_name: persistence_score_7d
type: REAL
unit: score_dimensionless
example_value: 1.0
definition:
  Share of the trailing 7 days on which this country was present in the audience. Closer to 1.0 typically reflects a stable cohort; closer to 0.0 reflects a one-day flash.


table_name: summary_channel_source_leading_indicator
column_name: source_hhi_14d
type: REAL
unit: score_dimensionless
example_value: 0.768014831961591
definition:
  Trailing-14-day Herfindahl-Hirschman index over traffic-source view share. 1.0 means a single source; 0.0 means evenly distributed.


table_name: summary_channel_source_leading_indicator
column_name: sticky_share_14d
type: REAL
unit: share_0_to_1
example_value: 0.0324074074074074
definition:
  Share of trailing-14-day views from sources classified as 'sticky' (subscriptions, browse, search). Stored 0..1.


table_name: summary_channel_source_leading_indicator
column_name: net_subs_next_7d
type: INTEGER
unit: count
nullable_reason: NULL when the 7-day forward window is beyond the reporting cutoff.
example_value: 4
definition:
  Net subscribers gained in the 7 days AFTER this date. Future-looking column — usually NULL near the cutoff.


table_name: summary_channel_source_leading_indicator
column_name: pearson_r_hhi_vs_subs
type: REAL
unit: score_dimensionless
nullable_reason: NULL when sample size is below the gate.
example_value: -0.297893540451509
definition:
  Rolling Pearson correlation between recent HHI movement and forward-window subs change. Useful as a leading-indicator strength hint.


table_name: summary_channel_source_leading_indicator
column_name: pattern_label
type: TEXT
unit: enum
definition: Short categorical summary of what the concentration shift typically precedes for this channel.
example_value: mixed
enum_values:
  mixed, concentrating_with_subs_rising, concentrating_with_subs_falling, diversifying_with_subs_rising, diversifying_with_subs_falling, unknown


table_name: summary_video_daily_rank
column_name: peer_count
type: INTEGER
unit: count
example_value: 23
definition:
  Number of videos in the peer set for this (video, date). Useful as a sample-size gate before reading the rank columns.


table_name: summary_video_decay_model
column_name: predicted_quiet_date
type: TEXT
unit: iso_date
nullable_reason: NULL when the model can't project a quiet date (typically plateau fits or insufficient sample).
definition:
  Earliest projected calendar date the video is expected to enter the 'quiet' lifecycle phase. Forecast, not measured.


table_name: summary_video_decay_model
column_name: goodness_of_fit_r2
type: REAL
unit: score_dimensionless
example_value: 0.334134404304709
definition:
  Coefficient of determination (R²) of the fit. Closer to 1.0 is a tighter fit; small samples can produce misleading high R².


table_name: summary_video_decay_model
column_name: usable_for_projection
type: INTEGER
unit: enum
enum_values: 0,1
example_value: 1
definition:
  1 when the fitted decay model is good enough to inform a channel-scope projection (R² ≥ 0.5, sample ≥ 21 days, fit age ≤ 90 days). 0 otherwise. Stricter than the per-video R² ≥ 0.7 'fire an insight' gate that produces `video_imminent_quiet` insights — usability is the looser 'input the catalog-share computation can read' bar. Catalog-share detectors filter weak fits with this flag.


table_name: summary_threshold_projection
column_name: rate_per_day
type: REAL
unit: score_dimensionless
confidence_column: confidence
example_value: 0.21
definition:
  Mean per-day rate of the underlying quantity over the trailing lookback window — units track threshold_type (subs/day, views/day, videos/day). Zero or negative when the channel has been flat or losing ground.


table_name: summary_threshold_projection
column_name: projected_date
type: TEXT
unit: iso_date
nullable_reason: NULL when confidence is 'insufficient' or the rate is not positive.
confidence_column: confidence
example_value: 2026-07-04
definition:
  Calendar date the threshold projects to cross at the trailing pace. Arithmetic on recent data, not a prediction; the projection assumes recent cadence and discovery mix hold.


table_name: summary_threshold_projection
column_name: projected_date_low
type: TEXT
unit: iso_date
nullable_reason: NULL when the bootstrap band's upper bound is not positive or confidence is 'insufficient'.
confidence_column: confidence
example_value: 2026-06-22
definition:
  Earlier end of the uncertainty band — the date the threshold projects to if recent pace tracks toward the high end of its bootstrap range.


table_name: summary_threshold_projection
column_name: projected_date_high
type: TEXT
unit: iso_date
nullable_reason: NULL when the bootstrap band's lower bound is not positive or confidence is 'insufficient'.
confidence_column: confidence
example_value: 2026-07-16
definition:
  Later end of the uncertainty band — the date the threshold projects to if recent pace tracks toward the low end of its bootstrap range.


table_name: summary_threshold_projection
column_name: lookback_days
type: INTEGER
unit: days
example_value: 7
definition:
  Trailing-window length used for the rate computation. Tighter on high-cadence channels (7d) and wider on sparse ones (28d).


table_name: summary_threshold_projection
column_name: confidence
type: TEXT
unit: enum
enum_values: high, medium, low, insufficient
example_value: medium
definition:
  Confidence tier derived from sample size and the relative width of the bootstrap band. Surfaces hedged copy when 'insufficient' or 'low'.


table_name: summary_channel_narrative_arc
column_name: arc_id
type: TEXT
unit: text
example_value: arc_01
definition:
  Stable arc identifier within a snapshot (`arc_NN`, ordered by start date). Part of the primary key with snapshot_date.


table_name: summary_channel_narrative_arc
column_name: arc_kind
type: TEXT
unit: enum
enum_values: opening, building, steady, shifting, quiet, current
example_value: opening
definition:
  Neutral descriptor of the arc's shape — never value-laden (no "peak"/"climax"/"decline"). A quiet arc is equal to a busy one; only the shape differs.


table_name: summary_channel_narrative_arc
column_name: span_days
type: INTEGER
unit: days
example_value: 14
definition:
  Length of the arc window in days (end − start + 1). Never below the 7-day floor that keeps a thin timeline from fragmenting into fake arcs.


table_name: summary_channel_narrative_arc
column_name: event_density
type: REAL
unit: score_dimensionless
example_value: 0.5
definition:
  Weighted timeline-events per day across the arc window — context for the arc's texture, not a ranking. Higher-significance events count for more; this is a sample-of-one observation, never a base rate.


table_name: summary_channel_narrative_arc
column_name: view_trend_label
type: TEXT
unit: enum
enum_values: rising, flat, easing, mixed
example_value: rising
definition:
  Hedged window-over-window direction of the channel's 7-day view rate across the arc — observational, never causal. `mixed` marks a noisy window with no clear direction.


table_name: summary_channel_narrative_arc
column_name: dominant_motion
type: TEXT
unit: enum
enum_values: arrival, expansion, plateau, turn, open
example_value: arrival
definition:
  The macro-shape the /story scroll choreographs for this arc. Neutral motion vocabulary, not a performance verdict.


table_name: summary_channel_narrative_arc
column_name: is_open
type: INTEGER
unit: enum
enum_values: 0, 1
example_value: 0
definition:
  1 for the current / unfinished arc that contains the snapshot date (the "not yet recorded" frame); 0 for closed arcs.


table_name: summary_channel_narrative_arc
column_name: headline
type: TEXT
unit: text
example_value: The opening stretch
definition:
  Hedged, observational label for the arc (e.g. "A steadier stretch"). No drama, no superlatives, no causal claims, no advice.


table_name: summary_channel_narrative_arc
column_name: computed_at
type: TEXT
unit: iso_datetime
example_value: 2026-05-28T00:00:00Z
definition:
  Build-time ISO timestamp the arc set was computed at, bound to the snapshot date (midnight UTC) so back-to-back builds stay byte-identical.


table_name: summary_channel_narrative_arc
column_name: snapshot_date
type: TEXT
unit: iso_date
example_value: 2026-05-28
definition:
  Reporting cutoff this arc set was computed for. Part of the primary key; the time-machine reads `WHERE snapshot_date = <scrub date>` so no future arc leaks into a rewound view.


table_name: summary_channel_forecast
column_name: as_of_date
type: TEXT
unit: iso_date
example_value: 2026-05-12
definition:
  Reporting cutoff the forecast was computed against — same as summary_channel.latest_data_date at build time. Pairs with horizon_days to anchor the projection in calendar time.


table_name: summary_channel_forecast
column_name: horizon_days
type: INTEGER
unit: days
example_value: 14
definition:
  Days from as_of_date the projection lands at. One row per (horizon, forecast_type); horizons 7 / 14 / 30 / 90.


table_name: summary_channel_forecast
column_name: forecast_type
type: TEXT
unit: enum
enum_values: subscribers, daily_views
example_value: subscribers
definition:
  Which channel-wide quantity the row projects. `subscribers` fits OLS directly on the cumulative subscriber count; `daily_views` fits OLS on the rolling 7-day mean of per-day views.


table_name: summary_channel_forecast
column_name: projected_value
type: REAL
unit: score_dimensionless
nullable_reason: NULL when confidence is 'insufficient'.
confidence_column: confidence
example_value: 14.0
definition:
  Point estimate the trailing-window linear trend projects to at as_of_date + horizon_days — units track forecast_type (subscriber count for `subscribers`, daily views for `daily_views`). Arithmetic on the past N days, not a prediction; assumes recent direction and discovery mix hold.


table_name: summary_channel_forecast
column_name: projected_low
type: REAL
unit: score_dimensionless
nullable_reason: NULL when confidence is 'insufficient'.
confidence_column: confidence
example_value: 11.5
definition:
  10th-percentile end of the bootstrap band — projected value when the resampled fit lands at the low end of its range.


table_name: summary_channel_forecast
column_name: projected_high
type: REAL
unit: score_dimensionless
nullable_reason: NULL when confidence is 'insufficient'.
confidence_column: confidence
example_value: 17.2
definition:
  90th-percentile end of the bootstrap band — projected value when the resampled fit lands at the high end of its range.


table_name: summary_channel_forecast
column_name: model_type
type: TEXT
unit: enum
enum_values: linear_trend
example_value: linear_trend
definition:
  Model family that produced the row. `linear_trend` is the only family in v1; future bumps can layer log-linear or seasonal models alongside without breaking readers.


table_name: summary_channel_forecast
column_name: lookback_days
type: INTEGER
unit: days
example_value: 28
definition:
  Trailing-window length used for the OLS fit. 28 days when the channel's classify_cadence reports high cadence; 56 days otherwise.


table_name: summary_channel_forecast
column_name: sample_size_days
type: INTEGER
unit: days
example_value: 28
definition:
  Number of (x, y) points the OLS fit actually had to work with after slicing to the lookback and computing the rolling 7-day mean (for the daily_views row). Below 14 the row collapses to 'insufficient'.


table_name: summary_channel_forecast
column_name: confidence
type: TEXT
unit: enum
enum_values: high, medium, low, insufficient
example_value: medium
definition:
  Confidence tier derived from sample size and the bootstrap band's width relative to the point estimate. `insufficient` also fires when the band straddles zero on a non-negative series — UI surfaces hedged "too volatile to project usefully" copy at that point.


table_name: summary_channel_forecast
column_name: historical_band_coverage
type: REAL
unit: share_0_to_1
example_value: 0.0
definition:
  Per-(forecast_type, horizon_days) inside-band fraction over the trailing window of up to 10 past forecasts on this cell whose inside_band has been resolved. Computed by compute-forecast-calibration.js after the ledger materializes, then back-written into the row for the current as_of_date. NULL when fewer than 5 such entries exist (small-sample floor); consumers read NULL as "no historical signal yet, no downgrade fires." When non-null and < 0.5, assessForecastUsability demotes the cell's confident tier to directional — the calibration-honesty rule applied per cell.

nullable_reason:
  Fewer than 5 resolved band-checked entries on this (forecast_type, horizon_days) cell, or no calibration ledger materialized for the build.


table_name: summary_channel_forecast
column_name: historical_hit_rate
type: REAL
unit: share_0_to_1
example_value: 0.0
definition:
  Per-(forecast_type, horizon_days) fraction of past forecasts on this cell whose point estimate landed within ±20% of the observed value, over the trailing window of up to 10 entries whose error_pct has been resolved. NULL when fewer than 5 such entries exist. Reserved for a future consumer; written deterministically alongside historical_band_coverage so the surface can be wired without a schema re-bump.

nullable_reason:
  Fewer than 5 resolved entries with a non-null error_pct on this (forecast_type, horizon_days) cell, or no calibration ledger materialized for the build.


table_name: summary_channel_forecast
column_name: band_widen_k_low
type: REAL
unit: count
example_value: 1.0
definition:
  Multiplier the build applied to the raw bootstrap lower-band half-width to produce the surfaced predicted_low. 1.0 means no widening (the raw bootstrap band already covered the trailing window's miss tail well); values above 1.0 mean the build widened the band because past forecasts on this cell undershot below it more often than they should have. Capped at 5.0 — a cell that would need more than 5× widening drops out of the high confidence tier via confidenceTierFor. NULL when the trailing window had fewer than 5 resolved entries to learn from; the band on the row is then the raw bootstrap band, un-widened.

nullable_reason:
  Fewer than 5 resolved band-checked entries with non-null predicted bands and actual_value on this (forecast_type, horizon_days) cell, or no calibration ledger materialized for the build.


table_name: summary_channel_forecast
column_name: band_widen_k_high
type: REAL
unit: count
example_value: 1.0
definition:
  Multiplier the build applied to the raw bootstrap upper-band half-width to produce the surfaced predicted_high. Mirrors band_widen_k_low semantics for the upper side; 1.0 = no widening, capped at 5.0. The raw bootstrap upper-band half-width is reconstructible as (predicted_high - projected_value) / band_widen_k_high when this column is non-null.

nullable_reason:
  Fewer than 5 resolved band-checked entries with non-null predicted bands and actual_value on this (forecast_type, horizon_days) cell, or no calibration ledger materialized for the build.


table_name: summary_channel_forecast
column_name: coverage_below_floor_days
type: INTEGER
unit: count
nullable_reason: as_of_date had no matching summary_channel_forecast row to autotune (defensive).
example_value: 0
definition:
  Number of consecutive trailing snapshots (ending at as_of_date) where historical_band_coverage on this cell was non-null and below 0.5. Resets to 0 the first time the streak is interrupted by a non-null coverage at or above 0.5. The dashboard's usability reader demotes a cell to directional once this exceeds the chronic-miss threshold (operator-locked at 30 days), making the surface fall back gracefully on cells where band widening can't close the gap. Limited today — historical_band_coverage is back-written only for the latest snapshot, so the streak meaningfully reads as 0 or 1 on past snapshots; a follow-up will make coverage per-snapshot so the streak grows across history.


table_name: summary_channel_regime_forecast
column_name: as_of_date
type: TEXT
unit: iso_date
example_value: 2026-05-10
definition:
  Reporting cutoff the forecast was computed against — same as summary_channel.latest_data_date at build time. One row per build.


table_name: summary_channel_regime_forecast
column_name: current_state
type: TEXT
unit: enum
example_value: crowded_launch
definition:
  The v1 surface-state label classifying the channel as of as_of_date. Mirrors the latest row in event_channel_state_change so consumers can read both surfaces consistently without joining tables.

enum_values:
  dormant, seeding, cold_testing, warm_testing, suggested_run, search_tail, mixed_expansion, crowded_launch, compounding, incumbent_anchored, cooling, external_spike


table_name: summary_channel_regime_forecast
column_name: projected_state
type: TEXT
unit: enum
confidence_column: confidence
example_value: incumbent_anchored
definition:
  The modal surface-state label the joint trajectory points to inside the 60-day projection horizon. Modal across bootstrap resamples — the most common projected state among samples that transitioned. NULL when too few features are moving meaningfully or bootstrap agreement is too thin to surface a state honestly.

nullable_reason:
  NULL when confidence is 'insufficient' — no clear trajectory toward a transition in the next 60 days.

enum_values:
  dormant, seeding, cold_testing, warm_testing, suggested_run, search_tail, mixed_expansion, crowded_launch, compounding, incumbent_anchored, cooling, external_spike


table_name: summary_channel_regime_forecast
column_name: expected_days
type: INTEGER
unit: days
nullable_reason: NULL when confidence is 'insufficient'.
confidence_column: confidence
example_value: 20
definition:
  Median number of days until the projected transition, taken across bootstrap resamples that converged on the modal projected_state. Arithmetic on the trailing 21-day window's feature slopes, not a prediction; assumes recent direction and discovery mix hold.


table_name: summary_channel_regime_forecast
column_name: expected_days_low
type: INTEGER
unit: days
nullable_reason: NULL when confidence is 'insufficient'.
confidence_column: confidence
example_value: 12
definition:
  10th-percentile end of the bootstrap band of transition days within the modal projected_state — the optimistic edge of "when could the transition land."


table_name: summary_channel_regime_forecast
column_name: expected_days_high
type: INTEGER
unit: days
nullable_reason: NULL when confidence is 'insufficient'.
confidence_column: confidence
example_value: 38
definition:
  90th-percentile end of the bootstrap band of transition days within the modal projected_state — the conservative edge of "when could the transition land."


table_name: summary_channel_regime_forecast
column_name: contributing_features_json
type: TEXT
unit: json
definition:
  JSON array of (name, slope_per_day, current_value, ci_low, ci_high) tuples for each classifier-input feature whose bootstrap slope confidence interval does not straddle zero. Empty array `[]` when no feature passes the gate. Names mirror the SurfaceStateFeatures camelCase keys (e.g. `top1ImpressionShare7d`, `searchShare7d`) so a consumer can render "driven by X and Y" without joining schema-side metadata.

example_value:
  [{"name":"top1ImpressionShare7d","slope_per_day":0.01658,"current_value":0.737,"ci_low":0.00861,"ci_high":0.022815}]


table_name: summary_channel_regime_forecast
column_name: lookback_days
type: INTEGER
unit: days
example_value: 21
definition:
  Trailing window length the writer used to fit feature slopes. v1 ships with a fixed 21-day window — long enough to detect month-scale drift on the rolling-7d / rolling-28d classifier inputs, short enough that the projection responds to recent direction shifts.


table_name: summary_channel_regime_forecast
column_name: sample_size_days
type: INTEGER
unit: days
example_value: 21
definition:
  Number of rows the writer actually had inside the lookback window. Below 14 the row collapses to 'insufficient' before any arithmetic runs.


table_name: summary_channel_regime_forecast
column_name: classifier_version
type: TEXT
unit: enum
enum_values: v1
example_value: v1
definition:
  The v-prefixed version string of the surface-state classifier the writer applied. Pins compute-channel-state-log.js's CLASSIFIER_VERSION so future bumps in classifier branch order or thresholds are observable downstream — old rows keep their original classifier_version.


table_name: summary_channel_regime_forecast
column_name: confidence
type: TEXT
unit: enum
enum_values: high, medium, low, insufficient
example_value: low
definition:
  Confidence tier. `insufficient` fires when fewer than 2 features are moving meaningfully, when fewer than 50% of bootstrap resamples produced any transition inside the 60-day horizon, or when modal-state agreement among transitioning resamples is below 60%. Otherwise tier derives from the bootstrap band width relative to expected_days — `high` at <=20%, `medium` at 20-50%, `low` above 50%.


table_name: forecast_calibration_log
column_name: as_of_date
type: TEXT
unit: iso_date
example_value: 2026-05-12
definition:
  Reporting cutoff of the build that materialized this row. Every row in a single build carries the same as_of_date so queries that group by build are straightforward.


table_name: forecast_calibration_log
column_name: forecast_made_on
type: TEXT
unit: iso_date
example_value: 2026-04-28
definition:
  Reporting cutoff on which the original forecast was computed and published. Pairs with horizon_days to recover the target_date = forecast_made_on + horizon_days that the row evaluates.


table_name: forecast_calibration_log
column_name: horizon_days
type: INTEGER
unit: days
example_value: 14
definition:
  Days ahead the forecast aimed at. Mirrors summary_channel_forecast.horizon_days (7 / 14 / 30 / 90 in v1).


table_name: forecast_calibration_log
column_name: forecast_type
type: TEXT
unit: enum
enum_values: subscribers, daily_views
example_value: subscribers
definition:
  Which channel-wide quantity the original forecast projected. `subscribers` is the cumulative subscriber count; `daily_views` is the rolling 7-day mean of per-day views.


table_name: forecast_calibration_log
column_name: predicted_value
type: REAL
unit: score_dimensionless
example_value: 13.5
definition:
  Point-estimate value the original forecast projected at target_date. Units track forecast_type (subscriber count for `subscribers`, daily views for `daily_views`).

nullable_reason:
  NULL when the original forecast was 'insufficient' — no point estimate was published, so calibration carries no number to compare against.


table_name: forecast_calibration_log
column_name: predicted_low
type: REAL
unit: score_dimensionless
nullable_reason: NULL when the original forecast was 'insufficient'.
example_value: 11.0
definition:
  10th-percentile end of the original forecast's bootstrap band — paired with predicted_high to recover the published uncertainty band.


table_name: forecast_calibration_log
column_name: predicted_high
type: REAL
unit: score_dimensionless
nullable_reason: NULL when the original forecast was 'insufficient'.
example_value: 16.0
definition:
  90th-percentile end of the original forecast's bootstrap band — paired with predicted_low to recover the published uncertainty band.


table_name: forecast_calibration_log
column_name: actual_value
type: REAL
unit: score_dimensionless
nullable_reason: NULL while target_date is still in the future (entry not yet resolved).
example_value: 13.0
definition:
  Observed value at target_date — subscriber count for `subscribers`, rolling 7-day mean of per-day views for `daily_views`. Filled in once a build's latest_data_date reaches target_date.


table_name: forecast_calibration_log
column_name: error_pct
type: REAL
unit: pct_0_to_100
nullable_reason: NULL when actual_value is missing OR predicted_value was NULL (insufficient row).
example_value: 3.85
definition:
  Absolute percentage error — |actual_value - predicted_value| / |predicted_value| * 100. Reported to two decimals so calibration dashboards can sort honestly without false precision.


table_name: forecast_calibration_log
column_name: inside_band
type: INTEGER
unit: enum
nullable_reason: NULL when actual_value is missing OR the band was null (insufficient row).
enum_values: 0, 1
example_value: 1
definition:
  1 when actual_value fell inside [predicted_low, predicted_high], 0 otherwise. The single most-honest credibility signal — past inside-band rate is what calibration footers surface to readers.


table_name: forecast_decay_history
column_name: snapshot_date
type: TEXT
unit: iso_date
example_value: 2026-05-12
definition:
  Reporting cutoff of the build that captured this row. Mirrors summary_channel.latest_data_date for the build.


table_name: forecast_decay_history
column_name: model_type
type: TEXT
unit: enum
nullable_reason: NULL when the underlying fit was missing — preserved as a placeholder row.
enum_values: exponential, log_linear, rising, flat
confidence_column: goodness_of_fit_r2
example_value: exponential
definition:
  Decay-shape classification for this snapshot's fit. Mirrors summary_video_decay_model.model_type at the time of the snapshot.


table_name: forecast_decay_history
column_name: decay_rate_per_day
type: REAL
unit: score_dimensionless
nullable_reason: NULL when the fit failed (fewer than 14 post-day-3 rows or zero variance in age).
confidence_column: goodness_of_fit_r2
example_value: -0.04
definition:
  OLS slope for log(views + 1) over post-day-3 daily rows, in log-per-day units. Negative when the video is fading, positive when it is climbing. Snapshot of summary_video_decay_model.decay_rate_per_day at snapshot_date.


table_name: forecast_decay_history
column_name: half_life_days
type: INTEGER
unit: days
confidence_column: goodness_of_fit_r2
example_value: 17
definition:
  Days for projected views to halve at this snapshot's decay rate. Mirrors summary_video_decay_model.half_life_days at snapshot_date.

nullable_reason:
  NULL when model_type is not 'exponential' — half-life is only meaningful for the falling-exponential branch.


table_name: forecast_decay_history
column_name: predicted_quiet_date
type: TEXT
unit: iso_date
confidence_column: goodness_of_fit_r2
example_value: 2026-06-15
definition:
  First calendar date the fitted curve falls below five views per day at this snapshot. Comparing across two snapshot_date rows lets a consumer name how far the projected quiet date has moved between builds.

nullable_reason:
  NULL when the model does not project a sub-5 day inside its 5-year horizon, OR when the underlying fit was missing.


table_name: forecast_decay_history
column_name: goodness_of_fit_r2
type: REAL
unit: share_0_to_1
nullable_reason: NULL when the fit failed.
example_value: 0.84
definition:
  Coefficient of determination on this snapshot's fit. Higher means the curve explains more of the per-day view variance and the projection deserves more weight.


table_name: forecast_decay_history
column_name: fit_age_days
type: INTEGER
unit: days
nullable_reason: NULL when the fit failed.
example_value: 14
definition:
  Video age at which the snapshot's fit was computed. Pairs with snapshot_date so a consumer can recover (publish_date, age) without joining video.


table_name: summary_video_launch_context
column_name: channel_day7_median_at_publish
type: REAL
unit: count
nullable_reason: NULL on the first video where there are no priors.
definition:
  Channel's median day-7 views across earlier videos at this point in time. Useful as a baseline for video_day7_views.


table_name: summary_video_launch_context
column_name: prior_video_id
type: TEXT
unit: video_id_anon
nullable_reason: NULL on the first video.
example_value: 1Hw_e3Aev3M
definition:
  Real YouTube video ID (11-char) of the prior published video on this channel. Useful for chaining context across launches.


table_name: summary_video_launch_context
column_name: video_day7_views
type: INTEGER
unit: count
nullable_reason: NULL on videos that haven't reached day 7.
example_value: 22
definition:
  This video's day-7 views. Sourced from summary_video.day7_views; mirrored here for self-contained launch-context queries.


table_name: summary_video_launch_context
column_name: context_label
type: TEXT
unit: enum
definition: Short categorical summary of the launch context the video debuted into.
example_value: launched_in_typical
enum_values:
  launched_in_typical, launched_in_weak_phase, launched_in_strong_phase, launched_in_crowded, launched_in_drought, insufficient_history


table_name: summary_video_nearest_peers
column_name: shared_age_days
type: INTEGER
unit: days
example_value: 15
definition:
  Number of overlapping days_since_publish on which the video and its peer set both have data. Sample-size gate for the similarity score.


table_name: summary_video_nearest_peers
column_name: peers_qualified
type: INTEGER
unit: count
example_value: 17
definition:
  Number of peer candidates that met the minimum sample gate. Reads as 0–5; lower values mean fewer peer columns are populated.


table_name: summary_video_trajectory_cone
column_name: peer_count
type: INTEGER
unit: count
example_value: 30
definition:
  Number of peer videos that contributed to the percentile band at this age. Useful as a confidence gate.


table_name: v_latest_channel_state
column_name: videos_in_debut
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 0
definition:
  Public videos in their 'debut' lifecycle stage on this date (days_since_publish 0..2). Mirrored from summary_channel_metrics_daily.


table_name: v_latest_channel_state
column_name: videos_in_growth
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 3
definition:
  Public videos in their 'growth' lifecycle stage on this date. Mirrored from summary_channel_metrics_daily.


table_name: v_latest_channel_state
column_name: videos_in_peak
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 2
definition:
  Public videos in their 'peak' lifecycle stage on this date. Mirrored from summary_channel_metrics_daily.


table_name: v_latest_channel_state
column_name: videos_in_tail
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 5
definition:
  Public videos in their 'tail' lifecycle stage on this date. Mirrored from summary_channel_metrics_daily.


table_name: v_latest_channel_state
column_name: videos_in_quiet
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 3
definition:
  Public videos in the 'quiet' lifecycle stage on this date — receiving few or zero recent views. Mirrored from summary_channel_metrics_daily.


table_name: v_latest_channel_state
column_name: videos_in_re_emerged
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 0
definition:
  Public videos in the 're_emerged' lifecycle stage on this date — previously quiet videos with a renewed view burst. Mirrored from summary_channel_metrics_daily.


table_name: v_latest_channel_state
column_name: videos_in_zombie
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 2
definition:
  Public videos in the 'zombie' lifecycle stage on this date — long-tail videos still receiving sporadic views. Mirrored from summary_channel_metrics_daily.


table_name: v_latest_channel_state
column_name: videos_in_dormant
type: INTEGER
unit: count
nullable_reason: NULL on dates where the stage classifier produced no rows.
example_value: 0
definition:
  Public videos in the 'dormant' lifecycle stage on this date. Mirrored from summary_channel_metrics_daily.


table_name: summary_channel_threshold_impression_pattern
column_name: snapshot_date
type: TEXT
unit: iso_date
example_value: 2026-05-13
definition:
  Reporting cutoff this row was computed against. One row per snapshot_date — the channel-pattern is a snapshot, not a time series.


table_name: summary_channel_threshold_impression_pattern
column_name: ctr_jump_event_count
type: INTEGER
unit: count
example_value: 6
definition:
  Number of distinct leading-edge click-rate jump events seen across the channel's videos up to this snapshot — using the same per-video threshold (>= 2 percentage-point lift on a trailing-7d weighted CTR vs the prior 7d, at age >= 14d, with at least 200 impressions in each window) as the `late-life-ctr-jump` detector. Sustained windows count once at the day they first trigger.


table_name: summary_channel_threshold_impression_pattern
column_name: ctr_jump_followed_count
type: INTEGER
unit: count
example_value: 4
definition:
  Of the `ctr_jump_event_count` events, the number where mean impressions per day across the next 7 days came in at least 20% above the prior 7 days. Observation about what happened — not a causal claim.


table_name: summary_channel_threshold_impression_pattern
column_name: avd_jump_event_count
type: INTEGER
unit: count
example_value: 3
definition:
  Number of distinct leading-edge average-view-duration jump events seen across the channel's videos up to this snapshot — mirror of `ctr_jump_event_count` on the AVD axis, using the same threshold as the `late-life-avd-jump` detector (>= 15% relative lift on a trailing-7d weighted AVD vs the prior 7d, at age >= 14d, with at least 30 views in each window).


table_name: summary_channel_threshold_impression_pattern
column_name: avd_jump_followed_count
type: INTEGER
unit: count
example_value: 1
definition:
  Of the `avd_jump_event_count` events, the number where mean impressions per day across the next 7 days came in at least 20% above the prior 7 days. Observation about what happened — not a causal claim.


table_name: summary_channel_threshold_impression_pattern
column_name: ctr_min_pp_delta
type: REAL
unit: pct_0_to_100
example_value: 2
definition:
  Minimum percentage-point lift (recent-7d weighted CTR minus prior-7d weighted CTR, both in percentage points) required to register a CTR jump event. Mirrors the per-video detector's `MIN_PP_JUMP`.


table_name: summary_channel_threshold_impression_pattern
column_name: avd_min_rel_delta
type: REAL
unit: share_0_to_1
example_value: 0.15
definition:
  Minimum relative lift (recent-7d weighted AVD divided by prior-7d weighted AVD minus 1) required to register an AVD jump event. Mirrors the per-video detector's `MIN_REL_JUMP`.


table_name: summary_channel_threshold_impression_pattern
column_name: followup_window_days
type: INTEGER
unit: days
example_value: 7
definition:
  Number of days after a jump that is evaluated for the impression follow-up. Held constant at 7 so the per-video card and channel-pattern row share the same definition of "after".


table_name: summary_channel_threshold_impression_pattern
column_name: followup_impression_lift_threshold
type: REAL
unit: share_0_to_1
example_value: 0.2
definition:
  Minimum relative lift in mean impressions-per-day across the follow-up window (vs the prior 7 days) for the follow-up to count as "impressions stepped up". Stored as a share — 0.20 means +20%.


table_name: summary_channel_threshold_impression_pattern
column_name: videos_evaluated
type: INTEGER
unit: count
example_value: 18
definition:
  Number of videos on the channel that had enough daily history (>= 2 windows + 1 follow-up window past the 14-day age gate) to be walked for jump events at this snapshot. When this is small, the followed-rate is a sample-of-one observation and the per-video card should hedge accordingly.


table_name: summary_channel_threshold_impression_pattern
column_name: computed_at
type: TEXT
unit: iso_datetime
example_value: 2026-05-13T00:00:00Z
definition:
  Build-time ISO timestamp the pattern row was computed at. Defaults to the snapshot_date at midnight UTC for byte-deterministic builds.


# _json_schema

table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: insight
json_keys: video_id, source, views
example_value: {"video_id":"v008","source":"Browse / Homepage","views":38}
description:
  Detector-fired insight surfaced for the Moment chapter. Shape varies by detector — see app_insight_snapshot.evidence_json for finer-grained per-detector shapes.


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: rank_change
json_keys: kind, prior_leader, new_leader, prior_value, new_value, prior_label, new_label
description: Lead shift between videos or sources for the named metric.
example_value:
  {"kind":"video_lead","prior_leader":"v001","new_leader":"v003","prior_value":11,"new_value":41,"prior_label":"Video 1","new_label":"Video 3"}


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: first_external
json_keys: video_id, first_external_date, views_that_day, share_at_first
example_value: {"video_id":"v003","first_external_date":"2026-04-11","views_that_day":1}
description:
  First day a video received External source traffic. share_at_first (the fraction of that day's views from External) may be absent on older rows.


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: note
json_keys: kind, scope, video_ids, fields, note_kind
example_value: {"kind":"manual","scope":"video","video_ids":["v003"],"fields":null,"note_kind":"expectation"}
description:
  Hand-authored creator note loaded from pipeline/channel-notes.yaml. Optional file. Untyped notes carry no evidence_json (NULL); the key set above appears only on a tense-tagged note. note_kind is one of expectation (a forward-looking read dated at/before the moment it precedes), change-rationale (the stated reason a change was made, no causal claim), or retrospective (a past-tense look-back recorded later). An expectation may name a video_id and is then anti-retrofit-checked at build time — its date must be at or before that video's publish.


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: stage_transition
json_keys:
  video_id, old_stage, new_stage, days_in_old_stage, magnitude, views_3d_before, views_3d_after, views_rate_change_pct

example_value:
  {"video_id":"v007","old_stage":"peak","new_stage":"tail","days_in_old_stage":12,"magnitude":34,"views_3d_before":420,"views_3d_after":180,"views_rate_change_pct":-57.1}

description:
  Lifecycle stage transition for a video. Phase 6.4 extends the older 4-key shape with magnitude + 3-day-window evidence; older snapshots may carry only the first three keys.


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: era_start
json_keys: video_id, era_index, era_start_date, prior_era_index, fields_changed
description: Title/thumbnail era boundary for a video (Phase 6.1).
example_value:
  {"video_id":"v003","era_index":2,"era_start_date":"2026-03-15","prior_era_index":1,"fields_changed":["title","thumbnail"]}


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: source_quiet
json_keys: video_id, source_id, source_name, quiet_started, prior_view_share
description: A previously-active traffic source went quiet for a video (Phase 6.3).
example_value:
  {"video_id":"v003","source_id":3,"source_name":"External","quiet_started":"2026-04-22","prior_view_share":0.12}


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: creator_event
json_keys: kind, scope, end_date, video_ids, fields, note_kind
example_value:
  {"kind":"republish","scope":"video","end_date":null,"video_ids":["v003"],"fields":["title"],"note_kind":"change-rationale"}

description:
  Owner-authored timeline annotation from pipeline/creator-events.yaml. note_kind is appended only on a tense-tagged event (untyped events omit the key, staying byte-identical to pre-tagging builds) and is DISTINCT from the `kind` slug: `kind` names the beat (republish, schedule_change, …) while note_kind names its tense — expectation (forward-looking, dated at/before the moment), change-rationale (the stated reason for a change, no causal claim), or retrospective (a past-tense look-back over a window). An expectation that names a video is anti-retrofit-checked at build time.


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: multi_video_activation
json_keys: videos
description: Multiple videos surged on the same day. `videos` is an array of per-video evidence rows.
example_value:
  {"videos":[{"video_id":"v008","lift_x":23.4,"today_views":39},{"video_id":"v004","lift_x":10.5,"today_views":9}]}


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: country_first_view
json_keys: country_code
example_value: {"country_code":"CA"}
description:
  Channel received its first view from this country on this date. (Alias of first_country_view; both event_type strings appear in older builds.)


table_name: summary_channel
column_name: publish_gap_distribution
json_keys: (histogram object; keys are gap-in-days strings, values are counts)
example_value: {"1":31}
description:
  Histogram of publish-gap-in-days across the catalog. Keys are gap values (as strings); values are video counts.


table_name: app_insight_snapshot
column_name: evidence_json
variant_key: detector_name
json_keys: (varies by detector — one row per detector slot)
example_value:
  {"video_id":"v007","metric":"weighted_ctr","value":0.083,"channel_median":0.045,"z_score":2.3,"sample_size":1400}

description:
  Per-detector evidence payload. Shape is the detector's `Evidence` record from dashboard/src/lib/insights/types.ts — `Record<string, number | string | null>`, booleans converted to 0/1. Detectors that ship after Phase 4 land their per-detector schema here as they appear in production builds.


table_name: app_dashboard_briefing_daily
column_name: evidence_json
variant_key: signal_id
json_keys: (varies by signal id)
example_value: {"top_video_id":"v003","top_video_share_7d":0.4398,"top3_video_share_7d":0.8855,"views_7d":166}
description:
  Per-signal evidence used to render the homepage briefing card. Shape varies by signal_id; example shown is the catalog-concentration variant.


table_name: app_wisdom_canon
column_name: default_thresholds_json
example_value: {"agrees_below": 0.7, "disagrees_above": 1.0, "min_pairs": 10}
json_keys:
  (varies per belief — typically agrees_below/disagrees_above/min_pairs or agrees_above/disagrees_below/min_count)

description:
  Default threshold object for the wisdom test. Shape varies per belief — the union of keys across the canon is documented in calculation_doc.


table_name: app_wisdom_test
column_name: evidence_json
variant_key: test_id
json_keys: (varies per belief — typically counts, medians, and the thresholds used)
example_value:
  {"low_count":0,"mid_count":0,"high_count":29,"low_median":null,"mid_median":null,"high_median":0.3,"threshold_high_above":0.7,"threshold_low_below":0.4}

description:
  Per-belief evidence for the current verdict. May also carry `missing_data` + `unblock_action` keys when the verdict is `not_currently_testable` (e.g. cross-channel beliefs at Stage 1).


table_name: app_wisdom_verdict_daily
column_name: evidence_json
variant_key: test_id
json_keys: (varies per belief — same family as app_wisdom_test.evidence_json)
example_value: {"eligible_pairs":0,"min_required":10}
description:
  Per-belief evidence for the verdict on this snapshot date. Same shape family as app_wisdom_test.evidence_json; this table carries one row per (test_id, snapshot_date) for historical verdict tracking.


table_name: app_channel_weekly_letter
column_name: evidence_json
json_keys: metrics, state_close, prior_state_close
description: Per-letter context: weekly metric rollup plus channel-state snapshot at week boundary.
example_value:
  {"metrics":{"week_views":186,"week_watch_minutes":425.5,"week_subs_net":2,"week_impressions":5990,"videos_published":7,"prior_week_views":116},"state_close":{"surface_state":"sprouting","reason_text":"channel hit 50 videos"}}


table_name: video_current_state
column_name: evidence_json
description: Per-video state snapshot used by the per-video page for current-state pills.
json_keys:
  days_since_publish, views_to_date, impressions_to_date, views_7d, impressions_7d, day7_views_to_date, late_views_to_date

example_value:
  {"days_since_publish":0,"views_to_date":9,"impressions_to_date":208,"views_7d":9,"impressions_7d":208,"day7_views_to_date":9,"late_views_to_date":0}


table_name: summary_channel_regime_forecast
column_name: contributing_features_json
json_keys: (array of objects with: name, slope_per_day, current_value, ci_low, ci_high)
example_value:
  [{"name":"top1ImpressionShare7d","slope_per_day":0.01658,"current_value":0.737,"ci_low":0.00861,"ci_high":0.022815}]

description:
  Array of classifier-input features whose bootstrap slope CI did not straddle zero. `name` matches the camelCase keys on the SurfaceStateFeatures classifier input; `slope_per_day` is the OLS slope on the trailing window (units track the underlying feature); `current_value` is the most-recent feature observation; `ci_low` / `ci_high` are the 10th / 90th percentiles of the bootstrap slope distribution. Empty array when no feature passed the gate (the row also collapses to confidence='insufficient' in that case).


# _query_examples

category: channel_overview
question: What is the channel's current snapshot (subscribers, total views, video count, latest date)?
difficulty: simple
sql:
  SELECT current_subscribers, total_views, total_public_videos, latest_data_date
  FROM summary_channel
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_channel);

notes:
  Filters to the latest snapshot (V64 / Backfill Pass Phase 2 introduces snapshot_date on summary_channel; the current view is always MAX(snapshot_date)).


category: channel_overview
question: What were the channel's daily totals over the last 30 days?
difficulty: simple
notes: Filters on the reporting cutoff so the latest row isn't a partial day.
sql:
  SELECT date, total_views, total_impressions, weighted_ctr, subs_net
  FROM summary_channel_metrics_daily
  WHERE date >= date((SELECT value FROM _about WHERE key = 'reporting_cutoff'), '-30 days')
    AND date <= (SELECT value FROM _about WHERE key = 'reporting_cutoff')
  ORDER BY date;


category: channel_overview
question: What's the conversion funnel from impressions to subscribers gained over the past 30 days?
difficulty: moderate
notes: Drop-off ratios are stored 0..1 so a 5% conversion appears as 0.05.
sql:
  SELECT
    SUM(total_impressions) AS impressions,
    SUM(total_views)       AS views,
    SUM(subs_gained)       AS subs_gained,
    CAST(SUM(total_views) AS REAL) / NULLIF(SUM(total_impressions), 0) AS imp_to_view,
    CAST(SUM(subs_gained) AS REAL) / NULLIF(SUM(total_views), 0)       AS view_to_sub
  FROM summary_channel_metrics_daily
  WHERE date >= date((SELECT value FROM _about WHERE key = 'reporting_cutoff'), '-30 days');


category: video_catalog
question: What are the top 10 videos by lifetime views?
difficulty: simple
sql:
  SELECT video_id, total_views, total_watch_minutes, weighted_ctr, avg_retention_pct
  FROM summary_video
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
  ORDER BY total_views DESC
  LIMIT 10;


category: video_catalog
difficulty: moderate
question:
  Which videos have a CTR in the top quartile but retention in the bottom quartile (the 'clicked-not-watched' quadrant)?

sql:
  SELECT video_id, weighted_ctr, avg_retention_pct, ctr_confidence
  FROM summary_video
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
    AND ctr_quadrant = 'clicked-not-watched'
    AND ctr_confidence IN ('medium', 'high')
  ORDER BY weighted_ctr DESC
  LIMIT 10;

notes:
  Returns 0 on small catalogs where no video meets the quadrant + confidence gates. Filtered to medium/high so noise-tier CTRs don't distort the answer.


category: video_catalog
question: What are the 10 newest videos with their day-7 view counts?
difficulty: simple
sql:
  SELECT video_id, published_at, day7_views, day7_ctr, ctr_confidence
  FROM summary_video
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
  ORDER BY published_at DESC
  LIMIT 10;


category: video_catalog
question: Which videos crossed the day-1000-views milestone, and how long did it take?
difficulty: moderate
sql:
  SELECT video_id, days_to_1000_views, total_views, published_at
  FROM summary_video
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
    AND days_to_1000_views IS NOT NULL
  ORDER BY days_to_1000_views ASC;

notes:
  Returns 0 on small channels that haven't hit 1000 views on any video. Larger milestones (10k, 100k) require a cumulative-sum query against video_daily.


category: traffic
question: What are the top 5 traffic sources by lifetime views?
difficulty: simple
sql:
  SELECT ts.display_name, sct.total_views, sct.total_watch_minutes, sct.weighted_ctr
  FROM summary_channel_traffic sct
  JOIN traffic_source ts ON sct.traffic_source_id = ts.id
  WHERE sct.snapshot_date = (SELECT MAX(snapshot_date) FROM summary_channel_traffic)
  ORDER BY sct.total_views DESC
  LIMIT 5;


category: traffic
question: How much watch-time-per-impression does each traffic source typically deliver?
difficulty: moderate
sql:
  SELECT
    ts.display_name,
    sct.total_views,
    sct.total_impressions,
    CAST(sct.total_watch_minutes AS REAL) / NULLIF(sct.total_impressions, 0) AS watch_min_per_imp
  FROM summary_channel_traffic sct
  JOIN traffic_source ts ON sct.traffic_source_id = ts.id
  WHERE sct.snapshot_date = (SELECT MAX(snapshot_date) FROM summary_channel_traffic)
    AND sct.total_impressions > 0
  ORDER BY watch_min_per_imp DESC;


category: traffic
question: What was the channel's source mix as a percentage of total over the last 7 days?
difficulty: moderate
sql:
  WITH window AS (
    SELECT date FROM summary_channel_metrics_daily
    WHERE date >= date((SELECT value FROM _about WHERE key = 'reporting_cutoff'), '-7 days')
      AND date <= (SELECT value FROM _about WHERE key = 'reporting_cutoff')
  ),
  totals AS (
    SELECT std.traffic_source_id, SUM(std.total_views) AS views
    FROM summary_traffic_source_daily std
    WHERE std.date IN (SELECT date FROM window)
    GROUP BY std.traffic_source_id
  )
  SELECT ts.display_name, t.views,
         CAST(t.views AS REAL) / NULLIF(SUM(t.views) OVER (), 0) AS share
  FROM totals t
  JOIN traffic_source ts ON t.traffic_source_id = ts.id
  ORDER BY t.views DESC;


category: geographic
question: What are the top 10 countries by lifetime views?
difficulty: simple
sql:
  SELECT country_code,
         SUM(views) AS total_views,
         SUM(watch_time_minutes) AS total_watch_minutes
  FROM summary_channel_country_daily
  GROUP BY country_code
  ORDER BY total_views DESC
  LIMIT 10;


category: geographic
difficulty: moderate
question:
  Which countries showed a rising share of the channel's trailing-7-day audience compared to their trailing-28-day share?

sql:
  WITH cutoff AS (SELECT value AS d FROM _about WHERE key = 'reporting_cutoff'),
  win_7 AS (
    SELECT country_code, SUM(views) AS views_7d
    FROM summary_channel_country_daily, cutoff
    WHERE date >= date(cutoff.d, '-7 days') AND date <= cutoff.d
    GROUP BY country_code
  ),
  win_28 AS (
    SELECT country_code, SUM(views) AS views_28d
    FROM summary_channel_country_daily, cutoff
    WHERE date >= date(cutoff.d, '-28 days') AND date <= cutoff.d
    GROUP BY country_code
  )
  SELECT w28.country_code,
         COALESCE(w7.views_7d, 0)   AS views_7d,
         w28.views_28d,
         CAST(COALESCE(w7.views_7d, 0) AS REAL) / NULLIF(w28.views_28d, 0) AS share_7_of_28
  FROM win_28 w28
  LEFT JOIN win_7 w7 USING (country_code)
  ORDER BY share_7_of_28 DESC NULLS LAST
  LIMIT 10;

notes:
  Country share isn't time-weighted — countries with all views in the past week show share_7_of_28 close to 1.0. The full cohort-stability scoring lives in summary_channel_country_cohort_stability (Phase 3.8).


category: events
question: What channel events happened in the last 14 days?
difficulty: simple
sql:
  SELECT date, event_type, video_id, headline, significance
  FROM channel_timeline_event
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM channel_timeline_event)
    AND date >= date((SELECT value FROM _about WHERE key = 'reporting_cutoff'), '-14 days')
  ORDER BY date DESC, significance DESC NULLS LAST;


category: events
question: Which videos had the biggest lifecycle stage transitions this week?
difficulty: moderate
sql:
  SELECT
    date,
    video_id,
    json_extract(evidence_json, '$.old_stage')  AS old_stage,
    json_extract(evidence_json, '$.new_stage')  AS new_stage,
    json_extract(evidence_json, '$.magnitude')  AS magnitude
  FROM channel_timeline_event
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM channel_timeline_event)
    AND event_type = 'stage_transition'
    AND date >= date((SELECT value FROM _about WHERE key = 'reporting_cutoff'), '-7 days')
  ORDER BY CAST(json_extract(evidence_json, '$.magnitude') AS REAL) DESC
  LIMIT 10;

notes:
  Returns 0 on weeks where no video transitioned. Older stage_transition rows may have NULL magnitude — Phase 6.4 enriched the evidence.


category: events
question: What was the first date each external source delivered traffic to a video?
difficulty: simple
sql:
  SELECT video_id, traffic_source_name, first_active_date
  FROM event_source_first_view
  ORDER BY first_active_date DESC
  LIMIT 20;


category: lifecycle
question: How are the channel's videos currently distributed across lifecycle stages?
difficulty: simple
sql:
  SELECT lifecycle_stage, COUNT(*) AS videos
  FROM summary_video
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
    AND lifecycle_stage IS NOT NULL
  GROUP BY lifecycle_stage
  ORDER BY videos DESC;


category: lifecycle
question: Which videos appear to be entering a decline phase based on their tail half-life?
difficulty: advanced
sql:
  SELECT video_id, lifecycle_stage, tail_half_life_days, days_in_tail, total_views
  FROM summary_video
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
    AND lifecycle_stage IN ('tail', 'decay')
    AND tail_half_life_days IS NOT NULL
  ORDER BY tail_half_life_days ASC
  LIMIT 10;

notes:
  Returns 0 on early channels where no video has reached the tail phase. Full decay-projection lands in Phase 3.6 / 4.7.


category: lifecycle
question: Week-over-week — how did the most recent publish cohort perform vs the prior week's cohort?
difficulty: advanced
sql:
  WITH cohorts AS (
    SELECT strftime('%Y-%W', published_at) AS cohort,
           COUNT(*) AS videos,
           SUM(day7_views) AS sum_day7_views,
           AVG(day7_views) AS avg_day7_views
    FROM summary_video
    WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
      AND published_at IS NOT NULL
    GROUP BY cohort
  )
  SELECT cohort, videos, sum_day7_views, avg_day7_views
  FROM cohorts
  ORDER BY cohort DESC
  LIMIT 8;

notes:
  Returns 0 on early channels with < 8 publish weeks. Phase 3.5 ships a richer summary_channel_cohort table.


category: wisdom
question: Which growth beliefs does the current snapshot say agree with this channel's data?
difficulty: simple
notes: Joins canon (prose belief) with the latest test verdict.
sql:
  SELECT wc.test_id, wc.belief_text, wt.status, wt.confidence, wt.sample_size
  FROM app_wisdom_canon wc
  JOIN app_wisdom_test wt USING (test_id)
  WHERE wt.snapshot_date = (SELECT MAX(snapshot_date) FROM app_wisdom_test)
    AND wt.status = 'agrees';


category: wisdom
question: Which beliefs flipped status today vs the prior snapshot?
difficulty: moderate
notes: Returns 0 on quiet days — beliefs flip rarely.
sql:
  SELECT test_id, prior_status, status AS current_status, snapshot_date
  FROM app_wisdom_test
  WHERE status_changed_today = 1
    AND snapshot_date = (SELECT MAX(snapshot_date) FROM app_wisdom_test);


category: data_quality
question: How many pre-publish stub rows exist in each fact table?
difficulty: simple
sql:
  SELECT 'video_daily'   AS tbl, COUNT(*) AS stub_rows FROM video_daily   WHERE pre_publish_stub = 1
  UNION ALL
  SELECT 'traffic_daily' AS tbl, COUNT(*) AS stub_rows FROM traffic_daily WHERE pre_publish_stub = 1;

notes:
  Stub rows pad each video's record back ~7 days before publish so the dashboard's pre-publish lead-in chart works. Filter them out for all analytics — or use video_daily_clean / traffic_daily_clean.


category: data_quality
question: Which videos have NULL weighted_ctr, and what's the apparent reason?
difficulty: moderate
sql:
  SELECT video_id, total_impressions, total_views, ctr_confidence
  FROM summary_video
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video)
    AND weighted_ctr IS NULL
  ORDER BY total_views DESC;

notes:
  A healthy build typically has no rows; NULL CTR usually indicates impressions = 0 (e.g. pre-reporting-cutoff videos).


category: data_quality
question: What share of impressions over time were unattributed to a traffic source?
difficulty: moderate
notes: A high or rising unattributed share suggests the per-source breakdown is incomplete for that date.
sql:
  SELECT
    date,
    total_impressions,
    unattributed_channel_views,
    CAST(unattributed_channel_views AS REAL) / NULLIF(total_impressions, 0) AS unattributed_share
  FROM summary_channel_metrics_daily
  WHERE total_impressions > 0
  ORDER BY date DESC
  LIMIT 30;


category: lifecycle
question: Which video is the nearest peer to v001 by trajectory similarity?
difficulty: advanced
sql:
  SELECT peer_1_id, peer_1_distance, shared_age_days, peers_qualified
  FROM summary_video_nearest_peers
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_video_nearest_peers)
    AND video_id = 'v001';

notes:
  Returns 0 when v001 has < ~5 post-publish rows or the catalog has < 5 peers. Table stores up to 5 peers in wide format (peer_1_id..peer_5_id with matching distance columns). Created in Phase 3.2.


category: video_catalog
question: How did v001's daily CTR rank shift over its first 14 days?
difficulty: moderate
notes: Returns 0 if v001 has no rankable days. Table created in Phase 3.3.
sql:
  SELECT date, ctr_rank, views_rank, peer_count
  FROM summary_video_daily_rank
  WHERE video_id = 'v001'
  ORDER BY date
  LIMIT 14;


category: traffic
difficulty: advanced
question:
  How has the channel's source-mix concentration shifted over the last 14 days, and what pattern does the leading-indicator label suggest?

sql:
  SELECT date, source_hhi_14d, top1_source_share_14d, hhi_change_14d, pattern_label
  FROM summary_channel_source_leading_indicator
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_channel_source_leading_indicator)
  ORDER BY date DESC
  LIMIT 14;

notes:
  Channel-grain (one row per date, not per source). `pattern_label` summarizes whether the HHI trend often precedes a subs response in this channel's history. Table created in Phase 3.7.


category: lifecycle
difficulty: advanced
question:
  Across this channel's videos, how often has a CTR jump on an older video been followed by a step-up in impressions within 7 days?

sql:
  SELECT
    ctr_jump_event_count,
    ctr_jump_followed_count,
    avd_jump_event_count,
    avd_jump_followed_count,
    videos_evaluated
  FROM summary_channel_threshold_impression_pattern
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_channel_threshold_impression_pattern);

notes:
  Channel-pattern memory. The followed-rate `ctr_jump_followed_count / ctr_jump_event_count` is an observation across this channel's history at the snapshot — not a base rate. When `videos_evaluated` is small, hedge accordingly.


category: lifecycle
difficulty: advanced
question:
  On the latest snapshot, which (horizon, forecast_type) cells did the build widen the most beyond the raw bootstrap band?

sql:
  SELECT
    horizon_days,
    forecast_type,
    projected_value,
    projected_low,
    projected_high,
    band_widen_k_low,
    band_widen_k_high,
    confidence
  FROM summary_channel_forecast
  WHERE as_of_date = (SELECT MAX(as_of_date) FROM summary_channel_forecast)
    AND band_widen_k_high IS NOT NULL
  ORDER BY band_widen_k_high DESC;

notes:
  Auto-tune widens the band when past forecasts on this cell landed outside the band more often than they should have. A high `band_widen_k_high` (closer to the 5× cap) flags a cell whose raw bootstrap band under-covered the most. NULL k columns mean the trailing window had fewer than 5 resolved entries to learn from — the raw band ships unchanged.


category: channel_overview
difficulty: moderate
question:
  What is the channel's consolidated 'today's read' on the latest date — view/sub/impression trends, the top-mover video, the active/quiet split, and the day's lead signal?

sql:
  SELECT
    snapshot_date,
    views_trend,
    subs_trend,
    impressions_trend,
    top_mover_video_id,
    top_mover_share,
    active_video_count,
    quiet_video_count,
    forecast_usability,
    top_signal_family,
    insight_count,
    thin_read
  FROM owner_read_daily
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM owner_read_daily);

notes:
  One consolidated synthesis row per snapshot_date. Filters to the current view via MAX(snapshot_date). Returns zero rows on a build that hasn't run the snapshot generator (the table is populated by dashboard/scripts/generate-snapshots.ts, not the bare build-public-db.js step). `thin_read = 1` marks a day too quiet to read as anything but quiet — an honest read, never a shortfall.


category: channel_overview
difficulty: moderate
question:
  What are the channel's narrative arcs on the latest snapshot — the multi-week movements the story scroll is built around?

sql:
  SELECT
    arc_index,
    arc_start_date,
    arc_end_date,
    arc_kind,
    span_days,
    view_trend_label,
    dominant_motion,
    is_open,
    headline
  FROM summary_channel_narrative_arc
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_channel_narrative_arc)
  ORDER BY arc_index;

notes:
  One row per arc at the current snapshot, earliest first. Observational segmentation of the channel's own life — never a ranking. A flat / quiet channel honestly returns a single `steady` arc spanning the whole history; a channel that has shifted shape returns a few. The last arc (is_open = 1) is the current / unfinished movement.


category: channel_overview
difficulty: moderate
question:
  On the latest snapshot, which narrative arc is the open / current one, and what shape does it carry?

sql:
  SELECT
    arc_index,
    arc_start_date,
    arc_end_date,
    arc_kind,
    view_trend_label,
    dominant_motion,
    event_density,
    headline
  FROM summary_channel_narrative_arc
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM summary_channel_narrative_arc)
    AND is_open = 1;

notes:
  Exactly one open arc per snapshot — the movement the channel is in right now (the 'not yet recorded' frame). arc_kind is the neutral `current` descriptor; event_density is context for its texture, not a score.


category: channel_overview
difficulty: simple
question:
  On the latest snapshot, which shared-pool aggregates are shown for this channel's cohort cell, and which stay silent?

sql:
  SELECT metric, cell_key, gate_state, rung, member_band,
         cell_median, cell_p25, cell_p75
  FROM app_pool_cell_stat
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM app_pool_cell_stat)
  ORDER BY metric;

notes:
  Legitimately empty when the build ran without a pool-input extract (fixtures, local builds, channels outside the shared pool) — emptiness and `withheld` are honest states, not errors. A withheld row carries NULL in every value column; `member_band` is a floored band (exact member counts are never stored).


category: channel_overview
question: Which channels share their numbers in the open pool at the latest snapshot, side by side?
difficulty: simple
sql:
  SELECT peer_handle, peer_title, total_views, total_subscribers,
         video_count, views_28d, uploads_28d, video_day7_views_median
  FROM app_pool_named_peer
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM app_pool_named_peer)
  ORDER BY peer_contributor_id;

notes:
  Legitimately empty without a pool-input extract. Every value is public-dashboard-class (a fact the peer's own public page already shows); the row set is identical in every channel's DB and reflects CURRENT consent — channels that left the pool vanish from every snapshot on the next build.


