Database schema

Tables

• Users - All possible actors in the system.
• Invitations - The active and inactive current invitations.
• Event log - Aggregation of events conducted by the different parties in the system.

Users

Primary key - user_id.

• user_id - Identification for a specific user (UUID).
• first_name - User's first name.
• last_name - User's last name.
• user_role - Permission level for the user.
  • user - Regular users.
  • guard - Security guards operating the gate.
  • admin - Security moderators reponsible for the total security and the gatekeeping.
• cellular_number - User's cellular phone number (Should be restricted with Regex)
• email - User's email address (Should be restricted with Regex).
• creation_timestamp - The timestamp of the user creation.
• modify_timestamp - The timestamp of the user modification.
• is_active - Is the user currently active.

Invitations

Primary key - invitation_id.

• invitation_id - Identification for an active or inactive current invitation (UUID).
• user_id - The identification of the inviter (UUID).
• invitees_admitted - Number of guests already passed the gate.
• invitees_amount - Number of total invited guests.
• invitees_arrival_timestamp - The time and date when the guests are supposed to arrive.
• is_active - The status of the request.
• creation_timestamp - When was this entry created.
• modify_timestamp - When was the last action performed on this entry.
• comment_for_guard - Some free text for the guard, limited to a reasonable amount of characters.

Event log

Primary key - event_id.

• event_id - Identification for a past event. (UUID)
• event_timestamp - Time stamp of the event.
• event_type - Classification of the event occured, could be one of the following:
  • Invitation creation (created) - The user created an invitation.
  • Invitation modification (modified) - The user modified the invitation.
  • Guest entered (entered) - The guard actively approved a guest.
  • Guest cancelled (cancelled) - The guard fixes a mistake of wrongly indicated entry.
  • Guard shift start (guard_in) - The guard started a shift.
  • Guard shift end (guard_out) - The guard ended a shift.
• amount_before - Amount of X before the event (For example 5 guests invited) applicable for all except guard shift start\end.
• amount_after - mount of X after the event (For example 4 guests remaining) applicable for all except guard shift start\end.
• user_id - Who's invitation is this? - Correlates to the User's UUID, applicable for all except guard shift start\end.
• guard_id - The identification of the guard performed the action.
• invitation_id - The identification of the invite in question, applicable for all except guard shift start\end.

Source

Editor

Table users {
  user_id UUID [pk] 
  first_name varchar(255)
  last_name varchar(255)
  cellular_number varchar(20)
  email varchar(255)
  user_role varchar
  is_active boolean
  creation_timestamp timestamptz
  modify_timestamp timestamptz
}
Table invitations {
invitation_id UUID [pk]
user_id UUID
invitees_amount INT
invitees_admitted INT
invitees_arrival_timestamp TIMESTAMPTZ
is_active BOOLEAN
creation_timestamp TIMESTAMPTZ
modify_timestamp TIMESTAMPTZ
comment_for_guard TEXT
}
Table event_log {
event_id UUID
event_timestamp TIMESTAMPTZ
event_type varchar
amount_before INT
amount_after INT
user_id UUID
guard_id UUID
invitation_id UUID
}
Ref: invitations.user_id > users.user_id

Ref: event_log.user_id > users.user_id
Ref: event_log.guard_id > users.user_id
Ref: event_log.invitation_id > invitations.invitation_id