123 lines
8.8 KiB
Plaintext
123 lines
8.8 KiB
Plaintext
-- SPDX-FileCopyrightText: 2022-2024 Sarah Vaupel <sarah.vaupel@uniworx.de>, Gregor Kleen <gregor.kleen@ifi.lmu.de>, Sarah Vaupel <sarah.vaupel@ifi.lmu.de>, Steffen Jost <jost@tcs.ifi.lmu.de>, Steffen Jost <s.jost@fraport.de>
|
|
--
|
|
-- SPDX-License-Identifier: AGPL-3.0-or-later
|
|
|
|
-- The files in /models determine the database scheme.
|
|
-- The organisational split into several files has no operational effects.
|
|
-- White-space and case matters: Each SQL table is named in 1st column of this file
|
|
-- Indendent lower-case lines describe the SQL-columns of the table with name, type and options
|
|
-- Nullable columns have "Maybe" written after their type
|
|
-- Option "default=xyz" is only used for database migrations due to changes in the SQL-schema, also see Model.Migration
|
|
-- Indendent upper-case lines usually impose Uniqueness constraints for rows by some columns.
|
|
-- Each table will also have an column storing a unique numeric row key, unless there is a row Primary columnname
|
|
--
|
|
|
|
User json -- Each Uni2work user has a corresponding row in this table; created upon first login.
|
|
ident UserIdent -- Case-insensitive user-identifier. Defaults to "AVSID:dddddddd" if unknown
|
|
created UTCTime default=now()
|
|
passwordHash Text Maybe -- If specified, allows the user to login with credentials independently of external authentication
|
|
lastAuthentication UTCTime Maybe -- When did the user last authenticate?
|
|
lastLdapSynchronisation UTCTime Maybe
|
|
ldapPrimaryKey UserEduPersonPrincipalName Maybe -- Fraport Personnel Number or Email-Prefix for @fraport.de work here
|
|
tokensIssuedAfter UTCTime Maybe -- do not accept bearer tokens issued before this time (accept all tokens if null)
|
|
matrikelnummer UserMatriculation Maybe -- usually a number; AVS Personalnummer; nicht Fraport Personalnummer!
|
|
surname UserSurname -- Display user names always through 'nameWidget displayName surname'
|
|
firstName Text -- For export in tables, pre-split firstName from displayName
|
|
title Text Maybe -- For upcoming name customisation
|
|
displayName UserDisplayName
|
|
displayEmail UserEmail -- Case-insensitive eMail address, used for sending; leave empty for using auto-update CompanyEmail via UserCompany
|
|
email UserEmail -- Case-insensitive eMail address, used for identification and fallback for sending. Defaults to "AVSNO:dddddddd" if unknown
|
|
maxFavourites Int default=12 -- max number of non-manual entries in favourites bar (pruned only if below a set importance threshold)
|
|
maxFavouriteTerms Int default=2 -- max number of term-sections in favourites bar
|
|
theme Theme default='ThemeDefault' -- Color-theme of the frontend; user-defined
|
|
dateTimeFormat DateTimeFormat "default='%d %b %y %R'" -- preferred Date+Time display format for user; user-defined
|
|
dateFormat DateTimeFormat "default='%d %b %Y'" -- preferred Date-only display format for user; user-defined
|
|
timeFormat DateTimeFormat "default='%R'" -- preferred Time-only display format for user; user-defined
|
|
downloadFiles Bool default=false -- Should files be opened in browser or downloaded? (users often oblivious that their browser has a setting for this)
|
|
languages Languages Maybe -- Preferred language; user-defined
|
|
notificationSettings NotificationSettings "default='{}'::jsonb" -- Bit-array for which events email notifications are requested by user; user-defined; missing fields in json object will be parsed to default trigger
|
|
warningDays NominalDiffTime default=1209600 -- timedistance to pending deadlines for homepage infos
|
|
csvOptions CsvOptions "default='{}'::jsonb"
|
|
sex Sex Maybe -- currently ignored
|
|
birthday Day Maybe -- for better identification
|
|
showSex Bool default=false
|
|
telephone Text Maybe
|
|
mobile Text Maybe
|
|
companyPersonalNumber Text Maybe -- Company will become a new table, but if company=fraport, some information is received via LDAP
|
|
companyDepartment Text Maybe -- thus we store such information for ease of reference directly, if available
|
|
pinPassword Text Maybe -- used to encrypt pins within emails, defaults to cardno.version
|
|
postAddress StoredMarkup Maybe -- including company name, if any, but excluding username; leave empty for using auto-update CompanyPostAddress via UserCompany
|
|
postLastUpdate UTCTime Maybe -- record postal address updates
|
|
prefersPostal Bool default=false -- user prefers letters by post instead of email
|
|
examOfficeGetSynced Bool default=true -- whether synced status should be displayed for exam results by default
|
|
examOfficeGetLabels Bool default=true -- whether labels should be displayed for exam results by default
|
|
lastSync UTCTime Maybe -- When was the User data last synchronised with external sources?
|
|
UniqueAuthentication ident -- Column 'ident' can be used as a row-key in this table
|
|
UniqueEmail email -- Column 'email' can be used as a row-key in this table
|
|
deriving Show Eq Ord Generic -- Haskell-specific settings for runtime-value representing a row in memory
|
|
|
|
-- | User data fetched from external user sources, used for authentication and data queries
|
|
ExternalUser
|
|
user UserIdent
|
|
source AuthSourceIdent -- Identifier of the external source in the config
|
|
data Value "default='{}'::jsonb" -- Raw user data from external source -- TODO: maybe make Maybe, iff the source only ever responds with "success"?
|
|
lastSync UTCTime -- When was the external source last queried?
|
|
UniqueExternalUser user source -- At most one entry of this user per source
|
|
deriving Show Eq Ord Generic
|
|
|
|
UserFunction -- Administratively assigned functions (lecturer, admin, evaluation, ...)
|
|
user UserId
|
|
school SchoolId
|
|
function SchoolFunction
|
|
UniqueUserFunction user school function
|
|
deriving Generic
|
|
UserSystemFunction Show
|
|
user UserId
|
|
function SystemFunction -- Defined in Model.Types.User
|
|
manual Bool -- Inserted manually by Admin or automatic from LDAP
|
|
isOptOut Bool -- User has currently deactivate the role for themselves
|
|
UniqueUserSystemFunction user function
|
|
deriving Generic Show
|
|
UserExamOffice
|
|
user UserId
|
|
field StudyTermsId
|
|
UniqueUserExamOffice user field
|
|
deriving Generic Show
|
|
UserSchool -- Managed by users themselves, encodes "schools of interest"
|
|
user UserId
|
|
school SchoolId
|
|
isOptOut Bool -- true if this a marker, that the user manually deleted this entry; it should not be recreated automatically
|
|
UniqueUserSchool user school
|
|
deriving Generic Show
|
|
UserGroupMember
|
|
group UserGroupName
|
|
user UserId
|
|
primary Checkmark nullable
|
|
UniquePrimaryUserGroupMember group primary !force
|
|
UniqueUserGroupMember group user
|
|
deriving Generic Show
|
|
UserCompany
|
|
user UserId
|
|
company CompanyId OnDeleteCascade OnUpdateCascade
|
|
supervisor Bool default=false -- should this user be made supervisor for all _new_ users associated with this company?
|
|
supervisorReroute Bool default=false -- if supervisor is true, should this supervisor receive email for _new_ company users?
|
|
priority Int default=0 -- higher number, higher priority; default=1 for Haskell-Code
|
|
useCompanyAddress Bool default=true -- if true, CompanyPostalAddress and CompanyEmail are used if UserPostalAddress/UserDisplayEmail are Nothing, respects priority
|
|
reason Text Maybe -- miscellaneous note, e.g. Superior
|
|
UniqueUserCompany user company -- a user may belong to multiple companies, but to each one only once
|
|
deriving Generic Show
|
|
UserSupervisor
|
|
supervisor UserId -- multiple supervisor per trainee possible
|
|
user UserId
|
|
rerouteNotifications Bool -- User can be his own supervisor to receive notifications as well
|
|
company CompanyId Maybe OnDeleteCascade OnUpdateCascade -- this supervisor was company default supervisor at time of entry
|
|
reason Text Maybe -- miscellaneous reason, e.g. Winterservice supervisision
|
|
UniqueUserSupervisor supervisor user -- each supervisor/user combination is unique (same supervisor can superviser the same user only once)
|
|
deriving Generic Show
|
|
UserDay
|
|
user UserId OnDeleteCascade OnUpdateCascade
|
|
day Day
|
|
parkingToken Bool default=false
|
|
UniqueUserDay user day
|
|
deriving Generic Show
|