Visit History

Problem

Maintain a more detailed history of users’ visits to threads. This can be used to:

1. Display a “visited threads history” page for users.
2. Enable users to see “new post/comment” status for previous visits to a thread (in case of accidental refresh, for example).

Current Status

Visit to threads are stored in the user_thread_last_visits table. It has the following schema:

CREATE TABLE IF NOT EXISTS user_thread_last_visits(
    user_id BIGINT REFERENCES users(id) ON DELETE RESTRICT NOT NULL,
    thread_id BIGINT REFERENCES threads(id) ON DELETE RESTRICT NOT NULL,
    last_visit_time timestamp NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX user_thread_entry on user_thread_last_visits(user_id, thread_id);

Visits are registered through the /threads/[thread_id]/visit endpoint, and the database update query is defined in server/threads/sql/visit-thread-by-string-id.sql.

Changes

The main issue is the UNIQUE INDEX in the user_thread_last_visits table, which prevents storing multiple visits in the same table. I would suggest dropping the index, but the last_visit_time column would still be a misnomer, and many queries would need to be updated to change it.

Instead, I propose a completely new user_thread_visits table. This would have a similar schema WITHOUT unique constraints and with a more reasonable name for the last_visit_time column (likely visit_time). We’d need to create a (user_id, thread_id, visit_time) index to make queries for the latest visit time faster.

Purposely leaving the queries as an exercise :)

Implementation plan

1. Create the user_thread_visits table.
2. Populate user_thread_visits when /threads/[thread_id]/visit endpoint is called.
3. DO THE MIGRATION STEPS
4. Figure out which queries are using user_thread_last_visits and switch them to user_thread_visits.
   • Note: this includes updating the thread_notification_dismissals view, which needs to be done by querying the DB directly (aka, I will be the one doing that).
5. NUKE user_thread_last_visits OUT OF EXISTENCE

Migration steps

1. Deploy the server version writing user_thread_visits in addition to user_thread_last_visits.
2. Dump the user_thread_last_visits entries into user_thread_visits.
3. Go back to step 4 in implementation plan.

We could technically nuke user_thread_last_visits immediately and create a view as a fallback, but there’s only 2-3 queries that rely on it so it’s easier to just do the change all together.

API endpoints

We should leave the definition of API endpoints to access these values for when we’re ready to implement UI that needs these values. I’ve learned my lessons.

If we’re ready, I would suggest a new /users/@me/history endpoint for visits history, as well as augmenting threads/[thread_id]/ to return details about last visits if we want to do fancy notification views.

Notes

The visit endpoint uses the GET HTTP method. This is incorrect (it should be POST). Updating it would be an easy extension to this project.