Technical Design TD-0004 — Issue Tracking Domain Model¶

1. Summary¶

• The loryth-issues service needs a complete domain model redesign to support Space-scoped catalogs, project configuration, immutable issue keys, custom fields, type hierarchy constraints, and soft delete
• This document specifies all entities, their fields, relationships, invariants, and SQL migrations for Phase 1
• Impact: transforms the skeleton service into a fully modeled issue tracking domain

2. Context and Problem Statement¶

The current data model has five entities (Status, Workflow/WorkflowTransition, IssueType, Project, Issue) with basic fields and no concept of organizational scoping, project configuration, custom fields, or issue numbering. Issues lack assignees, reporters, priority, ranking, and meaningful metadata. Projects have no key for human-readable issue identifiers.

This design document addresses the complete entity model needed for a production issue tracker, corresponding to Phase 1 of the roadmap.

2.1 Goals¶

• Define all entities with complete field specifications
• Establish Space-scoped isolation for catalog entities
• Design the project configuration model (enabled types, fields, link types, hierarchy rules)
• Specify the Issue aggregate with all required fields
• Define immutable issue key generation (PROJ-123)
• Specify the EAV custom field model
• Provide complete SQL migration DDL

2.2 Out of Scope¶

• Board, sprint, and ranking behavior (see TDD-0005)
• Comments, attachments, links, labels, checklists (see TDD-0006)
• Activity log, change history, search, reporting (see TDD-0007)
• Space entity definition (future core-platform service)
• Permission model beyond data isolation

2.3 Assumptions and Constraints¶

• The shared loryth PostgreSQL database is used (not iam)
• Existing migrations v00–v05 are preserved; new migrations extend the schema
• space_id references an external Space entity — no FK constraint to another service's table
• The existing event sourcing infrastructure (outbox, eventlog from v00-setup.sql) is reused
• All entities follow BaseAggregate/BaseEntity patterns from loryth-domain

3. Design Overview¶

3.1 Key Components¶

3.2 Architecture¶

erDiagram
    Space ||--o{ Status : "space_id"
    Space ||--o{ Workflow : "space_id"
    Space ||--o{ IssueTypeDefinition : "space_id"
    Space ||--o{ CustomFieldDefinition : "space_id"
    Space ||--o{ LinkTypeDefinition : "space_id"
    Space ||--o{ Project : "space_id"

    Workflow ||--o{ WorkflowTransition : "workflow_id"
    WorkflowTransition }o--|| Status : "source_status_id"
    WorkflowTransition }o--|| Status : "target_status_id"

    IssueTypeDefinition ||--o| Workflow : "default_workflow_id"

    Project ||--o{ ProjectIssueType : "project_id"
    ProjectIssueType }o--|| IssueTypeDefinition : "issue_type_definition_id"
    ProjectIssueType }o--o| Workflow : "workflow_id (override)"

    Project ||--o{ ProjectCustomField : "project_id"
    ProjectCustomField }o--|| CustomFieldDefinition : "custom_field_definition_id"

    Project ||--o{ ProjectLinkType : "project_id"
    ProjectLinkType }o--|| LinkTypeDefinition : "link_type_definition_id"

    Project ||--o{ TypeHierarchyRule : "project_id"
    TypeHierarchyRule }o--|| IssueTypeDefinition : "parent_type_id"
    TypeHierarchyRule }o--|| IssueTypeDefinition : "child_type_id"

    Project ||--|| ProjectIssueSequence : "project_id"

    Project ||--o{ Issue : "project_id"
    Issue }o--|| IssueTypeDefinition : "issue_type_id"
    Issue }o--|| Status : "status_id"
    Issue }o--o| Issue : "parent_id"

    Issue ||--o{ CustomFieldValue : "issue_id"
    CustomFieldValue }o--|| CustomFieldDefinition : "custom_field_definition_id"

3.3 Interface Impact (Before → After)¶

4. Implementation Details¶

4.1 Entity Specifications¶

Status (modified)¶

Index: (space_id, name) UNIQUE — status names unique within a Space.

Workflow (modified)¶

Index: (space_id, name) UNIQUE.

WorkflowTransition (unchanged)¶

Constraint: UNIQUE(workflow_id, source_status_id, target_status_id).

IssueTypeDefinition (renamed from IssueType, modified)¶

Index: (space_id, name) UNIQUE.

CustomFieldDefinition (new)¶

Index: (space_id, name) UNIQUE.

LinkTypeDefinition (new)¶

Index: (space_id, name) UNIQUE.

Project (modified)¶

Constraint: CHECK (key ~ '^[A-Z][A-Z0-9]{1,9}$').

ProjectIssueType (new)¶

Constraint: UNIQUE(project_id, issue_type_definition_id).

ProjectCustomField (new)¶

Constraint: UNIQUE(project_id, custom_field_definition_id).

ProjectLinkType (new)¶

Constraint: UNIQUE(project_id, link_type_definition_id).

TypeHierarchyRule (new)¶

Constraint: UNIQUE(project_id, parent_type_id, child_type_id).

ProjectIssueSequence (new)¶

Issue (modified)¶

Indexes: - UNIQUE(project_id, issue_number) — uniqueness within project - (project_id, deleted_at) — filtered queries for active issues - (project_id, rank) — ordered listing - (assignee_id, deleted_at) — "my issues" queries - (sprint_id) — sprint board queries - (parent_id) — child issue lookups

CustomFieldValue (new)¶

Constraint: UNIQUE(issue_id, custom_field_definition_id) — one value per field per issue.

4.2 Issue Key Generation¶

The issue key is a denormalized stored column combining the project key and a sequential issue number:

issue_key = project.key || '-' || issue_number

Example: PROJ-123, BUG-1, PLATFORM-4567.

Atomic Number Generation¶

-- Acquire advisory lock scoped to the project, auto-released at transaction end
SELECT pg_advisory_xact_lock(project_id);

-- Atomically increment and retrieve the next number
UPDATE issues.project_issue_sequence
SET next_number = next_number + 1
WHERE project_id = :project_id
RETURNING next_number - 1 AS issue_number;

The advisory lock prevents concurrent transactions from obtaining the same number. The lock is scoped to the transaction and released automatically on commit or rollback.

The issue_key is computed in the application layer and stored once at creation time. Neither issue_key nor issue_number may ever be updated.

4.3 Integration Points¶

• loryth-domain: Issue extends BaseAggregate; child entities extend BaseEntity + AuditMixin
• loryth-persistence: BaseRepository for all repositories; @transactional for service methods; column helpers (mapped_uuid(), mapped_enum(), mapped_jsonb(), mapped_primary_key())
• loryth-common: DI registration via DiContainerBuilder; Page for pagination
• loryth-fastapi: SuccessResponse wrapper; PageModel for paginated responses

4.4 Error Handling and Edge Cases¶

5. Semantics¶

Invariants¶

Naming Conventions¶

• Database tables use the issues schema (established in v00-setup.sql)
• Column names use snake_case
• Enum type names use snake_case (e.g., status_category, issue_priority, custom_field_type)
• Foreign key columns end with _id
• Timestamp columns use TIMESTAMPTZ

6. Examples¶

6.1 Creating a Project with Configuration¶

# Service layer
def create_project(cmd: CreateProjectCommand) -> ProjectDto:
    project = Project(
        space_id=cmd.space_id,
        key=cmd.key.upper(),
        name=cmd.name,
        description=cmd.description,
        lead_id=cmd.lead_id,
    )
    project = project_repo.save(project)

    # Initialize issue number sequence
    sequence = ProjectIssueSequence(project_id=project.id)
    sequence_repo.save(sequence)

    # Enable default issue types
    for type_def in type_def_repo.find_by_space(cmd.space_id):
        project_type = ProjectIssueType(
            project_id=project.id,
            issue_type_definition_id=type_def.id,
        )
        project_type_repo.save(project_type)

    return ProjectDto.map(project)

6.2 Creating an Issue with Key Generation¶

# Service layer
@transactional
def create_issue(cmd: CreateIssueCommand) -> IssueDto:
    project = project_repo.get_by_key(cmd.project_key)

    # Atomic issue number generation
    issue_number = sequence_repo.next_number(project.id)
    issue_key = f"{project.key}-{issue_number}"

    issue = Issue(
        project_id=project.id,
        issue_number=issue_number,
        issue_key=issue_key,
        issue_type_id=cmd.issue_type_id,
        status_id=cmd.initial_status_id,
        reporter_id=cmd.reporter_id,
        title=cmd.title,
        description=cmd.description,
        priority=cmd.priority or Priority.MEDIUM,
        rank=rank_service.generate_last_rank(project.id),
    )

    if cmd.parent_id:
        validate_hierarchy(project.id, cmd.parent_id, cmd.issue_type_id)
        validate_no_cycle(cmd.parent_id, issue)
        issue.parent_id = cmd.parent_id

    issue = issue_repo.save(issue)
    return IssueDto.map(issue)

7. Test Matrix¶

7.1 Unit Coverage¶

• Project key validation (format, uniqueness)
• Issue key generation (format, immutability)
• Type hierarchy rule validation (allowed/disallowed combinations)
• Cycle detection in parent-child relationships
• Custom field type validation (correct value column populated)
• Priority enum mapping

7.2 Integration Coverage¶

• Full issue creation flow with key generation and advisory lock
• Project configuration (enable/disable types, fields, link types)
• Custom field value CRUD with type enforcement
• Soft delete and restore round-trip
• Concurrent issue creation produces sequential, gap-free numbers
• Space-scoped catalog queries return only matching Space data

7.3 Regression Coverage¶

• Existing issue CRUD endpoints continue to work after schema migration
• Workflow transition validation unchanged
• Event sourcing (outbox) continues to function

8. Acceptance Criteria¶

• All existing migrations (v00–v05) continue to apply cleanly
• New migrations create all specified tables and modify existing ones
• Issue keys are generated atomically and are globally unique
• Space-scoped catalog queries filter by space_id
• Custom field values enforce type constraints
• Parent-child hierarchy respects type rules and prevents cycles
• Soft-deleted issues are excluded from standard queries
• All new entities follow BaseAggregate/BaseEntity patterns

9. Rollout and Migration¶

9.1 Phased Plan¶

Phase 1a: Schema migration - Add space_id to existing tables (nullable initially for backfill) - Create new enum types (status_category, issue_priority, custom_field_type) - Create new tables (all configuration and custom field tables)

Phase 1b: Data backfill - Assign a default Space UUID to all existing records - Set status.category based on status names (heuristic) - Generate project keys from project names - Backfill issue_key and issue_number for existing issues - Make space_id NOT NULL after backfill

Phase 1c: Application updates - Update aggregates, repositories, services, and API schemas - Update DI configuration - Add new API endpoints for configuration management

9.2 Migration Strategy¶

New SQL migration files will be numbered sequentially from v06. Each migration is forward-only (no rollback DDL). Backfill operations run as part of the migration to ensure data consistency before NOT NULL constraints are applied.

10. Risks and Mitigations¶

11. Progress¶

• Write SQL migrations (v06–v08)
• Update SQLAlchemy models
• Update repositories
• Update domain services
• Update API schemas and routers
• Update DI configuration
• Write integration tests
• Data backfill for existing records

12. Changelog¶

13. Glossary¶