Projects and backlog
Projects, Components, IssueTypes, Issues, Backlogs.
- Projects
- Components
- IssueTypes
- Issues
- Backlogs
Data model
Records and relationships behind PM for IT Projects.
34 tables · 68 relationships
How the model is organised
Sprints and commitments
Sprints, SprintCommitments, SprintOutcomes, Issues, TeamVelocity.
- Sprints
- SprintCommitments
- SprintOutcomes
- Issues
- TeamVelocity
Boards and workflow
Boards, BoardColumns, BoardColumnStatuses, WipLimits, Workflows, WorkflowTransitions, TransitionApprovals.
- Boards
- BoardColumns
- BoardColumnStatuses
- WipLimits
- Workflows
- WorkflowTransitions
- TransitionApprovals
Bugs and verification
Issues, IssueLinks, IssueComments, IssueAttachments, TransitionApprovals.
- Issues
- IssueLinks
- IssueComments
- IssueAttachments
- TransitionApprovals
Code and releases
PullRequestLinks, IntegrationEvents, Versions, Issues, IssueLabels.
- PullRequestLinks
- IntegrationEvents
- Versions
- Issues
- IssueLabels
Roadmaps and delivery insights
Epics, EpicProgress, Milestones, Burndowns, TeamVelocity, SprintOutcomes.
- Epics
- EpicProgress
- Milestones
- Burndowns
- TeamVelocity
- SprintOutcomes
Keys and references
Issue child records inherit the same project and workflow. TransitionApprovals bind to the requested transition and IssueRevision. SprintCommitments and SprintOutcomes preserve one immutable occurrence per sprint and issue. IntegrationEvents retains provider and operation identity.
Migration order
- Map Users, Teams, TeamMemberships and explicit project roles to verified principals.
- Load Projects, Components, IssueTypes and reviewed versioned workflow definitions.
- Reconcile Boards, effective WIP overrides and current backlog ownership.
- Import Issues with same-project dependencies and protected attachment audiences.
- Load sprint commitments, closure outcomes and release evidence without deriving history from current issue assignments.
- Configure provider connections; verify QA, concurrent WIP, close/rollover and release-scope cases.
Tables
34 tables
ProjectsThe agile project container — one row per product, service, ormaster
| Field | Type | Required | Details |
|---|---|---|---|
Key | text | yes | Short uppercase prefix used in IssueKey (e.g. AUTH) |
Name | text | yes | |
Description | text | Markdown | |
ProjectType | select | yes | Software, Infrastructure, InternalIT, Research, Other |
Lead | reference | yes | → Users (Project Lead) |
ProductOwner | reference | → Users | |
ScrumMaster | reference | → Users | |
Team | reference | → Teams (default squad) | |
Status | select | yes | Planning, Active, OnHold, Archived |
StandupTimezone | text | IANA tz for daily-standup-digest schedule | |
NotifyTo | text | Slack channel / Teams channel / email distribution | |
IssueRolloverRule | select | yes | MoveToBacklog, MoveToNextSprint |
AutoTransitionOnMerge | toggle | Auto-flip linked Issue on git PR merge | |
StartDate | date | ||
Active | toggle | yes | |
EstimateUnit | select | yes | Points, Hours |
ComponentsSub-modules within a Project — `auth-service`, `billing-api`, Line item of Projects.line item
| Field | Type | Required | Details |
|---|---|---|---|
Project | reference | yes | → Projects |
Sequence | number | yes | |
ComponentCode | text | yes | |
Name | text | yes | |
Description | text | ||
Lead | reference | → Users (component owner) | |
DefaultAssignee | reference | → Users | |
Active | toggle | yes |
IssueTypesThe taxonomy of work-item kinds — Story, Bug, Task, Epic, SubTask,master
| Field | Type | Required | Details |
|---|---|---|---|
TypeCode | text | yes | |
Name | text | yes | |
Category | select | yes | Standard, Bug, Subtask, Epic |
IconKey | text | Icon glyph identifier for UI | |
Description | text | ||
RequiresEstimate | toggle | If true, Issues of this type cannot move past ToDo without an Estimate | |
Active | toggle | yes |
WorkflowsIssue-lifecycle workflow definitions — one row per Project permaster
| Field | Type | Required | Details |
|---|---|---|---|
WorkflowCode | text | yes | |
Name | text | yes | |
Project | reference | → Projects (null = global default) | |
IssueType | reference | yes | → IssueTypes |
InitialStatus | text | yes | Name of the WorkflowStatuses row that newly-created Issues land in |
Description | text | ||
Version | number | yes | |
Active | toggle | yes | |
PreparedBy | reference | → Users | |
AdminApprovedBy | reference | → Users; independent configuration reviewer | |
LeadApprovedBy | reference | → Users; scoped Project Lead co-signer |
WorkflowStatusesThe states a Workflow allows — ToDo, InProgress, InReview, Done, Line item of Workflows.line item
| Field | Type | Required | Details |
|---|---|---|---|
Workflow | reference | yes | → Workflows |
Sequence | number | yes | |
StatusName | text | yes | e.g. ToDo, InProgress, InReview, Done, Blocked, Cancelled |
Category | select | yes | ToDo, InProgress, Done, Blocked, Cancelled |
IsTerminal | toggle | If true, no further transitions allowed (Done / Cancelled) | |
ColorHex | text | Display color for board chips |
WorkflowTransitionsThe allowed status moves — A → B with optional gating predicate Line item of Workflows.line item
| Field | Type | Required | Details |
|---|---|---|---|
Workflow | reference | yes | → Workflows |
Sequence | number | yes | |
FromStatus | reference | yes | → WorkflowStatuses |
ToStatus | reference | yes | → WorkflowStatuses |
RequiredApproverRole | select | None, Admin, ProjectLead, ScrumMaster, QA, Any | |
GatePredicate | text | Free-text precondition description (e.g. "Estimate must be set") | |
OnTransitionAction | select | None, EmailAssignee, RecomputeCurrentSprintProgress, FlipResolvedAt, UnblockLinks |
BoardsKanban or Scrum board per Project. A Project can have multiplemaster
| Field | Type | Required | Details |
|---|---|---|---|
BoardCode | text | yes | |
Name | text | yes | |
Project | reference | yes | → Projects |
BoardType | select | yes | Scrum, Kanban, Triage, Release |
FilterJQL | text | Optional saved query that scopes which Issues appear on the board | |
Owner | reference | yes | → Users (Scrum Master typically) |
SwimlaneBy | select | None, Assignee, Epic, Component, Priority | |
Active | toggle | yes | |
ValidatedFilter | longtext | Allowlisted parsed filter expression; FilterJQL is a display/source expression, never executable code |
BoardColumnsThe columns on a Board, mapped to one or more WorkflowStatuses. Line item of Boards.line item
| Field | Type | Required | Details |
|---|---|---|---|
Board | reference | yes | → Boards |
Sequence | number | yes | |
ColumnName | text | yes | e.g. To Do, In Progress, In Review, Done |
WipLimit | number | Max in-flight Issues; null = unlimited | |
MinIssues | number | Optional lower-bound warning | |
CurrentCount | number | Server-derived count of applicable nonterminal issues after validated board filter | |
EffectiveLimit | number | Current valid WipLimits override, otherwise WipLimit; null means unlimited | |
FillPercent | number | CurrentCount / EffectiveLimit * 100 when EffectiveLimit > 0; otherwise null |
BoardColumnStatusesThe WorkflowStatuses mapped to a BoardColumn — one row per Line item of BoardColumns.line item
| Field | Type | Required | Details |
|---|---|---|---|
BoardColumn | reference | yes | → BoardColumns |
Sequence | number | yes | |
WorkflowStatus | reference | yes | → WorkflowStatuses |
WipLimitsPer-Board per-column WIP-limit override and history — admin-tunablemaster
| Field | Type | Required | Details |
|---|---|---|---|
Board | reference | yes | → Boards |
BoardColumn | reference | yes | → BoardColumns |
EffectiveLimit | number | yes | |
EffectiveFrom | date | yes | |
EffectiveTo | date | ||
ChangedBy | reference | yes | → Users |
Reason | text |
BacklogsThe prioritized work pool per Project — Issues that are not yet onmaster
| Field | Type | Required | Details |
|---|---|---|---|
Project | reference | yes | → Projects |
Name | text | yes | e.g. "AUTH Backlog" |
Owner | reference | yes | → Users (Product Owner) |
GroomingCadence | select | Weekly, BiWeekly, Monthly, AdHoc | |
LastGroomedAt | date | ||
Active | toggle | yes |
SprintsTime-boxed iteration — one row per sprint per Project. Decomposestransaction
| Field | Type | Required | Details |
|---|---|---|---|
SprintNumber | text | yes | e.g. AUTH-S-2026-08 |
Project | reference | yes | → Projects |
Board | reference | → Boards (the Scrum board the sprint runs on) | |
Name | text | yes | e.g. "Sprint 24 — Login Hardening" |
Goal | text | The one-sentence sprint goal | |
StartDate | date | yes | |
EndDate | date | yes | |
Status | select | yes | Future, Active, Closed |
StartedAt | date | Stamped by sprint-start-snapshot | |
ClosedAt | date | Stamped by sprint-end-rollover | |
ScrumMaster | reference | yes | → Users |
CapacityPoints | number | Suggested capacity from velocity-recompute trailing average | |
RetrospectiveNotes | text | Markdown | |
EstimateUnit | select | yes | Points, Hours |
SprintCommitmentsFrozen snapshot of which Issues were on the Sprint at start, with Line item of Sprints.line item
| Field | Type | Required | Details |
|---|---|---|---|
Sprint | reference | yes | → Sprints |
Sequence | number | yes | |
Issue | reference | yes | → Issues |
CommittedEstimate | number | Story-points or hours snapshot at sprint-start | |
CommittedStatus | text | Issue's Status at sprint-start | |
CommittedAssignee | reference | → Users — Assignee at sprint-start | |
CommittedAt | date | yes | |
RolloverDestination | text | Read-only compatibility projection from immutable SprintOutcomes; never a write to this start snapshot |
IssuesThe unit of work — Story, Bug, Task, Epic, SubTask, Spike,transaction
| Field | Type | Required | Details |
|---|---|---|---|
IssueKey | text | yes | e.g. AUTH-123 |
Project | reference | yes | → Projects |
Component | reference | → Components | |
IssueType | reference | yes | → IssueTypes |
Workflow | reference | yes | → Workflows (resolved from Project × IssueType) |
Title | text | yes | |
Description | text | Markdown | |
Status | reference | yes | → WorkflowStatuses |
Priority | select | yes | Lowest, Low, Medium, High, Highest, Critical |
Severity | select | Sev1, Sev2, Sev3, Sev4 — set on Bugs / Incidents | |
PriorityScore | number | Computed by bug-priority-recompute for Bugs | |
ReportedFrequency | number | Count of duplicate IssueLinks pointing at this Issue | |
ReachEstimate | select | None, Some, Many, All — CustomersAffected bucket | |
Reporter | reference | yes | → Users |
Assignee | reference | → Users | |
Sprint | reference | → Sprints (null = on Backlog) | |
BacklogRank | number | Backlog ordering when Sprint is null | |
FixVersion | reference | → Versions (the release this is targeted for) | |
AffectsVersion | reference | → Versions | |
ParentEpic | reference | → Epics | |
ParentIssue | reference | → Issues (self) — for SubTasks | |
Estimate | number | Story-points or hours | |
Remaining | number | Remaining estimate in the project or active sprint EstimateUnit; never implicitly hours in a points project | |
Resolution | select | Fixed, WontFix, Duplicate, CannotReproduce, Done, Incomplete | |
StartedAt | date | Stamped on first move to InProgress | |
ResolvedAt | date | Stamped on first move to Done | |
ReleasedAt | date | Stamped by release-cut-from-version | |
CreatedAt | date | yes | |
UpdatedAt | date | yes | |
DaysSinceUpdate | number | Server-derived elapsed days since UpdatedAt |
IssueLinksTyped dependency / relationship between two Issues. Append-only;transaction
| Field | Type | Required | Details |
|---|---|---|---|
LinkNumber | text | yes | |
FromIssue | reference | yes | → Issues |
ToIssue | reference | yes | → Issues |
LinkType | select | yes | Blocks, IsBlockedBy, Duplicates, IsDuplicatedBy, Relates, Clones, IsClonedBy, ParentOf, ChildOf |
CreatedBy | reference | yes | → Users |
CreatedAt | date | yes | |
Active | toggle | yes |
IssueLabelsFree-form text labels on an Issue (`needs-design`, `tech-debt`, Line item of Issues.line item
| Field | Type | Required | Details |
|---|---|---|---|
Issue | reference | yes | → Issues |
Sequence | number | yes | |
LabelText | text | yes | |
AppliedBy | reference | yes | → Users |
AppliedAt | date | yes |
IssueCommentsThreaded discussion on an Issue. Supports markdown, edit history Line item of Issues.line item
| Field | Type | Required | Details |
|---|---|---|---|
Issue | reference | yes | → Issues |
Sequence | number | yes | |
Author | reference | yes | → Users |
Body | text | yes | Markdown |
ParentComment | reference | → IssueComments (self) | |
PostedAt | date | yes | |
EditedAt | date | ||
Resolved | toggle | ||
Audience | select | Internal, ReporterVisible; default Internal |
IssueAttachmentsFiles attached to an Issue — repro screenshots, log snippets, Line item of Issues.line item
| Field | Type | Required | Details |
|---|---|---|---|
Issue | reference | yes | → Issues |
Sequence | number | yes | |
FileName | text | yes | |
FileURL | text | yes | |
SizeBytes | number | yes | |
MimeType | text | yes | |
UploadedBy | reference | yes | → Users |
UploadedAt | date | yes | |
Audience | select | Internal, ReporterVisible; default Internal |
IssueWorkLogPer-Issue per-day time logged against the Issue. Feeds the Line item of Issues.line item
| Field | Type | Required | Details |
|---|---|---|---|
Issue | reference | yes | → Issues |
Sequence | number | yes | |
User | reference | yes | → Users |
Date | date | yes | |
Hours | number | yes | Field-masked per role (Developer sees own; Scrum Master aggregate) |
Description | text | ||
LoggedAt | date | yes |
PullRequestLinksReferences to git pull requests linked to the Issue. Populated Line item of Issues.line item
| Field | Type | Required | Details |
|---|---|---|---|
Issue | reference | yes | → Issues |
Sequence | number | yes | |
Provider | select | yes | GitHub, GitLab, Bitbucket, Other |
Repo | text | yes | e.g. acme/auth-service |
PRNumber | text | yes | |
Title | text | ||
Status | select | yes | Open, Merged, Closed, Draft |
URL | text | yes | |
OpenedAt | date | ||
MergedAt | date | ||
AuthorHandle | text | ||
TargetBranch | text | Verified provider target branch | |
ChecksStatus | select | Unknown, Pending, Passed, Failed; authenticated provider observation |
IssueHistoryAppend-only audit log of every change to an Issue — statustransaction
| Field | Type | Required | Details |
|---|---|---|---|
HistoryNumber | text | yes | |
Issue | reference | yes | → Issues |
Actor | reference | yes | → Users |
Field | text | yes | The Issue field that changed (Status, Assignee, Estimate, Sprint, ParentEpic, ...) |
BeforeValue | text | ||
AfterValue | text | ||
ChangedAt | date | yes | |
Source | select | yes | UI, Workflow, Webhook, API, BulkUpdate |
VersionsRelease versions per Project (e.g. `AUTH 2.4.0`, `BILL 2026.04`).master
| Field | Type | Required | Details |
|---|---|---|---|
VersionCode | text | yes | |
Project | reference | yes | → Projects |
Name | text | yes | e.g. AUTH 2.4.0 |
Description | text | Release notes header | |
StartDate | date | ||
ReleaseDate | date | ||
Status | select | yes | Unreleased, Released, Archived |
ReleasedBy | reference | → Users | |
ReleaseNotesURL | text | ||
ReleaseEvidence | longtext | Frozen issue scope, gate results and source revision evidence | |
SourceRevision | text | Observed git revision or build identity; not a deployment claim |
EpicsLarge bodies of work that span multiple Sprints — first-classmaster
| Field | Type | Required | Details |
|---|---|---|---|
EpicKey | text | yes | e.g. AUTH-EPIC-7 |
Project | reference | yes | → Projects |
Name | text | yes | |
Description | text | Markdown | |
Owner | reference | yes | → Users (Product Owner) |
Status | select | yes | Discovery, InProgress, Done, Cancelled |
TargetVersion | reference | → Versions | |
StartDate | date | ||
TargetDate | date | ||
Color | select | Gray, Blue, Green, Purple, Orange, Red | |
Active | toggle | yes |
EpicProgressDerived per-Epic rollup — total / done / in-progress / backlogtransaction
| Field | Type | Required | Details |
|---|---|---|---|
Epic | reference | yes | → Epics |
TotalIssues | number | yes | |
DoneIssues | number | yes | |
InProgressIssues | number | yes | |
BacklogIssues | number | yes | |
TotalEstimate | number | ||
CompletedEstimate | number | ||
PercentComplete | formula | yes | fx: (DoneIssues / TotalIssues) * 100 when TotalIssues > 0; otherwise null |
ForecastReleaseDate | date | Projected from velocity + remaining estimate | |
LastComputedAt | date | yes |
MilestonesNon-version delivery checkpoints per Project (alpha cut, beta cut,transaction
| Field | Type | Required | Details |
|---|---|---|---|
MilestoneNumber | text | yes | |
Project | reference | yes | → Projects |
Name | text | yes | |
TargetDate | date | yes | |
ActualDate | date | ||
Status | select | yes | Upcoming, AtRisk, Hit, Missed, Skipped |
Owner | reference | yes | → Users |
LinkedEpic | reference | → Epics | |
Description | text |
BurndownsPer-Sprint daily data points — the X / Y series feeding thetransaction
| Field | Type | Required | Details |
|---|---|---|---|
Sprint | reference | yes | → Sprints |
SnapshotDate | date | yes | |
RemainingEstimate | number | yes | Sum of Issues.Remaining for in-Sprint Issues |
RemainingIssueCount | number | yes | |
IdealRemaining | number | Linear-burndown reference line value | |
ScopeAddedPoints | number | Mid-sprint scope additions (committed-add-after-start) | |
ScopeRemovedPoints | number | Mid-sprint removals |
TeamVelocityPer-Project per-Sprint velocity rollup — committed vs. completedtransaction
| Field | Type | Required | Details |
|---|---|---|---|
Project | reference | yes | → Projects |
Sprint | reference | yes | → Sprints |
CommittedPoints | number | yes | |
CompletedPoints | number | yes | |
Carryover | number | yes | Sum SprintOutcomes.CommittedEstimate where WasCommitted=true and Outcome=Incomplete; added, removed and cancelled scope reported separately |
TrailingThreeAverage | number | Suggested capacity hint | |
ComputedAt | date | yes | |
EstimateUnit | select | yes | Points, Hours |
UsersScoped application users mapped to authenticated platform principals.master
| Field | Type | Required | Details |
|---|---|---|---|
UserNumber | text | yes | |
PrincipalID | text | yes | Unique verified principal within workspace |
FullName | text | yes | |
Email | text | yes | Contact only; never automatic role assignment |
Active | toggle | yes |
TeamsNamed delivery squads; team membership is explicit and scoped.master
| Field | Type | Required | Details |
|---|---|---|---|
TeamNumber | text | yes | |
Name | text | yes | |
Active | toggle | yes |
TeamMembershipsActive user-to-team relationship imported or approved by the workspace administrator.master
| Field | Type | Required | Details |
|---|---|---|---|
Team | reference | yes | → Teams |
User | reference | yes | → Users |
Active | toggle | yes |
ProjectMembershipsExplicit project role grants including stakeholder and reporter audiences; team membership alone does not grant an administrative role.master
| Field | Type | Required | Details |
|---|---|---|---|
Project | reference | yes | → Projects |
User | reference | yes | → Users |
Role | select | yes | ProjectLead, ScrumMaster, Developer, QA, Stakeholder, Reporter |
Active | toggle | yes |
TransitionApprovalsVersion-bound requests for a named workflow decision; the transition service consumes only a valid independent approval.transaction
| Field | Type | Required | Details |
|---|---|---|---|
ApprovalNumber | text | yes | |
Issue | reference | yes | → Issues |
Transition | reference | yes | → WorkflowTransitions |
RequestedBy | reference | yes | → Users |
IssueRevision | number | yes | Exact submitted issue revision |
DecisionBy | reference | → Users; independent of requester and protected work preparer | |
Status | select | yes | Pending, Approved, Rejected, Expired, Applied |
Evidence | longtext | Required verification or other gate evidence | |
RequestedAt | datetime | yes | |
DecisionAt | datetime | ||
WorkflowVersion | number | yes | Exact pinned workflow version reviewed |
AppliedAt | datetime | Set only when the approved transition commits |
SprintOutcomesImmutable per-issue closure observations frozen before rollover; historical velocity never reads a moved or reestimated current issue.transaction
| Field | Type | Required | Details |
|---|---|---|---|
Sprint | reference | yes | → Sprints |
Issue | reference | yes | → Issues |
WasCommitted | toggle | yes | |
CommittedEstimate | number | Preserved start estimate | |
ClosingEstimate | number | Accepted estimate at closure in the sprint unit | |
StatusAtClose | reference | yes | → WorkflowStatuses |
Outcome | select | yes | Completed, Incomplete, Cancelled, Removed |
RecordedAt | datetime | yes | |
RolloverSprint | reference | → Sprints; actual future destination |
IntegrationEventsAuthenticated source event observations and delivery intent outcomes; provider acknowledgments do not supply business approval.transaction
| Field | Type | Required | Details |
|---|---|---|---|
EventNumber | text | yes | |
Source | text | yes | Configured provider and source workspace |
EventID | text | yes | Unique provider event identity |
Project | reference | yes | → Projects |
Issue | reference | → Issues | |
OperationKey | text | yes | Stable inbound/outbound correlation key |
Status | select | yes | Pending, Succeeded, Rejected, Failed, Unknown |
ObservedAt | datetime | yes | |
Receipt | longtext | Redacted verified result |