Deploymentcenter/sql/migrations/019_webauthn_and_roadmap_board.sql

-- Migration 019: Hardwarekey an der WebUI-Anmeldung, Notfalltoken, Roadmap-Tafel
--
-- Additive Migration. Der Migrator toleriert 1050/1060/1061/1062/1091 ("existiert bereits").

-- ---------------------------------------------------------------------------
-- 1. Registrierte Hardwarekeys (WebAuthn/FIDO2)
-- ---------------------------------------------------------------------------
-- Ein Konto darf mehrere Keys haben, und das ist keine Bequemlichkeit: Wer nur
-- einen Schluessel registriert, hat beim Verlust genau einen Weg zurueck - den
-- Notfalltoken. Ein zweiter Key im Schrank ist der billigere Ersatzweg.
--
-- credential_id kommt roh vom Authenticator (bis 1023 Byte erlaubt, in der
-- Praxis 16-128). VARBINARY(255) deckt jeden real vorkommenden Fall ab und
-- bleibt als eindeutiger Schluessel unterhalb der InnoDB-Grenze.
--
-- public_key ist das PEM der oeffentlichen Haelfte. Bewusst PEM und nicht das
-- rohe COSE: Geprueft wird spaeter mit openssl_verify(), und das will ein PEM.
-- Die Wandlung einmal bei der Registrierung zu bezahlen ist billiger, als sie
-- bei jeder Anmeldung zu wiederholen.
--
-- sign_count ist der Zaehler des Authenticators. Faellt er bei einer Anmeldung
-- nicht hoeher aus als der gespeicherte Wert, ist das der Hinweis auf einen
-- geklonten Schluessel. Yubikeys, die den Zaehler gar nicht fuehren, melden
-- konstant 0 - dieser Fall ist erlaubt und darf nicht als Klon gelten.
CREATE TABLE IF NOT EXISTS dc_user_credentials (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NOT NULL,
  credential_id VARBINARY(255) NOT NULL,
  public_key    TEXT NOT NULL,
  -- Das COSE-Verfahren (-7 = ES256, -257 = RS256). Liesse sich zwar aus dem
  -- PEM herleiten, steht aber hier: Das Pruefen soll nicht davon abhaengen,
  -- dass das Herleiten richtig geraten hat.
  alg           INT NOT NULL DEFAULT -7,
  sign_count    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  aaguid        VARBINARY(16) NULL,
  transports    VARCHAR(128) NULL,
  label         VARCHAR(64) NOT NULL DEFAULT 'Hardwarekey',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at  DATETIME NULL,
  UNIQUE KEY ux_credential_id (credential_id),
  KEY ix_credential_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 2. Notfalltoken je Konto
-- ---------------------------------------------------------------------------
-- Nur der Hash liegt hier. Der Klartext wird bei der Erzeugung ein einziges
-- Mal angezeigt und danach nicht wieder - wer die Datenbank liest, haelt
-- deshalb keinen Zweitschluessel in der Hand.
--
-- recovery_used_at bleibt nach dem Gebrauch stehen, obwohl der Hash dabei
-- geleert wird. Ohne diese Spur stuende in der Oberflaeche nur "kein
-- Notfalltoken hinterlegt" - und das sieht genauso aus wie "es gab nie einen",
-- waehrend es in Wahrheit heisst: jemand hat ihn benutzt.
ALTER TABLE dc_users
  ADD COLUMN recovery_hash VARCHAR(255) NULL AFTER totp_secret;

ALTER TABLE dc_users
  ADD COLUMN recovery_created_at DATETIME NULL AFTER recovery_hash;

ALTER TABLE dc_users
  ADD COLUMN recovery_used_at DATETIME NULL AFTER recovery_created_at;

-- ---------------------------------------------------------------------------
-- 3. Phasen der Roadmap-Tafel
-- ---------------------------------------------------------------------------
-- Die Tafel schneidet die Vorhaben in Spalten entlang von roadmap_rank. Die
-- Grenzen duerfen nicht im Code stehen: Der Rang ist projektweit und frei
-- vergeben, und jedes Projekt benutzt andere Abstaende. Was fuer das
-- Deploymentcenter "Rang 5-40" ist, ist fuer Predictalytics eine einzige
-- Spalte.
--
-- Ist fuer ein Projekt nichts gepflegt, schneidet die Tafel selbst - an den
-- groessten Luecken der Rangfolge - und sagt in der Ansicht dazu, dass die
-- Grenzen geraten sind. Diese Tabelle ist der Weg, das Geratene zu ersetzen.
CREATE TABLE IF NOT EXISTS roadmap_phases (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  project_slug VARCHAR(64) NOT NULL,
  sort_order   INT NOT NULL DEFAULT 100,
  rank_from    INT NOT NULL,
  rank_to      INT NOT NULL,
  label        VARCHAR(80) NOT NULL,
  subtitle     VARCHAR(160) NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY ux_roadmap_phase (project_slug, sort_order),
  KEY ix_roadmap_phase_range (project_slug, rank_from, rank_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 4. Spuren der Tafel: Farbe und Hervorhebung je Themenbereich
-- ---------------------------------------------------------------------------
-- "Sicherheit faellt ins Auge" war in der Vorlage ein fest verdrahteter
-- Bereichsname. Das traegt genau ein Projekt weit: Andere Projekte nennen
-- ihren wichtigen Bereich anders. Deshalb ein Kennzeichen am Bereich statt
-- eines Namens im Code.
ALTER TABLE roadmap_epics
  ADD COLUMN color VARCHAR(16) NULL AFTER summary;

ALTER TABLE roadmap_epics
  ADD COLUMN highlight TINYINT(1) NOT NULL DEFAULT 0 AFTER color;