A B2B commerce platform built from scratch for an industrial distributor — because no off-the-shelf solution could model a catalog where every product category carries a different set of technical specifications.
What we built
A bolt and an electric motor share nothing in their specifications. A bolt needs thread_pitch, tensile_strength, head_type. A motor needs RPM, voltage, frame_size. Both live in the same catalog.
Shopify, WooCommerce, and Magento handle this with fixed attribute tables or manual per-product customisation — workable for fashion, unusable for industrial B2B where a single category change rewrites what fields are valid, required, and searchable.
The distributor also needed rigorous search: buyers search by spec value — "M12 stainless", "24V motor" — not just product name. That ruled out anything that doesn't index structured spec data as searchable text.
Iterosys owns the full stack — from database schema and search architecture through to both frontends and DigitalOcean deployment.
What made this hard
Fully dynamic schemas (store everything as JSONB, validate nothing) are easy to build and impossible to trust. We needed attributes to be flexible enough for any product category yet rigorous enough to enforce types, units, required fields, and regex patterns at write time. The solution is a schema-per-category-hierarchy that inherits down the tree and validates on every product create/update.
Spec values live in a JSONB column. Full-text search doesn't reach inside JSON by default. We solved this with PostgreSQL TSVECTOR triggers that extract all spec values into the product's search vector at write time — indexed with GIN. Searching "M12 stainless" matches products whose specs contain thread_size: M12 and material: stainless steel — with ranked results.
Product hierarchy (materialized path)
Attribute definitions (inherited)
type · unit · min/max · options · regex · variant-defining · filterable
Attributes are defined at any level of the catalog hierarchy — cluster, node, leaf, or product family — and cascade down automatically. A leaf category defining thread_pitch means every family and product under it inherits that attribute. Family-level definitions override category ones with the same key. On every product create or update, the backend resolves the full inherited attribute set and validates variable_specs against it — enforcing type, unit, required, min/max, regex, and allowed options. The result is JSON storage with relational-grade correctness.
PostgreSQL triggers maintain a weighted TSVECTOR column on each product: name and SKU at weight A, family name at B, brand at C, and all spec values (extracted from JSONB) at D. A GIN index makes this fast. Searching "M12 stainless" returns products whose variable_specs contain those values — ranked by relevance, not just name-matched.
Cross-model: categories · brands · families · products all have GIN-indexed search vectors
Each category stores its ancestor chain as a dot-delimited path string (e.g. "1.4.12"). Querying all ancestors for attribute inheritance is a single WHERE IN — no recursive CTEs, no self-joins. This keeps hierarchy queries O(depth) with a btree index rather than O(n) table scans.
Path updated on reparent; single query resolves full ancestor chain for attribute resolution
Separate async SQLAlchemy engines for the write primary and read replica. Listing and search endpoints route to the read engine; mutations route to write. Connection pools are tuned independently. This keeps read-heavy browse traffic off the write primary without requiring application-level sharding later.
asyncpg pools sized per environment — no shared state between read and write paths
We specialise in custom commerce platforms where off-the-shelf solutions fall short — complex catalogs, dynamic product data, and rigorous search.