Broadcast replay returns messages sent in the same transaction in reverse order

[hsusul]

Bug report

Broadcast replay delivers messages that were sent in the same database transaction in reverse order.

realtime.send / realtime.send_binary insert into realtime.messages without an inserted_at, so it takes the column default, now(). In Postgres now() is the transaction start time, so every message sent in one transaction gets the identical inserted_at. Replay (Realtime.Messages.replay/5) orders only by inserted_at DESC, takes the limit, then Enum.reverse/1s to oldest-first. Postgres returns the tied rows in insertion order, so the reverse flips them: a client replaying 1, 2, 3, 4, 5 receives 5, 4, 3, 2, 1.

Sending several messages in one transaction is an ordinary path — a trigger calling realtime.send / realtime.broadcast_changes on a multi-row statement, or any function that emits more than one event.

To Reproduce

1. On a private topic, in one transaction:

   BEGIN;
   SELECT realtime.send(jsonb_build_object('value', 1), 'event', 'room', true);
   -- ... values 2..5
   COMMIT;

2. All five rows have the same inserted_at.
3. Join realtime:room with config: %{private: true, broadcast: %{replay: %{limit: 10, since: 0}}}.
4. The replayed broadcasts arrive as 5, 4, 3, 2, 1.

Added as a regression test in test/integration/rt_channel/broadcast_test.exs; on main at ee315cae it fails deterministically on both serializers:

code:  assert replayed == [1, 2, 3, 4, 5]
left:  [5, 4, 3, 2, 1]
right: [1, 2, 3, 4, 5]

Expected behavior

Replay delivers messages in the order they were sent, including messages sent in one transaction.

Actual behavior

Same-transaction messages come back exactly reversed. Nothing is dropped and nothing is logged; the order is just wrong.

The existing "replays binary and json messages in insertion order" test does not catch it because message_fixture inserts each row in its own transaction with an explicit Elixir-side timestamp.

Root cause

realtime.messages.inserted_at defaults to now() (priv/repo/tenant_schema/realtime/tables/messages.sql), which does not distinguish rows inserted in the same transaction, and id is a random gen_random_uuid(), so no stored column records their order. Replay's ORDER BY inserted_at DESC therefore has nothing to sort them by.

clock_timestamp() gives the actual time of each insert. I checked it against 200 realtime.send calls in one transaction: 200 distinct inserted_at values, ORDER BY inserted_at matching send order exactly, and replay returning the last 25 in order. Rows go through the partitioned parent, whose default is the one applied.

Impact

Clients that rely on replay to rebuild state (chat history, event logs, collaborative edits) apply same-transaction events backwards after a reconnect, silently.

System information

• Version of realtime: main @ ee315cae
• Reproduced locally with mix test test/integration/rt_channel/broadcast_test.exs, Elixir 1.20.4 / OTP 29, supabase/postgres:15.14.1.167

I have a fix (a tenant migration switching the default to clock_timestamp(), with regenerated dumps and schema export) and a regression test ready, and will open a PR referencing this issue.