nexarch/workflow/migrations/tenant/0006_case_notes.up.sql

-- VOR-02: Freitext-Vermerke an einer Akte sind nach dem Speichern
-- unveraenderlich (Akzeptanzkriterium 1), analog zum append-only-Muster aus
-- 0004_history_append_only fuer workflow_instance_history.
CREATE TABLE case_notes (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id    UUID NOT NULL REFERENCES case_files(id) ON DELETE CASCADE,
    author     TEXT NOT NULL CHECK (author <> ''),
    body       TEXT NOT NULL CHECK (body <> ''),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX case_notes_case_id_created_at_idx ON case_notes (case_id, created_at);

CREATE OR REPLACE FUNCTION case_notes_append_only() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'case_notes ist unveraenderlich (append-only)';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER case_notes_no_update
    BEFORE UPDATE ON case_notes
    FOR EACH ROW EXECUTE FUNCTION case_notes_append_only();

CREATE TRIGGER case_notes_no_delete
    BEFORE DELETE ON case_notes
    FOR EACH ROW EXECUTE FUNCTION case_notes_append_only();