Skip to main content

Loyalty Cards & Membership

Overview​

An add-on app: stamp and point loyalty cards, designed as a replacement for LINE's own loyalty system. All of its tables live in the loyalty schema, with no Prisma definitions, applied with psql — the same approach as the appointment and bulletin apps.

Two core ideas explain almost the entire shape of this schema, and both are spelled out explicitly at the top of the migration files:

  1. The ledger is the source of truth — the system never stores a cached balance column; it sums live from unconsumed, unexpired lots in the transaction table. The reason is that a cached balance would go stale the instant any lot expires, because expiry is a function of time, not of a write happening
  2. A card in a wallet must honor the deal it was issued under — card_instance stores a snapshot of program_version and card_size, so a shop can change a program's rules later without affecting cards customers are already collecting

Core Data Structure​

loyalty.program — card configuration​

  • mode (stamp or point), name, unit_label (default "stamp"), and version
  • card_size is the number of stamp slots (1–20); baht_per_point is used in point mode
  • The images cover_path, logo_path, and stamp_icon_path are stored as object paths, not URLs — storing a URL would tie the data to the bucket's current layout and break when that config changes
  • Expiry: expiry_mode (from_first, from_last, or none) and expiry_months
  • Cooldown: cooldown_mode (unlimited, per_hours, or per_day), defaulting to unlimited — different from LINE, because this system's codes are issued by an already-logged-in staff member and expire in 60 seconds anyway
  • status (draft, active, paused, superseded) with a partial unique index (line_oa_id) WHERE status IN ('draft','active','paused'), enforcing that each channel has at most one program that hasn't been superseded
  • Tier-related columns are covered in Loyalty Tiers

loyalty.branch and loyalty.staff​

loyalty.branch stores branch info; loyalty.staff is an invite-only allowlist of staff who can issue stamps.

  • Staff lifecycle: invited → pending → active → revoked
  • display_name is set by the shop owner and never overwritten; the LINE profile of whoever claims the invite is stored separately in line_display_name and line_picture_url, so an approver can spot a mismatch
  • invite_token is partially unique and cleared once claimed, so it can only be used once, paired with invite_expires_at
  • Deny-by-default: only staff with status active can issue stamps

loyalty.account and loyalty.card_instance​

loyalty.account is one customer account per OA. A key design point is that line_user_id has been nullable from day one, to support earning points by phone number before the customer links their LINE account, backed by two paired partial unique indexes:

  • (line_oa_id, line_user_id) WHERE line_user_id IS NOT NULL
  • (line_oa_id, mobile_no) WHERE mobile_no IS NOT NULL AND line_user_id IS NULL

This exact design is what makes it possible to later merge a phone-based account with a LINE account.

loyalty.card_instance represents each physical card; once full, a new one starts, like a punch card. It stores sequence_no (unique per account), slots_filled, card_size, program_version, and status (active, complete, or expired).

loyalty.milestone — rewards on a program​

Rewards are modeled as an independent list, not a "main reward vs. secondary reward" structure like LINE's.

  • required_units, title, image_path, reward_expiry_days, and min_tier_rank
  • Unique on (program_id, program_version, required_units), preventing duplicate rewards at the same level

loyalty.transaction — the ledger​

This table is append-only; only consumed_units is ever updated after insert.

  • kind (earn, burn, expire, adjust), units, mode (stamp or point), and amount_spent
  • expires_date applies only to earn-side lots, paired with consumed_units
  • idempotency_key with a partial unique index (line_oa_id, idempotency_key) prevents double taps or retries after a dropped connection — enforced at the database level, not in code
  • The index (account_id, mode, kind, expires_date, id) WHERE kind='earn' is used both for summing the balance and for FIFO burning
  • Scoping by mode means that when a shop switches from stamp to point mode, lots from the old mode are set aside (not counted, but not deleted) — switching back restores the original balance

Supporting tables​

  • loyalty.reward — an already-earned reward waiting to be redeemed, with a code VARCHAR(16) drawn from an alphabet that excludes I, L, O, U and the digits 0 and 1, to reduce misreads and typos, plus status (unclaimed, claimed, expired), claimed_by_staff_id, and claimed_branch_id
  • loyalty.earn_token — a rotating, single-use, short-lived QR code, with staff_id and branch_id both NOT NULL, because every stamp issuance must always be traceable to a person and a branch
  • loyalty.bonus_rule and loyalty.bonus_grant — the bonus system, where the welcome bonus is just one instance of a general rule. trigger_type supports join, birthday, first_earn, time_window, spend_threshold, and audience; effect is grant_units or multiply; and cadence is once, yearly, or daily. Every cadence is controlled through a single column, bonus_grant.period_key, paired with a unique constraint on (rule_id, account_id, period_key)
  • loyalty.audit_log — records who edited a program, approved staff, or manually adjusted a balance
  • apps/loyalty/migrations/001_create_schema.sql — the CREATE SCHEMA loyalty statement
  • apps/loyalty/migrations/002_create_tables.sql — all core tables, with detailed reasoning in the comments
  • apps/loyalty/migrations/003_staff_invites.sql — converts the staff system to invite-only
  • apps/loyalty/migrations/004_stamp_icon.sql — the stamp_icon_path column
  • apps/loyalty/migrations/005_transaction_mode.sql — scopes the ledger by mode
  • apps/loyalty/migrations/006 through 009 — the tier system, see Loyalty Tiers
  • docs/superpowers/specs/2026-07-28-loyalty-tier-design.md and docs/superpowers/plans/2026-07-28-loyalty-tier.md
  • docs/ROLLBACK-tier.md — a cross-repo rollback checkpoint

Connections to Other Services​

  • client-api-go serves the customer-facing LIFF (view card, scan to earn a stamp, redeem rewards) and the staff-facing LIFF (issue a QR, redeem reward codes)
  • cms-api-go manages program settings, rewards, branches, staff invites and approvals, manual balance adjustments, and reporting
  • Linked to users via the LINE userId from LINE Friends, and toggled on/off via line_oa_app in LINE OA Channel