Skip to content

Instantly share code, notes, and snippets.

@andreafspeziale

andreafspeziale/Lisk blockchain database structure.md

Last active October 9, 2020 16:28
Show Gist options
  • Save andreafspeziale/088cfce3f7b3fa08c2fbcc4d89d80739 to your computer and use it in GitHub Desktop.
Save andreafspeziale/088cfce3f7b3fa08c2fbcc4d89d80739 to your computer and use it in GitHub Desktop.
Download ZIP
Lisk blockchain platform PostgreSQL database structure description
Raw
Lisk blockchain database structure.md

Lisk blockchain database structure

Lisk is a blockchain application platform and crypto-currency, which offers an all round solution for Node.js and JavaScript developers to deploy their own blockchain applications.

You can find a very nice documentation about Lisk here but some useful iformation like the complete database structure are missing.

This gist aims at telling you more about where the Lisk blockchain is stored. The following notes are taken from the study of Lisk testnet 0.9.3a version.

Database

The Lisk blockchain is stored in a relational database structure using PostgreSQL "the world's most advanced open source database".

Tables

In this section I'll list all the tables and releated information.

blocks

Column Name Type Length Not Null Default Value
id varchar 20 true NULL
rowId int4 10 true nextval('"blocks_rowId_seq"'::regclass)
version int4 10 true NULL
timestamp int4 10 true NULL
height int4 10 true NULL
previousBlock varchar 20 false NULL
numberOfTransactions int4 10 true NULL
totalAmount int8 19 true NULL
totalFee int8 19 true NULL
reward int8 19 true NULL
payloadLength int4 10 true NULL
payloadHash bytea true NULL
generatorPublicKey bytea true NULL
blockSignature bytea true NULL
Index Name Columns Unique
blocks_pkey id true
blocks_generator_public_key generatorPublicKey false
blocks_numberOfTransactions numberOfTransactions false
blocks_reward reward false
blocks_rounds (ceil(heigth::double precision / 101::double precision)::integer) false
blocks_rowId rowId false
blocks_timestamp timestamp false
blocks_totalAmount totalAmount false
blocks_totalFee totalFee false
blocks_heigth heigth true
blocks_previousBlock previousBlock true
Key Name Columns Foreign Table Foreign Columns On Update On Delete
blocks_previousBlock_fkey previousBlock blocks id NO_ACTION SET_NULL

blocks_list

Column Name Type Length Not Null Default Value
b_id varchar 20 false NULL
b_version int4 10 false NULL
b_timestamp int4 10 false NULL
b_heigth int4 10 false NULL
b_previousBlock varchar 20 false NULL
b_numberOfTransactions int4 10 false NULL
b_totalAmount int8 19 false NULL
b_totalFee int8 19 false NULL
b_reward int8 19 false NULL
b_payloadLength int4 10 false NULL
b_payloadHash text false NULL
b_generatorPublicKey text false NULL
b_blockSignature text false NULL
b_confirmations int4 10 false NULL

dapps

Column Name Type Length Not Null Default Value
transactionId varchar 20 true NULL
name varchar 32 true NULL
description varchar 160 false NULL
tags varchar 160 false NULL
link text false NULL
type int4 10 true NULL
category int4 10 true NULL
icon text false NULL
Index Name Columns Unique
dapps_name name false
dapps_trs_id transactionId false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
dapps_transactionId_fkey transactionId trs id NO_ACTION CASCADE

delegates

Column Name Type Length Not Null Default Value
username varchar 20 true NULL
transactionId varchar 20 true NULL
Index Name Columns Unique
delegates_trs_id transactionId false
delegates_unique (username, transactionId) true
unique_delegates (username, transactionId) true
Key Name Columns Foreign Table Foreign Columns On Update On Delete
delegates_transactionId_fkey transactionId trs id NO_ACTION CASCADE

fork_stat

Column Name Type Length Not Null Default Value
delegatePublicKey bytea true NULL
blockTimestamp int4 10 true NULL
blockId varchar 20 true NULL
blockHeight int4 10 true NULL
previousBlock varchar 20 true NULL
cause int4 10 true NULL

full_blocks_list

Column Name Type Length Not Null Default Value
b_id varchar 20 false NULL
b_version int4 10 false NULL
b_timestamp int4 10 false NULL
b_height int4 10 false NULL
b_previousBlock varchar 20 false NULL
b_numberOfTransactions int4 10 false NULL
b_totalAmount int8 19 false NULL
b_totalFee int8 19 false NULL
b_reward int8 19 false NULL
b_payloadLength int4 10 false NULL
b_payloadHash text false NULL
b_generatorPublicKey text false NULL
b_blockSignature text false NULL
t_id varchar 20 false NULL
t_rowId int4 10 false NULL
t_type int2 5 false NULL
t_timestamp int4 10 false NULL
t_senderPublicKey text false NULL
t_senderId varchar 22 false NULL
t_recipientId varchar 22 false NULL
t_amount int8 19 false NULL
t_fee int8 19 false NULL
t_signature text false NULL
t_signSignature text false NULL
s_publicKey text false NULL
d_username varchar 20 false NULL
v_votes text false NULL
m_min int4 10 false NULL
m_lifetime int4 10 false NULL
m_keysgroup text false NULL
dapp_name varchar 32 false NULL
dapp_description varchar 160 false NULL
dapp_tags varchar 160 false NULL
dapp_type int4 10 false NULL
dapp_link text false NULL
dapp_category int4 10 false NULL
dapp_icon text false NULL
in_dappId varchar 20 false NULL
ot_dappId varchar 20 false NULL
ot_outTransactionId varchar 20 false NULL
t_requesterPublicKey text false NULL
t_signatures text false NULL

intransfer

Column Name Type Length Not Null Default Value
dappId varchar 20 true NULL
transactionId varchar 20 true NULL
Key Name Columns Foreign Table Foreign Columns On Update On Delete
intransfer_transactionId_fkey transactionId trs id NO_ACTION CASCADE

mem_accounts

Column Name Type Length Not Null Default Value
username varchar 20 false NULL
isDelegate int2 5 false 0
u_isDelegate int2 5 false 0
secondSignature int2 5 false 0
u_secondSignature int2 5 false 0
u_username varchar 20 false NULL
address varchar 22 true NULL
publicKey bytea false NULL
secondPublicKey bytea false NULL
balance int8 19 false 0
u_balance int8 19 false 0
vote int8 19 false 0
rate int8 19 false 0
delegates text false NULL
u_delegates text false NULL
multisignatures text false NULL
u_multisignatures text false NULL
multimin int2 5 false 0
u_multimin int2 5 false 0
multilifetime int2 5 false 0
u_multilifetime int2 5 false 0
blockId varchar 20 false NULL
nameexist int2 5 false 0
u_nameexist int2 5 false 0
producedblocks int4 10 false 0
missedblocks int4 10 false 0
fees int8 19 false 0
rewards int8 19 false 0
virgin int2 5 false 1
Index Name Columns Unique
mem_accounts_pkey address true
mem_accounts_address address false
mem_accounts_address_upper upper(address::text) false
mem_accounts_balance balance false
mem_accounts_block_id blockId false
mem_accounts_get_delegates (vote, encode("publicKye", 'hex'::text)) false
mem_accounts_is_delegate isDelegate false

mem_accounts2delegates

Column Name Type Length Not Null Default Value
accountId varchar 22 true NULL
dependentId varchar 64 true NULL
Index Name Columns Unique
mem_accounts2delegates_accountId accountId false
mem_accounts2delegates_depId dependentId false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
mem_accounts2delegates_accountId_fkey accountId mem_accounts address NO_ACTION CASCADE

mem_accounts2multisignatures

Column Name Type Length Not Null Default Value
accountId varchar 22 true NULL
dependentId varchar 64 true NULL
Index Name Columns Unique
mem_accounts2multisignatures_accountId accountId false
Index Name Columns Unique
mem_accounts2multisignatures_accountId accountId false

mem_accounts2u_delegates

Column Name Type Length Not Null Default Value
accountId varchar 22 true NULL
dependentId varchar 64 true NULL
Index Name Columns Unique
mem_accounts2u_delegates_accountId accountId false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
mem_accounts2u_delegates_accountId_fkey accountId mem_accounts address NO_ACTION CASCADE

mem_accounts2u_multisignatures

Column Name Type Length Not Null Default Value
accountId varchar 22 true NULL
dependentId varchar 64 true NULL
Index Name Columns Unique
mem_accounts2u_multisignatures_accountId accountId false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
mem_accounts2u_multisignatures_accountId_fkey accountId mem_accounts address NO_ACTION CASCADE

mem_round

Column Name Type Length Not Null Default Value
address varchar 22 false NULL
amount int8 19 false NULL
delegate varchar 64 false NULL
blockId varchar 20 false NULL
round int8 19 false NULL
Index Name Columns Unique
mem_round_address address false
mem_round_round round false

mem_round_snapshot

Column Name Type Length Not Null Default Value
address varchar 22 false NULL
amount int8 19 false NULL
delegate varchar 64 false NULL
blockId varchar 20 false NULL
round int8 19 false NULL

mem_votes_snapshot

Column Name Type Length Not Null Default Value
address varchar 22 false NULL
vote int8 19 false NULL

migrations

Column Name Type Length Not Null Default Value
id varchar 22 true NULL
name text true NULL
Index Name Columns Unique
migrations_pkey id true

multisignatures

Column Name Type Length Not Null Default Value
min int4 10 true NULL
lifetime int4 10 true NULL
keysgroup text true NULL
transactionId varchar 20 true NULL
Index Name Columns Unique
multisignatures_trs_id transactionId false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
multisignatures_transactionId_fkey transactionId trs id NO_ACTION CASCADE

outtransfer

Column Name Type Length Not Null Default Value
transactionId varchar 20 true NULL
dappId varchar 20 true NULL
outTransactionId varchar 20 true NULL
Index Name Columns Unique
out_transaction_id outTransactionId true
outtransfer_outTransactionId_key outTransactionId true
Key Name Columns Foreign Table Foreign Columns On Update On Delete
outtransfer_transactionId_fkey transactionId trs id NO_ACTION CASCADE

peers

Column Name Type Length Not Null Default Value
id int4 10 true nextval('peers_id_seq'::regclass)
ip inet 50 true NULL
port int2 5 true NULL
state int2 5 true NULL
os varchar 64 false NULL
version varchar 11 false NULL
clock int8 19 false NULL
height int4 10 false NULL
Index Name Columns Unique
peers_pkey id true
peers_broadhash broadhash false
peers_height height false
address_unique (ip, port) true

peers_dapp

Column Name Type Length Not Null Default Value
peerId int4 10 true NULL
dappid varchar 20 true NULL
Index Name Columns Unique
peers_dapp_unique (peerId, dappid) true
Key Name Columns Foreign Table Foreign Columns On Update On Delete
peers_dapp_peerId_fkey peerId peers id NO_ACTION CASCADE

rounds_fees

Column Name Type Length Not Null Default Value
height int4 10 true NULL
timestamp int4 10 true NULL
fees int8 19 true NULL
publicKey bytea true NULL
Index Name Columns Unique
rounds_fees_fees fees false
rounds_fees_height height false
rounds_fees_public_key publicKey false
rounds_fees_round (ceil(height::double precision / 100::double precision)::integer) false
rounds_fees_timestamp timestamp false

signatures

Column Name Type Length Not Null Default Value
transactionId varchar 20 true NULL
publicKey bytea true NULL
Index Name Columns Unique
signatures_pkey transactionId true
signatures_trs_id transactionId false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
signatures_transactionId_fkey transactionId trs id NO_ACTION CASCADE

trs

Column Name Type Length Not Null Default Value
id varchar 20 true NULL
rowId int4 10 true nextval('"trs_rowId_seq"'::regclass)
blockId varchar 20 true NULL
type int2 5 true NULL
timestamp int4 10 true NULL
senderPublicKey bytea true NULL
senderId varchar 22 true NULL
recipientId varchar 22 false NULL
amount int8 19 true NULL
fee int8 19 true NULL
signature bytea true NULL
signSignature bytea false NULL
requesterPublicKye bytea false NULL
signatures text false NULL
Index Name Columns Unique
trs_pkey id true
trs_block_id blockId false
trs_recipient_id recipientId false
trs_rowId rowId false
trs_senderPublicKey senderPublicKey false
trs_sender_id senderId false
trs_timestamp timestamp false
trs_type type false
trs_upper_recipient_id upper("recipientId"::text) false
trs_upper_sender_id upper("senderId"::text) false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
trs_blockId_fkey blockId blocks id NO_ACTION CASCADE

trs_list

Column Name Type Length Not Null Default Value
t_id varchar 20 false NULL
b_height int4 10 false NULL
t_blockId varchar 20 false NULL
t_type int2 5 false NULL
t_timestamp int4 10 false NULL
t_senderPublicKey bytea false NULL
m_recipientPublicKey bytea false NULL
t_senderId text false NULL
t_recipientId text false NULL
t_amount int8 19 false NULL
t_fee int8 19 false NULL
t_signature text false NULL
t_SignSignature text false NULL
t_signatures text false NULL
confirmations int4 10 false NULL

votes

Column Name Type Length Not Null Default Value
votes text false NULL
transactionId varchar 20 true NULL
Index Name Columns Unique
votes_trs_id transactionId false
Key Name Columns Foreign Table Foreign Columns On Update On Delete
votes_transactionId_fkey transactionId trs id NO_ACTION CASCADE

E-R Draft

The following image is a draft about the relations btw the entities found in the upper database study.

E-R using Adobe XD

This is a more readable version of the above sketch

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment