YouTube Video-Stats Tier-Aware Aggregator (Plan 75)

Der youtube:aggregate-video-stats-tracking-Command aggregiert tägliche Coverage-Daten für die /admin/youtube-Page. Plan 75 (2026-05-02) hat den Aggregator tier-aware gemacht, damit nach Plan 56 v2 (Tier-System Deploy 2026-04-28) die Coverage-Daten konsistent + ehrlich sind.

Hintergrund

Plan 56 v2 (28.04.2026) hat das extended_stats_tier-System eingeführt:

• full — täglich Per-Video-Snapshots
• standard — alle 2 Tage
• light — alle 7 Tage
• paused — keine Snapshots, "intentional not synced"
• off — keine Snapshots, ebenfalls "intentional not synced"

Vor Plan 75 war der Aggregator nicht tier-aware → coverage-Werte verfälscht weil tier=light Profile (die nur 1×/Woche syncen) als "missing metrics" gezählt wurden, und tier=paused-Profile als "should sync but didn't".

Symptom: Aggregator failed seit 28.04.2026 ("Letzter Lauf: Fehlgeschlagen"), Daten ab 28.04 fehlten in youtube_video_stats_daily_aggregations.

Was Plan 75 ändert

Neue Spalten in youtube_video_stats_daily_aggregations

Spalte	Bedeutung
tier_full_count	Anzahl Profile in tier=full
tier_standard_count	Anzahl Profile in tier=standard
tier_light_count	Anzahl Profile in tier=light
tier_paused_count	Anzahl Profile in tier=paused (intentional not synced)
tier_off_count	Anzahl Profile in tier=off (intentional not synced)
tier_unclassified_count	Anzahl Profile mit extended_stats_tier IS NULL
profiles_due_today	Anzahl Profile bei denen heute ein Sync FÄLLIG war (basierend auf snapshotIntervalDays + last_sync_at)

Ehrliche Coverage-Berechnung

YouTubeVideoStatsDailyAggregation::honestCoveragePercent():

// Vorher (legacy):
coverage = profiles_synced / total_profiles_sync_enabled

// Plan 75:
coverage = profiles_synced / profiles_due_today  // ehrlicher Wert

Profile in tier=paused/off zählen NICHT mit (sie sollten ja gar nicht syncen). Profile in tier=light, die letzten Sync vor 3 Tagen hatten, zählen ebenfalls NICHT mit (Snapshot-Interval = 7 Tage, also nicht fällig).

Backfill historischer Daten

youtube:aggregate-video-stats-tracking --backfill-from=2026-04-28

Re-aggregiert ab dem Plan-56-v2-Deploy-Tag bis gestern. Bestehende Aggregations-Eintraege werden via UPSERT überschrieben.

Snapshot-Intervalle

Aus config/postbox.php → youtube_stats_tier:

Tier	snapshotIntervalDays	Bedeutung
full	1	täglich fällig
standard	2	alle 2 Tage fällig
light	7	alle 7 Tage fällig
paused, off	9999	praktisch nie fällig

profiles_due_today zählt ein Profil nur dann als fällig wenn (alle Bedingungen):

• social_profiles.platform = 'youtube'
• social_profiles.tracking_enabled = true
• social_profiles.pro_enabled = true (Filter seit 2026-05-27 — vorher zählten alle 467k Default-tier=light Profile mit, was die Coverage absurd niedrig machte; siehe Changelog)
• youtube_video_syncs.auto_sync_enabled = true (INNER JOIN seit 2026-05-27 — vorher LEFT JOIN)
• extended_stats_tier IN ('full', 'standard', 'light')
• AND (last_sync_at IS NULL OR last_sync_at < endOfDay - snapshotIntervalDays)

Datenquelle für last_sync_at: Profile-level aus youtube_video_syncs (konsistent mit SyncYouTubeVideoStatsJob Phase 5 Tier-Filter).

Console-Commands

Command	Zweck
youtube:aggregate-video-stats-tracking	Aggregiert "yesterday" (default)
youtube:aggregate-video-stats-tracking --date=2026-05-01	Spezifischer Tag
youtube:aggregate-video-stats-tracking --backfill	Alle fehlenden Tage in youtube_video_daily_metrics
youtube:aggregate-video-stats-tracking --backfill-from=2026-04-28	Plan 75: Re-Aggregation ab Datum mit Tier-Awareness

Admin-UI

/admin/youtube zeigt:

• Bestehender Coverage-Chart — Videos in/aus/Quota over time
• NEU: Tier-Verteilung Stacked-Bar — pro Tag wie viele Profile in welchem Tier liegen (Plan-56-v2-Diagnose)

Schedule

Unverändert: routes/console.php:

Schedule::command('youtube:aggregate-video-stats-tracking')
    ->dailyAt('22:00')
    ->timezone('UTC')
    ->withoutOverlapping(15);

Performance: statement_timeout-Schutz beim Backfill (seit 2026-05-26)

Beim grossen Backfill (--backfill-from=2026-01-01, ~145 Tage) liefen die calculateReasons()-Queries gegen die 5M+ Zeilen grosse youtube_videos-Tabelle früher in 15-Minuten-Postgres-statement_timeout. Zwei strukturelle Optimierungen schützen jetzt davor:

1. ANALYZE auf TEMP-Tables direkt nach INSERT — tmp_eligible_videos_X, tmp_metrics_video_ids_X und (neu) tmp_reason_candidates_X werden sofort nach dem INSERT analysiert (PG-only, in SQLite no-op). Ohne ANALYZE nutzt der Planner Default-Estimates (~1000 Rows), was die Join-Reihenfolge bei realen 1k-50k-Rows fehlerhaft schätzte.

2. Materialisierungs-Refactor in calculateReasons() — statt 5 separater COUNT-Queries gegen youtube_videos (jede mit eigenen Joins gegen youtube_video_syncs + social_profiles + Anti-Join gegen tmp_metrics_video_ids_X) wird einmal die TEMP-Table tmp_reason_candidates_X materialisiert. Sie enthält genau die Videos, die heute KEINE Metriken bekommen haben, zusammen mit vorberechneten Flags (is_removed, has_active_sync, has_tracking_enabled, is_youtube). Die 5 Reason-Counts danach sind triviale SELECT COUNT(*) FROM tmp_reason_candidates_X WHERE flag = ?-Queries — keine Joins, keine Subqueries, kein Risiko korrelierter Lookups.

Effekt: Statt 5 × Sequential Scan über 5M Rows mit Profil-Joins läuft jetzt 1 × Sequential Scan + LEFT JOIN + EXISTS-Lookup, danach 5 günstige Counts gegen die kleine Materialisierung. Der Backfill ist damit unabhängig von kurzfristiger DB-Last belastbar.

Die Materialisierung wird per mt_rand-Suffix benannt, alle drei TEMP-Tables (tmp_eligible_videos_X, tmp_metrics_video_ids_X, tmp_reason_candidates_X) werden am Ende von aggregateForDate per DROP TABLE IF EXISTS aufgeräumt.

Production-Aktivierung nach Plan-75-Deploy

# 1. Migration anwenden (passiert automatisch im Deploy via php artisan migrate --force)

# 2. Backfill historischer Daten ab 28.04 (einmalig):
php artisan youtube:aggregate-video-stats-tracking --backfill-from=2026-04-28

# 3. Naechster regulaerer Run um 22:00 UTC schreibt mit Tier-Awareness fort.

# 4. /admin/youtube → Coverage + Tier-Stacked-Bar verifizieren.