> ## 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 Development Guide

> Version: 1.2.0 Last Updated: 2026-04-24 Status: Stable Target Audience: Developers and AI agents (GitHub Copilot, Cursor, general AI assistants)

**Version:** 1.2.0\
**Last Updated:** 2026-04-24\
**Status:** Stable\
**Target Audience:** Developers and AI agents (GitHub Copilot, Cursor, general AI assistants)

Comprehensive guide for database development in the Encore Health OS Platform, covering RLS patterns, migrations, custom fields, multi-tenant queries, and debugging.

***

## AI Agent Context

**Key Patterns for AI:**

* Always include `organization_id` and `custom_fields JSONB DEFAULT '{}' NOT NULL` in business entity tables
* Always use `SECURITY DEFINER` functions for RLS policies to avoid recursion
* Always follow naming conventions: `snake_case` for tables/columns, `{core}_{action}_{entity}()` for functions
* Always ship schema changes as versioned `supabase/migrations/*.sql` — never manual SQL in production Dashboard
* Prefer **declarative DDL** under `supabase/schemas/` plus `supabase db diff` (see [DECLARATIVE\_SCHEMA\_GUIDE.md](supabase/DECLARATIVE_SCHEMA_GUIDE.md)); use hand-written migrations for documented diff caveats (DML, some policy/view/grant cases)
* Always test RLS policies with multi-tenant isolation scenarios

**Common Mistakes to Avoid:**

* Creating recursive RLS policies (querying RLS-protected tables in RLS policies)
* Forgetting `organization_id` in business tables (breaks multi-tenancy)
* Using CHECK constraints for time-based validations (use triggers or application logic)
* Creating schema changes outside migrations / declarative workflow (no orphan Dashboard DDL)
* Not including `custom_fields` JSONB column in business entities

***

## Quick Reference

| Task               | Pattern                                                                              | Example                                              |
| ------------------ | ------------------------------------------------------------------------------------ | ---------------------------------------------------- |
| Create table       | Include `organization_id`, `custom_fields`, RLS                                      | [Creating Tables](#creating-tables-with-rls)         |
| RLS policy         | Use `SECURITY DEFINER` functions                                                     | [RLS Patterns](#rls-policy-patterns)                 |
| Migration          | `{timestamp}_{snake_case_description}.sql` for new files (UUID-suffix grandfathered) | [Migrations](#migration-creation-and-testing)        |
| Custom fields      | `JSONB DEFAULT '{}'` with comment                                                    | [Custom Fields](#custom-fields-implementation)       |
| Multi-tenant query | Always filter by `organization_id`                                                   | [Multi-Tenant Queries](#multi-tenant-query-patterns) |

***

## Database Development Workflow

### 1. Plan the Schema

Before writing SQL, document:

* Table purpose and ownership (which core/module)
* Required columns (standard + module-specific)
* Relationships (foreign keys)
* RLS requirements
* Custom fields use cases

### 2. Author DDL (declarative) and generate a migration

**Preferred (DDL):**

1. Edit or add `.sql` under `supabase/schemas/` so it reflects the **desired final state** (see [DECLARATIVE\_SCHEMA\_GUIDE.md](supabase/DECLARATIVE_SCHEMA_GUIDE.md)).
2. Optional: `npm run db:schemas:lint` (`@supabase/pg-topo` ordering / parse diagnostics; skips if no `.sql` files yet).
3. Stop local Supabase, then generate a migration from drift:
   ```bash theme={null}
   npx supabase stop
   npx supabase db diff -f {core}_{entity}_{change}
   ```
   Shortcut: `npm run db:schemas:diff -- -f {core}_{entity}_{change}`
4. Review the new file under `supabase/migrations/` (watch for unintended drops).

**Legacy / caveat-only (DML, certain policy/view/grant cases):**

```bash theme={null}
npx supabase migration new {core}_{entity}_table
# Example
npx supabase migration new hr_employees_table
```

Follow [MIGRATION\_LANES.md](supabase/MIGRATION_LANES.md) for `schema` vs `system-defaults` lanes.

### 3. Write or verify SQL

Follow naming conventions and include:

* Table creation with standard columns
* RLS enablement
* RLS policies using `SECURITY DEFINER` functions
* Indexes for performance
* Comments for documentation

### 4. Test Locally

```bash theme={null}
# Reset local database
supabase db reset

# Run migrations
supabase migration up

# Verify in Supabase Studio
# Check RLS policies work correctly
```

### 5. Generate TypeScript Types

```bash theme={null}
# Generate types from schema
npx supabase gen types typescript --project-id {project-ref} > src/integrations/supabase/types.ts
```

### 6. Update Code

* Create TypeScript types/interfaces
* Implement queries with proper RLS
* Add tests for RLS policies
* Document in implementation log

***

## Creating Tables with RLS

### Standard Table Structure

**All business entity tables MUST include:**

```sql theme={null}
CREATE TABLE {core}_{entity} (
  -- Primary key
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Multi-tenant context (REQUIRED)
  organization_id UUID NOT NULL REFERENCES pf_organizations(id) ON DELETE CASCADE,
  
  -- Site context (where applicable)
  site_id UUID REFERENCES pf_sites(id),
  
  -- Custom fields (REQUIRED for business entities)
  custom_fields JSONB DEFAULT '{}' NOT NULL,
  
  -- Audit columns
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id),
  
  -- Module-specific columns
  -- ... your columns here ...
);

-- Enable RLS (REQUIRED)
ALTER TABLE {core}_{entity} ENABLE ROW LEVEL SECURITY;

-- Add comment for custom_fields
COMMENT ON COLUMN {core}_{entity}.custom_fields IS 
  'Organization-specific metadata (e.g., {"badge_number": "12345", "external_case_id": "CASE-001"})';

-- Updated_at trigger (REQUIRED - use shared function)
CREATE TRIGGER set_{core}_{entity}_updated_at
    BEFORE UPDATE ON {core}_{entity}
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
```

### When to Include custom\_fields

**✅ Include for:**

* Business entities (employees, residents, forms, transactions)
* User-facing data that may need org-specific metadata

**❌ Skip for:**

* Junction/mapping tables
* Audit/log tables
* System/configuration tables

### Example: Employee Table

```sql theme={null}
CREATE TABLE hr_employees (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES pf_organizations(id) ON DELETE CASCADE,
  site_id UUID REFERENCES pf_sites(id),
  custom_fields JSONB DEFAULT '{}' NOT NULL,
  
  -- Employee-specific columns
  full_name TEXT NOT NULL,
  email TEXT,
  employee_number TEXT,
  hire_date DATE,
  department_id UUID REFERENCES pf_departments(id),
  level_id UUID REFERENCES pf_levels(id),
  
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id)
);

ALTER TABLE hr_employees ENABLE ROW LEVEL SECURITY;

COMMENT ON COLUMN hr_employees.custom_fields IS 
  'Organization-specific metadata (e.g., {"badge_number": "12345", "external_case_id": "CASE-001", "union_member": true})';

-- Updated_at trigger (REQUIRED)
CREATE TRIGGER set_hr_employees_updated_at
    BEFORE UPDATE ON hr_employees
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
```

***

## RLS Policy Patterns

### Critical Rule: Use SECURITY DEFINER Functions

**❌ NEVER query RLS-protected tables directly in policies (causes infinite recursion):**

```sql theme={null}
-- BAD: pf_user_role_assignments has RLS too! (Note: pf_user_roles is DROPPED — use pf_user_role_assignments)
CREATE POLICY "access" ON my_table FOR SELECT
USING (organization_id IN (
  SELECT organization_id FROM pf_user_role_assignments WHERE user_id = auth.uid()
));
```

**✅ ALWAYS use SECURITY DEFINER helper functions:**

```sql theme={null}
-- Create helper function first
CREATE OR REPLACE FUNCTION {core}_has_org_access(org_id UUID, user_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_user_role_assignments
    WHERE pf_user_role_assignments.organization_id = org_id
      AND pf_user_role_assignments.user_id = user_id
  );
END;
$$;

-- Then use in policy
CREATE POLICY "{core}_{entity}_select" ON {core}_{entity}
FOR SELECT
USING ({core}_has_org_access(organization_id, auth.uid()));
```

### Standard RLS Policies

#### SELECT Policy (View)

```sql theme={null}
CREATE POLICY "{core}_{entity}_select" ON {core}_{entity}
FOR SELECT
USING ({core}_has_org_access(organization_id, auth.uid()));
```

#### INSERT Policy (Create)

```sql theme={null}
CREATE POLICY "{core}_{entity}_insert" ON {core}_{entity}
FOR INSERT
WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));
```

#### UPDATE Policy (CRITICAL: Must Include WITH CHECK)

```sql theme={null}
CREATE POLICY "{core}_{entity}_update" ON {core}_{entity}
FOR UPDATE
USING ({core}_has_org_access(organization_id, auth.uid()))
WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));
```

**Why WITH CHECK is Critical:**
Without `WITH CHECK`, a user can change `organization_id` to move data between tenants, breaking multi-tenant isolation.

#### DELETE Policy

```sql theme={null}
CREATE POLICY "{core}_{entity}_delete" ON {core}_{entity}
FOR DELETE
USING ({core}_has_org_access(organization_id, auth.uid()));
```

### Creator-Only Policies

For entities where only the creator can modify:

```sql theme={null}
-- Creator can update
CREATE POLICY "{core}_{entity}_update_creator" ON {core}_{entity}
FOR UPDATE
USING (created_by = auth.uid())
WITH CHECK (
  created_by = auth.uid()
  AND {core}_has_org_access(organization_id, auth.uid())
);

-- Creator can delete
CREATE POLICY "{core}_{entity}_delete_creator" ON {core}_{entity}
FOR DELETE
USING (
  created_by = auth.uid()
  AND {core}_has_org_access(organization_id, auth.uid())
);
```

### Role-Based Policies

For policies that require specific roles:

```sql theme={null}
-- ⚠️ DEPRECATED PATTERN — pf_user_roles is DROPPED. Use SECURITY DEFINER helpers instead.
-- Org admins can manage (correct pattern):
CREATE POLICY "{core}_{entity}_admin" ON {core}_{entity}
FOR ALL
USING ({core}_is_org_admin(organization_id, auth.uid()))
WITH CHECK ({core}_is_org_admin(organization_id, auth.uid()));
```

***

## Migration Creation and Testing

### Migration Naming

**Format (new migrations):** `{timestamp}_{snake_case_description}.sql` — e.g. `20260428000143_pf15_picklist_defaults_data_driven.sql`. The Lovable-generated `{timestamp}_{uuid}.sql` pattern is grandfathered for existing files but blocked for new ones by `scripts/database/lint-migration-filename.ts` in pre-commit.

```bash theme={null}
# Generated automatically by Supabase CLI
supabase migration new {core}_{entity}_table

# Example output:
# supabase/migrations/20251123140051_8ae69786-af17-4237-9157-b98f05f45ad7.sql
```

### Migration Requirements

1. **Monotonic and Ordered:** Migrations run in timestamp order
2. **Re-runnable:** Can run multiple times safely (use `IF NOT EXISTS`)
3. **Backward-Compatible:** When possible, avoid breaking changes

### Migration Template

```sql theme={null}
-- Migration: {core}_{entity}_table
-- Description: Create {entity} table for {core} module
-- Author: {your-name}
-- Date: {date}

-- Create table
CREATE TABLE IF NOT EXISTS {core}_{entity} (
  -- ... table definition ...
);

-- Enable RLS
ALTER TABLE {core}_{entity} ENABLE ROW LEVEL SECURITY;

-- Create helper function (if needed)
CREATE OR REPLACE FUNCTION {core}_has_org_access(org_id UUID, user_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_user_role_assignments
    WHERE pf_user_role_assignments.organization_id = org_id
      AND pf_user_role_assignments.user_id = user_id
  );
END;
$$;

-- Create RLS policies
CREATE POLICY "{core}_{entity}_select" ON {core}_{entity}
FOR SELECT
USING ({core}_has_org_access(organization_id, auth.uid()));

-- ... other policies ...

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_{core}_{entity}_org_id 
  ON {core}_{entity}(organization_id);

-- Add comments
COMMENT ON TABLE {core}_{entity} IS '{description}';
COMMENT ON COLUMN {core}_{entity}.custom_fields IS '{examples}';
```

### Testing Migrations

```bash theme={null}
# Reset local database
supabase db reset

# This will:
# 1. Drop all tables
# 2. Run all migrations in order
# 3. Apply seed data (if any)

# Verify in Supabase Studio
# - Check table exists
# - Check RLS is enabled
# - Test policies with different users
```

### Migration Best Practices

1. **One logical change per migration** - Easier to review and rollback
2. **Use IF NOT EXISTS** - Makes migrations re-runnable
3. **Add comments** - Document purpose and changes
4. **Test locally first** - Always test before committing
5. **Review RLS policies** - Ensure tenant isolation

***

## Custom Fields Implementation

### When to Use Custom Fields

**Use `custom_fields JSONB` for:**

* Organization-specific metadata on records
* Fields that vary by organization
* Extensibility without schema changes

**Examples:**

* Employee badge numbers
* External system IDs
* Custom flags or preferences
* Organization-specific classifications

### Custom Fields Pattern

```sql theme={null}
-- Add to table
custom_fields JSONB DEFAULT '{}' NOT NULL,

-- Add comment with examples
COMMENT ON COLUMN {table_name}.custom_fields IS 
  'Organization-specific metadata (e.g., {"badge_number": "12345", "external_case_id": "CASE-001", "union_member": true})';
```

### Querying Custom Fields

```typescript theme={null}
// Query with custom field filter
const { data } = await supabase
  .from('hr_employees')
  .select('*')
  .eq('organization_id', orgId)
  .filter('custom_fields->>badge_number', 'eq', '12345');

// Access custom field in code
const badgeNumber = employee.custom_fields?.badge_number;
const isUnionMember = employee.custom_fields?.union_member === true;
```

### Custom Fields Indexing

**For frequently queried custom fields, add GIN index:**

```sql theme={null}
CREATE INDEX idx_{table}_custom_fields 
ON {table} USING GIN (custom_fields);
```

**When to add index:**

* Custom field is queried frequently
* Organization has many records
* Query performance is slow

***

## Multi-Tenant Query Patterns

### Always Filter by organization\_id

#### ✅ CORRECT: Always include organization\_id filter

```typescript theme={null}
// Query with organization context
const { data } = await supabase
  .from('hr_employees')
  .select('*')
  .eq('organization_id', currentOrganization.id);  // ✅ Required
```

#### ❌ WRONG: Missing organization\_id filter

```typescript theme={null}
// This will fail RLS or return wrong data
const { data } = await supabase
  .from('hr_employees')
  .select('*');  // ❌ Missing organization_id!
```

### Getting Current Organization

```typescript theme={null}
import { useCurrentUser } from '@/platform/auth';
import { useOrganization } from '@/platform/organizations/OrganizationContext';

function MyComponent() {
  const { user } = useCurrentUser();
  const { currentOrganization } = useOrganization();
  
  // Use currentOrganization.id for queries
  const { data } = await supabase
    .from('hr_employees')
    .select('*')
    .eq('organization_id', currentOrganization.id);
}
```

### Multi-Organization Queries

**For users with access to multiple organizations:**

```typescript theme={null}
// Get user's organizations (Note: pf_user_roles is DROPPED — use pf_user_role_assignments)
const { data: userRoles } = await supabase
  .from('pf_user_role_assignments')
  .select('organization_id')
  .eq('user_id', user.id);

const orgIds = userRoles?.map(r => r.organization_id) || [];

// Query across organizations (if needed)
const { data } = await supabase
  .from('hr_employees')
  .select('*')
  .in('organization_id', orgIds);
```

**Note:** RLS policies will automatically filter to organizations the user has access to.

***

## Database Debugging

### Check RLS Policies

```sql theme={null}
-- List all policies on a table
SELECT * FROM pg_policies 
WHERE tablename = '{core}_{entity}';

-- Check if RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE tablename = '{core}_{entity}';
```

### Test RLS Policies

```sql theme={null}
-- Test as specific user
SET ROLE authenticated;
SET request.jwt.claim.sub = '{user-id}';

-- Try query
SELECT * FROM {core}_{entity};

-- Reset
RESET ROLE;
```

### Debug Query Performance

```sql theme={null}
-- Enable query logging
SET log_statement = 'all';
SET log_duration = on;

-- Run query and check logs
SELECT * FROM {core}_{entity} WHERE organization_id = '{org-id}';
```

### Common RLS Issues

#### Issue: "Infinite recursion" error

**Cause:** Querying RLS-protected table in policy

**Solution:** Use `SECURITY DEFINER` function

#### Issue: "No rows returned" when rows exist

**Cause:** RLS policy too restrictive or missing

**Solution:** Check policy conditions, verify user has org access

#### Issue: "Cross-organization data leakage"

**Cause:** Missing `WITH CHECK` in UPDATE policy

**Solution:** Add `WITH CHECK` clause to UPDATE policies

***

## Constitution playbook: PF DDL templates

Canonical SQL and JSON examples for **module settings**, **picklists (PF-15)**, and **custom field definitions (PF-16)**. The [constitution](../../constitution.md) §5.2 states the **MUST** rules; this section is the extended implementation reference. UI patterns for settings pages: [settings-pattern-guide.md](./settings-pattern-guide.md).

### Module settings table (`{core}_module_settings`)

```sql theme={null}
CREATE TABLE {core}_module_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL UNIQUE REFERENCES pf_organizations(id) ON DELETE CASCADE,

  -- Configuration columns (module-specific)
  -- Examples:
  -- default_status TEXT DEFAULT 'draft',
  -- retention_days INTEGER DEFAULT 365,
  -- max_upload_size_mb INTEGER DEFAULT 25,
  -- custom_categories TEXT[] DEFAULT ARRAY[]::TEXT[],

  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id)
);

ALTER TABLE {core}_module_settings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their org settings"
  ON {core}_module_settings FOR SELECT
  USING (pf_has_org_access(organization_id, auth.uid()));

CREATE POLICY "Org admins can insert settings"
  ON {core}_module_settings FOR INSERT
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));

CREATE POLICY "Org admins can update settings"
  ON {core}_module_settings FOR UPDATE
  USING (pf_is_org_admin(organization_id, auth.uid()))
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));

CREATE TRIGGER set_updated_at BEFORE UPDATE ON {core}_module_settings
  FOR EACH ROW EXECUTE FUNCTION pf_set_updated_at();
```

### Picklists: `pf_picklists` and `pf_picklist_items`

```sql theme={null}
CREATE TABLE pf_picklists (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES pf_organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT,
  is_system BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  version INTEGER DEFAULT 1,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id),
  UNIQUE(organization_id, name)
);

ALTER TABLE pf_picklists ENABLE ROW LEVEL SECURITY;

CREATE TABLE pf_picklist_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  picklist_id UUID NOT NULL REFERENCES pf_picklists(id) ON DELETE CASCADE,
  value TEXT NOT NULL,
  label TEXT NOT NULL,
  display_order INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  UNIQUE(picklist_id, value)
);

ALTER TABLE pf_picklist_items ENABLE ROW LEVEL SECURITY;

CREATE INDEX idx_picklists_org_category ON pf_picklists(organization_id, category);
CREATE INDEX idx_picklist_items_picklist ON pf_picklist_items(picklist_id, display_order);
```

Example RLS (use current `pf_has_org_access` / `pf_is_org_admin` from your schema):

```sql theme={null}
CREATE POLICY "Users can view org picklists"
  ON pf_picklists FOR SELECT
  USING (pf_has_org_access(organization_id, auth.uid()) OR is_system = true);

CREATE POLICY "Admins can manage picklists"
  ON pf_picklists FOR ALL
  USING (pf_is_org_admin(organization_id, auth.uid()))
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));

CREATE POLICY "Users can view org picklist items"
  ON pf_picklist_items FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM pf_picklists
      WHERE pf_picklists.id = pf_picklist_items.picklist_id
      AND pf_has_org_access(pf_picklists.organization_id, auth.uid())
    )
  );

CREATE POLICY "Admins can manage picklist items"
  ON pf_picklist_items FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM pf_picklists
      WHERE pf_picklists.id = pf_picklist_items.picklist_id
      AND pf_is_org_admin(pf_picklists.organization_id, auth.uid())
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM pf_picklists
      WHERE pf_picklists.id = pf_picklist_items.picklist_id
      AND pf_is_org_admin(pf_picklists.organization_id, auth.uid())
    )
  );
```

### Custom field definitions: `pf_custom_field_definitions`

```sql theme={null}
CREATE TABLE pf_custom_field_definitions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES pf_organizations(id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL,
  field_key TEXT NOT NULL,
  field_label TEXT NOT NULL,
  field_type TEXT NOT NULL,
  is_required BOOLEAN DEFAULT false,
  default_value JSONB,
  validation_rules JSONB DEFAULT '{}',
  display_order INTEGER DEFAULT 0,
  field_group TEXT,
  is_active BOOLEAN DEFAULT true,
  picklist_id UUID REFERENCES pf_picklists(id),
  help_text TEXT,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  created_by UUID REFERENCES pf_profiles(id),
  updated_by UUID REFERENCES pf_profiles(id),
  UNIQUE(organization_id, entity_type, field_key)
);

ALTER TABLE pf_custom_field_definitions ENABLE ROW LEVEL SECURITY;

CREATE INDEX idx_custom_field_defs_entity ON pf_custom_field_definitions(organization_id, entity_type, is_active);

CREATE POLICY "Users can view org field definitions"
  ON pf_custom_field_definitions FOR SELECT
  USING (pf_has_org_access(organization_id, auth.uid()));

CREATE POLICY "Admins can manage field definitions"
  ON pf_custom_field_definitions FOR ALL
  USING (pf_is_org_admin(organization_id, auth.uid()))
  WITH CHECK (pf_is_org_admin(organization_id, auth.uid()));
```

### Example `validation_rules` JSON

```json theme={null}
{
  "text": {
    "minLength": 2,
    "maxLength": 100,
    "pattern": "^[A-Z0-9-]+$"
  },
  "number": {
    "min": 0,
    "max": 999999,
    "decimals": 2
  },
  "date": {
    "minDate": "2020-01-01",
    "maxDate": "2030-12-31"
  },
  "select": {
    "allowOther": true
  }
}
```

***

## Related Documentation

### Core Standards

* [Constitution](../../constitution.md) §5 - Database rules and RLS requirements
* [AGENTS.md](../../AGENTS.md) - Database naming conventions and patterns

### Development Guides

* [Settings pattern guide](./settings-pattern-guide.md) - Module settings pages (UI; pairs with Constitution playbook DDL)
* [Troubleshooting Guide](./TROUBLESHOOTING_GUIDE.md) - Database connection and query issues
* [Testing Setup and Run Guide](../testing/TESTING_SETUP_AND_RUN.md) - RLS testing patterns and execution

### Architecture

* [Custom Fields Guide](../architecture/patterns/CUSTOM_FIELDS_GUIDE.md) - Detailed custom fields patterns
* [Constitution](../../constitution.md) - Database development guardrails and migration patterns

***

**Maintained By:** Platform Foundation Team