Schema notes¶
The schema is intentionally browse-oriented.
Main tables¶
tg_updates
raw incoming updates with source + status
tg_chats
stable chat catalog
tg_users
stable user catalog
tg_messages
normalized message rows, including reply/thread metadata
tg_message_entities
rich text / caption entities
tg_message_media
normalized media assets and storage state
tg_callback_queries
callback query catalog
tg_message_reactions
reaction history
tg_actions
outbound action audit log
tg_poll_schedules
persisted poll cadence and filters
tg_poll_schedule_claims
expiring owner tokens that serialize due schedule execution
tg_poll_runs
execution records for manual or scheduled polling
tg_route_executions / tg_handler_executions
higher-level automation audit surface
tg_legacy_imports
idempotency map for imported TGEX history
The tg_ prefix on the execution tables is intentional. TGEX used the names
route_executions and handler_executions for structurally different tables;
keeping the projection tables prefixed allows both schemas to coexist in a
single SQLite database during migration.
Schema version 3 adds tg_poll_schedule_claims. Existing version-2 databases are upgraded in place without dropping schedules or history.
The claim expiry is renewed by the active polling worker. Renewal and completion both require the same owner token and a lease that has not yet expired; a delayed worker cannot revive or finalize ownership after another process is eligible to recover the schedule.
Browse-friendly queries this supports¶
-- latest chats
SELECT ref, type, COALESCE(title, username, first_name) AS label, last_seen_at
FROM tg_chats
ORDER BY last_seen_at DESC
LIMIT 50;
-- latest messages in a chat
SELECT ref, date_ts, COALESCE(text, caption) AS preview
FROM tg_messages
WHERE chat_id = ?
ORDER BY date_ts DESC
LIMIT 100;
-- media attached to a message
SELECT ref, media_kind, storage_mode, download_status, local_path
FROM tg_message_media
WHERE message_ref = ?
ORDER BY ordinal ASC;
-- callback queries for a message
SELECT ref, data, answered, received_at
FROM tg_callback_queries
WHERE message_ref = ?
ORDER BY received_at DESC;
-- actions against a ref
SELECT ref, action_kind, ok, created_at, error
FROM tg_actions
WHERE subject_ref = ?
ORDER BY created_at DESC;
Media representation¶
tg_message_media is intentionally separate from tg_messages so that:
- one message can expose multiple normalized assets
- media persistence policy can evolve independently of message browse behavior
- downloads can be retried or backfilled without rewriting message rows
All rows produced by one normalized update are committed in one transaction. Network media retrieval happens before that transaction; local file finalization and the corresponding media row participate in the rollback protocol. Readers therefore observe either the previous complete projection or the new complete projection, not an intermediate mix.
Key columns: