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?

[lachlanglen]

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]

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

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]

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

[lachlanglen]

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

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

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

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

[lachlanglen]

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]

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]

Yes exactly, the list registrants are the accounts that are on the list.On Mar 15, 2024, at 5:23 PM, Prometheus @.***> wrote:

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?

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: @.***>

[lachlanglen]

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

[Prometheo]

@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! 🤦