Database Schema

Tables Overview

Catalog-backed merch tables across three schema files:

• Core tables: packages/db/src/schema/merchTables.ts (including campaign satellite tables and audit log)
• Design tables: packages/db/src/schema/merchDesignTables.ts
• Catalog tables: packages/db/src/schema/merchCatalogTables.ts
• Benchmark tables: packages/db/src/schema/merchBenchmarkTables.ts

Enums

19 enums in packages/db/src/schema/merchEnums.ts:

Key Table Details

merch_campaign (normalized)

The campaign table now holds core identity and lifecycle fields. Detail fields have been extracted into satellite tables:

• Core: id, slug, name, talentName, environment, startDate, endDate, isActive, status, shutdownMode, shutdownStartedAt, shutdownEndsAt
• Flags: requireNameOnCard, enableShareButton, enableSaveButton, enableLiveMockups
• Audit: createdAt, updatedAt, deletedAt (soft delete)

Satellite tables (all FK to merch_campaign.id):

Note: Generation-related fields (aiConfig, itemGenerationConfigs, upscaleConfig) have been removed from merch_campaign_config. All AI generation settings now live in merch_design_config.configJson.

merch_session

• Session: id, campaignId (nullable pointer, mutable for campaign adoption), startPage (nullable URL of the origin page), role, createdAt, updatedAt, expiresAt (default now()+24h)
• Photo ref: activeSelfieId (FK to merch_selfie.id — the selfie currently used for generation)
• AI: aiArtKey, aiArtPrompt, aiArtCandidates (json), bgMaskKey, plaqueIllustrationKey (legacy)
• Payment: paymentIntentId, consentGiven
• Tracking: delayedEmail, ipAddress, userAgent

Note: Selfie image data (selfieKey, sourceType, faceDetected, faceError, lightingError, gender, ageGroup, peopleCount) and cart items (cartItems JSON) have been extracted into dedicated merch_selfie and merch_item (originally merch_cart_item) tables. Legacy columns remain on merch_session for backwards compatibility. campaignId is nullable to support multishop journey sessions where the session is created before campaign selection.

merch_selfie

Extracted selfie record, created on every upload/capture. The session's activeSelfieId points to the selfie currently used for generation.

• Identity: id, sessionId
• Image: selfieKey (S3 key), sourceType (selfie | upload)
• Face detection: faceDetected (bool), faceError, lightingError
• Demographics (populated by verifyImageWithGemini): gender, ageGroup, peopleCount
• Audit: createdAt, updatedAt

merch_item

Unified table representing cart items and order items (cart-side rows have order_id IS NULL, order-side rows have it set). Replaces the old merch_session.cartItems JSON array and the deprecated merch_cart_item / merch_order_item tables.

• Identity: id, sessionId, orderId (nullable — set on checkout)
• Refs: selfieId (FK to merch_selfie), designId (FK to merch_design), productId/shopProductId (FK to shop_product)
• Product: productName, designName, catalog product type, size, quantity, personalizationText (stores plaque engraving / custom fanName text)
• Pricing: unitPriceMinorUnit, subtotalMinorUnit (currency inherited from campaign)
• Images: imageKey (media filename or legacy AI art key), cleanImageKey (clean media filename for confirm page)
• Sort: sortOrder
• Audit: createdAt, updatedAt

merch_design

Named design belonging to a campaign. Supports a 3-level hierarchy via the self-referencing parentDesignId FK:

• Identity: id, campaignId
• Hierarchy: parentDesignId (self-referencing FK — non-null = child design)
• Demographics (Level 3 only): gender (merch_gender), ageGroup (merch_age_group)
• Config: name, catalogProductIds (text array — controls which catalog products the client app offers for this design), displayImageAssetId, displayImageUrl, sortOrder, isActive
• Audit: createdAt, updatedAt

Resolution logic: During generation, resolveDesignConfig3Level walks the hierarchy:

1. Loads the Level 1 base config — the first config row on the top-level design, regardless of its productType value (the productType column on Level 1 configs is ignored; whatever config exists is treated as the universal default)
2. Finds a Level 2 product variation matching the requested catalog product ID (if any)
3. Merges Level 2 overrides on top of Level 1
4. Finds a Level 3 demographic variation matching the selfie's gender/ageGroup (if any)
5. Merges Level 3 overrides on top
6. Returns the merged config + effective design ID

Child configs only need to specify overridden fields — empty fields inherit from the parent level.

Legacy compatibility: Direct demographic sub-designs (Level 2 with gender/ageGroup, no intermediate product variation) are still supported as a fallback path.

merch_design_config

AI generation config blob for a design at any level.

• Identity: id, designId (FK to merch_design)
• Scope: productType (nullable — NULL = default config applying to all product types)
• Content: previewImageUrl, configJson (JSONB)
• Audit: createdAt, updatedAt

Level 1 designs have a single config row containing the full generation settings (template image, prompt, AI model, quality tiers, fan location text, overlay, back print). The productType value on this row is ignored at runtime — whatever config exists is treated as the universal default for all products. Legacy data may have a specific productType value (e.g. "tshirt") rather than NULL; this is harmless since the resolver ignores it.

Level 2/3 designs have a config row containing only override fields (template image, prompt, AI model). During resolution these are merged on top of the parent's config.

Unique index: (design_id, product_type) — enforced via manual upsert because PostgreSQL treats NULL as distinct in unique indexes.

merch_session_generation

Caches AI generation output keyed by (sessionId, designId, productType). Enables deduplication and avoids re-generation for the same selfie+design+product combination.

productType is retained in the cache key for fallback-chain compatibility; runtime product/design association uses the selected catalog product ID.

• Identity: id, sessionId, designId, productType
• Selfie ref: selfieId (FK to merch_selfie, nullable for backward compat), selfieKey
• Output: aiArtKey, aiArtPrompt, aiArtCandidates (jsonb), finalImgKey
• Cost: generationCost
• Audit: createdAt, updatedAt

merch_order

• Order: id, orderNumber (unique), campaignId, sessionId, role, status, mode, previewType
• Customer: email, firstName, lastName
• Address: addressLine1/2, city, state, postalCode, country
• Pricing: subtotal, shippingCost, total, currency (ISO 4217 — propagated from merch_campaign_config.currency)
• Payment: paymentIntentId, stripeCustomerId, paymentStatus
• Consent: consentGiven, consentTimestamp
• Content: aiArtKey, sourceType
• Fulfillment: fulfillmentStatus, trackingNumber, holdInfo (json)

JSON Column Shapes

configJson (merch_design_config)

Validated against GarmentDesignConfigSchema or PlaqueDesignConfigSchema (Zod) in packages/merch/srv/src/design-config-schemas.ts.

Garment example (tshirt / hoodie — Level 1 full config):

{
  "templateImageUrl": "https://s3.../merch/template.png",
  "templateImageAssetId": "asset-abc123",
  "prompt": "A vivid portrait of {fanName} as a superhero...",
  "modelEndpoint": "fal-ai/gpt-image-1.5/edit",
  "qualityTiers": ["low", "medium", "high"],
  "fanLocationText": "person on the left",
  "overlayImageUrl": "https://s3.../overlay.png",
  "backPrintImageUrl": "https://s3.../backprint.png"
}

Level 2/3 override example (only overridden fields):

{
  "templateImageUrl": "https://s3.../hoodie-specific-template.png",
  "prompt": "Same style but optimized for hoodie layout..."
}

Note: sourceImageUrl / sourceImageAssetId are deprecated aliases for templateImageUrl / templateImageAssetId. The runtime reads templateImageUrl ?? sourceImageUrl for backward compatibility.

Plaque example:

{
  "prompt": "Create a platinum vinyl record artwork...",
  "qualityTiers": ["low", "low"]
}

qualityTiers is an optional array of "low" | "medium" | "high" values. Each entry produces one generation attempt at that quality level. Examples:

• ["low", "medium", "high"] → 3 attempts (one at each tier) — fans choose from all candidates
• ["low", "low"] → 2 low-quality attempts
• Omitted → falls back to DEFAULT_QUALITY_TIERS = ["low"] (single low-quality generation)

aiArtCandidates (merch_session / merch_session_generation)

[
  { "artKey": "merch/ai-candidates/sess-1-best-1234.png", "url": "https://...", "score": 0.92 },
  { "artKey": "merch/ai-candidates/sess-2-alt-1234.png", "url": "https://...", "score": 0.85 },
  { "artKey": "merch/ai-candidates/sess-3-alt-1234.png", "url": "https://...", "score": 0.71 }
]

Sorted by score (character consistency likeness, 0–1). The fan selects their preferred candidate via POST /api/merch/ai/select.

shippingInfo (merch_session)

{
  "email": "fan@example.com",
  "firstName": "John",
  "lastName": "Doe",
  "addressLine1": "123 Main St",
  "city": "New York",
  "state": "NY",
  "postalCode": "10001",
  "country": "US"
}

plaqueConfig (merch_campaign)

{
  "vinyl": { "x": 200, "y": 200, "size": 280 },
  "prompt": "Create a platinum vinyl record artwork.",
  "fanName": "MARK THOMPSON",
  "standardPricing": { "price": 299, "shipping": 35 },
  "deluxePricing": { "price": 539, "shipping": 35 },
  "standardImages": { "basePlaqueUrl": "...", "recordFrameUrl": "...", "artistRefUrl": "..." },
  "deluxeImages": { "basePlaqueUrl": "...", "recordFrameUrl": "...", "artistRefUrl": "..." }
}

Access Layer

13 access files in packages/db/src/access/merch/:

Design access in merch-design.ts (also in access/merch/):

All exported from packages/db/src/index.ts.

Indexes

• Unique: merch_campaign(slug, talentName), merch_tracking_link(campaignId, slug), merch_campaign_visit(campaignId, ipAddress), merch_ai_model(endpointId), merch_session_analytics(sessionId, campaignId)
• Catalog product sku: catalog_product(sku) unique
• Shop product attachment: shop_product(campaign_id, catalog_product_id) unique
• Design config: merch_design_config(design_id, product_type) — enforced via manual upsert (NULL product_type = default config)
• Sub-design uniqueness: merch_design(parent_design_id, gender, age_group) NULLS NOT DISTINCT WHERE parent_design_id IS NOT NULL (prevents duplicate variations)
• Parent lookup: merch_design(parent_design_id)
• Generation cache: merch_session_generation(session_id, design_id, product_type) unique
• Selfie lookup: merch_selfie(session_id), merch_selfie(selfie_key)
• Cart lookup: merch_cart_item(session_id), merch_cart_item(selfie_id), merch_cart_item(product_id)
• Soft delete filter: merch_campaign(deletedAt) — queries filter deletedAt IS NULL
• IP dedup: merch_campaign_visit uses onConflictDoNothing() for upsert
• Atomic counters: tracking link visit uses SET visitCount = visitCount + 1

Migrations

Data migrations run automatically on deploy via packages/db/src/migrate.ts → runDataMigrations(). The registry is in packages/db/src/data-migrations/registry.ts.

S3 Key Patterns