A lookup table can be used instead of adding a new column for every service to the identity table

[shahthepro]

At present the identity table schema contains a column for every supported attestation type (like Google, Facebook, etc..).

This won't scale well in the long run. It'd be better if we can add a lookup table and store the verified identities in that table.

[shahthepro]

@franckc Any thoughts on this?

I'm thinking of adding a verified_accounts table with columns: eth_address(foreign key), provider(enum of all providers) and verified (boolean). And updated_at and created_at if necessary.

If bridge and identity packages share a database in all environments, We can just check if an entry exists in attestation table in bridge package.

[franckc]

What are exactly the "scaling" concerns with having a flat identity table ? Is it DB read/write performance ? Something else ?

A few things:

• Yes, all our back-end services currently share the same database. So all tables are accessible from any service
• Adding a "verified_accounts" table to store attestations would work. If we go this route we'll also have to backfill the legacy data into that table.
• Though I see a few downsides:
  • It would add a little bit more complexity for writing and reading the identity data in our source code
  • Also would make analytics queries slightly more complicated. The business team uses quite heavily metabase to get stats. It is a SQL replica of the prod data so it has exactly the same schema.

Overall, I'm not super concerned about having a flat identity table with a dozen or so attestation columns. Adding kakao, wechat, linkedin, Instagram, Youtube should be totally find. Now, if we have good reasons to think we'll need to store lot more than that then would be worth considering doing this data migration.

[shahthepro]

@franckc

What are exactly the "scaling" concerns with having a flat identity table ? Is it DB read/write performance ? Something else ?

AFAIK, Adding more number of columns can affect the read performance, unless we are using a columnar database. Also, if we normalize the table, we may be able to index and space-partition it, so that we can get better read performance.

Also, there is no direct way to delete a specific migration. So if we decide to remove an attestation type in future, we may be running into a few issues.

However, as you said, with the number of the columns we have right now, it may be unnecessary to worry about all these right now. 😄

Now, if we have good reasons to think we'll need to store lot more than that then would be worth considering doing this data migration.

@micahalcorn may be able to tell us if we plan on adding more attestations in future.

[franckc]

Re read performance I think that would be premature optimization, especially given our scale (both in terms of read volume and number of rows) is minuscule :)

Re ability to remove an attestation type in the future, we should be able to write a migration script that just deletes the associated column.