Paul Schoeneck
75af38a4b6
- Introduced additional terms under Section 7 of the GNU AGPL v3 for KIBU, including attribution, marking of modified versions, and trademark usage. - Added MIT License file for clarity on licensing options. - Updated README.md to reflect the new licensing terms, including detailed usage rights and obligations. - Created REUSE.toml for SPDX compliance and license annotations. - Added THIRD_PARTY_NOTICES.md to clarify third-party dependencies and their licenses. - Updated various source files and scripts to include SPDX headers for copyright and licensing. - Enhanced footer in layout to display licensing information and project link. - Implemented a new license page to present the AGPL and additional terms clearly.
102 lines
4.4 KiB
PL/PgSQL
102 lines
4.4 KiB
PL/PgSQL
-- SPDX-FileCopyrightText: 2026 Paul Schoeneck
|
|
--
|
|
-- SPDX-License-Identifier: AGPL-3.0-only AND LicenseRef-KIBU-Additional-Terms
|
|
|
|
CREATE TABLE pickup_contacts (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
child_id uuid NOT NULL REFERENCES children(id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
relationship text NOT NULL,
|
|
phone text NOT NULL DEFAULT '',
|
|
authorized boolean NOT NULL DEFAULT true,
|
|
notes text NOT NULL DEFAULT ''
|
|
);
|
|
CREATE INDEX pickup_contacts_child ON pickup_contacts(child_id);
|
|
|
|
CREATE TABLE alone_rules (
|
|
child_id uuid NOT NULL REFERENCES children(id) ON DELETE CASCADE,
|
|
weekday integer NOT NULL CHECK (weekday BETWEEN 1 AND 7),
|
|
earliest time NOT NULL,
|
|
PRIMARY KEY(child_id, weekday)
|
|
);
|
|
|
|
ALTER TABLE attendance DROP CONSTRAINT attendance_pickup_kind_check;
|
|
ALTER TABLE attendance ADD CONSTRAINT attendance_pickup_kind_check
|
|
CHECK (pickup_kind IN ('planned', 'parents', 'alone'));
|
|
ALTER TABLE attendance ADD CONSTRAINT attendance_chronology
|
|
CHECK (picked_up_at IS NULL OR picked_up_at >= arrived_at);
|
|
|
|
CREATE SEQUENCE attendance_revision;
|
|
ALTER TABLE attendance ADD COLUMN version bigint NOT NULL DEFAULT nextval('attendance_revision');
|
|
CREATE FUNCTION bump_attendance_revision() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
NEW.version := nextval('attendance_revision');
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
CREATE TRIGGER attendance_revision_trigger BEFORE UPDATE ON attendance
|
|
FOR EACH ROW EXECUTE FUNCTION bump_attendance_revision();
|
|
|
|
CREATE TABLE audit_log (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
child_id uuid NOT NULL,
|
|
entity text NOT NULL,
|
|
entity_id text,
|
|
day date,
|
|
operation text NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
|
|
actor_id uuid,
|
|
actor_name text NOT NULL,
|
|
reason text NOT NULL,
|
|
before_data jsonb,
|
|
after_data jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp()
|
|
);
|
|
CREATE INDEX audit_log_child ON audit_log(child_id, id DESC);
|
|
|
|
CREATE FUNCTION record_child_audit() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE
|
|
old_data jsonb;
|
|
new_data jsonb;
|
|
row_data jsonb;
|
|
actor uuid;
|
|
actor_label text;
|
|
BEGIN
|
|
IF TG_OP <> 'INSERT' THEN old_data := to_jsonb(OLD); END IF;
|
|
IF TG_OP <> 'DELETE' THEN new_data := to_jsonb(NEW); END IF;
|
|
IF TG_OP = 'UPDATE' AND old_data - 'version' = new_data - 'version' THEN RETURN NEW; END IF;
|
|
row_data := COALESCE(new_data, old_data);
|
|
actor := NULLIF(current_setting('kibu.actor_id', true), '')::uuid;
|
|
SELECT name || ' (' || email || ')' INTO actor_label FROM staff WHERE id = actor;
|
|
INSERT INTO audit_log(child_id, entity, entity_id, day, operation, actor_id, actor_name,
|
|
reason, before_data, after_data)
|
|
VALUES (
|
|
CASE WHEN TG_TABLE_NAME = 'children' THEN (row_data->>'id')::uuid ELSE (row_data->>'child_id')::uuid END,
|
|
TG_TABLE_NAME, row_data->>'id', (row_data->>'day')::date, TG_OP, actor,
|
|
COALESCE(actor_label, 'Datenbank / Einrichtung'),
|
|
COALESCE(NULLIF(current_setting('kibu.reason', true), ''), 'Direkte Datenbankänderung'),
|
|
old_data, new_data
|
|
);
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER children_audit AFTER INSERT OR UPDATE OR DELETE ON children FOR EACH ROW EXECUTE FUNCTION record_child_audit();
|
|
CREATE TRIGGER weekly_audit AFTER INSERT OR UPDATE OR DELETE ON weekly_schedules FOR EACH ROW EXECUTE FUNCTION record_child_audit();
|
|
CREATE TRIGGER override_audit AFTER INSERT OR UPDATE OR DELETE ON day_overrides FOR EACH ROW EXECUTE FUNCTION record_child_audit();
|
|
CREATE TRIGGER attendance_audit AFTER INSERT OR UPDATE OR DELETE ON attendance FOR EACH ROW EXECUTE FUNCTION record_child_audit();
|
|
CREATE TRIGGER contacts_audit AFTER INSERT OR UPDATE OR DELETE ON pickup_contacts FOR EACH ROW EXECUTE FUNCTION record_child_audit();
|
|
CREATE TRIGGER alone_audit AFTER INSERT OR UPDATE OR DELETE ON alone_rules FOR EACH ROW EXECUTE FUNCTION record_child_audit();
|
|
|
|
CREATE FUNCTION protect_audit_log() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
RAISE EXCEPTION 'Das Audit-Log ist unveraenderlich.';
|
|
END;
|
|
$$;
|
|
CREATE TRIGGER audit_immutable BEFORE UPDATE OR DELETE OR TRUNCATE ON audit_log
|
|
FOR EACH STATEMENT EXECUTE FUNCTION protect_audit_log();
|
|
|
|
INSERT INTO audit_log(child_id, entity, day, operation, actor_name, reason, after_data)
|
|
SELECT child_id, 'attendance', day, 'INSERT', 'Datenbank / Einrichtung',
|
|
'Bestandsübernahme bei Einführung des Audit-Logs; ursprünglicher Änderungsverlauf nicht verfügbar.',
|
|
to_jsonb(a) FROM attendance a;
|