Database Schema

Tables Overview

14 tables in packages/db/src/schema/merchTables.ts, migration 0017_conscious_sleepwalker.sql.

Enums

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

Key Table Details

merch_campaign (77 columns)

• Core: id, slug, name, talentName, environment, startDate, endDate, isActive, status, shutdownMode, shutdownStartedAt, shutdownEndsAt
• Assets (FK to merch_asset): partnerLogoAssetId, heroImageAssetId, studioSlide1-3ImageAssetId
• Branding: primaryColor (#465d91 default), secondaryColor (#dce2f9 default)
• Copy: desktopTitle, cameraPermissionText, progressIndicatorType, thankYouMessage, successHeadline, emailSubjectLine, shareTextTemplate, supportEmail
• Studio: studioHeadline, studioSelfieButtonLabel, studioUploadButtonLabel, studioBackLabel, studioSlide1-3Caption, studioVerificationHeadline/Subtext
• Legal: legalFooterConsentType, terms/privacy/cookie title+content+updatedAt
• Config: allowedCountries, currency, shippingFlatRate, shippingIntlRate, paymentMethods, mobileProductGrid
• JSON columns: aiConfig, upscaleConfig, itemGenerationConfigs, plaqueConfig, fanLocationText
• Flags: requireNameOnCard, enableShareButton, enableSaveButton, enableLiveMockups
• Audit: createdAt, updatedAt, deletedAt (soft delete)

merch_session (22 columns)

• Session: id, campaignId, role, createdAt, updatedAt, expiresAt (default now()+24h)
• Photo: selfieKey, sourceType, faceDetected, faceError, lightingError
• AI: aiArtKey, aiArtPrompt, aiArtCandidates (json), plaqueIllustrationKey
• Cart: previewType, cartItems (json), shippingInfo (json)
• Payment: paymentIntentId, consentGiven
• Tracking: delayedEmail, ipAddress, userAgent

merch_order (31 columns)

• 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
• Payment: paymentIntentId, stripeCustomerId, paymentStatus
• Consent: consentGiven, consentTimestamp
• Content: aiArtKey, sourceType
• Fulfillment: fulfillmentStatus, trackingNumber, holdInfo (json)

JSON Column Shapes

aiConfig (merch_campaign)

{
  "endpoint": "fal-ai/gpt-image-1.5/edit",
  "modelName": "GPT-Image 1.5",
  "imageField": "image_urls",
  "costPerImage": 0.001,
  "params": { "sync_mode": false, "aspect_ratio": "auto", "output_format": "png" }
}

aiArtCandidates (merch_session)

[
  { "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 }
]

Stores all generation candidates sorted by score (character consistency likeness, 0–1). The client uses this array to render the ArtSelectionSection carousel. The fan's selected candidate is written to aiArtKey via POST /api/merch/ai/select.

cartItems (merch_session)

[{
  "productId": "abc123",
  "productName": "T-Shirt",
  "type": "tshirt",
  "size": "L",
  "quantity": 1,
  "unitPrice": 69.0,
  "subtotal": 69.0,
  "imageKey": "merch/ai-candidates/sess-1-best-1234.png"
}]

The imageKey field (optional) stores the S3 key of the AI art associated with a specific cart item, enabling per-item art display in the CartResultView.

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

11 access files in packages/db/src/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)
• 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

S3 Key Patterns