Award points when an order is paid, hold them until the return window closes, redeem them at checkout without overspending, reverse them on refunds, and keep a ledger finance can reconcile.

A loyalty program reads like a marketing feature and behaves like a small currency. Points are earned from orders, held, spent at checkout, clawed back on refunds and expired, and every one of those movements has to be correct to the point. A customer who sees 1,200 points in their account and gets an error at checkout files a support ticket. Finance cares as well: under IFRS 15 and ASC 606, points that give customers a material right are a separate performance obligation, so part of the revenue from each order is deferred until the points are used or expire, and the outstanding balance has to be reportable.
This guide designs a points ledger with pending and available balances, then builds it in TypeScript: earning from an order event, releasing points after the return window, redeeming at checkout, reversing on refund and expiring inactive balances.
Ledger first, balance second. Every movement is a row in point_entries. The members row holds pending and available for fast reads, and both are updated in the same transaction as the ledger insert. A nightly check that sums the ledger per member and compares it to the balance row catches bugs before customers do.
Pending points. Awarding spendable points at payment time opens an easy abuse path: buy, spend the points on a second order, return the first. Holding points as pending for the length of the return window closes it. The release is a scheduled job that moves eligible entries from pending to available.
Idempotent earning. Order events are delivered at least once by most queues, so the same "order paid" message can arrive twice. A unique constraint on (kind, source_id) turns the second insert into a no-op, using Postgres ON CONFLICT DO NOTHING.
Redemption without races. Two tabs checking out at the same moment must not both spend the same 1,000 points. A single UPDATE ... WHERE available >= n RETURNING lets Postgres serialize the two updates on the row lock, and the second one matches no row once the first has committed. A CHECK constraint on available >= 0 is the backstop.
Expiry policy. Two common models are expiry per earned lot (points expire 12 months after they were earned) and expiry on inactivity (the whole balance expires after 12 months without earning or redeeming). Lot expiry needs FIFO consumption tracking on every redemption. Inactivity expiry needs one timestamp. This guide uses inactivity expiry, and the ledger design leaves room to switch.
The moving parts are a Postgres database (RDS), a queue for order events (an SNS topic from the order service with an SQS queue and dead-letter queue for the loyalty consumer), a scheduler for the hourly release and nightly expiry (EventBridge Scheduler invoking a task or Lambda), a container service for the API and consumer, and IAM policies that let each piece publish, consume or connect. That is a few hundred lines of Terraform before the first point is awarded, and a second copy of it for staging.
Encore is a backend framework with built-in infrastructure. The database, the topic the order service publishes to and the cron jobs are declared in the loyalty service's TypeScript. encore run starts Postgres and an in-memory Pub/Sub locally, and a deploy to your own AWS or GCP account creates RDS and SNS with SQS, or Cloud SQL and Pub/Sub. Encore provisions an IAM role for the service, or a service account on GCP. Cron jobs are managed by the Encore platform and do not run locally or in preview environments, so you call the release endpoint directly when testing.
Run in your terminal to get started locally.
-- loyalty/migrations/1_points.up.sql
CREATE TABLE members (
customer_id TEXT PRIMARY KEY,
pending INT NOT NULL DEFAULT 0 CHECK (pending >= 0),
available INT NOT NULL DEFAULT 0 CHECK (available >= 0),
last_activity TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE point_entries (
id BIGSERIAL PRIMARY KEY,
customer_id TEXT NOT NULL REFERENCES members (customer_id),
kind TEXT NOT NULL CHECK (kind IN ('earn', 'release', 'redeem', 'reverse', 'expire')),
points INT NOT NULL CHECK (points > 0),
source_id TEXT NOT NULL, -- order ID, checkout key or job run
release_at TIMESTAMPTZ, -- set on earn entries
released BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (kind, source_id)
);
CREATE INDEX point_entries_to_release ON point_entries (release_at) WHERE kind = 'earn' AND NOT released;
The database docs cover how Encore applies these migrations.
The order service publishes orderPaid when a payment succeeds. The loyalty service subscribes, converts the amount to points and inserts a pending entry. The unique (kind, source_id) key makes a redelivered message harmless.
// loyalty/earn.ts
import { Topic, Subscription } from "encore.dev/pubsub";
import { SQLDatabase } from "encore.dev/storage/sqldb";
export const db = new SQLDatabase("loyalty", { migrations: "./migrations" });
export interface OrderPaid {
orderId: string;
customerId: string;
amountCents: number;
}
// Published by the orders service when a payment succeeds.
export const orderPaid = new Topic<OrderPaid>("order-paid", {
deliveryGuarantee: "at-least-once",
});
const POINTS_PER_DOLLAR = 1;
const RETURN_WINDOW_DAYS = 30;
const _ = new Subscription(orderPaid, "award-points", {
handler: async ({ orderId, customerId, amountCents }) => {
const points = Math.floor(amountCents / 100) * POINTS_PER_DOLLAR;
if (points <= 0) return;
await using tx = await db.begin();
await tx.exec`
INSERT INTO members (customer_id) VALUES (${customerId})
ON CONFLICT (customer_id) DO NOTHING`;
const entry = await tx.queryRow<{ id: number }>`
INSERT INTO point_entries (customer_id, kind, points, source_id, release_at)
VALUES (${customerId}, 'earn', ${points}, ${orderId},
now() + make_interval(days => ${RETURN_WINDOW_DAYS}))
ON CONFLICT (kind, source_id) DO NOTHING
RETURNING id`;
if (!entry) return; // already awarded for this order
await tx.exec`
UPDATE members SET pending = pending + ${points}, last_activity = now()
WHERE customer_id = ${customerId}`;
await tx.commit();
},
retryPolicy: { minBackoff: "5s", maxBackoff: "5m", maxRetries: 10 },
});
If the handler throws, the message is retried with backoff, and after the last attempt it lands in a dead-letter queue instead of being dropped.
An hourly job moves earn entries whose release_at has passed from pending to available. FOR UPDATE SKIP LOCKED lets the job process a batch without blocking on rows a refund is touching at that moment, and a batch limit keeps each run short.
// loyalty/release.ts
import { api } from "encore.dev/api";
import { CronJob } from "encore.dev/cron";
import { db } from "./earn";
export const releasePending = api({}, async (): Promise<{ released: number }> => {
let released = 0;
for (;;) {
await using tx = await db.begin();
const batch = await tx.queryAll<{ id: number; customer_id: string; points: number }>`
SELECT id, customer_id, points FROM point_entries
WHERE kind = 'earn' AND NOT released AND release_at <= now()
ORDER BY id LIMIT 200
FOR UPDATE SKIP LOCKED`;
if (batch.length === 0) break;
for (const e of batch) {
await tx.exec`UPDATE point_entries SET released = true WHERE id = ${e.id}`;
await tx.exec`
INSERT INTO point_entries (customer_id, kind, points, source_id)
VALUES (${e.customer_id}, 'release', ${e.points}, ${"earn-" + e.id})`;
await tx.exec`
UPDATE members SET pending = pending - ${e.points}, available = available + ${e.points}
WHERE customer_id = ${e.customer_id}`;
}
await tx.commit();
released += batch.length;
}
return { released };
});
const _ = new CronJob("release-pending-points", {
title: "Release points after the return window",
every: "1h",
endpoint: releasePending,
});
Checkout calls redeem with an idempotency key it generates once per attempt. The ledger insert happens first: if the key already exists, the earlier result is returned and nothing is deducted. The conditional update then either deducts or matches nothing, and an insufficient balance rolls back the whole transaction.
// loyalty/redeem.ts
import { api, APIError } from "encore.dev/api";
import { db } from "./earn";
interface RedeemRequest {
customerId: string;
points: number;
idempotencyKey: string;
}
interface RedeemResponse {
redeemed: number;
available: number;
}
export const redeem = api(
{ method: "POST", path: "/loyalty/redeem" },
async ({ customerId, points, idempotencyKey }: RedeemRequest): Promise<RedeemResponse> => {
if (!Number.isInteger(points) || points <= 0) {
throw APIError.invalidArgument("points must be a positive integer");
}
await using tx = await db.begin();
const entry = await tx.queryRow<{ id: number }>`
INSERT INTO point_entries (customer_id, kind, points, source_id)
VALUES (${customerId}, 'redeem', ${points}, ${idempotencyKey})
ON CONFLICT (kind, source_id) DO NOTHING
RETURNING id`;
if (!entry) {
// Retry of a redemption that already succeeded.
const m = await tx.queryRow<{ available: number }>`
SELECT available FROM members WHERE customer_id = ${customerId}`;
return { redeemed: points, available: m?.available ?? 0 };
}
const updated = await tx.queryRow<{ available: number }>`
UPDATE members
SET available = available - ${points}, last_activity = now()
WHERE customer_id = ${customerId} AND available >= ${points}
RETURNING available`;
if (!updated) {
throw APIError.failedPrecondition("not enough available points");
}
await tx.commit();
return { redeemed: points, available: updated.available };
},
);
The endpoint is private, so only other services in the app, such as checkout, can call it. A thrown error leaves the transaction uncommitted, and await using rolls it back, including the ledger insert, so a failed attempt does not burn the key.
A refund event reverses what the order earned. If the earn entry has not been released yet, the points come out of pending. If it has, they come out of available, capped at what the member still has. The shortfall is the policy question: some programs let the balance go negative, others deduct the value from the refund amount. Record the reversal under the order ID with kind = 'reverse', which makes it idempotent in the same way as earning, and keep the cap and shortfall in the entry so support can see what happened.
A nightly cron job selects members whose last_activity is older than 12 months and available > 0, writes an expire entry with a source ID such as expire-2026-11-18-<customer>, and sets available to zero in the same transaction. Send a warning email 30 days before, which is a separate job reading the same column.
The ledger makes the reports simple queries. Outstanding liability is SUM(available) + SUM(pending) across members, multiplied by the redemption value per point. Breakage, the share of points that will never be redeemed, comes from the history of expire entries against earn entries. Both should come from the ledger, never from analytics events, so they reconcile with the balance rows.
release_at per entry, which the schema already does.Keep an append-only ledger of point entries (earn, release, redeem, reverse, expire) and a balance row per member with pending and available columns. Update the balance and insert the ledger entry in the same transaction, so the balance can always be rebuilt from the ledger and checked against it.
Most stores award points as pending when the order is paid and release them once the return window closes. Pending points cannot be redeemed, so a customer cannot earn points on an order, spend them and then return the order.
Redeem with a single conditional update that subtracts points only if the available balance is large enough, and store the checkout's idempotency key on the ledger entry with a unique constraint. A retried request finds the existing entry and returns it instead of deducting again.
Reverse the points that order earned. If they are still pending, remove them from pending. If they were already released, remove them from available and, if the member already spent them, allow a negative balance or recover the value from the refund, depending on your policy.
With Encore, the Postgres database, the order-paid topic and the hourly release job are declared in TypeScript next to the API. encore run starts them locally, and a deploy creates RDS and SNS with SQS on AWS, or Cloud SQL and Pub/Sub on GCP, in your own account.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.