Analytics API

The Analytics API enables you to create and execute queries against external databases and the tenant's own schema (internal). It provides a secure, read-only query execution environment with connector validation.

Overview

The Analytics module allows you to:

• Connect to External Databases: PostgreSQL, MySQL, Redshift, Elasticsearch, ClickHouse
• Query Internal Tenant Data: Use the tenant's own PostgreSQL schema without storing credentials
• Create Reusable Queries: Define parameterized query templates
• Execute Queries Securely: Read-only execution with dangerous operation blocking
• Use Dynamic Parameters: Jinja2 templating for runtime parameter injection
• Access Secrets Securely: Reference stored credentials without exposing them

Architecture

graph TD
    subgraph External
      A[AnalyticsQuery (external)] -->|references| B[Secret]
      B -->|has type| C[SiteSecretType]
      C -->|slug: analytics-driver| D[Driver Resolution]
      D -->|creates| E[External Connector]
      E -->|executes| F[External DB]
    end
    subgraph Internal
      A2[AnalyticsQuery (internal)] -->|driver: internal| E2[Internal Connector]
      E2 -->|executes| F2[Tenant Schema]
    end
    A -->|rendered with| G[Jinja2 Template]
    A2 -->|rendered with| G
    G -->|params + secrets| E
    G -->|params only| E2

Flow

1. Query Creation: Define a query with connection_type (external or internal), query_text, and (for external) secret_key
2. Driver Resolution: External uses secret type slug (e.g., analytics-postgres); internal uses built-in internal driver
3. Template Rendering: Jinja2 renders the query with parameters and secrets (secrets only for external)
4. Secure Execution: Connectors execute in read-only mode; internal forces tenant search_path and blocks cross-schema access
5. Result Return: Data is returned with execution metadata

Supported Drivers

Driver	Secret Type Slug	Use Case	Connection Type
PostgreSQL	analytics-postgres	Relational data, complex queries	external
MySQL	analytics-mysql	Web application databases	external
Redshift	analytics-redshift	Data warehouse analytics	external
Elasticsearch	analytics-elasticsearch	Log analysis, full-text search	external
ClickHouse	analytics-clickhouse	Real-time analytics, time-series	external
Internal (tenant DB)	none	Tenant’s own schema via Django connection	internal

Prerequisites

Before You Start

External analytics queries require secrets to be configured first. Internal analytics queries do not require secrets.

Step 1: Verify Secret Types Exist

Your tenant must have the analytics secret types seeded. These are created automatically when a tenant is provisioned. Verify they exist:

GET /api/apps/{app_slug}/secrets/types/

You should see types like:

• analytics-postgres
• analytics-mysql
• analytics-redshift
• analytics-elasticsearch
• analytics-clickhouse

If missing, contact your platform administrator to run the tenant seeder.

Step 2: Create a Connection Secret (external only)

Create a secret with your database credentials. The secret type determines which database driver will be used.

POST /api/apps/{app_slug}/secrets/
Content-Type: application/json

{
  "key": "my-postgres-readonly",
  "type_slug": "analytics-postgres",
  "value": {
    "host": "db.example.com",
    "port": 5432,
    "database": "analytics_db",
    "username": "readonly_user",
    "password": "secure_password"
  }
}

Step 3: Create Analytics Query

Now you can create a query that references your secret (external):

POST /api/apps/{app_slug}/analytics/queries/
Content-Type: application/json

{
  "name": "Sales Report",
  "connection_type": "external",
  "secret_key": "my-postgres-readonly",
  "query_text": "SELECT * FROM sales WHERE date = '{{ date }}'"
}

For internal (tenant DB) queries, omit secret_key and set connection_type to internal:

POST /api/apps/{app_slug}/analytics/queries/
Content-Type: application/json

{
  "name": "Internal Orders",
  "connection_type": "internal",
  "query_text": "SELECT * FROM app_orders WHERE status = '{{ status }}'"
}

Common Setup Errors

Error	Cause	Solution
Secret not found	Secret key doesn't exist (external)	Create the secret first (Step 2)
Secret type not found	Missing analytics-* type (external)	Run tenant seeder or contact admin
Unsupported analytics datasource	Wrong secret type slug (external)	Use analytics-postgres, not postgres
Driver not supported	Invalid secret type (external)	Check supported drivers table above
Tenant schema required	Internal query without tenant context	Call the API with a valid tenant/site

Secret Configuration

Before creating queries, you must create a secret with the appropriate type. The secret type slug determines which database driver is used.

PostgreSQL / MySQL / Redshift

{
  "host": "db.example.com",
  "port": 5432,
  "database": "analytics_db",
  "username": "readonly_user",
  "password": "secure_password"
}

Field	Type	Required	Description
host	string	Yes	Database host
port	integer	Yes	Database port (5432 for PostgreSQL, 3306 for MySQL, 5439 for Redshift)
database	string	Yes	Database name
username	string	Yes	Database username
password	string	Yes	Database password

Elasticsearch

{
  "host": "https://elasticsearch.example.com",
  "port": 9200,
  "index": "logs-*",
  "username": "elastic",
  "password": "secure_password",
  "max_result_rows": 10000
}

Field	Type	Required	Description
host	string	Yes	Elasticsearch host URL
port	integer	No	Port (default: 9200)
index	string	Yes	Default index pattern
username	string	No	Basic auth username
password	string	No	Basic auth password
api_key	string	No	API key (alternative to username/password)
headers	object	No	Custom HTTP headers
max_result_rows	integer	No	Maximum rows to return (default: 10000)

ClickHouse

{
  "host": "clickhouse.example.com",
  "port": 8443,
  "database": "analytics",
  "username": "readonly",
  "password": "secure_password",
  "secure": true
}

Field	Type	Required	Description
host	string	Yes	ClickHouse host
port	integer	Yes	Port (default: 8443 for HTTPS)
database	string	Yes	Database name
username	string	Yes	Username
password	string	Yes	Password
secure	boolean	No	Use HTTPS (default: true)

Base URL

All Analytics endpoints are scoped to an app:

/api/apps/{app_slug}/analytics/queries/

Endpoints

List Queries

GET /api/apps/{app_slug}/analytics/queries/

Response:

{
  "status": "success",
  "message": "Data retrieved successfully",
  "data": [
    {
      "id": "uuid",
      "name": "Daily Sales Report",
      "slug": "daily-sales-report",
      "description": "Aggregates daily sales by region",
      "secret_key": "sales-db-readonly",
      "query_text": "SELECT region, SUM(amount) FROM sales WHERE date = '{{ date }}' GROUP BY region",
      "created_at": "2024-01-15T10:30:00Z",
      "updated_at": "2024-01-15T10:30:00Z"
    }
  ],
  "total": 1
}

Create Query

POST /api/apps/{app_slug}/analytics/queries/
Content-Type: application/json

{
  "name": "Daily Sales Report",
  "description": "Aggregates daily sales by region",
  "secret_key": "sales-db-readonly",
  "query_text": "SELECT region, SUM(amount) as total FROM sales WHERE date = '{{ date }}' GROUP BY region"
}

Field	Type	Required	Description
name	string	Yes	Human-readable query name
description	string	No	Query description
connection_type	string	No (default external)	external or internal
secret_key	string	Required for external; must be null/omitted for internal	Reference to the connection secret
query_text	string	Yes	Query template (SQL or Elasticsearch DSL/ClickHouse)
tags	array[string]	No	Tag slugs for organization

Get Query

GET /api/apps/{app_slug}/analytics/queries/{slug}/

Update Query

PUT /api/apps/{app_slug}/analytics/queries/{slug}/
Content-Type: application/json

{
  "name": "Updated Query Name",
  "query_text": "SELECT * FROM updated_table WHERE id = '{{ id }}'"
}

Delete Query

DELETE /api/apps/{app_slug}/analytics/queries/{slug}/

Response: Uses standard AppResponse.success envelope (status/message).

Execute Query

POST /api/apps/{app_slug}/analytics/queries/{slug}/execute/
Content-Type: application/json

{
  "params": {
    "date": "2024-01-15",
    "region": "North America"
  }
}

Response: Uses AppDataResponse.success envelope. Example shape:

{
  "status": "success",
  "message": "Query executed successfully",
  "data": [
    {"region": "North America", "total": 150000},
    {"region": "Europe", "total": 120000}
  ],
  "total": 2,
  "execution_key": "exec_abc123"
}

Template Syntax

Query templates use Jinja2 syntax for dynamic parameter injection.

Basic Parameters

Use {{ param_name }} to inject parameters at execution time:

SELECT * FROM users
WHERE status = '{{ status }}'
AND created_at > '{{ start_date }}'

Execute with:

{
  "params": {
    "status": "active",
    "start_date": "2024-01-01"
  }
}

Secret References

Use {{ secret.key }} to reference values from other secrets (external only):

SELECT * FROM users
WHERE api_key = '{{ secret.api_credentials }}'
AND tenant_id = '{{ secret.tenant_config.id }}'

Built-in User Profile

Access the current user's profile with {{ secret.user_profile }}:

SELECT * FROM audit_logs
WHERE user_id = '{{ secret.user_profile.id }}'

Available user profile fields:

• id, username, email
• first_name, last_name
• is_staff, is_superuser

Query Examples

SQL Query (PostgreSQL/MySQL)

SELECT
    DATE(created_at) as date,
    COUNT(*) as orders,
    SUM(total_amount) as revenue
FROM orders
WHERE created_at BETWEEN '{{ start_date }}' AND '{{ end_date }}'
    AND status = '{{ status }}'
GROUP BY DATE(created_at)
ORDER BY date DESC
LIMIT 100

Elasticsearch Query

{
  "_source": ["customer_name", "order_total", "created_at"],
  "query": {
    "bool": {
      "must": [
        {"match": {"status": "{{ status }}"}},
        {"range": {"created_at": {"gte": "{{ start_date }}", "lte": "{{ end_date }}"}}}
      ]
    }
  },
  "sort": [{"created_at": "desc"}],
  "size": 100
}

ClickHouse Query

SELECT
    toDate(timestamp) as date,
    count() as events,
    uniq(user_id) as unique_users
FROM events
WHERE timestamp >= '{{ start_date }}'
    AND event_type = '{{ event_type }}'
GROUP BY date
ORDER BY date DESC

Security

Read-Only Execution

• Internal: Tenant search_path forced to current schema, transaction set READ ONLY.
• SQL (external): Session set to READ ONLY mode.
• ClickHouse (external): readonly=1 setting enforced.
• Elasticsearch (external): Only _search endpoint used.

Blocked Operations

The following are blocked for security:

SQL (PostgreSQL, MySQL, Redshift):

• DDL: CREATE, ALTER, DROP, TRUNCATE
• DML: INSERT, UPDATE, DELETE, MERGE
• Admin: GRANT, REVOKE
• Dangerous functions: pg_sleep, sleep, load_file

ClickHouse:

• External functions: file, s3, url, remote, mysql, postgresql
• System functions: sleep, input

Elasticsearch:

• Script execution: script, runtime_mappings

Secret Protection

• Secrets are resolved server-side and never exposed in responses.
• Query results never include secret values.
• Note: secret placeholders are allowed in templates but validation blocks dangerous use cases (e.g., passing secret.* in params). Avoid placing secrets in SELECT output.

Error Handling

Error Code	Description
QueryValidationError	Invalid query syntax or blocked operations
QueryExecutionError	Runtime execution failure
SecretResolutionError	Secret not found or invalid type
ConnectorError	Database connection failure

Error Responses: Errors are returned via standard AppResponse/DRF validation responses with a status and message. Validation failures include field-level errors; execution errors include a message and sanitized details. The payload will not include secrets.

Best Practices

1. Use Parameterized Queries: Always use {{ param }} instead of string concatenation
2. Limit Result Size: Add LIMIT clauses to prevent large result sets
3. Create Read-Only Database Users: Use dedicated read-only credentials
4. Use Descriptive Names: Name queries clearly for easy identification
5. Document Queries: Use the description field to explain query purpose
6. Test Parameters: Validate parameter values before execution