# summary_channel_regime_forecast

as_of_date: 2026-07-27
current_state: cold_testing
classifier_version: v2
confidence: insufficient
contributing_features_json:
  [{"name":"dailyImpressions","slope_per_day":51.461039,"current_value":308,"ci_low":4.66586,"ci_high":98.035208},{"name":"activeVideos7d","slope_per_day":0.187013,"current_value":25,"ci_low":0.171222,"ci_high":0.201478},{"name":"videosPublished14d","slope_per_day":-0.184416,"current_value":3,"ci_low":-0.219068,"ci_high":-0.163062},{"name":"suggestedShare7d","slope_per_day":-0.036392,"current_value":0.2,"ci_low":-0.042736,"ci_high":-0.03077},{"name":"suggestedShare28d","slope_per_day":-0.031909,"current_value":0.1708,"ci_low":-0.036398,"ci_high":-0.02913},{"name":"top1ImpressionShare7d","slope_per_day":0.031409,"current_value":0.6057,"ci_low":0.026683,"ci_high":0.038927},{"name":"launchPressureIndex","slope_per_day":0.027051,"current_value":0.8527,"ci_low":0.019433,"ci_high":0.034487},{"name":"diversityScore","slope_per_day":-0.016562,"current_value":0.4579,"ci_low":-0.021786,"ci_high":-0.011798},{"name":"newVideoImpressionShare7d","slope_per_day":0.016199,"current_value":0.6025,"ci_low":0.011255,"ci_high":0.021076},{"name":"maxSourceShare7d","slope_per_day":0.015096,"current_value":0.707,"ci_low":0.011233,"ci_high":0.019945},{"name":"viewsSlope7d","slope_per_day":-0.01338,"current_value":-0.44651,"ci_low":-0.023904,"ci_high":-0.000167},{"name":"viewsSlope28d","slope_per_day":0.006418,"current_value":0.06376,"ci_low":0.005122,"ci_high":0.008101},{"name":"topCountryShare7d","slope_per_day":0.004545,"current_value":0.544186,"ci_low":0.001367,"ci_high":0.00881},{"name":"searchShare7d","slope_per_day":-0.000983,"current_value":0.0093,"ci_low":-0.001548,"ci_high":-0.000594}]


# _about

key: description
value:
  Sibling artifact to ytpool-public.db. Same channel data; app-internal narrative tables removed; per-row interpretive labels (ctr_quadrant, lifecycle_stage, verdict_*) and narrative-prose columns (timeline headlines, state-change reason_text, annotation bodies) stripped. Forecast tables (summary_threshold_projection, summary_channel_forecast, summary_channel_regime_forecast, summary_video_decay_model, summary_video_trajectory_cone, summary_video_nearest_peers, forecast_calibration_log, forecast_decay_history) kept intact. Designed for LLM consumers that should reason from data rather than parrot stored conclusions.


key: canonical_entry_tables
value:
  video,summary_video,summary_channel_metrics_daily,summary_channel,channel_timeline_event,traffic_daily,traffic_source


key: pre_publish_stub_rule
value:
  Always filter WHERE pre_publish_stub = 0 on video_daily / traffic_daily, or use the _clean views shipped in this DB.


key: voice_rule
value:
  Forecasts state their assumption and uncertainty band. Banned forecast verbs: "you will", "you'll", "will reach", "will cross" (without "at current pace"), "is going to", "on track to win", "expected to surpass", "is set to". Required hedges: "projects", "tracks toward", "at current pace", "if X holds", "arithmetic on the past N days". Never produce a point estimate without bands. Never give advice; reformulate as observation.


# _table_catalog

table_name: _about
layer: metadata
grain: (key)
related_to: _table_catalog, _column_catalog, _query_examples, _json_schema, schema_version
description:
  Single-row product orientation. One key/value pair per fact the
  reader needs before querying: product name, schema version, build
  timestamp, reporting cutoff, voice rules, aggregate object counts,
  and the snapshot-date span (`playhead_range_start` /
  `playhead_range_end`) that every snapshot-extended table carries.
  `compute_version` is the SHA-1 of every
  `compute-*.js` source file — it flips when detector or compute
  logic changes between two deploys, signalling that the historical
  snapshots were re-derived under updated code. A new consumer
  should `SELECT * FROM _about;` before anything else.

caveats:
  `build_timestamp` drifts on every rebuild; the fingerprint script
  normalizes it so semantic equality still works.
  `playhead_range_*` reflect the snapshot_date span of every
  snapshot-extended table — the canonical bounds for an as-of
  point-lookup (readers bind to the latest
  `snapshot_date <= asOfDate`).


table_name: _table_catalog
layer: metadata
grain: (table_name)
related_to: _column_catalog, _query_examples, _about
description:
  Index of every table and view in the public DB. Carries the layer,
  grain, row count, prose description, and relationship pointers
  (upstream_of / related_to). Renamed tables carry their old name in
  `deprecated_alias`.


table_name: _column_catalog
layer: metadata
grain: (table_name, column_name)
related_to: _table_catalog, _json_schema
description:
  One row per column in every table and view. Carries type, unit,
  plain-language definition, nullability reason, enum values, and
  a pointer to a paired confidence column where applicable.


table_name: _json_schema
layer: metadata
grain: (table_name, column_name, variant_value)
related_to: _column_catalog
description:
  Shape documentation for columns that store JSON text. Lists keys
  present, a worked example, and a description. Variant-aware so
  polymorphic columns (e.g. `evidence_json` shape per detector)
  can carry one row per variant.


table_name: _query_examples
layer: metadata
grain: (id)
related_to: _table_catalog, _column_catalog
description:
  Golden-path SQL examples. Each row is a (question, SQL) pair that
  illustrates a common analytical task end-to-end. Categorised and
  difficulty-tagged so a new consumer can scan, lift, and adapt.


table_name: video
layer: raw
grain: (video_id)
upstream_of: summary_video, video_daily, video_era, summary_video_country_lifetime, traffic_daily
description:
  One row per public video on the channel. Carries publish date, exact duration in seconds, era, language, and the real 11-char YouTube video ID (preserved in this owner build). The real video title is preserved here too; display labels come from this table.

caveats:
  Owner (de-anon) build: real titles, real 11-char YouTube IDs, and exact durations are preserved (the anon-era duration bucketing was removed at flc/D2). Private / deleted videos are excluded upstream.


table_name: video_daily
layer: raw
grain: (video_id, date)
upstream_of: summary_video, summary_channel_metrics_daily, video_daily_clean
related_to: traffic_daily, summary_video, summary_video_traffic
description:
  Per-(video, day) metrics from the YouTube Analytics + Reporting
  APIs: views, watch time, impressions, click rate, average view
  duration, average view percentage, subscribers gained/lost,
  likes, comments, shares.

caveats:
  `pre_publish_stub = 1` rows exist for the seven days before a
  video's publish_date and carry NULL impressions / 0 views.
  Filter `WHERE pre_publish_stub = 0` or use `video_daily_clean`.
  YouTube reporting lags 2-3 days — see `_about.reporting_cutoff`.


table_name: video_era
layer: raw
grain: (video_id, field, era_index, snapshot_date)
related_to: summary_era, event_creator_edit
description:
  Title/thumbnail eras per video. Each row marks the start of an
  era with the (often-hashed) configuration string. Used by the
  `era` detector to compare CTR / retention before vs. after a
  swap. `field` distinguishes title vs thumbnail eras; era_index
  is monotonic within a (video, field) sequence.

caveats:
  `snapshot_date` is on the PK for V64 PK-shape consistency with
  every other Backfill-Pass-extended table, but era rows are
  written at SETUP time (one snapshot, the build's
  `latestDataDate`). As-of reads honor era membership
  via the start_date / end_date columns: a reader filtering
  `start_date <= asOfDate AND (end_date IS NULL OR end_date >
  asOfDate)` gets the era set active on `asOfDate`. Join restriction:
  a future query that joins `video_era` ON `snapshot_date` against
  another snapshot-extended table will return ZERO rows for any
  asOfDate other than `MAX(snapshot_date)` because only one snapshot
  is ever populated. The start_date/end_date filter is the only
  correct as-of read pattern; never join on snapshot_date here.
  Backfill Pass Phase 9 (F-4) reviewed the trade-off: a tiny
  `writeVideoEraWalker` step would re-INSERT ~100 era rows on every
  walker iteration (~33 × 100 = 3300 redundant rows for a ~33-day
  channel) without changing what any current reader returns. The
  decision (2026-05-14): keep video_era SETUP-populated and pin the
  constraint here so future joins don't silently lose rows.


table_name: traffic_daily
layer: raw
grain: (video_id, traffic_source_id, date)
upstream_of: summary_video_traffic, summary_traffic_source_daily, traffic_daily_clean
related_to: video_daily, traffic_source
description:
  Per-(video, source, day) view and click-rate share. The core
  long table for "where do views come from over time" questions.

caveats:
  Same `pre_publish_stub` trap as video_daily — filter or use
  `traffic_daily_clean`. Per-source CTR can be `noise` tier for
  small impression samples; see `ctr_confidence`.


table_name: traffic_source
layer: raw
grain: (traffic_source_id)
related_to: traffic_daily, summary_traffic_source_daily, summary_video_traffic
description:
  Lookup table mapping YouTube's numeric traffic_source_id to a
  stable display name and human-readable description (e.g.
  "Browse features", "External", "Suggested videos").


table_name: summary_channel
layer: derived
grain: (snapshot_date)
upstream_of: app_dashboard_briefing_daily
related_to: summary_channel_metrics_daily, summary_channel_snapshot_daily, summary_video
description:
  Single-row channel-wide lifetime summary: video count, total
  views, total watch hours, subscriber count, channel-internal
  medians (CTR, retention, impression-to-view ratio), library
  half-mass video count, maturity stage, funnel pcts.


table_name: summary_channel_metrics_daily
layer: derived
grain: (date)
upstream_of: app_dashboard_briefing_daily, event_channel_state_change
related_to: summary_channel, summary_channel_snapshot_daily, summary_channel_source_share_daily
deprecated_alias: summary_channel_daily
description:
  Channel-wide daily aggregates: views, impressions, weighted CTR,
  subscriber net change, plus rolling 7d / 28d windows, source-mix
  diversity, top-video share, quiet-inventory share, and per-stage
  video counts.

caveats:
  Latest date may lag `_about.reporting_cutoff` by 1 day. Filter
  `date <= (SELECT value FROM _about WHERE key = 'reporting_cutoff')`
  for safe queries.


table_name: summary_channel_snapshot_daily
layer: derived
grain: (date)
related_to: summary_channel_metrics_daily, summary_channel
deprecated_alias: channel_daily
description:
  Daily snapshot of subscriber count and lifetime view count from
  the YouTube Data API. The "official" channel counter. Compare to
  `summary_channel_metrics_daily.subs_net_change` for analytics-vs-data
  reconciliation.


table_name: summary_channel_traffic
layer: derived
grain: (traffic_source_id, snapshot_date)
related_to: traffic_source, summary_video_traffic, summary_traffic_source_daily
description:
  Lifetime per-source channel-wide rollup: views, watch hours,
  average view percentage, subscribers gained, weighted CTR. Used
  to compare source contribution at the channel level.


table_name: summary_channel_country_daily
layer: derived
grain: (country, date)
related_to: summary_channel_country_trailing30, summary_video_country_lifetime
description:
  Per-(country, day) channel-wide views, watch time, and
  subscribers gained. Long format for stacked-area charts and
  geographic drift detection.


table_name: summary_channel_country_trailing30
layer: derived
grain: (country, snapshot_date)
related_to: summary_channel_country_daily
description:
  Per-country trailing-30-day channel-wide rollup: views, watch
  hours, share of total. Snapshot of "where the recent audience
  lives" without re-aggregating the daily table on every read.


table_name: summary_channel_catalog_pressure_daily
layer: derived
grain: (date)
related_to: summary_channel_metrics_daily, summary_video
description:
  Daily catalog-shape signals: count of videos receiving views,
  Gini-style concentration metrics, and pressure indicators
  describing how evenly attention is distributed across the
  catalog.


table_name: summary_channel_source_geo_daily
layer: derived
grain: (date, traffic_source_id, country)
related_to: summary_channel_country_daily, summary_traffic_source_daily
description:
  Joint (source × country) channel-wide daily view counts.
  Supports questions like "what share of External traffic comes
  from US vs CA?" without joining three tables at query time.


table_name: summary_channel_source_share_daily
layer: derived
grain: (date, traffic_source_id)
related_to: summary_channel_metrics_daily, summary_traffic_source_daily
description:
  Long-format trailing-7-day per-source view share. Used by
  stacked-area charts and source-mix-shift detectors so they
  don't re-roll the 7d window per query.


table_name: summary_channel_stage_snapshot
layer: derived
grain: (stage_name)
related_to: summary_channel, channel_phase_log
description:
  Six rows — one per fixed channel-maturity stage (seedling →
  established). Each row carries the reached date, day number,
  and per-row stage facts. Stages still ahead are NULL.


table_name: summary_video
layer: derived
grain: (video_id, snapshot_date)
related_to: video, video_daily, summary_era, summary_video_country_lifetime
description:
  One row per public video. Lifetime aggregates plus derived
  metrics: velocity (days-to-first-1k, etc.), decay phase, CTR /
  retention quadrant, lifecycle classification, edit-intensity
  class, source HHI. Many columns require minimum sample sizes;
  rows below the gate are NULL.

upstream_of:
  summary_video_trajectory_cone, summary_video_nearest_peers, summary_video_decay_model, summary_video_launch_context, summary_video_daily_rank

caveats:
  CTR values carry a `ctr_confidence` tier (noise/low/medium/high).
  Never display CTR without its tier — small samples are noisy.
  `ctr_quadrant` is NULL for `noise`-tier videos.


table_name: summary_video_country_lifetime
layer: derived
grain: (video_id, country, snapshot_date)
related_to: summary_video_country_daily, summary_video
deprecated_alias: summary_video_country
description:
  Per-(video, country) lifetime views, watch hours, retention,
  and subscribers gained. Used by the geographic-concentration
  detector to flag per-video country dominance.


table_name: summary_video_retention_curve
layer: derived
grain: (video_id, elapsed_ratio)
related_to: summary_video, video
description:
  The audience-retention curve per video: one point per (video, elapsed-time
  ratio) showing how much of the audience was still watching at that fraction
  of the video. `audience_watch_ratio` is this video's own retention at that
  point; `relative_retention_performance` is YouTube's own attributed
  comparison band against similar videos (nullable when YouTube has no peer
  set). Both metrics are non-monetary. A curve is stored only for a video that
  has reached 100 cumulative views — below that the curve is absent by design
  (too few views to be meaningful) — so a small channel shows nothing here. The shape YouTube returns is a
  single lifetime curve that never changes per snapshot, so it is stored ONCE
  (no snapshot dimension); the dashboard re-derives on which as-of dates a
  video's curve was lit from `summary_video.total_views` at that date.

caveats:
  Empty when the build had no private retention source (fixtures, local
  builds) and on any channel whose videos haven't reached 100 cumulative
  views — emptiness is the honest empty state, not an error. NOT
  snapshot-extended (no snapshot_date column): the curve is a single lifetime
  shape stored once, so it is intentionally absent from the snapshot-coverage
  and snapshot-monotonicity gates. `relative_retention_performance` is
  YouTube's attributed band, not a benchmark this product invented.


table_name: summary_video_country_daily
layer: derived
grain: (video_id, country, date)
related_to: summary_video_country_lifetime, traffic_daily
description:
  Per-(video, country, day) view counts. Daily-resolution analog
  to `summary_video_country_lifetime`. Long table; filter
  aggressively.


table_name: summary_video_traffic
layer: derived
grain: (video_id, traffic_source_id, snapshot_date)
related_to: traffic_daily, summary_video, traffic_source
deprecated_alias: summary_traffic
description:
  Per-(video, source) lifetime view share, CTR, retention. Source
  HHI on `summary_video` derives from this table. `share_of_video_views`
  is the per-video proportion attributable to that source.


table_name: summary_video_stage_daily
layer: derived
grain: (video_id, date)
related_to: summary_video, video_daily
deprecated_alias: video_stage_daily
description:
  Per-(video, day) lifecycle-stage classification (e.g. ramp,
  plateau, decay) based on the video's own velocity curve. Used
  by the `lifecycle-phase` detector.


table_name: summary_traffic_source_daily
layer: derived
grain: (traffic_source_id, date)
related_to: traffic_daily, summary_traffic_source_daily_history
deprecated_alias: summary_traffic_daily
description:
  Channel-wide per-source daily aggregates: views, watch hours,
  average view percentage, weighted CTR, subscribers gained.
  Drives source-mix-over-time charts.


table_name: summary_traffic_source_daily_history
layer: derived
grain: (traffic_source_id, date)
related_to: summary_traffic_source_daily, traffic_daily
deprecated_alias: summary_traffic_daily_history
description:
  Longitudinal store of `summary_traffic_source_daily`, kept across
  rebuilds so detectors needing historical context can read it
  without re-aggregating raw traffic_daily on every read.


table_name: summary_traffic_top_videos_by_source
layer: derived
grain: (traffic_source_id, rank, snapshot_date)
related_to: traffic_source, traffic_daily, summary_video_traffic
deprecated_alias: summary_traffic_top_videos
description:
  Top videos per traffic source by lifetime views. Used by the
  `/traffic/[sourceId]` detail page to render a leaderboard
  without scanning `traffic_daily` per render.


table_name: cohort_video_geography_age_window
layer: derived
grain: (video_id, country, age_window, snapshot_date)
related_to: summary_video_country_lifetime, summary_video_country_daily
deprecated_alias: summary_video_geo_window
description:
  Per-(video, country, age-window) view counts. Buckets the first
  N days after publish, the next N, etc., so cross-video comparison
  is age-normalised. Used for "does this video's country mix
  typically settle at day 28?" questions.


table_name: cohort_video_launch_age_window
layer: derived
grain: (video_id, age_window, snapshot_date)
related_to: summary_video, video_daily
deprecated_alias: summary_video_launch_window
description:
  Per-(video, age-window) launch-context metrics: views, CTR,
  retention bucketed by days since publish. Drives the cohort-lift
  detector and the per-video launch comparison chart.


table_name: cohort_video_traffic_age_window
layer: derived
grain: (video_id, traffic_source_id, age_window, snapshot_date)
related_to: traffic_daily, summary_video_traffic
deprecated_alias: summary_video_source_window
description:
  Per-(video, source, age-window) view counts. "How quickly does
  Browse vs Suggested ramp up on this video?" Used by source-mix
  age-curve detectors.


table_name: cohort_video_change_verdict
layer: derived
grain: (video_id, pivot_date, pivot_kind, snapshot_date)
related_to: event_creator_edit, summary_subscriber_event_daily, video_era
deprecated_alias: summary_video_change_window
description:
  Before/after rollup per video pivot (era boundary, subscriber-loss
  event, or hand-authored note). Each row carries metric snapshots
  on both sides of the pivot so the `change-window` detector can
  score whether the swap moved the needle.


table_name: cohort_channel_change_verdict
layer: derived
grain: (pivot_date, pivot_kind, snapshot_date)
related_to: event_creator_edit, event_channel_annotation
deprecated_alias: summary_channel_change_window
description:
  Channel-scope mirror of `cohort_video_change_verdict`. Reads
  from `event_creator_edit` (channel-scope rows) and
  `event_channel_annotation` to score channel-wide pivots.


table_name: summary_creator_edits_daily
layer: derived
grain: (date)
related_to: event_creator_edit, summary_creator_edits_weekly
deprecated_alias: summary_change_daily
description:
  Daily count of creator edits across all videos plus field-level
  breakdowns (title, thumbnail, description, tags). Includes an
  `is_burst_day` flag for days with unusually high edit activity.


table_name: summary_creator_edits_weekly
layer: derived
grain: (week_start_date)
related_to: summary_creator_edits_daily, event_creator_edit
deprecated_alias: summary_change_weekly
description:
  Weekly rollup of creator edits with field-level breakdowns.
  Mirrors `summary_creator_edits_daily` at a coarser grain for
  chart use.


table_name: summary_creator_edits_monthly
layer: derived
grain: (month_start_date)
related_to: summary_creator_edits_daily, summary_creator_edits_weekly
deprecated_alias: summary_change_monthly
description:
  Monthly rollup of creator edits. Coarsest of the three edit
  cadence tables; used for long-trend visualisation.


table_name: summary_field_co_change
layer: derived
grain: (field_a, field_b, snapshot_date)
related_to: event_creator_edit, summary_creator_edits_daily
description:
  Pairwise co-occurrence of edit field combinations: how often
  title + thumbnail are swapped together, etc. Drives the
  co-change heatmap on the changes page.


table_name: summary_edit_view_position
layer: derived
grain: (video_id, changed_at, snapshot_date)
related_to: event_creator_edit, summary_video
description:
  Per-edit context: cumulative view percentage and lifecycle phase
  at the moment the edit happened. Lets the dashboard render
  "edited when this video had received 23% of its lifetime views"
  annotations.


table_name: channel_timeline_event
layer: event
grain: (event_key, snapshot_date)
upstream_of: v_event_ledger
deprecated_alias: channel_timeline
description:
  Unified event ledger: publish, milestone, insight, stat_snapshot,
  rank_change, first_external, note, subscriber_loss_event,
  stage_transition, era_start, audience_expansion, source_quiet,
  creator_event. Drives the Story page.

related_to:
  event_creator_edit, event_channel_annotation, event_country_first_view, event_source_first_view, event_channel_state_change

caveats:
  `evidence_json` is polymorphic — shape varies by `event_type`.
  See `_json_schema` for per-event-type key lists.


table_name: event_creator_edit
layer: event
grain: (scope, video_id, channel_id, field, changed_at, change_kind, snapshot_date)
related_to: cohort_video_change_verdict, cohort_channel_change_verdict, summary_edit_view_position
deprecated_alias: creator_change_log
description:
  One row per field-level edit detected by diffing successive
  YouTube Data API snapshots. Sanitised from the private
  video_change_log + channel_change_log. Powers the changes page
  and feeds `summary_creator_edits_*` rollups.


table_name: event_channel_annotation
layer: event
grain: (event_id)
related_to: channel_timeline_event
deprecated_alias: channel_event_public
description:
  Owner-authored timeline annotations from
  `pipeline/creator-events.yaml`. Durable record kept alongside
  `channel_timeline_event` so the source survives a rebuild.


table_name: event_country_first_view
layer: event
grain: (country)
related_to: summary_channel_country_daily, channel_timeline_event
deprecated_alias: country_activation_log
description:
  One row per country marking the date the channel received its
  first view from there. Feeds the geographic-emergence detector.


table_name: event_source_first_view
layer: event
grain: (video_id, traffic_source_id)
related_to: traffic_daily, summary_video_traffic
deprecated_alias: source_activation_log
description:
  First date each (video, traffic source) recorded any views.
  Computed from `MIN(date)` over `traffic_daily` where views > 0.
  Drives the source-activation-order detector.


table_name: event_channel_state_change
layer: event
grain: (date, state_kind)
related_to: summary_channel_metrics_daily, channel_timeline_event
deprecated_alias: summary_channel_state_log
description:
  Channel-wide state transitions (CUSUM step-change detections).
  Marks dates when channel-wide metrics shifted regime.


table_name: summary_subscriber_event_daily
layer: event
grain: (date, video_id, kind)
related_to: video_daily, summary_channel_metrics_daily, channel_timeline_event
description:
  Per-(date, video, kind) subscriber-loss and surge events. Lifts
  these out of the `channel_timeline_event.evidence_json`
  envelope into a queryable table for cross-reference.


table_name: summary_era
layer: derived
grain: (video_id, era_index, snapshot_date)
related_to: video_era, event_creator_edit
description:
  One row per (video, era) with weighted CTR, total impressions,
  average retention, and view share. Drives the `era` A/B detector
  and the changes page's era table.


table_name: channel_phase_log
layer: derived
grain: (phase_name, phase_start_date)
related_to: summary_channel_stage_snapshot, summary_channel
description:
  Log of channel-wide maturity-phase transitions. One row per
  phase entered (seedling → sprouting → growing → established)
  with the date and triggering threshold.


table_name: channel_cadence_daily
layer: derived
grain: (date)
related_to: summary_channel_metrics_daily, channel_timeline_event
description:
  Per-day publish-cadence state: rolling average gap between
  publishes, classifier output (steady, cadence_break, etc.),
  and per-day-of-week trend evidence.


table_name: channel_signal_daily
layer: derived
grain: (date, signal_name)
related_to: summary_channel_metrics_daily
description:
  Long-format per-(date, signal) feed of channel-wide signal
  values that don't fit the wide `summary_channel_metrics_daily`
  schema. Used by detectors that need a generic time-series
  backend.


table_name: source_age_pattern_daily
layer: derived
grain: (traffic_source_id, days_since_publish)
related_to: summary_video_traffic, cohort_video_traffic_age_window
description:
  Per-source age-curve template: median view share at each
  `days_since_publish`. Reference points for asking whether a
  specific video's source mix is precocious vs. typical.


table_name: video_current_state
layer: derived
grain: (date, video_id, snapshot_date)
related_to: summary_video, summary_video_stage_daily
description:
  Per-video snapshot of the most recent lifecycle / decay state
  flags. Read by the per-video page for "where this video is
  right now" pills.


table_name: app_wisdom_flip_lifetime
layer: app_internal
grain: (test_id)
related_to: app_wisdom_verdict_daily, app_wisdom_canon
description:
  Per-belief LIFETIME accumulator for the wisdom-flip history.
  app_wisdom_verdict_daily is bounded to a ~90-day tail, so a
  whole-ledger aggregate would go window-relative; this table folds
  each new verdict date forward so flip_count, first_seen_date,
  last_changed_date and current_streak_days stay lifetime-accurate.
  Read by /learnings (getWisdomHistorySummaries).


table_name: app_detector_lifetime
layer: app_internal
grain: (detector_name, scope)
related_to: app_insight_snapshot, app_detector_activity
description:
  Per-detector LIFETIME accumulator. app_detector_activity is a
  window rollup over the retained ~90-day app_insight_snapshot tail;
  this table survives the prune by folding each new snapshot date's
  fires forward, so total_fires / first_fired_date are lifetime-
  accurate. scope='slot' mirrors the raw prose-routing-slot
  aggregation; scope='logical' is the recovered logical-detector
  aggregation. Overlaid onto the window rollups by /help/detectors.


table_name: owner_read_daily
layer: app_internal
grain: (snapshot_date)
related_to: app_insight_snapshot, summary_channel_metrics_daily, summary_channel_forecast
description:
  One consolidated "today's read" row per `snapshot_date` — the
  synthesis the home/digest surfaces lead with. NOT a new raw
  metric: it consolidates signals that already exist (the day's
  ranked insights from `app_insight_snapshot`, the trailing-7d
  channel rollup vs the prior window, the forecast usability tier)
  into one row a reader can dispatch on without re-assembling them.
  Each row carries the views/subs/impressions trend tags + their
  7d windows, the top-mover video and its share, source-mix
  direction, active/quiet catalog split, the day's lead insight
  (key + signal_family + significance), and a sample-of-one
  `thin_read` honesty flag for days too quiet to read as anything
  but quiet. Populated by `dashboard/scripts/generate-snapshots.ts`
  inside its per-snapshot-date loop (empty at clean-build time, like
  `app_insight_snapshot`). Backfill-pattern-bound: PK ends in
  `snapshot_date`, `computed_at` binds to the snapshot date.


table_name: app_pool_cell_stat
layer: app_internal
grain: (cell_key, metric, snapshot_date)
related_to: app_pool_named_peer, summary_channel_metrics_daily
description:
  Stage-3 shared-pool aggregate slot, per snapshot: this channel's
  deciding cohort cell for each pool metric (views_28d, subs_28d,
  uploads_28d, video_day7_views, ctr_28d, the four
  traffic_*_share_28d mix shares, video_retention_half) — exactly
  one row per (metric, snapshot_date). `gate_state` is `lit` when the cell has data
  to show; the only non-lit value is `withheld`, which now marks a DARK
  row — the pool flag is off, or the metric has genuinely zero members —
  never a privacy withhold. All pool withholding was removed (operator-
  ratified): there is no k-anonymity floor, no dominant-member cap, no
  private-member-count rule, and no seeded noise. A whole-pool cell
  LIGHTS from the smallest real pool (≥2 members — the definition of a
  pool, not a floor) and publishes its RAW cohort statistics as-is. The
  deciding cell prefers the FINEST cohort rung holding ≥2 members (a real
  pool, as specific as the data allows); when no rung holds two, it falls
  back to the finest rung with at least one metric-contributing member — a
  `member_band` '1' bookkeeping row the dashboard never renders as a
  comparison. That lone member is usually the channel itself, but when the
  channel's own value is unknowable it can be a different pooled channel,
  whose exact value the row then carries. Lit
  rows carry the cell's median (and p25/p75 at the `quartile` rung), a
  floored member-count band, and a partial-pooling estimate for this
  channel (published whenever the channel is a member of the cell). At
  the `percentile` rung (a real pool — ≥2 members — with this channel
  itself a metric-contributing member) the row
  additionally carries this channel's standing in the cohort:
  `subject_percentile`, a distribution-free prediction band
  (`conformal_low`/`conformal_high`), and `trajectory_class`
  (below/within/above that band). Honesty is carried by accurate
  visuals + attribution (the floored member band, one tap away), never
  by a withheld cell or hedge copy; aggregates NEVER name a channel
  (cohort bands + counts + numbers only).
  The table also holds additive, age-indexed WHOLE-POOL cohort families
  on their own `<family>:<age>` cell keys (a magnitude band only —
  positioning columns and `pooled_estimate` stay NULL): `chanage:<age>`
  aligns pooled channels by days-since-first-video and publishes the RAW
  median + p25/p75 of cumulative subscribers (`chan_cumulative_subscribers`)
  and cumulative views (`chan_cumulative_views`) at that channel age — the
  source for the on-chart pool overlay — lit ONLY where ≥2 distinct
  channels align at that age (never a 1:1 mirror of a lone channel), with
  an HONEST member band ('2-4' at two channels). `vidage:<age>` is its
  video-grain sibling (per-day views by days-since-publish), lit once ≥2
  videos across ≥2 pooled channels align at an age (the
  definition-of-pool rule — the only numeric gate) — and, like every
  family, it publishes the raw order statistics (the former ±2%
  small-cohort factor is gone).

caveats:
  Empty when the build ran without a pool-input extract (fixtures,
  local builds, channels not in the shared pool) — emptiness is the
  honest state, not an error. Exact member counts are never stored;
  `member_band` is the floored band. `cell_p25`/`cell_p75` are NULL
  below the quartile rung (a single-member cell); the positioning columns
  (`subject_percentile`, `conformal_low`, `conformal_high`,
  `trajectory_class`) are NULL on every row below the `percentile`
  rung — cells where this channel is not itself a metric-contributing
  member, and the additive age-cohort families.
  `pooled_estimate` publishes for the channel-grain metrics whenever this
  channel is a member of the cell; it is NULL on the additive age-cohort
  families (`chanage`/`vidage`) and on dark rows.
  Fully re-derived every build, so a channel that leaves the pool
  vanishes from every snapshot on the next build. Cumulative-style
  monotonicity does NOT apply to this table.


table_name: app_pool_named_peer
layer: app_internal
grain: (peer_contributor_id, snapshot_date)
related_to: app_pool_cell_stat
description:
  The open pool's named side-by-side rows, per snapshot: one row per
  channel that is currently BOTH public and pool-consented (this
  channel included when eligible). Carries only public-dashboard-class
  facts — lifetime totals (views, subscribers), video count, trailing
  28-day views and uploads, and the channel's own median day-7 views —
  plus the peer's handle and display title. The same row set appears
  in every channel's DB; it is a pure function of the sweep-global
  pool extract.

caveats:
  Empty when the build ran without a pool-input extract. Peer
  lifetime totals can legitimately DECREASE across snapshot_date
  (deleted/privated videos, unsubscribes) and the peer set changes
  with consent — never assume monotonicity. `views_28d` is NULL when
  the trailing window isn't fully covered by the pool's analytics
  history (including the most recent dates, reporting lag);
  `video_day7_views_median` is NULL until a peer has ≥3 videos old
  enough for a day-7 reading. Current consent governs the whole
  history: every stored snapshot's rows reflect the channels
  consented TODAY.


table_name: app_video_forecast_prior
layer: app_internal
grain: (video_id, snapshot_date)
related_to: app_pool_cell_stat, summary_video_decay_model
description:
  The video-grain "what's likely next" empirical-Bayes prior, per snapshot:
  one row per this channel's own videos whose own (noisy, short-history)
  per-day-views decay rate is shrunk toward the cross-channel cohort's typical
  decay rate. The cohort comes ONLY from the gate-passed video-grain
  `vidage` cohort (app_pool_cell_stat, metric `video_daily_views` — raw
  order statistics); the own side comes ONLY from this channel's own video. `own_slope` is the video's own
  log-space decay rate (the same shape `summary_video_decay_model` fits),
  `cohort_slope` the cohort's typical decay rate (the prior mean), `tau2` the
  prior strength, `shrink_weight` the empirical-Bayes weight B in [0,1] (short
  history shrinks harder), and `shrunk_slope = B·cohort_slope + (1−B)·own_slope`
  the blended "what's likely next" estimate a forecast consumes. The
  channel-grain analog of this lives inline in summary_channel_forecast (the
  subscriber slope EB-shrink); this is its per-video cousin.

caveats:
  Empty (table absent) until the pool-aggregate flag is on — every build
  without a pool-input extract (fixtures, local builds, non-pooled channels)
  legitimately has no such table. Rows appear once this channel's `vidage`
  cohort lights — which needs ≥2 videos across ≥2 pooled channels at
  matching ages and ≥3 lit ages (the lit-ages minimum is a slope-fit
  degeneracy rule, not a size gate; the real 2-channel pool crosses both).
  A video with fewer than 3 closed, observed per-day-views points
  has no own estimate to shrink and gets no row. Exact member counts are never
  stored (`member_band` is a floored band, the most conservative across the lit
  ages); the cohort side is fit over the already-published cohort medians, so
  the shrunk slope discloses no more than the cohort band itself. Fully
  re-derived every build (consent
  purge by construction); monotonicity does NOT apply. Slopes are log-space
  decay rates (per day of age), quantized to 6 decimals for cross-architecture
  byte-stability.


table_name: data_status
layer: metadata
grain: (metric_type)
related_to: _about, schema_version
description:
  One row per data domain (views, impressions, traffic_sources,
  channel_snapshots, reporting_timezone) with available_from /
  available_to date range and a human note. Read by the
  HonestyPanel "what data is in this view?" annotations.


table_name: metric_metadata
layer: metadata
grain: (metric_id)
related_to: _column_catalog, _about
description:
  Semantic-metric definitions: each row carries a metric_id (e.g.
  `ctr_confidence`, `reporting_timezone`), plain-language
  definition, source tables, computation doc, and the confidence
  method. Surfaced via /data tile tooltips.

caveats:
  Coexists with `_column_catalog`. Many metric_ids span multiple
  tables (`source_tables` is a comma list) and some
  (`reporting_timezone`, `pre_publish_stub`) are concepts rather
  than columns.


table_name: schema_version
layer: metadata
grain: (version)
related_to: _about
description:
  Append-only log of schema versions with `applied_at` timestamps
  and a short note per version. The latest row's version number
  matches `_about.schema_version`.


table_name: engagement_weights
layer: metadata
grain: (weight_name)
related_to: summary_video
description:
  Configurable weights table used by composite scores. Carries
  `updated_at` which is fingerprint-normalized.


table_name: summary_video_trajectory_cone
layer: derived
grain: (video_id, age_days, snapshot_date)
upstream_of: summary_video_nearest_peers
related_to: summary_video, video_daily
description:
  Per-(video, age_days) percentile-band trajectory cone
  (p10/p25/median/p75/p90 + mean + peer_count) over the channel's
  OTHER videos at the same age. Doubles as a forward-looking
  forecast band — rows exist for ages the subject has not yet
  reached, populated with the cone older peers traced at that
  age. Percentile columns are NULL when peer_count < 5.


table_name: summary_video_nearest_peers
layer: derived
grain: (video_id, snapshot_date)
related_to: summary_video, summary_video_trajectory_cone
description:
  Top-5 nearest peers per video by trajectory similarity (cosine
  distance over the cumulative-views vector during the first 30
  post-publish days). Peers are flat columns peer_1_id … peer_5_id
  with matching peer_K_distance scores; trailing slots are NULL
  when fewer than 5 candidates qualify. Mirrors the live
  findNearestPeer() constants in dashboard/src/lib/trajectory-similarity.ts.


table_name: summary_video_daily_rank
layer: derived
grain: (video_id, date)
related_to: video_daily, summary_video
description:
  Per-(video, day) channel-internal rank by daily views. Lets
  cross-video comparison queries answer "was this video the
  channel's #1 driver on that date?" without re-aggregating.


table_name: event_video_phase_transition
layer: event
grain: (video_id, event_date, snapshot_date)
related_to: summary_video_stage_daily, channel_timeline_event
description:
  One row per detected lifecycle-phase transition (consecutive
  days where stage[d] != stage[d-1] in summary_video_stage_daily).
  Carries from_phase / to_phase, days_in_from_phase, trailing-3d
  view rates before / after, and a magnitude score
  (abs(views_rate_change_pct) * (1 + days_in_from_phase / 7))
  so detectors can rank transitions by impact. Feeds the existing
  `stage_transition` channel_timeline_event type via Phase 6.4.


table_name: summary_channel_cohort
layer: derived
grain: (cohort_week, snapshot_date)
related_to: summary_video, video, video_daily
description:
  Per-ISO-week cohort rollup of videos published that week.
  Carries mean day-1/day-7/day-30 views, mean click rate and
  retention, median days-to-100-views, and 7/14/30-day survival
  shares. `vs_baseline_pct` compares the cohort's mean day-30
  views to the rolling mean of the prior 8 cohorts; coarse
  `cohort_label` tier marks ≥ +20% as `outperforming` and
  ≤ -20% as `underperforming`. NULL `cohort_label` means the
  baseline is not yet available (fewer than one prior cohort).


table_name: summary_video_decay_model
layer: derived
grain: (video_id, snapshot_date)
related_to: summary_video, video_daily
description:
  Per-video decay model parameters: exponential / power-law fit
  coefficients, fit quality, and projected forward views over
  the next 30 / 90 days. Drives the `decay-projection` detector.


table_name: summary_threshold_projection
layer: derived
grain: (threshold_type, threshold_value, snapshot_date)
related_to: summary_channel, summary_channel_metrics_daily, forecast_calibration_log, summary_channel_forecast
description:
  Forward-looking projection of when the channel reaches the next
  round threshold (subscribers / cumulative views / video count).
  The pace starts from the trailing-window fit and, when the channel
  is pooled and a real ≥2-member cohort rung is lit, is EB-shrunk
  toward the shared pool's family prior for the threshold type —
  `rate_per_day` is always the rate the projection actually used.
  `projected_date_low` /
  `projected_date_high` are conformal crossing-date bands: the
  quantiles of the pace model's own past walk-forward prediction
  residuals, solved in closed form for the crossing time — the
  surface honestly widens when the model's own track record is
  noisy. The fitted dates stay published on the
  `confidence='insufficient'` tier (the dashboard then shows the
  date range only, never a single-date claim — the wide band
  carries the roughness); the date columns are NULL only when the
  pace is not positive or the crossing solves beyond the ~10-year
  horizon, where there is genuinely nothing honest to project at
  the current pace.

  Every published row also enters the calibration ledger
  (forecast_calibration_log) as a days-to-crossing prediction and is
  scored against the actual crossing. Post-build, the auto-tune pass
  (compute-forecast-autotune.js) UPDATEs the latest snapshot from
  the resolved entries: widened dates when past crossings landed
  outside their bands (band_widen_k_low / band_widen_k_high record
  the widen-only multipliers, NULL until 5 crossings have resolved
  on the cell), a confidence tier recomputed on the widened band,
  and historical_crossing_coverage — the trailing inside-band
  fraction the dashboard's demotion reads.

  Projections assume recent pace and cadence hold; they are
  arithmetic on the past N days, not predictions.


table_name: summary_channel_forecast
layer: derived
grain: (as_of_date, horizon_days, forecast_type)
description:
  Horizon-banded channel-level forecast. One row per
  (horizon, forecast_type) at the latest reporting cutoff: horizons
  7 / 14 / 30 / 90 days, types `subscribers` (cumulative count) and
  `daily_views` (rolling 7-day mean of per-day views). Model fit
  is ordinary least squares on a trailing 28-day window when the
  channel cadence classifies as high, 56 days otherwise — with the
  fitted slope EB-shrunk toward the shared pool's family prior
  (subscriber rate / views trend) when the channel is pooled and a
  real ≥2-member cohort rung is lit; bands are
  conformal / residual-quantile — the point plus-or-minus the
  asymmetric quantiles of the model's own past walk-forward
  prediction residuals, RNG-free, so reruns over identical input
  produce byte-identical bands. The `confidence='insufficient'`
  tier (below 14 days of sample, bands wider than the point
  estimate, or a band that dipped below zero on these non-negative
  series) KEEPS its fitted value and band — the dashboard renders
  the band only, no point claim, and published values clamp at 0.
  `projected_value` and the two `_low`/`_high` columns are NULL
  only when nothing was fittable at all (fewer than 2 usable
  points, or an all-NULL series). Projection assumes recent
  direction and discovery mix hold; arithmetic on the past N days,
  not a prediction.

  Post-build, the auto-tune pass (compute-forecast-autotune.js)
  UPDATEs each row's predicted_low / predicted_high to apply a
  widen-only multiplier (band_widen_k_low / band_widen_k_high)
  when the trailing window of resolved entries on the same
  (forecast_type, horizon_days) cell suggests the raw conformal
  band under-covered. The raw conformal band is reconstructible
  as `projected_value ± (predicted_high - projected_value) / band_widen_k_high`
  when the k columns are non-null. coverage_below_floor_days
  counts a trailing chronic-miss streak the dashboard uses to
  demote cells whose widening still can't close the gap.

related_to:
  summary_channel, summary_channel_metrics_daily, summary_threshold_projection, forecast_calibration_log


table_name: summary_channel_regime_forecast
layer: derived
grain: (as_of_date)
related_to: event_channel_state_change, summary_channel, summary_channel_metrics_daily
description:
  Joint-trajectory regime-transition forecast — one row per
  as_of_date carrying the channel's currently-classified surface
  state, the projected next state, and the expected transition
  window with uncertainty bands. Built by projecting each
  classifier-input feature in event_channel_state_change forward at
  its trailing 21-day OLS slope and re-classifying day-by-day for
  up to 60 days. Pair-bootstrap (B=200, seeded Mulberry32) widens
  the band: each feature's slope CI is computed, features whose CI
  straddles zero or whose |slope| sits below the noise floor are
  dropped, and the joint distribution of projected (transition_day,
  projected_state) tuples drives the modal projection.
  `projected_state` and the three `expected_days*` columns go NULL
  on the `confidence='insufficient'` branch (fewer than two
  meaningfully-moving features, bootstrap-transition rate under
  50%, or modal-state agreement under 60%).
  `contributing_features_json` is the (name, slope_per_day,
  current_value, ci_low, ci_high) payload for each feature driving
  the projection. `classifier_version` pins the v2 state classifier
  so future bumps in compute-channel-state-log.js are observable
  downstream. Projection assumes recent direction and discovery mix
  hold; arithmetic on the past 21 days, not a prediction.


table_name: summary_channel_threshold_impression_pattern
layer: derived
grain: (snapshot_date)
related_to: summary_video, video_daily, summary_channel
description:
  Channel-pattern memory for the "does a click-rate or watch-duration
  jump on one of this channel's older videos tend to be followed by
  a step-up in YouTube's impressions to that video?" question.
  Walks every video on the channel and, for each leading-edge CTR
  or AVD jump observed at age >= 14 days (mirror of the thresholds
  in `late-life-ctr-jump.ts` / `late-life-avd-jump.ts`), checks
  whether the next 7 days of impressions averaged at least 20%
  above the prior 7 days. Aggregates counts to channel-grain at
  this snapshot. The followed-rate (`*_followed_count / *_event_count`)
  is a sample-of-one observation, not a base rate — when
  `videos_evaluated` is small, the per-video card should hedge.


table_name: summary_channel_narrative_arc
layer: derived
grain: (arc_id, snapshot_date)
related_to: summary_channel_metrics_daily, channel_timeline_event
description:
  Macro-structure of the channel's life as a small set of contiguous
  multi-week windows ("arcs" / movements) that the /story scroll
  choreographs against — pacing and motion shift at arc boundaries.
  Each arc carries a coherent shape derived from the channel's OWN
  signals: weighted timeline-event density, day-over-day view-rate
  change, and lifecycle-stage churn, smoothed and z-scored against the
  window's own baseline (no absolute thresholds — sample-of-one), then
  segmented by change-point detection.

  This is INTERPRETIVE, OBSERVATIONAL SEGMENTATION, not a ranking. No
  arc is a "best era", a climax, or a winner; a quiet stretch is a
  first-class movement, equal in weight to a busy one — only the shape
  differs. The neutral `arc_kind` / `view_trend_label` / `dominant_motion`
  enums carry no value judgement and `headline` is hedged. The thin-
  timeline guardrails (min arc 7 days, max arcs min(5, floor(span/10)),
  a genuinely flat signal collapsing to ONE steady arc) mean a quiet
  channel honestly gets fewer arcs — never padded drama. Context metrics
  only (`event_density`); no per-arc subscriber/view totals that could
  read as a scoreboard.


table_name: forecast_calibration_log
layer: event
grain: (forecast_made_on, horizon_days, forecast_type)
description:
  Append-only credibility ledger for BOTH forecast layers. Arc rows
  (`subscribers` / `daily_views`) carry one past projection from
  summary_channel_forecast — the original published value + band,
  plus the observed actual once the build's reporting cutoff
  catches up to target_date. Milestone rows (`milestone_*`) carry
  one past crossing-date projection from
  summary_threshold_projection, measured in DAYS-TO-CROSSING:
  horizon_days holds the threshold value, predicted_* the projected
  days-out, and actual_value the days until the metric's cumulative
  series actually crossed. Source of truth lives at
  public/data/forecast-calibration-log.json (git-tracked, survives
  revert + rebuild); this table is a build-derived view of the
  latest 180 days of that file. `inside_band` is the load-bearing
  credibility signal — past forecasts whose actual fell inside
  [predicted_low, predicted_high] — and misses never silently exit
  the denominator: a sufficient-history forecast with no usable
  band scores 0 when the outcome arrives, and an overdue milestone
  (late edge passed, no crossing yet) scores 0 immediately while
  its actual_value stays open. `error_pct` and `inside_band` stay
  NULL while the outcome is open, and also when the model declined
  to forecast at all in its warm-up window (no claim to score — an
  honest gap, not a hidden one).

related_to:
  summary_channel_forecast, summary_threshold_projection, summary_channel, summary_channel_metrics_daily


table_name: forecast_decay_history
layer: event
grain: (snapshot_date, video_id)
related_to: summary_video_decay_model, video, summary_video
description:
  Append-only per-(snapshot_date, video_id) snapshots of
  summary_video_decay_model. Captures the build's fit so consumers
  can compute deltas across builds — e.g., "this video's projected
  quiet date moved 14 days further out in the past week." Source
  of truth lives at public/data/forecast-decay-log.json (git-
  tracked, survives revert + rebuild); this table is a build-
  derived view of the latest 180 days of that file. Today's fit
  still lives in summary_video_decay_model; this ledger only
  accumulates history. `predicted_quiet_date` is NULL on the same
  rows where summary_video_decay_model leaves it NULL (model did
  not project a sub-5 day inside its 5-year horizon).


table_name: forecast_video_calibration_log
layer: event
grain: (forecast_made_on, video_id, metric_type)
related_to: summary_video_decay_model, video, video_daily
description:
  Append-only credibility ledger for the per-video decay-projection
  layer — the video-grain mirror of forecast_calibration_log. One row
  per past day-30/60/90 view projection from summary_video_decay_model
  (predicted_views_day_30/60/90): the original predicted daily
  view-rate, plus the observed actual value once the build's reporting
  cutoff catches up to target_date (= the video's published_at + N
  days). Source of truth lives at
  public/data/forecast-video-calibration-log.json (git-tracked,
  survives revert + rebuild); this table is a build-derived view of the
  latest 180 days of that file. The decay model emits POINT projections
  (no band), so the load-bearing credibility signal is `hit` — whether
  the actual landed within ±20% of the prediction (hit-rate, not
  inside-band). `actual_value`, `error_pct`, and `hit` stay NULL while
  target_date is still in the future. Video-detail reads this as one
  channel-wide track-record statement
  (dashboard/src/lib/forecast/video-calibration.ts).


table_name: summary_channel_source_leading_indicator
layer: derived
grain: (date, snapshot_date)
related_to: summary_traffic_source_daily, summary_channel_metrics_daily
description:
  Per-source leading-indicator score: how reliably this source's
  daily movement predicts the next-week channel-wide trend.


table_name: summary_channel_country_cohort_stability
layer: derived
grain: (date, country_code, snapshot_date)
related_to: summary_channel_country_daily, summary_channel_country_trailing30
description:
  Per-country cohort stability: how much the country's relative
  share has drifted over rolling windows. Quiet countries with
  stable share differ from quiet countries fading out; this row
  distinguishes them.


table_name: summary_video_launch_context
layer: derived
grain: (video_id, snapshot_date)
related_to: summary_video, summary_channel_metrics_daily
description:
  Per-video launch-context snapshot: channel-wide state at publish
  (subscriber count, recent average daily views, publish gap,
  last era swap). Drives the `launch-context-underperformance`
  detector.


table_name: video_daily_clean
layer: derived
grain: (video_id, date)
related_to: video_daily
description:
  Stub-clean view over `video_daily` — `WHERE pre_publish_stub = 0`.
  Use this instead of `video_daily` directly unless you need the
  pre-publish stub rows for some specific reason.


table_name: traffic_daily_clean
layer: derived
grain: (video_id, traffic_source_id, date)
related_to: traffic_daily
description:
  Stub-clean view over `traffic_daily` — `WHERE pre_publish_stub = 0`.
  Use this instead of `traffic_daily` directly unless you need
  pre-publish stub rows.


table_name: v_latest_channel_state
layer: derived
grain: ()
related_to: summary_channel_metrics_daily, summary_channel_snapshot_daily, summary_channel
description:
  Single-row view of the most recent `summary_channel_metrics_daily`
  row joined with `summary_channel_snapshot_daily` for the same
  date. The "what's the state right now?" entry point.


table_name: v_video_state
layer: derived
grain: (video_id)
related_to: video, summary_video, video_current_state
description:
  Per-video state convenience view joining `video`, `summary_video`,
  and `video_current_state`. Replaces three separate lookups for
  the per-video page.


table_name: v_traffic_daily_labeled
layer: derived
grain: (video_id, traffic_source_id, date)
related_to: traffic_daily_clean, traffic_source
description:
  `traffic_daily_clean` joined to `traffic_source` so the source
  display name appears alongside the numeric source_id.


table_name: v_event_ledger
layer: derived
grain: (date, event_type, subject_id)
description:
  Unified event view spanning `channel_timeline_event` plus the
  typed event_* tables (`event_creator_edit`,
  `event_channel_annotation`, etc.). One canonical UNION ALL
  so cross-event-type queries don't have to list every table.

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


# _column_catalog

table_name: app_detector_lifetime
column_name: scope
type: TEXT
unit: enum
enum_values: slot, logical
example_value: logical
definition:
  Which aggregation this lifetime row carries — 'slot' (raw prose-routing slot, keyed like app_detector_activity.detector_name) or 'logical' (the recovered logical detector).


table_name: app_detector_lifetime
column_name: total_fires
type: INTEGER
unit: count
definition:
  Lifetime count of this detector's fires (accumulated across all snapshot dates, surviving the ~90-day tail prune).


table_name: app_detector_lifetime
column_name: through_date
type: TEXT
unit: iso_date
definition:
  Calendar date in PT (America/Los_Angeles) — the high-water snapshot date already folded into this accumulator (bookkeeping for the forward merge).


table_name: app_pool_cell_stat
column_name: cell_key
type: TEXT
unit: text
example_value: s2|27|a1|long
definition:
  The cohort cell this row aggregates. Channel-grain metric rows use the channel's own axes — size_band|category|age_band|format, with '*' marking axes dropped by the deterministic coarsening ladder (e.g. 's2|27|*|*'). The deciding cell is the FINEST ladder rung holding ≥2 members (a real pool) when one exists, else the finest rung with at least one metric-contributing pool member — usually the channel itself (its self-cell), but when the channel's own value is unknowable the lone member can be a different pooled channel, whose exact value the row then carries at member_band '1'; category is never wildcarded. Age-indexed cohort families instead ride a flat `<family>:<age>` key that is the SAME in every pooled channel's DB — `chanage:<age>` (the whole-pool channel band aligned by days-since-first-video) and `vidage:<age>` (the video band aligned by days-since-publish) — while `cone_d{age}` cone rows carry the axis-cell key.


table_name: app_pool_cell_stat
column_name: metric
type: TEXT
unit: enum
example_value: views_28d
definition:
  The pool metric this row aggregates, computed per member over data at or before snapshot_date. The channel-grain metrics — views_28d, subs_28d, uploads_28d, video_day7_views, plus the three families creators switch tools for: ctr_28d (28-day click-through rate, Σclicks/Σimpressions over the reach spine), the four traffic_*_share_28d mix shares (browse / suggested / search / external share of 28-day views), and video_retention_half (the channel's median halfway-point audience retention across curve-bearing videos; a lifetime shape, not 28d-windowed) — sit alongside additive cohort families that ride the same columns: `cone_d{age}` and `video_daily_views` (video-grain bands) and `chan_cumulative_subscribers` / `chan_cumulative_views` (the channel-age band, cumulative value at a channel age). The enum below lists the channel-grain set; the cohort-family keys are representative, not floored.

enum_values:
  views_28d,subs_28d,uploads_28d,video_day7_views,ctr_28d,traffic_browse_share_28d,traffic_suggested_share_28d,traffic_search_share_28d,traffic_external_share_28d,video_retention_half,video_daily_views,chan_cumulative_subscribers,chan_cumulative_views


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 has data to show ('lit') or is a dark row ('withheld' — the pool flag is off, or the metric has zero members). A dark row carries NULL in every value column. 'withheld' is now ONLY this no-data state, never a privacy withhold — all pool withholding was removed (no k-anonymity floor, no dominant-member cap, no private-member-count rule, no seeded noise).


table_name: app_pool_cell_stat
column_name: rung
type: TEXT
unit: enum
nullable_reason: NULL when gate_state = 'withheld'.
enum_values: median,quartile,percentile
example_value: median
definition:
  The display rung the gate cleared — 'median' (one typical value, a single-member cell), 'quartile' (median plus inner quartiles, a real pool of ≥2 members), or 'percentile' (where this channel sits in the cohort — its percentile plus a prediction band; reached whenever the pool has ≥2 members and this channel is itself a metric-contributing member).


table_name: app_pool_cell_stat
column_name: member_band
type: TEXT
unit: enum
nullable_reason: NULL when gate_state = 'withheld'.
enum_values: 1,2-4,5-9,10-24,25-99,100plus
example_value: 2-4
definition:
  Floored band of the cell's member count. The exact count is never stored — the band floors what an observer can infer about who occupies the cell. The low bands ('1', '2-4') are HONEST — a real small pool reads truthfully rather than inflating to '5-9', since the whole-pool aggregate lights from two members up.


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 for the count metrics; ctr_28d and the four traffic_*_share_28d metrics are 0–1 fractions (click-through rate; share of 28-day views); video_retention_half is the 0–1 audience share still watching at each video's halfway point, per-channel median. Published RAW for every family — the channel-grain metrics and the `chanage`/`vidage` age cohorts alike (no withholding, no seeded noise, no deterministic factor; the exact interpolated order statistic).


table_name: app_pool_cell_stat
column_name: cell_p25
type: REAL
unit: score_dimensionless
nullable_reason: NULL below the 'quartile' rung or when withheld.
definition:
  The cell's 25th-percentile member value (same units as cell_median; published RAW, the exact interpolated order statistic).


table_name: app_pool_cell_stat
column_name: cell_p75
type: REAL
unit: score_dimensionless
nullable_reason: NULL below the 'quartile' rung or when withheld.
definition:
  The cell's 75th-percentile member value (same units as cell_median; published RAW, the exact interpolated order statistic).


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:
  Published for the channel-grain metrics whenever this channel is a member of the cell; NULL on the additive age-cohort families (chanage/vidage) and on dark rows.


table_name: app_pool_cell_stat
column_name: subject_percentile
type: REAL
unit: share_0_to_1
nullable_reason: NULL except on a 'percentile'-rung row.
definition:
  Where this channel sits in its cohort for the metric — its percentile (0–1) among the cell's members. This channel's own standing, never another member's value. Reached only at the 'percentile' rung (a real pool of ≥2 members with this channel itself a metric-contributing member).


table_name: app_pool_cell_stat
column_name: conformal_low
type: REAL
unit: score_dimensionless
nullable_reason: NULL except on a 'percentile'-rung row.
definition:
  Lower edge of the cohort's prediction band for the metric — a distribution-free range a channel like this one typically lands in (same units as cell_median; a raw member order statistic, no noise). Reached only at the 'percentile' rung.


table_name: app_pool_cell_stat
column_name: conformal_high
type: REAL
unit: score_dimensionless
nullable_reason: NULL except on a 'percentile'-rung row.
definition:
  Upper edge of the cohort's prediction band for the metric (paired with conformal_low; same units as cell_median — a raw member order statistic, no noise). Reached only at the 'percentile' rung.


table_name: app_pool_cell_stat
column_name: trajectory_class
type: TEXT
unit: enum
nullable_reason: NULL except on a 'percentile'-rung row.
enum_values: below_band,within_band,above_band
example_value: within_band
definition:
  Where this channel sits relative to the cohort's prediction band — below it, within it, or above it. A plain position, never a ranking against a named channel. Reached only at the 'percentile' rung.


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_video_forecast_prior
column_name: video_id
type: TEXT
unit: text
example_value: aB3dEfGh1jk
definition:
  This channel's own video the forecast prior is for. Always one of this channel's own videos — never another pool member's.


table_name: app_video_forecast_prior
column_name: own_slope
type: REAL
unit: score_dimensionless
definition:
  The video's OWN log-space per-day-views decay rate (OLS slope of log(per-day-views + 1) over age), from this channel's data only — the same shape summary_video_decay_model fits.


table_name: app_video_forecast_prior
column_name: cohort_slope
type: REAL
unit: score_dimensionless
definition:
  The cross-channel cohort's typical log-space per-day-views decay rate (OLS slope over the lit vidage cohort medians by age — raw order statistics) — the empirical-Bayes prior mean.


table_name: app_video_forecast_prior
column_name: tau2
type: REAL
unit: score_dimensionless
definition:
  The prior strength — the OLS slope variance of the cohort decay trend (from the published cohort medians only). Larger means a less certain cohort trend, so less shrinkage.


table_name: app_video_forecast_prior
column_name: shrink_weight
type: REAL
unit: share_0_to_1
definition:
  The empirical-Bayes weight B = var_own / (var_own + tau2) in [0,1]. B near 1 (short, noisy own history) shrinks to the cohort; B near 0 (long, stable own history) trusts the video's own trend.


table_name: app_video_forecast_prior
column_name: shrunk_slope
type: REAL
unit: score_dimensionless
definition:
  The blended "what's likely next" decay rate = B·cohort_slope + (1−B)·own_slope — the estimate a per-video forecast consumes when the cohort is lit.


table_name: app_video_forecast_prior
column_name: own_sample_days
type: INTEGER
unit: count
example_value: 30
definition:
  How many closed, observed per-day-views points fed the video's own fit (at least 3). More points means a tighter own estimate and less shrinkage.


table_name: app_video_forecast_prior
column_name: cohort_sample_ages
type: INTEGER
unit: count
example_value: 40
definition:
  How many lit cohort ages fed the cohort trend fit (at least 3). Counts ages, not channels — never an exact member count.


table_name: app_video_forecast_prior
column_name: member_band
type: TEXT
unit: enum
enum_values: 2-4,5-9,10-24,25-49,50-99,100plus
example_value: 2-4
definition:
  Floored band of the cohort cell's channel count (the most conservative band across the lit ages). The exact count is never stored — the band floors what an observer can infer about who occupies the cell.


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_flip_lifetime
column_name: flip_count
type: INTEGER
unit: count
definition:
  Lifetime count of verdict changes for this belief (accumulated across all dates, surviving the ~90-day verdict-ledger prune).


table_name: app_wisdom_flip_lifetime
column_name: first_seen_date
type: TEXT
unit: iso_date
definition:
  Calendar date in PT (America/Los_Angeles) — the earliest date this belief was evaluated (lifetime-accurate).


table_name: app_wisdom_flip_lifetime
column_name: last_changed_date
type: TEXT
unit: iso_date
nullable_reason: Never changed verdict — no flip has been recorded.
definition:
  Calendar date in PT (America/Los_Angeles) — the date of the most recent verdict change (NULL if never changed).


table_name: app_wisdom_flip_lifetime
column_name: current_streak_days
type: INTEGER
unit: count
definition:
  Consecutive snapshot dates the belief has held its current status (lifetime-accurate; can exceed the retained window).


table_name: app_wisdom_flip_lifetime
column_name: through_date
type: TEXT
unit: iso_date
definition:
  Calendar date in PT (America/Los_Angeles) — the high-water verdict date already folded into this accumulator (bookkeeping for the forward merge).


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_signal_daily
column_name: evidence_json
type: TEXT
unit: json
definition: JSON-encoded payload. See `_json_schema` for shape.
example_value:
  {"recent_video_id":"b2Nc8VtY4pQ","first_week_candidate_views":41,"first_week_candidate_impressions":1657,"first_week_baseline_median_views":9}


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_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_snapshot_daily
column_name: catalog_total_views
type: INTEGER
unit: count
example_value: 0
definition:
  Catalog-sum cumulative views as-of this date — the running sum of attributed per-video reporting (SUM(video_daily.views) where pre_publish_stub=0 and date on or before this row's date). Same aggregation as summary_channel.total_views; its final point equals the lifetime catalog total. Distinct from total_views, which is YouTube's channel-reported figure and can diverge via deleted videos or late attribution.


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_era
column_name: thumb_ref
type: TEXT
unit: text
definition:
  Content-addressed ref (hash) of the stored image bytes that were live during this thumbnail era, carried verbatim from video_era. The change-story render resolves it to the real old-vs-new packaging. NULL for title eras and any thumbnail era whose bytes were never captured (history before byte capture began).

nullable_reason:
  NULL for title-field eras and any thumbnail era with no captured bytes (pre-capture history or an unfetched image).


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_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: summary_video_retention_curve
column_name: elapsed_ratio
type: REAL
unit: share_0_to_1
example_value: 0.25
definition:
  Position along the video as a fraction of its full length (0 = the very start, 1 = the end) — the x-axis of the retention curve. YouTube's `elapsedVideoTimeRatio` dimension.


table_name: summary_video_retention_curve
column_name: audience_watch_ratio
type: REAL
unit: score_dimensionless
example_value: 0.82
definition:
  Share of the audience still watching at this point of the video — this video's own retention (YouTube's non-monetary `audienceWatchRatio`). Around 1.0 at the start; usually declines toward the end and can briefly exceed 1.0 where viewers rewatch a segment.


table_name: summary_video_retention_curve
column_name: relative_retention_performance
type: REAL
unit: score_dimensionless
nullable_reason: NULL when YouTube has no comparable-video set for this point.
example_value: 0.91
definition:
  YouTube's own attributed comparison of this video's retention at this point against similar videos (the non-monetary `relativeRetentionPerformance` band). An attributed benchmark, not one this product invented.


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_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: video_era
column_name: thumb_ref
type: TEXT
unit: text
definition:
  Content-addressed ref (hash) of the stored image bytes that were live during this thumbnail era, correlated from the change ledger at build time. The change-story render resolves it to the real old-vs-new packaging. NULL for title eras and any thumbnail era whose bytes were never captured (history before byte capture began).

nullable_reason:
  NULL for title-field eras and any thumbnail era with no captured bytes (pre-capture history or an unfetched image).


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, 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:
  Per-day pace the projection actually used. Starts from the trailing-lookback-window mean rate and, when the channel is pooled and a real ≥2-member cohort rung is lit, is EB-shrunk toward the shared pool's family prior for the threshold type (subs / views / uploads rate) — so `projected_date = snapshot_date + needed / rate_per_day` stays a reader-checkable identity in both worlds. 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
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. Kept on the 'insufficient' tier (the dashboard then shows a range, never a single-date claim).

nullable_reason:
  NULL when the pace is not positive, or when the solved crossing sits beyond the ~10-year projection horizon — nothing honest to project at the current pace.


table_name: summary_threshold_projection
column_name: projected_date_low
type: TEXT
unit: iso_date
confidence_column: confidence
example_value: 2026-06-22
definition:
  Earlier end of the crossing-date band — the date the threshold crosses if the value tracks the optimistic edge of the conformal residual band (the model's own walk-forward prediction spread). Post-widened by the auto-tune pass when past crossings on this cell landed outside their stated bands (band_widen_k_low records the multiplier). Kept on the 'insufficient' tier — the wide band IS the roughness signal.

nullable_reason:
  NULL when the pace is not positive, or when the solved crossing sits beyond the ~10-year projection horizon — nothing honest to project at the current pace.


table_name: summary_threshold_projection
column_name: projected_date_high
type: TEXT
unit: iso_date
confidence_column: confidence
example_value: 2026-07-16
definition:
  Later end of the crossing-date band — the date the threshold crosses if the value tracks the pessimistic edge of the conformal residual band. Post-widened by the auto-tune pass when past crossings on this cell landed outside their stated bands (band_widen_k_high records the multiplier). Kept on the 'insufficient' tier — the wide band IS the roughness signal.

nullable_reason:
  NULL when the pace is not positive, or when the solved crossing sits beyond the ~10-year projection horizon — nothing honest to project at the current pace.


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 the usable history length (the finite points of the cumulative series behind the residual walk) and the relative width of the crossing-date band in days. Recomputed on the widened band when the auto-tune pass fires, so a heavily-widened cell drops tiers honestly. On 'insufficient' and 'low' the dashboard shows the range only, never a single-date claim — the fitted dates stay published and the band width carries the confidence.


table_name: summary_threshold_projection
column_name: band_widen_k_low
type: REAL
unit: count
confidence_column: confidence
example_value: 1.0
definition:
  Widen-only multiplier the auto-tune pass applied to the EARLY side of the crossing-date band, in days-space, learned from resolved milestone entries in forecast_calibration_log (trailing window of up to 10). 1 means past crossings covered fine and the raw conformal edge shipped unchanged; larger values record how far the stored projected_date_low was pulled earlier. The raw conformal edge is reconstructible by dividing the days-out distance from projected_date back by this multiplier.

nullable_reason:
  NULL when fewer than 5 resolved crossings exist on this (threshold_type, threshold_value) cell — the raw conformal band ships untuned.


table_name: summary_threshold_projection
column_name: band_widen_k_high
type: REAL
unit: count
confidence_column: confidence
example_value: 2.3
definition:
  Widen-only multiplier the auto-tune pass applied to the LATE side of the crossing-date band, in days-space — the mirror of band_widen_k_low for projected_date_high. Capped at 5; a heavily-widened cell drops out of the higher confidence tiers via the recomputed width fraction.

nullable_reason:
  NULL when fewer than 5 resolved crossings exist on this (threshold_type, threshold_value) cell — the raw conformal band ships untuned.


table_name: summary_threshold_projection
column_name: historical_crossing_coverage
type: REAL
unit: share_0_to_1
nullable_reason: NULL when fewer than 5 scoreable entries exist on this (threshold_type, threshold_value) cell.
example_value: 0.8
definition:
  Fraction of this cell's past crossing-date bands that contained the actual crossing, over the most-recent window (up to 10) of scoreable forecast_calibration_log milestone entries — including overdue entries whose band's late edge passed with no crossing yet (those count as misses, never a silent denominator exit). The dashboard demotes the cell's rendering to a directional band when this falls below 0.5.


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; as-of readers bind to the latest `snapshot_date` at or before their asOfDate (a `WHERE snapshot_date = MAX(...)` point-lookup) so no future arc leaks into an as-of 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 only when nothing was fittable at all — fewer than 2 usable points, or an all-NULL series.
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. Kept on the 'insufficient' tier (the dashboard then shows the band only, never this point); clamped at 0 — both series are non-negative counts.


table_name: summary_channel_forecast
column_name: projected_low
type: REAL
unit: score_dimensionless
nullable_reason: NULL only when nothing was fittable at all — fewer than 2 usable points, or an all-NULL series.
confidence_column: confidence
example_value: 11.5
definition:
  Lower edge of the conformal band — the point minus the 10th-percentile quantile of the model's own past walk-forward prediction residuals, scaled to this horizon. Post-widened by the auto-tune pass when past forecasts on this cell under-covered (band_widen_k_low records the multiplier). Kept on the 'insufficient' tier — the wide band IS the roughness signal; floors at 0 (both series are non-negative counts, through the widen too).


table_name: summary_channel_forecast
column_name: projected_high
type: REAL
unit: score_dimensionless
nullable_reason: NULL only when nothing was fittable at all — fewer than 2 usable points, or an all-NULL series.
confidence_column: confidence
example_value: 17.2
definition:
  Upper edge of the conformal band — the point plus the 90th-percentile quantile of the model's own past walk-forward prediction residuals, scaled to this horizon. Post-widened by the auto-tune pass when past forecasts on this cell under-covered (band_widen_k_high records the multiplier). Kept on the 'insufficient' tier — the wide band IS the roughness signal.


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 conformal band's width relative to the point estimate. `insufficient` also fires when the band straddles zero on a non-negative series. The fitted value/band stay published on 'insufficient' — the dashboard renders the band only (no point claim), so the band width carries the confidence.


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 conformal lower-band half-width to produce the surfaced predicted_low. 1.0 means no widening (the raw conformal 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 conformal 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 conformal 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 conformal 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. Coverage is recorded for the latest snapshot; on older snapshots the streak reads 0 — the demotion threshold applies to the current view.


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 v2 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, solo_launch


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, solo_launch


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. On arc rows it pairs with horizon_days to recover the target_date = forecast_made_on + horizon_days that the row evaluates; on milestone rows it is the day the crossing countdown starts from.


table_name: forecast_calibration_log
column_name: horizon_days
type: INTEGER
unit: days
example_value: 14
definition:
  On arc rows (`subscribers` / `daily_views`) the days ahead the forecast aimed at, mirroring summary_channel_forecast.horizon_days (7 / 14 / 30 / 90). On milestone rows (`milestone_*`) it carries the THRESHOLD VALUE instead (10 subscribers, 5000 views, …) — the within-type discriminator that keeps the shared (forecast_made_on, horizon_days, forecast_type) key unique with no schema change.


table_name: forecast_calibration_log
column_name: forecast_type
type: TEXT
unit: enum
enum_values: subscribers, daily_views, milestone_subscribers, milestone_cumulative_views, milestone_video_count
example_value: subscribers
definition:
  Which projection the row evaluates. `subscribers` is the cumulative subscriber count and `daily_views` the rolling 7-day mean of per-day views (the arc's fixed horizons); the `milestone_*` values are the crossing-date projections from summary_threshold_projection, measured in days-to-crossing.


table_name: forecast_calibration_log
column_name: predicted_value
type: REAL
unit: score_dimensionless
example_value: 13.5
definition:
  Point estimate the original forecast published. On arc rows the projected value at target_date, in the forecast_type's own unit (subscriber count / daily views). On milestone rows the projected DAYS-OUT to the crossing — the whole-day distance from forecast_made_on to the published projected_date. An 'insufficient'-tier forecast now publishes its fitted values too, so its entry carries real numbers and is scored like any other.

nullable_reason:
  NULL when the original forecast published nothing — a genuinely-empty fit (fewer than 2 usable points / an all-NULL series) on arc rows, a non-positive pace (no crossing date) on milestone rows; calibration then carries no number to compare against.


table_name: forecast_calibration_log
column_name: predicted_low
type: REAL
unit: score_dimensionless
example_value: 11.0
definition:
  Lower edge of the published uncertainty band, in the same unit as predicted_value — the 10th-percentile conformal edge on arc rows; the early-date days-out on milestone rows.

nullable_reason:
  NULL when the original forecast published nothing (genuinely-empty fit / non-positive pace — see predicted_value).


table_name: forecast_calibration_log
column_name: predicted_high
type: REAL
unit: score_dimensionless
example_value: 16.0
definition:
  Upper edge of the published uncertainty band, in the same unit as predicted_value — the 90th-percentile conformal edge on arc rows; the late-date days-out on milestone rows.

nullable_reason:
  NULL when the original forecast published nothing (genuinely-empty fit / non-positive pace — see predicted_value).


table_name: forecast_calibration_log
column_name: actual_value
type: REAL
unit: score_dimensionless
example_value: 13.0
definition:
  The observed outcome. On arc rows the value at target_date (subscriber count / rolling 7-day mean of daily views), filled once the build's latest_data_date reaches target_date. On milestone rows the ACTUAL DAYS from forecast_made_on to the first recorded date the metric's cumulative series crossed the threshold, filled once the crossing lands.

nullable_reason:
  NULL while the outcome is still open — target_date in the future on arc rows; the threshold not yet crossed on milestone rows (an overdue milestone may already carry inside_band = 0 while this stays NULL).


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
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. Misses are never silently dropped from the denominator — a forecast the model made with enough history but no usable band scores 0 when the outcome arrives, and an overdue milestone (the band's late edge passed with no crossing) scores 0 immediately, before actual_value is known.

nullable_reason:
  NULL when the outcome is still open with no verdict yet, or when the model declined to forecast at all (warm-up window — no claim to score).


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: forecast_video_calibration_log
column_name: forecast_made_on
type: TEXT
unit: iso_date
example_value: 2026-04-28
definition:
  Reporting cutoff (snapshot_date) on which the decay fit behind this projection was computed in summary_video_decay_model. Successive fits of the same (video, metric_type) carry different forecast_made_on but resolve against the same realized point.


table_name: forecast_video_calibration_log
column_name: video_id
type: TEXT
unit: video_id
example_value: 1Hw_e3Aev3M
definition:
  Per-video identifier; foreign key onto video. Identifies the video whose decay projection this row evaluates.


table_name: forecast_video_calibration_log
column_name: metric_type
type: TEXT
unit: enum
enum_values: views_day_30, views_day_60, views_day_90
example_value: views_day_30
definition:
  Which decay-model horizon this row evaluates. views_day_30 / views_day_60 / views_day_90 are the predicted daily view-rate at video age 30 / 60 / 90 days (summary_video_decay_model.predicted_views_day_30/60/90).


table_name: forecast_video_calibration_log
column_name: target_date
type: TEXT
unit: iso_date
nullable_reason: NULL when the video's publish date was unavailable when the entry was appended.
example_value: 2026-05-28
definition:
  Calendar date the video reaches the evaluated age — published_at + N days for metric_type views_day_N. The entry resolves once a build's reporting cutoff reaches this date.


table_name: forecast_video_calibration_log
column_name: predicted_value
type: INTEGER
unit: count
nullable_reason: NULL when the underlying fit produced no day-N projection.
example_value: 120
definition:
  Point-estimate daily view-rate the decay model projected for this video at the evaluated age (predicted_views_day_N from summary_video_decay_model at forecast_made_on).


table_name: forecast_video_calibration_log
column_name: actual_value
type: INTEGER
unit: count
example_value: 108
definition:
  Observed daily view-rate at the evaluated age — the rolling 7-day mean of this video's per-day views (video_daily.views, pre_publish_stub = 0) ending at days_since_publish = N, rounded to an integer. Measured on the same smoothed daily series the model predicts, so predicted vs actual are apples-to-apples. Filled in once the build's reporting cutoff reaches target_date.

nullable_reason:
  NULL while target_date is still in the future, or when the trailing 7-day daily window was incomplete.


table_name: forecast_video_calibration_log
column_name: error_pct
type: REAL
unit: pct_0_to_100
nullable_reason: NULL while unresolved OR when predicted_value was NULL (no number to compare against).
example_value: 10.00
definition:
  Absolute percentage error — |actual_value - predicted_value| / |predicted_value| * 100. Reported to two decimals so the receipt sorts honestly without false precision.


table_name: forecast_video_calibration_log
column_name: hit
type: INTEGER
unit: enum
nullable_reason: NULL while unresolved OR when predicted_value was NULL.
enum_values: 0, 1
example_value: 1
definition:
  1 when the actual landed within ±20% of the prediction (error_pct / 100 <= 0.20), 0 otherwise. The load-bearing credibility signal — the channel-wide hit-rate over resolved entries is what the video-detail reliability receipt surfaces. The decay model emits POINT projections (no band), so this replaces the channel log's inside_band.


table_name: forecast_video_calibration_log
column_name: resolved_on
type: TEXT
unit: iso_date
definition: Reporting cutoff of the build that resolved this entry (filled actual_value / error_pct / hit).
example_value: 2026-05-29
nullable_reason:
  NULL while the entry is still unresolved (target_date in the future or the daily window incomplete).


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
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":"Zn3dG5hJyLk","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":"Kx7bQp2mLdA","new_leader":"Rj9nT4vWc0E","prior_value":11,"new_value":41,"prior_label":"How I plan a week of videos","new_label":"The camera setup I regret buying"}


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":"Rj9nT4vWc0E","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":["Rj9nT4vWc0E"],"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":"Mb6cV1wXtRu","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":"Rj9nT4vWc0E","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":"Rj9nT4vWc0E","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":["Rj9nT4vWc0E"],"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":"Zn3dG5hJyLk","lift_x":23.4,"today_views":39},{"video_id":"Hs2yF8kNpZq","lift_x":10.5,"today_views":9}]}


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: wisdom_flip
json_keys: test_id, prior_status, new_status, belief_short
example_value:
  {"test_id":"mid_length_videos_optimal","prior_status":"inconclusive","new_status":"disagrees","belief_short":"Mid-length optimum"}

description:
  A wisdom belief test changed verdict between snapshots. belief_short is the curated short belief name (null for an id with no curated label).


table_name: channel_timeline_event
column_name: evidence_json
variant_key: event_type
variant_value: trend
json_keys:
  metric, metric_label, mode, window_days, direction, current_avg, baseline_avg, magnitude_pct, current_n, baseline_n

example_value:
  {"metric":"channel_views","metric_label":"daily views (7-day mean)","mode":"prev","window_days":7,"direction":"rising","current_avg":240,"baseline_avg":150,"magnitude_pct":60,"current_n":7,"baseline_n":7}

description:
  Window-over-window trend in a channel-wide metric (C4) — the recent window's mean against the prior window's, computed by the trend lib's compareWindows (the same primitive the channel-window-trend detector runs). current_avg / baseline_avg are integer-quantized window means; magnitude_pct is the integer relative delta; direction is rising | falling. Observational — never a verdict.


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: event_channel_annotation
column_name: affected_video_ids_json
json_keys: (array, no top-level keys)
example_value: ["Rj9nT4vWc0E","Mb6cV1wXtRu"]
description:
  Array of YouTube video IDs (11-character) affected by a channel-scope annotation. NULL for channel-only events.


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: channel_signal_daily
column_name: evidence_json
json_keys: (varies per signal type — typically per-video and per-window numbers)
description: Per-(date, signal) evidence payload. Shape varies per signal type.
example_value:
  {"recent_video_id":"Rj9nT4vWc0E","first_week_candidate_views":41,"first_week_candidate_impressions":1657,"first_week_baseline_median_views":9}


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
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: 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: 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: 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
difficulty: advanced
question:
  Which video is the nearest peer to a given video (identified by its video id) by trajectory similarity?

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 = 'aB3dEfGh1jk';

notes:
  The example filters on video_id = 'aB3dEfGh1jk', an illustrative placeholder — substitute a real 11-character YouTube video id from the channel's catalog. Returns 0 when the chosen video 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 a given video's daily CTR rank shift over its first 14 days (filtered by its video id)?
difficulty: moderate
sql:
  SELECT date, ctr_rank, views_rank, peer_count
  FROM summary_video_daily_rank
  WHERE video_id = 'aB3dEfGh1jk'
  ORDER BY date
  LIMIT 14;

notes:
  The example filters on video_id = 'aB3dEfGh1jk', an illustrative placeholder — substitute a real 11-character YouTube video id from the channel's catalog. Returns 0 if the chosen video has no rankable days. Table created in Phase 3.3.


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 conformal 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 conformal 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: 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,
         subject_percentile, conformal_low, conformal_high, trajectory_class
  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 is honest, not an error. `gate_state` is `lit` when the cell has data; the only non-lit value, `withheld`, now marks a DARK row (pool flag off, or zero members) that carries NULL in every value column — never a privacy withhold (all pool withholding was removed; a whole-pool cell lights from ≥2 members and publishes raw). `member_band` is a floored band (exact member counts are never stored). The positioning columns (`subject_percentile`, `conformal_low`, `conformal_high`, `trajectory_class`) are populated only on a `percentile`-rung row — a real pool (≥2 members) with this channel itself a metric-contributing member.


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.


category: video_catalog
difficulty: simple
question:
  What does each lit video's audience-retention curve look like (how much of the audience is still watching across the video)?

sql:
  SELECT video_id, elapsed_ratio, audience_watch_ratio,
         relative_retention_performance
  FROM summary_video_retention_curve
  ORDER BY video_id, elapsed_ratio;

notes:
  Legitimately empty when the build had no private retention source (fixtures, local builds) and on any channel whose videos haven't crossed 100 cumulative views — a video's curve is absent until then, so a small channel shows nothing here. audience_watch_ratio is the video's own retention at that elapsed point (non-monetary); relative_retention_performance is YouTube's own ATTRIBUTED comparison band (nullable when YouTube has no peer set), not a benchmark this product invented. The curve is a single lifetime shape stored once (no snapshot dimension); the dashboard re-derives on which as-of dates a video's curve was lit from summary_video.total_views.


category: channel_overview
difficulty: moderate
question:
  At the latest snapshot, what did the pool's cumulative subscribers look like across channel age (days since first video) — the swept band a young channel maps onto its own calendar?

sql:
  SELECT CAST(substr(cell_key, 9) AS INTEGER) AS channel_age_days,
         member_band, cell_p25, cell_median, cell_p75
  FROM app_pool_cell_stat
  WHERE metric = 'chan_cumulative_subscribers'
    AND cell_key LIKE 'chanage:%'
    AND snapshot_date = (SELECT MAX(snapshot_date) FROM app_pool_cell_stat)
  ORDER BY channel_age_days;

notes:
  Legitimately empty without a pool-input extract, on non-pooled channels, and at any age where fewer than 2 distinct pooled channels align — the whole-pool overlay lights only from two members up, never a 1:1 mirror of a lone channel. One anonymous band row per channel age (`chanage:<age>`): the RAW median with p25/p75, the same in every pooled channel's DB — cumulative subscribers by days-since-first-video. Swap the metric to `chan_cumulative_views` for the cumulative-views band. `member_band` is a floored, HONEST band ('2-4' at two channels — never inflated to '5-9'); the cell never names a channel.


