Building a Scalable User-Defined Fields System with Django & Postgres HStore

At Infino Legal we build software for insolvency practitioners. Like most SaaS products, it started as one size fits all. Then a big client asks for a "priority" column, another one for an incident date, and soon a solo lawyer is filling in the same gigantic form as a big firm, including the client's shoe size. User-defined fields fix that: each office defines its own fields, and only that office sees them.

Why not entity-attribute-value

The textbook answer is three tables: entity, attribute, value. It falls apart quickly. Fetching ten fields means ten joins or a pivot query with MAX(CASE WHEN ...), which the Django ORM can't express, so you end up with one subquery per field. Every value is stored as text, and indexing many attributes at once is hard. With a hundred clients you have thousands of field definitions in one shared system.

One HStore column instead

Postgres HStore (or JSONB) keeps the custom fields in a single key-value column on the same table. It stays queryable with plain SQL, and a GIN index makes lookups fast:

CREATE INDEX CONCURRENTLY idx_cases_udf
    ON cases USING GIN (user_defined_fields);

HStore stores keys and values as strings, so "10" < "5" and numeric filters behave oddly. ISO dates sort correctly, which helps. We started on Postgres 12 and wanted to keep fields flat, so we chose HStore. It has run in production for years without problems, but for a new project I'd pick JSONB: it keeps types and has better index support.

The Django side

• Content types. One UDFDefinition table serves cases, contacts and invoices, instead of a separate system per model.
• A schema for the schema-less column. The definition stores the label, the type (which decides the form widget), the key, whether the field is required, and which office owns it.
• Readable keys. "Date" becomes date_x9z2a, not a UUID. The key never changes, and when you look at the raw data you can tell which field is broken.
• A serializer per type. Values go to strings on save and back to Python types on load.
• Fields injected at runtime into forms from the definitions. Filtering works like a relation:

Case.objects.filter(user_defined_fields__priority_x9z2a="high")

One thing I didn't have time for on stage: an edge case that bit us in production. Some fields are shown only for one case type. If a user changes the case type, the filled-in field disappears but its data stays in the column. We block the change until the user clears those fields, rather than silently deleting data.

Was it worth it

It wasn't a difficult feature, but it changed how clients see the product: they configure their own fields without waiting for us, and the product feels configurable rather than rigid.