Log user logins and reconstruct last activity from logs

4 files changed, 63 insertions, 0 deletions

diff --git a/app/controllers/otp_challenges_controller.rb b/app/controllers/otp_challenges_controller.rb
index 2526d1fb..5a834ed0 100644
--- a/app/controllers/otp_challenges_controller.rb
+++ b/app/controllers/otp_challenges_controller.rb
@@ -28,6 +28,8 @@ class OtpChallengesController < ApplicationController
       reset_session
       self.current_user = user
       session[:logged_in_at] = Time.now.to_i
+      user.update_column(:last_login_at, Time.now)
+
       # an admin who logs in and goes straight to user management
       # is already elevated
       elevate! if user.is_admin?
diff --git a/app/controllers/sessions_controller.rb b/app/controllers/sessions_controller.rb
index bb63c51e..00d19cdd 100644
--- a/app/controllers/sessions_controller.rb
+++ b/app/controllers/sessions_controller.rb
@@ -30,6 +30,7 @@ class SessionsController < ApplicationController
       else
         self.current_user = user
         session[:logged_in_at] = Time.now.to_i
+        user.update_column(:last_login_at, Time.now)
 
         if user.otp_required?
           flash[:error] = t("flash.sessions.otp_setup_now")
diff --git a/db/migrate/20260802213023_add_last_login_to_users.rb b/db/migrate/20260802213023_add_last_login_to_users.rb
new file mode 100644
index 00000000..6fec24a1
--- /dev/null
+++ b/db/migrate/20260802213023_add_last_login_to_users.rb
@@ -0,0 +1,5 @@
+class AddLastLoginToUsers < ActiveRecord::Migration[8.1]
+  def change
+    add_column :users, :last_login_at, :datetime
+  end
+end
diff --git a/lib/tasks/users.rake b/lib/tasks/users.rake
index 4a941e39..ee5da7dc 100644
--- a/lib/tasks/users.rake
+++ b/lib/tasks/users.rake
@@ -22,4 +22,59 @@ namespace :users do
     puts "Cleared the second factor for #{user.login}. " \
          "They can re-enrol under My account."
   end
+
+  desc "Seed last_login_at from whatever the database still remembers. " \
+       "There is no login history, so the column is reconstructed once " \
+       "from the newest trace each account left: log entries, authorship, " \
+       "editing, tagging. Accounts with no trace fall back to their own " \
+       "created_at, which tells an ancient untraceable account apart from " \
+       "one made yesterday. Dry run unless WRITE=1; FORCE=1 is required " \
+       "once any real login has been recorded."
+  task :seed_last_login => :environment do
+    write = ENV["WRITE"] == "1"
+    force = ENV["FORCE"] == "1"
+
+    floor = nil
+    if ENV["FLOOR"].present?
+      floor = Time.zone.parse(ENV["FLOOR"]) or abort "FLOOR must be YYYY-MM-DD"
+    end
+
+    seeded = User.where.not(:last_login_at => nil).count
+    if seeded > 0 && write && !force
+      abort "#{seeded} accounts already carry a last_login_at, which may be " \
+            "a real login. Re-run with FORCE=1 to overwrite them."
+    end
+
+    users = User.order(:login).to_a
+    ids   = users.map(&:id)
+
+    newest = {
+      "log"    => NodeAction.where(:user_id => ids).group(:user_id).maximum(:occurred_at),
+      "author" => Page.where(:user_id => ids).group(:user_id).maximum(:created_at),
+      "editor" => Page.where(:editor_id => ids).group(:editor_id).maximum(:updated_at),
+      "tag"    => ActsAsTaggableOn::Tagging.where(:user_id => ids)
+                    .group(:user_id).maximum(:created_at),
+      "tagger" => ActsAsTaggableOn::Tagging.where(:tagger_type => "User", :tagger_id => ids)
+                    .group(:tagger_id).maximum(:created_at)
+    }
+
+    puts "DRY RUN -- nothing written. Re-run with WRITE=1." unless write
+    puts format("%-18s %-12s %-8s %s", "login", "seeded", "source", "roles")
+
+    users.each do |user|
+      clues        = newest.transform_values { |by_id| by_id[user.id] }.compact
+      source, date = clues.max_by { |_, at| at }
+      source, date = "created", user.created_at if date.nil?
+      source, date = "floor",   floor           if date.nil?
+
+      if date.nil?
+        puts format("%-18s %-12s %-8s %s", user.login, "SKIPPED", "none", user.roles.join(","))
+        next
+      end
+
+      user.update_columns(:last_login_at => date) if write
+      puts format("%-18s %-12s %-8s %s", user.login, date.to_date, source, user.roles.join(","))
+    end
+  end
+
 end