Query Syntax

alice supports two query modes depending on your data source: SQL queries for Supabase and metric blocks for Stripe. Both can be created through the skill builder or with AI assistance.

Supabase Skills (SQL)

Supabase skills run standard PostgreSQL queries against your connected database. You write the query in the skill builder's query editor, and alice executes it when the slash command is run.

Basic Syntax

Write a standard SELECT statement:

SELECT name, email, created_at
FROM users
WHERE plan = 'pro'
ORDER BY created_at DESC
LIMIT 10

alice supports the full PostgreSQL query syntax for SELECT statements, including:

• JOIN (INNER, LEFT, RIGHT, FULL, CROSS)
• GROUP BY and HAVING
• WITH (CTEs / Common Table Expressions)
• Subqueries
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.)
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
• Date/time functions (now(), interval, date_trunc, etc.)
• JSON functions (jsonb_extract_path_text, ->, ->>, etc.)
• String functions (CONCAT, LOWER, UPPER, SUBSTRING, etc.)
• CASE WHEN expressions
• UNION, INTERSECT, EXCEPT

Parameter Placeholders

Use positional $1, $2, $3 placeholders for dynamic values. Each placeholder maps to a parameter defined in the skill's parameter configuration:

SELECT name, email, plan, created_at
FROM users
WHERE created_at > now() - interval '1 day' * $1
  AND plan = $2
ORDER BY created_at DESC
LIMIT $3

When a user runs /alice users --days=7 --plan=pro --limit=20, the placeholders are replaced with the provided values. Parameters are always passed as parameterized queries (never string-interpolated) to prevent SQL injection.

Common Query Patterns

Recent signups with plan breakdown:

SELECT
  date_trunc('day', created_at) AS day,
  plan,
  COUNT(*) AS signups
FROM users
WHERE created_at > now() - interval '1 day' * $1
GROUP BY day, plan
ORDER BY day DESC

Users approaching trial expiration:

SELECT name, email, trial_ends_at,
  EXTRACT(DAY FROM trial_ends_at - now()) AS days_remaining
FROM users
WHERE trial_ends_at IS NOT NULL
  AND trial_ends_at > now()
  AND trial_ends_at < now() + interval '1 day' * $1
ORDER BY trial_ends_at ASC

Revenue by plan (using CTE):

WITH monthly_revenue AS (
  SELECT
    plan,
    COUNT(*) AS customer_count,
    SUM(monthly_amount) AS total_mrr
  FROM subscriptions
  WHERE status = 'active'
  GROUP BY plan
)
SELECT
  plan,
  customer_count,
  total_mrr,
  ROUND(total_mrr / customer_count, 2) AS avg_per_customer
FROM monthly_revenue
ORDER BY total_mrr DESC

User activity with JSON metadata:

SELECT
  name,
  email,
  metadata->>'company' AS company,
  (metadata->>'login_count')::int AS logins,
  last_seen_at
FROM users
WHERE last_seen_at < now() - interval '1 day' * $1
ORDER BY last_seen_at ASC
LIMIT $2

Read-Only Enforcement

All Supabase queries are enforced as read-only. This is a hard constraint at multiple levels:

1. Query validation -- The query is parsed before execution. Any statement other than SELECT is rejected, including INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, GRANT, and REVOKE.
2. Connection-level enforcement -- The database connection used for skill execution has read_only = true set, preventing writes even if validation were bypassed.
3. RLS enforcement -- Your database's Row Level Security policies are respected. alice queries using the credentials you provided during source setup.

Attempting to run a write query returns an error:

Query rejected: only SELECT statements are allowed.
alice runs all queries in read-only mode.

See Security for full details on query safety.

Query Size Limit

SQL queries have a maximum size of 50 KB. In practice, this is thousands of lines of SQL -- well beyond what any typical skill requires. If you are approaching this limit, consider breaking the query into multiple skills or using views in your database.

Stripe Skills (Metric Blocks)

Stripe skills use pre-built metric blocks rather than raw SQL. You select a block from the Stripe metrics catalog, configure its parameters, and alice handles the Stripe API calls.

How Metric Blocks Work

Each metric block encapsulates one or more Stripe API calls and data transformations:

1. Select a block -- Choose from the metric catalog (e.g., "MRR Breakdown")
2. Configure time period -- Select from presets or set a custom date range
3. Set filters -- Optional filters like plan name, customer status, or payment method
4. alice handles the rest -- API calls, pagination, data aggregation, and formatting

Time Period Presets

Every metric block supports configurable time periods:

The time period is passed as a parameter, so users can override it at runtime:

/alice mrr --period=last-90-days
/alice revenue --start=2026-01-01 --end=2026-01-31

Example: MRR Breakdown Block

The MRR Breakdown metric block returns:

See Stripe Metrics Reference for all 33 available blocks.

Visual Query Builder

For common query patterns, the skill builder offers a visual mode where you can construct queries without writing SQL:

The visual builder supports:

• Table selection -- Pick from your Supabase tables (schema auto-detected during source setup)
• Column selection -- Choose which columns to include in results
• Filters -- Add WHERE conditions using dropdowns for operators
• Sorting -- Set ORDER BY with direction (ASC/DESC)
• Grouping -- Add GROUP BY with aggregate functions
• Limit -- Set a maximum number of rows

The visual builder generates SQL under the hood. You can switch to the SQL editor at any time to see or modify the generated query. Switching from SQL back to visual mode is only possible if the SQL matches a pattern the visual builder supports.

Combined Query Syntax

Combined queries allow a single skill to query multiple sources and join the results. The skill builder shows a separate query panel for each source.

How Combined Queries Execute

1. Each source's query runs in parallel for performance
2. Results are joined in memory based on a shared key (e.g., customer email)
3. The joined result is formatted and delivered to Slack

Example: Churn Risk (Stripe + Supabase)

Stripe query (metric block):

• Block: Customer list with payment failures
• Filter: Payment failed in last 30 days

Supabase query (SQL):

SELECT email, last_seen_at, login_count
FROM users
WHERE last_seen_at < now() - interval '14 days'

Join key: email (Stripe customer email = Supabase user email)

Combined result:

The combined result shows data from both sources in a single Slack message with action buttons for each row.

AI-Generated Queries

The AI skill generation feature can write SQL queries from natural language descriptions. Describe what you want:

"Show me users who signed up in the last 7 days, grouped by plan, with a count per day"

Alice generates the SQL, parameter definitions, and formatting configuration. You can review and edit the generated query before saving.

AI generation counts toward the AI generation rate limit (20/hour) but does not count as a query execution.

Next Steps

• Stripe Metrics Reference -- Complete catalog of metric blocks
• Create a Skill -- Build a skill using these query patterns
• Parameters -- Define dynamic inputs with placeholders
• Combined Queries -- Join data across sources
• AI Skill Generation -- Let AI write your queries
• Security -- Read-only enforcement and RLS