EKYC 3.0 — Sync & Reference Tables Master List

All tables that must be populated / synced from old system or external sources • Updated per stage analysis
How to read this doc: SYNC = needs scheduled data sync from old system/API. POPULATE = one-time or manual load from docs/config. NEW TABLE = table we created, has no old equivalent. ENTITY EXISTS = entity defined in new backend code. This doc will be updated as each new stage is analysed.

1. Tables Requiring Data Sync from Old Back-Office (MOSL_FEED / BO)

New Table (Entity)Old Source Table / APISync FreqKey Columns to SyncUsed In StageStatus
client_master MOSL_FEED_CLIENT_DETAILS Daily ClientCode, PanHash, Mobile, Email, ClientName, ProductType, ClCode, IsActive, InactiveDate, BranchCode, Segments Stage 1 (CBOS check, bypass, offline client, referral) ENTITY EXISTS SYNC NEEDED
branch_master MOSL_FEED_BRANCH Daily BranchCode, Mobile, BaEmail, CorrEmail, PanHash, Code1200 Stage 1 (OWNER bypass for branch-owned mobiles/emails) ENTITY EXISTS SYNC NEEDED
subbroker_master MOSL_FEED_SUBBROKERS Daily SubbrokerCode, Mobile, BaEmail, CorrEmail, PanHash, Code1200 Stage 1 (OWNER bypass for subbroker-owned mobiles) ENTITY EXISTS SYNC NEEDED
employee_pan_master MOSL_SDLC_EMPLOYEEDETAILS / HR system Daily PanHash, EmpName, EmpActiveStatus, ResignedRaisedOn, HrStatus, DepartmentStatus Stage 4 (Staff PAN check — block/redirect) ENTITY EXISTS SYNC NEEDED
franchise_pan_whitelist Franchise operations team (manual upload) On-demand PanHash, FranchiseName, Mobile, PanType Stage 4 (Franchise PAN — allow but tag) ENTITY EXISTS SYNC NEEDED

2. Tables Requiring Sync from External / Regulatory Sources

New Table (Entity)Old Source Table / APISync FreqKey ColumnsUsed In StageStatus
negative_list_entries TBL_FFF_NOT_ALLOW + SEBI debarred list Daily + on SEBI update IdentifierType (MOBILE/PAN/IP), IdentifierHash, Source (MOFSL/SEBI), Reason, ExpiresAt Stage 1 (Negative list check) ENTITY EXISTS SYNC NEEDED
bank_ifsc_master RBI IFSC directory Monthly IfscCode, BankName, BranchName, MicrCode, City, State Stage 6 (Bank IFSC lookup) ENTITY EXISTS SYNC NEEDED

3. Tables Requiring One-Time / Manual Population

New Table (Entity)Old Source / ReferenceKey ColumnsUsed In StageStatus
restricted_mobiles Operations / Compliance team list Mobile, MobileHash, Reason, IsActive Stage 1 (Block restricted mobiles) ENTITY EXISTS POPULATE
restricted_email_domains TBL_RESTRICT_EMAIL_DOMAIN Domain, Reason, IsActive Stage 3 (Block disposable email domains) ENTITY EXISTS POPULATE
suspicious_contacts Usp_GetSuspiciousPhoneOrEMailId source table IdentifierHash, IdentifierType (MOBILE/EMAIL), Reason Stage 1, 3 (Flag for ops, don't block) ENTITY EXISTS POPULATE
duplicate_bypass_whitelist TBL_PAN_MOBILE_EMAIL_WHITELIST PanHash, Mobile, Email, Reason, IsActive Stage 1 (Allow pre-approved duplicates) ENTITY EXISTS POPULATE
allowed_email_domains TBL_ALLOW_EMAIL Domain, Reason, IsActive Stage 3 (Whitelist — bypass format checks) NEW TABLE NEEDED POPULATE
cvl_status_mapping BRD Section 19 mapping table CvlCode, InternalStatus (KRA_VALIDATED / KRA_MOD / NON_KRA / RESTRICTED / INVALID_PAN) Stage 2 (KRA raw code mapping) ENTITY EXISTS POPULATE
nsdl_error_master NSDL documentation ErrorCode, ErrorMessage, IsFatal Stage 2, 4 (NSDL error handling) ENTITY EXISTS POPULATE
referral_codes MOSL_FEED_CLIENT_DETAILS.CL_code + marketing Code, ReferrerName, Channel, IsActive, ExpiresAt Stage 0-1 (Referral validation) ENTITY EXISTS POPULATE
state_master TBL_STATE_CITY_MASTER_LSQ StateCode, StateName, IsSouth Stage 0 (South tag), Stage 9 (Address) ENTITY EXISTS POPULATE
city_master MST_CITY_STATE CityName, StateCode, IsMetro Stage 0 (Geo), Stage 9 (Address) ENTITY EXISTS POPULATE
pincode_master India Post / old pincode table Pincode, City, District, State, StateCode Stage 9 (Address validation) ENTITY EXISTS POPULATE
application_config TBL_APPLICATIONCONFIG SettingKey, SettingValue, Category, IsActive All stages (STP thresholds, feature flags, campaign routing keys) ENTITY EXISTS POPULATE
provider_configurations appsettings.json (migrate to DB) ProviderCategory, ProviderName, Priority, BaseUrl, TimeoutMs, MaxRetries, IsEnabled All stages (External provider config) ENTITY EXISTS POPULATE
location_exception_whitelist TBL_LIVE_PHOTO_LOCATION_EXCEPTION PanHash, Reason, IsActive Stage 7 (Location bypass for specific PANs) NEW TABLE NEEDED POPULATE
personal_field_master TBL_PERSONAL_FIELD_MASTER_SJET Category, Code, DisplayName, SortOrder, IsActive Stage 9 (All dropdown values for personal details, nominee, etc.) NEW TABLE NEEDED POPULATE

4. Summary

CategoryCountAction
Daily sync from MOSL back-office5Build sync jobs (client_master, branch_master, subbroker_master, employee_pan, franchise_pan)
Sync from external/regulatory2Build sync jobs (negative_list from SEBI, bank_ifsc from RBI)
One-time / manual populate15Load data from old tables, BRD docs, or config files
New table to create3allowed_email_domains + location_exception_whitelist + personal_field_master (no entities exist yet)
Total22

Last updated: Stage 1 through Stage 15 analysis complete. ALL 16 STAGES COMPLETE. No new sync tables needed for Stage 14/15. Product decisions applied: No Redis for OTP (in-memory store), No S3 (configured drive via document_path_info), Address translation via AiNXT only, No OCR PAN O/Q correction, No DigiLocker PAN cross-check. Stage 6 (Bank Verification) and Stage 7 (Liveness) added location_exception_whitelist as new table. Stage 8 (Signature) and Stage 9 (Personal Details) added personal_field_master as new table. Stage 10 (Income Proof) and Stage 11 (Final Validation) require no new sync tables — all reference data already covered by previous stages. Stage 12 (KRA Re-Check & Document Generation) and Stage 13 (eSign) require no new sync tables — CVL status mapping already synced, all other reference data covered by previous stages. Stage 14 (Account Creation) and Stage 15 (Activation) require no new sync tables — all data covered by existing entities (account_activations, fund_transfers, downstream_events, notifications).