Store every notification in a server-side inbox

How to do it

Give each row an ID, the user, the type, a small JSON payload, a link path, created_at and a nullable read_at. Index on user and creation time. Every API call filters by the signed-in user, never by a user ID from the request, as check access to every object describes. Page the list with a cursor, as in paginate with cursors.

Example

CREATE TABLE notifications (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type       text NOT NULL,
  payload    jsonb NOT NULL DEFAULT '{}',
  path       text NOT NULL CHECK (path LIKE '/%'),
  created_at timestamptz NOT NULL DEFAULT now(),
  read_at    timestamptz
);
CREATE INDEX notifications_inbox ON notifications (user_id, created_at DESC);
CREATE INDEX notifications_unread ON notifications (user_id) WHERE read_at IS NULL;

app.get('/api/notifications', requireUser, async (req, res) => {
  const page = await db.notifications.page({ userId: req.user.id, before: req.query.cursor, limit: 20 });
  res.json(page);
});

@app.get("/api/notifications")
@login_required
def list_notifications():
    return db.notifications.page(user_id=current_user.id, before=request.args.get("cursor"), limit=20)

How to check

An API test signs in as user A and asks for user B's notification by ID. It gets 404. A second test sends a push and finds the matching inbox row by its ID.