How We Rebuilt a Pharmacy E-Commerce Catalog into a Scalable, Airtable-Driven System (Step-by-Step)

Over the last 24 hours, we completed a full architectural reset of a pharmacy e-commerce operation: fixing production-blocking errors, modernizing the data model, and replacing a fragile CSV workflow with a reusable, team-friendly Airtable system.

This post walks through exactly how we did it, why each decision mattered, and how you can replicate the same approach for any complex product catalog, especially in regulated or high-variation industries like pharmacy, wellness, or healthcare.

A Note on Speed: From Two Weeks to Eight Hours

It’s worth calling out the operational impact of this approach.

What we accomplished in the last 24 hours, including architecture decisions, bug fixes, data normalization, migration, documentation, and automation, would have taken approximately two weeks using our previous workflow.

By:

• Eliminating manual CSV handling
• Centralizing logic in reusable scripts
• Using Airtable as a collaborative source of truth
• Building deterministic, idempotent imports
• And working inside a properly versioned Cursor → GitHub → WP Engine pipeline

We compressed that timeline to roughly eight hours of focused execution.

This wasn’t about working faster for the sake of speed.
It was about removing friction from the system.

Once the rules were explicit and the architecture was sound:

• Decisions stopped being debated
• Fixes stopped being one-off
• Changes became reversible
• And progress compounded instead of resetting

That’s the real leverage, not automation alone, but automation built on clarity.

Our Perspective

Most teams don’t move slowly because they lack talent.
They move slowly because their systems fight them.

This rebuild didn’t just clean up a catalog, it removed weeks of future work every time a product is added, changed, or audited.

That’s the difference between maintaining software and operating a system.

1. Start With Stability: Fix Production Errors First

Before building new systems, you must stabilize what’s already live.

The Problem

We were seeing 500 Internal Server Errors on WooCommerce product pages. The root cause was straightforward but dangerous:

• Multiple templates and hooks assumed the global $product object always existed
• In reality, WooCommerce sometimes renders templates outside a valid product context
• When $product was null or malformed, PHP fatals occurred

The Fix

We hardened every product-dependent surface area by adding defensive validation:

Key principles applied

• Never assume $product exists
• Validate before accessing methods or properties
• Fail gracefully instead of crashing the page

Files updated

• woocommerce/single-product/product-attributes.php
• inc/woocommerce.php
• inc/schema.php
• inc/seo.php

Techniques used

• is_object()
• method_exists()
• Guard clauses
• Try/catch blocks around schema + OG logic

Result

• Product pages no longer crash
• SEO and schema rendering works even in partial contexts
• The site became resilient to edge cases introduced by caching, previews, or API calls

Lesson:
Never build new systems on top of unstable foundations.

2. Establish a Proper Dev & Deployment Workflow

Before touching data, we aligned the tooling.

What We Set Up

• Cursor as the primary development environment
• GitHub for version control and safe iteration
• WP Engine as the hosting target (with Cloudflare/WAF constraints in mind)

Why This Matters

This gave us:

• Safe rollback for every change
• A single source of truth for scripts and logic
• Confidence that automation wouldn’t break production

Rule:
If changes can’t be versioned, reviewed, and reverted, they shouldn’t exist.

3. Replace CSV Chaos With an Airtable-First Architecture

The Core Decision

CSV files are not a scalable system for:

• Hundreds of product variations
• Multiple collaborators
• Ongoing updates

We replaced CSVs with Airtable as the source of truth.

Why Airtable

• Friendly for junior employees
• Enforces structure (selects, links, constraints)
• Easy collaboration
• API-first
• Auditable change history

Final Architecture

WooCommerce is no longer where products are managed—only where they are published.

4. Build a Deterministic Product Normalization Layer

Pharmacy catalogs are inherently messy:

• Compound names
• Strengths embedded in titles
• Inconsistent forms
• Duplicate products

We solved this with a normalization layer.

Core Files Created

normalizer.py

Responsible for:

• Parsing compound names
• Removing strength from parent titles
• Normalizing forms (cream, capsule, solution, etc.)
• Extracting:
  • mg/g
  • IU
  • percentages
• Generating:
  • Canonical product titles
  • SEO-safe slugs
  • Stable product keys

catalog_rules.yml

Configuration-driven rules for:

• Form mappings
• Size standards
• Pricing logic
• SEO formatting
• Product grouping behavior

Key Benefit:
Business logic lives in configuration, not hard-coded scripts.

5. Export the Existing Catalog Into Airtable (Cleanly)

Export Script

export-products-for-airtable-ready.py

What It Did

• Exported 691 WooCommerce variations
• Generated two clean CSVs:
  • 332 parent products
  • 691 variations

What Was Generated Automatically

• Canonical parent titles
  Example:
  Anastrozole — Capsule

• SEO-safe slugs
• Product grouping keys
• Variation strength keys
• Clear import instructions

This transformed an unmanageable catalog into a structured dataset ready for Airtable.

6. Clean, Merge, and De-Duplicate the Catalog

The Problem

The catalog contained:

• Strength embedded in parent titles
• Multiple parents for the same compound/form
• Orphaned variations

The Solution

Title Standardization

Scripts removed strength from parent titles:

• Before: Anastrozole 0.15 mg — Capsule
• After: Anastrozole — Capsule

Duplicate Merging

Script: merge-duplicate-parents-airtable.py

• Identified 279 duplicate parent products
• Merged variations under canonical parents
• Preserved pricing and attributes

7. Link Variations to Parents Reliably

Variation linking is where most migrations fail.

Strategy Used

We implemented multi-layer matching logic:

• Exact parent key match
• Compound + form match
• Base compound extraction
• Partial fallback matching

Scripts Created

• link-airtable-records.py
• fix-product-titles-and-variants.py
• link-orphaned-variations.py
• create-parent-from-orphaned.py

Final Outcome

• All 691 variations correctly linked
• 8 true orphans identified and resolved
• Parent/variation structure fully normalized

8. Enrich the Catalog With Clinical Data

Ingredients & Directions Extraction

Script: extract-ingredients-from-woocommerce.py

Process

• Parsed WooCommerce product IDs
• Fetched descriptions via API
• Extracted:
  • Active Ingredients
  • Inactive Ingredients
  • Prescriber Directions
  • Updated Airtable variations

Result

• 689 / 691 variations populated (99.7%)

9. Strength, Percentage & Size Population (Ongoing)

We attempted automated population of:

• strength_mg_per_g
• strength_percentages
• size

Why This Is Hard

• Multiple formatting conventions
• CSV mismatches
• Airtable select constraints

Improvements Implemented

• Base compound extraction
• Strength normalization
• Size inference from parent keys
• WooCommerce meta-data fallback

Script: fill-all-variation-fields-comprehensive.py

This is now a repeatable, incremental process, not a one-off script.

10. Validate Sync Integrity

Comparison Script

compare-woocommerce-airtable.py

• Confirms every WooCommerce product exists in Airtable
• Handles Cloudflare protection
• Prevents silent drift

Rule:
If you can’t verify sync integrity, you don’t have a system—you have hope.

11. Set Up Airtable Access for the Team

• Airtable base configured as shared workspace
• API access enabled
• Tokens stored securely (never in repos)
• Junior employees can manage products safely without touching code

This decouples operations from engineering, which is critical for scale.

12. Final Results

What’s Complete

• 332 parent products
• 691 variations
• Clean titles and slugs
• SEO fields generated
• URLs populated
• Ingredients & directions populated for 99.7%
• Duplicate parents eliminated
• Orphaned variations resolved

What’s Next

• Finish filling strength percentages and size fields
• Lock Airtable field constraints
• Automate scheduled syncs

Closing Thought

This wasn’t “data cleanup.”

It was a system redesign:

• Airtable became the operational brain
• WooCommerce became a rendering layer
• Python scripts became deterministic translators
• Humans stopped fighting the catalog

If your product catalog feels fragile, slow, or impossible to delegate—this is the model that fixes it.