Skip to main content

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.

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

​
Executive Summary

CategoryStatusIssues FoundRemediated
Security Functions✅ Fixed18 functions missing search_pathAll fixed
RLS Policies✅ Fixed1 overly permissive policyFixed
RLS Performance✅ Optimized48 JOIN-based policiesDenormalized
Extensions⚠️ PendingExtension in public schemaRequires dashboard
Auth Security⚠️ PendingLeaked password protection disabledRequires dashboard
Missing Triggers✅ Fixed16 tables missing updated_at triggersAll fixed
Missing custom_fields✅ Fixed8 business tablesAll fixed
FK Indexes✅ Added60 missing FK indexesAll added
Table Naming✅ ExcellentConsistent {core}_{entity} patternN/A
Multi-Tenancy✅ ExcellentConsistent organization_id usageN/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

StandardComplianceNotes
id UUID PRIMARY KEY100%All tables use UUID
organization_id reference95%Child tables inherit via FK
created_at TIMESTAMPTZ98%Standard across tables
updated_at TIMESTAMPTZ100%Now with triggers
custom_fields JSONB95%Added to missing business tables
{core}_ prefix100%Consistent naming

​
Tables Added custom_fields (8 tables)

ModuleTablePurpose
FAfa_budget_alertsBudget alert metadata
FAfa_budget_approvalsApproval workflow metadata
HRhr_disciplinary_actionsHR case metadata
HRhr_grievancesGrievance metadata
HRhr_incidentsIncident metadata
HRhr_investigationsInvestigation metadata
HRhr_offer_counteroffersOffer negotiation metadata
PFpf_notification_batchesBatch notification metadata

​
Tables Added updated_at Triggers (16 tables)

ModuleTables
FAfa_account_balances, fa_credit_memos, fa_customer_payments, fa_customers, fa_invoices, fa_payment_batches, fa_payments, fa_report_definitions
HRhr_disciplinary_actions, hr_grievances, hr_hire_transitions, hr_incidents, hr_investigations, hr_offer_counteroffers
PFpf_notification_batches, pf_wizard_template_versions

​
Module Summary

ModulePrefixTablesDescription
Platform Foundationpf_72Auth, tenancy, navigation, shared services
Human Resourceshr_76Workforce management, payroll, benefits
Finance & Accountingfa_45GL, AP, AR, budgeting, reporting
Forms & Workflowfw_54Form builder, workflow automation
Recovery Housingrh_51Resident management, billing, census
Governance & Riskgr_49Compliance, audits, risk management
Facilities Managementfm_24Assets, work orders, inventory
Leadership OSlo_29Meetings, goals, dashboards
IT Service Managementit_12Tickets, 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:
PriorityTables AffectedIndexes Added
HIGHpf_audit_logs, pf_notifications, fw_form_submissions, hr_*18
MEDIUMpf_ai_conversations, hr_employees, rh_episodes15
STANDARDfa_, 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

DateMigrationChanges
2026-01-10Fix function search_pathsFixed 14 SECURITY DEFINER functions
2026-01-10Fix RLS always-trueReplaced permissive policy with granular policies
2026-01-10Fix IT functions + triggersFixed 4 IT functions, added 16 triggers, added 8 custom_fields columns
2026-01-17FK Indexes Migration 1Added 18 high-priority FK indexes
2026-01-17FK Indexes Migration 2Added 15 medium-priority FK indexes
2026-01-17FK Indexes Migration 3Added 27 standard FK indexes
2026-01-17RLS DenormalizationAdded org_id to 12 child tables, backfilled data
2026-01-17RLS Policy UpdatesUpdated 48 policies to use direct org_id checks