# Graduation

## Tables

There exist three different tables to hold all graduation information of a student.
One global submission table called `Submit` writable by all students and two individual ones per student.
The separation is needed for the different required permissions to access the stored data.
All individual tables use the following naming scheme `${StudentId}${Purpose}`.

The section containing the student's tables is only available to teachers and the student.

### Submit Table

The submit table is writable by all students and is used to track observation requests from students.
It is configured to require approval to publish entries.
This ensures that entries are only readable by the submitter and the teachers.
Entries are simply never approved.

### Public Table

The public table tracks all graduation changes known by the student.
The included information are sufficient to render a student's profile.

Changes can be submitted by any teacher.
Each change is valid until a subsequent change renders it obsolete.

Changes include points and level changes.

### Shadow Table

The shadow table tracks **all** graduation changes for a student.

Changes can be submitted by any teacher.
Each change is valid until a subsequent change renders it obsolete.

## Graduation Changes

Graduation changes are unique and the systems ensures that no duplicates are entered.

Changes can be active or archived.
The state of a particular change is derived from subsequent changes.

Currently multiple changes have to be retrieved to determine their state.
This decision allows us to write data once and not modify changes in the future.
The assumption is that a single person does not acquire that much changes, that this would be a performance issue.

### Database format

Note that the database is a Moodle database activity and thus does not allow access using SQL.
The below scheme uses a SQL like syntax.

```sql
TABLE Change(
	Date: Date,
	Coach: String,
	Json: STRING,
)
```

The author of the change and the creation time are tracked by Moodle.

### Data format

Each stored change is represented as JSON and has the following fields:
(Fields marked by `-` are considered private and not stored in the public table.)

* `change` - the type of the change
* `-reason` - the type of the change
* `context` - arbitrary data used to handle the type of change

### Changes

Valid changes are:

* `Grün` - sign for wanted behavior
* `Orange` - sign for unwanted behavior
* `Rot` - sign for unacceptable behavior
* `ObservationRequest` - the student requested an observation
* `ObservationStart` - the student's observation started
* `ObservationEnd` - the student's observation ended
* `LevelChange` - the student's level changed
* `Observation` - an observation of the student

#### Green Mark

Green marks are collectables.

#### Orange Mark

Orange marks highlight the need for a pedagogical session and are rendered archived by any subsequent `Gespräch` change.

#### Red Mark

Red Marks track unacceptable behavior and are only archived by consequence explicitly doing so with a `Änderung` change.

#### Obseration

An observation consists of a set of answers to thefollowing questions:

1. "Arbeitet konzentriert an Aufgaben"
2. "Arbeitet zunehmend selbstständig"
3. "Führt das Logbuch zuverlässig"
4. "Bringt Material mit"
5. "Hält Regeln ein"
6. "Geht verantwortungsvoll mit Materialien und LernRaum um"
7. "Nimmt Rückmeldungen an und setzt sie um"

The answer set is ordered and each answer is one of the three values `"yes"|"no"|""`.