---
apiVersion: data-spec/v1
id: opportunitycontactrole-derived-permission-cleanup-20260426
title: "Profile convergence — one-time cleanup: remove obsolete OpportunityContactRole self permission rows"
description: "Delete legacy ObjectPermissions / FieldPermissions rows whose SobjectType is OpportunityContactRole now that OCR access is owned by derived AccessPolicy metadata instead of a self permission surface. Applies only after the OCR derived-policy binary is fully deployed. Natural no-op when OCR is absent, OCR is not yet derived, or no residual OCR self rows exist."
issue: "#1970"
created: 2026-04-26
scope: both
status: pending
tables_modified:
  - ObjectPermissions
  - FieldPermissions
tables: [_System_Object, ObjectPermissions, FieldPermissions, PermissionSet]
idempotent: true
risk: low
reviewers: ["@codeowners"]
depends_on: []
blocks: []
---

# Profile convergence — one-time cleanup: remove obsolete OpportunityContactRole self permission rows

## Summary

`OpportunityContactRole` now owns its access surface through
`_System_Object.AccessPolicy` with `mode = derived`:

- object read = `Opportunity.Read AND Contact.Read`
- object create/edit = `Opportunity.Edit AND Contact.Read`
- field read/edit inherit from the same composite gate
- row-level visibility still follows `ControlledByParent -> Opportunity`

After that cutover, any self `ObjectPermissions` / `FieldPermissions` rows for
`OpportunityContactRole` become dead state:

- runtime no longer reads them
- seeder no longer recreates them
- profile verify no longer expects them

Leaving the rows in place keeps stale permission surfaces in storage that no
live reader treats as authoritative. This spec removes those obsolete rows once,
in data-spec space, instead of adding another long-lived runtime cleanup branch.

## Precondition

- The binary that introduces OCR derived access policy MUST already be deployed
  to 100% of backend pods before this spec runs.
- In that binary:
  - `OpportunityContactRole` is present in `_System_Object`
  - `_System_Object.AccessPolicy.mode = 'derived'` for OCR
  - runtime / seed / repair / verify already treat OCR as derived

Applying this spec before the binary is live is unsafe because an older binary
could still treat OCR self permission rows as authoritative.

## Rollback

This spec is destructive (`DELETE`). Rollback is restore-from-backup:

1. The applier must back up matching `ObjectPermissions` and `FieldPermissions`
   rows before apply because this spec has `tables_modified`.
2. If recovery is needed, restore those deleted rows from the generated backup.
3. Do not restore the rows unless the runtime binary is also reverted, because
   the current derived-policy runtime intentionally does not read them.

## Preflight — prove OCR is currently derived

```sql
SELECT COUNT(*) AS derived_ocr_objects
FROM `_System_Object`
WHERE `IsDeleted` = 0
  AND `ApiName` = 'OpportunityContactRole'
  AND COALESCE(LOWER(JSON_UNQUOTE(JSON_EXTRACT(`AccessPolicy`, '$.mode'))), '') = 'derived';
```

Preflight result handling:

- `derived_ocr_objects = 0`
  - no-op
  - mark applied with status note:
    `no-op: OpportunityContactRole not present as a derived object on this cluster`
- `derived_ocr_objects = 1`
  - proceed to residual-row counts below

```sql
SELECT 'ObjectPermissions' AS surface, COUNT(*) AS residual_rows
FROM `ObjectPermissions`
WHERE `IsDeleted` = 0
  AND `SobjectType` = 'OpportunityContactRole'
UNION ALL
SELECT 'FieldPermissions' AS surface, COUNT(*) AS residual_rows
FROM `FieldPermissions`
WHERE `IsDeleted` = 0
  AND `SobjectType` = 'OpportunityContactRole';
```

If both counts are `0`, this spec is a natural no-op. Mark applied with status
note:

`no-op: no residual OpportunityContactRole self permission rows on this cluster`

## Changes

### Step 1. Delete obsolete OCR self ObjectPermissions rows

```sql
DELETE op
FROM `ObjectPermissions` op
WHERE op.`IsDeleted` = 0
  AND op.`SobjectType` = 'OpportunityContactRole';
```

Why this is safe:

- OCR object CRUD is now derived from `Opportunity` + `Contact`, not from
  `ObjectPermissions(SobjectType='OpportunityContactRole')`
- profile verify excludes derived objects from expected self object rows
- re-running after the first apply matches 0 rows

### Step 2. Delete obsolete OCR self FieldPermissions rows

```sql
DELETE fp
FROM `FieldPermissions` fp
WHERE fp.`IsDeleted` = 0
  AND fp.`SobjectType` = 'OpportunityContactRole';
```

Why this is safe:

- OCR field access is now derived from the same metadata-authored composite gate
- profile verify excludes derived objects from expected self field rows
- re-running after the first apply matches 0 rows

## Post-apply verification

```sql
SELECT COUNT(*) AS residual_object_rows
FROM `ObjectPermissions`
WHERE `IsDeleted` = 0
  AND `SobjectType` = 'OpportunityContactRole';
-- Expect: 0

SELECT COUNT(*) AS residual_field_rows
FROM `FieldPermissions`
WHERE `IsDeleted` = 0
  AND `SobjectType` = 'OpportunityContactRole';
-- Expect: 0
```

Optional profile-convergence verification after apply:

```bash
cd backend
go run ./cmd/setup verify-profile
```

Expected result:

- profile-specific verification passes
- no OCR self-row expectation remains in the profile coverage signal

## Notes

- This spec intentionally cleans only `OpportunityContactRole`.
- `CaseComment` remains out of scope for this spec and for the current
  execution-ready stage of `#1970`.
- Any future derived object should get its own cleanup decision, not silently
  piggyback on this one-shot OCR spec.