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

# Custom Fields Guide – Organization Extensibility

> Version: 1.0.0 Last Updated: 2025-12-31 Status: ✅ Complete

**Version:** 1.0.0\
**Last Updated:** 2025-12-31\
**Status:** ✅ Complete

This guide explains how to use `custom_fields` JSONB columns to extend Encore Health OS business entities with organization-specific metadata.

***

## 1. Purpose & Design Philosophy

### Why Custom Fields?

Organizations have diverse workflows that can't be predicted upfront:

* **Recovery housing orgs** may track custom resident phases, external case IDs, or funding sources
* **Healthcare providers** may need custom credential categories, shift codes, or billing codes
* **Multi-site operators** may have site-specific identifiers, reporting codes, or compliance flags

**Design Principle:** Rather than adding dozens of nullable columns or creating new tables for every edge case, `custom_fields` provides a type-safe, tenant-isolated extension point.

### What Custom Fields Are NOT

* **NOT a replacement for core schema design:** Frequently-queried fields should be proper columns
* **NOT a dumping ground:** Use for organization-specific business data, not technical config
* **NOT a security bypass:** `custom_fields` inherits table's RLS policies

***

## 2. When to Use Custom Fields

### Decision Tree

```
Is this data specific to ONE RECORD?
├─ Yes → Is it organization-specific business metadata?
│    ├─ Yes → Use `custom_fields` ✅
│    └─ No → Is it user preference (theme, notifications)?
│         ├─ Yes → Use `preferences` column (pf_profiles only)
│         └─ No → Core column or separate table
└─ No → Is this MODULE-WIDE configuration?
     └─ Yes → Use `{core}_module_settings` table
```

### Examples by Category

| Scenario                                 | Solution        | Storage                                                  |
| ---------------------------------------- | --------------- | -------------------------------------------------------- |
| Badge number for employee                | `custom_fields` | `hr_employees.custom_fields->>'badge_number'`            |
| External case ID for form submission     | `custom_fields` | `fw_form_submissions.custom_fields->>'external_case_id'` |
| Document retention policy (org-specific) | `custom_fields` | `pf_documents.custom_fields->>'retention_years'`         |
| User UI theme preference                 | `preferences`   | `pf_profiles.preferences->>'theme'`                      |
| Org default credential alert days        | `settings`      | `hr_module_settings.default_alert_days`                  |
| Frequently-queried resident status       | **Column**      | `rh_residents.phase` (not custom\_fields)                |

***

## 3. Implementation Patterns

### 3.1 Adding Custom Fields to New Tables

**Migration Template:**

```sql theme={null}
-- Create table with custom_fields
CREATE TABLE {core}_{entity} (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES pf_organizations(id),
  -- ... other columns ...
  custom_fields JSONB DEFAULT '{}' NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Add descriptive comment with 3-5 examples
COMMENT ON COLUMN {core}_{entity}.custom_fields IS 
'Organization-specific metadata. Examples:
- {"badge_number": "EMP-12345"} - Physical badge ID
- {"external_case_id": "CASE-2024-001"} - Integration with external system
- {"priority": "high", "sla_deadline": "2025-12-01"} - Workflow metadata
- {"review_date": "2026-01-15", "regulatory_category": "HIPAA"} - Compliance tracking
- {"shift_code": "NIGHT-A", "on_call_rotation": true} - Scheduling metadata';

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

CREATE POLICY "{entity}_org_access" ON {core}_{entity}
FOR SELECT USING (has_org_access(auth.uid(), organization_id));
```

### 3.2 Adding Custom Fields to Existing Tables

**Migration Template:**

```sql theme={null}
-- Add custom_fields to existing table
ALTER TABLE {core}_{entity} 
ADD COLUMN IF NOT EXISTS custom_fields JSONB DEFAULT '{}' NOT NULL;

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

### 3.3 Backend: Querying Custom Fields (TypeScript + Supabase)

**Basic Queries:**

```typescript theme={null}
import { supabase } from '@/integrations/supabase/client';

// 1. Select records with custom_fields
const { data, error } = await supabase
  .from('hr_employees')
  .select('id, full_name, custom_fields')
  .eq('organization_id', orgId);

// Access custom_fields in results
data?.forEach(employee => {
  const badgeNumber = employee.custom_fields?.badge_number;
  const shiftCode = employee.custom_fields?.shift_code;
});

// 2. Filter by custom field value
const { data } = await supabase
  .from('fw_form_submissions')
  .select('*')
  .eq('custom_fields->priority', 'high') // Query nested value
  .eq('organization_id', orgId);

// 3. Check if key exists
const { data } = await supabase
  .from('pf_documents')
  .select('*')
  .not('custom_fields', 'cs', '{"external_doc_id": null}') // Key exists
  .eq('organization_id', orgId);

// 4. Contains check (JSONB containment)
const { data } = await supabase
  .from('hr_onboarding_instances')
  .select('*')
  .contains('custom_fields', { special_accommodations: ['remote'] })
  .eq('organization_id', orgId);
```

**Update Custom Fields:**

```typescript theme={null}
// Merge new fields (Postgres JSONB || operator)
const { error } = await supabase
  .from('hr_employees')
  .update({
    custom_fields: {
      ...existingEmployee.custom_fields,
      badge_number: 'EMP-12345',
      building_access: ['A', 'B', 'C']
    }
  })
  .eq('id', employeeId);

// Or use Postgres JSONB functions directly
const { error } = await supabase.rpc('update_custom_field', {
  table_name: 'hr_employees',
  record_id: employeeId,
  field_path: 'badge_number',
  new_value: 'EMP-12345'
});
```

### 3.4 Frontend: Rendering Custom Fields (React)

**Type-Safe Custom Fields:**

```typescript theme={null}
// Define type for known custom fields (optional)
interface EmployeeCustomFields {
  badge_number?: string;
  building_access?: string[];
  shift_code?: string;
  emergency_contact_verified?: boolean;
}

// Use in components
const EmployeeDetail = ({ employee }: { employee: Employee }) => {
  const customFields = employee.custom_fields as EmployeeCustomFields;

  return (
    <div>
      <h2>{employee.full_name}</h2>
      {customFields?.badge_number && (
        <p>Badge: {customFields.badge_number}</p>
      )}
      {customFields?.building_access && (
        <p>Access: {customFields.building_access.join(', ')}</p>
      )}
    </div>
  );
};
```

**Generic Custom Fields Editor:**

```typescript theme={null}
import { useState } from 'react';
import { Input } from '@/shared/ui/input';
import { Button } from '@/shared/ui/button';

interface CustomFieldsEditorProps {
  value: Record<string, any>;
  onChange: (fields: Record<string, any>) => void;
}

const CustomFieldsEditor = ({ value, onChange }: CustomFieldsEditorProps) => {
  const [newKey, setNewKey] = useState('');
  const [newValue, setNewValue] = useState('');

  const addField = () => {
    if (!newKey) return;
    onChange({ ...value, [newKey]: newValue });
    setNewKey('');
    setNewValue('');
  };

  const removeField = (key: string) => {
    const { [key]: removed, ...rest } = value;
    onChange(rest);
  };

  return (
    <div className="space-y-4">
      <h3>Custom Fields</h3>
      
      {/* Existing fields */}
      {Object.entries(value || {}).map(([key, val]) => (
        <div key={key} className="flex gap-2">
          <Input value={key} disabled />
          <Input value={String(val)} disabled />
          <Button variant="destructive" onClick={() => removeField(key)}>
            Remove
          </Button>
        </div>
      ))}

      {/* Add new field */}
      <div className="flex gap-2">
        <Input
          placeholder="Field name"
          value={newKey}
          onChange={(e) => setNewKey(e.target.value)}
        />
        <Input
          placeholder="Field value"
          value={newValue}
          onChange={(e) => setNewValue(e.target.value)}
        />
        <Button onClick={addField}>Add</Button>
      </div>
    </div>
  );
};
```

***

## 4. Performance Considerations

### When to Add GIN Indexes

**Default:** No index required for occasional queries.

**Add GIN index if:**

* Frequently filtering by custom\_fields (e.g., dashboard queries)
* Complex JSONB queries (containment, existence checks)
* Query performance p95 > 500ms with EXPLAIN showing sequential scans

**Migration:**

```sql theme={null}
-- Add GIN index for JSONB queries
CREATE INDEX IF NOT EXISTS idx_{table}_custom_fields 
ON {table} USING GIN (custom_fields);

-- Specific path index (if always querying same nested key)
CREATE INDEX IF NOT EXISTS idx_{table}_custom_field_priority
ON {table} ((custom_fields->>'priority'));
```

**Performance Testing:**

```sql theme={null}
-- Test query performance
EXPLAIN ANALYZE
SELECT * FROM fw_form_submissions
WHERE custom_fields->>'priority' = 'high'
  AND organization_id = 'org-uuid';

-- Expected: Index Scan or Bitmap Index Scan (not Seq Scan)
```

***

## 5. Security & RLS

### Key Principle

**`custom_fields` inherits table's RLS policies.** No separate RLS required.

**Example:**

```sql theme={null}
-- Table has RLS for org isolation
CREATE POLICY "org_isolation" ON hr_employees
FOR SELECT USING (has_org_access(auth.uid(), organization_id));

-- custom_fields automatically inherits this policy
-- Users can only see custom_fields for their organization's employees
```

### What NOT to Store

**Prohibited in custom\_fields:**

* ❌ Passwords, API keys, secrets
* ❌ Social Security Numbers, full credit card numbers
* ❌ Unencrypted PHI (medical diagnoses, treatment notes)
* ❌ Data requiring separate access control from parent entity

**Allowed:**

* ✅ Badge numbers, employee IDs (non-sensitive identifiers)
* ✅ External system IDs (integration references)
* ✅ Workflow metadata (priority, status, dates)
* ✅ Business codes (cost centers, project codes, shift codes)

***

## 6. Testing Custom Fields

### Unit Tests

```typescript theme={null}
import { describe, it, expect } from 'vitest';

describe('Custom Fields', () => {
  it('should persist custom fields on employee', async () => {
    const employee = await createTestEmployee({
      custom_fields: { badge_number: 'TEST-001' }
    });
    
    expect(employee.custom_fields.badge_number).toBe('TEST-001');
  });

  it('should merge custom fields on update', async () => {
    const employee = await createTestEmployee({
      custom_fields: { badge_number: 'TEST-001' }
    });
    
    await updateEmployee(employee.id, {
      custom_fields: {
        ...employee.custom_fields,
        shift_code: 'NIGHT-A'
      }
    });
    
    const updated = await getEmployee(employee.id);
    expect(updated.custom_fields).toEqual({
      badge_number: 'TEST-001',
      shift_code: 'NIGHT-A'
    });
  });
});
```

### RLS Tests

```typescript theme={null}
import { describe, it, expect } from 'vitest';
import { signInTestUser } from '@/tests/utils/supabase-test-client';

describe('Custom Fields RLS', () => {
  it('should respect tenant isolation with custom_fields', async () => {
    // Create employees in different orgs with custom_fields
    const org1Employee = await createTestEmployee({
      organization_id: org1.id,
      custom_fields: { badge_number: 'ORG1-001' }
    });
    
    const org2Employee = await createTestEmployee({
      organization_id: org2.id,
      custom_fields: { badge_number: 'ORG2-001' }
    });
    
    // Sign in as org1 user
    const { client } = await signInTestUser(org1User.email, 'password');
    
    // Query employees
    const { data } = await client
      .from('hr_employees')
      .select('id, custom_fields');
    
    // Should only see org1 employee's custom_fields
    expect(data).toHaveLength(1);
    expect(data[0].custom_fields.badge_number).toBe('ORG1-001');
  });
});
```

***

## 7. Migration Checklist

When adding custom\_fields to a table:

* [ ] Add column: `ADD COLUMN IF NOT EXISTS custom_fields JSONB DEFAULT '{}' NOT NULL`
* [ ] Add comment with 3-5 example use cases
* [ ] Verify RLS policies cover custom\_fields (inherited automatically)
* [ ] Update TypeScript types if defining known custom fields
* [ ] Add integration tests for custom\_fields persistence
* [ ] Add RLS tests for custom\_fields tenant isolation
* [ ] Document in `specs/IMPLEMENTATION_LOG.md`
* [ ] Consider GIN index if frequently querying (defer until performance issue)

***

## 8. Common Patterns by Module

### Platform Foundation (PF)

**pf\_profiles:**

```json theme={null}
{
  "badge_number": "EMP-12345",
  "building_access": ["A", "B", "C"],
  "parking_space": "P-101",
  "emergency_contact_verified": true
}
```

**pf\_documents:**

```json theme={null}
{
  "classification": "Level 2",
  "retention_years": 7,
  "external_doc_id": "DOC-ABC-123",
  "regulatory_category": "HIPAA",
  "review_date": "2026-01-01"
}
```

### Forms & Workflow (FW)

**fw\_form\_submissions:**

```json theme={null}
{
  "approval_chain": ["mgr-uuid", "director-uuid"],
  "external_case_id": "CASE-2024-001",
  "workflow_state": "pending_director",
  "priority": "high",
  "sla_deadline": "2025-12-01"
}
```

**fw\_automation\_rules:**

```json theme={null}
{
  "owner_department": "IT",
  "business_critical": true,
  "external_webhook_id": "hook-123",
  "escalation_path": ["admin@org.com"],
  "monitoring_enabled": true
}
```

### Workforce & HR (HR)

**hr\_employees:**

```json theme={null}
{
  "badge_number": "EMP-12345",
  "building_access": ["A", "B"],
  "shift_code": "NIGHT-A",
  "payroll_id": "PAY-001",
  "external_hr_system_id": "EXT-HR-12345"
}
```

**hr\_onboarding\_instances:**

```json theme={null}
{
  "mentor_id": "emp-uuid-123",
  "buddy_id": "emp-uuid-456",
  "special_accommodations": ["remote"],
  "equipment_needed": ["laptop", "badge", "phone"],
  "workspace_assigned": "Floor 2, Desk 12"
}
```

**hr\_offboarding\_instances:**

```json theme={null}
{
  "exit_score": 8,
  "rehire_eligible": true,
  "knowledge_transfer_complete": true,
  "final_paycheck_date": "2025-12-15",
  "cobra_notified": true,
  "exit_interview_scheduled": "2025-12-10"
}
```

***

## 9. Troubleshooting

### Issue: Query Performance Slow

**Symptom:** Queries filtering by custom\_fields take >500ms

**Solution:**

1. Check EXPLAIN ANALYZE for sequential scans
2. Add GIN index if frequently querying:
   ```sql theme={null}
   CREATE INDEX idx_{table}_custom_fields 
   ON {table} USING GIN (custom_fields);
   ```
3. Consider promoting frequently-queried field to proper column

### Issue: Custom Fields Not Persisting

**Symptom:** Updates to custom\_fields don't save

**Debugging:**

1. Check RLS policies allow UPDATE on table
2. Verify column exists: `SELECT custom_fields FROM {table} LIMIT 1`
3. Check for TypeScript type errors in mutation

### Issue: RLS Blocking Custom Fields Access

**Symptom:** Can't read custom\_fields even though user should have access

**Debugging:**

1. Verify table has RLS policies
2. Test with service role key (bypasses RLS) to confirm data exists
3. Check RLS policies include necessary conditions
4. Remember: custom\_fields inherits table RLS, no separate policy needed

***

## 10. Future Enhancements

**Planned:**

* [ ] Custom fields UI builder for admins (per-organization field definitions)
* [ ] Validation rules for custom fields (e.g., badge\_number must match regex)
* [ ] Custom fields search across all tables
* [ ] Custom fields analytics/reporting
* [ ] Export custom fields schema per organization

**Not Planned:**

* ❌ Promoting all custom fields to columns (defeats extensibility purpose)
* ❌ Custom RLS per custom field (inherits table RLS)
* ❌ Cross-organization custom field sharing (violates tenant isolation)

***

**End of Custom Fields Guide**