Activity

The activity table

Each facility's database has an activity table recording twenty kinds of event. There is no screen and no endpoint — the reader is SQL, deliberately: an investigation happens rarely, under circumstances nobody anticipated, and a screen can only answer the questions someone guessed in advance.

The rule for what is in it: record what the data cannot tell you afterwards. A created stock is self-evidencing — it exists, with its date and its researcher. A deleted one leaves nothing, because every delete here is a hard delete. So deletions, privilege and authentication are recorded, and ordinary creates and updates are not. The highest-frequency writes in the system, swimmers moving between tanks, are left out so the table does not become mostly tank moves.

The event list and the reasoning per event live in zf-server/src/activity/activity-event.ts.

group	events
authentication & access	login.failed login.succeeded authorization.denied password.changed password.reset.requested password.reset.completed
identity & privilege	user.created user.deleted user.role.changed user.email.changed user.status.changed user.pi.changed user.invited user.forceLoggedOut
hard deletes	stock.deleted marker.deleted
configuration	settings.changed identity.prefix.changed
bulk & data leaving	import.ran report.exported

Two properties worth knowing before you trust it

No foreign keys, on purpose. The actor is an id and a name as it read at the time. An admin can create a user, act as them, rename them and delete them; a foreign key would either refuse that delete or cascade the trail away. The denormalised name is what keeps a row legible once the user it names is gone — and user.deleted carries that person's name, email and role, which is what makes every earlier row of theirs readable.

Recording never fails a request, and is not transactional. If the insert throws, the act the user asked for has already happened; failing it afterwards would roll back real work over bookkeeping. The failure goes to the journal instead (journalctl -u zfm-<CID> | grep ActivityService). The gap is a lost row if the process dies between the act and the insert.

Reaching a facility's table

# dev
docker exec -it zfm-mysql mysql -uroot -prootpass zf_fhcrc

# production — see the deployment docs for the credential model
ssh do1
mysql -u<admin> -p <CID>

Canned queries

Everything one person did, newest first. Start here when you have a name.

SELECT at, event, targetType, targetLabel, detail, ip
FROM activity
WHERE actorName = 'fred' OR actorId = '<uuid>'
ORDER BY at DESC;

Who deleted this stock. The reason the table exists.

SELECT at, actorName, actorRole, detail
FROM activity
WHERE event = 'stock.deleted' AND targetLabel = '8543.01';

Everything done to one user's account — created, invited, role moved, email moved, deleted.

SELECT at, event, actorName, detail
FROM activity
WHERE targetType = 'user' AND targetLabel = 'angel'
ORDER BY at;

Reconstructing a deleted user's trail. Take the id from the deletion, then read what they did under it — the name may have changed more than once, the id never did.

SELECT at, actorName, detail FROM activity WHERE event = 'user.deleted';
-- then, with targetId from that row:
SELECT at, event, targetLabel, detail FROM activity WHERE actorId = '<that id>' ORDER BY at;

Failed logins by hour, for telling a forgotten password from someone working through a list.

SELECT DATE_FORMAT(at, '%Y-%m-%d %H:00') AS hour,
       COUNT(*) AS attempts,
       COUNT(DISTINCT actorName) AS usernames,
       COUNT(DISTINCT ip) AS addresses
FROM activity
WHERE event = 'login.failed' AND at > NOW() - INTERVAL 30 DAY
GROUP BY hour
ORDER BY attempts DESC;

Privilege changes in the last quarter — the short list to read after any suspicion.

SELECT at, event, actorName, targetLabel, detail
FROM activity
WHERE event IN ('user.role.changed', 'user.pi.changed', 'user.created',
                'user.deleted', 'user.email.changed')
  AND at > NOW() - INTERVAL 90 DAY
ORDER BY at;

Refused attempts, which is how a stale menu looks different from somebody trying doors.

SELECT actorName, detail->>'$.path' AS path, COUNT(*) AS refusals
FROM activity
WHERE event = 'authorization.denied'
GROUP BY actorName, path
ORDER BY refusals DESC;

Which settings have been changed, and from what.

SELECT at, actorName, targetLabel,
       detail->>'$.before' AS was, detail->>'$.after' AS now
FROM activity
WHERE event IN ('settings.changed', 'identity.prefix.changed')
ORDER BY at DESC;

Volume

A user's session lasts thirty days at most, so logins are monthly rather than daily, and the rest of the events are rare. Expect a few thousand rows per facility per year. Nothing prunes it, which is the intent: tenant-lifetime, not droplet-lifetime.