The hardest part was the real-time functionality. I ended up writing an elixir server that listens to Postgres’ built-in replication stream, converts it into JSON, then sends it over websockets.
Edit: also, Debezium might be a good backend for this, that way you would be able to support other databases than Postgres.
The issue with NOTIFY is that it has an 8000 byte limit. Some of our updates are large (JSONB columns) so we need more than 8000 bytes, or we would have to notify just the row identifier, then fetch the data (inefficient).
Supabase hooks in to Postgres' replication stream so there is no issue. It converts the streaming bytes to JSON.
One other bonus - you don't have to set a trigger on every table. Just run `CREATE PUBLICATION supabase_realtime ON ALL TABLES`, then you're good to go.
Disclaimer: Debezium lead here