ldpos-database-schema-suggestion.txt

ldpos-database-schema-suggestion.txt

## Database tables and fields:

accounts(primaryKey: address)[
  address (type: string, maxLength: 50),
  type (type: string, maxLength: 30, possibleValues: ['sig', 'multisig']),
  balance (type: string, maxLength: 20),
  ?forgingPublicKey (type: string, maxLength: 64),
  ?nextForgingPublicKey(type: string, maxLength: 64),
  ?nextForgingKeyIndex (type: integer, maxValue: 9007199254740991),
  ?multisigPublicKey (type: string, maxLength: 64),
  ?nextMultisigPublicKey(type: string, maxLength: 64),
  ?nextMultisigKeyIndex (type: integer, maxValue: 9007199254740991),
  ?sigPublicKey (type: string, maxLength: 64),
  ?nextSigPublicKey(type: string, maxLength: 64),
  ?nextSigKeyIndex (type: integer, maxValue: 9007199254740991),
  ?requiredSignatureCount (type: integer, maxValue: 9007199254740991),
  ?lastTransactionTimestamp (type: integer, maxValue: 9007199254740991),
  ?updateHeight (type: integer, maxValue: 9007199254740991)
]

Indexes:

- address
- balance

Notes:

- The `balance` field is a string but needs to be sorted numerically (not alphabetically) inside the database.

----------

transactions(primaryKey: id)[
  id (type: string, maxLength: 40),
  type (type: string, maxLength: 30, possibleValues: ['transfer', 'vote', 'unvote', 'registerSigDetails', 'registerMultisigDetails', 'registerForgingDetails', 'registerMultisigWallet']),
  ?recipientAddress (type: string, maxLength: 50),
  amount (type: string, maxLength: 20),
  fee (type: string, maxLength: 20),
  timestamp (type: integer, maxValue: 9007199254740991),
  message (type: string, maxLength: 256 but should be configurable via DAL option passed to its 'init' method),
  senderAddress (type: string, maxLength: 50),
  ?sigPublicKey (type: string, maxLength: 64),
  ?nextSigPublicKey (type: string, maxLength: 64),
  ?nextSigKeyIndex (type: integer, maxValue: 9007199254740991),
  ?delegateAddress (type: string, maxLength: 50),
  ?newForgingPublicKey (type: string, maxLength: 64),
  ?newNextForgingPublicKey (type: string, maxLength: 64),
  ?newNextForgingKeyIndex (type: integer, maxValue: 9007199254740991),
  ?newMultisigPublicKey (type: string, maxLength: 64),
  ?newNextMultisigPublicKey (type: string, maxLength: 64),
  ?newNextMultisigKeyIndex (type: integer, maxValue: 9007199254740991),
  ?newSigPublicKey (type: string, maxLength: 64),
  ?newNextSigPublicKey (type: string, maxLength: 64),
  ?newNextSigKeyIndex (type: integer, maxValue: 9007199254740991),
  ?memberAddresses (type: an array of wallet address strings; can be stored as JSON array string, variable length, the maximum number of member addresses is flexible based on config so there should not be a maximum)
  ?requiredSignatureCount (type: integer, maxValue: 9007199254740991),
  ?senderSignatureHash (type: string, maxLength: 44),
  ?signatures (type: an array of JSON object containing transaction signature strings; this cannot change and is not searchable so the entire JSON could be stored as serialized text of variable length; expected length is over 34K characters),
  blockId (type: string, maxLength: 40),
  indexInBlock (type: integer, maxValue: 9007199254740991)
]

Indexes:

- id
- timestamp
- blockId
- senderAddress
- recipientAddress

Notes:

- The `amount` and `fee` fields are strings but needs to be sorted numerically (not alphabetically) inside the database.
- A transaction must either have a `senderSignatureHash` (if it is a sig transaction) or a `signatures` array (if it is a multisig transaction).

----------

blocks(primaryKey: id)[
  id (type: string, maxLength: 40),
  height (type: integer, maxValue: 9007199254740991),
  timestamp (type: integer, maxValue: 9007199254740991),
  ?previousBlockId (type: string, maxLength: 40),
  forgerAddress (type: string, maxLength: 50),
  forgingPublicKey (type: string, maxLength: 64),
  nextForgingPublicKey (type: string, maxLength: 64),
  nextForgingKeyIndex (type: integer, maxValue: 9007199254740991),
  forgerSignature (type: string, maxLength: 32984),
  signatures (type: Can be either an array of JSON object containing block signatures in base64 format or can be stored as binary blobs to save space),
  numberOfTransactions (type: integer, maxValue: 9007199254740991),
  synched (type: boolean)
]

Indexes:

- id
- height
- timestamp
- forgerAddress

Notes:

- The `synched` field is private and is only kept in the database; it should not be returned as part of the block by any DAL methods because it is not accounted as part of the block's signature.

----------

ballots(primaryKey: id)[
  id (type: string, maxLength: 40),
  type (type: string, maxLength: 20, possibleValues: ['vote', 'unvote'])
  voterAddress (type: string, maxLength: 50),
  delegateAddress (type: string, maxLength: 50),
  active (type: boolean)
]

Indexes:

- id
- type
- voterAddress
- delegateAddress
- active

Notes:

- id matches the underlying transaction id
- There should only be one active ballot for each unique (voterAddress + delegateAddress) combination.
- Inserting a new 'vote' ballot should cause all active 'unvote' ballots to have their active fields set to false.
- Inserting a new 'unvote' ballot should cause all active 'vote' ballots to have their active fields set to false.

----------

delegates(primaryKey: address)[
  address (type: string, maxLength: 50),
  voteWeight (type: string, maxLength: 20),
  ?updateHeight (type: integer, maxValue: 9007199254740991)
]

Indexes:

- address
- voteWeight

Notes:

- The `voteWeight` field is a string but needs to be sorted numerically (not alphabetically) inside the database.

----------

multisigMemberships(primaryKey: [multsigAccountAddress, memberAddress])[
  multsigAccountAddress (type: string, maxLength: 50),
  memberAddress (type: string, maxLength: 50)
]

Indexes:

- [multsigAccountAddress, memberAddress]
- multsigAccountAddress
- memberAddress

Notes:

- Composite primary key of multsigAccountAddress and memberAddress - Each combination of these must be unique.