Custom Fields and Objects
By the Syncfusion DevTutorials team · Last updated
Every customer wants different fields. Store customer-defined fields without a schema change per tenant: compare EAV, JSONB and generated columns in real Postgres, with indexes and query plans.
Every Customer Wants Different Fields
Litware wants a "Contract number" on every ticket. Acme Retail wants "Store ID" and "Region". Contoso wants twelve fields, two of which are dropdowns, and a whole new object called "Asset".
Northwind cannot add 500 columns to the shared tickets table, and it cannot run a schema migration every time one of 50 customers has an idea. The product has to let each tenant reshape its own data without a schema change.
The main idea: describe each tenant's fields as metadata (rows that say "t41 has a field called region, type text"), store the values in a flexible column, and index only the few fields that are queried a lot.
TL;DR
- Metadata-driven design: field definitions live in tables, not in the schema. The app reads them at run time.
- EAV (entity-attribute-value) stores one row per value. Very flexible, but values are text and every field is another join.
- JSONB stores all custom values in one column. Flexible, typed values, and Postgres can index individual keys.
- Generated columns turn one JSON key into a real typed column. Fast and typed, but each one is a schema change.
- Common answer: JSONB for every tenant field, plus an expression index or generated column for the hot ones.
A Real-World Example
A CRM starts with fixed columns. Customers ask for "just one more field" every week. The team adds custom1 to custom20, all text. Six months later, custom7 means "store ID" for one customer and "birthday" for another, a report sorts birthdays as text, and someone needs field 21.
Large SaaS platforms solved this with metadata: a table of field definitions per tenant, and a generic place to store the values. Salesforce, for example, describes storing custom field values in generic "flex" columns driven by metadata, with separate index tables for the fields people search on.
What You Will Learn
- How metadata describes each tenant's fields.
- The three storage models and their trade-offs.
- How to read a query plan and add the right index.
- How to validate values against a tenant's definitions.
EAV, JSONB and generated columns. Each trades flexibility against type safety and query speed.
Field Definitions Are Data
The first table is the same in every model: what fields each tenant has defined.
Adding a field is now an INSERT into field_defs. No deploy, no migration, no other tenant affected.
Validate Before You Store
Because the database no longer knows each field's type, the app must check values against the definition on every write.
Model 1: EAV
Entity-attribute-value stores one row per field value: (ticket_id, field, value). Any tenant can add any field. But the value column is text, and a query on two fields needs two joins.
The text type causes a classic bug: comparing numbers as text.
"100" > "90" is false as text, because "1" comes before "9". A report of "priority above 90" quietly misses every ticket with 100.
Model 2: JSONB
Postgres's JSONB type stores a JSON document in one column, keeps numbers as numbers, and lets you index a single key with an expression index:
An index on the expression (custom->>'priority_score')::int, with tenant_id first, lets the query "t41's tickets with priority above 90" jump straight to the matching rows. The value is cast to a number, so 100 sorts above 90.
A GIN index on the whole column (CREATE INDEX ON tickets_json USING gin (custom)) also helps "contains" queries such as custom @> '{"region": "EMEA"}'.
Model 3: Generated Columns
A generated column is a real, typed column that Postgres computes from another column:
Here a new column priority_score of type int is filled from custom->>'priority_score' and stored, so it always matches the JSON.
It is fast and typed, and ordinary indexes work on it. But adding one is a schema change on the shared table. Use it to promote a field that many tenants query heavily, not for every tenant's new idea.
Read the Plan
The question is always the same: does the query read only this tenant's matching rows, or does it scan the whole shared table?
Put tenant_id first in every index on a shared table. Every query filters by tenant, so the index jumps straight to that tenant's slice. This is the same rule as Lesson 2: the tenant goes everywhere, including index definitions.
Try It: Real Postgres in the Browser
The lab starts a real Postgres database in this page (PGlite), loads 30,000 tickets for 50 tenants, and stores the same two custom fields in all three models. Run the query for each model, read its plan, add the index, and try adding a new field.
Pass rule: the correct answer (19), a plan that uses an index instead of scanning the whole table, and a model where a tenant's new field needs no ALTER TABLE on the shared table.
The "time" shown depends on your machine and changes between runs, so the lab grades on the plan, not the time. In production you check plans the same way: EXPLAIN before you ship a query.
Custom Objects
Some tenants need whole new objects, like Contoso's "Asset". The same idea applies one level up: object definitions in a metadata table, records in a generic table with (tenant_id, object_type, id, data jsonb), and relationships as typed references.
Put limits on customization: fields per object, objects per tenant, and the size of each value. Without limits, one tenant's 4,000-field object becomes everyone's performance problem (Lesson 3).
Check Yourself
Common mistakes
| Mistake | Wrong | Correct | Why it happens |
|---|---|---|---|
| Numbered spare columns | custom1 … custom20 | Metadata + JSONB | It looks quick until field 21, or two tenants use custom7 differently |
| Text values | '100' > '90' | Typed JSON values or casts | EAV and spare columns store text |
| A schema change per tenant | ALTER TABLE when a tenant adds a field | Insert a field definition | Each migration locks and rewrites a shared table |
| No index on hot fields | Every report does a full scan | Expression index on (tenant_id, field) | Plans are never checked |
| No validation | Any JSON accepted | Validate against field_defs on every write | The database no longer knows each field's type |
Practice
What to Remember
- Tenant fields are metadata: definitions in tables, read at run time.
- EAV is flexible but stores text and needs a join per field.
- JSONB keeps types and lets you index hot keys; validate every write.
- Generated columns are for promoting a few hot fields, because each one is a schema change.
- Check query plans, and start shared-table indexes with
tenant_id.
Further Reading
- PostgreSQL: JSON Types: jsonb, operators and indexing.
- PostgreSQL: Generated Columns: stored generated columns.
- PGlite: the Postgres build that runs this lesson's lab in your browser.
- Salesforce Architects: Platform multitenant architecture: metadata-driven customization at very large scale.
Next: Authorization and Sharing. Everyone is in the right tenant. Now decide who inside that tenant may read, edit or delete each record.