RPC Functions

All lead mutations go through Supabase RPC functions rather than direct table writes. This ensures atomicity — a single contact attempt might need to create an activity, a calendar event, and update the lead's appointment time, all as one transaction. RPC functions are SECURITY DEFINER, meaning they run with elevated permissions regardless of RLS.

log_contact_with_outcome

The primary function for logging outbound contact attempts. Called when an agent calls, texts, or emails a lead.

log_contact_with_outcome(
  p_lead_id    UUID,
  p_method     TEXT,      -- 'call', 'text', 'email'
  p_direction  TEXT,      -- 'outbound' or 'inbound'
  p_result     TEXT,      -- see result table below
  p_metadata   JSONB DEFAULT '{}'
)

Returns: JSONB { success, contact_activity_id, outcome_activity_id, event_id }

What it does

Always inserts a contact_attempted activity with { method, direction, result, ...metadata }.
Based on p_result, may insert a second activity and/or create a calendar event:

Result	Second activity	Calendar event
`no_answer`	none	none
`voicemail`	none	none
`sent`	none	none
`callback`	`callback_requested`	Creates callback event if `metadata.scheduled_for` provided
`scheduled`	`appointment_scheduled`	Creates `lead_appointment` calendar event
`refused`	`refused` (terminal)	none
`wrong_number`	`wrong_number` (terminal)	none
`bad_number`	`bad_number` (terminal)	none
`unresponsive`	`unresponsive` (terminal)	none
`duplicate`	`duplicate` (terminal)	none
`dnc`	`dnc` (terminal)	none

For scheduled, the appointment date comes from metadata.scheduled_for. If metadata.is_instant is true, starts_at is set to NOW().

log_incoming_contact

Wrapper for inbound contacts — when the lead reaches out to the agent.

log_incoming_contact(
  p_lead_id   UUID,
  p_method    TEXT,      -- 'call', 'text', 'email'
  p_result    TEXT,
  p_metadata  JSONB DEFAULT '{}'
)

Internally calls log_contact_with_outcome with direction = 'inbound'. Same result codes apply.

manage_appointment

Handles all appointment lifecycle actions.

manage_appointment(
  p_lead_id  UUID,
  p_action   TEXT,       -- see action table below
  p_date     TIMESTAMPTZ DEFAULT NULL,
  p_note     TEXT DEFAULT NULL
)

Returns: JSON { success: boolean, error?: string }

Actions

Action	What happens	Calendar event	Activity logged
`schedule`	Cancels any existing appointment, creates new `lead_appointment` event	New event with `status = 'scheduled'`	`appointment_scheduled`
`instant`	Same as schedule but `starts_at = NOW()`	New event	`appointment_scheduled` (with `is_instant: true`)
`cancel`	Cancels the most recent scheduled event	`status → 'cancelled'`	`appointment_cancel`
`no_show`	Marks the most recent scheduled event as no-show	`status → 'no_show'`	`appointment_no_show`
`completed`	Marks the most recent scheduled event as completed	`status → 'completed'`	`appointment_completed`

For schedule and instant, the function also sets leads.appt_at to the appointment time. Metadata includes appointment_type: 'presentation'.

submit_lead_presentation

The big one. Called when an agent completes a presentation. Handles everything atomically.

submit_lead_presentation(
  p_lead_id          UUID,
  p_agent_id         UUID,
  -- Primary demographics
  p_primary_age      SMALLINT,
  p_primary_sex      TEXT,
  p_primary_income   INTEGER,
  -- ... (all demographic fields)
  -- Outcome
  p_is_sale          BOOLEAN,
  p_primary_alp      NUMERIC,
  p_primary_ahp      NUMERIC,
  -- ... (all outcome fields)
  p_sale_details     JSONB,     -- array of {insured, product, amount, premium, waiver_of_premium}
  p_referrals        JSONB      -- array of {first_name, last_name, phone, state, type}
)

Returns: JSONB { success, lead_id, stage, sale_details_count, referrals_count }

What it does

Updates leads with all demographic and outcome fields
Sets sold_at = NOW() if p_is_sale = true
Deletes existing lead_sale_details rows, inserts new ones from p_sale_details
Inserts presentation_ended activity
Inserts sale or no_sale activity (1 second later so it sorts after presentation_ended)
Updates the most recent calendar_events row to status = 'completed'
For each referral in p_referrals, inserts a new lead with referral_lead_id pointing back to the parent

create_lead_export

Creates an export record and marks leads as exported.

create_lead_export(
  p_lead_ids  UUID[],
  p_filters   JSONB DEFAULT '{}',
  p_filename  TEXT DEFAULT NULL
)

Returns: JSONB { success, export_id, lead_count }

Inserts a lead_exports row and bulk-updates leads.export_id for all provided lead IDs.

cancel_appointment / mark_appointment_no_show

Standalone functions that operate by appointment ID rather than lead ID.

cancel_appointment(p_appointment_id UUID, p_note TEXT DEFAULT NULL)
mark_appointment_no_show(p_appointment_id UUID, p_note TEXT DEFAULT NULL)

These update the calendar_events status and insert the corresponding activity. Used when managing appointments from the calendar view rather than from the lead detail panel.

get_lead_reports_aggregate

Aggregates daily reports for the WAR report view.

get_lead_reports_aggregate(
  p_user_ids    UUID[],
  p_start_date  DATE,
  p_end_date    DATE
)

Returns: JSONB { activity: [...], metrics: [...] }

activity: One row per agent, summed over the date range (contacts, leads worked, appointments, etc.)
metrics: One row per (agent_id, lead_type, state) with presentations, sales, premiums

RPC Functions

On this page