Encore OS — Documentation

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

Core: cl_ · Tables: 146 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`cl_988_transfers`

CL-07 EN-26: Documents 988 Crisis Line warm transfer outcomes RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`screening_id`	uuid	yes
`safety_plan_id`	uuid	yes
`transfer_date`	timestamp with time zone	no	now()
`outcome`	text	no
`follow_up_plan`	text	yes
`counselor_name`	text	yes
`peer_role`	text	yes
`hcpcs_code`	text	yes
`language_preference`	text	yes
`interpreter_used`	boolean	no	false
`cultural_notes`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
safety_plan_id → cl_safety_plans(id)
screening_id → cl_risk_screenings(id)
updated_by → pf_profiles(id)

Policies:

cl_988_transfers_delete (DELETE)
cl_988_transfers_insert (INSERT)
cl_988_transfers_select (SELECT)
cl_988_transfers_update (UPDATE)

`cl_aftercare_plans`

CL-29: Structured discharge aftercare plans linked to patient charts and optionally to care transitions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`transition_id`	uuid	yes		Optional link to cl_care_transitions(id) when CL-12 schema exists; plain UUID, no FK constraint
`plan_date`	date	no
`follow_up_appointments`	text	yes
`medication_continuation`	text	yes
`community_resources`	text	yes
`housing_plan`	text	yes
`employment_plan`	text	yes
`recovery_goals`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“family_meeting_notes”: ”…”, “caregiver_education”: ”…”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`hedis_fuh_denominator`	boolean	no	false	True if discharge qualifies for HEDIS FUH measure denominator (age >= 6, qualifying MH dx, IP/residential discharge)
`hedis_fum_denominator`	boolean	no	false	True if discharge qualifies for HEDIS FUM measure denominator (age >= 6, qualifying MH dx, ED visit)
`hedis_fuh_numerator_7`	boolean	no	false	True if qualifying FUH follow-up occurred within 7 days of discharge
`hedis_fuh_numerator_30`	boolean	no	false	True if qualifying FUH follow-up occurred within 30 days of discharge
`hedis_fum_numerator_7`	boolean	no	false	True if qualifying FUM follow-up occurred within 7 days of ED visit
`hedis_fum_numerator_30`	boolean	no	false	True if qualifying FUM follow-up occurred within 30 days of ED visit

Foreign keys:

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

Policies:

cl_aftercare_plans_delete (DELETE)
cl_aftercare_plans_insert (INSERT)
cl_aftercare_plans_select (SELECT)
cl_aftercare_plans_update (UPDATE)

`cl_ai_documentation_sessions`

CL-36: Immutable audit log of AI-assisted clinical documentation sessions. No DELETE allowed. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	no
`chart_id`	uuid	no
`encounter_id`	uuid	yes
`note_id`	uuid	yes
`note_type`	text	no
`session_started_at`	timestamp with time zone	no	now()
`session_ended_at`	timestamp with time zone	yes
`status`	text	no	’initiated’::text
`ai_vendor`	text	yes
`draft_source`	text	no	’structured_data’::text
`attestation_at`	timestamp with time zone	yes
`attestation_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“model_version”: “v2”, “input_token_count”: 1234})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

attestation_by → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
encounter_id → pm_encounters(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
user_id → pf_profiles(id)

Policies:

cl_ai_doc_sessions_insert (INSERT)
cl_ai_doc_sessions_select (SELECT)
cl_ai_doc_sessions_update (UPDATE)

`cl_ai_note_attributions`

CL-36: Attribution records for AI-assisted clinical notes. Links notes to AI sessions with attestation tracking. No DELETE allowed. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`note_id`	uuid	no
`note_type`	text	no
`ai_session_id`	uuid	no
`ai_assisted`	boolean	no	false
`ai_attestation_at`	timestamp with time zone	yes
`ai_attestation_by`	uuid	yes
`attribution_label`	text	no	’Drafted with AI assistance’::text
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“edit_distance_pct”: 42.5})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

ai_attestation_by → pf_profiles(id)
ai_session_id → cl_ai_documentation_sessions(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

cl_ai_note_attr_insert (INSERT)
cl_ai_note_attr_select (SELECT)
cl_ai_note_attr_update (UPDATE)

`cl_allergies`

CL-01: Patient allergy records with severity classification and verification tracking. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`allergen_type`	text	no	’drug’::text
`allergen`	text	no
`reaction`	text	yes
`severity`	text	no	’moderate’::text
`status`	text	no	’active’::text
`onset_date`	date	yes
`verified_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`site_id`	uuid	yes
`allergen_code`	text	yes
`allergen_code_system`	text	yes
`allergen_class_code`	text	yes		CL-45: RxNorm class code for class-based drug-allergy alert matching.
`allergen_display`	text	yes
`is_sud_mat_class`	boolean	no	false	CL-45: True if allergen RxNorm class is in SUD-MAT set. Gates 42 CFR Part 2 consent check via cl_has_sud_consent(chart_id).
`reaction_code`	text	yes
`reaction_display`	text	yes
`reaction_severity`	text	yes
`criticality`	text	yes		CL-45: FHIR AllergyIntolerance.criticality (low \| high \| unable-to-assess). UI maps legacy severity=life_threatening to criticality=high.
`verification_status`	text	no	’unconfirmed’::text
`nka_flag`	text	yes		CL-45: NKA/NKDA confirmation. NULL when allergens are documented; mutually exclusive with allergen_code.
`last_occurrence_date`	date	yes
`notes`	text	yes
`documented_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
documented_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

cl_allergies_delete (DELETE)
cl_allergies_insert (INSERT)
cl_allergies_select (SELECT)
cl_allergies_update (UPDATE)

`cl_allergy_alert_overrides`

CL-45: Append-only audit of drug-allergy alert overrides during prescribing (CL-05/CL-06). Mirrored to GR audit via cl_allergy_alert_overridden event. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`allergy_id`	uuid	no
`chart_id`	uuid	no
`ordered_drug_code`	text	no
`ordered_drug_display`	text	yes
`match_type`	text	no
`override_rationale`	text	no
`overridden_by`	uuid	no
`overridden_at`	timestamp with time zone	no	now()
`context_event_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

allergy_id → cl_allergies(id)
chart_id → cl_patient_charts(id)
organization_id → pf_organizations(id)
overridden_by → pf_profiles(id)

Policies:

cl_allergy_overrides_insert (INSERT)
cl_allergy_overrides_select (SELECT)

`cl_ambient_session_events`

CL-36-EN-01: Append-only audit log for ambient session lifecycle events (start, stop, approve, reject, break-glass, etc.). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ambient_session_id`	uuid	no
`event_type`	text	no
`reason`	text	yes
`metadata`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

ambient_session_id → cl_ambient_sessions(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

cl_ambient_session_events_delete (DELETE)
cl_ambient_session_events_insert (INSERT)
cl_ambient_session_events_select (SELECT)
cl_ambient_session_events_update (UPDATE)

`cl_ambient_sessions`

CL-36-EN-01: Ambient audio capture sessions linked to encounters. Tracks recording lifecycle, processing status, and audio retention policy. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`encounter_id`	uuid	no
`patient_id`	uuid	no
`provider_id`	uuid	no
`consent_record_id`	uuid	no
`chart_id`	uuid	no
`session_start`	timestamp with time zone	no
`session_end`	timestamp with time zone	yes
`processing_status`	text	no	’recording’::text	Session lifecycle: recording → processing → draft_ready → approved \| failed \| cancelled
`draft_note_id`	uuid	yes
`audio_storage_path`	text	yes
`audio_deleted_at`	timestamp with time zone	yes
`audio_retention_policy`	text	no	’immediate’::text	Audio file retention: immediate (delete after processing), 7_days, 30_days, 90_days
`error_message`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“vendor_session_id”: “abc123”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

chart_id → cl_patient_charts(id)
consent_record_id → cl_consents(id)
created_by → pf_profiles(id)
encounter_id → pm_encounters(id)
organization_id → pf_organizations(id)
patient_id → pf_profiles(id)
provider_id → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_ambient_sessions_delete (DELETE)
cl_ambient_sessions_insert (INSERT)
cl_ambient_sessions_select (SELECT)
cl_ambient_sessions_update (UPDATE)

`cl_ambient_transcripts`

CL-36-EN-01: Immutable transcript records from ambient audio processing. Contains raw transcript, speaker diarization, and structured SOAP sections. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ambient_session_id`	uuid	no
`transcript_data`	jsonb	no	’{}‘::jsonb
`diarization_data`	jsonb	no	’{}‘::jsonb
`soap_sections`	jsonb	no	’{}‘::jsonb	Structured SOAP note sections: {“subjective”: ”…”, “objective”: ”…”, “assessment”: ”…”, “plan”: ”…”}
`confidence_score`	numeric(5,4)	yes
`word_count`	integer	yes
`processing_duration_ms`	integer	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

ambient_session_id → cl_ambient_sessions(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

cl_ambient_transcripts_delete (DELETE)
cl_ambient_transcripts_insert (INSERT)
cl_ambient_transcripts_select (SELECT)
cl_ambient_transcripts_update (UPDATE)

`cl_assessment_instruments`

CL-02: Configurable assessment instruments per organization. Defines scoring rules, age ranges, and reassessment frequency. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`instrument_code`	text	no		Machine-readable identifier (e.g., calocus, asam, phq9, gad7)
`instrument_name`	text	no
`age_min`	integer	yes		Minimum patient age for instrument applicability (inclusive)
`age_max`	integer	yes		Maximum patient age for instrument applicability (inclusive)
`frequency_months`	integer	yes		Required reassessment interval in months
`scoring_rules`	jsonb	no	’{}‘::jsonb	Scoring configuration: { method: “sum”\|“weighted_sum”, ranges: […], weights: {…} }
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific instrument metadata
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_assessment_instruments_delete (DELETE)
cl_assessment_instruments_insert (INSERT)
cl_assessment_instruments_select (SELECT)
cl_assessment_instruments_update (UPDATE)

`cl_assessment_sections`

CL-02: Individual sections within an assessment (e.g., 18 AHCCCS elements). section_data stores the structured responses. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`assessment_id`	uuid	no
`section_code`	text	no		Machine-readable section identifier (e.g., presenting_problem, mental_status_exam)
`section_name`	text	no
`section_data`	jsonb	no	’{}‘::jsonb	Structured JSONB data for the section responses
`is_complete`	boolean	no	false	Whether this section has been fully completed
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific section metadata
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_assessment_sections_delete (DELETE)
cl_assessment_sections_insert (INSERT)
cl_assessment_sections_select (SELECT)
cl_assessment_sections_update (UPDATE)

`cl_assessment_template_sections`

CL-02-EN-61: Sections within an assessment template RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_id`	uuid	no
`section_code`	text	no
`section_name`	text	no
`display_order`	integer	no	0
`field_schema`	jsonb	no	’{}‘::jsonb	JSON schema defining the section fields and layout
`is_required`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_assessment_template_sections_delete (DELETE)
cl_assessment_template_sections_insert (INSERT)
cl_assessment_template_sections_select (SELECT)
cl_assessment_template_sections_update (UPDATE)

`cl_assessment_templates`

CL-02-EN-61: Configurable assessment template definitions per organization RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`assessment_type`	text	no	’comprehensive’::text
`version`	integer	no	1
`status`	text	no	’draft’::text
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_assessment_templates_delete (DELETE)
cl_assessment_templates_insert (INSERT)
cl_assessment_templates_select (SELECT)
cl_assessment_templates_update (UPDATE)

`cl_assessments`

CL-02: Comprehensive clinical assessments. Supports 18-element AHCCCS, CALOCUS, ASAM, and other standardized instruments. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`site_id`	uuid	yes
`assessment_type`	text	no	’comprehensive’::text	Type of assessment: comprehensive (18-element AHCCCS), calocus, asam, developmental, substance_use, review
`instrument`	text	yes
`status`	text	no	’draft’::text	Lifecycle: draft → completed → signed. Signed records are immutable (amendment path only).
`score`	numeric	yes		Numeric score from instrument scoring (e.g., CALOCUS total)
`score_details`	jsonb	yes		Detailed scoring breakdown (subscores, domain scores)
`recommended_loc`	text	yes		Recommended level of care based on instrument scoring
`clinician_id`	uuid	yes
`completed_at`	timestamp with time zone	yes
`expires_at`	timestamp with time zone	yes
`signed_at`	timestamp with time zone	yes
`signed_by`	uuid	yes
`cosigned_at`	timestamp with time zone	yes
`cosigned_by`	uuid	yes
`supersedes_id`	uuid	yes		Links to the prior assessment this one amends/supersedes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific assessment metadata
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`template_id`	uuid	yes
`cod_assessment_id`	uuid	yes		CL-31: Mirror link to cl_cod_assessments for the unified CL-02 timeline.

Foreign keys:

chart_id → cl_patient_charts(id)
clinician_id → pf_profiles(id)
cod_assessment_id → cl_cod_assessments(id)
cosigned_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
signed_by → pf_profiles(id)
site_id → pf_sites(id)
supersedes_id → cl_assessments(id)
template_id → cl_assessment_templates(id)
updated_by → pf_profiles(id)

Policies:

cl_assessments_delete (DELETE)
cl_assessments_insert (INSERT)
cl_assessments_select (SELECT)
cl_assessments_update (UPDATE)

`cl_audit_dashboard_break_glass_reviews`

CL-25: Append-only log of break-glass access reviews by compliance staff RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`audit_log_id`	uuid	no		References pf_audit_logs.id for the break-glass event being reviewed
`reviewed_by`	uuid	no
`review_notes`	text	yes
`reviewed_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)

Policies:

cl_audit_bg_reviews_insert (INSERT)
cl_audit_bg_reviews_select (SELECT)

`cl_between_session_checkins`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`checkin_at`	timestamp with time zone	no
`mood_rating`	integer	yes
`medication_adherence`	text	yes
`crisis_flag`	boolean	no	false
`crisis_warning_signs`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_between_session_checkins_clinician_select (SELECT)
cl_between_session_checkins_portal_insert (INSERT)
cl_between_session_checkins_portal_select (SELECT)

`cl_biometrics`

CL-22: Biometric measurements (height, weight, BMI, waist circumference) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`captured_at`	timestamp with time zone	no
`height_cm`	numeric	yes
`weight_kg`	numeric	yes
`bmi`	numeric	yes
`waist_circumference_cm`	numeric	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata; do not store structured PHI; constitution §5.2.1
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_biometrics_delete (DELETE)
cl_biometrics_insert (INSERT)
cl_biometrics_select (SELECT)
cl_biometrics_update (UPDATE)

`cl_care_gaps`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`gap_type`	text	no
`priority`	text	no	’routine’::text
`status`	text	no	’open’::text
`detected_at`	timestamp with time zone	no	now()
`due_date`	date	yes
`closed_at`	timestamp with time zone	yes
`closed_by`	uuid	yes		Actor that closed the gap. Holds an auth.users.id for human closures, the SYSTEM_AUTOMATION_PROFILE_ID sentinel (00000000-0000-0000-0000-000000000001) for cron auto-close, or NULL if never closed. Intentionally NOT a foreign key so the system sentinel is permitted.
`override_reason`	text	yes
`source_rule`	text	yes
`source_details`	jsonb	no	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`closure_type`	text	yes		CL-35 Phase 2: Categorization of how the gap was closed. Auto-close types: service_completed, medication_prescribed (set by cron). Manual types: clinician_override, not_applicable, patient_refused, transferred_out.
`risk_score_at_identification`	numeric(5,2)	yes		CL-35 Phase 2: Composite risk score (0-100) snapshot at the moment the care gap was detected, for retrospective analysis.

Foreign keys:

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

Policies:

cl_care_gaps_delete (DELETE)
cl_care_gaps_insert (INSERT)
cl_care_gaps_select (SELECT)
cl_care_gaps_update (UPDATE)

`cl_care_teams`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`site_id`	uuid	yes
`provider_id`	uuid	yes
`external_provider_name`	text	yes
`external_provider_npi`	text	yes
`role`	text	no
`is_primary`	boolean	no	false
`start_date`	date	no
`end_date`	date	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
provider_id → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_care_teams_delete (DELETE)
cl_care_teams_insert (INSERT)
cl_care_teams_select (SELECT)
cl_care_teams_update (UPDATE)

`cl_cda_documents`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`patient_id`	uuid	no
`document_type`	text	no
`cda_xml`	text	no
`validation_status`	text	no	’pending’::text
`validation_errors`	jsonb	no	’[]‘::jsonb
`transmission_status`	text	no	’draft’::text
`recipient_endpoint_id`	uuid	yes
`contains_part2_data`	boolean	no	false
`redisclosure_notice`	text	yes
`generated_at`	timestamp with time zone	no	now()
`transmitted_at`	timestamp with time zone	yes
`delivery_confirmed_at`	timestamp with time zone	yes
`retry_count`	integer	no	0
`last_error`	text	yes
`next_retry_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

chart_id → cl_patient_charts(id)
organization_id → pf_organizations(id)
recipient_endpoint_id → cl_cda_transmission_endpoints(id)

Policies:

cl_cda_documents_delete (DELETE)
cl_cda_documents_insert (INSERT)
cl_cda_documents_select (SELECT)
cl_cda_documents_update (UPDATE)

`cl_cda_transmission_endpoints`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`endpoint_name`	text	no
`direct_address`	text	no
`recipient_organization`	text	yes
`endpoint_type`	text	no	’direct’::text
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

organization_id → pf_organizations(id)

Policies:

cl_cda_endpoints_delete (DELETE)
cl_cda_endpoints_insert (INSERT)
cl_cda_endpoints_select (SELECT)
cl_cda_endpoints_update (UPDATE)

`cl_cds_alerts`

CL-08: CDS alerts. Append-only audit trail. PHI: contains clinical context. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`rule_id`	uuid	yes
`alert_type`	text	no
`message`	text	no		PHI: Alert message may contain drug names.
`severity`	text	no	’moderate’::text
`triggered_at`	timestamp with time zone	no	now()
`acknowledged_by`	uuid	yes
`acknowledged_at`	timestamp with time zone	yes
`overridden`	boolean	no	false
`override_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

acknowledged_by → pf_profiles(id)
created_by → pf_profiles(id)
rule_id → cl_cds_rules(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_cds_alerts_insert (INSERT)
cl_cds_alerts_select (SELECT)
cl_cds_alerts_update (UPDATE)

`cl_cds_rules`

CL-08: Configurable clinical decision support rules. Soft-delete via deleted_at. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`rule_name`	text	no
`rule_type`	text	no
`trigger_event`	text	no
`condition`	jsonb	no	’{}‘::jsonb
`action`	jsonb	no	’{}‘::jsonb
`severity`	text	no	’moderate’::text
`is_hard_stop`	boolean	no	false
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

cl_cds_rules_delete (DELETE)
cl_cds_rules_insert (INSERT)
cl_cds_rules_select (SELECT)
cl_cds_rules_update (UPDATE)

`cl_clinical_standing_orders`

CL-44: Cross-category standing order protocols (medication/lab/assessment/monitoring/nursing_action) executable by nursing staff within defined parameters. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`order_type`	text	no
`parameters`	jsonb	no
`authorized_roles`	text[]	no	’{}‘::text[]
`approved_by`	uuid	no
`approved_at`	timestamp with time zone	no	now()
`effective_date`	date	no	CURRENT_DATE
`expiration_date`	date	yes
`status`	text	no	’active’::text
`renewal_history`	jsonb	no	’[]‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

cl_clinical_standing_orders_delete (DELETE)
cl_clinical_standing_orders_insert (INSERT)
cl_clinical_standing_orders_select (SELECT)
cl_clinical_standing_orders_update (UPDATE)

`cl_cod_assessments`

CL-31: Integrated COD assessments (ASI-6, BASIS-32, GAIN-SS). SUD domain keys redacted at app layer per 42 CFR Part 2. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`assessment_type`	text	no
`assessment_date`	date	no
`assessor_id`	uuid	no
`status`	text	no	’draft’::text
`domain_scores`	jsonb	no	’{}‘::jsonb	Instrument-specific JSON. ASI-6 requires 7 domain keys. SUD keys redacted by redactSUDFields when consent absent.
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

assessor_id → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cod_assessments_delete (DELETE)
cod_assessments_insert (INSERT)
cod_assessments_select (SELECT)
cod_assessments_update (UPDATE)

`cl_cod_treatment_plan_extension`

CL-31: COD-specific extension to CL-03 treatment plans. ASAM level + dimensions 5/6 + optional MOUD link. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`treatment_plan_id`	uuid	no
`chart_id`	uuid	no
`asam_cod_level`	text	yes
`asam_dimension_5_notes`	text	yes
`asam_dimension_6_notes`	text	yes
`integrated_goals_summary`	text	yes
`moud_episode_id`	uuid	yes
`archived_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
moud_episode_id → cl_moud_enrollments(id)
organization_id → pf_organizations(id)
treatment_plan_id → cl_treatment_plans(id)
updated_by → pf_profiles(id)

Policies:

cod_tp_ext_insert (INSERT)
cod_tp_ext_select (SELECT)
cod_tp_ext_update (UPDATE)

`cl_concurrent_reviews`

CL-43: Concurrent review submissions and outcomes for utilization management. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no
`chart_id`	uuid	yes
`authorization_id`	uuid	no		CL-43: References pm_prior_authorizations.id (UUID-only cross-core reference).
`reviewer_id`	uuid	yes
`review_date`	date	yes
`review_due_date`	date	no
`review_type`	text	no
`status`	text	no	’scheduled’::text
`determination`	text	yes
`approved_days`	integer	yes
`approved_through_date`	date	yes
`payer_reference`	text	yes
`denial_reason`	text	yes
`clinical_summary`	jsonb	yes	’{}‘::jsonb	CL-43: Structured JSONB summary; excludes SUD narrative.
`criteria_responses`	jsonb	yes	’{}‘::jsonb	CL-43: InterQual/MCG criteria domain responses (JSONB).
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

chart_id → cl_patient_charts(id)
organization_id → pf_organizations(id)

Policies:

cl_concurrent_reviews_delete (DELETE)
cl_concurrent_reviews_insert (INSERT)
cl_concurrent_reviews_select (SELECT)
cl_concurrent_reviews_update (UPDATE)

`cl_consent_requests`

CL-11 EN-35: Patient portal consent change requests (revocation, scope change, renewal). Reviewed by staff. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`consent_id`	uuid	yes
`request_type`	text	no
`request_details`	jsonb	no	’{}‘::jsonb
`status`	text	no	’pending’::text
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`review_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for consent requests.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

chart_id → cl_patient_charts(id)
consent_id → cl_consents(id)
created_by → users(id)
organization_id → pf_organizations(id)
reviewed_by → users(id)
updated_by → users(id)

Policies:

cl_consent_requests_delete (DELETE)
cl_consent_requests_insert (INSERT)
cl_consent_requests_select (SELECT)
cl_consent_requests_update (UPDATE)

`cl_consents`

CL-11: Patient consent records for 42 CFR Part 2, HIPAA, and organizational consent types. PHI. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`site_id`	uuid	yes		Site where consent was captured (optional).
`consent_type`	text	no		Type: tpo, sud_counseling_notes, legal_proceedings, treatment, telehealth, medication, research, general.
`consent_category`	text	no		Consent category: standard (general clinical), part2 (42 CFR Part 2 SUD), minor (Arizona age 12+), guardian (legal guardian)
`scope`	jsonb	no	’{}‘::jsonb	JSONB: record_types[], date_range?, restrictions[], purpose[]. See EN-3.
`purpose`	text	no
`named_parties`	jsonb	yes	’[]‘::jsonb	JSONB array of parties authorized to receive disclosures.
`effective_date`	date	no
`expiration_date`	date	yes
`revoked_at`	timestamp with time zone	yes
`revocation_reason`	text	yes
`signed_by_patient_at`	timestamp with time zone	yes
`witnessed_by`	uuid	yes
`document_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete. NULL = active. Hard delete restricted to org_admin.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
witnessed_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_consents_delete (DELETE)
cl_consents_insert (INSERT)
cl_consents_select (SELECT)
cl_consents_update (UPDATE)

`cl_controlled_substance_inventory`

CL-06 EN-21: Tracks controlled substance transactions (receive, dispense, adjust, count, return, destroy) per 21 CFR 1304. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`ndc_code`	text	no
`medication_name`	text	no
`schedule`	text	no		DEA schedule classification: II, III, IV, V
`transaction_type`	text	no
`quantity`	numeric	no
`unit`	text	no
`lot_number`	text	yes
`expiration_date`	date	yes
`reference_prescription_id`	uuid	yes
`reference_external`	text	yes
`performed_by`	uuid	no
`witness_id`	uuid	yes		Required for Schedule II dispensing per 21 CFR 1304
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

reference_prescription_id → cl_prescriptions(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
performed_by → users(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
witness_id → users(id)

Policies:

cl_csi_delete (DELETE)
cl_csi_insert (INSERT)
cl_csi_select (SELECT)
cl_csi_update (UPDATE)

`cl_cron_run_log`

CL-24+: per-day cron idempotency log. Used by cl-telehealth-consent-expiry-scan to prevent re-publish within 24h. RLS: ✅ enabled

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`job_name`	text	no
`run_date`	date	no
`rows_processed`	integer	no	0
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()

`cl_data_exchange_log`

CL-16: Immutable audit trail for all FHIR, C-CDA, HIE, and bulk data exchanges. Append-only — no UPDATE or DELETE. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`exchange_type`	text	no		Type of data exchange: fhir_api, ccda, adt, hie, bulk_export, patient_access
`direction`	text	no
`partner_id`	text	yes
`partner_name`	text	yes
`resource_types`	jsonb	yes	’[]‘::jsonb
`record_count`	integer	yes
`status`	text	no
`initiated_at`	timestamp with time zone	no	now()
`completed_at`	timestamp with time zone	yes
`error_details`	text	yes		21st Century Cures Act compliance: captures error context for information blocking investigations
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

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

Policies:

cl_data_exchange_log_insert (INSERT)
cl_data_exchange_log_select (SELECT)

`cl_ddi_alert_suppressions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`provider_id`	uuid	no
`rxcui_1`	text	no
`rxcui_2`	text	no
`eligible_severity`	text	no
`suppressed_reason`	text	yes
`revoked_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_ddi_alert_suppressions_delete (DELETE)
cl_ddi_alert_suppressions_insert (INSERT)
cl_ddi_alert_suppressions_select (SELECT)
cl_ddi_alert_suppressions_update (UPDATE)

`cl_ddi_overrides`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`medication_id`	uuid	no
`interacting_medication_id`	uuid	no
`rxcui_pair`	text[]	no
`severity`	text	no
`reason_code`	text	no
`reason_text`	text	yes
`acknowledged_by`	uuid	no
`cosigner_id`	uuid	yes
`cosigned_at`	timestamp with time zone	yes
`consent_id`	uuid	yes
`override_context`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

acknowledged_by → users(id)
chart_id → cl_patient_charts(id)
consent_id → cl_consents(id)
cosigner_id → users(id)
created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

cl_ddi_overrides_delete (DELETE)
cl_ddi_overrides_insert (INSERT)
cl_ddi_overrides_select (SELECT)
cl_ddi_overrides_update (UPDATE)

`cl_designated_contacts`

CL-07 EN-25: Family/emergency contacts designated for risk notifications RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`contact_name`	text	no
`relationship`	text	yes
`phone`	text	yes
`email`	text	yes
`notification_enabled`	boolean	no	true
`consent_verified_at`	timestamp with time zone	yes
`consent_verified_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

chart_id → cl_patient_charts(id)
consent_verified_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

cl_designated_contacts_delete (DELETE)
cl_designated_contacts_insert (INSERT)
cl_designated_contacts_select (SELECT)
cl_designated_contacts_update (UPDATE)

`cl_diagnoses`

CL-40: Patient diagnoses with ICD-10 codes, linked to patient charts RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no
`chart_id`	uuid	no
`icd10_code`	text	no
`description`	text	no
`diagnosis_type`	text	yes	’preliminary’::text	preliminary, confirmed, rule_out, resolved
`status`	text	no	’active’::text
`onset_date`	date	yes
`resolved_date`	date	yes
`entered_by`	uuid	yes
`confirmed_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
entered_by → pf_profiles(id)
organization_id → pf_organizations(id)
patient_id → pm_patients(id)
updated_by → pf_profiles(id)

Policies:

cl_diagnoses_delete (DELETE)
cl_diagnoses_insert (INSERT)
cl_diagnoses_select (SELECT)
cl_diagnoses_update (UPDATE)

`cl_discharge_checklists`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`transition_id`	uuid	no
`items`	jsonb	no	’[]‘::jsonb
`overall_status`	text	no	’pending’::text
`waived_reason`	text	yes
`waived_by`	uuid	yes
`waived_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
updated_by → pf_profiles(id)
waived_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)
organization_id, transition_id → cl_transitions(organization_id, id)

Policies:

cl_discharge_checklists_delete (DELETE)
cl_discharge_checklists_insert (INSERT)
cl_discharge_checklists_select (SELECT)
cl_discharge_checklists_update (UPDATE)

`cl_disclosure_log`

CL-11: Append-only disclosure log for 42 CFR Part 2 accounting of disclosures. PHI. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`consent_id`	uuid	yes
`disclosed_to`	text	no
`disclosure_purpose`	text	no
`disclosure_date`	timestamp with time zone	no	now()
`record_types_disclosed`	jsonb	yes	’[]‘::jsonb
`consent_scope_summary`	text	yes
`redisclosure_notice_included`	boolean	no	true	Whether redisclosure prohibition notice was included (Part 2 requirement).
`disclosed_by`	uuid	no
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

consent_id → cl_consents(id)
created_by → pf_profiles(id)
disclosed_by → pf_profiles(id)
organization_id → pf_organizations(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_disclosure_log_delete (DELETE)
cl_disclosure_log_insert (INSERT)
cl_disclosure_log_select (SELECT)
cl_disclosure_log_update (UPDATE)

`cl_dsi_disclosures`

CL-65: §170.315(b)(11) DSI source-attribute disclosure registry. One published row per (org, model_id, model_version) gates AI clinical features (CL-36 note drafting). Org-scoped readable; publish/retire is org-admin-only; rows are append-versioned (retire, do not delete) for audit continuity. RLS: ✅ enabled (5 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`model_id`	text	no		CL-65: stable identifier of the model/algorithm the disclosure describes (matches the active PHI-lane model resolved by resolveModels in the edge function).
`model_version`	text	no		CL-65: model version the disclosure is valid for; the generation gate matches on (model_id, model_version, status=published).
`status`	text	no	’draft’::text
`published_at`	timestamp with time zone	yes
`published_by`	uuid	yes
`retired_at`	timestamp with time zone	yes
`intended_use`	text	yes
`intended_patient_population`	text	yes
`intended_users`	text	yes
`intended_decision_role`	text	yes
`cautioned_out_of_scope`	text	yes
`training_data_description`	text	yes
`fairness_development_approach`	text	yes
`external_validation_description`	text	yes
`quantitative_performance`	jsonb	no	’{}‘::jsonb	CL-65 §170.315(b)(11)(iv)(B)(9): quantitative performance measures (AUC, sensitivity, specificity, PPV/NPV, etc.) as a metric->value map. JSONB because the metric set varies per model.
`fairness_measures`	jsonb	no	’{}‘::jsonb	CL-65 §170.315(b)(11)(iv)(B)(10): validity/fairness measures by protected subgroup (race/ethnicity, gender, age, disability, primary language). JSONB because subgroups and per-group metrics are multi-valued.
`monitoring_cadence`	text	yes
`limitations`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Must not contain PHI — disclosure metadata only.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`retired_by`	uuid	yes
`developer_name`	text	yes
`developer_contact`	text	yes
`training_data_collection_methods`	text	yes
`inclusion_exclusion_criteria`	text	yes
`correlated_variables_disclosure`	text	yes
`update_validation_process`	text	yes

Foreign keys:

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

Policies:

cl_dsi_disclosures_delete (DELETE)
cl_dsi_disclosures_insert (INSERT)
cl_dsi_disclosures_public_read (SELECT)
cl_dsi_disclosures_select (SELECT)
cl_dsi_disclosures_update (UPDATE)

`cl_electronic_consents`

CL-11-EN-01: Signed electronic consent records with granular categories and e-signature data for 42 CFR Part 2 compliance RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no
`consent_categories`	jsonb	no		Canonical consent categories JSONB: required keys treatment,payment,operations,specific_providers,research. Each key maps to object with granted:boolean; optional scope:string; specific_providers may include provider_ids:uuid[].
`effective_date`	date	no
`expiration_date`	date	yes
`signature_data`	jsonb	no
`status`	text	no	’active’::text
`revoked_at`	timestamp with time zone	yes
`created_by`	uuid	yes
`updated_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for electronic consent extensibility

Foreign keys:

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

Policies:

cl_electronic_consents_delete (DELETE)
cl_electronic_consents_insert (INSERT)
cl_electronic_consents_select (SELECT)
cl_electronic_consents_update (UPDATE)

`cl_environmental_assessments`

CL-07: Environmental risk assessments for inpatient/residential settings. Ligature resistance per Joint Commission NPSG.15.01.01. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	no
`chart_id`	uuid	yes		Optional FK to cl_patient_charts. Set when assessment is linked to a specific patient admission (US-4 Clarification).
`assessed_by`	uuid	no
`assessment_date`	date	no
`location_name`	text	no
`location_type`	text	no
`ligature_points_identified`	jsonb	no	’[]‘::jsonb	Array of identified ligature points (e.g., [{“location”: “window blind cord”, “risk”: “high”}]).
`ligature_risk_level`	text	yes
`mitigation_actions`	jsonb	no	’[]‘::jsonb	Array of actions taken to reduce identified risks (e.g., [{“action”: “Remove cord”, “completed”: true}]).
`sharps_secured`	boolean	no	true
`medications_secured`	boolean	no	true
`windows_secured`	boolean	no	true
`status`	text	no	’completed’::text
`next_assessment_due`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific assessment metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assessed_by → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_environmental_assessments_delete (DELETE)
cl_environmental_assessments_insert (INSERT)
cl_environmental_assessments_select (SELECT)
cl_environmental_assessments_update (UPDATE)

`cl_family_notification_log`

CL-07 EN-25: Audit log of family notification decisions and outcomes RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`screening_id`	uuid	yes
`contact_id`	uuid	no
`risk_level`	text	no
`consent_status`	text	no
`notification_sent`	boolean	no	false
`notification_sent_at`	timestamp with time zone	yes
`notification_channel`	text	yes
`decision_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
contact_id → cl_designated_contacts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
screening_id → cl_risk_screenings(id)
updated_by → pf_profiles(id)

Policies:

cl_family_notification_log_insert (INSERT)
cl_family_notification_log_select (SELECT)

`cl_fhir_resources`

CL-16: Cached FHIR R4 resources mapped from internal clinical and patient data for interoperability. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`resource_type`	text	no		FHIR resource type (Patient, Condition, MedicationRequest, AllergyIntolerance, Encounter, Observation, etc.)
`resource_id`	text	no		FHIR resource logical ID, unique within organization and resource_type
`fhir_version`	text	no	’R4’::text
`resource_json`	jsonb	no		Full FHIR R4 JSON resource conforming to US Core profile
`source_system`	text	yes		Origin system identifier (e.g., “northsight”, “contexture_hie”)
`last_synced_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“hie_sync_status”: “pending”, “external_ref”: “HIE-001”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_fhir_resources_delete (DELETE)
cl_fhir_resources_insert (INSERT)
cl_fhir_resources_select (SELECT)
cl_fhir_resources_update (UPDATE)

`cl_follow_up_contacts`

CL-29: Post-discharge follow-up contacts (7-day, 30-day) feeding HEDIS FUH measures RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`aftercare_plan_id`	uuid	yes
`contact_type`	text	no
`contact_at`	timestamp with time zone	no
`outcome`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted
`contact_modality`	text	yes		Modality of follow-up contact: in_person, telehealth, telephone, other. HEDIS FUH counts in_person and telehealth only.

Foreign keys:

aftercare_plan_id → cl_aftercare_plans(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_follow_up_contacts_delete (DELETE)
cl_follow_up_contacts_insert (INSERT)
cl_follow_up_contacts_select (SELECT)
cl_follow_up_contacts_update (UPDATE)

`cl_group_attendance`

CL-14: Per-patient attendance and individual documentation for group therapy sessions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`session_id`	uuid	no
`chart_id`	uuid	no
`attendance_status`	text	no
`sign_in_time`	time without time zone	yes
`sign_out_time`	time without time zone	yes
`participation_level`	text	yes
`individual_response`	text	yes		PHI: Clinician notes on member individual response during group
`goal_progress_notes`	text	yes		PHI: Goal progress observations from group participation
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted
`recording_consent_captured_at`	timestamp with time zone	yes
`recording_consent_modality`	text	yes
`technology_check_completed_at`	timestamp with time zone	yes
`emergency_contact_verified_at`	timestamp with time zone	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
session_id → cl_group_sessions(id)
updated_by → pf_profiles(id)

Policies:

cl_group_attendance_delete (DELETE)
cl_group_attendance_insert (INSERT)
cl_group_attendance_select (SELECT)
cl_group_attendance_update (UPDATE)

`cl_group_curricula`

CL-14-ENHANCEMENTS (EN-39): Curriculum library for group therapy sessions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`program_type`	text	yes
`language`	text	yes
`topics`	jsonb	no	’[]‘::jsonb	Ordered list of session topics as JSON array
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

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

Policies:

cl_group_curricula_delete (DELETE)
cl_group_curricula_insert (INSERT)
cl_group_curricula_select (SELECT)
cl_group_curricula_update (UPDATE)

`cl_group_encounter_generations`

CL-14-EN-01: Tracks individual encounter generation from group session attendance for batch review and charge creation RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`group_session_id`	uuid	no
`encounter_id`	uuid	yes
`patient_id`	uuid	no		Attendee chart ID — maps to cl_group_attendance.chart_id (logical reference, no FK constraint)
`attendance_status`	text	no
`duration_minutes`	integer	yes
`proposed_cpt_code`	text	no
`status`	text	no	’pending_review’::text
`rejection_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for encounter generation
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
group_session_id → cl_group_sessions(id)
organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

cl_group_enc_gen_delete (DELETE)
cl_group_enc_gen_insert (INSERT)
cl_group_enc_gen_select (SELECT)
cl_group_enc_gen_update (UPDATE)

`cl_group_session_telehealth_metadata`

CL-55: Telehealth metadata for virtual/hybrid group sessions. Stores PM-13 telehealth session UUID reference (no durable join URLs), contingency plan, recording state, technology failures, and backup site. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`session_id`	uuid	no
`pm_telehealth_session_id`	uuid	yes		PM-13 telehealth session UUID-only reference. Validated via platform integration layer; not a direct FK to preserve cross-core boundary.
`vendor_session_ref`	text	yes
`join_token_expires_at`	timestamp with time zone	yes
`recording_enabled`	boolean	no	false
`recording_storage_uri`	text	yes		Reserved for Phase 2 recording storage/playback. MUST remain NULL in Phase 1.
`technology_failures`	jsonb	no	’[]‘::jsonb
`in_person_backup_site_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

in_person_backup_site_id → pf_sites(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
session_id → cl_group_sessions(id)
updated_by → pf_profiles(id)

Policies:

cl_group_session_telehealth_metadata_delete (DELETE)
cl_group_session_telehealth_metadata_insert (INSERT)
cl_group_session_telehealth_metadata_select (SELECT)
cl_group_session_telehealth_metadata_update (UPDATE)

`cl_group_sessions`

CL-14: Group therapy session records with facilitator, timing, and topic RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`session_date`	date	no
`actual_start_time`	time without time zone	no		Actual service start time per AHCCCS Policy 940; not rounded
`actual_end_time`	time without time zone	no		Actual service end time; when schedule_id is set, scheduled times derive from PM-04
`group_type`	text	no
`topic`	text	no
`curriculum_id`	uuid	yes		References external curriculum via Platform Integration Layer; no direct FK (E-4)
`facilitator_id`	uuid	no
`co_facilitator_id`	uuid	yes
`location`	text	yes
`program_id`	uuid	yes		References external program via Platform Integration Layer; no direct FK (E-4)
`schedule_id`	uuid	yes		Links to PM-04 pm_group_schedule via Platform Integration Layer; no direct FK (E-2)
`max_capacity`	integer	yes
`status`	text	no	’scheduled’::text
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“room_number”: “A-101”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted
`group_subtype`	text	yes		CL-14-ENHANCEMENTS (EN-41): standard, family, or multi_family subtype
`modality`	text	no	’in_person’::text
`technology_contingency_plan`	text	yes

Foreign keys:

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

Policies:

cl_group_sessions_delete (DELETE)
cl_group_sessions_insert (INSERT)
cl_group_sessions_select (SELECT)
cl_group_sessions_update (UPDATE)

`cl_inbasket_item_activity`

CL-23: Append-only activity log for in-basket item lifecycle events. Exempt from custom_fields (audit/log table). RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`item_id`	uuid	no
`activity_type`	text	no
`actor_id`	uuid	yes
`activity_at`	timestamp with time zone	no	now()
`details`	jsonb	yes	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()

Foreign keys:

actor_id → pf_profiles(id)
item_id → cl_inbasket_items(id)
organization_id → pf_organizations(id)

Policies:

cl_inbasket_activity_insert (INSERT)
cl_inbasket_activity_select (SELECT)

`cl_inbasket_items`

CL-23: Unified clinical in-basket queue for provider action items (co-sign, lab review, refill, chart messages, CDS alerts, referral responses) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	yes
`item_type`	text	no
`priority`	text	no
`classification`	text	no
`status`	text	no
`source_event`	text	yes
`source_reference_id`	uuid	yes
`assigned_to`	uuid	yes
`due_at`	timestamp with time zone	yes
`escalated_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`payload`	jsonb	yes	’{}‘::jsonb	Item-type-specific data payload (e.g., conversation_id for chart_message items)
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“external_system_id”: “EXT-123”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

assigned_to → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_inbasket_items_delete (DELETE)
cl_inbasket_items_insert (INSERT)
cl_inbasket_items_select (SELECT)
cl_inbasket_items_update (UPDATE)

`cl_incidents`

CL-15: AZDHS incident tracking with mandated reporting timeframes and immutability after reporting RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	yes
`incident_type`	text	no
`severity`	text	no
`occurred_at`	timestamp with time zone	no
`discovered_at`	timestamp with time zone	no
`reported_to_state_at`	timestamp with time zone	yes
`reporting_deadline`	timestamp with time zone	no
`description`	text	no
`investigation_status`	text	yes	’not_started’::text
`corrective_actions`	jsonb	yes	’[]‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“internal_case_number”: “INC-2026-001”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted.

Foreign keys:

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

Policies:

cl_incidents_delete (DELETE)
cl_incidents_insert (INSERT)
cl_incidents_select (SELECT)
cl_incidents_update (UPDATE)

`cl_intake_assessments`

CL-40: Clinical intake assessments with AHCCCS 18-element compliance tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no
`appointment_id`	uuid	no
`assessment_type`	text	yes	’primary_intake’::text
`chief_complaint`	text	no
`history_of_present_illness`	text	yes
`preliminary_diagnoses`	jsonb	yes	’[]‘::jsonb	Array of {icd10_code, description, status} objects
`mental_health_history`	jsonb	yes
`substance_use_history`	jsonb	yes
`medical_history`	jsonb	yes
`social_history`	jsonb	yes
`sdoh_screening_completed`	boolean	yes	false
`sdoh_screening_id`	uuid	yes
`peer_support_recommended`	boolean	yes	false
`peer_supporter_id`	uuid	yes
`assessment_version`	integer	yes	1
`status`	text	no	’draft’::text
`clinician_id`	uuid	yes
`cosigning_clinician_id`	uuid	yes
`signed_at`	timestamp with time zone	yes
`cosigned_at`	timestamp with time zone	yes
`ahcccs_18_element_complete`	boolean	yes	((chief_complaint IS NOT NULL) AND (mental_health_history IS NOT NULL) AND (substance_use_history IS NOT NULL) AND (medical_history IS NOT NULL) AND (social_history IS NOT NULL) AND (signed_at IS NOT NULL))	DEPRECATED: CL-02-EN-60. Use intake_element_complete instead. Retained during dual-read period.
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`intake_element_complete`	boolean	yes	false	CL-02-EN-60: Trigger-maintained intake element completeness flag. Replaces ahcccs_18_element_complete.

Foreign keys:

appointment_id → pm_appointments(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
patient_id → pm_patients(id)
sdoh_screening_id → cl_sdoh_screenings(id)
updated_by → pf_profiles(id)

Policies:

cl_intake_assessments_delete (DELETE)
cl_intake_assessments_insert (INSERT)
cl_intake_assessments_select (SELECT)
cl_intake_assessments_update (UPDATE)

`cl_lethal_means_assessments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`screening_id`	uuid	yes
`encounter_id`	uuid	yes
`firearm_access`	boolean	yes
`firearm_details`	jsonb	yes	’{}‘::jsonb
`medication_stockpile`	boolean	yes
`medication_details`	jsonb	yes	’{}‘::jsonb
`sharp_objects_access`	boolean	yes
`other_means`	jsonb	yes	’[]‘::jsonb
`restriction_plan`	jsonb	yes	’{}‘::jsonb
`counseling_provided`	boolean	no	false
`counseling_notes`	text	yes
`assessed_by`	uuid	no
`assessed_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

assessed_by → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
encounter_id → pm_encounters(id)
organization_id → pf_organizations(id)
screening_id → cl_risk_screenings(id)
updated_by → pf_profiles(id)

Policies:

rls_cl_lethal_means_assessments_delete (DELETE)
rls_cl_lethal_means_assessments_insert (INSERT)
rls_cl_lethal_means_assessments_select (SELECT)
rls_cl_lethal_means_assessments_update (UPDATE)

`cl_loc_assessments`

CL-49: ASAM/LOCUS level-of-care assessments. Chart-scoped; Part 2 gated when is_sud_related=true. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`instrument_type`	text	no
`instrument_version`	text	no	’asam-3e-2013’::text
`dimension_scores`	jsonb	no	’{}‘::jsonb	Per-dimension scores keyed by dimension code (severity 0-4 + optional clinical_notes).
`total_score`	numeric(5,2)	yes
`recommended_loc_code`	text	yes
`final_loc_code`	text	yes
`override_reason_code`	text	yes
`override_rationale`	text	yes
`supersedes_id`	uuid	yes		Prior assessment this row supersedes (reassessment chain). Prior row remains immutable.
`is_sud_related`	boolean	no	false	42 CFR Part 2 flag - when true, RLS hides row unless chart has active SUD consent.
`status`	text	no	’draft’::text
`assessed_at`	timestamp with time zone	no	now()
`assessed_by`	uuid	yes
`signed_at`	timestamp with time zone	yes
`signed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assessed_by → users(id)
chart_id → cl_patient_charts(id)
created_by → users(id)
organization_id → pf_organizations(id)
signed_by → users(id)
site_id → pf_sites(id)
supersedes_id → cl_loc_assessments(id)
updated_by → users(id)

Policies:

cl_loc_assessments_delete (DELETE)
cl_loc_assessments_insert (INSERT)
cl_loc_assessments_select (SELECT)
cl_loc_assessments_update (UPDATE)

`cl_loinc_codes`

CL-09: LOINC code reference (public read-only, no org scoping). Subset for behavioral health labs. RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`loinc_code`	text	no
`component`	text	no
`property`	text	yes
`time_aspect`	text	yes
`system_type`	text	yes
`scale_type`	text	yes
`method_type`	text	yes
`class_name`	text	yes
`long_common_name`	text	yes
`short_name`	text	yes
`specimen_type`	text	yes
`is_common_bh`	boolean	no	false
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Policies:

cl_loinc_codes_select_authenticated (SELECT)

`cl_marketplace_bundle_items`

CL-57: Items within a marketplace bundle. Each carries a typed payload that handlers materialize into per-org target tables. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`bundle_id`	uuid	no
`item_type`	text	no
`item_key`	text	no
`payload`	jsonb	no
`ordering`	integer	no	0
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

bundle_id → cl_marketplace_bundles(id)

Policies:

cl_marketplace_bundle_items_delete_curator (DELETE)
cl_marketplace_bundle_items_insert_curator (INSERT)
cl_marketplace_bundle_items_read_all (SELECT)
cl_marketplace_bundle_items_update_curator (UPDATE)

`cl_marketplace_bundles`

CL-57: Platform-curated clinical content bundles. Global table (no org_id). RLS: SELECT for all authenticated; writes restricted to marketplace curators. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`bundle_key`	text	no
`version`	integer	no
`name`	text	no
`description`	text	yes
`accreditation_tags`	text[]	no	’{}‘::text[]
`clinical_area_tags`	text[]	no	’{}‘::text[]
`jurisdiction_scope`	jsonb	no	’{}‘::jsonb
`published_at`	timestamp with time zone	no	now()
`published_by`	uuid	yes
`is_deprecated`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

published_by → pf_profiles(id)

Policies:

marketplace_bundles_delete_curator (DELETE)
marketplace_bundles_insert_curator (INSERT)
marketplace_bundles_read_all (SELECT)
marketplace_bundles_update_curator (UPDATE)

`cl_marketplace_imports`

CL-57: Per-org marketplace bundle import history. Supports rollback via standardized snapshot envelope. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`bundle_id`	uuid	no
`bundle_key`	text	no
`bundle_version`	integer	no
`imported_at`	timestamp with time zone	no	now()
`imported_by`	uuid	yes
`imported_item_count`	integer	no	0
`rollback_snapshot`	jsonb	yes		Standardized rollback envelope (snapshot_version, handler_results[]). Enforced at app layer.
`status`	text	no	’in_progress’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

bundle_id → cl_marketplace_bundles(id)
imported_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

cl_marketplace_imports_delete_org (DELETE)
cl_marketplace_imports_insert_org (INSERT)
cl_marketplace_imports_select_org (SELECT)
cl_marketplace_imports_update_org (UPDATE)

`cl_medication_adherence`

CL-05 EN-17: Medication adherence records for PDC calculation and peer support tracking. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`medication_id`	uuid	no
`type`	text	no	’self_reported’::text
`date`	date	no	CURRENT_DATE
`value`	text	yes
`notes`	text	yes
`adherence_barrier`	text	yes		ICD-10 Z-code (Z55-Z65) identifying SDOH barrier to adherence.
`sdoh_screening_id`	uuid	yes
`hcpcs_code`	text	yes
`peer_support_reference`	uuid	yes		UUID referencing cl_peer_encounters; no FK constraint (table not in types.ts).
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
medication_id → cl_medications(id)
organization_id → pf_organizations(id)
sdoh_screening_id → cl_sdoh_screenings(id)
updated_by → pf_profiles(id)

Policies:

cl_medication_adherence_delete (DELETE)
cl_medication_adherence_insert (INSERT)
cl_medication_adherence_select (SELECT)
cl_medication_adherence_update (UPDATE)

`cl_medication_education`

CL-05 EN-19: Medication education records with cultural responsiveness fields. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`medication_id`	uuid	yes
`topic`	text	no
`educated_at`	timestamp with time zone	no	now()
`educated_by`	uuid	yes
`educated_by_role`	text	yes		Role of educator (rn, pharmacist, peer_specialist, etc).
`acknowledgment`	text	yes
`language_preference`	text	yes
`interpreter_used`	boolean	yes	false
`interpreter_notes`	text	yes
`peer_support_reference`	uuid	yes		UUID referencing cl_peer_encounters; no FK constraint.
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
educated_by → pf_profiles(id)
medication_id → cl_medications(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

cl_medication_education_delete (DELETE)
cl_medication_education_insert (INSERT)
cl_medication_education_select (SELECT)
cl_medication_education_update (UPDATE)

`cl_medication_reconciliations`

CL-05: Medication reconciliation records for transitions of care (admission, transfer, discharge, routine). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`trigger_event`	text	no
`status`	text	no	’pending’::text
`reconciled_by`	uuid	yes
`reconciled_at`	timestamp with time zone	yes
`patient_reported_medications`	jsonb	yes		JSON snapshot of medications reported by the patient at time of reconciliation.
`discrepancies`	jsonb	yes		JSON array of identified discrepancies between patient-reported and chart medications.
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific reconciliation metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted.

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
reconciled_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_med_recon_delete (DELETE)
cl_med_recon_insert (INSERT)
cl_med_recon_select (SELECT)
cl_med_recon_update (UPDATE)

`cl_medications`

CL-05: Patient medication list; soft-delete via deleted_at; controlled substances require DEA audit trail. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`drug_name`	text	no
`generic_name`	text	yes
`brand_name`	text	yes
`ndc_code`	text	yes
`rxcui`	text	yes
`strength`	text	yes
`strength_unit`	text	yes
`dose_amount`	numeric	yes
`dose_unit`	text	yes
`route`	text	yes
`frequency`	text	yes
`instructions`	text	yes
`status`	text	no	’active’::text
`start_date`	date	yes
`end_date`	date	yes
`discontinued_reason`	text	yes
`discontinued_by`	uuid	yes
`discontinued_at`	timestamp with time zone	yes
`prescribed_by`	uuid	yes
`prescribed_at`	timestamp with time zone	yes
`prescription_id`	uuid	yes
`source`	text	yes
`is_controlled_substance`	boolean	no	false
`dea_schedule`	text	yes
`monitoring_protocol`	jsonb	yes
`last_refill_date`	date	yes
`refills_remaining`	integer	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“formulary_id”: “F-123”, “pharmacy_notes”: ”…”})
`entered_in_error`	boolean	no	false
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
discontinued_by → pf_profiles(id)
organization_id → pf_organizations(id)
prescribed_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_medications_delete (DELETE)
cl_medications_insert (INSERT)
cl_medications_select (SELECT)
cl_medications_update (UPDATE)

`cl_metabolic_monitoring_events`

CL-22: Metabolic monitoring events for antipsychotic/MAT regimens RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`monitoring_type`	text	no
`due_by`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`status`	text	no
`result_summary`	jsonb	no	’{}‘::jsonb	Structured results for the monitoring event (e.g., lab values, BMI trend)
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata; do not store structured PHI; constitution §5.2.1
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_metabolic_monitoring_events_delete (DELETE)
cl_metabolic_monitoring_events_insert (INSERT)
cl_metabolic_monitoring_events_select (SELECT)
cl_metabolic_monitoring_events_update (UPDATE)

`cl_module_settings`

CL-01: Per-organization clinical module configuration. One row per org. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`break_glass_duration_minutes`	integer	no	60	Duration in minutes that break-glass emergency access remains valid (15-480). Default 60.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`cosignature_deadline_days`	integer	yes	14
`identical_time_flag_threshold`	integer	yes	3
`low_risk_rescreen_interval_days`	integer	no	30	CL-07: Days until next screening for low-risk patients (1-365 day range). Key: cl.screening.low_risk_rescreen_interval_days. Default: 30 days.
`assessment_completion_hours`	integer	no	48	CL-04: Hours after encounter in which an assessment/note must be completed and locked (1-168). Default 48. Key: cl.notes.assessment_completion_hours.
`treatment_plan_review_days`	integer	no	90	CL-04: Days between required treatment plan reviews (1-365). Default 90. Key: cl.treatment_plan.review_days.
`cosign_required_days`	integer	no	5	CL-04: Org workflow deadline (days) for supervisor co-signature on BHP/trainee notes (1-90). Distinct from cosignature_deadline_days (AZDHS hard limit). Default 5. Key: cl.notes.cosign_required_days.
`break_glass_review_hours`	integer	no	24	CL-01: Hours within which a supervisor must review a break-glass access event (1-168). Default 24. Key: cl.break_glass.review_hours.
`part2_consent_required`	boolean	no	true	CL-01: When true, 42 CFR Part 2 SUD consent must be on file before disclosing applicable clinical records. Default true. Key: cl.privacy.part2_consent_required.
`suicide_screening_min_age`	integer	no	12	CL-07: Minimum patient age (years) for suicide/self-harm screening (5-18). Default 12 (PHQ-A threshold per AZDHS guidance). Key: cl.screening.suicide_screening_min_age.
`assessment_expiry_alert_days`	integer	no	30
`outcome_small_n_suppression_threshold`	integer	no	5	Minimum denominator for program outcome display. Outcomes with denominator below this are suppressed for de-identification.
`pending_result_expiry_days`	integer	yes	30	Number of days before pending (unsolicited) lab results automatically expire. Default: 30.
`default_report_format`	text	yes	’pdf’::text	Default export format for report runs (pdf, csv, excel)
`incident_deadline_alert_minutes`	integer	yes	15	Minutes before AZDHS reporting_deadline to send alert
`report_generation_timeout_seconds`	integer	yes	300	Max seconds for report generation before timeout
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates when true; otherwise use hardcoded wizards.
`consent_expiration_reminder_days`	integer[]	no	’{30,14,7}‘::integer[]	CL-11 EN-33: Array of day thresholds for consent expiration reminders (e.g., {30, 14, 7} means notify at 30, 14, and 7 days before expiry).
`care_gap_phq9_days`	integer	yes	90	CL-35: Days since last PHQ-9 before gap opens
`care_gap_gad7_days`	integer	yes	90	CL-35: Days since last GAD-7 before gap opens
`care_gap_plan_review_days`	integer	yes	90	CL-35: Days past review_due_date before gap opens
`care_gap_no_contact_days`	integer	yes	30	CL-35: Days since last signed note before gap opens
`care_gap_phq9_priority`	text	yes	’urgent’::text
`care_gap_gad7_priority`	text	yes	’urgent’::text
`care_gap_engine_enabled`	boolean	yes	false	CL-35: Enable/disable care gap rules engine
`cl_ai_documentation_enabled`	boolean	no	false	Org-level opt-in for AI-assisted clinical documentation (CL-36). BAA required before setting true.
`cl_ai_ambient_vendor`	text	yes		Selected ambient voice vendor slug (e.g. nabla, nuance_dax). Null = structured-data only. Phase 3.
`cl_ai_data_residency`	text	no	’us’::text	Data residency region for AI PHI processing. us = US-only (Phase 1/2 default).
`cl_ai_policy940_enforcement`	text	no	’block’::text	AHCCCS Policy 940 enforcement mode: warn \| block \| draft-warn-final-block. Default block for new orgs.
`break_glass_sla_hours`	integer	yes	24
`default_audit_date_range_days`	integer	yes	7
`anomaly_high_volume_threshold`	integer	yes	50
`anomaly_business_hours_start`	time without time zone	yes	’08:00:00’::time without time zone
`anomaly_business_hours_end`	time without time zone	yes	’18:00:00’::time without time zone
`anomaly_same_patient_repeat_threshold`	integer	yes	10
`tefca_match_confidence_threshold`	numeric(3,2)	yes	0.95
`tefca_max_retry_attempts`	integer	yes	3
`tefca_retry_backoff_base_ms`	integer	yes	1000
`custom_fields`	jsonb	no	’{}‘::jsonb
`max_pathway_milestones`	integer	yes	50	Maximum number of milestones allowed per pathway template
`pathway_overdue_alert_hours`	integer	yes	24	Hours after milestone expected completion before overdue alert is generated
`standing_order_default_expiry_days`	integer	yes	90	CL-44: Default expiration period in days for clinical standing order protocols.
`order_set_max_items`	integer	yes	50	CL-44: Maximum number of items allowed in a single order set template.
`order_sets_enabled`	boolean	no	true	CL-44: Feature flag controlling visibility of Order Set / Standing Order navigation and activation entry points.
`cohort_recompute_schedule`	text	no	’daily’::text	CL-54 / spec NC-3: Cron cadence for cl-cohort-recompute edge function. Default daily 02:00 UTC.
`pop_health_assessment_expiry_days`	integer	yes	365	Days before a comprehensive assessment expires and triggers a care gap
`pop_health_risk_refresh_hours`	integer	yes	24	Minimum hours between automated risk stratification refreshes
`pop_health_risk_weights`	jsonb	yes	’{“moud”: 0.10, “safety”: 0.40, “outcome”: 0.30, “metabolic”: 0.20}‘::jsonb	Component weights for composite risk score (must sum to 1.0): safety, outcome, metabolic, moud
`pop_health_risk_tier_thresholds`	jsonb	yes	’{“low”: 0, “high”: 50, “medium”: 25, “critical”: 75}‘::jsonb	Numeric thresholds for risk tier assignment: low, medium, high, critical
`cda_generation_enabled`	boolean	no	false	CL-48 feature flag: when true, CDA generation UI is visible to users with cl.cda.* permissions. Defaults to false (opt-in per org).
`incident_deadline_track`	text	no	’azdhs’::text	CL-15 Phase 2: which regulatory clock to apply to incident reporting deadlines. ‘azdhs’ (default, preserves Phase 1 behavior), ‘ahcccs’ (AHCCCS Policy 961 business-day clock), or ‘dual’ (whichever is earlier). PF-96 migration note: Arizona-specific; future jurisdictions should resolve via pf_resolve_jurisdiction_profile().
`incident_business_day_config`	jsonb	no	’{“excludeWeekends”: true}‘::jsonb	CL-15 Phase 2: JSONB config for business-day math. Example: {“excludeWeekends”: true, “holidayCalendar”: “us_federal”}. Runtime shape validation lives in application-layer Zod (no immutable JSON CHECK). holidayCalendar deferred per CL-15-CONTEXT.md; initial release is weekday-only.
`incident_sentinel_options`	jsonb	no	’{}‘::jsonb	CL-15 Phase 2: JSONB config for sentinel-event handling. Example: {“autoEscalate”: true, “notifyRoles”: [“org_admin”, “compliance_officer”]}. Runtime shape validation lives in application-layer Zod.
`portal_qm_config`	jsonb	no	jsonb_build_object(‘instruments’, jsonb_build_array(‘phq9’, ‘gad7’, ‘pcl5’, ‘audit’, ‘dast10’, ‘whoqol_bref’, ‘brs’, ‘promis_global’), ‘measure_types’, jsonb_build_array(‘symptom’, ‘recovery’, ‘wellness’, ‘hedis’, ‘functional’), ‘sud_instruments_require_consent’, true)	CL-15 Phase 3 (T8): Patient portal quality-measure display config. Shape: {instruments: string[], measure_types: string[], sud_instruments_require_consent: boolean}. SUD instruments (audit, dast10, and others tagged in application layer SUD_INSTRUMENTS) are filtered out when sud_instruments_require_consent=true AND cl_check_sud_consent(chart_id, ‘outcome_measure’, auth.uid()) returns false. Runtime shape validation lives in application-layer Zod (no immutable JSON CHECK).
`weno_sync_interval_minutes`	integer	no	15	CL-06-EN-23: NewRx sync polling interval in minutes (5–120). Vendor: WENO EZ.
`weno_max_query_window_days`	integer	no	7	CL-06-EN-23: Maximum NewRx query window in days (1–14, vendor cap 7). Vendor: WENO EZ.
`weno_directory_delta_cron`	text	no	’0 2 * * *‘::text	CL-06-EN-23: Cron expression for daily pharmacy directory delta fetch.
`weno_directory_full_refresh_day`	integer	no	0	CL-06-EN-23: Day-of-week for full directory refresh (0=Sunday … 6=Saturday).
`telehealth_documentation_enabled`	boolean	no	false	CL-24 feature flag — when false, all CL-24 hooks short-circuit and routes redirect.
`telehealth_consent_validity_days`	integer	no	365	CL-24: telehealth consent expires_at = consented_at + this many days.
`telehealth_block_session_without_consent`	boolean	no	true	CL-24: when true, useStartTelehealthSession throws if consent invalid.
`telehealth_block_session_without_safety_checklist`	boolean	no	true	CL-24: when true, useStartTelehealthSession throws if safety_checklist_completed_at is NULL.

Foreign keys:

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

Policies:

cl_module_settings_delete (DELETE)
cl_module_settings_insert (INSERT)
cl_module_settings_select (SELECT)
cl_module_settings_update (UPDATE)

`cl_moud_enrollments`

CL-21: MAT/MOUD enrollment and treatment phase tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`enrollment_status`	text	no
`treatment_phase`	text	no
`primary_medication`	text	no
`started_on`	date	no
`ended_on`	date	yes
`consent_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata; do not store structured PHI; constitution §5.2.1
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
consent_id → cl_consents(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_moud_enrollments_delete (DELETE)
cl_moud_enrollments_insert (INSERT)
cl_moud_enrollments_select (SELECT)
cl_moud_enrollments_update (UPDATE)

`cl_moud_medication_events`

CL-21: MOUD medication events (ordered, prescribed, administered, dispensed, held, missed) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`enrollment_id`	uuid	no
`chart_id`	uuid	no
`medication_name`	text	no
`event_type`	text	no
`dose_value`	numeric	yes
`dose_unit`	text	yes
`route`	text	yes
`occurred_at`	timestamp with time zone	no
`adherence_status`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata; do not store structured PHI; constitution §5.2.1
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
enrollment_id → cl_moud_enrollments(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

cl_moud_medication_events_delete (DELETE)
cl_moud_medication_events_insert (INSERT)
cl_moud_medication_events_select (SELECT)
cl_moud_medication_events_update (UPDATE)

`cl_moud_monitoring_events`

CL-21: Monitoring protocol events (UDS, hepatic, etc.) with due and completion RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`enrollment_id`	uuid	no
`chart_id`	uuid	no
`monitoring_type`	text	no
`due_by`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`result_status`	text	yes
`result_summary`	jsonb	yes	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata; do not store structured PHI; constitution §5.2.1
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
enrollment_id → cl_moud_enrollments(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

cl_moud_monitoring_events_delete (DELETE)
cl_moud_monitoring_events_insert (INSERT)
cl_moud_monitoring_events_select (SELECT)
cl_moud_monitoring_events_update (UPDATE)

`cl_multi_party_encounters`

CL-55: Non-group multi-party encounters (mobile crisis, family therapy, interpreter-assisted, care team meetings). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`primary_chart_id`	uuid	no
`encounter_type`	text	no
`modality`	text	no
`started_at`	timestamp with time zone	no
`ended_at`	timestamp with time zone	yes
`status`	text	no	’in_progress’::text
`encounter_notes`	text	yes
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_multi_party_encounters_delete (DELETE)
cl_multi_party_encounters_insert (INSERT)
cl_multi_party_encounters_select (SELECT)
cl_multi_party_encounters_update (UPDATE)

`cl_multi_party_participants`

CL-55: Participants in multi-party encounters with role attribution and per-participant recording consent. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`encounter_id`	uuid	no
`participant_role`	text	no
`chart_id`	uuid	yes
`staff_user_id`	uuid	yes
`external_participant_name`	text	yes
`joined_at`	timestamp with time zone	yes
`left_at`	timestamp with time zone	yes
`recording_consent_captured_at`	timestamp with time zone	yes
`recording_consent_modality`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
encounter_id → cl_multi_party_encounters(id)
organization_id → pf_organizations(id)
staff_user_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

cl_multi_party_participants_delete (DELETE)
cl_multi_party_participants_insert (INSERT)
cl_multi_party_participants_select (SELECT)
cl_multi_party_participants_update (UPDATE)

`cl_note_940_override_audit`

CL-04-EN-66: INSERT-only audit log for Policy 940 validation overrides. No UPDATE or DELETE permitted. Retention: minimum 6 years per HIPAA 45 CFR 164.316(b)(2)(i). RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`note_id`	uuid	no
`override_by`	uuid	no
`override_reason`	text	no
`override_at`	timestamp with time zone	no	now()
`validation_result_snapshot`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no

Foreign keys:

created_by → users(id)
note_id → cl_progress_notes(id)
organization_id → pf_organizations(id)
override_by → users(id)

Policies:

cl_note_940_audit_insert_org (INSERT)
cl_note_940_audit_select_org (SELECT)

`cl_note_templates`

CL-04: Service-line note templates (outpatient, residential, iop_php). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_name`	text	no
`note_type`	text	no
`service_line`	text	yes
`template_structure`	jsonb	no	’{}‘::jsonb
`required_fields`	jsonb	no	’[]‘::jsonb
`is_active`	boolean	yes	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_note_templates_delete (DELETE)
cl_note_templates_insert (INSERT)
cl_note_templates_select (SELECT)
cl_note_templates_update (UPDATE)

`cl_note_treatment_goals`

CL-04: Junction between progress notes and treatment plan goals. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`note_id`	uuid	no
`treatment_goal_id`	uuid	no
`organization_id`	uuid	no
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
note_id → cl_progress_notes(id)
organization_id → pf_organizations(id)
treatment_goal_id → cl_treatment_goals(id)

Policies:

cl_note_treatment_goals_delete (DELETE)
cl_note_treatment_goals_insert (INSERT)
cl_note_treatment_goals_select (SELECT)
cl_note_treatment_goals_update (UPDATE)

`cl_notification_delivery_attempts`

CL-56: Per-alert delivery attempt log (dispatch, retry, escalation, acknowledge, error). PHI prohibited in metadata/error_message. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`event_id`	uuid	no
`attempt_number`	integer	no	1
`attempt_type`	text	no
`channel`	text	yes
`recipient_user_id`	uuid	yes
`recipient_role_key`	text	yes
`status`	text	no
`error_code`	text	yes
`error_message`	text	yes
`duration_ms`	integer	yes
`metadata`	jsonb	no	’{}‘::jsonb
`occurred_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()

Foreign keys:

event_id → cl_notification_events(id)
organization_id → pf_organizations(id)
recipient_user_id → pf_profiles(id)

Policies:

cl_notif_attempts_insert (INSERT)
cl_notif_attempts_select (SELECT)

`cl_notification_escalation_chains`

CL-56: Ordered escalation steps per severity tier; recipient_role_key + delay_seconds. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`severity`	text	no
`step_order`	integer	no
`recipient_role_key`	text	no
`delay_seconds`	integer	no	0
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_notification_escalation_chains_delete (DELETE)
cl_notification_escalation_chains_insert (INSERT)
cl_notification_escalation_chains_select (SELECT)
cl_notification_escalation_chains_update (UPDATE)

`cl_notification_events`

CL-56: Append-only fact table of dispatched clinical notifications with ack/escalation/SLA tracking. PHI prohibited in payload (chk_cl_notification_no_phi). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`signal_type`	text	no
`signal_source`	text	no
`source_record_id`	uuid	yes
`chart_id`	uuid	yes
`severity`	text	no
`policy_id`	uuid	yes
`dispatched_at`	timestamp with time zone	no	now()
`recipient_user_id`	uuid	yes
`recipient_role_key`	text	yes
`escalation_step`	integer	no	0
`acknowledged_at`	timestamp with time zone	yes
`acknowledged_by`	uuid	yes
`escalated_at`	timestamp with time zone	yes
`escalation_reason`	text	yes
`sla_deadline`	timestamp with time zone	no
`sla_breached_at`	timestamp with time zone	yes
`payload`	jsonb	no	’{}‘::jsonb	IDs and severity context only. Disallowed keys: patient_name, diagnosis, lab_value, narrative.
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

acknowledged_by → pf_profiles(id)
organization_id → pf_organizations(id)
policy_id → cl_notification_policies(id)
recipient_user_id → pf_profiles(id)

Policies:

cl_notification_events_delete (DELETE)
cl_notification_events_insert (INSERT)
cl_notification_events_select (SELECT)

`cl_notification_policies`

CL-56: Per-organization rules mapping clinical signal types to severity, SLA, and dedup window. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`signal_type`	text	no
`severity_rules`	jsonb	no
`sla_seconds`	integer	no
`dedup_window_seconds`	integer	no	300
`is_active`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

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

Policies:

cl_notification_policies_delete (DELETE)
cl_notification_policies_insert (INSERT)
cl_notification_policies_select (SELECT)
cl_notification_policies_update (UPDATE)

`cl_order_results`

CL-09: Individual result values for lab/diagnostic orders RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`order_id`	uuid	no
`chart_id`	uuid	no
`result_code`	text	yes
`result_name`	text	yes
`result_value`	text	yes
`result_value_numeric`	numeric	yes
`result_type`	text	no	’text’::text
`result_units`	text	yes
`reference_range`	text	yes
`abnormal_flag`	text	no	’normal’::text
`result_status`	text	no	’final’::text
`source`	text	no	’manual’::text	Result origin: manual (clinician entry) or interface (HL7v2/FHIR ingestion)
`performed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`review_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)
organization_id, order_id → cl_orders(organization_id, id)

Policies:

cl_order_results_delete (DELETE)
cl_order_results_insert (INSERT)
cl_order_results_select (SELECT)
cl_order_results_update (UPDATE)

`cl_order_set_activations`

CL-44: Per-patient activation log of an order set template; created orders captured in created_orders JSONB. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_id`	uuid	no
`chart_id`	uuid	no
`patient_id`	uuid	no		Cross-core reference to pm_patients.id. No DB FK per ADR-002; validated at application layer.
`activated_by`	uuid	no
`activated_at`	timestamp with time zone	no	now()
`selected_items`	jsonb	no
`modified_params`	jsonb	no	’{}‘::jsonb
`created_orders`	jsonb	no	’[]‘::jsonb
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

activated_by → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
template_id → cl_order_set_templates(id)
updated_by → pf_profiles(id)

Policies:

cl_order_set_activations_insert (INSERT)
cl_order_set_activations_select (SELECT)

`cl_order_set_templates`

CL-44: Order set template definitions (medications + labs + assessments + monitoring) with versioning and approval workflow. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`category`	text	no
`items`	jsonb	no	’[]‘::jsonb
`status`	text	no	’draft’::text
`version`	integer	no	1
`parent_version_id`	uuid	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
parent_version_id → cl_order_set_templates(id)
updated_by → pf_profiles(id)

Policies:

cl_order_set_templates_delete (DELETE)
cl_order_set_templates_insert (INSERT)
cl_order_set_templates_select (SELECT)
cl_order_set_templates_update (UPDATE)

`cl_order_templates`

CL-09: Reusable order templates (e.g., CMP panel, UDS panel) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_name`	text	no
`template_type`	text	no	’lab’::text
`description`	text	yes
`order_items`	jsonb	no	’[]‘::jsonb	Array of order item definitions: [{“loinc_code”: ”…”, “description”: ”…”, “specimen_type”: ”…”}]
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

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

Policies:

cl_order_templates_delete (DELETE)
cl_order_templates_insert (INSERT)
cl_order_templates_select (SELECT)
cl_order_templates_update (UPDATE)

`cl_orders`

CL-09: Lab and diagnostic orders linked to patient charts RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`ordering_provider_id`	uuid	no
`reference_lab_id`	uuid	yes
`site_id`	uuid	yes
`order_type`	text	no	’lab’::text
`status`	text	no	’draft’::text
`priority`	text	no	’routine’::text
`loinc_code`	text	yes
`order_description`	text	yes
`specimen_type`	text	yes
`clinical_notes`	text	yes
`external_order_id`	text	yes		Placer/filler order number for matching incoming HL7v2/FHIR results
`ordered_at`	timestamp with time zone	yes
`collected_at`	timestamp with time zone	yes
`resulted_at`	timestamp with time zone	yes
`cancelled_at`	timestamp with time zone	yes
`cancelled_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

created_by → pf_profiles(id)
loinc_code → cl_loinc_codes(loinc_code)
ordering_provider_id → pf_profiles(id)
organization_id → pf_organizations(id)
reference_lab_id → cl_reference_labs(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_orders_delete (DELETE)
cl_orders_insert (INSERT)
cl_orders_select (SELECT)
cl_orders_update (UPDATE)

`cl_outcome_measures`

CL-10: Patient-reported outcome measures (PROMs). One row per instrument administration per patient chart. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`measure_type`	text	no
`instrument`	text	no
`version`	text	yes
`score`	numeric(10,2)	no
`subscale_scores`	jsonb	yes	’{}‘::jsonb
`responses`	jsonb	yes	’{}‘::jsonb
`severity_level`	text	yes
`clinically_significant_change`	boolean	yes
`reliable_change_index`	numeric(8,4)	yes
`administered_at`	timestamp with time zone	no	now()
`administered_by`	uuid	yes
`administration_method`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

administered_by → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_outcome_measures_delete (DELETE)
cl_outcome_measures_insert (INSERT)
cl_outcome_measures_select (SELECT)
cl_outcome_measures_update (UPDATE)

`cl_pathway_definitions`

CL-08 EN-28: Clinical pathway templates RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`pathway_name`	text	no
`description`	text	yes
`condition_type`	text	no
`steps`	jsonb	no	’[]‘::jsonb
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

cl_pathway_definitions_delete (DELETE)
cl_pathway_definitions_insert (INSERT)
cl_pathway_definitions_select (SELECT)
cl_pathway_definitions_update (UPDATE)

`cl_pathway_milestones`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_pathway_id`	uuid	no
`milestone_template_id`	text	no
`name`	text	no
`sequence_order`	integer	no
`expected_completion_date`	timestamp with time zone	yes
`actual_completion_date`	timestamp with time zone	yes
`completed_by`	uuid	yes
`status`	text	no	’pending’::text
`linked_assessment_ids`	uuid[]	yes	’{}‘::uuid[]
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

completed_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
patient_pathway_id → cl_patient_pathways(id)
updated_by → pf_profiles(id)

Policies:

cl_pathway_milestones_delete (DELETE)
cl_pathway_milestones_insert (INSERT)
cl_pathway_milestones_select (SELECT)
cl_pathway_milestones_update (UPDATE)

`cl_pathway_progress`

CL-08 EN-28: Patient progress through pathways RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`pathway_id`	uuid	no
`current_step`	integer	no	1
`status`	text	no	’active’::text
`started_at`	timestamp with time zone	no	now()
`completed_at`	timestamp with time zone	yes
`step_history`	jsonb	no	’[]‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

cl_pathway_progress_delete (DELETE)
cl_pathway_progress_insert (INSERT)
cl_pathway_progress_select (SELECT)
cl_pathway_progress_update (UPDATE)

`cl_pathway_templates`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`clinical_program`	text	yes
`version`	integer	no	1
`parent_template_id`	uuid	yes
`status`	text	no	’draft’::text
`expected_total_duration_days`	integer	yes
`milestones`	jsonb	no	’[]‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

cl_pathway_templates_delete (DELETE)
cl_pathway_templates_insert (INSERT)
cl_pathway_templates_select (SELECT)
cl_pathway_templates_update (UPDATE)

`cl_pathway_variances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_pathway_id`	uuid	no
`milestone_id`	uuid	no
`reason_code`	text	no
`rationale`	text	yes
`documented_by`	uuid	no
`documented_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

documented_by → pf_profiles(id)
milestone_id → cl_pathway_milestones(id)
organization_id → pf_organizations(id)
patient_pathway_id → cl_patient_pathways(id)
updated_by → pf_profiles(id)

Policies:

cl_pathway_variances_delete (DELETE)
cl_pathway_variances_insert (INSERT)
cl_pathway_variances_select (SELECT)
cl_pathway_variances_update (UPDATE)

`cl_patient_charts`

CL-01: Patient clinical chart shell. One chart per patient per organization. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no
`chart_number`	text	no
`status`	text	no	’active’::text
`primary_site_id`	uuid	yes
`flags`	jsonb	no	’{}‘::jsonb	Clinical alert flags (e.g., {“fall_risk”: true, “suicide_risk”: “high”})
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific clinical metadata
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`current_risk_level`	text	yes		CL-07 Errata E-7: Denormalized current suicide risk level from latest cl_risk_screenings row. Updated by trg_cl_risk_screenings_update_chart_risk on screening INSERT/UPDATE. NULL = never screened.
`risk_last_screened_at`	timestamp with time zone	yes		CL-07 Errata E-7: Timestamp of latest risk screening. Updated by trigger on cl_risk_screenings INSERT/UPDATE.
`chart_summary_metadata`	jsonb	no	’{}‘::jsonb	AI summary readiness metadata for future AI-generated chart summary feature. Schema: {“ai_summary_ready”: false, “last_summary_at”: null, “version”: 0}. ai_summary_ready=true signals chart has sufficient structured clinical data for AI summarization. STORAGE PREP ONLY: do not store AI-generated summary text in this column in this phase. PHI AUDIT RULE: writers must NOT log cached summary content to pf_audit_logs; log only that an update occurred.
`assigned_clinician_id`	uuid	yes		CL-35: Primary assigned clinician for panel management
`cod_indicator`	boolean	no	false	CL-31: Marks chart as Co-Occurring Disorder.
`ai_drafting_opt_out`	boolean	no	false	CL-65 §Patient Rights: when true, the patient has opted out of AI-assisted note drafting; cl-ai-generate-draft denies generation (gate=patient_opted_out) and the clinician uses the non-AI workflow.

Foreign keys:

assigned_clinician_id → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
patient_id → pf_patient_identities(id)
primary_site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_patient_charts_delete (DELETE)
cl_patient_charts_insert (INSERT)
cl_patient_charts_select (SELECT)
cl_patient_charts_update (UPDATE)

`cl_patient_cohort_definitions`

CL-54: Immutable, append-only definition history per cohort. Each new edit creates a new version row; prior versions are frozen for snapshot reproducibility. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`cohort_id`	uuid	no
`version`	integer	no
`definition_type`	text	no
`criteria`	jsonb	yes		PF-32 ConditionGroup envelope: { version: 1, root: ConditionGroup }. Populated only for rule_based.
`manual_chart_ids`	uuid[]	yes		Explicit list of cl_patient_charts.id values. Populated only for manual.
`query_template`	text	yes		Reserved for future query_based cohorts (Phase 2).
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

cohort_id → cl_patient_cohorts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

cl_patient_cohort_definitions_insert (INSERT)
cl_patient_cohort_definitions_select (SELECT)

`cl_patient_cohort_memberships`

CL-54: Append-only snapshot table. One row per (cohort, version, chart, as_of_date). Idempotent: recompute for the same key tuple is a no-op. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`cohort_id`	uuid	no
`cohort_version`	integer	no
`chart_id`	uuid	no
`as_of_date`	date	no
`recomputed_at`	timestamp with time zone	no	now()

Foreign keys:

chart_id → cl_patient_charts(id)
cohort_id → cl_patient_cohorts(id)
organization_id → pf_organizations(id)

Policies:

cl_patient_cohort_memberships_delete (DELETE)
cl_patient_cohort_memberships_insert (INSERT)
cl_patient_cohort_memberships_select (SELECT)

`cl_patient_cohorts`

CL-54: Patient cohort registry. Each cohort is a versioned, named patient population definition (manual, rule-based, or query-based) used for HEDIS reporting, VBP attribution, and outreach campaigns. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`name`	text	no
`description`	text	yes
`definition_type`	text	no		How the cohort membership is computed: manual (explicit chart list), rule_based (PF-32 condition tree), query_based (deferred Phase 2).
`current_version`	integer	no	1	Pointer to the latest immutable cl_patient_cohort_definitions.version row for this cohort.
`status`	text	no	’draft’::text	Lifecycle state: draft (editable, no snapshots), active (snapshottable), archived (frozen).
`custom_fields`	jsonb	no	’{}‘::jsonb	Org-specific metadata; see CL custom_fields conventions.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_patient_cohorts_delete (DELETE)
cl_patient_cohorts_insert (INSERT)
cl_patient_cohorts_select (SELECT)
cl_patient_cohorts_update (UPDATE)

`cl_patient_pathways`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no		References pm_patients.id (PM-owned). UUID-only, no FK per ADR-002.
`template_id`	uuid	no
`enrolled_at`	timestamp with time zone	no	now()
`enrolled_by`	uuid	yes
`completed_at`	timestamp with time zone	yes
`withdrawn_at`	timestamp with time zone	yes
`withdrawn_reason`	text	yes
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
enrolled_by → pf_profiles(id)
organization_id → pf_organizations(id)
template_id → cl_pathway_templates(id)
updated_by → pf_profiles(id)

Policies:

cl_patient_pathways_delete (DELETE)
cl_patient_pathways_insert (INSERT)
cl_patient_pathways_select (SELECT)
cl_patient_pathways_update (UPDATE)

`cl_patient_submissions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`submission_type`	text	no
`submitted_at`	timestamp with time zone	no
`responses`	jsonb	no
`score_result`	jsonb	yes
`status`	text	no
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_patient_submissions_clinician_select (SELECT)
cl_patient_submissions_clinician_update (UPDATE)
cl_patient_submissions_portal_insert (INSERT)
cl_patient_submissions_portal_select (SELECT)

`cl_pdmp_configuration`

CL-17: Org-level PDMP gateway configuration. One row per organization. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`gateway_provider`	text	no
`api_endpoint`	text	yes
`credentials_vault_ref`	text	yes		Reference to platform vault. Sensitive — never expose in UI or logs.
`auto_query_enabled`	boolean	yes	true
`interstate_query_enabled`	boolean	yes	true
`delegate_access_enabled`	boolean	yes	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_pdmp_configuration_delete (DELETE)
cl_pdmp_configuration_insert (INSERT)
cl_pdmp_configuration_select (SELECT)
cl_pdmp_configuration_update (UPDATE)

`cl_pdmp_queries`

CL-17: PDMP query audit trail per A.R.S. § 36-2606. Immutable — no soft delete. PHI table. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`queried_by`	uuid	no
`queried_on_behalf_of`	uuid	yes
`query_datetime`	timestamp with time zone	no	now()
`patient_match_found`	boolean	yes
`results_summary`	jsonb	yes	’{}‘::jsonb	Structured PDMP response (risk indicators, Rx counts). PHI — protected by RLS.
`results_reviewed_by`	uuid	yes
`results_reviewed_at`	timestamp with time zone	yes
`prescriber_attestation`	boolean	yes	false
`prescription_id`	uuid	yes
`gateway_transaction_id`	text	yes		Vendor correlation ID for audit. Safe to log (non-PHI).
`gateway_provider`	text	yes
`interstate_query`	boolean	yes	false
`states_queried`	jsonb	yes	’[“AZ”]‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`status`	text	no	’pending’::text	Query lifecycle: pending, completed, failed, manual, unavailable_documented
`query_result_source`	text	yes		Source: gateway, manual_portal, cached, not_applicable
`query_method`	text	no	’electronic’::text	How initiated: electronic (EMR-triggered) or manual (user-initiated)

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
prescription_id → cl_prescriptions(id)
queried_by → pf_profiles(id)
queried_on_behalf_of → pf_profiles(id)
results_reviewed_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

cl_pdmp_queries_delete (DELETE)
cl_pdmp_queries_insert (INSERT)
cl_pdmp_queries_select (SELECT)
cl_pdmp_queries_update (UPDATE)

`cl_peer_encounters`

CL-40: Peer recovery support encounter documentation linked to PM encounters RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no
`encounter_id`	uuid	no
`peer_supporter_id`	uuid	no
`supervising_clinician_id`	uuid	yes
`encounter_date`	date	no
`duration_minutes`	integer	yes
`topics_discussed`	text[]	yes
`interventions_provided`	text	yes
`patient_engagement_score`	integer	yes
`peer_observations`	jsonb	yes		Structured peer observations (strengths, concerns, recovery capital)
`clinician_review_notes`	text	yes
`billed_as`	jsonb	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
encounter_id → pm_encounters(id)
organization_id → pf_organizations(id)
patient_id → pm_patients(id)
updated_by → pf_profiles(id)

Policies:

cl_peer_encounters_delete (DELETE)
cl_peer_encounters_insert (INSERT)
cl_peer_encounters_select (SELECT)
cl_peer_encounters_update (UPDATE)

`cl_pending_lab_results`

CL-09 Phase 2: Staging table for unsolicited lab results pending clinical review and linking RLS: ✅ enabled (5 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`lab_id`	uuid	no
`external_order_id`	text	yes
`loinc_code`	text	yes
`message_format`	text	yes
`message_control_id`	text	yes
`result_data`	jsonb	no		Array of MappedResult objects from HL7v2/FHIR ingestion
`raw_metadata`	jsonb	yes
`status`	text	no	’pending’::text
`linked_chart_id`	uuid	yes
`linked_order_id`	uuid	yes
`linked_by`	uuid	yes
`linked_at`	timestamp with time zone	yes
`rejection_reason`	text	yes
`expires_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

created_by → pf_profiles(id)
lab_id → cl_reference_labs(id)
linked_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
organization_id, linked_chart_id → cl_patient_charts(organization_id, id)
organization_id, linked_order_id → cl_orders(organization_id, id)

Policies:

cl_pending_lab_results_delete (DELETE)
cl_pending_lab_results_delete_org (DELETE)
cl_pending_lab_results_insert (INSERT)
cl_pending_lab_results_select (SELECT)
cl_pending_lab_results_update (UPDATE)

`cl_pharmacies`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`ncpdp_id`	text	yes
`phone`	text	yes
`fax`	text	yes
`is_active`	boolean	no	true
`accepts_epcs`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`address`	jsonb	yes

Policies:

cl_pharmacies_delete (DELETE)
cl_pharmacies_insert (INSERT)
cl_pharmacies_select (SELECT)
cl_pharmacies_update (UPDATE)

`cl_prescriptions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`prescriber_id`	uuid	no
`pharmacy_id`	uuid	yes
`medication_name`	text	no
`dosage`	text	yes
`route`	text	yes
`quantity`	integer	yes
`days_supply`	integer	yes	30
`is_controlled`	boolean	no	false
`schedule`	text	yes
`transmission_status`	text	no	’pending’::text
`surescripts_message_id`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`site_id`	uuid	yes
`refills_authorized`	integer	no	0
`refills_remaining`	integer	no	0
`epcs_token_used`	boolean	no	false
`rtpb_response`	jsonb	yes
`rtpb_checked`	boolean	no	false
`medication_id`	uuid	yes
`rx_number`	text	yes
`frequency`	text	yes
`notes`	text	yes

Foreign keys:

chart_id → cl_patient_charts(id)
medication_id → cl_medications(id)
pharmacy_id → cl_pharmacies(id)
site_id → pf_sites(id)

Policies:

cl_prescriptions_delete (DELETE)
cl_prescriptions_insert (INSERT)
cl_prescriptions_select (SELECT)
cl_prescriptions_update (UPDATE)

`cl_problem_history`

CL-46: Append-only audit trail for cl_problems status transitions. RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`problem_id`	uuid	no
`previous_status`	text	yes
`new_status`	text	no
`reason`	text	yes
`changed_by`	uuid	yes
`changed_at`	timestamp with time zone	no	now()

Foreign keys:

changed_by → pf_profiles(id)
organization_id → pf_organizations(id)
problem_id → cl_problems(id)

Policies:

cl_problem_history_select (SELECT)

`cl_problem_status_history`

RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`problem_id`	uuid	no
`previous_status`	text	yes
`new_status`	text	no
`reason`	text	yes
`changed_by`	uuid	yes
`changed_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
problem_id → cl_problems(id)

Policies:

cl_problem_status_history_select (SELECT)

`cl_problems`

CL-01: Patient problem list entries. Supports ICD-10 coding and clinical status tracking. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`icd10_code`	text	yes
`description`	text	no
`onset_date`	date	yes
`resolved_date`	date	yes
`status`	text	no	’active’::text
`severity`	text	no	’moderate’::text
`managing_provider_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`patient_id`	uuid	no		CL-46: Patient reference (pf_patient_identities.id).
`icd10_display`	text	no	”::text	CL-46: ICD-10-CM code description from PF-70.
`clinical_notes`	text	yes		CL-46: Clinician notes about the problem.
`recorded_by`	uuid	yes		CL-46: Clinician who recorded the problem.
`priority`	integer	yes	0
`resolution_reason`	text	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
managing_provider_id → pf_profiles(id)
organization_id → pf_organizations(id)
patient_id → pf_patient_identities(id)
recorded_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

cl_problems_delete (DELETE)
cl_problems_insert_v2 (INSERT)
cl_problems_select_v2 (SELECT)
cl_problems_update_v2 (UPDATE)

`cl_program_attendance_summaries`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_enrollment_id`	uuid	no
`period_start`	date	no
`period_end`	date	no
`hours_attended`	numeric	no	0
`hours_required`	numeric	no
`compliant`	boolean	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_program_attendance_summaries_delete (DELETE)
cl_program_attendance_summaries_insert (INSERT)
cl_program_attendance_summaries_select (SELECT)
cl_program_attendance_summaries_update (UPDATE)

`cl_program_enrollments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`program_schedule_id`	uuid	no
`enrolled_at`	date	no
`ended_at`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
program_schedule_id → cl_program_schedules(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)

Policies:

cl_program_enrollments_delete (DELETE)
cl_program_enrollments_insert (INSERT)
cl_program_enrollments_select (SELECT)
cl_program_enrollments_update (UPDATE)

`cl_program_outcomes`

CL-10: Program-level aggregated outcomes (HEDIS, dashboards). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_id`	uuid	yes		References rh_programs(id). NULL if org-wide aggregate.
`measure_type`	text	no
`measure_name`	text	no
`period_start`	date	no
`period_end`	date	no
`numerator`	integer	yes
`denominator`	integer	yes
`rate`	numeric(5,2)	yes
`benchmark`	numeric(5,2)	yes
`meets_benchmark`	boolean	yes
`calculation_method`	text	yes
`data_sources`	jsonb	yes	’[]‘::jsonb
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

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

Policies:

cl_program_outcomes_delete (DELETE)
cl_program_outcomes_insert (INSERT)
cl_program_outcomes_select (SELECT)
cl_program_outcomes_update (UPDATE)

`cl_program_schedules`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`name`	text	no
`program_type`	text	no
`hours_per_week_min`	numeric	no
`days_per_week_min`	integer	no
`schedule_definition`	jsonb	no
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_program_schedules_delete (DELETE)
cl_program_schedules_insert (INSERT)
cl_program_schedules_select (SELECT)
cl_program_schedules_update (UPDATE)

`cl_progress_notes`

CL-04: Progress notes per AHCCCS Policy 940; soft-delete via deleted_at. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`encounter_id`	uuid	yes
`note_type`	text	no
`service_line`	text	yes
`service_date`	date	no
`begin_time`	time without time zone	no
`end_time`	time without time zone	no
`duration_minutes`	integer	no
`diagnosis_code`	text	no
`presenting_problem`	text	no
`mse_findings`	text	yes
`interventions`	text	no
`member_response`	text	no
`plan_next_session`	text	yes
`is_telehealth`	boolean	yes	false
`telehealth_modality`	text	yes
`patient_location`	text	yes
`provider_location`	text	yes
`place_of_service`	text	yes
`signed_by`	uuid	yes
`signed_at`	timestamp with time zone	yes
`signer_credentials`	text	yes
`cosigned_by`	uuid	yes
`cosigned_at`	timestamp with time zone	yes
`cosigner_credentials`	text	yes
`status`	text	no	’draft’::text
`amended_from`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`ai_draft_indicator`	boolean	yes	false	EN-13: Note created via AI assist or dictation draft
`dictation_metadata`	jsonb	no	’{}‘::jsonb	EN-13: Audio consent, dictation provenance metadata
`quality_score`	numeric(5,2)	yes		EN-14: Per-note documentation quality score (range 0.00–100.00)
`note_940_validation_passed`	boolean	yes
`note_940_validation_result`	jsonb	yes	’{}‘::jsonb
`note_940_override_reason`	text	yes
`note_940_override_by`	uuid	yes
`note_940_validated_at`	timestamp with time zone	yes
`cod_session_type`	text	yes		CL-31: Null for non-COD; ‘cod_dual_diagnosis’ triggers SUD section UI gating.
`cod_sud_section`	jsonb	yes		CL-31: SUD documentation for dual-diagnosis sessions. Null when 42 CFR Part 2 consent absent.

Foreign keys:

amended_from → cl_progress_notes(id)
chart_id → cl_patient_charts(id)
cosigned_by → pf_profiles(id)
created_by → pf_profiles(id)
note_940_override_by → users(id)
organization_id → pf_organizations(id)
signed_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_progress_notes_delete (DELETE)
cl_progress_notes_insert (INSERT)
cl_progress_notes_select (SELECT)
cl_progress_notes_update (UPDATE)

`cl_quality_measure_periods`

CL-35: HEDIS and MA STARS quality measure period calculations RLS: ✅ enabled (8 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`measure_id`	text	no		Measure identifier (e.g., AMM, FUH, FUM)
`measure_type`	text	no
`period_start`	date	no
`period_end`	date	no
`denominator_count`	integer	no
`numerator_count`	integer	no
`rate`	numeric(5,4)	yes		Numerator/denominator rate 0.0000–1.0000
`metadata`	jsonb	no	’{}‘::jsonb	Calculation metadata and audit trail
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_qmp_delete (DELETE)
cl_qmp_insert (INSERT)
cl_qmp_select (SELECT)
cl_qmp_update (UPDATE)
cl_quality_measure_periods_insert (INSERT)
cl_quality_measure_periods_no_delete (DELETE)
cl_quality_measure_periods_select (SELECT)
cl_quality_measure_periods_update (UPDATE)

`cl_readmission_risk_scores`

CL-29: Readmission risk assessments (0-100 scale) with configurable risk factors RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`assessed_at`	date	no
`risk_score`	numeric	yes
`risk_factors`	jsonb	no	’[]‘::jsonb	Array of risk factor objects [{name, weight, present}]
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted

Foreign keys:

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

Policies:

cl_readmission_risk_scores_delete (DELETE)
cl_readmission_risk_scores_insert (INSERT)
cl_readmission_risk_scores_select (SELECT)
cl_readmission_risk_scores_update (UPDATE)

`cl_redisclosure_log`

CL-11-EN-01: Immutable append-only audit trail for 42 CFR Part 2 record disclosures. UPDATE/DELETE denied for all application roles. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`consent_id`	uuid	no
`patient_id`	uuid	no
`recipient`	text	no
`purpose`	text	no
`records_disclosed`	jsonb	no
`disclosed_at`	timestamp with time zone	no
`disclosed_by`	uuid	no
`created_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for redisclosure log extensibility

Foreign keys:

consent_id → cl_electronic_consents(id)
disclosed_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

cl_redisclosure_log_insert (INSERT)
cl_redisclosure_log_select (SELECT)

`cl_reference_labs`

CL-09: Reference laboratory directory with integration configuration RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`lab_name`	text	no
`clia_number`	text	yes
`npi`	text	yes
`address_line_1`	text	yes
`address_line_2`	text	yes
`city`	text	yes
`state`	text	yes
`zip_code`	text	yes
`phone`	text	yes
`fax`	text	yes
`contact_name`	text	yes
`contact_email`	text	yes
`integration_type`	text	no	’manual’::text
`connection_config`	jsonb	no	’{}‘::jsonb	Integration credentials and settings (e.g., {“api_key”: ”…”, “allowed_ips”: […], “rate_limit”: 100})
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

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

Policies:

cl_reference_labs_delete (DELETE)
cl_reference_labs_insert (INSERT)
cl_reference_labs_select (SELECT)
cl_reference_labs_update (UPDATE)

`cl_reference_ranges`

CL-09 EN-32: Organization-specific reference ranges for lab result interpretation RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`loinc_code`	text	no		LOINC code for the test (text, resolved via PF-70 at runtime)
`display_name`	text	yes
`sex`	text	yes
`age_min_years`	numeric(5,1)	yes
`age_max_years`	numeric(5,1)	yes
`low_value`	numeric(12,4)	yes
`high_value`	numeric(12,4)	yes
`critical_low`	numeric(12,4)	yes
`critical_high`	numeric(12,4)	yes
`units`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_reference_ranges_delete (DELETE)
cl_reference_ranges_insert (INSERT)
cl_reference_ranges_select (SELECT)
cl_reference_ranges_update (UPDATE)

`cl_referral_status_history`

CL-12-EN-67: Append-only referral status history for closed-loop tracking RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`referral_id`	uuid	no		Bare UUID referencing pm_referrals.id (no FK per cross-core rules)
`status`	text	no
`patient_connected`	boolean	yes
`patient_connected_at`	timestamp with time zone	yes
`external_status_source`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

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

Policies:

cl_referral_status_history_delete (DELETE)
cl_referral_status_history_insert (INSERT)
cl_referral_status_history_select (SELECT)

`cl_report_definitions`

CL-15: Configurable clinical compliance report definitions (Joint Commission, CARF, HEDIS, AHCCCS, AZDHS, custom) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_name`	text	no
`report_type`	text	no
`measure_ids`	jsonb	yes	’[]‘::jsonb
`filters`	jsonb	yes	’{}‘::jsonb
`schedule_cron`	text	yes
`recipients`	jsonb	yes	’[]‘::jsonb
`format`	text	yes	’pdf’::text
`is_active`	boolean	yes	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“external_report_id”: “RPT-001”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_report_definitions_delete (DELETE)
cl_report_definitions_insert (INSERT)
cl_report_definitions_select (SELECT)
cl_report_definitions_update (UPDATE)

`cl_report_runs`

CL-15: Execution log for report generation runs; tracks status, parameters, and output document reference RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`report_definition_id`	uuid	yes
`organization_id`	uuid	no
`run_started_at`	timestamp with time zone	no	now()
`run_completed_at`	timestamp with time zone	yes
`status`	text	no	’running’::text
`parameters`	jsonb	yes	’{}‘::jsonb
`result_summary`	jsonb	yes	’{}‘::jsonb
`document_id`	uuid	yes
`generated_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

generated_by → pf_profiles(id)
organization_id → pf_organizations(id)
report_definition_id → cl_report_definitions(id)

Policies:

cl_report_runs_delete (DELETE)
cl_report_runs_insert (INSERT)
cl_report_runs_select (SELECT)
cl_report_runs_update (UPDATE)

`cl_review_appeals`

CL-43: Tracks appeals filed against denied concurrent reviews. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`review_id`	uuid	no
`appeal_level`	text	no
`appeal_date`	date	no
`appeal_deadline`	date	yes
`status`	text	no	’draft’::text
`outcome_date`	date	yes
`outcome_notes`	text	yes
`supporting_documents`	jsonb	yes	’[]‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

organization_id → pf_organizations(id)
review_id → cl_concurrent_reviews(id)

Policies:

cl_review_appeals_delete (DELETE)
cl_review_appeals_insert (INSERT)
cl_review_appeals_select (SELECT)
cl_review_appeals_update (UPDATE)

`cl_review_schedules`

CL-43: Payer-specific concurrent review interval configuration. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payer_id`	uuid	yes
`payer_name`	text	no
`level_of_care`	text	no
`review_interval_days`	integer	no
`initial_review_days`	integer	yes
`grace_period_days`	integer	no	0
`escalation_threshold_days`	integer	no	1
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

organization_id → pf_organizations(id)

Policies:

cl_review_schedules_delete (DELETE)
cl_review_schedules_insert (INSERT)
cl_review_schedules_select (SELECT)
cl_review_schedules_update (UPDATE)

`cl_risk_screenings`

CL-07: Suicide risk screening records. Instruments: C-SSRS, PHQ-9 Item 9, ASQ, SAFE-T. SAFE-T score column is NULL by design (non-numeric instrument; FR-1.6). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`instrument`	text	no
`score`	numeric	yes		Numeric score for C-SSRS, PHQ-9 Item 9, and ASQ. NULL for SAFE-T (non-numeric; clinician sets risk_level directly).
`risk_level`	text	no
`responses`	jsonb	no	’{}‘::jsonb	Instrument-specific response data (e.g., {“ideation_q1”: 1, “ideation_q2”: 0, …}).
`screened_by`	uuid	no
`screened_at`	timestamp with time zone	no	now()
`next_screening_due`	timestamp with time zone	yes		Calculated rescreening due date: high/imminent=+24h, moderate=+72h, low=+low_risk_rescreen_interval_days.
`trigger_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific screening metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
screened_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

cl_risk_screenings_delete (DELETE)
cl_risk_screenings_insert (INSERT)
cl_risk_screenings_select (SELECT)
cl_risk_screenings_update (UPDATE)

`cl_risk_stratifications`

CL-35: Patient risk stratification scores computed by the population health engine RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`stratification_date`	timestamp with time zone	no	now()
`composite_risk_score`	numeric(5,2)	no		Weighted composite score 0.00–100.00
`risk_tier`	text	no		Derived tier: low/medium/high/critical
`risk_factors`	jsonb	no	’{}‘::jsonb	JSONB breakdown of individual risk factor weights and values
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_risk_strat_delete (DELETE)
cl_risk_strat_insert (INSERT)
cl_risk_strat_select (SELECT)
cl_risk_strat_update (UPDATE)

`cl_safety_plan_shares`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`safety_plan_id`	uuid	no
`shared_with_name`	text	no
`shared_with_contact`	text	yes
`share_method`	text	no
`consent_documented`	boolean	no	false
`consent_date`	timestamp with time zone	yes
`shared_at`	timestamp with time zone	no	now()
`shared_by`	uuid	no
`expires_at`	timestamp with time zone	yes
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
safety_plan_id → cl_safety_plans(id)
shared_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

rls_cl_safety_plan_shares_delete (DELETE)
rls_cl_safety_plan_shares_insert (INSERT)
rls_cl_safety_plan_shares_select (SELECT)
rls_cl_safety_plan_shares_update (UPDATE)

`cl_safety_plans`

CL-07: Stanley-Brown safety plans with 6 components. Patient/clinician signatures via SignatureCanvas (Pattern A — Errata E-6). Versioning via superseded_by + status. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`warning_signs`	jsonb	no	’[]‘::jsonb	Stanley-Brown component 1: Warning signs (thoughts, images, mood, situation, behavior). Array of strings.
`coping_strategies`	jsonb	no	’[]‘::jsonb	Stanley-Brown component 2: Internal coping strategies. Array of strings.
`social_contacts`	jsonb	no	’[]‘::jsonb	Stanley-Brown component 3: People and social settings for distraction. Array of {name, details}.
`family_friends`	jsonb	no	’[]‘::jsonb	Stanley-Brown component 4: People to ask for help (family/friends). Array of {name, phone}.
`professionals`	jsonb	no	’[]‘::jsonb	Stanley-Brown component 5: Professionals or agencies to contact. Array of {name, phone, hours}.
`means_restriction`	jsonb	no	’{}‘::jsonb	Stanley-Brown component 6: Making the environment safe (means restriction). Object with lethal_means details.
`lethal_means_counseling_completed`	boolean	no	false
`patient_signed_at`	timestamp with time zone	yes
`clinician_signed_at`	timestamp with time zone	yes
`patient_signature_data`	jsonb	yes		Errata E-6: base64 SignatureCanvas image for patient signature. NULL until signed.
`clinician_signature_data`	jsonb	yes		Errata E-6: base64 SignatureCanvas image for clinician signature. NULL until signed.
`clinician_id`	uuid	yes
`status`	text	no	’active’::text	Plan lifecycle status. active = current plan; superseded = replaced by newer version.
`version`	integer	no	1
`superseded_by`	uuid	yes		FK to the newer plan version that replaced this one. NULL if still active.
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific safety plan metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.
`lethal_means_discussed_at`	timestamp with time zone	yes		EN-23: Timestamp when lethal means counseling was discussed
`lethal_means_outcome`	text	yes		EN-23: Outcome — discussed, declined, follow-up, not_applicable
`lethal_means_notes`	text	yes		EN-23: Free-text notes from lethal means counseling session

Foreign keys:

chart_id → cl_patient_charts(id)
clinician_id → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
superseded_by → cl_safety_plans(id)
updated_by → pf_profiles(id)

Policies:

cl_safety_plans_delete (DELETE)
cl_safety_plans_insert (INSERT)
cl_safety_plans_select (SELECT)
cl_safety_plans_update (UPDATE)

`cl_screening_administrations`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patient_id`	uuid	no
`chart_id`	uuid	no
`encounter_id`	uuid	yes
`instrument_code`	text	no
`administered_at`	timestamp with time zone	no	now()
`total_score`	numeric(6,2)	no	0
`severity`	text	no	”::text
`item_responses`	jsonb	no	’{}‘::jsonb
`score_result`	jsonb	no	’{}‘::jsonb
`administered_by`	uuid	no
`notes`	text	yes
`alert_triggered`	boolean	no	false
`alert_acknowledged_at`	timestamp with time zone	yes
`alert_acknowledged_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

organization_id → pf_organizations(id)

Policies:

cl_screening_admin_delete (DELETE)
cl_screening_admin_insert (INSERT)
cl_screening_admin_select (SELECT)
cl_screening_admin_update (UPDATE)

`cl_sdoh_screenings`

CL-18: SDOH screening records (PRAPARE, AHC-HRSN, etc.) linked to patient charts RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`instrument`	text	no
`screened_at`	timestamp with time zone	no
`screened_by`	uuid	yes
`responses`	jsonb	no	’{}‘::jsonb
`needs_identified`	text[]	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“screening_version”: “2.0”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
screened_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

cl_sdoh_screenings_delete (DELETE)
cl_sdoh_screenings_insert (INSERT)
cl_sdoh_screenings_select (SELECT)
cl_sdoh_screenings_update (UPDATE)

`cl_social_referrals`

CL-18: Community resource referrals for identified social needs RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`screening_id`	uuid	yes
`resource_name`	text	no
`resource_type`	text	yes
`need_category`	text	yes
`status`	text	no	’referred’::text
`referred_at`	timestamp with time zone	no	now()
`referred_by`	uuid	yes
`outcome_notes`	text	yes
`outcome_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
referred_by → pf_profiles(id)
screening_id → cl_sdoh_screenings(id)
updated_by → pf_profiles(id)

Policies:

cl_social_referrals_delete (DELETE)
cl_social_referrals_insert (INSERT)
cl_social_referrals_select (SELECT)
cl_social_referrals_update (UPDATE)

`cl_standing_order_executions`

CL-44: Append-only nursing execution log for standing order protocols. UPDATE/DELETE forbidden via restrictive RLS. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`standing_order_id`	uuid	no
`chart_id`	uuid	no
`patient_id`	uuid	no		Cross-core reference to pm_patients.id. No DB FK per ADR-002; validated at application layer.
`executed_by`	uuid	no
`executed_at`	timestamp with time zone	no	now()
`action_taken`	text	no
`parameters_used`	jsonb	no
`patient_response`	text	yes
`outcome`	text	yes
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
executed_by → pf_profiles(id)
organization_id → pf_organizations(id)
standing_order_id → cl_clinical_standing_orders(id)

Policies:

cl_standing_order_executions_insert (INSERT)
cl_standing_order_executions_no_delete (DELETE)
cl_standing_order_executions_no_update (UPDATE)
cl_standing_order_executions_select (SELECT)

`cl_standing_order_protocols`

CL-09 EN-30: Standing order protocols for automated/recurring lab orders RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`template_id`	uuid	yes
`order_items`	jsonb	yes
`recurrence_rule`	jsonb	no	’{}‘::jsonb
`is_active`	boolean	no	true
`patient_criteria`	jsonb	yes	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_standing_order_protocols_delete (DELETE)
cl_standing_order_protocols_insert (INSERT)
cl_standing_order_protocols_select (SELECT)
cl_standing_order_protocols_update (UPDATE)

`cl_tefca_exchange_log`

CL-16-EN-01: Append-only audit log tracking inbound and outbound TEFCA/QHIN exchange attempts and outcomes RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`patient_id`	uuid	yes		Patient reference (nullable for unmatched inbound searches)
`direction`	text	no
`qhin_identifier`	text	no
`purpose_of_use`	text	no		TEFCA purpose of use per Common Agreement: treatment, payment, operations, public_health, government_benefits_determination, individual_access_services
`document_type`	text	yes
`consent_id`	uuid	yes
`correlation_id`	text	no		External exchange correlation key for tracing across QHIN transport
`status`	text	no
`failure_reason`	text	yes		Structured failure reason (no PHI); e.g. timeout, auth_failure, consent_denied
`exchanged_at`	timestamp with time zone	no
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for extensibility
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_tefca_exchange_log_insert (INSERT)
cl_tefca_exchange_log_select (SELECT)
cl_tefca_exchange_log_service (ALL)
cl_tefca_exchange_log_update (UPDATE)

`cl_telehealth_consents`

CL-24: Per-patient telehealth consent with annual renewal. Immutable for audit; no deleted_at. Revoke is append-only for non-admins via trigger. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`consent_type`	text	no		CL-24: telehealth_general implies audio_only; audio_only does not imply general (see CONTEXT.md).
`consented_at`	timestamp with time zone	no
`expires_at`	timestamp with time zone	no
`revoked_at`	timestamp with time zone	yes
`revoked_reason`	text	yes
`consent_method`	text	no
`consent_document_ref`	text	yes
`consent_language_version`	text	no		CL-24: jurisdiction-aware consent language lineage. AZ-EN-2026-01 = Arizona ARS 13-3005 compliant.
`captured_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

captured_by → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

cl_telehealth_consents_delete (DELETE)
cl_telehealth_consents_insert (INSERT)
cl_telehealth_consents_select (SELECT)
cl_telehealth_consents_update (UPDATE)

`cl_telehealth_sessions`

CL-24: Clinical compliance wrapper around a PM-13 telehealth session. Holds safety checklist, jurisdictional state, connectivity log. Immutable for audit; no deleted_at. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`pm_telehealth_session_id`	uuid	yes		CL-24: UUID-only reference to pm_telehealth_sessions.id; no FK across cores.
`progress_note_id`	uuid	yes
`session_at`	timestamp with time zone	no
`modality`	text	no
`patient_state`	character(2)	yes
`provider_state`	character(2)	yes
`patient_location_type`	text	yes
`technology_verified`	boolean	no	false
`technology_verification_notes`	text	yes
`connectivity_issues`	jsonb	no	’[]‘::jsonb	CL-24: append-only JSONB array of {timestamp, type, duration_minutes, resolution}. App caps length at 50.
`patient_current_location_verified`	boolean	no	false
`local_emergency_number`	text	yes
`support_person_on_site`	boolean	yes
`support_person_name`	text	yes
`disconnect_protocol_acknowledged`	boolean	no	false
`safety_checklist_completed_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
progress_note_id → cl_progress_notes(id)
updated_by → pf_profiles(id)

Policies:

cl_telehealth_sessions_delete (DELETE)
cl_telehealth_sessions_insert (INSERT)
cl_telehealth_sessions_select (SELECT)
cl_telehealth_sessions_update (UPDATE)

`cl_transitions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`chart_id`	uuid	no
`site_id`	uuid	yes
`transition_type`	text	no
`transition_date`	date	no
`from_level_of_care`	text	yes
`to_level_of_care`	text	yes
`from_facility`	text	yes
`to_facility`	text	yes
`reason`	text	yes
`medication_reconciliation_id`	uuid	yes
`safety_plan_updated`	boolean	no	false
`referral_id`	uuid	yes
`discharge_summary_id`	uuid	yes
`fuh_eligible`	boolean	no	false
`fuh_exclusion_reason`	text	yes
`fuh_7day_met`	boolean	no	false
`fuh_30day_met`	boolean	no	false
`followup_scheduled`	boolean	no	false
`followup_date`	date	yes
`followup_completed_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
organization_id, chart_id → cl_patient_charts(organization_id, id)
organization_id, medication_reconciliation_id → cl_medication_reconciliations(organization_id, id)

Policies:

cl_transitions_delete (DELETE)
cl_transitions_insert (INSERT)
cl_transitions_select (SELECT)
cl_transitions_update (UPDATE)

`cl_treatment_goal_bank`

CL-03 Phase 3: Pre-built goal entries associated with treatment plan templates RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_id`	uuid	no
`goal_number`	integer	no
`goal_text_patient_words`	text	no		Goal text in patient-friendly language
`measurable_criteria`	text	no
`frequency`	text	no
`duration`	text	no
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_treatment_goal_bank_delete (DELETE)
cl_treatment_goal_bank_insert (INSERT)
cl_treatment_goal_bank_select (SELECT)
cl_treatment_goal_bank_update (UPDATE)

`cl_treatment_goals`

CL-03: Treatment goals in patient words per AHCCCS Person-Centered Planning. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`plan_id`	uuid	no
`problem_id`	uuid	yes
`goal_number`	integer	yes
`goal_text_patient_words`	text	no
`measurable_criteria`	text	no
`frequency`	text	no
`duration`	text	no
`target_date`	date	yes
`status`	text	no	’not_started’::text
`progress_pct`	integer	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
plan_id → cl_treatment_plans(id)
problem_id → cl_problems(id)
updated_by → pf_profiles(id)

Policies:

cl_treatment_goals_delete (DELETE)
cl_treatment_goals_insert (INSERT)
cl_treatment_goals_select (SELECT)
cl_treatment_goals_update (UPDATE)

`cl_treatment_interventions`

CL-03: Specific interventions linked to treatment goals. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`goal_id`	uuid	no
`intervention_description`	text	no
`responsible_provider_id`	uuid	yes
`frequency`	text	yes
`modality`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
goal_id → cl_treatment_goals(id)
organization_id → pf_organizations(id)
responsible_provider_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

cl_treatment_interventions_delete (DELETE)
cl_treatment_interventions_insert (INSERT)
cl_treatment_interventions_select (SELECT)
cl_treatment_interventions_update (UPDATE)

`cl_treatment_plan_signatures`

CL-03: Immutable multi-disciplinary sign-off records. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`treatment_plan_id`	uuid	no
`signer_id`	uuid	no
`signer_role`	text	no
`signed_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“badge_number”: “B-456”})

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
signer_id → pf_profiles(id)
treatment_plan_id → cl_treatment_plans(id)
updated_by → pf_profiles(id)

Policies:

cl_treatment_plan_signatures_delete (DELETE)
cl_treatment_plan_signatures_insert (INSERT)
cl_treatment_plan_signatures_select (SELECT)
cl_treatment_plan_signatures_update (UPDATE)

`cl_treatment_plan_templates`

CL-03 Phase 3: Pre-built treatment plan templates by service line/diagnosis with goal banks RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`service_line`	text	yes
`description`	text	yes
`template_snapshot`	jsonb	yes	’{}‘::jsonb	JSONB snapshot of template structure (plan fields, default values)
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_treatment_plan_templates_delete (DELETE)
cl_treatment_plan_templates_insert (INSERT)
cl_treatment_plan_templates_select (SELECT)
cl_treatment_plan_templates_update (UPDATE)

`cl_treatment_plans`

CL-03: Treatment plans with AHCCCS review frequency auto-calculation. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`plan_type`	text	no	’initial’::text
`service_line`	text	yes
`status`	text	no	’draft’::text
`effective_date`	date	yes
`review_due_date`	date	yes
`patient_signed`	boolean	yes	false
`patient_agrees`	boolean	yes	true
`patient_signed_at`	timestamp with time zone	yes
`clinician_signed_at`	timestamp with time zone	yes
`clinician_signed_by`	uuid	yes
`version`	integer	no	1
`revision_reason`	text	yes
`previous_plan_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`caregiver_participation_at`	timestamp with time zone	yes		CL-03 Phase 2: Timestamp when family/caregiver participated in plan development or review
`caregiver_role`	text	yes		CL-03 Phase 2: Role of caregiver (e.g. parent, spouse, guardian, peer). No PHI.

Foreign keys:

chart_id → cl_patient_charts(id)
clinician_signed_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
previous_plan_id → cl_treatment_plans(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_treatment_plans_delete (DELETE)
cl_treatment_plans_insert (INSERT)
cl_treatment_plans_select (SELECT)
cl_treatment_plans_update (UPDATE)

`cl_uds_interpretation_notes`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`uds_order_id`	uuid	no
`chart_id`	uuid	no
`encounter_id`	uuid	yes
`interpretation_summary`	text	yes
`counseling_provided`	boolean	no	false
`counseling_notes`	text	yes
`follow_up_plan`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → users(id)
encounter_id → pm_encounters(id)
organization_id → pf_organizations(id)
uds_order_id → cl_uds_orders(id)
updated_by → users(id)

Policies:

cl_uds_interp_delete (DELETE)
cl_uds_interp_insert (INSERT)
cl_uds_interp_select (SELECT)
cl_uds_interp_update (UPDATE)

`cl_uds_orders`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`encounter_id`	uuid	yes
`cl_order_id`	uuid	yes
`order_type`	text	no	’presumptive’::text
`panel_type`	text	no	’standard_5_panel’::text
`status`	text	no	’ordered’::text
`observed`	boolean	no	false
`collector_id`	uuid	yes
`is_external`	boolean	no	false
`external_collector_name`	text	yes
`collection_time`	timestamp with time zone	yes
`specimen_id`	text	yes
`temperature_at_collection`	numeric(5,1)	yes
`ordered_at`	timestamp with time zone	no	now()
`collected_at`	timestamp with time zone	yes
`sent_to_lab_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
cl_order_id → cl_orders(id)
collector_id → users(id)
created_by → users(id)
encounter_id → pm_encounters(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

cl_uds_orders_delete (DELETE)
cl_uds_orders_insert (INSERT)
cl_uds_orders_select (SELECT)
cl_uds_orders_update (UPDATE)

`cl_uds_results`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`uds_order_id`	uuid	no
`chart_id`	uuid	no
`result_date`	timestamp with time zone	no	now()
`drug_class`	text	no
`result`	text	no
`concentration`	numeric(10,2)	yes
`expected_from_medication`	boolean	yes	false
`court_reportable`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

chart_id → cl_patient_charts(id)
created_by → users(id)
organization_id → pf_organizations(id)
uds_order_id → cl_uds_orders(id)
updated_by → users(id)

Policies:

cl_uds_results_delete (DELETE)
cl_uds_results_insert (INSERT)
cl_uds_results_select (SELECT)
cl_uds_results_update (UPDATE)

`cl_user_icd10_favorites`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`user_id`	uuid	no
`organization_id`	uuid	no
`icd10_code`	text	no
`description`	text	no	”::text
`use_count`	integer	no	1
`last_used_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
user_id → users(id)

Policies:

cl_user_icd10_favorites_delete (DELETE)
cl_user_icd10_favorites_insert (INSERT)
cl_user_icd10_favorites_select (SELECT)
cl_user_icd10_favorites_update (UPDATE)

`cl_vital_signs`

CL-22: Vital signs measurements (BP, HR, RR, temp, SpO2, pain) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`captured_at`	timestamp with time zone	no
`systolic_bp`	integer	yes
`diastolic_bp`	integer	yes
`heart_rate`	integer	yes
`respiratory_rate`	integer	yes
`temperature_c`	numeric	yes
`spo2_percent`	numeric	yes
`pain_score`	integer	yes
`source`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata; do not store structured PHI; constitution §5.2.1
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_vital_signs_delete (DELETE)
cl_vital_signs_insert (INSERT)
cl_vital_signs_select (SELECT)
cl_vital_signs_update (UPDATE)

`cl_warm_handoffs`

CL-29: Provider-to-provider warm handoff documentation with caller/receiver tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`chart_id`	uuid	no
`transition_id`	uuid	no		Link to cl_care_transitions(id) when CL-12 schema exists; plain UUID, no FK constraint
`caller_id`	uuid	no
`receiver_id`	uuid	no
`accepted_at`	timestamp with time zone	yes
`acceptance_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active, non-NULL = soft deleted

Foreign keys:

caller_id → pf_profiles(id)
chart_id → cl_patient_charts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
receiver_id → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

cl_warm_handoffs_delete (DELETE)
cl_warm_handoffs_insert (INSERT)
cl_warm_handoffs_select (SELECT)
cl_warm_handoffs_update (UPDATE)

`cl_weno_directory_ingest_runs`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`run_mode`	text	no
`status`	text	no	’pending’::text
`record_count`	integer	no	0
`drift_columns`	jsonb	no	’[]‘::jsonb
`file_hash`	text	yes
`error_code`	text	yes
`error_category`	text	yes
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`metadata`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_weno_directory_ingest_runs_delete (DELETE)
cl_weno_directory_ingest_runs_insert (INSERT)
cl_weno_directory_ingest_runs_select (SELECT)
cl_weno_directory_ingest_runs_update (UPDATE)

`cl_weno_launch_attempts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	yes
`encounter_id`	uuid	yes
`endpoint_type`	text	no
`status`	text	no	’pending’::text
`correlation_id`	uuid	no	gen_random_uuid()
`error_code`	text	yes
`error_category`	text	yes
`metadata`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_weno_launch_attempts_delete (DELETE)
cl_weno_launch_attempts_insert (INSERT)
cl_weno_launch_attempts_select (SELECT)
cl_weno_launch_attempts_update (UPDATE)

`cl_weno_newrx_sync_runs`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vendor_report_id`	text	yes
`window_start`	timestamp with time zone	yes
`window_end`	timestamp with time zone	yes
`status`	text	no	’pending’::text
`parsed_count`	integer	no	0
`dedupe_count`	integer	no	0
`emitted_event_count`	integer	no	0
`error_code`	text	yes
`error_category`	text	yes
`metadata`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

cl_weno_newrx_sync_runs_delete (DELETE)
cl_weno_newrx_sync_runs_insert (INSERT)
cl_weno_newrx_sync_runs_select (SELECT)
cl_weno_newrx_sync_runs_update (UPDATE)

Community Engagement Schema Reference Finance & Accounting Schema Reference

​Entity relationships

​Tables

​cl_988_transfers

​cl_aftercare_plans

​cl_ai_documentation_sessions

​cl_ai_note_attributions

​cl_allergies

​cl_allergy_alert_overrides

​cl_ambient_session_events

​cl_ambient_sessions

​cl_ambient_transcripts

​cl_assessment_instruments

​cl_assessment_sections

​cl_assessment_template_sections

​cl_assessment_templates

​cl_assessments

​cl_audit_dashboard_break_glass_reviews

​cl_between_session_checkins

​cl_biometrics

​cl_care_gaps

​cl_care_teams

​cl_cda_documents

​cl_cda_transmission_endpoints

​cl_cds_alerts

​cl_cds_rules

​cl_clinical_standing_orders

​cl_cod_assessments

​cl_cod_treatment_plan_extension

​cl_concurrent_reviews

​cl_consent_requests

​cl_consents

​cl_controlled_substance_inventory

​cl_cron_run_log

​cl_data_exchange_log

​cl_ddi_alert_suppressions

​cl_ddi_overrides

​cl_designated_contacts

​cl_diagnoses

​cl_discharge_checklists

​cl_disclosure_log

​cl_dsi_disclosures

​cl_electronic_consents

​cl_environmental_assessments

​cl_family_notification_log

​cl_fhir_resources

​cl_follow_up_contacts

​cl_group_attendance

​cl_group_curricula

​cl_group_encounter_generations

​cl_group_session_telehealth_metadata

​cl_group_sessions

​cl_inbasket_item_activity

​cl_inbasket_items

​cl_incidents

​cl_intake_assessments

​cl_lethal_means_assessments

​cl_loc_assessments

​cl_loinc_codes

​cl_marketplace_bundle_items

​cl_marketplace_bundles

​cl_marketplace_imports

​cl_medication_adherence

​cl_medication_education

​cl_medication_reconciliations

​cl_medications

​cl_metabolic_monitoring_events

​cl_module_settings

​cl_moud_enrollments

​cl_moud_medication_events

​cl_moud_monitoring_events

​cl_multi_party_encounters

​cl_multi_party_participants

​cl_note_940_override_audit

​cl_note_templates

​cl_note_treatment_goals

​cl_notification_delivery_attempts

​cl_notification_escalation_chains

​cl_notification_events

​cl_notification_policies

​cl_order_results

Entity relationships

Tables

`cl_988_transfers`

`cl_aftercare_plans`

`cl_ai_documentation_sessions`

`cl_ai_note_attributions`

`cl_allergies`

`cl_allergy_alert_overrides`

`cl_ambient_session_events`

`cl_ambient_sessions`

`cl_ambient_transcripts`

`cl_assessment_instruments`

`cl_assessment_sections`

`cl_assessment_template_sections`

`cl_assessment_templates`

`cl_assessments`

`cl_audit_dashboard_break_glass_reviews`

`cl_between_session_checkins`

`cl_biometrics`

`cl_care_gaps`

`cl_care_teams`

`cl_cda_documents`

`cl_cda_transmission_endpoints`

`cl_cds_alerts`

`cl_cds_rules`

`cl_clinical_standing_orders`

`cl_cod_assessments`

`cl_cod_treatment_plan_extension`

`cl_concurrent_reviews`

`cl_consent_requests`

`cl_consents`

`cl_controlled_substance_inventory`

`cl_cron_run_log`

`cl_data_exchange_log`

`cl_ddi_alert_suppressions`

`cl_ddi_overrides`

`cl_designated_contacts`

`cl_diagnoses`

`cl_discharge_checklists`

`cl_disclosure_log`

`cl_dsi_disclosures`

`cl_electronic_consents`

`cl_environmental_assessments`

`cl_family_notification_log`

`cl_fhir_resources`

`cl_follow_up_contacts`

`cl_group_attendance`

`cl_group_curricula`

`cl_group_encounter_generations`

`cl_group_session_telehealth_metadata`

`cl_group_sessions`

`cl_inbasket_item_activity`

`cl_inbasket_items`

`cl_incidents`

`cl_intake_assessments`

`cl_lethal_means_assessments`

`cl_loc_assessments`

`cl_loinc_codes`

`cl_marketplace_bundle_items`

`cl_marketplace_bundles`

`cl_marketplace_imports`

`cl_medication_adherence`

`cl_medication_education`

`cl_medication_reconciliations`

`cl_medications`

`cl_metabolic_monitoring_events`

`cl_module_settings`

`cl_moud_enrollments`

`cl_moud_medication_events`

`cl_moud_monitoring_events`

`cl_multi_party_encounters`

`cl_multi_party_participants`

`cl_note_940_override_audit`

`cl_note_templates`

`cl_note_treatment_goals`

`cl_notification_delivery_attempts`

`cl_notification_escalation_chains`

`cl_notification_events`

`cl_notification_policies`

`cl_order_results`