ktx-context-layer-data-agents

name	ktx-context-layer-data-agents
description	Teach AI agents how to query data warehouses accurately using ktx - an executable context layer with skills, memory, and a semantic layer
triggers	["setup ktx for data warehouse queries","configure ktx context layer","build ktx semantic layer from database","integrate ktx with claude code","query warehouse using ktx","ingest dbt metrics into ktx","search ktx wiki or semantic layer","troubleshoot ktx mcp server"]

ktx Context Layer for Data Agents

Skill by ara.so — AI Agent Skills collection.

ktx is an executable context layer that teaches AI agents how to query data warehouses accurately. It automatically builds a semantic layer from your database, ingests business knowledge from wikis and tools like dbt/Looker, detects joinable columns, resolves fan/chasm traps, and exposes everything through CLI and MCP tools for agent execution.

Installation

Install ktx globally via npm:

npm install -g @kaelio/ktx

Or add to a project:

npm install --save-dev @kaelio/ktx

Quick Setup

Run the interactive setup wizard:

ktx setup

This will:

• Create or resume a ktx project in the current directory
• Configure LLM provider (Anthropic API, Google Vertex AI, or Claude Agent SDK)
• Configure embedding provider (OpenAI, Anthropic, Vertex AI)
• Set up database connections (PostgreSQL, Snowflake, BigQuery, etc.)
• Configure context sources (dbt, LookML, Looker, Metabase, Notion)
• Build initial context
• Install agent integration (Codex, Claude Code, etc.)

Check project status:

ktx status

Expected output after successful setup:

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)

Project Structure

ktx creates this 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)

Commit ktx.yaml, semantic-layer/, and wiki/. Keep .ktx/ local.

Configuration

ktx.yaml

Example project configuration:

version: 1
project_id: analytics_project
llm_provider: anthropic
embedding_provider: openai

connections:
  - id: warehouse
    type: postgres
    config:
      host: localhost
      port: 5432
      database: analytics
      user: readonly_user
      password_env: POSTGRES_PASSWORD
      ssl: false

context_sources:
  - id: dbt_main
    type: dbt
    config:
      manifest_path: ./dbt/target/manifest.json
      catalog_path: ./dbt/target/catalog.json
  
  - id: looker_metrics
    type: looker
    config:
      base_url_env: LOOKER_BASE_URL
      client_id_env: LOOKER_CLIENT_ID
      client_secret_env: LOOKER_CLIENT_SECRET

agent_integrations:
  - type: codex
    scope: project

Environment Variables

Store secrets in environment variables:

# LLM Provider
export ANTHROPIC_API_KEY=sk-ant-...
# or for Vertex AI
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/credentials.json

# Embedding Provider
export OPENAI_API_KEY=sk-...

# Database Credentials
export POSTGRES_PASSWORD=yourpassword
export SNOWFLAKE_PASSWORD=yourpassword

# Tool Integrations
export LOOKER_BASE_URL=https://company.looker.com
export LOOKER_CLIENT_ID=your_client_id
export LOOKER_CLIENT_SECRET=your_secret
export NOTION_TOKEN=secret_...

Core Commands

Build Context

Ingest from all configured connections and sources:

ktx ingest

Ingest from specific connection:

ktx ingest --connection warehouse

Ingest from specific context source:

ktx ingest --context-source dbt_main

Search Semantic Layer

Search for metrics, dimensions, and entities:

ktx sl "revenue"
ktx sl "customer lifetime value"
ktx sl "monthly active users"

Example output:

Found 3 semantic sources matching "revenue":

1. metric.monthly_recurring_revenue
   Type: metric
   Connection: warehouse
   Description: Sum of all active subscription values in a given month
   SQL: SUM(subscriptions.monthly_value)
   
2. dimension.revenue_tier
   Type: dimension
   Entity: customer
   Description: Customer revenue bracket (low/medium/high)
   
3. entity.revenue_events
   Type: entity
   Table: prod.revenue_events
   Primary key: event_id

Search Wiki

Search business knowledge and documentation:

ktx wiki "refund policy"
ktx wiki "customer segmentation"

Query Warehouse

Execute SQL queries through ktx:

ktx query "SELECT customer_tier, COUNT(*) FROM customers GROUP BY customer_tier"

Query with metric resolution:

ktx query --use-metrics "SELECT monthly_recurring_revenue FROM time WHERE month = '2024-01'"

MCP Server

Start the Model Context Protocol server for agent integration:

ktx mcp start

Specify project directory:

ktx mcp start --project-dir /path/to/project

The MCP server exposes these tools to agents:

• ktx_search_semantic_layer - Search metrics and dimensions
• ktx_search_wiki - Search business knowledge
• ktx_query - Execute SQL queries
• ktx_get_schema - Retrieve table schemas
• ktx_get_metric_definition - Get canonical metric SQL

Working with Semantic Sources

ktx automatically generates semantic sources during ingestion. You can also define them manually.

Metric Definition

# semantic-layer/warehouse/metrics/mrr.yaml
name: monthly_recurring_revenue
type: metric
description: Sum of all active subscription values in a given month
entity: subscription
sql: SUM(subscriptions.monthly_value)
filters:
  - sql: subscriptions.status = 'active'
dimensions:
  - customer_tier
  - plan_type
time_dimension: subscription_start_date

Entity Definition

# semantic-layer/warehouse/entities/customer.yaml
name: customer
type: entity
table: prod.customers
primary_key: customer_id
description: Customer master table
dimensions:
  - name: customer_tier
    type: categorical
    sql: tier
  - name: signup_date
    type: time
    sql: created_at

Join Configuration

ktx auto-detects joins, but you can override:

# semantic-layer/warehouse/joins/customer_subscription.yaml
from_entity: customer
to_entity: subscription
type: one_to_many
join_sql: customers.customer_id = subscriptions.customer_id

Common Patterns

Pattern 1: Setup New Project

# Navigate to analytics directory
cd ~/analytics

# Initialize ktx
ktx setup

# Follow prompts to configure:
# - LLM provider (Anthropic API recommended)
# - Embedding provider (OpenAI recommended)
# - Database connection (read-only credentials)
# - dbt integration (point to manifest.json)

# Verify setup
ktx status

# Build initial context
ktx ingest

Pattern 2: Query with Agent Context

From Claude Code, Cursor, Codex, or OpenCode:

User: What was our MRR in January 2024?

Agent uses ktx:
1. ktx sl "monthly recurring revenue" → finds metric definition
2. ktx query --use-metrics "SELECT monthly_recurring_revenue FROM time WHERE month = '2024-01'"
3. Returns accurate result using canonical metric logic

Pattern 3: Add Business Context

Create wiki pages for business knowledge:

# Create global wiki page
mkdir -p wiki/global
cat > wiki/global/refund-policy.md << 'EOF'
# Refund Policy

Customers can request refunds within 30 days of purchase.

## Refund Eligibility
- Full refund if < 7 days
- Prorated refund if 7-30 days
- No refund if > 30 days

## Accounting Treatment
Refunds are recorded as negative revenue in the month issued, not the original purchase month.
EOF

# Ingest wiki content
ktx ingest

Pattern 4: Integrate dbt Metrics

# Ensure dbt artifacts exist
cd dbt-project
dbt compile
dbt docs generate

# Configure in ktx.yaml
cat >> ktx.yaml << 'EOF'
context_sources:
  - id: dbt_main
    type: dbt
    config:
      manifest_path: ./dbt-project/target/manifest.json
      catalog_path: ./dbt-project/target/catalog.json
EOF

# Ingest dbt metrics
ktx ingest --context-source dbt_main

# Search for dbt metrics
ktx sl "customers"

Pattern 5: Agent Integration

For Codex:

# Install during setup or manually
ktx setup
# Select "codex" when prompted for agent integration

# Or install explicitly
npx skills add Kaelio/ktx --skill ktx

For Claude Code:

# ktx setup handles this automatically
# Adds MCP server config to Claude Code settings

Manual MCP configuration for Claude Desktop:

{
  "mcpServers": {
    "ktx": {
      "command": "ktx",
      "args": ["mcp", "start", "--project-dir", "/path/to/project"]
    }
  }
}

TypeScript API (Programmatic Usage)

While ktx is primarily a CLI tool, you can use it programmatically:

import { KtxProject } from '@kaelio/ktx';

// Load existing project
const project = await KtxProject.load('/path/to/project');

// Search semantic layer
const results = await project.searchSemanticLayer('revenue');
console.log(results);

// Search wiki
const wikiResults = await project.searchWiki('refund policy');
console.log(wikiResults);

// Execute query
const queryResult = await project.query(
  'warehouse',
  'SELECT * FROM customers LIMIT 10'
);
console.log(queryResult.rows);

// Get metric definition
const metric = await project.getMetric('monthly_recurring_revenue');
console.log(metric.sql);

Troubleshooting

MCP Server Not Starting

Symptom: Agent can't connect to ktx

Solution:

# Check status
ktx status

# Start MCP server manually
ktx mcp start --project-dir /path/to/project

# Check logs
tail -f ~/.ktx/logs/mcp.log

Ingestion Failures

Symptom: ktx ingest fails with connection errors

Solution:

# Test database connection
ktx test-connection warehouse

# Verify environment variables
env | grep -E 'POSTGRES|SNOWFLAKE|ANTHROPIC|OPENAI'

# Check ktx.yaml syntax
ktx validate

# Ingest with verbose logging
ktx ingest --verbose

Missing Metrics

Symptom: ktx sl "metric_name" returns no results

Solution:

# Re-ingest context
ktx ingest --force

# Check semantic layer directory
ls -la semantic-layer/warehouse/metrics/

# Manually create metric if needed
mkdir -p semantic-layer/warehouse/metrics
cat > semantic-layer/warehouse/metrics/my_metric.yaml << 'EOF'
name: my_metric
type: metric
description: My custom metric
entity: my_entity
sql: COUNT(*)
EOF

# Rebuild context
ktx ingest

LLM Provider Issues

Symptom: Context building fails with API errors

Solution:

# Verify API key
echo $ANTHROPIC_API_KEY

# Test LLM connection
ktx test-llm

# Switch provider if needed
ktx setup
# Select different LLM provider

# Check rate limits and quota
# Anthropic: https://console.anthropic.com
# OpenAI: https://platform.openai.com/usage

Permission Errors

Symptom: Can't write to project directory

Solution:

# Check directory permissions
ls -la .

# Fix ownership
sudo chown -R $USER:$USER .

# Or specify writable project directory
ktx setup --project-dir ~/my-ktx-project

Semantic Layer Contradictions

Symptom: ktx flags conflicting metric definitions

Solution:

# Review contradictions report
cat raw-sources/warehouse/contradictions.json

# Resolve by editing semantic sources
vim semantic-layer/warehouse/metrics/revenue.yaml

# Or update source (dbt, Looker, etc.)
# Then re-ingest
ktx ingest --force

Advanced Configuration

Custom Sampling Strategy

Control how ktx samples tables during ingestion:

# ktx.yaml
connections:
  - id: warehouse
    type: postgres
    config:
      host: localhost
      database: analytics
      sample_strategy: adaptive
      max_sample_rows: 10000
      min_sample_rows: 100

Join Detection Tuning

Adjust automatic join detection:

# ktx.yaml
semantic_layer:
  join_detection:
    min_confidence: 0.8
    sample_size: 1000
    detect_fan_traps: true
    detect_chasm_traps: true

Wiki Organization

Structure wiki for better retrieval:

wiki/
├── global/
│   ├── metrics/
│   │   ├── revenue-definitions.md
│   │   └── user-engagement.md
│   ├── policies/
│   │   ├── data-retention.md
│   │   └── refund-policy.md
│   └── glossary/
│       └── business-terms.md
└── user/
    └── <user-id>/
        └── scratch.md

Best Practices

1. Use read-only database credentials - ktx never writes, but enforce it at the DB level
2. Commit semantic layer and wiki - Share context across team
3. Keep .ktx/ local - Contains secrets and local state
4. Re-ingest after schema changes - ktx ingest after dbt runs or migrations
5. Document metrics in wiki - Add business context beyond SQL definitions
6. Use environment variables for secrets - Never commit credentials
7. Test queries before agents use them - ktx query validates SQL
8. Review contradiction reports - Resolve conflicting definitions promptly

Project Resolution

ktx finds projects in this order:

1. --project-dir flag
2. KTX_PROJECT_DIR environment variable
3. Nearest ktx.yaml in parent directories
4. Current working directory

For scripting, always use explicit project dir:

ktx ingest --project-dir /opt/analytics
ktx mcp start --project-dir /opt/analytics

---

Documentation: https://docs.kaelio.com/ktx
GitHub: https://github.com/Kaelio/ktx
Slack Community: https://join.slack.com/t/ktxcommunity/shared_invite/zt-3y9b44m1x-LVyNNJD5nwaZHq4XS29LMQ