⌘K
Proposal Demo
Requirements

TeamFlow Data Model

1. Design Goals

The data model must:

  • Preserve all approved requirement versions
  • Link work and tests to exact versions
  • Support traceable approvals
  • Prevent silent edits
  • Support auditability
  • Support project-level authorization
  • Remain understandable and maintainable

2. Core Entity Overview

Organization
 ├── User
 ├── Team
 └── Project
      ├── ProjectMember
      ├── Requirement
      │    ├── RequirementVersion
      │    │    ├── AcceptanceCriterion
      │    │    ├── RequirementApproval
      │    │    ├── Task
      │    │    └── TestCase
      │    ├── Clarification
      │    ├── DecisionLog
      │    └── ChangeRequest
      │         └── ChangeImpact
      ├── Sprint
      ├── Epic
      ├── Defect
      ├── Release
      └── AuditLog

3. Core Tables

Organization

  • id (UUID, primary key)
  • code (string, unique)
  • name (string)
  • status (ACTIVE, INACTIVE)
  • timezone (string)
  • createdAt, updatedAt (timestamp)

User

  • id (UUID, primary key)
  • organizationId (UUID, foreign key)
  • username (string, unique within organization)
  • email (string)
  • displayName (string)
  • passwordHash (string) or external identity reference
  • status (ACTIVE, INACTIVE, LOCKED)
  • lastLoginAt (timestamp)
  • version (integer for optimistic locking)
  • createdAt, updatedAt (timestamp)

Project

  • id (UUID, primary key)
  • organizationId (UUID, foreign key)
  • code (string, unique within organization)
  • name (string)
  • description (text)
  • status (ACTIVE, ARCHIVED)
  • startDate, targetEndDate (date)
  • createdBy (UUID, foreign key to User)
  • createdAt, updatedAt (timestamp)

Requirement

  • id (UUID, primary key)
  • projectId (UUID, foreign key)
  • requirementNumber (string, e.g., REQ-PRJ-0042)
  • currentApprovedVersionId (UUID, foreign key, nullable)
  • title (string)
  • status (DRAFT, SUBMITTED, APPROVED, IN_DEVELOPMENT, etc.)
  • priority (LOW, MEDIUM, HIGH, CRITICAL)
  • targetRelease (string, nullable)
  • createdBy (UUID, foreign key)
  • createdAt, updatedAt (timestamp)

RequirementVersion

  • id (UUID, primary key)
  • requirementId (UUID, foreign key)
  • versionNumber (string, e.g., 1.0, 1.1, 2.0)
  • versionStatus (DRAFT, APPROVED, SUPERSEDED)
  • businessObjective (text)
  • description (text)
  • actors (text)
  • mainWorkflow (text)
  • businessRules (text)
  • assumptions (text)
  • dependencies (text)
  • outOfScope (text)
  • approvalStatus (PENDING, APPROVED, REJECTED)
  • createdBy (UUID, foreign key)
  • createdAt (timestamp)
  • approvedAt (timestamp, nullable)
  • approvedBy (UUID, foreign key, nullable)

RequirementApproval

  • id (UUID, primary key)
  • requirementVersionId (UUID, foreign key)
  • reviewStage (BA_REVIEW, PM_REVIEW, TECHNICAL_REVIEW, QA_REVIEW)
  • reviewer (UUID, foreign key to User)
  • decision (APPROVE, REJECT, REQUEST_CHANGES, ON_HOLD)
  • comment (text, required for rejection)
  • decidedAt (timestamp)

ChangeRequest

  • id (UUID, primary key)
  • requirementVersionId (UUID, foreign key)
  • status (DRAFT, SUBMITTED, IMPACT_ANALYSIS_REQUIRED, UNDER_REVIEW, APPROVED, DEFERRED, REJECTED, CLOSED)
  • title (string)
  • requestedChange (text)
  • businessReason (text)
  • urgency (LOW, MEDIUM, HIGH, CRITICAL)
  • consequenceOfNotImplementing (text)
  • requestedBy (UUID, foreign key)
  • desiredRelease (string, nullable)
  • createdAt, updatedAt (timestamp)

ChangeImpact

  • id (UUID, primary key)
  • changeRequestId (UUID, foreign key)
  • impactType (BUSINESS, PROJECT, TECHNICAL, QA)
  • analyzedBy (UUID, foreign key to User)
  • effortEstimate (integer, in minutes)
  • riskLevel (LOW, MEDIUM, HIGH, CRITICAL)
  • affectedModules (text)
  • notes (text)
  • analyzedAt (timestamp)

Task

  • id (UUID, primary key)
  • projectId (UUID, foreign key)
  • requirementVersionId (UUID, foreign key, nullable)
  • epicId (UUID, foreign key, nullable)
  • title (string)
  • description (text)
  • status (BACKLOG, READY, IN_PROGRESS, BLOCKED, CODE_REVIEW, READY_FOR_QA, DONE, REOPENED, CANCELLED)
  • priority (LOW, MEDIUM, HIGH, CRITICAL)
  • estimatedMinutes (integer)
  • actualMinutes (integer, nullable)
  • assignedTo (UUID, foreign key, nullable)
  • sprintId (UUID, foreign key, nullable)
  • createdBy (UUID, foreign key)
  • createdAt, updatedAt (timestamp)

TestCase

  • id (UUID, primary key)
  • requirementVersionId (UUID, foreign key)
  • title (string)
  • preconditions (text)
  • steps (text)
  • expectedResult (text)
  • status (DRAFT, READY, REVIEW_REQUIRED, DEPRECATED)
  • createdBy (UUID, foreign key)
  • createdAt, updatedAt (timestamp)

AuditLog

  • id (UUID, primary key)
  • organizationId (UUID, foreign key)
  • projectId (UUID, foreign key, nullable)
  • userId (UUID, foreign key)
  • entityType (REQUIREMENT, CHANGE_REQUEST, TASK, TEST, DEFECT, RELEASE, USER, PROJECT)
  • entityId (UUID)
  • action (CREATE, UPDATE, APPROVE, REJECT, SUBMIT, etc.)
  • previousValue (JSON, nullable)
  • newValue (JSON, nullable)
  • reason (text, nullable)
  • traceId (string)
  • timestamp (timestamp)

4. Key Constraints

  • RequirementVersion.id is immutable once APPROVED
  • A Requirement can have only one current approved RequirementVersion
  • RequirementApproval entries are immutable once created
  • AuditLog entries are append-only and immutable
  • Foreign keys enforce referential integrity
  • Project isolation: all project-owned queries must filter by projectId

5. Indexing Strategy

Critical indexes for performance:

  • idx_requirement_project_id — for listing project requirements
  • idx_requirement_status — for status filtering
  • idx_requirement_version_requirement_id — for version history
  • idx_change_request_requirement_version_id — for linked changes
  • idx_task_requirement_version_id — for linked tasks
  • idx_task_assigned_to — for developer task lists
  • idx_audit_log_entity_id — for tracing entity changes
  • idx_audit_log_timestamp — for audit queries

Next: Review the Security Model →