EKYC 3.0 — Registration: Backend Developer Guide

New User Sign-Up Flow • Backend API, Stored Procedures, Database Tables & Business Logic
Document Type: Backend Implementation Reference BRD Stage: Pre-Stage / Stage 1 (Registration) Version: 1.0

Table of Contents

  1. Overview & Scope
  2. Existing API Endpoints (Current Code)
  3. SignUp Flow — Detailed Backend Logic
  4. Stored Procedures — Registration
  5. Database Tables & Reference Data
  6. Dedupe / Eligibility Logic
  7. External API Integrations
  8. BRD 3.0 Requirements — What Changes
  9. Gap Analysis: Current vs BRD 3.0

1. Overview & Scope

The Registration module handles new user sign-up for the EKYC Demat Account Opening journey. When a customer enters their mobile number and taps Proceed, the backend performs eligibility checks (negative list, existing account dedupe, 90-day logic), creates a lead record, and triggers OTP delivery.

Existing Code Location D:\MO_Project\ekyc\existing_code\Joruney_imp_code\Registration\RegistrationController.cs
D:\MO_Project\ekyc\existing_code\Joruney_imp_code\Registration\RegistrationRepository.cs
Stored Procedure Constants: Joruney_imp_code\Common\StoreProcedures.cs

2. Existing API Endpoints (Current Code)

Controller: RegistrationController — Route: api/Registration/{action}

Action NameHTTPAuthPurposeRepository Method
SIGNUP_OLDPOSTAnonymousMain registration (legacy)SignUp()
SignUpAsyncPOSTAnonymousAsync registration (current)SignUp()
VALIDATEREFERALCODEPOSTAnonymousValidate referral/BA codeValidateReferalCode()
CHECKMOBILEVERSIONPOSTAnonymousCheck app version compatibilityCheckMobileVersion()
INSERTUPDATESTAGEDETAILSPOSTAnonymousSave/update user journey stageInsertUpdateStageDetails()
GETUSERDATA_TRUECALLERPOSTAnonymousGet data from Truecaller SDKGetUserData_TrueCaller()
TRUECALLERCALLBACKPOSTAnonymousTruecaller callback handlerTruecallerCallback()
GETPINCODEDETAILSPOSTAnonymousValidate/return pincode detailsGetPincodeDetails()
LIVEPHOTOLOCATIONDETAILSPOSTAuthorizedRecord live photo + locationLivePhotoLocationDetails()
GeoLocationPOSTAnonymousRecord user geolocationGeoLocation()

3. SignUp Flow — Detailed Backend Logic

Entry point: RegistrationController.SignUp() / SignUpAsync()RegistrationRepository.SignUp()

Step-by-Step Backend Execution

1
Input ValidationCheckDangerousString() validates all input fields for SQL injection / XSS. chk_PhoneNo() validates Indian mobile format (10 digits, starts with 6/7/8/9). For NRI: chk_PhoneNo_NRI() with relaxed rules.
2
Application Type Resolution — Determines application type based on inputs:
• NRI + MOMEAST branch → ApplicationType = "DIRECT"
• AppLoginId = IFAFINCARE → ApplicationType = "CRM", FlowType = "DIY"
• CampaignName = FSFB3IN1 → FlowType = "APP"
• ApplicationType = PP + BACODE present → IsBaProspect = true
3
Call RegistrationRepository.SignUp() — Passes 40+ parameters including mobile, name, email, state, city, source, OTP, campaign, device info, UTM params, referral codes, branch codes.
4
Stored Procedure: [DIY].[USP_CLIENT_REGISTRATION_SJET] — This is the core registration SP (5,780+ lines). It performs:
• Dedupe check against TBL_DEDUPE_DATA_DUMP by mobile/email
• SSO user creation in MOSL_SSO.dbo.tblUser
• State/city validation against TBL_STATE_CITY_MASTER_LSQ
• Branch/RM assignment logic
• Campaign-specific routing
• Returns UserId + DEDUPE_PARAM (N/EPO/MPO/C2)
5
LeadSquare / Zoho CRM Lead Creation — If application is DIRECT, calls Proc_DedupeCheck_LeadSquare_LSQ_SJET to check CRM dedupe, then creates lead in LSQ or Zoho depending on IsZohoLead flag.
6
OTP Generation & Delivery — Generates OTP, stores in DB, sends via SMS cascade using SingleMessageService SOAP API (Netcore). Template from SMSTemplate.
7
Response — Returns GetLoginDetails with UserId, Status, Message, Token, and redirect information.

4. Stored Procedures — Registration

4.1 Core Registration SP

Stored ProcedureCalled FromPurpose
[DIY].[USP_CLIENT_REGISTRATION_SJET] CORE RegistrationRepository.SignUp() Master registration procedure. Creates user record, runs dedupe, assigns branch/RM, creates SSO user. 5,780+ lines.

USP_CLIENT_REGISTRATION_SJET — Key Parameters (200+)

CategoryKey Parameters
Basic@Name, @Email, @Mobile, @State, @City, @Password
Source / Campaign@PSource, @PSourceDetail, @Source, @CampaignName, @utm_source, @utm_medium, @utm_campaign, @GCLID
Application@ApplicationType, @AppLoginId, @ISNRI, @IsLeadDistribute, @IsSuperJet, @IsOcrUser
Device@IsMobileApp, @DeviceType, @FirebaseToken, @MobileDeviceId, @AppVersion, @IpAddress
Employee / RM@BranchRM_Empcode, @RMCode, @Emp_Code, @Emp_Name, @DAD_Advisor_Emp_Code
Referral@ReferalClientCode, @Referee_Code, @Referee_Name, @Referee_Email, @Referee_Mobile

USP_CLIENT_REGISTRATION_SJET — Business Logic Inside the SP

A
Dedupe Check: Checks TBL_DEDUPE_DATA_DUMP for existing mobile/email. Sets DEDUPE_PARAM = N (new) / EPO (email prev owned) / MPO (mobile prev owned) / C2 (caught in stage transition)
B
Product Transition: If deduped, checks PRODUCT_TRANSITION_FLAG and transfers to acquiring business
C
State/City Validation: Validates against TBL_STATE_CITY_MASTER_LSQ with fallback to MST_CITY_STATE
D
Branch/RM Assignment: Complex routing for CRM sources (Alliance, IDFC, INDUSIND, AUSFBDIRECT, SBOM, DBIZ, SIB, CSFB). IFSC-to-RM mapping for INDUSIND.
E
Campaign Logic: IIBLMICROSITE, AUFSB, CBI, BA Self-Direct each have custom RM assignment and branch routing
F
SSO User Creation: Creates user in MOSL_SSO.dbo.tblUser + USR_ADDITIONAL_INFO if not exists. Generates encrypted credentials.
G
INSERT into TBL_DEDUPE_DATA_DUMP: Creates the main registration record

4.2 Supporting Stored Procedures

Stored ProcedureCalled FromPurposeKey Tables
[DIY].[Proc_DedupeCheck_LeadSquare_LSQ_SJET] RegistrationRepository CRM dedupe check. Checks if mobile/email exists in CRM. Returns LSQ_LEADID, OPPORTUNITY_ID. Checks TBL_FFF_NOT_ALLOW for fraud blocked list. TBL_DEDUPE_DATA_DUMP TBL_LSQ_LEADID_STAGEDETAILS TBL_DAD_TO_RETAIL_TRANSFERRED_LEADS TBL_FFF_NOT_ALLOW
[DIY].[Proc_Dedupe_UserIDExistProcess_Revamp_LSQ_SJET] RegistrationRepository Checks if OAO UserId already exists. Handles product transition. Routes non-DIRECT apps to NEW_CRM database. TBL_DEDUPE_DATA_DUMP TBL_OAO_DETAILS
[DIY].[proc_execute_dad_dedupe_discard_LSQ_SJET] RegistrationRepository Discards/removes existing duplicate lead for re-registration. Handles DAD-to-Retail transfer. TBL_DEDUPE_DATA_DUMP TBL_ZOHOEKYC_MAPPER
[DIY].[USP_VALIDATE_REFERALCODE_SJET] RegistrationController Validates referral code against MOSL_FEED_CLIENT_DETAILS.CL_code. Returns referral name if valid. MOSL_FEED_CLIENT_DETAILS
[DIY].[USP_GET_REGISTRATIONDETAILS_RESUME_SJET] RegistrationRepository Resume existing registration. Looks up mobile in TBL_DEDUPE_DATA_DUMP. Checks if OTP verified, if LSQ/Zoho ID exists. May delete only-registered clients. TBL_DEDUPE_DATA_DUMP TBL_CLIENT_STAGEDETAILS TBL_LSQ_LEADID_STAGEDETAILS
[DIY].[USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET] RegistrationRepository Checks if mobile already exists in back-office. Checks across MOSL_FEED, TBL_DPMASTER, TBL_PAN_MOBILE_EMAIL_WHITELIST. VW_MOSL_FEED_CLIENT_DETAILS TBL_DPMASTER TBL_PAN_MOBILE_EMAIL_WHITELIST
[DIY].[USP_CHECK_MULTIPLE_USER_EXISTS_SJET] LoginRepository Counts records in TBL_DEDUPE_DATA_DUMP matching mobile/email with DedupParam IN (N, EPO, MPO). Returns 1 if multiple users found. TBL_DEDUPE_DATA_DUMP
[DIY].[USP_BYPASS_MOBILE_EMAIL_PAN_SJET] RegistrationRepository Bypass logic for duplicate mobile/email/PAN. Checks INACTIVE, PMS, OWNER (Branch/SubBroker) conditions against MOSL_FEED tables. Sets ISBYPASS flag. MOSL_FEED_CLIENT_DETAILS MOSL_FEED_BRANCH MOSL_FEED_SUBBROKERS TBL_TRACK_BYPASS_MOBILE_EMAIL_PAN
[DIY].[USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET] LoginRepository 90-day dormant lead check. Queries Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS for non-dormant leads. Returns OpportunityId and RelatedProspectId. Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS
[DIY].InsertApplicationStepsLog_SJET RegistrationRepository Logs each registration step for tracking/audit Application Steps Log table

5. Database Tables & Reference Data

5.1 Core Tables (Read/Write)

TableR/WPurposeKey Columns
TBL_DEDUPE_DATA_DUMPR/WPrimary registration/dedupe table. Every registration creates or updates a record here.MOBILE, EMAIL, NAME, PANNO, DedupParam (N/EPO/MPO/C2), ISMOBILEBYPASS, ISEMAILBYPASS, ApplicationType, PRODUCT_TRANSITION_FLAG, OTPMobile, PSOR CE
TBL_OAO_DETAILSR/WAccount opening opportunity details. Stores campaign, product, ISJET, ISSUPERJET flags.USERID, ProductTransitionFlag, ISOPPORTUNITY, ISSUPERJET, ISSUPERAPP, ISPANBYPASS
TBL_CLIENT_STAGEDETAILSR/WStage progression tracking. Tracks which stage each user has completed.USERID, CLIENTCODEGENERATED, AccountType, HolderType
TBL_LSQ_LEADID_STAGEDETAILSR/WLeadSquare lead ID and stage tracking.USERID, LSQ_LEADID, OPPORTUNITY_ID
MOSL_SSO.dbo.tblUserR/WSSO user account. Created during registration.USER_LOGINNAME, Encrypted Password, IP, Device
MOSL_SSO.dbo.USR_ADDITIONAL_INFOWriteAdditional SSO user infoUser demographics, device info
TBL_TRACK_BYPASS_MOBILE_EMAIL_PANWriteLogs bypass decisions for auditTYPE, VALUE, VALUE_TYPE, ISBYPASS, CREATEDDATE

5.2 Reference / Master Tables (Read Only)

TablePurposeUsed In
MOSL_FEED_CLIENT_DETAILSBack-office client master. Used for mobile/email/PAN duplicate check and bypass logic.USP_BYPASS_MOBILE_EMAIL_PAN, USP_CHECK_MOBILE_EXISTS_BO_EXP, USP_VALIDATE_REFERALCODE
MOSL_FEED_BRANCHBranch master with mobile/email for branch ownersUSP_BYPASS_MOBILE_EMAIL_PAN
MOSL_FEED_SUBBROKERSSub-broker master with mobile/email for sub-broker ownersUSP_BYPASS_MOBILE_EMAIL_PAN
TBL_STATE_CITY_MASTER_LSQState/city reference for LSQ complianceUSP_CLIENT_REGISTRATION_SJET
MST_CITY_STATEFallback city/state masterUSP_CLIENT_REGISTRATION_SJET
TBL_PAN_MOBILE_EMAIL_WHITELISTWhitelist for bypassing duplicate checksUSP_CHECK_MOBILE_EXISTS_BO_EXP
TBL_DPMASTERDP master with CM_MOBILE and CB_PANNOUSP_CHECK_MOBILE_EXISTS_BO_EXP
TBL_APPLICATIONCONFIGSystem configuration / feature flagsUSP_CLIENT_REGISTRATION_SJET
VW_CRM_EMPLOYEEWISE_BRANCH_TRADERCODEEmployee-branch mapping viewUSP_CLIENT_REGISTRATION_SJET
TBL_ALLIANCE_RM_BRANCH_LISTAlliance RM routingUSP_CLIENT_REGISTRATION_SJET
TBL_IDFCCustomersDetailsIDFC customer dataUSP_CLIENT_REGISTRATION_SJET
TBL_FFF_NOT_ALLOWFraud / blocked listProc_DedupeCheck_LeadSquare_LSQ_SJET
Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADSDAD-to-Retail migration trackingUSP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET
TBL_ZOHOEKYC_MAPPERZoho-EKYC system mappingproc_execute_dad_dedupe_discard
MOSL_SDLC_EMPLOYEEDETAILSEmployee details for RM assignmentUSP_CLIENT_REGISTRATION_SJET

6. Dedupe / Eligibility Logic

6.1 Dedupe Decision Flow (Current Code)

1
USP_CLIENT_REGISTRATION_SJET checks TBL_DEDUPE_DATA_DUMP for existing mobile/email match
2
Sets DEDUPE_PARAM: N = new, EPO = email previously owned, MPO = mobile previously owned, C2 = caught in transition
3
Proc_DedupeCheck_LeadSquare_LSQ_SJET checks CRM-level dedupe including TBL_DAD_TO_RETAIL_TRANSFERRED_LEADS
4
USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET checks back-office (MOSL_FEED, DPMASTER) for active Demat account with same mobile
5
USP_BYPASS_MOBILE_EMAIL_PAN_SJET evaluates if bypass is allowed: INACTIVE account, PMS-only, OWNER (Branch/SubBroker)
6
USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET checks 90-day rule on Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS

6.2 Bypass Conditions (USP_BYPASS_MOBILE_EMAIL_PAN_SJET)

TypeConditionBypass Allowed?VALUE_TYPE Set
MOBILEAll matching records in MOSL_FEED have InactiveDate set (all inactive)YesINACTIVE
MOBILENo matching record with Product_Type NOT IN ('PMS', 'RPS')YesPMS
MOBILEMobile belongs to Branch owner (MOSL_FEED_BRANCH) and no active client record with different CL_CODEYesOWNER
MOBILEMobile belongs to SubBroker (MOSL_FEED_SUBBROKERS) same logicYesOWNER
EMAILSame as MOBILE but checks EmailId columnSame rulesSame
PANPAN inactive, or PAN belongs to Branch/SubBrokerSame rulesSame

7. External API Integrations

External SystemPurpose in RegistrationCall TypeFailure Handling
LeadSquare CRMCreate lead, create opportunity, activity loggingAsync HTTPLead created in EKYC DB. LSQ sync retried later.
Zoho CRMCreate/update lead (alternative to LSQ based on IsZohoLead flag)HTTP PUT /crm/v7/Leads/{id}Lead Status update deferred. Token refresh on 401.
Netcore SMS (SingleMessageService)OTP delivery via SMSSOAP AsyncLogged to DB. Customer cannot proceed without OTP.
FirebaseShort URL generation, push notification setupHTTPNon-blocking. Graceful degradation.
Truecaller SDKAuto-fill name/mobile from Truecaller profileSDK callbackFalls back to manual entry.
SuperApp APICloud lead management, client code syncHTTPLead created locally. SuperApp sync retried.

8. BRD 3.0 Requirements — What Changes

BRD 3.0 — Registration (Stage 1) New Backend Requirements These are the key backend changes required vs current implementation.
BRD RequirementCurrent Code StatusAction Needed
Mobile number + Full Name + 3 mandatory consents as input Current code accepts mobile + many more fields. Email is optional at registration. No explicit consent tracking per version. Simplify registration input. Add consent versioning: consent_id, version, timestamp, ip_address, platform, lead_id
Mobile SHA-256 hash stored. Plain mobile never in DB. Current code stores plain mobile in TBL_DEDUPE_DATA_DUMP New: Implement SHA-256 hashing. Store mobile_hash only.
Negative List check on mobile + IP Current code has TBL_FFF_NOT_ALLOW fraud list but no IP-based negative list New: Add MOFSL negative list + SEBI debarred list check API. IP check.
CBOS check for existing active Demat account Current: USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET checks MOSL_FEED + DPMASTER Map existing BO check to CBOS API. Add CBOS_DEDUPE_SKIPPED flag if API unavailable.
In-progress application on old platform (< 90 days) → redirect Current: USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET handles 90-day logic Existing. Enhance to differentiate old vs new platform.
Same channel/BA/RM resume; different channel block Current dedupe does not check channel/BA/RM match New: Add channel, BA code, RM code comparison in dedupe logic.
OTP: 4-digit, 5-min TTL in Redis only, never in DB Current: OTP stored in DB. Delivery via SMS only (Netcore SOAP). New: Move OTP to Redis. 4-digit. TTL 5 min. Cascade: SMS → WhatsApp → Push → RCS.
OTP max 5 wrong attempts → DROPPED. Max 3 resends in 30 min. Current has attempt limits but different thresholds. Align thresholds. Add DROP_OTP_LOCKED code.
Lead creation retry: up to 3 times at 2-sec intervals Current: Single attempt with generic error New: Implement retry with backoff.
Consent save must succeed before OTP is sent Current: No explicit consent-before-OTP gate New: Consent persistence as prerequisite to OTP.
Negative List / CBOS unavailable → flag on lead, proceed Current: No graceful degradation flags New: Add NEGATIVE_LIST_CHECK_SKIPPED, CBOS_DEDUPE_SKIPPED flags.
Session: 15-min timeout, Redis-backed Current: Session in .NET context, not Redis-backed New: Redis session with 15-min TTL, reset on interaction.

9. Gap Analysis: Current vs BRD 3.0

AreaCurrent ImplementationBRD 3.0 RequirementGap Severity
Data PrivacyPlain mobile stored in DBSHA-256 hash onlyHIGH
Consent TrackingNo versioned consent records3 consents with version, timestamp, IP, platformHIGH
OTP StorageDatabaseRedis only, never in DBMEDIUM
OTP DeliverySMS only (Netcore SOAP)Cascade: SMS → WhatsApp → Push → RCSMEDIUM
Negative ListTBL_FFF_NOT_ALLOW (fraud list)MOFSL Negative List + SEBI Debarred List + IP checkMEDIUM
Channel-based DedupeNot implementedSame channel/BA/RM = resume; different = blockHIGH
Session Management.NET context basedRedis with 15-min TTLMEDIUM
Lead Creation RetrySingle attempt3 retries at 2-sec intervalsLOW
Drop/Error CodesGeneric error messagesSpecific codes: DROP_NEGATIVE_LIST, BE_REG_001-004, DROP_OTP_LOCKEDMEDIUM
Audit TrailBasic logging via InsertUpdateAPI_FlagwiseFull audit: lead_id, stage_at_reset, timestamp, rm_idMEDIUM
Existing SP Reuse Recommendation USP_CLIENT_REGISTRATION_SJET is 5,780+ lines and deeply coupled. For EKYC 3.0, consider decomposing into smaller focused SPs: (1) Dedupe check, (2) Lead creation, (3) SSO user creation, (4) Branch/RM assignment, (5) Campaign routing. Existing reference data tables (MOSL_FEED_*, TBL_STATE_CITY_MASTER_LSQ, etc.) can be reused as-is.