Apply database constraints

[mlhaufe]

pglite currently throws "memory access out of bounds" errors when foreign key constraints are defined and in some other cases.

Waiting for the following issue to be resolved:

https://github.com/electric-sql/pglite/issues/92

Some impacted areas in the initial migration:

CREATE TABLE component (
-- parent_component_id UUID REFERENCES component(id) -- FIXME: Memory access out of bounds
parent_component_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
)
INHERITS (actor);

CREATE TABLE person (
    email VARCHAR(60) NOT NULL UNIQUE,
    -- role_id UUID NOT NULL REFERENCES stakeholder(id) -- FIXME: Memory access out of bounds
    role_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
)
INHERITS (actor);

-- When a stakeholder is deleted, the person.role_id associated with it is set to an empty uuid.
CREATE FUNCTION set_default_role()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE person
    SET role_id = '00000000-0000-0000-0000-000000000000'
    WHERE role_id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_role_on_stakeholder_delete
AFTER DELETE ON stakeholder
FOR EACH ROW
EXECUTE FUNCTION set_default_role();

-- When an actor is deleted, the scenario.primary_actor_id associated with it is set to an empty uuid.
CREATE FUNCTION set_default_primary_actor_id()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE scenario
    SET primary_actor_id = '00000000-0000-0000-0000-000000000000'
    WHERE primary_actor_id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_primary_actor_id_on_actor_delete
AFTER DELETE ON actor
FOR EACH ROW
EXECUTE FUNCTION set_default_primary_actor_id();

-- When a functional_behavior is deleted, the user_story.functional_behavior_id associated with it is set to an empty uuid.
CREATE FUNCTION set_default_functional_behavior_id()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE user_story
    SET functional_behavior_id = '00000000-0000-0000-0000-000000000000'
    WHERE functional_behavior_id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_functional_behavior_id_on_functional_behavior_delete
AFTER DELETE ON functional_behavior
FOR EACH ROW
EXECUTE FUNCTION set_default_functional_behavior_id();

-- When an outcome is deleted, the user_story.outcome_id associated with it is set to an empty uuid.
CREATE FUNCTION set_default_outcome_id()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE user_story
    SET outcome_id = '00000000-0000-0000-0000-000000000000'
    WHERE outcome_id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_outcome_id_on_outcome_delete
AFTER DELETE ON outcome
FOR EACH ROW
EXECUTE FUNCTION set_default_outcome_id();

Note that a number of constraints are not supported including UNIQUE:

https://electric-sql.com/docs/usage/data-modelling/constraints

To get referential transparency to work, table inheritence will probably have to be dropped