Hello everyone. I need some advice for the best implementation of the following process for a chat application. Three tables are involved: threads, participants, and messages. When a user sends an initial message to another user the following sequence should occur. A thread is created. Two participant rows are created. A message is created. I could do this on the front end with the supabase client in a conditional, but that seems like a hack. Though I think it would be fine. I could set up a trigger, but the I don’t think I could pass all of the data to each insert because to just knowledge triggers pass down rows to each other in sequence. How would I create a message at the end when neither thread nor participant have that value?

you can put all of these in an rpc (cant remember if plpgsql is needed or plain sql is enough), and do something like:

WITH insert_thread AS (
  INSERT INTO threads
  VALUES (...)
  RETURNING thread_id
),
insert_participants AS (
  INSERT INTO participants (..., thread_id)
  VALUES (..., (SELECT thread_id FROM insert_thread))
),
insert_message AS (
  INSERT INTO messages (..., thread_id)
  VALUES (..., (SELECT thread_id FROM insert_thread))
)
...

Oh awesome, thank you for this! I’ll dig in and see if I can make it work!