---
name: precision-column-separator
description: Enhanced precision agent with column separation logic and ambiguity detection
---

# Precision Column Separator Agent

Analyze the database at `./database.sqlite` with extreme focus on column separation, foreign key joins, and ambiguity resolution. Save comprehensive findings to `./output/agent_output.txt`.

## Process Overview

Execute three analysis tools in sequence, then synthesize outputs into a comprehensive database guide with special attention to column separation patterns.

### Step 1: Junction Table and Foreign Key Analysis
Run the enhanced junction analyzer:
```bash
python tools/enhanced_junction_analyzer.py
```

This identifies:
- Junction tables with actual data
- Foreign key join templates (use these, not name concatenation!)
- Date/time columns needing STRFTIME
- Award/result columns needing filters

Review outputs:
- `tool_output/junction_analysis.txt` - Critical table patterns
- `tool_output/foreign_key_joins.txt` - Optimal join templates
- `tool_output/foreign_keys.json` - Complete foreign key data

### Step 2: Column Separation Analysis
Run the column separator analyzer:
```bash
python tools/column_separator_analyzer.py
```

This determines:
- When to return columns separately vs concatenated
- Multi-part name patterns (first, middle, last)
- SQLite date/time function templates
- Evidence interpretation patterns

Review outputs:
- `tool_output/column_separation.txt` - Separation rules and patterns
- `tool_output/column_separation.json` - Detailed separation data

### Step 3: Ambiguity Detection
Run the ambiguity detector:
```bash
python tools/ambiguity_detector.py
```

This identifies:
- Similar columns that might be confused
- Implicit filters needed for context
- Ambiguous negation patterns
- Common column name mismatches

Review outputs:
- `tool_output/ambiguity_report.txt` - Ambiguity warnings
- `tool_output/ambiguities.json` - Detailed ambiguity data

### Step 4: Comprehensive Database Documentation

Synthesize all tool outputs into complete analysis including:

1. **Database Overview**: Purpose and contents

2. **🔴 ULTRA-CRITICAL RULES** (from all tools):
   - **Column Separation**: When evidence lists columns without operators, return SEPARATELY
   - **Foreign Key Joins**: Always use foreign keys, not name concatenation
   - **Date Functions**: Use STRFTIME for date extraction, not CAST
   - **Implicit Filters**: Award queries often need result='Winner'
   - **Ambiguous Negations**: "non-X" patterns may have inverted logic

3. **Complete Schema with Precision Notes**:
   - Every table with exact column names
   - Foreign key relationships with join templates
   - Multi-part columns clearly marked
   - Sample values for validation

4. **Junction Table Analysis**:
   - Which junction tables contain data columns
   - When to query junction vs base tables
   - Specific foreign key join patterns

5. **Column Separation Matrix**:
   - Multi-part names: return separate unless || shown
   - Evidence patterns and their interpretation
   - Common mistakes to avoid

6. **SQLite Function Library**:
   - Date extraction: STRFTIME templates
   - Time parsing: SUBSTR patterns
   - String operations: concatenation vs separation

7. **Ambiguity Warnings**:
   - Similar columns that are easily confused
   - Negation patterns that might be inverted
   - Context-specific implicit filters

8. **Foreign Key Join Templates**:
   - Exact join syntax for each relationship
   - Prefer these over any name matching

## Output Format

Structure analysis for maximum clarity on separation and joins:

```
# [Database Name] Precision Analysis

## Database Overview
[Purpose and contents]

## 🔴 ULTRA-CRITICAL RULES

### Column Separation Rule #1
- "full name refers to first middle last" → SELECT first, middle, last (3 columns!)
- NOT: SELECT first || ' ' || middle || ' ' || last (unless || in evidence)

### Foreign Key Join Rule #2  
- ALWAYS use foreign key joins from analysis
- NEVER join on name concatenation
[Include specific join templates]

### Date Function Rule #3
- Use STRFTIME('%Y', date_col) for year extraction
- NOT: CAST(date_col AS DATE)

### Evidence Precision Rule #4
- "detailed issue refers to Sub-issue" → Use Sub-issue column
- Follow evidence column names EXACTLY

## Complete Schema
[For each table, include:]
- Exact column names
- Foreign keys with join templates
- Multi-part column warnings
- Date/time column formats

## Junction Tables - CRITICAL
[Which have data, which are pure links]
[When to use each]

## Column Separation Patterns
[From tool output - all patterns]

## SQLite Function Templates
[All date/time extraction patterns]

## Ambiguity Warnings
[Similar columns, negation patterns, implicit filters]

## Foreign Key Join Library
[Every foreign key with exact join syntax]
```

### Key Requirements

1. **Include ALL tool warnings**: Every separation rule, ambiguity, and pattern
2. **Show exact join templates**: For every foreign key relationship
3. **Emphasize column separation**: Make it crystal clear when to separate
4. **Flag ambiguities prominently**: Especially negation patterns
5. **Provide SQLite templates**: For all date/time operations

### Focus Areas

- **Name columns**: Always check if should be separate or concatenated
- **Foreign keys**: Provide exact join syntax, emphasize not using name matching
- **Date columns**: Always show STRFTIME templates
- **Award/result queries**: Note implicit winner filters
- **Ambiguous negations**: Warn about potential logic inversions

Save complete analysis to `./output/agent_output.txt`.