# schema_version

created_at: 2026-04-25T00:00:00.000Z
description:
  Longitudinal storage tables (source_activation_log, channel_phase_log, summary_traffic_daily_history) so daily history survives rebuilds.


created_at: 2026-04-26T00:00:00.000Z
description:
  Owner-mode F-tier tables: summary_video_subscribed, summary_channel_country_trailing30, summary_channel_device.


created_at: 2026-04-26T00:00:01.000Z
description:
  pre_publish_stub flag on video_daily / traffic_daily — flags rows where date < published_at so summary aggregations can exclude them.


created_at: 2026-04-26T00:00:02.000Z
description:
  ctr_confidence tier (noise / low / medium / high) + unattributed_impressions on video_daily, traffic_daily and summary_video.


created_at: 2026-04-26T00:00:03.000Z
description:
  Day-3 / Day-7 cohort columns + velocity (days_to_first_*) + peak_views_day/value + late_growth_pct on summary_video.


created_at: 2026-04-26T00:00:04.000Z
description:
  imp_to_view_ratio + avd_drift_sec + source_hhi on summary_video; imp_to_view_ratio_median on summary_channel.


created_at: 2026-04-26T00:00:05.000Z
description:
  ctr_quadrant column on summary_video (winner / clicked-not-watched / watched-not-clicked / quiet / noise) with median_video_ctr + median_video_retention on summary_channel as the crosshairs.


created_at: 2026-04-26T00:00:06.000Z
description:
  Rolling 7d / 28d columns (views, watch_min, impressions, weighted_ctr) on summary_channel_daily; new summary_channel_source_share_daily long-format table.


created_at: 2026-04-26T00:00:07.000Z
description:
  source_diversity_score + top_video_share_7d + top3_video_share_7d + top_video_id_7d + videos_with_zero_views_7d + quiet_inventory_pct columns on summary_channel_daily.


created_at: 2026-04-26T00:00:08.000Z
description:
  date-only indexes on video_daily and traffic_daily so date-range scans no longer fall back to a full table scan.


created_at: 2026-04-27T00:00:00.000Z
description:
  Impression-weighted CTR formula (SUM(impressions * ctr) / SUM(impressions)) on summary_video.day3_ctr / day7_ctr and summary_traffic_daily_history.weighted_ctr — replaces the views/impressions shortcut so cohort and per-day source CTR match the canonical weighted_ctr definition.


created_at: 2026-04-27T00:00:01.000Z
description:
  pre_publish_stub filter applied to source_activation_log and summary_traffic_daily_history build SELECTs — longitudinal storage tables now exclude pre-publish stub rows so trip events on /traffic/[sourceId] and the Spine no longer reflect zero-view pre-publish dates.


created_at: 2026-04-27T00:00:02.000Z
description:
  Foreign-key constraints on junction tables (video_daily, traffic_daily, video_era, summary_video, summary_era, summary_traffic, summary_channel_traffic, summary_traffic_daily, summary_traffic_top_videos, source_activation_log, summary_traffic_daily_history, summary_channel_source_share_daily, summary_video_country, summary_video_subscribed) — orphan video_id / traffic_source_id rows now raise SQLite errors when a writer enables PRAGMA foreign_keys. The dashboard read-only client opens the DB with PRAGMA foreign_keys = ON for belt-and-suspenders enforcement.


created_at: 2026-04-27T00:00:03.000Z
description:
  Covering index idx_traffic_daily_source_date on traffic_daily(traffic_source_id, date) — homepage Browse-impression activity card now uses an index SEARCH instead of a full traffic_daily scan (~91k rows on a 200-video fixture).


created_at: 2026-04-27T00:00:04.000Z
description:
  Covering index idx_insight_snapshot_cat_date on insight_snapshot(insight_category, snapshot_date) — detectShapeChange and any future category-scoped snapshot lookup now use an index SEARCH instead of falling back to idx_snapshot_date and re-checking insight_category row by row.


created_at: 2026-04-27T00:00:05.000Z
description:
  Owner-mode tables (summary_video_country, summary_video_subscribed, summary_channel_country_trailing30, summary_channel_device) suppressed by default; pass --include-owner-tables to emit them. Stage-1 public deploys ship without these tables; the dashboard read helpers hard-assert NEXT_PUBLIC_OWNER_MODE=true as defense-in-depth. The legacy --show-channel-name flag is removed.


created_at: 2026-04-27T00:00:06.000Z
description:
  Coarsened publish_date and duration_seconds in default builds so the public DB cannot be cross-referenced to identify the channel: video.published_at and summary_video.published_at snap to YYYY-MM-01; video.duration_seconds and summary_video.duration_seconds snap to one of eight bucket lower bounds (0/60/180/360/600/1200/1800/3600). Coarsening runs as a final UPDATE pass after all derived columns (day3/day7 cohorts, days_to_first_*, peak_views_day, summary aggregates) are computed against precise values, so build-time math is unaffected. Owner-mode rebuilds with --show-precise-metadata skip the UPDATE and preserve full precision.


created_at: 2026-04-27T11:00:00.000Z
description:
  Count consistency across surfaces. summary_channel.total_public_videos now counts videos with published_at <= latest_data_date (the reporting cutoff) instead of joining through video_daily, so videos published right at/after the cutoff stop being silently dropped from the count. summary_channel.total_days_tracked now derives from (latest_data_date - first_video_date + 1) so the days span and the public-video count align by construction for daily posters. summary_channel_daily.cumulative_videos_live is recomputed from precise source-side publish dates instead of the noisy data-api channel_snapshot.video_count, and two new columns most_recent_video_id (TEXT) and days_since_last_publish (INTEGER) let the dashboard render per-date "videos live" + "Video N is X days old" without recomputing from coarsened video.published_at (CLOSE-B-51 silently broke that path).


created_at: 2026-04-27T12:00:00.000Z
description:
  Reverts V23 (CLOSE-B-51) coarsening of video.published_at and summary_video.published_at to YYYY-MM-01. The collapse to first-of-month broke the cadence detector (every channel misclassified as "schema-ambiguous"), the per-date predicates in the dashboard, and the publish-date scatter (every dot landed on a single vertical line); the privacy gain was marginal because per-video age is already exposed via days_tracked, view trajectories, and the timeline. duration_seconds bucketing remains in place — it carries genuine de-anonymization risk (a precise integer-second runtime is rare enough to be fingerprintable) without the downstream collateral. The --show-precise-metadata flag still toggles the duration bucketing for owner-mode rebuilds.


created_at: 2026-04-28T00:00:00.000Z
description:
  A-01 — Country tables default-public. The two tables (summary_video_country, summary_channel_country_trailing30) now ship in default builds, with --coarsen-country-mix on so countries below 5% share collapse to "OT" (Other). The owner-mode flag still suppresses summary_video_subscribed plus summary_channel_device. The country dimension on its own does not deanonymize an English-language channel (top countries cluster around US/IN/GB/CA on essentially every channel); the prior owner-mode gate hid the most diagnostic dimension in the dataset.


created_at: 2026-04-28T01:00:00.000Z
description:
  A-02 + A-03 — Daily country tables and country_activation_log. summary_video_country_daily (per-(video, country, date)) and summary_channel_country_daily (per-(date, country)) ship in default builds, populated from reporting_video_metric with cutoff and pre-publish guards. The daily resolution unlocks per-video country stream charts on /videos/[id] and the channel country river on /traffic; downstream slices read these tables for activation logs, audience-swap detection, and per-country sub-funnel cohorts. Long-tail rows below the 5% per-video share threshold collapse into the same "OT" bucket the lifetime tables use. A-03 layers the country_activation_log table (per-(video, country) first-active and first-meaningful >= 5 views dates) on top, plus a first_country_view channel_timeline event (one per country the channel has reached, significance 50) so the Story dispatcher can render geographic-reach milestones once A-27 wires it.


created_at: 2026-04-28T02:00:00.000Z
description:
  A-04 — Country composites on summary_video. Five derived columns (country_hhi, dominant_country_code, dominant_country_share, country_concentration_with_weak_retention, country_audience_match_score) populated by a post-summary pass over the coarsened summary_video_country rows. HHI mirrors source_hhi shape (sum of squared shares × 10000); the concentration boolean fires when one country holds >= 40% share of a video and that country watches >= 30% shorter than the video overall; the match score is the cosine similarity between this video distribution and the channel-wide well-retaining country distribution (countries weighted by views from videos retaining at or above the channel median). Pairs the country dimension with the existing source_hhi to characterize concentration on two independent axes; the geographic-concentration detector now reads the persisted boolean instead of recomputing per-insight.


created_at: 2026-04-28T03:00:00.000Z
description:
  A-05 + A-06 — Lifecycle daily classifier and stage-transition timeline. A-05 ships the new video_stage_daily table (per-(video, date), one of nine stages: pre_publish / debut / growth / peak / tail / quiet / re_emerged / zombie / dormant) populated by a deterministic JS pass over video_daily anchored on summary_video.peak_views_day. Eight new columns on summary_video persist the latest stage (lifecycle_stage), the per-stage day spans (days_in_growth, days_in_peak, days_in_tail), the time from peak to half-of-peak rolling views (tail_half_life_days), and three survives-day-N flags (survives_day_7 / 14 / 30) gated on cumulative views at day N reaching the channel-median cumulative views at the same day. A-06 layers a stage_transition channel_timeline event type on top — emitted from diff of consecutive video_stage_daily rows, filtered to meaningful transitions only (any transition INTO re_emerged / zombie / dormant, any transition OUT of quiet, plus peak -> tail) — and adds three detectors that read the persisted stages: zombie-impressions, dormant-video, and channel-level video-re-emergence. Eight columns plus the transition events let the catalog grid read current stage and trajectory health without touching video_daily; downstream slices (A-23 StageChip, A-27 story dispatcher, A-29 video timeline strip) read these directly.


created_at: 2026-04-28T04:00:00.000Z
description:
  A-08 — Channel-scope co-movement events and anomaly composite scaffolding. Two new channel_timeline event types ship on default builds: multi_video_activation (a day where three or more videos each ran at or above 2x their prior 7-day rolling mean views) and cohort_lift (an ISO-week publish cohort whose median trailing-7-day views climbed at least 30 percent over the prior week while older cohorts stayed flat within plus-or-minus 10 percent). Two new detectors (multi-video-activation, cohort-lift) emit the same patterns as Insight objects routed through the existing emerging_signal slot via evidence.shape. summary_channel_daily gains the anomaly_score_composite REAL column; the column stays NULL on default builds until A-12 ships the metric_zscore_daily table that populates it (sum of absolute z-scores across tracked metrics per date). The Opening rail container exposes the data-testid="anomaly-marker-rail" hook so the smoke gate can catch a SQL-join-key class of regression at build time even before A-12 lands.


created_at: 2026-04-28T05:00:00.000Z
description:
  A-09 — Channel temperature and country diversity. summary_channel_daily gains two new REAL columns: temperature_score (a 0..100 weighted blend of trailing CTR, AVD, sub-rate, source diversity, and library activity z-scores against the prior 90-day baseline; 50 reads as the channel's own normal, 60 as a warmer week than typical for this channel, 40 as a cooler week than typical) and country_diversity_score (Simpson form 1 - sum(share^2) over the per-date top-country shares from summary_channel_country_daily). Both columns require at least 35 days of baseline; below that they stay NULL. Adds two channel-scope detectors that share the emerging_signal slot via evidence.shape: algorithmic_thaw fires when channel-wide impressions ran at or above 2x their prior 14-day median for two or more consecutive days, and algorithmic_frost fires on the symmetric drop (impressions at or below 50 percent of the prior 14-day median for two or more days). Both maturity-gated to sprouting+. The 5th tile on the home page right-now strip renders the score, a 14-day sparkline, and a hedged qualifier sourced from EVOLUTION section 4.1 ("warmer than typical" / "your normal" / "cooler than typical") so the channel position is observation, not verdict.


created_at: 2026-04-28T06:00:00.000Z
description:
  A-10 — Sticky-traffic ratio and algorithmic dependency index. traffic_source gains an is_sticky 0/1 column (sources where the viewer chose to come back: Direct, Channel Page, Playlist, End Screen, link-clicks from off-platform, subscriber notification; everything else is algorithmic discovery). summary_video adds sticky_traffic_ratio (0..1 share of total_views from sticky sources, NULL when no traffic rows). summary_channel adds algorithmic_dependency_index (1.0 minus the channel-wide sticky share). summary_channel_daily adds two columns: per-day algorithmic_dependency_index and a trailing 28-day OLS slope of that index (units: index points per day, NULL when fewer than 14 daily values fall in the trailing window). The Audience chapter mounts a new <StickyTrafficSparkline> tile that renders the per-day index as a sparkline against a hedged qualifier; per-video pages render a small sticky-traffic chip. Both extremes (high and low ratio) are legitimate channel patterns; the value is observation, not verdict.


created_at: 2026-04-28T07:00:00.000Z
description:
  A-11 — Audience persistence and replacement, plus the emerging_audience event type. summary_channel_daily gains two REAL columns: audience_persistence_pct (share of last week's (source, country) buckets that also appeared the prior week) and audience_replacement_rate (share of last week's buckets that did not appear the prior week). Computed as a JS pass over reporting_traffic_metric on the source DB; one weekly comparison written per ISO week-ending row in summary_channel_daily, with NULL on dates that do not fall on a week boundary. A new emerging_audience event_type emits one channel_timeline row per (traffic_source_id, country_code) bucket that did not appear in the channel's first 14 days yet drew at least 50 views in the last 28 days; headline names the country, then the source. The Audience chapter mounts a two-bar gauge showing the persistence and replacement values with arrows reading the most recent two weekly comparisons; both states (high persistence vs high replacement) are legitimate channel patterns at different ages.


created_at: 2026-04-28T08:00:00.000Z
description:
  A-12 + A-13 — Z-score machinery and step-change scaffolding. A-12 ships metric_zscore_daily (date, metric, value, baseline_mean, baseline_sd, zscore, sample_size; PK on (date, metric)) holding per-(date, metric) z-scores against a prior 28-day baseline for five tracked metrics: daily_views, weighted_ctr, weighted_avd, sub_gain_rate, total_impressions. Each row reads the 7-day trailing mean as the value, prior 28-day mean and sd as the baseline; baseline window must hold at least 28 non-null values or the z-score stays NULL. summary_channel_daily.anomaly_score_composite reads SUM(ABS(zscore)) per date so the Opening rail anomaly markers fire on multi-metric outlier days. The new metric-anomaly detector reads z > 2 outliers in the trailing 7 days and routes through the existing emerging_signal slot via evidence.shape. A-13 layers six new columns on summary_channel_daily: trailing 14-day OLS slopes for top_video_share, top3_video_share, source_diversity, quiet_inventory, and channel-level country HHI, plus recent_debut_trend_14d (relative delta between recent-14-day and prior-30-day mean day1_impressions across the catalog). A-13 also adds a CUSUM step-change scan over four channel-level rolling series (views, click rate, avg watch, subs gained) emitting step_change channel_timeline events when the maximally-separating split is at least 30% relative magnitude and sits at least 7 days from either series boundary. The new recent-debut-trend detector reads summary_video and surfaces the recent-vs-prior comparison as a single channel-scope insight at growing+ maturity. Observation-only voice across all surfaces.


created_at: 2026-04-28T09:00:00.000Z
description:
  A-14 + A-15 — Engagement composite, velocity, two new detectors, plus subscriber granularity. A-14 ships engagement_weights (default likes=1, comments=5, shares=3) and three columns on summary_video: engagement_rate_composite (weighted-engagement-events / total_views; NULL when total_views = 0), engagement_velocity_24h (likes + comments + shares from the first 24 hours since publish), engagement_velocity_7d (the same sum over the first 7 days). Two new detectors: comment-outliers fires per-video when comments-per-100-views runs at least 3x the channel median (sprouting+); engagement-leads-views runs a cross-correlation between engagement spikes and view spikes per video at lag 3-7 days (growing+). Both route through emerging_signal via evidence.shape. The chip on /videos/[id] surfaces the 24h and 7d velocity values; the new <EngagementVelocityChart> renders them as a two-bar Recharts BarChart. A-15 layers subscriber granularity on top: summary_traffic gains subs_gained INTEGER (per-(video, source) total_subs_gained allocated proportionally by view share; YouTube does not break subs out by source so the value is an inference) and funnel_engaged_views INTEGER (SUM(engaged_views) from reporting_traffic_metric per (video, source)); summary_video gains days_to_third_subscriber and days_to_fifth_subscriber (extends the days_to_first_subscriber pattern, walks video_daily for the day cumulative subs_gained reaches 3 / 5). channel_timeline.event_type adds subscriber_event for milestone-only channel-wide subs (every 5th up to 100, every 10th past 100, every 25th past 500, every 100th past 1k) — replaces the prior milestone-typed subscriber rows so the Story dispatcher can target the new event type explicitly. New <SubFunnelChart> mounts on /traffic/[sourceId] rendering impressions → views → engaged → subs as a horizontal Recharts BarChart; new sub-rate-vs-view-rate detector fires when the trailing 28-day sub-rate slope and view-rate slope diverge (audience-quality-improving / volume-without-depth shapes routed through emerging_signal). Observation-only voice across both slices.


created_at: 2026-04-28T10:00:00.000Z
description:
  A-16 — Days-to-N velocity columns. summary_video gains seven INTEGER columns: days_to_10_views, days_to_100_views, days_to_500_views, days_to_1000_views (cumulative-views milestones, populated by the same per-video walk that already produces days_to_50_views), plus days_to_first_comment, days_to_first_like, days_to_first_share (day offset where the cumulative engagement count first reaches one). All seven columns hold NULL when the milestone has not landed yet, mirroring the days_to_first_subscriber convention. summary_channel gains two INTEGER columns: median_days_to_100_views and median_days_to_1000_views — channel medians across videos that have crossed each milestone, NULL when fewer than one eligible video exists. The /videos/[id] discovery-unfolded strip extends to surface the new milestones inline alongside the existing days_to_first_* values; the Catalog chapter on the home page renders a hedged channel-median callout. Pure-data slice; observation-only voice.


created_at: 2026-04-28T11:00:00.000Z
description:
  A-17 + A-18 — Cadence classifier, cadence break events, day-of-week trend (A-17), plus library Gini coefficient and Lorenz half-mass count (A-18). A-17 ships channel_cadence_daily (one row per date with a classified publishing pattern: daily, every_other_day, weekday_only, weekly_3x, weekly, irregular; conformance percentage over the trailing 28 days; days_since_break that resets on each divergence) and the cadence_break channel_timeline event emitted on each break day after the channel held the pattern for at least seven days. Two TEXT JSON columns on summary_channel: publish_gap_distribution (histogram of inter-publish gaps in days) and day_of_week_trend (per-weekday twelve-week slope of channel views). A-18 layers three new columns on summary_channel (gini_views, gini_subs, library_half_life_days — the third counts top videos sorted desc by views needed to reach half of total channel views, populated as a Lorenz half-mass count) plus two trailing 28-day rolling Gini columns on summary_channel_daily (gini_views_28d, gini_subs_28d). The Coda chapter renders a hedged "{M} of {N} videos" callout from gini_subs against the Lorenz curve; /data renders a distribution-evolution line chart of the two daily Gini series. Observation-only voice across all surfaces; both extremes (concentrated vs even) are legitimate channel patterns.


created_at: 2026-04-28T12:00:00.000Z
description:
  A-19a — Wisdom canon foundation. Two new tables: wisdom_canon (50 rows loaded from scripts/wisdom-canon.yaml — one per conventional belief, with belief_text, calculation_doc, evidence_shape in {single-ratio, correlation, delta, share-distribution, count-tier, not-applicable}, and agrees_directionality in {positive, negative, neutral}) and wisdom_test (one verdict row per (test_id, snapshot_date) with status in {agrees, disagrees, inconclusive, not_currently_testable}, an optional inconclusive_reason that classifies into thin_sample / near_threshold, evidence_json, confidence, sample_size, prior_status, status_changed_today, and the not_yet_implemented flag the runner sets while the per-test impl files are still landing). New scripts/wisdom-runner.js loads the canon yaml on every build, populates wisdom_canon idempotently, and emits 50 wisdom_test rows: every requires_owner_mode / requires_cross_channel / not_currently_testable_reason entry short-circuits to status not_currently_testable; every testable entry with a per-test file at dashboard/src/lib/wisdom/tests/<test_id>.ts dispatches and writes a real verdict; every testable entry without an implementation file gets a placeholder row with not_yet_implemented = 1. Three anchor implementations land in this slice: frequency_cannibalization (single-ratio), first_24_48h_determines_fate (correlation), title_changes_reset_algo (delta). A-19b lands the remaining 26 testable implementations across multiple sessions; the architecture cannot regress because the runner already passes all 50 entries with the placeholder fallback.


created_at: 2026-04-28T13:00:00.000Z
description:
  A-23 — AVD confidence tier columns. summary_video, summary_traffic, and video_daily each gain an avd_confidence TEXT column (one of noise / low / medium / high) derived from the row's view sample size: noise < 10, low < 30, medium < 100, high otherwise. Mirrors the V10 ctr_confidence pattern but keyed on views (the denominator AVD averages over) instead of impressions. Persisting the tier alongside the value lets every UI surface that renders an AVD value wrap it in <AvdConfidencePip> — strikethrough on noise, muted on low, full on medium / high — without recomputing the threshold per call site. Same A-23 ships StageChip + VideoMiniStack visual chrome on /videos rows + comparison-band reuse on per-video charts; those are presentation-only and do not bump the schema.


created_at: 2026-04-28T14:00:00.000Z
description:
  A-25 — Honesty surface column. summary_channel_daily gains unattributed_channel_views INTEGER, populated post-insert as MAX(0, total_views - SUM(video_daily.views WHERE pre_publish_stub = 0)) per date. The column makes the per-date snapshot drift between channel-level views and the sum of per-video reporting rows readable directly from one column instead of recomputing every render. Powers the new <HonestyPanel> on /data (six sub-sections naming reporting lag, pre-publish stub rows, click-rate confidence distribution, AVD confidence distribution, unattributed impressions, and the channel-snapshot reconciliation chart) plus a one-line <HonestyDigest> in the Coda chapter on /. The digest reads "Data flows reliably through {cutoff}; per-source attribution is at {pct}%; click-rate sample tier today: {tier}." with a link to the full panel; the third clause omits when reconciliation is sparse. Observation-only voice; the surface is reference content, not a verdict.


created_at: 2026-04-28T15:00:00.000Z
description:
  A-30 — Calc hygiene + metric_metadata + indexes. New metric_metadata table (metric_id, definition, source_tables, computation_doc, confidence_method, last_modified) populated from scripts/metric-metadata.yaml at build time; consumed by /data tile tooltips so "How is this computed?" reads from a single source. Index audit: the eight Round-3 indexes (idx_svcd_video_date, idx_svcd_country_date, idx_sccd_country_date, idx_mzd_metric_date, idx_wt_status_date, idx_wt_test_date, idx_vsd_video_date, idx_cal_country_date) all use CREATE INDEX IF NOT EXISTS for build-rebuild idempotence. Time-zone documentation: data_status gains a reporting_timezone row pointing at America/Los_Angeles (PT) so the dashboard's honesty surfaces read the timezone from one source. Schema-history consistency check (scripts/schema-history-check.js) extends the prior tail-equality assertion with three new failure modes — version skip / gap, non-monotonic order, and position-by-position mismatch with a hard-coded EXPECTED_SCHEMA_HISTORY_VERSIONS sequence — so future schema arithmetic regressions fail the build with precise diagnostics.


created_at: 2026-04-29T00:00:00.000Z
description:
  L-16 — summary_detector_activity table. New per-detector aggregation table populated by scripts/build-detector-activity.js after dashboard/scripts/generate-snapshots.ts has written the insight_snapshot history. One row per detector_name with total_fires, first_fired_date, last_fired_date, highest_significance, and the snapshot_date that carried that highest_significance. Empty at clean-build time (insight_snapshot has no rows yet); populated when refresh-public.js completes its finalize step. The /help/detectors master list page (L-17) reads this table via getDetectorActivity() to render fired vs not-yet-fired groupings.


created_at: 2026-05-02T00:00:00.000Z
description:
  Batch 01 — traffic_daily gains per-source avg_view_duration_sec REAL and avg_view_pct REAL. The source v_video_traffic view now computes views-weighted AVD and AVP from reporting_traffic_metric at the (video_id, date, traffic_source_id) grain. Analytics-only rows and pre-publish stubs keep both columns NULL. The public build path and fixture writer both carry the two new columns between watch_time_minutes and impressions.


created_at: 2026-05-02T01:00:00.000Z
description:
  Batch 02 — summary_video_source_window. Per-(video_id, age_window, traffic_source_id) acquisition-quality mart: impressions, views, watch time, impression-weighted CTR, views-weighted AVD/AVP, watch_min_per_impression, ctr_confidence, and within-window impression/view/watch-time shares. Age windows: d0/d1/d2_7/d8_13/d14_29/d30_plus. Reads from public traffic_daily (pre_publish_stub = 0). Non-impression sources (Direct, External) legitimately carry NULL impressions and weighted_ctr.


created_at: 2026-05-02T02:00:00.000Z
description:
  Batch 02 — summary_video_geo_window. Per-(video_id, age_window, country_code) geography mart with stored geo_bucket (core | expansion | other). core = top countries reaching 60% of lifetime named-country views; expansion = summary_channel_country_trailing30 countries not already core; other = everything else including OT. Reads from summary_video_country_daily joined with video for age derivation; OT rows are always geo_bucket = other. Stores views, watch time, AVD, engaged views, subs, and within-window view/watch shares.


created_at: 2026-05-02T03:00:00.000Z
description:
  Batch 02 — summary_video_launch_window. Per-(video_id, age_window) early-life rollup: views, impressions, impression-weighted CTR, views-weighted AVD/AVP, watch time, subs gained, engaged views. Same age-window set as V44/V45. Reads from public video_daily (pre_publish_stub = 0). Windows with no volume signal (no views, impressions, or watch time) are omitted. Completes the three-mart current-analysis layer that stops per-video source/geo/launch reads from re-aggregating inline.


created_at: 2026-05-02T04:00:00.000Z
description:
  Batch 03 — summary_channel_catalog_pressure_daily. Per-date catalog pressure mart: trailing-7d impression shares by video age bucket (new <=7d / midlife 8-30d / library >30d), top-1 and top-3 video impression concentration, active_videos_7d, videos_published_14d, quiet_inventory_pct copy, and launch_pressure_index (composite of new-video share, catalog size, concentration, and publish-day flag). All share fields and launch_pressure_index are NULL when the trailing-7d impression denominator is zero; does not use lifetime or post-cutoff data for historical dates.


created_at: 2026-05-02T05:00:00.000Z
description:
  Batch 03 — summary_channel_source_geo_daily. Per-(date, traffic_source_id, country_code) acquisition geography mart with stored geo_bucket = core | expansion | other. Aggregated from src.reporting_traffic_metric with blank-country filter and pre-publish guard. OT folding uses the same default-on 5% share threshold as the existing country tables, applied within each (date, traffic_source_id) group. geo_bucket is channel-level: core = countries reaching 60% of lifetime named-country views; expansion = summary_channel_country_trailing30 countries not already core. OT rows are always geo_bucket = other. Stores views-weighted AVD/AVP. Does not contain subscriber columns.


created_at: 2026-05-02T06:00:00.000Z
description:
  V49 — summary_channel_state_log. Adds the new per-date channel-state backbone table with persisted v1 classifier outputs plus raw support features (source-share, launch-pressure, country concentration, recent-upload testing evidence). The table is rebuilt deterministically on every public DB build and mirrored in fixture generation so later route-context work can read one historical state spine instead of recomputing present-tense heuristics.


created_at: 2026-05-02T07:00:00.000Z
description:
  V50 — wisdom_verdict_daily. Adds a future-safe per-date wisdom history table keyed by (date, test_id). The table stores deterministic verdict status, evidence_json, confidence, sample_size, prior_status, status_changed_today, and not_yet_implemented for every canon belief on every historical summary_channel_daily date. Historical readers and wisdom_flip emission now read from this table instead of pretending the latest wisdom_test snapshot is enough for past dates.


created_at: 2026-05-02T08:00:00.000Z
description:
  V51 — summary_channel_weekly_letter. Adds one deterministic weekly-publication row per complete ISO week with headline, dek, body_markdown, and evidence_json derived from the state log, timeline, weekly metrics, and wisdom history. Powers /weekly, /weekly/[isoWeek], and the generated RSS feed.


created_at: 2026-05-05T00:00:00.000Z
description:
  V52 — summary_video_change_window. Adds the per-(video_id, pivot_date, pivot_kind) before/after rollup table that powers the per-video Eras section on /videos/[id] and the channel-scope rollup on /changes. Pivots come from era boundaries, summary_subscriber_event_daily (V55, when present), and pipeline/channel-notes.yaml. Each row carries 14-day before/after views, impression-weighted CTR (with sample sizes), retention, AVD, and sub-gain plus a two-proportion CTR z statistic kept internal-only and a verdict in {improved, worsened, inconclusive}. The verdict downgrades to inconclusive when either side cannot fit the full 14-day window.


created_at: 2026-05-06T00:00:00.000Z
description:
  V53 — summary_channel_stage_snapshot. Adds the six-row fixed-stage table (day1 / week1 / month1 / views_1k / subs_1k / monetization) that backs the homepage stage-snapshots strip. Each build writes all six rows; unreached stages carry NULL reached_date / day_number / per-row fact columns. The strip renders only when at least three stages have been reached (DECISIONS A.3); below the threshold the consumer returns null. editorial_note ships NULL — chrome reads its plain-language stage labels from dashboard/src/lib/labels.ts.


created_at: 2026-05-06T00:01:00.000Z
description:
  V54 — intentionally unused per CONTRACTS § V54. Reserved during scoping for the proposed summary_receipt table; the /receipts surface was cut at the Phase D review gate (GATE-DECISIONS D2 / D4) and the version number stays vacant so future per-version changelog rows on /data still render in order.


created_at: 2026-05-06T00:02:00.000Z
description:
  V55 — summary_subscriber_event_daily. Lifts subscriber-loss / surge events out of the channel_timeline JSON envelope into a queryable per-(date, video_id, kind) table. Per-video rows trigger when |net_subs| trips max(2, 0.02 × current_subscribers); channel-scope rows (video_id IS NULL) trigger at max(5, 0.05 × current_subscribers). The build-time consumer is V52 compute-change-windows (which reads sub_loss pivots); render-time consumers surface only inside per-video Eras sections via V52.


created_at: 2026-05-07T00:00:00.000Z
description:
  V56 — sample-aware intelligence layer. Adds source_age_pattern_daily, video_current_state, channel_signal_daily, and dashboard_briefing_daily so the public DB stores comparative observations with denominators, confidence tiers, and noise reasons. The homepage briefing now reads ranked DB findings instead of raw one-metric swings, and /data presents the SQLite file as an analyst-ready signal layer.


created_at: 2026-05-08T00:00:00.000Z
description:
  V57 — reach-only and covered zero days. Public builds now preserve active public video-days where YouTube reported impressions but no views, plus zero-signal days when both basic and reach reports covered the date. Source-level reach-only traffic rows are also preserved so impression and CTR analysis no longer loses no-click exposure.


created_at: 2026-05-09T00:00:00.000Z
description:
  V58 — creator metadata change ledger + multi-window/multi-metric verdicts + change aggregates. New creator_change_log table sanitizes the private video_change_log + channel_change_log into a categorical change_kind enum (description rewritten / extended / shortened / cleared / restored / set; tags added / removed / replaced / reordered / cleared / set; etc.) with NULL before/after for free-text fields and pass-through for already-public scalars (category_id, language codes, license, made_for_kids, country); custom_url is NULL it out for defense-in-depth. summary_video_change_window widens to 4 windows (7/14/28/56 days) × 5 metrics (views, impressions, ctr, retention, sub_rate) with per-metric verdicts plus a primary verdict mapped per pivot_field; pivot_kind gains "metadata"; pivot_field uses NOT NULL DEFAULT "" sentinel. New summary_channel_change_window mirrors the shape at channel scope. Five aggregate tables ship: summary_change_daily / weekly / monthly / summary_field_co_change / summary_edit_view_position. summary_video gains 8 edit-intensity columns (edit_count_lifetime / first_30d / after_30d, first_edited_at, last_edited_at, first_edit_lag_days, fields_ever_edited_json, edit_intensity_class). summary_channel_daily gains 4 cadence columns (edits_30d / 90d / 365d, edit_diversity_30d). New channel_event_public table holds owner-authored creator_event entries from pipeline/creator-events.yaml; creator_event and edit_burst rows emit into channel_timeline.


created_at: 2026-05-11T00:00:00.000Z
description:
  V59 — self-description layer (_about/_table_catalog/_column_catalog/_query_examples/_json_schema + 6 views); 9 new analytical tables; 10 new detectors; coherent naming convention with 30 table renames + 3 column renames; correctness fixes (creator-event privacy + orphan guard, EvidenceExportPanel SQL, dead cols, library_half_life rename, retention clamp, pipeline orchestration parity); dropped 4 unread edit-cadence columns (edits_30d / edits_90d / edits_365d / edit_diversity_30d) from summary_channel_metrics_daily; added 9 indexes covering frequent date / source / video joins plus the decay-model quiet-date lookup.


created_at: 2026-05-12T00:00:00.000Z
description:
  V60 — Phase 2 of the forecasting reshape: new summary_channel_forecast table (one row per (as_of_date, horizon_days, forecast_type) over horizons 7/14/30/90 and types subscribers/daily_views). OLS slope/intercept on a trailing 28-day window when channel cadence classifies as high, 56 days otherwise; bootstrap bands (B=200) from a seeded Mulberry32 PRNG keyed on the input (x, y) tuples so reruns over identical input produce byte-identical bands. The daily_views series is pre-smoothed with a rolling 7-day mean to suppress day-of-week noise. Confidence tier collapses to "insufficient" on samples shorter than 14 days, on bands wider than the point estimate, and on non-negative series whose lower bound dips below zero — surfaces honestly degrade to "too volatile to project usefully" copy at those points. Reader at dashboard/src/lib/forecast/channel.ts; first consumer is the +14-day view-band line on <ForecastHeadline>.


created_at: 2026-05-12T00:00:01.000Z
description:
  V61 — Phase 10 of the forecasting reshape: new summary_channel_regime_forecast table (one row per as_of_date). Pair-bootstrap OLS slopes on the trailing 21-day window of classifier-input features in event_channel_state_change; B=200 resamples with a seeded Mulberry32 PRNG so reruns over identical input produce byte-identical bands. Features whose bootstrap slope CI does not straddle zero AND whose |slope| clears the 1e-5 floor count as "moving meaningfully"; fewer than 2 such features collapses the row to confidence=insufficient with NULL projected_state. Otherwise the writer projects each numeric feature forward at its OLS slope (bounded01 / nonNegative / integer clamps applied) and re-classifies day-by-day for up to 60 days. Bootstrap-resampled slopes drive a distribution of (transition_day, projected_state) pairs; the modal projected state and the median / 10th / 90th percentile of transition days within that mode become projected_state / expected_days / expected_days_low / _high. contributing_features_json carries the (name, slope_per_day, current_value, ci_low, ci_high) tuples for each meaningfully-moving feature so consumers can render the driven-by-X-and-Y sentence honestly. classifier_version pins the v1 classifier so future bumps to the state-classifier (in compute-channel-state-log.js) are observable downstream. Reader at dashboard/src/lib/forecast/regime.ts; first consumer is the <WatchOutCard> on the home page.


created_at: 2026-05-13T00:00:00.000Z
description:
  V62 — Phase 2 of the Forecast Coherence Pass: usable_for_projection INTEGER NOT NULL DEFAULT 0 on summary_video_decay_model. The flag is 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 already shipped in detectDecayProjection — usability is the looser "input the catalog-share computation can read" bar, sitting as a strict subset of the per-video fire gate. The catalog-shape branch of detectDecayProjection now filters by usable_for_projection = 1, so noisy R² ≈ 0.05 fits stop being counted toward catalog_decay_concentration. The per-video fire gate at R² ≥ 0.7 is unchanged. Reader at dashboard/src/lib/db/analytical.ts; first consumer is dashboard/src/lib/insights/detectors/decay-projection.ts.


created_at: 2026-05-13T01:00:00.000Z
description:
  V63 — Phase 4 of the Forecast Coherence Pass: historical_band_coverage REAL and historical_hit_rate REAL on summary_channel_forecast. After the calibration ledger materializes for the build, compute-forecast-calibration.js back-writes per-(forecast_type, horizon_days) coverage = inside-band fraction over the trailing window of up to 10 resolved entries whose inside_band is non-null; hit_rate = fraction with error_pct ≤ 20 over the trailing window of up to 10 entries whose error_pct is non-null. Both columns stay NULL until at least 5 such entries exist on the cell (small-sample floor, picked over a stricter N≥10 so cells with 6–13 historical resolutions can self-report honestly). The dashboard reader at lib/forecast/channel.ts picks them up; assessForecastUsability demotes confident → directional when historical_band_coverage is non-null AND below 0.5, applying VOICE.md's calibration honesty rule at the per-(metric, horizon) cell rather than only the channel-wide strip.


created_at: 2026-05-13T02:00:00.000Z
description:
  V64 — Phase 2 of the Backfill Pass: snapshot_date TEXT NOT NULL extended onto the primary key of every entity-grain derived table so the public DB can carry rows for every historical reporting cutoff (not just the latest). 28 tables affected: summary_channel, summary_video, summary_channel_traffic, summary_video_traffic, summary_video_country_lifetime, summary_video_trajectory_cone, summary_video_nearest_peers, summary_video_decay_model, summary_threshold_projection, summary_era, video_era, summary_traffic_top_videos_by_source, summary_channel_country_trailing30, cohort_video_traffic_age_window, cohort_video_geography_age_window, cohort_video_launch_age_window, cohort_video_change_verdict, cohort_channel_change_verdict, summary_channel_cohort, summary_channel_country_cohort_stability, summary_channel_source_leading_indicator, summary_video_launch_context, video_current_state, event_video_phase_transition, event_creator_edit, app_detector_activity, summary_field_co_change, summary_edit_view_position. Two autoincrement-id event tables get stable event_key surgery: channel_timeline_event gains event_key TEXT NOT NULL (SHA-1 of event_type|event_date|detector|video_id|headline) with PK (event_key, snapshot_date); event_creator_edit extends its natural key with snapshot_date. summary_channel_forecast and summary_channel_regime_forecast already carry as_of_date in their PK and remain unchanged. Phase 2 still writes a single snapshot per build (snapshot_date = latestDataDate); Phase 3 wires the walker. snapshot_date matches the reporting cutoff at the time of computation (YouTube reporting lag of 2–3 days means snapshot_date IS NOT calendar date).


created_at: 2026-06-12T00:00:00.000Z
description:
  V65 — Stage 3 pool tables (R17 / D2.3; relaunch/specs/stage3.md §8). Two new app-layer derived tables computed from the consent-gated pool-input extract (the ONLY pool input the build sees — corpus/corpus-extract.js emits it; the build never reads Parquet, registry, or manifest): app_pool_cell_stat ((cell_key, metric, snapshot_date) — the withholding gate's bookkeeping plus lit aggregates: gate_state, rung, floored member_band, seeded-noise cell_median/cell_p25/cell_p75, and pooled_estimate stored only where publication is un-noised; withheld rows carry NULL in every value column; exactly one row per (metric, snapshot_date) — the subject's deciding cohort cell) and app_pool_named_peer ((peer_contributor_id, snapshot_date) — the named side-by-side rows: public-dashboard-class facts for every channel currently public AND pool-consented, identical in every channel's DB). Both tables are fully re-derived every build across all walker dates (TRUNCATE + rebuild — consent purge by construction, D-10) and are EMPTY when no pool-input extract is supplied (fixture and local builds). All stamps bind to asOfDate, never now(); the only randomness is the deterministic seeded plus/minus-2-percent noise, a pure function of the extract.


created_at: 2026-06-15T00:00:00.000Z
description:
  V66 — Stage 3 pool percentile/positioning rung (R20 / D1; relaunch/specs/stage3.md §1.6, §7 row 4). Adds four columns to app_pool_cell_stat for the highest aggregate rung, DARK until a cohort cell crosses the N≈100 positioning floor (POSITION_FLOOR=100): subject_percentile REAL (the subject channel's empirical Hazen-midrank percentile within its cohort cell, in (0,1) — its OWN standing, never a member's raw value), conformal_low REAL + conformal_high REAL (a distribution-free split-conformal prediction band over the cohort member values at ranks floor((alpha/2)(k+1)) / ceil((1-alpha/2)(k+1)), alpha=0.10 → 90% finite-sample coverage; both carry the same §3.2 seeded plus/minus-2-percent noise as cell_median/cell_p25/cell_p75 when the cell holds a private member), and trajectory_class TEXT (below_band | within_band | above_band — where the subject sits relative to the as-stored band; descriptive position only, no advice/causation/named-ranking). The rung lights only when the median rung lights AND k>=100 AND the subject is a metric-contributing member AND a width check anchored on the subject's OWN value passes (W <= 0.25*|subject value|); it falls back to the quartile/median magnitude rungs below the floor. All four columns are NULL on every median/quartile/withheld row and on every build below the floor. The real two-channel pool never reaches the floor, so every cell stays withheld/below-rung and these columns are NULL throughout — the single-channel fixture build writes no pool rows at all; only the table schema (hence the fingerprint) moves. Proven LIT vs a synthetic >=100-member extract and SILENT on the real N=2 shape (scripts/__tests__/pool-tables-build.test.js). POOL_AGGREGATES_ENABLED stays operator-gated; this lights nothing now. Determinism unchanged: every new value is a pure function of the extract; stamps still bind to asOfDate.


created_at: 2026-06-15T01:00:00.000Z
description:
  V67 — Stage 3 cross-channel twins (R20 / D4; relaunch/specs/stage3.md §7 row 3). New app-layer derived table app_pool_twin (grain (twin_rank, age_days, snapshot_date) — PK ends in snapshot_date per the backfill pattern), the cross-channel generalization of the within-channel nearest-peer base (summary_video_nearest_peers, k=5 NN cosine). For the subject channel it surfaces up to 3 real "opening curves" from the pool as concrete examples: the channels whose early observed cumulative-views trajectory SHAPE most resembles the subject's, by cosine distance over the opening curve. Each row is one curve point — columns cell_key (the deciding cohort cell), twin_rank (1..3, the NN order of the examples — never a standing of the subject in a cohort), twin_contributor_id / twin_handle / twin_title (the named PUBLIC twin), age_days + curve_value (the twin's own already-public cumulative-views opening at that age), distance (cosine NN distance to the subject, constant per twin), member_band (floored band of the cohort size — exact k NEVER stored), computed_at (= snapshot_date). JUXTAPOSITION ONLY: no median/quartile/percentile, no distributional/ranking/positioning claim. DARK until the subject's cohort cell reaches N≥10 (TWIN_FLOOR) AND POOL_AGGREGATES_ENABLED — the real two-channel pool emits ZERO twin rows, and the single-channel fixture build (no pool input) writes none at all, so only the new table's schema (hence the fingerprint) and _about.compute_version (the SHA over compute-*.js — the new compute-pool-twins.js module joins it) move. PRIVACY WALL #1 (DEC-3, non-negotiable): a private (handle-null) pooled channel is NEVER a twin — excluded from the selection pool entirely (never named, never an anonymous curve, since a distinct opening shape is identifying); private channels may count toward the N≥10 cohort floor only. Fully re-derived every build across all walker dates (TRUNCATE + rebuild — consent purge by construction, D-10); EMPTY when no pool-input extract is supplied; NOT registered in the snapshot-monotonicity gate. No seeded noise (a public channel's own curve is already public, like app_pool_named_peer). Determinism unchanged: a pure function of the extract, stable sort by (distance, twin contributor_id); all stamps bind to asOfDate, never now(). Proven LIT vs a synthetic ≥10-member extract and SILENT on the real N=2 shape, with a non-vacuous private-twin-nowhere killer test (scripts/__tests__/pool-tables-build.test.js).


created_at: 2026-06-17T00:00:00.000Z
description:
  V68 — audience-retention curve, snapshot-extended (R21 / Goal A; A2.2). New summary_video_retention_curve table (grain (video_id, elapsed_ratio, snapshot_date) — PK ends in snapshot_date per the backfill pattern) materializing the private video_retention_curve raw table (the A2.1 audienceRetention ingest) into the public DB. Two NON-monetary columns (yt-analytics.readonly): audience_watch_ratio (this video's own retention at that elapsed-time ratio) and relative_retention_performance (YouTube's own ATTRIBUTED peer-benchmark band, nullable when YouTube has no peer set). One curve point per (video, elapsed_ratio) for every snapshot on which the video was LIT. WITHHOLDING BY DEFAULT (R21, FB-3): a video emits rows only from the snapshot where its cumulative views first reach ≥100 — the ≥60s-duration half of the gate was applied upstream at ingest (A2.1), so a raw curve exists only for a ≥60s video and here we re-gate solely on the time-varying ≥100-views threshold read from summary_video.total_views at snapshot_date = asOfDate (already cutoff- and pre_publish_stub-correct). The raw curve YouTube returns is a single LIFETIME shape (there is no per-date historic retention curve to fetch), so under rewind-shows-current-app every lit snapshot carries the current curve — the snapshot dimension governs only WHETHER the surface was lit on that date, never a re-measured shape. NO monetary metric is read or stored. Self-heal: the raw table lives in the private pipeline DB, so fixture/local builds whose source was synthesized from a public fixture carry no raw retention table and the writer no-ops — the derived table is created EMPTY (only the new table schema, the V68 schema_version row, the catalog rows, and _about.compute_version move the fingerprint). Proven LIT vs a synthetic ≥100-view source carrying a raw curve AND SILENT on a tiny-channel source (scripts/__tests__/build-public-db-retention-curve.test.js). Determinism unchanged: stamps bind to asOfDate, never now(); byte-identical rebuilds. NOT registered in the snapshot-monotonicity gate (no cumulative column).


created_at: 2026-06-17T01:00:00.000Z
description:
  V69 — content-addressed thumbnail era refs (R22 / Goal C2; C2.1). Adds one nullable column thumb_ref TEXT to video_era AND summary_era: the content-addressed R2 ref (hash) of the stored image bytes that were live DURING that thumbnail era, so the /changes old-vs-new packaging render (C2.2) can resolve real before/after bytes rather than a thumbnail URL whose bytes have already rotted. Refs are threaded ONLY by extending the EXISTING public+active-filtered era-copy query (build-public-db.js video_era JOIN video WHERE is_active=1 AND privacy_status='public') — a private/unlisted video's eras are never selected, so its thumb_ref never reaches the public DB (consent boundary, invariant #3; pinned by scripts/__tests__/build-public-db-thumb-ref-correlation.test.js). The hash is CORRELATED to each era's boundary against the private video_change_log: an era that started at a thumbnail change takes new_thumb_ref of the change on its start_date; era 0 (no change at its start) takes old_thumb_ref of the first change (the change on end_date+1 day); the current era with no prior change takes the durable video.current_thumbnail_blob_ref — read from the same public-filtered video JOIN, never a fresh unfiltered read. Title eras and any thumbnail era whose bytes were never captured (pre-C1 history) carry NULL; the render fails open to the i.ytimg.com URL. thumb_ref is snapshot-invariant (an era's image is the same hash regardless of which snapshot views it — content-addressed), bound deterministically, byte-identical on rebuild. Self-heal: a pre-C1 / fixture-synthesized source (no video_change_log, no ref columns) yields thumb_ref = NULL everywhere via capability-gated SQL — so the fixture build moves only the new column schema, the V69 schema_version row, and the catalog rows (no compute-*.js touched → _about.compute_version unchanged). Guarded idempotent ALTER on both tables for the incremental-seed path. Proven correlated vs a synthetic source carrying thumbnail changes + refs AND private-ref-never-propagated (scripts/__tests__/build-public-db-thumb-ref-correlation.test.js).


created_at: 2026-06-19T00:00:00.000Z
description:
  V70 — video-grain forecast calibration (R24 / leg F, F2). New append-only event table forecast_video_calibration_log (grain (forecast_made_on, video_id, metric_type)) — the video-grain mirror of forecast_calibration_log, giving the per-video decay/landing projection its own "how reliable have these been?" receipt. Side-cart-backed: source of truth is the git-tracked public/data/forecast-video-calibration-log.json, the table is a build-derived view of its latest 180 days (excluded from --rebuild-all truncation, rebuilt from the ledger every build). One row per (video_id, metric_type) OUTCOME — the EARLIEST genuine FORWARD fit (snapshot_date < target_date) of each day-30/60/90 projection from summary_video_decay_model.predicted_views_day_30/60/90, EXCLUDING in-sample retrodictions (a fit made on/after the outcome date evaluates the curve on its own training data — not a projection the product shows) and the correlated re-fits of the same outcome, so the pooled hit-rate counts independent forward forecasts, not curve-evaluations: forecast_made_on = the earliest forward fit snapshot, target_date = video published_at + N days, predicted_value = the projected DAILY view-rate at age N. Resolution fills actual_value (the realized daily rate at age N — the rolling 7-day mean of video_daily.views ending at days_since_publish = N, the SAME smoothed daily series the model predicts, so predicted vs actual are apples-to-apples, mirroring how forecast_calibration_log resolves its smoothed daily_views forecast), error_pct, and hit once the build cutoff reaches target_date. The decay model emits POINT projections (no band), so the credibility signal is HIT-RATE based: hit = 1 when |actual − predicted| / |predicted| ≤ 0.20 (the same ±20% stake the channel hit-rate uses); there is no inside_band. predicted_value/actual_value are INTEGER and the only REAL (error_pct) is computed from two integers (no Math.exp/log in the compute), so the column is cross-platform byte-stable with no quantization needed. The video-detail receipt is computed in the reader (dashboard/src/lib/forecast/video-calibration.ts) as one CHANNEL-WIDE statement across all videos × horizons — no back-write into any existing table, so only the new table (plus the catalog/_about/schema_version bookkeeping) moves the fingerprint. Self-silences (renders nothing) until at least 5 resolved cases — withholding is the default on the young featured channel. Standing wipe policy keyed on an INDEPENDENT video_calibration_logic_version stamp (the ledger tracks the decay model + this build resolution logic, not the channel forecast FORECAST_LOGIC_VERSION). NOT snapshot-extended (no snapshot_date column, like forecast_calibration_log), so absent from the snapshot-coverage / snapshot-monotonicity gates. Wired into scripts/build-public-db.js after computeDecayHistory; unit-tested in scripts/__tests__/forecast-video-calibration.test.js. Determinism: pure, stamps bind to asOfDate, byte-identical rebuilds.


created_at: 2026-06-22T00:00:00.000Z
description:
  V71 — Weekly Letters removed. Drops the app_channel_weekly_letter table (the per-ISO-week "letter to a creator" publication — iso_week, week_start, week_end, headline, dek, body_markdown, evidence_json) and its idx_scwl_week_end index. The home Weekly Letters disclosure, the /weekly and /weekly/[isoWeek] archive surfaces, and the generated weekly RSS feed (generate-weekly-feed.js) are retired alongside the builder (compute-weekly-letters.js / the longitudinal-wisdom writeWeeklyLetters call); the bare /weekly path now 301s to the channel home. The fixture cold build produces one fewer table and the public DB schema_version advances to 71. No other table or column changes — a pure drop.


created_at: 2026-06-30T00:00:00.000Z
description:
  V72 — lifetime accumulators for the exempt forward-tail ledgers (Time-Machine Reckoning, Goal D / D0). The A3 de-walk bounded app_wisdom_verdict_daily and app_insight_snapshot to a ~90-day forward tail, so any reader that aggregated the WHOLE ledger for a lifetime figure went window-relative and LIED once a channel is older than the window (getWisdomHistorySummaries flip_count/first_date/last_changed; /help/detectors first_fired/total_fires — the B3 interim window-floor caveat). Two new NON-snapshot-extended accumulator tables survive the prune by folding each new date forward on a `through_date` high-water mark: (1) app_wisdom_flip_lifetime (PK test_id — flip_count, first_seen_date, last_changed_date, current_status, current_confidence, current_streak_days, through_date), written build-side in build-public-db.js by compute-wisdom-flip-lifetime.js right after app_wisdom_verdict_daily; (2) app_detector_lifetime (PK (detector_name, scope) — total_fires, first_fired_date, last_fired_date, highest_significance, through_date; scope in {slot, logical} carries BOTH the raw prose-routing-slot aggregation and the recovered logical-detector aggregation), written engine-side in dashboard/scripts/generate-snapshots.ts after app_insight_snapshot. Both are EXCLUDED from --rebuild-all truncation so a compute-drift rebuild (seed restored) preserves the accumulated lifetime; a from-scratch reconcile with no seed re-seeds from the bounded tail. Deterministic on the always-cold fixture (no prior lifetime row → seed from the tail; on a young channel tail == full history so lifetime == window). app_wisdom_flip_lifetime moves the BUILD fingerprint (populated at build time); app_detector_lifetime moves the ENGINE-output fingerprint (populated after generate-snapshots; added to the engine-output gate table set) and appears as an empty table in the BUILD fingerprint. getWisdomHistorySummaries re-points to app_wisdom_flip_lifetime; getDetectorActivity / getLogicalDetectorActivity overlay lifetime total_fires + first_fired onto their window rollups; /help/detectors copy reverts to an honest lifetime phrasing.


created_at: 2026-07-07T00:00:00.000Z
description:
  V73 — cross-channel twins retired (sis / Goal A, A5; operator-ratified 2026-07-07: twins are OUT entirely). Drops the V67 app_pool_twin table and its idx_aptw_snapshot_date index end-to-end: the compute module (compute-pool-twins.js), the writer seam in write-pool-tables.js, the dashboard reader/component, and the catalog rows all go with it. The named 1:1 "channels whose opening curve looks like yours" surface never fit the pool's model — it was DARK behind its own N≥10 floor from the day it shipped — and the named side-by-side now lives on exactly ONE surface, the /channels directory (app_pool_named_peer, D-17), which is UNTOUCHED by this drop. A defensive DROP TABLE IF EXISTS runs in createPoolTables so a seeded pre-V73 DB that ever skips the cold rebuild sheds the orphan table; fresh builds simply create one fewer table. This entry also states the pool's no-floors rung model plainly (the Anti-Gating Law, operator-ratified 2026-07-01/02, implemented sis/A1): the rungs are DATA-SHAPE rules only — a cohort with the subject as a member renders the FULL percentile grammar (quartiles + Hazen percentile + conformal band + trajectory class) from k=2; a cohort without the subject renders the quartile band from k=2; k=1 is a self-mirror and stays render-silent. The ONLY numeric gate anywhere in the pool is the definition of a pool itself (≥2 members / ≥2 videos across ≥2 channels). There is no k-floor, no positioning floor, no width check, no dominant-member cap, and no seeded noise; every published statistic is the raw order statistic, and honesty is carried by the drawn band plus the floored member_band attribution, never by withholding. This supersedes the V66 narration (its N≈100 POSITION_FLOOR, width check, and seeded-noise description are history, kept verbatim above per the append-only rule) and the V67 twins narration the same way.


created_at: 2026-07-08T00:00:00.000Z
description:
  V74 — milestone calibration feedback (sis / Goal B, B2): the most-read number gets a track record. summary_threshold_projection gains three nullable REAL columns — band_widen_k_low, band_widen_k_high, historical_crossing_coverage — back-written after the walker by the autotune third stage (compute-forecast-autotune.js#applyMilestoneAutotune), mirroring summary_channel_forecast's band_widen_k_* / historical_band_coverage columns. Mechanism: every published milestone row now enters the SAME calibration side-cart the arc uses (forecast_calibration_log / public/data/forecast-calibration-log.json) with documented reuse semantics and NO ledger DDL change — forecast_type = milestone_subscribers | milestone_cumulative_views | milestone_video_count, horizon_days carries the THRESHOLD VALUE (the within-type discriminator; a days-ahead horizon on arc rows), and predicted_value/_low/_high are the projected DAYS-OUT to the crossing (integer date-diffs of the published dates). Resolution: actual_value = days from forecast_made_on to the first recorded date the metric's cumulative series (subs_total_eod spine / backward-anchored views + video-count cumsums) crosses the threshold; an overdue-uncrossed entry (elapsed > predicted_high, no crossing) scores inside_band = 0 immediately — the C2 honest-MISS principle, never a silent denominator exit — with actual_value left open until the crossing lands (every materialized field is resolution-day-independent, which keeps the seeded-vs-cold gate's allowlist untouched). The backfill walker (backfill-forecast-calibration.js#backfillMilestoneCalibration) replays milestone projections per historical origin so crossing coverage exists from day one after the standing side-cart wipe (FORECAST_LOGIC_VERSION 4→5 arms it: the entry schema changed). Autotune back-write: widened projected_date_low/high in days-space (unwind-idempotent, floor at 0 days, dates rounded to whole days so re-application is an exact fixpoint), confidence recomputed from the FINAL stored dates, and historical_crossing_coverage = trailing-window inside-band fraction (COVERAGE_WINDOW=10, NULL below COVERAGE_MIN_N=5 — on the live channels these stay NULL for months by design: a safety net, not a day-one actor). Dashboard: the threshold reader carries the new columns and assessThreshold demotes confident → directional when historical_crossing_coverage < 0.5, composing with the existing calibration cap exactly like the arc's demoteByCoverage. Seeded-path migration: PRAGMA-checked ALTER TABLE ADD COLUMN in the writer's writeSchema (signal_family precedent) so the live incremental build gains the columns without a cold rebuild.


# data_status

metric_type: reporting_timezone
source: America/Los_Angeles
contributor_id: c-1cb6979849b44afd8a7d
notes:
  YouTube reporting rolls up to calendar days in PT (America/Los_Angeles), not your local timezone. A "day" in this dataset is a calendar day in PT.


# traffic_source

api_name: NO_LINK_OTHER
display_name: Direct / Unknown
category: direct
description:
  Direct visits or unknown sources. Catch-all when YouTube can't attribute the view to a specific surface.


api_name: ANNOTATION
display_name: Cards / Annotations
category: interactive
description:
  Legacy in-video annotation overlay (deprecated by YouTube — surfaces only on archival annotated videos).


# channel_timeline_event

event_key: 6ddf8f04fd9703c6
date: 2026-07-13
event_type: rank_change
detector: channel_parity
direction: notable
snapshot_date: 2026-07-14
evidence_json:
  {"kind":"channel_parity","prior_leader":"videos","new_leader":"subscribers","prior_value":0,"new_value":7,"parity_kind":"subs_eq_videos","video_count":1}


event_key: 96be75c47e7e59d9
date: 2026-07-13
event_type: rank_change
detector: channel_parity
direction: notable
snapshot_date: 2026-07-14
evidence_json:
  {"kind":"channel_parity","prior_leader":"5× videos","new_leader":"subscribers","prior_value":0,"new_value":7,"parity_kind":"subs_eq_5x_videos","video_count":1}


# metric_metadata

metric_id: temperature_score
source_tables: summary_channel_metrics_daily.temperature_score
confidence_method: NULL when fewer than 35 baseline days exist.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  A 0–100 weighted blend of trailing channel performance vs the channel's prior-90-day baseline; 50 reads as the channel's own normal.

computation_doc:
  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 "your normal", 60 as a warmer week than typical for this channel, 40 as a cooler week than typical. Both extremes are legitimate — the value is observation, not verdict.


metric_id: country_diversity_score
definition: Simpson diversity index of the channel's top-country shares per date.
source_tables: summary_channel_metrics_daily.country_diversity_score
confidence_method: NULL when fewer than 35 baseline days exist.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Simpson form 1 - sum(share^2) over the per-date top-country shares from summary_channel_country_daily. 0 reads as "all views from one country"; 1 reads as "spread evenly across all countries". Computed daily; NULL on dates with fewer than 35 days of baseline.


metric_id: algorithmic_dependency_index
confidence_method: NULL when no traffic rows exist for the channel.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Channel-wide share of views from algorithmic discovery sources (Browse, Suggested, Search) versus sticky sources (Direct, Channel Page, Playlist, End Screen, link-clicks, subscriber notifications).

source_tables:
  summary_channel.algorithmic_dependency_index, summary_channel_metrics_daily.algorithmic_dependency_index

computation_doc:
  1.0 minus the channel-wide sticky-traffic ratio. Sticky sources are those where the viewer chose to come back: Direct, Channel Page, Playlist, End Screen, link-clicks from off-platform, subscriber notification. Algorithmic sources are everything else (Browse, Suggested, Search, advertising, hashtags, shorts feed). 1.0 = every view came from algorithmic discovery; 0.0 = every view came from a sticky source.


metric_id: sticky_traffic_ratio
definition: Per-video share of total views from sticky (non-algorithmic) traffic sources.
source_tables: summary_video.sticky_traffic_ratio
confidence_method: NULL when no traffic rows exist for the video.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  SUM(traffic.views WHERE is_sticky=1) / total_views, on a per-video basis. NULL when the video has no traffic rows. Both extremes are legitimate channel patterns; the value is observation, not verdict.


metric_id: audience_persistence_pct
definition: Share of last week's (source, country) audience buckets that also appeared the prior week.
source_tables: summary_channel_metrics_daily.audience_persistence_pct
confidence_method: NULL on dates that are not ISO week-ending.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Computed as a weekly comparison: |intersection of last-week and prior-week (source, country) buckets| / |last-week buckets|. Written only on ISO week-ending dates; NULL on every other date. High persistence reads as "the same audience returned"; low persistence reads as "a different audience showed up."


metric_id: audience_replacement_rate
definition: Share of last week's (source, country) audience buckets that did NOT appear the prior week.
source_tables: summary_channel_metrics_daily.audience_replacement_rate
confidence_method: NULL on dates that are not ISO week-ending.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Complement of audience_persistence_pct over the same weekly window. High replacement reads as "fresh discovery"; low replacement reads as "the same audience returned." Both states are legitimate channel patterns at different ages.


metric_id: anomaly_score_composite
definition: Sum of absolute z-scores across tracked daily metrics; one row per date.
source_tables: summary_channel_metrics_daily.anomaly_score_composite, app_metric_zscore_daily
confidence_method: NULL until each input metric clears its 28-day baseline floor.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  SUM(ABS(zscore)) per date across the five tracked metrics in app_metric_zscore_daily (daily_views, weighted_ctr, weighted_avd, sub_gain_rate, total_impressions). Each input z-score is computed against a prior 28-day baseline; baseline window must hold at least 28 non-null values or the input z-score stays NULL. The composite is observation-only — not a "risk score" or "danger meter."


metric_id: engagement_rate_composite
definition: Weighted engagement events per total view, with comments and shares weighted higher than likes.
source_tables: summary_video.engagement_rate_composite, engagement_weights
confidence_method: NULL when total_views = 0.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  (likes × likes_weight + comments × comments_weight + shares × shares_weight) / total_views. Default weights: likes=1, comments=5, shares=3 (per the engagement_weights table). NULL when total_views = 0.


metric_id: engagement_velocity_24h
definition: Total engagement events (likes + comments + shares) in the first 24 hours after publish.
source_tables: summary_video.engagement_velocity_24h
confidence_method: NULL until the publish day has been reported.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  SUM(likes + comments + shares) from video_daily rows where the date matches the publish day. Since YouTube reporting rolls up to UTC days, "first 24 hours" approximates to "the publish day" — a video published at 22:00 UTC will show only the 2 hours before midnight here.


metric_id: engagement_velocity_7d
definition: Total engagement events (likes + comments + shares) in the first 7 days after publish.
source_tables: summary_video.engagement_velocity_7d
confidence_method: NULL until day 6 has been reported.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  SUM(likes + comments + shares) from video_daily rows in days 0-6 after publish (inclusive of publish day). NULL until day 6 has been reported.


metric_id: gini_views
definition: Library-wide Gini coefficient across video view totals; 0 = even, 1 = concentrated on one video.
source_tables: summary_channel.gini_views, summary_channel_metrics_daily.gini_views_28d
confidence_method: NULL when fewer than two videos hold positive views.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Standard Gini coefficient computed across summary_video.total_views for public videos. NULL when fewer than two videos hold a positive value (the formula is undefined). 0 reads as "views spread evenly across the catalog"; 1 reads as "one video carries everything." A higher Gini is common for channels with one or two breakout videos and is observation, not verdict.


metric_id: gini_subs
source_tables: summary_channel.gini_subs
confidence_method: NULL when fewer than two videos hold positive subs.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Library-wide Gini coefficient across video subscriber-gain totals; 0 = even, 1 = concentrated on one video.

computation_doc:
  Standard Gini coefficient computed across summary_video.total_subs_gained for public videos. Same shape as gini_views but for subs gained. NULL when fewer than two videos hold a positive value.


metric_id: library_half_mass_video_count
definition: Smallest top-N video count whose total views sum to at least half of channel total views.
source_tables: summary_channel.library_half_mass_video_count
confidence_method: NULL when no public video has positive views.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Lorenz half-mass count: sort summary_video desc by total_views, walk the cumulative sum, return the count at which the running total first reaches >= 50% of channel total.


metric_id: avd_confidence
source_tables: summary_video.avd_confidence, summary_video_traffic.avd_confidence, video_daily.avd_confidence
confidence_method: This column IS the confidence tier; UI surfaces the noise tier with strikethrough rendering.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Sample-size confidence tier for the row's average view duration; one of noise / low / medium / high.

computation_doc:
  Derived from the row's view sample size — the denominator AVD averages over. Noise tier when views < 10 (one or two viewers' watch time, statistically meaningless); low when views < 30; medium when views < 100; high otherwise. Mirrors the V10 ctr_confidence pattern but keyed on views instead of impressions.


metric_id: ctr_confidence
definition: Sample-size confidence tier for the row's click-through rate; one of noise / low / medium / high.
source_tables: summary_video.ctr_confidence, traffic_daily.ctr_confidence, video_daily.ctr_confidence
confidence_method: This column IS the confidence tier; UI surfaces the noise tier with strikethrough rendering.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Derived from the row's impressions sample size. Noise tier when impressions < 50; low when < 250; medium when < 1000; high otherwise. The thresholds are calibrated so that a noise-tier CTR is statistically meaningless (one or two clicks against a tiny denominator).


metric_id: ctr_quadrant
confidence_method: noise quadrant is the inclusion-failure bucket.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Per-video position relative to the channel's CTR and retention medians; one of winner / clicked-not-watched / watched-not-clicked / quiet / noise.

source_tables:
  summary_video.ctr_quadrant, summary_channel.median_video_ctr, summary_channel.median_video_retention

computation_doc:
  Compares the video's weighted_ctr and avg_retention_pct to the channel-internal medians. Winner = above median on both axes; quiet = below median on both. The noise bucket short-circuits inclusion when ctr_confidence = noise OR total_views < 30 (sample too thin to read either axis honestly).


metric_id: unattributed_channel_views
definition: Per-date drift between the channel-level views snapshot and the sum of per-video views.
source_tables: summary_channel_metrics_daily.unattributed_channel_views
confidence_method: Reconciliation chart shows a sparse fallback below 14 days of history.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  MAX(0, summary_channel_snapshot_daily.total_views - SUM(video_daily.views WHERE pre_publish_stub = 0)) per date. Reads >0 when the channel-snapshot total exceeds the sum of attributed per-video reporting (e.g. deleted videos, late attribution). Floored at zero so reporting overcounts (per-video sum exceeds channel snapshot — also possible during transient lag) don't render as negative drift.


metric_id: country_hhi
definition: Sum-of-squared-shares across the video's per-country views, scaled by 10000.
source_tables: summary_video.country_hhi
confidence_method: NULL when no country-attributed views exist.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Standard Herfindahl-Hirschman index over the coarsened summary_video_country_lifetime rows for the video. NULL when the video has zero country-attributed views. Mirrors source_hhi shape; pairs the country dimension with traffic-source concentration on two independent axes.


metric_id: dominant_country_share
definition: Per-video share (0..1) of total views from the top-by-share country.
source_tables: summary_video.dominant_country_share, summary_video.dominant_country_code
confidence_method: NULL when no country-attributed views exist.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  The single highest country's share of the video's total views, with the country code in the sibling dominant_country_code column. Computed on the coarsened summary_video_country_lifetime distribution (countries below 5% per-video share are bucketed into "OT" / Other).


metric_id: country_audience_match_score
source_tables: summary_video.country_audience_match_score
confidence_method: NULL when no well-retaining channel distribution can be built.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Cosine similarity between this video's country distribution and the channel-wide well-retaining country distribution.

computation_doc:
  The channel-wide well-retaining distribution is built by weighting each country by views from videos that retain at or above the channel median. The score is the cosine similarity (0..1) between this video's country shares and that distribution. 1.0 = the video's audience mirrors the channel's well-retaining countries exactly; 0.0 = no overlap.


metric_id: country_concentration_with_weak_retention
source_tables: summary_video.country_concentration_with_weak_retention
confidence_method: NULL when avg_avd_sec is unavailable.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  1 when one country holds >= 40% of a video's views AND that country watches >= 30% shorter than the video overall.

computation_doc:
  Boolean flag (0/1, NULL when avg_avd_sec is unavailable). Catches the pattern where a single country dominates the audience but retains less than the video as a whole — observation, not verdict. Both extremes (concentration with strong retention, dispersion with weak retention) are legitimate channel patterns.


metric_id: lifecycle_stage
source_tables: summary_video.lifecycle_stage, summary_video_stage_daily.stage
confidence_method: pre_publish stage means the video has no reported days yet.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Most recent lifecycle stage classification for the video; one of pre_publish / debut / growth / peak / tail / quiet / re_emerged / zombie / dormant.

computation_doc:
  Classified by a deterministic JS pass over video_daily anchored on summary_video.peak_views_day. Reads from summary_video_stage_daily for the latest date the video has reporting for. The stages partition a video's lifetime into recognizable shapes.


metric_id: tail_half_life_days
definition: Days from peak views day to the day rolling views fell to half of peak.
source_tables: summary_video.tail_half_life_days
confidence_method: NULL until the video has decayed to half-peak.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  From the video's peak_views_day, walks forward until the trailing-7-day rolling views falls to <= 50% of peak_views_value. The day count is written here. NULL when the video hasn't yet decayed to half-peak (or hasn't peaked at all).


metric_id: source_hhi
definition: Sum-of-squared-shares across the video's per-source views, scaled by 10000.
source_tables: summary_video.source_hhi
confidence_method: NULL when no traffic rows exist.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Standard Herfindahl-Hirschman index over the per-(video, source) shares from summary_video_traffic. NULL when no traffic rows exist. Pairs with country_hhi to characterize concentration on two independent axes.


metric_id: imp_to_view_ratio
definition: Total impressions per view; ratio of how many shown thumbnails it took to earn one view.
source_tables: summary_video.imp_to_view_ratio, summary_channel.imp_to_view_ratio_median
confidence_method: NULL when total_views = 0.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  total_impressions / total_views. NULL when total_views = 0. The channel-wide median is on summary_channel.imp_to_view_ratio_median for the per-video comparison.


metric_id: avd_drift_sec
source_tables: summary_video.avd_drift_sec
confidence_method: NULL when either window lacks reported days.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Delta between the video's last-7-days average view duration and its first-3-days average view duration, in seconds.

computation_doc:
  last_7d_avd_sec - first_3d_avd_sec. Positive = the audience is watching longer than early viewers did; negative = retention has fallen since publish. NULL when either window can't be measured.


metric_id: late_growth_pct
definition: Share of total views accumulated after day 30 from publish.
source_tables: summary_video.late_growth_pct
confidence_method: NULL when video is younger than 30 days.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  SUM(video_daily.views WHERE day_offset >= 30) / total_views. NULL when total_views = 0 or the video is younger than 30 days. High late-growth reads as "evergreen"; low late-growth reads as "all the views came in the first month." Both are legitimate.


metric_id: pre_publish_stub
source_tables: video_daily.pre_publish_stub, traffic_daily.pre_publish_stub
confidence_method: This column IS the data-quality flag.
last_modified: 2026-04-28T15:00:00.000Z
definition:
  Flag (0/1) on every per-video reporting row indicating whether the row sits before the video's published_at.

computation_doc:
  Set to 1 when the row's date is earlier than SUBSTR(published_at, 1, 10). Every summary_* CTE that aggregates over per-video reporting must include WHERE pre_publish_stub = 0 so the seven-row stub artifact (NULL impressions, 0 views) doesn't poison aggregations. The flag itself stays in the DB for downstream provenance.


metric_id: temperature_score_baseline
definition: Reference value for the channel temperature; always 50.
source_tables: summary_channel_metrics_daily.temperature_score
confidence_method: Always 50; not data-driven.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  Temperature is calibrated so 50 reads as "your normal" — the prior-90-day baseline midpoint. Drift in either direction is informational, not a verdict. Use 50 as the visual reference line on temperature surfaces, not zero.


metric_id: reporting_timezone
definition: The timezone YouTube reporting uses for per-day rollups.
source_tables: data_status.notes (metric_type = 'reporting_timezone')
confidence_method: Stable per YouTube's API contract.
last_modified: 2026-04-28T15:00:00.000Z
computation_doc:
  All dates in the public DB are calendar days in YouTube's reporting timezone (America/Los_Angeles, PT), not the visitor's local timezone. A "day" here is a calendar day in PT — a video published at 22:00 PT on Apr 27 will accumulate that day's reporting under the date 2026-04-27, even for viewers in UTC+12.


# video_current_state

date: 2026-07-14
video_id: 0VzFqfgJIT8
current_state: no_read
state_reason: Views and impressions are too thin to compare this video honestly.
confidence_tier: noise
noise_reason: below_minimum_sample
lifecycle_stage: peak
snapshot_date: 2026-07-14
evidence_json:
  {"days_since_publish":375,"views_to_date":0,"impressions_to_date":0,"views_7d":0,"impressions_7d":0,"day7_views_to_date":0,"late_views_to_date":0}


# _about

key: description
value:
  Sibling artifact to ytpool-public.db. Same anonymized 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.


key: channel_avatar_url
value:
  https://yt3.ggpht.com/VLK_e62sMThhnWCXJe9gtoJWio5zABi1c8dffWbdutBQyEdft1F5wUJoi58tWtxVK6aqiNxl=s800-c-k-c0x00ffffff-no-rj


# _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: v001
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":"v003","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. Limited today — historical_band_coverage is back-written only for the latest snapshot, so the streak meaningfully reads as 0 or 1 on past snapshots; a follow-up will make coverage per-snapshot so the streak grows across history.


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


table_name: summary_channel_regime_forecast
column_name: current_state
type: TEXT
unit: enum
example_value: crowded_launch
definition:
  The 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: v003
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.
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.


