How to Build a Loyalty Program with Replit

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

1-- Run in Replit SQL Editor after tables are created
2CREATE OR REPLACE FUNCTION update_member_points()
3RETURNS TRIGGER AS $$
4DECLARE
5  new_balance INTEGER;
6  new_lifetime INTEGER;
7  new_tier TEXT;
8BEGIN
9  -- Recalculate balance and lifetime points from all transactions
10  SELECT
11    COALESCE(SUM(points), 0),
12    COALESCE(SUM(CASE WHEN points > 0 THEN points ELSE 0 END), 0)
13  INTO new_balance, new_lifetime
14  FROM point_transactions
15  WHERE member_id = NEW.member_id;
16
17  -- Determine tier based on lifetime points
18  SELECT tier INTO new_tier
19  FROM tier_rules
20  WHERE min_lifetime_points <= new_lifetime
21  ORDER BY min_lifetime_points DESC
22  LIMIT 1;
23
24  -- Update the member row
25  UPDATE members
26  SET
27    points_balance = new_balance,
28    lifetime_points = new_lifetime,
29    tier = COALESCE(new_tier, 'bronze')
30  WHERE id = NEW.member_id;
31
32  RETURN NEW;
33END;
34$$ LANGUAGE plpgsql;
35
36CREATE TRIGGER trg_update_member_points
37AFTER INSERT ON point_transactions
38FOR EACH ROW EXECUTE FUNCTION update_member_points();

1const express = require('express');
2const { db } = require('../db');
3const { members, pointTransactions, rewards, redemptions } = require('../../shared/schema');
4const { eq, sql } = require('drizzle-orm');
5
6const router = express.Router();
7
8// POST /api/members/earn — called by your order system
9// Body: { userId, basePoints, source } e.g. { userId: 'u_123', basePoints: 50, source: 'order-789' }
10router.post('/members/earn', async (req, res) => {
11  const { userId, basePoints, source } = req.body;
12
13  // Find or create member
14  let [member] = await db.select().from(members).where(eq(members.userId, userId));
15  if (!member) {
16    [member] = await db.insert(members).values({ userId }).returning();
17  }
18
19  // Apply tier multiplier
20  const multiplierResult = await db.execute(
21    sql`SELECT multiplier FROM tier_rules WHERE tier = ${member.tier} LIMIT 1`
22  );
23  const multiplier = parseFloat(multiplierResult.rows[0]?.multiplier || '1.0');
24  const earnedPoints = Math.floor(basePoints * multiplier);
25
26  await db.insert(pointTransactions).values({
27    memberId: member.id,
28    points: earnedPoints,
29    type: 'earned',
30    source: source || null,
31    description: `Earned ${earnedPoints} points (${multiplier}x multiplier)`,
32  });
33
34  res.json({ memberId: member.id, earnedPoints, multiplierApplied: multiplier });
35});
36
37// POST /api/rewards/:id/redeem — atomic redemption with row lock
38router.post('/rewards/:id/redeem', async (req, res) => {
39  const userId = req.user?.id;
40  if (!userId) return res.status(401).json({ error: 'Login required' });
41
42  const rewardId = parseInt(req.params.id);
43
44  try {
45    await db.execute(sql`BEGIN`);
46
47    // Lock the member row to prevent concurrent redemptions
48    const memberResult = await db.execute(
49      sql`SELECT * FROM members WHERE user_id = ${userId} FOR UPDATE`
50    );
51    const member = memberResult.rows[0];
52    if (!member) {
53      await db.execute(sql`ROLLBACK`);
54      return res.status(404).json({ error: 'Member not found' });
55    }
56
57    const rewardResult = await db.execute(
58      sql`SELECT * FROM rewards WHERE id = ${rewardId} AND is_active = true FOR UPDATE`
59    );
60    const reward = rewardResult.rows[0];
61    if (!reward) {
62      await db.execute(sql`ROLLBACK`);
63      return res.status(404).json({ error: 'Reward not found or unavailable' });
64    }
65
66    if (member.points_balance < reward.points_cost) {
67      await db.execute(sql`ROLLBACK`);
68      return res.status(400).json({
69        error: 'Insufficient points',
70        balance: member.points_balance,
71        required: reward.points_cost,
72      });
73    }
74
75    if (reward.stock !== null && reward.stock <= 0) {
76      await db.execute(sql`ROLLBACK`);
77      return res.status(400).json({ error: 'Reward out of stock' });
78    }
79
80    // Deduct points (trigger updates balance)
81    await db.execute(
82      sql`INSERT INTO point_transactions (member_id, points, type, source, description)
83          VALUES (${member.id}, ${-reward.points_cost}, 'redeemed',
84                  ${'reward-' + rewardId}, ${'Redeemed: ' + reward.name})`
85    );
86
87    // Create redemption record
88    const redemptionResult = await db.execute(
89      sql`INSERT INTO redemptions (member_id, reward_id, points_spent)
90          VALUES (${member.id}, ${rewardId}, ${reward.points_cost}) RETURNING *`
91    );
92
93    // Decrement stock if tracked
94    if (reward.stock !== null) {
95      await db.execute(sql`UPDATE rewards SET stock = stock - 1 WHERE id = ${rewardId}`);
96    }
97
98    await db.execute(sql`COMMIT`);
99    res.status(201).json(redemptionResult.rows[0]);
100  } catch (err) {
101    await db.execute(sql`ROLLBACK`);
102    res.status(500).json({ error: err.message });
103  }
104});
105
106module.exports = router;

1// GET /api/members/me — full member profile for dashboard
2router.get('/members/me', async (req, res) => {
3  const userId = req.user?.id;
4  if (!userId) return res.status(401).json({ error: 'Login required' });
5
6  const result = await db.execute(sql`
7    SELECT
8      m.*,
9      tr.min_lifetime_points AS current_tier_min,
10      tr.multiplier AS current_multiplier,
11      tr.perks AS current_perks,
12      next_tr.tier AS next_tier,
13      next_tr.min_lifetime_points AS next_tier_min,
14      (next_tr.min_lifetime_points - m.lifetime_points) AS points_to_next_tier
15    FROM members m
16    LEFT JOIN tier_rules tr ON tr.tier = m.tier
17    LEFT JOIN tier_rules next_tr ON next_tr.min_lifetime_points > m.lifetime_points
18      AND next_tr.min_lifetime_points = (
19        SELECT MIN(min_lifetime_points) FROM tier_rules
20        WHERE min_lifetime_points > m.lifetime_points
21      )
22    WHERE m.user_id = ${userId}
23  `);
24
25  if (!result.rows[0]) {
26    // Auto-create member on first login
27    const [newMember] = await db.insert(members).values({ userId }).returning();
28    return res.json({ ...newMember, next_tier: 'silver', next_tier_min: 500, points_to_next_tier: 500 });
29  }
30
31  res.json(result.rows[0]);
32});
33
34// GET /api/rewards — rewards catalog
35router.get('/rewards', async (req, res) => {
36  const rows = await db.execute(sql`
37    SELECT r.*,
38      CASE WHEN ${req.user?.id} IS NOT NULL THEN
39        r.points_cost <= COALESCE((
40          SELECT points_balance FROM members WHERE user_id = ${req.user?.id || ''}
41        ), 0)
42      ELSE false END AS can_afford
43    FROM rewards r
44    WHERE r.is_active = true
45    ORDER BY r.points_cost ASC
46  `);
47  res.json(rows.rows);
48});

1// scripts/seed-tiers.js — run once to set up tier rules
2const { db } = require('../server/db');
3const { tierRules } = require('./shared/schema');
4
5async function seedTiers() {
6  await db.insert(tierRules).values([
7    {
8      tier: 'bronze',
9      minLifetimePoints: 0,
10      multiplier: '1.0',
11      perks: JSON.stringify(['Earn 1 point per dollar', 'Birthday bonus points']),
12    },
13    {
14      tier: 'silver',
15      minLifetimePoints: 500,
16      multiplier: '1.5',
17      perks: JSON.stringify(['Earn 1.5 points per dollar', 'Free shipping on redemptions', 'Early access to new rewards']),
18    },
19    {
20      tier: 'gold',
21      minLifetimePoints: 2000,
22      multiplier: '2.0',
23      perks: JSON.stringify(['Earn 2 points per dollar', 'Double points on birthday month', 'Priority support']),
24    },
25    {
26      tier: 'platinum',
27      minLifetimePoints: 10000,
28      multiplier: '3.0',
29      perks: JSON.stringify(['Earn 3 points per dollar', 'Exclusive platinum rewards', 'Dedicated account manager']),
30    },
31  ]).onConflictDoNothing();
32
33  console.log('Tier rules seeded successfully');
34  process.exit(0);
35}
36
37seedTiers().catch(console.error);