Work Item Model

The WorkItem model represents tasks and work items synced from Plane.so, supporting hierarchical relationships and comprehensive task management.

Schema Definition

model WorkItem {
  id            String     @id @default(uuid())
  planeId       String     @unique
  name          String?
  description   String?
  state         String?
  priority      String?
  assignees     String[]
  labels        String[]
  dueDate       DateTime?  @map("due_date")
  startDate     DateTime?  @map("start_date")
  estimatePoint Int?       @map("estimate_point")
  parentId      String?    @map("parent_id")
  projectId     String
  createdAt     DateTime   @default(now())
  updatedAt     DateTime   @updatedAt
  sequenceId    Int?
  parent        WorkItem?  @relation("SubWorkItems", fields: [parentId], references: [id])
  subWorkItems  WorkItem[] @relation("SubWorkItems")
  project       Project    @relation(fields: [projectId], references: [id], onDelete: Cascade)

  @@map("work_items")
}

Field Descriptions

Primary Identification

Field	Type	Description
id	String	UUID primary key
planeId	String	Unique Plane.so work item identifier

Basic Information

Field	Type	Description
name	String?	Work item title/name
description	String?	Detailed description
state	String?	Current state (todo, in_progress, done, cancelled)
priority	String?	Priority level (urgent, high, medium, low)

Assignment and Organization

Field	Type	Description
assignees	String[]	Array of assignee identifiers
labels	String[]	Array of label names

Scheduling

Field	Type	Description
dueDate	DateTime?	Due date for completion
startDate	DateTime?	Planned start date

Estimation

Field	Type	Description
estimatePoint	Int?	Story points or time estimate

Hierarchy

Field	Type	Description
parentId	String?	Reference to parent work item (for subtasks)
sequenceId	Int?	Order within parent or project

Relations

Field	Type	Description
projectId	String	Reference to Project.id

Metadata

Field	Type	Description
createdAt	DateTime	Work item sync timestamp
updatedAt	DateTime	Last update timestamp

Relationships

Belongs To

• project: Project - The project this work item belongs to (Cascade delete)
• parent: WorkItem? - Parent work item (for hierarchical tasks)

One-to-Many

• subWorkItems: WorkItem[] - Child work items (subtasks)

Usage Examples

Create Synced Work Item

const workItem = await prisma.workItem.create({
  data: {
    planeId: "plane-issue-123",
    name: "Implement user authentication",
    description: "Add login and registration functionality",
    state: "in_progress",
    priority: "high",
    assignees: ["user-456", "user-789"],
    labels: ["authentication", "frontend"],
    dueDate: new Date("2024-02-01"),
    estimatePoint: 8,
    projectId: "project-123"
  }
});

Create Subtask

const subtask = await prisma.workItem.create({
  data: {
    planeId: "plane-issue-124",
    name: "Create login form",
    description: "Design and implement login UI",
    state: "todo",
    priority: "medium",
    parentId: "work-item-123", // Reference to parent
    projectId: "project-123"
  }
});

Update Work Item Status

await prisma.workItem.update({
  where: { planeId: "plane-issue-123" },
  data: {
    state: "done",
    updatedAt: new Date()
  }
});

Get Work Items with Hierarchy

const workItems = await prisma.workItem.findMany({
  where: { projectId: "project-123" },
  include: {
    subWorkItems: {
      orderBy: { sequenceId: "asc" }
    },
    parent: true
  }
});

Get Overdue Items

const overdueItems = await prisma.workItem.findMany({
  where: {
    dueDate: {
      lt: new Date()
    },
    state: {
      not: "done"
    }
  }
});

Hierarchical Queries

Get Root Level Items

const rootItems = await prisma.workItem.findMany({
  where: {
    projectId: "project-123",
    parentId: null // No parent = root level
  },
  orderBy: { sequenceId: "asc" }
});

Get Complete Hierarchy

async function getWorkItemHierarchy(projectId: string) {
  const allItems = await prisma.workItem.findMany({
    where: { projectId },
    include: {
      subWorkItems: {
        orderBy: { sequenceId: "asc" }
      }
    },
    orderBy: { sequenceId: "asc" }
  });

  // Build tree structure
  const itemMap = new Map();
  const rootItems = [];

  allItems.forEach(item => {
    itemMap.set(item.id, { ...item, children: [] });
  });

  allItems.forEach(item => {
    if (item.parentId) {
      const parent = itemMap.get(item.parentId);
      if (parent) {
        parent.children.push(itemMap.get(item.id));
      }
    } else {
      rootItems.push(itemMap.get(item.id));
    }
  });

  return rootItems;
}

Sync Operations

Incremental Sync

async function syncWorkItems(projectId: string, since?: Date) {
  const project = await prisma.project.findUnique({
    where: { id: projectId },
    include: { integration: true }
  });

  const query = since ? `?updated_after=${since.toISOString()}` : '';
  const workItems = await fetchPlaneWorkItems(project.integration, project.planeId, query);

  for (const item of workItems) {
    await prisma.workItem.upsert({
      where: { planeId: item.id },
      update: {
        name: item.name,
        description: item.description,
        state: item.state,
        priority: item.priority,
        assignees: item.assignees || [],
        labels: item.labels || [],
        dueDate: item.due_date ? new Date(item.due_date) : null,
        startDate: item.start_date ? new Date(item.start_date) : null,
        estimatePoint: item.estimate_point,
        parentId: item.parent_id,
        sequenceId: item.sequence_id,
        updatedAt: new Date()
      },
      create: {
        planeId: item.id,
        name: item.name,
        description: item.description,
        state: item.state,
        priority: item.priority,
        assignees: item.assignees || [],
        labels: item.labels || [],
        dueDate: item.due_date ? new Date(item.due_date) : null,
        startDate: item.start_date ? new Date(item.start_date) : null,
        estimatePoint: item.estimate_point,
        parentId: item.parent_id,
        projectId: projectId,
        sequenceId: item.sequence_id
      }
    });
  }
}

Analytics and Reporting

Work Item Statistics

async function getWorkItemStats(projectId: string) {
  const stats = await prisma.workItem.groupBy({
    by: ['state', 'priority'],
    where: { projectId },
    _count: true
  });

  const overdueCount = await prisma.workItem.count({
    where: {
      projectId,
      dueDate: { lt: new Date() },
      state: { not: 'done' }
    }
  });

  return {
    byState: stats.reduce((acc, stat) => {
      acc[stat.state] = stat._count;
      return acc;
    }, {}),
    byPriority: stats.reduce((acc, stat) => {
      acc[stat.priority] = (acc[stat.priority] || 0) + stat._count;
      return acc;
    }, {}),
    overdue: overdueCount
  };
}

Burndown Data

async function getBurndownData(projectId: string, startDate: Date, endDate: Date) {
  const workItems = await prisma.workItem.findMany({
    where: {
      projectId,
      createdAt: {
        gte: startDate,
        lte: endDate
      }
    },
    select: {
      createdAt: true,
      updatedAt: true,
      state: true,
      estimatePoint: true
    }
  });

  // Process data for burndown chart
  // Implementation depends on reporting requirements
}

Performance Considerations

Indexing

• Unique index on planeId for sync operations
• Index on projectId for project filtering
• Index on parentId for hierarchy queries
• Index on state for status filtering
• Index on dueDate for deadline queries
• Index on updatedAt for incremental sync

Query Optimization

• Use select to limit returned fields
• Include relations only when needed
• Use pagination for large result sets
• Cache frequently accessed work items

Bulk Operations

// Bulk status update
await prisma.workItem.updateMany({
  where: {
    projectId: "project-123",
    state: "todo"
  },
  data: {
    state: "in_progress"
  }
});

Best Practices

Data Consistency

• Always sync through Plane API
• Validate data before saving
• Handle circular references in hierarchy
• Implement proper error handling

Hierarchy Management

• Limit hierarchy depth (max 3-4 levels)
• Maintain sequence IDs for ordering
• Handle orphaned items gracefully
• Implement move operations carefully

Performance

• Use batch operations for bulk updates
• Implement pagination for large lists
• Cache hierarchy structures
• Monitor query performance

Security

• Respect Plane permissions
• Filter items based on user access
• Log access for auditing
• Implement rate limiting