> ## 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. # Practice Management Schema Reference > Generated schema reference for the pm core (156 tables). > **Generated** by `npm run db:docs:generate` — DO NOT EDIT BY HAND. Change the schema via a migration, then regenerate. **Core:** `pm_` · **Tables:** 156 · **Source generated:** 2026-06-10T02:55:32.169Z ## Entity relationships ```mermaid theme={null} erDiagram pm_ai_coding_suggestions ||--o{ pm_ai_coding_suggestion_audits : "suggestion_id" pm_claim_denials ||--o{ pm_appeal_submissions : "denial_id" pm_appeal_templates ||--o{ pm_appeal_submissions : "template_id" pm_payers ||--o{ pm_appeal_templates : "payer_id" pm_appointments ||--o{ pm_appointment_match_log : "appointment_id" pm_patients ||--o{ pm_appointment_match_log : "patient_id" pm_appointments ||--o{ pm_appointment_reminders : "appointment_id" pm_patients ||--o{ pm_appointments : "patient_id" pm_rooms ||--o{ pm_appointments : "room_id" pm_patients ||--o{ pm_assistance_program_referrals : "patient_id" pm_insurance_policies ||--o{ pm_assistance_program_referrals : "policy_id" pm_managed_care_authorizations ||--o{ pm_auth_concurrent_reviews : "authorization_id" pm_managed_care_authorizations ||--o{ pm_auth_denial_appeals : "authorization_id" pm_prior_authorizations ||--o{ pm_auth_reviews : "authorization_id" pm_patient_payment_plans ||--o{ pm_bad_debt_writeoffs : "payment_plan_id" pm_eligibility_checks ||--o{ pm_benefit_details : "eligibility_check_id" pm_patients ||--o{ pm_benefit_details : "patient_id" pm_appointments ||--o{ pm_benefit_estimates : "appointment_id" pm_eligibility_checks ||--o{ pm_benefit_estimates : "eligibility_check_id" pm_patients ||--o{ pm_benefit_estimates : "patient_id" pm_payers ||--o{ pm_billing_suggestion_rules : "payer_id" pm_payers ||--o{ pm_capitation_contracts : "payer_id" pm_capitation_contracts ||--o{ pm_capitation_periods : "contract_id" pm_capitation_periods ||--o{ pm_capitation_reconciliations : "period_id" pm_capitation_contracts ||--o{ pm_capitation_roster_events : "contract_id" pm_patients ||--o{ pm_capitation_roster_events : "patient_id" pm_patients ||--o{ pm_charge_reconciliation_results : "patient_id" pm_encounters ||--o{ pm_charges : "encounter_id" pm_fee_schedules ||--o{ pm_charges : "fee_schedule_id" pm_patients ||--o{ pm_charges : "patient_id" pm_patients ||--o{ pm_charity_care_applications : "patient_id" pm_appointments ||--o{ pm_checkin_discrepancies : "appointment_id" pm_eligibility_checks ||--o{ pm_checkin_discrepancies : "eligibility_check_id" pm_patients ||--o{ pm_checkin_discrepancies : "patient_id" pm_carc_codes ||--o{ pm_claim_denials : "carc_code" pm_claims ||--o{ pm_claim_denials : "claim_id" pm_claim_lines ||--o{ pm_claim_denials : "claim_line_id" pm_payers ||--o{ pm_claim_denials : "payer_id" pm_rarc_codes ||--o{ pm_claim_denials : "rarc_code" pm_charges ||--o{ pm_claim_lines : "charge_id" pm_claims ||--o{ pm_claim_lines : "claim_id" pm_claims ||--o{ pm_claim_notes : "claim_id" pm_claims ||--o{ pm_claims : "original_claim_id" pm_patients ||--o{ pm_claims : "patient_id" pm_payers ||--o{ pm_claims : "payer_id" pm_insurance_policies ||--o{ pm_claims : "policy_id" pm_patient_payment_plans ||--o{ pm_collection_actions : "payment_plan_id" pm_patient_payment_plans ||--o{ pm_collections_external_referrals : "payment_plan_id" pm_contract_modeling_scenarios ||--o{ pm_contract_modeling_scenarios : "parent_scenario_id" pm_payer_contracts ||--o{ pm_contracted_rates : "contract_id" pm_patient_cost_estimates ||--o{ pm_cost_estimate_line_items : "estimate_id,organization_id" pm_cost_categories ||--o{ pm_cost_rates : "cost_category_id" pm_payer_edi_enrollments ||--o{ pm_edi_enrollment_status_history : "enrollment_id" pm_appointments ||--o{ pm_eligibility_checks : "appointment_id" pm_patients ||--o{ pm_eligibility_checks : "patient_id" pm_insurance_policies ||--o{ pm_eligibility_checks : "policy_id" pm_patients ||--o{ pm_emergency_contacts : "patient_id" pm_encounters ||--o{ pm_encounter_costs : "encounter_id" pm_patients ||--o{ pm_encounter_costs : "patient_id" pm_appointments ||--o{ pm_encounters : "appointment_id" pm_patients ||--o{ pm_encounters : "patient_id" pm_payers ||--o{ pm_era_files : "payer_id" pm_claims ||--o{ pm_era_orphan_lines : "matched_claim_id" pm_payers ||--o{ pm_era_orphan_lines : "payer_id" pm_era_files ||--o{ pm_era_orphan_lines : "remittance_id" pm_claims ||--o{ pm_era_reconciliation_results : "claim_id" pm_claim_lines ||--o{ pm_era_reconciliation_results : "claim_line_id" pm_payments ||--o{ pm_era_reconciliation_results : "era_payment_id" pm_payers ||--o{ pm_era_reconciliation_results : "payer_id" pm_fee_schedules ||--o{ pm_fee_schedule_entries : "fee_schedule_id" pm_payers ||--o{ pm_fee_schedules : "payer_id" pm_fhir_app_registrations ||--o{ pm_fhir_access_log : "app_registration_id" pm_patient_access_consents ||--o{ pm_fhir_access_log : "consent_id" pm_patients ||--o{ pm_fhir_access_log : "patient_id" pm_patients ||--o{ pm_financial_assistance_applications : "patient_id" pm_patients ||--o{ pm_financial_counseling_sessions : "patient_id" pm_good_faith_estimates ||--o{ pm_gfe_disputes : "gfe_id" pm_appointments ||--o{ pm_good_faith_estimates : "appointment_id" pm_patients ||--o{ pm_good_faith_estimates : "patient_id" pm_group_definitions ||--o{ pm_group_enrollments : "group_definition_id" pm_patients ||--o{ pm_group_enrollments : "patient_id" pm_group_definitions ||--o{ pm_group_schedule : "group_definition_id" pm_rooms ||--o{ pm_group_schedule : "room_id" pm_patients ||--o{ pm_guarantors : "patient_id" pm_fhir_app_registrations ||--o{ pm_information_blocking_log : "app_registration_id" pm_patients ||--o{ pm_information_blocking_log : "patient_id" pm_claims ||--o{ pm_institutional_claim_details : "claim_id" pm_patients ||--o{ pm_insurance_policies : "patient_id" pm_payers ||--o{ pm_insurance_policies : "payer_id" pm_appointments ||--o{ pm_kiosk_checkins : "appointment_id" pm_patients ||--o{ pm_kiosk_checkins : "patient_id" pm_patients ||--o{ pm_managed_care_authorizations : "patient_id" pm_payers ||--o{ pm_managed_care_authorizations : "payer_id" pm_prior_authorizations ||--o{ pm_managed_care_authorizations : "prior_auth_id" pm_fee_schedules ||--o{ pm_module_settings : "default_fee_schedule_id" pm_charges ||--o{ pm_otp_billing_periods : "charge_id" pm_claims ||--o{ pm_otp_billing_periods : "claim_id" pm_patients ||--o{ pm_otp_billing_periods : "patient_id" pm_otp_programs ||--o{ pm_otp_billing_periods : "program_id" pm_otp_billing_periods ||--o{ pm_otp_service_components : "billing_period_id" pm_fhir_app_registrations ||--o{ pm_patient_access_consents : "app_registration_id" pm_patients ||--o{ pm_patient_access_consents : "patient_id" pm_patients ||--o{ pm_patient_addresses : "patient_id" pm_patients ||--o{ pm_patient_cost_estimates : "patient_id" pm_payers ||--o{ pm_patient_cost_estimates : "payer_id" pm_patients ||--o{ pm_patient_financial_assessments : "patient_id" pm_patient_merge_log ||--o{ pm_patient_merge_conflicts : "merge_log_id" pm_patients ||--o{ pm_patient_site_transfers : "patient_id" pm_patients ||--o{ pm_patient_statements : "patient_id" pm_statement_runs ||--o{ pm_patient_statements : "statement_run_id" pm_payers ||--o{ pm_payer_clearinghouse_routes : "payer_id" pm_payers ||--o{ pm_payer_contracts : "payer_id" pm_clearinghouse_config ||--o{ pm_payer_edi_enrollments : "clearinghouse_id" pm_payers ||--o{ pm_payer_edi_enrollments : "payer_id" pm_clearinghouse_config ||--o{ pm_payer_enrollments : "clearinghouse_id" pm_payers ||--o{ pm_payer_enrollments : "payer_id" pm_payer_modifier_rules ||--o{ pm_payer_modifier_rule_versions : "rule_id" pm_payers ||--o{ pm_payer_modifier_rules : "payer_id" pm_payers ||--o{ pm_payer_performance_snapshots : "payer_id" pm_claims ||--o{ pm_payment_applications : "claim_id" pm_claim_lines ||--o{ pm_payment_applications : "claim_line_id" pm_payments ||--o{ pm_payment_applications : "payment_id" pm_patient_payment_plans ||--o{ pm_payment_plan_installments : "payment_plan_id" pm_patients ||--o{ pm_payment_plans : "patient_id" pm_era_files ||--o{ pm_payments : "era_file_id" pm_patients ||--o{ pm_payments : "patient_id" pm_payers ||--o{ pm_payments : "payer_id" pm_portal_packet_assignments ||--o{ pm_portal_packet_items : "assignment_id" pm_portal_users ||--o{ pm_portal_sessions : "portal_user_id" pm_patients ||--o{ pm_portal_users : "organization_id,patient_id" pm_patients ||--o{ pm_portal_users : "organization_id,proxy_for_patient_id" pm_appointments ||--o{ pm_preadmission_packets : "appointment_id" pm_preadmission_form_templates ||--o{ pm_preadmission_packets : "packet_template_id" pm_patients ||--o{ pm_preadmission_packets : "patient_id" pm_patients ||--o{ pm_prior_authorizations : "patient_id" pm_payers ||--o{ pm_prior_authorizations : "payer_id" pm_provider_enrollments ||--o{ pm_provider_enrollment_events : "organization_id,provider_enrollment_id" pm_payers ||--o{ pm_provider_enrollments : "payer_id" pm_rcm_rule_executions ||--o{ pm_rcm_rule_executions : "original_execution_id" pm_rcm_automation_rules ||--o{ pm_rcm_rule_executions : "rule_id" pm_referrals ||--o{ pm_referral_documents : "referral_id" pm_referrals ||--o{ pm_referral_packet_items : "referral_id" pm_patients ||--o{ pm_referrals : "patient_id" pm_payers ||--o{ pm_revenue_code_mappings : "payer_id" pm_payers ||--o{ pm_rpa_bot_configurations : "payer_id" pm_rpa_bot_configurations ||--o{ pm_rpa_bot_executions : "bot_configuration_id,organization_id" pm_rpa_bot_configurations ||--o{ pm_rpa_execution_daily_summaries : "bot_configuration_id" pm_rpa_bot_executions ||--o{ pm_rpa_staged_documents : "execution_id,organization_id" pm_payers ||--o{ pm_scrub_custom_edits : "payer_id" pm_payers ||--o{ pm_scrub_rule_overrides : "payer_id" pm_scrub_rule_definitions ||--o{ pm_scrub_rule_overrides : "rule_key" pm_claims ||--o{ pm_secondary_claims : "primary_claim_id" pm_payments ||--o{ pm_secondary_claims : "primary_remittance_id" pm_claims ||--o{ pm_secondary_claims : "secondary_claim_id" pm_clearinghouse_config ||--o{ pm_site_billing_config : "clearinghouse_config_id" pm_fee_schedules ||--o{ pm_site_billing_config : "default_fee_schedule_id" pm_patient_statements ||--o{ pm_statement_deliveries : "statement_id" pm_encounters ||--o{ pm_superbills : "encounter_id" pm_patients ||--o{ pm_superbills : "patient_id" pm_coordinator_tasks ||--o{ pm_task_audit_log : "task_id" pm_appointments ||--o{ pm_telehealth_sessions : "appointment_id" pm_telehealth_configuration ||--o{ pm_telehealth_sessions : "configuration_id" pm_encounters ||--o{ pm_telehealth_sessions : "encounter_id" pm_patients ||--o{ pm_telehealth_sessions : "patient_id" pm_clearinghouse_config ||--o{ pm_transaction_batches : "clearinghouse_id" pm_transaction_batches ||--o{ pm_transaction_log : "batch_id" pm_claims ||--o{ pm_transaction_log : "claim_id" pm_patients ||--o{ pm_transaction_log : "patient_id" pm_vbp_programs ||--o{ pm_vbp_incentive_payments : "program_id" pm_vbp_programs ||--o{ pm_vbp_measures : "program_id" pm_payers ||--o{ pm_vbp_programs : "payer_id" pm_appointments ||--o{ pm_waitlist : "offered_appointment_id" pm_patients ||--o{ pm_waitlist : "patient_id" pm_patients ||--o{ pm_waitlist_entries : "patient_id" pm_waitlist_entries ||--o{ pm_waitlist_outreach_log : "waitlist_id" pm_ai_coding_suggestion_audits { uuid id PK uuid organization_id FK uuid suggestion_id FK uuid coder_id FK uuid created_by FK uuid updated_by FK } pm_ai_coding_suggestions { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_analytics_appointment_confirmed_log { uuid id PK uuid organization_id FK } pm_analytics_appointment_created_log { uuid id PK uuid organization_id FK } pm_analytics_funnel_daily { uuid id PK uuid organization_id FK } pm_analytics_no_show_risk { uuid id PK uuid organization_id FK } pm_analytics_revenue_impact { uuid id PK uuid organization_id FK } pm_analytics_screening_log { uuid id PK uuid organization_id FK } pm_analytics_waitlist_abandon_risk { uuid id PK uuid organization_id FK } pm_analytics_waitlist_log { uuid id PK uuid organization_id FK } pm_appeal_submissions { uuid id PK uuid organization_id FK uuid denial_id FK uuid template_id FK uuid submitted_by FK uuid created_by FK uuid updated_by FK } pm_appeal_templates { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_appointment_match_log { uuid id PK uuid organization_id FK uuid screening_id FK uuid patient_id FK uuid appointment_id FK uuid selected_provider_id FK uuid coordinator_override_from FK uuid coordinator_override_to FK uuid created_by FK } pm_appointment_reminders { uuid id PK uuid organization_id FK uuid appointment_id FK uuid created_by FK uuid updated_by FK } pm_appointments { uuid id PK uuid organization_id FK uuid patient_id FK uuid provider_id FK uuid location_id FK uuid room_id FK uuid created_by FK uuid updated_by FK } pm_assistance_program_referrals { uuid id PK uuid organization_id FK uuid patient_id FK uuid policy_id FK uuid created_by FK uuid updated_by FK } pm_audit_log { uuid id PK uuid organization_id FK uuid changed_by FK } pm_auth_concurrent_reviews { uuid id PK uuid organization_id FK uuid authorization_id FK uuid reviewer_id FK uuid created_by FK uuid updated_by FK } pm_auth_denial_appeals { uuid id PK uuid organization_id FK uuid authorization_id FK uuid created_by FK uuid updated_by FK } pm_auth_reviews { uuid id PK uuid organization_id FK uuid authorization_id FK uuid created_by FK uuid updated_by FK } pm_bad_debt_writeoffs { uuid id PK uuid organization_id FK uuid payment_plan_id FK uuid requested_by FK uuid approved_by FK uuid created_by FK uuid updated_by FK } pm_batch_jobs { uuid id PK uuid organization_id FK uuid created_by FK } pm_benefit_details { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid eligibility_check_id FK uuid created_by FK uuid updated_by FK } pm_benefit_estimates { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid eligibility_check_id FK uuid appointment_id FK uuid created_by FK uuid updated_by FK } pm_billing_suggestion_rules { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_capitation_contracts { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_capitation_periods { uuid id PK uuid organization_id FK uuid contract_id FK uuid created_by FK uuid updated_by FK } pm_capitation_reconciliations { uuid id PK uuid organization_id FK uuid period_id FK uuid reconciled_by FK uuid created_by FK uuid updated_by FK } pm_capitation_roster_events { uuid id PK uuid organization_id FK uuid contract_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_charge_recon_daily_summary { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_charge_reconciliation_results { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid task_id FK uuid resolved_by FK uuid created_by FK uuid updated_by FK } pm_charges { uuid id PK uuid organization_id FK uuid patient_id FK uuid encounter_id FK uuid provider_id FK uuid fee_schedule_id FK uuid reviewed_by FK uuid approved_by FK uuid created_by FK uuid updated_by FK } pm_charity_care_applications { uuid id PK uuid organization_id FK uuid patient_id FK uuid approved_by FK uuid created_by FK uuid updated_by FK } pm_checkin_discrepancies { uuid id PK uuid organization_id FK uuid appointment_id FK uuid eligibility_check_id FK uuid patient_id FK uuid resolved_by FK uuid created_by FK uuid updated_by FK } pm_claim_denials { uuid id PK uuid organization_id FK uuid claim_id FK uuid claim_line_id FK uuid payer_id FK text carc_code FK text rarc_code FK uuid assigned_to FK uuid resolved_by FK uuid created_by FK uuid updated_by FK } pm_claim_lines { uuid id PK uuid organization_id FK uuid claim_id FK uuid charge_id FK uuid created_by FK uuid updated_by FK } pm_claim_notes { uuid id PK uuid organization_id FK uuid claim_id FK uuid created_by FK uuid updated_by FK } pm_claims { uuid id PK uuid organization_id FK uuid patient_id FK uuid payer_id FK uuid policy_id FK uuid facility_id FK uuid original_claim_id FK uuid created_by FK uuid updated_by FK } pm_clearinghouse_config { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_code_modifier_rules { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_collection_actions { uuid id PK uuid organization_id FK uuid payment_plan_id FK uuid performed_by FK } pm_collections_external_referrals { uuid id PK uuid organization_id FK uuid payment_plan_id FK uuid created_by FK uuid updated_by FK } pm_contract_modeling_scenarios { uuid id PK uuid organization_id FK uuid parent_scenario_id FK } pm_contracted_rates { uuid id PK uuid organization_id FK uuid contract_id FK uuid created_by FK uuid updated_by FK } pm_coordinator_tasks { uuid id PK uuid organization_id FK } pm_cost_categories { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_cost_estimate_line_items { uuid id PK uuid organization_id FK uuid estimate_id FK uuid created_by FK uuid updated_by FK } pm_cost_rates { uuid id PK uuid organization_id FK uuid site_id FK uuid cost_category_id FK uuid created_by FK uuid updated_by FK } pm_denial_models { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_denial_predictions { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_edi_enrollment_status_history { uuid id PK uuid organization_id FK uuid enrollment_id FK uuid changed_by FK } pm_eligibility_checks { uuid id PK uuid organization_id FK uuid patient_id FK uuid policy_id FK uuid checked_by FK uuid created_by FK uuid updated_by FK uuid appointment_id FK } pm_emergency_contacts { uuid id PK uuid organization_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_encounter_costs { uuid id PK uuid organization_id FK uuid encounter_id FK uuid patient_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_encounters { uuid id PK uuid organization_id FK uuid patient_id FK uuid appointment_id FK uuid rendering_provider_id FK uuid supervising_provider_id FK uuid service_location_id FK uuid created_by FK uuid updated_by FK } pm_era_files { uuid id PK uuid organization_id FK uuid site_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_era_orphan_lines { uuid id PK uuid organization_id FK uuid remittance_id FK uuid payer_id FK uuid matched_claim_id FK uuid matched_by FK uuid created_by FK uuid updated_by FK } pm_era_reconciliation_results { uuid id PK uuid organization_id FK uuid site_id FK uuid era_payment_id FK uuid claim_id FK uuid claim_line_id FK uuid payer_id FK uuid reviewed_by FK uuid created_by FK uuid updated_by FK } pm_fee_schedule_entries { uuid id PK uuid organization_id FK uuid fee_schedule_id FK uuid created_by FK uuid updated_by FK } pm_fee_schedules { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_fhir_access_log { uuid id PK uuid organization_id FK uuid patient_id FK uuid app_registration_id FK uuid consent_id FK } pm_fhir_app_registrations { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_financial_assistance_applications { uuid id PK uuid organization_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_financial_counseling_sessions { uuid id PK uuid organization_id FK uuid patient_id FK uuid counselor_id FK uuid created_by FK uuid updated_by FK } pm_fpl_thresholds { uuid id PK } pm_gfe_appointment_cpt_mapping { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_gfe_disputes { uuid id PK uuid organization_id FK uuid gfe_id FK uuid created_by FK uuid updated_by FK } pm_good_faith_estimates { uuid id PK uuid organization_id FK uuid patient_id FK uuid appointment_id FK uuid created_by FK uuid updated_by FK } pm_group_definitions { uuid id PK uuid organization_id FK uuid facilitator_id FK uuid co_facilitator_id FK uuid location_id FK uuid created_by FK uuid updated_by FK } pm_group_enrollments { uuid id PK uuid organization_id FK uuid group_definition_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_group_schedule { uuid id PK uuid organization_id FK uuid group_definition_id FK uuid facilitator_id FK uuid co_facilitator_id FK uuid location_id FK uuid created_by FK uuid updated_by FK uuid room_id FK } pm_guarantors { uuid id PK uuid organization_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_information_blocking_log { uuid id PK uuid organization_id FK uuid patient_id FK uuid app_registration_id FK uuid reviewed_by FK uuid created_by FK uuid updated_by FK } pm_institutional_claim_details { uuid id PK uuid organization_id FK uuid claim_id FK uuid created_by FK uuid updated_by FK } pm_insurance_policies { uuid id PK uuid organization_id FK uuid patient_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_kiosk_checkins { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid appointment_id FK uuid insurance_card_front_id FK uuid insurance_card_back_id FK uuid created_by FK uuid updated_by FK } pm_managed_care_authorizations { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid payer_id FK uuid prior_auth_id FK uuid created_by FK uuid updated_by FK } pm_module_settings { uuid id PK uuid organization_id FK uuid default_fee_schedule_id FK uuid created_by FK uuid updated_by FK } pm_otp_billing_periods { uuid id PK uuid organization_id FK uuid program_id FK uuid patient_id FK uuid charge_id FK uuid claim_id FK uuid created_by FK uuid updated_by FK } pm_otp_programs { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_otp_service_components { uuid id PK uuid organization_id FK uuid billing_period_id FK uuid created_by FK uuid updated_by FK } pm_patient_access_consents { uuid id PK uuid organization_id FK uuid patient_id FK uuid app_registration_id FK uuid created_by FK uuid updated_by FK } pm_patient_addresses { uuid id PK uuid organization_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_patient_communication_preferences { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_patient_cost_estimates { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid payer_id FK uuid clearance_completed_by FK uuid override_approved_by FK uuid created_by FK uuid updated_by FK } pm_patient_financial_assessments { uuid id PK uuid organization_id FK uuid patient_id FK uuid verified_by FK uuid created_by FK uuid updated_by FK } pm_patient_merge_conflicts { uuid id PK uuid merge_log_id FK uuid organization_id FK } pm_patient_merge_log { uuid id PK uuid organization_id FK uuid performed_by FK uuid undone_by FK } pm_patient_payment_plans { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_patient_site_transfers { uuid id PK uuid organization_id FK uuid patient_id FK uuid from_site_id FK uuid to_site_id FK uuid created_by FK uuid updated_by FK } pm_patient_statements { uuid id PK uuid organization_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK uuid statement_run_id FK } pm_patients { uuid id PK uuid organization_id FK uuid patient_identity_id FK uuid created_by FK uuid updated_by FK } pm_payer_clearinghouse_routes { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_payer_contracts { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_payer_edi_enrollments { uuid id PK uuid organization_id FK uuid site_id FK uuid payer_id FK uuid clearinghouse_id FK uuid created_by FK uuid updated_by FK } pm_payer_enrollments { uuid id PK uuid organization_id FK uuid site_id FK uuid payer_id FK uuid clearinghouse_id FK uuid created_by FK uuid updated_by FK } pm_payer_modifier_rule_versions { uuid id PK uuid organization_id FK uuid rule_id FK uuid created_by FK } pm_payer_modifier_rules { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_payer_performance_snapshots { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_payers { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_payment_applications { uuid id PK uuid organization_id FK uuid payment_id FK uuid claim_id FK uuid claim_line_id FK uuid created_by FK uuid updated_by FK } pm_payment_plan_installments { uuid id PK uuid organization_id FK uuid payment_plan_id FK uuid created_by FK uuid updated_by FK } pm_payment_plans { uuid id PK uuid organization_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_payments { uuid id PK uuid organization_id FK uuid site_id FK uuid payer_id FK uuid patient_id FK uuid era_file_id FK uuid posted_by FK uuid created_by FK uuid updated_by FK } pm_portal_packet_assignments { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_portal_packet_items { uuid id PK uuid organization_id FK uuid assignment_id FK uuid created_by FK uuid updated_by FK } pm_portal_sessions { uuid id PK uuid organization_id FK uuid portal_user_id FK } pm_portal_users { uuid id PK uuid organization_id FK uuid patient_id FK uuid auth_user_id FK uuid proxy_for_patient_id FK uuid created_by FK uuid updated_by FK } pm_preadmission_form_templates { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_preadmission_packets { uuid id PK uuid organization_id FK uuid appointment_id FK uuid patient_id FK uuid packet_template_id FK uuid exception_marked_by FK uuid created_by FK uuid updated_by FK } pm_prior_authorizations { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid payer_id FK uuid facility_id FK uuid requesting_provider_id FK uuid servicing_provider_id FK uuid submitted_by FK uuid created_by FK uuid updated_by FK } pm_program_insurance_revenue_matrix { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_provider_caseloads { uuid id PK uuid organization_id FK uuid provider_id FK uuid created_by FK uuid updated_by FK } pm_provider_enrollment_events { uuid id PK uuid organization_id FK uuid provider_enrollment_id FK uuid created_by FK } pm_provider_enrollments { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_provider_identifier_validations { uuid id PK uuid organization_id FK uuid created_by FK } pm_provider_schedules { uuid id PK uuid organization_id FK uuid provider_id FK uuid location_id FK uuid created_by FK uuid updated_by FK } pm_provider_time_off { uuid id PK uuid organization_id FK uuid provider_id FK uuid coverage_provider_id FK uuid approved_by FK uuid created_by FK uuid updated_by FK } pm_rcm_automation_rules { uuid id PK uuid organization_id FK uuid site_id FK uuid activated_by FK uuid created_by FK uuid updated_by FK } pm_rcm_benchmarks { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_rcm_rule_executions { uuid id PK uuid organization_id FK uuid rule_id FK uuid original_execution_id FK uuid created_by FK } pm_rcm_snapshots { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_rcm_work_queue_items { uuid id PK uuid organization_id FK uuid site_id FK uuid assigned_to FK uuid created_by FK uuid updated_by FK } pm_referral_documents { uuid id PK uuid organization_id FK uuid referral_id FK uuid uploaded_by FK } pm_referral_packet_items { uuid id PK uuid organization_id FK uuid referral_id FK uuid completed_by FK uuid created_by FK uuid updated_by FK } pm_referrals { uuid id PK uuid organization_id FK uuid site_id FK uuid patient_id FK uuid status_changed_by FK uuid created_by FK uuid updated_by FK } pm_resources { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_revenue_code_mappings { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_rooms { uuid id PK uuid organization_id FK uuid site_id FK uuid created_by FK uuid updated_by FK } pm_rpa_bot_configurations { uuid id PK uuid organization_id FK uuid payer_id FK uuid credential_vault_id FK uuid created_by FK uuid updated_by FK } pm_rpa_bot_executions { uuid id PK uuid organization_id FK uuid bot_configuration_id FK uuid created_by FK } pm_rpa_execution_daily_summaries { uuid id PK uuid organization_id FK uuid bot_configuration_id FK } pm_rpa_module_settings { uuid id PK uuid organization_id FK } pm_rpa_staged_documents { uuid id PK uuid organization_id FK uuid execution_id FK uuid processed_by FK } pm_scrub_custom_edits { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_scrub_rule_definitions { uuid id PK } pm_scrub_rule_overrides { uuid id PK uuid organization_id FK text rule_key FK uuid created_by FK uuid updated_by FK uuid payer_id FK } pm_secondary_claims { uuid id PK uuid organization_id FK uuid primary_claim_id FK uuid primary_remittance_id FK uuid secondary_claim_id FK uuid created_by FK uuid updated_by FK } pm_site_billing_config { uuid id PK uuid organization_id FK uuid site_id FK uuid default_fee_schedule_id FK uuid clearinghouse_config_id FK uuid created_by FK uuid updated_by FK } pm_sliding_fee_schedules { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_statement_deliveries { uuid id PK uuid organization_id FK uuid statement_id FK uuid created_by FK uuid updated_by FK } pm_statement_runs { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_superbills { uuid id PK uuid organization_id FK uuid encounter_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_task_audit_log { uuid id PK uuid organization_id FK uuid task_id FK } pm_telehealth_configuration { uuid id PK uuid organization_id FK uuid created_by FK uuid updated_by FK } pm_telehealth_sessions { uuid id PK uuid organization_id FK uuid appointment_id FK uuid encounter_id FK uuid configuration_id FK uuid provider_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_transaction_batches { uuid id PK uuid organization_id FK uuid clearinghouse_id FK uuid created_by FK uuid updated_by FK } pm_transaction_log { uuid id PK uuid organization_id FK uuid batch_id FK uuid claim_id FK uuid patient_id FK uuid created_by FK } pm_vbp_incentive_payments { uuid id PK uuid organization_id FK uuid program_id FK uuid created_by FK uuid updated_by FK } pm_vbp_measures { uuid id PK uuid organization_id FK uuid program_id FK uuid created_by FK uuid updated_by FK } pm_vbp_programs { uuid id PK uuid organization_id FK uuid payer_id FK uuid created_by FK uuid updated_by FK } pm_waitlist { uuid id PK uuid organization_id FK uuid patient_id FK uuid provider_id FK uuid offered_appointment_id FK uuid created_by FK uuid updated_by FK } pm_waitlist_entries { uuid id PK uuid organization_id FK uuid patient_id FK uuid created_by FK uuid updated_by FK } pm_waitlist_outreach_log { uuid id PK uuid waitlist_id FK uuid organization_id FK uuid created_by FK } ``` ## Tables ### `pm_ai_coding_suggestion_audits` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `suggestion_id` | uuid | no | | | | `decision` | text | no | | | | `edit_diff_jsonb` | jsonb | yes | | | | `rejection_reason` | text | yes | | | | `coder_id` | uuid | no | | | | `charge_ids` | jsonb | no | '\[]'::jsonb | Charges produced when decision = accepted/partial (cross-core ref to pm\_charges.id) | | `fairness_flag` | 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:** * `coder_id` → `users(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `suggestion_id` → `pm_ai_coding_suggestions(id)` * `updated_by` → `users(id)` **Policies:** * `pm_ai_coding_audits_insert` (INSERT) * `pm_ai_coding_audits_no_delete` (DELETE) * `pm_ai_coding_audits_no_update` (UPDATE) * `pm_ai_coding_audits_select` (SELECT) ### `pm_ai_coding_suggestions` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `encounter_id` | uuid | no | | Cross-core ref to pm\_encounters.id (PM-07) | | `note_id` | uuid | no | | Cross-core ref to cl\_clinical\_notes.id (CL-04) | | `icd10_codes` | jsonb | no | '\[]'::jsonb | | | `cpt_codes` | jsonb | no | '\[]'::jsonb | | | `hcpcs_codes` | jsonb | no | '\[]'::jsonb | | | `modifiers` | jsonb | no | '\[]'::jsonb | | | `em_level` | text | yes | | | | `confidence_score` | numeric(3,2) | no | | | | `rationale` | text | yes | | | | `model_name` | text | no | | | | `model_version` | text | yes | | | | `prompt_template_id` | text | yes | | | | `redaction_stats` | jsonb | no | '\{}'::jsonb | | | `prompt_token_count` | integer | yes | | | | `completion_token_count` | integer | yes | | | | `estimated_cost_usd` | numeric(8,4) | yes | | | | `jurisdiction_code` | text | yes | | | | `status` | text | no | 'pending'::text | | | `sud_flag` | 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:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_ai_coding_suggestions_insert` (INSERT) * `pm_ai_coding_suggestions_no_delete` (DELETE) * `pm_ai_coding_suggestions_select` (SELECT) * `pm_ai_coding_suggestions_update` (UPDATE) ### `pm_analytics_appointment_confirmed_log` PM-41: Immutable appointment confirmation/no-show/cancel event log **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `correlation_id` | uuid | no | | | | `appointment_id` | uuid | yes | | | | `patient_id` | uuid | yes | | | | `lead_id` | uuid | yes | | | | `confirmation_status` | text | no | | | | `confirmed_at` | timestamp with time zone | no | now() | | | `logged_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_analytics_appt_confirmed_log_insert` (INSERT) * `pm_analytics_appt_confirmed_log_select` (SELECT) ### `pm_analytics_appointment_created_log` PM-41: Immutable appointment creation event log **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------------ | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `correlation_id` | uuid | no | | | | `appointment_id` | uuid | yes | | | | `patient_id` | uuid | yes | | | | `lead_id` | uuid | yes | | | | `provider_id` | uuid | yes | | | | `appointment_type` | text | yes | | | | `match_score` | integer | yes | 0 | | | `match_method` | text | yes | 'manual\_override'::text | | | `scheduled_at` | timestamp with time zone | yes | | | | `logged_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_analytics_appt_created_log_insert` (INSERT) * `pm_analytics_appt_created_log_select` (SELECT) ### `pm_analytics_funnel_daily` PM-41: Pre-aggregated intake funnel metrics per org per day **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `date_day` | date | no | | | | `leads_created` | integer | yes | 0 | | | `leads_screened` | integer | yes | 0 | | | `leads_matched` | integer | yes | 0 | | | `leads_waitlisted` | integer | yes | 0 | | | `leads_appointed` | integer | yes | 0 | | | `leads_kept` | integer | yes | 0 | | | `leads_clinical` | integer | yes | 0 | | | `conversion_rate_pct` | numeric(5,2) | yes | | | | `avg_days_to_appointment` | integer | yes | | | | `no_show_count` | integer | yes | 0 | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_analytics_funnel_daily_insert` (INSERT) * `pm_analytics_funnel_daily_select` (SELECT) * `pm_analytics_funnel_daily_update` (UPDATE) ### `pm_analytics_no_show_risk` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `appointment_id` | uuid | no | | | | `lead_id` | uuid | yes | | | | `risk_score` | integer | no | | | | `risk_factors` | jsonb | yes | '\{}'::jsonb | | | `last_updated` | timestamp with time zone | no | now() | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_analytics_no_show_risk_insert` (INSERT) * `pm_analytics_no_show_risk_select` (SELECT) * `pm_analytics_no_show_risk_update` (UPDATE) ### `pm_analytics_revenue_impact` PM-41-P3: Per-lead revenue impact tracking with drop-off attribution **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `lead_id` | uuid | yes | | | | `program_type` | text | yes | | | | `insurance_type` | text | yes | | | | `lead_value_estimated` | numeric(10,2) | yes | | | | `converted_to_appointment` | boolean | yes | false | | | `appointment_kept` | boolean | yes | false | | | `revenue_realized` | numeric(10,2) | yes | 0 | | | `revenue_lost` | numeric(10,2) | yes | 0 | | | `drop_off_stage` | text | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_revenue_impact_insert` (INSERT) * `pm_revenue_impact_select` (SELECT) * `pm_revenue_impact_update` (UPDATE) ### `pm_analytics_screening_log` PM-41: Immutable screening event log **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `correlation_id` | uuid | no | | | | `lead_id` | uuid | yes | | | | `screening_attempt_id` | uuid | yes | | | | `screening_result_id` | uuid | yes | | | | `disposition` | text | yes | | | | `triage_category` | text | yes | | | | `program_type` | text | yes | | | | `screener_id` | uuid | yes | | | | `screening_date` | timestamp with time zone | yes | | | | `logged_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_analytics_screening_log_insert` (INSERT) * `pm_analytics_screening_log_select` (SELECT) ### `pm_analytics_waitlist_abandon_risk` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `waitlist_entry_id` | uuid | no | | | | `lead_id` | uuid | yes | | | | `risk_score` | integer | no | | | | `risk_factors` | jsonb | yes | '\{}'::jsonb | | | `last_updated` | timestamp with time zone | no | now() | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_analytics_waitlist_abandon_risk_insert` (INSERT) * `pm_analytics_waitlist_abandon_risk_select` (SELECT) * `pm_analytics_waitlist_abandon_risk_update` (UPDATE) ### `pm_analytics_waitlist_log` PM-41: Immutable waitlist entry/exit event log **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `correlation_id` | uuid | no | | | | `waitlist_id` | uuid | yes | | | | `patient_id` | uuid | yes | | | | `lead_id` | uuid | yes | | | | `priority` | text | yes | | | | `entry_date` | timestamp with time zone | yes | | | | `exit_date` | timestamp with time zone | yes | | | | `exit_reason` | text | yes | | | | `wait_duration_hours` | integer | yes | | | | `logged_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_analytics_waitlist_log_insert` (INSERT) * `pm_analytics_waitlist_log_select` (SELECT) * `pm_analytics_waitlist_log_update` (UPDATE) ### `pm_appeal_submissions` PM-29: Appeal submissions against denials with status and outcome tracking **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `denial_id` | uuid | no | | | | `template_id` | uuid | yes | | | | `appeal_level` | integer | no | 1 | | | `letter_content` | text | no | | | | `submitted_at` | timestamp with time zone | yes | | | | `submitted_by` | uuid | yes | | | | `status` | text | no | 'draft'::text | | | `outcome` | text | yes | | | | `outcome_date` | timestamp with time zone | yes | | | | `outcome_amount` | numeric(12,2) | yes | | | | `outcome_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` → `users(id)` * `denial_id` → `pm_claim_denials(id)` * `organization_id` → `pf_organizations(id)` * `submitted_by` → `users(id)` * `template_id` → `pm_appeal_templates(id)` * `updated_by` → `users(id)` **Policies:** * `pm_appeal_submissions_insert` (INSERT) * `pm_appeal_submissions_select` (SELECT) * `pm_appeal_submissions_update` (UPDATE) ### `pm_appeal_templates` PM-29: Reusable appeal letter templates with merge-field support **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `name` | text | no | | | | `denial_category` | text | no | 'other'::text | | | `payer_id` | uuid | yes | | | | `subject_template` | text | yes | | | | `body_template` | text | no | | | | `merge_fields` | 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 | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `users(id)` **Policies:** * `pm_appeal_templates_insert` (INSERT) * `pm_appeal_templates_select` (SELECT) * `pm_appeal_templates_update` (UPDATE) ### `pm_appointment_match_log` PM-38: Immutable audit log of automated provider matching decisions for intake appointments **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | --------------------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `screening_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `appointment_id` | uuid | yes | | | | `matching_criteria_applied` | jsonb | no | | JSON snapshot of matching criteria used (specialty, insurance, availability window) | | `candidates_found` | integer | no | | Number of eligible providers found during matching | | `selected_provider_id` | uuid | yes | | | | `selection_reason` | text | no | | Human-readable explanation of why this provider was selected | | `coordinator_override` | boolean | no | false | True if a coordinator overrode the automated match | | `coordinator_override_from` | uuid | yes | | | | `coordinator_override_to` | uuid | yes | | | | `override_reason` | text | yes | | Required reason when coordinator overrides automated match | | `appointment_created_at` | timestamp with time zone | no | | Timestamp when the appointment was created from this match | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | no | | | **Foreign keys:** * `appointment_id` → `pm_appointments(id)` * `coordinator_override_from` → `hr_employees(id)` * `coordinator_override_to` → `hr_employees(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `screening_id` → `ce_screening_attempts(id)` * `selected_provider_id` → `hr_employees(id)` **Policies:** * `pm_appointment_match_log_insert` (INSERT) * `pm_appointment_match_log_select` (SELECT) ### `pm_appointment_reminders` PM-03: Reminder records. PF-10 delivery deferred to Phase 2. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `appointment_id` | uuid | no | | | | `reminder_type` | text | no | | | | `scheduled_at` | timestamp with time zone | no | | | | `sent_at` | timestamp with time zone | yes | | | | `delivery_status` | text | no | 'pending'::text | | | `channel` | 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:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_appointment_reminders_insert` (INSERT) * `pm_appointment_reminders_select` (SELECT) * `pm_appointment_reminders_update` (UPDATE) ### `pm_appointments` PM-03: Core appointment entity. PHI-adjacent; FORCE RLS enforced. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `provider_id` | uuid | yes | | | | `appointment_type` | text | no | | Backed by PF-15 picklist pm\_appointment\_types. No DB CHECK — validated at application layer. | | `service_code` | text | yes | | | | `location_id` | uuid | yes | | | | `room_id` | uuid | yes | | | | `start_datetime` | timestamp with time zone | no | | | | `end_datetime` | timestamp with time zone | no | | | | `duration_minutes` | integer | no | 60 | | | `status` | text | no | 'scheduled'::text | | | `is_telehealth` | boolean | no | false | | | `telehealth_url` | text | yes | | Phase 1: manual entry only, nullable. Phase 2 (PM-13) adds on-demand generation + expiry + access logging. | | `is_recurring` | boolean | no | false | | | `recurrence_rule` | jsonb | yes | | iCal RRULE (RFC 5545) JSONB. \{"rrule":"FREQ=WEEKLY;BYDAY=MO","dtstart":"2026-03-01T09:00:00Z"} | | `series_id` | uuid | yes | | | | `reminder_sent` | boolean | no | false | | | `checked_in_at` | timestamp with time zone | yes | | | | `no_show_reason` | text | yes | | | | `cancellation_reason` | text | yes | | | | `notes` | 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)` * `location_id` → `pf_sites(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `provider_id` → `pf_profiles(id)` * `room_id` → `pm_rooms(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_appointments_delete` (DELETE) * `pm_appointments_insert` (INSERT) * `pm_appointments_select` (SELECT) * `pm_appointments_update` (UPDATE) ### `pm_assistance_program_referrals` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `program_type` | text | no | | | | `referral_date` | date | no | | | | `application_status` | text | no | 'submitted'::text | | | `application_submitted_at` | timestamp with time zone | yes | | | | `outcome_at` | timestamp with time zone | yes | | | | `policy_id` | uuid | 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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `policy_id` → `pm_insurance_policies(id)` * `updated_by` → `users(id)` **Policies:** * `pm_assistance_program_referrals_delete` (DELETE) * `pm_assistance_program_referrals_insert` (INSERT) * `pm_assistance_program_referrals_select` (SELECT) * `pm_assistance_program_referrals_update` (UPDATE) ### `pm_audit_log` PM-26: Append-only audit log for billing entity changes (claims, charges, payments). Supports compliance reporting and payer audit response. **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `table_name` | text | no | | | | `record_id` | uuid | no | | | | `action` | text | no | | | | `old_values` | jsonb | yes | | | | `new_values` | jsonb | yes | | | | `changed_by` | uuid | yes | | | | `changed_at` | timestamp with time zone | no | now() | | | `ip_address` | text | yes | | PII — subject to 7-year retention policy per C-2. | | `user_agent` | text | yes | | PII — subject to 7-year retention policy per C-2. | **Foreign keys:** * `changed_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_audit_log_select` (SELECT) ### `pm_auth_concurrent_reviews` PM-47: Concurrent review submissions and outcomes for managed care authorizations **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `authorization_id` | uuid | no | | | | `review_due_date` | date | no | | | | `submitted_date` | date | yes | | | | `reviewer_id` | uuid | yes | | | | `outcome` | text | yes | | | | `extended_expiration_date` | date | yes | | | | `additional_units_granted` | integer | yes | | | | `clinical_doc_reference` | uuid | yes | | UUID reference to clinical documentation — no FK to CL tables | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for review tracking | | `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:** * `authorization_id` → `pm_managed_care_authorizations(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `reviewer_id` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_acr_insert` (INSERT) * `pm_acr_select` (SELECT) * `pm_acr_update` (UPDATE) ### `pm_auth_denial_appeals` PM-47: Auth denial appeal lifecycle tracking with justification and outcome **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `authorization_id` | uuid | no | | | | `denial_reason` | text | no | | | | `appeal_justification` | text | no | | | | `appeal_deadline` | date | yes | | | | `submitted_date` | date | yes | | | | `status` | text | yes | 'draft'::text | | | `outcome_date` | date | yes | | | | `outcome_notes` | text | yes | | | | `clinical_doc_references` | jsonb | yes | '\[]'::jsonb | JSON array of UUID references to clinical documents supporting the appeal | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for appeal tracking | | `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:** * `authorization_id` → `pm_managed_care_authorizations(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_ada_insert` (INSERT) * `pm_ada_select` (SELECT) * `pm_ada_update` (UPDATE) ### `pm_auth_reviews` PM-10: Concurrent and retrospective reviews for prior authorizations **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `authorization_id` | uuid | no | | | | `review_type` | text | no | | | | `review_date` | date | no | CURRENT\_DATE | | | `reviewer_name` | text | yes | | | | `outcome` | text | yes | | | | `notes` | text | yes | | | | `next_review_date` | date | 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:** * `authorization_id` → `pm_prior_authorizations(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_auth_reviews_delete` (DELETE) * `pm_auth_reviews_insert` (INSERT) * `pm_auth_reviews_select` (SELECT) * `pm_auth_reviews_update` (UPDATE) ### `pm_bad_debt_writeoffs` PM-45: Bad debt write-off requests with approval workflow **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | --------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `payment_plan_id` | uuid | yes | | | | `writeoff_amount` | numeric(12,2) | no | | | | `aging_days` | integer | no | | | | `collection_attempts_count` | integer | no | 0 | | | `reason_code` | text | no | | | | `reason_notes` | text | yes | | | | `status` | text | no | 'pending'::text | | | `requested_by` | uuid | yes | | | | `approved_by` | uuid | yes | | | | `approval_date` | timestamp with time zone | yes | | | | `approval_notes` | text | yes | | | | `financial_adjustment_id` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | **Foreign keys:** * `approved_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payment_plan_id` → `pm_patient_payment_plans(id)` * `requested_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_writeoffs_insert` (INSERT) * `pm_writeoffs_select` (SELECT) * `pm_writeoffs_update` (UPDATE) ### `pm_batch_jobs` PM-24: Batch job audit and progress tracking for claim generation, submission, eligibility, and charge approval. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `job_type` | text | no | | | | `status` | text | no | 'pending'::text | | | `total_count` | integer | no | 0 | | | `processed_count` | integer | no | 0 | | | `error_count` | integer | no | 0 | | | `errors` | jsonb | no | '\[]'::jsonb | Per-item errors: \[\{ "id": "\", "message": "..." }]. Must be sanitized before persisting — no PHI. | | `started_at` | timestamp with time zone | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `created_by` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for batch jobs. | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_batch_jobs_insert` (INSERT) * `pm_batch_jobs_select` (SELECT) * `pm_batch_jobs_update` (UPDATE) ### `pm_benefit_details` **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 | no | | | | `eligibility_check_id` | uuid | no | | | | `service_type_code` | text | no | | | | `service_type_label` | text | yes | | | | `network_tier` | text | no | 'in\_network'::text | | | `copay_amount` | numeric(10,2) | yes | | | | `coinsurance_pct` | numeric(5,2) | yes | | | | `deductible_individual` | numeric(10,2) | yes | | | | `deductible_individual_remaining` | numeric(10,2) | yes | | | | `deductible_family` | numeric(10,2) | yes | | | | `deductible_family_remaining` | numeric(10,2) | yes | | | | `oop_max_individual` | numeric(10,2) | yes | | | | `oop_max_individual_remaining` | numeric(10,2) | yes | | | | `oop_max_family` | numeric(10,2) | yes | | | | `oop_max_family_remaining` | numeric(10,2) | yes | | | | `visit_limit_annual` | integer | yes | | | | `visit_limit_lifetime` | integer | yes | | | | `visits_used_annual` | integer | yes | | | | `visits_used_lifetime` | integer | yes | | | | `auth_required` | boolean | no | false | | | `auth_certification_number` | text | yes | | | | `effective_date` | date | yes | | | | `term_date` | date | yes | | | | `parsing_confidence` | text | no | 'low'::text | | | `raw_eb_segments` | jsonb | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `eligibility_check_id` → `pm_eligibility_checks(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_benefit_details_delete` (DELETE) * `pm_benefit_details_insert` (INSERT) * `pm_benefit_details_select` (SELECT) * `pm_benefit_details_update` (UPDATE) ### `pm_benefit_estimates` **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 | no | | | | `eligibility_check_id` | uuid | no | | | | `appointment_id` | uuid | yes | | | | `service_type_code` | text | no | | | | `cpt_code` | text | yes | | | | `fee_schedule_amount` | numeric(10,2) | yes | | | | `estimated_copay` | numeric(10,2) | yes | | | | `estimated_coinsurance` | numeric(10,2) | yes | | | | `estimated_deductible_applied` | numeric(10,2) | yes | | | | `estimated_total_patient_responsibility` | numeric(10,2) | yes | | | | `confidence` | text | no | 'low'::text | | | `calculation_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 | | | **Foreign keys:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `pf_profiles(id)` * `eligibility_check_id` → `pm_eligibility_checks(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_benefit_estimates_delete` (DELETE) * `pm_benefit_estimates_insert` (INSERT) * `pm_benefit_estimates_select` (SELECT) * `pm_benefit_estimates_update` (UPDATE) ### `pm_billing_suggestion_rules` CL-41: Payer-specific billing suggestion override rules **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `rule_type` | text | no | | | | `source_cpt_code` | text | no | | | | `target_cpt_code` | text | yes | | | | `modifier` | text | yes | | | | `modifier_action` | text | yes | | | | `replace_with_modifier` | text | yes | | | | `condition_config` | jsonb | no | '\{}'::jsonb | | | `priority` | integer | no | 0 | | | `is_active` | boolean | no | true | | | `description` | text | yes | | | | `effective_date` | date | yes | | | | `end_date` | date | yes | | | | `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)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_billing_suggestion_rules_delete` (DELETE) * `pm_billing_suggestion_rules_insert` (INSERT) * `pm_billing_suggestion_rules_select` (SELECT) * `pm_billing_suggestion_rules_update` (UPDATE) ### `pm_capitation_contracts` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `contract_name` | text | no | | | | `pmpm_rate` | numeric(12,4) | no | | | | `service_category` | text | yes | | | | `effective_from` | date | no | | | | `effective_to` | date | yes | | | | `roster_source` | text | no | | | | `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)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_capitation_contracts_delete` (DELETE) * `pm_capitation_contracts_insert` (INSERT) * `pm_capitation_contracts_select` (SELECT) * `pm_capitation_contracts_update` (UPDATE) ### `pm_capitation_periods` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `contract_id` | uuid | no | | | | `period_start_date` | date | no | | | | `period_end_date` | date | no | | | | `enrolled_member_count` | integer | no | | | | `expected_revenue` | numeric(12,2) | no | | | | `received_revenue` | numeric(12,2) | yes | | | | `status` | text | 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:** * `contract_id` → `pm_capitation_contracts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_capitation_periods_delete` (DELETE) * `pm_capitation_periods_insert` (INSERT) * `pm_capitation_periods_select` (SELECT) * `pm_capitation_periods_update` (UPDATE) ### `pm_capitation_reconciliations` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `period_id` | uuid | no | | | | `expected_amount` | numeric(12,2) | no | | | | `received_amount` | numeric(12,2) | no | | | | `variance_amount` | numeric(12,2) | yes | | | | `variance_disposition` | text | yes | | | | `reconciled_at` | timestamp with time zone | yes | | | | `reconciled_by` | uuid | 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)` * `organization_id` → `pf_organizations(id)` * `period_id` → `pm_capitation_periods(id)` * `reconciled_by` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_capitation_reconciliations_delete` (DELETE) * `pm_capitation_reconciliations_insert` (INSERT) * `pm_capitation_reconciliations_select` (SELECT) * `pm_capitation_reconciliations_update` (UPDATE) ### `pm_capitation_roster_events` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `contract_id` | uuid | no | | | | `patient_id` | uuid | yes | | | | `member_id_external` | text | yes | | | | `event_type` | text | no | | | | `effective_date` | date | no | | | | `source` | text | no | | | | `transaction_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 | | | **Foreign keys:** * `contract_id` → `pm_capitation_contracts(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_capitation_roster_events_delete` (DELETE) * `pm_capitation_roster_events_insert` (INSERT) * `pm_capitation_roster_events_select` (SELECT) * `pm_capitation_roster_events_update` (UPDATE) ### `pm_carc_codes` PM-29: Claim Adjustment Reason Codes (CARC) **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ------------- | ------------------------ | -------- | ------- | ------- | | `code` | text | no | | | | `description` | text | no | | | | `category` | text | yes | | | | `is_active` | boolean | no | true | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Policies:** * `pm_carc_codes_select` (SELECT) ### `pm_charge_recon_daily_summary` PM-36: One row per org per nightly reconciliation run. Feeds PM-11 dashboard KPI cards and trend charts. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | --------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `run_date` | date | no | | | | `encounter_no_charge_count` | integer | no | 0 | | | `charge_no_claim_count` | integer | no | 0 | | | `claim_no_payment_count` | integer | no | 0 | | | `duplicate_charge_count` | integer | no | 0 | | | `zero_amount_charge_count` | integer | no | 0 | | | `total_gap_count` | integer | no | 0 | | | `avg_days_outstanding` | numeric(6,1) | yes | | | | `avg_resolve_time_hours` | numeric(8,1) | 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:** * `pm_recon_summary_insert` (INSERT) * `pm_recon_summary_select` (SELECT) * `pm_recon_summary_update` (UPDATE) ### `pm_charge_reconciliation_results` PM-36: Stores individual charge reconciliation gap results detected by nightly edge function. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `run_date` | date | no | | | | `gap_type` | text | no | | | | `entity_id` | uuid | no | | | | `entity_type` | text | no | | | | `patient_id` | uuid | yes | | | | `provider_id` | uuid | yes | | Denormalized rendering provider UUID from source encounter or charge. No FK — populated by edge function for display convenience. | | `days_outstanding` | integer | yes | | | | `amount` | numeric(12,2) | yes | | | | `task_id` | uuid | yes | | | | `resolved_at` | timestamp with time zone | yes | | | | `resolved_by` | uuid | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"external\_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:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `resolved_by` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `task_id` → `pf_tasks(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_charge_recon_insert` (INSERT) * `pm_charge_recon_select` (SELECT) * `pm_charge_recon_update` (UPDATE) ### `pm_charges` PM-07: Billable charges captured from clinical 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 | yes | | | | `cpt_code` | text | no | | | | `hcpcs_code` | text | yes | | | | `modifiers` | text\[] | no | '\{}'::text\[] | Billing modifiers (e.g. HQ, 95, FQ) | | `units` | numeric(10,2) | no | 1 | | | `diagnosis_codes` | text\[] | no | '\{}'::text\[] | ICD-10-CM codes, primary first | | `place_of_service` | text | no | '11'::text | | | `service_date` | date | no | | | | `provider_id` | uuid | no | | | | `rendering_npi` | text | yes | | | | `fee_schedule_id` | uuid | yes | | | | `charge_amount` | numeric(12,2) | no | 0 | | | `status` | text | no | 'pending'::text | | | `reviewed_by` | uuid | yes | | | | `reviewed_at` | timestamp with time zone | yes | | | | `approved_by` | uuid | yes | | | | `approved_at` | timestamp with time zone | yes | | | | `claim_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 | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `approved_by` → `users(id)` * `created_by` → `users(id)` * `encounter_id` → `pm_encounters(id)` * `fee_schedule_id` → `pm_fee_schedules(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `provider_id` → `pf_profiles(id)` * `reviewed_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `pm_charges_delete` (DELETE) * `pm_charges_insert` (INSERT) * `pm_charges_select` (SELECT) * `pm_charges_update` (UPDATE) ### `pm_charity_care_applications` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `application_date` | date | no | | | | `policy_tier` | text | no | | | | `fpl_percentage` | numeric(5,2) | yes | | | | `approved_at` | timestamp with time zone | yes | | | | `approved_by` | uuid | yes | | | | `expiration_date` | date | yes | | | | `documentation_refs` | 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 | | | **Foreign keys:** * `approved_by` → `pf_profiles(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `users(id)` **Policies:** * `pm_charity_care_applications_delete` (DELETE) * `pm_charity_care_applications_insert` (INSERT) * `pm_charity_care_applications_select` (SELECT) * `pm_charity_care_applications_update` (UPDATE) ### `pm_checkin_discrepancies` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `appointment_id` | uuid | no | | | | `eligibility_check_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `discrepancy_type` | text | no | | | | `previous_value` | jsonb | yes | | | | `current_value` | jsonb | yes | | | | `resolution_status` | text | no | 'unresolved'::text | | | `resolution_action` | text | yes | | | | `resolution_notes` | text | yes | | | | `resolved_by` | uuid | yes | | | | `resolved_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:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `users(id)` * `eligibility_check_id` → `pm_eligibility_checks(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `resolved_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `pm_checkin_discrepancies_delete` (DELETE) * `pm_checkin_discrepancies_insert` (INSERT) * `pm_checkin_discrepancies_select` (SELECT) * `pm_checkin_discrepancies_update` (UPDATE) ### `pm_claim_denials` PM-29: Tracks payer denials with appeal deadlines and resolution status **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `claim_id` | uuid | no | | | | `claim_line_id` | uuid | yes | | | | `payer_id` | uuid | yes | | | | `prior_authorization_id` | uuid | yes | | | | `denial_date` | timestamp with time zone | no | now() | | | `denial_category` | text | no | 'other'::text | | | `carc_code` | text | yes | | | | `rarc_code` | text | yes | | | | `denial_reason` | text | yes | | | | `denied_amount` | numeric(12,2) | yes | | | | `remittance_id` | uuid | yes | | | | `payer_appeal_deadline` | timestamp with time zone | yes | | | | `assigned_to` | uuid | yes | | | | `status` | text | no | 'new'::text | | | `resolution` | text | yes | | | | `resolved_at` | timestamp with time zone | yes | | | | `resolved_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 | | Soft delete timestamp. NULL = active, non-NULL = soft deleted. | **Foreign keys:** * `assigned_to` → `users(id)` * `carc_code` → `pm_carc_codes(code)` * `claim_id` → `pm_claims(id)` * `claim_line_id` → `pm_claim_lines(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `rarc_code` → `pm_rarc_codes(code)` * `resolved_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `pm_claim_denials_insert` (INSERT) * `pm_claim_denials_select` (SELECT) * `pm_claim_denials_update` (UPDATE) ### `pm_claim_lines` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `claim_id` | uuid | no | | | | `charge_id` | uuid | yes | | | | `line_number` | integer | no | | | | `service_date` | date | no | | | | `cpt_code` | text | no | | | | `modifiers` | text\[] | no | '\{}'::text\[] | | | `units` | numeric(8,2) | no | 1 | | | `diagnosis_pointers` | jsonb | no | '\[]'::jsonb | | | `charge_amount` | numeric(12,2) | no | 0 | | | `paid_amount` | numeric(12,2) | yes | | | | `adjustment_amount` | numeric(12,2) | yes | | | | `adjustment_reason` | jsonb | no | '\[]'::jsonb | | | `denial_reason` | text | yes | | | | `rendering_provider_npi` | text | yes | | | | `place_of_service` | text | yes | | | | `revenue_code` | 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:** * `charge_id` → `pm_charges(id)` * `claim_id` → `pm_claims(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_claim_lines_delete` (DELETE) * `pm_claim_lines_insert` (INSERT) * `pm_claim_lines_select` (SELECT) * `pm_claim_lines_update` (UPDATE) ### `pm_claim_notes` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `claim_id` | uuid | no | | | | `note_type` | text | no | | | | `note_text` | text | no | | | | `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:** * `claim_id` → `pm_claims(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_claim_notes_delete` (DELETE) * `pm_claim_notes_insert` (INSERT) * `pm_claim_notes_select` (SELECT) * `pm_claim_notes_update` (UPDATE) ### `pm_claims` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `claim_number` | text | no | | | | `claim_type` | text | no | | | | `payer_id` | uuid | no | | | | `policy_id` | uuid | yes | | | | `billing_provider_npi` | text | no | | | | `rendering_provider_npi` | text | yes | | | | `facility_id` | uuid | yes | | | | `service_date_from` | date | no | | | | `service_date_to` | date | no | | | | `primary_diagnosis_code` | text | yes | | | | `total_charge` | numeric(12,2) | no | 0 | | | `status` | text | no | 'draft'::text | | | `submitted_at` | timestamp with time zone | yes | | | | `adjudicated_at` | timestamp with time zone | yes | | | | `paid_amount` | numeric(12,2) | yes | | | | `adjustment_amount` | numeric(12,2) | yes | | | | `patient_responsibility` | numeric(12,2) | yes | | | | `denial_codes` | jsonb | no | '\[]'::jsonb | | | `frequency_code` | text | yes | | | | `original_claim_id` | uuid | yes | | | | `filing_deadline` | date | no | | | | `clearinghouse_trace_id` | 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 | | | | `cob_sequence` | integer | yes | 1 | | | `frequency_type_code` | text | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `facility_id` → `pf_sites(id)` * `organization_id` → `pf_organizations(id)` * `original_claim_id` → `pm_claims(id)` * `patient_id` → `pm_patients(id)` * `payer_id` → `pm_payers(id)` * `policy_id` → `pm_insurance_policies(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_claims_delete` (DELETE) * `pm_claims_insert` (INSERT) * `pm_claims_select` (SELECT) * `pm_claims_update` (UPDATE) ### `pm_clearinghouse_config` PM-15: Clearinghouse connection configurations per organization. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `clearinghouse_provider` | text | no | | | | `clearinghouse_name` | text | no | | | | `sender_id` | text | no | | | | `receiver_id` | text | no | | | | `connection_type` | text | no | | | | `host` | text | yes | | | | `port` | integer | yes | | | | `username_vault_ref` | text | yes | | Reference to vault-stored username. Never store credentials directly. | | `credentials_vault_ref` | text | yes | | Reference to vault-stored password/key. Never store credentials directly. | | `submission_path` | text | yes | | | | `retrieval_path` | text | yes | | | | `api_endpoint` | text | yes | | | | `is_active` | boolean | yes | true | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"trading\_partner\_id": "TP-123"}) | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active. | | `health_status` | text | no | 'unknown'::text | Connection health: healthy, degraded, unhealthy, unknown | | `last_health_check_at` | timestamp with time zone | yes | | Timestamp of last health/connectivity check | | `submission_schedule` | text | yes | | Cron expression for automated batch submission | | `retrieval_schedule` | text | yes | | Cron expression for automated retrieval polling | | `secondary_vendor` | text | yes | | PM-15-P2-EN-01: Optional secondary clearinghouse vendor used for failover when primary degrades. | | `failover_enabled` | boolean | no | false | PM-15-P2-EN-01: Whether automatic failover to secondary\_vendor is enabled. | | `failover_threshold_minutes` | integer | no | 30 | PM-15-P2-EN-01: Minutes of sustained degradation before routing new transactions to secondary\_vendor (1-240). | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_clearinghouse_config_delete` (DELETE) * `pm_clearinghouse_config_insert` (INSERT) * `pm_clearinghouse_config_select` (SELECT) * `pm_clearinghouse_config_update` (UPDATE) ### `pm_code_modifier_rules` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `cpt_code` | text | no | | | | `modifier` | text | no | | | | `rule_type` | text | no | 'auto\_apply'::text | | | `description` | text | yes | | | | `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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_code_modifier_rules_delete` (DELETE) * `pm_code_modifier_rules_insert` (INSERT) * `pm_code_modifier_rules_select` (SELECT) * `pm_code_modifier_rules_update` (UPDATE) ### `pm_collection_actions` PM-45: Append-only audit trail of all collections actions **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `payment_plan_id` | uuid | yes | | | | `aging_days` | integer | no | | | | `aging_tier` | text | no | | | | `action_type` | text | no | | | | `action_status` | text | no | 'completed'::text | | | `communication_id` | uuid | yes | | | | `notes` | text | yes | | | | `performed_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `payment_plan_id` → `pm_patient_payment_plans(id)` * `performed_by` → `pf_profiles(id)` **Policies:** * `pm_collection_actions_delete` (DELETE) * `pm_collection_actions_insert` (INSERT) * `pm_collection_actions_select` (SELECT) ### `pm_collections_external_referrals` PM-45: External collections agency referral tracking **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `payment_plan_id` | uuid | yes | | | | `referral_amount` | numeric(12,2) | no | | | | `agency_name` | text | no | | | | `referral_date` | date | no | | | | `status` | text | no | 'referred'::text | | | `settlement_amount` | numeric(12,2) | yes | | | | `settlement_date` | date | yes | | | | `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 | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payment_plan_id` → `pm_patient_payment_plans(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_ext_referrals_insert` (INSERT) * `pm_ext_referrals_select` (SELECT) * `pm_ext_referrals_update` (UPDATE) ### `pm_contract_modeling_scenarios` PM-UX-19: Persisted payer contract modeling simulation scenarios with assumptions, rate adjustments, and forecast outputs. Tenant-scoped; no PHI. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `scenario_name` | text | no | | | | `version_notes` | text | yes | | | | `share_with_team` | boolean | no | false | | | `contract_id` | uuid | yes | | | | `contract_label` | text | yes | | | | `analysis_horizon` | text | no | 'fy'::text | | | `baseline_start_date` | date | yes | | | | `baseline_end_date` | date | yes | | | | `volume_growth_percent` | numeric(7,2) | yes | | | | `assumption_notes` | text | yes | | | | `baseline_revenue` | numeric(14,2) | yes | | | | `projected_revenue` | numeric(14,2) | yes | | | | `variance_dollars` | numeric(14,2) | yes | | | | `variance_percent` | numeric(7,2) | yes | | | | `forecast_notes` | text | yes | | | | `rate_adjustments` | jsonb | no | '\[]'::jsonb | Array of code-level rate adjustment entries (cptCode, currentRate, proposedRateChangePercent, effectiveDateStart/End). | | `custom_fields` | jsonb | no | '\{}'::jsonb | Org-specific extension metadata for scenario records. | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `parent_scenario_id` | uuid | yes | | PM-43: Self-reference to the original (root) scenario this row is a version of. NULL for the root version. | | `version_number` | integer | no | 1 | PM-43: Sequential version number within a scenario lineage (root = 1). | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `parent_scenario_id` → `pm_contract_modeling_scenarios(id)` **Policies:** * `pm_contract_modeling_scenarios_delete` (DELETE) * `pm_contract_modeling_scenarios_insert` (INSERT) * `pm_contract_modeling_scenarios_select` (SELECT) * `pm_contract_modeling_scenarios_update` (UPDATE) ### `pm_contracted_rates` PM-23: Contracted rates per contract and CPT (with optional modifiers) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------------- | ------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `contract_id` | uuid | no | | | | `cpt_code` | text | no | | | | `modifiers` | text\[] | yes | '\{}'::text\[] | Modifier set; exact set-equality match for lookup, else fallback to contract+CPT+date | | `rate_amount` | numeric(12,2) | no | | | | `rate_type` | text | no | 'fee\_for\_service'::text | | | `effective_date` | date | no | | | | `end_date` | 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 | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `contract_id` → `pm_payer_contracts(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_contracted_rates_delete` (DELETE) * `pm_contracted_rates_insert` (INSERT) * `pm_contracted_rates_select` (SELECT) * `pm_contracted_rates_update` (UPDATE) ### `pm_coordinator_tasks` PM-42: Intake coordinator task queue for appointment confirmation, waitlist calls, packet reminders **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `task_type` | text | no | | | | `related_id` | uuid | no | | | | `lead_id` | uuid | yes | | Nullable; PM-sourced tasks link to patient\_id not lead\_id. Populated via join if lead mapping exists. | | `description` | text | no | | | | `priority_score` | integer | no | 0 | | | `due_date` | date | no | | | | `status` | text | no | 'open'::text | | | `assigned_coordinator_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 | | | | `completed_at` | timestamp with time zone | yes | | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_coordinator_tasks_insert` (INSERT) * `pm_coordinator_tasks_select` (SELECT) * `pm_coordinator_tasks_update` (UPDATE) ### `pm_cost_categories` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `name` | text | no | | | | `category_type` | text | no | | | | `description` | text | yes | | | | `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)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_cost_categories_insert` (INSERT) * `pm_cost_categories_select` (SELECT) * `pm_cost_categories_update` (UPDATE) ### `pm_cost_estimate_line_items` PM-48: Per-service line items for patient cost estimates **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `estimate_id` | uuid | no | | | | `cpt_code` | text | no | | | | `service_description` | text | no | | | | `units` | integer | no | 1 | | | `rate` | numeric(10,2) | no | | | | `subtotal` | numeric(12,2) | no | | | | `rate_source` | text | no | | | | `sort_order` | integer | no | 0 | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `estimate_id, organization_id` → `pm_patient_cost_estimates(id, organization_id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_estimate_lines_delete` (DELETE) * `pm_estimate_lines_insert` (INSERT) * `pm_estimate_lines_select` (SELECT) * `pm_estimate_lines_update` (UPDATE) ### `pm_cost_rates` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `cost_category_id` | uuid | no | | | | `rate_amount` | numeric(12,4) | no | | | | `rate_unit` | text | no | | | | `provider_id` | uuid | yes | | | | `effective_from` | date | no | | | | `effective_to` | 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 | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `cost_category_id` → `pm_cost_categories(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_cost_rates_insert` (INSERT) * `pm_cost_rates_select` (SELECT) * `pm_cost_rates_update` (UPDATE) ### `pm_denial_models` PM-50: ML model registry for denial prediction. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `model_version` | text | no | | | | `status` | text | no | 'training'::text | | | `training_started_at` | timestamp with time zone | no | now() | | | `training_completed_at` | timestamp with time zone | yes | | | | `training_record_count` | integer | yes | | | | `precision_score` | numeric(5,4) | yes | | | | `recall_score` | numeric(5,4) | yes | | | | `f1_score` | numeric(5,4) | yes | | | | `false_positive_rate` | numeric(5,4) | yes | | | | `storage_path` | text | yes | | | | `promoted_at` | timestamp with time zone | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific model configuration 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:** * `pm_denial_models_delete` (DELETE) * `pm_denial_models_insert` (INSERT) * `pm_denial_models_select` (SELECT) * `pm_denial_models_update` (UPDATE) ### `pm_denial_predictions` PM-50: ML-based denial risk predictions for claims. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `claim_id` | uuid | yes | | References pm\_claims(id) — FK deferred until PM-08 deployment. | | `risk_score` | integer | no | | | | `risk_level` | text | no | | | | `predicted_denial_reasons` | jsonb | no | '\[]'::jsonb | | | `suggested_actions` | jsonb | no | '\[]'::jsonb | | | `documentation_completeness_score` | numeric(5,2) | yes | | | | `model_version` | text | no | | | | `prediction_date` | timestamp with time zone | no | now() | | | `actual_outcome` | text | yes | | | | `actual_denial_reason` | text | yes | | | | `biller_action` | text | yes | | | | `biller_action_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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_denial_predictions_delete` (DELETE) * `pm_denial_predictions_insert` (INSERT) * `pm_denial_predictions_select` (SELECT) * `pm_denial_predictions_update` (UPDATE) ### `pm_edi_enrollment_status_history` PM-46: Append-only audit trail of EDI enrollment status changes **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `enrollment_id` | uuid | no | | | | `previous_status` | text | no | | | | `new_status` | text | no | | | | `changed_by` | uuid | yes | | | | `changed_at` | timestamp with time zone | no | now() | | | `notes` | text | yes | | | **Foreign keys:** * `changed_by` → `pf_profiles(id)` * `enrollment_id` → `pm_payer_edi_enrollments(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_edi_status_history_delete` (DELETE) * `pm_edi_status_history_insert` (INSERT) * `pm_edi_status_history_select` (SELECT) * `pm_edi_status_history_update` (UPDATE) ### `pm_eligibility_checks` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `policy_id` | uuid | yes | | | | `check_type` | text | no | | | | `check_datetime` | timestamp with time zone | no | now() | | | `request_trace_id` | text | yes | | | | `response_status` | text | yes | | | | `eligible` | boolean | yes | | | | `benefit_details` | jsonb | yes | '\{}'::jsonb | | | `next_check_due` | timestamp with time zone | yes | | | | `checked_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `trigger_context` | text | yes | 'manual'::text | | | `appointment_id` | uuid | yes | | | | `is_stale` | boolean | no | false | | **Foreign keys:** * `appointment_id` → `pm_appointments(id)` * `checked_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `policy_id` → `pm_insurance_policies(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_eligibility_checks_insert` (INSERT) * `pm_eligibility_checks_select` (SELECT) * `pm_eligibility_checks_update` (UPDATE) ### `pm_emergency_contacts` PM-01: Patient emergency contacts. Soft-delete for audit. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `name` | text | no | | | | `relationship` | text | no | | | | `phone` | text | no | | | | `is_primary` | boolean | yes | false | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_emergency_contacts_delete` (DELETE) * `pm_emergency_contacts_insert` (INSERT) * `pm_emergency_contacts_select` (SELECT) * `pm_emergency_contacts_update` (UPDATE) ### `pm_encounter_costs` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------------------ | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `encounter_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `provider_id` | uuid | yes | | | | `site_id` | uuid | yes | | | | `service_date` | date | no | | | | `service_line` | text | yes | | | | `total_cost` | numeric(12,4) | no | 0 | | | `cost_breakdown` | jsonb | no | '\{}'::jsonb | | | `calculation_method` | text | no | | | | `status` | text | no | 'draft'::text | | | `charge_amount` | numeric(12,2) | yes | | | | `margin` | numeric(12,4) | yes | (charge\_amount - total\_cost) | | | `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)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_encounter_costs_insert` (INSERT) * `pm_encounter_costs_select` (SELECT) * `pm_encounter_costs_update` (UPDATE) ### `pm_encounters` PM-03 (owned): Links appointments to clinical docs and charges. Central for PM-07. **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 | yes | | | | `encounter_date` | date | no | | | | `encounter_type` | text | no | 'outpatient'::text | | | `status` | text | no | 'scheduled'::text | | | `rendering_provider_id` | uuid | yes | | | | `supervising_provider_id` | uuid | yes | | | | `service_location_id` | uuid | yes | | | | `place_of_service` | text | no | '11'::text | | | `clinical_note_id` | uuid | yes | | Set by CL-04 via cl\_pm\_events when note finalized. | | `note_finalized_at` | timestamp with time zone | yes | | | | `charge_id` | uuid | yes | | Set by PM-07 (Charge Capture). | | `charge_captured_at` | timestamp with time zone | yes | | | | `claim_id` | uuid | yes | | Set by PM-08 (Claims Management). | | `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:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `rendering_provider_id` → `pf_profiles(id)` * `service_location_id` → `pf_sites(id)` * `supervising_provider_id` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_encounters_delete` (DELETE) * `pm_encounters_insert` (INSERT) * `pm_encounters_select` (SELECT) * `pm_encounters_update` (UPDATE) ### `pm_era_files` PM-09: Electronic Remittance Advice (835) files **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `file_name` | text | no | | | | `file_path` | text | yes | | | | `file_size_bytes` | integer | yes | | | | `payer_id` | uuid | yes | | | | `check_number` | text | yes | | | | `total_paid` | numeric(12,2) | yes | | | | `total_claims` | integer | yes | 0 | | | `processed_claims` | integer | yes | 0 | | | `received_date` | date | no | CURRENT\_DATE | | | `processed_at` | timestamp with time zone | yes | | | | `status` | text | no | 'received'::text | | | `error_details` | 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 | | Soft delete timestamp. NULL = active, non-NULL = soft deleted. | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `pm_era_files_delete` (DELETE) * `pm_era_files_insert` (INSERT) * `pm_era_files_select` (SELECT) * `pm_era_files_update` (UPDATE) ### `pm_era_orphan_lines` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `remittance_id` | uuid | no | | | | `raw_835_line` | jsonb | no | | | | `patient_name_raw` | text | yes | | | | `service_date_raw` | date | yes | | | | `amount_cents` | integer | no | | | | `payer_id` | uuid | yes | | | | `status` | text | no | 'pending'::text | | | `matched_claim_id` | uuid | yes | | | | `matched_at` | timestamp with time zone | yes | | | | `matched_by` | uuid | yes | | | | `reject_reason` | text | yes | | | | `last_reprocess_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)` * `matched_by` → `pf_profiles(id)` * `matched_claim_id` → `pm_claims(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `remittance_id` → `pm_era_files(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_eol_delete` (DELETE) * `pm_eol_insert` (INSERT) * `pm_eol_select` (SELECT) * `pm_eol_update` (UPDATE) ### `pm_era_reconciliation_results` PM-44: Stores ERA payment reconciliation results comparing paid vs contracted amounts per claim line **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `era_payment_id` | uuid | no | | | | `claim_id` | uuid | no | | | | `claim_line_id` | uuid | yes | | | | `payer_id` | uuid | yes | | | | `provider_id` | uuid | yes | | | | `claim_line_number` | integer | no | | | | `cpt_code` | text | no | | | | `modifier` | text | yes | | | | `date_of_service` | date | yes | | | | `billed_amount` | numeric(10,2) | no | | | | `paid_amount` | numeric(10,2) | no | | | | `contracted_amount` | numeric(10,2) | yes | | | | `variance_amount` | numeric(10,2) | yes | | | | `variance_pct` | numeric(5,2) | yes | | | | `reconciliation_status` | text | no | 'pending'::text | | | `appeal_id` | uuid | yes | | | | `ai_pattern_flag` | text | yes | | | | `reviewed_by` | uuid | yes | | | | `reviewed_at` | timestamp with time zone | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"external\_ref": "ERR-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 | | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active, non-NULL = soft deleted. | **Foreign keys:** * `claim_id` → `pm_claims(id)` * `claim_line_id` → `pm_claim_lines(id)` * `created_by` → `pf_profiles(id)` * `era_payment_id` → `pm_payments(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `reviewed_by` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_era_recon_delete` (DELETE) * `pm_era_recon_insert` (INSERT) * `pm_era_recon_select` (SELECT) * `pm_era_recon_update` (UPDATE) ### `pm_fee_schedule_entries` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `fee_schedule_id` | uuid | no | | | | `cpt_code` | text | no | | | | `modifier` | text | yes | | | | `rate` | numeric(12,2) | no | 0 | | | `unit_type` | text | no | 'per\_unit'::text | | | `effective_date` | date | no | | | | `end_date` | date | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `users(id)` * `fee_schedule_id` → `pm_fee_schedules(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_fee_schedule_entries_delete` (DELETE) * `pm_fee_schedule_entries_insert` (INSERT) * `pm_fee_schedule_entries_select` (SELECT) * `pm_fee_schedule_entries_update` (UPDATE) ### `pm_fee_schedules` PM-07: Payer fee schedules defining rates for CPT/HCPCS codes **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | --------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `schedule_name` | text | no | | | | `effective_date` | date | no | | | | `end_date` | date | yes | | | | `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 | | User who created this fee schedule | | `updated_by` | uuid | yes | | User who last updated this fee schedule | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `users(id)` **Policies:** * `pm_fee_schedules_delete` (DELETE) * `pm_fee_schedules_insert` (INSERT) * `pm_fee_schedules_select` (SELECT) * `pm_fee_schedules_update` (UPDATE) ### `pm_fhir_access_log` PM-55: Append-only audit log of every FHIR resource request. No UPDATE/DELETE permitted (HIPAA 6yr retention). **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | yes | | | | `app_registration_id` | uuid | yes | | | | `consent_id` | uuid | yes | | | | `denial_log_id` | uuid | yes | | | | `request_method` | text | no | | | | `request_path` | text | no | | | | `resource_type` | text | yes | | | | `response_status` | integer | no | | | | `scopes_evaluated` | jsonb | no | '\[]'::jsonb | | | `client_ip` | text | yes | | | | `user_agent` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `app_registration_id` → `pm_fhir_app_registrations(id)` * `consent_id` → `pm_patient_access_consents(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` **Policies:** * `pm_fhir_log_insert` (INSERT) * `pm_fhir_log_select` (SELECT) ### `pm_fhir_app_registrations` PM-55: Third-party SMART-on-FHIR apps registered for patient access per tenant. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `client_id` | text | no | | | | `app_name` | text | no | | | | `app_type` | text | no | | | | `redirect_uris` | jsonb | no | '\[]'::jsonb | | | `jwks_uri` | text | yes | | | | `client_secret_hash` | text | yes | | | | `contact_email` | text | yes | | | | `homepage_url` | text | yes | | | | `logo_url` | text | yes | | | | `scopes_requested` | jsonb | no | '\[]'::jsonb | | | `status` | text | no | 'active'::text | | | `registered_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 | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_fhir_app_reg_delete` (DELETE) * `pm_fhir_app_reg_insert` (INSERT) * `pm_fhir_app_reg_select` (SELECT) * `pm_fhir_app_reg_update` (UPDATE) ### `pm_financial_assistance_applications` PM-16: Financial assistance / charity-care application and decision. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `status` | text | no | 'pending'::text | | | `applied_at` | date | no | CURRENT\_DATE | | | `decided_at` | date | yes | | | | `effective_from` | date | yes | | | | `effective_through` | date | yes | | | | `decision_notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"sliding\_scale\_tier": "2", "document\_id": "..."}). | | `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)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_financial_assistance_applications_delete` (DELETE) * `pm_financial_assistance_applications_insert` (INSERT) * `pm_financial_assistance_applications_select` (SELECT) * `pm_financial_assistance_applications_update` (UPDATE) ### `pm_financial_counseling_sessions` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `session_date` | date | no | | | | `counselor_id` | uuid | yes | | | | `income_amount` | numeric(12,2) | no | | | | `household_size` | integer | no | | | | `fpl_percentage` | numeric(5,2) | yes | | | | `income_verified_at` | timestamp with time zone | yes | | | | `income_verification_docs` | jsonb | no | '\[]'::jsonb | | | `form_id` | uuid | yes | | | | `form_snapshot` | jsonb | 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:** * `counselor_id` → `pf_profiles(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `users(id)` **Policies:** * `pm_financial_counseling_sessions_delete` (DELETE) * `pm_financial_counseling_sessions_insert` (INSERT) * `pm_financial_counseling_sessions_select` (SELECT) * `pm_financial_counseling_sessions_update` (UPDATE) ### `pm_fpl_thresholds` PM-21: HHS federal poverty level thresholds by household size. **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `calendar_year` | integer | no | | | | `household_size` | integer | no | | | | `threshold_amount` | numeric(12,2) | no | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `state_adjustment` | text | no | 'continental'::text | | **Policies:** * `pm_fpl_thresholds_select` (SELECT) ### `pm_gfe_appointment_cpt_mapping` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `appointment_type` | text | no | | | | `cpt_code` | text | no | | | | `display_order` | integer | no | 0 | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_gfe_cpt_mapping_delete` (DELETE) * `pm_gfe_cpt_mapping_insert` (INSERT) * `pm_gfe_cpt_mapping_select` (SELECT) * `pm_gfe_cpt_mapping_update` (UPDATE) ### `pm_gfe_disputes` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `gfe_id` | uuid | no | | | | `actual_total` | numeric(12,2) | no | | | | `variance_amount` | numeric(12,2) | no | | | | `dispute_date` | date | no | CURRENT\_DATE | | | `resolution` | text | yes | | | | `status` | text | no | 'open'::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)` * `gfe_id` → `pm_good_faith_estimates(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_gfe_disputes_delete` (DELETE) * `pm_gfe_disputes_insert` (INSERT) * `pm_gfe_disputes_select` (SELECT) * `pm_gfe_disputes_update` (UPDATE) ### `pm_good_faith_estimates` **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 | yes | | | | `estimate_date` | date | no | CURRENT\_DATE | | | `total_estimated` | numeric(12,2) | no | 0 | | | `items` | jsonb | no | '\[]'::jsonb | | | `delivery_method` | text | yes | | | | `delivered_at` | timestamp with time zone | yes | | | | `acknowledged_at` | timestamp with time zone | yes | | | | `status` | text | no | 'draft'::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:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_gfe_delete` (DELETE) * `pm_gfe_insert` (INSERT) * `pm_gfe_select` (SELECT) * `pm_gfe_update` (UPDATE) ### `pm_group_definitions` PM-04: Group therapy/session definitions. Reusable templates for recurring group sessions. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `group_name` | text | no | | | | `group_type` | text | no | 'therapy'::text | | | `facilitator_id` | uuid | yes | | | | `co_facilitator_id` | uuid | yes | | | | `location_id` | uuid | yes | | | | `room` | text | yes | | | | `max_capacity` | integer | no | 12 | | | `min_capacity` | integer | yes | 2 | | | `duration_minutes` | integer | no | 60 | | | `recurrence_rule` | jsonb | yes | | iCal RRULE (RFC 5545) JSONB. \{"rrule":"FREQ=WEEKLY;BYDAY=MO,WE,FR","dtstart":"2026-03-01T09:00:00Z"} | | `is_active` | boolean | no | true | | | `description` | text | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"program\_track": "IOP-AM"}) | | `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:** * `co_facilitator_id` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `facilitator_id` → `pf_profiles(id)` * `location_id` → `pf_sites(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_group_definitions_delete` (DELETE) * `pm_group_definitions_insert` (INSERT) * `pm_group_definitions_select` (SELECT) * `pm_group_definitions_update` (UPDATE) ### `pm_group_enrollments` PM-04: Patient enrollment in group definitions. Tracks enrolled, waitlisted, and disenrolled patients. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `group_definition_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `enrollment_status` | text | no | 'enrolled'::text | | | `waitlist_position` | integer | yes | | | | `enrolled_at` | timestamp with time zone | no | now() | | | `disenrolled_at` | timestamp with time zone | yes | | | | `disenroll_reason` | text | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific enrollment metadata. | | `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:** * `created_by` → `pf_profiles(id)` * `group_definition_id` → `pm_group_definitions(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_group_enrollments_delete` (DELETE) * `pm_group_enrollments_insert` (INSERT) * `pm_group_enrollments_select` (SELECT) * `pm_group_enrollments_update` (UPDATE) ### `pm_group_schedule` PM-04: Individual group session occurrences. Each row is one scheduled session date/time. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `group_definition_id` | uuid | no | | | | `session_date` | date | no | | | | `start_time` | timestamp with time zone | no | | | | `end_time` | timestamp with time zone | no | | | | `facilitator_id` | uuid | yes | | | | `co_facilitator_id` | uuid | yes | | | | `location_id` | uuid | yes | | | | `room` | text | yes | | | | `status` | text | no | 'scheduled'::text | | | `cancellation_reason` | text | yes | | | | `actual_attendance` | integer | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for this occurrence. | | `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 | | | | `room_id` | uuid | yes | | PM-03-EN-08: FK to pm\_rooms. Supersedes legacy free-text room column. | **Foreign keys:** * `co_facilitator_id` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `facilitator_id` → `pf_profiles(id)` * `group_definition_id` → `pm_group_definitions(id)` * `location_id` → `pf_sites(id)` * `organization_id` → `pf_organizations(id)` * `room_id` → `pm_rooms(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_group_schedule_delete` (DELETE) * `pm_group_schedule_insert` (INSERT) * `pm_group_schedule_select` (SELECT) * `pm_group_schedule_update` (UPDATE) ### `pm_guarantors` PM-01: Patient guarantors. Soft-delete for billing audit. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `guarantor_type` | text | yes | | | | `name` | text | no | | | | `relationship` | text | yes | | | | `address` | jsonb | yes | | | | `phone` | text | yes | | | | `employer` | 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)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_guarantors_delete` (DELETE) * `pm_guarantors_insert` (INSERT) * `pm_guarantors_select` (SELECT) * `pm_guarantors_update` (UPDATE) ### `pm_information_blocking_log` PM-55: ONC information-blocking exception log with 8 denial categories and Compliance Officer disposition workflow. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | yes | | | | `app_registration_id` | uuid | yes | | | | `resource_type` | text | yes | | | | `request_path` | text | yes | | | | `occurred_at` | timestamp with time zone | no | now() | | | `denial_category` | text | no | | | | `denial_narrative` | text | no | | | | `disposition` | text | no | 'pending'::text | | | `disposition_notes` | text | yes | | | | `reviewed_at` | timestamp with time zone | yes | | | | `reviewed_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:** * `app_registration_id` → `pm_fhir_app_registrations(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `reviewed_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `pm_ibl_insert` (INSERT) * `pm_ibl_select` (SELECT) * `pm_ibl_update` (UPDATE) ### `pm_institutional_claim_details` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `claim_id` | uuid | no | | | | `type_of_bill` | text | no | | | | `admission_date` | date | yes | | | | `admission_hour` | smallint | yes | | | | `admission_type_code` | text | yes | | | | `admission_source_code` | text | yes | | | | `discharge_status_code` | text | yes | | | | `patient_status_code` | text | yes | | | | `condition_codes` | jsonb | no | '\[]'::jsonb | | | `occurrence_codes` | jsonb | no | '\[]'::jsonb | | | `value_codes` | 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 | | | **Foreign keys:** * `claim_id` → `pm_claims(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_inst_claim_delete` (DELETE) * `pm_inst_claim_insert` (INSERT) * `pm_inst_claim_select` (SELECT) * `pm_inst_claim_update` (UPDATE) ### `pm_insurance_policies` PM: Patient insurance policies (link patient to payer) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `payer_id` | uuid | no | | | | `policy_holder_name` | text | yes | | | | `member_id` | text | yes | | | | `group_number` | text | yes | | | | `effective_date` | date | yes | | | | `end_date` | 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 | yes | | | | `updated_by` | uuid | yes | | | | `deleted_at` | timestamp with time zone | yes | | | | `policy_number` | text | yes | | | | `subscriber_name` | text | yes | | | | `subscriber_dob` | date | yes | | | | `relationship_to_subscriber` | text | yes | | | | `coverage_type` | text | yes | | | | `priority_order` | integer | yes | 1 | | | `termination_date` | date | yes | | | | `copay` | numeric | yes | | | | `coinsurance_pct` | numeric | yes | | | | `deductible` | numeric | yes | | | | `oop_max` | numeric | yes | | | | `bh_benefits` | jsonb | yes | '\{}'::jsonb | | | `status` | text | yes | 'active'::text | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `users(id)` **Policies:** * `pm_insurance_policies_delete` (DELETE) * `pm_insurance_policies_insert` (INSERT) * `pm_insurance_policies_select` (SELECT) * `pm_insurance_policies_update` (UPDATE) ### `pm_kiosk_checkins` PM-34-EN-01: Tracks patient self-service kiosk check-in sessions including demographics, insurance capture, consent signing, and copay collection. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | -------------------- | ----------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `appointment_id` | uuid | no | | | | `kiosk_device_id` | text | yes | | | | `current_step` | text | no | 'identify'::text | | | `demographics_updated` | boolean | yes | false | | | `insurance_card_captured` | boolean | yes | false | | | `insurance_card_front_id` | uuid | yes | | | | `insurance_card_back_id` | uuid | yes | | | | `consents_signed` | jsonb | yes | '\[]'::jsonb | | | `copay_amount` | numeric(10,2) | yes | | | | `copay_collected` | boolean | yes | false | | | `copay_declined` | boolean | yes | false | | | `payment_id` | uuid | yes | | | | `started_at` | timestamp with time zone | no | now() | | | `completed_at` | timestamp with time zone | yes | | | | `abandoned_at` | timestamp with time zone | yes | | | | `status` | text | no | 'in\_progress'::text | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"device\_location": "Lobby A"}) | | `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:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `pf_profiles(id)` * `insurance_card_back_id` → `pf_documents(id)` * `insurance_card_front_id` → `pf_documents(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_kiosk_checkins_delete` (DELETE) * `pm_kiosk_checkins_insert` (INSERT) * `pm_kiosk_checkins_select` (SELECT) * `pm_kiosk_checkins_update` (UPDATE) ### `pm_managed_care_authorizations` PM-47: Managed care authorization lifecycle tracking with expiration, utilization, and review scheduling **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `patient_id` | uuid | no | | Patient UUID reference — no FK to pm\_patients; resolved at display time | | `payer_id` | uuid | no | | | | `auth_number` | text | yes | | | | `auth_type` | text | no | | | | `service_type` | text | no | | | | `effective_date` | date | no | | | | `expiration_date` | date | no | | | | `authorized_units` | integer | yes | | | | `used_units` | integer | yes | 0 | | | `unit_type` | text | yes | 'days'::text | | | `status` | text | yes | 'pending'::text | | | `review_interval_days` | integer | yes | | | | `next_review_date` | date | yes | | | | `prior_auth_id` | uuid | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"mco\_contact": "Jane Smith", "internal\_ref": "REF-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 | | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active, non-NULL = soft deleted | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `payer_id` → `pm_payers(id)` * `prior_auth_id` → `pm_prior_authorizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_mca_insert` (INSERT) * `pm_mca_select` (SELECT) * `pm_mca_update` (UPDATE) ### `pm_module_settings` PM module operational parameters: MRN formatting, claim deadlines, scheduling reminders, revenue cycle targets. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------------------------------- | ------------------------ | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `mrn_prefix` | text | no | 'MRN-'::text | Prefix for auto-generated Medical Record Numbers (e.g. MRN-) | | `mrn_auto_generate` | boolean | no | true | When true, MRN is auto-generated on patient registration | | `claim_filing_deadline_days` | integer | no | 365 | Days from service date to file a claim (default 365) | | `default_place_of_service` | text | no | '11'::text | PM-07: Default POS code for new charges | | `clean_claim_threshold_percent` | integer | no | 95 | Target clean claim rate percentage (default 95) | | `days_in_ar_target` | integer | no | 45 | Target days in accounts receivable (default 45) | | `eligibility_check_frequency` | text | no | 'per\_encounter'::text | When to check insurance eligibility: per\_encounter, daily, weekly | | `appointment_reminder_days_first` | integer | no | 7 | First reminder: days before appointment (default 7) | | `appointment_reminder_days_second` | integer | no | 1 | Second reminder: days before appointment (default 1) | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata | | `reminder_channels` | text\[] | no | ARRAY\['sms'::text, 'email'::text] | Active delivery channels for appointment reminders: sms, email, voice | | `reminder_sms_template` | text | no | 'Hi \{\{patient\_name}}, this is a reminder for your appointment on \{\{appointment\_date}} at \{\{appointment\_time}} with \{\{provider\_name}}. Reply STOP to opt out.'::text | SMS message template. Merge fields: \{\{patient\_name}}, \{\{appointment\_date}}, \{\{appointment\_time}}, \{\{provider\_name}}, \{\{organization\_name}} | | `reminder_email_template` | text | no | 'Dear \{\{patient\_name}},\n\nThis is a reminder that you have an appointment scheduled for \{\{appointment\_date}} at \{\{appointment\_time}} with \{\{provider\_name}}.\n\nIf you need to reschedule, please contact us at your earliest convenience.\n\nThank you,\n\{\{organization\_name}}'::text | Email body template. Same merge fields as SMS. | | `reminder_voice_template` | text | no | 'Hello, this is an automated reminder from \{\{organization\_name}} for \{\{patient\_name}}. You have an appointment on \{\{appointment\_date}} at \{\{appointment\_time}} with \{\{provider\_name}}. Press 1 to confirm or press 2 to reschedule.'::text | Voice/IVR script template. Same merge fields as SMS. | | `referral_number_prefix` | text | yes | 'REF'::text | | | `referral_number_sequence` | integer | yes | 1 | | | `auto_approval_threshold` | numeric(12,2) | yes | 0 | | | `bh_block_grant_modifiers` | text\[] | yes | '\{}'::text\[] | | | `auto_apply_modifiers` | boolean | yes | true | | | `claim_number_prefix` | text | yes | 'CLM'::text | | | `claim_number_sequence` | integer | yes | 1 | | | `payment_number_prefix` | text | yes | 'PMT'::text | | | `payment_number_sequence` | integer | yes | 1 | | | `default_fee_schedule_id` | uuid | yes | | PM-07: Default fee schedule for charge capture when none specified | | `charge_review_required_roles` | text\[] | yes | '\{}'::text\[] | PM-07: Roles that must review/approve charges | | `ahcccs_min_billable_minutes` | integer | no | 8 | PM-07: Minimum minutes for AHCCCS timed billing (default 8) | | `documentation_window_hours` | integer | no | 48 | PM-07: Window in hours to capture charge from note (default 48) | | `snapshot_retention_days` | integer | no | 365 | | | `telehealth_require_consent_every_encounter` | boolean | yes | true | PM-13: Re-obtain consent per encounter. | | `telehealth_recording_retention_days` | integer | yes | 90 | PM-13: Recording retention days. | | `telehealth_default_platform` | text | yes | 'stub'::text | PM-13: Default platform (stub, zoom, doxy). | | `billing_provider_npi` | text | yes | | Billing provider NPI used for claim generation (PM-08). Configured in PM Settings. | | `recred_alert_lead_days` | integer | yes | 90 | PM-17: Days before recert\_due\_on to raise re-credentialing alerts (default 90, range 7-365). | | `use_configurable_wizards` | boolean | yes | false | PF-41: Use configurable wizard templates when true; otherwise use hardcoded wizards. | | `batch_size_max` | integer | yes | 200 | PM-24: Max items per batch operation (default 200). | | `batch_async_threshold` | integer | yes | 50 | PM-24: Above this count use async execution with progress dialog (default 50). | | `denial_appeal_default_days` | integer | yes | 60 | | | `denial_bulk_resubmit_batch_size` | integer | yes | 50 | | | `created_by` | uuid | yes | | User who initially created the settings row | | `updated_by` | uuid | yes | | User who last modified the settings row | | `checkin_eligibility_enabled` | boolean | yes | false | | | `checkin_eligibility_timeout_seconds` | integer | yes | 10 | | | `checkin_eligibility_notification_enabled` | boolean | yes | true | | | `crossover_routing` | text | no | 'manual'::text | | | `cob_secondary_claim_auto_trigger` | boolean | no | false | | | `cob_order_change_triggers_reverification` | boolean | no | false | | | `charge_recon_enabled` | boolean | no | true | Enable/disable nightly charge reconciliation job. Default true. | | `charge_recon_charge_lag_days` | integer | no | 3 | Days after encounter before flagging missing charge. Default 3. Range 1-30. | | `charge_recon_claim_lag_days` | integer | no | 5 | Days after charge before flagging missing claim. Default 5. Range 1-60. | | `charge_recon_payment_lag_days` | integer | no | 45 | Days after claim before flagging missing payment. Default 45. Range 7-365. | | `charge_recon_detect_duplicates` | boolean | no | true | Enable/disable duplicate charge detection. Default true. | | `charge_recon_detect_zero_amount` | boolean | no | true | Enable/disable zero-amount charge detection. Default true. | | `cost_accounting_enabled` | boolean | no | false | | | `auto_calculate_encounter_cost` | boolean | no | false | | | `ahcccs_compliance_window_hours` | integer | yes | 72 | PM-41: AHCCCS compliance window in hours (24-168) | | `no_show_risk_alert_threshold` | integer | yes | 75 | PM-41: No-show risk score threshold for alerts (50-95) | | `waitlist_abandon_risk_alert_threshold` | integer | yes | 70 | PM-41: Waitlist abandon risk score threshold for alerts (50-95) | | `intake_match_availability_days` | integer | yes | 7 | PM-38: Number of days ahead to search for provider availability (1-30) | | `intake_auto_schedule_enabled` | boolean | yes | true | PM-38: Whether automated intake appointment scheduling is enabled | | `intake_match_max_latency_sec` | integer | yes | 10 | PM-38: Maximum seconds allowed for matching algorithm execution (1-60) | | `intake_override_alert_pct` | integer | yes | 30 | PM-38: Override percentage threshold that triggers alerts (1-100) | | `denial_prediction_enabled` | boolean | yes | false | Master toggle for AI denial prediction feature. | | `denial_prediction_high_risk_threshold` | integer | yes | 70 | Risk score threshold (0-100) above which claims are classified as high-risk. | | `financial_clearance_threshold` | numeric(10,2) | yes | 1000.00 | Patient responsibility amount that triggers auto-referral to financial counseling (PM-32) | | `estimate_validity_days` | integer | yes | 30 | Number of days a cost estimate remains valid before requiring refresh | | `rcm_queue_weight_balance` | numeric(3,2) | yes | 0.35 | AR queue scoring weight for balance amount (0.00-1.00) | | `rcm_queue_weight_aging` | numeric(3,2) | yes | 0.25 | AR queue scoring weight for aging tier (0.00-1.00) | | `rcm_queue_weight_payer_rate` | numeric(3,2) | yes | 0.25 | AR queue scoring weight for payer collection rate (0.00-1.00) | | `rcm_queue_weight_service` | numeric(3,2) | yes | 0.15 | AR queue scoring weight for service type value (0.00-1.00) | | `rcm_queue_claim_lock_hours` | integer | yes | 24 | Hours a claimed work queue item is locked to the specialist | | `rcm_override_window_hours` | integer | yes | 48 | Hours within which automated actions can be manually overridden | | `auth_alert_intervals_days` | jsonb | yes | '\[14, 7, 3, 1]'::jsonb | PM-47: Array of days-before-expiration to trigger alerts (e.g., \[14,7,3,1]) | | `auth_unit_warning_threshold_pct` | numeric(5,2) | yes | 80.00 | PM-47: Percentage of authorized units used that triggers a warning (e.g., 80.00) | | `concurrent_review_lead_days` | integer | yes | 7 | PM-47: Days before review due date to surface in worklist (e.g., 7) | | `era_recon_variance_threshold` | numeric(10,2) | yes | 5.00 | Minimum dollar variance to flag an ERA claim line as underpaid | | `era_recon_auto_enabled` | boolean | yes | true | Whether ERA reconciliation runs automatically on payment posting | | `era_recon_ai_lookback_days` | integer | yes | 90 | Number of historical days used for AI pattern detection analysis | | `min_installment_amount` | numeric(12,2) | yes | 25.00 | PM-45: Minimum monthly installment for payment plans | | `max_plan_duration_months` | integer | yes | 24 | PM-45: Maximum payment plan duration in months | | `missed_payments_default_threshold` | integer | yes | 2 | PM-45: Consecutive missed payments before plan defaults | | `writeoff_approval_threshold` | numeric(12,2) | yes | 500.00 | PM-45: Dollar threshold requiring director-level write-off approval | | `small_balance_writeoff_threshold` | numeric(12,2) | yes | 25.00 | PM-45: Balances below this auto-qualify for write-off | | `edi_stall_threshold_days` | integer | yes | 45 | Number of days an enrollment can remain in pending\_payer status before being flagged as stalled | | `kiosk_enabled` | boolean | yes | false | Enable/disable kiosk self-service check-in for this organization. | | `kiosk_inactivity_timeout_seconds` | integer | yes | 90 | Seconds of inactivity before kiosk session times out. Default 90s. Range 30-300. | | `kiosk_insurance_card_capture_enabled` | boolean | yes | true | When true, kiosk includes insurance card photo capture step. | | `kiosk_consent_signing_enabled` | boolean | yes | true | When true, kiosk includes consent form signing step. | | `kiosk_copay_collection_enabled` | boolean | yes | true | When true, kiosk includes copay collection step. | | `patient_merge_undo_window_hours` | integer | no | 24 | PM-01-EN-01: Hours after a patient merge during which an authorized user may undo it. | | `referral_sla_default_hours` | integer | yes | 24 | Default SLA window (hours) for new inbound referrals | | `court_deadline_warning_days` | integer | yes | 3 | Days-out threshold for court deadline warning indicator | | `portal_packet_default_due_days` | integer | no | 7 | PM-12-EN-01: Default number of days from assignment to packet due date. | | `portal_packet_reminder_cadence_hours` | integer | no | 48 | | | `portal_packet_scheduling_block_enabled` | boolean | no | true | PM-12-EN-01: When true, scheduling is blocked until required packet items are completed. | | `portal_packet_required_item_types` | jsonb | no | '\["consent", "demographics", "insurance"]'::jsonb | | | `ai_coding_enabled` | boolean | no | false | PM-64: master switch for AI coding suggestions | | `ai_coding_cost_cap_per_suggestion` | numeric(5,2) | no | 0.05 | PM-64: max USD per suggestion (default \$0.05) | | `ai_coding_confidence_threshold` | numeric(3,2) | no | 0.60 | PM-64: min confidence to surface suggestion (default 0.60) | | `fhir_patient_access_enabled` | boolean | no | false | PM-55: Enables FHIR Patient Access API (SMART App Launch v2.0). Per-tenant feature flag for phased rollout. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `default_fee_schedule_id` → `pm_fee_schedules(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_module_settings_insert` (INSERT) * `pm_module_settings_select` (SELECT) * `pm_module_settings_update` (UPDATE) ### `pm_otp_billing_periods` **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `program_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `period_start_date` | date | no | | | | `period_end_date` | date | no | | | | `status` | text | no | 'draft'::text | | | `total_units` | numeric(10,2) | yes | 0 | | | `charge_amount` | numeric(12,2) | yes | | | | `charge_id` | uuid | yes | | | | `claim_id` | uuid | yes | | | | `pdmp_reference_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:** * `charge_id` → `pm_charges(id)` * `claim_id` → `pm_claims(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `program_id` → `pm_otp_programs(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_otp_billing_periods_insert` (INSERT) * `pm_otp_billing_periods_select` (SELECT) * `pm_otp_billing_periods_update` (UPDATE) ### `pm_otp_programs` **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 | | | | `effective_from` | date | no | | | | `effective_to` | date | yes | | | | `billing_frequency` | text | no | 'weekly'::text | | | `bundled_codes` | text\[] | no | '\{}'::text\[] | | | `add_on_codes` | text\[] | no | '\{}'::text\[] | | | `payer_specific_config` | 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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_otp_programs_delete` (DELETE) * `pm_otp_programs_insert` (INSERT) * `pm_otp_programs_select` (SELECT) * `pm_otp_programs_update` (UPDATE) ### `pm_otp_service_components` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `billing_period_id` | uuid | no | | | | `service_date` | date | no | | | | `service_type` | text | no | | | | `hcpcs_code` | text | no | | | | `units` | numeric(8,2) | no | | | | `amount` | numeric(12,2) | 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:** * `billing_period_id` → `pm_otp_billing_periods(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_otp_service_components_delete` (DELETE) * `pm_otp_service_components_insert` (INSERT) * `pm_otp_service_components_select` (SELECT) * `pm_otp_service_components_update` (UPDATE) ### `pm_patient_access_consents` PM-55: Per-patient consent records for SMART app FHIR access. Revocation propagates within 60s. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `app_registration_id` | uuid | no | | | | `scopes_granted` | jsonb | no | '\[]'::jsonb | | | `granted_at` | timestamp with time zone | no | now() | | | `expires_at` | timestamp with time zone | yes | | | | `revoked_at` | timestamp with time zone | yes | | | | `revoked_reason` | text | yes | | | | `last_accessed_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:** * `app_registration_id` → `pm_fhir_app_registrations(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `users(id)` **Policies:** * `pm_pac_delete` (DELETE) * `pm_pac_insert` (INSERT) * `pm_pac_select` (SELECT) * `pm_pac_update` (UPDATE) ### `pm_patient_addresses` PM-01: Patient address history. Soft-delete preserves audit trail. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `address_type` | text | no | | | | `line1` | text | no | | | | `line2` | text | yes | | | | `city` | text | no | | | | `state` | text | no | | | | `zip` | text | no | | | | `county` | text | yes | | | | `is_primary` | boolean | yes | false | | | `effective_date` | date | yes | | | | `end_date` | 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 | yes | | | | `updated_by` | uuid | yes | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_patient_addresses_delete` (DELETE) * `pm_patient_addresses_insert` (INSERT) * `pm_patient_addresses_select` (SELECT) * `pm_patient_addresses_update` (UPDATE) ### `pm_patient_communication_preferences` PM-45: TCPA opt-out tracking per patient for collections communications **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `sms_opt_out` | boolean | no | false | | | `email_opt_out` | boolean | no | false | | | `opt_out_date` | timestamp with time zone | yes | | | | `opt_out_reason` | text | yes | | | | `opt_out_source` | 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:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_comm_prefs_insert` (INSERT) * `pm_comm_prefs_select` (SELECT) * `pm_comm_prefs_update` (UPDATE) ### `pm_patient_cost_estimates` PM-48: Patient cost estimates with financial clearance checklist and accuracy tracking **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 | no | | | | `episode_id` | uuid | yes | | | | `payer_id` | uuid | yes | | | | `estimate_date` | date | no | CURRENT\_DATE | | | `estimate_valid_until` | date | yes | | | | `total_estimated_charges` | numeric(12,2) | no | | | | `insurance_expected_payment` | numeric(12,2) | yes | | | | `patient_estimated_responsibility` | numeric(12,2) | no | | | | `deductible_remaining` | numeric(10,2) | yes | | | | `coinsurance_pct` | numeric(5,2) | yes | | | | `copay_amount` | numeric(10,2) | yes | | | | `oop_max_remaining` | numeric(10,2) | yes | | | | `service_type` | text | no | | | | `estimated_los_days` | integer | yes | | | | `estimated_visit_count` | integer | yes | | | | `estimate_status` | text | no | 'draft'::text | | | `clearance_status` | text | no | 'pending'::text | | | `clearance_completed_at` | timestamp with time zone | yes | | | | `clearance_completed_by` | uuid | yes | | | | `patient_notified_at` | timestamp with time zone | yes | | | | `patient_notified_method` | text | yes | | | | `override_reason` | text | yes | | | | `override_approved_by` | uuid | yes | | | | `eligibility_verified` | boolean | yes | false | | | `eligibility_verified_at` | timestamp with time zone | yes | | | | `benefits_confirmed` | boolean | yes | false | | | `benefits_confirmed_at` | timestamp with time zone | yes | | | | `payment_arranged` | boolean | yes | false | | | `payment_arranged_at` | timestamp with time zone | yes | | | | `actual_patient_responsibility` | numeric(12,2) | yes | | | | `accuracy_calculated_at` | timestamp with time zone | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"external\_estimate\_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 | | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active, non-NULL = soft deleted. | **Foreign keys:** * `clearance_completed_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `override_approved_by` → `pf_profiles(id)` * `patient_id` → `pm_patients(id)` * `payer_id` → `pm_payers(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_cost_estimates_delete` (DELETE) * `pm_cost_estimates_insert` (INSERT) * `pm_cost_estimates_select` (SELECT) * `pm_cost_estimates_update` (UPDATE) ### `pm_patient_financial_assessments` PM-21: Patient income/FPL assessment and sliding fee tier assignment. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `assessment_date` | date | no | | | | `household_size` | integer | no | | | | `annual_income` | numeric(12,2) | no | | | | `fpl_percentage` | numeric(5,2) | yes | | | | `assigned_tier` | text | yes | | | | `discount_pct` | numeric(5,2) | yes | | | | `verified_by` | uuid | yes | | | | `verification_documents` | jsonb | no | '\[]'::jsonb | | | `next_review_date` | date | yes | | | | `status` | text | no | 'pending'::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 | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` * `verified_by` → `pf_profiles(id)` **Policies:** * `pm_patient_financial_assessments_delete` (DELETE) * `pm_patient_financial_assessments_insert` (INSERT) * `pm_patient_financial_assessments_select` (SELECT) * `pm_patient_financial_assessments_update` (UPDATE) ### `pm_patient_merge_conflicts` PM-01-EN-01: Field-level conflict resolutions captured during a patient merge. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `merge_log_id` | uuid | no | | | | `organization_id` | uuid | no | | | | `field_name` | text | no | | | | `survivor_value` | jsonb | yes | | | | `merged_value` | jsonb | yes | | | | `chosen_source` | text | no | | | | `chosen_value` | jsonb | yes | | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `merge_log_id` → `pm_patient_merge_log(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_patient_merge_conflicts_no_direct_write` (ALL) * `pm_patient_merge_conflicts_select` (SELECT) ### `pm_patient_merge_log` PM-01-EN-01: Append-only audit of patient merges. Used for traceability and undo. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `survivor_patient_id` | uuid | no | | | | `merged_patient_id` | uuid | no | | | | `merged_patient_snapshot` | jsonb | no | | | | `affected_rows` | jsonb | no | '\{}'::jsonb | | | `resolution_summary` | jsonb | no | '\{}'::jsonb | | | `reason` | text | yes | | | | `performed_at` | timestamp with time zone | no | now() | | | `performed_by` | uuid | yes | | | | `undo_window_expires_at` | timestamp with time zone | no | | | | `undone_at` | timestamp with time zone | yes | | | | `undone_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `performed_by` → `users(id)` * `undone_by` → `users(id)` **Policies:** * `pm_patient_merge_log_no_direct_write` (ALL) * `pm_patient_merge_log_select` (SELECT) ### `pm_patient_payment_plans` PM-45: Patient payment plan records with installment scheduling and consent tracking **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 | no | | | | `total_balance` | numeric(12,2) | no | | | | `installment_amount` | numeric(12,2) | no | | | | `frequency` | text | no | 'monthly'::text | | | `start_date` | date | no | | | | `end_date` | date | yes | | | | `total_installments` | integer | no | | | | `remaining_balance` | numeric(12,2) | no | | | | `status` | text | no | 'active'::text | | | `auto_debit_enabled` | boolean | yes | false | | | `payment_method_id` | uuid | yes | | | | `consent_type` | text | yes | | | | `consent_date` | timestamp with time zone | yes | | | | `missed_payments_count` | integer | no | 0 | | | `last_payment_date` | 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)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_payment_plans_delete` (DELETE) * `pm_payment_plans_insert` (INSERT) * `pm_payment_plans_select` (SELECT) * `pm_payment_plans_update` (UPDATE) ### `pm_patient_site_transfers` PM-27: Patient site-to-site transfer records with optional billing handoff notes **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `from_site_id` | uuid | no | | | | `to_site_id` | uuid | no | | | | `transfer_date` | date | no | | | | `billing_handoff_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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `from_site_id` → `pf_sites(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `to_site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_patient_site_transfers_delete` (DELETE) * `pm_patient_site_transfers_insert` (INSERT) * `pm_patient_site_transfers_select` (SELECT) * `pm_patient_site_transfers_update` (UPDATE) ### `pm_patient_statements` PM-09: Patient balance statements (UI deferred to PM-12) **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `statement_date` | date | no | CURRENT\_DATE | | | `due_date` | date | yes | | | | `total_charges` | numeric(12,2) | no | 0 | | | `total_payments` | numeric(12,2) | no | 0 | | | `total_adjustments` | numeric(12,2) | no | 0 | | | `balance_due` | numeric(12,2) | no | 0 | | | `status` | text | no | 'generated'::text | | | `sent_at` | timestamp with time zone | yes | | | | `sent_method` | 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 | | | | `statement_run_id` | uuid | yes | | PM-16: Batch run that generated this statement; NULL for legacy or manual. | | `collections_ready_at` | timestamp with time zone | yes | | PM-16: When account was flagged ready for collections handoff. | | `collections_status` | text | yes | | PM-16: Collections handoff status (e.g. ready, held, handed\_off). | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `statement_run_id` → `pm_statement_runs(id)` * `updated_by` → `users(id)` **Policies:** * `pm_statements_delete` (DELETE) * `pm_statements_insert` (INSERT) * `pm_statements_select` (SELECT) * `pm_statements_update` (UPDATE) ### `pm_patients` PM-01: Core patient demographics. Organization-scoped. Soft-delete via deleted\_at. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_identity_id` | uuid | yes | | FK to pf\_patient\_identities. Join path: pm\_patients → pf\_patient\_identities ← cl\_patient\_charts. | | `mrn` | text | no | | | | `first_name` | text | no | | | | `last_name` | text | no | | | | `preferred_name` | text | yes | | | | `date_of_birth` | date | no | | | | `sex_at_birth` | text | yes | | | | `gender_identity` | text | yes | | | | `sexual_orientation` | text | yes | | | | `race` | jsonb | yes | '\[]'::jsonb | | | `ethnicity` | text | yes | | | | `tribal_affiliation` | text | yes | | | | `preferred_language` | text | yes | 'en'::text | | | `ahcccs_member_id` | text | yes | | | | `ahcccs_cis_id` | text | yes | | | | `status` | text | no | 'active'::text | | | `photo_url` | 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 | | | | `dual_eligible` | boolean | no | false | | | `medicaid_member_id` | text | yes | | Generic Medicaid member identifier. Synced bidirectionally with ahcccs\_member\_id via trigger for Arizona orgs. | | `state_system_id` | text | yes | | State information system identifier (e.g., AHCCCS CIS ID). Generic alias for jurisdiction-neutral lookups. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_identity_id` → `pf_patient_identities(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_patients_delete` (DELETE) * `pm_patients_insert` (INSERT) * `pm_patients_select` (SELECT) * `pm_patients_update` (UPDATE) ### `pm_payer_clearinghouse_routes` PM-15-P2-EN-01: Per-payer clearinghouse vendor overrides. Precedence: payer override > primary vendor > failover secondary. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | no | | | | `preferred_vendor` | text | 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 | | | | `deleted_at` | timestamp with time zone | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_pcr_delete` (DELETE) * `pm_pcr_insert` (INSERT) * `pm_pcr_select` (SELECT) * `pm_pcr_update` (UPDATE) ### `pm_payer_contracts` PM-23: Payer contracts with terms and effective/termination dates **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | no | | | | `contract_number` | text | yes | | | | `effective_date` | date | no | | | | `termination_date` | date | no | | | | `auto_renew` | boolean | no | false | | | `terms` | jsonb | yes | '\{}'::jsonb | | | `status` | text | no | 'pending'::text | | | `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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `users(id)` **Policies:** * `pm_payer_contracts_delete` (DELETE) * `pm_payer_contracts_insert` (INSERT) * `pm_payer_contracts_select` (SELECT) * `pm_payer_contracts_update` (UPDATE) ### `pm_payer_edi_enrollments` PM-46: Tracks EDI enrollment status per payer/NPI/transaction type combination **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | -------------------- | ---------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `payer_id` | uuid | no | | | | `npi` | text | no | | | | `transaction_type` | text | no | | | | `clearinghouse_id` | uuid | yes | | | | `enrollment_status` | text | no | 'not\_started'::text | | | `application_date` | date | yes | | | | `active_date` | date | yes | | | | `submitter_id` | text | yes | | | | `credentialing_link_id` | uuid | yes | | | | `notes` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"enrollment\_ref": "ENR-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 | | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active, non-NULL = soft deleted. | **Foreign keys:** * `clearinghouse_id` → `pm_clearinghouse_config(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_edi_enrollments_delete` (DELETE) * `pm_edi_enrollments_insert` (INSERT) * `pm_edi_enrollments_select` (SELECT) * `pm_edi_enrollments_update` (UPDATE) ### `pm_payer_enrollments` PM-15: Payer enrollment status with clearinghouses. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `payer_id` | uuid | no | | | | `clearinghouse_id` | uuid | no | | | | `enrollment_status` | text | yes | 'pending'::text | | | `enrolled_date` | date | yes | | | | `electronic_payer_id` | text | yes | | Clearinghouse-specific payer identifier. | | `transaction_types` | jsonb | yes | '\[]'::jsonb | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for payer enrollment. | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active. | **Foreign keys:** * `clearinghouse_id` → `pm_clearinghouse_config(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_payer_enrollments_delete` (DELETE) * `pm_payer_enrollments_insert` (INSERT) * `pm_payer_enrollments_select` (SELECT) * `pm_payer_enrollments_update` (UPDATE) ### `pm_payer_modifier_rule_versions` PM-07-EN-14: Immutable version history for payer modifier rules **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `rule_id` | uuid | no | | | | `version_number` | integer | no | | | | `change_type` | text | no | | | | `previous_state` | jsonb | yes | | | | `new_state` | jsonb | no | | | | `change_reason` | text | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `rule_id` → `pm_payer_modifier_rules(id)` **Policies:** * `pm_payer_modifier_rule_versions_insert` (INSERT) * `pm_payer_modifier_rule_versions_select` (SELECT) ### `pm_payer_modifier_rules` PM-07-EN-14: Payer-specific modifier rules for claim scrubbing and charge validation **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | no | | | | `modifier_code` | text | no | | | | `cpt_code` | text | yes | | | | `action` | text | no | | | | `severity` | text | no | | | | `description` | text | yes | | | | `effective_from` | date | no | CURRENT\_DATE | | | `effective_to` | date | yes | | | | `is_active` | boolean | no | true | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for payer modifier rules | | `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:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_payer_modifier_rules_delete` (DELETE) * `pm_payer_modifier_rules_insert` (INSERT) * `pm_payer_modifier_rules_select` (SELECT) * `pm_payer_modifier_rules_update` (UPDATE) ### `pm_payer_performance_snapshots` PM-44: Aggregated payer performance metrics computed daily by cron function **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | no | | | | `snapshot_date` | date | no | | | | `period_days` | integer | no | | | | `total_claim_lines` | integer | no | 0 | | | `underpaid_count` | integer | no | 0 | | | `matched_count` | integer | no | 0 | | | `no_contract_count` | integer | no | 0 | | | `total_variance_amount` | numeric(12,2) | no | 0 | | | `avg_variance_amount` | numeric(10,2) | yes | | | | `payment_accuracy_rate` | numeric(5,2) | yes | | | | `avg_days_to_pay` | numeric(6,1) | 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:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_payer_perf_insert` (INSERT) * `pm_payer_perf_select` (SELECT) * `pm_payer_perf_update` (UPDATE) ### `pm_payers` PM: Payers (insurance, Medicaid, Medicare, etc.) per organization **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_name` | text | no | | | | `payer_type` | text | 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 | | | | `deleted_at` | timestamp with time zone | yes | | | | `appeal_deadline_days` | integer | yes | | PM-29: Default payer appeal deadline in days | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_payers_delete` (DELETE) * `pm_payers_insert` (INSERT) * `pm_payers_select` (SELECT) * `pm_payers_update` (UPDATE) ### `pm_payment_applications` PM-09: Line-level payment applications linking payments to claim lines **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payment_id` | uuid | no | | | | `claim_id` | uuid | no | | | | `claim_line_id` | uuid | yes | | | | `application_type` | text | no | 'payment'::text | | | `applied_amount` | numeric(12,2) | no | 0 | | | `adjustment_amount` | numeric(12,2) | yes | 0 | | | `patient_responsibility` | numeric(12,2) | yes | 0 | | | `adjustment_reason_code` | text | yes | | CARC (Claim Adjustment Reason Code) | | `adjustment_group_code` | text | yes | | CAS group: CO=Contractual, PR=Patient Resp, OA=Other, PI=Payer Initiated | | `remark_codes` | text\[] | yes | | Array of RARC codes | | `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 | | | | `is_underpayment` | boolean | no | false | PM-23: True when applied\_amount \< contracted rate (after \$0.01 tolerance) | **Foreign keys:** * `claim_id` → `pm_claims(id)` * `claim_line_id` → `pm_claim_lines(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payment_id` → `pm_payments(id)` * `updated_by` → `users(id)` **Policies:** * `pm_payment_apps_delete` (DELETE) * `pm_payment_apps_insert` (INSERT) * `pm_payment_apps_select` (SELECT) * `pm_payment_apps_update` (UPDATE) ### `pm_payment_plan_installments` PM-45: Individual installment records for payment plans **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payment_plan_id` | uuid | no | | | | `installment_number` | integer | no | | | | `due_date` | date | no | | | | `amount_due` | numeric(12,2) | no | | | | `amount_paid` | numeric(12,2) | yes | 0 | | | `status` | text | no | 'scheduled'::text | | | `paid_date` | timestamp with time zone | yes | | | | `payment_reference` | text | yes | | | | `retry_count` | integer | yes | 0 | | | `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:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payment_plan_id` → `pm_patient_payment_plans(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_installments_insert` (INSERT) * `pm_installments_select` (SELECT) * `pm_installments_update` (UPDATE) ### `pm_payment_plans` PM-16: Patient installment payment plans with status and terms. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `total_amount` | numeric(12,2) | no | | | | `installment_amount` | numeric(12,2) | no | | | | `frequency` | text | no | | | | `start_date` | date | no | | | | `end_date` | date | yes | | | | `status` | text | no | 'active'::text | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"approval\_notes": "...", "external\_plan\_id": "..."}). | | `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)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_payment_plans_delete` (DELETE) * `pm_payment_plans_insert` (INSERT) * `pm_payment_plans_select` (SELECT) * `pm_payment_plans_update` (UPDATE) ### `pm_payments` PM-09: Payment records for insurance ERA and patient payments **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | -------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `payment_number` | text | no | | | | `payment_date` | date | no | CURRENT\_DATE | | | `payment_type` | text | no | 'patient'::text | | | `payment_method` | text | yes | | | | `payer_id` | uuid | yes | | | | `patient_id` | uuid | yes | | | | `era_file_id` | uuid | yes | | | | `check_number` | text | yes | | | | `reference_number` | text | yes | | | | `total_amount` | numeric(12,2) | no | 0 | | | `posted_amount` | numeric(12,2) | no | 0 | | | `unapplied_amount` | numeric(12,2) | no | 0 | | | `status` | text | no | 'pending'::text | | | `notes` | text | yes | | | | `posted_by` | uuid | yes | | | | `posted_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, non-NULL = soft deleted. | **Foreign keys:** * `era_file_id` → `pm_era_files(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `payer_id` → `pm_payers(id)` * `posted_by` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `pm_payments_delete` (DELETE) * `pm_payments_insert` (INSERT) * `pm_payments_select` (SELECT) * `pm_payments_update` (UPDATE) ### `pm_portal_packet_assignments` PM-12-EN-01: Tracks digital intake packets assigned to portal patients. **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 | no | | | | `packet_template_id` | uuid | yes | | | | `packet_name` | text | no | | | | `status` | text | no | 'pending'::text | | | `scheduling_readiness` | boolean | no | false | | | `due_at` | timestamp with time zone | yes | | | | `assigned_at` | timestamp with time zone | no | now() | | | `started_at` | timestamp with time zone | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `cancelled_at` | timestamp with time zone | yes | | | | `last_reminder_sent_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 | | | | `completion_percentage` | numeric(5,2) | no | 0 | Auto-maintained 0-100 percent of required items completed. Updated by trg\_pm\_portal\_packet\_items\_recalc\_completion. | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `pm_portal_packet_assignments_delete` (DELETE) * `pm_portal_packet_assignments_insert` (INSERT) * `pm_portal_packet_assignments_select` (SELECT) * `pm_portal_packet_assignments_update` (UPDATE) ### `pm_portal_packet_items` PM-12-EN-01: Individual items inside a portal intake packet. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `assignment_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `item_type` | text | no | | | | `item_label` | text | no | | | | `item_reference_id` | uuid | yes | | | | `is_required` | boolean | no | true | | | `status` | text | no | 'pending'::text | | | `validation_errors` | jsonb | no | '\[]'::jsonb | | | `completed_at` | timestamp with time zone | yes | | | | `completed_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:** * `assignment_id` → `pm_portal_packet_assignments(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_portal_packet_items_delete` (DELETE) * `pm_portal_packet_items_insert` (INSERT) * `pm_portal_packet_items_select` (SELECT) * `pm_portal_packet_items_update` (UPDATE) ### `pm_portal_sessions` PM-12: Append-only portal session audit log. No UPDATE or DELETE allowed via RLS. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `portal_user_id` | uuid | no | | | | `session_token_hash` | text | no | | | | `ip_address` | inet | yes | | | | `user_agent` | text | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `expires_at` | timestamp with time zone | no | | | | `revoked_at` | timestamp with time zone | yes | | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `portal_user_id` → `pm_portal_users(id)` **Policies:** * `pm_portal_sessions_delete` (DELETE) * `pm_portal_sessions_insert` (INSERT) * `pm_portal_sessions_select` (SELECT) * `pm_portal_sessions_update` (UPDATE) ### `pm_portal_users` PM-12: Patient portal user metadata and proxy relationships. Authentication via Supabase Auth (no password\_hash). Proxy grants managed by staff. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `auth_user_id` | uuid | no | | | | `email` | text | no | | | | `mfa_enabled` | boolean | no | false | | | `status` | text | no | 'active'::text | | | `failed_login_attempts` | integer | no | 0 | | | `locked_until` | timestamp with time zone | yes | | | | `proxy_for_patient_id` | uuid | yes | | If set, this user is acting as a proxy (e.g., parent/guardian) for another patient. | | `proxy_relationship` | text | yes | | | | `proxy_expires_at` | timestamp with time zone | yes | | | | `terms_accepted_at` | timestamp with time zone | yes | | | | `last_login_at` | timestamp with time zone | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific portal metadata (e.g., \{"preferred\_contact": "sms", "language\_pref": "es"}) | | `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, patient_id` → `pm_patients(organization_id, id)` * `organization_id, proxy_for_patient_id` → `pm_patients(organization_id, id)` * `auth_user_id` → `users(id)` * `created_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `pm_portal_users_delete` (DELETE) * `pm_portal_users_insert` (INSERT) * `pm_portal_users_select` (SELECT) * `pm_portal_users_update` (UPDATE) ### `pm_preadmission_form_templates` PM-40: Template definitions for preadmission form packets, versioned per program type **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `program_type` | text | no | | | | `version` | integer | no | 1 | | | `forms` | jsonb | no | '\[]'::jsonb | JSONB array of form section definitions \[\{id, title, fields\[], is\_mandatory}] | | `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:** * `pm_preadmission_form_templates_delete` (DELETE) * `pm_preadmission_form_templates_insert` (INSERT) * `pm_preadmission_form_templates_select` (SELECT) * `pm_preadmission_form_templates_update` (UPDATE) ### `pm_preadmission_packets` PM-40: Tracks individual preadmission packets sent to patients before intake appointments **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `appointment_id` | uuid | yes | | | | `patient_id` | uuid | no | | | | `program_type` | text | no | | | | `packet_template_id` | uuid | yes | | | | `status` | text | no | 'pending'::text | | | `secure_link` | text | yes | | Opaque token for patient portal access (no PHI in URL) | | `link_expires_at` | timestamp with time zone | yes | | | | `sent_at` | timestamp with time zone | yes | | | | `first_viewed_at` | timestamp with time zone | yes | | | | `completed_at` | timestamp with time zone | yes | | | | `signed_at` | timestamp with time zone | yes | | | | `completion_data` | jsonb | no | '\{}'::jsonb | JSONB of completed form sections \{section\_id: \{field: value}} | | `delivery_channels` | jsonb | no | '\{}'::jsonb | JSONB tracking delivery attempts \[\{channel, sent\_at, status}] | | `is_exception` | boolean | no | false | | | `exception_reason` | text | yes | | | | `exception_marked_by` | uuid | yes | | | | `exception_marked_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 | | | **Foreign keys:** * `appointment_id` → `pm_appointments(id)` * `created_by` → `users(id)` * `exception_marked_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `packet_template_id` → `pm_preadmission_form_templates(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `users(id)` **Policies:** * `pm_preadmission_packets_insert` (INSERT) * `pm_preadmission_packets_select` (SELECT) * `pm_preadmission_packets_update` (UPDATE) ### `pm_prior_authorizations` PM-10: Prior authorization requests for behavioral health services **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 | no | | | | `payer_id` | uuid | no | | | | `authorization_number` | text | yes | | | | `service_type` | text | no | | | | `status` | text | no | 'draft'::text | | | `urgency` | text | no | 'standard'::text | | | `requested_units` | integer | yes | | | | `approved_units` | integer | yes | | | | `used_units` | integer | yes | 0 | | | `requested_start_date` | date | yes | | | | `requested_end_date` | date | yes | | | | `approved_start_date` | date | yes | | | | `approved_end_date` | date | yes | | | | `diagnosis_codes` | text\[] | yes | | | | `procedure_codes` | text\[] | yes | | | | `clinical_justification` | text | yes | | | | `determination_date` | date | yes | | | | `determination_notes` | text | yes | | | | `denial_reason` | text | yes | | | | `facility_id` | uuid | yes | | | | `requesting_provider_id` | uuid | yes | | | | `servicing_provider_id` | uuid | yes | | | | `submitted_at` | timestamp with time zone | yes | | | | `submitted_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 | | Soft delete timestamp. NULL = active. | **Foreign keys:** * `created_by` → `users(id)` * `facility_id` → `pf_sites(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `payer_id` → `pm_payers(id)` * `requesting_provider_id` → `pf_profiles(id)` * `servicing_provider_id` → `pf_profiles(id)` * `site_id` → `pf_sites(id)` * `submitted_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `pm_prior_auth_delete` (DELETE) * `pm_prior_auth_insert` (INSERT) * `pm_prior_auth_select` (SELECT) * `pm_prior_auth_update` (UPDATE) ### `pm_program_insurance_revenue_matrix` PM-41-P3: Per-program per-insurance estimated revenue lookup matrix **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `program_type` | text | no | | | | `insurance_type` | text | no | | | | `estimated_per_lead_revenue` | numeric(10,2) | no | | | | `effective_from` | date | yes | | | | `effective_to` | 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 | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_revenue_matrix_delete` (DELETE) * `pm_revenue_matrix_insert` (INSERT) * `pm_revenue_matrix_select` (SELECT) * `pm_revenue_matrix_update` (UPDATE) ### `pm_provider_caseloads` PM-05: Provider caseload capacity and panel status **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `provider_id` | uuid | no | | | | `active_patient_count` | integer | no | 0 | | | `max_caseload` | integer | no | 30 | | | `panel_status` | text | no | 'open'::text | open, limited, closed | | `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 | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `provider_id` → `pf_profiles(id)` * `updated_by` → `users(id)` **Policies:** * `pm_provider_caseloads_delete` (DELETE) * `pm_provider_caseloads_insert` (INSERT) * `pm_provider_caseloads_select` (SELECT) * `pm_provider_caseloads_update` (UPDATE) ### `pm_provider_enrollment_events` PM-17: Append-only audit log of enrollment lifecycle events. No updated\_at or custom\_fields (audit table exemption). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `provider_enrollment_id` | uuid | no | | | | `event_type` | text | no | | | | `occurred_at` | timestamp with time zone | no | now() | | | `details` | jsonb | yes | '\{}'::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, provider_enrollment_id` → `pm_provider_enrollments(organization_id, id)` **Policies:** * `pm_provider_enrollment_events_delete` (DELETE) * `pm_provider_enrollment_events_insert` (INSERT) * `pm_provider_enrollment_events_select` (SELECT) * `pm_provider_enrollment_events_update` (UPDATE) ### `pm_provider_enrollments` PM-17: Provider-level payer enrollment and credentialing records. Distinct from pm\_payer\_enrollments (PM-15) which tracks clearinghouse-level enrollment. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `provider_identity_id` | uuid | no | | Opaque UUID resolved via Platform Integration Layer (HR provider-identity contract). No direct FK to HR. | | `payer_id` | uuid | no | | | | `enrollment_status` | text | no | | | | `rendering_npi` | text | yes | | | | `billing_npi` | text | yes | | | | `taxonomy_code` | text | yes | | | | `effective_on` | date | yes | | | | `expires_on` | date | yes | | | | `recert_due_on` | date | yes | | | | `denial_reason` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., group\_npi, external\_credentialing\_id). | | `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 = archived. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_provider_enrollments_delete` (DELETE) * `pm_provider_enrollments_insert` (INSERT) * `pm_provider_enrollments_select` (SELECT) * `pm_provider_enrollments_update` (UPDATE) ### `pm_provider_identifier_validations` PM-17: Append-only NPI/taxonomy validation results. No updated\_at or custom\_fields (audit table exemption). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `provider_identity_id` | uuid | no | | | | `validation_type` | text | no | | | | `validation_status` | text | no | | | | `checked_at` | timestamp with time zone | no | now() | | | `message` | text | yes | | | | `details` | jsonb | yes | '\{}'::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)` **Policies:** * `pm_provider_identifier_validations_delete` (DELETE) * `pm_provider_identifier_validations_insert` (INSERT) * `pm_provider_identifier_validations_select` (SELECT) * `pm_provider_identifier_validations_update` (UPDATE) ### `pm_provider_schedules` PM-05: Recurring weekly availability blocks for providers **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `provider_id` | uuid | no | | | | `location_id` | uuid | yes | | | | `day_of_week` | integer | no | | 0=Sunday, 6=Saturday | | `start_time` | time without time zone | no | | | | `end_time` | time without time zone | no | | | | `block_type` | text | no | | appointments, telehealth, documentation, supervision, group, administrative, on\_call | | `effective_date` | date | no | | | | `end_date` | date | yes | | | | `recurrence_rule` | jsonb | 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 | | | **Foreign keys:** * `created_by` → `users(id)` * `location_id` → `pf_sites(id)` * `organization_id` → `pf_organizations(id)` * `provider_id` → `pf_profiles(id)` * `updated_by` → `users(id)` **Policies:** * `pm_provider_schedules_delete` (DELETE) * `pm_provider_schedules_insert` (INSERT) * `pm_provider_schedules_select` (SELECT) * `pm_provider_schedules_update` (UPDATE) ### `pm_provider_time_off` PM-05: Provider time-off requests with approval workflow **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `provider_id` | uuid | no | | | | `start_date` | date | no | | | | `end_date` | date | no | | | | `time_off_type` | text | no | | | | `reason` | text | yes | | | | `coverage_provider_id` | uuid | yes | | | | `status` | text | no | 'pending'::text | pending, approved, denied, cancelled | | `approved_by` | uuid | yes | | | | `approved_at` | timestamp with time zone | yes | | | | `denied_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:** * `approved_by` → `users(id)` * `coverage_provider_id` → `pf_profiles(id)` * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `provider_id` → `pf_profiles(id)` * `updated_by` → `users(id)` **Policies:** * `pm_provider_time_off_delete` (DELETE) * `pm_provider_time_off_insert` (INSERT) * `pm_provider_time_off_select` (SELECT) * `pm_provider_time_off_update` (UPDATE) ### `pm_rarc_codes` PM-29: Remittance Advice Remark Codes (RARC) **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ------------- | ------------------------ | -------- | ------- | ------- | | `code` | text | no | | | | `description` | text | no | | | | `category` | text | yes | | | | `is_active` | boolean | no | true | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Policies:** * `pm_rarc_codes_select` (SELECT) ### `pm_rcm_automation_rules` PM-49: Revenue cycle automation rule definitions with conditions and actions **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 | | | | `description` | text | yes | | | | `conditions` | jsonb | no | | | | `actions` | jsonb | no | | | | `trigger_type` | text | no | 'scheduled'::text | | | `trigger_event` | text | yes | | | | `priority` | integer | yes | 100 | | | `is_active` | boolean | yes | false | | | `is_template` | boolean | yes | false | | | `max_batch_amount` | numeric(12,2) | yes | 10000.00 | | | `max_batch_count` | integer | yes | 500 | | | `activated_by` | uuid | yes | | | | `activated_at` | timestamp with time zone | yes | | | | `last_simulation_at` | timestamp with time zone | yes | | | | `last_execution_at` | timestamp with time zone | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"notes": "...", "external\_ref": "..."}) | | `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:** * `activated_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_rcm_automation_rules_delete` (DELETE) * `pm_rcm_automation_rules_insert` (INSERT) * `pm_rcm_automation_rules_select` (SELECT) * `pm_rcm_automation_rules_update` (UPDATE) ### `pm_rcm_benchmarks` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `metric_type` | text | no | | | | `benchmark_source` | text | no | 'internal'::text | | | `target_value` | numeric | no | | | | `low_threshold` | numeric | yes | | | | `high_threshold` | numeric | yes | | | | `effective_date` | date | no | CURRENT\_DATE | | | `expires_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 | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `users(id)` **Policies:** * `pm_rcm_benchmarks_delete` (DELETE) * `pm_rcm_benchmarks_insert` (INSERT) * `pm_rcm_benchmarks_select` (SELECT) * `pm_rcm_benchmarks_update` (UPDATE) ### `pm_rcm_rule_executions` PM-49: Immutable audit trail of every rule execution action. No UPDATE or DELETE allowed. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `rule_id` | uuid | no | | | | `batch_id` | uuid | no | | | | `execution_type` | text | no | | | | `target_entity_type` | text | no | | | | `target_entity_id` | uuid | no | | | | `input_snapshot` | jsonb | no | | | | `action_taken` | text | no | | | | `action_details` | jsonb | yes | | | | `result` | text | no | | | | `error_message` | text | yes | | | | `original_execution_id` | uuid | yes | | | | `executed_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `original_execution_id` → `pm_rcm_rule_executions(id)` * `rule_id` → `pm_rcm_automation_rules(id)` **Policies:** * `pm_rcm_rule_executions_insert` (INSERT) * `pm_rcm_rule_executions_no_delete` (DELETE) * `pm_rcm_rule_executions_no_update` (UPDATE) * `pm_rcm_rule_executions_select` (SELECT) ### `pm_rcm_snapshots` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `snapshot_date` | date | no | | | | `metric_type` | text | no | | | | `metric_value` | numeric | no | | | | `dimensions` | 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 | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `pm_rcm_snapshots_delete` (DELETE) * `pm_rcm_snapshots_insert` (INSERT) * `pm_rcm_snapshots_select` (SELECT) * `pm_rcm_snapshots_update` (UPDATE) ### `pm_rcm_work_queue_items` PM-49: Dynamic AR work queue with priority scoring for claim follow-up **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `claim_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `balance_amount` | numeric(12,2) | no | | | | `aging_days` | integer | no | | | | `payer_collection_rate` | numeric(5,4) | yes | | | | `service_type` | text | yes | | | | `priority_score` | numeric(8,4) | no | 0 | | | `score_breakdown` | jsonb | yes | | | | `queue_status` | text | no | 'open'::text | | | `assigned_to` | uuid | yes | | | | `assigned_at` | timestamp with time zone | yes | | | | `claim_expires_at` | timestamp with time zone | yes | | | | `last_scored_at` | timestamp with time zone | no | now() | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `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)` **Policies:** * `pm_rcm_work_queue_delete` (DELETE) * `pm_rcm_work_queue_insert` (INSERT) * `pm_rcm_work_queue_select` (SELECT) * `pm_rcm_work_queue_update` (UPDATE) ### `pm_referral_documents` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `referral_id` | uuid | no | | | | `document_name` | text | no | | | | `document_type` | text | yes | | | | `storage_path` | text | no | | | | `file_size_bytes` | bigint | yes | | | | `mime_type` | text | yes | | | | `uploaded_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `referral_id` → `pm_referrals(id)` * `uploaded_by` → `users(id)` **Policies:** * `pm_referral_documents_delete` (DELETE) * `pm_referral_documents_insert` (INSERT) * `pm_referral_documents_select` (SELECT) * `pm_referral_documents_update` (UPDATE) ### `pm_referral_packet_items` PM-06-EN-01: Packet checklist items for outbound referrals. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `referral_id` | uuid | no | | | | `item_name` | text | no | | | | `item_description` | text | yes | | | | `is_required` | boolean | no | true | | | `is_complete` | boolean | no | false | | | `completed_at` | timestamp with time zone | yes | | | | `completed_by` | uuid | yes | | | | `sort_order` | integer | no | 0 | | | `custom_fields` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `completed_by` → `pf_profiles(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `referral_id` → `pm_referrals(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_referral_packet_items_delete` (DELETE) * `pm_referral_packet_items_insert` (INSERT) * `pm_referral_packet_items_select` (SELECT) * `pm_referral_packet_items_update` (UPDATE) ### `pm_referrals` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | yes | | | | `referral_number` | text | no | ''::text | | | `direction` | text | no | 'inbound'::text | | | `referral_source_type` | text | yes | | | | `referral_source_name` | text | yes | | | | `referral_source_phone` | text | yes | | | | `referral_source_email` | text | yes | | | | `referral_source_fax` | text | yes | | | | `ce_lead_id` | uuid | yes | | | | `referral_source_id` | uuid | yes | | | | `partner_id` | uuid | yes | | | | `patient_id` | uuid | yes | | | | `authorization_id` | uuid | yes | | | | `patient_first_name` | text | no | | | | `patient_last_name` | text | no | | | | `patient_dob` | text | yes | | | | `patient_phone` | text | yes | | | | `patient_email` | text | yes | | | | `presenting_issue` | text | yes | | | | `clinical_summary` | text | yes | | | | `diagnosis_codes` | text\[] | yes | | | | `medications` | text | yes | | | | `urgency` | text | no | 'routine'::text | | | `is_court_ordered` | boolean | no | false | | | `court_order_details` | text | yes | | | | `referred_level_of_care` | text\[] | yes | | | | `referred_program_notes` | text | yes | | | | `referring_facility_name` | text | yes | | | | `referring_facility_npi` | text | yes | | | | `referring_provider_name` | text | yes | | | | `referring_provider_npi` | text | yes | | | | `receiving_facility_name` | text | yes | | | | `receiving_facility_npi` | text | yes | | | | `receiving_provider_name` | text | yes | | | | `receiving_provider_npi` | text | yes | | | | `status` | text | no | 'received'::text | | | `status_reason` | text | yes | | | | `status_changed_at` | timestamp with time zone | yes | | | | `status_changed_by` | uuid | yes | | | | `referral_date` | timestamp with time zone | no | now() | | | `intake_date` | timestamp with time zone | yes | | | | `admission_date` | timestamp with time zone | yes | | | | `follow_up_date` | timestamp with time zone | yes | | | | `internal_notes` | 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 | | | | `hearing_date` | date | yes | | Court hearing date for court-ordered referrals | | `court_deadline_date` | date | yes | | Court-imposed compliance deadline for mandated referrals | | `sla_due_at` | timestamp with time zone | yes | | SLA deadline for referral acknowledgment/scheduling | | `sla_breached_at` | timestamp with time zone | yes | | Timestamp when SLA was breached (NULL if not breached) | | `referral_origin` | text | yes | 'portal'::text | Channel through which the referral was received | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `site_id` → `pf_sites(id)` * `status_changed_by` → `users(id)` * `updated_by` → `users(id)` **Policies:** * `pm_referrals_delete` (DELETE) * `pm_referrals_insert` (INSERT) * `pm_referrals_select` (SELECT) * `pm_referrals_update` (UPDATE) ### `pm_resources` PM-03-EN-08: Generic schedulable resources. Schema-only in this release. **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 | | | | `resource_type` | text | yes | | | | `status` | text | no | 'active'::text | | | `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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `pm_resources_delete` (DELETE) * `pm_resources_insert` (INSERT) * `pm_resources_select` (SELECT) * `pm_resources_update` (UPDATE) ### `pm_revenue_code_mappings` **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `code` | text | no | | | | `code_type` | text | no | | | | `revenue_code` | text | no | | | | `description` | 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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `users(id)` **Policies:** * `pm_rev_code_delete` (DELETE) * `pm_rev_code_insert` (INSERT) * `pm_rev_code_select` (SELECT) * `pm_rev_code_update` (UPDATE) ### `pm_rooms` PM-03-EN-08: Physical rooms used for in-person appointments and group sessions. **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 | | | | `capacity` | integer | no | 1 | | | `supported_appointment_types` | text\[] | no | '\{}'::text\[] | | | `status` | text | no | 'active'::text | | | `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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `users(id)` **Policies:** * `pm_rooms_delete` (DELETE) * `pm_rooms_insert` (INSERT) * `pm_rooms_select` (SELECT) * `pm_rooms_update` (UPDATE) ### `pm_rpa_bot_configurations` PM-51: RPA bot configurations for payer portal automation. One row per payer-portal-type combination. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | yes | | | | `payer_id` | uuid | yes | | | | `bot_name` | text | no | | | | `bot_type` | text | no | | | | `portal_url` | text | no | | | | `credential_vault_id` | uuid | yes | | | | `navigation_config` | jsonb | no | '\{}'::jsonb | | | `extraction_config` | jsonb | no | '\{}'::jsonb | | | `schedule_cron` | text | yes | | | | `blackout_windows` | jsonb | yes | '\[]'::jsonb | | | `retry_count` | integer | no | 3 | | | `is_active` | boolean | yes | false | | | `is_system_template` | boolean | yes | false | | | `last_run_at` | timestamp with time zone | yes | | | | `last_run_status` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"internal\_code": "BOT-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 | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `credential_vault_id` → `pf_credential_vault(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_rpa_bot_configurations_delete` (DELETE) * `pm_rpa_bot_configurations_insert` (INSERT) * `pm_rpa_bot_configurations_select` (SELECT) * `pm_rpa_bot_configurations_update` (UPDATE) ### `pm_rpa_bot_executions` PM-51: RPA bot execution log. One row per bot run with status, timing, and error details. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `bot_configuration_id` | uuid | no | | | | `started_at` | timestamp with time zone | no | now() | | | `completed_at` | timestamp with time zone | yes | | | | `status` | text | no | 'queued'::text | | | `records_queried` | integer | yes | 0 | | | `records_updated` | integer | yes | 0 | | | `records_failed` | integer | yes | 0 | | | `error_summary` | text | yes | | | | `error_screenshot_path` | text | yes | | | | `retry_attempt` | integer | no | 0 | | | `execution_metadata` | jsonb | yes | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | **Foreign keys:** * `bot_configuration_id, organization_id` → `pm_rpa_bot_configurations(id, organization_id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_rpa_bot_executions_insert` (INSERT) * `pm_rpa_bot_executions_select` (SELECT) ### `pm_rpa_execution_daily_summaries` PM-51: Aggregated daily execution metrics. Insert-only after archiver aggregation. **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `bot_configuration_id` | uuid | no | | | | `summary_date` | date | no | | | | `total_runs` | integer | no | 0 | | | `successful_runs` | integer | no | 0 | | | `partial_runs` | integer | no | 0 | | | `failed_runs` | integer | no | 0 | | | `total_records_queried` | integer | no | 0 | | | `total_records_updated` | integer | no | 0 | | | `total_records_failed` | integer | no | 0 | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `bot_configuration_id` → `pm_rpa_bot_configurations(id)` * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_rpa_execution_daily_summaries_select` (SELECT) ### `pm_rpa_module_settings` PM-51: Per-organization RPA module settings. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `default_retry_count` | integer | yes | 3 | Default retry count for new bot configurations (0-10) | | `failure_alert_threshold` | integer | yes | 3 | Consecutive bot execution failures before alerting RPA admins (1-20) | | `max_concurrent_sessions` | integer | yes | 5 | Maximum simultaneous headless browser sessions per organization (1-20) | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` **Policies:** * `pm_rpa_module_settings_select` (SELECT) * `pm_rpa_module_settings_update` (UPDATE) ### `pm_rpa_staged_documents` PM-51: Downloaded remittance/EOB documents staged for PM-09 processing. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `execution_id` | uuid | no | | | | `payer_id` | uuid | no | | | | `document_type` | text | no | | | | `storage_path` | text | no | | | | `file_hash` | text | no | | | | `document_date_from` | date | yes | | | | `document_date_to` | date | yes | | | | `check_eft_number` | text | yes | | | | `processing_status` | text | no | 'pending'::text | | | `processed_at` | timestamp with time zone | yes | | | | `processed_by` | uuid | yes | | | | `document_metadata` | jsonb | yes | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `execution_id, organization_id` → `pm_rpa_bot_executions(id, organization_id)` * `organization_id` → `pf_organizations(id)` * `processed_by` → `pf_profiles(id)` **Policies:** * `pm_rpa_staged_documents_delete` (DELETE) * `pm_rpa_staged_documents_select` (SELECT) * `pm_rpa_staged_documents_update` (UPDATE) ### `pm_scrub_custom_edits` PM-18-EN-06: Organization-defined custom scrub rules with condition expressions, severity, and message templates **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | ---------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `claim_type` | text | yes | | | | `name` | text | no | | | | `scope` | text | no | 'claim'::text | Evaluation scope: claim (once per claim) or line (per claim line) | | `condition_expression` | jsonb | no | | JSON condition: single predicate \{field, op, value} or compound \{all:\[...]} / \{any:\[...]} | | `severity` | text | no | | | | `message_template` | text | no | | Error message with \{\{field}} placeholders substituted at evaluation time | | `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` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_scrub_custom_edits_delete` (DELETE) * `pm_scrub_custom_edits_insert` (INSERT) * `pm_scrub_custom_edits_select` (SELECT) * `pm_scrub_custom_edits_update` (UPDATE) ### `pm_scrub_rule_definitions` PM-18: System-wide scrub rule definitions. Read-only reference table. **RLS:** ✅ enabled (1 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `rule_key` | text | no | | | | `name` | text | no | | | | `description` | text | yes | | | | `default_severity` | text | no | 'error'::text | | | `default_enabled` | boolean | no | true | | | `scope` | text | no | 'claim'::text | | | `config_schema` | jsonb | no | '\{}'::jsonb | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | **Policies:** * `pm_scrub_rule_definitions_select` (SELECT) ### `pm_scrub_rule_overrides` PM-18: Per-organization scrub rule overrides. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `rule_key` | text | no | | | | `enabled` | boolean | no | true | | | `severity` | text | no | 'error'::text | | | `config` | jsonb | no | '\{}'::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 | | | | `payer_id` | uuid | yes | | PM-18-EN-06: Per-payer override. NULL = org-wide override. | | `claim_type` | text | yes | | PM-18-EN-06: Per-claim-type override (professional\_837p or institutional\_837i). NULL = all types. | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `rule_key` → `pm_scrub_rule_definitions(rule_key)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_scrub_rule_overrides_delete` (DELETE) * `pm_scrub_rule_overrides_insert` (INSERT) * `pm_scrub_rule_overrides_select` (SELECT) * `pm_scrub_rule_overrides_update` (UPDATE) ### `pm_secondary_claims` PM-30: Secondary/tertiary claim records linking primary ERA to secondary claim generation **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `primary_claim_id` | uuid | no | | | | `primary_remittance_id` | uuid | yes | | | | `secondary_claim_id` | uuid | yes | | | | `cob_sequence` | integer | no | 2 | | | `status` | text | no | 'pending'::text | | | `primary_paid_amount` | numeric(12,2) | yes | | | | `primary_adjustment_amount` | numeric(12,2) | yes | | | | `primary_patient_responsibility` | numeric(12,2) | yes | | | | `carc_codes` | jsonb | yes | '\[]'::jsonb | | | `rarc_codes` | jsonb | yes | '\[]'::jsonb | | | `crossover_type` | 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 | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `primary_claim_id` → `pm_claims(id)` * `primary_remittance_id` → `pm_payments(id)` * `secondary_claim_id` → `pm_claims(id)` * `updated_by` → `users(id)` **Policies:** * `pm_secondary_claims_delete` (DELETE) * `pm_secondary_claims_insert` (INSERT) * `pm_secondary_claims_select` (SELECT) * `pm_secondary_claims_update` (UPDATE) ### `pm_site_billing_config` PM-27: Site-level billing NPI, tax ID, addresses, fee schedule, clearinghouse, claim defaults **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `site_id` | uuid | no | | | | `billing_npi` | text | no | | | | `tax_id` | text | no | | | | `billing_address` | jsonb | no | '\{}'::jsonb | Shape: \{ street, city, state, zip } optional: country | | `pay_to_address` | jsonb | no | '\{}'::jsonb | Same shape as billing\_address | | `default_fee_schedule_id` | uuid | yes | | | | `default_pos_code` | text | yes | | | | `clearinghouse_config_id` | uuid | yes | | | | `claim_defaults` | jsonb | no | '\{}'::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 | | | **Foreign keys:** * `clearinghouse_config_id` → `pm_clearinghouse_config(id)` * `created_by` → `pf_profiles(id)` * `default_fee_schedule_id` → `pm_fee_schedules(id)` * `organization_id` → `pf_organizations(id)` * `site_id` → `pf_sites(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_site_billing_config_delete` (DELETE) * `pm_site_billing_config_insert` (INSERT) * `pm_site_billing_config_select` (SELECT) * `pm_site_billing_config_update` (UPDATE) ### `pm_sliding_fee_schedules` PM-21: Sliding fee discount schedules with FPL brackets and discount %. Exactly one active per org. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------------- | ------------------------ | -------- | ------------------- | ----------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `schedule_name` | text | no | | | | `effective_date` | date | no | | | | `end_date` | date | yes | | | | `fpl_brackets` | jsonb | no | '\[]'::jsonb | Array of \{ fpl\_min, fpl\_max, discount\_pct, tier\_name } | | `is_active` | boolean | no | false | | | `nominal_charge_amount` | numeric(12,2) | 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)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_sliding_fee_schedules_delete` (DELETE) * `pm_sliding_fee_schedules_insert` (INSERT) * `pm_sliding_fee_schedules_select` (SELECT) * `pm_sliding_fee_schedules_update` (UPDATE) ### `pm_statement_deliveries` PM-16: Per-statement delivery attempt (print/email/portal) with outcome. Append-only. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `statement_id` | uuid | no | | | | `channel` | text | no | | | | `attempted_at` | timestamp with time zone | no | now() | | | `outcome` | text | no | | | | `delivery_ref` | text | yes | | External reference (e.g. notification\_id, document\_id). | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"batch\_id": "..."}). | | `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)` * `statement_id` → `pm_patient_statements(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_statement_deliveries_insert` (INSERT) * `pm_statement_deliveries_select` (SELECT) ### `pm_statement_runs` PM-16: Batch run for statement generation (scheduled or on-demand). **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ---------------------- | ------------------------ | -------- | ------------------- | -------------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `run_type` | text | no | | | | `run_started_at` | timestamp with time zone | no | now() | | | `run_completed_at` | timestamp with time zone | yes | | | | `status` | text | no | 'running'::text | | | `statements_generated` | integer | yes | 0 | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"cycle\_name": "monthly", "trigger": "cron"}). | | `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:** * `pm_statement_runs_delete` (DELETE) * `pm_statement_runs_insert` (INSERT) * `pm_statement_runs_select` (SELECT) * `pm_statement_runs_update` (UPDATE) ### `pm_superbills` PM-22: Generated superbills/patient receipts for encounters; persisted for audit and portal re-download. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `encounter_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `generated_at` | timestamp with time zone | no | now() | | | `document_url` | text | yes | | | | `delivery_method` | text | 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)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_superbills_delete` (DELETE) * `pm_superbills_insert` (INSERT) * `pm_superbills_select` (SELECT) * `pm_superbills_update` (UPDATE) ### `pm_task_audit_log` PM-42: Append-only audit log for coordinator task actions. Immutable — no UPDATE/DELETE allowed. **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `task_id` | uuid | no | | | | `coordinator_id` | uuid | no | | | | `action_type` | text | no | | | | `action_result` | text | yes | | | | `notes` | text | yes | | | | `performed_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `organization_id` → `pf_organizations(id)` * `task_id` → `pm_coordinator_tasks(id)` **Policies:** * `pm_task_audit_log_insert` (INSERT) * `pm_task_audit_log_select` (SELECT) ### `pm_telehealth_configuration` PM-13: Per-org telehealth platform configuration. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | -------------------------- | ------------------------ | -------- | ------------------------------------ | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `platform_name` | text | no | 'stub'::text | | | `api_key_vault_ref` | text | yes | | | | `api_secret_vault_ref` | text | yes | | | | `webhook_url` | text | yes | | | | `default_modality` | telehealth\_modality | no | 'audio\_video'::telehealth\_modality | | | `default_duration_minutes` | integer | no | 60 | | | `waiting_room_enabled` | boolean | no | true | | | `recording_enabled` | boolean | no | false | | | `is_active` | boolean | no | true | | | `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)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_telehealth_configuration_delete` (DELETE) * `pm_telehealth_configuration_insert` (INSERT) * `pm_telehealth_configuration_select` (SELECT) * `pm_telehealth_configuration_update` (UPDATE) ### `pm_telehealth_sessions` PM-13: Per-encounter telehealth session with lifecycle tracking. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | --------------------- | ------------------------ | -------- | ------------------------------------ | ---------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `appointment_id` | uuid | yes | | | | `encounter_id` | uuid | yes | | | | `configuration_id` | uuid | yes | | | | `provider_id` | uuid | yes | | | | `patient_id` | uuid | yes | | | | `session_status` | text | no | 'scheduled'::text | scheduled→waiting→active→completed\|cancelled\|failed\|no\_show. | | `telehealth_modality` | telehealth\_modality | no | 'audio\_video'::telehealth\_modality | Modifier: audio\_video→95, audio\_only→FQ, store\_forward→GQ. | | `session_url` | text | yes | | | | `provider_join_url` | text | yes | | | | `patient_join_url` | text | yes | | | | `external_session_id` | text | yes | | | | `platform_name` | text | no | 'stub'::text | | | `scheduled_start` | timestamp with time zone | no | | | | `scheduled_end` | timestamp with time zone | no | | | | `actual_start` | timestamp with time zone | yes | | | | `actual_end` | timestamp with time zone | yes | | | | `duration_minutes` | integer | yes | | | | `patient_location` | text | yes | | | | `provider_location` | text | yes | | | | `consent_obtained` | boolean | no | false | | | `consent_obtained_at` | timestamp with time zone | yes | | | | `recording_enabled` | boolean | no | false | | | `recording_url` | text | yes | | | | `place_of_service` | text | yes | '02'::text | 10 if patient\_location=home, 02 otherwise. | | `connection_quality` | text | yes | | | | `disconnect_count` | integer | yes | 0 | | | `notes` | 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:** * `appointment_id` → `pm_appointments(id)` * `configuration_id` → `pm_telehealth_configuration(id)` * `created_by` → `pf_profiles(id)` * `encounter_id` → `pm_encounters(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `provider_id` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_telehealth_sessions_delete` (DELETE) * `pm_telehealth_sessions_insert` (INSERT) * `pm_telehealth_sessions_select` (SELECT) * `pm_telehealth_sessions_update` (UPDATE) ### `pm_transaction_batches` PM-15: X12 transaction batches submitted to or received from clearinghouses. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ---------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `clearinghouse_id` | uuid | no | | | | `transaction_type` | text | no | | | | `direction` | text | no | | | | `file_name` | text | no | | | | `file_path` | text | yes | | | | `record_count` | integer | yes | | | | `status` | text | no | 'pending'::text | | | `submitted_at` | timestamp with time zone | yes | | | | `acknowledged_at` | timestamp with time zone | yes | | | | `errors` | jsonb | yes | '\[]'::jsonb | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata for batch processing. | | `created_by` | uuid | yes | | | | `updated_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | | `updated_at` | timestamp with time zone | no | now() | | | `deleted_at` | timestamp with time zone | yes | | Soft delete timestamp. NULL = active. | | `retry_count` | integer | no | 0 | Number of transport retry attempts | | `next_retry_at` | timestamp with time zone | yes | | Scheduled timestamp for next retry attempt | | `transport_error` | text | yes | | Last transport-level error message (not PHI) | **Foreign keys:** * `clearinghouse_id` → `pm_clearinghouse_config(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_transaction_batches_delete` (DELETE) * `pm_transaction_batches_insert` (INSERT) * `pm_transaction_batches_select` (SELECT) * `pm_transaction_batches_update` (UPDATE) ### `pm_transaction_log` PM-15: Append-only transaction log. No UPDATE or DELETE allowed. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------ | ------------------------ | -------- | ------------------- | ------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `batch_id` | uuid | yes | | | | `transaction_type` | text | no | | | | `claim_id` | uuid | yes | | | | `patient_id` | uuid | yes | | | | `trace_number` | text | yes | | | | `status` | text | no | | | | `error_codes` | jsonb | yes | '\[]'::jsonb | | | `response_data` | jsonb | yes | '\{}'::jsonb | | | `created_by` | uuid | yes | | | | `created_at` | timestamp with time zone | no | now() | | **Foreign keys:** * `batch_id` → `pm_transaction_batches(id)` * `claim_id` → `pm_claims(id)` * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` **Policies:** * `pm_transaction_log_deny_delete` (DELETE) * `pm_transaction_log_deny_update` (UPDATE) * `pm_transaction_log_insert` (INSERT) * `pm_transaction_log_select` (SELECT) ### `pm_vbp_incentive_payments` PM-25: Incentive payments earned from VBP programs. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ----------------- | ------------------------ | -------- | ------------------- | ------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `program_id` | uuid | no | | | | `payment_period` | text | no | | | | `earned_amount` | numeric(12,2) | no | 0 | | | `paid_amount` | numeric(12,2) | no | 0 | | | `status` | text | no | 'pending'::text | | | `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 | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `program_id` → `pm_vbp_programs(id)` * `updated_by` → `users(id)` **Policies:** * `pm_vbp_incentive_payments_delete` (DELETE) * `pm_vbp_incentive_payments_insert` (INSERT) * `pm_vbp_incentive_payments_select` (SELECT) * `pm_vbp_incentive_payments_update` (UPDATE) ### `pm_vbp_measures` PM-25: Quality measures tracked within a VBP program. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------- | ------------------------ | -------- | ------------------- | ------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `program_id` | uuid | no | | | | `measure_id` | text | no | | | | `measure_name` | text | no | | | | `numerator` | integer | yes | | | | `denominator` | integer | yes | | | | `rate` | numeric(7,4) | yes | | | | `benchmark` | numeric(7,4) | yes | | | | `performance_level` | 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 | | | **Foreign keys:** * `created_by` → `users(id)` * `organization_id` → `pf_organizations(id)` * `program_id` → `pm_vbp_programs(id)` * `updated_by` → `users(id)` **Policies:** * `pm_vbp_measures_delete` (DELETE) * `pm_vbp_measures_insert` (INSERT) * `pm_vbp_measures_select` (SELECT) * `pm_vbp_measures_update` (UPDATE) ### `pm_vbp_programs` PM-25: Value-based payment and APM program tracking. **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `payer_id` | uuid | yes | | | | `program_name` | text | no | | | | `contract_type` | text | no | 'other'::text | | | `measurement_period` | text | yes | | | | `status` | text | no | 'active'::text | | | `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` → `users(id)` * `organization_id` → `pf_organizations(id)` * `payer_id` → `pm_payers(id)` * `updated_by` → `users(id)` **Policies:** * `pm_vbp_programs_insert` (INSERT) * `pm_vbp_programs_select` (SELECT) * `pm_vbp_programs_update` (UPDATE) ### `pm_waitlist` PM-03: Patient waitlist for appointment slots. **RLS:** ✅ enabled (4 policies) | Column | Type | Nullable | Default | Comment | | ------------------------ | ------------------------ | -------- | ------------------- | ------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `provider_id` | uuid | yes | | | | `service_type` | text | yes | | | | `preferred_days` | jsonb | yes | | JSONB array: \["monday","wednesday"]. | | `preferred_times` | jsonb | yes | | JSONB: \[\{"start":"09:00","end":"12:00"}]. | | `priority` | integer | no | 5 | 1 = highest, 10 = lowest priority. | | `status` | text | no | 'waiting'::text | | | `offered_appointment_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:** * `created_by` → `pf_profiles(id)` * `offered_appointment_id` → `pm_appointments(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `provider_id` → `pf_profiles(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_waitlist_delete` (DELETE) * `pm_waitlist_insert` (INSERT) * `pm_waitlist_select` (SELECT) * `pm_waitlist_update` (UPDATE) ### `pm_waitlist_entries` PM-39: Waitlist entries tracking patients waiting for program slots with priority scoring **RLS:** ✅ enabled (3 policies) | Column | Type | Nullable | Default | Comment | | ------------------------- | ------------------------ | -------- | ------------------- | --------------------------------------------------------------------------------- | | `id` | uuid | no | gen\_random\_uuid() | | | `organization_id` | uuid | no | | | | `patient_id` | uuid | no | | | | `screening_id` | uuid | yes | | UUID reference to ce\_screening\_attempts (no FK per ADR-002 cross-core boundary) | | `program_type` | text | no | | | | `status` | text | no | 'waitlisted'::text | | | `priority_score` | integer | no | | | | `priority_reason` | jsonb | no | '\{}'::jsonb | | | `added_at` | timestamp with time zone | no | now() | | | `last_contacted_at` | timestamp with time zone | yes | | | | `next_contact_date` | timestamp with time zone | yes | | | | `estimated_wait_days` | integer | yes | | | | `promoted_at` | timestamp with time zone | yes | | | | `promoted_appointment_id` | uuid | yes | | | | `declined_at` | timestamp with time zone | yes | | | | `decline_reason` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata (e.g., \{"referral\_notes": "..."}) | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | yes | | | | `updated_at` | timestamp with time zone | no | now() | | | `updated_by` | uuid | yes | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `patient_id` → `pm_patients(id)` * `updated_by` → `pf_profiles(id)` **Policies:** * `pm_waitlist_entries_insert` (INSERT) * `pm_waitlist_entries_select` (SELECT) * `pm_waitlist_entries_update` (UPDATE) ### `pm_waitlist_outreach_log` PM-39: Immutable audit log of outreach attempts to waitlisted patients **RLS:** ✅ enabled (2 policies) | Column | Type | Nullable | Default | Comment | | -------------------- | ------------------------ | -------- | ------------------- | ------------------------------ | | `id` | uuid | no | gen\_random\_uuid() | | | `waitlist_id` | uuid | no | | | | `organization_id` | uuid | no | | | | `outreach_type` | text | no | | | | `template_id` | uuid | yes | | | | `template_variables` | jsonb | no | '\{}'::jsonb | | | `message` | text | no | | | | `sent_at` | timestamp with time zone | no | now() | | | `response_received` | boolean | no | false | | | `response_content` | text | yes | | | | `response_at` | timestamp with time zone | yes | | | | `coordinator_action` | text | yes | | | | `custom_fields` | jsonb | no | '\{}'::jsonb | Organization-specific metadata | | `created_at` | timestamp with time zone | no | now() | | | `created_by` | uuid | no | | | **Foreign keys:** * `created_by` → `pf_profiles(id)` * `organization_id` → `pf_organizations(id)` * `waitlist_id` → `pm_waitlist_entries(id)` **Policies:** * `pm_waitlist_outreach_log_insert` (INSERT) * `pm_waitlist_outreach_log_select` (SELECT)