Recovery Housing Database Tables

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

​

Overview

​

Table Categories

​

1. Residences & Beds (3 tables)

rh_residences (property)
    └── pf_sites (linked location)
    └── rh_beds (bed inventory)
            └── capacity, gender, status

• available - Ready for admission
• occupied - Currently assigned
• reserved - Hold for incoming resident
• maintenance - Out of service
• blocked - Administratively blocked

​

2. Resident Management (6 tables)

Admission → rh_episodes (status: 'active')
    ↓
rh_episode_phases (phase progression)
    ↓
Discharge → rh_episodes (status: 'discharged')

• active - Currently in residence
• discharged - Completed/left program
• transferred - Moved to another facility
• awol - Absent without leave
• administrative - Admin discharge

​

3. Programs & Phases (5 tables)

Phase 1: Orientation (Days 1-14)
    - Milestones: House rules quiz, mentor assignment
    - Privileges: Limited passes
    
Phase 2: Foundation (Days 15-60)
    - Milestones: Employment, sponsor
    - Privileges: Extended curfew
    
Phase 3: Independence (Days 61-90)
    - Milestones: Savings goal, treatment compliance
    - Privileges: Overnight passes

​

4. Daily Operations (5 tables)

• day_pass - Same-day return
• overnight_pass - Overnight leave
• work_pass - Employment-related
• medical_pass - Medical appointment
• emergency_pass - Emergency leave

​

5. Drug Screening (2 tables)

• negative - Clean test
• positive - Detected substances
• dilute - Invalid sample
• refused - Test refused
• missed - No-show for test

​

6. Medication Management (1 table)

​

7. Significant Events (4 tables)

• behavioral - Rule violations
• medical - Health incidents
• safety - Safety concerns
• positive - Achievements/milestones
• administrative - Admin events

​

8. Staff Scheduling (3 tables)

​

9. Staff Management (2 tables)

​

10. Discharge Planning (3 tables)

• successful_completion - Completed program
• step_down - Moved to lower level of care
• step_up - Moved to higher level of care
• voluntary - Left voluntarily
• administrative - Admin discharge
• medical - Medical discharge
• awol - Left without notice

​

11. Alumni Engagement (4 tables)

• 30-day post-discharge
• 60-day post-discharge
• 90-day post-discharge
• 6-month post-discharge
• 12-month post-discharge

​

12. Outcomes Measurement (5 tables)

• Sobriety maintenance
• Employment status
• Housing stability
• Treatment engagement
• Legal compliance
• Family reunification

​

13. Referrals (2 tables)

​

14. Compliance (4 tables)

​

15. Reporting (1 table)

​

16. Module Settings (1 table)

​

Common Query Patterns

​

Get Active Residents

const { data } = await supabase
  .from('rh_episodes')
  .select(`
    *,
    resident:rh_resident_profiles(*),
    bed:rh_beds(
      bed_number,
      residence:rh_residences(name)
    ),
    current_phase:rh_episode_phases(
      phase:rh_program_phases(name, level)
    )
  `)
  .eq('status', 'active')
  .eq('organization_id', orgId);

​

Get Census by Residence

const { data } = await supabase
  .from('rh_census_snapshots')
  .select(`
    *,
    residence:rh_residences(name, capacity)
  `)
  .eq('snapshot_date', today)
  .eq('organization_id', orgId);

​

Get UDS History

const { data } = await supabase
  .from('rh_uds_tests')
  .select('*')
  .eq('episode_id', episodeId)
  .order('test_date', { ascending: false });

​

Get Phase Progression

const { data } = await supabase
  .from('rh_episode_phases')
  .select(`
    *,
    phase:rh_program_phases(
      name,
      milestones:rh_phase_milestones(*)
    ),
    completions:rh_milestone_completions(*)
  `)
  .eq('episode_id', episodeId)
  .order('started_at', { ascending: true });

​

RLS Policies

• Staff access to assigned residences
• Manager access to all residences in org
• Resident self-service for limited data

• rh_user_has_residence_access() - Residence assignment check
• rh_user_can_view_resident() - Resident data access
• rh_user_can_modify_episode() - Episode modification

​

PHI Considerations

​

See Also

• RH Module Specs
• Census Dashboard
• Database Schema Overview