Four years of product data architecture, built around the team

Oh Polly ran on Airtable, and the teams were fluent in it. Our position was that this was not a problem to be migrated away from. It was the most valuable thing in the estate.

Usability and flexibility of that kind do not translate into an ERP. Push company-wide operations into one and the organisation eventually rejects it, or immediately does, and you have spent a great deal of money to end up with shadow spreadsheets and a system of record nobody trusts.

So the recommendation was to keep the finance system where it was for the moment, invest in reporting, and design the product data architecture around the tool the business would actually use.

NetSuite went in later, scoped to finance, which is where an ERP is unambiguously the right answer. The product master stayed where the people were. Four years on, that split is still the architecture.

In late 2024 we restructured the engagement around named workstreams rather than open-ended support, and concentrated on two: rebuilding the product master, and the finance integration. Scope discipline is a deliverable.

Four Airtable bases carry the product lifecycle: a sample board where design and merchandising create styles, the master PIM as the central enrichment hub, a production base for factory ordering, and the legacy product database.

The structural change was adding a style layer above the old parent level, giving a three-tier hierarchy. A style groups parent styles and variants; a parent style groups variants by colour; a variant SKU is the lowest record, at size and barcode level, formatted as style, colour and size.

So one style number resolves down through a colour to a specific size, and the variant SKU layer is the single source of truth for every operational system downstream.

Reporting was separated onto its own interface rather than living inside the working data - correct, because the people using a pricing interface don't need price history in front of them while they work.

Then team-specific interfaces, each showing only what that function needs with the workflow built in: an ecommerce interface shaped like a storefront PIM, a QA interface for care instructions, a pricing interface, and views for factory and copywriting. If a style is cancelled with no other linked styles, it disappears from the factory and copywriter views automatically.

Two sequencing decisions worth stating. The data cleanse ran before data validation, because validating dirty data just produces a long list.

And the legacy product database was kept alive on a two-way sync rather than cut over, because several teams still depended on it. Keeping a legacy system running on purpose is a decision; leaving one running by accident is not.

Shopify is fed by two custom functions on AWS rather than through middleware, and the design choices are the point.

The product sync watches a controlled view in the PIM, and a configuration table maps PIM fields to Shopify fields - so the mapping is data, not code, and adding a field is an edit rather than a deployment. Failures write to a sync error log capturing the script path and line number, and post to Slack.

When a product doesn't appear on the site, somebody knows within minutes and knows which line to look at.

The price sync runs hourly, batches changes into a queue and pushes them through a worker. It also carries scheduled sale pricing with start and end times, so promotional timing is handled in one place across every storefront.

Ahead of Black Friday the frequency was tightened from thirty minutes to fifteen. Eight-plus storefronts across two brands and five markets sit downstream.

For operational systems we defined the contract and a middleware partner consumed it. HighCohesion polls the variant SKU table every fifteen minutes and delivers to the UK warehouse system and to NetSuite; the Mexico 3PL is fed from a NetSuite saved search on a twenty-minute cycle. Three systems, one contract, and we own the contract rather than each connection.

After go-live we retrofitted governance, which is the right order even though it looks like the wrong one.

The data contract documents field-level schemas for styles, parent styles, variant SKUs, pricing and Shopify data - the parent style schema alone runs to a couple of hundred columns - plus reference tables, log tables, validation and quarantine rules, and a sync freshness target of fifteen minutes. Changes go through a request, approval and version-bump process, and sandbox mode was enabled so field-structure changes happen there first.

Before that existed, a field change was a conversation. Now it's a versioned decision.

Restock dates the customer service team couldn't answer. A single restock date field, in a business fulfilling from three separate channels.

Ticket volume rose after restock dates came off the website and nobody could give a straight answer, because the data model was coarser than the operation. A granularity failure, not a bug.

Purchase orders failing at scale in finance. Products weren't in NetSuite yet when the PO was raised - a fifteen-minute poll plus late upstream data entry, surfacing two systems away as a finance problem.

The sync error log and Slack alerting are the built answer, so the failure is visible where it originates rather than where it lands.

Two systems holding different customs codes, both partly right. Rather than overwrite either side, the field became a formula with an override: use the manual value if one exists, otherwise the value from the factory sync.

Field names were deliberately preserved so no integration downstream noticed. Both truths kept, nothing broken.

The most recent workstream is wholesale, and it turned into a pricing exercise.

Two defects sat in the logic. Wholesale prices had been set relative to retail in a way that gave away margin against target, and a made-to-order discount was being applied on top of logic that already accounted for it.

The fix was a single rule tying wholesale price to retail excluding VAT, and removing the duplicated branch so the discount applies once.