Encore OS — Documentation

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

Overview

This document tracks all database performance optimizations for Encore 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

SELECT * FROM pf_get_slow_queries();

Index Usage

SELECT * FROM pf_get_unused_indexes();

Table Bloat

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.

Start Here

Module Engineering Snapshots

Canonical AI Governance

Architecture

Integrations

Development

Decision Guides

Database

Module Reference

Testing

Migration

Operations

Governance & Security

Platform Internals

Performance Optimization Log

Overview

Optimizations Completed

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

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

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

Performance Monitoring

Query Performance

Index Usage

Table Bloat

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

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

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

Manual Actions Required

​Overview

​Optimizations Completed

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

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

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

​Performance Monitoring

​Query Performance

​Index Usage

​Table Bloat

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

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

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

​Manual Actions Required

Overview

Optimizations Completed

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

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

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

Performance Monitoring

Query Performance

Index Usage

Table Bloat

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

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

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

Manual Actions Required