Export structure

Introduction

While most users can rely entirely on obs_read() to handle data preparation, understanding the underlying structure of Observation.org exports is valuable for advanced debugging, writing custom SQL queries, or integrating with external database tools.

This vignette covers three layers:

Shared concepts — export formats, how obsR normalises them, and behaviour common to every export.
Opportunistic (classic) exports — the standard citizen-science SQLite, CSV, and Excel layout.
Structured project exports — the additional schema elements introduced by formal monitoring projects.

Field-level SQLite references are detailed in Appendix A (opportunistic) and Appendix B (project). For a comprehensive, step-by-step analysis guide to project data, see vignette("project-exports"). Language handling and spatial filtering are covered in vignette("labels") and vignette("spatial-filters"), respectively.

library(obsR)
library(DBI)
library(RSQLite)

db <- obs_example_db()

Shared concepts

Export formats

Observation.org provides three primary export formats. obsR normalises all of them into the same canonical column structure (obs_df).

Format	Supported in `obsR`	Language dependencies
SQLite	Yes (native)	Catalogue values (e.g. `species.name`) vary by export language.
CSV	Yes	Cell values vary by language. Column headers are always in English.
XLSX	Yes (requires `readxl`)	Same as CSV. Data is located in the `Data export` sheet.

When you specify locale = "es" (for example), obs_read() translates coded categorical fields using built-in dictionaries. It does not rename the standardised English column headers, nor does it translate species common names (which remain exactly as exported, e.g. House Sparrow or Gorrión común).

Two kinds of SQLite export

Observation.org SQLite files come in two distinct flavours:

Kind	Typical use	Recognition in `obsR`
Opportunistic (classic)	Casual observations; one fact table plus catalogues	Contains an `observation` table with classic fields such as validation and uuid.
Structured project	Formal monitoring protocols with survey effort	Contains project hierarchy tables (`project`, `protocol`, `visit`, `sample`, etc.).

obs_read() detects the export type automatically and returns a tidy obs_df (one row per observation) in both cases. It refuses a SQLite file that is neither classic, project, nor (in a future version) a single-table canonical cache.

The sections below treat the two kinds separately, so shared behaviour is not mixed with project-only concepts.

What `obs_read()` adds

Manually joining catalogue tables is tedious. obs_read() performs these JOIN operations automatically and returns a flat tibble with paired columns: a human-readable label and a stable *_code where relevant.

obs <- obs_read(db)
obs[1:3, c(
  "scientific_name", "common_name", "species_code",
  "validation", "validation_code",
  "sex", "sex_code"
)]
#> <obs_df> with 3 observations from obsR_example_47521ecbaf73.sqlite [en]
#> Species: 1
#> Validation: unknown (2), accepted (automatic validation) (1)
#> # A tibble: 3 × 3
#>   scientific_name   validation                      sex        
#>   <chr>             <chr>                           <chr>      
#> 1 Passer domesticus unknown                         Unspecified
#> 2 Passer domesticus accepted (automatic validation) Unspecified
#> 3 Passer domesticus unknown                         Unspecified

Privacy note: In the bundled opportunistic example, the user table contains one-way anonymised IDs and labels (e.g. Observer a1b2c3d4). When working with your own exports, always use obs_anonymise() before publishing or sharing data.

Handling large datasets (lazy evaluation)

For large SQLite exports, loading millions of rows into R memory at once is inefficient. By default, obs_read() applies a collect = 100000 threshold.

Fewer than 100,000 matching rows → a materialised tibble.
More than 100,000 matching rows → a lazy obs_df (dplyr database backend) until you call obs_collect() or a function that requires the full dataset.

You can override this behaviour with collect = TRUE, collect = FALSE, or a custom threshold (e.g. collect = 50000).

pending <- obs_read(db, collect = FALSE)
final_data <- obs_collect(pending)

CSV and Excel exports are always collected fully into memory, as there is no lazy SQL backend for these formats.

Opportunistic (classic) exports

This is the layout most users encounter first: opportunistic observations without a formal visit or sample protocol. The bundled Seville sparrows SQLite file is a typical example.

Relational SQLite layout

The core observation table acts as the fact table. Integer foreign keys (e.g. species, location, user) point to separate catalogue tables.

con <- dbConnect(SQLite(), db, flags = SQLITE_RO)
dbListTables(con)
#>  [1] "activity"            "country"             "country_division"   
#>  [4] "life_stage"          "location"            "metadata"           
#>  [7] "observation"         "observation_details" "observation_method" 
#> [10] "observation_source"  "species"             "species_group"      
#> [13] "species_type"        "substrate"           "user"               
#> [16] "validation_status"
dbGetQuery(con, "SELECT COUNT(*) AS n FROM observation")
#>      n
#> 1 1455
dbDisconnect(con)

con <- dbConnect(SQLite(), db, flags = SQLITE_RO)
dbGetQuery(con, "PRAGMA table_info(observation)")[, c("name", "type")]
#>                    name    type
#> 1                    id INTEGER
#> 2                  date    TEXT
#> 3                  time    TEXT
#> 4               species INTEGER
#> 5                number INTEGER
#> 6                   sex    TEXT
#> 7                 notes    TEXT
#> 8            life_stage INTEGER
#> 9              activity INTEGER
#> 10               method INTEGER
#> 11            is_escape INTEGER
#> 12           is_certain INTEGER
#> 13             location INTEGER
#> 14         embargo_date    TEXT
#> 15    validation_status    TEXT
#> 16   external_reference    TEXT
#> 17                 uuid    TEXT
#> 18            substrate INTEGER
#> 19                point    TEXT
#> 20                  lat    REAL
#> 21                  lng    REAL
#> 22              local_x    REAL
#> 23              local_y    REAL
#> 24               source INTEGER
#> 25             modified    TEXT
#> 26     country_division INTEGER
#> 27              country INTEGER
#> 28         validated_by INTEGER
#> 29 last_validation_date    TEXT
#> 30                 user INTEGER
dbGetQuery(con, "SELECT id, scientific_name, name, `group` FROM species LIMIT 3")
#>       id                   scientific_name                              name
#> 1    122                 Passer domesticus                     House Sparrow
#> 2   1579             Passer hispaniolensis                   Spanish Sparrow
#> 3 260927 Passer domesticus balearoibericus House Sparrow ssp balearoibericus
#>   group
#> 1     1
#> 2     1
#> 3     1
dbGetQuery(con, "SELECT * FROM validation_status")
#>   id                            name
#> 1  O                         unknown
#> 2  J        accepted (with evidence)
#> 3  P            accepted (plausible)
#> 4  A accepted (automatic validation)
#> 5  I                         pending
#> 6  N                        rejected
#> 7  U       cannot be validated (yet)
dbDisconnect(con)

Conceptually, the schema resembles the following:

observation (fact table)
    ├─ species ──────────────> species (catalogue)
    ├─ user ─────────────────> user (catalogue)
    ├─ location ─────────────> location ─> country / country_division
    ├─ validation_status ────> validation_status (codes: A, J, P, O, N, U, I)
    ├─ life_stage, activity, observation_method, substrate (coded catalogues)
    └─ observation_details ──> mixed-count breakdowns (SQLite only)

Column-level detail for these tables is provided in Appendix A.

Validation and certainty

The validation column standardises statuses. Filtering by validation = "validated" retains codes A (accepted by automatic validation), J (accepted with evidence), and P (accepted as plausible).
Other codes include O (not evaluated), N (rejected), U (uncertain), and I (under study).
The is_certain column is a separate flag set by the observer. It is not a synonym for formal validation.

These fields are characteristic of opportunistic exports. In project obs_df objects, they are typically NA.

Mixed counts

Observation.org allows a total count (e.g. “20 individuals”) alongside an explicit subgroup breakdown (e.g. “10 Male; 7 Female; 3 Juvenile”).

In opportunistic SQLite exports, this granular data lives in the observation_details table. obs_read() flags these records with is_multiple = TRUE and stores the text breakdown in the details column.
CSV and Excel exports do not carry this granular breakdown; for these formats, is_multiple is always FALSE and details is NA.
Use obs_expand() to duplicate only the mixed-count rows into individual records for analysis.

CSV and Excel opportunistic files

Flat CSV and XLSX opportunistic exports already resemble the joined view that obs_read() builds from SQLite. Headers are in English, while cell labels follow the export language. The same obs_df columns are produced after reading, albeit without an underlying relational schema to explore.

Structured project exports

Project SQLite files reuse concepts from opportunistic exports—an observation fact table, species and trait catalogues, and GeoJSON points—but wrap them in a formal monitoring hierarchy:

Project → Protocol → Visit → Sample → Observation

A single file represents one project, which may contain several protocols. Site geometry is richer (location.geom), and custom protocol fields use an entity–attribute–value (EAV) pattern. Full table definitions are detailed in Appendix B.

What `obs_read()` returns for projects

Schema detection is automatic. The result is still one row per observation, enriched with extra columns: project, protocol, visit_id, and sample_id. Classic-only fields (e.g. uuid, validation) will be NA.

Empty visits (visits with no observations) do not appear as rows in the output of obs_read(); use the hierarchy helpers described below to access them. You can filter by protocol during import using the protocol argument (accepting either an ID or a name).

# path <- "my-project-export.sqlite"
# p <- obs_read(path, protocol = "Transect A")

For worked, end-to-end examples, see vignette("project-exports").

Hierarchy and catalogues

The following helper functions accept either a file path or a project obs_df:

obs_project_info() — returns project metadata and summary counts.
obs_locations() — returns site details, including GeoJSON geometries and counts of visits, samples, and observations.
obs_users() — returns observer details; observer_code is always present, but the name may be NA.
obs_visits(nest = TRUE/FALSE) and obs_samples(nest = TRUE/FALSE) — return pivoted attributes. Crucially, these retain the original export names (no automatic translation). Setting nest = TRUE preserves empty samples or observations within the hierarchy.

Filter semantics: Filtering by species or group drops visits or samples that lack that taxon. Conversely, filtering by location, user, protocol, or dates preserves empty visits and samples within the filtered scope, which is essential for accurate effort calculation.

Mapping project sites

While obs_map() continues to plot individual observation points, obs_map_visits() is designed for project data. It plots the location.geom (site boundaries or points) so you can visualise survey coverage rather than individual detections.

Effort and detection metrics

Structured exports enable robust, effort-corrected analyses:

obs_frequency() — calculates naive occupancy (n_occupied / n_visits); empty visits correctly count in the denominator.
obs_abundance() — calculates the sum of number and the mean_per_visit.

Both functions accept a by argument (defaulting to species; also supports location, period, or combinations). Filters on location, user, protocol, and dates trim the overall effort. Filters on species or group restrict which taxa appear in the output, but do not alter the effort denominator.

The counted column: Current project exports store visit.counted with values such as NULL, n, s, i, and h; the visit_counted catalogue provides their descriptions. obsR evaluates the code’s suitability for the requested metric. Frequency analysis is suited to s or i, while abundance analysis is suited to i or h. If a value is unsuitable for the chosen analysis, obsR issues a warning. Crucially, these visits are not silently dropped from the denominator, so effort calculations remain transparent. Older exports without the counted column run without counted-based warnings.

Session and transect metrics

For protocols that record a GPS track while walking a transect, the path is stored on session.geom (usually a GeoJSON LineString), linked from sample.session. obs_session_metrics() returns one row per session with geodesic length, start/end coordinates, the earliest observation on the primary sample, and the distance from the transect start to that point. When a session links several samples or visits, the result retains all their identifiers and reports n_samples / n_visits; associated visit.counted codes are also retained. It requires the Suggested package sf. Worked examples and export patterns are in vignette("project-exports").

Appendix A: Classic opportunistic SQLite schema

This section outlines the database structure for standard, opportunistic observation exports from Observation.org. It consists of a central fact table linked to various categorical and geographic catalogues.

Typical table layout:

observation (fact table)
    ├─ species ──────────────> species (catalogue) ──> species_group
    ├─ user ─────────────────> user (catalogue)
    ├─ location ─────────────> location ─> country / country_division
    ├─ validation_status ────> validation_status (codes: A, J, P, O, N, U, I)
    ├─ life_stage, activity, observation_method, substrate (coded catalogues)
    ├─ observation_source
    └─ observation_details ──> mixed-count breakdowns
metadata

A.1 Core fact table: `observation`

The primary table containing one row per observation record.

Column Name	Data Type	Description / Meaning
`id`	INTEGER	Unique identifier for the observation.
`date`	TEXT	Date of the observation (YYYY-MM-DD).
`time`	TEXT	Time of the observation (HH:MM), if recorded.
`species`	INTEGER	Foreign key linking to the `species` catalog table.
`number`	INTEGER	Total count of individuals observed in this record.
`sex`	TEXT	Short code for the sex of the observed individuals (e.g., `M`, `F`, `U`).
`notes`	TEXT	Free-text comments provided by the observer.
`life_stage`	INTEGER	Foreign key linking to the `life_stage` catalog table.
`activity`	INTEGER	Foreign key linking to the `activity` catalog table (observed behaviour).
`method`	INTEGER	Foreign key linking to the `observation_method` catalog table.
`is_escape`	INTEGER	Boolean flag (1/0). Indicates if the individual is a captive or escaped specimen. A non-wild presence.
`is_certain`	INTEGER	Boolean flag (1/0). The observer’s own certainty about the identification.
`location`	INTEGER	Foreign key linking to the specific site name in the `location` table.
`embargo_date`	TEXT	Date until which the observation is hidden (used for sensitive species). Defaults to `1980-01-01` when no embargo is active.
`validation_status`	TEXT	Foreign key linking to `validation_status` (e.g., `A`, `J`, `P`, `O`, `N`, `U`, `I`).
`external_reference`	TEXT	Optional external ID (e.g., bird ringing number, museum specimen ID).
`uuid`	TEXT	Universal Unique Identifier, useful for external linking or deduplication.
`point`	TEXT	Optional GeoJSON geometry string for the location (typically a `Point` feature).
`lat`	REAL	Latitude in WGS84 (EPSG:4326).
`lng`	REAL	Longitude in WGS84 (EPSG:4326).
`local_x`, `local_y`	REAL	Projected coordinates. Used only for specific regions (e.g., EPSG:31370 for Belgium, EPSG:28992 for the Netherlands). Often empty.
`source`	INTEGER	Foreign key linking to `observation_source` (e.g., mobile app, web form, specific project).
`modified`	TEXT	Timestamp of the last modification to this record (YYYY-MM-DD HH:MM:SS.fraction).
`country_division`	INTEGER	Foreign key linking to the `country_division` table (e.g., province or state).
`country`	INTEGER	Foreign key linking to the `country` table.
`validated_by`	INTEGER	Foreign key linking to the `user` who performed the validation (if applicable).
`last_validation_date`	TEXT	Date of the most recent validation action.
`user`	INTEGER	Foreign key linking to the `user` (observer) who recorded the data.

A.2 Mixed counts breakdown: `observation_details`

Populated only when an observation contains a total count that is explicitly broken down by subgroups (e.g., “10 Male; 7 Female”). In obsR, records linked to this table are flagged with is_multiple = TRUE.

Column Name	Data Type	Description / Meaning
`id`	INTEGER	Unique identifier for the detail record.
`observation`	INTEGER	Foreign key linking to the parent `observation.id`.
`number`	INTEGER	Count of individuals in this specific subgroup.
`sex`	TEXT	Sex code for this subgroup.
`life_stage`	INTEGER	Foreign key linking to the `life_stage` of this subgroup.
`activity`	INTEGER	Foreign key linking to the `activity` of this subgroup.

A.3 Primary catalog tables

These tables provide the descriptive metadata linked by foreign keys in the observation table.

species

Column	Type	Description
`id`	INTEGER	Unique species identifier.
`scientific_name`	TEXT	Accepted Latin/binomial name of the taxon.
`name`	TEXT	Common name of the species in the language of the export.
`group`	INTEGER	Foreign key to `species_group` (e.g., 1 = Birds).
`type`	TEXT	Foreign key to `species_type` (e.g., `S` = species, `I` = subspecies).

user

Column	Type	Description
`id`	INTEGER	Unique user identifier.
`name`	TEXT	Observer’s display name. (Note: Use `obs_anonymise()` before sharing to keep this information private).

location

Column	Type	Description
`id`	INTEGER	Unique location identifier.
`name`	TEXT	Name of the specific site or place name (e.g., “Coto Doñana”).

country & country_division

Column	Type	Description
`id`	INTEGER	Unique identifier.
`name`	TEXT	Country or administrative division name (e.g., “Sevilla”).
`iso_alpha2`	TEXT	(Country only) Two-letter ISO country code (e.g., “ES”).

A.4 Categorical lookup tables

These tables map integer IDs or short codes to their human-readable names in the export language. They all follow a similar structure: id (primary key), name (translated label), and optionally group (linking to species_group to ensure, for example, that specific activities are only suggested for birds).

validation_status: Maps codes to their descriptive status: A (accepted automatic), J (accepted with evidence), P (accepted plausible), O (unknown), I (pending), N (rejected), U (cannot be validated yet).
species_type: S (species), I (subspecies), F (forma), V (variety), Y (synonym), H (hybrid), M (aggregate species).
species_group: High-level taxonomic groups (e.g., 1: Birds, 2: Mammals, 4: Butterflies, 10: Plants, 11: Fungi, 30: Disturbances).
life_stage: Maps IDs to stages (e.g., 1: unknown, 2: adult, 3: adult breeding, 5: juvenile, 1009: nestling).
activity: Maps IDs to observed behaviours (e.g., 1: present, 2: foraging, 4: courtship/singing, 3114: occupied nest with eggs, 3119: nest building).
observation_method: Maps IDs to detection methods (e.g., 48: seen, 49: heard, 51: seen and heard, 588: sound trapped).
substrate: Maps IDs to microhabitats. (Note: Often recorded as “not applicable” for birds, but actively used for taxa like fungi or lichens).
observation_source: Maps IDs to the data entry method or project. Ranges from generic sources (e.g., 42: API, 92: ObsMapp, 409: ObsIdentify, 421: iNaturalist) to specific monitoring projects (e.g., 27: Klapekstertelling, 107: SOVON autoclustering).

A.5 System metadata: `metadata`

A simple key-value store containing export-level information.

Column	Type	Description
`key`	TEXT	Metadata property name (e.g., `source`, `export_date`, `coordinate reference systems`).
`value`	TEXT	The corresponding value. Note: The CRS key explicitly states that `point`, `lat`, and `lng` use WGS84 (EPSG:4326), while `local_x` and `local_y` use regional projections where applicable.

Appendix B: Structured project SQLite schema

This section details the schema for structured project exports, which are designed for formal monitoring protocols. A single SQLite file represents one project, which may contain multiple protocols. The hierarchy flows from Project → Protocol → Visit → Sample → Observation. Spatial data is stored as GeoJSON strings (WGS84).

obs_read(), obs_visits(), and obs_frequency() are designed to parse and navigate this schema. For practical workflows, see vignette("project-exports").

Typical layout:

project
 ├─ protocol
 │   ├─ subject (target taxa/attributes for the protocol)
 │   ├─ protocol_visit_attribute ──┐
 │   ├─ protocol_sample_attribute ─┼──> attribute / choice (EAV)
 │   └─ protocol_observation_attribute ─┘
 ├─ visit
 │   ├─ visit_attr ────────────────> attribute / choice
 │   ├─ sample
 │   │   ├─ sample_attr ───────────> attribute / choice
 │   │   └─ observation
 │   │       └─ observation_attr ──> attribute / choice
 │   └─ session (optional grouping of samples, via sample.session)
 └─ project_user ──────────────────> project_user_role
location (geom) · user · species / species_group · metadata

Absence of counted: A counted column (e.g. visit.counted) is not present in current project export DDLs. Effort calculations in obsR use the explicit presence of visits and samples in the hierarchy. If a future export adds counted, obs_frequency() / obs_abundance() will honour it with warnings as described earlier in this vignette—do not assume the column exists in SQL against live files today.

B.1 Core project and protocol tables

project

Column	Type	Description
`id`	INTEGER	Unique project identifier.
`name`	TEXT	Name of the project.
`description`	TEXT	Project description or objectives.
`start_date`	TEXT	Project start date (YYYY-MM-DD).
`end_date`	TEXT	Project end date (YYYY-MM-DD).

protocol

Column	Type	Description
`id`	INTEGER	Unique protocol identifier.
`name`	TEXT	Name of the monitoring protocol (usable with `obs_read(..., protocol = ...)`).
`project`	TEXT	Foreign key linking to `project.id` (stored as text in current exports).

subject — target of the protocol (taxa / defaults).

Column	Type	Description
`id`	INTEGER	Unique subject identifier.
`name`	TEXT	Name of the subject.
`description`	TEXT	Description of the subject.
`protocol`	INTEGER	Foreign key linking to `protocol`.
`species_group`	INTEGER	Foreign key linking to `species_group`.
`sex`	TEXT	Coded sex focus, if any.
`activity`	INTEGER	Foreign key linking to `activity`.
`method`	INTEGER	Foreign key linking to `observation_method`.
`life_stage`	INTEGER	Foreign key linking to `life_stage`.

B.2 Survey effort hierarchy

visit — Empty visits (no observations) still appear here and matter for frequency denominators.

Column	Type	Description
`id`	INTEGER	Unique visit identifier (`visit_id` after `obs_read()`).
`start_date`	TEXT	Date the visit began.
`start_time`	TEXT	Time the visit began, if recorded.
`end_date`	TEXT	Date the visit concluded, if recorded.
`end_time`	TEXT	Time the visit concluded, if recorded.
`notes`	TEXT	Free-text notes for the visit.
`location`	INTEGER	Foreign key linking to `location`.
`geom`	TEXT	GeoJSON geometry representing the visit area or point.
`user`	INTEGER	Foreign key linking to `user` (observer).
`project`	INTEGER	Foreign key linking to `project`.
`protocol`	INTEGER	Foreign key linking to `protocol`.

sample

Column	Type	Description
`id`	INTEGER	Unique sample identifier (`sample_id` after `obs_read()`).
`visit`	INTEGER	Foreign key linking to the parent `visit`.
`start_date`	TEXT	Date the sample was recorded.
`geom`	TEXT	GeoJSON geometry for the specific sample location.
`location`	INTEGER	Foreign key linking to `location`.
`user`	INTEGER	Foreign key linking to `user`.
`session`	INTEGER	Foreign key linking to `session` (if applicable).

session — optional grouping of samples.

Column	Type	Description
`id`	INTEGER	Unique session identifier.
`geom`	TEXT	GeoJSON geometry for the session (typically a walked transect; used by `obs_session_metrics()`).
`notes`	TEXT	Free-text notes for the session.
`type`	INTEGER	Coded type of the session.
`user`	INTEGER	Foreign key linking to `user`.

B.3 Observation records (project form)

observation differs from the classic export: it links to visit and sample, and lacks fields such as uuid, validation_status, is_escape, is_certain, and country columns, which are not part of the structured project fact table.

Column	Type	Description
`id`	INTEGER	Unique observation identifier.
`date`	TEXT	Date of the observation.
`time`	TEXT	Time of the observation, if recorded.
`species`	INTEGER	Foreign key linking to `species`.
`number`	INTEGER	Total count of individuals observed.
`sex`	TEXT	Sex code.
`activity`	INTEGER	Foreign key linking to `activity`.
`life_stage`	INTEGER	Foreign key linking to `life_stage`.
`method`	INTEGER	Foreign key linking to `observation_method`.
`location`	INTEGER	Foreign key linking to `location`.
`point`	TEXT	Optional GeoJSON point geometry.
`lat`	REAL	Latitude (WGS84).
`lng`	REAL	Longitude (WGS84).
`user`	INTEGER	Foreign key linking to `user`.
`visit`	INTEGER	Foreign key linking to the parent `visit`.
`sample`	INTEGER	Foreign key linking to the parent `sample`.
`notes`	TEXT	Free-text comments for the observation.

B.4 Spatial and user catalogues

location

Column	Type	Description
`id`	INTEGER	Unique location identifier.
`name`	TEXT	Name of the site or location.
`description`	TEXT	Description of the location.
`geom`	TEXT	GeoJSON geometry defining the site boundary or point (used by `obs_map_visits()`).
`owner`	INTEGER	Foreign key linking to the user who owns/manages the site.
`modified`	TEXT	Timestamp of the last modification.
`country__iso_alpha2`	TEXT	Two-letter ISO country code.

user

Column	Type	Description
`id`	INTEGER	Unique user identifier.
`name`	TEXT	Observer’s display name. May be missing or sparse in some project exports; use `obs_anonymise()` before sharing when names are present.
`date_joined`	TEXT	Date the user joined the platform/project.

B.5 Entity–attribute–value (EAV) custom fields

Structured projects often use custom, protocol-specific attributes. These are stored in an EAV structure, preserving the original names from the export.

visit_attr / sample_attr / observation_attr — same shape; object points at the parent visit, sample, or observation id.

Column	Type	Description
`id`	INTEGER	Unique attribute record identifier.
`object`	INTEGER	Foreign key linking to the parent visit, sample, or observation ID.
`attribute`	INTEGER	Foreign key linking to the `attribute` catalogue.
`value`	TEXT	The recorded value (often a code or free text).
`choice`	INTEGER	Foreign key linking to `choice` (if the attribute is a predefined list).

attribute

Column	Type	Description
`id`	INTEGER	Unique attribute identifier.
`name`	TEXT	Name of the custom attribute.

choice

Column	Type	Description
`id`	INTEGER	Unique choice identifier.
`attribute`	INTEGER	Foreign key linking to `attribute`.
`value`	TEXT	The coded value of the choice.
`name`	TEXT	Human-readable name of the choice.
`description`	TEXT	Description of the choice.

protocol_visit_attribute, protocol_sample_attribute, protocol_observation_attribute define which custom fields apply to a protocol:

Column	Type	Description
`id`	INTEGER	Unique link identifier.
`protocol`	INTEGER	Foreign key linking to `protocol`.
`attribute`	INTEGER	Foreign key linking to `attribute`.

B.6 Project administration and metadata

project_user

Column	Type	Description
`id`	INTEGER	Unique record identifier.
`user`	INTEGER	Foreign key linking to `user`.
`role`	INTEGER	Foreign key linking to `project_user_role`.
`country__iso_alpha2`	TEXT	Two-letter ISO country code for the user’s project context.

project_user_role

Column	Type	Description
`id`	INTEGER	Unique role identifier.
`name`	TEXT	Name of the role (e.g. Admin, Observer).

species, species_group, and related catalogues (activity, life_stage, observation_method) mirror the classic export structure. Project files may also include species_list / species_list_item. metadata remains a key–value store (key, value) for export-level properties.

Tip: List tables with DBI::dbListTables(con) and inspect samples with DBI::dbGetQuery(con, "SELECT * FROM table_name LIMIT 5"). For classic files use Appendix A; for project files use Appendix B and vignette("project-exports").