Annotation Database Entity Documentation

Overview

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

There are several schemas that store information in a variety of categories. These are described here, but listed below in more detail.

Schemas

wiki

The wiki schema contains tables related to the Wiki -- the page you are currently looking at.

rov

The rov schema contains all the information related to ROV and submersible, from the cruises and dives to personnel, to equipment configuration, water properties and species and habitat observations.

shared

The shared schema contains entities that are shared between other schemas. These include site users, people, taxonomy, etc.

obis

Stores the OBIS taxonomic database.

inaturalist

Stores the iNaturalist taxonomic database.

ndst

Stores data imported directly from the Dive Logging App, which records information about cruises, dives, transects and equipment by NDST. These are imported into the rov tables during the import process.

intertidal

Stores information about intertidal surveys. This isn't currently being used -- the UAS and related data are being stored in a separate intertidal dabase.

pa

Stores geospatial information about protected areas that can be used to query spatial data in other schemas.

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 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.

Generating Documentation

Database entity documentation is generated from the production database by the script, db_generate_docs.py.

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.

Generated Documentation

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

Schema: inaturalist

Table: observation

Stores observations from the iNaturalist species guide. TODO: This table isn't linked to the taxon table by a foreign key relation because the taxon ID is not unique in this table.

Columns in "observation"

Name	Type	Nullable	Documentation
captive_cultivated	boolean	YES	True, if the observation is of a captive or farmed organism.
common_name	character varying	YES	The common name of the organism.
coordinates_obscured	boolean	YES	Indicates whether the coordinate is obscured for the public.
created_at	timestamp with time zone	YES	The time of creation of the record.
description	text	YES	A description of the observation.
geoprivacy	character varying	YES	Set to private if the record is not public. TODO: Other values?
iconic_taxon_name	character varying	YES	Grouping by arbitrary taxonomic level.
image_url	text	YES	A URL for an image of the observation.
latitude	real	YES	The latitude of the observation.
license	character varying	YES	The license applied to the record.
longitude	real	YES	The longitude of the observation.
num_identification_agreements	smallint	YES	The number of votes for the identifaction.
num_identification_disagreements	smallint	YES	Number of votes against the identification.
oauth_application_id	character varying	YES	The ID of an OAUTH application used to modify the record.
observed_on	date	YES	The time of observation. Created by combining the observed_on and time_observed_at and time_zone fields.
place_guess	character varying	YES	The informal name of the site of the observation.
positional_accuracy	integer	YES	The positional accuracy of the observation position (m).
positioning_device	character varying	YES	The device used to collect the position.
positioning_method	character varying	YES	The positioning method.
public_positional_accuracy	integer	YES	The positional accuracy of the position showed to the public (m).
quality_grade	character varying	YES	The quality of the report. 'research' indicates that the record is authoritative.
scientific_name	character varying	YES	The scientific name given to the observation.
sound_url	text	YES	A URL for a sound file for the record.
species_guess	character varying	YES	A guess at the species name.
tag_list	jsonb	YES	A list of tags associated with the observation. Represented as a JSON list.
taxon_class_name	character varying	YES	The name of the taxon's class.
taxon_family_name	character varying	YES	The family of the taxon.
taxon_form_name	character varying	YES	The form of the taxon.
taxon_genus_name	character varying	YES	The genus of the taxon.
taxon_genushybrid_name	character varying	YES	The hybrid genus of the taxon.
taxon_geoprivacy	character varying	YES	The privacy setting of the taxon. 'open' or 'obscured'.
taxon_hybrid_name	character varying	YES	The hybrid name of the taxon.
taxon_id	integer	NO	The iNaturalist taxon ID.
taxon_kingdom_name	character varying	YES	The taxon' kingdom.
taxon_order_name	character varying	YES	The order of the taxon.
taxon_phylum_name	character varying	YES	The taxon phylum.
taxon_species_name	character varying	YES	The species of the taxon.
taxon_subclass_name	character varying	YES	The taxon's subclass.
taxon_subfamily_name	character varying	YES	The subfamily of the taxon.
taxon_suborder_name	character varying	YES	The suborder of a taxon.
taxon_subphylum_name	character varying	YES	The taxon subphylum.
taxon_subspecies_name	character varying	YES	The subspecies of the taxon.
taxon_subtribe_name	character varying	YES	The subtribe of the taxon.
taxon_superclass_name	character varying	YES	The taxon superclass.
taxon_superfamily_name	character varying	YES	The taxon's superfamily.
taxon_superorder_name	character varying	YES	The superorder of the taxon.
taxon_supertribe_name	character varying	YES	The supertribe of the taxon.
taxon_tribe_name	character varying	YES	The tribe of the taxon.
taxon_variety_name	character varying	YES	The variety name of the taxon.
updated_at	timestamp with time zone	YES	The time of update of the record.
url	text	YES	A URL pointing to the online record.
user_id	integer	YES	The ID of the iNaturalist user.
user_login	character varying	YES	The login name of the iNaturalist user.

Schema: intertidal

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

Table: cruise

None

Columns in "cruise"

Name	Type	Nullable	Documentation
active	boolean	NO	[None]
created_on	timestamp without time zone	NO	[None]
leg	text	YES	[None]
name	text	YES	[None]
note	text	YES	[None]
objective	text	YES	[None]
row_id	text	YES	[None]
status	character varying	YES	[None]
summary	text	YES	[None]
updated_on	timestamp without time zone	NO	[None]

Table: diveconfig

None

Columns in "diveconfig"

Name	Type	Nullable	Documentation
active	boolean	NO	[None]
name	text	YES	[None]
note	text	YES	[None]
row_id	text	YES	[None]
ship_config	text	YES	[None]
ship_instrument_configs	text	YES	[None]
sub_config	text	YES	[None]
sub_instrument_configs	text	YES	[None]

Table: dives

None

Columns in "dives"

Name	Type	Nullable	Documentation
active	boolean	NO	[None]
cruise_name	text	YES	[None]
dive_config	text	YES	[None]
end_time	text	YES	[None]
leg	text	YES	[None]
name	text	YES	[None]
note	text	YES	[None]
objective	text	YES	[None]
pilot	text	YES	[None]
row_id	text	YES	[None]
site_name	text	YES	[None]
start_time	text	YES	[None]
summary	text	YES	[None]

Table: equipconfig

None

Columns in "equipconfig"

Name	Type	Nullable	Documentation
active	boolean	NO	[None]
configuration	text	YES	[None]
name	text	YES	[None]
note	text	YES	[None]
row_id	text	YES	[None]
short_code	text	YES	[None]
type	text	YES	[None]

Table: equipment

None

Columns in "equipment"

Name	Type	Nullable	Documentation
active	boolean	NO	[None]
brand	text	YES	[None]
instrument_id	integer	YES	[None]
model	text	YES	[None]
note	text	YES	[None]
platform_id	integer	YES	[None]
row_id	text	YES	[None]
serial_number	text	YES	[None]
short_code	text	YES	[None]
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	[None]

Table: people

None

Columns in "people"

Name	Type	Nullable	Documentation
active	boolean	NO	[None]
email	text	YES	[None]
first_name	text	YES	[None]
initials	text	YES	[None]
last_name	text	YES	[None]
person_id	integer	YES	[None]
row_id	text	YES	[None]

Table: transects

None

Columns in "transects"

Name	Type	Nullable	Documentation
active	boolean	NO	[None]
cruise_name	text	YES	[None]
dive_name	text	YES	[None]
end_time	text	YES	[None]
leg	text	YES	[None]
name	text	YES	[None]
note	text	YES	[None]
objective	text	YES	[None]
row_id	text	YES	[None]
start_time	text	YES	[None]
summary	text	YES	[None]

Schema: obis

Table: taxon

OBIS taxonomy records imported directly from the OBIS observation data, but filtered for uniqueness on the Aphia ID.

Columns in "taxon"

Name	Type	Nullable	Documentation
aphiaid	integer	NO	[None]
class	character varying	YES	[None]
domain	character varying	YES	[None]
family	character varying	YES	[None]
forma	character varying	YES	[None]
genus	character varying	YES	[None]
gigaclass	character varying	YES	[None]
infraclass	character varying	YES	[None]
infrakingdom	character varying	YES	[None]
infraorder	character varying	YES	[None]
infraphylum	character varying	YES	[None]
kingdom	character varying	YES	[None]
megaclass	character varying	YES	[None]
natio	character varying	YES	[None]
order	character varying	YES	[None]
organismname	character varying	YES	[None]
originalscientificname	character varying	YES	[None]
parvorder	character varying	YES	[None]
parvphylum	character varying	YES	[None]
phylum	character varying	YES	[None]
phylum_division	character varying	YES	[None]
scientificname	character varying	NO	[None]
section	character varying	YES	[None]
series	character varying	YES	[None]
species	character varying	YES	[None]
subclass	character varying	YES	[None]
subfamily	character varying	YES	[None]
subforma	character varying	YES	[None]
subgenus	character varying	YES	[None]
subkingdom	character varying	YES	[None]
suborder	character varying	YES	[None]
subphylum	character varying	YES	[None]
subphylum_subdivision	character varying	YES	[None]
subsection	character varying	YES	[None]
subspecies	character varying	YES	[None]
subterclass	character varying	YES	[None]
subtribe	character varying	YES	[None]
subvariety	character varying	YES	[None]
superclass	character varying	YES	[None]
superdomain	character varying	YES	[None]
superfamily	character varying	YES	[None]
superorder	character varying	YES	[None]
supertribe	character varying	YES	[None]
taxonid	character varying	YES	[None]
taxonomicstatus	character varying	YES	[None]
taxonrank	character varying	YES	[None]
taxonremarks	text	YES	[None]
tribe	character varying	YES	[None]
type	character varying	YES	[None]
variety	character varying	YES	[None]
vernacularname	character varying	YES	[None]

Schema: pa

Table: mpa

None

Columns in "mpa"

Name	Type	Nullable	Documentation
aichi_t11	character varying	YES	[None]
biome	character varying	YES	[None]
comments	character varying	YES	[None]
delisdate	integer	YES	[None]
geom	geometry	YES	[None]
geometry	USER-DEFINED	YES	[None]
gov_type	character varying	YES	[None]
iucn_cat	character varying	YES	[None]
jur_id	character varying	YES	[None]
legisl_e	character varying	YES	[None]
legisl_f	character varying	YES	[None]
loc_e	character varying	YES	[None]
loc_f	character varying	YES	[None]
mgmt_e	character varying	YES	[None]
mgmt_f	character varying	YES	[None]
name_e	character varying	YES	[None]
name_f	character varying	YES	[None]
name_ind	character varying	YES	[None]
o_area	double precision	YES	[None]
objectid	bigint	NO	[None]
oecm	character varying	YES	[None]
ogc_fid	integer	NO	[None]
owner_e	character varying	YES	[None]
owner_f	character varying	YES	[None]
parent_id	integer	YES	[None]
protdate	integer	YES	[None]
shape_area	double precision	YES	[None]
shape_length	double precision	YES	[None]
status_e	character varying	YES	[None]
status_f	character varying	YES	[None]
subs_right	character varying	YES	[None]
type_e	character varying	YES	[None]
type_f	character varying	YES	[None]
url	character varying	YES	[None]
zone_id	integer	YES	[None]
zonedesc_e	character varying	YES	[None]
zonedesc_f	character varying	YES	[None]

Table: mpa_data_object

A data object related to an MPA. This is the abstract, or top-level object, which may contain one or more physical objects (files) or documents.

Columns in "mpa_data_object"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The time of creation of the record.
description	text	YES	A description of the object.
doi	character varying	YES	The DOI of the object.
isbn	character varying	YES	The ISBN of the object.
mpa_id	integer	NO	A reference to the MPA record.
name	character varying	NO	The name of the data object.
updated_on	timestamp without time zone	NO	The last update time of the object.

Table: mpa_data_object_file

Gives the ability for more than one data object to own a file and vice versa.

Columns in "mpa_data_object_file"

Name	Type	Nullable	Documentation
data_object_id	integer	NO	A reference to the data object.
file_id	integer	NO	A reference to the file.

Table: protected_area

A unified table for protected areas.

Columns in "protected_area"

Name	Type	Nullable	Documentation
area	real	YES	The nominal area in sq. km of the protected area.
geom	geometry	NO	The boundary geometry of the protected area.
name_e	character varying	NO	The English name of the protected area.
name_f	character varying	NO	The French name of the protected area.
note	text	YES	An optional note about the protected area.
original_id	integer	YES	The original ID of the protected area, from the source database.
type	character varying	NO	The type of protected area, including "RCA", "MPA", etc.
year_created	integer	YES	The year of creation of the protected area.

Table: protected_area_data_object

A data object related to an RCA. This is the abstract, or top-level object, which may contain one or more physical objects (files) or documents.

Columns in "protected_area_data_object"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The time of creation of the record.
description	text	YES	A description of the object.
doi	character varying	YES	The DOI of the object.
isbn	character varying	YES	The ISBN of the object.
name	character varying	NO	The name of the data object.
protected_area_id	integer	YES	A link to the protected area.
updated_on	timestamp without time zone	NO	The last update time of the object.

Table: protected_area_data_object_file

Gives the ability for more than one data object to own a file and vice versa.

Columns in "protected_area_data_object_file"

Name	Type	Nullable	Documentation
data_object_id	integer	NO	A reference to the data object.
file_id	integer	NO	A reference to the file.

Table: rca

Stores basic information about RCAs along with a boundary geometry.

Columns in "rca"

Name	Type	Nullable	Documentation
area	real	YES	The area of the RCA (from source).
description	text	YES	A description of the RCA.
formerid	smallint	YES	The former ID of the RCA (from source).
geom	geometry	YES	The RCA geography.
hectares	real	YES	The number of hectares in the RCA (from source).
len	real	YES	The length (?) of the RCA (from source).
name	character varying	YES	The name of the RCA.
rca_id	double precision	YES	The RCA ID as defined by the provider.
sq_km	real	YES	The area of the RCA (from source).
yr_created	smallint	YES	The year the RCA was created (from source).

Schema: rov

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	NO	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_protocol

A table to record annotation protocols for annotation projects. TODO: Currently under development as a concept. The idea is to develop the protocol and mark it as a template. Users will create projects and either associate them with a template protocol or copy and edit the protocol and use the copy. Any protocol can be marked as a template for future use. The template concept is largely a convenience.

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	[None]
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	[None]
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	NO	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	[None]
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 rov.annotation_protocol 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_name	character varying	YES	The original name of the document file.
file_type	character varying	YES	The mime type of the file. Required if file data are given.
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.
short_code	character	NO	Provides a short code for looking up the entity.

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	NO	Contains a short code that can be used to look up a biocover (e.g., during import) without relying on the primary key.

Table: comment_event

Provides a way to record comments rather than using a sparse note field on the event.

Columns in "comment_event"

Name	Type	Nullable	Documentation
event_id	integer	NO	A reference to the parent event.
note	text	YES	A text comment or note.

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	NO	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	NO	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
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.
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	[None]
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

None

Columns in "cruise_document"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	[None]
cruise_id	integer	NO	[None]
file_name	character varying	YES	[None]
file_type	character varying	YES	[None]
note	text	YES	[None]
title	character varying	NO	[None]
updated_on	timestamp without time zone	NO	[None]
url	character varying	YES	[None]

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_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	NO	[None]

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	NO	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
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.
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.
ship_config_id	integer	NO	A reference to the platform config for the ship.
site_id	integer	YES	Optional reference to the geographic site of the dive.
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	NO	[None]

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.
short_code	character varying	NO	A short code for referencing the equipment type in import documents.

Table: event

The event table is a parent or abstract table that provides properties common to habitat, observation, status and measurement events. Each of those event records must have a reference to one event record. Conceptually, the event row and its child entity row are considered to be one object.

Columns in "event"

Name	Type	Nullable	Documentation
attributes	jsonb	YES	[None]
created_on	timestamp without time zone	NO	The time of creation of this record.
dive_id	integer	NO	A reference to a dive.
end_time	timestamp without time zone	YES	The end time of the event. If the event is instantaneous, this field is null.
frames	jsonb	YES	Preserves the frames from the Biigle annotation.
medium_filename	character varying	YES	The name of the media file from which this event is derived.
medium_id	integer	YES	A reference to the medium on which this annotation was created.
original_id	integer	NO	The original ID of the event if read from a source that has IDs.
original_labels	jsonb	YES	Optionally stores information about annotation labels used to generate this event. For Biigle, stores the label IDs.
shape	jsonb	YES	The shape used for annotations (e.g., Biigle). The coordinates for this shape should have been converted to metres. GeoJSON doesn't support circles, so we use a format that encapsulates the shape type and the raw list of coordinates, something like: { "type": "circle", "shape": [1.0, 1.0, 1.0] } which is a circle with radius 1 at position 1,1. A whole-screen annotation will be a rectangle the size of the screen.
shape_area	double precision	YES	The area of the shape in square metres, calculated from the screenand shape dimensions and the laser point distance.
start_time	timestamp without time zone	NO	The time at the start of the event. If the event is instantaneous, this is the time at which it occurred.
tags	jsonb	YES	Stores a list of tags relevant to this event.
transect_id	integer	YES	An optional reference to a transect. TODO: Clarify the use of transects and whether this column is nullable.

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: habitat_event

The habitat event table records information about the biocover, thickness, substrate and complexity of the habitat. Some survey protocols record the taxon with biocover observations. A reference to the taxon table is provided. This table is a realization of the event table.

Columns in "habitat_event"

Name	Type	Nullable	Documentation
annotation_protocol_id	integer	NO	A reference to the annotation protocol used in the creation of this observation.
biocover_coverage_id	integer	YES	A coverage value for the biocover.
biocover_id	integer	YES	A reference to the biocover lookup.
complexity_id	integer	YES	A reference to the habitat complexity lookup.
disturbance_id	integer	YES	Indicates the level of disturbance of the substrate and/or biota.
event_id	integer	NO	A reference to the parent event.
relief_id	integer	YES	A reference to the relief level.
substrate_coverage_id	integer	YES	A coverage value for the substrate.
substrate_id	integer	YES	A reference to the dominant substrate lookup.
taxon_id	integer	YES	A reference to the taxon used in the creation of this observation.
thickness_id	integer	YES	A reference to the biocover thickness lookup.

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	NO	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.

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.
mseauser_id	integer	NO	A reference to the MSEA user that created the record.
name	character varying	NO	A unique name for the import queue record.
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.

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	[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.
tag_data	jsonb	NO	The the tag data.
tags	jsonb	NO	The list of tags.
updated_on	timestamp without time zone	YES	[None]

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.
mseauser_id	integer	NO	A reference to the MSEA user that created the record.
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.

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.
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.
short_code	character varying	NO	Contains a short code that can be used to look up an instrument (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: 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.
name	character varying	NO	The name of the instrument config. Need not be unique: used to identify the config within the platform config.
note	text	YES	An optional note about this configuration.
platform_config_id	integer	YES	[None]
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_event

A table for storing human-created measurements.

Columns in "measurement_event"

Name	Type	Nullable	Documentation
event_id	integer	NO	A reference to the parent event.
measurement_type_id	integer	NO	A reference to the measurement type.
quantity	real	NO	The measurement value in the measurement type unit.

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	NO	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

This table represents videos, photographs or other media. It may store acoustic data in the future. The table is designed with a hierarchical structure to allow the preservation of links between original data and derived (i.e., cropped or otherwise modified) data. For example, if a ten minute segment of a one hour video is extracted, the new segment can be stored with a reference to its parent.

Columns in "medium"

Name	Type	Nullable	Documentation
biigle_video_id	integer	YES	Provisional: the ID of the video as represented in Biigle.de.
created_on	timestamp without time zone	NO	The date of creation of this record.
file_id	integer	NO	A required reference to the file.
geostamp_source_id	integer	YES	The source of the geostamp information on photo or video data. Refers to an instrument, e.g., a GPS.
height	integer	YES	The height of the video frame in pixels.
instrument_config_id	integer	NO	An instrument configuration for the instrument that generated this media.
length	real	YES	If a video or acoustic file (etc.), the length in seconds.
medium_format_id	integer	YES	A reference to the format of the data.
note	text	YES	An optional note about this data.
parent_id	integer	YES	If the video or photo is derived or cropped from another, this refers to the source record (optional).
source_time	real	YES	If this is an extract from a video or a frame grab, indicates the zero-based start time with respect to the source video.
start_time	timestamp without time zone	NO	The start date/time of this video, or the instantaneous time if it's a photograph.
thumbnail	bytea	YES	An optional thumbnail to represent the stored medium.
width	integer	YES	The width of the video frame in pixels.

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	NO	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	NO	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_category

Observation categories are used by annotation protocols.

Columns in "observation_category"

Name	Type	Nullable	Documentation
name	character varying	NO	The name of the observation category.
note	text	YES	An optional note about the observation category.
short_code	character varying	NO	A short code for referencing the observation category in import documents.

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	NO	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: observation_event

An observation event records the occurrence of a phenomenon as a result of observation by a human user, usually by analyzing a video recording or photograph. Observations can relate to a species taxonomy or an element in a label tree. This table will be "sparse," that is, certain a value will be given for only one or two columns in each row, and the interpretation of the values will be informed by the information in the annotation protocol table. This table is a realization of the event table.

Columns in "observation_event"

Name	Type	Nullable	Documentation
abundance_id	integer	YES	Link to an abundance level.
annotation_protocol_id	integer	NO	A reference to the annotation protocol used in the creation of this observation.
category	character varying	YES	A free-form category label used for distinguishing types of labels within an annotation project. Useful for flagging records for review.
count	integer	YES	The number of individuals observed. TODO: Interpretation depends on the observation interval as recorded in theannotation protocol table.
coverage_id	integer	YES	Provides an indication of the amount of a scene covered by an organism. Implies habitat forming.
event_id	integer	NO	A reference to the parent event.
observation_confidence_id	integer	YES	References the observation confidence lookup to indicate the user's confidence in the observation.
taxon_id	integer	YES	A reference to the taxon used in the creation of this observation.

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	NO	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 varying	NO	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.
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	NO	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	NO	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	NO	Contains a short code that can be used to look up a relief (e.g., during import) without relying on the primary key.

Table: status_event

This table records status events with names given by the status_type_detail table. Status events represent a state that changes at some time and persists until another state preempts it. For example, "on bottom" would persist until an "off bottom" event is recorded. A status_event is a specialization of an event.

Columns in "status_event"

Name	Type	Nullable	Documentation
event_id	integer	NO	The associated event.
status_type_detail_id	integer	NO	The detailed status type.

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 varying	NO	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
detail	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 varying	NO	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	NO	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	NO	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	NO	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
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.

Materialized View: cruise_track

Constructs a geometry for each cruise which describes the path of the ship.

Columns in "cruise_track"

Name	Type	Nullable	Documentation
colour	text	False	The a colour code generated from the cruise's ID used for cartography.
cruise_id	integer	False	The reference to the cruise.
cruise_name	text	False	The cruise name and leg.
geom	geometry	False	The cruise track geometry.
instrument_config_id	integer	False	The reference to the instrument configuration.

Materialized View: depth

A view on the measurements table containing only depths.

Columns in "depth"

Name	Type	Nullable	Documentation
dive_id	integer	False	The ID of the dive during which the depth was measured.
quantity	real	False	[None]
timestamp	timestamp(6) without time zone	False	The timestamp of the record.

Materialized View: dive_track

Constructs a geometry for each dive which describes the path of the submersible.

Columns in "dive_track"

Name	Type	Nullable	Documentation
colour	text	False	The a colour code generated from the cruise's ID. Used for cartography.
cruise_id	integer	False	The reference to the cruise.
cruise_name	text	False	The cruise name.
dive_id	integer	False	The reference to the dive.
dive_name	character varying(64)	False	The dive name.
geom	geometry	False	The dive track geometry.
transect_id	integer	False	The reference to the transect. Zero if there is no corresponding transect.
transect_name	character varying(64)	False	The transect name.

Materialized View: evt_depth

Creates a relation between an event and the depth nearest the start and end times of the event.

Columns in "evt_depth"

Name	Type	Nullable	Documentation
end_diff	interval	False	[None]
end_measurement_id	integer	False	The link to the depth nearest the event's end time, or null if one is not supplied.
event_id	integer	False	The link to the event's's event ID.
start_diff	interval	False	[None]
start_measurement_id	integer	False	The link to the depth nearest the event's start time.

Materialized View: evt_pos

Creates a relation between an event and the positions nearest the start and end times of the event.

Columns in "evt_pos"

Name	Type	Nullable	Documentation
end_diff	interval	False	[None]
end_position_id	integer	False	The link to the position nearest the event's end time, or null if one is not supplied.
event_id	integer	False	The link to the event's's event ID.
start_diff	interval	False	[None]
start_position_id	integer	False	The link to the position nearest the event's start time.

Materialized View: measurement_position

Creates a relation between a measurement and the temporally-nearest position.

Columns in "measurement_position"

Name	Type	Nullable	Documentation
measurement_id	integer	False	The link to the measurement.
position_id	integer	False	The link to the position.

Schema: shared

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: hart_taxon

Species from wherever the Hart codes are from? This table contains records loaded from the VideoMiner species table. This appears to be related to whatever the source of Hart codes is, but at this point VM is the only source. If the name needs to be changed in the future, so be it. Records in this table have a one-to-one relationship with records in the taxon table, and it serves as a metadata table for records stored there.

Columns in "hart_taxon"

Name	Type	Nullable	Documentation
authority	text	YES	The identification authority.
common_name	character varying	YES	The common name.
from_date	date	YES	?
grouping_id	integer	YES	The grouping. * B - Bird * F - Fish * I - Invertebrate * M - Mammal * O - Object? * R - Reptile
latin_name	character varying	YES	The Latin name of the item, which is distinct in this table from the scientific name. Appears to be informal, and not necessarily Latin.
nodc_code_v7	real	YES	The NODC code, v7.
nodc_code_v8	integer	YES	The NODC code, V8. Identical to the ITIS ID.
rank_id	integer	YES	A three-letter code indicating the taxonomic level.
scientific_name	character varying	YES	The scientific (e.g., Linnean) name.
sp_species_code	character varying	YES	Another species code whose purpose/origin is not known.
species_code	character varying	NO	The species, or Hart, code.
species_prov_code	character varying	YES	?
taxa_reference	text	YES	The reference for the identification.
to_date	date	YES	?

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
biigle_api_key	character varying	YES	The Biigle API key.
biigle_username	character varying	YES	The Biigle username.
pg_role	character varying	YES	The name of the PostgreSQL role that the user will use to log in directly to the database.
user_id	integer	NO	A reference to the Django user.

Table: mseauser_restriction

Links an MSEA user to a restriction. The user will have access to records related to this group.

Columns in "mseauser_restriction"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The creation time of the record.
mseauser_id	integer	NO	The ID of the MSEA user.
restriction_id	integer	NO	The ID of the restriction.
updated_on	timestamp without time zone	NO	The last update time of the record.

Table: news_item

A table for news items related to each sub-site.

Columns in "news_item"

Name	Type	Nullable	Documentation
content	text	NO	The content of the item.
created_on	timestamp without time zone	NO	The date of creation of the item.
sites	character varying	YES	An optional comma-delimited list of sites the item refers to. The current list is 'rca', 'rov', 'intertidal'. Leave empty to apply to all sites. TODO: Currently not standardized or enforced.
title	character varying	NO	A title for the item.

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.
short_code	character varying	NO	A short code for looking up the entity; usually the organisation's acronym.

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
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	character varying	NO	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

Provides a list of restrictions that can be applied to individual observation rows, to restrict access to allowed roles.

Columns in "restriction"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The creation time of the restriction.
name	character varying	NO	The name of the restriction.
note	text	YES	An optional note about the restriction.
short_code	character varying	NO	A short code used to reference the restriction.
updated_on	timestamp without time zone	NO	The last update time 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: survey_suggestion

A table of suggestions supplied by users for upgrades to the database.

Columns in "survey_suggestion"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	[None]
down_votes	integer	NO	A count of the down votes on the suggestion.
email	character varying	YES	The email of the suggesting user.
screen_name	character varying	YES	A screen name for the suggesting user.
suggestion	text	NO	The text of the suggestion.
up_votes	integer	NO	A count of the up votes on the suggestion.

Table: taxon

The taxon table stores information about the identification of observed specimens. A taxon record stores the name of the taxonomic unit (e.g., the species or genus name), or an open nomenclature (ON) identifier using *sp.* or *cf.*; an optional operational taxonomic unit (OTU), and a reference to any known taxa in the Hart, ITIS, iNaturalist or WoRMS databases. The original label stores the name as imported. The label, OTU and Hart code are updated at audit time from the original label and the scientific and common names are updated if available. The auditing process is necessary to produce reasonable data. Some of this work can be performed automatically. Multiple records in this table can refer to the same species or subspecies. It *may or may not be known whether the species referred to by a record are the same or different.* Some amount of auditing will probably be necessary on this table. The contents of this table are driven by observations; it is not populated from existing catalogues but from observations, and the authoritative entity IDs are populated as a post-processing step from existing catalogs. If a match in one of the authoritative catalogue is not found, the relation fields are left empty and the taxon should be audited, to correct spelling or assign a correct identifier.

Columns in "taxon"

Name	Type	Nullable	Documentation
aphia_id	integer	YES	A reference to the WoRMs database. The actual Aphia ID used by WoRMs is used as the primary key in the WoRMs taxon table.
common_name	character varying	YES	The common name of the taxon.
hart_code	character varying	YES	Used when there's not ref in the hart code table.
hart_id	integer	YES	A reference to the Hart code table. This is an identification with species used by VideoMiner and other tools that use that list.
inaturalist_id	integer	YES	A reference to the iNaturalist taxon. The iNaturalist taxon ID is used as the primary key in the iNaturalist taxon table.
label	character varying	YES	The label given to the species at audit time. May be modified from the original label. May differ from the scientific or common names.
original_label	character varying	NO	The original label as entered by the annotator or observer. On review or audit, the label, hart_code and otu fields will be populated and references to outside databases updated. This field will not be edited.
otu	character varying	YES	The operational taxonomic unit is a unique identifier for this instances of an observation whose identification isn't certain.
review_note	text	YES	Optional note about the review.
reviewed_by_id	integer	YES	Relates to the person who reviewed the record.
reviewed_on	timestamp without time zone	YES	Gives the time that the record was reviewed.
scientific_name	character varying	YES	The scientific (binomial, trinomial) name of the taxon. Updated at audit time, based on the label or other fields.

Table: taxon_restriction

Links an MSEA user to a restriction. The user will have access to records related to this group.

Columns in "taxon_restriction"

Name	Type	Nullable	Documentation
created_on	timestamp without time zone	NO	The creation time of the record.
restriction_id	integer	NO	The ID of the restriction.
taxon_id	integer	NO	The ID of the taxon.
updated_on	timestamp without time zone	NO	The last update time of the record.

Table: taxonomic_grouping

Provides a lookup for the taxonomic grouping (Invertebrate, Fish, etc.) Used by VideoMiner.

Columns in "taxonomic_grouping"

Name	Type	Nullable	Documentation
code	character	YES	A one-letter code for the grouping.
name	character varying	NO	The name of the taxonomic grouping.
note	text	YES	A note about the taxonomic grouping.

Table: taxonomic_rank

Provides a lookup for the taxonomic rank (Kingdom, Class, Species, etc.) Used by VideoMiner.

Columns in "taxonomic_rank"

Name	Type	Nullable	Documentation
code	character	YES	A three-letter code for the rank.
name	character varying	NO	The name of the taxonomic rank.
note	text	YES	A note about the taxonomic rank.

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.
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: wiki

Table: actor

None

Columns in "actor"

Name	Type	Nullable	Documentation
actor_id	bigint	NO	[None]
actor_name	text	NO	[None]
actor_user	integer	YES	[None]

Table: archive

None

Columns in "archive"

Name	Type	Nullable	Documentation
ar_actor	bigint	NO	[None]
ar_comment_id	bigint	NO	[None]
ar_deleted	smallint	NO	[None]
ar_id	integer	NO	[None]
ar_len	integer	YES	[None]
ar_minor_edit	smallint	NO	[None]
ar_namespace	integer	NO	[None]
ar_page_id	integer	YES	[None]
ar_parent_id	integer	YES	[None]
ar_rev_id	integer	NO	[None]
ar_sha1	text	NO	[None]
ar_timestamp	timestamp with time zone	NO	[None]
ar_title	text	NO	[None]

Table: bot_passwords

None

Columns in "bot_passwords"

Name	Type	Nullable	Documentation
bp_app_id	text	NO	[None]
bp_grants	text	NO	[None]
bp_password	text	NO	[None]
bp_restrictions	text	NO	[None]
bp_token	text	NO	[None]
bp_user	integer	NO	[None]

Table: category

None

Columns in "category"

Name	Type	Nullable	Documentation
cat_files	integer	NO	[None]
cat_id	integer	NO	[None]
cat_pages	integer	NO	[None]
cat_subcats	integer	NO	[None]
cat_title	text	NO	[None]

Table: categorylinks

None

Columns in "categorylinks"

Name	Type	Nullable	Documentation
cl_collation	text	NO	[None]
cl_from	integer	NO	[None]
cl_sortkey	text	NO	[None]
cl_sortkey_prefix	text	NO	[None]
cl_timestamp	timestamp with time zone	NO	[None]
cl_to	text	NO	[None]
cl_type	text	NO	[None]

Table: change_tag

None

Columns in "change_tag"

Name	Type	Nullable	Documentation
ct_id	integer	NO	[None]
ct_log_id	integer	YES	[None]
ct_params	text	YES	[None]
ct_rc_id	integer	YES	[None]
ct_rev_id	integer	YES	[None]
ct_tag_id	integer	NO	[None]

Table: change_tag_def

None

Columns in "change_tag_def"

Name	Type	Nullable	Documentation
ctd_count	bigint	NO	[None]
ctd_id	integer	NO	[None]
ctd_name	text	NO	[None]
ctd_user_defined	smallint	NO	[None]

Table: comment

None

Columns in "comment"

Name	Type	Nullable	Documentation
comment_data	text	YES	[None]
comment_hash	integer	NO	[None]
comment_id	bigint	NO	[None]
comment_text	text	NO	[None]

Table: content

None

Columns in "content"

Name	Type	Nullable	Documentation
content_address	text	NO	[None]
content_id	bigint	NO	[None]
content_model	smallint	NO	[None]
content_sha1	text	NO	[None]
content_size	integer	NO	[None]

Table: content_models

None

Columns in "content_models"

Name	Type	Nullable	Documentation
model_id	integer	NO	[None]
model_name	text	NO	[None]

Table: externallinks

None

Columns in "externallinks"

Name	Type	Nullable	Documentation
el_from	integer	NO	[None]
el_id	integer	NO	[None]
el_index	text	NO	[None]
el_index_60	text	NO	[None]
el_to	text	NO	[None]
el_to_domain_index	text	NO	[None]
el_to_path	text	YES	[None]

Table: filearchive

None

Columns in "filearchive"

Name	Type	Nullable	Documentation
fa_actor	bigint	NO	[None]
fa_archive_name	text	YES	[None]
fa_bits	integer	YES	[None]
fa_deleted	smallint	NO	[None]
fa_deleted_reason_id	bigint	NO	[None]
fa_deleted_timestamp	timestamp with time zone	YES	[None]
fa_deleted_user	integer	YES	[None]
fa_description_id	bigint	NO	[None]
fa_height	integer	YES	[None]
fa_id	integer	NO	[None]
fa_major_mime	text	YES	[None]
fa_media_type	text	YES	[None]
fa_metadata	text	YES	[None]
fa_minor_mime	text	YES	[None]
fa_name	text	NO	[None]
fa_sha1	text	NO	[None]
fa_size	integer	YES	[None]
fa_storage_group	text	YES	[None]
fa_storage_key	text	YES	[None]
fa_timestamp	timestamp with time zone	YES	[None]
fa_width	integer	YES	[None]

Table: image

None

Columns in "image"

Name	Type	Nullable	Documentation
img_actor	bigint	NO	[None]
img_bits	integer	NO	[None]
img_description_id	bigint	NO	[None]
img_height	integer	NO	[None]
img_major_mime	text	NO	[None]
img_media_type	text	YES	[None]
img_metadata	text	NO	[None]
img_minor_mime	text	NO	[None]
img_name	text	NO	[None]
img_sha1	text	NO	[None]
img_size	integer	NO	[None]
img_timestamp	timestamp with time zone	NO	[None]
img_width	integer	NO	[None]

Table: imagelinks

None

Columns in "imagelinks"

Name	Type	Nullable	Documentation
il_from	integer	NO	[None]
il_from_namespace	integer	NO	[None]
il_to	text	NO	[None]

Table: interwiki

None

Columns in "interwiki"

Name	Type	Nullable	Documentation
iw_api	text	NO	[None]
iw_local	smallint	NO	[None]
iw_prefix	character varying	NO	[None]
iw_trans	smallint	NO	[None]
iw_url	text	NO	[None]
iw_wikiid	character varying	NO	[None]

Table: ip_changes

None

Columns in "ip_changes"

Name	Type	Nullable	Documentation
ipc_hex	text	NO	[None]
ipc_rev_id	integer	NO	[None]
ipc_rev_timestamp	timestamp with time zone	NO	[None]

Table: ipblocks

None

Columns in "ipblocks"

Name	Type	Nullable	Documentation
ipb_address	text	NO	[None]
ipb_allow_usertalk	smallint	NO	[None]
ipb_anon_only	smallint	NO	[None]
ipb_auto	smallint	NO	[None]
ipb_block_email	smallint	NO	[None]
ipb_by_actor	bigint	NO	[None]
ipb_create_account	smallint	NO	[None]
ipb_deleted	smallint	NO	[None]
ipb_enable_autoblock	smallint	NO	[None]
ipb_expiry	timestamp with time zone	NO	[None]
ipb_id	integer	NO	[None]
ipb_parent_block_id	integer	YES	[None]
ipb_range_end	text	NO	[None]
ipb_range_start	text	NO	[None]
ipb_reason_id	bigint	NO	[None]
ipb_sitewide	smallint	NO	[None]
ipb_timestamp	timestamp with time zone	NO	[None]
ipb_user	integer	NO	[None]

Table: ipblocks_restrictions

None

Columns in "ipblocks_restrictions"

Name	Type	Nullable	Documentation
ir_ipb_id	integer	NO	[None]
ir_type	smallint	NO	[None]
ir_value	integer	NO	[None]

Table: iwlinks

None

Columns in "iwlinks"

Name	Type	Nullable	Documentation
iwl_from	integer	NO	[None]
iwl_prefix	text	NO	[None]
iwl_title	text	NO	[None]

Table: job

None

Columns in "job"

Name	Type	Nullable	Documentation
job_attempts	integer	NO	[None]
job_cmd	text	NO	[None]
job_id	integer	NO	[None]
job_namespace	integer	NO	[None]
job_params	text	NO	[None]
job_random	integer	NO	[None]
job_sha1	text	NO	[None]
job_timestamp	timestamp with time zone	YES	[None]
job_title	text	NO	[None]
job_token	text	NO	[None]
job_token_timestamp	timestamp with time zone	YES	[None]

Table: l10n_cache

None

Columns in "l10n_cache"

Name	Type	Nullable	Documentation
lc_key	character varying	NO	[None]
lc_lang	text	NO	[None]
lc_value	text	NO	[None]

Table: langlinks

None

Columns in "langlinks"

Name	Type	Nullable	Documentation
ll_from	integer	NO	[None]
ll_lang	text	NO	[None]
ll_title	text	NO	[None]

Table: linktarget

None

Columns in "linktarget"

Name	Type	Nullable	Documentation
lt_id	bigint	NO	[None]
lt_namespace	integer	NO	[None]
lt_title	text	NO	[None]

Table: log_search

None

Columns in "log_search"

Name	Type	Nullable	Documentation
ls_field	text	NO	[None]
ls_log_id	integer	NO	[None]
ls_value	character varying	NO	[None]

Table: logging

None

Columns in "logging"

Name	Type	Nullable	Documentation
log_action	text	NO	[None]
log_actor	bigint	NO	[None]
log_comment_id	bigint	NO	[None]
log_deleted	smallint	NO	[None]
log_id	integer	NO	[None]
log_namespace	integer	NO	[None]
log_page	integer	YES	[None]
log_params	text	NO	[None]
log_timestamp	timestamp with time zone	NO	[None]
log_title	text	NO	[None]
log_type	text	NO	[None]

Table: module_deps

None

Columns in "module_deps"

Name	Type	Nullable	Documentation
md_deps	text	NO	[None]
md_module	text	NO	[None]
md_skin	text	NO	[None]

Table: objectcache

None

Columns in "objectcache"

Name	Type	Nullable	Documentation
exptime	timestamp with time zone	NO	[None]
flags	integer	YES	[None]
keyname	text	NO	[None]
modtoken	character varying	NO	[None]
value	text	YES	[None]

Table: oldimage

None

Columns in "oldimage"

Name	Type	Nullable	Documentation
oi_actor	bigint	NO	[None]
oi_archive_name	text	NO	[None]
oi_bits	integer	NO	[None]
oi_deleted	smallint	NO	[None]
oi_description_id	bigint	NO	[None]
oi_height	integer	NO	[None]
oi_major_mime	text	NO	[None]
oi_media_type	text	YES	[None]
oi_metadata	text	NO	[None]
oi_minor_mime	text	NO	[None]
oi_name	text	NO	[None]
oi_sha1	text	NO	[None]
oi_size	integer	NO	[None]
oi_timestamp	timestamp with time zone	NO	[None]
oi_width	integer	NO	[None]

Table: page

None

Columns in "page"

Name	Type	Nullable	Documentation
page_content_model	text	YES	[None]
page_id	integer	NO	[None]
page_is_new	smallint	NO	[None]
page_is_redirect	smallint	NO	[None]
page_lang	text	YES	[None]
page_latest	integer	NO	[None]
page_len	integer	NO	[None]
page_links_updated	timestamp with time zone	YES	[None]
page_namespace	integer	NO	[None]
page_random	double precision	NO	[None]
page_title	text	NO	[None]
page_touched	timestamp with time zone	NO	[None]
titlevector	tsvector	YES	[None]

Table: page_props

None

Columns in "page_props"

Name	Type	Nullable	Documentation
pp_page	integer	NO	[None]
pp_propname	text	NO	[None]
pp_sortkey	double precision	YES	[None]
pp_value	text	NO	[None]

Table: page_restrictions

None

Columns in "page_restrictions"

Name	Type	Nullable	Documentation
pr_cascade	smallint	NO	[None]
pr_expiry	timestamp with time zone	YES	[None]
pr_id	integer	NO	[None]
pr_level	text	NO	[None]
pr_page	integer	NO	[None]
pr_type	text	NO	[None]

Table: pagelinks

None

Columns in "pagelinks"

Name	Type	Nullable	Documentation
pl_from	integer	NO	[None]
pl_from_namespace	integer	NO	[None]
pl_namespace	integer	NO	[None]
pl_title	text	NO	[None]

Table: protected_titles

None

Columns in "protected_titles"

Name	Type	Nullable	Documentation
pt_create_perm	text	NO	[None]
pt_expiry	timestamp with time zone	NO	[None]
pt_namespace	integer	NO	[None]
pt_reason_id	bigint	NO	[None]
pt_timestamp	timestamp with time zone	NO	[None]
pt_title	text	NO	[None]
pt_user	integer	NO	[None]

Table: querycache

None

Columns in "querycache"

Name	Type	Nullable	Documentation
qc_namespace	integer	NO	[None]
qc_title	text	NO	[None]
qc_type	text	NO	[None]
qc_value	integer	NO	[None]

Table: querycache_info

None

Columns in "querycache_info"

Name	Type	Nullable	Documentation
qci_timestamp	timestamp with time zone	NO	[None]
qci_type	text	NO	[None]

Table: querycachetwo

None

Columns in "querycachetwo"

Name	Type	Nullable	Documentation
qcc_namespace	integer	NO	[None]
qcc_namespacetwo	integer	NO	[None]
qcc_title	text	NO	[None]
qcc_titletwo	text	NO	[None]
qcc_type	text	NO	[None]
qcc_value	integer	NO	[None]

Table: recentchanges

None

Columns in "recentchanges"

Name	Type	Nullable	Documentation
rc_actor	bigint	NO	[None]
rc_bot	smallint	NO	[None]
rc_comment_id	bigint	NO	[None]
rc_cur_id	integer	NO	[None]
rc_deleted	smallint	NO	[None]
rc_id	integer	NO	[None]
rc_ip	text	NO	[None]
rc_last_oldid	integer	NO	[None]
rc_log_action	text	YES	[None]
rc_log_type	text	YES	[None]
rc_logid	integer	NO	[None]
rc_minor	smallint	NO	[None]
rc_namespace	integer	NO	[None]
rc_new	smallint	NO	[None]
rc_new_len	integer	YES	[None]
rc_old_len	integer	YES	[None]
rc_params	text	YES	[None]
rc_patrolled	smallint	NO	[None]
rc_source	text	NO	[None]
rc_this_oldid	integer	NO	[None]
rc_timestamp	timestamp with time zone	NO	[None]
rc_title	text	NO	[None]
rc_type	smallint	NO	[None]

Table: redirect

None

Columns in "redirect"

Name	Type	Nullable	Documentation
rd_fragment	text	YES	[None]
rd_from	integer	NO	[None]
rd_interwiki	character varying	YES	[None]
rd_namespace	integer	NO	[None]
rd_title	text	NO	[None]

Table: revision

None

Columns in "revision"

Name	Type	Nullable	Documentation
rev_actor	bigint	NO	[None]
rev_comment_id	bigint	NO	[None]
rev_deleted	smallint	NO	[None]
rev_id	integer	NO	[None]
rev_len	integer	YES	[None]
rev_minor_edit	smallint	NO	[None]
rev_page	integer	NO	[None]
rev_parent_id	integer	YES	[None]
rev_sha1	text	NO	[None]
rev_timestamp	timestamp with time zone	NO	[None]

Table: revision_comment_temp

None

Columns in "revision_comment_temp"

Name	Type	Nullable	Documentation
revcomment_comment_id	bigint	NO	[None]
revcomment_rev	integer	NO	[None]

Table: searchindex

None

Columns in "searchindex"

Name	Type	Nullable	Documentation
si_page	integer	NO	[None]
si_text	text	NO	[None]
si_title	character varying	NO	[None]

Table: site_identifiers

None

Columns in "site_identifiers"

Name	Type	Nullable	Documentation
si_key	text	NO	[None]
si_site	integer	NO	[None]
si_type	text	NO	[None]

Table: site_stats

None

Columns in "site_stats"

Name	Type	Nullable	Documentation
ss_active_users	bigint	YES	[None]
ss_good_articles	bigint	YES	[None]
ss_images	bigint	YES	[None]
ss_row_id	integer	NO	[None]
ss_total_edits	bigint	YES	[None]
ss_total_pages	bigint	YES	[None]
ss_users	bigint	YES	[None]

Table: sites

None

Columns in "sites"

Name	Type	Nullable	Documentation
site_config	text	NO	[None]
site_data	text	NO	[None]
site_domain	character varying	NO	[None]
site_forward	smallint	NO	[None]
site_global_key	text	NO	[None]
site_group	text	NO	[None]
site_id	integer	NO	[None]
site_language	text	NO	[None]
site_protocol	text	NO	[None]
site_source	text	NO	[None]
site_type	text	NO	[None]

Table: slot_roles

None

Columns in "slot_roles"

Name	Type	Nullable	Documentation
role_id	integer	NO	[None]
role_name	text	NO	[None]

Table: slots

None

Columns in "slots"

Name	Type	Nullable	Documentation
slot_content_id	bigint	NO	[None]
slot_origin	bigint	NO	[None]
slot_revision_id	bigint	NO	[None]
slot_role_id	smallint	NO	[None]

Table: templatelinks

None

Columns in "templatelinks"

Name	Type	Nullable	Documentation
tl_from	integer	NO	[None]
tl_from_namespace	integer	NO	[None]
tl_target_id	bigint	NO	[None]

Table: text

None

Columns in "text"

Name	Type	Nullable	Documentation
old_flags	text	NO	[None]
old_id	integer	NO	[None]
old_text	text	NO	[None]
textvector	tsvector	YES	[None]

Table: updatelog

None

Columns in "updatelog"

Name	Type	Nullable	Documentation
ul_key	character varying	NO	[None]
ul_value	text	YES	[None]

Table: uploadstash

None

Columns in "uploadstash"

Name	Type	Nullable	Documentation
us_chunk_inx	integer	YES	[None]
us_id	integer	NO	[None]
us_image_bits	smallint	YES	[None]
us_image_height	integer	YES	[None]
us_image_width	integer	YES	[None]
us_key	character varying	NO	[None]
us_media_type	USER-DEFINED	YES	[None]
us_mime	character varying	YES	[None]
us_orig_path	character varying	NO	[None]
us_path	character varying	NO	[None]
us_props	text	YES	[None]
us_sha1	character varying	NO	[None]
us_size	integer	NO	[None]
us_source_type	character varying	YES	[None]
us_status	character varying	NO	[None]
us_timestamp	timestamp with time zone	NO	[None]
us_user	integer	NO	[None]

Table: user

None

Columns in "user"

Name	Type	Nullable	Documentation
user_editcount	integer	YES	[None]
user_email	text	NO	[None]
user_email_authenticated	timestamp with time zone	YES	[None]
user_email_token	text	YES	[None]
user_email_token_expires	timestamp with time zone	YES	[None]
user_id	integer	NO	[None]
user_name	text	NO	[None]
user_newpass_time	timestamp with time zone	YES	[None]
user_newpassword	text	NO	[None]
user_password	text	NO	[None]
user_password_expires	timestamp with time zone	YES	[None]
user_real_name	text	NO	[None]
user_registration	timestamp with time zone	YES	[None]
user_token	text	NO	[None]
user_touched	timestamp with time zone	NO	[None]

Table: user_autocreate_serial

None

Columns in "user_autocreate_serial"

Name	Type	Nullable	Documentation
uas_shard	integer	NO	[None]
uas_value	integer	NO	[None]

Table: user_former_groups

None

Columns in "user_former_groups"

Name	Type	Nullable	Documentation
ufg_group	text	NO	[None]
ufg_user	integer	NO	[None]

Table: user_groups

None

Columns in "user_groups"

Name	Type	Nullable	Documentation
ug_expiry	timestamp with time zone	YES	[None]
ug_group	text	NO	[None]
ug_user	integer	NO	[None]

Table: user_newtalk

None

Columns in "user_newtalk"

Name	Type	Nullable	Documentation
user_id	integer	NO	[None]
user_ip	text	NO	[None]
user_last_timestamp	timestamp with time zone	YES	[None]

Table: user_properties

None

Columns in "user_properties"

Name	Type	Nullable	Documentation
up_property	text	NO	[None]
up_user	integer	NO	[None]
up_value	text	YES	[None]

Table: watchlist

None

Columns in "watchlist"

Name	Type	Nullable	Documentation
wl_id	integer	NO	[None]
wl_namespace	integer	NO	[None]
wl_notificationtimestamp	timestamp with time zone	YES	[None]
wl_title	text	NO	[None]
wl_user	integer	NO	[None]

Table: watchlist_expiry

None

Columns in "watchlist_expiry"

Name	Type	Nullable	Documentation
we_expiry	timestamp with time zone	NO	[None]
we_item	integer	NO	[None]