Triggers + OPFS can miss updates #787
Description
Overview
The triggers and High Performance Diffs functionality can produce unexpected results if used with multiple tabs and OPFS.
Our triggers implementation currently creates a temporary SQLite trigger and destination table to store the operations. These temporary items are scoped to the SQLite connection which they were created on.
Our OPFS implementation creates a unique SQLite connection per tab. The triggers created by a tab operate on the tab's SQLite connection. Changes made by connections do not trigger triggers on other connections. This causes tabs to miss operations caused by other tabs.
Note: The IndexedDB VFS implementation does not suffer from this since a single SQLite connection is shared between all tabs. Each tab has their own SQLite trigger and destination table for ops. Every change to a table will trigger all triggers.
This is the root cause of TanStack/db#948