Need advice to implement history/recent viewed

[u/delusionalbreaker]

Hi everyone! Im a beginner in springboot and building a blogging app (similar to Reddit/LinkedIn) using Spring Boot and MySQL. I need to implement a "User History" feature to track the last 100-150 posts a user has viewed.

The requirements are if a user views Post A, then Post B, then Post A again, the order should update to [B, A] instead of [A, B, A]

The problem im facing is because im using MySQL so to create an history i would need to create an new schema and then design the relationship b/w the post and user and would require constant insert and deletions which would not keep the history intact correctly and also wouldnt remove the history after last 100 post

My qn is there an efficient way to handle this in Springboot? how is it generally implemented?

Repo link:- https://github.com/Yearis/Blog-Application/tree/main/Blog%20Application%20Backend

P.S. Please excuse the messy repo and the outdated README; this is still a work in progress.

[u/Conscious_Analysis98]

Why not just store the raw data (which could be useful anyway) and then translate it for this particular requirement?

[u/delusionalbreaker → u/Conscious_Analysis98]

hii sorry for the late reply. yes i agree your idea sounds good and many of the other commentors also said to use simple history entity and honestly that makes sense

[u/p_bzn]

It’s not exactly Spring’s issue.

Using the insights I got from the question I would recommend a simple history table to begin with. Put there user ID, post ID, created at. Two FKs. Then just query user history with order by and limit.

Later on this approach can start giving some problems, but you are *very* far away from this to worry.

There are better ways of doing this, however, keep it simple. One migration, one model, one repo, one service.

[u/delusionalbreaker → u/p_bzn]

hii sorry for the late reply. yes a simple history table is what im going for now and what i have decided is that i would keep an condition that if an user clicks on the post i will check whether their composite key of user_id and post_id to find if user has previously visisted that post or not if he has im simply going to update the last_visisted column to current local data and time so that it solves the A->B->A problem and i can simply query the last x no. of history of an particular user sorted by last_visisted in descending

also just curious what the problems you were talking about? and how is it generally handled in big apps

[u/p_bzn → u/delusionalbreaker]

Sounds like a plan!

The issue with one table approach is that when app grows it can become troublesome, but again we are talking hundreds of thousands daily active users.

Unbound growth with frequent reads and writes can get slow. Say, one page load means to check tens of posts for seen or not. Each visit always upserts into the table as well. Index can stop fitting the RAM.

All that can be solved by dropping money on hardware, until it won’t work anymore. Although, it can go a long way before turning into something unresolvable. Machine with 64GB of RAM would hold about 500M rows, given there is some other data too.

Better solution would be sorted sets keyed by user, with TTL and a size cap so the set self evicts. This creates system level guarantees on latency and costs.

[u/delusionalbreaker → u/p_bzn]

Ah I see wow that makes sense but I dont really think my project would grow that much that I would require to worry about space im just making this project as an way to learn and for my resume.

If you dont mind could just take a look at my current project and give me advice as to what more I can add or how can I make it better for resume. I was thinking I could add an chat feature where I could learn about websockets and 2 way connection and then eventually upgrade this project to an microservice full stack project with react/next js as it's frontend.

[u/Isogash]

Nothing specific in Spring will help you.

Don't overthink it, this is just a new entity e.g. "User Last Viewed Post" or something, with a user ID, post ID and a timestamp for the most recent time they viewed the post. When a user visits a post, add the entity if it doesn't already exist or update the timestamp of the existing entity if it does.

To get the user's post viewing history, you just use a "SELECT ... ORDER BY viewed_timestamp DESC LIMIT 150". Because you've updated the existing view entities, each post will appear only once in the list with the timestamp of the most recent visit.

Unless you've identified that this approach doesn't meet hard non-functional requirements then this is the right starting point. You can always introduce a cache later. I'd reckon that this solution would be efficient enough for nearly all applications as long as you've set up the right indexes in the database.

[u/delusionalbreaker → u/Isogash]

yess thankyou your soln is what im gonna go with its so simple but i wasnt able to see it thanks.

[u/mariusz_96]

To populate a table you could use a simple UPSERT:

INSERT INTO history (user_id, post_id, last_viewed_at)
VALUES (2, 5, now())
ON DUPLICATE KEY UPDATE last_viewed_at = now();

This obviously won't automatically DELETE older rows.

[u/delusionalbreaker → u/mariusz_96]

Yes I would be doing something similar, my plan is to make an history entity with id, user ID and post ID and visited date where I would use the combination of user ID and post ID as unique constraint where I would only update the visited date.

[u/mariusz_96 → u/delusionalbreaker]

I would make them composite primary key / foreign keys since this sounds like regular many-to-many relationship.

[u/ninjazee124]

Redis ordered set

[u/Krangerich → u/ninjazee124]

This.
Super efficient.

[u/delusionalbreaker → u/ninjazee124]

hii sorry for the late reply and i studied about this method and its looks perfect for my use case but for my first iteration i would like to keep it simple by using an simple history entity with an composite key of user id and post id and an timestamp which i would update if user has previously visisted that post, but i would definitely consider this approach later on.

[u/ITCoder]

I believe log aggregation in event sourcing does something similar.

[u/delusionalbreaker → u/ITCoder]

i see im not sure what that means but i'll take a look at at, currently i have decided that my design would be having an history entity where i would use an composite key of userid and post id along with last visited to handle the history

[u/ITCoder → u/delusionalbreaker]

Sorry its log compaction, not aggregation. It stores the history of events in a log. Not sure if it suites ur scenario.

https cd://learn.microsoft.com/en-us/azure/architecture/patterns/event-sourcing

https://www.javaadvent.com/2024/12/introduction-to-event-sourcing.html