How to ensure a log-entry is created based on state of data in other tables

Hi.

I'm not sure what the best subject is for this email, but here are the
requirements: When all activities for a given product has status='DONE' then an
entry inactivity_product_log should be created. This should be guaranteed
somehow. If an activity for which there exists an entry inactivity_product_log
is set to status='NOT_DONE', then the entry inactivity_product_log should be
deleted.

Here's an example-schema:

DROP TABLE IF EXISTS activity_product_log;
DROP TABLE IF EXISTS activity;
DROP TABLE IF EXISTS product;

CREATE TABLE product
(
id INTEGER primary key,
name varchar not null unique
);

CREATE TABLE activity
(
id serial primary key,
name varchar not null unique,
product_id integer not null references product(id),
status varchar NOT NULL,
CHECK (status IN ('NOT_DONE', 'DONE'))
);

CREATE TABLE activity_product_log
(
id serial primary key,
product_id integer not null references product(id),
created TIMESTAMP NOT NULL
);

INSERT INTO product(id, name) VALUES(1, 'Horse'), (2, 'Fish');

INSERT INTO activity(name, product_id, status)
VALUES ('A1', 1, 'NOT_DONE'), ('A2', 1, 'NOT_DONE'), ('A3', 1, 'NOT_DONE'), (
'A4', 1, 'NOT_DONE')
, ('A5', 2, 'NOT_DONE'), ('A6', 2, 'NOT_DONE')
;

UPDATE activity SET status = 'DONE' WHERE name = 'A1';
UPDATE activity SET status = 'DONE' WHERE name = 'A2';
UPDATE activity SET status = 'DONE' WHERE name = 'A3';
UPDATE activity SET status = 'DONE' WHERE name = 'A4'; -- Here an entry in
activity_log should be created
I'm thinking about using constraint-triggers for this but will that actually
guarantee the requirements? I'm worried about the last part of the requirement,
if all activities for a product are ‘DONE’ but then gets toggled ‘NOT_DONE’ and
‘DONE’ “very fast”.

Is there a better way, using some extra tables to do bookkeeping can the have
constraints ensuring this business-requirement?

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>