Rationale: “The Ethereum Blockchain just isn’t cutting it for storing the Inventory data and accounting records… I need another form of non-volatile storage to hold all the inventory data and accounting records, one that’s fast, supports ACID and doesn’t have a confirmation popup for every insert or update in a transaction.”
Impulse: “Hey, I’ve got a great idea!”
Rationale: “Oh really? What is it??”
Impulse: “Let’s try to teach ourselves how to use MongoDB in an afternoon and use that to store all the inventory data and accounting records!”
Rationale: “Hmmm… That doesn’t sound like the best idea… trying to implement MongoDB could be more complicated than just learning how to u…”
Wreckless Decision Making: “LET’S DO IT!!!”
Problems With the Solo Blockchain Back-End
I have mentioned in a previous posting that I wanted to try and eliminate the need for a “traditional” centralised back-end database in my app, however, after attempting to implement the accounting functions into the app using only the blockchain, I ran into a few issues.
I believe most of these issues are a limitation of the Ethereum platform for my application, however, they were enough for me to reconsider my options when it came to achieving more “completeness” with the functions that I want to implement in the prototype.
Making “Inserts”
Ultimately, the big issues come from the speed (or lack of) of the Ethereum blockchain, the lack of ACID “transactions” and the gas system.
To implement the perpetual inventory system in conjunction with trying to create and maintain the accounting records I need to do multiple additional operations for:
- Creating an Invoice
- Signing an Invoice
- Paying an Invoice
- Receiving Payment for an Invoice
Essentially for the first two functions, I need to update the inventory numbers (current SOH and total current value) and for all three I need to add the appropriate debit and credit entries to the appropriate ledger accounts.
The obvious choice for consistency in this situation would be to bundle all of these operations together in a single “transaction” for each function which for the last 3 functions would actually be okay. The issue arises when creating an invoice which requires the following actions:
- Post the Invoice to the Blockchain
- Calculate the Average cost of each inventory item
- Transfer the cost of each item and the sale price to the ledger accounts
- Update the inventory
Sounds simple enough, however, when we break up the logic of this “transaction”, it looks more like this:
- Update Invoice Struct with New Invoice Details
- For each invoice line
- Convert bytes32 into a string
- split string by the delimiter into an array of SKUs
- For each SKU in SKU array:
- Get the current inventory values (SOH and Total value) from Inventory Struct
- Calculate average cost and store in a local variable
- Update Accounts Receivable Account – debit the invoice grand total
- For each SKU in SKU array:
- Guessing that it gets to about here before:
- ———-EXCEEDED BLOCK GAS LIMIT ERROR———-
- Update SKUs Sales Account – credit line total
- Update SKUs COGS Account – credit SKUs average cost
- Update SKUs Inventory Account – credit SKUs average cost
- For each SKU in SKU array
- Update Inventory Struct – deduct qty and (average cost * qty)
And here we can start to see the problem, to do all of this in one “transaction” and keep it atomic requires that all of the computation is done on the Blockchain side, which, as it turns out is quite a heavy burden for the Ethereum blockchain, too much in fact as I kept getting the error shown above in red.
Splitting this function up into smaller parts would allow it to “work” however, this implementation would mean the transactions are not atomic and could result in inconsistencies with the records and when it comes to accounting, that is simply not tolerable, not to mention the long wait times as each portion needs to be completed in sequence and the MetaMask popups for each one, over 5 of them actually…
Performing “Selects”
Another issue I have been having is to do with retrieving (“selecting”) data from the blockchain, it is cumbersome at best and a nightmare any other time.
Despite refactoring as best I could, just getting a list of all the invoices and turning it into “human-friendly” information takes 5 different functions, that doesn’t include actually displaying them on screen or allow for any kind of filtering (i.e. dates, companies, status) every single invoice needs to be pulled from the blockchain into memory and processed locally each time, with less than 100 “test” invoices it takes about 40 seconds, now imagine doing that for a corporation with hundreds of thousands of invoices.
Then imagine a company with thousands of inventory items and ledger accounts… pulling ALL of that information down to perform operations on just a few would be ridiculous.
And alas, this is where I have had to make an important decision and I have come to a major finding in trying to build my project.
Implementing the “Extended” MEAN Stack
Traditional MEAN Stack
In order to combat the issues with the Blockchain, I decided to implement the rest of the MEAN stack (as I am already using Angular.js for the front-end) which as shown above includes: MongoDB, the Express framework, Angular.js and Node.js.
As I will also be using Ethereum as a back-end, I have coined my current set up the MEEAN stack, shown below.
“Extended” MEEAN Stack
This allows me (and potentially many other developers that use this (or the MERN stack which uses the React framework instead of Angular)) to do many things that are otherwise not possible while using Ethereum as the sole “back-end” or database.
The main function that the MongoDB serves is to provide a lightning fast non-volatile storage solution with each entry containing a “pointer” to the information stored on the Blockchain. So, for example, the Inventory Ledger accounts records for an item would look something like this (i’m yet to finalise the design) in the MongoDB database:
{
accountCode: 601
name: [SKU/Item name] Inventory
category: Asset
debits:
{
{
date: 12/09/2018,
amount: 200,
blockchainTransactionHash: 0xabcd1234567890
}
{
date: 15/09/2018,
amount: 200,
blockchainTransactionHash: 0x2468abcdefghij
}
}
credits:
{
{
date: 13/09/2018,
amount: 200,
blockchainTransactionHash: 0xlmno0987654321
}
{
date: 16/09/2018,
amount: 200,
blockchainTransactionHash: 0xpqrs2468135790
}
}
}
This would allow me to do a few things, firstly, I can query the MongoDB and return only the records within a specific date range (i.e. the current or previous reporting periods) and secondly I can use the “blockchainTransactionHash” value to validate the records.
Perpetual Inventory System
I will also be using the MongoDB for the inventory system, currently, I have implemented the “sales” side of the inventory system which means when an invoice is created the inventory items document is updated with the new numbers (SOH and Total Value) and all of an items transactions are stored as a sub-document, it looks like this in the database:
I am yet to finalise where the invoice numbers will be coming from in the system (currently it is using only one incrementing number on the blockchain which means that companies cannot have their own numbering schemes) so the “Ref” field doesn’t contain the invoice number yet but this provides an audit trail for where the inventory is coming from or going to.
What Else Can it Do?
Another example of where this could be used (although I won’t be implementing it), it to monitor the blockchain and check each new block as it is added to determine whether it contains any transactions, and make a records of all the blocks that do alter a balance with the date and block number, this would allow me to see the “historical balance” of a blockchain account for a particular period (something that is currently not possible with Ethereum alone).
Considerations
There are, however, still a few things to consider with this stack design particularly for an accounting application. The main one is that, yes, it is still possible for there to be an inconsistency between the records on the Blockchain and in the MongoDB, the risk of this could potentially be reduced by using a messaging service like RabbitMQ but that definitely falls into the “work that could be done in the future” basket.
I hate to keep going back to this, however, EOS has a MongoDB plugin built-in which would actually do all of this for me but I have come too far with the Ethereum development to switch over now and still there are no great tutorials on building a front end for an EOS dApp.
Is MongoDB Really Viable for an Accounting Application in 2018?
Anyone reading this that has developed an accounting system might be thinking, but Corben, MongoDB is an awful choice for an accounting database! And, up until version 4.0 that may have been true.
One of the biggest arguments has been that MongoDB didn’t have ACID support for multi-document updates, which is inherently a trait that is required by an accounting system, however, this all changed on the release of MongoDB version 4.0 when they introduced proper multi-document “transactions” with full ACID support, meaning that, just like a relational database, all records are updated or everything is rolled back.
Another argument is that relational models are the perfect design for storing accounting data because, unlike most real-world data, accounting data is actually tabular and fits extremely well into a relational model. And actually, I have to agree with this argument, a traditional relational database would most likely be a better fit for my application, however, I believe that MongoDB will handle it just fine and I went with this option for two main reasons:
First and foremost, because over the last few years I have had a lot of interaction with SQL databases including MS Access, MySQL and SQL Server, but thus far I have never used MongoDB or any NoSQL databases for that matter, so I’m doing it for the experience (another plus is that this also gives me experience using NodeJS and Express).
And secondly, because it just seemed much easier to implement into my current stack, no need for creating a normalised design, working out joins or Object Relational Mapping.
This late in the game trying to add the kind of complexity that would come with an SQL design is a bad idea, plus my experience in using the various SQL systems as well as various programming languages translated rather easily to MongoDB as the concepts are similar but, in my opinion, far simpler, which meant I really could pick up the necessities in an afternoon, learning to use Express and the MongoDB API (and Mongoose) to build a RESTful API was actually much harder, but a lot of fun.
Goals for Next Week
Phew! This brings me to the conclusion of another week and to making plans for next week, this week was quite heavy although I have not moved forward much it has involved a lot of problem-solving, causing me to neglect my DAT701 assignment quite a lot (Sorry Nick! and myself 😓) but next week, unrelated to the Project: I need to finish my DAT assignment.
Related to the project, I need to really get cracking on the back-end of the accounting system and finish up the inventory system so I can finally get some data in! Only 3 weeks behind schedule…
But the delay has come because I now realise that the back-end of the accounting system needs to be functional (i.e. capturing the necessary data in the ledger accounts and inventory) at the time invoices are being created, signed and paid so I cannot begin to add test data until this is functional so that will be the only goal for next week.
Corben Ferris 