Triggers & Metrics

The lead system uses PostgreSQL triggers to keep derived state in sync. Every time an activity is logged, four triggers fire. Every time a lead field changes, an audit trigger fires.

Triggers on lead_activity INSERT

These fire in this order on every new lead_activity row:

1. update_lead_status_based_on_activity

Purpose: Advances leads.stage and leads.status based on the activity type.

This is the state machine engine. See State Machine for the full transition rules.

Key behavior:

If status = 'resolved', exits immediately (no-op)
Reads the current stage, applies the transition for the new activity type
Sets both stage and status in a single UPDATE
Terminal outcomes set status = 'resolved'

2. update_lead_action_tracking

Purpose: Sets leads.last_activity_at = NEW.created_at.

This timestamp is used for sorting leads by recency and for the "needs attention" filter (leads assigned to inactive agents).

3. update_lead_metrics

Purpose: Increments counters and timestamps on the lead_metrics row for this lead.

Activity type	What it updates
`contact_attempted`	`contact_attempts += 1`, sets `first_contact_at` and `time_to_first_contact` if first
`appointment_scheduled`	`appointments_scheduled += 1`, sets `first_appointment_at` and `time_to_first_appointment` if first
`appointment_no_show`	`appointments_no_show += 1`
`presentation_started`	`presentations_given += 1`, sets `first_presentation_at` and `time_to_first_presentation` if first
Terminal outcomes (`sale`, `no_sale`, `refused`, etc.)	Sets `outcome`, `outcome_at`, `time_to_outcome`, `time_contact_to_outcome`, `time_appointment_to_outcome`

The time_to_* intervals measure elapsed time from leads.created_at to the event timestamp. The time_contact_to_outcome and time_appointment_to_outcome intervals measure from first contact/appointment to outcome.

4. update_lead_report_on_activity

Purpose: Upserts the agent's daily lead_reports row and increments the relevant counters.

Activity type	Counter incremented
`contact_attempted`	`contacts_made`
`contact_attempted` (unique lead per day)	`leads_worked`
`callback_requested`, `appointment_scheduled` (reached outcomes)	`leads_reached` (unique lead per day)
`appointment_scheduled`	`appts_scheduled` or `appts_instant` based on metadata

Uses ON CONFLICT (agent_id, date) DO UPDATE for atomic upsert.

Triggers on leads UPDATE

log_lead_changes

Purpose: Inserts a lead_updated activity row when tracked fields change.

Compares OLD and NEW values for every demographic and contact field. Produces a metadata.changes array like:

{
  "changes": [
    { "field": "phone", "old": "555-0100", "new": "555-0199" },
    { "field": "primary_age", "old": 45, "new": 46 }
  ]
}

Skipped when: status or stage changes (those are handled by the activity trigger chain, not field edits).

update_lead_report_on_sale

Purpose: Adjusts lead_report_metrics when sale outcome fields change (ALP, AHP, is_sale, etc.).

This handles cases where a manager edits sale details after the fact — the daily report numbers stay accurate.

Triggers on lead_sale_details INSERT/UPDATE/DELETE

log_sale_details_changes

Purpose: Inserts a sale_updated activity for audit trail whenever a sale detail row is added, modified, or removed.

Metadata includes action ('added', 'modified', or 'removed'), plus the product, insured, and premium details.

Trigger on calendar_events UPDATE

calendar_events_updated_at

Purpose: Sets updated_at = now() before every update. Standard timestamp maintenance.

How metrics flow to reporting

Agent takes action
  → lead_activity INSERT
  → Trigger 3: lead_metrics updated (per-lead lifetime stats)
  → Trigger 4: lead_reports updated (per-agent daily stats)

Agent views WAR Report
  → get_lead_reports_aggregate RPC called
  → Aggregates lead_reports + lead_report_metrics for date range
  → Returns combined tracked + manual numbers

The WAR report shows both tracked (automatic from triggers) and manual (agent-entered) numbers side by side. This lets agents account for work done outside the app (personal phone calls, in-person meetings) while still getting automatic credit for everything done within Onsidian.

Triggers & Metrics

On this page