Custom Fields Guide – Organization Extensibility

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

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

-- 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"})';

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

// 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'
});

// 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>
  );
};

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>
  );
};

-- 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'));

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

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

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'
    });
  });
});

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');
  });
});

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

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

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

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

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

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

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