API Design Knowledge: Relations, Cascade & Deletion Strategies

Overview

This document captures architectural knowledge for two of the most commonly mishandled areas in API design involving relational data:

1. Junction Table Management — should ManyToMany pivot tables use hard or soft delete?
2. Cascade API Design — how should a PATCH endpoint interpret and handle child (OneToMany) data?

These decisions affect data integrity, query complexity, ORM compatibility, and auditability. Understanding the trade-offs prevents subtle bugs that are hard to trace in production.

---

Junction Table Management

What is a Junction Table?

A junction table (also called a pivot or bridge table) represents a Many-to-Many relationship between two entities. It typically holds only the two foreign keys forming a composite primary key.

-- Pure junction table
CREATE TABLE orders_tags (
    order_id UUID NOT NULL REFERENCES orders(id),
    tag_id   UUID NOT NULL REFERENCES tags(id),
    PRIMARY KEY (order_id, tag_id)
);

Should Junction Tables Use Hard Delete or Soft Delete?

Short answer: Hard Delete is the correct default for pure junction tables.

Reason 1 — Composite Primary Key Constraint Violation

Junction tables use a composite PK to guarantee uniqueness. With soft delete:

1. Assign tag: INSERT (order_id=1, tag_id=10)       → OK
2. Remove tag: UPDATE SET is_deleted=true            → "deleted" but row remains
3. Re-assign tag: INSERT (order_id=1, tag_id=10)    → ❌ DUPLICATE KEY ERROR

The fix requires checking for a soft-deleted row before deciding to INSERT or UPDATE — adding unnecessary complexity.

With hard delete:

1. Assign: INSERT (order_id=1, tag_id=10)          → OK
2. Remove: DELETE WHERE (order_id=1, tag_id=10)    → row gone
3. Re-assign: INSERT (order_id=1, tag_id=10)       → OK, no conflict

Reason 2 — Query Complexity

Every query joining a soft-deleted junction table must filter out deleted rows:

-- Every JOIN requires this extra condition
SELECT * FROM orders o
JOIN orders_tags ot ON ot.order_id = o.id AND ot.is_deleted = false
JOIN tags t ON t.id = ot.tag_id

Miss a single AND ot.is_deleted = false and deleted associations reappear as valid data — a silent bug.

With hard delete, the join is always clean:

SELECT * FROM orders o
JOIN orders_tags ot ON ot.order_id = o.id
JOIN tags t ON t.id = ot.tag_id

Reason 3 — ORM Default Behaviour

TypeORM’s built-in Many-to-Many tools (addAndRemove(), cascade save) operate with hard delete on the junction table. Fighting this default requires workarounds that break standard ORM usage patterns.

---

When Should a Junction Table Use Soft Delete?

Use soft delete only when the junction table has evolved into an entity with its own business data:

-- This is no longer a pure junction table — it's an Assignable entity
CREATE TABLE employee_projects (
    employee_id       UUID NOT NULL,
    project_id        UUID NOT NULL,
    assigned_by       UUID,          -- extra business data
    assigned_date     DATE,          -- extra business data
    unassigned_reason TEXT,          -- extra business data
    is_deleted        BOOLEAN DEFAULT false,
    deleted_at        TIMESTAMPTZ
);

When a table carries columns beyond the two FKs, it has transitioned from a junction table to a domain entity that deserves soft-delete treatment for audit purposes.

Decision Rule

Does the junction table have columns beyond the two foreign keys?
│
├─ NO  → Pure junction table → Hard Delete ✅
│
└─ YES → Has business data → Soft Delete ✅
         (treat it as a domain entity, not a pivot)

Implementation in this Codebase

_syncManyToManyRelations() follows this rule by using TypeORM’s addAndRemove(), which performs hard delete on junction rows. The method also accounts for soft-deleted records on the related entity side (not the junction itself) to avoid re-adding associations to deleted records:

const hasSoftDelete = inverseEntity.columns.some((c) => c.propertyName === 'is_deleted');

const currentIds = hasSoftDelete
    ? rawItems.filter((item) => item.is_deleted !== true).map((i) => i.id)
    : rawItems.map((i) => i.id);

---

Cascade API Design

The Two-Dimension Framework

When a PATCH endpoint receives a payload containing child records, the system must answer two questions independently:

Dimension 1: Should orphans be deleted?
     ↓
Dimension 2: How should they be deleted?

Dimension 1 — Interpreting the Payload (Delete or Keep?)

REST PATCH semantics distinguish between two client intents:

Omitted key → Keep existing children

PATCH /orders/:id
{ "name": "Updated Order Name" }

The client said nothing about items. The correct interpretation is “don’t touch my children” — this is a partial update. The absence of a key must not trigger deletion.

Client intent: "Update only the name. Leave everything else as-is."
System action: No orphan logic. All children remain in DB.

Explicit empty array → Delete all children

PATCH /orders/:id
{ "items": [] }

The client deliberately sent an empty array. This is an explicit statement of intent — “I want zero items for this order.” The system must honour this by removing all existing children.

Client intent: "Clear all items."
System action: Apply orphan deletion strategy to all existing children.

Array with items → Sync to desired state

PATCH /orders/:id
{ "items": [{ "id": "item-A", "is_primary": true }] }

The client specified the complete desired set. Children present in DB but absent from the payload are orphans that must be handled.

Client intent: "The final state should have only item-A."
System action: Update item-A, handle item-B (orphan) per deletion strategy.

Dimension 2 — Hard Delete vs Soft Delete for Orphans

Once the system determines that children must be removed, the deletion strategy depends on the nature of the child entity:

Use Hard Delete when	Use Soft Delete when
Child is a value object (meaningless without parent)	Child has independent business value
No audit or history requirements	Needed for audit trails or compliance
No foreign keys pointing to this child from other tables	Other tables reference this child by FK
Re-creation is safe (idempotent)	Historical record must be preserved
Examples: gallery images, temporary tags	Examples: order items, assignment logs, approval records

Default Strategy in this Codebase

IUpdateOptions encodes the safe default:

export class IUpdateOptions {
    hardDeleteOneToMany?: boolean = false;  // destructive — opt-in only
    softDeleteOneToMany?: boolean = true;   // safe default — preserves history
}

Note: If the children key is omitted from the DTO, orphan handling doesn’t run at all — this is the default safe behavior. Omitted keys already achieve this via REST PATCH semantics.

softDeleteOneToMany = true by default because most child entities in an enterprise system (orders, assignments, audit logs) carry business value that must not be permanently erased.

---

Full Decision Tree

PATCH request received with child data in payload
│
├── Child key OMITTED from payload?
│   └── YES → No orphan handling. Children untouched. (REST PATCH semantics)
│       (Example: { "name": "New Name" } — items key not present)
│
└── Child key PRESENT in payload?
    │
    ├── Is it an empty array?
    │   └── YES → All existing children must be deleted
    │           (Explicit client intent: "clear all children")
    │
    └── Is it an array with items?
        │
        └── YES → Find orphans (in DB but not in array)
            │
            ├── Are there orphans?
            │   │
            │   └── YES → Choose deletion strategy based on child entity type:
            │       │
            │       ├── Is child a pure value object (no business meaning)?
            │       │   └── YES → Hard Delete ✅
            │       │
            │       └── Does child have business value / FK references?
            │           └── YES → Soft Delete ✅ (preserves history)
            │
            └── No orphans → All children updated, transaction succeeds

---

Practical Examples from this Codebase

Order — Line Items (OneToMany, Soft Delete)

// OrderItem has business value — soft delete is correct
await this.orderService.update(id, {
    items: [{ id: 'item-A', is_primary: true }]
}, currentUser);
// item-B → is_deleted=true, deleted_reason='delete with cascade'

Order — Tags (ManyToMany, Hard Delete)

// Junction table: orders_tags (only 2 FK columns)
// addAndRemove() performs hard delete on junction rows automatically
await this.orderService.update(id, {
    tags: [{ id: 'tag-priority' }]
}, currentUser);
// Removed tag junction rows are permanently deleted

Employee — Projects (Junction with Business Data, Soft Delete)

// employee_projects has assigned_by, assigned_date, unassigned_reason
// This is a domain entity — soft delete preserves assignment history
await this.employeeService.update(id, {
    projects: [{ id: 'proj-A' }]
}, currentUser, { softDeleteOneToMany: true });

---

Summary

Scenario	Strategy	Reason
Pure junction table (M2M pivot)	Hard Delete	Avoids duplicate key; clean queries; ORM default
Junction table with business columns	Soft Delete	It is a domain entity with audit value
OneToMany child — value object	Hard Delete	No historical significance
OneToMany child — business entity	Soft Delete	Preserves audit trail and FK integrity
Client omits children key	No-op	REST PATCH: omitted = untouched
Client sends empty array []	Delete all	Explicit client intent to clear
Client sends partial array	Delete orphans	Sync to desired final state