Real-time matchmaking with Postgres NOTIFY and Socket.IO

allsquads.com matches Warframe players into squads in real time. You post "eidolon /4" in a Discord channel or on the website, others join from either place, and when the squad fills everyone gets a shared chat, a suggested host and a ping. I built it solo starting in 2022, and the community grew past 6,000 registered members. The core idea that made it work is that PostgreSQL is the event bus: every change is a row update, a database trigger turns it into a notification, and one Node.js process broadcasts it to the Discord bot and every browser over Socket.IO.

This post is the architecture, the squad lifecycle, and the race conditions that real players found before I did.

The architecture

There are three programs and one database:

• The API (Node.js, Express and a Socket.IO server) owns all the matchmaking logic and the only connection that writes squads.
• The Discord bot (discord.js) is a Socket.IO client of the API, exactly like a browser.
• The website (React and MUI) is another Socket.IO client.

Discord users ─► Discord bot ─┐
                              ├─ Socket.IO ─► API ─► PostgreSQL
Browsers ─────────────────────┘                ▲          │
                                               └─ NOTIFY ─┘

Neither the bot nor the website is the source of truth. Both send the same events (squadbot/squads/create, squadbot/squads/addmember, and so on) and both receive the same broadcasts. A squad created in Discord appears on the website at the same moment, because they are the same event to the API.

In January 2024 I merged the three into one Node.js process: the bot starts the API in-process and serves the website's build. Three deploys became one, and the bot's socket now connects to localhost.

Postgres as the event bus

Every table that matters has triggers that call pg_notify on insert and update, with the new and old row as the payload. The API holds one dedicated connection that listens on about 50 channels and turns each notification into a Socket.IO broadcast:

// simplified
db.query('LISTEN as_sb_squads_insert');
db.query('LISTEN as_sb_squads_update');
db.query('LISTEN as_sb_squads_messages_insert');

db.on('notification', ({ channel, payload }) => {
  const [row, oldRow] = JSON.parse(payload);
  if (channel === 'as_sb_squads_insert') io.emit('squadbot/squadCreate', row);
  if (channel === 'as_sb_squads_update') {
    io.emit('squadbot/squadUpdate', row);
    if (row.status !== oldRow.status) io.emit(`squadbot/squads/${row.status}`, row);
  }
});

Why this instead of emitting from the code that does the write?

• Nothing can change state without everyone hearing about it. An admin fixing a row by hand in SQL, a scheduled job, a write from the bot: all of them fire the trigger, so all of them reach every client.
• No broker to run. No Redis, no queue. For a single app at this scale, Postgres already had everything needed.

The trade-off is that NOTIFY is fire-and-forget. A client that is disconnected when a notification fires never gets it. The website handles that by ignoring duplicate inserts, dropping squads from its list as soon as their status leaves active, and re-fetching the whole list every two minutes as a safety net.

The squad lifecycle

A squad moves through active (recruiting) → opened (full) → closed, or ends as disbanded, abandoned (everyone left) or invalidated (staff rejected it).

Create

A message can create several squads, one per line. The size comes from a /2, /3 or /4 suffix and defaults to 4. Names have to contain a keyword from an admin-managed allowlist and nothing from a blocklist, which kept the board about Warframe.

Two players posting "eidolon /4" a minute apart almost always want the same squad. Each squad gets a code of its name, size and the day, under a unique index. When a second host posts the same squad, the insert hits the unique violation and the bot asks: "already exists. Would you like to join existing squad or host a new one?" When a squad stops recruiting, its code gets a timestamp appended, which frees the name for the next one.

Join and leave

Joining is one UPDATE that toggles membership, so clicking the same button twice leaves:

-- simplified; members is a jsonb array of user ids
UPDATE as_sb_squads
SET members = CASE
  WHEN members @> to_jsonb($1::text) THEN members - $1
  ELSE members || to_jsonb($1::text)
END
WHERE status = 'active' AND squad_id = $2
RETURNING *;

Because it is a single statement on one row, Postgres serialises concurrent joins on the same squad without any locking code of mine.

Fill, then open

The interesting part happens in the notification handler, not in the join. When an update arrives where the member count equals the squad's spots, the API tries to open it:

UPDATE as_sb_squads
SET status = 'opened', open_timestamp = $2, host_recommendation = $3
WHERE squad_id = $1
  AND status = 'active'
  AND jsonb_array_length(members) = spots;

if (result.rowCount !== 1) return; // someone else already opened it, or it changed

That WHERE is a compare-and-set. Only one caller can move a squad from active to opened, and only that caller runs the follow-up work. Then:

• Members leave every other squad. Players queue for several squads at once. When one fills, they are removed from all their other active squads, and any other squad of theirs that had already opened is disbanded with "A member joined another squad".
• A host is recommended. After each squad, players can rate each other's hosting ping. The API ranks the new members by the ratings their exact squadmates gave them when at least half have rated, otherwise by their overall rating when it has at least five, and shows a bucket such as "100-200 ms". Anyone can then claim host with a button.
• A chat opens everywhere. The bot creates a private thread in each Discord server the members came from, posts the /invite list and the host suggestion, and DMs everyone. Website members get a push notification and the site plays a sound and opens the chat drawer.
• The shared chat spans all of them. A message typed in one Discord thread is stored once, and the bot relays it into every other thread of that squad and to the website. Players from two Discord servers and a browser talk in one conversation. When that happens the thread says "This is a cross-server communication".

Timers that survive a restart

Squads need timers: each member's join expires after their chosen timeout, and an opened squad auto-closes after a set time per activity (15 minutes for a sortie, 40 for eidolon hunts, 20 by default).

setTimeout alone loses every timer when the process restarts, and this process restarted often (more below). So timers live in Postgres too. Scheduling one inserts the SQL to run and its due time into a scheduled_queries table. The insert fires a NOTIFY, the listener sets a setTimeout, and when it fires it runs the query and deletes the row. On boot, the process re-arms every pending row. A restart in the middle of a squad changes nothing.

Linking Discord, the website and the game

Players needed one identity across three places: their Discord account, their website login, and their in-game name. Discord OAuth handles the first two. The in-game name was harder, because Warframe has no public login API.

The solution used the official Warframe forums. The site gives you a six-character code (with no look-alike characters such as 0/O and 1/I/L), and you send a private message with that code as the subject to the site's forum account. The forum emails a notification for each message. The server polls that inbox, finds the code, and reads the sender's in-game name and platform out of the email. Verified names are what every squad action requires.

The race conditions real players found

Matchmaking looks simple until many people press the same button at once. The commit history has the scars:

• "Fix critical bug of multiple squads being opened by autofill" (February 2023). An autofill update was missing its squad_id filter, so filling one squad force-filled every squad the player was in. The fix scoped the update to one squad and told the player "its status may have changed, please try again" when the row had moved on.
• The compare-and-set above (June 2023). Before it, the open logic checked for exactly four members in application code and ran the "remove from other squads" cascade before an unconditional open. Two notifications arriving close together could both pass the check. Moving the condition into the WHERE and gating everything on rowCount === 1 made the database the referee.
• Global caps. The board allowed 75 active squads at once (24 per relic tier), enforced inside the insert itself, so a burst of posts could not race past the limit.

What I would do differently

• Version the schema. The triggers, indexes and constraints that the whole design depends on lived only in the database, not in the repository. Migrations in the repo would have made every one of the fixes above easier to review.
• Fix the cause, not the restart. The process deliberately exited every 24.5 hours and on any database error, and the host restarted it. The durable timers made that safe, but a scheduled restart is a sign of leaks I should have chased. The website's history has several "memory leak fix" commits for the same reason.
• Expect missed notifications from day one. NOTIFY suits this design, but clients must be able to resync. A version number per squad, sent with every event, would let a client detect a gap instead of re-fetching everything on a timer.

LISTEN/NOTIFY is underrated for this kind of app. If you already run Postgres and your traffic fits one database, it gives you real-time fan-out with no new infrastructure, as long as you design for the notifications you will miss. For the queue-shaped version of the same problem, see the SQS producer and consumer tutorial, and for making repeated writes safe, idempotent writes in DynamoDB.

Frequently asked questions

Can PostgreSQL LISTEN/NOTIFY replace a message broker?

For a single app with modest traffic, often yes. A trigger sends a NOTIFY when a row changes and any connected listener receives it, with no extra infrastructure. It is not durable: a listener that is disconnected misses notifications, so clients need a way to resync, and payloads are limited to 8,000 bytes.

How do you stop two players taking the last slot in a squad?

Make the state change a single conditional UPDATE. Opening a squad only succeeds WHERE status is still active AND the member count equals the spots, and only the caller whose update changed exactly one row runs the follow-up work.

How do you keep a Discord bot and a website in sync?

Make neither of them the source of truth. Both are clients of the same API and the same database, and every change is broadcast to both from one place, so a squad joined in Discord appears on the website in the same instant.