Fastest and only macOS Dictation app with on-device STT and custom trained AI enhancement model. Windows, iOS testflight! Android, Linux waitlist open! A local Wispr Flow alternative. Follow https://x.com/fluidvoiceapp for more
import Foundation
import SQLite3
struct AnalyticsOutboxItem {
let id: String
let payload: Data
}
enum AnalyticsDatabaseError: Error {
case open(String)
case sqlite(String)
case invalidPayload
}
/// SQLite aggregates and crash-safe upload outbox.
final class AnalyticsDatabase {
private static let performanceBucketUpperBounds = [
25, 50, 75, 100, 150, 200, 300, 500, 750, 1000,
1500, 2000, 3000, 5000, 7500, 10_000, 20_000, 60_000,
]
private let connection: OpaquePointer
private let distinctID: String
private let appVersion: String
private let systemConfiguration: AnalyticsSystemConfiguration
private let calendar: Calendar
private let iso8601 = ISO8601DateFormatter()
init(
url: URL,
distinctID: String,
appVersion: String,
systemConfiguration: AnalyticsSystemConfiguration = .current,
calendar: Calendar = .current
) throws {
self.distinctID = distinctID
self.appVersion = appVersion
self.systemConfiguration = systemConfiguration
self.calendar = calendar
try FileManager.default.createDirectory(
at: url.deletingLastPathComponent(),
withIntermediateDirectories: true
)
var database: OpaquePointer?
let result = sqlite3_open_v2(
url.path,
&database,
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX,
nil
)
guard result == SQLITE_OK, let database else {
let message = database.map { String(cString: sqlite3_errmsg($0)) } ?? "unknown error"
if let database { sqlite3_close(database) }
throw AnalyticsDatabaseError.open(message)
}
self.connection = database
do {
try self.execute("PRAGMA journal_mode=WAL")
try self.execute("PRAGMA synchronous=FULL")
try self.execute("PRAGMA secure_delete=ON")
try self.execute("PRAGMA auto_vacuum=INCREMENTAL")
try self.execute("PRAGMA busy_timeout=3000")
try self.createSchema()
} catch {
sqlite3_close(database)
throw error
}
}
deinit {
sqlite3_close(self.connection)
}
func recordActivity(_ kind: AnalyticsActivityKind, at date: Date) throws {
try self.finalizeDays(before: date)
try self.transaction {
try self.enqueueActivityIfNeeded(kind, at: date)
}
}
func recordUsage(
mode: AnalyticsUsageMode,
transcriptionModel: AnalyticsModelDescriptor?,
aiModel: AnalyticsModelDescriptor?,
at date: Date
) throws {
try self.finalizeDays(before: date)
let day = self.dayString(date)
try self.transaction {
try self.upsertDailyUsage(day: day, mode: mode)
if let transcriptionModel {
try self.upsertDailyModelUsage(
day: day,
role: .transcription,
mode: mode,
descriptor: transcriptionModel
)
}
if let aiModel {
try self.upsertDailyModelUsage(
day: day,
role: .aiPostProcessing,
mode: mode,
descriptor: aiModel
)
}
try self.enqueueActivityIfNeeded(.coreAction, at: date)
}
}
func recordModelUsage(
role: AnalyticsModelRole,
mode: AnalyticsUsageMode,
descriptor: AnalyticsModelDescriptor,
at date: Date
) throws {
try self.finalizeDays(before: date)
try self.transaction {
try self.upsertDailyModelUsage(day: self.dayString(date), role: role, mode: mode, descriptor: descriptor)
try self.enqueueActivityIfNeeded(.coreAction, at: date)
}
}
func recordDictationPerformance(
asrMilliseconds: Int? = nil,
fluidIntelligenceMilliseconds: Int? = nil,
fluidModel: AnalyticsFluidIntelligenceModel? = nil,
tokensPerSecond: Double? = nil,
measuredAppVersion: String,
at date: Date
) throws {
let validTokensPerSecond = tokensPerSecond.flatMap { value in
value.isFinite && value > 0 ? value : nil
}
guard asrMilliseconds != nil || fluidIntelligenceMilliseconds != nil ||
(fluidModel != nil && validTokensPerSecond != nil)
else { return }
try self.finalizeDays(before: date)
let day = self.dayString(date)
try self.transaction {
if let asrMilliseconds {
try self.upsertPerformanceMetric(
day: day,
measuredAppVersion: measuredAppVersion,
metric: "asr",
milliseconds: asrMilliseconds
)
}
if let fluidIntelligenceMilliseconds {
try self.upsertPerformanceMetric(
day: day,
measuredAppVersion: measuredAppVersion,
metric: "fluid_intelligence",
milliseconds: fluidIntelligenceMilliseconds
)
}
if let fluidModel, let validTokensPerSecond {
try self.upsertPerformanceMetric(
day: day,
measuredAppVersion: measuredAppVersion,
metric: "\(fluidModel.rawValue)_tps",
milliseconds: Int(min(validTokensPerSecond.rounded(), 60_001))
)
}
}
}
func recordInsertionLatency(
path: AnalyticsInsertionPath,
outcome: AnalyticsInsertionOutcome,
requestMilliseconds: Int,
readyMilliseconds: Int?,
toggleStopMilliseconds: Int? = nil,
at date: Date
) throws {
try self.finalizeDays(before: date)
let requestMilliseconds = max(0, requestMilliseconds)
let readyMilliseconds = readyMilliseconds.map { max(0, $0) }
let readyCount = readyMilliseconds == nil ? 0 : 1
let readyValue = readyMilliseconds ?? 0
let day = self.dayString(date)
try self.transaction {
try self.run(
"INSERT INTO daily_insertion_latency (" +
"day, delivery_path, outcome, request_count, request_total_ms, request_min_ms, request_max_ms, " +
"ready_count, ready_total_ms, ready_min_ms, ready_max_ms" +
") VALUES (?, ?, ?, 1, ?, ?, ?, ?, ?, ?, ?) " +
"ON CONFLICT(day, delivery_path, outcome) DO UPDATE SET " +
"request_count = request_count + 1, " +
"request_total_ms = request_total_ms + excluded.request_total_ms, " +
"request_min_ms = MIN(request_min_ms, excluded.request_min_ms), " +
"request_max_ms = MAX(request_max_ms, excluded.request_max_ms), " +
"ready_count = ready_count + excluded.ready_count, " +
"ready_total_ms = ready_total_ms + excluded.ready_total_ms, " +
"ready_min_ms = CASE " +
"WHEN excluded.ready_count = 0 THEN ready_min_ms " +
"WHEN ready_count = 0 THEN excluded.ready_min_ms " +
"ELSE MIN(ready_min_ms, excluded.ready_min_ms) END, " +
"ready_max_ms = CASE " +
"WHEN excluded.ready_count = 0 THEN ready_max_ms " +
"WHEN ready_count = 0 THEN excluded.ready_max_ms " +
"ELSE MAX(ready_max_ms, excluded.ready_max_ms) END",
bindings: [
.text(day), .text(path.rawValue), .text(outcome.rawValue),
.integer(requestMilliseconds), .integer(requestMilliseconds), .integer(requestMilliseconds),
.integer(readyCount), .integer(readyValue), .integer(readyValue), .integer(readyValue),
]
)
if let toggleStopMilliseconds,
outcome == .dispatched,
path == .clipboard || path == .clipboardFallback
{
let milliseconds = max(0, toggleStopMilliseconds)
try self.run(
"INSERT INTO daily_clipboard_toggle_latency " +
"(day, delivery_path, sample_count, total_ms, min_ms, max_ms) " +
"VALUES (?, ?, 1, ?, ?, ?) " +
"ON CONFLICT(day, delivery_path) DO UPDATE SET " +
"sample_count = sample_count + 1, " +
"total_ms = total_ms + excluded.total_ms, " +
"min_ms = MIN(min_ms, excluded.min_ms), " +
"max_ms = MAX(max_ms, excluded.max_ms)",
bindings: [
.text(day), .text(path.rawValue),
.integer(milliseconds), .integer(milliseconds), .integer(milliseconds),
]
)
}
}
}
func recordOnboardingStarted(origin: AnalyticsOnboardingOrigin, at date: Date) throws {
try self.finalizeDays(before: date)
try self.transaction {
let flowID = try self.activeOnboardingFlow(origin: origin, at: date)
let key = "onboarding:\(flowID):started"
if try self.insertDedupeKey(key, date: date) {
try self.enqueue(.onboardingStarted, at: date, properties: [
"flow_id": flowID,
"origin": origin.rawValue,
])
}
try self.enqueueActivityIfNeeded(.app, at: date)
}
}
func recordOnboardingStepViewed(
_ step: AnalyticsOnboardingStep,
origin: AnalyticsOnboardingOrigin,
at date: Date
) throws {
try self.transaction {
let flowID = try self.activeOnboardingFlow(origin: origin, at: date)
if step == .playground {
try self.ensureOnboardingTryoutState(flowID: flowID, enteredAt: date)
}
let key = "onboarding:\(flowID):viewed:\(step.rawValue)"
guard try self.insertDedupeKey(key, date: date) else { return }
try self.enqueue(.onboardingStepViewed, at: date, properties: [
"flow_id": flowID,
"origin": origin.rawValue,
"step": step.rawValue,
])
}
}
func recordOnboardingTryoutAttemptStarted(
startMethod: AnalyticsOnboardingTryoutStartMethod,
origin: AnalyticsOnboardingOrigin,
at date: Date
) throws {
try self.transaction {
let flowID = try self.activeOnboardingFlow(origin: origin, at: date)
try self.ensureOnboardingTryoutState(flowID: flowID, enteredAt: date)
try self.run(
"UPDATE onboarding_tryout_state SET attempt_count = attempt_count + 1, " +
"last_start_method = ?, last_outcome = NULL, last_failure_stage = NULL WHERE flow_id = ?",
bindings: [.text(startMethod.rawValue), .text(flowID)]
)
}
}
func recordOnboardingTryoutAttemptResult(
outcome: AnalyticsOnboardingTryoutOutcome,
failureStage: AnalyticsOnboardingTryoutFailureStage?,
origin: AnalyticsOnboardingOrigin,
at date: Date
) throws {
try self.transaction {
let flowID = try self.activeOnboardingFlow(origin: origin, at: date)
try self.ensureOnboardingTryoutState(flowID: flowID, enteredAt: date)
try self.run(
"UPDATE onboarding_tryout_state SET last_outcome = ?, last_failure_stage = ? WHERE flow_id = ?",
bindings: [
.text(outcome.rawValue),
failureStage.map { .text($0.rawValue) } ?? .null,
.text(flowID),
]
)
}
}
func finishOnboardingTryout(
outcome: AnalyticsOnboardingTryoutOutcome,
failureStage: AnalyticsOnboardingTryoutFailureStage?,
origin: AnalyticsOnboardingOrigin,
at date: Date
) throws {
try self.transaction {
let flowID = try self.activeOnboardingFlow(origin: origin, at: date)
try self.ensureOnboardingTryoutState(flowID: flowID, enteredAt: date)
let state = try self.onboardingTryoutState(flowID: flowID)
guard try self.insertDedupeKey("onboarding:\(flowID):tryout_finished", date: date) else { return }
var properties: [String: Any] = [
"flow_id": flowID,
"origin": origin.rawValue,
"outcome": outcome.rawValue,
"duration_bucket": Self.onboardingTryoutDurationBucket(
date.timeIntervalSince1970 - state.enteredAt
),
]
if state.attemptCount > 0 {
properties["attempt_count_bucket"] = Self.onboardingTryoutAttemptCountBucket(state.attemptCount)
}
if let startMethod = state.lastStartMethod {
properties["start_method"] = startMethod
}
if let failureStage {
properties["failure_stage"] = failureStage.rawValue
} else if outcome == .skippedAfterAttempt, let lastFailureStage = state.lastFailureStage {
properties["failure_stage"] = lastFailureStage
}
try self.enqueue(.onboardingTryoutFinished, at: date, properties: properties)
}
}
func skipOnboardingTryout(origin: AnalyticsOnboardingOrigin, at date: Date) throws {
let flowID = try self.activeOnboardingFlow(origin: origin, at: date)
try self.ensureOnboardingTryoutState(flowID: flowID, enteredAt: date)
let state = try self.onboardingTryoutState(flowID: flowID)
let outcome: AnalyticsOnboardingTryoutOutcome = state.attemptCount == 0
? .skippedBeforeAttempt
: .skippedAfterAttempt
try self.finishOnboardingTryout(
outcome: outcome,
failureStage: nil,
origin: origin,
at: date
)
}
func recordOnboardingStepCompleted(
_ step: AnalyticsOnboardingStep,
outcome: AnalyticsOnboardingOutcome,
origin: AnalyticsOnboardingOrigin,
completesFlow: Bool,
at date: Date
) throws {
try self.transaction {
let flowID = try self.activeOnboardingFlow(origin: origin, at: date)
let key = "onboarding:\(flowID):completed:\(step.rawValue)"
if try self.insertDedupeKey(key, date: date) {
try self.enqueue(.onboardingStepCompleted, at: date, properties: [
"flow_id": flowID,
"origin": origin.rawValue,
"step": step.rawValue,
"outcome": outcome.rawValue,
])
}
if completesFlow {
let completionKey = "onboarding:\(flowID):flow_completed"
if try self.insertDedupeKey(completionKey, date: date) {
try self.enqueue(.onboardingCompleted, at: date, properties: [
"flow_id": flowID,
"origin": origin.rawValue,
])
}
try self.run(
"UPDATE onboarding_flows SET completed = 1 WHERE flow_id = ?",
bindings: [.text(flowID)]
)
}
}
}
func recordModelDownloadStarted(
id: String,
descriptor: AnalyticsModelDescriptor,
source: AnalyticsModelDownloadSource,
at date: Date
) throws {
try self.finalizeDays(before: date)
try self.transaction {
let finishKey = "download:\(id):finished"
if try !self.dedupeKeyExists(finishKey),
try self.insertDedupeKey("download:\(id):started", date: date)
{
try self.run(
"INSERT OR IGNORE INTO model_download_attempts " +
"(download_id, provider, model, source, started_at) VALUES (?, ?, ?, ?, ?)",
bindings: [
.text(id), .text(descriptor.provider), .text(descriptor.model),
.text(source.rawValue), .double(date.timeIntervalSince1970),
]
)
try self.enqueue(.modelDownloadStarted, at: date, properties: [
"download_id": id,
"provider": descriptor.provider,
"model": descriptor.model,
"source": source.rawValue,
])
}
try self.enqueueActivityIfNeeded(.coreAction, at: date)
}
}
func recordModelDownloadFinished(
id: String,
descriptor: AnalyticsModelDescriptor,
source: AnalyticsModelDownloadSource,
outcome: AnalyticsModelDownloadOutcome,
duration: TimeInterval?,
at date: Date
) throws {
try self.transaction {
guard try self.insertDedupeKey("download:\(id):finished", date: date) else { return }
if try !self.modelDownloadExists(id: id) {
let inferredStart = duration.map { date.addingTimeInterval(-max(0, $0)) } ?? date
_ = try self.insertDedupeKey("download:\(id):started", date: inferredStart)
try self.enqueue(.modelDownloadStarted, at: inferredStart, properties: [
"download_id": id,
"provider": descriptor.provider,
"model": descriptor.model,
"source": source.rawValue,
])
}
var properties: [String: Any] = [
"download_id": id,
"provider": descriptor.provider,
"model": descriptor.model,
"source": source.rawValue,
"outcome": outcome.rawValue,
]
if let duration, duration >= 0 {
properties["duration_seconds"] = (duration * 10).rounded() / 10
}
try self.enqueue(.modelDownloadFinished, at: date, properties: properties)
try self.run("DELETE FROM model_download_attempts WHERE download_id = ?", bindings: [.text(id)])
}
}
func recoverInterruptedModelDownloads(at date: Date) throws {
let rows = try self.query(
"SELECT download_id, provider, model, source FROM model_download_attempts",
bindings: []
)
for row in rows {
guard row.count == 4 else { continue }
try self.recordModelDownloadFinished(
id: row[0],
descriptor: AnalyticsModelDescriptor(provider: row[1], model: row[2]),
source: AnalyticsModelDownloadSource(rawValue: row[3]) ?? .automatic,
outcome: .interrupted,
duration: nil,
at: date
)
}
}
func finalizeDays(before date: Date) throws {
let today = self.dayString(date)
let usageRows = try self.query(
"SELECT day, dictation_count, command_count, edit_count, meeting_count " +
"FROM daily_usage WHERE day < ? ORDER BY day",
bindings: [.text(today)]
)
let modelRows = try self.query(
"SELECT day, role, mode, provider, model, use_count FROM daily_model_usage " +
"WHERE day < ? ORDER BY day, role, mode, provider, model",
bindings: [.text(today)]
)
let performanceRows = try self.query(
"SELECT day, measured_app_version, measured_os_version, metric, bucket_index, sample_count " +
"FROM daily_dictation_performance WHERE day < ? " +
"ORDER BY day, measured_app_version, measured_os_version, metric, bucket_index",
bindings: [.text(today)]
)
let insertionRows = try self.query(
"SELECT day, delivery_path, outcome, request_count, request_total_ms, request_min_ms, " +
"request_max_ms, ready_count, ready_total_ms, ready_min_ms, ready_max_ms " +
"FROM daily_insertion_latency WHERE day < ? ORDER BY day, delivery_path, outcome",
bindings: [.text(today)]
)
let clipboardToggleRows = try self.query(
"SELECT day, delivery_path, sample_count, total_ms, min_ms, max_ms " +
"FROM daily_clipboard_toggle_latency WHERE day < ? ORDER BY day, delivery_path",
bindings: [.text(today)]
)
guard !usageRows.isEmpty || !modelRows.isEmpty || !performanceRows.isEmpty ||
!insertionRows.isEmpty || !clipboardToggleRows.isEmpty
else { return }
try self.transaction {
for row in usageRows where row.count == 5 {
try self.enqueue(.usageDailySummary, at: date, properties: [
"usage_date": row[0],
"dictation_count": Int(row[1]) ?? 0,
"command_count": Int(row[2]) ?? 0,
"edit_count": Int(row[3]) ?? 0,
"meeting_count": Int(row[4]) ?? 0,
])
}
for row in modelRows where row.count == 6 {
try self.enqueue(.modelUsageDailySummary, at: date, properties: [
"usage_date": row[0],
"role": row[1],
"mode": row[2],
"provider": row[3],
"model": row[4],
"use_count": Int(row[5]) ?? 0,
])
}
for summary in self.performanceSummaries(from: performanceRows) {
try self.enqueue(.dictationPerformanceDailySummary, at: date, properties: summary.properties)
}
for row in insertionRows where row.count == 11 {
let requestCount = Int(row[3]) ?? 0
let requestTotal = Int(row[4]) ?? 0
let readyCount = Int(row[7]) ?? 0
let readyTotal = Int(row[8]) ?? 0
let toggleRow = clipboardToggleRows.first {
$0.count == 6 &&
row[2] == AnalyticsInsertionOutcome.dispatched.rawValue &&
$0[0] == row[0] &&
$0[1] == row[1]
}
var properties: [String: Any] = [
"latency_date": row[0],
"delivery_path": row[1],
"outcome": row[2],
"request_count": requestCount,
"request_total_ms": requestTotal,
"request_average_ms": Self.average(total: requestTotal, count: requestCount),
"request_min_ms": Int(row[5]) ?? 0,
"request_max_ms": Int(row[6]) ?? 0,
"ready_count": readyCount,
]
if readyCount > 0 {
properties["ready_total_ms"] = readyTotal
properties["ready_average_ms"] = Self.average(total: readyTotal, count: readyCount)
properties["ready_min_ms"] = Int(row[9]) ?? 0
properties["ready_max_ms"] = Int(row[10]) ?? 0
}
if let toggleRow {
let count = Int(toggleRow[2]) ?? 0
let total = Int(toggleRow[3]) ?? 0
properties["toggle_stop_to_dispatch_count"] = count
properties["toggle_stop_to_dispatch_total_ms"] = total
properties["toggle_stop_to_dispatch_average_ms"] = Self.average(total: total, count: count)
properties["toggle_stop_to_dispatch_min_ms"] = Int(toggleRow[4]) ?? 0
properties["toggle_stop_to_dispatch_max_ms"] = Int(toggleRow[5]) ?? 0
}
try self.enqueue(.insertionLatencyDailySummary, at: date, properties: properties)
}
try self.run("DELETE FROM daily_usage WHERE day < ?", bindings: [.text(today)])
try self.run("DELETE FROM daily_model_usage WHERE day < ?", bindings: [.text(today)])
try self.run("DELETE FROM daily_insertion_latency WHERE day < ?", bindings: [.text(today)])
try self.run("DELETE FROM daily_clipboard_toggle_latency WHERE day < ?", bindings: [.text(today)])
try self.run("DELETE FROM daily_dictation_performance WHERE day < ?", bindings: [.text(today)])
}
}
/// Returns detailed events immediately and activity events only after their local week has ended.
func readyOutbox(limit: Int, at date: Date) throws -> [AnalyticsOutboxItem] {
let currentWeekStart = self.calendar.dateInterval(of: .weekOfYear, for: date)?.start ?? date
var statement: OpaquePointer?
try self.prepare(
"SELECT event_id, payload FROM outbox " +
"WHERE next_retry_at <= ? " +
"AND (event_name != ? OR created_at < ?) " +
"ORDER BY created_at LIMIT ?",
into: &statement
)
defer { sqlite3_finalize(statement) }
try self.bind(
[
.double(date.timeIntervalSince1970),
.text(AnalyticsEvent.activeUser.rawValue),
.double(currentWeekStart.timeIntervalSince1970),
.integer(limit),
],
to: statement
)
var items: [AnalyticsOutboxItem] = []
while sqlite3_step(statement) == SQLITE_ROW {
guard let idText = sqlite3_column_text(statement, 0) else { continue }
let id = String(cString: idText)
let byteCount = Int(sqlite3_column_bytes(statement, 1))
guard let bytes = sqlite3_column_blob(statement, 1), byteCount > 0 else { continue }
let payload = Data(bytes: bytes, count: byteCount)
items.append(AnalyticsOutboxItem(id: id, payload: payload))
}
return items
}
/// Deletes uploaded events and reclaims their SQLite pages.
func acknowledgeUploaded(ids: [String], at date: Date) throws {
guard !ids.isEmpty else { return }
try self.transaction {
for id in ids {
try self.run("DELETE FROM outbox WHERE event_id = ?", bindings: [.text(id)])
}
let retentionCutoff = date.addingTimeInterval(-45 * 24 * 60 * 60).timeIntervalSince1970
try self.run("DELETE FROM event_dedupe WHERE created_at < ?", bindings: [.double(retentionCutoff)])
}
try self.purgeDeletedPages()
}
func retry(ids: [String], at date: Date) throws {
for id in ids {
let attempt = try self.integer(
"SELECT attempt_count FROM outbox WHERE event_id = ?",
bindings: [.text(id)]
) ?? 0
let nextAttempt = attempt + 1
let delay = min(pow(2, Double(min(nextAttempt, 10))) * 5, 60 * 60)
try self.run(
"UPDATE outbox SET attempt_count = ?, next_retry_at = ? WHERE event_id = ?",
bindings: [.integer(nextAttempt), .double(date.timeIntervalSince1970 + delay), .text(id)]
)
}
}
func discardPermanently(ids: [String]) throws {
try self.transaction {
for id in ids {
try self.run("DELETE FROM outbox WHERE event_id = ?", bindings: [.text(id)])
}
}
try self.purgeDeletedPages()
}
func purgeAll() throws {
try self.transaction {
try self.execute("DELETE FROM outbox")
try self.execute("DELETE FROM daily_usage")
try self.execute("DELETE FROM daily_model_usage")
try self.execute("DELETE FROM daily_dictation_performance")
try self.execute("DELETE FROM daily_insertion_latency")
try self.execute("DELETE FROM daily_clipboard_toggle_latency")
try self.execute("DELETE FROM event_dedupe")
try self.execute("DELETE FROM onboarding_flows")
try self.execute("DELETE FROM onboarding_tryout_state")
try self.execute("DELETE FROM model_download_attempts")
}
try self.purgeDeletedPages()
}
/// Removes detailed analytics while preserving the daily active-user signal and its dedupe key.
func purgeDetailedAnalytics() throws {
try self.transaction {
try self.run(
"DELETE FROM outbox WHERE event_name != ? AND event_name != ?",
bindings: [
.text(AnalyticsEvent.activeUser.rawValue),
.text(AnalyticsEvent.dictationPerformanceDailySummary.rawValue),
]
)
try self.execute("DELETE FROM daily_usage")
try self.execute("DELETE FROM daily_model_usage")
try self.execute("DELETE FROM daily_insertion_latency")
try self.execute("DELETE FROM daily_clipboard_toggle_latency")
try self.run(
"DELETE FROM event_dedupe WHERE dedupe_key NOT LIKE ?",
bindings: [.text("activity:%")]
)
try self.execute("DELETE FROM onboarding_flows")
try self.execute("DELETE FROM model_download_attempts")
}
try self.purgeDeletedPages()
}
private func createSchema() throws {
try self.execute("""
CREATE TABLE IF NOT EXISTS outbox (
event_id TEXT PRIMARY KEY,
event_name TEXT NOT NULL,
payload BLOB NOT NULL,
attempt_count INTEGER NOT NULL DEFAULT 0,
next_retry_at REAL NOT NULL DEFAULT 0,
created_at REAL NOT NULL
);
CREATE INDEX IF NOT EXISTS outbox_ready ON outbox(next_retry_at, created_at);
CREATE TABLE IF NOT EXISTS daily_usage (
day TEXT PRIMARY KEY,
dictation_count INTEGER NOT NULL DEFAULT 0,
command_count INTEGER NOT NULL DEFAULT 0,
edit_count INTEGER NOT NULL DEFAULT 0,
meeting_count INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS daily_model_usage (
day TEXT NOT NULL,
role TEXT NOT NULL,
mode TEXT NOT NULL,
provider TEXT NOT NULL,
model TEXT NOT NULL,
use_count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(day, role, mode, provider, model)
);
CREATE TABLE IF NOT EXISTS daily_dictation_performance (
day TEXT NOT NULL,
measured_app_version TEXT NOT NULL,
measured_os_version TEXT NOT NULL,
metric TEXT NOT NULL,
bucket_index INTEGER NOT NULL,
sample_count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(day, measured_app_version, measured_os_version, metric, bucket_index)
);
CREATE TABLE IF NOT EXISTS daily_insertion_latency (
day TEXT NOT NULL,
delivery_path TEXT NOT NULL,
outcome TEXT NOT NULL,
request_count INTEGER NOT NULL DEFAULT 0,
request_total_ms INTEGER NOT NULL DEFAULT 0,
request_min_ms INTEGER NOT NULL DEFAULT 0,
request_max_ms INTEGER NOT NULL DEFAULT 0,
ready_count INTEGER NOT NULL DEFAULT 0,
ready_total_ms INTEGER NOT NULL DEFAULT 0,
ready_min_ms INTEGER NOT NULL DEFAULT 0,
ready_max_ms INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(day, delivery_path, outcome)
);
CREATE TABLE IF NOT EXISTS daily_clipboard_toggle_latency (
day TEXT NOT NULL,
delivery_path TEXT NOT NULL,
sample_count INTEGER NOT NULL DEFAULT 0,
total_ms INTEGER NOT NULL DEFAULT 0,
min_ms INTEGER NOT NULL DEFAULT 0,
max_ms INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(day, delivery_path)
);
CREATE TABLE IF NOT EXISTS event_dedupe (
dedupe_key TEXT PRIMARY KEY,
created_at REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS onboarding_flows (
flow_id TEXT PRIMARY KEY,
origin TEXT NOT NULL,
created_at REAL NOT NULL,
completed INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS onboarding_tryout_state (
flow_id TEXT PRIMARY KEY,
entered_at REAL NOT NULL,
attempt_count INTEGER NOT NULL DEFAULT 0,
last_start_method TEXT,
last_outcome TEXT,
last_failure_stage TEXT
);
CREATE TABLE IF NOT EXISTS model_download_attempts (
download_id TEXT PRIMARY KEY,
provider TEXT NOT NULL,
model TEXT NOT NULL,
source TEXT NOT NULL,
started_at REAL NOT NULL
);
""")
}
private func enqueue(_ event: AnalyticsEvent, at date: Date, properties: [String: Any]) throws {
let eventID = UUID().uuidString.lowercased()
var approvedProperties = properties
approvedProperties["distinct_id"] = self.distinctID
approvedProperties["platform"] = "macos"
approvedProperties["$os"] = "macOS"
approvedProperties["app_version"] = self.appVersion
approvedProperties["ram_gb"] = self.systemConfiguration.ramGB
approvedProperties["chip"] = self.systemConfiguration.chip
approvedProperties["$set"] = [
"ram_gb": self.systemConfiguration.ramGB,
"chip": self.systemConfiguration.chip,
]
approvedProperties["schema_version"] = 2
let payload: [String: Any] = [
"uuid": eventID,
"event": event.rawValue,
"timestamp": self.iso8601.string(from: date),
"properties": approvedProperties,
]
guard JSONSerialization.isValidJSONObject(payload) else {
throw AnalyticsDatabaseError.invalidPayload
}
let data = try JSONSerialization.data(withJSONObject: payload)
try self.run(
"INSERT INTO outbox (event_id, event_name, payload, created_at) VALUES (?, ?, ?, ?)",
bindings: [.text(eventID), .text(event.rawValue), .blob(data), .double(date.timeIntervalSince1970)]
)
}
private func enqueueActivityIfNeeded(_ kind: AnalyticsActivityKind, at date: Date) throws {
let day = self.dayString(date)
guard try self.insertDedupeKey("activity:\(day)", date: date) else { return }
try self.enqueue(.activeUser, at: date, properties: [
"activity_kind": kind.rawValue,
"activity_date": day,
])
}
private func upsertDailyUsage(day: String, mode: AnalyticsUsageMode) throws {
let column: String
switch mode {
case .dictation: column = "dictation_count"
case .command: column = "command_count"
case .edit: column = "edit_count"
case .meeting: column = "meeting_count"
}
try self.run(
"INSERT INTO daily_usage (day, \(column)) VALUES (?, 1) " +
"ON CONFLICT(day) DO UPDATE SET \(column) = \(column) + 1",
bindings: [.text(day)]
)
}
private func upsertDailyModelUsage(
day: String,
role: AnalyticsModelRole,
mode: AnalyticsUsageMode,
descriptor: AnalyticsModelDescriptor
) throws {
try self.run(
"INSERT INTO daily_model_usage (day, role, mode, provider, model, use_count) " +
"VALUES (?, ?, ?, ?, ?, 1) ON CONFLICT(day, role, mode, provider, model) " +
"DO UPDATE SET use_count = use_count + 1",
bindings: [
.text(day), .text(role.rawValue), .text(mode.rawValue),
.text(descriptor.provider), .text(descriptor.model),
]
)
}
private func upsertPerformanceMetric(
day: String,
measuredAppVersion: String,
metric: String,
milliseconds: Int
) throws {
let boundedMilliseconds = min(max(milliseconds, 0), 600_000)
let bucketIndex = Self.performanceBucketUpperBounds.firstIndex { boundedMilliseconds <= $0 }
?? Self.performanceBucketUpperBounds.count
try self.run(
"INSERT INTO daily_dictation_performance " +
"(day, measured_app_version, measured_os_version, metric, bucket_index, sample_count) " +
"VALUES (?, ?, ?, ?, ?, 1) " +
"ON CONFLICT(day, measured_app_version, measured_os_version, metric, bucket_index) " +
"DO UPDATE SET sample_count = sample_count + 1",
bindings: [
.text(day), .text(measuredAppVersion), .text(self.systemConfiguration.osVersion),
.text(metric), .integer(bucketIndex),
]
)
}
private struct PerformanceSummaryKey: Hashable {
let day: String
let measuredAppVersion: String
let measuredOSVersion: String
}
private struct PerformanceMetricSummary {
var bucketCounts = Array(repeating: 0, count: performanceBucketUpperBounds.count + 1)
var sampleCount = 0
}
private struct PerformanceSummary {
let key: PerformanceSummaryKey
var asr = PerformanceMetricSummary()
var fluidIntelligence = PerformanceMetricSummary()
var picoTPS = PerformanceMetricSummary()
var miniTPS = PerformanceMetricSummary()
var properties: [String: Any] {
var properties: [String: Any] = [
"performance_date": self.key.day,
"measured_app_version": self.key.measuredAppVersion,
"measured_os_version": self.key.measuredOSVersion,
"histogram_schema_version": 2,
]
Self.add(self.asr, prefix: "asr", to: &properties)
Self.add(self.fluidIntelligence, prefix: "fluid_intelligence", to: &properties)
Self.add(self.picoTPS, prefix: "pico_tps", to: &properties)
Self.add(self.miniTPS, prefix: "mini_tps", to: &properties)
return properties
}
private static func add(
_ metric: PerformanceMetricSummary,
prefix: String,
to properties: inout [String: Any]
) {
properties["\(prefix)_sample_count"] = metric.sampleCount
guard metric.sampleCount > 0 else { return }
properties["\(prefix)_p50_bucket"] = self.quantileBucket(metric.bucketCounts, percentile: 0.50)
properties["\(prefix)_p95_bucket"] = self.quantileBucket(metric.bucketCounts, percentile: 0.95)
}
private static func quantileBucket(_ counts: [Int], percentile: Double) -> String {
let target = max(1, Int(ceil(Double(counts.reduce(0, +)) * percentile)))
var cumulative = 0
for (index, count) in counts.enumerated() {
cumulative += count
if cumulative >= target {
guard index < performanceBucketUpperBounds.count else { return "60000_plus" }
return String(performanceBucketUpperBounds[index])
}
}
return "unknown"
}
}
private func performanceSummaries(from rows: [[String]]) -> [PerformanceSummary] {
var summaries: [PerformanceSummaryKey: PerformanceSummary] = [:]
for row in rows where row.count == 6 {
let key = PerformanceSummaryKey(
day: row[0],
measuredAppVersion: row[1],
measuredOSVersion: row[2]
)
var summary = summaries[key] ?? PerformanceSummary(key: key)
let bucketIndex = Int(row[4]) ?? -1
let sampleCount = Int(row[5]) ?? 0
if row[3] == "asr" {
Self.mergePerformanceRow(
into: &summary.asr,
bucketIndex: bucketIndex,
sampleCount: sampleCount
)
} else if row[3] == "fluid_intelligence" {
Self.mergePerformanceRow(
into: &summary.fluidIntelligence,
bucketIndex: bucketIndex,
sampleCount: sampleCount
)
} else if row[3] == "pico_tps" {
Self.mergePerformanceRow(
into: &summary.picoTPS,
bucketIndex: bucketIndex,
sampleCount: sampleCount
)
} else if row[3] == "mini_tps" {
Self.mergePerformanceRow(
into: &summary.miniTPS,
bucketIndex: bucketIndex,
sampleCount: sampleCount
)
}
summaries[key] = summary
}
return summaries.values.sorted {
($0.key.day, $0.key.measuredAppVersion, $0.key.measuredOSVersion) <
($1.key.day, $1.key.measuredAppVersion, $1.key.measuredOSVersion)
}
}
private static func mergePerformanceRow(
into metric: inout PerformanceMetricSummary,
bucketIndex: Int,
sampleCount: Int
) {
guard metric.bucketCounts.indices.contains(bucketIndex) else { return }
metric.bucketCounts[bucketIndex] += sampleCount
metric.sampleCount += sampleCount
}
private func activeOnboardingFlow(origin: AnalyticsOnboardingOrigin, at date: Date) throws -> String {
if let existing = try self.string(
"SELECT flow_id FROM onboarding_flows WHERE completed = 0 AND origin = ? " +
"ORDER BY created_at DESC LIMIT 1",
bindings: [.text(origin.rawValue)]
) {
return existing
}
let flowID = UUID().uuidString.lowercased()
try self.run(
"INSERT INTO onboarding_flows (flow_id, origin, created_at) VALUES (?, ?, ?)",
bindings: [.text(flowID), .text(origin.rawValue), .double(date.timeIntervalSince1970)]
)
return flowID
}
private struct OnboardingTryoutState {
let enteredAt: TimeInterval
let attemptCount: Int
let lastStartMethod: String?
let lastFailureStage: String?
}
private func ensureOnboardingTryoutState(flowID: String, enteredAt: Date) throws {
try self.run(
"INSERT OR IGNORE INTO onboarding_tryout_state (flow_id, entered_at) VALUES (?, ?)",
bindings: [.text(flowID), .double(enteredAt.timeIntervalSince1970)]
)
}
private func onboardingTryoutState(flowID: String) throws -> OnboardingTryoutState {
let rows = try self.query(
"SELECT entered_at, attempt_count, COALESCE(last_start_method, ''), " +
"COALESCE(last_failure_stage, '') " +
"FROM onboarding_tryout_state WHERE flow_id = ?",
bindings: [.text(flowID)]
)
guard let row = rows.first, row.count == 4 else {
throw AnalyticsDatabaseError.sqlite("Missing onboarding tryout state")
}
return OnboardingTryoutState(
enteredAt: TimeInterval(row[0]) ?? 0,
attemptCount: Int(row[1]) ?? 0,
lastStartMethod: row[2].isEmpty ? nil : row[2],
lastFailureStage: row[3].isEmpty ? nil : row[3]
)
}
private static func onboardingTryoutAttemptCountBucket(_ count: Int) -> String {
switch count {
case 1: "1"
case 2: "2"
default: "3+"
}
}
private static func onboardingTryoutDurationBucket(_ duration: TimeInterval) -> String {
let duration = max(0, duration)
guard duration < 30 else { return "30s_plus" }
let upperBound = max(0.5, ceil(duration * 2) / 2)
if upperBound == 0.5 { return "500ms" }
let wholeSeconds = Int(upperBound)
if upperBound == Double(wholeSeconds) { return "\(wholeSeconds)s" }
return "\(wholeSeconds)_5s"
}
private func insertDedupeKey(_ key: String, date: Date) throws -> Bool {
try self.run(
"INSERT OR IGNORE INTO event_dedupe (dedupe_key, created_at) VALUES (?, ?)",
bindings: [.text(key), .double(date.timeIntervalSince1970)]
) > 0
}
private func dedupeKeyExists(_ key: String) throws -> Bool {
try self.integer(
"SELECT COUNT(*) FROM event_dedupe WHERE dedupe_key = ?",
bindings: [.text(key)]
) == 1
}
private func modelDownloadExists(id: String) throws -> Bool {
try self.integer(
"SELECT COUNT(*) FROM model_download_attempts WHERE download_id = ?",
bindings: [.text(id)]
) == 1
}
private func dayString(_ date: Date) -> String {
let components = self.calendar.dateComponents([.year, .month, .day], from: date)
return String(format: "%04d-%02d-%02d", components.year ?? 0, components.month ?? 0, components.day ?? 0)
}
private static func average(total: Int, count: Int) -> Double {
guard count > 0 else { return 0 }
return (Double(total) / Double(count) * 10).rounded() / 10
}
private func purgeDeletedPages() throws {
try self.execute("PRAGMA incremental_vacuum")
try self.execute("PRAGMA wal_checkpoint(TRUNCATE)")
}
private func transaction(_ body: () throws -> Void) throws {
try self.execute("BEGIN IMMEDIATE")
do {
try body()
try self.execute("COMMIT")
} catch {
try? self.execute("ROLLBACK")
throw error
}
}
@discardableResult
private func run(_ sql: String, bindings: [SQLiteBinding] = []) throws -> Int {
var statement: OpaquePointer?
try self.prepare(sql, into: &statement)
defer { sqlite3_finalize(statement) }
try self.bind(bindings, to: statement)
guard sqlite3_step(statement) == SQLITE_DONE else { throw self.lastError() }
return Int(sqlite3_changes(self.connection))
}
private func execute(_ sql: String) throws {
var errorMessage: UnsafeMutablePointer<CChar>?
guard sqlite3_exec(self.connection, sql, nil, nil, &errorMessage) == SQLITE_OK else {
let message = errorMessage.map { String(cString: $0) } ?? "unknown SQLite error"
sqlite3_free(errorMessage)
throw AnalyticsDatabaseError.sqlite(message)
}
}
private func query(_ sql: String, bindings: [SQLiteBinding]) throws -> [[String]] {
var statement: OpaquePointer?
try self.prepare(sql, into: &statement)
defer { sqlite3_finalize(statement) }
try self.bind(bindings, to: statement)
var rows: [[String]] = []
while sqlite3_step(statement) == SQLITE_ROW {
rows.append((0..<sqlite3_column_count(statement)).map { index in
sqlite3_column_text(statement, index).map { String(cString: $0) } ?? ""
})
}
return rows
}
private func string(_ sql: String, bindings: [SQLiteBinding]) throws -> String? {
try self.query(sql, bindings: bindings).first?.first
}
private func integer(_ sql: String, bindings: [SQLiteBinding]) throws -> Int? {
try self.string(sql, bindings: bindings).flatMap(Int.init)
}
private func prepare(_ sql: String, into statement: inout OpaquePointer?) throws {
guard sqlite3_prepare_v2(self.connection, sql, -1, &statement, nil) == SQLITE_OK else {
throw self.lastError()
}
}
private func bind(_ bindings: [SQLiteBinding], to statement: OpaquePointer?) throws {
let transient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
for (offset, binding) in bindings.enumerated() {
let index = Int32(offset + 1)
let result: Int32
switch binding {
case let .text(value):
result = sqlite3_bind_text(statement, index, value, -1, transient)
case let .integer(value):
result = sqlite3_bind_int64(statement, index, sqlite3_int64(value))
case let .double(value):
result = sqlite3_bind_double(statement, index, value)
case let .blob(data):
result = data.withUnsafeBytes { bytes in
sqlite3_bind_blob(statement, index, bytes.baseAddress, Int32(bytes.count), transient)
}
case .null:
result = sqlite3_bind_null(statement, index)
}
guard result == SQLITE_OK else { throw self.lastError() }
}
}
private func lastError() -> AnalyticsDatabaseError {
AnalyticsDatabaseError.sqlite(String(cString: sqlite3_errmsg(self.connection)))
}
}
private enum SQLiteBinding {
case text(String)
case integer(Int)
case double(Double)
case blob(Data)
case null
}