Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Design tables #1

Open
lachlanglen opened this issue Mar 6, 2024 · 24 comments
Open

Design tables #1

lachlanglen opened this issue Mar 6, 2024 · 24 comments

Comments

@lachlanglen
Copy link

  • Determine resources/entities (e.g. Pots, Donations, Accounts etc)
  • Determine fields & "relationships" for each entity
@lachlanglen lachlanglen moved this to Backlog in PotLock Engineering Mar 6, 2024
@Prometheo Prometheo moved this from Backlog to In progress in PotLock Engineering Mar 6, 2024
@Prometheo
Copy link
Collaborator

@lachlanglen
Copy link
Author

lachlanglen commented Mar 6, 2024

Thanks @Prometheo, are you able to give me edit access? Either this GH account or my email lachlan@banyan.gg if possible

@Prometheo
Copy link
Collaborator

Unfortunately no, the tool is a free version, doesn't allow collabs. what we could probably do is you make the change in the widget?

@Prometheo
Copy link
Collaborator

so, do you think some of the config fields in the Pots table be removed?
also, quick question, can a project apply to more than one pot?

@lachlanglen
Copy link
Author

Ok no worries, here are some notes:

  • Let's add optional pot_id to Donation, since a Donation could represent either a direct donation or through a pot
  • Add Activities table which represents an activity, e.g. say we want to display an activity feed (global, by pot, by user etc)
    • should have an activity_type field, as well as signer_id, receiver_id (this would be the contract that is called), timestamp_ms, possibly others that are generic to all activities and maybe some that are only relevant to certain activities, e.g. donation_recipient for DONATE
    • think of all possible activity types; these might include REGISTER, UPDATE_REGISTRY_STATUS, UPDATE_REGISTRY_CONFIG, DONATE, DEPLOY_POT, UPDATE_POT_CONFIG, PROCESS_PAYOUTS
  • Combine Projects and Users into single Accounts table, which also should probably contain some NEAR Social data. We might want to index all NEAR Social profile-related data so it's synthesized into a single Account record. Open to hearing your thoughts on this.

We do want to add some totals, similarly to how you have noted e.g. totals raised for an account, but have to think about how we will handle non-native tokens. An amalgamated total of all $NEAR and $NEKO isn't very useful, for instance. We might want to store as a JSON-stringified object of FT -> total for each token.

Additionally, we need to think about USD totals. I heard that it's not possible to make fetch requests inside QueryAPI, which is kind of annoying, but just emailed the PM to verify that this is actually the case as it seems crazy. We would probably want to store an optional USD amount on each Donation and a total_raised_usd or similar for Accounts. We can access all this data on a free coingecko plan, but we would be throttled when backfilling data which would be an issue. Lots to think about.

@lachlanglen
Copy link
Author

so, do you think some of the config fields in the Pots table be removed? also, quick question, can a project apply to more than one pot?

Possibly, some might be better stored in a JSON string/blob but others we will probably want to keep at the top level so they can be queried on, e.g. timestamps

Yes, a project (aka Account) can be in more than one pot

@lachlanglen
Copy link
Author

lachlanglen commented Mar 7, 2024

Additional tables we will need:

  • Applications
  • Lists (this will be v2 of what is currently the registry contract, where the PotLock list - what is currently the PotLock registry - will be just one of many lists contained within the contract, each of which will have owner, admins & statuses)
  • Registrations (essentially an Account/List entity)
  • Nadabot tables: Stamps, Providers (I think it makes sense to bring Nadabot into this as well as the Potlock <> Nadabot relationship is only going to get closer)

@lachlanglen
Copy link
Author

Also will need PayoutsChallenges table, and CHALLENGE_PAYOUTS and UPDATE_PAYOUTS_CHALLENGE activities

@Prometheo
Copy link
Collaborator

We do want to add some totals, similarly to how you have noted e.g. totals raised for an account, but have to think about how we will handle non-native tokens. An amalgamated total of all $NEAR and $NEKO isn't very useful, for instance. We might want to store as a JSON-stringified object of FT -> total for each token.

Since donations entry exist for each donation, and the table has a amount_in_usd field, total raised for an account would just be a summation of all the amount_in_usd fields in all it's donations, something like sum(account.donations, amount_in_usd) graphql could have something to carry out this summation.
i'm also thinking to use the SQL Trigger function, so that the accounts table has a total_amount_usd and on every donation insert, it's automatically updated(by adding the latest donation amount)

@Prometheo
Copy link
Collaborator

  • which also should probably contain some NEAR Social data. We might want to index all NEAR Social profile-related data so it's synthesized into a single Account record. Open to hearing your thoughts on this.

i don't quite get the social data thing, plus it's going to be hard to index, since it's not coming with any of the interaction to potlock contracts, except we'll make request to social data api, which i think can be done like on demand pn the front end, since we would have thew user id..

@Prometheo
Copy link
Collaborator

also is there a reason why, events weren't really used in the contract?

@lachlanglen
Copy link
Author

We do want to add some totals, similarly to how you have noted e.g. totals raised for an account, but have to think about how we will handle non-native tokens. An amalgamated total of all $NEAR and $NEKO isn't very useful, for instance. We might want to store as a JSON-stringified object of FT -> total for each token.

Since donations entry exist for each donation, and the table has a amount_in_usd field, total raised for an account would just be a summation of all the amount_in_usd fields in all it's donations, something like sum(account.donations, amount_in_usd) graphql could have something to carry out this summation. i'm also thinking to use the SQL Trigger function, so that the accounts table has a total_amount_usd and on every donation insert, it's automatically updated(by adding the latest donation amount)

I think the second option is a better idea, the first would get very expensive and potentially slow it down a lot

@lachlanglen
Copy link
Author

  • which also should probably contain some NEAR Social data. We might want to index all NEAR Social profile-related data so it's synthesized into a single Account record. Open to hearing your thoughts on this.

i don't quite get the social data thing, plus it's going to be hard to index, since it's not coming with any of the interaction to potlock contracts, except we'll make request to social data api, which i think can be done like on demand pn the front end, since we would have thew user id..

Ok, let's postpone this and discuss again after the MVP is out

@lachlanglen
Copy link
Author

lachlanglen commented Mar 12, 2024

also is there a reason why, events weren't really used in the contract?

yeah unfortunately that was overlooked. there is a donation event in the Donation contract but as you note there aren't really events in the Pot contract other than config updates.

This isn't great but unfortunately we have to live with it for now and use method calls & results instead for activities that aren't covered by events. I'm sorry :(

I understand this sucks quite a lot. We can definitely add more events to the contracts. But we will also have to handle the data up until that point by checking method calls.

@lachlanglen
Copy link
Author

Man I'm really kicking myself about not adding more events. That was a stupid oversight.

@lachlanglen
Copy link
Author

@Prometheo I did a second draft of the schema here: https://dbdiagram.io/d/Copy-of-Potlock-Schema-65f08e68b1f3d4062cbf9b09

Check it out and lmk if you have any thoughts. Indexes aren't included but we should add those.

@Prometheo
Copy link
Collaborator

@Prometheo I did a second draft of the schema here: https://dbdiagram.io/d/Copy-of-Potlock-Schema-65f08e68b1f3d4062cbf9b09

Check it out and lmk if you have any thoughts. Indexes aren't included but we should add those.

Hey @lachlanglen , so maybe i don't get it yet, but should list_applications not be tied to the list, instead of a user/registrant?
Table list_registration { id SERIAL [primary key] registrant_id VARCHAR [ref: > account.id, not null] status ENUM('Pending','Approved','Rejected','Graylisted','Blacklisted') [not null] submitted_at TIMESTAMP [not null] updated_at TIMESTAMP [null] registrant_notes TEXT [null] admin_notes TEXT [null] }
if a user can be related to many lists, then the relationship to a list_registration should go through the particular list, right? so we can do like, user.list.list_reg instead of two separate, user.list and user.list_reg.
Hope this makes sense...

@lachlanglen
Copy link
Author

@Prometheo I did a second draft of the schema here: https://dbdiagram.io/d/Copy-of-Potlock-Schema-65f08e68b1f3d4062cbf9b09
Check it out and lmk if you have any thoughts. Indexes aren't included but we should add those.

Hey @lachlanglen , so maybe i don't get it yet, but should list_applications not be tied to the list, instead of a user/registrant? Table list_registration { id SERIAL [primary key] registrant_id VARCHAR [ref: > account.id, not null] status ENUM('Pending','Approved','Rejected','Graylisted','Blacklisted') [not null] submitted_at TIMESTAMP [not null] updated_at TIMESTAMP [null] registrant_notes TEXT [null] admin_notes TEXT [null] } if a user can be related to many lists, then the relationship to a list_registration should go through the particular list, right? so we can do like, user.list.list_reg instead of two separate, user.list and user.list_reg. Hope this makes sense...

Yes it was definitely missing list_id. Updated:

Table list_registration {
  id SERIAL [primary key]
  list_id INT [ref: > list.id, not null]
  registrant_id VARCHAR [ref: > account.id, not null]
  status ENUM('Pending','Approved','Rejected','Graylisted','Blacklisted') [not null]
  submitted_at TIMESTAMP [not null]
  updated_at TIMESTAMP [null]
  registrant_notes TEXT [null]
  admin_notes TEXT [null]
}

Is this what you're saying? Or perhaps I'm not understanding, in which case feel free to clarify further

@Prometheo
Copy link
Collaborator

essentially, yes. But since the table now carries the list_id also having the registrant_id, seems redundant to me.

@lachlanglen
Copy link
Author

essentially, yes. But since the table now carries the list_id also having the registrant_id, seems redundant to me.

I don't understand. Otherwise, how would we know the account that is registered for this list_registration?

An account can be related to a list in several different ways, as the owner, or an admin, or a registrant on the list.

Maybe I'm missing something.

@Prometheo
Copy link
Collaborator

An account can be related to a list in several different ways, as the owner, or an admin, or a registrant on the list.

This explains it, so it's possible that a list owner is not the registrant?

@lachlanglen
Copy link
Author

lachlanglen commented Mar 16, 2024 via email

@lachlanglen
Copy link
Author

@Prometheo will also need tx_hash column on Donation and Activity tables and possibly others, e.g. Application, Registration etc

@Prometheo
Copy link
Collaborator

@Prometheo will also need tx_hash column on Donation and Activity tables and possibly others, e.g. Application, Registration etc

How did i even miss that! 🤦

@lachlanglen lachlanglen moved this from In progress to Done in PotLock Engineering Apr 15, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
Status: Done
Development

No branches or pull requests

2 participants