Skip to main content

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​

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​

DriverSecret Type SlugUse CaseConnection Type
PostgreSQLanalytics-postgresRelational data, complex queriesexternal
MySQLanalytics-mysqlWeb application databasesexternal
Redshiftanalytics-redshiftData warehouse analyticsexternal
Elasticsearchanalytics-elasticsearchLog analysis, full-text searchexternal
ClickHouseanalytics-clickhouseReal-time analytics, time-seriesexternal
Internal (tenant DB)noneTenant's own schema via Django connectioninternal

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:

curl -X GET https://your-site.taruvi.cloud/api/apps/{app_slug}/secrets/types/ \
  -H "Authorization: Bearer YOUR_TOKEN"

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.

curl -X POST https://your-site.taruvi.cloud/api/apps/{app_slug}/secrets/ \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "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):

curl -X POST https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/ \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "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:

curl -X POST https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/ \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Internal Orders",
    "connection_type": "internal",
    "query_text": "SELECT * FROM app_orders WHERE status = '\''{{ status }}'\''"
  }'

Common Setup Errors​

ErrorCauseSolution
Secret not foundSecret key doesn't exist (external)Create the secret first (Step 2)
Secret type not foundMissing analytics-* type (external)Run tenant seeder or contact admin
Unsupported analytics datasourceWrong secret type slug (external)Use analytics-postgres, not postgres
Driver not supportedInvalid secret type (external)Check supported drivers table above
Tenant schema requiredInternal query without tenant contextCall 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"
}
FieldTypeRequiredDescription
hoststringYesDatabase host
portintegerYesDatabase port (5432 for PostgreSQL, 3306 for MySQL, 5439 for Redshift)
databasestringYesDatabase name
usernamestringYesDatabase username
passwordstringYesDatabase password

Elasticsearch​

{
  "host": "https://elasticsearch.example.com",
  "port": 9200,
  "index": "logs-*",
  "username": "elastic",
  "password": "secure_password",
  "max_result_rows": 10000
}
FieldTypeRequiredDescription
hoststringYesElasticsearch host URL
portintegerNoPort (default: 9200)
indexstringYesDefault index pattern
usernamestringNoBasic auth username
passwordstringNoBasic auth password
api_keystringNoAPI key (alternative to username/password)
headersobjectNoCustom HTTP headers
max_result_rowsintegerNoMaximum rows to return (default: 10000)

ClickHouse​

{
  "host": "clickhouse.example.com",
  "port": 8443,
  "database": "analytics",
  "username": "readonly",
  "password": "secure_password",
  "secure": true
}
FieldTypeRequiredDescription
hoststringYesClickHouse host
portintegerYesPort (default: 8443 for HTTPS)
databasestringYesDatabase name
usernamestringYesUsername
passwordstringYesPassword
securebooleanNoUse HTTPS (default: true)

Base URL​

All Analytics endpoints are scoped to an app:

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

Endpoints​

List Queries​

curl -X GET https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/ \
  -H "Authorization: Bearer YOUR_TOKEN"

Response (200 OK):

{
  "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​

curl -X POST https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/ \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "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"
  }'
FieldTypeRequiredDescription
namestringYesHuman-readable query name
descriptionstringNoQuery description
connection_typestringNo (default external)external or internal
secret_keystringRequired for external; must be null/omitted for internalReference to the connection secret
query_textstringYesQuery template (SQL or Elasticsearch DSL/ClickHouse)
tagsarray[string]NoTag slugs for organization

Get Query​

curl -X GET https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/{slug}/ \
  -H "Authorization: Bearer YOUR_TOKEN"

Update Query​

curl -X PUT https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/{slug}/ \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Updated Query Name",
    "query_text": "SELECT * FROM updated_table WHERE id = '\''{{ id }}'\''"
  }'

Delete Query​

curl -X DELETE https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/{slug}/ \
  -H "Authorization: Bearer YOUR_TOKEN"

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

Execute Query​

curl -X POST https://your-site.taruvi.cloud/api/apps/{app_slug}/analytics/queries/{slug}/execute/ \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "params": {
      "date": "2024-01-15",
      "region": "North America"
    }
  }'

Response (200 OK):

{
  "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 CodeDescription
QueryValidationErrorInvalid query syntax or blocked operations
QueryExecutionErrorRuntime execution failure
SecretResolutionErrorSecret not found or invalid type
ConnectorErrorDatabase 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