Database Documentation
Generated using DbSchema
ClientModule
Tables
public.quantity_kinds
public.roles
public.terms
public.units
public.uploads
public.user_relations
Table public.agent_relations
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 | id | bigserial | |
* ⬈ | subject_agent_id | integer | The agent table foreign key that identifies the subject agent in a unidirectional relationship (the term subject is derived from the RDF 1.1 Concepts and Abstract Syntax) |
* ⬈ | related_agent_id | integer | The agent table foreign key that identifies the agent related to the agent of interest in a unidirectional relationship |
relation_predicate | varchar(255) | Describes the nature of the relation between the subject and object (e.g., belongsTo memberOf), a subject - predicate - object relation | |
relation_type | varchar(255) | The high-level relationship classifier (e.g. Asymmetric) between the subject and object | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
subject_agent_role | varchar(255) |
Table public.agent_user
Establishes a relationship to authentication by related an Agent with a User. Since this establishes a relationship between two tables in separate modules and not all agents will be users, the relationship is stored in a separate table
Idx | Name | Data Type | Description |
|---|---|---|---|
* ⬈ | agent_id | integer | Agents table foreign key |
* ⬈ | user_id | integer | Users table foreign key |
remarks | text | General remarks regarding the relationship | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) |
Table public.agents
An agents is something that bears some form of responsibility for an activity taking place, for the existence of an entity, or for another agents activity within the context of a projects
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
display_name | varchar(255) | Name of agents for presentation purposes | |
phone_number | varchar(255) | The contact phone number for the agents with area code (XXX.YYY.ZZZZ) | |
varchar(255) | Email address of the agents | ||
street_address | varchar(255) | Street name and number of the agents mailing address | |
street_address_2 | varchar(255) | Second line for the street name and number of the agents mailing address if needed. | |
address_city | varchar(255) | City as written in the official postal address | |
address_state | varchar(255) | State as written in the official postal address | |
address_zip | varchar(20) | Zip code as written in the official postal address | |
address_country | varchar(255) | Country name as written in the agents mailing address | |
parent_id | integer | Primary key of parent agents to store parent-child relationships such as when a person is a member of an organization | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
deleted_at | timestamp(0) | ||
* ⬈ | agent_type_uuid | uuid |
Table public.document_access_group_user
Document access groups and user pivot table
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 | id | serial | |
* ⬈ | user_id | integer | |
* ⬈ | document_access_group_id | integer | |
* | access_level_id | integer DEFAULT 1 | Primary key to set the level of access to a document a user is granted |
created_at | timestamp(0) | ||
updated_at | timestamp(0) |
Table public.document_access_groups
Authorizes users to access specific shared documents using policies
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
group_name | varchar(255) | Reference name for the access group | |
slug | varchar(255) | Machine-friendly version of the access group name predominantly used as a URL parameter | |
policy_description | text | A description of the resource accessed by the policy, the rules that govern that access, and the conditions which the policy is applied | |
* ⬈ | document_id | integer | Document table foreign key |
created_at | timestamp(0) | ||
updated_at | timestamp(0) |
Table public.documentables
Idx | Name | Data Type |
|---|---|---|
* 🔎 | documentable_type | varchar(255) |
* 🔎 | documentable_id | bigint |
⬈ | document_id | integer |
created_at | timestamp(0) | |
updated_at | timestamp(0) | |
* | is_visible_client | boolean DEFAULT false |
Table public.documents
Documents associated with specific models within the scope of the project portal
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
title | varchar(255) | Formal title of the document as written in the source file | |
* ⬈ | upload_id | integer | Upload foreign key |
⬈ | parent_id | integer | Self referential primary key for parent-child relationships between documents |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
* | document_status | varchar DEFAULT 'New'::character varying | |
* ⬈ | owned_by | integer | Establishes a relation with the user table designating the owner of the document |
* ⬈ | document_type_uuid | uuid |
Table public.model_has_roles
Idx | Name | Data Type |
|---|---|---|
* 🔑 ⬈ | role_id | bigint |
* 🔑 | model_type | varchar(255) |
* 🔑 | model_id | bigint |
Table public.project_access_group_user
Project access groups and user pivot table
Idx | Name | Data Type |
|---|---|---|
* 🔑 | id | serial |
* 🔍 ⬈ | user_id | integer |
* 🔍 ⬈ | project_access_group_id | integer |
created_at | timestamp(0) | |
updated_at | timestamp(0) |
Table public.project_access_groups
Authorizes users to access specific projects using policies
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
group_name | varchar(255) | Reference name for the user group | |
slug | varchar(255) | Machine-friendly version of the access group name predominantly used as a URL parameter | |
⬈ | role_id | integer | |
* 🔍 ⬈ | project_id | integer | Project table foreign key |
created_at | timestamp(0) | ||
updated_at | timestamp(0) |
Table public.project_agent
Establishes a relation between a project and agent predicated by a specific role performed by the agent.
Idx | Name | Data Type | Description |
|---|---|---|---|
* ⬈ | agent_id | integer | |
* ⬈ | project_id | integer | |
start_date | varchar(255) | Date the relation between the project and agent began | |
end_date | varchar(255) | Date the relation between the project and agent ended | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
* 🔑 | id | serial | |
* | is_visible_client | boolean DEFAULT false | Determines the visibility of a project agent on the client side of the portal |
* ⬈ | agent_role_uuid | uuid |
Table public.project_feature_project
Pivot table joining Projects and Project Features
Idx | Name | Data Type |
|---|---|---|
* 🔑 | id | serial |
* 🔍 ⬈ | project_id | integer |
* 🔍 ⬈ | project_feature_id | integer |
created_at | timestamp(0) | |
updated_at | timestamp(0) |
Table public.project_features
Named featured types associated with a project.Examples: Indian Field 2 Watershed (Brosnan)
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
formal_name | varchar(255) | The formal, complete name of the named feature. Indian Field 2 Watershed | |
short_name | varchar(255) | The shortened form of a name, identified as an alternate preferred name of the feature, usually an acronym.. Example: IF2 | |
description | varchar(255) | General description of the project feature | |
remarks | varchar(255) | General remarks regarding the named feature | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
* ⬈ | feature_type_uuid | uuid |
Table public.project_measurements
The assignment of a number to a characteristic of a project, which can be compared with other projects. Each measurement consists of a value, a unit of measurement, and property (quantity kind).
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 | id | serial | |
value | double precision | quantity value representing a measurement result in numeric (float) format | |
description | text | Provides context for the measurement and provides information not covered by the existing set of attributes | |
measurement_date | date | Date the measurement was recorded | |
measured_by | varchar(255) | Name of person that recorded the measurement | |
* ⬈ | unit_id | integer | |
technique | varchar(255) | The techniques used to take the measurement | |
* ⬈ | quantity_kind_id | integer | |
label | varchar(255) | Fully qualified measurement record (feature, value, unit, property) for presentation purposes | |
feature_of_interest | varchar(255) | Subject of the observation that is being measured | |
* ⬈ | project_id | integer | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
* | is_visible_client | boolean DEFAULT false | |
⬈ | project_feature_id | integer | |
* ⬈ | feature_type_uuid | uuid |
Table public.project_stage_history
Record of project stage changes including the start and end date as described by formal project documentation
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 | id | bigint DEFAULT nextval('project_stage_history_id_seq1'::regclass) | |
start_date | date | Start date of the project stage recorded in projects deliverables | |
end_date | date | End date of the project stage recorded in projects deliverables | |
* | stage_index | integer DEFAULT 1 | Numeric index that stores the sequence of stages associated with a single project |
remarks | text | Generals remarks about the project stage change | |
updated_by | integer | User that updated the project stage | |
* | is_confirmed | boolean DEFAULT true | Verification that the project stage history record is correct and reflects the history of the project (not a mistake). |
* ⬈ | project_id | integer | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
* ⬈ | project_stage_uuid | uuid |
Table public.projects
A set of coordinated activities that is intended to achieve one or more objectives.
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
* 🔍 | title | varchar(191) | The formal, complete name of a project as written in legally binding documentation that is sufficient to differentiate the project from all others in an organization |
project_doc_path | varchar(255) | Full Path to project on LucidLink (internal cloud storage) | |
remarks | varchar(255) | ||
* | is_active | smallint DEFAULT '0'::smallint | Declaration of whether or not the project is active |
description | text | ||
🔍 | project_code | varchar(255) | Internal project identifier in the form XXX_NNNNN where XXX is a branch location code (e.g., RDU for Raleigh) |
country_code | varchar(255) | ISO3166-A3 country code | |
first_order_division | varchar(255) | Name of first order administrative division | |
second_order_division | varchar(255) | Name of second order administrative division | |
usace_district | varchar(255) | US Army Core of Engineers District. See https://www.usace.army.mil/Missions/Locations/ | |
contractual_start_date | date | The date a project began as written in the original contract. The value will always be a specific date (M/D/Y) per the contractual agreement | |
contractual_end_date | date | The legally binding date a project ended as written in the original contract. The value will always be a specific date (M/D/Y) per the contractual agreement | |
project_latitude | double precision | Latitude for the official spatial point for a project | |
project_longitude | double precision | Longitude for the official spatial point for a project | |
parent_id | integer | The foreign key of the parent of a child project. Null values are assigned to parent projects or projects that do not belong to parent-child hierarchies | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
🔍 | short_title | varchar(255) | A short and commonly used form of the formal title of the project. Commonly used for presentation purposes where visual space is limited |
abbreviation | varchar(255) | An abbreviate form of a project name formed by omitting parts from the formal project title that denotes the same concept. Example: Brosnan for Brosnan Forest | |
acronym | varchar(255) | An abbreviated form of the project name made up of the initial letters of the components of the full form of the project name. Example: BF for Brosnan Forest | |
deleted_at | timestamp(0) | ||
* | is_visible_client | boolean DEFAULT false | |
🔍 | slug | varchar | The lower, pascal-casing version of the project name primarily for use as a human-readable URL paramter that identifies the project |
* ⬈ | project_type_uuid | uuid | |
* ⬈ | project_stage_uuid | uuid |
Table public.quantity_kinds
Observable properties that can be measured and quantified numerically.
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
name | varchar(255) | Preferred name of the quantity kind | |
definition | varchar(255) | ||
qudt_quantitykind | varchar(255) | QUDT QuantityKind identifier | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
source_uri | varchar | ||
is_visible | boolean DEFAULT true |
Table public.roles
Idx | Name | Data Type |
|---|---|---|
* 🔑 ⬋ | id | bigserial |
* 🔍 | name | varchar(255) |
* 🔍 | guard_name | varchar(255) |
created_at | timestamp(0) | |
updated_at | timestamp(0) |
Table public.terms
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 | id | serial | |
* | pref_label | varchar(255) | Preferred name assigned to a resource in human-readable format |
alt_label | varchar(255) | Alternate name to resolve synonymy and heterogeneous term use | |
slug | varchar(255) | A machine-friendly version of the type that represents either the full path or terminal path segment of an HTTP(S) URL by which a resource can be located. | |
term_source | varchar(255) | Name or URI of the source for which the vocabulary originated where applicable | |
example | text | Supplies an example of the use of a concept (skos:example) | |
definition | text | Supplies a complete explanation of the intended meaning of the type | |
usage_note | text | Information on how the vocabulary should be used how it differentiates from other terms in the same vocabulary | |
remarks | text | Additional notes that better describe the term ideally comprised of information not addressed by the other term attributes | |
* | is_default | boolean DEFAULT false | Declaration of whether or not the term is the default value in the vocabulary |
* ⬈ | vocabulary_id | integer | Vocabulary the term belongs to |
* | is_visible | boolean DEFAULT true | Controls front-end visibility of a term |
parent_id | integer | Primary key of the parent domain value using a self-join | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
associated_model | varchar | Name of the type vocabulary model using upper-camel casing format for programmatic purposes (model scopes - https://github.com/tighten/parental) | |
type | varchar | ||
* 🔍 ⬋ | term_uuid | uuid | Secondary primary key |
Table public.units
A unit of measure, or unit, is a particular quantity value that has been chosen as a scale for measuring other quantities the same kind (more generally of equivalent dimension).
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
name | varchar(255) | Preferred name of the unit of measurement | |
abbreviation | varchar(255) | Abbreviated version of the unit of measurement | |
qudt_unit | varchar(255) | QUDT Unit identifier | |
created_at | timestamp(0) DEFAULT CURRENT_TIMESTAMP(0) | ||
updated_at | timestamp(0) DEFAULT CURRENT_TIMESTAMP(0) | ||
unit_plural_label | varchar(255) | Unit in plural form for presentation purposes | |
definition | varchar | ||
source_uri | varchar | Unform resource identifier for the source term if applicable | |
is_visible | boolean DEFAULT true | Property to designate if a resource is presented to the user through the frontend of the application |
Table public.uploads
Uploaded Files
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | serial | |
* | original_filename | varchar(255) | Original filename prior to unique name assignment |
* | filename | varchar(255) | Unique name assigned to the file after upload for the purposes of preventing a filename conflict between two uploaded files |
remarks | text | General remarks regarding the uploaded file provided by the user | |
* | filepath | varchar(255) | Absolute path of uploaded file on the server without the filename |
size | double precision | The size of the resource with regard to the size_unit measurement in form of a number | |
size_unit | varchar(255) | The unit of measurement used for determining and describing the size of a resource (part) | |
⬈ | uploaded_by | integer | The user who uploaded the file |
* | url_path | varchar(255) | Web-accessible path to the uploaded file |
visibility | varchar(255) | Visibility of upload per the Storage facade | |
thumbnail | varchar(255) | ||
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
mime_media_type | varchar(255) | The format of the file as provided by the IANA mimetypes https://www.iana.org/assignments/media-types/media-types.xhtml | |
* ⬈ | upload_type_uuid | uuid |
Table public.user_relations
Defines binary, ordered relationships between two users. See https://spec.edmcouncil.org/fibo/ontology/FND/Relations/Relations/
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 | id | bigserial | |
* ⬈ | subject_user_id | integer | The user table foreign key that identifies the subject user in a unidirectional relationship (the term subject is derived from the RDF 1.1 Concepts and Abstract Syntax) |
* ⬈ | related_user_id | integer | The user table foreign key that identifies the user related to the user of interest in a unidirectional relationship (the term object is derived from the RDF 1.1 Concepts and Abstract Syntax) |
relation_predicate | varchar(255) | Describes the nature of the relation between the subject and object (e.g., belongsTo memberOf), a subject - predicate - object relation | |
relation_type | varchar(255) | The high-level relationship classifier (e.g. Asymmetric) between the subject and object | |
created_at | timestamp(0) | ||
subject_user_role | varchar(255) | The function of a subject user with respect to the related user within the context of a user relation | |
updated_at | timestamp(0) |
Table public.users
Idx | Name | Data Type |
|---|---|---|
* 🔑 ⬋ | id | bigserial |
* | fname | varchar(255) |
* | lname | varchar(255) |
* | organization | varchar(255) |
* 🔍 | varchar(255) | |
email_verified_at | timestamp(0) | |
* | password | varchar(255) |
remember_token | varchar(100) | |
* | activated | boolean DEFAULT false |
created_at | timestamp(0) | |
updated_at | timestamp(0) | |
deleted_at | timestamp(0) | |
welcome_valid_until | timestamp(0) | |
🔍 | api_token | varchar(80) |
Table public.vocabularies
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | integer DEFAULT nextval('vocabularies_id_seq'::regclass) | |
🔍 | title | varchar(255) | Human-readable name that identifies the vocabulary |
🔍 | model | varchar(255) | Name of the associated model in upper camel casing without the namespace path prefix. |
column_slug | varchar(255) | Variation of the vocabulary name to construct foreign keys | |
description | text | General account of the vocabulary including the scope and origins | |
usage_note | varchar(255) | Information on how the vocabulary should be used within the context of the portal. | |
parent_id | integer | Primary key of the parent vocabulary to establish a parent - child relationship | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) | ||
slug | varchar(255) | Slug variation of the type vocabulary name | |
* ⬈ | vocabulary_type_id | integer | |
associated_model | varchar(255) | The model name, in upper camel-casing, associated with the vocabulary |
Table public.vocabulary_types
A set of atomic scalar values for categorizing vocabularies
Idx | Name | Data Type | Description |
|---|---|---|---|
* 🔑 ⬋ | id | integer DEFAULT nextval('vocabulary_types_id_seq1'::regclass) | |
* 🔍 | type_uuid | uuid DEFAULT gen_random_uuid() | |
* | pref_label | varchar(255) | Preferred name assigned to a resource in human-readable format |
slug | varchar(255) | Machine-friendly version of the type that represents either the full path or terminal path segment of an HTTP(S) URL by which a resource can be located. | |
definition | text | Supplies a complete explanation of the intended meaning of the type | |
source | varchar(255) | Name or URI that identifies the source of the type vocabulary term | |
usage_note | text | Description of the context in which the type is used and how it differentiates from other terms in the same vocabulary | |
remarks | text | General comments regarding the resource not covered by the existing set of attributes | |
created_at | timestamp(0) | ||
updated_at | timestamp(0) |