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

# Performance Optimization Log

> > Version: 1.0.0 > Last Updated: 2026-01-17 > Status: Active

> **Version:** 1.0.0\
> **Last Updated:** 2026-01-17\
> **Status:** Active

## Overview

This document tracks all database performance optimizations for Encore Health OS Platform.

## Optimizations Completed

### 2026-01-17: Phase 2A - Foreign Key Indexes (60 indexes)

| Migration   | Indexes | Tables                                                                                                                                                                                                        |
| ----------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Migration 1 | 18      | pf\_audit\_logs, pf\_notifications, fw\_form\_submissions, hr\_credential\_renewal\_workflows, pf\_tasks, lo\_issues, lo\_meetings, hr\_job\_postings, hr\_offers, hr\_employee\_credentials, hr\_interviews  |
| Migration 2 | 15      | pf\_ai\_conversations, pf\_org\_dashboard\_defaults, hr\_applications, hr\_employees, pf\_wizard\_templates, rh\_episodes, hr\_leave\_requests, hr\_incidents, hr\_pay\_rates                                 |
| Migration 3 | 27      | fa\_invoices, fa\_vendor\_bills, fa\_accounts, fa\_bank\_accounts, fa\_budgets, fa\_customer\_payments, fa\_credit\_memos, gr\_contracts, gr\_policies, fm\_work\_orders, fm\_inventory\_items, pf\_documents |

### 2026-01-17: Phase 3 - RLS Optimization (12 tables)

Added denormalized `organization_id` columns to child tables for faster RLS evaluation:

| Table                           | Parent Table              | Status     |
| ------------------------------- | ------------------------- | ---------- |
| fa\_invoice\_lines              | fa\_invoices              | ✅ Complete |
| fa\_vendor\_bill\_lines         | fa\_vendor\_bills         | ✅ Complete |
| fa\_journal\_entry\_lines       | fa\_journal\_entries      | ✅ Complete |
| fa\_budget\_lines               | fa\_budgets               | ✅ Complete |
| fa\_credit\_memo\_lines         | fa\_credit\_memos         | ✅ Complete |
| fa\_purchase\_order\_lines      | fa\_purchase\_orders      | ✅ Complete |
| fa\_bank\_statement\_lines      | fa\_bank\_statements      | ✅ Complete |
| fw\_form\_fields                | fw\_forms                 | ✅ Complete |
| fw\_form\_versions              | fw\_forms                 | ✅ Complete |
| it\_onboarding\_tasks           | it\_onboarding\_templates | ✅ Complete |
| it\_onboarding\_task\_instances | it\_onboarding\_instances | ✅ Complete |
| pf\_custom\_object\_fields      | pf\_custom\_objects       | ✅ Complete |

### 2026-01-17: Phase 4 - RLS Policy Updates (48 policies)

Updated RLS policies on 12 denormalized tables to use direct `organization_id` checks:

| Table                           | Policies Updated | Pattern Change          |
| ------------------------------- | ---------------- | ----------------------- |
| fa\_invoice\_lines              | 4                | EXISTS → direct org\_id |
| fa\_vendor\_bill\_lines         | 4                | EXISTS → direct org\_id |
| fa\_journal\_entry\_lines       | 4                | EXISTS → direct org\_id |
| fa\_budget\_lines               | 4                | EXISTS → direct org\_id |
| fa\_credit\_memo\_lines         | 4                | EXISTS → direct org\_id |
| fa\_purchase\_order\_lines      | 4                | EXISTS → direct org\_id |
| fa\_bank\_statement\_lines      | 4                | EXISTS → direct org\_id |
| fw\_form\_fields                | 4                | EXISTS → direct org\_id |
| fw\_form\_versions              | 2                | EXISTS → direct org\_id |
| it\_onboarding\_tasks           | 4                | EXISTS → direct org\_id |
| it\_onboarding\_task\_instances | 4                | EXISTS → direct org\_id |
| pf\_custom\_object\_fields      | 4                | EXISTS → direct org\_id |

**Expected Impact:** 2-5x faster queries on child tables

## Performance Monitoring

### Query Performance

```sql theme={null}
SELECT * FROM pf_get_slow_queries();
```

### Index Usage

```sql theme={null}
SELECT * FROM pf_get_unused_indexes();
```

### Table Bloat

```sql theme={null}
SELECT * FROM pf_get_table_bloat();
```

### 2026-01-21: Phase 5 - Performance Indexes Phase 2 ✅ DEPLOYED

Added composite indexes for date range queries, multi-column filters, and sort optimization:

| Category             | Indexes | Tables                                                                            |
| -------------------- | ------- | --------------------------------------------------------------------------------- |
| Date Range Queries   | 4       | rh\_schedule\_instances, rh\_attendance\_records, hr\_leave\_requests, gr\_audits |
| Multi-Column Filters | 3       | it\_tickets, rh\_episodes, fm\_work\_orders (conditional on site\_id column)      |
| Sort Optimization    | 5       | gr\_audits, it\_tickets, hr\_leave\_requests, rh\_episodes, fm\_work\_orders      |
| Search Optimization  | 1       | it\_tickets (subject text search)                                                 |

**Migration:** `20260121000000_performance_indexes_phase2.sql`\
**Status:** ✅ Successfully deployed to **Production** (zkgxozahyczcnzpwhbbf) and **Staging** (bcuftbeczbudndukafbc)\
**Note:** Indexes use conditional creation (DO blocks) to handle tables with/without `deleted_at` and `site_id` columns

**Expected Impact:** 2-3x faster queries for date ranges, multi-column filters, and sorted lists

### 2026-02-15: Phase 6A - Slow Query Indexes

Added composite indexes for the slowest application queries identified via `pg_stat_statements`:

| Table                      | Index                   | Query Pattern                                                     | Before (ms) |
| -------------------------- | ----------------------- | ----------------------------------------------------------------- | ----------- |
| hr\_federal\_tax\_brackets | org\_year\_freq         | org + year + frequency + ORDER BY bracket\_floor                  | 2,729       |
| hr\_federal\_tax\_brackets | org\_year\_freq\_status | org + year + frequency + filing\_status + ORDER BY bracket\_floor | 2,729       |
| hr\_deduction\_types       | org\_category\_name     | org + ORDER BY category, name                                     | 2,186       |
| hr\_deduction\_types       | org\_active (partial)   | org + is\_active WHERE is\_active = true                          | 2,186       |
| pf\_sites                  | org\_id                 | org (RLS optimization)                                            | 1,092       |
| hr\_fica\_config           | org\_year               | org + tax\_year                                                   | N/A         |

**Migration:** `20260215160000_performance_slow_query_indexes.sql`
**Expected Impact:** 10-15x faster queries on tax brackets and deduction types

### 2026-02-15: Phase 6B - FK Indexes Phase 3

Added indexes for \~27 remaining unindexed foreign key columns (created\_by, updated\_by, assigned\_to) across CE, IT, FM, GR, and LO tables:

| Core | Tables                                                                                 | Columns Indexed                        |
| ---- | -------------------------------------------------------------------------------------- | -------------------------------------- |
| CE   | activities, calls, campaigns, contact\_segments, contacts, events, lead\_stages, leads | created\_by, updated\_by               |
| IT   | change\_implementations, change\_requests                                              | created\_by, updated\_by, assigned\_to |
| FM   | vendors, assets                                                                        | created\_by, updated\_by               |
| GR   | policies, training\_records                                                            | created\_by, updated\_by               |
| LO   | goals, meetings                                                                        | created\_by, updated\_by               |

**Migration:** `20260215160100_performance_fk_indexes_phase3.sql`
**Expected Impact:** Faster cascading deletes on user removal; eliminates Supabase Advisor warnings

## Manual Actions Required

| Action                               | Status                |
| ------------------------------------ | --------------------- |
| Move `pg_net` to extensions schema   | ⏳ Pending (Dashboard) |
| Enable Leaked Password Protection    | ⏳ Pending (Dashboard) |
| Enable MFA options                   | ⏳ Pending (Dashboard) |
| Run baseline assessment queries      | ⏳ Pending (Week 1)    |
| Review Supabase Performance Advisors | ⏳ Pending (Week 1)    |

***

**See:** `docs/database/INDEX_OPTIMIZATION_REPORT.md` for detailed index analysis.
**See:** `docs/database/RLS_POLICY_AUDIT.md` for RLS optimization details.
**See:** `reports/performance/optimization-report-2026-01-21.md` for comprehensive optimization plan.