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

# Database Audit Report

> Generated: 2026-01-10 Updated: 2026-01-17 (Performance Optimization Phase) Database: Encore Health OS Platform Total Tables: 432 Total Functions: 100+

**Generated:** 2026-01-10\
**Updated:** 2026-01-17 (Performance Optimization Phase)\
**Database:** Encore Health OS Platform\
**Total Tables:** 432\
**Total Functions:** 100+

***

## Executive Summary

| Category                | Status      | Issues Found                            | Remediated         |
| ----------------------- | ----------- | --------------------------------------- | ------------------ |
| Security Functions      | ✅ Fixed     | 18 functions missing `search_path`      | All fixed          |
| RLS Policies            | ✅ Fixed     | 1 overly permissive policy              | Fixed              |
| RLS Performance         | ✅ Optimized | 48 JOIN-based policies                  | Denormalized       |
| Extensions              | ⚠️ Pending  | Extension in public schema              | Requires dashboard |
| Auth Security           | ⚠️ Pending  | Leaked password protection disabled     | Requires dashboard |
| Missing Triggers        | ✅ Fixed     | 16 tables missing `updated_at` triggers | All fixed          |
| Missing `custom_fields` | ✅ Fixed     | 8 business tables                       | All fixed          |
| FK Indexes              | ✅ Added     | 60 missing FK indexes                   | All added          |
| Table Naming            | ✅ Excellent | Consistent `{core}_{entity}` pattern    | N/A                |
| Multi-Tenancy           | ✅ Excellent | Consistent `organization_id` usage      | N/A                |

**Overall Health Score:** 96/100 (2 pending dashboard-only fixes)

***

## Security Audit

### Fixed Issues

#### 1. Functions Missing `search_path` (18 total - ALL FIXED)

**Risk Level:** HIGH\
**Issue:** SECURITY DEFINER functions without `SET search_path = public` are vulnerable to SQL injection via schema poisoning.

**Migration 1 (2026-01-10):** Fixed 14 functions

* `fa_days_past_due`
* `gr_calculate_risk_score_and_rating`
* `hr_hire_transitions_update_timestamp`
* `pf_has_org_access`
* `rh_calculate_length_of_stay`
* And 9 more...

**Migration 2 (2026-01-10):** Fixed 4 IT functions

* `it_validate_knowledge_base_article`
* `it_validate_sla_policy`
* `it_validate_ticket`
* `it_validate_ticket_attachment`

#### 2. Overly Permissive RLS Policy (FIXED)

**Risk Level:** CRITICAL\
**Table:** `hr_timesheet_entries`\
**Issue:** Policy "System can manage entries" was always true, allowing unrestricted write access.

**Fix:** Replaced with granular policies:

* `hr_timesheet_entries_select` - Org-scoped read access
* `hr_timesheet_entries_insert` - Self or manager can insert
* `hr_timesheet_entries_update` - Self or manager can update
* `hr_timesheet_entries_delete` - HR admin only

### Pending Issues (Require Dashboard Access)

#### 1. Extension in Public Schema

**Risk Level:** MEDIUM\
**Extension:** `pg_net`\
**Issue:** Extensions in public schema can cause namespace pollution.\
**Fix:** Move to `extensions` schema via Supabase dashboard.

#### 2. Leaked Password Protection Disabled

**Risk Level:** MEDIUM\
**Issue:** Users can set passwords that have been exposed in data breaches.\
**Fix:** Enable via Supabase Auth settings in dashboard.

***

## Consistency Audit

### Column Standards Compliance

| Standard                    | Compliance | Notes                            |
| --------------------------- | ---------- | -------------------------------- |
| `id UUID PRIMARY KEY`       | 100%       | All tables use UUID              |
| `organization_id` reference | 95%        | Child tables inherit via FK      |
| `created_at TIMESTAMPTZ`    | 98%        | Standard across tables           |
| `updated_at TIMESTAMPTZ`    | 100%       | Now with triggers                |
| `custom_fields JSONB`       | 95%        | Added to missing business tables |
| `{core}_` prefix            | 100%       | Consistent naming                |

### Tables Added `custom_fields` (8 tables)

| Module | Table                     | Purpose                     |
| ------ | ------------------------- | --------------------------- |
| FA     | `fa_budget_alerts`        | Budget alert metadata       |
| FA     | `fa_budget_approvals`     | Approval workflow metadata  |
| HR     | `hr_disciplinary_actions` | HR case metadata            |
| HR     | `hr_grievances`           | Grievance metadata          |
| HR     | `hr_incidents`            | Incident metadata           |
| HR     | `hr_investigations`       | Investigation metadata      |
| HR     | `hr_offer_counteroffers`  | Offer negotiation metadata  |
| PF     | `pf_notification_batches` | Batch notification metadata |

### Tables Added `updated_at` Triggers (16 tables)

| Module | Tables                                                                                                                                                        |
| ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| FA     | `fa_account_balances`, `fa_credit_memos`, `fa_customer_payments`, `fa_customers`, `fa_invoices`, `fa_payment_batches`, `fa_payments`, `fa_report_definitions` |
| HR     | `hr_disciplinary_actions`, `hr_grievances`, `hr_hire_transitions`, `hr_incidents`, `hr_investigations`, `hr_offer_counteroffers`                              |
| PF     | `pf_notification_batches`, `pf_wizard_template_versions`                                                                                                      |

***

## Module Summary

| Module                | Prefix | Tables | Description                                |
| --------------------- | ------ | ------ | ------------------------------------------ |
| Platform Foundation   | `pf_`  | 72     | Auth, tenancy, navigation, shared services |
| Human Resources       | `hr_`  | 76     | Workforce management, payroll, benefits    |
| Finance & Accounting  | `fa_`  | 45     | GL, AP, AR, budgeting, reporting           |
| Forms & Workflow      | `fw_`  | 54     | Form builder, workflow automation          |
| Recovery Housing      | `rh_`  | 51     | Resident management, billing, census       |
| Governance & Risk     | `gr_`  | 49     | Compliance, audits, risk management        |
| Facilities Management | `fm_`  | 24     | Assets, work orders, inventory             |
| Leadership OS         | `lo_`  | 29     | Meetings, goals, dashboards                |
| IT Service Management | `it_`  | 12     | Tickets, SLAs, knowledge base              |

***

## Recommendations

### Immediate (Dashboard Required)

1. **Enable Leaked Password Protection**
   * Navigate to: Authentication → Settings → Password Security
   * Enable "Check for leaked passwords"

2. **Move `pg_net` Extension**
   * Create `extensions` schema if not exists
   * Reinstall `pg_net` in `extensions` schema

### Future Improvements

1. **Partitioning Strategy**
   * Consider partitioning `pf_audit_logs` by month
   * Consider partitioning `fw_automation_logs` by month

2. **Archive Policy**
   * Implement archive for audit logs > 2 years
   * Implement archive for form submissions > 7 years

3. **Index Optimization**
   * Add composite indexes for common query patterns
   * Review slow query logs quarterly

***

## Performance Optimizations (2026-01-17)

### Phase 2A: Foreign Key Indexes

Added 60 FK indexes across 3 migrations to improve JOIN performance:

| Priority | Tables Affected                                                   | Indexes Added |
| -------- | ----------------------------------------------------------------- | ------------- |
| HIGH     | pf\_audit\_logs, pf\_notifications, fw\_form\_submissions, hr\_\* | 18            |
| MEDIUM   | pf\_ai\_conversations, hr\_employees, rh\_episodes                | 15            |
| STANDARD | fa\_*, gr\_*, fm\_\*                                              | 27            |

### Phase 3: RLS Denormalization

Added `organization_id` to 12 child tables and updated 48 RLS policies:

* Eliminated JOIN-based EXISTS subqueries
* Direct org access checks now used
* Expected 2-5x query improvement on child tables

***

## Appendix: Migration History

| Date       | Migration                   | Changes                                                                 |
| ---------- | --------------------------- | ----------------------------------------------------------------------- |
| 2026-01-10 | Fix function search\_paths  | Fixed 14 SECURITY DEFINER functions                                     |
| 2026-01-10 | Fix RLS always-true         | Replaced permissive policy with granular policies                       |
| 2026-01-10 | Fix IT functions + triggers | Fixed 4 IT functions, added 16 triggers, added 8 custom\_fields columns |
| 2026-01-17 | FK Indexes Migration 1      | Added 18 high-priority FK indexes                                       |
| 2026-01-17 | FK Indexes Migration 2      | Added 15 medium-priority FK indexes                                     |
| 2026-01-17 | FK Indexes Migration 3      | Added 27 standard FK indexes                                            |
| 2026-01-17 | RLS Denormalization         | Added org\_id to 12 child tables, backfilled data                       |
| 2026-01-17 | RLS Policy Updates          | Updated 48 policies to use direct org\_id checks                        |