ktx-ai-data-agents

# ktx AI Data Agents Skill > Skill by [ara.so](https://ara.so) — AI Agent Skills collection. ## Overview **ktx** is an executable context layer that teaches AI agents how to query data warehouses accurately. It automatically builds and maintains: - **Semantic layer** with approved metric definitions, join graphs, and fan/chasm trap resolution - **Wiki knowledge** from Notion, dbt docs, BI tools, and team documentation - **Warehouse metadata** including table schemas, joinable columns, and usage patterns - **MCP integration** for Claude Code, Codex, Cursor, and other AI agents Unlike general-purpose agents that reinvent SQL logic on every query, ktx provides agents with canonical definitions and business context through a searchable interface. ## Installation ```bash # Install globally npm install -g @kaelio/ktx # Or use npx npx @kaelio/ktx setup ``` **Requirements:** - Node.js 18+ - Access to a SQL warehouse (PostgreSQL, Snowflake, BigQuery, ClickHouse, MySQL, SQL Server, or SQLite) - LLM API key (Anthropic, Google Vertex AI) or Claude Pro/Max subscription ## Quick Start ### Initial Setup ```bash # Create or resume a ktx project in current directory ktx setup # Check project status ktx status ``` The `ktx setup` wizard will: 1. Create `ktx.yaml` configuration 2. Configure LLM and embedding providers 3. Set up database connections 4. Configure context sources (dbt, Looker, Metabase, Notion) 5. Build initial context 6. Install agent integration (MCP) ### Project Structure ``` my-project/ ├── ktx.yaml # Project configuration ├── semantic-layer/<connection-id>/ # YAML metric/dimension definitions ├── wiki/global/ # Shared business knowledge ├── wiki/user/<user-id>/ # User-scoped notes ├── raw-sources/<connection-id>/ # Ingest artifacts and reports └── .ktx/ # Local state (git-ignored) ``` **Commit:** `ktx.yaml`, `semantic-layer/`, `wiki/` **Ignore:** `.ktx/` ## Configuration ### ktx.yaml Structure ```yaml version: 1.0 name: my-analytics-project llm: provider: anthropic model: claude-sonnet-4-6 embeddings: provider: openai model: text-embedding-3-small connections: - id: warehouse type: postgres host: ${DATABASE_HOST} port: 5432 database: analytics user: ${DATABASE_USER} password: ${DATABASE_PASSWORD} ssl: true context_sources: - id: dbt_main type: dbt connection_id: warehouse manifest_path: ./target/manifest.json catalog_path: ./target/catalog.json - id: notion_docs type: notion token: ${NOTION_TOKEN} page_ids: - 3fa85f64-5717-4562-b3fc-2c963f66afa6 ``` ### Environment Variables ```bash # LLM providers export ANTHROPIC_API_KEY=your-key-here export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json # Embeddings export OPENAI_API_KEY=your-key-here # Database credentials export DATABASE_HOST=warehouse.example.com export DATABASE_USER=readonly_user export DATABASE_PASSWORD=secure-password # Context sources export NOTION_TOKEN=secret_notion_token export LOOKER_API_TOKEN=looker-token ``` ### Supported Databases **PostgreSQL:** ```yaml connections: - id: postgres_warehouse type: postgres host: ${PG_HOST} port: 5432 database: analytics user: ${PG_USER} password: ${PG_PASSWORD} ssl: true ``` **Snowflake:** ```yaml connections: - id: snowflake_warehouse type: snowflake account: ${SNOWFLAKE_ACCOUNT} user: ${SNOWFLAKE_USER} password: ${SNOWFLAKE_PASSWORD} warehouse: COMPUTE_WH database: ANALYTICS schema: PUBLIC ``` **BigQuery:** ```yaml connections: - id: bigquery_warehouse type: bigquery project_id: ${GCP_PROJECT_ID} dataset: analytics credentials_path: ${GOOGLE_APPLICATION_CREDENTIALS} ``` ## Core Commands ### Context Management ```bash # Build context from all configured sources ktx ingest # Build context for specific connection ktx ingest --connection warehouse # Force rebuild ignoring cache ktx ingest --force # Dry run to preview changes ktx ingest --dry-run ``` ### Search and Query ```bash # Search semantic layer (metrics, dimensions) ktx sl "revenue" ktx sl "customer churn rate" # Search wiki knowledge ktx wiki "refund policy" ktx wiki "data retention rules" # Get detailed entity information ktx describe metric monthly_recurring_revenue ktx describe dimension customer_segment ``` ### MCP Server ```bash # Start MCP server for agent clients ktx mcp start # Start with specific project directory ktx mcp start --project-dir /path/to/project # Check MCP server status ktx mcp status # Stop MCP server ktx mcp stop ``` ### Project Management ```bash # Validate configuration ktx validate # Show project status ktx status # List all configured connections ktx connections list # Test connection ktx connections test warehouse ``` ## Semantic Layer Usage ### Defining Metrics Create `semantic-layer/warehouse/metrics.yaml`: ```yaml metrics: - name: monthly_recurring_revenue label: Monthly Recurring Revenue description: Sum of all active subscription values normalized to monthly type: sum sql: | CASE WHEN billing_period = 'monthly' THEN amount WHEN billing_period = 'annual' THEN amount / 12 END table: subscriptions filters: - column: status operator: equals value: 'active' dimensions: - customer_segment - plan_type timestamp_column: created_at - name: customer_count label: Active Customers description: Count of distinct active customer IDs type: count_distinct sql: customer_id table: subscriptions filters: - column: status operator: equals value: 'active' ``` ### Defining Dimensions Create `semantic-layer/warehouse/dimensions.yaml`: ```yaml dimensions: - name: customer_segment label: Customer Segment description: Business vs. enterprise customer classification type: categorical sql: | CASE WHEN annual_revenue > 100000 THEN 'Enterprise' WHEN annual_revenue > 10000 THEN 'Business' ELSE 'Startup' END table: customers - name: signup_date label: Signup Date description: Date customer first signed up type: time sql: DATE(created_at) table: customers granularities: - day - week - month - quarter - year ``` ### Join Configuration Define table relationships in `semantic-layer/warehouse/joins.yaml`: ```yaml joins: - left_table: subscriptions right_table: customers type: left conditions: - left_column: customer_id right_column: id - left_table: subscriptions right_table: plans type: left conditions: - left_column: plan_id right_column: id ``` ## Wiki Management ### Creating Wiki Pages ```bash # Create global wiki page cat > wiki/global/refund-policy.md << 'EOF' # Refund Policy ## Overview Customers can request refunds within 30 days of purchase. ## Rules - Full refund: < 7 days - Prorated refund: 7-30 days - No refund: > 30 days ## Database Impact Refunds update `transactions.status` to 'refunded' and create negative entries in `revenue_events`. EOF # Create user-scoped note mkdir -p wiki/user/$(whoami) cat > wiki/user/$(whoami)/analysis-notes.md << 'EOF' # Analysis Notes ## 2025-05 Revenue Analysis Found discrepancy in EMEA revenue - missing Stripe events. Tracked in JIRA-1234. EOF ``` ### Ingesting from Notion ```yaml context_sources: - id: product_docs type: notion token: ${NOTION_TOKEN} page_ids: - 3fa85f64-5717-4562-b3fc-2c963f66afa6 # Product Roadmap - 7c9e6679-7425-40de-944b-e07fc1f90ae7 # Data Dictionary recursive: true # Include child pages ``` Run `ktx ingest` to sync Notion content into `wiki/global/`. ## Agent Integration (MCP) ### Starting MCP Server ```bash # In your ktx project directory ktx mcp start # Or specify project location ktx mcp start --project-dir ~/my-analytics ``` ### Claude Desktop Configuration Add to `~/Library/Application Support/Claude/claude_desktop_config.json`: ```json { "mcpServers": { "ktx": { "command": "ktx", "args": ["mcp", "start", "--project-dir", "/absolute/path/to/project"] } } } ``` Restart Claude Desktop to load the MCP server. ### Using ktx from Claude Once configured, you can prompt Claude: ``` What was our MRR last month by customer segment? ``` Claude will use ktx MCP tools to: 1. Search semantic layer for `monthly_recurring_revenue` metric 2. Find relevant dimensions (`customer_segment`) 3. Retrieve approved SQL definitions 4. Execute query using canonical metric logic ### Available MCP Tools - `ktx_search_semantic_layer` - Search metrics, dimensions, tables - `ktx_search_wiki` - Search business knowledge and documentation - `ktx_describe_entity` - Get detailed entity information - `ktx_list_connections` - List available database connections - `ktx_get_context_summary` - Get project context overview ## Common Patterns ### Initial Project Setup Workflow ```bash # 1. Navigate to analytics project cd ~/projects/analytics # 2. Run setup wizard ktx setup # Select: Anthropic Claude, OpenAI embeddings, configure Postgres connection # 3. Add dbt context source # Edit ktx.yaml to add dbt manifest/catalog paths # 4. Build context ktx ingest # 5. Verify ktx status ktx sl "revenue" # 6. Start MCP for agents ktx mcp start ``` ### Incremental Context Updates ```bash # After dbt run or schema changes dbt run dbt docs generate ktx ingest --connection warehouse # After updating wiki pages ktx ingest --source notion_docs # Check for conflicts or issues ktx validate ``` ### Searching Before Agent Queries ```bash # Find available metrics ktx sl "churn" # Output: # Metrics: # - customer_churn_rate (Monthly customer churn percentage) # - mrr_churn (Monthly recurring revenue lost to churn) # # Dimensions: # - churn_reason (Categorical reason for cancellation) # Get metric details ktx describe metric customer_churn_rate # Now prompt agent with context: # "Calculate customer_churn_rate for Q1 2025 by churn_reason" ``` ### Multi-Warehouse Setup ```yaml connections: - id: production type: snowflake account: ${SNOWFLAKE_PROD_ACCOUNT} # ... prod credentials - id: staging type: snowflake account: ${SNOWFLAKE_STAGING_ACCOUNT} # ... staging credentials context_sources: - id: dbt_prod type: dbt connection_id: production manifest_path: ./prod/target/manifest.json - id: dbt_staging type: dbt connection_id: staging manifest_path: ./staging/target/manifest.json ``` ```bash # Ingest specific warehouse ktx ingest --connection production # Search scoped to connection ktx sl "revenue" --connection production ``` ## Troubleshooting ### MCP Server Not Starting **Check project directory:** ```bash ktx status # If shows "ktx mcp start --project-dir ...", copy and run that command ``` **Verify ktx.yaml exists:** ```bash ls ktx.yaml # If missing, run: ktx setup ``` **Check Claude Desktop config:** ```bash cat ~/Library/Application\ Support/Claude/claude_desktop_config.json # Ensure path is absolute, not relative ``` ### Database Connection Issues ```bash # Test connection directly ktx connections test warehouse # Common fixes: # - Verify environment variables are set # - Check firewall/VPN for warehouse access # - Ensure user has SELECT permissions # - For Snowflake, verify warehouse is running ``` ### Context Ingestion Failures ```bash # Enable verbose logging ktx ingest --verbose # Check specific source ktx ingest --source dbt_main --verbose # Validate configuration ktx validate # Common issues: # - dbt manifest/catalog paths incorrect # - Missing environment variables # - LLM API rate limits (retry with backoff) ``` ### Semantic Layer Errors **Undefined metric:** ```bash # List all metrics ktx sl "*" --type metric # Ensure YAML is valid cat semantic-layer/warehouse/metrics.yaml ktx validate ``` **Join graph errors:** ```bash # Check join definitions cat semantic-layer/warehouse/joins.yaml # Common issues: # - Missing join between tables # - Ambiguous join paths (fan trap) # - Incorrect column names ``` ### Agent Not Finding Context **Restart MCP server:** ```bash ktx mcp stop ktx mcp start ``` **Rebuild context:** ```bash ktx ingest --force ``` **Check search results:** ```bash ktx sl "your search term" ktx wiki "your search term" # If empty, context may not have been ingested ``` ## TypeScript API Usage For programmatic usage in Node.js: ```typescript import { KtxProject } from '@kaelio/ktx'; // Load project const project = await KtxProject.load('/path/to/project'); // Search semantic layer const metrics = await project.searchSemanticLayer('revenue', { type: 'metric', limit: 10 });