35 lines
2.4 KiB
MySQL
35 lines
2.4 KiB
MySQL
|
create table clients
|
||
|
|
(
|
||
|
|
uuid varchar(36) default uuid() not null comment 'The UUID v4 ID for the client, null for anonymous users.',
|
||
|
|
enabled tinyint(1) default 1 not null comment 'Indicates if this client is enabled or not',
|
||
|
|
name text collate utf8mb4_unicode_ci not null comment 'Optional. Name of the client',
|
||
|
|
description text collate utf8mb4_unicode_ci null comment 'The optional description of the client',
|
||
|
|
secret_totp varchar(32) null comment 'The secret TOTP key used to generated authorization tokens for each request, if null then the client is not required to authenticate via providing generated authorization tokens',
|
||
|
|
query_permission tinyint(1) default 1 null comment 'The permission level set for the query permission
|
||
|
|
0 = Cannot query the database for content analysis
|
||
|
|
1 = Can query the database for content analysis
|
||
|
|
2 = Can query for additional sensitive information
|
||
|
|
3 = Can query and upate metadata',
|
||
|
|
update_permission tinyint(1) default 0 null comment 'The permission level set for the update permission
|
||
|
|
0 = Cannot update the database in anyway whatsoever
|
||
|
|
1 = Can update entity metadata
|
||
|
|
2 = Can submit evidence
|
||
|
|
3 = Can change entity flags & change entity association id',
|
||
|
|
flags varchar(255) null comment 'Comma separated flags associated with the client',
|
||
|
|
created_timestamp bigint default unix_timestamp() not null comment 'The Unix Timestamp for when this client was first registered into the database',
|
||
|
|
updated_timestamp bigint default unix_timestamp() not null comment 'The Unix Timestamp for when this client record was last updated in the database',
|
||
|
|
seen_timestamp bigint default unix_timestamp() not null comment 'The Unix Timestamp for when this client was last seen by the database',
|
||
|
|
constraint clients_uuid_uindex
|
||
|
|
unique (uuid)
|
||
|
|
)
|
||
|
|
comment 'A table for housing registered users/clients that can access the database';
|
||
|
|
|
||
|
|
create index clients_created_timestamp_seen_timestamp_updated_timestamp_index
|
||
|
|
on clients (created_timestamp, seen_timestamp, updated_timestamp)
|
||
|
|
comment 'Index for the client''s timestamps indicators';
|
||
|
|
|
||
|
|
create index clients_enabled_index
|
||
|
|
on clients (enabled)
|
||
|
|
comment 'Index for the client''s enabled state';
|
||
|
|
|