Skip to main content

ERPNext Integration

How Konsolidat extracts data from ERPNext (Frappe) over the REST API, and how the ERPNext adapter plugs into the multi-ERP canonical staging interface so that silver and gold stay ERP-agnostic.

The connector lives in source-erpnext/ (an Airbyte CDK source) and lands raw doctypes into the epm_raw database. The dbt stg_erpnext__* adapter models then transform those raw tables into the same canonical schema every other ERP adapter produces.

API Access (Frappe Token)​

ERPNext / Frappe authenticates API calls with a static token header — there is no OAuth token-exchange step (see source_erpnext/auth.py):

Authorization: token <api_key>:<api_secret>

Step-by-Step​

  1. In ERPNext, open the user that the connector will run as: User → Settings → API Access.
  2. Click Generate Keys to obtain an API Key and API Secret. The secret is shown only once — copy it immediately.
  3. Ensure that user has read access to the doctypes the connector reads (GL Entry, Account, Company, Currency Exchange, Budget, Fiscal Year).

The connector validates credentials on check by calling the lightweight authenticated endpoint frappe.auth.get_logged_user; a failure returns a generic, non-sensitive message and never echoes the raw server body.

Required Values​

ValueWhere to FindUsed By
Host URLThe ERPNext site base URL, e.g. https://erp.mycompany.com (no trailing /api)Source config (host_url)
API KeyUser → Settings → API AccessSource config (api_key)
API SecretUser → Settings → API AccessSource config (api_secret)

Doctype Streams​

The connector ships 6 streams, one per ERPNext doctype, declared in source_erpnext/source.py and streams.py. Each stream reads /api/resource/<DocType> with limit_start / limit_page_length paging (page_size, default 500). Master streams full-refresh; transactional streams sync incrementally on the doctype's modified timestamp.

Stream (raw table)ERPNext DocTypeModeCursorKey Fields
gl_entryGL EntryIncrementalmodifiedname, company, posting_date, fiscal_year, account, account_currency, debit, credit, against, voucher_no, voucher_type, cost_center, project, is_cancelled, remarks
accountAccountFull refresh—name, account_name, root_type, account_type, company, disabled
companyCompanyFull refresh—name, default_currency, country
currency_exchangeCurrency ExchangeIncrementalmodifiedname, from_currency, to_currency, date, exchange_rate
budgetBudget (+ Budget Account child, flattened)Incrementalmodifiedname, company, fiscal_year, cost_center, project, account, budget_amount
fiscal_yearFiscal YearFull refresh—name, year_start_date, year_end_date
Budget flattening

The Frappe list endpoint omits child tables. The budget stream therefore fetches each Budget document's detail (/api/resource/Budget/<name>) and emits one row per Budget Account child line, denormalizing the parent fields (company, fiscal_year, cost_center, project) onto each account row. This matches the flattened budget raw table the dbt adapter expects. See the Budget class in streams.py.

Pagination & Incremental​

  • Paging: each request sends limit_page_length=<page_size> and limit_start=<offset>, ordered modified asc. A page shorter than page_size ends the stream (next_page_token in streams.py).
  • Incremental (FrappeIncrementalStream): on the cursor field modified, the request adds a Frappe JSON filter filters=[["modified", ">=", <state>]], and the high-water mark advances per record as rows are read.
  • Backoff: a Retry-After response header is honored when present; otherwise the CDK exponential backoff applies. 5xx responses retry (capped at 5); 429 is treated as rate-limited and retried indefinitely, paced by Retry-After.

Source Configuration​

Configured via source_erpnext/spec.yaml:

SettingRequiredDefaultNotes
host_urlyes—ERPNext base URL, e.g. https://erp.mycompany.com (no /api).
api_keyyes—API key (stored as an Airbyte secret).
api_secretyes—API secret (stored as an Airbyte secret).
page_sizeno500Records per Frappe REST page (limit_page_length). Min 50, max 5000.

Raw doctypes land in the epm_raw database, registered as the dbt source erpnext_raw (see _erpnext__sources.yml). The connector can also be run standalone for local testing:

pip install -e .
source-erpnext spec
source-erpnext check --config secrets/config.json
source-erpnext read --config secrets/config.json --catalog integration_tests/configured_catalog.json

Canonical Staging Architecture​

Konsolidat supports multiple ERPs behind a single canonical staging interface. The flow has two layers:

  1. Per-ERP adapter models — stg_<erp>__<entity> (e.g. stg_erpnext__gl_entries, stg_d365_fo__gl_entries). Each adapter reads one ERP's raw tables and emits the same canonical column set for that entity.
  2. Canonical models — stg_<entity> (e.g. stg_gl_entries) UNION ALL every enabled adapter, then harmonize dimensions centrally. Bronze, silver, and gold consume these canonical models and never reference an ERP-specific table.

Which adapters are unioned is driven by the erp_sources var in dbt_project.yml:

vars:
  erp_sources:
    - d365_fo
    - erpnext

The canonical models loop over erp_sources and ref() each adapter by convention stg_<erp>__<entity> (see stg_gl_entries.sql):

{% set erp_sources = var('erp_sources', ['d365_fo']) %}

with unioned as (
    {% for erp in erp_sources %}
    select ... from {{ ref('stg_' ~ erp ~ '__gl_entries') }}
    {% if not loop.last %}union all{% endif %}
    {% endfor %}
)

Adding an ERP is therefore additive: ship its stg_<erp>__* adapters with the canonical column contract and append the source name to erp_sources. No silver or gold model changes.

Dimension Harmonization​

The canonical stg_gl_entries model emits dimension values raw from each adapter, then harmonizes them centrally, keyed on the per-row erp_source, via the dim_harmonize_select / dim_harmonize_joins macros (macros/dimension_helpers.sql). Unmapped values pass through unchanged. Each adapter therefore emits its dimension columns raw — harmonization is not restated per ERP.

ERPNext Adapter Mapping​

Each stg_erpnext__* model maps an ERPNext doctype to the canonical schema for its entity. Every adapter stamps a literal 'erpnext' as erp_source. The adapter _loaded_at / _raw_id come from _airbyte_extracted_at / _airbyte_raw_id.

Canonical ModelERPNext AdapterSource Doctype
stg_gl_entriesstg_erpnext__gl_entriesGL Entry
stg_trial_balancestg_erpnext__trial_balancederived from GL Entry
stg_accountsstg_erpnext__accountsAccount
stg_legal_entitiesstg_erpnext__legal_entitiesCompany
stg_exchange_ratesstg_erpnext__exchange_ratesCurrency Exchange
stg_budget_entriesstg_erpnext__budget_entriesBudget
stg_fiscal_periodsstg_erpnext__fiscal_periodsFiscal Year

Key Mapping Decisions​

Notable transforms applied in the ERPNext adapters to satisfy the canonical contract:

  • GL Entry → stg_gl_entries (stg_erpnext__gl_entries.sql): ERPNext stores each posting as a separate debit or credit row, with analytical dimensions as flat columns (cost_center → dim_cost_center, project → dim_business_unit). amount is computed as debit - credit (signed), and is_credit is derived from credit > debit. The string name is converted to a numeric record_id via reinterpretAsInt64(cityHash64(name)) so it is UNION-compatible with the D365 numeric SourceKey. Cancelled rows (is_cancelled in 1/yes/true) are filtered out.
  • Account → stg_accounts (stg_erpnext__accounts.sql): ERPNext root_type (Asset/Liability/Income/Expense/Equity) maps to canonical account_type; the finer account_type (Cash, Bank, Receivable, …) maps to account_category. account_id is the Account name, which is exactly what GL Entry.account references.
  • Company → stg_legal_entities (stg_erpnext__legal_entities.sql): ERPNext Company has no short code, so entity_id is upper(name) — matching the entity_id emitted by the GL and budget adapters for join stability.
  • Currency Exchange → stg_exchange_rates (stg_erpnext__exchange_rates.sql): ERPNext rates are point-in-time (a single date, no validity range), so valid_to is set to the open-ended sentinel 2099-12-31; ERPNext has no rate-type concept, so rate_type is empty.
  • Budget → stg_budget_entries (stg_erpnext__budget_entries.sql): consumes the already-flattened budget raw rows. ERPNext budgets have no posting date, so one is derived from the fiscal-year start (<year>-01-01). record_id is a deterministic UInt64 surrogate from name + account.
  • Fiscal Year → stg_fiscal_periods (stg_erpnext__fiscal_periods.sql): ERPNext fiscal years are named ranges (e.g. 2024-2025) with explicit start/end dates; the name doubles as both calendar_id and the (string) canonical fiscal_year label.
  • Trial Balance → stg_trial_balance (stg_erpnext__trial_balance.sql): ERPNext has no TB snapshot doctype (unlike D365's TrialBalanceFiscalYearSnapshots), so the adapter derives a TB by aggregating GL Entry per entity / account / fiscal year. opening_balance is 0 (GL carries no opening snapshot); closing_balance is sum(debit - credit).
ERP-agnostic downstream

Because all of the above resolves inside the adapters, the canonical stg_* models — and everything in silver and gold — see a uniform schema regardless of whether a row originated in ERPNext or D365.

Troubleshooting​

IssueCauseFix
Authentication failed (HTTP 401/403) on checkWrong host_url, api_key, or api_secret, or the key's user lacks read access to the GL doctypesVerify the three values and grant the user read access to GL Entry, Account, Company, Currency Exchange, Budget, Fiscal Year
Empty / partial GL syncAll cancelled rows are filtered (is_cancelled)Expected — cancelled vouchers are excluded by the GL adapter
429 / slow syncERPNext rate limitingThe stream honors Retry-After and retries indefinitely; reduce page_size if pressure persists
Budget rows missing accountsBudget document has no Budget Account child linesBudgets emit one row per child line; a budget with no lines produces no rows
ERPNext rows absent from silver/golderpnext not listed in erp_sourcesAdd erpnext to the erp_sources var in dbt_project.yml and rebuild

Next Steps​