[Messages V2] Migrate data layer

[benfurber]

Context

On profiles atm, messages can be sent to spaces.

1. User completes the form.
2. A message document is created on firebase.
3. Firebase function onCreate then retreives the receiver email address and creates an email document (if certain checks are passed).
4. The message doc is updated to say the message has been sent.
5. (We hope) that the email plugin for the firestore sends the email.

Goal

Replace steps 2-5 with supabase and related tools.

New workflow

1. User completes the form.
2. A message item is created on supabase via an api call.
3. On receipt of creation. The current checks in functions/src/messages/messages.ts take place.
4. If three is fine, a webhook to an email service is sent.
5. If three fails, the message is updated with the message fail reason
6. Optional: Have resend update the DB when sent sucessfully

Schema

Current -> new

_id -> id
_created -> created_at
email -> sender_email
name? -> sender_name?
text -> message_body
toUserName -> receiver_user_name
(new) -> receiver_email
isSent -> status

What email service to use

I believe it makes sense to use Resend. Lots of services could do this for us, the advantage of resend is that it's a great option for the auth email service as it has a great intergration with supabase. It'll cost a flat $20 a month for everything we'll end up needing.

[benfurber]

Description updated after chat with @mariojsnunes

[mariojsnunes]

could we store the user email in the profiles table now? (since it's no longer exposed to the frontend)
then we could just have receiver_id and sender_id instead of receiver_user_name, receiver_email, sender_name, sender_email

[benfurber]

@mariojsnunes Yes! I'm still not back into SQL thinking and should be joining. I think the email address should stay in the auth record though. e.g. something like this for the data the email service would need:

supabase.from('messages').select(`
  id,
  body,
  etc.
  receiver (
    auth (
      email
    )
  )
  sender (
    auth (
      email
    )
  )
`)

Though of course that then makes the auth work a dependency of this. Maybe unavoidable.

[benfurber]

Blocked until auth is done.