Database Schema

leads

The primary table. One row per prospect.

Core fields

Column	Type	Required	Default	Notes
`id`	uuid	yes	`gen_random_uuid()`	PK
`created_at`	timestamptz	yes	`NOW()`
`first_name`	text	yes
`last_name`	text	no
`phone`	text	yes
`email`	text	no
`address`	text	no
`city`	text	no
`state`	text	no		US state abbreviation
`zip`	text	no
`language`	text	no	`'en'`
`source`	text	yes		`'onsidian'` or `'manual'`
`type`	text	yes		FK to `lead_types.id`
`status`	text	yes	`'open'`	`'open'` or `'resolved'`
`stage`	text	no	`'new'`	Workflow position — see State Machine

Ownership

Column	Type	Notes
`agency_id`	uuid	FK to `agencies.id`
`agent_id`	uuid	FK to `profiles.id` — the assigned agent
`referral_lead_id`	uuid	Self-referencing FK — the parent lead that generated this referral
`export_id`	uuid	FK to `lead_exports.id`, ON DELETE SET NULL
`external_id`	uuid	ID from the source system (for deduplication on import)
`is_training`	boolean	Training leads don't count toward real metrics
`last_activity_at`	timestamptz	Set by trigger on every new activity

Demographics — primary insured

Column	Type	Notes
`primary_sex`	text
`primary_age`	smallint
`primary_income`	integer
`primary_work_coverage`	integer	Employer-provided coverage amount
`primary_term_coverage`	integer
`primary_whole_coverage`	integer
`primary_health_dq`	boolean	Disqualified for health insurance
`primary_life_dq`	boolean	Disqualified for life insurance
`primary_nicotine`	boolean

Demographics — secondary insured

Same pattern as primary: secondary_first_name, secondary_last_name, secondary_sex, secondary_age, secondary_income, secondary_work_coverage, secondary_term_coverage, secondary_whole_coverage, secondary_health_dq, secondary_life_dq, secondary_nicotine. Plus has_secondary boolean (default false).

Demographics — family

Column	Type	Default
`num_children`	smallint	`0`
`num_grandchildren`	smallint	`0`
`has_mortgage`	boolean
`has_bank_account`	boolean

Outcome / sale fields

These are written by submit_lead_presentation and represent the aggregate outcome.

Column	Type	Notes
`is_sale`	boolean	Default `false`
`sold_at`	timestamptz	Set atomically by presentation submission
`primary_is_trial`	boolean	Trial sale (free-look period)
`primary_alp`	numeric	Annual Life Premium
`primary_ahp`	numeric	Annual Health Premium
`secondary_is_trial`	boolean
`secondary_alp`	numeric
`secondary_ahp`	numeric

RLS policies

SELECT/INSERT/UPDATE: agency_id = get_user_agency() OR is_super_user()
DELETE: No policy — leads cannot be deleted

Indexes

agent_id, agency_id, status, stage, is_sale, sold_at, external_id, export_id, last_activity_at

lead_activity

Every action taken on a lead is an activity row. This is the audit trail and the trigger source for all automatic state transitions.

Column	Type	Required	Default	Notes
`id`	bigint	yes	identity	PK, auto-increment
`created_at`	timestamptz	yes	`NOW()`
`lead_id`	uuid	yes		FK to `leads.id`
`agent_id`	uuid	no		FK to `profiles.id`
`type`	text	yes		Activity type (see below)
`metadata`	jsonb	no		Flexible payload — replaced the old `note` column

Activity types

Type	When logged	Key metadata
`created`	Lead created
`contact_attempted`	Agent contacts lead	`method`, `direction`, `result`, `note`
`callback_requested`	Lead wants a callback	`scheduled_for?`, `note`
`appointment_scheduled`	Appointment booked	`appointment_type`, `is_instant`, `note`
`appointment_cancel`	Appointment cancelled	`note`
`appointment_no_show`	Lead didn't show	`note`
`appointment_completed`	Appointment finished	`note`
`presentation_started`	Presentation opened
`presentation_paused`	Presentation paused
`presentation_resumed`	Presentation resumed
`presentation_ended`	Presentation submitted
`sale`	Sale recorded
`no_sale`	No sale recorded
`refused`	Lead refused	`note`
`wrong_number`	Wrong number	`note`
`bad_number`	Bad/disconnected number	`note`
`unresponsive`	Lead stopped responding	`note`
`duplicate`	Duplicate lead	`note`
`dnc`	Do Not Contact	`note`
`lead_updated`	Field changed on lead	`changes: [{field, old, new}]`
`sale_updated`	Sale detail changed	`action`, `product`, `insured`, `premium`
`note`	Manual note added	`note`
`assigned`	Lead reassigned

RLS

SELECT/INSERT: lead's agency_id must match the user's agency.

lead_sale_details

Individual policy details for a sale. Multiple rows per lead (one per product sold).

Column	Type	Notes
`id`	bigint	PK, identity
`created_at`	timestamptz
`lead_id`	uuid	FK to `leads.id`, ON DELETE CASCADE
`insured`	text	`'primary'`, `'secondary'`, `'child_0'`, `'child_1'`, etc.
`product`	text	FK to `products.id`
`amount`	integer	Coverage amount
`premium`	numeric
`waiver_of_premium`	boolean	Default `false`

All rows are deleted and re-inserted atomically by submit_lead_presentation to handle re-submissions cleanly.

calendar_events

Stores all calendar events including lead appointments. Replaced the old appointments table.

Column	Type	Notes
`id`	uuid	PK
`agency_id`	uuid	FK to `agencies.id`
`owner_id`	uuid	FK to `profiles.id` — the agent who owns this event
`event_type`	text	`'lead_appointment'`, `'training'`, `'meeting'`, `'other'`, `'recruit_interview'`, `'group_interview'`
`title`	text
`description`	text
`starts_at`	timestamptz
`ends_at`	timestamptz
`location`	text
`status`	text	`'scheduled'`, `'completed'`, `'cancelled'`, `'no_show'`, `'rescheduled'`
`lead_id`	uuid	FK to `leads.id`, only set for `lead_appointment` type
`rrule`	text	RFC 5545 recurrence rule (null = one-time)
`rrule_until`	timestamptz	Precomputed end date for range queries
`rrule_exceptions`	timestamptz[]	Dates to skip
`recurring_event_id`	uuid	Self-referencing FK for exception instances

RLS

SELECT: owner or same agency
INSERT/UPDATE/DELETE: owner only

lead_exports

Tracks CSV export history for auditing and re-download.

Column	Type	Notes
`id`	uuid	PK
`created_at`	timestamptz
`user_id`	uuid	FK to `profiles.id` — who exported
`agency_id`	uuid	FK to `agencies.id`
`lead_count`	integer
`filters`	jsonb	The filter criteria used
`filename`	text

lead_metrics

One row per lead, auto-maintained by triggers. Used for reporting and analytics.

Column	Type	Notes
`id`	uuid	PK, FK to `leads.id` ON DELETE CASCADE
`contact_attempts`	integer	Default `0`
`first_contact_at`	timestamptz
`time_to_first_contact`	interval
`appointments_scheduled`	integer	Default `0`
`appointments_no_show`	integer	Default `0`
`first_appointment_at`	timestamptz
`time_to_first_appointment`	interval
`presentations_given`	integer	Default `0`
`first_presentation_at`	timestamptz
`time_to_first_presentation`	interval
`outcome`	text	Mirrors the terminal activity type
`outcome_at`	timestamptz
`time_to_outcome`	interval
`time_contact_to_outcome`	interval
`time_appointment_to_outcome`	interval

lead_reports / lead_report_metrics

Daily activity roll-ups per agent. lead_reports has one row per (agent, date). lead_report_metrics breaks it down further by (report, lead_type, state).

lead_reports

Column	Type	Notes
`id`	bigint	PK
`agent_id`	uuid	FK to `profiles.id`
`date`	date	Unique with agent_id
`contacts_made`	integer	Auto-tracked
`leads_worked`	integer	Auto-tracked
`leads_reached`	integer	Auto-tracked
`appts_scheduled`	integer	Auto-tracked
`appts_instant`	integer	Auto-tracked
`recruits`	integer	Auto-tracked
`manual_contacts_made`	integer	Agent self-reported
`manual_leads_worked`	integer	Agent self-reported
`manual_leads_reached`	integer	Agent self-reported
`manual_appts_scheduled`	integer	Agent self-reported
`manual_appts_instant`	integer	Agent self-reported

lead_report_metrics

Column	Type	Notes
`report_id`	bigint	FK to `lead_reports.id`
`lead_type`	text	FK to `lead_types.id`
`state`	text	US state
`presentations`	integer
`sales`	integer
`trial_sales`	integer
`referrals`	integer
`alp`	numeric
`trial_alp`	numeric
`ahp`	numeric
`trial_ahp`	numeric

Unique constraint: (report_id, lead_type, state).