---
title: "Environment Applicant Purge"
slug: environment-applicant-purge
section: Governance & Operations
status: draft
description: "A first-class, sanctioned way to remove applicants — up to and including **all** applicants — from a non-production Globus environment, keeping a protected allow-list (the shared E2E bot accounts) and cleaning up the external state a dat…"
last-updated: 2026-10-07
---

# Environment Applicant Purge

A first-class, sanctioned way to remove applicants — up to and including **all** applicants — from a non-production Globus environment, keeping a protected allow-list (the shared E2E bot accounts) and cleaning up the external state a database delete leaves behind.

## Status

> **DRAFT — requirement only, nothing is built.** Authored 2026-07-31 by the platform agent after executing a one-off manual wipe of the development environment (requested by Sam O'Regan). Every constraint and number in this page was observed live during that exercise. This page exists to turn that one-off into a reviewed, repeatable capability; no BackOfficePrototype code has been written for it.

## Problem

There is currently **no way to delete an applicant** in Globus, let alone reset an environment:

- No `DELETE` API route exists for applicants (nothing under `/api/applicants` or `/api/admin/applicants`), no storage-layer delete function, and no gateway/MCP tool.
- The `applicants` table has **no soft-delete** (`deleted_at` / `is_active`) column — any removal is a hard delete.
- A naive SQL `DELETE FROM applicants` fails immediately: effectively all foreign keys into `applicants` are `ON DELETE NO ACTION` (33 direct FK edges as of 2026-07-31; only `applicant_waitlist_entries` cascades).
- The closest existing tool, `scripts/cleanup-applicants-without-program.ts` (Task #819), deletes only program-less applicants, explicitly refuses a full wipe, and — as discovered live — currently **throws on any applicant with agreement data** because its FK cycle-breaker list is stale.

Meanwhile dev and QA accumulate hundreds of throwaway test applicants (Mailinator batches, E2E leftovers, tester signups), and the only reset options are bespoke one-off scripts run by hand against the production-grade FK graph. That is slow, risky, and unrepeatable.

## Evidence — the 2026-07-31 Manual Dev Wipe

The manual exercise this requirement is based on removed 227 of 229 applicants from development. What it took is the best available sizing of the real feature:

| Dimension | Observed value |
| --- | --- |
| Tables reachable from `applicants` via FK graph | 93 |
| Dependent rows cascaded | ~11,600 |
| Undeclared FK cycle that broke the topo sort | `partner_agreements.engine_acceptance_id` and `agreement_acceptances.migrated_from_partner_agreement_id` (2-cycle) |
| Composite-PK table needing special handling | `ops_alert_dismissals` (PK alert_id + user_id) |
| Soft references invisible to the FK walker | applicant-typed `notifications` (polymorphic recipient), `reference_submissions` and children, `program_enrollments` tree (keyed on the portal user id, not `applicants.id`) |
| Keycloak realm users deleted separately | 17 of 21 in the `applicant-portal` realm |
| Orphaned GCS objects deleted separately | 1,946 of 1,997 in the dev document bucket |
| Boot-time seeders that recreate demo applicants | 3 (about 24 applicants) — required `SKIP_SEEDS=true` on the dev deployment (now set, matching prod) |
| Outbound side effects of the DB delete | None — no `applicant.deleted` webhook event type exists; nothing notified HubSpot or n8n |

Two latent defects were found and are still relevant to any implementation: the stale cycle-breaker list in the Task #819 script (it would fail its own job today), and the fact that new applicants keep arriving mid-purge via the live HubSpot onboarding pipeline (a tester provisioned a new applicant minutes after the wipe committed — expected behaviour, but the feature must define its stance on it).

## Goals

1. **Environment reset**: an operator can remove all applicants (and their full dependency graph) from development or QA in one sanctioned operation, preserving a protected allow-list.
2. **Protected identities are structural, not tribal knowledge**: the shared E2E bot accounts (GLB-00163, GLB-00227, GLB-00234 and their logins) are protected by default, in code, not by operator memory.
3. **Complete cleanup**: the operation covers, or explicitly orchestrates, the external state a DB delete orphans — Keycloak realm users, GCS document/media objects, and boot-time seed behaviour.
4. **Safe by default**: dry-run first, loud plans, hard refusal on production, and post-run invariants that abort before commit if the survivor set is wrong.
5. **Repeatable and audited**: every execution leaves a record of who ran it, what filter was used, and what was deleted.

## Non-Goals

- **Production data deletion.** This feature must refuse to run against production. GDPR/right-to-erasure for real applicants is a separate requirement with different review, and is only noted under Open Questions.
- **Selective data surgery.** Removing one applicant's single application or partial history is out of scope; the unit of operation is the applicant.
- **Cleaning append-only audit tables.** `audit_logs` and `pii_access_logs` are trigger-protected append-only stores and survive by design (their PII retention is flagged under Open Questions).
- **HubSpot contact deletion.** Globus is deliberately downstream of HubSpot; contacts are never deleted from the CRM by this feature.

## Functional Requirements

| ID | Requirement |
| --- | --- |
| FR-1 | Purge all applicants in the target environment except a protected allow-list, cascading through every table that references `applicants` directly or transitively. |
| FR-2 | The protected allow-list defaults to the shared E2E bot applicants and their emails, and accepts additional keeps (by email or applicant id) per run. |
| FR-3 | Dry-run is the default mode: execute the full cascade in a transaction, report per-table delete counts and the exact survivor list, then roll back. A separate explicit flag or confirmation commits. |
| FR-4 | The FK graph is discovered at runtime from the database catalog — never from a hardcoded table list — so raw-SQL tables outside the ORM schema are covered and schema drift cannot silently skip tables. |
| FR-5 | Known FK cycles are declared in one shared registry used by every purge tool (today two copies exist and one is stale). An undeclared cycle must fail loudly with the offending tables named. |
| FR-6 | Composite-primary-key tables are supported at least as leaves (deleted via their incoming FKs). |
| FR-7 | Soft references are swept in the same transaction: applicant-typed `notifications` (and delivery children), `reference_submissions` (and children), and the `program_enrollments` tree keyed on the portal user id — with portal user ids snapshotted before the cascade deletes the applicant rows. |
| FR-8 | Keycloak: the operation deletes (or produces an exact, executable list of) the `applicant-portal` realm users that correspond to purged applicants, preserving the protected bot logins. |
| FR-9 | GCS: the operation deletes (or produces an exact, executable list of) orphaned objects under the applicant document/media prefixes, excluding objects referenced by surviving DB rows. |
| FR-10 | Seeds: the runbook (or the tool itself) accounts for boot-time seeders re-creating demo applicants; environments where the purge is expected to stick must run with seeds disabled. |
| FR-11 | Every run (dry or applied) is recorded: operator identity, environment, filter, per-table counts, and outcome. |

## Data-Model Constraints the Feature Must Respect

These are observed facts, not design choices, and they shape any implementation:

- FKs into `applicants` are `NO ACTION` — deletion order must be computed (reverse-topological over the discovered graph); `TRUNCATE ... CASCADE` reaches far beyond the applicant subgraph and is not acceptable.
- `session_replication_role = replica` requires superuser, which the Cloud SQL application role does not have — trigger suppression is not available; the engine must work within normal FK enforcement.
- The `applicants` table is defined **twice** in the codebase (`shared/schema.ts` and `shared/models/applicant-lifecycle.ts`, with the barrel copy shadowing the model copy) — implementations should work from the database catalog, not the ORM schema, until that duplication is resolved.
- `applicants.portal_user_id` carries both legacy numeric portal ids and Keycloak UUIDs; the Keycloak linkage is by this column plus email, with no FK.
- The onboarding pipeline (HubSpot to n8n to Globus provision) recreates applicants at any time; a purge is a point-in-time reset, not a lock.

## External Systems in Scope

| System | What a DB purge leaves behind | Required handling |
| --- | --- | --- |
| Keycloak (`applicant-portal` realm) | Orphaned logins for deleted applicants | Delete non-protected users (FR-8); admin credentials come from the environment's secret manager |
| GCS document bucket | All uploaded documents, step media, profile photos, integration files | Delete orphaned objects, keep survivor-referenced paths (FR-9) |
| Boot-time seeders | ~24 demo applicants recreated on next pod restart | Seeds disabled in target env, or documented re-run expectation (FR-10) |
| HubSpot | Contacts remain (by design) and can re-provision applicants | None — documented behaviour |
| n8n / webhooks | Nothing fires (no applicant.deleted event exists) | None for the purge itself; see Open Questions |

## Guard Rails

- **Production refusal**: hard-fail when the environment identifies as production (e.g. `CONFIG_ENV`), with no override flag in the first release.
- **Survivor invariant**: inside the transaction, after deletion, the remaining applicant set must exactly equal the protected list — any mismatch aborts before commit.
- **Dry-run parity**: the dry run must execute the identical code path as apply (real deletes, rolled back) so a green dry run is meaningful.
- **Loud failure over silent skip**: undeclared cycles, unsupported composite-PK tables with children, and non-converging scope population must all throw with actionable messages.

## Suggested Delivery Phases

1. **Phase 1 — sanctioned script** (small): a maintained repo script implementing FR-1..FR-7 and FR-11, plus a documented runbook for FR-8..FR-10. This is essentially a hardening of what the 2026-07-31 exercise already validated live.
2. **Phase 2 — orchestrated reset**: the script grows Keycloak and GCS execution (FR-8/FR-9 move from runbook to automation), gated behind per-system flags.
3. **Phase 3 — operator surface** (needs its own review): an admin API and/or BackOffice UI behind a dedicated RBAC permission, with the same guard rails; consider an `applicant.deleted` webhook event so downstream systems can react.

## Open Questions

1. **Who may run it?** Proposal: platform engineers only in Phase 1 (script access = repo + DB access); a dedicated RBAC permission if Phase 3 lands.
2. **Should `applicant.deleted` exist as an event type?** Today nothing fires on delete — good for a quiet reset, bad if any downstream ever needs to know. Decide before Phase 3.
3. **Per-applicant deletion (GDPR / right-to-erasure)** — the same engine trivially supports a single-applicant filter, but production erasure has legal/audit requirements (including the append-only `audit_logs` PII retention) that this draft does not attempt to cover. Separate requirement?
4. **QA parity** — the 2026-07-31 exercise covered development only. QA has its own Keycloak instance, bucket names, and seed posture; the runbook constants need a per-environment table.
5. **Mid-purge provisioning** — should the tool warn (or briefly pause the n8n onboarding webhook) when new applicants arrive during a purge window, or is "point-in-time reset" acceptable? Default proposal: acceptable, documented.

## Acceptance Criteria

- A dry run against a populated dev environment reports the full per-table plan and survivor list, rolls back, and leaves counts byte-identical.
- An applied run leaves exactly the protected applicants (and their applications/files/logins) intact, with portal login verified for at least one protected bot.
- The run record (FR-11) exists for both modes.
- Re-running immediately after an applied run is a no-op ("nothing to do").
- A seeded pod restart in the target environment does not resurrect purged applicants (FR-10 verified).
- The stale cycle-breaker defect in `scripts/cleanup-applicants-without-program.ts` is fixed as part of Phase 1 (shared registry, FR-5).

## Related Pages

- [Applicant vs Application](/docs/applicant-vs-application) — the data-model split this feature cascades through
- [PII & Data Handling](/docs/pii-data-handling) — audit-table retention constraints
- [Config Import/Export](/docs/config-import-export) — the adjacent environment-portability tooling
- [Applicant Lifecycle](/docs/applicant-lifecycle) — how applicants are provisioned (and re-provisioned) by the onboarding pipeline