Supabase Design

Schemas

We used to use only the public schema for all our tables. However, we began separating our database into different schemas, where each schema encapsulates one app/functionality. It is not advisable to move existing tables into a new schema, as it is difficult to account for every line of code where the table is accessed. However, future tables should grouped into schemas that define a certain function/app.

Current schemas in use:

• public
• events: Event calendar
• podium: Judging portal

Other schemas are Supabase/Postgres internals and should not be accessed or modified under any circumstance.

public

For login

• user_invites: Used to invite users via email with pre-defined roles.

For participant information

• application_status: Enum for application status
• hacker_profiles: Unsure if actually used. Overlap with participants
• participants: Participant information, including wristband information. Rows are created when participant RSVPs to event (I think).
• roles: Enum for user roles
• user_profiles: User information, including name and email. Also includes application status.

For handling leaderboard

• bonus_point_status: Enum for bonus point status. Whether bonus point request has been accepted.
• bonus_points: List of bonus points events/tasks. Is manually updated before each hackathon.
• bonus_points_log: Mapping of user to bonus point event/task. To track the status of the user's bonus points. Used to show status in dashboard, but otherwise not used to actually track the bonus points or give out bonus points on the admin side.
• event_log: Tracks events that participant has checked-in into. Added by scanning in identity portal.
• events: List of events for the hackathon. Is manually updated before each hackathon. Used for identity portal and event_log but should migrate to using events.events instead.
• leaderboard: Tracks amount of points of each user. Manually reset before each hackathon. event_points column is automatically modified when participant checks in into an event. bonus_points column must be manually modified (we should write something that automates this). total_points column is always automatically computed.

For sponsor portal

• companies: Spnosor details
• company_saved_participants: List of participants saved by each company. Not currently used in the portal as the feature to save participants had been removed. (May be implemented again in the future)
• notes: Notes that sponsors write for a participant.
• sponsor_user_bridge_company: Links user to a company so that they see correct dashboard and participant information.
• target_graduations: Perhaps meant to be filter for graduation years for a company in participant database. Unused and can be deleted.
• target_majors: Perhaps meant to be filter for majors for a company in participant database. Unused and can be deleted.

For Discord bot (unused)

• discord_invites
• discord_profiles
• discord_tokens