How can I open and write to an SQLite database from my DeviceActivityReport Extension?

Hello everyone,

I’m working on an iOS app that uses the new DeviceActivity framework to monitor and report user screen‐time in an extension (DeviceActivityReportExtension). I need to persist my processed screen‐time data into a standalone SQLite database inside the extension, but I’m running into issues opening and writing to the database file.

Here’s what I’ve tried so far:

import UIKit import DeviceActivity import SQLite3

class DeviceActivityReportExtension: DeviceActivityReportExtension { private var db: OpaquePointer?

override func didReceive(_ report: DeviceActivityReport) async {
    // 1. Construct path in app container:
    let containerURL = FileManager.default.containerURL(forSecurityApplicationGroupIdentifier: "group.com.mycompany.myapp")
    let dbURL = containerURL?.appendingPathComponent("ScreenTimeReports.db")

    // 2. Open database:
    if sqlite3_open(dbURL?.path, &db) != SQLITE_OK {
        print("❌ Unable to open database at \(dbURL?.path ?? "unknown path")")
        return
    }
    defer { sqlite3_close(db) }

    // 3. Create table if needed:
    let createSQL = """
    CREATE TABLE IF NOT EXISTS reports (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT,
        totalScreenTime DOUBLE
    );
    """
    if sqlite3_exec(db, createSQL, nil, nil, nil) != SQLITE_OK {
        print("❌ Could not create table: \(String(cString: sqlite3_errmsg(db)))")
        return
    }

    // 4. Insert data:
    let insertSQL = "INSERT INTO reports (date, totalScreenTime) VALUES (?, ?);"
    var stmt: OpaquePointer?
    if sqlite3_prepare_v2(db, insertSQL, -1, &stmt, nil) == SQLITE_OK {
        sqlite3_bind_text(stmt, 1, report.date.description, -1, nil)
        sqlite3_bind_double(stmt, 2, report.totalActivityDuration)
        if sqlite3_step(stmt) != SQLITE_DONE {
            print("❌ Insert failed: \(String(cString: sqlite3_errmsg(db)))")
        }
    }
    sqlite3_finalize(stmt)
}

}

However:

Path issues: The extension’s sandbox is separate from the app’s. I’m not sure if I can use the same App Group container, or if there’s a better location for an on‐extension database.

Entitlements: I’ve added the App Group (group.com.mycompany.myapp) to both the main app and the extension, but the file never appears, and I still get “unable to open database” errors.

My questions are:

How do I correctly construct a file URL for an SQLite file in a DeviceActivityReportExtension?

Is SQLite the recommended approach here, or is there a more “Apple-approved” pattern for writing data from a DeviceActivity extension?

Any sample code snippets, pointers to relevant Apple documentation, or alternative approaches would be greatly appreciated!