| 1 | import Foundation |
| 2 | import SQLite3 |
| 3 | |
| 4 | // 🤖 The local message store: every message decrypted ONCE, in the background, |
| 5 | // at ingest — then the UI reads plaintext rows with indexed queries and the |
| 6 | // main thread never touches crypto again. Also the foundation search stands on |
| 7 | // (FTS5 rides along from day one). |
| 8 | // |
| 9 | // E2E posture (deliberate, documented like the unfurl exception): the SERVER |
| 10 | // still only ever sees ciphertext — this database lives on the device, which |
| 11 | // already holds the keys in its Keychain. The file is OS-protected |
| 12 | // (complete-until-first-auth, so the store works after unlock exactly like the |
| 13 | // keys) and EXCLUDED FROM BACKUPS, so plaintext never leaves the device inside |
| 14 | // an iCloud/iTunes backup. |
| 15 | // |
| 16 | // An actor over a serial connection: all statements run off the main actor by |
| 17 | // construction. sqlite3 is compiled in on both platforms — the same engine the |
| 18 | // server itself runs on, fittingly. |
| 19 | public actor MessageStore { |
| 20 | public static let shared = MessageStore() |
| 21 | |
| 22 | public struct StoredMessage: Sendable { |
| 23 | public let id: Int64 |
| 24 | public let at: Int64 |
| 25 | public let edited: Bool |
| 26 | public let reactions: [Reaction] |
| 27 | public let payload: MessagePayload |
| 28 | |
| 29 | public init(id: Int64, at: Int64, edited: Bool, |
| 30 | reactions: [Reaction], payload: MessagePayload) { |
| 31 | self.id = id |
| 32 | self.at = at |
| 33 | self.edited = edited |
| 34 | self.reactions = reactions |
| 35 | self.payload = payload |
| 36 | } |
| 37 | } |
| 38 | |
| 39 | private var db: OpaquePointer? |
| 40 | |
| 41 | // The App Group container, so the notification extension can ingest a |
| 42 | // pushed message into the SAME store the app reads (still on-device, |
| 43 | // OS-protected, never backed up). Falls back to Application Support where |
| 44 | // no group container exists (ad-hoc dev signing without the entitlement). |
| 45 | private static let appGroupID = "group.com.elevenmessenger.messenger" |
| 46 | |
| 47 | private func storeDir() -> URL { |
| 48 | let fm = FileManager.default |
| 49 | let fallback = (fm.urls(for: .applicationSupportDirectory, in: .userDomainMask).first |
| 50 | ?? URL(fileURLWithPath: NSTemporaryDirectory())) |
| 51 | .appendingPathComponent("lchat", isDirectory: true) |
| 52 | // Prefer the shared group container. On a sandboxed app that lacks the |
| 53 | // App Group entitlement, `containerURL` can still hand back a path the |
| 54 | // sandbox then refuses to create — so we don't trust the URL alone, |
| 55 | // we confirm the directory actually exists after trying to create it. |
| 56 | // If it doesn't, fall back to Application Support rather than let every |
| 57 | // store op fail on a dead handle (which reads as "search finds nothing" |
| 58 | // while the network-backed feed still renders — #1121). |
| 59 | if let group = fm.containerURL(forSecurityApplicationGroupIdentifier: Self.appGroupID) { |
| 60 | let dir = group.appendingPathComponent("lchat", isDirectory: true) |
| 61 | try? fm.createDirectory(at: dir, withIntermediateDirectories: true) |
| 62 | if fm.fileExists(atPath: dir.path) { |
| 63 | // One-time migration from the pre-group location: move the db |
| 64 | // (and its WAL sidecars) so existing history survives the path |
| 65 | // change. |
| 66 | let old = fallback.appendingPathComponent("messages.db") |
| 67 | let new = dir.appendingPathComponent("messages.db") |
| 68 | if fm.fileExists(atPath: old.path), !fm.fileExists(atPath: new.path) { |
| 69 | for suffix in ["", "-wal", "-shm"] { |
| 70 | try? fm.moveItem(at: URL(fileURLWithPath: old.path + suffix), |
| 71 | to: URL(fileURLWithPath: new.path + suffix)) |
| 72 | } |
| 73 | } |
| 74 | return dir |
| 75 | } |
| 76 | } |
| 77 | // No usable group container — make sure the fallback exists before we |
| 78 | // hand it back, so open() can create the db there. |
| 79 | try? fm.createDirectory(at: fallback, withIntermediateDirectories: true) |
| 80 | return fallback |
| 81 | } |
| 82 | |
| 83 | private func open() -> OpaquePointer? { |
| 84 | if let db { return db } |
| 85 | let dir = storeDir() |
| 86 | try? FileManager.default.createDirectory(at: dir, withIntermediateDirectories: true) |
| 87 | var url = dir.appendingPathComponent("messages.db") |
| 88 | var handle: OpaquePointer? |
| 89 | guard sqlite3_open_v2(url.path, &handle, |
| 90 | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, |
| 91 | nil) == SQLITE_OK, let handle else { return nil } |
| 92 | exec(handle, "PRAGMA journal_mode=WAL") |
| 93 | exec(handle, "PRAGMA synchronous=NORMAL") |
| 94 | // Two processes share this file now (app + NSE); wait out the other's |
| 95 | // write instead of failing with SQLITE_BUSY. |
| 96 | exec(handle, "PRAGMA busy_timeout=3000") |
| 97 | exec(handle, """ |
| 98 | CREATE TABLE IF NOT EXISTS messages( |
| 99 | instance TEXT NOT NULL, |
| 100 | thread TEXT NOT NULL, |
| 101 | id INTEGER NOT NULL, |
| 102 | at INTEGER NOT NULL, |
| 103 | edited INTEGER NOT NULL DEFAULT 0, |
| 104 | reactions TEXT NOT NULL DEFAULT '[]', |
| 105 | payload TEXT NOT NULL, |
| 106 | PRIMARY KEY(instance, thread, id) |
| 107 | ) |
| 108 | """) |
| 109 | exec(handle, "CREATE INDEX IF NOT EXISTS idx_messages_at ON messages(instance, thread, at, id)") |
| 110 | exec(handle, """ |
| 111 | CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts |
| 112 | USING fts5(text, instance UNINDEXED, thread UNINDEXED, msgid UNINDEXED) |
| 113 | """) |
| 114 | // Where each thread's full-history backfill has reached (#584): the |
| 115 | // oldest fetched id, and whether the walk hit the beginning. Additive, |
| 116 | // like every migration here. |
| 117 | exec(handle, """ |
| 118 | CREATE TABLE IF NOT EXISTS backfill( |
| 119 | instance TEXT NOT NULL, |
| 120 | thread TEXT NOT NULL, |
| 121 | oldest INTEGER NOT NULL DEFAULT 0, |
| 122 | complete INTEGER NOT NULL DEFAULT 0, |
| 123 | PRIMARY KEY(instance, thread) |
| 124 | ) |
| 125 | """) |
| 126 | // Never in a backup; protected at rest like the keys. |
| 127 | try? FileManager.default.setAttributes( |
| 128 | [.protectionKey: FileProtectionType.completeUntilFirstUserAuthentication], |
| 129 | ofItemAtPath: url.path) |
| 130 | var values = URLResourceValues() |
| 131 | values.isExcludedFromBackup = true |
| 132 | try? url.setResourceValues(values) |
| 133 | db = handle |
| 134 | return handle |
| 135 | } |
| 136 | |
| 137 | private func exec(_ handle: OpaquePointer, _ sql: String) { |
| 138 | sqlite3_exec(handle, sql, nil, nil, nil) |
| 139 | } |
| 140 | |
| 141 | /// Close the connection so nothing spans suspension. Holding a file lock |
| 142 | /// in a shared (app-group) container while suspended is the 0xdead10cc |
| 143 | /// RunningBoard kill — four of them on a daily-driver phone 26–28 Jul |
| 144 | /// 2026: the app suspended mid-write (or mid-3s busy_timeout wait on the |
| 145 | /// NSE's write lock) and the system executed it. Called when the iOS app |
| 146 | /// backgrounds, when a Background App Refresh pass ends, and after the |
| 147 | /// NSE's per-push ingest; every op reopens lazily via open(), so this is |
| 148 | /// pure lock hygiene — actor serialization means no in-flight statement |
| 149 | /// can be interrupted (statements are prepared and finalized per call). |
| 150 | public func suspend() { |
| 151 | guard let db else { return } |
| 152 | sqlite3_close(db) |
| 153 | self.db = nil |
| 154 | } |
| 155 | |
| 156 | // ---- ingest ------------------------------------------------------------- |
| 157 | |
| 158 | /// Upsert a decrypted batch (history page or a single live frame). Existing |
| 159 | /// rows keep their reactions/edited unless the new row carries fresher ones |
| 160 | /// (live echoes arrive bare — the same merge rule the in-memory fold used). |
| 161 | public func upsert(instance: String, thread: String, _ batch: [StoredMessage]) { |
| 162 | guard let handle = open(), !batch.isEmpty else { return } |
| 163 | exec(handle, "BEGIN") |
| 164 | defer { exec(handle, "COMMIT") } |
| 165 | let sql = """ |
| 166 | INSERT INTO messages(instance, thread, id, at, edited, reactions, payload) |
| 167 | VALUES(?,?,?,?,?,?,?) |
| 168 | ON CONFLICT(instance, thread, id) DO UPDATE SET |
| 169 | at=excluded.at, |
| 170 | edited=CASE WHEN excluded.edited=1 THEN 1 ELSE messages.edited END, |
| 171 | reactions=CASE WHEN excluded.reactions='[]' THEN messages.reactions ELSE excluded.reactions END, |
| 172 | payload=excluded.payload |
| 173 | """ |
| 174 | var stmt: OpaquePointer? |
| 175 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return } |
| 176 | defer { sqlite3_finalize(stmt) } |
| 177 | let enc = JSONEncoder() |
| 178 | for m in batch { |
| 179 | sqlite3_reset(stmt) |
| 180 | bind(stmt, 1, instance) |
| 181 | bind(stmt, 2, thread) |
| 182 | sqlite3_bind_int64(stmt, 3, m.id) |
| 183 | sqlite3_bind_int64(stmt, 4, m.at) |
| 184 | sqlite3_bind_int(stmt, 5, m.edited ? 1 : 0) |
| 185 | bind(stmt, 6, (try? enc.encode(m.reactions)).flatMap { String(data: $0, encoding: .utf8) } ?? "[]") |
| 186 | bind(stmt, 7, (try? enc.encode(m.payload)).flatMap { String(data: $0, encoding: .utf8) } ?? "{}") |
| 187 | sqlite3_step(stmt) |
| 188 | indexText(handle, instance: instance, thread: thread, id: m.id, text: m.payload.text ?? "") |
| 189 | } |
| 190 | } |
| 191 | |
| 192 | public func updateReactions(instance: String, thread: String, id: Int64, _ reactions: [Reaction]) { |
| 193 | guard let handle = open() else { return } |
| 194 | let json = (try? JSONEncoder().encode(reactions)) |
| 195 | .flatMap { String(data: $0, encoding: .utf8) } ?? "[]" |
| 196 | run(handle, "UPDATE messages SET reactions=? WHERE instance=? AND thread=? AND id=?") { |
| 197 | bind($0, 1, json); bind($0, 2, instance); bind($0, 3, thread) |
| 198 | sqlite3_bind_int64($0, 4, id) |
| 199 | } |
| 200 | } |
| 201 | |
| 202 | // ---- full-history backfill (#584) --------------------------------------- |
| 203 | |
| 204 | /// Where a thread's backwards walk has reached. (0, false) = never started. |
| 205 | public func backfillMark(instance: String, thread: String) -> (oldest: Int64, complete: Bool) { |
| 206 | guard let handle = open() else { return (0, false) } |
| 207 | var stmt: OpaquePointer? |
| 208 | guard sqlite3_prepare_v2(handle, |
| 209 | "SELECT oldest, complete FROM backfill WHERE instance=? AND thread=?", |
| 210 | -1, &stmt, nil) == SQLITE_OK else { return (0, false) } |
| 211 | defer { sqlite3_finalize(stmt) } |
| 212 | bind(stmt, 1, instance) |
| 213 | bind(stmt, 2, thread) |
| 214 | guard sqlite3_step(stmt) == SQLITE_ROW else { return (0, false) } |
| 215 | return (sqlite3_column_int64(stmt, 0), sqlite3_column_int64(stmt, 1) == 1) |
| 216 | } |
| 217 | |
| 218 | public func setBackfillMark(instance: String, thread: String, oldest: Int64, complete: Bool) { |
| 219 | guard let handle = open() else { return } |
| 220 | run(handle, """ |
| 221 | INSERT INTO backfill(instance, thread, oldest, complete) VALUES(?,?,?,?) |
| 222 | ON CONFLICT(instance, thread) DO UPDATE SET oldest=excluded.oldest, complete=excluded.complete |
| 223 | """) { |
| 224 | bind($0, 1, instance); bind($0, 2, thread) |
| 225 | sqlite3_bind_int64($0, 3, oldest); sqlite3_bind_int64($0, 4, complete ? 1 : 0) |
| 226 | } |
| 227 | } |
| 228 | |
| 229 | /// The lowest stored id for a thread — where a first walk starts from. |
| 230 | /// 0 when nothing is stored yet. |
| 231 | public func oldestStoredId(instance: String, thread: String) -> Int64 { |
| 232 | guard let handle = open() else { return 0 } |
| 233 | var stmt: OpaquePointer? |
| 234 | guard sqlite3_prepare_v2(handle, |
| 235 | "SELECT id FROM messages WHERE instance=? AND thread=? ORDER BY id ASC LIMIT 1", |
| 236 | -1, &stmt, nil) == SQLITE_OK else { return 0 } |
| 237 | defer { sqlite3_finalize(stmt) } |
| 238 | bind(stmt, 1, instance) |
| 239 | bind(stmt, 2, thread) |
| 240 | guard sqlite3_step(stmt) == SQLITE_ROW else { return 0 } |
| 241 | return sqlite3_column_int64(stmt, 0) |
| 242 | } |
| 243 | |
| 244 | public func delete(instance: String, thread: String, id: Int64) { |
| 245 | guard let handle = open() else { return } |
| 246 | run(handle, "DELETE FROM messages WHERE instance=? AND thread=? AND id=?") { |
| 247 | bind($0, 1, instance); bind($0, 2, thread); sqlite3_bind_int64($0, 3, id) |
| 248 | } |
| 249 | run(handle, "DELETE FROM messages_fts WHERE instance=? AND thread=? AND msgid=?") { |
| 250 | bind($0, 1, instance); bind($0, 2, thread); sqlite3_bind_int64($0, 3, id) |
| 251 | } |
| 252 | } |
| 253 | |
| 254 | private func indexText(_ handle: OpaquePointer, instance: String, thread: String, |
| 255 | id: Int64, text: String) { |
| 256 | run(handle, "DELETE FROM messages_fts WHERE instance=? AND thread=? AND msgid=?") { |
| 257 | bind($0, 1, instance); bind($0, 2, thread); sqlite3_bind_int64($0, 3, id) |
| 258 | } |
| 259 | guard !text.isEmpty else { return } |
| 260 | run(handle, "INSERT INTO messages_fts(text, instance, thread, msgid) VALUES(?,?,?,?)") { |
| 261 | bind($0, 1, text); bind($0, 2, instance); bind($0, 3, thread) |
| 262 | sqlite3_bind_int64($0, 4, id) |
| 263 | } |
| 264 | } |
| 265 | |
| 266 | // ---- reads -------------------------------------------------------------- |
| 267 | |
| 268 | /// The newest `limit` messages of a thread (ascending) — the instant open. |
| 269 | public func page(instance: String, thread: String, before: Int64? = nil, |
| 270 | limit: Int = 200) -> [StoredMessage] { |
| 271 | guard let handle = open() else { return [] } |
| 272 | var sql = "SELECT id, at, edited, reactions, payload FROM messages WHERE instance=? AND thread=?" |
| 273 | if before != nil { sql += " AND id < ?" } |
| 274 | sql += " ORDER BY id DESC LIMIT \(limit)" |
| 275 | var out: [StoredMessage] = [] |
| 276 | var stmt: OpaquePointer? |
| 277 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return [] } |
| 278 | defer { sqlite3_finalize(stmt) } |
| 279 | bind(stmt, 1, instance) |
| 280 | bind(stmt, 2, thread) |
| 281 | if let before { sqlite3_bind_int64(stmt, 3, before) } |
| 282 | while sqlite3_step(stmt) == SQLITE_ROW { |
| 283 | if let m = readRow(stmt) { out.append(m) } |
| 284 | } |
| 285 | return out.reversed() |
| 286 | } |
| 287 | |
| 288 | /// The newest stored message's (id, at) for a thread — the incremental-sync |
| 289 | /// watermark background refresh and the NSE-ingest catch-up key off. (0, 0) |
| 290 | /// when nothing is stored yet. |
| 291 | public func latest(instance: String, thread: String) -> (id: Int64, at: Int64) { |
| 292 | guard let handle = open() else { return (0, 0) } |
| 293 | var stmt: OpaquePointer? |
| 294 | guard sqlite3_prepare_v2(handle, |
| 295 | "SELECT id, at FROM messages WHERE instance=? AND thread=? ORDER BY id DESC LIMIT 1", |
| 296 | -1, &stmt, nil) == SQLITE_OK else { return (0, 0) } |
| 297 | defer { sqlite3_finalize(stmt) } |
| 298 | bind(stmt, 1, instance) |
| 299 | bind(stmt, 2, thread) |
| 300 | guard sqlite3_step(stmt) == SQLITE_ROW else { return (0, 0) } |
| 301 | return (sqlite3_column_int64(stmt, 0), sqlite3_column_int64(stmt, 1)) |
| 302 | } |
| 303 | |
| 304 | /// Full-text search across an instance (newest first). |
| 305 | public func search(instance: String, query: String, limit: Int = 50) |
| 306 | -> [(thread: String, message: StoredMessage)] { |
| 307 | guard let handle = open(), !query.isEmpty else { return [] } |
| 308 | let sql = """ |
| 309 | SELECT m.thread, m.id, m.at, m.edited, m.reactions, m.payload |
| 310 | FROM messages_fts f JOIN messages m |
| 311 | ON m.instance=f.instance AND m.thread=f.thread AND m.id=f.msgid |
| 312 | WHERE f.instance=? AND messages_fts MATCH ? |
| 313 | ORDER BY m.id DESC LIMIT \(limit) |
| 314 | """ |
| 315 | var out: [(String, StoredMessage)] = [] |
| 316 | var stmt: OpaquePointer? |
| 317 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return [] } |
| 318 | defer { sqlite3_finalize(stmt) } |
| 319 | bind(stmt, 1, instance) |
| 320 | bind(stmt, 2, query) |
| 321 | while sqlite3_step(stmt) == SQLITE_ROW { |
| 322 | guard let thread = sqlite3_column_text(stmt, 0).map({ String(cString: $0) }) else { continue } |
| 323 | if let m = readRow(stmt, offset: 1) { out.append((thread, m)) } |
| 324 | } |
| 325 | return out |
| 326 | } |
| 327 | |
| 328 | /// Wipe an instance's rows (sign-out). |
| 329 | public func clear(instance: String) { |
| 330 | guard let handle = open() else { return } |
| 331 | run(handle, "DELETE FROM messages WHERE instance=?") { bind($0, 1, instance) } |
| 332 | run(handle, "DELETE FROM messages_fts WHERE instance=?") { bind($0, 1, instance) } |
| 333 | } |
| 334 | |
| 335 | // ---- plumbing ----------------------------------------------------------- |
| 336 | |
| 337 | private func readRow(_ stmt: OpaquePointer?, offset: Int32 = 0) -> StoredMessage? { |
| 338 | let dec = JSONDecoder() |
| 339 | guard let payloadText = sqlite3_column_text(stmt, offset + 4) |
| 340 | .map({ String(cString: $0) }), |
| 341 | let payload = try? dec.decode(MessagePayload.self, from: Data(payloadText.utf8)) |
| 342 | else { return nil } |
| 343 | let reactions = sqlite3_column_text(stmt, offset + 3) |
| 344 | .map { String(cString: $0) } |
| 345 | .flatMap { try? dec.decode([Reaction].self, from: Data($0.utf8)) } ?? [] |
| 346 | return StoredMessage(id: sqlite3_column_int64(stmt, offset + 0), |
| 347 | at: sqlite3_column_int64(stmt, offset + 1), |
| 348 | edited: sqlite3_column_int(stmt, offset + 2) == 1, |
| 349 | reactions: reactions, |
| 350 | payload: payload) |
| 351 | } |
| 352 | |
| 353 | private func run(_ handle: OpaquePointer, _ sql: String, |
| 354 | _ bindings: (OpaquePointer?) -> Void) { |
| 355 | var stmt: OpaquePointer? |
| 356 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return } |
| 357 | defer { sqlite3_finalize(stmt) } |
| 358 | bindings(stmt) |
| 359 | sqlite3_step(stmt) |
| 360 | } |
| 361 | |
| 362 | private func bind(_ stmt: OpaquePointer?, _ index: Int32, _ value: String) { |
| 363 | sqlite3_bind_text(stmt, index, value, -1, |
| 364 | unsafeBitCast(-1, to: sqlite3_destructor_type.self)) // SQLITE_TRANSIENT |
| 365 | } |
| 366 | } |
| 367 | |
amadan