# Issue 1667: Managed Relationship Delete Semantics PRD

Status: Ready for implementation
Date: 2026-06-01
Issue: pingcap-inc/NexusCRM#1667
Parent: pingcap-inc/NexusCRM#1666
Local ID: ENG-B001

## Problem Statement

NexusCRM can silently delete a parent record while child rows still reference
that parent when an effective relationship has neither cascade nor restrict set.
The records runtime treats this ambiguous relationship as "no delete rule", so
the delete guard does not block and the cascade path does not run. The parent
record is deleted or soft-deleted, and child records are left with a stale
foreign-key value.

This is a data-integrity bug. A user or integration deleting a parent record
expects the platform to do one of the declared referential actions: cascade the
children, block the parent delete, or clear the child reference. Returning a
successful delete response while doing none of those makes the record graph
untrustworthy.

The status quo has a second consistency problem. The records delete path and
the Salesforce describe path each derive relationship delete semantics in their
own helper. Both helpers privilege effective relationship rows when present.
Because the metadata service now materializes effective relationship rows from
field metadata, this is not only an explicit managed-relationship edge case:
ordinary lookup fields with no explicit delete rule can also arrive at callers
as a relationship with `cascadeDelete=false` and `restrictedDelete=false`.

## Status Quo

NexusCRM currently has three relationship facts in play:

1. Field metadata contains the reference field, optional `DeleteRule`, and
   master-detail flags.
2. Effective relationship metadata contains child object, parent object, field,
   relationship names, sharing-parent flag, cascade flag, restrict flag, and
   related-list decoration.
3. Physical schema generation can translate field `DeleteRule` into database
   `ON DELETE` behavior, but the records runtime usually soft-deletes records,
   so physical foreign-key behavior is not a complete runtime delete contract.

The metadata service builds an effective relationship snapshot by deriving
base relationships from fields and applying explicit relationship rows as
overlays. Overlay rows can decorate a field-derived edge and can set cascade or
restrict when their boolean is true. Because the effective relationship model
has only `CascadeDelete` and `RestrictedDelete` booleans, it cannot represent
the full three-way delete rule directly. A false/false effective relationship
can mean "no rule was declared", "the lookup is intended to be SetNull", or "an
overlay row exists only for display/list decoration".

The records delete helper currently interprets a non-nil relationship row in
this order:

1. `CascadeDelete=true` means cascade.
2. `RestrictedDelete=true` means restrict.
3. Otherwise return no rule.

The delete guard only blocks when the derived rule equals `Restrict`. An empty
rule therefore bypasses the guard. Later, cascade traversal only cascades when
the helper says cascade. The ambiguous false/false relationship does neither.

The field-only fallback is safer: master-detail cascades, explicit field
`DeleteRule` is honored, and a missing field `DeleteRule` defaults to
`Restrict`. In production, however, the field-only fallback is not reliable as
the only safety net because the metadata service commonly supplies effective
relationships for field-derived lookup edges.

The public describe handler has a parallel helper. For a non-nil relationship
it emits the relationship booleans directly. That means clients can see
`cascadeDelete=false` and `restrictedDelete=false` for the same edge that the
records runtime should treat as restrict when no authored `DeleteRule` exists.

## Salesforce Implementation To Model

Salesforce exposes relationship delete behavior through describe metadata and
record delete behavior.

In the SOAP API Developer Guide, Salesforce documents referential integrity as
an API responsibility: reference IDs are validated on create and update, and
when a parent record is deleted, children are automatically deleted if the
`ChildRelationship.cascadeDelete` property is true. Salesforce also documents
that some cascade attempts fail when a child cannot be deleted or when special
standard-object exceptions apply.

Salesforce's `DescribeSObjectResult.ChildRelationship` contract includes:

1. `cascadeDelete`: true when the child object is deleted when the parent is
   deleted.
2. `restrictedDelete`: true when the parent cannot be deleted because a child
   references it.

Salesforce's Metadata API `CustomField.deleteConstraint` is a separate
authoring contract for lookup relationships. It supports:

1. `Cascade`: delete dependent child records when the referenced parent record
   is deleted.
2. `Restrict`: prevent deletion when the record is in a lookup relationship.
3. `SetNull`: the default; if the lookup record is deleted, clear the lookup
   field.

That means exact Salesforce parity does not simply default all unspecified
lookups to `Restrict`. NexusCRM should fail closed only when no modeled delete
rule exists. When field metadata explicitly declares `DeleteRule=SetNull`, the
records runtime must clear the child lookup during soft delete and describe the
relationship as neither cascade nor restrict, matching the authored
Salesforce-modeled fact.

References:

1. Salesforce SOAP API Developer Guide v63.0:
   `https://resources.docs.salesforce.com/254/latest/en-us/sfdc/pdf/apex_api.pdf`
   sections "Referential Integrity", `delete()`, and `ChildRelationship`.
2. Salesforce Metadata API Developer Guide v63.0:
   `https://resources.docs.salesforce.com/254/latest/en-us/sfdc/pdf/api_meta.pdf`
   section `CustomField.deleteConstraint`.

## Solution

NexusCRM should introduce one generic effective-delete-semantics decision and
make all relationship delete consumers use it.

From the user's perspective, deleting a parent record must produce one of these
outcomes:

1. If the relationship is cascade, child records are deleted through the normal
   records runtime.
2. If the relationship is restrict, the parent delete is rejected when child
   records exist.
3. If no child records exist, the parent delete succeeds.
4. If metadata is ambiguous, NexusCRM treats the relationship as restrict
   rather than allowing a silent orphan.

The decision must be platform-generic. It must not branch on SObject names,
application packs, CRM object types, profiles, record types, or tenant-specific
fields. It reads only the generic field relationship facts and effective
relationship facts.

The practical target is:

1. Restrict wins when the effective relationship explicitly says restrict.
2. Restrict also wins if a malformed relationship somehow says both cascade and
   restrict. This fail-closed precedence avoids deleting data on inconsistent
   metadata and matches the effective relationship overlay's final state.
3. Cascade wins when the effective relationship explicitly says cascade and
   restrict is not true, or when the field is master-detail.
4. If the relationship row is silent, fall back to field-level semantics rather
   than treating the relationship row as complete.
5. If neither the relationship nor the field declares a supported behavior,
   default to restrict.
6. Do not invent a new delete-rule enum or schema column for this issue.

The public describe surface should use the same decision so clients do not see
relationship flags that contradict runtime delete behavior. Under the scoped
NexusCRM default, an ambiguous relationship with no authored delete rule should
describe as `restrictedDelete=true`; an authored `SetNull` relationship should
describe with both `cascadeDelete=false` and `restrictedDelete=false`.

## User Stories

1. As an API user deleting a parent record, I want the platform to reject the
   delete when existing children would otherwise be orphaned, so that my data
   graph stays valid.
2. As an API user deleting a parent record with no children, I want the delete
   to succeed, so that restrict semantics do not block harmless deletes.
3. As an API user deleting a parent record with cascade children, I want the
   children to be deleted through the existing runtime, so that declared
   cascade behavior remains unchanged.
4. As an API user deleting a parent record with explicit restrict children, I
   want the delete to fail with a conflict, so that declared restrict behavior
   remains unchanged.
5. As an integration author, I want ambiguous relationship metadata to fail
   closed, so that metadata omissions do not create silent data loss.
6. As an integration author, I want describe metadata to agree with delete
   behavior, so that I can decide whether deletion is safe before calling the
   delete endpoint.
7. As an admin authoring lookup metadata, I want explicit cascade settings to
   keep working, so that existing pack behavior is not broken.
8. As an admin authoring lookup metadata, I want explicit restrict settings to
   keep working, so that child records continue to protect parent records.
9. As a pack author, I want display-only relationship overlays to avoid erasing
   field-level delete semantics, so that related-list decoration cannot weaken
   referential integrity.
10. As a pack author, I want silent overlay rows to inherit the backing field's
    delete semantics, so that relationship overlays are not treated as a second
    complete source of truth.
11. As a platform maintainer, I want one helper to derive delete semantics, so
    that records runtime and describe runtime do not drift.
12. As a platform maintainer, I want tests at the records delete seam, so that
    future refactors cannot reintroduce successful parent deletes with live
    children.
13. As a platform maintainer, I want tests at the describe seam, so that public
    metadata keeps matching runtime behavior.
14. As a platform maintainer, I want relationship semantics to remain
    multi-app-neutral, so that CRM packs and future Jira-like packs use the
    same substrate.
15. As a platform maintainer, I want physical database `ON DELETE` behavior to
    remain out of the critical path for soft delete, so that runtime behavior
    is explicit and portable.
16. As a test author, I want a direct unit test for the ambiguous false/false
    branch, so that the exact bug is covered even if higher-level fixtures
    change.
17. As a test author, I want a no-children delete test for the same metadata
    shape, so that the fix blocks only real references.
18. As a test author, I want cascade regression coverage, so that the new
    default does not turn cascade relationships into restrict relationships.
19. As a reviewer, I want the Salesforce `SetNull` behavior documented, so that
    nobody mistakes ambiguous relationship booleans for an authored set-null
    rule.
20. As an operator, I want metadata that previously relied on silent orphaning
    to fail visibly, so that pack authors can correct their relationship
    declarations.

## Implementation Decisions

1. Add a single generic relationship-delete-semantics helper in the metadata
   relationship domain, or equivalently move both existing helper call sites to
   one shared helper. The helper accepts field metadata plus the effective
   relationship row for that field-parent pair and returns cascade/restrict
   booleans plus the internal delete rule needed by records runtime.
2. Treat an effective relationship row as an overlay on field-derived
   semantics, not as a complete replacement. Truthy cascade or restrict on the
   relationship row is authoritative; false/false falls through to the backing
   field.
3. Treat master-detail as cascade even if the relationship row is silent.
4. Treat explicit field-level restrict as restrict even if the relationship row
   is silent.
5. Treat explicit field-level cascade as cascade even if the relationship row
   is silent.
6. Treat explicit field-level `SetNull` as set-null: parent delete clears
   matching child lookup fields and describe emits neither cascade nor
   restricted.
7. Treat no declared behavior as restrict. This is the issue's fail-closed
   default and the key data-integrity fix.
8. Use the shared helper in the records delete guard when deciding whether an
   existing child row blocks parent deletion.
9. Use the shared helper in the cascade traversal when deciding whether child
   rows should be recursively deleted.
10. Records delete guard and cascade traversal must consider all relationship
    field types through the same generic relationship-semantics resolver used
    elsewhere in the platform, including `reference`, `masterdetail`, and
    `IsMasterDetail` fields. `constants.IsReferenceType(field.Type)` alone is
    not sufficient when a persisted field has `IsMasterDetail=true`.
11. Use the shared helper in object describe for both field-level relationship
    flags and parent describe `childRelationships` flags.
12. For field-level describe on a multi-target or polymorphic reference, derive
    `cascadeDelete` and `restrictedDelete` from field-level semantics plus all
    effective relationship rows for that field. Emit `restrictedDelete=true`
    if any target would restrict, including an ambiguous target with no
    authored delete rule. Emit `cascadeDelete=true` only when every target
    would cascade and no target would restrict. Authored `SetNull` targets emit
    both booleans false. Parent `childRelationships` remains parent+field-
    specific and uses the helper for that exact parent target.
13. Use the shared helper anywhere delete-time cascade semantics are read,
    including records delete guard, cascade recursion, public describe, and
    rollup delete transaction-boundary selection.
14. Keep the error shape consistent with the existing records delete conflict
    behavior for this issue. Do not expand scope into a broader Salesforce
    REST delete error-code parity project.
15. Do not add SObject-name branches, CRM object exceptions, tenant-specific
    names, profile checks, record-type checks, or app-pack branches.
16. Do not change relationship schema or add a new managed relationship field
    for this issue.
17. Do not move CRM relationship payload into platform code. The fix is generic
    substrate behavior over metadata, not application policy.

## Testing Decisions

Good tests for this work assert observable behavior at the highest practical
seam. They should not assert private branch order except in the direct helper
unit test that locks the ambiguity fix.

Test seams:

1. Relationship semantics unit seam: directly verify the shared helper for
   cascade, explicit restrict, explicit set-null, master-detail, explicit field
   cascade, explicit field restrict, both-booleans-true fail-closed restrict,
   and no-declared-rule default restrict.
2. Records delete seam: create parent and child metadata with an effective
   relationship where cascade and restrict are both false, create an existing
   child row, delete the parent, and assert conflict rather than success.
3. Records delete no-child seam: use the same metadata shape with no matching
   child row and assert the parent delete succeeds.
4. Records delete cascade seam: use a cascade relationship and assert the
   existing cascade behavior still runs.
5. Describe seam: describe the parent and child objects for the ambiguous,
   explicit SetNull, master-detail, and polymorphic/multi-target relationship
   cases and assert the emitted relationship flags match runtime behavior.
6. Metadata overlay seam: verify a display/list-only relationship overlay does
   not erase a backing field's explicit delete rule.
7. Traversal seam: verify records delete guard and cascade traversal both visit
   master-detail relationship fields, not only literal `reference` fields.

Prior art:

1. Effective relationship resolver tests already cover field-derived
   relationships plus explicit overlays.
2. Metadata service relationship lookup tests cover effective relationship
   snapshots and child relationship discovery.
3. Records rollup transaction boundary tests provide examples of delete tests
   through the records service.
4. SObject describe tests provide examples of validating Salesforce-shaped
   describe output.
5. Bootstrap cascade tests provide regression coverage for standard-action
   cascade behavior.

Verification commands for the eventual implementation should include focused
   Go tests for metadata relationship, records, REST describe, and the existing
   cascade regression suite. Full backend tests should run before merge when
   practical.

## Out of Scope

1. A new relationship delete-rule schema model.
2. Backfilling or repairing already-orphaned rows created before this fix.
3. Migration or correction of installed pack metadata.
4. A broader Salesforce REST delete error-envelope parity pass.
5. Any SObject-specific standard-object exception, such as Salesforce's special
   Account, Case, Opportunity, Contract, Quote, or Order behavior.
6. Tenant-specific cleanup of already-orphaned data.
7. Physical database foreign-key redesign.
8. Changing delete behavior for records that are already protected by explicit
   cascade or explicit restrict declarations.

## Further Notes

Salesforce parity is nuanced here. Salesforce's metadata authoring default for
lookup delete constraints is `SetNull`, but NexusCRM must still distinguish an
authored `SetNull` rule from a missing rule. The immediate product requirement
is therefore: never silently orphan children when metadata is ambiguous, and
honor explicit Salesforce-modeled delete rules when metadata is present.

The issue should be framed as a referential-integrity hardening fix. A follow-up
design can still decide whether `_System_Relationship` needs a first-class enum
for delete constraints; this PR keeps `FieldMetadata.DeleteRule` as the source
of truth for `SetNull` rather than overloading false/false relationship
booleans.