← Home

Letting Customers Define Their Own Data

Every mature SaaS product hits this request: “your data model is missing the thing we track.” I hit it building an integration platform at Sprinto. The rest is generic.

The requirement has two layers. Customers want extra fields on records your product already ships (an extra attribute on a user or a device). And they want whole new record types you never modeled (their homegrown risk register, a niche cloud resource), fed by their own data pipelines.

The goal we set was bigger than “extra fields.” A neutral catch-all model can hold any data, but it does not state the facts a customer actually cares about. We wanted customers to create first class entities of their own: typed records the rest of the system treats like any native model. Full CRUD, relationships built on the fly, their own pages in the UI, all scoped to their tenant. And because a definition is declarative, it is portable: the same custom entity can be created in another org the way you would install a package.

The storage decision

There are three classic answers, and two of them are traps.

EAV (one row per entity, attribute, value) keeps the schema in rows, but every real query becomes a pile of self joins, and typed filtering gets expensive fast.

Dynamic DDL (create a real table per customer entity type) gives you real columns and real constraints, but now your application mutates database schemas at runtime. Migrations, backups, ORMs, and your on-call rotation all inherit that decision.

We picked the third answer: a relational schema catalog driving one generic JSONB payload table. The catalog is normal rows: one row per entity type, one row per field, holding the type, required flag, reference edges, and UI options. The data is one table: one row per record, with a JSONB column holding the field values by name. DDL never changes. Postgres can still index and filter into JSONB paths, so querying stays sane.

One caveat comes with this choice: entity payloads are validated at the application layer instead of the database. Most of the design below exists to make that safe.

One contract, not two systems

The tempting design is a separate “custom fields subsystem” bolted onto the side. Most products do that, then spend years teaching every feature (filters, exports, permissions, UI) about the second system.

We went the other way, and the key idea is simple: services should care about the payload, not its source. An ORM record gets normalized into JSON on its way through your services anyway. We made that official. Reads and writes go through standard interfaces over the table, and a module never asks “is this an instance of X.” It asks “does this JSON have the properties my input schema requires.” If it validates, it is a valid entity.

It works a lot like GraphQL’s view of the world. A native database table is just an entity with a set of properties, their types, and their validations. A custom entity defines exactly the same thing, only from catalog rows instead of code. Feed both through one definition engine and downstream code cannot tell the difference. That one move is why custom entities got filtering, validation, API exposure, and UI rendering for free: every consumer that already understood “an entity definition” needed zero new code paths.

The decisions that carry the weight

A closed set of types. A field can be string, boolean, int, double, timestamp, date, or json. Nothing else. Every consumer handles every type with a small switch. Free-form types would break one consumer at a time.

Validation comes from the catalog. Every write builds its validation from the field definitions and checks each record, collecting errors per record instead of failing the whole batch. The catalog is the only source of truth.

References are declared, not enforced. A field can say “this points at that entity type.” The link is checked when the schema is saved, and the UI turns it into clickable navigation. But there is no database foreign key, so a referenced record can still be deleted later. We promise schema-level integrity, not row-level. Know which one you are selling.

Two kinds of unique, and only one is real. Records dedupe on a real database constraint: org plus source plus identifier. A field marked “unique” in the catalog is a display hint, not a constraint. One is enforced, one is a label. Keep them visibly separate or they become a bug factory.

Humans and sync jobs both write, so layer them. Machine-owned fields are overwritten every sync. User edits live in an override layer on top, with who and when. A re-sync never clobbers a human correction, and there is an audit trail.

Customer transforms run in a sandbox, never eval. Customers map raw API data into entities with a sandboxed query language. No arbitrary code runs, anywhere in the path.

Schema evolution, the honest part

Changing a schema is a metadata update, so “migrations” are instant. And that is exactly the trap: nothing touches data already stored. Change a field from string to int and old records keep their strings, now described by a schema that would reject them.

We made this forward-looking on purpose. New writes validate against the new schema. Readers do best-effort rendering of whatever is actually stored, and fall back to showing the raw value when it does not parse. No backfill, no coercion pass.

This is the standing bill for schema-optional storage: the database never guarantees the payload matches the current schema. If your domain cannot tolerate that, this whole design is wrong for you and dynamic DDL starts earning its pain.

The UI writes itself

Because the definition object is the single source of truth, the UI has no knowledge of any specific field. List pages render whatever fields the definition returns. Detail pages walk the field list and dispatch on type: boolean becomes Yes/No, timestamp becomes a formatted date, references become links that open the referenced record. The edit button appears only where the catalog says a field is user-editable.

Ship one generic renderer, and every entity type any customer ever invents gets a full UI on day one.

Takeaways