Nudge
The intervention system: reusable messages (NUDGE), reusable schedules (NUDGE_SCHEDULE / NUDGE_CRON), and an append-only delivery log. Sends have two paths: recipe-driven for group/cohort/theme audiences (defined in recipe) and API-driven for one-off or event-actor sends (the caller hits a trigger_type=webhook schedule and supplies the user_id). NUDGE_DELIVERY_LOG.recipe_id is nullable to reflect this — null means the send came in via API rather than a recipe.
See also: recipe for sequencing + targeting, user / cohort (delivery context).
Tables
NUDGE
A reusable intervention — an encouragement, prompt, reminder, or link to an exercise. type classifies it (prompt, reminder, encouragement, link, exercise), body_md is the message content, and link optionally points to a URL the recipient should visit. The "what to send", kept separate from both the "when" (NUDGE_SCHEDULE) and the "who" (RECIPE). Carries the same publishing lifecycle as a course: status (draft → published → archived) so a half-written nudge can't fire, author_id for ownership, version per published revision, and published_at / archived_at timestamps.
id ndg_01_three_things
author_id usr_09_teacher
title Three things
description Daily prompt to list three things you're grateful for.
type prompt
body_md Take a minute — what are **three things** you're grateful for today?
link https://app.afh.example/exercise/three-things
status published
version 1
published_at 2026-03-01T09:00:00Z
archived_at null
NUDGE_SCHEDULE
The "when" half of a nudge. trigger_type picks one of three modes:
cron— fires on the cadence incron_id.event— fires when aUSER_ACTIONwhoseaction_typematchesevent_action_typeis recorded.offset_days/offset_timeset how long after the event the nudge actually sends (both null = immediate). Optionally scope the match to a single target withevent_target_type+event_target_id(matched againstUSER_ACTION.target_type/target_id): leave both null to fire on any target of that action type (e.g. any module completed), or set them to fire only for one specific target (e.g. only when module X is completed).webhook— fires when an external caller hitswebhook_url. The DB doesn't know what the event was; whatever called the URL is the trigger.
dedupe_window_minutes is the schedule-level send policy: the minimum minutes that must pass before this same schedule may produce another delivery for the same user. For example 1440 means "don't send this scheduled/event-triggered nudge to the same user more than once per day"; null means no window-based dedupe beyond the per-send idempotency_key on the delivery log. This is enforced in application logic (often by deriving a dedupe_bucket from the window and folding it into the delivery idempotency_key), not by a constraint on this row.
Each row binds one nudge to one trigger.
id sch_01_three_things_daily
nudge_id ndg_01_three_things
trigger_type cron
cron_id cron_weekday_9am
event_action_type null
event_target_type null
event_target_id null
offset_days null
offset_time null
webhook_url null
dedupe_window_minutes null
Event-triggered example — "send the welcome nudge 1 day after a user enrolls, at 9am". event_target_* are null, so it fires on any enrolled action:
id sch_02_post_enrollment
nudge_id ndg_02_welcome
trigger_type event
cron_id null
event_action_type enrolled
event_target_type null
event_target_id null
offset_days 1
offset_time 09:00:00
webhook_url null
dedupe_window_minutes null
Target-scoped example — "send the forum prompt only when a learner completes this one module" (event_target_id pins the match to a single module):
id sch_03_day1_done
nudge_id ndg_module_done
trigger_type event
cron_id null
event_action_type completed_module
event_target_type module
event_target_id mod_01_day1
offset_days null
offset_time null
webhook_url null
dedupe_window_minutes 1440
NUDGE_CRON
A named, reusable cron expression with its YAML config alongside. Many nudge schedules can share one cron (e.g. "every Monday 9am") without duplicating the expression.
id cron_weekday_9am
title Weekdays at 9am
description Mon–Fri at 09:00 UTC
cron_expression 0 9 * * 1-5
yaml_text schedule:\n timezone: UTC\n expression: "0 9 * * 1-5"
NUDGE_DELIVERY_LOG
Append-only audit of every nudge sent — and the enforcement point for idempotent delivery. Records who received it, via which channel (email, sms, push, slack, in_app), the status (queued, sent, failed, bounced), and the raw provider response JSONB.
Three nullable provenance FKs capture what caused the send (any combination may be set depending on the path):
source_action_id→USER_ACTION.id— the action that triggered an event/webhook send.nudge_schedule_id→NUDGE_SCHEDULE.id— the schedule/trigger rule that produced it.recipe_step_id→RECIPE_STEP.id— the recipe step that produced it, when recipe-driven.
recipe_id is nullable as before: set when the send was driven by a recipe, null for a direct API call against a trigger_type=webhook schedule.
Deduplication lives in two columns:
idempotency_key— not null, a deterministic key the application computes (see below) that means "this exact send should happen only once." A unique index makes a second insert a no-op.dedupe_bucket— an optional time bucket (e.g.2026-05-28,2026-W22,2026-05-28T14) used for windowed dedupe; typically folded into theidempotency_keyso a per-window send collapses to one row.
CREATE UNIQUE INDEX nudge_delivery_log_idempotency_key_unique
ON nudge_delivery_log (idempotency_key);id dlv_2026_04_03_001
nudge_id ndg_01_three_things
user_id usr_42_maya
enrollment_id enr_01_april_maya
recipe_id rcp_01_gratitude_30d
source_action_id null
nudge_schedule_id sch_01_three_things_daily
recipe_step_id step_01_day1
channel email
status sent
idempotency_key nudge_delivery:usr_42_maya:ndg_01_three_things:weekly:2026-W14:email
dedupe_bucket 2026-W14
response {"provider": "postmark", "message_id": "pm_abc123"}
sent_at 2026-04-03T09:00:00Z
Generating idempotency keys
Idempotency keys are built by the application, not generated randomly by Postgres. The application derives a deterministic key from the business meaning of the send, so that the same logical send always produces the same key — and the unique index above collapses retries, duplicate webhook deliveries, and re-run worker jobs into a single row. Postgres only enforces uniqueness; it never decides what "the same send" means.
Patterns by trigger path:
# event-triggered — keyed on the action that caused it
nudge_delivery:{user_id}:{nudge_id}:{source_action_id}:{channel}
# cron-triggered weekly — keyed on the time bucket
nudge_delivery:{user_id}:{nudge_id}:weekly:{week_bucket}:{channel}
# recipe step — keyed on step + dedupe bucket
nudge_delivery:{user_id}:{recipe_id}:{recipe_step_id}:{dedupe_bucket}:{channel}
# direct API send — keyed on the caller's request id
nudge_delivery:direct:{request_id}:{user_id}:{nudge_id}:{channel}Idempotent inserts
Workers, webhooks, and retries all insert the same way — let the unique index absorb duplicates:
INSERT INTO nudge_delivery_log (...)
VALUES (...)
ON CONFLICT (idempotency_key) DO NOTHING;If the same job runs twice, the second insert is silently ignored, so a delivery is logged (and dispatched) exactly once. This is what makes the whole pipeline safe to retry.
How the three dedupe mechanisms divide the work
Each layer owns one concern, and they stay separate:
USER_ACTION(source_system/source_event_id/idempotency_key) prevents the same source event from being recorded twice.NUDGE_DELIVERY_LOG.idempotency_keyprevents the same send from happening twice.NUDGE_SCHEDULE.dedupe_window_minutesdefines how often a schedule is allowed to send again for the same user.
Keeping event ingestion, send logging, and schedule policy in three places means a fix or change to one never has to disturb the others.