Loading data into the telemetry database • spdgt.telem

library(spdgt.telem)

This is the reference for getting records in: the order they have to be created in, what each endpoint accepts, how to check a batch before sending it, and what comes back.

Getting your data into a tibble is your business — a capture spreadsheet, a vendor export, a database query. What this package guarantees is the other half: that whatever you assembled is well formed before it reaches the API.

Order of operations

The server assigns ids, and later records reference earlier ones by id, so the order is a hard constraint rather than a convention:

  individuals ──┬──────────────┐
                └─> captures ──┤
                               ├──> deployments ──> sensor deployments
  blueprints ──> devices ──────┘

  events and positions name a device; the server links them to a deployment

blueprints describe a make and model. A device can be created without one, but only a blueprint carries the manufacturer, so in practice this is step zero for any collar whose model is not already in the catalogue.
devices and individuals depend on nothing else, so either can go next.
captures need an individual_id.
deployments need a device_id, and want an individual_id, a capture_id and a start_at.
sensor deployments need a sensor_id and an individual_id, and usually a device_id and deployment_id too.
events and positions need a device_id.

A sensor is a device. A drop-off unit, a mortality beacon or an ear tag is created through telem_create_devices() with its own serial number, exactly like a collar — sensor_id is a device id. telem_create_sensor_deployments() then records what it was fitted to. Nothing about a sensor is a different kind of record, which is why sensors have their own serial numbers in the source data rather than sharing the collar’s.

A drop-off date is not part of that. was_dropped_off_at lives on the deployment, because it says when the deployment ended — a different fact from the unit that ended it.

Two things the order alone does not tell you.

A deployment written too early is silently incomplete. A deployment needs only device_id. If you create deployments before the individuals they belong to, the API accepts them and records them as Incomplete — they cannot anchor a position, and the status is not returned, so nothing shows it later. telem_create_deployments() stops and asks rather than writing them, and on_incomplete = "allow" is there for the legitimate case of a collar not yet on an animal.

allow cannot write everything, though. A deployment with neither an individual_id nor a latitude/longitude pair is refused by the API outright. The rule is enforced server-side and is not in the published field list, so neither telem_fields() nor discovery can show it to you — which is why this package checks it before sending rather than letting the rejection arrive partway through a batch. Give such a row either an animal or a place; recording where the collar went without knowing which animal wore it is the usual answer.

Device uniqueness is scoped, so a re-run can duplicate. A serial is unique per blueprint, not per project. Two consequences follow. The same serial sent with a different blueprint_id is a new device rather than a conflict, so a script that reads the devices back and matches on serial alone will not notice it made a second one. And two projects cannot hold the same serial under one blueprint — a reason to give a project its own blueprint rather than share a general one.

Ids the source data will not have

An individual requires a species_id, and a deployment can carry a management_unit_id or an analysis_unit_id. None of those are in a spreadsheet of collar records, and none can be invented — the API checks each one exists.

They come from spdgt.core, which caches every project lookup:

library(spdgt.core)

build_cache()
lkup_species_id("Mule Deer")
#> [1] 1

Two things to know before relying on it:

Species ids are per project. lkup_species_id() resolves against the project you are authenticated to, so the same name gives a different id in a different project. Resolve at run time; never hardcode the number.

The cache is built from the counts realm, because that is where lookups live for every project. So a session loading telemetry data needs counts authentication as well — which happens on its own the first time you call a lookup interactively, but not in a headless session holding only a telemetry token.

Analysis units carry a further trap: they are versioned, and the same name recurs across versions. Resolving one by name alone picks an arbitrary version, so pin the version when it matters.

What each endpoint accepts

telem_fields() is the list of writable fields, one row per field, taken from the API’s own validation rules rather than from a table maintained here:

telem_fields("devices")
#> # A tibble: 9 × 9
#>   resource field         presence na_ok type      enum   max_len min_val max_val
#>   <chr>    <chr>         <chr>    <lgl> <chr>     <chr>    <int>   <dbl>   <dbl>
#> 1 devices  serial_number required FALSE character NA          50      NA      NA
#> 2 devices  state         present  TRUE  character telem…      25      NA      NA
#> 3 devices  blueprint_id  optional TRUE  integer   NA          NA      NA      NA
#> 4 devices  price         optional TRUE  numeric   NA          NA       0      NA
#> 5 devices  purchased_at  optional TRUE  date      NA          NA      NA      NA
#> 6 devices  retired_at    optional TRUE  date      NA          NA      NA      NA
#> 7 devices  frequency     optional TRUE  numeric   NA          NA       0      NA
#> 8 devices  notes         optional TRUE  character NA          NA      NA      NA
#> 9 devices  project_id    optional TRUE  integer   NA          NA      NA      NA

Pass no argument for every resource at once. presence has three values, because the API distinguishes them:

`presence`	The column	Missing values
`required`	must be there	not allowed
`present`	must be there	allowed
`optional`	may be absent	allowed

present is the one that catches people out, and state on devices is the only field that has it: a devices tibble with no state column is rejected, while state = NA is accepted.

type says what the column has to hold. Whole-numbered doubles pass for integer fields, so species_id = 5 is fine; datetimes may be POSIXct or an ISO 8601 string, and a POSIXct column is rendered as "%Y-%m-%dT%H:%M:%SZ" on the way out. Nothing else is converted for you.

A few columns mean less than their names suggest:

individuals — species_id is a server id, not a name; nickname is free text, and the natural key for looking the animal up again.
blueprints — model, version and manufacturer are all required, and together with type and project_id they form the unique key. version is free text used loosely in practice, holding a year on one record and a product name on another.
devices — serial_number is the collar serial, and the natural key here. frequency behaves unlike the other fields: it cannot be filtered or sorted on, and it is stored as a number, so trailing zeros do not survive the round trip — a collar programmed to 170.000 reads back as 170. If you need the three-decimal form for people to read, keep it somewhere textual as well.
deployments — device_id and individual_id are server ids, not the serial number and the nickname. There is no writable status: the API computes it from individual_id and start_at.
captures — carry latitude and longitude, which is easy to miss.
events — deployment_id is not accepted, however much it looks like it should be. See telem_create_events() for what happens if you send it.
positions — the coordinates travel inside payload, not as columns of their own. See below.

Enumerated fields have a fixed vocabulary, and the functions that hold it are listed in telem_fields()’s enum column:

telem_device_states()
#>  [1] "Unknown"         "Deployed"        "On"              "Off"            
#>  [5] "Paused"          "Halted"          "Faulty"          "Offline"        
#>  [9] "Low-Battery"     "Depleted"        "Dropped"         "In-Hand"        
#> [13] "In-Repair"       "At-Warehouse"    "At-Manufacturer" "Decommissioned" 
#> [17] "Retired"
telem_capture_methods()
#> [1] "Dart"       "Net-Gun"    "Free-Range" "Culvert"    "Snare"     
#> [6] "Leg-Hold"
telem_event_types()
#>  [1] "Unknown"      "Position"     "Health"       "Alert"        "Mortality"   
#>  [6] "Conditions"   "Reproduction" "Drop-Off"     "Low-Battery"  "Trap"        
#> [11] "Geo-Fence"    "Observation"  "Depredation"  "Activity"     "Proximity"
telem_blueprint_types()
#> [1] "Unknown" "Tag"     "Collar"  "Sensor"  "Camera"

Two of them warn rather than abort: manufacturer on blueprints, and source on events. The API validates both as free strings, so refusing an unlisted value would invent a restriction it does not have — and would block a collar from a new maker until this package next released.

The warning is still worth heeding. Reading by manufacturer matches the vocabulary exactly, so a blueprint recorded as "Vectronics" rather than "Vectronic" is accepted on write and then never comes back from a manufacturer filter — along with every device hanging off it:

telem_device_manufacturers()
#> [1] "Lotek"              "Telonics"           "Vectronic"         
#> [4] "ATS"                "Track Tag"          "Wildlife Computers"

So a tibble ready to send looks like this:

individuals <- tibble::tribble(
  ~species_id, ~sex,     ~fate,     ~nickname,
  5L,          "Female", "Unknown", "EF01",
  5L,          "Male",   "Unknown", "EM01"
)

devices <- tibble::tribble(
  ~serial_number, ~state,
  "103633",       "Deployed",
  "103635",       "In-Hand"
)

Checking data before you send it

Every telem_create_*() validates against telem_fields() first, so a bad column fails here rather than as a 422 partway through a batch. To check ahead of time:

telem_validate(devices, "devices")

It needs no credentials and sends nothing. It reports every problem it can find at once, rather than stopping at the first, because fixing a hand-assembled batch one message per run is the slow way to do it:

telem_validate(
  tibble::tibble(
    serial_number = c("103633", NA),
    purchased_at  = c("2026-01-14", "2026-01-14")
  ),
  "devices"
)
#> Error in `telem_validate()`:
#> ! `data` is not ready to send to "devices".
#> ✖ Required column state is missing.
#> ✖ serial_number is missing in 1 of 2 rows (2).

A column the endpoint does not accept warns rather than aborting — the value is simply dropped on the way out, which is how a typo in an optional field disappears without trace.

To see the request that would go, without sending it:

telem_create_individuals(individuals, dry_run = TRUE, verbosity = 2)

spdgt.auth::api_discover("telemetry", "/individuals") describes an endpoint from the API’s own OpenAPI annotation, and it remains the way to find out what filters a read accepts. For writable fields prefer telem_fields(): the annotations have drifted from the validation rules that actually run, so discovery over-reports some fields as required and omits others the API takes.

What a create returns, and how to use it

Not a table of new records. telem_create_*() returns one row per record you submitted:

Column	Meaning
`success`	did this record succeed
`message`	the API’s message for it
`data`	list-column holding the created record, including its id

So the ids are not where you would reach for them. build_lookup() unpacks that — flattening to the successful rows and returning a named vector from your key to the new id:

created <- telem_create_devices(devices)
dev <- build_lookup(created, "serial_number")

dev
#> 103633 103635
#>     20     22

created_ind <- telem_create_individuals(individuals)
ind <- build_lookup(created_ind, "nickname")

That vector is what turns a serial into the device_id a deployment needs:

telem_create_deployments(tibble::tibble(
  device_id     = unname(dev[c("103633", "103635")]),
  individual_id = unname(ind[c("EF01", "EM01")]),
  start_at      = "2026-02-04T00:00:00Z"
))

Partial failure is normal

Batch endpoints commit per record. There is no rollback, so a batch can be half-applied — expect it rather than treating it as an error:

failed <- created[!created$success, ]
failed$message

# Resend only the rows that failed. Resending the batch duplicates the ones
# that worked.
telem_create_devices(devices[!created$success, ])

build_lookup() drops failed rows for you, and errors if every record failed — there is nothing to map at that point, and a later call would fail more obscurely.

Making the whole load re-runnable

Resending only the failed rows works when you still hold the response. It does not help when the session died, or when you are running the script a second time next week against a database someone has since edited.

telem_create_missing() is for that. It reads what the API already holds, matches on a natural key you nominate, creates only what is absent, and returns every input row with its id — whether that id was just assigned or was already there:

collars <- telem_create_missing(
  collars, "devices", by = "serial_number"
)

# Populated for every row, so the next step can join on it.
collars$id

Run it twice and the second run creates nothing. That makes the fix for a half-finished load “run it again” rather than working out by hand which rows landed.

Three things it deliberately does:

It never updates an existing record. A key match means “already here, leave it alone”, so a re-run cannot overwrite a correction someone made in the UI. Use telem_update_*() when a change is what you want.
It reads back rather than trusting the response, because /multiple commits per record and promises no ordering. A row that cannot be read back afterwards aborts the call instead of returning an NA id that something downstream would carry silently.
It refuses a row whose key is missing. Such a row could be created but never found again, so a second run would create it twice — which is the one thing the function exists to prevent.

Pick by from what the source and the API agree on: serial_number for devices, model/version/manufacturer together for blueprints. For individuals it is usually nickname, but nothing enforces its uniqueness server-side, so there it is a convention between you and the source data rather than a guarantee.

Positions are events

There is no positions endpoint. telem_create_positions() writes to /events/multiple with type = "Position", status = "New", and the coordinates in payload; a server-side ProcessGPS job then extracts them into position records. Reads come back from telem_read_positions(), which queries /events/positions.

This is the one batching function — batch_size defaults to 1000 — and a failed batch is recorded and skipped rather than aborting the run, so check the result for status == "failed":

res <- telem_create_positions(positions)
res[res$status == "failed", ]

Uploading positions you hold locally

Most positions arrive without you: the server collects them from the manufacturer’s API on a schedule, and stores the whole vendor record as the event’s payload. telem_create_positions() is for the exception — fixes you hold locally and need to upload.

telem_add_payload() builds that payload column, keeping every column the collar recorded rather than trimming to a tidy four — the whole record is what the server stores for the positions it collects itself, so it is what we send too. Name the two columns holding the coordinates and they are renamed inside the payload, so the server finds them whatever your source called them; altitude and DOP need no naming, because it already resolves those from a wide list of aliases.

fixes <- tibble::tibble(
  Latitude  = c(51.19446, 51.19447),
  Longitude = c(-123.41, -123.42),
  Temp      = c(-3, -2)
)

with_payload <- telem_add_payload(
  fixes,
  latitude = "Latitude",
  longitude = "Longitude"
)

with_payload$payload[[1]]
#> $latitude
#> [1] 51.19446
#> 
#> $longitude
#> [1] -123.41
#> 
#> $Temp
#> [1] -3

The source columns are kept, so the rest of the pipeline can still reach them:

fixes |>
  telem_add_payload(latitude = "Latitude", longitude = "Longitude") |>
  dplyr::mutate(
    device_id = unname(dev[serial]),
    date      = format(AcqTime, "%Y-%m-%dT%H:%M:%SZ"),
    source    = "CSV"
  ) |>
  telem_create_positions()

Positions are the one resource that does not warn about columns it does not recognise, for that reason: after telem_add_payload() the frame deliberately carries columns that are also inside payload, and they are dropped on the way out rather than complained about.

What no general function can absorb

Real capture spreadsheets carry problems specific to the project that produced them: dates held as Excel serials, collar ids with redeployment suffixes, end dates written as free text, a bare year where a date belongs. That work goes in a per-project script — get the data into the shapes above, then use the functions here.

inst/scripts/quinault_elk_load.R in this package is a worked example of one, readable start to finish.