Best practices for tracking user interactions in REST API to customize frontend interface

I’m building a system that needs to track what users do so I can customize their interface based on their activity. The main goal is to make the UI adapt to user behavior patterns.

My scenario:
I have a many-to-many relationship between Users and Projects. When a user views their project list, I want to show the most recently accessed projects first.

Current approach I’m considering:
Create an activity tracking table with this structure:

{
  userId: 42,
  entityId: 15,
  actionType: 'click',
  entityType: 'project',
  timestamp: '2022-07-15 14:30',
  // other fields
}

When users request their project list, I would:

  1. Query the activity table for recent project interactions
  2. Sort projects based on this data
  3. Return the customized list

Alternative option:
Add tracking fields directly to the projects table (like lastViewedAt, viewCount, etc.)

My question:
What’s the recommended architecture pattern for this kind of user activity logging? Should I use a separate activity table, modify existing tables, or is there a better approach I’m missing?

Looking for advice on scalability and maintainability. Thanks for any suggestions!

interesting challenge! what’s ur plan for handling data volume as you scale? that activity table will get huge fast. i’d suggest redis for caching recent interactions - store the last 10-20 project views per user, then hit ur main DB for older data. how many concurrent users are u expecting? that’ll make a big difference in ur approach.

You’re on the right track with the separate activity table, but I’d go with event sourcing for better scalability. Store raw activity events and use materialized views - works great for this.

Don’t query the activity table on every request. Instead, keep a denormalized user preferences table that updates asynchronously when activities get logged. Pre-compute rankings and preferences per user in there.

Use background jobs to process activity events and update preference records. This handles high-frequency interactions without killing your API response times, plus you keep the full activity history for analytics.

The trick is separating writes (logging activities) from reads (serving customized data).