The Lost Order Mystery: Dual-Writes vs. Transactional Outbox

// 1. PostgreSQL Schema: Transactional Outbox
// CREATE TABLE outbox_events (
//     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
//     aggregate_type VARCHAR(64) NOT NULL,
//     aggregate_id VARCHAR(64) NOT NULL,
//     event_type VARCHAR(64) NOT NULL,
//     payload JSONB NOT NULL,
//     created_at TIMESTAMPTZ DEFAULT NOW(),
//     processed_at TIMESTAMPTZ NULL
// );
// CREATE INDEX idx_outbox_unprocessed ON outbox_events (created_at) WHERE processed_at IS NULL;

// 2. Production Go: Atomic Business Write + Event Queueing in 1 Single Transaction
func (s *OrderService) CreateOrder(ctx context.Context, order *Order) error {
    tx, err := s.db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
    if err != nil {
        return err
    }
    defer tx.Rollback() // Safe rollback on any failure

    // Step 1: Write business record
    if _, err := tx.ExecContext(ctx, 
        `INSERT INTO orders (id, user_id, amount, status) VALUES ($1, $2, $3, $4)`,
        order.ID, order.UserID, order.Amount, "PAID"); err != nil {
        return err
    }

    // Step 2: Write outbox event in the EXACT SAME local transaction
    payload, _ := json.Marshal(order)
    if _, err := tx.ExecContext(ctx,
        `INSERT INTO outbox_events (aggregate_type, aggregate_id, event_type, payload) VALUES ($1, $2, $3, $4)`,
        "order", order.ID, "OrderPaid", payload); err != nil {
        return err
    }

    // Both commit atomically. If network or power fails right now, BOTH roll back safely!
    return tx.Commit()
}