ODK Sync Pipeline

ODK Sync And Attachments

Summary

ODK sync is an incremental batch process that:

  1. Resolves active site-level ODK mappings
  2. Materializes compatibility va_forms rows for those mappings
  3. For each form, runs an OData filter-gated delta check — skips if nothing changed since last sync
  4. Fetches changed submissions via OData JSON (paginated, no ZIP download)
  5. Thin-upserts va_submissions rows for all fetched ODK submissions, including consent=no and consent-missing rows
  6. Enriches the changed submissions for that same form into the canonical stored payload
  7. Reuses one pyODK client/session per (connection_id, odk_project_id) group across delta, fetch, targeted single-record refresh, enrichment, and attachment sync
  8. Per-form commit — earlier forms are not rolled back if a later form fails
  9. Routes consent-valid changed submissions into attachment_sync_pending
  10. Enriches changed submissions in bounded per-submission Celery batches so metadata work is checkpointed independently
  11. Syncs attachments for upserted submissions using bounded Celery batch subtasks and ETag-based conditional download
  12. Advances those submissions to smartva_pending only after all attachment batches for that form finish
  13. Rebuilds full CSV from DB for SmartVA compatibility and then runs SmartVA for that same form
  14. Records last_synced_at on the mapping row after each successful form
  15. Marks local sync issues when a local submission is missing from active ODK submissions

Current Identity Rule

Submission identity in the live sync path is based on the stable ODK submission KEY (__id from OData), not on instanceID.

Current implementation:

  • OData normalization maps __id to KEY
  • OData normalization computes sid as {KEY}-{form_id.lower()}
  • instanceID is stored as enriched metadata only
  • the displayed VA Form ID on coding/data-manager pages comes from the business payload identifier (va_uniqueid), not from va_submissions.va_sid

Practical consequence:

  • an ODK edit that changes instanceID but preserves KEY is still treated as the same DigitVA submission
  • instanceID churn is metadata and should not be used as the canonical local sync key

Current State: Not Codeable ODK Write-Back

Current behavior:

  • coder Not Codeable attempts to write ODK review state hasIssues with a coder-specific comment after the local workflow save succeeds
  • data-manager Not Codeable now follows the same pattern:
    • local not_codeable_by_data_manager save first
    • ODK hasIssues write-back second
    • role-specific comment text recorded in ODK
  • ODK write-back failure does not roll back the local Not Codeable outcome
  • success and failure are both auditable locally

Current State: Workflow State Guards

The current implementation now respects protected-state sync guards for finalized submissions.

Current behavior:

  • ODK updates on protected submissions do not automatically destroy active COD artifacts
  • the submission transitions to finalized_upstream_changed
  • SmartVA is not regenerated automatically for that protected state
  • the current runtime/API allows data managers or admins to accept or reject that protected-state transition
  • the current runtime/API now exposes a normalized changed-fields diff for the pending protected update through:
    • /api/v1/data-management/submissions/<va_sid>/upstream-change-details
    • inline data-manager submission view
    • dashboard View Changes modal
  • the sync path now stores a durable upstream-change record with:
    • prior workflow state
    • prior authoritative final-assessment id when present
    • previous VA payload snapshot
    • incoming VA payload snapshot
  • the sync path now creates pending notification rows for:
    • vaadmin
    • data_manager

Current lineage note:

  • runtime still treats va_submissions as the active submission row
  • additive payload-version schema now exists:
    • va_submission_payload_versions
    • va_submissions.active_payload_version_id
  • sync now writes active payload versions for newly created and payload-changed non-protected submissions
  • sync now writes pending upstream payload versions for protected finalized submissions when upstream ODK data changes
  • existing rows have been backfilled into initial active payload versions
  • current protected-state lineage is preserved through va_submission_upstream_changes, which stores:
    • previous payload-version id
    • incoming payload-version id
    • previous VA payload snapshot
    • incoming changed VA payload snapshot
    • previous authoritative final-assessment id when present
  • upstream accept now promotes the pending payload version to active and updates the active summary row
  • upstream keep-current-ICD now also promotes the pending payload version to active, preserves finalized ICD/COD artifacts, and restores the prior finalized workflow state
  • modal upstream review resolution is now local-only and does not post an ODK rejection comment
  • protected sync updates no longer overwrite the active summary row before the accept decision
  • normalized payload comparison now ignores volatile metadata and representation-only churn such as updatedAt, attachment counters, and numeric-vs-string-equivalent scalar values
  • changed-field review presentation now separates:
    • data changes
    • metadata changes
    • formatting-only changes
  • this gives current upstream-review lineage for protected updates, while the new payload-version schema is now the active lineage model for sync writes and protected upstream resolution
  • SmartVA rows now also bind to payload_version_id
  • coder and reviewer final-COD artifacts now also bind to payload_version_id
  • final COD authority resolution now ignores stale coder/reviewer final rows from superseded payload versions

Current workflow follow-through after sync:

  • screening-enabled projects may route submissions through screening_pending -> smartva_pending or screening_pending -> not_codeable_by_data_manager
  • admin override and recode now use explicit workflow transitions
  • the hourly coding-maintenance path now writes reviewer_eligible after the coder recode window expires when no active recode episode exists

Current naming:

  • current implemented state key: finalized_upstream_changed
  • legacy migrated key: revoked_va_data_changed
  • preferred UI label: Finalized - ODK Data Changed

See ODK Sync Policy for the policy baseline and remaining implementation work.

Current Workflow Execution Layer

ODK sync no longer writes canonical workflow states ad hoc.

Current runtime behavior:

  • sync calls named transitions in app/services/workflow/transitions.py
  • sync-originated transitions use the typed vasystem actor
  • transition execution locks the target workflow row before validation and write
  • route_synced_submission() now explicitly excludes protected source states, so finalized, reviewer-eligible, reviewer-finalized, and legacy-closed cases cannot be silently rerouted by a generic sync transition

Connection Model

  • ODK connection details are stored in mas_odk_connections (DB) with encrypted credentials
  • Each project is linked to a connection via map_project_odk
  • va_odk_clientsetup(project_id) resolves the connection from DB first, falls back to legacy odk_config.toml if no DB mapping exists
  • pyODK Client is built using an explicit Session (base_url, username, password); a shared stub config file (odk_stub_config.toml) satisfies the file-read requirement without storing credentials
  • Each connection uses its own cache file (odk_cache_<connection_id>.toml) so concurrent calls to different ODK servers do not share auth tokens
  • Each DB-managed connection also stores shared guard state:
    • cooldown_until
    • consecutive_failure_count
    • last_failure_at
    • last_failure_message
    • last_success_at
    • last_request_started_at
  • The legacy odk_config.toml fallback remains for projects not yet migrated to DB-managed connections

Sync Entry Point

Main service:

Behavior:

  • Loads active map_project_site_odk rows for active project-site assignments
  • Upserts compatibility va_forms rows for those mappings
  • Captures snapshot_time = datetime.now(UTC) before any ODK calls
  • Loops over forms, running the delta check → fetch → thin upsert → enrich → attachments → SmartVA pipeline per form

Important detail:

  • map_project_site_odk is the source of truth for what gets synced
  • va_forms still exists because submissions, media storage, permissions, and several workflow paths key off va_form_id
  • Sync materializes va_forms rows from the site mapping table rather than requiring admins to manage both separately
  • the sync loop now groups forms by (connection_id, odk_project_id) and reuses one pyODK client across every form in that group for delta checks, OData fetches, and attachment requests

ODK-only Death Cases (digitva-l8y3)

In a tree project, a new or amended ODK submission that the sync has routed and that has no death-register case (and no intake draft) gets an odk-source case: state submitted, owned by the "ODK sync" service account. Skipped when name, sex or date of death is missing. Policy and rules: Web Intake Policy; code in app/services/odk_case_service.py, called from _upsert_form_submissions after routing. An unrouted ODK VA still gets its case, with no unit, counted in the area's Unrouted row. The case follows its submission's unit when a data manager pins it or a later sync re-routes it (sync_case_unit, called from routing).

Canonical Payload Enrichment

Current sync writes remain OData-first, but selected operational metadata is now enriched before payload fingerprinting and persistence.

Current canonical stored payload fields include:

  • OData form-answer payload
  • FormVersion
  • DeviceID
  • SubmitterID
  • instanceID
  • ReviewState
  • instanceName
  • AttachmentsExpected
  • AttachmentsPresent

Current enrichment sources:

  • submission XML: FormVersion, DeviceID
  • submission metadata endpoint: SubmitterID, instanceID, and fallback review metadata
  • attachments endpoint: AttachmentsExpected, AttachmentsPresent

NaN-like values are normalized to missing before persistence so downstream consumers such as SmartVA do not receive stored NaN payload values.

National IDs reduced at ingest (PII slice 1a)

Every incoming ODK record is passed through _reduce_incoming_national_ids (va_data_sync_01_odkcentral.py) before it is compared, fingerprinted, copied into an upstream-change row or stored: at the top of _upsert_form_submissions (every caller passes enrich_payloads=False) and inside _enrich_submission_payload_for_storage (the finalize pass and the enrichment backfill). Id10073 keeps only its last 3 digits as XXXXXXXXX123; a 12-digit (Aadhaar) or 16-digit (VID) run in any other string answer is masked the same way, except Id10070, which is only flagged (app/utils/national_id.py). Review flags are logged as field ids only; there is no stored flag column yet (slice 1b). The payload version service reduces again before every write, as a backstop.

Change detection is unaffected: normalize_payload_for_fingerprint reduces national IDs on both sides, so a stored version holding the full number (from before slice 1a) or the reduced form compares equal to the full incoming ODK record. A rerun of unchanged ODK data creates no version, no upstream-change row and releases no coding (tests/services/test_national_id_sync_rerun.py). Only a change in the kept digits, or in any other answer, is a change. An "unchanged" pass that writes the version in place (the finalize pass) stores the reduced form in the same version. Status, commands and dev counts: docs/kb/pii-slice-1a.md.

Payload PII split (PII slice 1b part II-A, behind a flag)

PAYLOAD_PII_SPLIT_ENABLED (default false; see runtime-and-operations.md). Off, sync behaves exactly as above, except that a version which already has a side row in map_payload_version_pii is refused (PayloadSplitStateError): that form's sync fails, is rolled back and logged, and the other forms continue (a mis-set process never rewrites a split version by the old rule). On (only after flask pii split-payloads --apply, in the release window of docs/kb/pii-slice-1b.md):

  • Every "same payload?" decision of the sync goes through submission_payload_version_service.payload_matches: the incoming record is split on the form type's sealed set and its version_fingerprint compared with the stored stripped payload plus the side row's pii_digest (no decrypt). That is _upsert_form_submissions (the release decision for an unprotected case and the protected-case branch) and _finalize_enriched_submissions_for_form.
  • Every write goes through write_payload_version: payload_data without the sealed set, the sealed fields in map_payload_version_pii, new and "unchanged" branches alike, so a no-op rerun never writes plaintext back.
  • Upstream-change rows store stripped copies (payload_copy); the data manager's diff reports a sealed-field change as one row, sealed_personal_data, decided by comparing the two versions' digests (pii_changed in the details reply), never naming a field or a value.
  • Accepted limitation (OD1): a changed national ID with the same last 3 digits is not a change.

Delta Check (Phase 1)

Before fetching any submission data, the sync runs a lightweight OData count:

GET /v1/projects/{id}/forms/{formId}/submissions.svc/Submissions
  ?$filter=(__system/submissionDate gt T) or (__system/updatedAt gt T)
  &$top=0&$count=true

Implemented in va_odk_delta_count().

Rules:

Condition Action
mapping.last_synced_at is NULL First-ever sync — always download
Delta count = 0 Run gap check (compare ODK IDs vs local)
Delta count > 0 Proceed to full OData JSON fetch
ODK error / timeout Run gap check as safe fallback

snapshot_time is captured before any ODK calls. last_synced_at is set to snapshot_time (not wall clock) after a successful form sync. This ensures submissions arriving mid-run are caught on the next sync rather than missed.

Gap Sync (Phase 1b)

When the delta count is 0 or the delta check fails, the sync runs a gap check instead of skipping the form entirely. This catches submissions that were missed by earlier failed syncs.

Steps:

  1. Fetch all ODK submission instance IDs via GET /v1/projects/{pid}/forms/{fid}/submissions (single REST call, metadata only)
  2. Load all local va_sid values for the form
  3. Compare local rows to active ODK IDs and flag local submissions missing from ODK as a sync issue
  4. Compute missing IDs: ODK IDs that don't have a corresponding local {id}-{form_id.lower()}
  5. If no missing IDs → skip form (truly in sync from a fetch perspective)
  6. Fetch missing submissions in batches of 50 via OData single-entity access Submissions('{instanceId}')
  7. Upsert and commit each batch independently for crash resilience

Implemented in:

Note: OData $filter on __id is not supported by ODK Central (HTTP 501). The single-entity access pattern Submissions('{instanceId}') is used instead.

Local sync-issue tracking

DigitVA now stores local sync-health markers on va_submissions:

  • va_sync_issue_code
  • va_sync_issue_detail
  • va_sync_issue_updated_at

Current code written by the runtime path:

  • missing_in_odk — the submission exists locally but was absent from the current active ODK submission ID list for that form

The flag is cleared automatically if the submission reappears in a later sync.

Submission Fetch (Phase 2)

Changed submissions are fetched via OData JSON (paginated, 250 per page):

GET /v1/projects/{id}/forms/{formId}/submissions.svc/Submissions
  ?$top=250&$skip=N
  &$filter=(__system/submissionDate gt T) or (__system/updatedAt gt T)

For first-ever syncs (last_synced_at is NULL), no filter is applied — all submissions are fetched.

Implemented in va_odk_fetch_submissions().

The main sync does not hold a form's records in memory. It loops over va_odk_fetch_submissions_page() (one page, each fetch wrapped in the connectivity backoff so a retry re-fetches that page) and upserts each page with _upsert_form_submissions before fetching the next. The form is still one transaction: one commit after the last page, and a failure on any page rolls the whole form back. Only when no enrichment dispatcher is passed are the pages also kept, for the in-process comment and enrichment steps. The single-form force-resync (run_single_form_sync) pages the same way, without the backoff, and hands the repair-map build only the fetched {sid: KEY} map; backfill and gap sync fetch by instance id in batches of 50. va_odk_fetch_submissions() (the page function in a loop that returns the full list) has no sync caller left. _upsert_form_submissions works 250 records at a time, loading the chunk's va_submissions rows and active payload versions in two queries, and reads the form type and its active category config once per call, not per record (never cached across calls: admins edit those tables).

OData JSON normalization

OData returns nested group objects. The fetch utility flattens them to leaf field names (equivalent to groupPaths=false) and maps __system.* fields to CSV column equivalents:

OData field Normalized name
__id KEY
__system/submissionDate SubmissionDate
__system/updatedAt updatedAt
__system/submitterName SubmitterName
__system/reviewState ReviewState

Nested group objects are recursively flattened to leaf names. Because WHO VA 2022 field names are globally unique, name collisions from flattening are not a concern in practice.

SmartVA input lineage

ODK sync no longer rebuilds a flat CSV for SmartVA. Instead:

  • sync writes normalized submission content into va_submissions
  • each submission also carries an active VaSubmissionPayloadVersion
  • SmartVA prep reads from VaSubmissionPayloadVersion.payload_data for the pending va_sid set
  • the sealed set (PII_FIELDS plus the form type's admin-flagged is_pii fields, pii_field_registry.sealed_field_ids) is dropped from the SmartVA input: SmartVA scores none of it

This means incremental syncs do not need a form-wide CSV rebuild step. SmartVA input is derived directly from the active payload-version records for the submissions being processed.

Attachment Sync (Phase 2)

Attachments are downloaded per-submission using ETag-based conditional HTTP:

# 1. Fetch attachment list (no binary)
GET /v1/projects/{id}/forms/{formId}/submissions/{instanceId}/attachments
→ [{name: "audio.amr", exists: true}, ...]

# 2. Conditional download
GET /v1/projects/{id}/forms/{formId}/submissions/{instanceId}/attachments/{filename}
If-None-Match: <stored_etag>
→ 304 Not Modified  (skip — nothing transferred)
→ 200 + binary      (download — write file, update ETag record)

Sync owns the network half only. It lists the attachments, issues the conditional GET, writes the body to the temp path attachment_service.new_ingest_temp_path() hands it, calls attachment_service.ingest_download(), and applies the readiness state that comes back with its PK-safe upsert. Everything else — AMR→MP3 conversion, the store write (local file or S3 object), the opaque storage_name, presence, and the removal of superseded blobs — belongs to app/services/attachment_service.py. va_odk_07_syncattachments.py imports no os, subprocess, tempfile or pathlib, knows no media directory, and never calls the store; a test enforces this (tests/test_sync_attachments_boundary.py).

Current implementation detail:

  • attachment downloads are streamed to disk in chunks rather than buffered via response.content
  • ODK attachment requests use explicit connect/read timeouts from ODK_CONNECT_TIMEOUT_SECONDS and ODK_READ_TIMEOUT_SECONDS
  • the stored MIME is decided from the leading bytes when they match an accepted signature (web_intake_attachment_service.detect_type), and otherwise from Central's Content-Type after safe_mime_type: an empty, malformed, or literal "null" label is stored as NULL. A label a browser could run or render (HTML, SVG, XML, any script or +xml type) is stored as application/octet-stream. Nothing is rejected on type: ODK sends files such as audit.csv outside the intake allow-list
  • size caps are shared with web intake: 50 MB per file (owner decision 2026-10-14, superseding 2026-10-06), 5 MB for .amr (attachment_service.MAX_BYTES and AMR_MAX_BYTES). A file over its cap is not stored. The check runs first on Content-Length, then as the body streams, which stops at the cap. The row keeps exists_on_odk, its metadata and Central's ETag, and records refused_reason = too_large and the size seen in refused_size_bytes. A 304 for a refused row is skipped without the forced re-download, so only a changed ETag fetches it again; a 200 that fits clears the refusal. Refusals are counted per submission (refused), per run (va_sync_runs.attachment_refused) and on the Attachment Management panel. Policy: docs/policy/odk-attachment-intake-limits.md
  • .amr narration is converted to MP3 with SoX inside the attachment service; a failed conversion raises AmrConversionError, leaves no partial .mp3 and no temp file, counts as an attachment error, and leaves the existing row untouched so ordinary repair retries it
  • the 304 branch asks attachment_service.attachment_present() whether the store already holds the blob; sync never stats a file or reads store_state itself
  • attachment serving (/api/v1/attachments/<storage_name>; the old /vaform/attachment/<storage_name> is kept for rendered pages) enforces the submission-level authorization matrix and Cache-Control: private, no-store defined in the Attachment Storage and Delivery Policy

Implemented in:

Rules:

  • attachment sync runs for submissions in upserted_map during full and single-form sync
  • single-submission refresh also syncs attachments for that submission
  • Submissions that already exist with unchanged updatedat receive zero attachment API calls
  • Forms with delta = 0 receive zero attachment API calls
  • The media directory is never cleared (rmtree eliminated) — files for unchanged submissions remain on disk
  • operational cleanup scripts may quarantine orphaned files under media/.orphaned/ for inspection and rollback instead of deleting them
  • .orphaned subtrees are excluded from active attachment integrity scans so quarantined files do not keep appearing as live orphaned media

ETag cache

ETag records are stored in va_submission_attachments:

Column Purpose
va_sid FK → va_submissions
filename Original filename from ODK
local_path Path on disk (may differ — .amr stored as .mp3)
mime_type Content-Type from download response
etag ETag header from last successful download
exists_on_odk Whether ODK reports the file as present
last_downloaded_at When the file was last downloaded

Primary key: (va_sid, filename).

Source and derivative state writes

The same rows also carry the readiness state added in Phase 2 of the Central attachment plan (migration b7e4c2a91d38; column reference in the data model). The values are decided by attachment_service.ingest_download() and attachment_service.mark_removed_on_odk(); sync applies them in _apply_submission_attachment_result:

Outcome Columns written
Successful download source_state='available', source_verified_at=<download time>, source_error_code=NULL, source_mime_type=<validated original MIME>
Successful download of an .amr additionally derivative_state='ready', derivative_mime_type='audio/mpeg', derivative_source_validator=<source ETag>, derivative_verified_at=<download time>, derivative_error_code=NULL
ODK no longer lists the file (exists=false) source_state='missing' (the local blob and its derivative record are not rewritten)
AmrConversionError derivative_state='error', derivative_error_code='conversion_failed' on the existing row; every other column, including storage_name and local_path, is left as it was

These columns are now what completeness decides on: attachment_service.attachment_state_by_submission() reports, per submission, whether it is retired, how many attachment blobs are ready (present in the store, and for audio with a ready derivative), and which are not — see the repair workflow and the attachment storage policy. mime_type keeps its existing meaning and is not repurposed — the original's validated type is source_mime_type.

Audio conversion

.amr files are converted to .mp3 using SoX immediately after download. The converter probes the source bitrate via soxi and targets 2x the source bitrate (capped 16–64 kbps) — AMR-NB speech at ~12 kbps produces 24 kbps MP3 output, optimal quality for the source without bloated file size. The .amr file is deleted after successful conversion. A failed conversion raises AmrConversionError: no partial .mp3 is kept, the temporary source is removed by the caller, the existing row keeps its previous blob, and the failure is recorded as derivative_state='error' / derivative_error_code='conversion_failed'.

API call volume

For each submission in upserted_map:

  • 1 attachment-list API call (always)
  • N download calls (one per new or changed file; 0 if all ETags match → 304)

On first sync of a large form: O(submissions) attachment-list calls are unavoidable. On subsequent syncs with no changes: 0 calls (delta check skips the form entirely).

Auth/session reuse

The sync path now reuses one pyODK client per (connection_id, odk_project_id) group for:

  • delta check
  • submission fetch pagination
  • attachment list calls
  • conditional attachment downloads

This reduces repeated auth/session verification calls from roughly O(submissions) to roughly O(connection/project groups) during the attachment-heavy parts of a sync run.

Bounded parallelism

Attachment sync now processes changed submissions with a small bounded worker pool per form:

  • default worker cap is 3
  • network I/O and file writes happen in worker threads
  • ORM record creation and updates are applied sequentially in the main thread

This keeps the sync run fully synchronous and only marks the form successful after all attachment work completes, while reducing attachment wall-clock time without sharing the Flask SQLAlchemy session across threads.

Connectivity Retry Policy

ODK sync now applies bounded retry only at the form-fetch boundary.

Rules:

  • delta-check timeout still falls through immediately to the full-download path
  • the full OData fetch is retried only for retryable upstream failures such as:
    • connect timeout
    • connection error
    • request timeout
    • auth/session failures surfaced as token or 401/403-style errors
  • retry budget is fixed at 3 attempts total
  • backoff schedule is 5s, then 10s
  • each retry refreshes the cached pyODK client for that (connection_id, odk_project_id) group before retrying
  • after the final failed attempt, the form is marked failed for the run and the sync continues with later forms

This is intended to stop repeated hammering of ODK Central while still allowing short-lived auth/connectivity faults to recover cleanly.

Shared Connection Guard

DB-managed ODK connections now use a shared guard service across app and worker processes.

Rules:

  • every outbound ODK request for DB-managed connections reserves a per-connection request slot before the request is sent
  • the minimum interval between request starts is configurable through ODK_CONNECTION_MIN_REQUEST_INTERVAL_SECONDS
  • retryable connectivity/auth failures increment a shared consecutive-failure counter on mas_odk_connections
  • once the configured failure threshold is reached, the connection enters cooldown until cooldown_until
  • during cooldown, later callers fail fast without attempting a live upstream call
  • successful calls reset the shared failure count and clear cooldown

This guard is used by:

  • OData delta checks
  • OData submission fetch pages
  • attachment list and download requests
  • admin live ODK project/form lookups
  • admin connection tests
  • ODK review-state write-back

Operator-facing visibility now exists in:

  • the ODK Connections admin panel
  • the Project Forms connection bar for the selected project
  • the Sync Dashboard status card when any active ODK connection is degraded or in cooldown

Related policy:

Update Detection

During upsert:

  • Submission exists with same va_odk_updatedat → skipped (no DB write, no attachment call)
  • Submission exists with changed va_odk_updatedat + protected state → metadata updated, transitions to finalized_upstream_changed
  • Submission exists with changed va_odk_updatedat + non-protected state → updated; active workflow artifacts deactivated; consent re-evaluated to set new workflow state
  • Submission does not exist → inserted unconditionally; consent evaluated to set initial workflow state

All submissions from ODK are stored regardless of consent value. Consent determines workflow routing:

Consent value Workflow state
Present and not "no" (e.g. "yes", "telephonic_consent") smartva_pending in current runtime for newly synced or payload-changed submissions; desired target remains smartva_pending until SmartVA is generated, regenerated, or explicitly failed-and-recorded
"no" consent_refused
Empty / missing consent_refused

When a non-protected submission is updated, the app deactivates related local workflow artifacts:

  • Active coder allocations
  • Coder review records
  • Initial assessments
  • Final assessments
  • Reviewer reviews
  • User notes

ODK is treated as the source of truth for submission content. See ODK Sync Policy.

Language Normalization

During upsert, raw ODK language values (narr_language or language field) are normalized to canonical codes via _normalize_language(). This looks up the map_language_aliases table (cached per sync run). Unknown values pass through unchanged and appear in the admin Languages panel unmapped alert.

Example: "Bengali", "bn", "bengali" all normalize to "bangla".

Derived Data Added During Sync

After upsert, the app computes and stores:

  • va_summary
  • va_catcount
  • va_category_list

The sync path also maintains:

  • va_submission_workflow
  • va_submissions.org_unit_id / org_unit_resolution — the organization unit the death is attributed to, for projects that have a tree

Organization routing (app/services/org_unit_routing_service.py) runs for every created or changed submission, inside the same upsert loop:

  • the deepest org_<level_code>_code field naming a live unit of the project wins; failing that, the ODK mapping's fallback unit; failing that, the submission stays unrouted for the data manager's queue
  • a routing context is built once per form, so the project's levels and its unit-code lookups are not re-queried per submission
  • routing is idempotent, and a data manager's manual pin is never overwritten
  • projects with no organization tree are skipped entirely

Each mapped form is also checked once per run against the project's expected org_<level_code>_code fields, reading the field list from ODK Central through the same client. Missing required fields (an absent optional level is not missing) are logged and written to the run's progress log, so a form edited in Central after setup surfaces immediately rather than as a growing unrouted queue. The check is advisory: it never blocks a sync, and a Central failure on the field list is ignored.

Policy: docs/policy/organization-model.md.

Current behavior:

  • newly inserted consented submissions are initialized as attachment_sync_pending
  • updated submissions refresh their canonical workflow row after local workflow artifacts are deactivated
  • attachment completion transitions attachment_sync_pending submissions into smartva_pending
  • SmartVA generation transitions smartva_pending submissions to ready_for_coding once results are saved
  • active SmartVA projections are expected to align to the submission's current active payload version

Desired target behavior:

  • consent-valid submissions should enter attachment_sync_pending first
  • only after attachment syncing finishes should they become smartva_pending
  • only after SmartVA is generated, regenerated, or explicitly failed-and-recorded, should the submission become ready_for_coding
  • only new or changed payloads should pass through that gate
  • same-payload cleanup returns do not require SmartVA rerun

These are derived from mapping-driven preprocessing and drive UI rendering and workflow logic.

Per-Form Isolation

Each form is committed independently. A failure on one form:

  • Does not roll back earlier committed forms
  • Does not update last_synced_at for the failed form (it retries next run)
  • Is recorded in failed_forms; the overall run status becomes partial

SmartVA During Sync

After all forms complete, SmartVA runs on any submissions without results:

  • Prepares input CSV
  • Runs SmartVA analysis
  • Formats and stores results tied to the submission

Current upstream-review behavior:

  • Accept And Recode promotes the pending upstream payload, deactivates the old SmartVA projection, and reruns SmartVA for the new payload
  • Keep Current ICD Decision promotes the pending upstream payload but keeps the prior SmartVA interpretation by rebinding the preserved SmartVA projection to the newly active payload instead of regenerating it

Current implementation gap:

  • explicit SmartVA-failure recording is not yet implemented
  • so the failure-handled path from smartva_pending to ready_for_coding remains incomplete

Sync Scheduling

Sync is driven by Celery beat using celery-sqlalchemy-scheduler DatabaseScheduler.

Tasks:

During gap rebuilds for forms with many missing local rows, both regular sync and form backfill now queue canonical repair per fetched/upserted missing-data batch rather than waiting for the entire form gap fill to finish first.

Because canonical repair now owns the current-payload workflow handoff, these incremental batches can also advance workflow earlier:

  • attachment_sync_pending -> smartva_pending once attachments are complete
  • smartva_pending -> ready_for_coding once current-payload SmartVA is present

Within each canonical repair batch, SmartVA now runs through the production form-batch service path for the batch target SIDs instead of one generate_for_submission(...) call per submission. That keeps the current payload semantics the same while using SmartVA much more efficiently.

Default schedule: every 6 hours (configurable via admin sync dashboard without restart).

Manual triggers:

  • POST /admin/api/sync/trigger — dispatches run_odk_sync.delay(); returns 409 if a run is already in progress
  • POST /admin/api/sync/form/<form_id> — dispatches run_single_form_sync.delay(form_id)
  • POST /vadashboard/data-manager/api/forms/<form_id>/sync — dispatches scoped single-form sync for a data manager
  • POST /vadashboard/data-manager/api/submissions/<va_sid>/sync — dispatches scoped single-submission refresh for a data manager

Sync Run History

Every sync run is recorded in va_sync_runs:

Field Purpose
sync_run_id UUID primary key
triggered_by "scheduled" or "manual"
triggered_user_id FK to va_users (manual runs only)
started_at When the run began
finished_at When the run ended (null while running)
status "running" / "success" / "partial" / "error"
records_added New submissions inserted
records_updated Existing submissions updated
progress_log JSON array of timestamped progress messages
error_message First 2000 chars of exception or list of failed form IDs

Status meanings:

Status Meaning
success All forms succeeded or were delta-skipped
partial At least one form failed, at least one succeeded
error Task crashed or all forms failed

Stale running rows (older than 45 minutes) are marked error automatically on worker restart and before each new sync run.

ODK Coverage Check

A lightweight OData count utility:

  • va_odk_delta_count() — uses $top=0&$count=true with an optional since filter
  • Returns total or filtered submission count without downloading any data

Also used by GET /admin/api/sync/coverage to compare ODK totals against local va_submissions counts per site mapping.

The sync dashboard also exposes a separate backfill coverage view and trigger:

  • GET /admin/api/sync/backfill-stats returns project/site/form completeness counts for:
    • local stored submission totals
    • metadata completeness
    • attachment completeness
    • attachment row coverage split between current rows (storage_name IS NOT NULL) and legacy rows (storage_name IS NULL)
    • SmartVA completeness (smartva_complete)
    • SmartVA missing, failed/null, and no-consent detail counts (smartva_missing, smartva_failed, smartva_no_consent)
    • SmartVA missing excludes consent-refused rows (va_consent = "no")
  • POST /admin/api/sync/backfill/form/<form_id> triggers the ODK-backed repair path for one form
  • GET /admin/api/sync/legacy-attachment-stats returns counts of va_submission_attachments rows where storage_name IS NULL, split between legacy media rows and intentionally skipped audit.csv rows, plus a derived count of already repaired legacy media rows
  • POST /admin/api/sync/legacy-attachment-repair triggers a dedicated repair task that assigns deterministic opaque storage_name values to legacy non-audit.csv attachment rows and renames the local files accordingly
  • The dashboard table groups rows by project, site, and form so operators can see which forms are missing local data, metadata, or attachments before triggering a repair
  • In the Form Repair table, SmartVA displays complete-only counts in-cell; missing, failed/null, and no-consent counts are shown on hover
  • The Legacy Attachment Rows card should normally show zero legacy media rows; nonzero values indicate renderability gaps that the general repair-coverage query will not surface on its own

Important distinction:

  • POST /admin/api/sync/form/<form_id> is the force-resync path and bypasses the delta check for the whole form
  • POST /admin/api/sync/backfill/form/<form_id> is the targeted repair path:
    • it fetches ODK instance IDs for the form
    • backfills only missing thin local submissions
    • then revalidates the current ODK payload for each repair candidate before ordinary metadata, attachment, or SmartVA repair continues
    • those ODK payload/comment fetches now use the configured connect/read request timeouts and the bounded repair stages currently run in batches of 5 submissions
    • ordinary repair still runs only for submissions with local gaps
    • protected submissions whose current ODK payload changed during that revalidation follow the existing finalized_upstream_changed / pending-upstream path and are held out of ordinary attachment and SmartVA repair against the stale active payload
    • attachment repair now also treats legacy attachment rows with storage_name IS NULL as incomplete and migrates them onto opaque storage names during the same run, even when the local file already exists
  • the form backfill trigger can therefore repair thin data, metadata enrichment, attachment sync, legacy attachment-row migration, and SmartVA follow-through together without redownloading all form rows

Admin Sync Dashboard

Available at Admin Console → Data Sync (admin-only).

Sections:

  • Status bar — last run outcome; auto-refreshes every 30s (5s while running)
  • Sync — manual routine sync trigger; 409 guard against concurrent runs
  • Stop — shown only while a sync run is active; sends a revoke/terminate signal to the active Celery sync task and marks the run cancelled
  • Schedule configurator — change beat interval (1–168h) without restarting
  • Coverage table — ODK total vs local total, last synced time, per-form Force-resync button; loaded on demand rather than automatically on panel load
  • Form Repair coverage table — project/site/form completeness counts for local data, metadata, attachments, and SmartVA, with a per-form Repair trigger; the attachments cell also shows current-vs-legacy attachment row coverage
  • Legacy Attachment Rows — status card for storage_name IS NULL rows, repaired legacy media row totals, plus a dedicated Repair trigger for legacy media rows
  • Progress log — live timestamped entries; clears and resets when a new run starts
  • Run history — last 20 runs with duration, trigger source, status, and error detail

Local Storage Layout

data/
  <form_id>/
    <odk_form_id>.csv        ← rebuilt from va_submissions.va_data after each sync
    media/
      <filename>             ← attachment files (never cleared between syncs)
      <basename>.mp3         ← converted audio (original .amr deleted after conversion)

Mapping Spreadsheets

Under resource/mapping:

  • mapping_labels.xlsx — field display config and category ordering
  • mapping_choices.xlsx — choice value mappings
  • icdcodes.xlsx — ICD lookup data

These feed mapping generation used by preprocessing and rendering. They are not used during the ODK sync itself.