Manage Concepts Database

This skill provides workflows for working with the concepts SQLite database to prevent duplicates and maintain data quality.

MANDATORY RULES

1. ALWAYS verify before adding a new concept

   • Run npx tsx scripts/verify-concept.ts BEFORE creating any new concept JSON file
   • Minimum 90% confidence that concept doesn't exist
   • If confidence ≥70%, manually review potential duplicates

2. ALWAYS update database after concept changes

   • After adding/editing concept JSON: run npx tsx scripts/sync-concepts-db.ts
   • Database must stay in sync with JSON files

3. NEVER skip duplicate checks

   • Even if you think it's unique, run verification
   • All checks are logged for audit trail

Available Scripts

All scripts are TypeScript and should be invoked with npx tsx:

Workflow: Adding a New Concept

Step 1: Verify Concept Doesn't Exist

npx tsx scripts/verify-concept.ts \
  --name "Concept Name" \
  --summary "Brief summary" \
  --aliases "Alias 1,Alias 2" \
  --related-notes "https://notes.dsebastien.net/..."

Interpret Results:

• Confidence ≥90%: STOP - concept likely exists. Review suggested duplicates.
• Confidence 70-89%: REVIEW manually. Check suggested duplicates. Decide if truly different.
• Confidence <70%: PROCEED with caution. Log decision reasoning.

Step 2: If Approved, Create Concept JSON

Only proceed if confidence <90% OR you've manually verified it's unique.

Create /home/dsebastien/wks/concept-cards/src/data/concepts/{id}.json following schema in AGENTS.md.

Step 3: Sync Database

npx tsx scripts/sync-concepts-db.ts

Verify concept was added to database successfully.

Workflow: Updating an Existing Concept

Step 1: Edit Concept JSON

Make changes to /home/dsebastien/wks/concept-cards/src/data/concepts/{id}.json

Step 2: Sync Database

npx tsx scripts/sync-concepts-db.ts

Database will automatically update based on content hash change.

Workflow: Merging Duplicates

Step 1: Identify Duplicates

# Scan all concepts for duplicates
npx tsx scripts/find-duplicates.ts --threshold 80

Step 2: Review and Decide

• Compare concepts side-by-side
• Decide which to keep (target) and which to merge (source)
• Choose merge strategy

Step 3: Execute Merge

npx tsx scripts/merge-duplicates.ts \
  --source {source-id} \
  --target {target-id} \
  --strategy merge-fields

This will:

• Combine data from both concepts
• Update cross-references
• Delete source JSON file
• Update database

Step 4: Sync Database

npx tsx scripts/sync-concepts-db.ts

Step 5: Verify

# Check target concept exists
cat /home/dsebastien/wks/concept-cards/src/data/concepts/{target-id}.json

# Check source concept deleted
ls /home/dsebastien/wks/concept-cards/src/data/concepts/{source-id}.json  # should error

Common Scenarios

Scenario 1: User Asks to Add Concepts from MoC

1. For EACH concept to add:

   • Run verify-concept.ts with name and summary
   • If confidence <90%, proceed with creation
   • If confidence ≥90%, inform user of existing concept and ask if they want to update it instead
   • After creating concept, run sync-concepts-db.ts

2. Run final sync after all concepts added:

   npx tsx scripts/sync-concepts-db.ts
   

Scenario 2: Bulk Import from Multiple MoCs

1. Create a temporary script that:

   • Reads each MoC note
   • For each potential concept, calls verify-concept.ts
   • Logs all HIGH confidence duplicates
   • Only creates LOW/MEDIUM confidence concepts
   • Outputs report of skipped duplicates

2. Review report with user

3. Manually handle high-confidence duplicates

4. Run final sync

Scenario 3: User Reports Duplicate Concepts

1. Run similarity check:

   npx tsx scripts/verify-concept.ts --name "Concept Name" --summary "..."
   

2. If duplicates confirmed, merge:

   npx tsx scripts/merge-duplicates.ts --source {id1} --target {id2} --strategy merge-fields
   npx tsx scripts/sync-concepts-db.ts
   

Database Maintenance

Check Database Health

# View database stats
sqlite3 /home/dsebastien/wks/concept-cards/concepts.db "
  SELECT
    (SELECT COUNT(*) FROM concepts) as total_concepts,
    (SELECT COUNT(*) FROM concept_aliases) as total_aliases,
    (SELECT COUNT(*) FROM concept_tags) as total_tags,
    (SELECT COUNT(*) FROM duplicate_checks) as total_checks;
"

Rebuild Database from JSON

If database gets corrupted or out of sync:

# Delete database
rm /home/dsebastien/wks/concept-cards/concepts.db

# Reinitialize
npx tsx scripts/init-concepts-db.ts

Troubleshooting

Issue: verify-concept.ts shows false positives

Solution: Adjust similarity thresholds in script. Review and tune weights.

Issue: Database out of sync with JSON files

Solution: Run npx tsx scripts/sync-concepts-db.ts

Issue: Need to force-add concept despite high confidence match

Solution: Add --force flag to skip duplicate check (use sparingly, document why)

Documentation References

• Implementation plan: /home/dsebastien/wks/concept-cards/documentation/plans/sqlite-duplicate-detection-plan.md
• Database reference: /home/dsebastien/wks/concept-cards/documentation/plans/concepts-database.md
• AGENTS.md: Complete concept schema and guidelines

Key Principles

1. Prevention over correction: Always verify BEFORE creating
2. Automatic sync: Run sync after every change
3. Audit trail: All verification checks are logged
4. Conservative thresholds: 90%+ confidence = reject
5. User control: High-confidence matches require user decision

Example: Complete Workflow

# 1. Verify concept doesn't exist
npx tsx scripts/verify-concept.ts \
  --name "Parkinson's Law" \
  --summary "Work expands to fill time available"

# Output shows: Confidence: 15% - No strong matches found. Safe to add.

# 2. Create concept JSON file
# (Create /home/dsebastien/wks/concept-cards/src/data/concepts/parkinsons-law.json)

# 3. Sync database
npx tsx scripts/sync-concepts-db.ts

# Output: ✓ Added parkinsons-law to database