Database Schema

[0x4007]

Background

In a future vision, we will have a Chrome Extension which will inject related information on top of the GitHub UI such as your incentives (see the incentives section) and your "level" which recognizes your skills for ranking up through our Bounty System.

We need to keep track of this information somewhere, so this is why we must think through our database schema.

Progress

I have a database set up on supabase because its free, cloud hosted, has a great developer experience and has a built in RESTful API. For better or for worse (I think for better - because of performance and scaling!) it is a SQL style database.

We scraped the GitHubs of Ethereum hackathon participants and stored their information there. The intent is to associate those leads with developer relations managers so that whenever the leads solve a bounty, our bounty bot will automatically recognize and pay the devrel that recruited them. This is one of our many incentives in our system.

I am not an expert in database schema design, but we should saddle up on a final design once the v1 spec of the Bounty System is complete (looks like we're getting pretty close based on #1.)

[0x4007]

GitHub User Public Profile Schema

I prefixed column names with an underscore if the public profile property name matches what a property name returned by the GitHub User API. The data might be rendered differently on the public profile. A good example is _followers (public profile) vs followers (user API)

_followers will have humanized integers e.g. "1.3k" whereas followers would be e.g. "1305"

I wanted to make sure that there would not be property name collisions once we also include API queries. It was just easier for me to start by setting up public profile scraping.

Perhaps its smarter to have a table for public profiles, and then a separate table for User API profiles; and then associate those by using login as the key? Their username (login) should be available in both datasets.

Name	Description	Data Type	Format
created_at	No description	timestamp with time zone	timestamptz
login	GitHub Username	text	text
type	No description	text	text
name	No description	text	text
company	No description	text	text
blog	No description	text	text
location	No description	text	text
email	No description	text	text
bio	No description	text	text
twitter_username	No description	text	text
_public_repos	No description	text	text
_followers	No description	text	text
_following	No description	text	text
_created_at	No description	text	text
contributions	No description	text	text
percent_commits	No description	text	text
percent_issues	No description	text	text
percent_pull_requests	No description	text	text
percent_code_reviews	No description	text	text
updated_at	No description	timestamp with time zone	timestamptz
recruited_by	The Ubiquity Developer Relations Manager	text	text

[0x4007]

The public profile schema is determined by this code

https://github.com/ubiquity/scraper/blob/03bdf8aae22602e107ed15437c4d0b7dc1abfccf/src/pages/github.com/*/index.ts#L66-L96

[0x4007]

Ubiquity Bounty System Metadata Schema

Name	Description	Data Type	Format
github_username	No description	text	text
created_at	No description	timestamp with time zone	timestamptz
updated_at	No description	timestamp with time zone	timestamptz
recruited_by	No description	text	text
wallet_address	No description	character	bpchar
ens	ENS name which should take precedence over wallet_address	text	text

[0x4007]

Ideally we should use the ens name over an address (so that users can update what address the ens is pointing to.) However, not everybody uses ens which is why I allowed for both.

[Steveantor]

We might consider MongoDB Atlas (which has a free tier like Supabase as well)
I've worked with MongoDB before, and it's more performant/scalable than SQL.