Skip to navigation
Documents AI Release 2

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)

{
  "message": "Extracted entity deleted successfully",
  "deletedRecords": {
    "snapshots": 5,
    "entity": 1,
    "document": 1
  }
}

Response Body (Not Found)

{
  "error": "Extracted entity not found"
}

Implementation Details

File Changes Required

1. Model Layer (extractedEntities.model.ts)

Add new method to handle cascading delete:

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:

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:

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:

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

  • 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