ISSWA CMS

Client: International Salon Supplies

Role: Lead Full-Stack Engineer

Period: Jul 2023 – Present

Status: Production

Tech stack: Next.js, TypeScript, PostgreSQL, RabbitMQ, Node.js, Clerk, DigitalOcean Spaces, Sharp, Gemini, Claude API, OpenAI, MCP, OAuth 2.1, Docker, Retail Express API, WooCommerce, Monday.com, Vitest

What started in 2023 as a tool for adding images and descriptions to ERP products has grown into International Salon Supplies' internal operations platform: 15+ modules, 142 API routes and ~119k lines of TypeScript covering content, purchasing, receiving, labels, customer onboarding and AI-assisted reporting, all built on a live PostgreSQL mirror of the ERP.

The problem

International Salon Supplies (ISS) runs on Retail Express (REX), a retail ERP that is excellent at stock and point-of-sale but was never designed to be a content system. Every product that reached the wholesale website needed images, galleries, attachments and a proper description, and none of that had a good home. In 2023 more than 32,000 rows of legacy website product data needed to be cleaned, deduplicated and rehomed (it came down to ~14,200 real products), and every image needed to be normalised and moved to a CDN.

So I built a CMS. Three years later, that CMS is the place staff go to do almost everything that isn't selling at the counter.

Architecture at a glance

Retail Express ERP ──► RabbitMQ ──► CMS Sync worker ──► PostgreSQL (ERP mirror + CMS data)
                                         │                     ▲
                                         │ business events     │
                                         ▼                     │
                     Omnisend · Google Chat · Mobile app    ISSWA CMS (Next.js, Clerk RBAC)
                                                              ├── DigitalOcean Spaces + CDN
                                                              ├── REX REST & SOAP (write-back)
                                                              ├── WordPress / WooCommerce
                                                              ├── Monday.com · MachShip · Omnisend
                                                              ├── Gemini / Claude / OpenAI
                                                              └── MCP endpoint (OAuth 2.1)

There are two codebases:

• ISSWA CMS – a Next.js App Router application with PostgreSQL (tagged-template SQL via postgres, no ORM), Clerk authentication with a custom role/page permission matrix, and 142 API route handlers.
• CMS Sync – a Node.js worker that keeps a local PostgreSQL mirror of the ERP (products, inventory, orders, customers, purchase orders, packages) so the CMS and reporting never have to hammer the rate-limited ERP API.

What the platform does today

Engineering highlights

1. A draft/live content model with "publish when in stock"

Every content asset has a work-in-progress twin (wip_header_image, wip_gallery_image, …). Drafts upload to a draft prefix in object storage and are promoted to live with a server-side object copy, so a half-edited product never leaks onto the website. Content moves through IN_PROGRESS → READY_FOR_APPROVAL → WAITING_FOR_STOCK → COMPLETED.

The WAITING_FOR_STOCK state was the most valuable. Merchandisers can approve a product before it has arrived. When the sync worker sees that product's inventory move from zero to positive, it calls back into the CMS, which flips the ERP's sales-channel flag and the product goes live on the wholesale site automatically. The callback respects the ERP's rate limit (300 requests/min) and skips variations that are disabled, already live or not marked for web export.

2. From polling to an event-driven mirror

The sync worker has been rewritten twice, and it's the clearest lesson in the project:

1. Jul 2023 – Express + MongoDB poller. Direct ERP polling with modified_since pagination and a resumable INCOMPLETE status. MongoDB was dropped within a week; the reporting use case wanted relational views.
2. Jul 2023 – Nov 2024 – PostgreSQL poller. A per-resource loop with a sync table (lastSync, status, page), pages of 300, and a 30-second overlap window so records modified mid-sync weren't missed.
3. Nov 2024 – today – RabbitMQ consumer. A durable queue consumer with prefetch(1), explicit ack/nack, exponential-backoff reconnects, graceful shutdown, and a generic upsert driven by a declarative table schema (INSERT … ON CONFLICT … DO UPDATE, composite keys like outlet_id, product_id).

Once changes arrived as events, new features became cheap:

• Back-in-stock alerts: inventory 0 → positive fires an Omnisend event to customers who asked to be notified.
• Auto-publish: products waiting on stock go live the moment stock lands.
• New web orders: a push notification to the warehouse mobile app.
• Content team pings: Google Chat messages with @mentions and a deep link when a product family is fully published.

3. AI invoice reconciliation

Supplier invoices are messy PDFs. The reconciliation module sends them to Gemini with forced function calling, so the model can only answer by calling a typed tool. Retries only happen for transport and server errors, not content errors. The model escalates to a stronger tier automatically when needed, and a correction chat lets staff fix edge cases. The prompt makes one rule explicit: the purchase order is the authority for what was ordered, the invoice is the authority for what was billed. The results feed gross-profit calculations, back-order handling and price updates that are written back to the ERP over REST/SOAP and mirrored to Monday.com.

4. Letting LLMs query company data safely

The AI assistant (and an MCP endpoint that lets Claude or ChatGPT connect directly) is protected at several independent layers:

• A dedicated mcp_reader Postgres role with SELECT on 41 curated mcp_* views and nothing else. It does not rely on read-only transaction settings, which a client can override.
• Single-statement SELECT/WITH only, with row caps and statement timeouts.
• A hand-built, stateless MCP Streamable-HTTP endpoint. The SDK's Node-stream transport doesn't fit Next.js's Web Request/Response model, so I implemented the protocol directly.
• A full OAuth 2.1 authorization server: dynamic client registration, mandatory PKCE (S256), rotating refresh tokens with reuse detection, hashed secrets, audience-bound tokens and HMAC-signed consent.
• Model-written Python runs in a sandbox container with no network and no credentials. It has a read-only root filesystem, all Linux capabilities dropped, pid/memory/CPU limits, and exchanges files with the app only through a shared job directory.

5. Replacing n8n with a scheduler that can't fail silently

Report schedules originally lived in n8n, with the CMS creating one workflow per schedule through n8n's API. It worked until a schedule failed for two weeks without anyone noticing. The replacement is deliberately boring:

• A ticker starts from Next.js's instrumentation.ts hook.
• It claims due tasks atomically (UPDATE … WHERE claimed_at IS NULL AND next_run_at <= now()) and reaps stale claims.
• It waits a short delay after a cold boot before the first tick.
• It is off by default, so a developer laptop pointed at production data can never email customers.
• A secret-guarded /tick endpoint lets an external cron drive it if needed.

6. An image pipeline, three generations deep

• Gen 1 (2023): a Sharp script that composited every image onto a fixed 1000×1000 white canvas.
• Gen 2 (2023): a bulk migration of ~14,000 legacy image URLs to compressed WebP, falling back from Jimp to Sharp for formats Jimp couldn't decode.
• Gen 3 (today): trim uniform borders (kept only if the result stays ≥ 50 px), flatten onto white, fit inside the target size without enlarging, output WebP, keep animated GIFs animated, and upload to DigitalOcean Spaces behind a CDN.

Security hardening worth mentioning

• The middleware rejects POST requests to page routes. Next.js 13 routes them to a server-action handler that crashes.
• The database client throws if it's ever bundled into client code.
• A previously enumerable report download route (?id=) was locked down.
• Stored supplier email-account credentials are encrypted at rest.

By the numbers

• ~119k lines of TypeScript/SQL across 572 files
• 585 commits on the CMS and 90 on the sync worker (Jul 2023 → Sep 2026)
• 142 API routes, 35 pages, ~56 tables and 41 views
• 55 Vitest test files, including opt-in integration suites
• 32,760 → 14,187 products in the initial data migration

What I'd do differently

• The sync consumer still builds some SQL with string concatenation and manual escaping. Parameterising it is on the list.
• I'd introduce a lightweight query builder earlier. Tagged-template SQL is great for control, but a codebase this size benefits from typed results.
• Start with events from day one. The polling era worked, but every business automation above only became easy once the ERP's changes arrived as messages.

Takeaway

The most useful thing I did wasn't any single feature. It was building one shared read model of the ERP and one permission model, then adding modules on top. Each new module (purchasing, labels, onboarding, AI reporting) came quickly because the data and the auth were already there.