RFC: Hybrid Custom Fields on Core Tables

Authors: Prathik Shetty(@pshettydev)
Date: 2025-11-21
Status: Accepted

Abstract

This RFC proposes a hybrid approach for enabling dynamic, tenant-defined custom fields on core ATS tables (companies, positions, contacts, candidates, etc.) by combining JSONB storage with the existing Schema-Driven Table Framework (SDTF).

The system leverages:

JSONB columns for storing custom field values on each core table
SDTF schema for managing custom field definitions, UI configuration, and validation rules
Application-layer validation with optional database-level type enforcement via generated columns

Motivation

Business Requirements

Flexibility: Tenants need to define custom fields specific to their business processes without waiting for engineering deployments (e.g., "Customer Tier", "Deal Size", "Priority Score")
Performance: Custom field operations must not degrade core table query performance
User Experience: Custom fields should appear seamlessly alongside native fields in the UI
Type Safety: Custom fields must support various data types (text, number, date, select, relations) with proper validation

Technical Context

Implementing the entire dynamic tables without defining any core tables in the DB (as per our previous analysis in The EAV Approach and JSONB Approach) had many benefits, however, it came with its own share of drawbacks. Which included:

Complexity: Managing custom fields across multiple tables and ensuring consistency can be challenging
Migration: Migrating the entire backend as per the new schema and also migrating the data from the present DB to the new schema can be error-prone
Time Constraint: The most important issue of all, given the timeline, it is extremely difficult to ensure that the implementation is completed within the specified timeframe.

Core Architecture

Why Not Pure Dynamic Tables?

While we have dynamic table capabilities, core tables (companies, positions, contacts) have:

Complex existing relationships and foreign key constraints
Heavy indexing and query optimization requirements
Critical business logic and validation rules
Integration with external systems expecting stable schemas (like migration with Airtable)

A hybrid approach preserves these advantages while adding customization capabilities.

Approach

JSONB Column Per Core Table

Add a custom_fields JSONB column to each core table that supports customization.

Schema Example:

model Company {
  // ... existing native fields
  name    String
  website String
  status  String
 
  // Custom fields storage
  custom_fields Json? @db.JsonB  // { "field_<uuid>": value }
 
  // ... rest of model
}

Field Structure:

{
  "field_abc123": "Premium",
  "field_def456": 42,
  "field_xyz789": "2024-12-20",
  "field_rel001": "uuid-of-related-record"
}

Strengths:

✅ Performance: Single-row fetch, no joins required
✅ Indexing: GIN indexes enable fast JSON path queries
✅ Sparse Storage: Null if no custom fields defined
✅ Postgres-Native: Leverages JSONB operators and functions
✅ Simple Queries: Standard WHERE clauses with JSONB operators

Weaknesses:

❌ Schema Migration: One-time migration needed per core table
❌ Size Limits: JSONB practical limit (~100MB per record)
❌ Type Enforcement: Requires application-layer validation

However, these weaknesses have already been mitigated or can be managed. For example, type enforcement is already handled at the application layer through @pnats/core, which utilizes Zod schemas for validation.

Query Examples:

-- Filter by custom field
SELECT * FROM companies
WHERE custom_fields->>'tier' = 'Premium';
 
-- Sort by numeric custom field
SELECT * FROM companies
ORDER BY (custom_fields->>'score')::numeric DESC;
 
-- Index for performance
CREATE INDEX idx_companies_custom_gin
ON companies USING gin(custom_fields jsonb_path_ops);

Proposal

Adopt JSONB-backed custom fields as the primary implementation with a clear path to promote high-traffic fields to generated columns based on production profiling.

When a user creates a new custom field for a core table, the field definition is registered in the SDTF schema layer, which serves as the single source of truth for all field metadata.

However, the system employs a lazy population strategy: the custom field is not automatically initialized in the custom_fields JSONB column of existing records. Instead, custom field values are only written to a record when that record is explicitly updated or when a value is assigned to the new field.

Benefits of this approach:

Storage efficiency: Avoids bloating existing records with null/empty fields
Performance: Eliminates the need for expensive bulk updates when fields are added
Sparse data handling: Records naturally contain only the custom fields that have been populated

Implementation Strategy

Core Schema Changes

Add custom_fields JSONB column to all core tables that require customization:

model Company {
  // ... existing fields
 
  // Custom fields storage
  custom_fields Json? @db.JsonB
 
  // ... rest of model
 
  @@index([custom_fields], type: Gin)
}
 
model Position {
  // ... existing fields
 
  custom_fields Json? @db.JsonB
 
  // ... rest of model
 
  @@index([custom_fields], type: Gin)
}
 
model Contact {
  // ... existing fields
 
  custom_fields Json? @db.JsonB
 
  // ... rest of model
 
  @@index([custom_fields], type: Gin)
}
 
// Repeat for other core tables: Candidate, Opportunity, etc.

Tables to include:

✅ Company
✅ Position
✅ Contact
✅ Candidate
✅ CandidateSubmission
✅ Opportunity
✅ Interview
✅ Agreement
✅ Lead (optional)

Tables to exclude (system tables):

❌ User
❌ Tenant
❌ Permission
❌ Role
❌ AuditLog

SDTF Schema Extension

Extend TableSchema to support core table customization:

model TableSchema {
  // ... existing fields
 
  table_name        String  // e.g., "Companies"
  db_table_name     String  // e.g., "companies"
  is_core_table     Boolean @default(false)  // NEW: Flag for core vs dynamic
 
  // ... rest of model
}
 
model TableField {
  // ... existing fields
 
  is_custom_field   Boolean @default(false)  // NEW: Distinguish custom from native
 
  // ... rest of model
}

Schema Creation Pattern:

For each core table, create a corresponding TableSchema entry that includes:

All native fields (marked with is_custom_field = false)
Custom fields defined by tenant (marked with is_custom_field = true)

Example SDTF schema for companies with custom fields:

{
  "table_name": "Companies",
  "db_table_name": "companies",
  "is_core_table": true,
  "fields": [
    {
      "field_id": "native_name",
      "display_name": "Company Name",
      "db_field_name": "name",
      "type": "SHORT_TEXT",
      "is_custom_field": false
    },
    {
      "field_id": "native_website",
      "display_name": "Website",
      "db_field_name": "website",
      "type": "URL",
      "is_custom_field": false
    },
    {
      "field_id": "field_abc123",
      "display_name": "Customer Tier",
      "db_field_name": "custom_fields->>'field_abc123'",
      "type": "SINGLE_SELECT",
      "is_custom_field": true,
      "validation": {
        "required": false,
        "options": ["Free", "Premium", "Enterprise"]
      }
    },
    {
      "field_id": "field_def456",
      "display_name": "Deal Score",
      "db_field_name": "custom_fields->>'field_def456'",
      "type": "NUMBER",
      "is_custom_field": true,
      "validation": {
        "min": 0,
        "max": 100
      }
    }
  ]
}

Validation Layer

Implement multi-tier validation strategy:

Tier 1: Application-Layer Validation (Primary)

Store validation rules in TableField.validation JSONB:

interface ValidationRule {
  required?: boolean
  type?: 'string' | 'number' | 'date' | 'boolean'
  min?: number
  max?: number
  pattern?: string
  options?: string[] // For select fields
  customValidator?: string // Reference to custom function
}
 
// Example validation
const fieldValidation: ValidationRule = {
  type: 'number',
  required: true,
  min: 0,
  max: 100,
}

Tier 2: Database Type Hints (Optional - Future Enhancement)

For critical custom fields that require database-level type safety, promote to generated columns:

-- After profiling indicates "deal_score" is frequently queried
ALTER TABLE companies
ADD COLUMN custom_deal_score INTEGER
GENERATED ALWAYS AS ((custom_fields->>'field_def456')::integer) STORED;
 
CREATE INDEX idx_company_deal_score ON companies(custom_deal_score);
 
-- Now queries can use native column
SELECT * FROM companies WHERE custom_deal_score > 50;

If done correctly, we can have scripts that automatically generate these columns and indexes based on the validation rules. _ :heart_eyes: _

API Layer Design

Request Format (Create/Update):

POST /api/v1/companies
{
  "name": "ACME Corp",
  "website": "https://acme.com",
  "status": "active",
  "custom_fields": {
    "field_abc123": "Premium",
    "field_def456": 85,
    "field_xyz789": "2024-12-20"
  }
}

Response Format:

{
  "id": "uuid",
  "name": "ACME Corp",
  "website": "https://acme.com",
  "status": "active",
  "created_at": "2024-12-20T10:00:00Z",
  "custom_fields": {
    "field_abc123": "Premium",
    "field_def456": 85,
    "field_xyz789": "2024-12-20"
  }
}

Query Parameters:

GET /api/v1/companies?filter[custom_fields.field_abc123]=Premium&sort=-custom_fields.field_def456

Backend Translation:

// Filter translation
const filters = {
  'custom_fields.field_abc123': 'Premium',
}
 
// Prisma query
const companies = await prisma.company.findMany({
  where: {
    custom_fields: {
      path: ['field_abc123'],
      equals: 'Premium',
    },
  },
})
 
// Sort translation
const sort = '-custom_fields.field_def456'
 
// Prisma query with raw SQL for JSONB sorting
const companies = await prisma.$queryRaw`
  SELECT * FROM companies
  ORDER BY (custom_fields->>'field_def456')::numeric DESC
`

Key Design Decisions (v.v.v. important)

1. Field Addressing

Use deterministic, immutable field IDs to survive renames:

<sdtf_field_id>  // e.g., pnfld-ghsi-lern-yzpc-jwpz-pxha

Rationale:

Field display name can change without affecting data
No collision risk across tenants (UUID guaranteed unique)
Clear distinction from native field names

2. Type Coercion

Store values in their natural JSON types within JSONB:

{
  "field_text": "string value",
  "field_number": 42,
  "field_date": "2024-12-20",
  "field_bool": true,
  "field_select": "option1",
  "field_multi": ["option1", "option2"],
  "field_relation": "sdtf-field-id"
}

Type Handling:

function coerceToType(value: any, fieldType: FieldType): any {
  switch (fieldType) {
    case FieldType.NUMBER:
      return typeof value === 'number' ? value : parseFloat(value)
    case FieldType.DATE:
      return value instanceof Date ? value.toISOString() : value
    case FieldType.CHECKBOX:
      return Boolean(value)
    // ... other types
    default:
      return value
  }
}

3. Relational Custom Fields

Support linking custom fields to other records:

{
  "field_rel001": "uuid-of-related-record"
}

SDTF Field Definition:

{
  "field_id": "field_rel001",
  "display_name": "Parent Company",
  "type": "LINK_TO_ANOTHER_RECORD",
  "is_custom_field": true,
  "relation": {
    "type": "many-to-one",
    "table": "companies",
    "display": {
      "format": {
        "fields": ["name", "website"],
        "template": "{name} ({website})"
      }
    }
  }
}

Query with Relation Resolution:

// Fetch company with resolved custom relations
const contact = await prisma.contact.findUnique({
  where: { id },
})
 
// Resolve custom field relations
if (contact.custom_fields?.field_rel001) {
  const relatedCompany = await prisma.company.findUnique({
    where: { id: company.custom_fields.field_rel001 },
  })
 
  // Enrich response
  contact.custom_fields.field_rel001_resolved = {
    id: relatedCompany.id,
    name: relatedCompany.name,
    website: relatedCompany.website,
  }
}

4. Indexing Strategy

Base Index (All Tables):

CREATE INDEX idx_{table}_custom_gin
ON {table} USING gin(custom_fields jsonb_path_ops);

Specific Field Indexes (As Needed):

-- For frequently filtered fields
CREATE INDEX idx_companies_custom_tier
ON companies ((custom_fields->>'field_abc123'));
 
-- For frequently sorted numeric fields
CREATE INDEX idx_companies_custom_score
ON companies (((custom_fields->>'field_def456')::numeric));

When to Add Specific Indexes:

These are a few important factors to consider when deciding whether to add specific indexes:

Field appears in >10% of queries
Filter/sort performance is measurably slow
Query planner shows sequential scans

5. Performance Optimization

Optimization Path:

1. Start: JSONB + GIN index
   ↓
2. Profile production queries
   ↓
3. Identify "hot" custom fields (>10% query usage)
   ↓
4. Promote to generated columns
   ↓
5. Add B-tree indexes on generated columns
   ↓
6. Monitor performance improvements

Example Promotion:

-- Before: Using JSONB path
EXPLAIN ANALYZE
SELECT * FROM companies
WHERE custom_fields->>'field_def456' > '50';
 
-- Slow: JSONB path requires casting and full scan
 
-- After: Promote to generated column
ALTER TABLE companies
ADD COLUMN custom_deal_score INTEGER
GENERATED ALWAYS AS ((custom_fields->>'field_def456')::integer) STORED;
 
CREATE INDEX idx_company_deal_score ON companies(custom_deal_score);
 
-- Fast: Uses B-tree index
EXPLAIN ANALYZE
SELECT * FROM companies
WHERE custom_deal_score > 50;

Data Integrity Considerations

Handling Type Changes

When a field type changes (e.g., TEXT → NUMBER):

async function changeFieldType(
  fieldId: string,
  oldType: FieldType,
  newType: FieldType,
) {
  // 1. Validate change is safe
  if (!isTypeChangeAllowed(oldType, newType)) {
    throw new Error('Cannot convert between these types')
  }
 
  // 2. Scan existing data for compatibility
  const incompatibleRecords = await scanForIncompatibleData(
    fieldId,
    oldType,
    newType,
  )
 
  if (incompatibleRecords.length > 0) {
    return {
      success: false,
      message: `${incompatibleRecords.length} records have incompatible values`,
      records: incompatibleRecords,
    }
  }
 
  // 3. Update field definition
  await prisma.tableField.update({
    where: { field_id: fieldId },
    data: {
      type: newType,
      db_field_type: mapToDBFieldType(newType),
    },
  })
 
  // 4. Optionally convert existing values
  await convertExistingValues(fieldId, oldType, newType)
 
  return { success: true }
}

Handling Null vs Undefined

// Custom field not set (undefined in input)
custom_fields: {
  field_abc123: "value"
  // field_def456 not present
}
 
// Custom field explicitly cleared (null in input)
custom_fields: {
  field_abc123: "value",
  field_def456: null
}
 
// Store null explicitly to distinguish "not set" from "cleared"

Security & Permissions

Field-Level Permissions

Extend existing permission system to support custom field access control:

model FieldPermission {
  id       String @id @default(uuid())
  field_id String
  role_id  String
 
  can_read   Boolean @default(true)
  can_write  Boolean @default(true)
  can_delete Boolean @default(false)
 
  field TableField @relation(fields: [field_id], references: [field_id])
 
  @@unique([field_id, role_id])
}

Permission Check in API:

async function filterFieldsByPermission(
  fields: TableField[],
  userId: string,
  action: 'read' | 'write',
): Promise<TableField[]> {
  const userRoles = await getUserRoles(userId)
 
  return fields.filter((field) => {
    if (!field.is_custom_field) return true // Native fields use existing RBAC
 
    const permission = field.permissions.find((p) =>
      userRoles.includes(p.role_id),
    )
 
    return permission?.[`can_${action}`] ?? false
  })
}

Error Handling

Validation Errors

interface ValidationError {
  field: string;
  message: string;
  code: string;
}
 
// Example error response
{
  "error": "Validation failed",
  "code": "VALIDATION_ERROR",
  "details": [
    {
      "field": "custom_fields.field_abc123",
      "message": "Value must be one of: Free, Premium, Enterprise",
      "code": "INVALID_OPTION"
    },
    {
      "field": "custom_fields.field_def456",
      "message": "Value must be between 0 and 100",
      "code": "OUT_OF_RANGE"
    }
  ]
}

Type Coercion Errors

// Graceful handling of type mismatches
try {
  const numericValue = coerceToType(inputValue, FieldType.NUMBER)
} catch (error) {
  return {
    error: 'Type conversion failed',
    field: fieldId,
    expected: 'number',
    received: typeof inputValue,
  }
}

Future Enhancements

Conditional Field Visibility

Show/hide fields based on other field values:

{
  "field_id": "field_conditional_001",
  "display_name": "Enterprise Features",
  "display": {
    "visibility": {
      "condition": "custom_fields.field_abc123 == 'Enterprise'"
    }
  }
}

Field Templates

Pre-configured field sets for common use cases:

const fieldTemplates = {
  sales_pipeline: [
    {
      name: 'Deal Stage',
      type: 'SINGLE_SELECT',
      options: ['Lead', 'Qualified', 'Proposal', 'Closed'],
    },
    { name: 'Deal Value', type: 'CURRENCY' },
    { name: 'Expected Close Date', type: 'DATE' },
  ],
  customer_success: [
    { name: 'Health Score', type: 'NUMBER', min: 0, max: 100 },
    { name: 'Last Touchpoint', type: 'DATETIME' },
    { name: 'CSM Assigned', type: 'USER' },
  ],
}

Bulk Operations

Efficient bulk updates for custom fields:

PATCH /api/v1/companies/bulk-update
{
  "filter": {
    "custom_fields.field_abc123": "Free"
  },
  "update": {
    "custom_fields.field_abc123": "Premium"
  }
}

Custom Field Analytics

Aggregations and reporting on custom fields:

GET /api/v1/companies/analytics?group_by=custom_fields.field_abc123
{
  "aggregations": [
    { "tier": "Free", "count": 450, "avg_score": 42.5 },
    { "tier": "Premium", "count": 325, "avg_score": 68.3 },
    { "tier": "Enterprise", "count": 125, "avg_score": 89.7 }
  ]
}

Field Change Notifications

Webhook/event system for custom field changes:

{
  "event": "custom_field.updated",
  "table": "companies",
  "record_id": "uuid",
  "field_id": "field_abc123",
  "old_value": "Free",
  "new_value": "Premium",
  "changed_by": "user_uuid",
  "changed_at": "2024-12-20T10:00:00Z"
}

References

EAV Approach RFC - Original EAV pattern analysis
EAV to JSONB RFC - Migration path to JSONB
SDTF Creation Guide - SDTF schema documentation
PostgreSQL JSONB Documentation
PostgreSQL GIN Indexes
Prisma JSON Fields

Conclusion

The hybrid approach leveraging JSONB storage for custom fields on core tables provides the optimal balance of flexibility, performance, and maintainability, at least for now.

We achieve an Airtable-like customization experience without sacrificing the robustness of our core data model, which was the whole point of taking this approach. This approach aligns with our previous technical analysis, leverages existing infrastructure (SDTF), and provides a clear evolution path through generated columns as usage patterns emerge.

With careful monitoring and the ability to promote hot fields to generated columns, this architecture will serve our dynamic field requirements for the foreseeable future, fingers crossed.

RFC: Hybrid Custom Fields on Core Tables

On this page