Loyalty Cards & Membership

Column-level detail for the entire loyalty schema (program/branch/staff/account/card_instance/milestone/transaction/reward/earn_token/bonus)

Overview

The loyalty schema (excluding the tier feature, covered in Loyalty Tiers) has 12 tables from migrations 001-005. On the configuration side: program (the card), branch, staff, milestone (rewards on the ladder) and bonus_rule. On the customer side: account, card_instance, transaction (the ledger), reward, earn_token (the rotating QR), bonus_grant and audit_log. This schema is applied separately with psql rather than through Prisma.

Two ideas shape the whole design. The ledger is the truth: balances are never stored, they are summed at read time from unconsumed, unexpired transaction lots. A card in a wallet keeps its deal: card_instance snapshots the program version and card size it was issued under, so a merchant editing their card cannot retroactively change one a customer is already filling.

Table `loyalty.program`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	LINE OA this program belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`version`	`INTEGER`	NO	`1`	Program version, bumped on structural edits
`mode`	`VARCHAR(10)`	NO	`'stamp'`	Accrual mode: `stamp` or `point`
`name`	`VARCHAR(120)`	NO	-	Program / card name
`unit_label`	`VARCHAR(30)`	NO	`'ดวง'`	Word for the accrual unit shown on the card
`card_size`	`INTEGER`	YES	-	Slots per card (stamp mode), capped at 20 in validation
`baht_per_point`	`NUMERIC(12,2)`	YES	-	Spend per point (point mode)
`cover_path`	`TEXT`	YES	-	Object path of the card cover image (a path, not a URL)
`logo_path`	`TEXT`	YES	-	Object path of the merchant logo
`theme`	`JSONB`	NO	`'{}'`	Card theme and colours
`terms`	`TEXT`	YES	-	Terms and conditions
`expiry_mode`	`VARCHAR(20)`	NO	`'from_first'`	Expiry rule: `from_first`, `from_last`, `none`
`expiry_months`	`INTEGER`	YES	-	Lifetime in months, used with `expiry_mode`
`cooldown_mode`	`VARCHAR(20)`	NO	`'unlimited'`	Earn frequency limit: `unlimited`, `per_hours`, `per_day`
`cooldown_hours`	`INTEGER`	YES	-	Cooldown interval in hours when `cooldown_mode = 'per_hours'`
`status`	`VARCHAR(20)`	NO	`'draft'`	Status: `draft`, `active`, `paused`, `superseded`
`published_date`	`TIMESTAMPTZ(3)`	YES	-	When the program was published
`created_by`	`INTEGER`	YES	-	Creator user id
`updated_by`	`INTEGER`	YES	-	Last updater user id
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`stamp_icon_path`	`TEXT`	YES	-	Object path of the merchant's stamp icon; falls back to `unit_label` when absent (migration 004)

Table `loyalty.branch`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	LINE OA this branch belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`name`	`VARCHAR(120)`	NO	-	Branch name
`code`	`VARCHAR(40)`	YES	-	Merchant's own branch code
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`deleted_date`	`TIMESTAMPTZ(3)`	YES	-	Soft-delete timestamp

Table `loyalty.staff`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	LINE OA this staff member belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`line_user_id`	`VARCHAR(255)`	YES	-	Staff member's LINE user id — nullable since migration 003, because an `invited` row has no LINE identity yet
`display_name`	`VARCHAR(255)`	YES	-	The name the owner chose, never overwritten by a claim
`picture_url`	`TEXT`	YES	-	Profile picture URL
`branch_id`	`INTEGER`	YES	-	FK to `loyalty.branch(id)` — NULL means every branch
`status`	`VARCHAR(20)`	NO	`'pending'`	Status: `invited`, `pending`, `active`, `revoked`
`approved_by`	`INTEGER`	YES	-	Approver user id
`approved_date`	`TIMESTAMPTZ(3)`	YES	-	Approval timestamp
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp
`line_display_name`	`VARCHAR(255)`	YES	-	LINE profile name of whoever claimed the invite (migration 003)
`line_picture_url`	`TEXT`	YES	-	LINE profile picture of whoever claimed the invite (migration 003)
`invite_token`	`VARCHAR(64)`	YES	-	Single-use invite token, cleared on claim (migration 003)
`invite_expires_at`	`TIMESTAMPTZ(3)`	YES	-	Invite expiry timestamp (migration 003)
`invited_by`	`INTEGER`	YES	-	User who sent the invite (migration 003)

Table `loyalty.account`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	LINE OA this account belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`line_user_id`	`VARCHAR(255)`	YES	-	Customer's LINE user id — nullable, since a phone-only account has no LINE identity yet
`mobile_no`	`VARCHAR(32)`	YES	-	Customer phone number, for accounts created before registration
`status`	`VARCHAR(20)`	NO	`'active'`	Account status
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp

The tier_id, tier_since and tier_evaluated_date columns added in migration 006 are documented in Loyalty Tiers.

Table `loyalty.card_instance`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`account_id`	`INTEGER`	NO	-	FK to `loyalty.account(id)`, ON DELETE CASCADE
`program_id`	`INTEGER`	NO	-	FK to `loyalty.program(id)`
`line_oa_id`	`INTEGER`	NO	-	LINE OA this card belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`program_version`	`INTEGER`	NO	-	Snapshot of the program version this card was issued under
`card_size`	`INTEGER`	NO	-	Snapshot of the slot count at issue time
`sequence_no`	`INTEGER`	NO	-	Card number within the customer's account (1st, 2nd, 3rd ...)
`slots_filled`	`INTEGER`	NO	`0`	Slots stamped so far
`status`	`VARCHAR(20)`	NO	`'active'`	Card status: `active`, `complete`, `expired`
`expires_date`	`TIMESTAMPTZ(3)`	YES	-	Card expiry timestamp
`completed_date`	`TIMESTAMPTZ(3)`	YES	-	When the card was filled
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	When the card was issued

Table `loyalty.milestone`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`program_id`	`INTEGER`	NO	-	FK to `loyalty.program(id)`, ON DELETE CASCADE
`program_version`	`INTEGER`	NO	-	Program version this reward is attached to
`line_oa_id`	`INTEGER`	NO	-	LINE OA this reward belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`required_units`	`INTEGER`	NO	-	Units needed to earn this reward (must be greater than 0)
`title`	`VARCHAR(150)`	NO	-	Reward title
`description`	`TEXT`	YES	-	Reward description
`image_path`	`TEXT`	YES	-	Object path of the reward image
`reward_expiry_days`	`INTEGER`	YES	-	Reward lifetime in days after being earned — NULL follows the card's expiry
`sort_order`	`INTEGER`	NO	`0`	Display order
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp

The min_tier_rank column added in migration 006 is documented in Loyalty Tiers.

Table `loyalty.transaction`

Column	Type	Nullable	Default	Description
`id`	`BIGSERIAL`	NO	auto increment	Primary key
`account_id`	`INTEGER`	NO	-	FK to `loyalty.account(id)`, ON DELETE CASCADE
`line_oa_id`	`INTEGER`	NO	-	LINE OA this entry belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`program_id`	`INTEGER`	YES	-	Program the entry was recorded under
`program_version`	`INTEGER`	YES	-	Program version at the time of the entry
`kind`	`VARCHAR(20)`	NO	-	Entry kind: `earn`, `burn`, `expire`, `adjust`
`units`	`INTEGER`	NO	-	Units in this entry
`amount_spent`	`NUMERIC(12,2)`	YES	-	Amount spent (point mode); NULL for stamps
`branch_id`	`INTEGER`	YES	-	FK to `loyalty.branch(id)` — where it happened
`staff_id`	`INTEGER`	YES	-	FK to `loyalty.staff(id)` — who recorded it
`source`	`VARCHAR(30)`	NO	-	Origin of the entry, e.g. QR scan, manual adjustment, bonus
`source_ref`	`INTEGER`	YES	-	Reference id into the origin named by `source`
`card_instance_id`	`INTEGER`	YES	-	FK to `loyalty.card_instance(id)` — the card affected
`expires_date`	`TIMESTAMPTZ(3)`	YES	-	Lot expiry (earn entries only)
`consumed_units`	`INTEGER`	NO	`0`	Units of this lot already consumed — the only column ever updated
`idempotency_key`	`VARCHAR(80)`	YES	-	Duplicate guard, normally the earn token id
`note`	`TEXT`	YES	-	Free-text note
`created_by`	`INTEGER`	YES	-	User who recorded the entry
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`mode`	`VARCHAR(10)`	NO	`'stamp'`	Entry mode: `stamp` or `point` — keeps the two currencies apart (migration 005)

Table `loyalty.reward`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`account_id`	`INTEGER`	NO	-	FK to `loyalty.account(id)`, ON DELETE CASCADE
`milestone_id`	`INTEGER`	NO	-	FK to `loyalty.milestone(id)` — the reward earned
`card_instance_id`	`INTEGER`	YES	-	FK to `loyalty.card_instance(id)` — the card that produced it
`line_oa_id`	`INTEGER`	NO	-	LINE OA this reward belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`code`	`VARCHAR(16)`	NO	-	Redemption code; the alphabet excludes I, L, O, U and 0/1 so it cannot be misread aloud
`status`	`VARCHAR(20)`	NO	`'unclaimed'`	Status: `unclaimed`, `claimed`, `expired`
`expires_date`	`TIMESTAMPTZ(3)`	YES	-	Reward expiry timestamp
`claimed_date`	`TIMESTAMPTZ(3)`	YES	-	When the reward was claimed
`claimed_by_staff_id`	`INTEGER`	YES	-	FK to `loyalty.staff(id)` — who handed it over
`claimed_branch_id`	`INTEGER`	YES	-	FK to `loyalty.branch(id)` — where it was claimed
`claimed_transaction_id`	`BIGINT`	YES	-	Id of the `burn` entry paired with the claim
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	When the reward was earned

Table `loyalty.earn_token`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	LINE OA this token belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`program_id`	`INTEGER`	NO	-	FK to `loyalty.program(id)`
`token`	`VARCHAR(64)`	NO	-	Token value carried in the QR (unique); single-use and short-lived
`staff_id`	`INTEGER`	NO	-	FK to `loyalty.staff(id)` — the issuing staff member (required)
`branch_id`	`INTEGER`	NO	-	FK to `loyalty.branch(id)` — the issuing branch (required)
`units`	`INTEGER`	NO	-	Units granted on scan (must be greater than 0)
`amount_spent`	`NUMERIC(12,2)`	YES	-	Spend attached to the token (point mode)
`expires_at`	`TIMESTAMPTZ(3)`	NO	-	Token expiry timestamp
`consumed_by_account_id`	`INTEGER`	YES	-	FK to `loyalty.account(id)` — the account that scanned it
`consumed_date`	`TIMESTAMPTZ(3)`	YES	-	When the token was consumed
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	When the token was issued

Table `loyalty.bonus_rule`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`program_id`	`INTEGER`	NO	-	FK to `loyalty.program(id)`, ON DELETE CASCADE
`line_oa_id`	`INTEGER`	NO	-	LINE OA this rule belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`trigger_type`	`VARCHAR(30)`	NO	-	Trigger: `join`, `birthday`, `first_earn`, `time_window`, `spend_threshold`, `audience`
`effect`	`VARCHAR(20)`	NO	-	Effect: `grant_units` or `multiply`
`units`	`INTEGER`	YES	-	Units granted when `effect = 'grant_units'`
`multiplier`	`NUMERIC(4,2)`	YES	-	Multiplier when `effect = 'multiply'`
`conditions`	`JSONB`	NO	`'{}'`	Additional rule conditions
`cadence`	`VARCHAR(20)`	NO	`'once'`	How often it may fire: `once`, `yearly`, `daily`
`valid_from`	`TIMESTAMPTZ(3)`	YES	-	When the rule starts applying
`valid_to`	`TIMESTAMPTZ(3)`	YES	-	When the rule stops applying
`status`	`VARCHAR(20)`	NO	`'active'`	Record status
`priority`	`INTEGER`	NO	`0`	Precedence when several rules match at once
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp
`updated_date`	`TIMESTAMPTZ(3)`	YES	-	Last update timestamp

Table `loyalty.bonus_grant`

Column	Type	Nullable	Default	Description
`id`	`SERIAL`	NO	auto increment	Primary key
`rule_id`	`INTEGER`	NO	-	FK to `loyalty.bonus_rule(id)`, ON DELETE CASCADE
`account_id`	`INTEGER`	NO	-	FK to `loyalty.account(id)`, ON DELETE CASCADE
`transaction_id`	`BIGINT`	YES	-	Ledger entry produced by this grant
`line_oa_id`	`INTEGER`	NO	-	LINE OA this grant belongs to
`period_key`	`VARCHAR(20)`	NO	-	Grant period key: `''` for once-ever, `'2026'` for yearly, `'2026-07-27'` for daily
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	When the bonus was granted

Table `loyalty.audit_log`

Column	Type	Nullable	Default	Description
`id`	`BIGSERIAL`	NO	auto increment	Primary key
`line_oa_id`	`INTEGER`	NO	-	LINE OA this event belongs to
`organization_id`	`INTEGER`	NO	-	Owning organization
`actor_type`	`VARCHAR(20)`	NO	-	Actor kind, e.g. admin / staff / system
`actor_id`	`VARCHAR(255)`	YES	-	Actor identifier
`actor_name`	`VARCHAR(255)`	YES	-	Actor name as of the time of the event
`action`	`VARCHAR(50)`	NO	-	Action taken, e.g. edit program, approve staff, manual adjustment
`target_type`	`VARCHAR(20)`	NO	-	Kind of the affected object
`target_id`	`INTEGER`	YES	-	Id of the affected object
`before`	`JSONB`	YES	-	State before the change
`after`	`JSONB`	YES	-	State after the change
`ip`	`VARCHAR(45)`	YES	-	Actor IP address
`user_agent`	`TEXT`	YES	-	Actor user agent
`created_date`	`TIMESTAMPTZ(3)`	NO	`CURRENT_TIMESTAMP`	Creation timestamp

Notes

There is no balance column. Balances are summed at read time from transaction lots with kind = 'earn' that are unconsumed and unexpired. A cached balance would be silently wrong the moment a lot expired, because expiry is a function of the clock, not of a write
program check constraints: chk_loy_program_mode (mode IN ('stamp','point')), chk_loy_program_expiry, chk_loy_program_cooldown, chk_loy_program_status, chk_loy_program_size (stamp mode needs card_size between 1 and 20, otherwise filling a card would divide by zero), and chk_loy_program_cdhours (cooldown_mode = 'per_hours' requires cooldown_hours > 0)
program uniqueness: idx_loy_program_active on line_oa_id WHERE status IN ('draft','active','paused') — only one live program per OA. superseded rows are exempt so old versions coexist and the cards issued under them can still be read
staff check constraints: chk_loy_staff_status (status IN ('invited','pending','active','revoked')) and chk_loy_staff_invite, which requires an invited row to have no line_user_id but a live invite_token, and any other status to have a line_user_id and no leftover token
Staff lifecycle: invited → (claim) → pending → (approve) → active → (revoke) → revoked. The pending step is deliberate: an invite link can be forwarded before it is used, so holding at pending lets the owner see which LINE account actually claimed it, next to the name they typed, and reject a mismatch
Deny by default: only the exact status active may issue a stamp. Pending, revoked, unknown and empty statuses all fail closed
staff uniqueness: idx_loy_staff_user on (line_oa_id, line_user_id) WHERE line_user_id IS NOT NULL — partial, so multiple unclaimed invites can coexist. And idx_loy_staff_invite on invite_token WHERE invite_token IS NOT NULL, which also enforces single use: claiming clears the token, so it is no longer present to be matched
account uniqueness: idx_loy_account_line on (line_oa_id, line_user_id) WHERE line_user_id IS NOT NULL, and idx_loy_account_phone on (line_oa_id, mobile_no) WHERE mobile_no IS NOT NULL AND line_user_id IS NULL. Once a phone-only account merges into a LINE account the second index stops applying, which is what lets the merge happen
card_instance constraints: chk_loy_card_status, chk_loy_card_filled (slots_filled between 0 and card_size), and unique idx_loy_card_seq on (account_id, sequence_no)
milestone uniqueness: idx_loy_milestone_at on (program_id, program_version, required_units) — two rewards cannot sit on the same threshold, which would make "what do I get at 10 stamps" ambiguous
transaction constraints: chk_loy_txn_kind, chk_loy_txn_consumed (consumed_units between 0 and units), chk_loy_txn_mode (migration 005), and unique idx_loy_txn_idem on (line_oa_id, idempotency_key) WHERE idempotency_key IS NOT NULL — a customer who double-taps, loses signal and retries, or reopens the LIFF cannot be stamped twice, and the guarantee is enforced by the index rather than by app logic
transaction.mode separates the currencies (migration 005): a stamp is not a point, so balance reads filter on mode as well. Old-mode lots are set aside rather than destroyed — they stop counting while another mode is active and come back untouched if the merchant switches back
reward / earn_token / bonus_grant uniqueness: idx_loy_reward_code on (line_oa_id, code); idx_loy_token on token; idx_loy_bonus_once on (rule_id, account_id, period_key), which makes a double grant impossible at the database and covers every cadence with one column
Storage paths, never URLs: cover_path, logo_path, stamp_icon_path and image_path store object paths. Storing a URL bakes in the bucket layout and breaks when STORAGE_PUBLIC_URL_INCLUDE_BUCKET changes; the client resolves them through storagex at read time

Overview​

Table loyalty.program​

Table loyalty.branch​

Table loyalty.staff​

Table loyalty.account​

Table loyalty.card_instance​

Table loyalty.milestone​

Table loyalty.transaction​

Table loyalty.reward​

Table loyalty.earn_token​

Table loyalty.bonus_rule​

Table loyalty.bonus_grant​

Table loyalty.audit_log​

Notes​