Track record

The Postgres trigger below runs BEFORE UPDATE on the picks table. Any attempt to mutate entry_price_usd or entry_at raises an exception and writes the attempted change to audit_pick_attempts.

CREATE OR REPLACE FUNCTION prevent_pick_entry_update() RETURNS trigger AS $$
BEGIN
  IF NEW.entry_price_usd IS DISTINCT FROM OLD.entry_price_usd
     OR NEW.entry_at IS DISTINCT FROM OLD.entry_at THEN
    INSERT INTO audit_pick_attempts (id, pick_id, attempted_by, old_value, new_value)
    VALUES (
      gen_random_uuid(),
      OLD.id,
      current_user,
      jsonb_build_object('entry_price_usd', OLD.entry_price_usd, 'entry_at', OLD.entry_at),
      jsonb_build_object('entry_price_usd', NEW.entry_price_usd, 'entry_at', NEW.entry_at)
    );
    RAISE EXCEPTION 'picks.entry_price_usd and picks.entry_at are immutable (audited)';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER picks_immutable_entry
  BEFORE UPDATE ON picks
  FOR EACH ROW EXECUTE FUNCTION prevent_pick_entry_update();