Updating JSONB column ignores removed properties

[coolayatuvxtech]

Describe the bug
When mutating or updating a jsonb column of an existing record, supabase-cache-helpers incorrectly ignores undefined properties within the updated JSON object. This leads to the cached client-side data (e.g., in a React Native/Expo app) not reflecting the actual database state, even after a successful database update. The documentation states that the library currently only supports updating the entire JSON object, implying that undefined properties should effectively remove those keys from the jsonb column, but this is not the observed behavior.

To Reproduce

1. Assume an existing Supabase record with a jsonb column (e.g., additional_attributes) having a value like {a: 'foo', b: 'bar'}.

2. Use supabase-cache-helpers to update this record, attempting to remove a property by setting it to undefined within the jsonb object. For example, updating additional_attributes to {a: 'foo', b: undefined} or {a: 'foo'} (where b is implicitly undefined).

• Example code snippet (replace with your actual update logic):

// Assuming 'mutate' is a function from supabase-cache-helpers
mutate({ additional_attributes: { a: 'foo' } });
// Or if 'b' is explicitly undefined:
// mutate({ additional_attributes: { a: 'foo', b: undefined } });

3. Observe the local client-side cache (e.g., in an Expo React app) for the updated record.
4. Notice that the b property still exists in the client-side cached data (i.e., additional_attributes remains {a: 'foo', b: 'bar'}).
5. Verify the database directly (e.g., via Supabase Console). The b property will correctly be removed (i.e., additional_attributes will be {a: 'foo'}).
6. Refresh the client-side application (e.g., hot reload in browser, or closing and reopening the app).
7. The client-side cache will now correctly reflect the database state (additional_attributes as {a: 'foo'}).

Expected behavior
When a jsonb column is updated with a new JSON object via supabase-cache-helpers, any properties that are undefined (or implicitly absent if the entire object is replaced and a property is missing) in the new object should be removed from the jsonb column in the database and, critically, this change should be immediately reflected in the client-side cache. The client-side cache should not retain old, removed properties.

Additional context
The issue specifically seems to be with how supabase-cache-helpers processes the updated jsonb object before invalidating and re-fetching/updating the local cache. The database update itself works as expected, confirming that Supabase's jsonb handling is correct. The problem lies in the cache helper's synchronization of this change with the client-side data. This impacts user experience as the UI can display stale data until a full app refresh.

Using:

    "expo": "~53.0.9",
    "@supabase-cache-helpers/postgrest-swr": "^2.0.1",
    "@supabase/supabase-js": "^2.49.8",

[psteinroe]

hey, thanks for the detailed report!

I am not really sure how this can be fixed yet. The "issue" is that cache helpers uses merge-anything to merge old with new item during a mutation. For now, you can overwrite that by passing a custom merge function to the mutation.

Will think about a better solution though. Maybe I can derive whether or not this property is a json column (vs a joined relation) from the paths of the query, and overwrite the object instead of merge it.