1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
|
package main
// ============================================================================
// Schema + migrations
// ============================================================================
// Each migration is applied exactly once, in order, tracked by schema_version.
// Never modify an existing migration — add a new one instead.
const migration001 = `
CREATE TABLE schema_version (
version INTEGER PRIMARY KEY,
applied_at INTEGER NOT NULL
);
-- Records every UIDVALIDITY value ever seen for a mailbox.
-- A new row means the mailbox was reconstructed and UIDs were reset.
CREATE TABLE mailbox_uidvalidity_log (
mailbox TEXT NOT NULL,
uidvalidity INTEGER NOT NULL,
first_seen INTEGER NOT NULL,
PRIMARY KEY (mailbox, uidvalidity)
);
-- header_raw: RFC 5322 header block only (FetchRFC822Header).
-- Always populated at insert time. Used to parse
-- References and other headers not in the IMAP envelope.
-- display_part: The single MIME part chosen for display (text/html or
-- text/plain bytes). Populated lazily on first open via
-- a two-pass BODYSTRUCTURE + targeted section fetch.
-- NOT the full message — just the selected part bytes.
-- display_part_mime: MIME type of display_part: "text/html" or "text/plain".
CREATE TABLE messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
mailbox TEXT NOT NULL,
uid INTEGER NOT NULL,
uidvalidity INTEGER NOT NULL,
subject TEXT NOT NULL DEFAULT '',
from_addr TEXT NOT NULL DEFAULT '',
date INTEGER NOT NULL DEFAULT 0,
header_raw BLOB,
display_part BLOB,
display_part_mime TEXT,
message_id TEXT,
in_reply_to TEXT,
references_ TEXT,
to_addrs TEXT,
cc_addrs TEXT,
bcc_addrs TEXT,
UNIQUE (mailbox, uid, uidvalidity)
);
CREATE INDEX idx_messages_mailbox_date
ON messages(mailbox, uidvalidity, date DESC);
`
const migration003 = `
-- Contacts explicitly hidden from the /contacts listing.
-- Hiding is reversible via /contacts?show=hidden.
CREATE TABLE hidden_contacts (
address TEXT PRIMARY KEY,
hidden_at INTEGER NOT NULL
);
`
const migration004 = `
-- Contacts marked as important; floated to the top of /contacts.
CREATE TABLE important_contacts (
address TEXT PRIMARY KEY,
marked_at INTEGER NOT NULL
);
`
const migration005 = `
-- Unified contact flags table replacing hidden_contacts and important_contacts.
-- flag values: 'hidden', 'important', 'spam'
CREATE TABLE contact_flags (
address TEXT NOT NULL,
flag TEXT NOT NULL,
marked_at INTEGER NOT NULL,
PRIMARY KEY (address, flag)
);
INSERT INTO contact_flags (address, flag, marked_at)
SELECT address, 'hidden', hidden_at FROM hidden_contacts;
INSERT INTO contact_flags (address, flag, marked_at)
SELECT address, 'important', marked_at FROM important_contacts;
DROP TABLE hidden_contacts;
DROP TABLE important_contacts;
`
const migration002 = `
-- Normalized header table: one row per header field per message.
-- name is lower-cased (e.g. "x-github-sender") for case-insensitive lookup.
-- value is the decoded, unfolded header value.
-- ord preserves the original order of headers within the message.
CREATE TABLE message_headers (
message_id INTEGER NOT NULL REFERENCES messages(id),
name TEXT NOT NULL,
value TEXT NOT NULL,
ord INTEGER NOT NULL
);
CREATE INDEX idx_message_headers_message_id ON message_headers(message_id);
CREATE INDEX idx_message_headers_name ON message_headers(name);
`
const migration006 = `
-- RFC822.SIZE as reported by the IMAP server: the size of the whole message on
-- the wire, in bytes. Fetched alongside the envelope during sync, so it costs
-- no extra round-trip.
--
-- This is an approximation of what a message costs to read, not an exact one:
-- it counts every attachment and the base64 overhead of encoding them, while
-- the rendered text is only the single display part. It is advertised to
-- clients as a hint so they can tell a 3K notification from a 600K digest.
--
-- NULL for messages synced before this column existed; those simply carry no
-- size hint rather than a wrong one.
ALTER TABLE messages ADD COLUMN rfc822_size INTEGER;
`
const migration007 = `
-- The message index orders the whole table by date, across all mailboxes. The
-- only index on date is idx_messages_mailbox_date, whose leading column is the
-- mailbox, so it cannot serve that order: the query planner fell back to a full
-- scan plus a temporary B-tree sort on every request. That is affordable for
-- the ten newest messages and not for a paged listing, where the same scan is
-- repeated for every page.
--
-- id is part of the key because date alone is not unique — a mail server hands
-- out the same second to messages that arrive together, and thousands of rows
-- share a timestamp with another. Ordering by date alone leaves ties in an
-- unspecified order, which is invisible in a single listing but breaks paging:
-- two requests can order the same tied rows differently, so a message is shown
-- on both pages or on neither. The tiebreak makes the order total, and this
-- index makes SQLite able to walk it directly.
CREATE INDEX idx_messages_date ON messages(date DESC, id DESC);
`
const migration008 = `
-- One row per attachment: a leaf MIME part that is not the part being
-- displayed and not an inline image already reachable through /part/{cid}.
--
-- The defining property of the mirror is that it stores headers only and
-- fetches one part on demand, which means an attachment is never downloaded
-- and, until now, never even mentioned: a message arrived with an invoice,
-- the body rendered three lines of cover note, and nothing said the invoice
-- existed. This table records what the BODYSTRUCTURE said was there, so the
-- listing can name it and a reader can decide whether to fetch it.
--
-- idx is a 1-based position within the message and is the handle used in
-- URLs. Filenames are attacker-controlled, may repeat within one message and
-- may be absent entirely, so they cannot address a part; the position can.
--
-- part_path is the IMAP section path ("2.1"), which is what a fetch needs.
CREATE TABLE attachments (
message_id INTEGER NOT NULL REFERENCES messages(id),
idx INTEGER NOT NULL,
part_path TEXT NOT NULL,
mime_type TEXT NOT NULL,
filename TEXT,
size INTEGER,
disposition TEXT,
cid TEXT,
PRIMARY KEY (message_id, idx)
);
-- When the MIME structure of this message was last examined.
--
-- Without it, an empty attachment list is ambiguous: it means either that the
-- message has no attachments or that nobody has looked yet, and those must not
-- read the same. Every message synced before this column existed has NULL, and
-- says so rather than claiming an absence it cannot know. --analyze fills it
-- in for the whole archive.
ALTER TABLE messages ADD COLUMN bodystructure_scanned_at INTEGER;
`
const migration009 = `
-- A name the account owner assigned to an address.
--
-- Every name mailweb printed came out of a From: header, which is written by
-- whoever sent the mail and verified against nothing. This archive holds
-- "Deutsche-Baпk AG" (Cyrillic п) and "DKB AG" at addresses belonging to
-- neither, and both rendered as the contact's name in the same voice as
-- mailweb's own words. A petname is the one name on the page that did not
-- arrive over the wire, and therefore the only one anybody here vouches for.
--
-- Keyed by address alone: contacts are not stored as rows, they are derived per
-- request by grouping messages, so the canonical address string is the whole
-- identity of a contact — the same key contact_flags uses, and it must be
-- written through contactAddress for the same reason.
CREATE TABLE contact_petnames (
address TEXT PRIMARY KEY,
petname TEXT NOT NULL,
set_at INTEGER NOT NULL
);
`
const migration010 = `
-- A reply being written, and not yet sent.
--
-- This is the first thing mailweb stores that did not come off the server. The
-- mirror holds headers and one display part per message and is strictly
-- derived: anything in it can be thrown away and re-fetched. A draft cannot —
-- it is the only copy of something the account owner wrote — so it is kept in
-- its own tables, and nothing that reconciles the mirror against the server
-- looks at them.
--
-- token, not the rowid, is what appears in URLs. A draft is reachable by
-- whoever can reach the listen address, and mailweb has no authentication, so
-- sequential ids would let anything that can guess "2" read a half-written
-- letter and press send on it. The token is 64 bits from the CSPRNG, drawn the
-- same way the text renderings draw their delimiter tokens.
--
-- parent_msg_id is the message being answered, and is deliberately allowed to
-- dangle. A message expunged on the server is deleted from the mirror at the
-- next reconciliation, and a reply must survive its parent going away: the
-- draft is the only copy of what the account owner wrote. Everything the reply
-- needs from the parent — recipients, subject, threading headers — is copied
-- out at creation, so a vanished parent costs the quoted context and nothing
-- else, and readers treat a row that no longer resolves as "parent gone".
--
-- Note that the REFERENCES clauses in these tables document intent and enforce
-- nothing: SQLite ignores foreign keys unless a connection asks for them, and
-- none here does (see reconcileMailbox, which deletes header rows by hand for
-- exactly this reason). Deletions that must cascade are written out in Go.
--
-- sent_at and sent_message_id record that a draft was sent and what it became.
-- A sent draft is kept rather than deleted: it is what the Sent copy was built
-- from, and "this was sent, at this time, as this Message-ID" is the only
-- record mailweb has that ties the two together.
CREATE TABLE drafts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
token TEXT NOT NULL UNIQUE,
parent_msg_id INTEGER REFERENCES messages(id),
subject TEXT NOT NULL DEFAULT '',
in_reply_to TEXT NOT NULL DEFAULT '',
references_ TEXT NOT NULL DEFAULT '',
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
sent_at INTEGER,
sent_message_id TEXT
);
CREATE INDEX idx_drafts_updated ON drafts(sent_at, updated_at DESC);
-- A file attached to a draft, stored inline.
--
-- This is the one place mailweb keeps bytes that are not a display part, and it
-- is a deliberate exception rather than a drift: the header-only rule is about
-- the mirror, which can always re-fetch, and these bytes exist nowhere else
-- until the draft is sent. They are bounded by what the account owner attaches
-- and are the draft's to keep for as long as it lives.
CREATE TABLE draft_assets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
draft_id INTEGER NOT NULL REFERENCES drafts(id),
sha256 TEXT NOT NULL,
mime_type TEXT NOT NULL,
filename TEXT NOT NULL DEFAULT '',
bytes BLOB NOT NULL,
created_at INTEGER NOT NULL
);
CREATE INDEX idx_draft_assets_draft ON draft_assets(draft_id);
-- One entry in a draft's ordered list of contents.
--
-- A draft is a list of typed blocks rather than one string because what goes
-- on the wire is decided by what the blocks are: prose and quotes alone can be
-- sent as text/plain, while a code listing or an image needs a MIME structure
-- that preserves it. Storing the parts separately is what lets that question be
-- answered at send time instead of being guessed while typing.
--
-- position is dense and server-assigned. The editor never computes one: every
-- mutation that can reorder answers with the whole list, so the client cannot
-- drift from the database.
CREATE TABLE draft_blocks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
draft_id INTEGER NOT NULL REFERENCES drafts(id),
position INTEGER NOT NULL,
kind TEXT NOT NULL,
content TEXT NOT NULL DEFAULT '',
meta TEXT NOT NULL DEFAULT '{}',
asset_id INTEGER REFERENCES draft_assets(id)
);
CREATE INDEX idx_draft_blocks_draft ON draft_blocks(draft_id, position);
-- Who a draft may be sent to, one row per address per candidate set.
--
-- The sets are named ('sender', 'all', 'list') and are resolved once, when the
-- draft is created, out of the message being answered. They are stored rather
-- than recomputed at send time for two reasons. The parent may be expunged
-- between composing and sending, and a send whose recipients silently changed
-- underneath the person who read them is the worst failure this feature has.
-- And the send button is labelled from these same rows, so what was read and
-- what goes into the envelope cannot disagree.
--
-- Addresses only. A petname never appears here and is never accepted as one:
-- it may name several addresses, so resolving one means guessing which
-- correspondent was meant. See "Petnames are not addresses" in mailweb(7).
CREATE TABLE draft_recipients (
draft_id INTEGER NOT NULL REFERENCES drafts(id),
set_name TEXT NOT NULL,
kind TEXT NOT NULL,
address TEXT NOT NULL,
position INTEGER NOT NULL,
PRIMARY KEY (draft_id, set_name, kind, address)
);
`
const migration011 = `
-- Two covering indexes for the contacts listing, which was the slowest page in
-- mailweb at roughly 380ms for this author's archive. Neither changes any
-- behaviour; both exist to stop a query reading pages it has no use for.
--
-- The first replaces the index on message_headers(name). Every listing excludes
-- forge notifications with
--
-- WHERE id NOT IN (SELECT message_id FROM message_headers WHERE name = ?)
--
-- and the old index carried only name, so the 21298 entries it found each cost
-- a separate lookup into a 113 MB table to fetch the message_id the query
-- actually wanted. Adding message_id to the index answers the subquery from the
-- index alone: 130ms to 17ms, and the forge listing 12x faster. It is a
-- replacement rather than an addition — name is still the leading column, so it
-- serves everything the old index served, at the same 23 MB.
--
-- The second is subtler and was the larger surprise. The contacts scan reads
-- five narrow columns out of messages, but a table scan walks whole rows, and
-- rows here are dominated by header_raw: 133 MB of table for 5 MB of addresses.
-- Nothing on a request path ever reads header_raw — it is written during sync
-- and read once at startup to populate message_headers — yet every contacts
-- listing paged past all of it. A covering index over exactly the columns the
-- scan wants is 5 MB, and SQLite uses it instead of the table.
--
-- Measured together, through the driver: 380ms to 190ms, with no code change.
-- The cost is about 5 MB net on disk (the header index replaces one of equal
-- size) and roughly 20% on inserts of 5000 rows, on a path that inserts a
-- handful at a time during sync.
--
-- Both are worth stating as a rule: an index whose columns stop short of what
-- the query selects is a per-row lookup wearing the costume of an index scan,
-- and EXPLAIN QUERY PLAN says which one it is — SEARCH ... USING INDEX against
-- SEARCH ... USING COVERING INDEX.
DROP INDEX IF EXISTS idx_message_headers_name;
CREATE INDEX idx_message_headers_name_msg
ON message_headers(name, message_id);
CREATE INDEX idx_messages_contacts
ON messages(id, from_addr, to_addrs, cc_addrs, bcc_addrs, date);
`
// The vendored pdf.js viewer, so that a PDF attachment can be read in the page
// rather than downloaded and opened elsewhere.
//
// This is the one place mailweb stores bytes it did not get from the mail
// server, and it sits oddly beside the header-only mirror: the point of that
// design is that an archive of years costs megabytes, and this is ~4.7MB of
// JavaScript in the same file. It is here rather than on disk because the
// database is the only per-instance state mailweb has — a store path would tie
// the binary to an asset bundle, and a cache directory would be a second thing
// to back up, relocate and reason about.
//
// Named for what it holds rather than as a general asset store. mailweb serves
// its own script from //go:embed static, and a table called static_asset would
// suggest those two live in the same place.
//
// Generations exist so an update is atomic. A new release is written in full
// under generation N+1 while readers still see N, and pdfjs_head is flipped in
// the same transaction that finishes the write. The previous generation is kept
// rather than deleted immediately: a page loaded seconds before an update is
// still fetching viewer.mjs and pdf.worker.mjs, and pulling those out from
// under it would fail the frame it was about to draw.
const migration012 = `
CREATE TABLE pdfjs_asset (
generation INTEGER NOT NULL,
path TEXT NOT NULL, -- 'build/pdf.worker.mjs', as named in the zip
mime_type TEXT NOT NULL,
size INTEGER NOT NULL,
content BLOB NOT NULL,
PRIMARY KEY (generation, path)
);
-- Exactly one row: which generation is live, and which upstream release it
-- came from. The CHECK is what makes "the live generation" a fact about the
-- database rather than a convention the code has to remember.
CREATE TABLE pdfjs_head (
id INTEGER PRIMARY KEY CHECK (id = 1),
generation INTEGER NOT NULL,
version TEXT NOT NULL, -- '6.3.289', the release tag without its 'v'
ingested INTEGER NOT NULL
);
`
var migrations = []string{
migration001,
migration002,
migration003,
migration004,
migration005,
migration006,
migration007,
migration008,
migration009,
migration010,
migration011,
migration012,
}
|