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!
StoreFlux is a simple way to open and run an online store. You get your own shop for customers, plus a dashboard where you manage products, orders, payments and customers, all in one place.
Great question, and thanks for the kind words. The index isn't tied to the filterable flag: on every save we write index rows for every field defined on the category's form. "Filterable" only controls whether a field appears as a facet in the filter UI. So making a field filterable doesn't need a re-index, the rows already exist. The only case with nothing to index is a product that never had a value for that field, and it gets indexed the next time it's saved with one.
That makes a lot of sense. Decoupling the index from the UI "filterable" flag is a really smart architectural choice—it means you don't have to run painful backfill migrations every time a PM decides they want a new facet in the sidebar.
The edge case handling is clean too: if someone adds a value later, the save action naturally backfills it.
Out of curiosity, what happens if a field is removed from a category's form? Does the shadow index row get cleaned up on the next save, or do you have a separate garbage collection job for orphaned index rows?
StoreFlux is a simple way to open and run an online store. You get your own shop for customers, plus a dashboard where you manage products, orders, payments and customers, all in one place.
Removing a field from a form doesn't touch existing products right away. Their stored values stay on the product. The index rows for that field are cleared the next time each product is saved, because every save rebuilds that product's rows from the current form.
There isn't a background cleanup job for orphaned rows yet. The facet counts only read the fields the form still defines, so leftover rows don't show up in filters. A garbage collection job is a fair thing to add, and worth looking at.
this makes sense, but the "stored values stay on the product" part is what would keep me up at night. if someone removes a field, adds it back three months later under the same key, does the old data just... resurface on the product? because that could be a pretty confusing surprise for whoever re-added the field. or do you version the form schema so old keys never collide?
StoreFlux is a simple way to open and run an online store. You get your own shop for customers, plus a dashboard where you manage products, orders, payments and customers, all in one place.
Yes, the old value comes back. Removing a field from a form doesn't clear it from the products. A key outside the form keeps its stored value, so if the same key is added back, the product shows the old value. We don't version the form schema, so nothing stops a reused key from carrying old data.
The filters behave differently. Filter counts come from the index table, which is rebuilt only when a product is saved. So a product that wasn't saved after the field came back can show the old value while the filter doesn't count it yet.
That's a real gap, and thanks for pushing on it. The two fixes I'd look at are a cleanup when a field is removed, or versioned keys. Neither is built yet, so for now, re-adding a key with a different meaning is risky.
We're a place where coders share, stay up-to-date and grow their careers.
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!
Great question, and thanks for the kind words. The index isn't tied to the filterable flag: on every save we write index rows for every field defined on the category's form. "Filterable" only controls whether a field appears as a facet in the filter UI. So making a field filterable doesn't need a re-index, the rows already exist. The only case with nothing to index is a product that never had a value for that field, and it gets indexed the next time it's saved with one.
That makes a lot of sense. Decoupling the index from the UI "filterable" flag is a really smart architectural choice—it means you don't have to run painful backfill migrations every time a PM decides they want a new facet in the sidebar.
The edge case handling is clean too: if someone adds a value later, the save action naturally backfills it.
Out of curiosity, what happens if a field is removed from a category's form? Does the shadow index row get cleaned up on the next save, or do you have a separate garbage collection job for orphaned index rows?
Good question, and thanks for the kind words.
Removing a field from a form doesn't touch existing products right away. Their stored values stay on the product. The index rows for that field are cleared the next time each product is saved, because every save rebuilds that product's rows from the current form.
There isn't a background cleanup job for orphaned rows yet. The facet counts only read the fields the form still defines, so leftover rows don't show up in filters. A garbage collection job is a fair thing to add, and worth looking at.
this makes sense, but the "stored values stay on the product" part is what would keep me up at night. if someone removes a field, adds it back three months later under the same key, does the old data just... resurface on the product? because that could be a pretty confusing surprise for whoever re-added the field. or do you version the form schema so old keys never collide?
Yes, the old value comes back. Removing a field from a form doesn't clear it from the products. A key outside the form keeps its stored value, so if the same key is added back, the product shows the old value. We don't version the form schema, so nothing stops a reused key from carrying old data.
The filters behave differently. Filter counts come from the index table, which is rebuilt only when a product is saved. So a product that wasn't saved after the field came back can show the old value while the filter doesn't count it yet.
That's a real gap, and thanks for pushing on it. The two fixes I'd look at are a cleanup when a field is removed, or versioned keys. Neither is built yet, so for now, re-adding a key with a different meaning is risky.