Project Issues Schema

Project issue records provide a shared lifecycle model with typed subcategories and optional project association.

Tables

issues

Parent issue object.

• Primary key: id
• Required fields: tenant_id, issue_type, title, status, priority, opened_at
• Optional fields: project_id, description, closed_at
• Foreign keys:
  • tenant_id -> tenants.id
  • project_id -> projects.id (ON DELETE SET NULL)

Allowed issue_type values:

• known_problem
• service_request
• change
• post_incident_analysis
• incident
• repair

Allowed status values:

• open
• in_progress
• resolved
• cancelled

Allowed priority values:

• low
• normal
• high
• critical

Checks:

• non-empty title
• closed_at is null or closed_at >= opened_at

asset_repairs (issue subtype bridge)

Repair subtype details live in Assets but link one-to-one to parent issue.

• Link field: issue_id
• Foreign key: issue_id -> issues.id (ON DELETE CASCADE)
• Unique index enforces one repair detail row per issue

Relationship Notes

• Issues provide a single shared lifecycle model across issue subtypes.
• Repair subtype records keep asset-specific fields while inheriting issue lifecycle state.