Scheduled Reminders (Phases 1-4 + Evening-Checklist Reminder)

Scheduled Reminders

A 4-phase scheduled-reminders system layered on top of the centralized notification stack, plus a sibling evening-checklist nudge that reuses the same pg_cron-SQL-fn pattern without touching the reminder table. Lets the user fire a notification to themselves at a future time — ad-hoc one-offs (P1), recurring rules (P2), automatic “up next” reminders for day-plan blocks (P3), and snooze-a-notification (P4). The whole scheduler is a pure-SQL pg_cron job; reminders fire by inserting a notification row directly. The evening-checklist reminder (shipped 2026-05-11) is a second, independent pg_cron SQL function built on the same primitives — see Evening-Checklist Reminder.

For Agents

Builds on push-notifications (the centralized notification system) and mobile-notifications-ui (the in-app notification surfaces). Reminders deliberately bypass emit_notification — see the warning callout below; this is the single most surprising design choice in the feature. All four phases are shipped and applied to the remote DB. The canonical design doc is docs/superpowers/specs/2026-05-10-scheduled-reminders-design.md (its Phase 3 section was rewritten to the real day_plan_block schema after the original spec got it wrong — though even the rewrite mis-typed start_time as time; it’s actually timestamptz, see Gotchas #4); plan is docs/superpowers/plans/2026-05-10-scheduled-reminders.md. The evening-checklist reminder is a separate feature in the same family — spec docs/superpowers/specs/2026-05-11-evening-checklist-reminder-design.md, plan docs/superpowers/plans/2026-05-11-evening-checklist-reminder.md; it does not use the reminder table or process_due_reminders — see its own section below.

All migrations applied — Phase 3 is live

20260512000400_reminder_p2_5_hardening.sql (P2.5 backend hardening), 20260513000100_day_plan_block_reminder_trigger.sql (P3 trigger) and 20260513000200_reminder_update_guard.sql (the reminder BEFORE UPDATE guard) are committed to master and applied to the remote DB. Phase 3 is live and smoke-tested. (Historical note: those migrations sat un-applied for part of the session because the Supabase MCP token expired mid-work — see Gotchas #1 for why supabase db push is the wrong way to ship them when the MCP is down.)

The four phases at a glance

Phase	What	Key objects	Status
P1	Ad-hoc one-off reminders	public.reminder table, create_reminder RPC, process_due_reminders() + pg_cron job, /reminders page, MobileReminders, ReminderCreateDialog, CUSTOM_REMINDER notification type	Shipped
P1.5	Post-review fixes	recipient_self SECDEF→INVOKER, ownership validation, FOR UPDATE SKIP LOCKED, send-push EF v6 pref-fallback + HTTP 500 on errors, i18n, toast [object Object] fix	Shipped
P2	Recurring reminders	compute_first_fire_at / compute_next_fire_at SQL helpers, scheduler advances fire_at, 6-tab dialog (Once/Daily/Weekdays/Weekly/Monthly/Hourly)	Shipped
P2.5	Post-review fixes	STABLE not IMMUTABLE, SET search_path='', monthly day-of-month clamping, RAISE LOG on terminated recurrence, dialog Radix-nesting fix, max-h-[90dvh] scroll, string-state number inputs	Shipped (migration 20260512000400 applied)
P3	Day-plan-block reminders	sync_day_plan_block_reminder() trigger split into trg_dpb_reminder_iud + trg_dpb_reminder_upd on day_plan_block	Shipped & smoke-tested (migration 20260513000100 applied)
P4	Snooze on notifications	SnoozePopover, useSnoozeNotification, long-press 500ms (mobile) / hover-chip (desktop), process_due_reminders re-emits (forwards original entity_type/entity_id)	Shipped
P3+P4 review	4-perspective review (DB/security/frontend/ops) of P3 and P4; all critical+important findings fixed	Shipped (incl. migration 20260513000200 — reminder BEFORE UPDATE guard)

Architecture

Reminder lifecycle

graph TD
    subgraph Create
        UI1["ReminderCreateDialog<br/>(/reminders, MobileReminders)"] --> RPC["create_reminder RPC<br/>(SECURITY DEFINER)"]
        UI2["SnoozePopover<br/>(notification long-press/chip)"] --> RPC
        TRG["day_plan_block trigger<br/>sync_day_plan_block_reminder()"] -.upsert.-> ROW
        RPC --> ROW["public.reminder row<br/>fire_at, recur_rule?, source, source_notification_id?"]
    end
    subgraph Scheduler
        CRON["pg_cron: '* * * * *'<br/>SELECT process_due_reminders()"] --> SCAN["scan reminder WHERE<br/>fire_at <= now() AND fired_at IS NULL<br/>FOR UPDATE SKIP LOCKED"]
        SCAN --> INS["INSERT INTO notification<br/>origin='reminder', type='CUSTOM_REMINDER'<br/>data->>'source_id' dedup key"]
        INS --> REC{"recur_rule<br/>set?"}
        REC -->|yes| ADV["fire_at = compute_next_fire_at(r)<br/>(or RAISE LOG + terminate if NULL)"]
        REC -->|no| MARK["fired_at = now()"]
    end
    subgraph Deliver
        INS --> TRG2["send_push_on_notification trigger<br/>(silences origin='event_fanout')"]
        TRG2 -->|origin='reminder' → NOT silenced| EF["send-push EF v6<br/>(verify_jwt: false)"]
        EF --> DEV["push to devices<br/>(falls back to notification_type_def.default_push)"]
        INS --> FEED["in-app notification feed<br/>(NotificationBell / MobileNotificationsSheet)"]
    end
    style RPC fill:#264653,stroke:#2a9d8f,color:#fff
    style CRON fill:#264653,stroke:#2a9d8f,color:#fff
    style INS fill:#2d2d2d,stroke:#888,color:#fff
    style TRG2 fill:#3d2020,stroke:#a44,color:#fff

Why reminders bypass emit_notification (the load-bearing decision)

Reminders direct-INSERT into notification on purpose

emit_notification hardcodes origin = 'event_fanout'. The send_push_on_notification trigger silences origin = 'event_fanout' during the shadow-mode rollout of the centralized notification system. So if reminders went through emit_notification, no push would ever fire. Instead process_due_reminders inserts a notification row directly with data->>'origin' = 'reminder', which the send-push trigger does NOT silence.

If the shadow-mode rollout ever completes and the event_fanout silence is removed, this can be revisited — but until then, do not “clean this up” by routing reminders through emit_notification. It will silently kill push delivery.

The scheduler is pure SQL — no Edge Function in the loop

cron.schedule('process-due-reminders', '* * * * *', 'SELECT public.process_due_reminders();')

No Edge Function is invoked by the scheduler itself. (Push delivery downstream goes through the send-push EF, but that’s triggered by the notification INSERT, not by the cron job.) The cron.schedule call is idempotent — a DO block unschedules first, then re-schedules — so re-running the migration doesn’t duplicate the job.

CUSTOM_REMINDER notification type

Registered in notification_type_def with default_push = true. It also has a placeholder recipient_self resolver fn (entity_type = 'reminder', match_event_type = 'CREATED'). The resolver never actually matches — there’s no audit trigger on the reminder table — so it’s vestigial; recipient resolution happens implicitly because process_due_reminders writes the recipient column on the notification row directly.

The public.reminder table

Concern	Detail
Owner column	scoped to actor_person_id() for RLS SELECT
No INSERT policy	Rows are created only via the create_reminder SECURITY DEFINER RPC. PostgREST cannot insert directly.
reminder_update_own	Permissive UPDATE policy — lets a user update their own row via PostgREST. Flagged across reviews as too loose. Now backstopped by the reminder_update_guard BEFORE UPDATE trigger (migration 20260513000200): when the current role is authenticated, the trigger rejects any direct UPDATE that changes a column other than status — so the PostgREST path can only flip status, while process_due_reminders (running as the cron/owner role) is unaffected. A full RLS rewrite is still a deferred follow-up but the direct-PostgREST hole is closed.
fire_at	timestamptz — when the reminder should fire
fired_at	timestamptz nullable — set when a one-off fires; recurring rows keep this NULL and advance fire_at instead
recur_rule	jsonb nullable — discriminated union (see below)
source	'manual' / 'snooze' / 'day_plan_block'
source_notification_id	uuid nullable — set for snoozes; FK to the notification being snoozed; create_reminder validates the caller owns it
source_entity_id	for source='day_plan_block' this is the block’s id; keyed by partial unique index reminder_dpb_uidx (source_entity_id) WHERE source = 'day_plan_block'

recur_rule — discriminated-union jsonb

Stored verbatim by the RPC. The scheduler’s date math fails safe via its exception handler if the jsonb is malformed.

Preset	Shape
Daily	{kind:'daily', at:'HH:MM'}
Weekdays (Mon-Fri)	{kind:'weekdays', at:'HH:MM'}
Weekly (day-set)	{kind:'weekly', days:['MON','WED',...], at:'HH:MM'}
Monthly (day-of-month)	{kind:'monthly', day_of_month:N, at:'HH:MM'}
Hourly (every-N)	{kind:'hourly', every_n_hours:N}

source_id dedup key

In the notification row’s data:

data->>'source_id' = reminder.id::text || ':' || extract(epoch from reminder.fire_at)::bigint::text

So a recurring reminder’s each occurrence dedups independently (different fire_at → different key), while a same-tick double-fire of the same occurrence is prevented via:

ON CONFLICT (recipient, type, (data->>'source_id')) WHERE data ? 'source_id'

SQL helpers (P2 recurrence math)

Function	Purpose	Volatility	search_path
compute_first_fire_at(recur_rule, tz)	Given a rule + tz, when does the first occurrence land? Used by create_reminder when p_fire_at is null.	STABLE (calls now())	SET search_path = ''
compute_next_fire_at(reminder)	Given a reminder row, when’s the next occurrence? Used by process_due_reminders to advance recurring rows.	STABLE	SET search_path = ''

These are STABLE, not IMMUTABLE — on purpose

They call now(). IMMUTABLE on a function that calls now() is a correctness lie — the planner is entitled to constant-fold it at plan time. Behaviorally identical inside a plpgsql body, but bites if ever used in an index expression / generated column / CHECK / matview. Originally declared IMMUTABLE; fixed in P2.5.

Monthly recurrence clamps day_of_month to the actual month length

LEAST(dom, EXTRACT(DAY FROM <end-of-month>)). Before the P2.5 fix, a day_of_month = 31 rule in February (or 30-day months) overflowed into the next month and then drifted permanently (each subsequent computation pushed it further). Now it clamps to e.g. Feb 28/29.

Scheduler cursor must be %ROWTYPE, not record

The FOR ... LOOP cursor variable in process_due_reminders must be declared public.reminder%ROWTYPE. If it’s record, compute_next_fire_at(r) fails with cannot cast type record to reminder (SQLSTATE 42846) — record won’t implicitly cast to a named composite type.

Phase 1 — ad-hoc one-off reminders

• public.reminder table (above).
• create_reminder RPC (SECURITY DEFINER) — the only insert path. Validates p_source_notification_id ownership. When p_fire_at is null and a p_recur_rule is given, derives fire_at from compute_first_fire_at.
• process_due_reminders() — run every minute by pg_cron. Scans due rows with FOR UPDATE SKIP LOCKED, inserts the notification, advances or marks each. Exception handler RAISE WARNINGs instead of dying on a bad row. RAISE LOGs when a row has a recur_rule but compute_next_fire_at returns NULL (terminating the recurrence) — was silent before P2.5.
• Frontend: /reminders desktop page + MobileReminders screen + ReminderCreateDialog + sidebar / More-menu entries.

Phase 1.5 — post-review fixes

Backend:

• recipient_self resolver downgraded SECURITY DEFINER → SECURITY INVOKER.
• create_reminder validates p_source_notification_id ownership.
• process_due_reminders got FOR UPDATE SKIP LOCKED, a RAISE WARNING in the exception handler, and an idempotent cron.schedule (DO block: unschedule then schedule).
• send-push EF v6 fallback bug fix — it used to treat a missing notification_preference row as “push disabled”, so push only fired for types the user had explicitly toggled on in settings. Now falls back to notification_type_def.default_push when there’s no explicit preference row. (See push-notifications for the centralized prefs model — emit_notification already had the equivalent default_in_app fallback for in-app delivery.)
• send-push EF v6 also: returns HTTP 500 on real errors (was returning 200, which masked failures — the error lived only in net._http_response); push tag falls back to notification:<id> when no better tag is available.

Frontend:

• i18n (EN + HU) on all reminder strings; loading / error UI states.
• Smarter mobile back button: navigate(-1) with /more as the fallback.
• Toast [object Object] bug fix — Supabase error objects are plain objects, not Error instances, so String(err) rendered [object Object]. Now pulls .message / .details / .hint / .code explicitly. (Recorded in debugging-log-crm.)

Phase 2 — recurring reminders

• compute_first_fire_at / compute_next_fire_at SQL helpers (above) covering presets daily / weekdays / weekly (day-set) / monthly (day-of-month) / hourly (every-N).
• process_due_reminders advances fire_at via compute_next_fire_at for recurring rows instead of marking them fired.
• create_reminder derives fire_at from compute_first_fire_at when p_fire_at is null.
• Dialog rebuilt with 6 tabs — Once / Daily / Weekdays / Weekly / Monthly / Hourly:
  • Select-based HH:MM TimePicker (two <select>s, not a native time input).
  • Day-of-week chips for Weekly.
  • Native <input type="date"> + Today / Tomorrow / Next-week chips — replaced the iOS-clunky datetime-local input.

Phase 2.5 — post-review fixes

Backend (migration 20260512000400 — committed and applied to remote):

• compute_*_fire_at declared STABLE not IMMUTABLE.
• Both got SET search_path = ''.
• Monthly recurrence clamps day_of_month to days-in-month via LEAST(dom, EXTRACT(DAY FROM <end-of-month>)).
• process_due_reminders RAISE LOGs when a recur_rule row gets a NULL next-fire (terminating the recurrence).

Frontend (shipped):

• The recurrence dialog’s <Tabs> was wrongly wrapping the always-visible title / body inputs — invalid Radix nesting, broke keyboard nav + autoFocus. Restructured so <Tabs> wraps only TabsList + TabsContent.
• DialogContent got max-h-[90dvh] overflow-y-auto — the 6-tab layout was clipping Save / Cancel off-screen on small phones.
• Day-of-month / every-N-hours number inputs switched to string state — clearing the field to retype a 2-digit value was snapping it back to 1.
• TimePicker got role="group" + per-SelectTrigger aria-labels.

Phase 3 — day-plan-block reminders

Live & smoke-tested — migrations 20260513000100 (trigger) + 20260513000200 (reminder update guard) applied.

sync_day_plan_block_reminder() — a SECURITY DEFINER trigger function on public.day_plan_block. After the P3 review it’s wired up as two triggers (see C-finding #2 below) rather than one AFTER INSERT OR UPDATE OR DELETE:

Trigger	Fires	Why
trg_dpb_reminder_iud	AFTER INSERT OR DELETE, always	inserts always create / deletes always remove the paired reminder
trg_dpb_reminder_upd	AFTER UPDATE WHEN (start_time / status / title changed)	the day-planner does lots of UPDATEs that touch none of these (drag-reorder position, notes edits, …); the WHEN clause stops those no-op-for-reminders updates from re-running the trigger body

Trigger body:

• When a block is status = 'PLANNED' with a non-null start_time → upsert a reminder at start_time - interval '5 minutes', title 'Up next: <block title>', source = 'day_plan_block', person_id = NEW.person, keyed on the reminder_dpb_uidx partial unique index (source_entity_id) WHERE source = 'day_plan_block'.
  • start_time is a timestamp with time zone — an absolute instant. No standup.date join, no AT TIME ZONE math: the fire time is literally start_time - interval '5 minutes'.
  • The reminder’s owner comes from NEW.person — the block’s own column, which RLS already pins to auth.uid(). The trigger does not do a SECURITY-DEFINER-amplified lookup of the standup owner, so the cross-person-injection concern the P3 review raised is moot (see C-finding below).
  • ON CONFLICT DO UPDATE only resets status = 'pending' / fired_at = NULL when fire_at actually changed AND the new fire_at > now(). So re-saving a block (or editing its title) doesn’t re-arm a reminder that already fired, and moving a block into the past doesn’t resurrect a stale reminder.
• When the block is status DONE / CANCELLED / SKIPPED, has a NULL start_time, or is deleted → cancel the reminder.

day_plan_block.start_time is timestamptz, not time — and an earlier review assumed time

Schema reality for the columns this trigger touches:

• The owner column is person — not person_id. (RLS pins it to auth.uid().)
• start_time is a timestamp with time zone — an absolute instant, NOT a time. An earlier review of day_plan_block (and an early spec draft) assumed start_time was a time value living alongside the standup’s date. A trigger that did standup.date + start_time would have been a date + timestamptz runtime type error — and database.types.ts types start_time as string either way, so nothing static would have caught it; only a live INSERT during smoke-testing surfaced it.
• Because start_time is already absolute, there is no timezone math in the trigger and no standup join — the original “hardcoded Europe/Budapest” shortcut is gone too.
• day_plan_block.status values: PLANNED, DONE, CANCELLED, SKIPPED. See day-planner for the day-plan architecture.

P3 review — critical / important findings & fixes

A 4-perspective review (DB / security / frontend / ops) of the Phase 3 work; all critical + important findings were fixed and shipped before P3 went live.

• C1 — re-arming an already-fired reminder. The original ON CONFLICT DO UPDATE unconditionally reset status='pending' / fired_at=NULL, so any later UPDATE of the block (even one that didn’t move it) re-armed the reminder. Fixed: the conflict branch only resets those columns when fire_at genuinely changed and the new fire_at > now().
• C2 — single trigger churned on every block UPDATE. One AFTER INSERT OR UPDATE OR DELETE trigger meant the day-planner’s frequent reminder-irrelevant UPDATEs (position reorders, notes edits) re-ran the trigger body for nothing. Fixed: split into trg_dpb_reminder_iud (INSERT/DELETE, always) + trg_dpb_reminder_upd (UPDATE, WHEN start_time/status/title changed).
• Cross-person injection (raised, then resolved by design change). A SECURITY-DEFINER trigger that looked up the standup’s owner to set the reminder’s person_id could in principle write a reminder for someone else’s account. Resolved by using NEW.person (the block’s own RLS-pinned column) instead of a standup-owner lookup — no amplified read, nothing to inject.
• See also P4 review — important findings & fixes and the new reminder_update_guard BEFORE UPDATE trigger (migration 20260513000200), which independently restricts the authenticated role’s direct UPDATEs on reminder to status only.

Phase 4 — snooze on notifications

• Mobile: long-press a notification (500ms timer, post-review — was 350ms) → SnoozePopover. Long-press now fires a haptic tick and shows press feedback so the gesture is discoverable.
• Desktop: hover a notification → a Snooze chip appears → click it → SnoozePopover.
• SnoozePopover — 4 presets: 1h / 3h / Tomorrow 9am / Next week. The fire time is computed client-side in the browser’s timezone.
• → useSnoozeNotification → create_reminder with source = 'snooze', source_notification_id = <the notification's id>. The hook also forwards the original notification’s entity_type / entity_id so the re-fired notification keeps its click-through target (P4 review fix — see below).
• When the snooze reminder fires, process_due_reminders re-emits it as a fresh CUSTOM_REMINDER notification — title / body and entity_type / entity_id copied from the original.
• SnoozePopover is shared by the desktop NotificationPopover and the mobile MobileNotificationsSheet (both render NotificationItem). See mobile-notifications-ui.

P4 review — important findings & fixes

A 4-perspective review (DB / security / frontend / ops) of the Phase 4 work; fixes shipped before P4 was finalized.

• Snooze lost the click-through target. The re-emitted notification used to copy only title / body, not entity_type / entity_id — so a snoozed TASK_ASSIGNED re-fired with no destination to click into. Fixed: useSnoozeNotification forwards entity_type / entity_id into create_reminder, and process_due_reminders writes them onto the re-emitted notification row.
• Long-press too twitchy / undiscoverable. Bumped the mobile long-press from 350ms → 500ms, added a haptic tick and visible press feedback on press-start.
• reminder UPDATE policy too permissive (shared with P3 review). Added the reminder_update_guard BEFORE UPDATE trigger (migration 20260513000200) — when the role is authenticated, direct UPDATEs are restricted to changing status only; process_due_reminders (cron/owner role) is unaffected. See the reminder_update_own row in [[scheduled-reminders#the-publicreminder-table|The public.reminder table]].

Evening-Checklist Reminder

Shipped 2026-05-11 — separate from Phases 1-4

A timed nudge to finish the daily Evening Checklist. Same pattern as process_due_reminders (self-contained pg_cron SQL fn, direct-insert with origin='reminder', dedup via data->>'source_id', per-person BEGIN/EXCEPTION) but it does not touch the reminder table, process_due_reminders, or the day-plan-block trigger — zero impact on those. Spec: docs/superpowers/specs/2026-05-11-evening-checklist-reminder-design.md (revised after a 3-perspective DB/security/ops review — the review added the in-app-pref gate, the count(DISTINCT), the RAISE LOGs, and the 'standup' click-through). Plan: docs/superpowers/plans/2026-05-11-evening-checklist-reminder.md. Commits on master: cf6e947 (migration), ed5a34d (send-push EF v7), 8fbfda1 (click handlers + NotificationPopover.test.tsx).

process_evening_checklist_reminders() — the function

A SECURITY DEFINER SQL function, called by a new pg_cron job process-evening-checklist-reminders on schedule '0 * * * *' — top of every hour, UTC. The function self-gates: it only does work when extract(hour from now() AT TIME ZONE 'Europe/Budapest') is 21 or 22. So it’s DST-proof (the gate is on Budapest wall-clock, not on a UTC cron offset), and 22 of the 24 hourly ticks are a one-statement RETURN.

Why a UTC '0 * * * *' cron + in-function Budapest gate instead of '0 20,21 * * *'

Pinning the cron schedule to UTC hours 20 & 21 would be the right Budapest times only in winter (CET = UTC+1); in summer (CEST = UTC+2) those would fire at 22:00 & 23:00 local. Running every hour and gating on now() AT TIME ZONE 'Europe/Budapest' inside the function makes the nudge land at 21:00 / 22:00 local year-round at the cost of 22 cheap no-op ticks/day. Same trick is reusable for any “fire at a local wall-clock hour” job.

When the gate passes, per tick:

1. X = count of active standup_datapoint_config rows with phase = 'EVENING_CHECKLIST' (currently 4). standup_datapoint_config is global — no person column — so X is the same for everyone.

2. Loop over people who actually do the ritual: SELECT DISTINCT person FROM standup WHERE date >= today - 30. (Scoping to recent-standup people keeps the loop tiny and avoids nudging someone who’s never used the feature.)

3. Per person, N = count(DISTINCT config) of that person’s standup_datapoint rows attached to today’s standup, where the row’s config is an active EVENING_CHECKLIST config and value is non-empty.

4. If N >= X → done, skip (RAISE LOG ... -> skip(done)).

5. Else check the (person, CUSTOM_REMINDER, in_app) notification_preference (falling back to notification_type_def.default_in_app when there’s no explicit row — same fallback semantics as process_due_reminders / emit_notification). If in-app is disabled → skip (RAISE LOG ... -> skip(in_app disabled)) — a disabled-in-app user gets neither the in-app row nor a push, exactly like process_due_reminders.

6. Else direct-INSERT a notification:

   Column	Value
   type	'CUSTOM_REMINDER'
   title	'Evening checklist'
   body	'N/X done — wrap up your day'
   entity_type	'standup'
   entity_id	today’s standup id, or NULL if no standup row exists yet
   data	{ source:'evening_checklist', source_id:'evening_checklist:<YYYY-MM-DD>:<hour>', origin:'reminder', n, x }

   …with ON CONFLICT (recipient, type, (data->>'source_id')) WHERE data ? 'source_id' DO NOTHING — the existing notification_dedup_uidx. Then RAISE LOG ... -> insert. Each person’s iteration is wrapped in BEGIN ... EXCEPTION WHEN OTHERS THEN RAISE WARNING ... so one bad person doesn’t abort the rest of the loop.

source_id includes the hour → 21:00 and 22:00 are distinct rows

source_id = 'evening_checklist:' || <today's date> || ':' || <Budapest hour>. So the 21:00 nudge and the 22:00 nudge dedup independently — the 22:00 tick naturally re-nudges only if still incomplete (it re-checks N < X); if the checklist got finished between 21 and 22, the 22:00 tick hits the skip(done) branch. Two different source_ids also means the OS doesn’t collapse the two pushes into one notification (see the tag note below).

Delivery & click-through

• Push: the notification INSERT fires the existing send_push_on_notification AFTER INSERT trigger → pg_net → send-push EF. data->>'origin' = 'reminder' is what escapes the shadow-mode push silence (the trigger only silences origin = 'event_fanout') — same mechanism the reminder table relies on. Push tag falls back to notification:<id> when no better tag is derivable, so the 21:00 and 22:00 pushes don’t collapse into a single OS notification.
• Click target: send-push’s deriveUrl gained case 'standup': return '/standup' (EF v7; verify_jwt stays false — see Gotcha #6). In-app: handleClick in both NotificationPopover.tsx (desktop) and MobileNotificationsSheet.tsx (mobile) got an else if (notification.entity_type === 'standup') navigate('/standup') branch (+ a new NotificationPopover.test.tsx unit test). So tapping the nudge — in-app or push — opens /standup, which auto-creates today’s standup row if it doesn’t exist yet.

What’s hardcoded / out of scope

• Nudge hours 21:00 / 22:00 Europe/Budapest are hardcoded. A user-configurable hour is out of scope — it would need a settings row.
• “Done” is judged by filled-in answers (N >= X, i.e. all EVENING_CHECKLIST datapoint values non-empty), not standup.evening_completed_at. This is deliberate: the checklist/reflection fields autosave on input (see mobile-autosave) and there’s no separate “complete” button, so evening_completed_at isn’t a reliable “the user is done” signal. If a real “complete” affordance is ever added, revisit this.

Evening-checklist-reminder gotchas / notes

• “Done” = N >= X, not standup.evening_completed_at IS NOT NULL — see the “out of scope” note above. The evening checklist has no “complete” button; fields autosave. Revisit if that changes.
• standup_datapoint_config is global (no person column) → X is the same for every person. If it ever gains a person column, X has to become per-person.
• Latent multi-tenant note: process_evening_checklist_reminders is SECURITY DEFINER and standup’s RLS is WITH CHECK (true), so in a future multi-user world user A could INSERT a standup row attributed to B → B gets a (harmless, fixed-content) nudge. Single-user CRM today, not exploitable; tracked for a future multi-tenant pass (tighten standup RLS to person = auth.uid()). Same family of concern as the day_plan_block/standup RLS follow-up below.
• Snooze quirk: because the nudge is type = 'CUSTOM_REMINDER', the Phase-4 long-press snooze works on it. A snoozed copy re-fires next morning copying the literal “3/4 done” body against tomorrow’s fresh 0/4 standup. Harmless; not special-cased.
• It does not write to or read the reminder table, does not call process_due_reminders, and does not interact with the day-plan-block trigger. Brand-new pg_cron job (process-evening-checklist-reminders), brand-new function. The only shared surface is the notification table + the send_push_on_notification trigger + the send-push EF + the notification_dedup_uidx index.

Gotchas

These were learned during this work. The Postgres ones have cross-project value and are also captured in debugging-log-crm.

1. Supabase MCP apply_migration assigns its OWN timestamp version (YYYYMMDDHH24MISS at apply time), NOT the version in your migration filename. So a project where migrations were applied via the MCP shows a permanent remote-vs-local mismatch in supabase migration list, and supabase db push against it tries to re-run all the local migration files (their versions don’t match remote rows) — which fails on CREATE TABLE (non-IF NOT EXISTS) statements. The migration files are the source of truth for intended schema; remote history is just an audit log with different version numbers. Don’t chase parity. If you must apply a migration while the MCP is down: write the file, commit it, and apply via psql -f against the connection string — supabase db push is dangerous here.
2. IMMUTABLE on a function that calls now() is a correctness lie. The planner can constant-fold it at plan time. Use STABLE. Behaviorally identical inside a plpgsql body, but bites in index expressions / generated columns / CHECKs / matviews.
3. PL/pgSQL cursor variables must be %ROWTYPE, not record, if you pass them to a function expecting that exact row type. record won’t implicitly cast — cannot cast type record to reminder, SQLSTATE 42846.
4. day + time in Postgres yields timestamp WITHOUT time zone (wall-clock). To get a timestamptz you must AT TIME ZONE '<zone>'. Casting a wall-clock timestamp directly to timestamptz uses the server’s tz (UTC on Supabase) — almost never what you want for user-facing times.
5. Supabase error objects are NOT Error instances — String(err) renders [object Object]. Pull err.message / .details / .hint / .code explicitly when surfacing in a toast.
6. send-push EF must keep verify_jwt: false. The send_push_on_notification trigger calls it via pg_net with Authorization: Bearer <SEND_PUSH_WEBHOOK_SECRET>, not a JWT. Re-deploying with verify_jwt: true (the MCP deploy_edge_function default) silently 401s every push. Always pass verify_jwt: false explicitly and verify it stuck via list_edge_functions.
7. Notification preference “no row” fallback semantics — emit_notification (in-app) falls back to notification_type_def.default_in_app; send-push (push) now falls back to notification_type_def.default_push. Before the P1.5 fix, send-push treated a missing row as disabled — so push only worked for types the user had explicitly toggled.
8. An earlier review of day_plan_block assumed start_time was a time; it’s actually timestamptz. The early Phase 3 plan was going to compute the fire time as standup.date + start_time — which would have been a date + timestamptz runtime type error (operator does not exist: date + timestamp with time zone). database.types.ts types start_time as string regardless of whether it’s time or timestamptz, so nothing static catches the difference — it only surfaced on a live INSERT during smoke-testing. Lesson: when a trigger or RPC depends on a column’s exact SQL type (not just “is it a string in TS”), verify it in the actual schema (\d table / information_schema.columns), not just the generated TS types or a spec draft.
9. A WHEN (...) clause on an AFTER UPDATE trigger is the cheap fix for “this trigger churns on UPDATEs that don’t matter to it.” The day-planner UPDATEs day_plan_block constantly for things the reminder trigger doesn’t care about (position, notes). Splitting the trigger into INSERT/DELETE-always + UPDATE-WHEN-relevant-columns-changed avoids re-running the body for nothing — without that, every drag-reorder re-evaluated the reminder upsert.
10. For a “fire at a local wall-clock hour” pg_cron job, schedule it '0 * * * *' (every hour, UTC) and gate inside the function on extract(hour from now() AT TIME ZONE '<zone>'). pg_cron schedules are UTC; pinning the cron to fixed UTC hours is only correct in one half of the DST year. Hourly + an in-function local-hour gate is DST-proof; the cost is N−k cheap no-op ticks/day (a one-statement RETURN). Used by process_evening_checklist_reminders (gate = hour ∈ {21,22} Europe/Budapest).
11. count(DISTINCT config) not count(*) when “progress” is “how many distinct items have an answer.” A person could have multiple standup_datapoint rows pointing at the same config (history / re-saves); count(*) would over-count and could spuriously cross the N >= X “done” threshold. count(DISTINCT config) FILTER (WHERE value <> '') is the honest count. (A 3-perspective review caught this in the evening-checklist reminder.)
12. A self-nudging cron job should re-check completion every tick, and put a discriminator in the dedup source_id so successive ticks are distinct rows. process_evening_checklist_reminders runs at both 21:00 and 22:00 Budapest; source_id embeds the hour, so the 22:00 tick is a separate (deduped-independently) notification that only materializes if the work is still incomplete at 22:00. Without the hour in the key, the 22:00 re-nudge would be swallowed by the 21:00 row’s dedup.

Open follow-ups (post-Phase 4 + P3/P4 review)

• Multi-tenant day_plan_block / standup RLS tightening (the P3 review’s C2) — much less urgent now that the trigger derives the reminder’s owner from NEW.person rather than a standup-owner lookup, so there’s no SECURITY-DEFINER-amplified cross-person write path. Still worth doing if/when the CRM goes multi-tenant.
• Full reminder_update_own RLS rewrite — partially addressed: the new reminder_update_guard BEFORE UPDATE trigger (migration 20260513000200) already blocks the direct-PostgREST path from changing anything but status. Replacing the permissive USING (true) policy with a column-scoped one is still nice-to-have but no longer load-bearing.
• No retention / cleanup job for reminder (fired rows accumulate), net._http_response, or cron.job_run_details.
• useReminders / recurrence / snooze / the day_plan_block trigger have thin test coverage.
• Per-block / per-person configurable lead time for day-plan-block reminders (currently hardcoded 5 min).
• Custom snooze time picker (currently 4 presets only).

Spec & Plan

• Spec — docs/superpowers/specs/2026-05-10-scheduled-reminders-design.md (canonical design; Phase 3 section rewritten to the real day_plan_block schema — but note even that rewrite mis-typed start_time as time; it’s timestamptz)
• Plan — docs/superpowers/plans/2026-05-10-scheduled-reminders.md
• Evening-checklist reminder spec — docs/superpowers/specs/2026-05-11-evening-checklist-reminder-design.md (revised after a 3-perspective DB/security/ops review)
• Evening-checklist reminder plan — docs/superpowers/plans/2026-05-11-evening-checklist-reminder.md

Commits (master)

Phase	Commits
P1	~5de4cbc..a390815 + 5018344 + 2130a06 + c60d2b8
P2	5d4bd47, 4e413eb, 6e87c08
P2.5	6757811 (migration 20260512000400 — applied), fa1ea6b (frontend)
P3	cd67841 (migration 20260513000100 — applied)
P4	664a63f
P3+P4 review fixes	trigger split / ON CONFLICT guard / snooze entity forwarding / long-press 500ms / migration 20260513000200 reminder_update_guard (commits interspersed)
Evening-checklist reminder	cf6e947 (migration — process_evening_checklist_reminders + process-evening-checklist-reminders cron job), ed5a34d (send-push EF v7 — 'standup' URL case), 8fbfda1 (NotificationPopover + MobileNotificationsSheet standup click branch + NotificationPopover.test.tsx), plus the spec/plan doc commits

Plus build / deploy / push commits interspersed.

Related

• push-notifications — Centralized notification system this builds on (notification table, notification_type_def, send-push EF, the event_fanout silencing)
• mobile-notifications-ui — In-app notification surfaces (NotificationItem, MobileNotificationsSheet, NotificationPopover) that snooze + the evening-checklist click-through hook into
• evening-checklist — The Evening Checklist ritual the evening-checklist reminder nudges (EVENING_CHECKLIST phase, standup_datapoint_config/standup_datapoint)
• mobile-autosave — Why “done” for the evening checklist is N >= X, not evening_completed_at (fields autosave, no complete button)
• day-planner — Day-plan / day_plan_block architecture (Phase 3 trigger source); also hosts the desktop standup page /standup opens to
• debugging-log-crm — The Postgres gotchas + the actor_person_id / send-push / toast bugs
• security — Auth model (actor_person_id(), RLS, CF Access JWT)
• tech-debt — Where the open follow-ups land
• levandor-crm — Project overview