This is a post to list the needed stats/history for SPHERE.
For each user - creation and deletion events
- timestamp, username, created or deleted
For each user login attempt
- timestamp, username, succeded or failed
For each organization - creation and deletion
- timestamp, org, username, created or deleted, org category, org description
For each project - creation and deletion
- timestamp, project, username, created or deleted, project category, project description
For each membership change
- timestamp, project or org name, username originating the change, username whose membership is changing, new membership role
For each experiment - creation or deletion or realization/materialization/revision
- timestamp, experiment name, project name, username originating the event, type of event (created, deleted, pushed new model, realization, materialization), succeeded or failed, for push/realize/materialize include model file, for failures include error message
For realizations and materializations
- timestamp, experiment, project, user, list of physical resources involved, list of virtual nodes and lans, allocate or deallocate, success or failure
If some of these cannot be achieved, let’s document what we can and cannot do and keep moving towards the ideal where we have all these
After dev discussion we decided that a database to track these stats is the way to go.
We can still use the existing stats service (which publishes stats to https://grafana.sphere-testbed.net), but those stats are time-series and it sounds like you want straight up stats - which don’t really fit well in time-series format.
Do you have a database preference? MongoDB was one suggestion that was raised…
Once this is done, we also need a page that shows relevant stats, number of users, projects, etc.
And a page that shows “status” of every facility, possibly overlaid on a map. Click facility to show status, etc.
And support for exporting this data.
For “For each X - creation and deletion events” - we can note creation, activation, decline, and delete. Or just activation, freeze, decline. Not sure which we want.
Went with postgres as SQL seemed to make more sense based on the queries we may do. The setup is a single “event” table with fields for expected query parameters and JSON for “other stuff” related to the entry. For example, realization, project, experiment in a realization-created entry.
Sample data:
portal-audit=> SELECT * from events;
event_uuid | timestamp | event_type | caller | subject | subject_type | target | success | error_message | payload
--------------------------------------+-------------------------------+--------------------+---------+---------+--------------+-------------+---------+---------------+----------------------------------------------------------------
2e2812be-3e7a-4a30-8c64-4ec09018f1ab | 2026-04-23 20:50:23.597554+00 | org_member_added | glawler | orgtest | organization | testuser | t | | {"kind": "usermember", "role": "member", "state": "active"}
5e46bd28-2982-40e6-b39f-770fe69f7137 | 2026-04-23 20:50:28.16609+00 | org_member_added | glawler | orgtest | organization | testproject | t | | {"kind": "projectmember", "role": "member", "state": "active"}
bb539f70-7571-4f37-b6e1-e9f2ee40ea5c | 2026-04-23 20:50:41.026119+00 | org_member_deleted | glawler | orgtest | organization | testproject | f | | {"kind": "projectmember"}
307025f7-fa9f-43f8-8979-883788a272fe | 2026-04-23 20:50:47.453077+00 | org_member_deleted | glawler | orgtest | organization | testuser | f | | {"kind": "usermember"}
4e1c56c3-4456-4bce-b208-5f073ccff081 | 2026-04-23 21:17:46.79238+00 | experiment_created | | testexp | experiment | | t | | {"project": "glawler"}
e0bfb051-70d2-425e-92c9-6a4cd2c9dfaf | 2026-04-23 21:17:55.933064+00 | experiment_deleted | glawler | testexp | experiment | | t | | {"project": "glawler"}
e018cb7c-8f94-47a4-ba03-e317fba25d61 | 2026-04-23 21:18:45.443554+00 | experiment_created | glawler | testexp | experiment | | t | | {"project": "glawler"}
dd64b054-0334-41c4-a7af-f021232938b3 | 2026-04-23 21:18:48.265956+00 | experiment_deleted | glawler | testexp | experiment | | t | | {"project": "glawler"}
(8 rows)
The table:
CREATE TABLE IF NOT EXISTS events (
event_uuid UUID PRIMARY KEY,
timestamp TIMESTAMPTZ NOT NULL,
event_type TEXT NOT NULL,
caller TEXT NOT NULL,
subject TEXT NOT NULL,
subject_type TEXT NOT NULL,
target TEXT,
success BOOLEAN NOT NULL,
error_message TEXT,
payload JSONB NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS idx_events_timestamp
ON events (timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_events_event_type
ON events (event_type);
CREATE INDEX IF NOT EXISTS idx_events_caller
ON events (caller);
CREATE INDEX IF NOT EXISTS idx_events_subject
ON events (subject_type, subject);
CREATE INDEX IF NOT EXISTS idx_events_target
ON events (target);
Note that with postgres you can use BJSON to query into the json payload field.
SELECT *
FROM events
WHERE event_type = 'org_member_added'
AND success = true
AND payload->>'kind' = 'usermember';
