ktx-ai-data-context-layer

# ktx AI Data Context Layer > Skill by [ara.so](https://ara.so) — AI Agent Skills collection. **ktx** is a self-improving context layer that teaches AI agents how to query your data warehouse accurately. It ingests metadata from databases, dbt, Looker, Metabase, and wikis to build a semantic layer with approved metric definitions, joinable columns, and business knowledge. Agents access this through MCP (Model Context Protocol) tools and CLI commands. ## What ktx Does - **Learns from company knowledge** — ingests wiki content, removes duplicates, flags contradictions - **Maps the data stack** — samples tables, detects joinable columns, captures metadata and usage patterns - **Builds a semantic layer** — combines raw tables and high-level metrics with automatic join resolution - **Serves agents at execution** — exposes CLI and MCP tools with semantic search across wiki and semantic-layer entities Works with PostgreSQL, Snowflake, BigQuery, ClickHouse, MySQL, SQL Server, and SQLite. Integrates with dbt, MetricFlow, LookML, Looker, Metabase, and Notion. ## Installation ### Global CLI Installation ```bash npm install -g @kaelio/ktx ``` ### Project-Specific Installation ```bash cd /path/to/your/project npm install @kaelio/ktx ``` ### Verify Installation ```bash ktx --version ktx --help ``` ## Quick Setup ```bash # Create or resume a ktx project in current directory ktx setup # Check project readiness ktx status ``` The `ktx setup` command will: 1. Create or resume a local ktx project 2. Configure LLM and embedding providers 3. Set up database connections 4. Configure context sources (dbt, Looker, etc.) 5. Build initial context 6. Install agent integration (MCP) ## Project Structure ``` my-project/ ├── ktx.yaml # Project configuration ├── semantic-layer/<connection-id>/ # YAML semantic sources ├── wiki/global/ # Shared business context ├── wiki/user/<user-id>/ # User-scoped notes ├── raw-sources/<connection-id>/ # Ingest artifacts and reports └── .ktx/ # Local state and secrets (git-ignored) ``` **Important**: Commit `ktx.yaml`, `semantic-layer/`, and `wiki/`. Keep `.ktx/` local. ## Configuration ### ktx.yaml Structure ```yaml version: 1 name: my-analytics-project llm: provider: anthropic model: claude-sonnet-4-6 embeddings: provider: openai model: text-embedding-3-small databases: warehouse: type: postgres host: localhost port: 5432 database: analytics # Credentials stored in .ktx/secrets.yaml context-sources: dbt_main: type: dbt path: ./dbt-project target: prod looker_main: type: looker base_url: https://company.looker.com # API credentials in .ktx/secrets.yaml agent-integration: type: mcp clients: - codex:project - claude-code ``` ### LLM Configuration ktx supports multiple LLM providers: ```bash # During setup, choose provider ktx setup # Supported providers: # - anthropic (requires ANTHROPIC_API_KEY) # - vertex-ai (requires Google Cloud credentials) # - ai-gateway (custom endpoint) # - claude-agent-sdk (local Claude Code session) ``` Set API keys via environment variables: ```bash export ANTHROPIC_API_KEY=your-key-here export OPENAI_API_KEY=your-key-here ``` ### Database Connection Example PostgreSQL connection in `ktx.yaml`: ```yaml databases: warehouse: type: postgres host: localhost port: 5432 database: analytics schema: public ssl: true ``` Credentials in `.ktx/secrets.yaml` (auto-generated, git-ignored): ```yaml databases: warehouse: user: readonly_user password: ${DB_PASSWORD} ``` ### Context Sources #### dbt Integration ```yaml context-sources: dbt_main: type: dbt path: ./dbt-project target: prod manifest_path: ./dbt-project/target/manifest.json ``` #### Looker Integration ```yaml context-sources: looker_main: type: looker base_url: https://company.looker.com # client_id and client_secret in .ktx/secrets.yaml ``` #### Notion Integration ```yaml context-sources: company_wiki: type: notion # token in .ktx/secrets.yaml database_ids: - abc123def456 ``` ## Key Commands ### Setup and Status ```bash # Create/resume project ktx setup # Check project status ktx status # Example output: # ktx project: /home/user/analytics # Project ready: yes # LLM ready: yes (claude-sonnet-4-6) # Embeddings ready: yes (text-embedding-3-small) # Databases configured: yes (warehouse) # Context sources configured: yes (dbt_main) # ktx context built: yes # Agent integration ready: yes (codex:project) ``` ### Building Context ```bash # Ingest all configured sources ktx ingest # Ingest specific connection ktx ingest --connection warehouse # Ingest specific context source ktx ingest --source dbt_main # Force re-ingestion (skip cache) ktx ingest --force ``` ### Searching Context ```bash # Search semantic layer (metrics, dimensions, entities) ktx sl "revenue" ktx sl "monthly active users" # Search wiki pages ktx wiki "refund policy" ktx wiki "data retention" # Combined search (semantic layer + wiki) ktx search "customer churn" ``` ### 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 ``` ### Semantic Layer Management ```bash # List semantic sources ktx sl list # Validate semantic layer definitions ktx sl validate # Show semantic source details ktx sl show users ktx sl show revenue_metrics ``` ### Project Management ```bash # Initialize new project (alternative to setup) ktx init # Update project configuration ktx config set llm.provider anthropic ktx config set llm.model claude-sonnet-4-6 # Show current configuration ktx config show # Clean cached context ktx clean --cache # Clean everything (reset project) ktx clean --all ``` ## Real Usage Examples ### Example 1: Set Up ktx for PostgreSQL Warehouse ```bash # Install globally npm install -g @kaelio/ktx # Navigate to project cd ~/my-analytics-project # Set LLM API key export ANTHROPIC_API_KEY=sk-ant-... # Run setup (interactive) ktx setup # Choose: anthropic, claude-sonnet-4-6 # Choose: openai, text-embedding-3-small # Add database: postgres # Host: localhost # Port: 5432 # Database: analytics # User: readonly_user # Password: [enter securely] # Verify setup ktx status ``` ### Example 2: Ingest dbt and Build Context ```bash # Configure dbt source in ktx.yaml cat >> ktx.yaml <<EOF context-sources: dbt_main: type: dbt path: ./dbt-project target: prod EOF # Run ingestion ktx ingest # Search for dbt models ktx sl "dim_customers" ktx sl "fct_orders" ``` ### Example 3: Query Semantic Layer from TypeScript ```typescript import { execSync } from 'child_process'; // Search semantic layer function searchSemanticLayer(query: string): string { const result = execSync(`ktx sl "${query}"`, { encoding: 'utf-8' }); return result; } // Example usage const revenueMetrics = searchSemanticLayer('revenue'); console.log(revenueMetrics); // Search wiki function searchWiki(query: string): string { const result = execSync(`ktx wiki "${query}"`, { encoding: 'utf-8' }); return result; } const refundPolicy = searchWiki('refund policy'); console.log(refundPolicy); ``` ### Example 4: Set Up MCP for Claude Code ```bash # Navigate to project cd ~/my-analytics-project # Run setup with MCP integration ktx setup # During setup, select: "Install MCP integration? yes" # Select agent: "Claude Code (codex)" # Start MCP server ktx mcp start --project-dir ~/my-analytics-project # Keep terminal open, open Claude Code in another window # Claude Code will auto-discover the MCP server ``` ### Example 5: Create Semantic Layer Definition Create `semantic-layer/warehouse/customers.yaml`: ```yaml kind: SemanticSource name: customers type: entity description: Customer dimension with lifetime metrics columns: - name: customer_id type: dimension data_type: integer primary_key: true - name: email type: dimension data_type: string - name: created_at type: dimension data_type: timestamp - name: lifetime_value type: metric data_type: numeric sql: SUM(order_total) aggregation: sum - name: total_orders type: metric data_type: integer sql: COUNT(DISTINCT order_id) aggregation: count sql: | SELECT c.id as customer_id, c.email, c.created_at, COALESCE(SUM(o.total), 0) as lifetime_value, COUNT(o.id) as total_orders FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.email, c.created_at joins: - to: orders type: one_to_many on: customers.customer_id = orders.customer_id ``` Validate and ingest: ```bash ktx sl validate ktx ingest --force ktx sl show customers ``` ### Example 6: Add Wiki Context Create `wiki/global/data-definitions.md`: ```markdown # Data Definitions ## Revenue Recognition Revenue is recognized at the point of sale, not when payment is received. **Key Metrics:** - `gross_revenue`: Total sales before discounts - `net_revenue`: Sales minus refunds and discounts - `arr`: Annual Recurring Revenue (subscription only) ## Customer Classification - **Active**: Made a purchase in last 90 days - **Churned**: No purchase in 90+ days - **New**: First purchase within last 30 days ## Refund Policy Refunds are processed within 7 days and deducted from revenue in the same period. ``` Ingest wiki: ```bash ktx ingest ktx wiki "revenue recognition" ``` ## Common Patterns ### Pattern 1: Daily Context Refresh ```bash #!/bin/bash # refresh-context.sh cd /path/to/ktx/project ktx ingest --force ktx sl validate ``` Run via cron: ```bash # Run daily at 2am 0 2 * * * /path/to/refresh-context.sh ``` ### Pattern 2: Multi-Environment Setup ```yaml # ktx.yaml databases: warehouse_prod: type: postgres host: prod.example.com database: analytics warehouse_staging: type: postgres host: staging.example.com database: analytics ``` ```bash # Ingest only production ktx ingest --connection warehouse_prod # Ingest only staging ktx ingest --connection warehouse_staging ``` ### Pattern 3: CI/CD Validation ```yaml # .github/workflows/validate-ktx.yml name: Validate ktx Semantic Layer on: [push, pull_request] jobs: validate: runs-on: ubuntu-latest steps: - uses: actions/checkout@v3 - name: Install ktx run: npm install -g @kaelio/ktx - name: Validate semantic layer run: ktx sl validate ``` ### Pattern 4: Environment-Specific Secrets ```bash # .env.production ANTHROPIC_API_KEY=sk-ant-prod-... DB_PASSWORD=prod_password # .env.staging ANTHROPIC_API_KEY=sk-ant-staging-... DB_PASSWORD=staging_password ``` Load before running: ```bash source .env.production ktx ingest ``` ## Troubleshooting ### Issue: `ktx command not found` **Solution**: Ensure global installation or use npx: ```bash npm install -g @kaelio/ktx # or npx @kaelio/ktx setup ``` ### Issue: MCP server not connecting **Solution**: Check server is running and project directory is correct: ```bash ktx mcp status # If not running: ktx mcp start --project-dir /absolute/path/to/project # Verify in Claude Code settings: # MCP servers should show ktx with status "Connected" ``` ### Issue: Database connection fails **Solution**: Verify credentials and network access: ```bash # Test connection manually psql -h localhost -p 5432 -U readonly_user -d analytics # Check ktx configuration ktx config show # Re-run setup to update credentials ktx setup ``` ### Issue: Ingestion fails with "LLM error" **Solution**: Verify API key and quota: ```bash # Check API key is set echo $ANTHROPIC_API_KEY # Verify in ktx config ktx config show # Try with different model ktx config set llm.model claude-sonnet-3-5-20240620 ktx ingest ``` ### Issue: Semantic layer validation errors **Solution**: Check YAML syntax and required fields: ```bash # Run validation with verbose output ktx sl validate --verbose # Common issues: # - Missing 'kind' field # - Invalid column types (must be: dimension, metric, or attribute) # - Invalid join syntax # - Missing SQL or columns definition ```