Build a Tenant Data Deletion Job

Delete tenant rows across tables in dependency order, remove object storage prefixes, and write a completion receipt with counts.

~40 mindata-deletionprivacyoperations

Task

Offboard a tenant completely within the contractual deletion window and prove afterwards that no data remains.

When to use this

A contract ends and the customer requests deletion. Data is spread across orders, invoices, files, and audit events, with foreign keys linking them. A manual script deletes the main table, fails on constraints, and leaves files in object storage.

Prerequisites

PostgreSQL with foreign keys and a tenant_id column on every table
An object storage bucket with tenant-prefixed keys
A job runner with audited credentials

Steps

1. Dry-run a per-table row count so the deletion scope is explicit before anything is removed.

SELECT 'orders' AS table_name, count(*) FROM orders WHERE tenant_id = $1
UNION ALL
SELECT 'invoices', count(*) FROM invoices WHERE tenant_id = $1
UNION ALL
SELECT 'audit_events', count(*) FROM audit_events WHERE tenant_id = $1;

Expected output: A row count per table that the operator can attach to the deletion request.

2. Delete children before parents inside one transaction, with a bounded statement timeout.

BEGIN;
SET LOCAL statement_timeout = '10min';

DELETE FROM invoice_lines
WHERE invoice_id IN (SELECT id FROM invoices WHERE tenant_id = $1);
DELETE FROM invoices WHERE tenant_id = $1;
DELETE FROM orders WHERE tenant_id = $1;
DELETE FROM audit_events WHERE tenant_id = $1;
DELETE FROM tenants WHERE id = $1;
COMMIT;

Expected output: All tenant rows are removed atomically; a constraint failure rolls back the entire deletion.

3. Remove tenant objects from object storage after the database transaction commits.

aws s3 rm s3://acme-tenant-data/tenants/42 --recursive

Expected output: Objects under the tenant prefix are deleted, while other tenant prefixes remain untouched.

4. Write a completion receipt with counts, timestamps, operator identity, and any data still held in backups.

INSERT INTO deletion_receipts
  (tenant_id, orders_deleted, invoices_deleted, objects_deleted, completed_at, operator, notes)
VALUES
  ('42', 1841, 220, 96, now(), 'oncall-ada', 'Backups expire 2026-11-20');

Expected output: The receipt is stored outside the tenant schema and referenced in the customer response.

Troubleshooting

Problem

A foreign key blocks deletion

Solution

List dependencies with a query on pg_constraint, then delete child rows first or use ON DELETE CASCADE deliberately; do not disable triggers to force it.

Problem

Deleting a large tenant exceeds the timeout

Solution

Batch deletes by id ranges, commit each batch, and record progress so the job can resume without repeating completed work.

Limitations

Data in backups and replicas persists until the retention window ends; state this explicitly in the receipt.
Object storage versioning keeps previous object versions until lifecycle rules expire them.

Deletion is a workflow, not a DELETE

Tenant offboarding touches relational data, files, caches, search indexes, and backups. Start with an explicit inventory of every store that holds tenant data. The database is usually the source of truth, but object storage and search indexes are the places where data survives by accident. The job should be idempotent so a partial failure can be resumed.

Order matters

Foreign keys define a dependency order: invoice lines before invoices, invoices before tenants. Run the relational deletion in one transaction so a failure leaves a consistent state. Set a statement timeout so a lock wait does not hang the job. For large tenants, batch by id ranges and record progress; a ten-minute transaction can bloat WAL and block vacuum.

BEGIN;
SET LOCAL statement_timeout = '10min';
DELETE FROM invoice_lines
WHERE invoice_id IN (SELECT id FROM invoices WHERE tenant_id = $1);
DELETE FROM invoices WHERE tenant_id = $1;
COMMIT;

Handle the non-relational stores

Delete object prefixes after the database commit, because a rollback would otherwise leave the database rows pointing at missing files. Invalidate cached pages and search documents, and remove tenant-scoped encryption keys if you use per-tenant keys. Verify each store with a count query and record the result.

Prove completion

Write a receipt with counts, timestamps, operator, and the remaining backup expiry date. Respond to the customer only after verification. Keep the receipt outside the deleted tenant’s schema so it is not removed by the same job. Schedule a follow-up check after the backup retention window to confirm nothing is restorable.