Custom Fields Guide – Organization Extensibility

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:

-- 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:

-- 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:

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:

// 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:

// 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:

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:

-- 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:

-- 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:

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

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

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:

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

pf_documents:

{
  "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:

{
  "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:

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

Workforce & HR (HR)

hr_employees:

{
  "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:

{
  "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:

{
  "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:

Check EXPLAIN ANALYZE for sequential scans

Add GIN index if frequently querying:

CREATE INDEX idx_{table}_custom_fields 
ON {table} USING GIN (custom_fields);

Consider promoting frequently-queried field to proper column

Issue: Custom Fields Not Persisting

Symptom: Updates to custom_fields don’t save Debugging:

Check RLS policies allow UPDATE on table
Verify column exists: SELECT custom_fields FROM {table} LIMIT 1
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:

Verify table has RLS policies
Test with service role key (bypasses RLS) to confirm data exists
Check RLS policies include necessary conditions
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

Architecture

Development

Database

Testing

Migration

Operations

Governance & Security

Platform Internals

Custom Fields Guide – Organization Extensibility

1. Purpose & Design Philosophy

Why Custom Fields?

What Custom Fields Are NOT

2. When to Use Custom Fields

Decision Tree

Examples by Category

3. Implementation Patterns

3.1 Adding Custom Fields to New Tables

3.2 Adding Custom Fields to Existing Tables

3.3 Backend: Querying Custom Fields (TypeScript + Supabase)

3.4 Frontend: Rendering Custom Fields (React)

4. Performance Considerations

When to Add GIN Indexes

5. Security & RLS

Key Principle

What NOT to Store

6. Testing Custom Fields

Unit Tests

RLS Tests

7. Migration Checklist

8. Common Patterns by Module

Platform Foundation (PF)

Forms & Workflow (FW)

Workforce & HR (HR)

9. Troubleshooting

Issue: Query Performance Slow

Issue: Custom Fields Not Persisting

Issue: RLS Blocking Custom Fields Access

10. Future Enhancements

Architecture

Development

Database

Testing

Migration

Operations

Governance & Security

Platform Internals

Documentation Index

​1. Purpose & Design Philosophy

​Why Custom Fields?

​What Custom Fields Are NOT

​2. When to Use Custom Fields

​Decision Tree

​Examples by Category

​3. Implementation Patterns

​3.1 Adding Custom Fields to New Tables

​3.2 Adding Custom Fields to Existing Tables

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

​3.4 Frontend: Rendering Custom Fields (React)

​4. Performance Considerations

​When to Add GIN Indexes

​5. Security & RLS

​Key Principle

​What NOT to Store

​6. Testing Custom Fields

​Unit Tests

​RLS Tests

​7. Migration Checklist

​8. Common Patterns by Module

​Platform Foundation (PF)

​Forms & Workflow (FW)

​Workforce & HR (HR)

​9. Troubleshooting

​Issue: Query Performance Slow

​Issue: Custom Fields Not Persisting

​Issue: RLS Blocking Custom Fields Access

​10. Future Enhancements

1. Purpose & Design Philosophy

Why Custom Fields?

What Custom Fields Are NOT

2. When to Use Custom Fields

Decision Tree

Examples by Category

3. Implementation Patterns

3.1 Adding Custom Fields to New Tables

3.2 Adding Custom Fields to Existing Tables

3.3 Backend: Querying Custom Fields (TypeScript + Supabase)

3.4 Frontend: Rendering Custom Fields (React)

4. Performance Considerations

When to Add GIN Indexes

5. Security & RLS

Key Principle

What NOT to Store

6. Testing Custom Fields

Unit Tests

RLS Tests

7. Migration Checklist

8. Common Patterns by Module

Platform Foundation (PF)

Forms & Workflow (FW)

Workforce & HR (HR)

9. Troubleshooting

Issue: Query Performance Slow

Issue: Custom Fields Not Persisting

Issue: RLS Blocking Custom Fields Access

10. Future Enhancements