Current Data Model

Summary

The current model is centered on:

• Project
• Site
• Form
• Submission

with workflow state attached to submissions.

Submission Identity

Current submission identifiers serve different purposes:

• va_sid
  • canonical local technical submission id
  • built as {KEY}-{form_id.lower()}
  • KEY is the stable ODK submission key (__id in OData)
• displayed VA Form ID
  • business-facing identifier shown in the UI
  • currently rendered from va_uniqueid
• instanceID
  • stored as ODK metadata
  • not the canonical local sync identity

Current rule:

• DigitVA identity and payload lineage are anchored on ODK KEY
• instanceID may change across edited versions and must not be treated as the primary local identifier

This is currently implemented as a single-project-first schema.

Core Master Tables

va_research_projects

Purpose:

• stores app project metadata

Key fields:

• project_id
• project_code
• project_name
• project_nickname
• project_status
• narrative_qa_enabled
• social_autopsy_enabled
• reviewer_social_autopsy_enabled
• coding_intake_mode
• demo_training_enabled
• demo_retention_minutes
• recode_window_minutes (1..10080, default 1440; TRN01 60): the coder recode / reviewer-eligibility window, migration c8e4a2f6b1d3 (docs/policy/coding-workflow-state-machine.md, "Recode Window")
• project_country_iso3 (nullable, SmartVA's list): stamped on each new va_submissions.va_country_iso3 and used by SmartVA ahead of va_forms.form_smartvacountry, migration c3f9a1d7e2b4 (docs/policy/va-form-project-configuration.md, "Project country")
• masked_cod_required (default true)
• cod_entry_mode (simple by default, or doris when unmasked and ICD-11)

Current role:

• top-level project master, but effectively used in a one-project deployment model
• also stores project-level workflow toggles such as Narrative QA enablement, coder/reviewer Social Autopsy analysis enablement, and coder intake mode
• now also stores demo/training project behavior:
  • whether the project is an open training pool
  • how many minutes demo-created coding artifacts should remain active before cleanup

The final coder and reviewer assessment tables also hold the unmasked-mode immediate COD and provenance, associated-condition text, and final DORIS certificate plus independent DORIS/CoDEdit result envelopes. Their mode snapshot records masking, entry mode, ICD release and pinned WHO image digest. The existing conclusive COD field remains the human final UCOD and final authority source. See DORIS COD Workflow.

va_sites

Purpose:

• stores site metadata

Key fields:

• site_id
• project_id
• site_name
• site_abbr
• site_status

Current behavior:

• each site belongs to exactly one project because project_id is stored directly on the site row

Current limitation:

• sites are not modeled as reusable across multiple projects

va_forms

Purpose:

• stores app form identity and ODK mapping

Key fields:

• form_id
• project_id
• site_id
• odk_form_id
• odk_project_id
• form_type
• SmartVA-related flags and country settings
• form_status

Current behavior:

• this table combines:
  • app-side business identity
  • project/site assignment
  • standardized form meaning
  • ODK source identity

Current convention:

• form_id is a synthetic internal key such as UNSW01NC0101
• it effectively encodes project, site, and a sequence/version

Current runtime role:

• va_forms is still the compatibility registry used by submissions, media paths, and permission queries
• active sync scope now comes from map_project_site_odk, and sync materializes or updates matching va_forms rows as needed

COD Reporting Master Tables

mas_cod_bucket_schemes

Purpose:

• stores versioned COD reporting schemes such as SRS India and CMEA10

Current schemes:

scheme_code	Source	Notes
SRS_INDIA	icd-10-CODES_SRS_India.xlsx
CMEA10	icd-10-CODES_CMEA10_mapped.xlsx
WHO_2022_VA	WHO_2022_VA_Bucket_Mapping_document_derived.xlsx plus WHO_2022_VA_Bucket_Mapping_admin_overrides.csv	The CSV holds 34 ICD codes the WHO derivation missed, added through the admin bucket editor and frozen so a fresh install reaches the same mapping count as a long-lived one. An administrator's later repointing of one of those codes wins over the frozen value.
WHO_2022_VA_2026	WHO_2022_VA_Bucket_Mapping_document_derived_2026_revision.xlsx	The WHO 2026 annex ranges. Sixteen codes deliberately depart from the annex; those departures are documented and pinned by a file-based test. Migration c5f2a8d1e9b3.

mapping_version moves when a scheme's mappings are re-imported, so a consumer can tell that a scheme's content changed without diffing its rows.

icd11_method (crosswalk | native | NULL, CHECK-constrained; migration 62a637f5c38a) says how an ICD-11 code finds its bucket in the scheme (policy: docs/policy/icd11-cod-bucket-schemes.md). NULL is an ICD-10-only scheme, which every scheme was before. WHO_2022_VA_2026 is native: migration 6c11b620f48f seeds its generated ICD-11 rows from resource/who_2022_va_2026_icd11_native_mappings.csv (output of flask cod-buckets generate-icd11 --scheme WHO_2022_VA_2026 --apply) and adds its vas_11_01 "Fresh stillbirth" leaf beside vas_11_02. ICD-11 KD3B.1 maps there; ICD-10 P95 cannot separate fresh from macerated and stays with vas_11_02. Re-importing or resetting a scheme from its workbook rebuilds its nodes, which drops the ICD-11 rows with them (and vas_11_01, which the source workbook does not have), so a native scheme falls back to NULL until the generator runs again.

mas_cod_bucket_scheme_age_bands

Purpose:

• stores age-band metadata for a COD reporting scheme

Key fields:

• scheme_id
• age_scope
• age_label
• min_age_value
• min_age_unit
• max_age_value
• max_age_unit
• level_count
• sort_order

Current behavior:

• lower bound is inclusive
• upper bound is exclusive
• all age bands persist explicit min/max bounds; no NULL open-ended ranges
• built-in open-ended scopes currently use 120 years as the explicit upper cap
• unit normalization for reporting uses:
  • days = 1
  • months = 365 / 12
  • years = 365

mas_cod_bucket_nodes

Purpose:

• stores hierarchy nodes inside a COD reporting scheme

Current behavior:

• supports variable depth by scheme_id + age_scope
• node types currently used are:
  • category
  • subcategory
  • field

map_icd_cod_buckets

Purpose:

• maps an ICD code to one reporting leaf within a scheme and age scope

Current behavior:

• one ICD code can map to only one leaf per scheme_id + icd_classification + age_scope (unique constraint and the upper(icd_code) unique index)
• icd_classification (icd10 | icd11, default icd10; migration 62a637f5c38a) says which classification icd_code belongs to. Existing rows are icd10. Every ICD-10 consumer (reporting, the COD bucket panel's editor, search and unmapped grid, JSON import, the xlsx export, the ICD-10 browser's manual-override column) filters on icd10 explicitly. The JSON export carries the field and icd11_method; a file without them imports as ICD-10 only.
• ICD-11 rows are generated, not hand-edited: match_type range or split, source_category the WHO VA cause code, mapping_note the covering range. The panel shows them read-only behind an ICD-10 | ICD-11 toggle; the mapping PATCH/DELETE endpoints refuse them.
• va_submission_cod_snapshot_mv still joins WHO_2022_VA rows without a classification filter: historical migrations rebuild that view through the same SQL builder, so it cannot reference a column that did not yet exist. WHO_2022_VA has no ICD-11 rows, and an ICD-10 code can never equal an ICD-11 one (their second characters differ).

Organization Master Tables

Health-system projects describe their hierarchy with these per-project tables (policy: docs/policy/organization-model.md; plan: docs/planning/health-system-organization-model-plan.md). Projects without rows here keep the Project > Site > Form model.

mas_org_level

• one row per level of a project's tree: level_code, level_name, depth (1 = top), is_optional, is_active
• unique on (project_id, level_code) and (project_id, depth)
• the ODK form field for a level is org_<level_code>_code

mas_org_unit

• one row per unit: unit_code (unique per project), unit_name, org_level_id, parent_org_unit_id, path (PostgreSQL ltree of unit codes, GiST-indexed), address, phone, latitude, longitude, google_maps_url, remarks, is_active
• subtree queries use path <@ :ancestor_path

mas_cadre

• per-project cadres: cadre_code (unique per project), cadre_name, is_active

map_org_level_cadre

• which cadres exist at a level: org_level_id, cadre_id, can_fill_va_form, can_code_va_form, can_supervise_interviews (migration d7f3b1a9c5e2), can_report_deaths (migration f2j6n9r4u1x7; default false, set for ANM and MPW at subcentre and ASHA at village where those rows exist), default_roles (migration g3k7o1s5w9a2, digitva-vjt; varchar[] not null, default empty, CHECK limits it to the unit-scope roles; the roles pre-ticked when a person of that cadre is granted at that level, validated on save against the row's flags with CADRE_FLAG_BY_ROLE; never a grant itself; a new grid takes DEFAULT_TYPICAL_ROLES, an existing row starts empty), is_active; unique on (level, cadre)

mas_org_unit_worker

• people attached to a unit: worker_code (unique per project), worker_name, org_unit_id, cadre_id, phone, optional user_id (va_users), remarks, is_active
• name and phone are personal data

Mentoring institutes

• mas_mentor_institute: cross-project master (institute_code unique, institute_name, is_active, created_at, created_by_user_id); not an org unit
• map_mentor_institute_org_unit: institute to district-level unit, composite PK (institute_id, org_unit_id), is_active, created_at, created_by_user_id; cascades on institute or unit delete; index on org_unit_id
• map_mentor_institute_user: institute to staff user, composite PK (institute_id, user_id), same trailing columns plus is_admin (institute admin, default false; migration d7e3a1c9b5f2); index on user_id
• many to many on both maps; they only guard grant writes (app/services/mentor_institute_service.py), they confer no access. Migration c4a9e7d2b6f1. Policy: docs/policy/organization-model.md

Unit-scoped access grants

• va_user_access_grants accepts scope_type = 'org_unit' with org_unit_id (FK mas_org_unit) and an optional descriptive cadre_id (FK mas_cadre)
• a unit grant leaves project_id and project_site_id empty; the grant's project is the unit's project, resolved in queries through the unit
• the grant covers the unit's whole subtree (path <@ grant unit path)
• check constraints: the scope shape, the role/scope pairs (collaborator, collaborator_pii, coder, coding_tester, reviewer, data_manager, interviewer may use org_unit; interview_supervisor and death_reporter (migration f2j6n9r4u1x7) may use only org_unit; site_pi may use project_site or org_unit, the In-charge, since migration e2b7c4d9a1f3, which reversed c4a9e7d2b6f1's tightening; the unit rows c4a9e7d2b6f1 deactivated stay deactive), and cadre_id only on unit grants
• partial unique index uq_va_user_access_grants_org_unit on (user_id, role, org_unit_id); lookup index on (org_unit_id, role, grant_status)
• resolution lives in app/services/org_grant_service.py; coding and reviewer enforcement still runs off va_forms, so a unit grant does not yet change what a coder may open

Submission routing to units

• map_project_site_odk.org_unit_id (nullable, FK mas_org_unit): the fallback unit for submissions of that ODK form whose payload carries no usable unit code
• va_submissions.org_unit_id (nullable, indexed, FK mas_org_unit): the unit a death is attributed to
• va_submissions.org_unit_resolution: form_field, mapping_fallback or manual, constrained to those values and required whenever org_unit_id is set (and forbidden when it is not)
• va_submissions.org_unit_pinned_by / org_unit_pinned_at: who pinned the unit by hand and when; a check constraint allows them only alongside org_unit_resolution = 'manual'
• index on (org_unit_id, org_unit_resolution) for the unrouted queue
• rules in app/services/org_unit_routing_service.py, applied by ODK sync and by web intake

Several ODK forms per project-site

• map_project_site_odk is unique on (project_id, site_id, odk_project_id, odk_form_id), not on (project_id, site_id): one DigitVA project accepts submissions from several ODK Central forms over one connection
• each mapping materializes its own va_forms row; the two are matched on project, site, ODK project and ODK form. va_forms.odk_project_id is text while the mapping's is an integer, so joins compare them as text
• the reverse rule still holds and is enforced in app/services/odk_form_mapping_service.py: one ODK form belongs to one project-site per connection
• _next_form_id already allocated sequential ids per project-site (PRJ01ST0101, …02), so several forms per pair need no identity change

Project coding scope

• va_project_master.coding_scope_level_id (nullable, FK mas_org_level): the level within which a death may be coded; NULL means no unit-based scope
• va_project_master.above_scope_coding_mode: code_any or view_only (check-constrained), governing coders granted above that level
• a project with a scope level must use pick_and_choose coding intake

Migrations: c8d2e4f6a1b3 (additive; enables the ltree extension), d9e3f5a7b2c4 (additive; adds the org_unit scope value and the two grant columns), c1d4e7f9a3b6 (additive; adds the routing columns), e2a5c8b1d7f3 (widens the mapping uniqueness constraint), f7b2d4e6a8c9 (additive; adds the project coding-scope columns).

Instrument Versions

mas_instrument_versions

Added 2026-10-05 (a8c4e2f6b9d1, beads digitva-xuf9 / digitva-6pwq).

• one row per composed form version the server has served; surrogate id, unique (instrument_code, version)
• version: <WHO version>-<first 10 hex of SHA-256> of the composed all-extensions definition
• activated_at (timestamptz, default now()): when this server first served it
• definition (JSONB): the complete composed definition with each conditional-extension item tagged extensions: [..], so an upload filled on that version can be re-checked against its rules (web_form_relevance_service, version=)
• written by served_form_service.record_served_version, once per process per version, ON CONFLICT DO NOTHING; never edited

Instrument Translation Tables

Added 2026-09-19 (WP6 of docs/planning/web-capture-project-configuration-plan.md). A display language is data, not part of the instrument bundle; the instrument's structure stays pre-built and immutable.

mas_instrument_locales

• one row per display language of one standard instrument; PK (instrument_code, locale_code)
• language_name, is_active (default false — set only by an explicit set_locale_active call; coverage is reported but never gates it, decided 2026-09-19), source_document, source_sha256, imported_at, version, updated_at
• lifecycle_state (draft default / in_review / approved), approved_by_user_id (FK va_users, nullable), approved_at (nullable) — added 2026-09-20: a locale must be approved before it can be is_active, enforced both in set_locale_active/set_locale_lifecycle_state and by ck_mas_instrument_locales_active_requires_approved (is_active = false OR lifecycle_state = 'approved'). Entering approved sets approved_by_user_id/approved_at; leaving it clears both to NULL. Migration a3f7c1d9e6b4 backfills every existing row to in_review and forces is_active to false — a deliberate mass-deactivation, not a no-op (see the migration's docstring and "Instrument Translations Panel" in docs/current-state/admin-and-setup.md). a3f7c1d9e6b4 and the model both passed the naming convention's already-prefixed name, which doubled the prefix and Postgres truncated the stored name to ck_mas_instrument_locales_ck_mas_instrument_locales_act_5121 (digitva-we0); migration d2b5c7f9e4a3 renames the live constraint to the name above, and the model now passes only the discriminator so a fresh database gets the same name directly. The same mistake was present in thirteen other CheckConstraint declarations across the models (four truncated the same way); migration fd232fab5987 renames each live constraint and every model now passes only the discriminator (digitva-liu). When declaring a CheckConstraint name, pass only the discriminator (e.g. name="depth_positive"), never the already-prefixed form (e.g. name="ck_mas_org_level_depth_positive") — the naming convention in app/__init__.py (ck_%(table_name)s_%(constraint_name)s) adds the ck_<table>_ prefix itself, and passing it again doubles it, truncating past 63 characters on longer names.
• version is bumped by every import and every edit; clients cache a locale by it and revalidate against translation_versions in the form-options payload or the serving endpoint's ETag
• en is the base locale of every instrument: always served, always first, and has no row here
• locale codes are the XLSForm label::Name (code) codes and are a different axis from mas_languages codes (kha versus khasi); nothing maps between them

map_instrument_translations

• one translated string; PK is the five key columns (instrument_code, locale_code, item_kind, item_key, field), which is also the uniqueness the importer upserts on
• item_kind is question or choice; item_key is the question name or list_name + / + the choice name; field is label, hint, guidance_hint or constraint_message (the last added 2026-10-06, digitva-8go.1; field is a plain string with no CHECK, so it needed no schema change -- migration e9h3k6p2s8v4 only seeds 429 Hindi machine drafts of constraint messages and guidance notes)
• source is imported, edited or machine (added 2026-09-20, digitva-4kj: an LLM-authored draft awaiting human review, e.g. migration b6d2f4a9c1e7's 214 seeded rows, relabelled from imported by migration c1a4b6e8d3f2). Precedence highest first: edited > imported > machine — a re-import or bulk XLIFF hand-back overwrites imported and machine rows alike and never an edited one. export_translations and coverage both exclude a machine row; the panel's Accept action promotes it straight to edited. updated_by FK va_users, updated_at
• FK (instrument_code, locale_code) -> mas_instrument_locales, ON DELETE CASCADE
• roughly 2,800 rows per fully translated locale of WHO_2022_VA
• Seeded on a fresh install (added 2026-09-20, digitva-dms): migration 7134cb5dc7b6 creates the twelve DigitVA-layer locale rows (draft, inactive) when absent and inserts the same 214 strings b6d2f4a9c1e7 seeds, as source='machine' from the start (b6d2f4a9c1e7 only inserts where a locale row already exists, so a fresh database got none of them).

map_instrument_translation_suggestions

Migration g3k7n1s5v9y2 (digitva-5op). One district-staff suggestion for one instrument translation string; accepting writes through update_string (source edited), so this table is the audit of who suggested and who decided.

• id identity PK; instrument_code + locale_code FK -> mas_instrument_locales (ON DELETE CASCADE); item_kind, item_key, field (the string, as in map_instrument_translations)
• proposed_text; seen_text (the served translation the suggester saw, NULL if none: the staleness check on accept); reason (<= 1000)
• project_id FK -> va_project_master: the project it was suggested in, whose project PI may decide
• suggested_by, suggested_at; status (pending | accepted | rejected, CHECK); decided_by, decided_at, decision_note (CHECK: a decided row has a decider and a time)
• indexes ix_mits_locale_status (instrument_code, locale_code, status, id), ix_mits_project_status (project_id, status, id), and the partial unique uq_mits_pending_per_user (suggested_by, instrument_code, locale_code, item_kind, item_key, field) WHERE status = 'pending'
• policy: docs/policy/va-form-project-configuration.md, "District review and suggestions" 7134cb5dc7b6 reads its rows from the checked-in resource/digitva_layer_translations_2026_09_20.csv rather than a third copy of the literal — b6d2f4a9c1e7 and c1a4b6e8d3f2 are applied and must not be edited, so they keep their own inlined literal copies; tests/migrations/test_seed_layer_translations_on_fresh_install.py checks the CSV against both, byte-for-byte. Precedent for a checked-in data file read by a migration: resource/icd11_mms_2026_01_hierarchy.csv / mas_icd11_mms, below.

mas_ui_string_override

Migration m4v8r2t6q1w9 (digitva-fsjz). One administrator's correction to one Expo UI string in one locale, served to every browser and phone without a new build. The bundled JSON under mobile/digitva-collect/src/strings is untouched.

• PK (locale_code, string_key): one correction per key and locale, so the pair is also the unique key. locale_code String(16), string_key String(255).
• text (Text, NOT NULL); updated_by FK -> va_users (NULL allowed); updated_at (timestamptz, NOT NULL, server default now(); the service sets it in Python).
• source (String(16), NOT NULL, server default edited, CHECK in machine/edited), migration q7c2n5x9d4b8 (digitva-cqw9). machine rows are Azure drafts, left out of _corrections, the version, the API and the export. The downgrade also refuses while machine rows exist.
• No index or table of its own beyond the PK. A revert deletes the row. The version of a locale is computed from count(*) and max(updated_at) over its rows, not stored.
• Downgrade refuses while rows exist (corrections are data).
• Written by app/services/ui_string_service.py; policy docs/policy/ui-strings.md.

The reference an item's key is checked against

instrument_translation_service.reference_items() is the WHO base workbook plus the DigitVA layer entries from the committed vendor/who-va-2022/src/generated/digitva-layers.reference.json artifact (built by tooling/who-va-2022/build-layer-reference.mjs; Python only reads it). reference_item_extensions() maps each reference key to the extension name(s) it belongs to (a WHO base item maps to an empty set); base survey-label coverage (reference_label_keys()) deliberately excludes layer labels, so it keeps its original meaning, and per-extension coverage is reported alongside it. A layer entry colliding with a WHO base key raises rather than one silently shadowing the other — the two namespaces are disjoint today.

What a submission records

va_web_intake_drafts.meta carries {"locale", "translation_version"} from the draft's start and every locale switch, and build_web_payload copies them into the payload as intake_locale and intake_translation_version, so what the respondent was shown stays reconstructible.

Web Intake Cases

Policy: docs/policy/web-intake.md, "Case worklist and interview states"; migrations c4e8a2f6b9d3 (digitva-vzk.4, worklist phases 2 and 3) and e5b2c8d4a1f7 (digitva-vzk.9, phase 5: visits, contact attempts, phones, address).

va_death_register (the case)

One row per death, for all three routes: source = 'register' (the register form), 'direct' (a questionnaire started directly, which creates its case at once), or 'odk' (an ODK-only interview, created by the sync in a tree project; registered_by is the "ODK sync" service account, which cannot sign in; a partial unique index uq_va_death_register_odk_va_sid on va_sid where source = 'odk' allows one per submission). status is the case state, written only by app/services/case_transition_service.py:

draft_identity, registered, scheduled, in_progress, paused, not_reachable, refused, submitted, duplicate, cancelled.

• deceased_name, deceased_sex, date_of_death are nullable; CHECK ck_va_death_register_identity requires has_deceased_name (see "Sealed personal data" below), the date and the sex unless the state is draft_identity or cancelled. A direct start's draft saves fill them from Id10017/Id10018, Id10019 and Id10023 (else Id10023_a/_b, else 1 July of the Id10024 year when the exact date is unknown); form edits on a registered case flow back the same way.
• date_of_birth_partial (String(7), nullable; migration b7d4e9f2a6c1): YYYY-MM or YYYY when the day, or day and month, of birth is unknown. CHECK ck_va_death_register_date_of_birth_partial_format holds the format; CHECK ck_va_death_register_date_of_birth_exact_or_partial forbids it beside date_of_birth. Prefill mapping: docs/policy/web-intake.md.
• latitude, longitude, accuracy_m (Float) and captured_at (timestamptz, UTC), all nullable (migration q7w2e5r8t1y4): the optional death-site GPS fix. CHECKs ck_va_death_register_location_range, _location_all_or_none (a full fix, with captured_at, or none; reads has_location, so a sealed fix passes) and _location_accuracy (>= 0). captured_at is server time at save. Personal data with the address's audience; never logged. Policy: docs/policy/web-intake.md, "Death-site GPS location".
• started_by_user_id (FK va_users): who opened the first interview. The migration backfilled it from each case's earliest draft.
• pending_flag (duplicate | cancel) and duplicate_of_death_id (self FK): an interviewer's flag waiting for a supervisor.
• The migration mapped the old statuses forward: va_in_progress -> in_progress, va_submitted -> submitted; registered and cancelled kept their names. Its downgrade maps back (scheduled -> registered; paused, not_reachable, refused -> va_in_progress; duplicate -> cancelled) and refuses while any case has no identity.
• Index ix_va_death_register_updated (updated_at, death_id) serves the supervisor list's keyset paging. A draft save bumps the case's updated_at (last activity). Index ix_va_death_register_registered_by (registered_by, updated_at, death_id) (migration f2j6n9r4u1x7) serves a death reporter's own list ("registered by me, newest activity first", keyset-paged).
• Index ix_va_death_register_va_sid (va_sid) (migration e2b7d4a9c6f1) serves the area dashboard's death-to-interview medians, which join the register to submissions by va_sid.
• next_visit_at, last_contact_at (timestamptz, nullable): the appointment or follow-up date and the latest contact attempt. Index ix_va_death_register_next_visit (next_visit_at, updated_at, death_id) serves the worklist sort (next visit, undated last, then last activity) and its keyset paging (GET /api/v1/intake/cases). Starting an interview clears next_visit_at.
• informant_phone_2 (String(32)) beside informant_phone; both hold a validated Indian mobile as 10 digits for rows registered from phase 5 on (older rows may hold free text). address_house_street, address_village_ward, address_landmark (Text, 200 characters by the service) beside the free-text address. All PII; they stay on the case and are not copied into the submission payload. Plaintext today; encryption at rest of these and the payload PII fields is proposed in PII Encryption at Rest.

• father_name, mother_name (Text, nullable, 200 characters by the service; migration c5e2b7a9d4f6, digitva-vzk.1): optional register-form fields, PII. They reach the payload only as the editable prefill of WHO Id10061 / Id10062 (asked for children and neonates only).

• Sealed personal data (migration e5a2c7b9d3f1, PII slice 2 T1; policy: PII Encryption at Rest, "Register design"; pii-slice-2.md). Nothing is sealed at upgrade. pii_sealed (JSONB, nullable; {field: "v1:aes256gcm:..."}), pii_dek_wrapped (Text), pii_key_id (String(16)), pii_blind_key_id (String(8)), pii_sealed_at (timestamptz). With REGISTER_PII_SPLIT_ENABLED on, a write seals deceased_name, father_name, mother_name, informant_name, informant_phone, informant_phone_2, abha_number, abha_address, address, address_house_street, address_village_ward, address_landmark, place_of_death, remarks and the GPS fix (latitude, longitude, accuracy_m as one field location) and NULLs those columns. informant_phone_bidx, informant_phone_2_bidx, abha_number_bidx, abha_address_bidx (String(128), nullable) are blind indexes with partial indexes ix_va_death_register_project_<bidx> (project_id, <bidx>) WHERE <bidx> IS NOT NULL; ix_va_death_register_pii_key (pii_key_id) WHERE pii_sealed IS NOT NULL serves rotation. Presence flags (Boolean not null, default false): has_deceased_name, has_informant_phone, has_informant_phone_2, has_location; filled by the migration from the plaintext and kept by a mapper listener (non-null plaintext sets the flag; a NULL clears it only on an unsealed row) and by the seal writer. Filters and identity_complete read the flags, never the sealed column. The downgrade refuses while any row is sealed. Readers (T2) go through register_pii_accessor.open_register_pii: the serialisers, prefill, the worklist and the shared case list open a page's rows once, only for fields the viewer may see; the case list's SQL selects the blob under the same visibility CASE as the name, and the export (with_names=False) selects and opens nothing.

map_case_transitions

One audit row per case creation, state change or flag: death_id, action (short code: created, interview_started, identity_captured, submitted, draft_discarded, flag_duplicate, details_edited and identity_from_interview (the changed field names in reason, never their values; state unchanged), ...), from_state (NULL on creation), to_state, reason (at most 200 characters, no personal data), actor_user_id, authorizing_grant_id and authorizing_cadre_id (the supervisor grant and its cadre a supervisor action relied on; NULL for team, starter and registrant moves and for rows before migration a8d4f1c7e3b9; foreign keys to va_user_access_grants and mas_cadre, not indexed), changes (JSONB, nullable; migration d8q4e1h6n3v9, digitva-uq6v: on details_edited and identity_from_interview rows, {field: {"old": ..., "new": ...}} of each case detail changed, dates as ISO strings; personal data, kept out of reason, never logged or returned by an API; NULL on other rows and rows before the migration), changes_dek_wrapped (Text) and changes_key_id (String(16)) (migration e5a2c7b9d3f1: with REGISTER_PII_SPLIT_ENABLED on, the old/new values of the sealed register fields inside changes are v1:aes256gcm: ciphertexts under this one wrapped DEK, AAD = map_case_transitions, <field>.old|new, transition_id; other fields stay plain; both NULL otherwise), created_at. Indexed on (death_id, created_at) and (actor_user_id, death_id); the second serves the worklist's "mine" filter (cases a user registered, started or acted on).

map_user_notifications

Migration h2n5q8t1v4w7, digitva-hdrv; policy docs/policy/app-notifications.md. One polled nudge per user per event: id (BIGINT identity, the poll cursor), user_id (FK va_users), kind (at most 32 characters: revision_requested, other_draft_started, case_submitted_by_other, case_reopened, interview_chosen), project_id, death_id, draft_id and va_sid (all ids, nullable, no foreign key so a purge or case change never waits on them), created_at (default now()). No name, phone or answer. Named map_* because it maps a user to an event. Indexed on (user_id, id) (the poll) and created_at (the purge). Rows are inserted in the event's transaction by notification_service.notify and deleted after 30 days by a daily beat task.

map_case_contact_attempts

One row per attempt to reach a case's family: attempt_id, death_id (FK va_death_register), attempted_at, outcome (CHECK ck_map_case_contact_attempts_outcome: reached, no_answer, wrong_number, moved, refused), next_visit_at (nullable), by_user_id (FK va_users), created_at. Outcome only, no notes. Indexed on (death_id, attempted_at), (by_user_id, death_id) and (attempted_at) (migration b8d2e5f1a7c3); the second serves the worklist's "mine" filter, the third the area staff view's 30-day attempt counts. Written by web_intake_service.log_contact_attempt; a state change the attempt causes is also audited in map_case_transitions.

Device uploads and device sessions

Migration d7a3c9e1f5b2 (digitva-kmk.1): va_web_intake_drafts.client_draft_id (the Android app's draft UUID, unique where not null) and a draft status superseded (a device upload, or a browser submit, for a closed case, kept, never submitted; migration e6a2c9d4f1b7, digitva-xz83, adds the partial unique index uq_va_web_intake_drafts_user_death_open on (death_id, user_id) where status = 'draft': one open draft per interviewer per case) and replaced (the losing version of a draft edited on the phone and in the browser: history only, one history section, client_draft_id NULL; no migration, status is free text; digitva-xz83 part B); auth_device_enrolment_codes, auth_devices and auth_device_sessions hold hashed enrolment codes, device secrets and tokens. See Device Collection API.

Migration c7a3e9d1f5b8 (digitva-5bzf.4): auth_devices.client_device_uuid (UUID, nullable, plain index ix_auth_devices_client_device_uuid): the Collect Plus install UUID sent at enrolment; repeats across rows on re-enrolment, so not unique.

Migration b9e4f2a7c1d3 (digitva-gb8w.5, docs/policy/offline-bundle-and-transfer.md 3.2): auth_devices gains signing_public_key (raw 32-byte Ed25519 public key, bytea), signing_key_id (String(16), indexed; first 16 hex of the key's SHA-256), signing_key_set_at and revoked_compromised (boolean, not null, default false: a lost or stolen device whose bundles are all refused). auth_device_signing_keys keeps every key a device has held (current one with retired_at NULL), unique on (device_id, signing_key_id), so bundles signed before a re-key still verify. Additive; downgrade drops all of it.

Migration f3a9c6d2e1b7 (digitva-gb8w.4, digitva-gb8w.8; contract docs/policy/offline-api-contract.md 4.1; model app/models/auth_station.py): va_users.user_number (BIGINT identity, unique, filled for every existing user at upgrade) is the integer user id the offline station roster and bundle actor_user_id carry, since user_id is a UUID. auth_station_custodians (PK device_id FK auth_devices; name, designation code, phone 10 digits or null, email or null, CHECK one of them, onboarded_on, verified_by FK va_users, updated_at): the station's custodian. auth_station_roster (id, device_id, user_id FK va_users, unique together; designation, roles JSONB [{role, project_id, site_id, org_unit_id}], active, updated_by, updated_at). auth_station_configs (PK config_id; device_id, issued_at, expires_at, issued_by, signed_text the exact two-line file, text_sha256 its ETag; index (device_id, issued_at)): every config issued, the newest in force. Additive and empty at upgrade; the downgrade refuses while a config has been issued.

Migration a7d3e9b2c4f6 (digitva-gb8w.2; bundle spec docs/policy/offline-bundle-and-transfer.md 3.3, 5.6; model app/models/va_bundle_ingest.py): va_bundle_ingests (PK bundle_id, the replay guard; device_id FK auth_devices, project_id, bundle_seq, manifest_sha256, station_config_id FK auth_station_configs, kem_key_id, uploader_user_id FK va_users, channel browser or device, created_at as claimed, received_at, status processing or processed, counts, report JSONB, receipt_text; indexes (device_id, received_at) for the per-station rate and (device_id, bundle_seq) for missing_seq): one row per real ingest, never for a dry run. map_bundle_records (PK client_id; bundle_id, record_type, content_sha256 of the decrypted record, death_id, applied_at; index (death_id, applied_at)): one row per applied record, for cross-bundle idempotency (same hash duplicate, other conflict) and the correction base_client_id stale check. Neither holds personal data. Additive; the downgrade refuses while an ingest is recorded.

Migration c5e8a1f4b2d9 (digitva-gb8w.2; bundle spec 5.5; model app/models/va_bundle_ingest.py): va_bundle_pending_coding (PK client_id of the coding_result; bundle_ids uuid[] of every ingest whose report names it pending, the first the original; project_id, va_sid FK va_submissions, death_id, actor_user_id FK va_users, content_sha256 of the decrypted record, result JSONB with the masked coding fields only (client_draft_id, coded_answers_sha256, kind, initial / final / not_codeable, coding_mode), status pending, applied or rejected, code (the pending reason while pending: not_ready, allocation_exists, doris_pending; the refusal once rejected), created_at, resolved_at; indexes va_sid and (status, created_at)): a coding result held until its case leaves smartva_pending / attachment_sync_pending, or an ICD-11 DORIS final the applier reprocesses (digitva-lmxr). No informant data, no ciphertext. On applied a map_bundle_records row is added. Additive; the downgrade refuses while a row is pending.

Migration f3a8c1d6e2b9 (digitva-l7c2, docs/policy/mobile-sign-in.md): va_users.email is nullable (still unique); va_users.mobile_login (String(10), unique) is the canonical sign-in number, kept in step with the free-text phone by user_account_service.assign_phone and set only when no other account holds the number (as mobile_login or as the canonical form of its phone); va_users.mobile_verified_at records a mobile-only account's first redeemed code; CHECK ck_va_users_email_or_mobile requires one of email or mobile_login. auth_mobile_codes holds one-time sign-in codes (HMAC hash, issuer, issued/expiry times, failed attempts, redeemed or voided time). The migration backfilled mobile_login only for numbers held by exactly one account (dev 2026-10-03: 103 of 107 valid numbers; two shared numbers and four 9-digit numbers left for correction); its downgrade refuses while any account has no email.

Migration c7p3d9k2m5t8 (digitva-04u4, digitva-ci8; policy docs/policy/people-and-roles-page.md): two nullable columns on va_users. job_title (String(120)) is a person's public post, written only through user_account_service.clean_job_title, never redacted, never consulted for access. last_signed_in_at (timezone-aware) is set by VaUsers.mark_signed_in on a completed web sign-in and a device session opening. The web_sign_in event in auth_security_events now also carries the client IP in detail.ip (no schema change; stored only when it parses as an address; the daily wipe_sign_in_ips_task removes it from events older than 210 days).

Death Registration Identifiers

Migration c7e3a9d1f5b2 (digitva-kzyp stage 1; policy Death Registration Identifiers; models in app/models/map_death_identifier.py). Three additive tables, no change to va_death_register, no backfill; the downgrade drops them.

mas_death_identifier_type

Per-project types of official number (CRS, MCCD, certificate): identifier_type_id (UUID PK), project_id (FK va_project_master, indexed), code (String(32), unique with project_id), label, min_length (default 1), max_length (default 64), char_type (nullable; digits, letters, letters_digits, letters_digits_hyphen_slash), pattern (String(200), nullable, safe subset and re2 compile checked on save), example (plaintext hint), required_at_report, uniqueness_scope (none or project), sort_order, is_active, timestamps. CHECK constraints on the scope, the length range (1 <= min <= max <= 64) and char_type. Never deleted, only deactivated.

map_death_identifier

One encrypted value per type per death: death_identifier_id (PK), death_id (FK va_death_register), identifier_type_id (FK), value_ciphertext (v1:aes256gcm:...), dek_wrapped (v1:aes256gcm+mlkem768x25519:<key_id>:...), value_blind_index (hex HMAC-SHA-512, String(128)), key_id, entered_by, entered_at, updated_by, updated_at. unique(death_id, identifier_type_id); non-unique ix_map_death_identifier_type_bidx (identifier_type_id, value_blind_index) (a duplicate number is a warning); ix_map_death_identifier_key (key_id) for the re-wrap job. No plaintext column exists. Written by death_identifier_service.set_identifiers (stage 2): the AAD is death_id.bytes || identifier_type_id.bytes, built by death_identifier_service.aad for both write and read; a cleared value deletes the row.

map_identifier_audit

Append-only configuration and export events: audit_id, project_id, event (type_created, type_updated, export_with_identifiers), actor_user_id, actor_role, identifier_type_id (nullable), details (JSONB: changed rule fields {field: {old, new}}; never a value), created_at; ix_map_identifier_audit_project_created (project_id, created_at). Stage 1 writes the two type events. Value writes use map_case_transitions (action = identifiers_set, changes keyed identifier:<code> with blind-index traces only; legacy rows may still hold a mask member), not this table.

Payload Version PII

map_payload_version_pii

Migration d4f1b8c2a6e9 (PII slice 1b part I; policy Personal Data Encryption at Rest, "Payload design"; model app/models/map_payload_version_pii.py; status pii-slice-1b.md). Additive. Empty until the release: the writers are wired (part II-A) but write it only with PAYLOAD_PII_SPLIT_ENABLED on, which stays off until the release (the readers are switched, part II-B); flask pii split-payloads --apply fills it for every existing version in the release window, and --restore joins it back. While it is empty payload_data holds every field. The downgrade drops it.

With the flag on, va_submission_payload_versions.payload_data holds no field of the form type's sealed set (pii_field_registry.sealed_field_ids, OD9), payload_fingerprint is payload_pii_split.version_fingerprint (the stripped payload plus this row's pii_digest; flag off it stays canonical_payload_fingerprint), and va_submission_upstream_changes.previous_va_data / incoming_va_data are stripped copies. The only writer of both together is submission_payload_version_service.write_payload_version.

One row per payload version: payload_version_id (PK, FK va_submission_payload_versions), va_sid (FK va_submissions, indexed), sealed (JSONB {field_id: "v1:aes256gcm:..."}, each value the JSON text of the answer, all under one data key per row; AAD map_payload_version_pii || 0x00 || field_id || 0x00 || payload_version_id bytes), dek_wrapped, key_id (ix_map_payload_version_pii_key, for the re-wrap job), pii_digest (keyed digest the version fingerprint is built from, so comparisons never decrypt), sealed_ids (JSONB sorted list: the sealed set the digest was computed under; every comparison against the row splits both sides on it, so an admin is_pii toggle never reads as a change before split-payloads --apply reruns), abha_number_bidx and abha_address_bidx (String(128), nullable, partial indexes on not null), blind_key_id (first 8 hex of SHA-256 of the blind key), review_flags (JSONB list of field ids the national-ID sweep flagged; ids only), created_at, updated_at. Read and written only by app/services/payload_pii_accessor.py. Deleted with its version by the synthetic demo reset (DELETE_TABLES).

Respondent Consent

Migration e8c4f2a7b913 (digitva-gb8w.3; contract Offline API Contract section 5; model app/models/va_consent.py; service app/services/consent_service.py). Additive and empty at upgrade: a project without a settings row does not require consent, so every existing project behaves as before. No table holds informant personal data. The downgrade refuses while va_consent_records has a row.

mas_project_consent_settings

One row per project (PK project_id, FK va_project_master): consent_required (default false), contacts (JSONB, the 5.2 contacts object: deletion, mentor_institution, central_team, dpo or null, districts keyed by district org_unit_id), retention_period_days (null: until the project ends), expiry_action (erase|lock), refusal_behaviour (interview_without_informant_data), updated_by, updated_at. Written only by consent_service.save_settings.

mas_consent_notice_versions

notice_version_id (PK), project_id, version (int >= 1), language, text, text_sha256 (hex SHA-256 of the text's UTF-8 bytes as stored), purposes (JSONB [{code, label, required}] by code), covered_fields (JSONB), allowed_modes (JSONB subset of spoken, tap, signed), witness_allowed, contacts_snapshot (the settings' contacts when created), created_by, created_at, retired_at (null: current; no API sets it yet), approved_at / approved_by (FK va_users; null: pending legal review; set once by an admin, consent_service.approve_notice_version, migration d4a7c9e2f5b1). Unique (project_id, version, language). The text, purposes and fields are never updated: a change is a new version; only the approval is set later.

va_consent_records and va_consent_record_purposes

Append-only (a trigger, va_consent_refuse_update, refuses UPDATE on both; the app has no delete path, the synthetic demo reset deletes its own project's rows through DELETE_TABLES). va_consent_records: consent_id (PK), client_consent_id (the client's client_id, unique: the idempotency key), project_id, death_id (FK the case), client_draft_id (the interview attempt, null for a registration consent; the spec sketch's draft_id), notice_version_id (FK), notice_language, notice_sha256, event (granted|refused|withdrawn; withdrawal is digitva-athy), mode, witness_present, supersedes_consent_id (FK self), recorded_by, recorded_at (client clock), received_at, channel (web|device| bundle), content_sha256 (fingerprint of the client object, case and recorder, telling a resend from reuse of a client_id). Indexes ix_va_consent_records_death (death_id, received_at), ix_va_consent_records_client_draft (client_draft_id). va_consent_record_purposes: PK (consent_id, purpose_code), required (as the version said), granted.

map_consent_audit

Append-only configuration events (consent_settings_saved with {field: {old, new}}, consent_notice_created with version, language, text hash and purpose codes): audit_id, project_id, event, actor_user_id, actor_role, notice_version_id (nullable), details, created_at; ix_map_consent_audit_project_created.

Training Sessions

The Training & demo project (TRN01, docs/policy/training-demo-project.md) keeps its trainings in three tables. They are additive (migration e3b7d1a9c4f2); no existing table changed. A session's cases are ordinary va_submissions rows under the TRN01 web form, so every case screen, query and export treats them like any other case.

mas_training_sessions

One training. training_session_id (PK), project_id (FK va_project_master), session_code (S001, S002, ... per project; unique with project_id), name, created_at (timezone-aware), created_by (FK va_users), org_unit_id (FK mas_org_unit, unique: the session root unit, PHC level), status (active or closed) and coder_slots (1 to 100, default 20; migration f6b2d8a4c1e7; the session has coder_slots x 10 cases). Index (project_id, created_at).

map_training_cases

One row per case copy. va_sid (PK and FK va_submissions, trn-<code>-<n>), training_session_id (FK), case_number (1 to 1000, unique per session), coder_slot (1 to 100), source_va_sid (plain text: the source case belongs to another project; not unique, since a source case may be copied into several slots of one session) and source_final_cod (the source case's authoritative final cause, the trainer's answer key; read only by admin-only code, never rendered on a coder screen). Index (training_session_id, coder_slot).

map_training_participants

An existing user added to a session in one role (death_reporter, interview_supervisor, interviewer, coder, reviewer_data_manager). participant_id (PK), training_session_id and user_id (FKs), role, coder_slot (1 to 100; set exactly for coders), grant_ids (uuid array: the va_user_access_grants written for the role, two for reviewer_data_manager), added_at, added_by, onboarded_at (null until the user pressed Start on the welcome page) and status (active or removed). Unique (training_session_id, user_id, role); unique (training_session_id, coder_slot) where the slot is set; index (training_session_id, role); a partial index on user_id where status = 'active' AND onboarded_at IS NULL serves the sign-in onboarding check.

Removing a participant or closing a session changes only the status of the listed grants; nothing here is deleted. The tables are not in the DEMO01 reset scope, but map_training_cases is named in its dependency list (synthetic_demo_service.DELETE_TABLES) because it references va_submissions.

ICD Reference Master Table

mas_icd10_2019_2

Purpose:

• stores the ICD-10 2019 hierarchy as denormalized master reference data for coding and future policy curation

Key fields:

• code
• title
• node_type
• semantic_level
• parent_code
• chapter_code
• block_code
• three_character_code
• sort_order
• is_coding_selectable
• sex_selectable
• age_group_selectable
• is_active

Current behavior:

• one row per ICD hierarchy node
• includes chapters, blocks, three-character categories, and detailed dotted codes
• includes modifier-derived detailed rows expanded from the WHO ClaML XML, such as V01.1, V10.4, and E10.0
• fresh migration creation seeds the table from the checked-in ICD hierarchy CSV
• coder ICD search now reads from this table for selectable three-character and detailed-code lookup
• coding-time age policy supports all, neonate, infant, child, and adult; submission age is classified as <28 days, 28-<365 days, 365 days-<12 years, and >=12 years
• preserves local policy flags in the same table
• import is idempotent:
  • existing rows are updated by code
  • newly introduced rows are inserted
  • source-missing rows are marked inactive rather than deleted

mas_icd11_mms

Purpose:

• stores the WHO ICD-11 MMS linearization (a given release, e.g. 2026-01) as master reference data, mirroring the mas_icd10_2019_2 policy pattern. See docs/policy/icd11-reference-catalog.md.

Key fields:

• id (surrogate uuid primary key)
• release, linearization_uri (unique together — the stable key per release)
• foundation_uri (nullable — residual categories have none)
• code (nullable — chapters and blocks have none), block_id
• title (WHO's - depth prefixes stripped)
• class_kind (chapter | block | category), depth_in_kind, chapter_no
• is_residual, is_leaf, primary_tabulation (informational only — WHO leaves its meaning undefined), coding_note, sort_order
• parent_foundation_uri (from the export's Parent column), parent_linearization_uri (resolved on import)
• is_coding_selectable, sex_selectable, age_group_selectable, policy_status, restriction_note (DigitVA-local policy fields, same semantics as mas_icd10_2019_2)
• is_active, source_version, source_path

Current behavior (phases 1-2 of docs/planning/icd11-coding-screen-integration-plan.md):

• one row per linearization entity in the frozen WHO Simple Tabulation export (docs/icd-causegrp-mappings/migration-artifacts/icd11-mms-2026-01-base-2026-09-16/)
• fresh migration creation seeds the table from the checked-in generated CSV (resource/icd11_mms_2026_01_hierarchy.csv)
• import (app/services/icd11_mms_service.py::import_icd11_mms_from_export, flask icd11 import) is idempotent and streamed/batched, upserting on (release, linearization_uri); source-missing rows are marked inactive, never deleted; policy columns are preserved on rerun unless apply_policy_columns is set
• admin browser and policy editor at /admin/panels/icd11-browser (app/routes/admin_icd11.py); policy is curated there or through flask icd11 policy-export/policy-import, which share one JSON format
• va_project_master.icd_classification (icd10 | icd11 | selectable, default icd10, CHECK ck_va_project_master_icd_classification; migration 33dea3ea3303) is the only source of a death's coding classification, for ODK and web-form submissions alike; resolved by app/services/icd_coding_value.py::get_icd_classification_for_submission (submission -> va_forms.project_id -> project). selectable lets the coder pick ICD-10 or ICD-11 per death with a switch on the coding screen (va_form_partials/_icd_classification_switch.html). Saves go through validate_coding_value_for_submission, which checks a fixed project's one catalogue, or in a selectable project the catalogue the value's code shape names. ICD-11 coding search: GET /api/v1/icd11/coding-search/<va_sid>. Policy: docs/policy/va-form-project-configuration.md ("5. ICD classification")
• map_project_site_odk.icd_classification is deprecated (kept for rollback; not read, written or shown). Migration 33dea3ea3303 moved its values up to the project: one value -> that value, both values -> selectable
• the ICD-11 per-code allowability policy is signed off (2026-09-29) and shipped by migration a7c3e9f1b5d2: release 2026-01 rows still never reviewed take the frozen policy (resource/icd11_mms_2026_01_policy_signoff_2026_09_29.json) and are marked reviewed; see docs/policy/who-2022-icd11-coding-allowability.md

mas_va_cause_definitions

Purpose:

• WHO VA cause codes, titles and definitions shown to coders and reviewers (the "VA Definitions" modal and /help/va-definitions), editable by admins. Policy: docs/policy/va-cause-definitions.md.

Key fields:

• id (uuid primary key), va_code (unique cause code, e.g. VAs-01.01; WHO section headings and VAs-98 are groups and are not stored)
• title, definition_html (sanitized rich text, allowlist in app/utils/rich_text.py), is_active, source
• created_at, updated_at, updated_by (nullable FK to va_users, ON DELETE SET NULL; set only by an admin edit, and protects the row from flask va-definitions import unless --force)

Current behavior:

• migration fba41e2f1f9d creates the table and seeds 63 rows from resource/va_cause_definitions_who_2022.json; flask seed run loads the same file, skipping existing codes
• read by GET /api/v1/va-definitions in one query, in va_code order; GET /api/v1/va-definitions/for-icd joins map_icd_cod_buckets -> mas_cod_bucket_nodes (vas_01_02 -> VAs-01.02) to find the definition for a selected ICD code; edited through /admin/api/va-definitions (app/routes/admin_va_definitions.py)

Submission Table

va_submissions

Purpose:

• stores imported ODK submissions after preprocessing

Key fields:

• va_sid
• va_form_id
• va_submission_date
• va_odk_updatedat
• va_data_collector
• va_odk_reviewstate
• va_sync_issue_code
• va_sync_issue_detail
• va_sync_issue_updated_at
• va_instance_name
• va_uniqueid_real
• va_uniqueid_masked
• va_consent
• va_narration_language
• va_deceased_age (from finalAgeInYears; web and device payloads get it from the server, see docs/policy/who-2022-age-derivation.md)
• va_deceased_age_normalized_days
• va_deceased_age_normalized_years
• va_deceased_age_source
• va_deceased_gender
• va_data
• va_summary
• va_catcount
• va_category_list

Current behavior:

• va_form_id points to va_forms.form_id
• va_data holds the processed ODK row as JSONB
• additional structured and derived fields are extracted for workflow and UI use
• normalized age fields store sync-time WHO age precedence outputs for downstream analytics
• va_odk_reviewstate mirrors ODK Central review state locally for dashboard visibility
• va_sync_issue_* captures local sync-health markers such as missing_in_odk
• coder-ready counting paths are indexed for common filters:
  • va_form_id
  • lower(va_narration_language)
  • join key va_sid

Workflow Tables

va_allocations

Purpose:

• reserves one submission for one user for coding or review

Key fields:

• va_sid
• va_allocated_to
• va_allocation_for
• va_allocation_status
• va_allocation_training (boolean, NOT NULL, default false; true for an allocation inside the training project, DIGITVA_TRAINING_PROJECT_ID)
• timestamps

Indexes: ix_va_allocations_to_for_createdat on (va_allocated_to, va_allocation_for, va_allocation_createdat) serves "this user's most recent coding or reviewing allocation", which picks the project a queue opens on (digitva-umc4, migration j7n3q9w5y2e8). The partial unique index uq_va_allocations_active_to_for on (va_allocated_to, va_allocation_for, va_allocation_training) WHERE va_allocation_status = 'active' (created by a7c3e9f1d5b2, digitva-igq7; rebuilt over the side by d6a1f3b8c2e4, digitva-rcwt, which also adds the column and marks existing training-project rows) enforces at most one active coding and one active reviewing allocation per user and side (training or real work); an allocator whose insert is refused resumes the held allocation or answers allocation_exists.

va_submission_payload_versions

Purpose:

• holds every version of a submission's answers, one row per version, with version_status in active, pending_upstream, superseded or rejected and a unique-active index per va_sid

Current behavior:

• payload_data is the answers as stored; va_submissions.active_payload_version_id names the version in force
• validation_err (JSONB, NOT NULL DEFAULT '[]') records the server's own disagreements with the client's valid: true for that version -- {"question", "rule"} where rule is relevant, constraint or evaluation_error. Never enforced and never carries an answer value: it exists so the disagreement rate can be measured before anyone decides whether to start refusing submissions. Per-version deliberately, so a resubmission carries its own record rather than overwriting its predecessor's. Migration f2031819b3aa; policy: docs/policy/va-web-form-options.md.

Interviewer revisions (migration f7b3d9e1a5c4, digitva-bhpl part A): nullable revision_reason_code (String 32; a fixed code, never free text) and answers_sha256 (String 64; SHA-256 of the exact answers text the client sent) on the version a revision creates, and answers_sha256 on a web or device submission's first version when the draft had one. Also the partial index ix_va_web_intake_drafts_va_sid (va_sid where not null) on va_web_intake_drafts. A submitted draft stores its complete raw answers in a final section; a revision replaces it and keeps the previous answers as a replaced draft row. See Device Collection API.

va_submission_workflow

Purpose:

• stores one canonical local workflow-state row per submission

Key fields:

• va_sid
• workflow_state
• workflow_reason
• workflow_updated_by_role
• workflow_updated_by
• workflow_created_at
• workflow_updated_at

Current behavior:

• this table is additive and coexists with legacy workflow tables
• current rows are backfilled from active legacy records
• current route integration updates the row on:
  • coder allocation start
  • initial COD submit
  • final COD submit
  • coder Not Codeable submit
  • data-manager Not Codeable submit
  • sync-created submissions
  • stale coding allocation release
• coder dashboard availability and coder intake selection now read this table
• data-manager triage also writes directly to this table
• completion history and recode behavior still rely on legacy workflow tables in parallel, so this table is the canonical state store under migration, not yet the sole source of truth
• coder-ready workflows are additionally indexed on (workflow_state, va_sid) to reduce join/filter cost in coder and dashboard APIs

va_icd_codes

Deprecated as of 2026-04-20.

Purpose:

• stores searchable ICD code catalog entries for coding forms

Key fields:

• icd_code
• icd_to_display
• category

Current behavior:

• API search supports both code-prefix and display-text matching
• performance indexes include:
  • lower(icd_code) (prefix/code search)
  • gin(lower(icd_to_display) gin_trgm_ops) for text matching
• primary ICD lookup behavior has moved to mas_icd10_2019_2
• remaining va_icd_codes usage is legacy compatibility and source-workbook support, not the primary runtime ICD catalog

mas_cod_bucket_schemes

Purpose:

• stores versioned reporting taxonomies for ICD bucket aggregation

Key fields:

• scheme_code
• scheme_name
• mapping_version
• source_path
• is_active

Current behavior:

• separate from submission COD data
• examples include SRS India and CMEA10

mas_cod_bucket_nodes

Purpose:

• stores the hierarchy nodes inside a reporting scheme

Key fields:

• scheme_id
• age_scope
• node_type
• parent_node_id
• node_code
• node_label

Current behavior:

• supports variable hierarchy depth
• supports age-scoped trees, used by SRS India
• supports flat schemes, used by CMEA10

map_icd_cod_buckets

Purpose:

• maps an ICD code and optional age scope to a reporting leaf node

Key fields:

• scheme_id
• age_scope
• icd_code
• node_id
• source_sheet
• source_row_number

Current behavior:

• leaves coded submissions unchanged
• drives reporting-only COD bucket aggregation
• icd_classification (icd10 | icd11) is part of the unique key; see the COD Reporting Master Tables section above

va_initial_assessments

Purpose:

• first coding pass by coder

Key fields:

• va_sid
• va_iniassess_by
• va_immediate_cod
• va_antecedent_cod
• va_other_conditions
• icd11_provenance (nullable JSONB)
• doris_certificate, doris_result, codedit_result, cod_entry_mode_snapshot (nullable JSONB, migration b8e2d4f6a1c3)
• va_iniassess_status
• is_tester (Boolean, default false, migration c4e8a1f7d2b3)

Current behavior:

• a coding_tester's Step 1 row is stamped is_tester at write time and deactivated when the tester finishes (digitva-ggc3)

• masked DORIS (masked ICD-11) Step 1 stores the verified certificate and DORIS/CoDEdit envelopes here; va_immediate_cod is the first condition on Part I line 1 and va_antecedent_cod is the coder's confirmed underlying cause. The envelopes stay on this row; the final row links to it through source_initial_assessment_id

• va_reviewer_initial_assessments has the same four nullable JSONB columns; the masked DORIS reviewer's own Step 1 writes them the same way (its certificate starts as a copy of the coder's; the coder's row is not changed)

va_final_assessments

Purpose:

• final coding outcome by coder

Key fields:

• va_sid
• payload_version_id
• va_finassess_by
• source_initial_assessment_id
• va_conclusive_cod
• icd11_provenance (nullable JSONB)
• va_finassess_remark
• va_finassess_status
• demo_expires_at
• is_tester (Boolean, default false, migration c4e8a1f7d2b3)

Current behavior:

• a coding_tester's final COD on a real submission is written deactive with is_tester true (digitva-ggc3): it never becomes the authority, never supersedes a coder's active final, and every count that reads active rows leaves it out. Rows from before c4e8a1f7d2b3 default to false and are not backfilled (grants keep no history, so old tester rows cannot be told apart)

• this table still stores the underlying coder final-COD records

• multiple historical rows may now exist for the same submission across recode episodes
• coder final-COD rows are now stamped with the submission's current active_payload_version_id
• coder final-COD rows now store explicit linkage to the exact Step 1 draft used during final submit via source_initial_assessment_id -> va_initial_assessments.va_iniassess_id
• the active row alone is no longer the sole authority signal during the workflow migration
• normal coding leaves demo_expires_at null
• demo coding stamps a temporary expiry timestamp
• admin-started demo sessions on ordinary projects use a 6-hour expiry
• demo_training_enabled projects use demo_retention_minutes, default 10 minutes

va_reviewer_final_assessments

Purpose:

• reviewer-owned final coding outcome after reviewer secondary coding

Key fields:

• va_sid
• payload_version_id
• va_rfinassess_by
• va_conclusive_cod
• icd11_provenance (nullable JSONB)
• supersedes_coder_final_assessment_id
• va_rfinassess_status

Current behavior:

• reviewer final-COD rows are now stamped with the submission's current active_payload_version_id
• reviewer final-COD history is stored separately from coder final-COD history
• active reviewer final-COD rows may supersede coder final-COD rows in authority resolution

All four coder/reviewer initial/final assessment tables have nullable icd11_provenance JSONB. New ICD-11 selections store the full code expression, canonical MMS title, selected display text, pinned release, and server-verified WHO linearization and codeinfo URIs; foundation_uri is nullable. ICD-10 and historical assessment rows remain NULL. The original COD text fields remain the display values and are not rewritten by migration d9e0f1a2b3c4.

va_smartva_runs

Purpose:

• stores one durable SmartVA execution attempt per submission payload version

Key fields:

• va_smartva_run_id
• va_sid
• payload_version_id
• trigger_source
• va_smartva_outcome
• va_smartva_failure_stage
• va_smartva_failure_detail
• run_metadata
• run timestamps

Current behavior:

• one row is written per SmartVA attempt
• both successful and failed attempts are preserved
• each run is linked to the submission payload lineage through payload_version_id

va_smartva_run_outputs

Purpose:

• stores emitted SmartVA output rows for a SmartVA run

Key fields:

• va_smartva_run_output_id
• va_smartva_run_id
• output_kind
• output_source_name
• output_sid
• output_resultfor
• output_payload

Current behavior:

• stores the emitted SmartVA likelihood/output row as JSONB
• preserves per-run emitted output separately from the active projection row
• currently stores likelihood rows only

va_smartva_form_runs

Purpose:

• stores one SmartVA execution batch per form run

Key fields:

• form_run_id
• form_id
• project_id
• trigger_source
• pending_sid_count
• outcome
• disk_path
• run_started_at
• run_completed_at
• archive_state (local | archived | failed | absent, NOT NULL, default local, indexed)
• archive_key_prefix
• archived_at
• archive_error_code
• archive_file_count
• archive_bytes

Current behavior:

• stores the on-disk workspace path for raw SmartVA debug artifacts, relative to APP_SMARTVA_RUNS
• groups multiple va_smartva_runs created during the same form execution
• the archive columns record where that workspace lives now: with ATTACHMENT_STORE=s3 it is copied to the DigitVA bucket under smartva_runs/{project_id}/{form_id}/{form_run_id}/, verified, and then removed from the VM — at which point disk_path becomes NULL and archive_key_prefix holds the store-relative prefix
• archive_error_code is a short category only (upload_failed, verify_mismatch, store_unavailable, walk_failed, local_delete_failed) — never a path, a key or a submission identifier
• migration d3b8f1e40a72; policy baseline SmartVA Generation Policy

Analytics Materialized View

va_submission_analytics_mv

Purpose:

• provides one-row-per-submission analytics data for trends, demographics, workflow, SmartVA, and human coding

Key fields:

• project/site/form identifiers
• submission day/week/month buckets
• workflow and sync states
• normalized age fields
• analytics age band
• human initial COD and authoritative final COD
• parsed human ICD prefixes
• SmartVA causes and ICD codes
• cod_pending_upstream_review (boolean) — true when workflow_state = 'finalized_upstream_changed'; allows downstream reports to distinguish cases with an unresolved upstream change from cleanly-coded cases

KPI policy (Option C):

• coded_submissions KPI includes finalized_upstream_changed cases alongside coder_finalized, reviewer_eligible, and reviewer_finalized
• the dashboard label for these cases is "Data Changed" (not "Revoked")
• cod_pending_upstream_review is the flag that lets consumers distinguish them

Current behavior:

• populated by a PostgreSQL materialized view (rebuilt by migration b1c2d3e4f5a6)
• refreshed hourly by Celery Beat
• intended for reporting and chart APIs, not operational workflow writes

va_submission_upstream_changes

Purpose:

• durable record of each protected upstream payload change event
• captures a snapshot of what was authoritative at the time of the change so the accept/reject decision has full context

Key fields:

• va_sid — FK to va_submissions
• previous_payload_version_id — FK to va_submission_payload_versions (was active before the change)
• pending_payload_version_id — FK to va_submission_payload_versions (new/incoming payload)
• previous_workflow_state_before — the submission state at the time of the change
• previous_final_assessment_id — FK to va_final_assessments (coder COD snapshot)
• previous_reviewer_final_assessment_id — nullable FK to va_reviewer_final_assessments (reviewer COD snapshot, if the submission was reviewer_finalized at the time); added by migration aacf89977029
• change_status — pending / accepted / kept_current_icd / rejected
• resolved_by — user id of the data manager or admin who resolved it
• resolved_at — when the resolution occurred

Current behavior:

• created by record_protected_upstream_change() in app/services/workflow/upstream_changes.py whenever a protected submission receives a changed ODK payload
• accepted by dm_accept_upstream_change() — promotes pending payload to active, clears reviewer authority, routes to smartva_pending
• resolved by dm_reject_upstream_change() — promotes pending payload to active, preserves finalized ICD/COD artifacts, restores workflow_state_before, and records the kept_current_icd resolution outcome

va_final_cod_authority

Purpose:

• stores the single authoritative final-COD pointer for each submission

Key fields:

• va_sid
• authoritative_final_assessment_id
• authoritative_reviewer_final_assessment_id
• authority_source_role
• authority_reason
• effective_at
• updated_by

Current behavior:

• this table is additive and backfilled from the most recent active va_final_assessments row during migration
• runtime COD panel rendering now prefers this table when deciding which final COD to show as current
• when a recode replacement final COD is submitted, the authority row is moved to the replacement final assessment
• when reviewer final COD exists, the authority row may instead point to authoritative_reviewer_final_assessment_id
• when sync invalidates a submission's finalized COD, the authority row is cleared instead of relying only on va_finassess_status
• service-level authority resolution now rejects stale coder/reviewer final-COD rows whose payload_version_id does not match the submission's current active_payload_version_id

va_coding_episodes

Purpose:

• tracks additive coding episodes such as recode attempts without destroying the currently authoritative coder outcome

Key fields:

• episode_id
• va_sid
• episode_type
• episode_status
• started_by
• base_final_assessment_id
• replacement_final_assessment_id
• started_at
• completed_at
• abandoned_at

Current behavior:

• currently used for non-destructive recode handling
• only one active recode episode is allowed per submission
• starting recode creates an episode but does not deactivate the current authoritative final COD
• successful replacement final COD completes the episode and links the replacement final assessment
• stale allocation timeout or sync invalidation abandons the active recode episode without deleting historical COD rows

va_coder_review

Purpose:

• records coder decision that a submission is not codeable or has an issue

Key fields:

• va_sid
• va_creview_by
• va_creview_reason
• va_creview_other
• va_creview_status
• is_tester (Boolean, default false, migration c4e8a1f7d2b3): a coding_tester's not-codeable report is written deactive with it set; the case is not excluded and ODK is not flagged (digitva-ggc3)

va_data_manager_review

Purpose:

• records data-manager decision that a submission should be kept out of coder allocation

Key fields:

• va_sid
• va_dmreview_by
• va_dmreview_reason
• va_dmreview_other
• va_dmreview_status

Current behavior:

• one active row is allowed per submission
• this table is distinct from coder-owned Not Codeable records
• an active row drives canonical workflow state not_codeable_by_data_manager

va_reviewer_review

Purpose:

• legacy reviewer quality/review outcome over a submission

Key fields:

• va_sid
• va_rreview_by
• review flags and remarks
• va_rreview
• va_rreview_status

Current behavior:

• this table stores the current legacy reviewer QA/quality-review form
• it does not store reviewer final COD
• it should not be treated as the target reviewer secondary-coding artifact

va_reviewer_final_assessments

Purpose:

• stores the additive reviewer-owned final COD artifact for the new reviewer secondary-coding model

Key fields:

• va_sid
• va_rfinassess_by
• va_conclusive_cod
• va_rfinassess_remark
• supersedes_coder_final_assessment_id
• va_rfinassess_status

Current behavior:

• this table now exists in runtime schema
• reviewer final COD rows may coexist with coder final COD rows
• service-level authoritative-final-COD resolution can now prefer reviewer final COD from this table
• not all downstream readers have been cut over yet

va_narrative_assessments

Purpose:

• stores coder Narrative Quality Assessment answers for a submission

Key fields:

• va_sid
• payload_version_id
• va_nqa_by
• six scored question fields
• va_nqa_score
• va_nqa_status
• demo_expires_at

Current behavior:

• the table is now payload-version aware
• the current NQA for a coder is the active row whose payload_version_id matches the submission's active_payload_version_id
• payload changes create a new current row on next save unless a protected keep-current-ICD decision rebinds the preserved row to the promoted payload
• normal coding leaves demo_expires_at null
• demo coding (vademo_start_coding) stamps a temporary expiry timestamp
• admin-started demo sessions on ordinary projects use a 6-hour expiry
• demo_training_enabled projects use demo_retention_minutes, default 10 minutes

va_social_autopsy_analyses

Purpose:

• stores coder Social Autopsy analysis for a submission as an app-owned workflow artifact

Key fields:

• va_sid
• payload_version_id
• va_saa_by
• va_saa_remark
• va_saa_status
• demo_expires_at

Current behavior:

• the table is now payload-version aware
• the current Social Autopsy row for a coder is the active row whose payload_version_id matches the submission's active_payload_version_id
• normal coding leaves demo_expires_at null
• demo coding (vademo_start_coding) stamps a temporary expiry timestamp
• admin-started demo sessions on ordinary projects use a 6-hour expiry
• demo_training_enabled projects use demo_retention_minutes, default 10 minutes
• one active analysis row is stored per (va_sid, coder) at a time; older payload versions are kept as inactive history
• selected delay options are normalized into child rows, not flattened into the submission JSON
• demo coding stamps a 6-hour expiry timestamp on the parent row so completed demo artifacts can be cleaned up automatically

va_social_autopsy_analysis_options

Purpose:

• stores selected Social Autopsy delay-factor options under a parent analysis row

Key fields:

• va_saa_id
• delay_level
• option_code

Supporting Tables

Other important tables:

• va_users — year_of_birth (Integer, nullable, 1900 to this year) and sex (String(16), nullable, female | male | undetermined, checked by VaUsers.set_interviewer_profile, no CHECK) from migration c5e2b7a9d4f6 (digitva-vzk.3): optional interviewer details, PII, set in Profile or by an admin. Web intake prefills and locks Id10010b from sex, and prefills (unlocked) Id10010a from year_of_birth when the resulting age is 18-89 (owner decision 2026-10-20; docs/policy/web-intake.md).
• va_project_master — also carries project_target_completion_date (DATE, nullable), the admin-set target the DM burndown KPI projects against (app/routes/api/dm_kpi/dm_kpi_burndown.py), and the four web intake form options served by GET /api/v1/organization/<project_id>/form-options (migration f2a9c4d7e1b3): web_intake_default_locale (String(16), NOT NULL, default 'en'), web_intake_available_locales (JSONB, nullable — NULL means every active language), web_intake_narration_languages (JSONB, nullable — NULL means none offered) and web_intake_show_guidance (Boolean, NOT NULL, default false). Migration c3e8b5a1f4d2 adds the web questionnaire and its two extension settings: web_intake_form_type_id (UUID, nullable, FK fk_va_project_master_web_intake_form_type -> mas_form_types.form_type_id — NULL means WHO_2022_VA), web_intake_intake_note (Text, nullable — NULL means the system default welcome note, '' means no welcome screen) and web_intake_death_summary_enabled (Boolean, NOT NULL, default true). Migration e1b6c9a3d7f4 adds web_intake_medical_records_enabled (Boolean, NOT NULL, default true), mirroring web_intake_death_summary_enabled. Migration b4f8d2e6a1c3 adds web_intake_equity_enabled (Boolean, NOT NULL, server default false): equity joins enabled_extensions only when it is true (docs/policy/digitva-who-form-overrides.md, "Equity extension"). Migration b4k8m2r6w9x3 adds self_coding_enabled (Boolean, NOT NULL, server default false): in a self-coding project a coder also interviews and may code their own case (ProjectSettings.self_coding in app/services/authz/grants.py; it is on only while web_intake_mode is not off, which the admin API enforces both ways). Policy: docs/policy/web-intake.md, "Self-coding projects". Policy: docs/policy/va-web-form-options.md, docs/policy/va-form-project-configuration.md
• va_site_master
• va_project_sites
• va_user_access_grants — see Unit-scoped access grants for the org_unit scope columns. Also carries created_by_user_id (UUID, nullable, FK fk_va_user_access_grants_created_by -> va_users.user_id): the granting user, written on insert by the admin and data-manager grant routes and the project user import. A reactivated grant keeps its original value (insert-only); null means not recorded (CLI, seed, older grants). Read by the People & roles page as "granted by".
• va_usernotes
• va_smartva_form_runs
• va_smartva_runs
• va_smartva_run_outputs
• va_smartva_results
• va_submissions_auditlog
• va_icd_codes
• va_forms
• va_sites
• va_research_projects

va_submission_attachments

Purpose:

• one row per (va_sid, filename) ODK submission attachment
• ETag cache for conditional GET, plus the local blob's opaque storage_name
• readiness state for the Central-backed attachment work

Key fields:

• va_sid, filename — composite primary key
• local_path — actual path on disk; differs from filename for .amr, which is stored as its .mp3 derivative
• mime_type — copied from upstream, not authoritative (may be the derivative's type for AMR rows)
• etag, last_downloaded_at, exists_on_odk
• storage_name — opaque serving token (unique where not null)

Source/derivative state (added by migration b7e4c2a91d38, Phase 2 of the Central attachment plan). Vocabularies are constants in app/services/attachment_service.py; all timestamps are timezone-aware:

Column	Type	Meaning
source_state	VARCHAR(16) NOT NULL, default unknown	unknown, listed, available, missing, retired, error
source_verified_at	TIMESTAMPTZ	last observed Central content response
source_error_code	VARCHAR(32)	not_found, auth, throttled, transient, invalid_redirect, unknown — category only, never free text
source_mime_type	VARCHAR(64)	validated MIME of the original; mime_type is not repurposed
derivative_state	VARCHAR(16)	audio only: pending, ready, stale, error; NULL for non-audio rows
derivative_mime_type	VARCHAR(64)	e.g. audio/mpeg
derivative_source_validator	VARCHAR(128)	opaque source ETag the current MP3 was built from
derivative_verified_at	TIMESTAMPTZ	last derivative verification
derivative_error_code	VARCHAR(32)	e.g. conversion_failed
local_fallback_state	VARCHAR(16) NOT NULL, default present	present, retained (archival copy of a retired submission — never deleted), quarantined, absent

Store location (added by migration a4f1c07b62d9):

Column	Type	Meaning
store_state	VARCHAR(16) NOT NULL, default local	which store holds the blob: local (a file under APP_DATA/<form_id>/media/), s3 (an object in the DigitVA bucket; local_path is NULL for these rows), absent (not stored anywhere yet)

Indexes:

• ix_va_submission_attachments_sid_odk — partial, exists_on_odk IS TRUE
• ix_va_submission_attachments_storage_name — unique, partial, storage_name IS NOT NULL
• ix_va_submission_attachments_source_state — request-path readiness reads
• ix_va_submission_attachments_derivative_state — partial, derivative_state IS NOT NULL, for repair candidate selection over the audio rows only
• ix_va_submission_attachments_store_state — cutover tool and integrity check select rows by which store holds their object

Current behavior:

• sync writes source_state='available' with the observed time and the original's validated MIME on a successful download, 'missing' when ODK no longer lists the file, and the AMR derivative columns (including the ETag it was built from) when an MP3 is produced; a failed conversion records derivative_state='error', derivative_error_code='conversion_failed' and leaves the previous blob alone
• with ATTACHMENT_STORE=s3, sync uploads the blob and writes store_state='s3', local_path=NULL, local_fallback_state='absent'; with the local store it keeps writing store_state='local' and the file path exactly as before
• bulk presence (present_attachment_files_by_submission()) resolves an s3 row from store_state alone and a local row from disk; it never probes the bucket per row. Object-level truth comes from scripts/check_attachment_integrity.py --store s3

va_web_intake_attachments

Migration g4k8p2t6x1b5 (digitva-ej1). A file a web interviewer uploaded while the draft was still open; submit copies it into va_submission_attachments over the same stored object (Web Intake Policy, "Attachments"; Attachment Storage and Delivery Policy, "Web intake uploads").

• primary key (draft_id, client_attachment_id): draft_id -> va_web_intake_drafts.draft_id; client_attachment_id is the UUID the page made for the file, so a retried upload finds its row and stores nothing
• filename — <client_attachment_id><ext> of the detected type; the value the submission payload carries for the answer and the va_submission_attachments.filename it becomes. The uploader's own filename is never kept
• storage_name — opaque store token (unique), local_path (local store only), store_state (local or s3)
• mime_type — validated type of the uploaded original, decided from its leading bytes (an .amr is stored as an MP3 derivative)
• size_bytes, sha256 of the bytes received, created_at (timezone-aware)

Indexes: the primary key; uq_va_web_intake_attachments_storage_name (unique); ix_va_web_intake_attachments_created_at (migration h5m2r8v4z1d9, the daily purge's age scan). Discarding a draft deletes its rows and their stored objects; rows of submitted drafts stay (their objects are held by va_submission_attachments). The daily purge (digitva-i9lb) deletes the rows of an open draft untouched for 30 days and rows no answer references after 30 days, never one a submission holds, and audits each draft as draft_attachments_purged in map_case_transitions.

map_project_site_odk

Purpose:

• stores the source-of-truth mapping from a project-site pair to an ODK project and form
• optionally links that mapping to a configured form type

Key fields:

• project_id
• site_id
• odk_project_id
• odk_form_id
• form_type_id

Current behavior:

• admin project form mapping writes here
• ODK sync now enumerates this table, not va_forms, to decide what to sync
• sync then materializes compatibility va_forms rows so the legacy workflow stack continues to function

ODK Connection Tables

mas_odk_connections

Purpose:

• ODK Central connection master; one row per ODK server

Key fields:

• connection_id — UUID primary key
• connection_name — unique human-readable name for the connection
• base_url — base URL of the ODK Central server
• username_enc — Fernet-encrypted ciphertext of the username
• username_salt — per-row salt used when deriving the encryption key for the username
• password_enc — Fernet-encrypted ciphertext of the password
• password_salt — per-row salt used when deriving the encryption key for the password
• status — active/inactive status (VaStatuses)
• notes — optional free-text notes
• created_at, updated_at — timestamps

Form-Type Mapping Tables

The field-mapping system is scoped by form type under the mas_* table family. These tables are now the source of truth for structural display configuration.

mas_form_types

Purpose:

• registers supported form types such as WHO_2022_VA and WHO_2022_VA_SOCIAL

Key fields:

• form_type_id
• form_type_code
• form_type_name
• base_instrument_code — String(32), nullable (migration c3e8b5a1f4d2, backfilled WHO_2022_VA for every WHO_2022_VA% code): the standard instrument this form type layers on. Read by instrument_code_for() to tell the web form which bundled questionnaire to render; NULL means none is bundled. Policy: docs/policy/new-form-type-onboarding.md
• base_template_path
• mapping_version
• is_active

mas_category_order

Legacy compatibility table retained during the category-config cutover. Runtime navigation and category admin now use mas_category_display_config as the authoritative category table.

Purpose:

• stores category membership and base display order per form type

Key fields:

• form_type_id
• category_code
• category_name
• display_order
• is_active

mas_category_display_config

Purpose:

• stores category-level display metadata per form type

Key fields:

• form_type_id
• category_code
• display_label
• nav_label
• icon_name
• display_order
• render_mode
• show_to_coder
• show_to_reviewer
• show_to_site_pi
• always_include
• is_default_start
• is_active

Current seeded behavior:

• the schema is added by Alembic revision c7f1d2e3a4b5
• the migration seeds deterministic rows for WHO_2022_VA and WHO_2022_VA_SOCIAL
• the current seed count is 14 rows for WHO_2022_VA and 15 rows for WHO_2022_VA_SOCIAL

mas_subcategory_order

Purpose:

• stores subcategory order within a category for a form type

Key fields:

• form_type_id
• category_code
• subcategory_code
• subcategory_name
• display_order
• is_active

mas_field_display_config

Purpose:

• stores field placement, labels, flags, and order per form type

Key fields:

• form_type_id
• field_id
• category_code
• subcategory_code
• short_label
• full_label
• summary_label
• flip_color
• is_info
• summary_include
• is_pii
• display_order
• is_active

mas_languages

Purpose:

• canonical language list for the application
• used by coder profile language selection, submission filtering, and sync normalization

Key fields:

• language_code — primary key (e.g. bangla, english, hindi)
• language_name — display name (e.g. Bangla, English, Hindi)
• is_active — boolean; inactive languages hidden from profile selection

map_language_aliases

Purpose:

• maps raw ODK language values to canonical mas_languages.language_code
• lookup table used by _normalize_language() during sync

Key fields:

• alias — primary key (e.g. bn, bengali, Bengali)
• language_code — FK to mas_languages

Behavior:

• multiple aliases can map to one canonical code
• alias conflicts across languages are prevented (unique PK)
• unknown ODK values pass through unchanged and appear in admin unmapped alert

mas_choice_mappings

Purpose:

• stores per-form-type choice-value to label translations

Key fields:

• form_type_id
• field_id
• choice_value
• choice_label
• display_order
• is_active

Credential storage:

• credentials are encrypted using Fernet AES-128
• each credential field has its own per-row salt stored alongside it
• a shared pepper is read from the environment at runtime; it is not stored in the database
• multiple projects may share one connection

map_project_odk

Purpose:

• maps one app project to one ODK connection

Key fields:

• id — UUID primary key
• project_id — String(6) foreign key to the project master, unique constraint enforced
• connection_id — UUID foreign key to mas_odk_connections

Behavior:

• unique on project_id — one project has at most one ODK connection
• a project without a row here falls back to the legacy odk_config.toml connection during sync

map_project_site_odk

Purpose:

• maps a project-site pair to a specific ODK Central project ID, form ID, and VA form type

Key fields:

• id — UUID primary key
• project_id — String(6) foreign key
• site_id — String(4) foreign key
• odk_project_id — Integer; the numeric project ID on the ODK Central server
• odk_form_id — Text; the xmlFormId of the form on ODK Central
• form_type_id — UUID nullable foreign key to mas_form_types; identifies which VA form type (e.g. WHO_2022_VA, WHO_2022_VA_SOCIAL) this site uses for field display and rendering
• created_at, updated_at — timestamps

Behavior:

• unique on (project_id, site_id) — one ODK form per project-site combination
• the ODK connection used for this mapping is derived via map_project_odk and is not stored here directly
• form_type_id is optional but strongly recommended; if absent, rendering falls back to the hardcoded default WHO_2022_VA

va_sync_runs

Purpose:

• records every ODK sync run — start time, outcome, and submission-level metrics

Key fields:

• sync_run_id — UUID primary key
• triggered_by — "scheduled" or "manual"
• triggered_user_id — nullable FK to va_users.user_id (set for manual runs)
• started_at — indexed timestamp when the run began
• finished_at — null while the run is in progress
• status — "running" / "success" / "error"
• records_added, records_updated — submission counts from the completed run
• error_message — first 2000 chars of the exception on failure

Current behavior:

• written by the run_odk_sync Celery task in app/tasks/sync_tasks.py
• a "running" row is committed before sync begins so the admin dashboard can display live status
• stale "running" rows older than 2 hours are marked "error" on worker restart

va_db_backups

Purpose:

• records every database dump — where it went, how big it was, and its sha256

Key fields:

• backup_id — UUID primary key
• started_at — timestamptz, indexed DESC (ix_va_db_backups_started_at)
• completed_at — null while the dump is in progress
• triggered_by — "scheduled" / "manual" / "cli"
• triggered_user_id — nullable FK to va_users.user_id (set for admin-triggered runs)
• status — "running" / "success" / "failed" / "pruned", indexed (ix_va_db_backups_status)
• store — "local" or "s3"
• object_key — the bucket key (or the file name on the local store); the only identifier a dump has outside the store
• size_bytes, sha256 — recorded on success; the sha256 is what a restore verifies against
• error_code — short failure category (pg_dump_failed, upload_failed, verify_mismatch, dump_too_large, …), never a message from pg_dump or the transport
• pruned_at — set when retention removed the object

Current behavior:

• written by app/services/db_backup_service.py, driven by the run_db_backup Celery task, the flask backups CLI, and the admin Database backups panel
• a "running" row is committed before pg_dump starts, so an interrupted run leaves evidence rather than nothing
• DB_BACKUP_KEEP_DAILY retention flips a "success" row to "pruned" when its object is deleted; the row is kept as history
• this table is the only dump history on the VM — see backup.md

va_cod_bucket_scheme_snapshots

Purpose:

• a whole-scheme JSON snapshot taken automatically before every COD bucket scheme "reset from source", so admin edits, ICD-11 rows and manual overrides survive a rebuild that would otherwise silently drop them (policy: docs/policy/icd11-cod-bucket-schemes.md)

Key fields:

• snapshot_id — UUID primary key
• scheme_id — nullable FK to mas_cod_bucket_schemes.scheme_id, ondelete="SET NULL" (a deleted scheme's snapshots stay findable)
• scheme_code — duplicates the FK target's code so a snapshot survives scheme deletion; this is what routes filter and index by
• reason — "reset_scheme" / "reset_age_band" / "cli_import", CHECK-constrained
• age_scope — nullable; what triggered the snapshot, not what it covers (the payload is always the whole scheme, since restore is whole-scheme)
• payload — JSONB, the export_cod_bucket_scheme_json() output
• created_by_user_id — nullable FK to va_users.user_id
• created_at — timestamptz; indexed with scheme_code, created_at DESC (ix_va_cod_bucket_scheme_snapshots_scheme_code_created_at)

Current behavior:

• written inside the same transaction as the reset it precedes (snapshot_cod_bucket_scheme() flushes, does not commit); if the snapshot fails, the reset is refused
• flask cod-buckets import-* also snapshots (reason cli_import) before replacing an already-existing scheme; a fresh install skips it
• restore is the existing JSON import (import_cod_bucket_scheme_json); there is no dedicated restore endpoint, only list and download
• no retention/prune job yet — add one if this table grows large enough to matter

Schema Drift Guard

The models must describe the live schema exactly — indexes, constraint names, foreign-key names and ondelete included — so that autogenerate has nothing to say.

• Naming convention. db is constructed with a MetaData(naming_convention=...) in app/__init__.py, so constraints the models leave unnamed get deterministic names. The uq key is %(table_name)s_%(column_0_name)s_key, PostgreSQL's own default, rather than the Alembic-standard uq_...: Alembic applies the convention to tables built by op.create_table too, so the standard form would retroactively rename constraints that existing migrations already created under the PostgreSQL name.
• Externally-owned tables. app/schema_filters.include_object hides the celery_* scheduler tables and the Flask-Session va_sessions table from the comparison. migrations/env.py installs it for both the offline and online contexts.

• Check before committing a model change.

  docker compose exec -T minerva_app_service uv run flask db check
  

  It is read-only and must print No new upgrade operations detected.

• Automated guard. tests/migrations/test_schema_drift.py creates a throwaway database, builds it with flask db upgrade alone (never create_all()), and asserts that compare_metadata against db.metadata returns nothing. The rest of the suite builds its schema with create_all() and so cannot see this class of drift.

Key Current-State Observations

• sites are modeled as project-owned
• forms are overloaded with multiple responsibilities
• submissions are keyed to synthetic app form identity
• legacy runtime permissions still center on va_users.permission
• explicit auth foundation tables now exist additively in va_project_master, va_site_master, va_project_sites, and va_user_access_grants, but runtime authorization has not cut over yet
• ODK identifiers are stored per app form
• ODK connection credentials are now stored encrypted in mas_odk_connections rather than in a flat TOML file
• per-site ODK project, form, and VA form type mapping is now managed via map_project_site_odk
• the current schema is suitable for one-project-first operation, not generalized multi-project reuse