Encore OS — Documentation

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

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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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”: “<entity_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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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
`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)

Platform Foundation Schema Reference Recovery Housing Schema Reference

​Entity relationships

​Tables

​pm_ai_coding_suggestion_audits

​pm_ai_coding_suggestions

​pm_analytics_appointment_confirmed_log

​pm_analytics_appointment_created_log

​pm_analytics_funnel_daily

​pm_analytics_no_show_risk

​pm_analytics_revenue_impact

​pm_analytics_screening_log

​pm_analytics_waitlist_abandon_risk

​pm_analytics_waitlist_log

​pm_appeal_submissions

​pm_appeal_templates

​pm_appointment_match_log

​pm_appointment_reminders

​pm_appointments

​pm_assistance_program_referrals

​pm_audit_log

​pm_auth_concurrent_reviews

​pm_auth_denial_appeals

​pm_auth_reviews

​pm_bad_debt_writeoffs

​pm_batch_jobs

​pm_benefit_details

​pm_benefit_estimates

​pm_billing_suggestion_rules

​pm_capitation_contracts

​pm_capitation_periods

​pm_capitation_reconciliations

​pm_capitation_roster_events

​pm_carc_codes

​pm_charge_recon_daily_summary

​pm_charge_reconciliation_results

​pm_charges

​pm_charity_care_applications

​pm_checkin_discrepancies

​pm_claim_denials

​pm_claim_lines

​pm_claim_notes

​pm_claims

​pm_clearinghouse_config

​pm_code_modifier_rules

​pm_collection_actions

​pm_collections_external_referrals

​pm_contract_modeling_scenarios

​pm_contracted_rates

​pm_coordinator_tasks

​pm_cost_categories

​pm_cost_estimate_line_items

​pm_cost_rates

​pm_denial_models

​pm_denial_predictions

​pm_edi_enrollment_status_history

​pm_eligibility_checks

​pm_emergency_contacts

​pm_encounter_costs

​pm_encounters

​pm_era_files

​pm_era_orphan_lines

​pm_era_reconciliation_results

​pm_fee_schedule_entries

​pm_fee_schedules

​pm_fhir_access_log

​pm_fhir_app_registrations

​pm_financial_assistance_applications

​pm_financial_counseling_sessions

​pm_fpl_thresholds

​pm_gfe_appointment_cpt_mapping

​pm_gfe_disputes

​pm_good_faith_estimates

​pm_group_definitions

​pm_group_enrollments

​pm_group_schedule

​pm_guarantors

​pm_information_blocking_log

​pm_institutional_claim_details

​pm_insurance_policies

​pm_kiosk_checkins

​pm_managed_care_authorizations

Entity relationships

Tables

`pm_ai_coding_suggestion_audits`

`pm_ai_coding_suggestions`

`pm_analytics_appointment_confirmed_log`

`pm_analytics_appointment_created_log`

`pm_analytics_funnel_daily`

`pm_analytics_no_show_risk`

`pm_analytics_revenue_impact`

`pm_analytics_screening_log`

`pm_analytics_waitlist_abandon_risk`

`pm_analytics_waitlist_log`

`pm_appeal_submissions`

`pm_appeal_templates`

`pm_appointment_match_log`

`pm_appointment_reminders`

`pm_appointments`

`pm_assistance_program_referrals`

`pm_audit_log`

`pm_auth_concurrent_reviews`

`pm_auth_denial_appeals`

`pm_auth_reviews`

`pm_bad_debt_writeoffs`

`pm_batch_jobs`

`pm_benefit_details`

`pm_benefit_estimates`

`pm_billing_suggestion_rules`

`pm_capitation_contracts`

`pm_capitation_periods`

`pm_capitation_reconciliations`

`pm_capitation_roster_events`

`pm_carc_codes`

`pm_charge_recon_daily_summary`

`pm_charge_reconciliation_results`

`pm_charges`

`pm_charity_care_applications`

`pm_checkin_discrepancies`

`pm_claim_denials`

`pm_claim_lines`

`pm_claim_notes`

`pm_claims`

`pm_clearinghouse_config`

`pm_code_modifier_rules`

`pm_collection_actions`

`pm_collections_external_referrals`

`pm_contract_modeling_scenarios`

`pm_contracted_rates`

`pm_coordinator_tasks`

`pm_cost_categories`

`pm_cost_estimate_line_items`

`pm_cost_rates`

`pm_denial_models`

`pm_denial_predictions`

`pm_edi_enrollment_status_history`

`pm_eligibility_checks`

`pm_emergency_contacts`

`pm_encounter_costs`

`pm_encounters`

`pm_era_files`

`pm_era_orphan_lines`

`pm_era_reconciliation_results`

`pm_fee_schedule_entries`

`pm_fee_schedules`

`pm_fhir_access_log`

`pm_fhir_app_registrations`

`pm_financial_assistance_applications`

`pm_financial_counseling_sessions`

`pm_fpl_thresholds`

`pm_gfe_appointment_cpt_mapping`

`pm_gfe_disputes`

`pm_good_faith_estimates`

`pm_group_definitions`

`pm_group_enrollments`

`pm_group_schedule`

`pm_guarantors`

`pm_information_blocking_log`

`pm_institutional_claim_details`

`pm_insurance_policies`

`pm_kiosk_checkins`

`pm_managed_care_authorizations`