Integration - Exact Online

All code related to communication with the Exact Online API is located in the app/exact directory. Exact Online API endpoints are typically of the form https://start.exactonline.nl/api/v1/{division}/{entity-endpoint} where {division} is an integer defining the administration we want to extract data from.

Setup

To get started with the Exact Online API you need an app registration. There are two ways to get one:

Test account: Register a developer account here and then follow this article to register an app.
Live account: Follow this article to the letter to extract data from a live administration.

Once an app is registrered store the Client ID and Client Secret in the .env file.

Auth flow

To get a token for Exact Online go to https://baa.metriek.tech/api/v1/exact/auth.

To run the auth flow on metriek.local SSL is required.

Models

Config

The Exact Online entities that are fetched are defined in app/exact/config.json.

Config example

{
    "Divisions": {
        "endpoint": "system/Divisions",
        "ensure_division_column": false,
        "skip": true,
        "check_count": false
    },
    "GLAccounts": {
        "endpoint": "/financial/GLAccounts",
        "webhookTopics": ["GLAccounts"]
    }
}

The keys in this file are the entity names and are used for the automataic generation of model and table names, the values specify the configuration for a specific entity. The following keys are possible in the entity configuration:

endpoint (required): the endpoint to call to get the data;
ensure_division_column: if this value is true the package ensures the data includes a 'Division' column, this can be used to link data to a specific division;
skip: if this is set to true, the entity is not included in the 'get_all' loop;
check_count: if this is set to true, the count check after data is fetched is skipped;
one_division_only: if this is true an entity is fetched for one division only, use this for Exact Online constants like GLTransactionTypes;
webhookTopics: an array to specify the webhook topics we need to subscribe to to receive updates about an entity, using webhooks is mostly used to get updates about deletions;
dependsOn: an array of other entities that this entity depends on, this is used to create a fetch tree that makes sure entities that are needed by other entities get fetched first.

Schema detection

Run baa exact detect_schema to automatically detect the schema of entities defined in the JSON config. The logic for this functionality is located in app/integrations/exact/schema_maker.py and does roughly the following:

Call the $metadata endpoint of an entity. This is equal to the in the JSON defined endpoint with the last part replaced with $metadata (financial/GLAccounts -> financial/$metadata);
The XML response of the request is parsed into a list of tuples of the form [Field: str, PrimaryKey: bool, Type: str, Optional: bool];
The list of tuples is then transformed to a SQLAlchemy model and saved in a file in the app/integrations/exact/models directory. In addition to this fields extracted from the $metadata endpoint we add the following fields to the model:
- SyncedAt: the date/time that the entry was added to our database or the last time it was updated;
- DeletedAt: the date/time that the entry was deleted.

After the schema detection runs, add any new models to app/integrations/exact/models/__init__.py and create and run database migrations.

Authentication

See this article for more info about authenticating with the Exact Online API.

Authentication logic is written in app/exact/auth.py and makes use of two database models, Token and TokenDivisions (db tables exact_token and exact_token_divisions respectively). The token model stores the actual access and refresh tokens and their expiry, Exact Online tokens expire after 10 minutes and the corresponding refresh tokens can be used for 30 days. Whenever a token is added or refreshed through a scheduled job a call to the Exact Online API endpoint system/Divisions is made to retrieve a list of divisions that can be accessed using the token. This list is stored in the TokenDivisions model.

Whenever a token is used to make a call to the Exact Online API (in other words added to the Authorization header) the expiry is checked. In case the token is expired it is automatically refreshed. Refreshing tokens is done within a threadlock in order to prevent failures due to race conditions.

Fetching data

All code related to fetching and storing Exact Online entities defined in the JSON config can be found in app/exact/fetcher.py.

All entities

When all entities are fetched using the get_all function of the ExactFetcher the following steps are executed:

Get a list of all divisions that we currently have access to (all divisions with at least one token linked in the exact_token_divisions table);
All entities defined in app/exact/config.json (except the ones the have skip: true) are passed to the get function one by one, this function returns whether or not there are any changes detected in the entity;
A list of entities with changes is passed to a PowerBIAPI instance to instruct Power BI to refresh certain tables of the PBI dataset. This functionality is optional. See this page for more info about this functionality.

One entity

Simplified pseudocode of the fetch process

now = datetime.now()

# Get the last fetch for an entity/division combination
last_fetch = get_last_fetch_for_entity_and_division(entity, division)

# Apply the filter in case there is a last fetch
filters = {}
if last_fetch:
    filters.add(Modified > last_fetch)

# Get the data based on the filters
data = fetch(entity, division, filters)

# Update the 'last fetch' with the current time
last_fetch.update(now)

Incremental fetching

It is prefered to fetch data from the Exact Online API incrementally, this increases performance and reduces the chance of failure. Most entities have a Modified field that holds the date/time at which the entry was last changed.

Rate limiting

Information about rate limiting of the Exact Online API can be found here.