Account Deletion Pipeline

How user-requested account deletion works end-to-end: soft-delete with a 30-day grace window, a daily sweep that hard-deletes and anonymizes, and an append-only audit log.

Shipped in #385. Follow-ups tracked in #386 (integration test), #387 (frontend UI), #388 (performance & robustness).

Overview

  User clicks "Delete my account"
         │
         ▼
  POST /api/account/delete  ──► profiles.deleted_at = now()
                                profiles.deletion_scheduled_at = +30d
                                Supabase ban (ban_duration=720h)
                                Email sent with signed restore link
         │
         │     ◄── user clicks restore link within 30d ──
         │            POST /api/account/restore
         │            clears deleted_at / deletion_scheduled_at
         │
  [30 days pass]
         │
         ▼
  pg_cron nightly ──► POST /internal/run-deletion-sweep
                      │
                      ▼
              For each user past their schedule:
                1. Delete S3 objects
                2. Delete per-user DB rows
                3. Anonymize Stripe customer (PII cleared)
                4. auth.users.deleteUser() (cascades profiles)
                5. Insert account_deletion_audit row (hashed)
                6. Send post-deletion email

During the grace window, the user is blocked from all authenticated endpoints by requireAuth (middleware checks profiles.deleted_at).

Data model

Migrations: supabase/migrations/20260421_085_account_deletion_pipeline.sql and 20260421_086_account_deletion_audit_uniqueness.sql.

`profiles` additions

Column	Type	Purpose
`deleted_at`	`TIMESTAMPTZ`	Soft-delete marker (added in an earlier migration)
`deletion_scheduled_at`	`TIMESTAMPTZ`	When the sweep will hard-delete
`deletion_reason`	`TEXT`	Optional user-supplied reason

Index: idx_profiles_deletion_scheduled_at (partial, WHERE NOT NULL) — used by the sweep query.

`account_deletion_audit`

Append-only. No PII — only a hashed user id plus timing and counts.

Column	Notes
`user_id_hash`	`SHA-256(user_id + AUDIT_HASH_SALT)` — not reversible without the salt
`requested_at`	Original `profiles.deleted_at`
`scheduled_at`	Original `profiles.deletion_scheduled_at`
`executed_at`	When the sweep ran
`files_deleted`, `r2_objects_deleted`	Counts from the sweep
`stripe_anonymized`	Boolean
`errors`	`TEXT[]` — per-step error strings for partial failures

Unique index uq_account_deletion_audit_user_schedule on (user_id_hash, scheduled_at) prevents duplicate rows on rerun.

`bugflow.submitted_by`

Changed to ON DELETE SET NULL so ticket rows survive but the user association drops when auth.users is deleted.

API surface

All routes are in workers/api/src/routes/account.ts and mounted at /api/account in index-aws.ts, index.ts, and server.ts.

Method	Path	Auth	Purpose
`POST`	`/api/account/delete`	`requireAuth` (Supabase JWT)	Schedule deletion
`POST`	`/api/account/restore`	Signed restore token (body)	Cancel pending deletion
`GET`	`/api/account/deletion`	`requireAuth`	Current deletion status
`POST`	`/internal/run-deletion-sweep`	`requireInternalOrAdmin` (proxy secret or admin)	Run the sweep

The sweep’s batch size defaults to 50 users per invocation. Run as often as makes sense; daily is the default.

Restore token

Signed HS256 with INTERNAL_TOKEN_SECRET — not SUPABASE_JWT_SECRET. The two must differ so a leaked restore token can never be used as a Supabase session JWT. requireAuth also rejects any JWT carrying a purpose claim (belt-and-suspenders).

Token exp equals the scheduled hard-delete time. Expiry is in milliseconds (matches the createSessionToken/verifySessionToken convention, distinct from Supabase’s seconds-based JWT exp).

Secrets

All under /accessible-pdf/shared/ in AWS SSM Parameter Store (us-east-1), SecureString type. Loaded once per Lambda cold start by index-aws.ts:loadSsmSecrets().

Parameter	Purpose	Rotation rules
`SUPABASE_JWT_SECRET`	Verifies Supabase-issued session JWTs	Rotate when Supabase rotates
`INTERNAL_TOKEN_SECRET`	Signs restore tokens (and any future internal single-purpose tokens)	Rotate quarterly. In-flight restore links become invalid — acceptable because the grace window is 30 days; users can contact support
`AUDIT_HASH_SALT`	Salts the `user_id_hash` in `account_deletion_audit`	NEVER rotate. Rotating breaks the ability to resolve a past audit hash back to a specific user on regulator request
`RESEND_API_KEY`	Sends restore + post-deletion emails	Per Resend best practice
`STRIPE_SECRET_KEY`	Anonymizes Stripe customer	Per Stripe best practice
`PROXY_SHARED_SECRET`	Auths calls to `/internal/run-deletion-sweep` and other `/internal/` routes. Also mirrored into Supabase Vault* under the name `proxy_shared_secret` so pg_cron can read it via `vault.decrypted_secrets`.	Rotate in SSM and Vault together. If they drift, the sweep 401s and is a no-op until reconciled

Never commit secrets. Exclude AUDIT_HASH_SALT from any rotation script — it is intentionally immutable.

Scheduled job (pg_cron)

The sweep is driven by a single pg_cron job in Supabase that calls the internal endpoint. The proxy secret lives in Supabase Vault and is decrypted per-run.

Prerequisites (one-time)

pg_cron and pg_net extensions enabled — done in the enable_pg_net_for_cron_http migration.
PROXY_SHARED_SECRET stored in /accessible-pdf/shared/ in AWS SSM.
The same value mirrored into Supabase Vault under the name proxy_shared_secret:

-- Use vault.create_secret() — a direct INSERT into vault.secrets fails
-- with a pgsodium permission error (_crypto_aead_det_noncegen).
SELECT vault.create_secret(
  '<paste PROXY_SHARED_SECRET>',
  'proxy_shared_secret',
  'Shared secret for calling workers/api /internal/* endpoints. Matches SSM /accessible-pdf/shared/PROXY_SHARED_SECRET.'
);

If Vault and SSM drift, the sweep will fire but the internal endpoint returns 401 and the run is a no-op. Rotate both or neither.

Schedule

SELECT cron.schedule(
  'account-deletion-sweep',
  '0 8 * * *',           -- 08:00 UTC daily
  $job$
    SELECT net.http_post(
      url := 'https://api-pdf.theaccessible.org/internal/run-deletion-sweep',
      headers := jsonb_build_object(
        'x-proxy-secret', (SELECT decrypted_secret FROM vault.decrypted_secrets WHERE name = 'proxy_shared_secret'),
        'content-type',   'application/json'
      ),
      body := '{}'::jsonb,
      timeout_milliseconds := 600000   -- 10 minutes; sweep is serial
    ) AS request_id;
  $job$
);

Checking job health

-- Recent runs of the sweep
SELECT * FROM cron.job_run_details
WHERE jobname = 'account-deletion-sweep'
ORDER BY start_time DESC LIMIT 20;

-- The job itself
SELECT * FROM cron.job WHERE jobname = 'account-deletion-sweep';

Disabling temporarily

SELECT cron.unschedule('account-deletion-sweep');

Audit and observability

Querying recent deletions

SELECT executed_at, files_deleted, r2_objects_deleted,
       stripe_anonymized, errors
FROM public.account_deletion_audit
ORDER BY executed_at DESC
LIMIT 50;

Resolving an audit hash to a user

If a user contacts us saying “I requested deletion on X date, can you confirm it ran?” — we can resolve their user id to the hash and look it up.

-- Pseudo: only runs with the real salt available server-side.
SELECT *
FROM public.account_deletion_audit
WHERE user_id_hash = encode(
  digest('<user_uuid>' || ':' || current_setting('app.audit_salt'), 'sha256'),
  'hex'
);

This is only useful while AUDIT_HASH_SALT is stable — another reason never to rotate it.

Structured logs

Sweep execution logs go to Loki via @anglinai/logger. Search for:

[deletion-sweep] user ... failed — per-user errors
[internal.run-deletion-sweep] failed — top-level failures

Runbook

Manually trigger the sweep

curl -X POST \
  -H "x-proxy-secret: $PROXY_SHARED_SECRET" \
  -H "content-type: application/json" \
  -d '{}' \
  https://api-pdf.theaccessible.org/internal/run-deletion-sweep

Response shape:

{
  "success": true,
  "data": {
    "processed": 3,
    "errors": [],
    "users": [
      { "userIdHash": "...", "filesDeleted": 4, "r2ObjectsDeleted": 4, "stripeAnonymized": true },
      ...
    ]
  }
}

Restore an account outside the email flow

If the user lost the email but is still within the 30-day window:

UPDATE public.profiles
SET deleted_at = NULL,
    deletion_scheduled_at = NULL,
    deletion_reason = NULL
WHERE id = '<user_uuid>'
  AND deletion_scheduled_at > now();

Then lift the Supabase ban:

UPDATE auth.users
SET banned_until = NULL
WHERE id = '<user_uuid>';

After the 30-day window has passed and the sweep has fired, recovery is not possible — the user will need to create a new account.

Accidentally-triggered sweep on a user who didn’t intend to delete

If a user calls POST /api/account/delete by mistake, they should use the email restore link. If the email never arrived (Resend outage, spam filter), either:

They sign in — and see the “scheduled for deletion” banner and can click restore from there (after #387 lands).
Support runs the SQL UPDATE above within the grace window.

Sweep is failing repeatedly

Check cron.job_run_details for the status column and return_message. Then hit the endpoint manually (see above) to get a structured error. Common causes:

INTERNAL_TOKEN_SECRET or AUDIT_HASH_SALT missing from SSM.
Stripe API outage → all Stripe anonymizations fail → sweep throws per user (by design — we don’t delete the user if Stripe PII can’t be cleared, so the next sweep can retry).
S3 permissions issue.

Known gaps

Tracked in follow-up issues:

#386 — end-to-end integration test (create user → delete → fast-forward → run sweep → assert).
#387 — frontend UI (profile settings deletion card, /account/restore page, deletion banner).
#388 — performance/robustness: cache isUserActive per-request DB hit, progressive audit rows, batch sizing.

Legal ties

This system backs the commitments in the Data Retention Policy §3. That doc stays in draft until #386 (integration test) proves the pipeline end-to-end, then can be flipped to approved.