Orders Realtime (Apt Interview Assignment)

Real-time order updates without client polling.

🔗 Live Demo: https://orders-realtime.onrender.com

Flow: REST writes → Postgres triggers → LISTEN/NOTIFY → Node fan-out → browser SSE.

Why this approach

Choice	Reason
PostgreSQL LISTEN/NOTIFY	True push from the database on commit (no client polling, no outbox poll lag)
Dedicated LISTEN connection	pg pool connections are not suitable for long-lived LISTEN
SSE	Simple one-way browser push with automatic reconnect
NOTIFY-only fan-out	One pipeline for API writes and raw SQL; no double-send from REST
Single Node process	Enough for the demo; scale-out via Redis pub/sub (see below)

Tradeoffs (called out on purpose)

• NOTIFY is lossy if nothing is listening (e.g. app restart). Clients recover with GET /orders snapshot.
• Production durability upgrade: transactional outbox + NOTIFY as a wake-up (or logical decoding / Debezium).
• Multi-instance upgrade: one listener (or each instance listens) publishes to Redis pub/sub; each web process fans out to its local SSE clients.

Stack

• Node.js + Express
• PostgreSQL 16
• Server-Sent Events browser client

Quick start (local)

1. Start Postgres

docker compose up -d

2. Configure env

cp .env.example .env

3. Install & run

npm install
npm start

Open http://localhost:3000.

Local note: Compose maps Postgres to host port 5433 (avoids clashing with a local Postgres on 5432).

Create/update/delete orders in the UI — the event feed and table update live via SSE.

API

• GET /api/orders — snapshot
• POST /api/orders — { customer_name, product_name, status? }
• PATCH /api/orders/:id — partial update
• DELETE /api/orders/:id
• GET /api/events — SSE stream
• GET /api/health — health check

status must be pending | shipped | delivered (enforced in API and DB CHECK).

Deploy (free — Render, not Railway)

Uses Render free web service + free Postgres (no credit card required for the free tier). Railway is intentionally not used.

Or manually:

1. Open Render → New Blueprint and select Stokesy-dev/Atypical-Assignment
2. Apply the included render.yaml (free web + free Postgres)
3. Render sets DATABASE_URL and DATABASE_SSL=true

The app runs sql/init.sql on boot (CREATE IF NOT EXISTS / replace functions & triggers).

Notes: Free web services sleep after ~15 minutes idle (cold start ~30–60s). Free Render Postgres expires after 30 days — fine for an interview demo.

Project layout

src/
  index.js              # Express app, SSE endpoint, schema boot
  db.js                 # Pool
  routes/orders.js      # REST CRUD
  realtime/listener.js  # LISTEN orders_channel
  realtime/sse.js       # In-memory SSE clients
public/                 # Mini UI
sql/init.sql            # Table, updated_at trigger, notify triggers

Assignment mapping

• Insert/update/delete on orders notify clients with the new row data
• Clients do not poll the database
• Backend listens for DB changes and pushes updates
• Simple browser client shows updates in real time
• README documents approach, run steps, and design rationale