search

Giveth / giveth-dapps-v2

This project is the aggregation of GIVeconomy and Giveth.io DApps in a single repo
https://staging.giveth.io
GNU General Public License v3.0
61 stars 34 forks source link

Design database table for project verification schema #711

Closed mohammadranjbarz closed 2 years ago

mohammadranjbarz commented 2 years ago

The schema design based on https://docs.google.com/presentation/d/1P_vUtporlEqtu_hY5aIVrySskdM-__rSfGObUwIlhJI/edit#slide=id.g125fd5dd422_0_108

Entity Schemas

Basic fields

Personal info

It can use the project and project owner data, no need for additional fields ( just may need adding verify email that it would call another resolver to verify email, so it's not related to ProjectVerification entity

Social Profiles

It can be another table with these schema

Project Registry

Project Contact

Milestones

Managing Funds

Diagram

aminlatifi commented 2 years ago

Thanks @mohammadranjbarz, it's great. Just a few things:

mohammadranjbarz commented 2 years ago

Thanks @mohammadranjbarz, it's great. Just a few things:

  • The Primary Address is defined in the project entity and we don't need it here
  • I suppose SocialProfiles entity is the same OAuth connections. It should have a Access Token probably which implies is verified or not. Also, I think fields of projectVerificationId or ProjectId are not needed there, having a relation table would be enough.
  1. @aminlatifi @MoeNick So we assume primaryAddress is a relatedAddress that have a isPrimary:true ?
  2. We don't need to have accessToken because we don't want to call social networks , we need something in verify with OAUTH2 but at the end knowing that is verified is enough
  3. Do you mean when a user verified a social profile he/she can use it for other project verification requests without verifying again (In this case we would have Many to Many relations between socialProfile and projectVerification) ? I assume everytime they want to add a social profile to a project verification request they have to verify it by OAU2 again
aminlatifi commented 2 years ago

Thanks @mohammadranjbarz, it's great. Just a few things:

  • The Primary Address is defined in the project entity and we don't need it here
  • I suppose SocialProfiles entity is the same OAuth connections. It should have a Access Token probably which implies is verified or not. Also, I think fields of projectVerificationId or ProjectId are not needed there, having a relation table would be enough.
  1. @aminlatifi @MoeNick So we assume primaryAddress is a relatedAddress that have a isPrimary:true ?
  2. We don't need to have accessToken because we don't want to call social networks , we need something in verify with OAUTH2 but at the end knowing that is verified is enough
  3. Do you mean when a user verified a social profile he/she can use it for other project verification requests without verifying again (In this case we would have Many to Many relations between socialProfile and projectVerification) ? I assume everytime they want to add a social profile to a project verification request they have to verify it by OAU2 again
  1. Yeah, but we don't need it in ProjectVerification entity. The relation should be saved in Project entity and be filled in Create Project scenario, not verify.
  2. You are right, we need it once. Just a point, backend should verify the access token itself and not just rely on value obtained by api
  3. It's a misunderstanding. I don't mean to reuse them. But you are right, you don't need to change your design. Relation between ProjectVerification and connected account can be established by projectId and projectVerificationId. It plays ManyToOne relation role.
mohammadranjbarz commented 2 years ago

Thanks @mohammadranjbarz, it's great. Just a few things:

  • The Primary Address is defined in the project entity and we don't need it here
  • I suppose SocialProfiles entity is the same OAuth connections. It should have a Access Token probably which implies is verified or not. Also, I think fields of projectVerificationId or ProjectId are not needed there, having a relation table would be enough.
  1. @aminlatifi @MoeNick So we assume primaryAddress is a relatedAddress that have a isPrimary:true ?
  2. We don't need to have accessToken because we don't want to call social networks , we need something in verify with OAUTH2 but at the end knowing that is verified is enough
  3. Do you mean when a user verified a social profile he/she can use it for other project verification requests without verifying again (In this case we would have Many to Many relations between socialProfile and projectVerification) ? I assume everytime they want to add a social profile to a project verification request they have to verify it by OAU2 again
  1. Yeah, but we don't need it in ProjectVerification entity. The relation should be saved in Project entity and be filled in Create Project scenario, not verify.
  2. You are right, we need it once. Just a point, backend should verify the access token itself and not just rely on value obtained by api
  3. It's a misunderstanding. I don't mean to reuse them. But you are right, you don't need to change your design. Relation between ProjectVerification and connected account can be established by projectId and projectVerificationId. It plays ManyToOne relation role.

Nice, I changed the diagrams and edited my comment https://github.com/Giveth/giveth-dapps-v2/issues/711#issuecomment-1128435255

CarlosQ96 commented 2 years ago

I was thinking how we can handle project verification rejections and the current step queries (in case he closes the browser). We could have a projectVerificationForm Entity like I mentioned in issue: https://github.com/Giveth/giveth-dapps-v2/issues/713

Basically relate it all the info the form needs

ProjectVerificationForm

CarlosQ96 commented 2 years ago

When the project verification form is completed, the jsonb column data is deleted or maybe moved to a historic. What do you think @mohammadranjbarz @aminlatifi?

CarlosQ96 commented 2 years ago

Aside from that I think it's good! Great Job Mohammad, if we go with the projectVerificationForm entity we could put the Term & Conditions field isTermAndConditionAccepted field there.

CarlosQ96 commented 2 years ago

Wait maybe I was confused (text vs diagram), the entities will be separated? or we have them all in 1 entity called projectVerification?? (with validators handle all).

Either approach works for me I think. I have done all in one entity with validators or separate it into models. I think it's easier to implement in one entity.

So I would add in that case: To the ProjectVerification entity the currentStep and the jsonB column for rejectedReasons.

Sorry for rambling a lot.

mohammadranjbarz commented 2 years ago

@CarlosQ96 Thank you , I had not worked with JSONB so I it didn't come to my mind, your suggestion was great, using JSONB instead of using flat fields is more readable ( I feel like it's Mongo DB haahaa) and for the entities, diagram is what I meant, 3 tables for this purpose

mohammadranjbarz commented 2 years ago

@CarlosQ96 I changed the diagrams and reflect your suggestions there, thank you

The schema design based on https://docs.google.com/presentation/d/1P_vUtporlEqtu_hY5aIVrySskdM-__rSfGObUwIlhJI/edit#slide=id.g125fd5dd422_0_108

Entity Schemas

Basic fields

Personal info

It can use the project and project owner data, no need for additional fields ( just may need adding verify email that it would call another resolver to verify email, so it's not related to ProjectVerification entity

Social Profiles

It can be another table with these schema

Project Registry

We fill all fields in a field named projectRegistry as JSONB and after request got confirmed we clone those data in below fields in project registry

Project Contacts

We fill all fields in a field named projectContact as JSONB and after request got confirmed we clone those data in below fields in projectContacts

Milestones

We fill all fields in a field named milestones as JSONB and after request got confirmed we clone those data in below fields in milestones

Managing Funds

We fill all fields in a field named managingFunds as JSONB and after request got confirmed we clone those data in below fields in managingFunds We store related addresses as an array in JSONB but after verifying we will create related addresses based on that and relate them to project

Diagram

MoeNick commented 2 years ago

@WhyldWanderer jfyi.