tactical-docs/install-notes/015-initiales-postgresql-datenbankschema-und-migrationen.md

015 - Initiales PostgreSQL-Datenbankschema und Migrationen

Ziel

Für RUVNOX Tactical wurde die erste PostgreSQL-Datenbankmigration angelegt und erfolgreich auf CT101 angewendet.

Umgebung

• Tactical Server Container: RUVNOX-TACTICAL
• PostgreSQL Container: CT101 / PostgreSQL
• PostgreSQL IP: 192.168.178.4
• Datenbank: ruvnox_tactical
• Datenbank-User: ruvnox_tactical
• Deploy-Repository: /opt/ruvnox/tactical/deploy-repo

Neue Dateien im tactical-deploy Repository

database/migrations/001_initial_schema.sql

scripts/apply-db-migrations.sh

Angelegte Tabellen

Die Migration legt folgende Tabellen an:

• app_users
• devices
• auth_sessions
• operation_rooms
• operation_room_members
• messages
• audit_events
• schema_migrations

Migration 001_initial_schema.sql

Die Migration aktiviert pgcrypto:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

pgcrypto wird für UUID-Erzeugung über gen_random_uuid() verwendet.

app_users

Speichert Benutzerkonten.

Wichtige Felder:

• id
• username
• display_name
• role
• password_hash
• is_active
• created_at
• updated_at

Erlaubte Rollen:

• admin
• dispatcher
• operator
• viewer

devices

Speichert registrierte Endgeräte.

Wichtige Felder:

• id
• user_id
• device_name
• platform
• public_key
• last_seen_at
• is_active
• created_at
• updated_at

auth_sessions

Speichert Login- und Refresh-Sessions.

Wichtige Felder:

• id
• user_id
• device_id
• refresh_token_hash
• expires_at
• revoked_at
• created_at
• last_seen_at

operation_rooms

Speichert Einsatzräume.

Wichtige Felder:

• id
• code
• name
• description
• classification
• is_active
• created_by
• created_at
• updated_at

Erlaubte Klassifizierungen:

• public
• internal
• confidential
• restricted

operation_room_members

Speichert Raum-Mitgliedschaften.

Wichtige Felder:

• room_id
• user_id
• member_role
• joined_at
• left_at

Erlaubte Rollen:

• lead
• dispatcher
• member
• observer

messages

Speichert Nachrichten-Metadaten und serverseitig lesbare Einsatzraum-Nachrichten.

Wichtige Felder:

• id
• room_id
• sender_user_id
• sender_device_id
• message_type
• content_text
• metadata
• created_at
• deleted_at

Erlaubte Nachrichtentypen:

• text
• system
• status
• file

Hinweis: Diese Tabelle ist für Einsatzräume gedacht, bei denen der Server lesen und schreiben darf. Für echte 1:1-Ende-zu-Ende-verschlüsselte Nachrichten wird später eine separate Struktur ergänzt.

audit_events

Speichert sicherheits- und betriebsrelevante Ereignisse.

Wichtige Felder:

• id
• actor_user_id
• event_type
• entity_type
• entity_id
• ip_address
• user_agent
• details
• created_at

schema_migrations

Speichert angewendete Migrationen.

Wichtige Felder:

• version
• checksum
• applied_at

Damit erkennt das Migrationsscript bereits angewendete Migrationen und schützt vor nachträglich veränderten SQL-Dateien.

Trigger

Die Migration legt die Funktion set_updated_at() an.

Diese aktualisiert updated_at automatisch bei Updates.

Trigger wurden gesetzt auf:

• app_users
• devices
• operation_rooms

Indizes

Angelegt wurden Indizes für:

• devices.user_id
• auth_sessions.user_id
• auth_sessions.device_id
• operation_room_members.user_id
• messages.room_id und messages.created_at
• messages.sender_user_id
• audit_events.actor_user_id
• audit_events.created_at
• audit_events.entity_type und audit_events.entity_id

Migrationsscript

Das Script liegt unter:

scripts/apply-db-migrations.sh

Aufgaben:

1. Lädt /opt/ruvnox/tactical/config/server.env
2. Setzt PGPASSWORD aus DB_PASSWORD
3. Erstellt schema_migrations falls nötig
4. Prüft Checksums bereits angewendeter Migrationen
5. Wendet neue SQL-Migrationen transaktional an
6. Bricht bei Checksum-Abweichungen ab

Ausführung:

cd /opt/ruvnox/tactical/deploy-repo ./scripts/apply-db-migrations.sh

Verifikation

Tabellenprüfung:

psql -h 192.168.178.4 -p 5432 -U ruvnox_tactical -d ruvnox_tactical -c "\dt"

Ergebnis:

• app_users
• audit_events
• auth_sessions
• devices
• messages
• operation_room_members
• operation_rooms
• schema_migrations

Migrationstabelle:

select version, applied_at from schema_migrations order by applied_at;

Ergebnis:

001_initial_schema.sql wurde angewendet am 2026-06-14 06:41:04 UTC.

Git-Commit

tactical-deploy:

9e6d27e Add initial database schema migration

Abschlussstand

• Migration erfolgreich angewendet
• 8 Tabellen vorhanden
• schema_migrations aktiv
• Migration im tactical-deploy Repository gepusht
• SQL-Datei visuell geprüft und sauber

Nächster Abschnitt

Als nächstes folgt die serverseitige Datenbankzugriffsschicht im Ktor-Server.