So, I can add an axiom that my system S is consistent, and arrive at a new system S' where S' = S + (S is consistent)
Yes, that is fine. If you will allow me to switch to variables that are easier to distinguish from one another, you can have:
B = A + (A is consistent)
Or even
C = A + (A is not consistent)
Neither(!) of those will entail a contradiction (but C will fail to be omega-consistent, which is a stronger form of consistency that arises when you try to reconcile theory and metatheory with one another). Neither B nor C can prove that B/C is itself consistent, although B obviously proves that A is consistent.
The full explanation of C is out of scope here, but in brief, it asserts that a proof of some contradiction, such as 0=1, exists and can be encoded with some Gödel numeral, but it turns out that this numeral does not actually exist in the standard model of arithmetic (it is not any of 0, 1, 2, etc.). Peano arithmetic is not strong enough to disprove the existence of such nonstandard numbers, so no contradiction arises within the system C. Nevertheless, it's intuitively obvious that C is "wrong" in some sense, and that's what omega-consistency is all about.
But there's a big exception: If A is already inconsistent, then it proves everything, including its own consistency and its own inconsistency, and that inconsistency is inherited by B and C. Whenever we talk about any of the incompleteness theorems, we always take the consistency of the theory as a baseline assumption, because there's very little you can usefully say about an inconsistent theory of arithmetic.
On the other hand, we can't get away with something like this:
D = A + (D is consistent)
Because it turns out that, assuming you can find a way to express the self-reference (with clever use of Gödel numbering), the resulting system would run afoul of the second incompleteness theorem and therefore be inconsistent.
Now, returning to your questions:
This still doesn't make S consistent! Or does it? If I understand the rules of system S, can I again see but not prove consistency of S, or is consistency of S still an open question?
If you believe that S' does not prove any contradictions (or equivalently, that S' is consistent), then you necessarily believe that S is consistent, and so a proof is not required. If S were inconsistent, then S' would also be inconsistent, and any "proofs" it provided would be worthless. Therefore, you can't use S' to prove that S is consistent, because either you already believe that S is consistent, or you already doubt that S' is consistent, and so S' accomplishes nothing for you.
How is consistency of a system S related to Universal Turing Machine for first order logic? I mean what is the technical analog of consistency in Turing machines? Is my computer really not provably consistent? And does that mean someday it may give a recognisable contradiction?
The fact that you are unable to prove consistency does not mean that a system is necessarily inconsistent. Mathematicians have carefully considered the consistency of Peano arithmetic and Zermelo-Fraenkel set theory for a very long time, and nobody has ever demonstrated that either system is inconsistent. We might imagine that some incredibly subtle and elaborate contradiction might one day be constructed, but it would not be a simple restatement of e.g. Russell's paradox, because all of the "simple" problems such as Russell's paradox have already been explored and "fixed." If we ever did find such a contradiction, it could likely be constrained by a slight modification of the axioms in order to rule out whatever line of argument leads to a contradiction, so we could likely recover most existing mathematical theorems with little disruption.
Frankly, I would be much more concerned about the possibility that your computer's software is buggy or incorrectly designed, rather than that the entire Curry-Howard correspondence is going to come crashing down at some point in the near future. Software bugs happen all the time; mathematics bugs are (in recent years) much rarer.
