ktx-ai-data-context-layer

# ktx AI Data Context Layer Skill > Skill by [ara.so](https://ara.so) — MCP Skills collection. ## What is ktx? **ktx** is a self-improving context layer that teaches AI agents how to query your data warehouse accurately. It automatically: - **Learns from company knowledge** - ingests wiki content, organizes it, removes duplicates, flags contradictions - **Maps the data stack** - samples tables, captures metadata, detects joinable columns - **Builds a semantic layer** - combines raw tables and metrics through a join graph that resolves chasm and fan traps - **Serves agents at execution** - exposes CLI and MCP tools with combined full-text and semantic search 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 npm install @kaelio/ktx ``` ### Quick Setup ```bash ktx setup ``` This interactive command: 1. Creates or resumes a local ktx project 2. Configures LLM and embedding providers 3. Sets up database connections 4. Configures context sources (dbt, Looker, etc.) 5. Builds initial context 6. Installs agent integration ## 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 and git-ignored. ## Core Commands ### Check Project Status ```bash 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) ``` ### Build Context ```bash # Ingest all configured sources ktx ingest # Ingest specific connection ktx ingest --connection warehouse # Ingest specific source ktx ingest --source dbt_main ``` ### Search Semantic Layer ```bash # Search for metrics and dimensions ktx sl "revenue" # Search with JSON output ktx sl "customer lifetime value" --json ``` ### Search Wiki ```bash # Search wiki pages ktx wiki "refund policy" # Search with context ktx wiki "how do we calculate churn" ``` ### MCP Server ```bash # Start MCP server for agent clients ktx mcp start # Start with specific project ktx mcp start --project-dir /path/to/project # Check MCP status ktx mcp status ``` ## Configuration ### ktx.yaml Structure ```yaml version: "1" project: name: "analytics" description: "Company analytics warehouse" llm: provider: "anthropic" model: "claude-sonnet-4-6" apiKeyEnvVar: "ANTHROPIC_API_KEY" embeddings: provider: "openai" model: "text-embedding-3-small" apiKeyEnvVar: "OPENAI_API_KEY" connections: warehouse: type: "postgres" host: "localhost" port: 5432 database: "analytics" user: "readonly_user" passwordEnvVar: "DB_PASSWORD" ssl: false sources: dbt_main: type: "dbt" connection: "warehouse" manifestPath: "./target/manifest.json" catalogPath: "./target/catalog.json" ``` ### Environment Variables Create a `.env` file in your project root: ```bash # LLM Provider ANTHROPIC_API_KEY=your_key_here # Embeddings Provider OPENAI_API_KEY=your_key_here # Database Credentials DB_PASSWORD=your_db_password_here # Optional: Project directory override KTX_PROJECT_DIR=/path/to/project ``` ### LLM Provider Configuration #### Anthropic API ```yaml llm: provider: "anthropic" model: "claude-sonnet-4-6" apiKeyEnvVar: "ANTHROPIC_API_KEY" ``` #### Google Vertex AI ```yaml llm: provider: "vertex" model: "claude-sonnet-4-6" projectId: "my-gcp-project" region: "us-central1" credentialsEnvVar: "GOOGLE_APPLICATION_CREDENTIALS" ``` #### Claude Code Session (Local) ```yaml llm: provider: "claude-agent-sdk" ``` ### Database Connection Examples #### PostgreSQL ```yaml connections: warehouse: type: "postgres" host: "db.example.com" port: 5432 database: "analytics" user: "readonly" passwordEnvVar: "POSTGRES_PASSWORD" ssl: true ``` #### Snowflake ```yaml connections: snowflake: type: "snowflake" account: "xy12345.us-east-1" warehouse: "COMPUTE_WH" database: "ANALYTICS" schema: "PUBLIC" user: "ktx_user" passwordEnvVar: "SNOWFLAKE_PASSWORD" ``` #### BigQuery ```yaml connections: bigquery: type: "bigquery" projectId: "my-project" dataset: "analytics" credentialsEnvVar: "GOOGLE_APPLICATION_CREDENTIALS" ``` ### Context Source Configuration #### dbt ```yaml sources: dbt_main: type: "dbt" connection: "warehouse" manifestPath: "./target/manifest.json" catalogPath: "./target/catalog.json" docsPath: "./target/index.html" # optional ``` #### Looker ```yaml sources: looker: type: "looker" connection: "warehouse" projectPath: "./looker-models" ``` #### Metabase ```yaml sources: metabase: type: "metabase" connection: "warehouse" apiUrl: "https://metabase.example.com" apiKeyEnvVar: "METABASE_API_KEY" ``` #### Notion ```yaml sources: notion_wiki: type: "notion" apiKeyEnvVar: "NOTION_API_KEY" databaseIds: - "abc123def456" - "789ghi012jkl" ``` ## Agent Integration ### Claude Code After running `ktx setup`, the integration is automatic. From your project directory: ``` What is our total revenue this quarter? ``` Claude Code will use ktx's semantic layer to query accurately. ### Codex ```bash # Install ktx skill in Codex npx skills add Kaelio/ktx --skill ktx # Use in any project with ktx.yaml ``` ### Cursor / OpenCode Configure MCP in your editor settings: ```json { "mcpServers": { "ktx": { "command": "ktx", "args": ["mcp", "start", "--project-dir", "/path/to/project"] } } } ``` ## Semantic Layer Usage ### Defining Metrics Create YAML files in `semantic-layer/<connection-id>/`: ```yaml # semantic-layer/warehouse/revenue.yaml version: "1" type: "metric" name: "total_revenue" description: "Sum of all order amounts" sql: "SUM(orders.amount)" dimensions: - "customer_id" - "order_date" filters: - "orders.status = 'completed'" source_table: "orders" ``` ### Defining Dimensions ```yaml # semantic-layer/warehouse/customer_dimension.yaml version: "1" type: "dimension" name: "customer_segment" description: "Customer segment based on lifetime value" sql: | CASE WHEN total_spent > 10000 THEN 'enterprise' WHEN total_spent > 1000 THEN 'mid-market' ELSE 'smb' END source_table: "customers" ``` ### Join Graph ktx automatically detects joinable columns. You can override in `ktx.yaml`: ```yaml semantic_layer: joins: - left_table: "orders" right_table: "customers" left_column: "customer_id" right_column: "id" type: "inner" ``` ## Wiki Management ### Adding Wiki Pages ```bash # Add to global wiki mkdir -p wiki/global cat > wiki/global/refund-policy.md <<EOF # Refund Policy Customers can request refunds within 30 days. Full refunds issued if: - Product not as described - Technical issues unresolved Partial refunds (50%) if: - Customer changed mind - Alternative solution offered EOF ``` ### User-Scoped Notes ```bash # Add user-specific notes mkdir -p wiki/user/alice cat > wiki/user/alice/analysis-notes.md <<EOF # Q1 Analysis Notes Revenue spike in March due to new product launch. Check customer_acquisition_source for details. EOF ``` ### Ingesting Wiki Content ```bash # Rebuild wiki index ktx ingest # Search after ingestion ktx wiki "refund timeline" ``` ## Common Patterns ### Initial Project Setup ```typescript // scripts/setup-ktx.ts import { execSync } from 'child_process'; import * as fs from 'fs'; import * as path from 'path'; const projectDir = process.cwd(); // Create ktx.yaml const config = { version: "1", project: { name: path.basename(projectDir), description: "Analytics warehouse" }, llm: { provider: "anthropic", model: "claude-sonnet-4-6", apiKeyEnvVar: "ANTHROPIC_API_KEY" }, embeddings: { provider: "openai", model: "text-embedding-3-small", apiKeyEnvVar: "OPENAI_API_KEY" }, connections: { warehouse: { type: "postgres", host: process.env.DB_HOST || "localhost", port: parseInt(process.env.DB_PORT || "5432"), database: process.env.DB_NAME || "analytics", user: process.env.DB_USER || "readonly", passwordEnvVar: "DB_PASSWORD" } } }; fs.writeFileSync( path.join(projectDir, 'ktx.yaml'), JSON.stringify(config, null, 2) ); // Run setup execSync('ktx setup', { stdio: 'inherit' }); ``` ### Programmatic Ingestion ```typescript // scripts/daily-ingest.ts import { execSync } from 'child_process'; async function runDailyIngest() { console.log('Starting daily ktx ingestion...'); try { // Ingest all sources execSync('ktx ingest', { stdio: 'inherit', env: { ...process.env, KTX_PROJECT_DIR: '/path/to/project' } }); console.log('Ingestion complete'); } catch (error) { console.error('Ingestion failed:', error); process.exit(1); } } runDailyIngest(); ``` ### Custom Metric Definition Workflow ```typescript // scripts/add-metric.ts import * as fs from 'fs'; import * as path from 'path'; import * as yaml from 'yaml'; interface MetricDefinition { version: string; type: 'metric'; name: string; description: string; sql: string; dimensions?: string[]; filters?: string[]; source_table: string; } function addMetric( connectionId: string, metric: Omit<MetricDefinition, 'version' | 'type'> ) { const metricDef: MetricDefinition = { version: "1", type: "metric", ...metric }; const dir = path.join( process.cwd(), 'semantic-layer', connectionId ); fs.mkdirSync(dir, { recursive: true }); const filename = `${metric.name}.yaml`; const filepath = path.join(dir, filename); fs.writeFileSync( filepath, yaml.stringify(metricDef) ); console.log(`Created metric: ${filepath}`); }