CRM Custom Fields — metadata-driven dynamic schema, without losing search speed.

A CRM platform whose customers each wanted their own custom fields per entity, with fast filtering and search across them, without exploding the relational schema. A hybrid storage + metadata-driven architecture solved it.

Industry: CRM / Enterprise SaaS Stack: Relational DB (PostgreSQL / MS SQL) + JSONB / key-value store + search index Role: Solution Architect

The situation (why they called)

The CRM needed to let every customer define their own fields on the core entities (Contacts, Accounts, Deals, Tickets) — different field names, different types, different validation rules. And those custom fields had to be searchable and filterable just like the built-in ones — "show me all Deals where Custom Region = 'EMEA' and Custom Tier >= 3".

The team had two prototypes to choose between, both bad:

• Schema explosion approach: add a column to the table for each custom field per customer. After a year you'd have a Contacts table with thousands of mostly-NULL columns and DBAs in tears.
• Generic key-value approach: a single custom_field_values table with one row per (entity, field, value). Easy to write to, but every search means N joins and the query planner gives up.

Neither could carry the customer base they were targeting.

The diagnosis

Three observations changed the design:

1. Most custom fields are read-heavy and filter-heavy (sales reps slice and dice their pipeline), but write-rare. Optimise for read.
2. A small fraction of custom fields drive most of the queries. Those need real database indexes; the long tail can stay flexible.
3. The metadata about fields is small (hundreds of definitions per customer at most), but the data itself is large (millions of rows of values). Treat them differently.

The decision

The architecture, in four layers

1. Field-definition layer (metadata)

• One custom_field_definition row per (customer, entity, field): name, data type, validation rules, default value, whether it's indexed, whether it appears in search
• Metadata is cached in-process and invalidated via pub/sub on change — every API call resolves field info without a DB hit
• Field rename / type-change is a managed migration (not a free-for-all on production data)

2. Value-storage layer (hybrid)

• Structured fields (built-in + customer-promoted hot fields): real columns on the entity table
• Dynamic fields: JSONB column custom_fields on the entity table; one document per row keyed by field ID
• Same row = same physical page = no joins for the common "load contact + all custom fields" query
• Migration path: a hot dynamic field can be promoted to a structured column without API changes (the value layer abstracts where it lives)

3. Search optimisation

• For frequently-filtered custom fields: PostgreSQL expression indexes on JSONB paths (e.g., CREATE INDEX ON deals ((custom_fields->>'region'))) generated from metadata
• Dynamic query builder reads the metadata, picks the cheapest plan, falls back gracefully when no index exists
• For very large customers / cross-field search: an OpenSearch / Elasticsearch index syncs from the JSONB column via a CDC stream; the application falls through to it for queries the relational DB can't serve cheaply

4. UI & API layer

• Dynamic form rendering: the UI fetches field definitions for the customer, renders the right input control per type, validates against the rules — no per-customer UI code
• API: field reads and writes go through a single endpoint that resolves metadata on the fly; consumers don't need to know which fields are structured vs dynamic
• The same shape works for built-in and custom fields — clients treat them uniformly

Key decisions (and what we said no to)

• Yes: JSONB on the parent table — colocated reads, zero joins for common loads
• Yes: metadata-driven dynamic indexing — promote hot fields to indexes without code changes
• No: a column-per-custom-field schema (would explode within a year)
• No: a generic EAV (Entity-Attribute-Value) table (easy to write, impossible to query at scale)
• No: shipping every customer a search engine on day one — only the ones whose load justifies it

The outcome

What I'd do differently

I'd ship the search-engine layer earlier, even for small customers. We initially gated it behind "you've crossed N records" — which made sense for cost reasons, but also meant that any customer who hit a complex cross-field query in the relational DB had a noticeably worse experience until they crossed the threshold. The failure mode mattered more than the cost — a small monthly fee per customer would have been worth the consistent UX.

Have a CRM, ERP, or vertical SaaS where customers want to extend the schema themselves?