--- url: /api/routes-reference.md description: >- Comprehensive reference for all API endpoints and page routes in the UberLotto v2 application, including request/response formats, authentication, and security measures. --- # API Routes Reference ## Overview UberLotto uses React Router v7 file-based routing with the `($locale).` prefix convention for i18n support. Routes are organized as: * **API routes** (`api.*`) — Backend endpoints for payments, data, and integrations * **Page routes** (`($locale).*`) — User-facing pages with server-side loaders * **SEO routes** — Sitemaps, robots.txt, and other crawler-facing responses ### Route File Naming Convention | Pattern | Example | URL | |---|---|---| | `api.name.ts` | `api.plisio-invoice.ts` | `/api/plisio-invoice` | | `api.name.$param.ts` | `api.get-product.$handle.ts` | `/api/get-product/:handle` | | `($locale).name.tsx` | `($locale)._index.tsx` | `/` or `/:locale` | | `[name].tsx` | `[robots.txt].tsx` | `/robots.txt` | *** ## Payment API Endpoints ### POST `/api/plisio-invoice` Creates a cryptocurrency payment invoice via the Plisio gateway. **File:** `app/routes/api.plisio-invoice.ts` | Property | Details | |---|---| | **Methods** | POST (action), OPTIONS (loader) | | **Authentication** | Required — Shopify Customer Account login (`customerAccount.isLoggedIn()`) | | **CORS** | Origin-restricted via `getAllowedOrigin()` (same-origin + localhost in dev) | | **Rate Limiting** | Max 5 pending transactions per email; max 3 transactions per minute per email | **Request Body:** ```json { "amount": 50, "email": "user@example.com", "customer_name": "John", "customer_lastname": "Doe", "customer_id": "gid://shopify/Customer/123" } ``` **Response (200):** ```json { "success": true, "data": { "txn_id": "abc123", "invoice_url": "https://plisio.net/invoice/abc123", "amount": "0.025", "wallet_hash": "0x...", "currency": "ETH", "source_currency": "USD", "source_rate": "2000.00", "qr_code": "data:image/png;base64,...", "order_number": "UL-1234567890", "expected_confirmations": 3 } } ``` **Security Measures:** * Customer must be logged in (`context.customerAccount.isLoggedIn()`) * Amount validation with precision exploit prevention (NaN, Infinity, scientific notation) * Email format validation (RFC 5321 limit: 254 chars) * Failed validation logged to Supabase security events * Pending transaction saved to `payment_transactions` table before invoice creation; updated with Plisio's `provider_txn_id` and `provider_invoice_url` after invoice is created * CORS restricted to same-origin and localhost in development via shared `getAllowedOrigin()` * No-cache response headers *** ### GET/POST `/api/plisio-webhook` Handles Plisio payment notification callbacks with a 6-layer defense-in-depth security model. **File:** `app/routes/api.plisio-webhook.ts` | Property | Details | |---|---| | **Methods** | GET (loader), POST (action) | | **Authentication** | HMAC signature verification | | **Rate Limiting** | IP-based, per-transaction, and global rate limits | **Security Layers:** | Layer | Protection | Response on Failure | |---|---|---| | 1 | IP Whitelisting (if `PLISIO_WEBHOOK_IPS` configured) | 403 Forbidden | | 2 | Rate Limiting (IP + global) | 429 / 503 | | 3 | HMAC Signature Validation | 401 Unauthorized | | 4 | Replay Attack Prevention (nonce-based) | 409 Conflict | | 5 | Transaction Rate Limiting | 429 Too Many Requests | | 6 | Duplicate Transaction Check (database) | 200 Already Processed | **Processing:** After passing all security layers, the webhook payload is processed and the transaction is saved to Supabase. Amount and currency validation occur during processing. ::: warning Both GET and POST handlers implement the full 6-layer security stack. GET support exists for backward compatibility with older webhook integrations. ::: *** ### GET `/api/plisio-status` Polls the current status of a Plisio cryptocurrency transaction. **File:** `app/routes/api.plisio-status.ts` | Property | Details | |---|---| | **Methods** | GET (loader), OPTIONS (action) | | **Authentication** | Required — Shopify Customer Account login (`customerAccount.isLoggedIn()`) | | **CORS** | Origin-restricted via `getAllowedOrigin()` (same-origin + localhost in dev) | | **Rate Limiting** | None | **Query Parameters:** | Parameter | Required | Description | |---|---|---| | `txn_id` | Yes | Plisio transaction ID | **Response (200):** ```json { "success": true, "data": { "txn_id": "abc123", "status": "confirmed", "plisio_status": "completed", "amount": "0.025", "currency": "ETH", "order_number": "UL-1234567890", "tx_url": "https://etherscan.io/tx/0x..." } } ``` **Status Mapping:** Plisio statuses are mapped to internal `PaymentStatus` values (`completed`, `pending`, `error`). The `payment_transactions` record in Supabase is looked up by `provider_txn_id` and updated on each status check. *** ### POST `/api/moonpay-sign` Signs MoonPay widget URLs using HMAC-SHA256 for secure fiat-to-crypto payment integration. **File:** `app/routes/api.moonpay-sign.ts` | Property | Details | |---|---| | **Methods** | POST (action), OPTIONS (loader) | | **Authentication** | Required — Shopify Customer Account login | | **Rate Limiting** | IP-based (via Supabase) | **Request Body:** ```json { "url": "https://buy.moonpay.com/?apiKey=...¤cyCode=ETH" } ``` **Response (200):** ```json { "success": true, "signature": "base64-encoded-hmac-sha256" } ``` **Security Measures:** * Customer must be logged in (`context.customerAccount.isLoggedIn()`) * URL host validation — only allows `buy.moonpay.com`, `buy-sandbox.moonpay.com`, `sell.moonpay.com`, `sell-sandbox.moonpay.com` * CORS restricted to same-origin and localhost in development via shared `getAllowedOrigin()` * HMAC-SHA256 signing via Web Crypto API (Cloudflare Workers compatible) * IP-based rate limiting (via Supabase, when configured) **Transaction Pre-Creation:** Before signing the URL, the endpoint pre-creates a pending `payment_transactions` record in Supabase using parameters extracted from the MoonPay widget URL (`externalTransactionId`, `externalCustomerId`, `baseCurrencyAmount`, `baseCurrencyCode`, `walletAddress`). This allows the MoonPay webhook to match incoming notifications to existing transactions via `order_number` (= `externalTransactionId`). If pre-creation fails, the URL is still signed (non-blocking). *** ### POST `/api/moonpay-webhook` Handles MoonPay transaction notification webhooks with a 5-layer security pipeline and full transaction lifecycle management. **File:** `app/routes/api.moonpay-webhook.ts` | Property | Details | |---|---| | **Methods** | POST (action) | | **Authentication** | HMAC-SHA256 signature via `Moonpay-Signature-V2` header | | **Rate Limiting** | IP-based (100/min) and global (1000/min) | **Security Layers:** | Layer | Protection | Response on Failure | |---|---|---| | 1 | IP rate limiting (100 req/min per IP) | 429 Too Many Requests | | 2 | Global rate limiting (1000 req/min) | 503 Service Unavailable | | 3 | HMAC-SHA256 signature verification (timestamp freshness + constant-time comparison) | 401 Unauthorized | | 4 | Replay attack prevention (nonce-based via `checkGenericWebhookNonce()`) | 200 Duplicate acknowledged | | 5 | Payload structure validation + security event logging | 200 (logged) | **HMAC-SHA256 Verification Details:** * Header format: `Moonpay-Signature-V2: t=TIMESTAMP,s=SIGNATURE` * Signed payload: `TIMESTAMP.BODY` (timestamp + literal period + raw body) * Signature encoding: hex (NOT base64) * Timestamp freshness: rejects webhooks older than 5 minutes * Comparison: XOR-based constant-time function **Replay Protection:** * Nonce string: `SHA-256(moonpay_txn_id:status:event_type)` * Uses `checkGenericWebhookNonce()` from shared `replay-protection.server.ts` * Stores nonces in shared `webhook_nonces` table with 5-minute TTL * Fail-open on database errors **Transaction Processing:** After passing security layers, the webhook processes transactions into the `payment_transactions` table using a three-step lookup strategy: 1. Look up by `provider_txn_id` (MoonPay's internal ID) — matches previously updated records 2. Look up by `order_number` (= `externalTransactionId`) — matches pre-created pending records from the sign endpoint 3. If neither found — create a new transaction record **Status Mapping:** | MoonPay Status | Internal `PaymentStatus` | |---|---| | `completed` | `completed` | | `failed` | `failed` | | `waitingPayment`, `waitingAuthorization` | `processing` | | `pending` | `pending` | **Payload Validation:** * Handles MoonPay sending `data` as a JSON string (double-parse) * Validates required fields: `type`, `data.id` **Webhook Event Types:** `transaction_created`, `transaction_updated`, `transaction_failed` ::: tip Returns 200 OK even on internal errors to prevent MoonPay from retrying, per MoonPay's best practices documentation. ::: *** ### POST `/api/shopify-checkout` Creates a Shopify checkout cart for credit loading (UBL Points purchases). **File:** `app/routes/api.shopify-checkout.ts` | Property | Details | |---|---| | **Methods** | POST (action) | | **Authentication** | Required — Shopify Customer Account (`handleAuthStatus()`) | | **Rate Limiting** | Relies on Shopify's built-in API rate limits | **Request Body:** ```json { "amount": "50", "email": "user@example.com", "customerAccessToken": "token" } ``` **Response (200):** ```json { "success": true, "checkoutUrl": "https://checkout.shopify.com/...", "cart": { "id": "gid://shopify/Cart/123", "totalQuantity": 1, "totalAmount": "50.00", "currencyCode": "USD" } } ``` **Security Measures:** * Content-Type validation (must be `application/json`) * Request body size limit (1KB max) * CSRF protection via origin/referer validation * Customer authentication required * Comprehensive request body validation * Price manipulation prevention (variant price vs. requested amount check with 1 cent tolerance) **Error Codes:** `METHOD_NOT_ALLOWED`, `INVALID_CONTENT_TYPE`, `PAYLOAD_TOO_LARGE`, `MISSING_ORIGIN`, `INVALID_ORIGIN`, `AUTHENTICATION_REQUIRED`, `INVALID_JSON`, `VALIDATION_FAILED`, `AMOUNT_UNAVAILABLE`, `PRICE_MISMATCH`, `STOREFRONT_UNAVAILABLE`, `CHECKOUT_FAILED` *** ## Data API Endpoints ### GET/POST `/api/wallet-transactions` CRUD operations for payment transaction records in the `payment_transactions` table. **File:** `app/routes/api.wallet-transactions.ts` | Property | Details | |---|---| | **Methods** | GET (loader), POST (action), OPTIONS | | **Authentication** | Required — Shopify Customer Account login (`customerAccount.isLoggedIn()`) | | **CORS** | Origin-restricted via `getAllowedOrigin()` (same-origin + localhost in dev) | | **Rate Limiting** | In-memory: 30 requests per minute per IP | #### GET — Fetch Transaction History Fetches transaction history for the authenticated user from `payment_transactions`. **Query Parameters:** | Parameter | Required | Description | |---|---|---| | `email` | One of email/wallet | User email address | | `wallet_address` | One of email/wallet | Ethereum wallet address (`0x...`) | | `limit` | No | Max results (default: 50) | **Response:** ```json { "transactions": [ { "id": "uuid", "user_email": "john@example.com", "order_number": "UL-1234567890", "provider": "plisio", "provider_txn_id": "abc123", "amount": 50.00, "amount_usd": 50.00, "currency": "USD", "crypto_currency": "ETH", "wallet_address": "0x...", "tx_hash": "0x...", "status": "completed", "created_at": "2025-01-15T10:30:00Z", "completed_at": "2025-01-15T10:35:00Z" } ] } ``` #### POST — Create Transaction Creates a new record in the `payment_transactions` table. This endpoint is primarily for server-side/internal use — transactions are normally created by the invoice and webhook flows. **Request Body Fields:** | Field | Required | Type | Validation | |---|---|---|---| | `user_email` | Yes | string | Email format | | `order_number` | Yes | string | Non-empty | | `provider` | Yes | string | Must be `moonpay` or `plisio` | | `amount` | Yes | number | Positive number | | `user_name` | No | string | Customer first name | | `user_lastname` | No | string | Customer last name | | `user_id` | No | string | Customer ID | | `provider_txn_id` | No | string | Provider's transaction ID | | `provider_invoice_url` | No | string | Provider's invoice URL | | `amount_usd` | No | number | Positive number | | `currency` | No | string | Defaults to `USD` | | `crypto_currency` | No | string | Crypto currency code | | `wallet_address` | No | string | Ethereum address format (`0x` + 40 hex chars) | | `tx_hash` | No | string | Transaction hash | | `conversion_rate` | No | number | Positive number | | `status` | No | string | One of: `pending`, `processing`, `awaiting_confirmation`, `completed`, `failed`, `cancelled`, `expired`, `refunded`, `partially_refunded`, `error` | | `metadata` | No | object | Arbitrary JSON metadata | | `expires_at` | No | string | ISO 8601 expiration timestamp | **Security Headers:** All responses include `X-Content-Type-Options: nosniff`, `X-Frame-Options: DENY`, `X-XSS-Protection: 1; mode=block`. *** ### GET `/api/get-product/:handle` Fetches full product details with lottery drawing data. **File:** `app/routes/api.get-product.$handle.ts` | Property | Details | |---|---| | **Methods** | GET (loader) | | **Authentication** | None | | **Rate Limiting** | None | **URL Parameters:** | Parameter | Description | |---|---| | `handle` | Shopify product handle (e.g., `powerball-ticket`) | **Response:** Returns product data from Shopify Storefront API including variants, options, selected variant, and enriched drawing data fetched from Supabase `jackpots` table using the product's `custom.game_slug` metafield. *** ### GET `/api/get-product-variants/:handle` Fetches product variants and options only (lightweight alternative to the full product endpoint). **File:** `app/routes/api.get-product-variants.$handle.ts` | Property | Details | |---|---| | **Methods** | GET (loader) | | **Authentication** | None | | **Rate Limiting** | None | **Response:** Returns `{ variants, options, handle }` — variant pricing, availability, and product options from Shopify Storefront API. *** ### GET `/api/predictive-search` Provides search autocomplete results from Shopify's Predictive Search API. **File:** `app/routes/($locale).api.predictive-search.tsx` | Property | Details | |---|---| | **Methods** | GET (loader) | | **Authentication** | None | | **Rate Limiting** | None | **Query Parameters:** | Parameter | Default | Description | |---|---|---| | `q` | (empty) | Search term | | `limit` | 10 | Max results | | `type` | `ANY` | Comma-separated types (currently only `PRODUCT`) | **Response:** Normalized search results with product data including lottery-specific metafields (`game_slug`, `lottery_ticket_multiplier`, `lottery_pool_cutoff_time`). **Caching:** 60 seconds when a search term is provided; 3600 seconds for empty queries. *** ## Utility API Endpoints ### POST `/api/cleanup-pending-transactions` Expires stale pending transactions to prevent rate limit blockage. Designed for automated cron jobs. **File:** `app/routes/api.cleanup-pending-transactions.ts` | Property | Details | |---|---| | **Methods** | POST (action), OPTIONS (loader) | | **Authentication** | Bearer token (`CLEANUP_SECRET_TOKEN` env var) | | **Rate Limiting** | None (token-protected) | **Request:** ```bash curl -X POST https://your-domain.com/api/cleanup-pending-transactions \ -H "Authorization: Bearer YOUR_CLEANUP_SECRET_TOKEN" ``` **Response (200):** ```json { "success": true, "data": { "expiredCount": 3, "expirationTime": "2025-01-15T12:00:00Z" } } ``` **Behavior:** Expires pending `payment_transactions` older than 60 minutes by updating their status from `pending` to `expired`. Uses constant-time string comparison for token validation to prevent timing attacks. *** ### POST `/api/seed-transactions` Seeds dummy transaction data for development and testing. **File:** `app/routes/api.seed-transactions.ts` | Property | Details | |---|---| | **Methods** | POST (action), OPTIONS | | **Authentication** | None | | **Rate Limiting** | None | ::: danger Development/testing endpoint only. Should be disabled or protected in production environments. ::: **Query Parameters:** | Parameter | Required | Description | |---|---|---| | `email` | No | Target email for transactions (default: generates for multiple dummy users) | **Behavior:** * Without `email`: Creates 10 transactions for various dummy users * With `email`: Creates 5-6 transactions for the specified user *** ## Infrastructure Endpoints ### POST `/api/:version/graphql.json` Proxies requests to the Shopify Storefront API. Used by Shopify's checkout system. **File:** `app/routes/($locale).api.$version.[graphql.json].tsx` | Property | Details | |---|---| | **Methods** | POST (action) | | **Authentication** | Passed through from client headers | | **Rate Limiting** | Shopify's built-in limits | **Behavior:** Forwards the request body and headers directly to `https://{PUBLIC_CHECKOUT_DOMAIN}/api/{version}/graphql.json` and returns the response as-is. *** ### GET `/robots.txt` Generates a dynamic robots.txt with Shopify-standard disallow rules. **File:** `app/routes/[robots.txt].tsx` | Property | Details | |---|---| | **Methods** | GET (loader) | | **Caching** | 24 hours (`max-age=86400`) | **Disallow Rules Include:** `/admin`, `/cart`, `/orders`, `/checkouts/`, `/account`, collection sort/filter parameters, preview parameters, and search pages. **Bot-Specific Rules:** * `adsbot-google` — Checkout/order restrictions * `Nutch` — Fully blocked * `AhrefsBot` / `AhrefsSiteAudit` — 10-second crawl delay * `MJ12bot` — 10-second crawl delay * `Pinterest` — 1-second crawl delay *** ### GET `/sitemap.xml` Returns the sitemap index using Shopify Hydrogen's built-in `getSitemapIndex`. **File:** `app/routes/($locale).[sitemap.xml].tsx` | Property | Details | |---|---| | **Methods** | GET (loader) | | **Caching** | 24 hours (`max-age=86400`) | *** ### GET `/sitemap/:type/:page.xml` Returns paginated sitemap pages with locale support. **File:** `app/routes/($locale).sitemap.$type.$page[.xml].tsx` | Property | Details | |---|---| | **Methods** | GET (loader) | | **Caching** | 24 hours (`max-age=86400`) | | **Locales** | `EN-US`, `EN-CA`, `FR-CA` | **URL Pattern:** Links are generated as `{baseUrl}/{locale}/{type}/{handle}` for localized content, or `{baseUrl}/{type}/{handle}` when no locale is specified. *** ## Key Page Routes The following are the primary user-facing page routes. All use the `($locale).` prefix for i18n support. ### Public Pages | Route File | URL Path | Purpose | |---|---|---| | `($locale)._index.tsx` | `/` | Homepage | | `($locale).collections._index.tsx` | `/collections` | All collections listing | | `($locale).collections.$handle.tsx` | `/collections/:handle` | Single collection page | | `($locale).collections.all.tsx` | `/collections/all` | All products | | `($locale).products.$handle.tsx` | `/products/:handle` | Product detail page | | `($locale).cart.tsx` | `/cart` | Shopping cart | | `($locale).search.tsx` | `/search` | Search results | | `($locale).pages.faq.tsx` | `/pages/faq` | FAQ page | | `($locale).pages.how-to-play.tsx` | `/pages/how-to-play` | How to play guide | | `($locale).pages.about-us.tsx` | `/pages/about-us` | About page | | `($locale).pages.contact.tsx` | `/pages/contact` | Contact page | | `($locale).pages.past-drawings.tsx` | `/pages/past-drawings` | Past lottery drawings | | `($locale).pages.games-schedule.tsx` | `/pages/games-schedule` | Game schedule | ### Game Pages | Route File | URL Path | Purpose | |---|---|---| | `($locale).pages.game-detail.tsx` | `/pages/game-detail` | Lottery game detail | | `($locale).pages.game-pay.tsx` | `/pages/game-pay` | Game payment page | | `($locale).pages.scratch-collection.tsx` | `/pages/scratch-collection` | Scratch card collection | | `($locale).pages.scratch-detail.tsx` | `/pages/scratch-detail` | Scratch card detail | ### Account Pages (Authenticated) | Route File | URL Path | Purpose | |---|---|---| | `($locale).account.tsx` | `/account` | Account layout wrapper | | `($locale).account._index.tsx` | `/account` | Account dashboard | | `($locale).account.profile.tsx` | `/account/profile` | Profile management | | `($locale).account.addresses.tsx` | `/account/addresses` | Address management | | `($locale).account.orders._index.tsx` | `/account/orders` | Order history | | `($locale).account.orders.$id.tsx` | `/account/orders/:id` | Order detail | | `($locale).account.load-credit.tsx` | `/account/load-credit` | Load UBL credits | | `($locale).account.payment-history.tsx` | `/account/payment-history` | Payment history | ### Auth Pages | Route File | URL Path | Purpose | |---|---|---| | `($locale).account_.login.tsx` | `/account/login` | Login page | | `($locale).account_.logout.tsx` | `/account/logout` | Logout handler | | `($locale).account_.authorize.tsx` | `/account/authorize` | OAuth callback | ### Other Pages | Route File | URL Path | Purpose | |---|---|---| | `($locale).blogs._index.tsx` | `/blogs` | Blog listing | | `($locale).blogs.$blogHandle._index.tsx` | `/blogs/:blogHandle` | Blog posts | | `($locale).blogs.$blogHandle.$articleHandle.tsx` | `/blogs/:blogHandle/:articleHandle` | Blog article | | `($locale).policies._index.tsx` | `/policies` | Policies listing | | `($locale).policies.$handle.tsx` | `/policies/:handle` | Policy page | | `($locale).discount.$code.tsx` | `/discount/:code` | Discount code handler | | `($locale).offline.tsx` | `/offline` | PWA offline fallback | | `($locale).$.tsx` | `/*` | Catch-all 404 page | *** ## Error Response Conventions All API endpoints follow consistent error response patterns: ```json { "success": false, "error": "Human-readable error message", "code": "MACHINE_READABLE_CODE" } ``` ### Common HTTP Status Codes | Code | Meaning | Used By | |---|---|---| | 200 | Success | All endpoints | | 201 | Created | wallet-transactions POST, seed-transactions | | 400 | Bad Request / Validation Error | All POST endpoints | | 401 | Unauthorized | Webhook endpoints, checkout, moonpay-sign | | 403 | Forbidden | Webhook (IP whitelist), checkout (CSRF) | | 405 | Method Not Allowed | Wrong HTTP method | | 409 | Conflict | Webhook (replay detection) | | 413 | Payload Too Large | checkout, wallet-transactions | | 415 | Unsupported Media Type | checkout (wrong Content-Type) | | 429 | Too Many Requests | Invoice, webhook, wallet-transactions | | 500 | Internal Server Error | Unhandled errors | | 502 | Bad Gateway | Plisio API failures | | 503 | Service Unavailable | Missing Supabase config, global rate limit | --- --- url: /deployment/ci-cd.md description: >- Complete reference for UberLotto's GitHub Actions CI/CD pipelines, deployment workflows, secrets management, and branch strategy. --- # CI/CD Workflows ## Overview UberLotto uses GitHub Actions for continuous integration and deployment to Shopify Oxygen. The repository contains **two deployment workflows** that run in parallel on every push, deploying to two separate Shopify storefronts. ## Workflow Files Both workflows are located in `.github/workflows/`: | File | Name | Deployment Token Secret | |---|---|---| | `oxygen-deployment-1000042728.yml` | Storefront 1000042728 | `OXYGEN_DEPLOYMENT_TOKEN_1000042728` | | `oxygen-deployment-1000083568.yml` | Storefront 1000083568 | `OXYGEN_DEPLOYMENT_TOKEN_1000083568` | ## Workflow Configuration Both workflows share an identical structure. Below is the annotated configuration: ```yaml name: Storefront 1000042728 # Unique name per workflow on: [push] # Triggers on every push to any branch permissions: contents: read # Read repository code deployments: write # Create GitHub deployment records jobs: deploy: name: Deploy to Oxygen timeout-minutes: 30 # Fail if build exceeds 30 minutes runs-on: ubuntu-latest # GitHub-hosted runner steps: # Step 1: Checkout the repository - uses: actions/checkout@v4 # Step 2: Setup Node.js (latest LTS version) - name: Setup node.js uses: actions/setup-node@v4 with: node-version: "lts/*" check-latest: true # Step 3: Cache npm modules for faster builds - name: Cache node modules id: cache-npm uses: actions/cache@v4 env: cache-name: cache-node-modules with: path: ~/.npm key: ${{ runner.os }}-build-${{ env.cache-name }}-${{ hashFiles('**/package-lock.json') }} restore-keys: | ${{ runner.os }}-build-${{ env.cache-name }}- ${{ runner.os }}-build- ${{ runner.os }}- # Step 4: Install dependencies using clean install - name: Install dependencies run: npm ci # Step 5: Build and deploy to Shopify Oxygen - name: Build and Publish to Oxygen run: npx shopify hydrogen deploy env: SHOPIFY_HYDROGEN_DEPLOYMENT_TOKEN: ${{ secrets.OXYGEN_DEPLOYMENT_TOKEN_1000042728 }} ``` ### Workflow Steps Explained | Step | Action | Purpose | |---|---|---| | Checkout | `actions/checkout@v4` | Clone the repository at the pushed commit | | Setup Node.js | `actions/setup-node@v4` | Install Node.js LTS with latest patch | | Cache | `actions/cache@v4` | Cache `~/.npm` directory keyed by `package-lock.json` hash | | Install | `npm ci` | Clean install from lockfile (deterministic) | | Deploy | `npx shopify hydrogen deploy` | Build the Hydrogen app and push to Oxygen | ## Trigger Behavior ### When Workflows Run Both workflows trigger on **every `push` event** to any branch: ```yaml on: [push] ``` This means: * Pushing to `main` → **Production deployment** to both storefronts * Pushing to any other branch → **Preview deployment** to both storefronts * Both workflows execute **in parallel** ### Branch-to-Environment Mapping | Action | Storefront 1000042728 | Storefront 1000083568 | |---|---|---| | Push to `main` | Production deploy | Production deploy | | Push to `feature/*` | Preview deploy | Preview deploy | | Push to `develop` | Preview deploy | Preview deploy | | Push to `hotfix/*` | Preview deploy | Preview deploy | ::: tip Shopify Oxygen determines the target environment (production vs. preview) based on the branch configuration in your Shopify admin, not the workflow file. ::: ## Required GitHub Secrets ### Current Secrets | Secret Name | Used By | Description | |---|---|---| | `OXYGEN_DEPLOYMENT_TOKEN_1000042728` | Workflow 1 | Oxygen deployment token for Storefront 1000042728 | | `OXYGEN_DEPLOYMENT_TOKEN_1000083568` | Workflow 2 | Oxygen deployment token for Storefront 1000083568 | ### How to Add or Update Secrets 1. Navigate to your GitHub repository 2. Go to **Settings → Secrets and variables → Actions** 3. Click **New repository secret** (or update an existing one) 4. Enter the secret name exactly as shown above 5. Paste the token value from Shopify Admin 6. Click **Add secret** ### Obtaining Deployment Tokens 1. Go to **Shopify Admin → Sales channels → Hydrogen** 2. Select the target storefront (by its ID) 3. Navigate to **Settings → Deployment** 4. Copy the deployment token 5. Add it as a GitHub secret with the matching name ::: warning Deployment tokens are storefront-specific. Each storefront requires its own token. If you regenerate a token in Shopify, update the corresponding GitHub secret immediately. ::: ## Branch Strategy ### Recommended Branch Flow ``` main (production) │ ├── develop (staging/preview) │ │ │ ├── feature/new-payment-method │ ├── feature/ui-redesign │ └── bugfix/cart-total │ └── hotfix/critical-fix (urgent production fixes) ``` ### Branch Purposes | Branch | Purpose | Deploys To | |---|---|---| | `main` | Production-ready code | Production environment | | `develop` | Integration branch for features | Preview environment | | `feature/*` | Individual feature development | Preview environment | | `bugfix/*` | Non-urgent bug fixes | Preview environment | | `hotfix/*` | Urgent production fixes | Preview (then merge to `main`) | ### Merge Flow 1. Create feature branch from `develop` 2. Develop and test using preview deployments 3. Merge feature branch into `develop` 4. Test integration in `develop` preview 5. Merge `develop` into `main` for production release ## Build Pipeline Details ### What `shopify hydrogen deploy` Does The deploy command performs these steps internally: 1. **GraphQL Codegen** — Generates TypeScript types from Storefront API queries 2. **Vite Build** — Compiles React components, bundles CSS (Tailwind v4), tree-shakes imports 3. **Worker Bundle** — Creates the Cloudflare Worker edge bundle (`dist/worker/index.js`) 4. **Client Assets** — Produces hashed static assets, PWA manifest, and service worker 5. **Upload** — Pushes the build artifacts to Shopify Oxygen 6. **Activate** — Makes the deployment live (production) or generates a preview URL ### Build Artifacts ``` dist/ ├── client/ │ ├── assets/ # Hashed JS/CSS bundles │ ├── manifest.webmanifest # PWA manifest │ └── sw.js # Service worker └── worker/ └── index.js # Edge worker entry point ``` ## Pre-Deployment Checklist Before merging to `main` for a production deployment: * \[ ] TypeScript compiles without errors (`npm run typecheck`) * \[ ] ESLint shows no errors (`npm run lint`) * \[ ] Application builds successfully (`npm run build`) * \[ ] Preview deployment tested and verified * \[ ] Environment variables verified in Oxygen (`shopify hydrogen env list`) * \[ ] Database migrations applied (if any) via Supabase dashboard * \[ ] No secrets or `.env` files committed ## Monitoring and Debugging ### Viewing Workflow Runs 1. Go to your GitHub repository 2. Click the **Actions** tab 3. Both workflows appear as separate entries 4. Click a workflow run to see logs for each step ### Common CI Failure Causes | Symptom | Likely Cause | Fix | |---|---|---| | `SHOPIFY_HYDROGEN_DEPLOYMENT_TOKEN` error | Missing or expired secret | Regenerate token in Shopify and update GitHub secret | | `npm ci` failure | Lockfile mismatch | Run `npm install` locally and commit updated `package-lock.json` | | TypeScript errors | Type errors in code | Run `npm run typecheck` locally and fix errors | | Timeout (>30 min) | Large dependencies or build loop | Check for circular imports; review recent dependency additions | | One workflow fails, other succeeds | Token issue for that specific storefront | Verify the failing workflow's secret token | ### Re-running Failed Workflows 1. Navigate to the failed workflow run in GitHub Actions 2. Click **Re-run all jobs** or **Re-run failed jobs** 3. Both workflows can be re-run independently ## Adding New Workflows To add a new Oxygen deployment for a third storefront: 1. Copy an existing workflow file: ```bash cp .github/workflows/oxygen-deployment-1000083568.yml \ .github/workflows/oxygen-deployment-NEW_ID.yml ``` 2. Update the workflow name and token reference: ```yaml name: Storefront NEW_ID # ... env: SHOPIFY_HYDROGEN_DEPLOYMENT_TOKEN: ${{ secrets.OXYGEN_DEPLOYMENT_TOKEN_NEW_ID }} ``` 3. Add the new secret `OXYGEN_DEPLOYMENT_TOKEN_NEW_ID` in GitHub Settings 4. Commit and push the new workflow file ::: tip Keep workflow filenames consistent with the pattern `oxygen-deployment-{storefront-id}.yml` for easy identification. ::: --- --- url: /products/product-creation.md description: >- Step-by-step guide for creating lottery game products in Shopify and connecting them to Supabase data via the game_slug metafield. --- # Creating Lottery Products This guide covers the complete workflow for adding a new lottery game to UberLotto — from Supabase database setup to Shopify product creation and verification. ## Overview ### How the System Works UberLotto uses two systems that work together: | System | Purpose | Data Stored | | --- | --- | --- | | **Shopify** | Product management, checkout, storefront | Product details, pricing, images, variants, metafields | | **Supabase** | Real-time lottery data | Jackpot amounts, past drawing results | ### Data Flow ``` ┌──────────────────┐ ┌──────────────────┐ │ SHOPIFY │ │ SUPABASE │ │──────────────────│ │──────────────────│ │ - Product Title │ │ - Jackpot Amount │ │ - Description │ │ - Past Drawings │ │ - Images │ │ - Drawing Dates │ │ - Pricing/Variants│ │ - Winning Numbers│ │ - game_slug │◄───────►│ - slug │ │ (metafield) │ MATCH │ (column) │ └──────────────────┘ └──────────────────┘ │ │ ▼ ▼ ┌────────────────────────────────────────────────┐ │ UBERLOTTO WEBSITE │ │────────────────────────────────────────────────│ │ Combines Shopify product data with Supabase │ │ jackpot/drawing data using matching slugs │ └────────────────────────────────────────────────┘ ``` ::: danger CRITICAL The `custom.game_slug` metafield in Shopify must **exactly match** the `slug` column in the Supabase `jackpots` and `past_drawings` tables. This is the **only** identifier that connects the two systems. There is no `game_type` numeric ID — the system uses text slugs exclusively. ::: ### Key Codebase Files | File | Purpose | | --- | --- | | `app/utils/jackpot.server.ts` | Fetches jackpot data from Supabase by slug | | `app/utils/pastDrawings.server.ts` | Fetches past drawing data by slug | | `app/services/lottery-product-enrichment.service.ts` | Matches products to jackpot data via `gameSlug` | | `app/lib/settings.ts` | Default collection ID and display settings | | `app/shared-types/supabase.ts` | TypeScript types for Supabase tables | ## Prerequisites Before adding your first product, ensure the `custom.game_slug` metafield definition exists in Shopify: 1. Go to **Shopify Admin > Settings > Custom data** 2. Click **Products** 3. Click **Add definition** 4. Configure: | Field | Value | | --- | --- | | Name | Game Slug | | Namespace and key | `custom.game_slug` | | Type | **Single line text** | | Description | Unique identifier matching the Supabase slug (e.g., "powerball") | 5. Click **Save** ::: tip This only needs to be done once per Shopify store. If the metafield definition already exists, skip to the next section. ::: ## Step 1: Supabase Setup Before creating the Shopify product, ensure game data exists in Supabase. ### Add Entry to `jackpots` Table Navigate to **Supabase Dashboard > Table Editor > jackpots** and insert a new row: | Column | Description | Example | | --- | --- | --- | | `slug` | Unique identifier (lowercase, hyphens) | `powerball` | | `game_name` | Display name | `Powerball` | | `jackpot_numeric` | Current jackpot in USD | `209000000` | | `jackpot_alpha_numeric` | Human-readable amount (optional) | `$209 Million` | | `time_stamp_iso` | Last updated timestamp | `2024-01-15T12:00:00Z` | ### Add Entries to `past_drawings` Table (Optional) If historical drawing data is available: | Column | Description | Example | | --- | --- | --- | | `slug` | Must match `jackpots.slug` | `powerball` | | `game_name` | Display name | `Powerball` | | `draw_date` | Drawing date (YYYY-MM-DD) | `2024-01-13` | | `drawing_time_et` | Time in Eastern (optional) | `10:59 PM` | | `draw_datetime_iso` | Full ISO timestamp (optional) | `2024-01-14T03:59:00Z` | | `winning_numbers` | Comma-separated | `12,24,36,48,67` | | `bonus_ball` | Bonus number (nullable) | `10` | | `multiplier` | Multiplier value (nullable) | `2x` | | `jackpot_status` | Winner status (optional) | `Rollover` | | `jackpot_usd` | Jackpot for this drawing | `200000000` | | `drawing_official_link` | Official results link (optional) | `https://...` | ## Step 2: Shopify Product Creation ### Create the Product 1. Go to **Shopify Admin > Products > Add product** 2. Fill in basic information: | Field | Guidance | | --- | --- | | **Title** | Game name (e.g., "Powerball Pool") | | **Description** | Marketing copy explaining the game, odds, prizes | | **Product type** | `Lottery` (for collection filtering) | | **Media** | Upload game logo and promotional images | | **Status** | Set to "Draft" while configuring | ### Configure Variants Create variants for different pricing tiers: 1. Under **Variants**, click **Add options** 2. Add option name (e.g., `Power Play` or `Lines`) 3. Add values and set prices **Example:** | Variant | Price | SKU | | --- | --- | --- | | 1x (No Multiplier) | $2.00 | `PB-STANDARD` | | Up to 2x | $3.00 | `PB-PP-2X` | | Up to 5x | $3.00 | `PB-PP-5X` | | Up to 10x | $3.00 | `PB-PP-10X` | ## Step 3: Configure Metafields This is the most critical step. Navigate to the product's **Metafields** section. ### Required Metafields #### `custom.game_slug` (Required) **Type:** Single line text **Purpose:** Links the product to Supabase jackpot and drawing data | Game | Slug Value | | --- | --- | | Powerball | `powerball` | | Mega Millions | `mega-millions` | | Megabucks NH | `megabucks-nh` | | Lucky for Life | `lucky-for-life` | | Lotto America | `lotto-america` | | Gimme 5 | `gimme-5` | ::: warning Enter the slug **exactly** as shown — all lowercase, hyphens for spaces, no trailing spaces. Mismatches cause jackpots to show as "NaN" or "$0". ::: #### `custom.draw_games_schedule` (Required) **Type:** Single line text **Purpose:** Defines which days drawings occur **Format:** Comma-separated day abbreviations: `Mon`, `Tue`, `Wed`, `Thu`, `Fri`, `Sat`, `Sun` | Game | Schedule | | --- | --- | | Powerball | `Mon, Wed, Sat` | | Mega Millions | `Tue, Fri` | | Lucky for Life | `Mon, Thu` | #### `custom.lottery_pool_cutoff_time` (Required) **Type:** Single line text **Purpose:** Entry cutoff time before the drawing **Format:** 24-hour time (HH:MM) in EST/EDT | Game | Cutoff | | --- | --- | | Powerball | `22:00` | | Mega Millions | `23:00` | ### Optional Metafields #### `custom.logo_image` **Type:** File reference (Media Image) **Purpose:** Custom logo displayed on game cards **Recommendation:** PNG with transparent background, 200x200px minimum #### `custom.lottery_ticket_multiplier` **Type:** Single line text **Purpose:** Enables the multiplier feature display **Value:** `true` or `false` #### `custom.enable_play_options` **Type:** Single line text **Purpose:** Enables play options UI (multi-draw, subscription) **Value:** `true` or `false` #### `custom.disabled_days` **Type:** JSON **Purpose:** Days when the game is unavailable for purchase **Value:** `["Sun"]` #### `custom.game_db_name` **Type:** Single line text **Purpose:** Legacy/optional. Currently unused — `game_slug` is the primary identifier. Stores the exact game name as it appears in Supabase (e.g., "Powerball") but is not consumed by any application logic. ## Step 4: Add to Collection 1. In the product editor, find the **Collections** section 2. Add the product to the **Lottery Games** collection The default collection ID is `gid://shopify/Collection/429088702676`. This can be overridden via page metafields (see `app/lib/settings.ts`). ## Step 5: Publish 1. Set **Status** to "Active" 2. Ensure proper sales channels are selected 3. Click **Save** ## Existing Game Slugs | Game Name | Slug | Notes | | --- | --- | --- | | Powerball | `powerball` | National multi-state game | | Mega Millions | `mega-millions` | National multi-state game | | Megabucks NH | `megabucks-nh` | New Hampshire state game | | Lucky for Life | `lucky-for-life` | Regional multi-state game | | Lotto America | `lotto-america` | Multi-state game | | Gimme 5 | `gimme-5` | Regional game | **Slug naming convention:** * All lowercase * Hyphens (`-`) for spaces * No special characters * Short but descriptive ## Verification Checklist After creating a product, verify everything works: ### Home Page * \[ ] Product appears in the game grid * \[ ] Jackpot amount displays correctly (not "NaN" or "$0") * \[ ] Product image/logo shows properly * \[ ] "Play Now" button links to the correct product page ### Product Detail Page * \[ ] Page loads without errors * \[ ] Jackpot amount displays correctly * \[ ] All variants and pricing options are visible * \[ ] "Add to Cart" functionality works * \[ ] Next drawing date shows correctly ### Past Drawings Page * \[ ] Game appears in the dropdown selector * \[ ] Selecting the game shows drawing results * \[ ] Date filtering works correctly ### Collections Page * \[ ] Product appears in the Lottery Games collection * \[ ] Filters include the new game ## Troubleshooting ### Jackpot shows NaN or $0 **Cause:** Slug mismatch between Shopify and Supabase **Fix:** 1. Check the `custom.game_slug` value on the Shopify product 2. Verify it **exactly** matches the `slug` in the Supabase `jackpots` table 3. Common issues: * Extra spaces: `"powerball "` vs `"powerball"` * Case mismatch: `"Powerball"` vs `"powerball"` * Typos: `"powrball"` vs `"powerball"` ### Product not appearing on home page **Cause:** Product not in the correct collection **Fix:** 1. Edit the product in Shopify 2. Add it to the "Lottery Games" collection 3. Save and wait a few minutes for cache to clear ### Past drawings empty or game missing from dropdown **Cause:** Missing or mismatched data in Supabase **Fix:** 1. Check the `past_drawings` table in Supabase 2. Verify entries exist with the correct `slug` 3. Ensure the `slug` matches the product's `game_slug` metafield ### Next drawing date is wrong **Cause:** Incorrect schedule or cutoff time **Fix:** 1. Check `custom.draw_games_schedule` format (comma-separated day abbreviations) 2. Verify `custom.lottery_pool_cutoff_time` is in 24-hour format (HH:MM) 3. Ensure days match the actual drawing schedule ### Jackpot amount is outdated **Cause:** Supabase data not updated **Fix:** 1. Navigate to the `jackpots` table in Supabase 2. Update `jackpot_numeric` and `jackpot_alpha_numeric` 3. Update `time_stamp_iso` to the current time 4. The website will reflect changes within minutes ## Quick Reference ``` NEW GAME CHECKLIST: 1. [ ] Choose slug (lowercase, hyphens) 2. [ ] Add to Supabase jackpots table 3. [ ] Add to Supabase past_drawings (optional) 4. [ ] Create Shopify product 5. [ ] Set custom.game_slug metafield (MUST match Supabase slug) 6. [ ] Set custom.draw_games_schedule 7. [ ] Set custom.lottery_pool_cutoff_time 8. [ ] Add to Lottery Games collection 9. [ ] Publish product 10.[ ] Verify on website ``` --- --- url: /payments/shopify-checkout-credits.md description: >- How Shopify's native checkout is used to load UBL Points (credits) into user wallets. --- # Credit Loading via Shopify Checkout UberLotto uses Shopify's native checkout system to let users purchase "Load Credits" (UBL Points). This approach leverages Shopify's PCI-compliant checkout infrastructure for credit card processing while using a dedicated Shopify product collection to represent credit denominations. ## Key Files | File | Purpose | |------|---------| | `app/routes/api.shopify-checkout.ts` | `POST /api/shopify-checkout` — creates a checkout cart | | `app/utils/shopify-checkout.server.ts` | Server utilities: fetch variants, create cart, validate prices | | `app/utils/validation.server.ts` | Request validation: body structure, email, token, size checks | | `app/graphql/load-credits/LoadCreditsQuery.ts` | GraphQL queries for collection + cart creation | ## How It Works Instead of a custom payment form, UberLotto creates a Shopify cart containing a single "Load Credits" product variant (e.g., "$50 UBL Points") and redirects the user to Shopify's hosted checkout. ``` User selects credit amount (e.g., $50) │ ▼ ┌──────────────────────────┐ │ POST /api/shopify- │ │ checkout │ │ { amount: "50" } │ └──────────┬───────────────┘ │ ▼ ┌──────────────────────────┐ │ Security Checks: │ │ • Content-Type check │ │ • Body size ≤ 1KB │ │ • CSRF origin check │ │ • Auth required │ │ • Input validation │ └──────────┬───────────────┘ │ ▼ ┌──────────────────────────┐ │ Fetch "load-credits" │ ← Storefront API: collection query │ collection variants │ returns available denominations └──────────┬───────────────┘ │ ▼ ┌──────────────────────────┐ │ Validate amount against │ ← Amount must match a real variant │ available variants │ AND variant price must match └──────────┬───────────────┘ │ ▼ ┌──────────────────────────┐ │ Create cart via │ ← Storefront API: cartCreate mutation │ Storefront API │ with variant ID + buyer identity └──────────┬───────────────┘ │ ▼ ┌──────────────────────────┐ │ Return checkoutUrl │ ← User redirected to Shopify checkout │ to client │ └──────────────────────────┘ ``` ## Security Layers The checkout endpoint implements multiple security checks before processing: ### 1. Content-Type Validation ```typescript if (!isValidContentType(contentType)) { return Response.json({ error: 'Content-Type must be application/json' }, { status: 415 }); } ``` ### 2. Request Body Size Limit Maximum body size of **1KB** to prevent payload-based attacks: ```typescript if (!isWithinSizeLimit(request, 1024)) { return Response.json({ error: 'Request body too large' }, { status: 413 }); } ``` ### 3. CSRF Protection (Origin Validation) Validates the `Origin` or `Referer` header against a whitelist of allowed domains: ```typescript const allowedDomains = [ 'localhost', '127.0.0.1', 'playuberlotto.myshopify.com', 'uberlotto.com', 'www.uberlotto.com', 'dev.uberlotto.com', 'uberlotto.promesolutions.com', ]; ``` Requests missing both `Origin` and `Referer` headers are rejected to prevent CSRF bypass. ::: tip Development Mode CSRF validation is relaxed in development (when `PUBLIC_STORE_DOMAIN` includes `localhost` or `promesolutions.com`). ::: ### 4. Authentication Required ```typescript await context.customerAccount.handleAuthStatus(); ``` Users must be logged in via Shopify Customer Account API. Unauthenticated requests receive a `401` response. ### 5. Input Validation The `validateCheckoutRequest()` function performs comprehensive checks: * **amount** (required): must be a non-empty string, max 20 characters * **email** (optional): RFC 5322 format, max 254 characters, no header injection characters * **customerAccessToken** (optional): 10-500 characters, alphanumeric only * **Unexpected fields** are rejected (prevents injection of arbitrary data) ### 6. Price Validation After fetching the variant from Shopify, the endpoint verifies the variant's actual price matches the requested amount: ```typescript const variantPrice = parseFloat(variant.price.amount); const requestedAmount = parseFloat(amount); if (Math.abs(variantPrice - requestedAmount) > 0.01) { // Price mismatch — possible manipulation attempt return Response.json({ error: 'Price validation failed' }, { status: 400 }); } ``` This prevents attacks where a user might try to purchase a higher-value credit for a lower price. ## Load Credits Collection The "load-credits" Shopify collection contains products representing different credit denominations. Each product has a single variant whose price equals the credit amount. ### Variant Fetching ```typescript const variantsMap = await fetchLoadCreditVariants(storefront, 'load-credits'); // Returns: { "50": LoadCreditVariant, "100": LoadCreditVariant, ... } ``` The `fetchLoadCreditVariants()` function: 1. Queries the Shopify Storefront API for the collection 2. Iterates all products and their variants 3. Builds a map keyed by rounded price amount (e.g., `"50"`, `"100"`) 4. Only includes variants that are `availableForSale` ### Cart Creation ```typescript const { checkoutUrl, cart } = await createCheckoutForVariant( storefront, variant.id, // gid://shopify/ProductVariant/... 1, // Quantity is always 1 buyerIdentity, // Optional: email, customerAccessToken, countryCode ); ``` The function validates: * Variant ID format (must start with `gid://shopify/ProductVariant/`) * Quantity bounds (1-100) ## Request / Response ### Request ```json POST /api/shopify-checkout Content-Type: application/json { "amount": "50", "email": "user@example.com", "customerAccessToken": "abc123def456" } ``` ### Success Response ```json { "success": true, "checkoutUrl": "https://checkout.playuberlotto.myshopify.com/cart/c/...", "cart": { "id": "gid://shopify/Cart/...", "totalQuantity": 1, "totalAmount": "50.00", "currencyCode": "USD" } } ``` ### Error Codes | Code | Status | Description | |------|--------|-------------| | `METHOD_NOT_ALLOWED` | 405 | Not a POST request | | `INVALID_CONTENT_TYPE` | 415 | Content-Type not `application/json` | | `PAYLOAD_TOO_LARGE` | 413 | Body exceeds 1KB | | `MISSING_ORIGIN` | 403 | No Origin or Referer header | | `INVALID_ORIGIN` | 403 | Origin not in whitelist | | `AUTHENTICATION_REQUIRED` | 401 | User not logged in | | `VALIDATION_FAILED` | 400 | Input validation errors | | `AMOUNT_UNAVAILABLE` | 400 | Credit amount not in collection | | `PRICE_MISMATCH` | 400 | Variant price doesn't match amount | | `CHECKOUT_FAILED` | 500 | Cart creation failed | ## Environment Notes ::: warning Oxygen Deployment In-memory rate limiting does not work on Shopify Oxygen because it runs on stateless edge workers with no persistent memory. The checkout endpoint relies on Shopify's built-in API rate limits instead. ::: No additional environment variables are required beyond the standard Shopify Hydrogen configuration (`PUBLIC_STORE_DOMAIN`, `PUBLIC_STOREFRONT_API_TOKEN`, etc.). --- --- url: /architecture/data-flow.md description: >- Core data flow patterns in UberLotto v2 — SSR page loads, payment flows, and product enrichment. --- # Data Flow Patterns UberLotto v2 uses five primary data flow patterns across the platform. ## Pattern 1: SSR Page Load Every page request flows through React Router loaders on the Oxygen edge runtime, fetching data in parallel from multiple sources before rendering HTML. ``` Browser Request │ ▼ ┌─────────────┐ │ Oxygen │ │ Runtime │ └──────┬──────┘ │ ▼ ┌──────────────┐ │ React Router │ ┌──────────────┐ │ Loader │─────▶│ Parallel │ └──────────────┘ │ Fetches │ └──────┬───────┘ │ ┌──────────────────┼──────────────────┐ ▼ ▼ ▼ ┌────────────┐ ┌────────────┐ ┌────────────┐ │ Shopify │ │ Supabase │ │ PostHog │ │ Storefront │ │ API │ │ Analytics │ │ API │ │ │ │ │ └────────────┘ └────────────┘ └────────────┘ │ │ │ └──────────────────┼──────────────────┘ ▼ ┌──────────────┐ │ Rendered │ │ HTML │ └──────┬──────┘ │ ▼ ┌──────────────┐ │ Browser │ │ Hydration │ └──────────────┘ ``` **Key details:** * **Critical data** (header, shop limits) is awaited before rendering — blocks time to first byte * **Deferred data** (footer, cart, customer profile) loads after initial render — non-blocking * Loaders use `Promise.all()` for parallel fetches * `shouldRevalidate` in `root.tsx` prevents unnecessary re-fetches on client navigation * Shopify data is cached using Hydrogen's built-in `CacheLong()` and `CacheShort()` strategies ```typescript // Example: root.tsx critical vs deferred loading async function loadCriticalData({ context }) { const [header, shopLimits] = await Promise.all([ storefront.query(HEADER_QUERY, { cache: storefront.CacheLong() }), getShopLimits(storefront), ]); return { header, shopLimits }; } function loadDeferredData({ context }) { return { cart: cart.get(), // deferred isLoggedIn: customerAccount.isLoggedIn(), // deferred footer: storefront.query(FOOTER_QUERY), // deferred }; } ``` ## Pattern 2: Plisio Cryptocurrency Payment Direct cryptocurrency payments using Plisio's invoice system with webhook-based confirmation. All transactions use the unified `payment_transactions` table. ``` ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ Client │ │ Oxygen │ │ Supabase │ │ Plisio │ └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ │ │ │ │ 1. Request │ │ │ │ Payment │ │ │ │ (auth req.) │ │ │ │───────────────▶│ │ │ │ │ 2. Save │ │ │ │ Pending Txn │ │ │ │ (payment_ │ │ │ │ transactions│ │ │ │───────────────▶│ │ │ │ │ │ │ │ 3. Create │ │ │ │ Invoice │ │ │ │───────────────────────────────▶ │ │ │ │ │ │ │◀─────────────────────────────── │ │ │ 4. Invoice URL │ │ │ │ │ │ │ 5. Update Txn │ │ │ │ w/ provider │ │ │ │ _txn_id │ │ │ │───────────────▶│ │ │ │ │ │ │◀───────────────│ │ │ │ 6. Redirect │ │ │ │ to Plisio │ │ │ │ │ │ │ │ │ │ 7. User Pays │ │ │ │ Crypto │ │ │ │ │ │ │◀────────────────────────────── │ │ │ 8. Webhook Notification │ │ │ │ │ │ │ 9. 6-Layer │ │ │ │ Security │ │ │ │ Pipeline │ │ │ │ │ │ │ │ 10. Update Txn │ │ │ │ in payment_│ │ │ │ transactions │ │ │───────────────▶│ │ │ │ │ │ ``` **Authentication:** Customer must be logged in (`customerAccount.isLoggedIn()`) to create invoices and check status. **Security layers applied:** 1. **Rate limiting** — IP-based and global rate limits on webhook endpoint 2. **IP allowlisting** — `PLISIO_WEBHOOK_IPS` restricts webhook source IPs 3. **HMAC-SHA1 verification** — Webhook signatures verified against `PLISIO_SECRET_KEY` 4. **Replay protection** — Nonces stored in `webhook_nonces` table prevent duplicate processing 5. **Transaction rate limiting** — Per-txn rate limit (10 req/min per txn\_id) 6. **Duplicate check** — Database-level dedup against `payment_transactions` **CORS:** Invoice and status endpoints use origin-restricted `getAllowedOrigin()` (replaces wildcard `*`). **Endpoints:** `api.plisio-invoice.ts`, `api.plisio-webhook.ts`, `api.plisio-status.ts` ## Pattern 3: MoonPay Fiat-to-Crypto Payment MoonPay provides a fiat on-ramp — users pay with credit card and receive crypto credited to their account. The complete pipeline includes transaction pre-creation, widget signing, and webhook-based processing into the unified `payment_transactions` table. ``` ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ Client │ │ Oxygen │ │ Supabase │ │ MoonPay │ └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ │ │ │ │ 1. Open │ │ │ │ MoonPay │ │ │ │ Widget │ │ │ │ │ │ │ │ 2. Sign URL │ │ │ │ (auth req.) │ │ │ │───────────────▶│ │ │ │ │ │ │ │ │ 3. Pre-create │ │ │ │ pending txn │ │ │ │ (payment_ │ │ │ │ transactions│ │ │ │───────────────▶│ │ │ │ │ │ │ │ 4. HMAC sign │ │ │ │ URL query │ │ │◀───────────────│ │ │ │ 5. Signed │ │ │ │ URL │ │ │ │ │ │ │ │ 6. User completes purchase │ │ │ in MoonPay widget ──────────────────────────▶ │ │ │ │ │ │ │◀────────────────────────────── │ │ │ 7. Webhook: transaction_updated│ │ │ │ │ │ │ 8. Security │ │ │ │ Pipeline: │ │ │ │ Rate limit │ │ │ │ → HMAC-256 │ │ │ │ → Timestamp │ │ │ │ → Replay │ │ │ │ → Validate │ │ │ │ │ │ │ │ 9. Find-or- │ │ │ │ create txn │ │ │ │ a) by │ │ │ │ provider_ │ │ │ │ txn_id │ │ │ │ b) by │ │ │ │ order_number│ │ │ │ c) create │ │ │ │ new │ │ │ │ │ │ │ │ 10. Update │ │ │ │ status in │ │ │ │ payment_ │ │ │ │ transactions │ │ │───────────────▶│ │ │ │ │ │ │ │ 11. Log │ │ │ │ security │ │ │ │ event │ │ │ │───────────────▶│ │ │ │ │ │ ``` **Key details:** * MoonPay widget is embedded via `@moonpay/moonpay-react` (`MoonPayCheckout` component) * **Authentication required:** Sign endpoint requires `customerAccount.isLoggedIn()` * **Transaction pre-creation:** The sign endpoint (`api.moonpay-sign.ts`) pre-creates a pending `payment_transactions` record using URL parameters (`externalTransactionId` → `order_number`, `externalCustomerId` → `user_email`). This allows the webhook to match incoming notifications to existing records. * **HMAC-SHA256 verification:** Webhooks use `Moonpay-Signature-V2` header with format `t=TIMESTAMP,s=SIGNATURE`. Signs `timestamp.body`, hex-encoded. * **Timestamp freshness:** Rejects webhooks older than 5 minutes * **Replay protection:** Nonce-based via `checkGenericWebhookNonce()` using shared `webhook_nonces` table with 5-min TTL * **Transaction lookup:** Three-step strategy — by `provider_txn_id`, then by `order_number`, then create new * **Payload handling:** Handles MoonPay sending `data` as a JSON string (double-parse) * Webhook handler returns 200 even on errors to prevent MoonPay retries (per their best practice) * **CORS:** Sign endpoint uses origin-restricted `getAllowedOrigin()` (replaces wildcard `*`) **Endpoints:** `api.moonpay-sign.ts`, `api.moonpay-webhook.ts` ## Pattern 4: Shopify Checkout Credit Loading Users can load UBL Points (credits) by purchasing products through standard Shopify checkout. ``` ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ Client │ │ Oxygen │ │ Shopify │ │ Shopify │ │ │ │ │ │Storefront│ │ Checkout │ └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ │ │ │ │ 1. Select │ │ │ │ Credit Amt │ │ │ │───────────────▶│ │ │ │ │ 2. Verify Auth │ │ │ │ (Customer │ │ │ │ Account) │ │ │ │ │ │ │ │ 3. Fetch │ │ │ │ Variants │ │ │ │───────────────▶│ │ │ │◀───────────────│ │ │ │ │ │ │ │ 4. Validate │ │ │ │ Amount & │ │ │ │ Price Match │ │ │ │ │ │ │ │ 5. Create Cart │ │ │ │ (cartCreate)│ │ │ │───────────────▶│ │ │ │◀───────────────│ │ │ │ checkoutUrl │ │ │ │ │ │ │◀───────────────│ │ │ │ 6. Redirect │ │ │ │ to Checkout│ │ │ │─────────────────────────────────────────────────▶│ │ │ │ 7. User │ │ │ │ Pays │ │ │ │ │ ``` **Key details:** * "Load Credits" collection in Shopify contains UBL Point products at fixed amounts * Server-side `api.shopify-checkout.ts` endpoint handles checkout creation * **Authentication required** — `customerAccount.handleAuthStatus()` checked before processing * **Price validation** — variant price from Shopify is compared against requested amount to prevent manipulation (1 cent tolerance) * **CSRF protection** — origin/referer header validation against allowed domains * **Input validation** — Content-Type, body size (1KB max), and structured field validation **Files:** `api.shopify-checkout.ts`, `utils/shopify-checkout.server.ts`, `graphql/load-credits/LoadCreditsQuery.ts` ## Pattern 5: Product-to-Jackpot Data Enrichment Lottery products from Shopify are enriched with real-time jackpot data from Supabase using a slug-based matching system. ``` ┌─────────────────────────────────────────────────────────┐ │ Shopify Product │ │ ┌───────────────────────────────────────────────────┐ │ │ │ Product: "Powerball Pool" │ │ │ │ Metafield: custom.game_slug = "powerball" │ │ │ └──────────────────────┬────────────────────────────┘ │ └─────────────────────────┼───────────────────────────────┘ │ │ slug = "powerball" │ ▼ ┌─────────────────────────────────────────────────────────┐ │ LotteryProductEnrichmentService │ │ ┌───────────────────────────────────────────────────┐ │ │ │ 1. Build Map from drawings │ │ │ │ 2. Match product.gameSlug → drawingsMap.get() │ │ │ │ 3. Calculate next drawing date from schedule │ │ │ │ 4. Return EnrichedLotteryProduct │ │ │ └──────────────────────┬────────────────────────────┘ │ └─────────────────────────┼───────────────────────────────┘ │ ┌───────────┴───────────┐ ▼ ▼ ┌──────────────────────┐ ┌──────────────────────┐ │ Supabase: jackpots │ │ Supabase: │ │ ┌────────────────┐ │ │ past_drawings │ │ │ slug │ │ │ ┌────────────────┐ │ │ │ game_name │ │ │ │ slug │ │ │ │ jackpot_numeric│ │ │ │ game_name │ │ │ │ last_updated │ │ │ │ draw_date │ │ │ └────────────────┘ │ │ │ winning_numbers│ │ └──────────────────────┘ │ └────────────────┘ │ └──────────────────────┘ ``` **Key details:** * **Single identifier system** — the `custom.game_slug` Shopify metafield is the only connection between products and Supabase data * `LotteryProductEnrichmentService` creates an O(1) lookup map from drawings array * Products are deduplicated by ID during batch enrichment * Next drawing dates are calculated from product schedule metafields (`drawGamesSchedule`, `lotteryPoolCutoffTime`) * This service is shared between Home and Collections pages to eliminate code duplication **Files:** `services/lottery-product-enrichment.service.ts`, `utils/jackpot.server.ts`, `utils/pastDrawings.server.ts` --- --- url: /database/monitoring.md description: >- Monitoring queries, scheduled maintenance tasks, and operational best practices for the UberLotto v2 Supabase database. --- # Database Monitoring & Maintenance Operational guide for monitoring database health, running maintenance tasks, and keeping the UberLotto v2 Supabase instance running smoothly. ## Monitoring Queries ### Row Counts Get a quick overview of all table sizes: ```sql SELECT 'payment_transactions' as table_name, COUNT(*) as rows FROM payment_transactions UNION ALL SELECT 'payment_transaction_events', COUNT(*) FROM payment_transaction_events UNION ALL SELECT 'webhook_nonces', COUNT(*) FROM webhook_nonces UNION ALL SELECT 'security_events', COUNT(*) FROM security_events UNION ALL SELECT 'jackpots', COUNT(*) FROM jackpots UNION ALL SELECT 'past_drawings', COUNT(*) FROM past_drawings; ``` ### Recent Security Events Check for errors and critical events in the last 24 hours: ```sql SELECT * FROM security_events WHERE severity IN ('error', 'critical') AND created_at > NOW() - INTERVAL '24 hours' ORDER BY created_at DESC; ``` Or use the built-in function with filtering: ```sql -- All critical events in the last 6 hours SELECT * FROM get_recent_security_events(NULL, 'critical', 100, 6); -- HMAC failures in the last 24 hours SELECT * FROM get_recent_security_events('hmac_failure', NULL, 100, 24); -- Replay attack attempts SELECT * FROM get_recent_security_events('replay_detected', NULL, 50, 168); ``` ### Payment Transaction Status View the current distribution of payment transaction statuses by provider: ```sql SELECT provider, status, COUNT(*), MAX(created_at) as latest FROM payment_transactions GROUP BY provider, status ORDER BY provider, status; ``` ### Stale Pending Transactions Find transactions that should have been expired by the cron job: ```sql SELECT COUNT(*) as stale_count FROM payment_transactions WHERE expires_at < NOW() AND status = 'pending'; ``` Or use the built-in function to expire them: ```sql SELECT expire_stale_transactions(); ``` ### Expired Webhook Nonces Check for nonces that haven't been cleaned up yet: ```sql SELECT COUNT(*) as expired_count FROM webhook_nonces WHERE expires_at < NOW(); ``` ::: tip If this count is consistently high, the `cleanup_expired_nonces()` function may not be running on schedule. See [Scheduled Maintenance](#scheduled-maintenance) below. ::: ### Security Event Summary Get an aggregate view of event types over the last 7 days: ```sql SELECT event_type, severity, COUNT(*) as event_count, MIN(created_at) as first_seen, MAX(created_at) as last_seen FROM security_events WHERE created_at > NOW() - INTERVAL '7 days' GROUP BY event_type, severity ORDER BY event_count DESC; ``` ### Payment Transaction Events View recent status changes across all transactions: ```sql SELECT pte.created_at, pt.order_number, pt.provider, pte.previous_status, pte.new_status, pte.changed_by, pte.change_reason FROM payment_transaction_events pte JOIN payment_transactions pt ON pt.id = pte.transaction_id ORDER BY pte.created_at DESC LIMIT 50; ``` ### Transaction Summary by User Get aggregated stats for a specific user using the built-in function: ```sql SELECT * FROM get_transaction_summary('user@example.com'); ``` ### Cancellation Rate Check Check if a user is hitting the anti-abuse threshold (10+ cancellations per hour): ```sql SELECT check_cancellation_rate('user@example.com'); -- Returns FALSE if abuse threshold exceeded ``` ### Active Transactions by Provider Monitor currently active (non-terminal) transactions: ```sql SELECT provider, status, COUNT(*) as count, MIN(created_at) as oldest, MAX(created_at) as newest FROM payment_transactions WHERE status IN ('pending', 'processing', 'awaiting_confirmation') GROUP BY provider, status ORDER BY provider, count DESC; ``` ### Jackpot Data Freshness Check when jackpot data was last updated: ```sql SELECT slug, game_name, jackpot_alpha_numeric, time_stamp_iso, created_at, NOW() - created_at as age FROM jackpots ORDER BY created_at DESC; ``` ## Scheduled Maintenance The following maintenance tasks must run on a regular schedule: | Task | Schedule | Method | Description | | --- | --- | --- | --- | | Clean expired nonces | Every 5 minutes | Database function | Removes expired webhook nonces | | Clean old security events | Monthly | Database function | Removes events older than 1 year | | Expire stale transactions | Periodic (cron) | Database function | Marks pending transactions past `expires_at` as expired | | Archive old events | Monthly | Database function | Removes transaction events older than 90 days | ### Nonce Cleanup (Every 5 Minutes) Expired webhook nonces must be cleaned up regularly to prevent the `webhook_nonces` table from growing unbounded. **Method:** Call the `cleanup_expired_nonces()` database function. ```sql SELECT cleanup_expired_nonces(); ``` **Setup options:** 1. **Supabase pg\_cron** (recommended): ```sql SELECT cron.schedule( 'cleanup-expired-nonces', '*/5 * * * *', $$SELECT cleanup_expired_nonces()$$ ); ``` 2. **External cron job** calling the Supabase REST API or SQL Editor. ### Security Event Cleanup (Monthly) Security events are retained for 1 year. The cleanup function removes older records. **Method:** Call the `cleanup_old_security_events()` database function. ```sql SELECT cleanup_old_security_events(); ``` **Setup with pg\_cron:** ```sql SELECT cron.schedule( 'cleanup-old-security-events', '0 3 1 * *', -- 3:00 AM on the 1st of each month $$SELECT cleanup_old_security_events()$$ ); ``` ### Stale Transaction Expiry (Periodic) Pending transactions that exceed their `expires_at` timestamp are marked as expired by the `expire_stale_transactions()` database function. ```sql SELECT expire_stale_transactions(); ``` **Setup with pg\_cron:** ```sql SELECT cron.schedule( 'expire-stale-transactions', '*/15 * * * *', -- Every 15 minutes $$SELECT expire_stale_transactions()$$ ); ``` ### Old Event Archival (Monthly) Transaction events older than 90 days are cleaned up by `archive_old_events()`. ```sql SELECT archive_old_events(); -- Default: 90 days SELECT archive_old_events('180 days'); -- Custom retention ``` **Setup with pg\_cron:** ```sql SELECT cron.schedule( 'archive-old-events', '0 4 1 * *', -- 4:00 AM on the 1st of each month $$SELECT archive_old_events()$$ ); ``` ## Alerts to Configure Set up monitoring alerts for these conditions: | Condition | Query | Threshold | | --- | --- | --- | | Critical security events | `SELECT COUNT(*) FROM security_events WHERE severity = 'critical' AND created_at > NOW() - INTERVAL '1 hour'` | > 0 | | High rate of HMAC failures | `SELECT COUNT(*) FROM security_events WHERE event_type = 'hmac_failure' AND created_at > NOW() - INTERVAL '1 hour'` | > 10 | | Replay attacks detected | `SELECT COUNT(*) FROM security_events WHERE event_type = 'replay_detected' AND created_at > NOW() - INTERVAL '1 hour'` | > 0 | | Stale nonces accumulating | `SELECT COUNT(*) FROM webhook_nonces WHERE expires_at < NOW()` | > 100 | | Stale pending payments | `SELECT COUNT(*) FROM payment_transactions WHERE expires_at < NOW() AND status = 'pending'` | > 10 | | Failed payments spike | `SELECT COUNT(*) FROM payment_transactions WHERE status = 'failed' AND created_at > NOW() - INTERVAL '1 hour'` | > 5 | | High cancellation rate | `SELECT check_cancellation_rate('user@example.com')` | Returns FALSE | | Event log overflow | `SELECT transaction_id, COUNT(*) FROM payment_transaction_events GROUP BY transaction_id HAVING COUNT(*) >= 45` | Any rows | ## Best Practices 1. **Run monitoring queries daily** — Check security events and payment transaction status at minimum once per day. 2. **Verify cleanup jobs are running** — If expired nonces or old security events accumulate, the scheduled functions may have stopped. 3. **Review security events after incidents** — Use `get_recent_security_events()` with appropriate filters to investigate payment issues. 4. **Monitor table sizes** — The `security_events` table grows continuously. Ensure the monthly cleanup is running to keep it within the 1-year retention window. 5. **Back up before maintenance** — Before running any manual cleanup or schema changes, create a Supabase backup via the Dashboard. 6. **Check jackpot freshness** — If jackpot amounts appear stale on the website, check the `time_stamp_iso` and `created_at` columns in the `jackpots` table. 7. **Use the Supabase Dashboard** — The Table Editor and SQL Editor in the Supabase Dashboard are the primary tools for ad-hoc monitoring and maintenance. --- --- url: /deployment/oxygen.md description: >- Guide to deploying UberLotto v2 to Shopify's Oxygen edge hosting platform, including CI/CD workflows, environment management, and rollback procedures. --- # Deployment to Shopify Oxygen ## Overview Shopify Oxygen is an edge hosting platform built on Cloudflare Workers. It provides: * **Global edge deployment** — Low latency worldwide via Cloudflare's network * **Automatic scaling** — No capacity planning needed * **CI/CD integration** — Two GitHub Actions workflows deploy automatically on every push * **Environment management** — Preview and production environments with variable inheritance ## Deployment Architecture ``` ┌─────────────────────────────────────────────────────────────────┐ │ GitHub Repository │ │ │ │ │ git push │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ GitHub Actions (TWO workflows) │ │ │ │ oxygen-deployment-1000042728.yml (Storefront A) │ │ │ │ oxygen-deployment-1000083568.yml (Storefront B) │ │ │ │ │ │ │ │ 1. Checkout code │ │ │ │ 2. Setup Node.js (LTS) │ │ │ │ 3. Cache & install dependencies (npm ci) │ │ │ │ 4. Build & Deploy (shopify hydrogen deploy) │ │ │ └─────────────────────────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ Shopify Oxygen │ │ │ │ │ │ │ │ ┌─────────────┐ ┌─────────────┐ │ │ │ │ │ Preview │ │ Production │ │ │ │ │ │ Environment │ │ Environment │ │ │ │ │ └─────────────┘ └─────────────┘ │ │ │ └─────────────────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────────────────┘ ``` ## GitHub Actions Workflows UberLotto uses **two** Oxygen deployment workflows. Both trigger on every push to any branch, and Shopify Oxygen automatically determines whether to deploy to production (main branch) or a preview environment (other branches). ### Workflow 1: Storefront 1000042728 **File:** `.github/workflows/oxygen-deployment-1000042728.yml` ```yaml name: Storefront 1000042728 on: [push] permissions: contents: read deployments: write jobs: deploy: name: Deploy to Oxygen timeout-minutes: 30 runs-on: ubuntu-latest steps: - uses: actions/checkout@v4 - name: Setup node.js uses: actions/setup-node@v4 with: node-version: "lts/*" check-latest: true - name: Cache node modules id: cache-npm uses: actions/cache@v4 env: cache-name: cache-node-modules with: path: ~/.npm key: ${{ runner.os }}-build-${{ env.cache-name }}-${{ hashFiles('**/package-lock.json') }} restore-keys: | ${{ runner.os }}-build-${{ env.cache-name }}- ${{ runner.os }}-build- ${{ runner.os }}- - name: Install dependencies run: npm ci - name: Build and Publish to Oxygen run: npx shopify hydrogen deploy env: SHOPIFY_HYDROGEN_DEPLOYMENT_TOKEN: ${{ secrets.OXYGEN_DEPLOYMENT_TOKEN_1000042728 }} ``` ### Workflow 2: Storefront 1000083568 **File:** `.github/workflows/oxygen-deployment-1000083568.yml` Identical structure to Workflow 1 but uses its own deployment token: ```yaml name: Storefront 1000083568 on: [push] # ... same steps ... - name: Build and Publish to Oxygen run: npx shopify hydrogen deploy env: SHOPIFY_HYDROGEN_DEPLOYMENT_TOKEN: ${{ secrets.OXYGEN_DEPLOYMENT_TOKEN_1000083568 }} ``` ::: tip Why Two Workflows? Each workflow targets a different Shopify storefront environment. This enables deploying the same codebase to multiple storefronts (e.g., staging and production stores, or regional storefronts) from a single repository. ::: ### Required GitHub Secrets Configure these in **GitHub Repository → Settings → Secrets and variables → Actions**: | Secret | Description | |---|---| | `OXYGEN_DEPLOYMENT_TOKEN_1000042728` | Deployment token for Storefront 1000042728 | | `OXYGEN_DEPLOYMENT_TOKEN_1000083568` | Deployment token for Storefront 1000083568 | ### Getting Deployment Tokens 1. Navigate to **Shopify Admin → Sales channels → Hydrogen** 2. Select the target storefront 3. Go to **Settings → Deployment** 4. Copy the deployment token ## Branch Strategy | Branch | Environment | URL | |---|---|---| | `main` | Production | Custom domain / `.myshopify.com` | | Any other branch | Preview | Unique preview URL per deployment | ``` main (production) │ ├── develop (staging/preview) │ │ │ ├── feature/xxx │ └── bugfix/xxx │ └── hotfix/xxx (urgent fixes) ``` ::: warning Both workflows trigger on every push to **any** branch. Shopify Oxygen determines the environment (production vs. preview) based on the branch configuration in your Shopify admin. ::: ## Manual Deployment ### Prerequisites ```bash # Ensure you're logged into Shopify CLI shopify auth login # Link to your store (if not already linked) shopify hydrogen link ``` ### Deploy Command ```bash # Build and deploy to Oxygen npm run build && npx shopify hydrogen deploy ``` ### Preview Deployment ```bash # Deploy to preview environment npx shopify hydrogen deploy --preview ``` ## Environment Variables in Oxygen ### Setting Variables via Shopify Admin 1. **Shopify Admin → Sales channels → Hydrogen** 2. Select storefront → **Settings → Environment variables** 3. Add or edit variables ### Setting Variables via CLI ```bash # Push local .env to Oxygen shopify hydrogen env push # Pull Oxygen env to local shopify hydrogen env pull # List current environment variables shopify hydrogen env list ``` ### Variable Inheritance ``` Production Environment │ ▼ ┌───────────────────┐ │ Base Variables │ ← Set at storefront level │ (shared) │ └───────┬───────────┘ │ ┌────┴────┐ ▼ ▼ ┌──────┐ ┌───────┐ │ Prod │ │Preview│ ← Can override base variables └──────┘ └───────┘ ``` ### Key Environment Variables The application requires the following environment variables configured in Oxygen: | Category | Variables | |---|---| | **Shopify** | `PUBLIC_STORE_DOMAIN`, `PUBLIC_STOREFRONT_API_TOKEN`, `PUBLIC_CHECKOUT_DOMAIN` | | **Supabase** | `SUPABASE_URL`, `SUPABASE_ANON_KEY`, `SUPABASE_SERVICE_ROLE_KEY` | | **Plisio** | `PLISIO_API_KEY`, `PLISIO_SECRET_KEY` | | **MoonPay** | `MOONPAY_SECRET_KEY`, `MOONPAY_WEBHOOK_KEY` | | **Analytics** | `VITE_PUBLIC_POSTHOG_KEY`, `VITE_PUBLIC_POSTHOG_HOST` | | **Cron/Security** | `CLEANUP_SECRET_TOKEN` | ::: danger Never commit secrets to the repository. Always configure sensitive values through Shopify Admin or `shopify hydrogen env push`. ::: ## Build Process ### What Happens During Build 1. **Shopify Codegen** — Generates GraphQL types from queries ```bash shopify hydrogen codegen ``` 2. **Vite Build** — Bundles the application for production ```bash vite build ``` 3. **Asset Processing** * CSS minification (Tailwind CSS v4) * JavaScript minification and tree-shaking * PWA manifest generation * Service worker compilation (via `vite-plugin-pwa`) ### Build Output ``` dist/ ├── client/ │ ├── assets/ # Hashed static assets │ ├── manifest.webmanifest # PWA manifest │ └── sw.js # Service worker └── worker/ └── index.js # Edge worker bundle ``` ## Monitoring Deployments ### GitHub Actions * View workflow runs: **Repository → Actions** tab * Both workflows run in parallel on each push * Check deployment status and logs per workflow * Re-run failed deployments individually ### Shopify Admin * Deployment history: **Hydrogen → Deployments** * View preview URLs for non-main branches * Monitor deployment status in real-time ## Rollback Procedure ### Via Shopify Admin 1. Navigate to **Hydrogen → Deployments** 2. Find the previous successful deployment 3. Click **"Rollback to this version"** ### Via Git ```bash # Safe rollback: revert the last commit git revert HEAD git push origin main ``` ::: danger Avoid force-pushing to `main`. Use `git revert` to create a new commit that undoes changes, preserving history and enabling both workflows to redeploy the previous state. ::: ## Performance Optimization ### Edge Caching Oxygen automatically caches static assets (JS, CSS, images). For dynamic content, set cache headers in loaders: ```typescript export async function loader() { return data(result, { headers: { 'Cache-Control': 'public, max-age=3600, stale-while-revalidate=86400', }, }); } ``` ### Bundle Analysis ```bash # Analyze bundle size npm run build -- --analyze ``` ## Troubleshooting ### Deployment Timeout **Symptom:** Build exceeds the 30-minute timeout. **Solutions:** * Check for infinite loops in build scripts * Reduce dependency count * npm caching is already configured in both workflows ### Missing Environment Variables **Symptom:** Runtime errors about undefined variables. ```bash # Verify variables are set in Oxygen shopify hydrogen env list # Push missing variables shopify hydrogen env push ``` ### Build Failures **Symptom:** TypeScript or bundling errors in CI. ```bash # Validate locally before pushing npm run typecheck npm run lint npm run build ``` ### PWA Assets Not Found **Symptom:** 404 errors for manifest or service worker. * Verify files exist in `public/` directory * Check `vite.config.ts` for correct PWA plugin configuration ### Workflow-Specific Failures If one workflow succeeds but the other fails, check that both deployment tokens are valid and not expired. Each storefront requires its own token. --- --- url: /getting-started/environment-variables.md description: Complete reference for all environment variables used in UberLotto v2. --- # Environment Variables Reference All environment variables used by UberLotto v2. Copy `.env.example` to `.env` and fill in the values. ## Variable Categories | Category | Prefix | Description | |----------|--------|-------------| | Public | `PUBLIC_*` | Safe to expose in client bundles | | Vite Public | `VITE_PUBLIC_*` | Exposed to client via Vite | | Private | No prefix | Server-only, never exposed to client | ## Shopify Configuration **Required** for all environments. | Variable | Description | Example | |----------|-------------|---------| | `PUBLIC_STOREFRONT_ID` | Shopify Storefront ID | `gid://shopify/Shop/12345` | | `PUBLIC_STOREFRONT_API_TOKEN` | Public Storefront API token | `shpat_xxxxx` | | `PUBLIC_STORE_DOMAIN` | Store domain | `uberlotto.myshopify.com` | | `PRIVATE_STOREFRONT_API_TOKEN` | Private Storefront API token | `shpat_xxxxx` | | `PUBLIC_CUSTOMER_ACCOUNT_API_CLIENT_ID` | Customer Account API client ID | `shp_xxxxx` | | `PUBLIC_CUSTOMER_ACCOUNT_API_URL` | Customer Account API URL | `https://shopify.com/xxxxx` | | `SHOP_ID` | Shop GID | `gid://shopify/Shop/12345` | | `SESSION_SECRET` | Session encryption key (32+ chars) | Random string | | `PUBLIC_CHECKOUT_DOMAIN` | Checkout domain | `uberlotto.myshopify.com` | ### How to Obtain 1. **Storefront API credentials** — Shopify Admin → Settings → Apps → Develop apps → Create/select app → API credentials 2. **Customer Account API** — Shopify Admin → Settings → Customer accounts → Enable and configure ## Supabase Configuration **Required** for database functionality. | Variable | Description | Example | |----------|-------------|---------| | `SUPABASE_URL` | Project URL | `https://xxxxx.supabase.co` | | `SUPABASE_ANON_KEY` | Anonymous/public key | `eyJhbGciOiJIUzI1NiIs...` | | `SUPABASE_SERVICE_ROLE_KEY` | Service role key (admin) | `eyJhbGciOiJIUzI1NiIs...` | ::: danger SUPABASE\_SERVICE\_ROLE\_KEY The service role key bypasses Row Level Security (RLS). **Never expose it** in client code, logs, or version control. Only use in `.server.ts` files. ::: ### How to Obtain 1. Open [Supabase Dashboard](https://supabase.com/dashboard) 2. Select project → Settings → API 3. Copy keys from "Project API keys" section ## Analytics (PostHog) **Optional** — leave empty to disable analytics. | Variable | Description | Example | |----------|-------------|---------| | `VITE_PUBLIC_POSTHOG_KEY` | PostHog project API key | `phc_xxxxx` | | `VITE_PUBLIC_POSTHOG_HOST` | PostHog host URL | `https://us.i.posthog.com` | ::: tip Both variables must be set for analytics to work. Leave both empty to disable. ::: ## Payment Gateway — Plisio **Required** for cryptocurrency payments via Plisio. | Variable | Description | Example | |----------|-------------|---------| | `PLISIO_API_KEY` | Plisio API key | `xxxxx` | | `PLISIO_SECRET_KEY` | Webhook HMAC secret | `xxxxx` | | `PLISIO_WEBHOOK_IPS` | Allowed webhook IPs (comma-separated) | `216.219.89.38` | ### How to Obtain 1. Open [Plisio Dashboard](https://plisio.net/account) 2. API Settings → Create/view API keys 3. Webhook Settings → Copy secret key ::: warning `PLISIO_SECRET_KEY` is used to verify webhook HMAC signatures. `PLISIO_WEBHOOK_IPS` should include the official Plisio webhook IP: `216.219.89.38`. ::: ## Payment Gateway — MoonPay **Required** for fiat-to-crypto on-ramp payments via MoonPay. | Variable | Description | Example | |----------|-------------|---------| | `MOONPAY_PUBLISHABLE_KEY` | MoonPay publishable key | `pk_test_your_key_here` | | `MOONPAY_SECRET_KEY` | MoonPay secret key | `sk_test_your_key_here` | | `MOONPAY_WEBHOOK_KEY` | Webhook HMAC verification key | `whk_your_webhook_key_here` | | `MOONPAY_WALLET_ADDRESS` | Destination wallet address | `0x3ae230Bc...` | | `MOONPAY_ENVIRONMENT` | `sandbox` or `production` | `sandbox` | ### How to Obtain 1. Open [MoonPay Dashboard](https://dashboard.moonpay.com/developers) 2. Get publishable and secret keys from the API Keys section 3. Configure webhook key in the Webhooks section ## Cleanup Job Authentication **Required** for the auto-expire pending transactions cron job. | Variable | Description | Example | |----------|-------------|---------| | `CLEANUP_SECRET_TOKEN` | Auth token for `/api/cleanup-pending-transactions` | Random 64-char hex | This token authenticates the cron endpoint that automatically expires pending transactions older than 1 hour. The cron runs every 15 minutes (`*/15 * * * *`). ### How to Generate ```bash # Generate a cryptographically random 64-character token node -e "console.log(require('crypto').randomBytes(32).toString('hex'))" ``` ::: danger Store this token in your password manager and deployment platform. **Never commit the actual token to git.** ::: ## Environment-Specific Setup ### Local Development ```bash # .env (gitignored) — copy from .env.example cp .env.example .env # .env.local (gitignored) — optional overrides VITE_PUBLIC_POSTHOG_KEY= # Disable analytics locally ``` ### Production (Shopify Oxygen) Set environment variables in: 1. **Shopify Admin** → Hydrogen → Environment Variables 2. **Or via CLI:** `shopify hydrogen env push` ### CI/CD (GitHub Actions) Set as GitHub Secrets: 1. Repository → Settings → Secrets and variables → Actions 2. Add each production variable as a secret ## Validation Checklist Before deploying, verify: * \[ ] All required Shopify variables are set * \[ ] `SESSION_SECRET` is unique per environment * \[ ] `SUPABASE_SERVICE_ROLE_KEY` is not exposed in logs * \[ ] `PLISIO_SECRET_KEY` matches Plisio dashboard * \[ ] `MOONPAY_WEBHOOK_KEY` matches MoonPay dashboard * \[ ] `MOONPAY_ENVIRONMENT` is set to `production` (not `sandbox`) * \[ ] `CLEANUP_SECRET_TOKEN` is secure and stored safely --- --- url: /getting-started/local-development.md description: How to set up and run UberLotto v2 locally for development. --- # Local Development Setup Step-by-step guide to get UberLotto v2 running on your local machine. ## Prerequisites | Requirement | Version | Notes | |------------|---------|-------| | Node.js | >= 18.0.0 | LTS recommended | | npm | Included with Node.js | Used for package management | | Shopify CLI | Latest | Install via `npm install -g @shopify/cli` | | Git | Latest | For cloning the repository | ::: tip The project uses Node.js >= 18.0.0 as specified in `package.json` engines. Use [nvm](https://github.com/nvm-sh/nvm) to manage Node.js versions. ::: ## Setup Steps ### 1. Clone the Repository ```bash git clone cd uberlotto-v2 ``` ### 2. Install Dependencies ```bash npm ci ``` ::: warning Use `npm ci` (not `npm install`) for deterministic installs from the lockfile. This ensures all developers use identical dependency versions. ::: ### 3. Configure Environment Variables ```bash cp .env.example .env ``` Fill in all required values in `.env`. See the [Environment Variables Reference](/getting-started/environment-variables) for details on each variable. ### 4. Link Shopify Store Connect your local project to a Shopify store: ```bash shopify hydrogen link ``` This links the project to your Shopify store and configures the Hydrogen storefront. You'll be prompted to select the store and storefront to connect. ### 5. Start the Dev Server ```bash npm run dev ``` This runs `shopify hydrogen dev --codegen`, which: * Starts the Vite development server with HMR * Spins up a local Oxygen runtime (Mini Oxygen) * Watches GraphQL files and auto-generates TypeScript types * Enables the PWA service worker in dev mode The dev server will be available at `http://localhost:3000`. ### 6. Customer Account API Setup To use the `/account` section locally, follow the Shopify Customer Account API setup: 1. Set up a public domain for local development (e.g., using ngrok or Cloudflare tunnel) 2. Configure the Customer Account API redirect URIs in Shopify Admin See [Shopify's Customer Account API guide](https://shopify.dev/docs/custom-storefronts/building-with-the-customer-account-api/hydrogen#step-1-set-up-a-public-domain-for-local-development) for detailed instructions. ## Common Development Commands ### GraphQL Codegen Generate TypeScript types from GraphQL queries: ```bash npm run codegen ``` This runs `shopify hydrogen codegen` to scan all `.ts`/`.tsx` files for GraphQL queries and generates type definitions in `storefrontapi.generated.d.ts` and `customer-accountapi.generated.d.ts`. ::: tip Codegen runs automatically in watch mode during `npm run dev`. Run it manually after adding new GraphQL queries outside the dev server. ::: ### TypeScript Type Checking ```bash npm run typecheck ``` Runs `tsc --noEmit` to check all TypeScript files without producing output. ### Linting ```bash npm run lint ``` Runs ESLint across the project. The config is in `eslint.config.js` and uses `@typescript-eslint`, React, and JSX a11y plugins. ### Production Build ```bash npm run build ``` Creates a production build with `shopify hydrogen build --codegen`. Output goes to the `dist/` directory. ### Preview Production Build ```bash npm run preview ``` Runs `shopify hydrogen preview --build` to preview the production build locally using Mini Oxygen. ## Project Structure at a Glance After setup, the key directories are: ``` uberlotto-v2/ ├── app/ # Application source code │ ├── routes/ # File-based route definitions │ ├── components/ # React components │ ├── lib/ # Core utilities and context │ ├── store/ # Zustand state stores │ └── ... ├── public/ # Static assets ├── supabase/ # Database migrations ├── server.ts # Oxygen worker entry point ├── vite.config.ts # Vite + Hydrogen + PWA config └── .env # Environment variables (gitignored) ``` See the [Project Structure](/architecture/project-structure) guide for a detailed breakdown. ## Vite Configuration The project uses several Vite plugins configured in `vite.config.ts`: | Plugin | Purpose | |--------|---------| | `@tailwindcss/vite` | Tailwind CSS v4 integration | | `@shopify/hydrogen/vite` | Hydrogen framework support | | `@shopify/mini-oxygen/vite` | Local Oxygen runtime emulation | | `@react-router/dev/vite` | React Router v7 file-based routing | | `vite-tsconfig-paths` | TypeScript path alias resolution | | `vite-plugin-pwa` | PWA manifest, service worker, offline support | ### PWA Dev Mode The PWA service worker is enabled in development (`devOptions.enabled: true`). This allows testing offline behavior and install prompts during local development. ## Troubleshooting ### `SESSION_SECRET environment variable is not set` Make sure your `.env` file exists and contains a valid `SESSION_SECRET` value. The app will throw at startup if this is missing. ### GraphQL type errors after adding queries Run `npm run codegen` to regenerate types, or restart the dev server which runs codegen in watch mode. ### CJS/ESM compatibility errors If you see `ReferenceError: module is not defined` for a dependency, add the package to the `ssr.optimizeDeps.include` array in `vite.config.ts`. --- --- url: /products/metafields-reference.md description: >- Complete catalog of all Shopify metafields used in UberLotto v2, organized by scope — product, variant, page, and shop level. --- # Metafields Reference Complete catalog of every Shopify metafield used in UberLotto v2. Organized by scope (product, variant, page, shop) with namespace, key, type, and purpose. ## Product-Level Metafields These metafields are set on individual Shopify products. | Namespace | Key | Full Reference | Type | Required | Description | | --- | --- | --- | --- | --- | --- | | `custom` | `game_slug` | `custom.game_slug` | Single line text | Yes | Unique slug linking to Supabase data (e.g., `powerball`) | | `custom` | `game_db_name` | `custom.game_db_name` | Single line text | No | Legacy/unused. Stores Supabase game name but is not consumed by application logic. `game_slug` is the primary identifier. | | `custom` | `draw_games_schedule` | `custom.draw_games_schedule` | Single line text | Yes | Drawing days (e.g., `Mon, Wed, Sat`) | | `custom` | `lottery_pool_cutoff_time` | `custom.lottery_pool_cutoff_time` | Single line text | Yes | Entry cutoff in 24h format (e.g., `22:00`) | | `custom` | `logo_image` | `custom.logo_image` | File reference | No | Custom logo for game cards | | `custom` | `lottery_ticket_multiplier` | `custom.lottery_ticket_multiplier` | Single line text | No | Enables multiplier display (`true`/`false`) | | `custom` | `enable_play_options` | `custom.enable_play_options` | Single line text | No | Enables play options UI (`true`/`false`) | | `custom` | `disabled_days` | `custom.disabled_days` | JSON | No | Days game is unavailable (e.g., `["Sun"]`) | ### GraphQL Query All product metafields are fetched in `app/graphql/game-detail/GameDetailQuery.ts`: ```graphql product(handle: $handle) { gameSlug: metafield(namespace: "custom", key: "game_slug") { value } gameDbName: metafield(namespace: "custom", key: "game_db_name") { value } # Queried but unused drawGamesSchedule: metafield(namespace: "custom", key: "draw_games_schedule") { value } lotteryPoolCutoffTime: metafield(namespace: "custom", key: "lottery_pool_cutoff_time") { value } logoImage: metafield(namespace: "custom", key: "logo_image") { reference { ... on MediaImage { image { url } } } } lotteryTicketMultiplier: metafield(namespace: "custom", key: "lottery_ticket_multiplier") { value } enablePlayOptions: metafield(namespace: "custom", key: "enable_play_options") { value } enabledDays: metafield(namespace: "custom", key: "disabled_days") { value } } ``` ::: tip The `game_slug` metafield is the **only** identifier that connects Shopify products to Supabase data. It replaced an earlier `game_type` numeric ID system. ::: ## Variant-Level Metafields These metafields are set on individual product variants and control per-variant quantity limits. | Namespace | Key | Full Reference | Type | Status | Description | | --- | --- | --- | --- | --- | --- | | `cart` | `max_quantity` | `cart.max_quantity` | Integer | **Current** | Maximum quantity per cart for this variant | | `limits` | `max_quantity` | `limits.max_quantity` | Integer | **Legacy** | Legacy max quantity (fallback only) | ### Priority Chain When determining the max quantity for a variant, the system checks in this order: 1. **Shopify native `quantityRule`** (highest priority) — Set in variant inventory settings 2. **`cart.max_quantity`** metafield — Current namespace 3. **`limits.max_quantity`** metafield — Legacy fallback ### GraphQL Query ```graphql variants(first: 250) { nodes { quantityRule { maximum minimum increment } maxQuantity: metafield(namespace: "cart", key: "max_quantity") { value type } legacyMaxQuantity: metafield(namespace: "limits", key: "max_quantity") { value type } } } ``` ## Page-Level Metafields (Home Page) These metafields are set on the Shopify Page with handle `home` and control home page behavior. | Namespace | Key | Full Reference | Type | Description | | --- | --- | --- | --- | --- | | `custom` | `banner` | `custom.banner` | JSON | Banner configuration data (title, message, link, etc.) | | `custom` | `product_limit` | `custom.product_limit` | Single line text | Number of products to display on home page | | `custom` | `lottery_collection_id` | `custom.lottery_collection_id` | Single line text | Override the default lottery collection ID | ### GraphQL Query Fetched in `app/routes/($locale)._index.tsx`: ```graphql page(handle: "home") { banner: metafield(namespace: "custom", key: "banner") { value } productLimit: metafield(namespace: "custom", key: "product_limit") { value } lotteryCollectionId: metafield(namespace: "custom", key: "lottery_collection_id") { value } } ``` ### Defaults | Setting | Default | Defined In | | --- | --- | --- | | Product limit | `10` (range: 1–250) | `app/lib/settings.ts` | | Collection ID | `gid://shopify/Collection/429088702676` | `app/lib/settings.ts` | | Banner | `null` (no banner) | — | The `parseProductLimit()` and `parseCollectionId()` functions in `app/lib/settings.ts` handle validation and fallback to defaults. ## Shop-Level Metafields These metafields are set on the Shopify Shop object and define global quantity limits. See [Quantity Limits](./quantity-limits.md) for full details. ### Current Namespace (`cart.*`) | Namespace | Key | Full Reference | Type | Description | | --- | --- | --- | --- | --- | | `cart` | `max_quantity_global` | `cart.max_quantity_global` | Integer | Global max quantity across all product types | | `cart` | `max_quantity_lottery` | `cart.max_quantity_lottery` | Integer | Max quantity for lottery products | | `cart` | `max_quantity_scratch_card` | `cart.max_quantity_scratch_card` | Integer | Max quantity for scratch card products | | `cart` | `max_quantity_game` | `cart.max_quantity_game` | Integer | Max quantity for game products | ### Legacy Namespace (`limits.*`) | Namespace | Key | Full Reference | Type | Status | | --- | --- | --- | --- | --- | | `limits` | `global_limit` | `limits.global_limit` | Integer | Legacy fallback | | `limits` | `lottery_limit` | `limits.lottery_limit` | Integer | Legacy fallback | | `limits` | `scratch_card_limit` | `limits.scratch_card_limit` | Integer | Legacy fallback | | `limits` | `game_limit` | `limits.game_limit` | Integer | Legacy fallback | ### Fallback Chain For each product type, the system tries the `cart.*` metafield first, then falls back to `limits.*`: ``` cart.max_quantity_lottery → limits.lottery_limit → cart.max_quantity_global → limits.global_limit ``` Implementation: `app/lib/shop-limits.server.ts` ## Customer Account Metafields | Namespace | Key | Full Reference | Type | Description | | --- | --- | --- | --- | --- | | `favorites` | `products` | `favorites.products` | JSON | Customer's favorited product IDs | ## Complete Metafield Map Summary of all metafields by scope: | Scope | Count | Namespaces Used | | --- | --- | --- | | Product | 8 | `custom` | | Variant | 2 | `cart`, `limits` | | Page (Home) | 3 | `custom` | | Shop | 8 | `cart`, `limits` | | Customer | 1 | `favorites` | | **Total** | **22** | | ::: warning When creating metafield definitions in Shopify Admin, ensure the namespace and key match exactly. Metafields are case-sensitive. ::: --- --- url: /database/migrations.md description: >- Database migration structure, existing migrations, and how to create and run new migrations for UberLotto v2. --- # Migration Guide UberLotto v2 uses Supabase migrations to manage database schema changes. This guide covers the migration directory structure, existing migrations, and how to create new ones. ## Migration Directory ``` uberlotto-v2/ └── supabase/ └── migrations/ ├── 20251113002700_create_security_tables.sql ├── 20251113002700_create_security_tables_rollback.sql └── 20260218000000_payment_logging_system.sql ``` Migrations follow the Supabase CLI naming convention: ``` _.sql ``` ## Existing Migrations ### `20251113002700_create_security_tables.sql` **Date:** 2025-11-13 **Purpose:** Plisio Payment Gateway Hardening — replay attack prevention and security logging. #### Tables Created | Table | Primary Key | Description | | --- | --- | --- | | `webhook_nonces` | `id BIGSERIAL` | Replay attack prevention via SHA-256 nonce hashes | | `security_events` | `id BIGSERIAL` | Security audit log with GDPR-compliant data masking | ::: warning Both tables use **BIGSERIAL** primary keys (auto-incrementing bigint), not UUID. This differs from the other tables in the database which use `BIGINT GENERATED BY DEFAULT AS IDENTITY`. ::: #### Indexes Created **webhook\_nonces:** * `idx_webhook_nonces_nonce_hash` — fast duplicate lookup * `idx_webhook_nonces_expires_at` — efficient cleanup * `idx_webhook_nonces_txn_id` — partial index on `txn_id` * `idx_webhook_nonces_created_at` — descending time order **security\_events:** * `idx_security_events_event_type` * `idx_security_events_severity` * `idx_security_events_created_at` — descending time order * `idx_security_events_txn_id` — partial index * `idx_security_events_client_ip` — partial index * `idx_security_events_status` * `idx_security_events_source` * `idx_security_events_type_created` — composite index `(event_type, created_at DESC)` #### RLS Policies Both tables enable Row Level Security with: * **Service role full access** — `auth.role() = 'service_role'` * **Admin read access** (security\_events only) — authenticated users with `role = 'admin'` in JWT The migration uses conditional policy creation (`IF NOT EXISTS` via `pg_policies` check) to safely handle re-runs. #### Database Functions Created | Function | Purpose | Schedule | | --- | --- | --- | | `cleanup_expired_nonces()` | Deletes nonces past their `expires_at` | Every 5 minutes | | `cleanup_old_security_events()` | Removes events older than 1 year | Monthly | | `get_recent_security_events()` | Queries recent events with filtering | On demand | All functions are created with `SECURITY DEFINER` and granted `EXECUTE` permission to the `service_role`. #### Rollback A companion rollback script is provided at: ``` supabase/migrations/20251113002700_create_security_tables_rollback.sql ``` It drops all functions and tables created by the migration: ```sql DROP FUNCTION IF EXISTS get_recent_security_events(TEXT, TEXT, INTEGER, INTEGER) CASCADE; DROP FUNCTION IF EXISTS cleanup_old_security_events() CASCADE; DROP FUNCTION IF EXISTS cleanup_expired_nonces() CASCADE; DROP TABLE IF EXISTS security_events CASCADE; DROP TABLE IF EXISTS webhook_nonces CASCADE; ``` ::: danger Running the rollback deletes **all** security event logs and webhook nonces permanently. Back up data before running. ::: *** ### `20260218000000_payment_logging_system.sql` **Date:** 2026-02-18 **Purpose:** Unified Payment Transaction Logging — replaces `wallet_load_credit` and `pending_transactions` with a single `payment_transactions` table and an immutable `payment_transaction_events` audit log. #### What It Drops | Object | Type | Reason | | --- | --- | --- | | `wallet_load_credit` | Table | Replaced by `payment_transactions` | | `pending_transactions` | Table | Replaced by `payment_transactions` | | `pending_transactions_id_seq` | Sequence | Orphaned after table drop | ::: danger The migration drops the legacy tables. Existing data is migrated into `payment_transactions` before the drop — see [Data Migration Logic](#data-migration-logic) below. ::: #### Tables Created | Table | Primary Key | Description | | --- | --- | --- | | `payment_transactions` | `id UUID` (gen\_random\_uuid) | Unified payment records for MoonPay + Plisio | | `payment_transaction_events` | `id BIGINT` (GENERATED ALWAYS AS IDENTITY) | Immutable status change audit log | #### Indexes Created (10) **payment\_transactions:** * `idx_pt_user_email` — user email lookups * `idx_pt_user_id` — partial index on user\_id (WHERE NOT NULL) * `idx_pt_active_status` — partial index on active statuses (pending/processing/awaiting\_confirmation) * `idx_pt_provider_status` — composite `(provider, status)` * `idx_pt_created_at` — `created_at DESC` * `idx_pt_provider_txn_id` — partial index (WHERE NOT NULL) * `idx_pt_order_number` — order number lookups * `idx_pt_expires_pending` — partial index on `expires_at` (WHERE status = 'pending') **payment\_transaction\_events:** * `idx_pte_transaction_id` — event lookups by transaction * `idx_pte_txn_created` — composite `(transaction_id, created_at DESC)` #### Functions Created | Function | Type | Description | | --- | --- | --- | | `set_updated_at()` | Trigger | Auto-sets `updated_at` on row update | | `fn_log_transaction_status_change()` | Trigger | Logs status transitions to events table; reads `app.changed_by` and `app.change_reason` session vars | | `fn_guard_max_events_per_transaction()` | Trigger | Prevents more than 50 events per transaction | | `fn_guard_immutable_completed()` | Trigger | Prevents reopening completed/refunded transactions | | `expire_stale_transactions()` | Utility | Cron: marks pending transactions past `expires_at` as expired | | `archive_old_events(interval)` | Utility | Cleanup: removes events older than specified interval (default 90 days) | | `get_transaction_summary(email)` | Utility | Returns aggregated stats for admin dashboards | | `check_cancellation_rate(email)` | Utility | Anti-abuse: returns FALSE if 10+ cancels/hour | #### Triggers Created | Trigger | Table | Event | Function | | --- | --- | --- | --- | | `trg_payment_transactions_updated_at` | `payment_transactions` | BEFORE UPDATE | `set_updated_at()` | | `trg_log_status_change` | `payment_transactions` | AFTER UPDATE (status) | `fn_log_transaction_status_change()` | | `trg_guard_max_events` | `payment_transaction_events` | BEFORE INSERT | `fn_guard_max_events_per_transaction()` | | `trg_guard_immutable_completed` | `payment_transactions` | BEFORE UPDATE (status) | `fn_guard_immutable_completed()` | #### RLS Policies Both tables enable Row Level Security with: * **Service role full access** — `service_role` bypasses RLS automatically * **Authenticated user read** — users can read their own transactions (matched by `user_email = auth.jwt()->>'email'`) * **Authenticated user events read** — users can read events for their own transactions (via subquery on `payment_transactions`) #### Data Migration Logic {#data-migration-logic} The migration includes data migration from the legacy tables: 1. **From `pending_transactions`:** Maps `customer_email` → `user_email`, `customer_name` → `user_name`, `customer_lastname` → `user_lastname`, `customer_id` → `user_id`, `plisio_txn_id` → `provider_txn_id`, `plisio_invoice_url` → `provider_invoice_url`, `amount_usd` → `amount`. Sets `provider = 'plisio'` for all migrated rows. 2. **From `wallet_load_credit`:** Maps `user_email`, `user_name`, `user_lastname`, `user_wallet_address` → `wallet_address`, `tx_hash`, `tx_amount` → `amount`, `tx_currency` → `crypto_currency`, `tx_amount_usd` → `amount_usd`, `tx_conversion_rate` → `conversion_rate`, `tx_status` → `status` (mapped: 'confirmed' → 'completed'). Sets `provider = 'plisio'` and generates `order_number` as `'LEGACY-' || id` for each row. ::: tip Both data migration steps use `INSERT ... SELECT` for atomicity. If the legacy tables are empty, the migration still succeeds — the INSERTs simply insert zero rows. ::: #### No Rollback Script Unlike the security tables migration, this migration does **not** include a rollback script because the data migration is destructive. If a rollback is needed, restore from a database backup taken before running the migration. ### Tables Without Migrations The following tables were created directly in the Supabase Dashboard (no migration files exist): | Table | Notes | | --- | --- | | `jackpots` | Created manually; stores current jackpot amounts | | `past_drawings` | Created manually; stores historical drawing results | ::: tip If you need to recreate these tables in a new environment, refer to the table definitions in the [Supabase Database Guide](./supabase-guide.md). ::: ## How to Run Migrations ### Prerequisites Install the Supabase CLI: ```bash npm install -g supabase ``` Link your project: ```bash supabase link --project-ref iwmsnkgimodfucvwecvc ``` ### Apply Migrations Push all pending migrations to the remote database: ```bash supabase db push ``` To see migration status: ```bash supabase migration list ``` ### Apply a Specific Migration Manually If you need to run a migration manually (e.g., in the Supabase SQL Editor): 1. Open **Supabase Dashboard** > **SQL Editor** 2. Paste the contents of the migration file 3. Click **Run** ## Creating New Migrations ### Using the Supabase CLI Generate a new migration file: ```bash supabase migration new ``` This creates a timestamped file in `supabase/migrations/`: ``` supabase/migrations/20260210120000_.sql ``` ### Migration Best Practices 1. **Use `IF NOT EXISTS` / `IF EXISTS`** — Makes migrations idempotent and safe to re-run 2. **Always create a rollback script** — Name it `__rollback.sql` 3. **Include verification queries** — Add commented-out queries at the bottom for testing: ```sql -- Verify: SELECT table_name FROM information_schema.tables -- WHERE table_schema = 'public' AND table_name = 'your_table'; ``` 4. **Enable RLS immediately** — Every new table should have RLS enabled with at minimum a service role policy: ```sql ALTER TABLE your_table ENABLE ROW LEVEL SECURITY; CREATE POLICY "Service role full access" ON your_table FOR ALL USING (auth.role() = 'service_role'); ``` 5. **Add indexes for query patterns** — Index columns used in `WHERE`, `ORDER BY`, and `JOIN` clauses 6. **Add table/column comments** — Document purpose directly in the schema: ```sql COMMENT ON TABLE your_table IS 'Description of what this table stores'; COMMENT ON COLUMN your_table.column IS 'Description of this column'; ``` 7. **Test locally first** — Use `supabase start` to run a local Supabase instance and validate migrations before pushing to production ### Migration Template ```sql -- ===================================================================== -- Migration: -- Purpose: -- Date: -- ===================================================================== -- Create table CREATE TABLE IF NOT EXISTS your_table ( id BIGSERIAL PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() -- ... columns ); -- Indexes CREATE INDEX IF NOT EXISTS idx_your_table_created_at ON your_table(created_at DESC); -- RLS ALTER TABLE your_table ENABLE ROW LEVEL SECURITY; DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_policies WHERE schemaname = 'public' AND tablename = 'your_table' AND policyname = 'Service role full access' ) THEN CREATE POLICY "Service role full access" ON your_table FOR ALL USING (auth.role() = 'service_role'); END IF; END $$; -- Comments COMMENT ON TABLE your_table IS 'Description'; -- Verification (run manually) -- SELECT table_name FROM information_schema.tables -- WHERE table_schema = 'public' AND table_name = 'your_table'; ``` --- --- url: /payments/moonpay.md description: >- Complete integration guide for the MoonPay fiat-to-crypto onramp, including URL signing, webhook handling, transaction logging, and multi-layer security pipeline. --- # MoonPay Fiat-to-Crypto Integration [MoonPay](https://www.moonpay.com/) provides a fiat-to-crypto onramp widget that allows users to purchase cryptocurrency using credit cards, bank transfers, and other traditional payment methods. UberLotto integrates MoonPay as an alternative funding method. All transactions are stored in the unified `payment_transactions` table with `provider = 'moonpay'`. The `payment_transaction_events` table automatically logs every status change via a database trigger. ## Key Files | File | Purpose | |------|---------| | `app/routes/api.moonpay-sign.ts` | `POST /api/moonpay-sign` — signs widget URLs + pre-creates pending transaction | | `app/routes/api.moonpay-webhook.ts` | `POST /api/moonpay-webhook` — handles payment notifications with full transaction logging | | `app/components/MoonPayCheckout.tsx` | Client component — renders the MoonPay widget | ## How MoonPay Works MoonPay provides an embeddable widget (hosted at `buy.moonpay.com`) that handles the entire fiat-to-crypto purchase flow. The integration requires two server-side components: 1. **URL Signing** — MoonPay requires all widget URLs to be cryptographically signed to prevent parameter tampering 2. **Webhook Handling** — MoonPay sends transaction status updates via webhooks ## Integration Flow ``` User selects amount in LoadCreditPopup │ ▼ ┌──────────────────────┐ │ Client builds │ ← Constructs MoonPay widget URL with: │ MoonPay URL │ externalCustomerId (user email), │ │ externalTransactionId (order number), │ │ externalCustomerGid (Shopify GID) └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ POST /api/moonpay- │ ← 1. Auth check (must be logged in) │ sign { url } │ 2. HMAC-SHA256 sign the query string │ │ 3. Pre-create pending transaction │ │ in payment_transactions └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ Append signature │ ← Client adds &signature={sig} │ to URL │ to the MoonPay widget URL └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ Open MoonPay widget │ ← User completes purchase on │ (iframe or redirect)│ MoonPay-hosted page └──────────────────────┘ │ │ (async) ▼ ┌──────────────────────┐ │ MoonPay sends │ ← POST /api/moonpay-webhook │ webhook │ with transaction status └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ 5-layer security │ ← See "Webhook Security Pipeline" │ validation │ └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ Find-or-create │ ← 3-step lookup: provider_txn_id → │ transaction record │ order_number → create new (fallback) └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ UPDATE transaction │ ← Status, tx_hash, wallet_address, │ in DB │ crypto details; event auto-logged └──────────────────────┘ ``` ## Widget URL Parameters The client builds the MoonPay widget URL with these custom parameters that link the MoonPay purchase back to the UberLotto user: | Parameter | Value | Purpose | |-----------|-------|---------| | `externalCustomerId` | User's email address | Identifies the customer in webhook payloads | | `externalTransactionId` | Generated order number (e.g. `MP-1699892345678-A3B4C5`) | Links MoonPay transaction to the pre-created `payment_transactions` row | | `externalCustomerGid` | Shopify Customer GID (e.g. `gid://shopify/Customer/123`) | Stored as `user_id` in the transaction record | | `baseCurrencyAmount` | USD amount | Purchase amount | | `walletAddress` | Destination crypto wallet | Where the crypto will be sent | ## URL Signing Endpoint **`POST /api/moonpay-sign`** This endpoint performs two actions: signs the MoonPay URL and pre-creates a pending transaction record. ### Security Layers 1. **Authentication** — requires logged-in user (`context.customerAccount.isLoggedIn()`) 2. **Rate Limiting** — IP-based rate limiting via the shared rate limiter 3. **URL Validation** — only signs URLs pointing to allowed MoonPay hosts 4. **CORS** — origin-restricted (localhost in dev, same-origin in production) ### Allowed Hosts The endpoint only signs URLs targeting these MoonPay domains: ```typescript const allowedHosts = [ 'buy.moonpay.com', 'buy-sandbox.moonpay.com', 'sell.moonpay.com', 'sell-sandbox.moonpay.com', ]; ``` Any URL with a different hostname is rejected with a `400` error. ### Signing Algorithm MoonPay uses **HMAC-SHA256** to sign the query string (including the leading `?`): ```typescript // Extract query string including '?' prefix const queryString = parsedUrl.search; // HMAC-SHA256 using Web Crypto API const cryptoKey = await crypto.subtle.importKey( 'raw', encoder.encode(secretKey), { name: 'HMAC', hash: 'SHA-256' }, false, ['sign'], ); const signatureBuffer = await crypto.subtle.sign('HMAC', cryptoKey, encoder.encode(queryString)); // Base64 encode the signature const signature = btoa(String.fromCharCode(...new Uint8Array(signatureBuffer))); ``` ### Pending Transaction Pre-Creation After signing, the endpoint extracts parameters from the MoonPay URL and creates a pending `payment_transactions` row: | Field | Source | |-------|--------| | `user_email` | `externalCustomerId` param (user's email) | | `order_number` | `externalTransactionId` param | | `user_id` | `externalCustomerGid` param (Shopify GID) | | `amount` / `amount_usd` | `baseCurrencyAmount` param | | `currency` | `baseCurrencyCode` param (defaults to `USD`) | | `wallet_address` | `walletAddress` param | | `provider` | `'moonpay'` | | `status` | `'pending'` | | `metadata` | `{ source: 'moonpay-sign' }` | This pre-creation is **non-blocking** — if it fails, the URL is still signed and the user can proceed. The webhook handler has fallback logic to create a transaction record if no pre-created one is found. ### Request / Response ```typescript // Request POST /api/moonpay-sign { "url": "https://buy.moonpay.com?apiKey=pk_test_...¤cyCode=eth&..." } // Response { "success": true, "signature": "base64EncodedSignature==" } ``` ## Webhook Endpoint **`POST /api/moonpay-webhook`** ### Webhook Security Pipeline When MoonPay sends a payment notification, the request passes through a **5-layer security pipeline**: ``` Webhook Request (POST) │ ┌────┴────┐ │ Layer 1 │ IP Rate Limiting └────┬────┘ Per-IP rate limit check │ ┌────┴────┐ │ Layer 2 │ Global Rate Limiting └────┬────┘ Circuit breaker for infrastructure protection │ ┌────┴────┐ │ Layer 3 │ HMAC-SHA256 Signature Verification └────┬────┘ Parses Moonpay-Signature-V2 header, │ verifies timestamp.body, hex-encoded │ ┌────┴────┐ │ Layer 4 │ Replay Attack Prevention └────┬────┘ Nonce-based via checkGenericWebhookNonce() │ using webhook_nonces table │ ┌────┴────┐ │ Layer 5 │ Security Event Logging └─────────┘ All events logged to security_events table ``` ### Signature Verification ::: warning Correction from Previous Docs The previous documentation showed the webhook signature as base64-encoded. In fact, MoonPay's `Moonpay-Signature-V2` header uses **hex encoding**, NOT base64. The header format is `t=TIMESTAMP,s=SIGNATURE` where the signature is computed over `TIMESTAMP.BODY`. ::: MoonPay sends a signature in the `Moonpay-Signature-V2` HTTP header with the format: `t=TIMESTAMP,s=SIGNATURE`. The verification process: ```typescript // Step 1: Parse header — format is "t=TIMESTAMP,s=SIGNATURE" const parts = signatureHeader.split(','); const timestamp = timestampPart.slice(2); // Remove "t=" const receivedSignature = signaturePart.slice(2); // Remove "s=" // Step 2: Timestamp freshness check (5-minute window) const MAX_AGE_SECONDS = 300; if (Math.abs(nowSeconds - timestampSeconds) > MAX_AGE_SECONDS) { return false; // Reject stale or future webhooks } // Step 3: Create signed payload: "timestamp.body" const signedPayload = `${timestamp}.${body}`; // Step 4: Compute HMAC-SHA256 using MOONPAY_WEBHOOK_KEY const signatureBuffer = await crypto.subtle.sign('HMAC', cryptoKey, payloadData); // Step 5: Convert to hex (NOT base64) const computedSignature = Array.from(new Uint8Array(signatureBuffer)) .map(b => b.toString(16).padStart(2, '0')) .join(''); // Step 6: Constant-time comparison (XOR-based) return timingSafeEqual(computedSignature, receivedSignature); ``` ::: warning Constant-Time Comparison The signature comparison uses a **custom XOR-based `timingSafeEqual()` function**, NOT `crypto.timingSafeEqual`. This is because the Web Crypto API (used in Cloudflare Workers / Oxygen) does not expose Node.js `crypto.timingSafeEqual`. ::: ### Replay Protection The replay protection uses `checkGenericWebhookNonce()` with the MoonPay transaction ID, status, and event type: ```typescript const nonceCheck = await checkGenericWebhookNonce( { rawNonceString: `${payload.data.id}:${payload.data.status}:${payload.type}`, txn_id: payload.data.id, provider: 'moonpay', }, securityContext ); ``` This stores a SHA-256 hash nonce in the `webhook_nonces` table to prevent duplicate processing of the same webhook event. ### Payload Parsing ::: tip JSON String Data MoonPay may send the `data` field as a **JSON string** rather than an object. The webhook handler detects this and parses it automatically: ```typescript if (typeof parsed.data === 'string') { parsed.data = JSON.parse(parsed.data); } ``` ::: ### Webhook Event Types | Event Type | Description | |-----------|-------------| | `transaction_created` | New transaction initiated | | `transaction_updated` | Transaction status changed | | `transaction_failed` | Transaction failed | ### Status Mapping | MoonPay Status | Internal `PaymentStatus` | Description | |----------------|------------------------|-------------| | `completed` | `completed` | Payment successful | | `failed` | `failed` | Payment failed | | `pending` | `pending` | Awaiting payment | | `waitingPayment` | `processing` | Awaiting user payment | | `waitingAuthorization` | `processing` | Awaiting bank authorization | ### Webhook Payload Structure ```typescript interface MoonPayWebhookPayload { type: 'transaction_created' | 'transaction_updated' | 'transaction_failed'; data: { id: string; status: MoonPayTransactionStatus; baseCurrencyAmount: number; quoteCurrencyAmount: number; walletAddress: string; externalTransactionId?: string; // Our order number externalCustomerId?: string; // User's email baseCurrency?: { code: string; name: string }; quoteCurrency?: { code: string; name: string }; createdAt?: string; updatedAt?: string; cryptoTransactionId?: string; // Blockchain tx hash failureReason?: string; }; } ``` ::: tip Response Codes The webhook handler returns `200 OK` for all processed webhooks (even errors) to prevent MoonPay from retrying. This is per MoonPay's best practice documentation. ::: ## Transaction Lookup Strategy The webhook uses a **3-step lookup strategy** to find or create the transaction record: ``` MoonPay webhook received │ ▼ ┌──────────────────────────┐ │ Step 1: Look up by │ ← getTransactionByProviderTxnId(moonpayTxnId) │ provider_txn_id │ Matches previously updated records └──────────┬───────────────┘ │ not found ▼ ┌──────────────────────────┐ │ Step 2: Look up by │ ← getTransactionByOrderNumber(externalTransactionId) │ order_number │ Matches pre-created pending records └──────────┬───────────────┘ │ not found ▼ ┌──────────────────────────┐ │ Step 3: Create new │ ← createPaymentTransaction() │ transaction (fallback) │ Edge case: no pre-created record └──────────────────────────┘ ``` ### What Gets Stored When **updating** an existing transaction: | Field | Source | |-------|--------| | `status` | Mapped from MoonPay status | | `provider_txn_id` | MoonPay's internal transaction ID | | `tx_hash` | `data.cryptoTransactionId` (blockchain hash) | | `wallet_address` | `data.walletAddress` | | `crypto_currency` | `data.quoteCurrency.code` | | `amount_usd` | `data.baseCurrencyAmount` (if USD) | | `completed_at` | Set when status is `completed` | | `error_code` / `error_message` | Set when status is `failed` | | `metadata` | Merged with webhook event details | When **creating** a new fallback transaction: | Field | Source | |-------|--------| | `user_email` | `externalCustomerId` (validated as email, not Shopify GID) | | `order_number` | `externalTransactionId` or `MP-{moonpayTxnId}` | | `provider` | `'moonpay'` | | All other fields | Same as update mapping above | ::: tip Email Validation The webhook handler validates that `externalCustomerId` is a real email address (contains `@`, does not start with `gid://`). If it's a Shopify GID instead of an email, it falls back to `moonpay-unknown@uberlotto.com` to prevent invalid data in the `user_email` column. ::: ## Environment Variables | Variable | Required | Description | |----------|----------|-------------| | `MOONPAY_PUBLISHABLE_KEY` | Yes | Public API key for the MoonPay widget (client-side) | | `MOONPAY_SECRET_KEY` | Yes | Secret key for signing widget URLs (HMAC-SHA256, server-only) | | `MOONPAY_WEBHOOK_KEY` | Yes | Webhook key for verifying incoming webhook signatures (server-only) | | `MOONPAY_WALLET_ADDRESS` | Yes | Destination cryptocurrency wallet address | | `MOONPAY_ENVIRONMENT` | Yes | `sandbox` or `production` — controls which MoonPay hosts are used | ::: danger Security `MOONPAY_SECRET_KEY` and `MOONPAY_WEBHOOK_KEY` are **server-only** secrets and must never be exposed to the client. `MOONPAY_PUBLISHABLE_KEY` is safe for client-side use (passed to the widget). The signing endpoint requires user authentication to prevent unauthorized URL generation. ::: ## Data Flow Summary | Step | Where | What Happens | |------|-------|-------------| | 1 | Client | User selects amount in LoadCreditPopup | | 2 | Client | Widget URL built with `externalCustomerId` (email), `externalTransactionId` (order number), `externalCustomerGid` (Shopify GID) | | 3 | `api.moonpay-sign` | Auth check → rate limit → URL validation → HMAC-SHA256 sign → pre-create pending `payment_transactions` row | | 4 | Client | Appends `&signature={sig}` to URL, opens MoonPay widget | | 5 | MoonPay | User completes purchase on MoonPay-hosted page | | 6 | `api.moonpay-webhook` | Rate limit → signature verification (timestamp.body, hex) → timestamp freshness (5-min) → replay protection → payload parsing | | 7 | `api.moonpay-webhook` | 3-step transaction lookup → UPDATE existing or CREATE new row → event auto-logged | --- --- url: /payments/plisio-crypto.md description: >- Integration guide for the Plisio cryptocurrency payment gateway, including invoice creation, webhook handling, and multi-layer security pipeline. --- # Plisio Cryptocurrency Payments UberLotto uses [Plisio](https://plisio.net/) as its cryptocurrency payment gateway. Plisio provides a white-label invoicing API that lets users pay with Bitcoin, Ethereum, Litecoin, and other cryptocurrencies while the platform receives USD-denominated invoices. ## Key Files | File | Purpose | |------|---------| | `app/lib/plisio.server.ts` | `PlisioClient` class — invoice creation, status polling, HMAC verification | | `app/routes/api.plisio-invoice.ts` | `POST /api/plisio-invoice` — creates new invoices | | `app/routes/api.plisio-webhook.ts` | `GET/POST /api/plisio-webhook` — receives payment notifications | | `app/routes/api.plisio-status.ts` | `GET /api/plisio-status?txn_id=xxx` — polls transaction status | ## Payment Flow All transactions are stored in the unified `payment_transactions` table with `provider = 'plisio'`. The `payment_transaction_events` table automatically logs every status change via a database trigger. ``` User clicks "Pay with Crypto" │ ▼ ┌──────────────────────┐ │ POST /api/plisio- │ │ invoice │ │ { amount, email } │ └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ Rate-limit checks │ ← Max 5 pending / Max 3 per minute │ (per user email) │ └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ Amount validation │ ← Security-hardened: NaN, Infinity, │ (sanitize input) │ precision exploits blocked └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ CREATE pending row │ ← payment_transactions: stores email, │ in DB (status= │ order_number, amount, provider='plisio' │ pending) │ BEFORE calling Plisio API └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ Plisio API call │ ← GET /api/v1/invoices/new │ (create invoice) │ with api_key, source_amount, etc. └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ UPDATE row with │ ← Sets provider_txn_id + invoice_url │ Plisio response │ on the existing pending row └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ Return invoice_url │ ← User redirected to Plisio │ + txn_id + QR code │ payment page └──────────────────────┘ │ │ (async) ▼ ┌──────────────────────┐ │ Plisio sends │ ← POST /api/plisio-webhook │ webhook callbacks │ with payment status updates └──────────────────────┘ │ ▼ ┌──────────────────────┐ │ 6-layer security │ ← See "Webhook Security Pipeline" │ validation │ └──────────┬───────────┘ │ ▼ ┌──────────────────────┐ │ UPDATE existing │ ← Looks up row by order_number, │ transaction row │ updates status, tx_hash, crypto │ │ details; auto-logs event └──────────────────────┘ ``` ## PlisioClient Class The `PlisioClient` in `app/lib/plisio.server.ts` wraps the Plisio REST API: ```typescript import { createPlisioClient } from '@lib/plisio.server'; const client = createPlisioClient({ PLISIO_API_KEY: env.PLISIO_API_KEY, PLISIO_SECRET_KEY: env.PLISIO_SECRET_KEY, }); ``` ### Methods | Method | Description | |--------|-------------| | `createInvoice(params)` | Creates a new payment invoice via `GET /api/v1/invoices/new` | | `checkStatus(txnId)` | Polls transaction status via `GET /api/v1/operations/{txnId}` | | `verifyWebhookSignature(payload, hash)` | HMAC-SHA1 verification using PHP serialize format | ::: tip API Format Plisio uses **GET requests** with query parameters for all API calls, not POST with JSON bodies. The `PlisioClient` constructs URL query strings accordingly. ::: ### Invoice Request Parameters ```typescript interface PlisioInvoiceRequest { source_amount: number; // Amount in USD source_currency: string; // Always "USD" order_number: string; // Generated: "UL-{timestamp}-{random}" order_name: string; // "UberLotto Credit Purchase - {name} - {order}" callback_url: string; // Webhook URL for payment notifications email?: string; // Customer email (optional) } ``` ## Invoice Creation Endpoint **`POST /api/plisio-invoice`** ### Request Body ```json { "amount": 50, "email": "user@example.com", "customer_name": "John", "customer_lastname": "Doe", "customer_id": "gid://shopify/Customer/123" } ``` ### Invoice Rate Limits Before creating an invoice, the endpoint enforces per-user limits: | Check | Limit | Response | |-------|-------|----------| | Pending transactions | Max **5** per user email | `429` — wait for existing to complete | | Recent transactions | Max **3** per minute per user email | `429` — try again in a moment | These limits are enforced via Supabase queries (`countPendingTransactions`, `countRecentTransactions`) and only apply when an email is provided. ### Amount Validation Amounts pass through `validateAmount()` from `app/lib/amount-validator.server.ts`, which prevents: * `NaN` and `Infinity` values * Scientific notation (`1e5`) * Floating-point precision exploits * Overflow attacks * Negative values Failed validations are logged to `security_events` via `logAmountValidationFailed()`. ### Order Number Generation ```typescript function generateOrderNumber(prefix = 'UL'): string { const timestamp = Date.now().toString(); const random = Math.random().toString(36).substring(2, 8); return `${prefix}-${timestamp}-${random}`.toUpperCase(); } // Example: "UL-1699892345678-A3B4C5" ``` ## Webhook Security Pipeline When Plisio sends a payment notification to `/api/plisio-webhook`, the request passes through a **6-layer security pipeline** (plus amount validation during processing): ``` Webhook Request (GET or POST) │ ┌────┴────┐ │ Layer 1 │ IP Whitelist Check └────┬────┘ PLISIO_WEBHOOK_IPS env var │ ┌────┴────┐ │ Layer 2 │ Rate Limiting └────┬────┘ IP (100/min) + Global (1000/min) │ ┌────┴────┐ │ Layer 3 │ Data Extraction + HMAC-SHA1 Verification └────┬────┘ PHP serialize → sorted keys → HMAC compare │ ┌────┴────┐ │ Layer 4 │ Replay Attack Prevention └────┬────┘ SHA-256 nonce in webhook_nonces table (5-min TTL) │ ┌────┴────┐ │ Layer 5 │ Transaction Rate Limiting └────┬────┘ Per txn_id: 10 req/min │ ┌────┴────┐ │ Layer 6 │ Duplicate Transaction Check └────┬────┘ Database-level deduplication │ ┌────┴────┐ │ Process │ Amount & Currency Validation └─────────┘ Update existing transaction in Supabase ``` ### HMAC-SHA1 Verification ::: warning Correction from Manual Docs The signature comparison uses a **custom XOR-based `constantTimeEqual()` function**, NOT `crypto.timingSafeEqual`. This is because the Web Crypto API (used in Cloudflare Workers / Oxygen) does not expose Node.js `crypto.timingSafeEqual`. ::: The HMAC verification follows Plisio's PHP-based algorithm: 1. Remove `verify_hash` from payload 2. Sort remaining keys alphabetically (`ksort` equivalent) 3. Handle special fields (`expire_utc` → string, `tx_urls` → decode HTML entities) 4. Serialize using PHP format (`php-serialize` library) 5. Compute HMAC-SHA1 using Web Crypto API 6. Compare with XOR-based constant-time comparison ```typescript private constantTimeEqual(a: string, b: string): boolean { if (a.length !== b.length) return false; let result = 0; for (let i = 0; i < a.length; i++) { result |= a.charCodeAt(i) ^ b.charCodeAt(i); } return result === 0; } ``` ### Replay Protection Nonce Formula ::: warning Correction from Manual Docs The nonce formula uses **colon separators**, NOT string concatenation with `+`. The fields are `txn_id:status:amount:order_number` — there is no `timestamp` component. ::: ```typescript const nonceString = [ webhookData.txn_id, webhookData.status, webhookData.amount, webhookData.order_number, ].join(':'); // SHA-256 hash via Web Crypto API const hashBuffer = await crypto.subtle.digest('SHA-256', encoder.encode(nonceString)); ``` ## Status Polling Endpoint **`GET /api/plisio-status?txn_id=xxx`** The frontend polls this endpoint to check payment progress. It: 1. Calls Plisio API `GET /api/v1/operations/{txnId}` 2. Maps Plisio status to internal status 3. Looks up the existing `payment_transactions` row by `provider_txn_id` using `getTransactionByProviderTxnId()` 4. Updates the row by its UUID via `updateTransactionStatus()` ### Status Mapping | Plisio Status | Internal Status | PaymentStatus (DB) | |---------------|----------------|-------------------| | `completed` | `confirmed` | `completed` | | `new`, `pending` | `pending` | `pending` | | `expired`, `cancelled`, `error` | `failed` | `error` | ## Environment Variables | Variable | Required | Description | |----------|----------|-------------| | `PLISIO_API_KEY` | Yes | API key from Plisio dashboard | | `PLISIO_SECRET_KEY` | Yes | Secret key for HMAC verification | | `PLISIO_WEBHOOK_IPS` | No | Comma-separated whitelist IPs (e.g. `"216.219.89.38"`) | ::: danger Security Both `PLISIO_API_KEY` and `PLISIO_SECRET_KEY` are **server-only** secrets. They must never be exposed to the client. All Plisio files use the `.server.ts` extension to enforce this. ::: ## Database Interaction Both the invoice endpoint and webhook handler operate on the same `payment_transactions` row: | Step | Operation | Fields Set | |------|-----------|-----------| | Invoice creation | `createPaymentTransaction()` | `user_email`, `order_number`, `amount`, `amount_usd`, `provider='plisio'`, `status='pending'` | | After Plisio API | `updateTransactionStatus()` | `provider_txn_id`, `provider_invoice_url` | | Webhook received | `updateTransactionStatus()` | `status`, `tx_hash`, `crypto_currency`, `wallet_address`, `conversion_rate`, `completed_at` | | Status polling | `updateTransactionStatus()` | `status`, `completed_at` (via `getTransactionByProviderTxnId()` lookup) | The `payment_transaction_events` table automatically captures every status change via a database trigger, providing a complete audit trail. ## Data Flow Summary | Step | Where | What Happens | |------|-------|-------------| | 1 | Client | User selects amount, clicks pay | | 2 | `api.plisio-invoice` | Validate, CREATE pending row in `payment_transactions`, call Plisio API, UPDATE row with `provider_txn_id` | | 3 | Plisio | User pays on Plisio-hosted page | | 4 | `api.plisio-webhook` | Plisio notifies us (6-layer security) | | 5 | `webhook-processor` | UPDATE existing `payment_transactions` row (status, tx\_hash, crypto details); event auto-logged | | 6 | `api.plisio-status` | Client polls; looks up by `provider_txn_id`, updates status by UUID | --- --- url: /getting-started/overview.md description: >- What is UberLotto v2, its tech stack, import conventions, path aliases, and common commands. --- # Project Overview UberLotto v2 is a headless online lottery platform built with **Shopify Hydrogen** and **React Router v7**. It uses Shopify's Storefront and Customer Account APIs for commerce, **Supabase** for database and transaction management, and **Plisio** + **MoonPay** for cryptocurrency payments. The app is deployed as a PWA on **Shopify Oxygen** (Cloudflare Workers). ## Tech Stack | Layer | Technology | |-------|-----------| | Framework | [Shopify Hydrogen](https://shopify.dev/custom-storefronts/hydrogen) | | Routing | React Router v7 (file-based via `@react-router/fs-routes`) | | Build Tool | Vite 6 with `@tailwindcss/vite` | | Language | TypeScript (strict mode) | | Styling | Tailwind CSS v4, Shadcn/ui (New York style), Radix UI, Motion | | State | Zustand (client), TanStack Query (server cache) | | Database | Supabase (PostgreSQL) — custom fetch client, no SDK | | Commerce | Shopify Storefront API (GraphQL), Customer Account API | | Payments | Plisio (crypto), MoonPay (fiat-to-crypto on-ramp), Shopify Checkout (credit loading) | | Analytics | PostHog | | Deployment | Shopify Oxygen (Cloudflare Workers edge runtime) | | PWA | `vite-plugin-pwa` with Workbox service worker | ## Critical Import Rule ::: danger ALWAYS use `react-router` imports This project uses **React Router v7**, NOT Remix. All route-related imports must come from `react-router`. ::: ```typescript // CORRECT import { useLoaderData, Link, Form, redirect } from 'react-router'; import type { LoaderFunctionArgs, ActionFunctionArgs } from 'react-router'; // INCORRECT — never use these import { ... } from '@remix-run/react'; // ❌ import { ... } from 'react-router-dom'; // ❌ ``` ## Path Aliases The project uses TypeScript path aliases defined in `tsconfig.json`: | Alias | Maps To | Usage | |-------|---------|-------| | `~/*` | `app/*` | General app imports | | `@/*` | `./*` | Project root imports | | `@components/*` | `app/components/*` | UI components | | `@lib/*` | `app/lib/*` | Core utilities | | `@utils/*` | `app/utils/*` | Helper functions | | `@hooks/*` | `app/hooks/*` | Custom React hooks | | `@store/*` | `app/store/*` | Zustand stores | | `@graphql/*` | `app/graphql/*` | GraphQL queries | | `@services/*` | `app/services/*` | Business logic services | | `@sections/*` | `app/components/sections/*` | Page sections | | `@skeletons/*` | `app/components/skeletons/*` | Loading skeletons | | `@shared-types/*` | `app/shared-types/*` | Shared type definitions | ## Common Commands ```bash # Start dev server with GraphQL codegen watch npm run dev # Production build npm run build # Preview production build locally npm run preview # TypeScript type checking npm run typecheck # Run ESLint npm run lint # Generate GraphQL types from queries npm run codegen ``` ::: tip All scripts use `shopify hydrogen` CLI under the hood. Running `npm run dev` executes `shopify hydrogen dev --codegen`, which starts the Vite dev server with automatic GraphQL type generation. ::: --- --- url: /architecture/project-structure.md description: >- Detailed breakdown of the UberLotto v2 directory structure and file organization. --- # Project Structure Overview of the UberLotto v2 codebase organization. ## Top-Level Directory ``` uberlotto-v2/ ├── app/ # Application source code │ ├── routes/ # File-based route definitions (68 files) │ ├── components/ # React components (~97 files) │ │ ├── ui/ # Shadcn/ui primitives (40 files) │ │ ├── sections/ # Page section components │ │ ├── skeletons/ # Loading skeleton components │ │ ├── shared/ # Shared components │ │ ├── game-card/ # Game card component variants │ │ ├── game-detail/ # Game detail page components │ │ ├── lottery/ # Lottery-specific components │ │ └── prizes/ # Prize display components │ ├── lib/ # Core utilities (30 files) │ ├── store/ # Zustand state stores (8 files) │ ├── hooks/ # Custom React hooks (13 files) │ ├── graphql/ # GraphQL queries (3 directories) │ ├── services/ # Business logic services │ ├── utils/ # Utility functions (22 files) │ ├── styles/ # CSS/SCSS stylesheets │ ├── providers/ # React context providers │ ├── shared-types/ # Shared TypeScript types │ └── entry.server.tsx # Server entry for SSR ├── public/ # Static assets (favicons, PWA icons) ├── supabase/ # Database migrations │ └── migrations/ # SQL migration files ├── types/ # Global TypeScript type declarations ├── server.ts # Oxygen worker entry point ├── vite.config.ts # Vite + Hydrogen + PWA configuration ├── tsconfig.json # TypeScript configuration with path aliases ├── react-router.config.ts # React Router configuration ├── eslint.config.js # ESLint configuration ├── components.json # Shadcn/ui component configuration ├── package.json # Dependencies and scripts └── env.d.ts # Environment variable type declarations ``` ## `app/routes/` — Route Definitions 68 route files using React Router v7 file-based routing with the `($locale).` prefix for i18n support. ### Naming Convention Routes follow the pattern `($locale)..tsx`: ``` ($locale)._index.tsx → / (homepage) ($locale).collections.$handle.tsx → /collections/:handle ($locale).products.$handle.tsx → /products/:handle ($locale).account._index.tsx → /account ($locale).account.orders.$id.tsx → /account/orders/:id ($locale).pages.game-detail.tsx → /pages/game-detail ``` * `($locale)` — Optional locale prefix (e.g., `/en-US/collections/all`) * `_index` — Index route for a parent path * `$param` — Dynamic URL parameter * `_` prefix — Layout/pathless route (e.g., `account_` for auth routes) ### Route Categories | Category | Files | Examples | |----------|-------|---------| | **Pages** | 17 | `pages.games-schedule`, `pages.past-drawings`, `pages.how-to-play` | | **Account** | 8 | `account._index`, `account.orders`, `account.profile`, `account.load-credit` | | **API** | 10 | `api.plisio-webhook`, `api.moonpay-webhook`, `api.shopify-checkout` | | **Commerce** | 5 | `products.$handle`, `collections.$handle`, `cart`, `discount.$code` | | **Content** | 5 | `blogs._index`, `blogs.$blogHandle.$articleHandle`, `policies.$handle` | | **System** | 5 | `[robots.txt]`, `[sitemap.xml]`, `sitemap.$type.$page[.xml]`, `search` | | **Auth** | 3 | `account_.login`, `account_.logout`, `account_.authorize` | ### API Routes (No UI) API routes export `action` and/or `loader` functions without React components: ``` api.plisio-invoice.ts # Create Plisio crypto invoice api.plisio-webhook.ts # Handle Plisio payment webhooks api.plisio-status.ts # Check Plisio payment status api.moonpay-sign.ts # Sign MoonPay widget URLs api.moonpay-webhook.ts # Handle MoonPay payment webhooks api.shopify-checkout.ts # Create Shopify checkout cart api.cleanup-pending-transactions.ts # Cron: expire stale transactions api.wallet-transactions.ts # Wallet transaction queries api.get-product.$handle.ts # Fetch product by handle api.get-product-variants.$handle.ts # Fetch product variants ``` ## `app/lib/` — Core Utilities Central library for framework-level utilities and server-side modules. | File | Purpose | |------|---------| | `context.ts` | Creates `HydrogenRouterContext` with storefront, cart, session | | `session.ts` | Cookie-based session management | | `fragments.ts` | Shared GraphQL fragments (header, footer, cart) | | `i18n.ts` | Internationalization locale extraction | | `settings.ts` | App-wide settings (collection IDs, display config) | | `auth.ts` | Authentication utilities | | `redirect.ts` | Redirect helpers | | `supabase-client.server.ts` | Custom fetch-based Supabase client (server-only) | | `supabase-client.ts` | Supabase client for non-sensitive operations | | `plisio.server.ts` | Plisio API integration (server-only) | | `rate-limiter.server.ts` | IP and global rate limiting | | `replay-protection.server.ts` | Webhook replay attack prevention | | `security-logger.server.ts` | Security event logging to Supabase | | `security-types.ts` | Security event type definitions | | `webhook-validator.server.ts` | HMAC signature verification | | `webhook-extractor.server.ts` | Webhook payload extraction | | `webhook-processor.server.ts` | Webhook processing logic | | `webhook-utils.server.ts` | Webhook utility functions | | `amount-validator.server.ts` | Payment amount validation | | `shop-limits.server.ts` | Shop-level quantity limits | | `cart-limits.ts` | Cart quantity limits | | `mitt.ts` | Event emitter (mitt library wrapper) | | `favorites-events.ts` | Favorites event system | | `layout-context.ts` | Layout context provider | | `root-data.ts` | Root loader data utilities | | `search.ts` | Search utilities | | `variants.ts` | Product variant utilities | | `utils.ts` | General utilities (`cn()` for class merging) | ### Server vs Client Convention Files ending in `.server.ts` contain server-only code: ```typescript // app/lib/supabase-client.server.ts — NEVER imported by client code // Contains: SUPABASE_SERVICE_ROLE_KEY usage, direct DB operations // app/lib/supabase-client.ts — Safe for client import // Contains: Public anon key operations with RLS protection ``` ::: warning Importing a `.server.ts` file from client code will cause a build error. This boundary is enforced by the bundler. ::: ## `app/store/` — Zustand Stores Client-side state management using Zustand v5. | Store | Purpose | |-------|---------| | `useCartLoadingStore.ts` | Cart loading/updating state | | `useCartPanelStore.ts` | Cart panel open/close state | | `useFavoritesStore.ts` | User's favorite products | | `useGlobalStore.ts` | Global app state | | `useSidebarStore.ts` | Sidebar navigation state | | `StoreProvider.tsx` | Zustand store context provider | | `utils.ts` | Store utility functions | ## `app/hooks/` — Custom Hooks | Hook | Purpose | |------|---------| | `useFavorites.ts` | Favorites CRUD with Shopify metafields | | `useGameCard.ts` | Game card data and interactions | | `useGameCardModal.ts` | Game card modal state | | `useInstantCart.ts` | Quick add-to-cart functionality | | `useLotteryProducts.ts` | Lottery product data fetching | | `usePlisioPayment.ts` | Plisio payment flow management | | `usePriceAnimation.ts` | Jackpot price animation effects | | `usePWA.ts` | PWA install prompt and status | | `usePWAFallback.ts` | PWA fallback for unsupported browsers | | `usePWASplashScreen.ts` | PWA splash screen management | | `useQuickView.ts` | Product quick view modal | | `useMinimumLoadingTime.ts` | Ensures minimum loading indicator display | | `use-mobile.ts` | Mobile viewport detection | ## `app/graphql/` — GraphQL Queries Organized by API source: ``` graphql/ ├── customer-account/ # Customer Account API queries │ ├── CustomerDetailsQuery.ts │ ├── CustomerOrderQuery.ts │ ├── CustomerOrdersQuery.ts │ ├── CustomerAddressMutations.ts │ ├── CustomerUpdateMutation.ts │ └── CustomerFavoritesMutation.ts ├── game-detail/ # Game detail page queries │ └── GameDetailQuery.ts └── load-credits/ # Credit loading queries └── LoadCreditsQuery.ts ``` ::: tip Most Storefront API queries are co-located in route files or in `app/lib/fragments.ts`. The `graphql/` directory is for larger, reusable queries that serve specific features. ::: ## `app/services/` — Business Logic | Service | Purpose | |---------|---------| | `lottery-product-enrichment.service.ts` | Matches Shopify products to Supabase jackpot data via `custom.game_slug` metafield. O(1) slug-based lookup, batch enrichment, deduplication. | ## `app/utils/` — Utility Functions | File | Purpose | |------|---------| | `jackpot.server.ts` | Fetch jackpot data from Supabase by slug | | `pastDrawings.server.ts` | Fetch past drawing results from Supabase | | `shopify-checkout.server.ts` | Checkout cart creation utilities | | `validation.server.ts` | Server-side input validation | | `calculateNextDrawing.ts` | Next drawing date calculation | | `formatJackpotValue.ts` | Jackpot amount formatting | | `priceCalculations.ts` | Price and discount calculations | | `game-card.ts` | Game card display utilities | | `game-logo-mapping.ts` | Game logo asset mapping | | `logo-resolver.ts` | Dynamic logo resolution | | `gameOfTheDay.ts` | "Game of the Day" selection logic | | `games.ts` | Game data utilities | | `prize-utils.ts` | Prize tier display utilities | | `cart/` | Cart-related utility functions | | `countries.ts` | Country data for address forms | | `data.ts` | Static data constants | | `dummyData.ts` | Development placeholder data | | `browser-detection.ts` | Browser/device detection | | `pwa-helpers.ts` | PWA utility functions | | `getUserInitials.ts` | User initials from name | | `debug.ts` | Development debugging helpers | ## `app/components/` — Component Categories ### UI Primitives (`components/ui/`) 40 Shadcn/ui components configured via `components.json` (New York style, Lucide icons): `accordion`, `badge`, `button`, `calendar`, `card`, `checkbox`, `dialog`, `drawer`, `dropdown-menu`, `input`, `pagination`, `popover`, `select`, `separator`, `sheet`, `sidebar`, `skeleton`, `slider`, `switch`, `table`, `tabs`, `textarea`, `toast`, `tooltip`, and more. ### Feature Components | Component | Purpose | |-----------|---------| | `GameCard.tsx` | Lottery game card with jackpot display | | `GamePay.tsx` | Payment page for game purchases | | `MoonPayCheckout.tsx` | MoonPay widget integration | | `PlisioPaymentModal.tsx` | Plisio payment modal | | `LoadCreditPopup.tsx` | Credit loading amount selector | | `LoadCreditActions.tsx` | Credit loading action buttons | | `Cart.tsx`, `CartMain.tsx`, `CartSummary.tsx` | Shopping cart | | `Favorites.tsx`, `FavoritesButton.tsx` | Favorites system | | `Header.tsx`, `Footer.tsx`, `Sidebar.tsx` | Layout components | | `InstallPrompt.tsx`, `PWAProvider.tsx` | PWA install experience | | `TransactionHistory.tsx` | Transaction history display | | `Search.tsx`, `SearchResults.tsx` | Search functionality | ## `supabase/migrations/` — Database Migrations SQL migration files for Supabase schema changes. These are applied in order during deployment. See the [Database documentation](/database/supabase-guide) for table schemas. --- --- url: /security/rate-limiting.md description: >- Three-tier rate limiting system with sliding window algorithm and LRU eviction for webhook and API protection. --- # Rate Limiting Configuration UberLotto implements a three-tier in-memory rate limiting system to protect against abuse, DoS attacks, and replay spam. The rate limiter uses a sliding window algorithm with LRU eviction for memory management. **File:** `app/lib/rate-limiter.server.ts` ## Rate Limit Tiers | Tier | Scope | Limit | Window | Purpose | |------|-------|-------|--------|---------| | Transaction | Per transaction ID | **10 req** | **1 min** | Prevents replay spam of same transaction | | IP Address | Per client IP | **100 req** | **1 min** | Prevents DoS from single source | | Global Circuit Breaker | All requests | **1000 req** | **1 min** | Infrastructure protection | ::: danger Correction The manual documentation listed incorrect values. The table above reflects the actual values from `RATE_LIMIT_CONFIG` in `rate-limiter.server.ts`. ::: ### Additional Invoice Limits Enforced in `app/routes/api.plisio-invoice.ts` (separate from the rate limiter module): | Check | Limit | Scope | |-------|-------|-------| | Pending transactions | Max **5** | Per user email | | New invoices | Max **3/min** | Per user email | These limits query Supabase directly (`countPendingTransactions`, `countRecentTransactions`) rather than using the in-memory rate limiter. ## Implementation Details ### Sliding Window Algorithm The rate limiter uses a **sliding window counter** — more accurate than fixed-window approaches because it considers the actual timestamp of each request: ```typescript check(key: string): RateLimitResult { const now = Date.now(); const entry = this.store.get(key) || { requests: [], firstRequest: now }; // Remove requests outside the sliding window const validRequests = entry.requests.filter( (timestamp) => timestamp > now - this.windowMs ); // Check if limit exceeded if (validRequests.length >= this.maxRequests) { return { isAllowed: false, remaining: 0, resetTime: ... }; } // Record this request validRequests.push(now); this.store.set(key, { requests: validRequests, firstRequest: entry.firstRequest }); return { isAllowed: true, remaining: this.maxRequests - validRequests.length, ... }; } ``` ### LRU Eviction When the store exceeds `maxEntries` (default: **10,000**), the oldest entry is evicted: ```typescript private evictOldest(): void { let oldestKey: string | null = null; let oldestTime = Date.now(); for (const [key, entry] of this.store.entries()) { if (entry.firstRequest < oldestTime) { oldestTime = entry.firstRequest; oldestKey = key; } } if (oldestKey) this.store.delete(oldestKey); } ``` ### Memory Usage * \~100 bytes per entry * 10,000 entries maximum = ~1 MB * Performance: <1ms per check (Map lookup + array filter) ## Singleton Instances Three rate limiter instances are created at module level: ```typescript const transactionLimiter = new RateLimiter({ maxRequests: 10, windowMs: 60_000, maxEntries: 10_000, }); const ipLimiter = new RateLimiter({ maxRequests: 100, windowMs: 60_000, maxEntries: 10_000, }); const globalLimiter = new RateLimiter({ maxRequests: 1000, windowMs: 60_000, maxEntries: 1, // Global only needs one entry }); ``` ## Public API ### Individual Check Functions ```typescript import { checkTransactionRateLimit, checkIPRateLimit, checkGlobalRateLimit, } from '@lib/rate-limiter.server'; // Each returns Promise const result = await checkIPRateLimit(clientIP, context); if (!result.isAllowed) { return Response.json( { error: 'Rate limit exceeded' }, { status: 429, headers: createRateLimitHeaders(result) } ); } ``` ### Combined Check ```typescript import { checkAllRateLimits } from '@lib/rate-limiter.server'; // Checks all three tiers in order: global → IP → transaction const result = await checkAllRateLimits(txnId, clientIP, context); ``` Check order is optimized for fastest fail: 1. **Global** — single lookup, protects infrastructure 2. **IP** — prevents single-source DoS 3. **Transaction** — prevents replay spam ### Helper Functions ```typescript import { extractClientIP, createRateLimitHeaders } from '@lib/rate-limiter.server'; // Extract client IP from request headers const ip = extractClientIP(request); // Priority: cf-connecting-ip → x-forwarded-for (first) → x-real-ip → 'unknown' // Create standard rate limit response headers const headers = createRateLimitHeaders(result); // Returns: X-RateLimit-Limit, X-RateLimit-Remaining, X-RateLimit-Reset, Retry-After ``` ## Response Headers When a rate limit is hit, the response includes standard headers: | Header | Description | Example | |--------|-------------|---------| | `X-RateLimit-Limit` | Maximum requests allowed | `100` | | `X-RateLimit-Remaining` | Requests remaining in window | `0` | | `X-RateLimit-Reset` | Window reset time (ISO 8601) | `2024-01-15T12:01:00.000Z` | | `Retry-After` | Seconds until retry is allowed | `45` | ## Security Event Logging Rate limit violations are automatically logged to `security_events` via `logRateLimitViolation()`: ```typescript { event_type: 'rate_limit_violation', severity: 'warning', source: 'rate_limiter', status: 'blocked', error_message: 'Rate limit exceeded: 100 requests per 1 minute(s)', event_data: { identifier: '192.168.1.100', limit: 100, window_minutes: 1 } } ``` ## Periodic Cleanup Expired entries are cleaned up to prevent memory growth: ```typescript import { cleanupExpiredEntries, startPeriodicCleanup } from '@lib/rate-limiter.server'; // Manual cleanup const stats = cleanupExpiredEntries(); // Returns: { transaction: 5, ip: 12, global: 0, total: 17 } // Automatic cleanup (every 5 minutes) startPeriodicCleanup(); // Call once at app startup ``` ## Monitoring ```typescript import { getRateLimiterStats } from '@lib/rate-limiter.server'; const stats = getRateLimiterStats(); // { transaction: 150, ip: 3200, global: 1, total: 3351 } ``` ::: warning Single-Instance Limitation The in-memory rate limiter only works for **single-instance deployments**. On Shopify Oxygen (stateless edge workers), each worker instance maintains its own rate limit state. For distributed rate limiting, migration to Redis or a similar shared store would be needed. The API interface is designed to make this migration straightforward. ::: ## Configuration Reference All configuration is defined in `RATE_LIMIT_CONFIG`: ```typescript export const RATE_LIMIT_CONFIG = { transaction: { maxRequests: 10, windowMs: 60_000 }, ip: { maxRequests: 100, windowMs: 60_000 }, global: { maxRequests: 1000, windowMs: 60_000 }, maxEntries: 10_000, cleanupIntervalMs: 300_000, // 5 minutes } as const; ``` --- --- url: /security/overview.md description: >- Comprehensive security architecture overview covering network, application, and data layer protections. --- # Security Architecture UberLotto implements a **3-layer defense-in-depth** security model spanning network, application, and data layers. All security-related types and enums are defined in the canonical source file `app/lib/security-types.ts`. ## Security Layers ``` ┌──────────────────────────────────────────────────────────────────┐ │ SECURITY LAYERS │ ├──────────────────────────────────────────────────────────────────┤ │ │ │ Layer 1: NETWORK │ │ ┌──────────────────────────────────────────────────────────┐ │ │ │ • Supabase Network Restrictions (IP Whitelisting) │ │ │ │ • Cloudflare DDoS Protection (via Shopify Oxygen) │ │ │ │ • HTTPS/TLS Encryption │ │ │ └──────────────────────────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ Layer 2: APPLICATION │ │ ┌──────────────────────────────────────────────────────────┐ │ │ │ • Rate Limiting (per-txn, per-IP, global circuit breaker)│ │ │ │ • Input & Amount Validation │ │ │ │ • CSRF Protection (origin-based) │ │ │ │ • Webhook HMAC Signature Verification │ │ │ │ • Endpoint Authentication (Shopify Customer Account) │ │ │ │ • CORS Origin Restriction (getAllowedOrigin) │ │ │ └──────────────────────────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ Layer 3: DATA │ │ ┌──────────────────────────────────────────────────────────┐ │ │ │ • Row Level Security (RLS) in PostgreSQL │ │ │ │ • Replay Attack Prevention (nonce tracking) │ │ │ │ • GDPR-Compliant Security Event Logging │ │ │ │ • Data Encryption at Rest (Supabase-managed) │ │ │ │ • DB-Level Triggers (immutable completed, max events) │ │ │ └──────────────────────────────────────────────────────────┘ │ │ │ └──────────────────────────────────────────────────────────────────┘ ``` ## Network Security ### Supabase Network Restrictions Direct PostgreSQL connections are restricted to whitelisted IP addresses only. | Type | CIDRs | Description | |------|-------|-------------| | Developer IP | `178.148.227.175/32` | Local development access | | Cloudflare IPv4 | 15 ranges | Shopify Oxygen edge servers | | Cloudflare IPv6 | 7 ranges | Shopify Oxygen edge servers | ::: warning Important Limitation Network Restrictions only apply to **direct PostgreSQL connections**. They do NOT restrict: * REST API (PostgREST) * Auth API * Storage API For API security, use Row Level Security (RLS) policies. ::: ### Cloudflare IP Ranges (Whitelisted) **IPv4:** ``` 173.245.48.0/20 103.21.244.0/22 103.22.200.0/22 103.31.4.0/22 141.101.64.0/18 108.162.192.0/18 190.93.240.0/20 188.114.96.0/20 197.234.240.0/22 198.41.128.0/17 162.158.0.0/15 104.16.0.0/13 104.24.0.0/14 172.64.0.0/13 131.0.72.0/22 ``` **IPv6:** ``` 2400:cb00::/32 2606:4700::/32 2803:f800::/32 2405:b500::/32 2405:8100::/32 2a06:98c0::/29 2c0f:f248::/32 ``` ### Managing Restrictions ```bash # View current restrictions supabase network-restrictions --project-ref get --experimental # Update restrictions supabase network-restrictions --project-ref update \ --db-allow-cidr YOUR_IP/32 \ --experimental ``` ## Application Security ### Rate Limiting **File:** `app/lib/rate-limiter.server.ts` Three-tier in-memory rate limiting with sliding window algorithm: | Tier | Scope | Limit | Window | |------|-------|-------|--------| | Transaction | Per transaction ID | 10 req | 1 min | | IP Address | Per client IP | 100 req | 1 min | | Global Circuit Breaker | All requests | 1000 req | 1 min | Additional invoice creation limits (enforced in `api.plisio-invoice.ts`): * Max **5 pending** transactions per user email * Max **3 new** invoices per minute per user email See [Rate Limiting Configuration](/security/rate-limiting) for full details. ### Webhook Security **Files:** * `app/lib/webhook-validator.server.ts` — HMAC verification, IP whitelisting * `app/lib/webhook-processor.server.ts` — Business logic, amount/currency validation * `app/lib/webhook-extractor.server.ts` — Multi-format data extraction * `app/lib/replay-protection.server.ts` — Nonce-based replay prevention #### HMAC Signature Verification Plisio webhooks are verified using **HMAC-SHA1** with PHP serialize format: ::: warning Correction The signature comparison uses a **custom XOR-based `constantTimeEqual()` function**, NOT `crypto.timingSafeEqual`. This is because the Web Crypto API used in Cloudflare Workers does not provide `timingSafeEqual`. ::: ```typescript // Custom constant-time comparison (XOR-based) private constantTimeEqual(a: string, b: string): boolean { if (a.length !== b.length) return false; let result = 0; for (let i = 0; i < a.length; i++) { result |= a.charCodeAt(i) ^ b.charCodeAt(i); } return result === 0; } ``` MoonPay webhooks use **HMAC-SHA256** with timestamp freshness validation: * Header format: `Moonpay-Signature-V2: t=TIMESTAMP,s=SIGNATURE` * Signed payload: `TIMESTAMP.BODY` (timestamp + literal period + raw body) * Signature encoding: hex * Timestamp freshness: rejects webhooks older than 5 minutes * Replay protection: nonce-based via `checkGenericWebhookNonce()` using shared `webhook_nonces` table with 5-minute TTL * Same XOR-based constant-time comparison pattern as Plisio See [Webhook Security Deep Dive](/security/webhook-security) for full pipeline details. ### Endpoint Authentication Several payment API endpoints require Shopify Customer Account authentication via `customerAccount.isLoggedIn()`. Unauthenticated requests receive a `401 Unauthorized` response. | Endpoint | Auth Required | |----------|--------------| | `POST /api/plisio-invoice` | Yes — `customerAccount.isLoggedIn()` | | `GET /api/plisio-status` | Yes — `customerAccount.isLoggedIn()` | | `GET/POST /api/wallet-transactions` | Yes — `customerAccount.isLoggedIn()` | | `POST /api/moonpay-sign` | Yes — `customerAccount.isLoggedIn()` | | `POST /api/shopify-checkout` | Yes — `customerAccount.handleAuthStatus()` | | `POST /api/moonpay-webhook` | No (server-to-server, HMAC-verified) | | `GET/POST /api/plisio-webhook` | No (server-to-server, HMAC-verified) | ### CORS Origin Restriction **File:** `app/lib/cors.server.ts` All payment API endpoints use a shared `getAllowedOrigin()` function that restricts CORS origins: * **Development:** Allows `localhost` and `127.0.0.1` (any port) * **Production:** Only allows the app's own origin (same-origin) Requests from disallowed origins receive no `Access-Control-Allow-Origin` header (or `403 Forbidden` on OPTIONS preflight). This replaces the previous wildcard `*` CORS policy. Endpoints using `getAllowedOrigin()`: `wallet-transactions`, `plisio-invoice`, `plisio-status`, `moonpay-sign`. ### Input Validation **File:** `app/lib/amount-validator.server.ts` All payment amounts are validated against: * NaN / Infinity rejection * Scientific notation blocking * Precision exploit prevention * Range bounds checking ## Data Security ### Row Level Security (RLS) All Supabase tables have RLS enabled. Security tables restrict access to the service role only: ```sql -- Only service role can insert webhook nonces CREATE POLICY "Service role can insert nonces" ON webhook_nonces FOR INSERT TO service_role WITH CHECK (true); -- Only service role can read security events CREATE POLICY "Service role can read events" ON security_events FOR SELECT TO service_role USING (true); ``` ### Security Database Tables #### `webhook_nonces` — Replay Attack Prevention ::: warning Correction from Manual Docs Primary keys use **BIGSERIAL** (auto-incrementing integer), NOT UUID. ::: ```sql CREATE TABLE webhook_nonces ( id BIGSERIAL PRIMARY KEY, nonce_hash TEXT UNIQUE NOT NULL, txn_id TEXT NOT NULL, status TEXT, amount TEXT, order_number TEXT, expires_at TIMESTAMPTZ NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); ``` #### `security_events` — Audit Logging ```sql CREATE TABLE security_events ( id BIGSERIAL PRIMARY KEY, event_type TEXT NOT NULL, severity TEXT NOT NULL, source TEXT NOT NULL, client_ip TEXT, user_email TEXT, txn_id TEXT, order_number TEXT, amount NUMERIC, currency TEXT, user_agent TEXT, status TEXT NOT NULL, error_message TEXT, event_data JSONB, created_at TIMESTAMPTZ DEFAULT NOW() ); ``` ### Database-Level Protections The `payment_transactions` table includes PostgreSQL trigger-based protections: | Trigger | Purpose | |---------|---------| | **Immutable completed** | Prevents updates to transactions with `completed` status — ensures payment records cannot be modified after finalization | | **Max events limiter** | Limits the number of `payment_transaction_events` per transaction to prevent unbounded event accumulation | | **Cancellation rate limiter** | Rate-limits cancellation operations to prevent abuse | These triggers operate at the database level, providing defense-in-depth protection independent of application code. ### Replay Protection Nonce Formula Two nonce functions are available: * **Plisio (specific):** `checkWebhookNonce()` — `SHA256(txn_id:status:amount:order_number)` with colon separators * **Generic (any provider):** `checkGenericWebhookNonce()` — `SHA256(rawNonceString)` where the caller provides the raw string ::: warning Correction from Manual Docs The Plisio nonce formula is `SHA256(txn_id:status:amount:order_number)` with **colon separators** — NOT `SHA256(txn_id + status + amount + timestamp)`. ::: MoonPay uses the generic function with nonce string: `SHA256(moonpay_txn_id:status:event_type)`. Both functions share the same `webhook_nonces` table with 5-minute TTL and fail-open behavior. ### Security Event Types The canonical list of event types is defined in `app/lib/security-types.ts`: | Event Type | Severity | Description | |-----------|----------|-------------| | `webhook_received` | info | Webhook successfully received | | `hmac_failure` | critical | HMAC signature verification failed | | `replay_detected` | critical | Duplicate webhook nonce detected | | `rate_limit_violation` | warning | Rate limit exceeded | | `ip_whitelist_violation` | critical | Request from non-whitelisted IP | | `payment_success` | info | Payment completed successfully | | `payment_failure` | error | Payment processing failed | | `amount_validation_failed` | warning | Amount validation rejected | | `currency_mismatch` | warning | Unexpected source currency | ### Security Event Sources | Source | Module | |--------|--------| | `webhook_validator` | `app/lib/webhook-validator.server.ts` | | `webhook_processor` | `app/lib/webhook-processor.server.ts` | | `rate_limiter` | `app/lib/rate-limiter.server.ts` | | `payment_validator` | Amount / currency validation | | `replay_protection` | `app/lib/replay-protection.server.ts` | | `ip_validator` | IP whitelist checks | | `invoice_handler` | `app/routes/api.plisio-invoice.ts` | ## API Key Security | Key | Exposure | Use Case | |-----|----------|----------| | `SUPABASE_ANON_KEY` | Public (client) | Client-side queries (limited by RLS) | | `SUPABASE_SERVICE_ROLE_KEY` | **PRIVATE** | Server-side operations (bypasses RLS) | | `PLISIO_API_KEY` | **PRIVATE** | Payment gateway API calls | | `PLISIO_SECRET_KEY` | **PRIVATE** | Webhook HMAC verification | | `MOONPAY_SECRET_KEY` | **PRIVATE** | MoonPay URL signing | | `MOONPAY_WEBHOOK_KEY` | **PRIVATE** | MoonPay webhook verification | ### Key Storage | Environment | Storage | |-------------|---------| | Local | `.env` file (gitignored) | | Production | Shopify Oxygen environment variables | | CI/CD | GitHub Secrets | ## Monitoring ### Critical Event Queries ```sql -- Critical events in last 24 hours SELECT * FROM security_events WHERE severity = 'critical' AND created_at > NOW() - INTERVAL '24 hours' ORDER BY created_at DESC; -- Rate limit violations by IP SELECT client_ip, COUNT(*) as hit_count, MAX(created_at) as last_hit FROM security_events WHERE event_type = 'rate_limit_violation' AND created_at > NOW() - INTERVAL '1 hour' GROUP BY client_ip ORDER BY hit_count DESC; ``` ### Alerting Thresholds Set up alerts for: * `severity = 'critical'` events (HMAC failures, replay attacks, IP violations) * High volume of rate limit violations from a single IP * Multiple `payment_failure` events in short succession ## Compliance Notes ### GDPR Data Handling * **PII Masking:** Emails are masked in security logs (e.g., `te***t@example.com`) * **IP Masking:** IPs are partially masked (e.g., `192.168.xxx.xxx`) * **Retention:** Security events retained for 365 days * **Data Purpose:** Logged data is used for security monitoring only * **Deletion:** Users can request data deletion ### Incident Response 1. **Identify** — Check `security_events` table for anomalies 2. **Contain** — Block suspicious IPs via network restrictions 3. **Eradicate** — Fix the vulnerability 4. **Recover** — Restore normal operation 5. **Learn** — Update security measures and documentation --- --- url: /products/quantity-limits.md description: >- How UberLotto v2 enforces quantity limits at the shop and variant level using a dual-namespace metafield system with Shopify native quantityRule integration. --- # Shop & Variant Quantity Limits UberLotto v2 enforces quantity limits at two levels — **shop-wide** (global and per-product-type) and **per-variant**. The system uses a dual-namespace approach (`cart.*` and `limits.*`) with automatic fallback, plus integration with Shopify's native `quantityRule`. ## Architecture ### Implementation The quantity limits system is implemented in `app/lib/shop-limits.server.ts` and queried via the Storefront API GraphQL in `app/graphql/game-detail/GameDetailQuery.ts`. ### Interfaces ```typescript // app/lib/shop-limits.server.ts interface ShopLimits { global?: number; lottery?: number; scratchCard?: number; game?: number; } ``` The `ShopLimits` interface represents the resolved limits after the fallback chain is applied. All fields are optional — if no metafield is set, the limit is undefined (no restriction). ## Dual-Namespace System The system maintains two parallel sets of metafields: | Purpose | Current Namespace | Legacy Namespace | | --- | --- | --- | | Standard | `cart.*` | `limits.*` | | Priority | Checked first | Fallback only | This exists for backward compatibility. New configurations should use the `cart.*` namespace. ### Shop-Level Metafields #### Current (`cart.*`) | Metafield | Description | | --- | --- | | `cart.max_quantity_global` | Maximum quantity across all product types | | `cart.max_quantity_lottery` | Maximum quantity for lottery products | | `cart.max_quantity_scratch_card` | Maximum quantity for scratch card products | | `cart.max_quantity_game` | Maximum quantity for game products | #### Legacy (`limits.*`) | Metafield | Description | | --- | --- | | `limits.global_limit` | Legacy global maximum | | `limits.lottery_limit` | Legacy lottery maximum | | `limits.scratch_card_limit` | Legacy scratch card maximum | | `limits.game_limit` | Legacy game maximum | ### Variant-Level Metafields | Metafield | Status | Description | | --- | --- | --- | | `cart.max_quantity` | **Current** | Per-variant maximum quantity | | `limits.max_quantity` | **Legacy** | Per-variant maximum (fallback) | ## Priority Chain ### Shop-Level Resolution For each product type, the system resolves limits in this order: ``` 1. cart.max_quantity_{type} (e.g., cart.max_quantity_lottery) 2. limits.{type}_limit (e.g., limits.lottery_limit) 3. cart.max_quantity_global (global fallback) 4. limits.global_limit (legacy global fallback) ``` Implementation in `getShopLimitByType()`: ```typescript function getShopLimitByType( shopLimits: ShopLimits, productType: string, ): number | undefined { switch (productType?.toLowerCase()) { case 'lottery': return shopLimits.lottery ?? shopLimits.global; case 'scratch-card': return shopLimits.scratchCard ?? shopLimits.global; case 'game': return shopLimits.game ?? shopLimits.global; default: return shopLimits.global; } } ``` ::: tip The `parseWithFallback()` helper in `shop-limits.server.ts` resolves `cart.*` → `limits.*` for each category before the type-to-global fallback runs. This means `cart.max_quantity_lottery` takes priority over `limits.lottery_limit`, which takes priority over `cart.max_quantity_global`. ::: ### Variant-Level Resolution For individual variants, the priority is: ``` 1. Shopify native quantityRule.maximum (highest priority) 2. cart.max_quantity (variant metafield) 3. limits.max_quantity (legacy variant metafield) ``` The native Shopify `quantityRule` is set in the variant's inventory settings and is the recommended approach for new configurations. ## Shopify Native quantityRule Shopify's built-in quantity rules provide: | Property | Type | Description | | --- | --- | --- | | `maximum` | Integer | Maximum purchasable quantity | | `minimum` | Integer | Minimum purchasable quantity | | `increment` | Integer | Quantity step (e.g., buy in multiples of 5) | ### How to Set 1. Go to **Shopify Admin > Products > \[Product] > Variants** 2. Edit the variant 3. Under **Inventory**, set **Quantity rules**: * **Minimum:** `1` * **Maximum:** `10` (or desired limit) * **Increment:** `1` ### GraphQL Response ```graphql variants(first: 250) { nodes { quantityRule { maximum # null if not set minimum # defaults to 1 increment # defaults to 1 } } } ``` ::: warning If `quantityRule.maximum` is set, it **always** takes precedence over metafield-based limits. This is by design — native Shopify rules are the most reliable enforcement mechanism. ::: ## How to Configure ### Setting Shop-Level Limits Shop metafields must be set via the Shopify Admin API or a metafield editor app, as the Shopify Admin UI does not expose shop-level metafields directly. **Via Shopify Admin API:** ```bash # Set global limit to 20 POST /admin/api/2024-01/metafields.json { "metafield": { "namespace": "cart", "key": "max_quantity_global", "value": "20", "type": "number_integer" } } ``` ```bash # Set lottery-specific limit to 10 POST /admin/api/2024-01/metafields.json { "metafield": { "namespace": "cart", "key": "max_quantity_lottery", "value": "10", "type": "number_integer" } } ``` ### Setting Variant-Level Limits **Option 1: Native quantityRule (Recommended)** 1. Edit the variant in Shopify Admin 2. Set quantity rules under Inventory **Option 2: Metafield** 1. Edit the variant in Shopify Admin 2. Scroll to Metafields 3. Set `cart.max_quantity` to the desired integer value ### Example Configuration A typical lottery product setup: | Level | Metafield | Value | Effect | | --- | --- | --- | --- | | Shop | `cart.max_quantity_global` | `50` | No customer can add more than 50 of any single item | | Shop | `cart.max_quantity_lottery` | `10` | Lottery products capped at 10 | | Variant | `quantityRule.maximum` | `5` | This specific variant capped at 5 | The effective limit for this variant would be **5** (the most restrictive applicable limit). ## Caching Shop limits are fetched using Shopify's `CacheLong()` strategy: ```typescript const {shop} = await storefront.query(SHOP_LIMITS_QUERY, { cache: storefront.CacheLong(), }); ``` This means changes to shop-level metafields may take time to propagate. To force a refresh, clear the Hydrogen cache or wait for the cache TTL to expire. ## Parsing Behavior The `parseMetafieldValue()` function handles edge cases: * `null` or `undefined` metafield → returns `null` (no limit) * Empty string → returns `null` * `"0"` or negative values → returns `null` (treated as no limit) * Valid positive integer string → returns the parsed number ```typescript function parseMetafieldValue(metafield?: ShopMetafield | null): number | null { if (!metafield?.value) return null; const parsed = parseInt(metafield.value, 10); return isNaN(parsed) || parsed <= 0 ? null : parsed; } ``` ## Migration Path If you're migrating from the legacy `limits.*` namespace: 1. Set the new `cart.*` metafields with the same values 2. Leave the `limits.*` metafields in place as fallback 3. Verify the application reads the correct values (check debug logs) 4. Optionally remove `limits.*` metafields after confirming ::: tip The system logs which source was used for each limit at `debug` level. Check server logs for messages like `Set lottery limit: 10 (source: primary)` to confirm the correct namespace is being read. ::: --- --- url: /database/supabase-guide.md description: >- Complete guide to the Supabase database schema, tables, RLS policies, and TypeScript interfaces for UberLotto v2. --- # Supabase Database Guide Complete reference for the Supabase database powering UberLotto v2. Covers all tables, Row Level Security policies, database functions, connection configuration, and TypeScript interfaces. ## Project Information | Property | Value | | --- | --- | | **Project ID** | `iwmsnkgimodfucvwecvc` | | **Organization** | Dynflux | ## Tables Overview | Table | RLS | Description | | --- | --- | --- | | `jackpots` | Yes | Current lottery jackpot amounts | | `past_drawings` | Yes | Historical drawing results (last 90 days) | | `payment_transactions` | Yes | Unified payment records (MoonPay + Plisio) | | `payment_transaction_events` | Yes | Immutable payment status audit log | | `webhook_nonces` | Yes | Replay attack prevention nonces | | `security_events` | Yes | Security audit log | ## Table Definitions ### jackpots Current jackpot amounts for lottery games. Updated by an external data pipeline. ```sql CREATE TABLE jackpots ( id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, created_at TIMESTAMPTZ DEFAULT NOW(), game_name TEXT, slug TEXT NOT NULL, jackpot_numeric NUMERIC, -- Raw numeric value jackpot_alpha_numeric TEXT, -- Display format: "$1.5 Billion" time_stamp_iso TIMESTAMP WITHOUT TIME ZONE ); ``` **Primary Key:** `id` **RLS:** Enabled *** ### past\_drawings Last 90 days of drawing results for all supported games (Powerball, Mega Millions, Megabucks, Lucky for Life, Gimme 5, Lotto America). ```sql CREATE TABLE past_drawings ( id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, created_at TIMESTAMPTZ DEFAULT NOW(), game_name TEXT, slug TEXT, draw_date DATE, drawing_time_et TIME WITHOUT TIME ZONE, draw_datetime_iso TIMESTAMP WITHOUT TIME ZONE, winning_numbers TEXT, bonus_ball NUMERIC, multiplier TEXT, jackpot_status TEXT, -- 'Rollover' or winner state initials drawing_official_number NUMERIC UNIQUE, drawing_official_link TEXT, jackpot_usd NUMERIC ); ``` **Primary Key:** `id` **Unique:** `drawing_official_number` **RLS:** Enabled ::: tip The `slug` column links each drawing to its corresponding Shopify product via the `custom.game_slug` metafield. Both `jackpots.slug` and `past_drawings.slug` must match the product metafield exactly. ::: *** ### payment\_transactions Unified payment records for all cryptocurrency payment providers (MoonPay and Plisio). Replaces the legacy `wallet_load_credit` and `pending_transactions` tables. ```sql CREATE TABLE payment_transactions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id TEXT, -- Shopify GID: gid://shopify/Customer/123 user_email TEXT NOT NULL, user_name TEXT, user_lastname TEXT, order_number TEXT UNIQUE NOT NULL, -- UL-xxx (Plisio), MP-xxx (MoonPay) provider TEXT NOT NULL CHECK (provider IN ('moonpay', 'plisio')), provider_txn_id TEXT, -- Provider's transaction ID provider_invoice_url TEXT, amount NUMERIC NOT NULL CHECK (amount > 0 AND amount <= 10000), amount_usd NUMERIC(12, 2), currency TEXT NOT NULL DEFAULT 'USD', crypto_currency TEXT, wallet_address TEXT, tx_hash TEXT, conversion_rate NUMERIC, status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ( 'pending', 'processing', 'awaiting_confirmation', 'completed', 'failed', 'cancelled', 'expired', 'refunded', 'partially_refunded', 'error' )), error_code TEXT, error_message TEXT, metadata JSONB, ip_address TEXT, -- GDPR-masked user_agent TEXT, -- Truncated to 512 chars created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), completed_at TIMESTAMPTZ, expires_at TIMESTAMPTZ ); ``` **Primary Key:** `id` (UUID) **Unique:** `order_number` **RLS:** Enabled **Indexes:** | Index | Column(s) | Purpose | | --- | --- | --- | | `idx_pt_user_email` | `user_email` | User transaction lookups | | `idx_pt_user_id` | `user_id` (partial, WHERE NOT NULL) | Shopify customer lookups | | `idx_pt_active_status` | `status` (partial, WHERE status IN pending/processing/awaiting\_confirmation) | Active transaction queries | | `idx_pt_provider_status` | `(provider, status)` | Provider-specific filtering | | `idx_pt_created_at` | `created_at DESC` | Time-based queries | | `idx_pt_provider_txn_id` | `provider_txn_id` (partial, WHERE NOT NULL) | Provider transaction lookups | | `idx_pt_order_number` | `order_number` | Order lookups | | `idx_pt_expires_pending` | `expires_at` (partial, WHERE status = 'pending') | Stale transaction cleanup | ::: warning The `status` column accepts 10 values reflecting a full payment lifecycle. The `amount` column has guardrails: must be greater than 0 and no more than 10,000. ::: | Column | Description | | --- | --- | | `order_number` | Unique order ID: `UL-xxx` for Plisio, `MP-xxx` for MoonPay | | `provider` | Payment provider: `'moonpay'` or `'plisio'` | | `status` | Full lifecycle: `pending` → `processing` → `completed` / `failed` / `expired` etc. | | `ip_address` | Client IP, GDPR-masked (e.g., `192.168.xxx.xxx`) | | `user_agent` | Browser user agent, truncated to 512 characters | | `metadata` | Flexible JSONB for provider-specific data | *** ### payment\_transaction\_events Immutable audit log for payment status transitions. Automatically populated by a database trigger on `payment_transactions` status changes. ```sql CREATE TABLE payment_transaction_events ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, transaction_id UUID NOT NULL REFERENCES payment_transactions(id) ON DELETE CASCADE, previous_status TEXT CHECK (previous_status IN ( 'pending', 'processing', 'awaiting_confirmation', 'completed', 'failed', 'cancelled', 'expired', 'refunded', 'partially_refunded', 'error' )), new_status TEXT CHECK (new_status IN ( 'pending', 'processing', 'awaiting_confirmation', 'completed', 'failed', 'cancelled', 'expired', 'refunded', 'partially_refunded', 'error' )), changed_by TEXT, -- 'webhook', 'system', 'cron', 'manual', 'user' change_reason TEXT, metadata JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ``` **Primary Key:** `id` (BIGINT GENERATED ALWAYS AS IDENTITY) **Foreign Key:** `transaction_id` → `payment_transactions(id)` ON DELETE CASCADE **RLS:** Enabled **Indexes:** | Index | Column(s) | Purpose | | --- | --- | --- | | `idx_pte_transaction_id` | `transaction_id` | Event lookups by transaction | | `idx_pte_txn_created` | `(transaction_id, created_at DESC)` | Chronological event history | ::: tip Events are automatically created by the `trg_log_status_change` trigger. A maximum of 50 events per transaction is enforced by `trg_guard_max_events` to prevent abuse. ::: *** ### webhook\_nonces Stores webhook nonces for replay attack prevention. Each nonce auto-expires after 5 minutes. ```sql CREATE TABLE webhook_nonces ( id BIGSERIAL PRIMARY KEY, nonce_hash TEXT UNIQUE NOT NULL, -- SHA-256 hash of txn_id:status:amount:order_number txn_id TEXT NOT NULL, status TEXT, amount TEXT, order_number TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), expires_at TIMESTAMPTZ NOT NULL -- 5 minutes from creation ); ``` **Primary Key:** `id` (BIGSERIAL) **Unique:** `nonce_hash` **RLS:** Enabled **Indexes:** | Index | Column(s) | Purpose | | --- | --- | --- | | `idx_webhook_nonces_nonce_hash` | `nonce_hash` | Fast duplicate lookup | | `idx_webhook_nonces_expires_at` | `expires_at` | Efficient cleanup queries | | `idx_webhook_nonces_txn_id` | `txn_id` (partial) | Transaction lookups | | `idx_webhook_nonces_created_at` | `created_at DESC` | Recent nonce queries | *** ### security\_events Security audit log for the Plisio payment gateway. GDPR compliant with data masking on IP addresses and emails. ```sql CREATE TABLE security_events ( id BIGSERIAL PRIMARY KEY, event_type TEXT NOT NULL, severity TEXT NOT NULL CHECK (severity IN ('info', 'warning', 'error', 'critical')), source TEXT NOT NULL, client_ip TEXT, -- Masked: 192.168.xxx.xxx user_email TEXT, -- Masked: te***t@example.com txn_id TEXT, order_number TEXT, amount NUMERIC(12, 2), currency TEXT, user_agent TEXT, status TEXT, error_message TEXT, event_data JSONB, -- Flexible storage for additional context created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ``` **Primary Key:** `id` (BIGSERIAL) **RLS:** Enabled **Indexes:** | Index | Column(s) | Purpose | | --- | --- | --- | | `idx_security_events_event_type` | `event_type` | Filter by event type | | `idx_security_events_severity` | `severity` | Filter by severity | | `idx_security_events_created_at` | `created_at DESC` | Time-based queries | | `idx_security_events_txn_id` | `txn_id` (partial) | Transaction lookups | | `idx_security_events_client_ip` | `client_ip` (partial) | IP-based searches | | `idx_security_events_status` | `status` | Status filtering | | `idx_security_events_source` | `source` | Source filtering | | `idx_security_events_type_created` | `(event_type, created_at DESC)` | Composite time+type | **Event Types:** | Event Type | Severity | Description | | --- | --- | --- | | `hmac_failure` | error | Invalid webhook signature | | `hmac_success` | info | Signature verified | | `replay_detected` | critical | Duplicate webhook blocked | | `rate_limit_violation` | warning | Rate limit exceeded | | `payment_completed` | info | Successful payment | | `payment_failed` | error | Failed payment | | `webhook_received` | info | Webhook received | ## Row Level Security (RLS) All tables have RLS enabled. The `service_role` bypasses RLS automatically in Supabase. ### Standard Policy (All Tables) ```sql CREATE POLICY "Service role full access" ON FOR ALL USING (auth.role() = 'service_role'); ``` ### Authenticated User Policy: payment\_transactions Authenticated users can read their own transactions: ```sql CREATE POLICY "Users read own transactions" ON payment_transactions FOR SELECT USING ( auth.role() = 'authenticated' AND user_email = (auth.jwt() ->> 'email') ); ``` ### Authenticated User Policy: payment\_transaction\_events Authenticated users can read events for their own transactions: ```sql CREATE POLICY "Users read own events" ON payment_transaction_events FOR SELECT USING ( auth.role() = 'authenticated' AND transaction_id IN ( SELECT id FROM payment_transactions WHERE user_email = (auth.jwt() ->> 'email') ) ); ``` ### Additional Policy: security\_events Admin users have read-only access to security events: ```sql CREATE POLICY "Admin read access" ON security_events FOR SELECT USING ( auth.role() = 'authenticated' AND (auth.jwt() ->> 'role')::text = 'admin' ); ``` ## Database Functions ### Trigger Functions #### set\_updated\_at() Automatically sets `updated_at` to `NOW()` on row updates. Used by `payment_transactions`. ```sql CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; ``` #### fn\_log\_transaction\_status\_change() Automatically logs status transitions to `payment_transaction_events`. Reads session variables `app.changed_by` and `app.change_reason` for audit context. ```sql -- Triggered on UPDATE of payment_transactions when status changes -- Reads: current_setting('app.changed_by', true) -- Reads: current_setting('app.change_reason', true) -- Inserts a row into payment_transaction_events ``` #### fn\_guard\_max\_events\_per\_transaction() Anti-abuse guard that prevents more than 50 events per transaction. Raises an exception if the limit is reached. #### fn\_guard\_immutable\_completed() Prevents reopening settled transactions. Once a transaction reaches `completed`, only a transition to `refunded` is allowed. Transactions in `refunded` status are fully locked. ### Triggers | Trigger | Table | Event | Description | | --- | --- | --- | --- | | `trg_payment_transactions_updated_at` | `payment_transactions` | BEFORE UPDATE | Auto-sets `updated_at` via `set_updated_at()` | | `trg_log_status_change` | `payment_transactions` | AFTER UPDATE (status) | Logs status change to `payment_transaction_events` | | `trg_guard_max_events` | `payment_transaction_events` | BEFORE INSERT | Limits to 50 events per transaction | | `trg_guard_immutable_completed` | `payment_transactions` | BEFORE UPDATE (status) | Prevents reopening completed/refunded transactions | ### Utility Functions #### expire\_stale\_transactions() Cron function that marks pending transactions past their `expires_at` as expired. ```sql -- Finds payment_transactions WHERE status = 'pending' AND expires_at < NOW() -- Updates status to 'expired' -- Run via pg_cron on a schedule ``` #### archive\_old\_events(interval DEFAULT '90 days') Cleanup function that removes old transaction events beyond the retention period. ```sql SELECT archive_old_events(); -- Default: 90 days SELECT archive_old_events('180 days'); -- Custom retention ``` #### get\_transaction\_summary(email TEXT) Returns aggregated transaction statistics for a given user email. Useful for admin dashboards. ```sql SELECT * FROM get_transaction_summary('user@example.com'); ``` #### check\_cancellation\_rate(email TEXT) Anti-abuse function that returns `FALSE` if the user has 10 or more cancellations in the last hour. ```sql SELECT check_cancellation_rate('user@example.com'); -- Returns FALSE if abuse threshold exceeded ``` ### Security Functions (Existing) #### cleanup\_expired\_nonces() Deletes expired webhook nonces. Run every 5 minutes via cron. ```sql CREATE OR REPLACE FUNCTION cleanup_expired_nonces() RETURNS void AS $$ BEGIN DELETE FROM webhook_nonces WHERE expires_at < NOW(); END; $$ LANGUAGE plpgsql SECURITY DEFINER; ``` #### cleanup\_old\_security\_events() Removes security events older than 1 year. Run monthly. ```sql CREATE OR REPLACE FUNCTION cleanup_old_security_events() RETURNS void AS $$ BEGIN DELETE FROM security_events WHERE created_at < NOW() - INTERVAL '1 year'; END; $$ LANGUAGE plpgsql SECURITY DEFINER; ``` #### get\_recent\_security\_events() Query function for monitoring dashboards with optional filtering. ```sql SELECT * FROM get_recent_security_events(); -- Last 24h SELECT * FROM get_recent_security_events(NULL, 'critical', 50, 1); -- Critical, last hour SELECT * FROM get_recent_security_events('hmac_failure', NULL, 100, 6); -- HMAC failures, 6h ``` ## Connection Configuration ### Network Restrictions | Type | CIDRs | Description | | --- | --- | --- | | Developer IP | `178.148.227.175/32` | Local development | | Cloudflare IPv4 | 15 ranges | Shopify Oxygen workers | | Cloudflare IPv6 | 7 ranges | Shopify Oxygen workers | ::: danger The database restricts connections to known IP ranges. When deploying to a new environment, ensure its IP range is added to the Supabase network restrictions. ::: ### Connection Strings **Direct Connection:** ``` postgresql://postgres:[PASSWORD]@db.iwmsnkgimodfucvwecvc.supabase.co:5432/postgres ``` **Connection Pooler (Recommended for production):** ``` postgresql://postgres:[PASSWORD]@aws-0-[region].pooler.supabase.com:6543/postgres ``` ## TypeScript Interfaces Located in `app/shared-types/supabase.ts`. These interfaces are shared between client and server code. ### PaymentStatus ```typescript type PaymentStatus = | 'pending' | 'processing' | 'awaiting_confirmation' | 'completed' | 'failed' | 'cancelled' | 'expired' | 'refunded' | 'partially_refunded' | 'error'; ``` ### PaymentProvider ```typescript type PaymentProvider = 'moonpay' | 'plisio'; ``` ### PaymentTransaction ```typescript interface PaymentTransaction { id: string; // UUID user_id: string | null; user_email: string; user_name: string | null; user_lastname: string | null; order_number: string; provider: PaymentProvider; provider_txn_id: string | null; provider_invoice_url: string | null; amount: number; amount_usd: number | null; currency: string; crypto_currency: string | null; wallet_address: string | null; tx_hash: string | null; conversion_rate: number | null; status: PaymentStatus; error_code: string | null; error_message: string | null; metadata: Record | null; ip_address: string | null; user_agent: string | null; created_at: string; updated_at: string; completed_at: string | null; expires_at: string | null; } ``` ### PaymentTransactionInsert ```typescript type PaymentTransactionInsert = Omit< PaymentTransaction, 'id' | 'created_at' | 'updated_at' >; ``` ### PaymentTransactionUpdate ```typescript type PaymentTransactionUpdate = Partial< Omit >; ``` ### PaymentTransactionEvent ```typescript interface PaymentTransactionEvent { id: number; transaction_id: string; previous_status: PaymentStatus | null; new_status: PaymentStatus; changed_by: string | null; change_reason: string | null; metadata: Record | null; created_at: string; } ``` ### JackpotRecord ```typescript interface JackpotRecord { id: number; game_name: string; slug: string; jackpot_numeric: string | number; jackpot_alpha_numeric: string | null; time_stamp_iso: string; created_at: string; } ``` ### PastDrawingRecord ```typescript interface PastDrawingRecord { id: number; game_name: string; slug: string; draw_date: string; drawing_time_et: string | null; draw_datetime_iso: string | null; winning_numbers: string; bonus_ball: number | null; multiplier: string | null; jackpot_status: string | null; drawing_official_number: number | null; drawing_official_link: string | null; jackpot_usd: number | null; created_at: string; } ``` ## Best Practices 1. **Always use the service role key** for server-side operations 2. **Never expose the service role key** to client-side code 3. **Monitor `security_events`** regularly for anomalies (especially `critical` severity) 4. **Run cleanup functions** on schedule (see [Monitoring & Maintenance](./monitoring.md)) 5. **Keep TypeScript interfaces** in `app/shared-types/supabase.ts` in sync with schema changes 6. **Use the anon key** for read-only operations (jackpots, past drawings) --- --- url: /architecture/overview.md description: >- High-level architecture overview of UberLotto v2 — client, edge, and data layers. --- # System Architecture UberLotto v2 is a three-layer system: a PWA client layer, a Shopify Oxygen edge layer, and a multi-provider data layer. ## Architecture Diagram ``` ┌─────────────────────────────────────────────────────────────────┐ │ CLIENT LAYER │ │ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │ │ │ Web Browser │ │ Mobile PWA │ │ Desktop PWA │ │ │ └───────┬───────┘ └───────┬───────┘ └───────┬───────┘ │ └──────────┼──────────────────┼──────────────────┼────────────────┘ └──────────────────┼──────────────────┘ │ ┌─────────────────────────────▼───────────────────────────────────┐ │ EDGE LAYER (Cloudflare) │ │ ┌───────────────────────────────────────────────────────────┐ │ │ │ Shopify Oxygen Runtime │ │ │ │ ┌─────────────────────────────────────────────────────┐ │ │ │ │ │ Hydrogen SSR Application │ │ │ │ │ │ ┌──────────────┐ ┌──────────┐ ┌──────────┐ │ │ │ │ │ │ │ React Router │ │ Loaders │ │ Actions │ │ │ │ │ │ │ │ (v7) │ │ (Server) │ │ (Server) │ │ │ │ │ │ │ └──────────────┘ └──────────┘ └──────────┘ │ │ │ │ │ └─────────────────────────────────────────────────────┘ │ │ │ └───────────────────────────────────────────────────────────┘ │ └────────────┬──────────────┬──────────────┬──────────────┬───────┘ │ │ │ │ ▼ ▼ ▼ ▼ ┌────────────────┐ ┌────────────┐ ┌────────────┐ ┌────────────┐ │ Shopify │ │ Supabase │ │ Plisio │ │ MoonPay │ │ Storefront API │ │ PostgreSQL │ │ Payment API│ │ On-Ramp │ │ (GraphQL) │ │ REST API │ │ Webhooks │ │ Webhooks │ └────────────────┘ └────────────┘ └────────────┘ └────────────┘ ``` ## Client Layer The application is a Progressive Web App (PWA) that runs across: * **Web Browsers** — full desktop experience * **Mobile PWA** — installable on iOS/Android * **Desktop PWA** — installable on Windows/macOS/Linux **Key capabilities:** | Feature | Implementation | |---------|---------------| | Service Worker | Workbox via `vite-plugin-pwa` — offline caching, runtime caching strategies | | App Manifest | `manifest.webmanifest` with icons, shortcuts, screenshots | | Responsive UI | Tailwind CSS v4 | | Animations | Motion library | | State Management | Zustand stores (cart, favorites, sidebar, global state) | | Install Prompts | Custom `PWAProvider` component with Safari-specific modal | ## Edge Layer (Shopify Oxygen) The application runs on Shopify's Oxygen platform, which uses Cloudflare Workers. **Runtime characteristics:** * Edge-deployed globally (low latency) * Serverless, stateless execution * 50ms CPU time limit per request * No persistent in-memory storage **Framework stack:** | Component | Role | |-----------|------| | Hydrogen | Shopify's React framework for headless commerce | | React Router v7 | File-based routing, server loaders/actions, SSR | | Vite | Build tool, dev server, HMR | | Mini Oxygen | Local dev emulation of the Oxygen runtime | ### Entry Point The server entry (`server.ts`) is an Oxygen worker that: 1. Creates the Hydrogen context (storefront, customer account, cart, session) 2. Passes context to React Router's request handler 3. Handles session cookie commits 4. Falls back to Shopify storefront redirects on 404s ## Data Layer ### Shopify Storefront API (GraphQL) * Product catalog and collections * Cart management * Customer accounts and authentication * Checkout creation * Shop analytics ### Supabase (PostgreSQL) * Jackpot data (`jackpots` table) * Past drawing results (`past_drawings` table) * Wallet transactions * Security events and audit logging * Webhook nonces for replay protection ### Plisio API * Cryptocurrency invoice creation * Webhook notifications for payment status * Transaction status polling ### MoonPay API * Fiat-to-crypto on-ramp purchases * Widget integration via `@moonpay/moonpay-react` * Webhook notifications for transaction updates * HMAC-SHA256 signature verification ### Shopify Checkout (Credit Loading) * "Load Credits" collection with UBL Point products * Cart creation via Storefront API `cartCreate` mutation * Redirect to Shopify checkout for payment processing * Authenticated endpoint — requires customer login ## Key Architectural Decisions ### 1. Custom Fetch-based Supabase Client **Decision:** Use native `fetch` API instead of `@supabase/supabase-js` SDK. **Rationale:** * Avoids SSR compatibility issues in the edge runtime * Smaller bundle size (no SDK overhead) * Direct control over request/response handling * Better error handling for edge-specific constraints **Location:** `app/lib/supabase-client.server.ts` ### 2. Server-Side Security Modules **Decision:** All security-critical logic runs server-side only via `.server.ts` files. **Modules:** | Module | Purpose | |--------|---------| | `rate-limiter.server.ts` | IP and global request rate limiting | | `replay-protection.server.ts` | Webhook replay attack prevention | | `security-logger.server.ts` | Security event audit logging to Supabase | | `webhook-validator.server.ts` | HMAC signature verification | **Rationale:** Edge runtime provides isolation; secrets never exposed to client; centralized security logging. ### 3. Zustand for Client State **Decision:** Use Zustand instead of Redux or React Context. **Rationale:** * Minimal boilerplate * Works well with React 18 concurrent features * Easy integration with TanStack Query for server state * Good DevTools support **Stores:** `useCartLoadingStore`, `useCartPanelStore`, `useFavoritesStore`, `useGlobalStore`, `useSidebarStore` ### 4. File-Based Routing with i18n Prefix **Decision:** Use React Router v7's file-based routing with `($locale).` prefix convention. **Rationale:** * Convention over configuration * Automatic code splitting per route * Server-side loaders and actions per route * i18n support via optional locale URL segment ## Performance Considerations ### Edge Caching Strategy | Asset Type | Strategy | Cache Duration | |-----------|----------|----------------| | Static images | CacheFirst | 30 days | | Google Fonts | CacheFirst | 365 days | | API responses | NetworkFirst | 5 minutes | | Shopify GraphQL | NetworkFirst | 2 minutes | ### Bundle Optimization * **Code Splitting** — automatic per route via React Router * **Tree Shaking** — enabled via Vite * **Asset Inlining** — disabled (`assetsInlineLimit: 0`) for strict CSP compliance * **SSR Optimization** — specific packages pre-bundled for edge compatibility ## Security Architecture **Key security layers:** 1. **Network** — Supabase network restrictions (Cloudflare IPs only) 2. **Application** — Rate limiting, input validation, CSRF protection 3. **Data** — Row Level Security (RLS) in PostgreSQL 4. **Webhooks** — HMAC signature verification, replay protection, IP allowlisting ## Scalability | Component | Scaling Model | |-----------|--------------| | Oxygen | Auto-scales based on traffic (Cloudflare Workers) | | Supabase | Pro plan with connection pooling | | Plisio | Rate limited by API tier | | MoonPay | Rate limited by API tier | **Bottlenecks to monitor:** * Supabase connection pool exhaustion * Webhook processing throughput * Large GraphQL queries to Shopify Storefront API --- --- url: /security/webhook-security.md description: >- Detailed walkthrough of the multi-layer webhook validation pipeline, replay protection, and GDPR-compliant security logging. --- # Webhook Security Deep Dive UberLotto's webhook handlers implement defense-in-depth security pipelines with multi-layer validation, replay protection via database-backed nonces, and GDPR-compliant audit logging with PII masking. Both Plisio and MoonPay webhooks are protected by independent security stacks. ## Involved Files | File | Responsibility | |------|---------------| | `app/routes/api.plisio-webhook.ts` | Plisio entry point — orchestrates 6-layer security | | `app/routes/api.moonpay-webhook.ts` | MoonPay entry point — orchestrates 5-layer security | | `app/lib/webhook-extractor.server.ts` | Multi-format data extraction (URL params, JSON, FormData) | | `app/lib/webhook-validator.server.ts` | Field validation, IP whitelisting, HMAC-SHA1 verification | | `app/lib/webhook-processor.server.ts` | Business logic: status mapping, amount/currency validation, DB save | | `app/lib/webhook-utils.server.ts` | Shared types, response helpers, payload sanitization | | `app/lib/replay-protection.server.ts` | SHA-256 nonce generation and database-backed dedup (Plisio-specific `checkWebhookNonce()` + generic `checkGenericWebhookNonce()`) | | `app/lib/rate-limiter.server.ts` | Three-tier in-memory rate limiting | | `app/lib/security-logger.server.ts` | GDPR-compliant event logging with PII masking | | `app/lib/security-types.ts` | Canonical type definitions, enums, and constants | | `app/lib/cors.server.ts` | Shared CORS origin validation | *** ## Plisio Webhook Security ## Step-by-Step Validation Pipeline The webhook handler (`api.plisio-webhook.ts`) supports both **GET** (query params) and **POST** (JSON or FormData) requests. Both paths follow the same security pipeline: ### Layer 1: IP Whitelisting ``` Request arrives │ ▼ ┌────────────────────────────┐ │ Read PLISIO_WEBHOOK_IPS │ ← Comma-separated env var │ from environment │ └────────────┬───────────────┘ │ ▼ ┌────────────────────────────┐ │ Extract client IP: │ │ cf-connecting-ip │ ← Cloudflare (priority 1) │ x-forwarded-for (first) │ ← Proxy (priority 2) │ x-real-ip │ ← Nginx (priority 3) └────────────┬───────────────┘ │ ▼ ┌────────────────────────────┐ │ IP in whitelist? │ │ YES → continue │ │ NO → 403 Forbidden │ ← Logs ip_whitelist_violation (CRITICAL) └────────────────────────────┘ ``` If `PLISIO_WEBHOOK_IPS` is not configured, this layer is skipped (fail-open). ### Layer 2: Rate Limiting Two rate limit checks run sequentially: | Check | Limit | Failure Response | |-------|-------|-----------------| | IP rate limit | 100 req/min per IP | `429` with `Retry-After` header | | Global circuit breaker | 1000 req/min total | `503` Service Unavailable | Both use the shared `RateLimiter` class with sliding window algorithm. See [Rate Limiting](/security/rate-limiting) for details. ### Layer 3: Data Extraction + HMAC Verification #### Data Extraction The `extractWebhookData()` function auto-detects the format: | Method | Content-Type | Extraction | |--------|-------------|------------| | GET | — | `URLSearchParams` | | POST | `application/json` | `request.json()` | | POST | `multipart/form-data` | `request.formData()` | | POST | `application/x-www-form-urlencoded` | `request.formData()` | All formats use a unified `WebhookDataSource` interface with adapters (`URLSearchParamsWrapper`, `JSONDataWrapper`, `FormDataWrapper`). ::: tip HMAC-Critical Detail The extractor only adds fields that Plisio actually sent. Empty strings are preserved exactly as-is for HMAC computation. Adding default empty strings for missing fields would break the HMAC signature. ::: #### Required Fields ```typescript const requiredFields = ['txn_id', 'status', 'order_number']; ``` Missing required fields result in a `400` error. #### HMAC-SHA1 Verification The `validateWebhook()` function delegates to `PlisioClient.verifyWebhookSignature()`: 1. **Remove** `verify_hash` from payload 2. **Sort** keys alphabetically (PHP `ksort` equivalent) 3. **Handle special fields:** `expire_utc` → string cast, `tx_urls` → HTML entity decode 4. **Serialize** using PHP format (`php-serialize` library) 5. **Compute** HMAC-SHA1 via Web Crypto API 6. **Compare** using XOR-based constant-time function ```typescript // Step 5: HMAC computation const cryptoKey = await crypto.subtle.importKey( 'raw', encoder.encode(secretKey), { name: 'HMAC', hash: 'SHA-1' }, false, ['sign'] ); const signature = await crypto.subtle.sign('HMAC', cryptoKey, encoder.encode(serialized)); // Step 6: Constant-time comparison private constantTimeEqual(a: string, b: string): boolean { if (a.length !== b.length) return false; let result = 0; for (let i = 0; i < a.length; i++) { result |= a.charCodeAt(i) ^ b.charCodeAt(i); } return result === 0; } ``` Failed HMAC verification returns `401 Unauthorized`. ### Layer 4: Replay Attack Prevention The `checkWebhookNonce()` function in `replay-protection.server.ts` implements database-backed nonce tracking: #### Nonce Hash Formula ``` SHA-256( txn_id : status : amount : order_number ) ``` Fields are joined with **colon separators** and hashed via Web Crypto API: ```typescript const nonceString = [ webhookData.txn_id, webhookData.status, webhookData.amount, webhookData.order_number, ].join(':'); const hashBuffer = await crypto.subtle.digest('SHA-256', encoder.encode(nonceString)); ``` #### Algorithm ``` 1. Generate SHA-256 nonce from webhook fields 2. Query webhook_nonces table for existing nonce 3. If EXISTS → replay detected → log CRITICAL event → return 409 4. If NOT EXISTS → insert nonce with 5-minute TTL → continue 5. Handle unique constraint violation (race condition) as replay ``` #### Nonce TTL Nonces expire after **5 minutes**. Expired nonces are cleaned up by: * A scheduled Supabase function (runs every 5 minutes) * Manual cleanup via `cleanupExpiredNonces()` function #### Fail-Open Design ::: tip Fail-Open Philosophy Replay protection **fails open** on all errors (network failures, database errors, missing credentials). The rationale: HMAC verification provides the primary security guarantee. Blocking legitimate webhooks due to infrastructure issues is worse than allowing a rare duplicate through (which is caught by Layer 6 anyway). ::: Error scenarios that fail open: * Missing Supabase credentials * Network errors during database query * Database insert failures (non-duplicate) * Unexpected exceptions ### Layer 5: Transaction Rate Limiting Per-transaction rate limit: **10 requests per minute** per `txn_id`. This prevents rapid-fire webhook spam targeting the same transaction, even if each webhook has a unique nonce (e.g., different status values). ### Layer 6: Duplicate Transaction Check Database-level deduplication checks if a transaction with the same `txn_id` already exists in the `payment_transactions` table: ```typescript const alreadyExists = await checkTransactionExists(payload.txn_id, context); if (alreadyExists) { return createSuccessResponse('Already processed'); } ``` Note: returns `200 OK` (not an error) since the transaction was already successfully processed. ### Processing: Amount & Currency Validation After passing all security layers, the webhook is processed: 1. **Status Mapping:** Plisio status → internal status (`completed`→`confirmed`, `new`/`pending`→`pending`, rest→`failed`) 2. **Currency Validation:** Rejects non-USD source currencies; logs `currency_mismatch` event 3. **Amount Validation:** Enhanced validation prevents precision exploits, overflow, NaN/Infinity 4. **Customer Extraction:** Looks up email from `payment_transactions` pending record, falls back to webhook data 5. **Database Save:** Creates or updates transaction record in `payment_transactions` ## GDPR-Compliant Security Logging The `security-logger.server.ts` module automatically masks PII before writing to the database. ### Email Masking ```typescript maskEmail('test@example.com') // → "te***t@example.com" maskEmail('a@example.com') // → "a***@example.com" ``` ### IP Masking ```typescript maskIP('192.168.1.100') // → "192.168.xxx.xxx" maskIP('2001:0db8:85a3:...') // → "2001:0db8:85a3:0000:xxxx:xxxx:xxxx:xxxx" ``` ### Recursive Sensitive Data Masking The `maskSensitiveData()` function recursively scans JSONB `event_data` fields and masks: | Field Pattern | Action | |--------------|--------| | `*email*` | Email masking | | `*ip*` | IP masking | | `password`, `secret`, `token`, `key`, `api_key`, `api_secret`, `private_key`, `secret_key` | `***REDACTED***` | ### Logging Behavior * **Never throws** — logging failures are caught and logged to console as fallback * **Never blocks** — application flow continues even if database insert fails * **Console fallback** — masked events are written to console if DB is unavailable * **Service role** — uses `SUPABASE_SERVICE_ROLE_KEY` to bypass RLS ## Convenience Logger Functions | Function | Event Type | Severity | |----------|-----------|----------| | `logWebhookReceived()` | `webhook_received` | info | | `logHMACFailure()` | `hmac_failure` | critical | | `logReplayAttack()` | `replay_detected` | critical | | `logRateLimitViolation()` | `rate_limit_violation` | warning | | `logIPViolation()` | `ip_whitelist_violation` | critical | | `logPaymentSuccess()` | `payment_success` | info | | `logPaymentFailure()` | `payment_failure` | error | | `logAmountValidationFailed()` | `amount_validation_failed` | warning | | `logCurrencyMismatch()` | `currency_mismatch` | warning | *** ## MoonPay Webhook Security **File:** `app/routes/api.moonpay-webhook.ts` MoonPay webhooks use a 5-layer security pipeline. Unlike Plisio (which has IP whitelisting and per-transaction rate limiting), MoonPay relies on HMAC-SHA256 with timestamp freshness as its primary authentication mechanism. ### Pipeline Overview ``` MoonPay Webhook POST │ ▼ ┌─────────────────────────────────┐ │ Layer 1: IP Rate Limiting │ │ 100 req/min per IP │ │ Failure → 429 Too Many Requests │ └────────────┬────────────────────┘ │ ▼ ┌─────────────────────────────────┐ │ Layer 2: Global Rate Limiting │ │ 1000 req/min total │ │ Failure → 503 Service Unavail. │ └────────────┬────────────────────┘ │ ▼ ┌─────────────────────────────────┐ │ Layer 3: HMAC-SHA256 Signature │ │ Timestamp freshness (5-min) │ │ Constant-time comparison │ │ Failure → 401 Unauthorized │ └────────────┬────────────────────┘ │ ▼ ┌─────────────────────────────────┐ │ Layer 4: Replay Prevention │ │ Nonce via checkGenericWebhook │ │ Nonce() — shared webhook_nonces │ │ Duplicate → 200 Acknowledged │ └────────────┬────────────────────┘ │ ▼ ┌─────────────────────────────────┐ │ Layer 5: Payload Validation │ │ Required fields: type, data.id │ │ Handle data as JSON string │ │ Security event logging │ └────────────┬────────────────────┘ │ ▼ ┌─────────────────────────────────┐ │ Transaction Processing │ │ Find-or-create in │ │ payment_transactions │ └─────────────────────────────────┘ ``` ### Layer 3: HMAC-SHA256 Signature Verification MoonPay sends the signature in the `Moonpay-Signature-V2` header with the format: ``` Moonpay-Signature-V2: t=1492774577,s=abc123def456... ``` #### Verification Steps 1. **Parse header** — extract `t=TIMESTAMP` and `s=SIGNATURE` parts 2. **Timestamp freshness** — reject if `|now - timestamp| > 300 seconds` (5-minute window) 3. **Construct signed payload** — `TIMESTAMP.BODY` (timestamp + literal period + raw body) 4. **Compute HMAC-SHA256** — using `MOONPAY_WEBHOOK_KEY` via Web Crypto API 5. **Convert to hex** — the signature is hex-encoded (NOT base64) 6. **Constant-time comparison** — XOR-based `timingSafeEqual()` function ```typescript // Signed payload construction const signedPayload = `${timestamp}.${body}`; // HMAC-SHA256 computation const cryptoKey = await crypto.subtle.importKey( 'raw', encoder.encode(webhookKey), { name: 'HMAC', hash: 'SHA-256' }, false, ['sign'] ); const signatureBuffer = await crypto.subtle.sign('HMAC', cryptoKey, encoder.encode(signedPayload)); // Hex encoding (NOT base64) const computedSignature = Array.from(new Uint8Array(signatureBuffer)) .map(b => b.toString(16).padStart(2, '0')) .join(''); // Constant-time comparison timingSafeEqual(computedSignature, receivedSignature); ``` ::: warning Key Differences from Plisio | Property | Plisio | MoonPay | |----------|--------|---------| | Algorithm | HMAC-SHA1 | HMAC-SHA256 | | Signed data | PHP-serialized sorted payload | `timestamp.body` string | | Signature encoding | Hex | Hex | | Timestamp validation | No | Yes (5-min freshness window) | | Header name | `verify_hash` field in payload | `Moonpay-Signature-V2` header | ::: ### Layer 4: Replay Prevention (Generic) MoonPay uses the `checkGenericWebhookNonce()` function (not the Plisio-specific `checkWebhookNonce()`): ```typescript const nonceCheck = await checkGenericWebhookNonce( { rawNonceString: `${payload.data.id}:${payload.data.status}:${payload.type}`, txn_id: payload.data.id, provider: 'moonpay', }, securityContext ); ``` **Nonce string:** `SHA-256(moonpay_txn_id:status:event_type)` — e.g., `SHA-256(txn_abc:completed:transaction_updated)` This uses the same `webhook_nonces` table and 5-minute TTL as Plisio, with the same fail-open behavior. ### Transaction Processing After security validation, the webhook processes transactions into the `payment_transactions` table: 1. **Look up by `provider_txn_id`** — MoonPay's internal transaction ID; matches previously updated records 2. **Look up by `order_number`** — the `externalTransactionId` from the MoonPay URL; matches pre-created pending records from the sign endpoint 3. **Create new** — if neither lookup finds a match, creates a new transaction record **Status mapping:** | MoonPay Status | Internal `PaymentStatus` | |---|---| | `completed` | `completed` | | `failed` | `failed` | | `waitingPayment`, `waitingAuthorization` | `processing` | | `pending` | `pending` | **Payload handling:** MoonPay may send the `data` field as a JSON string. The handler detects this and double-parses it before validation. ::: tip Returns 200 OK even on internal errors to prevent MoonPay from retrying, per MoonPay's best practices documentation. ::: *** ## Generic Replay Protection Function **File:** `app/lib/replay-protection.server.ts` The `checkGenericWebhookNonce()` function provides provider-agnostic replay protection, complementing the Plisio-specific `checkWebhookNonce()`. ### Interface ```typescript interface GenericWebhookNonceData { /** Raw string to hash (e.g. "txn_id:status:event_type") */ rawNonceString: string; /** Transaction ID for logging and storage */ txn_id: string; /** Provider name for logging context */ provider: string; } async function checkGenericWebhookNonce( data: GenericWebhookNonceData, context: SecurityLoggerContext ): Promise ``` ### Algorithm 1. **Hash** — `SHA-256(rawNonceString)` via Web Crypto API 2. **Check** — Query `webhook_nonces` table for existing hash 3. **If exists** → replay detected → log `CRITICAL` event → return `{ isValid: false, isDuplicate: true }` 4. **If not exists** → insert nonce with 5-minute TTL → return `{ isValid: true }` 5. **Race condition** — unique constraint violation (PostgreSQL error 23505) treated as replay ### Comparison: Plisio vs Generic Nonce Functions | Property | `checkWebhookNonce()` | `checkGenericWebhookNonce()` | |----------|----------------------|------------------------------| | Provider | Plisio only | Any provider | | Input | Structured `WebhookDataForNonce` | Raw string via `GenericWebhookNonceData` | | Nonce formula | `SHA-256(txn_id:status:amount:order_number)` | `SHA-256(rawNonceString)` | | Storage fields | `txn_id`, `status`, `amount`, `order_number` | `txn_id` only | | Fail-open | Yes | Yes | | Table | `webhook_nonces` | `webhook_nonces` (shared) | | TTL | 5 minutes | 5 minutes | ## Security Constants Defined in `app/lib/security-types.ts`: ```typescript export const SECURITY_CONSTANTS = { NONCE_TTL_MINUTES: 5, NONCE_CLEANUP_INTERVAL_MINUTES: 5, SECURITY_EVENTS_RETENTION_DAYS: 365, DEFAULT_RATE_LIMIT_WINDOW_MINUTES: 1, DEFAULT_RATE_LIMIT_MAX_REQUESTS: 10, DEFAULT_MIN_AMOUNT_USD: 1, DEFAULT_MAX_AMOUNT_USD: 10000, DEFAULT_MAX_DECIMAL_PLACES: 2, }; ```