# πŸ›’ Order Hub β€” Design Doc > **Status:** Design draft Β· **Date:** 2026-06-08 > **Scope decision:** design only β€” no code yet. eBay is planned from the developer account up (Β§6.3). A central place where orders from **Wix, Etsy, and eBay** land in one inbox, resolve to your own products, and roll up into the one question that matters in the lab: > **"Given everything that's unfulfilled, what do I actually need to grow / make, and by when?"** --- ## 1. Goals / Non-goals **Goals** - One `orders` inbox, deduplicated, across all three channels. - A product catalog that maps each platform's listing/SKU β†’ one internal product. - A **production-demand engine**: open orders β†’ "make 10 shiitake blocks + 20 grain bags; net of what's already incubating, start 5 this week by Tuesday." - **Customer intelligence**: dedup buyers across channels for repeat-customer, channel-attribution, and lifetime-value stats β€” for free, out of the orders you're already ingesting. - Fit existing conventions: `node:sqlite` migrations, single-row config tables, `setInterval` jobs, SSE live updates, worker/admin roles, the PWA Orders tab. - A zero-API bridge (CSV import + manual add) so the hub is useful on **day one**, before any channel API is live. **Non-goals (v1)** - **No fresh-mushroom / fresh-produce orders** β€” mail-order side only (grow-kits, spawn, supplies). The `harvest` fulfillment path is designed-for but deferred. - Shipping labels + tracking write-back are **Phase 4, not v1** β€” fully planned in Β§11, but the orderβ†’production core (Phases 0–3) ships first. - Not accounting / invoicing / VAT β€” bookkeeping lives outside Meistertracker; orders stay operational. - Not a replacement for the channels' own storefronts β€” read-mostly ingestion. --- ## 2. Where this fits today Meistertracker is currently **100% production-side**. Confirmed against the schema β€” there is no `orders`, `products`, `customers`, or `sku` concept anywhere. What exists and what we'll connect to: | Existing table | Role in the order hub | | --------------------- | ------------------------------------------------------------------------------- | | `batches` | A "produce" demand line becomes / links to a batch (species, strain, qty, substrate, `due`). | | `recipes` | Substrate formula (`hardwood_pct`/`wheatbran_pct`/`gypsum_pct`/`rh_pct`) a product's block uses. | | `inventory` | Raw-material rollup target (do we have the hardwood/grain to make the blocks?). | | `harvests` | (Deferred) fresh-mushroom lines would allocate against harvest grams β€” not in v1. | | `manual_tasks` | Optional: a demand line can spawn a "start batch X" task with a `due_date`. | | `duckdns_config` / `print_bridge_config` | The **single-row config-table pattern** the channel credentials copy. | | SSE broadcast | New order β†’ live badge on the Orders tab, same channel the app already uses. | | `setInterval` jobs | The order poller is one more job (DuckDNS already polls every 5 min β€” same shape).| --- ## 3. Data model All new tables ship as **migrations v42+** (latest is v41), same style as `db.js` (`node:sqlite`, ISO-8601 `TEXT` timestamps, `INTEGER` booleans, single-row config with `CHECK (id = 1)`). ### 3.1 Channel credentials β€” `sales_channel_config` One row per channel. Mirrors `duckdns_config` (single-row, `enabled` flag, secret columns). ```sql CREATE TABLE IF NOT EXISTS sales_channel_config ( channel TEXT PRIMARY KEY, -- 'wix' | 'etsy' | 'ebay' enabled INTEGER DEFAULT 0, -- generic credential bag; not every field used by every channel api_key TEXT DEFAULT '', -- Wix API key site_id TEXT DEFAULT '', -- Wix site id / Etsy shop id / eBay marketplace client_id TEXT DEFAULT '', -- Etsy keystring / eBay App ID client_secret TEXT DEFAULT '', -- eBay Cert ID, etc. access_token TEXT DEFAULT '', -- OAuth access token (Etsy/eBay) refresh_token TEXT DEFAULT '', -- OAuth refresh token (Etsy/eBay) token_expires TEXT, -- ISO ts; refresh proactively before this webhook_secret TEXT DEFAULT '', -- Wix webhook JWT / signature verification last_sync TEXT, -- watermark: last successful poll last_cursor TEXT, -- updated_min / pagination cursor last_error TEXT, -- surfaced in the admin UI created TEXT NOT NULL ); ``` > **Secrets at rest.** These are higher-value than the DuckDNS token (they read customer PII and > can act on your shops). Two options β€” see Β§10. Recommendation: encrypt `*_token`/`*_secret` > columns with a key derived from an env var, decrypt only in memory. ### 3.2 Product catalog β€” `products` ```sql CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY AUTOINCREMENT, sku TEXT UNIQUE, -- your internal SKU (nullable for ad-hoc) name TEXT NOT NULL, category TEXT, -- 'growkit' | 'spawn' | 'culture' | 'fresh' | 'supply' species TEXT, -- nullable; for grow kits / fresh strain TEXT, active INTEGER DEFAULT 1, notes TEXT DEFAULT '', created TEXT NOT NULL ); ``` ### 3.3 Channel ↔ product mapping β€” `product_channel_map` The linchpin. The same grow kit has a different listing id on each platform, so an order only becomes useful once it resolves to a shared `product_id`. ```sql CREATE TABLE IF NOT EXISTS product_channel_map ( id INTEGER PRIMARY KEY AUTOINCREMENT, channel TEXT NOT NULL, -- 'wix' | 'etsy' | 'ebay' channel_sku TEXT, -- platform SKU if present listing_id TEXT, -- platform listing/variation id product_id INTEGER REFERENCES products(id) ON DELETE CASCADE, created TEXT NOT NULL, UNIQUE (channel, channel_sku, listing_id) ); ``` Unmapped incoming lines resolve to `product_id = NULL` β†’ they appear in a **"needs mapping"** queue in the UI (one click to bind to a product, remembered forever). ### 3.4 What it takes to fulfill β€” `product_components` (the BOM) This is what turns an order into "what to make." Lead-time-aware, and supports **bundles** (a starter kit = 1 block + 1 spawn bag + a printed guide). ```sql CREATE TABLE IF NOT EXISTS product_components ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE, fulfill_type TEXT NOT NULL, -- 'produce' | 'harvest' | 'stock' -- produce: make a batch batch_type TEXT, -- 'block' | 'grain' (matches batches.batch_type) species TEXT, strain TEXT, recipe_id INTEGER REFERENCES recipes(id),-- substrate formula for the block lead_days INTEGER DEFAULT 0, -- colonisation time β†’ start_by = ship_by - lead_days -- harvest: allocate fresh grams grams REAL, -- common qty_per_unit REAL NOT NULL DEFAULT 1, -- components per 1 ordered unit notes TEXT DEFAULT '' ); ``` - **grow kit** β†’ one `produce`/`block` row, `lead_days` = its colonisation time, `recipe_id` = its substrate. - **grain spawn** β†’ one `produce`/`grain` row. - **fresh mushrooms** β†’ one `harvest` row, `grams` set. - **supplies** (cultivation gear) β†’ one `stock` row, no production. > v1 can collapse this to a 1:1 "one product = one component" if you'd rather not model bundles > yet β€” the engine query is identical, just fewer rows. Recommendation: keep the table, > it's the same effort and bundles are inevitable. ### 3.5 Orders β€” `orders` + `order_items` ```sql CREATE TABLE IF NOT EXISTS orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, channel TEXT NOT NULL, -- 'wix' | 'etsy' | 'ebay' | 'manual' channel_order_id TEXT NOT NULL, -- platform order id (or generated for manual) status TEXT NOT NULL DEFAULT 'new', -- new|in_production|ready|shipped|cancelled order_date TEXT, ship_by TEXT, -- channel SLA / promised date β†’ drives urgency customer_id INTEGER REFERENCES customers(id), -- deduped aggregate (Β§3.7) customer_name TEXT, -- snapshot at order time customer_email TEXT, ship_country TEXT, total_amount REAL, currency TEXT, raw_json TEXT, -- full payload for re-parsing / audit imported TEXT NOT NULL, updated TEXT NOT NULL, UNIQUE (channel, channel_order_id) -- idempotency: re-sync never duplicates ); CREATE TABLE IF NOT EXISTS order_items ( id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, channel_sku TEXT, listing_id TEXT, title TEXT, -- platform line title (for the mapping UI) qty INTEGER NOT NULL DEFAULT 1, product_id INTEGER REFERENCES products(id), -- NULL = needs mapping unit_price REAL ); CREATE INDEX IF NOT EXISTS idx_orderitems_order ON order_items(order_id); CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status); ``` The `UNIQUE (channel, channel_order_id)` constraint is the whole idempotency story β€” the same pattern you already rely on with `scan_log.client_uuid` (the I-11 index). Polls upsert; webhooks upsert; CSV re-imports upsert. No doubles, ever. ### 3.6 Sync audit β€” `order_sync_log` (optional but cheap) ```sql CREATE TABLE IF NOT EXISTS order_sync_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, time TEXT NOT NULL, channel TEXT NOT NULL, ok INTEGER NOT NULL, fetched INTEGER DEFAULT 0, -- orders seen upserted INTEGER DEFAULT 0, -- new/changed message TEXT ); ``` Surfaces "last sync 4 min ago, 3 new" and errors in the admin UI β€” same spirit as `backups/.backup-status.json`. ### 3.7 Customers β€” `customers` + `customer_identities` A deduped buyer record so repeat-customer, channel, and lifetime-value stats fall out of orders you already ingest. The `orders` row keeps its own PII snapshot; `customers` is the rolled-up aggregate. ```sql CREATE TABLE IF NOT EXISTS customers ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT, -- lowercased; primary dedup key when present name TEXT, country TEXT, first_channel TEXT, -- where we first saw them first_order TEXT, last_order TEXT, order_count INTEGER DEFAULT 0, -- maintained on every import total_spent REAL DEFAULT 0, currency TEXT, notes TEXT DEFAULT '', created TEXT NOT NULL, UNIQUE (email) ); -- eBay masks buyer email, and one person may buy on several channels β†’ match on a per-channel -- handle, with manual merge to fold identities into one customer. CREATE TABLE IF NOT EXISTS customer_identities ( id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE, channel TEXT NOT NULL, -- 'wix' | 'etsy' | 'ebay' handle TEXT NOT NULL, -- email, eBay username, Etsy buyer id… created TEXT NOT NULL, UNIQUE (channel, handle) ); ``` Import resolution: lookup `(channel, handle)` in `customer_identities` β†’ if found, use that customer; else if `email` matches an existing customer, link + add the identity; else create. Then bump `order_count` / `total_spent` / `last_order`. Cross-channel matches that aren't certain stay separate until you **manually merge** them in the UI. ### 3.8 Reservation β€” `order_allocations` When you commit to producing for an order, this links the order line to the batch making it, so it **drops off the make-list** (your "reserve for customer" choice). ```sql CREATE TABLE IF NOT EXISTS order_allocations ( id INTEGER PRIMARY KEY AUTOINCREMENT, order_item_id INTEGER NOT NULL REFERENCES order_items(id) ON DELETE CASCADE, batch_id TEXT REFERENCES batches(batch_id) ON DELETE SET NULL, -- the producing batch qty REAL NOT NULL, -- component units reserved for this line status TEXT NOT NULL DEFAULT 'reserved', -- reserved | produced | shipped created TEXT NOT NULL, UNIQUE (order_item_id, batch_id) ); CREATE INDEX IF NOT EXISTS idx_alloc_batch ON order_allocations(batch_id); ``` A batch's `qty` is its capacity; allocations consume it, so one batch can be reserved across several orders up to that capacity (the UI shows "#B-100: 10 blocks, 5 reserved, 5 free"). Manual override = add/edit/remove allocations by hand. --- ## 4. The production-demand engine (the payoff) A single derived view, recomputed on demand (cheap at your volumes). It is **reservation-aware**: once you commit to making something for an order, that order stops asking for it. ``` 1. Take order_items on orders with status IN ('new','in_production'), component.fulfill_type = 'produce' (stock = pick & pack; harvest = deferred in v1) 2. reserved = Ξ£ order_allocations.qty already linking those items to a batch 3. gross_need = Ξ£ (item.qty Γ— component.qty_per_unit) βˆ’ reserved grouped by (batch_type, species, strain, recipe_id) 4. free_pipe = open batches matching (species, strain, batch_type), capacity minus what's already allocated to other orders 5. net_to_start = max(0, gross_need βˆ’ free_pipe) 6. start_by = min(order.ship_by) βˆ’ component.lead_days ← urgency / sort key ``` **Clicking "Create batch" on a row reserves it** β€” it writes `order_allocations` (status `reserved`) for the contributing lines, so they drop out of `gross_need` on the next recompute and the order moves to `in_production`. Manual override = edit or remove those allocations. **Worked example** | Need (component) | Gross | Free pipeline | **Net to start** | Start by | | ------------------------------ | ----- | --------------------- | ------------------- | -------- | | Shiitake block (recipe "Demo") | 10 | 5 free (batch #B-100) | **5** | Jun 14 | | Oyster grain bag | 20 | 10 free (#B-200) | **10** | Jun 11 | | Cultivation supplies | 6 | β€” (stock) | **0 β€” pick & pack** | β€” | From a net-to-start row, one click β†’ **pre-filled "Create batch"** (species/strain/qty/recipe/ `due = start_by + lead_days`) which also reserves it against the orders that drove it β€” or a **manual_task** "Start 5Γ— shiitake blocks by Jun 14". That's the bridge from sales back into the production tracker you already run. ### 4.1 Customers β€” repeat buyers & channel attribution Every import upserts a `customers` row (Β§3.7): dedup by lowercased email, falling back to `customer_identities` (eBay masks buyer email) with manual merge to fold one person's eBay + Etsy + Wix identities together. Maintained automatically per customer β€” order count, total spent, first/last order, first channel. With zero extra data entry you get: - **Repeat customers** β€” who has ordered more than once, and how often. - **Channel attribution** β€” which channel each customer came from and buys most on. - **Lifetime value** β€” total spent, average order, recency β€” the inputs for rewarding regulars. --- ## 5. Sync architecture Two ingestion modes, one normalizer per channel writing the same `orders`/`order_items` shape: - **Webhook (push)** β€” Wix only. `POST /api/orders/webhook/wix`, verify signature, upsert, SSE-broadcast. Near-real-time. - **Poll (pull)** β€” Etsy + eBay. A `setInterval` job (model it on `updateDuckdnsIP`'s 5-min timer) walks each enabled channel using the `last_sync` / `last_cursor` watermark, fetches orders changed since, upserts, advances the watermark, writes `order_sync_log`. Cross-cutting: - **Idempotent upsert** keyed on `(channel, channel_order_id)` β€” webhook and poll can both see the same order, only one row results. - **Token refresh** β€” before each Etsy/eBay call, if `token_expires` is within ~5 min, refresh and persist. (Etsy access token ~1 h, eBay ~2 h β€” verify at build time.) - **Backoff** β€” on 429/5xx, exponential backoff; record `last_error`; never crash the timer. - **PII minimization** β€” store only what fulfillment needs (name, ship country, email for contact); keep the raw payload in `raw_json` with a retention cap (Β§10). --- ## 6. Per-channel integration plans ### 6.1 Wix β€” *easiest, do first* - **Auth:** API Key + Site ID (header `Authorization: ` + `wix-site-id: `). Simplest for a single site you own; no OAuth dance. - **Read orders:** Wix eCommerce **Orders** API β€” `Search/Query Orders` + `Get Order`. - **Push:** Wix webhooks (e.g. *Order Paid / Order Created*) β†’ your `/api/orders/webhook/wix`, payload signed as a JWT you verify against Wix's public key (`webhook_secret`). - **Why first:** no OAuth dance, supports real-time push, exercises the full catalog β†’ mapping β†’ demand path end-to-end on one channel. ### 6.2 Etsy β€” *poll-based* - **Auth:** OAuth 2.0 **Authorization Code + PKCE** (you've already implemented PKCE β€” as an MCP *provider*; here you're the *client*, mirror image but same concepts). Scope `transactions_r` (read receipts); add `transactions_w` later for marking shipped. - **Read orders:** orders = **Shop Receipts** β†’ `getShopReceipts` (filter `was_paid=true`, paginate, use `min_last_modified` as the watermark). - **No webhooks** β†’ poll every few minutes. Access token ~1 h β†’ auto-refresh with the refresh token (good ~90 days). Rate limits ~10/s, ~10k/day β€” a 5-min poll is nowhere near. ### 6.3 eBay β€” *the onboarding runway* The plan starts with the onboarding. The important correction up front: > For **your own sales** you need the **Sell β†’ Fulfillment API** (`getOrders`). That is > **generally available** β€” it is *not* the rejection-prone gate. The gate everyone means when > they say "eBay is hard" is the **Buy API**, which you do **not** need. **Onboarding checklist** 1. Create an eBay **developer account** β†’ an application β†’ get **Sandbox + Production keysets** (App ID / Cert ID / Dev ID). 2. **Marketplace Account Deletion/Closure notification endpoint** β€” eBay will not fully activate production keys until your app exposes a public HTTPS endpoint that: - answers a **GET verification challenge**: compute `SHA-256(challengeCode + verificationToken + endpointURL)` and return it as JSON; and - **acks POST** account-deletion notifications with `200`. With public HTTPS (e.g. DuckDNS + Let's Encrypt) meistertracker can host this, for instance at `/api/orders/webhook/ebay-deletion`. **This is usually the entire blocker** β€” it's ~30 lines. 3. **OAuth user token:** Authorization Code grant; you (the seller) consent once. Scope `https://api.ebay.com/oauth/api_scope/sell.fulfillment` (read). Access token ~2 h, refresh token ~18 months β†’ auto-refresh + persist. 4. **Read orders:** Sell **Fulfillment** `getOrders` (filter on `lastmodifieddate`, paginate). **Interim bridge (works now, zero API):** Seller Hub β†’ Orders β†’ **CSV export** β†’ drop into a `POST /api/orders/import` (channel `ebay`, same upsert path), plus a **manual add** form. So eBay is in the hub from day one and the API just makes it automatic later. ### 6.4 Billbee β€” *the hub, not a shop* (built) A lab may run [Billbee](https://billbee.io), which already collects the orders of every channel it sells on. Meistertracker reads them and writes nothing but a Sendungsnummer: Billbee keeps the stock, the invoices, the shipping and the customer. What follows are the ways that differs from Β§6.1–6.3. - **Auth:** an application API key (`X-Billbee-Api-Key`) **plus** basic auth with the Billbee login and an API password created in the Billbee account. The key has to be requested from Billbee and the API switched on in the account β€” neither is something the code can do, and both surface as a plain 401. No OAuth, so the credentials fit `sales_channel_config` unchanged: `api_key` = application key, `client_id` = login, `client_secret` = API password. - **Read orders:** `GET /api/v1/orders`, paged (`page`, `pageSize` ≀ 250) and floored at `minOrderDate` = 30 days β€” without a floor every poll would walk the entire order history of every connected channel, and the sync route stops after 20 pages regardless. The cursor is `page|since`: recomputing the floor per page moves the filter underneath the paging, and the order that shift pushes across a page break is never returned by that sync at all. - **What is not an order:** coupon and discount positions arrive in `OrderItems` like any other line and are dropped (`IsCoupon`) β€” they are not things to make, and they cannot map to a product, so they would sit in the mapping screen for ever. An order's total prefers `PaidAmount` on a paid order over `TotalCost + ShippingCost`: the sum rests on a reading of the API documentation that cannot be checked from here, and read the wrong way it overstates every order by its postage. - **Dedup β€” the one rule to understand:** orders are filed under the channel `billbee` and keyed on `BillBeeOrderId`, never re-attributed to the shop they came from the way `_wixOriginChannel` does. Orders dedupe on (channel, channelOrderId), and Billbee's order id has nothing to do with eBay's, so re-attributing would file one sale twice instead of merging it. **Consequence: when Billbee is on, the direct connection for every shop it covers belongs off.** The shop name still travels in `raw_json`. - **No customer comes into the lab.** Billbee's order carries the buyer's name, e-mail, telephone and delivery address; a lab needs none of it, and a self-hosted lab server is not built to keep it, while Billbee is built to hold exactly that and already does. `_billbeeLabRecord()` keeps the order's identity, state, money, country, articles and a link back into Billbee, and drops the person β€” `raw_json` included, or filtering the columns would only have moved the address rather than left it behind. An order with no identity and no name creates no customer row at all (`upsertCustomerFromOrder` returns null), so the customer list does not fill with blank strangers. - **Tracking write-back:** `POST /api/v1/orders/{id}/shipment`. Carrier ids are read once per process from `GET /api/v1/enums/shippingcarriers` rather than hard-coded from a guess β€” a miss costs the carrier id, never the Sendungsnummer, and the carrier name still travels in the comment. - **Stock is read, never written.** Billbee is the commercial side of the business and owns what may be sold; the lab's job with a Billbee order is to make what it says. An earlier draft of this section pushed the harvest releases into Billbee with `updatestockmultiple` β€” it came out again: harvest releases are fresh produce, which need not be sold through a Billbee channel at all, so that road may have nothing to carry. The four commits are on the branch's history if the question ever changes shape (see the note under Β§11.1). - **When it runs:** every 15 minutes for every channel that is switched on *and* connected, one after another (`pollSalesChannels`), plus the button. Before that timer existed a channel was pulled only when somebody pressed "Jetzt synchronisieren", which made the production planning depend on somebody remembering β€” least likely on the day it matters. Not started in a worktree copy; the button still works there. - **Throttle:** 2 calls/second per endpoint for one key + user, answered with 429 + `Retry-After`. All calls are serialised through one 550 ms queue and a 429 is retried once. The poll is sequential for the same reason: two channels at once share one connection and one account throttle. - **Credentials that cannot work are refused at the keyboard:** a colon breaks basic auth outright in the login and, per Billbee's documentation, the connection in the API password. Both arrive otherwise as a plain 401, which reads as "the key was never approved" and sends somebody off requesting a new one. - **Not webhooks β€” for now.** Billbee can register one, which needs a URL it can reach; the server has public endpoints already (the eBay deletion route is one), so this is a later step rather than an impossibility. Polling reuses the sync route the other three channels use. --- ## 7. REST API surface (matches your `/api/*` style) | Method & path | Role | Purpose | | -------------------------------------- | ------ | ---------------------------------------- | | `GET /api/orders` | worker | List/filter orders (status, channel) | | `GET /api/orders/:id` | worker | Order detail + items | | `PATCH /api/orders/:id` | worker | Set status (in_production/ready/shipped) | | `POST /api/orders/import` | admin | CSV/manual import (any channel) | | `GET /api/orders/demand` | worker | The Β§4 production-demand rollup | | `POST /api/orders/demand/:row/batch` | worker | Create a pre-filled batch from a demand row | | `GET /api/products` Β· `POST/PATCH` | admin | Catalog CRUD | | `GET /api/products/unmapped` | admin | Lines needing a product mapping | | `POST /api/products/map` | admin | Bind a channel listing β†’ product | | `GET/POST /api/channels/:c/config` | admin | Channel credentials + enable/disable | | `POST /api/channels/:c/sync` | admin | Force a sync now | | `GET /api/products/mappings?channel=` | worker | The standing article ↔ listing mappings of one channel | | `POST /api/orders/webhook/wix` | public | Signed Wix webhook | | `GET/POST /api/orders/webhook/ebay-deletion` | public | eBay account-deletion compliance | Role gating follows your README matrix: viewing demand = worker; editing catalog/credentials = admin (like inventory thresholds & suppliers). --- ## 8. UI β€” new "Orders" tab (PWA) Three screens, consistent with the existing tabbed SPA + SSE live updates: 1. **Inbox** β€” orders across channels, channel badge, status chips, `ship_by` urgency colouring, a **"needs mapping (N)"** banner. Offline-friendly like the rest of the PWA. 2. **Mapping** β€” unresolved listing lines on the left, product picker on the right; bind once, remembered. Catalog + components editor lives here too. 3. **To-Make board** β€” the Β§4 table: net-to-start per component, sorted by start-by date, each row with **"Create batch"** / **"Add task"**. This is the screen you'd actually open each morning. 4. **Customers** β€” repeat buyers, channel attribution, lifetime value (reads the Β§4.1 aggregates), with a one-click "erase customer" for deletion requests. --- ## 9. MCP additions (optional, you already have the surface) Natural new tools on `mcp-server.js`: `list_orders`, `get_production_demand`, `map_listing_to_product`. Then *"Claude, what do I need to grow this week?"* answers from live order data. Cheap once the REST layer exists. --- ## 10. Security, PII & data protection - **Customer data is retained for analytics.** The hub keeps repeat-customer and channel-attribution stats, so customer records **persist** rather than being purged. That's a legitimate use β€” just keep it clean under GDPR: document a **legitimate-interest** basis for retaining purchase history, offer **access / deletion** on request (a one-click "erase customer" that nulls PII but can keep the anonymized aggregates so your totals don't break), and cap only the bulky raw payloads β€” e.g. drop `orders.raw_json` after ~12 months while the parsed stats live on. Point buyers at the shop's own legal/privacy notice. - **Secrets at rest.** Existing config tables store tokens in **plaintext** (DuckDNS token, print bridge token). Channel refresh tokens are more sensitive. **Recommendation:** encrypt `access_token`/`refresh_token`/`client_secret` with AES-GCM using a key from an env var (`ORDERS_SECRET_KEY`), decrypt only in memory. Falls back to plaintext if unset, matching current behavior. Either way, the credentials API is **admin-only** and the encrypted DB backup already covers exfil-via-backup. - **Webhook auth.** Verify Wix JWT signatures and the eBay challenge token β€” public endpoints must reject forged payloads. --- ## 11. Versand & Tracking β€” Labels kaufen + Tracking-RΓΌckkanal (Phase 4) The fulfillment time-sink is hopping between carrier portals and three channel backends. This phase collapses it to **one "Pack & Ship" action** inside Meistertracker. It's the most involved module, but one decision removes most of the complexity. ### 11.1 Principle: one aggregator API, not per-carrier Integrating **DHL + DPD + Hermes** each directly = three contracts + three uneven APIs + slow onboarding. Instead use **one shipping-aggregator API** that fronts all of them (and usually offers better-than-retail rates). Candidates for Germany: **Shipcloud** (API-first, German), **Sendcloud** (more turnkey, has marketplace connectors), **Billbee** (covers more of the order side too). *Provider choice is a separate comparison β€” the design below stays provider-agnostic behind a thin `shipping/provider.js` adapter, so swapping later is cheap.* > **Billbee is connected since Β§6.4 β€” reading only.** When a lab buys its labels inside > Billbee or separately, Meistertracker is not on the label path at all for > that lab, and `pushTracking` only ever fires for a label bought here. That answers open question > 4 for such a lab without answering it for the product: a lab that does want Meistertracker to > buy labels still needs an aggregator, and the comparison is still owed. > > The reverse direction β€” the lab telling Billbee what is in stock β€” was built and then taken out > again: it derived quantities from harvest releases, and a lab need not sell fresh produce through > Billbee at all. Durable goods sold there (growkits, spawn, cultures, for example) would need a > finished-goods stock that Meistertracker does not have: `inventory` is substrate materials, and > a batch is a production run, not a shelf. That is the feature to build first if the question > comes back. > **Division of labour:** Meistertracker stays the **brain** (orders β†’ production β†’ pack-ready); > the aggregator is only the **arm** (label + tracking). One API call between them. ### 11.2 Data model Add package data to products, plus two new tables (migrations continue after the v42+ block): ```sql -- products gain shipping defaults β†’ one-click label buying ALTER TABLE products ADD COLUMN weight_g INTEGER DEFAULT 0; ALTER TABLE products ADD COLUMN length_cm REAL DEFAULT 0; ALTER TABLE products ADD COLUMN width_cm REAL DEFAULT 0; ALTER TABLE products ADD COLUMN height_cm REAL DEFAULT 0; ALTER TABLE products ADD COLUMN default_carrier TEXT; -- 'dhl' | 'dpd' | 'hermes' ALTER TABLE products ADD COLUMN default_service TEXT; -- carrier service code -- aggregator credentials (single-row, like duckdns_config) CREATE TABLE IF NOT EXISTS shipping_config ( id INTEGER PRIMARY KEY CHECK (id = 1), enabled INTEGER DEFAULT 0, provider TEXT DEFAULT '', -- 'shipcloud' | 'sendcloud' | … api_key TEXT DEFAULT '', api_secret TEXT DEFAULT '', sender_json TEXT DEFAULT '', -- your Absender address default_carrier TEXT DEFAULT 'dhl', test_mode INTEGER DEFAULT 1 ); -- one row per parcel CREATE TABLE IF NOT EXISTS shipments ( id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, carrier TEXT, -- 'dhl' | 'dpd' | 'hermes' service TEXT, weight_g INTEGER, tracking_number TEXT, tracking_url TEXT, label_format TEXT, -- 'pdf' | 'zpl' label_ref TEXT, -- stored label file / blob cost REAL, currency TEXT, provider TEXT, -- aggregator used provider_ref TEXT, -- aggregator shipment id (cancel/refund) status TEXT NOT NULL DEFAULT 'created', -- created|purchased|handed_over|in_transit|delivered|cancelled pushed_to_channel INTEGER DEFAULT 0, -- tracking written back yet? push_error TEXT, created TEXT NOT NULL, updated TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_shipments_order ON shipments(order_id); ``` Encrypt `shipping_config.api_key`/`api_secret` with the same `ORDERS_SECRET_KEY` scheme as the channel tokens (Β§10). ### 11.3 The "Pack & Ship" flow (the actual time-saver) From an order that's produced/ready, **one screen**: ``` Order (address + items known) β†’ weight/size auto-filled from products (override if needed) β†’ pick carrier/service (default per product; rules e.g. >2 kg β†’ DPD) β†’ "Label kaufen" β†’ aggregator returns label (PDF/ZPL) + tracking number β†’ print to the 100Γ—150 printer (existing print pipeline / print-bridge) β†’ tracking written back to the channel β†’ order = "Versandt", allocations = "shipped" ``` No portal-hopping, no retyping tracking into three backends. **Batch mode:** select N orders β†’ buy + print all labels in one pick/pack run. ### 11.4 Label printing β€” already solved You can print **100Γ—150 mm** labels β†’ no new hardware. Aggregators return either **PDF** (send straight to that printer) or **ZPL** (reuse the existing **print-bridge** that already drives the Zebra). The 50Γ—30 product/bag labels stay on the GK420d; parcels go to the 100Γ—150. ### 11.5 Tracking write-back β€” supported on all three channels | Channel | Call | Effect | | ------- | ---- | ------ | | **Wix** | Create **Fulfillment** (trackingNumber + shippingProvider) | order β†’ fulfilled, buyer notified | | **Etsy** | `createReceiptShipment` (tracking_code + carrier_name) | marks shipped, buyer email | | **eBay** | `createShippingFulfillment` (trackingNumber + shippingCarrierCode) | marks shipped β€” *same Fulfillment API used to read orders* | Caveat: each channel has its **own carrier-name list**, so keep a small `carrier_map` (`dhl`/`dpd`/`hermes` β†’ each platform's exact code). eBay write-back rides on the Phase 3 eBay auth, so **Wix + Etsy tracking can go live before eBay**. ### 11.6 Delivery status (nice-to-have) Aggregators emit **tracking webhooks** β†’ `POST /api/shipping/webhook/:provider` updates `shipments.status` (in_transit β†’ delivered). Gives you a "Sendungsverfolgung" column with no manual checking, and closes the loop on "where is order X". ### 11.7 REST surface (Phase 4) | Method & path | Role | Purpose | | ------------- | ---- | ------- | | `POST /api/orders/:id/shipment/rates` | worker | (optional) live rate/service options | | `POST /api/orders/:id/shipment` | worker | buy label β†’ returns label + tracking | | `POST /api/shipments/:id/print` | worker | send label to printer / print-bridge | | `POST /api/shipments/:id/push` | worker | write tracking back to the channel (or automatic) | | `POST /api/shipping/webhook/:provider` | public | delivery-status updates | | `GET/POST /api/shipping/config` | admin | aggregator credentials + sender address | UI: a 5th Orders sub-screen **"Versand"** (pack queue β†’ buy β†’ print β†’ done), plus a "Label kaufen" button on each ready order. --- ## 12. Phased rollout | Phase | Deliverable | Rough effort | | ----- | --------------------------------------------------------------------------- | ------------ | | **0** | Migrations v42+ (all tables), catalog CRUD, **manual add + CSV import**, mapping UI, **reservation-aware To-Make board**, **customers view**. Useful with zero APIs. | ~3 days | | **1** | **Wix** live (API key + Order webhook + poll fallback). | ~1–2 days | | **2** | **Etsy** live (OAuth+PKCE client, `getShopReceipts` poller, token refresh). | ~2 days | | **3** | **eBay**: account-deletion endpoint β†’ keyset activation β†’ OAuth user token β†’ `getOrders` poller. (The eBay-portal steps are manual; the endpoints are code.) | ~2 days + portal time | | **4a** | **Versand**: shipping-aggregator adapter β†’ buy label, print (100Γ—150 PDF/ZPL), store tracking (Β§11). DHL + DPD + Hermes via one API. | ~2–3 days\* | | **4b** | **Tracking-RΓΌckkanal**: push tracking to Wix / Etsy / eBay (reuses channel auth from Phases 1–3); delivery-status webhook; MCP tools. | ~2 days | \* plus provider selection + aggregator account/contract setup (see Β§11.1). Phase 0 alone delivers the core value β€” every order in one place and a real "what to make" list β€” even before a single API is connected. --- ## 13. Decisions **Resolved (this round)** - βœ… **No fresh-mushroom orders in v1** β€” mail-order only (grow-kits / spawn / supplies); `harvest` fulfillment deferred. - βœ… **Reserve on batch creation** β€” making a batch from a demand row reserves it against the driving orders so it drops off the make-list (Β§3.8, Β§4); manual override supported. - βœ… **Retain customer data for analytics** β€” keep repeat-customer / channel / LTV stats long-term (Β§3.7, Β§4.1, Β§10), with a deletion-request path rather than a blanket purge. **Still open** 1. **Bundles now or later?** Keep `product_components` (supports kits) vs. 1:1 product=recipe. *(Recommend: keep it β€” same effort.)* 2. **Encrypt channel secrets at rest?** *(Recommend: yes, env-keyed, plaintext fallback.)* 3. **Cross-channel customer identity.** Auto-merge a buyer across channels when email matches, and leave manual merge for the rest (eBay masks email)? *(Recommend: yes β€” auto on email, `customer_identities` + manual merge otherwise.)* 4. **Shipping provider + tariffs (Phase 4).** Which aggregator (Shipcloud / Sendcloud / Billbee …), and bring-your-own carrier contracts vs. the aggregator's negotiated rates? *(Pending the provider comparison, see Β§11.1.)* --- *Next step: Phase 0 as one PR (migrations + catalog + manual/CSV + reservation-aware To-Make board + customers view), then the channels one at a time.*