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

Database schemas

See #156 (comment)

Event Endpoints

I think most of the endpoints are fine for event_info, but GET /events should also include the image_url of the event.

I would add some way to perform CRUD operations on a group of events.

1. POST /event/group
   Creates one or more events under the same group key.
   Body: Array of EventCreate

Response:

{
  "group_id": string;
  "events": Event[]
}

2. PATCH /event/group/{group_id}
   Updates one more more events under the same group.

{
  "group_id": string;
  "events": Event[]
}

3. DELETE /event/group/{group_id}
   Updates one more more events under the same group. Responses should follow the same patterns as before.

[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.

[jbriones1]

I've been thinking about this schema, but I wanted to see if you two could figure out if my thinking is wrong. The first schema I sent to @arunPdl02 was ill-thought out, so I've taken my time thinking of this one. I'd like both your opinions on this. I think we'll make 2 tables, one we need immediately and one we should make now which will make our lives easier later (image assets).

Event Info Table

Named event_info.

Schema

Anything without an explicit "nullable" is required.

• eid (int, autoincrement, primary_key)
• description (text)
• name (text) : the title for the event
• start_datetime (timestamptz): the time and date the event starts, requires timezone
• end_datetime (timestamptz): the time and date the event ends, requires timezone
• group_id (uuid, nullable): indicates this event is part of a group, so they can all be updated at once.
• location (text, nullable): either the room number (ASB 9971), full address (8888 University Drive, Burnaby, BC, V5A 1S6).
• organizer (text, nullable): the main group organizing this e.g. CSSS, SFU, Faculty of Applied Sciences, a club, etc.
• status (int): 0 for cancelled, 1 for scheduled. We might need to have a table to store a mapping of this to what they mean, but for now we can document it. Another option is storing this as text in the database, but mapped to a string enum in Pydantic, similar to the site_user_role table's role.
• image_id (int, fk, nullable): the ID to the poster of this event.

Table Constraints

• end_datetime >= start_datetime: they can be equal in case we have an event that just sort of starts but doesn't have an end.
• (if status is text) status IN ('scheduled', 'cancelled'): need to ensure we don't store unknown values here.

Indexes

• group_id: so we can performs operations by group_id more efficiently.

Reasons for frequency removal

• The CSSS rarely plan events past 2 semesters, so infinitely recurring events don't happen.
• Our recurring events always have breaks at the end of the semester and/or change dates (board game nights, meetings).
• Our big recurring events (Tech Fair, Mountain Madness, Fall Hacks, FROSH week, Silicon Valley Trip) don't have clear repeatable dates, so a new event would have to be created anyway.

Image Asset Table (I'll handle this one)

Named image_asset, this is flexible enough to be used in-app or from a media server.

Schema

• image_id (int, primary_key): just for database referencing.
• storage_key (text, unique): the path to the image stored on the media server e.g. if the image is stored at https://sfucsss.org/media/picture.png storage_key would be picture.png.