{
    "schema": "https://saferpage.de/schemas/security-feed-storage-readiness.v1",
    "generated_at": "2026-06-09T21:14:36+00:00",
    "available": true,
    "ready_for_write": false,
    "migration_required": true,
    "admin_dsn_required": true,
    "summary": "Security-Feed-Storage ist noch nicht schreibbereit: 11 Datenbank-Artefakt(e) fehlen oder die Serverfreigabe ist nicht aktiv.",
    "metrics": {
        "required_table_count": 3,
        "ready_table_count": 0,
        "required_index_count": 6,
        "ready_index_count": 0,
        "required_trigger_count": 2,
        "ready_trigger_count": 0,
        "missing_database_artifact_count": 11,
        "preflight_check_count": 11,
        "preflight_ready_artifact_count": 0,
        "preflight_missing_required_artifact_count": 11
    },
    "database": {
        "available": true,
        "error": "",
        "database_name": "saferpage",
        "db_user_hash": "d3234487bb1bc80ee9da6915667ce1f2",
        "public_schema_create_privilege": false,
        "tables_ready": false,
        "indexes_ready": false,
        "triggers_ready": false
    },
    "storage_preflight": {
        "admin_dsn_required": true,
        "available": true,
        "checks": [
            {
                "id": "security_feed_observations_table",
                "label": "Feed-Observations-Tabelle",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Private Malware-/Blacklist-/DAST-Observations mit Hash, Verdict, Review- und Publish-Status.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_alert_links_table",
                "label": "Alert-Link-Tabelle",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Verknuepft private Feed-Observations mit Alerts/Tickets ohne Rohpayload-Export.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_audit_log_table",
                "label": "Feed-Auditlog",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Append-only Review-, Publish-, Suppress- und Delete-Entscheidungen.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_observations_domain_checked_idx",
                "label": "Domain/Checked-Index",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Schneller Zugriff auf letzte Feed-Checks pro Domain.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_observations_source_verdict_idx",
                "label": "Source/Verdict-Index",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Auswertung nach Feed-Quelle und Verdict.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_observations_expires_idx",
                "label": "Retention-Index",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Loesch- und Ablaufjobs finden faellige Observations.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_observations_result_meta_gin_idx",
                "label": "Result-Meta-GIN-Index",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Sanitisierte Metadaten bleiben filterbar.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_alert_links_dedupe_idx",
                "label": "Alert-Dedupe-Index",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Verhindert doppelte Alert-Verknuepfungen.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_audit_log_dedupe_idx",
                "label": "Audit-Dedupe-Index",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Audit-Events koennen pro Dedupe-Key nachvollzogen werden.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_observations_set_updated_at_trigger",
                "label": "Observations-updated_at-Trigger",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Aenderungen setzen updated_at automatisch.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            },
            {
                "id": "security_feed_alert_links_set_updated_at_trigger",
                "label": "Alert-Link-updated_at-Trigger",
                "operator_action": "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin aus sicherer Shell anwenden.",
                "purpose": "Aenderungen setzen updated_at automatisch.",
                "ready": false,
                "required_for_storage": true,
                "status": "missing"
            }
        ],
        "database_name": "saferpage",
        "database_reachable": true,
        "db_user_redacted": "63a9f0ea7bb98050796b649e85481845",
        "generated_at": "2026-06-09T21:13:53.284528Z",
        "migration_path": "infra/postgres/migrations/security-feed-storage.sql",
        "migration_required": true,
        "migration_sha256": "306a569bdf0d59054861ddc8ddfcf0d09a825f097f3d1dbe88993e481f048314",
        "missing_artifact_count": 11,
        "missing_required_artifacts": [
            "security_feed_observations_table",
            "security_feed_alert_links_table",
            "security_feed_audit_log_table",
            "security_feed_observations_domain_checked_idx",
            "security_feed_observations_source_verdict_idx",
            "security_feed_observations_expires_idx",
            "security_feed_observations_result_meta_gin_idx",
            "security_feed_alert_links_dedupe_idx",
            "security_feed_audit_log_dedupe_idx",
            "security_feed_observations_set_updated_at_trigger",
            "security_feed_alert_links_set_updated_at_trigger"
        ],
        "operator_runbook": [
            "Kurzlebigen Admin-DSN nur in einer sicheren Shell setzen; nicht in History, Projektdateien, systemd-Units oder Public-State speichern.",
            "scripts/run-security-feed-storage-preflight.sh ausfuehren und missing_required_artifacts pruefen.",
            "infra/postgres/migrations/security-feed-storage.sql mit DB-Owner/Admin anwenden oder scripts/run-storage-migrations.sh nutzen.",
            "Preflight erneut ausfuehren, danach Retention/Review freigeben und erst dann SAFERPAGE_SECURITY_FEED_STORAGE_APPROVED setzen.",
            "Storage-Canary ausfuehren, bevor echte externe Feed-Treffer gespeichert werden."
        ],
        "public_schema_create_privilege": false,
        "ready_artifact_count": 0,
        "required_artifact_count": 11,
        "safe_apply_command": "psql -d saferpage -v ON_ERROR_STOP=1 -f infra/postgres/migrations/security-feed-storage.sql",
        "safe_combined_apply_command": "SAFERPAGE_MIGRATION_DATABASE_URL='postgresql://admin@localhost/saferpage' scripts/run-storage-migrations.sh",
        "safe_next_action": "Apply the storage migration from a secure shell with a DB owner/admin role; keep credentials out of project files, public state and logs.",
        "safe_preflight_command": "scripts/run-security-feed-storage-preflight.sh",
        "schema": "https://saferpage.de/schemas/security-feed-storage-preflight.v1",
        "secret_policy": "No DSN, password, raw database user, host, port, feed credential, webhook URL or raw feed payload is exported.",
        "security_feed_alert_links_dedupe_idx": false,
        "security_feed_alert_links_set_updated_at_trigger": false,
        "security_feed_alert_links_table": false,
        "security_feed_audit_log_dedupe_idx": false,
        "security_feed_audit_log_table": false,
        "security_feed_observations_domain_checked_idx": false,
        "security_feed_observations_expires_idx": false,
        "security_feed_observations_result_meta_gin_idx": false,
        "security_feed_observations_set_updated_at_trigger": false,
        "security_feed_observations_source_verdict_idx": false,
        "security_feed_observations_table": false,
        "storage_artifact_ready": false,
        "preflight_evidence_url": "https://saferpage.de/evidence/security-feed-storage-preflight.json"
    },
    "approval": {
        "storage_approved": false,
        "required_env": "SAFERPAGE_SECURITY_FEED_STORAGE_APPROVED=yes",
        "reason": "Ohne Serverfreigabe bleibt Speicherung deaktiviert, selbst wenn Tabellen vorhanden sind."
    },
    "tables": [
        {
            "id": "security_feed_observations",
            "exists": false,
            "status": "missing",
            "row_count": null,
            "purpose": "Normalisierte Momentaufnahmen aus externen Malware-/Blacklist-/DAST-Feeds.",
            "required_fields": [
                "domain",
                "source_id",
                "checked_at",
                "verdict",
                "severity",
                "matched_url_hash",
                "reference_id",
                "dedupe_key",
                "evidence_url",
                "expires_at",
                "created_by_job_id",
                "review_status",
                "publish_decision",
                "result_meta"
            ]
        },
        {
            "id": "security_feed_alert_links",
            "exists": false,
            "status": "missing",
            "row_count": null,
            "purpose": "Verknuepfung gespeicherter Feed-Beobachtungen mit Alert, Ticket, Owner, SLA und Reviewstatus.",
            "required_fields": [
                "dedupe_key",
                "alert_id",
                "ticket_id",
                "owner_role",
                "sla",
                "publish_decision",
                "review_status"
            ]
        },
        {
            "id": "security_feed_audit_log",
            "exists": false,
            "status": "missing",
            "row_count": null,
            "purpose": "Auditlog fuer Import, Review, Freigabe, Loeschung und Re-Scan.",
            "required_fields": [
                "dedupe_key",
                "event_type",
                "actor_role",
                "before_hash",
                "after_hash",
                "reason",
                "meta"
            ]
        }
    ],
    "indexes": [
        {
            "id": "security_feed_observations_domain_checked_idx",
            "exists": false,
            "status": "missing"
        },
        {
            "id": "security_feed_observations_source_verdict_idx",
            "exists": false,
            "status": "missing"
        },
        {
            "id": "security_feed_observations_expires_idx",
            "exists": false,
            "status": "missing"
        },
        {
            "id": "security_feed_observations_result_meta_gin_idx",
            "exists": false,
            "status": "missing"
        },
        {
            "id": "security_feed_alert_links_dedupe_idx",
            "exists": false,
            "status": "missing"
        },
        {
            "id": "security_feed_audit_log_dedupe_idx",
            "exists": false,
            "status": "missing"
        }
    ],
    "triggers": [
        {
            "id": "security_feed_observations_set_updated_at",
            "table": "security_feed_observations",
            "exists": false,
            "status": "missing"
        },
        {
            "id": "security_feed_alert_links_set_updated_at",
            "table": "security_feed_alert_links",
            "exists": false,
            "status": "missing"
        }
    ],
    "write_contract": {
        "allowed_fields": [
            "domain",
            "source_id",
            "checked_at",
            "verdict",
            "severity",
            "matched_url_hash",
            "reference_id",
            "dedupe_key",
            "evidence_url",
            "expires_at",
            "created_by_job_id",
            "review_status",
            "publish_decision",
            "result_meta"
        ],
        "forbidden_fields": [
            "raw_malware_sample",
            "page_content_dump",
            "secret_value",
            "visitor_ip",
            "personal_log_line",
            "full_feed_payload"
        ],
        "retention": "Clean-Momentaufnahmen 30 Tage, Treffer mit Review/Audit 365 Tage, inconclusive 14 Tage.",
        "publish_policy": "Oeffentliche Anzeige erst nach Betreiber-Review; Runner speichert private Evidence, keine öffentlichen Treffer."
    },
    "runbook": [
        "Dedizierte Migration mit einem autorisierten DB-Owner anwenden: psql -d saferpage -v ON_ERROR_STOP=1 -f infra/postgres/migrations/security-feed-storage.sql",
        "Danach Readiness erneut prüfen und Tabellen/Indizes/Trigger als ready bestätigen.",
        "SAFERPAGE_SECURITY_FEED_STORAGE_APPROVED=yes erst nach Betreiberfreigabe, Retention-Review und Alert-Routing setzen.",
        "Optionalen Storage-Canary mit --storage-canary ausführen, um eine synthetische private Observation ohne externe Feed-Abfrage zu testen.",
        "Separaten systemd-Canary-Timer prüfen: systemctl list-timers saferpage-security-feed-canary.timer --no-pager.",
        "Ersten Runner-Lauf kontrollieren: stored_observation_count muss zu den aktivierungsbereiten Domains passen."
    ],
    "migration_package": {
        "schema_version": "security-feed-storage-2026-06-09",
        "requires_role": "PostgreSQL-Owner oder Rolle mit CREATE TABLE, CREATE INDEX, CREATE TRIGGER im Schema public.",
        "does_not_require_secret_values": true,
        "sql_url": "https://saferpage.de/sicherheit/feed-storage-migration.sql",
        "preflight_evidence_url": "https://saferpage.de/evidence/security-feed-storage-preflight.json",
        "migration_required": true,
        "admin_dsn_required": true,
        "safe_next_action": "Migration mit DB-Owner/Admin aus sicherer Shell anwenden; DSN/Passwort nicht in Projektdateien, Public-State oder Logs speichern.",
        "sql_sha256": "306a569bdf0d59054861ddc8ddfcf0d09a825f097f3d1dbe88993e481f048314",
        "sql_bytes": 3885,
        "preflight_commands": [
            "psql -d saferpage -Atq -c \"select current_database(), current_user;\"",
            "psql -d saferpage -Atq -c \"select coalesce(to_regclass('public.security_feed_observations')::text,'missing');\""
        ],
        "migration_command": "psql -d saferpage -v ON_ERROR_STOP=1 -f infra/postgres/migrations/security-feed-storage.sql",
        "download_and_apply_command": "curl -fsS https://saferpage.de/sicherheit/feed-storage-migration.sql -o /tmp/saferpage-security-feed-storage.sql && psql -d saferpage -v ON_ERROR_STOP=1 -f /tmp/saferpage-security-feed-storage.sql",
        "smoke_test_commands": [
            "psql -d saferpage -Atq -c \"select to_regclass('public.security_feed_observations') is not null, to_regclass('public.security_feed_alert_links') is not null, to_regclass('public.security_feed_audit_log') is not null;\"",
            "psql -d saferpage -Atq -c \"select count(*) from pg_trigger where tgname in ('security_feed_observations_set_updated_at','security_feed_alert_links_set_updated_at');\"",
            "curl -fsS https://saferpage.de/sicherheit/feed-storage-readiness-json | python3 -m json.tool"
        ],
        "activation_order": [
            "DDL-Migration anwenden.",
            "Readiness-Endpunkt prüfen: Tabellen, Indizes und Trigger müssen ready sein.",
            "Feed- und Delivery-Secrets serverseitig setzen und PHP/Runner neu laden.",
            "Canary-Timer installieren/aktivieren und zuerst ohne Storage-Freigabe blockiertes Verhalten pruefen.",
            "SAFERPAGE_SECURITY_FEED_STORAGE_APPROVED=yes erst nach Betreiberfreigabe setzen.",
            "Canary-Service manuell starten und storage_canary_stored_observation_count=1 pruefen.",
            "Runner einmal starten und stored_observation_count kontrollieren."
        ],
        "rollback_or_pause": [
            "Sofortige Pause ohne DDL-Rollback: SAFERPAGE_SECURITY_FEED_STORAGE_APPROVED entfernen und Runner neu laden.",
            "Bei fehlerhaften Observations: review_status=suppressed setzen statt Rohdaten zu löschen, sofern Auditpflicht besteht.",
            "DDL-Drop nur nach Retention-/Audit-Entscheidung und Backupfreigabe ausführen."
        ],
        "acceptance_criteria": [
            "Readiness zeigt required_table_count=3 und ready_table_count=3.",
            "Readiness zeigt required_index_count=6 und ready_index_count=6.",
            "Readiness zeigt required_trigger_count=2 und ready_trigger_count=2.",
            "Storage-Freigabe ist nur nach dokumentierter Betreiberentscheidung aktiv.",
            "Runner-State zeigt keine Secret-Werte und keine Feed-Rohpayloads."
        ]
    },
    "links": {
        "html": "https://saferpage.de/sicherheit/feed-storage-readiness",
        "json": "https://saferpage.de/sicherheit/feed-storage-readiness-json",
        "csv": "https://saferpage.de/sicherheit/feed-storage-readiness-csv",
        "markdown": "https://saferpage.de/sicherheit/feed-storage-readiness-md",
        "runner": "https://saferpage.de/sicherheit/feed-runner-json",
        "storage_example": "https://saferpage.de/sicherheit/anrufer.info/feed-storage-json",
        "activation_example": "https://saferpage.de/sicherheit/anrufer.info/feed-activation-json",
        "secrets_example": "https://saferpage.de/sicherheit/anrufer.info/feed-secrets-json",
        "schema_registry": "https://saferpage.de/schemas/security-feed-storage-readiness.v1",
        "migration_sql": "https://saferpage.de/sicherheit/feed-storage-migration.sql",
        "storage_canary_html": "https://saferpage.de/sicherheit/feed-storage-canary",
        "storage_canary_json": "https://saferpage.de/sicherheit/feed-storage-canary-json",
        "storage_canary_csv": "https://saferpage.de/sicherheit/feed-storage-canary-csv",
        "storage_canary_markdown": "https://saferpage.de/sicherheit/feed-storage-canary-md"
    },
    "disclaimer": "Readiness zeigt technische Schreibbereitschaft und Betreiberfreigabe. Es veröffentlicht keine Secrets, keine Feed-Rohdaten und keine personenbezogenen Logs."
}