This is Part 3 of a series. In Part 1 we designed dual-target data models for PostgreSQL and ClickHouse. In Part 2 we built the sync pipeline using Airbyte and dbt. Now it's time to make that data useful to the outside world.
You've done the hard work. Your ClickHouse OLAP layer is loaded with clean, denormalized, blazing-fast data. But data sitting in a warehouse nobody can reach is just expensive storage.
In this article we'll build the final piece: an analytics API that exposes your ClickHouse data to customers or internal teams, with row-level access control, multi-tenant query isolation, and cost attribution per tenant — so you always know who's costing you what.
Here's what we'll cover:
- Building a lightweight query API on top of ClickHouse
- Implementing row-level security with OpenFGA (Google's Zanzibar model)
- Embedding analytics in a dashboard using pre-signed tokens
- Attributing query cost per tenant using ClickHouse system tables
The Architecture
Customer Browser / Internal Dashboard
│
▼
Analytics API (Node.js / FastAPI)
│
┌────┴────┐
│ │
OpenFGA ClickHouse
(AuthZ) (OLAP Layer)
The API sits between your ClickHouse cluster and your customers. It:
- Authenticates the user
- Checks their permissions via OpenFGA (what tenants/data they can see)
- Injects a
WHERE tenant_id = ?row filter into the query - Runs the query against ClickHouse
- Returns results — and logs cost attribution
Step 1: The Query API
// analytics-api/src/routes/query.js
import { createClient } from '@clickhouse/client';
import { checkPermission } from '../auth/openfga.js';
const clickhouse = createClient({
host: process.env.CLICKHOUSE_HOST,
username: process.env.CLICKHOUSE_USER,
password: process.env.CLICKHOUSE_PASSWORD,
});
export async function handleQuery(req, res) {
const { tenantId, metric, startDate, endDate } = req.body;
const userId = req.user.id; // from JWT middleware
// 1. Authorization check via OpenFGA
const allowed = await checkPermission(userId, 'viewer', `tenant:${tenantId}`);
if (!allowed) {
return res.status(403).json({ error: 'Access denied for this tenant' });
}
// 2. Build a safe, parameterized query
const query = `
/* tenant:${tenantId} user:${userId} metric:${metric} */
SELECT
toDate(created_at) AS date,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders_flat
WHERE
tenant_id = {tenantId: String}
AND created_at >= {startDate: DateTime}
AND created_at <= {endDate: DateTime}
GROUP BY date
ORDER BY date
`;
// 3. Execute with bound parameters (prevents SQL injection)
const result = await clickhouse.query({
query,
query_params: { tenantId, startDate, endDate },
format: 'JSONEachRow',
});
const rows = await result.json();
// 4. Log cost attribution (async, non-blocking)
logQueryCost(userId, tenantId, metric).catch(console.error);
return res.json({ data: rows });
}
Key security note: Never interpolate tenantId directly into SQL strings. Always use parameterized queries. ClickHouse's Node client supports {param: Type} syntax natively.
Step 2: Row-Level Access Control with OpenFGA
OpenFGA is an open-source authorization engine based on Google's Zanzibar model. It lets you define fine-grained, relationship-based access policies.
Define your authorization model
{
"type_definitions": [
{
"type": "tenant",
"relations": {
"viewer": { "this": {} },
"admin": { "this": {} }
}
},
{ "type": "user", "relations": {} }
]
}
Check a permission at runtime
// auth/openfga.js
import { OpenFgaClient } from '@openfga/sdk';
const fga = new OpenFgaClient({
apiUrl: process.env.FGA_API_URL,
storeId: process.env.FGA_STORE_ID,
});
export async function checkPermission(userId, relation, object) {
const { allowed } = await fga.check({
tuple_key: {
user: `user:${userId}`,
relation,
object,
},
});
return allowed;
}
Grant access to a tenant
await fga.write({
writes: {
tuple_keys: [{
user: 'user:alice@acme.com',
relation: 'viewer',
object: 'tenant:acme-corp',
}],
},
});
Now Alice can query analytics for acme-corp only — even if she guesses another tenant ID, the API returns 403.
Step 3: Embedding Analytics With Short-Lived Tokens
To embed charts in a customer-facing app without exposing raw ClickHouse credentials, use pre-signed, short-lived tokens scoped to a specific tenant.
import jwt from 'jsonwebtoken';
export function generateEmbedToken(userId, tenantId) {
return jwt.sign(
{ userId, tenantId, scope: 'analytics:read' },
process.env.JWT_SECRET,
{ expiresIn: '1h' }
);
}
Your frontend fetches this token at page load and passes it to the chart component:
function AnalyticsDashboard({ tenantId }) {
const [token, setToken] = useState(null);
useEffect(() => {
fetch('/api/analytics/token', {
method: 'POST',
body: JSON.stringify({ tenantId }),
headers: { 'Content-Type': 'application/json' },
})
.then(r => r.json())
.then(({ token }) => setToken(token));
}, [tenantId]);
if (!token) return <Spinner />;
return <RevenueChart apiUrl="/api/analytics/query" token={token} />;
}
Your ClickHouse host, credentials, and raw schema stay completely hidden from the browser.
Step 4: Cost Attribution Per Tenant
ClickHouse's built-in system.query_log makes it trivial to track query cost per tenant.
Query the system log
SELECT
extract(query, 'tenant:([^ ]+)') AS tenant,
COUNT(*) AS query_count,
SUM(read_bytes) / 1e9 AS gb_scanned,
SUM(memory_usage) / 1e9 AS gb_memory,
AVG(query_duration_ms) AS avg_latency_ms
FROM system.query_log
WHERE
type = 'QueryFinish'
AND event_time >= now() - INTERVAL 7 DAY
GROUP BY tenant
ORDER BY gb_scanned DESC;
Because we injected /* tenant:${tenantId} */ comments in Step 1, we can extract and attribute costs accurately. This powers pricing decisions, capacity planning, and "noisy neighbor" detection.
Gotchas
1. Time zones — ClickHouse stores in UTC. Convert to user timezone:
SELECT toTimeZone(created_at, 'America/New_York') AS local_time, ...
2. Result caching — Cache identical queries (same tenant + date range) in Redis to avoid repeated scans on popular dashboards.
3. Always aggregate in ClickHouse — Return summary rows, never raw millions. ClickHouse is fast at aggregation; your API layer is not.
4. Connection keep-alive — Don't open a new ClickHouse HTTP connection per API request. Use the client's built-in keep-alive.
Full Request Lifecycle
1. User opens dashboard for tenant "acme-corp"
2. Frontend fetches signed embed token → /api/analytics/token
3. Frontend calls /api/analytics/query with token + date params
4. API middleware validates JWT, extracts userId + tenantId
5. API calls OpenFGA: can user:alice view tenant:acme-corp? → YES
6. API builds tagged, parameterized ClickHouse query
7. ClickHouse executes query → returns results in ~20ms
8. API returns JSON to frontend, chart renders
9. Background job reads system.query_log → updates cost dashboard
Final Thoughts
Building a safe, multi-tenant analytics layer isn't just about fast queries. The combination of:
- OpenFGA for relationship-based access control
- Parameterized queries for SQL injection prevention
- Short-lived signed tokens for embed security
- system.query_log attribution for cost transparency
...gives you a production-grade platform that scales to many tenants without losing visibility or control.
This is the pattern that powers real-time cost observability where understanding who is using what at scale is just as important as the query latency itself.
Series:
- Part 1: Designing Data Models for PostgreSQL + ClickHouse
- Part 2: Syncing Postgres to ClickHouse with Airbyte + dbt
- Part 3: Exposing ClickHouse as an Analytics API (this article)
Tags: #clickhouse #postgres #node #analytics #data #openfga #multitenancy #api
Top comments (1)
Cost attribution belongs next to access control, not after it. If every embedded analytics query carries tenant, feature, and user context, you can debug both permission mistakes and runaway spend from the same trail. That makes the API much easier to operate.