Encore OS — Documentation

Generated by npm run db:docs:generate — DO NOT EDIT BY HAND. Change the schema via a migration, then regenerate.

Core: ce_ · Tables: 64 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`ce_activities`

CE-04: Activity log for contacts and partners RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`activity_type`	text	no
`subject_type`	text	no
`subject_id`	uuid	no
`subject`	text	yes
`description`	text	yes
`activity_date`	timestamp with time zone	no	now()
`duration_minutes`	integer	yes
`call_id`	uuid	yes
`outcome`	text	yes
`follow_up_date`	timestamp with time zone	yes
`follow_up_task_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

call_id → ce_calls(id)
created_by → pf_profiles(id)
follow_up_task_id → pf_tasks(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_activities_delete (DELETE)
ce_activities_insert (INSERT)
ce_activities_select (SELECT)
ce_activities_update (UPDATE)

`ce_calendar_connections`

CE-21: User OAuth connections to external calendar providers (Google, Microsoft) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	no
`provider`	text	no
`provider_account_id`	text	no
`access_token_encrypted`	text	no
`refresh_token_encrypted`	text	no
`token_expires_at`	timestamp with time zone	yes
`calendar_ids`	jsonb	yes	’[]‘::jsonb	JSONB array of selected calendars, max 10
`sync_enabled`	boolean	no	true
`last_sync_at`	timestamp with time zone	yes
`sync_token`	text	yes		Google syncToken / Microsoft deltaLink for incremental sync
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`access_token_credential_id`	uuid	yes		PF-76 credential vault id for OAuth access token. Source of truth once migration_required = false.
`refresh_token_credential_id`	uuid	yes		PF-76 credential vault id for OAuth refresh token.
`gws_connection_id`	uuid	yes		Optional link to the tenant-level pf_google_workspace_connections row that supplied the OAuth client credentials.
`migration_required`	boolean	no	true	TRUE when legacy plaintext token columns still need to be backfilled into PF-76 vault. Cleared by the calendar-token-backfill edge function.
`migrated_at`	timestamp with time zone	yes

Foreign keys:

access_token_credential_id → pf_credential_vault(id)
created_by → pf_profiles(id)
gws_connection_id → pf_google_workspace_connections(id)
organization_id → pf_organizations(id)
refresh_token_credential_id → pf_credential_vault(id)
updated_by → pf_profiles(id)
user_id → pf_profiles(id)

Policies:

ce_calendar_connections_delete (DELETE)
ce_calendar_connections_insert (INSERT)
ce_calendar_connections_select (SELECT)
ce_calendar_connections_update (UPDATE)

`ce_calendar_events`

CE-21: Mapping between external calendar events and CE activities RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`calendar_connection_id`	uuid	no
`external_event_id`	text	no
`activity_id`	uuid	yes
`title`	text	yes
`start_time`	timestamp with time zone	no
`end_time`	timestamp with time zone	no
`location`	text	yes
`attendee_emails`	jsonb	yes	’[]‘::jsonb
`sync_direction`	text	no	’inbound’::text	inbound = from external calendar, outbound = created in CE
`sync_status`	text	no	’synced’::text
`last_synced_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

activity_id → ce_activities(id)
calendar_connection_id → ce_calendar_connections(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_calendar_events_delete (DELETE)
ce_calendar_events_insert (INSERT)
ce_calendar_events_select (SELECT)
ce_calendar_events_update (UPDATE)

`ce_calls`

CE-03: Call records from RingCentral webhooks and manual entry. All calls should be recorded for compliance. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`external_call_id`	text	yes		RingCentral telephonySessionId. Used for upsert deduplication.
`external_provider`	text	yes	’ringcentral’::text
`external_party_id`	text	yes
`recording_url`	text	yes		URL to playback recording. Requires RingCentral auth via recording-proxy edge function.
`recording_id`	text	yes
`recording_duration_seconds`	integer	yes
`recording_status`	text	yes	’not_recorded’::text
`has_recording`	boolean	yes	false	CRITICAL: All calls should be recorded. Check recording_status for availability.
`direction`	text	no
`from_number`	text	no
`to_number`	text	no
`start_time`	timestamp with time zone	no
`end_time`	timestamp with time zone	yes
`duration_seconds`	integer	yes
`call_result`	text	yes
`call_action`	text	yes
`termination_reason`	text	yes
`disposition`	text	yes
`disposition_notes`	text	yes
`dispositioned_at`	timestamp with time zone	yes
`dispositioned_by`	uuid	yes
`contact_id`	uuid	yes
`partner_id`	uuid	yes
`user_id`	uuid	yes
`outcome_summary`	text	yes
`follow_up_required`	boolean	yes	false
`follow_up_date`	timestamp with time zone	yes
`follow_up_task_id`	uuid	yes
`is_manual`	boolean	yes	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
dispositioned_by → pf_profiles(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)
user_id → pf_profiles(id)

Policies:

ce_calls_insert (INSERT)
ce_calls_select (SELECT)
ce_calls_update (UPDATE)

`ce_campaigns`

Marketing campaigns for tracking outreach, awareness, and referral drives RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`campaign_type`	text	no	’awareness’::text
`status`	text	no	’draft’::text
`start_date`	date	yes
`end_date`	date	yes
`budget`	numeric(12,2)	yes
`actual_spend`	numeric(12,2)	yes	0
`lead_goal`	integer	yes
`conversion_goal`	integer	yes
`utm_source`	text	yes
`utm_medium`	text	yes
`utm_campaign`	text	yes
`leads_generated`	integer	yes	0
`conversions`	integer	yes	0
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

ce_campaigns_delete (DELETE)
ce_campaigns_insert (INSERT)
ce_campaigns_select (SELECT)
ce_campaigns_update (UPDATE)

`ce_consent_evidence`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`consent_type`	text	no
`action`	text	no
`consent_method`	text	no
`ip_address`	inet	yes
`user_agent`	text	yes
`consent_text`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

ce_consent_evidence_insert (INSERT)
ce_consent_evidence_select (SELECT)

`ce_contact_audit_log`

CE-60: Immutable contact activity/audit timeline rows. Append-only via SECURITY DEFINER helper ce_log_contact_audit_event. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`category`	text	no
`event_type`	text	no
`event_source`	text	no
`source_record_id`	uuid	yes
`field_name`	text	yes
`old_value_redacted`	text	yes
`new_value_redacted`	text	yes
`details`	jsonb	no	’{}‘::jsonb
`summary`	text	no
`event_at`	timestamp with time zone	no	now()
`actor_id`	uuid	yes
`actor_type`	text	no	’user’::text
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

actor_id → pf_profiles(id)
contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

ce_contact_audit_log_insert_denied (INSERT)
ce_contact_audit_log_select (SELECT)

`ce_contact_document_extraction_audit`

CE-59 Phase 2 (CAC-6): Append-only audit of AI extraction outcomes. Records every Apply Confirmed and every blocked attempt (Part 2 / redaction). RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`document_id`	uuid	no
`actor_user_id`	uuid	no
`outcome`	text	no
`confirmed_fields`	jsonb	no	’[]‘::jsonb
`reason`	text	yes
`occurred_at`	timestamp with time zone	no	now()

Foreign keys:

contact_id → ce_contacts(id)
document_id → ce_contact_documents(id)
organization_id → pf_organizations(id)

Policies:

ce_doc_extraction_audit_insert (INSERT)
ce_doc_extraction_audit_select (SELECT)

`ce_contact_documents`

CE-59: Contact-attached documents. Soft-deleted via deleted_at. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`document_name`	text	no
`document_type`	text	no
`file_path`	text	no
`file_size_bytes`	bigint	no
`mime_type`	text	no
`uploaded_at`	timestamp with time zone	no	now()
`ai_extraction_status`	text	yes
`ai_extraction_data`	jsonb	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Org-specific metadata.
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp. NULL = active.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`ai_suggestions`	jsonb	yes		CE-59 Phase 2: Last extraction suggestions (transient; cleared after Apply Confirmed). Never written to ce_contacts without explicit user confirmation.
`ai_redaction_confidence`	numeric(4,3)	yes		CE-59 Phase 2: Composite redaction confidence score 0..1 from preflight; <0.95 fails closed.

Foreign keys:

contact_id → ce_contacts(id)
created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

ce_contact_documents_delete (DELETE)
ce_contact_documents_insert (INSERT)
ce_contact_documents_select (SELECT)
ce_contact_documents_update (UPDATE)

`ce_contact_relationships`

CE-20: Directed contact-to-contact relationship edges with soft-delete. Two rows per bi-directional relationship. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`related_contact_id`	uuid	no
`relationship_type`	text	no		Forward label from PF-15 picklist ce_contact_relationship_type (family, professional, organizational, referral, other)
`inverse_relationship_type`	text	yes		Reverse label stored on the paired row for display clarity
`strength`	text	yes	’medium’::text	Relationship strength: strong, medium, weak
`notes`	text	yes
`start_date`	date	yes
`end_date`	date	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted.

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
related_contact_id → ce_contacts(id)
updated_by → pf_profiles(id)

Policies:

ce_contact_relationships_delete (DELETE)
ce_contact_relationships_insert (INSERT)
ce_contact_relationships_select (SELECT)
ce_contact_relationships_update (UPDATE)

`ce_contact_segments`

Dynamic contact segments based on criteria filters RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`criteria`	jsonb	no	’{}‘::jsonb
`member_count`	integer	yes	0
`last_calculated_at`	timestamp with time zone	yes
`is_active`	boolean	no	true
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

ce_contact_segments_delete (DELETE)
ce_contact_segments_insert (INSERT)
ce_contact_segments_select (SELECT)
ce_contact_segments_update (UPDATE)

`ce_contact_tag_assignments`

CE-61: Many-to-many assignment of tags to contacts. Append/remove only; no updates. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`tag_id`	uuid	no
`assigned_at`	timestamp with time zone	no	now()
`assigned_by`	uuid	yes

Foreign keys:

assigned_by → pf_profiles(id)
contact_id → ce_contacts(id)
organization_id → pf_organizations(id)
tag_id → ce_contact_tags(id)

Policies:

ce_tag_assignments_delete (DELETE)
ce_tag_assignments_insert (INSERT)
ce_tag_assignments_select (SELECT)

`ce_contact_tags`

CE-61: Organization-scoped tag definitions applied to contacts (e.g., High Acuity, Do Not Admit). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`color`	text	no	’#6B7280’::text
`is_alert`	boolean	no	false
`icon`	text	yes
`description`	text	yes
`sort_order`	integer	no	0
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_tags_delete (DELETE)
ce_tags_insert (INSERT)
ce_tags_select (SELECT)
ce_tags_update (UPDATE)

`ce_contacts`

CE module contacts - leads, referral contacts, prospects, and community members RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`first_name`	text	no
`last_name`	text	no
`email`	text	yes
`phone`	text	yes
`mobile_phone`	text	yes
`contact_type`	text	no	’lead’::text
`status`	text	no	’active’::text
`source_type`	text	yes
`source_details`	text	yes
`referral_source_id`	uuid	yes		FK to ce_referral_sources - constraint added in CE-02 migration
`address_line_1`	text	yes
`address_line_2`	text	yes
`city`	text	yes
`state`	text	yes
`zip_code`	text	yes
`partner_id`	uuid	yes		FK to ce_partners - constraint added in CE-02 migration
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“preferred_contact_method”: “phone”, “insurance_type”: “medicaid”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`date_of_birth`	date	yes		CE-57: HIPAA-quasi identifier used for duplicate detection and demographic reporting. Read access additionally gated by ce.contacts.view_dob; never log raw values.
`gender`	text	yes		CE-57: Backed by ce_gender_options PF-15 picklist. Examples: Male, Female, Non-binary.
`preferred_pronouns`	text	yes		CE-57: Free text. Examples: she/her, they/them.
`contact_category`	text	no	’lead’::text	CE-57: Coarse partition derived from contact_type. lead=potential patient pipeline; community=partner liaison/referral contact. Maintained automatically via trigger ce_contacts_sync_contact_category.
`insurance_carrier`	text	yes		CE-65: Insurance carrier display name (e.g., “BCBS AZ”). Not masked but only edited with ce.contacts.insurance.edit.
`insurance_member_id`	text	yes		CE-65: PHI. Insurance member ID. UI masks except in permission-gated detail (ce.contacts.insurance.view_detail). Never logged/telemetered in cleartext.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
partner_id → ce_partners(id)
referral_source_id → ce_referral_sources(id)

Policies:

ce_contacts_delete (DELETE)
ce_contacts_insert (INSERT)
ce_contacts_select (SELECT)
ce_contacts_update (UPDATE)

`ce_contract_renewal_log`

CE-24: Append-only audit log of contract renewal actions and notification dispatch. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contract_id`	uuid	no
`action`	text	no
`previous_status`	text	no
`new_status`	text	yes
`notes`	text	yes
`notification_window`	integer	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

contract_id → ce_partner_contracts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

ce_contract_renewal_log_insert (INSERT)
ce_contract_renewal_log_select (SELECT)

`ce_crisis_alerts`

CE-28-EN-01: Suspected crisis interactions detected from inbound channels. PHI-safe (excerpt is redacted; payloads/events use IDs only). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`source_kind`	text	no
`source_id`	uuid	yes
`contact_id`	uuid	yes
`screening_attempt_id`	uuid	yes
`model_version`	text	no		CE-28-EN-01: Keyword-set or model version snapshot at detection time (governance/auditability).
`confidence`	numeric(4,3)	no
`confidence_tier`	text	no		CE-28-EN-01: tier_1 = exact-match (pages on-call); tier_2 = fuzzy/partial (queue only).
`detected_keywords`	text[]	no	’{}‘::text[]
`evidence_excerpt`	text	yes		CE-28-EN-01: 50-char PHI-redacted excerpt around triggering token. Surrounding content replaced with [REDACTED].
`routed_to`	uuid	yes
`acknowledged_at`	timestamp with time zone	yes
`resolved_at`	timestamp with time zone	yes
`outcome`	text	yes
`reviewer_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
reviewer_id → pf_profiles(id)
routed_to → pf_profiles(id)
screening_attempt_id → ce_screening_attempts(id)
updated_by → pf_profiles(id)

Policies:

ce_crisis_alerts_insert (INSERT)
ce_crisis_alerts_select (SELECT)
ce_crisis_alerts_update (UPDATE)

`ce_email_accounts`

CE-07: OAuth credentials for Gmail/Outlook email integrations RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	no
`provider`	text	no
`email_address`	text	no
`display_name`	text	yes
`access_token`	text	no
`refresh_token`	text	yes
`token_expires_at`	timestamp with time zone	yes
`sync_status`	text	no	’pending’::text
`last_sync_at`	timestamp with time zone	yes
`last_sync_error`	text	yes
`sync_cursor`	text	yes
`is_active`	boolean	no	true
`sync_enabled`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`access_token_vault_id`	uuid	yes		PF-75: Reference to encrypted credential in pf_credential_vault
`refresh_token_vault_id`	uuid	yes		PF-75: Reference to encrypted credential in pf_credential_vault

Foreign keys:

access_token_vault_id → pf_credential_vault(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
refresh_token_vault_id → pf_credential_vault(id)
updated_by → pf_profiles(id)
user_id → pf_profiles(id)

Policies:

ce_email_accounts_delete (DELETE)
ce_email_accounts_insert (INSERT)
ce_email_accounts_select (SELECT)
ce_email_accounts_update (UPDATE)

`ce_email_messages`

CE-07: Synced email messages with contact/lead/partner matching RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`email_account_id`	uuid	no
`contact_id`	uuid	yes
`partner_id`	uuid	yes
`lead_id`	uuid	yes
`provider_message_id`	text	no
`thread_id`	text	yes
`direction`	text	no
`subject`	text	yes
`from_address`	text	no
`from_name`	text	yes
`to_addresses`	jsonb	no	’[]‘::jsonb
`cc_addresses`	jsonb	yes	’[]‘::jsonb
`bcc_addresses`	jsonb	yes	’[]‘::jsonb
`reply_to_address`	text	yes
`body_text`	text	yes
`body_html`	text	yes
`body_preview`	text	yes
`has_attachments`	boolean	yes	false
`attachment_count`	integer	yes	0
`tracking_id`	uuid	yes
`open_count`	integer	yes	0
`click_count`	integer	yes	0
`first_opened_at`	timestamp with time zone	yes
`last_opened_at`	timestamp with time zone	yes
`sent_at`	timestamp with time zone	no
`received_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
email_account_id → ce_email_accounts(id)
lead_id → ce_leads(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)

Policies:

ce_email_messages_delete (DELETE)
ce_email_messages_insert (INSERT)
ce_email_messages_select (SELECT)
ce_email_messages_update (UPDATE)

`ce_email_templates`

CE-07: Admin-managed email templates for common communications RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`category`	text	no	’general’::text
`subject_template`	text	no
`body_template`	text	no
`body_html_template`	text	yes
`available_merge_fields`	jsonb	yes	’[]‘::jsonb
`is_active`	boolean	no	true
`is_default`	boolean	yes	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_email_templates_delete (DELETE)
ce_email_templates_insert (INSERT)
ce_email_templates_select (SELECT)
ce_email_templates_update (UPDATE)

`ce_email_tracking_events`

CE-07: Email open/click tracking events (audit table) RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`email_message_id`	uuid	no
`event_type`	text	no
`event_data`	jsonb	yes	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`event_hash`	text	yes

Foreign keys:

email_message_id → ce_email_messages(id)
organization_id → pf_organizations(id)

Policies:

ce_email_tracking_events_select (SELECT)

`ce_event_attendees`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`event_id`	uuid	no
`contact_id`	uuid	yes
`first_name`	text	no
`last_name`	text	no
`email`	text	yes
`phone`	text	yes
`registration_status`	text	no	’registered’::text
`checked_in_at`	timestamp with time zone	yes
`checked_in_by`	uuid	yes
`notes`	text	yes
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

checked_in_by → pf_profiles(id)
contact_id → ce_contacts(id)
created_by → pf_profiles(id)
event_id → ce_events(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_event_attendees_delete (DELETE)
ce_event_attendees_insert (INSERT)
ce_event_attendees_select (SELECT)
ce_event_attendees_update (UPDATE)

`ce_events`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`event_type`	text	no	’other’::text
`status`	text	no	’scheduled’::text
`start_datetime`	timestamp with time zone	no
`end_datetime`	timestamp with time zone	yes
`timezone`	text	no	’America/New_York’::text
`location_name`	text	yes
`address`	text	yes
`is_virtual`	boolean	no	false
`virtual_link`	text	yes
`campaign_id`	uuid	yes
`max_attendees`	integer	yes
`registered_count`	integer	no	0
`attended_count`	integer	no	0
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

campaign_id → ce_campaigns(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_events_delete (DELETE)
ce_events_insert (INSERT)
ce_events_select (SELECT)
ce_events_update (UPDATE)

`ce_insurance_access_audit`

CE-65 / CAC-006: Append-only audit of unmasked insurance member-ID views and insurance field edits. NEVER stores cleartext insurance_member_id. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`actor_user_id`	uuid	no
`action`	text	no
`occurred_at`	timestamp with time zone	no	now()

Foreign keys:

contact_id → ce_contacts(id)
organization_id → pf_organizations(id)

Policies:

ce_insurance_audit_insert (INSERT)
ce_insurance_audit_no_delete (DELETE)
ce_insurance_audit_no_update (UPDATE)
ce_insurance_audit_select (SELECT)

`ce_intake_oncall_rotations`

CE-28-EN-02: After-hours intake on-call rotations with escalation ladder. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`rotation_name`	text	no
`members`	jsonb	no	’[]‘::jsonb	CE-28-EN-02: JSON array of {user_id, priority, label?} sorted by priority ascending (1 = primary).
`schedule`	jsonb	no	’{}‘::jsonb
`escalation_minutes`	integer[]	no	ARRAY[15, 30]	CE-28-EN-02: Wait minutes before escalating to next priority. Length should be members.length - 1.
`active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_intake_oncall_rotations_delete (DELETE)
ce_intake_oncall_rotations_insert (INSERT)
ce_intake_oncall_rotations_select (SELECT)
ce_intake_oncall_rotations_update (UPDATE)

`ce_lead_assignment_queue`

CE-10: Round-robin lead assignment queue configuration RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	no
`web_form_id`	uuid	yes
`queue_order`	integer	no	0
`is_active`	boolean	no	true
`last_assigned_at`	timestamp with time zone	yes
`assignment_count`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
user_id → pf_profiles(id)
web_form_id → ce_web_forms(id)

Policies:

ce_lead_assignment_queue_delete (DELETE)
ce_lead_assignment_queue_insert (INSERT)
ce_lead_assignment_queue_select (SELECT)
ce_lead_assignment_queue_update (UPDATE)

`ce_lead_closures`

CE-63: Immutable closure facts for a lead (append-only). updated_* mirror constitution audit defaults; application must not issue UPDATE. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`lead_id`	uuid	no
`closure_reason`	text	no		Code key aligned to ce_closure_reasons / PF-15 picklist.
`comments`	text	yes
`referral_given`	boolean	no	false
`outbound_partner_id`	uuid	yes		References ce_partners.id (no FK — application-layer validation). Partner we referred the person to.
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
lead_id → ce_leads(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_lead_closures_insert (INSERT)
ce_lead_closures_select (SELECT)

`ce_lead_conversions`

CE-29: Audit trail for lead-to-patient/resident conversions. INSERT-only. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`lead_id`	uuid	no
`conversion_type`	text	no
`patient_id`	uuid	yes
`resident_id`	uuid	yes		Plain UUID — no FK until RH-01 creates rh_residents. Add FK via ALTER TABLE when available.
`correlation_id`	text	no		Idempotency key. Format: {leadId}to{type}_{timestamp}
`converted_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

converted_by → users(id)
created_by → users(id)
lead_id → ce_leads(id)
organization_id → pf_organizations(id)
patient_id → pm_patients(id)

Policies:

ce_lead_conversions_delete_deny (DELETE)
ce_lead_conversions_insert (INSERT)
ce_lead_conversions_select (SELECT)
ce_lead_conversions_update_deny (UPDATE)

`ce_lead_field_audit`

CE-58: Per-field audit trail for ce_leads intake fields (urgency, requested_services, etc.). One row per changed field, written by the ce_leads_field_audit_trigger. RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`lead_id`	uuid	no
`organization_id`	uuid	no
`action`	text	no
`field_name`	text	no		Audited column name (e.g. urgency, requested_services). Free-text PHI fields like notes are intentionally excluded.
`old_value`	jsonb	yes
`new_value`	jsonb	yes
`changed_by`	uuid	yes
`changed_at`	timestamp with time zone	no	now()

Foreign keys:

lead_id → ce_leads(id)
organization_id → pf_organizations(id)

Policies:

ce_lead_field_audit_select (SELECT)

`ce_lead_stages`

CE module lead pipeline stages - configurable per organization with auto-seeded defaults RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`stage_order`	integer	no
`stage_type`	text	no	’active’::text
`color`	text	yes
`is_default`	boolean	yes	false
`is_active`	boolean	yes	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific extensibility (platform standard)
`track_id`	uuid	no		CE-64: Pipeline track this stage belongs to.
`prompt_on_enter`	boolean	no	false	CE-63: When true, moving a lead onto this stage opens a dialog before committing the stage change.
`stage_behavior`	text	yes		CE-63: Optional behavior hint for prompt dialog selection (scheduled \| admitted \| not_admitted).

Foreign keys:

organization_id → pf_organizations(id)
track_id → ce_pipeline_tracks(id)

Policies:

ce_lead_stages_delete (DELETE)
ce_lead_stages_insert (INSERT)
ce_lead_stages_select (SELECT)
ce_lead_stages_update (UPDATE)

`ce_leads`

CE module leads - tracks potential residents/clients through pipeline stages RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`lead_stage_id`	uuid	yes
`status`	text	no	’new’::text
`source_type`	text	yes
`source_details`	text	yes
`referral_source_id`	uuid	yes		FK to ce_referral_sources - constraint added in CE-02 migration
`partner_id`	uuid	yes		FK to ce_partners - constraint added in CE-02 migration
`assigned_to`	uuid	yes
`expected_admission_date`	date	yes
`converted_at`	timestamp with time zone	yes
`converted_by`	uuid	yes
`converted_to_type`	text	yes		Type of entity lead was converted to (e.g., “resident” for RH module)
`converted_to_id`	uuid	yes		UUID of the created entity (e.g., rh_residents.id after conversion)
`lost_at`	timestamp with time zone	yes
`lost_reason`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“urgency”: “high”, “insurance_verified”: true})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp - null means active
`site_id`	uuid	yes		Optional site scoping for multi-site organizations
`campaign_id`	uuid	yes		Campaign that generated this lead
`utm_source`	text	yes		UTM source parameter (e.g., google, facebook)
`utm_medium`	text	yes		UTM medium parameter (e.g., cpc, email, referral)
`utm_campaign`	text	yes		UTM campaign parameter
`utm_term`	text	yes
`utm_content`	text	yes
`attribution_date`	timestamp with time zone	yes
`attribution_type`	text	yes		How attribution was determined: first_touch, last_touch, or manual
`urgency`	text	no	’medium’::text	CE-58: Triage urgency. One of low\|medium\|high\|critical. Default medium.
`requested_services`	text[]	no	’{}‘::text[]	CE-58: Service lines requested at intake. Constrained to active PF-15 picklist ce_requested_services for the row organization.
`track_id`	uuid	no		CE-64: Pipeline track this lead is assigned to. Exactly one track per lead.
`scheduled_at`	timestamp with time zone	yes		CE-63: Scheduled appointment datetime captured when moving to a stage with stage_behavior=scheduled.
`appointment_type`	text	yes		CE-63: PF-15 picklist key from ce_appointment_types.
`schedule_notes`	text	yes		CE-63: Optional free-text notes for the scheduled appointment.

Foreign keys:

assigned_to → pf_profiles(id)
campaign_id → ce_campaigns(id)
contact_id → ce_contacts(id)
converted_by → pf_profiles(id)
created_by → pf_profiles(id)
lead_stage_id → ce_lead_stages(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
track_id → ce_pipeline_tracks(id)
updated_by → pf_profiles(id)
partner_id → ce_partners(id)
referral_source_id → ce_referral_sources(id)

Policies:

ce_leads_delete (DELETE)
ce_leads_insert (INSERT)
ce_leads_select (SELECT)
ce_leads_update (UPDATE)

`ce_message_routing_rules`

CE-08-ENHANCEMENTS EN-05: outbound sender routing precedence rules by organization context. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`phone_number_id`	uuid	no		Identifier of provisioned CE sending number (resolved in CE phone number inventory surfaces).
`routing_type`	text	no
`routing_target_id`	uuid	no
`priority`	integer	no	100
`is_default`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for routing rules.
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_message_routing_rules_delete (DELETE)
ce_message_routing_rules_insert (INSERT)
ce_message_routing_rules_select (SELECT)
ce_message_routing_rules_update (UPDATE)

`ce_module_settings`

CE module configuration settings per organization RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`stale_lead_threshold_days`	integer	yes	30
`lead_auto_assignment_enabled`	boolean	yes	false
`default_lead_stage_id`	uuid	yes
`duplicate_detection_enabled`	boolean	yes	true
`contact_page_size`	integer	yes	50
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`ringcentral_enabled`	boolean	yes	false	Enable/disable RingCentral integration for the organization
`require_call_disposition`	boolean	yes	true	Require disposition for all calls before closing
`call_disposition_deadline_hours`	integer	yes	24	Hours before undispositioned call is flagged as overdue
`auto_create_follow_up_task`	boolean	yes	true	Automatically create task when follow-up is scheduled
`default_follow_up_days`	integer	yes	3	Default number of days for follow-up tasks
`call_recording_retention_days`	integer	yes	365	Days to retain call recordings (compliance)
`show_undispositioned_widget`	boolean	yes	true	Show pending calls widget on CE dashboard
`call_page_size`	integer	yes	25	Default page size for call log lists
`activity_page_size`	integer	yes	20	Number of activities per page in timeline view (10-100). Default: 20. Used by CE-04 Activity Timeline.
`activity_export_max_rows`	integer	yes	1000	Maximum rows allowed for activity CSV export (100-5000). Default: 1000. Prevents large exports for performance.
`default_dashboard_date_range_days`	integer	yes	30	CE-05: Default date range for dashboard widgets in days. Default: 30.
`pipeline_polling_interval_seconds`	integer	yes	60	CE-05: Dashboard polling interval in seconds (60-300). Default: 60.
`declining_success_rate_threshold`	integer	yes	20	CE-05: Percent drop to trigger partner success rate alert. Default: 20.
`declining_volume_threshold`	integer	yes	30	CE-05: Percent drop to trigger partner volume alert. Default: 30.
`referral_count_target`	integer	yes	5
`successful_admission_rate_target`	numeric(5,2)	yes	0.50
`average_quality_target`	numeric(3,2)	yes	3.50
`response_time_target_hours`	numeric(5,2)	yes	24.00
`email_sync_history_days`	integer	yes	30
`email_body_size_limit_kb`	integer	yes	500
`email_sync_interval_minutes`	integer	yes	15
`email_tracking_enabled`	boolean	yes	true
`email_custom_tracking_domain`	text	yes
`sms_provider`	text	yes	’ringcentral’::text
`sms_retention_days`	integer	yes	730
`sms_opt_out_footer`	text	yes	’Reply STOP to opt-out’::text
`sms_phi_detection_enabled`	boolean	yes	true
`sms_phi_detection_mode`	text	yes	’warn’::text
`ringcentral_sms_phone_number`	text	yes
`sms_business_hours_start`	text	yes	’09:00’::text
`sms_business_hours_end`	text	yes	’18:00’::text
`campaign_conversion_definition`	text	yes	’lead_converted’::text	What counts as a conversion for campaign ROI: lead_converted, lead_qualified, or custom
`campaign_utm_match_mode`	text	yes	’all’::text	How to match UTM parameters to campaigns: all, source_campaign, or campaign_only
`segment_recalc_frequency`	text	yes	’nightly’::text
`campaign_default_roi_days`	integer	yes	30
`campaign_conversion_value`	numeric	yes	0
`recaptcha_threshold`	numeric(3,2)	yes	0.5
`web_form_rate_limit_per_ip`	integer	yes	10
`web_form_rate_limit_window_minutes`	integer	yes	60
`web_form_default_lead_stage_id`	uuid	yes
`web_form_send_staff_notification`	boolean	yes	true
`web_form_send_visitor_confirmation`	boolean	yes	false
`click_to_call_enabled`	boolean	yes	true	CE-03-E1: Enable click-to-call feature for users. Default: true.
`screen_pop_enabled`	boolean	yes	true	CE-03-E1: Enable screen pop notifications on inbound calls. Default: true.
`screen_pop_duration_seconds`	integer	yes	30	CE-03-E1: Auto-dismiss timeout for screen pop (10-120 seconds). Default: 30.
`screen_pop_sound_enabled`	boolean	yes	true	CE-03-E1: Play sound notification on incoming call. Default: true.
`embeddable_enabled`	boolean	yes	false	CE-14: Enable RingCentral Embeddable widget for in-browser calling
`embeddable_widget_position`	text	yes	’bottom-right’::text	CE-14: Widget position (bottom-right, bottom-left, etc.)
`embeddable_custom_tabs_enabled`	boolean	yes	true	CE-14: Enable custom contact/partner tabs in widget
`embeddable_prefer_over_ringout`	boolean	yes	true	CE-14: Prefer Embeddable WebRTC over RingOut desk phone
`email_sender_address`	text	yes		Optional per-module email sender address (e.g. admissions@domain.com). Falls back to org default if null.
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates when true; otherwise use hardcoded wizards.
`calendar_default_meeting_duration_minutes`	integer	yes	30	Default duration in minutes for new calendar meetings
`calendar_sync_poll_interval_minutes`	integer	yes	15	Polling fallback interval in minutes when webhooks are unavailable
`calendar_pre_meeting_notification_minutes`	integer	yes	30	Minutes before a meeting to send CRM-context notification via PF-10
`business_hours_start`	time without time zone	yes	’09:00:00’::time without time zone	CE-13 default start of allowable sequence sends for business-hours-aware steps.
`business_hours_end`	time without time zone	yes	’18:00:00’::time without time zone	CE-13 default end of allowable sequence sends for business-hours-aware steps.
`sequence_batch_limit`	integer	yes	100	Maximum number of leads allowed in a manual/bulk sequence enrollment operation.
`dnc_sync_frequency_days`	integer	yes	30	CE-16: Days between National Do Not Call Registry syncs (0 = disabled)
`suppression_check_enabled`	boolean	yes	true	CE-16: Enable automatic suppression checks before SMS/email/phone sends
`consent_retention_years`	integer	yes	7	CE-16: Years to retain consent evidence records for compliance
`intake_business_hours_start`	time without time zone	yes		CE-28-EN-02: Daily business-hours start time (org timezone). NULL = 24/7 / not configured.
`intake_business_hours_end`	time without time zone	yes		CE-28-EN-02: Daily business-hours end time (org timezone). NULL = 24/7 / not configured.
`intake_business_hours_timezone`	text	yes		CE-28-EN-02: IANA timezone for business hours (e.g. ‘America/Phoenix’).
`intake_business_hours_days`	integer[]	yes	ARRAY[1, 2, 3, 4, 5]	CE-28-EN-02: Days of week considered business days (0=Sun..6=Sat). Default Mon-Fri.
`intake_after_hours_routing_id`	uuid	yes		CE-28-EN-02: Active on-call rotation used for after-hours routing.
`crisis_detection_enabled`	boolean	no	true	CE-28-EN-01 (CONTEXT §Feature Flags): Enable EN-01 keyword-based crisis detection. Default on; orgs may opt out.
`eligibility_check_mode`	text	no	’advisory’::text	CE-28-EN-04 (CONTEXT D-06): advisory (default) shows badge; gating blocks screening on non-eligible result.
`quality_review_sample_pct`	integer	no	5	CE-28-EN-06: Percent of screenings sampled for coordinator quality review (0-100, default 5).
`document_max_size_mb`	integer	no	25	CE-59: Per-org max document upload size (1-100 MB). Default 25.
`ai_extraction_enabled`	boolean	no	false	CE-59 Phase 2: Org-level AI extraction toggle. Default false.
`tile_field_config`	jsonb	no	jsonb_build_object(‘fields’, jsonb_build_array(‘stage’, ‘assignee’, ‘next_action’, ‘scheduled_appointment’))	CE-65: Org-level configuration of pipeline tile metadata rows. Shape: {“fields”: string[]}. Max 4 rows rendered per FR-1.4 with FR-1.5 priority ordering for overflow.
`history_default_range`	text	no	’all_time’::text	CE-60: Default date range filter for All History tab. One of last_7_days\|last_30_days\|last_90_days\|all_time.
`history_page_size`	integer	no	50	CE-60: Default page size for All History timeline cursor pagination (10-200, default 50).
`history_stage_fallback_enabled`	boolean	no	false	CE-60: When true, fall back to CE-04 activity-derived stage markers when CE-63 stage events are missing. Default false post CE-63 GA.
`uhc_optum_pa_enabled`	boolean	no	false	CE-69: Enable UHC/Optum AZ IOP prior-auth automation for this org (default off).
`uhc_optum_iop_default_sessions`	integer	no	30	CE-69: Default number of IOP sessions to pre-fill in the PA wizard.
`uhc_optum_iop_default_frequency`	text	no	’3x/week’::text	CE-69: Default IOP session frequency to pre-fill (e.g. 3x/week).

Foreign keys:

created_by → users(id)
default_lead_stage_id → ce_lead_stages(id)
intake_after_hours_routing_id → ce_intake_oncall_rotations(id)
organization_id → pf_organizations(id)
updated_by → users(id)
web_form_default_lead_stage_id → ce_lead_stages(id)

Policies:

ce_module_settings_delete (DELETE)
ce_module_settings_insert (INSERT)
ce_module_settings_select (SELECT)
ce_module_settings_update (UPDATE)

`ce_partner_contacts`

CE-02: Junction table linking contacts to partner organizations RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`partner_id`	uuid	no
`contact_id`	uuid	yes
`is_primary`	boolean	no	false	Whether this contact is the primary contact for the partner (enforced unique per partner)
`role_title`	text	yes		Contact role/title at the partner organization
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`name`	text	yes
`phone`	text	yes
`email`	text	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
partner_id → ce_partners(id)

Policies:

ce_partner_contacts_delete (DELETE)
ce_partner_contacts_insert (INSERT)
ce_partner_contacts_select (SELECT)
ce_partner_contacts_update (UPDATE)

`ce_partner_contracts`

CE-02: Lightweight partner contracts/agreements. For full governance (approvals, obligations, milestones), link to gr_contracts via gr_contract_id. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`partner_id`	uuid	no
`contract_type`	text	no		Type: service, referral, funding, mou, other
`contract_name`	text	yes
`document_id`	uuid	yes
`start_date`	date	yes
`end_date`	date	yes
`auto_renew`	boolean	yes	false
`renewal_notice_days`	integer	yes	30
`value`	numeric(12,2)	yes
`status`	text	no	’draft’::text
`gr_contract_id`	uuid	yes		Optional link to GR-10 gr_contracts for formal governance. Use when contract requires approval workflows, obligation tracking, or compliance oversight.
`terms_summary`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)
updated_by → pf_profiles(id)

Policies:

ce_partner_contracts_delete (DELETE)
ce_partner_contracts_insert (INSERT)
ce_partner_contracts_select (SELECT)
ce_partner_contracts_update (UPDATE)

`ce_partner_documents`

CE-24: Partner documents (contracts, certificates, protocols) categorized by type with version chain via supersedes_id. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`partner_id`	uuid	no
`contract_id`	uuid	yes
`document_type`	text	no	’other’::text
`document_id`	uuid	no
`title`	text	no
`description`	text	yes
`version`	integer	no	1
`supersedes_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

contract_id → ce_partner_contracts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)
supersedes_id → ce_partner_documents(id)
updated_by → pf_profiles(id)

Policies:

ce_partner_documents_insert (INSERT)
ce_partner_documents_select (SELECT)
ce_partner_documents_update (UPDATE)

`ce_partner_milestones`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`partner_type`	text	yes
`milestone_order`	integer	no	1
`target_days`	integer	no	30
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp. NULL = active, non-NULL = deleted

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_partner_milestones_delete (DELETE)
ce_partner_milestones_insert (INSERT)
ce_partner_milestones_select (SELECT)
ce_partner_milestones_update (UPDATE)

`ce_partner_progress`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`partner_id`	uuid	no
`progress_date`	date	no	CURRENT_DATE
`period_type`	text	no	’monthly’::text
`referrals_count`	integer	no	0
`successful_admissions`	integer	no	0
`average_referral_quality`	numeric(3,2)	yes
`response_time_hours`	numeric(6,2)	yes
`milestones_completed`	jsonb	yes	’[]‘::jsonb
`milestones_missed`	jsonb	yes	’[]‘::jsonb
`status`	text	no	’on_track’::text
`notes`	text	yes
`form_submission_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp. NULL = active, non-NULL = deleted

Foreign keys:

created_by → pf_profiles(id)
form_submission_id → fw_form_submissions(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)
updated_by → pf_profiles(id)

Policies:

ce_partner_progress_delete (DELETE)
ce_partner_progress_insert (INSERT)
ce_partner_progress_select (SELECT)
ce_partner_progress_update (UPDATE)

`ce_partners`

CE-02: Partner organizations (hospitals, treatment centers, payers, etc.) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes		Optional site scoping for multi-site organizations
`name`	text	no
`legal_name`	text	yes
`partner_type`	text	no		Type: hospital, treatment_center, court_probation, payer, insurance, community_org, employer, other
`relationship_status`	text	no	’prospective’::text	Status: prospective, active, inactive, on_hold
`relationship_start_date`	date	yes
`relationship_end_date`	date	yes
`primary_contact_id`	uuid	yes
`website`	text	yes
`address_line_1`	text	yes
`address_line_2`	text	yes
`city`	text	yes
`state`	text	yes
`zip_code`	text	yes
`total_referrals`	integer	no	0	Denormalized count - updated by trigger on ce_leads
`successful_admissions`	integer	no	0	Denormalized count - updated by trigger on resident admission
`last_referral_date`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., tier, contract_value, liaison_employee_id)
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp - null means active
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
primary_contact_id → ce_contacts(id)

Policies:

ce_partners_delete (DELETE)
ce_partners_insert (INSERT)
ce_partners_select (SELECT)
ce_partners_update (UPDATE)

`ce_pipeline_tracks`

CE-64: Service line pipeline tracks. Each track is an independent kanban row with its own stage list. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`color`	text	yes
`associated_services`	text[]	no	’{}‘::text[]	CE-58 requested_services values that auto-route a new lead to this track.
`sort_order`	integer	no	0
`is_active`	boolean	no	true
`is_default`	boolean	no	false	Exactly one default catch-all track per organization. Enforced by partial unique index.
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

ce_pipeline_tracks_delete (DELETE)
ce_pipeline_tracks_insert (INSERT)
ce_pipeline_tracks_select (SELECT)
ce_pipeline_tracks_update (UPDATE)

`ce_prior_auth_audit_log`

CE-69: Append-only audit trail for PA actions. PHI MUST NOT appear in details. Insert only via ce_log_prior_auth_event(). RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`prior_auth_id`	uuid	yes
`actor_id`	uuid	yes
`action`	text	no
`details`	jsonb	no	’{}‘::jsonb	CE-69: PHI-FREE context (status transition, error codes). No patient name/DOB/MRN.
`created_at`	timestamp with time zone	no	now()

Foreign keys:

actor_id → pf_profiles(id)
prior_auth_id → ce_prior_auth_submissions(id)

Policies:

ce_prior_auth_audit_log_insert_denied (INSERT)
ce_prior_auth_audit_log_select (SELECT)

`ce_prior_auth_submissions`

CE-69: UHC/Optum AZ IOP prior-authorization submissions, tenant-isolated. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`lead_id`	uuid	yes
`payer_type`	text	no	’uhc_optum_ahcccs’::text
`status`	text	no	’draft’::text
`billing_code`	text	yes
`review_type`	text	yes
`optum_confirmation_number`	text	yes
`authorized_start_date`	date	yes
`authorized_end_date`	date	yes
`authorized_sessions`	integer	yes
`submission_payload`	jsonb	no	’{}‘::jsonb	CE-69: PHI-REDACTED snapshot of submitted fields (member ID replaced with [REDACTED]). Never store raw PHI here.
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`last_status_update_at`	timestamp with time zone	yes
`last_status_update_by`	uuid	yes
`automation_error`	text	yes		CE-69: PHI-free structured error code if portal automation failed.
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific extension data.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

last_status_update_by → pf_profiles(id)
lead_id → ce_leads(id)
submitted_by → pf_profiles(id)

Policies:

ce_prior_auth_submissions_insert (INSERT)
ce_prior_auth_submissions_select (SELECT)
ce_prior_auth_submissions_update (UPDATE)

`ce_referral_sources`

CE-02: Referral source tracking for leads and contacts RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`name`	text	no
`source_type`	text	no		Type: partner, website, event, campaign, direct, other
`partner_id`	uuid	yes		Required when source_type = partner
`description`	text	yes
`is_active`	boolean	yes	true
`total_referrals`	integer	yes	0
`successful_conversions`	integer	yes	0
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

ce_referral_sources_delete (DELETE)
ce_referral_sources_insert (INSERT)
ce_referral_sources_select (SELECT)
ce_referral_sources_update (UPDATE)

`ce_ringcentral_extensions`

CE-03: Maps RingCentral extension numbers to pf_profiles. Used to identify user_id from webhook events. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`extension`	text	no
`user_id`	uuid	no
`is_active`	boolean	yes	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`callback_phone`	text	yes		CE-03-E1: Optional override phone number for RingOut calls. If set, this number is called instead of extension_phone.
`extension_phone`	text	yes		CE-03-E1: Primary phone number for this extension. Retrieved from RingCentral API or manually configured.
`extension_id`	text	yes		RingCentral numeric extension ID from API (e.g., 987654321)
`rc_name`	text	yes		RingCentral user name at time of mapping
`rc_email`	text	yes		RingCentral user email at time of mapping

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
user_id → pf_profiles(id)

Policies:

ce_rc_ext_delete (DELETE)
ce_rc_ext_insert (INSERT)
ce_rc_ext_select (SELECT)
ce_rc_ext_update (UPDATE)

`ce_ringcentral_subscriptions`

CE-03: Tracks RingCentral webhook subscriptions. Subscriptions expire after ~7 days and must be renewed. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`subscription_id`	text	no
`event_filters`	text[]	no
`delivery_mode`	text	no	’WebHook’::text
`webhook_url`	text	no
`status`	text	no	’active’::text
`expires_at`	timestamp with time zone	no
`renewed_at`	timestamp with time zone	yes
`verification_token`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)

Policies:

ce_rc_subs_select_admin (SELECT)
ce_rc_subs_select_member (SELECT)

`ce_ringout_sessions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ringout_id`	text	no
`status`	text	no	’initiated’::text
`from_number`	text	no
`to_number`	text	no
`user_id`	uuid	yes
`contact_id`	uuid	yes
`partner_id`	uuid	yes
`call_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

call_id → ce_calls(id)
contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)
updated_by → pf_profiles(id)
user_id → pf_profiles(id)

Policies:

ce_ringout_sessions_delete (DELETE)
ce_ringout_sessions_insert (INSERT)
ce_ringout_sessions_select (SELECT)
ce_ringout_sessions_update (UPDATE)

`ce_scheduled_messages`

CE-08-ENHANCEMENTS EN-02: queued outbound SMS executions with timezone-resolved schedule metadata. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	yes
`to_number`	text	yes		Resolved recipient phone number at schedule creation (E.164).
`message_content`	text	no
`template_id`	uuid	yes
`scheduled_at`	timestamp with time zone	no
`resolved_timezone`	text	no		Locked timezone resolved at schedule creation (contact -> site -> organization precedence).
`status`	text	no	’queued’::text
`executed_at`	timestamp with time zone	yes
`provider_response`	jsonb	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for scheduled SMS records.
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
template_id → ce_sms_templates(id)
updated_by → pf_profiles(id)

Policies:

ce_scheduled_messages_delete (DELETE)
ce_scheduled_messages_insert (INSERT)
ce_scheduled_messages_select (SELECT)
ce_scheduled_messages_update (UPDATE)

`ce_scheduled_messages_archive`

CE-08 EN-02: long-term archive of terminal-state scheduled SMS records (>=90 days old). RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`id`	uuid	no
`organization_id`	uuid	no
`contact_id`	uuid	yes
`to_number`	text	yes
`message_content`	text	no
`template_id`	uuid	yes
`scheduled_at`	timestamp with time zone	no
`resolved_timezone`	text	no
`status`	text	no
`executed_at`	timestamp with time zone	yes
`provider_response`	jsonb	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no
`updated_by`	uuid	yes
`archived_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)

Policies:

ce_scheduled_messages_archive_select (SELECT)

`ce_screening_attempts`

CE-28: Tracks each intake screening attempt for a lead RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`lead_id`	uuid	no
`screener_id`	uuid	no
`screening_date`	timestamp with time zone	no
`attempted_at`	timestamp with time zone	no	now()
`disposition`	text	no
`reason_code`	text	yes
`notes`	text	yes
`needs_clinical_escalation`	boolean	no	false
`escalation_notes`	text	yes
`consent_obtained`	boolean	no	false
`consent_method`	text	yes
`consent_recorded_at`	timestamp with time zone	yes
`sla_minutes`	integer	no	0
`sla_status`	text	no	’on_time’::text
`screening_outcome_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`crisis_alert_id`	uuid	yes		CE-28-EN-01: FK to crisis alert that initiated or is associated with this screening, if any.
`routed_after_hours`	boolean	no	false	CE-28-EN-02: True when the screening attempt was initiated outside configured business hours.
`after_hours_sla_minutes`	integer	yes		CE-28-EN-02: Separate SLA clock for after-hours response (D-04: business-hours SLA pauses; this tracks after-hours).

Foreign keys:

created_by → pf_profiles(id)
crisis_alert_id → ce_crisis_alerts(id)
lead_id → ce_leads(id)
organization_id → pf_organizations(id)
screener_id → pf_profiles(id)
screening_outcome_id → ce_screening_results(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

ce_screening_attempts_insert (INSERT)
ce_screening_attempts_select (SELECT)
ce_screening_attempts_update (UPDATE)

`ce_screening_questionnaires`

CE-28: Questionnaire templates per program type for intake screening RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_type`	text	no
`name`	text	no
`version`	integer	no	1
`questions`	jsonb	no	’[]‘::jsonb
`asam_scoring_rules`	jsonb	yes
`triage_rules`	jsonb	no	’{}‘::jsonb
`active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_screening_questionnaires_insert (INSERT)
ce_screening_questionnaires_select (SELECT)
ce_screening_questionnaires_update (UPDATE)

`ce_screening_results`

CE-28: Detailed screening questionnaire responses and calculated scores RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_type`	text	no
`questionnaire_responses`	jsonb	no	’{}‘::jsonb
`asam_score`	integer	yes
`clinical_flags`	jsonb	yes	’[]‘::jsonb
`recommended_level_of_care`	text	yes
`calculated_triage_category`	text	no	’standard’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_screening_results_insert (INSERT)
ce_screening_results_select (SELECT)
ce_screening_results_update (UPDATE)

`ce_screening_sla_config`

CE-28: SLA thresholds per program/site for intake screening RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`program_type`	text	no
`response_minutes`	integer	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

ce_screening_sla_config_insert (INSERT)
ce_screening_sla_config_select (SELECT)
ce_screening_sla_config_update (UPDATE)

`ce_sequence_enrollments`

CE-13: Tracks leads enrolled in follow-up sequences, their progress, and exit reasons. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`sequence_id`	uuid	no
`lead_id`	uuid	no
`contact_id`	uuid	no
`status`	text	no	’active’::text
`current_step_id`	uuid	yes
`next_step_at`	timestamp with time zone	yes
`completed_steps`	integer	no	0
`enrolled_by`	uuid	yes
`enrolled_at`	timestamp with time zone	no	now()
`enrollment_source`	text	yes
`exited_at`	timestamp with time zone	yes
`exit_reason`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

contact_id → ce_contacts(id)
current_step_id → ce_sequence_steps(id)
enrolled_by → pf_profiles(id)
lead_id → ce_leads(id)
organization_id → pf_organizations(id)
sequence_id → ce_sequences(id)
updated_by → pf_profiles(id)

Policies:

ce_sequence_enrollments_delete (DELETE)
ce_sequence_enrollments_insert (INSERT)
ce_sequence_enrollments_select (SELECT)
ce_sequence_enrollments_update (UPDATE)

`ce_sequence_execution_log`

CE-13: Append-only log of sequence step executions. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`enrollment_id`	uuid	no
`step_id`	uuid	no
`executed_at`	timestamp with time zone	no	now()
`status`	text	no
`result_data`	jsonb	no	’{}‘::jsonb
`error_message`	text	yes
`retry_count`	integer	no	0
`engagement_detected_at`	timestamp with time zone	yes
`engagement_type`	text	yes
`skip_reason`	text	yes		Reason a step was skipped (suppression, missing channel data, business-hours rule, etc.).

Foreign keys:

enrollment_id → ce_sequence_enrollments(id)
organization_id → pf_organizations(id)
step_id → ce_sequence_steps(id)

Policies:

ce_sequence_execution_log_insert (INSERT)
ce_sequence_execution_log_select (SELECT)

`ce_sequence_outbound_executions`

CE-13: Internal idempotency ledger for sequence step dispatch. Intentionally omits custom_fields because it stores only system-managed execution state. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`enrollment_id`	uuid	no
`step_id`	uuid	no
`action_type`	text	no
`idempotency_key`	text	no
`status`	text	no	’claimed’::text
`provider_message_id`	text	yes
`error_message`	text	yes
`claimed_at`	timestamp with time zone	no	now()
`sent_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
enrollment_id → ce_sequence_enrollments(id)
organization_id → pf_organizations(id)
step_id → ce_sequence_steps(id)
updated_by → pf_profiles(id)

Policies:

ce_sequence_outbound_executions_delete (DELETE)
ce_sequence_outbound_executions_insert (INSERT)
ce_sequence_outbound_executions_select (SELECT)
ce_sequence_outbound_executions_update (UPDATE)

`ce_sequence_steps`

CE-13: Individual steps in a follow-up sequence (email, SMS, or task). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`sequence_id`	uuid	no
`step_order`	integer	no
`step_type`	text	no
`name`	text	no
`delay_days`	integer	no	0
`delay_hours`	integer	no	0
`delay_minutes`	integer	no	0
`email_template_id`	uuid	yes
`email_subject`	text	yes
`email_body`	text	yes
`sms_template_id`	uuid	yes
`sms_body`	text	yes
`task_title`	text	yes
`task_description`	text	yes
`task_due_days`	integer	yes	1
`skip_conditions`	jsonb	no	’[]‘::jsonb
`execution_count`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
email_template_id → ce_email_templates(id)
organization_id → pf_organizations(id)
sequence_id → ce_sequences(id)
sms_template_id → ce_sms_templates(id)
updated_by → pf_profiles(id)

Policies:

ce_sequence_steps_delete (DELETE)
ce_sequence_steps_insert (INSERT)
ce_sequence_steps_select (SELECT)
ce_sequence_steps_update (UPDATE)

`ce_sequences`

CE-13: Sequence templates for automated multi-step lead follow-up (email, SMS, task). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`is_active`	boolean	no	true
`business_hours_only`	boolean	no	true
`auto_enroll`	boolean	no	false
`enrollment_criteria`	jsonb	no	’{}‘::jsonb	JSON criteria for auto-enrollment triggers (e.g., lead stage, source type).
`exit_on_reply`	boolean	no	true
`exit_on_call`	boolean	no	true
`exit_on_conversion`	boolean	no	true
`total_enrolled`	integer	no	0
`total_completed`	integer	no	0
`total_converted`	integer	no	0
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.
`notify_on_exit`	boolean	no	true	When true, PF-10 notifications are sent to enrolled_by when a sequence exits or completes.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_sequences_delete (DELETE)
ce_sequences_insert (INSERT)
ce_sequences_select (SELECT)
ce_sequences_update (UPDATE)

`ce_sms_consent`

TCPA compliance tracking for SMS consent per phone number RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	yes
`phone_number`	text	no
`consent_status`	text	no	’pending’::text	pending, opted_in, opted_out, revoked
`consent_method`	text	yes		How consent was obtained: web_form, verbal, written, etc.
`consented_at`	timestamp with time zone	yes
`opted_out_at`	timestamp with time zone	yes
`opt_out_method`	text	yes		How opt-out occurred: reply_stop, manual, api, etc.
`last_message_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_sms_consent_delete (DELETE)
ce_sms_consent_insert (INSERT)
ce_sms_consent_select (SELECT)
ce_sms_consent_update (UPDATE)

`ce_sms_messages`

SMS message log for inbound and outbound messages RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	yes
`partner_id`	uuid	yes
`lead_id`	uuid	yes
`activity_id`	uuid	yes
`direction`	text	no		inbound (received) or outbound (sent)
`status`	text	no	’queued’::text	queued, sending, sent, delivered, failed, received
`from_number`	text	no
`to_number`	text	no
`body`	text	no
`provider`	text	no
`provider_message_id`	text	yes
`sent_at`	timestamp with time zone	yes
`delivered_at`	timestamp with time zone	yes
`received_at`	timestamp with time zone	yes
`failed_at`	timestamp with time zone	yes
`failure_reason`	text	yes
`segments_count`	integer	yes	1	Number of SMS segments (160 chars per segment)
`media_urls`	jsonb	yes
`template_id`	uuid	yes
`merge_data`	jsonb	yes
`phi_detected`	boolean	yes	false	Whether PHI was detected in message body
`phi_detection_result`	jsonb	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

activity_id → ce_activities(id)
contact_id → ce_contacts(id)
created_by → pf_profiles(id)
lead_id → ce_leads(id)
organization_id → pf_organizations(id)
partner_id → ce_partners(id)
template_id → ce_sms_templates(id)
updated_by → pf_profiles(id)

Policies:

ce_sms_messages_delete (DELETE)
ce_sms_messages_insert (INSERT)
ce_sms_messages_select (SELECT)
ce_sms_messages_update (UPDATE)

`ce_sms_templates`

Reusable SMS message templates with merge field support RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`body_template`	text	no
`category`	text	no	’general’::text
`merge_fields_used`	jsonb	yes	’[]‘::jsonb	Array of merge field names used in template
`is_active`	boolean	no	true
`is_default`	boolean	yes	false
`character_count`	integer	yes	length(body_template)	Auto-calculated character count
`segments_estimate`	integer	yes	ceil(((length(body_template))::numeric / (160)::numeric))	Estimated SMS segments based on length
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_sms_templates_delete (DELETE)
ce_sms_templates_insert (INSERT)
ce_sms_templates_select (SELECT)
ce_sms_templates_update (UPDATE)

`ce_suppression_audit_log`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`suppression_id`	uuid	yes
`contact_id`	uuid	yes
`action`	text	no
`changes`	jsonb	no
`change_reason`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
suppression_id → ce_suppressions(id)

Policies:

ce_suppression_audit_log_insert (INSERT)
ce_suppression_audit_log_select (SELECT)

`ce_suppressions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contact_id`	uuid	no
`suppress_sms`	boolean	no	false
`suppress_email`	boolean	no	false
`suppress_phone`	boolean	no	false
`suppress_all`	boolean	no	false
`suppression_reason`	text	no
`suppression_source`	text	no
`suppression_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

contact_id → ce_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_suppressions_delete (DELETE)
ce_suppressions_insert (INSERT)
ce_suppressions_select (SELECT)
ce_suppressions_update (UPDATE)

`ce_unmatched_calls`

Temporary storage for RingCentral webhook events that could not be matched to an organization. Records are moved to ce_calls once organization is resolved. Service-role only access. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`telephony_session_id`	text	no
`call_data`	jsonb	no
`resolved_at`	timestamp with time zone	yes
`resolved_by`	uuid	yes
`resolved_organization_id`	uuid	yes
`resolved_call_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`organization_id`	uuid	yes		Organization ID if resolved after initial storage. Null until resolved.

Foreign keys:

organization_id → pf_organizations(id)
resolved_by → pf_profiles(id)
resolved_organization_id → pf_organizations(id)

Policies:

ce_unmatched_calls_service_role_delete (DELETE)
ce_unmatched_calls_service_role_insert (INSERT)
ce_unmatched_calls_service_role_select (SELECT)
ce_unmatched_calls_service_role_update (UPDATE)

`ce_web_form_submissions`

CE-10: Web form submission records with UTM tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`web_form_id`	uuid	no
`form_data`	jsonb	no	’{}‘::jsonb
`utm_source`	text	yes
`utm_medium`	text	yes
`utm_campaign`	text	yes
`utm_term`	text	yes
`utm_content`	text	yes
`ip_address`	inet	yes
`user_agent`	text	yes
`referrer_url`	text	yes
`landing_page_url`	text	yes
`status`	text	no	’pending’::text
`captcha_score`	numeric(3,2)	yes
`contact_id`	uuid	yes
`lead_id`	uuid	yes
`assigned_to`	uuid	yes
`error_message`	text	yes
`processing_attempts`	integer	no	0
`last_processing_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

assigned_to → pf_profiles(id)
contact_id → ce_contacts(id)
created_by → pf_profiles(id)
lead_id → ce_leads(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
web_form_id → ce_web_forms(id)

Policies:

ce_web_form_submissions_delete (DELETE)
ce_web_form_submissions_insert (INSERT)
ce_web_form_submissions_select (SELECT)
ce_web_form_submissions_update (UPDATE)

`ce_web_forms`

CE-10: Web form configurations for lead capture RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`slug`	text	no
`fields`	jsonb	no	’[]‘::jsonb
`styling`	jsonb	no	’{}‘::jsonb
`is_active`	boolean	no	true
`require_captcha`	boolean	no	true
`confirmation_message`	text	yes	’Thank you for your submission. We will be in touch soon.’::text
`redirect_url`	text	yes
`allowed_domains`	text[]	yes	ARRAY[]::text[]
`assignment_type`	text	no	’round_robin’::text
`default_assignee_id`	uuid	yes
`assignment_rules`	jsonb	yes	’{}‘::jsonb
`submission_count`	integer	no	0
`last_submission_at`	timestamp with time zone	yes
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
default_assignee_id → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

ce_web_forms_delete (DELETE)
ce_web_forms_insert (INSERT)
ce_web_forms_select (SELECT)
ce_web_forms_update (UPDATE)

​Entity relationships

​Tables

​ce_activities

​ce_calendar_connections

​ce_calendar_events

​ce_calls

​ce_campaigns

​ce_consent_evidence

​ce_contact_audit_log

​ce_contact_document_extraction_audit

​ce_contact_documents

​ce_contact_relationships

​ce_contact_segments

​ce_contact_tag_assignments

​ce_contact_tags

​ce_contacts

​ce_contract_renewal_log

​ce_crisis_alerts

​ce_email_accounts

​ce_email_messages

​ce_email_templates

​ce_email_tracking_events

​ce_event_attendees

​ce_events

​ce_insurance_access_audit

​ce_intake_oncall_rotations

​ce_lead_assignment_queue

​ce_lead_closures

​ce_lead_conversions

​ce_lead_field_audit

​ce_lead_stages

​ce_leads

​ce_message_routing_rules

​ce_module_settings

​ce_partner_contacts

​ce_partner_contracts

​ce_partner_documents

​ce_partner_milestones

​ce_partner_progress

​ce_partners

​ce_pipeline_tracks

​ce_prior_auth_audit_log

​ce_prior_auth_submissions

​ce_referral_sources

​ce_ringcentral_extensions

​ce_ringcentral_subscriptions

​ce_ringout_sessions

​ce_scheduled_messages

​ce_scheduled_messages_archive

​ce_screening_attempts

​ce_screening_questionnaires

​ce_screening_results

​ce_screening_sla_config

​ce_sequence_enrollments

​ce_sequence_execution_log

​ce_sequence_outbound_executions

​ce_sequence_steps

​ce_sequences

​ce_sms_consent

​ce_sms_messages

​ce_sms_templates

​ce_suppression_audit_log

​ce_suppressions

​ce_unmatched_calls

​ce_web_form_submissions

​ce_web_forms

Entity relationships

Tables

`ce_activities`

`ce_calendar_connections`

`ce_calendar_events`

`ce_calls`

`ce_campaigns`

`ce_consent_evidence`

`ce_contact_audit_log`

`ce_contact_document_extraction_audit`

`ce_contact_documents`

`ce_contact_relationships`

`ce_contact_segments`

`ce_contact_tag_assignments`

`ce_contact_tags`

`ce_contacts`

`ce_contract_renewal_log`

`ce_crisis_alerts`

`ce_email_accounts`

`ce_email_messages`

`ce_email_templates`

`ce_email_tracking_events`

`ce_event_attendees`

`ce_events`

`ce_insurance_access_audit`

`ce_intake_oncall_rotations`

`ce_lead_assignment_queue`

`ce_lead_closures`

`ce_lead_conversions`

`ce_lead_field_audit`

`ce_lead_stages`

`ce_leads`

`ce_message_routing_rules`

`ce_module_settings`

`ce_partner_contacts`

`ce_partner_contracts`

`ce_partner_documents`

`ce_partner_milestones`

`ce_partner_progress`

`ce_partners`

`ce_pipeline_tracks`

`ce_prior_auth_audit_log`

`ce_prior_auth_submissions`

`ce_referral_sources`

`ce_ringcentral_extensions`

`ce_ringcentral_subscriptions`

`ce_ringout_sessions`

`ce_scheduled_messages`

`ce_scheduled_messages_archive`

`ce_screening_attempts`

`ce_screening_questionnaires`

`ce_screening_results`

`ce_screening_sla_config`

`ce_sequence_enrollments`

`ce_sequence_execution_log`

`ce_sequence_outbound_executions`

`ce_sequence_steps`

`ce_sequences`

`ce_sms_consent`

`ce_sms_messages`

`ce_sms_templates`

`ce_suppression_audit_log`

`ce_suppressions`

`ce_unmatched_calls`

`ce_web_form_submissions`

`ce_web_forms`