Encore OS — Documentation

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

Core: rh_ · Tables: 53 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`rh_alumni`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`resident_profile_id`	uuid	no
`episode_id`	uuid	no
`program_completion_date`	date	no
`program_completion_type`	text	no	’normal’::text
`contact_consent`	boolean	no	false
`contact_phone`	text	yes
`contact_email`	text	yes
`contact_address`	text	yes
`success_story`	text	yes
`success_story_approved`	boolean	yes	false
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“preferred_contact_time”: “evenings”, “employer_name”: “ABC Company”, “housing_type”: “independent_lease”, “emergency_contact”: “Jane Doe”, “volunteer_interests”: [“mentoring”, “events”]}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
resident_profile_id → rh_resident_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_alumni_delete (DELETE)
rh_alumni_insert (INSERT)
rh_alumni_select (SELECT)
rh_alumni_update (UPDATE)

`rh_alumni_engagement`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`alumni_id`	uuid	no
`engagement_type`	text	no
`engagement_date`	date	no
`engagement_description`	text	yes
`mentee_episode_id`	uuid	yes
`event_name`	text	yes
`event_date`	date	yes
`status`	text	no	’scheduled’::text
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“hours_contributed”: 4.5, “event_location”: “Main Campus”, “mentee_outcome”: “positive”, “follow_up_required”: false, “recognition_given”: “certificate”}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

alumni_id → rh_alumni(id)
created_by → pf_profiles(id)
mentee_episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_alumni_engagement_delete (DELETE)
rh_alumni_engagement_insert (INSERT)
rh_alumni_engagement_select (SELECT)
rh_alumni_engagement_update (UPDATE)

`rh_attendance_records`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`schedule_instance_id`	uuid	no
`site_id`	uuid	yes
`attendance_status`	text	no
`arrival_time`	timestamp with time zone	yes
`departure_time`	timestamp with time zone	yes
`is_excused`	boolean	no	false
`excused_reason`	text	yes
`excused_approved_by`	uuid	yes
`excused_approved_at`	timestamp with time zone	yes
`make_up_session_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“external_tracking_id”: “ATT-2025-001”, “requires_ada_accommodation”: true, “participation_quality_score”: 8.5, “therapeutic_alliance_rating”: “good”, “behavioral_observations”: “engaged and cooperative”}
`created_at`	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)
episode_id → rh_episodes(id)
excused_approved_by → pf_profiles(id)
make_up_session_id → rh_schedule_instances(id)
organization_id → pf_organizations(id)
schedule_instance_id → rh_schedule_instances(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

attendance_records_delete (DELETE)
attendance_records_insert (INSERT)
attendance_records_select (SELECT)
attendance_records_update (UPDATE)

`rh_audit_findings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`audit_schedule_id`	uuid	no
`finding_description`	text	no
`finding_category`	text	yes
`severity`	text	no
`regulatory_reference`	text	yes
`corrective_action_plan`	text	yes
`corrective_action_deadline`	date	yes
`corrective_action_completed_date`	date	yes
`corrective_action_verified_by`	uuid	yes
`status`	text	no	’open’::text
`evidence_document_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific finding metadata. Examples: {“root_cause”: “training gap”, “responsible_party”: “uuid”, “escalated”: false, “recurrence_count”: 0, “cost_to_remediate”: 1200.00}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

audit_schedule_id → rh_audit_schedules(id)
corrective_action_verified_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_audit_findings_delete (DELETE)
rh_audit_findings_insert (INSERT)
rh_audit_findings_select (SELECT)
rh_audit_findings_update (UPDATE)

`rh_audit_schedules`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`residence_id`	uuid	yes
`audit_type`	text	no
`audit_name`	text	no
`audit_date`	date	no
`auditor_name`	text	yes
`auditor_organization`	text	yes
`status`	text	no	’scheduled’::text
`findings_count`	integer	yes	0
`critical_findings_count`	integer	yes	0
`overall_rating`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific audit metadata. Examples: {“audit_scope”: [“documentation”, “safety”], “preparation_checklist_id”: “uuid”, “follow_up_date”: “2025-02-15”, “report_url”: “https://…”, “audit_fee”: 500.00}
`created_at`	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)
residence_id → rh_residences(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_audit_schedules_delete (DELETE)
rh_audit_schedules_insert (INSERT)
rh_audit_schedules_select (SELECT)
rh_audit_schedules_update (UPDATE)

`rh_beds`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`residence_id`	uuid	no
`room_number`	text	no
`bed_label`	text	no
`gender_restriction`	text	yes
`is_ada_accessible`	boolean	yes	false
`status`	text	yes	’available’::text
`notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific bed metadata. Examples: {“mattress_type”: “twin_xl”, “last_deep_clean_date”: “2024-11-01”, “amenities”: [“lamp”, “nightstand”], “accessibility_features”: [“grab_bars”], “window_view”: “courtyard”}
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes
`site_id`	uuid	yes		Physical site where bed is located. Denormalized from residence for query performance.
`unit_label`	text	yes		Optional grouping label for inpatient_unit facilities (e.g. “East Wing”, “ICU Unit 2”). NULL for recovery_housing and psychiatric_residential.

Foreign keys:

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

Policies:

rh_beds_delete (DELETE)
rh_beds_insert (INSERT)
rh_beds_select (SELECT)
rh_beds_update (UPDATE)

`rh_census_snapshots`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`residence_id`	uuid	yes
`snapshot_date`	date	no
`snapshot_time`	time without time zone	yes	’23:59:59’::time without time zone
`total_capacity`	integer	no
`occupied_count`	integer	no
`available_count`	integer	no
`maintenance_count`	integer	yes	0
`reserved_count`	integer	yes	0
`waitlist_count`	integer	yes	0
`male_occupied`	integer	yes	0
`female_occupied`	integer	yes	0
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific census metadata. Examples: {“snapshot_trigger”: “manual”, “census_taken_by”: “staff_user_id”, “weather_conditions”: “normal”, “special_event”: “holiday_weekend”, “notes”: “Two residents on overnight pass”}
`created_at`	timestamp with time zone	yes	now()
`site_id`	uuid	yes		Site for which census was calculated. Enables site-level reporting.
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

organization_id → pf_organizations(id)
residence_id → rh_residences(id)
site_id → pf_sites(id)

Policies:

rh_census_snapshots_delete (DELETE)
rh_census_snapshots_insert (INSERT)
rh_census_snapshots_select (SELECT)
rh_census_snapshots_update (UPDATE)

`rh_chore_assignments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`residence_id`	uuid	no
`site_id`	uuid	yes
`chore_name`	text	no
`chore_description`	text	yes
`chore_schedule`	text	no
`assigned_date`	date	no
`is_completed`	boolean	no	false
`completed_date`	date	yes
`completed_by`	uuid	yes
`status`	text	no	’assigned’::text
`deleted_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“equipment_needed”: [“vacuum”, “cleaning_supplies”], “estimated_duration_minutes”: 30, “requires_supervision”: false, “photo_evidence_required”: true, “rotation_group”: “A”}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

completed_by → pf_profiles(id)
created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
residence_id → rh_residences(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

chore_assignments_delete (DELETE)
chore_assignments_insert (INSERT)
chore_assignments_select (SELECT)
chore_assignments_update (UPDATE)

`rh_compliance_checklists`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`compliance_requirement_id`	uuid	no
`residence_id`	uuid	yes
`checklist_item`	text	no
`is_completed`	boolean	no	false
`completed_date`	date	yes
`completed_by`	uuid	yes
`evidence_document_id`	uuid	yes
`verified_by`	uuid	yes
`verified_date`	date	yes
`verification_notes`	text	yes
`status`	text	no	’pending’::text
`due_date`	date	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific checklist metadata. Examples: {“evidence_type”: “photo”, “inspector_signature”: true, “review_cycle”: “quarterly”, “risk_score”: 3, “auto_remind”: 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

Foreign keys:

completed_by → pf_profiles(id)
compliance_requirement_id → rh_compliance_requirements(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
residence_id → rh_residences(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

rh_compliance_checklists_delete (DELETE)
rh_compliance_checklists_insert (INSERT)
rh_compliance_checklists_select (SELECT)
rh_compliance_checklists_update (UPDATE)

`rh_compliance_requirements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`requirement_name`	text	no
`requirement_category`	text	no
`regulatory_source`	text	no
`description`	text	yes
`frequency`	text	no
`deadline_calculation`	text	yes
`evidence_required`	boolean	no	true
`verification_required`	boolean	no	true
`applies_to`	text	no	’organization’::text
`residence_id`	uuid	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific requirement metadata. Examples: {“state_regulation_code”: “R9-12-301”, “accreditation_standard”: “NARR 2.1”, “penalty_amount”: 5000, “last_citation_date”: “2024-06-15”, “inspector_notes”: “Focus area”}
`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)
residence_id → rh_residences(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_compliance_requirements_delete (DELETE)
rh_compliance_requirements_insert (INSERT)
rh_compliance_requirements_select (SELECT)
rh_compliance_requirements_update (UPDATE)

`rh_curfew_checks`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`site_id`	uuid	yes
`check_date`	date	no
`check_time`	timestamp with time zone	no
`curfew_time`	time without time zone	no
`actual_return_time`	timestamp with time zone	yes
`compliance_status`	text	no
`minutes_late`	integer	yes
`verified_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“verification_method”: “in_person”, “weather_conditions”: “heavy rain”, “pre_approved_late_arrival”: false, “emergency_contact_notified”: true, “documentation_photo_url”: “https://…”}
`created_at`	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)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

curfew_checks_delete (DELETE)
curfew_checks_insert (INSERT)
curfew_checks_select (SELECT)
curfew_checks_update (UPDATE)

`rh_discharge_checklists`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes		Site context for checklist item. Inherited from discharge plan.
`discharge_plan_id`	uuid	no
`checklist_item`	text	no
`description`	text	yes
`is_required`	boolean	no	true
`is_completed`	boolean	no	false
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`due_date`	date	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific checklist metadata. Examples: {“form_version”: “v2.1”, “evidence_document_id”: “doc_123”, “supervisor_review_required”: true, “special_instructions”: “Coordinate with VA”, “regulatory_requirement”: “NARR 3.2”}
`created_at`	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)
discharge_plan_id → rh_discharge_plans(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_discharge_checklists_delete (DELETE)
rh_discharge_checklists_insert (INSERT)
rh_discharge_checklists_select (SELECT)
rh_discharge_checklists_update (UPDATE)

`rh_discharge_documents`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes		Site context for document. Used for access control and reporting.
`episode_id`	uuid	no
`discharge_plan_id`	uuid	yes
`document_type`	text	no
`document_name`	text	no
`document_id`	uuid	yes
`storage_path`	text	yes
`is_required`	boolean	no	false
`is_completed`	boolean	no	false
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific document metadata. Examples: {“template_id”: “tpl_discharge_summary”, “version”: “1.2”, “signature_required”: true, “notarization_required”: false, “copy_to_resident”: 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

Foreign keys:

completed_by → pf_profiles(id)
created_by → pf_profiles(id)
discharge_plan_id → rh_discharge_plans(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_discharge_documents_delete (DELETE)
rh_discharge_documents_insert (INSERT)
rh_discharge_documents_select (SELECT)
rh_discharge_documents_update (UPDATE)

`rh_discharge_plans`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes		Physical site where discharge is being planned from. Denormalized from episode for query performance.
`episode_id`	uuid	no
`planning_initiated_date`	date	no	CURRENT_DATE
`planning_initiated_by`	uuid	yes
`expected_discharge_date`	date	no
`actual_discharge_date`	date	yes
`status`	text	no	’in_progress’::text
`housing_placement_planned`	boolean	no	false
`employment_referral_planned`	boolean	no	false
`healthcare_referral_planned`	boolean	no	false
`medication_management_planned`	boolean	no	false
`transportation_arranged`	boolean	no	false
`continuing_care_planned`	boolean	no	false
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific discharge planning metadata. Examples: {“referral_agency”: “Phoenix Recovery”, “insurance_case_id”: “INS-2024-001”, “court_case_number”: “CR-2024-789”, “funding_source”: “grant”, “case_manager_notes”: “Needs wheelchair-accessible housing”}
`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, NOT NULL = deleted.

Foreign keys:

created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
planning_initiated_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_discharge_plans_delete (DELETE)
rh_discharge_plans_insert (INSERT)
rh_discharge_plans_select (SELECT)
rh_discharge_plans_update (UPDATE)

`rh_eligibility_checklists`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`willing_to_follow_rules_status`	text	yes
`willing_to_follow_rules_notes`	text	yes
`willing_to_follow_rules_verified_by`	uuid	yes
`willing_to_follow_rules_verified_date`	date	yes
`commit_to_recovery_status`	text	yes
`commit_to_recovery_notes`	text	yes
`commit_to_recovery_verified_by`	uuid	yes
`commit_to_recovery_verified_date`	date	yes
`no_violence_history_status`	text	yes
`no_violence_history_notes`	text	yes
`no_violence_history_verified_by`	uuid	yes
`no_violence_history_verified_date`	date	yes
`no_arson_history_status`	text	yes
`no_arson_history_notes`	text	yes
`no_arson_history_verified_by`	uuid	yes
`no_arson_history_verified_date`	date	yes
`no_sex_offense_status`	text	yes
`no_sex_offense_notes`	text	yes
`no_sex_offense_verified_by`	uuid	yes
`no_sex_offense_verified_date`	date	yes
`background_check_status`	text	yes
`background_check_notes`	text	yes
`background_check_verified_by`	uuid	yes
`background_check_verified_date`	date	yes
`medication_compliance_status`	text	yes
`medication_compliance_notes`	text	yes
`medication_compliance_verified_by`	uuid	yes
`medication_compliance_verified_date`	date	yes
`is_priority_population`	boolean	yes	false
`priority_reason`	text	yes
`decision_type`	text	yes
`decision_date`	date	yes
`decision_by`	uuid	yes
`decision_notes`	text	yes
`redirect_resource_name`	text	yes
`redirect_contact_person`	text	yes
`redirect_phone`	text	yes
`warm_handoff_completed`	boolean	yes	false
`warm_handoff_date`	date	yes
`warm_handoff_notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific eligibility metadata. Examples: {“screening_tool_version”: “v2.1”, “external_assessment_id”: “ASM-001”, “interpreter_needed”: true, “language_preference”: “Spanish”, “accessibility_needs”: [“wheelchair_ramp”]}
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes

Foreign keys:

background_check_verified_by → pf_profiles(id)
commit_to_recovery_verified_by → pf_profiles(id)
created_by → pf_profiles(id)
decision_by → pf_profiles(id)
episode_id → rh_episodes(id)
medication_compliance_verified_by → pf_profiles(id)
no_arson_history_verified_by → pf_profiles(id)
no_sex_offense_verified_by → pf_profiles(id)
no_violence_history_verified_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
willing_to_follow_rules_verified_by → pf_profiles(id)

Policies:

rh_eligibility_checklists_delete (DELETE)
rh_eligibility_checklists_insert (INSERT)
rh_eligibility_checklists_select (SELECT)
rh_eligibility_checklists_update (UPDATE)

`rh_episode_payment_status`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`current_balance`	numeric(10,2)	yes	0
`last_payment_date`	date	yes
`last_payment_amount`	numeric(10,2)	yes
`payment_status`	text	yes
`days_past_due`	integer	yes	0
`last_updated_at`	timestamp with time zone	yes	now()
`fa_api_response_cached`	jsonb	yes
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

episode_id → rh_episodes(id)
organization_id → pf_organizations(id)

Policies:

rh_episode_payment_status_delete (DELETE)
rh_episode_payment_status_insert (INSERT)
rh_episode_payment_status_select (SELECT)
rh_episode_payment_status_update (UPDATE)

`rh_episode_phases`

RLS: ✅ enabled (7 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`program_phase_id`	uuid	no
`phase_name`	text	no
`phase_start_date`	date	no
`phase_end_date`	date	yes
`expected_end_date`	date	yes
`progression_type`	text	no	’automatic’::text
`progression_reason`	text	yes
`is_current_phase`	boolean	no	false
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific phase history metadata. Examples: {“override_reason”: “medical_emergency”, “approved_by_external”: “judge_smith”, “documentation_links”: [“url1”, “url2”], “appeal_submitted”: false}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`site_id`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
program_phase_id → rh_program_phases(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_episode_phases_delete (DELETE)
rh_episode_phases_insert (INSERT)
rh_episode_phases_org_delete (DELETE)
rh_episode_phases_org_insert (INSERT)
rh_episode_phases_org_update (UPDATE)
rh_episode_phases_select (SELECT)
rh_episode_phases_update (UPDATE)

`rh_episode_programs`

RLS: ✅ enabled (7 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`program_id`	uuid	no
`enrollment_date`	date	no	CURRENT_DATE
`completion_date`	date	yes
`termination_date`	date	yes
`termination_reason`	text	yes
`status`	text	no	’active’::text
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific enrollment metadata. Examples: {“referral_source”: “court_mandated”, “grant_funded”: true, “special_accommodations”: [“wheelchair_accessible”], “case_manager_id”: “CM-456”}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`site_id`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
program_id → rh_programs(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_episode_programs_delete (DELETE)
rh_episode_programs_insert (INSERT)
rh_episode_programs_org_delete (DELETE)
rh_episode_programs_org_insert (INSERT)
rh_episode_programs_org_update (UPDATE)
rh_episode_programs_select (SELECT)
rh_episode_programs_update (UPDATE)

`rh_episodes`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`resident_profile_id`	uuid	no
`residence_id`	uuid	no
`bed_id`	uuid	yes
`episode_number`	text	no		Human-readable episode identifier (e.g., EP-000001). Auto-generated on INSERT via trigger.
`status`	text	yes	’referred’::text
`referral_date`	date	no
`referral_source`	text	yes
`referral_notes`	text	yes
`admission_date`	date	yes
`expected_discharge_date`	date	yes
`actual_discharge_date`	date	yes
`discharge_type`	text	yes
`discharge_reason`	text	yes
`discharge_notes`	text	yes
`current_phase`	integer	yes
`phase_start_date`	date	yes
`total_blackout_days`	integer	yes	0
`notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific episode metadata. Examples: {“referral_agency”: “Phoenix Recovery”, “insurance_case_id”: “INS-2024-001”, “court_case_number”: “CR-2024-789”, “funding_source”: “grant”, “discharge_placement_type”: “sober_living”}
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes
`site_id`	uuid	yes		Optional site reference for multi-site organizations. Allows filtering episodes by physical location where resident is housed.
`episode_type`	text	no	’residential_admission’::text	RH-01 episode classification: residential_admission (new intake), readmission, referral, transfer, extension.

Foreign keys:

bed_id → rh_beds(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
residence_id → rh_residences(id)
resident_profile_id → rh_resident_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_episodes_delete (DELETE)
rh_episodes_insert (INSERT)
rh_episodes_select (SELECT)
rh_episodes_update (UPDATE)

`rh_follow_up_contacts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes		Site that made the contact attempt.
`follow_up_schedule_id`	uuid	no
`contact_date`	timestamp with time zone	no	now()
`contact_method`	text	no
`contacted_by`	uuid	yes
`contact_result`	text	no
`outcome`	text	yes
`housing_status`	text	yes
`employment_status`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific contact metadata. Examples: {“call_duration_minutes”: 15, “interpreter_used”: false, “consent_renewed”: true, “referred_to_services”: [“housing”, “employment”], “risk_flags”: [“relapse_warning”]}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

contacted_by → pf_profiles(id)
created_by → pf_profiles(id)
follow_up_schedule_id → rh_follow_up_schedules(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_follow_up_contacts_delete (DELETE)
rh_follow_up_contacts_insert (INSERT)
rh_follow_up_contacts_select (SELECT)
rh_follow_up_contacts_update (UPDATE)

`rh_follow_up_schedules`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes		Site responsible for follow-up. May differ from discharge site.
`episode_id`	uuid	no
`timepoint`	text	no
`scheduled_date`	date	no
`status`	text	no	’scheduled’::text
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific follow-up metadata. Examples: {“preferred_contact_time”: “morning”, “alternate_contact”: “555-1234”, “interpreter_needed”: true, “grant_reporting_required”: true, “survey_form_id”: “form_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:

completed_by → pf_profiles(id)
created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_follow_up_schedules_delete (DELETE)
rh_follow_up_schedules_insert (INSERT)
rh_follow_up_schedules_select (SELECT)
rh_follow_up_schedules_update (UPDATE)

`rh_grievance_attachments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`grievance_id`	uuid	no
`file_name`	text	no
`file_path`	text	no
`file_size`	bigint	no
`mime_type`	text	no
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

rh_grievance_attachments_row_delete (DELETE)
rh_grievance_attachments_row_insert (INSERT)
rh_grievance_attachments_row_select (SELECT)
rh_grievance_attachments_row_update (UPDATE)

`rh_grievances`

RH-09: Resident grievance records with state machine (received->under_review->resolved|escalated->closed). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`residence_id`	uuid	yes
`episode_id`	uuid	yes
`resident_profile_id`	uuid	yes
`status`	text	no	’received’::text
`category`	text	no	’other’::text
`subject`	text	no
`narrative`	text	yes
`submitted_at`	timestamp with time zone	no	now()
`closed_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
`assigned_to`	uuid	yes		RH-09: staff member the grievance is assigned to for resolution.
`resolution_notes`	text	yes		RH-09: notes recorded when the grievance is resolved or closed.

Foreign keys:

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

Policies:

rh_grievances_delete (DELETE)
rh_grievances_insert (INSERT)
rh_grievances_select (SELECT)
rh_grievances_update (UPDATE)

`rh_med_storage_audits`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`residence_id`	uuid	no
`audit_date`	date	no
`audit_type`	text	no
`auditor_id`	uuid	no
`storage_secure`	boolean	yes
`temperature_controlled`	boolean	yes
`temperature_log_current`	boolean	yes
`medications_labeled`	boolean	yes
`expired_meds_removed`	boolean	yes
`inventory_accurate`	boolean	yes
`narcotics_double_locked`	boolean	yes
`narcotics_log_current`	boolean	yes
`compliance_status`	text	no
`findings`	text	yes
`deficiencies_identified`	jsonb	no	’[]‘::jsonb
`corrective_actions_required`	boolean	no	false
`corrective_actions`	jsonb	no	’[]‘::jsonb
`follow_up_required`	boolean	no	false
`follow_up_date`	date	yes
`follow_up_audit_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“regulatory_inspector”: “State Board Inspector”, “photos_taken”: 5, “witness_staff”: [“staff_user_id_1”], “next_routine_audit”: “2026-01-15”}
`site_id`	uuid	yes		Site where medication storage was audited. Required for regulatory compliance.

Foreign keys:

auditor_id → pf_profiles(id)
created_by → pf_profiles(id)
follow_up_audit_id → rh_med_storage_audits(id)
organization_id → pf_organizations(id)
residence_id → rh_residences(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

med_audits_delete (DELETE)
med_audits_insert (INSERT)
med_audits_select (SELECT)
med_audits_update (UPDATE)

`rh_milestone_completions`

RLS: ✅ enabled (6 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`milestone_id`	uuid	no
`status`	text	no	’pending’::text
`completed_date`	date	yes
`verified_by`	uuid	yes
`completion_notes`	text	yes
`completion_evidence_document_id`	uuid	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific completion metadata. Examples: {“external_verification_id”: “VER-789”, “completion_location”: “onsite”, “witness_name”: “Staff Member”, “photo_evidence”: “url”, “retroactive_approval”: false}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`site_id`	uuid	yes

Foreign keys:

completion_evidence_document_id → pf_documents(id)
created_by → pf_profiles(id)
episode_id → rh_episodes(id)
milestone_id → rh_phase_milestones(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

rh_milestone_completions_delete (DELETE)
rh_milestone_completions_insert (INSERT)
rh_milestone_completions_org_delete (DELETE)
rh_milestone_completions_org_update (UPDATE)
rh_milestone_completions_select (SELECT)
rh_milestone_completions_update (UPDATE)

`rh_module_settings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`default_stay_duration_days`	integer	yes	120
`phase_1_duration_days`	integer	yes	30
`phase_2_duration_days`	integer	yes	30
`phase_3_duration_days`	integer	yes	30
`phase_4_duration_days`	integer	yes	30
`license_expiry_warning_days`	integer	yes	60
`minimum_attendance_percentage`	numeric(5,2)	yes	85.00
`curfew_grace_period_minutes`	integer	yes	15
`auto_assign_beds_from_waitlist`	boolean	yes	false
`waitlist_priority_by_referral_date`	boolean	yes	true
`integrate_with_fa`	boolean	yes	true
`integrate_with_hr`	boolean	yes	true
`auto_create_invoices`	boolean	yes	false
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific module configuration. Examples: {“default_payment_method”: “check”, “require_sponsor_approval”: true, “grace_period_override_days”: 3, “custom_phase_names”: {“1”: “Orientation”, “2”: “Foundation”}, “intake_form_id”: “frm_123”}
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes
`ai_enabled`	boolean	yes	true	Enable AI features for Recovery Housing module
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates instead of hardcoded wizards
`auto_save_interval_seconds`	integer	yes	30	Interval in seconds for auto-saving wizard and form drafts. Range: 10-300 seconds.
`draft_expiration_days`	integer	yes	7	Number of days before unsaved wizard drafts expire and are deleted. Range: 1-90 days.
`discharge_planning_initiation_days`	integer	yes	30	Days before planned discharge date to begin discharge planning process. Range: 7-90 days.
`outcome_follow_up_days`	integer[]	yes	ARRAY[7, 30, 90, 180, 365]	Array of days post-discharge to schedule outcome follow-up assessments (e.g., 7, 30, 90, 180, 365).
`audit_critical_response_hours`	integer	yes	48	Required response time for critical audit findings in hours
`audit_major_response_days`	integer	yes	30	Required response time for major audit findings in days
`audit_minor_response_days`	integer	yes	60	Required response time for minor audit findings in days
`audit_observation_response_days`	integer	yes	90	Required response time for audit observations in days
`resident_number_prefix`	text	yes	’R’::text
`resident_number_sequence`	integer	yes	1
`email_sender_address`	text	yes		Optional per-module email sender address (e.g. admissions@domain.com). Falls back to org default if null.

Foreign keys:

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

Policies:

rh_module_settings_delete (DELETE)
rh_module_settings_insert (INSERT)
rh_module_settings_select (SELECT)
rh_module_settings_update (UPDATE)

`rh_outcome_assessments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`episode_id`	uuid	no
`outcome_checkpoint_id`	uuid	yes
`assessment_type`	text	no	’baseline’::text
`assessment_date`	date	no
`assessment_form_id`	uuid	yes
`assessment_form_response_id`	uuid	yes
`status`	text	no	’pending’::text
`completed_date`	date	yes
`completed_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“assessor_notes”: “Completed via phone”, “location”: “off_site”, “interpreter_used”: false, “time_to_complete_minutes”: 25}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

assessment_form_id → fw_forms(id)
completed_by → pf_profiles(id)
created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
outcome_checkpoint_id → rh_outcome_checkpoints(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_outcome_assessments_delete (DELETE)
rh_outcome_assessments_insert (INSERT)
rh_outcome_assessments_select (SELECT)
rh_outcome_assessments_update (UPDATE)

`rh_outcome_checkpoints`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`checkpoint_name`	text	no
`days_post_discharge`	integer	no
`description`	text	yes
`assessment_form_id`	uuid	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“contact_method”: “phone”, “max_attempts”: 3, “required_for_grant”: true, “data_collection_form”: “follow_up_survey_v2”}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

rh_outcome_checkpoints_delete (DELETE)
rh_outcome_checkpoints_insert (INSERT)
rh_outcome_checkpoints_select (SELECT)
rh_outcome_checkpoints_update (UPDATE)

`rh_outcome_indicators`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`indicator_name`	text	no
`description`	text	yes
`measurement_type`	text	no	’binary’::text
`assessment_form_id`	uuid	yes
`assessment_question_ids`	uuid[]	yes	’{}‘::uuid[]
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“target_population”: “MAT clients”, “data_source”: “self_report”, “narr_standard”: “2.1”, “grant_requirement”: “SAMHSA”, “weight_factor”: 1.5}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

rh_outcome_indicators_delete (DELETE)
rh_outcome_indicators_insert (INSERT)
rh_outcome_indicators_select (SELECT)
rh_outcome_indicators_update (UPDATE)

`rh_outcome_measurements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`episode_id`	uuid	no
`outcome_indicator_id`	uuid	no
`outcome_checkpoint_id`	uuid	yes
`measurement_type`	text	no	’baseline’::text
`measurement_value`	numeric	yes
`measurement_date`	date	no
`is_success`	boolean	yes
`success_score`	numeric(5,2)	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“data_quality_flag”: “verified”, “measurement_context”: “stable_housing”, “comparison_to_baseline”: “improved”, “confidence_level”: “high”}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
outcome_checkpoint_id → rh_outcome_checkpoints(id)
outcome_indicator_id → rh_outcome_indicators(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_outcome_measurements_delete (DELETE)
rh_outcome_measurements_insert (INSERT)
rh_outcome_measurements_select (SELECT)
rh_outcome_measurements_update (UPDATE)

`rh_participation_metrics`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`site_id`	uuid	yes
`total_activities_scheduled`	integer	no	0
`total_activities_attended`	integer	no	0
`total_activities_excused`	integer	no	0
`total_activities_absent`	integer	no	0
`attendance_percentage`	numeric(5,2)	yes	CASE WHEN (total_activities_scheduled > 0) THEN ((((total_activities_attended + total_activities_excused))::numeric / (total_activities_scheduled)::numeric) * (100)::numeric) ELSE (0)::numeric END
`engagement_score`	numeric(5,2)	yes
`engagement_trend`	text	yes
`chores_assigned`	integer	no	0
`chores_completed`	integer	no	0
`chore_completion_percentage`	numeric(5,2)	yes	CASE WHEN (chores_assigned > 0) THEN (((chores_completed)::numeric / (chores_assigned)::numeric) * (100)::numeric) ELSE (0)::numeric END
`curfew_checks_total`	integer	no	0
`curfew_checks_compliant`	integer	no	0
`curfew_compliance_percentage`	numeric(5,2)	yes	CASE WHEN (curfew_checks_total > 0) THEN (((curfew_checks_compliant)::numeric / (curfew_checks_total)::numeric) * (100)::numeric) ELSE (0)::numeric END
`last_calculated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“custom_scoring_factors”: {“peer_feedback”: 0.2, “staff_observations”: 0.3}, “risk_level”: “low”, “motivational_stage”: “action”, “barriers_to_participation”: [“transportation”, “childcare”]}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)

Policies:

participation_metrics_delete (DELETE)
participation_metrics_insert (INSERT)
participation_metrics_select (SELECT)
participation_metrics_update (UPDATE)

`rh_passes`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`program_id`	uuid	no
`pass_type`	text	no
`start_datetime`	timestamp with time zone	no
`end_datetime`	timestamp with time zone	no
`destination`	text	no
`purpose`	text	no
`contact_person`	text	yes
`contact_phone`	text	yes
`phase_name`	text	no
`meets_phase_requirements`	boolean	no	false
`phase_override`	boolean	no	false
`phase_override_reason`	text	yes
`status`	text	no	’requested’::text
`requested_at`	timestamp with time zone	no	now()
`requested_by`	uuid	no
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`approval_notes`	text	yes
`denied_at`	timestamp with time zone	yes
`denied_by`	uuid	yes
`denial_reason`	text	yes
`expected_return_datetime`	timestamp with time zone	yes
`actual_return_datetime`	timestamp with time zone	yes
`returned_on_time`	boolean	yes
`return_verified_by`	uuid	yes
`return_notes`	text	yes
`curfew_violation`	boolean	no	false
`curfew_violation_notes`	text	yes
`cancelled_at`	timestamp with time zone	yes
`cancelled_by`	uuid	yes
`cancellation_reason`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“transportation_method”: “public_transit”, “emergency_contact”: “+1-555-0199”, “special_instructions”: “Must check in every 4 hours”, “ua_test_on_return”: true}
`site_id`	uuid	yes		Site from which pass was issued/approved.
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp. NULL = active, NOT NULL = deleted.

Foreign keys:

approved_by → pf_profiles(id)
cancelled_by → pf_profiles(id)
created_by → pf_profiles(id)
denied_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
program_id → rh_programs(id)
requested_by → pf_profiles(id)
return_verified_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

passes_delete (DELETE)
passes_insert (INSERT)
passes_select (SELECT)
passes_update (UPDATE)

`rh_phase_milestones`

RLS: ✅ enabled (7 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_phase_id`	uuid	no
`milestone_name`	text	no
`milestone_order`	integer	no
`description`	text	yes
`completion_criteria`	text	yes
`is_required`	boolean	no	true
`dependency_milestone_id`	uuid	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific milestone metadata. Examples: {“external_tracking_id”: “MS-001”, “celebration_type”: “certificate”, “notification_template_id”: “tmpl_123”, “points_awarded”: 50, “badge_icon”: “trophy”}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`site_id`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
dependency_milestone_id → rh_phase_milestones(id)
organization_id → pf_organizations(id)
program_phase_id → rh_program_phases(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_phase_milestones_delete (DELETE)
rh_phase_milestones_insert (INSERT)
rh_phase_milestones_org_delete (DELETE)
rh_phase_milestones_org_insert (INSERT)
rh_phase_milestones_org_update (UPDATE)
rh_phase_milestones_select (SELECT)
rh_phase_milestones_update (UPDATE)

`rh_phase_privileges`

RLS: ✅ enabled (7 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_phase_id`	uuid	no
`curfew_time`	time without time zone	yes
`visitor_policy`	text	no	’none’::text
`pass_eligibility`	text	no	’none’::text
`job_search_allowed`	boolean	no	false
`housing_search_allowed`	boolean	no	false
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific privilege metadata. Examples: {“external_system_id”: “PRIV-001”, “override_rules”: [“manager_approval”], “location_restrictions”: [“within_5_miles”], “time_restrictions”: {“weekday_only”: true}}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`site_id`	uuid	yes

Foreign keys:

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

Policies:

rh_phase_privileges_delete (DELETE)
rh_phase_privileges_insert (INSERT)
rh_phase_privileges_org_delete (DELETE)
rh_phase_privileges_org_insert (INSERT)
rh_phase_privileges_org_update (UPDATE)
rh_phase_privileges_select (SELECT)
rh_phase_privileges_update (UPDATE)

`rh_program_phases`

RLS: ✅ enabled (7 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_id`	uuid	no
`phase_name`	text	no
`phase_order`	integer	no
`duration_baseline_days`	integer	no	30
`description`	text	yes
`requirements`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific phase metadata. Examples: {“phase_color”: “#4CAF50”, “extended_requirements”: [“sponsor_meeting”, “employment_proof”], “custom_duration_rules”: {“employment”: 60}, “graduation_ceremony”: true}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`site_id`	uuid	yes

Foreign keys:

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

Policies:

rh_program_phases_delete (DELETE)
rh_program_phases_insert (INSERT)
rh_program_phases_org_delete (DELETE)
rh_program_phases_org_insert (INSERT)
rh_program_phases_org_update (UPDATE)
rh_program_phases_select (SELECT)
rh_program_phases_update (UPDATE)

`rh_programs`

RLS: ✅ enabled (7 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`program_type`	text	no
`duration_baseline_days`	integer	no	120
`capacity_limit`	integer	yes
`eligibility_criteria`	jsonb	yes	’{}‘::jsonb
`program_rules`	text	yes
`intake_form_id`	uuid	yes
`assessment_form_id`	uuid	yes
`exit_form_id`	uuid	yes
`status`	text	no	’active’::text
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific program metadata. Examples: {“external_program_id”: “PROG-123”, “funding_source”: “grant”, “accreditation_code”: “NARR-4”, “reporting_category”: “intensive”, “custom_capacity_rules”: {“male”: 10, “female”: 8}}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`site_id`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assessment_form_id → fw_forms(id)
created_by → pf_profiles(id)
exit_form_id → fw_forms(id)
intake_form_id → fw_forms(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_programs_delete (DELETE)
rh_programs_insert (INSERT)
rh_programs_org_delete (DELETE)
rh_programs_org_insert (INSERT)
rh_programs_org_update (UPDATE)
rh_programs_select (SELECT)
rh_programs_update (UPDATE)

`rh_referrals`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes		Site from which referral was initiated.
`episode_id`	uuid	no
`discharge_plan_id`	uuid	yes
`referral_type`	text	no
`referral_date`	date	no	CURRENT_DATE
`agency_name`	text	no
`contact_name`	text	yes
`contact_phone`	text	yes
`contact_email`	text	yes
`status`	text	no	’sent’::text
`follow_up_date`	date	yes
`follow_up_completed`	boolean	no	false
`outcome`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific referral metadata. Examples: {“priority”: “high”, “external_referral_id”: “REF-2024-001”, “insurance_accepted”: true, “waitlist_position”: 3, “special_requirements”: [“wheelchair_accessible”, “pet_friendly”]}
`created_at`	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)
discharge_plan_id → rh_discharge_plans(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_referrals_delete (DELETE)
rh_referrals_insert (INSERT)
rh_referrals_select (SELECT)
rh_referrals_update (UPDATE)

`rh_report_definitions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`report_name`	text	no
`report_type`	text	no
`report_config`	jsonb	no	’{}‘::jsonb
`is_scheduled`	boolean	no	false
`schedule_frequency`	text	yes
`schedule_day_of_week`	integer	yes
`schedule_day_of_month`	integer	yes
`recipient_user_ids`	uuid[]	yes	’{}‘::uuid[]
`recipient_emails`	text[]	yes	’{}‘::text[]
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“template_version”: “2.0”, “export_format”: “pdf”, “include_charts”: true, “watermark”: “confidential”, “auto_archive_days”: 90}
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`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:

rh_report_definitions_delete (DELETE)
rh_report_definitions_insert (INSERT)
rh_report_definitions_select (SELECT)
rh_report_definitions_update (UPDATE)

`rh_residences`

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
`narr_level`	text	yes
`address_line1`	text	yes
`address_line2`	text	yes
`city`	text	yes
`state`	text	yes
`postal_code`	text	yes
`license_number`	text	yes
`license_expiry`	date	yes
`total_capacity`	integer	yes	0
`gender_model`	text	yes
`designated_manager_staff_id`	uuid	yes
`status`	text	yes	’active’::text
`notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific residence metadata. Examples: {“fire_inspection_date”: “2024-06-15”, “accreditation_body”: “NARR”, “wifi_password”: “encrypted_value”, “emergency_contact”: “+1-555-0199”, “pet_policy”: “service_animals_only”}
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes
`facility_type`	text	no	’recovery_housing’::text	Type of facility: recovery_housing (default), psychiatric_residential, or inpatient_unit

Foreign keys:

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

Policies:

rh_residences_delete (DELETE)
rh_residences_insert (INSERT)
rh_residences_select (SELECT)
rh_residences_update (UPDATE)

`rh_resident_agreements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`agreement_date`	date	no
`signed_date`	date	yes
`signed_by_resident`	uuid	yes
`agreement_document_id`	uuid	yes
`status`	text	yes	’draft’::text
`payment_amount`	numeric(10,2)	no
`payment_frequency`	text	yes
`payment_day_of_period`	integer	yes
`accepted_payment_methods`	text[]	yes
`late_fee_amount`	numeric(10,2)	yes	0
`grace_period_days`	integer	yes	5
`first_invoice_date`	date	yes
`last_invoice_created_date`	date	yes
`invoice_count`	integer	yes	0
`notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific agreement metadata. Examples: {“payment_plan_approved”: true, “guarantor_name”: “John Smith”, “scholarship_amount”: 200, “sliding_scale_tier”: 2, “e_signature_reference_id”: “esig_abc123”}
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp for audit trail compliance.

Foreign keys:

created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
signed_by_resident → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

rh_resident_agreements_delete (DELETE)
rh_resident_agreements_insert (INSERT)
rh_resident_agreements_select (SELECT)
rh_resident_agreements_update (UPDATE)

`rh_resident_profiles`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`profile_id`	uuid	no
`diagnosis_category`	text	yes
`is_on_mat_moud`	boolean	yes	false
`mat_moud_medication`	text	yes
`legal_supervision_type`	text	yes
`probation_officer_name`	text	yes
`probation_officer_phone`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb	Organization-specific resident metadata. Examples: {“preferred_name”: “Mike”, “dietary_restrictions”: [“vegetarian”, “gluten_free”], “emergency_contact_2”: {“name”: “Jane Doe”, “phone”: “+1-555-0100”}, “insurance_provider”: “Medicaid”, “case_manager_external_id”: “CM-456”}
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes
`resident_number`	text	yes		Human-readable resident identifier (e.g., R-000001). Auto-generated on INSERT via trigger.

Foreign keys:

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

Policies:

rh_resident_profiles_delete (DELETE)
rh_resident_profiles_insert (INSERT)
rh_resident_profiles_select (SELECT)
rh_resident_profiles_update (UPDATE)

`rh_schedule_instances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`schedule_template_id`	uuid	yes
`program_id`	uuid	yes
`site_id`	uuid	yes
`activity_type`	text	no
`activity_date`	date	no
`activity_time`	time without time zone	no
`activity_location`	text	yes
`is_mandatory`	boolean	no	true
`max_participants`	integer	yes
`status`	text	no	’scheduled’::text
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“actual_attendance_count”: 12, “session_recording_url”: “https://…”, “guest_speaker_name”: “Dr. Smith”, “weather_cancelled”: false, “rescheduled_from_date”: “2025-01-15”}
`created_at`	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_id → rh_programs(id)
schedule_template_id → rh_schedule_templates(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

schedule_instances_delete (DELETE)
schedule_instances_insert (INSERT)
schedule_instances_select (SELECT)
schedule_instances_update (UPDATE)

`rh_schedule_templates`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_id`	uuid	yes
`site_id`	uuid	yes
`template_name`	text	no
`description`	text	yes
`day_of_week`	integer	yes
`activity_time`	time without time zone	no
`activity_type`	text	no
`activity_location`	text	yes
`is_mandatory`	boolean	no	true
`max_participants`	integer	yes
`is_active`	boolean	no	true
`deleted_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“external_calendar_id”: “cal_abc123”, “recurring_pattern_override”: “biweekly”, “room_booking_code”: “RM-301”, “facilitator_certification_required”: true, “materials_needed”: [“worksheets”, “projector”]}
`created_at`	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_id → rh_programs(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

schedule_templates_delete (DELETE)
schedule_templates_insert (INSERT)
schedule_templates_select (SELECT)
schedule_templates_update (UPDATE)

`rh_shift_notes`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`residence_id`	uuid	no
`shift_date`	date	no
`shift_time`	time without time zone	yes
`shift_type`	text	yes
`staff_employee_id`	uuid	no
`daily_operations`	text	yes
`significant_events`	text	yes
`resident_status`	text	yes
`notes`	text	yes
`linked_episode_ids`	uuid[]	yes
`linked_significant_event_ids`	uuid[]	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific shift note metadata. Examples: {“census_count”: 8, “visitor_count”: 2, “medication_rounds”: 3, “incident_free”: true, “weather_conditions”: “clear”}
`created_at`	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)
residence_id → rh_residences(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

rh_shift_notes_delete (DELETE)
rh_shift_notes_insert (INSERT)
rh_shift_notes_select (SELECT)
rh_shift_notes_update (UPDATE)

`rh_significant_event_actions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`investigation_id`	uuid	no
`action_description`	text	no
`action_type`	text	no
`assigned_to`	uuid	yes
`assigned_department`	text	yes
`target_completion_date`	date	yes
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`completion_notes`	text	yes
`status`	text	no	’pending’::text
`priority`	text	no	’medium’::text
`verified`	boolean	no	false
`verified_at`	timestamp with time zone	yes
`verified_by`	uuid	yes
`verification_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“budget_impact”: 5000, “vendor_required”: “Security Systems Inc”, “completion_percentage”: 75, “milestones”: [“planning”, “procurement”, “installation”]}

Foreign keys:

assigned_to → pf_profiles(id)
completed_by → pf_profiles(id)
created_by → pf_profiles(id)
investigation_id → rh_significant_event_investigations(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

actions_delete (DELETE)
actions_insert (INSERT)
actions_select (SELECT)
actions_update (UPDATE)

`rh_significant_event_investigations`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`significant_event_id`	uuid	no
`assigned_investigator_id`	uuid	yes
`investigation_team`	jsonb	no	’[]‘::jsonb
`status`	text	no	’open’::text
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`root_cause_analysis`	text	yes
`contributing_factors`	jsonb	no	’[]‘::jsonb
`findings`	text	yes
`investigation_checklist`	jsonb	no	’[]‘::jsonb
`evidence_collected`	jsonb	no	’[]‘::jsonb
`recommendations`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“external_consultant”: “Dr. Smith”, “review_board_date”: “2025-12-20”, “policy_changes_required”: true, “staff_training_needed”: [“de-escalation”, “first_aid”]}

Foreign keys:

assigned_investigator_id → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
significant_event_id → rh_significant_events(id)
updated_by → pf_profiles(id)

Policies:

investigations_delete (DELETE)
investigations_insert (INSERT)
investigations_select (SELECT)
investigations_update (UPDATE)

`rh_significant_event_types`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`code`	text	no
`description`	text	yes
`severity_default`	text	no	’moderate’::text
`requires_investigation`	boolean	no	true
`requires_regulatory_report`	boolean	no	false
`regulatory_deadline_hours`	integer	yes
`is_active`	boolean	no	true
`is_system`	boolean	no	false
`display_order`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“notification_template”: “critical_event_v1”, “escalation_roles”: [“house_manager”, “program_director”], “incident_tags”: [“medical”, “urgent”]}

Foreign keys:

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

Policies:

event_types_delete (DELETE)
event_types_insert (INSERT)
event_types_select (SELECT)
event_types_update (UPDATE)

`rh_significant_events`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`event_type_id`	uuid	no
`episode_id`	uuid	no
`event_date`	timestamp with time zone	no
`event_location`	text	no
`severity`	text	no
`event_summary`	text	no
`event_details`	text	yes
`immediate_action_taken`	text	yes
`involved_parties`	jsonb	no	’[]‘::jsonb
`witness_info`	jsonb	no	’[]‘::jsonb
`status`	text	no	’draft’::text
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	yes
`closed_at`	timestamp with time zone	yes
`closed_by`	uuid	yes
`closure_notes`	text	yes
`regulatory_report_required`	boolean	no	false
`regulatory_report_filed`	boolean	no	false
`regulatory_report_date`	timestamp with time zone	yes
`regulatory_report_number`	text	yes
`deleted_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“police_report_number”: “2025-12345”, “injury_type”: “laceration”, “hospital_name”: “County General”, “followup_appointment”: “2025-12-15T10:00:00Z”, “family_notified”: true}

Foreign keys:

closed_by → pf_profiles(id)
created_by → pf_profiles(id)
episode_id → rh_episodes(id)
event_type_id → rh_significant_event_types(id)
organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)
site_id → pf_sites(id)
submitted_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

events_delete (DELETE)
events_insert (INSERT)
events_select (SELECT)
events_update (UPDATE)

`rh_staff_assignments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`residence_id`	uuid	no
`staff_employee_id`	uuid	no
`staff_role`	text	no
`is_primary_residence`	boolean	no	false
`assignment_start_date`	date	no
`assignment_end_date`	date	yes
`status`	text	no	’active’::text
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific assignment metadata. Examples: {“backup_staff_id”: “uuid”, “shift_preference”: “day”, “max_residents”: 12, “certifications”: [“CPI”, “FA”], “languages”: [“en”, “es”]}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

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

Policies:

rh_staff_assignments_delete (DELETE)
rh_staff_assignments_insert (INSERT)
rh_staff_assignments_select (SELECT)
rh_staff_assignments_update (UPDATE)

`rh_staff_trainings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`staff_employee_id`	uuid	no
`training_type`	text	no
`training_name`	text	no
`training_provider`	text	yes
`completion_date`	date	no
`expiry_date`	date	yes
`certification_number`	text	yes
`certification_document_id`	uuid	yes
`is_current`	boolean	no	true
`is_required`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific training metadata. Examples: {“ce_credits”: 4, “course_code”: “NARR-101”, “renewal_reminder_days”: 60, “cost”: 150.00, “instructor”: “John Smith”}
`created_at`	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:

rh_staff_trainings_delete (DELETE)
rh_staff_trainings_insert (INSERT)
rh_staff_trainings_select (SELECT)
rh_staff_trainings_update (UPDATE)

`rh_transport_requests`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`site_id`	uuid	yes
`transport_type`	text	no
`destination`	text	yes
`request_date`	date	no
`request_time`	time without time zone	no
`return_date`	date	yes
`return_time`	time without time zone	yes
`status`	text	no	’requested’::text
`scheduled_driver`	uuid	yes
`scheduled_vehicle`	text	yes
`deleted_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Examples: {“wheelchair_accessible_required”: true, “insurance_info_needed”: false, “mileage_reimbursement_amount”: 25.50, “companion_allowed”: true, “special_instructions”: “use rear entrance”}
`created_at`	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)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

transport_requests_delete (DELETE)
transport_requests_insert (INSERT)
transport_requests_select (SELECT)
transport_requests_update (UPDATE)

`rh_uds_random_schedule`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`program_id`	uuid	no
`schedule_name`	text	no
`selection_percentage`	numeric(5,2)	no	10.00
`selection_frequency`	text	no
`selection_algorithm`	text	no	’random_uniform’::text
`effective_start_date`	date	no
`effective_end_date`	date	yes
`is_active`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“excluded_days”: [“sunday”], “notification_method”: “email”, “test_window_hours”: 4, “makeup_test_allowed”: true}
`site_id`	uuid	yes		Site for which random testing schedule applies.

Foreign keys:

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

Policies:

uds_schedule_delete (DELETE)
uds_schedule_insert (INSERT)
uds_schedule_select (SELECT)
uds_schedule_update (UPDATE)

`rh_uds_tests`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`episode_id`	uuid	no
`test_date`	date	no
`test_type`	text	no	’urine’::text
`is_random`	boolean	no	false
`random_schedule_id`	uuid	yes
`collected_at`	timestamp with time zone	yes
`collected_by`	uuid	yes
`collection_site`	text	yes
`specimen_id`	text	yes
`lab_name`	text	yes
`lab_sent_date`	date	yes
`result`	text	yes
`result_date`	date	yes
`substances_tested`	jsonb	no	’[]‘::jsonb
`follow_up_required`	boolean	no	false
`follow_up_notes`	text	yes
`confirmation_test_ordered`	boolean	no	false
`confirmation_test_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Examples: {“test_kit_id”: “TK-2025-001”, “temperature_valid”: true, “observed_collection”: true, “dilute_specimen”: false, “mro_review_required”: true}
`site_id`	uuid	yes		Site where drug test was administered.
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp for regulatory retention.

Foreign keys:

collected_by → pf_profiles(id)
confirmation_test_id → rh_uds_tests(id)
created_by → pf_profiles(id)
episode_id → rh_episodes(id)
organization_id → pf_organizations(id)
random_schedule_id → rh_uds_random_schedule(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

uds_tests_delete (DELETE)
uds_tests_insert (INSERT)
uds_tests_select (SELECT)
uds_tests_update (UPDATE)

​Entity relationships

​Tables

​rh_alumni

​rh_alumni_engagement

​rh_attendance_records

​rh_audit_findings

​rh_audit_schedules

​rh_beds

​rh_census_snapshots

​rh_chore_assignments

​rh_compliance_checklists

​rh_compliance_requirements

​rh_curfew_checks

​rh_discharge_checklists

​rh_discharge_documents

​rh_discharge_plans

​rh_eligibility_checklists

​rh_episode_payment_status

​rh_episode_phases

​rh_episode_programs

​rh_episodes

​rh_follow_up_contacts

​rh_follow_up_schedules

​rh_grievance_attachments

​rh_grievances

​rh_med_storage_audits

​rh_milestone_completions

​rh_module_settings

​rh_outcome_assessments

​rh_outcome_checkpoints

​rh_outcome_indicators

​rh_outcome_measurements

​rh_participation_metrics

​rh_passes

​rh_phase_milestones

​rh_phase_privileges

​rh_program_phases

​rh_programs

​rh_referrals

​rh_report_definitions

​rh_residences

​rh_resident_agreements

​rh_resident_profiles

​rh_schedule_instances

​rh_schedule_templates

​rh_shift_notes

​rh_significant_event_actions

​rh_significant_event_investigations

​rh_significant_event_types

​rh_significant_events

​rh_staff_assignments

​rh_staff_trainings

​rh_transport_requests

​rh_uds_random_schedule

​rh_uds_tests

Entity relationships

Tables

`rh_alumni`

`rh_alumni_engagement`

`rh_attendance_records`

`rh_audit_findings`

`rh_audit_schedules`

`rh_beds`

`rh_census_snapshots`

`rh_chore_assignments`

`rh_compliance_checklists`

`rh_compliance_requirements`

`rh_curfew_checks`

`rh_discharge_checklists`

`rh_discharge_documents`

`rh_discharge_plans`

`rh_eligibility_checklists`

`rh_episode_payment_status`

`rh_episode_phases`

`rh_episode_programs`

`rh_episodes`

`rh_follow_up_contacts`

`rh_follow_up_schedules`

`rh_grievance_attachments`

`rh_grievances`

`rh_med_storage_audits`

`rh_milestone_completions`

`rh_module_settings`

`rh_outcome_assessments`

`rh_outcome_checkpoints`

`rh_outcome_indicators`

`rh_outcome_measurements`

`rh_participation_metrics`

`rh_passes`

`rh_phase_milestones`

`rh_phase_privileges`

`rh_program_phases`

`rh_programs`

`rh_referrals`

`rh_report_definitions`

`rh_residences`

`rh_resident_agreements`

`rh_resident_profiles`

`rh_schedule_instances`

`rh_schedule_templates`

`rh_shift_notes`

`rh_significant_event_actions`

`rh_significant_event_investigations`

`rh_significant_event_types`

`rh_significant_events`

`rh_staff_assignments`

`rh_staff_trainings`

`rh_transport_requests`

`rh_uds_random_schedule`

`rh_uds_tests`