How to read this document. Chunks are set eval = FALSE — this is a walkthrough, not a runnable copy. The pipeline itself lives in tape_pipeline.R; the section numbers here match the section numbers there. Everything below is intent and rationale. Where I say “this used to do X”, that is a bug we already hit on real data, not a hypothetical.

1 What this is, and what it is not

This is a validation and flagging layer that sits between the Kobo export and whatever consumes TAPE data downstream — the Postgres load, the country dashboards, the descriptive statistics.

The single most important design decision is this:

The pipeline never deletes, corrects, or silently drops a submission. It attaches evidence and lets a human decide.

Every problem it finds becomes a column on the record and a row in a register. Nothing is imputed. Nothing is dropped. If a value cannot be converted, it is left as it was and an error is raised — the old behaviour of quietly turning an unconvertible area into NA while relabelling the unit column "ha" is exactly the failure mode this replaces.

The reason is auditability. TAPE microdata gets published and reused. When a country office asks in eighteen months why a holding has 0.9 ha instead of 9,000 m², we need the answer to be in the data, not in someone’s memory.

1.1 What it produces

Object	What it is	Who consumes it
`res$clean_df`	Every original column, unmodified, plus ~65 `qc_*` columns	Postgres load, analysis
`res$flag_register`	One row per (record × check) that fired	Field team follow-up
`res$check_summary`	Every check in the catalogue with a fire count	Round-over-round monitoring
`res$enum_summary`	Per-enumerator error rates and median duration	Field supervision
`res$repeat_orphans`	Repeat rows whose parent is missing	Export integrity

1.2 The flow

   Kobo API (.xlsx export setting)
            │
            ▼
   read_kobo_export()          main sheet + 11 repeat sheets
            │                   ├── harmonise_names()   _uuid → uuid
            │                   └── resolve_aliases()   version renames
            ▼
   build_var_def()             types from the questionnaire, not from the data
            │
            ▼
   harmonise_types()  →  apply_sentinels()  →  check_ranges()
            │
            ▼
   attach_repeat_counts()      resolve nested repeats to the submission
            │
            ▼
   ┌─────────────────────────────────────────────────┐
   │  ~62 checks, each returning a tidy flag tibble  │
   │  submission · timing · GPS · area · system ·    │
   │  household · workforce · repeats · CAET ·       │
   │  indicators · response behaviour                │
   └─────────────────────────────────────────────────┘
            │
            ▼
   attach_flag_columns()       one qc_* column per catalogue entry
            │
            ▼
   clean_df  +  flag_register  +  summaries   →   PostgreSQL

2 Ingest — and the one thing not to change

2.1 Why `.xlsx` and not `.csv`

The Kobo synchronous export endpoint offers both. Only the xlsx carries the repeat groups. The CSV is a single flat table; anim_details, crop, varieties and the rest simply do not exist in the response. There is nothing to join.

kobo_base <- "https://kobo.fao.org/api/v2/assets/<asset>/export-settings/<export>"

fetch_kobo <- function(base = kobo_base, token = Sys.getenv("KOBO_TOKEN")) {
  if (!nzchar(token)) stop("KOBO_TOKEN not set — add it to .Renviron and restart R")

  tmp <- tempfile(fileext = ".xlsx")
  res <- GET(paste0(base, "/data.xlsx"),
             add_headers(Authorization = paste("Token", token)),
             write_disk(tmp, overwrite = TRUE))

  if (status_code(res) != 200)
    stop(sprintf("Kobo returned HTTP %d: %s", status_code(res),
                 substr(content(res, as = "text", encoding = "UTF-8"), 1, 300)))
  tmp
}

Three details worth flagging to whoever maintains this:

write_disk() is not optional. xlsx is binary. content(res, as = "text") corrupts it.
Token comes from .Renviron, never the script. It is a credential with read access to submissions.
On the test form, the CSV export was also losing main-sheet values — two submissions came through with no _uuid, no __version__, and a dropped fies_score. The xlsx had all of them. So this is not only about repeats.

2.2 Naming: `harmonise_names()`, and why `clean_names()` is banned

Kobo metadata columns are prefixed with underscores (_uuid, _index, __version__). We rename them once, explicitly, through a lookup table.

KOBO_RENAME <- c(
  uuid            = "_uuid",
  index           = "_index",          # links parent to repeat groups
  submission_time = "_submission_time",
  form_version    = "__version__",
  gps_lat         = "_gps_loc_latitude",
  gps_lon         = "_gps_loc_longitude",
  gps_precision   = "_gps_loc_precision"
  # ... see the script for the full map
)

janitor::clean_names() must not run before this. It strips the leading underscores that KOBO_RENAME matches on and mangles __version__, so the rename becomes a silent no-op and form_version, gps_lat, gps_lon and submission_time are never created. Every check that depends on them then skips — quietly, because has_cols() only warns.

Convention for the team: add to KOBO_RENAME rather than renaming ad hoc anywhere else in the pipeline.

2.3 `resolve_aliases()` — the form is a moving target

Mid-round, five variables were renamed in the XLSForm:

ALIASES <- c(
  agecon_focus   = "agricultural_economy_focus",
  div_score      = "diversity_score",
  cultfood_score = "culture_food_score",
  cocrea_score   = "cocreation_score",
  respgov_score  = "responsible_governance_score"
)

Left side is the name the pipeline uses; right side is what newer form versions call it. resolve_aliases() coalesces old ← new.

This is the single highest-impact line in the file. Before it existed, nine of eighteen submissions were flagged CAET_INCOMPLETE and ten were flagged SYSTEM_MISSING — every one a false positive. Those records had all ten CAET elements; they were just under new names. Since qc_caet_incomplete is the filter on the analysis subset, the pipeline would have silently discarded half the round for a naming reason.

The confirmation that the mapping is right: after aliasing, the recomputed CAET mean matches the form’s reported caet_score on every record that has one.

Action for the dev team: when the form changes, add the pair here. The coverage_by_version sheet in the QC report exists to make the next rename visible — it shows, per variable, what share of submissions have it populated in each form_version. A column that goes from 100% to 0% at a version boundary is a rename.

3 Repeat groups — the part most likely to be got wrong

3.1 The linkage model

Every repeat sheet carries two keys:

_index — its own row key within that sheet
_parent_index — the _index of its parent row

For a top-level repeat the parent is the main sheet. For a nested repeat the parent is another repeat sheet.

On the TAPE form, varieties sits inside crops. Its _parent_index values run 1–21 and point at crops$_index, not at a submission. The main sheet only has 18 rows. Matching varieties directly against the main sheet produces counts that look plausible and mean nothing, plus three phantom orphans.

repeat_spec <- list(
  list(name = "anim_details", sheet = "anim_details", parent = "main",
       required_when = quote(!is.na(raise_animals) & raise_animals == 1)),
  list(name = "crop",         sheet = "crop",         parent = "main",
       required_when = quote(!is.na(agecon_focus) & agecon_focus %in% c(1, 3))),
  list(name = "crops",        sheet = "crops",        parent = "main",  required_when = NULL),
  # ... cfp, ap, youthinfo_m, youthinfo_f, acrev_more, m, animals_001
  list(name = "varieties",    sheet = "varieties",    parent = "crops", required_when = NULL)
)

Two rules the spec now encodes:

parent is either "main" or the name of another entry in this list.
Order matters — a nested group must appear after its parent, because the resolver walks the list once and needs the parent’s map already built.

You can verify the parent of any sheet from the export itself: each repeat sheet has a _parent_table_name column.

3.2 Resolving the chain

attach_repeat_counts <- function(df, repeats) {
  root_maps <- list()
  main_idx  <- suppressWarnings(as.integer(df[[cfg$index_col]]))

  for (sp in repeat_spec) {
    r   <- repeats[[sp$name]]
    own <- suppressWarnings(as.integer(r[["_index"]]))
    par <- suppressWarnings(as.integer(r[["_parent_index"]]))

    if (identical(sp$parent, "main")) {
      root  <- par                              # already a submission index
      valid <- main_idx
    } else {
      pm    <- root_maps[[sp$parent]]           # parent's own → root map
      root  <- pm$root[match(par, pm$own)]      # walk one level up
      valid <- pm$own
    }

    root_maps[[sp$name]] <- list(own = own, root = root)
    # ... orphan detection, then count rows per submission into n_<name>
  }
  df
}

root is always a submission _index, however deep the nesting. Counts land in n_anim_details, n_crop, n_varieties and so on, which the content checks then use as evidence.

Important caveat for anyone caching: _index is generated at export time and is not a stable submission identifier. It is fine for linking parent to child within one download — which is all we use it for — but never persist it or join it across rounds. uuid is the stable key.

3.3 What the counts are for

n_anim_details is how we know a holding that declared livestock actually has animals recorded. Previously the evidence came from num_animal, which takes values like 0.5 and 1.5 — it is a score, not a headcount, and using it made the livestock consistency checks meaningless.

animal_evidence <- (raise_animals == 1) | (n_anim_details > 0)

4 The check contract

Every check is a function that takes a data frame and returns a tidy tibble with a fixed shape. That uniformity is what lets us add checks without touching anything downstream.

make_flags <- function(df, check_id, condition, message) {
  sev <- CHECK_CATALOGUE$severity[match(check_id, CHECK_CATALOGUE$check_id)]
  if (is.na(sev)) stop("check_id not in CHECK_CATALOGUE: ", check_id)

  cond <- eval_tidy(enquo(condition), df)
  cond <- !is.na(cond) & cond          # NA never flags
  if (!any(cond)) return(empty_flags())

  tibble(record_id  = as.character(df[[cfg$id_col]][cond]),
         enumerator = as.character(df[[cfg$enum_col]][cond]),
         check_id   = check_id,
         severity   = sev,
         message    = msg)
}

Three rules baked into this function:

NA never flags. Missingness has its own dedicated checks (HH_ALL_MISSING, GPS_MISSING, TIME_MISSING). Conflating “not asked” with “answered wrongly” is how you get a QC report nobody trusts.
Severity is looked up, never passed in. A check cannot invent its own severity, and a typo in check_id is a hard error at the point of use.
Messages are specific. "Household size 62 beyond the plausible maximum", not "invalid household size". The register goes to field teams who need to act on it without opening R.

⚠️ Gotcha when writing a new check. eval_tidy() is plain tidy evaluation, not a data-masking verb. across() and if_any() will error here. If you need a row-wise “any of these columns”, build a logical vector first — see any_negative() in the helpers.

4.1 The catalogue is the schema

CHECK_CATALOGUE <- tribble(
  ~check_id,             ~severity,  ~description,
  "MISSING_RECORD_KEY",  "ERROR",    "Submission exported without a _uuid",
  "DUP_UUID",            "ERROR",    "Duplicate submission key",
  "FORM_LEFT_OPEN",      "WARNING",  "Interview spans an implausible elapsed time",
  "TEST_RECORD",         "REVIEW",   "Enumerator name matches a test pattern"
  # ... ~62 entries in total
)

attach_flag_columns() emits one qc_* column per catalogue entry, whether or not it fired. That is deliberate: the output schema is identical across rounds and countries, so the Postgres table definition and the dashboards do not break when a check happens to catch nothing in a given round.

To add a check: add a catalogue row, write a function returning make_flags(), register it in run_validation(). Nothing else changes — the column appears automatically.

4.2 Severity — what the three levels mean operationally

Severity	`qc_status`	Meaning	Downstream rule
`ERROR`	`blocked`	The record is internally inconsistent or unusable as it stands	Resolve with the field team before analysis
`WARNING`	`use_with_care`	Usable, but something is off and context matters	Include, but document
`REVIEW`	`review`	Worth a human glance; often legitimate	Include
—	`clean`	Nothing fired	Include

qc_status = case_when(
  qc_n_error   > 0 ~ "blocked",
  qc_n_warning > 0 ~ "use_with_care",
  qc_n_review  > 0 ~ "review",
  TRUE             ~ "clean")

Severity is a statement about what the consumer should do, not about how bad the data is. That distinction matters when someone proposes reclassifying a check.

5 Selected checks worth explaining in the meeting

These are the ones where the intent is not obvious from the code.

5.1 Timing: duration and upload lag are different metrics

end - start is how long the form was open. submission_time - end is how long it sat on the device before upload. They were previously combined into a ratio, which hid both.

The offsets matter and are easy to get wrong: start and end carry a local ISO-8601 offset (+02:00); _submission_time has no offset and is UTC. Parsing them the same way produced a systematic ~2 hour error.

start_t <- parse_kobo_time(start)                              # honours offset
sub_t   <- parse_kobo_time(submission_time, assume_utc = TRUE) # naive UTC

On the test form, nine records show elapsed times of hours to days, and the end of one submission equals the start of the next. That is not a long interview — the enumerator leaves the form open and only closes it when opening the next one. FORM_LEFT_OPEN (WARNING) separates that from DURATION_SHORT (ERROR), which is a real data-quality problem.

5.2 GPS: accuracy of exactly 0 m is impossible

A GNSS fix always reports some uncertainty. gps_precision == 0 means the point was placed by hand on a map. Eight of the located records report exactly 0.0. That is GPS_ZERO_PRECISION, a WARNING — the coordinates exist but do not mean what a consumer would assume.

5.3 Area: unknown units fail loudly

factor_v <- unname(cfg$area_units[unit_raw])              # sqm, acre, ha, ...
conv     <- as.numeric(df$area_umeas_conversion)          # form's own factor
factor_v <- if_else(is.na(factor_v) & conv > 0, conv, factor_v)

for (v in area_cols) {
  x <- as.numeric(out[[v]])
  out[[v]] <- if_else(is.na(factor_v), x, x * factor_v)   # untouched if unconvertible
}

When the unit is other_unit and the form supplied no conversion factor, the values are left as they are and AREA_UNIT_UNKNOWN (ERROR) is raised. The old code turned all fourteen area variables into NA while relabelling the unit column "ha" — losing the data and misrepresenting what was left.

AREA_FORM_CONV_MISMATCH cross-checks our conversion against the form’s own *_ha calculated fields. Two independent routes to the same number; if they disagree, one of them is wrong and we want to know which.

5.4 Household checks are gated on `hh_or_not`

Half the submissions are non-household holdings. An empty roster on those is correct, not an error. is_household gates every roster check, which is why HH_UNEXPECTED (a roster where there should not be one) is a distinct check from HH_ALL_MISSING.

5.5 CAET: the reported score and the elements must agree

caet_score is the arithmetic mean of the ten element scores. Where elements are missing the form still reports a value, and that value is not the mean of the surviving elements — so those records are not comparable with complete ones and must not be pooled.

analysis_df <- clean_df %>%
  filter(qc_status != "blocked", is.na(qc_caet_incomplete))

This filter is the one that moves the descriptive statistics most. Flag it in the meeting — it is the line most likely to be copied without being understood.

6 What it produced on the TEST form

18 submissions, 11 repeat sheets, 104 flags, every submission flagged at least once. 14 blocked, 4 use-with-care.

Check	Severity	Records
`REPEAT_MISSING`	ERROR	12
`DURATION_SHORT`	ERROR	3
`SYSTEM_LIVESTOCK_NO_ANIMALS`	ERROR	2
`AREA_UNIT_UNKNOWN`	ERROR	1
`OLD_FORM_VERSION`	REVIEW	13
`SENTINEL_CODES`	REVIEW	7
`TIME_OF_DAY`	REVIEW	5
`AREA_OUTLIER`	REVIEW	4
`TEST_RECORD`	REVIEW	4
`FORM_LEFT_OPEN`	WARNING	9
`GPS_MISSING`	WARNING	9
`GPS_ZERO_PRECISION`	WARNING	8
`SUBMIT_BEFORE_END`	WARNING	6
`SYSTEM_CROP_HAS_LIVESTOCK`	WARNING	5
`SYSTEM_LIVESTOCK_HAS_LAND`	WARNING	5

REPEAT_MISSING dominating is the headline: only submissions 3, 5, 7, 9 and 10 have any child rows at all, yet 16 of 18 report raise_animals = 1. Twelve holdings declare livestock or crops with no detail rows behind them.

Most of the rest reads as form-testing artefacts rather than field problems — which is expected on a TEST form, and is itself a useful check on the checks.

7 Integration notes for the pipeline

7.1 Where the Postgres load goes

run_validation() returns; it does not write. The load belongs after it.

res <- run_validation(path = fetch_kobo(), strata_col = "project_code")

con <- dbConnect(RPostgres::Postgres(), ...)

dbWriteTable(con, Id(schema = "tape", table = "submissions"),
             res$clean_df, overwrite = TRUE)
dbWriteTable(con, Id(schema = "tape", table = "qc_flags"),
             res$flag_register, append = TRUE)   # append: keeps round history

clean_df is overwrite, flag_register is append — the register is the audit trail and should accumulate across rounds.

7.2 Idempotency

The pipeline is pure with respect to the export: same file in, same flags out. There is no state and nothing is written mid-run. Re-running is always safe.

7.3 Cost

The heavy steps are check_form_warnings() and check_heaping(), both of which scan every column (~1,760 on this form). Fine at 18 rows; if a country round lands in the tens of thousands, these are the two to vectorise first.

7.4 Configuration to review per country

Everything tunable is in cfg at the top. Before a new round, check:

tz_field — affects hour-of-day checks only
dur_min_min, dur_max_hr — plausible interview length
hh_size_review, hh_size_max — household size expectations vary a lot
sentinels and sentinel_exempt — country_code is already exempt, since codes like 999 would otherwise be recoded to NA
bbox — currently NULL, so GPS_OUT_OF_AREA cannot fire

8 Open items — what I want from this meeting

TAPE_definition.xlsx. RANGE_VIOLATION cannot fire without it, and variable types are currently inferred from observed values (numeric if ≥90% of non-missing values parse). Supplying the questionnaire definition replaces a heuristic with the actual contract. Where does this file live, and can it be pulled from the asset rather than maintained by hand?
A bounding box per country. GPS_OUT_OF_AREA is dark until we have one.
Stratification. strata_col = "project_code" is populated on 7 of 18 records, so duration z-scores are computed against a mixed baseline. country_code may be the better key — needs a decision.
REPEAT_MISSING at 12 of 18. Is this a form-design issue (the repeat is reachable but easy to skip), an enumerator-training issue, or expected on a test form? This determines whether it stays an ERROR.
Ownership of the alias map. Form renames will keep happening. Ideally the pipeline detects them rather than waiting for someone to notice — the coverage_by_version report is a start, but it is currently read by a human.
Token handling. Currently Sys.getenv("KOBO_TOKEN") from .Renviron. Confirm this is how we want service credentials handled in the deployed environment.

9 Appendix — running it

source("tape_pipeline.R")     # sections 0–13: config, helpers, checks

res <- run_validation(
  path            = fetch_kobo(),
  definition_file = NULL,       # "TAPE_definition.xlsx" once available
  strata_col      = "project_code",
  bbox            = NULL,       # c(xmin, ymin, xmax, ymax)
  indicator_vars  = vars$caet_elements
)

View(res$check_summary)
View(res$enum_summary)

readr::write_csv(res$clean_df,      "tape_flagged.csv")
readr::write_csv(res$flag_register, "qc_flags_for_field_team.csv")

Section order in tape_pipeline.R is load-bearing — config, then helpers, then ingest, then checks, then orchestration, then the run at the bottom. An earlier draft had the run_validation() call near the top, above every function it needed, so it could not execute at all. Keep the run last.

TAPE Validation Pipeline — Design Walkthrough

Kobo → validate → flag → PostgreSQL

Dave Nkoro, FAO

28 July 2026

1 What this is, and what it is not

1.1 What it produces

1.2 The flow

2 Ingest — and the one thing not to change

2.1 Why `.xlsx` and not `.csv`

2.2 Naming: `harmonise_names()`, and why `clean_names()` is banned

2.3 `resolve_aliases()` — the form is a moving target

3 Repeat groups — the part most likely to be got wrong

3.1 The linkage model

3.2 Resolving the chain

3.3 What the counts are for

4 The check contract

4.1 The catalogue is the schema

4.2 Severity — what the three levels mean operationally

5 Selected checks worth explaining in the meeting

5.1 Timing: duration and upload lag are different metrics

5.2 GPS: accuracy of exactly 0 m is impossible

5.3 Area: unknown units fail loudly

5.4 Household checks are gated on `hh_or_not`

5.5 CAET: the reported score and the elements must agree

6 What it produced on the TEST form

7 Integration notes for the pipeline

7.1 Where the Postgres load goes

7.2 Idempotency

7.3 Cost

7.4 Configuration to review per country

8 Open items — what I want from this meeting

9 Appendix — running it

TAPE Validation Pipeline — Design Walkthrough

Kobo → validate → flag → PostgreSQL

Dave Nkoro, FAO

28 July 2026

1 What this is, and what it is not

1.1 What it produces

1.2 The flow

2 Ingest — and the one thing not to change

2.1 Why .xlsx and not .csv

2.2 Naming: harmonise_names(), and why clean_names() is banned

2.3 resolve_aliases() — the form is a moving target

3 Repeat groups — the part most likely to be got wrong

3.1 The linkage model

3.2 Resolving the chain

3.3 What the counts are for

4 The check contract

4.1 The catalogue is the schema

4.2 Severity — what the three levels mean operationally

5 Selected checks worth explaining in the meeting

5.1 Timing: duration and upload lag are different metrics

5.2 GPS: accuracy of exactly 0 m is impossible

5.3 Area: unknown units fail loudly

5.4 Household checks are gated on hh_or_not

5.5 CAET: the reported score and the elements must agree

6 What it produced on the TEST form

7 Integration notes for the pipeline

7.1 Where the Postgres load goes

7.2 Idempotency

7.3 Cost

7.4 Configuration to review per country

8 Open items — what I want from this meeting

9 Appendix — running it

2.1 Why `.xlsx` and not `.csv`

2.2 Naming: `harmonise_names()`, and why `clean_names()` is banned

2.3 `resolve_aliases()` — the form is a moving target

5.4 Household checks are gated on `hh_or_not`