Data Sync EventsV9 Remote Database Delete Should Invoke remote

[williscool]

Not just a DELETE FROM but a

TRUNCATE TABLE public.eventsV9 CASCADE;

[williscool]

Okay so it turns out sequel lite doesn't support truncate

https://www.perplexity.ai/search/does-sqlite-have-truncate-XNQBT3zuTWOAn0JLUY1Bwg

Look at how power sync handles. Todd, if they have anything in their documentation. If not, we can just delete from and update the sequence count I guess

[williscool]

So after some digging my intial diagnosis of this was wrong. The problem was currently in the powersync supabase connector lib/powersync/Connector.ts

we have NO error handling in uploadData so if encounter ANY errors the entire transaction (in this case we a trying to delete the whole eventsV9 table) fails.

Also the operation count can get out of whack on the mobile device (because we are not handling the errors in the transaction at all)

The real long term solution is the proerply handle errors

i.e. connections droping (or putting in the wrong supabase credentials)

the short term solution is to move the DELETE FROM from powersync on the mobile device to instead calling supabase directly to do the delete from and letting powersync do the delete sync from the server.

This way we don't have to worry about all the delete's not happening (if we aren't connected to supabase right the delete call just never happens)

[williscool]

From an old comment from the code when I though we needed the truncate before I figured out/ remember the real issue. That we just want to let the serverside do the delete and let powersync propagate it back to mobile

  // so sqlite doesn't support TRUNCATE
  // and even if it did that isn't supported by powersync's protocol
  // https://docs.powersync.com/architecture/powersync-protocol
  // https://docs.powersync.com/architecture/client-architecture
  // so I think we can just use the supabase client to TRUNCATE the table
  // and still DELETE FROM to clear local
  // honestly the sync server should just handle from the truncate

[williscool]

So after some digging my intial diagnosis of this was wrong. The problem was currently in the powersync supabase connector lib/powersync/Connector.ts

we have NO error handling in uploadData so if encounter ANY errors the entire transaction (in this case we a trying to delete the whole eventsV9 table) fails.

Also the operation count can get out of whack on the mobile device (because we are not handling the errors in the transaction at all)

The real long term solution is the proerply handle errors

i.e. connections droping (or putting in the wrong supabase credentials)

the short term solution is to move the DELETE FROM from powersync on the mobile device to instead calling supabase directly to do the delete from and letting powersync do the delete sync from the server.

This way we don't have to worry about all the delete's not happening (if we aren't connected to supabase right the delete call just never happens)

after trying the above in #88 and it not being better from a syncing perspective (still throws a bunch of errors and gets out of sync until you exit and reenter the react native part of the app)

after trying this it doesn't seem to solve the issue. think im gonna close it in favor of actually fixing lib/powersync/Connector.ts uploadData