Event database table update

[jbriones1]

Description

We currently have no way of assigning a poster to an event. The events will live on this web server for now, but may change later (no need to worry about this).

Features

• Ability to add an image filename to an event, either through adding a new column, OR adding a new table that will reference the main event table. Can discuss the behaviour further in the comments, but I think adding a new table will be better.
• The new column will indicate the filename, with some way to generate the full path based on the event series/event structure. Structure of the path will be constants or environment variables held on the web server.
• The images need to be supported in the existing code, so updating/creating an event or event occurrence needs to be handled.
• If a second table is added which defines every single event in a series, we can either pre-compute these entries and store them in that table, but we need to handle it a different way if they're infinitely recurring. We could also just make infinitely recurring events impossible.
• Create an endpoint that can fetch all events in an event series.

Problems

• If we pre-compute, how do we handle exceptions? For example, if an event runs every 2 weeks, but one event is missed, should we delete the event or have something that tracks exceptions?

[jbriones1]

@arunPdl02 What do you think about the event and event_occurrence table relationship? From what I can see we have two options: two events tables or rework the existing one.

Option 1

Right now, I'm thinking every single poster for every event occurrence could be unique, but we can have a fallback, which is defined in event. So event could have an extra column appended, image_file or something. This would be something auto-generated, like a hash or UUID, so we could ensure they're unique. We could also have it be nullable and then let the frontend decide what to do with null values.

Then in event_occurrence, these would be the actual events that occur. So instead of pulling from events we instead run CRUD operations over event_occurrence.

Option 2

We change the events table and remove the frequency information. When we receive an events create operation and it specifies its recurring, we generate every single occurrence and the primary key would become a unique hash or string, which would become the file name for the event. This would remove the need for an event_occurrence table and we wouldn't need to add a filename column to the table, but we'd need the event ID to be a bit more random to prevent people iterating through our images.

---

Both of these options require the filenames/event IDs generating without / and . to prevent path traversal issues. There would need to be a sanitization step to remove these characters from being placed as a filename or being fetched through the API.

Lmk if you think of any other designs or see any other security risks.