[Feature]: Delete de-bridged user's messages from Discord

[L-e-x-o-n]

Describe the Feature

During recent server issues people start spamming all kinds of messages in #main that get bridged to Discord, an option to delete debridged user's recent messages is wanted.

Expected Behavior

No response

Screenshots

No response

Additional context

There are a few ways to do this, perhaps the simplest would be for the Discord bridge bot/bridge server to subscribe to de-bridged actions in the global_moderation channel and then delete the last X messages from that user.
A more complex solution could offer the option to specify the number of deleted messages when applying the de-bridging action on an user.

In any case, the challenging part is figuring out which messages to delete because Discord message IDs of sent messages aren't saved in DB. The room messages are saved in the teiserver_room_messages table so that one could be extended with a discord_message_id field of the bridged message.

[NortySpock]

I was able to get my personal discord server bot working again last night (and working with my Teiserver), so I will try to tackle this as I find time ... Probably over the course of a month or something. I have no ETA yet.

[NortySpock]

I'm researching the proposal of adding discord_message_id to teiserver_room_messages table.

I'm trying to map the path of of a message out to discord (where we'd pick up the Discord Message ID that we need) and I spotted a potential race condition.

Teiserver.Room.create_room_message() (module) -> Teiserver.Bridge.BridgeServer (genserver) -> Nostrum.Api.Message.create() -> Discord API ()

Teiserver.Room.create_room_message() -> Teiserver.Chat.create_room_message() (module) -> RoomMessage.changeset() (ecto)

The problem is that these appear to be two entirely separate paths, and there's no common Genserver in the middle of these two to act as a FIFO queue to guarantee ordering when we try to pass the discord_message_id back from Discord and tie it to the Teiserver chat message. Thus in theory, if postgres is backed up / not syncronizing fast enough, Discord could return a discord_message_id that is supposed to be applied as an update to a row in the database before the database has inserted the original record of the chat message into teiserver_room_messages table.

@geekingfrog this may inform your architecture question on #936 -- a chatroom may need to be a genserver that assists in managing requests to and from the database table that backs the room as well as sequencing the update to discord_message_id that follows-on from the discord API call.

(please let me know if I am misunderstanding something.)

Diagram:
(Disclaimer:I generated this mermaid diagram by feeding the above hand-written description into an LLM. It looks correct on first glance)

sequenceDiagram
    participant CRM as Teiserver.Room.create_room_message
    participant DB as Postgres (teiserver_room_messages)
    participant Bridge as Teiserver.Bridge.BridgeServer
    participant Discord as Discord API

    CRM->>DB: 1. Start DB Insert (Teiserver.Chat.create_room_message)
    CRM->>Bridge: 2. Trigger Discord Send (Teiserver.Bridge.BridgeServer)
    
    Note over Bridge, Discord: Async Path to External Service
    Bridge->>Discord: 3. Nostrum.Api.create_message()
    Discord-->>Bridge: 4. Returns discord_message_id
    
    Note right of DB: Race Condition: DB is slow/locked
    Bridge->>DB: 5. Attempt UPDATE with discord_message_id
    
    alt Row Not Found
        DB-->>Bridge: Error: Record does not exist
    else Row Found
        DB-->>Bridge: Success: Record updated
    end

    DB-->>CRM: 6. Insert Completed
    

Loading

And the LLM also generated the below sequence diagram to show how a Room Genserver would constrain the order of events.
The LLM generated fix with the GenServer required a bit of fixing, but we got there... Though I may have to play with this diagram more to confirm I understand the concurrency risks...

sequenceDiagram
    participant User as Teiserver.Room.create_room_message
    participant RoomGS as Room GenServer
    participant DB as Postgres (teiserver_room_messages)
    participant Bridge as Teiserver.Bridge.BridgeServer
    participant Discord as Discord API

    User->>RoomGS: cast/call(:new_message, data)
    
    Note over RoomGS, DB: Step 1: Guarantee Local Persistence
    RoomGS->>DB: Teiserver.Chat.create_room_message()

    Note over RoomGS, Bridge: Step 2: Trigger Bridge could occur in parallel with step 1
    RoomGS->>Bridge: send_to_discord(message_struct)
    
    Bridge->>Discord: Nostrum.Api.Message.create()
    Discord-->>Bridge: {:ok, %{id: discord_message_id}}


    DB-->>RoomGS: {:ok, message_struct} (with internal_id)


    Note over Bridge, RoomGS: Step 3: Reply from discord is queued behind initial write to database (Row is basically guaranteed to exist unless DB refused it)
    Bridge->>RoomGS: Update row (internal_id) with discord_message_id
    RoomGS->>DB: Teiserver.Chat.update_room_message()
    DB-->>Bridge: {:ok, updated_message}
    
    Bridge-->>RoomGS: (Optional) Ack/Update state

Loading

[geekingfrog]

seems reasonable. Though I think I would put the discord thing in a fire&forget task that post to discord then update the row accordingly, so that discord being down or slow doesn't impact regular chat room stuff.