syncova-backup/migrations/000006_verification.up.sql

-- Pruefung und Recovery Assurance (Phase 10).
--
-- Das Exit-Kriterium der Phase lautet: Ein Backup laesst sich objektiv
-- einstufen. Dafuer braucht es zwei Dinge in der Datenbank:
--
--   1. Eine **Historie der Pruefungen**. Ohne sie waere jede Aussage ueber die
--      Wiederherstellbarkeit eine Momentaufnahme ohne Herkunft.
--   2. Die **Einstufung am Backup selbst**, damit eine Uebersicht sie zeigen
--      kann, ohne fuer jede Zeile die Pruefhistorie zu durchsuchen.
--
-- Der wichtigste Grundsatz steckt in den Spaltentypen: Alles Ungemessene ist
-- NULL, niemals ein Nullwert. „Nie geprueft" und „vor einer Sekunde geprueft"
-- duerfen sich nicht gleich anfuehlen.

-- ---------------------------------------------------------------------------
-- Pruefauftraege
-- ---------------------------------------------------------------------------

CREATE TABLE verification_jobs (
    -- id ist der oeffentliche Bezeichner.
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- backup_id ist das gepruefte Backup.
    backup_id UUID NOT NULL REFERENCES backups(id) ON DELETE CASCADE,
    -- verification_type benennt die Art der Pruefung.
    --
    -- Die Arten unterscheiden sich in Aussagekraft und Kosten erheblich:
    -- manifest ist in Millisekunden erledigt und sagt fast nichts,
    -- restore_test dauert und ist der einzige echte Nachweis.
    verification_type TEXT NOT NULL,
    -- status ist der Zustand des Auftrags.
    status TEXT NOT NULL DEFAULT 'queued',
    -- result ist das Ergebnis: clean, corrupted, incomplete oder failed.
    result TEXT,
    -- report haelt den vollstaendigen Pruefbericht als JSON.
    --
    -- Der Bericht bleibt erhalten, nicht nur sein Ergebnis: Wer spaeter fragt,
    -- warum ein Backup als beschaedigt gilt, braucht die betroffenen Objekte.
    report JSONB,
    -- chunks_checked ist die Zahl gepruefter Bloecke.
    chunks_checked BIGINT NOT NULL DEFAULT 0,
    -- chunks_missing ist die Zahl fehlender Bloecke.
    chunks_missing BIGINT NOT NULL DEFAULT 0,
    -- chunks_corrupted ist die Zahl beschaedigter Bloecke.
    chunks_corrupted BIGINT NOT NULL DEFAULT 0,
    -- bytes_read ist die gelesene Datenmenge.
    bytes_read BIGINT NOT NULL DEFAULT 0,
    -- started_at ist der Beginn in UTC.
    started_at TIMESTAMPTZ,
    -- completed_at ist das Ende in UTC.
    completed_at TIMESTAMPTZ,
    -- error_message ist die verstaendliche Fehlermeldung.
    error_message TEXT,
    -- correlation_id verbindet den Auftrag mit seinen Protokollzeilen.
    correlation_id UUID NOT NULL,
    -- scheduler_instance benennt den ausfuehrenden Control-Server.
    scheduler_instance TEXT,
    -- heartbeat_at ist die letzte Lebendmeldung in UTC.
    heartbeat_at TIMESTAMPTZ,
    -- created_by benennt den Anfordernden; NULL bei automatischer Pruefung.
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    -- created_at ist der Anlagezeitpunkt in UTC.
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT verification_jobs_type_valid
        CHECK (verification_type IN ('manifest', 'chunk_presence', 'chunk_integrity', 'chain', 'restore_test')),
    CONSTRAINT verification_jobs_status_valid
        CHECK (status IN ('queued', 'running', 'completed', 'failed', 'cancelled')),
    CONSTRAINT verification_jobs_result_valid
        CHECK (result IS NULL OR result IN ('clean', 'corrupted', 'incomplete', 'failed')),
    -- Ein abgeschlossener Auftrag ohne Endzeitpunkt waere in jeder Auswertung
    -- eine Luecke.
    CONSTRAINT verification_jobs_completion_consistent CHECK (
        status IN ('queued', 'running') OR completed_at IS NOT NULL
    ),
    -- Ein abgeschlossener Auftrag ohne Ergebnis waere eine Pruefung, die nichts
    -- feststellte — dann haette sie nicht stattgefunden.
    CONSTRAINT verification_jobs_completed_has_result CHECK (
        status <> 'completed' OR result IS NOT NULL
    ),
    -- Beschaedigte oder fehlende Bloecke sind niemals ein sauberes Ergebnis.
    CONSTRAINT verification_jobs_clean_means_clean CHECK (
        result <> 'clean' OR (chunks_missing = 0 AND chunks_corrupted = 0)
    )
);

CREATE INDEX verification_jobs_backup_idx ON verification_jobs (backup_id, completed_at DESC);
CREATE INDEX verification_jobs_status_idx ON verification_jobs (status) WHERE status IN ('queued', 'running');
CREATE INDEX verification_jobs_created_idx ON verification_jobs (created_at DESC);

-- Zwei gleichzeitige Pruefungen desselben Backups lesen dieselben Daten
-- doppelt, ohne mehr festzustellen.
CREATE UNIQUE INDEX verification_jobs_single_active_idx
    ON verification_jobs (backup_id)
    WHERE status IN ('queued', 'running');

-- ---------------------------------------------------------------------------
-- Einstufung am Backup
-- ---------------------------------------------------------------------------

-- Die Einstufung steht am Backup, damit eine Uebersicht sie zeigen kann, ohne
-- fuer jede Zeile die Pruefhistorie zu durchsuchen. Sie ist ein Ergebnis der
-- Pruefungen, keine eigene Wahrheit — und wird ausschliesslich von ihnen
-- fortgeschrieben.
ALTER TABLE backups
    -- classification ist die objektive Einstufung (Exit-Kriterium §12).
    ADD COLUMN classification TEXT,
    -- last_verified_at ist die letzte erfolgreiche Integritaetspruefung in UTC.
    --
    -- NULL bedeutet: nie geprueft. Der Unterschied zu einem Nullwert ist der
    -- Kern der ehrlichen Bewertung.
    ADD COLUMN last_verified_at TIMESTAMPTZ,
    -- last_restore_test_at ist der letzte bestandene Wiederherstellungstest in UTC.
    ADD COLUMN last_restore_test_at TIMESTAMPTZ,
    -- last_restore_test_duration_seconds ist die gemessene Dauer eines Tests.
    --
    -- Sie ist die einzige belastbare Grundlage fuer die Aussage, ob eine
    -- Wiederherstellungsdauer eingehalten wird. Ohne sie bleibt die Vorgabe
    -- eine Absichtserklaerung.
    ADD COLUMN last_restore_test_duration_seconds DOUBLE PRECISION,
    -- assurance_score ist die zuletzt berechnete Bewertung in Prozent.
    ADD COLUMN assurance_score INTEGER,
    -- assurance_unknown_inputs ist die Zahl ungemessener Eingangsgroessen.
    --
    -- Sie steht neben der Bewertung, damit niemand eine Prozentzahl fuer
    -- belastbar haelt, die auf lauter Unbekanntem beruht.
    ADD COLUMN assurance_unknown_inputs INTEGER,
    -- assurance_calculated_at ist der Zeitpunkt der Berechnung in UTC.
    ADD COLUMN assurance_calculated_at TIMESTAMPTZ,

    ADD CONSTRAINT backups_classification_valid
        CHECK (classification IS NULL OR classification IN
               ('failed', 'corrupted', 'successful', 'verified', 'recoverable')),
    ADD CONSTRAINT backups_assurance_score_range
        CHECK (assurance_score IS NULL OR (assurance_score >= 0 AND assurance_score <= 100)),
    -- Ein als wiederherstellbar eingestuftes Backup muss einen bestandenen
    -- Wiederherstellungstest vorweisen. Die Einstufung ist die staerkste
    -- Aussage der Anlage; sie ohne Nachweis zu vergeben waere genau die
    -- Beschoenigung, die PROMPT.md §138 verbietet.
    ADD CONSTRAINT backups_recoverable_needs_test CHECK (
        classification <> 'recoverable' OR last_restore_test_at IS NOT NULL
    ),
    -- Ebenso: „geprueft" verlangt eine Pruefung.
    ADD CONSTRAINT backups_verified_needs_verification CHECK (
        classification <> 'verified' OR last_verified_at IS NOT NULL
    );

-- Fuer die Uebersicht: Welche Backups wurden nie geprueft?
CREATE INDEX backups_never_verified_idx ON backups (repository_id)
    WHERE last_verified_at IS NULL AND status = 'complete';

-- Fuer die Uebersicht: Welche Backups sind auffaellig?
CREATE INDEX backups_classification_idx ON backups (classification)
    WHERE classification IN ('corrupted', 'failed');

-- ---------------------------------------------------------------------------
-- Berechtigungen
-- ---------------------------------------------------------------------------

-- verification.read und verification.write stammen bereits aus 000002_identity.
-- Neu ist der Wiederherstellungstest: Er schreibt zwar nur an einen Wegwerfort,
-- liest aber das gesamte Backup und belastet damit Datentraeger und Leitung.
-- Wer ihn ausloest, sollte wissen was er tut.
INSERT INTO permissions (name, description) VALUES
    ('verification.restore_test', 'Wiederherstellungstests ausloesen');

-- Vergeben wird das Recht nur an die Rollen, die ueberhaupt eine Pruefung
-- ausloesen koennen (verification.write): super_administrator und
-- backup_operator. Ein Recht an eine Rolle zu haengen, die den Endpunkt gar
-- nicht erreicht, sieht in der Rechteuebersicht nach einer Faehigkeit aus, die
-- es nicht gibt.
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.name IN ('super_administrator', 'backup_operator')
  AND p.name = 'verification.restore_test';