Sqlite Analyzer

SQLite Database Analyzer

Overview

This skill enables systematic analysis of SQLite databases to identify structural issues, data inconsistencies, and relationship problems. It provides workflows for investigating database bugs, validating schemas, and understanding entity relationships.

When to Use This Skill

• Debugging ORM Issues: ID fields mapped incorrectly (e.g., using name instead of actual id)
• Data Integrity Checks: Finding orphaned references or foreign key violations
• Schema Investigation: Understanding table structure and relationships
• Bug Investigation: Identifying root causes of data-related bugs
• Migration Validation: Verifying database migrations completed successfully

Quick Start Workflow

1. Locate Database: Find the SQLite .db file path
2. Inspect Schema: Use .schema or .tables to understand structure
3. Query Data: Run targeted SELECT queries to investigate issues
4. Cross-Reference: Compare data between related tables
5. Report Findings: Document issues with specific examples

Analysis Tasks

1. Schema Inspection

Discover all tables:

.tables

View specific table schema:

.schema table_name

Get detailed table info:

PRAGMA table_info(table_name);

Example output interpretation:

cid  name       type     notnull  dflt_value  pk
---  ---------  -------  -------  ----------  --
0    id         TEXT     1        NULL        1
1    name       TEXT     1        NULL        0
2    config     TEXT     0        NULL        0

2. Data Sampling

Quick row count:

SELECT COUNT(*) FROM table_name;

Sample data with column details:

SELECT * FROM table_name LIMIT 5;

Check for NULL values:

SELECT
  COUNT(*) as total,
  COUNT(column_name) as non_null,
  COUNT(*) - COUNT(column_name) as null_count
FROM table_name;

3. Relationship Validation

Find orphaned references:

SELECT child.id, child.foreign_key_column
FROM child_table child
LEFT JOIN parent_table parent ON child.foreign_key_column = parent.id
WHERE parent.id IS NULL;

Count references per parent:

SELECT
  parent.id,
  parent.name,
  COUNT(child.id) as child_count
FROM parent_table parent
LEFT JOIN child_table child ON parent.id = child.parent_id
GROUP BY parent.id, parent.name
ORDER BY child_count DESC;

Verify bidirectional relationships:

-- Check if all referenced IDs exist
SELECT DISTINCT foreign_key_column
FROM child_table
WHERE foreign_key_column NOT IN (SELECT id FROM parent_table);

4. ID Mismatch Detection

Common bug pattern: ORM returns name as id

Diagnostic query:

-- Compare actual ID with name field
SELECT
  id,
  name,
  CASE
    WHEN id = name THEN '⚠️ ID equals name (potential bug)'
    ELSE '✅ ID distinct from name'
  END as status
FROM table_name;

Check for CUID/UUID patterns:

SELECT
  id,
  name,
  LENGTH(id) as id_length,
  CASE
    WHEN LENGTH(id) = 25 THEN 'CUID2'
    WHEN LENGTH(id) = 36 THEN 'UUID'
    WHEN LENGTH(id) < 20 THEN 'Short ID (possibly name)'
    ELSE 'Unknown format'
  END as id_format
FROM table_name
LIMIT 10;

5. Debugging Workflow for ID Mismatches

When you suspect IDs are being mapped incorrectly (like the MCP server bug):

Step 1: Verify actual database content

SELECT id, name FROM mcp_servers;

Step 2: Check what backend returns

• Add debug logging in DTO/serialization layer
• Compare logged IDs with database IDs

Step 3: Identify the mapping bug

// ❌ WRONG: Using name as ID
id: model.name.clone(),

// ✅ CORRECT: Using actual ID field
id: model.id.clone(),

Step 4: Verify the fix

-- After fix, verify IDs are preserved
SELECT id, name FROM table_name
WHERE id IN ('expected-cuid-1', 'expected-cuid-2');

6. Data Consistency Checks

Find duplicate keys:

SELECT column_name, COUNT(*) as count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

Check for invalid JSON:

SELECT id, json_column
FROM table_name
WHERE json_valid(json_column) = 0;

Timestamp validation:

SELECT
  id,
  created_at,
  updated_at,
  CASE
    WHEN updated_at < created_at THEN '⚠️ Updated before created'
    WHEN created_at > strftime('%s', 'now') * 1000 THEN '⚠️ Future timestamp'
    ELSE '✅ Valid'
  END as status
FROM table_name
WHERE updated_at < created_at OR created_at > strftime('%s', 'now') * 1000;

Investigation Templates

Template 1: Entity Relationship Bug

## Issue

[Description of the bug, e.g., "Assistant shows MCP server IDs instead of names"]

## Database Inspection

```sql
-- Step 1: Check parent table
SELECT id, name FROM parent_table LIMIT 5;

-- Step 2: Check child table references
SELECT id, name, foreign_key_column FROM child_table LIMIT 5;

-- Step 3: Join to verify relationships
SELECT
  child.id as child_id,
  child.foreign_key_column as ref_id,
  parent.name as parent_name
FROM child_table child
LEFT JOIN parent_table parent ON child.foreign_key_column = parent.id;
```

Findings

• Actual database IDs: [list actual IDs]
• Backend returned IDs: [list returned IDs]
• Mismatch: [describe the discrepancy]

Root Cause

[Identify the code causing incorrect mapping]

Fix

[Show the correction needed]

### Template 2: Migration Validation

```markdown
## Migration Goal
[What the migration was supposed to achieve]

## Validation Queries
```sql
-- Check schema changes
PRAGMA table_info(table_name);

-- Verify data migration
SELECT COUNT(*) FROM table_name WHERE new_column IS NOT NULL;

-- Check constraints
PRAGMA foreign_key_list(table_name);

Results

• Schema: [✅ correct / ❌ issues found]
• Data: [✅ migrated / ❌ missing data]
• Constraints: [✅ valid / ❌ violations found]

## Best Practices

1. **Always use absolute paths** when locating database files
2. **Sample before bulk operations** - check a few rows first
3. **Use transactions for analysis** - wrap in BEGIN/ROLLBACK if testing updates
4. **Document ID formats** - note whether using CUID, UUID, or other formats
5. **Cross-reference logs** - compare database state with application logs
6. **Check multiple timestamps** - initial app start vs. current state
7. **Consider data lifecycle** - some inconsistencies may be from migration periods

## Common Pitfalls

- **Assuming ID == name**: Many bugs come from treating name as primary key
- **Ignoring case sensitivity**: SQLite is case-insensitive by default
- **Not checking NULL values**: Can cause unexpected JOIN results
- **Forgetting LIMIT**: Large tables can overflow terminal output
- **Using old schema assumptions**: Schema may have changed between app versions

## Resources

### scripts/
Contains executable utilities for common database analysis tasks:

- `analyze_schema.py` - Generate comprehensive schema report
- `find_orphans.py` - Detect orphaned references across all tables
- `compare_ids.py` - Compare ID formats and detect mismatches
- `export_sample.py` - Export sample data for debugging

### references/
Additional documentation for advanced analysis:

- `sqlite-optimization.md` - Performance tuning and query optimization
- `common-patterns.md` - Frequently encountered database patterns
- `debugging-checklist.md` - Systematic debugging approach