Short summary: Heian-period Japanese acquired new sounds, including a syllable-final -t, a geminated tt, and other geminated consonants. The つ kana, originally tu, was a natural match to write the new -t, which led to its use in tt, and from that it slowly became the symbol for gemination in general.
Nara-period Old Japanese had no long consonants. This is why the writing system originally had no way of representing them, and was forced to coöpt some other symbol into the role. (They could have created a new symbol—it seems that katakana ン was invented around the 11th century specifically to write the moraic nasal unambiguously—but people generally seem to prefer working with the symbols they already know; consider how English writing failed to create letters to denote all its new vowels, but just make do awkwardly with the five Latin basic vowel letters.)
In the transition to Heian-period Early Middle Japanese, some very significant sound changes occurred (the onbin changes). They added, among other things, long vowels and consonants, and are the source of current verb forms like motte < motite. (For greater clarity, I will use kunrei rōmaji in this answer; つ = tu, ち = ti. Notice these sounds weren't originally pronounced as in Modern Japanese, but likely as in ta.)
Now that Japanese had become a moraic language, they had the problem of how to represent those new sounds. They might just not represent them at all (e.g. motte written as モテ; 9th century). Or they might use a number of different old kana—I don't have a listing of which ones, exactly, but there was overlap between them and also the new moraic nasal (now written ん); e.g. gemination could be written as 牟／ム, the usual symbol for the nasal (as if, in modern kana, you could write motte as もんて).
Around the same period, a second, distinct source of new sounds were Chinese loanwords—the ancestor of current kango (on'yomi)—and a lot of those ended with a syllable-final -t (as in e.g. 罰, modern Japanese batu, modern Cantonese fat, Hakka fat; 達, J. tatu, Cantonese daat/taat; 日, J. niti/jitu, Cantonese jat, Hakka ngit, Hokkien jit/lit/git, and so on). That these sounds were pronounced with a final -t consonant in Middle Japanese is clear from the descriptions left by the Jesuits in the 16th century, and also supported by reconstruction.
How was syllable-final -t represented in writing? As you might have guessed, by the kana symbols for -tu (or, sometimes, -ti). Recall that in historical times there were several kana symbols for each sound (the variants now called hentaigana). There's evidence that some texts consistently use some graphical variants for -tu and others for -t; but most texts just used an underspecified -tu, and you had to guess which sound was meant.
Since the tu symbols could denote a single -t, they were a natural choice for the first t in tt; mo-t-te = も.つ.て. By extension, つ came to be the generic symbol of gemination for other consonants (cf. the case of ム above). All this dates from Heian, but was quite in a state of flux for a long time, and consolidated slowly. There's probably no sharp line for when did all geminates could be said to always and consistently be marked by つ; both sources for this answer only say it "spread gradually" and was settled "relatively late". The syllable-final -t, too, only became -ti and -tu quite recently (historically speaking).