test.sql

DROP TABLE IF EXISTS sent;
DROP TABLE IF EXISTS message;
DROP TABLE IF EXISTS recipient;

CREATE TABLE IF NOT EXISTS recipient (
  id serial primary key
);

CREATE TABLE IF NOT EXISTS message (
  id serial primary key
);

CREATE TABLE IF NOT EXISTS sent (
  id serial primary key,
  recipient_id bigint NOT NULL REFERENCES recipient(id) ON UPDATE CASCADE,
  message_id bigint NOT NULL REFERENCES message(id) ON UPDATE CASCADE
);

INSERT INTO recipient VALUES (1), (2), (3);
INSERT INTO message VALUES (1), (2), (3), (4), (5);
INSERT INTO sent(recipient_id, message_id) VALUES (1, 1), (1, 2), (1, 3), (1, 4);

WITH unsent AS (
  SELECT id FROM message
  EXCEPT
  SELECT message_id FROM sent WHERE sent.recipient_id = 1
)
SELECT id FROM unsent ORDER BY RANDOM() LIMIT 1;