I'm having a hard time modeling an activity feed that should show events based on user roles. I'm using Postgresql 9.4.

I have a projects table. Each project has many users through a memberships table. The memberships table has a role column that stores a user's role in a given project. Standard stuff.

| id | user_id | project_id | role  |
|  1 |       1 |          5 | admin |
|  2 |       2 |          5 | user  |

I have a buckets, uploads, and documents tables. A bucket is essentially a folder, and it has many uploads, and an upload has many documents. A bucket has many documents through the uploads table.

| id | name  | public |
|  1 | Stuff | true   |

I need to track activities on memberships, buckets, uploads, and documents. This is fairly easy. I'm planning to use a polymorphic association. In an activities table I'll have these columns.

| id | user_id | project_id | action | trackable_id | trackable_type |
|  1 |       1 |          5 | create |            3 | Upload         |

Trackable id and type store the record id and table name of the trackable object, in this case an upload. This lets me easily retrieve, in a single query, a user's activities, and a project's activities, and render view templates based on the trackable object type, and the action type.


My problems start with buckets. Any activity on private buckets should only be visible to admins. This includes activity on descendents of private buckets: uploads, and documents that belong to those uploads.

Solution 1

The easiest way I can think of is to add a private column to the activities table. The lets me do something like this (this is Ruby pseudo-code, specifically Rails, but logic stands):

project = Project.find(1)
activities = if project.admins.include?(current_user)

This is not scalable though. If we add a table to track access to folders, and change permissions so that they go from:

Admins see all activity, users see public bucket activity, activity on their uploads and documents that belong to public buckets, and membership activity.

To something like

Admins see all activity, users see public bucket activity like above, and in addition they see activity that belongs to private buckets they can access.

Then the private column becomes meaningless in the activities table.

Solution 2

Another way is to add a bucket_id column to the activities table, and separate activities by ones that belong to projects or buckets.

| id | user_id | project_id | bucket_id | ...
|  1 |       1 |          5 |         3 | ...

For non admin users I can then run two queries. One for project activities, and one for bucket activities that are either public, or in the future, on buckets that the users have access to.

I don't like this approach because it's also not scalable. If we add a tasks table where tasks can also be restricted by access, we would have to add another column to the activities table, task_id, and so on.

Approach 3

Create a table for each activity type. Much like approach 2, but with normalized data. I still don't feel comfortable creating what essentially is three different tables with almost identical data.

Any ideas on the best way to approach this problem?

  • You are saying that "buckets are private". So why do not simply add a column private to the table buckets? – Renzo Jul 2 '15 at 4:37
  • @Renzo buckets my be private or public. If a bucket is private, I can't show its activity in the feed unless the user is a member of said bucket, or an admin. I think the solution is to create a many-to-many table between users and activities. After the creation of each activity, a background job should go and create a mapping in that table. That way displaying the feed for a current user in a current project easy. If the user has permission, it a record should be created for him or her. – Mohamad Jul 2 '15 at 14:19
  • You'd need a column with Tinyint length = 1, allowing 0/1 = off/on i.e. Private = false/true. – John Smith Sep 1 '15 at 10:59

An activity feed simple generates a log of activities with the most recent first. There are a few ways one can implement an activity feed for a user. E.g. John Smith's answer is one approach. Everything depends on support you want to cover.

  • 3
    Could you be more specific? Apart from the mentioned answer, what other solutions are worth mentioning? – dezso Sep 1 '15 at 10:59
  • 1
    Using Ruby on Rails, this is very popular these days to use, specially for activity feeds, see: sitepoint.com/activity-feeds-rails – Michael Johnson Sep 1 '15 at 11:02
  • @dezso, was that something good for you. – Michael Johnson Sep 1 '15 at 11:05
  • 1
    @MichaelJohnson I read almost every single Google result on the first few pages on this topic. The first issue is nearly all approaches use a gem, and they don't account for privacy. Yes, activity feeds on the surface seem simple, but the moment you try to scope access and deal with aggregates it quickly becomes a very, very complex issue. – Mohamad Sep 1 '15 at 12:15
  • You need to use the third table for controlling the privacy. Or a 2 table approach with table stream + following. This way you can write a query to select the stuff from the people/pages you follow. And then you can also add a "privacy" column or table. Michael Johnson is correct about some of the stuff he's written. Also see my query. – John Smith Sep 1 '15 at 12:18

By having three tables in the database (e.g. activities, user, and user_activities) and a simple LEFT JOIN SQL query as the one below can be executed:

SELECT SELECT user.name AS user_name , activities.name AS activity_name
FROM user_activities 
LEFT JOIN user ON user.userid = user_activities.user_id 
LEFT JOIN activities ON activities.activityid = user_activities.activity_id

As for your approach:

Activities table,

id - Unique Stream Item ID
user_id - ID of the user who created the stream item
object_id - Internal ID of the object (currently either the buckets, uploads, or documents ID)
action_name - The action taken against the object
activity_date - Timestamp that the action was created.
hidden - Boolean of if the user has chosen to hide the item.
privacy - An id to a table with Privacy Groups Options
  • 1
    I don't think you read my question carefully enough. – Mohamad Sep 1 '15 at 12:18
  • I updated my answer. – John Smith Sep 1 '15 at 12:24

Your Answer

