3. Database model

Introduction

For the design of the data model, a separation was established between the following aspects:

1. sensor network
2. timeseries
3. users and roles

Through the conceptual model based on the E-R (Entity-Relationships) scheme, the subjects to be described (the Entities) and any relationships between them (Relationships) are identified.

Sensor network and timeseries

This aspect concerns the net management and the mapping of net's channels with timeseries.

Conceptual model

Sensor network

Station and Channel

Each node of the network is a station (Station) to which one or more acquisition channels (Channel) are associated.

The mapping between the sensor network and a timeseries pass from channels: a time series is linked to zero, one or more channels.

Net, Site, Owner

For each station, the name, the geographical coordinates, the network to which it belongs (Net), and the installation site (Site) are stored.

The site is a specific geographical point with a reference name, which is associated with the installation of one or more stations, and is mainly represented by its coordinates, which can be punctual or relative to an area. For each network it is also possible to identify an owner (Owner).

Sensor, Digitizer, Station_config

The relationship between station and channels is not direct, but passes through the sensor entity (Sensor) and the related (if any) digitizer (Digitizer). In fact, over time, the station instrumentation can be updated. Each update of the sensor and/or digitizer settings produces a new set of channels.

The list of channels of a station is therefore associated with a very specific configuration (Station_config) identified by the tuple (Sensor, Digitizer, start date, end date), to trace the history of the station instrumentation over time.

Sensortype, Digitizertype

There are different types of sensors (Sensortype) from which the installed instances inherit the structure of the main properties (e.g. model, number of components, response parameters). The sensortypes can be grouped into classes, based on certain common characteristics, defining categories (Sensortype_category) from which the typologies inherit the structure of the descriptive information.

Similarly, there are different types of digitizers (Digitizertype) from which the installed instances inherit the structure of the main properties (e.g. model, sampling frequency, sensitivity, etc.). Both the sensor and the digitizer mainly take into account the assigned name, the serial number and the type.

Timeseries

The main resource of the TSDSystem are timeseries (Timeseries). For each timeseries, it is useful to define and save:

• the name,
• the type of values (usually numeric) of which it is composed,
• the sampling rate,
• if in the public domain,
• whether the times are saved with reference to the time zone (Time Zone Definition – TZD), always compatible with the ISO-8601 standard (https://www.w3.org/TR/NOTE-datetime) or not (in this case the times are treated as UTC),
• perhaps other configuration parameters for plotting or state identification (thresholds).

Entity-Relationships Diagram

Physical design with PostGreSQL

List of schemas -> tables:

• tsd_pnet
  • owners
  • nets
  • sites
  • stations
  • station_configs
  • channels
  • sensors
  • sensortypes
  • sensortype_categories
  • digitizers
  • digitizertypes
• tsd_main
  • timeseries
  • timeseries_mapping_channels

Note: For tables owners, nets, stations, sites, sensors, sensortypes, sensortype_categories, digitizers, digitizer_types, station_configs, channels, timeseries, in addition to the fields described in the diagrams, the following have been included:

• create_time timestamp without time zone DEFAULT (now() AT TIME ZONE 'utc'::text),
• update_time timestamp without time zone,
• remove_time timestamp without time zone,
• create_user integer,
• update_user integer,
• remove_user integer

Users and roles

This aspect concerns the management of users permissions using the RBAC (Roles Based Access Control) model.

Conceptual model

TSDSystem exposes a REST API to perform writing and reading operations.

To obtain control over the required operations it is necessary to define the user authentication system (user authentication) and the resource access policies through authorization systems (user authorization).

Regarding the resource access policies, the RBAC (Role Based Access Control) design pattern was followed. The RBAC mechanism is based on the concepts of role and privilege. The object of privileges, in our case, is each resource that can be interfaced by the REST service. The main subjects are: resources, roles (Roles) and permissions (Permissions):

• The resources are connected to the entities of the conceptual data model and are those accessible by the REST service.
• Users are linked to roles.
• Each role has a set of permissions defined on the resources. The permissions can be mainly distinguished for read and/or write operations. For some resources, such as timeseries, permissions can be limited to a specific time period.

Entity-Relationships Diagram

Physical design with PostGreSQL

List of schemas -> tables:

• tsd_users
  • members (registered users)
  • roles
  • member_mapping_roles
  • members_permissions (permissions can be linked directly to users)
  • roles_permissions
  • tokens (not included into E-R diagram - service table for logging all JWT tokens generated for the utilization of REST API)
  • temp_reset_keys (not included into E-R diagram - service table for user reset password)

Note: create_time, update_time and remove_time fields have been included also into tables roles, roles_permissions, members_permissions, member_mapping_roles.

Predefined roles

Some predefined roles are registered during installation, such as:

• Admin user who has administration privileges on all resources;
• Network reader who can read all information about sensor network data;
• Timeseries editor who can read e write timeseries

See codelines 136-146 in https://github.com/ingv-oe-dev/tsdsystem/blob/master/docker/timescale/sql/tsd_users.sql)

Permissions model

There are two types of permissions stored into members_permissions and roles_permissions:

1. specific for the manipulation of resources such as: owners, nets, sites, stations, sensortypes, sensors, digitizers, digitizertypes, channels, timeseries, which in turn are specified separately for reading and for writing.
2. administrative for the management of roles, members, their mapping (member_mapping_roles) and the relative permissions (members_permissions and roles_permissions), as well as operations on the resources.

The permission model is defined by the JSON schema at: https://github.com/ingv-oe-dev/tsdsystem/blob/master/src/tsdws/json-schemas/resource_permissions.json

Note: In the proposed JSON schema, permissions defined at resource level has the following subsections:

• enabled <boolean> - which generally indicates whether enabled or not
• ip <array> - where you can enter specific IP addresses enabled to make the request
• permissions <object> - where you can enable permission on specific resource IDs

Permissions can be stored directly into the payload of the JWT authorization tokens when these last are used to perform automatical action through the REST API.

Checking timeseries permissions

Special mention must be made of the timeseries resource section. For this resource, the read->permissions section adds more details to specify a permission on a limited period of the timeseries through the fields:

• start_period <timestamp>,
• end_period <timestamp>,
• number_of_days <integer>,
• last_days <boolean>

Edit

Permissions structure:

"edit": {
	"enabled": true,
	"ip": [],
	"permissions": {
		"net_id": [], // o null
		"station_id": [], // o null
		"channel_id": [], // o null
		"id" : [] // o null
	}
}

Permission checks will be carried out from the authorization system as follows.

Step 1

Check if edit->enabled = true

Step 2

Check if the IP of the client performing the request is into edit->ip list (empty list equals to all ip)

Step 3

If edit->permissions section does not exist, all writes are available, else:

• Check if there is a restriction on specific timeseries by id and then that the id of the requested timeseries is into edit->permissions->id list
• Else check timeseries dependencies in the following order (channels, stations, nets to which the timeseries can be related):
  1. else check if edit->permissions->channel_id list is not empty and that the timeseries is related at least with one listed channel id
  2. else check if edit->permissions->station_id list is not empty and that the timeseries is related at least with one listed station id (passing from channels)
  3. else check if edit->permissions->net_id list is not empty and that the timeseries is related at least with one listed net id (even passing from channels)

Read

Permissions structure:

"read": {
	"enabled": true,
	"ip": [],
	"permissions": [{
		"target": <string> // one of ["all", "nets", "stations", "channels", "timeseries"]
		"id": [] // list of IDs for the selected target
		“priority”: <integer> 
		"settings": {
			"start_period": <datetime>,
			"end_period": <datetime>,
			"number_of_days": <integer>,
			"last_days": <boolean>
		}
	}]
}

Permission checks will be carried out from the authorization system as follows.

Step 1

Check if read->enabled = true

Step 2

Check if the IP of the client performing the request is into read->ip list (empty list equals to all ip)

Step 3

If read->permissions section does not exist, all reads are available, else:

• Check if there is a restriction on specific timeseries by id and then that the id of the requested timeseries is into read->permissions->id list
• Else check timeseries dependencies in the following order (channels, stations, nets to which the timeseries can be related):
  1. else check if read->permissions->channel_id list is not empty and that the timeseries is related at least with one listed channel id
  2. else check if read->permissions->station_id list is not empty and that the timeseries is related at least with one listed station id (passing from channels)
  3. else check if read->permissions->net_id list is not empty and that the timeseries is related at least with one listed net id (even passing from channels)
  4. finally check if there is a permission defined for all cases (read->permissions->all section)
  5. otherwise the default permission is selected (last day of the requested timeseries):

   {
     "start_period": null,
     "end_period": null,
     "number_of_days": 1,
     "last_days": true
   }
  

Other

List of other schemas -> tables used in the physical design with PostGreSQL:

• public
• <all schemas created by Timescale extension>