' The alarm went off at the very moment that he opened the door ' : you can see that this is used with the past tense.
' He opened the door, and at that very moment the alarm went off ' : this is also in the past tense.
The difference is that, in the second sentence, using 'that', the moment has already been defined as 'when he opened the door', so 'that' refers to the time [moment] when the door was opened.
In the first case, the moment in time concerned is defined after the word 'moment' is used [when he opened the door].
Use of 'the' or 'that' jn this example is not dependent on the tense used.
However, the phrase 'at the moment' would be set in the present, whereas 'at that moment' would always be in the past. In this use of the words, without the word 'very', your American friend is correct.