# SQL over $SOURCES

Read-only SQLite over a private snapshot of the files you can read. It refreshes when sources change; `snapshot.seq`
identifies the version an answer came from.

## Discover

- `sql_schema` (CLI `shepherd schema`): every table with row count, columns, completeness and one example. Curated views
  with no rows yet are listed by name in `emptyViews`.
- `sql_schema` with `table` (CLI `shepherd schema TABLE`): full typed columns, the source layout, all examples.
- Table kinds: `files` (one row per file: path, provider, dir, name, ext, size, sha256, version_id, captured_at),
  `records` (one row per JSON document: path, provider, schema, parts, data), `csv_*` (typed tables from CSV files),
  and curated views over `records`. Anything a view does not expose is still in `records.data` / a view's `raw_payload`.

## Rules that matter

- One SELECT or WITH ... SELECT. Use `?` placeholders and `params` for values.
- Default 500 rows (MCP 200), max 5,000 (MCP 1,000); add ORDER BY and page with `offset = nextOffset`.
- `complete: false` with `pending` = documents not indexed yet (retry later); `errors` = named files that cannot be
  rows (e.g. malformed JSON). State this in your answer instead of presenting partial counts as totals.
- `cellsCut` = values longer than `maxCellChars` (default 16384) were cut; each is listed (row, column, full length in
  characters as SQLite counts them — an emoji is one; bytes for blobs). Cuts never split a character.
  Ask again with `maxCellChars` up to 4194304, or page one value: `SELECT substr(text, ?, 16384) ... WHERE path = ?`.
- Paging: pass `seq` (the previous `snapshot.seq`) with `offset`; `snapshotMoved` means the data changed in between.
- JSON: `json_extract(data, '$.a.b')`, `json_each(data, '$.list')`. Booleans come back as 1/0.
- Times are ISO-8601 text (UTC unless the provider gave an offset); compare as text, e.g. `sent_at >= '2026-10-01'`.

## Messages (iMessage / SMS from the user's Mac)

| View | One row per | Key columns |
| --- | --- | --- |
| `imessage_conversations` | conversation | conversation_id, conversation_type (direct/group), name, counterpart_handle, first/last_message_at, dir |
| `imessage_conversation_months` | conversation × month | conversation_id, month, transcript_path (readable Markdown), rows |
| `imessage_messages` | message (current state) | conversation_id, sent_at, is_from_me, sender_handle, sender_name, text, original_text, edit_versions, unsent_parts, is_reaction, reaction_kind/emoji/removed, reaction_target (target guid) + reaction_target_part/raw, reply_to, thread_root, edited_at, unsent_at, attachment_count, message_guid, source_event_id, path, raw_payload |
| `imessage_message_versions` | part × version of an edited message (Messages' own history) | message_guid, part, version (0 = original), at, text |
| `imessage_participants` | conversation × handle | handle, name, first/last_seen_at, messages_sent |
| `imessage_attachments` | attachment | message_source_event_id, file_name, mime_type, state (stored/pending/unavailable), reason, file_path, sha256, file_present, bytes_verified |
| `imessage_contacts` | synced address-book entry | handle, name, variants |
| `imessage_message_edits` | earlier state of a changed message | source_event_id, observed_at, kinds, previous_text |

Caveats: names are what the Mac resolved and uploaded (`sender_name`, `imessage_contacts`); a NULL name means none was
available or the number matched different contacts (ambiguous), not an unknown person. `chat_name` is the title when
the row was uploaded; the current title is `imessage_conversations.name`. `original_text` comes from Messages' edit
history when the Mac uploaded it, else from the first version the server saw. Tapbacks are rows with `is_reaction = 1` (exclude them when counting messages).
Archived duplicate uploads are excluded from `imessage_messages` but kept in `activity_events` (source
`imessage_alias`). An attachment with `state = 'unavailable'` was not on the Mac (e.g. iCloud-only); `reason` says why.

```sql
-- latest conversations
SELECT name, conversation_type, last_message_at, conversation_id FROM imessage_conversations
ORDER BY last_message_at DESC LIMIT 20;

-- one conversation, newest first, without tapbacks
SELECT sent_at, CASE is_from_me WHEN 1 THEN 'me' ELSE coalesce(sender_name, sender_handle) END AS sender, text
FROM imessage_messages WHERE conversation_id = ? AND is_reaction = 0 ORDER BY sent_at DESC LIMIT 50;

-- who talks most in a group
SELECT coalesce(p.name, c.name, p.handle) AS who, p.messages_sent FROM imessage_participants p
LEFT JOIN imessage_contacts c ON c.handle = p.handle WHERE p.conversation_id = ? ORDER BY p.messages_sent DESC;

-- replies, with the message each one answers
SELECT r.sent_at, coalesce(r.sender_name, r.sender_handle) AS who, r.text AS reply, o.text AS replied_to
FROM imessage_messages r JOIN imessage_messages o ON o.message_guid = r.reply_to ORDER BY r.sent_at DESC LIMIT 20;

-- tapbacks: who reacted how to what (reaction_removed = 1 when it was taken back)
SELECT t.sent_at, CASE t.is_from_me WHEN 1 THEN 'me' ELSE coalesce(t.sender_name, t.sender_handle) END AS reactor,
       t.reaction_kind, t.reaction_removed, o.text AS on_message
FROM imessage_messages t LEFT JOIN imessage_messages o ON o.message_guid = t.reaction_target WHERE t.is_reaction = 1;

-- photos sent in a date range, with the stored file to open
SELECT a.message_at, m.chat_name, a.file_name, a.file_path FROM imessage_attachments a
JOIN imessage_messages m ON m.source_event_id = a.message_source_event_id
WHERE a.mime_type LIKE 'image/%' AND a.bytes_verified = 1 AND a.message_at BETWEEN ? AND ? ORDER BY a.message_at;
```

Then `read` / `cat` the `path`, `transcript_path` or `file_path` a row gives you (`read` shows PNG/JPEG/GIF/WebP images
to the model; `shepherd get` downloads any attachment byte-exactly).

## Other providers (curated views)

`gmail_messages` (+ subject/from/to/date/snippet from the raw message), `gmail_attachments`, `slack_conversations`,
`slack_messages` (Slack user ids, not names), `calendar_events`, `contacts` (Google), `jira_issues`, `linear_issues`,
`github_repositories`, `granola_notes`, `stripe_objects`, `discord_messages`, and `activity_events` (every provider's raw
event ledger with `raw_payload`). For a provider without a view, explore `records`:

```sql
SELECT schema, count(*) FROM records WHERE provider = ? GROUP BY 1 ORDER BY 2 DESC;
SELECT path, data FROM records WHERE provider = ? AND schema = ? LIMIT 3;
```