Spec a live backfill of a denormalized column

Your users table in Postgres has 50 million rows, and the admin console wants to sort users by when they were last active. Today that means scanning a 2-billion-row events table. You are adding a last_active_at column to users, filling it for old rows and keeping it current as events arrive. An agent will write each step and run it on production only after you approve.

Write the spec you would hand the agent, including where it stops and waits for you.

What you know:

• Postgres 15 with 2 read replicas, and reads must stay within 5 seconds of the primary
• events(user_id, created_at, ...) has an index on (user_id, created_at)
• events arrive at about 3,000 a second, written by 4 different services
• the system is read-heavy and cannot take downtime

Then, in a few sentences: what does an agent left to itself get wrong on a job like this?