Data Storage Models (KV & D1)
Relevant source files
The following files were used as context for generating this wiki page:
The Telegram History Bot employs a hybrid storage architecture combining Cloudflare KV (Key-Value) and Cloudflare D1 (Relational). KV serves as an ephemeral, high-speed buffer for raw messages and real-time counters. D1 functions as the authoritative repository for long-term relational aggregates, violation history, and job state management.
Sources: AGENTS.md:6-10, README.md:9-16
Ephemeral Storage (KV)
Cloudflare KV stores raw Telegram messages for a duration of 7 days using a strictly defined Time-To-Live (TTL). It also maintains real-time user counters and media statistics before they undergo aggregation.
KV Key Patterns
The system organizes data using specific prefix-based key patterns to enable efficient listing and retrieval.
| Key Pattern | Description | TTL (Seconds) |
|---|---|---|
msg:{chat}:{ts}:{id} | Raw Telegram message payloads. | 604800 (7 days) |
stats_v2:{chat}:{day}:{user} | Message counts per user per day. | Persistent until backfill |
word_stats_v2:{chat}:{day}:{user} | Word counts per user per day. | Persistent until backfill |
profanity:{chat}:{user}:{day} | Profanity occurrence counts. | Persistent until backfill |
last_message:{chat}:{userId} | Timestamp of the last seen message for a user. | Persistent |
Sources: AGENTS.md:65-68, src/features/stats/daily-backfill.ts:470-520, tests/counters-do-d1-aggregates.test.ts:205-208
Relational Storage (D1)
The D1 database manages structured data that requires complex querying or long-term retention. This includes daily user activity aggregates, criminal violation records, and the state of background jobs.
Core Data Schema
The relational model consists of several aggregate tables that store summarized activity.
The STATS_DAILY_USER table utilizes composite keys on (chat_id, day, user_id) to ensure unique daily entries per user.
Sources: src/features/stats/daily-backfill.ts:250-265, tests/admin-stats-d1.test.ts:135-156
Data Synchronization (Backfill)
The bot synchronizes data from ephemeral KV storage to persistent D1 tables through a multi-phase backfill process. This process ensures historical parity between high-speed counters and relational reports.
Backfill Phases
The backfill operation progresses through deterministic phases to populate specific D1 columns.
- Base Phase: Populates
message_countand user profile snapshots. - Words Phase: Updates
word_countaggregates. - Media Phase: Syncs
voice_countandvideo_note_count. - Category Phases: Transfers
profanityandcriminalstatistics. - Finalize Phase: Performs parity checks against legacy activity totals.
Sources: src/features/stats/daily-backfill.ts:117-133
Atomic Coordination
To prevent race conditions during synchronization, the system implements a singleton job lease.
The lease is renewed on every state write. If a run crashes, the lease stalls until expiry (600 seconds), protecting data integrity.
Sources: src/features/stats/daily-backfill.ts:13-40, src/features/stats/daily-backfill.ts:100-110
Dual-Write Logic
The CountersDO (Durable Object) manages real-time increments by performing dual-writes. When a message arrives, the system increments the value in KV for immediate feedback and simultaneously queues an additive write to D1 for long-term reporting.
- KV Write: Maintains the legacy counter path for existing summary tools.
- D1 Write: Updates additive aggregates (e.g.,
SET message_count = message_count + 1). - Integrity Poisoning: If a D1 write fails, the system marks the specific category (base, profanity, or criminal) as "poisoned" to prevent serving inaccurate statistics until a reconciliation occurs.
Sources: tests/counters-do-d1-aggregates.test.ts:145-165, tests/counters-do-d1-aggregates.test.ts:320-340
Summary
The system utilizes KV for speed and TTL-based expiration while D1 provides a robust relational layer for analytics. This architecture supports high-concurrency Telegram updates through Durable Objects and ensures data consistency via a structured multi-phase backfill pipeline.