How to store ‘last seen’ for users in Phoenix

This week, I worked on some under-the-hood improvements to Plausible to give me better insights into my userbase. One of these was to store a last_seen timestamp for all users. This is a private piece of data that I use to determine:

Let’s see how this can be achieved in Phoenix. First, we’ll start with the schema.

Schema #

We’ll need to generate a migration for the last_seen column:

$ mix ecto.gen.migration add_last_seen_to_users

and the migration itself:

defmodule Plausible.Repo.Migrations.AddLastSeenToUsers do
  use Ecto.Migration

  def change do
    alter table(:users) do
      add :last_seen, :naive_datetime, default: fragment("now()")

At this point, you’ll want to add the field to your User Ecto schema as well.

Plug #

Now I want to update this field every time use uses the website. A natural solution here is to add a Plug that intercepts every request. However, updating the database on every request seems fairly wasteful. Besides, for my purposes, the timestamp doesn’t need to be accurate to the millisecond. This is a perfect case for throttling, which can be achieved by using the browser session.

defmodule PlausibleWeb.LastSeenPlug do
  import Plug.Conn
  use Plausible.Repo

  @one_hour 60 * 60

  def init(opts) do

  def call(conn, _opts) do
    last_seen = get_session(conn, :last_seen)
    user = conn.assigns[:current_user]

    cond do
      user && last_seen && last_seen < (unix_now() - @one_hour) ->
        put_session(conn, :last_seen, unix_now())
      user && !last_seen ->
        put_session(conn, :last_seen, unix_now())
      true ->

  defp persist_last_seen(user) do
    q = from(u in Plausible.Auth.User, where: == ^

    Repo.update_all(q, [set: [last_seen: DateTime.utc_now()]])

  defp unix_now do
    DateTime.utc_now() |> DateTime.to_unix

I added this plug to my browser pipeline in my router. Note that I have another plug running before this one which looks up the current user and makes conn.assigns[:current_user] available.

This approach satisfies every requirement I had for this feature:

This blog documents the journey of building Plausible
Get notified of new posts →


Now read this

Learning design as a developer

Working on I often find myself doing things I have no clue how to do. I struggle massively with marketing, copywriting, and most of all, UX/UI design. Here are some tips I’ve found helpful when trying to design for the web... Continue →