Daily Aggregates & Backfilling
Relevant source files
The following files were used as context for generating this wiki page:
Daily Aggregates & Backfilling is a multi-phase system that synchronizes Telegram chat statistics from Cloudflare KV (ephemeral storage) to a Cloudflare D1 SQL database (authoritative storage). This system enables long-term historical analytics and powers the admin dashboard while maintaining strict resource limits and data integrity.
The system employs a dual-write strategy: a live writer records current events directly to D1, while a background backfiller traverses historical KV keys to recover data created before the D1 migration or during downtime.
Sources: src/features/stats/daily-backfill.ts:6-20, src/features/stats/d1-aggregate-writer.ts:11-20
Architecture and Job Control
The backfill operation runs as a singleton scheduled task named daily_aggregates_v1. It uses an atomic lease mechanism to ensure only one worker processes a specific phase at a time. The system stores all progress, cursors, and state in the stats_backfill_state table.
Concurrency and Lease Management
Each execution attempt must acquire a short-lived lease (defaulting to 600 seconds). The worker uses an "Epoch-fenced" strategy where every data mutation embeds a captured job version and revision. If a competing run resets or advances the state, the current runner's fenced writes will fail.
Sources: src/features/stats/daily-backfill.ts:12-22, src/features/stats/daily-backfill.ts:108
The lease acquisition prevents overlapping cron runs from duplicating work or corrupting the cursor.
Sources: src/features/stats/daily-backfill.ts:15-20, src/features/stats/daily-backfill.ts:1022-1045
State Tracking
The system persists the following state to maintain resumes across Cloudflare Worker invocations:
| Field | Type | Description |
|---|---|---|
version | INTEGER | Current state model version (V3 resets historical data). |
status | TEXT | 'running' or 'done'. |
phase | TEXT | The specific data family currently being processed. |
cursor | TEXT | Cloudflare KV list cursor for pagination. |
page_offset | INTEGER | Index within the current KV list page. |
cutoff_day | TEXT | Immutable UTC day; backfill skips keys >= this day. |
| Sources: src/features/stats/daily-backfill.ts:315-330 |
Backfill Phases
The backfiller processes modern source families in a deterministic global order. This ensure that dependencies, such as user profiles, are hydrated before or alongside specific counts.
- Base: Processes
stats_v2keys (message counts) and hydratesstats_chat_user_profile. - Words: Processes
word_stats_v2(word counts). - Media Stats/Duration: Processes voice and video note counts and total seconds.
- Category Metrics: Processes
profanity,criminal, andcriminal_severity. - Activity Metrics: Processes
hoursandbuckets(time-of-day distribution). - Finalize: Performs a parity check between backfilled D1 aggregates and legacy activity totals.
Sources: src/features/stats/daily-backfill.ts:133-145
Data Flow for Source Slices
Each slice is bounded to 50 keys, 300 KV gets, and 90 D1 statements to stay within Cloudflare platform limits.
Sources: src/features/stats/daily-backfill.ts:48-60
Live Aggregate Writing
Live writes occur via d1-aggregate-writer.ts whenever a CountersDO mutation is serialized. These writes use additive logic (count = count + excluded.count) rather than absolute snapshots used in backfilling.
Coverage and Ownership
The stats_daily_coverage table tracks the source of truth for every (chat, day) pair.
- Backfill Source: Written when the backfiller discovers a day. Status remains
pending. - Live Source: Written when a live event occurs. Live ownership is monotonic; once a day is marked
source='live', the backfiller will not overwrite its coverage status.
Sources: src/features/stats/d1-aggregate-writer.ts:168-180, src/features/stats/d1-coverage.ts:140-155
| Status | Source | Description |
|---|---|---|
pending | backfill | Data recovered but not yet verified against legacy totals. |
complete | backfill | Verified parity with legacy activity table. Eligible for D1 reading. |
live | live | Actively receiving dual-writes. Not eligible for historical closed-range reads. |
none | live | Category (e.g., criminal) has not been touched by live events yet. |
| Sources: src/features/stats/d1-aggregate-writer.ts:24-26, src/features/stats/d1-coverage.ts:145-160 |
The Finalize Parity Gate
A day is only considered "Ready" for D1-based historical reporting if it passes the Finalize phase.
- Parity Read: The system reads the total
SUM(message_count)fromstats_daily_userfor a chat/day. - Legacy Check: It fetches the count from the legacy
activitytable. - Validation:
- If
SUM == legacy_countANDSUM > 0, the day is markedcomplete. - If there is a mismatch, it records
message_count_mismatch. - If legacy data is missing, it records
missing_activity_total.
Sources: src/features/stats/daily-backfill.ts:795-850
- If
Implementation Details
Epoch Fencing
To prevent late writes from a worker that has lost its lease, every SQL upsert includes an EPOCH_FENCE. This is a subquery that validates the lease_owner and revision still match the worker's captured identity.
INSERT INTO stats_daily_user (chat_id, day, user_id, message_count)
SELECT ?, ?, ?, ? WHERE EXISTS (
SELECT 1 FROM stats_backfill_state
WHERE job_name = ? AND version = ? AND revision = ? AND lease_owner = ?
AND status = 'running'
)
ON CONFLICT(chat_id, day, user_id) DO UPDATE SET message_count = excluded.message_count
WHERE EXISTS ( ... same fence ... )
Sources: src/features/stats/daily-backfill.ts:380-410
Integration via Index
The main worker entry point manages the backfill through a distinct cron trigger * * * * *. It executes runDailyAggregateBackfill independently of the daily summary or cleanup tasks.
Sources: src/index.ts:725-745
Conclusion
The Daily Aggregates & Backfilling system provides a reliable bridge between KV-based counters and a D1 SQL database. By using deterministic phases, atomic leases, and a final parity gate, it ensures historical data is accurately recovered and verified before being served to administrators. This architecture allows the bot to handle high-volume chat data within the constraints of the Cloudflare Workers environment.