Database Schema Improvement Plan

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.

Created: 2026-02-25
Source: SCHEMA_REVIEW.md (validation run, gap list), constitution §5, .cursor/rules/database-patterns.mdc, .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

---

​

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.
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; 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.
• New migration: npx supabase migration new <name>; idempotent DDL (IF NOT EXISTS); CONCURRENTLY for indexes on existing tables.
• Verification: npx ts-node scripts/database/validate-migration.ts --all --verbose and audit-rls-policies.sql.

---

​

Common Mistakes

---

​

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 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 ts-node 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 ts-node 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 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 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__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 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 – Schema overview and gap list
• constitution.md §5 – Database and RLS requirements
• .cursor/rules/database-patterns.mdc
• .cursor/rules/database-migrations.md
• scripts/database/validate-migration.ts
• scripts/database/audit-rls-policies.sql
• scripts/database/generate-migration-inventory.ts