1 contributor
import Foundation
import HealthKit
import SQLite3
private enum SQLiteHealthArchiveStoreError: Error {
case openFailed(String)
case prepareFailed(String)
case stepFailed(String)
case incompatibleSchema(Int)
case exportEncodingFailed
}
// Interface updated 2026-05-18 — see AGENTS.md
actor SQLiteHealthArchiveStore: HealthArchiveStore {
static let shared = SQLiteHealthArchiveStore()
nonisolated private static let archiveSchemaVersion = 2
nonisolated private static let requiredArchiveV2Tables: [String] = [
"schema_migrations",
"archive_metadata",
"device_chains",
"observations",
"sample_types",
"observation_type_runs",
"sources",
"source_revisions",
"hk_devices",
"metadata_blobs",
"samples",
"sample_versions",
"sample_observation_events",
"sample_visibility_ranges",
"sample_relationships",
"observation_type_summaries",
"daily_type_aggregates",
"export_manifests",
"export_items"
]
private let databaseURL: URL
private var didPrepareSchema = false
init(databaseURL: URL? = nil) {
let supportURL = URL.applicationSupportDirectory
self.databaseURL = databaseURL ?? supportURL.appending(path: "HealthProbeArchive.sqlite")
}
func upsertSamples(_ samples: [HKSample], observedAt: Date) async throws -> HealthArchiveWriteSummary {
guard !samples.isEmpty else {
return HealthArchiveWriteSummary(insertedCount: 0, updatedCount: 0, unchangedCount: 0)
}
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
try execute("BEGIN IMMEDIATE TRANSACTION", db: db)
do {
let summary = try upsertSamples(samples, observedAt: observedAt, db: db)
try execute("PRAGMA foreign_key_check", db: db)
try execute("COMMIT", db: db)
return summary
} catch {
try? execute("ROLLBACK", db: db)
throw error
}
}
func markVerification(sampleType: HKSampleType, verifiedAt: Date) async throws {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
let sql = """
UPDATE archive_samples
SET last_verified_at = ?, last_seen_at = COALESCE(last_seen_at, ?)
WHERE type_identifier = ? AND disappeared_at IS NULL
"""
try withStatement(sql, db: db) { statement in
sqlite3_bind_double(statement, 1, verifiedAt.timeIntervalSinceReferenceDate)
sqlite3_bind_double(statement, 2, verifiedAt.timeIntervalSinceReferenceDate)
bindText(sampleType.identifier, to: 3, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
func recordDisappearance(sampleUUIDHash: String, sampleTypeIdentifier: String, observedMissingAt: Date) async throws {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
try execute("BEGIN IMMEDIATE TRANSACTION", db: db)
do {
let observationID = try createObservation(
observedAt: observedMissingAt,
triggerReason: "deleted_object",
status: "completed",
db: db
)
if let sampleTypeID = try sampleTypeID(typeIdentifier: sampleTypeIdentifier, db: db),
let sampleID = try sampleID(sampleUUIDHash: sampleUUIDHash, sampleTypeID: sampleTypeID, db: db) {
try insertObservationEvent(
observationID: observationID,
sampleID: sampleID,
versionID: nil,
eventKind: "disappeared",
evidenceKind: "deleted_object",
observedAt: observedMissingAt,
db: db
)
try closeOpenVisibilityRanges(
sampleID: sampleID,
excludingVersionID: nil,
closedAtObservationID: observationID,
observedAt: observedMissingAt,
db: db
)
try rebuildTypeSummary(observationID: observationID, sampleTypeID: sampleTypeID, db: db)
try rebuildDailyAggregates(
observationID: observationID,
sampleTypeID: sampleTypeID,
observedAt: observedMissingAt,
db: db
)
}
let sql = """
UPDATE archive_samples
SET disappeared_at = ?, last_verified_at = ?
WHERE sample_uuid_hash = ? AND type_identifier = ?
"""
try withStatement(sql, db: db) { statement in
sqlite3_bind_double(statement, 1, observedMissingAt.timeIntervalSinceReferenceDate)
sqlite3_bind_double(statement, 2, observedMissingAt.timeIntervalSinceReferenceDate)
bindText(sampleUUIDHash, to: 3, in: statement)
bindText(sampleTypeIdentifier, to: 4, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
try execute("COMMIT", db: db)
} catch {
try? execute("ROLLBACK", db: db)
throw error
}
}
func records(for request: HealthArchiveRecordRequest) async throws -> [ArchivedHealthRecord] {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
var clauses: [String] = []
if request.sampleTypeIdentifier != nil {
clauses.append("t.type_identifier = ?")
}
if !request.fingerprints.isEmpty {
clauses.append("s.strict_fingerprint IN (\(Array(repeating: "?", count: request.fingerprints.count).joined(separator: ",")))")
}
if request.disappearedOnly {
clauses.append("rr.last_observation_id IS NOT NULL AND es.disappeared_at IS NOT NULL")
}
if request.firstSeenAfter != nil {
clauses.append("s.first_seen_at >= ?")
}
if request.firstSeenBefore != nil {
clauses.append("s.first_seen_at <= ?")
}
if request.afterCursor != nil {
clauses.append("(v.start_date > ? OR (v.start_date = ? AND s.strict_fingerprint > ?))")
}
let whereClause = clauses.isEmpty ? "" : "WHERE \(clauses.joined(separator: " AND "))"
let limitClause = request.limit.map { "LIMIT \(max($0, 0))" } ?? ""
let sql = """
WITH selected_ranges AS (
SELECT
r.sample_id, r.version_id, r.first_observation_id, r.last_observation_id,
r.first_seen_at, r.last_seen_at,
ROW_NUMBER() OVER (
PARTITION BY r.sample_id
ORDER BY
CASE
WHEN ? IS NOT NULL THEN 0
WHEN r.last_observation_id IS NULL THEN 0
ELSE 1
END,
COALESCE(r.last_observation_id, 9223372036854775807) DESC,
r.first_observation_id DESC
) AS record_rank
FROM sample_visibility_ranges r
WHERE (? IS NULL OR (
r.first_observation_id <= ?
AND (r.last_observation_id IS NULL OR r.last_observation_id > ?)
))
),
record_ranges AS (
SELECT *
FROM selected_ranges
WHERE record_rank = 1
),
event_summary AS (
SELECT
sample_id,
MAX(CASE WHEN event_kind != 'disappeared' THEN observed_at END) AS last_seen_at,
MAX(observed_at) AS last_verified_at,
MAX(CASE WHEN event_kind = 'disappeared' THEN observed_at END) AS disappeared_at
FROM sample_observation_events
GROUP BY sample_id
)
SELECT
COALESCE(s.sample_uuid_hash, s.strict_fingerprint) AS record_id,
t.type_identifier, s.strict_fingerprint, s.semantic_fingerprint, s.sample_uuid_hash,
v.start_date, v.end_date, s.first_seen_at,
COALESCE(es.last_seen_at, rr.first_seen_at) AS last_seen_at,
es.last_verified_at,
CASE WHEN rr.last_observation_id IS NULL THEN NULL ELSE es.disappeared_at END AS disappeared_at,
v.value_kind, v.numeric_value, v.unit, v.category_value, v.workout_activity_type, v.duration_seconds,
src.bundle_identifier
FROM record_ranges rr
JOIN samples s ON s.id = rr.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
JOIN sample_versions v ON v.id = rr.version_id
LEFT JOIN source_revisions sr ON sr.id = v.source_revision_id
LEFT JOIN sources src ON src.id = sr.source_id
LEFT JOIN event_summary es ON es.sample_id = s.id
\(whereClause)
ORDER BY v.start_date ASC, s.strict_fingerprint ASC
\(limitClause)
"""
return try withStatement(sql, db: db) { statement in
var index: Int32 = 1
bindInt64(request.visibleAtObservationID, to: index, in: statement)
index += 1
bindInt64(request.visibleAtObservationID, to: index, in: statement)
index += 1
bindInt64(request.visibleAtObservationID, to: index, in: statement)
index += 1
bindInt64(request.visibleAtObservationID, to: index, in: statement)
index += 1
if let typeIdentifier = request.sampleTypeIdentifier {
bindText(typeIdentifier, to: index, in: statement)
index += 1
}
for fingerprint in request.fingerprints.sorted() {
bindText(fingerprint, to: index, in: statement)
index += 1
}
if let firstSeenAfter = request.firstSeenAfter {
sqlite3_bind_double(statement, index, firstSeenAfter.timeIntervalSince1970)
index += 1
}
if let firstSeenBefore = request.firstSeenBefore {
sqlite3_bind_double(statement, index, firstSeenBefore.timeIntervalSince1970)
index += 1
}
if let cursor = request.afterCursor {
sqlite3_bind_double(statement, index, cursor.startDate.timeIntervalSince1970)
index += 1
sqlite3_bind_double(statement, index, cursor.startDate.timeIntervalSince1970)
index += 1
bindText(cursor.strictFingerprint, to: index, in: statement)
index += 1
}
var records: [ArchivedHealthRecord] = []
while sqlite3_step(statement) == SQLITE_ROW {
records.append(archiveRecord(from: statement))
}
return records
}
}
func diffSummary(_ request: HealthArchiveDiffRequest) async throws -> HealthArchiveDiffSummary {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
let typeClause = request.sampleTypeIdentifier == nil ? "" : "AND t.type_identifier = ?"
let sql = """
WITH from_visible AS (
SELECT sample_id, version_id
FROM sample_visibility_ranges
WHERE first_observation_id <= ?
AND (last_observation_id IS NULL OR last_observation_id > ?)
),
to_visible AS (
SELECT sample_id, version_id
FROM sample_visibility_ranges
WHERE first_observation_id <= ?
AND (last_observation_id IS NULL OR last_observation_id > ?)
)
SELECT
(
SELECT COUNT(*)
FROM to_visible tv
LEFT JOIN from_visible fv ON fv.sample_id = tv.sample_id
JOIN samples s ON s.id = tv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
WHERE fv.sample_id IS NULL \(typeClause)
) AS appeared_count,
(
SELECT COUNT(*)
FROM from_visible fv
LEFT JOIN to_visible tv ON tv.sample_id = fv.sample_id
JOIN samples s ON s.id = fv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
WHERE tv.sample_id IS NULL \(typeClause)
) AS disappeared_count,
(
SELECT COUNT(*)
FROM to_visible tv
JOIN from_visible fv ON fv.sample_id = tv.sample_id
JOIN samples s ON s.id = tv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
WHERE tv.version_id != fv.version_id \(typeClause)
) AS representation_changed_count
"""
return try withStatement(sql, db: db) { statement in
var index: Int32 = 1
bindDiffObservationIDs(request.fromObservationID, request.toObservationID, to: statement, startingAt: &index)
for _ in 0..<3 {
if let sampleTypeIdentifier = request.sampleTypeIdentifier {
bindText(sampleTypeIdentifier, to: index, in: statement)
index += 1
}
}
guard sqlite3_step(statement) == SQLITE_ROW else {
return HealthArchiveDiffSummary(
fromObservationID: request.fromObservationID,
toObservationID: request.toObservationID,
sampleTypeIdentifier: request.sampleTypeIdentifier,
appearedCount: 0,
disappearedCount: 0,
representationChangedCount: 0
)
}
return HealthArchiveDiffSummary(
fromObservationID: request.fromObservationID,
toObservationID: request.toObservationID,
sampleTypeIdentifier: request.sampleTypeIdentifier,
appearedCount: columnInt(statement, 0) ?? 0,
disappearedCount: columnInt(statement, 1) ?? 0,
representationChangedCount: columnInt(statement, 2) ?? 0
)
}
}
func diffRecords(_ request: HealthArchiveDiffRecordRequest) async throws -> [ArchivedHealthRecord] {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
let selectedRangeSQL: String
switch request.kind {
case .appeared:
selectedRangeSQL = """
SELECT tv.sample_id, tv.version_id
FROM to_visible tv
LEFT JOIN from_visible fv ON fv.sample_id = tv.sample_id
WHERE fv.sample_id IS NULL
"""
case .disappeared:
selectedRangeSQL = """
SELECT fv.sample_id, fv.version_id
FROM from_visible fv
LEFT JOIN to_visible tv ON tv.sample_id = fv.sample_id
WHERE tv.sample_id IS NULL
"""
case .representationChanged:
selectedRangeSQL = """
SELECT tv.sample_id, tv.version_id
FROM to_visible tv
JOIN from_visible fv ON fv.sample_id = tv.sample_id
WHERE tv.version_id != fv.version_id
"""
}
var clauses: [String] = []
if request.sampleTypeIdentifier != nil {
clauses.append("t.type_identifier = ?")
}
if request.afterCursor != nil {
clauses.append("(v.start_date > ? OR (v.start_date = ? AND s.strict_fingerprint > ?))")
}
let whereClause = clauses.isEmpty ? "" : "WHERE \(clauses.joined(separator: " AND "))"
let limitClause = request.limit.map { "LIMIT \(max($0, 0))" } ?? ""
let sql = """
WITH from_visible AS (
SELECT sample_id, version_id
FROM sample_visibility_ranges
WHERE first_observation_id <= ?
AND (last_observation_id IS NULL OR last_observation_id > ?)
),
to_visible AS (
SELECT sample_id, version_id
FROM sample_visibility_ranges
WHERE first_observation_id <= ?
AND (last_observation_id IS NULL OR last_observation_id > ?)
),
selected_ranges AS (
\(selectedRangeSQL)
),
event_summary AS (
SELECT
sample_id,
MAX(CASE WHEN event_kind != 'disappeared' THEN observed_at END) AS last_seen_at,
MAX(observed_at) AS last_verified_at,
MAX(CASE WHEN event_kind = 'disappeared' THEN observed_at END) AS disappeared_at
FROM sample_observation_events
WHERE observation_id <= ?
GROUP BY sample_id
)
SELECT
COALESCE(s.sample_uuid_hash, s.strict_fingerprint) AS record_id,
t.type_identifier, s.strict_fingerprint, s.semantic_fingerprint, s.sample_uuid_hash,
v.start_date, v.end_date, s.first_seen_at,
COALESCE(es.last_seen_at, s.first_seen_at) AS last_seen_at,
es.last_verified_at,
es.disappeared_at,
v.value_kind, v.numeric_value, v.unit, v.category_value, v.workout_activity_type, v.duration_seconds,
src.bundle_identifier
FROM selected_ranges srng
JOIN samples s ON s.id = srng.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
JOIN sample_versions v ON v.id = srng.version_id
LEFT JOIN source_revisions sr ON sr.id = v.source_revision_id
LEFT JOIN sources src ON src.id = sr.source_id
LEFT JOIN event_summary es ON es.sample_id = s.id
\(whereClause)
ORDER BY v.start_date ASC, s.strict_fingerprint ASC
\(limitClause)
"""
return try withStatement(sql, db: db) { statement in
var index: Int32 = 1
bindDiffObservationIDs(request.fromObservationID, request.toObservationID, to: statement, startingAt: &index)
bindInt64(request.toObservationID, to: index, in: statement)
index += 1
if let sampleTypeIdentifier = request.sampleTypeIdentifier {
bindText(sampleTypeIdentifier, to: index, in: statement)
index += 1
}
if let cursor = request.afterCursor {
sqlite3_bind_double(statement, index, cursor.startDate.timeIntervalSince1970)
index += 1
sqlite3_bind_double(statement, index, cursor.startDate.timeIntervalSince1970)
index += 1
bindText(cursor.strictFingerprint, to: index, in: statement)
index += 1
}
var records: [ArchivedHealthRecord] = []
while sqlite3_step(statement) == SQLITE_ROW {
records.append(archiveRecord(from: statement))
}
return records
}
}
func aggregateComparison(_ request: HealthArchiveAggregateComparisonRequest) async throws -> [HealthArchiveAggregateComparisonRow] {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
var clauses: [String] = []
if request.sampleTypeIdentifier != nil {
clauses.append("t.type_identifier = ?")
}
if request.afterBucketStart != nil {
clauses.append("k.bucket_start > ?")
}
let whereClause = clauses.isEmpty ? "" : "WHERE \(clauses.joined(separator: " AND "))"
let limitClause = request.limit.map { "LIMIT \(max($0, 0))" } ?? ""
let sql = """
WITH from_aggregates AS (
SELECT
sample_type_id,
bucket_start,
bucket_end,
SUM(visible_record_count) AS visible_record_count,
SUM(value_sum) AS value_sum
FROM daily_type_aggregates
WHERE observation_id = ?
GROUP BY sample_type_id, bucket_start, bucket_end
),
to_aggregates AS (
SELECT
sample_type_id,
bucket_start,
bucket_end,
SUM(visible_record_count) AS visible_record_count,
SUM(value_sum) AS value_sum
FROM daily_type_aggregates
WHERE observation_id = ?
GROUP BY sample_type_id, bucket_start, bucket_end
),
aggregate_keys AS (
SELECT sample_type_id, bucket_start, bucket_end FROM from_aggregates
UNION
SELECT sample_type_id, bucket_start, bucket_end FROM to_aggregates
)
SELECT
t.type_identifier,
k.bucket_start,
k.bucket_end,
COALESCE(f.visible_record_count, 0) AS from_visible_record_count,
COALESCE(ta.visible_record_count, 0) AS to_visible_record_count,
f.value_sum,
ta.value_sum
FROM aggregate_keys k
JOIN sample_types t ON t.id = k.sample_type_id
LEFT JOIN from_aggregates f
ON f.sample_type_id = k.sample_type_id
AND f.bucket_start = k.bucket_start
AND f.bucket_end = k.bucket_end
LEFT JOIN to_aggregates ta
ON ta.sample_type_id = k.sample_type_id
AND ta.bucket_start = k.bucket_start
AND ta.bucket_end = k.bucket_end
\(whereClause)
ORDER BY k.bucket_start ASC, t.type_identifier ASC
\(limitClause)
"""
return try withStatement(sql, db: db) { statement in
var index: Int32 = 1
bindInt64(request.fromObservationID, to: index, in: statement)
index += 1
bindInt64(request.toObservationID, to: index, in: statement)
index += 1
if let sampleTypeIdentifier = request.sampleTypeIdentifier {
bindText(sampleTypeIdentifier, to: index, in: statement)
index += 1
}
if let afterBucketStart = request.afterBucketStart {
sqlite3_bind_double(statement, index, afterBucketStart.timeIntervalSince1970)
index += 1
}
var rows: [HealthArchiveAggregateComparisonRow] = []
while sqlite3_step(statement) == SQLITE_ROW {
guard let bucketStart = columnUnixDate(statement, 1),
let bucketEnd = columnUnixDate(statement, 2) else {
continue
}
rows.append(HealthArchiveAggregateComparisonRow(
sampleTypeIdentifier: columnText(statement, 0) ?? "",
bucketStart: bucketStart,
bucketEnd: bucketEnd,
fromVisibleRecordCount: columnInt(statement, 3) ?? 0,
toVisibleRecordCount: columnInt(statement, 4) ?? 0,
fromValueSum: columnDouble(statement, 5),
toValueSum: columnDouble(statement, 6)
))
}
return rows
}
}
func sourceProvenanceBreakdown(_ request: HealthArchiveSourceProvenanceRequest) async throws -> [HealthArchiveSourceProvenanceRow] {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
var clauses: [String] = []
if request.sampleTypeIdentifier != nil {
clauses.append("t.type_identifier = ?")
}
let whereClause = clauses.isEmpty ? "" : "WHERE \(clauses.joined(separator: " AND "))"
let limitClause = request.limit.map { "LIMIT \(max($0, 0))" } ?? ""
let sql = """
WITH visible_ranges AS (
SELECT sample_id, version_id
FROM sample_visibility_ranges
WHERE (? IS NULL OR (
first_observation_id <= ?
AND (last_observation_id IS NULL OR last_observation_id > ?)
))
)
SELECT
t.type_identifier,
src.source_name_hash,
src.bundle_identifier,
sr.product_type,
sr.version,
sr.operating_system_version,
d.model,
COUNT(*) AS visible_record_count,
SUM(v.numeric_value) AS value_sum,
MIN(v.start_date) AS earliest_start_date,
MAX(v.end_date) AS latest_end_date
FROM visible_ranges vr
JOIN sample_versions v ON v.id = vr.version_id
JOIN samples s ON s.id = vr.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
LEFT JOIN source_revisions sr ON sr.id = v.source_revision_id
LEFT JOIN sources src ON src.id = sr.source_id
LEFT JOIN hk_devices d ON d.id = v.hk_device_id
\(whereClause)
GROUP BY
t.type_identifier,
src.source_name_hash,
src.bundle_identifier,
sr.product_type,
sr.version,
sr.operating_system_version,
d.model
ORDER BY visible_record_count DESC, t.type_identifier ASC, src.bundle_identifier ASC, d.model ASC
\(limitClause)
"""
return try withStatement(sql, db: db) { statement in
var index: Int32 = 1
bindInt64(request.visibleAtObservationID, to: index, in: statement)
index += 1
bindInt64(request.visibleAtObservationID, to: index, in: statement)
index += 1
bindInt64(request.visibleAtObservationID, to: index, in: statement)
index += 1
if let sampleTypeIdentifier = request.sampleTypeIdentifier {
bindText(sampleTypeIdentifier, to: index, in: statement)
index += 1
}
var rows: [HealthArchiveSourceProvenanceRow] = []
while sqlite3_step(statement) == SQLITE_ROW {
rows.append(HealthArchiveSourceProvenanceRow(
sampleTypeIdentifier: columnText(statement, 0) ?? "",
sourceNameHash: columnText(statement, 1),
sourceBundleIdentifier: columnText(statement, 2),
sourceProductType: columnText(statement, 3),
sourceVersion: columnText(statement, 4),
sourceOperatingSystemVersion: columnText(statement, 5),
deviceModel: columnText(statement, 6),
visibleRecordCount: columnInt(statement, 7) ?? 0,
valueSum: columnDouble(statement, 8),
earliestStartDate: columnUnixDate(statement, 9),
latestEndDate: columnUnixDate(statement, 10)
))
}
return rows
}
}
func consolidationEvidence(_ request: HealthArchiveConsolidationEvidenceRequest) async throws -> [HealthArchiveConsolidationEvidence] {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
let typeClause = request.sampleTypeIdentifier == nil ? "" : "AND t.type_identifier = ?"
let topFilterClause = request.sampleTypeIdentifier == nil ? "" : "WHERE tk.type_identifier = ?"
let sql = """
WITH from_visible AS (
SELECT sample_id, version_id
FROM sample_visibility_ranges
WHERE first_observation_id <= ?
AND (last_observation_id IS NULL OR last_observation_id > ?)
),
to_visible AS (
SELECT sample_id, version_id
FROM sample_visibility_ranges
WHERE first_observation_id <= ?
AND (last_observation_id IS NULL OR last_observation_id > ?)
),
type_keys AS (
SELECT DISTINCT sample_type_id, type_identifier
FROM (
SELECT s.sample_type_id, t.type_identifier
FROM from_visible fv
JOIN samples s ON s.id = fv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
UNION
SELECT s.sample_type_id, t.type_identifier
FROM to_visible tv
JOIN samples s ON s.id = tv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
)
),
from_type_metrics AS (
SELECT
s.sample_type_id,
COUNT(*) AS from_visible_record_count,
SUM(v.numeric_value) AS from_value_sum,
MIN(v.start_date) AS from_start_date,
MAX(v.end_date) AS from_end_date,
COUNT(DISTINCT CASE WHEN src.bundle_identifier IS NULL THEN '__NULL__' ELSE src.bundle_identifier END) AS from_bundle_count,
COUNT(DISTINCT CASE WHEN d.model IS NULL THEN '__NULL__' ELSE d.model END) AS from_model_count
FROM from_visible fv
JOIN samples s ON s.id = fv.sample_id
JOIN sample_versions v ON v.id = fv.version_id
LEFT JOIN source_revisions sr ON sr.id = v.source_revision_id
LEFT JOIN sources src ON src.id = sr.source_id
LEFT JOIN hk_devices d ON d.id = v.hk_device_id
GROUP BY s.sample_type_id
),
to_type_metrics AS (
SELECT
s.sample_type_id,
COUNT(*) AS to_visible_record_count,
SUM(v.numeric_value) AS to_value_sum,
MIN(v.start_date) AS to_start_date,
MAX(v.end_date) AS to_end_date,
COUNT(DISTINCT CASE WHEN src.bundle_identifier IS NULL THEN '__NULL__' ELSE src.bundle_identifier END) AS to_bundle_count,
COUNT(DISTINCT CASE WHEN d.model IS NULL THEN '__NULL__' ELSE d.model END) AS to_model_count
FROM to_visible tv
JOIN samples s ON s.id = tv.sample_id
JOIN sample_versions v ON v.id = tv.version_id
LEFT JOIN source_revisions sr ON sr.id = v.source_revision_id
LEFT JOIN sources src ON src.id = sr.source_id
LEFT JOIN hk_devices d ON d.id = v.hk_device_id
GROUP BY s.sample_type_id
),
appeared_by_type AS (
SELECT s.sample_type_id, COUNT(*) AS appeared_count
FROM to_visible tv
LEFT JOIN from_visible fv ON fv.sample_id = tv.sample_id
JOIN samples s ON s.id = tv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
WHERE fv.sample_id IS NULL \(typeClause)
GROUP BY s.sample_type_id
),
disappeared_by_type AS (
SELECT s.sample_type_id, COUNT(*) AS disappeared_count
FROM from_visible fv
LEFT JOIN to_visible tv ON tv.sample_id = fv.sample_id
JOIN samples s ON s.id = fv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
WHERE tv.sample_id IS NULL \(typeClause)
GROUP BY s.sample_type_id
),
changed_by_type AS (
SELECT s.sample_type_id, COUNT(*) AS representation_changed_count
FROM to_visible tv
JOIN from_visible fv ON fv.sample_id = tv.sample_id
JOIN samples s ON s.id = tv.sample_id
JOIN sample_types t ON t.id = s.sample_type_id
WHERE tv.version_id != fv.version_id \(typeClause)
GROUP BY s.sample_type_id
)
SELECT
tk.type_identifier,
COALESCE(d.disappeared_count, 0) AS disappeared_count,
COALESCE(a.appeared_count, 0) AS appeared_count,
COALESCE(c.representation_changed_count, 0) AS representation_changed_count,
COALESCE(f.from_visible_record_count, 0) AS from_visible_record_count,
COALESCE(t.to_visible_record_count, 0) AS to_visible_record_count,
f.from_value_sum,
t.to_value_sum,
f.from_start_date,
f.from_end_date,
t.to_start_date,
t.to_end_date,
CASE
WHEN f.from_start_date IS NULL OR f.from_end_date IS NULL OR t.to_start_date IS NULL OR t.to_end_date IS NULL THEN NULL
ELSE MAX(0, MIN(f.from_end_date, t.to_end_date) - MAX(f.from_start_date, t.to_start_date))
END AS coverage_overlap_seconds,
CASE
WHEN COALESCE(f.from_visible_record_count, 0) = 0 OR f.from_start_date IS NULL OR f.from_end_date IS NULL OR f.from_end_date <= f.from_start_date THEN NULL
ELSE CAST(f.from_visible_record_count AS REAL) / ((f.from_end_date - f.from_start_date) / 3600.0)
END AS density_before,
CASE
WHEN COALESCE(t.to_visible_record_count, 0) = 0 OR t.to_start_date IS NULL OR t.to_end_date IS NULL OR t.to_end_date <= t.to_start_date THEN NULL
ELSE CAST(t.to_visible_record_count AS REAL) / ((t.to_end_date - t.to_start_date) / 3600.0)
END AS density_after,
CASE
WHEN COALESCE(f.from_bundle_count, 0) <= 1
AND COALESCE(f.from_model_count, 0) <= 1
AND COALESCE(t.to_bundle_count, 0) <= 1
AND COALESCE(t.to_model_count, 0) <= 1
THEN 1 ELSE 0
END AS source_compatible
FROM type_keys tk
LEFT JOIN from_type_metrics f ON f.sample_type_id = tk.sample_type_id
LEFT JOIN to_type_metrics t ON t.sample_type_id = tk.sample_type_id
LEFT JOIN appeared_by_type a ON a.sample_type_id = tk.sample_type_id
LEFT JOIN disappeared_by_type d ON d.sample_type_id = tk.sample_type_id
LEFT JOIN changed_by_type c ON c.sample_type_id = tk.sample_type_id
\(topFilterClause)
ORDER BY tk.type_identifier
"""
return try withStatement(sql, db: db) { statement in
var index: Int32 = 1
bindInt64(request.fromObservationID, to: index, in: statement)
index += 1
bindInt64(request.fromObservationID, to: index, in: statement)
index += 1
bindInt64(request.toObservationID, to: index, in: statement)
index += 1
bindInt64(request.toObservationID, to: index, in: statement)
index += 1
if let sampleTypeIdentifier = request.sampleTypeIdentifier {
bindText(sampleTypeIdentifier, to: index, in: statement)
index += 1
}
if let sampleTypeIdentifier = request.sampleTypeIdentifier {
bindText(sampleTypeIdentifier, to: index, in: statement)
index += 1
}
if let sampleTypeIdentifier = request.sampleTypeIdentifier {
bindText(sampleTypeIdentifier, to: index, in: statement)
index += 1
}
if request.sampleTypeIdentifier != nil {
bindText(request.sampleTypeIdentifier, to: index, in: statement)
index += 1
}
var rows: [HealthArchiveConsolidationEvidence] = []
while sqlite3_step(statement) == SQLITE_ROW {
let sampleTypeIdentifier = columnText(statement, 0) ?? ""
let disappearedCount = columnInt(statement, 1) ?? 0
let appearedCount = columnInt(statement, 2) ?? 0
let representationChangedCount = columnInt(statement, 3) ?? 0
let fromVisibleRecordCount = columnInt(statement, 4) ?? 0
let toVisibleRecordCount = columnInt(statement, 5) ?? 0
let fromValueSum = columnDouble(statement, 6)
let toValueSum = columnDouble(statement, 7)
let coverageOverlapSeconds = columnDouble(statement, 12)
let densityBefore = columnDouble(statement, 13)
let densityAfter = columnDouble(statement, 14)
let sourceCompatible = columnInt(statement, 15) == 1
let stableValue: Bool
if let fromValueSum, let toValueSum {
stableValue = abs(toValueSum - fromValueSum) <= max(1.0, abs(fromValueSum) * 0.1)
} else {
stableValue = false
}
let hasCoverageOverlap = (coverageOverlapSeconds ?? 0) > 0
let densityDoesNotIncrease: Bool
if let densityBefore, let densityAfter {
densityDoesNotIncrease = densityAfter <= densityBefore
} else {
densityDoesNotIncrease = false
}
let consolidationEvidencePresent = disappearedCount > 0
&& sourceCompatible
&& hasCoverageOverlap
&& densityDoesNotIncrease
&& stableValue
let label: String
let uncertaintyReason: String?
if representationChangedCount > 0 && disappearedCount == 0 && appearedCount == 0 {
label = "representation_changed"
uncertaintyReason = nil
} else if consolidationEvidencePresent {
label = "consolidation_likely"
uncertaintyReason = nil
} else if appearedCount > 0 && disappearedCount == 0 && representationChangedCount == 0 {
label = "appeared"
uncertaintyReason = nil
} else if appearedCount > 0 && disappearedCount > 0 && sourceCompatible && stableValue {
label = "aggregate_changed"
uncertaintyReason = nil
} else if disappearedCount == 0 && appearedCount == 0 && representationChangedCount == 0 {
label = "uncertain"
uncertaintyReason = "no structural change detected"
} else {
label = "uncertain"
uncertaintyReason = "insufficient evidence for a stable consolidation label"
}
rows.append(HealthArchiveConsolidationEvidence(
sampleTypeIdentifier: sampleTypeIdentifier,
fromObservationID: request.fromObservationID,
toObservationID: request.toObservationID,
disappearedCount: disappearedCount,
appearedCount: appearedCount,
representationChangedCount: representationChangedCount,
fromVisibleRecordCount: fromVisibleRecordCount,
toVisibleRecordCount: toVisibleRecordCount,
fromValueSum: fromValueSum,
toValueSum: toValueSum,
coverageOverlapSeconds: coverageOverlapSeconds,
densityBefore: densityBefore,
densityAfter: densityAfter,
sourceCompatible: sourceCompatible,
label: label,
uncertaintyReason: uncertaintyReason
))
}
return rows
}
}
func exportReport(_ request: HealthArchiveReportRequest) async throws -> URL {
let recordRequest = HealthArchiveRecordRequest(
sampleTypeIdentifier: request.typeIdentifierFilter,
fingerprints: request.includedFingerprints,
disappearedOnly: request.disappearedOnly,
firstSeenAfter: request.firstSeenAfter,
firstSeenBefore: request.firstSeenBefore,
limit: nil
)
let records = try await records(for: recordRequest)
let payload = HealthArchiveReportPayload(
reportID: request.reportID,
title: request.title,
exportedAt: Date(),
records: records
)
let data = try JSONEncoder.healthArchive.encode(payload)
let exportURL = URL.temporaryDirectory
.appending(path: "HealthProbe-\(request.reportID.uuidString)")
.appendingPathExtension("json")
try data.write(to: exportURL, options: [.atomic])
return exportURL
}
func checkIntegrity() async throws -> HealthArchiveIntegrityReport {
let db = try openDatabase()
defer { sqlite3_close(db) }
try prepareSchemaIfNeeded(db)
let schemaVersion = try archiveSchemaVersionIfPresent(db)
let sqliteIntegrityStatus = try firstText("PRAGMA integrity_check", db: db) ?? "missing"
let foreignKeyIssueCount = try countRows("PRAGMA foreign_key_check", db: db)
let requiredTables = Set(Self.requiredArchiveV2Tables)
var missingTables = Set<String>()
for tableName in requiredTables {
if try !tableExists(tableName, db: db) {
missingTables.insert(tableName)
}
}
return HealthArchiveIntegrityReport(
schemaVersion: schemaVersion,
sqliteIntegrityStatus: sqliteIntegrityStatus,
foreignKeyIssueCount: foreignKeyIssueCount,
requiredTableNames: requiredTables,
missingTableNames: missingTables
)
}
private func openDatabase() throws -> OpaquePointer? {
try FileManager.default.createDirectory(
at: databaseURL.deletingLastPathComponent(),
withIntermediateDirectories: true
)
var db: OpaquePointer?
guard sqlite3_open_v2(databaseURL.path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK else {
let message = db.map(lastErrorMessage) ?? "unable to open archive database"
sqlite3_close(db)
throw SQLiteHealthArchiveStoreError.openFailed(message)
}
return db
}
private func prepareSchemaIfNeeded(_ db: OpaquePointer?) throws {
guard !didPrepareSchema else { return }
try execute("PRAGMA journal_mode = WAL", db: db)
try execute("PRAGMA foreign_keys = ON", db: db)
let existingVersion = try archiveSchemaVersionIfPresent(db)
if let existingVersion, existingVersion > Self.archiveSchemaVersion {
throw SQLiteHealthArchiveStoreError.incompatibleSchema(existingVersion)
}
if existingVersion != Self.archiveSchemaVersion {
let needsReset = existingVersion != nil ? true : try hasUserTables(db)
if needsReset {
try resetPrototypeSchema(db)
}
}
try createArchiveV2Schema(db)
try seedArchiveMetadata(db)
didPrepareSchema = true
}
private func archiveSchemaVersionIfPresent(_ db: OpaquePointer?) throws -> Int? {
guard try tableExists("archive_metadata", db: db) else { return nil }
let sql = "SELECT value FROM archive_metadata WHERE key = 'schema_version' LIMIT 1"
return try withStatement(sql, db: db) { statement in
guard sqlite3_step(statement) == SQLITE_ROW,
let value = columnText(statement, 0) else {
return nil
}
return Int(value)
}
}
private func hasUserTables(_ db: OpaquePointer?) throws -> Bool {
let sql = """
SELECT name
FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
LIMIT 1
"""
return try withStatement(sql, db: db) { statement in
sqlite3_step(statement) == SQLITE_ROW
}
}
private func tableExists(_ tableName: String, db: OpaquePointer?) throws -> Bool {
let sql = """
SELECT 1
FROM sqlite_master
WHERE type = 'table' AND name = ?
LIMIT 1
"""
return try withStatement(sql, db: db) { statement in
bindText(tableName, to: 1, in: statement)
return sqlite3_step(statement) == SQLITE_ROW
}
}
private func resetPrototypeSchema(_ db: OpaquePointer?) throws {
// Prototype/test installs are disposable for archive v2. Future real archives
// must use explicit migrations instead of destructive reset.
try execute("PRAGMA foreign_keys = OFF", db: db)
for objectName in try schemaObjectNames(types: ["view", "trigger"], db: db) {
try execute("DROP \(objectName.kind.uppercased()) IF EXISTS \(quotedIdentifier(objectName.name))", db: db)
}
for tableName in try schemaObjectNames(types: ["table"], db: db) {
try execute("DROP TABLE IF EXISTS \(quotedIdentifier(tableName.name))", db: db)
}
try execute("PRAGMA foreign_keys = ON", db: db)
}
private func schemaObjectNames(types: [String], db: OpaquePointer?) throws -> [(kind: String, name: String)] {
let typeList = types.map { "'\($0)'" }.joined(separator: ",")
let sql = """
SELECT type, name
FROM sqlite_master
WHERE type IN (\(typeList)) AND name NOT LIKE 'sqlite_%'
ORDER BY type, name
"""
return try withStatement(sql, db: db) { statement in
var names: [(kind: String, name: String)] = []
while sqlite3_step(statement) == SQLITE_ROW {
guard let kind = columnText(statement, 0),
let name = columnText(statement, 1) else {
continue
}
names.append((kind, name))
}
return names
}
}
private func createArchiveV2Schema(_ db: OpaquePointer?) throws {
try execute("""
CREATE TABLE IF NOT EXISTS schema_migrations (
version INTEGER PRIMARY KEY,
applied_at REAL NOT NULL,
description TEXT NOT NULL
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS archive_metadata (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS device_chains (
id INTEGER PRIMARY KEY,
device_chain_hash TEXT NOT NULL UNIQUE,
created_at REAL NOT NULL,
recovered_from_keychain INTEGER NOT NULL DEFAULT 0
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS observations (
id INTEGER PRIMARY KEY,
device_chain_id INTEGER NOT NULL REFERENCES device_chains(id),
observed_at REAL NOT NULL,
started_at REAL,
ended_at REAL,
status TEXT NOT NULL,
trigger_reason TEXT NOT NULL,
app_version TEXT,
os_version TEXT,
time_zone_identifier TEXT,
time_zone_seconds_from_gmt INTEGER,
schema_version INTEGER NOT NULL,
selected_type_set_hash TEXT,
notes TEXT
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_observations_device_time ON observations(device_chain_id, observed_at)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS sample_types (
id INTEGER PRIMARY KEY,
type_identifier TEXT NOT NULL UNIQUE,
display_name TEXT,
category TEXT
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS observation_type_runs (
id INTEGER PRIMARY KEY,
observation_id INTEGER NOT NULL REFERENCES observations(id),
sample_type_id INTEGER NOT NULL REFERENCES sample_types(id),
status TEXT NOT NULL,
started_at REAL,
ended_at REAL,
anchor_before BLOB,
anchor_after BLOB,
inserted_event_count INTEGER NOT NULL DEFAULT 0,
deleted_event_count INTEGER NOT NULL DEFAULT 0,
verified_visible_count INTEGER,
error_kind TEXT,
error_message_hash TEXT,
UNIQUE(observation_id, sample_type_id)
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_type_runs_type_observation ON observation_type_runs(sample_type_id, observation_id)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS sources (
id INTEGER PRIMARY KEY,
source_name_hash TEXT,
bundle_identifier TEXT
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS source_revisions (
id INTEGER PRIMARY KEY,
source_id INTEGER NOT NULL REFERENCES sources(id),
product_type TEXT,
version TEXT,
operating_system_version TEXT,
UNIQUE(source_id, product_type, version, operating_system_version)
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS hk_devices (
id INTEGER PRIMARY KEY,
device_hash TEXT,
manufacturer_hash TEXT,
model TEXT,
hardware_version TEXT,
firmware_version TEXT,
software_version TEXT,
local_identifier_hash TEXT,
udi_hash TEXT
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS metadata_blobs (
id INTEGER PRIMARY KEY,
metadata_hash TEXT NOT NULL UNIQUE,
metadata_json TEXT NOT NULL
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS samples (
id INTEGER PRIMARY KEY,
sample_type_id INTEGER NOT NULL REFERENCES sample_types(id),
sample_uuid_hash TEXT,
strict_fingerprint TEXT NOT NULL,
semantic_fingerprint TEXT,
fuzzy_key TEXT,
first_seen_observation_id INTEGER NOT NULL REFERENCES observations(id),
first_seen_at REAL NOT NULL,
UNIQUE(sample_type_id, strict_fingerprint)
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_samples_uuid_hash ON samples(sample_uuid_hash)", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_samples_type_semantic ON samples(sample_type_id, semantic_fingerprint)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS sample_versions (
id INTEGER PRIMARY KEY,
sample_id INTEGER NOT NULL REFERENCES samples(id),
payload_hash TEXT NOT NULL,
start_date REAL NOT NULL,
end_date REAL NOT NULL,
value_kind TEXT,
numeric_value REAL,
unit TEXT,
category_value INTEGER,
workout_activity_type INTEGER,
duration_seconds REAL,
source_revision_id INTEGER REFERENCES source_revisions(id),
hk_device_id INTEGER REFERENCES hk_devices(id),
metadata_id INTEGER REFERENCES metadata_blobs(id),
created_observation_id INTEGER NOT NULL REFERENCES observations(id),
UNIQUE(sample_id, payload_hash)
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_sample_versions_sample ON sample_versions(sample_id)", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_sample_versions_time ON sample_versions(start_date, end_date)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS sample_observation_events (
id INTEGER PRIMARY KEY,
observation_id INTEGER NOT NULL REFERENCES observations(id),
sample_id INTEGER NOT NULL REFERENCES samples(id),
version_id INTEGER REFERENCES sample_versions(id),
event_kind TEXT NOT NULL,
observed_at REAL NOT NULL,
evidence_kind TEXT,
UNIQUE(observation_id, sample_id, event_kind)
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_events_observation_kind ON sample_observation_events(observation_id, event_kind)", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_events_sample ON sample_observation_events(sample_id, observation_id)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS sample_visibility_ranges (
sample_id INTEGER NOT NULL REFERENCES samples(id),
version_id INTEGER REFERENCES sample_versions(id),
first_observation_id INTEGER NOT NULL REFERENCES observations(id),
last_observation_id INTEGER REFERENCES observations(id),
first_seen_at REAL NOT NULL,
last_seen_at REAL,
PRIMARY KEY (sample_id, version_id, first_observation_id)
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_visibility_open_ranges ON sample_visibility_ranges(last_observation_id)", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_visibility_point_lookup ON sample_visibility_ranges(first_observation_id, last_observation_id)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS sample_relationships (
id INTEGER PRIMARY KEY,
observation_id INTEGER REFERENCES observations(id),
source_sample_id INTEGER NOT NULL REFERENCES samples(id),
target_sample_id INTEGER NOT NULL REFERENCES samples(id),
relationship_kind TEXT NOT NULL,
metadata_id INTEGER REFERENCES metadata_blobs(id),
UNIQUE(observation_id, source_sample_id, target_sample_id, relationship_kind)
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_relationship_source ON sample_relationships(source_sample_id, relationship_kind)", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_relationship_target ON sample_relationships(target_sample_id, relationship_kind)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS observation_type_summaries (
observation_id INTEGER NOT NULL REFERENCES observations(id),
sample_type_id INTEGER NOT NULL REFERENCES sample_types(id),
visible_record_count INTEGER NOT NULL,
appeared_count INTEGER NOT NULL DEFAULT 0,
disappeared_count INTEGER NOT NULL DEFAULT 0,
representation_changed_count INTEGER NOT NULL DEFAULT 0,
earliest_start_date REAL,
latest_end_date REAL,
value_sum REAL,
value_max REAL,
aggregate_hash TEXT,
PRIMARY KEY (observation_id, sample_type_id)
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS daily_type_aggregates (
observation_id INTEGER NOT NULL REFERENCES observations(id),
sample_type_id INTEGER NOT NULL REFERENCES sample_types(id),
bucket_start REAL NOT NULL,
bucket_end REAL NOT NULL,
visible_record_count INTEGER NOT NULL,
value_sum REAL,
value_max REAL,
source_revision_id INTEGER,
aggregate_hash TEXT,
PRIMARY KEY (observation_id, sample_type_id, bucket_start, source_revision_id)
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_daily_type_bucket ON daily_type_aggregates(sample_type_id, bucket_start)", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS export_manifests (
id INTEGER PRIMARY KEY,
export_id TEXT NOT NULL UNIQUE,
created_at REAL NOT NULL,
export_kind TEXT NOT NULL,
from_observation_id INTEGER REFERENCES observations(id),
to_observation_id INTEGER REFERENCES observations(id),
filter_json TEXT,
manifest_hash TEXT NOT NULL,
record_count INTEGER NOT NULL
)
""", db: db)
try execute("""
CREATE TABLE IF NOT EXISTS export_items (
export_manifest_id INTEGER NOT NULL REFERENCES export_manifests(id),
sample_id INTEGER NOT NULL REFERENCES samples(id),
version_id INTEGER REFERENCES sample_versions(id),
item_hash TEXT NOT NULL,
PRIMARY KEY (export_manifest_id, sample_id, version_id)
)
""", db: db)
try createLegacyArchiveSamplesTable(db)
}
private func createLegacyArchiveSamplesTable(_ db: OpaquePointer?) throws {
try execute("""
CREATE TABLE IF NOT EXISTS archive_samples (
sample_uuid_hash TEXT PRIMARY KEY NOT NULL,
type_identifier TEXT NOT NULL,
strict_fingerprint TEXT NOT NULL,
semantic_fingerprint TEXT,
start_date REAL NOT NULL,
end_date REAL NOT NULL,
first_seen_at REAL NOT NULL,
last_seen_at REAL,
last_verified_at REAL,
disappeared_at REAL,
observed_count INTEGER NOT NULL DEFAULT 1,
value_kind TEXT,
value REAL,
unit TEXT,
category_value INTEGER,
workout_activity_type INTEGER,
duration_seconds REAL,
source_name TEXT,
source_bundle_identifier TEXT,
source_product_type TEXT,
source_version TEXT,
source_operating_system_version TEXT,
device_name TEXT,
device_manufacturer TEXT,
device_model TEXT,
device_hardware_version TEXT,
device_firmware_version TEXT,
device_software_version TEXT,
device_local_identifier TEXT,
device_udi_device_identifier TEXT,
metadata_json TEXT,
archived_at REAL NOT NULL
)
""", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_archive_samples_type_date ON archive_samples(type_identifier, start_date)", db: db)
try execute("CREATE INDEX IF NOT EXISTS idx_archive_samples_strict_fingerprint ON archive_samples(strict_fingerprint)", db: db)
}
private func seedArchiveMetadata(_ db: OpaquePointer?) throws {
try upsertMetadata(key: "schema_version", value: "\(Self.archiveSchemaVersion)", db: db)
try insertMetadataIfMissing(key: "created_at_unix", value: "\(Date().timeIntervalSince1970)", db: db)
try upsertMetadata(key: "timestamp_convention", value: "unix_seconds_utc_real", db: db)
try upsertMetadata(key: "identifier_hash_algorithm", value: "hmac-sha256-local-secret", db: db)
try upsertMetadata(key: "content_hash_algorithm", value: "sha256", db: db)
try upsertMetadata(key: "prototype_reset_policy", value: "reset_or_reinitialize_test_installs", db: db)
try withStatement(
"INSERT OR IGNORE INTO schema_migrations (version, applied_at, description) VALUES (?, ?, ?)",
db: db
) { statement in
sqlite3_bind_int64(statement, 1, sqlite3_int64(Self.archiveSchemaVersion))
sqlite3_bind_double(statement, 2, Date().timeIntervalSince1970)
bindText("Initialize archive v2 schema", to: 3, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
private func upsertMetadata(key: String, value: String, db: OpaquePointer?) throws {
try withStatement("INSERT OR REPLACE INTO archive_metadata (key, value) VALUES (?, ?)", db: db) { statement in
bindText(key, to: 1, in: statement)
bindText(value, to: 2, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
private func insertMetadataIfMissing(key: String, value: String, db: OpaquePointer?) throws {
try withStatement("INSERT OR IGNORE INTO archive_metadata (key, value) VALUES (?, ?)", db: db) { statement in
bindText(key, to: 1, in: statement)
bindText(value, to: 2, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
private func upsertSamples(_ samples: [HKSample], observedAt: Date, db: OpaquePointer?) throws -> HealthArchiveWriteSummary {
let rows = samples.map { ArchiveSampleRow(sample: $0, observedAt: observedAt) }
let observationID = try createObservation(
observedAt: observedAt,
triggerReason: "anchored_page",
status: "completed",
db: db
)
var inserted = 0
var updated = 0
var unchanged = 0
var touchedTypeIDs = Set<Int64>()
for row in rows {
let result = try upsertArchiveV2Sample(row, observationID: observationID, db: db)
touchedTypeIDs.insert(result.sampleTypeID)
switch result.kind {
case .inserted:
inserted += 1
case .updated:
updated += 1
case .unchanged:
unchanged += 1
}
}
for sampleTypeID in touchedTypeIDs {
try rebuildTypeSummary(observationID: observationID, sampleTypeID: sampleTypeID, db: db)
try rebuildDailyAggregates(
observationID: observationID,
sampleTypeID: sampleTypeID,
observedAt: observedAt,
db: db
)
}
return HealthArchiveWriteSummary(
insertedCount: inserted,
updatedCount: updated,
unchangedCount: unchanged
)
}
private func upsertArchiveV2Sample(
_ row: ArchiveSampleRow,
observationID: Int64,
db: OpaquePointer?
) throws -> ArchiveV2SampleWriteResult {
let sampleTypeID = try upsertSampleType(typeIdentifier: row.typeIdentifier, db: db)
let sourceRevisionID = try upsertSourceRevision(row, db: db)
let deviceID = try upsertDevice(row, db: db)
let metadataID = try upsertMetadataBlob(row, db: db)
let sampleResult = try upsertSample(row, sampleTypeID: sampleTypeID, observationID: observationID, db: db)
let versionResult = try upsertSampleVersion(
row,
sampleID: sampleResult.id,
sourceRevisionID: sourceRevisionID,
deviceID: deviceID,
metadataID: metadataID,
observationID: observationID,
db: db
)
let writeKind: ArchiveV2SampleWriteKind
let eventKind: String
if sampleResult.inserted {
writeKind = .inserted
eventKind = "appeared"
} else if versionResult.inserted {
writeKind = .updated
eventKind = "representationChanged"
} else {
writeKind = .unchanged
eventKind = "verified"
}
try insertObservationEvent(
observationID: observationID,
sampleID: sampleResult.id,
versionID: versionResult.id,
eventKind: eventKind,
evidenceKind: "healthkit_sample",
observedAt: row.observedAt,
db: db
)
try closeOpenVisibilityRanges(
sampleID: sampleResult.id,
excludingVersionID: versionResult.id,
closedAtObservationID: observationID,
observedAt: row.observedAt,
db: db
)
try insertOpenVisibilityRangeIfNeeded(
sampleID: sampleResult.id,
versionID: versionResult.id,
observationID: observationID,
observedAt: row.observedAt,
db: db
)
return ArchiveV2SampleWriteResult(sampleTypeID: sampleTypeID, kind: writeKind)
}
private func createObservation(
observedAt: Date,
triggerReason: String,
status: String,
db: OpaquePointer?
) throws -> Int64 {
let deviceChainID = try upsertCurrentDeviceChain(db)
let timeZone = TimeZone.current
let sql = """
INSERT INTO observations (
device_chain_id, observed_at, started_at, ended_at, status, trigger_reason,
app_version, os_version, time_zone_identifier, time_zone_seconds_from_gmt,
schema_version, selected_type_set_hash, notes
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NULL)
"""
try withStatement(sql, db: db) { statement in
bindInt64(deviceChainID, to: 1, in: statement)
sqlite3_bind_double(statement, 2, observedAt.timeIntervalSince1970)
sqlite3_bind_double(statement, 3, observedAt.timeIntervalSince1970)
sqlite3_bind_double(statement, 4, Date().timeIntervalSince1970)
bindText(status, to: 5, in: statement)
bindText(triggerReason, to: 6, in: statement)
bindText(Bundle.main.infoDictionary?["CFBundleShortVersionString"] as? String, to: 7, in: statement)
bindText(ProcessInfo.processInfo.operatingSystemVersionString, to: 8, in: statement)
bindText(timeZone.identifier, to: 9, in: statement)
sqlite3_bind_int(statement, 10, Int32(timeZone.secondsFromGMT(for: observedAt)))
bindInt(Self.archiveSchemaVersion, to: 11, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
return sqlite3_last_insert_rowid(db)
}
private func upsertCurrentDeviceChain(_ db: OpaquePointer?) throws -> Int64 {
let resolution = KeychainService.resolveDeviceID(swiftDataStoreIsEmpty: false)
let chainHash = HashService.archiveContentHash(domain: "hp:v2:device_chain", parts: [resolution.id])
try withStatement(
"INSERT OR IGNORE INTO device_chains (device_chain_hash, created_at, recovered_from_keychain) VALUES (?, ?, ?)",
db: db
) { statement in
bindText(chainHash, to: 1, in: statement)
sqlite3_bind_double(statement, 2, Date().timeIntervalSince1970)
sqlite3_bind_int(statement, 3, resolution.isRecovered ? 1 : 0)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
return try requiredInt64(
"SELECT id FROM device_chains WHERE device_chain_hash = ? LIMIT 1",
db: db
) { statement in
bindText(chainHash, to: 1, in: statement)
}
}
private func upsertSampleType(typeIdentifier: String, db: OpaquePointer?) throws -> Int64 {
try withStatement(
"INSERT OR IGNORE INTO sample_types (type_identifier, display_name, category) VALUES (?, NULL, NULL)",
db: db
) { statement in
bindText(typeIdentifier, to: 1, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
return try requiredInt64("SELECT id FROM sample_types WHERE type_identifier = ? LIMIT 1", db: db) { statement in
bindText(typeIdentifier, to: 1, in: statement)
}
}
private func sampleTypeID(typeIdentifier: String, db: OpaquePointer?) throws -> Int64? {
try optionalInt64("SELECT id FROM sample_types WHERE type_identifier = ? LIMIT 1", db: db) { statement in
bindText(typeIdentifier, to: 1, in: statement)
}
}
private func upsertSourceRevision(_ row: ArchiveSampleRow, db: OpaquePointer?) throws -> Int64? {
guard row.sourceName != nil || row.sourceBundleIdentifier != nil else { return nil }
let sourceNameHash = row.sourceName.map { HashService.archiveContentHash(domain: "hp:v2:source_name", parts: [$0]) }
let sourceID = try upsertSource(sourceNameHash: sourceNameHash, bundleIdentifier: row.sourceBundleIdentifier, db: db)
if let existing = try sourceRevisionID(
sourceID: sourceID,
productType: row.sourceProductType,
version: row.sourceVersion,
operatingSystemVersion: row.sourceOperatingSystemVersion,
db: db
) {
return existing
}
try withStatement(
"INSERT INTO source_revisions (source_id, product_type, version, operating_system_version) VALUES (?, ?, ?, ?)",
db: db
) { statement in
bindInt64(sourceID, to: 1, in: statement)
bindText(row.sourceProductType, to: 2, in: statement)
bindText(row.sourceVersion, to: 3, in: statement)
bindText(row.sourceOperatingSystemVersion, to: 4, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
return sqlite3_last_insert_rowid(db)
}
private func sourceRevisionID(
sourceID: Int64,
productType: String?,
version: String?,
operatingSystemVersion: String?,
db: OpaquePointer?
) throws -> Int64? {
try optionalInt64(
"""
SELECT id FROM source_revisions
WHERE source_id = ?
AND (product_type = ? OR (product_type IS NULL AND ? IS NULL))
AND (version = ? OR (version IS NULL AND ? IS NULL))
AND (operating_system_version = ? OR (operating_system_version IS NULL AND ? IS NULL))
LIMIT 1
""",
db: db
) { statement in
bindInt64(sourceID, to: 1, in: statement)
bindText(productType, to: 2, in: statement)
bindText(productType, to: 3, in: statement)
bindText(version, to: 4, in: statement)
bindText(version, to: 5, in: statement)
bindText(operatingSystemVersion, to: 6, in: statement)
bindText(operatingSystemVersion, to: 7, in: statement)
}
}
private func upsertSource(sourceNameHash: String?, bundleIdentifier: String?, db: OpaquePointer?) throws -> Int64 {
if let existing = try optionalInt64(
"""
SELECT id FROM sources
WHERE (source_name_hash = ? OR (source_name_hash IS NULL AND ? IS NULL))
AND (bundle_identifier = ? OR (bundle_identifier IS NULL AND ? IS NULL))
LIMIT 1
""",
db: db,
bind: { statement in
bindText(sourceNameHash, to: 1, in: statement)
bindText(sourceNameHash, to: 2, in: statement)
bindText(bundleIdentifier, to: 3, in: statement)
bindText(bundleIdentifier, to: 4, in: statement)
}
) {
return existing
}
try withStatement("INSERT INTO sources (source_name_hash, bundle_identifier) VALUES (?, ?)", db: db) { statement in
bindText(sourceNameHash, to: 1, in: statement)
bindText(bundleIdentifier, to: 2, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
return sqlite3_last_insert_rowid(db)
}
private func upsertDevice(_ row: ArchiveSampleRow, db: OpaquePointer?) throws -> Int64? {
guard row.hasDeviceProvenance else { return nil }
let deviceHash = row.deviceName.map { HashService.archiveContentHash(domain: "hp:v2:device_name", parts: [$0]) }
let manufacturerHash = row.deviceManufacturer.map { HashService.archiveContentHash(domain: "hp:v2:device_manufacturer", parts: [$0]) }
let localIdentifierHash = row.deviceLocalIdentifier.map { HashService.archiveContentHash(domain: "hp:v2:device_local_id", parts: [$0]) }
let udiHash = row.deviceUDI.map { HashService.archiveContentHash(domain: "hp:v2:device_udi", parts: [$0]) }
if let existing = try optionalInt64(
"""
SELECT id FROM hk_devices
WHERE (device_hash = ? OR (device_hash IS NULL AND ? IS NULL))
AND (local_identifier_hash = ? OR (local_identifier_hash IS NULL AND ? IS NULL))
AND (udi_hash = ? OR (udi_hash IS NULL AND ? IS NULL))
AND (model = ? OR (model IS NULL AND ? IS NULL))
LIMIT 1
""",
db: db,
bind: { statement in
bindText(deviceHash, to: 1, in: statement)
bindText(deviceHash, to: 2, in: statement)
bindText(localIdentifierHash, to: 3, in: statement)
bindText(localIdentifierHash, to: 4, in: statement)
bindText(udiHash, to: 5, in: statement)
bindText(udiHash, to: 6, in: statement)
bindText(row.deviceModel, to: 7, in: statement)
bindText(row.deviceModel, to: 8, in: statement)
}
) {
return existing
}
try withStatement(
"""
INSERT INTO hk_devices (
device_hash, manufacturer_hash, model, hardware_version, firmware_version,
software_version, local_identifier_hash, udi_hash
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""",
db: db
) { statement in
bindText(deviceHash, to: 1, in: statement)
bindText(manufacturerHash, to: 2, in: statement)
bindText(row.deviceModel, to: 3, in: statement)
bindText(row.deviceHardwareVersion, to: 4, in: statement)
bindText(row.deviceFirmwareVersion, to: 5, in: statement)
bindText(row.deviceSoftwareVersion, to: 6, in: statement)
bindText(localIdentifierHash, to: 7, in: statement)
bindText(udiHash, to: 8, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
return sqlite3_last_insert_rowid(db)
}
private func upsertMetadataBlob(_ row: ArchiveSampleRow, db: OpaquePointer?) throws -> Int64? {
guard let metadataHash = row.metadataHash, let metadataJSON = row.metadataJSON else { return nil }
try withStatement(
"INSERT OR IGNORE INTO metadata_blobs (metadata_hash, metadata_json) VALUES (?, ?)",
db: db
) { statement in
bindText(metadataHash, to: 1, in: statement)
bindText(metadataJSON, to: 2, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
return try requiredInt64("SELECT id FROM metadata_blobs WHERE metadata_hash = ? LIMIT 1", db: db) { statement in
bindText(metadataHash, to: 1, in: statement)
}
}
private func upsertSample(
_ row: ArchiveSampleRow,
sampleTypeID: Int64,
observationID: Int64,
db: OpaquePointer?
) throws -> (id: Int64, inserted: Bool) {
try withStatement(
"""
INSERT OR IGNORE INTO samples (
sample_type_id, sample_uuid_hash, strict_fingerprint, semantic_fingerprint,
fuzzy_key, first_seen_observation_id, first_seen_at
) VALUES (?, ?, ?, ?, NULL, ?, ?)
""",
db: db
) { statement in
bindInt64(sampleTypeID, to: 1, in: statement)
bindText(row.sampleUUIDHash, to: 2, in: statement)
bindText(row.strictFingerprint, to: 3, in: statement)
bindText(row.semanticFingerprint, to: 4, in: statement)
bindInt64(observationID, to: 5, in: statement)
sqlite3_bind_double(statement, 6, row.observedAt.timeIntervalSince1970)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
let inserted = sqlite3_changes(db) > 0
let id = try requiredInt64(
"SELECT id FROM samples WHERE sample_type_id = ? AND strict_fingerprint = ? LIMIT 1",
db: db
) { statement in
bindInt64(sampleTypeID, to: 1, in: statement)
bindText(row.strictFingerprint, to: 2, in: statement)
}
return (id, inserted)
}
private func sampleID(sampleUUIDHash: String, sampleTypeID: Int64, db: OpaquePointer?) throws -> Int64? {
try optionalInt64(
"SELECT id FROM samples WHERE sample_type_id = ? AND sample_uuid_hash = ? LIMIT 1",
db: db
) { statement in
bindInt64(sampleTypeID, to: 1, in: statement)
bindText(sampleUUIDHash, to: 2, in: statement)
}
}
private func upsertSampleVersion(
_ row: ArchiveSampleRow,
sampleID: Int64,
sourceRevisionID: Int64?,
deviceID: Int64?,
metadataID: Int64?,
observationID: Int64,
db: OpaquePointer?
) throws -> (id: Int64, inserted: Bool) {
try withStatement(
"""
INSERT OR IGNORE INTO sample_versions (
sample_id, payload_hash, start_date, end_date, value_kind, numeric_value,
unit, category_value, workout_activity_type, duration_seconds,
source_revision_id, hk_device_id, metadata_id, created_observation_id
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
db: db
) { statement in
bindInt64(sampleID, to: 1, in: statement)
bindText(row.payloadHash, to: 2, in: statement)
sqlite3_bind_double(statement, 3, row.startDate.timeIntervalSince1970)
sqlite3_bind_double(statement, 4, row.endDate.timeIntervalSince1970)
bindText(row.valueKind, to: 5, in: statement)
bindDouble(row.value, to: 6, in: statement)
bindText(row.unit, to: 7, in: statement)
bindInt(row.categoryValue, to: 8, in: statement)
bindInt(row.workoutActivityType, to: 9, in: statement)
bindDouble(row.durationSeconds, to: 10, in: statement)
bindInt64(sourceRevisionID, to: 11, in: statement)
bindInt64(deviceID, to: 12, in: statement)
bindInt64(metadataID, to: 13, in: statement)
bindInt64(observationID, to: 14, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
let inserted = sqlite3_changes(db) > 0
let id = try requiredInt64(
"SELECT id FROM sample_versions WHERE sample_id = ? AND payload_hash = ? LIMIT 1",
db: db
) { statement in
bindInt64(sampleID, to: 1, in: statement)
bindText(row.payloadHash, to: 2, in: statement)
}
return (id, inserted)
}
private func insertObservationEvent(
observationID: Int64,
sampleID: Int64,
versionID: Int64?,
eventKind: String,
evidenceKind: String,
observedAt: Date,
db: OpaquePointer?
) throws {
try withStatement(
"""
INSERT OR IGNORE INTO sample_observation_events (
observation_id, sample_id, version_id, event_kind, observed_at, evidence_kind
) VALUES (?, ?, ?, ?, ?, ?)
""",
db: db
) { statement in
bindInt64(observationID, to: 1, in: statement)
bindInt64(sampleID, to: 2, in: statement)
bindInt64(versionID, to: 3, in: statement)
bindText(eventKind, to: 4, in: statement)
sqlite3_bind_double(statement, 5, observedAt.timeIntervalSince1970)
bindText(evidenceKind, to: 6, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
private func closeOpenVisibilityRanges(
sampleID: Int64,
excludingVersionID: Int64?,
closedAtObservationID: Int64,
observedAt: Date,
db: OpaquePointer?
) throws {
let versionPredicate = excludingVersionID == nil
? ""
: "AND (version_id IS NULL OR version_id != ?)"
let sql = """
UPDATE sample_visibility_ranges
SET last_observation_id = ?, last_seen_at = ?
WHERE sample_id = ? AND last_observation_id IS NULL \(versionPredicate)
"""
try withStatement(sql, db: db) { statement in
bindInt64(closedAtObservationID, to: 1, in: statement)
sqlite3_bind_double(statement, 2, observedAt.timeIntervalSince1970)
bindInt64(sampleID, to: 3, in: statement)
if let excludingVersionID {
bindInt64(excludingVersionID, to: 4, in: statement)
}
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
private func insertOpenVisibilityRangeIfNeeded(
sampleID: Int64,
versionID: Int64,
observationID: Int64,
observedAt: Date,
db: OpaquePointer?
) throws {
let existing = try optionalInt64(
"""
SELECT first_observation_id
FROM sample_visibility_ranges
WHERE sample_id = ? AND version_id = ? AND last_observation_id IS NULL
LIMIT 1
""",
db: db
) { statement in
bindInt64(sampleID, to: 1, in: statement)
bindInt64(versionID, to: 2, in: statement)
}
guard existing == nil else { return }
try withStatement(
"""
INSERT OR IGNORE INTO sample_visibility_ranges (
sample_id, version_id, first_observation_id, last_observation_id, first_seen_at, last_seen_at
) VALUES (?, ?, ?, NULL, ?, NULL)
""",
db: db
) { statement in
bindInt64(sampleID, to: 1, in: statement)
bindInt64(versionID, to: 2, in: statement)
bindInt64(observationID, to: 3, in: statement)
sqlite3_bind_double(statement, 4, observedAt.timeIntervalSince1970)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
private func rebuildTypeSummary(observationID: Int64, sampleTypeID: Int64, db: OpaquePointer?) throws {
let summary = try typeSummary(observationID: observationID, sampleTypeID: sampleTypeID, db: db)
let aggregateParts: [String?] = [
String(observationID),
String(sampleTypeID),
String(summary.visibleRecordCount),
String(summary.appearedCount),
String(summary.disappearedCount),
String(summary.representationChangedCount),
summary.earliestStartDate.map { String($0) },
summary.latestEndDate.map { String($0) },
summary.valueSum.map { String(format: "%.17g", $0) },
summary.valueMax.map { String(format: "%.17g", $0) }
]
let aggregateHash = HashService.archiveContentHash(
domain: "hp:v2:type_summary",
parts: aggregateParts
)
try withStatement(
"""
INSERT OR REPLACE INTO observation_type_summaries (
observation_id, sample_type_id, visible_record_count, appeared_count,
disappeared_count, representation_changed_count, earliest_start_date,
latest_end_date, value_sum, value_max, aggregate_hash
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
db: db
) { statement in
bindInt64(observationID, to: 1, in: statement)
bindInt64(sampleTypeID, to: 2, in: statement)
bindInt(summary.visibleRecordCount, to: 3, in: statement)
bindInt(summary.appearedCount, to: 4, in: statement)
bindInt(summary.disappearedCount, to: 5, in: statement)
bindInt(summary.representationChangedCount, to: 6, in: statement)
bindDouble(summary.earliestStartDate, to: 7, in: statement)
bindDouble(summary.latestEndDate, to: 8, in: statement)
bindDouble(summary.valueSum, to: 9, in: statement)
bindDouble(summary.valueMax, to: 10, in: statement)
bindText(aggregateHash, to: 11, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
private func rebuildDailyAggregates(
observationID: Int64,
sampleTypeID: Int64,
observedAt: Date,
db: OpaquePointer?
) throws {
let secondsFromGMT = TimeZone.current.secondsFromGMT(for: observedAt)
try withStatement(
"DELETE FROM daily_type_aggregates WHERE observation_id = ? AND sample_type_id = ?",
db: db
) { statement in
bindInt64(observationID, to: 1, in: statement)
bindInt64(sampleTypeID, to: 2, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
let rows = try dailyAggregateRows(sampleTypeID: sampleTypeID, secondsFromGMT: secondsFromGMT, db: db)
try withStatement(
"""
INSERT INTO daily_type_aggregates (
observation_id, sample_type_id, bucket_start, bucket_end,
visible_record_count, value_sum, value_max, source_revision_id, aggregate_hash
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
db: db
) { statement in
for row in rows {
sqlite3_reset(statement)
sqlite3_clear_bindings(statement)
let aggregateHash = HashService.archiveContentHash(
domain: "hp:v2:daily_type_aggregate",
parts: row.hashParts(observationID: observationID, sampleTypeID: sampleTypeID)
)
bindInt64(observationID, to: 1, in: statement)
bindInt64(sampleTypeID, to: 2, in: statement)
bindDouble(row.bucketStart, to: 3, in: statement)
bindDouble(row.bucketEnd, to: 4, in: statement)
bindInt(row.visibleRecordCount, to: 5, in: statement)
bindDouble(row.valueSum, to: 6, in: statement)
bindDouble(row.valueMax, to: 7, in: statement)
bindInt64(row.sourceRevisionID, to: 8, in: statement)
bindText(aggregateHash, to: 9, in: statement)
guard sqlite3_step(statement) == SQLITE_DONE else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
}
}
private func dailyAggregateRows(
sampleTypeID: Int64,
secondsFromGMT: Int,
db: OpaquePointer?
) throws -> [ArchiveV2DailyAggregateRow] {
let sql = """
SELECT
CAST(((v.start_date + ?) / 86400) AS INTEGER) * 86400 - ? AS bucket_start,
CAST(((v.start_date + ?) / 86400) AS INTEGER) * 86400 - ? + 86400 AS bucket_end,
COUNT(*),
SUM(v.numeric_value),
MAX(v.numeric_value),
v.source_revision_id
FROM sample_visibility_ranges r
JOIN samples s ON s.id = r.sample_id
JOIN sample_versions v ON v.id = r.version_id
WHERE s.sample_type_id = ? AND r.last_observation_id IS NULL
GROUP BY bucket_start, bucket_end, v.source_revision_id
ORDER BY bucket_start ASC, v.source_revision_id ASC
"""
return try withStatement(sql, db: db) { statement in
sqlite3_bind_double(statement, 1, Double(secondsFromGMT))
sqlite3_bind_double(statement, 2, Double(secondsFromGMT))
sqlite3_bind_double(statement, 3, Double(secondsFromGMT))
sqlite3_bind_double(statement, 4, Double(secondsFromGMT))
bindInt64(sampleTypeID, to: 5, in: statement)
var rows: [ArchiveV2DailyAggregateRow] = []
while sqlite3_step(statement) == SQLITE_ROW {
rows.append(ArchiveV2DailyAggregateRow(
bucketStart: sqlite3_column_double(statement, 0),
bucketEnd: sqlite3_column_double(statement, 1),
visibleRecordCount: columnInt(statement, 2) ?? 0,
valueSum: columnDouble(statement, 3),
valueMax: columnDouble(statement, 4),
sourceRevisionID: columnInt64(statement, 5)
))
}
return rows
}
}
private func typeSummary(observationID: Int64, sampleTypeID: Int64, db: OpaquePointer?) throws -> ArchiveV2TypeSummary {
let counts = try eventCounts(observationID: observationID, sampleTypeID: sampleTypeID, db: db)
let aggregate = try visibleAggregate(sampleTypeID: sampleTypeID, db: db)
return ArchiveV2TypeSummary(
visibleRecordCount: aggregate.visibleRecordCount,
appearedCount: counts.appeared,
disappearedCount: counts.disappeared,
representationChangedCount: counts.representationChanged,
earliestStartDate: aggregate.earliestStartDate,
latestEndDate: aggregate.latestEndDate,
valueSum: aggregate.valueSum,
valueMax: aggregate.valueMax
)
}
private func eventCounts(
observationID: Int64,
sampleTypeID: Int64,
db: OpaquePointer?
) throws -> (appeared: Int, disappeared: Int, representationChanged: Int) {
let sql = """
SELECT event_kind, COUNT(*)
FROM sample_observation_events e
JOIN samples s ON s.id = e.sample_id
WHERE e.observation_id = ? AND s.sample_type_id = ?
GROUP BY event_kind
"""
return try withStatement(sql, db: db) { statement in
bindInt64(observationID, to: 1, in: statement)
bindInt64(sampleTypeID, to: 2, in: statement)
var appeared = 0
var disappeared = 0
var representationChanged = 0
while sqlite3_step(statement) == SQLITE_ROW {
let kind = columnText(statement, 0)
let count = columnInt(statement, 1) ?? 0
switch kind {
case "appeared":
appeared = count
case "disappeared":
disappeared = count
case "representationChanged":
representationChanged = count
default:
break
}
}
return (appeared, disappeared, representationChanged)
}
}
private func visibleAggregate(sampleTypeID: Int64, db: OpaquePointer?) throws -> ArchiveV2VisibleAggregate {
let sql = """
SELECT COUNT(*), MIN(v.start_date), MAX(v.end_date), SUM(v.numeric_value), MAX(v.numeric_value)
FROM sample_visibility_ranges r
JOIN samples s ON s.id = r.sample_id
JOIN sample_versions v ON v.id = r.version_id
WHERE s.sample_type_id = ? AND r.last_observation_id IS NULL
"""
return try withStatement(sql, db: db) { statement in
bindInt64(sampleTypeID, to: 1, in: statement)
guard sqlite3_step(statement) == SQLITE_ROW else {
return ArchiveV2VisibleAggregate(
visibleRecordCount: 0,
earliestStartDate: nil,
latestEndDate: nil,
valueSum: nil,
valueMax: nil
)
}
return ArchiveV2VisibleAggregate(
visibleRecordCount: columnInt(statement, 0) ?? 0,
earliestStartDate: columnDouble(statement, 1),
latestEndDate: columnDouble(statement, 2),
valueSum: columnDouble(statement, 3),
valueMax: columnDouble(statement, 4)
)
}
}
private func bindDiffObservationIDs(
_ fromObservationID: Int64,
_ toObservationID: Int64,
to statement: OpaquePointer?,
startingAt index: inout Int32
) {
bindInt64(fromObservationID, to: index, in: statement)
index += 1
bindInt64(fromObservationID, to: index, in: statement)
index += 1
bindInt64(toObservationID, to: index, in: statement)
index += 1
bindInt64(toObservationID, to: index, in: statement)
index += 1
}
private func archiveRecord(from statement: OpaquePointer?) -> ArchivedHealthRecord {
ArchivedHealthRecord(
id: columnText(statement, 0) ?? "",
sampleTypeIdentifier: columnText(statement, 1) ?? "",
strictFingerprint: columnText(statement, 2) ?? "",
semanticFingerprint: columnText(statement, 3),
healthKitUUIDHash: columnText(statement, 4),
startDate: columnUnixDate(statement, 5) ?? Date(timeIntervalSince1970: 0),
endDate: columnUnixDate(statement, 6) ?? Date(timeIntervalSince1970: 0),
firstSeenAt: columnUnixDate(statement, 7) ?? Date(timeIntervalSince1970: 0),
lastSeenAt: columnUnixDate(statement, 8),
lastVerifiedAt: columnUnixDate(statement, 9),
disappearedAt: columnUnixDate(statement, 10),
valueKind: columnText(statement, 11),
value: columnDouble(statement, 12),
unit: columnText(statement, 13),
categoryValue: columnInt(statement, 14),
workoutActivityType: columnInt(statement, 15),
durationSeconds: columnDouble(statement, 16),
sourceName: nil,
sourceBundleIdentifier: columnText(statement, 17),
deviceName: nil
)
}
private func requiredInt64(
_ sql: String,
db: OpaquePointer?,
bind: (OpaquePointer?) throws -> Void
) throws -> Int64 {
guard let value = try optionalInt64(sql, db: db, bind: bind) else {
throw SQLiteHealthArchiveStoreError.stepFailed("missing required row")
}
return value
}
private func optionalInt64(
_ sql: String,
db: OpaquePointer?,
bind: (OpaquePointer?) throws -> Void
) throws -> Int64? {
try withStatement(sql, db: db) { statement in
try bind(statement)
guard sqlite3_step(statement) == SQLITE_ROW else { return nil }
return sqlite3_column_int64(statement, 0)
}
}
private func firstText(_ sql: String, db: OpaquePointer?) throws -> String? {
try withStatement(sql, db: db) { statement in
guard sqlite3_step(statement) == SQLITE_ROW else { return nil }
return columnText(statement, 0)
}
}
private func countRows(_ sql: String, db: OpaquePointer?) throws -> Int {
try withStatement(sql, db: db) { statement in
var count = 0
while sqlite3_step(statement) == SQLITE_ROW {
count += 1
}
return count
}
}
private func execute(_ sql: String, db: OpaquePointer?) throws {
guard sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK else {
throw SQLiteHealthArchiveStoreError.stepFailed(lastErrorMessage(db))
}
}
private func withStatement<T>(_ sql: String, db: OpaquePointer?, body: (OpaquePointer?) throws -> T) throws -> T {
var statement: OpaquePointer?
guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
throw SQLiteHealthArchiveStoreError.prepareFailed(lastErrorMessage(db))
}
defer { sqlite3_finalize(statement) }
return try body(statement)
}
}
private struct ArchiveSampleRow {
let sampleUUIDHash: String
let typeIdentifier: String
let strictFingerprint: String
let semanticFingerprint: String
let payloadHash: String
let startDate: Date
let endDate: Date
let observedAt: Date
let valueKind: String?
let value: Double?
let unit: String?
let categoryValue: Int?
let workoutActivityType: Int?
let durationSeconds: Double?
let sourceName: String?
let sourceBundleIdentifier: String?
let sourceProductType: String?
let sourceVersion: String?
let sourceOperatingSystemVersion: String?
let deviceName: String?
let deviceManufacturer: String?
let deviceModel: String?
let deviceHardwareVersion: String?
let deviceFirmwareVersion: String?
let deviceSoftwareVersion: String?
let deviceLocalIdentifier: String?
let deviceUDI: String?
let metadataJSON: String?
let metadataHash: String?
nonisolated var hasDeviceProvenance: Bool {
deviceName != nil ||
deviceManufacturer != nil ||
deviceModel != nil ||
deviceHardwareVersion != nil ||
deviceFirmwareVersion != nil ||
deviceSoftwareVersion != nil ||
deviceLocalIdentifier != nil ||
deviceUDI != nil
}
nonisolated init(sample: HKSample, observedAt: Date) {
let sampleUUID = sample.uuid.uuidString
let typeIdentifier = sample.sampleType.identifier
let startDate = sample.startDate
let endDate = sample.endDate
let quantity = ArchiveSampleRow.quantityPayload(sample)
let category = sample as? HKCategorySample
let workout = sample as? HKWorkout
let sourceRevision = sample.sourceRevision
let device = sample.device
let valueKind = quantity?.kind ?? (category == nil ? (workout == nil ? nil : "workout") : "category")
let numericValue = quantity?.value
let unit = quantity?.unit
let categoryValue = category?.value
let workoutActivityType = workout.map { Int($0.workoutActivityType.rawValue) }
let durationSeconds = workout?.duration
let sourceBundleIdentifier = sourceRevision.source.bundleIdentifier
let sourceProductType = sourceRevision.productType
let sourceVersion = sourceRevision.version
let sourceOperatingSystemVersion = ArchiveSampleRow.operatingSystemVersionString(sourceRevision.operatingSystemVersion)
let deviceModel = device?.model
let deviceHardwareVersion = device?.hardwareVersion
let deviceFirmwareVersion = device?.firmwareVersion
let deviceSoftwareVersion = device?.softwareVersion
let deviceLocalIdentifier = device?.localIdentifier
let deviceUDI = device?.udiDeviceIdentifier
let metadataJSON = ArchiveSampleRow.metadataJSONString(sample.metadata)
let metadataHash = metadataJSON.map { HashService.archiveContentHash(domain: "hp:v2:metadata", parts: [$0]) }
self.sampleUUIDHash = HashService.sampleUUIDHash(sampleUUID)
self.typeIdentifier = typeIdentifier
self.strictFingerprint = HashService.sampleFingerprint(
typeIdentifier: typeIdentifier,
sampleUUID: sampleUUID,
startDate: startDate,
endDate: endDate
)
self.semanticFingerprint = HashService.archiveSemanticFingerprint(
typeIdentifier: typeIdentifier,
startDate: startDate,
endDate: endDate,
value: numericValue,
unit: unit,
categoryValue: categoryValue,
workoutActivityType: workout?.workoutActivityType.rawValue,
sourceBundleIdentifier: sourceBundleIdentifier
)
self.valueKind = valueKind
self.value = numericValue
self.unit = unit
self.categoryValue = categoryValue
self.workoutActivityType = workoutActivityType
self.durationSeconds = durationSeconds
self.sourceName = sourceRevision.source.name
self.sourceBundleIdentifier = sourceBundleIdentifier
self.sourceProductType = sourceProductType
self.sourceVersion = sourceVersion
self.sourceOperatingSystemVersion = sourceOperatingSystemVersion
self.deviceName = device?.name
self.deviceManufacturer = device?.manufacturer
self.deviceModel = deviceModel
self.deviceHardwareVersion = deviceHardwareVersion
self.deviceFirmwareVersion = deviceFirmwareVersion
self.deviceSoftwareVersion = deviceSoftwareVersion
self.deviceLocalIdentifier = deviceLocalIdentifier
self.deviceUDI = deviceUDI
self.metadataJSON = metadataJSON
self.metadataHash = metadataHash
self.payloadHash = HashService.archiveContentHash(
domain: "hp:v2:payload",
parts: [
typeIdentifier,
ArchiveSampleRow.timestampString(startDate),
ArchiveSampleRow.timestampString(endDate),
valueKind,
numericValue.map { String(format: "%.17g", $0) },
unit,
categoryValue.map(String.init),
workoutActivityType.map(String.init),
durationSeconds.map { String(format: "%.17g", $0) },
sourceBundleIdentifier,
sourceProductType,
sourceVersion,
sourceOperatingSystemVersion,
deviceModel,
deviceHardwareVersion,
deviceFirmwareVersion,
deviceSoftwareVersion,
deviceLocalIdentifier,
deviceUDI,
metadataHash
]
)
self.startDate = startDate
self.endDate = endDate
self.observedAt = observedAt
}
nonisolated private static func timestampString(_ date: Date) -> String {
String(format: "%.6f", date.timeIntervalSince1970)
}
nonisolated private static func quantityPayload(_ sample: HKSample) -> (kind: String, value: Double, unit: String)? {
guard let sample = sample as? HKQuantitySample else { return nil }
let identifier = sample.quantityType.identifier
switch identifier {
case HKQuantityTypeIdentifier.heartRate.rawValue,
HKQuantityTypeIdentifier.restingHeartRate.rawValue:
return ("quantity", sample.quantity.doubleValue(for: HKUnit.count().unitDivided(by: .minute())), "count/min")
case HKQuantityTypeIdentifier.respiratoryRate.rawValue:
return ("quantity", sample.quantity.doubleValue(for: HKUnit.count().unitDivided(by: .minute())), "count/min")
case HKQuantityTypeIdentifier.activeEnergyBurned.rawValue:
return ("quantity", sample.quantity.doubleValue(for: .kilocalorie()), "kcal")
case HKQuantityTypeIdentifier.distanceWalkingRunning.rawValue:
return ("quantity", sample.quantity.doubleValue(for: .meter()), "m")
case HKQuantityTypeIdentifier.appleExerciseTime.rawValue:
return ("quantity", sample.quantity.doubleValue(for: .minute()), "min")
case HKQuantityTypeIdentifier.environmentalAudioExposure.rawValue,
HKQuantityTypeIdentifier.headphoneAudioExposure.rawValue:
return ("quantity", sample.quantity.doubleValue(for: .decibelAWeightedSoundPressureLevel()), "dBASPL")
case HKQuantityTypeIdentifier.bodyMass.rawValue:
return ("quantity", sample.quantity.doubleValue(for: HKUnit.gramUnit(with: .kilo)), "kg")
case HKQuantityTypeIdentifier.vo2Max.rawValue:
let unit = HKUnit.literUnit(with: .milli)
.unitDivided(by: HKUnit.gramUnit(with: .kilo))
.unitDivided(by: .minute())
return ("quantity", sample.quantity.doubleValue(for: unit), "mL/kg/min")
default:
return ("quantity", sample.quantity.doubleValue(for: .count()), "count")
}
}
nonisolated private static func metadataJSONString(_ metadata: [String: Any]?) -> String? {
guard let metadata, !metadata.isEmpty else { return nil }
let sanitized = metadata.mapValues(sanitize)
guard JSONSerialization.isValidJSONObject(sanitized),
let data = try? JSONSerialization.data(withJSONObject: sanitized, options: [.sortedKeys]) else {
return nil
}
return String(data: data, encoding: .utf8)
}
nonisolated private static func sanitize(_ value: Any) -> Any {
switch value {
case let value as String:
return value
case let value as NSNumber:
return value
case let value as Date:
return ISO8601DateFormatter().string(from: value)
case let value as UUID:
return value.uuidString
case let values as [Any]:
return values.map(sanitize)
case let values as [String: Any]:
return values.mapValues(sanitize)
default:
return String(describing: value)
}
}
nonisolated private static func operatingSystemVersionString(_ version: OperatingSystemVersion) -> String? {
guard version.majorVersion != 0 || version.minorVersion != 0 || version.patchVersion != 0 else {
return nil
}
guard (0...100).contains(version.majorVersion),
(0...1_000).contains(version.minorVersion),
(0...1_000).contains(version.patchVersion) else {
return nil
}
return "\(version.majorVersion).\(version.minorVersion).\(version.patchVersion)"
}
}
private enum ArchiveV2SampleWriteKind {
case inserted
case updated
case unchanged
}
private struct ArchiveV2SampleWriteResult {
let sampleTypeID: Int64
let kind: ArchiveV2SampleWriteKind
}
private struct ArchiveV2TypeSummary {
let visibleRecordCount: Int
let appearedCount: Int
let disappearedCount: Int
let representationChangedCount: Int
let earliestStartDate: Double?
let latestEndDate: Double?
let valueSum: Double?
let valueMax: Double?
}
private struct ArchiveV2VisibleAggregate {
let visibleRecordCount: Int
let earliestStartDate: Double?
let latestEndDate: Double?
let valueSum: Double?
let valueMax: Double?
}
private struct ArchiveV2DailyAggregateRow {
let bucketStart: Double
let bucketEnd: Double
let visibleRecordCount: Int
let valueSum: Double?
let valueMax: Double?
let sourceRevisionID: Int64?
nonisolated func hashParts(observationID: Int64, sampleTypeID: Int64) -> [String?] {
[
String(observationID),
String(sampleTypeID),
String(bucketStart),
String(bucketEnd),
String(visibleRecordCount),
valueSum.map { String(format: "%.17g", $0) },
valueMax.map { String(format: "%.17g", $0) },
sourceRevisionID.map(String.init)
]
}
}
nonisolated private struct HealthArchiveReportPayload: Encodable {
let reportID: UUID
let title: String
let exportedAt: Date
let records: [ArchivedHealthRecord]
}
nonisolated private extension JSONEncoder {
static var healthArchive: JSONEncoder {
let encoder = JSONEncoder()
encoder.dateEncodingStrategy = .iso8601
encoder.outputFormatting = [.prettyPrinted, .sortedKeys]
return encoder
}
}
nonisolated private func bindText(_ value: String?, to index: Int32, in statement: OpaquePointer?) {
guard let value else {
sqlite3_bind_null(statement, index)
return
}
sqlite3_bind_text(statement, index, value, -1, unsafeBitCast(-1, to: sqlite3_destructor_type.self))
}
nonisolated private func bindDouble(_ value: Double?, to index: Int32, in statement: OpaquePointer?) {
guard let value else {
sqlite3_bind_null(statement, index)
return
}
sqlite3_bind_double(statement, index, value)
}
nonisolated private func bindInt(_ value: Int?, to index: Int32, in statement: OpaquePointer?) {
guard let value else {
sqlite3_bind_null(statement, index)
return
}
sqlite3_bind_int64(statement, index, sqlite3_int64(value))
}
nonisolated private func bindInt64(_ value: Int64?, to index: Int32, in statement: OpaquePointer?) {
guard let value else {
sqlite3_bind_null(statement, index)
return
}
sqlite3_bind_int64(statement, index, sqlite3_int64(value))
}
nonisolated private func columnText(_ statement: OpaquePointer?, _ index: Int32) -> String? {
guard sqlite3_column_type(statement, index) != SQLITE_NULL,
let pointer = sqlite3_column_text(statement, index) else {
return nil
}
return String(cString: pointer)
}
nonisolated private func columnDate(_ statement: OpaquePointer?, _ index: Int32) -> Date? {
guard sqlite3_column_type(statement, index) != SQLITE_NULL else { return nil }
return Date(timeIntervalSinceReferenceDate: sqlite3_column_double(statement, index))
}
nonisolated private func columnUnixDate(_ statement: OpaquePointer?, _ index: Int32) -> Date? {
guard sqlite3_column_type(statement, index) != SQLITE_NULL else { return nil }
return Date(timeIntervalSince1970: sqlite3_column_double(statement, index))
}
nonisolated private func columnDouble(_ statement: OpaquePointer?, _ index: Int32) -> Double? {
guard sqlite3_column_type(statement, index) != SQLITE_NULL else { return nil }
return sqlite3_column_double(statement, index)
}
nonisolated private func columnInt(_ statement: OpaquePointer?, _ index: Int32) -> Int? {
guard sqlite3_column_type(statement, index) != SQLITE_NULL else { return nil }
return Int(sqlite3_column_int(statement, index))
}
nonisolated private func columnInt64(_ statement: OpaquePointer?, _ index: Int32) -> Int64? {
guard sqlite3_column_type(statement, index) != SQLITE_NULL else { return nil }
return sqlite3_column_int64(statement, index)
}
nonisolated private func quotedIdentifier(_ value: String) -> String {
"\"\(value.replacingOccurrences(of: "\"", with: "\"\""))\""
}
nonisolated private func lastErrorMessage(_ db: OpaquePointer?) -> String {
guard let message = sqlite3_errmsg(db) else { return "unknown SQLite error" }
return String(cString: message)
}