The Adverfly Loyalty platform powers cashback-style loyalty programs. Customers earn cashback on every purchase, redeem it via a persistent Shopify discount code, and unlock tier gifts at higher spend levels.

This page is the starting point — read it first, then drill into authentication, REST reference, webhooks, or data model.

Architecture in one diagram

                    ┌────────────────────────────────┐
                    │     api.adverfly.com           │
                    │     (single Custom Domain)     │
                    └─┬──────────┬──────────┬────────┘
                      │          │          │
                ┌─────▼────┐  ┌──▼─────┐  ┌─▼────────────────┐
                │ loyalty  │  │ web    │  │ Storefront SDK   │
                │ /v1/*    │  │ hooks  │  │ window.adverfly  │
                │          │  │ /*     │  │ .loyalty.*       │
                └─────┬────┘  └──┬─────┘  └─┬────────────────┘
                      │          │          │
                      ▼          ▼          ▼
                ┌────────────────────────────────┐
                │  Adverfly backend (Lambda)     │
                │  ─ session-token verify        │
                │  ─ HMAC verify (Shopify)       │
                │  ─ compute-balance on-the-fly  │
                │  ─ sync customer coupon        │
                │  ─ dispatch tier unlocks       │
                └─┬──────────────────────────┬───┘
                  │                          │
            ┌─────▼─────┐              ┌─────▼─────────────┐
            │ Aurora    │              │  Shopify Admin    │
            │ enduser   │              │  GraphQL          │
            │ cluster   │              │  ─ discountCode*  │
            │ ─ loyalty │              │  ─ discountAuto*  │
            │   tables  │              └───────────────────┘
            └───────────┘

Core concepts

Cashback (the earning side)

• A workspace has one loyalty_program (single-program rule).
• Earning is on-the-fly — there are no per-purchase "credit rows" written. Adverfly walks each purchase × cashback_rate × tier_multiplier at query time.
• The rate is configured via a loyalty_earn_rule with trigger_type='purchase'.
• Per-product or per-category overrides live in the same rule's conditions.overrides[] JSONB.

Balance (the redeemable side)

• balance = earned - redeemed, computed live on every read.
• earned comes from the transactions ClickHouse table (Shopify orders ingestion).
• redeemed comes from the loyalty_transactions RDS table — every debit is a row with type='redeemed'.

Persistent customer coupon

One Shopify discount code per customer, minted lazily on the first balance event. The code string never rotates; only its value is updated via the Shopify Admin GraphQL discountCodeBasicUpdate mutation whenever the balance moves.

• Stored in loyalty_coupons (the GraphQL Node GID under shopify_discount_id).
• Set up with usageLimit=null + combinesWith={order,product,shipping} so the customer can reuse it across checkouts and stack it with tier-gift discounts.

Coupon-use detection

When the customer uses the code at checkout, two independent paths debit the balance:

1. Webhook (instant) — Shopify fires orders/create to /webhooks/loyalty/order. The Lambda parses discount_codes[], matches against loyalty_coupons.code, INSERTs into loyalty_transactions with reference_type='coupon_use', then PUTs the new value to Shopify.
2. 15-min API sync (backup) — the existing Shopify Order API poll (fetch-from-shopify.ts) extracts discount info and feeds the same processor.

Both paths use INSERT ... ON CONFLICT (workspace_id, reference_id) DO NOTHING. The partial UNIQUE index guarantees no double-debit regardless of which path arrives first.

Tier unlock gifts

When a customer crosses a tier threshold and that tier has an unlock_reward_id:

1. Local entitlement — loyalty_transactions row inserted, customers.properties.tier_unlock_gifts[tier_id] = null.
2. Shopify automatic discount — discountAutomaticBasicCreate mutation: 100% off the gift product, scoped to the customer. No code entry needed.
3. Storefront snippet — cart-drawer "Claim" button calls /cart/add.js → discount fires at checkout → line drops to €0.
4. Idempotent — tier_unlock_gifts[tier_id] is set to the discount GID after success; next cron run skips. A mid-loop crash retries only the missing mints.

The automatic discount sets combinesWith.orderDiscounts=true so the customer can still use their persistent code on the same order.

Custom earn rules

Non-purchase earn rules (signup bonus, review, birthday, custom). Each rule gets a webhook_token; the merchant pastes the URL into TrustPilot / Yotpo / Zapier / their CRM. See Webhooks.

Terminology

Where things live in code

Read next

• Authentication — api_key, session tokens, webhook signing
• Public API Reference — Every REST endpoint
• Incoming Webhooks — Custom Earn + Shopify Order
• Shopify Setup — End-to-end wiring
• Data Model — Every table

The Loyalty platform uses three different auth mechanisms depending on what's calling:

All three ultimately tie back to ONE secret: loyalty_programs.api_key. It's the program's master key — keep it server-side.

Program API Key

Generated automatically when the loyalty program is created. Find it in the Adverfly dashboard under Loyalty → Integrations (Loyalty API Key card).

Format: adv_loy_<32 hex chars>
Example: adv_loy_8f3c5d1e9a2b7f4c6d8a0e3f1b9c5d7e

Where to use it directly:

• Custom Earn webhooks — set X-Adverfly-Key: <api_key> header (or ?key=<api_key> query param fallback) when firing /webhooks/loyalty/earn.
• Shopify Order webhook — set the api_key as the webhook signing key in Shopify Admin (Adverfly verifies HMAC with the api_key, not Shopify's app secret).
• Storefront session-token block (Shopify Liquid) — store as a Shopify metafield adverfly.api_key, read server-side to mint per-customer session tokens.

Never expose to the browser. The key is the program's root credential — anyone with it can mint balance via custom-earn endpoints.

Session Token (browser SDK)

The Adverfly Loyalty SDK's customer-scoped methods (getBalance, updateProfile, getUnlockedGifts) require a session token that proves "this browser is acting on behalf of <email>". The token is minted server-side via Liquid (or your own framework's equivalent) and assigned to window.adverfly.loyalty_session_token before the SDK is used.

Token shape

<email>:<expiry_unix_ts>:<hex_hmac_sha256>

• email — lowercased customer email.
• expiry_unix_ts — seconds since epoch when the token stops working. We recommend 3600 (1 hour) so each page render mints a fresh one.
• hex_hmac_sha256 — HMAC-SHA256 over <email>:<expiry_unix_ts> using the program api_key as the secret.

Shopify Liquid block

Paste this once near the top of layout/theme.liquid. Reads the api_key from a server-only metafield, mints a fresh token per page render, never leaks the key to the browser.

{%- if customer -%}
  {%- assign adv_api_key  = shop.metafields.adverfly.api_key -%}
  {%- assign adv_exp_ts   = "now" | date: "%s" | plus: 3600 -%}
  {%- assign adv_email    = customer.email | downcase -%}
  {%- assign adv_payload  = adv_email | append: ":" | append: adv_exp_ts -%}
  {%- assign adv_sig      = adv_payload | hmac_sha256: adv_api_key -%}
  <script>
    window.adverfly = window.adverfly || [];
    window.adverfly.loyalty_session_token = {{ adv_payload | append: ":" | append: adv_sig | json }};
    window.adverfly.customer_id           = {{ adv_email | json }};
  </script>
{%- endif -%}

Non-Shopify storefronts

Generate the same shape server-side in whatever stack you use:

// Node example
const crypto = require("crypto");
const email = customer.email.toLowerCase();
const exp = Math.floor(Date.now() / 1000) + 3600;
const payload = `${email}:${exp}`;
const sig = crypto.createHmac("sha256", apiKey).update(payload).digest("hex");
const token = `${payload}:${sig}`;
// Assign to window.adverfly.loyalty_session_token before the SDK is used

Verification (server side)

Done automatically by Adverfly when the browser calls Tier-2 endpoints. If you want to verify yourself (custom backend that proxies to Adverfly), the logic is:

1. Split on : into email, exp, sig
2. Reject if exp < now
3. Recompute HMAC over <email>:<exp> with the program api_key
4. Constant-time compare against the supplied sig

If any step fails, return 401 { code: "auth_required" }.

Shopify HMAC (incoming webhooks)

Shopify signs the raw POST body with HMAC-SHA256, base64-encoded, in the X-Shopify-Hmac-Sha256 header on every webhook.

In Adverfly's webhook handler we verify against the loyalty program's api_key — NOT the Shopify app's shared secret. The merchant configures their loyalty api_key as the webhook signing key when they create the webhook in Shopify Admin.

Why the program key, not the Shopify app secret

• Per-merchant keys → each merchant's webhooks isolated; one leaked key doesn't compromise other shops.
• Simpler onboarding — merchant copy-pastes one value (already visible in the dashboard) instead of needing app-level secrets.
• Same key already powers session tokens + custom earn → single secret to manage.

Verification code (illustrative)

import * as crypto from "crypto";

function verify(rawBody: string, signatureB64: string, programApiKey: string) {
  const expected = crypto
    .createHmac("sha256", programApiKey)
    .update(rawBody, "utf8")
    .digest("base64");
  return crypto.timingSafeEqual(
    Buffer.from(expected),
    Buffer.from(signatureB64)
  );
}

Rotation

Program API key rotation — Coming soon. Today the api_key is fixed for the life of the program. If you suspect compromise:

1. Email support to schedule a rotation
2. Plan a coordinated update: new api_key → reissue Shopify webhook signing key → reissue any Liquid metafield → rotate custom-earn webhook senders

Per-rule webhook tokens (in the Custom Earn tab) can be rotated independently by deleting + recreating the rule — the URL changes, the api_key stays put.

All Loyalty endpoints live under https://api.adverfly.com/loyalty/v1/* (HTTP API v2, regional). CORS is open (Access-Control-Allow-Origin: *) because storefront snippets call from arbitrary merchant domains; all sensitive endpoints are session-token-gated at the Lambda level.

Endpoint matrix

GET /program

Public snapshot of the loyalty program — tiers, earn rules, rewards, branding. Used by storefront snippets to render badges and labels without needing an authenticated session.

Query params:

• workspace_id (required) — the merchant's workspace id.

Response:

{
  "data": {
    "program": { "id": "...", "name": "...", "currency": "EUR", "branding": {...} },
    "tiers": [{ "id": "...", "name": "Silver", "min_spend_threshold": 0, "earn_multiplier": 1, ... }],
    "earn_rules": [{ "id": "...", "trigger_type": "purchase", "earn_type": "percentage", "earn_value": 5, ... }],
    "rewards": [{ "id": "...", "name": "...", "reward_type": "free_product", ... }]
  }
}

Behind the scenes: the snapshot is precomputed and CloudFront-cached (60s stale-while-revalidate). Refreshes when the merchant saves changes in the dashboard.

GET /balance

Live balance + persistent coupon code + unlocked tier gifts for one customer. Session-token gated.

Query params:

• workspace_id (required)
• customer_id (required) — the customer's lowercased email. Must match the token.

Headers:

• Authorization: Bearer <session_token> (see Authentication)

Response:

{
  "data": {
    "balance": 12.50,
    "total_earned": 47.30,
    "total_redeemed": 34.80,
    "tier_name": "Gold",
    "currency": "EUR",
    "coupon_code": "ADV-A1B2-C3D4-E5F6",
    "unlocked_gifts": [
      {
        "tier_id": "...",
        "tier_name": "Gold",
        "reward_id": "...",
        "reward_name": "Ceramic Mug",
        "reward_type": "free_product",
        "shopify_product_id": "...",
        "shopify_discount_id": "gid://shopify/DiscountAutomaticNode/...",
        "reward_image_s3_key": "..."
      }
    ]
  }
}

Errors:

• 400 — missing workspace_id or customer_id
• 401 — missing / invalid / expired session token, OR token email ≠ requested customer_id

POST /balance-lookup

Public "what's my balance?" magic link mail. No auth — anyone can request a mail to any email, but the mail itself only sends if the email is a known customer (no enumeration leakage in the response).

Body:

{ "workspace_id": 123, "email": "customer@example.com" }

Response:

{ "data": { "ok": true } }

(Always returns ok=true to avoid leaking which emails are customers.)

GET / POST /preferences

Public preferences page — same Lambda renders the toggle UI (GET) and processes flips (POST). Auth is a signed magic-link token (one-shot URL in the mail), not a session token.

Query params (GET):

• token (required) — the signed token from the magic link

The Lambda renders an HTML page with subscription toggles. POST submits the form.

GET /unsubscribe

One-click unsubscribe from a signed magic-link URL. Same token pattern as preferences. Sets customers.properties.unsubscribed_at = now().

POST /profile

Customer-initiated profile update — birthday, locale, custom fields. Session-token gated.

Body:

{
  "workspace_id": 123,
  "customer_id": "customer@example.com",
  "properties": {
    "birthday": "1990-05-15",
    "locale": "de",
    "favorite_color": "blue"
  }
}

Validation:

• birthday must be YYYY-MM-DD (server validates).
• Values must be string | number | boolean | null (arrays and nested objects rejected).
• Max 32 keys per request, max 1024 chars per value.
• Server-managed keys are silently rejected with 400 invalid_field: last_birthday_credited_year, tier_unlock_gifts, granted_tier_unlocks, granted_tier_unlocks_shopify, after_earn, monthly_summary, subscribed_at, unsubscribed_at.

Response:

{ "data": { "properties": { ... } } }

(Merged JSONB after the update.)

Rate limits

Stage-level throttling (HTTP API v2 defaultRouteSettings):

• Burst: 1000 req/s
• Sustained: 500 req/s

Per-customer quota is not enforced today — the session-token + email-match check is the practical abuse barrier.

SDK wrapper

All endpoints have a matching method in the Pixel v4 SDK — see Loyalty SDK. Most storefronts should use the SDK rather than calling these endpoints directly; it handles tokens, caching, and error shapes for you.

Two server-to-server endpoints under https://api.adverfly.com/webhooks/loyalty/*. Both rely on the program's api_key for auth — no app-level OAuth, no separate webhook secrets.

Custom Earn — /webhooks/loyalty/earn

Fires a non-purchase earn rule (signup bonus, review reward, birthday, custom). Each rule in the dashboard has its own webhook_token exposed under the Custom Earn tab — that's the id you put in the URL.

Request

curl -X POST "https://api.adverfly.com/webhooks/loyalty/earn?id=<RULE_TOKEN>&email=customer@example.com" \
  -H "X-Adverfly-Key: <PROGRAM_API_KEY>"

GET works too — pasteable into any webhook field that doesn't know about HTTP methods (Klaviyo flows, some Zapier zaps). The Lambda reads query params + headers and ignores the method.

Parameters

Rate limits per rule

Configurable per rule in the dashboard:

• Unbegrenzt — no limit (default)
• Einmalig — once per customer, forever
• Monatlich — once per customer per 30 days
• Halbjährl. — once per customer per 180 days
• Jährlich — once per customer per 365 days

Dedup happens server-side via the webhook_log audit table (queried for the configured window per (rule, idempotency_key)).

Response

{ "ok": true, "amount_credited": 5, "reference_id": "..." }

Status 200 in all expected cases (including rate-limited + duplicate). The body's ok + reason fields tell you what happened — Adverfly treats every fire as best-effort so retry-on-error doesn't accidentally double-credit.

Outcome codes

Shopify Order — /webhooks/loyalty/order

Instant coupon_use detection. Shopify fires orders/create after every order; Adverfly parses discount_codes[] and discount_applications[], matches against the customer's persistent loyalty coupon, and debits the balance.

Request

Shopify POSTs the full Order JSON. Headers include:

X-Shopify-Hmac-Sha256: <base64-hmac-sha256-of-raw-body>
X-Shopify-Shop-Domain: myshop.myshopify.com
X-Shopify-Topic: orders/create

HMAC verification

Adverfly verifies the signature against the loyalty program's api_key — the merchant sets the api_key as the webhook signing key when configuring the webhook in Shopify Admin. See Authentication → Shopify HMAC.

Workspace resolution

The Lambda looks up the workspace by X-Shopify-Shop-Domain against the apis table (integration_key IN ('shopify_loyalty', 'shopify')). If no workspace matches, returns 200 with { skipped: "no_program" } — Shopify thinks delivery succeeded; we just don't process.

Idempotency

INSERT into loyalty_transactions with reference_type='coupon_use' + reference_id=<order_id>. A partial UNIQUE index on (workspace_id, reference_id) WHERE reference_type='coupon_use' makes the insert ON CONFLICT NOOP — Shopify retries, simultaneous webhook + 15-min-sync race, and any other replay all collapse to one debit.

Response

{ "ok": true, "inserted": 1, "skipped_duplicate": 0, "skipped_no_match": 0, "errored": 0 }

Status codes:

• 200 — processed (including skips and partial failures)
• 400 — missing headers or malformed body
• 401 — invalid HMAC signature

200 on internal errors too (logged server-side) so Shopify doesn't aggressively retry transient bugs — the 15-min Shopify Order API sync is the backup safety net.

Backup detection path (no webhook required)

Even if a merchant never configures the Shopify webhook, the existing 15-min Shopify Order API poll (fetch-from-shopify.ts) extracts discountCodes[].code + discountCodes[].amountSet.shopMoney.amount from every order and feeds the same processor (processOrderCouponUses) — same idempotency, same dedup. Webhook is the snappy UX (~seconds); 15-min API sync is the safety net.

Merchant onboarding

For end-to-end Shopify wiring (App install + webhook creation + storefront snippet), see Shopify Setup.

This is the merchant-facing setup guide for hooking a Shopify store into Adverfly Loyalty. After you've finished, your customers will:

• Earn cashback on every purchase (live, no manual claim)
• See their balance + persistent coupon code in widgets / mails
• Unlock free product gifts at tier thresholds, auto-discounted at checkout
• Stack the loyalty code with any other discount

There are four pieces to wire up. Most merchants are live within 30 minutes.

1. Connect Shopify (if not already)

The Adverfly Loyalty app needs read access to orders + customers + products, and write access to discounts.

1. Adverfly dashboard → Integrations → search "Shopify" → Connect
2. Authorize the requested scopes (read_orders, read_customers, read_products, write_discounts)
3. Your shop appears in Integrations → Active

If you already had Shopify connected for analytics, reconnect to pick up the new write_discounts scope — Adverfly mints customer-coupons via this scope.

2. Create the Order webhook

Lets Adverfly debit balances the moment a customer uses their loyalty code (instead of waiting for the 15-min order-API poll).

1. Shopify Admin → Settings → Notifications → scroll to Webhooks → Create webhook
2. Event: Order creation
3. Format: JSON
4. URL: https://api.adverfly.com/webhooks/loyalty/order
5. Webhook signature key: your loyalty program's api_key (Adverfly dashboard → Loyalty → Integrations → Loyalty API Key card)
6. Save

Test it: place a test order, check Loyalty → Members → [the customer] in Adverfly — the debit should show up within seconds.

What if I skip this step? The 15-min Shopify Order API sync still picks up uses — you just trade ~seconds for ~minutes of latency. We recommend the webhook for snappy UX but it's optional.

3. Install the storefront blocks (Liquid)

The Adverfly Liquid snippets render the customer-facing pieces:

• Session Token — required base block, lives in layout/theme.liquid, signs a per-page session token for the SDK
• Product Badge — "Earn +€2.50 credit" pill on product pages
• Balance Badge — inline pill showing the logged-in customer's balance
• Cart-Drawer Tier Gifts — claim button for unlocked free-product gifts inside the cart drawer
• Balance Link — <a href="#adv-balance"> opens the balance lookup modal

Find ready-to-copy versions in the Adverfly dashboard under Loyalty → Integration. Each block is a single <script> + matching DOM element you paste into the right Liquid template.

Session token block (do this first)

This is the prerequisite for all token-gated snippets. Set your loyalty api_key as a Shopify metafield:

1. Settings → Custom data → Shop → Add definition
2. Namespace and key: adverfly.api_key
3. Type: Single line text
4. Save → paste your loyalty api_key as the value

Then paste this block near the top of layout/theme.liquid:

{%- if customer -%}
  {%- assign adv_api_key  = shop.metafields.adverfly.api_key -%}
  {%- assign adv_exp_ts   = "now" | date: "%s" | plus: 3600 -%}
  {%- assign adv_email    = customer.email | downcase -%}
  {%- assign adv_payload  = adv_email | append: ":" | append: adv_exp_ts -%}
  {%- assign adv_sig      = adv_payload | hmac_sha256: adv_api_key -%}
  <script>
    window.adverfly = window.adverfly || [];
    window.adverfly.loyalty_session_token = {{ adv_payload | append: ":" | append: adv_sig | json }};
    window.adverfly.customer_id           = {{ adv_email | json }};
  </script>
{%- endif -%}

The api_key never reaches the browser — only the signed token, which expires in 1 hour.

4. (Optional) Custom Earn webhooks

For non-purchase earn rules (signup, review, birthday, custom):

1. Adverfly dashboard → Loyalty → Custom Earn → create a rule (name + € amount + rate limit)
2. Save → a unique webhook URL appears under the rule
3. Paste that URL into the external system (Klaviyo flow, Yotpo trigger, Zapier webhook step, …)
4. Set the X-Adverfly-Key header (or &key= query param) to your loyalty api_key

See Incoming Webhooks → Custom Earn for full request/response details.

What runs end-to-end

Customer flow                                 Adverfly side
─────────────                                 ─────────────
1. Buys €100 of products                  →   ClickHouse `transactions` row (via Shopify Order sync)
                                              compute-balance: earned += €5 (at 5% cashback)
                                              syncCustomerCoupon: mint Shopify discount with value €5
2. Receives "Du hast €5 erhalten" mail    ←   after-earn cron (every 15 min)
3. Adds gift T-shirt to cart (Gold tier)  ←   Cart-drawer Tier Gift snippet
   Shopify auto-discount fires           ←   discountAutomaticBasicCreate (minted at unlock)
4. Enters loyalty code at checkout        →   persistent code applies, drops cart by €5
5. Places order                           →   Shopify orders/create webhook (instant)
                                              processOrderCouponUses: INSERT loyalty_transactions
                                              compute-balance: redeemed += €5
                                              updateShopifyDiscountCode: value PUT to new balance (€0)
6. Earns cashback on the new order        →   (loop back to step 1)

Troubleshooting

"Customer's code says €0 but their balance is €10" → Shopify-side sync race. The merchant-side balance is the source of truth (getBalance is correct). Wait for the next sync (≤15 min) or trigger a syncCustomerCoupon from the admin UI.

"Tier gift code isn't applying" → Check customers.properties.tier_unlock_gifts[<tier_id>] — should be a Shopify GID, not null. If null, the Shopify mint failed (look at customers.properties.tier_unlock_gifts_error or the tier-unlocks Lambda CloudWatch logs).

"Webhook test fires but balance doesn't update" → HMAC signature mismatch. Confirm the Shopify webhook signing key = your loyalty api_key exactly. The webhook also returns 200 on no_program if X-Shopify-Shop-Domain doesn't match a connected workspace.

"Customer says their gift didn't auto-add to cart" → The cart-drawer snippet only fires on cart:open / cart:refresh events. Some themes don't dispatch these — try a manual page reload after adding to cart. Coming soon: a more robust event hook.

Read next

• Loyalty SDK — Custom storefronts beyond the Liquid snippets
• Public API Reference — REST endpoints
• Data Model — Backend tables

The Loyalty platform stores customer-facing state in a dedicated end-user Aurora cluster (separate from the main platform RDS) to keep merchant-admin traffic and end-shopper traffic isolated.

Cluster split

Tables

loyalty_programs

One row per workspace. Singleton enforced via partial UNIQUE index on (workspace_id) WHERE status != 'archived'.

loyalty_tiers

The tier ladder. Ordered by position within a program.

loyalty_rewards

Today only used as tier-unlock-gift targets — there's no customer-facing reward catalog UI anymore. The merchant defines the available gifts here and assigns them to tiers via loyalty_tiers.unlock_reward_id.

loyalty_earn_rules

How customers earn cashback / bonus credit.

loyalty_coupons ⭐

One persistent Shopify discount code per (workspace, customer).

UNIQUE (workspace_id, customer_id) and UNIQUE (workspace_id, code).

loyalty_transactions ⭐

The debit ledger. Every balance-changing event that's not a pure purchase-cashback (which is computed on the fly) lands here.

Partial UNIQUE index idx_loyalty_tx_coupon_use_dedup ON (workspace_id, reference_id) WHERE reference_type='coupon_use' is the idempotency guarantee for coupon-use detection — Shopify webhook + 15-min sync can both fire, only one row lands.

customers

Unified end-user table — used by loyalty AND future end-user features (surveys, reviews, account portal).

Server-owned property keys (rejected on customer updateProfile): last_birthday_credited_year, tier_unlock_gifts, granted_tier_unlocks, granted_tier_unlocks_shopify, after_earn, monthly_summary, subscribed_at, unsubscribed_at.

tier_unlock_gifts map

{
  "tier-uuid-1": "gid://shopify/DiscountAutomaticNode/12345",
  "tier-uuid-2": null
}

Key existence = local entitlement granted (loyalty_transactions row exists). Value = Shopify automatic-discount GID for free_product rewards. null = grant happened but Shopify mint hasn't (yet) — retried on next dispatchTierUnlocks run.

webhook_log

Audit / dedup for incoming earn-rule webhooks (/webhooks/loyalty/earn).

UNIQUE (domain, source_id, idempotency_key) — duplicate INSERTs fail cleanly, no race.

email_send_log

Idempotency for outbound lifecycle mails (after_earn, monthly_summary). UNIQUE (workspace_id, email_type, dedupe_key).

email_subscriptions (legacy name)

Now consolidated into customers table. The subscription flags (after_earn, monthly_summary, subscribed_at, unsubscribed_at) live in customers.properties.

referrals

Coming soon. Table exists with (referrer_email, referee_email, status, ...) but the UI + Shopify discount issuance is not yet wired.

campaigns

Coming soon. Renamed from loyalty_campaigns. Holds campaign metadata for targeted lifecycle pushes — currently no live consumer.

Migration numbering

Migrations live in backend/src/models/enduser/migrations/. Numbered sequentially independently from the main cluster (0001_* upward). The done_ filename prefix means the migration has been applied to both dev + prod.

Latest:

• 0017_done_earn_rule_limits.sql — per-rule rate limit window
• 0018_done_loyalty_coupons.sql — persistent coupon table + partial UNIQUE coupon_use dedup index

Related

• Overview — how the tables come together
• Public API — what the endpoints read/write
• Authentication — api_key role across the system