Tenant erasure runbook

This is the procedure the Terms §5, the Privacy Policy §6 and the DPA §10 describe: after termination, the workspace stays intact for a 30-day retention-and-return window, then is deleted within a further 30 days and the deletion is confirmed in writing. There is no self-service or automated erasure yet (retention rules never purge — GAP-659); this runbook is the sanctioned path and supersedes the interim checklist in docs/tenant-data-erasure-manual-runbook.md. Owner: the on-call founder. Last reviewed: 13 September 2026. Executed: not yet — the first execution is scheduled against a throwaway workspace (KAN-808) and will be recorded in the change log below.

Architecture this runbook is written for: one shared PostgreSQL database with tenant_id on every quality-record table (161 tables carry the column), files on the backend_uploads volume whose paths are recorded in 19 tables’ file_path-style columns (only licensed standards live in a per-tenant directory, uploads/standards_licences/<tenant_id>/), one Qdrant collection per workspace for licensed standards plus a shared documents collection filtered by tenant, and one Redis debounce key per workspace (sign-in codes are keyed by email and expire in 15 minutes). When the database-per-workspace model (ADR 0078) lands, this runbook is replaced by DROP DATABASE plus the platform-row purge.

Preconditions — all recorded on a ticket KAN-… Erasure: <workspace>

1. Trigger: the retention-and-return window has ended (termination date + 30 days), or a written erasure instruction from the workspace’s administrator has been verified by a reply from their registered email address.
2. Legal hold check: no litigation hold, regulator request or open breach ticket references the workspace.
3. Export offered: the administrator has been told the window end date in writing and offered the export; note whether it was taken.
4. Identify: tenant_id, workspace name, administrator email, subscription state (must be cancelled or expired in Stripe — never erase a workspace with an active subscription).
5. Freeze: suspend the workspace from the control panel (Tenants → Suspend, reason “erasure in progress”). Suspension is reversible; nothing is deleted yet.

Step 1 — Inventory (read-only)

On the production host, as root, against the live database (replace 42 with the tenant_id everywhere):

docker exec -i innoqualis-db sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -v tid=42 -v ON_ERROR_STOP=1' <<'SQL'
-- every table that carries the workspace id, with its row count for this workspace
SELECT set_config('erase.tid', :'tid', false);
DO $$
DECLARE r record; n bigint; tid int := current_setting('erase.tid')::int;
BEGIN
  FOR r IN SELECT table_name FROM information_schema.columns
           WHERE table_schema = 'public' AND column_name = 'tenant_id' AND table_name <> 'tenants'
           ORDER BY table_name LOOP
    EXECUTE format('SELECT count(*) FROM %I WHERE tenant_id = $1', r.table_name) INTO n USING tid;
    IF n > 0 THEN RAISE NOTICE '% rows: %', lpad(n::text, 8), r.table_name; END IF;
  END LOOP;
END $$;
-- child tables that hang off a tenant-scoped parent but carry no tenant_id of their own
SELECT DISTINCT tc.table_name AS child, kcu.column_name, ccu.table_name AS parent
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_name IN (SELECT table_name FROM information_schema.columns WHERE column_name = 'tenant_id')
  AND tc.table_name NOT IN (SELECT table_name FROM information_schema.columns WHERE column_name = 'tenant_id')
ORDER BY 1;
SQL

Then the file inventory — every stored file belonging to the workspace, one path per line, into a file on the host that step 2 consumes (paths are relative to /app inside the backend container):

docker exec -i innoqualis-db sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -At -v tid=42' > erase-42-files.txt <<'SQL'
SELECT set_config('erase.tid', :'tid', false);
SELECT string_agg(format('SELECT %I AS p FROM %I WHERE tenant_id = %s AND %I IS NOT NULL', c.column_name, c.table_name, current_setting('erase.tid'), c.column_name), ' UNION ALL ')
FROM information_schema.columns c
WHERE c.table_schema = 'public' AND c.column_name ~ '(file_path|attachment_path|evidence_path|evidence_file_path|screenshot_path)$'
  AND EXISTS (SELECT 1 FROM information_schema.columns t WHERE t.table_name = c.table_name AND t.column_name = 'tenant_id') \gexec
SQL
grep -vE '^(set_config|string_agg|)$' erase-42-files.txt | sort -u > erase-42-files.clean && mv erase-42-files.clean erase-42-files.txt; wc -l erase-42-files.txt

Paste the outputs on the ticket. The second SQL list (child tables without tenant_id) is deleted through their parents in step 3, before the parents go.

Step 2 — Files and vector data

# the file list from step 1, checked for existence inside the backend container (paths are relative to /app)
docker cp erase-42-files.txt innoqualis-backend:/tmp/erase-files.txt
docker exec innoqualis-backend sh -c 'cd /app && while read -r f; do [ -e "$f" ] && echo "exists $f" || echo "missing $f"; done < /tmp/erase-files.txt'
# licensed standards are the one per-tenant directory
docker exec innoqualis-backend sh -c 'du -sh /app/uploads/standards_licences/42 2>/dev/null || echo "no standards dir"'

Record what exists. The Qdrant collection for licensed standards is tenant_standards_42; document embeddings live in the shared documents collection with a tenant_id payload. Redis holds one activity-debounce key for the workspace (activity:tenant:42 — ACTIVITY_REDIS_PREFIX in backend/app/middleware/activity.py).

Step 3 — Delete (one transaction)

Run in a transaction so a mistake rolls back completely. The schema has 521 foreign keys with no ON DELETE CASCADE and two genuine cycles (documents ↔ document_versions.latest_version_id, documents ↔ document_workflow_instances), so no delete order satisfies the constraints row by row. The transaction therefore disables foreign-key enforcement for its own duration (SET LOCAL session_replication_role = replica — superuser only; the container’s POSTGRES_USER is one), deletes the workspace’s rows from every table, and then proves there are no orphans before committing: every foreign key in the database must still resolve. If a proof fails the transaction rolls back; add the child table to the explicit deletes and record the addition in the change log.

docker exec -i innoqualis-db sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -v tid=42 -v ON_ERROR_STOP=1' <<'SQL'
BEGIN;
SELECT set_config('erase.tid', :'tid', true);
SET LOCAL session_replication_role = replica;   -- FK checks off for this transaction only
-- child tables without tenant_id (step 1, second list) go here, through their parent, e.g.
-- DELETE FROM test_results WHERE test_execution_id IN (SELECT id FROM validation_test_executions WHERE tenant_id = current_setting('erase.tid')::int);
DO $$
DECLARE r record; n bigint; tid int := current_setting('erase.tid')::int;
BEGIN
  -- audit_logs and electronic_signatures go with the workspace: their ORM listeners forbid
  -- UPDATE/DELETE from the application, and this raw-SQL path under an approved erasure ticket
  -- is the one sanctioned exception (DPA §10; see the decision note below).
  FOR r IN SELECT table_name FROM information_schema.columns
           WHERE table_schema = 'public' AND column_name = 'tenant_id'
             AND table_name NOT IN ('tenants', 'audit_logs')
           ORDER BY table_name LOOP
    EXECUTE format('DELETE FROM %I WHERE tenant_id = $1', r.table_name) USING tid;
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n > 0 THEN RAISE NOTICE 'deleted % from %', n, r.table_name; END IF;
  END LOOP;
  DELETE FROM audit_logs WHERE tenant_id = tid;
  DELETE FROM tenants WHERE id = tid;
END $$;
-- proof 1: no row anywhere still carries the workspace id
DO $$
DECLARE r record; n bigint; tid int := current_setting('erase.tid')::int; total bigint := 0;
BEGIN
  FOR r IN SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND column_name='tenant_id' LOOP
    EXECUTE format('SELECT count(*) FROM %I WHERE tenant_id = $1', r.table_name) INTO n USING tid; total := total + n;
  END LOOP;
  IF total > 0 THEN RAISE EXCEPTION 'erasure incomplete: % rows remain', total; END IF;
END $$;
-- proof 2: no orphans — every foreign key still resolves (FK checks were off, so verify by hand)
DO $$
DECLARE r record; n bigint; bad text := '';
BEGIN
  FOR r IN SELECT tc.table_name AS child, kcu.column_name AS col, ccu.table_name AS parent, ccu.column_name AS pcol
           FROM information_schema.table_constraints tc
           JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = tc.constraint_name
           JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
           WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public' LOOP
    EXECUTE format('SELECT count(*) FROM %I c WHERE c.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.%I = c.%I)',
                   r.child, r.col, r.parent, r.pcol, r.col) INTO n;
    IF n > 0 THEN bad := bad || format(' %s.%s(%s)', r.child, r.col, n); END IF;
  END LOOP;
  IF bad <> '' THEN RAISE EXCEPTION 'orphans left behind:%', bad; END IF;
END $$;
COMMIT;
SQL

Audit-chain note: the hash chain is verified per workspace (verify_audit_chain(db, tenant_id), with a per-workspace chain head), so deleting one workspace’s rows does not break any other workspace’s chain. Platform-level rows (tenant_id IS NULL) are untouched.

Decision note (2026-09-13): the interim checklist in docs/tenant-data-erasure-manual-runbook.md treated audit_logs, electronic_signatures and the validation-package tables as Part 11 carve-outs that survive erasure. The DPA in force since 2026-09-13 (§10) and the Terms (§5) commit to deleting the workspace with its audit trails: the customer is the controller of those records, and Part 11 binds the customer’s retention of them, not ours after they leave. The only exception is a documented legal hold recorded on the ticket (regulator request, litigation, open breach), in which case the affected rows are kept and the customer is told what was retained and why. Validation-package rows document the customer’s validation of their workspace and go with it; evidence that the platform itself was validated lives in InnoQualis’s own workspace.

Then the files and vectors:

# files: the list from steps 1–2, then the licensed-standards directory
docker exec innoqualis-backend sh -c 'cd /app && while read -r f; do rm -f -- "$f"; done < /tmp/erase-files.txt; rm -rf /app/uploads/standards_licences/42; rm -f /tmp/erase-files.txt'
# Qdrant is not published on the host and the backend image has no curl — use the backend's Python
docker exec innoqualis-backend python -c 'import os, httpx; b=os.environ["QDRANT_URL"].rstrip("/"); h={"api-key": os.environ.get("QDRANT_API_KEY",""), "content-type": "application/json"}; t=42; print(httpx.delete(f"{b}/collections/tenant_standards_{t}", headers=h, timeout=30).status_code); print(httpx.post(f"{b}/collections/documents/points/delete", headers=h, timeout=60, json={"filter": {"must": [{"key": "tenant_id", "match": {"value": t}}]}}).json())'
# Redis: the workspace's activity-debounce key
docker exec innoqualis-redis sh -c 'redis-cli --no-auth-warning DEL activity:tenant:42'   # REDISCLI_AUTH is set in the container

Step 4 — Verify and confirm

1. Re-run the step 1 inventory: every count must be 0 and SELECT * FROM tenants WHERE id = 42 must return nothing.
2. Sign-in with any former member’s email must fail with “no account”.
3. Backups: the nightly and monthly copies containing the workspace age out on their rolling cycle. They are never restored except for disaster recovery; if that happens before they age out, re-run this runbook for the same tenant_id — record that obligation on the ticket with the date the last copy expires.
4. Email the administrator the written confirmation (template below) from dpo@innoqualis.com and attach the ticket reference. Close the ticket.

Subject: Confirmation of erasure — InnoQualis workspace <name>

Following the end of the retention-and-return window on <date>, the workspace <name> (id <tenant_id>) was deleted from the InnoQualis platform on <date time UTC>: all records, user accounts, files, search indexes and audit trails scoped to the workspace. Encrypted backup copies that predate the deletion expire by <date>; they are used only for disaster recovery, in which case the deletion is re-applied. Reference: <KAN-…>.

Change log

Date	Change
2026-09-13	First version (KAN-808 / KAN-822). Not yet executed; the first run against a throwaway workspace is scheduled and will add any child tables it discovers to step 3.