Skip to main content

QuickBooks

Overview​

This document provides a comprehensive explanation of our QuickBooks integration flow. The integration connects our application with QuickBooks Online, enabling automatic synchronization of customers and invoices between the two systems.

Architecture​

The QuickBooks integration follows a OAuth 2.0 flow for authentication and uses the QuickBooks API to interact with customer and invoice data. The integration is built using several key components:

  1. Authentication Service: Handles OAuth flow, token management, and API communication
  2. Data Resolvers: Process and transform data between our system and QuickBooks
  3. Background Jobs: Schedule and execute regular polling for data synchronization
  4. Data Models: Store QuickBooks data in our application

Integration Flow​

Authentication Flow​

  1. Consent URL Generation: The application generates a consent URL for the user to authorize our application to access their QuickBooks data.
  2. Authorization Code Exchange: After authorization, QuickBooks redirects back to our application with an authorization code.
  3. Token Exchange: The code is exchanged for access and refresh tokens.
  4. Token Storage: Tokens are securely stored in our database along with other integration details.
  5. Token Refresh: The system automatically refreshes tokens before they expire.

Data Synchronization​

Customer Synchronization​

  1. QuickbooksPollJob: Scheduled job that triggers the customer and invoice polling process.
  2. QuickbooksCustomersPollJob: Fetches all customers from QuickBooks for a specific company.
  3. QuickbooksCustomerResolverJob: Processes each customer:
    • QuickbooksCustomerResolver: Handles the main customer record.
    • QuickbooksCustomerByCompanyResolver: Handles company-specific customer records.
  4. Customer Hierarchy Management: Establishes relationships between head offices and distribution centers if 2 fetched customers have the same address.

Invoice Synchronization​

  1. QuickbooksPollJob: Identifies customers with active EDI partners.
  2. QuickbooksCustomerInvoicesPollJob: Fetches invoices for EDI partners customers.
  3. QuickbooksInvoiceHeaderResolverJob: Processes each invoice:
    • QuickbooksInvoiceHeaderResolver: Handles the invoice header record.
    • QuickbooksInvoiceDetailResolver: Handles each line item in the invoice.
  4. Execute Stored Procedure: execute pr_load_edi_210_invoice to process the quickbooks invoice into the edi tables.
  5. Status Updates: Updates the SPS Ready status in QuickBooks after processing by sending a POST request.

Technical Implementation​

Authentication Implementation​

When a user wants to connect their QuickBooks account, we generate a consent URL:

# QuickbooksApiService
def consent_url(state:)
    URI::HTTPS.build(
    host: URI(AUTH_URL).host,
    path: URI(AUTH_URL).path,
    query: URI.encode_www_form(
      response_type: 'code',
      client_id: CLIENT_ID,
      redirect_uri: REDIRECT_URI,
      scope: SCOPE,
      state: state
    )
  ).to_s
end

2. Exchange Code for Tokens​

After the user authorizes our application, QuickBooks redirects back with a code that we exchange for tokens:

# QuickbooksApiService
def integrate(code:, realm_id:)
  tokens = exchange_code_for_tokens(code)

  integration = Integration.find_or_initialize_by(
    company_id: company_id,
    integration_id: Integration.quickbooks.id
  )

  integration.assign_attributes(
    credentials: tokens.merge('realm_id' => realm_id),
    active: true
  )

  integration.save!
  integration
end

def exchange_code_for_tokens(code)
  response = Faraday.post(TOKEN_URL) do |req|
    req.headers['Content-Type'] = 'application/x-www-form-urlencoded'
    req.headers['Accept'] = 'application/json'
    req.body = URI.encode_www_form(
      grant_type: 'authorization_code',
      code: code,
      redirect_uri: REDIRECT_URI
    )
  end

  raise "Token exchange failed: #{response.body}" unless response.success?

  JSON.parse(response.body).merge('created_at' => Time.now.to_i)
end

3. Token Management​

Tokens are automatically refreshed before they expire:

# QuickbooksApiService
def valid_tokens
  tokens = company_integration.credentials
  expires_at = tokens['created_at'].to_i + tokens['expires_in'].to_i

  if Time.now.to_i + MINIMUM_TOKEN_REMAINING_TIME >= expires_at
    new_tokens = refresh_tokens(tokens['refresh_token'])
    updated = tokens.merge(new_tokens)
    company_integration.update!(credentials: updated)
    updated
  else
    tokens
  end
end

Data Synchronization Implementation​

Customer Synchronization​

The customer synchronization process is triggered by the QuickbooksPollJob, which runs on a schedule:

# QuickbooksPollJob
def poll_companies_customers
  Integration.quickbooks_companies.select(:company_id, :suffix_code).find_each do |integration|
    QuickbooksCustomersPollJob.perform_later(
      company_id: integration.company_id,
      suffix_code: integration.suffix_code
    )
  end
end

Note suffix_code was added to be able to store a unique customer code identifier that stores the quickbooks customer id. Example: Company Centrix - suffix_code: 'CNTX'.

Fetching Customers​

The QuickbooksCustomersPollJob fetches customers from QuickBooks:

# QuickbooksCustomersPollJob
def resolve_customers
  quickbooks_api_service.get_each_paginated_resource(
    path: 'Customer',
    query: 'SELECT * FROM Customer'
  ) do |quickbooks_customer|
    Rails.logger.info("Processing QB customer: #{quickbooks_customer['DisplayName']}")
    QuickbooksCustomerResolverJob.perform_later(
      company_id: company_id,
      suffix_code: suffix_code,
      quickbooks_customer: quickbooks_customer
    )
  end
end

Processing Customers​

Each customer is processed by the QuickbooksCustomerResolverJob:

# QuickbooksCustomerResolverJob
def perform(company_id:, suffix_code:, quickbooks_customer:)
  # 1. Resolve the main customer record
  customer_id = resolve_customer(
    company_id: company_id,
    suffix_code: suffix_code,
    quickbooks_customer: quickbooks_customer
  )

  # 2. Resolve the company-specific customer record
  resolve_customer_by_company(
    company_id: company_id,
    customer_id: customer_id,
    quickbooks_customer: quickbooks_customer
  )
end

The resolver follows these steps:

  1. Find existing customer or create a new one
  2. Update customer attributes
  3. Create integration reference
# QuickbooksCustomerResolver
def resolve
  ActiveRecord::Base.transaction do
    if find_integrated_customer
      update_customer
      check_and_create_dc_customer_if_needed
      { action: Actions::UPDATE, customer: @customer }
    elsif find_customer_to_integrate
      integrate_customer
      update_customer
      check_and_create_dc_customer_if_needed
      { action: Actions::INTEGRATE, customer: @customer }
    elsif create_customer
      integrate_customer
      check_and_create_dc_customer_if_needed
      { action: Actions::CREATE, customer: @customer }
    end
  end
rescue StandardError => e
  Rails.logger.error "QuickBooks Customer resolve failed: #{e.message}"
  raise e
end

Invoice Synchronization​

Invoice synchronization is similar to customer synchronization but with additional filtering:

# QuickbooksPollJob
def poll_customers_invoices
  Integration.quickbooks_companies.select(:company_id, :suffix_code).find_each do |integration|
    # Get all customers for this company
    CustomersByCompanyView.where(company_id: integration.company_id).find_each do |customer|
      # Check if customer has an active EDI partner
      edi_partner = EdiPartner.find_by(
        entity_type: 'C',
        head_office_id: customer.customer_id,
        active: true
      )

      if edi_partner.present?
        # Extract the QuickBooks ID from customer_code
        prefix = "#{integration.suffix_code}-"
        customer_code = customer.customer_code.gsub(/^#{Regexp.escape(prefix)}/, '')

        QuickbooksCustomerInvoicesPollJob.perform_later(
          company_id: customer.company_id,
          customer_id: customer.customer_id,
          customer_code: customer_code
        )
      end
    end
  end
end

Fetching and Processing Invoices​

Invoices are fetched for specific customers:

# QuickbooksCustomerInvoicesPollJob
def resolve_invoices
  quickbooks_api_service.get_invoices_for_customer(customer_id: customer_code) do |invoice|
    Rails.logger.info("Processing QB invoice #{invoice['Id']}:")

    QuickbooksInvoiceHeaderResolverJob.perform_later(
      company_id: company_id,
      customer_id: customer_id,
      invoice: invoice
    )
  end
end

# QuickbooksApiService
def get_invoices_for_customer(customer_id:, &block)
  get_each_paginated_resource(
    path: 'Invoice',
    query: "SELECT * FROM Invoice WHERE CustomerRef = '#{customer_id}' AND Balance = '0'"
  ) do |invoice|
    custom_fields = invoice['CustomField'] || []
    sps_ready = custom_fields.find { |field| field['Name'] == 'SPS Ready' }&.dig('StringValue')
    yield invoice if sps_ready == 'Y'
  end
end

Each invoice is then processed:

# QuickbooksInvoiceHeaderResolverJob
def perform(company_id:, customer_id:, invoice:)
  # 1. Resolve the invoice header
  result = QuickbooksInvoiceHeaderResolver.new(
    company_id: company_id,
    customer_id: customer_id,
    invoice: invoice
  ).resolve

  quickbooks_invoice_header_id = result[:invoice_header]

  # 2. Resolve each invoice line
  invoice['Line'].each_with_index do |line, index|
    QuickbooksInvoiceDetailResolver.new(
      company_id: company_id,
      customer_id: customer_id,
      quickbooks_invoice_header_id: quickbooks_invoice_header_id,
      invoice_detail: line
    ).resolve
  end

  # 3. Update SPS Ready status
  update_invoice_status_and_call_procedure(quickbooks_invoice_header_id)
end

Updating QuickBooks Invoice Status​

After processing an invoice, we update its status in QuickBooks:

def update_invoice(invoice_id:, attributes:)
  tokens = valid_tokens
  realm_id = tokens['realm_id']

  # First, we need to get the current invoice to merge with our updates
  response = Faraday.get("#{API_BASE_URL}/#{realm_id}/invoice/#{invoice_id}") do |req|
    req.headers['Authorization'] = "Bearer #{tokens['access_token']}"
    req.headers['Accept'] = 'application/json'
    req.headers['Content-Type'] = 'application/json'
  end

  raise "Get invoice failed: #{response.body}" unless response.success?

  current_invoice = JSON.parse(response.body)['Invoice']

  # Merge the current invoice with our updates
  # We need to include the SyncToken to avoid concurrency issues
  update_data = {
    'Id' => invoice_id,
    'SyncToken' => current_invoice['SyncToken'],
    'sparse' => true
  }.merge(attributes)

  # POST the updated invoice back to QuickBooks
  response = Faraday.post("#{API_BASE_URL}/#{realm_id}/invoice") do |req|
    req.headers['Authorization'] = "Bearer #{tokens['access_token']}"
    req.headers['Accept'] = 'application/json'
    req.headers['Content-Type'] = 'application/json'
    req.body = update_data.to_json
  end

  raise "Update invoice failed: #{response.body}" unless response.success?

  JSON.parse(response.body)['Invoice']
end
def update_invoice_status_and_call_procedure(quickbooks_invoice_header_id)
  # Find the invoice header to get both our internal status and the QuickBooks invoice ID
  invoice_header = QuickbooksInvoiceHeader.find(quickbooks_invoice_header_id)
  quickbooks_invoice_id = invoice_header.quickbooks_invoice_id

  ActiveRecord::Base.transaction do
    # Call the stored procedure with the invoice header ID
    ActiveRecord::Base.connection.execute_procedure(
      'pr_load_edi_210_invoice',
      p_qb_invoice_header_id: quickbooks_invoice_header_id
    )

    # Update the QuickBooks invoice via API
    update_quickbooks_invoice(
      company_id: invoice_header.company_id,
      quickbooks_invoice_id: quickbooks_invoice_id,
      po_number: invoice_header.po_number,
      shipment_id: invoice_header.shipment_identification_no
    )

    # Update our internal status to 'invoice sent'
    invoice_header.update!(status: 'invoice sent')
  end
rescue StandardError => e
  Rails.logger.error("Failed to update: #{e.message}")
  raise e
end

def update_quickbooks_invoice(company_id:, quickbooks_invoice_id:, po_number:, shipment_id:)
  # Create the custom fields array with updated SPS Ready status
  # Note: we need to add all the custom fields so that they are not overwritten
  # by QuickBooks when we update the invoice
  custom_fields = [
    { 'DefinitionId' => '1', 'Name' => 'P.O. Number', 'Type' => 'StringType', 'StringValue' => po_number },
    { 'DefinitionId' => '2', 'Name' => 'SPS Ready', 'Type' => 'StringType', 'StringValue' => 'Invoice sent' },
    { 'DefinitionId' => '3', 'Name' => 'Shipment ID', 'Type' => 'StringType', 'StringValue' => shipment_id }
  ]

  # Update the invoice via the QuickBooks API
  api_service = QuickbooksApiService.new(company_id: company_id)
  api_service.update_invoice(
    invoice_id: quickbooks_invoice_id,
    attributes: { 'CustomField' => custom_fields }
  )
rescue StandardError => e
  Rails.logger.error("Failed to update QuickBooks invoice: #{e.message}")
  raise e
end

API Communication​

The core of our integration is the QuickbooksApiService, which handles all communication with QuickBooks API:

# QuickbooksApiService
def get_each_paginated_resource(path:, query:, &block)
  tokens = valid_tokens
  realm_id = tokens['realm_id']
  # start_position and max_results are used for pagination they should be strings
  start_position = '1'
  max_results = '1000' # 1000 is the maximum allowed by QuickBooks

  loop do
    paginated_query = "#{query} STARTPOSITION #{start_position} MAXRESULTS #{max_results}"
    response = Faraday.get("#{API_BASE_URL}/#{realm_id}/query") do |req|
      req.headers['Authorization'] = "Bearer #{tokens['access_token']}"
      req.headers['Accept'] = 'application/json'
      req.headers['Content-Type'] = 'application/text'
      req.params['query'] = paginated_query
    end

    raise "Get #{path} failed: #{response.body}" unless response.success?

    parsed = JSON.parse(response.body)
    resources = parsed.dig('QueryResponse', path.capitalize)

    break if resources.blank?

    resources.each(&block)

    break unless resources.size == max_results

    start_position = start_position.to_i + max_results.to_i
  end
end

Error Handling and Logging​

The integration includes comprehensive error handling and logging:

# QuickbooksCustomersPollJob
def reference_success
  ReferenceEvent.create!(
    level: ReferenceEvent::Levels::INFO,
    company_id: company_id,
    slug: ReferenceEvent::Slugs::QUICKBOOKS_CUSTOMERS_POLL,
    reference: quickbooks_api_service.get_company_integration
  )
end

def reference_error(error)
  ReferenceEvent.create!(
    level: ReferenceEvent::Levels::ERROR,
    company_id: company_id,
    slug: ReferenceEvent::Slugs::QUICKBOOKS_FORBADE_CUSTOMERS_POLL,
    reference: quickbooks_api_service.get_company_integration,
    data: error.response
  )
end

Configuration Management​

The integration uses configuration values from Rails credentials:

# QuickbooksApiService
CLIENT_ID     = Rails.application.credentials.quickbooks_client_id
CLIENT_SECRET = Rails.application.credentials.quickbooks_client_secret
REDIRECT_URI  = Rails.application.credentials.quickbooks_redirect_uri
AUTH_URL      = Rails.application.credentials.quickbooks_auth_url
TOKEN_URL     = Rails.application.credentials.quickbooks_token_url
API_BASE_URL  = Rails.application.credentials.quickbooks_api_url
SCOPE         = Rails.application.credentials.quickbooks_scope

Data Models​

The integration utilizes several data models to store and manage QuickBooks data:

  1. QuickbooksInvoiceHeader: Stores the main invoice information.
  2. QuickbooksInvoiceDetail: Stores individual line items for each invoice.
  3. Customer: Stores the main customer information.
  4. CustomerByCompany: Assign the customer to a company.
  5. IntegrationReference: Maps our system's records to QuickBooks records.
  6. ReferenceEvent: Logs important events and errors during the integration process.

Developer Guide​

API Reference​

Customer Endpoints​

  • GET /v3/company/{realmId}/query?query=<query> - Get all customers

Invoice Endpoints​

  • GET /v3/company/{realmId}/query?query=<query> - Get all invoices
  • GET /v3/company/{realmId}/invoice/{id} - Get a specific invoice
  • POST /v3/company/{realmId}/invoice - Create/Update an invoice

Extending the Integration​

Adding New Data Types​

To add synchronization for a new data type (e.g., Bills, Payments):

  1. Create new resolver classes for the data type
  2. Create new models to store the data
  3. Add new polling jobs to fetch the data
  4. Update the main polling job to trigger the new synchronization

References:​