Middleware-based encryption: decrypt on sync, encrypt on upload (alternative to raw tables)

[raivieiraadriano92]

Middleware-based encryption: decrypt on sync, encrypt on upload (alternative to raw tables)

We're exploring a middleware-based encryption approach as an alternative to PowerSync's raw tables for E2E encryption. We'd like feedback on whether this fits PowerSync's design and constraints.

Context

PowerSync's E2E encryption with raw tables works, but adds significant complexity (mirror tables, manual migrations, etc.). We're looking for a simpler way to encrypt sensitive columns while keeping normal queries on the local DB.

Approach

We extend PowerSync and override two methods:

1. generateBucketStorageAdapter() – We return our own TransformableBucketStorage instead of SqliteBucketStorage. This adapter is the one that receives sync data from the Rust client.
2. control() – We override control() in our storage adapter to intercept PROCESS_TEXT_LINE sync data. Before passing the payload to the WASM engine, we run a middleware pipeline that can transform the data (e.g. decrypt sensitive columns).

Flow

• Download: Server → adapter.control(PROCESS_TEXT_LINE, payload) → our override parses the payload, runs middleware (decrypt), then calls super.control() with the transformed payload → WASM writes to SQLite.
• Upload: In our connector's uploadData(), we encrypt sensitive columns before sending to the backend.

Benefits

• No raw tables or mirror tables.
• No custom migrations for encrypted columns.
• Local queries stay simple; data is decrypted before it hits SQLite.
• We only need enableMultiTabs and useWebWorker set to false so our adapter is used.

Questions

• Does this pattern conflict with any PowerSync principles or assumptions?
• Are there known pitfalls (e.g. checksums, conflict resolution, sync invariants) we should be aware of?
• Would PowerSync consider supporting this kind of middleware/transformation hook in the official SDK?

---

Additional question: enableMultiTabs / useWebWorker and custom bucket storage

Right now we need enableMultiTabs and useWebWorker set to false because when they're true, PowerSync uses a SharedWorker (SharedSyncImplementation.worker.js) that creates its own SqliteBucketStorage directly (see SharedSyncImplementation.ts around line 432: adapter: new SqliteBucketStorage(this.distributedDB!, this.logger)). That path never uses our generateBucketStorageAdapter() override, so our middleware never runs.

How we're thinking about overriding it

1. Custom worker script – PowerSync already supports sync.worker (path or factory) to override the default SharedWorker. We could ship our own worker that extends or wraps SharedSyncImplementation and swaps SqliteBucketStorage for our TransformableBucketStorage when creating the adapter.

2. Main blocker – The worker runs in a separate context and doesn't receive our PowerSyncDatabase instance. It has no access to generateBucketStorageAdapter() or our transformers config. We'd need a way to pass a bucket storage factory (or equivalent) into the worker so it can create our adapter instead of SqliteBucketStorage.

3. Proposed API – Add something like sync.bucketStorageFactory?: (db: DBAdapter, logger?: ILogger) => BucketStorageAdapter (or a similar hook). When set, the SharedWorker would use this factory instead of new SqliteBucketStorage(...). That would let us use enableMultiTabs and useWebWorker while still running our middleware.

Question: Would PowerSync consider a PR that adds a bucketStorageFactory (or similar) option so custom bucket storage adapters can be used with the SharedWorker path? If so, what shape would you prefer for that API?

---

We're happy to share more implementation details or diagrams if that would help.

[simolus3]

Thanks for starting this discussion! I think this is an interesting use case. First, to answer your questions:

Does this pattern conflict with any PowerSync principles or assumptions?

While your approach isn't broken fundamentally (it will work with the existing protocol), we treat the sync protocol and the functionality of clients as an internal implementation detail we're free to change without taking these transformations into account. So while the approach definitely works, I'd call it hacky in its current state. You're hinting towards something like this eventually being supported officially, and that's probably a direction worth exploring.

The behavior of uploadData is definitely a fully supported use-case, backend connectors may transform data before uploading it to your backend.

Are there known pitfalls (e.g. checksums, conflict resolution, sync invariants) we should be aware of?

I'm not aware of any such issues. From the client's perspective, checksums are mostly opaque numbers and we don't try to re-construct them from downloaded data for verification. So this data transformation shouldn't interfere with the sync proces as long as the transferred checksum isn't altered (I believe we mainly need checksums for compact operations and re-deployments, although the latter should likely not need them anymore).

Would PowerSync consider supporting this kind of middleware/transformation hook in the official SDK?

This is an interesting use case, and there are different approaches that come to mind here. For instance, we could potentially have something like the put and delete triggers for raw tables except that they could use SQL to transform data on JSON tables (e.g. something like transform: "json_replace(source_data, '$.sensitive_column', my_decryption_function(source_data ->> '$.sensitive_column'))" on the table definition).

Another option perhaps worth exploring could be to:

1. Use a local table to store decrypted contents.
2. Use triggers to keep local tables in sync with encrypted data transferred through PowerSync.

This is kind of similar to raw tables, but you wouldn't have to manage the entire schema (triggers are stateless, you could create them after opening the PowerSync database and migrate them by dropping + re-creating them).

Custom worker script

I like this idea FWIW. We already support something similar for our Node SDK to let users alter database workers. So I could see a named export of @powersync/web you could use to define custom workers that you can then use as a path instead of the default worker. I'm still not super happy about patching the bucket storage implementation since the entire thing should be internal, but it might be a sensible workaround to restore worker support here.

Add something like sync.bucketStorageFactory?: (db: DBAdapter, logger?: ILogger) => BucketStorageAdapter (or a similar hook). When set, the SharedWorker would use this factory instead of new SqliteBucketStorage(...). That would let us use enableMultiTabs and useWebWorker while still running our middleware.

This is not that easy to implement since we can't send BucketStorageAdapters to the remote context, we'd have to proxy it with comlink. That could probably work, but I'm a bit worried about a potential performance impact. We'd definitely have to check that very carefully.