---
# System prepended metadata

title: data model for CH2
tags: [data_model]

---

---
title: data model for CH2
tags: data_model
---

## Operations for CH1.5
```rust
// Insert transaction row
insert_to_tx_table(hash, trytes, milestone);

// Insert ADDRESS hint row (address hint with year/month clustered)
insert_to_hint_table(address, ADDRESS, year, month, milestone);

// Insert address data row (so we can query w/ (address, year, month))
insert_to_data_table(address,
                     year,
                     month,
                     address_kind, // (value < 0) ? INPUT:OUTPUT
                     timestamp_ms,
                     hash,
                     value,
                     milestone);
                     
let itr: [(&str, &str, &str, u8); 4] = [
          (trytes.trunk(), APPROVEE, TRUNK, 4),
          (trytes.branch(), APPROVEE, BRANCH, 6),
          (trytes.bundle(), BUNDLE, BUNDLE, 8),
          (tag, TAG, TAG, 10),
];
for (vertex, hint_kind, data_kind, query_id) in &itr {

    // Insert trunk/branch/bundle/tag hint row (vertex hint with year/month clustered)
    insert_to_hint_table(vertex, hint_kind, year, month, milestone);
    
    // Insert trunk/branch/bundle/tag data row (so we can query w/ (vertex, year, month))
    insert_to_data_table(vertex,
                         year,
                         month,
                         data_kind,
                         timestamp_ms,
                         hash,
                         value,
                         milestone);
}
```
## Tables for CH1.5

- Transaction table
```sql
CREATE TABLE IF NOT EXISTS keyspace_name.transaction (
    hash varchar,
    payload varchar,
    address varchar,
    value varchar,
    obsolete_tag varchar,
    timestamp varchar,
    current_index varchar,
    last_index varchar,
    bundle varchar,
    trunk varchar,
    branch varchar,
    tag varchar,
    attachment_timestamp varchar,
    attachment_timestamp_lower varchar,
    attachment_timestamp_upper varchar,
    nonce varchar,
    milestone bigint,
    PRIMARY KEY(hash, payload, address, value, obsolete_tag, timestamp, 
                current_index, last_index, bundle, trunk, branch, tag,
                attachment_timestamp,attachment_timestamp_lower,attachment_timestamp_upper, 
                nonce)
);
```

- Hint table
```sql=
CREATE TABLE IF NOT EXISTS keyspace_name.hint (
    vertex varchar,
    kind varchar,
    year smallint,
    month tinyint,
    milestone bigint,
    PRIMARY KEY(vertex, kind, year, month)
) WITH CLUSTERING ORDER BY (kind DESC, year DESC, month DESC);
```
- Data table
```sql=
CREATE TABLE IF NOT EXISTS keyspace_name.data (
    vertex varchar,
    year smallint,
    month tinyint,
    kind varchar,
    timestamp bigint,
    tx varchar,
    value bigint,
    milestone bigint,
    PRIMARY KEY((vertex,year,month), kind, timestamp, tx, value)
) WITH CLUSTERING ORDER BY (kind DESC, timestamp DESC);

```

## CH2 Message Spec 
- Specs
    - [Transaction](https://github.com/iotaledger/protocol-rfcs/blob/faa8857719264f7b028bcf6712c021f5fb0fc6c7/text/0000-transaction-payload/0000-transaction-payload.md)
    - [Milestone](https://github.com/iotaledger/protocol-rfcs/blob/002b8db2ab1ac044273e43dd1dce23745140436d/text/0019-milestone-payload/0019-milestone-payload.md)
    - [Indexation]()
- [bee-message]()

## Supported API for CH2
- `messageID`
    - which returns complete message payload
    - Query Message Table
- `milestoneIndex`
    - returns milestone index details
    - Query Hint Table -> Data Table
        - We calculate the milestone index details from the payload
        - Another way: we store the milestone index details in Data Table
- `indexationKey`
    - returns all items with matching indexation key
    - Query Hint Table -> Data Table
        - We return the messageID stored in the Data Table
- `addressHash` (hex or bech32)
    - returns balance and outputIDs
    - Query Hint Table -> Data Table
        - We calculate the balance and outputIDs from the payload
        - Another way: we store the balance and outputIDs in Data Table
- `outputID`
    - returns output details
    - Query Hint Table -> Data Table
        - We calculate the output details from the payload
        - Another way: we store the output details in Data Table
- `getMessageMetadata`
    - Query Message Table
        - We calculate the metadata from the payload
        - Another way: we store the metadata in the Message Table
- `getMessageChildIDs`
    - Query Message Table
        - Another way: we store the ChildIDs in the Message Table

## Tables for CH2

- Message table

```sql
CREATE TABLE IF NOT EXISTS keyspace_name.message (
    id varchar, // messageID
    type int, // 0: Transaction, 1: MileStone, 2: Indexation
    payload blob, // The whole field for the given message type
    PRIMARY KEY(id, type, payload)
);
```

- Hint table
    - Insert address/trunk/branch/bundle/tag hint row (vertex hint with shard clustered)
    - kind
        - MILESTONE (vertex is milestoneIndex)
        - INDEXATION (vertex is indexationKey)
        - ADDRESS (vertex is address)
```sql=
CREATE TABLE IF NOT EXISTS keyspace_name.hint (
    vertex varchar,
    kind varchar,
    shard bigint,
    PRIMARY KEY(vertex, kind, shard)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);
```

- Data table
    - Insert address/trunk/branch/bundle/tag data row (so we can query w/ (vertex, shard))
    - kind
        - MILESTONE (vertex is (milestoneIndex, shard))
        - INDEXATION (vertex is (indexationKey, shard))
        - INPUT (vertex is (address, shard))
        - OUTPUT (vertex is (address, shard))
```sql=
CREATE TABLE IF NOT EXISTS keyspace_name.data (
    vertex varchar,
    shard bigint,
    kind varchar,
    id varchar, // messageID
    payload blob,
    PRIMARY KEY((vertex,shard), kind, id, payload)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);

```

## Data Model w/o Payload Calculation for Each API Calls

- Message table

```sql
CREATE TABLE IF NOT EXISTS keyspace_name.message (
    id varchar, // messageID
    type int, // 0: Transaction, 1: MileStone, 2: Indexation
    payload blob, // The whole field for the given message type
    metadata varchar,
    child_ids varchar,
    PRIMARY KEY(id, type, payload, metadata)
);
```

- Hint table
    - Insert address/trunk/branch/bundle/tag hint row (vertex hint with shard clustered)
    - kind
        - MILESTONE (vertex is milestoneIndex)
        - INDEXATION (vertex is indexationKey)
        - ADDRESS (vertex is address)
```sql=
CREATE TABLE IF NOT EXISTS keyspace_name.hint (
    vertex varchar,
    kind varchar,
    shard bigint,
    PRIMARY KEY(vertex, kind, shard)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);
```

- Data table
    - Insert address/trunk/branch/bundle/tag data row (so we can query w/ (vertex, shard))
    - kind
        - MILESTONE (vertex is (milestoneIndex, shard))
        - INDEXATION (vertex is (indexationKey, shard))
        - INPUT (vertex is (address, shard))
        - OUTPUT (vertex is (address, shard))
```sql=
CREATE TABLE IF NOT EXISTS keyspace_name.data (
    vertex varchar,
    shard bigint,
    kind varchar,
    id varchar, // messageID
    payload blob,
    milestone_details varchar,
    balance bigint,
    outputIDs varchar,
    output_details varchar,
    PRIMARY KEY((vertex,shard), kind, id, payload, milestone_details, balance, 
                outputIDs, output_details)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);
```

## Unsolved Questions
- The balance definition? (Not found in the transaction spec)
- The outputIDs definition? (Not found in the transaction spec)
- The output details definition? (Not found in the transaction spec)
- The metadata definition? (Not found in the transaction spec) 
- ChildIDs definition? ((Not found in the transaction spec))
