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

# Database Schema Improvement Plan

> Created: 2026-02-25 Source: SCHEMA_REVIEW.md (validation run, gap list), constitution §5, .cursor/rules/database-patterns.mdc, .cursor/rules/database-migration…

**Created:** 2026-02-25\
**Source:** [SCHEMA\_REVIEW.md](https://github.com/Encore-OS/encoreos/blob/development/docs/database/SCHEMA_REVIEW.md) (validation run, gap list), constitution §5, [.cursor/rules/database-patterns.mdc](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-patterns.mdc), [.cursor/rules/database-migrations.md](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-migrations.md)

This document turns the schema review gap list into an actionable, prioritized improvement plan. Each item includes what to change, where, and how (migration approach). Implement in order of priority; Critical items must be addressed before production if not already fixed.

***

## Quick Reference

| Item                                   | Priority          | Owner / Status           | Link                                                                                                                                    |
| -------------------------------------- | ----------------- | ------------------------ | --------------------------------------------------------------------------------------------------------------------------------------- |
| UPDATE policies WITH CHECK             | Critical §1       | Migration + audit        | [§1](#1-update-policies-missing-with-check)                                                                                             |
| Tables missing RLS                     | Critical §2       | Migration + RLS tests    | [§2](#2-tables-missing-rls), [SCHEMA\_REVIEW.md](https://github.com/Encore-OS/encoreos/blob/development/docs/database/SCHEMA_REVIEW.md) |
| Recursive RLS                          | Critical §3       | SECURITY DEFINER helpers | [§3](#3-recursive-rls)                                                                                                                  |
| SECURITY DEFINER search\_path          | Critical §4       | Migration                | [§4](#4-security-definer-without-search_path)                                                                                           |
| custom\_fields, audit, naming, indexes | High/Medium §5–12 | Per table                | [High](#high-consistency-and-correctness), [Medium](#medium-performance-and-maintainability)                                            |

***

## Decision Tree

1. **Security/tenant isolation issue?** → Start with Critical items 1–4 (WITH CHECK, RLS, recursive RLS, search\_path).
2. **New business table?** → Ensure RLS, custom\_fields, audit columns, indexes per [database-patterns.mdc](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-patterns.mdc).
3. **Existing table missing RLS or WITH CHECK?** → Add migration per §1 or §2; use SECURITY DEFINER helpers, no direct subqueries to pf\_user\_roles/pf\_user\_role\_assignments.
4. **Naming or tooling?** → Follow constitution and [database-migrations.md](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-migrations.md); document exceptions if needed.

***

## Pattern Library

* **RLS policies:** Use `{core}_has_org_access(organization_id, auth.uid())` SECURITY DEFINER; every UPDATE policy needs WITH CHECK. See [database-patterns.mdc](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-patterns.mdc).
* **New migration:** `npx supabase migration new <name>`; idempotent DDL (IF NOT EXISTS); CONCURRENTLY for indexes on existing tables.
* **Verification:** `npx tsx scripts/database/validate-migration.ts --all --verbose` and [audit-rls-policies.sql](https://github.com/Encore-OS/encoreos/blob/development/scripts/database/audit-rls-policies.sql).

***

## Common Mistakes

| Mistake                                             | Impact                           | Mitigation                                                                      |
| --------------------------------------------------- | -------------------------------- | ------------------------------------------------------------------------------- |
| UPDATE policy without WITH CHECK                    | Tenant data can move across orgs | Add WITH CHECK (same expression as USING or false).                             |
| RLS disabled on business table                      | Tenant isolation broken          | Enable RLS and add policies per §2.                                             |
| Direct subquery to pf\_user\_roles in policy        | Recursive RLS / infinite loop    | Use SECURITY DEFINER helper only.                                               |
| CREATE INDEX without CONCURRENTLY on existing table | Table lock in production         | Use CONCURRENTLY in a separate migration.                                       |
| Migration file descriptive name only                | validate-migration naming errors | Prefer timestamp\_uuid; document exception in database-migrations.md if legacy. |

***

## Pre-Flight Checklist

* [ ] validate-migration --all run; errors addressed or documented.
* [ ] RLS enabled on all business tables; policies use SECURITY DEFINER helpers.
* [ ] Every UPDATE policy has WITH CHECK.
* [ ] SECURITY DEFINER functions have `SET search_path = public`.
* [ ] New migrations are idempotent; indexes on existing tables use CONCURRENTLY.
* [ ] [SCHEMA\_REVIEW.md](https://github.com/Encore-OS/encoreos/blob/development/docs/database/SCHEMA_REVIEW.md) and this plan updated when adding/changing tables.

***

## Critical (security / tenant isolation)

### 1. UPDATE policies missing WITH CHECK

**Why:** Without WITH CHECK, a user who can update a row can change `organization_id`, moving data between tenants (constitution §5.2.4).

**Affected (from validate-migration):**

* `hr_onboarding_tasks_audit` – policy `hr_onboarding_tasks_audit_update_disabled`\
  Migration: `supabase/migrations/20260211233828_46b5afce-470e-4992-afbe-eeb92032458e.sql`
* `fa_kpi_history` – policy `fa_kpi_history_no_update`\
  Migration: `supabase/migrations/20260213032225_7a90dc59-e49a-4924-af8e-3403c58620d0.sql`

**Action:** For each policy, add a new migration that:

1. `DROP POLICY IF EXISTS "policy_name" ON schema.table;`
2. `CREATE POLICY "policy_name" ON schema.table FOR UPDATE USING (<existing_using>) WITH CHECK (<same_expression>);`

If the policy intentionally disables update, use `WITH CHECK (false)` so that no row can be updated (prevents organization\_id change). Re-run `scripts/database/audit-rls-policies.sql` on the live DB to catch any further UPDATE policies missing WITH CHECK.

**Verification:** `npx tsx scripts/database/validate-migration.ts --all` shows 0 with-check errors; or run Query 1 in audit-rls-policies.sql.

***

### 2. Tables missing RLS

**Why:** RLS is required on all business tables for tenant isolation (constitution §5.1).

**Affected (from validate-migration):**

* **PF:** pf\_conversations, pf\_conversation\_members, pf\_messages, pf\_message\_read\_receipts; pf\_code\_sets, pf\_icd10\_codes, pf\_cpt\_codes, pf\_hcpcs\_codes, pf\_code\_modifiers, pf\_code\_crosswalks, pf\_payer\_code\_rules, pf\_code\_favorites, pf\_code\_recent
* **CL:** cl\_risk\_screenings, cl\_safety\_plans, cl\_environmental\_assessments

**Action:** For each table, add a new migration that:

1. `ALTER TABLE <table> ENABLE ROW LEVEL SECURITY;` (and `FORCE ROW LEVEL SECURITY` if desired)
2. CREATE POLICY for SELECT using `pf_has_org_access(organization_id, auth.uid())` or the correct core helper (e.g. `cl_has_org_access`)
3. CREATE POLICY for INSERT WITH CHECK (same expression)
4. CREATE POLICY for UPDATE with USING and WITH CHECK (same expression)
5. CREATE POLICY for DELETE using `{core}_is_org_admin(organization_id, auth.uid())` (or deny deletes if append-only)

For **pf\_code\_**\* and other reference tables that may not have `organization_id`, decide per spec: either add organization\_id and scope by tenant, or document as global and use a service-role–only or restrictive policy (e.g. SELECT for org users via a join path). Do not leave RLS disabled on tenant-scoped business data.

**Verification:** `npx tsx scripts/database/validate-migration.ts --all` shows 0 rls-enabled errors; RLS tests pass.

***

### 3. Recursive RLS

**Why:** Policies that query RLS-protected tables (e.g. pf\_user\_roles, pf\_user\_role\_assignments) cause infinite recursion.

**Action:** Run Query 3 in [scripts/database/audit-rls-policies.sql](https://github.com/Encore-OS/encoreos/blob/development/scripts/database/audit-rls-policies.sql) and any custom scan for `pf_user_roles` / `pf_user_role_assignments` in policy expressions. Replace with the appropriate `{core}_has_org_access(organization_id, auth.uid())` or `{core}_is_org_admin(organization_id, auth.uid())` SECURITY DEFINER helper. Add a migration per changed policy.

**Verification:** No recursion in RLS; RLS tests and manual checks pass.

***

### 4. SECURITY DEFINER without search\_path

**Why:** SECURITY DEFINER functions must `SET search_path = public` to avoid search\_path injection.

**Action:** Run validate-migration --all; for each reported function, add a new migration with `CREATE OR REPLACE FUNCTION ... SET search_path = public`. Fix all reported in the validation run.

**Verification:** validate-migration reports 0 search-path warnings for SECURITY DEFINER functions.

***

## High (consistency and correctness)

### 5. Missing custom\_fields on business entities

**Why:** Constitution §5.2.1 requires `custom_fields JSONB DEFAULT '{}' NOT NULL` on business entities (excluding junction/audit/config).

**Action:** From validate-migration --all --verbose info output, identify business entities missing custom\_fields. Add migrations: `ALTER TABLE <table> ADD COLUMN IF NOT EXISTS custom_fields JSONB DEFAULT '{}' NOT NULL;` and `COMMENT ON COLUMN <table>.custom_fields IS '...';` Exclude junction, audit/log, and config tables per .cursor/rules/database-patterns.mdc.

**Tables to consider (from validation info):** fa\_report\_favorites, pf\_data\_migration\_runs, pf\_bulk\_operation\_items, pf\_feature\_flags, pf\_feature\_flag\_usage, pf\_export\_files, pf\_conversation\_members, pf\_messages, pf\_message\_read\_receipts, and others listed in verbose output.

**Verification:** validate-migration info list shrinks; new business tables include custom\_fields by default.

***

### 6. Missing audit columns and updated\_at trigger

**Why:** Business tables should have created\_at, updated\_at and optionally created\_by, updated\_by; updated\_at should be maintained by trigger.

**Action:** For each table reported in validate-migration (updated-at-trigger warning), add a migration that creates the trigger using the project’s `update_updated_at_column()` (or equivalent) function. Ensure created\_at/updated\_at exist; add if missing.

**Notable tables (from validation):** fa\_collection\_queue, pf\_export\_templates, it\_change\_implementations, pf\_import\_batches, fa\_alert\_thresholds, pf\_patient\_identities, cl\_module\_settings, cl\_patient\_charts, cl\_problems, cl\_allergies, cl\_treatment\_plans, cl\_treatment\_goals, cl\_treatment\_interventions, cl\_progress\_notes, cl\_note\_templates, cl\_medications, cl\_medication\_reconciliations, cl\_pharmacies, cl\_prescriptions, pm\_patients, pm\_patient\_addresses, pm\_emergency\_contacts, pm\_guarantors, pm\_module\_settings, pm\_appointments, pm\_appointment\_reminders, cl\_inbasket\_items, and others from validation output.

**Verification:** validate-migration reports 0 updated-at-trigger warnings for tables with updated\_at.

***

### 7. Migration file naming consistency

**Why:** The constitution’s migration naming policy is authoritative; [database-migrations.md](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-migrations.md) should be aligned to it.

**Affected files (examples):** 20260211222508\_post\_squash\_dml.sql, 20260214120000\_it\_pf15\_picklists\_seed.sql, 20260214130000\_it\_realtime\_tickets.sql, 20260214140000\_it\_events\_phase\_a.sql, 20260214153000\_fix\_ce\_unmatched\_calls\_service\_role\_only.sql, 20260214154500\_fix\_gr\_has\_org\_access\_legacy\_arg\_order.sql, 20260214162000\_fix\_fa\_is\_finance\_admin\_permission\_key\_join.sql, 20260214170000\_fix\_pf\_can\_admin\_wizards\_permission\_function.sql, 20260216220000\_add\_pf\_import\_batches.sql, 20260216235000\_add\_fa\_plaid\_category\_mappings\_columns\_and\_seed.sql, 20260217140000\_plaid\_transfer\_ui\_origination\_config.sql, 20260217190000\_cl\_05\_medication\_permissions\_seed.sql, 20260217210000\_fa\_plaid\_transfer\_intents.sql, 20260218140000\_cl\_06\_site\_id\_and\_idempotent\_rls.sql, 20260225140000\_pf41\_system\_wizard\_templates\_and\_rpc.sql.

**Action:** Either: (a) Rename to `{timestamp}_{uuid}.sql` and update any references, or (b) Document the naming exception in database-migrations.md and ensure validate-migration naming rule allows these names. Prefer (a) for new work; (b) for legacy descriptive names that are costly to change.

**Verification:** validate-migration naming-convention errors reduced or documented.

***

### 8. Soft-delete partial indexes

**Why:** Tables with deleted\_at should have a partial index on (organization\_id) WHERE deleted\_at IS NULL for active-record queries (database-patterns.mdc).

**Affected (from validation):** pf\_messages, ce\_events, ce\_event\_attendees, pf\_import\_batches, cl\_treatment\_goals, cl\_treatment\_interventions, cl\_pharmacies, cl\_prescriptions, pm\_appointments.

**Action:** Add migrations: `CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_<table>_active ON <table>(organization_id) WHERE deleted_at IS NULL;` (use CONCURRENTLY for existing tables; separate migration per table or batch).

**Verification:** validate-migration reports 0 soft-delete-index warnings for tables with deleted\_at.

***

## Medium (performance and maintainability)

### 9. Index CONCURRENTLY for existing tables

**Why:** Creating indexes on tables that already exist without CONCURRENTLY can lock tables (database-migrations.md).

**Action:** Identify all CREATE INDEX statements in migrations where the table was not created in the same migration (validate-migration already warns). For each, add a new migration that creates the same index with `CREATE INDEX CONCURRENTLY ...`. Optionally drop the non-CONCURRENTLY index in a prior migration if it was added in a later file. Prefer one migration per index or a small batch to avoid long-running transactions.

**Verification:** validate-migration reports 0 index-concurrently warnings; production index creation does not cause long blocks.

***

### 10. Required indexes (org, site, FK)

**Why:** database-patterns.mdc requires idx\_{core}\_{entity}*org, idx*\*\_site where site\_id exists, and an index on every FK column.

**Action:** Cross-check schema (or live DB) against these rules. Add migrations for missing indexes. Use CONCURRENTLY for existing tables. Focus first on high-read and mutation-heavy tables (e.g. pm\_patients, cl\_patient\_charts, fw\_form\_submissions).

**Verification:** New tables have required indexes in the same migration; existing tables get indexes via CONCURRENTLY migrations.

***

### 11. Table and custom\_fields comments

**Why:** Table comment and COMMENT ON COLUMN ... custom\_fields are required (database-patterns.mdc).

**Action:** Add migrations that add COMMENT ON TABLE and COMMENT ON COLUMN for custom\_fields for tables that are missing them. Use brief descriptions and example use cases for custom\_fields.

**Verification:** Key business tables have table comment and custom\_fields comment.

***

### 12. GIN index on custom\_fields (where needed)

**Why:** If application or reporting queries filter or search on custom\_fields, a GIN index improves performance.

**Action:** Identify tables where custom\_fields is queried (e.g. `custom_fields ? 'key'`, `custom_fields @> '{"k": "v"}'`). Add `CREATE INDEX CONCURRENTLY idx_<table>_custom_fields ON <table> USING GIN (custom_fields);` in a dedicated migration. Do not add GIN everywhere by default.

**Verification:** Queries that filter on custom\_fields use the index where added.

***

## Low (technical debt and nice-to-have)

### 13. Idempotency and rollback

**Why:** Migrations should be re-runnable and have rollback notes where applicable (database-migrations.md).

**Action:** Spot-check recent migrations for CREATE without IF NOT EXISTS, ADD COLUMN without IF NOT EXISTS. Add idempotent variants in new migrations or document exceptions. Add rollback notes (e.g. DROP statements) in migration comments or a rollback doc.

**Verification:** New migrations are idempotent; rollback strategy is documented where needed.

***

### 14. Documentation and linking

**Why:** Schema docs should stay findable and up to date.

**Action:** Link to SCHEMA\_REVIEW\.md and this file from [AGENTS.md](https://github.com/Encore-OS/encoreos/blob/development/AGENTS.md) or docs/README (or equivalent). When adding or changing tables, update SCHEMA\_REVIEW\.md (overview, table counts) and run generate-migration-inventory.ts and validate-migration as part of the workflow.

**Verification:** Links from main docs to schema review and improvement plan; process documented.

***

### 15. Supabase schema architect agent

**Why:** The schema architect MCP agent could not be run (model error). Once fixed, it can validate and suggest further optimizations.

**Action:** When the MCP model is fixed, run the supabase-schema-architect agent on the codebase. Merge any additional recommendations (e.g. partitioning, partial indexes, query patterns) into this plan and SCHEMA\_REVIEW\.md.

**Verification:** Agent runs successfully; recommendations reviewed and incorporated.

***

## Implementation order

1. **Critical:** 1 → 2 → 3 → 4 (WITH CHECK, RLS on missing tables, recursive RLS, SECURITY DEFINER search\_path).
2. **High:** 5 → 6 → 8 (custom\_fields, audit/trigger, soft-delete indexes); then 7 (naming) as policy or rename.
3. **Medium:** 9 → 10 → 11 → 12 (CONCURRENTLY, required indexes, comments, GIN where needed).
4. **Low:** 13, 14, 15 as ongoing hygiene and when the agent is available.

***

## References

* [SCHEMA\_REVIEW.md](https://github.com/Encore-OS/encoreos/blob/development/docs/database/SCHEMA_REVIEW.md) – Schema overview and gap list
* [constitution.md](https://github.com/Encore-OS/encoreos/blob/development/constitution.md) §5 – Database and RLS requirements
* [.cursor/rules/database-patterns.mdc](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-patterns.mdc)
* [.cursor/rules/database-migrations.md](https://github.com/Encore-OS/encoreos/blob/development/.cursor/rules/database-migrations.md)
* [scripts/database/validate-migration.ts](https://github.com/Encore-OS/encoreos/blob/development/scripts/database/validate-migration.ts)
* [scripts/database/audit-rls-policies.sql](https://github.com/Encore-OS/encoreos/blob/development/scripts/database/audit-rls-policies.sql)
* [scripts/database/generate-migration-inventory.ts](https://github.com/Encore-OS/encoreos/blob/development/scripts/database/generate-migration-inventory.ts)