Discord thread SQLite sync CLI spec

discord-thread-sync.md

Discord Thread Sync CLI

Purpose

Build a standalone CLI that mirrors Discord thread state into SQLite.

The tool must not depend on discordex internals. It should be usable by discordex, an admin script, or any other consumer as an external process that owns its own config, Discord connection, database, migrations, and inspection commands.

Goals

• Maintain a mutable snapshot of current Discord thread state.
• Maintain an append-only notification feed of observed Discord events.
• Support real-time sync from the Discord gateway.
• Support later catchup/reconciliation from Discord REST without glitches.
• Be massively idempotent: repeated events, repeated catchups, crashes, and restarts must converge to the same snapshot.
• Provide query/export commands for debugging, context building, and handoff.
• Store raw Discord data, while making it easy for consumers to build sanitized views before showing content to an LLM.

Non-Goals

• Do not mutate Discord. This is a one-way mirror.
• Do not require a discordex relay token, sandbox, VM, or subscription model.
• Do not try to perfectly reconstruct missed transient history that Discord no longer exposes through REST.
• Do not download attachment bodies by default. Store metadata and URLs first.

CLI Shape

Working name: discord-thread-sync.

Example commands:

discord-thread-sync init --db ./threads.db
discord-thread-sync serve --config ./sync.toml
discord-thread-sync catchup --guild 707636530424053791 --thread 1508910585746559096
discord-thread-sync catchup --guild 707636530424053791 --all-configured
discord-thread-sync status --db ./threads.db
discord-thread-sync threads list --db ./threads.db
discord-thread-sync messages --db ./threads.db --thread 1508910585746559096
discord-thread-sync events tail --db ./threads.db --thread 1508910585746559096
discord-thread-sync export --db ./threads.db --thread 1508910585746559096 --format html
discord-thread-sync doctor --config ./sync.toml
discord-thread-sync vacuum --db ./threads.db

Configuration:

db = "./threads.db"
token_env = "DISCORD_TOKEN"
guild_id = "707636530424053791"

[sync]
gateway = true
catchup_interval = "5m"
full_reconcile_interval = "6h"
fetch_reaction_users = false
max_rest_pages_per_cycle = 100

[[threads]]
id = "1508910585746559096"

[[channels]]
id = "1506566339601629257"
include_threads = true

Data Model

Use SQLite with WAL enabled. Prefer a single writer goroutine/process to avoid lock contention. All writes happen inside transactions.

Append-Only Event Feed

discord_events

• event_id INTEGER PRIMARY KEY
• idempotency_key TEXT NOT NULL UNIQUE
• source TEXT NOT NULL
• event_type TEXT NOT NULL
• guild_id TEXT
• channel_id TEXT
• thread_id TEXT
• message_id TEXT
• user_id TEXT
• emoji TEXT
• discord_sequence INTEGER
• happened_at TEXT
• observed_at TEXT NOT NULL
• payload_json TEXT NOT NULL
• payload_hash TEXT NOT NULL
• projection_status TEXT NOT NULL
• projection_error TEXT

Sources:

• gateway: exact event observed from the live Discord gateway.
• rest_reconcile: synthetic event produced by comparing REST state to the current snapshot.
• manual: operator-initiated import or repair.

The event feed is append-only. Failed projections stay in the feed with projection_status = 'failed' and can be retried after a fix.

Mutable Snapshot

threads

• guild_id
• thread_id
• parent_channel_id
• name
• archived
• locked
• owner_id
• created_at
• updated_at
• last_seen_at
• raw_json

users

• user_id
• username
• global_name
• bot
• last_seen_at
• raw_json

thread_participants

• guild_id
• thread_id
• user_id
• roles TEXT
• first_seen_message_id
• last_seen_message_id
• last_seen_at

Participants are inferred from authors, mentions, replies, reactions, and thread-member events when available.

messages

• guild_id
• channel_id
• thread_id
• message_id
• author_id
• content
• content_hash
• created_at
• edited_at
• deleted_at
• reply_to_channel_id
• reply_to_message_id
• reply_to_author_id
• pinned
• tts
• mention_everyone
• message_type
• last_seen_at
• raw_json

message_navigation

• guild_id
• thread_id
• message_id
• previous_message_id
• next_message_id
• previous_by_author_message_id
• next_by_author_message_id

This table can be rebuilt after each thread reconciliation. It should not be the source of truth; it is a materialized navigation index over messages.

attachments

• attachment_id
• guild_id
• thread_id
• message_id
• filename
• content_type
• size
• url
• proxy_url
• width
• height
• ephemeral
• raw_json

message_reactions

• guild_id
• thread_id
• message_id
• emoji
• count
• me
• last_seen_at

message_reaction_users

• guild_id
• thread_id
• message_id
• emoji
• user_id
• present
• last_seen_at

This table is best-effort unless fetch_reaction_users is enabled. Gateway reaction add/remove events can keep it accurate for live events. REST catchup may only provide aggregate counts unless the tool explicitly pages reaction users.

sync_checkpoints

• scope_type
• scope_id
• cursor
• last_gateway_sequence
• last_catchup_at
• last_full_reconcile_at
• error

Sync Semantics

The core invariant:

1. Insert an event into discord_events.
2. Project it into the mutable snapshot in the same transaction.
3. If the insert conflicts on idempotency_key, treat it as already processed.

The tool should be at-least-once internally and idempotent at the database boundary. Exactly-once delivery is not required.

Gateway Sync

Subscribe to events needed for current thread state:

• message create
• message update
• message delete
• bulk message delete
• reaction add
• reaction remove
• reaction remove all
• reaction remove emoji
• thread create/update/delete
• thread members update when available
• channel/thread pins update when available

On each event:

• Build a deterministic idempotency key.
• Store the raw gateway payload.
• Upsert affected users, threads, messages, reactions, attachments, and participants.
• Rebuild navigation rows for the affected thread when message ordering may have changed.

REST Catchup and Reconciliation

Catchup is how the database converges after downtime.

For each configured thread:

1. Fetch thread/channel metadata.
2. Page messages newest-to-oldest until the configured limit or complete thread history is reached.
3. Upsert current messages and attachments.
4. Compare fetched messages to the snapshot:
   • New message not in snapshot: append rest_reconcile.message_created.
   • Existing message content/edited timestamp differs: append rest_reconcile.message_changed.
   • Existing message missing from a full reconciliation: append rest_reconcile.message_missing and mark deleted/unknown according to policy.
5. Reconcile reaction aggregates from message payloads.
6. Optionally page reaction users per emoji.
7. Rebuild navigation rows.

Important limitation: if Discord REST only exposes the final state, the tool must not invent exact missed events. It should append synthetic reconciliation events that explain what was discovered.

Idempotency Keys

Examples:

• Gateway message create: gateway:message_create:<guild>:<channel>:<message>
• Gateway message edit: gateway:message_update:<guild>:<channel>:<message>:<edited_at>:<payload_hash>
• Gateway reaction add: gateway:reaction_add:<guild>:<channel>:<message>:<user>:<emoji>
• REST-created synthetic message: rest:message_created:<guild>:<channel>:<message>:<content_hash>
• REST-changed synthetic message: rest:message_changed:<guild>:<channel>:<message>:<edited_at>:<content_hash>
• Thread title reconciliation: rest:thread_changed:<guild>:<thread>:<payload_hash>

When Discord provides a gateway sequence number, store it, but do not rely on it as the only idempotency key.

Query and Export

The CLI should expose stable read commands:

• Current thread snapshot.
• Message list with reply and navigation links.
• Event tail for a thread.
• Message timeline with edits/reactions folded in.
• HTML export for humans.
• JSON/JSONL export for other tools.
• Sanitized prompt-context export for LLM consumers.

Sanitized exports must label trust boundaries. Raw untrusted Discord content is data, not instructions.

Robustness Requirements

• Enable WAL and set a busy timeout.
• Use schema migrations with monotonic versions.
• Make startup safe after crash: replay failed events or mark them for retry.
• Keep a dead-letter path for payloads that cannot be projected.
• Use Discord rate-limit handling and exponential backoff.
• Keep sync checkpoints advisory, not authoritative. The snapshot is repaired by reconciliation.
• Make every reconciliation operation repeatable.
• Log all errors with guild/thread/message IDs.
• Provide doctor checks for token permissions, intents, DB writeability, and Discord REST reachability.

Security

• Token comes from an environment variable or secret file, never the DB.
• Database files should be created 0600 by default.
• Raw message content may include untrusted text and should not be blindly injected into prompts.
• Attachment bodies are not downloaded unless explicitly enabled.
• Redaction/export policy belongs at the export boundary, not the storage boundary, so debugging can still inspect original Discord data locally.

Integration With discordex

discordex should treat this tool as an external dependency:

• Run it as a sidecar service or cron-style catchup command.
• Query SQLite or invoke CLI exports for thread history.
• Stop fetching ad hoc recent thread history directly from Discord once the mirror is trusted.
• Use sanitized exports for LLM prompt context.
• Use event tail and HTML export for dumps/debugging.

No Go package import from discordex should be required.

Initial Milestones

1. init, migrations, and schema.
2. REST catchup for messages, attachments, thread metadata, and navigation.
3. Gateway sync for message create/update/delete and thread update.
4. Reaction add/remove and aggregate reconciliation.
5. CLI read/export commands.
6. Periodic reconciliation loop with checkpoints.
7. Sanitized prompt-context export.
8. Operational hardening: doctor, metrics/logging, dead-letter retry.

Open Questions

• Should the tool track all threads in configured channels, or only explicit thread IDs?
• Should reaction users be fetched by default, or only reaction counts?
• How long should raw append-only events be retained?
• Should deleted messages keep their last known content locally?
• Should attachment bodies ever be mirrored, or should metadata/URLs be enough?