96 lines
4.8 KiB
MySQL
96 lines
4.8 KiB
MySQL
|
-- Migration: 0001_per_diem_rates
|
||
|
|
-- Legt die Tabelle für Verpflegungsmehraufwands-Pauschalen (BMF-Sätze etc.) an.
|
||
|
|
-- Siehe docs/architecture/v1.1.md §6.2 und schemas/ds/per_diem/v1.0.0.json.
|
||
|
|
--
|
||
|
|
-- Design-Entscheidungen:
|
||
|
|
-- * rate_id ist eine stabile textuelle ID "PDR-<n>", vergeben über eine Sequence.
|
||
|
|
-- * Kernfelder (Beträge, country, valid_from, source_regulation) sind unveränderlich,
|
||
|
|
-- erzwungen per BEFORE UPDATE Trigger. Nur valid_to und rate_change_attestation_ref
|
||
|
|
-- dürfen nachträglich gesetzt werden (für kontrolliertes Ablaufen bzw. das Nachtragen
|
||
|
|
-- der Attestations-Referenz).
|
||
|
|
-- * Keine Überlappung von Gültigkeitsbereichen pro (country, city) — EXCLUDE-Constraint
|
||
|
|
-- mit GiST + daterange, benötigt die btree_gist Extension.
|
||
|
|
-- * Betrage als NUMERIC(10,2) — Geld, keine Floats.
|
||
|
|
|
||
|
|
CREATE EXTENSION IF NOT EXISTS btree_gist;
|
||
|
|
|
||
|
|
CREATE SEQUENCE IF NOT EXISTS per_diem_rates_seq START 1;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS per_diem_rates (
|
||
|
|
rate_id TEXT PRIMARY KEY
|
||
|
|
DEFAULT ('PDR-' || nextval('per_diem_rates_seq')),
|
||
|
|
country TEXT NOT NULL CHECK (country ~ '^[A-Z]{2}$'),
|
||
|
|
city TEXT,
|
||
|
|
currency TEXT NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
|
||
|
|
full_day NUMERIC(10, 2) NOT NULL CHECK (full_day >= 0),
|
||
|
|
partial_day_min8h NUMERIC(10, 2) NOT NULL CHECK (partial_day_min8h >= 0),
|
||
|
|
arrival_day NUMERIC(10, 2) NOT NULL CHECK (arrival_day >= 0),
|
||
|
|
departure_day NUMERIC(10, 2) NOT NULL CHECK (departure_day >= 0),
|
||
|
|
valid_from DATE NOT NULL,
|
||
|
|
valid_to DATE,
|
||
|
|
source_regulation TEXT NOT NULL CHECK (length(source_regulation) BETWEEN 1 AND 500),
|
||
|
|
created_by TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
rate_change_attestation_ref TEXT CHECK (rate_change_attestation_ref ~ '^sha256:[0-9a-f]{64}$'),
|
||
|
|
CONSTRAINT per_diem_rates_valid_range CHECK (valid_to IS NULL OR valid_to >= valid_from)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_pdr_country_valid_from
|
||
|
|
ON per_diem_rates (country, valid_from);
|
||
|
|
|
||
|
|
CREATE INDEX idx_pdr_lookup_active
|
||
|
|
ON per_diem_rates (country, valid_from)
|
||
|
|
WHERE valid_to IS NULL;
|
||
|
|
|
||
|
|
-- Prevent overlapping validity windows per (country, city).
|
||
|
|
-- COALESCE(city, '') normalises NULL to a sentinel so NULL-city overlaps are also caught.
|
||
|
|
ALTER TABLE per_diem_rates
|
||
|
|
ADD CONSTRAINT per_diem_rates_no_overlap EXCLUDE USING gist (
|
||
|
|
country WITH =,
|
||
|
|
COALESCE(city, '') WITH =,
|
||
|
|
daterange(valid_from, COALESCE(valid_to, 'infinity'::date), '[]') WITH &&
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Immutability trigger: only valid_to and rate_change_attestation_ref may be updated.
|
||
|
|
CREATE OR REPLACE FUNCTION per_diem_rates_prevent_rewrite() RETURNS TRIGGER AS $$
|
||
|
|
BEGIN
|
||
|
|
IF NEW.rate_id IS DISTINCT FROM OLD.rate_id
|
||
|
|
OR NEW.country IS DISTINCT FROM OLD.country
|
||
|
|
OR NEW.city IS DISTINCT FROM OLD.city
|
||
|
|
OR NEW.currency IS DISTINCT FROM OLD.currency
|
||
|
|
OR NEW.full_day IS DISTINCT FROM OLD.full_day
|
||
|
|
OR NEW.partial_day_min8h IS DISTINCT FROM OLD.partial_day_min8h
|
||
|
|
OR NEW.arrival_day IS DISTINCT FROM OLD.arrival_day
|
||
|
|
OR NEW.departure_day IS DISTINCT FROM OLD.departure_day
|
||
|
|
OR NEW.valid_from IS DISTINCT FROM OLD.valid_from
|
||
|
|
OR NEW.source_regulation IS DISTINCT FROM OLD.source_regulation
|
||
|
|
OR NEW.created_by IS DISTINCT FROM OLD.created_by
|
||
|
|
OR NEW.created_at IS DISTINCT FROM OLD.created_at
|
||
|
|
THEN
|
||
|
|
RAISE EXCEPTION 'per_diem_rates: only valid_to and rate_change_attestation_ref may be updated (rate_id=%)', OLD.rate_id;
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER per_diem_rates_prevent_rewrite_trg
|
||
|
|
BEFORE UPDATE ON per_diem_rates
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION per_diem_rates_prevent_rewrite();
|
||
|
|
|
||
|
|
-- Audit log for admin actions on per_diem_rates. The authoritative audit lives
|
||
|
|
-- in the Transparency Log (rate_change attestations); this table is a local
|
||
|
|
-- convenience mirror for operators to query without hitting Trillian.
|
||
|
|
CREATE TABLE IF NOT EXISTS per_diem_rate_changes (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
rate_id TEXT NOT NULL REFERENCES per_diem_rates(rate_id),
|
||
|
|
change_kind TEXT NOT NULL CHECK (change_kind IN ('create', 'retire')),
|
||
|
|
previous_valid_to DATE,
|
||
|
|
new_valid_to DATE,
|
||
|
|
performed_by TEXT NOT NULL,
|
||
|
|
performed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
rate_change_attestation_ref TEXT NOT NULL CHECK (rate_change_attestation_ref ~ '^sha256:[0-9a-f]{64}$')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_pdr_changes_rate_id ON per_diem_rate_changes (rate_id, performed_at DESC);
|