# Under the hood

> The architecture end to end: what executes a query, why Rust, DuckDB and Arrow, how caching and backpressure behave, and where access control is applied

Source: https://aipril.dev/docs/engine/under-the-hood

Analytics on this platform is not one program. It is a chain: sources are registered, data is either
queried where it stands or kept in the platform's own lake, a query engine compiles a semantic model
into SQL and executes it next to the data, and every surface reads the result through one contract.

```mermaid
flowchart BT
    DB["Databases<br/>PostgreSQL, ClickHouse, Oracle,<br/>MS SQL, MySQL, and others"]
    API["REST services<br/>any endpoint; Swagger<br/>or Postman import"]
    FILE["Files<br/>uploads, S3, SFTP, SMB,<br/>a folder, a shared sheet"]
    BPM["Processes<br/>tasks, statuses, custom fields"]
    LEDGER["Ledger<br/>account movements, balances"]

    LAKE["Data lake<br/>Parquet in object storage"]
    MODEL["Semantic model<br/>metrics, dimensions, joins"]
    ACL["Roles and access<br/>grants, row policies, column masks"]
    ENG["Query engine<br/>compiles the model, executes the plan"]

    DOOR["/api/query and /models<br/>the only entry point"]

    DASH["Dashboards<br/>and the exploration table"]
    APPS["Apps and portal pages"]
    AGENTS["AI agents and MCP"]
    WB["Workbooks<br/>a dataset linked into a sheet"]

    DB -->|"live"| ENG
    DB -->|"extract"| LAKE
    API -->|"live"| ENG
    API -->|"snapshot"| LAKE
    FILE --> LAKE
    BPM -->|"live relation"| ENG
    LEDGER -->|"dataset bridge"| LAKE

    LAKE --> ENG
    MODEL --> ENG
    ACL -->|"injected into the plan"| ENG

    ENG --> DOOR
    DOOR --> DASH
    DOOR --> APPS
    DOOR --> AGENTS
    DOOR --> WB
```

The narrow point in the middle of the picture is the one worth noticing. Every consumer reaches the
data through the same entry point, which is the mechanical reason a figure cannot differ between a
dashboard, a portal page, an export and an answer from the AI. A test enforces it, not convention: a
caller that builds its own path to the engine fails the build. The access gates live on that path,
so a second door would be a second, ungated one.

## What executes a query

The browser does not aggregate. A table, a chart and an Excel export compile to the same query,
executed next to the data by a Go service; what comes back is a result set sized by the question,
not by the table.

Two execution modes run against the same model:

- **Live** - the query runs against the source, with the engine rendering that database's dialect.
- **Extract** - the data is laid out as Parquet in object storage and queried by our own execution
  service.

The two paths are checked against each other in CI: a harness seeds both, compiles the same queries
against each, and requires the answers to match to within floating-point tolerance. That is what
makes moving a model from live to extract a capacity decision, not a correctness risk.

## Why Rust, DuckDB and Arrow

On the extract path the executor is our own service written in **Rust**, built on
[**DuckDB**](https://duckdb.org) - the columnar, vectorised database analytical workloads are run
on. The three choices answer three different questions:

- **Columnar and vectorised, because of the shape of the work.** Analytical queries touch few
  columns and many rows, and process them in batches instead of row at a time. A row-store engine
  spends its time reading fields nobody asked for.
- **Embedded, because there is no cluster to operate.** DuckDB runs inside the executor, not as a
  database to deploy, back up and tune beside everything else.
- **Rust, because this is the layer where a stall is not recoverable.** Predictable memory
  behaviour and no garbage-collection pause in the middle of a scan, in the one component where a
  pause is visible to everybody at once.
- **[Apache Arrow](https://arrow.apache.org) for the wire, because the alternative is undoing the
  work.** Results stream back in columnar batches instead of being serialised row by row, which is
  what stops a wide result from costing more to transport than it cost to compute.

## Caching and backpressure

Extract results are cached under a key carrying the tenant, the request, and the dataset's freshness
timestamp - so a refresh changes the key and new data invalidates the cache by construction instead
of by waiting out a timer. Authority decisions are cached the same way: a short in-process layer
with a shared cache behind it, because the alternative is re-deciding access on every query.

Concurrent queries are capped by a semaphore; past the cap a request waits briefly and is shed with
a 429. At saturation a system that answers "not now" is more useful than one that queues everything
into unbounded latency and fails at the timeout anyway. The query layer runs as replicas and scales
horizontally - registry invalidation is cross-replica safe, so adding a replica does not add a
stale-cache problem.

## Access control lives in the query plan

Row policies and column masks are applied while the plan is compiled, not filtered out of the answer
afterwards:

- row filters are injected below the aggregate node and AND-combined, so a restricted reader's
  totals are computed over the rows they may see, not sliced out of someone else's total
- masked columns are output expressions of the plan; regrouping by a masked value is refused, not
  answered approximately
- the policy decision is fail-closed - if it cannot be resolved, the query stops
- decisions are written to an audit trail, including the deliberate bypasses

Access itself is granted on resources - a model, a metric - through groups, on one ladder shared by
every object type instead of a separate scheme per surface. Organisations are separated by schema,
not by a tenant column, and a cached datum whose visibility depends on who is asking may only be
held by the layer that computes that decision.

## Where it joins the rest of the platform

- **Processes.** A published process model is synced to the engine as a live relation, so tasks,
  statuses and their custom fields become an ordinary queryable model - time fields included, so
  grain-by-month works on them like any date. The sync runs on every app publish that touches a
  process, not as a manual step. Access to such a model is granted at model level.
- **The ledger.** Account movements reach analytics through a dataset bridge, as single-fact models.
- **Workbooks.** A sheet's rows are kept as a relational projection alongside the collaborative
  document, which is what lets server-side readers treat sheet contents as data. In the other
  direction, a dataset or an engine model can be linked into a sheet.
- **Portals and apps.** A page gets its data only through the engine's contract; there is no direct
  path from a page to storage.
- **External agents.** MCP tools resolve the same models through the
  same entry point - a different surface, not a different pipeline.

What is built on top of all this is [Analytics](https://aipril.dev/docs/analytics.md), and the front end's own
engineering is on its [Under the hood](https://aipril.dev/docs/analytics/under-the-hood.md).