79 lines
3.6 KiB
SQL
79 lines
3.6 KiB
SQL
-- ============================================================================
|
||
-- W A R N U N G – K I – G E N E R I E R T E S D A T E N B A N K – S C H E M A
|
||
--
|
||
-- Dieses SQL-Schema / diese Daten wurden vollständig von einer KI generiert.
|
||
-- Experiment-Key: run_17_gates_default
|
||
--
|
||
-- WICHTIG:
|
||
-- • Rein experimentell – KEINE Qualitätssicherung, KEINE Tests
|
||
-- • KEIN PRODUKTIVEINSATZ – auch nicht in Test- oder Staging-Umgebungen!
|
||
-- • Kann Datenverlust, Inkonsistenzen, Sicherheitslücken oder Abstürze verursachen
|
||
-- • Nur für Forschung, Demonstration und Lernzwecke gedacht
|
||
--
|
||
-- Verwenden auf eigene Gefahr!
|
||
-- ============================================================================
|
||
-- Datenbank-Dialekt: PostgreSQL 16+
|
||
-- Kompatibilität: PostgreSQL >= 15 empfohlen
|
||
|
||
CREATE TABLE runs (
|
||
run_id BIGSERIAL NOT NULL,
|
||
run_label TEXT NOT NULL,
|
||
experiment_key TEXT NOT NULL,
|
||
setup_fingerprint TEXT NOT NULL,
|
||
policy_hash TEXT NOT NULL,
|
||
gate_version TEXT NOT NULL,
|
||
mode TEXT NOT NULL DEFAULT 'warn',
|
||
started_at TIMESTAMPTZ NOT NULL,
|
||
completed_at TIMESTAMPTZ,
|
||
decision_card_url TEXT,
|
||
notes TEXT,
|
||
CONSTRAINT runs_pkey PRIMARY KEY (run_id),
|
||
CONSTRAINT runs_run_label_key UNIQUE (run_label),
|
||
CONSTRAINT runs_mode_chk CHECK (mode IN ('warn', 'observe', 'enforce')),
|
||
CONSTRAINT runs_timeline_chk CHECK (completed_at IS NULL OR completed_at >= started_at)
|
||
);
|
||
|
||
COMMENT ON TABLE runs IS 'Metadaten zu Gate-V1-Experimentläufen (z. B. Run #14–#17).';
|
||
|
||
CREATE TABLE strata (
|
||
stratum_code TEXT NOT NULL,
|
||
description TEXT NOT NULL,
|
||
is_pinned BOOLEAN NOT NULL,
|
||
near_expiry BOOLEAN NOT NULL,
|
||
CONSTRAINT strata_pkey PRIMARY KEY (stratum_code)
|
||
);
|
||
|
||
COMMENT ON TABLE strata IS 'Referenztabelle für Strata (pinned, unpinned, near-expiry, ...).';
|
||
|
||
CREATE TABLE log_entries (
|
||
log_id BIGSERIAL NOT NULL,
|
||
run_id BIGINT NOT NULL,
|
||
stratum_code TEXT NOT NULL,
|
||
recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
retry_taken BOOLEAN NOT NULL,
|
||
retry_total_overhead_ms INTEGER NOT NULL,
|
||
expires_at_dist_hours NUMERIC(8,3) NOT NULL,
|
||
delta_t NUMERIC(9,3) NOT NULL,
|
||
CONSTRAINT log_entries_pkey PRIMARY KEY (log_id),
|
||
CONSTRAINT log_entries_run_fk FOREIGN KEY (run_id)
|
||
REFERENCES runs (run_id) ON DELETE CASCADE ON UPDATE RESTRICT,
|
||
CONSTRAINT log_entries_stratum_fk FOREIGN KEY (stratum_code)
|
||
REFERENCES strata (stratum_code) ON DELETE RESTRICT ON UPDATE CASCADE,
|
||
CONSTRAINT log_entries_retry_overhead_chk CHECK (retry_total_overhead_ms BETWEEN 0 AND 1000),
|
||
CONSTRAINT log_entries_expires_dist_chk CHECK (expires_at_dist_hours BETWEEN -720 AND 720),
|
||
CONSTRAINT log_entries_delta_t_chk CHECK (delta_t BETWEEN -500 AND 500)
|
||
);
|
||
|
||
COMMENT ON TABLE log_entries IS 'Zentrale Log-Tabelle für Gate-V1-Entscheidungsdaten (Δt, Retry-Overhead etc.).';
|
||
COMMENT ON COLUMN log_entries.delta_t IS 'Gemessene Δt-Abweichung in Millisekunden (negativ bedeutet zu früh).';
|
||
COMMENT ON COLUMN log_entries.expires_at_dist_hours IS 'Zeitabstand bis zum Expiry (Stunden, kann negativ sein).';
|
||
|
||
CREATE INDEX log_entries_run_stratum_idx
|
||
ON log_entries (run_id, stratum_code);
|
||
|
||
CREATE INDEX log_entries_stratum_delta_t_idx
|
||
ON log_entries (stratum_code, delta_t);
|
||
|
||
CREATE INDEX log_entries_retry_taken_idx
|
||
ON log_entries (stratum_code, retry_taken)
|
||
WHERE retry_taken = TRUE;
|