Design seat capacity that cannot be oversold, a FIFO waitlist, unpaid holds that expire, payment confirmation and notices sent once, then implement the flow in TypeScript.

A course offering with 30 seats looks simple until two students submit at the same time, a hold expires unpaid, and a waitlisted student should get the next place. Spreadsheets and an enrolled_count column that any handler can increment are how a seminar is oversold, or how a cancellation never promotes the next person in line.
This guide designs a course enrollment backend: capacity enforced in the database, a waitlist offered in order, unpaid holds that expire, and confirmations sent once. It starts with the state machine and the SQL, then shows the same design by hand on AWS and in TypeScript with Encore.
This is an enrollment and capacity service, not a student information system: grades, attendance and identity live elsewhere. Student records stay in your own AWS or GCP account. Talk to your compliance team about any student-privacy requirements that apply to you.
Treat every enrollment as a record that moves through a small set of statuses, with the allowed transitions enforced in SQL. A loose counter on the offering, updated after a successful insert, is how two concurrent requests both see 29 of 30 seats and both write a confirmation.
A practical set of statuses is:
A few rules are easy to get wrong:
held + confirmed: a pending enrollment occupies a seat, and confirming it moves the count from held to confirmed without incrementing the total.pending is the only way they take capacity, and it uses the same conditional UPDATE as a first-time hold.expired and cancelled free a reserved seat when the row was pending or confirmed. A cancelled waitlist row frees nothing.pending, confirmed or waitlisted) per offering. A new request after expired or cancelled is allowed, which is why a plain unique constraint on (offering_id, student_id) is not enough.expires_at. If the student lets that hold lapse, the next person in line is offered the seat. Do not confirm a waitlisted student without payment, or without an explicit policy that the seat is free.Keep the current status and a short movement history, which is what you read when a student, a cashier and the waitlist disagree.
CREATE TABLE course_offerings (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
capacity INT NOT NULL CHECK (capacity > 0),
held INT NOT NULL DEFAULT 0 CHECK (held >= 0),
confirmed INT NOT NULL DEFAULT 0 CHECK (confirmed >= 0),
CHECK (held + confirmed <= capacity)
);
CREATE TABLE enrollments (
id TEXT PRIMARY KEY,
offering_id TEXT NOT NULL REFERENCES course_offerings (id),
student_id TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN (
'pending', 'confirmed', 'waitlisted', 'expired', 'cancelled'
)),
waitlist_pos BIGINT,
expires_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX one_active_enrollment
ON enrollments (offering_id, student_id)
WHERE status IN ('pending', 'confirmed', 'waitlisted');
CREATE SEQUENCE waitlist_pos_seq;
CREATE TABLE processed_events (
event_id TEXT PRIMARY KEY,
received_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE sent_notices (
enrollment_id TEXT NOT NULL,
kind TEXT NOT NULL,
PRIMARY KEY (enrollment_id, kind)
);
The check on course_offerings is a backstop, and the increment has to be a conditional UPDATE, so a request that arrives when the last seat is gone receives no row and falls through to the waitlist instead of raising a constraint error after a write that has to be undone. Under Read Committed, a plain read of held + confirmed followed by an increment in a second statement can still oversell; both transactions can read 29 and both write 30. Putting the limit in the UPDATE avoids that.
-- Take a seat if one is free. No row means the offering is full.
UPDATE course_offerings
SET held = held + 1
WHERE id = $1
AND held + confirmed < capacity
RETURNING id;
Confirming a paid hold is a move between counters, not a second increment:
UPDATE course_offerings
SET held = held - 1, confirmed = confirmed + 1
WHERE id = $1
AND held > 0
RETURNING id;
UPDATE enrollments
SET status = 'confirmed', expires_at = NULL
WHERE id = $2
AND status = 'pending'
RETURNING id;
Both statements belong in one transaction with the payment event. If the enrollment is no longer pending, the second UPDATE returns no row and the transaction rolls back, so a late webhook does not invent a confirmation and the counters stay aligned.
A pending hold needs an expires_at. The window has to be long enough for the student to pay and short enough that a dropped checkout does not block the waitlist, and you store the value you choose on the offering, for example 24 hours for a tuition deposit or 15 to 30 minutes for a live registration event. The user interface can show a countdown, but the expiry job is what releases the seat.
The job claims expired rows, decrements held, marks them expired, then promotes from the waitlist in position order. Promotion is the same seat-taking UPDATE as a first request: if it returns a row, the waitlisted enrollment becomes pending with a fresh expires_at; if it returns nothing, capacity is gone and the loop stops.
-- Claim one expired hold. SKIP LOCKED lets an overlapping run move on.
UPDATE enrollments
SET status = 'expired'
WHERE id = (
SELECT id FROM enrollments
WHERE status = 'pending' AND expires_at <= now()
ORDER BY expires_at
LIMIT 1
FOR UPDATE SKIP LOCKED
)
RETURNING id, offering_id;
-- Next waitlisted student for that offering.
SELECT id FROM enrollments
WHERE offering_id = $1 AND status = 'waitlisted'
ORDER BY waitlist_pos
LIMIT 1
FOR UPDATE SKIP LOCKED;
FOR UPDATE SKIP LOCKED is what keeps two expiry workers from releasing the same hold, or from offering the same waitlist row twice. waitlist_pos comes from a sequence at insert time, not from counting current rows, so a cancellation in the middle of the line does not renumber everyone else.
A signed payment webhook is what moves pending to confirmed. Providers deliver events at least once, so processed_events records each event ID before any counter change, the same pattern Stripe documents for event destinations. The payment call that created the hold should send an idempotency key built from the enrollment ID, so a retried checkout does not charge the student twice.
The design above does not depend on a particular application framework. A service you build and operate yourself on AWS can implement every part of it.
DATABASE_URL (or a secrets-backed equivalent) that the task uses, and you apply migrations as a step in the deploy so two replicas do not race at startup.pending with an expires_at when a seat is free, or inserts waitlisted with the next waitlist_pos when it is not. The partial unique index is what rejects a second active request from the same student.processed_events, and in the same transaction moves the enrollment to confirmed and adjusts the counters. You terminate TLS, rotate the webhook secret in Secrets Manager and inject it into the task.FOR UPDATE SKIP LOCKED so a missed run and a late run can overlap safely.sent_notices enforces.You also write the Dockerfile, the load balancer and the deployment pipeline. This path is explicit and portable, and the connecting work (IAM, networking, secret injection, the scheduler and the deploy graph) stays yours to keep in sync with the schema as the rules grow.
Encore is a backend framework with built-in infrastructure for TypeScript and Go. The offerings database, the notices topic, the expiry job and the payment webhook secret are declared in the service code. When the app deploys to your own AWS account, Encore provisions RDS, SNS, SQS and Secrets Manager, as the AWS infrastructure docs list. The same code deploys to GCP with Cloud SQL, Pub/Sub and Secret Manager. Encore provisions an IAM role for the service, or a service account on GCP.

A coding agent working in this repository adds a table or a topic in the same change as the handler, and encore run starts Postgres locally so the request, payment and expiry paths can be called before anything is pushed. Cron jobs do not run in local development or preview environments, so the hold sweep is tested by calling its endpoint from the local dashboard.
Run in your terminal to get started locally.
Secret values stay out of the repository, where a coding agent working in the code sees the secret's name but not its value, and each environment gets its own webhook secret. Put the schema from the data model section in enrollment/migrations/1_enrollment.up.sql.
The handlers below take a seat with the conditional UPDATE, confirm a paid hold in the same transaction as the webhook event, and expire due holds before promoting the waitlist. Notices are published after the transaction commits.
// enrollment/enrollment.ts
import { api, APIError, Gateway } from "encore.dev/api";
import { authHandler, Header } from "encore.dev/auth";
import { secret } from "encore.dev/config";
import { CronJob } from "encore.dev/cron";
import { Subscription, Topic } from "encore.dev/pubsub";
import { SQLDatabase } from "encore.dev/storage/sqldb";
import log from "encore.dev/log";
import { getAuthData } from "~encore/auth";
import { createHmac, timingSafeEqual, randomUUID } from "node:crypto";
const db = new SQLDatabase("enrollment", { migrations: "./migrations" });
const webhookSecret = secret("PspWebhookSecret");
const sessionKey = secret("SessionKey");
interface AuthParams {
authorization: Header<"Authorization">;
}
interface AuthData {
userID: string;
}
// Tokens are "<user id>.<unix expiry>.<hex HMAC of id.expiry>", issued by your login flow.
export const auth = authHandler<AuthParams, AuthData>(async (params) => {
const token = (params.authorization ?? "").replace(/^Bearer\s+/i, "");
const parts = token.split(".");
if (parts.length !== 3) throw APIError.unauthenticated("invalid token");
const [id, expStr, sig] = parts;
const exp = Number(expStr);
if (!id || !Number.isFinite(exp) || exp <= Math.floor(Date.now() / 1000)) {
throw APIError.unauthenticated("invalid token");
}
const want = createHmac("sha256", sessionKey()).update(`${id}.${expStr}`).digest("hex");
const a = Buffer.from(sig);
const b = Buffer.from(want);
if (a.length !== b.length || !timingSafeEqual(a, b)) {
throw APIError.unauthenticated("invalid token");
}
return { userID: id };
});
export const gateway = new Gateway({ authHandler: auth });
// 24 hours for a tuition deposit. Shorten this for a live registration event.
const HOLD_TTL_MS = 24 * 60 * 60 * 1000;
export interface EnrollmentNotice {
kind: "pending" | "confirmed" | "waitlisted" | "offered" | "expired";
enrollmentId: string;
offeringId: string;
studentId: string;
}
export const notices = new Topic<EnrollmentNotice>("enrollment-notices", {
deliveryGuarantee: "at-least-once",
});
interface OfferingParams {
offeringId: string;
}
export const requestSeat = api(
{ expose: true, auth: true, method: "POST", path: "/offerings/:offeringId/enrollments" },
async ({ offeringId }: OfferingParams): Promise<{
enrollmentId: string;
status: "pending" | "waitlisted";
expiresAt?: string;
}> => {
const studentId = getAuthData()!.userID;
await using tx = await db.begin();
const id = randomUUID();
const expiresAt = new Date(Date.now() + HOLD_TTL_MS);
const seat = await tx.queryRow`
UPDATE course_offerings
SET held = held + 1
WHERE id = ${offeringId} AND held + confirmed < capacity
RETURNING id`;
if (seat) {
try {
await tx.exec`
INSERT INTO enrollments (id, offering_id, student_id, status, expires_at)
VALUES (${id}, ${offeringId}, ${studentId}, 'pending', ${expiresAt})`;
} catch {
throw APIError.alreadyExists("this student already has an active enrollment");
}
await tx.commit();
await notices.publish({
kind: "pending", enrollmentId: id, offeringId, studentId,
});
return { enrollmentId: id, status: "pending", expiresAt: expiresAt.toISOString() };
}
try {
await tx.exec`
INSERT INTO enrollments (id, offering_id, student_id, status, waitlist_pos)
VALUES (${id}, ${offeringId}, ${studentId}, 'waitlisted', nextval('waitlist_pos_seq'))`;
} catch {
throw APIError.alreadyExists("this student already has an active enrollment");
}
await tx.commit();
await notices.publish({
kind: "waitlisted", enrollmentId: id, offeringId, studentId,
});
return { enrollmentId: id, status: "waitlisted" };
},
);
interface PspEvent {
id: string;
type: string;
enrollmentId: string;
}
export const pspWebhook = api.raw(
{ expose: true, method: "POST", path: "/webhooks/psp" },
async (req, resp) => {
const chunks: Buffer[] = [];
for await (const chunk of req) chunks.push(chunk as Buffer);
const body = Buffer.concat(chunks);
const got = Buffer.from(String(req.headers["x-psp-signature"] ?? ""), "hex");
const want = createHmac("sha256", webhookSecret()).update(body).digest();
if (got.length !== want.length || !timingSafeEqual(got, want)) {
resp.writeHead(401).end("invalid signature");
return;
}
let event: PspEvent;
try {
event = JSON.parse(body.toString("utf8"));
} catch {
resp.writeHead(400).end("invalid json");
return;
}
if (event.type === "payment.succeeded") {
await confirmPayment(event);
}
resp.writeHead(204).end();
},
);
async function confirmPayment(ev: PspEvent) {
await using tx = await db.begin();
const fresh = await tx.queryRow`
INSERT INTO processed_events (event_id) VALUES (${ev.id})
ON CONFLICT DO NOTHING RETURNING event_id`;
if (!fresh) return;
const row = await tx.queryRow<{ offering_id: string; student_id: string }>`
UPDATE enrollments
SET status = 'confirmed', expires_at = NULL
WHERE id = ${ev.enrollmentId} AND status = 'pending'
RETURNING offering_id, student_id`;
if (!row) throw APIError.failedPrecondition("enrollment is not pending");
const moved = await tx.queryRow`
UPDATE course_offerings
SET held = held - 1, confirmed = confirmed + 1
WHERE id = ${row.offering_id} AND held > 0
RETURNING id`;
if (!moved) throw APIError.internal("offering counters are out of sync");
await tx.commit();
await notices.publish({
kind: "confirmed",
enrollmentId: ev.enrollmentId,
offeringId: row.offering_id,
studentId: row.student_id,
});
}
interface ExpiredHold {
id: string;
offering_id: string;
student_id: string;
}
export const expireAndPromote = api({}, async (): Promise<{ expired: number; offered: number }> => {
let expired = 0;
let offered = 0;
while (expired + offered < 200) {
await using tx = await db.begin();
const hold = await tx.queryRow<ExpiredHold>`
UPDATE enrollments
SET status = 'expired'
WHERE id = (
SELECT id FROM enrollments
WHERE status = 'pending' AND expires_at <= now()
ORDER BY expires_at
LIMIT 1
FOR UPDATE SKIP LOCKED
)
RETURNING id, offering_id, student_id`;
if (!hold) break;
await tx.exec`
UPDATE course_offerings
SET held = held - 1
WHERE id = ${hold.offering_id} AND held > 0`;
await tx.commit();
await notices.publish({
kind: "expired",
enrollmentId: hold.id,
offeringId: hold.offering_id,
studentId: hold.student_id,
});
expired++;
if (await offerNext(hold.offering_id)) offered++;
}
return { expired, offered };
});
async function offerNext(offeringId: string): Promise<boolean> {
await using tx = await db.begin();
const seat = await tx.queryRow`
UPDATE course_offerings
SET held = held + 1
WHERE id = ${offeringId} AND held + confirmed < capacity
RETURNING id`;
if (!seat) {
return false;
}
const expiresAt = new Date(Date.now() + HOLD_TTL_MS);
const next = await tx.queryRow<{ id: string; student_id: string }>`
UPDATE enrollments
SET status = 'pending', expires_at = ${expiresAt}
WHERE id = (
SELECT id FROM enrollments
WHERE offering_id = ${offeringId} AND status = 'waitlisted'
ORDER BY waitlist_pos
LIMIT 1
FOR UPDATE SKIP LOCKED
)
RETURNING id, student_id`;
if (!next) {
await tx.exec`
UPDATE course_offerings
SET held = held - 1
WHERE id = ${offeringId} AND held > 0`;
await tx.commit();
return false;
}
await tx.commit();
await notices.publish({
kind: "offered",
enrollmentId: next.id,
offeringId,
studentId: next.student_id,
});
return true;
}
const _ = new CronJob("expire-enrollment-holds", {
title: "Expire unpaid enrollment holds and promote the waitlist",
every: "5m",
endpoint: expireAndPromote,
});
const __ = new Subscription(notices, "email-student", {
handler: async (n) => {
const first = await db.queryRow`
INSERT INTO sent_notices (enrollment_id, kind)
VALUES (${n.enrollmentId}, ${n.kind})
ON CONFLICT DO NOTHING RETURNING enrollment_id`;
if (!first) return;
log.info("enrollment email", {
kind: n.kind, enrollment: n.enrollmentId, student: n.studentId,
});
},
});
The database docs cover migrations and transactions, the Pub/Sub docs cover topics and subscriptions, and the cron job docs cover schedules. encore run starts Postgres locally so you can request a seat, post a signed payment event and call expireAndPromote from the local development dashboard. Cron jobs do not run locally or in preview environments; once deployed, the Encore platform calls the endpoint every five minutes.
Connect your AWS account as described in Connect your cloud account, create an environment and run git push encore. Encore builds the Docker image, so you do not maintain a Dockerfile. When a coding agent later adds a rule, such as a per-student cap across offerings, the change is a query in this service and it reaches production through the same review and deploy as any other pull request.
requestSeat, not inside the counter UPDATE.Keep a held count and a confirmed count on the offering, with a check that their sum never exceeds capacity, and increment them only with a conditional UPDATE that has the same limit in its WHERE clause. Two students who submit at the same time then serialize on the row, and the student who arrives after the last seat gets a waitlist place instead of a hold.
When a pending hold expires or a confirmed student cancels, decrement the matching counter and offer the seat to the waitlisted enrollment with the lowest position. The promotion itself is another conditional UPDATE on the offering, so two overlapping runs cannot hand the same seat to two students.
Long enough for the student to pay, and short enough that a dropped checkout does not block the waitlist. A common window is 24 hours for tuition deposits, or 15 to 30 minutes for a live registration event. Store expires_at on the pending enrollment and enforce it in the expiry job, not only in the user interface.
Declare the database, the notices topic, the expiry job and the payment webhook secret in the service code. When the app deploys to your own AWS or GCP account, Encore provisions the database, the topic and the job, and Encore provisions an IAM role for the service, or a service account on GCP.
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.