Implement activity registration contact reset hook

epic-assignment-follow-up-reminders-cron-infrastructure-task-004 — Add a database trigger or service hook that updates last_contact_date on the relevant assignment record whenever a peer mentor logs a new activity against a contact. This hook closes the feedback loop — once contact is registered, the reminder evaluation service will see a recent last_contact_date and stop generating reminders for that assignment until the threshold is exceeded again.

critical priority medium complexity backend pending database specialist Tier 19

Acceptance Criteria

Inserting a new activity row in the `activities` table with a valid assignment_id triggers an update to `assignments.last_contact_date` set to the activity's recorded date

If the activity's recorded date is earlier than the current `last_contact_date`, the field is NOT updated (only newer contact dates advance the last_contact_date)

The hook applies only to activity types that represent actual contact with the assigned contact — not administrative or system-generated activities

Updating or deleting an activity does NOT retroactively change last_contact_date (insert-only semantics)

The hook completes atomically within the same database transaction as the activity insert — if the activity insert rolls back, last_contact_date is not updated

After the hook executes, the ReminderSchedulerService on the next cron run correctly evaluates the updated last_contact_date and does not generate a reminder for that assignment

The hook works correctly when a coordinator logs activity on behalf of a peer mentor (proxy registration)

Logging is emitted (at debug level) whenever last_contact_date is updated, recording the assignment_id, old value, and new value

Technical Requirements

frameworks

PostgreSQL trigger functions (PL/pgSQL)

Supabase Edge Functions (Deno/TypeScript) as alternative hook implementation

apis

Supabase Database webhooks or PostgreSQL AFTER INSERT trigger

data models

activities

assignments

performance requirements

The trigger/hook must not add more than 10ms to the activity insert transaction

UPDATE on assignments must target a single row by primary key — no full table scans

The trigger must be a FOR EACH ROW AFTER INSERT to avoid blocking the insert

security requirements

The trigger function must run with SECURITY DEFINER only if required; prefer SECURITY INVOKER with appropriate grants

Ensure the trigger cannot be exploited to set last_contact_date to an arbitrary future date — validate that activity.recorded_date <= NOW() + 1 day

Execution Context

Execution Tier

Tier 19

Tier 19 - 2 tasks

Can start after Tier 18 completes

View Full Execution Plan

Implementation Notes

Prefer a PostgreSQL AFTER INSERT trigger over a Supabase Database webhook for this use case — it executes in the same transaction, guarantees atomicity, and avoids webhook delivery latency. Create the trigger with: `CREATE TRIGGER update_last_contact_date AFTER INSERT ON activities FOR EACH ROW EXECUTE FUNCTION fn_reset_assignment_last_contact()`. The trigger function should filter by activity type using a whitelist of contact-qualifying type codes (store the whitelist in a configuration table or as a trigger function parameter to avoid hardcoding). Use `NEW.recorded_date` as the source value.

Add a migration file for the trigger so it is version-controlled. If a Database webhook approach is chosen instead for flexibility, ensure idempotency guards are in place since webhooks can be delivered more than once.

Testing Requirements

Integration tests: insert a new activity row with a recent date and assert that `assignments.last_contact_date` is updated to match. Insert a second activity with an older date and assert last_contact_date is unchanged. Insert an activity of an administrative type and assert last_contact_date is unchanged. Simulate the full cron reminder cycle: create an assignment that would generate a reminder, insert a new activity, then run the ReminderSchedulerService evaluation and assert no reminder is generated.

Test the coordinator proxy path: coordinator inserts activity on behalf of peer mentor and last_contact_date updates correctly. Use flutter_test with Supabase test utilities to set up and tear down test data.

Component

Assignment Reminder Cron Trigger

infrastructure medium

Dependencies (1)

Configure and register the pg_cron or Edge Function cron schedule that triggers the daily reminder evaluation cycle. Define the cron expression (e.g., daily at 06:00 UTC), register it in Supabase infrastructure, and wire it to invoke the ReminderSchedulerService endpoint. Ensure the schedule is idempotent and can survive restarts without creating duplicate registrations. epic-assignment-follow-up-reminders-cron-infrastructure-task-001

Epic Risks (2)

high impact low prob technical

If the daily cron job takes longer than 24 hours to complete (due to a large dataset or a slow query), a second instance will start while the first is still running, causing duplicate reminder dispatch for assignments processed twice.

Mitigation & Contingency

Mitigation: Implement an advisory lock that prevents a second run from starting if the first is still active. Monitor run duration via the execution log table and alert if any run exceeds 30 minutes. The 10,000-assignment load test should verify the run completes in under 5 minutes.

Contingency: If a double-run occurs, the idempotency guard in ReminderDispatchService prevents duplicate notifications from being sent. The execution log identifies the overlap and allows the ops team to investigate the root cause.

high impact medium prob integration

If the activity registration hook that resets last_contact_date is implemented incorrectly or not triggered for all activity types (e.g., proxy registrations, bulk registrations), peer mentors will continue receiving reminders even after logging contact, damaging user trust.

Mitigation & Contingency

Mitigation: Audit all code paths that create activity records (direct registration, proxy registration, bulk registration, coordinator proxy) and ensure each path calls the assignment contact update. Write integration tests for each registration path asserting that last_contact_date is updated.

Contingency: Provide an authenticated admin endpoint that allows manual correction of last_contact_date for a specific assignment, enabling ops to resolve individual cases while the bug is fixed and deployed.

Quick Links

All Tasks Execution Plan