CSSS Blog backend

[EarthenSky]

We wanted a blog, so we need to add support for it.

I imagine we'll want to store everything in a database table, then dynamically serve content based on the page. We'll also have a page for searching through

This task will have several steps:

• Create a table in the database to store blog entries
  • this table should store:
    • html contents as a string
    • date of creation & date of last edit
    • computing id of creator (this is what we use as a foreign key to access user accounts)
    • (unique) title of article
    • string containing a list of comma separated tags
  • create crud functions for:
    • creating new blog entries
    • getting a blog entry using it's title
    • getting a set of blog titles by date range & tag (paginated, say 50 per page for starters)
    • updating blog data.
• Create an endpoint api.sfucsss.org/blog/<title> for generating a directly servable webpage given the (unique) title of the article
  • TODO: @micahdbak how should this be integrated with the frontend? What are your thoughts? I'd prefer to serve the files directly rather than use js to dynamically build the webpage.
• Migrate all past "announcements" from https://sfucsss.org/ to the database
• Update the nginx config so that the backend endpoint is being served directly
  • also @micahdbak do we want to do this?
• Add support for fuzzy keyword search of contents (!)

[EarthenSky]

As always, if you need help with anything or with clarifications, feel free to ask me anything!!

[gunmack]

1. For storing computing IDs:
   Should the data type be:
   - Text
   - Varchar(n)

2. For storing html content as a string:
   - Assuming the content of a post would generally be large, is it okay to use TOAST compression method?

3. For storing tags:
   - In case the list of tags is large, can TOAST be used?

[EarthenSky]

1. For storing computing IDs

You can find the size used in the existing database tables here => we use Varchar(16). It's a little overkill just in case the size increases in the future, but SFU claims there is a max length of 9 chars (this might include non-ascii wide chars, so buffer space is good to have).

2. For storing html content as a string

For the first version, we want a quick & easy proof of concept (like an MVP, you know?). No need to make something optimal before profiling / evaluating it first! Once the simple version works good, we can upgrade to using something like TOAST. I've never heard of it before, but if you think it's easy to implement, go for it!

That being said, I'd love to learn more about how TOAST works, because it looks like psql does it automatically? => "large field values are compressed and/or broken up into multiple physical rows ... This happens transparently to the user ... The technique is affectionately known as TOAST"

3. For storing tags

Before we consider things like TOAST, please take a look at how authentication is implemented in our stack using alembic & sqlalchemy. I know you're interested in psql, but we might be working at a higher level for the time being so we might not get to do super cool things ; _ ;

That being said, i doubt we'll have more than 8kb of text for tags, so no TOAST if it's not automatic. Though, my assumption was that having an entry with a large amount of text would only reduce retrieval performance. Do you think this 8kb limit might truncate content stored?

// Aside:

I actually hadn't thought about the performance implications of large contents, we should be careful to limit the number & size of tags to a reasonable amount so a bad actor can't be evil and add hundreds of thousands of tags hahaha. Though, that's for a future issue I suppose...

[EarthenSky]

Heyo @gunmack!

Despite that you've been working on the frontend (also super awesome!), how's the progress with this issue?