Postgresql – How to model an activity feed

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.

memberships
-------------------------------------
| 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.

buckets
-----------------------
| 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.

activities
----------------------------------------------------------------------
| 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.

Problem

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)
  project.activities.all
else
  project.activities.public
end

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.

activities
---------------------------------------------
| 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?