Source: Derived from Resources/Assignment_DB_Updates.pdf — an "Apt Interview Assignment" take-home for a backend/full-stack engineering role. The brief asks the candidate to build a system that pushes database changes to connected clients in real time, without client-side polling.
LISTEN/NOTIFY, logical replication/WAL), MySQL (binlog), or MongoDB (Change Streams).INSERT / UPDATE / DELETE and emit a notification payload (e.g. Postgres pg_notify).ws, Socket.IO), or Server-Sent Events (SSE). Understanding when each is appropriate.LISTEN connection cannot be reused in a normal connection pool.WebSocket/EventSource), a CLI, or a script that prints updates live.orders table and its trigger.The student should already be comfortable writing a basic backend server and connecting it to a relational or document database with CRUD operations. They need to understand the request/response model well enough to see why it's a poor fit for live updates (polling is wasteful and laggy), and have at least seen WebSockets or SSE before. Familiarity with SQL, JSON, and asynchronous/event-driven code is important. No prior CDC experience is required — but the student must be willing to learn one notification mechanism (Postgres LISTEN/NOTIFY is the gentlest entry point) and reason about its limits.
Build a backend service that detects every change to an orders table and pushes the new data to all connected clients in real time, without clients polling. Pair it with a simple client (browser, CLI, or script) that displays updates as they happen, plus a README explaining the approach and why it was chosen. The assessment rewards good design thinking around scalability and efficiency as much as a working implementation.
Design and implement a system where clients automatically receive updates whenever data in the database changes. The system must not rely on frequent polling from clients. The core challenge is to think through how updates can be efficiently propagated from the database to connected clients in real time.
Assume a table named orders with the following fields:
| Field | Type | Notes |
|---|---|---|
id | int | primary key |
customer_name | string | |
product_name | string | |
status | string | one of 'pending', 'shipped', 'delivered' |
updated_at | timestamp |
Any INSERT, UPDATE, or DELETE on this table should trigger an update that reaches connected clients.
orders and turn it into a notification.> The stated goal: not just coding, but demonstrating how you think about building real-time systems.
A clean trigger-based approach with Postgres looks like this:
orders fires AFTER INSERT OR UPDATE OR DELETE, building a JSON payload like { "event": "UPDATE", "data": { ...row... } } and calling pg_notify('orders_channel', payload).LISTEN orders_channel and receives each notification.A strong submission will also discuss the limits of this approach (e.g., LISTEN/NOTIFY is at-most-once and has an ~8KB payload cap, listener connections can't be pooled, and for thousands of clients a Redis pub/sub or a managed realtime layer scales better) — exactly the "design thinking" the rubric rewards.
orders rows, and a client that polls it every 2 seconds and re-renders. Then instrument it — log how many requests return no change, and measure the latency between a DB write and the client seeing it. Write a short analysis of the waste and lag. This is a good first exercise because it forces the student to feel the problem the real assignment solves, grounds the later WebSocket version in a concrete comparison, and teaches HTTP basics, timestamps/cursors, and measurement before introducing new transport tech.EventSource. Create the orders trigger, a backend LISTENer, and a one-way stream to the browser that prints each change as it arrives. SSE is simpler than WebSockets (HTTP-based, auto-reconnect built in, one-directional), making it an ideal stepping stone. This teaches database triggers, the pub/sub mental model, async listener connections, and the realization that for a display-only dashboard you don't even need full-duplex sockets.LISTEN/NOTIFY → WebSocket broadcast → live client, but invest in robustness — handle client disconnects, automatic reconnection with a "catch-up" query on reconnect (so a client that was briefly offline isn't permanently stale), and clean modular separation between the DB listener, the broadcaster, and the transport. This is the heart of the exercise and teaches connection lifecycle management, the at-most-once delivery gap of NOTIFY, and how to design for the unreliable network conditions real systems face.orders collection, relays changes through Socket.IO, and a React frontend that renders a live, auto-updating orders table with status badges (pending/shipped/delivered) and optimistic UI. Add a small form to create/update orders so the student can trigger changes from the UI and watch them propagate to a second browser tab. This twist teaches the MERN realtime pattern end-to-end, Change Streams (Mongo's native CDC, which is cleaner than triggers), Socket.IO rooms, and React state management driven by a live event stream — directly mirroring how production dashboards are built.pending too long it drafts a follow-up message, when status flips to shipped it generates a customer notification, and it can answer natural-language questions like "which customers have undelivered orders older than a day?" by querying the DB. Implement it with a tool-calling agent loop (function/tool calling) and guardrails so it only acts on well-defined triggers. This teaches event-driven agent architectures, turning a raw data stream into structured tool inputs, prompt/tool design, and the safety concern of letting an autonomous agent react to live production events — a fast-growing, market-relevant skill set.pg_notify → asyncpg listener → WebSocket broadcast → live JS frontend, distinguishing INSERT/UPDATE/DELETE.LISTEN/NOTIFY to WebSocket clients with JWT auth.LISTEN/NOTIFY events out to WebSocket clients via a small Go service.