Metadata-Version: 2.5
Name: canvasxpress-connectors
Version: 0.8.0
Summary: Feed CanvasXpress from authenticated data sources (databases, Google Sheets) — reshape rows into CanvasXpress data objects behind your own origin.
Project-URL: Homepage, https://github.com/neuhausi/canvasxpress-connectors
Author: Isaac Neuhaus
License: MIT
License-File: LICENSE
Keywords: canvasxpress,data-proxy,google-sheets,oauth,visualization
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Requires-Python: >=3.9
Requires-Dist: cryptography>=42.0
Provides-Extra: all
Requires-Dist: duckdb-engine>=0.13; extra == 'all'
Requires-Dist: duckdb>=1.0; extra == 'all'
Requires-Dist: fastapi>=0.110; extra == 'all'
Requires-Dist: google-api-python-client>=2.120; extra == 'all'
Requires-Dist: google-auth-oauthlib>=1.2; extra == 'all'
Requires-Dist: google-auth>=2.28; extra == 'all'
Requires-Dist: itsdangerous>=2.1; extra == 'all'
Requires-Dist: python-dotenv>=1.0; extra == 'all'
Requires-Dist: sqlalchemy>=2.0; extra == 'all'
Requires-Dist: uvicorn[standard]>=0.29; extra == 'all'
Provides-Extra: alphavantage
Requires-Dist: requests>=2.31; extra == 'alphavantage'
Provides-Extra: analytics
Requires-Dist: google-analytics-data>=0.18; extra == 'analytics'
Requires-Dist: google-auth>=2.28; extra == 'analytics'
Provides-Extra: bigquery
Requires-Dist: sqlalchemy-bigquery>=1.11; extra == 'bigquery'
Requires-Dist: sqlalchemy>=2.0; extra == 'bigquery'
Provides-Extra: databricks
Requires-Dist: databricks-sql-connector[sqlalchemy]>=3.0; extra == 'databricks'
Requires-Dist: sqlalchemy>=2.0; extra == 'databricks'
Provides-Extra: dev
Requires-Dist: httpx>=0.27; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Provides-Extra: duckdb
Requires-Dist: duckdb-engine>=0.13; extra == 'duckdb'
Requires-Dist: duckdb>=1.0; extra == 'duckdb'
Requires-Dist: sqlalchemy>=2.0; extra == 'duckdb'
Provides-Extra: mssql
Requires-Dist: pyodbc>=5.1; extra == 'mssql'
Requires-Dist: sqlalchemy>=2.0; extra == 'mssql'
Provides-Extra: mysql
Requires-Dist: pymysql>=1.1; extra == 'mysql'
Requires-Dist: sqlalchemy>=2.0; extra == 'mysql'
Provides-Extra: nasdaq
Requires-Dist: requests>=2.31; extra == 'nasdaq'
Provides-Extra: oracle
Requires-Dist: oracledb>=2.0; extra == 'oracle'
Requires-Dist: sqlalchemy>=2.0; extra == 'oracle'
Provides-Extra: posit-connect
Requires-Dist: posit-sdk>=0.17; extra == 'posit-connect'
Provides-Extra: postgres
Requires-Dist: psycopg[binary]>=3.1; extra == 'postgres'
Requires-Dist: sqlalchemy>=2.0; extra == 'postgres'
Provides-Extra: redshift
Requires-Dist: redshift-connector>=2.1; extra == 'redshift'
Requires-Dist: sqlalchemy-redshift>=0.8; extra == 'redshift'
Requires-Dist: sqlalchemy>=2.0; extra == 'redshift'
Provides-Extra: salesforce
Requires-Dist: simple-salesforce>=1.12; extra == 'salesforce'
Provides-Extra: servicenow
Requires-Dist: requests>=2.31; extra == 'servicenow'
Provides-Extra: sheets
Requires-Dist: google-api-python-client>=2.120; extra == 'sheets'
Requires-Dist: google-auth-oauthlib>=1.2; extra == 'sheets'
Requires-Dist: google-auth>=2.28; extra == 'sheets'
Provides-Extra: snowflake
Requires-Dist: snowflake-sqlalchemy>=1.6; extra == 'snowflake'
Requires-Dist: sqlalchemy>=2.0; extra == 'snowflake'
Provides-Extra: sql
Requires-Dist: sqlalchemy>=2.0; extra == 'sql'
Provides-Extra: stooq
Requires-Dist: requests>=2.31; extra == 'stooq'
Provides-Extra: teradata
Requires-Dist: sqlalchemy>=2.0; extra == 'teradata'
Requires-Dist: teradatasqlalchemy>=20.0; extra == 'teradata'
Provides-Extra: web
Requires-Dist: fastapi>=0.110; extra == 'web'
Requires-Dist: itsdangerous>=2.1; extra == 'web'
Requires-Dist: python-dotenv>=1.0; extra == 'web'
Requires-Dist: uvicorn[standard]>=0.29; extra == 'web'
Provides-Extra: yahoo
Requires-Dist: requests>=2.31; extra == 'yahoo'
Description-Content-Type: text/markdown

# canvasxpress-connectors

[![CI](https://github.com/neuhausi/canvasxpress-connectors/actions/workflows/ci.yml/badge.svg)](https://github.com/neuhausi/canvasxpress-connectors/actions/workflows/ci.yml)
[![PyPI](https://img.shields.io/pypi/v/canvasxpress-connectors?v=040)](https://pypi.org/project/canvasxpress-connectors/)
[![Python versions](https://img.shields.io/badge/python-3.9%2B-blue)](https://pypi.org/project/canvasxpress-connectors/)
[![License: MIT](https://img.shields.io/pypi/l/canvasxpress-connectors)](LICENSE)

Feed [CanvasXpress](https://www.canvasxpress.org/) from **authenticated** data sources —
databases and Google Sheets — by reshaping query results into CanvasXpress data objects
served from **your own origin**. The browser never holds a credential.

```
Browser (CanvasXpress)  ──►  your app (this package)  ──►  authenticated source
   no secrets                 auth + encrypted creds        DB / Google Sheets
```

![Example CanvasXpress bar chart rendered from data reshaped by canvasxpress-connectors](docs/screenshot.png)

## Install

```bash
pip install canvasxpress-connectors            # core only (needs just cryptography)
pip install "canvasxpress-connectors[sql]"     # + SQLAlchemy databases
pip install "canvasxpress-connectors[sheets]"  # + Google Sheets
pip install "canvasxpress-connectors[analytics]" # + Google Analytics 4 (GA4)
pip install "canvasxpress-connectors[salesforce]" # + Salesforce (SOQL)
pip install "canvasxpress-connectors[servicenow]" # + ServiceNow (Table API)
pip install "canvasxpress-connectors[all]"     # everything incl. the web app
```

## The 3-second version

Any source returns `(header, rows)`; `rows_to_cx` turns that into a CanvasXpress object:

```python
from cx_connectors.sources import SqlSource
from cx_connectors.sources.base import to_cx

data = to_cx(SqlSource(
    "sqlite:///demo.db",
    'SELECT sample, GeneA, GeneB, category AS "Category" FROM expression',
))
# {"y": {"vars": ["GeneA","GeneB"], "smps": [...], "data": [...]}, "x": {"Category": [...]}}
```

Return `data` as JSON from an endpoint; the page does `new CanvasXpress("cx", data, {...})`.

## Architecture

| Layer | Module | Job |
|-------|--------|-----|
| Reshape | `cx_connectors.reshape` | rows → CanvasXpress `{y, x}` (core, no heavy deps) |
| Sources | `cx_connectors.sources` | `DataSource` protocol + `SqlSource`, `GoogleSheetsSource`, `GoogleAnalyticsSource`, `SalesforceSource`, `ServiceNowSource` |
| Live | `cx_connectors.sources` | `LiveSource` protocol (`poll()` → a `pushData` tick) + `SimulatedLiveSource`; streamed over SSE by `cx_connectors.web.sse` |
| Store | `cx_connectors.store` | users (PBKDF2) + per-user **encrypted** connection strings |
| Web | `cx_connectors.web` | `create_byo_app()` (databases + Salesforce/ServiceNow, login, live SSE streams: **`GET /api/streams`** lists them, **`GET /api/stream/<name>`** streams one; register your own with `live_streams=`) · `create_sheets_app()` (Google Sheets, OAuth) — mountable FastAPI apps |

Adding a request/response backend (BigQuery, a REST API, CSV) = one class with a `read()`
returning `(header, rows)`. Adding a **live** backend = one class with `interval` + `poll()`
returning a CanvasXpress tick (new samples); the SSE transport relays it to the browser's
`pushData(tick)`. Nothing else changes.

## Runnable demo — bring-your-own-database, with login

Each user logs in, registers **their own** database (connection string stored
encrypted), and charts **their own** data. Users are isolated by session.

```bash
pip install -e ".[all]"
export ENCRYPTION_KEY=$(python -c "from cx_connectors.store import generate_key;print(generate_key())")
export SESSION_SECRET=$(python -c "import secrets;print(secrets.token_urlsafe(32))")
python examples/seed_demo.py     # users alice & bob, each with their own SQLite DB
python examples/run_byo.py       # http://localhost:8100
```

Log in as `alice`/`alicepw` and `bob`/`bobpw` (incognito) — each sees only their own rows.

## Runnable demo — Google Sheets, per-user OAuth

Each user connects **their own** Google account; the app reads **their** private sheet.
The browser never sees a Google token or URL.

Prereqs: a Google OAuth **Web application** client (Cloud Console → Credentials) with
redirect URI `http://localhost:8080/oauth/callback`, and the **Google Sheets API** enabled.

```bash
pip install -e ".[all]"
export GOOGLE_CLIENT_ID=...  GOOGLE_CLIENT_SECRET=...
export OAUTH_REDIRECT_URI=http://localhost:8080/oauth/callback
export SESSION_SECRET=$(python -c "import secrets;print(secrets.token_urlsafe(32))")
export TOKEN_ENCRYPTION_KEY=$(python -c "from cx_connectors.store import generate_key;print(generate_key())")
export OAUTHLIB_INSECURE_TRANSPORT=1     # localhost http only; remove in production
python examples/run_sheets.py            # http://localhost:8080 → Connect Google Sheets
```

## Use it inside your own FastAPI

```python
from cx_connectors.web import create_byo_app, create_sheets_app
app = create_byo_app(https_only=True)      # database app: login + per-user DBs
# or
app = create_sheets_app(https_only=True)   # Google Sheets app: per-user OAuth
```

Or just the pieces — call `SqlSource` / `GoogleSheetsSource` + `rows_to_cx` from your
own handlers, and bring your own auth.

## Databases beyond SQLite

`SqlSource` is backend-agnostic — it has **no database-specific code**. Any database with a
SQLAlchemy dialect works by changing only the connection URL; add the driver and go. Each
database has a convenience extra (`pip install "canvasxpress-connectors[<name>]"`) that pulls
SQLAlchemy plus the right DBAPI driver:

| Database | Extra | Driver | Connection URL |
|----------|-------|--------|----------------|
| PostgreSQL | `[postgres]` | `psycopg` | `postgresql+psycopg://user:pw@host/db` |
| MySQL | `[mysql]` | `PyMySQL` | `mysql+pymysql://user:pw@host/db` |
| MS SQL Server | `[mssql]` | `pyodbc` | `mssql+pyodbc://user:pw@host/db?driver=ODBC+Driver+18+for+SQL+Server` |
| Oracle | `[oracle]` | `oracledb` | `oracle+oracledb://user:pw@host:1521/?service_name=ORCLPDB1` |
| Teradata | `[teradata]` | `teradatasqlalchemy` | `teradatasql://user:pw@host` |
| Snowflake | `[snowflake]` | `snowflake-sqlalchemy` | `snowflake://user:pw@account/db/schema?warehouse=wh&role=r` |
| Google BigQuery | `[bigquery]` | `sqlalchemy-bigquery` | `bigquery://<project>/<dataset>` (auth out-of-band, see below) |
| Amazon Redshift | `[redshift]` | `redshift-connector` | `redshift+redshift_connector://user:pw@host:5439/db` |
| Azure Synapse | `[mssql]` | `pyodbc` | `mssql+pyodbc://user:pw@host:1433/db?driver=ODBC+Driver+18+for+SQL+Server` |
| Databricks | `[databricks]` | `databricks-sql-connector` | `databricks://token:<PAT>@<host>?http_path=/sql/1.0/warehouses/<id>&catalog=<c>&schema=<s>` |
| DuckDB (Parquet / CSV / Arrow files) | `[duckdb]` | `duckdb-engine` | `duckdb:///:memory:` (query files with `read_parquet('…')`) or `duckdb:///path/to/file.duckdb` |

The read-only `SELECT` guard and `:name` bind-parameter forwarding work identically across all
of them, since they operate on the SQL string, not the backend. A few backend notes:

- **MS SQL Server / Azure Synapse** also need Microsoft's native **ODBC Driver 18** installed on
  the host (the `pyodbc` package is only the Python binding). Synapse is SQL Server-wire-compatible,
  so it reuses the `[mssql]` extra and the `mssql+pyodbc://` URL — point it at the dedicated SQL
  pool endpoint. `pymssql` is an alternative that bundles its own driver:
  `pip install pymssql` → `mssql+pymssql://user:pw@host/db`.
- **Teradata**'s dialect (`teradatasqlalchemy`) is maintained by Teradata; pin/verify it against
  your SQLAlchemy version. A quick connection test before relying on it is worthwhile.
- **Google BigQuery** does not authenticate through the URL — it uses a service-account JSON or
  Application Default Credentials from the environment (e.g. `GOOGLE_APPLICATION_CREDENTIALS=/path/sa.json`),
  so the URL is just `bigquery://<project>` or `bigquery://<project>/<dataset>`. Give the service
  account read-only (`roles/bigquery.dataViewer` + `bigquery.jobUser`) access.
- **Amazon Redshift** is Postgres-wire-compatible: `redshift+redshift_connector://` uses Amazon's
  driver (recommended, supports IAM auth), but `postgresql+psycopg://…:5439/db` also works if you
  prefer the plain Postgres driver.

### DuckDB — Parquet files at interactive speed (no database server)

DuckDB is an in-process engine, so `pip install "canvasxpress-connectors[duckdb]"` is the whole
setup: no server, no credentials. Point a `SELECT` at a Parquet file with `read_parquet()` and
DuckDB pushes the `WHERE` and the column list **into the file scan** — it reads only the row groups
and columns the query needs, so the table is never loaded:

```python
from cx_connectors.sources import SqlSource
from cx_connectors.sources.base import to_cx

data = to_cx(SqlSource(
    "duckdb:///:memory:",
    """SELECT sample, avg(expr) AS mean_expr, avg(logfc) AS mean_logfc
         FROM read_parquet('/srv/data/expression.parquet')
        WHERE gene = :gene AND (:tissue IS NULL OR tissue = :tissue)
        GROUP BY sample ORDER BY sample""",
    {"gene": "TP53", "tissue": None},
))
```

Measured on a 10,000,000-row / 181 MB Parquet file (Mac Studio, DuckDB 1.5): the query above
returns in **18–48 ms**, and a 5,000-row range filter (`WHERE expr BETWEEN :lo AND :hi`) in
**29 ms**. Registered through the web app, the `:gene` / `:tissue` binds become live dashboard
controls exactly like any other parameterized query (see below) — a 10M-row source that filters
interactively without a warehouse. `read_parquet` also takes globs (`'data/*.parquet'`), HTTP(S)
and S3 URLs (`INSTALL httpfs` on first use), and `read_csv_auto(...)` covers CSV. The read-only guard
applies unchanged: it is still a single `SELECT`.

### Databricks

Databricks SQL Warehouses (and clusters) speak SQL through the
[`databricks-sql-connector`](https://pypi.org/project/databricks-sql-connector/) SQLAlchemy
dialect, so `SqlSource` reaches them with **no new code** — only the connection URL changes:

```python
import os
from cx_connectors.sources import SqlSource
from cx_connectors.sources.base import to_cx

CONN_URL = (
    "databricks://token:" + os.environ["DATABRICKS_TOKEN"] + "@"
    + os.environ["DATABRICKS_HOST"]                       # dbc-xxxx.cloud.databricks.com
    + "?http_path=" + os.environ["DATABRICKS_HTTP_PATH"]  # /sql/1.0/warehouses/<id>
    + "&catalog=main&schema=default"
)
data = to_cx(SqlSource(
    CONN_URL,
    "SELECT sample, geneA, geneB, category FROM expression "
    "WHERE (:cohort IS NULL OR cohort = :cohort) ORDER BY sample",
))
```

The read-only `SELECT` guard, `:name` bind-parameter forwarding, and everything downstream
work unchanged. Use a Databricks **personal access token** (or an OAuth M2M token) scoped to a
least-privilege user with read-only access to the warehouse. A runnable end-to-end example is
in [`examples/databricks/`](examples/databricks/).

#### Sign in as the viewer (Posit Connect OAuth)

When the web app is deployed to **Posit Connect**, a Databricks source can sign in **as the
signed-in viewer** instead of storing any credential — each viewer reads Databricks as
themselves, and the source stores no token at all. It opts in with the username
`posit-connect` and **no password**:

```
databricks://posit-connect@dbc-1234.cloud.databricks.com?http_path=/sql/1.0/warehouses/<id>&catalog=main&schema=default
```

Register it exactly like any other SQL source (same URL field, same read-only `SELECT`).
On every request the app exchanges the viewer's `Posit-Connect-User-Session-Token` header
for *that viewer's* Databricks access token (via [`posit-sdk`](https://pypi.org/project/posit-sdk/),
the optional `[posit-connect]` extra) and connects with it — a fresh token per request,
never stored, logged or cached. Normal `databricks://token:<PAT>@...` sources keep working
unchanged.

**Setup.** A Connect admin creates a Databricks **OAuth integration of the *Viewer* type**,
and the content owner attaches it in the deployed content's **Access** panel. If more than
one OAuth integration is attached, set `CXC_CONNECT_OAUTH_AUDIENCE` to the Databricks
integration's GUID so the exchange targets the right one. Install the extra with
`pip install 'canvasxpress-connectors[web,sql,posit-connect]'`.

**Limitation.** A viewer-OAuth source only works for a signed-in viewer visiting the content
on Connect. It has no credential of its own, so it cannot be read outside Connect or on a
**scheduled dataset refresh** (no viewer → a clear 403); use a PAT source for scheduled data.

## Non-SQL sources (REST / SaaS APIs)

Not every source is a database. SaaS APIs (Google Analytics, Salesforce, ServiceNow, …) have
**no SQLAlchemy dialect**, so `SqlSource` can't reach them — but the `DataSource` seam is exactly
for this: a source's only job is to return `(header, rows)`, so each API is one small class and
nothing downstream changes. Google Analytics 4, Salesforce, and ServiceNow ship built-in.

### Google Analytics 4 (GA4)

`GoogleAnalyticsSource` runs a GA4 **Data API** `runReport` (dimensions + metrics over a date
range) and reshapes it: the **first dimension** becomes the sample axis, **metrics** become
numeric variables, and any **further dimensions** become per-sample annotations.

```bash
pip install "canvasxpress-connectors[analytics]"
```

```python
from google.oauth2 import service_account
from cx_connectors.sources import GoogleAnalyticsSource
from cx_connectors.sources.base import to_cx

creds = service_account.Credentials.from_service_account_file(
    "sa.json", scopes=["https://www.googleapis.com/auth/analytics.readonly"])

data = to_cx(GoogleAnalyticsSource(
    credentials=creds,
    property_id="123456789",                 # GA4 property id (digits only)
    dimensions=["date", "sessionDefaultChannelGroup"],
    metrics=["activeUsers", "sessions"],
    start_date="28daysAgo", end_date="today",
))
# y.smps = dates · y.vars = [activeUsers, sessions] · x.sessionDefaultChannelGroup = channel per row
```

Auth is **out-of-band**, like BigQuery: a service-account JSON (share the GA4 property with the
service account's email, Viewer) or user OAuth — the credential never touches the URL or the
browser. GA4 reports are inherently read-only, so there is no SELECT guard to mirror; the caller
picks the dimensions/metrics server-side.

### Salesforce (SOQL)

`SalesforceSource` runs a **SOQL** query and reshapes the records: the first selected field is
the sample axis, numeric fields become variables, text fields become annotations. Column order
follows the `SELECT` list, and relationship fields (`Account.Name`) are read from the nested
record. A read-only guard rejects anything that isn't a `SELECT` (mirroring `SqlSource`).

```bash
pip install "canvasxpress-connectors[salesforce]"
```

```python
from cx_connectors.sources import SalesforceSource
from cx_connectors.sources.base import to_cx

data = to_cx(SalesforceSource(
    "SELECT Name, Amount, StageName, Account.Name FROM Opportunity WHERE IsClosed = true",
    username="integration@acme.com", password="…", security_token="…",
    domain="login",                      # "test" for a sandbox
))
# or session-based auth: SalesforceSource(soql, session_id="…", instance_url="https://…")
```

Use a least-privilege Salesforce **integration user** with read-only object/field permissions.
`query_all` follows the API's paging cursors, so large result sets come back complete.

### ServiceNow (Table API)

`ServiceNowSource` reads a table through the REST **Table API** (`GET /api/now/table/<table>`)
with an encoded `sysparm_query`. It only ever issues `GET`, so it is read-only by construction.
Reference/choice fields (returned as `{display_value, value}`) are flattened to their display
value.

```bash
pip install "canvasxpress-connectors[servicenow]"
```

```python
from cx_connectors.sources import ServiceNowSource
from cx_connectors.sources.base import to_cx

data = to_cx(ServiceNowSource(
    instance="acme",                     # acme.service-now.com
    table="incident",
    query="active=true^priority=1",      # encoded ServiceNow query
    fields=["number", "priority", "category"],   # also fixes column order
    limit=1000,
    username="integration", password="…",        # basic auth; use an integration user
))
```

Give the ServiceNow user a least-privilege read-only role (ACLs still apply per row/field).

**OAuth 2.0** instead of basic auth: mint a short-lived bearer token and pass it as `oauth_token`.
Store the long-lived refresh token / password (encrypted), not the access token — mint one per read.

```python
from cx_connectors.sources.servicenow import servicenow_oauth_token

token = servicenow_oauth_token(
    "acme", client_id="…", client_secret="…",
    username="integration", password="…",   # password grant
    # or: refresh_token="…"                  # refresh_token grant
)
data = to_cx(ServiceNowSource(instance="acme", table="incident",
                              fields=["number", "priority"], oauth_token=token))
```

### Registering SaaS sources through the web app

The BYO web app (`create_byo_app` / the `/connectors` demo) registers these too, not just
databases. The **Type** selector on the register-source form switches between *SQL database*,
*Salesforce (SOQL)*, and *ServiceNow (Table API)*; ServiceNow offers **Basic** or **OAuth 2.0**
auth. Credentials are stored **encrypted** exactly like a connection string — for ServiceNow
OAuth, the refresh/password credentials are stored and a bearer token is minted per request. The
`POST /api/sources` body carries `kind` (`"salesforce"`/`"servicenow"`) plus an `auth` object;
`GET /api/data?source=<name>` then runs the SOQL query / Table API read and reshapes it.

## Parameterized queries (live-data controls)

A source's SQL can declare `:name` bind parameters, and `GET /api/data?source=…&name=…`
forwards matching request params into them — **only** the params the query declares,
always as **bound** parameters (never string-interpolated), so the browser can supply
values but never alter the query shape. An unknown/extra request key is ignored; a
declared param absent from the request is bound as `NULL`, so the `(:name IS NULL OR …)`
idiom lets a control "widen" back to everything.

```python
store.save_source(
    "alice", "sales",
    "sqlite:///file:/srv/data/sales.sqlite?mode=ro&uri=true",
    'SELECT sample, revenue FROM sales '
    'WHERE (:region IS NULL OR region = :region) '
    '  AND (:q IS NULL OR product LIKE :q) '
    'ORDER BY sample',
)
```

Then `/api/data?source=sales&region=EMEA` binds `region="EMEA"`, `q=NULL`. This is what a
[canvasxpress-dashboards](https://github.com/neuhausi/canvasxpress-dashboards) `mode:"param"`
control drives: a source `"query": { "region": "$region", "q": "$q" }` maps the dashboard
parameters onto these bind names. For a `LIKE` search, wrap the value with `%` in SQL —
`'%' || :q || '%'` — rather than in the browser.

To register many sources for a deployment at once (instead of the `/connectors` UI), use
[`examples/_tools/register_sources.py`](examples/_tools/register_sources.py) with a JSON
config (`examples/_tools/sources.example.json`) — the DB files stay on the server; only the
encrypted URL + SQL are stored, and it prints each source's declared bind params.

## Pushdown queries: aggregate, filter and join in the database

A dashboard over a large table should not fetch the table. A request to a SQL source can
carry a declarative **pushdown query** in the `_q` parameter (JSON). The connector
compiles it around the owner's `SELECT`, used as a subquery. The database then filters,
groups, sorts and limits, and only the result travels:

```text
GET /api/data?source=orders&_q={"groupBy":["region"],
    "measures":[{"fn":"sum","column":"amount"},{"fn":"count"}],
    "where":[{"column":"status","op":"in","value":["won","open"]}],
    "orderBy":[{"column":"sum_amount","desc":true}],"limit":100}
```

```sql
SELECT region, sum(amount) AS sum_amount, count(*) AS count
  FROM (<the source's SELECT>) AS cxq
 WHERE status IN (?, ?) GROUP BY region ORDER BY sum_amount DESC LIMIT 101
```

**Parts of a query:**

| Key | Takes |
|---|---|
| `groupBy` | Column names. Each result row's id joins the group values (`EMEA · 2026-01`), and every group column stays a column. |
| `measures` | `{fn, column?, as?}`, where `fn` is `count`, `count_distinct`, `sum`, `avg` (or `mean`), `min` or `max`. The default output name is `fn_column` (`count` for `count(*)`). With no `groupBy`, one row of totals labelled `all`. |
| `where` | `{column, op, value}`, where `op` is `=`, `!=`, `<`, `<=`, `>`, `>=`, `in`, `not_in`, `between` (`[low, high]`), `is_null` or `not_null`. |
| `columns` | Rows mode: return only these columns, filtered, sorted and capped. |
| `orderBy` | Output names, or `{column, desc}`. |
| `limit` | 1 to 1,000,000. |

**The response:**

- **Row cap.** Results are capped at `CX_MAX_ROWS` (default 100,000). The
  `X-Cx-Truncated: 1` header says rows were cut, and `X-Cx-Rows` gives the count.
- **Empty results.** A filter that matches nothing returns an empty data object, not
  an error.

**Safety.** Nothing from the request becomes SQL text:

- Column names must be columns of the source's own query (read from the database) and
  are quoted by the dialect.
- Functions and operators come from fixed lists.
- Every value is a bound parameter.
- The source's own `:name` parameters are still forwarded as bound parameters.

**Columns.** `GET /api/columns?source=…` lists a source's columns, each with a sampled
type (`number` or `text`), for building pushdown queries.

**Joins in the database.** `GET /api/join?left=…&right=…&on=…&how=…` joins two of a
user's SQL sources in their database, as one SQL `JOIN`. This needs both sources on
the same connection (otherwise `400`: join them in the browser).

- **Keys:** `on` is a column on both sides, `{"left": …, "right": …}`, or a list of
  those. `smps` means each side's first column. `how` is `inner`, `left`, `right` or
  `outer`.
- **Output:** the left's columns, then the right's non-key columns. A clashing right
  column gets the suffix `.<right_name>`, which matches the canvasxpress-dashboards
  `kind:"join"`.
- **Aggregating:** a `_q` pushdown query is run over the joined rows.

**Scale.** With DuckDB over Parquet (see above), a grouped, filtered query over a
2,000,000-row file returns in about 0.3 s, and 50 rows travel.

In [canvasxpress-dashboards](https://github.com/neuhausi/canvasxpress-dashboards):

- A connector source's `pushdown` block becomes `_q`.
- Its Filters panel sends selections as `where` clauses and re-queries.
- A `kind:"join"` with `pushdown` uses `/api/join`.

## Packed-matrix sources (CCLE / TCGA expression)

Some reference databases store an expression / copy-number matrix in a **packed** form
for fast single-gene retrieval — one row per gene holding a JSON array of its values
across all samples, plus a shared `json` template table carrying the sample axis +
annotations once. A plain `SELECT` can't reassemble that, so a **`packed`** source does:
it loads the template, fetches the requested genes' arrays, and appends each as a
`vars`/`data` row, yielding a CanvasXpress object for a boxplot / violin / heatmap.

Register one with `kind="packed"` and a config (the gene list comes from a request param
named by `gene_param`, comma-separated), then the source drives a gene-search dashboard
control live:

```python
store.save_source(
    "alice", "ccle-rna-expression",
    "sqlite:///file:/srv/cxd-data/ccle.sqlite?mode=ro&uri=true",
    "",                                   # no SQL for a packed source
    kind="packed",
    config={"table": "rnaseq", "value_col": "log2tpm", "template_key": "rna1",
            "gene_param": "genes"},
)
# GET /api/data?source=ccle-rna-expression&genes=TP53,KRAS
```

Table/column identifiers come from this server-side config (validated as SQL identifiers);
gene values are always bound parameters.

The packed encoding is configurable, so the one source type covers CCLE/TCGA **and** GTEx:
- `value_encoding`: `"json"` (a JSON array, CCLE/TCGA) or `"delimited"` with `value_sep`
  (GTEx stores tpm as a `;`-separated string).
- template location: `template_col` (default `str`) and `template_key` — set
  `template_key` to `null` when the template table holds a single row (GTEx's
  `json.samples`), otherwise it's looked up by `template_key_col` (default `key`).

```python
# GTEx: ;-separated tpm, single-row template in json.samples
config={"table": "expression", "value_col": "tpm", "name_col": "geneName",
        "template_key": None, "template_col": "samples",
        "value_encoding": "delimited", "value_sep": ";"}
```

## Security notes

- Connection strings / tokens are **Fernet-encrypted at rest**; passwords are PBKDF2-hashed.
- `SqlSource` enforces a single read-only `SELECT`; still give the DB user least-privilege read access.
- **Query params are bound, never interpolated**, and only the `:name` binds the SQL declares
  are forwarded — an injected `?foo=…` key never reaches the database.
- **Pushdown queries** (`_q`, `/api/join`) are compiled with SQLAlchemy Core. Column names
  are checked against the source's own columns and quoted by the dialect, functions and
  operators come from fixed lists, and every value is bound. Results are capped at
  `CX_MAX_ROWS`.
- For production: HTTPS + `https_only=True` cookies, rate-limit `/auth/login`, secrets from a
  manager (not `.env`), and pool engines per source.

## Postgres-backed store (multi-process / restart-safe)

By default, users and their registered sources are stored in a local SQLite
file (`app.db` / `tokens.db`).  For hosts that restart or run several
processes — where a local file would be wiped at restart or each process would
have its own copy — point `APP_DB_PATH` at a Postgres database instead:

```bash
export APP_DB_PATH="postgresql://user:pw@host:5432/db?sslmode=require"
export ENCRYPTION_KEY=$(python -c "from cx_connectors.store import generate_key;print(generate_key())")
export SESSION_SECRET=$(python -c "import secrets;print(secrets.token_urlsafe(32))")
pip install "canvasxpress-connectors[postgres]"   # SQLAlchemy + psycopg driver
```

For the Sheets app, set `TOKEN_DB_PATH` the same way.  Both env vars accept
`postgres://`, `postgresql://`, `postgresql+<driver>://` or `sqlite://` in
addition to a bare file path (unchanged default).

The store creates three tables on first start:

| Table | Contents |
|-------|----------|
| `cxc_users` | Usernames + PBKDF2 password hashes |
| `cxc_sources` | Per-user encrypted connection strings and SQL |
| `cxc_tokens` | Per-user encrypted OAuth refresh tokens (Sheets app) |

The `cxc_` prefix lets the store share a database with other apps.

**`ENCRYPTION_KEY` must be set once and never changed.** It is a Fernet key
from `cx_connectors.store.generate_key()`.  Changing it makes every stored
connection string and token unreadable.

There is no automatic migration from an existing SQLite file.

## Deploying the BYO-database demo behind a reverse-proxy subpath (canvasxpress.org)

The demo runs as a plain localhost uvicorn service exposed by Apache under a
path prefix — the same pattern as the
[canvasxpress-mcp](https://github.com/neuhausi/canvasxpress-mcp) server. The
demo UI (`src/cx_connectors/web/static/index.html`) derives its API base from
the path it is served under, so it works at `/` in development and at
`/connectors/` in production. The deployment at
`https://www.canvasxpress.org/connectors/` was set up exactly as follows
(2026-08-20).

### 1 — Install (as the site user)

```bash
ssh <deploy-user>@<your-host> -p <ssh-port>
git clone https://github.com/neuhausi/canvasxpress-connectors.git
cd canvasxpress-connectors
python3 -m venv .venv
.venv/bin/pip install -e '.[all]'
```

### 2 — Persistent secrets + demo seed

Secrets live in `examples/byo_database/.env` (gitignored, `chmod 600`) so the
encrypted connection strings survive restarts:

```bash
cd examples/byo_database
EK=$(../../.venv/bin/python -c 'from cx_connectors.store import generate_key;print(generate_key())')
SS=$(../../.venv/bin/python -c 'import secrets;print(secrets.token_urlsafe(32))')
printf 'ENCRYPTION_KEY=%s\nSESSION_SECRET=%s\nHTTPS_ONLY=1\nMOUNT_PREFIX=/connectors\n' "$EK" "$SS" > .env
chmod 600 .env
../../.venv/bin/python seed_demo.py     # users alice/alicepw & bob/bobpw
```

`HTTPS_ONLY=1` marks the session cookie Secure (the app sits behind Apache
TLS). The cookie is named `cxc_session` so it can't collide with the co-hosted
canvasxpress-dashboards app (`cxd_session`).

`MOUNT_PREFIX` is needed because cPanel's LiteSpeed (which parses the Apache
config) forwards the request path **unstripped** through a
`ProxyPass`-in-`<Location>` — the backend receives `/connectors/...`, not
`/...`. With the prefix set, `examples/byo_database/app.py` mounts the app at
both `/` and `/connectors`, so it works direct and proxied. (On genuine Apache
httpd, which strips the matched prefix, you can omit it.)

### 3 — Run it on port 8300

Port **8300** avoids the MCP server (8100) and dashboards (8200) on the same
host. A `server.sh` in the repo root wraps uvicorn:

```bash
cd ~/canvasxpress-connectors
./server.sh start        # stop | restart | status
curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8300/   # → 200
```

Logs and pidfile land in `examples/byo_database/`. It does **not** auto-start
on reboot; add a crontab entry if you want that:

```
@reboot /home/canvasxpress/canvasxpress-connectors/server.sh start
```

### 4 — Expose it through Apache (as root, one time)

Shared with the dashboards app — one userdata include, staged at
`~canvasxpress/dashboards-connectors-proxy.conf`:

```apache
# Trailing-slash URLs are required: the UI derives its API base from the prefix.
RedirectMatch ^/connectors$ /connectors/

<Location /connectors>
    PassengerEnabled Off
    ProxyPass http://127.0.0.1:8300
    ProxyPassReverse http://127.0.0.1:8300
</Location>
```

```bash
cp ~canvasxpress/dashboards-connectors-proxy.conf \
   /etc/apache2/conf.d/userdata/ssl/2_4/canvasxpress/canvasxpress.org/
/scripts/ensure_vhost_includes --user=canvasxpress
/scripts/restartsrv_httpd
```

The demo is then live at `https://www.canvasxpress.org/connectors/`
(log in as `alice`/`alicepw` or `bob`/`bobpw`).

To update the deployment: `git pull && ./server.sh restart` (re-run
`pip install -e '.[all]'` if dependencies changed).

## Contributing

Development setup, linting/tests, how to add a new data source, and the release process
are in [CONTRIBUTING.md](CONTRIBUTING.md).

## License

MIT

