Building a "Sell Anything" Product Catalog in Postgres: JSONB + a Typed Shadow Index

The problem

We're building StoreFlux, a headless commerce backend-as-a-service. Most commerce platforms quietly assume every tenant is selling the same kind of thing — clothing, with size and color as the universal example fields. That assumption breaks the moment someone wants to list cars (mileage, transmission, fuel type), real estate (bedrooms, area, purpose), or anything else that isn't retail apparel.

The constraint we set for ourselves: nothing about a product category is predefined or coded. Every product has fixed basic information and a price. Everything else — field names, field types, which fields are filterable — is defined by the store owner themselves, while they add their first product in a category. That becomes the category's form. Every later product in that category reuses the same form; the same form can be attached to any number of categories; sub-categories inherit their parent's form unless they define their own.

A Zameen-style property listing, a PakWheels-style vehicle listing, and a plain t-shirt all go through the exact same mechanism. No "car mode." No vertical-specific entities, migrations, or seed data.

Why this is harder than it sounds

The obvious naive approach — a single attributes JSONB column on the product table — solves half the problem instantly. Rendering a product page, building API responses, generating webhook payloads: all trivial, no joins, just read the JSON. For arbitrary key/value storage with unknown shape ahead of time, JSONB is the right starting point.

It falls apart the moment you need fast filtering at scale:

• "Show me cars between 20,000 and 30,000 miles" — a numeric range query on a key that's different for every tenant and every category.
• "How many cars are automatic vs manual?" — a facet count, computed live, over an arbitrary enum field.
• Doing this with pure JSONB means either a full scan with a cast-and-compare predicate ((attributes->>'mileage')::numeric BETWEEN 20000 AND 30000), which doesn't use an index efficiently for ranges across unknown keys, or declaring a btree expression index per key — which you can't do, because the keys aren't known at schema-design time. They're invented by a store owner at runtime.

We considered three alternatives before landing on the current design:

1. Dynamic DDL — literally ALTER TABLE to add a real column per tenant-defined field. Rejected early: migrations, locking, and column-count ceilings get ugly fast once tenants start defining dozens of fields across hundreds of categories. It also couples schema changes (expensive, rare) to a frequent product-owner action (cheap, common).
2. External search engine (Elasticsearch/OpenSearch/Algolia) — still on the table for full-text search and large-scale faceting later, but we didn't want to take on an async sync pipeline and an eventual-consistency window before the core feature itself was even proven out. Every extra moving part is something that can silently drift out of sync.
3. JSONB + GIN only — covers existence/equality queries reasonably well via jsonb_path_ops, but doesn't give you real range queries or facet counts without the expression-index problem above.

What we actually built: JSONB as source of truth, typed index as a disposable shadow

The design that stuck:

-- source of truth — one row per product, no joins needed to render it
products (
  ...
  attributes jsonb  -- GIN index, jsonb_path_ops
)

-- disposable, rebuildable filter index — NOT a source of truth
product_field_values (
  product_id,
  field_key,
  value_text,
  value_number,
  value_bool,
  value_date
  -- real indexes on value_number, value_date, (field_key, value_text), etc.
)

products.attributes is what the product page, the API, the cards, the export, and the webhooks all read from. It never needs a join. It's the only place a product's data can be considered authoritative.

product_field_values exists purely to make filtering and faceting fast. On every product save — create or update — we rebuild that product's rows in product_field_values from scratch, inside the same database transaction as the JSONB write. Not an incremental patch, not an async job: a synchronous rebuild, same transaction, same commit. That's the key invariant that keeps the two in lockstep — there is never a moment where the index can be observed out of sync with the source of truth, because they commit atomically together.

With that table in place, "cars between 20k and 30k miles" becomes an ordinary indexed range query against value_number, filtered by field_key = 'mileage'. Facet counts become a GROUP BY against value_text or value_bool. No per-tenant-key expression indexes needed — the index columns are generic and typed once, for every possible field, forever.

The form layer on top

The index table alone doesn't explain where field types come from. That's a separate concept: a form.

product_forms (fields jsonb)   -- field definitions: key, kind, required?, filterable?, ...
categories.product_form_id     -- a category points at a form

When a store owner adds the first product in a category, the "Details" section is empty and offers "Add detail": a name and a kind of value (short text, long text, number+unit, yes/no, choose one, choose many, date), plus flags like required, filterable, and show-on-card. Saving the product does two things at once: it writes the chosen values into that product's attributes JSONB, and it persists the field definitions as the category's form. The next product added to that category sees the same form pre-built. Attaching the same form to a different category, or letting sub-categories inherit their parent's form, are both just pointer operations on categories.product_form_id — no data migration needed.

A field's key and type are immutable once created (changing the type of an existing field would invalidate every stored value against it). Removing a field from a form doesn't delete the data already stored in products' JSONB — if the field is added back later, the old values simply reappear, since the JSONB never lost them.

Update semantics worth calling out

Product create validates every field in the form (required fields must be present, unless the product is saved as a draft). Product update merges: only the fields explicitly sent in the update change, null clears a specific field, and anything else — including fields outside the current form — is left untouched. This matters for bulk import and partial-update API calls, where you don't want a product's unrelated custom fields silently wiped because an update payload didn't mention them.

What this bought us

• A property listing, a vehicle listing, and a t-shirt all flow through identical code paths. Zero vertical-specific entities or seed data.
• Filtering and faceting stay index-fast regardless of how many arbitrary fields a tenant has defined, because the shadow index is generically typed, not per-key.
• The source of truth stays simple (one JSONB column) for every consumer that doesn't care about filtering — rendering, export, webhooks.

What it costs

• A double write on every product save: the JSONB write, then the shadow-index rebuild. For our volumes this is a non-issue (it's one transaction, a handful of rows), but it's a real cost that a pure-JSONB design wouldn't have.
• The shadow index must always be rebuilt, never incrementally patched, or it will drift. That's a discipline requirement on every code path that writes a product, not just a nice-to-have.
• No foreign key from product_field_values back to products, since the products table is partitioned — referential integrity here is enforced in application code, not the database, which is a trade-off we're watching carefully.

Where we're not done yet

CSV export doesn't currently include the dynamic field columns (import does — field:{key} columns work both ways for import, just not export yet). Site-search text indexing doesn't currently include field values either. Both are straightforward extensions of the same pattern, just not built yet.

Open question for anyone who's solved this before

If you've built a "tenant defines arbitrary fields, needs fast filtering" system before — a form builder, a headless CMS with custom fields, a multi-vertical marketplace — I'd genuinely like to know what you did differently. Expression indexes per discovered key? A columnar side-store? CDC into something OLAP-ish? Happy to compare notes in the comments.

---

We're building this at storeflux.store — a headless commerce backend-as-a-service. This feature shipped a few days ago and is running on our own demo store now.
``

Comments

[Oleksandr]

The "disposable shadow index" pattern is the perfect way to solve the dynamic filtering problem without going full Elasticsearch. Rebuilding it synchronously in the same transaction as the JSONB write guarantees zero drift, which is a massive relief because eventual consistency in search indexes is a nightmare to debug.

One question on the update semantics: since you rebuild the product_field_values rows from scratch on every save, how are you handling bulk imports or mass updates of thousands of products? If a store owner changes a field definition (e.g., makes a field filterable), do you have to re-index every product in that category to populate the new index rows, or does the query layer just ignore the missing index entries until the next save?

Great write-up on the generic typed index approach!