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
pub const MIGRATIONS: &[&str] = &[
// Our Ed25519 secret. olm_account_data left for forward compat but unused.
"CREATE TABLE IF NOT EXISTS identity (
id INTEGER PRIMARY KEY CHECK (id = 1),
ed25519_secret BLOB NOT NULL,
olm_account_data BLOB,
created_at INTEGER NOT NULL
);",
// Rooms we've created or joined
"CREATE TABLE IF NOT EXISTS rooms (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
creator_fingerprint TEXT NOT NULL,
encrypted INTEGER NOT NULL,
passphrase_salt BLOB,
created_at INTEGER NOT NULL,
last_active INTEGER
);",
// Per-room Megolm sessions: ours (outbound) and others' (inbound)
"CREATE TABLE IF NOT EXISTS room_megolm_sessions (
room_id TEXT NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
sender_fingerprint TEXT NOT NULL,
session_id TEXT NOT NULL,
session_data BLOB NOT NULL,
is_outbound INTEGER NOT NULL,
created_at INTEGER NOT NULL,
PRIMARY KEY (room_id, sender_fingerprint, session_id)
);",
// Known members of each room, keyed by their fingerprint (the stable
// cryptographic identity). peer_id is informational and often unknown
// at the app layer, so it is not part of the primary key.
"CREATE TABLE IF NOT EXISTS room_members (
room_id TEXT NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
peer_id TEXT NOT NULL DEFAULT '',
fingerprint TEXT NOT NULL,
last_seen INTEGER,
PRIMARY KEY (room_id, fingerprint)
);",
"CREATE TABLE IF NOT EXISTS room_messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
room_id TEXT NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
sender_fingerprint TEXT NOT NULL,
direction TEXT NOT NULL CHECK (direction IN ('in', 'out')),
body TEXT NOT NULL,
sent_at INTEGER NOT NULL
);",
"CREATE INDEX IF NOT EXISTS idx_room_messages_room ON room_messages(room_id, sent_at);",
"CREATE INDEX IF NOT EXISTS idx_room_members_room ON room_members(room_id);",
// Peers we've manually dialed. We auto-reconnect on the next launch so
// the user doesn't have to retype an address to rejoin a room.
"CREATE TABLE IF NOT EXISTS known_peers (
address TEXT PRIMARY KEY,
label TEXT,
last_connected_at INTEGER,
last_attempt_at INTEGER,
created_at INTEGER NOT NULL
);",
// File attachments offered / received in a room. A row is created
// the moment we see a FileOffer; status moves through the lifecycle
// as chunks arrive and the user activates the card.
"CREATE TABLE IF NOT EXISTS room_attachments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
room_id TEXT NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
message_id INTEGER,
sender_fingerprint TEXT NOT NULL,
file_id TEXT NOT NULL,
name TEXT NOT NULL,
mime TEXT,
size_bytes INTEGER NOT NULL,
status TEXT NOT NULL CHECK (status IN ('offered','downloading','ready','saved','failed','cancelled')),
cache_path TEXT,
saved_path TEXT,
error TEXT,
encrypted INTEGER NOT NULL DEFAULT 0,
wrapped_key TEXT,
nonce TEXT,
megolm_session_id TEXT,
content_hash TEXT,
created_at INTEGER NOT NULL,
UNIQUE(room_id, file_id)
);",
"CREATE INDEX IF NOT EXISTS idx_room_attachments_room ON room_attachments(room_id);",
// Phase 5: contact verification — user marks a member's fingerprint
// as verified after comparing it out-of-band. Default 0 (unverified).
"ALTER TABLE room_members ADD COLUMN verified INTEGER NOT NULL DEFAULT 0;",
// Phase 6 QoL: per-room mute flag.
"ALTER TABLE rooms ADD COLUMN muted INTEGER NOT NULL DEFAULT 0;",
// Phase 6: display names — our own, plus per-room remembered names
// of other members.
"ALTER TABLE identity ADD COLUMN display_name TEXT;",
"ALTER TABLE room_members ADD COLUMN display_name TEXT;",
// Phase 0 (v0.3.0): app-level signed message envelopes. Members learn
// each others' pubkeys from `MemberAnnounce.sender_ed25519_pubkey`
// and persist them here so `SignedRoomMessage` envelopes can be
// verified without re-asking the network on every message.
"ALTER TABLE room_members ADD COLUMN ed25519_pubkey TEXT;",
// Phase A (v0.3.0): inbound-dial accept. Trusted=1 means an inbound
// connection from a peer with this fingerprint bypasses the prompt.
// Fingerprint is learned from Identify after the dial completes, so
// it's nullable on pre-Phase-A rows.
"ALTER TABLE known_peers ADD COLUMN fingerprint TEXT;",
"ALTER TABLE known_peers ADD COLUMN trusted INTEGER NOT NULL DEFAULT 0;",
// Phase A: a fingerprint the user has explicitly rejected. Inbound
// connections from a blocked fingerprint are auto-disconnected on
// every restart (the in-memory blocklist on its own would reset).
"CREATE TABLE IF NOT EXISTS blocked_peers (
fingerprint TEXT PRIMARY KEY,
blocked_at INTEGER NOT NULL
);",
// Phase B: soft owner role. 'owner' = can grant other owners and
// ban members; 'member' = vanilla participant. The creator of a
// room is auto-promoted at start_room time; subsequent grants
// come from `RoomMessage::OwnerGrant` (signed envelopes).
"ALTER TABLE room_members ADD COLUMN role TEXT NOT NULL DEFAULT 'member';",
// Phase B: per-room ban list. A banned fingerprint is ignored by
// honest clients — their MemberAnnounce is dropped, their messages
// skipped. The cryptographic enforcement is the immediate
// RotateRoomKey that follows a ban: the banned peer can't unwrap
// the new session key without the new passphrase.
"CREATE TABLE IF NOT EXISTS room_bans (
room_id TEXT NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
banned_fingerprint TEXT NOT NULL,
banned_by_fingerprint TEXT NOT NULL,
signature_b64 TEXT NOT NULL,
banned_at INTEGER NOT NULL,
PRIMARY KEY (room_id, banned_fingerprint)
);",
"CREATE INDEX IF NOT EXISTS idx_room_bans_room ON room_bans(room_id);",
// Phase G: global per-fingerprint verification — populated when an
// SAS verification succeeds. Distinct from `room_members.verified`
// (which is per-room) so Phase E's global inbound filter can
// answer "is this fingerprint SAS-verified at all?" in one query.
"CREATE TABLE IF NOT EXISTS verified_peers (
fingerprint TEXT PRIMARY KEY,
verified_at INTEGER NOT NULL
);",
// Phase E: simple app-wide settings KV. First use: a global
// 'verified_only_inbound' flag that auto-rejects inbound dials
// from unverified fingerprints without prompting.
"CREATE TABLE IF NOT EXISTS app_settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);",
// Phase E: per-room verified-only join. When 1, existing members
// refuse to wrap their session key for an unverified joiner's
// MemberAnnounce, and the lowest-fp owner sends a signed
// `JoinRefused` so the joiner sees an explanation instead of a
// silent hang.
"ALTER TABLE rooms ADD COLUMN verified_only_join INTEGER NOT NULL DEFAULT 0;",
// Phase H: a flag for "we've shown the welcome-and-key-concepts
// onboarding card to this user". Persisted on identity (single
// row) so it doesn't reappear next launch.
"ALTER TABLE identity ADD COLUMN onboarding_seen INTEGER NOT NULL DEFAULT 0;",
// huddle 0.5: per-peer profile cache populated by signed
// ProfileUpdate broadcasts. `username = NULL` means the peer has
// explicitly cleared their username and should render as
// `[anonymous]`. `updated_at` is the sender's claimed monotonic ms;
// last-write-wins so an out-of-order replay can't downgrade a
// newer name.
"CREATE TABLE IF NOT EXISTS peer_profiles (
fingerprint TEXT PRIMARY KEY,
username TEXT,
updated_at INTEGER NOT NULL
);",
// huddle 0.7: explicit room kind ('direct' = 1-1 DM, 'group' = N-way
// room). Existing rooms back-fill to 'group' via the column default —
// they were created via the named `start_room` flow with group
// ergonomics from the start, so the back-fill is loss-free.
// RoomKind drives the sidebar split: DMs go in the Direct messages
// section, groups in Group rooms. Direct rooms also reject any
// MemberAnnounce that would push them past 2 members (honest-client
// enforcement) and are filtered out of third parties' discovery
// caches.
"ALTER TABLE rooms ADD COLUMN kind TEXT NOT NULL DEFAULT 'group';",
// huddle 0.7.7: pending inbound friend requests. When an `InboundDial`
// modal isn't acted on within the 15s in-memory window, we spill the
// request here instead of just rejecting — the user gets up to 3 days
// to review and accept (or reject) later from the People pane.
//
// Primary key is (fingerprint, address) so a peer who dials from
// multiple addresses (LAN + relay-circuit) gets one row per address.
// On Accept we re-dial the stored address and run the same trust
// upsert as `trust_inbound`. A startup sweep drops rows older than
// 3 days (`PENDING_FRIEND_REQUEST_TTL_SECS`), so the table size
// stays bounded without an extra background task.
"CREATE TABLE IF NOT EXISTS pending_friend_requests (
fingerprint TEXT NOT NULL,
address TEXT NOT NULL,
peer_id TEXT NOT NULL,
received_at INTEGER NOT NULL,
PRIMARY KEY (fingerprint, address)
);",
"CREATE INDEX IF NOT EXISTS idx_pending_friend_requests_received
ON pending_friend_requests(received_at);",
];