Proposal: Built-in Diagnostics Collection in sqlite-core

[khawarizmus]

Problem Statement

Currently, diagnostics collection requires SDK-level interception. The diagnostics app uses adapters like RecordingStorageAdapter (JS) and RustClientInterceptor (Rust) that wrap storage adapters to track bucket (size, operations, status) and table (row counts, data sizes) diagnostics.

This approach has limitations:

• SDK-specific: Each SDK must implement its own interception layer
• Inconsistency: Different SDKs may collect different metrics
• Complexity: Requires wrapping/hijacking storage adapters

Proposed Solution

Move diagnostics collection into sqlite-core by:

• Adding a SQL function (e.g., powersync_enable_diagnostics(enable BOOLEAN)) to enable/disable collection
• Creating internal diagnostics tables (e.g., ps_diagnostics_buckets, ps_diagnostics_tables) that are automatically populated
• Using SQLite hooks/triggers to collect metrics from existing PowerSync operations

Benefits

• Universal: All SDKs benefit automatically
• Simple: SDKs only need to invoke the function if a flag is present, then query the tables
• Consistent: Same diagnostics across all platforms
• Maintainable: Single implementation in sqlite-core
• Performance: Can be optimized at the core level
• Platform-agnostic tooling: Enables consistent diagnostics across platforms (diagnostics app, Nuxt module, future Chrome extension, Flutter devtools) without platform-specific interception

Possible API

-- Enable diagnostics
SELECT powersync_enable_diagnostics(1);

-- Disable diagnostics (stops collection, tables remain)
SELECT powersync_enable_diagnostics(0);

-- Enable diagnostics (with optional clear)
SELECT powersync_enable_diagnostics(1, 1);  -- enable and clear existing data
SELECT powersync_enable_diagnostics(1, 0);  -- enable without clearing

-- Clear all diagnostics
SELECT powersync_clear_diagnostics();

-- Or clear specific tables (if needed)
SELECT powersync_clear_diagnostics('buckets');
SELECT powersync_clear_diagnostics('tables');

[simolus3]

I like this idea 👍 Also considering that we have plans to deprecate and to eventually remove the non-Rust clients, we probably don't have another option.

From a quick look, the diagnostics interceptor is essentially does the following:

1. Constructing a dynamic schema based on data seen in the client. The diagnostic app stores this in local storage.
2. More detailed download stats for buckets. Some of these fields (total_operations, last_op, downloaded_operators) are already part of ps_buckets for progress tracking, but not exposed to SDKs. Others (downloading corresponding to has_more and download_size) are not currently tracked.

Given that download_size is not particularly expensive to track, I wonder if we should just add that column to ps_buckets and update it in the sync client.
I don't think has_more needs to be persisted in the database since it only reflects the current sync operation. I'm not a fan of exposing that on an SDK-level though, since a bucket being downloaded doesn't mean one could access its data (we need to wait for a checkpoint first).

So we will need some diagnostic-specific interface. I'm not entirely sold on making the core extension write those stats into tables because some state (like the inferred schema or downloading) is ephermal. Maybe the diagnostic client could create a temporary table manually and give the extension an SQL string to execute for those writes though? So we'd kind of have a "raw table" for metadata :D

To enable or disable diagnostics, I think passing a flag to the powersync_control('START') call might be cleaner in the core extension but it would require effort to expose this from our SDKs.