How to guarantee data consistency — that the message will be stored in the database and delivered to the recipient?

To ensure data consistency so that a message and a database record are synchronized (i.e., either both are saved or neither), patterns that provide atomicity between the database and the messaging system are used.

Main approaches:

1. Transactional Outbox — within a single transaction, both the main business data and the message are saved in a special outbox table. Then a separate process reads and sends the messages.

2. Two-Phase Commit (2PC) — a distributed transaction between the database and the message broker, but it is often complex and reduces performance.

3. Idempotent Consumers and re-sending — if atomicity is impossible, messages are sent with the possibility of reprocessing without side effects.

In Go, a transactional outbox can be implemented roughly as follows:

func SaveDataAndOutbox(db *sql.DB, data Data, message Message) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Save data
    _, err = tx.Exec("INSERT INTO data_table (...) VALUES (...)", ...)
    if err != nil {
        return err
    }

    // Save message in outbox
    _, err = tx.Exec("INSERT INTO outbox (event_type, payload) VALUES (?, ?)", message.Type, message.Payload)
    if err != nil {
        return err
    }

    return tx.Commit()
}

Thus, either both data and message are saved, or none are.