Questionnaire Dropdown vs Global Metadata (KeyPointer) Value Mismatch

(Also: Global metadata with name {label} does not have any choices present if options are empty.)

Code (source of truth for the rule):

• questionnaire_v2/utils/question_variable_and_global_metadata_validations.py — validate_dropdown_options_for_question_variable compares question_variable.choices to the set of option["value"] from global metadata format_option.options.
• questionnaire_v2/question_variable/domain/use_cases/validate_question_variable_with_global_metadata_use_case.py — wires validation for DROPDOWN / MULTI_DROPDOWN.

Not the same as:

• Campaign Excel / ₹ in options — see campaign-excel-inr-dropdown-triage.
• Edit Template Questionnaire stale prefill — see edit-template-questionnaire-contractdata-split.
• Hidden variable data loss on edit — see questionnaire-hidden-variable-data-loss.

When to Use / Not Use

Use when:

• Customer updated dropdown options in Metadata Manager but Workflow Manager (questionnaire/template) save fails with global-metadata validation messaging.
• You can see label vs value drift in Django admin: KeyPointer.format_option JSON shows labels matching new copy while values still hold legacy strings.
• Engineering needs a data fix path. The first decision is whether any ContractKeyPointer rows still use the legacy option values:
  • if yes, align KeyPointer, ContractKeyPointer, and QuestionVariable / FrozenQuestionVariable, then resync affected contracts.
  • if no, a lighter fix can be enough: update KeyPointer and questionnaire variables to the intended full option set without CKP migration.

Do not use when:

• The failure is Excel/campaign ingestion or currency-symbol stripping — use the campaign skill above.
• No global metadata linkage — different triage.

Investigation Workflow

1. Capture identifiers from the ticket or admin:

   • {workspace_id}, cluster (api.{us|in|eu}.spotdraft.com), workflow / template / questionnaire links if provided.
   • Global metadata: KeyPointer id or search by label / template_field_name (question variable name).

2. Inspect global metadata (KeyPointer) — Django admin (replace cluster):

   • List: https://api.{cluster}.spotdraft.com/admin/historic_contracts/keypointer/?created_by_workspace_id={workspace_id}&q={search_term}
   • Change: https://api.{cluster}.spotdraft.com/admin/historic_contracts/keypointer/{key_pointer_id}/change/
   • In format_option, confirm each option: for dropdowns, value is what validation uses; compare to the labels the customer believes they “renamed.”

3. Inspect questionnaire variables for the same logical field:

   • QuestionVariable / FrozenQuestionVariable by workspace + name (template_field_name / variable name). Check choices list vs KeyPointer option values. Mismatch confirms the failure mode.

4. Check contracts using old values before choosing the mitigation path:

   • Query ContractKeyPointer rows for that key_pointer_id with value equal to legacy option strings.
   • If rows exist, those values must be migrated before or with the metadata fix, or saved answers will keep referencing stale tokens.
   • If no rows exist, you can usually skip CKP migration and contract resync.

5. Optional — BigQuery (Prod US pattern) — inspect key pointer JSON and CKP usage (run one query at a time; adjust dataset for cluster):

SELECT id, label, template_field_name, format_option
FROM `spotdraft-prod.prod_usa_db.public_historic_contracts_keypointer`
WHERE created_by_workspace_id = {workspace_id}
  AND id = {key_pointer_id}

SELECT id, contract_id, value
FROM `spotdraft-prod.prod_usa_db.public_contracts_v3_contractkeypointer`
WHERE created_by_workspace_id = {workspace_id}
  AND key_pointer_id = {key_pointer_id}
  AND value IN ({old_value_1}, {old_value_2}, {old_value_3})
ORDER BY id DESC
LIMIT 100

Use prod_india_db / prod_eu_db / prod_mea_db per region as in other skills.

Mitigation Pattern (Engineering / On-Call Script)

Incidents of this class have been mitigated with a reviewed Django shell script (dry-run first), not a self-serve product button. There are two supported branches:

Branch A — Legacy values already used on contracts

Use this when ContractKeyPointer rows still carry the old option values.

1. Define a VALUE_FIXES map: {old_value: new_value} for options where the value must change to match new labels or canonical strings.
2. KeyPointer: deep-update format_option so each affected option’s value (and usually label) matches the intended pairings.
3. ContractKeyPointer: UPDATE rows where value is still an old token to the new value for the same key_pointer_id / workspace; collect **contract_id**s touched.
4. QuestionVariable and FrozenQuestionVariable: set format_option (full options list) and choices (list of values) so questionnaire definitions match KeyPointer.
5. Async resync: enqueue partially_resync_contracts_task (or equivalent) for affected **contract_id**s and relevant fields (e.g. key_pointer_list) so downstream views stay consistent.

Branch B — No contracts use the old values yet

Use this when the old values are only present in the metadata / questionnaire definitions and there are no affected ContractKeyPointer rows.

1. Build the intended full option set and canonical choices list.
2. KeyPointer: replace or normalize format_option.options to the full intended list.
3. QuestionVariable: update format_option and choices for the linked variable name.
4. FrozenQuestionVariable: update only if published / frozen questionnaire copies for the same logical field exist and still show stale choices.
5. Skip CKP migration and contract resync if no contract rows were touched.

Operational notes:

• Always dry_run=True first; use a transaction with rollback for dry runs if that is your team’s standard.
• Get two-person review for production scripts (as in incident workflow).
• Decide the branch by checking CKP usage before you write the script; do not assume every incident needs contract backfill.
• Permanent fix is product-side: allow safe editing of option values or keep labels/values in sync in Metadata Manager — track with product/engineering backlog; this skill covers operational recovery.

Environment-to-Dataset Mapping

Same as edit-template-questionnaire-contractdata-split: spotdraft-prod + prod_usa_db / prod_india_db / prod_eu_db / prod_mea_db with public_ table prefix where applicable.

Escalation Triggers

• Unclear which variable name maps to which KeyPointer across many templates — need eng to trace template_field_name and frozen questionnaire IDs.
• Large blast radius (many workspaces or contracts) — script review + change window.
• Suspected platform bug (validation wrong, or UI not persisting choices) — file/attach logs and link a new ticket; use Groundcover / request logs around the failed save if available.

Related Incidents / Tickets

Known Example (Illustrative Only)

Do not treat as live config. From SPD-42511 / incident channel:

• Workspace 564833, US cluster, metadata field “Fee Installment Schedule”, example KeyPointer id 97109, variable name like sd_fee_installment_schedule.
• Observed drift: labels updated to strings such as Monthly installments during the Term while values remained e.g. Equal monthly installments during the term — validation failed until values and dependent rows were aligned.
• Because existing contracts already stored legacy values, the mitigation also had to update ContractKeyPointer rows and resync affected contracts.

Do not treat as live config. From SPD-42680 / incident channel:

• Workspace 671499, US cluster, metadata field “1st Merchant Location: Zipping Point”, example KeyPointer id 102636, variable name sd_1st_merchant_location_zipping_point.
• Observed drift: the metadata manager showed labels Yes / No / TBD / Maybe, but the stored options still included mismatched values such as TBD and TBD-11a0eb32-3127-4576-93f0-6c5c97b5ec23.
• Engineering confirmed there were no CKP rows to migrate, so the lighter remediation was sufficient: normalize the KeyPointer option list and update the linked QuestionVariable choices to the canonical values.

Connections to Other Skills

• execution-date-missing-from-report — KeyPointer admin URLs and BQ table naming for historic_contracts_keypointer.
• fullstory-bulk-delete-metadata-value — ContractKeyPointer model context when bulk-touching metadata per contract.