# Custom Fields and Objects

Course: [High-Level Design](https://www.devtutorials.ai/learn/system-design/hld) · Section: B2B SaaS Architectures
Source: https://www.devtutorials.ai/learn/system-design/hld/b2b-saas/custom-fields

> 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

1. How metadata describes each tenant's fields.
2. The three storage models and their trade-offs.
3. How to read a query plan and add the right index.
4. How to validate values against a tenant's definitions.

## 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](https://www.postgresql.org/docs/current/datatype-json.html): jsonb, operators and indexing.
- [PostgreSQL: Generated Columns](https://www.postgresql.org/docs/current/ddl-generated-columns.html): stored generated columns.
- [PGlite](https://pglite.dev/): the Postgres build that runs this lesson's lab in your browser.
- [Salesforce Architects: Platform multitenant architecture](https://architect.salesforce.com/fundamentals/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.