laxmanhalaki/Centralized_Reporting_Backend
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
laxman_dev
Centralized_Reporting_Backend/BULK_READ_REFACTORING_SUMMARY.md

8.8 KiB
Raw Permalink Blame History

Bulk Read Functionality Refactoring Summary

Overview

The bulk read functionality has been completely refactored to support multiple service providers and services instead of being limited to Zoho only. This refactoring enables the system to handle data from various providers like Zoho, Salesforce, HubSpot, Keka, BambooHR, Intuit/QuickBooks, etc.

Key Changes

1. Database Schema Changes

New Provider-Agnostic Tables

Created new module-specific tables that support multiple providers:

  • contacts_bulk - Contact data from any provider/service
  • leads_bulk - Lead data from any provider/service
  • accounts_bulk - Account data from any provider/service
  • deals_bulk - Deal/Opportunity data from any provider/service
  • tasks_bulk - Task data from any provider/service
  • vendors_bulk - Vendor data from any provider/service
  • invoices_bulk - Invoice data from any provider/service
  • sales_orders_bulk - Sales order data from any provider/service
  • purchase_orders_bulk - Purchase order data from any provider/service
  • employees_bulk - Employee/HR data from any provider/service
  • products_bulk - Product/inventory data from any provider/service
  • customers_bulk - Customer data from any provider/service

New Job Tracking Table

  • bulk_read_jobs - Provider-agnostic job tracking table replacing zoho_bulk_read_jobs

Key Schema Features

  • Provider Field: Identifies the service provider (zoho, salesforce, hubspot, etc.)
  • Service Field: Identifies the specific service within a provider (crm, books, hr, etc.)
  • Optional Fields: Most data fields are now optional/nullable to accommodate varying data structures
  • Raw Data Field: JSON field to store original response data
  • Enhanced Indexing: Composite indexes on (user_uuid, provider, service) for efficient querying

2. API Endpoint Changes

Updated Routes in bulkReadRoutes.js

Before:

POST /api/v1/bulk-read/initiate
Body: { module, fields, page, limit }

GET /api/v1/bulk-read/data/:module

After:

POST /api/v1/bulk-read/initiate
Body: { provider, service, module, fields, page, limit }

GET /api/v1/bulk-read/data/:provider/:service/:module
GET /api/v1/bulk-read/providers
GET /api/v1/bulk-read/modules/:provider/:service

New Query Parameters

  • Added provider, service, module filters for job listing
  • Enhanced filtering capabilities for multi-provider support

3. Service Layer Refactoring

BulkReadService Updates

Provider Configuration System:

  • Configurable provider endpoints and authentication methods
  • Support for multiple services per provider
  • Provider-specific request formatting

Supported Providers & Services:

{
  zoho: { services: ['crm', 'books', 'inventory'] },
  salesforce: { services: ['crm'] },
  hubspot: { services: ['crm'] },
  keka: { services: ['hr'] },
  bamboohr: { services: ['hr'] },
  intuit: { services: ['accounting'] },
  quickbooks: { services: ['accounting'] }
}

Key Methods:

  • initiateBulkRead(userId, provider, service, module, fields, options)
  • getBulkReadData(userId, provider, service, module, options)
  • getAvailableProviders() - Returns all supported providers/services
  • getAvailableModules(provider, service) - Returns modules for specific provider/service

Provider-Specific Adapters

  • Request Formatting: Each provider has custom request structure
  • Authentication Headers: Provider-specific auth token handling
  • Response Parsing: Custom job ID extraction per provider

4. Repository Layer Changes

New bulkReadRepository.js

Replaces provider-specific repositories with a unified repository that:

  • Handles multiple table operations
  • Supports provider/service filtering
  • Provides bulk data insertion capabilities
  • Includes error handling for non-existent tables

Key Methods

  • createBulkReadJob() - Creates jobs in new schema
  • getUserData(userId, provider, service, module) - Gets filtered data
  • insertBulkData(module, data) - Inserts processed webhook data

5. Data Models

New Sequelize Models

Created contactsBulk.js and bulkReadJobs.js models following the new schema structure.

Features

  • Provider/service aware models
  • JSON field support for raw data
  • Comprehensive indexing strategy
  • Foreign key relationships maintained

Migration Path

Database Migrations (Files 014-026)

  1. 014-022: Core module tables (contacts, leads, accounts, deals, tasks, vendors, invoices, sales_orders, purchase_orders)
  2. 023: New bulk_read_jobs table
  3. 024-026: Additional provider tables (employees, products, customers)

Backward Compatibility

The refactoring maintains API compatibility for existing Zoho integrations while extending support for new providers.

Provider-Specific Implementation Details

Zoho Integration

  • CRM Service: contacts, leads, accounts, deals, tasks, vendors
  • Books Service: invoices, sales_orders, purchase_orders, customers, vendors
  • Inventory Service: products, customers, vendors, sales_orders, purchase_orders

Salesforce Integration

  • CRM Service: contacts, leads, accounts, deals, tasks
  • Uses SOQL query format for bulk operations
  • Bearer token authentication

HubSpot Integration

  • CRM Service: contacts, leads, accounts, deals
  • REST API with properties-based queries
  • Bearer token authentication

HR Providers (Keka, BambooHR)

  • HR Service: employees
  • Employee data with comprehensive fields (hire_date, department, salary, etc.)

Accounting Providers (Intuit, QuickBooks)

  • Accounting Service: customers, vendors, invoices, products
  • Financial data with proper decimal handling

Benefits of Refactoring

  1. Scalability: Easy to add new providers and services
  2. Maintainability: Single codebase handles all providers
  3. Flexibility: Optional fields accommodate varying data structures
  4. Performance: Optimized indexing for multi-provider queries
  5. Extensibility: JSON raw_data field preserves original data
  6. Consistency: Unified API across all providers

Usage Examples

Initiating a Bulk Read Job

// Zoho CRM Contacts
POST /api/v1/bulk-read/initiate
{
  "provider": "zoho",
  "service": "crm", 
  "module": "contacts",
  "fields": ["first_name", "last_name", "email", "phone"]
}

// Salesforce CRM Leads  
POST /api/v1/bulk-read/initiate
{
  "provider": "salesforce",
  "service": "crm",
  "module": "leads", 
  "fields": ["FirstName", "LastName", "Company", "Email"]
}

// Keka HR Employees
POST /api/v1/bulk-read/initiate  
{
  "provider": "keka",
  "service": "hr",
  "module": "employees",
  "fields": ["employee_id", "first_name", "last_name", "department"]
}

Retrieving Data

// Get Zoho CRM contacts
GET /api/v1/bulk-read/data/zoho/crm/contacts

// Get Salesforce leads  
GET /api/v1/bulk-read/data/salesforce/crm/leads

// Get employee data from Keka
GET /api/v1/bulk-read/data/keka/hr/employees

Discovery Endpoints

// Get all available providers
GET /api/v1/bulk-read/providers

// Get modules for Zoho CRM
GET /api/v1/bulk-read/modules/zoho/crm

// Get modules for Salesforce CRM  
GET /api/v1/bulk-read/modules/salesforce/crm

Next Steps

  1. Run Database Migrations: Execute migrations 014-026 to create new schema
  2. Update Webhook Handlers: Modify webhook processors to use new repository methods
  3. Test Provider Integrations: Validate each provider's bulk read functionality
  4. Update Documentation: Update API documentation with new endpoints
  5. Monitor Performance: Ensure new indexing strategy performs well with real data

Files Modified/Created

New Migrations

  • src/db/migrations/014_create_contacts_bulk.sql
  • src/db/migrations/015_create_leads_bulk.sql
  • src/db/migrations/016_create_accounts_bulk.sql
  • src/db/migrations/017_create_deals_bulk.sql
  • src/db/migrations/018_create_tasks_bulk.sql
  • src/db/migrations/019_create_vendors_bulk.sql
  • src/db/migrations/020_create_invoices_bulk.sql
  • src/db/migrations/021_create_sales_orders_bulk.sql
  • src/db/migrations/022_create_purchase_orders_bulk.sql
  • src/db/migrations/023_create_bulk_read_jobs.sql
  • src/db/migrations/024_create_employees_bulk.sql
  • src/db/migrations/025_create_products_bulk.sql
  • src/db/migrations/026_create_customers_bulk.sql

Updated Files

  • src/api/routes/bulkReadRoutes.js - Updated for multi-provider support
  • src/services/bulkReadService.js - Complete refactoring for provider-agnostic operations

New Files

  • src/data/repositories/bulkReadRepository.js - Unified repository for all providers
  • src/data/models/contactsBulk.js - Example model for new schema
  • src/data/models/bulkReadJobs.js - New job tracking model

This refactoring provides a solid foundation for supporting multiple service providers while maintaining clean, maintainable code architecture.