Skip to content
ScottyLabs Docs

Schema Information

Users

The Users table stores information on users.

Schema Definition

struct User {
  id: i32,
  name: string,
  andrew_id: string,
  oidc_subject: string,
  created_at: DateTimeWithTimeZone
}

Field Descriptions

  • id: Primary Key. The id for the user. Autogenerated.
  • name: The (full) name of the user, as fetched from auth.
  • andrew_id: The andrew_id of the user, as fetched from auth.
  • oidc_subject: The OIDC subject of the user, as fetched from auth. This is for authentication, and not for the frontend.
  • created_at: The timestamp when the user was created (when the entry was created). Autogenerated.

Organizations

Schema Definition

struct Organization {
  id: i32,
  name: string,
  data: Json
}

Field Descriptions

  • id: Primary Key. The id for the organization. Autogenerated.
  • name: The name of the organization.
  • data: Other data related to the organization. See organization.data.

Organization Members

The OrganizationMembers table is a join table between the Organizations and Users table to describe the many-to-many relationship users are allowed to have with organizations. It also stores information on what the user is allowed to do in the organization.

Schema Definition

struct OrganizationMember {
  organization_id: i32,
  user_id: i32,
  user_role: string,
  joined_at: DateTimeWithTimeZone
}

Field Descriptions

  • organization_id: Primary Key. The id for an organization. References Organizations.
  • user_id: Primary Key. The id for the user in the organization. References Users.
  • user_role: The role of the user in the organization. Specifies their permissions in the organization and creating events.
  • joined_at: The timestamp when the user joined the organization (when the entry was created). Autogenerated.

Sessions

Schema Definition

struct Session {
  id: i32,
  join_code: String
  status: SessionStatus,
  created_by_user_id: i32
}

Field Descriptions

  • id: Primary Key. The id for the session. Autogenerated.
  • join_code: The join code for the session. Capital alphabet and digits, 6 characters.
  • status: The status of the session, can be “open”, “locked”, “closed”.
  • created_by_user_id: The id of the user who created the session. Reference Users.

User Sessions

Schema Definition

struct UserSession {
  id: i32,
  user_id: i32,
  session_id: i32,
  proxy: Option<string>,
  join_left: JoinLeft,
  timestamp: DateTimeWithTimeZone
}

Field Descriptions

  • id: Primary Key. Autogenerated.
  • user_id: Foreign Key. The id for the user in the session. References Users.
  • session_id: Foreign Key. The id for the session. References Sessions.
  • proxy: Nullable string. If set, stores the user id (as a string) of the participant this user is proxying for.
  • join_left: Whether the user joined or left the session. Can be “joined” or “left”.
  • timestamp: The timestamp when the user joined the session (when the entry was created).

Events

Schema Definition

struct Event {
  id: i32,
  event_type: EventType,
  name: String,
  status: StatusOption,
  start_time: DateTimeWithTimeZone,
  end_time: Option<DateTimeWithTimeZone>,
  data: Json,
  created_by_user_id: i32,
  session_id: i32,
}

Field Descriptions

  • id: Primary Key. The id for the event. Autogenerated.
  • event_type: The type of the event. Is an enum type. Can be “motion” or “election”.
  • name: The name for the event.
  • status: The status of the event. Is an enum type. Can be “active” or “inactive”.
  • start_time: The time when the event(voting) starts.
  • end_time: The time when the event(voting) ends, if ever.
  • data: The other data related to the event. See event.data
  • created_by_user_id. The id of the user who created the event. References Users.
  • session_id: The id for the session this event belongs to. References Sessions.

Votes

This table probably breaks a normalization rule or two, but that doesn’t really matter right now.

Schema Definition

struct Vote {
  id: i32,
  event_id: i32,
  user_session_id: i32,
  cast_time: DateTimeWithTimeZone,
  data: Json,
}

Field Descriptions

  • id: Primary Key. The id for the vote. Autogenerated.
  • event_id: Foreign Key. The event this vote belongs to. References Events.
  • user_session_id: Foreign Key. The user_session row representing the participant/proxy vote instance. References User Sessions.
  • cast_time: The time at which this vote was cast (when the entry was created). Autogenerated.
  • data: Other data pertaining to the vote. See vote.data

Votes are unique per (event_id, user_session_id).