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. |
| 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. |
| managed_location_types | 1 | 0 | 2 | -1 | Table | (Enum) Type of managed location for business analytics purposes. |
| species | 18 | 7 | 26 | -1 | Table | Per-organization information about species. |
| seed_fund_reports | 2 | 6 | 14 | -1 | Table | Partner-submitted reports about their organizations and projects. |
| 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. |
| project_report_settings | 0 | 1 | 2 | -1 | Table | Which projects require reports to be submitted each quarter. Organization-level settings are in |
| 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. |
| organizations | 16 | 6 | 14 | -1 | Table | Top-level information about organizations. |
| chat_memory_conversations | 1 | 1 | 4 | -1 | Table | Information about Ask Terraware conversations. |
| 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. |
| seed_treatments | 3 | 0 | 2 | -1 | Table | (Enum) Techniques that can be used to treat seeds before testing them for viability. |
| species_successional_groups | 0 | 2 | 2 | -1 | Table | The successional groupings that the species is planted in. |
| countries | 7 | 1 | 5 | 249 | Table | Country information per ISO-3166. |
| facility_connection_states | 1 | 0 | 2 | -1 | Table | (Enum) Progress of the configuration of a device manager for a facility. |
| app_versions | 0 | 0 | 4 | -1 | Table | Minimum and recommended versions for Terraware mobile apps. |
| growth_forms | 1 | 0 | 2 | -1 | Table | (Enum) What physical form a particular species takes. For example, “Tree” or “Shrub.” |
| 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. |
| files | 9 | 2 | 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. |
| roles | 1 | 0 | 2 | -1 | Table | (Enum) Roles a user is allowed to have in an organization. |
| 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. |
| country_subdivisions | 1 | 1 | 3 | 57 | Table | Country subdivision (state, province, region, etc.) information per ISO-3166-2. |
| user_global_roles | 0 | 2 | 2 | -1 | Table | Which users have which global roles. |
| upload_problems | 0 | 2 | 8 | -1 | Table | Details about problems (validation failures, etc.) in user-uploaded files. |
| 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. |
| plant_material_sourcing_methods | 1 | 0 | 2 | -1 | Table | (Enum) Sourcing methods for acquiring plant material. |
| 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 | 114 | 3 | 16 | -1 | Table | User identities. A user can be associated with organizations via |
| organization_internal_tags | 0 | 3 | 4 | -1 | Table | Which internal (non-user-facing) tags apply to which organizations. |
| organization_managed_location_types | 0 | 2 | 2 | -1 | Table | Per-organization information about managed location types for business analytics purposes. |
| 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. |
| 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. |
| 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. |
| 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. |
| conservation_categories | 1 | 0 | 2 | -1 | Table | (Enum) IUCN conservation category codes. |
| regions | 2 | 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 |
| organization_users | 0 | 5 | 7 | -1 | Table | Organization membership and role information. |
| thumbnails | 0 | 1 | 8 | -1 | Table | Information about scaled-down versions of photos. |
| 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. |
| land_use_model_types | 1 | 0 | 2 | -1 | Table | (Enum) Types of ways a project’s land can be used. |
| projects | 29 | 5 | 10 | -1 | Table | Distinguishes among an organization’s projects. |
| rate_limited_events | 0 | 0 | 4 | -1 | Table | Tracks events such as notifications that should have a minimum interval between instances. |
| facilities | 10 | 6 | 22 | -1 | Table | Physical locations at a site. For example, each seed bank and each nursery is a facility. |
| 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. |
| 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 | 10 | -1 | Table | Properties of a series of values collected from a device. Each device metric is represented as a timeseries. |
| species_native_categories | 1 | 0 | 2 | -1 | Table | (Enum) Categories related to native-ness of a species. |