Notifications

[aumetra]

Notifications

[aumetra]

Yeah, that's true. I just wanted to write that down somewhere.

If you want to, you can go ahead with an MVP with the first table design you proposed, the externally tagged foreign key.

[zeerooth]

I've been looking into notifications for a bit and I think the best approach would be to create a single table with notifications:

[Notifications]
id: uuid
account_id: uuid
activity_id: uuid
activity_type: Activity (mention, status, reblog, follow etc.)
created_at: Timestamp

which is basically how mastodon does it.

Then we also need to have new columns for the Account with notification preferences:

[Account]
...
notify_on_follow: bool
notify_on_follow_request: bool
notify_on_reblog: bool
....

The actual writing to the notifications table would happen in the services and activitypub handlers, only for account_ids that have the proper preferences set.

Of course using notifications/:id/dismiss would remove the notification, but I think it would also be wise to clear notifications after N days by using a worker (this would require adding periodical workers?), but that can also be postponed for later.

I think it's mostly implementable at this moment and it's required to make full status api compatibility, so I'd like to start working on that.

Do you see any possible problems or things that require some more thought?

[aumetra]

For an MVP, no, I don't really see issues here. I would maybe split the account preferences into an own table though, as to not fill up the main accounts table with too many things.

One issue I'd like to consider before implementing this though is that this model doesn't really allow us to have a neat REFERENCES definition, like all the other tables.

Since we use the same column for likes, follows, reposts, etc. we aren't able to define clear REFERENCES definitions between these tables. That moves some of the mental complexity of cleanups from the database layer, where we originally were just able to run a cascading delete, to the application layer, where we have to consciously keep in mind that we are now, for example, deleting a post and that we need to delete all the notifications belonging to that post.

Not a blocker for an MVP (and I'm right now not even sure how we could actually resolve this issue), just something to keep in mind.

[zeerooth]

You're right, unfortunately this sounds an awful lot like polymorphic associations and there's really not a clear way to implement that in a typical SQL database without moving the validation to the code, like some frameworks do (most notably ruby on rails). A different approach to work around this that I'd lean more into is having a nullable column for each possible reference, like so:

[Notifications]
id: uuid
*account_id: nullable<uuid>
*post_id: nullable<uuid>
*follow_id: nullable<uuid>
-- when adding a new model just add a new column --
activity_type: Activity (mention, status, reblog, follow etc.)
created_at: Timestamp

Then just add a custom check using num_nonnulls that makes sure one of these columns is actually not a null and it should be pretty sound, we can add references as normal.

Of course there is still a concern that, let's say a follow request notification requires a follow_id, but (for some reason) we only write post_id to the database, which would make the data invalid, but hey, that could also happen in a table with a single activity_id field and in the end I don't think there's a way around that particular issue. Invalid notifications could always be discarded in the worst case scenario.