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

# PostgreSQL Extensions Review & Recommendations

> This document reviews currently installed PostgreSQL extensions and provides recommendations for additional extensions that would benefit the Encore OS platfor…

**Version:** 1.1.0\
**Date:** 2026-01-17 (Updated)\
**Status:** ✅ Phase 1 Complete\
**Database:** PostgreSQL 15 (Supabase)

***

## Executive Summary

This document reviews currently installed PostgreSQL extensions and provides recommendations for additional extensions that would benefit the Encore OS platform based on:

1. Current extension usage patterns
2. Codebase analysis (full-text search, ILIKE queries, JSONB operations)
3. Multi-tenant healthcare ERP requirements
4. Performance optimization needs

**Key Findings:**

* ✅ **10 extensions currently installed** (pgcrypto, pg\_stat\_statements, pg\_net, pg\_cron, uuid-ossp, pg\_graphql, pgmq, pg\_trgm, unaccent, btree\_gin)
* ✅ **3 high-priority extensions enabled** (pg\_trgm, unaccent, btree\_gin) - Phase 1 Complete
* 📊 **2 medium-priority extensions** for future optimization (pg\_partman, pg\_jsonschema)
* 🔍 **Full-text search patterns optimized** - trigram indexes added

***

## Currently Installed Extensions

| Extension            | Version | Schema      | Purpose                                                    | Status    |
| -------------------- | ------- | ----------- | ---------------------------------------------------------- | --------- |
| `pgcrypto`           | 1.3     | extensions  | Cryptographic functions (SSN encryption, password hashing) | ✅ Active  |
| `pg_stat_statements` | 1.11    | extensions  | Query performance monitoring                               | ✅ Active  |
| `pg_net`             | 0.19.5  | extensions  | Async HTTP requests from database                          | ✅ Active  |
| `pg_cron`            | 1.6.4   | pg\_catalog | Job scheduler for scheduled tasks                          | ✅ Active  |
| `uuid-ossp`          | 1.1     | extensions  | UUID generation                                            | ✅ Active  |
| `pg_graphql`         | 1.5.11  | graphql     | GraphQL API support                                        | ✅ Active  |
| `pgmq`               | 1.5.1   | pgmq        | Lightweight message queue                                  | ✅ Active  |
| `supabase_vault`     | 0.3.1   | vault       | Supabase Vault Extension                                   | ✅ Active  |
| `pg_trgm`            | 1.6     | public      | Trigram text search                                        | ✅ **NEW** |
| `unaccent`           | 1.1     | public      | Accent-insensitive search                                  | ✅ **NEW** |
| `btree_gin`          | 1.3     | public      | GIN indexes for standard types                             | ✅ **NEW** |

**Total:** 11 extensions installed

***

## High-Priority Recommendations

### 1. `pg_trgm` - Trigram Text Search ⭐⭐⭐

**Priority:** HIGH\
**Impact:** Performance improvement for text search\
**Effort:** Low (enable extension, add indexes)

**Why:**

* **Current Pattern:** Extensive use of `ILIKE '%pattern%'` queries (found 25+ instances)
* **Problem:** ILIKE with leading wildcards cannot use indexes efficiently
* **Solution:** Trigram indexes enable fast fuzzy text matching

**Usage Patterns Found:**

```sql theme={null}
-- Current pattern (slow on large tables)
WHERE name ILIKE '%search%'
WHERE description ILIKE '%pattern%'

-- With pg_trgm (fast with index)
WHERE name % 'search'  -- Similarity operator
WHERE similarity(name, 'search') > 0.3
```

**Tables That Would Benefit:**

* `hr_employees` (name, job\_title searches)
* `hr_positions` (title searches)
* `pf_documents` (title, content searches)
* `fw_workflow_templates` (name, description searches)
* `it_knowledge_base_articles` (title, content searches)
* All tables with full-text search indexes

**Implementation:**

```sql theme={null}
-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Add trigram indexes for common search columns
CREATE INDEX idx_hr_employees_name_trgm ON hr_employees USING gin (name gin_trgm_ops);
CREATE INDEX idx_hr_employees_job_title_trgm ON hr_employees USING gin (job_title gin_trgm_ops);
CREATE INDEX idx_pf_documents_title_trgm ON pf_documents USING gin (title gin_trgm_ops);
```

**Performance Impact:**

* **Before:** Sequential scan on ILIKE queries (slow on large tables)
* **After:** Index scan with trigram matching (10-100x faster)
* **Storage:** \~30% of indexed column size (acceptable trade-off)

**Migration Path:**

1. Enable extension in migration
2. Add trigram indexes for high-traffic search columns
3. Update search queries to use `%` operator or `similarity()` function
4. Monitor query performance improvement

***

### 2. `unaccent` - Accent-Insensitive Search ⭐⭐⭐

**Priority:** HIGH\
**Impact:** Better user experience for name searches\
**Effort:** Low (enable extension, update indexes)

**Why:**

* **Healthcare Context:** Names often contain accents (José, François, etc.)
* **Current Problem:** `ILIKE` searches won't match "Jose" when searching for "José"
* **Solution:** Unaccent extension removes accents for search

**Usage Pattern:**

```sql theme={null}
-- Enable extension
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Create unaccented search index
CREATE INDEX idx_hr_employees_name_unaccent 
ON hr_employees USING gin (unaccent(name) gin_trgm_ops);

-- Search query
WHERE unaccent(name) ILIKE unaccent('%jose%')
```

**Tables That Would Benefit:**

* `hr_employees` (first\_name, last\_name)
* `pf_profiles` (first\_name, last\_name)
* `rh_residents` (first\_name, last\_name)
* Any table with person names

**Performance Impact:**

* Minimal overhead (unaccent is fast)
* Significantly better search results for international names

***

### 3. `btree_gin` - GIN Indexes for Standard Types ⭐⭐

**Priority:** HIGH\
**Impact:** Better JSONB query performance\
**Effort:** Low (enable extension, add indexes)

**Why:**

* **Current Pattern:** 363 tables with `custom_fields JSONB` columns
* **Problem:** Standard GIN indexes on JSONB can be large
* **Solution:** btree\_gin allows composite indexes mixing JSONB with other types

**Usage Pattern:**

```sql theme={null}
-- Enable extension
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- Composite index for organization_id + custom_fields queries
CREATE INDEX idx_hr_employees_org_custom_fields 
ON hr_employees USING gin (organization_id, custom_fields);

-- Query example
WHERE organization_id = '...' 
AND custom_fields @> '{"badge_number": "12345"}'
```

**Performance Impact:**

* Faster queries filtering by organization\_id + custom\_fields
* Smaller indexes than separate GIN indexes
* Better query planning for multi-tenant queries

***

### 4. `pg_partman` - Table Partitioning ⭐⭐

**Priority:** HIGH (Future)\
**Impact:** Performance for large audit/log tables\
**Effort:** Medium (requires planning and migration)

**Why:**

* **Data Archival Plan:** Identified need to archive old audit logs, activity logs
* **Problem:** Large tables (audit logs, workflow executions) will slow down over time
* **Solution:** Partition tables by date for easier archival and better query performance

**Tables That Would Benefit:**

* `pf_audit_logs` (partition by `created_at` monthly)
* `pf_activity_logs` (partition by `created_at` monthly)
* `fw_workflow_executions` (partition by `created_at` monthly)
* `pf_notifications` (partition by `created_at` monthly)

**Implementation:**

```sql theme={null}
-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- Convert existing table to partitioned
SELECT partman.create_parent(
  p_parent_table => 'public.pf_audit_logs',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => 'monthly',
  p_premake => 3
);
```

**Performance Impact:**

* **Query Performance:** Faster queries on recent data (only scan relevant partitions)
* **Maintenance:** Easy to drop old partitions (vs. DELETE operations)
* **Storage:** Better compression and index efficiency per partition

**Migration Path:**

1. Review data archival plan (`docs/migration/DATA_ARCHIVAL_PLAN.md`)
2. Plan partition strategy (monthly vs. quarterly)
3. Test partitioning on staging environment
4. Migrate production tables during maintenance window

**Note:** This is a **future optimization** - implement after monitoring shows performance issues with large tables.

***

### 5. `pg_jsonschema` - JSONB Validation ⭐

**Priority:** MEDIUM\
**Impact:** Data quality for custom\_fields\
**Effort:** Low (enable extension, add check constraints)

**Why:**

* **Current Pattern:** 363 tables with `custom_fields JSONB DEFAULT '{}'`
* **Problem:** No validation of JSONB structure (could store invalid data)
* **Solution:** Validate custom\_fields against JSON Schema

**Usage Pattern:**

```sql theme={null}
-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_jsonschema;

-- Add validation constraint
ALTER TABLE hr_employees
ADD CONSTRAINT hr_employees_custom_fields_schema
CHECK (jsonb_matches_schema(
  '{
    "type": "object",
    "properties": {
      "badge_number": {"type": "string"},
      "external_id": {"type": "string"}
    },
    "additionalProperties": true
  }'::jsonb,
  custom_fields
));
```

**Performance Impact:**

* Minimal (validation only on INSERT/UPDATE)
* Prevents invalid data from being stored
* Better data quality for reporting

**Consideration:**

* May be too restrictive if custom\_fields structure varies significantly
* Consider making validation optional per organization

***

## Medium-Priority Recommendations

### 6. `citext` - Case-Insensitive Text Type

**Priority:** MEDIUM\
**Impact:** Simpler queries (no need for LOWER()/UPPER())\
**Effort:** Low (enable extension, migrate columns if desired)

**Why:**

* Many queries use `LOWER(column) = LOWER(value)` or `ILIKE`
* `citext` type handles case-insensitivity at the type level

**Consideration:**

* **Not recommended for existing tables** (would require column type changes)
* **Consider for new tables** where case-insensitivity is required
* Current ILIKE pattern is acceptable and doesn't require migration

**Usage:**

```sql theme={null}
-- For new tables only
CREATE TABLE example (
  id UUID PRIMARY KEY,
  email citext NOT NULL UNIQUE  -- Case-insensitive email
);
```

***

### 7. `ltree` - Hierarchical Tree Structures

**Priority:** MEDIUM (If Needed)\
**Impact:** Efficient tree queries\
**Effort:** Medium (requires schema changes)

**Why:**

* Could be useful for organizational hierarchies, category trees
* More efficient than recursive CTEs for tree queries

**Consideration:**

* **Only if tree structures are needed**
* Current patterns don't show hierarchical data requirements
* **Defer until needed**

***

### 8. `tcn` - Triggered Change Notifications

**Priority:** MEDIUM\
**Impact:** Event-driven workflows\
**Effort:** Low (enable extension, add triggers)

**Why:**

* **Current Pattern:** Event-based integration for workflows (FW-16)
* **Problem:** Manual event publishing via triggers
* **Solution:** Automatic change notifications via tcn extension

**Usage Pattern:**

```sql theme={null}
-- Enable extension
CREATE EXTENSION IF NOT EXISTS tcn;

-- Add trigger for change notifications
CREATE TRIGGER hr_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON hr_employees
FOR EACH ROW EXECUTE FUNCTION triggered_change_notification();
```

**Consideration:**

* **May duplicate existing event publishing logic**
* Review FW-16 event integration patterns before implementing
* **Defer until event system review**

***

## Low-Priority / Not Recommended

### Extensions to Avoid

| Extension       | Why Not Recommended                                         |
| --------------- | ----------------------------------------------------------- |
| `hstore`        | JSONB already used throughout codebase                      |
| `moddatetime`   | Custom triggers already handle `updated_at`                 |
| `fuzzystrmatch` | `pg_trgm` provides better fuzzy matching                    |
| `pgroonga`      | Full-text search already implemented with native PostgreSQL |
| `vector`        | No AI/ML use cases identified in codebase                   |

***

## Implementation Plan

### Phase 1: Immediate (Week 1-2) ✅ COMPLETE

**Priority Extensions (All Enabled 2026-01-17):**

1. ✅ **pg\_trgm** - Enabled with trigram indexes for high-traffic search columns
2. ✅ **unaccent** - Enabled with unaccented search indexes for name columns
3. ✅ **btree\_gin** - Enabled for composite index optimization

**Migration:**

```sql theme={null}
-- Migration: Enable high-priority extensions
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- Add trigram indexes for common search patterns
-- (See Phase 1 implementation script)
```

**Testing:**

* Verify search performance improvement
* Test accent-insensitive searches
* Monitor index sizes

***

### Phase 2: Optimization (Month 2-3)

**Conditional Extensions:**
4\. **pg\_jsonschema** - If custom\_fields validation is needed
5\. **pg\_partman** - If audit/log tables exceed 1M rows

**Decision Criteria:**

* Monitor table sizes monthly
* Review data archival plan progress
* Assess custom\_fields data quality issues

***

### Phase 3: Future (As Needed)

**Deferred Extensions:**
6\. **citext** - For new tables requiring case-insensitive text
7\. **ltree** - If hierarchical data structures are needed
8\. **tcn** - If event system review recommends it

***

## Migration Script Template

```sql theme={null}
-- ============================================================================
-- PostgreSQL Extensions - Phase 1 Implementation
-- ============================================================================
-- Enable high-priority extensions for text search and JSONB optimization
-- Date: 2026-01-16
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. Enable Extensions
-- ----------------------------------------------------------------------------

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- ----------------------------------------------------------------------------
-- 2. Add Trigram Indexes for High-Traffic Search Columns
-- ----------------------------------------------------------------------------

-- HR Module
CREATE INDEX IF NOT EXISTS idx_hr_employees_name_trgm 
ON hr_employees USING gin (name gin_trgm_ops)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_hr_employees_job_title_trgm 
ON hr_employees USING gin (job_title gin_trgm_ops)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_hr_positions_title_trgm 
ON hr_positions USING gin (title gin_trgm_ops)
WHERE deleted_at IS NULL;

-- Platform Module
CREATE INDEX IF NOT EXISTS idx_pf_documents_title_trgm 
ON pf_documents USING gin (title gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_pf_profiles_name_trgm 
ON pf_profiles USING gin (
  (first_name || ' ' || last_name) gin_trgm_ops
);

-- Forms & Workflow Module
CREATE INDEX IF NOT EXISTS idx_fw_workflow_templates_name_trgm 
ON fw_workflow_templates USING gin (name gin_trgm_ops);

-- IT Module
CREATE INDEX IF NOT EXISTS idx_it_knowledge_base_articles_title_trgm 
ON it_knowledge_base_articles USING gin (title gin_trgm_ops);

-- ----------------------------------------------------------------------------
-- 3. Add Unaccented Search Indexes for Name Columns
-- ----------------------------------------------------------------------------

CREATE INDEX IF NOT EXISTS idx_hr_employees_name_unaccent_trgm 
ON hr_employees USING gin (unaccent(name) gin_trgm_ops)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_pf_profiles_name_unaccent_trgm 
ON pf_profiles USING gin (
  unaccent(first_name || ' ' || last_name) gin_trgm_ops
);

-- ----------------------------------------------------------------------------
-- 4. Add Composite GIN Indexes (btree_gin) for Multi-Tenant + JSONB Queries
-- ----------------------------------------------------------------------------

-- Example: If queries filter by organization_id + custom_fields
-- CREATE INDEX IF NOT EXISTS idx_hr_employees_org_custom_fields 
-- ON hr_employees USING gin (organization_id, custom_fields)
-- WHERE deleted_at IS NULL;

-- ----------------------------------------------------------------------------
-- 5. Comments
-- ----------------------------------------------------------------------------

COMMENT ON EXTENSION pg_trgm IS 
  'Trigram text search for fast fuzzy matching (replaces slow ILIKE queries)';

COMMENT ON EXTENSION unaccent IS 
  'Accent-insensitive text search for international names';

COMMENT ON EXTENSION btree_gin IS 
  'GIN indexes for standard types, enables composite indexes with JSONB';
```

***

## Performance Monitoring

### Before/After Metrics

**Track these metrics after enabling extensions:**

1. **Search Query Performance:**
   ```sql theme={null}
   -- Before: ILIKE query execution time
   EXPLAIN ANALYZE SELECT * FROM hr_employees 
   WHERE name ILIKE '%john%';

   -- After: Trigram query execution time
   EXPLAIN ANALYZE SELECT * FROM hr_employees 
   WHERE name % 'john';
   ```

2. **Index Sizes:**
   ```sql theme={null}
   SELECT 
     schemaname,
     tablename,
     indexname,
     pg_size_pretty(pg_relation_size(indexrelid)) as index_size
   FROM pg_stat_user_indexes
   WHERE indexname LIKE '%_trgm'
   ORDER BY pg_relation_size(indexrelid) DESC;
   ```

3. **Index Usage:**
   ```sql theme={null}
   SELECT 
     schemaname,
     tablename,
     indexrelname,
     idx_scan,
     idx_tup_read,
     idx_tup_fetch
   FROM pg_stat_user_indexes
   WHERE indexname LIKE '%_trgm'
   ORDER BY idx_scan DESC;
   ```

***

## Rollback Plan

If extensions cause issues:

```sql theme={null}
-- Remove indexes first
DROP INDEX IF EXISTS idx_hr_employees_name_trgm;
DROP INDEX IF EXISTS idx_hr_employees_name_unaccent_trgm;
-- ... (remove all trigram indexes)

-- Drop extensions (only if necessary)
-- DROP EXTENSION IF EXISTS pg_trgm;
-- DROP EXTENSION IF EXISTS unaccent;
-- DROP EXTENSION IF EXISTS btree_gin;
```

**Note:** Extensions themselves don't break existing functionality - only new indexes/queries use them.

***

## References

### Project Documentation

* **Performance Recommendations:** `docs/migration/SUPABASE_PERFORMANCE_RECOMMENDATIONS.md`
* **Data Archival Plan:** `docs/migration/DATA_ARCHIVAL_PLAN.md`
* **Search Framework:** `specs/pf/specs/PF-57-search-filter-sort-framework.md`
* **Database Schema:** `docs/architecture/DATABASE_SCHEMA.md`

### External Resources

* **pg\_trgm Documentation:** [https://www.postgresql.org/docs/current/pgtrgm.html](https://www.postgresql.org/docs/current/pgtrgm.html)
* **unaccent Documentation:** [https://www.postgresql.org/docs/current/unaccent.html](https://www.postgresql.org/docs/current/unaccent.html)
* **btree\_gin Documentation:** [https://www.postgresql.org/docs/current/btree-gin.html](https://www.postgresql.org/docs/current/btree-gin.html)
* **pg\_partman Documentation:** [https://github.com/pgpartman/pg\_partman](https://github.com/pgpartman/pg_partman)

***

## Summary

**Recommended Extensions (Priority Order):**

1. ✅ **pg\_trgm** - HIGH - Fast fuzzy text search (replaces slow ILIKE)
2. ✅ **unaccent** - HIGH - Accent-insensitive name searches
3. ✅ **btree\_gin** - HIGH - Composite indexes for JSONB + standard types
4. ⏳ **pg\_partman** - HIGH (Future) - Table partitioning for large audit logs
5. ⏳ **pg\_jsonschema** - MEDIUM - JSONB validation (if needed)

**Total New Extensions:** 3-5 (depending on future needs)

**Expected Impact:**

* **Search Performance:** 10-100x faster text searches
* **User Experience:** Better search results (accent-insensitive, fuzzy matching)
* **Database Performance:** Better index utilization, smaller query times

***

**Last Updated:** 2026-01-16\
**Status:** Ready for Implementation\
**Next Steps:** Create migration script for Phase 1 extensions