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)       │
                │  ─ ledger balance (SUM)        │
                │  ─ 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).
• 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.
• Earning is based on platform-confirmed orders only (ClickHouse transactions from the shop integration) — pixel events are forgeable from the browser and never feed cashback.

Balance (the redeemable side)

Every balance-affecting event is a booking on the append-only cashback ledger (ledger_entries, double-entry, same engine as creator commissions). Earns carry the rate/tier/campaign frozen as of the purchase; refunds and expiry are explicit bookings. balance = SUM over the customer's customer_cashback:{email} account — nothing drifts retroactively. Earning only happens for live programs and starts at launch; drafts never accrue.

Points display mode (optional)

A merchant can render balances as points ("1.250 Punkte", "500 PTS", …) instead of currency — configured in Loyalty → Settings (branding.points: rate + label). This is presentation only: the ledger, every booking and the Shopify code value stay in the program currency; widget, mails and the SDK payload (getBalance().points) convert at display time.

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, the Shopify Order API sync (fetch-from-shopify.ts, every 15 minutes per workspace) extracts the discount info, matches it against loyalty_coupons.code, books a redeem on the cashback ledger, then PUTs the new value to Shopify. There is no order webhook.

The ledger's dedupe key coupon_use:{order_id} makes a re-synced order a no-op — no double-debit.

Tier unlock gifts

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

1. Local entitlement — customers.properties.tier_unlock_gifts[tier_id] = null (store-credit gifts additionally book a ledger adjustment).
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",
    "points": {
      "balance": 1250,
      "total_earned": 4730,
      "total_redeemed": 3480,
      "rate": 100,
      "label": "Punkte"
    },
    "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": "..."
      }
    ]
  }
}

points is only present when the merchant enabled the points display mode (Loyalty → Settings). It is pure presentation: the same values pre-converted at rate points per 1 currency unit — the balance/coupon_code fields stay the source of truth (the code applies the currency value at checkout). Render points.balance + " " + points.label when present.

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.

One server-to-server endpoint under https://api.adverfly.com/webhooks/loyalty/*. It relies on the program's api_key for auth — no app-level OAuth, no separate webhook secrets.

There is no order webhook. Everything an order triggers — loyalty coupon uses, referral qualification, raffle tickets — is detected by the Shopify Order API sync that runs every 15 minutes per workspace. Nothing to configure in Shopify Admin.

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

Order detection (no webhook)

The Shopify Order API sync (every 15 minutes per workspace) extracts discountCodes[].code + discountCodes[].amountSet.shopMoney.amount from every order and feeds processOrderCouponUses. The debit lands on the cashback ledger with dedupe key coupon_use:{order_id}, so a re-synced order never double-debits. Expect a coupon use to show up in the member's balance within 15 minutes of checkout, plus the sync's own runtime.

Merchant onboarding

For end-to-end Shopify wiring (App install + 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. Order detection — nothing to configure

Adverfly reads orders through the Shopify Order API sync that runs every 15 minutes per workspace. Coupon uses, referral qualification and raffle tickets are all detected there. There is no order webhook to create in Shopify Admin.

Test it: place a test order, then check Loyalty → Members → [the customer] in Adverfly — the debit shows up after the next sync, at most 15 minutes later.

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 Order API sync (every 15 min)
                                              processOrderCouponUses: book ledger redeem     
                                              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).

ledger_entries ⭐ (cashback ledger)

The book of record for every balance. Same table shape, booking engine and invariants as the creator-commission ledger on the main cluster — one unified double-entry system, one physical book per cluster. Append-only (DB trigger; the single exception is GDPR erasure, which removes whole bookings under a session GUC).

A customer's balance is SUM(amount) over their customer_cashback:{email} account — earns are frozen at booking time (rate × tier × campaign as of the purchase), so merchant config edits are forward-only and late refunds become explicit refund clawback bookings. Bookings are idempotent via the partial UNIQUE index on (workspace_id, metadata->>'dedupe_key') scoped to customer accounts (earn:{order}, coupon_use:{order}, birthday-{year}:{email}, …).

Writers: the 15-min reconcile sweep in loyalty-run-rewards (earns from ClickHouse transactions for LIVE programs only, refund clawbacks, FIFO expiry, zero-sum verify) plus every adjustment path (admin, birthday, tier gifts, webhooks, workflows, referrals) and the coupon-use redeem detection. ledger_enabled_at is set at program creation (and moved to the launch moment when a draft goes live, so pre-launch orders never earn); the former loyalty_transactions table was verified empty in every workspace and dropped in migration 0028.

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. 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

Live — owned by the standalone Referrals app (not loyalty). One row per claimed referee, scoped to domain='referral'; the referrer's durable personal code lives on their customers row (domain='referral', properties.referral_code). Program config in referral_programs (reward provider: loyalty_credit credits both sides via cashback-ledger bookings; coupon mints Shopify discount codes). Loyalty is an optional reward backend — the app works without a loyalty program.

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