msgvault-query

name: msgvault-query description: "Query msgvault email archive analytics via SQL views. Use when: querying email history, analyzing senders/domains/labels, thread analysis, attachment stats, messages per month, sender graphs, domain breakdowns, email analytics. Triggers on: msgvault, email archive, email search, email analytics, sender analysis, domain analysis." triggers: - msgvault - email archive - email search - email analytics - sender analysis - domain analysis

msgvault-query

Run SQL against the msgvault email archive via msgvault query. The analytics cache is DuckDB over Parquet — queries run in milliseconds. No DuckDB binary or Parquet path knowledge required.

The analytics cache is built automatically when stale or missing.

Quick Start

# Top 10 senders, table format
msgvault query --format table "SELECT from_email, message_count FROM v_senders ORDER BY message_count DESC LIMIT 10"

# Messages from a domain in 2024, CSV output
msgvault query --format csv "SELECT subject, sent_at, from_email FROM v_messages WHERE from_domain = 'example.com' AND year = 2024 ORDER BY sent_at DESC"

# Label distribution as JSON
msgvault query "SELECT name, message_count, total_size FROM v_labels ORDER BY message_count DESC"

Available Views

See references/views.md for full column schemas.

Base views (direct Parquet data)

Convenience views (pre-joined aggregates)

Output Formats

msgvault query "..."                    # JSON (default): {"columns":[...], "rows":[...], "row_count":N}
msgvault query --format csv "..."       # CSV with header row
msgvault query --format table "..."     # Aligned text table with row count

Common Queries

Top senders

SELECT from_email, from_name, message_count, total_size
FROM v_senders
ORDER BY message_count DESC
LIMIT 20

Domain breakdown

SELECT domain, message_count, sender_count, total_size
FROM v_domains
ORDER BY message_count DESC
LIMIT 20

Messages from a domain in a date range

SELECT subject, sent_at, from_email, snippet
FROM v_messages
WHERE from_domain = 'example.com'
  AND year BETWEEN 2022 AND 2024
ORDER BY sent_at DESC
LIMIT 50

Label distribution

SELECT name, message_count, total_size
FROM v_labels
ORDER BY message_count DESC

Thread analysis

SELECT conversation_title, message_count, first_message_at, last_message_at, participant_emails
FROM v_threads
ORDER BY message_count DESC
LIMIT 20

Large attachments

SELECT m.subject, m.from_email, m.sent_at, a.filename, a.size
FROM v_messages m
JOIN attachments a ON a.message_id = m.id
ORDER BY a.size DESC
LIMIT 20

Messages per month

SELECT year, month, COUNT(*) AS message_count
FROM messages
GROUP BY year, month
ORDER BY year, month

CLI Commands (non-SQL)

For tasks that don't need SQL aggregation, use these directly:

Tips

• Partition pruning: Always add WHERE year = YYYY or year BETWEEN X AND Y when filtering by date — this skips entire Parquet partitions and cuts query time significantly.
• Labels are JSON: In v_messages, labels is a JSON array string. Use DuckDB's json_array_contains(labels, 'INBOX') to filter by label, or join through message_labels + labels for exact matching.
• Sender resolution is dual-path: v_messages resolves the sender via message_recipients (email messages) with a fallback to messages.sender_id (chat messages). Use v_messages instead of messages whenever you need from_email or from_name.
• All queries are read-only: msgvault query never modifies the archive.
• Default format is JSON: Pipe through jq for further filtering. Use --format table for human-readable output.