Behavision/server/migrations/013_references_are_immutable.sql

-- The references clients are now told to use must not be able to change.
--
-- 012 made `chennai`, `Office1` and `V-42` first-class: every route that takes
-- an id takes one of these instead, and the documentation tells a client to
-- prefer them. That turns three columns which were merely descriptive into
-- IDENTIFIERS other people store - in an agent's config file on a shop counter,
-- in a saved URL, in a report somebody scheduled.
--
-- All three were already treated as stable, and none of it was enforced:
--
--   * `clients.slug` appears in MQTT topics as bv/<client>.<site>/... and the
--     broker ACL is written against it. Renaming one silently stops that
--     tenant's estate from being able to publish, and the agents cannot be told
--     - they would simply be refused by the broker.
--   * `sites.slug` is what a shop PC calls itself: agent.json holds
--     "site_id": "chennai". A rename orphans the PC from the shop it is
--     standing in.
--   * `site_cameras.camera_id` is what lands in `visits.camera_id`, which is a
--     text column and not a foreign key. Renaming it orphans every visit
--     already attributed to the old name - the footfall is still there and no
--     longer joins to a camera.
--
-- The camera case was half-enforced in one handler (`handleUpdateCamera` nils
-- CameraID before saving) and nowhere else, which is the shape of a rule that
-- holds until somebody adds a second write path. This is the backstop, in the
-- one place every write has to go through.
--
-- Deliberately a trigger and not a CHECK: a CHECK cannot see the old row, and
-- the rule is about the transition, not the value.
--
-- Note what this does NOT freeze. `name` - "TeNext Chennai", "Front door" - is
-- free to change and always should be: it is what a person reads, it is not what
-- anything keys on, and conflating the two is how a system ends up unable to fix
-- a typo in a shop's name.

BEGIN;

CREATE OR REPLACE FUNCTION reference_is_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION
        '% is a public reference and cannot be changed (% -> %); '
        'create a new row instead, or change the display name',
        TG_ARGV[0], OLD.slug, NEW.slug
        USING ERRCODE = 'check_violation';
END;
$$;

-- camera_id lives in its own function only because the column is named
-- differently; splitting it keeps the message honest about which value moved.
CREATE OR REPLACE FUNCTION camera_reference_is_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION
        'camera_id is a public reference and cannot be changed (% -> %); '
        'every visit already recorded names the old one. Change the label instead',
        OLD.camera_id, NEW.camera_id
        USING ERRCODE = 'check_violation';
END;
$$;

DROP TRIGGER IF EXISTS clients_slug_immutable ON clients;
CREATE TRIGGER clients_slug_immutable
    BEFORE UPDATE OF slug ON clients
    FOR EACH ROW WHEN (OLD.slug IS DISTINCT FROM NEW.slug)
    EXECUTE FUNCTION reference_is_immutable('clients.slug');

DROP TRIGGER IF EXISTS sites_slug_immutable ON sites;
CREATE TRIGGER sites_slug_immutable
    BEFORE UPDATE OF slug ON sites
    FOR EACH ROW WHEN (OLD.slug IS DISTINCT FROM NEW.slug)
    EXECUTE FUNCTION reference_is_immutable('sites.slug');

DROP TRIGGER IF EXISTS site_cameras_id_immutable ON site_cameras;
CREATE TRIGGER site_cameras_id_immutable
    BEFORE UPDATE OF camera_id ON site_cameras
    FOR EACH ROW WHEN (OLD.camera_id IS DISTINCT FROM NEW.camera_id)
    EXECUTE FUNCTION camera_reference_is_immutable();

-- A visitor's number is assigned once from the tenant's counter and read back
-- as V-42. Nothing writes it after the insert; this says so.
DROP TRIGGER IF EXISTS visitors_number_immutable ON visitors;
CREATE OR REPLACE FUNCTION visitor_number_is_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION
        'visitors.number is a public reference and cannot be changed (% -> %)',
        OLD.number, NEW.number
        USING ERRCODE = 'check_violation';
END;
$$;
CREATE TRIGGER visitors_number_immutable
    BEFORE UPDATE OF number ON visitors
    FOR EACH ROW WHEN (OLD.number IS DISTINCT FROM NEW.number)
    EXECUTE FUNCTION visitor_number_is_immutable();

COMMIT;