[CRM][Backend] - Granular Tag Types (Position Tags vs Task Template Tags)

[bderbs30]

[CRM][Backend] - Granular Tag Types (Position Tags vs Task Template Tags)

Description

Right now our Tag model is a single flat table shared across the entire app position tags and task template tags all live in the same pool. We want to split these out into separate, normalized tag tables per entity so that each taggable type has its own distinct set of tags.

Here's what the current Tag model looks like:

model Tag {
  id           String   @id @unique @default(cuid())
  name         String
  orgId        String
  colorHexCode String
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  organization  Organization   @relation(fields: [orgId], references: [id], onDelete: Cascade)
  taskTemplates TaskTemplate[] @relation("TaskTemplateTags")
  positions     Position[]     @relation("PositionTags")

  @@unique([name, orgId])
  @@index([orgId])
}

The ask is to replace this with separate PositionTag and TaskTemplateTag models. Something along these lines:

model PositionTag {
  id           String   @id @unique @default(cuid())
  name         String
  orgId        String
  colorHexCode String
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  organization Organization @relation(fields: [orgId], references: [id], onDelete: Cascade)
  positions    Position[]   @relation("PositionTags")

  @@unique([name, orgId])
  @@index([orgId])
}

model TaskTemplateTag {
  id           String   @id @unique @default(cuid())
  name         String
  orgId        String
  colorHexCode String
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  organization  Organization   @relation(fields: [orgId], references: [id], onDelete: Cascade)
  taskTemplates TaskTemplate[] @relation("TaskTemplateTags")

  @@unique([name, orgId])
  @@index([orgId])
}

You'll also need to update the relation fields on Position and TaskTemplate to point to their respective tag models, and update the Organization model to reference both.

This will ripple into the service layer, Zod schemas, and API routes the current tag service/schema/routes are generic and will need to be split to handle each tag type separately. The relevant files are:

• prisma/schema.prisma
• src/lib/services/tag.service.ts
• src/lib/schemas/tag.schema.ts
• src/lib/api/tags.ts
• src/app/api/tags/route.ts

For reference, Prisma's docs on [table inheritance patterns](https://www.prisma.io/docs/orm/prisma-schema/data-model/table-inheritance) cover the single-table vs multi-table tradeoffs if you want some background reading.

Also, we'd need to migrate / update the seeding info

Let @bderbs30 know if you have any questions

Acceptance Criteria

• The generic Tag model is removed and replaced with PositionTag and TaskTemplateTag models
• Relations on Position, TaskTemplate, and Organization are updated accordingly
• Tag services, Zod schemas, and API routes are split per tag type
• Migration runs cleanly