Introduction

The FIO Foundation is considering developing a FIO-specific chain data storage solution to improve accessibility of the data. The purpose of this document is to define an approach for storing data specific to the FIO Protocol in a Relational Database (FIO RDB). Unlike a generic smart contract platform, FIO Chain data can be easily modeled into a relational database, because it contains limited number of entities, e.g. FIO Handles, FIO Domains, FIO Requests, etc. See Proposed FIO RDB Model.

Objective

The primary objective of the FIO RDB is to allow for easy and fast query for FIO Specific information using well understood SQL language.

Existing Query Functionality

Chain API

Chain API allows for limited query of state data by using FIO Chain Getters for specific data or /get_table_rows for more generic data. However, these getters have a number of limitations including:

Queries can only be performed using indexes that exist in state tables
Some indexes are hashes, which requires additional hashing before query can be executed
Queries for large sets of data can time out without returning sufficient results even when paging is attempted

History APIs

History APIs (V1, Hyperion) allow for querying of historical data, but the functionality is limited to generic EOSIO block, transactions, traces. Answering more complex questions such as when was a particular FIO Domain last renewed is not possible.

FIO ETL

fio.etl is a service purpose built to consume action traces via web sockets and storing them in elasticsearch in order to power Grafana Statistics site. There are limited number of indexes and number of queries are not supported.

Approach

It is believed that the best approach would be to stream blocks, transactions, and action traces via web sockets similar to eos-chronicle or antelope-ship-reader and inserting the data into RDB using pre-defined transformation rules for which action traces trigger which inserts.

Requirements

High performance

FIO RDB needs to be able to:

Achieve performance required to build real-time services
Replay the entire FIO Chain since genesis in reasonable time
Handle microforks

API

FIO RDB data would be accessed using purpose-built API queries, which would map to SQL queries, and returning results in json format.

Prototype Examples

The following are examples from a prototype built to prove the concept.

Sample DB Model

Sample Transformation rules

// Defines strategy for parsing a transaction
// class name is action
export interface ParseStrategy {
    supports(transactionData: any, blockData: any): boolean;
    process(transactionData: any, blockData: any): Promise<void>;
}

export class newaccount implements ParseStrategy {
    // Check if this strategy applies to the given trace and block data
    supports(transactionData: any, blockData: any): boolean {
        return (
            transactionData.act.account === 'eosio' &&
            transactionData.act.name === 'newaccount'
        );
    }

    // Process the given trace and block data
    async process(transactionData: any, blockData: any): Promise<void> {
        await prisma.accounts.create({
            data: {
                account: transactionData.act.data.name,
                timestamp: new Date(blockData.timestamp),
            }
        });

        // Insert account_transactions relationship
        prisma.account_transactions.upsert({
            where: {
                accountId_transactionId: {
                    accountId: transactionData.act.data.name,
                    transactionId: transactionData.trx_id,
                }
            },
            update: {},
            create: {
                accountId: transactionData.act.data.name,
                transactionId: transactionData.trx_id,
                type: 'target'
            },
        });

    }
}

export class trnsfiopubky implements ParseStrategy {
    supports(transactionData: any, blockData: any): boolean {
        return transactionData.act.account === 'fio.token' &&
            transactionData.act.name === 'trnsfiopubky';
    }

    async process(transactionData: any, blockData: any): Promise<void> {
        prisma.token_transfers.create({
            data: {
                // id is auto-generated by the database
                timestamp: new Date(blockData.timestamp),
                from: transactionData.act.authorization[0].actor,
                to: accountHash(transactionData.act.data.payee_public_key),
                amount:  transactionData.act.data.amount/1000000000,
                type: 'a2a',
                memo: '',
                transactionRel: { connect: {id: transactionData.trx_id} }
            }
        });

        // Insert account_transactions relationship
        prisma.account_transactions.upsert({
            where: {
                accountId_transactionId: {
                    accountId: accountHash(transactionData.act.data.payee_public_key),
                    transactionId: transactionData.trx_id,
                }
            },
            update: {},
            create: {
                accountId: accountHash(transactionData.act.data.payee_public_key),
                transactionId: transactionData.trx_id,
                type: 'target'
            },
        });

    }
}

export class transfer implements ParseStrategy {
    supports(transactionData: any, blockData: any): boolean {
        return transactionData.act.account === 'fio.token' &&
            transactionData.act.name === 'transfer' &&
            transactionData.receiver === 'fio.treasury';}

    async process(transactionData: any, blockData: any): Promise<void> {
        let transferType = this.determineTransferType(transactionData.act.data.memo);
        if (transferType !== 'unknown') {
            prisma.token_transfers.create({
                data: {
                    // id is auto-generated by the database
                    timestamp: new Date(blockData.timestamp),
                    from: transactionData.act.data.from,
                    to: transactionData.act.data.to,
                    amount: parseFloat(transactionData.act.data.quantity.replace(' FIO', '')),
                    type: transferType,
                    memo: transactionData.act.data.memo,
                    transactionRel: {connect: {id: transactionData.trx_id}}
                }
            });

            // Insert account_transactions relationship for 'fio.treasury'
            // The actor account relationship is covered in primary transaction processing
            prisma.account_transactions.upsert({
                where: {
                    accountId_transactionId: {
                        accountId: 'fio.treasury',
                        transactionId: transactionData.trx_id,
                    }
                },
                update: {},
                create: {
                    accountId: 'fio.treasury',
                    transactionId: transactionData.trx_id,
                    type: 'target'
                },
            });

            // Insert account_transactions relationship for receiver of transfer
            // In most cases the actor from primary transaction is receiver, except below
            if (transferType === 'tpid_reward') {
                prisma.account_transactions.upsert({
                    where: {
                        accountId_transactionId: {
                            accountId: transactionData.act.data.to,
                            transactionId: transactionData.trx_id,
                        }
                    },
                    update: {},
                    create: {
                        accountId: transactionData.act.data.to,
                        transactionId: transactionData.trx_id,
                        type: 'target'
                    },
                });
            }
        }
    }

    private determineTransferType(memo: string): string {
        if (memo.startsWith('FIO fee:')) return 'fee';
        if (memo.startsWith('New tokens produced from reserves')) return 'mint';
        if (memo.startsWith('Paying TPID from treasury.')) return 'tpid_reward';
        if (memo.startsWith('Paying Staking Rewards')) return 'staking_reward';
        if (memo.startsWith('Paying producer from treasury.')) return 'bp_reward';
        return 'unknown'; // Default or unknown type
    }
}

export class regaddress implements ParseStrategy {
    supports(transactionData: any, blockData: any): boolean {
        return transactionData.act.account === 'fio.address' &&
            transactionData.act.name === 'regaddress'
    }

    async process(transactionData: any, blockData: any): Promise<void> {
        prisma.handles.create({
            data: {
                timestamp: new Date(blockData.timestamp),
                handle: transactionData.act.data.fio_address,
                owner: accountHash(transactionData.act.data.owner_fio_public_key),
                status:  'registered',
                domainRel: { connect: {domain: transactionData.act.data.fio_address.split('@')[1]} }
            }
        });

        // Insert account_transactions relationship
        prisma.account_transactions.upsert({
            where: {
                accountId_transactionId: {
                    accountId: accountHash(transactionData.act.data.owner_fio_public_key),
                    transactionId:  transactionData.trx_id,
                }
            },
            update: {},
            create: {
                accountId: accountHash(transactionData.act.data.owner_fio_public_key),
                transactionId:  transactionData.trx_id,
                type: 'target'
            },
        });
    }
}

export class regdomain implements ParseStrategy {
    supports(transactionData: any, blockData: any): boolean {
        return transactionData.act.account === 'fio.address' &&
            transactionData.act.name === 'regdomain';
    }

    async process(transactionData: any, blockData: any): Promise<void> {
        prisma.domains.create({
            data: {
                timestamp: new Date(blockData.timestamp),
                domain: transactionData.act.data.fio_domain,
                owner: accountHash(transactionData.act.data.owner_fio_public_key),
                status:  'registered',
                expire: new Date(new Date(blockData.timestamp).getTime() + YEARTOSECONDS * 1000)
            }
        });

        // Insert account_transactions relationship
        prisma.account_transactions.upsert({
            where: {
                accountId_transactionId: {
                    accountId: accountHash(transactionData.act.data.owner_fio_public_key),
                    transactionId:  transactionData.trx_id,
                }
            },
            update: {},
            create: {
                accountId: accountHash(transactionData.act.data.owner_fio_public_key),
                transactionId:  transactionData.trx_id,
                type: 'target'
            },
        });
    }
}

export class addaddress implements ParseStrategy {
    supports(transactionData: any, blockData: any): boolean {
        return transactionData.act.account === 'fio.address' &&
            transactionData.act.name === 'addaddress'
    }

    async process(transactionData: any, blockData: any): Promise<void> {

        prisma.pub_addresses.create({
            data: {
                // id is auto-generated by the database
                chain_code: transactionData.act.data.chain_code,
                token_code: transactionData.act.data.token_code,
                public_address: transactionData.act.data.public_address,
                handleRel: { connect: { handle: transactionData.act.data.fio_address } }
            }
        });
    }
}

Contributors DAO

FIO Chain Data Storage

Table of Contents