Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
import Foundation
public struct Message: Encodable, Equatable {
public let id: String
public let rowid: Int64
public let timestamp: String
public let isFromMe: Bool
public let text: String
public let textStatus: String
public let from: String?
public let fromIdentifier: String?
public let hasAttachments: Bool
public let editedAt: String?
public let retracted: Bool
}
public struct ThreadSummary: Encodable {
public let threadId: String
public let chatRowids: [Int64]
public let chatGuids: [String]
public let displayName: String
public let participantCount: Int
public let participants: [String]
public let isGroup: Bool
public let service: String?
public let lastMessageAt: String?
public let lastMessage: Message?
}
public struct ThreadPage: Encodable {
public let contact: String
public let threadId: String
public let isGroup: Bool
public let messages: [Message]
}
public struct PageMeta: Encodable {
public let count: Int
public let undecodable: Int
public let hasMore: Bool
public let nextSinceRowid: Int64?
}
public struct ListMeta: Encodable {
public let count: Int
public let undecodable: Int
}
public struct Envelope<Data: Encodable, Meta: Encodable>: Encodable {
public let data: Data
public let meta: Meta
}
public struct Stats: Encodable {
public let db: String
public let threads: Int
public let threadIdShapes: [String: Int]
public let recentMessagesScanned: Int
public let decodedFromBody: Int
public let undecodable: Int
public let edited: Int
public let editedTextDiffers: Int
public let retracted: Int
}
public enum ThreadSelector: Equatable {
case identifier(String)
case chatRowid(Int64)
case chatGuid(String)
}
struct ChatInfo {
let rowid: Int64
let guid: String
let identifier: String
let displayName: String?
let service: String?
let style: Int64
}
public final class Reader {
let db: ChatDB
public init(db: ChatDB) {
self.db = db
}
public func listThreads(limit: Int, includeLastMessage: Bool) throws -> Envelope<[ThreadSummary], ListMeta> {
var rows: [(String, Int64)] = []
try db.query(listSQL(), [.int(Int64(limit))]) { row in
if let identifier = row.text(0) { rows.append((identifier, row.int(1))) }
}
var threads: [ThreadSummary] = []
var undecodable = 0
for (identifier, lastRaw) in rows {
let chats = try chats(for: .identifier(identifier))
guard !chats.isEmpty else { continue }
let participants = try participants(of: chats)
var last: Message?
if includeLastMessage {
last = try messages(in: chats, sinceRowid: nil, since: nil, limit: 1, includeReactions: false).messages.last
if last?.textStatus == "undecodable" { undecodable += 1 }
}
threads.append(summary(identifier: identifier, chats: chats, participants: participants, last: last, lastRaw: lastRaw))
}
return Envelope(data: threads, meta: ListMeta(count: threads.count, undecodable: undecodable))
}
public func getThread(_ selector: ThreadSelector, sinceRowid: Int64?, since: Date?, limit: Int, includeReactions: Bool) throws -> Envelope<ThreadPage, PageMeta>? {
let chats = try chats(for: selector)
guard let first = chats.first else { return nil }
let participants = try participants(of: chats)
let page = try messages(in: chats, sinceRowid: sinceRowid, since: since, limit: limit, includeReactions: includeReactions)
let named = chats.compactMap { $0.displayName }.first { !$0.isEmpty }
let data = ThreadPage(contact: named ?? first.identifier, threadId: first.identifier, isGroup: isGroup(chats, participants), messages: page.messages)
let meta = PageMeta(
count: page.messages.count,
undecodable: page.messages.filter { $0.textStatus == "undecodable" }.count,
hasMore: page.hasMore,
nextSinceRowid: page.messages.map(\.rowid).max() ?? sinceRowid
)
return Envelope(data: data, meta: meta)
}
public func stats(recent: Int = 500) throws -> Stats {
var shapes: [String: Int] = [:]
try db.query("SELECT MIN(COALESCE(chat_identifier, guid)) FROM chat GROUP BY lower(COALESCE(chat_identifier, guid))", []) { row in
shapes[Self.shape(of: row.text(0) ?? ""), default: 0] += 1
}
let threads = shapes.values.reduce(0, +)
var scanned = 0, decoded = 0, undecodable = 0, edited = 0, differs = 0, retracted = 0
try db.query(statsSQL(), [.int(Int64(recent))]) { row in
scanned += 1
let text = row.text(0)
let body = row.blob(1)
let isEdited = row.int(2) != 0
let isRetracted = row.int(3) != 0
if isRetracted { retracted += 1 }
if isEdited {
edited += 1
if let body, case .text(let fromBody) = TypedStream.decodeText(body), Self.clean(fromBody) != Self.clean(text ?? "") { differs += 1 }
}
let resolved = Self.resolveText(text: text, body: body, edited: isEdited, retracted: isRetracted)
if resolved.status == "undecodable" { undecodable += 1 }
if resolved.status == "ok", Self.clean(text ?? "").trimmingCharacters(in: .whitespacesAndNewlines).isEmpty { decoded += 1 }
}
return Stats(db: "ok", threads: threads, threadIdShapes: shapes, recentMessagesScanned: scanned, decodedFromBody: decoded, undecodable: undecodable, edited: edited, editedTextDiffers: differs, retracted: retracted)
}
public func healthCheck() throws {
try db.validateSchema()
try db.check(listSQL())
try db.check(chatSQL(.identifier("")))
try db.check(chatSQL(.chatRowid(0)))
try db.check(chatSQL(.chatGuid("")))
try db.check(participantsSQL(count: 1))
try db.check(statsSQL())
for (rowid, date) in [(false, false), (true, false), (false, true), (true, true)] {
try db.check(messageSQL(chatCount: 1, sinceRowid: rowid, sinceDate: date, includeReactions: false))
try db.check(messageSQL(chatCount: 1, sinceRowid: rowid, sinceDate: date, includeReactions: true))
}
try db.query("SELECT 1 FROM message LIMIT 1", []) { _ in }
}
static func shape(of identifier: String) -> String {
let digits = CharacterSet.decimalDigits
func allDigits(_ text: Substring) -> Bool { !text.isEmpty && text.unicodeScalars.allSatisfy { digits.contains($0) } }
if identifier.hasPrefix("+"), allDigits(identifier.dropFirst()) { return "plus_digits" }
if identifier.contains("@") { return "email" }
if identifier.hasPrefix("chat"), allDigits(identifier.dropFirst(4)) { return "chat_digits" }
if allDigits(Substring(identifier)) { return identifier.count <= 6 ? "short_code" : "long_digits" }
if identifier.hasPrefix("urn:biz:") { return "business" }
return identifier.count >= 30 ? "other_30_plus" : "other_under_30"
}
static func resolveText(text: String?, body: [UInt8]?, edited: Bool, retracted: Bool) -> (text: String, status: String) {
if retracted { return ("", "empty") }
if edited, let body, !body.isEmpty {
switch TypedStream.decodeText(body) {
case .text(let fromBody) where !isBlank(clean(fromBody)): return (clean(fromBody), "ok")
case .undecodable: return ("", "undecodable")
default: break
}
}
if let text, !isBlank(clean(text)) { return (clean(text), "ok") }
guard let body, !body.isEmpty else { return ("", "empty") }
switch TypedStream.decodeText(body) {
case .text(let fromBody):
let cleaned = clean(fromBody)
return isBlank(cleaned) ? ("", "empty") : (cleaned, "ok")
case .undecodable:
return ("", "undecodable")
}
}
static func clean(_ text: String) -> String {
text.replacingOccurrences(of: "\u{FFFC}", with: "")
}
static func isBlank(_ text: String) -> Bool {
text.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty
}
func summary(identifier: String, chats: [ChatInfo], participants: [String], last: Message?, lastRaw: Int64) -> ThreadSummary {
let named = chats.compactMap { $0.displayName }.first { !$0.isEmpty }
let services = chats.compactMap { $0.service }.reduce(into: [String]()) { if !$0.contains($1) { $0.append($1) } }
return ThreadSummary(
threadId: identifier,
chatRowids: chats.map(\.rowid),
chatGuids: chats.map(\.guid),
displayName: named ?? identifier,
participantCount: participants.count + 1,
participants: participants,
isGroup: isGroup(chats, participants),
service: services.isEmpty ? nil : services.joined(separator: ","),
lastMessageAt: last?.timestamp ?? (lastRaw != 0 ? AppleTime.iso(fromRaw: lastRaw) : nil),
lastMessage: last
)
}
func isGroup(_ chats: [ChatInfo], _ participants: [String]) -> Bool {
chats.contains { $0.style == 43 } || participants.count > 1
}
func chats(for selector: ThreadSelector) throws -> [ChatInfo] {
var result: [ChatInfo] = []
let bind: SQLValue
switch selector {
case .identifier(let value): bind = .text(value)
case .chatRowid(let value): bind = .int(value)
case .chatGuid(let value): bind = .text(value)
}
try db.query(chatSQL(selector), [bind]) { row in
result.append(ChatInfo(rowid: row.int(0), guid: row.text(1) ?? "", identifier: row.text(2) ?? "", displayName: row.text(3), service: row.text(4), style: row.int(5)))
}
return result
}
func participants(of chats: [ChatInfo]) throws -> [String] {
var handles: [String] = []
try db.query(participantsSQL(count: chats.count), chats.map { .int($0.rowid) }) { row in
if let id = row.text(0) { handles.append(id) }
}
return handles
}
func messages(in chats: [ChatInfo], sinceRowid: Int64?, since: Date?, limit: Int, includeReactions: Bool) throws -> (messages: [Message], hasMore: Bool) {
let cursor = sinceRowid != nil || since != nil
var binds: [SQLValue] = chats.map { .int($0.rowid) }
if let sinceRowid { binds.append(.int(sinceRowid)) }
if let since { binds.append(.int(AppleTime.raw(from: since))) }
binds.append(.int(Int64(limit + 1)))
var messages: [Message] = []
let sql = messageSQL(chatCount: chats.count, sinceRowid: sinceRowid != nil, sinceDate: since != nil, includeReactions: includeReactions)
try db.query(sql, binds) { row in
messages.append(Self.message(from: row))
}
let hasMore = messages.count > limit
if hasMore { messages.removeLast(messages.count - limit) }
if !cursor { messages.reverse() }
return (messages, hasMore)
}
static func message(from row: Row) -> Message {
let fromMe = row.int(5) != 0
let editedRaw = row.int(8)
let resolved = resolveText(text: row.text(2), body: row.blob(3), edited: editedRaw != 0, retracted: row.int(9) != 0)
return Message(
id: row.text(1) ?? "",
rowid: row.int(0),
timestamp: AppleTime.iso(fromRaw: row.int(4)),
isFromMe: fromMe,
text: resolved.text,
textStatus: resolved.status,
from: fromMe ? nil : row.text(6),
fromIdentifier: fromMe ? nil : row.text(6),
hasAttachments: row.int(7) != 0,
editedAt: editedRaw != 0 ? AppleTime.iso(fromRaw: editedRaw) : nil,
retracted: row.int(9) != 0
)
}
func column(_ table: String, _ name: String, _ alias: String, fallback: String) -> String {
db.has(table, name) ? "\(alias).\(name)" : fallback
}
func listSQL() -> String {
let joinHasDate = db.has("chat_message_join", "message_date")
let date = AppleTime.normalizedSQL(joinHasDate ? "cmj.message_date" : "m.date")
let join = joinHasDate ? "" : " JOIN message m ON m.ROWID = cmj.message_id"
return """
SELECT MIN(COALESCE(c.chat_identifier, c.guid)) AS ident, MAX(\(date)) AS last
FROM chat c JOIN chat_message_join cmj ON cmj.chat_id = c.ROWID\(join)
GROUP BY lower(COALESCE(c.chat_identifier, c.guid))
ORDER BY last DESC
LIMIT ?
"""
}
func chatSQL(_ selector: ThreadSelector) -> String {
let condition: String
switch selector {
case .identifier: condition = "lower(COALESCE(c.chat_identifier, c.guid)) = lower(?)"
case .chatRowid: condition = "c.ROWID = ?"
case .chatGuid: condition = "c.guid = ?"
}
return """
SELECT c.ROWID, c.guid, COALESCE(c.chat_identifier, c.guid), \(column("chat", "display_name", "c", fallback: "NULL")),
\(column("chat", "service_name", "c", fallback: "NULL")), \(column("chat", "style", "c", fallback: "0"))
FROM chat c WHERE \(condition) ORDER BY c.ROWID
"""
}
func participantsSQL(count: Int) -> String {
"""
SELECT DISTINCT h.id FROM chat_handle_join chj JOIN handle h ON h.ROWID = chj.handle_id
WHERE chj.chat_id IN (\(placeholders(count))) ORDER BY h.id
"""
}
func messageSQL(chatCount: Int, sinceRowid: Bool, sinceDate: Bool, includeReactions: Bool) -> String {
let date = AppleTime.normalizedSQL("m.date")
var sql = """
SELECT m.ROWID, m.guid, m.text, \(column("message", "attributedBody", "m", fallback: "NULL")), m.date, m.is_from_me, h.id,
\(column("message", "cache_has_attachments", "m", fallback: "0")), \(column("message", "date_edited", "m", fallback: "0")),
\(column("message", "date_retracted", "m", fallback: "0"))
FROM message m LEFT JOIN handle h ON h.ROWID = m.handle_id
WHERE m.ROWID IN (SELECT cmj.message_id FROM chat_message_join cmj WHERE cmj.chat_id IN (\(placeholders(chatCount))))
"""
if !includeReactions { sql += reactionFilter() }
if sinceRowid { sql += " AND m.ROWID > ?" }
if sinceDate { sql += " AND \(date) > ?" }
if sinceRowid || sinceDate {
sql += " ORDER BY m.ROWID ASC"
} else {
sql += " ORDER BY \(date) DESC, m.ROWID DESC"
}
return sql + " LIMIT ?"
}
func statsSQL() -> String {
"""
SELECT m.text, \(column("message", "attributedBody", "m", fallback: "NULL")), \(column("message", "date_edited", "m", fallback: "0")),
\(column("message", "date_retracted", "m", fallback: "0"))
FROM message m WHERE 1 = 1\(reactionFilter())
ORDER BY m.ROWID DESC LIMIT ?
"""
}
func reactionFilter() -> String {
var sql = ""
if db.has("message", "associated_message_type") { sql += " AND COALESCE(m.associated_message_type, 0) NOT BETWEEN 2000 AND 3999" }
if db.has("message", "item_type") { sql += " AND COALESCE(m.item_type, 0) = 0" }
return sql
}
func placeholders(_ count: Int) -> String {
Array(repeating: "?", count: max(count, 1)).joined(separator: ", ")
}
}