1. Stage 3 Objective (from BRD)
Customer provides their email address. Three paths are available: KRA pre-fill confirm, Google OAuth, or manual entry with OTP verification. The email is stored as email_hash (SHA-256). The plain email is never persisted.
Backend Only — No Frontend
This document covers only backend APIs, stored procedures, database changes, and business logic. All frontend/UI concerns are excluded.
KRA RESTRICTED Not Surfaced Here
If KRA returned a RESTRICTED status in Stage 2, that information is NOT surfaced or acted upon at this stage. The KRA RESTRICTED flag is handled later in the journey. Stage 3 only uses the kra_prefill_email value if available.
2. Preconditions
| Parameter | Required Value | Notes |
|---|
lead.state |
OTP_VERIFIED |
Stage 2 (PAN + KRA) must be complete. Lead must be in OTP_VERIFIED state. |
| KRA Background Checks |
Should be complete |
Wait a maximum of 3 seconds for CVL KRA result. If CVL result is not available within 3 seconds, render the stage with kra_prefill_email = null (manual entry path). |
KRA Wait Logic
The backend should poll or wait up to 3 seconds for the KRA/CVL background check to return the pre-fill email. If the result is not ready, proceed without it. The customer will use manual entry or Google OAuth instead. This prevents the journey from stalling on slow KRA responses.
3. Email Capture Decision Flow
| Condition | Path | Behaviour |
|---|
kra_prefill_email is set (non-null) |
KRA Pre-fill Path |
Email is pre-filled from KRA. If customer confirms, no OTP is needed — email is accepted directly. If customer chooses to change, they fall through to the manual entry path with OTP. |
kra_prefill_email is null |
Manual Entry Path |
Customer must enter email manually. Google Sign-In option is also presented. If neither is used, a 4-digit OTP is sent to the entered email for verification. |
| Google Sign-In used successfully |
Google OAuth Path |
Email is extracted from the Google OAuth token. No OTP needed. Email is accepted as verified via Google. |
| Google Sign-In closed or failed |
Silent Fallback |
No error shown to customer. Silent fallback to manual email entry path. No retry prompt for Google. |
| Manual email entry |
OTP Verification Path |
A 4-digit OTP is sent to the entered email address. Customer must enter the OTP to verify ownership. Standard OTP rules apply (see Section 5). |
4. Backend Validations
| Validation | Rule | Result if Fails | Blocking? |
|---|
| Email OTP match |
OTP entered must match OTP stored in in-memory OTP store for this lead + email combination |
Attempt count incremented. Customer can retry until max attempts reached. |
Yes |
| Email OTP expiry |
OTP has a 10-minute TTL enforced by in-memory store |
OTP expired. Customer must request a new OTP. FE_EMAIL_003 |
Yes |
| Email OTP max attempts |
Maximum 5 wrong OTP attempts per email address |
Email locked. Customer must enter a different email address to proceed. BE_EMAIL_001 |
Yes |
| Email duplicate check |
Email hash is checked against existing leads. If email hash is already linked to a post-eSign lead (completed or near-complete), it is blocked. |
Duplicate email blocked. BE_EMAIL_002 |
Yes |
| Email OTP delivery failure |
OTP email fails to deliver (Netcore returns failure) |
Journey continues. email_verified = false is stored. No block. |
No |
| Google OAuth failure |
Google Sign-In flow fails, times out, or is cancelled by customer |
Silent fallback to manual email entry. No error surfaced to customer. |
No |
| Restricted email domain check |
Email domain checked against restricted_email_domains table (disposable email services like mailinator, guerrillamail, tempmail, etc.) |
Email rejected. Customer must use a non-disposable email provider. |
Yes |
| Suspicious email flag |
Email checked against known suspicious patterns (e.g., random strings, known fraud patterns) |
Email is flagged for ops review but the journey is NOT blocked. Customer can proceed. |
No — flag only |
5. Email OTP Specification
| Parameter | Value | Notes |
|---|
| OTP Length |
4 digits |
Numeric only (0000–9999) |
| Validity |
10 minutes |
TTL enforced by in-memory store. OTP expires after 10 minutes from generation. |
| Max Wrong Attempts |
5 |
After 5 wrong OTP entries, the email is locked. Customer must enter a different email address to continue. |
| Resend Cooldown |
30 seconds |
Minimum 30-second gap between resend requests for the same email. |
| Max Resend Count |
3 |
Maximum 3 OTP resends per email address. After 3 resends, customer must enter a different email. |
| Attempt Counter Reset |
On new email |
When the customer enters a different email address, all counters (wrong attempts + resend count) reset to zero. |
| Storage |
In-memory only |
OTP stored in server memory only (keyed by email address + OTP type in in-memory store). Never stored in the database. |
6. Existing Code Mapping
Old Stored Procedures
| Stored Procedure | Purpose |
|---|
USP_SEND_RESUME_EMAILOTP_SJET |
Generate email OTP, validate email format, check whitelisted domains, perform duplicate checks across multiple tables. (Note: CYBRIDGE and Karza email validation removed per Product team decision.) |
USP_VALIDATE_UPDATE_OTP_EMAIL_SJET |
Validate submitted OTP against stored value, update stage on success |
USP_UPDATE_OTPEMAIL_STAGE_SJET |
Update OTP email stage tracking |
USP_ALLOW_EMAIL_SJET |
Multi-layer email validation: whitelist check, format validation. (CYBRIDGE and Karza validation removed — not required per Product team.) |
USP_CUSTOM_EMAIL_VALIDATION_SJET |
Custom email format validation rules |
USP_RESTRICT_EMAIL_DOMAIN_SJET |
Domain restriction check against blocked/disposable domain list |
USP_BYPASS_MOBILE_EMAIL_PAN_SJET EMAIL SECTION |
Bypass logic for INACTIVE / PMS / OWNER emails — allows certain emails to skip standard validation |
USP_INSERT_UPDATE_MOBILE_EMAIL_OTP_BYPASS_SJET |
OTP bypass mechanism for RM-assisted resume from Zoho — when an RM resumes a dropped-off journey from Zoho CRM, OTP is bypassed. (Changed: was franchise bypass, now RM-resume-from-Zoho only.) |
Usp_GetSuspiciousPhoneOrEMailId |
Check email against suspicious email patterns / known fraud indicators |
USP_GET_EMAILVALIDATION_FLAG |
Retrieve email validation configuration flags (controls which validations are active) |
USP_UPDATE_EMAIL_STAGE_SJET |
Update the lead stage after email verification is complete |
Old External APIs
| API Call | Purpose |
|---|
EmailAPI.REALTIME_EMAIL_VALIDATION() |
Real-time email deliverability check via CYBRIDGE — REMOVED per Product team. Not required. |
EmailAPI.KARZA_EMAIL_VALIDATION() |
Karza email validation — REMOVED per Product team. Not required. |
EmailAPI.CUSTOM_EMAIL_VALIDATION() |
Custom SP-based email validation — invokes USP_CUSTOM_EMAIL_VALIDATION_SJET |
EmailAPI.CHECK_RESTRICTED_DOMAIN() |
Domain restriction SP call — invokes USP_RESTRICT_EMAIL_DOMAIN_SJET |
EmailAPI.sendMail_Netcore() |
Email OTP delivery via Netcore email service |
Old Reference Tables Used
| Table | Purpose |
|---|
TBL_ALLOW_EMAIL |
Whitelisted email domains — emails from these domains skip certain validation checks |
TBL_RESTRICT_EMAIL_DOMAIN |
Restricted / blocked email domains — disposable email services and known bad domains |
TBL_REALTIME_EMAIL_RESPONSE |
Cached realtime email validation results — avoids repeated API calls for the same email |
TBL_KARZA_EMAIL_VALIDATE_RESPONSE |
Cached Karza email validation results — NOT NEEDED. Karza removed. |
TBL_SEND_RESUME_OTP |
OTP tracking table — stores OTP generation, attempt counts, and verification status |
7. Error / Drop Codes
| Code | Type | Situation | Customer Impact | Ops Impact |
|---|
FE_EMAIL_001 |
Frontend |
Email format validation failed (invalid format) |
Inline validation error — customer must correct the email format |
None |
FE_EMAIL_002 |
Frontend |
Wrong OTP entered |
Error shown, attempt count incremented, customer can retry |
None |
FE_EMAIL_003 |
Frontend |
OTP expired (past 10-minute TTL) |
Customer must request a new OTP |
None |
BE_EMAIL_001 |
Backend |
Email locked — 5 wrong OTP attempts reached |
Customer must enter a different email address to proceed |
Logged for fraud analysis |
BE_EMAIL_002 |
Backend |
Duplicate email — email hash already linked to a post-eSign lead |
Email rejected. Customer must use a different email address. |
Logged for audit |
8. Exit State — What Is Set When Stage 3 Completes
| Field | Value | Storage |
|---|
lead.state |
EMAIL_VERIFIED |
leads table |
email_hash |
SHA-256 hash of the verified email address. Plain email is never stored. |
leads table / email_verifications table |
email_source |
KRA_PREFILL | GOOGLE_OAUTH | MANUAL_OTP |
email_verifications table |
email_verified |
true (verified via KRA confirm, Google OAuth, or OTP match) or false (OTP delivery failed, verification skipped) |
email_verifications table |
email_verified_at |
Timestamp of successful email verification |
email_verifications table |
Email Source Tracking
The email_source field is critical for downstream analytics and compliance. It records how the email was obtained:
• KRA_PREFILL — Customer confirmed the KRA-provided email without modification
• GOOGLE_OAUTH — Email extracted from Google OAuth token (verified by Google)
• MANUAL_OTP — Customer entered email manually and verified via 4-digit OTP
9. Vendor & Integration Calls
| System | Type | What It Does at Stage 3 | Call Type | If Unavailable |
|---|
| Google OAuth |
3rd Party |
Provides verified email from Google account. Extracts email from OAuth ID token. No OTP needed when used. |
Sync |
Silent fallback to manual email entry. No error surfaced. |
| Netcore Email |
3rd Party |
Delivers email OTP to the customer's email address for manual entry path. |
Async |
Journey continues with email_verified = false. No block. |
| In-Memory OTP Store |
Internal |
Stores email OTP (TTL 10 min), attempt counters, resend counters, and cooldown tracking in server memory. |
Sync |
OTP flow unavailable. Fallback to Google OAuth or KRA pre-fill only. |
| CleverTap |
Analytics |
Events: email_verified_en, email_otp_sent_en, email_otp_failed_en, email_source_en |
Async (non-blocking) |
Non-blocking. Events queued for retry. |
| Zoho CRM |
CRM |
Update lead record with email_hash, email_source, email_verified status. |
Async (non-blocking) |
Queued for retry. Lead exists in EKYC DB. |
| CDP |
Internal |
Email verification event: lead_id, email_hash, email_source, verification method. |
Async (non-blocking) |
Non-blocking. |
| Datalake |
Internal |
Full email verification payload: lead_id, email_hash, email_source, OTP attempts, verification timestamp. |
Async (non-blocking) |
Non-blocking. |
10. Database Tables (New Backend)
Operational Tables
leads MODIFY
Existing leads table — updated with email verification fields at Stage 3 completion.
| Column | Type | Notes |
|---|
email_hash | VARCHAR(64) | SHA-256 hash of verified email. Set on Stage 3 completion. |
state | VARCHAR(30) | Updated to EMAIL_VERIFIED on Stage 3 completion. |
email_verifications NEW
Tracks email verification details for each lead. One record per lead, updated on each email attempt.
| Column | Type | Notes |
|---|
id | BIGINT IDENTITY | PK |
lead_id | UUID | FK to leads table |
email_hash | VARCHAR(64) | SHA-256 hash of the email address |
email_source | VARCHAR(20) | KRA_PREFILL | GOOGLE_OAUTH | MANUAL_OTP |
email_verified | BIT | true if verified, false if delivery failed or skipped |
email_verified_at | DATETIME2 | Timestamp of successful verification |
otp_attempts | INT | Number of wrong OTP attempts for current email |
resend_count | INT | Number of OTP resends for current email |
google_oauth_sub | VARCHAR(255) | Google OAuth subject ID (if Google path used). Nullable. |
kra_prefill_used | BIT | Whether the KRA pre-filled email was confirmed |
restricted_domain_checked | BIT | Whether domain restriction check was performed |
suspicious_flag | BIT | Whether the email was flagged as suspicious |
created_at | DATETIME2 | Record creation timestamp |
updated_at | DATETIME2 | Last update timestamp |
otp_verifications (type=EMAIL) EXISTS
Shared OTP verifications table — used for both mobile and email OTP tracking. Filtered by type = 'EMAIL' for this stage.
| Column | Type | Notes |
|---|
id | BIGINT IDENTITY | PK |
lead_id | UUID | FK to leads table |
type | VARCHAR(10) | EMAIL for this stage (also supports MOBILE) |
target_hash | VARCHAR(64) | SHA-256 hash of email address |
otp_sent_at | DATETIME2 | When the OTP was sent |
otp_verified_at | DATETIME2 | When the OTP was successfully verified (nullable) |
attempt_count | INT | Number of wrong OTP attempts |
resend_count | INT | Number of OTP resends |
delivery_status | VARCHAR(20) | SENT | DELIVERED | FAILED |
delivery_channel | VARCHAR(20) | NETCORE_EMAIL |
created_at | DATETIME2 | Record creation timestamp |
downstream_events EXISTS
Async event queue for pushing email verification data to external systems (CleverTap, Zoho, CDP, Datalake).
| Column | Type | Notes |
|---|
id | BIGINT IDENTITY | PK |
lead_id | UUID | FK to leads table |
event_type | VARCHAR(50) | e.g., EMAIL_VERIFIED, EMAIL_OTP_SENT |
target_system | VARCHAR(30) | CLEVERTAP | ZOHO | CDP | DATALAKE |
payload | NVARCHAR(MAX) | JSON payload for the target system |
status | VARCHAR(20) | PENDING | SENT | FAILED |
retry_count | INT | Number of delivery attempts |
created_at | DATETIME2 | Event creation timestamp |
Reference Tables
restricted_email_domains NEW
List of blocked email domains (disposable email services). Replaces the old TBL_RESTRICT_EMAIL_DOMAIN table.
| Column | Type | Notes |
|---|
id | BIGINT IDENTITY | PK |
domain | VARCHAR(255) | Blocked domain (e.g., mailinator.com, guerrillamail.com, tempmail.com) |
reason | VARCHAR(100) | Reason for restriction (e.g., disposable, spam, known fraud) |
is_active | BIT | Active flag — allows soft-disabling without deletion |
added_by | VARCHAR(50) | Who added this domain |
created_at | DATETIME2 | Date added |
suspicious_contacts NEW
Tracks emails (and phone numbers) flagged as suspicious. Replaces the old Usp_GetSuspiciousPhoneOrEMailId SP logic with a table-driven approach.
| Column | Type | Notes |
|---|
id | BIGINT IDENTITY | PK |
contact_type | VARCHAR(10) | EMAIL | MOBILE |
contact_hash | VARCHAR(64) | SHA-256 hash of the suspicious contact |
pattern_match | VARCHAR(255) | Description of the pattern that triggered the flag (nullable) |
flagged_by | VARCHAR(30) | SYSTEM | OPS_MANUAL | FRAUD_ENGINE |
is_active | BIT | Active flag |
created_at | DATETIME2 | Date flagged |
Migration Summary
The new backend replaces the fragmented old structure (11 stored procedures + 5 reference tables + 5 external API calls) with a cleaner architecture:
• Operational tables: leads, email_verifications, otp_verifications, downstream_events
• Reference tables: restricted_email_domains, suspicious_contacts
• OTP storage: In-memory store only (10-min TTL), never in DB
• Three verification paths: KRA pre-fill (no OTP), Google OAuth (no OTP), Manual entry (4-digit email OTP)