email/skills/email-db/SKILL.md

---
name: email-db
title: Email database
description: Use when querying the user's synced email — searching messages, senders, domains, attachments or counts. Carries the SQLite schema and the queries worth knowing, so you do not have to rediscover them.
version: 1
---

**There is one database per ACCOUNT, and the owner may have several.**

```
$OFFICER_EMAIL_ACCOUNTS_DIR/          one directory per connected address
  andre.padez@gmail.com/emails.db
  someone.else@work.com/emails.db
  attachment_cache/                   NOT an account — skip it
```

`$OFFICER_EMAIL_DB` points at exactly **one** of them: the most recently synced. That is the right guess
for "what did X say yesterday" and the wrong one for anything that means *all* my mail — you will search
one mailbox and report as though you searched every one.

So: for a question about a specific account, or a quick lookup, use `$OFFICER_EMAIL_DB`. For anything
that spans the mailbox, **list `$OFFICER_EMAIL_ACCOUNTS_DIR` first** and query each `emails.db` you find,
or `ATTACH` them into one connection. Say which accounts you actually covered.

Query with the `email_db` tool, or with `sqlite3` directly.

**Two things measured on a real 19,000-message database (2026-08-20), because the schema alone misleads:**

- **`read` is almost never populated.** 93 rows of 19,097 had it set. The documented unread query
  (`WHERE read = 0`) therefore returns essentially the whole mailbox. Do not report an unread count from
  it without saying what it actually measures.
- **`labels` is a comma-separated string, not a set.** Real values include `updates,inbox` and
  `important,updates,inbox`, so `labels = 'inbox'` misses most of the inbox. Use `LIKE '%inbox%'`, and
  mind that `promotions,inbox` matches too.

There is a full-text index (`emails_fts`) over the same rows — prefer it to `LIKE '%...%'` on `text_body`
for anything word-shaped, since the table holds ~0.5 GB of bodies.

# Email Database Schema

The email database is a SQLite file containing synced emails from the user's connected accounts (Gmail, IMAP, etc.).

---

## Tables

### emails

Primary table. One row per email message.

| Column | Type | Description |
|--------|------|-------------|
| `id` | TEXT PK | Unique message ID (e.g. Gmail message ID) |
| `integration` | TEXT | Email provider (`gmail`, `imap`) |
| `email_account` | TEXT | Account address this email belongs to |
| `from_name` | TEXT | Sender display name |
| `from_address` | TEXT | Sender email address |
| `from_domain` | TEXT | Sender domain (extracted from address) |
| `to_address` | TEXT | Recipient address |
| `cc` | TEXT | CC recipients |
| `subject` | TEXT | Email subject line |
| `date` | TEXT | ISO 8601 date string (`YYYY-MM-DD HH:MM:SS`) |
| `snippet` | TEXT | Short preview of body text |
| `html` | TEXT | Full HTML body (may be large) |
| `text_body` | TEXT | Plain text body |
| `attachment_count` | INTEGER | Number of attachments (0 if none) |
| `read` | INTEGER | Read status (0 = unread, 1 = read) |
| `deleted` | INTEGER | Soft-delete flag (0 = active, 1 = deleted) |
| `labels` | TEXT | Comma-separated labels (e.g. `INBOX,IMPORTANT`) |

**Indexes**: `date`, `from_domain`, `from_address`, `integration`, `email_account`, `labels`.

### attachments

One row per email attachment.

| Column | Type | Description |
|--------|------|-------------|
| `id` | INTEGER PK | Auto-increment ID |
| `email_id` | TEXT FK | References `emails(id)`, cascades on delete |
| `idx` | INTEGER | Attachment index within the email |
| `filename` | TEXT | Original filename |
| `size` | INTEGER | Size in bytes |
| `content_type` | TEXT | MIME type (e.g. `application/pdf`, `image/png`) |
| `content` | TEXT | Base64-encoded file content |

### sync_meta

Internal sync state. Generally not useful for querying.

| Column | Type | Description |
|--------|------|-------------|
| `key` | TEXT PK | Metadata key |
| `value` | TEXT | Metadata value |

---

## Common Queries

### Recent emails
```sql
SELECT id, date, from_name, from_address, subject, snippet
FROM emails
WHERE deleted = 0
ORDER BY date DESC
LIMIT 20;
```

### Search by subject or body
```sql
SELECT id, date, from_address, subject
FROM emails
WHERE deleted = 0
  AND (subject LIKE '%keyword%' OR text_body LIKE '%keyword%')
ORDER BY date DESC;
```

### Emails from a specific sender
```sql
SELECT id, date, subject, snippet
FROM emails
WHERE from_address = 'sender@example.com' AND deleted = 0
ORDER BY date DESC;
```

### Emails from a domain
```sql
SELECT id, date, from_address, subject
FROM emails
WHERE from_domain = 'example.com' AND deleted = 0
ORDER BY date DESC;
```

### Top senders
```sql
SELECT from_address, from_name, count(*) AS total
FROM emails
WHERE deleted = 0
GROUP BY from_address
ORDER BY total DESC
LIMIT 20;
```

### Top domains
```sql
SELECT from_domain, count(*) AS total
FROM emails
WHERE deleted = 0
GROUP BY from_domain
ORDER BY total DESC
LIMIT 20;
```

### Unread count
```sql
SELECT count(*) AS unread FROM emails WHERE read = 0 AND deleted = 0;
```

### Emails with attachments
```sql
SELECT e.id, e.date, e.from_address, e.subject, e.attachment_count
FROM emails e
WHERE e.attachment_count > 0 AND e.deleted = 0
ORDER BY e.date DESC;
```

### List attachments for an email
```sql
SELECT a.filename, a.size, a.content_type
FROM attachments a
WHERE a.email_id = 'MESSAGE_ID';
```

### Find attachments by type
```sql
SELECT e.id, e.date, e.subject, a.filename, a.size, a.content_type
FROM attachments a
JOIN emails e ON e.id = a.email_id
WHERE a.content_type LIKE 'application/pdf%'
ORDER BY e.date DESC;
```

### Email volume by month
```sql
SELECT strftime('%Y-%m', date) AS month, count(*) AS total
FROM emails
WHERE deleted = 0
GROUP BY month
ORDER BY month DESC;
```

### Emails by label
```sql
SELECT id, date, from_address, subject
FROM emails
WHERE labels LIKE '%INBOX%' AND deleted = 0
ORDER BY date DESC
LIMIT 20;
```

### Full email content
```sql
SELECT id, date, from_name, from_address, to_address, cc, subject, text_body, html, labels
FROM emails
WHERE id = 'MESSAGE_ID';
```

---

## Notes

- **Soft deletes**: Always filter `deleted = 0` unless you want deleted emails.
- **Labels**: Stored as comma-separated text. Use `LIKE '%LABEL%'` to filter.
- **Date sorting**: The `date` column is TEXT in ISO format, so lexicographic sorting works correctly.
- **Large content**: `html` and `text_body` can be very large. Use `snippet` for previews, only select full body when needed.
- **Attachment content**: The `content` column in `attachments` is base64-encoded. It can be very large — avoid selecting it unless you need the actual file data.
- **SQL reference**: For full SQLite syntax, functions, and operators, refer to the **sqlite** tool documentation.