Here is a table of events from a fitness app. Pick one feature to build from it and scope it to something you could ship in two weeks.

Reading the table. “Before I pick, let me read it. The grain is one row per event, per user, per device. The two completed runs for 4117 are the same workout with the same timestamp, most likely a phone that retried a sync after being offline, so I’ll ask how the client retries and dedupe on (user_id, device_id, event_type, occurred_at). Every timestamp ends in Z except the one from android-3f, so at least one client writes device-local time. I’ll ask which, and until I know I read a view, events_clean, that dedupes and converts occurred_at to UTC. And 5230 started a strength session that never completed: an abandoned workout or a lost event.”

Options, then a pick. “Three options: a comeback reminder, a weekly summary, or flagging workouts that start and never complete. I’d run one query first: of members who miss their usual weekday, how many complete a workout in the next seven days without help? If that number is low, a reminder has room to work. If it’s high, I’d pick the drop-off flag instead. Assume it’s low. The user is a member who works out on a regular weekday and misses it. The decision is whether to send one push today, and when. It is not a coach, a plan generator or a streak badge.”

Data model. “One derived table, rebuilt nightly from events_clean:”

CREATE TABLE member_habit (
  user_id         BIGINT PRIMARY KEY,
  usual_dow       SMALLINT,     -- weekday with the most completed workouts, last 8 weeks
  usual_hour      SMALLINT,     -- median local start hour on that weekday
  last_completed  TIMESTAMPTZ,
  tz              TEXT          -- from the user profile; NULL means skip the member
);

“Members with a null timezone or fewer than 4 workouts in the window get no reminder. That is a deliberate cut, not a bug.”

Where the work happens. “The nightly job scans only the day’s partition of events, adds it to a small table of completed workouts per member, weekday and local start hour, rebuilds member_habit from the last eight weeks of that table, and records today’s decision for every member it considered, in both arms, in reminder_decisions(user_id, decided_at, arm, would_send, sent, reason). Only the treatment arm’s sends go on reminder_queue(user_id, send_at, reason). Logging the holdout’s decisions, the reminders we would have sent, is what makes the comparison fair: it gives me the same kind of member on both sides. At send_at, the push worker checks one thing against live data: an indexed lookup on events(user_id, occurred_at) for a workout_completed since the member’s local midnight. If there is one, it drops the reminder, so nobody who ran that morning gets nagged that evening; otherwise it sends and sets sent on the decision row. That lookup is the only raw-event read on the hot path, and it hits an index, not a scan. The first endpoint is GET /members/{id}/reminders for support and debugging: the recent decisions for one member, and why each was made.”

Metric. “Success is a workout_completed within 48h of the decision. I hold out a slice of eligible members who get no reminder, because the comparison that matters is reminded against not reminded, not before against after:”

SELECT r.arm,
       COUNT(*) AS eligible,
       AVG((EXISTS (
             SELECT 1 FROM events_clean e
             WHERE e.user_id = r.user_id
               AND e.event_type = 'workout_completed'
               AND e.occurred_at >= r.decided_at
               AND e.occurred_at <  r.decided_at + INTERVAL '48 hours'
           ))::int) AS return_rate
FROM reminder_decisions r
WHERE r.would_send
  AND r.decided_at < now() - INTERVAL '48 hours'
GROUP BY r.arm;

“The arm is fixed per member, by a stable hash, for example hashtext(user_id::text) % 10 = 0 in Postgres as the holdout, so nobody switches sides. would_send is true in both arms when the member qualified, so members who were never candidates stay out. I drop the last two days because their window hasn’t closed. I’d also watch the opt-out rate, because a reminder that wins workouts but loses push permission is a loss. A holdout of one member in ten is small, so before launch I write down the smallest lift worth shipping and run until the holdout is big enough to see it, and I don’t stop on the first good day.”

Two-week cut. “In week one I build events_clean, the nightly job, the queue and the worker’s check, and send reminders to internal staff accounts through your existing notification service. In week two I open it to the holdout test, fix the timezone and dedupe rules on what staff saw, and ship the metric query as a dashboard. What I am deferring: a learned send time, multiple reminders and anything that reads workout content.”