# MailCollector migration and FTS5 example

The article describes a tested proposal. Upstream PR #74 was open when the
article was prepared; adoption by the project is not claimed.

## Source and versions

- Project: https://github.com/ArronHC/MailCollector
- Original issue: https://github.com/ArronHC/MailCollector/issues/11
- Proposal: https://github.com/ArronHC/MailCollector/issues/73
- Implementation: https://github.com/ArronHC/MailCollector/pull/74
- Fork: https://github.com/denisvmedia/MailCollector
- Validated integration: `f808a0909f60939397b004ec6ac3fc3678bce819`.
- Upstream base: `85aeeb4` (v0.14.1).
- Historical schema fixture: v0.5.1, `a0d635a08e36a9a44561ea38b3505b6688defd46`.
  Its `src/database.ts` is unchanged at the upstream base.
- Ptah: released 0.12.0, with binary details in `measured/version.txt`.
- Query and fixture clients: `measured/sqlite-version.json` records Node,
  better-sqlite3 and its SQLite version; `measured/sqlite-cli-version.txt`
  records the sqlite3 CLI used for the article's search output.

The SQL migration pair and `ptah.sum` are byte-identical to our contribution
in the fork. `fts-declaration.excerpt.sql` formats its first statement across
lines; `backfill.excerpt.sql` selects its final statement. Both were executed.
The preexisting application schema is read from the fork's fixture rather than
copied into this post. `seed.sql` and `search.sql` are article fixtures authored
for this reproduction.

## Reproduce the focused CLI example

Use a disposable working directory, Ptah 0.12.0, sqlite3 with FTS5 and trigram
support, and a checkout of the fork at the validated commit. Set `FORK` to that
checkout and `EXAMPLES` to a copy of this article's `examples` directory.

From the example directory, initialize a new fixture database:

```sh
sqlite3 mail.db < "$FORK/tests/fixtures/v0.5.1-schema.sql"
sqlite3 mail.db < seed.sql
```

This creates a fixture representing the completed legacy-adoption boundary.
The version-3 marker in `seed.sql` is valid only for this deliberately prepared
fixture. Do not run it against an installation or use it to skip the real
adapter. The full fork tests below exercise that adapter, including rollback
and the old message-table rebuild.

Run `commands/apply.txt`, followed by `commands/repeat.txt` and
`commands/status.txt`, from this example directory. Run the search with:

```sh
sqlite3 -json mail.db < search.sql
```

The message predates the FTS index. Finding its subject proves that the initial
backfill contributes searchable content. The repeat invocation has no pending
migration. The full application handles initialization automatically and passes
an absolute database URL and migration directory to the same Ptah command.

`measured/*.txt` retain the command output and stderr. The absolute disposable
working-directory prefix in the up output is replaced with `<example-directory>`;
no result text is changed. The article uses labeled excerpts of those files.
`measured/commands.json` records exit codes. `measured/search.json` is sqlite3's
actual JSON output.

## Application verification

In the fork checkout, install its locked dependencies, put released Ptah 0.12.0
on `PATH` (or set `PTAH_BIN`), and run `npm test`. The article preparation reran
all 61 tests successfully; `measured/fork-tests.txt` retains that run's output.
It includes the 30,000-message comparison and diagnostic timings. Those timings
are not a cross-machine performance claim or a CI pass threshold.

Relevant tests are `tests/database-migrations.test.ts` and
`tests/message-search.test.ts`. The upgrade tests compare original revision rows
and account/message/label/job/operation/session data, preserve ciphertext, and
decrypt it using the original key. They exercise v0.5.1 and the old UID identity
layout. Failure tests exercise the adoption transaction and a later SQL file;
repeated startup checks both the adapter and FTS index remain untouched.

Final-head fork checks:

- Node tests, type checks, build and production Docker migration probe; Rust
  tests and Clippy: https://github.com/denisvmedia/MailCollector/actions/runs/37115114440
- Windows NSIS installer: https://github.com/denisvmedia/MailCollector/actions/runs/37115114433

Both workflows completed successfully at the validated integration commit.
Local integration verification also upgraded a populated v0.5.1 fixture inside
the production image, checked search and credential bytes, repeated startup,
and verified `/api/service` on a running server. Task-owned Docker resources
were removed after verification. The image copies Ptah and its license from
`stokaro/ptah:0.12.0`, pinned to multi-platform index digest
`sha256:fdf9c7009018a6cd3954c43f986d8b283c35255ed468c98eb4317529be19928c`.

## Scope

The integration manages the core mail schema. Client/account-sync stores keep
their independent initialization; their tables are outside this patch. It does
not implement cursor pagination or move message bodies into separate storage.
Short queries without three consecutive literal characters keep the old scan
path. Future virtual-table definition changes require an explicit migration.

`diagrams/upgrade.svg` in the parent post is authored semantic SVG. It shows the
separate adoption and SQL-file transactions, not one transaction spanning both.