Data Model

All data lives in Supabase Postgres. Access is server-side only via the admin client (lib/server/supabase-admin.ts, service-role key). Storage uses three buckets (input / output / merchant images).

Reading or writing the PRODUCTION database from a console or a script needs the owner’s approval for that operation, first (#451) — one gated door, fail-closed, reads and writes approved separately. See Deployment → Touching the production database. Nothing on this page describes the deployed app’s own runtime, which is not gated.

Core tables

product_variant_references (#547)

Merchant-uploaded shade reference images + a first-class colour code, one row per scope — same shape and precedence as #406’s product_variant_settings: option:<name>=<value> (a colour-level rule) or variant:<gid> (a single-variant override). See Try-On Pipeline → Phase 3 — merchant-uploaded shade references.

Columns: store_id, product_id, scope_key, option_name / option_value | variant_external_id (scope fields, same as product_variant_settings), colour_code text (≤64 chars), storage_bucket, images jsonb (array of {path, mimeType, sizeBytes, uploadedAt}). Unique on (product_id, scope_key). RLS on — service-role only, like the other merchant-catalog tables.

Try-on flow tables

tryon_feedback.provider / .model — the model that ACTUALLY ran (#303)

These two columns must always come from the session’s own latest completed tryon_provider_jobs row (provider + request_payload->>'generationModel'), resolved server-side by getSessionGenerationAttribution(storeId, sessionId). Never from stores.generation_provider/_model.

Why this is a rule and not a preference. Those stores columns are the configured default. Since smart routing (#107, live ~2026-07-27) the provider is picked per generation — kie/fal by live health, the intimates pin (#59), the per-product tuning override (#87) — so the default and the actual generator routinely differ. Writing the default is what made 100% of the first 45 days of feedback (527 prod rows) read fal.ai / nano-banana-lite while kie.ai ran the large majority, which in turn fed the “>5 downs → pin nano-banana-2” auto-proposal a fiction.

• NULL means “unknown”, never “the default” — no completed job resolves (old session, mock mode, a session belonging to another store). Nothing may fill it in with a guess.
• tryon_provider_jobs carries no store_id (#291), so tenant scoping goes through tryon_sessions.
• Reads: getTryOnFeedbackStats(storeId, …) is the tenant-scoped, merchant-shaped one — counts only, no provider/model in the type. The cross-store operator view is getTryOnFeedbackStatsAllStores() (#289); which model ran is operator information.

Bundle / outfit tables

product_bundles

Columns: id, store_id, primary_product_id, complement_product_id, position smallint (1 or 2), active, created_at.

Unique constraint: (store_id, primary_product_id, position) — one row per slot per primary product. The position column was added in migration 20260624_outfit_bundles.sql, which backfills legacy single-complement rows to position = 1.

Access via getProductBundleSlots, slot-aware upsertProductBundle, deleteProductBundle, listProductBundles (all in lib/server/supabase-admin.ts). Every query filters store_id.

stores bundle columns

Three columns added in migration 20260617_product_bundles.sql, plus two more added in migration 20260820_copurchase_pairs.sql (#393):

bundle_shopify_discount_id is written/cleared by the store-settings PATCH handler when the bundle is enabled/disabled. copurchase_synced_at/copurchase_status are written by setCopurchaseStatus at the end of each store’s cron pass.

bundleDiscountTiers (#601)

An optional tier ladder on top of the single bundle_discount_percent, e.g. 2 products → −20%, 3 → −25%, 4 → −30%. Stored as the additive named key bundleDiscountTiers inside storefront_theme_settings.metadata (the #511 named-key pattern — read-modify-write merge, no migration). Shape: [{ count, percent, shopifyDiscountId }], valid count 2–4 (the #523-proven look caps). Parsed/validated by the pure module lib/billing/bundle-tiers.ts (resolveTierForCount, tiersForStorefront).

One Shopify discount code is auto-managed per tier, extending the TRYVIOBUNDLE rails: count 2 reuses TRYVIOBUNDLE itself, higher tiers get TRYVIOBUNDLE3 / TRYVIOBUNDLE4 (same “Tryvio:“-prefixed title ownership guard). Codes are synced on save and deleted on tier removal or bundle-off in the store-settings PATCH handler; the ladder configuration itself survives bundle-off. When a ladder is saved, stores.bundle_discount_percent is kept in sync with the lowest tier’s percent. See Try-On Pipeline → Tiered bundle discounts for the proxy config and cart-application details.

A stored tier above count 2 with a null shopifyDiscountId is configured but not served (#592) — its discount code hasn’t been created in Shopify yet, so tiersForStorefront withholds it until the sync completes. The admin/editor contract is unchanged either way: GET/PATCH /api/shopify/store-settings still return bundleDiscountTiers as {count, percent} only — codes and node ids stay server-owned.

copurchase_pairs (#393)

Products bought together in the same Shopify order, recomputed daily by GET /api/cron/copurchase-pairs. Added in migration 20260820_copurchase_pairs.sql. No shopper PII — product ids and counts only.

Columns: store_id (FK stores, on delete cascade), product_a_external_id, product_b_external_id (Shopify product GIDs; a < b lexicographically, so a pair is stored once), orders_together, orders_a, orders_b (int), window_from, window_to (timestamptz — the order date range the pair was computed over), computed_at (timestamptz, default now()), dismissed_at (timestamptz, nullable — set when a merchant dismisses the suggestion).

Primary key (store_id, product_a_external_id, product_b_external_id); index on (store_id, product_b_external_id); RLS enabled.

Access via listCopurchaseStores, replaceCopurchasePairs, setCopurchaseStatus, getCopurchaseStatus, listCopurchasePairs, listCopurchasePartners, setCopurchasePairDismissed, listStorefrontProductsByExternalIds (matches both external_id spellings per #193, keyed by the canonical GID) — all in lib/server/supabase-admin.ts. Every query filters store_id. replaceCopurchasePairs upserts without touching dismissed_at, then deletes only rows with an older computed_at than the current recompute — a merchant’s dismissal survives the nightly refresh.

Analytics attribution tables

tried_products and attributed_order_lines were added by 20260630_analytics_roi_attribution.sql so merchant ROI can be calculated from product-level proof, not broad session influence.

tried_products

One row means: this shopper successfully saw this product in a Tryvio result. Bundle/outfit results write one row for the primary product and one row for each complement. Related-product try-ons write their own proof row.

Important columns: store_id, tryon_session_id, product_id, product_handle, external_product_id, external_variant_id, shopper_session_id, shopper_pseudo_id, role, source, tried_at, expires_at, attribution_key.

attributed_order_lines

One row means: this purchased line item matched a tried-product proof inside the 30-day window. The table is idempotent by (store_id, idempotency_key).

Important columns: order_id, order_line_id, product_id, product_handle, external_product_id, external_variant_id, quantity, line_value, currency, tryon_session_id, tried_product_id, attribution_mode, proof, metadata.

See Analytics & ROI for the business rules and dashboard semantics.

Growth / ops tables

platform_monthly_totals (#656)

Anonymous monthly totals across all stores (is_test excluded), so a former merchant’s contribution survives its erasure. It holds no store id, domain or hash. It is not used for AI tuning and not shared with third parties (Shopify API License §2, §6.2.8).

• Key (month, metric, bucket), plus value and shops. Metrics: installs, uninstalls, reinstalls (from install_events, complete since #531), lifetime_days (sum over shops that uninstalled that month), tryons, generations, attributed_revenue (bucket = currency, one order counted once) and shops_by_plan (bucket = plan, point-in-time, so only for the month that just closed).
• Every metric is defined once, in platform_month_cells(from, store?). Rows are grouped by created_at, so a late row lands in a month that has not been captured yet.
• capture_platform_monthly_totals() writes each closed month once (on conflict do nothing). It runs daily in log-maintenance and right before every erasure. Any cell covering < 10 shops is folded into bucket other. For attributed_revenue, other adds up amounts in different currencies, so read it as an order of magnitude only.
• A store erased before its last months are captured leaves platform_monthly_carry(store) on its store_erasures row. The capture of that month adds the carry for completed erasures, so nothing is lost and nothing is counted twice.

order_fees (#441)

One row per order we decided about — including the ones we decided NOT to charge, which is the point of it. A row records what we concluded at the time and why, so a merchant question six weeks later is answered from the ledger instead of re-running a rule that may have changed since.

stores order-fee columns (#441)

🚨 Both columns must appear in EVERY explicit stores select the order-fee run touches — notably getActiveSubscribedStores. A column missing from an explicit list is not a type error, it is undefined at runtime (the #246 class). order-fee-run.test.ts asserts the select list by parsing the source, because that is the only failure here that would OVER-charge.

Notable patterns

• captured_emails is reused as the store for demo leads and contact inquiries (no dedicated tables) — distinguished by the source column and metadata JSON. The demo store id (teststoretryon) is used as the store_id for these “system” records.
• stores.custom_plan + custom_price/custom_try_on_limit/custom_overage_rate drive custom plans; getPlanLimit() / getOverageRate() in lib/billing/plans.ts read them. reconcileStoreBilling is the authority: it keeps these set while the active sub is the custom one and clears them (CLEARED_CUSTOM_PLAN_FIELDS) the moment the store lands on a standard tier or trial.
• Usage/period columns on stores: try_ons_used + overage_pending are the live counters (atomic RPC increment_try_ons); current_period_starts_at/current_period_ends_at track the active 30-day Shopify cycle. The daily billing-overage cron resets the counters when current_period_ends_at advances (Shopify fires no webhook on renewal).
• Trial dates are written once on first install in upsertStore (only when trial_starts_at IS NULL).