Skip to content

Database Schema (Current)

This diagram reflects the current database schema as of December 2024.

dsn~plantuml-database-model-current~1

«core»Userid: uint «PK»email: string «unique»first_name: stringlast_name: stringaddress: stringbirthday: datetimeactivated_at: datetimevalid_until: datetimeactivated: boolactivate_code: stringlanguage: stringcreated_at: datetimeupdated_at: datetimedeleted_at: datetime«core»Childid: uint «PK»external_id: string «unique»first_name: stringmiddle_names: stringlast_name: stringbirthday: datetimegroup_id: uint «FK»active: boolvalid_from: datetimevalid_until: datetimecreated_at: datetimeupdated_at: datetimedeleted_at: datetime«core»Locationid: uint «PK»external_id: string «unique»name: stringaddress: stringlead_id: uint «FK»lead_2nd_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetime«core»Groupid: uint «PK»external_id: string «unique»name: stringlocation_id: uint «FK»lead_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetime«core»LocationDeviceid: uint «PK»location_id: uint «FK»device_ident: stringcreated_at: datetimeupdated_at: datetimedeleted_at: datetime«auth»Roleid: uint «PK»name: stringcreated_at: datetimeupdated_at: datetimedeleted_at: datetime«auth»Passwdid: uint «PK»user_id: uint «FK»pass_hash: stringcreated_at: datetimeupdated_at: datetimedeleted_at: datetime«auth»Pinid: uint «PK»user_id: uint «FK»pin_hash: stringcreated_at: datetimeupdated_at: datetimedeleted_at: datetime«auth»Permissionsid: uint «PK»role_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetime«auth»APITokenid: uint «PK»name: stringtoken_hash: string «unique»ip_allowlist: textlast_used_at: datetimelast_used_ip: stringactive: boolcreated_by_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetimeuser_rolesuser_id: uint «FK»role_id: uint «FK»UserChilduser_id: uint «PK,FK»child_id: uint «PK,FK»relationship_role: stringvalid_from: datetimevalid_until: datetimecreated_at: datetimeupdated_at: datetimegroup_teachersgroup_id: uint «FK»user_id: uint «FK»group_childrengroup_id: uint «FK»child_id: uint «FK»«core»CareDayid: uint «PK»weekday: stringstart: stringend: stringlocation_id: uint «FK»group_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetimechildren_caredayschild_id: uint «FK»care_day_id: uint «FK»«core»AbsenceNotificationid: uint «PK»child_id: uint «FK»user_id: uint «FK»from_date: datetimeto_date: datetimeabsence_type: stringmessage: textacknowledged: boolacknowledged_by: uint «FK»acknowledged_at: datetimecreated_at: datetimeupdated_at: datetimedeleted_at: datetime«core»Statusid: uint «PK»status: stringcreated_at: datetimeupdated_at: datetimedeleted_at: datetime«messaging»ParentalLetterid: uint «PK»created_by_id: uint «FK»delegated_to_id: uint «FK»reviewer_id: uint «FK»review_status: stringreview_comments: textreviewed_at: datetimelast_edited_by_id: uint «FK»edited_at: datetimesubject: stringtext: textinteraction_type: stringdeadline: datetimelocation_id: uint «FK»group_id: uint «FK»draft: boolpublished_at: datetimevalid_until: datetimecreated_at: datetimeupdated_at: datetimedeleted_at: datetime«messaging»ParentalLetterReadid: uint «PK»letter_id: uint «FK»user_id: uint «FK»read_at: datetimenotified_at: datetimeanswer: textanswered_at: datetimecreated_at: datetimeupdated_at: datetimedeleted_at: datetime«messaging»Messageid: uint «PK»created_by_id: uint «FK»child_id: uint «FK»subject: stringtext: textinteraction_type: stringdeadline: datetimedraft: boolpublished_at: datetimevalid_until: datetimeedited_at: datetimelast_edited_by_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetimemessage_recipientsmessage_id: uint «FK»user_id: uint «FK»«messaging»MessageReadid: uint «PK»message_id: uint «FK»user_id: uint «FK»read_at: datetimeanswer: textanswered_at: datetimecreated_at: datetimeupdated_at: datetimedeleted_at: datetime«messaging»MessageTemplateid: uint «PK»created_by_id: uint «FK»name: stringsubject: stringtext: textinteraction_type: stringcreated_at: datetimeupdated_at: datetimedeleted_at: datetime«documents»Newsid: uint «PK»title: stringtext: textcreated_by_id: uint «FK»published_at: datetimevalid_until: datetimelocation_id: uint «FK»group_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetime«documents»NewsReadid: uint «PK»news_id: uint «FK»user_id: uint «FK»read_at: datetimecreated_at: datetimeupdated_at: datetimedeleted_at: datetime«documents»Documentid: uint «PK»title: stringpath: stringpublished_at: datetimevalid_until: datetimecreated_by_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetime«documents»BlackboardDocumentid: uint «PK»title: stringpath: stringpublished_at: datetimevalid_until: datetimecreated_by_id: uint «FK»created_at: datetimeupdated_at: datetimedeleted_at: datetime«staging»SyncPersonid: uint «PK»external_id: string «unique»vorname: stringnachname: stringgeburtstag: daterolle: stringcomments: textsynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncParentChildid: uint «PK»eltern_id: stringkind_id: stringvon: datebis: datesynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncBelegungid_internal: uint «PK»id_krp: stringid_ue3: stringkind_id: stringgruppen_id: stringtage_binaer: intanzahl_tage: intvon: datebis: datestatus: intcomments: textsome_id: stringsynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncEmployeeid: uint «PK»external_id: string «unique»name: stringvorname: stringnachname: stringqualifikation: stringvon: datebis: datesynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncLocationid: uint «PK»einrichtungs_id: string «unique»name: stringadresse: textreihenfolge: intg_von: dateg_bis: datesynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncGroupid: uint «PK»gruppen_id: string «unique»einrichtungs_id: stringe_art_id: stringreihenfolge: intname: stringoez: stringg_von: dateg_bis: datesynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncLocationLeadid: uint «PK»einrichtungs_id: stringmitarbeiter_id: stringstellvertreter_id: stringstellvertreter_anteil: intg_von: dateg_bis: datesynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncGroupLeadid: uint «PK»gruppen_id: stringmitarbeiter_id: stringg_von: dateg_bis: datesynced_at: datetimecreated_at: datetimeupdated_at: datetime«staging»SyncProcessingLogid: uint «PK»data_type: stringstarted_at: datetimecompleted_at: datetimestatus: stringrecords_total: intcreated: intupdated: intskipped: interrored: interror_details: texttriggered_by_id: uint «FK»containsbelongs_toassigned_toleadlead_2ndleadcreated_bysubmitted_byacknowledged_bycreated_bydelegated_toreviewerlast_edited_bycreated_bylast_edited_bycreated_bycreated_bycreated_bytriggered_by

Entity Categories

Category Color Description
Core White Main business entities (User, Child, Location, Group)
Auth Blue Authentication and authorization
Messaging Green Communication system (Letters, Messages)
Documents Gray Documents and News
Staging Yellow Intranet sync staging tables

Key Relationships

User-Child Relationships

  • Many-to-many via user_children join table
  • Includes relationship_role (mother, father, guardian, etc.)
  • Validity period with valid_from and valid_until

Organizational Hierarchy

Location
    └── Group (many)
           └── Child (many, via group_id)
           └── Teacher (many, via group_teachers)

Staff Assignments

  • group_teachers: Assigns employees to groups
  • Group.lead_id: Group leader assignment
  • Location.lead_id / lead_2nd_id: Location leadership

ExternalID Mapping

The following app tables have external_id for intranet sync: - Location.external_id ← SyncLocation.einrichtungs_id - Group.external_id ← SyncGroup.gruppen_id - Child.external_id ← SyncPerson.external_id

Staging Tables Data Flow

Intranet API                    Processing                    App Tables
─────────────────────────────────────────────────────────────────────────
SyncLocation         ──────────────────────────────▶         Location
SyncGroup            ──────────────────────────────▶         Group
SyncPerson (child)   ──────────────────────────────▶         Child
SyncBelegung         ──────────────────────────────▶         Child.group_id

SyncEmployee         ─┐
SyncPerson (parent)  ─┼── Used by Registration ──▶          User
SyncParentChild      ─┤                          ▶          UserChild
SyncLocationLead     ─┤                          ▶          Location.lead_id
SyncGroupLead        ─┘                          ▶          Group.lead_id

Changelog vs Original Schema

Added Tables

  • APIToken - Intranet API authentication
  • UserChild - Enhanced user-child relationship with metadata
  • AbsenceNotification - Parent absence notifications
  • MessageTemplate - Reusable message templates
  • All Sync* staging tables (9 tables)
  • SyncProcessingLog - Processing audit trail

Added Fields

  • Child.external_id - Intranet mapping
  • Group.external_id - Intranet mapping
  • Location.external_id - Intranet mapping
  • Child.valid_from, valid_until - Contract validity
  • User.language - Language preference
  • Various *_edited_by, *_edited_at audit fields

Renamed/Clarified

  • ChildUserMapping → UserChild (with additional fields)
  • GroupTeacher → group_teachers (GORM convention)
  • GroupChildren → group_children (GORM convention)

Needs: impl, utest, itest

Covers:

  • req~datenbank-modell~1