TEST: End-to-End Flow Verificatie

Overzicht

╔══════════════════════════════════════════════════════════════════════╗
║                                                                      ║
║   ████████╗███████╗███████╗████████╗                                 ║
║   ╚══██╔══╝██╔════╝██╔════╝╚══██╔══╝                                ║
║      ██║   █████╗  ███████╗   ██║                                    ║
║      ██║   ██╔══╝  ╚════██║   ██║                                    ║
║      ██║   ███████╗███████║   ██║                                    ║
║      ╚═╝   ╚══════╝╚══════╝   ╚═╝                                    ║
║                                                                      ║
║   E N D - T O - E N D   F L O W   V E R I F I C A T I E            ║
║                                                                      ║
║   Connector → APLT → Medusa → Tenant                                ║
║   Inclusief stock updates & data-integriteit                         ║
║                                                                      ║
║   Datum: Maart 2026                                                  ║
╚══════════════════════════════════════════════════════════════════════╝

Data Flow Overzicht

┌─────────────┐     ┌──────────────┐     ┌─────────────┐     ┌──────────────┐
│  Supplier   │────▶│    APLT      │────▶│   Medusa    │────▶│   Tenant     │
│  Connectors │     │   Tabellen   │     │   Sync      │     │   Database   │
└─────────────┘     └──────────────┘     └─────────────┘     └──────────────┘
   Spranz (01)       aplt_products       Product upsert       magic_b2b_*
   XD Connect (04)   aplt_techniques     Variant mapping      Sales channels
   PF Concept (05)   aplt_stock          Tiered pricing       Stock levels
   Mid Ocean (06)    aplt_tech_pricing   Image handling
   Toppoint (07)     aplt_categories     Metadata sync
   NewWave (09)

---

Fase 1: Connectors → APLT

Verifieer dat de PIM-update connectors correct data doorstromen naar de APLT-tabellen.

1.1 Connector Sync Status Controleren

1. Overzicht van alle connectors ophalen

   Controleer de huidige sync status van alle connectors via de PIM admin API:

   # Via PIM Admin API
   GET /admin/connectors/sync

   Of direct via de database:

   SELECT connector_id, last_sync_at
   FROM aplt_connector_sync
   ORDER BY last_sync_at DESC;

2. Per connector de laatste sync tijd verifiëren

   Connector	Verwachte frequentie	Check
   spranz_stock	2x per dag (06:00, 18:00)	last_sync_at < 12 uur geleden
   xdconnect_products	Dagelijks	last_sync_at < 24 uur geleden
   xdconnect_stock	Elk 15 minuten	last_sync_at < 30 min geleden
   pfconcept_products	Dagelijks	last_sync_at < 24 uur geleden
   pfconcept_stock	2x per dag	last_sync_at < 12 uur geleden
   midocean_products	Dagelijks	last_sync_at < 24 uur geleden
   midocean_stock	Elk uur	last_sync_at < 2 uur geleden
   toppoint_products	Dagelijks (04:15)	last_sync_at < 24 uur geleden
   toppoint_stock	Elk uur	last_sync_at < 2 uur geleden

3. Handmatige sync triggeren voor elke connector

   # Trigger sync per connector
   POST /admin/connectors/sync?connector=spranz_stock
   POST /admin/connectors/sync?connector=xdconnect_products
   POST /admin/connectors/sync?connector=pfconcept_products
   POST /admin/connectors/sync?connector=midocean_products
   POST /admin/connectors/sync?connector=toppoint_products

   Gebruik ?force=true om cache te bypassen en ?limit=50 voor een test-batch.

1.2 APLT Data Integriteit Controleren

1. Product telling per supplier

   SELECT supplier_code, COUNT(*) as product_count,
          COUNT(DISTINCT CASE WHEN name_nl IS NOT NULL THEN sku END) as with_nl_name,
          COUNT(DISTINCT CASE WHEN name_en IS NOT NULL THEN sku END) as with_en_name,
          MIN(updated_at) as oldest_update,
          MAX(updated_at) as newest_update
   FROM aplt_products
   GROUP BY supplier_code
   ORDER BY supplier_code;

2. Ontbrekende verplichte velden detecteren

   -- Producten zonder naam
   SELECT supplier_code, sku, variant_code
   FROM aplt_products
   WHERE name_nl IS NULL OR name_nl = ''
   LIMIT 20;
   
   -- Producten zonder prijs
   SELECT supplier_code, sku, variant_code
   FROM aplt_products
   WHERE retail_price_100 IS NULL OR retail_price_100 = 0
   LIMIT 20;
   
   -- Producten zonder afbeelding
   SELECT supplier_code, sku, COUNT(*)
   FROM aplt_products
   WHERE image_1 IS NULL OR image_1 = ''
   GROUP BY supplier_code, sku
   LIMIT 20;

3. Printtechnieken controleren

   -- Technieken per supplier
   SELECT p.supplier_code, COUNT(DISTINCT t.technique_code) as technique_count,
          COUNT(DISTINCT t.product_sku) as products_with_techniques
   FROM aplt_techniques t
   JOIN aplt_products p ON t.product_sku = p.variant_code
   GROUP BY p.supplier_code;
   
   -- Technieken zonder pricing
   SELECT t.technique_code, t.technique_name, t.product_sku
   FROM aplt_techniques t
   LEFT JOIN aplt_technique_pricing tp ON t.technique_code = tp.technique_code
   WHERE tp.technique_code IS NULL
   LIMIT 20;
   
   -- Ontbrekende posities
   SELECT technique_code, technique_name, product_sku
   FROM aplt_techniques
   WHERE position IS NULL OR position = ''
   LIMIT 20;

4. Data-anomalieën opsporen

   -- Negatieve prijzen
   SELECT sku, supplier_code, retail_price_100
   FROM aplt_products
   WHERE retail_price_100 < 0;
   
   -- Extreem hoge prijzen (>1000 EUR)
   SELECT sku, supplier_code, retail_price_100
   FROM aplt_products
   WHERE retail_price_100 > 100000
   LIMIT 20;
   
   -- Dubbele SKU's binnen zelfde supplier
   SELECT supplier_code, variant_code, COUNT(*) as cnt
   FROM aplt_products
   GROUP BY supplier_code, variant_code
   HAVING COUNT(*) > 1;

Let op: Supplier-specifieke SKU logica

Elke supplier heeft een eigen logica voor base SKU vs variant grouping:

• Spranz (01): 101-05.001 → base=101-05, variant=101-05.001 (split op punt)
• XD Connect (04): P110.023 → base=P110, variant=P110.023 (split op punt)
• PF Concept (05): 10016500 → base=10016 (laatste 3 cijfers verwijderen)
• Mid Ocean (06): AR1804-03 → base=AR1804 (split op koppelteken)
• Toppoint (07): LT26203-N0001 → base=LT26203 (split op koppelteken)

---

Fase 1b: Vertalingen Toevoegen

Voor alle distributeurs behalve Langenberg (03) en Spranz (01) moeten volledige vertalingen worden toegevoegd (DE → NL + EN).

Scope

Distributeur	Code	Vertalingen
XD Connect	04	Alle vertalingen (NL + EN)
PF Concept	05	Alle vertalingen (NL + EN)
Mid Ocean	06	Alle vertalingen (NL + EN)
Toppoint	07	Alle vertalingen (NL + EN)
NewWave	09	Alle vertalingen (NL + EN)
Langenberg	03	Uitgesloten
Spranz	01	Uitgesloten

Te vertalen velden

1. Productnamen en beschrijvingen

   • name_nl, name_en (bron: name_de of Bezeichnung)
   • description_nl, description_en (bron: description_de)

2. Materiaal en kleur

   • color_nl, color_en (bron: color_de)
   • material_nl, material_en (bron: material_de)

3. Printtechniek posities

   Standaard vertalingen:

   Duits (bron)	Nederlands	Engels
   seitlich	zijkant	lateral
   vorne	voorzijde	front
   Ruckseite	achterzijde	back
   oben	bovenzijde	top
   unten	onderzijde	bottom

4. Techniek namen

   Duits (bron)	Nederlands	Engels
   Tampon-Druck	Tampondruk	Pad printing
   Siebdruck	Zeefdruk	Screen printing
   Lasergravur	Lasergravure	Laser engraving
   Digitaldruck	Digitale druk	Digital printing
   Stickerei	Borduurwerk	Embroidery

5. Verificatie na vertaling

   -- Controleer ontbrekende vertalingen per supplier
   SELECT supplier_code,
          COUNT(*) as total,
          COUNT(CASE WHEN name_nl IS NULL OR name_nl = '' THEN 1 END) as missing_nl,
          COUNT(CASE WHEN name_en IS NULL OR name_en = '' THEN 1 END) as missing_en
   FROM aplt_products
   WHERE supplier_code NOT IN ('01', '03')
   GROUP BY supplier_code;
   
   -- Positie-vertalingen controleren
   SELECT DISTINCT position, position_nl
   FROM aplt_techniques t
   JOIN aplt_products p ON t.product_sku = p.variant_code
   WHERE p.supplier_code NOT IN ('01', '03')
     AND (position_nl IS NULL OR position_nl = '');

Langenberg & Spranz uitgesloten

Langenberg (03) en Spranz (01) worden overgeslagen voor vertalingen. Deze suppliers hebben ofwel al vertalingen of worden apart afgehandeld.

---

Fase 2: APLT → Medusa Sync

Verifieer dat APLT-data correct gesynchroniseerd wordt naar Medusa.

2.1 Medusa Sync Triggeren en Monitoren

1. Sync starten per supplier

   # Start sync voor een specifieke supplier (bv. PF Concept)
   POST /admin/connectors/medusa-sync?supplier=05&limit=50

2. Sync status monitoren

   GET /admin/connectors/medusa-sync
   # Response: { status, processed, inserted, updated, errors }

3. Verifieer product-aanmaak in Medusa

   Na sync, controleer of producten correct zijn aangemaakt:

   -- In magic_pim database (Medusa)
   SELECT p.id, p.title, p.handle, p.metadata->>'aplt_sku' as aplt_sku,
          p.metadata->>'supplier_code' as supplier,
          p.metadata->>'synced_at' as synced_at
   FROM product p
   WHERE p.metadata->>'supplier_code' IS NOT NULL
   ORDER BY p.metadata->>'synced_at' DESC
   LIMIT 20;

2.2 Variant Mapping Controleren

1. Varianten per product controleren

   SELECT p.title, p.metadata->>'aplt_sku' as base_sku,
          COUNT(pv.id) as variant_count,
          p.metadata->>'variant_count' as expected_variants
   FROM product p
   JOIN product_variant pv ON pv.product_id = p.id
   WHERE p.metadata->>'supplier_code' IS NOT NULL
   GROUP BY p.id, p.title, p.metadata->>'aplt_sku', p.metadata->>'variant_count'
   HAVING COUNT(pv.id) != CAST(p.metadata->>'variant_count' AS int)
   LIMIT 20;

2. Prijzen verifiëren (tiered pricing)

   Medusa slaat tiered pricing op als:

   Tier	min_quantity	max_quantity
   price_100	1	199
   price_200	200	299
   price_300	300	499
   price_500	500	999
   price_1000	1000	NULL

   -- Controleer of alle tiers aanwezig zijn
   SELECT pv.sku, COUNT(pp.id) as price_tiers
   FROM product_variant pv
   JOIN product_variant_price_set pvps ON pvps.variant_id = pv.id
   JOIN price_set ps ON ps.id = pvps.price_set_id
   JOIN price pp ON pp.price_set_id = ps.id
   WHERE pp.currency_code = 'eur'
   GROUP BY pv.sku
   HAVING COUNT(pp.id) != 5
   LIMIT 20;

3. Technieken in metadata controleren

   -- Producten met technieken in metadata
   SELECT p.title, p.metadata->>'aplt_sku' as sku,
          jsonb_array_length(p.metadata->'available_techniques') as technique_count
   FROM product p
   WHERE p.metadata->'available_techniques' IS NOT NULL
   ORDER BY jsonb_array_length(p.metadata->'available_techniques') DESC
   LIMIT 20;
   
   -- Producten ZONDER technieken die ze wel zouden moeten hebben
   SELECT p.metadata->>'aplt_sku' as sku, p.metadata->>'supplier_code' as supplier
   FROM product p
   WHERE p.metadata->>'supplier_code' IS NOT NULL
     AND (p.metadata->'available_techniques' IS NULL
          OR jsonb_array_length(p.metadata->'available_techniques') = 0)
   LIMIT 20;

4. Afbeeldingen verifiëren

   SELECT p.title, p.thumbnail,
          (SELECT COUNT(*) FROM image i
           JOIN product_image pi ON pi.image_id = i.id
           WHERE pi.product_id = p.id) as image_count
   FROM product p
   WHERE p.metadata->>'supplier_code' IS NOT NULL
     AND p.thumbnail IS NULL
   LIMIT 20;

Sync Hash Controle

Elke gesyncte product heeft een sync_hash in metadata. Bij her-sync worden alleen producten met gewijzigde hash bijgewerkt. Als data niet doorkomt, controleer of de hash correct berekend wordt.

---

Fase 3: Tenant Update

Verifieer de volledige tenant update flow — wijzigingen in PIM/APLT moeten correct doorstromen naar de juiste tenant.

3.1 Beschikbare Tenants

Tenant	Database	Opmerkingen
magic_brinxx	magic_b2b_brinxx	Primaire test-tenant
magic_brandbutler	magic_b2b_brandbutler
magic_demo	magic_b2b_demo	Demo omgeving
magic_jodasign	magic_b2b_jodasign
magic_logohorloge	magic_b2b_logohorloge
magic_bovisales	magic_b2b_bovisales
magic_desluis	magic_b2b_desluis
magic_default	magic_b2b_default	Default template

3.2 Tenant Update Uitvoeren

1. Sales channels ophalen voor de tenant

   -- In tenant database (bv. magic_b2b_brinxx)
   SELECT id, name, description
   FROM sales_channel
   WHERE deleted_at IS NULL;

2. Tenant update triggeren

   POST /admin/tenant-update
   {
     "tenant": "magic_brinxx",
     "salesChannel": "<sales_channel_id>",
     "suppliers": ["01", "04", "05", "06", "07"]
   }

3. Sync volgorde verifiëren

   De tenant update moet in de juiste volgorde uitvoeren:

   1. Colors (FK dependency)
   2. Products (gefilterd op supplier codes)
   3. Techniques (gelinkt via variant_code)
   4. Technique pricing
   5. Images (met rank ordering)
   6. Category linking (via product_category_product)

4. Data in tenant database controleren

   -- In tenant database
   -- Producten per supplier
   SELECT supplier_code, COUNT(*) as count
   FROM aplt_products
   GROUP BY supplier_code;
   
   -- Technieken aanwezig
   SELECT COUNT(*) FROM aplt_techniques;
   
   -- Pricing aanwezig
   SELECT COUNT(*) FROM aplt_technique_pricing;
   
   -- Controleer product-sales channel koppeling
   SELECT sc.name, COUNT(psc.product_id) as product_count
   FROM sales_channel sc
   LEFT JOIN product_sales_channel psc ON psc.sales_channel_id = sc.id
   GROUP BY sc.name;

5. Vergelijk bron en doel

   -- Tel producten in PIM vs tenant (per supplier)
   -- PIM (magic_pim):
   SELECT supplier_code, COUNT(*) FROM aplt_products
   WHERE supplier_code IN ('01','04','05','06','07')
   GROUP BY supplier_code;
   
   -- Tenant (magic_b2b_brinxx):
   SELECT supplier_code, COUNT(*) FROM aplt_products
   WHERE supplier_code IN ('01','04','05','06','07')
   GROUP BY supplier_code;
   
   -- Aantallen moeten overeenkomen!

3.3 Category Sync Testen

1. Categorie sync apart triggeren

   POST /admin/tenant-update?action=categories
   {
     "tenant": "magic_brinxx"
   }

2. Controleer categorie-koppeling

   -- In tenant database
   SELECT pc.name, COUNT(pcp.product_id) as linked_products
   FROM product_category pc
   LEFT JOIN product_category_product pcp ON pcp.product_category_id = pc.id
   GROUP BY pc.name
   ORDER BY linked_products DESC;

---

Fase 4: Tenant Stock Update

Verifieer dat voorraadmutaties vanuit connectors correct verwerkt worden in APLT en doorstromen naar tenants.

4.1 APLT Stock Tabel Controleren

1. Stock records per supplier

   SELECT supplier_code,
          COUNT(*) as total_records,
          COUNT(CASE WHEN quantity_available > 0 THEN 1 END) as in_stock,
          COUNT(CASE WHEN quantity_available = 0 THEN 1 END) as out_of_stock,
          MIN(last_updated) as oldest_update,
          MAX(last_updated) as newest_update,
          AVG(quantity_available) as avg_stock
   FROM aplt_stock
   GROUP BY supplier_code
   ORDER BY supplier_code;

2. Verouderde stock records identificeren

   -- Records die langer dan 24 uur niet bijgewerkt zijn
   SELECT supplier_code, product_sku, quantity_available,
          last_updated,
          NOW() - last_updated as age
   FROM aplt_stock
   WHERE last_updated < NOW() - INTERVAL '24 hours'
   ORDER BY last_updated ASC
   LIMIT 20;

3. Stock sync triggeren per connector

   POST /admin/connectors/sync?connector=spranz_stock&force=true
   POST /admin/connectors/sync?connector=xdconnect_stock&force=true
   POST /admin/connectors/sync?connector=pfconcept_stock&force=true
   POST /admin/connectors/sync?connector=midocean_stock&force=true
   POST /admin/connectors/sync?connector=toppoint_stock&force=true

4. Verifieer upsert werking

   -- Controleer dat ON CONFLICT correct werkt
   -- Neem een bekend product en vergelijk voor/na sync
   SELECT product_sku, quantity_available, quantity_reserved,
          expected_delivery_date, expected_quantity, status, last_updated
   FROM aplt_stock
   WHERE supplier_code = '05' -- PF Concept
   ORDER BY last_updated DESC
   LIMIT 10;

4.2 Stock Doorstroom naar Tenant

1. Vergelijk stock in PIM vs Tenant

   -- PIM (magic_pim)
   SELECT supplier_code, product_sku, quantity_available, last_updated
   FROM aplt_stock
   WHERE supplier_code = '05'
   ORDER BY product_sku
   LIMIT 10;
   
   -- Tenant (magic_b2b_brinxx) - zelfde query
   SELECT supplier_code, product_sku, quantity_available, last_updated
   FROM aplt_stock
   WHERE supplier_code = '05'
   ORDER BY product_sku
   LIMIT 10;

2. Afwijkingen detecteren

   Als de tenant een aplt_stock tabel heeft, vergelijk de aantallen:

   -- In PIM
   SELECT supplier_code, COUNT(*) as pim_count,
          SUM(quantity_available) as pim_total_stock
   FROM aplt_stock GROUP BY supplier_code;
   
   -- In Tenant
   SELECT supplier_code, COUNT(*) as tenant_count,
          SUM(quantity_available) as tenant_total_stock
   FROM aplt_stock GROUP BY supplier_code;

3. Controleer Medusa inventory levels

   -- Medusa inventory tracking (in tenant db)
   SELECT il.id, ii.sku, il.stocked_quantity, il.reserved_quantity,
          il.incoming_quantity
   FROM inventory_level il
   JOIN inventory_item ii ON ii.id = il.inventory_item_id
   ORDER BY il.stocked_quantity DESC
   LIMIT 20;

Stock Update Patroon

Stock updates gebruiken een ON CONFLICT (supplier_code, product_sku) DO UPDATE patroon. Dit zorgt ervoor dat bestaande records bijgewerkt worden en nieuwe records worden aangemaakt. De last_updated timestamp wordt altijd op NOW() gezet bij een upsert.

---

Acceptatiecriteria Checklist

Fase 1: Connectors → APLT

• Alle connectors draaien op schema
• aplt_products bevat data van alle actieve suppliers
• Geen ontbrekende verplichte velden (naam, prijs)
• Printtechnieken correct gekoppeld
• Technique pricing compleet
• Geen data-anomalieën (negatieve prijzen, duplicaten)

Fase 2: APLT → Medusa

• Producten correct aangemaakt in Medusa
• Varianten juist gegroupeerd per base SKU
• Tiered pricing (5 tiers) aanwezig per variant
• Technieken in product metadata
• Afbeeldingen gelinkt en thumbnails gezet
• Sync hash wordt correct bijgewerkt

Fase 3: Tenant Update

• Colors sync succesvol (FK dependency)
• Producten per supplier correct in tenant
• Aantallen PIM vs Tenant komen overeen
• Technieken en pricing doorgestroomd
• Sales channel koppeling correct
• Categorieën gelinkt aan producten

Fase 4: Stock Update

• aplt_stock tabel actueel (< 24 uur)
• Stock sync per connector werkt
• ON CONFLICT upsert werkt correct
• Stock doorgestroomd naar tenant
• Medusa inventory levels kloppen
• Geen verweesde stock records

---

Relevante Bestanden

Doel	Pad
Connector Index	/mnt/data/magic_pim/backend/src/modules/connectors/index.ts
Database Operations	/mnt/data/magic_pim/backend/src/modules/connectors/database.ts
Connector API	/mnt/data/magic_pim/backend/src/api/admin/connectors/sync/route.ts
Medusa Sync API	/mnt/data/magic_pim/backend/src/api/admin/connectors/medusa-sync/route.ts
Tenant Update API	/mnt/data/magic_pim/backend/src/api/admin/tenant-update/route.ts
APLT Products API	/mnt/data/magic_pim/backend/src/api/admin/aplt/products/route.ts
Sync State	/mnt/data/magic_pim/backend/src/modules/connectors/sync-state.ts
Master Sync Script	/mnt/data/magic_pim/sync-master.js

---

Eindconclusie

Het gehele proces van Connector → APLT → Medusa → Tenant moet end-to-end werken zonder handmatige tussenkomst. Na het doorlopen van alle vier fasen moet elk acceptatiecriterium afgevinkt zijn. Eventuele fouten worden gedocumenteerd met de specifieke query-resultaten en foutmeldingen voor verdere analyse.