Database Properties
Database Type: PostgreSQL - 17.4 (Debian 17.4-1.pgdg110+2)
Schema public
standard public schema
Tables
| Table / View | Children | Parents | Columns | Rows | Type | Comments |
|---|---|---|---|---|---|---|
| upload_types | 1 | 0 | 3 | -1 | Table | (Enum) Types of user-uploaded files whose progress can be tracked in the uploads table. |
| notification_types | 2 | 1 | 3 | -1 | Table | (Enum) Types of notifications in the application. |
| managed_location_types | 1 | 0 | 2 | -1 | Table | (Enum) Type of managed location for business analytics purposes. |
| species | 24 | 7 | 26 | -1 | Table | Per-organization information about species. |
| test_clock | 0 | 0 | 2 | -1 | Table | User-adjustable clock for test environments. Not used in production. |
| organization_report_settings | 0 | 1 | 2 | -1 | Table | Organization-level settings for quarterly reports. Project-level settings are in |
| upload_problem_types | 1 | 0 | 2 | -1 | Table | (Enum) Specific types of problems encountered while processing a user-uploaded file. |
| user_types | 1 | 0 | 2 | -1 | Table | (Enum) Types of users. Most users are of type 1, “Individual.” |
| successional_groups | 1 | 0 | 2 | -1 | Table | (Enum) Successional Groups that a plant may be planted in. |
| project_land_use_model_types | 0 | 2 | 2 | -1 | Table | Which projects have which types of land use models. |
| project_internal_users | 0 | 5 | 8 | -1 | Table | Which users have which internal roles on a project. |
| birdnet_results | 0 | 3 | 7 | -1 | Table | Results of BirdNET detection pipeline from video files. |
| chat_memory_conversations | 1 | 1 | 4 | -1 | Table | Information about Ask Terraware conversations. |
| file_batches | 1 | 3 | 5 | -1 | Table | Groups of files that were uploaded together. |
| species_successional_groups | 0 | 2 | 2 | -1 | Table | The successional groupings that the species is planted in. |
| countries | 10 | 1 | 5 | 249 | Table | Country information per ISO-3166. |
| disclaimers | 1 | 0 | 3 | -1 | Table | Disclaimers content and effective dates. |
| app_versions | 0 | 0 | 4 | -1 | Table | Minimum and recommended versions for Terraware mobile apps. |
| species_growth_forms | 0 | 2 | 2 | -1 | Table | Growth forms of each species |
| timeseries_types | 1 | 0 | 2 | -1 | Table | (Enum) Data formats of the values of a timeseries. |
| device_templates | 0 | 1 | 12 | -1 | Table | Canned device configurations for use in cases where we want to show a list of possible devices to the user and create the selected device with the correct settings so that the device manager can talk to it. |
| roles | 1 | 0 | 2 | -1 | Table | (Enum) Roles a user is allowed to have in an organization. |
| country_subdivisions | 1 | 1 | 3 | 57 | Table | Country subdivision (state, province, region, etc.) information per ISO-3166-2. |
| internal_tags | 1 | 2 | 8 | -1 | Table | Internal (non-user-facing) tags. Low-numbered tags are defined by the system; the rest may be edited by super admins. |
| species_plant_material_sourcing_methods | 0 | 2 | 2 | -1 | Table | Sourcing methods for the plant material used to grow a particular species. |
| timeseries_values | 0 | 1 | 3 | -1 | Table | Individual data points on a timeseries. For example, each time the temperature is read from a thermometer, the reading is inserted here. |
| uploads | 1 | 5 | 11 | -1 | Table | Information about the status of files uploaded by users. This is used to track the progress of file processing such as importing datafiles; contents of this table may expire and be deleted after a certain amount of time. |
| seed_fund_report_photos | 0 | 2 | 3 | -1 | Table | Linking table between |
| users | 143 | 3 | 16 | -1 | Table | User identities. A user can be associated with organizations via |
| organization_managed_location_types | 0 | 2 | 2 | -1 | Table | Per-organization information about managed location types for business analytics purposes. |
| splat_annotations | 0 | 3 | 11 | -1 | Table | Annotations that should be displayed inside splat models. |
| wcvp_taxa | 1 | 0 | 12 | -1 | Table | World Checklist of Vascular Plants species taxonomy. |
| chat_memory_message_types | 1 | 0 | 2 | -1 | Table | (Enum) Message types that can be stored as part of the memory of an LLM chat conversation. |
| seed_fund_report_files | 0 | 2 | 2 | -1 | Table | Linking table between |
| user_preferences | 0 | 2 | 3 | -1 | Table | Client-defined preferences that should persist across browser sessions. |
| notification_criticalities | 1 | 0 | 2 | -1 | Table | (Enum) Criticality information of notifications in the application. |
| seed_fund_report_statuses | 1 | 0 | 2 | -1 | Table | (Enum) Describes where in the workflow each partner report is. |
| species_problem_types | 1 | 0 | 2 | -1 | Table | (Enum) Specific types of problems that can be detected in species data. |
| automations | 0 | 4 | 15 | -1 | Table | Configuration of automatic processes run by the device manager. |
| country_botanical_countries | 0 | 2 | 2 | -1 | Table | Which countries contain (either entirely or partially) which botanical countries. |
| regions | 1 | 0 | 2 | -1 | Table | (Enum) Parts of the world where countries are located. |
| species_problems | 0 | 3 | 6 | -1 | Table | Problems found in species data. Rows are deleted from this table when the problem is marked as ignored by the user or the user accepts the suggested fix. |
| notifications | 0 | 3 | 9 | -1 | Table | Notifications for application users. |
| wood_density_levels | 1 | 0 | 2 | -1 | Table | The taxonomic level in the at which a wood density measurement is known |
| thumbnails | 0 | 1 | 9 | -1 | Table | Information about server-generated versions of photos and still images of videos. |
| device_template_categories | 1 | 0 | 2 | -1 | Table | (Enum) User-facing categories of device templates; used to show templates for a particular class of devices where the physical device type may differ from one entry to the next. |
| projects | 50 | 6 | 11 | -1 | Table | Distinguishes among an organization’s projects. |
| facilities | 10 | 6 | 23 | -1 | Table | Physical locations at a site. For example, each seed bank and each nursery is a facility. |
| asset_statuses | 3 | 0 | 2 | -1 | Table | (Enum) Possible statuses of externally-generated assets as they’re processed. |
| project_internal_roles | 1 | 0 | 2 | -1 | Table | (Enum) Roles a user is allowed to have on a project. |
| species_ecosystem_types | 0 | 2 | 2 | -1 | Table | Ecosystems where each species can be found. |
| species_problem_fields | 1 | 0 | 2 | -1 | Table | (Enum) Species fields that can be scanned for problems. |
| devices | 3 | 4 | 15 | -1 | Table | Hardware devices managed by the device manager at a facility. |
| facility_types | 1 | 0 | 2 | -1 | Table | (Enum) Types of facilities that can be represented in the data model. |
| organization_types | 1 | 0 | 2 | -1 | Table | (Enum) Type of forestry organization for business analytics purposes. |
| timeseries | 1 | 4 | 11 | -1 | Table | Properties of a series of values collected from a device. Each device metric is represented as a timeseries. |
| upload_statuses | 1 | 0 | 3 | -1 | Table | (Enum) Available statuses of user-uploaded files. Uploads progress through these statuses as the system processes the files. |
| seed_fund_reports | 2 | 6 | 14 | -1 | Table | Partner-submitted reports about their organizations and projects. |
| project_report_settings | 0 | 1 | 2 | -1 | Table | Which projects require reports to be submitted each quarter. Organization-level settings are in |
| organizations | 18 | 7 | 15 | -1 | Table | Top-level information about organizations. |
| seed_storage_behaviors | 1 | 0 | 2 | -1 | Table | (Enum) How seeds of a particular species behave in storage. |
| chat_memory_messages | 0 | 2 | 5 | -1 | Table | Messages from Ask Terraware conversations. Both questions (prompts to the LLM) and answers (responses from the LLM) are recorded. |
| identifier_sequences | 0 | 1 | 3 | -1 | Table | Current state for generating user-facing identifiers (accession number, etc.) for each organization. |
| file_batch_types | 1 | 0 | 2 | -1 | Table | (Enum) Types of file batches. |
| seed_treatments | 3 | 0 | 2 | -1 | Table | (Enum) Techniques that can be used to treat seeds before testing them for viability. |
| file_access_tokens | 0 | 2 | 5 | -1 | Table | Temporary tokens for unauthenticated access to files from the file store. |
| facility_connection_states | 1 | 0 | 2 | -1 | Table | (Enum) Progress of the configuration of a device manager for a facility. |
| growth_forms | 1 | 0 | 2 | -1 | Table | (Enum) What physical form a particular species takes. For example, “Tree” or “Shrub.” |
| griis_taxa | 0 | 2 | 11 | -1 | Table | Information about which species are introduced or invasive in which locations. |
| files | 21 | 3 | 12 | -1 | Table | Generic information about individual files. Files are associated with application entities using linking tables such as |
| time_zones | 5 | 0 | 1 | 604 | Table | (Enum) Valid time zone names. This is populated with the list of names from the IANA time zone database. |
| sub_locations | 5 | 3 | 7 | -1 | Table | The available locations where seeds can be stored at a seed bank facility or seedlings can be stored at a nursery facility. |
| external_dataset_types | 1 | 0 | 2 | -1 | Table | Types of external data sets that the system can pull information from. |
| user_global_roles | 0 | 2 | 2 | -1 | Table | Which users have which global roles. |
| mux_assets | 0 | 2 | 7 | -1 | Table | Information about Mux (video streaming) assets associated with files. |
| upload_problems | 0 | 2 | 8 | -1 | Table | Details about problems (validation failures, etc.) in user-uploaded files. |
| botanical_countries | 4 | 0 | 5 | -1 | Table | Geographic areas of each of the TDWG Level 3 regions. |
| plant_material_sourcing_methods | 1 | 0 | 2 | -1 | Table | (Enum) Sourcing methods for acquiring plant material. |
| splats | 0 | 5 | 18 | -1 | Table | Information about 3D Gaussian splatting models generated from video files. |
| wcvp_distributions | 0 | 3 | 7 | -1 | Table | World Checklist of Vascular Plants distribution data, indicating which species are found in which botanical countries. |
| organization_internal_tags | 0 | 3 | 4 | -1 | Table | Which internal (non-user-facing) tags apply to which organizations. |
| global_roles | 1 | 0 | 2 | -1 | Table | (Enum) System-wide roles that can be assigned to users. Global roles are not tied to organizations. These are generally for system or business administration; most users have no global roles. |
| organization_media_files | 0 | 2 | 3 | -1 | Table | Media files (photos, videos, etc.) associated with organizations. |
| vector_store | 0 | 0 | 4 | -1 | Table | Content snippets and embeddings for use as context in Ask Terraware LLM prompts. This table is used by the Spring AI vector store library, and its structure needs to match what the library expects. |
| conservation_categories | 1 | 0 | 2 | -1 | Table | (Enum) IUCN conservation category codes. |
| event_log | 1 | 1 | 7 | -1 | Table | Holds historical information about what operations were performed at what times. Events are JSON-serialized objects that may contain identifiers such as organization IDs; identifiers are not treated as foreign keys, so events may contain references to objects that were subsequently deleted. |
| griis_resources | 1 | 1 | 5 | -1 | Table | Resource-level metadata from the Global Register of Introduced and Invasive Species. |
| species_nativities | 2 | 0 | 2 | -1 | Table | (Enum) Native/invasive/introduced values for a species in a given region. |
| external_dataset_imports | 0 | 1 | 3 | -1 | Table | The most recently imported versions of external data sets. |
| user_disclaimers | 0 | 2 | 3 | -1 | Table | Records of user acceptance of disclaimers. |
| organization_users | 0 | 5 | 7 | -1 | Table | Organization membership and role information. |
| land_use_model_types | 2 | 0 | 2 | -1 | Table | (Enum) Types of ways a project’s land can be used. |
| rate_limited_events | 0 | 0 | 4 | -1 | Table | Tracks events such as notifications that should have a minimum interval between instances. |
| device_managers | 0 | 2 | 13 | -1 | Table | Information about device managers. This is a combination of information from the Balena API and locally-generated values. |
| ecosystem_types | 1 | 0 | 2 | -1 | Table | (Enum) Types of ecosystems in which plants can be found. Based on the World Wildlife Federation’s “Terrestrial Ecoregions of the World” report. |
| file_batch_statuses | 1 | 0 | 2 | -1 | Table | (Enum) Statuses of file batches. |
| species_native_categories | 1 | 0 | 2 | -1 | Table | (Enum) Categories related to native-ness of a species. Used for accelerator species management. |