Officers database refactor #195

Description

We used to have permissions based off officer terms, but there was no clean way to give permissions outside of that. Since there is usually a transition period and a constraint of there being only one of each role is going to be enforced later on, that also made the Officer term table unreliable. Furthermore, historical data is missing computing IDs for several entries, without a clear way to recover them. This makes the Officer term table requiring a Site User to exist not very practical, as they currently share either an empty or NA value in the database.

We also should differentiate Executives and Non-Executives/Representative terms since they operate with different rules and Executives can also hold Representative roles.

Tasks

• Constraints are things the database should enforce.
• Validators are things Pydantic models can enforce. These can match the database constraints, but some of them are unreasonable, like determining if something is unique.

Officer Info

• Contains information about every single Officer.

• Clean up phone numbers from historical data that seem incorrect (they're stored as int in the pre-rewrite database lmao).
• Fix github_username, discord_id, and google_drive_email to be NULL instead of an empty string on existing data.

Officer Position

• Contains every type of Officer that has ever existed.
• New table, ensures validation code doesn't drift from our defined positions.

• Remove the current StrEnum that define this.

Officer Position Version

• Contains the version of a position, to ensure historical data isn't affected by changing roles.

Constraints

• If category = executive, then multiple_allowed must not be NULL
• If category <> executive, then multiple_allowed must be NULL
• effective_to >= effective_from, when effective_to is not NULL
• Versions of the same Position must not have overlapping effective periods.

EXCLUDE USING gist (
    position_id WITH =,
    daterange(effective_from, effective_to, '[]') WITH &&
)

Endpoints

• GET /position: Returns a list of officer roles.
  • active: Truthy value that defaults to false. If true, returns the position versions where effective_version.effective_to IS NULL or has not passed yet and effective_from >= current date.
• POST /position: Create a new position and version.
• POST /position/<position_id>: Create a new version, and set the effective_to date on the old version to the supplied effective_from date, minus 1 day.
• PATCH /position/<version_id>: Set the effective_to date on a role.

Officer Term

Constraints

• (Check) end_date IS NULL OR end_date >= start_date.
• (Trigger) An Officer Term must fall entirely within the effective period of its referenced Position Version.
• (Trigger) An Officer cannot have overlapping Officer Terms where both Position Versions are categorized as executive.
• (Trigger) If a Position Version is categorized as executive and has multiple_allowed = false, there cannot be another overlapping Officer Term referencing a version of the same underlying Position.

Endpoints

• Remove GET /officer/all.
• Remove GET /officer/current.
• Add GET /officer endpoint.
  • current: Optional, defaults to false. If true, only retrieve officers with term that has started and hasn't ended, if false returns all officers.

Officer Term Data

• Contains information we don't really collect anymore and is missing on a lot of historical data.

Other changes

• The first migration will need

CREATE EXTENSION IF NOT EXISTS btree_gist;

• Endpoint /officers/all on the production database returns 500 #159 : this should be fixed once the data has been cleaned and the changes implemented.

Tests

Tests should be updated, as indicated in #165.