Looking Inside Xero - Part 1

Over the years I have written numerous articles about 'the guts' of QuickBooks, first desktop, and then QuickBooks Online.  While I have done some in-depth feature review of other products, this is the first time I have taken any sort of 'deep dive' into the inner workings of a different general ledger.  If you are a 'geek' like me, you will want to read along as we begin our journey to the 'inside of Xero'.

Background

Xero is cloud-based accounting software designed for small and medium-sized business. Xero Limited, the parent of Xero software, was formed in 2006 in New Zealand. Xero was founded by Rod Drury, and his accountant, when they came to the recognition that the then current desktop accounting software was rapidly growing obsolete in the face of an ever expanding interest in cloud-based software. Xero expanded beyond the New Zealand market into Australia and the United Kingdom during 2008, and entered the North American market in 2011.

The Xero Platform and API

In 2013 Xero began migrating their infrastructure from Rackspace in preparation for a platform based upon Amazon Web Services (AWS). The move signaled Xero’s intent to offer their accounting platform to essentially an unlimited number of users, with almost unlimited file sizes.

During 2016 Xero migrated all of their customers to the AWS platform which provides improved security, availability and performance. It also streamlines the process of deployment of new and enhanced feature sets to all users. That migration involved more than 59-billion data records, and encompassed more than $1-trillion worth of transactions.

Even with the enhanced capabilities of the AWS platform and long-term aspects of organization file size, Xero defines certain limits including:

  • Up to 1,000 Sales invoices (Accounts Receivables) per month1
  • Up to 1,000 Purchases bills (Accounts Payables) per month1
  • Up to 2,000 bank transactions per month2
  • A maximum number of 4,000 tracked inventory items per organization
  • Contact list entries not to exceed 10,000
  • Up to 500 fixed assets.

Xero developed a series of Application Programming Interfaces (APIs) for the Xero accounting platform and related components, and made those APIs available to 3rd-party developers so as to provide a means whereby those developers could interact with any Xero Organization’s data. Generally, these 3rd party Apps use the API as the basis upon which to query the Xero data using XML commands which are then returned in the form of record sets that qualify as the query results.

With that said App developers should be aware that Xero has apparently created some limits with regard to API call limitations. Based upon our research, the following limitations may apply. A daily limit of 5000 API calls against any single Xero organization with a rolling 24-hour period. Typically, an update of an existing record within a Xero organization involves 2 API requests, the first to get the current record, and a second to submit a change to the record. If you exceed the API rate limit, the Xero API will return an HTTP 503 error: Service Unavailable.

At the time of this writing, another Xero API usage limitation may apply. In addition to the daily API call limitation, there is a limitation in which a single access token can only be used 60 times in a rolling 60-second period. In cases in which the token limitation is not observed, the Xero API will return a message: OAuth_problem=token limit exceeded.

The Xero API also limits the size of requests. A single POST to the Accounting or Payroll APIs has a size limit of 3.5MB. There is no limit to the number of elements within a request, provided the overall request size does not exceed 3.5MB. A single POST to the Files API has a size limit of 10MB.

Xero recommends developers only create or update 100 items per API request.

Developer applications that might tend to exceed the Xero API rate limits, can in fact work within the limits by analyzing how they intend to use the Xero API and then taking an approach in which more than one thing is undertaken within a single request: For example, it’s entirely possible to create more than one Invoice in a single PUT or POST Invoices API call. While there is no upper limit in the number of nodes that can be sent at one time, a ceiling of 50 nodes per request is recommended since such a limit should ensure that any request does not exceed the maximum 3.5MB request size.

The extraction of data from a Xero Organization is a function that is likely to exceed the Xero usage limits:

  • Retrieving all invoices does not return line item details for individual invoices.
  • Use pagination to retrieve line item details for 100 invoices at a time.
  • Endpoints that currently support pagination are invoices, contacts, bank transactions and manual journals.
  • When working with other endpoints, a request for each individual object may be required to get full details.
  • An organization can have thousands of journals, which can be retrieved only in batches of 100.

Large extraction operations situations will require a substantial amount of time to obtain the required data. Xero developer guidelines specify that data-utilities or other applications structured for such extractions “should be configured with schedule or queue functions so that users have no expectation of an immediate response.”

The primary APIs for Xero are the Accounting API and the Payroll API. We will explore characteristics of each of these APIs within this mini-series.

The Accounting API

The Xero Accounting API exposes the relational Tables, Views, and Stored Procedures associated with Xero Organization data. The tables are normalized and contain an Id column, which is the primary key. This behavior is consistent across all tables within the Xero Accounting API. Views are tables that can not be directly modified via the API; Xero Organization accounting data that is read-only are reflected as Views. Stored procedures provide functional interfaces to the data sources, they can be used to search, update, and modify information within the data source.

Xero Accounting API Tables

The Xero API exposes tables for both retrieving and updating data.  The graphic below illustrates the various Xero Accounting API Tables and their access functionality.

When we look at the relatively small number of table types found in Xero it should bring to mind the old acronym, "KISS" (Keep it simple stupid), especially if we contrast this configuration with a couple of other general ledger systems for which we have explored their internal designs.  

A simplified table structure is not necessarily limiting in terms of functionality, it does however mean that there are fewer requirements to link associated sets of data between tables.

"Accounts" Table

As an example of the table structure of a list-type table within Xero, let's look at the 'accounts' table. The Xero Accounting API permits creation, deletion, and query of accounts for a Xero organization. Specifically you can SELECT and INSERT accounts. As part of any SELECT, with limited exceptions, the Xero Accounting API allows extensive filtering and ordering so that users can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.  The Xero API does not support filtering on line items or journal lines.

The Xero Accounting API permits the inserting of a single row even with line item tables. By setting the required fields a new table object will be created as a single entry. The following fields are required for inserts: Code, Name, and Type. An example of an insert is shown below:

The fields exposed by the Xero Accounting API for the Accounts table are shown in the graphic below:

We will continue our look at the internal structure of Xero in part 2 of this mini-series.