# Realtime User Store
This started as just a bug/feature (opens new window) Supabase needs to implement. Now it's turning into a challenge (opens new window).
# Problem
The problem is this: how can we implement a live-updating user store (like MobX) that responds to changes in a database.
I originally got this idea for GraphQL Live Queries (opens new window), and I'm exploring options to combat performance concerns (opens new window).
# Option
Imagine a user visited your website, and you fetched all their data:
const data =
supabase
.from('users')
.eq('id', '10')
select(`
id, name,
organizations (
id, name,
projects (
id, name
)
)
`)
Great! Now they have everything:
let store = {
id: 'user1',
name: 'Paul',
organizations: [
{
id: 'org1',
name: 'Org 1',
projects: [ { id: 'proj1', name: 'Project1' } ]
}
{
id: 'org2',
name: 'Org 2',
projects: [ { id: 'proj2', name: 'Project 2' } ]
}
]
}
We could wrap a whole app
in this store so that it's accessible anywhere - like Redux/MobX.
But now what if their data changes? What if someone on another browser updates the name
of org1
? Then they have stale data. We would need to refetch the data. Or ... perhaps they could have a live graph, that is always refreshed when changes occur?
Perhaps we could change the select
to a subscribe
:
// when a customer subscribes to a graph
const sub =
supabase
.from('users')
.eq('id', '10')
subscribe(`
id, name,
organizations (
id, name,
projects (
id, name
)
)
`);
On first fetch, the data comes from PostgREST. This handles all the the complicated work: deep nesting, auth etc. We receive back the same store of data:
let store = {
id: 'user1',
name: 'Paul',
organizations: [
{
id: 'org1',
name: 'Org 1',
projects: [ { id: 'proj1', name: 'Project1' } ]
}
{
id: 'org2',
name: 'Org 2',
projects: [ { id: 'proj2', name: 'Project 2' } ]
}
]
}
If we normalized this into a map, it would look something like this:
let normalized = {
users: {
'1': {
id: 1,
name: 'Paul',
}
},
organizations: {
'org1': {
id: 'org1',
name: 'Org 1',
user_id: 1
},
'org2': {
id: 'org2',
name: 'Org 2',
user_id: 2
},
},
projects: {
'proj1': {
id: 'proj1',
name: 'Project 1',
organization_id: 1
},
'proj2': {
id: 'proj2',
name: 'Project 2',
organization_id: 2
},
}
}
From here we can deduce that the user should have the following subscriptions:
subscriptions = [
"public:users:id.eq.user1",
"public:organizations:id.eq.org1",
"public:projects:id.eq.proj1",
"public:projects:id.eq.proj2",
// FK relationships
"public:organizations:user_id.eq.user1",
"public:projects:organization_id.eq.org1",
"public:projects:organization_id.eq.org2",
];
We need to add the foreign key relations here too (more on that later).
Now, we need to keep track of these rules, so we need some middleware. The middleware would handle this subscription, and all other subscriptions. Here is an example of how it would look with 2 subscribers foo-foo
and bar-bar
(where both users belong to org1
):
subscriptions = [
"public:users:id.eq.user1": [ 'foo-foo' ]
"public:organizations:id.eq.org1": [ 'foo-foo', 'bar-bar' ],
"public:projects:id.eq.proj1": [ 'foo-foo', 'bar-bar' ],
"public:projects:id.eq.proj2": [ 'foo-foo' ],
// FK relationships
"public:organizations:user_id.eq.user1": [ 'foo-foo' ],
"public:organizations:user_id.eq.user2": [ 'bar-bar' ],
"public:projects:organization_id.eq.org1": [ 'foo-foo', 'bar-bar' ],
"public:projects:organization_id.eq.org2": [ 'foo-foo' ],
];
It looks like a simple KV store, so could be something fast like Redis. When a new subscriber connects, we add the rules to Redis, when they disconnect we remove the rules.
# When a new project is inserted
Now let's assume someone creates a new Project, which belongs to org2
:
const event = {
type: 'INSERT',
schema: 'public',
table: 'projects',
new: {
id: 'proj3',
name: 'Project 3',
organization_id: 'org2',
}
}
Because it's an insert, we go through the FK rules looking for any matching criteria, and we come up with this rule:
"public:projects:organization_id.eq.org2": [ 'foo-foo' ]
So we send the event to the 'foo-foo'
subscriber, and we update the rules:
subscriptions = [
"public:users:id.eq.user1": [ 'foo-foo' ]
"public:organizations:id.eq.org1": [ 'foo-foo', 'bar-bar' ],
"public:projects:id.eq.proj1": [ 'foo-foo', 'bar-bar' ],
"public:projects:id.eq.proj2": [ 'foo-foo' ],
"public:projects:id.eq.proj3": [ 'foo-foo' ], // New rule
// FK relationships
"public:organizations:user_id.eq.user1": [ 'foo-foo' ],
"public:organizations:user_id.eq.user2": [ 'bar-bar' ],
"public:projects:organization_id.eq.org1": [ 'foo-foo', 'bar-bar' ],
"public:projects:organization_id.eq.org2": [ 'foo-foo' ],
];
# When an existing organization is updated
Now let's assume that there is an event which happens: the name
of org1
is updated to Organization 1
. An event like this is generated:
const event = {
type: 'UPDATE',
schema: 'public',
table: 'organization',
new: {
id: 'org1',
name: 'Org 1',
}
}
Because it's an update, we go through the PK rules looking for any matching criteria, and we come up with this rule:
"public:organizations:id.eq.org1": [ 'foo-foo', 'bar-bar' ]
So we send the event to both subscribers: 'foo-foo'
and 'bar-bar'
. No update to the rules are needed!
# Challenges
Column names
In the real world, people don't always use column names like this. We would need some metadata about the columns. We receive column metadata in the WAL
stream, but I need to confirm that it has the correct details. If it doesn't, we would need to build this schema/graph upon connecting to the database (by introspecting the schema).
Many to many joins
Notice that the organizations
has a foreign key
of user_id
. This is actually false. There is a many to many join between these two tables. The foreign key for both goes through a members
table, so really we need a subscription to that table so that we know when a user is added / removed to an organization. How do we do this?
# Potential resources
- Noria (opens new window): Rust library for dynamic, partially-stateful dataflow
- Noria whitepaper (opens new window)
- Materialize (opens new window) - these guys have built a whole business around this (very popular (opens new window) on HN) - although they do a lot more than this.
- Incremental View Maintenance (opens new window): someone is developing a patch for postgres that implements incrementally updating/materializing views, although there are no
WAL
events for materialized views