1. Stage 1 Objective (from BRD)
Customer enters their mobile number and accepts all three mandatory consents. The platform checks eligibility (negative list, existing account, dedupe) and either:
- Creates a new lead and sends an OTP
- Resumes an existing application
- Blocks the journey with a specific reason
Backend Only — No Frontend
This document covers only backend APIs, stored procedures, database changes, and business logic. All frontend/UI concerns are excluded.
2. Preconditions
| Parameter | Required Value | Source |
session_id | Active session from Stage 0. Stored in Redis (TTL 15 min). | Stage 0 output |
journey_variant_id | Set by LaunchDarkly at Stage 0 | Stage 0 output |
location_tag | SOUTH or OTHERS — must be set before Stage 1 loads | Stage 0 output |
| OTP state | No OTP in flight for this mobile number (in-memory store key must not exist) | In-memory store check |
3. Backend Flow — What to Build
Complete Backend Execution Sequence
1
Receive Request NEW API
Input: mobile_number (10 digits), registration_name (full name), consent_1 (bool), consent_2 (bool), consent_3 (bool), session_id
Validate session_id exists in cache/memory and is not expired.
2
Backend Validation
• Mobile: 10 digits, starts with 6/7/8/9
• Name: 2-100 chars, alpha + spaces only
• All 3 consents must be true
• No OTP currently in flight for this mobile (check in-memory store key)
3
Run Eligibility Checks in Parallel NEW
Fire these three checks simultaneously:
(a) Negative List API — mobile + IP against MOFSL blacklist + SEBI debarred list
(b) CBOS Dedupe API — check for existing active Demat account for this mobile
(c) EKYC DB Dedupe — check for in-progress application in TBL_DEDUPE_DATA_DUMP
4
Apply Eligibility Decision Table (see Section 5)
Priority-ordered rules determine outcome: block, redirect, resume, or create new lead.
5
Create Lead Record MODIFY
If eligible for new lead:
• Generate lead_id (UUID)
• Store mobile_hash (SHA-256 of mobile, not plain mobile)
• Store registration_name
• Store channel, source, UTM params, device info, journey variant from session
• Set lead.state = INITIATED
Existing SP: [DIY].[USP_CLIENT_REGISTRATION_SJET] — needs modification to store hash instead of plain mobile, simplified input params.
6
Save Consent Records NEW
Each consent saved with: consent_id, version, timestamp, ip_address, platform, lead_id
Consent save MUST succeed before OTP is sent — compliance requirement.
Retry once on failure. If still fails → error BE_REG_004, OTP not sent.
7
Generate & Send OTP MODIFY
• Generate 4-digit OTP
• Store in in-memory OTP store (ConcurrentDictionary keyed by mobile number) (key: keyed by mobile number + OTP type, TTL: 5 minutes). Never in DB.
• Send via cascade: SMS (primary) → WhatsApp → Push → RCS
• Record which channel succeeded: otp_channel_used
• If all 4 channels fail: CS Journey CS_OTP_PROVIDER_DOWN
Existing: SEND_RESUME_OTP SP + SingleMessageService SOAP for SMS only. Needs in-memory OTP store migration + cascade channels.
8
Push to External Systems (Async, Non-Blocking)
• CleverTap: registration_proceed_en, lead_created_en
• Zoho CRM: New lead record with mobile_hash, source, location_tag
• RIISE App: Lead created notification
• Datalake: Full lead creation payload
• CDP: Lead creation event
9
Return Response
Success: { status: true, lead_id, otp_sent: true }
Failure: { status: false, error_code, message }
4. API Design
POST /api/v3/registration/initiate
Creates a new lead, validates eligibility, saves consents, sends OTP.
Request Body
| Field | Type | Required | Validation |
mobile_number | string | Yes | 10 digits, starts with 6/7/8/9 |
registration_name | string | Yes | 2-100 chars, alpha + spaces only |
consent_account_opening | boolean | Yes | Must be true |
consent_communication | boolean | Yes | Must be true |
consent_terms | boolean | Yes | Must be true |
session_id | string (UUID) | Yes | Must exist in cache/memory, not expired |
Success Response (HTTP 200)
{
"status": true,
"lead_id": "uuid-here",
"lead_state": "INITIATED",
"otp_sent": true,
"otp_channel_used": "SMS",
"message": null
}
Error Responses
| Error Code | HTTP | Condition | Message |
DROP_NEGATIVE_LIST | 200 | Mobile on MOFSL/SEBI list | This number is not eligible. Please use a different mobile number. |
BE_REG_001 | 200 | Active Demat exists (CBOS) | An active account already exists. Please log in to RIISE. |
BE_REG_002 | 200 | In-progress lead, different channel/RM | This mobile number already has an application in progress. |
BE_REG_003 | 200 | Lead creation failed after 3 retries | Something went wrong. Please try again. |
BE_REG_004 | 200 | Consent save failed after retry | Something went wrong saving your consent. Please try again. |
CS_OTP_PROVIDER_DOWN | 200 | All OTP channels failed | We are having trouble sending your OTP. We will notify you once it is ready. |
5. Eligibility Decision Table — All Combinations
Priority Order
Checks run in parallel. If multiple conditions are true simultaneously, the priority order below determines which outcome fires (top = highest priority).
| Priority | Condition | Outcome | Error Code | Existing SP / Logic |
| 1 |
Mobile on MOFSL negative list OR SEBI debarred list |
Journey ends permanently |
DROP_NEGATIVE_LIST |
NEW — Current TBL_FFF_NOT_ALLOW is partial. Need full MOFSL + SEBI API. |
| 2 |
Active Demat account exists in CBOS for this mobile |
Journey ends |
BE_REG_001 |
EXISTS USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET checks MOSL_FEED + DPMASTER |
| 3 |
In-progress app on OLD platform (< 90 days) |
Redirect to old platform |
— (redirect) |
EXISTS USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET + Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS |
| 4 |
In-progress app on NEW platform (< 90 days), same channel / same BA / same RM |
OTP sent. On verify, resume existing lead. |
— (resume) |
MODIFY Dedupe exists via Proc_DedupeCheck_LeadSquare_LSQ_SJET. Need to add channel/BA/RM comparison. |
| 5 |
In-progress app on NEW platform (< 90 days), DIFFERENT channel or BA/RM |
Journey blocked until day 91 |
BE_REG_002 |
NEW — Channel/BA/RM comparison not in current dedupe logic |
| 6 |
In-progress app in REJECTED or PERMANENTLY_CLOSED state |
Treated as no active app. New lead created. |
— |
MODIFY Extend dedupe to check lead state |
| 7 |
In-progress app in CS_EXPIRED state |
Old lead archived. New lead created. |
— |
NEW — CS_EXPIRED state handling |
| 8 |
No application exists — all checks pass |
New lead created. OTP sent. |
— |
EXISTS USP_CLIENT_REGISTRATION_SJET |
6. Backend Validations
| Check | API Called | Condition | Result if Fails | Blocking? |
| Negative List |
Internal Negative List API NEW |
Mobile found in MOFSL blacklist or SEBI debarred |
DROP_NEGATIVE_LIST — journey ends |
Yes |
| CBOS active account |
CBOS Dedupe API |
Active Demat found for this mobile |
BE_REG_001 — journey ends |
Yes |
| EKYC DB dedupe |
Internal Lead API |
In-progress lead found |
Varies per eligibility table |
Yes (priorities 1-5) |
| Negative List API unavailable |
— |
Service down |
Proceed. Tag NEGATIVE_LIST_CHECK_SKIPPED. Manual check post-eSign. |
No — non-blocking with flag |
| CBOS unavailable |
— |
Service down |
Proceed. Tag CBOS_DEDUPE_SKIPPED. Manual check post-eSign. |
No — non-blocking with flag |
| OTP resend rate limit |
In-memory counter |
Same mobile > 3 OTP resends in 30 min |
Block resend. BE_OTP_002 |
Yes — resend blocked |
| Lead creation retry |
Internal Lead API |
Lead creation fails on first attempt |
Retry 3x at 2-sec intervals. If all fail → BE_REG_003 |
Yes |
| Consent save |
Internal Lead API |
Consent record fails to persist |
Retry once. If still fails → BE_REG_004. OTP not sent. |
Yes |
7. Consent Specification
| Consent | What Customer Agrees To | Stored Fields |
| Account Opening Consent |
Permission to open a Demat account on their behalf with Motilal Oswal |
consent_id, version, timestamp, ip_address, platform, lead_id |
| Communication Consent |
Permission to contact via WhatsApp, SMS, email, push. Also acts as WhatsApp opt-in (TRAI). |
consent_id, version, timestamp, whatsapp_optin = true |
| Terms & Conditions |
Acceptance of platform terms of use and privacy policy |
consent_id, version, timestamp |
Compliance Requirement
Storing the exact version of the consent text is mandatory. If consent text is updated in future, historical records must show what the customer agreed to at the time of consent. This is a new requirement — current code has no consent versioning.
8. OTP Specification
| Parameter | Value | Current Code | Change Needed |
| Length | 4 digits | Exists | No change |
| Validity | 5 minutes from generation | Exists (DB-stored) | TTL enforced by in-memory store |
| Max wrong attempts | 5. After 5th → OTP locked, lead.state = DROPPED, DROP_OTP_LOCKED | Attempt tracking exists | Align state codes |
| Max resend requests | 3 within 30-min window. 4th blocked for 30 min. | Rate limiting exists | Verify thresholds |
| Min wait between resends | 30 seconds | May exist | Enforce via in-memory store |
| Storage | In-memory OTP store (ConcurrentDictionary keyed by mobile number). Never written to application DB. TTL = 5 min. | Stored in DB | NEW Store OTP in server in-memory store (not DB, not Redis) |
| Delivery cascade | SMS → WhatsApp → Push → RCS. Each attempted only if previous fails. | SMS only (Netcore SOAP) | NEW Add WhatsApp, Push, RCS channels |
| All channels fail | CS Journey: CS_OTP_PROVIDER_DOWN. Customer shown paused state. | Error only | NEW Implement CS Journey pause |
9. Existing Stored Procedure Mapping
How existing stored procedures map to BRD Stage 1 requirements:
| BRD Requirement | Existing SP | What It Does Today | Reusable? | Changes Needed |
| Create lead record |
[DIY].[USP_CLIENT_REGISTRATION_SJET] |
5,780-line master SP. Creates user in TBL_DEDUPE_DATA_DUMP, SSO user, branch/RM assignment, campaign routing. |
Partially |
Simplify inputs. Store mobile_hash not plain. Add consent versioning. Remove email (moved to Stage 3). |
| CBOS / Back-office dedupe |
[DIY].[USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET] |
Checks MOSL_FEED, DPMASTER, PAN_MOBILE_EMAIL_WHITELIST for existing active account by mobile. |
Yes |
Wrap as CBOS API adapter. Add CBOS_DEDUPE_SKIPPED flag on unavailability. |
| CRM dedupe |
[DIY].[Proc_DedupeCheck_LeadSquare_LSQ_SJET] |
Checks TBL_DEDUPE_DATA_DUMP, LSQ leads, DAD transfer table, FFF_NOT_ALLOW. |
Partially |
Add channel/BA/RM comparison for Priority 4 vs 5 distinction. |
| 90-day logic |
[DIY].[USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET] |
Checks Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS for non-dormant leads by mobile. |
Yes |
Add old-platform vs new-platform distinction. |
| Bypass logic |
[DIY].[USP_BYPASS_MOBILE_EMAIL_PAN_SJET] |
Checks INACTIVE, PMS, OWNER bypass conditions against MOSL_FEED tables. |
Yes |
No change for Stage 1. Used as-is. |
| Lead discard/re-register |
[DIY].[proc_execute_dad_dedupe_discard_LSQ_SJET] |
Removes duplicate lead for re-registration. Handles DAD-to-Retail transfer. |
Yes |
Add REJECTED / CS_EXPIRED state handling per BRD Priority 6-7. |
| Referral code validation |
[DIY].[USP_VALIDATE_REFERALCODE_SJET] |
Validates referral code against MOSL_FEED_CLIENT_DETAILS.CL_code. |
Yes |
BRD: Referral validated at Stage 0. Can still reuse for backend validation. |
| OTP generation & send |
SEND_RESUME_OTP (SP) + SingleMessageService (SOAP) |
Generates OTP, stores in DB, sends SMS via Netcore SOAP. |
Partially |
Store OTP in server in-memory store (not DB, not Redis). Add WhatsApp/Push/RCS cascade. |
| Negative list check |
TBL_FFF_NOT_ALLOW (table check in dedupe SP) |
Basic fraud/blocked list |
Partially |
NEW Need dedicated Negative List API + SEBI debarred integration. IP-based check. |
10. New Stored Procedures / Logic Needed
| New SP / Logic | Purpose | Key Logic |
USP_CHECK_NEGATIVE_LIST NEW |
Check mobile + IP against MOFSL negative list + SEBI debarred list |
Query negative list table by mobile. Query SEBI API by mobile/PAN. Return PASSED / BLOCKED / SKIPPED. |
USP_SAVE_CONSENT_RECORDS NEW |
Save 3 consent records with versioning |
INSERT into new TBL_LEAD_CONSENTS table. Each row: consent_id, consent_type, version, text_hash, timestamp, ip_address, platform, lead_id. |
USP_ELIGIBILITY_CHECK_V3 NEW |
Unified eligibility check implementing BRD priority table |
Runs: (1) Negative list, (2) CBOS, (3) EKYC dedupe with channel/BA/RM comparison, (4) 90-day logic, (5) Lead state check (REJECTED/CS_EXPIRED). Returns priority-ordered result. |
USP_CREATE_LEAD_V3 NEW |
Simplified lead creation for EKYC 3.0 |
Stores mobile_hash (not plain), registration_name, channel, source, UTM params, device_type, location_tag. Sets lead.state = INITIATED. Returns lead_id (UUID). |
| Channel/BA/RM comparison logic NEW |
In dedupe check, compare channel + BA code + RM code of existing lead vs new request |
If same: allow resume (Priority 4). If different: block (Priority 5). Do NOT reveal which channel owns the lead. |
11. Database Schema — New & Modified Tables
11.1 New Table: TBL_LEAD_CONSENTS
| Column | Type | Nullable | Description |
consent_id | UNIQUEIDENTIFIER | No (PK) | Unique consent record ID |
lead_id | UNIQUEIDENTIFIER | No (FK) | Reference to lead |
consent_type | VARCHAR(50) | No | ACCOUNT_OPENING / COMMUNICATION / TERMS |
version | VARCHAR(20) | No | Version of consent text shown (e.g. "v2.1") |
text_hash | VARCHAR(64) | No | SHA-256 hash of exact consent text shown |
ip_address | VARCHAR(45) | No | Customer IP at time of consent |
platform | VARCHAR(20) | No | WEB_MOBILE / WEB_DESKTOP / ANDROID_APP / IOS_APP |
whatsapp_optin | BIT | Yes | True for Communication consent (TRAI compliance) |
created_at | DATETIME2 | No | Timestamp of consent |
11.2 Modified Table: TBL_DEDUPE_DATA_DUMP
| Change | Column | Type | Description |
| ADD | mobile_hash | VARCHAR(64) | SHA-256 hash of mobile. Replaces plain mobile storage. |
| ADD | channel | VARCHAR(20) | DAD / FRANCHISE / BRANCH |
| ADD | ba_code | VARCHAR(50) | BA code for channel comparison |
| ADD | rm_code | VARCHAR(50) | RM code for channel comparison |
| ADD | lead_state | VARCHAR(30) | INITIATED / OTP_VERIFIED / DROPPED / REJECTED / CS_EXPIRED |
| ADD | drop_code | VARCHAR(50) | DROP_NEGATIVE_LIST / DROP_OTP_LOCKED / DROP_RESET_JOURNEY / etc. |
| ADD | negative_list_check_status | VARCHAR(10) | PASSED / SKIPPED |
| ADD | cbos_dedupe_status | VARCHAR(10) | PASSED / SKIPPED |
11.3 New Table: TBL_NEGATIVE_LIST (if not using external API)
| Column | Type | Description |
id | BIGINT IDENTITY | PK |
mobile_hash | VARCHAR(64) | SHA-256 of blocked mobile |
pan_number | VARCHAR(10) | Blocked PAN (nullable) |
list_source | VARCHAR(20) | MOFSL / SEBI |
reason | VARCHAR(200) | Reason for listing |
added_date | DATETIME2 | Date added to list |
is_active | BIT | Active flag |
12. External Integrations
| System | Type | What It Does at Stage 1 | Call Type | If Unavailable |
| Internal Negative List API NEW |
Internal |
Checks mobile + IP vs MOFSL blacklist and SEBI debarred list |
Sync parallel |
Allow proceed. Tag NEGATIVE_LIST_CHECK_SKIPPED. Ops review post-eSign. |
| CBOS |
Back-office |
Checks if active Demat account exists for this mobile |
Sync parallel |
Allow proceed. Tag CBOS_DEDUPE_SKIPPED. Ops review post-eSign. |
| In-memory store / Cache |
Internal |
OTP: in-memory ConcurrentDictionary keyed by mobile number (TTL 5 min). Session management: cache (TTL 15 min). |
Sync |
CS Journey CS_OTP_PROVIDER_DOWN. |
| Netcore SMS |
3rd Party |
OTP delivery (primary channel). TRAI DND exempt (transactional). |
Async |
Fallback to WhatsApp → Push → RCS. |
| WhatsApp Business API NEW |
3rd Party |
OTP delivery (2nd channel in cascade) |
Async |
Fallback to Push. |
| Push Notification NEW |
3rd Party |
OTP delivery (3rd channel) |
Async |
Fallback to RCS. |
| RCS NEW |
3rd Party |
OTP delivery (4th channel, last resort) |
Async |
CS Journey if all 4 fail. |
| Zoho CRM |
CRM |
New lead record: mobile_hash, source, location_tag, journey_variant_id |
Async (non-blocking) |
Queued for retry. Lead exists in EKYC DB. |
| CleverTap |
Analytics |
Events: registration_proceed_en, lead_created_en, eligibility_failed_en |
Async |
Non-blocking. |
| Datalake |
Internal |
Full lead creation payload: all fields, UTM, device, consent versions |
Async |
Non-blocking. |
13. Error / Drop Codes
| Code | Situation | Customer Impact | Ops Impact |
DROP_NEGATIVE_LIST | Mobile on negative list / SEBI debarred | Journey ends permanently | Logged for audit |
BE_REG_001 | Active Demat exists in CBOS | Journey ends, told to login to RIISE | None |
BE_REG_002 | Different channel/RM owns in-progress lead | Journey blocked, no cross-channel info disclosed | None |
BE_REG_003 | Lead creation failed after 3 retries | Generic error, can retry | Alert to engineering |
BE_REG_004 | Consent save failed | Generic error, OTP not sent | Alert to engineering |
CS_OTP_PROVIDER_DOWN | All 4 OTP channels failed | Paused state, will be notified | CS Journey triggered |
BE_OTP_002 | OTP resend limit reached | Wait 30 minutes | None |
DROP_OTP_LOCKED | 5 wrong OTP attempts | Must restart application | Logged for fraud analysis |
DROP_SESSION_TIMEOUT | 15 min inactivity | Session ended, resume available | Drop analysis |
DROP_RESET_JOURNEY | Customer chose to reset | Mobile freed, can re-register | Behavioural tracking |
14. Exit State — What Is Set When Stage 1 Completes
| Field | Value | Storage |
lead.state | INITIATED | TBL_DEDUPE_DATA_DUMP (or new leads table) |
lead_id | Generated UUID — primary key for all subsequent stages | DB |
registration_name | Full name as entered. Display-only. Does NOT set ekyc_name. | DB |
mobile_hash | SHA-256 hash of mobile. Plain mobile never stored. | DB |
channel | DAD / FRANCHISE / BRANCH (from session) | DB |
source | utm_source from Stage 0 session | DB |
journey_variant_id | Inherited from Stage 0 | DB |
south_tag | SOUTH or OTHERS (from Stage 0) | DB |
device_type | WEB_MOBILE / WEB_DESKTOP / ANDROID_APP / IOS_APP | DB |
consent_1_version | Version string of Account Opening Consent text | TBL_LEAD_CONSENTS |
consent_2_version | Version string of Communication Consent text | TBL_LEAD_CONSENTS |
consent_3_version | Version string of T&C text | TBL_LEAD_CONSENTS |
otp_sent_at | Timestamp of first OTP send | DB |
otp_channel_used | SMS / WHATSAPP / PUSH / RCS | DB |
negative_list_check_status | PASSED / SKIPPED | DB |
cbos_dedupe_status | PASSED / SKIPPED | DB |
15. System Interactions (Post-Lead Creation)
| System | What Is Sent | When |
| CleverTap | registration_proceed_en, lead_created_en, eligibility_failed_en | On lead creation or eligibility failure |
| Zoho CRM | New lead: mobile_hash, source channel, south/others tag, journey_variant_id, timestamp | On lead creation |
| RIISE App | Lead created notification: lead_id, channel, source | On lead creation |
| GCM | Lead created: lead_id, mobile_hash, channel | On lead creation |
| Datalake | Full payload: all fields, UTM params, device, journey variant, consent versions | On lead creation |
| CDP | Lead creation event: mobile_hash, channel, UTM params, journey_variant_id | On lead creation |
16. Implementation Task Checklist
| # | Task | Type | Priority | Depends On |
| 1 | Create TBL_LEAD_CONSENTS table | NEW | P0 | — |
| 2 | Add columns to TBL_DEDUPE_DATA_DUMP: mobile_hash, channel, ba_code, rm_code, lead_state, drop_code, negative_list_check_status, cbos_dedupe_status | MODIFY | P0 | — |
| 3 | Create TBL_NEGATIVE_LIST table (or integrate MOFSL/SEBI external API) | NEW | P0 | — |
| 4 | Build USP_CHECK_NEGATIVE_LIST SP | NEW | P0 | Task 3 |
| 5 | Build USP_SAVE_CONSENT_RECORDS SP | NEW | P0 | Task 1 |
| 6 | Build USP_ELIGIBILITY_CHECK_V3 SP with full priority table | NEW | P0 | Tasks 2, 4 |
| 7 | Build USP_CREATE_LEAD_V3 SP (simplified lead creation with mobile_hash) | NEW | P0 | Task 2 |
| 8 | Implement in-memory OTP store via ConcurrentDictionary keyed by mobile number (replace DB storage, not Redis) | MODIFY | P0 | — |
| 9 | Build OTP cascade service: SMS → WhatsApp → Push → RCS | NEW | P1 | — |
| 10 | Build /api/v3/registration/initiate API endpoint | NEW | P0 | Tasks 4-8 |
| 11 | Implement lead creation retry (3x at 2-sec intervals) | NEW | P1 | Task 10 |
| 12 | Add channel/BA/RM comparison to dedupe logic (Priority 4 vs 5) | NEW | P0 | Task 6 |
| 13 | Add REJECTED / CS_EXPIRED state handling in dedupe (Priority 6-7) | MODIFY | P1 | Task 6 |
| 14 | Implement Redis session management (15-min TTL) | NEW | P0 | — |
| 15 | Implement async push to CleverTap, Zoho, RIISE, Datalake, CDP | MODIFY | P1 | Task 10 |
| 16 | Implement CS Journey pause flow for CS_OTP_PROVIDER_DOWN | NEW | P1 | Task 9 |
| 17 | Implement graceful degradation flags (NEGATIVE_LIST_CHECK_SKIPPED, CBOS_DEDUPE_SKIPPED) | NEW | P0 | Tasks 4, 6 |
| 18 | Add OTP resend rate limiting in in-memory store (3 resends / 30 min, 30-sec cooldown) | MODIFY | P1 | Task 8 |
| 19 | Implement SHA-256 mobile hashing utility | NEW | P0 | — |
| 20 | Unit + integration tests for eligibility decision table (all 8 priorities) | NEW | P0 | Tasks 6, 10 |
Reuse Summary
Out of the 8 BRD eligibility rules, 3 are fully covered by existing SPs (CBOS check, 90-day logic, bypass), 2 need modifications (CRM dedupe + lead states), and 3 are new (negative list, channel comparison, CS_EXPIRED handling).
The existing reference data tables (MOSL_FEED_*, TBL_DPMASTER, TBL_PAN_MOBILE_EMAIL_WHITELIST, TBL_STATE_CITY_MASTER_LSQ) are all reusable as-is.