Annotation Database Entity Documentation

The database is large, complex and relational with a lot of entities describing many facets of ROV operations, measurements and observations.

Notes About Specific Entities

These notes about specific entities complement the generated documentation below.

rov.dive and rov.transect

Dives represent the span of time during which an ROV, submersible or drop camera is in the water, but may also represent the time during which the instruments are operating. In cases when the instrument data are contained entirely within the time span logged by the operator, the times are recorded unchanged. When the instrument data extend beyond the recorded times of the dive, they may be extended. Occasionally, the operator will record an erroneous time, or omit a time altogether. The instrument times can help to reconstruct this history. Dives also relate to important configuration information, objectives, crew and operational notes.

Transects are defined by their start and end times, contained entirely within dives and do not cross the boundaries between dives. They do not have any related entities and function mainly as accounting items. However, they are extremely important in the analysis of observations and measurements collected during the dive. For example, if one is calculating the density of a particular population of organisms, one must know the precise geographic extent of the region. Transects accomplish this and store objectives and operational notes.

In instances where the times of transects are not recorded, or lost, they can sometimes be reconstructed from status events in the observation record (e.g., a VideoMiner database may have transect start/end events). Otherwise, they are not recorded at all. Ideally, transect records are present and their start and end times are recorded without modification, so long as they are reasonable (i.e., the end time occurs after the start time).

Generated Documentation

Database entity documentation is generated from the production database by the script, db_generate_docs.py. The script outputs a file whose contents can be copied into this page after the <!-- BEGIN GENERATED CONTENT --> tag.

The documentation is arranged into conceptual groupings with the following structure.

• Schema
  • Table
  • Materialized View
  • View

System and Django tables aren't included. The ID column of each table is also excluded.

Database Comments

Documentation resides in the database itself, in the form of comments on the relations and columns. Documentations can be created by executing SQL statements in the form,

   COMMENT ON SHCEMA [schema] IS 'This is a schema comment.'
   COMMENT ON TABLE [schema].[table name] IS 'This is a table comment.'
   COMMENT ON COLUMN [schema].[table name].[column name] IS 'This is a column comment.'

Comments are managed using DDL, as described on the Database Upgrades page.

Generated Entity Documentation

Note: this page is auto-generated from the documentation comments in the actual database. Do not edit it directly.

Schema: intertidal

Stores data for the intertidal database. Contains information about UAS flights, surveys and other derivative data.

Table: quadrat

A record of observations within an intertidal quadrat.

Columns in "quadrat"

Name	Type	Nullable	Documentation
centroid_height	real	YES	The elevation of the corner position in m (ellipsoidal).
centroid_lat	double precision	YES	The corner latitude.
centroid_lon	double precision	YES	The corner longitude.
corner_height1	real	YES	The elevation of the corner position in m (ellipsoidal).
corner_height2	real	YES	The elevation of the corner position in m (ellipsoidal).
corner_height3	real	YES	The elevation of the corner position in m (ellipsoidal).
corner_height4	real	YES	The elevation of the corner position in m (ellipsoidal).
corner_lat1	double precision	YES	The corner latitude.
corner_lat2	double precision	YES	The corner latitude.
corner_lat3	double precision	YES	The corner latitude.
corner_lat4	double precision	YES	The corner latitude.
corner_lon1	double precision	YES	The corner longitude.
corner_lon2	double precision	YES	The corner longitude.
corner_lon3	double precision	YES	The corner longitude.
corner_lon4	double precision	YES	The corner longitude.
created_on	timestamp without time zone	NO	created_on = models.DateTimeField(default = datetime.utcnow, null = False)
flag_number	character varying	NO	The number of the flag used to mark the quadrat. May be non-numeric if necessary.
geom	geometry	YES	A geometry representing either the corners of the quadrat (multipoint) or the centroid.
name	character varying	NO	A unique name for the quadrat.
note	text	YES	An optional note about the quadrat.
survey_id	integer	NO	A reference to the intertidal suvey.
time	timestamp without time zone	NO	The time of the quadrat (UTC).
transect	integer	NO	The transect number. This is an integer corresponding to whatever rubric was chosen for the survey.
zone	integer	NO	The zone number. This is an integer corresponding to whatever rubric was chosen for the survey.

Table: quadrat_observation

Records observations within quadrats. The observation type enumeration is defined in the database export panel.

Columns in "quadrat_observation"

Name	Type	Nullable	Documentation
cell	integer	YES	The quadrat cell, typically from 0 - 100.
count	integer	YES	The number of organisms observed.
coverage	real	YES	The percent coverage by the organism, 0-100. TODO: Not part of the specification.
length	real	YES	The measured length of an organism.
quadrat_id	integer	NO	A link to the quadrat.
surveytaxon_id	integer	NO	A link to the survey taxon.
type	integer	YES	The type of observation; one of quadrat_obs_type.

Table: quadrat_substrate

List substrates observed in intertidal quadrats.

Columns in "quadrat_substrate"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the substrate.

Table: quadrat_substrate_coverage

Associates quadrat substrate coverages with quadrats.

Columns in "quadrat_substrate_coverage"

Name	Type	Nullable	Documentation
coverage	real	NO	The coverage amount between 0% and 100%.
quadrat_id	integer	NO	A reference to the quadrat.
substrate_id	integer	NO	A reference to the substrate.

Table: survey

Stores metadata about intertidal surveys.

Columns in "survey"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The time of creation of this record.
end_date	timestamp without time zone	YES	The end date of the survey.
name	character varying	NO	The name of the survey.
note	text	YES	Notes about the survey.
objective	text	YES	The survey objective.
site_id	integer	YES	Relates the survey to a site entity.
start_date	timestamp without time zone	NO	The start date of the survey.

Table: survey_crew

Associates intertidal survey crew members with roles.

Columns in "survey_crew"

Name	Type	Nullable	Documentation
note	text	YES	An optional note about the survey role.
person_id	integer	NO	A reference to the person with this role.
role_id	integer	NO	A reference to the role.
survey_id	integer	NO	A reference to the survey.

Table: survey_role

A list of roles available to intertidal survey crew members.

Columns in "survey_role"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the role.
note	text	YES	A note about the role.

Table: survey_taxon

Links taxon records to intertidal surveys.

Columns in "survey_taxon"

Name	Type	Nullable	Documentation
survey_id	integer	NO	A link to the survey.
taxon_id	integer	NO	A link to the taxon record.

Schema: ndst

Contains tables that store transient data loaded from the Dive Logging App so it can be loaded into the ROV database.

Table: cruise

Contains information about a cruise, as entered by NDST staff. Will be combined with other data to populate the cruise table.

Columns in "cruise"

Name	Type	Nullable	Documentation
active	boolean	NO	Set to true if the entity is active, that is, if it's currently being used. If not, set to false.
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
hide	integer	NO	True if the entity should be hidden in the UI.
leg	text	YES	The leg of the cruise. Should start at 1 and increase.
name	text	YES	The name of the cruise.
note	text	YES	The objective of the cruise.
objective	text	YES	[None]
row_id	text	YES	A UUID providing a universally-unique identifier for the cruise.
status	character varying	YES	The current status of the cruise viz. database import.
summary	text	YES	A summary of the cruise, its accomplishments, problems, etc.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.

Table: diveconfig

Stores configurations for each dive, including the submersible, ship and any instruments on either.

Columns in "diveconfig"

Name	Type	Nullable	Documentation
active	boolean	NO	Set to true if the entity is active, that is, if it's currently being used. If not, set to false.
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
name	text	YES	The name of the dive config.
note	text	YES	A note about the dive config.
row_id	text	YES	A UUID providing a universally-unique identifier for the entity.
ship_config	text	YES	Stores the name of the ship configuration during the dive.
ship_instrument_configs	text	YES	Stores the names of the instruments on the ship during the dive.
sub_config	text	YES	Stores the name of the submersible configuration during the dive.
sub_instrument_configs	text	YES	Stores the names of the instruments on the sub during the dive.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.

Table: dives

Stores information about each dive.

Columns in "dives"

Name	Type	Nullable	Documentation
active	boolean	NO	Set to true if the entity is active, that is, if it's currently being used. If not, set to false.
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
cruise_name	text	YES	The name of the cruise.
dive_config	text	YES	The name of the dive configuration.
end_time	text	YES	The time of dive ending.
hide	integer	NO	True if the entity should be hidden in the UI.
leg	text	YES	The leg of the cruise.
name	text	YES	The name of the dive.
note	text	YES	A note about the dive.
objective	text	YES	The objective of the dive.
pilot	text	YES	The pilot(s) of the dive.
row_id	text	YES	A UUID providing a universally-unique identifier for the entity.
site_name	text	YES	The name of the dive site.
start_time	text	YES	The time of dive start.
summary	text	YES	A summary of the dive, its accomplishments, problems, etc.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.

Table: equipconfig

Stores configurations for equipment, including instruments and platforms.

Columns in "equipconfig"

Name	Type	Nullable	Documentation
active	boolean	NO	Set to true if the entity is active, that is, if it's currently being used. If not, set to false.
configuration	text	YES	A JSON object containing configuration properties.
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
name	text	YES	The name of the equipment config.
note	text	YES	A note about the equipment config.
row_id	text	YES	A UUID providing a universally-unique identifier for the entity.
short_code	text	YES	A short, easy-to-use identifier for the configuration.
type	text	YES	The type of equipment.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.

Table: equipment

Stores equipment, including instruments and platforms.

Columns in "equipment"

Name	Type	Nullable	Documentation
active	boolean	NO	Set to true if the entity is active, that is, if it's currently being used. If not, set to false.
brand	text	YES	The brand name of the equipment.
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
instrument_id	integer	YES	The ID of the instrument in the ROV database. Mutually exclusive with platform_id.
model	text	YES	The model name of the equipment.
note	text	YES	A note about the equipment.
platform_id	integer	YES	The ID of the platform in the ROV database. Mutually exclusive with instrument_id.
row_id	text	YES	A UUID providing a universally-unique identifier for the entity.
serial_number	text	YES	The serial number.
short_code	text	YES	The short code for the equipment as used in the ROV database equipment table.
short_code_mapped	character varying	YES	An optional mapping from the origin short code to an internal short code. Not unique because multiple source items can map to a single internal item.
type	text	YES	The type of equipment.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.

Table: people

Stores the people who worked on the cruise.

Columns in "people"

Name	Type	Nullable	Documentation
active	boolean	NO	Set to true if the entity is active, that is, if it's currently being used. If not, set to false.
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
email	text	YES	The email address.
first_name	text	YES	The first name.
initials	text	YES	The initials. Used like a short code for the person.
last_name	text	YES	The last name.
person_id	integer	YES	The ID of the person in the ROV database.
row_id	text	YES	A UUID providing a universally-unique identifier for the entity.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.

Table: transects

Stores information about each transect.

Columns in "transects"

Name	Type	Nullable	Documentation
active	boolean	NO	Set to true if the entity is active, that is, if it's currently being used. If not, set to false.
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
cruise_name	text	YES	The name of the cruise.
dive_name	text	YES	The name of the dive.
end_time	text	YES	The time of transect ending.
hide	integer	NO	True if the entity should be hidden in the UI.
leg	text	YES	The leg of the cruise.
name	text	YES	The name of the transect.
note	text	YES	A note about the transect.
objective	text	YES	The objective of the transect.
row_id	text	YES	A UUID providing a universally-unique identifier for the entity.
start_time	text	YES	The time of transect start.
summary	text	YES	A summary of the transect, its accomplishments, problems, etc.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.

Schema: rov

The main schema of the ROV database, stores information about all relevant entities including cruise metadata, personnel, dives, transects, equipment configuration, telemetry, water properties and observations.

Table: abundance

Stores a list of abundance labels from the ACFOR scale.

Columns in "abundance"

Name	Type	Nullable	Documentation
name	character varying	NO	A descriptive label for the abundance level.
note	text	YES	An optional note about the abundance level.
rank	integer	YES	A rank used to objectively interpret the abundance label as an ordinal rank. It doesn't matter what the values are so long as they increase monotonically and do not overlap.
short_code	character varying	YES	Contains a short code that can be used to look up an abundance (e.g., during import) without relying on the primary key.
source	character varying	NO	A source label to distinguish abundance labels with the same names. TODO: Provisional, pending determination of how abundance codes are handled.

Table: annotation_job

Stores information about the annotation projects that generate information from photo and video media.

Columns in "annotation_job"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	YES	The date of creation of the entity.
end_date	date	YES	The ending date of the project.
name	character varying	NO	A name for the annotation project.
note	text	YES	Operational notes about the annotation project.
objective	text	YES	A statement of the objectives of the annotation project.
start_date	date	YES	The starting date of the project.
updated_on	timestamp without time zone	YES	The date of last modification of the entity.

Table: annotation_job_annotation_protocol

Associates the annotation job with one or more annotation protocols, which are used to direct annotation activities.

Columns in "annotation_job_annotation_protocol"

Name	Type	Nullable	Documentation
annotation_job_id	integer	NO	The ID of the annotation job.
annotation_protocol_id	integer	NO	The ID of the annotation protocol.

Table: annotation_job_crew

Associates crew members with an annotation job with their respective roles.

Columns in "annotation_job_crew"

Name	Type	Nullable	Documentation
annotation_job_id	integer	NO	The ID of the annotation job.
person_id	integer	NO	The ID of the crew member.
role_id	integer	NO	The ID of the role.

Table: annotation_job_role

Stores the possible roles one can perform on an annotation job.

Columns in "annotation_job_role"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the role.
note	text	YES	A descript of the role.

Table: annotation_protocol

A table to record annotation protocols for annotation projects. Stores information such as the author of the protocol, the observation interval, the medium used and the types of observations to be made.

Columns in "annotation_protocol"

Name	Type	Nullable	Documentation
algae_species	character varying	YES	Whether all algae species are identified: "all", "subset" or "none".
annotation_software_id	integer	NO	Software used to annotate the video or images. Looked up in the annotation software table.
biogenic_habitat	boolean	YES	True if any habitat categories include fauna (e.g., sponge reefs).
created_on	timestamp without time zone	NO	The date of creation of this record.
creator_id	integer	YES	Records the identity of the person who created this protocol.
fish_species	character varying	YES	Whether all fish species are identified: "all", "subset" or "none".
fov_interval	real	YES	The time interval between field-of-view measurements.
fov_interval_unit	character varying	NO	A unit for the field of view interval, such as 's' for seconds or 'm' for metres.
habitat_interval	real	YES	The time interval between habitat observations.
habitat_interval_unit	character varying	NO	A unit for the habitat interval, such as 's' for seconds or 'm' for metres.
habitat_only	boolean	YES	Set to true if only habitat variables were recorded.
image_interval	real	YES	The interval between images or frame grabs. Typically 3 to 10 seconds.
image_interval_unit	character varying	NO	A unit for the image interval, such as 's' for seconds or 'm' for metres.
image_overlap	boolean	YES	Is there overlap between the images (true) or do they represent independent non-overlapping space (false).
invertebrate_species	character varying	YES	Whether all invertebrate species are identified: "all", "subset" or "none".
is_template	boolean	YES	If this is meant to be a template that is copied for use, mark this column `true`.
medium_type_id	integer	NO	The type of media used for annotation. Looked up in media type table.
name	character varying	NO	The name of the annotation protocol. Should be unique.
note	text	YES	If only a subset of invert, fish or algae species were identified, note here which groups were the primary focus (target species). For example, benthic fish or corals and sponges. Also for other notes of interest.
observation_interval	real	YES	The time interval between species or anthropogenic observations.
observation_interval_unit	character varying	NO	A unit for the observation interval, such as 's' for seconds or 'm' for metres.
protocol_document	character varying	YES	Link, title, DOI, etc. of a document describing the protocol in full.
species_guide	character varying	YES	A URL to the iNaturalist species guide that was used for annotation.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: annotation_protocol_document

Contains documents related to an annotation protocol. These can be a URL or actual file data. If the file data are present in the database, the URL can still be used to provide the origin of the file, etc.

Columns in "annotation_protocol_document"

Name	Type	Nullable	Documentation
annotation_protocol_id	integer	NO	A reference to the annotation protocol.
created_on	timestamp without time zone	NO	The time of creation of the record.
file_id	integer	YES	A reference to the uploaded file.
note	text	YES	An optional note about the document.
title	character varying	NO	The title of the document.
updated_on	timestamp without time zone	NO	The time of update of the record.
url	character varying	YES	An optional URL for the document. If file data are not given, this field is required.

Table: annotation_software

The software used for annotation.

Columns in "annotation_software"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the annotation software.
note	text	YES	An optional note about the annotation software.

Table: biigle_label_map

Contains mappings from Biigle labels to properties that apply to events.

Columns in "biigle_label_map"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The time of creation of the record.
label_hierarchy	text	NO	The full hierarchical text of the Biigle label.
label_id	integer	NO	The ID of the Biigle label.
label_text	text	NO	The text of the final element of the label.
label_tree_id	integer	NO	The ID of the Biigle label tree.
note	text	YES	A textual note or comment.
properties	jsonb	NO	Stores the properties of the observation.
updated_on	timestamp without time zone	NO	Time of last update of the record.

Table: biigle_label_map_restriction

Links a Biigle label map to a [restriction][#shared.restriction].

Columns in "biigle_label_map_restriction"

Name	Type	Nullable	Documentation
biigle_label_map_id	integer	NO	A link to the label map.
restriction_id	integer	NO	A link to the restriction.

Table: biocover

A lookup table listing the available biocover types for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity. TODO: Should perhaps refer to the taxon table.

Columns in "biocover"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the biocover.
note	text	YES	An optional description of the biocover.
short_code	character varying	YES	Contains a short code that can be used to look up a biocover (e.g., during import) without relying on the primary key.

Table: complexity

A lookup table listing the available habitat complexity types for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity.

Columns in "complexity"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the complexity type.
note	text	YES	An optional description of the complexity type.
short_code	character varying	YES	Contains a short code that can be used to look up a complexity (e.g., during import) without relying on the primary key.

Table: coverage

A lookup table listing the percentage of coverage for the habitat_event table. The coverages are given as ranges so the text of the level is given in the name field and the values in the min and max fields contain the bounding values.

Columns in "coverage"

Name	Type	Nullable	Documentation
maximum	real	NO	The minimum value in the range.
minimum	real	NO	The minimum value in the range.
name	character varying	NO	A characterization of percent coverage. Presented as a range of percentages.
note	text	YES	An optional note about the coverage percentage.
short_code	character varying	YES	Contains a short code that can be used to look up a coverage (e.g., during import) without relying on the primary key.

Table: cruise

Cruise legs occur within a cruise and are assigned specific crews, scientific programs, etc. A single leg can cover an entire cruise, legs can be spaced end-to-end, or can theoretically overlap. Legs can share members or equipment, and crew members can have specific roles related to a leg. Cruise legs can be created without a scientific program or a cruise because imported data sets may only list the name of the cruise and not indicate whether it was part of a longer cruise.

Columns in "cruise"

Name	Type	Nullable	Documentation
admin_note	text	YES	Notes by the database administrator about this entity.
approved	integer	NO	If zero, the record is not approved and should not be shown or used. Currently a non-zero value represents approval, but may be expanded to various levels of approval in the future.
created_on	timestamp without time zone	NO	The date of creation of this record.
end_time	timestamp without time zone	YES	The end time of the leg.
leg	integer	NO	Cruise legs are numbered from 1.
mark_for_delete	boolean	NO	Marks the entity for asynchronous deletion by the runner.
name	character varying	NO	A name for this leg of the cruise.
note	text	YES	Notes about the cruise.
objective	text	YES	A statement of the operational or scientific objectives of the cruise.
planned_track	USER-DEFINED	YES	A multilinestring containing the planned track of the leg. TODO: Not known whether this is necessary.
ship_id	integer	NO	The ID of the ship used on this cruise.
start_time	timestamp without time zone	NO	The start time of the leg.
summary	text	YES	A summary of the cruise, with information about whether the objectives were met and any other pertinent information.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: cruise_crew

Associates crew members with a cruise leg and their roles. A crew member can have multiple roles. Note: these roles are distinct from members of programs, such as Chief Scientist. It may be necessary to revisit this structure or the division of roles.

Columns in "cruise_crew"

Name	Type	Nullable	Documentation
cruise_id	integer	NO	Reference to the cruise leg to which the member is assigned.
cruise_role_id	integer	NO	Reference to the cruise role.
note	text	YES	An optional note about the crew member.
person_id	integer	NO	Reference to the person on the crew.

Table: cruise_document

Stores information about documents related to a cruise.

Columns in "cruise_document"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The date and time of creation of the entity.
cruise_id	integer	NO	The ID of the cruise.
file_id	integer	YES	[None]
note	text	YES	A note about the document.
title	character varying	NO	The title of the document.
updated_on	timestamp without time zone	NO	The date and time of the entity's last update.
url	character varying	YES	A URL for the online copy of the document. May be used when no file is available.

Table: cruise_fn_contact

A table for associating First Nations contacts with a cruise.

Columns in "cruise_fn_contact"

Name	Type	Nullable	Documentation
contact_name	character varying	NO	The full name of the contact.
cruise_id	integer	NO	A reference to the cruise.
email	character varying	YES	The email address of the contact.
nation	text	YES	The name of the nation or group represented by the contact.
note	text	YES	A note about the contact.
phone	character varying	YES	The phone number of the contact.

Table: cruise_import

Stores cruise import tasks in the database. These contain the JSON data description, a status message and complete processing log.

Columns in "cruise_import"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	YES	[None]
data	jsonb	NO	A JSON document containing the cruise import data.
logs	jsonb	NO	A JSON array containing the complete processing log.
name	character varying	NO	A unique name for the import job.
status	character varying	NO	A status message about processing.
updated_on	timestamp without time zone	YES	[None]
version	integer	NO	[None]

Table: cruise_library

This table creates an association between a cruise leg and documents in the library that may be relevant to its research objectives.

Columns in "cruise_library"

Name	Type	Nullable	Documentation
cruiseleg_id	integer	NO	A reference to the cruise leg.
library_id	integer	NO	A reference to the document in the library.
note	text	YES	An optional note about the document with respect to the cruise leg. May be used to reference points of interest in a paper, etc.

Table: cruise_program

A table to link programs and cruises. A cruise can be under the auspices of more than one program or none.

Columns in "cruise_program"

Name	Type	Nullable	Documentation
cruise_id	integer	NO	The ID of a cruise.
program_id	integer	NO	The ID of a program.

Table: cruise_role

A lookup table of roles available to members of cruise leg crews.

Columns in "cruise_role"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the role.
note	text	YES	An optional note about the role and its responsibilities.
short_code	character varying	YES	A short string used to identify the role.

Table: disturbance

Provides a nominal level of disturbance for habitat events.

Columns in "disturbance"

Name	Type	Nullable	Documentation
name	character varying	NO	The textual representation of the disturbance level.
note	text	YES	A note about the disturbance level.
short_code	character varying	YES	Contains a short code that can be used to look up a disturbance (e.g., during import) without relying on the primary key.

Table: dive

A dive is what an ROV does. The dive has a start and end time (not necessarily submerge/resurface), a crew and possibly a name. Transects occur during dives.

Columns in "dive"

Name	Type	Nullable	Documentation
admin_note	text	YES	[None]
attributes	jsonb	YES	A JSON column used for recording structured attributes that do not fit with the regular table structure.
created_on	timestamp without time zone	NO	The date of creation of this record.
cruise_id	integer	NO	A reference to the cruise leg during which the dive was performed.
end_time	timestamp without time zone	NO	The end of the dive.
mark_for_delete	boolean	NO	Marks the entity for asynchronous deletion by the runner.
name	character varying	NO	A name for the dive.
note	text	YES	An optional note about the dive.
objective	text	YES	A statement of the practical or research objectives for this dive.
seatube_id	integer	YES	The ID of a dive on SeaTube corresponding to this dive.
ship_config_id	integer	NO	A reference to the platform config for the ship.
site	character varying	YES	An optional name for the site.
start_time	timestamp without time zone	NO	The start of the dive. Not necessarily the time the vehicle is placed in the water.
sub_config_id	integer	NO	A reference to the platform config for the submersible or ROV.
summary	text	YES	A summary of the dive; whether objectives were met, problems encountered, etc.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: dive_crew

Assigns roles to dive crew members. Crew members are selected from the person.

Columns in "dive_crew"

Name	Type	Nullable	Documentation
dive_id	integer	NO	The dive to which the crew member is assigned.
dive_role_id	integer	NO	A reference to the dive role.
note	text	YES	An optional note about the crew member.
person_id	integer	NO	A reference to the person.

Table: dive_role

A list of roles available to crew members on a dive via the dive_crew table.

Columns in "dive_role"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the role.
note	text	YES	An optional description of the role.
short_code	character varying	YES	A short string used to identify the role.

Table: equipment_type

This is a lookup table to provide the names of types of equipment for the model_equipment_type table, e.g., "Digital Still Camera", "Thermometer," "ROV," etc.

Columns in "equipment_type"

Name	Type	Nullable	Documentation
category	USER-DEFINED	YES	An enumeration column identifying the equipment as platform, instrument or some other type.
name	character varying	NO	A name for the equipment type.
note	text	YES	An optional note about the equipment type.

Table: event

The event table, a schemaless table storing all recorded observations and statuses during operations.

Columns in "event"

Name	Type	Nullable	Documentation
annotation_job_id	integer	YES	[None]
created_on	timestamp without time zone	NO	The time of creation of the event.
dive_id	integer	NO	A reference to the dive during which this event occurred.
end_time	timestamp without time zone	YES	The end time of the event. Null, if the event is discrete.
hidden	boolean	NO	If true, the entity should be hidden from searches and reports. An alternative to deleting entities that may prove useful in the future.
instrument_config_id	integer	YES	[None]
note	text	YES	An optional note about the event. Do not use to store event data.
properties	jsonb	NO	A JSON object containing name-value pairs describing the event.
restriction_mask	bit	NO	[None]
start_time	timestamp without time zone	NO	The start time of the event.
updated_on	timestamp without time zone	NO	The time of update of the event. Automatically updated by a trigger.

Table: event_logger

This table tracks the people who contributed to annotation, which may be composed of a number of separate labels.

Columns in "event_logger"

Name	Type	Nullable	Documentation
event_id	integer	NO	The event that was created from the annotation(s).
person_id	integer	NO	A person who contributed to the annotation.

Table: flow

Stores categories of current flow for habitat.

Columns in "flow"

Name	Type	Nullable	Documentation
name	character varying	NO	A note about the flow category.
note	text	YES	[None]
short_code	character varying	YES	A short code for the flow category.

Table: generic_label_map

A table for storing mappings from annotation labels to property sets.

Columns in "generic_label_map"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The date of creation.
label_text	USER-DEFINED	NO	The text of the label.
note	text	YES	A note about the mapping.
properties	jsonb	NO	A dictionary of mapped properties.
updated_on	timestamp without time zone	NO	The date of last update.

Table: generic_label_map_restriction

Links a generic label mapping to a restriction.

Columns in "generic_label_map_restriction"

Name	Type	Nullable	Documentation
generic_label_map_id	integer	NO	A reference to a generic label mapping.
restriction_id	integer	NO	A reference to a restriction.

Table: image_quality

Nominal image quality levels, originally used by VideoMiner but applicable to new records.

Columns in "image_quality"

Name	Type	Nullable	Documentation
name	character varying	NO	A name for the quality level.
note	text	YES	An optional note.
rank	integer	YES	An ordinal rank (zero is high) for the quality level.
short_code	character varying	YES	Contains a short code that can be used to look up an image quality (e.g., during import) without relying on the primary key.

Table: import_queue_annotator

Stores the import packages created by annotators. Some fields are transferred to the annotation job to record the objectives of the project. Each queue item (and therefore each job) should correspond to a single annotation project, and not encompass multiple projects with different objectives.

Columns in "import_queue_annotator"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The date of creation of the record.
cruise_name	character varying	NO	The name of the cruise to which this record is linked. A lookup is not used because the cruise record may not have been created yet.
data	jsonb	NO	Stores the JSON representation of the import job.
end_date	date	YES	[None]
hidden	boolean	NO	[None]
name	character varying	NO	A unique name for the import queue record.
note	text	YES	An optional note about the import package.
objective	text	YES	[None]
start_date	date	YES	[None]
status	character varying	YES	A short description of the processing status of the job.
updated_on	timestamp without time zone	NO	The date of update of the record.
user_id	integer	NO	A reference to the MSEA user that created the record.

Table: import_queue_annotator_label_map_prefill

A table to store the last configured tags and values for labels. Provides pre-filling in label mapping application.

Columns in "import_queue_annotator_label_map_prefill"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	YES	The date and time of creation of the entity.
event_type	jsonb	YES	[None]
label_tree_name	character varying	YES	The label tree name. If given identifies the label uniquely with the tree name.
name	character varying	NO	The text of the label.
properties	jsonb	NO	The the tag data.
tags	jsonb	NO	The list of tags.
updated_on	timestamp without time zone	YES	The date and time of the entity's last update.

Table: import_queue_pi

Stores the import packages created by principal investigators.

Columns in "import_queue_pi"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The date of creation of the record.
cruise_id	integer	NO	A reference to the cruise.
hidden	boolean	NO	[None]
name	character varying	NO	A name of the import job
note	text	YES	An optional note about the import package.
status	character varying	YES	A short description of the processing status of the job.
updated_on	timestamp without time zone	NO	The date of update of the record.
user_id	integer	NO	A reference to the MSEA user that created the record.

Table: instrument

This table represents instruments, which are concrete instances of the types represented in the model table. instruments tend to be things that generate data, be it a thermometer or a camera. For the purposes of this database, an instrument is anything that is not a platform.

Columns in "instrument"

Name	Type	Nullable	Documentation
attributes	jsonb	YES	A freeform list of attributes for this instrument.
created_on	timestamp without time zone	NO	The date of creation of this record.
model_id	integer	NO	A reference to the instrument model.
name	character varying	YES	A name for the instrument to distinguish it from others of the same model.
note	text	YES	An optional note about this instrument.
organisation_id	integer	NO	A reference to the organisation that owns and operates the instrument.
retired	date	YES	If the instrument is retired, this records the retirement date. If null, the instrument is assumed to be active.
serial_number	character varying	NO	The serial number of the instrument. If a serial number is not available, some unique identifier can be substituted. No two instruments of the same model may have the same serial number.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: instrument_config

This table records the configuration of an instrument, including settings and its spatial relationship to a parent entity -- another configured vehicle or instrument upon which this instrument is mounted.

Columns in "instrument_config"

Name	Type	Nullable	Documentation
configuration	jsonb	YES	Configuration information about the instrument config.
created_on	timestamp without time zone	NO	The date of creation of this record.
instrument_id	integer	NO	Reference to the instrument targeted by the configuration.
mark_for_delete	boolean	NO	Marks the entity for asynchronous deletion by the runner.
note	text	YES	An optional note about this configuration.
platform_config_id	integer	YES	The ID of the platform upon which the instrument is used.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: measurement

This table preserves time-stamped measurements generated by instruments. Each measurement has an associated quantity and unit. TODO: At this point multi-part measurement would be stored separately as the ability to store vectors isn't universal across DBMSes.

Columns in "measurement"

Name	Type	Nullable	Documentation
instrument_config_id	integer	NO	The configured instrument used to generate this item.
is_modelled	boolean	NO	A flag to indicate whether the value is derived from measurements by some modelling process.
measurement_type_id	integer	NO	A reference to the type of this measurement.
quantity	real	NO	The scalar quantity or magnitude of the measurement.
signal_quality	real	YES	A quality of the measurement as reported by the instrument. TODO: Requires clarification.
timestamp	timestamp without time zone	NO	The time that the measurement was recorded.

Table: measurement_type

This is a lookup table of types of measurement types for the measurement or measurement_event tables. This is fairly open-ended and can represent something like salinity with a specific unit, be it the SI unit or a discipline-specific unit. It is provided to allow users to easily select units for a measurement category when importing data.

Columns in "measurement_type"

Name	Type	Nullable	Documentation
maximum	real	YES	An optional upper bound on the value of the measurement. Null implies no limit.
minimum	real	YES	An optional lower bound on the value of the measurement. Null implies no limit.
name	character varying	NO	The type of measurement. E.g., "Density" or "Salinity."
note	text	YES	An optional note about this measurement type.
short_code	character varying	YES	Contains a short code that can be used to look up a measurement type (e.g., during import) without relying on the primary key.
unit	character varying	NO	The unit. SI units are preferred but not required. The unit selection should probably depend on field-specific idiomatic or cultural preferences.

Table: medium_format

A lookup table for data formats for the medium table. This will include things like video, photo and acoustic. TODO: To be determined whether medium formats should be discriminated more granularly than this. TODO: Should maybe be categorised into video/photo/acoustic/etc. along with things like the format (JPG, MP4, AAC) and possibly more specific encoding parameters.

Columns in "medium_format"

Name	Type	Nullable	Documentation
extensions	jsonb	NO	A list of file extensions that correspond to this medium type. For example, the JPEG image type may have extensions "jpg" or "jpeg" in any case. This list will help applications guess the correct format of a file if it isn't known. Note that more than one media type can have the same extension, so this feature doesn't provide a guaranteed one-to-one mapping. Use it only as a guide.
medium_type_id	integer	NO	A reference to the medium type (e.g., video or photo).
name	character varying	NO	The name of the format.
note	text	YES	An optional note about the format.
short_code	character varying	YES	Contains a short code that can be used to look up a medium format (e.g., during import) without relying on the primary key.

Table: medium_type

A simple lookup to provide media types to the annotation_protocol table. These are not specific media formats (as stored in medium_format), but provided a higher-level distinction.

Columns in "medium_type"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the media type.
note	text	YES	An optional note about the media type.
short_code	character	YES	Provides a short code for looking up the entity.

Table: model

This table records the brand and model of equipment in the inventory. This data is abstract, which is to say, there is only one record for "GoPro Hero4" but there will be one record for each concrete instance of the model in the instrument table. The model table stores both instruments and platforms.

Columns in "model"

Name	Type	Nullable	Documentation
attributes	jsonb	YES	A free-form JSON field for attributes of this model.
brand_name	character varying	NO	The brand name.
equipment_type_id	integer	NO	A reference to the equipment type.
model_name	character varying	NO	The model name.
note	text	YES	An optional note about the model.

Table: model_documentation

This table creates an association between a model and documentation in the library.

Columns in "model_documentation"

Name	Type	Nullable	Documentation
library_id	integer	NO	A reference to the document in the library.
model_id	integer	NO	A reference to the model.
note	text	YES	An optional note about the document with respect to the cruise leg. May be used to reference points of interest in a paper, etc.

Table: model_equipment_type

This table associates an equipment model with the abstract type of equipment it represents. For example, a GoPro Hero4 is both a digital video camera and a digital still camera. These values are taken from the equipment_type table.

Columns in "model_equipment_type"

Name	Type	Nullable	Documentation
equipment_type_id	integer	NO	The equipment type ID.
model_id	integer	NO	A reference to the model.

Table: observation_confidence

Provides a nominal observation confidence level for observation events.

Columns in "observation_confidence"

Name	Type	Nullable	Documentation
name	character varying	NO	The textual representation of the confidence level.
note	text	YES	A note about the confidence level.
rank	integer	NO	This field is a way of ranking confidence levels so that an ordering can be established.
short_code	character varying	YES	Contains a short code that can be used to look up a observation confidence (e.g., during import) without relying on the primary key.

Table: orientation

This table preserves time-stamped orientation measurements from instruments. These are interpreted according to a specified type. Ship and ROV orientation will be recorded here, though ships don't ordinarily have an orientation.

Columns in "orientation"

Name	Type	Nullable	Documentation
instrument_config_id	integer	NO	The configured instrument used to generate this item.
is_modelled	boolean	NO	A flag to indicate whether the value is derived from measurements by some modelling process.
orientation	jsonb	NO	The orientation vector.
orientation_type_id	integer	NO	A reference to the orientation type of this orientation.
signal_quality	real	YES	The signal quality of the position as reported by the instrument. TODO: Requires clarification.
timestamp	timestamp without time zone	NO	The time the position was recorded.

Table: orientation_type

These entities describe the interpretation of the orientation vector in the instrument_config and orientation tables. This can be a 3- or 4-element vector representing yaw, pitch roll; Tait-Bryan angles; Euler angles or a Quaternion.

Columns in "orientation_type"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the orientation type (e.g., "Quaternion").
note	text	YES	An optional note about the orientation type.
short_code	character varying	YES	Contains a short code that can be used to look up an orientation type (e.g., during import) without relying on the primary key.
unit	character varying	NO	The units used to interpret the elements of the vector.

Table: platform

This table maintains the inventory of vehicles, that is, ships and ROVs.

Columns in "platform"

Name	Type	Nullable	Documentation
attributes	jsonb	YES	A freeform list of attributes for this platform.
created_on	timestamp without time zone	NO	The date of creation of this record.
model_id	integer	NO	A reference to the model of the platform.
name	character varying	NO	The name of the platform. If this is a ship, it might be "CCGS Vector" or if it's an ROV, it might be given an arbitrary name by the maintainer.
note	text	YES	An optional note about this platform.
organisation_id	integer	NO	A reference to the organisation that owns and operates the platform.
retired	date	YES	If the platform is retired, this records the date. If null, the platform is assumed to be active.
serial_number	character varying	YES	The serial number of the platform. If this is an ROV it will be the manufacturer's serial number. If it's a vessel, this might be the IMO number. In any case, it must be unique.
short_code	character	YES	Contains a short code that can be used to look up a platform (e.g., during import) without relying on the primary key.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: platform_config

This table stores configuration information about vehicles (ships, ROVs,etc.) used for surveys. All references to vehicles are made through this table rather than directly to the platform table, because the disposition of equipment on the platform is essential to understanding how data has been generated, as well as for simple record-keeping purposes.

Columns in "platform_config"

Name	Type	Nullable	Documentation
configuration	jsonb	YES	The configuration data as a JSON object.
created_on	timestamp without time zone	NO	The date of creation of this record.
mark_for_delete	boolean	NO	Marks the entity for asynchronous deletion by the runner.
note	text	YES	An optional note about the configuration record.
platform_id	integer	NO	A reference to the platform.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: position

This table preserves time-stamped position measurements from instruments. These can be linear (e.g., UTM) or angular (e.g., Geographic) positions in a specified unit. Ship and ROV positions will be recorded here. TODO: Should this table have a spatial object (point) as well, or be split into two tables, one for absolute georeferenced positions and one for relative positions and orientations?

Columns in "position"

Name	Type	Nullable	Documentation
geom	geometry	YES	The point geometry.
instrument_config_id	integer	NO	The configured instrument used to generate this item.
is_modelled	boolean	NO	A flag to indicate whether the value is derived from measurements by some modelling process.
position_type_id	integer	NO	A reference to the position type of this position.
signal_quality	real	YES	The signal quality of the position as reported by the instrument. TODO: Requires clarification.
timestamp	timestamp without time zone	NO	The time the position was recorded.

Table: position_type

Describes the interpretation of a coordinate vector in the position table. These can be geographic or Cartesian positions or orientations and have defined linear or angular units.

Columns in "position_type"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the position type. E.g., "Geographic" or "Yaw, Pitch, Roll."
note	text	YES	An optional note about this position type.
short_code	character varying	YES	Contains a short code that can be used to look up a position type (e.g., during import) without relying on the primary key.
unit	character varying	NO	The unit. Linear or angular. E.g., "m" or "radians."

Table: program

This is a listing of scientific programs that can be associated with cruise legs, and gives information about the governorship and funding of specific research activities. Label trees and taxonomies may be associated with programs, since some trees are relevant to specific research objectives and not others. TODO: Currently a lable tree/taxonomy can only be linked to one program. Need to study further.

Columns in "program"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The date of creation of this item.
end_date	date	YES	The optional end date of the program.
name	character varying	NO	The name of the program.
note	text	YES	An optional note about the program.
objective	character varying	YES	The objective or mandate of the program.
start_date	date	NO	The starting date of the program.
summary	text	YES	A summary of the program; whether objectives were met, problems encountered, etc.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: program_library

A table to associate programs with library documents.

Columns in "program_library"

Name	Type	Nullable	Documentation
library_id	integer	NO	A reference to the library item.
program_id	integer	NO	A reference to the program item.

Table: program_member

Assigns user roles to a program.

Columns in "program_member"

Name	Type	Nullable	Documentation
person_id	integer	NO	A reference to the person.
program_id	integer	NO	The reference to the program.
role_id	integer	NO	A reference to the program role.

Table: program_role

Represents the roles a person might perform in respect to a program. A person can be assigned multiple roles within a single program, and multiple people can work on a program with the same role.

Columns in "program_role"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the role. E.g., "Chief Scientist."
note	text	YES	An optional note about the role.

Table: protocol

This table stores "(survey) protocol" entries from the source data. TODO: Used by events though it's not yet clear if it should stay this way.

Columns in "protocol"

Name	Type	Nullable	Documentation
name	character varying	NO	A name for the survey protocol.
note	text	YES	An optional note about the protocol.
short_code	character varying	YES	Contains a short code that can be used to look up a survey protocol (e.g., during import) without relying on the primary key.

Table: relief

Provides a nominal level of terrain relief for habitat events.

Columns in "relief"

Name	Type	Nullable	Documentation
name	character varying	NO	The textual representation of the relief level.
note	text	YES	A note about the relief level.
short_code	character varying	YES	Contains a short code that can be used to look up a relief (e.g., during import) without relying on the primary key.

Table: signup_area

Stores the region within which sign-ups are permitted, generally North America.

Columns in "signup_area"

Name	Type	Nullable	Documentation
geom	geometry	YES	The geometry of the sign-up region.
name	character varying	NO	The name of the sign-up region.

Table: status_type

This is a lookup table for available status types, such as "on bottom" or "off bottom." These are specialized in the status_type_detail table where a note distinguishes different flavours of a given type. For example, there can be multiple types of "Off Transect" events with a different explanation for each.

Columns in "status_type"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the status event.
note	text	YES	An optional description of the status type.
short_code	character	YES	A short code for referencing the status type in import documents.

Table: status_type_detail

This table associates a status type with a status event. The point of this indirection is to allow the addition of detail relative to the status type. For example, there are multiple reasons why an ROV might be "off transect", including that the vehicle has experienced a failure, or the pilot is investigating some interesting object with no research value. The status type value is the discriminator for the event, but the detail adds context the doesn't interfere with it.

Columns in "status_type_detail"

Name	Type	Nullable	Documentation
name	character varying	NO	The status type detail. This field distinguishes different uses for status types. For example, there can be more than one reason to record an "Off Transect" event.
note	text	YES	Optional extended description of detail.
short_code	character	YES	Contains a short code that can be used to look up a status type detail (e.g., during import) without relying on the primary key.
status_type_id	integer	NO	A reference to the status type.

Table: substrate

A lookup table listing the available substrate types for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity.

Columns in "substrate"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the substrate.
note	text	YES	An optional description of the substrate.
short_code	character varying	YES	Contains a short code that can be used to look up a substrate (e.g., during import) without relying on the primary key.

Table: survey_mode

This table stores "survey mode" entries from the source data. TODO: Used by events though it's not yet clear if it should stay this way.

Columns in "survey_mode"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the survey mode.
note	text	YES	An optional note about the survey mode.
short_code	character varying	YES	Contains a short code that can be used to look up a survey mode (e.g., during import) without relying on the primary key.

Table: thickness

A lookup table listing the available substrate thicknesses for the habitat_event table. TODO: This table may be altered to provide a hierarchical list of types with increasing specificity.

Columns in "thickness"

Name	Type	Nullable	Documentation
maximum	real	YES	The maximum value in the range.
minimum	real	YES	The minimum value in the range.
name	character varying	NO	A characterization of biocover thickness.
note	text	YES	An optional note about the biocover thickness.
short_code	character varying	YES	Contains a short code that can be used to look up a thickness (e.g., during import) without relying on the primary key.

Table: transect

A transect is a section of a dive during which interesting data are collected. Every transect is associated with a dive.

Columns in "transect"

Name	Type	Nullable	Documentation
admin_note	text	YES	[None]
attributes	jsonb	YES	A JSON column used for recording structured attributes that do not fit with the regular table structure.
dive_id	integer	NO	A reference to the dive during which this transect occurred.
end_time	timestamp without time zone	YES	The end time of the transect.
name	character varying	NO	The name of the transect.
note	text	YES	An optional note about the transect.
objective	text	YES	A statement of the practical or research objectives for this true.
start_time	timestamp without time zone	NO	The start time of the transect.
summary	text	YES	A summary of the transect; whether objectives were met, problems encountered, etc.

Table: weather_observation

Surface weather observations can be recorded by any crew member aboard a ship during a cruise leg.

Columns in "weather_observation"

Name	Type	Nullable	Documentation
cruise_crew_id	integer	YES	A reference to the cruise leg crew member who is making the report.
cruise_id	integer	NO	A link to the cruise during which this record was recorded.
note	text	YES	An optional note about the observation
pressure	real	YES	The air pressure.
swell	character varying	YES	Description of swell.
temperature	real	YES	The air temperature.
time	timestamp without time zone	NO	An optional note about the weather.
wind_direction	real	YES	The wind direction.
wind_speed	real	YES	The wind speed.

Schema: shared

Stores data that are shared between other schemas, such as personnel information, taxonomy, etc.

Table: db_version

Stores the current database version so that upgrade scripts can perform migrations appropriately.

Columns in "db_version"

Name	Type	Nullable	Documentation
revision	integer	NO	The revision number.
updated_on	timestamp without time zone	NO	The time the upgrade was performed.
version_major	integer	NO	The major version.
version_minor	integer	NO	The minor version number.

Table: file

Stores a record of a file object and its location on disk, along with some metadata. This entity is used by all other entities in all schemas that refer to a file object. This should make it easier to audit file stores and e.g., find duplicates using the hash.

Columns in "file"

Name	Type	Nullable	Documentation
blob_url	character varying	YES	A URL referencing the file in online blob storage. This field is subject to change depending on where the files reside and will change if they're moved.
created_on	timestamp without time zone	NO	The creation time of the file record, not necessarily the file itself (this should be stored in metadata).
description	text	YES	An optional description for the file.
file_type_id	integer	YES	An optional reference to the file type.
hash	character varying	NO	And MD5 hash of the file data. Used to compare files and search for identical versions.
metadata	jsonb	YES	A JSON dictionary containing metadata relating to the file.
name	character varying	NO	The name for the file.
path	character varying	NO	A path of the file, relative to the root directory where files are stored.
updated_on	timestamp without time zone	NO	The update time of the file record, not necessarily the file itself (this should be stored in metadata).

Table: file_type

A list of file types.

Columns in "file_type"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the file type.
short_code	character varying	NO	A short code for referencing the file type in import documents.

Table: library

This table stores a partial record of documents, books and papers that can be referenced by other entities. The ISBN and DOI fields are set to unique, but these may vary (in representation, at least) for a single document so care should be taken not to add duplicates. However, updated papers with the same title and author, but different date, are not disallowed.

Columns in "library"

Name	Type	Nullable	Documentation
abstract	text	YES	The abstract of the entry.
authors	jsonb	YES	The (list of) author(s) of the document. This is a JSON list of objects containing whatever information is necessary. Authors should be listed with at least first_name and last_name, and any other relevant information, such as email or institution.
created_on	timestamp without time zone	YES	The creation time of the file record, not necessarily the file itself (this should be stored in metadata).
doi	character varying	YES	The DOI of the document.
file_id	integer	NO	An optional reference to a file.
institution	character varying	YES	The name of the institution or publisher responsible for the document.
isbn	character varying	YES	The ISBN of the book, if it is a book.
issn	character varying	YES	The ISSN of the entry.
keywords	jsonb	YES	A list of keywords relating to the entry.
mendeley_id	character varying	YES	The ID of the record in Mendeley.
publication	character varying	YES	The name of the publication in which the document appeared.
title	text	NO	The title of the book, paper, or other document.
type	character varying	YES	Describes the type of entry: book, article, etc.
updated_on	timestamp without time zone	YES	The update time of the file record, not necessarily the file itself (this should be stored in metadata).
year	character varying	YES	The publishing date of the document.

Table: mseauser

Represents an MSEA user and is linked to a single Django auth User. Stores extra application-related properties such as the Biigle API key.

Columns in "mseauser"

Name	Type	Nullable	Documentation
allowed	boolean	NO	If true, the user is allowed to sign in. This can be set to false during sign up (e.g., by failing the IP check) or by an admin.
biigle_api_key	character varying	YES	The Biigle API key.
biigle_username	character varying	YES	The Biigle username.
bio	text	YES	Biographical information about the user.
ip_in_region	boolean	YES	Set to true if the user's IP is within the signup region. False positives and negatives are possible.
org_type	character varying	YES	The the type of organization with which the user is affiliated.
organization	character varying	YES	The organization with which a user is affiliated.
pg_role	character varying	YES	The name of the PostgreSQL role that the user will use to log in directly to the database.
registration_ip	inet	YES	The IP used by the user to register.
registration_note	text	YES	Administrator notes about the user's registration status.
registration_reason	text	YES	A short note about why the user registered.
user_id	integer	NO	A reference to the Django user.
verification_code	character	YES	An auto-generated string used to identify the user for verification purposes.
verification_expiry	timestamp without time zone	YES	The time of expiration of the verification code.
verification_time	timestamp without time zone	YES	The date and time at which the user was verified.

Table: organisation

Convenient storage for organisations involved in MSEA activities.

Columns in "organisation"

Name	Type	Nullable	Documentation
country	character varying	NO	The code for the country where the organisation is based (e.g., "CA" for Canada).
name	character varying	NO	The full name of the organisation.
note	text	YES	An optional note about the organisation.

Table: person

Stores information about people involved in all aspects of the application. This is not intended to be a comprehensive duplicate of the individual's HR dossier, just a means of representing a person and providing a means of associating it with a real-world individual, e.g., by their email.

Columns in "person"

Name	Type	Nullable	Documentation
affiliation	character varying	YES	The organization with which this user is currently affiliated.
biigle_user_id	integer	YES	The Biigle database ID of a user. The UUID should be used instead.
biigle_uuid	character	YES	The Biigle UUID is used to identify the user in Biigle apps.
bio	text	YES	A brief biography of the person.
email	USER-DEFINED	YES	Email address.
first_name	character varying	NO	First name.
last_name	character varying	NO	Last name.
photo	bytea	YES	A photo of the person.

Table: restriction

The restriction table provides restrictions for events, based on specific property keys and values. If any event has a key and matching value in a restriction, the event is not shown to non-administrators.

Columns in "restriction"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The date of creation of the restriction.
key	character varying	NO	The top-level property name or key.
name	character varying	NO	The name of the restriction.
note	text	YES	An optional note about the restriction.
pattern	text	NO	A pattern matching the property value using the SQL ILIKE operator. An exact (case-insensitive) match is a bare string, while appending % at any location is a wildcard match. For example, '%term' matches 'exterm' but not 'exterminate'.
updated_on	timestamp without time zone	NO	The date of last update of the restriction.

Table: restriction_group

Links the restriction to a group. Members of linked groups are able to view the restricted items.

Columns in "restriction_group"

Name	Type	Nullable	Documentation
group_id	integer	NO	The ID of the Django authentication group.
restriction_id	integer	NO	The ID of the restriction.

Table: site

Contains the names and geographic boundaries of study sites. This will be useful not only for mapping but for querying data by spatial extent.

Columns in "site"

Name	Type	Nullable	Documentation
name	character varying	NO	A name for the site.
note	text	YES	An optional note about the site.
spatial_library_id	integer	YES	A reference to the spatial library entry.

Table: spatial_library

Maintains a library of spatial data.

Columns in "spatial_library"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The time when this record was created.
file_id	integer	YES	A reference to a related file.
geom	geometry	YES	A vector representation of the object(s), projected into WGS84 (lat/lon) and stored as a geography type.
metadata	jsonb	YES	A JSON object containing metadata related to the spatial object.
name	character varying	NO	A name for the entry.
note	text	YES	A note about this library item.
rast	bytea	YES	A raster.
thumbnail	bytea	YES	A small thumbnail in binary (JPG) form.
updated_on	timestamp without time zone	NO	The date of update of this record.

Table: spatial_library_file

Allows a spatial library entity to own more than one file.

Columns in "spatial_library_file"

Name	Type	Nullable	Documentation
file_id	integer	NO	A link to the file.
spatial_library_id	integer	NO	A link to the spatial library.

Table: uploaded_file

A table to store uploaded files that can be associated with other entities in separate requests. This table is intended to store temporary entities: when an uploaded file has been handled, these records should be deleted.

Columns in "uploaded_file"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The time of creation of the record.
expires_on	timestamp without time zone	NO	The expiry time of the file. It may be deleted by a maintenance script after this time.
marked_for_delete	boolean	NO	If true, the file is marked for delete. Otherwise it will not be touched unless there is an age limit set in the daemon.
name	character varying	NO	The original name of the file.
path	character varying	NO	The path to the temporary location of the file.
type	character varying	NO	The guessed MIME type of the file.

Schema: taxonomy

Table: taxon

Stores taxonomic names from a variety of databases in a common format distinguished by source and taxon_id.

Columns in "taxon"

Name	Type	Nullable	Documentation
accepted_taxon_id	integer	YES	The taxon ID of the accepted taxon for this unaccepted taxon. If this column is null, the taxon is accepted.
class	character varying	YES	The "class" name.
common_name	character varying	YES	The common name of the species.
domain	character varying	YES	The domain name.
family	character varying	YES	The family name.
form	character varying	YES	The form name.
genus	character varying	YES	The genus name.
genus_hybrid	character varying	YES	The genus hybrid name.
gigaclass	character varying	YES	The gigaclass name.
hybrid	character varying	YES	The hybrid name.
infraclass	character varying	YES	The infraclass name.
infrakingdom	character varying	YES	The infrakingdom name.
infraorder	character varying	YES	The infraorder name.
infraphylum	character varying	YES	The infraphylum name.
kingdom	character varying	YES	The kingdom name.
megaclass	character varying	YES	The megaclass name.
natio	character varying	YES	The natio name.
no_common_name	boolean	YES	If a search has been performed for a common name and none was found, this prevents the processor from trying again.
order	character varying	YES	The "order" name.
parent_taxon_id	integer	YES	The taxon ID of the taxon's parent taxon. If this column is null, the taxon has no parents.
parvorder	character varying	YES	The parvorder name.
parvphylum	character varying	YES	The parvphylum name.
phylum	character varying	YES	The phylum name.
phylum_division	character varying	YES	The phylum division name.
rank	character varying	YES	The name of the taxonomic rank of the record, e.g., "class", "subspecies", etc.
scientific_name	character varying	YES	The scientific name of the species.
section	character varying	YES	The section name.
series	character varying	YES	The series name.
source	character varying	NO	The name of the source database: "inaturalist", "worms", "obis", etc.
species	character varying	YES	The species name.
subclass	character varying	YES	The subclass name.
subfamily	character varying	YES	The subfamily name.
subform	character varying	YES	The subform name.
subgenus	character varying	YES	The subgenus name.
subkingdom	character varying	YES	The subkingdom name.
suborder	character varying	YES	The suborder name.
subphylum	character varying	YES	The subphylum name.
subphylum_subdivision	character varying	YES	The subphylum division name.
subsection	character varying	YES	The subsection name.
subspecies	character varying	YES	The subspecies name.
subterclass	character varying	YES	The subterclass name.
subtribe	character varying	YES	The subtribe name.
subvariety	character varying	YES	The subvariety name.
superclass	character varying	YES	The superclass name.
superdomain	character varying	YES	The superdomain name.
superfamily	character varying	YES	The superfamily name.
superorder	character varying	YES	The superorder name.
supertribe	character varying	YES	The supertribe name.
taxon_id	character varying	NO	The taxonomic ID from the source database.
tribe	character varying	YES	The tribe name.
variety	character varying	YES	The variety name.