🔒 Internal Handbook — confidential. Do not share links or content with anyone outside G-Starlink.
ProductData model spec

Product data model specification

The authoritative data structure for all G-Starlink products. Every system (OMS, eSIM Hub, Shopify sync, chatbot) reads from this model.

Full SQL DDL lives in schema.sql in the OMS repo. This page explains the design.

Core concepts

SPU vs Variant

G-Starlink products have two layers:

SPU (Standard Product Unit) — what the customer sees. Example: eSIM-UK-10GB-30D. No carrier dimension. Customers buy SPUs on Shopify, Amazon, eBay.

Variant — what we actually fulfill. Example: eSIM-UK-10GB-30D-VODA. Includes the carrier. One SPU can have N variants (one per carrier). The backend chooses which variant to activate based on cost, stock, and routing rules.

Why this two-layer model

  1. Customer-side simplicity. Most retail customers don’t care whether they’re on Vodafone or EE — they just want UK 10GB for 30 days.
  2. Backend flexibility. Because variants sit behind the SPU, we can switch carriers (for cost, for stock, for quality) without changing the customer’s SKU.
  3. Margin visibility. Carrier-level cost differences can be >10%. Tracking costs at variant level makes margin analysis real.
  4. Enables a routing engine. Future: automatic carrier selection based on rules (lowest cost, highest margin, Tier-1-customer-gets-premium, etc.)

Table overview

Five core tables + one utility view:

suppliers ──< product_variants >── products ──< product_price_tiers
                                      │
                                      └──< customer_specific_prices >── customers

suppliers

Upstream providers. Every variant belongs to one supplier. Supplier info includes contact, API endpoint, and auth notes (not the actual keys — those live in a secrets manager).

products (the SPU layer)

Customer-facing products. Includes: SKU, name, product type (eSIM / physical), coverage scope (single country / region / global), coverage countries (as JSONB array), data amount, validity, billing type, retail prices (AUD/USD/EUR), description, image URL, Shopify handle.

product_variants

The actual ship-able SKUs. Includes: variant SKU, the product SKU it belongs to, supplier, supplier’s SKU code, carrier code, carrier name, 5G support flag, cost price (USD), default routing priority, stock threshold, current stock, active flag.

Constraint: (product_sku, carrier_code) must be unique. No two “Vodafone UK 10GB 30D” variants under the same SPU.

product_price_tiers

Standard wholesale prices by customer tier and quantity break. A Tier 1 customer buying 500+ gets a different price than a Tier 2 customer buying 1. Retail prices also live here (tier = retail, min_quantity = 1).

customer_specific_prices

Per-customer negotiated prices that override the standard tier. Use sparingly — every exception makes pricing less maintainable. Requires a reason and a valid_to date.

v_effective_price (view)

Convenience view that unifies the two pricing tables. App logic queries this view for a given (customer, product, quantity) and picks the first row (customer-specific prices win).

Price lookup logic

For a given (customer, product, quantity):

  1. Check customer_specific_prices for (customer_id, product_sku) where quantity ≤ order_qty and date is within validity. If a row matches → use that price.
  2. Otherwise, look up the customer’s tier and query product_price_tiers with (tier, product_sku). Take the row where min_quantity is the largest value ≤ order_qty.
  3. Apply.

Example: Tier 1 customer orders 300 of eSIM-EU-5GB-7D. Tiers defined:

  • tier_1 / min_qty=1 / $8.00
  • tier_1 / min_qty=100 / $7.20
  • tier_1 / min_qty=500 / $6.50

300 matches the min_qty=100 bracket (100 ≤ 300 < 500). Unit price: $7.20.

System integration

SystemWhat it reads from the catalog
ShopifySKU field maps to products.sku. Product description, price, image sync from products table (long-term: API-driven sync)
Amazon / eBaySame SKU field. Listings reference our SPU SKU
OMSReads products for order items, product_variants for fulfillment routing
eSIM Hub (future)Reads products for reseller catalog, v_effective_price for quotes, writes orders referencing variant SKUs
ChatbotReads products for customer-facing Q&A (price, coverage, data amount). Never cached — always live query

Data quality rules

  • SKUs are immutable. Once an order references a SKU, that SKU can never change. To retire a product: is_active = false + create a successor.
  • coverage_countries uses ISO 3166-1 alpha-2 codes as a JSONB array. Always an array, even for single countries (["UK"]).
  • cost_price_usd precision: 4 decimal places (to handle fractional cents at scale).
  • Every price change in product_price_tiers uses valid_from / valid_to — never overwrite old prices. Historical pricing is audit trail.

Implementation status

  • Supabase migration applied
  • TypeScript types generated
  • pricing.repo.ts implemented (effective price lookup)
  • routing.repo.ts v1 implemented (priority-based carrier selection)
  • Initial SKU inventory loaded (via Excel template)
  • Shopify SKU reconciliation
  • Amazon / eBay SKU reconciliation

Related

OverviewSKU naming