Loyalty Program Management System Development

Development of Loyalty Program Management System

Loyalty program is not just "bonus points." It's mechanism that changes buyer behavior: makes them return, increase average check, choose specific channel. Technically, it's points account system, levels, and rewards, embedded in all customer touchpoints.

Architecture: Account, Transactions, Levels

-- User's bonus account
CREATE TABLE loyalty_accounts (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT UNIQUE REFERENCES users(id),
    balance         DECIMAL(12,2) DEFAULT 0,     -- current balance
    lifetime_earned DECIMAL(12,2) DEFAULT 0,     -- total earned (for levels)
    tier_id         BIGINT REFERENCES loyalty_tiers(id),
    expires_at      DATE,                         -- points expiration date
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- All point movements (append-only log)
CREATE TABLE loyalty_transactions (
    id              BIGSERIAL PRIMARY KEY,
    account_id      BIGINT REFERENCES loyalty_accounts(id),
    type            VARCHAR(32) NOT NULL, -- 'earn', 'redeem', 'expire', 'adjust', 'refund'
    amount          DECIMAL(12,2) NOT NULL, -- positive or negative
    balance_after   DECIMAL(12,2) NOT NULL,
    reason          VARCHAR(255),
    source_type     VARCHAR(64),  -- 'order', 'manual', 'birthday', 'referral'
    source_id       BIGINT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- Program levels
CREATE TABLE loyalty_tiers (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(64) NOT NULL,    -- Bronze, Silver, Gold, Platinum
    min_lifetime    DECIMAL(12,2) NOT NULL,  -- accumulated threshold
    earn_multiplier DECIMAL(4,2) DEFAULT 1.0, -- earning multiplier
    redeem_rate     DECIMAL(4,2) DEFAULT 1.0, -- 1 point = X rubles
    perks           JSONB                     -- additional privileges
);

Transactional log is architectural decision. Balance always calculated from history or stored denormalized and recalculated on discrepancy. Allows auditing any movement.

Points Earning

class LoyaltyService {
    public function earnPoints(User $user, float $amount, string $sourceType, int $sourceId): LoyaltyTransaction {
        $account = LoyaltyAccount::firstOrCreate(['user_id' => $user->id]);
        $tier    = $account->tier ?? LoyaltyTier::where('min_lifetime', 0)->orderBy('min_lifetime')->first();

        $points = round($amount * $tier->earn_multiplier * config('loyalty.earn_rate'));
        // earn_rate: for example, 0.05 = 5 points per 100 rubles

        return DB::transaction(function() use ($account, $points, $sourceType, $sourceId) {
            $newBalance = $account->balance + $points;
            $account->update([
                'balance'         => $newBalance,
                'lifetime_earned' => $account->lifetime_earned + $points,
            ]);

            // Recalculate tier
            $newTier = LoyaltyTier::where('min_lifetime', '<=', $account->lifetime_earned)
                ->orderByDesc('min_lifetime')
                ->first();
            if ($newTier && $newTier->id !== $account->tier_id) {
                $account->update(['tier_id' => $newTier->id]);
                event(new TierUpgraded($account->user, $newTier));
            }

            return LoyaltyTransaction::create([
                'account_id'    => $account->id,
                'type'          => 'earn',
                'amount'        => $points,
                'balance_after' => $newBalance,
                'source_type'   => $sourceType,
                'source_id'     => $sourceId,
                'reason'        => 'Purchase reward',
            ]);
        });
    }
}

Points Redemption on Payment

public function redeemPoints(User $user, float $pointsToRedeem, int $orderId): array {
    $account = LoyaltyAccount::where('user_id', $user->id)->lockForUpdate()->first();

    if (!$account || $account->balance < $pointsToRedeem) {
        throw new InsufficientPointsException();
    }

    $tier       = $account->tier;
    $discount   = $pointsToRedeem * $tier->redeem_rate; // points → rubles
    $newBalance = $account->balance - $pointsToRedeem;

    DB::transaction(function() use ($account, $pointsToRedeem, $newBalance, $orderId, $discount) {
        $account->update(['balance' => $newBalance]);
        LoyaltyTransaction::create([
            'account_id'    => $account->id,
            'type'          => 'redeem',
            'amount'        => -$pointsToRedeem,
            'balance_after' => $newBalance,
            'source_type'   => 'order',
            'source_id'     => $orderId,
            'reason'        => 'Point payment',
        ]);
    });

    return ['discount' => $discount, 'points_used' => $pointsToRedeem, 'remaining' => $newBalance];
}

Points Expiration

Points can have lifetime. Scheduler task:

// Daily at 02:00
Schedule::call(function() {
    // Find transactions expiring today
    $expiring = LoyaltyTransaction::where('type', 'earn')
        ->whereDate('expires_at', today())
        ->where('expired', false)
        ->get();

    foreach ($expiring as $transaction) {
        $available = $this->loyaltyService->getAvailableFromTransaction($transaction);
        if ($available > 0) {
            $this->loyaltyService->expirePoints($transaction->account, $available, $transaction->id);
        }
    }
})->dailyAt('02:00');

Configurable Earning Rules: Campaigns

Instead of hardcoded multipliers — campaigns table:

CREATE TABLE loyalty_campaigns (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(255),
    type        VARCHAR(32),    -- 'multiplier', 'fixed', 'category', 'birthday'
    multiplier  DECIMAL(4,2),
    fixed_bonus DECIMAL(10,2),
    conditions  JSONB,          -- {"min_order": 2000, "category_ids": [5, 12]}
    starts_at   TIMESTAMPTZ,
    ends_at     TIMESTAMPTZ,
    active      BOOLEAN DEFAULT TRUE
);

public function getApplicableCampaigns(Order $order): Collection {
    return LoyaltyCampaign::active()
        ->where('starts_at', '<=', now())
        ->where(fn($q) => $q->whereNull('ends_at')->orWhere('ends_at', '>=', now()))
        ->get()
        ->filter(fn($campaign) => $this->campaignApplies($campaign, $order));
}

private function campaignApplies(LoyaltyCampaign $campaign, Order $order): bool {
    $conditions = $campaign->conditions ?? [];

    if (isset($conditions['min_order']) && $order->total < $conditions['min_order']) {
        return false;
    }
    if (isset($conditions['category_ids'])) {
        $hasCategory = $order->items->pluck('product.category_id')
            ->intersect($conditions['category_ids'])
            ->isNotEmpty();
        if (!$hasCategory) return false;
    }
    return true;
}

UI: Balance Widget and Transaction History

const LoyaltyWidget: React.FC = () => {
    const { data: account } = useQuery({
        queryKey: ['loyalty', 'account'],
        queryFn: () => api.get('/loyalty/account'),
    });

    if (!account) return null;

    return (
        <div className="loyalty-widget bg-gradient-to-r from-amber-400 to-orange-500 rounded-xl p-4 text-white">
            <div className="flex justify-between items-center">
                <div>
                    <p className="text-sm opacity-80">Bonus Balance</p>
                    <p className="text-3xl font-bold">{account.balance.toLocaleString()}</p>
                    <p className="text-xs opacity-70">points</p>
                </div>
                <div className="text-right">
                    <p className="text-sm opacity-80">Level</p>
                    <p className="font-semibold">{account.tier.name}</p>
                    <p className="text-xs opacity-70">×{account.tier.earn_multiplier} to points</p>
                </div>
            </div>
        </div>
    );
};

Checkout Integration

Buyer selects how many points to apply:

const maxRedeemable = Math.min(
    loyaltyAccount.balance,
    order.total * (loyaltySettings.max_redeem_percent / 100)
);

Implementation Timeline

Basic system with earning, redemption, and transaction history: 1.5–2 weeks. Adding levels, campaigns with multipliers, points expiration, and frontend widgets: 3–4 weeks. Mobile loyalty card with QR code and POS terminal integration: plus 2–3 weeks.