Database

Overview

We separate the user database from the other database that the services have access to. All information from the separated users database is passed to functions that need that information by using the JWT.

---

Users database

The users database is used for authentication upon log in and responsible for the authorization for the requests since it helps to bind the user's role to the JWT.

Users' Tables

---

Users

Column	Type	Description
UserId	UUID	This acts like the id so don't have to expose sensitive information
UserEmail	VARCHAR(255)	User's unique email address
UserName	VARCHAR(100)	User's display name
UserRole	User_Role_Enum	Role: 'ADMIN','INVESTIGATOR','USER'
UserPassword	VARCHAR(255)	Hashed Password
UserJWTIssued	TIMESTAMPTZ	Time of last Issued JWT

---

Cases database

This contains information that is needed for services related to cases.

Cases' Tables

---

MediaType

Column	Type	Description
MediaTypeId	UUID	Unique Identifier
MediaName	varchar(100)	Human readable identifier
MediaExtension	varchar(10)	The file type e.g. .png
MediaBucket	VARCHAR(255)	The storage bucket location for the media.

---

Cases

Column	Type	Description
CaseId	UUID	Unique identifier
CaseName	VARCHAR(255)	Name of case
CaseCreator	VARCHAR(100)	Name of creator
CaseDescription	TEXT	The description of the case
CaseClosed	Boolean	true or false to show if case solved
CaseCreationDate	TIMESTAMPTZ	Timestamp of creation

---

Media

Column	Type	Description
MediaId	UUID	Unique identifier.
MediaType	UUID	Referencing Mediatype table
MediaUploadDate	TIMESTAMPTZ	Timestamp of media uploaded.
MediaAnnotations	JSONB	The annotations that are overlayed onto the images by investigators
MediaHash	VARCHAR(255)	Hash value for the uploaded media.

---

Reports

Column	Type	Description
ReportId	UUID	Primary key; automatically generated unique identifier.
CaseId	UUID	Foreign key referencing CaseId in the Cases table; cascades on both delete and update; cannot be null.
ImageId	UUID	Foreign key referencing MediaId in the Media table; cascades on update; cannot be null.
ImageTitle	TEXT	This is name of the file when it was uploaded
ReportArtifacts	JSONB	Semi-structured JSON data containing report artifacts.
ReportFindings	TEXT	Detailed text findings for the report.
ReportComments	TEXT	Additional comments or notes for the report.
ReportDateCreation	TIMESTAMPTZ	Timestamp of when the report was created; defaults to the current time with time zone.
ReportCertainty	SMALLINT	Can only be 0, 1,2,3 and it is the comparable as an int

There is enforce uniqueness with the combination of the ImageId and CaseId since there can not be two images that are the same in one case.

---

Comments

This holds the comments left from case reviews

Column	Type	Description
CommentID	BIGINT	A serial primary key.
CaseId	UUID	Foreign key that links to the case table
Username	varchar(100)	The username of the individual who wrote the comment
Comment	Text	The comment
CommentTimestamp	TIMESTAMPTZ	The timestamp saying when comment was created

---