> ## 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. # Clinical Schema Reference > Generated schema reference for the cl core (146 tables). > **Generated** by `npm run db:docs:generate` — DO NOT EDIT BY HAND. Change the schema via a migration, then regenerate. **Core:** `cl_` · **Tables:** 146 · **Source generated:** 2026-06-10T02:55:32.169Z ## Entity relationships ```mermaid theme={null} erDiagram cl_patient_charts ||--o{ cl_988_transfers : "chart_id" cl_safety_plans ||--o{ cl_988_transfers : "safety_plan_id" cl_risk_screenings ||--o{ cl_988_transfers : "screening_id" cl_patient_charts ||--o{ cl_aftercare_plans : "chart_id" cl_patient_charts ||--o{ cl_ai_documentation_sessions : "chart_id" cl_ai_documentation_sessions ||--o{ cl_ai_note_attributions : "ai_session_id" cl_patient_charts ||--o{ cl_allergies : "chart_id" cl_allergies ||--o{ cl_allergy_alert_overrides : "allergy_id" cl_patient_charts ||--o{ cl_allergy_alert_overrides : "chart_id" cl_ambient_sessions ||--o{ cl_ambient_session_events : "ambient_session_id" cl_patient_charts ||--o{ cl_ambient_sessions : "chart_id" cl_consents ||--o{ cl_ambient_sessions : "consent_record_id" cl_ambient_sessions ||--o{ cl_ambient_transcripts : "ambient_session_id" cl_assessments ||--o{ cl_assessment_sections : "assessment_id" cl_assessment_templates ||--o{ cl_assessment_template_sections : "template_id" cl_patient_charts ||--o{ cl_assessments : "chart_id" cl_cod_assessments ||--o{ cl_assessments : "cod_assessment_id" cl_assessments ||--o{ cl_assessments : "supersedes_id" cl_assessment_templates ||--o{ cl_assessments : "template_id" cl_patient_charts ||--o{ cl_between_session_checkins : "organization_id,chart_id" cl_patient_charts ||--o{ cl_biometrics : "chart_id" cl_patient_charts ||--o{ cl_care_gaps : "chart_id" cl_patient_charts ||--o{ cl_care_teams : "organization_id,chart_id" cl_patient_charts ||--o{ cl_cda_documents : "chart_id" cl_cda_transmission_endpoints ||--o{ cl_cda_documents : "recipient_endpoint_id" cl_cds_rules ||--o{ cl_cds_alerts : "rule_id" cl_patient_charts ||--o{ cl_cds_alerts : "organization_id,chart_id" cl_patient_charts ||--o{ cl_cod_assessments : "chart_id" cl_patient_charts ||--o{ cl_cod_treatment_plan_extension : "chart_id" cl_moud_enrollments ||--o{ cl_cod_treatment_plan_extension : "moud_episode_id" cl_treatment_plans ||--o{ cl_cod_treatment_plan_extension : "treatment_plan_id" cl_patient_charts ||--o{ cl_concurrent_reviews : "chart_id" cl_patient_charts ||--o{ cl_consent_requests : "chart_id" cl_consents ||--o{ cl_consent_requests : "consent_id" cl_patient_charts ||--o{ cl_consents : "organization_id,chart_id" cl_prescriptions ||--o{ cl_controlled_substance_inventory : "reference_prescription_id" cl_patient_charts ||--o{ cl_ddi_overrides : "chart_id" cl_consents ||--o{ cl_ddi_overrides : "consent_id" cl_patient_charts ||--o{ cl_designated_contacts : "chart_id" cl_patient_charts ||--o{ cl_diagnoses : "chart_id" cl_patient_charts ||--o{ cl_discharge_checklists : "organization_id,chart_id" cl_transitions ||--o{ cl_discharge_checklists : "organization_id,transition_id" cl_consents ||--o{ cl_disclosure_log : "consent_id" cl_patient_charts ||--o{ cl_disclosure_log : "organization_id,chart_id" cl_patient_charts ||--o{ cl_environmental_assessments : "chart_id" cl_patient_charts ||--o{ cl_family_notification_log : "chart_id" cl_designated_contacts ||--o{ cl_family_notification_log : "contact_id" cl_risk_screenings ||--o{ cl_family_notification_log : "screening_id" cl_aftercare_plans ||--o{ cl_follow_up_contacts : "aftercare_plan_id" cl_patient_charts ||--o{ cl_follow_up_contacts : "chart_id" cl_patient_charts ||--o{ cl_group_attendance : "chart_id" cl_group_sessions ||--o{ cl_group_attendance : "session_id" cl_group_sessions ||--o{ cl_group_encounter_generations : "group_session_id" cl_group_sessions ||--o{ cl_group_session_telehealth_metadata : "session_id" cl_inbasket_items ||--o{ cl_inbasket_item_activity : "item_id" cl_patient_charts ||--o{ cl_inbasket_items : "organization_id,chart_id" cl_patient_charts ||--o{ cl_incidents : "chart_id" cl_sdoh_screenings ||--o{ cl_intake_assessments : "sdoh_screening_id" cl_patient_charts ||--o{ cl_lethal_means_assessments : "chart_id" cl_risk_screenings ||--o{ cl_lethal_means_assessments : "screening_id" cl_patient_charts ||--o{ cl_loc_assessments : "chart_id" cl_loc_assessments ||--o{ cl_loc_assessments : "supersedes_id" cl_marketplace_bundles ||--o{ cl_marketplace_bundle_items : "bundle_id" cl_marketplace_bundles ||--o{ cl_marketplace_imports : "bundle_id" cl_patient_charts ||--o{ cl_medication_adherence : "chart_id" cl_medications ||--o{ cl_medication_adherence : "medication_id" cl_sdoh_screenings ||--o{ cl_medication_adherence : "sdoh_screening_id" cl_patient_charts ||--o{ cl_medication_education : "chart_id" cl_medications ||--o{ cl_medication_education : "medication_id" cl_patient_charts ||--o{ cl_medication_reconciliations : "chart_id" cl_patient_charts ||--o{ cl_medications : "chart_id" cl_patient_charts ||--o{ cl_metabolic_monitoring_events : "chart_id" cl_patient_charts ||--o{ cl_moud_enrollments : "chart_id" cl_consents ||--o{ cl_moud_enrollments : "consent_id" cl_patient_charts ||--o{ cl_moud_medication_events : "chart_id" cl_moud_enrollments ||--o{ cl_moud_medication_events : "enrollment_id" cl_patient_charts ||--o{ cl_moud_monitoring_events : "chart_id" cl_moud_enrollments ||--o{ cl_moud_monitoring_events : "enrollment_id" cl_patient_charts ||--o{ cl_multi_party_encounters : "primary_chart_id" cl_patient_charts ||--o{ cl_multi_party_participants : "chart_id" cl_multi_party_encounters ||--o{ cl_multi_party_participants : "encounter_id" cl_progress_notes ||--o{ cl_note_940_override_audit : "note_id" cl_progress_notes ||--o{ cl_note_treatment_goals : "note_id" cl_treatment_goals ||--o{ cl_note_treatment_goals : "treatment_goal_id" cl_notification_events ||--o{ cl_notification_delivery_attempts : "event_id" cl_notification_policies ||--o{ cl_notification_events : "policy_id" cl_patient_charts ||--o{ cl_order_results : "organization_id,chart_id" cl_orders ||--o{ cl_order_results : "organization_id,order_id" cl_patient_charts ||--o{ cl_order_set_activations : "chart_id" cl_order_set_templates ||--o{ cl_order_set_activations : "template_id" cl_order_set_templates ||--o{ cl_order_set_templates : "parent_version_id" cl_loinc_codes ||--o{ cl_orders : "loinc_code" cl_reference_labs ||--o{ cl_orders : "reference_lab_id" cl_patient_charts ||--o{ cl_orders : "organization_id,chart_id" cl_patient_charts ||--o{ cl_outcome_measures : "chart_id" cl_patient_pathways ||--o{ cl_pathway_milestones : "patient_pathway_id" cl_pathway_definitions ||--o{ cl_pathway_progress : "pathway_id" cl_pathway_templates ||--o{ cl_pathway_templates : "parent_template_id" cl_pathway_milestones ||--o{ cl_pathway_variances : "milestone_id" cl_patient_pathways ||--o{ cl_pathway_variances : "patient_pathway_id" cl_patient_cohorts ||--o{ cl_patient_cohort_definitions : "cohort_id" cl_patient_charts ||--o{ cl_patient_cohort_memberships : "chart_id" cl_patient_cohorts ||--o{ cl_patient_cohort_memberships : "cohort_id" cl_pathway_templates ||--o{ cl_patient_pathways : "template_id" cl_patient_charts ||--o{ cl_patient_submissions : "organization_id,chart_id" cl_patient_charts ||--o{ cl_pdmp_queries : "chart_id" cl_prescriptions ||--o{ cl_pdmp_queries : "prescription_id" cl_reference_labs ||--o{ cl_pending_lab_results : "lab_id" cl_patient_charts ||--o{ cl_pending_lab_results : "organization_id,linked_chart_id" cl_orders ||--o{ cl_pending_lab_results : "organization_id,linked_order_id" cl_patient_charts ||--o{ cl_prescriptions : "chart_id" cl_medications ||--o{ cl_prescriptions : "medication_id" cl_pharmacies ||--o{ cl_prescriptions : "pharmacy_id" cl_problems ||--o{ cl_problem_history : "problem_id" cl_problems ||--o{ cl_problem_status_history : "problem_id" cl_patient_charts ||--o{ cl_problems : "chart_id" cl_program_enrollments ||--o{ cl_program_attendance_summaries : "program_enrollment_id" cl_program_schedules ||--o{ cl_program_enrollments : "program_schedule_id" cl_patient_charts ||--o{ cl_program_enrollments : "organization_id,chart_id" cl_progress_notes ||--o{ cl_progress_notes : "amended_from" cl_patient_charts ||--o{ cl_progress_notes : "chart_id" cl_patient_charts ||--o{ cl_readmission_risk_scores : "chart_id" cl_electronic_consents ||--o{ cl_redisclosure_log : "consent_id" cl_report_definitions ||--o{ cl_report_runs : "report_definition_id" cl_concurrent_reviews ||--o{ cl_review_appeals : "review_id" cl_patient_charts ||--o{ cl_risk_screenings : "chart_id" cl_patient_charts ||--o{ cl_risk_stratifications : "chart_id" cl_safety_plans ||--o{ cl_safety_plan_shares : "safety_plan_id" cl_patient_charts ||--o{ cl_safety_plans : "chart_id" cl_safety_plans ||--o{ cl_safety_plans : "superseded_by" cl_patient_charts ||--o{ cl_sdoh_screenings : "chart_id" cl_patient_charts ||--o{ cl_social_referrals : "chart_id" cl_sdoh_screenings ||--o{ cl_social_referrals : "screening_id" cl_patient_charts ||--o{ cl_standing_order_executions : "chart_id" cl_clinical_standing_orders ||--o{ cl_standing_order_executions : "standing_order_id" cl_consents ||--o{ cl_tefca_exchange_log : "consent_id" cl_patient_charts ||--o{ cl_telehealth_consents : "chart_id" cl_patient_charts ||--o{ cl_telehealth_sessions : "chart_id" cl_progress_notes ||--o{ cl_telehealth_sessions : "progress_note_id" cl_patient_charts ||--o{ cl_transitions : "organization_id,chart_id" cl_medication_reconciliations ||--o{ cl_transitions : "organization_id,medication_reconciliation_id" cl_treatment_plan_templates ||--o{ cl_treatment_goal_bank : "template_id" cl_treatment_plans ||--o{ cl_treatment_goals : "plan_id" cl_problems ||--o{ cl_treatment_goals : "problem_id" cl_treatment_goals ||--o{ cl_treatment_interventions : "goal_id" cl_treatment_plans ||--o{ cl_treatment_plan_signatures : "treatment_plan_id" cl_patient_charts ||--o{ cl_treatment_plans : "chart_id" cl_treatment_plans ||--o{ cl_treatment_plans : "previous_plan_id" cl_patient_charts ||--o{ cl_uds_interpretation_notes : "chart_id" cl_uds_orders ||--o{ cl_uds_interpretation_notes : "uds_order_id" cl_patient_charts ||--o{ cl_uds_orders : "chart_id" cl_orders ||--o{ cl_uds_orders : "cl_order_id" cl_patient_charts ||--o{ cl_uds_results : "chart_id" cl_uds_orders ||--o{ cl_uds_results : "uds_order_id" cl_patient_charts ||--o{ cl_vital_signs : "chart_id" cl_patient_charts ||--o{ cl_warm_handoffs : "chart_id" cl_988_transfers { uuid id PK uuid organization_id FK uuid chart_id FK uuid screening_id FK uuid safety_plan_id FK uuid created_by FK uuid updated_by FK } cl_aftercare_plans { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_ai_documentation_sessions { uuid id PK uuid organization_id FK uuid user_id FK uuid chart_id FK uuid encounter_id FK uuid attestation_by FK uuid created_by FK uuid updated_by FK } cl_ai_note_attributions { uuid id PK uuid organization_id FK uuid ai_session_id FK uuid ai_attestation_by FK uuid created_by FK uuid updated_by FK } cl_allergies { uuid id PK uuid organization_id FK uuid chart_id FK uuid verified_by FK uuid created_by FK uuid updated_by FK uuid site_id FK uuid documented_by FK } cl_allergy_alert_overrides { uuid id PK uuid organization_id FK uuid allergy_id FK uuid chart_id FK uuid overridden_by FK } cl_ambient_session_events { uuid id PK uuid organization_id FK uuid ambient_session_id FK uuid created_by FK } cl_ambient_sessions { uuid id PK uuid organization_id FK uuid site_id FK uuid encounter_id FK uuid patient_id FK uuid provider_id FK uuid consent_record_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_ambient_transcripts { uuid id PK uuid organization_id FK uuid ambient_session_id FK uuid created_by FK } cl_assessment_instruments { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_assessment_sections { uuid id PK uuid organization_id FK uuid assessment_id FK uuid created_by FK uuid updated_by FK } cl_assessment_template_sections { uuid id PK uuid organization_id FK uuid template_id FK uuid created_by FK uuid updated_by FK } cl_assessment_templates { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_assessments { uuid id PK uuid organization_id FK uuid chart_id FK uuid site_id FK uuid clinician_id FK uuid signed_by FK uuid cosigned_by FK uuid supersedes_id FK uuid created_by FK uuid updated_by FK uuid template_id FK uuid cod_assessment_id FK } cl_audit_dashboard_break_glass_reviews { uuid id PK uuid organization_id FK uuid reviewed_by FK } cl_between_session_checkins { uuid id PK uuid organization_id FK uuid chart_id FK uuid created_by FK } cl_biometrics { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_care_gaps { uuid id PK uuid organization_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_care_teams { uuid id PK uuid organization_id FK uuid chart_id FK uuid site_id FK uuid provider_id FK uuid created_by FK uuid updated_by FK } cl_cda_documents { uuid id PK uuid organization_id FK uuid chart_id FK uuid recipient_endpoint_id FK } cl_cda_transmission_endpoints { uuid id PK uuid organization_id FK } cl_cds_alerts { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid rule_id FK uuid acknowledged_by FK uuid created_by FK uuid updated_by FK } cl_cds_rules { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } cl_clinical_standing_orders { uuid id PK uuid organization_id FK uuid approved_by FK uuid created_by FK uuid updated_by FK } cl_cod_assessments { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid assessor_id FK uuid created_by FK uuid updated_by FK } cl_cod_treatment_plan_extension { uuid id PK uuid organization_id FK uuid treatment_plan_id FK uuid chart_id FK uuid moud_episode_id FK uuid created_by FK uuid updated_by FK } cl_concurrent_reviews { uuid id PK uuid organization_id FK uuid chart_id FK } cl_consent_requests { uuid id PK uuid organization_id FK uuid chart_id FK uuid consent_id FK uuid reviewed_by FK uuid created_by FK uuid updated_by FK } cl_consents { uuid id PK uuid organization_id FK uuid chart_id FK uuid site_id FK uuid witnessed_by FK uuid created_by FK uuid updated_by FK } cl_controlled_substance_inventory { uuid id PK uuid organization_id FK uuid site_id FK uuid reference_prescription_id FK uuid performed_by FK uuid witness_id FK uuid created_by FK uuid updated_by FK } cl_cron_run_log { uuid id PK } cl_data_exchange_log { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK } cl_ddi_alert_suppressions { uuid id PK uuid organization_id FK uuid provider_id FK uuid created_by FK uuid updated_by FK } cl_ddi_overrides { uuid id PK uuid organization_id FK uuid chart_id FK uuid acknowledged_by FK uuid cosigner_id FK uuid consent_id FK uuid created_by FK uuid updated_by FK } cl_designated_contacts { uuid id PK uuid organization_id FK uuid chart_id FK uuid consent_verified_by FK uuid created_by FK uuid updated_by FK } cl_diagnoses { uuid id PK uuid organization_id FK uuid patient_id FK uuid chart_id FK uuid entered_by FK uuid created_by FK uuid updated_by FK } cl_discharge_checklists { uuid id PK uuid organization_id FK uuid chart_id FK uuid transition_id FK uuid waived_by FK uuid created_by FK uuid updated_by FK } cl_disclosure_log { uuid id PK uuid organization_id FK uuid chart_id FK uuid consent_id FK uuid disclosed_by FK uuid created_by FK } cl_dsi_disclosures { uuid id PK uuid organization_id FK uuid published_by FK uuid created_by FK uuid updated_by FK uuid retired_by FK } cl_electronic_consents { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_environmental_assessments { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid assessed_by FK uuid created_by FK uuid updated_by FK } cl_family_notification_log { uuid id PK uuid organization_id FK uuid chart_id FK uuid screening_id FK uuid contact_id FK uuid created_by FK uuid updated_by FK } cl_fhir_resources { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } cl_follow_up_contacts { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid aftercare_plan_id FK uuid created_by FK uuid updated_by FK } cl_group_attendance { uuid id PK uuid organization_id FK uuid session_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_group_curricula { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_group_encounter_generations { uuid id PK uuid organization_id FK uuid group_session_id FK uuid created_by FK uuid updated_by FK uuid reviewed_by FK } cl_group_session_telehealth_metadata { uuid id PK uuid organization_id FK uuid session_id FK uuid in_person_backup_site_id FK uuid created_by FK uuid updated_by FK } cl_group_sessions { uuid id PK uuid organization_id FK uuid facilitator_id FK uuid co_facilitator_id FK uuid created_by FK uuid updated_by FK } cl_inbasket_item_activity { uuid id PK uuid organization_id FK uuid item_id FK uuid actor_id FK } cl_inbasket_items { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid assigned_to FK uuid created_by FK uuid updated_by FK } cl_incidents { uuid id PK uuid organization_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_intake_assessments { uuid id PK uuid organization_id FK uuid patient_id FK uuid appointment_id FK uuid sdoh_screening_id FK uuid created_by FK uuid updated_by FK } cl_lethal_means_assessments { uuid id PK uuid organization_id FK uuid chart_id FK uuid screening_id FK uuid encounter_id FK uuid assessed_by FK uuid created_by FK uuid updated_by FK } cl_loc_assessments { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid supersedes_id FK uuid assessed_by FK uuid signed_by FK uuid created_by FK uuid updated_by FK } cl_marketplace_bundle_items { uuid id PK uuid bundle_id FK } cl_marketplace_bundles { uuid id PK uuid published_by FK } cl_marketplace_imports { uuid id PK uuid organization_id FK uuid bundle_id FK uuid imported_by FK } cl_medication_adherence { uuid id PK uuid organization_id FK uuid chart_id FK uuid medication_id FK uuid sdoh_screening_id FK uuid created_by FK uuid updated_by FK } cl_medication_education { uuid id PK uuid organization_id FK uuid chart_id FK uuid medication_id FK uuid educated_by FK uuid created_by FK uuid updated_by FK } cl_medication_reconciliations { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid reconciled_by FK uuid created_by FK uuid updated_by FK } cl_medications { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid discontinued_by FK uuid prescribed_by FK uuid created_by FK uuid updated_by FK } cl_metabolic_monitoring_events { uuid id PK uuid organization_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_module_settings { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_moud_enrollments { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid consent_id FK uuid created_by FK uuid updated_by FK } cl_moud_medication_events { uuid id PK uuid organization_id FK uuid enrollment_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_moud_monitoring_events { uuid id PK uuid organization_id FK uuid enrollment_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_multi_party_encounters { uuid id PK uuid organization_id FK uuid site_id FK uuid primary_chart_id FK uuid created_by FK uuid updated_by FK } cl_multi_party_participants { uuid id PK uuid organization_id FK uuid encounter_id FK uuid chart_id FK uuid staff_user_id FK uuid created_by FK uuid updated_by FK } cl_note_940_override_audit { uuid id PK uuid organization_id FK uuid note_id FK uuid override_by FK uuid created_by FK } cl_note_templates { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_note_treatment_goals { uuid id PK uuid note_id FK uuid treatment_goal_id FK uuid organization_id FK uuid created_by FK } cl_notification_delivery_attempts { uuid id PK uuid organization_id FK uuid event_id FK uuid recipient_user_id FK } cl_notification_escalation_chains { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_notification_events { uuid id PK uuid organization_id FK uuid policy_id FK uuid recipient_user_id FK uuid acknowledged_by FK } cl_notification_policies { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_order_results { uuid id PK uuid organization_id FK uuid order_id FK uuid chart_id FK uuid reviewed_by FK uuid created_by FK uuid updated_by FK } cl_order_set_activations { uuid id PK uuid organization_id FK uuid template_id FK uuid chart_id FK uuid activated_by FK uuid created_by FK uuid updated_by FK } cl_order_set_templates { uuid id PK uuid organization_id FK uuid parent_version_id FK uuid approved_by FK uuid created_by FK uuid updated_by FK } cl_order_templates { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_orders { uuid id PK uuid organization_id FK uuid chart_id FK uuid ordering_provider_id FK uuid reference_lab_id FK uuid site_id FK text loinc_code FK uuid created_by FK uuid updated_by FK } cl_outcome_measures { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid administered_by FK uuid created_by FK uuid updated_by FK } cl_pathway_definitions { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } cl_pathway_milestones { uuid id PK uuid organization_id FK uuid patient_pathway_id FK uuid completed_by FK uuid created_by FK uuid updated_by FK } cl_pathway_progress { uuid id PK uuid organization_id FK uuid site_id FK uuid pathway_id FK uuid created_by FK uuid updated_by FK } cl_pathway_templates { uuid id PK uuid organization_id FK uuid parent_template_id FK uuid created_by FK uuid updated_by FK } cl_pathway_variances { uuid id PK uuid organization_id FK uuid patient_pathway_id FK uuid milestone_id FK uuid documented_by FK uuid updated_by FK } cl_patient_charts { uuid id PK uuid organization_id FK uuid patient_id FK uuid primary_site_id FK uuid created_by FK uuid updated_by FK uuid assigned_clinician_id FK } cl_patient_cohort_definitions { uuid id PK uuid organization_id FK uuid cohort_id FK uuid created_by FK } cl_patient_cohort_memberships { uuid id PK uuid organization_id FK uuid cohort_id FK uuid chart_id FK } cl_patient_cohorts { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } cl_patient_pathways { uuid id PK uuid organization_id FK uuid template_id FK uuid enrolled_by FK uuid created_by FK uuid updated_by FK } cl_patient_submissions { uuid id PK uuid organization_id FK uuid chart_id FK uuid reviewed_by FK uuid created_by FK uuid updated_by FK } cl_pdmp_configuration { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_pdmp_queries { uuid id PK uuid organization_id FK uuid chart_id FK uuid queried_by FK uuid queried_on_behalf_of FK uuid results_reviewed_by FK uuid prescription_id FK uuid created_by FK uuid updated_by FK } cl_peer_encounters { uuid id PK uuid organization_id FK uuid patient_id FK uuid encounter_id FK uuid created_by FK uuid updated_by FK } cl_pending_lab_results { uuid id PK uuid organization_id FK uuid lab_id FK uuid linked_chart_id FK uuid linked_order_id FK uuid linked_by FK uuid created_by FK uuid updated_by FK } cl_pharmacies { uuid id PK uuid organization_id } cl_prescriptions { uuid id PK uuid organization_id uuid chart_id FK uuid pharmacy_id FK uuid site_id FK uuid medication_id FK } cl_problem_history { uuid id PK uuid organization_id FK uuid problem_id FK uuid changed_by FK } cl_problem_status_history { uuid id PK uuid organization_id FK uuid problem_id FK } cl_problems { uuid id PK uuid organization_id FK uuid chart_id FK uuid managing_provider_id FK uuid created_by FK uuid updated_by FK uuid patient_id FK uuid recorded_by FK } cl_program_attendance_summaries { uuid id PK uuid organization_id FK uuid program_enrollment_id FK uuid created_by FK uuid updated_by FK } cl_program_enrollments { uuid id PK uuid organization_id FK uuid chart_id FK uuid program_schedule_id FK uuid created_by FK uuid updated_by FK } cl_program_outcomes { uuid id PK uuid organization_id FK uuid program_id FK uuid created_by FK uuid updated_by FK } cl_program_schedules { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } cl_progress_notes { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid signed_by FK uuid cosigned_by FK uuid amended_from FK uuid created_by FK uuid updated_by FK uuid note_940_override_by FK } cl_quality_measure_periods { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_readmission_risk_scores { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_redisclosure_log { uuid id PK uuid organization_id FK uuid consent_id FK uuid disclosed_by FK } cl_reference_labs { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_reference_ranges { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_referral_status_history { uuid id PK uuid organization_id FK uuid created_by FK } cl_report_definitions { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_report_runs { uuid id PK uuid report_definition_id FK uuid organization_id FK uuid generated_by FK } cl_review_appeals { uuid id PK uuid organization_id FK uuid review_id FK } cl_review_schedules { uuid id PK uuid organization_id FK } cl_risk_screenings { uuid id PK uuid organization_id FK uuid chart_id FK uuid screened_by FK uuid created_by FK uuid updated_by FK } cl_risk_stratifications { uuid id PK uuid organization_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_safety_plan_shares { uuid id PK uuid organization_id FK uuid safety_plan_id FK uuid shared_by FK uuid created_by FK uuid updated_by FK } cl_safety_plans { uuid id PK uuid organization_id FK uuid chart_id FK uuid clinician_id FK uuid superseded_by FK uuid created_by FK uuid updated_by FK } cl_screening_administrations { uuid id PK uuid organization_id FK } cl_sdoh_screenings { uuid id PK uuid organization_id FK uuid chart_id FK uuid screened_by FK uuid created_by FK uuid updated_by FK } cl_social_referrals { uuid id PK uuid organization_id FK uuid chart_id FK uuid screening_id FK uuid referred_by FK uuid created_by FK uuid updated_by FK } cl_standing_order_executions { uuid id PK uuid organization_id FK uuid standing_order_id FK uuid chart_id FK uuid executed_by FK uuid created_by FK } cl_standing_order_protocols { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } cl_tefca_exchange_log { uuid id PK uuid organization_id FK uuid site_id FK uuid consent_id FK uuid created_by FK uuid updated_by FK } cl_telehealth_consents { uuid id PK uuid organization_id FK uuid chart_id FK uuid captured_by FK uuid created_by FK uuid updated_by FK } cl_telehealth_sessions { uuid id PK uuid organization_id FK uuid chart_id FK uuid progress_note_id FK uuid created_by FK uuid updated_by FK } cl_transitions { uuid id PK uuid organization_id FK uuid chart_id FK uuid site_id FK uuid medication_reconciliation_id FK uuid created_by FK uuid updated_by FK } cl_treatment_goal_bank { uuid id PK uuid organization_id FK uuid template_id FK uuid created_by FK uuid updated_by FK } cl_treatment_goals { uuid id PK uuid organization_id FK uuid plan_id FK uuid problem_id FK uuid created_by FK uuid updated_by FK } cl_treatment_interventions { uuid id PK uuid organization_id FK uuid goal_id FK uuid responsible_provider_id FK uuid created_by FK uuid updated_by FK } cl_treatment_plan_signatures { uuid id PK uuid organization_id FK uuid treatment_plan_id FK uuid signer_id FK uuid created_by FK uuid updated_by FK } cl_treatment_plan_templates { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_treatment_plans { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid clinician_signed_by FK uuid previous_plan_id FK uuid created_by FK uuid updated_by FK } cl_uds_interpretation_notes { uuid id PK uuid organization_id FK uuid uds_order_id FK uuid chart_id FK uuid encounter_id FK uuid created_by FK uuid updated_by FK } cl_uds_orders { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid encounter_id FK uuid cl_order_id FK uuid collector_id FK uuid created_by FK uuid updated_by FK } cl_uds_results { uuid id PK uuid organization_id FK uuid uds_order_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_user_icd10_favorites { uuid user_id FK uuid organization_id FK } cl_vital_signs { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid created_by FK uuid updated_by FK } cl_warm_handoffs { uuid id PK uuid organization_id FK uuid site_id FK uuid chart_id FK uuid caller_id FK uuid receiver_id FK uuid created_by FK uuid updated_by FK } cl_weno_directory_ingest_runs { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } cl_weno_launch_attempts { uuid id PK uuid organization_id FK uuid user_id FK uuid created_by FK uuid updated_by FK } cl_weno_newrx_sync_runs { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } ``` ## Tables ### `cl_988_transfers` CL-07 EN-26: Documents 988 Crisis Line warm transfer outcomes **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `screening_id` | uuid | yes | | | | `safety_plan_id` | uuid | yes | | | | `transfer_date` | timestamp with time zone | no | now() | | | `outcome` | text | no | | | | `follow_up_plan` | text | yes | | | | `counselor_name` | text | yes | | | | `peer_role` | text | yes | | | | `hcpcs_code` | text | yes | | | | `language_preference` | text | yes | | | | `interpreter_used` | boolean | no | false | | | `cultural_notes` | text | yes | | | | `notes` | text | yes | | | | `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 | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `safety_plan_id` → `cl_safety_plans(id)` * `screening_id` → `cl_risk_screenings(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_988_transfers_delete` (DELETE) * `cl_988_transfers_insert` (INSERT) * `cl_988_transfers_select` (SELECT) * `cl_988_transfers_update` (UPDATE) ### `cl_aftercare_plans` CL-29: Structured discharge aftercare plans linked to patient charts and optionally to care transitions **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `transition_id` | uuid | yes | | Optional link to cl\_care\_transitions(id) when CL-12 schema exists; plain UUID, no FK constraint | | `plan_date` | date | no | | | | `follow_up_appointments` | text | yes | | | | `medication_continuation` | text | yes | | | | `community_resources` | text | yes | | | | `housing_plan` | text | yes | | | | `employment_plan` | text | yes | | | | `recovery_goals` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"family\_meeting\_notes": "...", "caregiver\_education": "..."}) | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `hedis_fuh_denominator` | boolean | no | false | True if discharge qualifies for HEDIS FUH measure denominator (age >= 6, qualifying MH dx, IP/residential discharge) | | `hedis_fum_denominator` | boolean | no | false | True if discharge qualifies for HEDIS FUM measure denominator (age >= 6, qualifying MH dx, ED visit) | | `hedis_fuh_numerator_7` | boolean | no | false | True if qualifying FUH follow-up occurred within 7 days of discharge | | `hedis_fuh_numerator_30` | boolean | no | false | True if qualifying FUH follow-up occurred within 30 days of discharge | | `hedis_fum_numerator_7` | boolean | no | false | True if qualifying FUM follow-up occurred within 7 days of ED visit | | `hedis_fum_numerator_30` | boolean | no | false | True if qualifying FUM follow-up occurred within 30 days of ED visit | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_aftercare_plans_delete` (DELETE) * `cl_aftercare_plans_insert` (INSERT) * `cl_aftercare_plans_select` (SELECT) * `cl_aftercare_plans_update` (UPDATE) ### `cl_ai_documentation_sessions` CL-36: Immutable audit log of AI-assisted clinical documentation sessions. No DELETE allowed. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------------ | --------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `user_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `encounter_id` | uuid | yes | | | | `note_id` | uuid | yes | | | | `note_type` | text | no | | | | `session_started_at` | timestamp with time zone | no | now() | | | `session_ended_at` | timestamp with time zone | yes | | | | `status` | text | no | 'initiated'::text | | | `ai_vendor` | text | yes | | | | `draft_source` | text | no | 'structured\_data'::text | | | `attestation_at` | timestamp with time zone | yes | | | | `attestation_by` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"model\_version": "v2", "input\_token\_count": 1234}) | | `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:** * `attestation_by` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` * `user_id` → `pf_profiles(id)` **Policies:** * `cl_ai_doc_sessions_insert` (INSERT) * `cl_ai_doc_sessions_select` (SELECT) * `cl_ai_doc_sessions_update` (UPDATE) ### `cl_ai_note_attributions` CL-36: Attribution records for AI-assisted clinical notes. Links notes to AI sessions with attestation tracking. No DELETE allowed. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ---------------------------------- | --------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `note_id` | uuid | no | | | | `note_type` | text | no | | | | `ai_session_id` | uuid | no | | | | `ai_assisted` | boolean | no | false | | | `ai_attestation_at` | timestamp with time zone | yes | | | | `ai_attestation_by` | uuid | yes | | | | `attribution_label` | text | no | 'Drafted with AI assistance'::text | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"edit\_distance\_pct": 42.5}) | | `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:** * `ai_attestation_by` → `pf_profiles(id)` * `ai_session_id` → `cl_ai_documentation_sessions(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_ai_note_attr_insert` (INSERT) * `cl_ai_note_attr_select` (SELECT) * `cl_ai_note_attr_update` (UPDATE) ### `cl_allergies` CL-01: Patient allergy records with severity classification and verification tracking. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `allergen_type` | text | no | 'drug'::text | | | `allergen` | text | no | | | | `reaction` | text | yes | | | | `severity` | text | no | 'moderate'::text | | | `status` | text | no | 'active'::text | | | `onset_date` | date | yes | | | | `verified_by` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `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 | | | | `site_id` | uuid | yes | | | | `allergen_code` | text | yes | | | | `allergen_code_system` | text | yes | | | | `allergen_class_code` | text | yes | | CL-45: RxNorm class code for class-based drug-allergy alert matching. | | `allergen_display` | text | yes | | | | `is_sud_mat_class` | boolean | no | false | CL-45: True if allergen RxNorm class is in SUD-MAT set. Gates 42 CFR Part 2 consent check via cl\_has\_sud\_consent(chart\_id). | | `reaction_code` | text | yes | | | | `reaction_display` | text | yes | | | | `reaction_severity` | text | yes | | | | `criticality` | text | yes | | CL-45: FHIR AllergyIntolerance.criticality (low \| high \| unable-to-assess). UI maps legacy severity=life\_threatening to criticality=high. | | `verification_status` | text | no | 'unconfirmed'::text | | | `nka_flag` | text | yes | | CL-45: NKA/NKDA confirmation. NULL when allergens are documented; mutually exclusive with allergen\_code. | | `last_occurrence_date` | date | yes | | | | `notes` | text | yes | | | | `documented_by` | uuid | yes | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `documented_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `verified_by` → `pf_profiles(id)` **Policies:** * `cl_allergies_delete` (DELETE) * `cl_allergies_insert` (INSERT) * `cl_allergies_select` (SELECT) * `cl_allergies_update` (UPDATE) ### `cl_allergy_alert_overrides` CL-45: Append-only audit of drug-allergy alert overrides during prescribing (CL-05/CL-06). Mirrored to GR audit via cl\_allergy\_alert\_overridden event. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `allergy_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `ordered_drug_code` | text | no | | | | `ordered_drug_display` | text | yes | | | | `match_type` | text | no | | | | `override_rationale` | text | no | | | | `overridden_by` | uuid | no | | | | `overridden_at` | timestamp with time zone | no | now() | | | `context_event_id` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `allergy_id` → `cl_allergies(id)` * `chart_id` → `cl_patient_charts(id)` * `organization_id` → `pf_organizations(id)` * `overridden_by` → `pf_profiles(id)` **Policies:** * `cl_allergy_overrides_insert` (INSERT) * `cl_allergy_overrides_select` (SELECT) ### `cl_ambient_session_events` CL-36-EN-01: Append-only audit log for ambient session lifecycle events (start, stop, approve, reject, break-glass, etc.). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `ambient_session_id` | uuid | no | | | | `event_type` | text | no | | | | `reason` | text | yes | | | | `metadata` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `ambient_session_id` → `cl_ambient_sessions(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_ambient_session_events_delete` (DELETE) * `cl_ambient_session_events_insert` (INSERT) * `cl_ambient_session_events_select` (SELECT) * `cl_ambient_session_events_update` (UPDATE) ### `cl_ambient_sessions` CL-36-EN-01: Ambient audio capture sessions linked to encounters. Tracks recording lifecycle, processing status, and audio retention policy. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `encounter_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `provider_id` | uuid | no | | | | `consent_record_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `session_start` | timestamp with time zone | no | | | | `session_end` | timestamp with time zone | yes | | | | `processing_status` | text | no | 'recording'::text | Session lifecycle: recording → processing → draft\_ready → approved \| failed \| cancelled | | `draft_note_id` | uuid | yes | | | | `audio_storage_path` | text | yes | | | | `audio_deleted_at` | timestamp with time zone | yes | | | | `audio_retention_policy` | text | no | 'immediate'::text | Audio file retention: immediate (delete after processing), 7\_days, 30\_days, 90\_days | | `error_message` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"vendor\_session\_id": "abc123"}) | | `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:** * `chart_id` → `cl_patient_charts(id)` * `consent_record_id` → `cl_consents(id)` * `created_by` → `pf_profiles(id)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pf_profiles(id)` * `provider_id` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_ambient_sessions_delete` (DELETE) * `cl_ambient_sessions_insert` (INSERT) * `cl_ambient_sessions_select` (SELECT) * `cl_ambient_sessions_update` (UPDATE) ### `cl_ambient_transcripts` CL-36-EN-01: Immutable transcript records from ambient audio processing. Contains raw transcript, speaker diarization, and structured SOAP sections. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `ambient_session_id` | uuid | no | | | | `transcript_data` | jsonb | no | '\{}'::jsonb | | | `diarization_data` | jsonb | no | '\{}'::jsonb | | | `soap_sections` | jsonb | no | '\{}'::jsonb | Structured SOAP note sections: \{"subjective": "...", "objective": "...", "assessment": "...", "plan": "..."} | | `confidence_score` | numeric(5,4) | yes | | | | `word_count` | integer | yes | | | | `processing_duration_ms` | integer | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `ambient_session_id` → `cl_ambient_sessions(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_ambient_transcripts_delete` (DELETE) * `cl_ambient_transcripts_insert` (INSERT) * `cl_ambient_transcripts_select` (SELECT) * `cl_ambient_transcripts_update` (UPDATE) ### `cl_assessment_instruments` CL-02: Configurable assessment instruments per organization. Defines scoring rules, age ranges, and reassessment frequency. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `instrument_code` | text | no | | Machine-readable identifier (e.g., calocus, asam, phq9, gad7) | | `instrument_name` | text | no | | | | `age_min` | integer | yes | | Minimum patient age for instrument applicability (inclusive) | | `age_max` | integer | yes | | Maximum patient age for instrument applicability (inclusive) | | `frequency_months` | integer | yes | | Required reassessment interval in months | | `scoring_rules` | jsonb | no | '\{}'::jsonb | Scoring configuration: \{ method: "sum"\|"weighted\_sum", ranges: \[...], weights: \{...} } | | `is_active` | boolean | no | true | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific instrument 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)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_assessment_instruments_delete` (DELETE) * `cl_assessment_instruments_insert` (INSERT) * `cl_assessment_instruments_select` (SELECT) * `cl_assessment_instruments_update` (UPDATE) ### `cl_assessment_sections` CL-02: Individual sections within an assessment (e.g., 18 AHCCCS elements). section\_data stores the structured responses. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `assessment_id` | uuid | no | | | | `section_code` | text | no | | Machine-readable section identifier (e.g., presenting\_problem, mental\_status\_exam) | | `section_name` | text | no | | | | `section_data` | jsonb | no | '\{}'::jsonb | Structured JSONB data for the section responses | | `is_complete` | boolean | no | false | Whether this section has been fully completed | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific section 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:** * `assessment_id` → `cl_assessments(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_assessment_sections_delete` (DELETE) * `cl_assessment_sections_insert` (INSERT) * `cl_assessment_sections_select` (SELECT) * `cl_assessment_sections_update` (UPDATE) ### `cl_assessment_template_sections` CL-02-EN-61: Sections within an assessment template **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `template_id` | uuid | no | | | | `section_code` | text | no | | | | `section_name` | text | no | | | | `display_order` | integer | no | 0 | | | `field_schema` | jsonb | no | '\{}'::jsonb | JSON schema defining the section fields and layout | | `is_required` | boolean | no | false | | | `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)` * `template_id` → `cl_assessment_templates(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_assessment_template_sections_delete` (DELETE) * `cl_assessment_template_sections_insert` (INSERT) * `cl_assessment_template_sections_select` (SELECT) * `cl_assessment_template_sections_update` (UPDATE) ### `cl_assessment_templates` CL-02-EN-61: Configurable assessment template definitions per organization **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 | | | | `assessment_type` | text | no | 'comprehensive'::text | | | `version` | integer | no | 1 | | | `status` | text | no | 'draft'::text | | | `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)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_assessment_templates_delete` (DELETE) * `cl_assessment_templates_insert` (INSERT) * `cl_assessment_templates_select` (SELECT) * `cl_assessment_templates_update` (UPDATE) ### `cl_assessments` CL-02: Comprehensive clinical assessments. Supports 18-element AHCCCS, CALOCUS, ASAM, and other standardized instruments. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | --------------------- | ----------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `assessment_type` | text | no | 'comprehensive'::text | Type of assessment: comprehensive (18-element AHCCCS), calocus, asam, developmental, substance\_use, review | | `instrument` | text | yes | | | | `status` | text | no | 'draft'::text | Lifecycle: draft → completed → signed. Signed records are immutable (amendment path only). | | `score` | numeric | yes | | Numeric score from instrument scoring (e.g., CALOCUS total) | | `score_details` | jsonb | yes | | Detailed scoring breakdown (subscores, domain scores) | | `recommended_loc` | text | yes | | Recommended level of care based on instrument scoring | | `clinician_id` | uuid | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `expires_at` | timestamp with time zone | yes | | | | `signed_at` | timestamp with time zone | yes | | | | `signed_by` | uuid | yes | | | | `cosigned_at` | timestamp with time zone | yes | | | | `cosigned_by` | uuid | yes | | | | `supersedes_id` | uuid | yes | | Links to the prior assessment this one amends/supersedes | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific assessment 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 | | | | `template_id` | uuid | yes | | | | `cod_assessment_id` | uuid | yes | | CL-31: Mirror link to cl\_cod\_assessments for the unified CL-02 timeline. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `clinician_id` → `pf_profiles(id)` * `cod_assessment_id` → `cl_cod_assessments(id)` * `cosigned_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `signed_by` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `supersedes_id` → `cl_assessments(id)` * `template_id` → `cl_assessment_templates(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_assessments_delete` (DELETE) * `cl_assessments_insert` (INSERT) * `cl_assessments_select` (SELECT) * `cl_assessments_update` (UPDATE) ### `cl_audit_dashboard_break_glass_reviews` CL-25: Append-only log of break-glass access reviews by compliance staff **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `audit_log_id` | uuid | no | | References pf\_audit\_logs.id for the break-glass event being reviewed | | `reviewed_by` | uuid | no | | | | `review_notes` | text | yes | | | | `reviewed_at` | timestamp with time zone | no | now() | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `reviewed_by` → `pf_profiles(id)` **Policies:** * `cl_audit_bg_reviews_insert` (INSERT) * `cl_audit_bg_reviews_select` (SELECT) ### `cl_between_session_checkins` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `checkin_at` | timestamp with time zone | no | | | | `mood_rating` | integer | yes | | | | `medication_adherence` | text | yes | | | | `crisis_flag` | boolean | no | false | | | `crisis_warning_signs` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_between_session_checkins_clinician_select` (SELECT) * `cl_between_session_checkins_portal_insert` (INSERT) * `cl_between_session_checkins_portal_select` (SELECT) ### `cl_biometrics` CL-22: Biometric measurements (height, weight, BMI, waist circumference) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `captured_at` | timestamp with time zone | no | | | | `height_cm` | numeric | yes | | | | `weight_kg` | numeric | yes | | | | `bmi` | numeric | yes | | | | `waist_circumference_cm` | numeric | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata; do not store structured PHI; constitution §5.2.1 | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_biometrics_delete` (DELETE) * `cl_biometrics_insert` (INSERT) * `cl_biometrics_select` (SELECT) * `cl_biometrics_update` (UPDATE) ### `cl_care_gaps` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------------ | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `gap_type` | text | no | | | | `priority` | text | no | 'routine'::text | | | `status` | text | no | 'open'::text | | | `detected_at` | timestamp with time zone | no | now() | | | `due_date` | date | yes | | | | `closed_at` | timestamp with time zone | yes | | | | `closed_by` | uuid | yes | | Actor that closed the gap. Holds an auth.users.id for human closures, the SYSTEM\_AUTOMATION\_PROFILE\_ID sentinel (00000000-0000-0000-0000-000000000001) for cron auto-close, or NULL if never closed. Intentionally NOT a foreign key so the system sentinel is permitted. | | `override_reason` | text | yes | | | | `source_rule` | text | yes | | | | `source_details` | jsonb | no | '\{}'::jsonb | | | `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 | | | | `closure_type` | text | yes | | CL-35 Phase 2: Categorization of how the gap was closed. Auto-close types: service\_completed, medication\_prescribed (set by cron). Manual types: clinician\_override, not\_applicable, patient\_refused, transferred\_out. | | `risk_score_at_identification` | numeric(5,2) | yes | | CL-35 Phase 2: Composite risk score (0-100) snapshot at the moment the care gap was detected, for retrospective analysis. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `cl_care_gaps_delete` (DELETE) * `cl_care_gaps_insert` (INSERT) * `cl_care_gaps_select` (SELECT) * `cl_care_gaps_update` (UPDATE) ### `cl_care_teams` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `provider_id` | uuid | yes | | | | `external_provider_name` | text | yes | | | | `external_provider_npi` | text | yes | | | | `role` | text | no | | | | `is_primary` | boolean | no | false | | | `start_date` | date | no | | | | `end_date` | date | yes | | | | `notes` | text | 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:** * `created_by` → `pf_profiles(id)` * `provider_id` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_care_teams_delete` (DELETE) * `cl_care_teams_insert` (INSERT) * `cl_care_teams_select` (SELECT) * `cl_care_teams_update` (UPDATE) ### `cl_cda_documents` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `document_type` | text | no | | | | `cda_xml` | text | no | | | | `validation_status` | text | no | 'pending'::text | | | `validation_errors` | jsonb | no | '\[]'::jsonb | | | `transmission_status` | text | no | 'draft'::text | | | `recipient_endpoint_id` | uuid | yes | | | | `contains_part2_data` | boolean | no | false | | | `redisclosure_notice` | text | yes | | | | `generated_at` | timestamp with time zone | no | now() | | | `transmitted_at` | timestamp with time zone | yes | | | | `delivery_confirmed_at` | timestamp with time zone | yes | | | | `retry_count` | integer | no | 0 | | | `last_error` | text | yes | | | | `next_retry_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:** * `chart_id` → `cl_patient_charts(id)` * `organization_id` → `pf_organizations(id)` * `recipient_endpoint_id` → `cl_cda_transmission_endpoints(id)` **Policies:** * `cl_cda_documents_delete` (DELETE) * `cl_cda_documents_insert` (INSERT) * `cl_cda_documents_select` (SELECT) * `cl_cda_documents_update` (UPDATE) ### `cl_cda_transmission_endpoints` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `endpoint_name` | text | no | | | | `direct_address` | text | no | | | | `recipient_organization` | text | yes | | | | `endpoint_type` | text | no | 'direct'::text | | | `is_active` | boolean | no | true | | | `notes` | text | 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:** * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_cda_endpoints_delete` (DELETE) * `cl_cda_endpoints_insert` (INSERT) * `cl_cda_endpoints_select` (SELECT) * `cl_cda_endpoints_update` (UPDATE) ### `cl_cds_alerts` CL-08: CDS alerts. Append-only audit trail. PHI: contains clinical context. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `rule_id` | uuid | yes | | | | `alert_type` | text | no | | | | `message` | text | no | | PHI: Alert message may contain drug names. | | `severity` | text | no | 'moderate'::text | | | `triggered_at` | timestamp with time zone | no | now() | | | `acknowledged_by` | uuid | yes | | | | `acknowledged_at` | timestamp with time zone | yes | | | | `overridden` | boolean | no | false | | | `override_reason` | text | 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:** * `acknowledged_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `rule_id` → `cl_cds_rules(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_cds_alerts_insert` (INSERT) * `cl_cds_alerts_select` (SELECT) * `cl_cds_alerts_update` (UPDATE) ### `cl_cds_rules` CL-08: Configurable clinical decision support rules. Soft-delete via deleted\_at. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `rule_name` | text | no | | | | `rule_type` | text | no | | | | `trigger_event` | text | no | | | | `condition` | jsonb | no | '\{}'::jsonb | | | `action` | jsonb | no | '\{}'::jsonb | | | `severity` | text | no | 'moderate'::text | | | `is_hard_stop` | boolean | no | false | | | `is_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 | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_cds_rules_delete` (DELETE) * `cl_cds_rules_insert` (INSERT) * `cl_cds_rules_select` (SELECT) * `cl_cds_rules_update` (UPDATE) ### `cl_clinical_standing_orders` CL-44: Cross-category standing order protocols (medication/lab/assessment/monitoring/nursing\_action) executable by nursing staff within defined parameters. **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 | | | | `order_type` | text | no | | | | `parameters` | jsonb | no | | | | `authorized_roles` | text\[] | no | '\{}'::text\[] | | | `approved_by` | uuid | no | | | | `approved_at` | timestamp with time zone | no | now() | | | `effective_date` | date | no | CURRENT\_DATE | | | `expiration_date` | date | yes | | | | `status` | text | no | 'active'::text | | | `renewal_history` | jsonb | no | '\[]'::jsonb | | | `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:** * `approved_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_clinical_standing_orders_delete` (DELETE) * `cl_clinical_standing_orders_insert` (INSERT) * `cl_clinical_standing_orders_select` (SELECT) * `cl_clinical_standing_orders_update` (UPDATE) ### `cl_cod_assessments` CL-31: Integrated COD assessments (ASI-6, BASIS-32, GAIN-SS). SUD domain keys redacted at app layer per 42 CFR Part 2. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `assessment_type` | text | no | | | | `assessment_date` | date | no | | | | `assessor_id` | uuid | no | | | | `status` | text | no | 'draft'::text | | | `domain_scores` | jsonb | no | '\{}'::jsonb | Instrument-specific JSON. ASI-6 requires 7 domain keys. SUD keys redacted by redactSUDFields when consent absent. | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | no | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `assessor_id` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cod_assessments_delete` (DELETE) * `cod_assessments_insert` (INSERT) * `cod_assessments_select` (SELECT) * `cod_assessments_update` (UPDATE) ### `cl_cod_treatment_plan_extension` CL-31: COD-specific extension to CL-03 treatment plans. ASAM level + dimensions 5/6 + optional MOUD link. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `treatment_plan_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `asam_cod_level` | text | yes | | | | `asam_dimension_5_notes` | text | yes | | | | `asam_dimension_6_notes` | text | yes | | | | `integrated_goals_summary` | text | yes | | | | `moud_episode_id` | uuid | yes | | | | `archived_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 | no | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `moud_episode_id` → `cl_moud_enrollments(id)` * `organization_id` → `pf_organizations(id)` * `treatment_plan_id` → `cl_treatment_plans(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cod_tp_ext_insert` (INSERT) * `cod_tp_ext_select` (SELECT) * `cod_tp_ext_update` (UPDATE) ### `cl_concurrent_reviews` CL-43: Concurrent review submissions and outcomes for utilization management. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `chart_id` | uuid | yes | | | | `authorization_id` | uuid | no | | CL-43: References pm\_prior\_authorizations.id (UUID-only cross-core reference). | | `reviewer_id` | uuid | yes | | | | `review_date` | date | yes | | | | `review_due_date` | date | no | | | | `review_type` | text | no | | | | `status` | text | no | 'scheduled'::text | | | `determination` | text | yes | | | | `approved_days` | integer | yes | | | | `approved_through_date` | date | yes | | | | `payer_reference` | text | yes | | | | `denial_reason` | text | yes | | | | `clinical_summary` | jsonb | yes | '\{}'::jsonb | CL-43: Structured JSONB summary; excludes SUD narrative. | | `criteria_responses` | jsonb | yes | '\{}'::jsonb | CL-43: InterQual/MCG criteria domain responses (JSONB). | | `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:** * `chart_id` → `cl_patient_charts(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_concurrent_reviews_delete` (DELETE) * `cl_concurrent_reviews_insert` (INSERT) * `cl_concurrent_reviews_select` (SELECT) * `cl_concurrent_reviews_update` (UPDATE) ### `cl_consent_requests` CL-11 EN-35: Patient portal consent change requests (revocation, scope change, renewal). Reviewed by staff. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `consent_id` | uuid | yes | | | | `request_type` | text | no | | | | `request_details` | jsonb | no | '\{}'::jsonb | | | `status` | text | no | 'pending'::text | | | `reviewed_by` | uuid | yes | | | | `reviewed_at` | timestamp with time zone | yes | | | | `review_notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for consent requests. | | `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. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `consent_id` → `cl_consents(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `reviewed_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `cl_consent_requests_delete` (DELETE) * `cl_consent_requests_insert` (INSERT) * `cl_consent_requests_select` (SELECT) * `cl_consent_requests_update` (UPDATE) ### `cl_consents` CL-11: Patient consent records for 42 CFR Part 2, HIPAA, and organizational consent types. PHI. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `site_id` | uuid | yes | | Site where consent was captured (optional). | | `consent_type` | text | no | | Type: tpo, sud\_counseling\_notes, legal\_proceedings, treatment, telehealth, medication, research, general. | | `consent_category` | text | no | | Consent category: standard (general clinical), part2 (42 CFR Part 2 SUD), minor (Arizona age 12+), guardian (legal guardian) | | `scope` | jsonb | no | '\{}'::jsonb | JSONB: record\_types\[], date\_range?, restrictions\[], purpose\[]. See EN-3. | | `purpose` | text | no | | | | `named_parties` | jsonb | yes | '\[]'::jsonb | JSONB array of parties authorized to receive disclosures. | | `effective_date` | date | no | | | | `expiration_date` | date | yes | | | | `revoked_at` | timestamp with time zone | yes | | | | `revocation_reason` | text | yes | | | | `signed_by_patient_at` | timestamp with time zone | yes | | | | `witnessed_by` | uuid | yes | | | | `document_id` | uuid | yes | | | | `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. NULL = active. Hard delete restricted to org\_admin. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `witnessed_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_consents_delete` (DELETE) * `cl_consents_insert` (INSERT) * `cl_consents_select` (SELECT) * `cl_consents_update` (UPDATE) ### `cl_controlled_substance_inventory` CL-06 EN-21: Tracks controlled substance transactions (receive, dispense, adjust, count, return, destroy) per 21 CFR 1304. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `ndc_code` | text | no | | | | `medication_name` | text | no | | | | `schedule` | text | no | | DEA schedule classification: II, III, IV, V | | `transaction_type` | text | no | | | | `quantity` | numeric | no | | | | `unit` | text | no | | | | `lot_number` | text | yes | | | | `expiration_date` | date | yes | | | | `reference_prescription_id` | uuid | yes | | | | `reference_external` | text | yes | | | | `performed_by` | uuid | no | | | | `witness_id` | uuid | yes | | Required for Schedule II dispensing per 21 CFR 1304 | | `notes` | text | yes | | | | `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. | **Foreign keys:** * `reference_prescription_id` → `cl_prescriptions(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `performed_by` → `users(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `witness_id` → `users(id)` **Policies:** * `cl_csi_delete` (DELETE) * `cl_csi_insert` (INSERT) * `cl_csi_select` (SELECT) * `cl_csi_update` (UPDATE) ### `cl_cron_run_log` CL-24+: per-day cron idempotency log. Used by cl-telehealth-consent-expiry-scan to prevent re-publish within 24h. **RLS:** ✅ enabled | Column | Type | Nullable | Default | Comment | | ---------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `job_name` | text | no | | | | `run_date` | date | no | | | | `rows_processed` | integer | no | 0 | | | `notes` | text | yes | | | | `created_at` | timestamp with time zone | no | now() | | ### `cl_data_exchange_log` CL-16: Immutable audit trail for all FHIR, C-CDA, HIE, and bulk data exchanges. Append-only — no UPDATE or DELETE. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `exchange_type` | text | no | | Type of data exchange: fhir\_api, ccda, adt, hie, bulk\_export, patient\_access | | `direction` | text | no | | | | `partner_id` | text | yes | | | | `partner_name` | text | yes | | | | `resource_types` | jsonb | yes | '\[]'::jsonb | | | `record_count` | integer | yes | | | | `status` | text | no | | | | `initiated_at` | timestamp with time zone | no | now() | | | `completed_at` | timestamp with time zone | yes | | | | `error_details` | text | yes | | 21st Century Cures Act compliance: captures error context for information blocking investigations | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` **Policies:** * `cl_data_exchange_log_insert` (INSERT) * `cl_data_exchange_log_select` (SELECT) ### `cl_ddi_alert_suppressions` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `provider_id` | uuid | no | | | | `rxcui_1` | text | no | | | | `rxcui_2` | text | no | | | | `eligible_severity` | text | no | | | | `suppressed_reason` | text | yes | | | | `revoked_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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `provider_id` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `cl_ddi_alert_suppressions_delete` (DELETE) * `cl_ddi_alert_suppressions_insert` (INSERT) * `cl_ddi_alert_suppressions_select` (SELECT) * `cl_ddi_alert_suppressions_update` (UPDATE) ### `cl_ddi_overrides` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `medication_id` | uuid | no | | | | `interacting_medication_id` | uuid | no | | | | `rxcui_pair` | text\[] | no | | | | `severity` | text | no | | | | `reason_code` | text | no | | | | `reason_text` | text | yes | | | | `acknowledged_by` | uuid | no | | | | `cosigner_id` | uuid | yes | | | | `cosigned_at` | timestamp with time zone | yes | | | | `consent_id` | uuid | yes | | | | `override_context` | jsonb | no | '\{}'::jsonb | | | `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:** * `acknowledged_by` → `users(id)` * `chart_id` → `cl_patient_charts(id)` * `consent_id` → `cl_consents(id)` * `cosigner_id` → `users(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `cl_ddi_overrides_delete` (DELETE) * `cl_ddi_overrides_insert` (INSERT) * `cl_ddi_overrides_select` (SELECT) * `cl_ddi_overrides_update` (UPDATE) ### `cl_designated_contacts` CL-07 EN-25: Family/emergency contacts designated for risk notifications **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `contact_name` | text | no | | | | `relationship` | text | yes | | | | `phone` | text | yes | | | | `email` | text | yes | | | | `notification_enabled` | boolean | no | true | | | `consent_verified_at` | timestamp with time zone | yes | | | | `consent_verified_by` | uuid | yes | | | | `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 | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `consent_verified_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_designated_contacts_delete` (DELETE) * `cl_designated_contacts_insert` (INSERT) * `cl_designated_contacts_select` (SELECT) * `cl_designated_contacts_update` (UPDATE) ### `cl_diagnoses` CL-40: Patient diagnoses with ICD-10 codes, linked to patient charts **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `icd10_code` | text | no | | | | `description` | text | no | | | | `diagnosis_type` | text | yes | 'preliminary'::text | preliminary, confirmed, rule\_out, resolved | | `status` | text | no | 'active'::text | | | `onset_date` | date | yes | | | | `resolved_date` | date | yes | | | | `entered_by` | uuid | yes | | | | `confirmed_at` | timestamp with time zone | yes | | | | `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 | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `entered_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_diagnoses_delete` (DELETE) * `cl_diagnoses_insert` (INSERT) * `cl_diagnoses_select` (SELECT) * `cl_diagnoses_update` (UPDATE) ### `cl_discharge_checklists` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `transition_id` | uuid | no | | | | `items` | jsonb | no | '\[]'::jsonb | | | `overall_status` | text | no | 'pending'::text | | | `waived_reason` | text | yes | | | | `waived_by` | uuid | yes | | | | `waived_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:** * `created_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` * `waived_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` * `organization_id, transition_id` → `cl_transitions(organization_id, id)` **Policies:** * `cl_discharge_checklists_delete` (DELETE) * `cl_discharge_checklists_insert` (INSERT) * `cl_discharge_checklists_select` (SELECT) * `cl_discharge_checklists_update` (UPDATE) ### `cl_disclosure_log` CL-11: Append-only disclosure log for 42 CFR Part 2 accounting of disclosures. PHI. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `consent_id` | uuid | yes | | | | `disclosed_to` | text | no | | | | `disclosure_purpose` | text | no | | | | `disclosure_date` | timestamp with time zone | no | now() | | | `record_types_disclosed` | jsonb | yes | '\[]'::jsonb | | | `consent_scope_summary` | text | yes | | | | `redisclosure_notice_included` | boolean | no | true | Whether redisclosure prohibition notice was included (Part 2 requirement). | | `disclosed_by` | uuid | no | | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `consent_id` → `cl_consents(id)` * `created_by` → `pf_profiles(id)` * `disclosed_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_disclosure_log_delete` (DELETE) * `cl_disclosure_log_insert` (INSERT) * `cl_disclosure_log_select` (SELECT) * `cl_disclosure_log_update` (UPDATE) ### `cl_dsi_disclosures` CL-65: §170.315(b)(11) DSI source-attribute disclosure registry. One published row per (org, model\_id, model\_version) gates AI clinical features (CL-36 note drafting). Org-scoped readable; publish/retire is org-admin-only; rows are append-versioned (retire, do not delete) for audit continuity. **RLS:** ✅ enabled (5 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `model_id` | text | no | | CL-65: stable identifier of the model/algorithm the disclosure describes (matches the active PHI-lane model resolved by resolveModels in the edge function). | | `model_version` | text | no | | CL-65: model version the disclosure is valid for; the generation gate matches on (model\_id, model\_version, status=published). | | `status` | text | no | 'draft'::text | | | `published_at` | timestamp with time zone | yes | | | | `published_by` | uuid | yes | | | | `retired_at` | timestamp with time zone | yes | | | | `intended_use` | text | yes | | | | `intended_patient_population` | text | yes | | | | `intended_users` | text | yes | | | | `intended_decision_role` | text | yes | | | | `cautioned_out_of_scope` | text | yes | | | | `training_data_description` | text | yes | | | | `fairness_development_approach` | text | yes | | | | `external_validation_description` | text | yes | | | | `quantitative_performance` | jsonb | no | '\{}'::jsonb | CL-65 §170.315(b)(11)(iv)(B)(9): quantitative performance measures (AUC, sensitivity, specificity, PPV/NPV, etc.) as a metric->value map. JSONB because the metric set varies per model. | | `fairness_measures` | jsonb | no | '\{}'::jsonb | CL-65 §170.315(b)(11)(iv)(B)(10): validity/fairness measures by protected subgroup (race/ethnicity, gender, age, disability, primary language). JSONB because subgroups and per-group metrics are multi-valued. | | `monitoring_cadence` | text | yes | | | | `limitations` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Must not contain PHI — disclosure metadata only. | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `retired_by` | uuid | yes | | | | `developer_name` | text | yes | | | | `developer_contact` | text | yes | | | | `training_data_collection_methods` | text | yes | | | | `inclusion_exclusion_criteria` | text | yes | | | | `correlated_variables_disclosure` | text | yes | | | | `update_validation_process` | text | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `published_by` → `pf_profiles(id)` * `retired_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_dsi_disclosures_delete` (DELETE) * `cl_dsi_disclosures_insert` (INSERT) * `cl_dsi_disclosures_public_read` (SELECT) * `cl_dsi_disclosures_select` (SELECT) * `cl_dsi_disclosures_update` (UPDATE) ### `cl_electronic_consents` CL-11-EN-01: Signed electronic consent records with granular categories and e-signature data for 42 CFR Part 2 compliance **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `consent_categories` | jsonb | no | | Canonical consent categories JSONB: required keys treatment,payment,operations,specific\_providers,research. Each key maps to object with granted:boolean; optional scope:string; specific\_providers may include provider\_ids:uuid\[]. | | `effective_date` | date | no | | | | `expiration_date` | date | yes | | | | `signature_data` | jsonb | no | | | | `status` | text | no | 'active'::text | | | `revoked_at` | timestamp with time zone | yes | | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for electronic consent extensibility | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_electronic_consents_delete` (DELETE) * `cl_electronic_consents_insert` (INSERT) * `cl_electronic_consents_select` (SELECT) * `cl_electronic_consents_update` (UPDATE) ### `cl_environmental_assessments` CL-07: Environmental risk assessments for inpatient/residential settings. Ligature resistance per Joint Commission NPSG.15.01.01. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | no | | | | `chart_id` | uuid | yes | | Optional FK to cl\_patient\_charts. Set when assessment is linked to a specific patient admission (US-4 Clarification). | | `assessed_by` | uuid | no | | | | `assessment_date` | date | no | | | | `location_name` | text | no | | | | `location_type` | text | no | | | | `ligature_points_identified` | jsonb | no | '\[]'::jsonb | Array of identified ligature points (e.g., \[\{"location": "window blind cord", "risk": "high"}]). | | `ligature_risk_level` | text | yes | | | | `mitigation_actions` | jsonb | no | '\[]'::jsonb | Array of actions taken to reduce identified risks (e.g., \[\{"action": "Remove cord", "completed": true}]). | | `sharps_secured` | boolean | no | true | | | `medications_secured` | boolean | no | true | | | `windows_secured` | boolean | no | true | | | `status` | text | no | 'completed'::text | | | `next_assessment_due` | date | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific assessment 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 | | | **Foreign keys:** * `assessed_by` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_environmental_assessments_delete` (DELETE) * `cl_environmental_assessments_insert` (INSERT) * `cl_environmental_assessments_select` (SELECT) * `cl_environmental_assessments_update` (UPDATE) ### `cl_family_notification_log` CL-07 EN-25: Audit log of family notification decisions and outcomes **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `screening_id` | uuid | yes | | | | `contact_id` | uuid | no | | | | `risk_level` | text | no | | | | `consent_status` | text | no | | | | `notification_sent` | boolean | no | false | | | `notification_sent_at` | timestamp with time zone | yes | | | | `notification_channel` | text | yes | | | | `decision_reason` | text | yes | | | | `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:** * `chart_id` → `cl_patient_charts(id)` * `contact_id` → `cl_designated_contacts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `screening_id` → `cl_risk_screenings(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_family_notification_log_insert` (INSERT) * `cl_family_notification_log_select` (SELECT) ### `cl_fhir_resources` CL-16: Cached FHIR R4 resources mapped from internal clinical and patient data for interoperability. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `resource_type` | text | no | | FHIR resource type (Patient, Condition, MedicationRequest, AllergyIntolerance, Encounter, Observation, etc.) | | `resource_id` | text | no | | FHIR resource logical ID, unique within organization and resource\_type | | `fhir_version` | text | no | 'R4'::text | | | `resource_json` | jsonb | no | | Full FHIR R4 JSON resource conforming to US Core profile | | `source_system` | text | yes | | Origin system identifier (e.g., "northsight", "contexture\_hie") | | `last_synced_at` | timestamp with time zone | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"hie\_sync\_status": "pending", "external\_ref": "HIE-001"}) | | `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:** * `cl_fhir_resources_delete` (DELETE) * `cl_fhir_resources_insert` (INSERT) * `cl_fhir_resources_select` (SELECT) * `cl_fhir_resources_update` (UPDATE) ### `cl_follow_up_contacts` CL-29: Post-discharge follow-up contacts (7-day, 30-day) feeding HEDIS FUH measures **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `aftercare_plan_id` | uuid | yes | | | | `contact_type` | text | no | | | | `contact_at` | timestamp with time zone | no | | | | `outcome` | text | yes | | | | `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 | no | | | | `updated_by` | uuid | yes | | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active, non-NULL = soft deleted | | `contact_modality` | text | yes | | Modality of follow-up contact: in\_person, telehealth, telephone, other. HEDIS FUH counts in\_person and telehealth only. | **Foreign keys:** * `aftercare_plan_id` → `cl_aftercare_plans(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_follow_up_contacts_delete` (DELETE) * `cl_follow_up_contacts_insert` (INSERT) * `cl_follow_up_contacts_select` (SELECT) * `cl_follow_up_contacts_update` (UPDATE) ### `cl_group_attendance` CL-14: Per-patient attendance and individual documentation for group therapy sessions **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `session_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `attendance_status` | text | no | | | | `sign_in_time` | time without time zone | yes | | | | `sign_out_time` | time without time zone | yes | | | | `participation_level` | text | yes | | | | `individual_response` | text | yes | | PHI: Clinician notes on member individual response during group | | `goal_progress_notes` | text | yes | | PHI: Goal progress observations from group participation | | `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, non-NULL = soft deleted | | `recording_consent_captured_at` | timestamp with time zone | yes | | | | `recording_consent_modality` | text | yes | | | | `technology_check_completed_at` | timestamp with time zone | yes | | | | `emergency_contact_verified_at` | timestamp with time zone | yes | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `session_id` → `cl_group_sessions(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_group_attendance_delete` (DELETE) * `cl_group_attendance_insert` (INSERT) * `cl_group_attendance_select` (SELECT) * `cl_group_attendance_update` (UPDATE) ### `cl_group_curricula` CL-14-ENHANCEMENTS (EN-39): Curriculum library for group therapy sessions **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | -------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `name` | text | no | | | | `program_type` | text | yes | | | | `language` | text | yes | | | | `topics` | jsonb | no | '\[]'::jsonb | Ordered list of session topics as JSON array | | `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. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_group_curricula_delete` (DELETE) * `cl_group_curricula_insert` (INSERT) * `cl_group_curricula_select` (SELECT) * `cl_group_curricula_update` (UPDATE) ### `cl_group_encounter_generations` CL-14-EN-01: Tracks individual encounter generation from group session attendance for batch review and charge creation **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ----------------------- | ------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `group_session_id` | uuid | no | | | | `encounter_id` | uuid | yes | | | | `patient_id` | uuid | no | | Attendee chart ID — maps to cl\_group\_attendance.chart\_id (logical reference, no FK constraint) | | `attendance_status` | text | no | | | | `duration_minutes` | integer | yes | | | | `proposed_cpt_code` | text | no | | | | `status` | text | no | 'pending\_review'::text | | | `rejection_reason` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for encounter generation | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `reviewed_by` | uuid | yes | | | | `reviewed_at` | timestamp with time zone | yes | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `group_session_id` → `cl_group_sessions(id)` * `organization_id` → `pf_organizations(id)` * `reviewed_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_group_enc_gen_delete` (DELETE) * `cl_group_enc_gen_insert` (INSERT) * `cl_group_enc_gen_select` (SELECT) * `cl_group_enc_gen_update` (UPDATE) ### `cl_group_session_telehealth_metadata` CL-55: Telehealth metadata for virtual/hybrid group sessions. Stores PM-13 telehealth session UUID reference (no durable join URLs), contingency plan, recording state, technology failures, and backup site. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `session_id` | uuid | no | | | | `pm_telehealth_session_id` | uuid | yes | | PM-13 telehealth session UUID-only reference. Validated via platform integration layer; not a direct FK to preserve cross-core boundary. | | `vendor_session_ref` | text | yes | | | | `join_token_expires_at` | timestamp with time zone | yes | | | | `recording_enabled` | boolean | no | false | | | `recording_storage_uri` | text | yes | | Reserved for Phase 2 recording storage/playback. MUST remain NULL in Phase 1. | | `technology_failures` | jsonb | no | '\[]'::jsonb | | | `in_person_backup_site_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:** * `in_person_backup_site_id` → `pf_sites(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `session_id` → `cl_group_sessions(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_group_session_telehealth_metadata_delete` (DELETE) * `cl_group_session_telehealth_metadata_insert` (INSERT) * `cl_group_session_telehealth_metadata_select` (SELECT) * `cl_group_session_telehealth_metadata_update` (UPDATE) ### `cl_group_sessions` CL-14: Group therapy session records with facilitator, timing, and topic **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `session_date` | date | no | | | | `actual_start_time` | time without time zone | no | | Actual service start time per AHCCCS Policy 940; not rounded | | `actual_end_time` | time without time zone | no | | Actual service end time; when schedule\_id is set, scheduled times derive from PM-04 | | `group_type` | text | no | | | | `topic` | text | no | | | | `curriculum_id` | uuid | yes | | References external curriculum via Platform Integration Layer; no direct FK (E-4) | | `facilitator_id` | uuid | no | | | | `co_facilitator_id` | uuid | yes | | | | `location` | text | yes | | | | `program_id` | uuid | yes | | References external program via Platform Integration Layer; no direct FK (E-4) | | `schedule_id` | uuid | yes | | Links to PM-04 pm\_group\_schedule via Platform Integration Layer; no direct FK (E-2) | | `max_capacity` | integer | yes | | | | `status` | text | no | 'scheduled'::text | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"room\_number": "A-101"}) | | `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 | | `group_subtype` | text | yes | | CL-14-ENHANCEMENTS (EN-41): standard, family, or multi\_family subtype | | `modality` | text | no | 'in\_person'::text | | | `technology_contingency_plan` | text | yes | | | **Foreign keys:** * `co_facilitator_id` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `facilitator_id` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_group_sessions_delete` (DELETE) * `cl_group_sessions_insert` (INSERT) * `cl_group_sessions_select` (SELECT) * `cl_group_sessions_update` (UPDATE) ### `cl_inbasket_item_activity` CL-23: Append-only activity log for in-basket item lifecycle events. Exempt from custom\_fields (audit/log table). **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `item_id` | uuid | no | | | | `activity_type` | text | no | | | | `actor_id` | uuid | yes | | | | `activity_at` | timestamp with time zone | no | now() | | | `details` | jsonb | yes | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `actor_id` → `pf_profiles(id)` * `item_id` → `cl_inbasket_items(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_inbasket_activity_insert` (INSERT) * `cl_inbasket_activity_select` (SELECT) ### `cl_inbasket_items` CL-23: Unified clinical in-basket queue for provider action items (co-sign, lab review, refill, chart messages, CDS alerts, referral responses) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | yes | | | | `item_type` | text | no | | | | `priority` | text | no | | | | `classification` | text | no | | | | `status` | text | no | | | | `source_event` | text | yes | | | | `source_reference_id` | uuid | yes | | | | `assigned_to` | uuid | yes | | | | `due_at` | timestamp with time zone | yes | | | | `escalated_at` | timestamp with time zone | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `payload` | jsonb | yes | '\{}'::jsonb | Item-type-specific data payload (e.g., conversation\_id for chart\_message items) | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"external\_system\_id": "EXT-123"}) | | `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)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_inbasket_items_delete` (DELETE) * `cl_inbasket_items_insert` (INSERT) * `cl_inbasket_items_select` (SELECT) * `cl_inbasket_items_update` (UPDATE) ### `cl_incidents` CL-15: AZDHS incident tracking with mandated reporting timeframes and immutability after reporting **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | -------------------- | ---------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | yes | | | | `incident_type` | text | no | | | | `severity` | text | no | | | | `occurred_at` | timestamp with time zone | no | | | | `discovered_at` | timestamp with time zone | no | | | | `reported_to_state_at` | timestamp with time zone | yes | | | | `reporting_deadline` | timestamp with time zone | no | | | | `description` | text | no | | | | `investigation_status` | text | yes | 'not\_started'::text | | | `corrective_actions` | jsonb | yes | '\[]'::jsonb | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"internal\_case\_number": "INC-2026-001"}) | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_incidents_delete` (DELETE) * `cl_incidents_insert` (INSERT) * `cl_incidents_select` (SELECT) * `cl_incidents_update` (UPDATE) ### `cl_intake_assessments` CL-40: Clinical intake assessments with AHCCCS 18-element compliance tracking **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `appointment_id` | uuid | no | | | | `assessment_type` | text | yes | 'primary\_intake'::text | | | `chief_complaint` | text | no | | | | `history_of_present_illness` | text | yes | | | | `preliminary_diagnoses` | jsonb | yes | '\[]'::jsonb | Array of \{icd10\_code, description, status} objects | | `mental_health_history` | jsonb | yes | | | | `substance_use_history` | jsonb | yes | | | | `medical_history` | jsonb | yes | | | | `social_history` | jsonb | yes | | | | `sdoh_screening_completed` | boolean | yes | false | | | `sdoh_screening_id` | uuid | yes | | | | `peer_support_recommended` | boolean | yes | false | | | `peer_supporter_id` | uuid | yes | | | | `assessment_version` | integer | yes | 1 | | | `status` | text | no | 'draft'::text | | | `clinician_id` | uuid | yes | | | | `cosigning_clinician_id` | uuid | yes | | | | `signed_at` | timestamp with time zone | yes | | | | `cosigned_at` | timestamp with time zone | yes | | | | `ahcccs_18_element_complete` | boolean | yes | ((chief\_complaint IS NOT NULL) AND (mental\_health\_history IS NOT NULL) AND (substance\_use\_history IS NOT NULL) AND (medical\_history IS NOT NULL) AND (social\_history IS NOT NULL) AND (signed\_at IS NOT NULL)) | DEPRECATED: CL-02-EN-60. Use intake\_element\_complete instead. Retained during dual-read period. | | `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 | | | | `intake_element_complete` | boolean | yes | false | CL-02-EN-60: Trigger-maintained intake element completeness flag. Replaces ahcccs\_18\_element\_complete. | **Foreign keys:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `sdoh_screening_id` → `cl_sdoh_screenings(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_intake_assessments_delete` (DELETE) * `cl_intake_assessments_insert` (INSERT) * `cl_intake_assessments_select` (SELECT) * `cl_intake_assessments_update` (UPDATE) ### `cl_lethal_means_assessments` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `screening_id` | uuid | yes | | | | `encounter_id` | uuid | yes | | | | `firearm_access` | boolean | yes | | | | `firearm_details` | jsonb | yes | '\{}'::jsonb | | | `medication_stockpile` | boolean | yes | | | | `medication_details` | jsonb | yes | '\{}'::jsonb | | | `sharp_objects_access` | boolean | yes | | | | `other_means` | jsonb | yes | '\[]'::jsonb | | | `restriction_plan` | jsonb | yes | '\{}'::jsonb | | | `counseling_provided` | boolean | no | false | | | `counseling_notes` | text | yes | | | | `assessed_by` | uuid | no | | | | `assessed_at` | timestamp with time zone | no | now() | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | no | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `assessed_by` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `screening_id` → `cl_risk_screenings(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `rls_cl_lethal_means_assessments_delete` (DELETE) * `rls_cl_lethal_means_assessments_insert` (INSERT) * `rls_cl_lethal_means_assessments_select` (SELECT) * `rls_cl_lethal_means_assessments_update` (UPDATE) ### `cl_loc_assessments` CL-49: ASAM/LOCUS level-of-care assessments. Chart-scoped; Part 2 gated when is\_sud\_related=true. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | -------------------- | --------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `instrument_type` | text | no | | | | `instrument_version` | text | no | 'asam-3e-2013'::text | | | `dimension_scores` | jsonb | no | '\{}'::jsonb | Per-dimension scores keyed by dimension code (severity 0-4 + optional clinical\_notes). | | `total_score` | numeric(5,2) | yes | | | | `recommended_loc_code` | text | yes | | | | `final_loc_code` | text | yes | | | | `override_reason_code` | text | yes | | | | `override_rationale` | text | yes | | | | `supersedes_id` | uuid | yes | | Prior assessment this row supersedes (reassessment chain). Prior row remains immutable. | | `is_sud_related` | boolean | no | false | 42 CFR Part 2 flag - when true, RLS hides row unless chart has active SUD consent. | | `status` | text | no | 'draft'::text | | | `assessed_at` | timestamp with time zone | no | now() | | | `assessed_by` | uuid | yes | | | | `signed_at` | timestamp with time zone | yes | | | | `signed_by` | 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 | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `assessed_by` → `users(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `signed_by` → `users(id)` * `site_id` → `pf_sites(id)` * `supersedes_id` → `cl_loc_assessments(id)` * `updated_by` → `users(id)` **Policies:** * `cl_loc_assessments_delete` (DELETE) * `cl_loc_assessments_insert` (INSERT) * `cl_loc_assessments_select` (SELECT) * `cl_loc_assessments_update` (UPDATE) ### `cl_loinc_codes` CL-09: LOINC code reference (public read-only, no org scoping). Subset for behavioral health labs. **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------- | ------- | | `loinc_code` | text | no | | | | `component` | text | no | | | | `property` | text | yes | | | | `time_aspect` | text | yes | | | | `system_type` | text | yes | | | | `scale_type` | text | yes | | | | `method_type` | text | yes | | | | `class_name` | text | yes | | | | `long_common_name` | text | yes | | | | `short_name` | text | yes | | | | `specimen_type` | text | yes | | | | `is_common_bh` | boolean | no | false | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Policies:** * `cl_loinc_codes_select_authenticated` (SELECT) ### `cl_marketplace_bundle_items` CL-57: Items within a marketplace bundle. Each carries a typed payload that handlers materialize into per-org target tables. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `bundle_id` | uuid | no | | | | `item_type` | text | no | | | | `item_key` | text | no | | | | `payload` | jsonb | no | | | | `ordering` | integer | no | 0 | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `bundle_id` → `cl_marketplace_bundles(id)` **Policies:** * `cl_marketplace_bundle_items_delete_curator` (DELETE) * `cl_marketplace_bundle_items_insert_curator` (INSERT) * `cl_marketplace_bundle_items_read_all` (SELECT) * `cl_marketplace_bundle_items_update_curator` (UPDATE) ### `cl_marketplace_bundles` CL-57: Platform-curated clinical content bundles. Global table (no org\_id). RLS: SELECT for all authenticated; writes restricted to marketplace curators. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `bundle_key` | text | no | | | | `version` | integer | no | | | | `name` | text | no | | | | `description` | text | yes | | | | `accreditation_tags` | text\[] | no | '\{}'::text\[] | | | `clinical_area_tags` | text\[] | no | '\{}'::text\[] | | | `jurisdiction_scope` | jsonb | no | '\{}'::jsonb | | | `published_at` | timestamp with time zone | no | now() | | | `published_by` | uuid | yes | | | | `is_deprecated` | boolean | no | false | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `published_by` → `pf_profiles(id)` **Policies:** * `marketplace_bundles_delete_curator` (DELETE) * `marketplace_bundles_insert_curator` (INSERT) * `marketplace_bundles_read_all` (SELECT) * `marketplace_bundles_update_curator` (UPDATE) ### `cl_marketplace_imports` CL-57: Per-org marketplace bundle import history. Supports rollback via standardized snapshot envelope. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | -------------------- | ----------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `bundle_id` | uuid | no | | | | `bundle_key` | text | no | | | | `bundle_version` | integer | no | | | | `imported_at` | timestamp with time zone | no | now() | | | `imported_by` | uuid | yes | | | | `imported_item_count` | integer | no | 0 | | | `rollback_snapshot` | jsonb | yes | | Standardized rollback envelope (snapshot\_version, handler\_results\[]). Enforced at app layer. | | `status` | text | no | 'in\_progress'::text | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `bundle_id` → `cl_marketplace_bundles(id)` * `imported_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_marketplace_imports_delete_org` (DELETE) * `cl_marketplace_imports_insert_org` (INSERT) * `cl_marketplace_imports_select_org` (SELECT) * `cl_marketplace_imports_update_org` (UPDATE) ### `cl_medication_adherence` CL-05 EN-17: Medication adherence records for PDC calculation and peer support tracking. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ---------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `medication_id` | uuid | no | | | | `type` | text | no | 'self\_reported'::text | | | `date` | date | no | CURRENT\_DATE | | | `value` | text | yes | | | | `notes` | text | yes | | | | `adherence_barrier` | text | yes | | ICD-10 Z-code (Z55-Z65) identifying SDOH barrier to adherence. | | `sdoh_screening_id` | uuid | yes | | | | `hcpcs_code` | text | yes | | | | `peer_support_reference` | uuid | yes | | UUID referencing cl\_peer\_encounters; no FK constraint (table not in types.ts). | | `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. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `medication_id` → `cl_medications(id)` * `organization_id` → `pf_organizations(id)` * `sdoh_screening_id` → `cl_sdoh_screenings(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_medication_adherence_delete` (DELETE) * `cl_medication_adherence_insert` (INSERT) * `cl_medication_adherence_select` (SELECT) * `cl_medication_adherence_update` (UPDATE) ### `cl_medication_education` CL-05 EN-19: Medication education records with cultural responsiveness fields. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | --------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `medication_id` | uuid | yes | | | | `topic` | text | no | | | | `educated_at` | timestamp with time zone | no | now() | | | `educated_by` | uuid | yes | | | | `educated_by_role` | text | yes | | Role of educator (rn, pharmacist, peer\_specialist, etc). | | `acknowledgment` | text | yes | | | | `language_preference` | text | yes | | | | `interpreter_used` | boolean | yes | false | | | `interpreter_notes` | text | yes | | | | `peer_support_reference` | uuid | yes | | UUID referencing cl\_peer\_encounters; no FK constraint. | | `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. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `educated_by` → `pf_profiles(id)` * `medication_id` → `cl_medications(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_medication_education_delete` (DELETE) * `cl_medication_education_insert` (INSERT) * `cl_medication_education_select` (SELECT) * `cl_medication_education_update` (UPDATE) ### `cl_medication_reconciliations` CL-05: Medication reconciliation records for transitions of care (admission, transfer, discharge, routine). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `trigger_event` | text | no | | | | `status` | text | no | 'pending'::text | | | `reconciled_by` | uuid | yes | | | | `reconciled_at` | timestamp with time zone | yes | | | | `patient_reported_medications` | jsonb | yes | | JSON snapshot of medications reported by the patient at time of reconciliation. | | `discrepancies` | jsonb | yes | | JSON array of identified discrepancies between patient-reported and chart medications. | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific reconciliation 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, non-NULL = soft deleted. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `reconciled_by` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_med_recon_delete` (DELETE) * `cl_med_recon_insert` (INSERT) * `cl_med_recon_select` (SELECT) * `cl_med_recon_update` (UPDATE) ### `cl_medications` CL-05: Patient medication list; soft-delete via deleted\_at; controlled substances require DEA audit trail. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `drug_name` | text | no | | | | `generic_name` | text | yes | | | | `brand_name` | text | yes | | | | `ndc_code` | text | yes | | | | `rxcui` | text | yes | | | | `strength` | text | yes | | | | `strength_unit` | text | yes | | | | `dose_amount` | numeric | yes | | | | `dose_unit` | text | yes | | | | `route` | text | yes | | | | `frequency` | text | yes | | | | `instructions` | text | yes | | | | `status` | text | no | 'active'::text | | | `start_date` | date | yes | | | | `end_date` | date | yes | | | | `discontinued_reason` | text | yes | | | | `discontinued_by` | uuid | yes | | | | `discontinued_at` | timestamp with time zone | yes | | | | `prescribed_by` | uuid | yes | | | | `prescribed_at` | timestamp with time zone | yes | | | | `prescription_id` | uuid | yes | | | | `source` | text | yes | | | | `is_controlled_substance` | boolean | no | false | | | `dea_schedule` | text | yes | | | | `monitoring_protocol` | jsonb | yes | | | | `last_refill_date` | date | yes | | | | `refills_remaining` | integer | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"formulary\_id": "F-123", "pharmacy\_notes": "..."}) | | `entered_in_error` | boolean | no | false | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active, non-NULL = soft deleted. | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `discontinued_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `prescribed_by` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_medications_delete` (DELETE) * `cl_medications_insert` (INSERT) * `cl_medications_select` (SELECT) * `cl_medications_update` (UPDATE) ### `cl_metabolic_monitoring_events` CL-22: Metabolic monitoring events for antipsychotic/MAT regimens **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `monitoring_type` | text | no | | | | `due_by` | timestamp with time zone | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `status` | text | no | | | | `result_summary` | jsonb | no | '\{}'::jsonb | Structured results for the monitoring event (e.g., lab values, BMI trend) | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata; do not store structured PHI; constitution §5.2.1 | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_metabolic_monitoring_events_delete` (DELETE) * `cl_metabolic_monitoring_events_insert` (INSERT) * `cl_metabolic_monitoring_events_select` (SELECT) * `cl_metabolic_monitoring_events_update` (UPDATE) ### `cl_module_settings` CL-01: Per-organization clinical module configuration. One row per org. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------------------------------------- | ------------------------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `break_glass_duration_minutes` | integer | no | 60 | Duration in minutes that break-glass emergency access remains valid (15-480). Default 60. | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `cosignature_deadline_days` | integer | yes | 14 | | | `identical_time_flag_threshold` | integer | yes | 3 | | | `low_risk_rescreen_interval_days` | integer | no | 30 | CL-07: Days until next screening for low-risk patients (1-365 day range). Key: cl.screening.low\_risk\_rescreen\_interval\_days. Default: 30 days. | | `assessment_completion_hours` | integer | no | 48 | CL-04: Hours after encounter in which an assessment/note must be completed and locked (1-168). Default 48. Key: cl.notes.assessment\_completion\_hours. | | `treatment_plan_review_days` | integer | no | 90 | CL-04: Days between required treatment plan reviews (1-365). Default 90. Key: cl.treatment\_plan.review\_days. | | `cosign_required_days` | integer | no | 5 | CL-04: Org workflow deadline (days) for supervisor co-signature on BHP/trainee notes (1-90). Distinct from cosignature\_deadline\_days (AZDHS hard limit). Default 5. Key: cl.notes.cosign\_required\_days. | | `break_glass_review_hours` | integer | no | 24 | CL-01: Hours within which a supervisor must review a break-glass access event (1-168). Default 24. Key: cl.break\_glass.review\_hours. | | `part2_consent_required` | boolean | no | true | CL-01: When true, 42 CFR Part 2 SUD consent must be on file before disclosing applicable clinical records. Default true. Key: cl.privacy.part2\_consent\_required. | | `suicide_screening_min_age` | integer | no | 12 | CL-07: Minimum patient age (years) for suicide/self-harm screening (5-18). Default 12 (PHQ-A threshold per AZDHS guidance). Key: cl.screening.suicide\_screening\_min\_age. | | `assessment_expiry_alert_days` | integer | no | 30 | | | `outcome_small_n_suppression_threshold` | integer | no | 5 | Minimum denominator for program outcome display. Outcomes with denominator below this are suppressed for de-identification. | | `pending_result_expiry_days` | integer | yes | 30 | Number of days before pending (unsolicited) lab results automatically expire. Default: 30. | | `default_report_format` | text | yes | 'pdf'::text | Default export format for report runs (pdf, csv, excel) | | `incident_deadline_alert_minutes` | integer | yes | 15 | Minutes before AZDHS reporting\_deadline to send alert | | `report_generation_timeout_seconds` | integer | yes | 300 | Max seconds for report generation before timeout | | `use_configurable_wizards` | boolean | yes | false | PF-41: Use configurable wizard templates when true; otherwise use hardcoded wizards. | | `consent_expiration_reminder_days` | integer\[] | no | '\{30,14,7}'::integer\[] | CL-11 EN-33: Array of day thresholds for consent expiration reminders (e.g., \{30, 14, 7} means notify at 30, 14, and 7 days before expiry). | | `care_gap_phq9_days` | integer | yes | 90 | CL-35: Days since last PHQ-9 before gap opens | | `care_gap_gad7_days` | integer | yes | 90 | CL-35: Days since last GAD-7 before gap opens | | `care_gap_plan_review_days` | integer | yes | 90 | CL-35: Days past review\_due\_date before gap opens | | `care_gap_no_contact_days` | integer | yes | 30 | CL-35: Days since last signed note before gap opens | | `care_gap_phq9_priority` | text | yes | 'urgent'::text | | | `care_gap_gad7_priority` | text | yes | 'urgent'::text | | | `care_gap_engine_enabled` | boolean | yes | false | CL-35: Enable/disable care gap rules engine | | `cl_ai_documentation_enabled` | boolean | no | false | Org-level opt-in for AI-assisted clinical documentation (CL-36). BAA required before setting true. | | `cl_ai_ambient_vendor` | text | yes | | Selected ambient voice vendor slug (e.g. nabla, nuance\_dax). Null = structured-data only. Phase 3. | | `cl_ai_data_residency` | text | no | 'us'::text | Data residency region for AI PHI processing. us = US-only (Phase 1/2 default). | | `cl_ai_policy940_enforcement` | text | no | 'block'::text | AHCCCS Policy 940 enforcement mode: warn \| block \| draft-warn-final-block. Default block for new orgs. | | `break_glass_sla_hours` | integer | yes | 24 | | | `default_audit_date_range_days` | integer | yes | 7 | | | `anomaly_high_volume_threshold` | integer | yes | 50 | | | `anomaly_business_hours_start` | time without time zone | yes | '08:00:00'::time without time zone | | | `anomaly_business_hours_end` | time without time zone | yes | '18:00:00'::time without time zone | | | `anomaly_same_patient_repeat_threshold` | integer | yes | 10 | | | `tefca_match_confidence_threshold` | numeric(3,2) | yes | 0.95 | | | `tefca_max_retry_attempts` | integer | yes | 3 | | | `tefca_retry_backoff_base_ms` | integer | yes | 1000 | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `max_pathway_milestones` | integer | yes | 50 | Maximum number of milestones allowed per pathway template | | `pathway_overdue_alert_hours` | integer | yes | 24 | Hours after milestone expected completion before overdue alert is generated | | `standing_order_default_expiry_days` | integer | yes | 90 | CL-44: Default expiration period in days for clinical standing order protocols. | | `order_set_max_items` | integer | yes | 50 | CL-44: Maximum number of items allowed in a single order set template. | | `order_sets_enabled` | boolean | no | true | CL-44: Feature flag controlling visibility of Order Set / Standing Order navigation and activation entry points. | | `cohort_recompute_schedule` | text | no | 'daily'::text | CL-54 / spec NC-3: Cron cadence for cl-cohort-recompute edge function. Default daily 02:00 UTC. | | `pop_health_assessment_expiry_days` | integer | yes | 365 | Days before a comprehensive assessment expires and triggers a care gap | | `pop_health_risk_refresh_hours` | integer | yes | 24 | Minimum hours between automated risk stratification refreshes | | `pop_health_risk_weights` | jsonb | yes | '\{"moud": 0.10, "safety": 0.40, "outcome": 0.30, "metabolic": 0.20}'::jsonb | Component weights for composite risk score (must sum to 1.0): safety, outcome, metabolic, moud | | `pop_health_risk_tier_thresholds` | jsonb | yes | '\{"low": 0, "high": 50, "medium": 25, "critical": 75}'::jsonb | Numeric thresholds for risk tier assignment: low, medium, high, critical | | `cda_generation_enabled` | boolean | no | false | CL-48 feature flag: when true, CDA generation UI is visible to users with cl.cda.\* permissions. Defaults to false (opt-in per org). | | `incident_deadline_track` | text | no | 'azdhs'::text | CL-15 Phase 2: which regulatory clock to apply to incident reporting deadlines. 'azdhs' (default, preserves Phase 1 behavior), 'ahcccs' (AHCCCS Policy 961 business-day clock), or 'dual' (whichever is earlier). PF-96 migration note: Arizona-specific; future jurisdictions should resolve via pf\_resolve\_jurisdiction\_profile(). | | `incident_business_day_config` | jsonb | no | '\{"excludeWeekends": true}'::jsonb | CL-15 Phase 2: JSONB config for business-day math. Example: \{"excludeWeekends": true, "holidayCalendar": "us\_federal"}. Runtime shape validation lives in application-layer Zod (no immutable JSON CHECK). holidayCalendar deferred per CL-15-CONTEXT.md; initial release is weekday-only. | | `incident_sentinel_options` | jsonb | no | '\{}'::jsonb | CL-15 Phase 2: JSONB config for sentinel-event handling. Example: \{"autoEscalate": true, "notifyRoles": \["org\_admin", "compliance\_officer"]}. Runtime shape validation lives in application-layer Zod. | | `portal_qm_config` | jsonb | no | jsonb\_build\_object('instruments', jsonb\_build\_array('phq9', 'gad7', 'pcl5', 'audit', 'dast10', 'whoqol\_bref', 'brs', 'promis\_global'), 'measure\_types', jsonb\_build\_array('symptom', 'recovery', 'wellness', 'hedis', 'functional'), 'sud\_instruments\_require\_consent', true) | CL-15 Phase 3 (T8): Patient portal quality-measure display config. Shape: \{instruments: string\[], measure\_types: string\[], sud\_instruments\_require\_consent: boolean}. SUD instruments (audit, dast10, and others tagged in application layer SUD\_INSTRUMENTS) are filtered out when sud\_instruments\_require\_consent=true AND cl\_check\_sud\_consent(chart\_id, 'outcome\_measure', auth.uid()) returns false. Runtime shape validation lives in application-layer Zod (no immutable JSON CHECK). | | `weno_sync_interval_minutes` | integer | no | 15 | CL-06-EN-23: NewRx sync polling interval in minutes (5–120). Vendor: WENO EZ. | | `weno_max_query_window_days` | integer | no | 7 | CL-06-EN-23: Maximum NewRx query window in days (1–14, vendor cap 7). Vendor: WENO EZ. | | `weno_directory_delta_cron` | text | no | '0 2 \* \* \*'::text | CL-06-EN-23: Cron expression for daily pharmacy directory delta fetch. | | `weno_directory_full_refresh_day` | integer | no | 0 | CL-06-EN-23: Day-of-week for full directory refresh (0=Sunday … 6=Saturday). | | `telehealth_documentation_enabled` | boolean | no | false | CL-24 feature flag — when false, all CL-24 hooks short-circuit and routes redirect. | | `telehealth_consent_validity_days` | integer | no | 365 | CL-24: telehealth consent expires\_at = consented\_at + this many days. | | `telehealth_block_session_without_consent` | boolean | no | true | CL-24: when true, useStartTelehealthSession throws if consent invalid. | | `telehealth_block_session_without_safety_checklist` | boolean | no | true | CL-24: when true, useStartTelehealthSession throws if safety\_checklist\_completed\_at is NULL. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_module_settings_delete` (DELETE) * `cl_module_settings_insert` (INSERT) * `cl_module_settings_select` (SELECT) * `cl_module_settings_update` (UPDATE) ### `cl_moud_enrollments` CL-21: MAT/MOUD enrollment and treatment phase tracking **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `enrollment_status` | text | no | | | | `treatment_phase` | text | no | | | | `primary_medication` | text | no | | | | `started_on` | date | no | | | | `ended_on` | date | yes | | | | `consent_id` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata; do not store structured PHI; constitution §5.2.1 | | `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:** * `chart_id` → `cl_patient_charts(id)` * `consent_id` → `cl_consents(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_moud_enrollments_delete` (DELETE) * `cl_moud_enrollments_insert` (INSERT) * `cl_moud_enrollments_select` (SELECT) * `cl_moud_enrollments_update` (UPDATE) ### `cl_moud_medication_events` CL-21: MOUD medication events (ordered, prescribed, administered, dispensed, held, missed) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `enrollment_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `medication_name` | text | no | | | | `event_type` | text | no | | | | `dose_value` | numeric | yes | | | | `dose_unit` | text | yes | | | | `route` | text | yes | | | | `occurred_at` | timestamp with time zone | no | | | | `adherence_status` | text | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata; do not store structured PHI; constitution §5.2.1 | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `enrollment_id` → `cl_moud_enrollments(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_moud_medication_events_delete` (DELETE) * `cl_moud_medication_events_insert` (INSERT) * `cl_moud_medication_events_select` (SELECT) * `cl_moud_medication_events_update` (UPDATE) ### `cl_moud_monitoring_events` CL-21: Monitoring protocol events (UDS, hepatic, etc.) with due and completion **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `enrollment_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `monitoring_type` | text | no | | | | `due_by` | timestamp with time zone | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `result_status` | text | yes | | | | `result_summary` | jsonb | yes | '\{}'::jsonb | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata; do not store structured PHI; constitution §5.2.1 | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `enrollment_id` → `cl_moud_enrollments(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_moud_monitoring_events_delete` (DELETE) * `cl_moud_monitoring_events_insert` (INSERT) * `cl_moud_monitoring_events_select` (SELECT) * `cl_moud_monitoring_events_update` (UPDATE) ### `cl_multi_party_encounters` CL-55: Non-group multi-party encounters (mobile crisis, family therapy, interpreter-assisted, care team meetings). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | -------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `primary_chart_id` | uuid | no | | | | `encounter_type` | text | no | | | | `modality` | text | no | | | | `started_at` | timestamp with time zone | no | | | | `ended_at` | timestamp with time zone | yes | | | | `status` | text | no | 'in\_progress'::text | | | `encounter_notes` | text | 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)` * `organization_id` → `pf_organizations(id)` * `primary_chart_id` → `cl_patient_charts(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_multi_party_encounters_delete` (DELETE) * `cl_multi_party_encounters_insert` (INSERT) * `cl_multi_party_encounters_select` (SELECT) * `cl_multi_party_encounters_update` (UPDATE) ### `cl_multi_party_participants` CL-55: Participants in multi-party encounters with role attribution and per-participant recording consent. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `encounter_id` | uuid | no | | | | `participant_role` | text | no | | | | `chart_id` | uuid | yes | | | | `staff_user_id` | uuid | yes | | | | `external_participant_name` | text | yes | | | | `joined_at` | timestamp with time zone | yes | | | | `left_at` | timestamp with time zone | yes | | | | `recording_consent_captured_at` | timestamp with time zone | yes | | | | `recording_consent_modality` | text | 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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `encounter_id` → `cl_multi_party_encounters(id)` * `organization_id` → `pf_organizations(id)` * `staff_user_id` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_multi_party_participants_delete` (DELETE) * `cl_multi_party_participants_insert` (INSERT) * `cl_multi_party_participants_select` (SELECT) * `cl_multi_party_participants_update` (UPDATE) ### `cl_note_940_override_audit` CL-04-EN-66: INSERT-only audit log for Policy 940 validation overrides. No UPDATE or DELETE permitted. Retention: minimum 6 years per HIPAA 45 CFR 164.316(b)(2)(i). **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `note_id` | uuid | no | | | | `override_by` | uuid | no | | | | `override_reason` | text | no | | | | `override_at` | timestamp with time zone | no | now() | | | `validation_result_snapshot` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | no | | | **Foreign keys:** * `created_by` → `users(id)` * `note_id` → `cl_progress_notes(id)` * `organization_id` → `pf_organizations(id)` * `override_by` → `users(id)` **Policies:** * `cl_note_940_audit_insert_org` (INSERT) * `cl_note_940_audit_select_org` (SELECT) ### `cl_note_templates` CL-04: Service-line note templates (outpatient, residential, iop\_php). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `template_name` | text | no | | | | `note_type` | text | no | | | | `service_line` | text | yes | | | | `template_structure` | jsonb | no | '\{}'::jsonb | | | `required_fields` | jsonb | no | '\[]'::jsonb | | | `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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_note_templates_delete` (DELETE) * `cl_note_templates_insert` (INSERT) * `cl_note_templates_select` (SELECT) * `cl_note_templates_update` (UPDATE) ### `cl_note_treatment_goals` CL-04: Junction between progress notes and treatment plan goals. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `note_id` | uuid | no | | | | `treatment_goal_id` | uuid | no | | | | `organization_id` | uuid | no | | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `note_id` → `cl_progress_notes(id)` * `organization_id` → `pf_organizations(id)` * `treatment_goal_id` → `cl_treatment_goals(id)` **Policies:** * `cl_note_treatment_goals_delete` (DELETE) * `cl_note_treatment_goals_insert` (INSERT) * `cl_note_treatment_goals_select` (SELECT) * `cl_note_treatment_goals_update` (UPDATE) ### `cl_notification_delivery_attempts` CL-56: Per-alert delivery attempt log (dispatch, retry, escalation, acknowledge, error). PHI prohibited in metadata/error\_message. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `event_id` | uuid | no | | | | `attempt_number` | integer | no | 1 | | | `attempt_type` | text | no | | | | `channel` | text | yes | | | | `recipient_user_id` | uuid | yes | | | | `recipient_role_key` | text | yes | | | | `status` | text | no | | | | `error_code` | text | yes | | | | `error_message` | text | yes | | | | `duration_ms` | integer | yes | | | | `metadata` | jsonb | no | '\{}'::jsonb | | | `occurred_at` | timestamp with time zone | no | now() | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `event_id` → `cl_notification_events(id)` * `organization_id` → `pf_organizations(id)` * `recipient_user_id` → `pf_profiles(id)` **Policies:** * `cl_notif_attempts_insert` (INSERT) * `cl_notif_attempts_select` (SELECT) ### `cl_notification_escalation_chains` CL-56: Ordered escalation steps per severity tier; recipient\_role\_key + delay\_seconds. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `severity` | text | no | | | | `step_order` | integer | no | | | | `recipient_role_key` | text | no | | | | `delay_seconds` | 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:** * `cl_notification_escalation_chains_delete` (DELETE) * `cl_notification_escalation_chains_insert` (INSERT) * `cl_notification_escalation_chains_select` (SELECT) * `cl_notification_escalation_chains_update` (UPDATE) ### `cl_notification_events` CL-56: Append-only fact table of dispatched clinical notifications with ack/escalation/SLA tracking. PHI prohibited in payload (chk\_cl\_notification\_no\_phi). **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `signal_type` | text | no | | | | `signal_source` | text | no | | | | `source_record_id` | uuid | yes | | | | `chart_id` | uuid | yes | | | | `severity` | text | no | | | | `policy_id` | uuid | yes | | | | `dispatched_at` | timestamp with time zone | no | now() | | | `recipient_user_id` | uuid | yes | | | | `recipient_role_key` | text | yes | | | | `escalation_step` | integer | no | 0 | | | `acknowledged_at` | timestamp with time zone | yes | | | | `acknowledged_by` | uuid | yes | | | | `escalated_at` | timestamp with time zone | yes | | | | `escalation_reason` | text | yes | | | | `sla_deadline` | timestamp with time zone | no | | | | `sla_breached_at` | timestamp with time zone | yes | | | | `payload` | jsonb | no | '\{}'::jsonb | IDs and severity context only. Disallowed keys: patient\_name, diagnosis, lab\_value, narrative. | | `custom_fields` | jsonb | no | '\{}'::jsonb | | **Foreign keys:** * `acknowledged_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `policy_id` → `cl_notification_policies(id)` * `recipient_user_id` → `pf_profiles(id)` **Policies:** * `cl_notification_events_delete` (DELETE) * `cl_notification_events_insert` (INSERT) * `cl_notification_events_select` (SELECT) ### `cl_notification_policies` CL-56: Per-organization rules mapping clinical signal types to severity, SLA, and dedup window. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `signal_type` | text | no | | | | `severity_rules` | jsonb | no | | | | `sla_seconds` | integer | no | | | | `dedup_window_seconds` | integer | no | 300 | | | `is_active` | boolean | no | 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 | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_notification_policies_delete` (DELETE) * `cl_notification_policies_insert` (INSERT) * `cl_notification_policies_select` (SELECT) * `cl_notification_policies_update` (UPDATE) ### `cl_order_results` CL-09: Individual result values for lab/diagnostic orders **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `order_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `result_code` | text | yes | | | | `result_name` | text | yes | | | | `result_value` | text | yes | | | | `result_value_numeric` | numeric | yes | | | | `result_type` | text | no | 'text'::text | | | `result_units` | text | yes | | | | `reference_range` | text | yes | | | | `abnormal_flag` | text | no | 'normal'::text | | | `result_status` | text | no | 'final'::text | | | `source` | text | no | 'manual'::text | Result origin: manual (clinician entry) or interface (HL7v2/FHIR ingestion) | | `performed_at` | timestamp with time zone | yes | | | | `reviewed_by` | uuid | yes | | | | `reviewed_at` | timestamp with time zone | yes | | | | `review_notes` | text | yes | | | | `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. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `reviewed_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` * `organization_id, order_id` → `cl_orders(organization_id, id)` **Policies:** * `cl_order_results_delete` (DELETE) * `cl_order_results_insert` (INSERT) * `cl_order_results_select` (SELECT) * `cl_order_results_update` (UPDATE) ### `cl_order_set_activations` CL-44: Per-patient activation log of an order set template; created orders captured in created\_orders JSONB. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `template_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `patient_id` | uuid | no | | Cross-core reference to pm\_patients.id. No DB FK per ADR-002; validated at application layer. | | `activated_by` | uuid | no | | | | `activated_at` | timestamp with time zone | no | now() | | | `selected_items` | jsonb | no | | | | `modified_params` | jsonb | no | '\{}'::jsonb | | | `created_orders` | jsonb | no | '\[]'::jsonb | | | `notes` | text | 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:** * `activated_by` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `template_id` → `cl_order_set_templates(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_order_set_activations_insert` (INSERT) * `cl_order_set_activations_select` (SELECT) ### `cl_order_set_templates` CL-44: Order set template definitions (medications + labs + assessments + monitoring) with versioning and approval workflow. **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 | | | | `items` | jsonb | no | '\[]'::jsonb | | | `status` | text | no | 'draft'::text | | | `version` | integer | no | 1 | | | `parent_version_id` | uuid | yes | | | | `approved_by` | uuid | yes | | | | `approved_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:** * `approved_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `parent_version_id` → `cl_order_set_templates(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_order_set_templates_delete` (DELETE) * `cl_order_set_templates_insert` (INSERT) * `cl_order_set_templates_select` (SELECT) * `cl_order_set_templates_update` (UPDATE) ### `cl_order_templates` CL-09: Reusable order templates (e.g., CMP panel, UDS panel) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `template_name` | text | no | | | | `template_type` | text | no | 'lab'::text | | | `description` | text | yes | | | | `order_items` | jsonb | no | '\[]'::jsonb | Array of order item definitions: \[\{"loinc\_code": "...", "description": "...", "specimen\_type": "..."}] | | `is_active` | boolean | no | true | | | `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. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_order_templates_delete` (DELETE) * `cl_order_templates_insert` (INSERT) * `cl_order_templates_select` (SELECT) * `cl_order_templates_update` (UPDATE) ### `cl_orders` CL-09: Lab and diagnostic orders linked to patient charts **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `ordering_provider_id` | uuid | no | | | | `reference_lab_id` | uuid | yes | | | | `site_id` | uuid | yes | | | | `order_type` | text | no | 'lab'::text | | | `status` | text | no | 'draft'::text | | | `priority` | text | no | 'routine'::text | | | `loinc_code` | text | yes | | | | `order_description` | text | yes | | | | `specimen_type` | text | yes | | | | `clinical_notes` | text | yes | | | | `external_order_id` | text | yes | | Placer/filler order number for matching incoming HL7v2/FHIR results | | `ordered_at` | timestamp with time zone | yes | | | | `collected_at` | timestamp with time zone | yes | | | | `resulted_at` | timestamp with time zone | yes | | | | `cancelled_at` | timestamp with time zone | yes | | | | `cancelled_reason` | text | yes | | | | `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. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `loinc_code` → `cl_loinc_codes(loinc_code)` * `ordering_provider_id` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `reference_lab_id` → `cl_reference_labs(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_orders_delete` (DELETE) * `cl_orders_insert` (INSERT) * `cl_orders_select` (SELECT) * `cl_orders_update` (UPDATE) ### `cl_outcome_measures` CL-10: Patient-reported outcome measures (PROMs). One row per instrument administration per patient chart. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------------- | ------------------------ | -------- | ------------------- | ------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `measure_type` | text | no | | | | `instrument` | text | no | | | | `version` | text | yes | | | | `score` | numeric(10,2) | no | | | | `subscale_scores` | jsonb | yes | '\{}'::jsonb | | | `responses` | jsonb | yes | '\{}'::jsonb | | | `severity_level` | text | yes | | | | `clinically_significant_change` | boolean | yes | | | | `reliable_change_index` | numeric(8,4) | yes | | | | `administered_at` | timestamp with time zone | no | now() | | | `administered_by` | uuid | yes | | | | `administration_method` | text | yes | | | | `notes` | text | yes | | | | `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. | **Foreign keys:** * `administered_by` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_outcome_measures_delete` (DELETE) * `cl_outcome_measures_insert` (INSERT) * `cl_outcome_measures_select` (SELECT) * `cl_outcome_measures_update` (UPDATE) ### `cl_pathway_definitions` CL-08 EN-28: Clinical pathway templates **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `pathway_name` | text | no | | | | `description` | text | yes | | | | `condition_type` | text | no | | | | `steps` | jsonb | no | '\[]'::jsonb | | | `is_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 | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_pathway_definitions_delete` (DELETE) * `cl_pathway_definitions_insert` (INSERT) * `cl_pathway_definitions_select` (SELECT) * `cl_pathway_definitions_update` (UPDATE) ### `cl_pathway_milestones` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_pathway_id` | uuid | no | | | | `milestone_template_id` | text | no | | | | `name` | text | no | | | | `sequence_order` | integer | no | | | | `expected_completion_date` | timestamp with time zone | yes | | | | `actual_completion_date` | timestamp with time zone | yes | | | | `completed_by` | uuid | yes | | | | `status` | text | no | 'pending'::text | | | `linked_assessment_ids` | uuid\[] | yes | '\{}'::uuid\[] | | | `notes` | text | 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:** * `completed_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_pathway_id` → `cl_patient_pathways(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_pathway_milestones_delete` (DELETE) * `cl_pathway_milestones_insert` (INSERT) * `cl_pathway_milestones_select` (SELECT) * `cl_pathway_milestones_update` (UPDATE) ### `cl_pathway_progress` CL-08 EN-28: Patient progress through pathways **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `pathway_id` | uuid | no | | | | `current_step` | integer | no | 1 | | | `status` | text | no | 'active'::text | | | `started_at` | timestamp with time zone | no | now() | | | `completed_at` | timestamp with time zone | yes | | | | `step_history` | jsonb | no | '\[]'::jsonb | | | `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)` * `pathway_id` → `cl_pathway_definitions(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_pathway_progress_delete` (DELETE) * `cl_pathway_progress_insert` (INSERT) * `cl_pathway_progress_select` (SELECT) * `cl_pathway_progress_update` (UPDATE) ### `cl_pathway_templates` **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 | | | | `clinical_program` | text | yes | | | | `version` | integer | no | 1 | | | `parent_template_id` | uuid | yes | | | | `status` | text | no | 'draft'::text | | | `expected_total_duration_days` | integer | yes | | | | `milestones` | jsonb | no | '\[]'::jsonb | | | `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)` * `parent_template_id` → `cl_pathway_templates(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_pathway_templates_delete` (DELETE) * `cl_pathway_templates_insert` (INSERT) * `cl_pathway_templates_select` (SELECT) * `cl_pathway_templates_update` (UPDATE) ### `cl_pathway_variances` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_pathway_id` | uuid | no | | | | `milestone_id` | uuid | no | | | | `reason_code` | text | no | | | | `rationale` | text | yes | | | | `documented_by` | uuid | no | | | | `documented_at` | timestamp with time zone | no | now() | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `documented_by` → `pf_profiles(id)` * `milestone_id` → `cl_pathway_milestones(id)` * `organization_id` → `pf_organizations(id)` * `patient_pathway_id` → `cl_patient_pathways(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_pathway_variances_delete` (DELETE) * `cl_pathway_variances_insert` (INSERT) * `cl_pathway_variances_select` (SELECT) * `cl_pathway_variances_update` (UPDATE) ### `cl_patient_charts` CL-01: Patient clinical chart shell. One chart per patient per organization. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `chart_number` | text | no | | | | `status` | text | no | 'active'::text | | | `primary_site_id` | uuid | yes | | | | `flags` | jsonb | no | '\{}'::jsonb | Clinical alert flags (e.g., \{"fall\_risk": true, "suicide\_risk": "high"}) | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific clinical 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 | | | | `current_risk_level` | text | yes | | CL-07 Errata E-7: Denormalized current suicide risk level from latest cl\_risk\_screenings row. Updated by trg\_cl\_risk\_screenings\_update\_chart\_risk on screening INSERT/UPDATE. NULL = never screened. | | `risk_last_screened_at` | timestamp with time zone | yes | | CL-07 Errata E-7: Timestamp of latest risk screening. Updated by trigger on cl\_risk\_screenings INSERT/UPDATE. | | `chart_summary_metadata` | jsonb | no | '\{}'::jsonb | AI summary readiness metadata for future AI-generated chart summary feature. Schema: \{"ai\_summary\_ready": false, "last\_summary\_at": null, "version": 0}. ai\_summary\_ready=true signals chart has sufficient structured clinical data for AI summarization. STORAGE PREP ONLY: do not store AI-generated summary text in this column in this phase. PHI AUDIT RULE: writers must NOT log cached summary content to pf\_audit\_logs; log only that an update occurred. | | `assigned_clinician_id` | uuid | yes | | CL-35: Primary assigned clinician for panel management | | `cod_indicator` | boolean | no | false | CL-31: Marks chart as Co-Occurring Disorder. | | `ai_drafting_opt_out` | boolean | no | false | CL-65 §Patient Rights: when true, the patient has opted out of AI-assisted note drafting; cl-ai-generate-draft denies generation (gate=patient\_opted\_out) and the clinician uses the non-AI workflow. | **Foreign keys:** * `assigned_clinician_id` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pf_patient_identities(id)` * `primary_site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_patient_charts_delete` (DELETE) * `cl_patient_charts_insert` (INSERT) * `cl_patient_charts_select` (SELECT) * `cl_patient_charts_update` (UPDATE) ### `cl_patient_cohort_definitions` CL-54: Immutable, append-only definition history per cohort. Each new edit creates a new version row; prior versions are frozen for snapshot reproducibility. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `cohort_id` | uuid | no | | | | `version` | integer | no | | | | `definition_type` | text | no | | | | `criteria` | jsonb | yes | | PF-32 ConditionGroup envelope: \{ version: 1, root: ConditionGroup }. Populated only for rule\_based. | | `manual_chart_ids` | uuid\[] | yes | | Explicit list of cl\_patient\_charts.id values. Populated only for manual. | | `query_template` | text | yes | | Reserved for future query\_based cohorts (Phase 2). | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `cohort_id` → `cl_patient_cohorts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_patient_cohort_definitions_insert` (INSERT) * `cl_patient_cohort_definitions_select` (SELECT) ### `cl_patient_cohort_memberships` CL-54: Append-only snapshot table. One row per (cohort, version, chart, as\_of\_date). Idempotent: recompute for the same key tuple is a no-op. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `cohort_id` | uuid | no | | | | `cohort_version` | integer | no | | | | `chart_id` | uuid | no | | | | `as_of_date` | date | no | | | | `recomputed_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `cohort_id` → `cl_patient_cohorts(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_patient_cohort_memberships_delete` (DELETE) * `cl_patient_cohort_memberships_insert` (INSERT) * `cl_patient_cohort_memberships_select` (SELECT) ### `cl_patient_cohorts` CL-54: Patient cohort registry. Each cohort is a versioned, named patient population definition (manual, rule-based, or query-based) used for HEDIS reporting, VBP attribution, and outreach campaigns. **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 | | | | `description` | text | yes | | | | `definition_type` | text | no | | How the cohort membership is computed: manual (explicit chart list), rule\_based (PF-32 condition tree), query\_based (deferred Phase 2). | | `current_version` | integer | no | 1 | Pointer to the latest immutable cl\_patient\_cohort\_definitions.version row for this cohort. | | `status` | text | no | 'draft'::text | Lifecycle state: draft (editable, no snapshots), active (snapshottable), archived (frozen). | | `custom_fields` | jsonb | no | '\{}'::jsonb | Org-specific metadata; see CL custom\_fields conventions. | | `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:** * `cl_patient_cohorts_delete` (DELETE) * `cl_patient_cohorts_insert` (INSERT) * `cl_patient_cohorts_select` (SELECT) * `cl_patient_cohorts_update` (UPDATE) ### `cl_patient_pathways` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | References pm\_patients.id (PM-owned). UUID-only, no FK per ADR-002. | | `template_id` | uuid | no | | | | `enrolled_at` | timestamp with time zone | no | now() | | | `enrolled_by` | uuid | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `withdrawn_at` | timestamp with time zone | yes | | | | `withdrawn_reason` | text | yes | | | | `status` | text | no | 'active'::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)` * `enrolled_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `template_id` → `cl_pathway_templates(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_patient_pathways_delete` (DELETE) * `cl_patient_pathways_insert` (INSERT) * `cl_patient_pathways_select` (SELECT) * `cl_patient_pathways_update` (UPDATE) ### `cl_patient_submissions` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `submission_type` | text | no | | | | `submitted_at` | timestamp with time zone | no | | | | `responses` | jsonb | no | | | | `score_result` | jsonb | yes | | | | `status` | text | no | | | | `reviewed_by` | uuid | yes | | | | `reviewed_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)` * `organization_id` → `pf_organizations(id)` * `reviewed_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_patient_submissions_clinician_select` (SELECT) * `cl_patient_submissions_clinician_update` (UPDATE) * `cl_patient_submissions_portal_insert` (INSERT) * `cl_patient_submissions_portal_select` (SELECT) ### `cl_pdmp_configuration` CL-17: Org-level PDMP gateway configuration. One row per organization. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `gateway_provider` | text | no | | | | `api_endpoint` | text | yes | | | | `credentials_vault_ref` | text | yes | | Reference to platform vault. Sensitive — never expose in UI or logs. | | `auto_query_enabled` | boolean | yes | true | | | `interstate_query_enabled` | boolean | yes | true | | | `delegate_access_enabled` | boolean | yes | true | | | `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:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_pdmp_configuration_delete` (DELETE) * `cl_pdmp_configuration_insert` (INSERT) * `cl_pdmp_configuration_select` (SELECT) * `cl_pdmp_configuration_update` (UPDATE) ### `cl_pdmp_queries` CL-17: PDMP query audit trail per A.R.S. § 36-2606. Immutable — no soft delete. PHI table. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `queried_by` | uuid | no | | | | `queried_on_behalf_of` | uuid | yes | | | | `query_datetime` | timestamp with time zone | no | now() | | | `patient_match_found` | boolean | yes | | | | `results_summary` | jsonb | yes | '\{}'::jsonb | Structured PDMP response (risk indicators, Rx counts). PHI — protected by RLS. | | `results_reviewed_by` | uuid | yes | | | | `results_reviewed_at` | timestamp with time zone | yes | | | | `prescriber_attestation` | boolean | yes | false | | | `prescription_id` | uuid | yes | | | | `gateway_transaction_id` | text | yes | | Vendor correlation ID for audit. Safe to log (non-PHI). | | `gateway_provider` | text | yes | | | | `interstate_query` | boolean | yes | false | | | `states_queried` | jsonb | yes | '\["AZ"]'::jsonb | | | `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 | | | | `status` | text | no | 'pending'::text | Query lifecycle: pending, completed, failed, manual, unavailable\_documented | | `query_result_source` | text | yes | | Source: gateway, manual\_portal, cached, not\_applicable | | `query_method` | text | no | 'electronic'::text | How initiated: electronic (EMR-triggered) or manual (user-initiated) | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `prescription_id` → `cl_prescriptions(id)` * `queried_by` → `pf_profiles(id)` * `queried_on_behalf_of` → `pf_profiles(id)` * `results_reviewed_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_pdmp_queries_delete` (DELETE) * `cl_pdmp_queries_insert` (INSERT) * `cl_pdmp_queries_select` (SELECT) * `cl_pdmp_queries_update` (UPDATE) ### `cl_peer_encounters` CL-40: Peer recovery support encounter documentation linked to PM encounters **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `encounter_id` | uuid | no | | | | `peer_supporter_id` | uuid | no | | | | `supervising_clinician_id` | uuid | yes | | | | `encounter_date` | date | no | | | | `duration_minutes` | integer | yes | | | | `topics_discussed` | text\[] | yes | | | | `interventions_provided` | text | yes | | | | `patient_engagement_score` | integer | yes | | | | `peer_observations` | jsonb | yes | | Structured peer observations (strengths, concerns, recovery capital) | | `clinician_review_notes` | text | yes | | | | `billed_as` | jsonb | yes | | | | `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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_peer_encounters_delete` (DELETE) * `cl_peer_encounters_insert` (INSERT) * `cl_peer_encounters_select` (SELECT) * `cl_peer_encounters_update` (UPDATE) ### `cl_pending_lab_results` CL-09 Phase 2: Staging table for unsolicited lab results pending clinical review and linking **RLS:** ✅ enabled (5 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `lab_id` | uuid | no | | | | `external_order_id` | text | yes | | | | `loinc_code` | text | yes | | | | `message_format` | text | yes | | | | `message_control_id` | text | yes | | | | `result_data` | jsonb | no | | Array of MappedResult objects from HL7v2/FHIR ingestion | | `raw_metadata` | jsonb | yes | | | | `status` | text | no | 'pending'::text | | | `linked_chart_id` | uuid | yes | | | | `linked_order_id` | uuid | yes | | | | `linked_by` | uuid | yes | | | | `linked_at` | timestamp with time zone | yes | | | | `rejection_reason` | text | yes | | | | `expires_at` | timestamp with time zone | yes | | | | `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. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `lab_id` → `cl_reference_labs(id)` * `linked_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, linked_chart_id` → `cl_patient_charts(organization_id, id)` * `organization_id, linked_order_id` → `cl_orders(organization_id, id)` **Policies:** * `cl_pending_lab_results_delete` (DELETE) * `cl_pending_lab_results_delete_org` (DELETE) * `cl_pending_lab_results_insert` (INSERT) * `cl_pending_lab_results_select` (SELECT) * `cl_pending_lab_results_update` (UPDATE) ### `cl_pharmacies` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `name` | text | no | | | | `ncpdp_id` | text | yes | | | | `phone` | text | yes | | | | `fax` | text | yes | | | | `is_active` | boolean | no | true | | | `accepts_epcs` | boolean | no | 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 | | | | `address` | jsonb | yes | | | **Policies:** * `cl_pharmacies_delete` (DELETE) * `cl_pharmacies_insert` (INSERT) * `cl_pharmacies_select` (SELECT) * `cl_pharmacies_update` (UPDATE) ### `cl_prescriptions` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `prescriber_id` | uuid | no | | | | `pharmacy_id` | uuid | yes | | | | `medication_name` | text | no | | | | `dosage` | text | yes | | | | `route` | text | yes | | | | `quantity` | integer | yes | | | | `days_supply` | integer | yes | 30 | | | `is_controlled` | boolean | no | false | | | `schedule` | text | yes | | | | `transmission_status` | text | no | 'pending'::text | | | `surescripts_message_id` | text | 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 | | | | `site_id` | uuid | yes | | | | `refills_authorized` | integer | no | 0 | | | `refills_remaining` | integer | no | 0 | | | `epcs_token_used` | boolean | no | false | | | `rtpb_response` | jsonb | yes | | | | `rtpb_checked` | boolean | no | false | | | `medication_id` | uuid | yes | | | | `rx_number` | text | yes | | | | `frequency` | text | yes | | | | `notes` | text | yes | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `medication_id` → `cl_medications(id)` * `pharmacy_id` → `cl_pharmacies(id)` * `site_id` → `pf_sites(id)` **Policies:** * `cl_prescriptions_delete` (DELETE) * `cl_prescriptions_insert` (INSERT) * `cl_prescriptions_select` (SELECT) * `cl_prescriptions_update` (UPDATE) ### `cl_problem_history` CL-46: Append-only audit trail for cl\_problems status transitions. **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `problem_id` | uuid | no | | | | `previous_status` | text | yes | | | | `new_status` | text | no | | | | `reason` | text | yes | | | | `changed_by` | uuid | yes | | | | `changed_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `changed_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `problem_id` → `cl_problems(id)` **Policies:** * `cl_problem_history_select` (SELECT) ### `cl_problem_status_history` **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `problem_id` | uuid | no | | | | `previous_status` | text | yes | | | | `new_status` | text | no | | | | `reason` | text | yes | | | | `changed_by` | uuid | yes | | | | `changed_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `problem_id` → `cl_problems(id)` **Policies:** * `cl_problem_status_history_select` (SELECT) ### `cl_problems` CL-01: Patient problem list entries. Supports ICD-10 coding and clinical status tracking. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `icd10_code` | text | yes | | | | `description` | text | no | | | | `onset_date` | date | yes | | | | `resolved_date` | date | yes | | | | `status` | text | no | 'active'::text | | | `severity` | text | no | 'moderate'::text | | | `managing_provider_id` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `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 | | | | `patient_id` | uuid | no | | CL-46: Patient reference (pf\_patient\_identities.id). | | `icd10_display` | text | no | ''::text | CL-46: ICD-10-CM code description from PF-70. | | `clinical_notes` | text | yes | | CL-46: Clinician notes about the problem. | | `recorded_by` | uuid | yes | | CL-46: Clinician who recorded the problem. | | `priority` | integer | yes | 0 | | | `resolution_reason` | text | yes | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `managing_provider_id` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pf_patient_identities(id)` * `recorded_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_problems_delete` (DELETE) * `cl_problems_insert_v2` (INSERT) * `cl_problems_select_v2` (SELECT) * `cl_problems_update_v2` (UPDATE) ### `cl_program_attendance_summaries` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `program_enrollment_id` | uuid | no | | | | `period_start` | date | no | | | | `period_end` | date | no | | | | `hours_attended` | numeric | no | 0 | | | `hours_required` | numeric | no | | | | `compliant` | boolean | 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)` * `program_enrollment_id` → `cl_program_enrollments(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_program_attendance_summaries_delete` (DELETE) * `cl_program_attendance_summaries_insert` (INSERT) * `cl_program_attendance_summaries_select` (SELECT) * `cl_program_attendance_summaries_update` (UPDATE) ### `cl_program_enrollments` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `program_schedule_id` | uuid | no | | | | `enrolled_at` | date | no | | | | `ended_at` | date | 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 | no | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `program_schedule_id` → `cl_program_schedules(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` **Policies:** * `cl_program_enrollments_delete` (DELETE) * `cl_program_enrollments_insert` (INSERT) * `cl_program_enrollments_select` (SELECT) * `cl_program_enrollments_update` (UPDATE) ### `cl_program_outcomes` CL-10: Program-level aggregated outcomes (HEDIS, dashboards). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `program_id` | uuid | yes | | References rh\_programs(id). NULL if org-wide aggregate. | | `measure_type` | text | no | | | | `measure_name` | text | no | | | | `period_start` | date | no | | | | `period_end` | date | no | | | | `numerator` | integer | yes | | | | `denominator` | integer | yes | | | | `rate` | numeric(5,2) | yes | | | | `benchmark` | numeric(5,2) | yes | | | | `meets_benchmark` | boolean | yes | | | | `calculation_method` | text | yes | | | | `data_sources` | jsonb | yes | '\[]'::jsonb | | | `notes` | text | yes | | | | `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. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `program_id` → `rh_programs(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_program_outcomes_delete` (DELETE) * `cl_program_outcomes_insert` (INSERT) * `cl_program_outcomes_select` (SELECT) * `cl_program_outcomes_update` (UPDATE) ### `cl_program_schedules` **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 | | | | `program_type` | text | no | | | | `hours_per_week_min` | numeric | no | | | | `days_per_week_min` | integer | no | | | | `schedule_definition` | jsonb | no | | | | `is_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)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_program_schedules_delete` (DELETE) * `cl_program_schedules_insert` (INSERT) * `cl_program_schedules_select` (SELECT) * `cl_program_schedules_update` (UPDATE) ### `cl_progress_notes` CL-04: Progress notes per AHCCCS Policy 940; soft-delete via deleted\_at. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `encounter_id` | uuid | yes | | | | `note_type` | text | no | | | | `service_line` | text | yes | | | | `service_date` | date | no | | | | `begin_time` | time without time zone | no | | | | `end_time` | time without time zone | no | | | | `duration_minutes` | integer | no | | | | `diagnosis_code` | text | no | | | | `presenting_problem` | text | no | | | | `mse_findings` | text | yes | | | | `interventions` | text | no | | | | `member_response` | text | no | | | | `plan_next_session` | text | yes | | | | `is_telehealth` | boolean | yes | false | | | `telehealth_modality` | text | yes | | | | `patient_location` | text | yes | | | | `provider_location` | text | yes | | | | `place_of_service` | text | yes | | | | `signed_by` | uuid | yes | | | | `signed_at` | timestamp with time zone | yes | | | | `signer_credentials` | text | yes | | | | `cosigned_by` | uuid | yes | | | | `cosigned_at` | timestamp with time zone | yes | | | | `cosigner_credentials` | text | yes | | | | `status` | text | no | 'draft'::text | | | `amended_from` | 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 | | | | `deleted_at` | timestamp with time zone | yes | | | | `ai_draft_indicator` | boolean | yes | false | EN-13: Note created via AI assist or dictation draft | | `dictation_metadata` | jsonb | no | '\{}'::jsonb | EN-13: Audio consent, dictation provenance metadata | | `quality_score` | numeric(5,2) | yes | | EN-14: Per-note documentation quality score (range 0.00–100.00) | | `note_940_validation_passed` | boolean | yes | | | | `note_940_validation_result` | jsonb | yes | '\{}'::jsonb | | | `note_940_override_reason` | text | yes | | | | `note_940_override_by` | uuid | yes | | | | `note_940_validated_at` | timestamp with time zone | yes | | | | `cod_session_type` | text | yes | | CL-31: Null for non-COD; 'cod\_dual\_diagnosis' triggers SUD section UI gating. | | `cod_sud_section` | jsonb | yes | | CL-31: SUD documentation for dual-diagnosis sessions. Null when 42 CFR Part 2 consent absent. | **Foreign keys:** * `amended_from` → `cl_progress_notes(id)` * `chart_id` → `cl_patient_charts(id)` * `cosigned_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `note_940_override_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `signed_by` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_progress_notes_delete` (DELETE) * `cl_progress_notes_insert` (INSERT) * `cl_progress_notes_select` (SELECT) * `cl_progress_notes_update` (UPDATE) ### `cl_quality_measure_periods` CL-35: HEDIS and MA STARS quality measure period calculations **RLS:** ✅ enabled (8 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `measure_id` | text | no | | Measure identifier (e.g., AMM, FUH, FUM) | | `measure_type` | text | no | | | | `period_start` | date | no | | | | `period_end` | date | no | | | | `denominator_count` | integer | no | | | | `numerator_count` | integer | no | | | | `rate` | numeric(5,4) | yes | | Numerator/denominator rate 0.0000–1.0000 | | `metadata` | jsonb | no | '\{}'::jsonb | Calculation metadata and audit trail | | `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:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `cl_qmp_delete` (DELETE) * `cl_qmp_insert` (INSERT) * `cl_qmp_select` (SELECT) * `cl_qmp_update` (UPDATE) * `cl_quality_measure_periods_insert` (INSERT) * `cl_quality_measure_periods_no_delete` (DELETE) * `cl_quality_measure_periods_select` (SELECT) * `cl_quality_measure_periods_update` (UPDATE) ### `cl_readmission_risk_scores` CL-29: Readmission risk assessments (0-100 scale) with configurable risk factors **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `assessed_at` | date | no | | | | `risk_score` | numeric | yes | | | | `risk_factors` | jsonb | no | '\[]'::jsonb | Array of risk factor objects \[\{name, weight, present}] | | `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, non-NULL = soft deleted | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_readmission_risk_scores_delete` (DELETE) * `cl_readmission_risk_scores_insert` (INSERT) * `cl_readmission_risk_scores_select` (SELECT) * `cl_readmission_risk_scores_update` (UPDATE) ### `cl_redisclosure_log` CL-11-EN-01: Immutable append-only audit trail for 42 CFR Part 2 record disclosures. UPDATE/DELETE denied for all application roles. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `consent_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `recipient` | text | no | | | | `purpose` | text | no | | | | `records_disclosed` | jsonb | no | | | | `disclosed_at` | timestamp with time zone | no | | | | `disclosed_by` | uuid | no | | | | `created_at` | timestamp with time zone | no | now() | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for redisclosure log extensibility | **Foreign keys:** * `consent_id` → `cl_electronic_consents(id)` * `disclosed_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_redisclosure_log_insert` (INSERT) * `cl_redisclosure_log_select` (SELECT) ### `cl_reference_labs` CL-09: Reference laboratory directory with integration configuration **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `lab_name` | text | no | | | | `clia_number` | text | yes | | | | `npi` | text | yes | | | | `address_line_1` | text | yes | | | | `address_line_2` | text | yes | | | | `city` | text | yes | | | | `state` | text | yes | | | | `zip_code` | text | yes | | | | `phone` | text | yes | | | | `fax` | text | yes | | | | `contact_name` | text | yes | | | | `contact_email` | text | yes | | | | `integration_type` | text | no | 'manual'::text | | | `connection_config` | jsonb | no | '\{}'::jsonb | Integration credentials and settings (e.g., \{"api\_key": "...", "allowed\_ips": \[...], "rate\_limit": 100}) | | `is_active` | boolean | no | true | | | `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. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_reference_labs_delete` (DELETE) * `cl_reference_labs_insert` (INSERT) * `cl_reference_labs_select` (SELECT) * `cl_reference_labs_update` (UPDATE) ### `cl_reference_ranges` CL-09 EN-32: Organization-specific reference ranges for lab result interpretation **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `loinc_code` | text | no | | LOINC code for the test (text, resolved via PF-70 at runtime) | | `display_name` | text | yes | | | | `sex` | text | yes | | | | `age_min_years` | numeric(5,1) | yes | | | | `age_max_years` | numeric(5,1) | yes | | | | `low_value` | numeric(12,4) | yes | | | | `high_value` | numeric(12,4) | yes | | | | `critical_low` | numeric(12,4) | yes | | | | `critical_high` | numeric(12,4) | yes | | | | `units` | text | yes | | | | `is_active` | boolean | no | true | | | `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:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `cl_reference_ranges_delete` (DELETE) * `cl_reference_ranges_insert` (INSERT) * `cl_reference_ranges_select` (SELECT) * `cl_reference_ranges_update` (UPDATE) ### `cl_referral_status_history` CL-12-EN-67: Append-only referral status history for closed-loop tracking **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `referral_id` | uuid | no | | Bare UUID referencing pm\_referrals.id (no FK per cross-core rules) | | `status` | text | no | | | | `patient_connected` | boolean | yes | | | | `patient_connected_at` | timestamp with time zone | yes | | | | `external_status_source` | text | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_referral_status_history_delete` (DELETE) * `cl_referral_status_history_insert` (INSERT) * `cl_referral_status_history_select` (SELECT) ### `cl_report_definitions` CL-15: Configurable clinical compliance report definitions (Joint Commission, CARF, HEDIS, AHCCCS, AZDHS, custom) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `report_name` | text | no | | | | `report_type` | text | no | | | | `measure_ids` | jsonb | yes | '\[]'::jsonb | | | `filters` | jsonb | yes | '\{}'::jsonb | | | `schedule_cron` | text | yes | | | | `recipients` | jsonb | yes | '\[]'::jsonb | | | `format` | text | yes | 'pdf'::text | | | `is_active` | boolean | yes | true | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"external\_report\_id": "RPT-001"}) | | `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:** * `cl_report_definitions_delete` (DELETE) * `cl_report_definitions_insert` (INSERT) * `cl_report_definitions_select` (SELECT) * `cl_report_definitions_update` (UPDATE) ### `cl_report_runs` CL-15: Execution log for report generation runs; tracks status, parameters, and output document reference **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `report_definition_id` | uuid | yes | | | | `organization_id` | uuid | no | | | | `run_started_at` | timestamp with time zone | no | now() | | | `run_completed_at` | timestamp with time zone | yes | | | | `status` | text | no | 'running'::text | | | `parameters` | jsonb | yes | '\{}'::jsonb | | | `result_summary` | jsonb | yes | '\{}'::jsonb | | | `document_id` | uuid | yes | | | | `generated_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `generated_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `report_definition_id` → `cl_report_definitions(id)` **Policies:** * `cl_report_runs_delete` (DELETE) * `cl_report_runs_insert` (INSERT) * `cl_report_runs_select` (SELECT) * `cl_report_runs_update` (UPDATE) ### `cl_review_appeals` CL-43: Tracks appeals filed against denied concurrent reviews. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `review_id` | uuid | no | | | | `appeal_level` | text | no | | | | `appeal_date` | date | no | | | | `appeal_deadline` | date | yes | | | | `status` | text | no | 'draft'::text | | | `outcome_date` | date | yes | | | | `outcome_notes` | text | yes | | | | `supporting_documents` | jsonb | yes | '\[]'::jsonb | | | `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:** * `organization_id` → `pf_organizations(id)` * `review_id` → `cl_concurrent_reviews(id)` **Policies:** * `cl_review_appeals_delete` (DELETE) * `cl_review_appeals_insert` (INSERT) * `cl_review_appeals_select` (SELECT) * `cl_review_appeals_update` (UPDATE) ### `cl_review_schedules` CL-43: Payer-specific concurrent review interval configuration. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `payer_name` | text | no | | | | `level_of_care` | text | no | | | | `review_interval_days` | integer | no | | | | `initial_review_days` | integer | yes | | | | `grace_period_days` | integer | no | 0 | | | `escalation_threshold_days` | integer | no | 1 | | | `is_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 | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_review_schedules_delete` (DELETE) * `cl_review_schedules_insert` (INSERT) * `cl_review_schedules_select` (SELECT) * `cl_review_schedules_update` (UPDATE) ### `cl_risk_screenings` CL-07: Suicide risk screening records. Instruments: C-SSRS, PHQ-9 Item 9, ASQ, SAFE-T. SAFE-T score column is NULL by design (non-numeric instrument; FR-1.6). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `instrument` | text | no | | | | `score` | numeric | yes | | Numeric score for C-SSRS, PHQ-9 Item 9, and ASQ. NULL for SAFE-T (non-numeric; clinician sets risk\_level directly). | | `risk_level` | text | no | | | | `responses` | jsonb | no | '\{}'::jsonb | Instrument-specific response data (e.g., \{"ideation\_q1": 1, "ideation\_q2": 0, ...}). | | `screened_by` | uuid | no | | | | `screened_at` | timestamp with time zone | no | now() | | | `next_screening_due` | timestamp with time zone | yes | | Calculated rescreening due date: high/imminent=+24h, moderate=+72h, low=+low\_risk\_rescreen\_interval\_days. | | `trigger_reason` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific screening 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. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `screened_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_risk_screenings_delete` (DELETE) * `cl_risk_screenings_insert` (INSERT) * `cl_risk_screenings_select` (SELECT) * `cl_risk_screenings_update` (UPDATE) ### `cl_risk_stratifications` CL-35: Patient risk stratification scores computed by the population health engine **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `stratification_date` | timestamp with time zone | no | now() | | | `composite_risk_score` | numeric(5,2) | no | | Weighted composite score 0.00–100.00 | | `risk_tier` | text | no | | Derived tier: low/medium/high/critical | | `risk_factors` | jsonb | no | '\{}'::jsonb | JSONB breakdown of individual risk factor weights and values | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `cl_risk_strat_delete` (DELETE) * `cl_risk_strat_insert` (INSERT) * `cl_risk_strat_select` (SELECT) * `cl_risk_strat_update` (UPDATE) ### `cl_safety_plan_shares` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `safety_plan_id` | uuid | no | | | | `shared_with_name` | text | no | | | | `shared_with_contact` | text | yes | | | | `share_method` | text | no | | | | `consent_documented` | boolean | no | false | | | `consent_date` | timestamp with time zone | yes | | | | `shared_at` | timestamp with time zone | no | now() | | | `shared_by` | uuid | no | | | | `expires_at` | timestamp with time zone | yes | | | | `status` | text | no | 'active'::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 | no | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `safety_plan_id` → `cl_safety_plans(id)` * `shared_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `rls_cl_safety_plan_shares_delete` (DELETE) * `rls_cl_safety_plan_shares_insert` (INSERT) * `rls_cl_safety_plan_shares_select` (SELECT) * `rls_cl_safety_plan_shares_update` (UPDATE) ### `cl_safety_plans` CL-07: Stanley-Brown safety plans with 6 components. Patient/clinician signatures via SignatureCanvas (Pattern A — Errata E-6). Versioning via superseded\_by + status. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `warning_signs` | jsonb | no | '\[]'::jsonb | Stanley-Brown component 1: Warning signs (thoughts, images, mood, situation, behavior). Array of strings. | | `coping_strategies` | jsonb | no | '\[]'::jsonb | Stanley-Brown component 2: Internal coping strategies. Array of strings. | | `social_contacts` | jsonb | no | '\[]'::jsonb | Stanley-Brown component 3: People and social settings for distraction. Array of \{name, details}. | | `family_friends` | jsonb | no | '\[]'::jsonb | Stanley-Brown component 4: People to ask for help (family/friends). Array of \{name, phone}. | | `professionals` | jsonb | no | '\[]'::jsonb | Stanley-Brown component 5: Professionals or agencies to contact. Array of \{name, phone, hours}. | | `means_restriction` | jsonb | no | '\{}'::jsonb | Stanley-Brown component 6: Making the environment safe (means restriction). Object with lethal\_means details. | | `lethal_means_counseling_completed` | boolean | no | false | | | `patient_signed_at` | timestamp with time zone | yes | | | | `clinician_signed_at` | timestamp with time zone | yes | | | | `patient_signature_data` | jsonb | yes | | Errata E-6: base64 SignatureCanvas image for patient signature. NULL until signed. | | `clinician_signature_data` | jsonb | yes | | Errata E-6: base64 SignatureCanvas image for clinician signature. NULL until signed. | | `clinician_id` | uuid | yes | | | | `status` | text | no | 'active'::text | Plan lifecycle status. active = current plan; superseded = replaced by newer version. | | `version` | integer | no | 1 | | | `superseded_by` | uuid | yes | | FK to the newer plan version that replaced this one. NULL if still active. | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific safety plan metadata. | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | no | | | | `updated_by` | uuid | yes | | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active. | | `lethal_means_discussed_at` | timestamp with time zone | yes | | EN-23: Timestamp when lethal means counseling was discussed | | `lethal_means_outcome` | text | yes | | EN-23: Outcome — discussed, declined, follow-up, not\_applicable | | `lethal_means_notes` | text | yes | | EN-23: Free-text notes from lethal means counseling session | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `clinician_id` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `superseded_by` → `cl_safety_plans(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_safety_plans_delete` (DELETE) * `cl_safety_plans_insert` (INSERT) * `cl_safety_plans_select` (SELECT) * `cl_safety_plans_update` (UPDATE) ### `cl_screening_administrations` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `encounter_id` | uuid | yes | | | | `instrument_code` | text | no | | | | `administered_at` | timestamp with time zone | no | now() | | | `total_score` | numeric(6,2) | no | 0 | | | `severity` | text | no | ''::text | | | `item_responses` | jsonb | no | '\{}'::jsonb | | | `score_result` | jsonb | no | '\{}'::jsonb | | | `administered_by` | uuid | no | | | | `notes` | text | yes | | | | `alert_triggered` | boolean | no | false | | | `alert_acknowledged_at` | timestamp with time zone | yes | | | | `alert_acknowledged_by` | 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:** * `organization_id` → `pf_organizations(id)` **Policies:** * `cl_screening_admin_delete` (DELETE) * `cl_screening_admin_insert` (INSERT) * `cl_screening_admin_select` (SELECT) * `cl_screening_admin_update` (UPDATE) ### `cl_sdoh_screenings` CL-18: SDOH screening records (PRAPARE, AHC-HRSN, etc.) linked to patient charts **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `instrument` | text | no | | | | `screened_at` | timestamp with time zone | no | | | | `screened_by` | uuid | yes | | | | `responses` | jsonb | no | '\{}'::jsonb | | | `needs_identified` | text\[] | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"screening\_version": "2.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 | | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `screened_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_sdoh_screenings_delete` (DELETE) * `cl_sdoh_screenings_insert` (INSERT) * `cl_sdoh_screenings_select` (SELECT) * `cl_sdoh_screenings_update` (UPDATE) ### `cl_social_referrals` CL-18: Community resource referrals for identified social needs **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `screening_id` | uuid | yes | | | | `resource_name` | text | no | | | | `resource_type` | text | yes | | | | `need_category` | text | yes | | | | `status` | text | no | 'referred'::text | | | `referred_at` | timestamp with time zone | no | now() | | | `referred_by` | uuid | yes | | | | `outcome_notes` | text | yes | | | | `outcome_at` | timestamp with time zone | yes | | | | `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. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `referred_by` → `pf_profiles(id)` * `screening_id` → `cl_sdoh_screenings(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_social_referrals_delete` (DELETE) * `cl_social_referrals_insert` (INSERT) * `cl_social_referrals_select` (SELECT) * `cl_social_referrals_update` (UPDATE) ### `cl_standing_order_executions` CL-44: Append-only nursing execution log for standing order protocols. UPDATE/DELETE forbidden via restrictive RLS. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `standing_order_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `patient_id` | uuid | no | | Cross-core reference to pm\_patients.id. No DB FK per ADR-002; validated at application layer. | | `executed_by` | uuid | no | | | | `executed_at` | timestamp with time zone | no | now() | | | `action_taken` | text | no | | | | `parameters_used` | jsonb | no | | | | `patient_response` | text | yes | | | | `outcome` | text | yes | | | | `notes` | text | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `executed_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `standing_order_id` → `cl_clinical_standing_orders(id)` **Policies:** * `cl_standing_order_executions_insert` (INSERT) * `cl_standing_order_executions_no_delete` (DELETE) * `cl_standing_order_executions_no_update` (UPDATE) * `cl_standing_order_executions_select` (SELECT) ### `cl_standing_order_protocols` CL-09 EN-30: Standing order protocols for automated/recurring lab orders **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `template_id` | uuid | yes | | | | `order_items` | jsonb | yes | | | | `recurrence_rule` | jsonb | no | '\{}'::jsonb | | | `is_active` | boolean | no | true | | | `patient_criteria` | jsonb | yes | '\{}'::jsonb | | | `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)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `cl_standing_order_protocols_delete` (DELETE) * `cl_standing_order_protocols_insert` (INSERT) * `cl_standing_order_protocols_select` (SELECT) * `cl_standing_order_protocols_update` (UPDATE) ### `cl_tefca_exchange_log` CL-16-EN-01: Append-only audit log tracking inbound and outbound TEFCA/QHIN exchange attempts and outcomes **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `patient_id` | uuid | yes | | Patient reference (nullable for unmatched inbound searches) | | `direction` | text | no | | | | `qhin_identifier` | text | no | | | | `purpose_of_use` | text | no | | TEFCA purpose of use per Common Agreement: treatment, payment, operations, public\_health, government\_benefits\_determination, individual\_access\_services | | `document_type` | text | yes | | | | `consent_id` | uuid | yes | | | | `correlation_id` | text | no | | External exchange correlation key for tracing across QHIN transport | | `status` | text | no | | | | `failure_reason` | text | yes | | Structured failure reason (no PHI); e.g. timeout, auth\_failure, consent\_denied | | `exchanged_at` | timestamp with time zone | no | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for extensibility | | `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:** * `consent_id` → `cl_consents(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_tefca_exchange_log_insert` (INSERT) * `cl_tefca_exchange_log_select` (SELECT) * `cl_tefca_exchange_log_service` (ALL) * `cl_tefca_exchange_log_update` (UPDATE) ### `cl_telehealth_consents` CL-24: Per-patient telehealth consent with annual renewal. Immutable for audit; no deleted\_at. Revoke is append-only for non-admins via trigger. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `consent_type` | text | no | | CL-24: telehealth\_general implies audio\_only; audio\_only does not imply general (see CONTEXT.md). | | `consented_at` | timestamp with time zone | no | | | | `expires_at` | timestamp with time zone | no | | | | `revoked_at` | timestamp with time zone | yes | | | | `revoked_reason` | text | yes | | | | `consent_method` | text | no | | | | `consent_document_ref` | text | yes | | | | `consent_language_version` | text | no | | CL-24: jurisdiction-aware consent language lineage. AZ-EN-2026-01 = Arizona ARS 13-3005 compliant. | | `captured_by` | 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:** * `captured_by` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_telehealth_consents_delete` (DELETE) * `cl_telehealth_consents_insert` (INSERT) * `cl_telehealth_consents_select` (SELECT) * `cl_telehealth_consents_update` (UPDATE) ### `cl_telehealth_sessions` CL-24: Clinical compliance wrapper around a PM-13 telehealth session. Holds safety checklist, jurisdictional state, connectivity log. Immutable for audit; no deleted\_at. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `pm_telehealth_session_id` | uuid | yes | | CL-24: UUID-only reference to pm\_telehealth\_sessions.id; no FK across cores. | | `progress_note_id` | uuid | yes | | | | `session_at` | timestamp with time zone | no | | | | `modality` | text | no | | | | `patient_state` | character(2) | yes | | | | `provider_state` | character(2) | yes | | | | `patient_location_type` | text | yes | | | | `technology_verified` | boolean | no | false | | | `technology_verification_notes` | text | yes | | | | `connectivity_issues` | jsonb | no | '\[]'::jsonb | CL-24: append-only JSONB array of \{timestamp, type, duration\_minutes, resolution}. App caps length at 50. | | `patient_current_location_verified` | boolean | no | false | | | `local_emergency_number` | text | yes | | | | `support_person_on_site` | boolean | yes | | | | `support_person_name` | text | yes | | | | `disconnect_protocol_acknowledged` | boolean | no | false | | | `safety_checklist_completed_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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `progress_note_id` → `cl_progress_notes(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_telehealth_sessions_delete` (DELETE) * `cl_telehealth_sessions_insert` (INSERT) * `cl_telehealth_sessions_select` (SELECT) * `cl_telehealth_sessions_update` (UPDATE) ### `cl_transitions` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `transition_type` | text | no | | | | `transition_date` | date | no | | | | `from_level_of_care` | text | yes | | | | `to_level_of_care` | text | yes | | | | `from_facility` | text | yes | | | | `to_facility` | text | yes | | | | `reason` | text | yes | | | | `medication_reconciliation_id` | uuid | yes | | | | `safety_plan_updated` | boolean | no | false | | | `referral_id` | uuid | yes | | | | `discharge_summary_id` | uuid | yes | | | | `fuh_eligible` | boolean | no | false | | | `fuh_exclusion_reason` | text | yes | | | | `fuh_7day_met` | boolean | no | false | | | `fuh_30day_met` | boolean | no | false | | | `followup_scheduled` | boolean | no | false | | | `followup_date` | date | yes | | | | `followup_completed_at` | timestamp with time zone | yes | | | | `notes` | text | 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)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` * `organization_id, chart_id` → `cl_patient_charts(organization_id, id)` * `organization_id, medication_reconciliation_id` → `cl_medication_reconciliations(organization_id, id)` **Policies:** * `cl_transitions_delete` (DELETE) * `cl_transitions_insert` (INSERT) * `cl_transitions_select` (SELECT) * `cl_transitions_update` (UPDATE) ### `cl_treatment_goal_bank` CL-03 Phase 3: Pre-built goal entries associated with treatment plan templates **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `template_id` | uuid | no | | | | `goal_number` | integer | no | | | | `goal_text_patient_words` | text | no | | Goal text in patient-friendly language | | `measurable_criteria` | text | no | | | | `frequency` | text | no | | | | `duration` | text | no | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `template_id` → `cl_treatment_plan_templates(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_treatment_goal_bank_delete` (DELETE) * `cl_treatment_goal_bank_insert` (INSERT) * `cl_treatment_goal_bank_select` (SELECT) * `cl_treatment_goal_bank_update` (UPDATE) ### `cl_treatment_goals` CL-03: Treatment goals in patient words per AHCCCS Person-Centered Planning. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | -------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `plan_id` | uuid | no | | | | `problem_id` | uuid | yes | | | | `goal_number` | integer | yes | | | | `goal_text_patient_words` | text | no | | | | `measurable_criteria` | text | no | | | | `frequency` | text | no | | | | `duration` | text | no | | | | `target_date` | date | yes | | | | `status` | text | no | 'not\_started'::text | | | `progress_pct` | integer | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `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)` * `plan_id` → `cl_treatment_plans(id)` * `problem_id` → `cl_problems(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_treatment_goals_delete` (DELETE) * `cl_treatment_goals_insert` (INSERT) * `cl_treatment_goals_select` (SELECT) * `cl_treatment_goals_update` (UPDATE) ### `cl_treatment_interventions` CL-03: Specific interventions linked to treatment goals. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `goal_id` | uuid | no | | | | `intervention_description` | text | no | | | | `responsible_provider_id` | uuid | yes | | | | `frequency` | text | yes | | | | `modality` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `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)` * `goal_id` → `cl_treatment_goals(id)` * `organization_id` → `pf_organizations(id)` * `responsible_provider_id` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_treatment_interventions_delete` (DELETE) * `cl_treatment_interventions_insert` (INSERT) * `cl_treatment_interventions_select` (SELECT) * `cl_treatment_interventions_update` (UPDATE) ### `cl_treatment_plan_signatures` CL-03: Immutable multi-disciplinary sign-off records. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `treatment_plan_id` | uuid | no | | | | `signer_id` | uuid | no | | | | `signer_role` | text | no | | | | `signed_at` | timestamp with time zone | no | now() | | | `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. | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"badge\_number": "B-456"}) | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `signer_id` → `pf_profiles(id)` * `treatment_plan_id` → `cl_treatment_plans(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_treatment_plan_signatures_delete` (DELETE) * `cl_treatment_plan_signatures_insert` (INSERT) * `cl_treatment_plan_signatures_select` (SELECT) * `cl_treatment_plan_signatures_update` (UPDATE) ### `cl_treatment_plan_templates` CL-03 Phase 3: Pre-built treatment plan templates by service line/diagnosis with goal banks **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `name` | text | no | | | | `service_line` | text | yes | | | | `description` | text | yes | | | | `template_snapshot` | jsonb | yes | '\{}'::jsonb | JSONB snapshot of template structure (plan fields, default values) | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_treatment_plan_templates_delete` (DELETE) * `cl_treatment_plan_templates_insert` (INSERT) * `cl_treatment_plan_templates_select` (SELECT) * `cl_treatment_plan_templates_update` (UPDATE) ### `cl_treatment_plans` CL-03: Treatment plans with AHCCCS review frequency auto-calculation. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `plan_type` | text | no | 'initial'::text | | | `service_line` | text | yes | | | | `status` | text | no | 'draft'::text | | | `effective_date` | date | yes | | | | `review_due_date` | date | yes | | | | `patient_signed` | boolean | yes | false | | | `patient_agrees` | boolean | yes | true | | | `patient_signed_at` | timestamp with time zone | yes | | | | `clinician_signed_at` | timestamp with time zone | yes | | | | `clinician_signed_by` | uuid | yes | | | | `version` | integer | no | 1 | | | `revision_reason` | text | yes | | | | `previous_plan_id` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `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 | | | | `caregiver_participation_at` | timestamp with time zone | yes | | CL-03 Phase 2: Timestamp when family/caregiver participated in plan development or review | | `caregiver_role` | text | yes | | CL-03 Phase 2: Role of caregiver (e.g. parent, spouse, guardian, peer). No PHI. | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `clinician_signed_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `previous_plan_id` → `cl_treatment_plans(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_treatment_plans_delete` (DELETE) * `cl_treatment_plans_insert` (INSERT) * `cl_treatment_plans_select` (SELECT) * `cl_treatment_plans_update` (UPDATE) ### `cl_uds_interpretation_notes` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `uds_order_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `encounter_id` | uuid | yes | | | | `interpretation_summary` | text | yes | | | | `counseling_provided` | boolean | no | false | | | `counseling_notes` | text | yes | | | | `follow_up_plan` | text | 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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `users(id)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `uds_order_id` → `cl_uds_orders(id)` * `updated_by` → `users(id)` **Policies:** * `cl_uds_interp_delete` (DELETE) * `cl_uds_interp_insert` (INSERT) * `cl_uds_interp_select` (SELECT) * `cl_uds_interp_update` (UPDATE) ### `cl_uds_orders` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------------- | ------------------------ | -------- | -------------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `encounter_id` | uuid | yes | | | | `cl_order_id` | uuid | yes | | | | `order_type` | text | no | 'presumptive'::text | | | `panel_type` | text | no | 'standard\_5\_panel'::text | | | `status` | text | no | 'ordered'::text | | | `observed` | boolean | no | false | | | `collector_id` | uuid | yes | | | | `is_external` | boolean | no | false | | | `external_collector_name` | text | yes | | | | `collection_time` | timestamp with time zone | yes | | | | `specimen_id` | text | yes | | | | `temperature_at_collection` | numeric(5,1) | yes | | | | `ordered_at` | timestamp with time zone | no | now() | | | `collected_at` | timestamp with time zone | yes | | | | `sent_to_lab_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:** * `chart_id` → `cl_patient_charts(id)` * `cl_order_id` → `cl_orders(id)` * `collector_id` → `users(id)` * `created_by` → `users(id)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `cl_uds_orders_delete` (DELETE) * `cl_uds_orders_insert` (INSERT) * `cl_uds_orders_select` (SELECT) * `cl_uds_orders_update` (UPDATE) ### `cl_uds_results` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `uds_order_id` | uuid | no | | | | `chart_id` | uuid | no | | | | `result_date` | timestamp with time zone | no | now() | | | `drug_class` | text | no | | | | `result` | text | no | | | | `concentration` | numeric(10,2) | yes | | | | `expected_from_medication` | boolean | yes | false | | | `court_reportable` | boolean | no | 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 | | | **Foreign keys:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `uds_order_id` → `cl_uds_orders(id)` * `updated_by` → `users(id)` **Policies:** * `cl_uds_results_delete` (DELETE) * `cl_uds_results_insert` (INSERT) * `cl_uds_results_select` (SELECT) * `cl_uds_results_update` (UPDATE) ### `cl_user_icd10_favorites` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | -------- | ------- | | `user_id` | uuid | no | | | | `organization_id` | uuid | no | | | | `icd10_code` | text | no | | | | `description` | text | no | ''::text | | | `use_count` | integer | no | 1 | | | `last_used_at` | timestamp with time zone | no | now() | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `user_id` → `users(id)` **Policies:** * `cl_user_icd10_favorites_delete` (DELETE) * `cl_user_icd10_favorites_insert` (INSERT) * `cl_user_icd10_favorites_select` (SELECT) * `cl_user_icd10_favorites_update` (UPDATE) ### `cl_vital_signs` CL-22: Vital signs measurements (BP, HR, RR, temp, SpO2, pain) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `captured_at` | timestamp with time zone | no | | | | `systolic_bp` | integer | yes | | | | `diastolic_bp` | integer | yes | | | | `heart_rate` | integer | yes | | | | `respiratory_rate` | integer | yes | | | | `temperature_c` | numeric | yes | | | | `spo2_percent` | numeric | yes | | | | `pain_score` | integer | yes | | | | `source` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata; do not store structured PHI; constitution §5.2.1 | | `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:** * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_vital_signs_delete` (DELETE) * `cl_vital_signs_insert` (INSERT) * `cl_vital_signs_select` (SELECT) * `cl_vital_signs_update` (UPDATE) ### `cl_warm_handoffs` CL-29: Provider-to-provider warm handoff documentation with caller/receiver tracking **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `chart_id` | uuid | no | | | | `transition_id` | uuid | no | | Link to cl\_care\_transitions(id) when CL-12 schema exists; plain UUID, no FK constraint | | `caller_id` | uuid | no | | | | `receiver_id` | uuid | no | | | | `accepted_at` | timestamp with time zone | yes | | | | `acceptance_notes` | text | yes | | | | `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, non-NULL = soft deleted | **Foreign keys:** * `caller_id` → `pf_profiles(id)` * `chart_id` → `cl_patient_charts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `receiver_id` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `cl_warm_handoffs_delete` (DELETE) * `cl_warm_handoffs_insert` (INSERT) * `cl_warm_handoffs_select` (SELECT) * `cl_warm_handoffs_update` (UPDATE) ### `cl_weno_directory_ingest_runs` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `run_mode` | text | no | | | | `status` | text | no | 'pending'::text | | | `record_count` | integer | no | 0 | | | `drift_columns` | jsonb | no | '\[]'::jsonb | | | `file_hash` | text | yes | | | | `error_code` | text | yes | | | | `error_category` | text | yes | | | | `started_at` | timestamp with time zone | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `metadata` | 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:** * `cl_weno_directory_ingest_runs_delete` (DELETE) * `cl_weno_directory_ingest_runs_insert` (INSERT) * `cl_weno_directory_ingest_runs_select` (SELECT) * `cl_weno_directory_ingest_runs_update` (UPDATE) ### `cl_weno_launch_attempts` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `user_id` | uuid | yes | | | | `encounter_id` | uuid | yes | | | | `endpoint_type` | text | no | | | | `status` | text | no | 'pending'::text | | | `correlation_id` | uuid | no | gen\_random\_uuid() | | | `error_code` | text | yes | | | | `error_category` | text | yes | | | | `metadata` | 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)` * `user_id` → `users(id)` **Policies:** * `cl_weno_launch_attempts_delete` (DELETE) * `cl_weno_launch_attempts_insert` (INSERT) * `cl_weno_launch_attempts_select` (SELECT) * `cl_weno_launch_attempts_update` (UPDATE) ### `cl_weno_newrx_sync_runs` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `vendor_report_id` | text | yes | | | | `window_start` | timestamp with time zone | yes | | | | `window_end` | timestamp with time zone | yes | | | | `status` | text | no | 'pending'::text | | | `parsed_count` | integer | no | 0 | | | `dedupe_count` | integer | no | 0 | | | `emitted_event_count` | integer | no | 0 | | | `error_code` | text | yes | | | | `error_category` | text | yes | | | | `metadata` | 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:** * `cl_weno_newrx_sync_runs_delete` (DELETE) * `cl_weno_newrx_sync_runs_insert` (INSERT) * `cl_weno_newrx_sync_runs_select` (SELECT) * `cl_weno_newrx_sync_runs_update` (UPDATE)