# msgvault-query

Adopt this skill: https://proskills.md/skills/other/kenn-io-msgvault-skills-claude-code

Page: https://proskills.md/skills/other/kenn-io-msgvault-skills-claude-code
This document is the public skill bundle for that page. It contains SKILL.md and every helper file the catalog carries. No login is required.

## File: skills/claude-code/SKILL.md
size: 5229
upstream: https://github.com/kenn-io/msgvault/blob/main/skills/claude-code/SKILL.md

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

# msgvault-query

Run SQL against the msgvault 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

```bash
# 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](references/views.md) for full column schemas.

### Base views (direct Parquet data)

| View | Description |
|------|-------------|
| `messages` | Raw message metadata partitioned by year |
| `participants` | Email addresses with domain and display name |
| `message_recipients` | Message-to-participant links (from/to/cc/bcc) |
| `labels` | Gmail label names |
| `message_labels` | Message-to-label links |
| `attachments` | Attachment metadata (filename, size) per message |
| `conversations` | Thread grouping |
| `sources` | Synced accounts |

### Convenience views (pre-joined aggregates)

| View | Description |
|------|-------------|
| `v_messages` | Messages with resolved sender (from_email, from_name, from_domain) and labels as JSON array |
| `v_senders` | Per-sender aggregates: message_count, total_size, attachment stats, first/last message |
| `v_domains` | Per-domain aggregates: message_count, total_size, sender_count |
| `v_labels` | Per-label aggregates: message_count, total_size |
| `v_threads` | Per-conversation aggregates: message_count, date range, participant_emails as JSON array |

## Output Formats

```bash
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

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

### Domain breakdown

```sql
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

```sql
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

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

### Thread analysis

```sql
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

```sql
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

```sql
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:

| Task | Command |
|------|---------|
| Archive stats | `msgvault stats` |
| Full-text search | `msgvault search "<query>" --json` |
| Incremental sync | `msgvault sync-incremental <email>` |
| Full sync | `msgvault sync-full <email>` |
| Build analytics cache | `msgvault build-cache` |
| Interactive TUI | `msgvault tui` |

## 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.
````

## File: README.md
upstream: https://github.com/kenn-io/msgvault/blob/main/README.md

````text
<p align="center">
  <img src=".github/assets/msgvault-mark.svg" width="160" height="160" alt="msgvault logo">
</p>

<h1 align="center">msgvault</h1>

<p align="center">
  <a href="https://go.dev"><img src="https://img.shields.io/badge/Go-1.27+-00ADD8?logo=go" alt="Go 1.27+"></a>
  <a href="LICENSE"><img src="https://img.shields.io/badge/License-MIT-yellow.svg" alt="License: MIT"></a>
  <a href="https://msgvault.io"><img src="https://img.shields.io/badge/Docs-msgvault.io-blue" alt="Docs"></a>
  <a href="https://discord.gg/fDnmxB8Wkq"><img src="https://img.shields.io/badge/Discord-Join-5865F2?logo=discord&amp;logoColor=white" alt="Discord"></a>
</p>

<p align="center">
  <a href="https://msgvault.io/docs/">Documentation</a> ·
  <a href="https://msgvault.io/docs/setup/">Setup Guide</a> ·
  <a href="https://msgvault.io/docs/usage/tui/">Interactive TUI</a>
</p>

**Keep your communications and relationships in an archive you own.**

msgvault is a local-first, open-source archive for email, chat, meetings,
calendars, and contacts. Keep your history on your own hardware, find messages
and files, and connect the addresses and handles that belong to the same person.
Use the browser, terminal, CLI, HTTP API, or an AI assistant through MCP.

> **Alpha software.** APIs, storage format, and CLI flags may change. Back up
> your data. This README follows current `main`; see the
> [0.23.0 changelog](docs/changelog.md#0230) for features and upgrade steps.

## What you can do

- **Bring your history together.** Sync mail, chat, calendars, meeting notes,
  and contacts, or import local exports. See the [source guide](docs/guides/sources.md).
- **Find the message or file you need.** Search by sender, date, mailing list,
  or words. Optionally enable search by meaning, document text extraction,
  and image search with a provider you choose.
- **Keep track of people.** Connect identities, curate profiles and relationships,
  browse contact activity, and sync contacts with CardDAV. Optional profile
  automation and conversation briefs require separate consent.
- **Explore the archive.** Group messages by people, domains, time, source, and
  type in the [Web UI](docs/web-ui.md) or [TUI](docs/usage/tui.md). Save useful
  views and monitor background work.
- **Use your own tools.** Query with SQL, export messages and attachments, or
  connect an agent to the [MCP server](docs/usage/chat.md).
- **Preserve and maintain it.** Deduplicate copies, back up the archive, and
  review staged mail deletions before explicitly removing messages upstream.
  Remote deletion preserves archived messages and attachments.

Keyword search and analytics read your archive without contacting its source
services. Sync needs access to those services. Optional model and enrichment
features send selected data to the endpoints you configure; local embedding
servers are also supported. See [recommended configuration](docs/usage/recommended-configuration.md)
for the choices and consent steps.

The daemon also sends anonymous usage telemetry, a daily ping and web, command-line and
terminal UI usage carrying the version, platform and a random install ID, never archive
content. See [telemetry](docs/configuration.md#telemetry) for the full list and how to
turn it off.

## Installation

**macOS / Linux:**
```bash
curl -fsSL https://msgvault.io/install.sh | bash
```

**macOS via Homebrew**
```bash
brew install msgvault
```

**Windows (PowerShell):**
```powershell
powershell -ExecutionPolicy ByPass -c "irm https://msgvault.io/install.ps1 | iex"
```

The installer detects your OS and architecture, downloads the latest release from [GitHub Releases](https://github.com/kenn-io/msgvault/releases), verifies the SHA-256 checksum, and installs the binary. You can review the script ([bash](https://msgvault.io/install.sh), [PowerShell](https://msgvault.io/install.ps1)) before running, or download a release binary directly from GitHub.

Direct downloads include DuckDB, SQLite, and the Web UI; no developer tools or
separate DuckDB installation are needed. See the setup guide for
[native runtime requirements](docs/setup.md#runtime-requirements) and
[vendoring a pinned executable](docs/setup.md#vendor-a-pinned-binary).

To build from source on macOS or Linux instead (requires **Go 1.27+**, **Bun
1.3.14+**, **Node.js 20.19+ on 20.x, 22.13+ on 22.x, or 24+**, and a C/C++
compiler for CGO and to statically link DuckDB; on Debian/Ubuntu also install
`libsqlite3-dev` for the `sqlite3.h` header used by the default `sqlite_vec`
build):

```bash
git clone https://github.com/kenn-io/msgvault.git
cd msgvault
make install
```

**Conda-Forge:**

You can install msgvault [from conda-forge](https://prefix.dev/channels/conda-forge/packages/msgvault) using Pixi or Conda:

```bash
pixi global install msgvault
conda install -c conda-forge msgvault
```

## Quick Start

For Gmail, first create an OAuth credential with the
[OAuth setup guide](docs/guides/oauth-setup.md). Then archive a small first batch:

```bash
msgvault init-db
msgvault add-account you@gmail.com
msgvault sync-full you@gmail.com --limit 100
msgvault daemon restart
```

Open the URL printed by `msgvault daemon restart` in your browser. For foreground
operation, see the [setup guide](docs/setup.md#open-the-web-ui). The release binary
includes the browser application; it needs no separate Node or Bun installation
at runtime. Use `msgvault tui` for the terminal interface.

For another provider or a local export, start with
[choosing a source](docs/guides/sources.md). Google credentials are needed only
for Google-backed sources. The [setup guide](docs/setup.md) covers installation,
first sync, and running on your own server.

## Find your next step

| I want to… | Read |
|---|---|
| Understand the product | [Product overview](https://msgvault.io/) and [archive lifecycle](https://msgvault.io/guide/) |
| Upgrade an existing archive | [Changelog and upgrade notes](https://github.com/kenn-io/msgvault/releases/latest) |
| Find messages by words or meaning, read threads, and locate attachments | [Searching](docs/usage/searching.md) and [Docbank document search](https://msgvault.io/docs/configuration/#search-document-text-through-docbank-unreleased) |
| Maintain contacts and relationships | [People and profiles](docs/usage/people.md) |
| Import local text-message history | [Text message imports](docs/usage/text-messages.md) |
| Configure optional AI features | [Recommended configuration](docs/usage/recommended-configuration.md) |
| Run msgvault on a server | [Remote deployment](docs/guides/remote-deployment.md) |
| Back up or free mailbox space | [Backup](docs/usage/backup.md) and [deleting email](docs/usage/deletion.md) |
| Look up a command or setting | [CLI reference](docs/cli-reference.md) and [configuration](docs/configuration.md) |
| Build or contribute | [Development](docs/development.md) and [agent guide](AGENTS.md) |

## Community

Join the [msgvault Discord](https://discord.gg/fDnmxB8Wkq),
[report an issue](https://github.com/kenn-io/msgvault/issues), or read the
[full documentation](https://msgvault.io/docs/).

## License

[MIT](LICENSE)
````
