Nov 16, 202615 min read

Course Enrollment Backend: Capacity, Waitlists and Confirmations

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.

Illustration for Course Enrollment Backend: Capacity, Waitlists and Confirmations

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.

The Enrollment Backend at a Glance

JobPatternHand-built on AWSWith Encore in your AWS account
Capacityheld + confirmed <= capacity, incremented with a conditional UPDATERDS for PostgreSQLEncore database on RDS
Active enrollmentAt most one pending, confirmed or waitlisted row per student and offeringPartial unique indexThe same index
Payment holdA pending seat with expires_at, confirmed by a signed webhookAPI plus Secrets ManagerEncore secret and a raw webhook
WaitlistFIFO position, promoted when a seat opensSame databaseSame database
Expiry and promotionSweep expired holds, then offer seats in orderEventBridge Scheduler or an ECS scheduled taskCron job, Encore platform Managed
NoticesHold created, confirmed, waitlisted, offered or expired, each sent onceSNS topic and SQS queuePub/Sub topic and subscription

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.

The Enrollment State Machine

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:

StatusMeaningTypical next steps
pendingA seat is held for this student and payment is outstanding.confirmed, expired, cancelled
confirmedPayment has been recorded and the seat is taken.cancelled
waitlistedNo seat was free at request time. The student is in line.pending (an offer), cancelled
expiredThe pending hold passed expires_at without payment.Terminal for this row; the student may enroll again
cancelledThe student, or an admin, released the hold or the seat.Terminal for this row; the student may enroll again

A few rules are easy to get wrong:

  • Capacity is held + confirmed: a pending enrollment occupies a seat, and confirming it moves the count from held to confirmed without incrementing the total.
  • Waitlisted enrollments occupy no seat; promoting one into 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.
  • A student may have at most one active enrollment (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.
  • The waitlist offer becomes a new pending hold with its own 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.

A Data Model That Enforces the Rules

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.

Unpaid Holds and the Waitlist

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.

How Do You Build It by Hand on AWS?

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.

  1. Store offerings and enrollments. RDS for PostgreSQL holds the schema, and the API runs on ECS Fargate in private subnets that can reach the database. You write the task role, the security groups and the 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.
  2. Accept enrollment requests. The request handler runs the conditional UPDATE, inserts 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.
  3. Confirm payment. An HTTPS endpoint verifies a signature over the raw body, writes to 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.
  4. Sweep holds and promote the waitlist. An EventBridge Scheduler rule, or an ECS scheduled task, runs every few minutes, expires due holds and offers open seats in order. The job uses FOR UPDATE SKIP LOCKED so a missed run and a late run can overlap safely.
  5. Notify. Hold, confirmation, waitlist, offer and expiry notices go to SNS, and a worker on an SQS queue sends each one once, keyed by enrollment and kind, which is what 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.

How Do You Build It with Encore?

Encore

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.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account.
Encore provisions the infrastructure your application needs in your own AWS or GCP account.

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.

Set Up the App

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create campus --example=ts/hello-world$ cd campus && mkdir -p enrollment/migrations$ encore secret set --type prod,dev,local PspWebhookSecret

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.

Request, Confirm, Expire and Promote

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.

Deploy to Your AWS Account

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.

Things to Decide Early

  • Hold length. A 24-hour tuition window is too long for a live registration event, and a 15-minute window is too short if payment can take a bank transfer. Pick one expiry per offering type and store it on the row.
  • What cancellation refunds. Releasing a confirmed seat should follow your refund policy, and the payment provider call needs its own idempotency key. The capacity rules above only free the seat.
  • Publishing after commit. The code publishes notices after the transaction commits, so a crash between the two loses one email but never sends one for a rolled-back change. A transactional outbox table removes that gap if every email must go out.
  • Who may enroll. Eligibility, prerequisites and identity checks belong in front of requestSeat, not inside the counter UPDATE.

Frequently asked questions

How do you stop a course from being oversold?

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.

How does a waitlist get the next open seat?

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.

How long should an unpaid enrollment hold last?

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.

How do you build a course enrollment backend with Encore?

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.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders