# How to Build a Loyalty Program with Replit

- Tool: How to Build with Replit
- Difficulty: Intermediate
- Compatibility: Replit Free
- Last updated: April 2026

## TL;DR

Build a loyalty program in Replit in 1-2 hours. Use Replit Agent to generate an Express + PostgreSQL app with a points economy — customers earn points on purchases, climb bronze/silver/gold/platinum tiers, and redeem rewards. Atomic redemption prevents double-spends. Deploy on Autoscale.

## Before you start

- A Replit account (Free plan is sufficient)
- A list of reward items you want to offer (discounts, products, experiences)
- Tier names and minimum lifetime point thresholds (e.g., Bronze: 0, Silver: 500, Gold: 2000)
- Optional: an existing order system to call the /api/members/earn endpoint from

## Step-by-step guide

### 1. Scaffold the project with Replit Agent

Create a new Repl and use the Agent prompt below to generate the full Express + PostgreSQL loyalty program with Drizzle schema, PostgreSQL trigger, routes, and React frontend in one shot.

```
// Type this into Replit Agent:
// Build a loyalty program platform with Express and PostgreSQL using Drizzle ORM.
// Tables:
// - members: id serial pk, user_id text unique not null, name text, email text,
//   points_balance integer not null default 0, lifetime_points integer not null default 0,
//   tier text default 'bronze' (enum: bronze/silver/gold/platinum), joined_at timestamp default now()
// - point_transactions: id serial pk, member_id integer FK members not null,
//   points integer not null (positive=earned, negative=redeemed), type text not null
//   (enum: earned/redeemed/expired/adjusted/bonus), source text, description text,
//   created_at timestamp default now()
// - rewards: id serial pk, name text not null, description text, points_cost integer not null,
//   category text (enum: discount/product/experience/gift_card), image_url text,
//   stock integer, is_active boolean default true, created_at timestamp default now()
// - redemptions: id serial pk, member_id integer FK members not null, reward_id integer FK rewards not null,
//   points_spent integer not null, status text default 'pending'
//   (enum: pending/fulfilled/cancelled), fulfilled_at timestamp, created_at timestamp default now()
// - tier_rules: id serial pk, tier text unique not null,
//   min_lifetime_points integer not null, multiplier numeric not null default 1.0,
//   perks jsonb
// Create a PostgreSQL trigger on point_transactions INSERT that updates
// members.points_balance (SUM of all points), members.lifetime_points (SUM of positive points),
// and recalculates the tier by checking lifetime_points against tier_rules.
// Routes: GET /api/members/me, GET /api/members/me/transactions,
// POST /api/members/earn (internal API), GET /api/rewards, POST /api/rewards/:id/redeem,
// GET /api/members/me/redemptions, GET /api/tiers, GET /api/admin/members,
// POST /api/admin/members/:id/adjust.
// Use Replit Auth. React frontend with member dashboard, rewards catalog,
// and admin panel. Bind server to 0.0.0.0.
```

> Pro tip: After Agent creates the tables, immediately seed the tier_rules table with your four tiers using Drizzle Studio. The trigger won't work correctly until tier_rules has data.

**Expected result:** A running Express app with all tables, the PostgreSQL trigger, and a React frontend. Drizzle Studio shows the trigger under the database's functions list.

### 2. Add the PostgreSQL trigger for automatic balance updates

The trigger fires after every INSERT into point_transactions and updates members.points_balance, lifetime_points, and tier automatically. Run this SQL in the Replit SQL Editor.

```
-- Run in Replit SQL Editor after tables are created
CREATE OR REPLACE FUNCTION update_member_points()
RETURNS TRIGGER AS $$
DECLARE
  new_balance INTEGER;
  new_lifetime INTEGER;
  new_tier TEXT;
BEGIN
  -- Recalculate balance and lifetime points from all transactions
  SELECT
    COALESCE(SUM(points), 0),
    COALESCE(SUM(CASE WHEN points > 0 THEN points ELSE 0 END), 0)
  INTO new_balance, new_lifetime
  FROM point_transactions
  WHERE member_id = NEW.member_id;

  -- Determine tier based on lifetime points
  SELECT tier INTO new_tier
  FROM tier_rules
  WHERE min_lifetime_points <= new_lifetime
  ORDER BY min_lifetime_points DESC
  LIMIT 1;

  -- Update the member row
  UPDATE members
  SET
    points_balance = new_balance,
    lifetime_points = new_lifetime,
    tier = COALESCE(new_tier, 'bronze')
  WHERE id = NEW.member_id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_member_points
AFTER INSERT ON point_transactions
FOR EACH ROW EXECUTE FUNCTION update_member_points();
```

**Expected result:** After inserting a row into point_transactions, the corresponding member row shows updated points_balance and lifetime_points. The tier field changes automatically when lifetime_points crosses a tier threshold.

### 3. Build the earn and atomic redemption routes

The earn route is called by your order system to award points. The redemption route uses SELECT FOR UPDATE to prevent two concurrent requests from double-spending the same points balance.

```
const express = require('express');
const { db } = require('../db');
const { members, pointTransactions, rewards, redemptions } = require('../../shared/schema');
const { eq, sql } = require('drizzle-orm');

const router = express.Router();

// POST /api/members/earn — called by your order system
// Body: { userId, basePoints, source } e.g. { userId: 'u_123', basePoints: 50, source: 'order-789' }
router.post('/members/earn', async (req, res) => {
  const { userId, basePoints, source } = req.body;

  // Find or create member
  let [member] = await db.select().from(members).where(eq(members.userId, userId));
  if (!member) {
    [member] = await db.insert(members).values({ userId }).returning();
  }

  // Apply tier multiplier
  const multiplierResult = await db.execute(
    sql`SELECT multiplier FROM tier_rules WHERE tier = ${member.tier} LIMIT 1`
  );
  const multiplier = parseFloat(multiplierResult.rows[0]?.multiplier || '1.0');
  const earnedPoints = Math.floor(basePoints * multiplier);

  await db.insert(pointTransactions).values({
    memberId: member.id,
    points: earnedPoints,
    type: 'earned',
    source: source || null,
    description: `Earned ${earnedPoints} points (${multiplier}x multiplier)`,
  });

  res.json({ memberId: member.id, earnedPoints, multiplierApplied: multiplier });
});

// POST /api/rewards/:id/redeem — atomic redemption with row lock
router.post('/rewards/:id/redeem', async (req, res) => {
  const userId = req.user?.id;
  if (!userId) return res.status(401).json({ error: 'Login required' });

  const rewardId = parseInt(req.params.id);

  try {
    await db.execute(sql`BEGIN`);

    // Lock the member row to prevent concurrent redemptions
    const memberResult = await db.execute(
      sql`SELECT * FROM members WHERE user_id = ${userId} FOR UPDATE`
    );
    const member = memberResult.rows[0];
    if (!member) {
      await db.execute(sql`ROLLBACK`);
      return res.status(404).json({ error: 'Member not found' });
    }

    const rewardResult = await db.execute(
      sql`SELECT * FROM rewards WHERE id = ${rewardId} AND is_active = true FOR UPDATE`
    );
    const reward = rewardResult.rows[0];
    if (!reward) {
      await db.execute(sql`ROLLBACK`);
      return res.status(404).json({ error: 'Reward not found or unavailable' });
    }

    if (member.points_balance < reward.points_cost) {
      await db.execute(sql`ROLLBACK`);
      return res.status(400).json({
        error: 'Insufficient points',
        balance: member.points_balance,
        required: reward.points_cost,
      });
    }

    if (reward.stock !== null && reward.stock <= 0) {
      await db.execute(sql`ROLLBACK`);
      return res.status(400).json({ error: 'Reward out of stock' });
    }

    // Deduct points (trigger updates balance)
    await db.execute(
      sql`INSERT INTO point_transactions (member_id, points, type, source, description)
          VALUES (${member.id}, ${-reward.points_cost}, 'redeemed',
                  ${'reward-' + rewardId}, ${'Redeemed: ' + reward.name})`
    );

    // Create redemption record
    const redemptionResult = await db.execute(
      sql`INSERT INTO redemptions (member_id, reward_id, points_spent)
          VALUES (${member.id}, ${rewardId}, ${reward.points_cost}) RETURNING *`
    );

    // Decrement stock if tracked
    if (reward.stock !== null) {
      await db.execute(sql`UPDATE rewards SET stock = stock - 1 WHERE id = ${rewardId}`);
    }

    await db.execute(sql`COMMIT`);
    res.status(201).json(redemptionResult.rows[0]);
  } catch (err) {
    await db.execute(sql`ROLLBACK`);
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
```

**Expected result:** POST /api/members/earn with basePoints: 100 for a Gold member (2x multiplier) inserts 200 points. The trigger updates members.points_balance to reflect the new total. POST /api/rewards/1/redeem fails with 400 if balance is insufficient.

### 4. Build the member dashboard and rewards catalog

The member endpoint returns everything the dashboard needs in one query. The rewards endpoint filters to show only items the current member can afford, making the catalog feel attainable.

```
// GET /api/members/me — full member profile for dashboard
router.get('/members/me', async (req, res) => {
  const userId = req.user?.id;
  if (!userId) return res.status(401).json({ error: 'Login required' });

  const result = await db.execute(sql`
    SELECT
      m.*,
      tr.min_lifetime_points AS current_tier_min,
      tr.multiplier AS current_multiplier,
      tr.perks AS current_perks,
      next_tr.tier AS next_tier,
      next_tr.min_lifetime_points AS next_tier_min,
      (next_tr.min_lifetime_points - m.lifetime_points) AS points_to_next_tier
    FROM members m
    LEFT JOIN tier_rules tr ON tr.tier = m.tier
    LEFT JOIN tier_rules next_tr ON next_tr.min_lifetime_points > m.lifetime_points
      AND next_tr.min_lifetime_points = (
        SELECT MIN(min_lifetime_points) FROM tier_rules
        WHERE min_lifetime_points > m.lifetime_points
      )
    WHERE m.user_id = ${userId}
  `);

  if (!result.rows[0]) {
    // Auto-create member on first login
    const [newMember] = await db.insert(members).values({ userId }).returning();
    return res.json({ ...newMember, next_tier: 'silver', next_tier_min: 500, points_to_next_tier: 500 });
  }

  res.json(result.rows[0]);
});

// GET /api/rewards — rewards catalog
router.get('/rewards', async (req, res) => {
  const rows = await db.execute(sql`
    SELECT r.*,
      CASE WHEN ${req.user?.id} IS NOT NULL THEN
        r.points_cost <= COALESCE((
          SELECT points_balance FROM members WHERE user_id = ${req.user?.id || ''}
        ), 0)
      ELSE false END AS can_afford
    FROM rewards r
    WHERE r.is_active = true
    ORDER BY r.points_cost ASC
  `);
  res.json(rows.rows);
});
```

**Expected result:** GET /api/members/me returns points_balance, tier, next_tier, and points_to_next_tier. GET /api/rewards includes a can_afford boolean on each reward so the frontend can disable the Redeem button for unaffordable items.

### 5. Seed tier rules and deploy on Autoscale

Seed the tier_rules table with your four tiers before deploying. Then deploy on Autoscale — loyalty programs have usage patterns aligned with business hours rather than constant 24/7 traffic.

```
// scripts/seed-tiers.js — run once to set up tier rules
const { db } = require('../server/db');
const { tierRules } = require('./shared/schema');

async function seedTiers() {
  await db.insert(tierRules).values([
    {
      tier: 'bronze',
      minLifetimePoints: 0,
      multiplier: '1.0',
      perks: JSON.stringify(['Earn 1 point per dollar', 'Birthday bonus points']),
    },
    {
      tier: 'silver',
      minLifetimePoints: 500,
      multiplier: '1.5',
      perks: JSON.stringify(['Earn 1.5 points per dollar', 'Free shipping on redemptions', 'Early access to new rewards']),
    },
    {
      tier: 'gold',
      minLifetimePoints: 2000,
      multiplier: '2.0',
      perks: JSON.stringify(['Earn 2 points per dollar', 'Double points on birthday month', 'Priority support']),
    },
    {
      tier: 'platinum',
      minLifetimePoints: 10000,
      multiplier: '3.0',
      perks: JSON.stringify(['Earn 3 points per dollar', 'Exclusive platinum rewards', 'Dedicated account manager']),
    },
  ]).onConflictDoNothing();

  console.log('Tier rules seeded successfully');
  process.exit(0);
}

seedTiers().catch(console.error);
```

> Pro tip: To deploy: click the Deploy button in Replit, choose Autoscale, and set the run command to node server/index.js. Autoscale scales down during quiet hours and up during your business's peak times automatically.

**Expected result:** The tier_rules table shows four rows. Members who earn 500 lifetime points automatically upgrade to Silver tier because the PostgreSQL trigger recalculates the tier on every transaction.

## Complete code example

File: `server/routes/loyalty.js`

```javascript
const express = require('express');
const { db } = require('../db');
const { members, pointTransactions, rewards, redemptions, tierRules } = require('../../shared/schema');
const { eq, sql } = require('drizzle-orm');

const router = express.Router();

// POST /api/members/earn
router.post('/members/earn', async (req, res) => {
  const { userId, basePoints, source, description } = req.body;
  if (!userId || !basePoints) return res.status(400).json({ error: 'userId and basePoints required' });

  let [member] = await db.select().from(members).where(eq(members.userId, userId));
  if (!member) {
    [member] = await db.insert(members).values({ userId }).returning();
  }

  const multiplierResult = await db.execute(
    sql`SELECT multiplier FROM tier_rules WHERE tier = ${member.tier} LIMIT 1`
  );
  const multiplier = parseFloat(multiplierResult.rows[0]?.multiplier || '1.0');
  const earnedPoints = Math.floor(basePoints * multiplier);

  await db.insert(pointTransactions).values({
    memberId: member.id,
    points: earnedPoints,
    type: 'earned',
    source: source || null,
    description: description || `Earned ${earnedPoints} pts`,
  });

  res.json({ memberId: member.id, earnedPoints, multiplier });
});

// POST /api/rewards/:id/redeem — atomic with FOR UPDATE lock
router.post('/rewards/:id/redeem', async (req, res) => {
  const userId = req.user?.id;
  if (!userId) return res.status(401).json({ error: 'Login required' });
  const rewardId = parseInt(req.params.id);

  try {
    await db.execute(sql`BEGIN`);

    const mr = await db.execute(sql`SELECT * FROM members WHERE user_id = ${userId} FOR UPDATE`);
    const rr = await db.execute(sql`SELECT * FROM rewards WHERE id = ${rewardId} AND is_active = true FOR UPDATE`);
    const member = mr.rows[0];
    const reward = rr.rows[0];

    if (!member) { await db.execute(sql`ROLLBACK`); return res.status(404).json({ error: 'Member not found' }); }
    if (!reward) { await db.execute(sql`ROLLBACK`); return res.status(404).json({ error: 'Reward not found' }); }
    if (member.points_balance < reward.points_cost) {
      await db.execute(sql`ROLLBACK`);
      return res.status(400).json({ error: 'Insufficient points', balance: member.points_balance, required: reward.points_cost });
    }
    if (reward.stock !== null && reward.stock <= 0) { await db.execute(sql`ROLLBACK`); return res.status(400).json({ error: 'Out of stock' }); }

    await db.execute(sql`INSERT INTO point_transactions (member_id, points, type, source, description) VALUES (${member.id}, ${-reward.points_cost}, 'redeemed', ${'reward-' + rewardId}, ${'Redeemed: ' + reward.name})`);
    const result = await db.execute(sql`INSERT INTO redemptions (member_id, reward_id, points_spent) VALUES (${member.id}, ${rewardId}, ${reward.points_cost}) RETURNING *`);
    if (reward.stock !== null) await db.execute(sql`UPDATE rewards SET stock = stock - 1 WHERE id = ${rewardId}`);

module.exports = router;
```

## Common mistakes

- **Updating points_balance directly instead of inserting a transaction** — Direct UPDATE on members.points_balance bypasses the audit trail and can cause the balance to drift out of sync with the actual transaction history. Fix: Always insert a row into point_transactions. The PostgreSQL trigger recalculates the balance as a SUM of all transactions, keeping the audit trail and balance permanently in sync.
- **Not using SELECT FOR UPDATE in the redemption route** — Without a row lock, two simultaneous redemption requests for the same member can both read the same positive balance, both pass the balance check, and both deduct points — leaving the member with negative points. Fix: The redemption route wraps the full operation in a transaction with SELECT ... FOR UPDATE on the member row. The second request waits for the first to commit before reading the balance.
- **Forgetting to seed tier_rules before the trigger fires** — The trigger looks up the tier by querying tier_rules. If that table is empty, the COALESCE(new_tier, 'bronze') fallback sets all members to bronze regardless of their lifetime points. Fix: Run scripts/seed-tiers.js immediately after deploying the schema. Verify the tier_rules table has four rows in Drizzle Studio before inserting any point transactions.
- **Calculating tier based on points_balance instead of lifetime_points** — Using points_balance for tier calculation means members get demoted after redeeming rewards. Loyalty programs should reward cumulative spending, not current balance. Fix: The trigger always recalculates tier from lifetime_points (sum of all positive transactions), which only increases. Redemptions reduce points_balance but never affect tier.

## Best practices

- Use the PostgreSQL trigger for balance and tier updates — never update members.points_balance directly from application code.
- Always use SELECT FOR UPDATE in the redemption route to prevent concurrent double-spends.
- Calculate tier from lifetime_points (cumulative earnings), not points_balance (current balance after redemptions).
- Use Drizzle Studio to seed tier_rules before running any point transactions — the trigger depends on these rows.
- Log every point event to point_transactions with a meaningful source field (e.g., 'order-789') for auditability and dispute resolution.
- Deploy on Autoscale — loyalty programs have traffic peaks aligned with business hours and Autoscale handles them cost-effectively.
- Store no external API keys in Secrets unless integrating payment processing. The loyalty engine itself needs no third-party services.

## Frequently asked questions

### How do I connect this to my existing checkout system?

Call POST /api/members/earn from your checkout system after a purchase is confirmed. Send the user's ID, the base points to award (e.g., 1 point per dollar: amount_cents / 100), and the order ID as the source. The loyalty API handles multipliers and tier updates automatically.

### Can members have negative points after a redemption?

No. The redemption route checks points_balance >= points_cost before deducting. The SELECT FOR UPDATE lock ensures this check and the deduction happen atomically — no concurrent request can slip through between the check and the update.

### What happens to tier when a member redeems a lot of points?

Nothing — tier is calculated from lifetime_points (total earned, never decreases), not points_balance (current spendable balance). A Platinum member who redeems 10,000 points stays Platinum because their lifetime_points remain above the threshold.

### What Replit plan do I need?

The Free plan is sufficient for development. For a public-facing loyalty program, deploy on Autoscale (Core plan or higher). Autoscale scales to zero during quiet periods and handles traffic spikes during your business's peak hours.

### How do I handle the admin adjusting a member's points?

The POST /api/admin/members/:id/adjust route inserts a point_transactions row with type = 'adjusted', the adjustment amount (positive or negative), and the admin's name as the source. The trigger updates the balance. Every adjustment is logged in the transaction history for the member to see.

### Can I add a point expiry system?

Yes. Add an expires_at column to point_transactions. Create a Replit Scheduled Deployment that runs daily: it finds transactions where expires_at < now() and inserts a corresponding negative 'expired' transaction to remove those points. The trigger automatically reduces the member's balance.

### Can RapidDev help build a custom loyalty program?

Yes. RapidDev has built 600+ apps including e-commerce platforms and customer engagement tools. They can add custom earning rules, points multiplier campaigns, referral programs, and integrations with your existing checkout system. Book a free consultation at rapidevelopers.com.

### What if two customers redeem the last unit of a reward simultaneously?

The SELECT FOR UPDATE lock on the rewards row prevents this. The first request locks the row, checks stock > 0, and decrements it. The second request waits for the first to commit, then reads stock = 0 and returns a 400 Out of Stock response.

---

Source: https://www.rapidevelopers.com/how-to-build-replit/loyalty-program
© RapidDev — https://www.rapidevelopers.com/how-to-build-replit/loyalty-program