Exporting data for analytics and BI

Bezala has two distinct surfaces for data extraction, and they're optimised for different things. Picking the right one matters.

Operational endpoints — the ones you've seen elsewhere in these docs: GET /api/transactions, GET /api/daily_allowances, GET /api/trips, GET /api/rewards. They're designed for live UIs and operational integrations. The records they return are detailed enough for accounting flows but lean for analytics — you'd have to make extra calls per record to expand cost centers, attachments, vendors, and so on.

Export endpoints — GET /api/export/receipts, GET /api/export/trips, GET /api/export/daily_allowances, GET /api/export/rewards. These are purpose-built for analytics. Each response includes:

For analytics work, start with the export endpoints. Fall back to the operational ones only if you need a field they don't carry.

Notice the joins are pre-done: cost center names and IDs and external IDs in a single field, account codes alongside account IDs, the batch reference number that ties the row back to a batch document. This is the row you want sitting in your warehouse.

The first four (receipts, trips, daily allowances, rewards) all return the same shape and accept the same filters.

per_page accepts up to several hundred records — paginate as on any list endpoint.

A few details about the export rows are worth understanding before you build a warehouse on top of them. These are the questions that come up most often.

When a receipt has multiple VAT lines — a hotel bill split between "Accommodation" and "Meals" — the export returns one row per VAT line, not one per receipt. Every row from the same receipt shares the same id, so you group when needed:

This is the right shape for analytics — you slice spend by expense account, not by receipt — but a naive "count of receipts" needs COUNT(DISTINCT id), not COUNT(*).

credit_acc_code is always the accounting code of the asset account the receipt was paid from — company card, personal-card-reimbursable account, bank account. It does not change based on whether the row is a purchase or a refund.

debit_acc_code is the expense account for that VAT line — Meals, Travel, Software. On a refund or credit note, the convention stays the same: codes unchanged, amount is negative. The bookkeeping system reads the sign and produces the appropriate debit/credit entry.

Reviewer please verify the negative-amount convention. If Bezala's actual implementation swaps the codes on credit notes, or uses positive amounts with a type=refund flag, this section needs correction.

Receipts carry credit_acc_code and debit_acc_code because the accounting treatment is per-line and the user picks. Trips and daily allowances don't — the asset and expense accounts for these are configured at the company level.

Reviewer please verify that the trips and daily-allowance export endpoints genuinely don't carry these fields. If they do (perhaps under different field names, or only for some company configurations), update this section.

If you need the accounting code on trips and daily allowances, the workaround is a join: read the company's trip / daily-allowance settings via GET /api/company once, and apply the same code to every row. The codes don't vary by record.

Refunds, credit notes, and corrections show up as receipts with negative total_cost. Same lifecycle, same accounting batch. There's no separate "credit note" object type.

For analytics:

If your dashboard hides refunds, do it explicitly. Don't assume the export only includes positive amounts.

Useful for the first warehouse build and monthly reconciliation runs.

Page until collection.length < per_page. The total_records field tells you how many to expect.

Filter on updated_at_range. For analytics specifically, combine with state=accounted — only pull rows once they're locked, so your warehouse never has to handle "this row used to be approved but now it's been disapproved."

Power BI. Web data source, paging function per resource, hourly refresh. Tableau. Web Data Connector, or land in SQL warehouse first. Looker. Land in warehouse first; LookML is built for SQL. Custom Python/Node ETL. Most common path. Loop with per_page=500, write to warehouse with external_id as natural key.

Don't go directly from the BI tool to Bezala for production dashboards. Land in a warehouse first.

Returns every receipt, daily allowance, trip, and reward in that batch, grouped by type. See SDI-274 / article 09 for the full pattern.

Attachment URLs in export rows are short-lived signed S3 links — they work for a few minutes. Don't store the URL itself in your warehouse and try to use it tomorrow; it will have expired.

Instead, when you ingest a row, fetch the attachment, store it in your own object storage, keep a reference to your copy.

If you need a long-lived link, use GET /api/attachments/:id/image — returns the image content directly against your authenticated session.

Refresh: expense tables hourly, master-data tables nightly. This shape lets you build the standard finance dashboards — spend by department, spend by employee, top vendors, monthly close metrics — with simple SQL joins.