Scheduled KPIs

Scheduled KPIs let you define SQL queries that run on a recurring schedule and materialize results into real, typed PostgreSQL tables — creating a reliable gold layer for dashboards, predictions, and natural language queries.

How It Works

1. Define a KPI — write any SQL query (or import from a saved query)
2. Set a schedule — collection interval from 1 to 24 hours
3. Actyze materializes the results — creates a typed table in the kpi_data schema
4. Data is AI-discoverable — the table registers with FAISS so you can query it in natural language

Each collection appends new rows with a collected_at timestamp, building a time-series automatically.

Creating a KPI

From the UI

1. Navigate to Scheduled KPIs in the sidebar
2. Click Create KPI
3. Enter a name and description
4. Write your SQL query or click Import from Saved Query
5. Set the collection interval (1–24 hours)
6. Click Save

Example KPI

-- Daily revenue by product category
SELECT
  category,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value
FROM orders
JOIN products ON orders.product_id = products.id
WHERE orders.created_at >= CURRENT_DATE - INTERVAL '1' DAY
GROUP BY category

With a 24-hour interval, this query runs daily and appends results with a collected_at timestamp. After a week you have 7 snapshots showing daily trends.

Materialized Tables

When a KPI is first collected, Actyze:

1. Creates a table in the kpi_data schema (e.g., kpi_data.kpi_daily_revenue)
2. Maps column types — SQL types from Trino are mapped to proper PostgreSQL types (not JSONB blobs)
3. Adds metadata columns — collected_at timestamp is appended automatically
4. Registers with FAISS — the table becomes discoverable via natural language queries

Querying KPI Data

Once materialized, KPI tables are queryable like any other table:

Natural language:

"Show me daily revenue trends for the past month"
→ AI discovers kpi_data.kpi_daily_revenue and generates the right SQL

Direct SQL:

SELECT collected_at, category, total_revenue
FROM kpi_data.kpi_daily_revenue
WHERE collected_at >= CURRENT_DATE - INTERVAL '30' DAY
ORDER BY collected_at, category

Collection Scheduling

How Scheduling Works

Actyze uses APScheduler with a shared PostgreSQL job store. This means:

• Multi-pod safe — schedules are coordinated across Nexus replicas via the database
• Persistent — schedules survive pod restarts
• No external scheduler needed — no cron jobs, Airflow, or Celery required

Manual Collection

Click Collect Now on any KPI to trigger an immediate collection outside the schedule.

Status Tracking

Each KPI shows:

• Last collected at — timestamp of the most recent successful collection
• Last error — error message if the last collection failed
• Status indicator — green (healthy) or red (error)

Linking KPIs to Predictions

Scheduled KPIs are the primary data source for Predictive Intelligence. When creating a prediction pipeline:

1. Select KPI as the data source
2. Choose the KPI table
3. Set the training trigger to After KPI Collection

Now, every time the KPI collects fresh data, the prediction pipeline retrains automatically. This creates a fully automated loop:

Schedule collects data → KPI table updated → ML model retrains → Fresh predictions available

Configuration

Environment Variables

Variable	Default	Description
KPI_MIN_INTERVAL_HOURS	1	Minimum allowed collection interval
KPI_MAX_INTERVAL_HOURS	24	Maximum allowed collection interval
KPI_SCHEDULER_ENABLED	true	Enable/disable the KPI scheduler

Permissions

Role	Can create	Can collect	Can view
ADMIN	Yes	Yes	Yes
USER	Yes	Yes	Yes
READONLY	No	No	Yes

Best Practices

Query Design

• Use date filters — avoid collecting the entire history every time; filter to the relevant window
• Pre-aggregate — KPI queries should return summary-level data, not raw rows
• Name clearly — use descriptive KPI names like "Daily Revenue by Category" so the AI can match them to natural language questions

Scheduling

• Match your data cadence — if source data updates hourly, collect hourly; if daily, collect daily
• Stagger intervals — avoid scheduling all KPIs at the same interval to spread database load
• Monitor errors — check the status indicator regularly; a failed collection means stale data

Integration with Predictions

• Use KPI sources over custom SQL when possible — KPI data is clean, scheduled, and reliable
• Set prediction triggers to "After KPI Collection" for fully automated ML retraining