> ## 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.

# Recovery Housing Database Tables

> Module: Recovery Housing Management Prefix: rh_ Table Count: 51 Last Updated: 2026-01-10

**Module:** Recovery Housing Management\
**Prefix:** `rh_`\
**Table Count:** 51\
**Last Updated:** 2026-01-10

***

## Overview

The RH module provides comprehensive recovery housing management including resident tracking, program phases, daily operations, drug screening, discharge planning, alumni engagement, and outcome measurement.

***

## Table Categories

### 1. Residences & Beds (3 tables)

| Table                 | Columns | Description                   |
| --------------------- | ------- | ----------------------------- |
| `rh_residences`       | 24      | Recovery residence properties |
| `rh_beds`             | 16      | Individual bed inventory      |
| `rh_census_snapshots` | 14      | Daily census records          |

**Residence Structure:**

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

**Bed 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)

| Table                       | Columns | Description                |
| --------------------------- | ------- | -------------------------- |
| `rh_resident_profiles`      | 36      | Resident demographic data  |
| `rh_resident_agreements`    | 18      | Signed agreements          |
| `rh_episodes`               | 28      | Stay episodes (admissions) |
| `rh_episode_phases`         | 14      | Phase progression tracking |
| `rh_episode_programs`       | 10      | Program enrollments        |
| `rh_episode_payment_status` | 16      | Payment tracking           |

**Episode Flow:**

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

**Resident Status:**

* `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)

| Table                      | Columns | Description            |
| -------------------------- | ------- | ---------------------- |
| `rh_programs`              | 20      | Program definitions    |
| `rh_program_phases`        | 18      | Phase definitions      |
| `rh_phase_milestones`      | 14      | Milestone requirements |
| `rh_phase_privileges`      | 12      | Privilege levels       |
| `rh_milestone_completions` | 12      | Milestone tracking     |

**Typical Phase Structure:**

```
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)

| Table                   | Columns | Description             |
| ----------------------- | ------- | ----------------------- |
| `rh_attendance_records` | 14      | Daily attendance        |
| `rh_curfew_checks`      | 12      | Curfew verification     |
| `rh_chore_assignments`  | 14      | House chore tracking    |
| `rh_passes`             | 18      | Leave pass management   |
| `rh_transport_requests` | 16      | Transportation requests |

**Pass Types:**

* `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)

| Table                    | Columns | Description             |
| ------------------------ | ------- | ----------------------- |
| `rh_uds_tests`           | 22      | Drug screen records     |
| `rh_uds_random_schedule` | 12      | Random testing schedule |

**UDS Result Types:**

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

### 6. Medication Management (1 table)

| Table                   | Columns | Description              |
| ----------------------- | ------- | ------------------------ |
| `rh_med_storage_audits` | 16      | Medication locker audits |

### 7. Significant Events (4 tables)

| Table                                 | Columns | Description            |
| ------------------------------------- | ------- | ---------------------- |
| `rh_significant_events`               | 26      | Incident records       |
| `rh_significant_event_types`          | 12      | Event type catalog     |
| `rh_significant_event_actions`        | 14      | Follow-up actions      |
| `rh_significant_event_investigations` | 18      | Investigation tracking |

**Event Categories:**

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

### 8. Staff Scheduling (3 tables)

| Table                   | Columns | Description         |
| ----------------------- | ------- | ------------------- |
| `rh_schedule_templates` | 14      | Schedule patterns   |
| `rh_schedule_instances` | 16      | Actual schedules    |
| `rh_shift_notes`        | 12      | Shift handoff notes |

### 9. Staff Management (2 tables)

| Table                  | Columns | Description                    |
| ---------------------- | ------- | ------------------------------ |
| `rh_staff_assignments` | 12      | Staff-to-residence assignments |
| `rh_staff_trainings`   | 14      | Training records               |

### 10. Discharge Planning (3 tables)

| Table                     | Columns | Description        |
| ------------------------- | ------- | ------------------ |
| `rh_discharge_plans`      | 22      | Discharge planning |
| `rh_discharge_checklists` | 14      | Checklist tracking |
| `rh_discharge_documents`  | 12      | Required documents |

**Discharge Types:**

* `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)

| Table                    | Columns | Description           |
| ------------------------ | ------- | --------------------- |
| `rh_alumni`              | 20      | Alumni records        |
| `rh_alumni_engagement`   | 14      | Engagement activities |
| `rh_follow_up_schedules` | 12      | Follow-up scheduling  |
| `rh_follow_up_contacts`  | 16      | Contact records       |

**Follow-Up Schedule:**

* 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)

| Table                      | Columns | Description          |
| -------------------------- | ------- | -------------------- |
| `rh_outcome_indicators`    | 16      | Outcome definitions  |
| `rh_outcome_measurements`  | 14      | Measurement records  |
| `rh_outcome_assessments`   | 18      | Assessment instances |
| `rh_outcome_checkpoints`   | 12      | Checkpoint tracking  |
| `rh_participation_metrics` | 14      | Participation data   |

**Key Outcome Indicators:**

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

### 13. Referrals (2 tables)

| Table                       | Columns | Description           |
| --------------------------- | ------- | --------------------- |
| `rh_referrals`              | 24      | Referral records      |
| `rh_eligibility_checklists` | 14      | Eligibility screening |

### 14. Compliance (4 tables)

| Table                        | Columns | Description             |
| ---------------------------- | ------- | ----------------------- |
| `rh_compliance_requirements` | 16      | Requirement definitions |
| `rh_compliance_checklists`   | 14      | Compliance tracking     |
| `rh_audit_schedules`         | 12      | Audit scheduling        |
| `rh_audit_findings`          | 16      | Audit results           |

### 15. Reporting (1 table)

| Table                   | Columns | Description           |
| ----------------------- | ------- | --------------------- |
| `rh_report_definitions` | 18      | Custom report configs |

### 16. Module Settings (1 table)

| Table                | Columns | Description      |
| -------------------- | ------- | ---------------- |
| `rh_module_settings` | 30      | RH configuration |

**Key Settings:**

| Setting                       | Type       | Default             |
| ----------------------------- | ---------- | ------------------- |
| `default_program_length_days` | integer    | 90                  |
| `uds_frequency_days`          | integer    | 7                   |
| `curfew_time`                 | time       | '22:00'             |
| `pass_approval_required`      | boolean    | true                |
| `alumni_follow_up_intervals`  | integer\[] | \[30,60,90,180,365] |

***

## Common Query Patterns

### Get Active Residents

```typescript theme={null}
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

```typescript theme={null}
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

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

### Get Phase Progression

```typescript theme={null}
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

RH uses role-based RLS:

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

**Helper Functions:**

* `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

**Sensitive Fields:**

| Table                   | Fields              | Protection      |
| ----------------------- | ------------------- | --------------- |
| `rh_resident_profiles`  | ssn, dob, medical   | Encrypted + RLS |
| `rh_uds_tests`          | results, substances | RLS restricted  |
| `rh_significant_events` | details             | Audit logged    |

***

## See Also

* [RH Module Specs](../../specs/rh/)
* [Census Dashboard](../../specs/rh/specs/RH-01-census-beds-episodes.md)
* [Database Schema Overview](../architecture/DATABASE_SCHEMA.md)