PIM Audit & Open-Source Strategy

Executive Summary

This document provides a comprehensive audit of the Magic e-VERSE product data architecture across all 10+ brands and 11 tenants, identifies pain points, and evaluates whether adopting an open-source PIM system would be a strategic improvement over the current custom-built APLT/Medusa PIM platform.

Tip

TL;DR — The current Magic PIM is significantly more capable than a typical custom build: 50+ database tables, 20+ admin UI pages, 7 live supplier connectors, AI-powered product management (Chloe), 13-step tenant sync, 90+ utility scripts, and a full B2B quotation-to-invoice pipeline. Replacing it wholesale with an open-source PIM would destroy more value than it creates. The strategic recommendation is to keep and harden the current system, while selectively adopting Directus or AtroPIM only if specific gaps (DAM, enrichment workflows, completeness scoring) cannot be addressed incrementally.

---

Part 1: Current State — What We Actually Have

Platform Stack

Layer	Technology
Backend	Node.js + Express.js (TypeScript) on Medusa 2.13.1
Database	PostgreSQL 16 (43 databases across tenants, agents, services)
Frontend	React 18 + Next.js 15 (Tailwind CSS)
Commerce	Medusa 2.0 (11 tenants, 42 Docker containers)
AI	Claude API (Chloe product assistant + Claude Vision image analysis)
Orchestration	n8n Cloud (3 instances) + webhook server
PIM	Custom APLT module on Medusa 2.0 with dedicated admin UI
Deployment	Docker + PM2, Nginx reverse proxy

Multi-Tenant Architecture

Magic PIM (Master)                     pim.magiceverse.online
    |                                  DB: magic_pim (PostgreSQL)
    |                                  Admin: magic-pimadmin.magiceverse.online
    |
    +-- 13-Step Tenant Sync ──────────────────────────────────+
    |                                                          |
    v                                                          v
+---------------------------+          +---------------------------+
| Tenant: Brinxx            |          | Tenant: Spranz            |
| Agent DB: magic_agent_    |          | Agent DB: magic_agent_    |
|           brinxx          |          |           spranz          |
| Commerce DB: magic_b2b_   |          | Commerce DB: magic_b2b_   |
|              brinxx       |          |              spranz       |
| Storefront: Next.js       |          | Storefront: Next.js       |
| Backend port: 40XX        |          | Backend port: 40XX        |
| Storefront port: 100XX    |          | Storefront port: 100XX    |
+---------------------------+          +---------------------------+
    ... + logohorloge, langenberg, bovisales, desluis,
        jodasign, demo, default, development, master_magic

11 Tenants: brinxx, logohorloge, spranz, bovisales, desluis, jodasign, demo, default, development, brandbutler, master_magic

Product Data Scale

Supplier	Code	Products	Feed Format	Update Frequency
Spranz	01	760	CSV (stock feed)	2x daily (06:00, 18:00)
Moxz	02	15	—	Manual
Langenberg	03	244	CSV	Manual
XD Connect	04	6,158	Excel	Hourly (products), Daily (prices), 15min (stock)
PF Concept	05	17,780	JSON	Daily at 05:00 (products), 2x daily (stock)
Midocean	06	264	API (JSON)	Hourly (stock)
Toppoint	07	239	XML (AWS S3)	Daily at 04:15 (products), Hourly (stock)
NewWave Textiles	09	26,239	Excel + ZIP	Weekly
Total		~51,700 products

Database Architecture (50+ APLT Tables)

The APLT module is far more than a product catalog. It is a full B2B ERP layer on top of Medusa:

Product Catalog (8 tables):

• aplt_products — 100+ columns, multilingual (NL/DE/EN), 5-tier pricing, 5 image slots, physical properties, trade data, print flags
• aplt_techniques — 1,116+ records, print/decoration methods with 5 position coordinates, dimensions, max colors, pricing
• aplt_technique_pricing — Cost and retail pricing per technique across 7 quantity tiers (100-5000+)
• aplt_categories + aplt_category_mappings + aplt_category_keyword_rules — Hierarchical categories with auto-categorization by keyword
• aplt_stock — Real-time inventory per supplier with expected delivery dates
• aplt_suppliers — Supplier master data

Documents (10 tables):

• aplt_quotation_headers / aplt_quotation_lines — Full B2B quotation management
• aplt_order_headers / aplt_order_lines — Order lifecycle from quotation conversion
• aplt_invoice_headers / aplt_invoice_lines / aplt_invoice_vat_summary — Invoice generation with VAT
• aplt_credit_notes — Credit memo management
• aplt_document_relations — Links quotation to order to invoice
• aplt_number_sequences — Thread-safe document numbering (Q/O/F/C/P prefixes)

CRM and Commerce (8 tables):

• aplt_customers — B2B customers with company info, addresses
• aplt_discount_groups — Customer discount tiers
• aplt_payment_terms / aplt_vat_codes — Commercial terms
• aplt_payments / aplt_payment_allocations — Payment tracking
• aplt_ledger_accounts — Chart of accounts
• aplt_access_requests — Customer access workflow

CMS and Branding (8 tables):

• aplt_cms_settings — Per-brand configuration (company info, defaults)
• aplt_cms_pages / aplt_cms_menus / aplt_cms_menu_items — Content management
• aplt_cms_footer_sections / aplt_cms_footer_links — Footer content
• aplt_cms_featured_products / aplt_cms_popular_categories — Homepage curation
• aplt_brands — Brand master data

System (7 tables):

• aplt_audit_log — Audit trail for all operations
• aplt_connector_sync — Sync status tracking
• aplt_master_update — Tenant push logging with timestamps
• aplt_migrations / aplt_configurations — System management

3D/Design (3 tables):

• aplt_3d_products_config / aplt_3d_config — 3D viewer configurations with mesh data (JSONB)

Admin UI — 20+ Custom Pages

The PIM has a complete custom admin interface built on Medusa Admin SDK that entirely replaces the default Medusa UI:

SALES Section:

• Quotations — Full CRUD with document numbering, line items, PDF generation
• Orders — Order management with quotation conversion, shipping, status tracking (97.6KB of UI code)
• Invoices — PDF + EML generation with VAT breakdown
• Customers — B2B customer management with discount group assignment
• Credit Notes — Credit memo management
• Payments — Payment tracking and allocation
• Reports — Business analytics
• Subscriptions — Subscription management

STIJL Section (Content and Branding):

• CMS Editor — Multi-language page content management
• Brand Wizard — Brand configuration per tenant
• CMS Modules — Featured products, popular categories, project carousels
• Page Manager — WYSIWYG editor (GrapesJS drag-and-drop)
• Menu Manager — Hierarchical navigation in NL/DE/EN

ADVANCED Section (Products and System):

• APLT Products — Product catalog with Chloe AI assistant (natural language commands like “Copy 376-00 and make it 10 euros more expensive”)
• Connectors — Real-time sync management with progress tracking
• Category Manager — Hierarchical categories with keyword auto-mapping
• Technique Pricing — 7-tier pricing management for print techniques
• Translations — Multi-language storefront text (NL/DE/FR/EN, 8 categories)
• Sales Channel Products — Link products to Medusa sales channels
• Suppliers — Supplier configuration
• Product Copy — Product cloning tool

Medusa Product Widgets (3):

• Product APLT Tab — Shows variant pricing, techniques, materials on Medusa product page
• Product Supplier Info — Supplier badge, brand, categories, technique count
• Product Supplier Filter — Dropdown filter for product lists

Supplier Connectors (7 Live + 16 Total)

Each connector handles:

• Data extraction from supplier APIs/feeds (CSV, JSON, Excel, XML, S3)
• Standardized transformation into APLT ProductData schema
• Multilingual field mapping (NL, DE, EN per supplier)
• Tiered pricing extraction (retail + buying prices across 5 quantity breaks)
• Image URL mapping with supplier-specific path patterns
• Stock level tracking with expected delivery dates
• Print technique extraction with position coordinates

Key implementation details:

• Feed caching (1-6 hour TTL per feed type)
• Rate limiting (50-item batches, 200ms delays)
• Abort signal support for cancellation
• Duplicate prevention via sync time tracking
• PF Concept: Malformed JSON auto-fix via Python subprocess

Product Sync Pipeline

The sync operates in two phases:

Phase 1: APLT to Medusa (Master DB)

Supplier Feeds --> Connectors --> aplt_products (magic_pim)
                                       |
                                       v
                              Medusa Sync (POST /admin/connectors/medusa-sync)
                                       |
                                  Hash-based change detection (MD5)
                                  Supplier-specific SKU grouping
                                  Variant creation with tiered pricing
                                  Image attachment
                                  Technique metadata in product.metadata
                                       |
                                       v
                              Medusa product + product_variant tables

Phase 2: Master to Tenant (13-Step Distribution)

POST /admin/tenant-update {tenant, salesChannel, suppliers[]}

Step 1:  Sync aplt_colors (FK dependency)
Step 2:  Sync aplt_products (filtered by supplier codes)
Step 3:  Sync aplt_techniques (linked by variant_code)
Step 4:  Sync Medusa products (3-tier conflict resolution: active/soft-deleted/ID)
Step 5:  Sync product_category
Step 6:  Create product-sales-channel links
Step 7:  Sync product_option (color/size)
Step 8:  Sync product_option_value
Step 9:  Sync product_variant with metadata (packaging, MOQ, multilingual names)
Step 10: Sync product images
Step 11: Create variant-image links (image_1/2/3 from APLT)
Step 12: Create variant-option links
Step 13: Trigger remote sync + category conversion on tenant

Logged to: aplt_master_update (tenant_name, counts, timestamps)

Local Tools Inventory (90+ Scripts)

Category	Count	Examples
Product sync/migration	20+	sync-master.js, sync-aplt-to-medusa-v4.js, migrate-brinxx-to-pim.js
Product import	15+	import-langenberg.js, import-csv-with-mapping.js, import-technique-pricing.js
Data validation	10+	check-missing-products.js, compare-products.js, check-product-images.js
Image processing	10+	download_midocean_images.py, convert_xdconnects.py, create_xdconnect_symlinks.py
SVG tools	6+	center_svg_images.py, center_and_scale_svg.py
AI analysis	1	analyze-product-images.js (Claude Vision, populates physical characteristics)
Nightly automation	2	nightly-sync.sh (1:00 AM daily), update_magiceverse_stats.sh
Customer import	1	import-customers-brinxx.js
n8n workflows	4+	ai-quotation-node.json, brinxx-quotation-request-flow.json
Database tools	15+	convert-mysql-to-pg.py, verify-tables-exist.js, run-migrations.js
Image server	1	image-server.js (Express on port 3333, serves product images)

AI Integration (Already Built)

Chloe Product Assistant (/api/admin/aplt/chloe-product/route.ts):

• Natural language commands for product operations via Claude API
• Functions: getProductBySku(), generateNewSku(), createProduct(), updateProductPrices(), addTechnique(), copyProduct()

Claude Vision Analysis (analyze-product-images.js):

• Batch-analyzes product images using claude-haiku-4-5-20251001
• Extracts: primary/secondary colors, material, shape, size category, surface finish, features (lid, handle, strap, transparency, logo area)
• Populates product_physical_characteristics table with AI confidence scores
• Supports --limit, --reanalyze, --sku flags

AI Agent Product Search (per-brand):

• ProductSearch class with parseProductSyntax(), parseOrderSyntax(), parseDiscountRequestSyntax()
• Product view tracking for analytics
• AI-formatted product cards for chat responses

---

Part 2: Pain Points (Revised)

Caution

The original audit identified 18 pain points. After deep-diving the actual codebase, some are confirmed, some were inaccurate, and new ones emerged.

Confirmed Critical Issues

#	Issue	Impact	Verified Location
1	Hardcoded database credentials in 8+ source files	Security vulnerability	medusa-sync/route.ts, conversion/route.ts, tenant-update/route.ts, menu-manager/route.ts, cms-modules/route.ts, security-groups/route.ts, page-manager/route.ts, license-manager/route.ts
2	1,500+ failed PF Concept imports	Missing products in catalog	/mnt/data/magic_pim/missing-products.csv (1,501 rows with malformed data)
3	No input validation library (no Zod/Joi)	Data quality risk — manual null checks only	Entire PIM backend
4	New DB connection per API request	Resource exhaustion — new Client({...}) in 41 route files	All admin/store route.ts files

Confirmed High Issues

#	Issue	Impact	Verified Location
5	N+1 subquery for technique count in product listing	Performance at scale	aplt/products/route.ts
6	ILIKE with leading wildcards in product search	Cannot use B-tree indexes	product-search.ts (AI agents), aplt/products/route.ts (PIM admin)
7	Max 2 products per AI agent search	Limits AI product discovery	product-search.ts:22 (hardcoded)
8	Multiple sync script versions (v1-v4)	No clear canonical sync process	/mnt/data/magic_pim/sync-aplt-to-medusa-v*.js
9	Statistics query on every product list request	Unnecessary DB load	aplt/products/route.ts

Corrected Assessments

#	Original Claim	Reality
”No sync retry logic”	The medusa-sync does have state tracking (idle/running/completed/error/cancelled), abort support, and hash-based incremental updates. However, there is no automatic retry on failure — manual re-trigger required.
”Batch sync without rollback”	The tenant sync uses per-entity conflict resolution (3-tier: active/soft-deleted/ID check) and ON CONFLICT handling. It is not fully transactional, but it is more robust than initially described.
”No audit trail”	aplt_audit_log table exists and tracks operations. aplt_master_update logs all tenant syncs with timestamps and counts.
”No DAM”	While not a full DAM, the system has: image serving via Nginx (30d cache) + backend API, RemBG background removal service (port 5050), SVG generation pipeline, and per-supplier image directories at /mnt/data/pim_data/.
”No enrichment workflow”	The Chloe AI assistant provides natural language product enrichment. Claude Vision auto-populates physical characteristics. No formal approval workflow exists, but enrichment is partially automated.

Newly Discovered Issues

#	Issue	Impact
10	Hardcoded admin password supersecret in medusa-sync authentication	Security — anyone who reads the code can authenticate
11	Silent table-not-found errors swallowed in product detail route	Debugging impossible — catch { // Table may not exist, ignore }
12	PF Concept JSON fix depends on external Python subprocess with 5-min timeout	Fragile — if Python fails, falls back to raw malformed JSON
13	No cache invalidation on product sync — Redis TTL is 5 minutes	Stale data served for up to 5 minutes after sync completes
14	Image URL hardcoded to https://magic-pimadmin.magiceverse.online/pim_data/ during tenant sync	Cannot change domain without code change

---

Part 3: What the Current PIM Already Does Well

Before evaluating replacements, it is critical to recognize what would be lost:

Capability	Current Implementation	Replacement Effort
7 live supplier connectors with format-specific parsing (CSV, JSON, Excel, XML, S3)	Fully built and tested per supplier	4-8 weeks per connector to rebuild
Supplier-specific SKU grouping (different logic per supplier)	Hardcoded but working	Must be replicated exactly
13-step tenant distribution with conflict resolution	Proven in production	Months to rebuild
50+ APLT tables with full B2B ERP (quotation-to-invoice)	Complete pipeline	Cannot be replaced by a PIM
20+ admin UI pages with custom Medusa branding	Production-ready	Would need to be rebuilt from scratch
Chloe AI product assistant	Working Claude integration	Not available in any PIM
Claude Vision image analysis	Populates physical characteristics	Not available in any PIM
Thread-safe document numbering (Q/O/F/C/P)	Atomic increment with row locking	Must be preserved
Nightly automated sync	Cron-based, running daily at 01:00	Must be migrated
3 Medusa product widgets	Extend Medusa admin with APLT data	Would break without APLT
n8n workflow integration	4+ workflow files for quotation automation	Must be preserved
90+ utility scripts	Battle-tested data tools	Institutional knowledge

Danger

Replacing the current PIM with Directus, AtroPIM, or any other system would require rebuilding ALL of the above. No open-source PIM provides supplier connectors for Spranz, PF Concept, XD Connect, Midocean, Toppoint, or NewWave. No PIM provides a B2B quotation-to-invoice pipeline. No PIM has Claude AI integration. The migration cost is not weeks — it is 6-12 months of development with high risk of business disruption.

---

Part 4: Open-Source PIM Evaluation (Revised)

Revised Evaluation Criteria

Given the depth of the current system, criteria must account for migration risk and what already exists:

Criterion	Weight	Reasoning
Preserves existing capabilities	30%	Cannot lose 50+ tables, 20+ pages, 7 connectors
Stack alignment (Node.js/TS/PostgreSQL)	20%	Second tech stack is costly for small team
Fills actual gaps (DAM, completeness, workflows)	20%	Only adopt if it solves real unmet needs
Migration risk	15%	Business continuity during transition
Community and viability	10%	Long-term maintenance
Licensing	5%	Must be commercially usable

---

Option 1: Directus (as Complementary Layer)

Directus

Role: Complementary DAM + enrichment layer alongside current PIM (NOT a replacement) License: BSL 1.1 (free under $5M revenue) Stack: Node.js + TypeScript + Vue.js + PostgreSQL URL: https://directus.io

Use Case

Directus makes sense as a product enrichment and DAM layer that sits alongside the current APLT system:

• DAM — Manage product images with on-the-fly transformations, metadata, versioning. Replace the current file-path-in-database approach.
• Translation workflows — Side-by-side multilingual editing for product descriptions with Crowdin integration.
• Enrichment UI — Non-technical product managers can enrich descriptions, add marketing copy, manage SEO metadata through a polished admin UI.
• Approval workflows — Directus Flows for Draft to Review to Published stages before syncing to APLT.

It does NOT replace: supplier connectors, tenant sync, quotation pipeline, technique pricing, APLT admin UI, AI integration.

Architecture

Supplier Feeds --> Current APLT Connectors --> aplt_products (master data)
                                                    |
                                                    v
                                               Directus (enrichment)
                                               - DAM (product images)
                                               - Translations (NL/DE/EN/FR)
                                               - Marketing copy
                                               - SEO metadata
                                                    |
                                                    v
                                               Enriched data synced back
                                               to aplt_products via Flows
                                                    |
                                                    v
                                               Existing tenant sync
                                               (unchanged)

Fit Score (Revised)

Criterion	Score	Notes
Preserves existing	9/10	Complementary, nothing destroyed
Stack alignment	10/10	Node.js + TypeScript + PostgreSQL
Fills actual gaps	7/10	DAM + workflows, but no completeness scoring
Migration risk	9/10	Additive only, can run in parallel
Community	9/10	30K+ stars, monthly releases
Licensing	7/10	BSL — verify revenue threshold
Weighted Total	8.7/10	As complementary layer

Cost: Self-hosted free (if under $5M). Integration: ~2-3 weeks to connect to APLT via API.

---

Option 2: AtroPIM (as Complementary Layer)

AtroPIM

Role: Purpose-built PIM for data quality + completeness alongside current APLT License: GPLv3 (core free), premium modules paid Stack: PHP + Vue.js + PostgreSQL URL: https://www.atropim.com/en

Use Case

AtroPIM makes sense if you need purpose-built PIM features the current system lacks:

• Completeness scoring per locale (NL/DE/EN/FR) — know exactly which products need attention (paid module)
• Product variant inheritance — parent products automatically pass attributes to variants
• Supplier portal — let suppliers update their own product data
• Industry standards — ETIM, BMEcat, ECLASS, GS1 support for supply chain
• Import/Export feeds with scheduled execution and error logs

Trade-off: introduces PHP as a second runtime alongside Node.js/TypeScript.

Fit Score (Revised)

Criterion	Score	Notes
Preserves existing	8/10	Complementary, but adds PHP stack complexity
Stack alignment	5/10	PostgreSQL yes, PHP runtime no
Fills actual gaps	9/10	Completeness, supplier portal, standards
Migration risk	7/10	Additive, but PHP ops overhead
Community	4/10	185 stars, single company
Licensing	7/10	GPLv3 core free, modules ~EUR 200-500/mo
Weighted Total	6.9/10	As complementary layer

---

Option 3: Pimcore

Pimcore

Verdict: NOT recommended. PHP + MySQL only (no PostgreSQL), massive complexity, EUR 20-80K implementation cost. Would require running an entirely separate infrastructure stack. Only consider if you are prepared to make PIM your primary platform and rebuild commerce around it.

Criterion	Score	Notes
Preserves existing	2/10	Would require near-complete rebuild
Stack alignment	2/10	PHP + MySQL, full mismatch
Fills actual gaps	9/10	Best feature set available
Migration risk	2/10	6-12 month migration, high disruption risk
Weighted Total	3.5/10

---

Option 4: Akeneo CE / LibrePIM

Akeneo CE / LibrePIM

Verdict: NOT recommended. CE officially abandoned by Akeneo (no releases since 2023). LibrePIM fork (v8.0.0, Dec 2025) is active but small community. PHP + MySQL only. Support for PIM v7 ends September 30, 2026.

Criterion	Score	Notes
Preserves existing	2/10	Would require near-complete rebuild
Stack alignment	2/10	PHP + MySQL
Fills actual gaps	7/10	Good PIM features, but CE limited
Migration risk	2/10	Abandoned CE is high risk
Weighted Total	3.0/10

---

Option 5: Ergonode

Ergonode

Verdict: Interesting (PostgreSQL native, REST + GraphQL + Event API) but community too small (107 stars) to bet on. PHP backend still required.

Criterion	Score	Notes
Preserves existing	3/10	Would need significant rebuilding
Stack alignment	5/10	PostgreSQL native, but PHP
Fills actual gaps	6/10	Modern APIs, basic PIM features
Migration risk	3/10	Tiny community is risky
Weighted Total	3.8/10

---

Option 6: Harden Current System

Harden Current APLT PIM

Role: Fix the 14 identified pain points incrementally without disrupting production Stack: Node.js + TypeScript + PostgreSQL (no changes) Cost: Targeted fixes, not a full rewrite

Fix Plan

Quick wins (1-2 days each):

1. Move all hardcoded credentials to environment variables
2. Remove hardcoded supersecret admin password
3. Add pg connection pooling (replace new Client() with shared pool)
4. Remove stats subquery from product listing (cache or compute separately)
5. Increase AI agent search limit from 2 to configurable value
6. Add Redis cache invalidation on product sync completion

Medium effort (1-2 weeks each): 7. Add Zod validation on all product API inputs 8. Add PostgreSQL GIN trigram index for product search (replace ILIKE) 9. Consolidate sync scripts into canonical version (retire v1-v3) 10. Add retry logic to sync state manager (exponential backoff) 11. Re-import 1,501 failed PF Concept products with data cleaning

Larger effort (2-4 weeks each): 12. Add product completeness scoring (per locale, per channel) 13. Add product change history / version tracking 14. Build proper DAM layer (image metadata, versioning, transformations)

Fit Score

Criterion	Score	Notes
Preserves existing	10/10	Everything preserved and improved
Stack alignment	10/10	No changes to stack
Fills actual gaps	6/10	Fixes pain points but DAM/workflows remain basic
Migration risk	10/10	Zero migration — incremental improvements
Community	7/10	Medusa community + own team
Licensing	10/10	MIT, fully owned
Weighted Total	8.8/10

Cost: Quick wins: ~1-2 weeks total. Medium fixes: ~4-6 weeks. Larger efforts: ~6-10 weeks. Total: approximately 3-4 months of focused development, far less than any PIM migration.

---

Part 5: Comparison Matrix (Revised)

	Harden Current	Directus (complement)	AtroPIM (complement)	Pimcore (replace)	Akeneo (replace)
Preserves 50+ tables	10	9	8	2	2
Preserves 20+ admin pages	10	9	8	2	2
Preserves 7 connectors	10	10	10	3	3
Preserves AI integration	10	10	10	0	0
Adds DAM	3	8	8	10	5
Adds completeness scoring	6	4	9	9	7
Adds approval workflows	3	7	7	9	5
Stack alignment	10	10	5	2	2
Migration risk	none	low	low	extreme	extreme
Time to value	weeks	weeks	weeks	6-12 months	6-12 months
Weighted Score	8.8	8.7	6.9	3.5	3.0

---

Part 6: Strategic Recommendation

Verdict: Harden First, Complement Later

Recommended Strategy

Phase 1: Harden the current APLT PIM (3-4 months) — Fix the 14 pain points, especially security, connection pooling, search indexing, and validation. This preserves all existing value and eliminates the most damaging issues.

Phase 2: Evaluate Directus as a complementary DAM/enrichment layer (after Phase 1) — Only if the team genuinely needs structured translation workflows, image DAM, or approval processes that cannot be built incrementally in APLT.

Do NOT replace the current system wholesale. The custom APLT PIM represents 12+ months of domain-specific development for the promotional products industry. No off-the-shelf PIM understands print techniques, decoration positions, tiered B2B pricing, or the Spranz/PF Concept/XD Connect/Midocean/Toppoint/NewWave supplier ecosystem.

Implementation Roadmap

1. Phase 1: Security and Stability (Week 1-2)

   • Move all hardcoded credentials to .env (8+ files)
   • Replace new Client() with connection pooling (41 instances)
   • Remove supersecret hardcoded auth
   • Add Redis cache invalidation on sync

2. Phase 2: Data Quality (Week 3-6)

   • Add Zod validation on all product API inputs
   • Add PostgreSQL pg_trgm GIN index for product search
   • Consolidate sync scripts to single canonical version
   • Re-import 1,501 failed PF Concept products
   • Add error classification (retry-able vs permanent)

3. Phase 3: Observability (Week 7-8)

   • Add sync retry with exponential backoff
   • Add structured logging (replace console.error)
   • Add product completeness scoring (per locale)
   • Add product change history tracking

4. Phase 4: Evaluate DAM Needs (Week 9-12)

   • Assess if current image serving (Nginx + backend API + RemBG) is sufficient
   • If not: spin up Directus as DAM layer connected to APLT via PostgreSQL
   • POC with 100 products from one supplier
   • Evaluate translation workflow needs

What NOT to Do

• Do NOT rip and replace — The current system has too much domain-specific value
• Do NOT adopt Pimcore or Akeneo — PHP + MySQL stack mismatch, and you would lose everything custom
• Do NOT build “PIM from scratch” — You already have one. Improve it.
• Do NOT add Directus before fixing fundamentals — Security and connection pooling come first
• Do NOT ignore the pain points — They will compound as the catalog grows past 50K products

---

Appendix A: Files Referenced

PIM Backend

File	Relevance
/mnt/data/magic_pim/backend/src/api/admin/aplt/products/route.ts	Product listing, N+1 query, stats query
/mnt/data/magic_pim/backend/src/api/admin/aplt/products/[sku]/route.ts	Product detail, supplier folder mapping, silent errors
/mnt/data/magic_pim/backend/src/api/admin/connectors/medusa-sync/route.ts	Sync logic, hash detection, pricing tiers, credentials
/mnt/data/magic_pim/backend/src/api/admin/tenant-update/route.ts	13-step tenant distribution (1,532 lines)
/mnt/data/magic_pim/backend/src/api/admin/aplt/conversion/route.ts	Category conversion with backup and hierarchy
/mnt/data/magic_pim/backend/src/modules/connectors/	All supplier connector implementations
/mnt/data/magic_pim/backend/src/modules/connectors/database.ts	upsertProducts, upsertStock
/mnt/data/magic_pim/backend/src/modules/connectors/sync-state.ts	Sync progress tracking with abort support
/mnt/data/magic_pim/backend/src/utils/redis-cache.ts	5-min TTL, silent failures
/mnt/data/magic_pim/backend/src/admin/routes/	20+ admin UI page components
/mnt/data/magic_pim/backend/src/admin/widgets/	3 Medusa product widgets

APLT Commerce Module (Development Template)

File	Relevance
/mnt/data/magic_omniverse/magic_commerce/magic_development/backend/src/api/admin/aplt/	40+ admin API endpoints
/mnt/data/magic_omniverse/magic_commerce/magic_development/backend/src/api/store/aplt/	6 store API endpoints
/mnt/data/magic_omniverse/magic_commerce/magic_development/backend/src/api/admin/aplt/chloe-product/route.ts	AI product assistant
/mnt/data/magic_omniverse/magic_commerce/magic_development/backend/src/admin/routes/	28 admin page components
/mnt/data/magic_omniverse/magic_commerce/magic_development/backend/medusa-config.ts	Custom branding, menu replacement

AI Agent (Per-Brand)

File	Relevance
/home/adminwayne/magic_agent/agents/{brand}/src/lib/product-search.ts	ILIKE queries, max 2 results
/home/adminwayne/magic_agent/agents/brinxx/scripts/analyze-product-images.js	Claude Vision image analysis

Utility Scripts

File	Relevance
/mnt/data/magic_pim/sync-master.js	Master sync (standalone)
/mnt/data/magic_pim/sync-aplt-to-medusa-v4.js	Latest sync version
/mnt/data/magic_pim/missing-products.csv	1,501 failed imports
/mnt/data/pim_data/scripts/download_midocean_images.py	Image download + compression
/mnt/data/pim_data/svg/convert_xdconnects.py	SVG base64 conversion
/home/adminwayne/scripts/nightly-sync.sh	Daily automated sync (01:00)

---

Last updated: March 11, 2026 Analysis performed by: Claude Code (Opus 4.6) — Deep codebase exploration across 6 parallel agents