Respondent Email Collision Audit

KinoForms canonicalizes new respondent email writes with trim + lowercase and uses case-insensitive lookup for legacy records. The historical database index is still exact-case (workspace_id, email): changing it directly to workspace_id, lower(email) could fail on an existing workspace that already contains case variants, and automatically merging those rows could transfer assignments, sessions, or submissions to the wrong identity.

No migration in Release 1 merges or deletes respondent records. If more than one legacy row matches the same canonical address, password login and magic-link verification fail closed for that identity pending administrator review.

Read-only production audit

Run the following only through an approved read-only production session. It returns record IDs and counts, not email addresses. Do not copy the result into CI logs or public artifacts.

SELECT
  workspace_id,
  count(*) AS matching_rows,
  array_agg(id ORDER BY created_at, id) AS respondent_ids
FROM form_respondents
GROUP BY workspace_id, lower(btrim(email))
HAVING count(*) > 1
ORDER BY workspace_id, min(created_at);

An empty result confirms there are no case/whitespace collision groups at that moment. It does not authorize adding a new unique index without the normal migration review and production approval.

Repair policy

If the audit finds a group, stop before mutating it. An approved repair must establish which row owns the address, inventory every dependent assignment, session, submission, agent grant, and submitter link, resolve uniqueness conflicts explicitly, and preserve an audit record. Never choose the oldest row or delete duplicates automatically. Take a recoverable backup and validate the repair on a production clone before scheduling the production transaction.