Custom Fields & Schema Extensibility — Chat Prompts

⬅️ Back to 6. Grow

If you're a B2B SaaS in 2026 with anything resembling a "record" — customers, deals, tasks, tickets, projects, contacts, products, invoices, anything — you'll hit the moment a customer says "we need a field for X." Then another customer says "we need Y." Then enterprise procurement says "we need 25 fields including custom dropdowns and multi-select tags with validation." The naive shape: add columns to your DB schema as customers ask. Works for the first 3 fields; breaks at 30. By 100 fields, your schema is unreadable, every migration is a battle, and the customer-by-customer customization is impossible to maintain.

The right shape: a custom-fields system that lets customers (or your team via admin) define schema extensions per-tenant, per-resource-type, with proper validation, indexing, search, permissions, and audit. Salesforce built a multi-billion-dollar moat on this — flexibility without forking. Linear, Asana, HubSpot, Notion, ClickUp all ship sophisticated custom-field systems because customers will pay 2-5x more for "we adapt to your data" vs "you adapt to our schema."

This chat walks through implementing a real custom-fields system: data model, field types, validation, query/filter/sort, search, permissions, the UI for definers + users, performance at scale, and operational realities.

What you're building

Custom-field definition system (per-tenant, per-resource-type)
Multiple field types (text, number, date, dropdown, multi-select, user-picker, etc.)
Per-field validation rules
Per-field permissions (admin-only / read-only / etc.)
Storage strategy (JSONB, EAV, or per-resource columns)
Query/filter/sort by custom fields
Search-indexable custom fields
UI for admins (define fields)
UI for users (fill in fields)
Audit log of field changes
Migration / import of existing customer data into custom fields

1. Decide the scope BEFORE building

Help me decide what shape of custom fields to ship.

Three increasingly-deep shapes:

LEVEL 1: PRESET CUSTOM FIELDS (the simplest start)
- You define ~20 optional fields per resource type
- Customer toggles which they want visible
- No customer-defined fields; you control the schema
- Pros: ships in 2-3 weeks; manageable schema
- Cons: limited; doesn't satisfy enterprise asks

LEVEL 2: TENANT-DEFINED CUSTOM FIELDS (typical)
- Customer admin can ADD their own fields
- Field types from your menu (text, number, date, dropdown, etc.)
- Stored in JSONB or EAV
- Pros: real flexibility; matches Salesforce-style demand
- Cons: more engineering; query/index complexity

LEVEL 3: USER-DEFINED FIELDS WITH FORMULAS / VALIDATION
- Level 2 + computed fields (Excel-formula style)
- Per-field permissions
- Multi-step validation
- Conditional visibility / required-when rules
- Pros: power-user friendly; deep customization
- Cons: 3-6 months; ongoing complexity

DEFAULT FOR MOST B2B SaaS:
- Year 1-2: Level 1 (preset)
- Year 2+: Level 2 (tenant-defined; this is "real" custom fields)
- Year 3+: Level 3 only when customers explicitly demand
- Don't build Level 3 speculatively

Which to ship FIRST:
- Have <20 customers asking: Level 1
- Have 20+ customers asking + clear Salesforce-style enterprise pull: Level 2
- Don't pre-build Level 3 features

Output: scope statement; explicit "we are NOT building" boundary.

Output: scope statement preventing scope creep.

2. Choose the storage strategy

Storage strategy is the most important design decision. Three approaches:

OPTION A: JSONB COLUMN ON THE RESOURCE TABLE
- Each resource (e.g., contacts) has a `custom_fields` JSONB column
- Field definitions stored separately (custom_field_definitions table)
- Field values stored as { field_definition_id: value } in JSONB

Pros:
- Simple schema; no joins
- Postgres GIN index on JSONB makes queries fast
- Backup / restore trivial
- Modern; aligned with Postgres strengths

Cons:
- Hard to enforce data types at DB level
- Schema validation must happen in app code
- Querying complex deeply-nested structures has limits
- Aggregations slower than columnar

Best for: most B2B SaaS in 2026

OPTION B: EAV (Entity-Attribute-Value) PATTERN
- Three tables: entities, attributes, values
- Each attribute value is a row in `custom_field_values`
- Joins required for any read

Pros:
- Strict data typing per attribute
- Strict referential integrity
- Granular per-attribute audit

Cons:
- Joins everywhere; slow at scale
- Querying is awkward (lots of LEFT JOINs)
- Hard to maintain
- Modern Postgres makes this less attractive than 10 years ago

Best for: enterprise systems where strict data integrity > query simplicity

OPTION C: PER-TENANT DYNAMIC TABLES
- Generate actual DDL: ALTER TABLE customers_acme ADD COLUMN custom_field_x ...
- Each tenant gets their own column set

Pros:
- Native types; native indexes; native query plan optimization
- Best query performance

Cons:
- DDL operations are heavy; row-level locks
- Schema drift between tenants
- Backup / migration complexity
- Hard to reason about
- Doesn't scale beyond ~1K tenants comfortably

Best for: very specific scenarios (Salesforce historically did this); generally avoid in 2026

DEFAULT FOR 2026:
- OPTION A (JSONB) for most B2B SaaS
- Postgres GIN index handles the common queries
- App-level validation enforces types

Schema:

custom_field_definitions (
  id              uuid pk
  workspace_id    uuid not null
  resource_type   text not null  -- 'contact', 'deal', 'task', etc.
  name            text not null  -- 'Customer Tier'
  slug            text not null  -- 'customer_tier' (auto-generated; immutable)
  type            text not null  -- 'text', 'number', 'date', 'dropdown', 'multi_select', 'user', 'checkbox', 'currency', 'url', 'email', 'phone'
  config          jsonb  -- type-specific (e.g., dropdown options, min/max, etc.)
  required        bool default false
  default_value   jsonb
  visibility      text default 'all'  -- 'all', 'admin_only', 'creator_only'
  editable_by     text default 'all'
  display_order   int default 0
  created_at      timestamptz
  archived_at     timestamptz
)

UNIQUE INDEX (workspace_id, resource_type, slug)

resources have a `custom_fields jsonb` column.

GIN INDEX on resources.custom_fields for query performance.

Walk me through:
1. Storage decision (JSONB recommended)
2. Migration to add custom_fields JSONB to existing resource tables
3. The custom_field_definitions schema
4. Indexes for common query patterns
5. RLS / multi-tenancy enforcement

Output: a storage approach you don't regret in 18 months.

3. Define field types + validation

Field types you should ship in v1:

CORE TYPES:

1. text (single line; max 255 chars)
2. textarea (multi-line; max 5000 chars)
3. number (integer or decimal; min/max optional)
4. currency (number + currency code; e.g., $1500.00 USD)
5. date (date only; ISO 8601)
6. datetime (date + time; with tz)
7. checkbox (boolean)
8. url
9. email
10. phone

REFERENCE TYPES:

11. dropdown / single_select (options defined in config; one value)
12. multi_select (options defined in config; array of values)
13. user (references a user in the workspace)
14. record_link (references another resource type, e.g. a deal links to a contact)

ADVANCED (Level 3):

15. formula (computed; depends on other fields; later)
16. rollup (aggregate from related records; later)
17. attachment (file reference; later)

Validation rules per type:

text / textarea:
- min_length, max_length
- regex pattern (use sparingly)

number / currency:
- min, max
- decimal places (currency: 2)

dropdown / multi_select:
- option list (with display labels + stored values)
- allow_other (custom value beyond list)

date:
- min_date, max_date

required:
- enforced at write time

uniqueness:
- enforced per (workspace, resource_type, field, value)
- e.g., 'External ID' must be unique per workspace

Implementation:

const fieldTypeRegistry = {
  text: {
    serialize: (val) => String(val).trim(),
    deserialize: (val) => val,
    validate: (val, config) => {
      if (typeof val !== 'string') return { error: 'must_be_string' }
      if (config.min_length && val.length < config.min_length) return { error: 'too_short' }
      if (config.max_length && val.length > config.max_length) return { error: 'too_long' }
      if (config.pattern && !new RegExp(config.pattern).test(val)) return { error: 'invalid_format' }
      return { ok: true }
    },
  },
  number: {
    serialize: (val) => Number(val),
    deserialize: (val) => val,
    validate: (val, config) => {
      if (typeof val !== 'number' || isNaN(val)) return { error: 'must_be_number' }
      if (config.min !== undefined && val < config.min) return { error: 'too_small' }
      if (config.max !== undefined && val > config.max) return { error: 'too_large' }
      return { ok: true }
    },
  },
  // ... and so on per type
}

Validation pipeline:

async function validateCustomFields(workspaceId, resourceType, values) {
  const definitions = await getFieldDefinitions(workspaceId, resourceType)
  const errors = []
  
  for (const def of definitions) {
    const value = values[def.slug]
    
    if (def.required && (value == null || value === '')) {
      errors.push({ field: def.slug, error: 'required' })
      continue
    }
    
    if (value != null) {
      const handler = fieldTypeRegistry[def.type]
      const result = handler.validate(value, def.config)
      if (result.error) {
        errors.push({ field: def.slug, error: result.error })
      }
    }
  }
  
  return errors
}

Implement:
1. Field type registry
2. Per-type validators
3. Validation pipeline at write
4. Error messaging UI
5. Type-safe TypeScript types per field (zod schemas)

Output: a typed system that catches bad data.

4. Build the query / filter / sort layer

Customers will WANT to filter, sort, and search by custom fields. This is where JSONB earn its keep.

Postgres JSONB query patterns:

-- Filter by exact match
SELECT * FROM contacts 
WHERE workspace_id = $1
  AND custom_fields ->> 'customer_tier' = 'Enterprise'

-- Filter by range
SELECT * FROM contacts
WHERE (custom_fields -> 'annual_revenue')::numeric > 100000

-- Multi-select contains
SELECT * FROM contacts
WHERE custom_fields -> 'tags' @> '["VIP"]'::jsonb

-- Sort by custom field
SELECT * FROM contacts ORDER BY custom_fields ->> 'priority'

Indexing:

For frequently-filtered fields, create GIN expression indexes:

CREATE INDEX idx_contacts_customer_tier 
  ON contacts ((custom_fields ->> 'customer_tier'))
  WHERE workspace_id IS NOT NULL;

CREATE INDEX idx_contacts_custom_fields_gin
  ON contacts USING GIN (custom_fields jsonb_path_ops);

For very high-volume filtering on specific fields:
- Promote to first-class column (denormalize) on a per-customer basis
- Periodic background job: detect "filtered > 1000 times/day" → suggest promotion

API patterns:

POST /api/contacts/search
{
  "filters": [
    { "field": "custom.customer_tier", "op": "eq", "value": "Enterprise" },
    { "field": "custom.annual_revenue", "op": "gt", "value": 100000 }
  ],
  "sort": { "field": "custom.priority", "direction": "asc" },
  "limit": 50
}

Implementation:

function buildQueryFromFilters(filters: Filter[]) {
  const wheres = []
  const params = []
  
  for (const f of filters) {
    if (f.field.startsWith('custom.')) {
      const slug = f.field.replace('custom.', '')
      const opSql = mapOpToSql(f.op)
      wheres.push(`custom_fields ->> '${slug}' ${opSql} $${params.length + 1}`)
      params.push(f.value)
    } else {
      // first-class column
      wheres.push(`${f.field} ${mapOpToSql(f.op)} $${params.length + 1}`)
      params.push(f.value)
    }
  }
  
  return { wheres: wheres.join(' AND '), params }
}

Performance considerations:
- For tables > 100K rows, indexes are mandatory
- GIN expression indexes per high-traffic field
- LIMIT all queries (default 50; max 500)
- EXPLAIN ANALYZE every customer-facing query plan
- Cache frequently-run queries in Redis (60s TTL)

Implement:
1. Query builder for custom-field filters
2. GIN indexing strategy
3. Auto-suggest indexes based on usage
4. Performance budget (p95 < 500ms for typical filter)
5. Pagination

Output: queries that don't time out at scale.

5. Make custom fields searchable

Search is harder than filter. Customers expect "search across all fields" to include custom fields.

Approach 1: Full-text search via Postgres FTS

Add a generated column for searchable custom field values:

ALTER TABLE contacts 
ADD COLUMN search_text text 
GENERATED ALWAYS AS (
  name || ' ' ||
  email || ' ' ||
  coalesce(custom_fields ->> 'company', '') || ' ' ||
  coalesce(custom_fields ->> 'notes', '')
) STORED;

CREATE INDEX idx_contacts_search ON contacts USING GIN (to_tsvector('english', search_text));

Approach 2: Algolia / Typesense (for serious search)

- Index resources with all standard + custom fields flattened
- Per-tenant index OR per-tenant filter
- Re-index on resource update

Approach 3: Semantic search (embeddings)

- For unstructured text fields (notes, descriptions)
- Embed at write time; store in vector DB
- Pair with FTS for hybrid

Tradeoffs:
- Postgres FTS: simple; works to ~1M rows/tenant
- Algolia/Typesense: faster; per-record cost
- Semantic: best for long text; expensive

Default: Postgres FTS with generated columns + GIN. Migrate to Algolia/Typesense if FTS hits ceiling.

Implement:
1. Generated column for search_text including key custom fields
2. GIN tsvector index
3. Search API endpoint
4. Re-search-text update on field-definition changes
5. Migrate path to external search if scale demands

Output: search that surfaces records by any field.

6. Build the admin UI (define custom fields)

Admin-side UI: define + manage custom fields.

Page: /settings/custom-fields/[resource_type]

Layout:
- List of existing custom fields
- "Add custom field" button → wizard
- Reorder via drag-drop
- Per-field: edit, archive, delete (with cascade warnings)

Add-field wizard:

Step 1: Field type
- Visual grid of field types (text, number, date, dropdown, etc.)
- Brief description per type

Step 2: Field details
- Name (display label)
- Slug (auto-generated from name; editable; immutable post-creation)
- Description / help text (shown to users)
- Required (yes/no)
- Default value
- Type-specific config (dropdown options, min/max, etc.)

Step 3: Permissions
- Visible to: All / Admin only / Creator only
- Editable by: All / Admin only / Read-only
- Required fields visible

Step 4: Confirm
- Preview of how field appears
- Confirm + create

Edit-field:
- Limited to non-breaking changes:
  - Display label changeable
  - Description changeable
  - Required toggle (must handle existing nulls)
  - Default value changeable (existing values not affected)
  - Display order
- NOT changeable:
  - Slug (immutable; defines the JSONB key)
  - Type (would corrupt existing data)

Archive vs delete:
- Archive: hide from UI; preserve existing values
- Delete: remove field def + all values (DANGEROUS; warn + confirm + audit)

Bulk operations:
- Apply defaults to existing records (e.g., add new field; set all to default)
- Validate existing data against new constraints (e.g., if making required, find empty)

Implement:
1. The list + detail pages
2. The wizard
3. The edit flow with safety
4. The archive vs delete semantics
5. Permissions enforcement
6. Audit log of definition changes

Output: admin UX that doesn't terrify customers.

7. Build the user UI (fill in custom fields)

User-side UI: customer-defined fields appear seamlessly alongside built-in fields.

Display patterns:

A. Detail view (record detail page)
- Built-in fields at top (name, email, etc.)
- Custom fields section below (or interleaved per design)
- Required custom fields marked with *
- Help text on hover/tooltip

B. List view (table of records)
- Custom fields available as columns
- User can show/hide via column-picker
- Default visible columns set by admin per resource type

C. Filter UI
- Available custom fields appear in filter dropdown
- Operators per field type
- Multiselect for dropdown / multi_select fields

D. Form UI (create / edit record)
- Built-in fields rendered in standard layout
- Custom fields rendered per their type:
  - text → input
  - number → numeric input with step
  - dropdown → select with options
  - multi_select → tag-input or chip-picker
  - date → date picker
  - user → user-picker autocomplete
  - record_link → record-picker autocomplete

Type-specific components:

const fieldComponents = {
  text: TextField,
  textarea: TextareaField,
  number: NumberField,
  currency: CurrencyField,
  date: DateField,
  datetime: DateTimeField,
  checkbox: CheckboxField,
  dropdown: DropdownField,
  multi_select: MultiSelectField,
  user: UserPickerField,
  record_link: RecordPickerField,
  // ...
}

function renderCustomField(definition, value, onChange) {
  const Component = fieldComponents[definition.type]
  return (
    <Component
      label={definition.name}
      help={definition.description}
      value={value}
      onChange={onChange}
      required={definition.required}
      config={definition.config}
    />
  )
}

Save flow:

On submit:
1. Validate all fields (built-in + custom) via app-level validators
2. Show errors inline
3. Submit to API; server re-validates
4. Server stores: { ...built_in_fields, custom_fields: { ...customs } }

Optimistic UI:
- Update local state immediately
- Sync to server async
- Revert + show error on failure

Implement:
1. Field renderer mapping field types to components
2. Detail / list / form views
3. Filter UI showing custom fields
4. Validation on form submit
5. Optimistic updates with revert

Output: a user UX where custom fields feel native.

8. Permissions + audit

Custom fields can contain sensitive data. Enforce permissions + audit.

Permission tiers:

Per-field:
- visibility: all (workspace member sees) | admin_only | creator_only
- editable_by: all | admin_only | read_only

Per-record:
- Inherits from base resource permissions
- Custom fields don't grant additional access

Implementation:

When fetching a resource, mask custom fields the viewer can't see:

async function getResourceForViewer(resourceId, viewerId) {
  const resource = await db.contacts.findById(resourceId)
  const definitions = await getFieldDefinitions(resource.workspace_id, 'contact')
  
  const masked = { ...resource, custom_fields: {} }
  
  for (const def of definitions) {
    if (canViewerSeeField(def, viewerId, resource)) {
      masked.custom_fields[def.slug] = resource.custom_fields[def.slug]
    }
  }
  
  return masked
}

When updating, check editable_by:

async function updateResource(resourceId, updates, editorId) {
  for (const slug in updates.custom_fields) {
    const def = definitions[slug]
    if (!canEditorEditField(def, editorId)) {
      throw new PermissionError(`Cannot edit ${slug}`)
    }
  }
  // ... write
}

Audit log:

Every field-value change logged:

custom_field_audit (
  id            uuid pk
  resource_type text
  resource_id   uuid
  field_slug    text
  old_value     jsonb
  new_value     jsonb
  editor_id     uuid
  occurred_at   timestamptz
)

Surfaced in:
- Resource detail page (history tab)
- Customer-facing audit log export
- Internal admin tools for support

Implement:
1. Per-field permission enforcement
2. Field-level masking on read
3. Field-level access check on write
4. Audit log capture per change
5. Customer-facing audit export

Output: permissions that don't leak.

9. Operational realities + edge cases

Walk me through:

1. Customer adds 50+ custom fields
- Pre-list: warn at 20+ fields ("dashboards may slow")
- Hard limit: 100 fields per resource type per workspace (configurable per plan)

2. Customer wants to import existing data into custom fields
- CSV import flow: map source columns to custom fields
- Validate per-row; show errors before commit
- Background job for >1000 rows

3. Customer changes field type
- Don't allow direct type change (data corruption)
- Migration: create new field; copy/transform values; archive old; rename to original slug if needed
- Document the workaround

4. Customer deletes a field with data
- Confirm (text-typed confirmation: "DELETE")
- Background job: remove field from all records' custom_fields
- Audit: who deleted; when; how many records affected

5. Workspace admin makes field required after data exists
- Warn: N records currently empty
- Options: keep optional / set default for empty / make required (and show warning until customers fill in)

6. Field rename
- Display label changeable; slug stays
- Show "(was: Old Name)" in audit log

7. Workspace template
- Workspaces created from templates inherit field definitions
- Templates are: "blank", "sales-CRM", "support-ticketing", etc.

8. API consumers (external apps)
- API exposes both built-in + custom fields
- Schema endpoint: GET /api/schema/contacts → field definitions
- Webhook payloads include custom fields

9. Reporting / BI export
- All custom fields exported to data warehouse
- Schema evolution handled (new field appears as new column in BI)

10. GDPR / data export
- Customer's data export includes custom fields
- Customer's data deletion removes custom fields too

11. Performance at scale
- 100K records × 50 fields = ok with GIN
- 10M records × 100 fields = heavy; consider selective denormalization
- Monitor: query latency p95 by tenant

12. Multi-tenancy + GIN index
- Compound index: (workspace_id, ...) + GIN on custom_fields
- Helps planner pick right plan

For each: code change + UX impact + ops consideration.

Output: ops that survive customer creativity.

10. Recap

What you've built:

What you're explicitly NOT building in v1:

Formula / computed fields (Level 3; defer)
Rollup / aggregation fields (Level 3; defer)
Conditional visibility / required-when rules (Level 3; defer)
Per-customer custom validation rules (defer)
Field-history time-travel queries (use audit log)
Automatic field-discovery from CSV import (manual mapping is fine)

Ship Level 2 (tenant-defined custom fields) in 6-12 weeks. Add Level 3 features only when customer demand justifies.

The biggest mistake teams make: choosing EAV storage. Modern Postgres + JSONB beats EAV in every dimension that matters in 2026.

The second mistake: not planning for indexes. Without GIN indexes, custom-field queries scan the table and fail at scale.

The third mistake: skipping permissions. Custom fields end up containing PII; permissions matter from day one.