orion/app/modules/marketplace/docs/data-model.md

Marketplace Data Model

Entity relationships and database schema for the marketplace module.

Entity Relationship Diagram

┌──────────────────────────┐
│        Store             │ (from tenancy module)
└──────┬───────────────────┘
       │ 1
       │
  ┌────┴──────────────────────────────────────────────┐
  │                    │                │              │
  ▼ 1                  ▼ *              ▼ 0..1         ▼ *
┌──────────────┐  ┌──────────────┐  ┌───────────────┐ ┌──────────────┐
│ Marketplace  │  │ Marketplace  │  │ StoreLetzshop │ │ Letzshop     │
│ ImportJob    │  │ Product      │  │ Credentials   │ │ Order        │
│              │  │              │  │               │ │              │
│ source_url   │  │ gtin, mpn    │  │ api_key_enc   │ │ letzshop_id  │
│ language     │  │ sku, brand   │  │ auto_sync     │ │ sync_status  │
│ status       │  │ price_cents  │  │ last_sync_at  │ │ customer     │
│ imported_cnt │  │ is_digital   │  │ default_      │ │ total_amount │
│ error_count  │  │ marketplace  │  │ carrier       │ │ tracking     │
└──────┬───────┘  └──────┬───────┘  └───────────────┘ └──────┬───────┘
       │                 │                                     │
       ▼ *               ▼ *                                   │
┌──────────────┐  ┌──────────────────┐                        │
│ Marketplace  │  │ Marketplace      │                        │
│ ImportError  │  │ Product          │                        │
│              │  │ Translation      │                        │
│ row_number   │  │                  │                        │
│ error_type   │  │ language         │                        │
│ error_msg    │  │ title            │                        │
│ row_data     │  │ description      │                        │
└──────────────┘  │ meta_title       │                        │
                  └──────────────────┘                        │
                                                              │
  ┌───────────────────────────────────────────────────────────┘
  │
  ▼ *                    ▼ *                    ▼ *
┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐
│ Letzshop         │  │ Letzshop         │  │ Letzshop         │
│ FulfillmentQueue │  │ SyncLog          │  │ Historical       │
│                  │  │                  │  │ ImportJob        │
│ operation        │  │ operation_type   │  │                  │
│ payload          │  │ direction        │  │ current_phase    │
│ status           │  │ status           │  │ current_page     │
│ attempts         │  │ records_*        │  │ orders_processed │
└──────────────────┘  └──────────────────┘  └──────────────────┘

┌──────────────────┐  ┌──────────────────┐
│ Letzshop         │  │ Store            │
│ StoreCache       │  │ Onboarding       │
│                  │  │                  │
│ letzshop_id      │  │ status           │
│ name, slug       │  │ current_step     │
│ claimed_by_      │  │ step_*_completed │
│ store_id         │  │ skipped_by_admin │
└──────────────────┘  └──────────────────┘

Models

Product Import Pipeline

MarketplaceProduct

Canonical product data imported from marketplace sources. Serves as a staging area — stores publish selected products to their own catalog.

Field	Type	Description
marketplace_product_id	String (unique)	Unique ID from marketplace
marketplace	String	Source marketplace (e.g., "Letzshop")
gtin	String	EAN/UPC barcode
mpn	String	Manufacturer Part Number
sku	String	Merchant's internal SKU
store_name	String	Store name from marketplace
source_url	String	CSV source URL
product_type_enum	Enum	physical, digital, service, subscription
is_digital	Boolean	Whether product is digital
digital_delivery_method	Enum	download, email, in_app, streaming, license_key
brand	String	Brand name
google_product_category	String	Google Shopping category
condition	String	new, used, refurbished
price_cents	Integer	Price in cents
sale_price_cents	Integer	Sale price in cents
currency	String	Currency code (default EUR)
tax_rate_percent	Decimal	VAT rate (default 17%)
image_link	String	Main product image
additional_images	JSON	Array of additional image URLs
attributes	JSON	Flexible attributes
weight_grams	Integer	Product weight
is_active	Boolean	Whether product is active

Relationships: translations (MarketplaceProductTranslation), store_products (Product)

MarketplaceProductTranslation

Localized content for marketplace products.

Field	Type	Description
marketplace_product_id	FK	Parent product
language	String	Language code (en, fr, de, lb)
title	String	Product title
description	Text	Full description
short_description	Text	Short description
meta_title	String	SEO title
meta_description	String	SEO description
url_slug	String	URL-friendly slug
source_import_id	Integer	Import job that created this
source_file	String	Source CSV file

Constraint: Unique (marketplace_product_id, language)

MarketplaceImportJob

Tracks CSV product import jobs with progress and metrics.

Field	Type	Description
store_id	FK	Target store
user_id	FK	Who triggered the import
marketplace	String	Source marketplace (default "Letzshop")
source_url	String	CSV feed URL
language	String	Import language for translations
status	String	pending, processing, completed, failed, completed_with_errors
imported_count	Integer	New products imported
updated_count	Integer	Existing products updated
error_count	Integer	Failed rows
total_processed	Integer	Total rows processed
error_message	Text	Error message if failed
celery_task_id	String	Background task ID
started_at	DateTime	Processing start time
completed_at	DateTime	Processing end time

Relationships: store, user, errors (MarketplaceImportError, cascade delete)

MarketplaceImportError

Detailed error records for individual import failures.

Field	Type	Description
import_job_id	FK	Parent import job (cascade delete)
row_number	Integer	Row in source CSV
identifier	String	Product identifier (ID, GTIN, etc.)
error_type	String	missing_title, missing_id, parse_error, validation_error
error_message	String	Human-readable error
row_data	JSON	Snapshot of key fields from failing row

Letzshop Order Integration

StoreLetzshopCredentials

Encrypted API credentials and sync settings per store.

Field	Type	Description
store_id	FK (unique)	One credential set per store
api_key_encrypted	String	Fernet-encrypted API key
api_endpoint	String	GraphQL endpoint URL
auto_sync_enabled	Boolean	Enable automatic order sync
sync_interval_minutes	Integer	Auto-sync interval (5-1440)
test_mode_enabled	Boolean	Test mode flag
default_carrier	String	Default shipping carrier
carrier_*_label_url	String	Per-carrier label URL prefixes
last_sync_at	DateTime	Last sync timestamp
last_sync_status	String	success, failed, partial
last_sync_error	Text	Error message if failed

LetzshopOrder

Tracks orders imported from Letzshop marketplace.

Field	Type	Description
store_id	FK	Store that owns this order
letzshop_order_id	String	Letzshop order GID
letzshop_shipment_id	String	Letzshop shipment GID
letzshop_order_number	String	Human-readable order number
external_order_number	String	Customer-facing reference
shipment_number	String	Carrier shipment number
local_order_id	FK	Linked local order (if any)
letzshop_state	String	Current Letzshop state
customer_email	String	Customer email
customer_name	String	Customer name
customer_locale	String	Customer language for invoicing
total_amount	String	Order total
currency	String	Currency code
raw_order_data	JSON	Full order data from Letzshop
inventory_units	JSON	List of inventory units
sync_status	String	pending, confirmed, rejected, shipped
shipping_carrier	String	Carrier code
tracking_number	String	Tracking number
tracking_url	String	Full tracking URL
shipping_country_iso	String	Shipping country
billing_country_iso	String	Billing country
order_date	DateTime	Original order date from Letzshop

LetzshopFulfillmentQueue

Outbound operation queue with retry logic for Letzshop fulfillment.

Field	Type	Description
store_id	FK	Store
order_id	FK	Linked order
operation	String	confirm_item, decline_item, set_tracking
payload	JSON	Operation data
status	String	pending, processing, completed, failed
attempts	Integer	Retry count
max_attempts	Integer	Max retries (default 3)
error_message	Text	Last error
response_data	JSON	Response from Letzshop

LetzshopSyncLog

Audit trail for all Letzshop sync operations.

Field	Type	Description
store_id	FK	Store
operation_type	String	order_import, confirm_inventory, set_tracking, etc.
direction	String	inbound, outbound
status	String	success, failed, partial
records_processed	Integer	Total records
records_succeeded	Integer	Successful records
records_failed	Integer	Failed records
error_details	JSON	Detailed error info
started_at	DateTime	Operation start
completed_at	DateTime	Operation end
duration_seconds	Integer	Total duration
triggered_by	String	user_id, scheduler, webhook

LetzshopHistoricalImportJob

Tracks progress of historical order imports for real-time progress polling.

Field	Type	Description
store_id	FK	Store
user_id	FK	Who triggered
status	String	pending, fetching, processing, completed, failed
current_phase	String	confirmed, declined
current_page	Integer	Current pagination page
total_pages	Integer	Total pages
shipments_fetched	Integer	Shipments fetched so far
orders_processed	Integer	Orders processed
orders_imported	Integer	New orders imported
orders_updated	Integer	Updated existing orders
orders_skipped	Integer	Duplicate orders skipped
products_matched	Integer	Products matched by EAN
products_not_found	Integer	Products not found
confirmed_stats	JSON	Stats for confirmed phase
declined_stats	JSON	Stats for declined phase
celery_task_id	String	Background task ID

Supporting Models

LetzshopStoreCache

Cache of Letzshop marketplace store directory for browsing and claiming during signup.

Field	Type	Description
letzshop_id	String (unique)	Letzshop store identifier
slug	String	URL slug
name	String	Store name
merchant_name	String	Merchant name
is_active	Boolean	Active on Letzshop
description_en/fr/de	Text	Localized descriptions
email, phone, website	String	Contact info
street, zipcode, city, country_iso	String	Address
latitude, longitude	Float	Geo coordinates
categories	JSON	Category array
social_media_links	JSON	Social links
claimed_by_store_id	FK	Store that claimed this listing
claimed_at	DateTime	When claimed
last_synced_at	DateTime	Last directory sync

StoreOnboarding

Tracks completion of mandatory onboarding steps for new stores.

Field	Type	Description
store_id	FK (unique)	One record per store
status	String	not_started, in_progress, completed, skipped
current_step	Integer	Current onboarding step
Step 1	—	Merchant profile completion
Step 2	—	Letzshop API key setup + verification
Step 3	—	Product import (CSV URL set)
Step 4	—	Order sync (first sync job)
skipped_by_admin	Boolean	Admin override
skipped_reason	Text	Reason for skip