Prep database schema for supporting multiple wallets per user/org

[wgwz]

Building off of the following work:

• https://github.com/regen-network/regen-registry/issues/885
• https://github.com/regen-network/regen-registry/issues/914

And this earlier research work:

• https://github.com/regen-network/regen-registry/issues/859#issuecomment-1078416974

I want to adjust the party <> wallet schema to be a one-to-many relationship. Additionally, I want to write a function that can be used for adding a new user/org and a wallet to the database. I also want to write a function that can be used for adding a new wallet address to an already existing user/org in the database. These are two obviously required functions in the keplr login design, and accomplishing this would be a good step forward. An implementation should consider whether these functions will be accessed via API endpoints or if somehow graphql could be used.

Note: Part of this task is to answer this question, can organizations have multiple wallets? Because a few weeks ago, I thought there was a reason organizations would not be able to have wallets. But I've lost track of my line of thinking, and now I don't see why I thought that was a necessary constraint. I'll write down a concrete answer to that question here.

[wgwz]

Hey team! Please add your planning poker estimate with ZenHub @blushi @mhagel

[wgwz]

I just want to start with a definition first. So let's define the term "registry account":

A registry account is an account in the registry, which can aggregate one or more unique regen addresses. These accounts can have one of two types; a "user-type" or an "organization-type". The uniqueness matters here because we must be able to log a user in to a single registry account. A user will create one of these two-types of accounts, they will be able to sign in to the registry with this account and they had to create this account with a keplr wallet. At a later time, they can add additional regen addresses to this account. Adding additional addresses to their account has two effects; they can sign in this registry account with any of the addresses they added, and they can toggle between these addresses to view their registry data on a per address basis.

I think that my confusion boils down to this.

Do we want a user-type registry account to be able to switch/toggle the profile drop-down to switch into an address that also belongs to an organization-type account?

If the answer is yes, I'm having trouble seeing how we would make this work on a technical level.

As far as I can tell, a user-type registry account can only switch between regen addresses that are associated to it (which again must unique). In this model, there is no way that a single regen address can be associated to more than one registry account. Otherwise, we would effectively be authenticating a user into two different accounts.

I'm just looking for clarification on these points, and help re-framing how I am thinking about this (if required). Apologies if I'm over-complicating this.

c.c. @blushi , @erikalogie , @S4mmyb , and @clevinson

[blushi]

A registry account is an account in the registry, which can aggregate one or more unique regen addresses. These accounts can have one of two types; a "user-type" or an "organization-type". The uniqueness matters here because we must be able to log a user in to a single registry account. A user will create one of these two-types of accounts, they will be able to sign in to the registry with this account and they had to create this account with a keplr wallet. At a later time, they can add additional regen addresses to this account. Adding additional addresses to their account has two effects; they can sign in this registry account with any of the addresses they added, and they can toggle between these addresses to view their registry data on a per address basis.

A registry account doesn't have a type on its own, it's rather the associated regen addresses that have a type (individual or org), per latest design from @erikalogie

[wgwz]

OK, I think I see it now, thanks @blushi this helps a lot.

[wgwz]

I wanted to share rough draft notes that I've taken, these are my plans for next steps so far. (Based on this design)

new fields that need to be on the address table:

  - name => i.e. default "Unnamed", or "Mary Smith"
  - wallet_type => this will be either "organization" or "user" (this
    determines the icon shown on profile page, might want to change
    the name of this field to profile_type)
  - profile_image

functions to be created (an implicit assumption is that before any of these functions can be called, is that the user's signed transaction was verified):

1. getting user id by regen address:
  - high-level user actions:
    - login
  - given:
    - a regen address
  - is the address already associated to a user?
    - if yes, return the corresponding user id
    - if no, create a new user account, attach the address and return the user
      id

2. fetching associated addresses:
  - high-level user actions:
    - login
  - given:
    - the user id
  - return all associated addresses

3. check if a user can add an address:
  - high-level user actions:
    - adding an address
    - signing up
  - given:
    - the user id (maybe)
    - the regen address to check
  - if the user id is not given, is the address associated with any account?
    - if no, create a new user and association to the regen address.
    - if yes, specify that the address is already being used by another
      account.
  - otherwise, is the address already associated with the given user id?
    - if yes, specify that the address is already associated to their account,
      do nothing
    - if no, is the address associated to a different user id?
      - if yes, specify that the "remove & connect" process needs to happen 
      - if no, connect the address to the user

4. remove an address from user:
  - high-level user actions:
    - remove & connect address
  - given:
    - the user id
    - the regen address
  - remove the association between the corresponding user and address

5. connect an address to a user
  - high-level user actions:
    - adding an address
  - given:
    - the user id
    - the regen address
    - the wallet_type (profile type) (either user or org) 
  - add the association between the corresponding user and address

6. update fields for a given profile:
  - high-level user actions:
    - update a profile
  - given:
    - the user id
    - the regen address
  - the following fields must be editable:
    - wallet_type (profile type)
    - name
    - profile_image

[wgwz]

Remaining goal for this task is to settle the database design questions that have come up in the PR review here:

https://github.com/regen-network/registry-server/pull/187

My last comment regarding new functions has been split into two separate tasks for the next sprint:

• https://github.com/regen-network/regen-registry/issues/944
• https://github.com/regen-network/regen-registry/issues/945

I'm considering those two tasks as blocked by resolution of the database design (no point in writing database functions for tables and relationships that are yet decided on).