> ## Documentation Index > Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt > Use this file to discover all available pages before exploring further. # Community Engagement Schema Reference > Generated schema reference for the ce core (64 tables). > **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 ```mermaid theme={null} erDiagram ce_calls ||--o{ ce_activities : "call_id" ce_activities ||--o{ ce_calendar_events : "activity_id" ce_calendar_connections ||--o{ ce_calendar_events : "calendar_connection_id" ce_contacts ||--o{ ce_calls : "contact_id" ce_partners ||--o{ ce_calls : "partner_id" ce_contacts ||--o{ ce_consent_evidence : "contact_id" ce_contacts ||--o{ ce_contact_audit_log : "contact_id" ce_contacts ||--o{ ce_contact_document_extraction_audit : "contact_id" ce_contact_documents ||--o{ ce_contact_document_extraction_audit : "document_id" ce_contacts ||--o{ ce_contact_documents : "contact_id" ce_contacts ||--o{ ce_contact_relationships : "contact_id" ce_contacts ||--o{ ce_contact_relationships : "related_contact_id" ce_contacts ||--o{ ce_contact_tag_assignments : "contact_id" ce_contact_tags ||--o{ ce_contact_tag_assignments : "tag_id" ce_partners ||--o{ ce_contacts : "partner_id" ce_referral_sources ||--o{ ce_contacts : "referral_source_id" ce_partner_contracts ||--o{ ce_contract_renewal_log : "contract_id" ce_contacts ||--o{ ce_crisis_alerts : "contact_id" ce_screening_attempts ||--o{ ce_crisis_alerts : "screening_attempt_id" ce_contacts ||--o{ ce_email_messages : "contact_id" ce_email_accounts ||--o{ ce_email_messages : "email_account_id" ce_leads ||--o{ ce_email_messages : "lead_id" ce_partners ||--o{ ce_email_messages : "partner_id" ce_email_messages ||--o{ ce_email_tracking_events : "email_message_id" ce_contacts ||--o{ ce_event_attendees : "contact_id" ce_events ||--o{ ce_event_attendees : "event_id" ce_campaigns ||--o{ ce_events : "campaign_id" ce_contacts ||--o{ ce_insurance_access_audit : "contact_id" ce_web_forms ||--o{ ce_lead_assignment_queue : "web_form_id" ce_leads ||--o{ ce_lead_closures : "lead_id" ce_leads ||--o{ ce_lead_conversions : "lead_id" ce_leads ||--o{ ce_lead_field_audit : "lead_id" ce_pipeline_tracks ||--o{ ce_lead_stages : "track_id" ce_campaigns ||--o{ ce_leads : "campaign_id" ce_contacts ||--o{ ce_leads : "contact_id" ce_lead_stages ||--o{ ce_leads : "lead_stage_id" ce_pipeline_tracks ||--o{ ce_leads : "track_id" ce_partners ||--o{ ce_leads : "partner_id" ce_referral_sources ||--o{ ce_leads : "referral_source_id" ce_lead_stages ||--o{ ce_module_settings : "default_lead_stage_id" ce_intake_oncall_rotations ||--o{ ce_module_settings : "intake_after_hours_routing_id" ce_lead_stages ||--o{ ce_module_settings : "web_form_default_lead_stage_id" ce_contacts ||--o{ ce_partner_contacts : "contact_id" ce_partners ||--o{ ce_partner_contacts : "partner_id" ce_partners ||--o{ ce_partner_contracts : "partner_id" ce_partner_contracts ||--o{ ce_partner_documents : "contract_id" ce_partners ||--o{ ce_partner_documents : "partner_id" ce_partner_documents ||--o{ ce_partner_documents : "supersedes_id" ce_partners ||--o{ ce_partner_progress : "partner_id" ce_contacts ||--o{ ce_partners : "primary_contact_id" ce_prior_auth_submissions ||--o{ ce_prior_auth_audit_log : "prior_auth_id" ce_leads ||--o{ ce_prior_auth_submissions : "lead_id" ce_partners ||--o{ ce_referral_sources : "partner_id" ce_calls ||--o{ ce_ringout_sessions : "call_id" ce_contacts ||--o{ ce_ringout_sessions : "contact_id" ce_partners ||--o{ ce_ringout_sessions : "partner_id" ce_contacts ||--o{ ce_scheduled_messages : "contact_id" ce_sms_templates ||--o{ ce_scheduled_messages : "template_id" ce_crisis_alerts ||--o{ ce_screening_attempts : "crisis_alert_id" ce_leads ||--o{ ce_screening_attempts : "lead_id" ce_screening_results ||--o{ ce_screening_attempts : "screening_outcome_id" ce_contacts ||--o{ ce_sequence_enrollments : "contact_id" ce_sequence_steps ||--o{ ce_sequence_enrollments : "current_step_id" ce_leads ||--o{ ce_sequence_enrollments : "lead_id" ce_sequences ||--o{ ce_sequence_enrollments : "sequence_id" ce_sequence_enrollments ||--o{ ce_sequence_execution_log : "enrollment_id" ce_sequence_steps ||--o{ ce_sequence_execution_log : "step_id" ce_sequence_enrollments ||--o{ ce_sequence_outbound_executions : "enrollment_id" ce_sequence_steps ||--o{ ce_sequence_outbound_executions : "step_id" ce_email_templates ||--o{ ce_sequence_steps : "email_template_id" ce_sequences ||--o{ ce_sequence_steps : "sequence_id" ce_sms_templates ||--o{ ce_sequence_steps : "sms_template_id" ce_contacts ||--o{ ce_sms_consent : "contact_id" ce_activities ||--o{ ce_sms_messages : "activity_id" ce_contacts ||--o{ ce_sms_messages : "contact_id" ce_leads ||--o{ ce_sms_messages : "lead_id" ce_partners ||--o{ ce_sms_messages : "partner_id" ce_sms_templates ||--o{ ce_sms_messages : "template_id" ce_contacts ||--o{ ce_suppression_audit_log : "contact_id" ce_suppressions ||--o{ ce_suppression_audit_log : "suppression_id" ce_contacts ||--o{ ce_suppressions : "contact_id" ce_contacts ||--o{ ce_web_form_submissions : "contact_id" ce_leads ||--o{ ce_web_form_submissions : "lead_id" ce_web_forms ||--o{ ce_web_form_submissions : "web_form_id" ce_activities { uuid id PK uuid organization_id FK uuid call_id FK uuid follow_up_task_id FK uuid created_by FK uuid updated_by FK } ce_calendar_connections { uuid id PK uuid organization_id FK uuid user_id FK uuid created_by FK uuid updated_by FK uuid access_token_credential_id FK uuid refresh_token_credential_id FK uuid gws_connection_id FK } ce_calendar_events { uuid id PK uuid organization_id FK uuid calendar_connection_id FK uuid activity_id FK uuid created_by FK uuid updated_by FK } ce_calls { uuid id PK uuid organization_id FK uuid dispositioned_by FK uuid contact_id FK uuid partner_id FK uuid user_id FK uuid created_by FK } ce_campaigns { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_consent_evidence { uuid id PK uuid organization_id FK uuid contact_id FK uuid created_by FK } ce_contact_audit_log { uuid id PK uuid organization_id FK uuid contact_id FK uuid actor_id FK uuid created_by FK } ce_contact_document_extraction_audit { uuid id PK uuid organization_id FK uuid contact_id FK uuid document_id FK } ce_contact_documents { uuid id PK uuid organization_id FK uuid contact_id FK uuid created_by FK uuid updated_by FK } ce_contact_relationships { uuid id PK uuid organization_id FK uuid contact_id FK uuid related_contact_id FK uuid created_by FK uuid updated_by FK } ce_contact_segments { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_contact_tag_assignments { uuid id PK uuid organization_id FK uuid contact_id FK uuid tag_id FK uuid assigned_by FK } ce_contact_tags { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_contacts { uuid id PK uuid organization_id FK uuid referral_source_id FK uuid partner_id FK uuid created_by FK uuid updated_by FK } ce_contract_renewal_log { uuid id PK uuid organization_id FK uuid contract_id FK uuid created_by FK } ce_crisis_alerts { uuid id PK uuid organization_id FK uuid contact_id FK uuid screening_attempt_id FK uuid routed_to FK uuid reviewer_id FK uuid created_by FK uuid updated_by FK } ce_email_accounts { uuid id PK uuid organization_id FK uuid user_id FK uuid created_by FK uuid updated_by FK uuid access_token_vault_id FK uuid refresh_token_vault_id FK } ce_email_messages { uuid id PK uuid organization_id FK uuid email_account_id FK uuid contact_id FK uuid partner_id FK uuid lead_id FK uuid created_by FK } ce_email_templates { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_email_tracking_events { uuid id PK uuid organization_id FK uuid email_message_id FK } ce_event_attendees { uuid id PK uuid organization_id FK uuid event_id FK uuid contact_id FK uuid checked_in_by FK uuid created_by FK uuid updated_by FK } ce_events { uuid id PK uuid organization_id FK uuid campaign_id FK uuid created_by FK uuid updated_by FK } ce_insurance_access_audit { uuid id PK uuid organization_id FK uuid contact_id FK } ce_intake_oncall_rotations { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_lead_assignment_queue { uuid id PK uuid organization_id FK uuid user_id FK uuid web_form_id FK uuid created_by FK uuid updated_by FK } ce_lead_closures { uuid id PK uuid organization_id FK uuid lead_id FK uuid created_by FK uuid updated_by FK } ce_lead_conversions { uuid id PK uuid organization_id FK uuid lead_id FK uuid patient_id FK uuid converted_by FK uuid created_by FK } ce_lead_field_audit { uuid id PK uuid lead_id FK uuid organization_id FK } ce_lead_stages { uuid id PK uuid organization_id FK uuid track_id FK } ce_leads { uuid id PK uuid organization_id FK uuid contact_id FK uuid lead_stage_id FK uuid referral_source_id FK uuid partner_id FK uuid assigned_to FK uuid converted_by FK uuid created_by FK uuid updated_by FK uuid site_id FK uuid campaign_id FK uuid track_id FK } ce_message_routing_rules { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_module_settings { uuid id PK uuid organization_id FK uuid default_lead_stage_id FK uuid created_by FK uuid updated_by FK uuid web_form_default_lead_stage_id FK uuid intake_after_hours_routing_id FK } ce_partner_contacts { uuid id PK uuid partner_id FK uuid contact_id FK uuid created_by FK } ce_partner_contracts { uuid id PK uuid organization_id FK uuid partner_id FK uuid created_by FK uuid updated_by FK } ce_partner_documents { uuid id PK uuid organization_id FK uuid partner_id FK uuid contract_id FK uuid supersedes_id FK uuid created_by FK uuid updated_by FK } ce_partner_milestones { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_partner_progress { uuid id PK uuid organization_id FK uuid partner_id FK uuid form_submission_id FK uuid created_by FK uuid updated_by FK } ce_partners { uuid id PK uuid organization_id FK uuid site_id FK uuid primary_contact_id FK uuid created_by FK uuid updated_by FK } ce_pipeline_tracks { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_prior_auth_audit_log { uuid id PK uuid organization_id uuid prior_auth_id FK uuid actor_id FK } ce_prior_auth_submissions { uuid id PK uuid organization_id uuid lead_id FK uuid submitted_by FK uuid last_status_update_by FK } ce_referral_sources { uuid id PK uuid organization_id FK uuid site_id FK uuid partner_id FK uuid created_by FK uuid updated_by FK } ce_ringcentral_extensions { uuid id PK uuid organization_id FK uuid user_id FK uuid created_by FK uuid updated_by FK } ce_ringcentral_subscriptions { uuid id PK uuid organization_id FK } ce_ringout_sessions { uuid id PK uuid organization_id FK uuid user_id FK uuid contact_id FK uuid partner_id FK uuid call_id FK uuid created_by FK uuid updated_by FK } ce_scheduled_messages { uuid id PK uuid organization_id FK uuid contact_id FK uuid template_id FK uuid created_by FK uuid updated_by FK } ce_scheduled_messages_archive { uuid id PK uuid organization_id FK } ce_screening_attempts { uuid id PK uuid organization_id FK uuid site_id FK uuid lead_id FK uuid screener_id FK uuid screening_outcome_id FK uuid created_by FK uuid updated_by FK uuid crisis_alert_id FK } ce_screening_questionnaires { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_screening_results { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_screening_sla_config { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } ce_sequence_enrollments { uuid id PK uuid organization_id FK uuid sequence_id FK uuid lead_id FK uuid contact_id FK uuid current_step_id FK uuid enrolled_by FK uuid updated_by FK } ce_sequence_execution_log { uuid id PK uuid organization_id FK uuid enrollment_id FK uuid step_id FK } ce_sequence_outbound_executions { uuid id PK uuid organization_id FK uuid enrollment_id FK uuid step_id FK uuid created_by FK uuid updated_by FK } ce_sequence_steps { uuid id PK uuid organization_id FK uuid sequence_id FK uuid email_template_id FK uuid sms_template_id FK uuid created_by FK uuid updated_by FK } ce_sequences { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_sms_consent { uuid id PK uuid organization_id FK uuid contact_id FK uuid created_by FK uuid updated_by FK } ce_sms_messages { uuid id PK uuid organization_id FK uuid contact_id FK uuid partner_id FK uuid lead_id FK uuid activity_id FK uuid template_id FK uuid created_by FK uuid updated_by FK } ce_sms_templates { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ce_suppression_audit_log { uuid id PK uuid organization_id FK uuid suppression_id FK uuid contact_id FK uuid created_by FK } ce_suppressions { uuid id PK uuid organization_id FK uuid contact_id FK uuid created_by FK uuid updated_by FK } ce_unmatched_calls { uuid id PK uuid resolved_by FK uuid resolved_organization_id FK uuid organization_id FK } ce_web_form_submissions { uuid id PK uuid organization_id FK uuid web_form_id FK uuid contact_id FK uuid lead_id FK uuid assigned_to FK uuid created_by FK uuid updated_by FK } ce_web_forms { uuid id PK uuid organization_id FK uuid default_assignee_id FK uuid created_by FK uuid updated_by FK } ``` ## Tables ### `ce_activities` CE-04: Activity log for contacts and partners **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------ | ------------------------ | -------- | ------------------------- | ------- | | `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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `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](mailto: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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------- | ------------------------ | -------- | ------------ | ------- | | `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 | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `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 | Comment | | ---------------------- | ------------------------ | -------- | ---------------------------------------------------------------- | ------- | | `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)