When users start registration but never verify their OTP, their rows linger as 'ghost users' — polluting metrics and blocking email reuse. The solution is to model unverified signups explicitly with a Pending status, flip to Active only on OTP verification, add a partial index on pending rows for cheap scans, and run a Quartz.NET background job every 15 minutes to hard-delete anyone who never verified within a configurable window. Two-table separation (pending_users vs users) is also discussed as an alternative for high-query-load scenarios. Bonus considerations include decoupling OTP sending via domain events, the outbox pattern for delivery guarantees, rate limiting on registration endpoints, and making the cleanup job safe for multi-instance deployments.
Table of contents
The Naive WayWhy This HappensStep 1: Add a Status to the UserStep 2: Insert as Pending, Generate the OTPStep 3: Flip to Active on VerificationStep 4: Index the Pending RowsStep 5: Sweep the Ghosts with QuartzWhen Two Tables Is the Better CallBonus: What I’d Improve With More TimeSummary9.5K Impressions1 Comment