> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://cla-demo.postmansolutions.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://cla-demo.postmansolutions.com/_mcp/server.

# Delete Extracted Entity

## Overview

Implement a cascading delete operation for extracted entities that safely removes an entity and all its related records from the database. This operation must handle the proper deletion order to maintain referential integrity: snapshots first, then the extracted entity, and finally the associated document record.

## Business Context

Currently, the system lacks the ability to remove extracted entities and their associated data. As the system processes invoices and documents, there may be legitimate reasons to delete records such as:

* Test data cleanup

* Duplicate document removal

* Data privacy/compliance requests (GDPR, right to be forgotten)

* Erroneous uploads that need to be removed entirely

* Data retention policy enforcement

Without a proper delete mechanism, these records accumulate indefinitely, creating data management and compliance challenges.

## User Story

**As a** system administrator

**I want** to delete an extracted entity and all its related data

**So that** I can maintain data hygiene, comply with data retention policies, and remove erroneous or duplicate records from the system

## Technical Requirements

### Database Schema Relationships

The deletion must respect the following relationship hierarchy:

```

ExtractedEntities (parent)

  ↓ ExtractedEntityId

ExtractedEntitySnapshots (child - must be deleted first)

  

ExtractedEntities

  ↓ DocumentId

Documents (related - should be deleted after entity)

```

### Deletion Order

1. **ExtractedEntitySnapshots** - Delete all snapshot history for the entity

2. **ExtractedEntities** - Delete the extracted entity record

3. **Documents** - Delete the associated document record

### API Endpoint Specification

#### Endpoint

```

DELETE /v1/extracted_entities/:entityId

```

#### Authentication & Authorization

* **Roles**: Administrator only

* **Scopes**: `EXTRACTED_ENTITIES_WRITE`

#### Request Parameters

* **Path Parameter**:

\- `entityId` (integer, required) - The unique identifier of the extracted entity to delete

#### Response Codes

* **200 OK** - Entity and related records deleted successfully

* **404 Not Found** - Entity does not exist

* **500 Internal Server Error** - Database or system error occurred

#### Response Body (Success)

```json

{

  "message": "Extracted entity deleted successfully",

  "deletedRecords": {

    "snapshots": 5,

    "entity": 1,

    "document": 1

  }

}

```

#### Response Body (Not Found)

```json

{

  "error": "Extracted entity not found"

}

```

## Implementation Details

### File Changes Required

#### 1. Model Layer (`extractedEntities.model.ts`)

Add new method to handle cascading delete:

```typescript

static async deleteEntityByID(entityId: string | number): Promise<{

  snapshotsDeleted: number;

  entityDeleted: boolean;

  documentDeleted: boolean;

} | null> {

  const db = getDb();

  

  // First, verify the entity exists and get its DocumentId

  const entityCheckSql = `

    SELECT DocumentId

    FROM ExtractedEntities

    WHERE ExtractedEntityId = @id;

  `;

  const entityCheck = await db.executeQuery(entityCheckSql, { id: entityId });

  if (!entityCheck.recordset?.[0]) {

    return null; // Entity not found

  }

  const documentId = entityCheck.recordset[0].DocumentId;

  

  // Step 1: Delete all snapshots for this entity

  const deleteSnapshotsSql = `

    DELETE FROM ExtractedEntitySnapshots

    WHERE ExtractedEntityId = @id;

  `;

  const snapshotsResult = await db.executeQuery(deleteSnapshotsSql, { id: entityId });

  const snapshotsDeleted = snapshotsResult.rowsAffected?.[0] || 0;

  

  // Step 2: Delete the extracted entity

  const deleteEntitySql = `

    DELETE FROM ExtractedEntities

    WHERE ExtractedEntityId = @id;

  `;

  const entityResult = await db.executeQuery(deleteEntitySql, { id: entityId });

  const entityDeleted = (entityResult.rowsAffected?.[0] || 0) > 0;

  

  // Step 3: Delete the associated document

  const deleteDocumentSql = `

    DELETE FROM Documents

    WHERE DocumentId = @documentId;

  `;

  const documentResult = await db.executeQuery(deleteDocumentSql, { documentId });

  const documentDeleted = (documentResult.rowsAffected?.[0] || 0) > 0;

  

  return {

    snapshotsDeleted,

    entityDeleted,

    documentDeleted,

  };

}

```

#### 2. Controller Layer (`extractedEntities.controller.ts`)

Add controller method:

```typescript

async deleteEntityByID(fastify: FastifyInstance, entityId: string | number) {

  const result = await EntitiesModel.deleteEntityByID(entityId);

  if (!result) {

    throw new Error('Extracted entity not found');

  }

  

  return {

    message: 'Extracted entity deleted successfully',

    deletedRecords: {

      snapshots: result.snapshotsDeleted,

      entity: result.entityDeleted ? 1 : 0,

      document: result.documentDeleted ? 1 : 0,

    },

  };

}

```

#### 3. Route Layer (`extractedEntities.route.ts`)

Add DELETE route:

```typescript

fastify.delete(`${basePath}/extracted_entities/:entityId`, {

  schema: entitySchema.deleteEntity,

  preValidation: [fastify.authenticate, fastify.authorize],

  config: {

    validRoles: [ROLES.Administrator],

    validScopes: [SCOPES.EXTRACTED_ENTITIES_WRITE],

  },

  handler: async (request: FastifyRequest, reply: FastifyReply) => {

    try {

      const { entityId } = request.params as { entityId: string };

      const response = await EntitiesController.deleteEntityByID(

        fastify,

        entityId

      );

      reply.status(200).send(response);

    } catch (err: any) {

      request.log.error(err);

      if (err.message === 'Extracted entity not found') {

        return reply.status(404).send({ error: 'Extracted entity not found' });

      }

      reply.status(500).send({ error: 'Internal Server Error' });

    }

  },

});

```

#### 4. Schema Layer (`extractedEntities.schema.ts`)

Add OpenAPI schema definition:

```typescript

deleteEntity: {

  security: [

    {

      bearerAuth: [],

    },

  ],

  tags: ['Extracted Entities'],

  summary: 'Delete an extracted entity and all related records',

  description: 'Permanently deletes an extracted entity, its snapshots, and associated document. This operation cannot be undone.',

  params: {

    type: 'object',

    properties: {

      entityId: {

        type: 'integer',

        description: 'The unique identifier of the extracted entity to delete',

      },

    },

    required: ['entityId'],

  },

  response: {

    200: {

      type: 'object',

      properties: {

        message: { type: 'string' },

        deletedRecords: {

          type: 'object',

          properties: {

            snapshots: { type: 'integer', description: 'Number of snapshot records deleted' },

            entity: { type: 'integer', description: 'Number of entity records deleted (should be 1)' },

            document: { type: 'integer', description: 'Number of document records deleted (should be 1)' },

          },

        },

      },

      examples: [

        {

          message: 'Extracted entity deleted successfully',

          deletedRecords: {

            snapshots: 5,

            entity: 1,

            document: 1,

          },

        },

      ],

    },

    404: {

      type: 'object',

      properties: {

        error: { type: 'string' },

      },

      examples: [

        {

          error: 'Extracted entity not found',

        },

      ],

    },

    500: {

      type: 'object',

      properties: {

        error: { type: 'string' },

      },

      examples: [

        {

          error: 'Internal Server Error',

        },

      ],

    },

  },

},

```

## Acceptance Criteria

### Functional Requirements

* [ ] **AC1**: DELETE endpoint accepts an `entityId` path parameter and returns 200 on success

* [ ] **AC2**: Endpoint returns 404 with appropriate error message when entity does not exist

* [ ] **AC3**: All related `ExtractedEntitySnapshots` records are deleted before the entity

* [ ] **AC4**: The `ExtractedEntities` record is deleted after snapshots

* [ ] **AC5**: The related `Documents` record is deleted after the entity

* [ ] **AC6**: Response includes count of deleted records for each table

* [ ] **AC7**: Deletion is performed in a single database transaction (all-or-nothing)

* [ ] **AC8**: Proper error handling with rollback if any deletion step fails

### Security Requirements

* [ ] **AC9**: Only administrators can delete extracted entities (role-based access)

* [ ] **AC10**: Endpoint requires `EXTRACTED_ENTITIES_WRITE` scope

* [ ] **AC11**: Authentication middleware is properly applied

* [ ] **AC12**: Authorization middleware validates roles and scopes

### API Documentation Requirements

* [ ] **AC13**: OpenAPI schema includes complete request/response documentation

* [ ] **AC14**: Schema includes example responses for success and error cases

* [ ] **AC15**: Schema documents the cascading delete behavior

* [ ] **AC16**: Endpoint appears in generated API documentation

### Code Quality Requirements

* [ ] **AC17**: Follows existing patterns in `users.route.ts` for DELETE operations

* [ ] **AC18**: Uses proper TypeScript types throughout

* [ ] **AC19**: Includes comprehensive error logging

* [ ] **AC20**: No use of `any` type (use proper interfaces/types)

* [ ] **AC21**: Follows SOLID principles and existing architectural patterns

* [ ] **AC22**: Code passes ESLint checks without errors

* [ ] **AC23**: Proper code formatting with Prettier

### Testing Requirements

* [ ] **AC24**: Unit test for successful deletion (all records deleted)

* [ ] **AC25**: Unit test for 404 when entity doesn't exist

* [ ] **AC26**: Unit test verifies all three deletion steps execute in order

* [ ] **AC27**: Unit test validates proper count of deleted records in response

* [ ] **AC28**: Unit test validates transaction rollback on error

* [ ] **AC29**: Integration test with authentication/authorization

* [ ] **AC30**: Tests achieve minimum 85% code coverage for new code

* [ ] **AC31**: Test that foreign key constraints are properly handled

### Edge Cases & Error Handling

* [ ] **AC32**: Handles case where snapshots exist but entity delete fails

* [ ] **AC33**: Handles case where entity deletes but document delete fails

* [ ] **AC34**: Handles database connection errors gracefully

* [ ] **AC35**: Does not log sensitive data or PII in error logs

* [ ] **AC36**: Returns appropriate HTTP status codes for all scenarios

## Technical Considerations

### Database Transaction Safety

The implementation should use database transactions to ensure atomicity:

* If any deletion step fails, all changes should be rolled back

* This prevents orphaned records or partial deletions

* Consider using SQL Server's `BEGIN TRANSACTION` / `COMMIT` / `ROLLBACK`

### Potential Issues to Address

1. **Foreign Key Constraints**: Ensure database schema has proper FK relationships that allow deletion

2. **Blob Storage**: Consider whether the physical blob file should also be deleted from Azure Storage

3. **Audit Trail**: Consider whether to maintain soft-delete or audit logs before physical deletion

4. **Concurrent Operations**: Handle race conditions where entity is being processed while delete is called

### Performance Considerations

* For entities with many snapshots, deletion could take time

* Consider adding query timeouts or async processing for large deletions

* Monitor database locks during cascading deletes

### Future Enhancements (Out of Scope)

* Soft delete implementation (mark as deleted instead of physical removal)

* Bulk delete endpoint for multiple entities

* Delete confirmation workflow

* Azure Blob Storage cleanup integration

* Audit logging for compliance tracking

## Story Points

**Estimate**: 3 Story Points

**Breakdown**:

* Model implementation: 1 point

* Controller/Route/Schema implementation: 1 point

* Testing and documentation: 1 point

## Dependencies

* Existing authentication and authorization middleware

* Database connection plugin (`src/plugins/db.ts`)

* Roles and scopes configuration (`src/auth/roles.ts`, `src/auth/scopes.ts`)

## Definition of Done

* [ ] All acceptance criteria are met

* [ ] Code is implemented following existing patterns

* [ ] Unit tests written and passing with 85%+ coverage

* [ ] Integration tests passing

* [ ] ESLint and Prettier checks pass

* [ ] OpenAPI documentation generated and accurate

* [ ] Code reviewed and approved

* [ ] Merged to development branch

* [ ] Manual testing completed successfully

## Related Work

* Reference: `src/api/v1/users/users.route.ts` - DELETE user implementation

* Reference: `src/api/v1/users/users.model.ts` - `deleteUser` method

* Related: Entity update operations in `extractedEntities.controller.ts`

* Related: Snapshot management in `extractedEntities.model.ts`

## Notes

* This is a destructive operation with no undo capability

* Consider implementing soft delete in future iterations for better auditability

* The blob file in Azure Storage is NOT deleted by this operation (separate story may be needed)

* Ensure proper database backups are in place before deploying to production