Skip to main content

ADR-029-v4: FoundationDB Issue Tracking Data Model - Part 1 (Narrative)

Document Specification Block​

Document: ADR-029-v4-foundationdb-issue-tracking-data-model-part1-narrative
Version: 1.1.0
Purpose: Explain FoundationDB data modeling for issue tracking in CODI2
Audience: Business leaders, developers, architects
Date Created: 2025-09-13
Date Modified: 2025-09-13
Status: APPROVED
Approved By: RUST-QA-SESSION1
Approval Date: 2025-09-13

Table of Contents​

  1. Introduction
  2. Context and Problem Statement
  3. Decision
  4. Key Capabilities
  5. Benefits
  6. Analogies and Examples
  7. Risks and Mitigations
  8. Success Criteria
  9. Testing & Validation
  10. Conclusion

↑ Back to Top

1. Introduction​

1.1 For Business Leaders​

Imagine you're organizing a massive filing cabinet system for tracking problems in your software development process. Traditional databases are like having one giant drawer where everything gets mixed together - finding specific issues becomes slower as you add more.

Our FoundationDB approach is like having an intelligent filing system with multiple indexes - you can instantly find all "critical" issues, all issues from "today," or all issues affecting "authentication" without searching through everything. It's like having a librarian who maintains separate card catalogs for every way you might want to look up information.

1.2 For Technical Leaders​

We're implementing a normalized key-value data model on top of FoundationDB's distributed architecture. This provides ACID guarantees while enabling efficient querying through secondary indexes. Think of it as building a document store with the consistency of a relational database but the scalability of NoSQL.

2. Context and Problem Statement​

2.1 The Challenge​

CODI2 needs to track various types of issues:

  • Build failures with specific error details
  • Test failures with expected vs actual results
  • Runtime errors with stack traces
  • Configuration problems
  • Authentication issues

Each issue needs to be queryable by multiple dimensions (severity, date, category, status) without full table scans. Traditional relational databases would require complex joins, while simple key-value stores would require scanning all data.

2.2 Current State​

Currently, issues are logged to text files or lost in build logs. There's no systematic way to:

  • Track issue resolution over time
  • Query issues by various criteria
  • Link issues to specific test runs or builds
  • Generate analytics on issue patterns

2.3 Business Impact​

Without proper issue tracking:

  • Developer Time Lost: 30% of debugging time spent finding related issues
  • Duplicate Work: Same issues solved multiple times by different developers
  • Quality Metrics: No visibility into issue trends or hot spots
  • Release Risk: Unknown issue accumulation affects release confidence

2.4 Quantified Business Value​

Implementing structured issue tracking delivers measurable ROI:

  • Time Savings: 10 developers × 2 hours/week saved × $150/hour = $156,000/year
  • Reduced Duplicates: 50 duplicate issues/month × 4 hours each × $150/hour = $360,000/year
  • Faster Resolution: 25% reduction in MTTR = 2 days faster release cycles
  • Total Annual ROI: $516,000+ in direct savings

3. Decision​

3.1 Core Concept​

We will implement a normalized key-value pattern where:

  • Primary data (full issue details) stored once under a unique key
  • Secondary indexes (just references) enable fast queries
  • All updates happen atomically across all indexes
  • Queries use range scans on indexes for efficiency

3.2 How It Works​

4. Key Capabilities​

4.1 Multi-Dimensional Queries​

Query issues by any combination of:

  • Time range (today, this week, this month)
  • Severity (critical, high, medium, low)
  • Category (build, test, runtime, config)
  • Status (open, in-progress, resolved)
  • Associated context (test ID, build ID, session ID)

4.2 Atomic Updates​

When an issue changes status:

  1. Update primary issue data
  2. Remove from old status index
  3. Add to new status index
  4. Update modification timestamp
  5. All happen in one atomic transaction

4.3 Efficient Analytics​

Pre-aggregated counters enable instant metrics:

  • Issues per day/hour
  • Issues by severity distribution
  • Mean time to resolution
  • Hot spot identification

5. Benefits​

5.1 For End Users​

  • Instant Search: Find related issues in milliseconds
  • Complete History: Never lose track of an issue
  • Context Preservation: Full details including stack traces
  • Cross-Reference: Link issues to builds, tests, and sessions

5.2 For Organizations​

  • Quality Metrics: Track issue trends over time
  • Resource Planning: Identify problem areas needing attention
  • Release Confidence: Know exactly what issues exist
  • Knowledge Base: Historical issues become searchable documentation

5.3 For Operations​

  • Zero Downtime: Add new indexes without migration
  • Horizontal Scale: Distribute load across FDB cluster
  • Backup/Restore: Built-in FDB capabilities
  • Multi-Region: Geographic distribution supported

6. Analogies and Examples​

6.1 Library Card Catalog Analogy​

Traditional Database (Single Index):

  • Like old libraries with one card catalog sorted by title
  • To find books by author, check every single card
  • Adding new ways to search requires reorganizing everything

FoundationDB Approach (Multiple Indexes):

  • Like modern libraries with separate catalogs for title, author, subject, date
  • Each catalog points to the same books on shelves
  • Adding new catalogs doesn't disturb existing ones

6.2 Real-World Scenario​

Without This Solution:

  1. Developer encounters authentication error
  2. Searches through build logs (10 minutes)
  3. Greps through test outputs (5 minutes)
  4. Asks team if anyone saw similar issue (waiting)
  5. Finally finds related issue from last week
  6. Total time lost: 30+ minutes

With This Solution:

  1. Developer encounters authentication error
  2. Queries: codi2 issue list --category auth --since last-week
  3. Sees 3 similar issues with resolutions
  4. Reviews solutions and stack traces
  5. Applies known fix
  6. Total time: 2 minutes

7. Risks and Mitigations​

7.1 Storage Overhead​

  • Risk: Multiple indexes consume more storage
  • Mitigation: Indexes store only IDs (minimal overhead ~10%)

7.2 Index Consistency​

  • Risk: Indexes could become out of sync
  • Mitigation: Atomic transactions ensure all-or-nothing updates

7.3 Query Complexity​

  • Risk: Complex queries might be slow
  • Mitigation: Design indexes for common query patterns

8. Success Criteria​

8.1 Performance Metrics​

  • Single issue lookup: < 10ms
  • Query by index: < 50ms for 1000 results
  • Issue creation: < 100ms including all indexes
  • Concurrent operations: 10,000+ per second

8.2 Business Metrics​

  • Issue resolution time: 50% reduction
  • Duplicate issues: 80% reduction
  • Developer satisfaction: 90%+ positive feedback
  • Issue visibility: 100% searchable history

8.3 Technical Architecture​

↑ Back to Top

9. Testing & Validation​

9.1 Test Coverage Requirements​

  • Unit Tests: 90% coverage of all business logic
  • Integration Tests: 80% coverage of API endpoints
  • Critical Path Tests: 100% coverage of issue creation/query flows
  • Performance Tests: Validate all SLA requirements

9.2 Validation Criteria​

Each issue operation must be validated for:

  • Data Integrity: All indexes updated atomically
  • Query Performance: Meeting response time SLAs
  • Concurrent Access: No race conditions under load
  • Error Recovery: Graceful handling of FDB failures

9.3 Test Scenarios​

  1. Create 10,000 issues and verify all indexes
  2. Query by each dimension with 1000+ results
  3. Concurrent updates to same issue
  4. Network partition recovery
  5. Index consistency verification

10. Conclusion​

By implementing a properly indexed data model on FoundationDB, we transform issue tracking from a logging afterthought into a powerful debugging and quality improvement tool. The investment in proper data modeling pays dividends through faster issue resolution, better quality metrics, and improved developer productivity.

This approach balances the consistency needs of critical issue data with the performance requirements of a high-velocity development environment. It's not just about storing issues - it's about making that information instantly accessible and actionable.