Maintaining historical state in our database

[il3ven]

We currently use LevelDB and we are facing a few limitations. We want to represent one-to-many relationship but it isn't straightforward in LevelDB.

I proposed to give sqlite a try. We favored LevelDB earlier because it is faster. I also believe that even if sqlite is slower than levelDB it won't matter because network I/O should be the bottleneck and not disk I/O.

Challenges with sqlite

Creating a database that maintains history

In the spirit of blockchains, we structured LevelDB to maintain all the history. Like Ethereum we keep track of all states and not just the latest state.

track = getTrack(id, 100)
track.owner // 0xabc

track = getTrack(id, 101)
track.owner // 0x123

// We get different owners because the owner changed in block number 101
// The point is that we can query for any block number and get the state at that point of time

It is good to have all the history though we haven't found a proper use case for it. Another advantage was that it helped in debugging. We could re-run the crawler for any block and not worry about the database being changed.

Maintaining history in SQL

Since we maintain all the previous states in LevelDB, it would have been ideal to maintain it in SQL too. I faced challenges here.

Upon researching, I found that the recommended way to maintain history in SQL is to have a createdAt column. Updates will result in creation of a new row with an updated createdAt column instead of updating the previous row.

| id  |  name   | uri           | createdAt |
| 1   |  Bolt   | ipfs://Qmfumk | 100       |
| 1   |  Bolt   | ipfs://Befqkn | 101       |

In the above example, we didn't update the row with id=1 but instead added a new row.

In theory, the above works but I faced development challenges while implementing it. The SQL queries start getting really complicated when we have four tables implementing one-to-many relationships with each other and each table keeping record of previous rows.

a visualization of the proposed schema (note: this is not the current schema but the ideal schema we want to have)

I tried implementing the above schema by creating four tables: tracks, manifestations, tokens and owners. Writing a SELECT query joining these four tables where each table maintains history using a createdAt field turned out to be difficult that I anticipated.

(let me know if an example is required here to explain the challenges)

Maintaining only the latest state

As explained above, there are implementation challenges in maintaining state. Therefore, I propose to skip the maintenance of history. Important note - we can still and should maintain the history of certain information such as owners. We will maintain only the latest state but the latest state will have a list of all the previous and the current owners.

What's the downside?

Debugging would be harder. Imagine the following scenario:

At block number 100, a track was minted with the name "Humble". At block number 101, the track's name was changed to "Humbleness". Assuming our crawler is synced up-to block number 101, the name of the track in our DB will be "Humbleness". If at this point we were to re-run a history-maintaining crawler for just block number 100 nothing would change in our DB. However, if a crawler that does not maintain history is re-run for just block number 100. It will now say the track name as "Humble".

(let me know if an example is required here)

What's the upside?

Easier development which still allows us to achieve our goal.

---

Open to hearing everyone's view on this.

[neatonk]

Thanks for outlining these trade-offs @il3ven. If you'd like to share the sql data model, I might be able to comment on that in some detail. I'm not as familiar with SQLite, but it looks like you can use the .schema command to dump create table statements for each table.

https://sqlite.org/cli.html#querying_the_database_schema

[il3ven]

I have written this migration file using knex. Does this work? I can try to dump the schema from SQLite if this doesn't work.

knex migration

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
export const up = async function (knex) {
  await knex.schema.createTable("tracks", (table) => {
    table.string("version").notNullable();
    table.string("title").notNullable();
    table.string("duration");

    table.string("artist_version").notNullable();
    table.string("artist_name").notNullable();
    table.string("artist_address").nullable();

    table.string("platform_name").index().notNullable();
    table.string("platform_version").notNullable();
    table.string("platform_uri").notNullable();

    table.string("erc721_version").notNullable();
    table.string("erc721_address").notNullable();
    table.json("erc721_metadata");

    table.integer("lastUpdatedAt").index().notNullable();
    table.string("uid");
    table.primary("uid");
  });

  await knex.schema.createTable("manifestations", (table) => {
    table.string("version").notNullable();
    table.string("uri").notNullable();
    table.string("mimetype").notNullable();

    table.string("uid");
    table.foreign(["uid"]).references(["uid"]).on("tracks");
    table.primary(["uid", "uri"]);
  });

  await knex.schema.createTable("tokens", (table) => {
    table.string("id").notNullable();
    table.string("uri").notNullable();
    table.json("metadata");

    table.string("uid");
    table.foreign(["uid"]).references(["uid"]).on("tracks");
    table.primary(["uid", "id"]);
  });

  await knex.schema.createTable("owners", (table) => {
    table.integer("blockNumber").notNullable();
    table.string("from").notNullable();
    table.string("to").notNullable();
    table.string("transactionHash").notNullable();
    table.string("alias");

    table.string("uid");
    table.string("id");

    table.foreign(["uid", "id"]).references(["uid", "id"]).on("tokens");
    table.primary(["uid", "id", "transactionHash", "to"]);
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
export const down = async function (knex) {
  await knex.schema.dropTable("owners");
  await knex.schema.dropTable("tokens");
  await knex.schema.dropTable("manifestations");
  await knex.schema.dropTable("tracks");
};

The above migration creates 4 tables: tracks, tokens, owners, and manifestations. The relation between them is described in the diagram at the top of the discussion. The migration is a representation of a Track. The schema for the Track can be found below.

Track schema in TS

interface Track {
  version: string;
  title: string;
  uid: string;
  duration?: string;
  artist: {
    version: string;
    name: string;
    address?: string;
    [k: string]: unknown;
  };
  platform: {
    version: string;
    name: string;
    uri: string;
    [k: string]: unknown;
  };
  erc721: {
    version: string;
    tokens: Token[];
    address: string;
    metadata?: ERC721Metadata;
    [k: string]: unknown;
  };
  manifestations: Manifestation[];
  [k: string]: unknown;
}

interface Artist {
  version: string;
  name: string;
  address?: string;
  [k: string]: unknown;
}

interface Platform {
  version: string;
  name: string;
  uri: string;
  [k: string]: unknown;
}
export interface Manifestation {
  version: string;
  uri: string;
  mimetype: string;
  [k: string]: unknown;
}

interface Owner {
  from: string;
  to: string;
  blockNumber: number;
  transactionHash: string;
  alias?: string;
  [k: string]: unknown;
}

type Token = {
  id: string;
  uri: string;
  metadata?: ERC721Metadata;
  owners: Owner[];
};

interface ERC721Metadata {
  name: string;
  description: string;
  image: string;
  [k: string]: unknown;
}

[il3ven]

Maintaining historical state using logs

In an open office call @neatonk mentioned that it would be good to maintain historical state so that other nodes could sync up-to a particular block number. (@neatonk correct if i understood your point incorrectly)

If we only maintain the latest state then syncing can still happen but it will always sync to the latest state. A consumer of neume can be thought of a node that only syncs and never crawls. So, from a consumer point of view syncing to the latest state should be good enough. However, I do agree that it will be good to sync up-to a particular block number.

We can maintain historical state by the help of logs.

How will log work?

Suppose we have two functions to manipulate our DB: newTrack and addOwner. Every time these functions are called we will record their usage in a log file. It will look like below.

newTrack called at block number 1000 with <some-data>
addOwner called at block number 1001 with <some-more-data>
newTrack called at block number 1050 with <some-new-data>

The SQL database will maintain only the latest state. If a node wants sync upto block number 1001, it will replay the first two logs from the above example.

What do you all think about this approach? Maintaining a SQL DB and a log file.

[neatonk]

I think this is a fine approach for now, but also maybe an unnecessary half-step. I see no issue with the direction you are going and appreciate that it serves the current needs (e.g. tracking the current state only in SQL). I think we can continue to discuss and plan to address longer term goals and needs without blocking your work here.

In an open office call @neatonk mentioned that it would be good to maintain historical state so that other nodes could sync up-to a particular block number. (@neatonk correct if i understood your point incorrectly)

My thinking on historical state is driven by determinism and reproducibility within a network. E.g. In the future, it should be provable that, for a given config, a new node will deterministically progress to the desired state given data provided by other nodes. This requires the node to be able to specify the data it needs as a starting point and have that be fulfilled in a deterministic and repeatable way.