Migrate Planet Feed to Supabase db

[joelazwar]

What would you like to be added:
Part of the Supabase implementation, we need to move the list of feeds from the Seneca CDOT wiki page to new table(s)

We need to figure out the schema to store and relate the tables.

Note: Duc has made a PR regarding this "Handle Telescope user registration and login", but we plan to split the PR into smaller manageable parts.

Why would you like this to be added:

To further progress implementing Supabase to Telescope

[humphd]

The code in https://github.com/Seneca-CDOT/telescope/tree/master/tools/migrate can help with this.

In our discussions today, we said that we'd untangle the idea of a user from feeds, so that we can create feeds in our database, but not necessarily associate them with a user. This could be done via an optional foreign key value that links to a user; or we could use a third table to link users <-> user_feeds <-> feeds. It wasn't clear which way to go, but the group seemed to lean toward making the ownership field for a feed optional.

[JerryHue]

For the sake of simplicity, I suggest we go with the option that @DukeManh proposed: an optional field.

For this to work, we need to keep two facts:

• feed url
• author name

The feeds table already has a column to store the URL, so where does the author name go?

I suggest we violate some rules of database design for the sake of simplicity and add a field that stores the author name (call it author_name). The author name refers to the name stored in the planet feed list. However, on the application, we will choose by default the author_name UNLESS there is an user_id key defined, which means we have to use the information from the telescope_profiles table.

[humphd]

I like this. We would end up with:

Column	Notes
url	Unique, primary key for a feed
author_name	Optional value from planet feed wiki, unused for future records
telescope_user_id	Optional foreign key to a Telescope user who owns the feed

We still need to add some more data here. Our concept of a Feed is a bit more complex than just having url and author: https://github.com/Seneca-CDOT/telescope/blob/master/src/backend/data/feed.js#L27-L44

For example, we still need:

• link - the URL of the actual thing the feed refers to. This might be a blog URL (vs. a feed URL), a YouTube channel, a Twitch channel, etc.
• etag - the etag value last reported by the server. We use this for caching. It can continue to live in Redis I think (e.g., we don't need it in the db)
• lastModified - the last-modified value last reported by the server. We use this for caching. It can also go in Redis and not the db.

Then we have some other things to consider:

• invalid: when a feed is no longer valid (e.g., we can't get it to download after many tries), we need to flag it so we don't keep trying. This probably belongs in the db (it currently lives only in Redis)
• delayed: when a we hit a feed too often, the server will sometimes tell us to wait before we try again. This can live in Redis (where it currently is).
• flagged: sometimes we need to flag a feed and take it out of Telescope temporarily. For example, if a student blogs something offensive. We could flag a feed, or a user, or both, and this probably belongs in the db (it's currently in Redis).

I'm not sure what else I'm missing, but we'll have to deal with all of this in various PRs.

[JerryHue]

link - the URL of the actual thing the feed refers to. This might be a blog URL (vs. a feed URL), a YouTube channel, a Twitch channel, etc.

For this, we may name it as the "source" of the feed. We can create a table called sources, so every feed has a connection to a source. The reason I do it like this is because there are some blogs that can have several feeds depending on the category (Wordpress comes to mind). This also offers some flexibility. After all, there is no rule that says that one blog belongs to a single feed.

• flagged: sometimes we need to flag a feed and take it out of Telescope temporarily. For example, if a student blogs something offensive. We could flag a feed, or a user, or both, and this probably belongs in the db (it's currently in Redis).

The flagged flag can be applied for both, although one to only flag a single feed and the other to flag an entire user from appearing (or deactivating other stuff, I am not sure). For example, in the case of the user's dev.to account getting hacked, you may just want to flag the feed that got compromised, and not the user itself.

• invalid: when a feed is no longer valid (e.g., we can't get it to download after many tries), we need to flag it so we don't keep trying. This probably belongs in the db (it currently lives only in Redis)

I agree. There can be a simple invalid flag column here, and it would do the job just fine.

[DukeManh]

I also want to inform that Postgres tables can contain Array type and JSON type and we probably don't have to create many tables that reference each other.