Flutter stream not working with filter that is more complex than a column name?

[mrspence]

Describe the bug Flutter stream not working with filter that is more complex than a column name?

To Reproduce

1. Create a database column with a JSONB file type

   CREATE TABLE chats (
   id UUID PRIMARY KEY,
   users JSONB,
   );

2. Create a stream that listens to that table and applies an eq filter against it:

   var stream = Supabase.instance.client
       .from("chats")
       .stream(primaryKey: ["id"])
       .eq('user_ids->>${Supabase.instance.client.auth.currentUser!.id}',
           'true')
       .order("created_at");
   
   var subscription = stream.listen((event) {
     var chats = event.map((chatJson) => Chat.fromJson(chatJson)).toList();
     print(chats);
   });

3. When running, you'll see that print(chats) only gets called once successfully and any changes made to the database does not work. Commenting out the eq statement works, indicating that the problem lies within the websocket filtering.

Upon investigating further, I can see that the websockets does also feature an error message that polls up repeatedly:

{
    "ref": null,
    "event": "system",
    "payload": {
        "message": "{:error, \"Unable to subscribe to changes with given parameters. An exception happened so please check your connect parameters: [event: INSERT, filter: user_ids->>729f8372-3174-48b3-a46a-53a4c36c3c45=eq.true, schema: public, table: chats]. Exception: ERROR P0001 (raise_exception) invalid column for filter user_ids->>729f8372-3174-48b3-a46a-53a4c36c3c45\"}",
        "status": "error",
        "extension": "postgres_changes",
        "channel": "public:chats:1"
    },
    "topic": "realtime:public:chats:1"
}

Expected behavior Changes to rows that match that eq filter should successfully sync, without any errors.

Version (please complete the following information):

├── supabase_auth_ui 0.4.3
│   └── supabase_flutter...
├── supabase_flutter 2.5.6
│   ├── supabase 2.2.2
│   │   ├── functions_client 2.2.0
│   │   ├── gotrue 2.8.1
│   │   ├── postgrest 2.1.2
│   │   ├── realtime_client 2.1.0
│   │   ├── storage_client 2.0.2

Additional context Currently trying to use a JSONB column map so I can track realtime changes to chats/messages that are linked to the authorized user. Of course I discovered that relations cannot be used, which has led me here. Open to other ideas and thank you for your time on this!

[dshukertjr]

This is a limitation on the Realtime engine of Supabase.

Of course I discovered that relations cannot be used, which has led me here.

You should not change your database structure to work around the limitations of the tools that you are working with. With proper RLS in place, your users will not be able to read those messages that they are not allowed to read anyway, so you shouldn't need to add the filter you are trying to add anyway.

[mrspence]

Very good point about RLS! Thank you