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.
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 |
SELECT * FROM pf_get_slow_queries();
Index Usage
SELECT * FROM pf_get_unused_indexes();
Table Bloat
SELECT * FROM pf_get_table_bloat();
| 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) |
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 |
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 |
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.
