# Database Overview

{% @mermaid/diagram content="erDiagram
gps\_points {
BIGSERIAL id PK
UUID trekkie\_run FK "trekkie\_runs(id)"
TIMESTAMP timestamp
DOUBLE lat
DOUBLE lon
DOUBLE elevation         "optional"
DOUBLE accuracy          "optional"
DOUBLE vertical\_accuracy "optional"
DOUBLE bearing           "optional"
DOUBLE speed             "optional"
}

```
r09_telegrams {
    BIGSERIAL id PK
    TIMESTAMP time
    UUID station FK "stations(id)"
    BIGINT r09_type
    INT delay              "optional"
    INT reporting_point
    INT junction
    SMALLINT direction
    SMALLINT request_status
    SMALLINT priority           "optional"
    SMALLINT direction_request  "optional"
    INT line               "optional"
    INT run_number         "optional"
    INT destination_number "optional"
    INT train_length       "optional"
    INT vehicle_number     "optional"
    SMALLINT operator           "optional"
    BIGINT region FK "regions(id)"
}

r09_transmission_locations {
    BIGSERIAL id PK
    BIGINT region FK "regions(id)"
    INT reporting_point
    DOUBLE lat
    DOUBLE lon
    BOOLEAN ground_truth
}

r09_transmission_locations_raw {
    BIGSERIAL id PK
    BIGINT region FK "regions(id)"
    INT reporting_point
    DOUBLE lat
    DOUBLE lon
    UUID trekkie_run FK "trekkie_runs(id)"
    UUID run_owner FK "users(id)"
}

raw_telegrams {
    BIGSERIAL id PK
    TIMESTAMP time
    UUID station FK "stations(id)"
    BIGINT telegram_type
    BYTEA data
}

regions {
    BIGSERIAL id PK
    TEXT name
    TEXT transport_company
    TEXT regional_company   "optional"
    BIGINT frequency          "optional"
    BIGINT r09_type           "optional"
    INT encoding           "optional"
    BOOLEAN deactivated
    FLOAT lat
    FLOAT lon
    FLOAT zoom
    FLOAT work_in_progress
}

region_statistics {
    BIGINT id PK "regions(id)"
    TIMESTAMP last_updated
    BIGINT total_telegrams
    BIGINT month_telegrams
    BIGINT week_telegrams
    BIGINT day_telegrams
    BIGINT total_gps
    BIGINT month_gps
    BIGINT week_gps
    BIGINT day_gps
}

stations {
    UUID id PK
    VARCHAR(36) token                 "optional"
    TEXT name
    DOUBLE lat
    DOUBLE lon
    BIGSERIAL region FK "regions(id)"
    UUID owner FK "users(id)"
    BOOLEAN approved
    BOOLEAN deactivated
    BOOLEAN public
    INT radio                    "optional"
    INT architecture             "optional"
    INT device                   "optional"
    DOUBLE elevation              "optional"
    INT antenna                  "optional"
    TEXT telegram_decoder_version "optional"
    TEXT notes                    "optional"
    UUID organization FK "organizations(id)"
}

station_statistics {
    UUID id PK "stations(id)"
    TIMESTAMP last_updated
    BIGINT total_telegrams
    BIGINT month_telegrams
    BIGINT week_telegrams
    BIGINT day_telegrams
}

trekkie_runs {
    TIMESTAMP start_time
    TIMESTAMP end_time
    INT line
    INT run
    BIGSERIAL region FK "regions(id)"
    UUID owner FK "users(id)"
    BOOLEAN finished
    UUID id PK
    BOOLEAN correlated
}

users {
    UUID id PK
    TEXT name          "optional"
    TEXT email         "optional"
    VARCHAR(100) password
    INT email_setting "optional"
    BOOLEAN deactivated
    BOOLEAN admin
}

user_statistics {
    UUID id PK "users(id)"
    TIMESTAMP last_updated
    BIGINT total_gps
    BIGINT month_gps
    BIGINT week_gps
    BIGINT day_gps
}

organizations {
    UUID id PK
    TEXT name
    BOOLEAN public
    UUID owner FK "users(id)"
    BOOLEAN deactivated
}

org_users_relations {
    UUID id PK
    UUID organization FK "organizations(id)"
    UUID user_id FK "users(id)"
    INT role
}
```

r09\_transmission\_locations }|--|| regions : "has"
region\_statistics ||--o| regions : "statistics"
r09\_telegrams }|--|| regions : "received in"
r09\_transmission\_locations\_raw }|--|| regions : ""
stations }|--|| regions : "contains"

gps\_points }|--|| trekkie\_runs : "contains"
trekkie\_runs }|--|| users : "from"

r09\_transmission\_locations\_raw }|--|| users : ""
trekkie\_runs }|--|| regions : "in"
r09\_transmission\_locations\_raw }|--|| trekkie\_runs : "contains"

r09\_telegrams }|--|| stations : "received"
raw\_telegrams }|--|| stations : "received"
stations }|--|| organizations: "belongs"
stations }|--|| users : "owns"
organizations }|--|| users : "owns"
org\_users\_relations }|--|| users : "has role"
org\_users\_relations }|--|| organizations : "associated key"
station\_statistics ||--o| stations : "statistics"
user\_statistics ||--o| users : "statistics"
" fullWidth="true" %}