Skip to main content

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.