Delete Operations (DELETE)

Overview

DELETE operations remove records through two modes: soft delete (default, reversible) and force delete (permanent, irreversible). CoreService routes DELETE requests to CoreWrite for validation and execution.

Endpoint:

DELETE /api/v4/core/{dim}/{dim_id}
DELETE /api/v4/core/{dim}/{dim_id}?force=true
Authorization: Bearer {token}

Two Deletion Modes:

Soft Delete (default): Sets TREC='C', record remains in database
Force Delete: Physically removes record from database

Soft Delete (Default)

Behavior

Default DELETE is soft (reversible):

Request:

DELETE /api/v4/core/PRD/123
Authorization: Bearer eyJhbGc...

Database Action:

-- Soft delete: UPDATE, not DELETE
UPDATE TB_ANAG_PRD00
SET TREC = 'C',
    LDATA = '20251219160000',
    LOWNER = 'admin@example.com'
WHERE PRD_ID = 123;

Response:

{
  "status": "success",
  "message": "Record soft deleted",
  "data": {
    "PRD_ID": 123,
    "XPRD01": "Widget Pro",
    "TREC": "C",  // Changed to 'C' (Cancelled)
    "LDATA": "20251219160000",  // Last modification timestamp
    "LOWNER": "admin@example.com"  // Who deleted it
  }
}

Benefits:

✅ Reversible (can restore)
✅ Maintains referential integrity
✅ Preserves audit trail
✅ "Trash" functionality possible

TREC State Transition

TREC='N' or TREC='M'
    ↓ DELETE (soft)
TREC='C' (Cancelled)

Audit Fields:

TREC: Changed to 'C'
LDATA: Set to deletion timestamp
LOWNER: Set to user who deleted
CDATA/OWNER: Preserved (original creation info)

Query Behavior After Soft Delete

Default queries exclude TREC='C':

# This won't return soft-deleted products
GET /api/v4/core/PRD?source=productList

To include soft-deleted records:

# Show deleted records
GET /api/v4/core/PRD?source=productList&cond_trec=C

# Show all records (including deleted)
GET /api/v4/core/PRD?source=productList&cond_trec=N,M,C

Force Delete (Permanent)

Behavior

Force delete physically removes record:

Request:

DELETE /api/v4/core/PRD/123?force=true
Authorization: Bearer eyJhbGc...

Database Action:

-- Force delete: Physical DELETE
DELETE FROM TB_ANAG_PRD00
WHERE PRD_ID = 123;

Response:

{
  "status": "success",
  "message": "Record permanently deleted"
}

⚠️ Warning: Force delete is irreversible. Use with extreme caution.

When to Use Force Delete

Rarely needed:

GDPR "right to be forgotten" compliance
Test data cleanup
Removing invalid/corrupted records
Database maintenance

Usually avoid:

Regular deletion (use soft delete instead)
User-initiated deletes (offer restore functionality)
Production data (maintain audit trail)

Restore (Undelete)

Restore Soft-Deleted Record

Soft-deleted records can be restored via PATCH:

# Restore by setting TREC='M'
PATCH /api/v4/core/PRD/123
{
  "data": {
    "TREC": "M"  // Or "N" depending on use case
  }
}

Result:

UPDATE TB_ANAG_PRD00
SET TREC = 'M',
    LDATA = '20251219170000',
    LOWNER = 'admin@example.com'
WHERE PRD_ID = 123 AND TREC = 'C';

DELETE_CASCADE Operations

Soft Delete Cascades

TB_COST.DELETE_CASCADE triggers related deletions:

-- When order is soft-deleted, soft-delete all items
INSERT INTO TB_COST (COD_DIM, NUM_COST, COD_VAR, DELETE_CASCADE) VALUES
('ORD', 10, 'items', 'ORDITEM:XORDITEM_ORDER_ID:soft');

Single Request Cascades to Related Records:

Request:

DELETE /api/v4/core/ORD/123

Result:

-- Soft delete order
UPDATE TB_ANAG_ORD00
SET TREC = 'C', LDATA = '20251219160000'
WHERE ORD_ID = 123;

-- Cascade: Soft delete all order items
UPDATE TB_ANAG_ORDITEM00
SET TREC = 'C', LDATA = '20251219160000'
WHERE XORDITEM_ORDER_ID = 123;

Force Delete Cascades

Force delete can cascade permanently:

-- When order is force-deleted, force-delete all items
INSERT INTO TB_COST (COD_DIM, NUM_COST, COD_VAR, DELETE_CASCADE) VALUES
('ORD', 10, 'items', 'ORDITEM:XORDITEM_ORDER_ID:force');

Request:

DELETE /api/v4/core/ORD/123?force=true

Result:

-- Force delete order
DELETE FROM TB_ANAG_ORD00
WHERE ORD_ID = 123;

-- Cascade: Force delete all order items
DELETE FROM TB_ANAG_ORDITEM00
WHERE XORDITEM_ORDER_ID = 123;

⚠️ Danger: Force cascades are irreversible for all related records.

Outbox Event

Delete Event Publication

Every DELETE publishes event to RabbitMQ:

Soft Delete Event:

{
  "event_id": "evt_770g0622",
  "event_type": "ProductDeleted",
  "aggregate_id": "123",
  "aggregate_type": "PRD",
  "operation": "DELETE",
  "deletion_type": "soft",
  "timestamp": "2025-12-19T16:00:00Z",
  "payload": {
    "PRD_ID": 123,
    "XPRD01": "Widget Pro",
    "TREC": "C"
  },
  "user": "admin@example.com"
}

Force Delete Event:

{
  "event_id": "evt_880h1733",
  "event_type": "ProductForceDeleted",
  "aggregate_id": "123",
  "aggregate_type": "PRD",
  "operation": "DELETE",
  "deletion_type": "force",
  "timestamp": "2025-12-19T17:00:00Z",
  "payload": {
    "PRD_ID": 123,
    "XPRD01": "Widget Pro"
  },
  "user": "admin@example.com"
}

JavaScript Implementation

Delete Service

class ProductService {
  constructor(apiBase, token) {
    this.apiBase = apiBase;
    this.token = token;
  }

  /**
   * Soft delete (default)
   */
  async deleteProduct(productId) {
    const response = await fetch(`${this.apiBase}/api/v4/core/PRD/${productId}`, {
      method: 'DELETE',
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    if (!response.ok) {
      const error = await response.json();
      throw new ProductDeleteError(error);
    }

    return response.json();
  }

  /**
   * Force delete (permanent)
   */
  async forceDeleteProduct(productId) {
    const url = new URL(`${this.apiBase}/api/v4/core/PRD/${productId}`);
    url.searchParams.append('force', 'true');

    const response = await fetch(url, {
      method: 'DELETE',
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    if (!response.ok) {
      const error = await response.json();
      throw new ProductDeleteError(error);
    }

    return response.json();
  }

  /**
   * Restore soft-deleted product
   */
  async restoreProduct(productId) {
    const response = await fetch(`${this.apiBase}/api/v4/core/PRD/${productId}`, {
      method: 'PATCH',
      headers: {
        'Authorization': `Bearer ${this.token}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        data: { TREC: 'M' }
      })
    });

    if (!response.ok) {
      const error = await response.json();
      throw new ProductRestoreError(error);
    }

    return response.json();
  }

  /**
   * Check if product is soft-deleted
   */
  async isDeleted(productId) {
    const url = new URL(`${this.apiBase}/api/v4/core/PRD/${productId}`);
    url.searchParams.append('cond_trec', 'N,M,C');  // Include deleted

    const response = await fetch(url, {
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    const result = await response.json();
    return result.data?.TREC === 'C';
  }
}

class ProductDeleteError extends Error {
  constructor(errorResponse) {
    super(errorResponse.message);
    this.code = errorResponse.code;
  }
}

class ProductRestoreError extends Error {
  constructor(errorResponse) {
    super(errorResponse.message);
    this.code = errorResponse.code;
  }
}

// Usage
const productService = new ProductService(apiBase, token);

// Soft delete
await productService.deleteProduct(123);

// Restore
await productService.restoreProduct(123);

// Force delete (with confirmation)
if (confirm('Permanently delete? This cannot be undone!')) {
  await productService.forceDeleteProduct(123);
}

Trash Management

class TrashService {
  constructor(apiBase, token, dimension) {
    this.apiBase = apiBase;
    this.token = token;
    this.dimension = dimension;
  }

  /**
   * Get trash (soft-deleted records)
   */
  async getTrash(options = {}) {
    const url = new URL(`${this.apiBase}/api/v4/core/${this.dimension}`);
    url.searchParams.append('source', options.source || 'list');
    url.searchParams.append('cond_trec', 'C');  // Only soft-deleted
    url.searchParams.append('$order', 'LDATA DESC');  // Recently deleted first
    url.searchParams.append('$num_rows', options.limit || 25);
    url.searchParams.append('$offset', options.offset || 0);

    const response = await fetch(url, {
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    return response.json();
  }

  /**
   * Empty trash (force delete all soft-deleted records)
   */
  async emptyTrash() {
    // 1. Get all soft-deleted records
    const trash = await this.getTrash({ limit: 1000 });

    // 2. Force delete each
    const deletions = trash.data.map(record =>
      fetch(`${this.apiBase}/api/v4/core/${this.dimension}/${record[`${this.dimension}_ID`]}?force=true`, {
        method: 'DELETE',
        headers: { 'Authorization': `Bearer ${this.token}` }
      })
    );

    return Promise.all(deletions);
  }

  /**
   * Restore from trash
   */
  async restore(recordId) {
    const response = await fetch(`${this.apiBase}/api/v4/core/${this.dimension}/${recordId}`, {
      method: 'PATCH',
      headers: {
        'Authorization': `Bearer ${this.token}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        data: { TREC: 'M' }
      })
    });

    return response.json();
  }
}

// Usage
const trash = new TrashService(apiBase, token, 'PRD');

// Get trash
const deleted = await trash.getTrash();
console.log(`${deleted.data.length} items in trash`);

// Restore item
await trash.restore(123);

// Empty trash (with confirmation)
if (confirm('Permanently delete all items in trash?')) {
  await trash.emptyTrash();
}

Common Patterns

Pattern 1: Soft Delete with Confirmation

async function deleteProductWithConfirmation(productId) {
  if (!confirm('Move product to trash?')) {
    return;
  }

  try {
    await fetch(`${apiBase}/api/v4/core/PRD/${productId}`, {
      method: 'DELETE',
      headers: { 'Authorization': `Bearer ${token}` }
    });

    alert('Product moved to trash. You can restore it later.');
  } catch (error) {
    alert('Failed to delete product');
  }
}

Pattern 2: Trash View with Restore

async function loadTrash() {
  const url = new URL(`${apiBase}/api/v4/core/PRD`);
  url.searchParams.append('source', 'productList');
  url.searchParams.append('cond_trec', 'C');  // Only deleted
  url.searchParams.append('$order', 'LDATA DESC');

  const response = await fetch(url, {
    headers: { 'Authorization': `Bearer ${token}` }
  });

  const trash = await response.json();

  // Render trash items with restore buttons
  trash.data.forEach(product => {
    renderTrashItem(product, async () => {
      // Restore callback
      await fetch(`${apiBase}/api/v4/core/PRD/${product.PRD_ID}`, {
        method: 'PATCH',
        headers: {
          'Authorization': `Bearer ${token}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify({ data: { TREC: 'M' } })
      });

      alert('Product restored!');
      loadTrash();  // Refresh
    });
  });
}

Pattern 3: Auto-Cleanup (Force Delete Old Soft-Deleted Records)

async function autoCleanup(daysOld = 30) {
  // Calculate cutoff date
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - daysOld);
  const cutoffTimestamp = cutoffDate.toISOString().replace(/[-:T.]/g, '').slice(0, 14);

  // Get old soft-deleted records
  const url = new URL(`${apiBase}/api/v4/core/PRD`);
  url.searchParams.append('source', 'productList');
  url.searchParams.append('cond_trec', 'C');  // Only soft-deleted
  url.searchParams.append('$filter', `LDATA lt '${cutoffTimestamp}'`);

  const response = await fetch(url, {
    headers: { 'Authorization': `Bearer ${token}` }
  });

  const oldDeleted = await response.json();

  // Force delete each
  const deletions = oldDeleted.data.map(product =>
    fetch(`${apiBase}/api/v4/core/PRD/${product.PRD_ID}?force=true`, {
      method: 'DELETE',
      headers: { 'Authorization': `Bearer ${token}` }
    })
  );

  await Promise.all(deletions);

  console.log(`Auto-cleaned ${oldDeleted.data.length} records older than ${daysOld} days`);
}

// Run daily cleanup
setInterval(() => autoCleanup(30), 24 * 60 * 60 * 1000);

Best Practices

✅ DO:

Use soft delete by default:

// ✅ Good - reversible
DELETE /api/v4/core/PRD/123

Confirm before force delete:

// ✅ Good
if (confirm('Permanently delete? This cannot be undone!')) {
  DELETE /api/v4/core/PRD/123?force=true
}

Provide restore functionality:

// ✅ Good - users can restore
async function restoreProduct(id) {
  await fetch(`${apiBase}/api/v4/core/PRD/${id}`, {
    method: 'PATCH',
    body: JSON.stringify({ data: { TREC: 'M' } })
  });
}

Auto-cleanup old soft-deleted records:

// ✅ Good - force delete after 30 days
await autoCleanup(30);

❌ DON'T:

Don't use force delete for regular deletions:

// ❌ Bad - irreversible by default
DELETE /api/v4/core/PRD/123?force=true

// ✅ Good - soft delete first
DELETE /api/v4/core/PRD/123

Don't forget to handle cascades:

// ❌ Bad - orphaned order items
DELETE /api/v4/core/ORD/123
// Order items left without parent

// ✅ Good - configure DELETE_CASCADE in metadata
// TB_COST.DELETE_CASCADE handles related records

Don't force delete without confirmation:

// ❌ Bad - no warning
await forceDeleteProduct(123);

// ✅ Good - confirm first
if (confirm('Permanently delete?')) {
  await forceDeleteProduct(123);
}

Summary

✅ Soft delete (default): Sets TREC='C', reversible
✅ Force delete (?force=true): Physical removal, irreversible
✅ Soft-deleted records excluded from default queries
✅ Restore via PATCH setting TREC='M'
✅ DELETE_CASCADE propagates deletions to related records
✅ Outbox event published to RabbitMQ
✅ Use cond_trec=C to query soft-deleted records

Key Takeaways:

Default DELETE is soft (TREC='C'), not physical removal
Force delete (?force=true) is permanent and irreversible
Soft delete enables "trash" and restore functionality
Always confirm before force delete
DELETE_CASCADE handles related record deletion
Auto-cleanup old soft-deleted records (e.g., after 30 days)

Decision Matrix:

Use Case	Operation
User deletes record	Soft delete (TREC='C')
Restore from trash	PATCH with TREC='M'
GDPR data removal	Force delete (?force=true)
Test data cleanup	Force delete (?force=true)
30+ day old deleted records	Force delete (auto-cleanup)

Next: Validation →

TREC Filtering - Query by TREC state
Entity Lifecycle - TREC state machine
Cascade Operations - DELETE_CASCADE patterns

Delete Operations (DELETE)

Overview​

Soft Delete (Default)​

Behavior​

TREC State Transition​

Query Behavior After Soft Delete​

Force Delete (Permanent)​

Behavior​

When to Use Force Delete​

Restore (Undelete)​

Restore Soft-Deleted Record​

DELETE_CASCADE Operations​

Soft Delete Cascades​

Force Delete Cascades​

Outbox Event​

Delete Event Publication​

JavaScript Implementation​

Delete Service​

Trash Management​

Common Patterns​

Pattern 1: Soft Delete with Confirmation​

Pattern 2: Trash View with Restore​

Pattern 3: Auto-Cleanup (Force Delete Old Soft-Deleted Records)​

Best Practices​

✅ DO:​

❌ DON'T:​

Summary​

Related Concepts​

Overview

Soft Delete (Default)

Behavior

TREC State Transition

Query Behavior After Soft Delete

Force Delete (Permanent)

Behavior

When to Use Force Delete

Restore (Undelete)

Restore Soft-Deleted Record

DELETE_CASCADE Operations

Soft Delete Cascades

Force Delete Cascades

Outbox Event

Delete Event Publication

JavaScript Implementation

Delete Service

Trash Management

Common Patterns

Pattern 1: Soft Delete with Confirmation

Pattern 2: Trash View with Restore

Pattern 3: Auto-Cleanup (Force Delete Old Soft-Deleted Records)

Best Practices

✅ DO:

❌ DON'T:

Summary

Related Concepts