Nonce-based Transactional Database

UserTransaction.md

Table UserTransactions

account_id {String}	nonce {Integer}	updates {Integer}	balance {Integer}	previous {Integer}	Description {String}
0000001	0	0	0	0	Initialization
0000001	1	10	10	0	Deposit from ....

Constraint:

• UNIQUE(AccountId, Nonce) prevent double spending
• CHECK(balance >= 0) sanity check
• CHECK(previous + updates = balance) sanity check extra, because we're hyperparanoid

Actions

class UserTransaction extends SomeMySQLLibrary {
  fetchCurrentBalance(){
      return this.query(`SELECT * FROM UserTransactions WHERE AccountId = ${this.accountId()} ORDER BY Nonce DESC`)
  }
          
  /**
  * @params oldTx {UserTransaction}
  * @params update {Integer}
  */
  createNewTx(update){
     const balance = this.balance() + update;
     const nonce = this.nonce() + 1;
     const props = Object.assign({}, this.props, {Balance: balance, Update: update, Nonce: nonce });
     return new UserTransaction(props);
  }
                                                
  /**
  * @params targetUserTx {UserTransaction}
  * @params amount {Integer}
  */                                                
  transferWithJS(targetUserBalance, amount){
      const myNewBalance = this.createNewTx(myCurrentBalance, amount * -1);
      const targetNewBalance = targetUserTx.createNewTx(targetCurrentBalance, amount);
      return DB.runTransaction(() => {
         myNewBalance.save();
         targetNewBalance.save();
      }).catch((error, RecordNotUniqueError) => {
         // todo: handle if the record failed to be saved because UserId+Nonce index unique constraint doesn't satisfied.
         // or just throw error, and let the user handle that.
         // or you can try to retry it yourself.
      });
  }
  
  transferWithMySQL(targetAccountId, amount, description){
     const query = `
      START TRANSACTION;

      INSERT INTO user_transactions (account_id, nonce, updates, balance, description, previous)
      SELECT t1.account_id, t1.nonce + 1, ${amount * -1}, t1.balance + ${amount * -1}, ${description}, t1.balance
      FROM user_transactions t1
      WHERE t1.account_id = ${this.accountId()} AND nonce = (
        SELECT max(nonce) from user_transactions WHERE (account_id = ${this.accountId()})
      );

      INSERT INTO user_transactions (account_id, nonce, updates, balance, description, previous)
      SELECT t1.account_id, t1.nonce + 1, ${amount}, t1.balance + ${amount}, ${description}, t1.balance
      FROM user_transactions t1
      WHERE t1.account_id = ${targetAccountId} AND nonce = (
        SELECT max(nonce) from user_transactions WHERE (account_id = ${targetAccountId})
      );

      COMMIT;
     `;
     // TODO: sanitize the query first
     return this.query(query);
  }
  // .... more code ....
}