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).

For rules that govern new TimescaleDB tables, aggregates, compression, and retention, see the data design standard.

The three layers

Shared Chat session data

The TimescaleDB migration 20261023200000_shared_chat_session_history adds four nullable columns without an index or data rewrite:

Column	Purpose
channel_history.shared_chat_at	First session moment with Shared Chat; supports the With/Without filter, including a live session.
channel_history.partner_messages	Marker and count for separating old frozen own-channel chat totals from partner messages.
channel_history_streams.shared_chat_timeline	Event-exact start/end, host and per-channel participation spans; frozen with shared_chat_sources at finalization.
channel_history_streams.shared_chat_insights	Frozen partner chat figures and keyword/link/command lists.

While a stream is live, services/history/shared_chat_timeline.rs derives the timeline from the four twitch:shared_chat_* rows in platform_event_logs. The finalizer freezes it in the same update as shared_chat_sources, before event retention can remove those rows. A channel that leaves and rejoins has two spans. For older streams without transition events, the sample buckets give approximate boundaries (± one minute). The boot backfill of partner messages skips crawled and erased sessions. It checks retention for every candidate before it writes anything: a stream whose chat log is pruned (fewer rows than its stored totals, an empty log included) is left untouched, so both markers — partner_messages and bot_messages — stay NULL. It keeps its older mixed total and is marked as including partner chat.

db::channel_history::OWN_CHANNEL_SQL is the shared predicate for every My channel message count, including retained-data checks and backfills. Partner messages do not enter own totals. Whole session uses partner counts and insights frozen at finalization. Partner keyword, link and command lists retain only the term and count: no viewer identities, poster lists or occurrences, and therefore no partner-list viewer-erasure path. Own-channel insights keep their existing poster details and erasure handling. Partner Subs, Bits and gifts are display events, never revenue in either view.

Data	Live read	Finalized read
Session transitions	Derived from stored platform_event_logs events	Frozen shared_chat_timeline
Partner viewers and messages	Newest 60-second sample plus server session state	Frozen per-channel aggregates and insights
Older streams without events	Minute-sample reconstruction	Minute-sample fallback; no invented event-exact boundaries

Layer	Table / view	Grain	Written by	Read for
Summary	channel_history, channel_history_streams	one row per session / per broadcast	the finalizer (history_sampler.rs) once a session closes; for crawled sessions the crawler itself	list previews, channel-page aggregates
Raw samples	channel_history_stats (hypertable)	one 60s sample per (session, platform)	the sampler, every 60s per open first-party session; the crawler's stats poller for crawled sessions	the detail curve of one stream
Rollups	channel_history_stats_5m, channel_history_stats_1h (continuous aggregates)	one bucket per (5m or 1h, session, platform)	TimescaleDB refresh policies	long / cross-session ranges

Running first-party history reads

For a first-party session that is not finalized and started within the last 48 hours, service::list_sessions and service::build_report_from overlay the stored summary in memory. The finalizer remains the only writer of channel_history and channel_history_streams. The list uses one batched live_overlay_samples query for eligible sessions on its page; the report folds the raw samples it already loaded. Per-minute viewer sums give running peak and average viewers. The newest sample per platform gives cumulative messages, followers, paid Subs, gifted Subs and the platform breakdown. Duration runs from the session start. A session without samples marks these values unavailable.

Unique chatters, per-platform chatters, bot-message count, emote and GIF counts and top lists are derived from the retained chat log with the finalizer's counting rules. That read has a three-second budget and a cache with a 60-second soft age and 120-second expiry. Chatter suppression is applied after every cache read. If the derivation or suppression check cannot finish, the affected values are unavailable rather than a meaningful zero. Chat sampling uses a 30-second watermark for late messages, events and tips; a displayed chat counter can therefore trail incoming chat by about 90 seconds in total. YouTube subscriber gain is calculated after the stream from the subscriber count at its start and end.

The overlay is shared by GraphQL and REST list and report reads, dashboard latestSession, and authorized report share links. It does not affect the summary email, report and list exports, milestone calculations, overview KPI totals or public Stats reads: those use stored finalized values. An open session older than 48 hours also keeps its stored values because its sample counters may no longer be reliable.

Crawled sessions (nil-UUID account, source = 'external_crawler') share these tables but not the first-party workers. The API's sampler, grace close, startup orphan recovery and finalizer all skip the nil-UUID account; the crawler opens, samples, closes and freezes those sessions itself (see the crawler's session lifecycle). Before that split the sampler overwrote crawler samples with empty ones and every API deploy ended the crawled streams that were live.

Boot backfills

At startup, before the platform workers start, the API runs a set of one-shot, idempotent backfills over finalized channel_history rows (services::history::backfill): backfill_monetization_split, backfill_revenue_counters (gift tiers, ad-break list), backfill_currency_amounts, backfill_member_levels and backfill_bot_messages. Each one re-derives a figure from the retained event or chat log and only picks rows that still lack it.

All of them skip crawled sessions (account_id = CRAWLER_ACCOUNT_ID, the nil UUID; one constant in db::channel_history). The crawler stores no event or chat log for its sessions and freezes total_messages from its IRC aggregates, so a crawled row could never be re-derived. Without the filter it would stay a backfill_bot_messages candidate (counted as not_retained) on every boot, and the boot would grow with every crawled session. The scanned figure in the backfill log lines counts first-party sessions only.

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.

List sparklines

viewer_sparkline on GET /v1/history?include=sparkline (or GraphQL channelHistory.sessions.viewerSparkline) produces at most 32 combined-viewer points for each visible session in one page query. Each session reads exactly one tier, in this order: raw samples while they exist; after they age out (90 days), channel_history_stats_5m; after that ages out too (12 months), channel_history_stats_1h. The rollup tiers sum the per-platform bucket peak across platforms. All three tiers are in the same single page query (db::channel_history_reports::viewer_sparklines). Live sessions bucket through the current time. With no samples or rollup points the field is null.

The dashboard's per-platform viewer series of the latest session (db::channel_history_reports::viewer_series, GraphQL latestSession.viewerSeries) uses the same three tiers, without summing platforms.

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. Dropped after 12 months (see Retention).
• channel_history_stats_1h - 1-hour buckets, refreshed hourly. Month / quarter / year reads. Kept, and built from the raw table (not from _5m), so the _5m drop does not affect it.

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.

For the dashboard's latest-session chart, viewer_series keeps each platform's maximum viewer count in up to 60 equal session-span buckets. viewer_total uses the same bucket times and only the selected platforms. While raw samples exist, it sums viewer counts from the same minute across those platforms, then keeps the highest concurrent sum in each bucket. Adding the separate viewer_series maxima would overstate the audience when the platforms peaked at different minutes. The total has no point for a bucket without samples. After raw samples expire, the 5-minute and then 1-hour rollups supply the curve by summing their stored per-platform peaks at each rollup time; those older total points are an approximation because the original minute-level alignment is no longer available.

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 5m request whose from lies before the 5m retention window (12 months) reads the 1h aggregate for the whole range (effective_resolution), so it never returns a silently partial 5-minute 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.

In an agent run, use the injected TimescaleDB test URL for that suite: DATABASE_URL="$TEST_TSDB_DATABASE_URL" RUN_TSDB_MIGRATION_TEST=1 cargo test -p lumio-api --test tsdb_channel_history_aggregates.

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.

Chunks, compression and sample ids

• Raw samples: channel_history_stats uses 1-day chunks and compresses a chunk 7 days after its range ends (segmentby session_id). With the old 7-day default one chunk held ~83 GB at 30 000 live crawled channels. Chunks written before 20261012100000 keep their 7-day size until retention drops them.
• Rollups: channel_history_stats_5m compresses after 7 days and channel_history_stats_1h after 30 days - never inside their refresh window (start_offset 2 days / 30 days). Both segment by session_id and order by bucket.
• Sample ids: id defaults to uuidv7() (PostgreSQL 18), so inserts into the (id, time) primary-key index are time-ordered. Both writers (API sampler, crawler) leave id to the default.
• Account index: idx_channel_history_stats_account_first_party (account_id, time DESC) is partial and skips crawler samples (nil account_id). A query that looks samples up by account must repeat account_id <> '00000000-0000-0000-0000-000000000000'::uuid literally (as latest_sample_for does), otherwise a generic plan cannot use the index.

apps/api/tests/tsdb_chunk_intervals_compression.rs (gated by RUN_TSDB_MIGRATION_TEST) pins all four.

Deletion

Deleting a report (deleteChannelHistory / DELETE /v1/history/reports/{id}, db::channel_history_reports::delete_session) and deleting a channel's whole history on dissolution or operator delete (db::channel_history::delete_all_account_history) remove the rows of all three layers: the session, its streams, raw samples and milestones, and the matching rows of channel_history_stats_5m and _1h. The crawler's opt-out erasure (apps/crawler-twitch/src/optout/erase.rs) does the same for the crawled sessions of an opted-out channel. Each path is one transaction, so a failure deletes nothing.

Deleting the raw rows alone would leave the rollup rows in place: the refresh policies only recompute their recent window (_5m 2 days, _1h 30 days), and _1h has no retention. So lo_database::viewer_curve::delete_viewer_curve_rows (re-exported as db::channel_history::delete_viewer_curve_rows; the crawler cannot call apps/api) deletes them where they are stored, in the aggregates' materialisation hypertables (by account_id for a channel, by session_id = ANY(…) + account_id for one report or the sessions of an opted-out crawled channel). The hypertable names are read at run time from timescaledb_information.continuous_aggregates; a missing aggregate is an error, never a skipped delete. The transaction lifts timescaledb.max_tuples_decompressed_per_dml_transaction, because the rows sit in compressed chunks and a large channel exceeds the default cap of 100,000 decompressed tuples. Bloom pruning on account_id / the session_id index keep the delete to the affected batches (about 1 ms, ZAF-2661).

Sessions deleted before this path existed left their rollup rows behind. The one-off TimescaleDB migration 20261022100000_viewer_curve_orphan_cleanup removes them: it collects the session_ids of both rollups that have no channel_history row (buckets of the last day excluded, they go with the next refresh), then deletes them from the materialisation hypertables with session_id = ANY(…), the batch-pruned path on the session_id-segmented compressed chunks. It never refreshes, compresses or drops, fails on a missing aggregate, writes the counts to the migration log and deletes nothing on a second run.

Never call refresh_continuous_aggregate on channel_history_stats_5m/_1h over a range older than 90 days, neither in a migration nor by hand; raw samples are gone there and the refresh erases every channel's buckets. A raw-row delete logs invalidations for its range, and the policies leave those older than their own window in the log, so a manual refresh of an old range would act on them.

The tests: tsdb_viewer_curve_erasure.rs (materialised and compressed rows of both rollups, account and session delete, a foreign session, no return after a refresh) and tsdb_crawler_optout_curve_erasure.rs (the crawler's real opt-out transaction on sessions older than both refresh windows; another channel's rows stay) under RUN_TSDB_MIGRATION_TEST.

Retention

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

• 1-hour rollup: kept, no retention drop. The published privacy policy (apps/web/content/{en,de}/legal/privacy.mdx, §"Stream history (broadcast statistics)") keeps the viewer-count curve for as long as the channel exists. channel_history_stats_1h is that long-lived pre-computed curve, so it carries no retention policy. Its rows go only with a deleted report or a deleted channel (see Deletion).

• 5-minute rollup: dropped after 12 months. Continuous aggregates are not compressed, and at crawl scale channel_history_stats_5m grows by ~570 GB a year (ZAF-2180 capacity model). Migration 20261012110000 adds add_retention_policy('channel_history_stats_5m', drop_after => INTERVAL '12 months'). Legal decided the window on ZAF-2189 (founder-accepted), on the condition that the _1h read fallback ships with it (see List sparklines and list_account_viewer_curve above). Stream history older than 12 months therefore reads at 1-hour resolution. The policy is safe on the aggregate: its refresh policy only recomputes the last 2 days, so a refresh never re-materialises a dropped range. It is table-wide (_5m has no source column), so first-party and external_crawler buckets share the window. _5m materialises forward from 2026-08-23 (WITH NO DATA), so the first drop is around 2027-08-23.

• 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 1-hour rollup is the long-lived preserved curve; the 5-minute rollup is the 12-month working resolution; 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.

The tests: tsdb_channel_history_retention.rs (raw, 90 days) and tsdb_channel_history_rollup_retention.rs (_5m drops an aged bucket, _1h has no policy and keeps the hour) under RUN_TSDB_MIGRATION_TEST; the raw → 5m → 1h read fallback in history_overview.rs.