Skip to content

Standard Operating Procedure (SOP): TMS Organization Data Deletion

1. Purpose and Scope

This document outlines the standard procedure for safely extracting, backing up, and permanently deleting all data associated with a specific organization from the TMS databases (tms_booking, tms_drivers, tms_fleet, tms_planner, tms_reports, and tms_resource).

2. Prerequisites

  • Target Identification: The exact UUID of the organization to be deleted (e.g., org_uuid = 'b067f771-edf6-47fa-b58d-e2c6fcebd54b').

  • Database Access: Read/Write access to the nightly (staging) and stable (production) databases.

  • Personnel:

  • Data Engineer / DBA: Executes the scripts.

  • QA / Reviewer: Validates system stability post-deletion (e.g., Sanna Koskiranta-Nurmi).

  • Database Architect: Approves cascading impact (e.g., Koste Sibinovski).

3. Phase 1: Data Discovery and Extraction (SELECT)

Before any data is deleted, it must be located and backed up for compliance and review.

  1. Formulate SELECT Queries: Write SELECT statements for every table across all microservice databases (booking, drivers, fleet, planner, reports, resource) that contain an org_uuid or an org_index.

  2. Join Linked Tables: For tables that do not have a direct organization UUID, use JOIN statements to link back to the core table that holds the organization reference (e.g., joining booking_conversation to booking to organization).

  3. Execute and Export: Run the queries against the database and export the results to standard data files (e.g., CSV).

  4. Record Row Counts: Document the exact number of rows returned by each SELECT query. This is a critical metric for Phase 4.

4. Phase 2: Backup and Stakeholder Review

  1. Secure Storage: Upload all exported data files to the designated secure Google Drive folder.

  2. Stakeholder Handoff: Share the folder with the designated QA/Reviewer (Sanna) for a final review of the data slated for permanent deletion.

  3. Wait for Sign-off: Do not proceed to deletion until the reviewer confirms the data is correct and safe to remove.

5. Phase 3: Query Conversion and Impact Analysis

  1. Convert to DELETE: Rewrite all verified SELECT queries into DELETE queries.

  2. Database Architecture Review:

  3. Submit the DELETE queries to the Database Architect (Koste) for inspection.

  4. Goal: Verify cascading deletion impacts. Determine if deleting a parent row will automatically drop child rows (CASCADE), or if child rows need to be explicitly deleted first to avoid database constraint errors or orphaned records.

  5. Schema Verification: Clarify any ambiguous foreign keys during this step (e.g., confirming if user_index maps exactly to customer_index in the current schema).

6. Phase 4: Staging / Nightly Execution

All deletions must be tested in a non-production environment first.

  1. Execute Deletions: Run the approved DELETE queries in the nightly environment.

  2. Compare Row Counts: Ensure the number of deleted rows perfectly matches the number of rows recorded during the SELECT phase.

  3. QA Regression Testing: Notify QA (Sanna) to test the application in the nightly environment. The goal is to ensure the UI, related microservices, and general application stability have not been broken by the missing data or cascading effects.

7. Phase 5: Production / Stable Execution

Proceed only after successful QA sign-off from the nightly environment.

  1. Final Verification: Ensure no new data has been written for the organization since the initial extraction.

  2. Execute Deletions: Run the DELETE queries against the stable database, strictly following the execution order mandated by the DB Architect to prevent foreign key constraint violations.

  3. Post-Execution Audit: Re-run the initial SELECT queries. They should all return 0 rows.

  4. Final Confirmation: Notify the team that the organization data has been permanently purged from production.

To verify how user_index is mapped, you can query the database's information_schema.

These queries will show you exactly which table and column user_index points to, confirming if it maps to customer_index or the customer table.

PostgreSQL requires joining three different tables in the information_schema to get the full picture of a foreign key relationship.

SQL

SELECT
    tc.table_name AS source_table, 
    kcu.column_name AS source_column, 
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column,
    tc.constraint_name
FROM 
    information_schema.table_constraints AS tc 
JOIN 
    information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN 
    information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
WHERE 
    tc.constraint_type = 'FOREIGN KEY' 
    AND kcu.column_name = 'user_index';