Metadata-Version: 2.5
Name: signal-sqlite
Version: 0.1.1
Summary: Extract Signal backups to searchable SQLite; query and browse channels and conversations with a CLI and TUI.
Project-URL: Homepage, https://github.com/jglanz/signal-sqlite
Project-URL: Source, https://github.com/jglanz/signal-sqlite
Project-URL: Issues, https://github.com/jglanz/signal-sqlite/issues
Author-email: Jonathan Glanz <jonathan.glanz@gmail.com>
Maintainer-email: Jonathan Glanz <jonathan.glanz@gmail.com>
License-Expression: AGPL-3.0-only
License-File: LICENSE
License-File: THIRD_PARTY_NOTICES.md
Keywords: backup,export,messenger,signal,sqlite
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: End Users/Desktop
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: Python :: 3.15
Classifier: Topic :: Communications :: Chat
Classifier: Topic :: Database
Classifier: Topic :: System :: Archiving :: Backup
Requires-Python: >=3.12
Requires-Dist: click<9,>=8.5.1
Requires-Dist: cryptography<51,>=50.0.2
Requires-Dist: protobuf<8,>=7.36.2
Requires-Dist: textual<9,>=8.2.8
Description-Content-Type: text/markdown

# Signal SQLite

Signal SQLite turns Signal's newer **local backup v2 directory format** into a
searchable SQLite database and, by default, decrypted attachment files. Use the
CLI to extract a backup, list and search channels, and read conversation history
as text or JSON. Use the interactive Textual TUI to browse channels and
conversations, inspect filtered message ranges, and preview or save output. The
same export powers both the query commands and the TUI; querying it is read-only.

The expected input is a `SignalBackups/` directory containing timestamped
snapshots (`main`, `metadata`, `files`) and a shared `files/` media directory. The
original backup is read without modification.

## Install

Requires Python 3.12 or newer. Install the `signal-sqlite` command from PyPI
into an isolated environment:

```bash
pipx install signal-sqlite
# or
uv tool install signal-sqlite

signal-sqlite --help
```

## Quick start

Extract a backup into a new output directory. Without `--key-file`, the command
prompts for the recovery key with hidden input in an interactive terminal.

```bash
signal-sqlite extract-backup ~/SignalBackups ./signal-export
```

Then use the generated database to list channels, search channel names or
participants, read a conversation, or open the TUI:

```bash
signal-sqlite --database ./signal-export channels list
signal-sqlite --database ./signal-export channels search 'Alice'
signal-sqlite --database ./signal-export conversations get CHANNEL_ID
signal-sqlite --database ./signal-export tui
```

`CHANNEL_ID` is the channel ID shown by `channels list`. The `--database` option
also accepts the export directory itself; the default is `output/signal.sqlite3`
relative to the current working directory.

## Development setup

Use Python 3.12+ and Hatch. The root `pyproject.toml` is the dependency manifest;
Hatch creates the project-local `.venv` using its virtual environment type.

```bash
uv python install 3.12
git clone https://github.com/jglanz/signal-sqlite.git
cd signal-sqlite
hatch env create
hatch run signal-sqlite --help
hatch run check
```

Dependencies stay inside the virtual environment. Python source is in
`src/signal_sqlite/`; tests are in `tests/`.

## Extract

```text
signal-sqlite extract-backup [--skip-attachments] [--key-file PATH] INPUT_PATH OUTPUT_PATH
```

```bash
signal-sqlite extract-backup \
  --key-file /run/user/1000/signal-backup.key \
  ~/SignalBackups \
  ./output
```

Both positional paths are required. `INPUT_PATH` accepts the backup root or a
specific timestamped snapshot directory. For a root, the newest snapshot is
selected and printed. An incomplete newest snapshot is reported instead of
silently switching to older data.

`OUTPUT_PATH` is a directory. A missing directory and all missing parents are
created with `os.makedirs`. An existing empty directory is accepted; existing
files and nonempty directories are never overwritten. The output must be outside
the source backup tree.

Attachments are exported by default. Add `--skip-attachments` to retain their
metadata without decrypting the files. Full message bodies held in long-text
attachments can only be indexed when those attachments are exported.

Without `--key-file`, the CLI prompts with hidden input in an interactive terminal.
Noninteractive runs require a key file. Key files accept UTF-8 text with spaces,
tabs, and line breaks. Signal's uppercase display format is accepted, including
`#` for the letter O and `=` for zero. Those documented display substitutions are
reversed and whitespace is removed; the result must contain exactly 64 ASCII
letters/digits. Key files must be regular files owned by the
current user with no group/other access (`chmod 600 PATH`); symlinks are rejected.

### Temporary key file

Run this command in **your terminal**. Paste the key on one line (spaces are
fine). Input is hidden, and the command prints only a randomly named private file
path under your runtime directory. Share only that path with an agent running
the extraction, never the key itself.

```bash
bash -c '
  set -e
  SIGNAL_KEY_TEMPLATE="${XDG_RUNTIME_DIR:?}/signal-backup.XXXXXXXX.key"
  SIGNAL_KEY_OUTPUT_FORMAT="%s"
  SIGNAL_PATH_OUTPUT_FORMAT="\n%s\n"
  signal_key_file=$(mktemp "$SIGNAL_KEY_TEMPLATE")
  IFS= read -r -s -p "Recovery key (one line; hidden): " signal_recovery_key
  printf "$SIGNAL_KEY_OUTPUT_FORMAT" "$signal_recovery_key" > "$signal_key_file"
  unset signal_recovery_key
  printf "$SIGNAL_PATH_OUTPUT_FORMAT" "$signal_key_file"
'
```

Remove that temporary file after extraction. The CLI leaves caller-provided key
files untouched. Recovery and derived master keys are never written to logs or
output. Outputs are plaintext and include the backup's original account/message
records; treat them as private. New directories are mode `0700`, new files `0600`,
including any newly created intermediate directories.

## Commands, output, and search

### Command reference

Queries open an existing export read-only; no recovery key or re-extraction is
needed. The global options `-f/--format text|json` and `-d/--database PATH` go
**before** the command. `--format` applies to extraction and query output and
selects the initial TUI output preview format.

The default format is `text`. The default database is `output/signal.sqlite3`,
relative to the working directory. `--database` accepts either a SQLite file or
an export directory containing `signal.sqlite3`.

| Command | Aliases | Options |
| --- | --- | --- |
| `extract-backup INPUT_PATH OUTPUT_PATH` | — | `--key-file PATH`, `--skip-attachments` |
| `channels list` | `ch all` | `--page`, `--page-size` |
| `channels search QUERY` | `ch match` | `--page`, `--page-size`, `--regex` / `--exp` / `-r`, `--include-messages` |
| `conversations get CHANNEL_ID` | `convo get`, `co get`, `co g` | `--offset`, `--limit`, `--since`, `--until`, `--search`, regex flags, `--include-revisions` |
| `tui` | — | Opens the interactive browser for the selected database |

The query aliases can be combined: `ch` is an alias for `channels`, `all` for
`channels list`, `match` for `channels search`, `convo` and `co` for
`conversations`, and `g` for `conversations get`.

Command-specific options follow the command:

```text
signal-sqlite [-f/--format text|json] [-d/--database PATH] COMMAND ...
signal-sqlite extract-backup [--key-file PATH] [--skip-attachments] INPUT_PATH OUTPUT_PATH
signal-sqlite channels list [--page N] [--page-size N]
signal-sqlite channels search [--page N] [--page-size N] [--regex] [--include-messages] QUERY
signal-sqlite conversations get [--offset N] [--limit N] [--since DATE] [--until DATE] [--search TEXT] [--regex] [--include-revisions] CHANNEL_ID
signal-sqlite tui
```

```bash
signal-sqlite channels list --page 1 --page-size 25
signal-sqlite ch match 'Alice'
signal-sqlite channels search --regex --include-messages 'contract|employment'
signal-sqlite --format text conversations get 187
signal-sqlite --format json co g 187 --since 2026-01-01 --until 2026-11-08
signal-sqlite --database /path/to/export convo get 187 --offset 20 --limit 50
```

Channels are ordered by most recent activity, then numeric channel ID. Each
channel includes its ID, group name when applicable, participants, and latest
activity in UTC and original milliseconds. Activity includes conversation
updates and sent/received times. Empty channels have a null timestamp. Group
participants combine the saved membership snapshot with historical authors and
reactors. Available names are used; a member without an identifiable contact is
represented by its service ID. Your own recipient is included in participants.

Search is a case-insensitive literal substring by default, including saved
profile/system names and nicknames. Regex matching is opt-in and case-insensitive.
`--include-messages` also searches bodies, quotes, captions, filenames, and earlier
revisions. Channel pages default to 50 entries, with a maximum of 1,000; page
numbers start at 1. JSON channel results contain `channels`, `page`, `page_size`,
`total`, and `total_pages`.

Conversation messages are chronological and include the current versions by
default. `--include-revisions` adds historical versions with `parent_message_id`
and `revision_index`. Filters are applied before `--offset` / `--limit`; without
a limit the full matching conversation is returned. `--search` searches the same
message fields as channel message matching; regex flags require `--search`.

Dates accept ISO 8601 dates/times or epoch milliseconds. Naive dates/times use
UTC; explicit timezone offsets are respected. Start and end timestamps are
inclusive. A date-only `--until` includes that whole day. For example,
`--since 2026-11-01 --until 2026-11-01` selects one full UTC day.

Conversation JSON is one object with `channel`, `messages`, `attachments`,
`total_messages`, `offset`, `limit`, and `has_more`. Attachments appear both in
the top-level array and beneath their message, with `relative_path` from the
database directory and `absolute_path`. Both paths are null for unavailable or
deliberately skipped attachments. Only attachments belonging to the selected
messages are included.

Text conversations place your messages on the right and other messages on the
left, with quotes, reactions, and attachment paths beneath their owning message.
There is no separate attachment listing. Absolute attachment paths are kept
intact, even when longer than the terminal width. Conversation updates and deleted
messages have descriptive placeholders.

The global JSON format also works for `extract-backup`; progress and warnings go
to stderr so stdout contains exactly one JSON object.

### Terminal UI

```bash
signal-sqlite tui
signal-sqlite --database /path/to/export tui
```

The TUI is installed with the package and uses the same read-only query layer as
the CLI. It has three tabs:

- **Channels:** participant/group search, regex and message matching, a keyboard
  navigable table, and Previous/Next paging. Select a row and press Enter to open
  its conversation.
- **Conversation:** your messages appear on the right, others on the left. It
  opens the latest 100 messages; Older, Newer, and Latest navigate the history.
  Expand Message filters to search text, apply regex, set date bounds, or include
  revisions. Apply and Reset update the selected message range.
- **Output:** select the current channel page or current conversation message
  page, preview text/JSON, and save it to a new file. JSON includes message and
  attachment arrays with relative/absolute attachment paths. Saving creates
  missing directories with private permissions and refuses to overwrite files.

`Ctrl+F` focuses channel search, `Esc` returns to the channel table, `Ctrl+R`
refreshes the current data, and `Ctrl+E` opens Output. Use Tab/Shift+Tab to move
between controls and `q` (outside text inputs) or `Ctrl+Q` to quit. The global
`--format` chooses the initial Output preview format. The TUI requires an
interactive terminal; its layout is tested at 80×24 and 120×40.

### Database and files

```text
OUTPUT_PATH/
├── signal.sqlite3
├── attachments/       # absent with --skip-attachments
└── manifest.json
```

- `frames`: byte-exact protobuf payloads and decoded JSON, including the backup
  header and unknown frame types. Record IDs retain their stream ordering.
- `recipients`, `chats`, `messages`, `reactions`, `attachments`: relational
  projections. Source recipient/chat IDs are stored as text to preserve uint64s.
- `messages.parent_message_id` and `revision_index`: distinguish current messages
  from their revisions. Original millisecond timestamps are preserved.
- `message_details`: messages joined to sender/conversation names and UTC dates.
- `messages_fts`: Unicode FTS5 search over bodies, quoted text, other text
  (including captions), and filenames; revisions are searchable too.
- `export_metadata`: the manifest information embedded in the database.

For example, in any SQLite client with FTS5 support:

```sql
SELECT m.date_sent_utc, m.conversation_name, m.author_name, m.body
FROM messages_fts
JOIN message_details AS m ON m.id = messages_fts.rowid
WHERE messages_fts MATCH 'employment'
  AND m.parent_message_id IS NULL
ORDER BY m.date_sent_ms;
```

Omit the `parent_message_id` condition to include revision matches. SQLite's FTS5
query syntax supports phrases and Boolean operators. Exported attachment names
are generated from local media identifiers with safe suffixes; the original
filename, caption, relative output path, verification hashes, and export status
are stored in `attachments`.

`manifest.json` records source snapshot hashes, backup/tool versions, counts,
warnings, and completeness. The frame count includes the header; current messages
and revisions are counted separately. Attachment counts describe references,
so multiple references may share a single exported file.

The main backup is authenticated before parsing. Attachments are authenticated,
trimmed to their declared plaintext size, and checked against their plaintext
hash before publication. Work is staged privately and published by directory
rename after SQLite/FTS integrity verification.

Exit status `0` means extraction completed (including deliberately skipped media).
Status `1` indicates an error; if only individual attachment extraction/indexing
failed, a usable database is still published with `complete: false`. Missing
remote-only attachments are recorded as `unavailable`. Usage errors return `2`.
Invalid keys, a corrupt main stream, unsupported versions, or invalid references
publish no extracted data; a newly created output directory may remain empty.

## Development and compatibility

```bash
hatch run check
hatch build
hatch run proto
```

The tests use synthetic encrypted backups and Signal's public derivation vectors.
They never require a real recovery key. The pinned schemas and provenance are in
`src/signal_sqlite/_proto/`; their upstream license is retained in
`THIRD_PARTY_NOTICES.md`. Regenerate bindings with `hatch run proto`, then rerun
checks and build.

This version supports local metadata version 1 and message backup version 1.
Unknown protobuf fields remain in original record BLOBs even when they cannot be
represented in normalized tables. This tool creates an analysis database; it
does not restore a Signal installation, fetch remote media, or parse older
Android single-file backups or the live Signal Desktop SQLCipher database.

## License

GNU Affero General Public License v3.0 only (`AGPL-3.0-only`). See `LICENSE`.
