Local changes reverted when a replication delay is present

[rkistner]

To reproduce:

1. Stop the replication service, still running the API service.
2. Make changes on the client and upload.
3. Reconnect the client (restarting the app works for this).

This causes the local changes to be reverted. If you then restart the replication service and complete a sync, the changes appear again.

This is exactly the issue that write checkpoints is designed to prevent. The issue was reduced in #27, which changed the pseudo "$local" bucket to use pending_delete = 0 instead of 1. Client SDKs typically have this query to get current buckets: SELECT name as bucket, cast(last_op as TEXT) as op_id FROM ps_buckets WHERE pending_delete = 0. This change caused that query to include the "$local" bucket, which then causes it to remove removed when connecting, and reverting the local changes.

The issue is only triggered when the connects to the sync service, but there is a replication delay. It is therefore rare to see in practice without simulating a replication delay.

While the issue was introduced in this repo, the simplest fix is in the individual SDKs, by filtering out the $local bucket in the query mentioned above. A future version of powersync-sqlite-core will better abstract away the internals, which will avoid issues like this.

See test cases added in the Dart SDK here: powersync-ja/powersync.dart@5aa9919