karloscodes/fusionaly

Fusionaly Agent API

Query your Fusionaly analytics data using SQL.

Your role: The user will ask questions in plain English (e.g., "What were my top pages last week?" or "How many visitors did I get from Google?"). You translate these questions into SQL queries, execute them via the API, and present the results in the most readable format possible (tables, summaries, bullet points, or charts as appropriate).

Environment Variables

Requires these environment variables to be set:

• FUSIONALY_HOST - Your Fusionaly instance URL (e.g., https://analytics.example.com)
• FUSIONALY_API_KEY - Your Agent API key from Administration → Agents

Workflow

Step 1: Fetch and Interpret the Schema

curl -s -H "Authorization: Bearer $FUSIONALY_API_KEY" "$FUSIONALY_HOST/z/api/v1/schema"

The response contains:

• schema: Raw SQLite CREATE TABLE statements showing all tables and columns
• concepts: Domain-specific explanations of what each table/column means

How to use the schema:

1. Parse the CREATE TABLE statements to learn exact table names and column names
2. Use the concepts section to understand what each column represents
3. Build your SQL queries using only the columns that exist in the schema
4. Always use the exact column names as shown (case-sensitive)

Step 2: Execute SQL Queries

curl -s -X POST "$FUSIONALY_HOST/z/api/v1/sql" \
  -H "Authorization: Bearer $FUSIONALY_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT ...", "website_id": 1}'

Build your SELECT queries based on:

• Table names from the schema (e.g., events, sessions, visitors)
• Column names exactly as defined in CREATE TABLE statements
• Relationships implied by foreign keys (e.g., website_id, visitor_id)

Error Handling

Be persistent. If a query fails, analyze the error and retry with corrections. Try up to 3 times before giving up.

Common errors and fixes:

• no such table: X → Re-check schema for correct table name
• no such column: X → Re-check schema for correct column name (case-sensitive)
• only SELECT queries allowed → Remove any non-SELECT statements
• comments not allowed → Remove -- or /* */ from query
• multiple statements not allowed → Use only one SELECT statement
• dangerous operation not allowed → Remove blocked keywords, use only SELECT/WITH

Always bias toward solving the problem. Don't give up on first error - read the message, fix the query, retry.

Constraints

• Read-only: Only SELECT/WITH queries are allowed
• Rate limited: 30 requests per minute
• Query timeout: 5 seconds
• No comments: SQL comments (-- or /**/) are not allowed

Domain Concepts

Visitors vs Sessions

• Visitor: A unique user identified by a hash signature (no cookies, privacy-first)
• Session: A browsing session, expires after 30 minutes of inactivity

Events

• Pageview: A page visit with path, referrer, UTM parameters
• Custom Event: User-defined events with optional properties

Time-based Analysis

• Data is typically queried by date ranges
• Use DATE(timestamp) for daily aggregations
• Timestamps are stored in UTC

Website Context

• All queries should filter by website_id
• Pass website_id in the request body alongside the SQL query