Docs/Skills and code/src/
src/analysis/within-channel-performance-schema.js
export const WITHIN_CHANNEL_SCHEMA_VERSION = "within-channel-performance-schema-v1";
export function ensureWithinChannelPerformanceSchema(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS within_channel_analysis_runs (
run_id TEXT PRIMARY KEY,
generated_at TEXT NOT NULL,
analysis_version TEXT NOT NULL,
channels_analyzed INTEGER NOT NULL,
linked_unique_channel_video_pairs INTEGER NOT NULL,
eligible_video_records INTEGER NOT NULL,
reliable_dated_video_records INTEGER NOT NULL,
age_cohort_count INTEGER NOT NULL,
config_json TEXT NOT NULL,
summary_json TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS within_channel_channel_profiles (
run_id TEXT NOT NULL,
channel_url TEXT NOT NULL,
channel_name TEXT,
linked_unique_video_count INTEGER NOT NULL,
eligible_view_count INTEGER NOT NULL,
missing_view_count INTEGER NOT NULL,
subscriber_count REAL,
subscriber_count_precision TEXT,
declared_video_count_precision TEXT,
exact_view_count INTEGER NOT NULL,
approximate_view_count INTEGER NOT NULL,
declared_video_count REAL,
catalogue_coverage_ratio REAL,
eligible_catalogue_coverage_ratio REAL,
linked_view_coverage_ratio REAL,
sample_assessment TEXT NOT NULL,
median_views REAL,
p25_views REAL,
p75_views REAL,
p90_views REAL,
view_iqr REAL,
view_mad REAL,
log_view_median REAL,
log_view_mad REAL,
sampled_view_total REAL,
top_1_view_share REAL,
top_5_view_share REAL,
top_10_view_share REAL,
twice_median_count INTEGER NOT NULL,
twice_median_share REAL,
reach_threshold_views REAL,
reach_threshold_count INTEGER NOT NULL,
reach_threshold_share REAL,
reliable_dated_view_count INTEGER NOT NULL,
reliable_dated_view_share REAL,
median_views_per_day REAL,
p90_views_per_day REAL,
oldest_reliable_published_at TEXT,
newest_reliable_published_at TEXT,
reliable_publication_span_days REAL,
outlier_candidate_count INTEGER NOT NULL,
strong_outlier_count INTEGER NOT NULL,
age_adjusted_outlier_candidate_count INTEGER NOT NULL,
age_adjusted_strong_outlier_count INTEGER NOT NULL,
outlier_candidate_share REAL,
strong_outlier_share REAL,
age_adjusted_outlier_candidate_share REAL,
age_adjusted_strong_outlier_share REAL,
warnings_json TEXT NOT NULL,
PRIMARY KEY (run_id, channel_url),
FOREIGN KEY (run_id) REFERENCES within_channel_analysis_runs(run_id)
);
CREATE TABLE IF NOT EXISTS within_channel_video_outliers (
run_id TEXT NOT NULL,
channel_url TEXT NOT NULL,
channel_name TEXT,
video_id TEXT NOT NULL,
title TEXT,
url TEXT,
view_count REAL,
view_count_precision TEXT,
view_count_source TEXT,
view_observed_at TEXT,
published_at TEXT,
published_at_precision TEXT,
published_at_normalization_method TEXT,
date_reliability TEXT NOT NULL,
duration_seconds REAL,
duration_precision TEXT,
category TEXT,
age_days REAL,
age_cohort TEXT NOT NULL,
views_per_day REAL,
channel_sample_size INTEGER NOT NULL,
channel_dated_sample_size INTEGER NOT NULL,
channel_median_views REAL,
lifetime_relative_ratio REAL,
lifetime_log_robust_z REAL,
lifetime_outlier_label TEXT NOT NULL,
lifetime_outlier_method TEXT NOT NULL,
channel_median_views_per_day REAL,
age_adjusted_relative_ratio REAL,
age_adjusted_log_robust_z REAL,
age_adjusted_outlier_label TEXT NOT NULL,
age_adjusted_outlier_method TEXT NOT NULL,
age_cohort_video_count INTEGER NOT NULL,
age_cohort_median_views_per_day REAL,
age_cohort_relative_ratio REAL,
age_cohort_benchmark_status TEXT NOT NULL,
evidence_status TEXT NOT NULL,
quality_flags_json TEXT NOT NULL,
PRIMARY KEY (run_id, channel_url, video_id),
FOREIGN KEY (run_id, channel_url) REFERENCES within_channel_channel_profiles(run_id, channel_url)
);
CREATE TABLE IF NOT EXISTS within_channel_age_cohort_benchmarks (
run_id TEXT NOT NULL,
age_cohort TEXT NOT NULL,
eligible_video_count INTEGER NOT NULL,
median_views_per_day REAL,
p90_views_per_day REAL,
benchmark_status TEXT NOT NULL,
PRIMARY KEY (run_id, age_cohort),
FOREIGN KEY (run_id) REFERENCES within_channel_analysis_runs(run_id)
);
CREATE TABLE IF NOT EXISTS within_channel_queue_evidence (
run_id TEXT NOT NULL,
channel_url TEXT NOT NULL,
channel_name TEXT,
queue_name TEXT NOT NULL,
integration_status TEXT NOT NULL,
score_effect REAL NOT NULL DEFAULT 0,
candidate_features_json TEXT NOT NULL,
evidence_refs_json TEXT NOT NULL,
interpretation TEXT NOT NULL,
PRIMARY KEY (run_id, channel_url, queue_name),
FOREIGN KEY (run_id, channel_url) REFERENCES within_channel_channel_profiles(run_id, channel_url)
);
CREATE INDEX IF NOT EXISTS idx_within_channel_profiles_median
ON within_channel_channel_profiles(run_id, median_views DESC);
CREATE INDEX IF NOT EXISTS idx_within_channel_profiles_outliers
ON within_channel_channel_profiles(run_id, outlier_candidate_count DESC);
CREATE INDEX IF NOT EXISTS idx_within_channel_video_lifetime_outlier
ON within_channel_video_outliers(run_id, lifetime_outlier_label, lifetime_log_robust_z DESC);
CREATE INDEX IF NOT EXISTS idx_within_channel_video_age_outlier
ON within_channel_video_outliers(run_id, age_adjusted_outlier_label, age_adjusted_log_robust_z DESC);
CREATE INDEX IF NOT EXISTS idx_within_channel_queue_evidence_queue
ON within_channel_queue_evidence(run_id, queue_name, channel_url);
`);
}
Shown as it is in the repository at build time (2026-11-03); the home directory appears as <home>. If this page and the file differ, the file is right. Back to the catalogue.