rdmbair15m5-changelog-20260921-1815-ticket-store-project-case-folding-and-normalization

Summary: Resolved ISSUE-20260904-04 in issues/bin/ticket: eliminated case-split backlog omissions by adding COLLATE NOCASE query filtering in cmd_list and cmd_next (queries such as --project tyrell now match all 188 tickets across casing variations) and added canonical project name normalization on write via FLEET-PROJECT-POINTERS.md; verified across all 12 test suites in issues/tests/ with zero regressions.

Scope

• Hosts Affected: Canonical dev hub rdmsm4x and fleet-wide ticket consumers (Claude Code, Antigravity, Codex, ticket CLI, and ticket-mcp).
• Components: issues/bin/ticket, issues/bin/ticket-mcp, SQLite schema query engine, and issue tracking test suite.

Key Remediation & Architecture

1. Case-Folded Query Matching on Read (cmd_list & cmd_next):

   • Updated SQLite query generation in cmd_list to WHERE ... AND project = ? COLLATE NOCASE.
   • Updated SQLite query generation in cmd_next to WHERE ... AND project = ? COLLATE NOCASE.
   • Result: Queries like ticket list --project tyrell and ticket next --project tyrell now match both Tyrell (130 tickets) and tyrell (58 tickets), returning the full 188-item backlog rather than silently dropping 70% of tickets.

2. Canonical Project Key Normalization on Write (cmd_open & Indexing):

   • Added get_canonical_projects_map() loading canonical project names from FLEET-PROJECT-POINTERS.md (e.g. Tyrell, RTTy, LogTTY, HyperCPU, ModelVault, PasswordScope, CoreAudit).
   • Implemented normalize_project(project_name, conn):
     1. Resolves against FLEET-PROJECT-POINTERS.md canonical names.
     2. Falls back to most frequent existing project casing in SQLite database.
     3. Preserves novel project names.
   • Wired normalize_project() into cmd_open when authoring ticket metadata and into index_ticket_file() during reindexing.

3. Regression Test Suite Added:

   • Authored tests/test_project_case_folding.py using isolated test store harness (tests/isolation.py).
   • Asserts that ticket open --project tyrell writes normalized Tyrell to frontmatter.
   • Asserts that legacy tickets with lowercase tyrell are indexed and matched equally by ticket list --project tyrell and ticket list --project Tyrell.
   • Asserts that ticket next evaluates tickets across all casing variants.
   • Verifies canonical store is completely untouched.

Verification Evidence

• Empirical CLI Query Verification on Canonical Hub rdmsm4x:

  • ticket list --project tyrell: 188 tickets (previously 58).
  • ticket list --project Tyrell: 188 tickets (exact match).
  • ticket list --project rtty: 68 tickets (previously 14).
  • ticket list --project RTTy: 68 tickets (exact match).
  • ticket list --project replicantdb: 45 tickets (previously 8).
  • ticket list --project replicantDB: 45 tickets (exact match).
  • ticket next --project tyrell: Evaluated full backlog and correctly discovered critical work (EPIC-20260905-01).

• Test Suite Execution (12 Suites Passing 100%):

  • tests/test_adversarial_suite.py: PASS
  • tests/test_claim_leases_and_plans.py: PASS
  • tests/test_cli_and_mcp_adversarial.py: PASS
  • tests/test_concurrent_locks.py: PASS
  • tests/test_e2e_adversarial_suite.py: PASS
  • tests/test_fts5_queries.py: PASS
  • tests/test_isolation_coverage.py: PASS
  • tests/test_jsonrpc_stdio.py: PASS
  • tests/test_offer_accept.py: PASS
  • tests/test_project_case_folding.py: PASS (New regression suite)
  • tests/test_stale_index_resolve.py: PASS
  • tests/test_sync_trailers.py: PASS

• Git Repositories & Commits:

  • issues: Commit e3d99be (fix(ticket): resolve ISSUE-20260904-04 case-split project keys via COLLATE NOCASE and pointer normalization).
    • Pushed to git.ecs0.net:git/dev/issues.git (main).
    • Pushed to GitHub backup mirror https://github.com/richhdoty/rdmsm4x-dev-issues.git (main).

• Resolved Tickets:

  • ISSUE-20260904-04: Ticket store: project keys are case-split, so --project silently returns half the backlog.

Files Modified

• issues/bin/ticket
• issues/tests/test_project_case_folding.py
• issues/resolved/ISSUE-20260904-04-ticket-store-project-keys-are-case-split.md

Rollback & Undo

• Revert commit e3d99be in issues.