Bulk Delete Contract Metadata Values

When NOT to use

• Single-contract metadata edits.
• A request to delete the key pointer definition itself from the workspace.
• Bulk metadata update/import work where the values should be changed, not cleared.
• Incidents unrelated to ContractKeyPointer state.

Key identifiers

Known Fullstory example values:

• workspace_id = 41811
• cluster = US
• key_pointer_id = 101111
• Field: Salesforce Opportunity ID
• Section: Contract Information
• Contract type: Order Form

Investigation workflow

1. Clarify the intended mutation

Before touching data, separate these three operations:

1. Clear one field's value from matching CKP rows.
2. Delete the key pointer definition / config from the workspace.
3. Delete CKP rows from contracts.

Do not treat them as interchangeable.

In the Fullstory case, the intended operation was to clear only Salesforce Opportunity ID values, not to delete all CKPs and not to delete the key pointer definition.

Also verify that support and engineering are aligned on the exact field. In the same Fullstory thread, the target field was initially described as Salesforce Account ID and later corrected to Salesforce Opportunity ID.

2. Validate the sheet semantics before any dry run

Confirm all of the following from the latest approved sheet or Slack instruction:

• Which column contains contract IDs.
• Which column contains the target field or reference value.
• Whether the sheet is already filtered on a condition such as "Opportunity ID is not empty".
• Whether duplicates exist.
• Whether the task applies to all contract types or only a subset.

This is the failure pattern that caused the incident:

• The final sheet had 6645 contracts listed.
• Only 3408 rows actually had the relevant Opportunity ID populated and were intended to be cleared.
• The live script was run against all contracts listed in the sheet.

If the counts in the sheet and the count of intended target rows are materially different, stop and resolve that discrepancy first.

3. Check whether the key pointer definition itself is broken

If the customer says the field is missing entirely, not just its value, check whether the key pointer definition was deleted or hidden.

Useful query shape from the Fullstory investigation:

SELECT
  kp.id AS key_pointer_id,
  kp.label,
  kp.is_deleted,
  kp.modified AS kp_modified,
  kp.modified_by_id AS modified_by_user_id,
  u.username AS modified_by_username,
  u.email AS modified_by_email,
  ou.id AS modified_by_org_user_id
FROM historic_contracts_keypointer kp
LEFT JOIN auth_user u ON u.id = kp.modified_by_id
LEFT JOIN sd_organizations_organizationuser ou ON ou.user_id = kp.modified_by_id
WHERE kp.id = {key_pointer_id}
  AND kp.created_by_workspace_id = {workspace_id};

In the Fullstory workspace, key_pointer_id = 101111 was also found with is_deleted = true, and that had to be restored separately.

4. Dry run the exact one-field scope

For planned deletion of one field, the queryset must be scoped by:

• contract_id__in={contract_ids}
• created_by_workspace={workspace_id}
• key_pointer_id={key_pointer_id} or key_pointer__name={key_pointer_name}

Do not run an unscoped queryset that only filters by contract list and workspace.

Safe Django shell pattern:

from contracts_v3.models import ContractKeyPointer

TARGET_KEY_POINTER_ID = {key_pointer_id}

contract_key_pointers = ContractKeyPointer.objects.filter(
    contract_id__in=CONTRACT_IDS,
    created_by_workspace=WORKSPACE_ID,
    key_pointer_id=TARGET_KEY_POINTER_ID,
)

print(contract_key_pointers.count())

The dry-run count should roughly match the number of rows you actually intend to clear, not the total CKP footprint of the listed contracts.

5. Prefer value-clear over row delete for one-field cleanup

If the ask is "remove this field's value", prefer clearing only the targeted field's value rather than deleting CKP rows for every field.

The earlier Fullstory investigation proposed this safer pattern:

from contracts_v3.models import ContractKeyPointer

TARGET_KEY_POINTER_ID = {key_pointer_id}

def execute(dry_run=True):
    contract_key_pointers = ContractKeyPointer.objects.filter(
        contract_id__in=CONTRACT_IDS,
        created_by_workspace=WORKSPACE_ID,
        key_pointer_id=TARGET_KEY_POINTER_ID,
    )
    pointers_to_update = list(contract_key_pointers)

    for ckp in pointers_to_update:
        ckp.value = ""
        ckp.value_source_metadata = None

    if not dry_run:
        ContractKeyPointer.objects.bulk_update(
            pointers_to_update,
            ["value", "value_source_metadata"],
        )

After the mutation, enqueue search resync for the affected contracts:

from contracts_v3.search_v2.domain.constants import KEY_POINTER_DERIVED_SEARCH_FIELDS
from contracts_v3.search_v2.tasks import partially_resync_contracts_task

partially_resync_contracts_task.delay(
    contract_ids=CONTRACT_IDS,
    fields=KEY_POINTER_DERIVED_SEARCH_FIELDS,
)

6. Treat the bundled full_story_bulk_delete_metadata_values.py as an incident artifact

The file in this skill directory:

• fullstory-bulk-delete-metadata-value/full_story_bulk_delete_metadata_values.py

is not a safe default.

It batches over contract IDs and executes:

ContractKeyPointer.objects.filter(
    contract_id__in=batch_contract_ids,
    created_by_workspace=WORKSPACE_ID,
).delete()

That query is missing any filter on key_pointer_id / key_pointer__name, so it deletes all CKPs for those contracts in the workspace.

Only reuse that file after adding the exact target-field filter and re-verifying the dry-run count.

7. Detect accidental mass deletion quickly

Suspect a CKP wipe, not a one-field clear, if any of these are true:

• Multiple metadata fields disappear, not just the requested one.
• The customer says there is no associated activity log for metadata that used to be present.
• The live script output shows counts far larger than the expected target field row count.
• Search resync ran for every listed contract immediately after the operation.

Fullstory blast-radius indicators:

• Total key pointers deleted: 113393
• Elasticsearch resync triggered for 6645 contracts (async)

Those numbers are incompatible with "clear one metadata field from 3408 rows".

8. Recovery workflow for accidental CKP deletion

A. Restore the correct cluster from backup

• Identify the earliest confirmed destructive run timestamp.
• Restore a replica / side instance, not the production instance itself.
• Make sure the restore target matches the workspace cluster.

Known failure pattern from the incident:

• Earliest confirmed delete batch: 2026-03-19T06:28:06.625530 UTC.
• The first restore attempt used an IN restore instance for a US workspace and returned no results.
• The correct restore target was the US instance (prod-us-18-03-2026), restored from before the destructive run.

B. Export CKPs for affected contracts from the restored DB

Use the restored instance to dump CKP rows for the affected contracts. Reconcile the export against:

• the final contract list,
• the intended target field,
• and the destructive-run totals.

In the Fullstory incident, the restored query returned 15372 rows from backup for the affected contracts, and the team used batched exports because the SQL studio response was too large.

C. Recreate all CKPs except the field that was intentionally meant to stay deleted

This is the most reusable lesson from the incident.

Because the original business ask was to delete only Salesforce Opportunity ID, the restore script intentionally recreated every other CKP from backup and skipped key_pointer_id = 101111.

Adapted restore pattern:

from contracts_v3.models import ContractKeyPointer
from contracts_v3.search_v2.domain.constants import KEY_POINTER_DERIVED_SEARCH_FIELDS
from contracts_v3.search_v2.tasks import partially_resync_contracts_task
import json

INTENTIONALLY_DELETED_KEY_POINTER_ID = {key_pointer_id}

def create_ckps_from_json_file(path):
    with open(path, "r") as f:
        ckps_from_backup = json.load(f)

    ckps_to_restore = [
        ckp
        for ckp in ckps_from_backup
        if ckp["key_pointer_id"] != INTENTIONALLY_DELETED_KEY_POINTER_ID
    ]

    rows = [
        ContractKeyPointer(
            contract_id=ckp["contract_id"],
            key_pointer_id=ckp["key_pointer_id"],
            value=ckp["value"],
            created_by_workspace=ckp["created_by_workspace"],
            created_by_id=ckp["created_by_id"],
            created_by_org_user_id=ckp["created_by_org_user_id"],
            was_edited_by_user=ckp["was_edited_by_user"],
            was_edited_by_system=ckp["was_edited_by_system"],
            value_source_metadata=ckp["value_source_metadata"],
            source_non_validated_value=ckp["source_non_validated_value"],
            source_datatype=ckp["source_datatype"],
        )
        for ckp in ckps_to_restore
    ]

    ContractKeyPointer.objects.bulk_create(rows, batch_size=1000)

    contract_ids = sorted({ckp["contract_id"] for ckp in ckps_to_restore})
    partially_resync_contracts_task.delay(
        contract_ids,
        fields=KEY_POINTER_DERIVED_SEARCH_FIELDS,
    )

You can also use the admin utility:

• https://api.us.spotdraft.com/admin/contracts_v3/bulkimportcontractkeypointerstask/

if it is operationally easier than a custom script.

9. Verify recovery in the right order

1. Test the restore on one contract first.
2. Verify the restored values in DB and platform.
3. Run the restore for the full set.
4. Confirm resync has run for all affected contracts.
5. Ask support / CS to validate the customer-facing view.

The Fullstory team first verified a single contract (1182037) before restoring the remaining contracts the next day during non-US hours.

10. If reports show the field but validator / metadata UI does not

Check the key pointer definition and pointer config again.

This happened in the same Fullstory workspace:

• The KP was restored and visible in reports.
• It still did not appear correctly in the validator / metadata UI.
• The working theory in Slack was that pointer config also needed correction once the non-global-metadata workspace was restored.

So if DB values look correct but the product UI is still inconsistent, do not assume the restore is complete.

Useful code paths and commands

Important guardrails

• Always confirm the final sheet and the actual filtered target set.
• Always dry run with the exact target field filter.
• Never use "delete all CKPs for these contracts" to implement "clear one metadata field".
• Never reuse the bundled Fullstory script without adding the missing field filter.
• Restore from the correct cluster and from a timestamp before the destructive run.
• If one field was intentionally supposed to remain deleted, exclude that field from restore.

Related incidents and references

• Fullstory planned delete task: Rootly incident 3007, Jira SPD-42475.
• Fullstory follow-up outage after accidental CKP deletion: Rootly incident 3038, Jira SPD-42734.
• Same workspace: a separate earlier issue also showed Salesforce Opportunity ID key pointer definition 101111 marked is_deleted=true, which is a useful adjacent check when values appear restored but the field is still missing in product surfaces.

Connections to other skills

• contract-lookup for fast contract / workspace verification before and after restore.
• Search/index-related skills if metadata remains stale after CKP recovery and resync.