Technical Design TD-0007 — Issue Tracking Audit, Search and Reporting¶

1. Summary¶

• The issue tracking service needs an activity log, field-level change history, soft delete workflow, search integration with ai-service, and reporting endpoints
• This document specifies the audit trail model, soft delete mechanics, search indexing/querying, and reporting queries
• Impact: provides accountability, discoverability, and delivery insights for project management

2. Context and Problem Statement¶

The existing service has basic event sourcing (outbox/eventlog from v00-setup.sql) for four event types, but no user-facing activity timeline, no field-level audit trail, no search capability, and no reporting. Users need to understand what changed, when, and by whom. They need to find issues quickly and measure delivery performance.

2.1 Goals¶

• User-facing activity log with human-readable event timeline per issue
• Field-level change history for complete audit trail
• Soft delete with restore workflow and cascade to child issues
• Search integration with ai-service via RabbitMQ events
• Reporting endpoints: velocity, cycle time, epic progress, bugs-in-progress

2.2 Out of Scope¶

• Hard delete background job (future iteration)
• Search ranking algorithm (ai-service responsibility)
• Custom report builder or dashboards
• Export to CSV/PDF
• Compliance-grade audit (tamper-proof log, digital signatures)

2.3 Assumptions and Constraints¶

• Activity log and change history are append-only — never updated or deleted
• Search indexing is eventually consistent (slight delay after writes)
• Reporting queries run against the same database (no dedicated analytics store)
• ai-service is an optional dependency; search degrades gracefully to SQL ILIKE

3. Design Overview¶

3.1 Key Components¶

3.2 Architecture¶

flowchart TB
    subgraph IssueService["loryth-issues"]
        AL[Activity Log]
        CH[Change History]
        SD[Soft Delete]
        SP[Search Proxy]
        RP[Reporting]
    end

    subgraph Events["RabbitMQ"]
        IE[IssueIndexEvent]
        CE[CommentIndexEvent]
        DE[IssueDeleteIndexEvent]
    end

    subgraph AI["ai-service"]
        SI[Search Index]
        SQ[Search Query API]
    end

    IssueService -->|publish| Events
    Events -->|consume| AI
    SP -->|query| SQ
    SQ -->|results| SP

4. Implementation Details¶

4.1 Activity Log¶

ActivityLog Entity¶

Index: (issue_id, created_at) — chronological timeline per issue.

Event Types¶

CREATE TYPE issues.activity_event_type AS ENUM (
    'issue_created',
    'status_changed',
    'assignee_changed',
    'priority_changed',
    'parent_changed',
    'title_changed',
    'description_changed',
    'comment_added',
    'comment_edited',
    'attachment_added',
    'tag_added',
    'tag_removed',
    'link_added',
    'link_removed',
    'checklist_item_checked',
    'checklist_item_unchecked',
    'sprint_changed',
    'issue_deleted',
    'issue_restored',
    'custom_field_changed'
);

Event Payload Examples¶

Auto-Walk Recording Rule¶

When auto-walk traverses statuses A → B → C → D, the activity log records one entry:

{
  "event_type": "status_changed",
  "payload": { "from": "A", "to": "D" }
}

Intermediate statuses B and C are never visible in the activity log. The user sees a single transition.

Activity Log API¶

4.2 Change History¶

ChangeHistory Entity¶

Index: (issue_id, changed_at) — chronological audit trail.

Relationship to Activity Log¶

Multiple field changes in a single operation (e.g., updating title and priority together) produce multiple change_history rows with the same changed_at timestamp, allowing them to be grouped.

Change History API¶

4.3 Soft Delete¶

Soft delete is implemented via the deleted_at: TIMESTAMPTZ column on the issue table (defined in TDD-0004).

Repository-Level Filtering¶

All issue repository methods include WHERE deleted_at IS NULL by default:

class IssueRepository(BaseRepository[Issue]):
    """Repository for Issue aggregate with soft-delete filtering."""

    def _base_query(self) -> Select:
        """Base query that excludes soft-deleted issues.

        :return: SQLAlchemy select with deleted_at filter
        """
        return select(Issue).where(Issue.deleted_at.is_(None))

    def find_deleted_by_project(self, project_id: int) -> list[Issue]:
        """Find soft-deleted issues for restore view.

        :param project_id: Project ID
        :return: List of soft-deleted issues
        """
        return self._execute(
            select(Issue)
            .where(Issue.project_id == project_id)
            .where(Issue.deleted_at.is_not(None))
            .order_by(Issue.deleted_at.desc())
        )

Cascade Soft Delete¶

When an issue is soft-deleted: 1. Set issue.deleted_at = NOW() 2. Recursively soft-delete all child issues (parent_id = issue.id) with the same timestamp 3. Record issue_deleted activity log entry for each affected issue 4. Publish IssueDeleteIndexEvent for each affected issue (removes from search index)

Comments, attachments, links, tags, and checklist items are not individually soft-deleted — they are hidden because their parent issue is hidden.

Restore¶

Restoring an issue: 1. Clear issue.deleted_at = NULL 2. Clear deleted_at on all child issues that were cascade-deleted (same deleted_at timestamp) 3. Record issue_restored activity log entry 4. Publish IssueIndexEvent for each restored issue (re-index in search)

Restore does not recurse into children that were independently soft-deleted before the parent (different deleted_at timestamp). Only cascade-deleted children (matching timestamp) are restored.

Soft Delete API¶

4.4 Search Integration¶

Indexing Events¶

loryth-issues publishes events to ai-service via RabbitMQ when issue or comment content changes:

IssueIndexEvent Payload¶

{
  "issue_key": "PROJ-123",
  "project_key": "PROJ",
  "space_id": "...",
  "title": "Implement login page",
  "description": "Full description text...",
  "status": "In Progress",
  "issue_type": "Story",
  "assignee_id": "...",
  "reporter_id": "...",
  "priority": "high",
  "tags": ["Frontend", "Auth"],
  "custom_fields": {
    "Story Points": "5",
    "Sprint": "Sprint 3"
  }
}

CommentIndexEvent Payload¶

{
  "comment_uuid": "...",
  "issue_key": "PROJ-123",
  "body": "Comment text...",
  "author_id": "..."
}

IssueDeleteIndexEvent Payload¶

{
  "issue_key": "PROJ-123"
}

Search Query Proxy¶

loryth-issues exposes search endpoints that proxy to ai-service:

SearchResultModel:

{
  "issue_key": "PROJ-123",
  "title": "Implement login page",
  "relevance_score": 0.95,
  "snippet": "...matching text excerpt..."
}

Flow: 1. Receive search query from client 2. Call ai-service search API with query + scope (project_key or space_id) 3. Receive ranked list of issue keys with relevance scores 4. Load full issue data from local database for returned keys 5. Return combined results

Fallback (ai-service unavailable):

SELECT issue_key, title
FROM issues.issue
WHERE project_id = :project_id
  AND deleted_at IS NULL
  AND (title ILIKE '%' || :query || '%' OR description ILIKE '%' || :query || '%')
ORDER BY updated_at DESC
LIMIT 20;

4.5 Reporting¶

All reporting endpoints are scoped to a project and return aggregated data.

Velocity Report¶

Query logic: Count issues where a status_changed activity log entry has the target status's category = done and created_at falls within each week window.

SELECT
    date_trunc('week', al.created_at) AS week,
    COUNT(DISTINCT al.issue_id) AS completed_count
FROM issues.activity_log al
JOIN issues.issue i ON i.id = al.issue_id
WHERE i.project_id = :project_id
  AND al.event_type = 'status_changed'
  AND al.payload->>'to' IN (
      SELECT s.name FROM issues.status s WHERE s.category = 'done'
  )
  AND al.created_at >= NOW() - INTERVAL ':weeks weeks'
GROUP BY week
ORDER BY week;

Response:

{
  "data": {
    "weeks": [
      { "week_start": "2026-02-10", "completed": 8 },
      { "week_start": "2026-02-03", "completed": 12 }
    ]
  }
}

Cycle Time Report¶

Query logic: For completed issues, compute time spent in each status using consecutive status_changed events in the activity log. Average per status per issue type.

Response:

{
  "data": {
    "issue_type": "Story",
    "statuses": [
      { "status": "Open", "avg_hours": 24.5 },
      { "status": "In Progress", "avg_hours": 48.0 },
      { "status": "Code Review", "avg_hours": 8.2 },
      { "status": "Done", "avg_hours": null }
    ]
  }
}

Epic Progress Report¶

Query logic: For each Epic-type issue, count children by status category.

SELECT
    parent.issue_key AS epic_key,
    parent.title AS epic_title,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE s.category = 'done') AS done_count,
    COUNT(*) FILTER (WHERE s.category = 'in_progress') AS in_progress_count,
    COUNT(*) FILTER (WHERE s.category = 'todo') AS todo_count
FROM issues.issue child
JOIN issues.issue parent ON child.parent_id = parent.id
JOIN issues.status s ON child.status_id = s.id
JOIN issues.issue_type_definition itd ON parent.issue_type_id = itd.id
WHERE parent.project_id = :project_id
  AND parent.deleted_at IS NULL
  AND child.deleted_at IS NULL
  AND itd.name = 'Epic'
GROUP BY parent.id, parent.issue_key, parent.title;

Response:

{
  "data": {
    "epics": [
      {
        "epic_key": "PROJ-10",
        "title": "User Authentication",
        "total": 8,
        "done": 5,
        "in_progress": 2,
        "todo": 1,
        "progress_percent": 62.5
      }
    ]
  }
}

Bugs in Progress Report¶

Query logic:

SELECT COUNT(*)
FROM issues.issue i
JOIN issues.issue_type_definition itd ON i.issue_type_id = itd.id
JOIN issues.status s ON i.status_id = s.id
WHERE i.project_id = :project_id
  AND i.deleted_at IS NULL
  AND itd.name = 'Bug'
  AND s.category = 'in_progress';

4.6 Error Handling and Edge Cases¶

5. Semantics¶

• Activity log entries are append-only and immutable — they are never modified or deleted.
• Change history captures every field-level mutation, not just user-visible events. It serves as the complete audit trail.
• The activity log is the user-facing timeline; change history is for admin/debugging purposes.
• Search indexing is eventually consistent — there may be a brief delay between a write and its appearance in search results.
• Reporting queries use activity log data as the source of truth for timing calculations (when statuses changed).
• Soft delete uses the deleted_at timestamp as a correlation key for cascade restore — only children with the exact same deleted_at value are restored together.

6. Examples¶

6.1 Activity Timeline¶

GET /issues/PROJ-123/activity

{
  "data": {
    "items": [
      { "event_type": "status_changed", "payload": {"from": "In Progress", "to": "Done"}, "actor_id": "...", "created_at": "2026-02-17T14:00:00Z" },
      { "event_type": "comment_added", "payload": {"comment_uuid": "..."}, "actor_id": "...", "created_at": "2026-02-17T11:00:00Z" },
      { "event_type": "assignee_changed", "payload": {"from": null, "to": "..."}, "actor_id": "...", "created_at": "2026-02-16T09:00:00Z" },
      { "event_type": "issue_created", "payload": {"title": "Fix login bug", "type": "Bug"}, "actor_id": "...", "created_at": "2026-02-15T10:00:00Z" }
    ]
  }
}

6.2 Soft Delete and Restore¶

DELETE /issues/PROJ-123   → 204

Side effects:
- PROJ-123.deleted_at = NOW()
- Child PROJ-124.deleted_at = NOW() (same timestamp)
- Child PROJ-125.deleted_at = NOW() (same timestamp)
- Activity log entries for all three issues
- IssueDeleteIndexEvent for all three issues

PUT /issues/PROJ-123/restore

Side effects:
- PROJ-123.deleted_at = NULL
- PROJ-124.deleted_at = NULL (cascade restore, same timestamp)
- PROJ-125.deleted_at = NULL (cascade restore, same timestamp)
- Activity log entries for all three issues
- IssueIndexEvent for all three issues

6.3 Search with Fallback¶

GET /projects/PROJ/search?q=authentication

1. Attempt: Call ai-service → GET /search?q=authentication&scope=project:PROJ
2. Success: Receive [{issue_key: "PROJ-123", score: 0.95}, {issue_key: "PROJ-45", score: 0.82}]
3. Load full issue data from local DB for PROJ-123 and PROJ-45
4. Return combined results

If ai-service unavailable:
1. Fallback: SELECT issue_key, title FROM issue WHERE title ILIKE '%authentication%' ...
2. Return results with relevance_score = null

7. Test Matrix¶

7.1 Unit Coverage¶

• Activity log event type mapping and payload serialization
• Change history field-name normalization
• Cascade soft delete timestamp propagation
• Search fallback query construction
• Velocity week bucketing
• Cycle time status duration calculation

7.2 Integration Coverage¶

• Activity log entries created on status change, assignment, comment, etc.
• Auto-walk records single activity log entry (not intermediate)
• Change history captures old/new values for all field updates
• Soft delete cascade to child issues and restore round-trip
• Search event publication to RabbitMQ
• Reporting queries with real data (multi-week velocity, cycle time across statuses)

7.3 Regression Coverage¶

• Existing event sourcing (outbox) unaffected by activity log additions
• Soft-deleted issues do not appear in standard listing endpoints
• Search endpoints return valid responses when ai-service is unavailable

8. Acceptance Criteria¶

• Activity log records entries for all defined event types
• Auto-walk produces exactly one status_changed activity log entry
• Change history captures field-level old/new values for every mutation
• Soft delete sets deleted_at and cascades to child issues
• Restore clears deleted_at for issue and cascade-deleted children only
• Issue keys are never reused after soft delete
• Search indexing events published to RabbitMQ on issue/comment changes
• Search falls back to SQL ILIKE when ai-service is unavailable
• Velocity report returns correct completed issue counts per week
• Cycle time report computes average duration per status
• Epic progress report shows correct done/total ratios

9. Rollout and Migration¶

9.1 Phased Plan¶

Phase 4a: Audit schema - Create activity_event_type enum - Create activity_log and change_history tables - Instrument existing service methods to write audit entries

Phase 4b: Soft delete - Add deleted_at column to issue table (if not already added in Phase 1) - Implement cascade soft delete and restore logic - Add soft delete filtering to all repository queries - Build soft delete/restore API endpoints

Phase 4c: Search integration - Implement search indexing event publishers - Build search proxy endpoint - Implement SQL ILIKE fallback

Phase 4d: Reporting - Implement velocity, cycle time, epic progress, and bugs-in-progress queries - Build reporting API endpoints

9.2 Migration Strategy¶

New tables (activity_log, change_history) are additive. No backfill of historical activity is performed — the audit trail begins from the point of deployment. Existing event sourcing data (outbox/eventlog) is preserved but not migrated into the new activity log format.

10. Risks and Mitigations¶

11. Progress¶

• Create activity log entity and repository
• Instrument all service methods to record activity
• Create change history entity and repository
• Instrument field-level change tracking
• Implement cascade soft delete logic
• Implement restore logic
• Build soft delete/restore API endpoints
• Implement search event publishers
• Build search proxy endpoint with fallback
• Implement velocity report
• Implement cycle time report
• Implement epic progress report
• Implement bugs-in-progress report
• Write integration tests

12. Changelog¶

13. Glossary¶