EKYC 3.0 - DB Actions Checklist | ConceptGood Consultants

These tables need automated pipelines that run on a schedule. Entity + table already exist. You need to build the ETL/sync code + schedule it.

#	New Table	Old Source	Frequency	Columns to Sync	Used In	Action
1	`client_master`	`MOSL_FEED_CLIENT_DETAILS`	Daily	ClientCode, PanHash, Mobile, Email, ClientName, ProductType, ClCode, IsActive, InactiveDate, BranchCode, Segments	Stage 1	BUILD SYNC JOB
2	`branch_master`	`MOSL_FEED_BRANCH`	Daily	BranchCode, Mobile, BaEmail, CorrEmail, PanHash, Code1200	Stage 1	BUILD SYNC JOB
3	`subbroker_master`	`MOSL_FEED_SUBBROKERS`	Daily	SubbrokerCode, Mobile, BaEmail, CorrEmail, PanHash, Code1200	Stage 1	BUILD SYNC JOB
4	`employee_pan_master`	`MOSL_SDLC_EMPLOYEEDETAILS`	Daily	PanHash, EmpName, EmpActiveStatus, ResignedRaisedOn, HrStatus, DepartmentStatus	Stage 4	BUILD SYNC JOB
5	`franchise_pan_whitelist`	Franchise ops team (manual CSV)	On-demand	PanHash, FranchiseName, Mobile, PanType	Stage 4	BUILD SYNC JOB
6	`negative_list_entries`	`TBL_FFF_NOT_ALLOW` + SEBI debarred list	Daily + SEBI trigger	IdentifierType (MOBILE/PAN/IP), IdentifierHash, Source (MOFSL/SEBI), Reason, ExpiresAt	Stage 1	BUILD SYNC JOB
7	`bank_ifsc_master`	RBI IFSC directory	Monthly	IfscCode, BankName, BranchName, MicrCode, City, State	Stage 6	BUILD SYNC JOB

Sync job pattern: Each job should (1) connect to old DB / download file, (2) hash sensitive fields (PAN, mobile) before inserting, (3) upsert into new table by primary/unique key, (4) log row counts & errors to api_audit_logs, (5) run via cron or Hangfire on schedule shown above.

B. One-Time Data Load (Copy from Old DB / Docs)

These tables need a one-time data migration from old system tables or manual entry from BRD docs. Entity + table already exist. Run the migration SQL once.

C. Seed Data Already Scripted (Just Run the SQL)

#	New Table	Copy From	Key Columns	Used In	Row Est.	Action
8	`restricted_mobiles`	Ops / Compliance team list	Mobile, MobileHash, Reason, IsActive	Stage 1	~50-200	ONE-TIME LOAD
9	`restricted_email_domains`	`TBL_RESTRICT_EMAIL_DOMAIN`	Domain, Reason, IsActive	Stage 3	~100-500	ONE-TIME LOAD
10	`suspicious_contacts`	`Usp_GetSuspiciousPhoneOrEMailId` source	IdentifierHash, IdentifierType, Reason	Stage 1, 3	~500-2K	ONE-TIME LOAD
11	`duplicate_bypass_whitelist`	`TBL_PAN_MOBILE_EMAIL_WHITELIST`	PanHash, Mobile, Email, Reason, IsActive	Stage 1	~100-1K	ONE-TIME LOAD
12	`allowed_email_domains`	`TBL_ALLOW_EMAIL`	Domain, Reason, IsActive	Stage 3	~50-200	ONE-TIME LOAD
13	`location_exception_whitelist`	`TBL_LIVE_PHOTO_LOCATION_EXCEPTION`	PanHash, Reason, IsActive	Stage 7	~10-50	ONE-TIME LOAD
14	`referral_codes`	`MOSL_FEED_CLIENT_DETAILS.CL_code` + marketing	Code, ReferrerName, Channel, IsActive, ExpiresAt	Stage 0-1	~1K-10K	ONE-TIME LOAD
15	`state_master`	`TBL_STATE_CITY_MASTER_LSQ`	StateCode, StateName, IsSouth	Stage 0, 9	~36	ONE-TIME LOAD
16	`city_master`	`MST_CITY_STATE`	CityName, StateCode, IsMetro	Stage 0, 9	~5K	ONE-TIME LOAD
17	`pincode_master`	India Post / old pincode table	Pincode, City, District, State, StateCode	Stage 9	~30K	ONE-TIME LOAD

These tables already have seed SQL scripts in backend/database/. The data is config/BRD-derived and doesn't need old DB access. Just execute the scripts.

D. Migration SQL Templates (Copy-Paste for One-Time Loads)

Sample SQL to migrate data from old tables into new tables. Adapt connection/schema as needed.

D1. restricted_email_domains (from old TBL_RESTRICT_EMAIL_DOMAIN)

#	New Table	Seed Script	Content	Used In	Action
18	`lookups` (= personal_field_master)	`03_seed_data.sql` + `11_seed_lookups.sql`	12 categories: OCCUPATION, EDUCATION, ANNUAL_INCOME, MARITAL_STATUS, NOMINEE_RELATIONSHIP, GENDER, INVESTMENT_EXPERIENCE, SETTLEMENT_PREFERENCE, DIS_BOOKLET, MTF, RELATION, GUARDIAN_RELATIONSHIP	Stage 9	SEED SQL EXISTS
19	`cvl_status_mapping`	`03_seed_data.sql`	KRA_VALIDATED, KRA_MOD, NON_KRA, RESTRICTED, INVALID_PAN mappings from CVL raw codes	Stage 2	SEED SQL EXISTS
20	`nsdl_error_master`	`03_seed_data.sql`	NSDL error codes, messages, IsFatal flag	Stage 2, 4	SEED SQL EXISTS
21	`application_config`	`03_seed_data.sql`	STP thresholds, feature flags, campaign routing, toggle keys	All stages	SEED SQL EXISTS
22	`provider_configurations`	`03_seed_data.sql`	External provider URLs, timeouts, retries, priority rankings	All stages	SEED SQL EXISTS

-- Run against OLD DB, export CSV, then INSERT into new DB INSERT INTO ekyc.restricted_email_domains (domain, reason, is_active, created_at) SELECT LOWER(TRIM(domain_name)), 'Migrated from TBL_RESTRICT_EMAIL_DOMAIN', TRUE, NOW() FROM old_db_export.restricted_email_domains_csv ON CONFLICT (domain) DO NOTHING;

D2. allowed_email_domains (from old TBL_ALLOW_EMAIL)

INSERT INTO ekyc.allowed_email_domains (domain, reason, is_active, created_at) SELECT LOWER(TRIM(email_domain)), 'Migrated from TBL_ALLOW_EMAIL', TRUE, NOW() FROM old_db_export.allow_email_csv ON CONFLICT DO NOTHING;

D3. duplicate_bypass_whitelist (from old TBL_PAN_MOBILE_EMAIL_WHITELIST)

INSERT INTO ekyc.duplicate_bypass_whitelist (pan_hash, mobile, email, reason, is_active, created_at) SELECT pan_hash_value, mobile_no, email_id, 'Migrated from TBL_PAN_MOBILE_EMAIL_WHITELIST', TRUE, NOW() FROM old_db_export.pan_mobile_email_whitelist_csv ON CONFLICT DO NOTHING;

D4. suspicious_contacts (from old Usp_GetSuspiciousPhoneOrEMailId source)

INSERT INTO ekyc.suspicious_contacts (identifier_hash, identifier_type, reason, is_active, created_at) SELECT hashed_value, CASE WHEN type = 'P' THEN 'MOBILE' ELSE 'EMAIL' END, reason_text, TRUE, NOW() FROM old_db_export.suspicious_contacts_csv ON CONFLICT DO NOTHING;

D5. location_exception_whitelist (from old TBL_LIVE_PHOTO_LOCATION_EXCEPTION)

INSERT INTO ekyc.location_exception_whitelist (pan_hash, reason, added_by, is_active, created_at) SELECT pan_hash_value, 'Migrated from TBL_LIVE_PHOTO_LOCATION_EXCEPTION', 'MIGRATION', TRUE, NOW() FROM old_db_export.location_exception_csv ON CONFLICT DO NOTHING;

D6. state_master (from old TBL_STATE_CITY_MASTER_LSQ)

INSERT INTO ekyc.state_master (state_code, state_name, is_south) SELECT state_code, state_name, CASE WHEN state_name IN ('ANDHRA PRADESH', 'KARNATAKA', 'KERALA', 'TAMIL NADU', 'TELANGANA') THEN TRUE ELSE FALSE END FROM old_db_export.state_city_master_csv GROUP BY state_code, state_name ON CONFLICT (state_code) DO NOTHING;

D7. city_master (from old MST_CITY_STATE)

INSERT INTO ekyc.city_master (city_name, state_code, is_metro) SELECT city_name, state_code, CASE WHEN city_name IN ('MUMBAI', 'DELHI', 'BANGALORE', 'CHENNAI', 'KOLKATA', 'HYDERABAD', 'PUNE', 'AHMEDABAD') THEN TRUE ELSE FALSE END FROM old_db_export.mst_city_state_csv ON CONFLICT DO NOTHING;

D8. pincode_master (from India Post / old pincode table)

-- Download from https://data.gov.in or export from old pincode table INSERT INTO ekyc.pincode_master (pincode, city, district, state, state_code) SELECT pincode::TEXT, office_name, district, state_name, state_code FROM old_db_export.pincode_csv ON CONFLICT (pincode) DO NOTHING;

D9. referral_codes (from MOSL_FEED + marketing)

INSERT INTO ekyc.referral_codes (code, referrer_name, channel, is_active, expires_at, created_at) SELECT cl_code, client_name, 'EXISTING_CLIENT', TRUE, NULL, NOW() FROM old_db_export.client_details_csv WHERE cl_code IS NOT NULL ON CONFLICT DO NOTHING;

D10. restricted_mobiles (from Ops/Compliance)

-- Get list from compliance team as CSV: mobile, reason INSERT INTO ekyc.restricted_mobiles (mobile, mobile_hash, reason, is_active, created_at) SELECT mobile_no, encode(sha256(mobile_no::bytea), 'hex'), reason_text, TRUE, NOW() FROM old_db_export.restricted_mobiles_csv ON CONFLICT DO NOTHING;

E. Execution Order (Recommended)

F. Verification Queries (Run After Each Phase)

Phase	When	Tables	Action
Phase 0 Before go-live	Day 1 (dev env)	`lookups`, `cvl_status_mapping`, `nsdl_error_master`, `application_config`, `provider_configurations`	Run `01_create_tables.sql` → `02_create_indexes.sql` → `03_seed_data.sql` → `11_seed_lookups.sql`
Phase 1 One-time migrations	After old DB access granted	`state_master`, `city_master`, `pincode_master`, `restricted_email_domains`, `allowed_email_domains`, `restricted_mobiles`, `suspicious_contacts`, `duplicate_bypass_whitelist`, `location_exception_whitelist`, `referral_codes`	Export CSVs from old DB → run migration SQLs from Section D above
Phase 2 Build sync pipelines	Before UAT	`client_master`, `branch_master`, `subbroker_master`, `employee_pan_master`, `franchise_pan_whitelist`	Build daily sync jobs (Hangfire / cron) from old MOSL_FEED tables
Phase 3 External syncs	Before UAT	`negative_list_entries`, `bank_ifsc_master`	Build sync from SEBI list (daily) + RBI IFSC directory (monthly)

-- Quick row-count check for all reference/sync tables SELECT 'client_master' AS tbl, COUNT(*) FROM ekyc.client_master UNION ALL SELECT 'branch_master', COUNT(*) FROM ekyc.branch_master UNION ALL SELECT 'subbroker_master', COUNT(*) FROM ekyc.subbroker_master UNION ALL SELECT 'employee_pan_master', COUNT(*) FROM ekyc.employee_pan_master UNION ALL SELECT 'franchise_pan_whitelist', COUNT(*) FROM ekyc.franchise_pan_whitelist UNION ALL SELECT 'negative_list_entries', COUNT(*) FROM ekyc.negative_list_entries UNION ALL SELECT 'bank_ifsc_master', COUNT(*) FROM ekyc.bank_ifsc_master UNION ALL SELECT 'restricted_mobiles', COUNT(*) FROM ekyc.restricted_mobiles UNION ALL SELECT 'restricted_email_domains', COUNT(*) FROM ekyc.restricted_email_domains UNION ALL SELECT 'suspicious_contacts', COUNT(*) FROM ekyc.suspicious_contacts UNION ALL SELECT 'duplicate_bypass_whitelist', COUNT(*) FROM ekyc.duplicate_bypass_whitelist UNION ALL SELECT 'allowed_email_domains', COUNT(*) FROM ekyc.allowed_email_domains UNION ALL SELECT 'location_exception_whitelist', COUNT(*) FROM ekyc.location_exception_whitelist UNION ALL SELECT 'referral_codes', COUNT(*) FROM ekyc.referral_codes UNION ALL SELECT 'state_master', COUNT(*) FROM ekyc.state_master UNION ALL SELECT 'city_master', COUNT(*) FROM ekyc.city_master UNION ALL SELECT 'pincode_master', COUNT(*) FROM ekyc.pincode_master UNION ALL SELECT 'lookups', COUNT(*) FROM ekyc.lookups UNION ALL SELECT 'cvl_status_mapping', COUNT(*) FROM ekyc.cvl_status_mapping UNION ALL SELECT 'nsdl_error_master', COUNT(*) FROM ekyc.nsdl_error_master UNION ALL SELECT 'application_config', COUNT(*) FROM ekyc.application_config UNION ALL SELECT 'provider_configurations', COUNT(*) FROM ekyc.provider_configurations ORDER BY tbl;

Key note on personal_field_master: The BRD called for a separate personal_field_master table, but the new codebase uses the lookups table (same schema: Category + Code + DisplayName + SortOrder + IsActive) as its superset replacement. No separate table is needed. Seed data already covers all 12 dropdown categories.

DB Actions Checklist — Old / Reference Data

Quick Count

A. Build Sync Jobs (Recurring — Old Back-Office & External)