Skip to main content

Stream History: storage, rollups & retention

Stream History stores each stream in three layers in TimescaleDB. Knowing which layer answers which read — and how long each layer lives — is the difference between a query that scans a year of raw samples and one that reads a pre-computed series. This page is the map (ZAF-681, the product side of the ZAF-672 observability decision).

The three layers​

LayerTable / viewGrainWritten byRead for
Summarychannel_history, channel_history_streamsone row per session / per broadcastthe finalizer (history_sampler.rs) once a session closeslist previews, channel-page aggregates
Raw sampleschannel_history_stats (hypertable)one 60s sample per (session, platform)the sampler, every 60s per open sessionthe detail curve of one stream
Rollupschannel_history_stats_5m, channel_history_stats_1h (continuous aggregates)one bucket per (5m or 1h, session, platform)TimescaleDB refresh policieslong / cross-session ranges

Raw samples — the detail curve​

channel_history_stats is the raw time series: viewer_count plus the cumulative counters (messages_total, followers_total, subs_total, donations_total_cents) and per-platform platform_metrics, sampled every 60s while a session is live. For one stream's page this is exactly the right resolution, so the detail readers stay on it:

  • db::public_stats_read::list_stream_viewer_curve — the public stream-page viewer curve for a single (session, platform).
  • db::channel_history_reports::list_stats — the report charts for a session.
  • db::channel_history::{session_peak_avg_viewers, stream_peak_avg_viewers, …} — per-session finalizer aggregates.

None of these span beyond a session, so none were changed.

Rollups — the pre-computed series for long ranges​

The ZAF-594 public Stats app spans a year across thousands of channels, and the ZAF-595 crawler adds tens of thousands of foreign channels (source = 'external_crawler'). Scanning raw 60s samples over those ranges is exactly the pattern we avoid on the Prometheus side with recording rules (ZAF-675 §4). So two continuous aggregates roll the raw samples up:

  • channel_history_stats_5m — 5-minute buckets, refreshed every 15 min (leaving the last 10 min to the raw detail curve). Multi-day / multi-week reads.
  • channel_history_stats_1h — 1-hour buckets, refreshed hourly. Month / quarter / year reads.

Each bucket keeps viewer_sum + viewer_samples (so a read that re-aggregates across sessions gets an exact sample-weighted average, not an average of averages), peak_viewers / min_viewers, and the max() of each cumulative counter (== the value at the last sample of the bucket). Both are materialized_only — a read only ever touches pre-computed buckets, never the raw table.

The cross-session read primitive is db::public_stats_read::list_account_viewer_curve(account_id, platform, from, to, resolution). It picks the 5m or 1h aggregate and returns a downsampled curve. A gated test (apps/api/tests/tsdb_channel_history_aggregates.rs, run under RUN_TSDB_MIGRATION_TEST) proves a long range returns downsampled buckets whose plan scans the aggregate's materialization hypertable — not raw channel_history_stats.

Migrations​

The aggregates ship in apps/api/tsdb_migrations/20260823000010_channel_history_continuous_aggregates.{up,down}.sql. Creating (and dropping) a continuous aggregate cannot run inside a transaction, so the migration opts out of sqlx's transaction wrapper with a -- no-transaction first line (sqlx 0.9). The .down.sql drops both views, which also removes their refresh-policy jobs.

Retention​

Two very different retention questions live here, and they are answered differently.

  • Aggregates: kept, no retention drop. The published privacy policy (apps/web/content/{en,de}/legal/privacy.mdx, §"Stream history (broadcast statistics)") promises the viewer-count curve for the lifetime of the account, and — as aggregate reach statistics — retains it on an ongoing basis even after the account ends. The rollups are that long-lived pre-computed curve, so they carry no retention policy. Keeping them is what makes the lifetime promise sustainable at crawl scale.

  • Raw 60s samples: dropped after 90 days. The raw hypertable carries a TimescaleDB retention policy — add_retention_policy('channel_history_stats', INTERVAL '90 days'), migration 20260824000001 — that drops each chunk once its whole time range is older than 90 days. This is lawful precisely because the rollups above preserve the promised viewer-count curve: max (peaks survive), min, an exact sample-weighted average (SUM(viewer_sum)/SUM(viewer_samples)), and the cumulative counters (max() of the monotonic-in-session totals). Dropping the 60s cadence — which the privacy policy never published — therefore loses no promised information and is Art. 5(1)(c)/(e) DSGVO data-minimisation-positive. Legal & Compliance signed off on the 90-day window on ZAF-689 (founder-approved 2026-08-24), the same align-with-Legal-before-you-set-a-window gate ZAF-648/653 applied to the GDPR-bound hypertables (audit_events, platform_event_logs). The policy is table-wide, so it covers first-party and source = 'external_crawler' (ZAF-595) rows identically — both are de-identified aggregate reach data under the same window. Raw samples are still compressed after 7 days (independent compression policy); retention then drops the chunk outright once it ages past 90 days.

The rollups are the long-lived preserved curve; the raw samples are the short-lived (90-day) detail curve. Both migrations are reversible with no public surface, and the one-way-door raw-retention window was set only after Legal signed off — the same decouple-the-reversible-migration-from-the-approval-gate discipline the audit and telemetry retention work used. The retention migration is numbered after the rollup migration (20260823000010), so the preserving aggregate is always materialising before any raw chunk can age out.