Detailed table descriptions

websites

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
title	char varying (100)		No	Website title.
description	text		Yes	Website description.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
url	char varying (500)		No	URL of the website root.
default_survey_id	integer		Yes	Survey which records for this website are created under if not specified by the data entry form.
password	char varying (30)		No	Encrypted password for the website. Enables secure access to services.
deleted	boolean	false	No	Has this record been deleted?
verification_checks_enabled	boolean	false	No	Are automated verification checks enabled for this website?
public_key	text		Yes	Public key for checking signed JWT access tokens in the API.

surveys

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
title	char varying (100)		No	Title of the survey.
owner_id	integer		Yes	Foreign key to the people table. Identifies the person responsible for the survey.
description	text		Yes	Description of the survey.
website_id	integer		No	Foreign key to the websites table. Identifies the website that the survey is available for.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
parent_id	integer		Yes	Identifies the survey’s parent survey, if there is one.
core_validation_rules	text		Yes	JSON listing core fields (entity.fieldname) with altered validation rules for this survey dataset, for example {“sample.location_name”:”required”}.

samples

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
survey_id	integer		No	Foreign key to the surveys table. Identifies the survey that this sample belongs to.
location_id	integer		Yes	Foreign key to the locations table. Identifies the location this sample is at, if known.
date_start	date		Yes	Start of the range of dates that this sample could have been made on.
date_end	date		Yes	End of the range of dates that this sample could have been made on.
date_type	char varying (2)		Yes	Vague date type code. D = Day, DD = Day range, O = Month in year, OO = Month range in year, P = Season in year, Y = Year, YY = Year range, Y- = From year, -Y = Until year, C = Century, CC = Century range, C- = From century, -C = Until century, M = Month, S = Season, U = Unknown.
entered_sref	char varying (40)		Yes	Spatial reference that was entered for the sample.
entered_sref_system	char varying (10)		Yes	System that was used for the spatial reference in entered_sref.
location_name	char varying (200)		Yes	Free text name of the location or other locality information given for the sample.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
comment	text		Yes	Comment regarding the sample.
external_key	char varying (50)		Yes	For samples imported from an external system, provides a field to store the external system’s primary key for the record allowing re-synchronisation.
sample_method_id	integer		Yes	Foreign key to the termlists_terms table. Identifies the term which describes the sampling method.
deleted	boolean	false	No	Has this record been deleted?
geom	geom		Yes	WGS84 geometry describing the spatial reference of the sample. This describes the full grid square as a polygon for grid references, or a point for other spatial references.
recorder_names	char varying		Yes	List of names of the people who were involved in recording of this sample, one per line. Used when the recorders are not listed in the people table.
parent_id	integer		Yes	In cases where sampling data is gathered in a hierarchical fashion, this allows samples to be linked to a parent sample. For example, a sample linear transect may have several quadrat samples taken along it’s length.
input_form	char varying		Yes	Identifier of the input form used to create the record, allowing the client website to use the same form when editing. It is suggested that this is used to store the path to the form (either the complete URL or a partial path).
group_id	integer		Yes	Foreign key to the groups table. Identifies the recording group that this sample was posted into, if explicitly posted to a group.
privacy_precision	integer		Yes	Allows record precision to be blurred for public viewing for privacy (as opposed to sensitivity) reasons. An example might be to obscure the garden location of a minor.
record_status	character (1)	‘C’::bpchar	Yes	Status of this sample. I - in progress, C - completed, V - verified, R - rejected, D - dubious/queried (deprecated), T - test.
verified_by_id	integer		Yes	Foreign key to the users table (verifier of the sample).
verified_on	timestamp (no tz)		Yes	Date this record was verified.
licence_id	integer		Yes	ID of the licence that is associated with this sample and the records it contains.
training	boolean	false	No	Flag indicating if this sample was created for training purposes and is therefore not considered real.

occurrences

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
sample_id	integer		No	Foreign key to the samples table. Identifies the sample that this occurrence record is part of.
determiner_id	integer		Yes	Foreign key to the people table. Identifies the person who determined the record.
confidential	boolean	false	No	Flag set to true if this record is flagged confidential by the dataset administrator. The confidential flag relates to the need to control communications around a record rather then simply an indicator that a record is sensitive (which should be done via the sensitivity_precision field) so this flag prevents notifications about this record being sent to the recorder.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
website_id	integer		No	Foreign key to the websites table. Website that the occurrence record is linked to.
external_key	char varying (50)		Yes	For occurrences imported from an external system, provides a field to store the external system’s primary key for the record allowing re-synchronisation.
comment	text		Yes	User’ comment on data entry of the occurrence.
taxa_taxon_list_id	integer		Yes	Foreign key to the taxa_taxon_lists table. Identifies the taxa on a taxon list that this occurrence is a record of.
deleted	boolean	false	No	Has this record been deleted?
record_status	character (1)	‘C’::bpchar	Yes	Status of this record. I - in progress, C - completed, V - verified, R - rejected, D - dubious/queried (deprecated), T - test.
verified_by_id	integer		Yes	Foreign key to the users table (verifier).
verified_on	timestamp (no tz)		Yes	Date this record was verified.
downloaded_flag	character (1)	‘N’::bpchar	No	Downloaded status flag: N - not downloaded, I - Initial download, F - Final download
downloaded_on	timestamp (no tz)		Yes	Date occurrence downloaded out of system
all_info_in_determinations	character (1)	‘N’::bpchar	No	Flag to indicate whether occurrence uses determinations table
zero_abundance	boolean	false	No	Flag that is set to true when a record indicates the absence of something rather than presence of something.
last_verification_check_date	timestamp (no tz)		Yes	The taxa_taxon_list_id that this occurrence was associated with when verification checks were last run. If this has changed then verification checks should be re-run. Foreign key to taxa_taxon_lists.
training	boolean	false	No	Flag indicating if this record was created for training purposes and is therefore not considered real.
sensitivity_precision	integer		Yes	Precision of grid references for public access of records that are sensitive. For example, set to 1000 to limit public access to a 1km grid square. If null then not sensitive.
release_status	character (1)	‘R’::bpchar	Yes	Release states of this record. R - released, P - recorder has requested a precheck before release, U - unreleased as part of a project whcih is witholding records until completion.
record_substatus	smallint		Yes	Provides additional detail on the record status. Values are: 1=accepted as correct, 2=accepted as considered correct, 3=plausible, 4=not accepted as unable to verify, 5=not accepted, incorrect. Null for unchecked records.
record_decision_source	character (1)		Yes	Defines if the record status decision was by a human (H) or machine (M).
import_guid	char varying		Yes	Globally unique identifier of the import batch.
metadata	json		Yes	Record metadata. Use this to store additional metadata that is not part of the actual record, e.g. information about a mobile device used for the record. For system use, not shown to the recorder.

occurrence_comments

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
comment	text		No
created_by_id	integer		Yes	Foreign key to the users table (creator), if user was logged in when comment created.
created_on	timestamp (no tz)		No	Date and time this comment was created.
updated_by_id	integer		Yes	Foreign key to the users table (updater), if user was logged in when comment updated.
updated_on	timestamp (no tz)		No	Date and time this comment was updated.
occurrence_id	integer		Yes	Foreign key to the occurrences table. Identifies the commented occurrence.
email_address	char varying (50)		Yes	Email of user who created the comment, if the user was not logged in but supplied an email address.
deleted	boolean	false	No	Has this record been deleted?
person_name	char varying		Yes	Identifier for anonymous commenter.
auto_generated	boolean	false	No	Was this comment generated by an automated verification check?
generated_by	char varying (100)		Yes	When a comment is auto-generated, names the system process (e.g. verification ruleset) that generated the comment.
implies_manual_check_required	boolean	false	No	When a comment is generated by a verification rule, if this is true then it implies that the record has been flagged for a manual verification check.
generated_by_subtype	char varying (100)		Yes	Allows a generator to subtype the output into different categories, e.g. ID difficulty levels.
query	boolean	false	Yes	Set to true if this comment asks a question that needs a response.
record_status	character (1)		Yes	If this comment relates to the changing of the status of a record, then determines the status it was changed to. Provides and audit trail of verification changes.
record_substatus	smallint		Yes	As record_status but provides an audit trail of the occurrences.record_substatus field
external_key	char varying (50)		Yes	For comments imported from an external system, provides a field to store the external system’s primary key for the record allowing re-synchronisation.
comment_type_id	integer		Yes	Points to a termlists_term which describes the type of the comment.
correspondence_data	text		Yes	Stores correspondence data related to the comment in JSON format. Typically this will be the sender, recipient and body of an email or the URL of a social media post.
reference	text		Yes	Description of reference used, link to web address, journal or book name etc.
confidential	boolean	false	No	Is this occurrence_comment confidential?

determinations

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
occurrence_id	integer		No	Foreign key to the occurrences table. Identifies the determined occurrence.
email_address	char varying (50)		Yes	Email of user who created the determination.
person_name	char varying		Yes	Identifier for determiner.
cms_ref	integer		Yes	CMS Identifier for determiner.
taxa_taxon_list_id	integer		Yes	Foreign key to the taxa_taxon_lists table. Identifies the taxa on a taxon list that this detemination is a record of.
comment	text		Yes	Text description of Taxon if not in list, or if a list.
taxon_extra_info	text		Yes	Additional information that may provide more accurate determination.
deleted	boolean	false	No	Has this record been deleted?
created_by_id	integer		Yes	Foreign key to the users table (creator), if user was logged in when comment created.
created_on	timestamp (no tz)		No	Date and time this comment was created.
updated_by_id	integer		Yes	Foreign key to the users table (updater), if user was logged in when comment updated.
updated_on	timestamp (no tz)		No	Date and time this comment was updated.
determination_type	character (1)	‘N’::bpchar	No	Type of determination. Can be one of determination_type can be one of: – ‘A’ : Considered correct; – ‘B’ : Considered incorrect; – ‘C’ : Correct; – ‘I’ : Incorrect; – ‘R’ : Requires confirmation; – ‘U’ : Unconfirmed; – ‘X’ : Unidentified;
taxon_details	text		Yes
taxa_taxon_list_id_list	ARRAY		Yes	Where this determination refers to a list of possible taxa, contains an array of the IDs of those taxa.

taxon_lists

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
title	char varying (100)		Yes	Title of the taxon list.
description	text		Yes	Description of the taxon list.
website_id	integer		Yes	Foreign key to the websites table. Identifies the website that this list is available for, or null for lists available across all websites.
parent_id	integer		Yes	Foreign key to the taxon_lists table. For lists that are subsets of other taxon lists, identifies the parent list.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this list been deleted?

taxa_taxon_lists

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
taxon_list_id	integer		Yes
taxon_id	integer		No
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
parent_id	integer		Yes	Foreign key to the taxa table. Identifies the taxonomic parent, for example the genus of a species.
taxon_meaning_id	integer		Yes	Foreign key to the taxon_meanings table. Identifies the meaning of this taxon record. Eacg group of taxa with the same meaning are considered synonymous.
taxonomic_sort_order	bigint		Yes	Provides a sort order which allows the taxon hierarchy to be displayed in taxonomic rather than alphabetical order.
preferred	boolean	false	No	Flag set to true if the name constitutes the preferred name when selected amongst all taxa that have the same meaning.
updated_on	timestamp (no tz)		No	Date this record was updated.
updated_by_id	integer		No	Foreign key to the users table (updater).
deleted	boolean	false	No	Has this record been deleted?
description	char varying		Yes
common_taxon_id	integer		Yes	Link to the first common name for this taxon entry.
allow_data_entry	boolean	true	No
verification_check_version	integer	0	No	A version number which is incremented each time that a custom attribute attached to a taxon which provides an input value for verification checks is changed.

taxa

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
taxon	char varying (200)		Yes	Term used for the taxon, excluding the authority.
taxon_group_id	integer		No	Foreign key to the taxon_groups table. Identifies a label that describes the taxon’s higher level grouping.
language_id	integer		Yes	Foreign key to the languages table. Identifies the language used for this taxon name.
external_key	char varying (50)		Yes	For taxa which are directly mappable onto taxon records in an external system, identifies the external record’s key. For example, this is used to store the taxon version key from the NBN Gateway.
authority	char varying (100)		Yes	Authority label for the taxon name.
search_code	char varying (20)		Yes	A search code that may be used for rapid lookup of the taxon name.
scientific	boolean		Yes	Flag set to true if the name is a scientific name rather than vernacular.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
description	char varying		Yes
taxon_rank_id	integer		Yes	Foreign key to the taxon_ranks table. Identifies the rank of the taxon (e.g. species, phylum).
attribute	char varying (100)		Yes	Attributes such as sensu lato that are associated with the taxon name.
marine_flag	boolean	false	No	Set to true for marine species.
freshwater_flag	boolean	false	No	Set to true for freshwater species.
terrestrial_flag	boolean	false	No	Set to true for terrestrial species.
non_native_flag	boolean	false	No	Set to true for non-native species.
organism_key	char varying		Yes	Identifier for the organism concept, e.g. when linking to UKSI.

taxon_groups

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
title	char varying (100)		Yes
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
external_key	char varying (50)		Yes
parent_id	integer		Yes	Identifies the parent of the taxon group if there is one.
description	char varying		Yes	Description of the taxon group.

languages

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
iso	character (3)		Yes	ISO 639-2 code for the language.
language	char varying (50)		Yes	Term used to describe the language in the system.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?

cache_occurrences_functional

Column	Type	Default	Nullable	Description
id	integer		No
sample_id	integer		Yes
website_id	integer		Yes
survey_id	integer		Yes
input_form	char varying		Yes
location_id	integer		Yes
location_name	char varying		Yes
public_geom	geom		Yes
map_sq_1km_id	integer		Yes
map_sq_2km_id	integer		Yes
map_sq_10km_id	integer		Yes
date_start	date		Yes
date_end	date		Yes
date_type	char varying (2)		Yes
created_on	timestamp (no tz)		Yes
updated_on	timestamp (no tz)		Yes
verified_on	timestamp (no tz)		Yes
created_by_id	integer		Yes
group_id	integer		Yes
taxa_taxon_list_id	integer		Yes
preferred_taxa_taxon_list_id	integer		Yes
taxon_meaning_id	integer		Yes
taxa_taxon_list_external_key	char varying (50)		Yes
family_taxa_taxon_list_id	integer		Yes
taxon_group_id	integer		Yes
taxon_rank_sort_order	integer		Yes
record_status	character (1)		Yes
record_substatus	smallint		Yes
certainty	character (1)		Yes
query	character (1)		Yes
sensitive	boolean		Yes
release_status	character (1)		Yes
marine_flag	boolean		Yes
data_cleaner_result	boolean		Yes
media_count	integer	0	Yes
training	boolean	false	No
zero_abundance	boolean		Yes
licence_id	integer		Yes
identification_difficulty	integer		Yes	Identification difficulty assigned by the data_cleaner module, on a scale from 1 (easy) to 5 (difficult)
import_guid	char varying		Yes	Globally unique identifier of the import batch.
confidential	boolean	false	Yes
external_key	char varying		Yes
location_ids	ARRAY		Yes
taxon_path	ARRAY		Yes
blocked_sharing_tasks	ARRAY		Yes
parent_sample_id	integer		Yes
verification_checks_enabled	boolean	false	No
tracking	bigint		Yes	Autogenerated tracking number for updates. A report can grab all new or updated records by filtering on this value.
freshwater_flag	boolean	false	No
terrestrial_flag	boolean	false	No
non_native_flag	boolean	false	No

cache_occurrences_nonfunctional

Column	Type	Default	Nullable	Description
id	integer		No
data_cleaner_info	char varying		Yes
media	char varying		Yes
comment	text		Yes
sensitivity_precision	integer		Yes
privacy_precision	integer		Yes
output_sref	char varying		Yes
verifier	char varying		Yes
licence_code	char varying		Yes
attr_sex_stage	char varying		Yes
attr_sex	char varying		Yes
attr_stage	char varying		Yes
attr_sex_stage_count	char varying		Yes
attr_certainty	char varying		Yes
attr_det_first_name	char varying		Yes
attr_det_last_name	char varying		Yes
attr_det_full_name	char varying		Yes
output_sref_system	char varying		Yes
attrs_json	json		Yes
attr_behaviour	char varying		Yes
attr_reproductive_condition	char varying		Yes

cache_samples_functional

Column	Type	Default	Nullable	Description
id	integer		No
website_id	integer		Yes
survey_id	integer		Yes
input_form	char varying		Yes
location_id	integer		Yes
location_name	char varying		Yes
public_geom	geom		Yes
map_sq_1km_id	integer		Yes
map_sq_2km_id	integer		Yes
map_sq_10km_id	integer		Yes
date_start	date		Yes
date_end	date		Yes
date_type	char varying (2)		Yes
created_on	timestamp (no tz)		Yes
updated_on	timestamp (no tz)		Yes
verified_on	timestamp (no tz)		Yes
created_by_id	integer		Yes
group_id	integer		Yes
record_status	character (1)		Yes
query	character (1)		Yes
media_count	integer	0	Yes
location_ids	ARRAY		Yes
blocked_sharing_tasks	ARRAY		Yes
parent_sample_id	integer		Yes
tracking	bigint		Yes	Autogenerated tracking number for updates. A report can grab all new or updated records by filtering on this value.
training	boolean		Yes	Flag indicating if this sample was created for training purposes and is therefore not considered real.

cache_samples_nonfunctional

Column	Type	Default	Nullable	Description
id	integer		No
website_title	char varying		Yes
survey_title	char varying		Yes
group_title	char varying		Yes
public_entered_sref	char varying		Yes
entered_sref_system	char varying		Yes
recorders	char varying		Yes
media	char varying		Yes
comment	text		Yes
privacy_precision	integer		Yes
licence_code	char varying		Yes
attr_email	char varying		Yes
attr_cms_user_id	integer		Yes
attr_cms_username	char varying		Yes
attr_first_name	char varying		Yes
attr_last_name	char varying		Yes
attr_full_name	char varying		Yes
attr_biotope	char varying		Yes
attr_sref_precision	double precision		Yes
attr_linked_location_id	integer		Yes
attr_sample_method	char varying		Yes
attrs_json	json		Yes

cache_taxa_taxon_lists

Column	Type	Default	Nullable	Description
id	integer		No
preferred	boolean		Yes
taxon_list_id	integer		Yes
taxon_list_title	char varying		Yes
website_id	integer		Yes
preferred_taxa_taxon_list_id	integer		Yes
parent_id	integer		Yes
taxonomic_sort_order	bigint		Yes
taxon	char varying		Yes
authority	char varying (100)		Yes
language_iso	char varying (3)		Yes
language	char varying (50)		Yes
preferred_taxon	char varying		Yes
preferred_authority	char varying (100)		Yes
preferred_language_iso	char varying (3)		Yes
preferred_language	char varying (50)		Yes
default_common_name	char varying		Yes
search_name	char varying		Yes
external_key	char varying (50)		Yes
taxon_meaning_id	integer		Yes
taxon_group_id	integer		Yes
taxon_group	char varying (100)		Yes
cache_created_on	timestamp (no tz)		No
cache_updated_on	timestamp (no tz)		No
allow_data_entry	boolean	true	Yes
kingdom_taxa_taxon_list_id	integer		Yes	Preferred taxa_taxon_list record which identifies the Kingdom of this taxon
order_taxa_taxon_list_id	integer		Yes	Preferred taxa_taxon_list record which identifies the Order of this taxon
family_taxa_taxon_list_id	integer		Yes	Preferred taxa_taxon_list record which identifies the Family of this taxon
kingdom_taxon	char varying		Yes	The taxon name of the Kingdom containing this taxon
order_taxon	char varying		Yes	The taxon name of the Order containing this taxon
family_taxon	char varying		Yes	The taxon name of the Family containing this taxon
taxon_rank_id	integer		Yes
taxon_rank_sort_order	integer		Yes
taxon_rank	char varying		Yes
marine_flag	boolean	false	No	Set to true for marine species.
attrs_json	json		Yes
freshwater_flag	boolean	false	No
terrestrial_flag	boolean	false	No
non_native_flag	boolean	false	No
taxon_id	integer		Yes
search_code	char varying (20)		Yes

cache_taxon_searchterms

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No	Unique identifier and primary key for the table.
taxa_taxon_list_id	integer		No	Identifies the taxon list entry which this searchable name applies to.
taxon_list_id	integer		No	Identifies the taxon list which this searchable name is from.
searchterm	char varying		No	Searchable identifier for the taxon. Includes taxon formal and vernacular names, simplified versions of these for searching and codes, abbreviations or other shortcuts used to lookup taxa.
original	char varying		No	When the term is simplified, provides the original unsimplified version of the term.
taxon_group	char varying		No	Name of the taxon group.
taxon_meaning_id	integer		No	Identifies the taxon meaning associated with this name. All names with the same ID refer to the same taxon.
preferred_taxon	char varying		No	Provides the preferred taxon name for a taxon that has been looked up,
default_common_name	char varying		Yes	Provides the preferred common name for a taxon that has been looked up,
preferred_authority	char varying		Yes	The taxonomic authority of the preferred taxon name.
language_iso	char varying		Yes	The language associated with the search term, or null if not language specific.
name_type	character (1)		No	Type of taxon name string. Options are (L)atin, (S)ynonym, (V)ernacular, (O)ther vernacular name, (C)ode, (A)bbreviation.
simplified	boolean	false	Yes	Is this a name which has been simplified make it tolerant of some spelling and punctuation errors when searching.
code_type_id	integer		Yes	For names which are codes, identifies the type of code. Foreign key to the termlists_terms table.
source_id	integer		Yes	When the search term is from a taxon_codes table record, provides the id of the record which the code was source from.
taxon_group_id	integer		Yes	ID of the taxon group
preferred	boolean		Yes	Does this entry represent a list-preferred name?
searchterm_length	integer		Yes	Contains the length of the searchterm field, useful for taxon name searches. Putting shorter searchterms at the top of a list brings the “nearest” matches to the top.
parent_id	integer		Yes	Identifies the parent of the taxon in the hierarchy, if one exists.
preferred_taxa_taxon_list_id	integer		Yes	ID of the preferred version of this term.
identification_difficulty	integer		Yes	Identification difficulty assigned by the data_cleaner module, on a scale from 1 (easy) to 5 (difficult)
id_diff_verification_rule_id	integer		Yes	Verification rule that is associated with the identification difficulty.
taxon_rank_sort_order	integer		Yes
marine_flag	boolean	false	No	Set to true for marine species.
external_key	char varying		Yes	External identifier for the taxon.
authority	char varying		Yes	The taxonomic authority of the name.
search_code	char varying		Yes
taxonomic_sort_order	bigint		Yes
freshwater_flag	boolean	false	No
terrestrial_flag	boolean	false	No
non_native_flag	boolean	false	No

location_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
caption	char varying (50)		Yes	Display caption for the attribute.
data_type	character (1)		Yes	Data type for the attribute. Possible values are T (text), I (integer), F (float), D (date), V (vague date), L (item looked up from a termlist).
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
validation_rules	char varying		Yes	Validation rules defined for this attribute, for example: number, required,max[50].
termlist_id	integer		Yes	For attributes which define a term from a termlist, provides the ID of the termlist the term can be selected from.
multi_value	boolean	false	Yes	Does this attribute allow multiple values? If set to true, then multiple values for this attribute can be stored against a single record.
public	boolean	false	No	Flag set to true if this attribute is available for selection and use by any website. If false the attribute is only available for use in the website which created it.
deleted	boolean	false	No	Has this record been deleted?
system_function	char varying (30)		Yes	Machine readable function of this attribute. Defines how the field can be interpreted by the system.
description	text		Yes	Holds a description for the attribute.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute originated.
caption_i18n	json		Yes	Stores a list of localised versions of the caption keyed by language code.
term_name	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term name. Otherwise provide a brief alphanumeric only (with no spaces) version of the attribute name to give it a unique identifier within the context of the survey dataset to make it easier to refer to in configuration.
term_identifier	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term identifier, typically the URL to the term definition.
allow_ranges	boolean	false	No	Set to true if numeric attributes allow a value range to be entered.
reporting_category_id	integer		Yes	Foreign key to the termlists_terms table. Identifies an optional reporting category for this attribute which can be used to organise the display of multiple attribute values in report outputs.
unit	char varying		Yes	Name of the attributes unit where relevant, e.g. mm
description_i18n	json		Yes	Stores a list of localised versions of the description keyed by language code.
image_path	char varying		Yes	Path to an image file representing the attribute, e.g. an explanatory diagram.

location_attributes_websites

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
website_id	integer		No	Foreign key to the websites table. Identifies the website that the location attribute is available for.
location_attribute_id	integer		No	Foreign key to the location_attributes table. Identifies the location attribute that is available for the website.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
restrict_to_survey_id	integer		Yes	Foreign key to the survey table. For attributes that are only applicable to a given survey, identifies the survey.
deleted	boolean	false	No	Has this record been deleted?
form_structure_block_id	integer		Yes	Additional validation rules that are defined for this attribute but only active within the context of this survey/website.
validation_rules	char varying (500)		Yes
weight	integer	0	No	Dictates the order of controls within the block or at the top level. Controls with a higher weight will sink to the end of the list.
control_type_id	integer		Yes	Foreign key to the control_types table. Identifies the default type of control used for this attribute when used in this survey on a dynamically generated form.
default_text_value	text		Yes	For default text values, provides the value.
default_float_value	double precision		Yes	For default float values, provides the value.
default_int_value	integer		Yes	For default integer values, provides the value. For default lookup values, provides the term id.
default_date_start_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_end_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_type_value	char varying (2)		Yes	For default vague date values, provides the date type identifier.
restrict_to_location_type_id	integer		Yes	If this attribute is only used for a specific location type within the context of the website & survey combination, then specifies the termlist entry for the appropriate location type.
default_upper_value	double precision		Yes	If the attribute allows value ranges, then provides the default upper value of the range.

location_attribute_values

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
location_id	integer		Yes	Foreign key to the locations table. Identifies the location that this value applies to.
location_attribute_id	integer		Yes	Foreign key to the location_attributes table. Identifies the attribute that this value is for.
text_value	text		Yes	For text values, provides the value.
float_value	double precision		Yes	For float values, provides the value.
int_value	integer		Yes	For integer values, provides the value. For lookup values, provides the term id.
date_start_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_end_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_type_value	char varying (2)		Yes	For vague date values, provides the date type identifier.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
source_id	integer		Yes	Points to a termlists_term which describes where the attribute value originated.
upper_value	double precision		Yes	If the attribute allows value ranges, then provides the upper value of the range.

occurrence_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
caption	char varying (50)		Yes	Display caption for the attribute.
data_type	character (1)		Yes	Data type for the attribute. Possible values are T (text), I (integer), F (float), D (date), V (vague date), L (item looked up from a termlist).
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
validation_rules	char varying		Yes	Validation rules defined for this attribute, for example: number, required,max[50].
termlist_id	integer		Yes	For attributes which define a term from a termlist, provides the ID of the termlist the term can be selected from.
multi_value	boolean	false	Yes	Does this attribute allow multiple values? If set to true, then multiple values for this attribute can be stored against a single record.
public	boolean	false	No	Flag set to true if this attribute is available for selection and use by any website. If false the attribute is only available for use in the website which created it.
deleted	boolean	false	No	Has this record been deleted?
system_function	char varying (30)		Yes	Machine readable function of this attribute, e.g. sex/stage. Defines how the field can be interpreted by the system.
description	text		Yes	Holds a description for the attribute.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute originated.
caption_i18n	json		Yes	Stores a list of localised versions of the caption keyed by language code.
term_name	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term name. Otherwise provide a brief alphanumeric only (with no spaces) version of the attribute name to give it a unique identifier within the context of the survey dataset to make it easier to refer to in configuration.
term_identifier	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term identifier, typically the URL to the term definition.
allow_ranges	boolean	false	No	Set to true if numeric attributes allow a value range to be entered.
reporting_category_id	integer		Yes	Foreign key to the termlists_terms table. Identifies an optional reporting category for this attribute which can be used to organise the display of multiple attribute values in report outputs.
unit	char varying		Yes	Name of the attributes unit where relevant, e.g. mm
description_i18n	json		Yes	Stores a list of localised versions of the description keyed by language code.
image_path	char varying		Yes	Path to an image file representing the attribute, e.g. an explanatory diagram.

occurrence_attributes_websites

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
website_id	integer		No	Foreign key to the websites table. Identifies the website that the occurrence attribute is available for.
occurrence_attribute_id	integer		No	Foreign key to the occurrence_attributes table. Identifies the occurrence attribute that is available for the website.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
restrict_to_survey_id	integer		Yes	Foreign key to the survey table. For attributes that are only applicable to a given survey, identifies the survey.
deleted	boolean	false	No	Has this record been deleted?
form_structure_block_id	integer		Yes	Additional validation rules that are defined for this attribute but only active within the context of this survey/website.
validation_rules	char varying (500)		Yes
weight	integer	0	No	Dictates the order of controls within the block or at the top level. Controls with a higher weight will sink to the end of the list.
control_type_id	integer		Yes	Foreign key to the control_types table. Identifies the default type of control used for this attribute when used in this survey on a dynamically generated form.
default_text_value	text		Yes	For default text values, provides the value.
default_float_value	double precision		Yes	For default float values, provides the value.
default_int_value	integer		Yes	For default integer values, provides the value. For default lookup values, provides the term id.
default_date_start_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_end_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_type_value	char varying (2)		Yes	For default vague date values, provides the date type identifier.
default_upper_value	double precision		Yes	If the attribute allows value ranges, then provides the default upper value of the range.

occurrence_attribute_values

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
occurrence_id	integer		Yes	Foreign key to the occurrences table. Identifies the occurrence that this value applies to.
occurrence_attribute_id	integer		Yes	Foreign key to the occurrence_attributes table. Identifies the attribute that this value is for.
text_value	text		Yes	For text values, provides the value.
float_value	double precision		Yes	For float values, provides the value.
int_value	integer		Yes	For integer values, provides the value. For lookup values, provides the term id.
date_start_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_end_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_type_value	char varying (2)		Yes	For vague date values, provides the date type identifier.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
source_id	integer		Yes	Points to a termlists_term which describes where the attribute value originated.
upper_value	double precision		Yes	If the attribute allows value ranges, then provides the upper value of the range.

person_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
caption	char varying (50)		Yes	Display caption for the attribute.
data_type	character (1)		Yes	Data type for the attribute. Possible values are T (text), I (integer), F (float), D (date), V (vague date), L (item looked up from a termlist).
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
validation_rules	char varying		Yes	Validation rules defined for this attribute, for example: number, required,max[50].
termlist_id	integer		Yes	For attributes which define a term from a termlist, provides the ID of the termlist the term can be selected from.
multi_value	boolean	false	Yes	Does this attribute allow multiple values? If set to true, then multiple values for this attribute can be stored against a single record.
public	boolean	false	Yes	Flag set to true if this attribute is available for all people on the warehouse or false if only available when the person is a user of a website linked to the attribute.
deleted	boolean	false	No	Has this record been deleted?
synchronisable	boolean	false	No	Does this attribute allow the values to be synchronised with client website users associated with the person? Set to false for attributes that should keep their values on the warehouse only.
system_function	char varying (30)		Yes	Machine readable function of this attribute. Defines how the field can be interpreted by the system.
description	text		Yes	Holds a description for the attribute.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute originated.
caption_i18n	json		Yes	Stores a list of localised versions of the caption keyed by language code.
term_name	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term name. Otherwise provide a brief alphanumeric only (with no spaces) version of the attribute name to give it a unique identifier within the context of the survey dataset to make it easier to refer to in configuration.
term_identifier	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term identifier, typically the URL to the term definition.
allow_ranges	boolean	false	No	Set to true if numeric attributes allow a value range to be entered.
reporting_category_id	integer		Yes	Foreign key to the termlists_terms table. Identifies an optional reporting category for this attribute which can be used to organise the display of multiple attribute values in report outputs.
unit	char varying		Yes	Name of the attributes unit where relevant, e.g. mm
description_i18n	json		Yes	Stores a list of localised versions of the description keyed by language code.
image_path	char varying		Yes	Path to an image file representing the attribute, e.g. an explanatory diagram.

person_attributes_websites

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
website_id	integer		No	Foreign key to the websites table. Identifies the website that the person attribute is available for.
person_attribute_id	integer		No	Foreign key to the person_attributes table. Identifies the person attribute that is available for the website.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
deleted	boolean	false	No	Has this record been deleted?
form_structure_block_id	integer		Yes	Additional validation rules that are defined for this attribute but only active within the context of this survey/website.
validation_rules	char varying (500)		Yes
weight	integer	0	No	Dictates the order of controls within the block or at the top level. Controls with a higher weight will sink to the end of the list.
control_type_id	integer		Yes	Foreign key to the control_types table. Identifies the default type of control used for this attribute when used in this survey on a dynamically generated form.
default_text_value	text		Yes	For default text values, provides the value.
default_float_value	double precision		Yes	For default float values, provides the value.
default_int_value	integer		Yes	For default integer values, provides the value. For default lookup values, provides the term id.
default_date_start_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_end_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_type_value	char varying (2)		Yes	For default vague date values, provides the date type identifier.
default_upper_value	double precision		Yes	If the attribute allows value ranges, then provides the default upper value of the range.

person_attribute_values

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
person_id	integer		Yes	Foreign key to the people table. Identifies the person that this value applies to.
person_attribute_id	integer		Yes	Foreign key to the person_attributes table. Identifies the attribute that this value is for.
text_value	text		Yes	For text values, provides the value.
float_value	double precision		Yes	For float values, provides the value.
int_value	integer		Yes	For integer values, provides the value. For lookup values, provides the term id.
date_start_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_end_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_type_value	char varying (2)		Yes	For vague date values, provides the date type identifier.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
source_id	integer		Yes	Points to a termlists_term which describes where the attribute value originated.
upper_value	double precision		Yes	If the attribute allows value ranges, then provides the upper value of the range.

sample_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
caption	char varying (50)		Yes	Display caption for the attribute.
data_type	character (1)		Yes	Data type for the attribute. Possible values are T (text), I (integer), F (float), D (date), V (vague date), L (item looked up from a termlist).
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
applies_to_location	boolean	false	No	For attributes that are gathered which pertain to the site or location rather than the specific sample, this flag is set to true.
validation_rules	char varying		Yes	Validation rules defined for this attribute, for example: number, required,max[50].
termlist_id	integer		Yes	For attributes which define a term from a termlist, provides the ID of the termlist the term can be selected from.
multi_value	boolean	false	Yes	Does this attribute allow multiple values? If set to true, then multiple values for this attribute can be stored against a single record.
public	boolean	false	No	Flag set to true if this attribute is available for selection and use by any website. If false the attribute is only available for use in the website which created it.
deleted	boolean	false	No	Has this record been deleted?
applies_to_recorder	boolean	false	No	For attributes that are gathered which pertain to the person recording the sample rather than the specific sample, this flag is set to true.
system_function	char varying (30)		Yes	Machine readable function of this attribute, e.g. email, cms user ID. Defines how the field can be interpreted by the system.
description	text		Yes	Holds a description for the attribute.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute originated.
caption_i18n	json		Yes	Stores a list of localised versions of the caption keyed by language code.
term_name	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term name. Otherwise provide a brief alphanumeric only (with no spaces) version of the attribute name to give it a unique identifier within the context of the survey dataset to make it easier to refer to in configuration.
term_identifier	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term identifier, typically the URL to the term definition.
allow_ranges	boolean	false	No	Set to true if numeric attributes allow a value range to be entered.
reporting_category_id	integer		Yes	Foreign key to the termlists_terms table. Identifies an optional reporting category for this attribute which can be used to organise the display of multiple attribute values in report outputs.
unit	char varying		Yes	Name of the attributes unit where relevant, e.g. mm
description_i18n	json		Yes	Stores a list of localised versions of the description keyed by language code.
image_path	char varying		Yes	Path to an image file representing the attribute, e.g. an explanatory diagram.

sample_attributes_websites

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
website_id	integer		No	Foreign key to the websites table. Identifies the website that the sample attribute is available for.
sample_attribute_id	integer		No	Foreign key to the sample attributes table. Identifies the sample attribute that is available for the website.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
restrict_to_survey_id	integer		Yes	Foreign key to the survey table. For attributes that are only applicable to a given survey, identifies the survey.
deleted	boolean	false	No	Has this record been deleted?
form_structure_block_id	integer		Yes	Additional validation rules that are defined for this attribute but only active within the context of this survey/website.
validation_rules	char varying (500)		Yes
weight	integer	0	No	Dictates the order of controls within the block or at the top level. Controls with a higher weight will sink to the end of the list.
control_type_id	integer		Yes	Foreign key to the control_types table. Identifies the default type of control used for this attribute when used in this survey on a dynamically generated form.
default_text_value	text		Yes	For default text values, provides the value.
default_float_value	double precision		Yes	For default float values, provides the value.
default_int_value	integer		Yes	For default integer values, provides the value. For default lookup values, provides the term id.
default_date_start_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_end_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_type_value	char varying (2)		Yes	For default vague date values, provides the date type identifier.
restrict_to_sample_method_id	integer		Yes	If this attribute is only used for a specific sample method within the context of the website & survey combination, then specifies the termlist entry for the appropriate sample method.
default_upper_value	double precision		Yes	If the attribute allows value ranges, then provides the default upper value of the range.

sample_attribute_values

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
sample_id	integer		Yes	Foreign key to the samples table. Identifies the sample that this value applies to.
sample_attribute_id	integer		Yes	Foreign key to the sample_attributes table. Identifies the attribute that this value is for.
text_value	text		Yes	For text values, provides the value.
float_value	double precision		Yes	For float values, provides the value.
int_value	integer		Yes	For integer values, provides the value. For lookup values, provides the term id.
date_start_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_end_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_type_value	char varying (2)		Yes	For vague date values, provides the date type identifier.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
source_id	integer		Yes	Points to a termlists_term which describes where the attribute value originated.
upper_value	double precision		Yes	If the attribute allows value ranges, then provides the upper value of the range.

survey_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
caption	char varying (50)		Yes	Display caption for the attribute.
data_type	character (1)		Yes	Data type for the attribute. Possible values are T (text), I (integer), F (float), D (date), V (vague date), L (item looked up from a termlist).
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
validation_rules	char varying		Yes	Validation rules defined for this attribute, for example: number, required,max[50].
termlist_id	integer		Yes	For attributes which define a term from a termlist, provides the ID of the termlist the term can be selected from.
multi_value	boolean	false	Yes	Does this attribute allow multiple values? If set to true, then multiple values for this attribute can be stored against a single record.
public	boolean	false	No	Flag set to true if this attribute is available for selection and use by any website. If false the attribute is only available for use in the website which created it.
deleted	boolean	false	No	Has this record been deleted?
system_function	char varying (30)		Yes	Machine readable function of this attribute. Defines how the field can be interpreted by the system.
description	text		Yes	Holds a description for the attribute.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute originated.
caption_i18n	json		Yes	Stores a list of localised versions of the caption keyed by language code.
term_name	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term name. Otherwise provide a brief alphanumeric only (with no spaces) version of the attribute name to give it a unique identifier within the context of the survey dataset to make it easier to refer to in configuration.
term_identifier	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term identifier, typically the URL to the term definition.
allow_ranges	boolean	false	No	Set to true if numeric attributes allow a value range to be entered.
reporting_category_id	integer		Yes	Foreign key to the termlists_terms table. Identifies an optional reporting category for this attribute which can be used to organise the display of multiple attribute values in report outputs.
unit	char varying		Yes	Name of the attributes unit where relevant, e.g. mm.
description_i18n	json		Yes	Stores a list of localised versions of the description keyed by language code.
image_path	char varying		Yes	Path to an image file representing the attribute, e.g. an explanatory diagram.

survey_attributes_websites

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
website_id	integer		No	Foreign key to the websites table. Identifies the website that the survey attribute is available for.
survey_attribute_id	integer		No	Foreign key to the survey_attributes table. Identifies the survey attribute that is available for the website.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
deleted	boolean	false	No	Has this record been deleted?
form_structure_block_id	integer		Yes	Additional validation rules that are defined for this attribute but only active within the context of this survey/website.
validation_rules	char varying (500)		Yes
weight	integer	0	No	Dictates the order of controls within the block or at the top level. Controls with a higher weight will sink to the end of the list.
control_type_id	integer		Yes	Foreign key to the control_types table. Identifies the default type of control used for this attribute when used in this survey on a dynamically generated form.
default_text_value	text		Yes	For default text values, provides the value.
default_float_value	double precision		Yes	For default float values, provides the value.
default_int_value	integer		Yes	For default integer values, provides the value. For default lookup values, provides the term id.
default_date_start_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_end_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_type_value	char varying (2)		Yes	For default vague date values, provides the date type identifier.
default_upper_value	double precision		Yes	If the attribute allows value ranges, then provides the default upper value of the range.

survey_attribute_values

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
survey_id	integer		Yes	Foreign key to the surveys table. Identifies the survey that this value applies to.
survey_attribute_id	integer		Yes	Foreign key to the survey_attributes table. Identifies the attribute that this value is for.
text_value	text		Yes	For text values, provides the value.
float_value	double precision		Yes	For float values, provides the value.
int_value	integer		Yes	For integer values, provides the value. For lookup values, provides the term id.
date_start_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_end_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_type_value	char varying (2)		Yes	For vague date values, provides the date type identifier.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
source_id	integer		Yes	Points to a termlists_term which describes where the attribute value originated.
upper_value	double precision		Yes	If the attribute allows value ranges, then provides the upper value of the range.

taxa_taxon_list_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
caption	char varying (50)		Yes	Display caption for the attribute.
data_type	character (1)		Yes	Data type for the attribute. Possible values are T (text), I (integer), F (float), D (date), V (vague date), L (item looked up from a termlist), G (geometry).
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
validation_rules	char varying		Yes	Validation rules defined for this attribute, for example: number, required,max[50].
termlist_id	integer		Yes	For attributes which define a term from a termlist, provides the ID of the termlist the term can be selected from.
multi_value	boolean	false	Yes	Does this attribute allow multiple values? If set to true, then multiple values for this attribute can be stored against a single record.
public	boolean	false	No	Flag set to true if this attribute is available for selection and use by any website. If false the attribute is only available for use in the website which created it.
deleted	boolean	false	No	Has this record been deleted?
system_function	char varying (30)		Yes	Machine readable function of this attribute. Defines how the field can be interpreted by the system.
description	text		Yes	Holds a description for the attribute.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute originated.
caption_i18n	json		Yes	Stores a list of localised versions of the caption keyed by language code.
term_name	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term name. Otherwise provide a brief alphanumeric only (with no spaces) version of the attribute name to give it a unique identifier within the context of the survey dataset to make it easier to refer to in configuration.
term_identifier	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term identifier, typically the URL to the term definition.
allow_ranges	boolean	false	No	Set to true if numeric attributes allow a value range to be entered.
reporting_category_id	integer		Yes	Foreign key to the termlists_terms table. Identifies an optional reporting category for this attribute which can be used to organise the display of multiple attribute values in report outputs.
unit	char varying		Yes	Name of the attributes unit where relevant, e.g. mm
description_i18n	json		Yes	Stores a list of localised versions of the description keyed by language code.
image_path	char varying		Yes	Path to an image file representing the attribute, e.g. an explanatory diagram.

taxon_lists_taxa_taxon_list_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
taxon_list_id	integer		No	Foreign key to the taxon_lists table. Identifies the taxon_lists that the taxa_taxon_list attribute is available for.
taxa_taxon_list_attribute_id	integer		No	Foreign key to the taxa_taxon_list_attributes table. Identifies the taxa_taxon_list attribute that is available for the taxon list.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
deleted	boolean	false	No	Has this record been deleted?
form_structure_block_id	integer		Yes	Additional validation rules that are defined for this attribute but only active within the context of this taxon list.
validation_rules	char varying (500)		Yes
weight	integer	0	No	Dictates the order of controls within the block or at the top level. Controls with a higher weight will sink to the end of the list.
control_type_id	integer		Yes	Foreign key to the control_types table. Identifies the default type of control used for this attribute when used in this taxon list on a dynamically generated form.
default_text_value	text		Yes	For default text values, provides the value.
default_float_value	double precision		Yes	For default float values, provides the value.
default_int_value	integer		Yes	For default integer values, provides the value. For default lookup values, provides the term id.
default_date_start_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_end_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_type_value	char varying (2)		Yes	For default vague date values, provides the date type identifier.
default_upper_value	double precision		Yes	If the attribute allows value ranges, then provides the default upper value of the range.

taxa_taxon_list_attribute_values

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
taxa_taxon_list_id	integer		Yes	Foreign key to the taxa_taxon_lists table. Identifies the taxa_taxon_list that this value applies to.
taxa_taxon_list_attribute_id	integer		Yes	Foreign key to the taxa_taxon_list_attributes table. Identifies the attribute that this value is for.
text_value	text		Yes	For text values, provides the value.
float_value	double precision		Yes	For float values, provides the value.
int_value	integer		Yes	For integer values, provides the value. For lookup values, provides the term id.
date_start_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_end_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_type_value	char varying (2)		Yes	For vague date values, provides the date type identifier.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
geom_value	geom		Yes	For geometry values, stores the geometry.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute value originated.
upper_value	double precision		Yes	If the attribute allows value ranges, then provides the upper value of the range.

termlists_term_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
caption	char varying (50)		Yes	Display caption for the attribute.
description	char varying		Yes	Description of the attribute.
data_type	character (1)		Yes	Data type for the attribute. Possible values are T (text), I (integer), F (float), D (date), V (vague date), L (item looked up from a termlist).
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
validation_rules	char varying		Yes	Validation rules defined for this attribute, for example: number, required,max[50].
termlist_id	integer		Yes	For attributes which define a term from a termlist, provides the ID of the termlist the term can be selected from.
multi_value	boolean	false	Yes	Does this attribute allow multiple values? If set to true, then multiple values for this attribute can be stored against a single record.
public	boolean	false	No	Flag set to true if this attribute is available for selection and use by any website. If false the attribute is only available for use in the website which created it.
deleted	boolean	false	No	Has this record been deleted?
system_function	char varying (30)		Yes	Machine readable function of this attribute. Defines how the field can be interpreted by the system.
source_id	integer		Yes	Points to a termlists_term which describes where the attribute originated.
caption_i18n	json		Yes	Stores a list of localised versions of the caption keyed by language code.
term_name	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term name. Otherwise provide a brief alphanumeric only (with no spaces) version of the attribute name to give it a unique identifier within the context of the survey dataset to make it easier to refer to in configuration.
term_identifier	char varying		Yes	If the attribute is linked to a standardised glossary such as Darwin Core then provide the term identifier, typically the URL to the term definition.
allow_ranges	boolean	false	No	Set to true if numeric attributes allow a value range to be entered.
reporting_category_id	integer		Yes	Foreign key to the termlists_terms table. Identifies an optional reporting category for this attribute which can be used to organise the display of multiple attribute values in report outputs.
unit	char varying		Yes	Name of the attributes unit where relevant, e.g. mm
description_i18n	json		Yes	Stores a list of localised versions of the description keyed by language code.
image_path	char varying		Yes	Path to an image file representing the attribute, e.g. an explanatory diagram.

termlists_termlists_term_attributes

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
termlist_id	integer		No	Foreign key to the termlists table. Identifies the termlist that the termlists_term attribute is available for.
termlists_term_attribute_id	integer		No	Foreign key to the termlists_term_attributes table. Identifies the termlists_term attribute that is available for the website.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
deleted	boolean	false	No	Has this record been deleted?
form_structure_block_id	integer		Yes	Additional validation rules that are defined for this attribute but only active within the context of this termlist.
validation_rules	char varying (500)		Yes
weight	integer	0	No	Dictates the order of controls within the block or at the top level. Controls with a higher weight will sink to the end of the list.
control_type_id	integer		Yes	Foreign key to the control_types table. Identifies the default type of control used for this attribute when used in this termlist on a dynamically generated form.
default_text_value	text		Yes	For default text values, provides the value.
default_float_value	double precision		Yes	For default float values, provides the value.
default_int_value	integer		Yes	For default integer values, provides the value. For default lookup values, provides the term id.
default_date_start_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_end_value	date		Yes	For default vague date and date values, provides the start date of the range of dates covered by the date.
default_date_type_value	char varying (2)		Yes	For default vague date values, provides the date type identifier.
default_upper_value	double precision		Yes	If the attribute allows value ranges, then provides the default upper value of the range.

termlists_term_attribute_values

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
termlists_term_id	integer		Yes	Foreign key to the termlists_terms table. Identifies the termlists_term that this value applies to.
termlists_term_attribute_id	integer		Yes	Foreign key to the termlists_term_attributes table. Identifies the attribute that this value is for.
text_value	text		Yes	For text values, provides the value.
float_value	double precision		Yes	For float values, provides the value.
int_value	integer		Yes	For integer values, provides the value. For lookup values, provides the term id.
date_start_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_end_value	date		Yes	For vague date and date values, provides the start date of the range of dates covered by the date.
date_type_value	char varying (2)		Yes	For vague date values, provides the date type identifier.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
source_id	integer		Yes	Points to a termlists_term which describes where the attribute value originated.
upper_value	double precision		Yes	If the attribute allows value ranges, then provides the upper value of the range.

people

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
first_name	char varying (50)		No	First name of the person.
surname	char varying (50)		No	Surname of the person.
initials	char varying (6)		Yes	Initials of the person.
email_address	char varying (100)		Yes	Email address of the person.
website_url	char varying (1000)		Yes	Website URL for the person.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Optional persons address.
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
title_id	integer		Yes	Foreign key to the titles table.
address	char varying (200)		Yes
deleted	boolean	false	No	Has this record been deleted?
external_key	char varying (50)		Yes	For people imported from an external system, provides a field to store the external system’s primary key for the record allowing re-synchronisation.

users

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
person_id	integer		Yes	Foreign key to the people table. Identifies the person record that this user is associated with.
core_role_id	integer		Yes	Foreign key to the core_roles table. Identifies the user’s role within the core module.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
username	char varying (30)		No
password	char varying		Yes
forgotten_password_key	char varying		Yes
deleted	boolean	false	No	Has this record been deleted?
default_digest_mode	character (1)	‘D’::bpchar	No	Specifies the default digest behaviour of notifications for this user. Options are null N (no email), I (immediate), D (daily), W (weekly).
allow_share_for_reporting	boolean	true	No	Flag set to true if the user allows their records to be reported by users on other websites that have a sharing agreement with the site they have contributed to.
allow_share_for_peer_review	boolean	true	No	Flag set to true if the user allows their records to be reviewed by users on other websites that have a sharing agreement with the site they have contributed to.
allow_share_for_verification	boolean	true	No	Flag set to true if the user allows their records to be verified by users on other websites that have a sharing agreement with the site they have contributed to.
allow_share_for_data_flow	boolean	true	No	Flag set to true if the user allows their records to be passed on by other websites that have a sharing agreement with the site they have contributed to.
allow_share_for_moderation	boolean	true	No	Flag set to true if the user allows their records to be moderated by users on other websites that have a sharing agreement with the site they have contributed to.
allow_share_for_editing	boolean	true	No	Flag set to true if the user allows their records to be edited by users on other websites that have a sharing agreement with the site they have contributed to.

users_websites

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
user_id	integer		No	Foreign key to the users table. Identifies the user with access to the website.
website_id	integer		No	Foreign key to the websites table. Identifies the website accessible by the user.
activated	boolean	false	No	Flag indicating if the user’s account has been activated.
banned	boolean	false	No	Flag indicating if the user’s account has been banned from this site.
activation_key	char varying (128)		Yes	Unique key used by the activation process.
site_role_id	integer		Yes	Foreign key to the site_roles table. Identifies the role of the user on this specific site.
registration_datetime	timestamp (no tz)		Yes	Date and time of registration on this website.
last_login_datetime	timestamp (no tz)		Yes	Date and time of last login to this website.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
preferred_sref_system	char varying (10)		Yes	Spatial reference system used for data entry and viewing of spatial data by this user of the website.
licence_id	integer		Yes	ID of the licence that is granted for media added this website by this user.
media_licence_id	integer		Yes

groups

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No	Primary key and unique identifier for the table
title	char varying		No	Title for the group
description	char varying		Yes	Description of the group
filter_id	integer		Yes	Foreign key to the filters table. Identifies the scope of the group.
joining_method	character (1)		No	Defines how a user can join a group. Options are P (public, anyone can join), R (by request or invite, group admins approve members), I (invite only, group admins send invites).
website_id	integer		No	Foreign key to the websites table, identifies the website that hosts this group.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
code	char varying (20)		Yes	A code or abbreviation identifying the group.
group_type_id	integer		No	Foreign key to the termlists_terms table. Identifies the type of group, e.g. recording group, project, organisation.
from_date	date		Yes	Date the group’s activities commenced if relevent, e.g. a project start date.
to_date	date		Yes	Date the group’s activities ceased if relevent, e.g. a project finish date.
private_records	boolean	false	Yes	Set to true to indicate that the records input which are directly linked to the group should be witheld from uses outside the group. Relies on reporting queries to respect this.
implicit_record_inclusion	boolean	false	Yes	If true, then records are included in this group’s content if they are posted by a group member and meet the groups filter criteria. If false, then records must be explicitly posted into the group by a group member. If null, then they are included if they match the filter no matter who or how they were posted.
view_full_precision	boolean	false	No	Allow group members to view records explicitly posted into the at full precision.
logo_path	char varying		Yes	Path to the group logo file, within the server’s upload directory.
licence_id	integer		Yes	ID of the licence that is associated with this group and the records submitted to it.

groups_users

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No	Unique identifier and primary key for the table.
group_id	integer		Yes	Foreign key to the groups table. Identifies the group of which the user is a member.
user_id	integer		Yes	Foreign key to the users table. Identifies the user who is a member of the group.
administrator	boolean	false	No	Is this user an administrator of this group.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
pending	boolean	false	Yes	Is the membership pending approval by the group admin?
access_level	integer		Yes	Access level value that this user has within this group. Unlocks access to pages with the same or lower access level. Null is treated as a value of zero

group_pages

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No	Unique identifier and primary key for the table.
group_id	integer		Yes	Foreign key to the groups table. Identifies the group of which the user is a member.
caption	char varying		No	Caption shown for this form when viewed in the group.
path	char varying		No	Path to the page on the client site which is enabled for this group
administrator	boolean	false	Yes	Set to true for pages that require group admin rights to be able to see them, false for pages that require normal membership and null for pages that are accessible to all.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
access_level	integer		Yes	Optional minimum access level value required by the user in order to be able to use this page (in addition to the rules defined in group_pages.administrator). Null is treated as a value of zero (i.e. open access).

filters

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No	Primary key and unique identifier for the table.
title	char varying		No	Title of the filter
description	char varying		Yes	Optional description of the filter.
definition	char varying		No	A JSON string holding a definition of the filter parameters. Decodes to an array of key value pairs suitable for passing to a report supporting the standard report parameters.
sharing	character (1)		Yes	Identifies the record sharing task that this filter is for. Options are R(eporting), P(eer review), V(erification), D(ata flow), M(oderation).
public	boolean	false	Yes	Flag indicating when a filter is publically available and discoverable. Non-public filters can only be allocated to users by the creator.
defines_permissions	boolean	false	Yes	Flag indicating when a filter defines a limited set of permissions for the user having the filter. E.g. this could describe a set of records that a user is able to verify, any number of sets can be defined per user.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
deleted	boolean	false	No	Has this record been deleted?
website_id	integer		Yes	Foreign key to the websites table. Optionally limits the filter to being available on this website.

locations

Column	Type	Default	Nullable	Description
id	integer	nextval(…)	No
name	char varying (100)		No	Name of the location.
code	char varying (20)		Yes	Location reference code.
parent_id	integer		Yes	Identifies the location’s parent location, if there is one.
centroid_sref	char varying (40)		No	Spatial reference at the centre of the location.
centroid_sref_system	char varying (10)		No	System used for the centroid_sref field.
created_on	timestamp (no tz)		No	Date this record was created.
created_by_id	integer		No	Foreign key to the users table (creator).
updated_on	timestamp (no tz)		No	Date this record was last updated.
updated_by_id	integer		No	Foreign key to the users table (last updater).
comment	text		Yes	Comment regarding the location.
external_key	char varying (50)		Yes	For locations imported from an external system, provides a field to store the external system’s primary key for the record allowing re-synchronisation.
deleted	boolean	false	No	Has this record been deleted?
centroid_geom	geom		Yes	Geometry of the spatial reference at the centre of the location. This is a point, or a polygon for grid references. Uses Latitude and Longitude on the WGS84 datum.
boundary_geom	geom		Yes	Polygon for the location’s boundary. Uses Latitude and Longitude on the WGS84 datum.
location_type_id	integer		Yes	Identifies term describing the type of the location. Foreign key to the termlists_terms table.
public	boolean	false	No	Flag set to true if this location is available for use by any website. If false the location is only available for use by the websites in the locations_websites table.