2026-10-07_core-product-database-buildup

Wine2You — Core Build Plan: The Product Database (PIM) as the Central Piece

Date: 2026-10-07 (prepared 2026-10-06) Status: PLAN ONLY — no build started Author: Hermes (from the Google Notebook: Wine2You — 31 sources, all ready) Sources: Wine2You Architecture (PaaS, Medusa v2 & Temporal) · Wine2You E-Commerce Tech Stack Architecture · API Schema and Taxonomy Research · Programmatic MedusaJS Instance Initialization · Avelyn core concept specification · order-flow-schematic · Wine2You Identity & API-Key Architecture (deployed) Related: docs/plans/identity-auth-architecture-deployed.md, infra/identity/, docs/diagrams/w2y-identity-architecture.png

0. One-paragraph thesis

Wine2You's core is not a storefront — it is the Product Database: a single, aggregated, multi-supplier wine catalogue where each wine carries both commercial facts (who owns it, where it physically sits, what price, how much stock) and descriptive/compliance facts (vintage, ABV, terroir, HS/TARIC code, certifications). The chosen split is Payload CMS v3 = master Wine PIM (rich, technical, compliance, storytelling data) + Medusa v2 = commerce/inventory/pricing ledger (stock, prices, channels, orders), joined by a single medusaProductId back-reference on the Payload wines document. Everything else in the E2E flow — estate onboarding, price lists, curation into shops, flash-trade orders, landed-cost, excise/VAT — is a consumer of this one data model. So the model is mapped first, in one place, before any flow code is written.

1. What "the core" is (scope discipline)

Rule: the Product Database is the only place a wine's facts live. Every other system holds a reference (medusaProductId, sku, entity_id) plus derived/transactional state. If a fact exists in two places, Payload wins for descriptive data, Medusa wins for commercial data.

2. The canonical Product Database model

2.1 Payload CMS wines collection (master PIM)

Source: apps/payload/src/collections/Wines.ts. Field names verbatim:

Plus supporting collections: estates (the supplier entity), media (DAM), CuratedShops (curator config, markup, layout — the tenant record).

2.2 The Wine ↔ Medusa link (one field, one direction of truth)

• wines.medusaProductId → the Medusa v2 Product (and via its variants, the ProductVariant / SKU).
• Writes are two-way coordinated but atomically owned by the API Gateway — the estate never talks to two systems. A single payload hits POST /api/v1/estate/inventory, and a Medusa v2 workflow (create-estate-product-workflow) splits it atomically:
  1. descriptive/compliance → Payload CMS (wines collection, pim_metadata),
  2. commercial → Medusa (Product, ProductVariant, InventoryItem, StockLocation, PriceList = P_estate).
• Failure rule: if either leg fails, the whole workflow compensates (Medusa saga). Never a Payload-only or Medusa-only orphan. Add a nightly reconciliation report (wines without medusaProductId, Medusa products without a Payload wine) — this is the PIM's integrity monitor.

2.3 Commercial model in Medusa v2

2.4 Distributed inventory & multi-supplier catalogue

• Stock fields carried: on_hand_quantity, allocated_quantity, reserved_quantity, available_to_promise = on_hand − allocated − reserved.
• Multi-supplier: each estate = its own StockLocation with owner_entity_id; inventory items are network-shareable via is_network_shareable (a seller can opt to let others sell its stock → cross-seller dropshipping). Warehouses/importers act as fulfilment nodes.
• Location taxonomy (LocationDataSchema): location_id, gln (GS1), location_type (BONDED_WAREHOUSE, DUTY_PAID_WAREHOUSE, RETAIL_STORE, WINERY_ESTATE, 3PL_FULFILLMENT_CENTER, CONSIGNMENT_CELLAR, IN_TRANSIT_CONTAINER), address, geo_coordinates{lat,long}, tax_jurisdiction{country,state,excise_tax_zone_id,eu_seed_number,…}, capabilities{supports_d2c_fulfillment,supports_b2b_wholesale,supports_bonded_transfer,temperature_controlled}.
• Tax/duty taxonomy (TaxDutiesContextSchema): hs_tariff_code, country_of_origin, duty_status (DUTY_PAID|IN_BOND|DUTY_FREE|EXCISE_SUSPENDED|PRE_ARRIVAL), volumetric_metrics{volume_liters,net_weight_kg,gross_weight_kg,abv_percentage,liters_pure_alcohol} (LPA = V × ABV/100), tax_classification{sales_tax_category_code,vat_rate_type,excise_tax_category}, cross_border_terms{incoterm,import_duty_rate_percent,preferential_trade_agreement_code}, packaging_and_environmental_levies{container_deposit_fee,epr_recycling_category_code}.

Design note: these two schemas (LocationDataSchema, TaxDutiesContextSchema) are the API contract (OpenAPI 3.2), not the DB. Store them as explicit DTOs in packages/types and map to Payload/Medusa tables in application code — this is what lets us refactor storage without breaking partner integrations.

3. Mapping the End-to-End Flow against PIM features

The 7-stage lifecycle (Avelyn core concept + order-flow-schematic) vs. what the PIM must supply at each step:

Landed cost (the pricing brain):

Landed = P_estate + D_customs + D_excise(ABV, V)
       + VAT_dest × (P_estate + D_customs + D_excise) + Freight + Fees

Implemented once in packages/duty-engine (eu-excise.ts, eu-vat-oss.ts, norway-ddp.ts) and called from Medusa workflows (compute-landed-cost.ts) — never duplicated per storefront.

API contracts the core exposes/consumes:

• GET /v1/inventory/network-snapshot — node capability + coords + stock + duty context (the read side of the Product DB across the network).
• POST /v1/orders/quote-calculate — landed cost + financial_breakdown + settlement_split.

4. Tooling scorecard — chosen PIM vs. alternatives

Payload vs. "just use Medusa's product module"? The notebook is explicit: Payload is the optimal PIM because the wine schema (grain blends, appellations, TARIC, awards, bilingual storytelling, DAM) is far richer than commerce product fields, and Payload's draft/published workflow + versioning give a proper publish gate for catalogue changes. Medusa stays the fast query layer (SKU/price/stock); Payload stays the rich layer.

5. Proposed build sequence (for approval — nothing started)

Phase A — Schema & contracts (the "core" proper). ~1 increment

1. packages/types: author the OpenAPI 3.2 / JSON-Schema contracts for Product, ProductVariant, InventoryItem, LocationDataSchema, TaxDutiesContextSchema, plus GET /v1/inventory/network-snapshot + POST /v1/orders/quote-calculate DTOs.
2. Payload: stand up apps/payload, implement Wines.ts, Estates.ts, Media.ts, CuratedShops.ts with the field names above; enable drafts/versioning.
3. Medusa: define the mirrored product/inventory/price/channel schema; declare the medusaProductId link contract.
4. DB migrations as code (Atlas or Flyway/Liquibase) + expand–contract policy; schema-registry policy for async events.

Phase B — Ingest (single front door). ~1 increment 5. create-estate-product-workflow: one endpoint, atomic split to Payload + Medusa. 6. Three intake paths: Merchant Dashboard (UI), Unified API key (w2y_estate_…), Bulk CSV/Excel importer — all funnel to the one endpoint. 7. Estates can never see two systems / two keys.

Phase C — Commercial wiring. ~1 increment 8. PriceList = P_estate per estate; SalesChannel per curator shop; publishable API keys per channel. 9. duty-engine package + compute-landed-cost.ts; unit-tested tax matrices (EU excise, OSS VAT, Norway DDP).

Phase D — Read & trade paths. ~1 increment 10. Search sync (Medusa → Meilisearch/Typesense) with faceted filters (region, vintage, cert, price). 11. Next.js multi-tenant storefront + curator Puck editing + [tenant] routing via SalesChannel. 12. flash-trade.ts back-to-back order workflow; then Temporal macro-workflows (onboarding, escrow, filings).

Phase E — Integrity & ops (runs across all). 13. Nightly PIM↔Medusa reconciliation report; event schema registry; contract tests in CI.

Sequencing rationale: the model and its contracts are the core — B–D are consumers. Getting Phase A's field names and the medusaProductId join right is what makes curation, flash-trade and compliance cheap later.

6. Hosting (already decided, for context)

Temporal server and Meilisearch will need a host decision at Phase D (candidate: contabo1, or scale Oracle).

7. Open decisions for Kris

1. Payload vs. Medusa-only product module — confirm Payload as master PIM (rec: yes, per notebook).
2. Build sequence — approve Phase A first (schema/contracts) before any flow code (rec: yes).
3. Temporal + Meilisearch host — contabo1 now vs. defer to Phase D (rec: decide at Phase D).
4. Migration tool — Atlas vs. Flyway/Liquibase for schema-as-code (rec: pick one in Phase A).

8. AMENDMENT — Account primitives change (2026-10-07, from Kris)

Change: ZITADEL is now a shared, multi-org instance — Wine2You is a virtual organisation (org) inside it, not a dedicated per-project ZITADEL instance. Per-tenant/per-project isolation is expressed as ZITADEL organisations on one instance, not separate instances.

Consequence for the core build: the "Identity/API keys" spine in §1/§3/§6 now multi-tenants at the org layer. Every entity that maps to a "customer" or "integrator" resolves to a ZITADEL org; OpenFGA relationship tuples must carry the org/tenant scope, and API keys stay entity-scoped (Unkey), gated per org membership.

Live state observed while amending (must be reconciled before wiring):

• Live shared instance = contabo1 /opt/compose/zitadel, zitadel-api v4.19.4 at tailnet 100.64.0.8:18090 (+ zitadel-login :18091, zitadel-db-contabo1 pg17 :15433). ZITADEL_DOMAIN=auth.digid.co; first org digid; project orgs (zipcheck, zipid, wine2you, lubit) created as additional orgs after first-instance.
• auth.tail.digid.co → 100.64.0.8 (contabo1). No ZITADEL container on hetzner1.
• Old w2y-identity stack (ZITADEL v2.71.0 :18080 + OpenFGA :18081) is STALE — the deployed w2y-user-mgmt module still points at it and is therefore broken by the change.
• ⚠️ Open conflict: Kris states the instance "moved to hetzner1"; observed live it has not (still contabo1, DNS still → contabo1, nothing on hetzner1). The zitadel compose itself requires the DB DSN to stay latency-local. Hold module re-point until the host is confirmed.

Pending action (blocked on host confirmation): re-point w2y-user-mgmt from the stale v2.71.0 :18080 instance to the v4.19.4 :18090 instance, targeting the Wine2You org; re-verify the OpenFGA-gated key E2E live; retire the stale w2y-identity stack once clear. §8 supersedes the host/identity rows in §6 for the ZITADEL line.