Provisa Architecture¶

Overview¶

Provisa is a config-driven data virtualization platform: federation, metrics, ontology and governance from a single model, for small teams and large enterprises alike. It provides a unified API over heterogeneous data sources with governance, security, and performance optimization. Clients query via SQL, GraphQL, or Cypher; all three get identical governance.

The model is the only place meaning is added. To add to it, you register new data sources or aggregates in the data virtualization layer. That keeps a clean separation, since nothing can add semantics outside the platform, and it's what makes real data governance possible. The compiler enforces it: the approved relationship catalog is the source of truth, whichever query language is used.

One platform serves operational and analytical reads. A query that touches one source goes straight to that source over its native driver, typically back in under 100 ms, and a query that joins sources runs in the federation engine you chose for analytical scale.

Config YAML → PG Metadata → Federation Catalogs
                               ↓
         Federation engine metadata → Schema Generator → SDL / SQL catalog / Cypher labels / gRPC proto (per role)
                                     ↓
                     Query → Parser → SQL Compiler → Transpiler
                                     ↓
                             Router (Smart Dispatch)
                         /           |            \
                    Federation  Direct PG      Direct MySQL/etc.
                         \           |            /
                              Executor Pool
                                     ↓
                         ┌───── Inline ─────┐     ┌──── Redirect ────┐
                         │  JSON (HTTP)     │     │  CTAS → S3       │
                         │  Arrow (Flight)  │     │  (Parquet, ORC)  │
                         │  Protobuf (gRPC) │     │  Provisa → S3    │
                         └─────────────────-┘     │  (JSON, CSV, …)  │
                                                  └─────────────────-┘

Query Interfaces¶

Each interface is a separate transport, and all four apply the same security pipeline (RLS, masking, sampling, role checks). Clients never talk to the federation engine directly. The query language (SQL, GraphQL or Cypher) is independent of the transport, and several languages can arrive over the same transport.

Port	Transport	Accepted query languages	Use case
8001	HTTP	GraphQL, SQL, Cypher	Web clients, BI tools, curl, REST consumers
8815	Arrow Flight (gRPC)	SQL (via Arrow Flight SQL)	Data tools (Pandas, DuckDB, Spark, ADBC)
50051	Protobuf gRPC	Per-role generated proto RPCs	Service-to-service with typed contracts
configurable¹	PostgreSQL wire protocol (pgwire)	SQL	psql, DBeaver, SQLAlchemy, any PG-compatible client

¹ Set PROVISA_PGWIRE_PORT (e.g. 5433). Disabled when unset or 0.

HTTP (port 8001)¶

Multiple endpoints under the same port, distinguished by path:

Path	Language	Notes
`POST /data/graphql`	GraphQL	Reads and mutations; APQ hash accepted via `extensions.persistedQuery`
`POST /data/sql`	SQL	Read-only; no capability gate — governed by object visibility + RLS + masking
`POST /data/query`	Cypher	Read-only; standard role
`GET /data/nl`	Natural language	Translates to SQL/GraphQL/Cypher based on source type
`GET /data/subscribe/{table}`	GraphQL	SSE subscription stream
`GET /neo4j/...`	Cypher (Neo4j compat)	Neo4j HTTP API compatibility shim
`POST /admin/graphql`	GraphQL	Admin API (superuser/admin role required)

All paths return JSON by default. Accept: text/csv, application/vnd.apache.parquet, application/vnd.apache.arrow.stream, and application/octet-stream (raw binary) are supported via content negotiation. Results exceeding the configured size threshold are automatically redirected to a signed S3 URL.

Arrow Flight (port 8815)¶

Native Arrow columnar transport over gRPC. Clients send a JSON ticket:

{"query": "SELECT name, email FROM customers", "role": "analyst"}

and receive Arrow RecordBatches streamed lazily. When the Zaychik Flight SQL proxy is available, data flows as a stream of Arrow record batches end-to-end:

Client ←(Arrow batches)← Provisa Flight Server ←(Arrow batches)← Zaychik ←(JDBC)← Federation Engine

The full result is never held in Provisa's memory: batches are passed on as they arrive. That makes Arrow Flight an unbounded path, fit for results of any size.

Protobuf gRPC (port 50051)¶

Auto-generated .proto from the data schema, generated per role. Streaming queries (one message per row), unary mutations. Server reflection enabled; it describes each caller the service of its own role. Role via x-provisa-role metadata key.

PostgreSQL wire protocol / pgwire (configurable port)¶

Implements the PostgreSQL frontend/backend wire protocol using the buenavista library. Any PostgreSQL-compatible client (psql, DBeaver, SQLAlchemy with psycopg2, JDBC) can connect without modification. Accepts SQL only. The full governance pipeline (RLS, masking, domain permissions) applies identically to pgwire connections. Enabled by setting PROVISA_PGWIRE_PORT to a non-zero port.

Request Pipeline¶

Three query languages are accepted, and all three meet at governance after their own parse and compile steps. All three can also write: GraphQL mutations, SQL INSERT, UPDATE, DELETE, MERGE and TRUNCATE, and Cypher CREATE, SET and DELETE lower to SQL and pass the same write admission. Querying itself has no capability gate. Any authenticated identity may query in any language, and the data is governed only by object visibility, RLS and masking.

Interface	Reads	Writes	Query gate
GraphQL (`/data/graphql`)	Yes	Yes (mutations)	None — data-layer governance only
SQL (`/data/sql`)	Yes	No	None — data-layer governance only
Cypher (`/data/query`)	Yes	No	None — data-layer governance only

flowchart TD
    A[GraphQL Request] --> B[Auth / Role Resolution]
    A2[SQL Request] --> B
    A3[Cypher Request] --> B
    B --> E[APQ Hash Check]
    E --> F[Parse & Validate]
    F --> G[Extract Directives / Hints]
    G --> H{Cache Hit?}
    H -- yes --> R
    H -- no --> I{Input Type}
    I -- GraphQL --> I1[Compile → Semantic SQL]
    I -- SQL --> I2[Parse & Validate SQL\nApply Namespace / Source Binding]
    I -- Cypher --> I3[Translate Cypher → SQL\nResolve Node / Rel Mappings]
    I1 --> J[Governance: RLS + Masking + Visibility + Sampling]
    I2 --> J
    I3 --> J
    J --> K[MV Rewrite]
    K --> L{Route}
    L -- Direct --> M[Transpile → Source Dialect\nExecute via Driver]
    L -- Federation --> N[Transpile → Federation SQL\nInject Session Hints\nExecute via Federation Engine / Flight]
    L -- Materialize --> O[Fetch from REST / GraphQL / gRPC\nMaterialize → S3 Parquet\nPost-filter via Federation Engine]
    L -- Mutation --> P[RLS Injection\nTranspile → Source Dialect\nExecute via Driver\nInvalidate Cache + MV\nEmit Change Event]
    M --> Q{Redirect?}
    N --> Q
    O --> Q
    Q -- yes --> S[Upload to S3\nReturn Signed URL]
    Q -- no --> R[Serialize: JSON / CSV / Parquet / Arrow]
    R --> T[Store in Cache]
    T --> U[Return to Client]
    P --> U

Route decisions:

Route	When
Cache	Result cache hit — evaluated first, serves the stored result with no execution
Cheap-count	`count(*)`-shaped query over an unmaterialized source that exposes an exact native count — routed to the native count call instead of materializing to count
Direct	Single source + has native driver + has federation connector
Federation	Multi-source federation, or source has connector but no driver
Materialize	Source has no federation connector — fetch and cache to S3/PG first
Mutation	A write: the source's own driver when it has one; otherwise the engine, through a connector that writes the source

Routing consumes the output of the post-governance optimization stage, never the pre-optimization governed SQL. Governance may ADD sources (RLS subquery predicates); the optimization stage may REMOVE them (hot-table VALUES-CTE inlining, API-cache rewrites, union-branch pruning). A federated query that collapses to a single live source after inlining is therefore re-routed as direct.

Multi-Root Queries¶

GraphQL queries with multiple root fields (e.g., { orders { id } customers { name } }) are compiled into separate SQL queries and executed independently. SQL and Cypher requests are single-root by definition. Results are merged into a single response:

Fields below the redirect threshold are returned inline in data
Fields above the threshold are redirected, with per-field entries in redirects
Binary formats (Parquet, Arrow) are only supported for single-root queries

Query Pipeline¶

Every read passes through five stages. GraphQL, Cypher, SPARQL, gRPC and SQL are entry points, not stages — each compiles or lowers to semantic SQL before the pipeline begins.

flowchart TD
    REST[REST and JSON:API] --> GQL[GraphQL]
    GQL --> SEM[Semantic SQL]
    GRPC[gRPC] --> GQL
    CY[Cypher] --> GP[Graph pattern form]
    SP[SPARQL] --> GP
    GP --> SEM
    SQL[SQL] --> SEM
    SEM --> GOV[Governed SQL]
    GOV --> OPT[Optimized SQL]
    OPT --> ROUTE{Plan and route}
    ROUTE -- "one source, direct driver" --> DIRECT[Native SQL in the source's dialect]
    ROUTE -- "otherwise" --> ENGINE[Native SQL in the engine's dialect]

Front ends¶

A front end parses a language and names what it reads in the model's terms. GraphQL compiles straight to semantic SQL. The REST endpoints that the OpenAPI document describes, the JSON:API endpoints and gRPC build a GraphQL query from each request and compile it the same way. The one exception is JSON:API's page[total], the total row count, which the JSON:API transport composes itself. Cypher and SPARQL parse into one graph pattern form that both graph languages share, and that form compiles to semantic SQL. SQL you write against the model's tables is already semantic SQL, so it enters at the next stage. No front end decides who may read what.

Semantic SQL¶

Semantic SQL reads the model. Its tables are domain-qualified (sales.orders), its columns are the ones the model publishes, and its joins are the declared relationships. It says nothing about where the data lives.

A nested read compiles to one canonical CTE pipeline, the same for every engine and built from the declared relationships alone. A root CTE reads the root table with its filter, order, limit and offset. Each to-many level is a CTE of its own. It joins its table to the parent CTE on the relationship's keys, takes each parent's first rows with ROW_NUMBER() OVER (PARTITION BY ...), and aggregates them into a JSON list per parent. The final SELECT reads the root CTE and left-joins each level on the parent key. GraphQL, Cypher and SPARQL reads that ask the same question compile to the same statement.

The Cypher translator writes one CTE per WITH stage; aligning those shapes to the canonical pipeline is follow-on work. Until it is complete, equivalent Cypher and GraphQL reads do not always compile to the same semantic SQL. The GraphQL compiler writes nested fields with arguments as per-parent LEFT JOIN LATERAL joins. Both current forms appear in the example below.

Governed SQL¶

Governance works on the whole semantic statement at once. Every SELECT in it gets the reading role's visibility rules, row-level security and masks, so each nested level is filtered at its own depth. A row-level rule whose condition is a subquery reads another table, so governance can add sources to a statement.

Optimized SQL¶

The optimized stage rewrites the governed statement in ways that hold on every engine. It puts materialized views back in where they answer part of the statement, inlines small hot tables as VALUES, and drops API-cache and union branches the statement can't reach. Nothing here knows which engine will run the statement. These rewrites can remove sources. A federated read whose other sources are all inlined is left with one live source.

Plan and route¶

Landing on the federation engine is the default; routing reads the optimized sources, never the governed ones. A statement runs directly on its source only when it passes all six gates in order. The first gate it fails names the reason.

Gate	What it checks
`one_source`	Reads one source after optimization, or reads sources that share a physical database
`dialect_supports`	That source's dialect supports every construct in the statement
`access_path`	A direct driver, an API caller, or a code function exists for the source
`colocated_login`	Sources counted as one database share the same login credential
`not_forced`	No operator floor, faked column, or engine-required condition applies
`trusted_to_answer`	A non-SQL source can answer the complete dataset without further in-process filtering

See Request Pipeline for the cache, replica and steward cases.

Native SQL¶

The native stage writes the routed statement in the source's dialect for a direct read and in the engine's for a federated one. It picks the spelling of the JSON aggregate and object functions, writes the empty list for a parent with no rows, turns DISTINCT ON into ROW_NUMBER() where the dialect has none, and chooses engine-specific forms such as a per-parent LATERAL join or a CTE materialization hint.

A worked example¶

This GraphQL read asks for one customer and that customer's three largest orders:

{
  customers(where: { name: { eq: "Acme" } }) {
    id
    name
    orders(order_by: { amount: desc }, limit: 3) {
      id
      amount
    }
  }
}

In the canonical form, it compiles to this semantic SQL:

WITH customers AS (
  SELECT c.id, c.name
  FROM sales.customers AS c
  WHERE c.name = 'Acme'
),
customers_orders AS (
  SELECT ranked.customer_id,
         JSON_AGG(JSON_OBJECT('id', ranked.id, 'amount', ranked.amount)
                  ORDER BY ranked.amount DESC, ranked.id) AS orders
  FROM (
    SELECT o.customer_id, o.id, o.amount,
           ROW_NUMBER() OVER (PARTITION BY o.customer_id
                              ORDER BY o.amount DESC, o.id) AS rn
    FROM sales.orders AS o
    JOIN customers AS p ON o.customer_id = p.id
  ) AS ranked
  WHERE ranked.rn <= 3
  GROUP BY ranked.customer_id
)
SELECT customers.id, customers.name, customers_orders.orders
FROM customers
LEFT JOIN customers_orders ON customers_orders.customer_id = customers.id

The order's id breaks ties in the ranking, so two orders of the same amount always come back in the same order. A customer with no orders gets no row in customers_orders; the native stage writes the empty list for it.

The same read in Cypher matches the customer and reads the three largest orders in a subquery:

MATCH (a:Customers)
WHERE a.name = 'Acme'
CALL {
  WITH a
  OPTIONAL MATCH (a)-[:HAS_ORDERS]->(b:Orders)
  RETURN b.id AS id, b.amount AS amount
  ORDER BY b.amount DESC
  LIMIT 3
}
RETURN a.id, a.name, id AS orders__id, amount AS orders__amount

In the canonical form, this Cypher read and the GraphQL read compile to the statement above. That is what lets Open as query hand a GraphQL read to the Cypher page and get the same answer back.

Today the GraphQL compiler writes the nested level as a per-parent lateral join instead:

SELECT "t0"."id", "t0"."name", "t1"."id" AS "orders__id", "t1"."amount" AS "orders__amount"
FROM (SELECT * FROM "sales"."customers" "t0" WHERE "t0"."name" = $2 LIMIT 100) "t0"
LEFT JOIN LATERAL (
  SELECT * FROM "sales"."orders" WHERE "customer_id" = "t0"."id" ORDER BY "amount" DESC LIMIT $1
) "t1" ON TRUE

You can see the semantic SQL for any GraphQL read in the Provisa panel of the GraphQL page.

Federation Execution Paths¶

Path	Transport	Via	When used
REST	federation engine client (HTTP :8080)	Direct query	Default, always available
Flight SQL	`adbc-driver-flightsql` (gRPC :8480)	Zaychik proxy → JDBC	When Zaychik is running
CTAS	federation engine client (HTTP :8080)	Direct write, Iceberg to S3	Parquet/ORC redirect

Zaychik Arrow Flight SQL Proxy¶

The federation engine does not natively support the Arrow Flight SQL protocol. Zaychik is a Java proxy that implements the Arrow Flight SQL gRPC interface, translates requests to JDBC queries, and streams results back as Arrow record batches.

ADBC client → gRPC :8480 → Zaychik → JDBC :8080 → Federation Engine → results → Arrow batches → client

The Provisa Flight server (port 8815) connects to Zaychik as an ADBC client, so Arrow streams end to end and results are never materialized.

Iceberg Results Catalog¶

CTAS redirect uses an Iceberg connector (results catalog) backed by a JDBC catalog on the existing PostgreSQL instance. Iceberg writes Parquet/ORC files directly to MinIO/S3 via the native S3 filesystem (fs.native-s3.enabled=true).

Federation Engines¶

Provisa picks a federation engine at startup from the PROVISA_ENGINE environment variable, the config saved from the admin UI, or the default. When nothing is set, the default is DuckDB, fully in-process with no external service. See Configuration for how selection works.

Every engine is a FederationEngine instance defined in provisa/federation/engine.py. The instance owns a connector collection that determines which source types the engine can read live (ATTACH) versus which must land into the engine's materialization store first.

Driver classes¶

Class	Meaning	Examples
`BROAD`	Reaches many external source types via native connectors	Trino
`PARTIAL`	Reaches a subset (relational, files, cloud object/lake) plus lands everything else	DuckDB, PostgreSQL, ClickHouse, Databricks, Snowflake, BigQuery, Fabric, Synapse
`SELF_ONLY`	Reaches only its own store; every other source lands in	SQLAlchemy

Available engines¶

Engine key	Dialect	MPP	External-link mechanism	Auth
`trino` / `trino-byo`	Trino SQL	Yes	Trino catalogs (broad connector set)	JDBC credentials
`pg`	PostgreSQL	No	FDW / pg_duckdb	PostgreSQL credentials
`duckdb`	DuckDB	No	Extension-native ATTACH	None (in-process)
`clickhouse` / `clickhouse-server`	ClickHouse	Yes (shards)	S3 / IcebergS3 / DeltaLake table engines	ClickHouse credentials
`snowflake`	Snowflake	Yes	External stage + external table	`PROVISA_ENGINE_URL`
`databricks`	Databricks SQL	Yes	Unity Catalog external tables via REST	Bearer token (`http_path` in `federation_hints`)
`bigquery`	BigQuery	Yes (Dremel)	BigQuery external / BigLake tables	`GOOGLE_APPLICATION_CREDENTIALS` service-account key
`fabric`	T-SQL	Yes	OneLake shortcuts → OPENROWSET	Azure AD (`az login` / managed identity)
`synapse`	T-SQL	Yes	ADLS OPENROWSET / external tables	Azure AD
`sqlalchemy`	Any SQLAlchemy dialect	No	None (land-only)	Per-dialect credentials

Zero-config default: DuckDB¶

When PROVISA_ENGINE is unset, Provisa uses the fully embedded in-process DuckDB engine. DuckDB's materialization store is an embedded DuckDB file at $PROVISA_DATA_DIR/materialize.duckdb (defaulting to ~/.provisa/materialize.duckdb). No external database or service is required.

DuckDB allows a single writer per file, so store_connection.py writes into the embedded store through the engine's own connection and never opens a second, independent one. This is the one case where the engine and the materialization store share a file handle by design.

Arrow-native read transport¶

ClickHouse, DuckDB, Snowflake, Databricks, BigQuery, Fabric, and Synapse all advertise EngineCapability.ARROW and EngineCapability.ARROW_STREAM. Queries against these engines return Arrow RecordBatches directly; the row-serialization path is bypassed entirely. The Flight server streams those batches to clients without materializing the full result in Provisa's process memory. For Trino, Arrow streaming relies on the Zaychik proxy; for the warehouse engines, the engine's own Arrow-native API (Cloud Fetch for Databricks, Storage Read API for BigQuery, fetch_arrow_table for DuckDB and Snowflake) feeds the Flight stream.

External data links (ATTACH)¶

Every warehouse engine can scan cloud object/lake data in place without landing a copy. Parquet, CSV, Iceberg, and Delta Lake files on S3, GCS, or OneLake attach directly to the engine as if they were native tables. The strategy (ATTACH (scan in place) or LAND (copy into the store)) is determined by the connector's declared Mechanism; no engine-specific branching exists in the planner. A Mechanism.ATTACH_R connector triggers zero-copy scan; a Mechanism.DIRECT or absent connector triggers a land.

Attach auto-provisions all prerequisites at attach time:

Engine	Object/lake formats	Mechanism	Auto-provisioning
Databricks	parquet, csv, iceberg, delta_lake	UC external table (`ATTACH_R`)	REST installs Unity Catalog storage credential + external location, then `CREATE TABLE … USING <format> LOCATION …` — live-verified over Cloudflare R2
BigQuery	parquet, csv, json, iceberg, delta_lake	BigQuery external / BigLake table (`ATTACH_R`)	`CREATE OR REPLACE EXTERNAL TABLE … OPTIONS(format=…, uris=[…])` — live-verified
ClickHouse	csv, parquet, iceberg, delta_lake	S3 / IcebergS3 / DeltaLake table engine (`ATTACH_R`)	Validation probe executed at attach time — live-verified over Cloudflare R2
Fabric	parquet, csv, iceberg, delta_lake	OneLake shortcut → OPENROWSET (`ATTACH_R`)	REST creates an `AmazonS3Compatible` connection + lakehouse + shortcut; returns the OneLake `BULK` path — live-verified reading R2 through Fabric
Snowflake	parquet, csv, json, iceberg, delta_lake	External stage + external table (`ATTACH_R`)	`CREATE STAGE … URL=… CREDENTIALS=…`, then `CREATE OR REPLACE EXTERNAL TABLE … LOCATION=@stage FILE_FORMAT=(TYPE=…)` — implemented; not live-tested (no account available)

Credentials for cloud storage travel in the source's federation_hints (see Sources). Any source type that cannot ATTACH lands into the engine's materialization store first.

Columnar materialization writes¶

Connection.bulk_copy in provisa/core/database.py chooses the fastest bulk-ingest path per store dialect: binary COPY (asyncpg copy_records_to_table) for PostgreSQL stores, and a single executemany prepared statement for all other relational stores. The DuckDB embedded store lands through land_duckdb_native in store_connection.py: one executemany call for the whole batch, never a per-row loop.

Large Result Redirect¶

Results exceeding a row threshold are redirected to S3-compatible storage (MinIO) instead of being returned inline.

Redirect Modes¶

Mode	How it works	Data touches Provisa?
CTAS (Parquet, ORC)	Federation engine writes directly to S3 via `CREATE TABLE AS SELECT`	No
Provisa upload (JSON, NDJSON, CSV, Arrow IPC)	Provisa serializes and uploads via boto3	Yes

For CTAS-native formats, Provisa never touches the data: the federation engine writes files straight to MinIO/S3. This is the path to prefer for large analytical exports.

With no object store¶

A result goes to the object store when one is configured (an endpoint, or credentials for AWS S3) and the engine writes the requested format to it itself. Otherwise it is written to a file in the org's folder, <data directory>/orgs/<org>/results/. A configured store that the engine writes to but cannot reach fails the request by name. The result is not written to the folder instead.

Engine	Writes the file itself	Formats
DuckDB	`COPY (query) TO '<file>'`	parquet, csv, json, ndjson
PostgreSQL	`COPY (query) TO STDOUT`, streamed into the file on Provisa's host	csv
Any other format or engine	Provisa writes it from the rows	parquet, orc, csv, arrow, json, ndjson

The handle has the same keys as the object store's, with "sink": "local" and a redirect_url of /data/redirect-file/<name>, a path on the server that answered. Only the user who asked for the result may fetch it. Each fetch compares the request's own identity with the owner recorded beside the file, and answers 403 to anyone else and 401 to a request with no identity.

A file older than redirect.ttl is answered 404 and deleted. A timer deletes it at its expiry, as the object store's results are deleted. Each new result also sweeps the org's results folder, which removes files whose timer a restart ended.

Redirect Headers¶

Header	Effect
`X-Provisa-Redirect-Format: <mime>`	Redirect in this format (implies force unless threshold set)
`X-Provisa-Redirect-Threshold: N`	Only redirect if result exceeds N rows
`X-Provisa-Redirect: true`	Force redirect using default format

These headers implement client-controlled redirect.

Response:

{
  "data": {"orders": null},
  "redirect": {
    "redirect_url": "https://minio:9000/provisa-results/results/abc.parquet?...",
    "row_count": 50000,
    "expires_in": 3600,
    "content_type": "application/vnd.apache.parquet"
  }
}

Server Configuration¶

Env var	Default	Purpose
`PROVISA_REDIRECT_ENABLED`	`false`	Enable server-side threshold redirect
`PROVISA_REDIRECT_THRESHOLD`	`1000`	Default row count threshold
`PROVISA_REDIRECT_FORMAT`	`parquet`	Default redirect format
`PROVISA_REDIRECT_BUCKET`	`provisa-results`	S3 bucket name
`PROVISA_REDIRECT_ENDPOINT`		S3-compatible endpoint URL
`PROVISA_REDIRECT_TTL`	`3600`	Presigned URL TTL (seconds)

Routing Decision Tree¶

Multi-source query? → Federation engine
NoSQL source (MongoDB, Cassandra)? → Federation engine
Uses path columns on non-PG source? → Federation engine
Single RDBMS with driver? → Direct (sub-100ms target)
Single RDBMS without driver? → Federation engine
Steward hint "federated"? → Federation engine (override)
Steward hint "direct"? → Direct (if possible)
Redirect to Parquet/ORC? → Federation engine (CTAS, regardless of source count)

Federation Query Optimization¶

Provisa primes the federation engine's cost-based optimizer automatically, so cross-source query plans are based on how the data is really distributed and don't rely on hardcoded defaults.

Automatic Statistics (`ANALYZE`)¶

On source registration, Provisa runs ANALYZE catalog.schema.table for every published table. This collects:

Row count
Per-column: null fraction, distinct value count, min/max, histograms (connector-dependent)

The optimizer uses these to estimate selectivity for filtered queries. Without statistics, it falls back to fixed defaults (e.g., 10% selectivity for equality predicates) which produce poor join plans on skewed or high-cardinality data. With statistics, estimates are accurate enough to make correct broadcast vs. partitioned join decisions for most workloads.

Coverage: statistics support depends on the connector. PostgreSQL, MySQL, Hive, Iceberg and Delta Lake fully support ANALYZE; MongoDB and Cassandra have partial support or none. A catalog whose connector collects no statistics is skipped, and an ANALYZE that fails is logged and passed over, so registration is never blocked.

Selectivity limits: statistics provide per-column estimates. For correlated predicates (WHERE region = 'US' AND city = 'Seattle'), the optimizer assumes column independence, which may underestimate row counts. This is a known limitation of column-level statistics in all cost-based optimizers.

API sources: api_cache_{table_name} tables in PostgreSQL are analyzed automatically after each cache refresh cycle, so the optimizer has current row estimates when joining API-backed sources with relational sources.

Admin: Refresh Statistics¶

Re-run statistics collection on demand via the admin API:

mutation {
  refreshSourceStatistics(sourceId: "sales-pg") {
    tablesAnalyzed
    failures { table message }
  }
}

Useful when a source has received significant new data since registration.

Materialized Views¶

MVs speed up expensive queries by computing their results ahead of time. Queries pick them up with no change.

Relationships as MV Hints¶

A relationship declaration is a governance artifact, and it's also the structural description of a join shape. That shape (the tables, the columns, the join type) is exactly what the MV optimizer needs, so a relationship can drive materialization directly.

For cross-source relationships this happens automatically at startup: every relationship with materialize: true whose legs land in more than one source generates a JoinPattern MV (auto-mv-<rel_id>), with no separate MV config. When the compiler sees that join in a query, the rewriter swaps in the pre-materialized result without the caller noticing. Same-source relationships generate nothing, because those JOINs are already fast through direct execution.

A junction-backed relationship materializes its whole traversal. The associative table is a third leg, so the pattern carries the source hop, the junction hop and the discriminator that pins the rows to one edge type, with the junction's own columns landing in the view next to the target's. Because the junction counts as a leg, an edge whose junction sits in a different source from the two tables it links is cross-source even when those two share a source. The rewriter matches the two hops as a chain, where the second must start from the alias the first introduced. So a query that reaches the same two tables without going through the junction reads the base tables, and a view built for one discriminator value never answers a traversal filtered to another.

The practical consequence: stewards who approve a relationship are implicitly deciding whether the join is a good candidate for materialization. The governance act and the optimization hint are the same declaration.

Modes¶

Mode	Config	Behavior
Join-pattern	`join_pattern` in MV config	Rewrites matching JOINs to read from MV table
Custom SQL	`sql` in MV config	Arbitrary SELECT, optionally exposed in SDL
Auto-materialized relationship	cross-source relationship (automatic)	Auto-generates a join-pattern MV; no config required
Junction-backed relationship	`materialize: true` on a junction relationship	Materializes the two-hop traversal, discriminator and edge attributes included

Auto-Materialization¶

Cross-source JOINs are the most expensive queries (always federated). Cross-source relationships automatically generate MV definitions at startup:

relationships:
  - id: orders-to-reviews
    source_table_id: orders        # sales-pg
    target_table_id: product_reviews  # reviews-mongo
    source_column: product_id
    target_column: product_id
    cardinality: one-to-many
    materialize: true              # auto-create MV
    refresh_interval: 600          # refresh every 10 minutes

Only cross-source relationships generate MVs (same-source JOINs are already fast via direct execution). The MV starts in STALE status and is refreshed by the background refresh loop before being used by the query optimizer.

Refresh Lifecycle¶

STALE → (refresh loop picks up) → REFRESHING → FRESH
  ↑                                                |
  └──── mutation hits source table ────────────────┘

The refresh loop runs every 30 seconds, checks get_due_for_refresh(), and executes CREATE TABLE AS SELECT (first run) or DELETE + INSERT (subsequent) against the MV target table via the federation engine.

Module Map¶

Module	Purpose
`api/`	FastAPI app, routers, middleware, lifespan management
`api/flight/`	Arrow Flight server (gRPC, port 8815)
`api/admin/`	Strawberry GraphQL admin API — config, discovery, views
`api/rest/`	Auto-generated REST endpoints from registered tables
`api/jsonapi/`	Auto-generated JSON:API endpoints with pagination and error handling
`api/data/subscribe.py`	SSE subscriptions — LISTEN/NOTIFY, polling, Debezium CDC
`compiler/`	GraphQL/SQL parsers, semantic SQL generator, RLS, masking, sampling, two-stage governance (`stage2.py`)
`cypher/`	Cypher → SQL translator, parser, label map, write translator for Cypher mutations
`pgwire/`	PostgreSQL wire-protocol server; `catalog.py` intercepts pg_catalog/information_schema for per-role object visibility
`vector/`	Vector search — model registry, embedding providers (openai/ollama/huggingface), `cosine_similarity()` translation, HNSW index on a replica's embedding column, declarative embedding generation
`compiler/federation.py`	Apollo Federation v2 subgraph support
`transpiler/`	Dialect transpilation, routing logic
`executor/`	Federated/direct execution, serialization, output formats
`executor/drivers/`	Direct source drivers (PostgreSQL, MySQL, DuckDB, Snowflake, Databricks, ClickHouse, …)
`executor/trino_flight.py`	ADBC Flight SQL client for the federation engine
`executor/ctas_write.py`	CTAS-based redirect (federation engine writes to S3)
`executor/redirect.py`	S3 redirect logic, Provisa-side upload
`federation/engine.py`	`FederationEngine`, `DriverClass`, `_ENGINE_BUILDERS`, `ENGINE_REGISTRY`, `build_engine`
`federation/connector.py`	Connector abstractions — Trino, ClickHouse; `Mechanism`, `WarehouseNativeConnector`
`federation/connector_duckdb.py`	DuckDB and PostgreSQL FDW connector definitions
`federation/snowflake_connectors.py`	Snowflake external stage + external table ATTACH connectors
`federation/databricks_connectors.py`	Databricks UC external table ATTACH connectors
`federation/bigquery_connectors.py`	BigQuery external / BigLake ATTACH connectors
`federation/databricks_uc.py`	Unity Catalog credential + external location auto-provisioning
`federation/databricks_backend.py`	Databricks SQL warehouse execution backend
`federation/snowflake_backend.py`	Snowflake execution backend
`federation/bigquery_backend.py`	BigQuery execution backend (Storage Read API Arrow transport)
`federation/mssql_warehouse_backend.py`	Fabric Warehouse + Synapse execution backends (T-SQL over ODBC)
`federation/mssql_warehouse_connectors.py`	OPENROWSET ATTACH connectors for Fabric / Synapse
`federation/fabric_shortcuts.py`	OneLake shortcut auto-provisioning (connection → lakehouse → shortcut)
`federation/clickhouse_backend.py`	ClickHouse execution backend
`federation/duckdb_backend.py`	DuckDB in-process execution backend
`federation/pg_backend.py`	PostgreSQL execution backend
`federation/store_connection.py`	DuckDB-native materialization store write face
`registry/`	Persisted query registry, governance
`security/`	Visibility, rights, column masking
`cache/`	Redis-backed query result caching (hot tier)
`mv/`	Materialized view registry, refresh, SQL rewriter
`events/`	Dataset change events and trigger dispatch
`webhooks/`	Outbound webhook execution for mutations and events
`scheduler/`	APScheduler-based background job management — cron and interval triggers that fire webhooks and mutations, and the polls of live Kafka outputs
`apq/`	Apollo APQ wire protocol — Redis-backed query hash cache; separate from result caching
`compiler/cursor.py`	Relay-style cursor pagination — `first`/`after`/`last`/`before` arguments and `pageInfo` generation on all list queries
`compiler/aggregate_gen.py`	Auto-generated `{table}_aggregate` query types with `count`, `sum`, `avg`, `min`, `max` sub-fields and filtered `nodes` access
`compiler/enum_detect.py`	Enum type auto-detection — PostgreSQL native enum types (`pg_enum`) exposed as GraphQL enum types rather than string scalars
`compiler/hints.py`	Federation performance hints — query-level routing directives embedded as SQL comments (`-- @provisa route=federated`) that override automatic routing
`compiler/mutation_gen.py`	Mutation compiler; column presets — server-side static or session-variable values applied on insert/update, not exposed in the mutation input type
`auth/approval_hook.py`	ABAC approval hook — pluggable external authorization called before query execution; webhook, gRPC, and unix_socket transports; per-table/source/global scope; configurable fallback policy
`subscriptions/`	SSE subscription state and delivery
`discovery/`	LLM relationship discovery (Claude API)
`grpc/`	Proto generation, gRPC server, reflection
`api_source/`	REST/GraphQL/gRPC API sources with PG cache
`kafka/`	Kafka topic sources, sink, Schema Registry
`auth/`	Pluggable auth providers, middleware, role mapping
`core/`	Config, models, DB, repositories, secrets; role model supports `parents` and `flatten_roles()` for role inheritance; `standard_roles.yaml` defines the standard roles
`hasura_v2/`	Hasura v2 metadata → Provisa config converter
`ddn/`	Hasura DDN supergraph → Provisa config converter
`mongodb/`	MongoDB source connector
`elasticsearch/`	Elasticsearch source connector
`cassandra/`	Cassandra source connector
`prometheus/`	Prometheus metrics source connector
`source_adapters/`	Generic adapter layer for source connections

Admin API¶

The admin Strawberry GraphQL API is mounted at /admin/graphql (HTTP port 8001). It is separate from the data GraphQL endpoint and requires superuser or admin role.

Capability	Description
Config download/upload	Export or replace the full Provisa YAML config
Relationship editor	Create, update, delete relationship definitions
AI FK discovery	Trigger Claude-powered FK candidate analysis
Schema introspection	Browse published tables, columns, and roles
View management	Register and manage materialized view definitions

AI Models Configuration¶

GET /admin/ai-models and PUT /admin/ai-models configure the LLM pipeline for each org.

Settings are org-scoped: each org's choices sit on top of the deployment config and apply from the next request, with no restart.

Per-operation model assignments. Six operations each have a configurable vendor and model string:

Operation	What it drives
`table_description`	LLM-generated table descriptions
`column_description`	LLM-generated column descriptions
`relationship_inference`	FK candidate discovery
`sql_generation`	NL → SQL generation
`table_selection`	Choosing which tables to include in the NL prompt
`mcp_chat`	The chat assistant panel's tool-use loop

The vendor field accepts any aisuite-compatible vendor (anthropic, openai, groq, mistral, cohere, and others) or a local endpoint (ollama, lmstudio). Blank model string removes the org's override and reverts to the deployment default.

NL rate limit. An optional requests-per-period cap applied per role. Excess requests return 429 with Retry-After.

Vector model registry. A list of embedding models (fields: id, provider, dimensions, optional api_key_env and base_url, enabled flag). Full-list replacement: every entry must have id, provider, and dimensions or the write is rejected 400.

API keys. Each vendor's LLM API key is stored encrypted through provisa.core.org_secrets (see below). The GET response says only whether a key is set for each vendor and never returns the value. Sending a blank string for a vendor clears its key, so LLM calls for that vendor go back to the deployment's environment-variable credential.

Per-Org Encrypted Secrets¶

provisa/core/org_secrets.py stores credentials that must never appear as plaintext in the database. It holds the AI vendor API keys ({vendor}_api_key) and the Jev key (jev_api_key).

Values are encrypted with the process-wide encryption_service from provisa.encryption.runtime, the same mechanism as api_sources.auth.

Twelve aisuite-compatible vendors are supported: anthropic, openai, cohere, groq, mistral, xai, deepseek, together, fireworks, nebius, sambanova, and inception. Google, AWS, and Azure are excluded because they require configuration beyond a plain API key (project IDs, IAM roles, region). Local-endpoint vendors (ollama, lmstudio) have no key and are excluded for the same reason.

Passing value=None to write_org_secret deletes the row. Callers that read a secret consume it immediately (e.g. to construct an LLM client) and must not echo it in any API response.

Auto-Generated REST & JSON:API Endpoints¶

Registered tables are exposed as REST and JSON:API endpoints alongside the GraphQL interface.

Interface	Mount path	Spec
REST	`/rest/<table-id>`	Simple GET/POST with query parameters
JSON:API	`/jsonapi/<table-id>`	jsonapi.org compliant — pagination, relationships, error objects

These endpoints apply the same security pipeline (RLS, masking, role checks) as the GraphQL endpoint.

Subscriptions¶

SSE subscriptions are served at GET /data/subscribe/{table}. Three delivery modes:

Mode	Mechanism	When used
LISTEN/NOTIFY	PostgreSQL `LISTEN` on a channel	PG sources with mutation activity
Polling	Re-execute query on interval	Non-PG sources, or when CDC unavailable
Debezium CDC	Kafka topic from Debezium connector	High-frequency change streams

The client receives text/event-stream with one JSON event per changed row or diff.

Event & Webhook System¶

Database mutations (INSERT/UPDATE/DELETE) can trigger outbound events via the events/ and webhooks/ modules.

Mutation executed → EventDispatcher → match event trigger rules
                                          ↓
                               WebhookExecutor → HTTP POST to configured URL

Event triggers are defined in config and matched on table, operation type, and optional row filter. Webhook payloads include the operation type, changed row, and role context.

Background Services¶

Four background loops start during app lifespan (api/app.py):

Service	Interval	Purpose
MV refresh loop	30 s	Polls `get_due_for_refresh()`, executes CTAS or DELETE+INSERT on stale MVs
Hot replication evaluation	`replication.hot_interval`	Promotes a table whose statement count passed its threshold and requests its replica; demotes one that fell below half. Runs in the one process holding the scheduler lock
Hot table loader	Configurable	Loads small reference tables into in-memory cache for sub-millisecond access
API source poller	Per-source interval	Re-fetches and re-caches remote REST/GraphQL/gRPC sources

Hot Tables and Replicated-When-Busy Tables¶

Tier	Storage	Promotion criteria	Access latency
Hot	In-process memory	Row count < threshold, or is a relationship target	<1 ms
Replicated when busy	Replica in the engine's store	`replicate` is Default or Hot-N and the governed statements reading the table passed its threshold	The engine's own read of its store
Cold	Remote source	Default	50–500 ms

A table lives in at most one of the two: a table the hot tier manages is not replicated for being busy.

Metadata Import (Hasura v2 / DDN)¶

Existing Hasura deployments can be converted to Provisa config without manual rewriting.

Module	Input	Output
`hasura_v2/`	Hasura v2 `metadata.yaml`	Provisa `config.yaml`
`ddn/`	Hasura DDN supergraph JSON	Provisa `config.yaml`

Both converters map tracked tables, relationships, permissions, and remote schemas. The result is a complete Provisa config ready for deployment.

Apollo Federation¶

compiler/federation.py exposes Provisa as an Apollo Federation v2 subgraph. The subgraph SDL is auto-generated from the published schema with @key directives on primary-key columns and @external/@provides annotations on cross-subgraph relationships. Provisa responds to _entities and _service queries required by the federation gateway.

Cursor-Based Pagination¶

All list queries support Relay-style cursor pagination via compiler/cursor.py. Clients pass first/after (forward) or last/before (backward) arguments. The compiler encodes row position as an opaque base64 cursor and injects the appropriate WHERE/LIMIT clauses. Every list query returns a pageInfo object:

Field	Type	Description
`hasNextPage`	Boolean	True if more results exist after this page
`hasPreviousPage`	Boolean	True if results exist before this page
`startCursor`	String	Cursor of the first node in this page
`endCursor`	String	Cursor of the last node in this page

Aggregate Queries¶

Every registered table gets an auto-generated {table}_aggregate root field (compiler/aggregate_gen.py). The aggregate type exposes count, sum, avg, min, max per numeric column, and nodes for filtered row access with full field selection (same RLS/masking as the base query). An aggregate over one table, from any surface (SQL or compiled), is answered from a fresh materialized view that pre-aggregates it, with nothing declared: a view whose SQL is a single-table GROUP BY of named SUM, COUNT, MIN and MAX serves the aggregates that group by a subset of its grouping columns and filter only on them. Each aggregate is re-aggregated over the stored ones (COUNT as the sum of the stored counts, AVG from a stored sum and count of the same column); anything the view cannot answer exactly is left to the table. The rewrite runs after governance, so a row filter on one of the view's grouping columns is carried onto the view, and a row filter on any other column, or a mask over a summed column, leaves the read to the table.

Automatic Persisted Queries (APQ)¶

apq/cache.py implements the Apollo APQ wire protocol. When a client sends only a query hash (extensions.persistedQuery), Provisa looks it up in Redis. On a miss it returns a PersistedQueryNotFound error; the client retries with the full query body, which Provisa stores. This is separate from result caching (cache/).

Inherited Roles¶

A role names the roles it inherits from in parents, in order. At runtime build, security/inheritance.py walks each role's ancestors depth-first in that order, each once, and folds them into the build's own copies of the loaded data, so every later lookup stays keyed by the acting role id. Capabilities are the union over the ancestors; domain_access is the role's own and is not inherited. A column's visible_to, each entry of its mask list, and a metric's, function's or webhook's visible_to, hold for a role when they name it or an ancestor. RLS resolves per table: the role's own rule (its table rule before its domain rule) stands; otherwise the rules its parents resolve are combined with OR into one predicate, and a parent that resolves none adds nothing. With one parent this is the nearest rule in the chain. Write grants resolve per operation the same way: their columns are unioned and their row and check conditions combined with OR. A property (rate limit, max_rows, a session value) comes from the role, else the first ancestor that sets it. A parent must exist, be named once, not be the role itself and not close a cycle; a role other roles inherit from cannot be deleted until they are reparented.

The standard roles are defined in core/standard_roles.yaml, written into every model schema on each init_schema (db.assert_standard_roles) and refused by the admin surfaces and the config loader. The sandbox role a "Try it Out" visitor holds is a union of standard roles; the visitor reads a copied demo's columns through the grants that name viewer or data_steward, which the sandbox inherits.

ABAC Approval Hook¶

auth/approval_hook.py is a pluggable authorization hook invoked before query execution, after RLS and masking. It integrates with external policy engines (OPA, custom ABAC services).

Setting	Description
Transport	`webhook` (HTTP POST), `grpc`, or `unix_socket`
Scope	Per-table, per-source, or global
Fallback policy	`allow` or `deny` when the hook endpoint is unreachable

Enum Type Auto-Detection¶

compiler/enum_detect.py introspects PostgreSQL native enum types (pg_enum) when the schema is generated. A column of a user-defined PostgreSQL enum type becomes a GraphQL enum type, and its values become enum members instead of string scalars.

Scheduled Triggers¶

scheduler/jobs.py uses APScheduler to run background jobs defined as cron or interval triggers. Each job can POST to a webhook URL or execute a mutation against the data endpoint; a table's live Kafka output (live.outputs) is polled on the same scheduler. Triggers are configured via the admin API (scheduledTrigger mutations) or the scheduled_triggers key in the YAML config.

Federation Performance Hints¶

compiler/hints.py parses steward hints embedded in queries as comments using Provisa's comment syntax. The hint format varies by query language:

# @provisa route=federated
{ orders { id amount } }

-- @provisa route=federated
SELECT id, amount FROM orders

// @provisa route=federated
MATCH (o:Order) RETURN o.id, o.amount

Hint	Effect
`route=federated`	Force federation through the federation engine, bypassing direct-driver routing
`route=direct`	Force direct-driver execution
`cache=true`	Opt this query into the response cache (SQL comment form: `-- @provisa cache=true`)
`cache_ttl=N`	Opt in, and keep the cached result for N seconds (`-- @provisa cache_ttl=N`)

The response cache is opt-in per request: GraphQL uses the @cached(ttl: Int) directive, SQL and Cypher use the cache/cache_ttl comment, gRPC uses the x-provisa-cache / x-provisa-cache-ttl call metadata. The hint applies on every transport that carries the language (Cypher over HTTP, Bolt and Arrow Flight; GraphQL over HTTP and Arrow Flight). A request without one never reads or writes the cache, and neither form reads fresher data than the operator's source and table settings allow. See Cache.

Write Grants and Presets in Mutations¶

A table's write_grants state, per role and operation, which columns a write may name, which rows it may touch, and which values the grant sets itself. compiler/write_admission.py admits every write in the governance stage, so GraphQL, SQL, Cypher and the other surfaces get the same answer: the write right, the role's grant for the operation, the columns, the presets added to the statement, the rows predicate ANDed into the WHERE, and the check evaluated on the new rows before anything reaches a source. Presets come from a session variable, a literal or the time of the write. The statement may not supply a preset column, so presets are not in the generated GraphQL mutation input type.

GraphQL Voyager Schema Explorer¶

The admin UI (provisa-ui/src/pages/SchemaExplorer.tsx) embeds GraphQL Voyager as an interactive schema visualization. It draws the role-scoped schema as an entity relationship diagram you can pan, zoom and click through, with tables as nodes and relationships as edges. The schema it shows is always filtered to the role currently selected.

Security Enforcement Order¶

Querying has no capability gate; governance comes entirely from controls at the data layer. A raw-SQL request is refused (HTTP 403) for any table outside the role's object scope before governance runs.

Object Visibility: Per-role schema hides unauthorized tables/columns; out-of-scope tables in raw SQL are rejected
Relationship enforcement: Traversals must exist in the approved relationship catalog, unless the role holds ignore_relationships. Among the seeded system roles, only modeler does. In high-security mode the capability is ignored and no traversal escapes the catalog
RLS: Per-table per-role WHERE clause injection
Column Masking: Per-column per-role data transformation
Row cap (LIMIT): Row-count cap for roles without full_results; random statistical sampling is a separate user query feature

All four query interfaces (HTTP, Flight, gRPC, pgwire) enforce the same Stage 2 governance pipeline; no client path can bypass it without bypassing the server.

Scalability Limits¶

Provisa is a thin layer for compiling and routing, and it adds single-digit milliseconds to query latency. Paths where Provisa serializes the result data are bounded by process memory, though. Two paths have no bound at all:

Path	Memory bound?	Suitable for
JSON inline (HTTP)	Yes	Small-medium results
Arrow Flight streaming (gRPC :8815)	No	Unbounded — streaming via Zaychik or warehouse Arrow API
Protobuf gRPC inline (:50051)	Yes	Medium results, service-to-service
Redirect: Provisa upload (JSON, CSV, NDJSON, Arrow IPC)	Yes	Medium results, file download
Redirect: CTAS (Parquet, ORC)	No	Unbounded — federation engine writes to S3

Threshold Probing¶

For threshold-based redirect, Provisa injects LIMIT threshold + 1 into the query as a probe. If the result has fewer rows, it returns inline (complete result, no wasted work). If the result hits the limit, the probe is discarded and the full query is re-executed via CTAS or Provisa upload. This avoids SELECT COUNT(*) (which some sources don't optimize) and works on every source.

For large analytical workloads, use either:

Arrow Flight (port 8815) for streaming to data tools; batches flow through Provisa without materializing
Parquet/ORC redirect for file-based exports: the federation engine writes directly to S3 and Provisa returns a presigned URL

Infrastructure¶

Service	Image	Port	Purpose
Provisa API	(host process)	8001	HTTP/REST endpoint
Provisa Flight	(host process)	8815	Arrow Flight gRPC server
Provisa gRPC	(host process)	50051	Protobuf gRPC server
Federation Engine	`trinodb/trino` (default) or external warehouse	8080 / varies	Query federation engine — Trino for the embedded stack; Snowflake/Databricks/BigQuery/Fabric/Synapse/DuckDB for warehouse targets
Zaychik	`provisa-zaychik` (built from source)	8480	Arrow Flight SQL proxy for Trino; not required for warehouse engines
PostgreSQL	`postgres:16`	5432	Config metadata + Iceberg catalog
MongoDB	`mongo:7`	27017	Demo NoSQL data source
MinIO	`minio/minio`	9000/9001	S3-compatible object storage
Redis	`redis:7-alpine`	6379	Query result cache
PgBouncer	`edoburu/pgbouncer`	6432	Connection pooling for PG
Kafka	`confluentinc/cp-kafka:7.6.0`	9092	Streaming data sources
Schema Registry	`confluentinc/cp-schema-registry:7.6.0`	8081	Avro/Protobuf schema management