Skip to content

Commit 47ff817

Browse files
titanismtitanism
committed
fix(sqlite): safe cross-process VACUUM/rekey file swap to prevent SQLITE_CORRUPT
Root cause of the mass SQLITE_NOTADB/SQLITE_CORRUPT incident (and the mass "needs password reset" / "Database backup fix" notification emails): three code paths renamed a new SQLite file over a LIVE database without any cross-process quiesce (worker vacuum(), inline vacuum in _runDeferredMaintenance, worker rekey()). Stale handles in other PM2 cluster workers kept writing to the old inode and the orphaned encrypted -wal file was replayed onto the new file. - add helpers/safe-vacuum.js: shared safe-swap (kill switch, vacuum_lock + db_swap_lock owner-token locks, db_cache_evict quiesce broadcast, fail-closed WAL checkpoint busy check, tmp verify, -wal/-shm exclusivity proof, lock ownership re-check before rename, atomic rename, post-rename eviction broadcast, Lua CAS releases, per-pid tmp paths) - get-database: gate opens on db_swap_lock (pre-open 60s poll-wait + mget check, retryable SQLITE_BUSY), don't cache custom-path handles, skip maintenance for custom paths, reuse resolved dbFilePath, replace inline vacuum with safeVacuum, DEL db_swap_lock on recovery, skip destructive recovery for custom-path (tmp/backup) files, fail-closed corruption notifications when Redis is down, atomic fleet-wide hourly throttle (50/h) on corrupt alerts, extend db_open_lock past migrateSchema, gate vacuum offload publish on the kill switch - worker: vacuum() uses safeVacuum, backup() verifies the tmp backup file (5-arg getDatabase arity fix), rekey() quiesces via db_swap_lock + db_cache_evict and removes -wal/-shm before the rename - parse-payload: reset action publishes db_cache_evict - env: add SQLITE_AUTO_VACUUM_MIGRATION_ENABLED kill switch (default false) - ansible: tune /mnt/<SQLITE_STORAGE_PATH> instead of /home/deploy/sqlite and hard-fail I/O tuning when data_directory is missing
1 parent 212fb0c commit 47ff817

9 files changed

Lines changed: 671 additions & 239 deletions

File tree

‎.env.defaults‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -138,6 +138,8 @@ SQLITE_HOST=localhost
138138
SQLITE_PORT=3456
139139
SQLITE_RCLONE_ENABLED=false
140140
SQLITE_FTS5_ENABLED=false
141+
# kill switch for the auto-vacuum migration (VACUUM INTO + atomic rename)
142+
SQLITE_AUTO_VACUUM_MIGRATION_ENABLED=false
141143
SQLITE_VERBOSE=false
142144
SQLITE_DEBUG_TIMERS=false
143145
DATABASE_MAP_MAX_SIZE=500

‎.env.schema‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -162,6 +162,7 @@ SQLITE_HOST=
162162
SQLITE_PORT=
163163
SQLITE_RCLONE_ENABLED=
164164
SQLITE_FTS5_ENABLED=
165+
SQLITE_AUTO_VACUUM_MIGRATION_ENABLED=
165166
SQLITE_VERBOSE=
166167
SQLITE_DEBUG_TIMERS=
167168
DATABASE_MAP_MAX_SIZE=

‎PUSH_NOTIFICATIONS.md‎

Lines changed: 9 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -20,26 +20,27 @@ This split has to be decided on the server. A push carrying an FCM `notification
2020

2121
Being a `newMessage` is not on its own enough to raise an alert. The event fires for *any* message appended to *any* mailbox, so saving a draft or filing a Sent copy looks identical to incoming mail at the event level. `isAlertWorthyNewMessage` silences three cases:
2222

23-
- the mailbox is one of `SILENT_MAILBOX_PATHS` (Drafts, Sent, Archive, All Mail, Junk, Spam, Trash and their common aliases), matched case-insensitively against `data.mailbox` or `data.message.folder_path`;
24-
- the message carries `\Draft`, whatever folder it landed in;
25-
- the message arrives already `\Seen`, which a real delivery never is — that is another client copying or migrating existing mail.
23+
* the mailbox is one of `SILENT_MAILBOX_PATHS` (Drafts, Sent, Archive, All Mail, Junk, Spam, Trash and their common aliases), matched case-insensitively against `data.mailbox` or `data.message.folder_path`;
24+
* the message carries `\Draft`, whatever folder it landed in;
25+
* the message arrives already `\Seen`, which a real delivery never is — that is another client copying or migrating existing mail.
2626

2727
A payload that says nothing about its folder stays visible: a stray alert is better than a swallowed delivery. This list is kept in step with `SILENT_FOLDERS` in the mail app's `utils/notification-manager.js`, which applies the same rules to the WebSocket path.
2828

2929
`buildPayload` sets `silent` on the payload, and each transport honors it:
3030

31-
| Transport | User-visible | Silent |
32-
| ----------- | ------------------------------------------------------- | ------------------------------------------------------------------ |
33-
| FCM | `notification` block, `android.priority` `high` | data-only, no `notification` block, `android.priority` `normal` |
34-
| APNs | `pushType` `alert`, `priority` 10, `alert`, `sound` | `pushType` `background`, `priority` 5, `content-available` 1 |
35-
| UnifiedPush | `title` and `body` in the encrypted body | `silent: true`, no `title` or `body` |
31+
| Transport | User-visible | Silent |
32+
| ----------- | --------------------------------------------------- | --------------------------------------------------------------- |
33+
| FCM | `notification` block, `android.priority` `high` | data-only, no `notification` block, `android.priority` `normal` |
34+
| APNs | `pushType` `alert`, `priority` 10, `alert`, `sound` | `pushType` `background`, `priority` 5, `content-available` 1 |
35+
| UnifiedPush | `title` and `body` in the encrypted body | `silent: true`, no `title` or `body` |
3636

3737
Silent events carry no `title` or `body` at all, rather than unused strings. A transport that forwards whatever it is given — the UnifiedPush body reaches an Android client that renders it directly — will otherwise display them.
3838

3939
> **APNs background pushes are best effort.** Apple throttles them and only delivers them to an app that declares the `remote-notification` background mode. Treat the WebSocket as the reliable path for state a client needs promptly, and silent push as an optimization.
4040
4141
Adding an event to `USER_VISIBLE_PUSH_EVENTS` also needs a matching client change: the Android UnifiedPush plugin keeps its own allowlist and suppresses anything outside it, and FCM's `android.notification.channel_id` is currently hardcoded to `new-mail`, which is only correct while mail is the sole visible category.
4242

43+
4344
## Environment variable summary
4445

4546
| Variable | Required for | Value source | Secret |

‎ansible/playbooks/io-filesystem-tuning.yml‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,9 +11,9 @@
1111
path: "{{ data_directory }}"
1212
register: data_dir_check
1313

14-
- name: "Skip I/O tuning if {{ data_directory }} does not exist"
15-
debug:
16-
msg: "Skipping I/O tuning - {{ data_directory }} does not exist yet"
14+
- name: "Fail if {{ data_directory }} does not exist"
15+
fail:
16+
msg: "I/O tuning aborted - {{ data_directory }} does not exist yet"
1717
when: not data_dir_check.stat.exists
1818

1919
# Copy device detection script

‎ansible/playbooks/sqlite.yml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,7 @@
6666
target_hosts: sqlite
6767
service_name: SQLite
6868
service_identifier: sqlite
69-
data_directory: /home/deploy/sqlite
69+
data_directory: "/mnt/{{ lookup('env', 'SQLITE_STORAGE_PATH') | default('storage_do_1', true) }}"
7070
read_ahead_kb: 16
7171

7272

0 commit comments

Comments
 (0)