discord-chat-insights

name

discord-chat-insights

description

Query and analyze Discord chat history stored in a Neon/PostgreSQL database and support/billing emails from configured IMAP/POP3 mailboxes. Use when Codex needs to answer business, product, operations, community, support, billing, trend, topic, channel, user, language, mailbox, or time-window questions from collected Discord messages or company support emails; search messages by keyword; inspect channel or mailbox activity; compare periods; verify or summarize daily/hourly reports; or produce evidence-backed conclusions without depending on the original project checkout.

Discord Chat Insights

Overview

Use this skill to answer questions from Discord chat history stored in Neon/PostgreSQL and support/billing emails from configured IMAP/POP3 mailboxes. Prefer the bundled read-only scripts over project code so the workflow works from any workspace.

Quick Start

Resolve the skill directory from the loaded SKILL.md path. If the shell is already in the skill directory, run:

python3 scripts/query_discord.py overview --format markdown

From any other directory, run with the actual skill path:

SKILL_DIR="/path/to/discord-chat-insights"
python3 "$SKILL_DIR/scripts/query_discord.py" overview --format markdown

For natural-language questions, first narrow the question into a time window, source, scope, and evidence need. Then run one or more script commands and synthesize the answer with message IDs, channel/mailbox names, and timestamps.

Dependencies

The Discord database script requires Python 3 and psycopg2-binary>=2.9.0. If psycopg2 is missing, scripts/query_discord.py automatically runs:

python3 -m pip install --user "psycopg2-binary>=2.9.0"

Set DISCORD_CHAT_INSIGHTS_NO_AUTO_INSTALL=1 only when automatic installation must be disabled.

The email script uses Python standard-library IMAP/POP3 modules and installs no packages.

Workflow

1. Read references/schema.md when table/column details matter.
2. Read references/query-patterns.md for common analysis recipes.
3. Read references/complaint-taxonomy.json only when adjusting or auditing complaint categories.
4. Read references/email-taxonomy.json only when adjusting or auditing email categories.
5. Use scripts/query_discord.py overview to learn available Discord date ranges, active channels, languages, and report coverage.
6. Use scripts/query_email.py health to confirm configured support/billing mailboxes can be read.
7. Use scripts/query_discord.py complaints first for broad Discord complaint, pain-point, issue, bug, support, and negative-feedback questions. It uses the bundled weighted taxonomy and returns the matched terms behind each evidence row.
8. Use scripts/query_email.py summary --hours 4 first for recent support/billing email summaries. It automatically reads all complete EMAIL_ACCOUNT_* entries in config/.env; do not require the user to name mailboxes.
9. Use messages commands for extra evidence rows. Keep limits modest first, then widen only if needed.
10. Use scripts/query_discord.py query --sql "SELECT ..." for custom read-only aggregate SQL.
11. Use scripts/query_discord.py reports when existing daily/hourly reports can answer faster, but verify from raw messages before making high-confidence claims.
12. State conclusions separately from evidence. Say when a conclusion is based on a sample, an aggregate, keyword taxonomy matches, or existing generated reports.

Script Commands

# Connection smoke test and table counts
python3 scripts/query_discord.py health

# Database overview
python3 scripts/query_discord.py overview --format markdown

# Recent messages from the last 24 hours
python3 scripts/query_discord.py messages --days 1 --limit 100 --format markdown

# Objective complaint analysis for the last 3 days
python3 scripts/query_discord.py complaints --days 3 --format markdown

# Search a keyword in the last 7 days
python3 scripts/query_discord.py messages --days 7 --keyword bug --limit 80 --format json

# Pull existing daily reports
python3 scripts/query_discord.py reports --kind daily --days 7 --format markdown

# Check configured email mailboxes
python3 scripts/query_email.py health --format markdown

# Summarize all configured support/billing mailboxes from the last 4 hours
python3 scripts/query_email.py summary --hours 4 --format markdown

# Pull recent email evidence rows from all configured mailboxes
python3 scripts/query_email.py messages --hours 4 --limit 50 --format markdown

# Run custom read-only SQL
python3 scripts/query_discord.py query --sql "SELECT channel_name, COUNT(*) FROM messages WHERE is_deleted = false GROUP BY 1 ORDER BY 2 DESC LIMIT 20"

Analysis Rules

• Treat all database access as read-only. Do not use project migrations, app workers, collectors, or bot code.
• Prefer messages.created_at as the source timestamp. Interpret business day questions in Asia/Shanghai unless the user specifies another timezone.
• Exclude deleted messages by default. Mention deleted-message inclusion only if the user asks for deletion analysis.
• Avoid dumping large raw chat logs. Pull enough rows to support the conclusion, then summarize.
• Cite concrete evidence with message_id, created_at, channel_name or scope_key, and a short paraphrase or short quote.
• For broad questions such as "what are users complaining about", run complaints first. Treat its counts as weighted keyword-taxonomy matches, not human-labeled ground truth; cite the returned matched_terms, match_score, and message evidence when making conclusions.
• For email questions, run scripts/query_email.py summary first. It defaults to all configured mailboxes; use --account support or --account billing only when the user explicitly asks to narrow scope.
• Treat support/billing email access as read-only. Do not send, delete, archive, mark read, or mutate labels from this skill.
• For open-ended email triage such as "what needs urgent handling", prioritize security vulnerability reports, paid-but-not-credited billing issues, login/access blockers, product bugs blocking core use, repeated complaints, and recent high-signal evidence. Treat system notifications and vendor sales outreach as low priority unless the user asks about operations/vendor emails.
• For trend questions, compare equivalent windows and report denominators, not only raw counts.
• If the answer depends on data not present in the returned rows, say what query would be needed next instead of guessing.

Private Configuration

The script looks for credentials in this order:

1. DISCORD_CHAT_INSIGHTS_DATABASE_URL
2. DATABASE_URL
3. config/.env next to this skill

Use config/.env.example as the template for config/.env. Keep config/.env ignored by git. Do not paste database URLs, API keys, or bot tokens into responses.

For email, fill complete EMAIL_ACCOUNT_* blocks in config/.env. The email script automatically reads every complete account block by default. IMAP is preferred for repeatable time-window queries; POP3 is supported only as a read-only fallback. SMTP credentials are not used.