Design tables

[lachlanglen]

• Determine resources/entities (e.g. Pots, Donations, Accounts etc)
• Determine fields & "relationships" for each entity

[Prometheo]

here is the first draft,
https://dbdiagram.io/d/Potlock-Schema-65e8f2a07570557c713e4f9b

[lachlanglen]

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

[Prometheo]

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]

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]

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]

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]

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]

Also will need PayoutsChallenges table, and CHALLENGE_PAYOUTS and UPDATE_PAYOUTS_CHALLENGE activities

[Prometheo]

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]

• 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]

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