Delete Global Metadata Fields And Sections

Quick Routing

Confirmed Incident Patterns

Informed IQ incident summary (evidence-backed):

• Request: delete section OLD_JW_SOW_Info and JW_ prefixed metadata fields (21 fields).
• CKP check: 0 ContractKeyPointer rows for KP ids — safe to soft-delete definitions (still get explicit approval for prod).
• Workflow check: Metabase native query joining historic_contracts_keypointer → questionnaire_v2_questionvariable → questionnaire_v2_questionnairetoownerentitymapping (entity_type = 'WORKFLOW') → workflow_v1_workflow returned no rows for those KP ids (draft path). Re-run logic for frozen path if you need published-snapshot certainty.
• Section: Managerie delete_key_pointer_sections with dry run first (managementcommandlog id 29344), then actual run (29345) on api.us.spotdraft.com.
• Fields: Reviewed Django shell snippet in-thread to set KeyPointer.is_deleted = True (separate Metabase proof of CKP=0 was recorded before prod run).

Repeated operational conclusion:

• Section deletion and field deletion are separate steps with separate verification.
• Do not assume deleting a section fully cleans up underlying KeyPointer rows.
• Always verify is_deleted, CKP count, and workflow linkage before declaring completion.

Key Objects And Terms

Global Metadata Code Caveats

When GLOBAL_METADATA is enabled for a workspace:

• key_pointers_service.delete_key_pointers returns without deleting key pointers — see key_pointers/services/key_pointers_service.py.
• delete_key_pointer_sections still calls section soft-delete after the bulk KP use case; KP rows may remain if you relied on that command alone for “full cleanup.”
• Prefer explicit verification in DB / Metabase after any managerie run.

Identifiers To Collect First

Capture these before taking action:

• {workspace_id} and cluster (us, in, eu, mea)
• requested section ids and section name (for support comms)
• requested key pointer ids (or enumerate from section)
• whether global-metadata flag is on
• whether the ask includes unlink / “used in workflow” — then capture template_field_name list

If the incident starts with labels only, derive IDs first from admin or Metabase before discussing deletion strategy.

Investigation Workflow

1. Confirm the exact deletion request

Separate the ask into:

• section ids to delete
• key pointer ids under those sections
• standalone key pointer ids not covered by the section list

Do not merge them mentally. Example (Informed IQ): one section + 21 KPs tied to it.

2. Verify section state

• Django admin: https://api.{cluster}.spotdraft.com/admin/historic_contracts/keypointersection/{section_id}/change/
• Managerie: https://api.{cluster}.spotdraft.com/admin/managerie/contracts_v3/delete_key_pointer_sections/
• After command: confirm KeyPointerSection.is_deleted and capture managementcommandlog links for dry-run vs actual.

If the section is already deleted, do not assume the associated fields were deleted too.

3. Enumerate key pointers under the section

SELECT
  id,
  label,
  template_field_name,
  field_name,
  section_id,
  is_deleted
FROM `spotdraft-prod.{dataset}.public_historic_contracts_keypointer`
WHERE created_by_workspace_id = {workspace_id}
  AND section_id IN ({section_ids})
ORDER BY id;

Use prod_usa_db, prod_india_db, prod_eu_db, or prod_mea_db for {dataset}.

4. Check whether any contract values still use those key pointers

SELECT
  id,
  contract_id,
  key_pointer_id,
  value
FROM `spotdraft-prod.{dataset}.public_contracts_v3_contractkeypointer`
WHERE created_by_workspace_id = {workspace_id}
  AND key_pointer_id IN ({key_pointer_ids})
ORDER BY key_pointer_id, contract_id;

Routing rule:

• 0 CKPs: field definition soft-delete is often acceptable after explicit engineering + customer sign-off
• non-zero CKPs: do not casually soft-delete definitions; plan migration / blast radius

5. Confirm “used in workflows” (draft questionnaire path)

Postgres / Metabase pattern (matches incident 3069 draft check — adjust schema if your warehouse prefixes tables):

Join chain:

historic_contracts_keypointer.template_field_name = questionnaire_v2_questionvariable.name
→ questionnaire_v2_questionnairetoownerentitymapping.questionnaire_id
→ filter entity_type = 'WORKFLOW'
→ workflow_v1_workflow.id = entity_id

Required filters:

• kp.created_by_workspace_id = {workspace_id} AND kp.is_deleted = false AND kp.template_field_name IS NOT NULL
• qv.created_by_workspace_id = {workspace_id} AND qv.is_deleted = false
• qtom.created_by_workspace_id = {workspace_id} AND qtom.is_deleted = false
• w.tenant_workspace_id = {workspace_id} AND w.is_deleted = false
• Optional strict “active”: w.status = 'PUBLISHED'

Empty result ⇒ no draft workflow-owned questionnaire variable with that name for those KPs (in that workspace).

6. Confirm frozen workflow path (recommended)

Repeat using:

• questionnaire_v2_frozenquestionvariable (name, frozen_questionnaire_id)
• questionnaire_v2_frozenquestionnairetoconsumerentitymapping with entity_type = 'FROZEN_WORKFLOW'
• workflow_v1_frozenworkflow → workflow_v1_workflow

Same workspace and is_deleted discipline on each hop.

7. Optional — condition library

If stakeholders ask about conditions (not just questionnaire variables), scan core_condition for the workspace where entity_type IN ('WORKFLOW','FROZEN_WORKFLOW') and condition blobs reference template_field_name / intake.{name} (JSON logic, AST, or complex expression text).

8. Distinguish section deletion from field deletion

1. Section only — soft-delete section; confirm whether fields must remain visible in admin.
2. Section + fields — section path first, then KP soft-delete after CKP + workflow checks.
3. Standalone fields — separate batch of ids and checks.

9. Check deletion blockers for key pointers

Bulk deletion can reject:

• computed key pointers
• parent key pointers with children in section pointers_config

Relevant code:

• contracts_v3/management/commands/delete_key_pointers.py
• contracts_v3/management/commands/delete_key_pointer_sections.py
• contracts_v3/contract_key_pointers/domain/use_cases/delete_bulk_key_pointers_use_case.py
• contracts_v3/contract_key_pointers/domain/use_cases/delete_bulk_key_pointer_sections_use_case.py

If blockers fire, escalate instead of forcing raw DB edits.

Operational Actions

A. Delete section only

• Django admin: KeyPointerSection.is_deleted = true
• Managerie: delete_key_pointer_sections — always dry-run first, then production, and link managementcommandlog in the incident thread.

B. Delete fields (soft-delete KeyPointer)

After CKP = 0 (or approved plan) and workflow checks:

• Prefer QuerySet.update(is_deleted=True) in a reviewed shell with dry_run over per-row save() in loops.
• On-call may reference scripts/oncall_mitigations/glytec_req_to_delete_metadata_section.py as the shape of the incident-3069 run; prove CKP = 0 in Metabase (or assert in shell) before mutating prod, and get peer review regardless of filename.

C. Verification checklist

• Section: is_deleted = true (Metabase / admin)
• Key pointers: is_deleted = true for target ids
• CKP query re-run spot-check
• Workflow join queries (draft + frozen) if unlink was a concern
• Post managementcommandlog + Metabase question links in Slack

Escalate Instead Of Deleting When

• Non-zero CKP for target key pointers without an approved data plan
• Computed or parent key pointers block bulk delete
• Workflow or frozen questionnaire mapping returns rows and no one signed off blast radius
• Large scope / needs change window
• Ambiguous labels without resolved ids

Environment Mapping

• Prod US: spotdraft-prod.prod_usa_db
• Prod India: spotdraft-prod.prod_india_db
• Prod EU: spotdraft-prod.prod_eu_db
• Prod MEA: spotdraft-prod.prod_mea_db

Prod tables usually carry the public_ prefix.

Related Incidents

Related Skills

• questionnaire-dropdown-global-metadata-mismatch for global metadata option/value mismatches
• fullstory-bulk-delete-metadata-value for deleting contract metadata values rather than metadata definitions