Get the difference for a column value by all its previous rows data

Please consider these two tables:

1. Vault

   • id BIGSERIAL PRIMARY KEY
   • name VARCHAR(30) NOT NULL

2. Transaction

   • id BIGSERIAL PRIMARY KEY
   • transaction_type VARCHAR(6) NOT NULL — Choice between “credit” or “debit”
   • amount NUMERIC(15,6) NOT NULL
   • vault_id BIGINT REFERENCES Vault (id)

When getting Transaction row/s, I would like to get an extra column for the remaining vault balance after the transaction is done.

Example of Transaction:

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
5       credit                  400         1               500
12      debit                   200         1               300
20      credit                  100         1               400

I can add a column to hold the vault balance and use that for the next transaction. But suppose the user deletes a row, for example row id 5 which has credit amount of 400 is deleted. Then its following transactions vault balance should change accordingly. So for row 12 the vault balance would be -100, and row id 20’s vault balance would be 0.

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
12      debit                   200         1               -100
20      credit                  100         1               0

The way I see it I have two ways:

• Add a column to store the vault balance in a transaction, and if there is a change, all of its following transaction’s vault balance should be updated accordingly.
• Maybe there’s a way to get the difference of credit and debit between all of its previous and the current transaction amount.

But I am hoping there is a much better way? Could you please help me what would be most efficient way to handle such situation?

Update

Link to the Table fiddle.

Something like this, may be?

sum(case transaction_type when 'debit' then -1 else 1 end * amount)
over (partition by vault_id order by id rows unbounded preceding and current row)