You only need postgres

PUB / SUB

No polling, Realtime updates directly from database, no external broker like redis like redis, rabbitMQ or kafka, great of dashboard , notification, live feeds etc.

-- 1. Create a table
CREATE TYPE order_status AS ENUM('open', 'paid');
 
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product TEXT,
    amount NUMERIC,
    status order_status NOT NULL DEFAULT 'open'
);
 
-- 2. Create a trigger function that notifies when a new order is inserted
CREATE OR REPLACE FUNCTION notify_new_order() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('new_order_channel', row_to_json(NEW)::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- 3. Attach it to the 'orders' table
CREATE TRIGGER trigger_new_order
AFTER INSERT ON orders FOR EACH ROW
EXECUTE FUNCTION notify_new_order();

and on node js

await client.connect().then(() => {
  console.log("Connection to DB established.");
});
 
/* NOTIFY/LISTEN */
await client.query("LISTEN new_order_channel");
 
client.on("notification", (msg) => {
  const order = JSON.parse(msg.payload);
  console.log("📦 New Order Received:", order);
  console.log("📨 Sending out the e-mail");
});
 
app.post("/create-order", async (req, res) => {
  const { user_id, product, amount } = req.body;
 
  await client.query(
    `INSERT INTO orders (user_id, product, amount) VALUES ($1, $2, $3)`,
    [user_id, product, amount]
  );
 
  res.status(201);
});

Row level security.

you do not need to add WHERE user_id = ? to your every query.
Enforced at the database level, even if someone bypass you api
super useful for multitenant sass application

 
/* Row-Level Security */
-- 1. Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
-- 3. Create a policy
CREATE POLICY user_only_access ON orders
USING (
    user_id = current_setting('app.current_user_id')::int
);

Advisory lock

RDS and EC2 Fundamentals