BD-4167 working doc, Spike: summarize design strategy for short-term off-chain database for improving table lookups
Purpose:
This document collects thoughts and ideas relating to BD-4167.
What are we trying to do:
understand the processes, tools, disciplines, and techniques which surround the standup and maintenance of an off chain, centralized, web based interface that provides read access for state tables which become performance challenged as the number of records per secondary index increases beyond the functioning limit of 1500 records per index.
Limitations of approach :
1. Cohesion of off-chain data
We will address the proof of cohesion of this off chain data with the FIO protocols state tables in a simple way that adds overhead by comparing record contents item by item between state and the off chain data.
We will perform a read of the desired off chain data, then we will get the record on chain by primary key and verify coherence with the off chain state item by item within the record.
The better way to do this :
Design something more all inclusive, an on chain hash of the contents of a record which is stored on chain, now we can verify cohesion of all record contents by looking at one item. this has more complexity and deserves focused attention to explore the best way to achieve proof of cohesion for FIO off chain data.
2. Latency of off-chain data
The process of populating this off chain data has a latency when compared to the chain which MUST be accounted for in the user experience. “IE: the record is NOT yet available in the off chain resource, please try this operation again later”.
Process high level :
Step 1: identify the problematic table, and clearly identify its' limitations of indexing.
Step 2: model the table in a cloud hosted relational database.
Step 3: modify a history node to capture the blockchain transactions relating to this table, and integrate this with the relational database. (insert update modify).
Step 4: create a javascript API into the relational data model.
Step 5: support and aid the integrators in using the new database.
Process description and resources:
Step 1: Identify limitations of existing tables
The existing limitation in FIO state must be analyzed, and documented by an operations maintenance blockchain engineer.
1) review the issue with the integrator/s
2) reproduce the issue on a developer local environment.
3) produce documentation of the issue and identify off chain DB as solution.
Step 2: Model the table in relational database
The data model must be ported from FIO contracts into the target database model by a cloud database engineer.
it is recommended that backing tables be created for each table designed.
it is recommended that insert, delete, and update triggers be created to maintain a record of all data modifications to the data model in the backing database.
it is recommended that performance and response time of the new data model be documented for the foreseeable number of records (publish the expected wait times for result sets, or paged data views).
The cloud database must be analyzed for hosting requirements by the database engineer and operations support.
operational database. (testnet and Mainnet)
integration and testing database.
performance and offline analysis database.
The database must be performance optimized for the queries being performed by the javascript SDK by integrators.
Step 3: FIO History node integration with relational database
The history node must be modified by a blockchain engineer with relational database experience.
The history node must be hosted and maintained by a block producer or motivated community member (or FIO Core team ops support).
The history node needs hosted in the following capacities
testnet
mainnet
integration and testing env
dev local testing
Step 4: Off-chain API
The javascript API for the data must be created, tested and maintained by a front end developer.
code level examples must be created to assist integrators in integrating the new DB
the javascript will query the offline DB and the FIO chain, it will verity matching data of all fields in the data, if this fails it will give a message “offline data is out of date, please try again later.”
Step 5: Integrator support and documentation
support for integrators must be provided to aid them in integration and troubleshooting.