#25 fix(pm-tasks): exclude subtasks and normalize status categories

[Anonymous]

Originally created by: adityaharishch

• Filter Jira REST queries to fetch only Task and Feature issue types
• Add separate parent_key column for epic/parent relationship tracking
• Use only normalized status_category values (todo, in_progress, done)
• Remove redundant status column via database migration
• Fix Rust daemon pm_tasks upsert SQL parameter binding

Changes:

• src/intelligence/providers/jira.rs: Updated JQL, added parent handling, fixed SQL
• scripts/refresh_pm_tasks.py: Filter subtasks, normalize status, add parent tracking
• services/agents/run_task_linker.py: Query parent_key from db, filter subtasks
• services/agents/_system_context.py: Update column documentation
• src/migrations/021_remove_subtasks.sql: Delete existing subtasks
• src/migrations/022_drop_status_keep_category.sql: Drop status column
• tests/task_linker_smoke.rs: Update migration runs
• services/requirements.txt: Add dependencies
• .gitignore: Ignore tool artifacts

[Anonymous]

Originally posted by: Akarsh-Hegde

Code Review - PR [#25]: Fix PM Tasks

✅ Overall Assessment

Good work on cleaning up the PM tasks schema and filtering logic. The changes improve data quality by excluding subtasks and normalizing status categories.

🔍 Detailed Review

1. JQL Filtering Logic (src/intelligence/providers/jira.rs)

• Positive: Adding AND type NOT IN (Subtask) is the correct way to filter subtasks in Jira
• Question: Have you tested this JQL works across different Jira Cloud instances? Some orgs may have custom issue types
• Suggestion: Add a comment explaining why we exclude subtasks (they clutter the task list and are tracked via parent_key instead)

2. Status Category Normalization (Migrations 021 & 022)

• Concern: Two separate migrations for related changes. Consider consolidating:
• 021: DELETE FROM pm_tasks WHERE issue_type = 'Subtask'
• 022: ALTER TABLE DROP status column
• These could be one migration: DELETE subtasks, then drop column
• Question: What happens to existing subtask rows? Are they properly deleted before dropping the column?
• Data Integrity: Verify no foreign keys or triggers depend on the status column

3. parent_key Column

• Positive: Good design for tracking epic/parent relationships
• Question: What values should parent_key contain?
• For subtasks: the parent task key
• For regular tasks: NULL?
• For epics: NULL?
• Document this in a schema comment or DB docstring

4. Python Changes (services/agents/run_task_linker.py)

• Question: How does the task linker handle subtasks now that they're excluded from pm_tasks?
• If a session maps to a subtask, will it fail to find the parent?
• Should we add fallback logic to map subtasks → parent_key → actual task?

5. SQL Parameter Binding (Rust upsert)

• Positive: Good catch on fixing SQL bindings
• Verification: Did you run cargo test to confirm no test regressions?

⚠️ Potential Issues

1. Backwards Compatibility: Existing data with status column will be deleted. Is there a backfill/migration path for production?
2. Subtask Handling: What's the migration path for sessions already linked to subtasks in ticket_links?
3. Testing: Are there integration tests verifying the new JQL works correctly and fetches the right task count?

🚀 Suggestions

1. Add a unit test for the new JQL in jira.rs:
   rust #[test] fn test_jql_excludes_subtasks() { /* ... */ }

2. Add a comment to the parent_key column documenting its semantics

3. Verify the migrations work on a fresh DB and also on an existing DB with data

✨ Nice to Have

• Document why we're excluding subtasks (e.g., "Subtasks clutter the task list; use parent_key for relationships instead")
• Add a changelog note for ops/deployment (migrations are destructive)

---

Status: Request Review After Addressing [#1] & [#2]

Let me know if you'd like me to review the full diff in more detail or pair on the migration consolidation.

[Anonymous]

Ticket changed by: adityaharishch

• status: open --> closed