Subscriptions for more events for the admins in API

[blokhin]

The admin users should be able to claim their interest in the general events which do not concern their accounts.

metis-bff/db_seed.js

Lines 322 to 336 in 1dd1a04

	db(USER_ROLES_TABLE).insert(
	[
	{
	slug: DEFAULT_USER_ROLE,
	label: 'Member',
	permissions: {},
	},
	{
	slug: ADMIN_USER_ROLE,
	label: 'Admin',
	permissions: {},
	},
	],
	['id', 'slug']
	),

Such general events are, mainly:

• new user registrations
• new data created by anyone
• new calculation submitted by anyone

@pyoner has suggested to log such events and flush them to the user in the API.
@knopki we should provision this somewhere in the API client.

[pyoner]

Schema of logs table:

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  type TEXT NOT NULL,
  value JSONB,
  user_id INTEGER REFERENCES user (id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

To logging events we can use one of two approaches:

1. use database triggers to insert events to the logs table;
2. use a server side script to insert events to the logs table;

API to retrieve logs /v0/logs can accept http get method

Query parameter:
limit - optional default 1000
offset - optional
type - optional default all
user_ids - optional (example user_id=1,2,3)
after - optional default now - 1 hour

To authorize access to the logs table we can use one of two approaches:

1. use database Policy and Row Level Security
2. use a server side script

@blokhin @knopki

[blokhin]

@pyoner many thanks! To log events, I'd vote for:

1. use database triggers to insert events to the logs table;

To authorize access, I'd vote for retrieving the admin users and checking if the request issuer is among them, which is, apparently:

2. use a server side script

[blokhin]

Probably /v0/logs should use SSE for responding, shouldn't it?

[pyoner]

Probably /v0/logs should use SSE for responding, shouldn't it?

@blokhin admin can use SSE for listening real-time events

[blokhin]

partially implemented in https://github.com/basf/metis-bff/blob/master/routes/%5Bversion%5D/logs.js