We started with this implementation, however there are 2 issues with this approach. 1) NOTIFY has a limit of 8000 bytes, so large rows are silently dropped. 2) you need to attach the notify trigger to every table (vs a Publication, which can listen to an entire database in a single line of code).
> why WS was used
The Server is Elixir (Erlang) so it's very good at WS. Over time we will add other multiplayer useful features (like presence)
Thanks for the response! I have a few more questions if you have the time:
If I'm reading the article correctly this feature is only available for authenticated users? Is that restriction based on scaling or some other limitation?
When I looked at the example query in the article it only queries the primary key, does this also work for tables where the GRANT hides certain columns from different users?
> does this also work for tables where the GRANT hides certain columns from different users?
Yes, it does! Any columns that are not selectable by the user's role are hidden from the JSON `response` and `old_response` keys and are not allowed for user defined filtered.
> this feature is only available for authenticated users
Every client is expected to provide an API key at a minimum. The API key is actually a long-lived "anon" JWT, so unauthenticated users can connect and listen to "public" changes (for example, you might want to add a table of stock prices which update every second)
> only queries the primary key
That's correct. We use PostgreSQL logical replication to capture changes, which requires PK's. This is expected for replication because otherwise there would be no way of definitely identifying an update/delete. IMO it's a good practice to add a PK to every table anyway (a simple "serial" will suffice), especially for auditing and historical analysis
Inside a supabase project users can connect to their projects as either "anon" or "authenticated". The role information is passed inside a JWT.
I'll need to confirm with Oli (asleep right now) on the technical implementation but I believe either of these roles is assumed when running the RLS checks.
We started with this implementation, however there are 2 issues with this approach. 1) NOTIFY has a limit of 8000 bytes, so large rows are silently dropped. 2) you need to attach the notify trigger to every table (vs a Publication, which can listen to an entire database in a single line of code).
> why WS was used
The Server is Elixir (Erlang) so it's very good at WS. Over time we will add other multiplayer useful features (like presence)