Database Schema

interface User {
  _id: ObjectId
  name: string
  email: string
  password: string          // bcrypt hashed
  avatar?: string
  emailVerified: boolean
  emailVerificationToken?: string
  emailVerificationExpires?: Date
  resetPasswordToken?: string
  resetPasswordExpires?: Date
  hasCompletedOnboarding: boolean
  onboardingData?: {
    companyName: string
    companySize: string
    role: string
    useCase: string
    firstWorkspace: string
    companyLogo?: string
  }
  createdAt: Date
  updatedAt: Date
}

db.users.createIndex({ email: 1 }, { unique: true })
db.users.createIndex({ emailVerificationToken: 1 })
db.users.createIndex({ resetPasswordToken: 1 })

interface Workspace {
  _id: ObjectId
  name: string
  icon: string
  color: string
  ownerId: ObjectId
  createdAt: Date
  updatedAt: Date
}

interface Board {
  _id: ObjectId
  name: string
  workspaceId: ObjectId
  description?: string
  groups: Group[]
  columns: Column[]
  members?: BoardMember[]
  background?: {
    type: 'gradient' | 'solid' | 'none'
    value: string
  }
  theme?: 'light' | 'dark'
  viewDensity?: 'compact' | 'comfortable' | 'spacious'
  createdAt: Date
  updatedAt: Date
}
 
interface Group {
  id: string
  name: string
  color: string       // Hex color
  collapsed?: boolean
}
 
interface Column {
  id: string
  name: string
  type: ColumnType
  width?: number
  visible?: boolean
  required?: boolean
  defaultValue?: string
  options?: string[]     // For dropdown type
  colorRules?: { value: string; color: string }[]
  description?: string
}
 
type ColumnType = 
  | "status" | "person" | "date" | "priority" 
  | "text" | "number" | "timeline" | "timeTracking"
  | "labels" | "dropdown" | "link" | "email"
  | "phone" | "checkbox" | "rating" | "progress"
  | "location" | "files"
 
interface BoardMember {
  userId: string
  role: "viewer" | "editor" | "admin"
  invitedBy: string
  invitedAt: string
}

interface Task {
  _id: ObjectId
  boardId: ObjectId
  groupId: string
  name: string
  description?: string
  status: "done" | "working" | "stuck" | "pending" | "review"
  priority: "critical" | "high" | "medium" | "low"
  assignees: string[]
  columnValues?: Record<string, any>  // Dynamic column data
  dueDate?: Date
  startDate?: Date
  endDate?: Date
  comments: Comment[]
  subtasks: Subtask[]
  timeEntries: TimeEntry[]
  dependencies: string[]
  files: FileAttachment[]
  createdAt: Date
  updatedAt: Date
}
 
interface Comment {
  id: string
  userId: string
  content: string
  mentions?: string[]
  createdAt: Date
}
 
interface Subtask {
  id: string
  name: string
  completed: boolean
  subtasks?: Subtask[]  // Nested subtasks
}
 
interface TimeEntry {
  id: string
  taskId: string
  userId: string
  startTime: Date
  endTime?: Date
  duration: number      // In seconds
  description?: string
}
 
interface FileAttachment {
  id: string
  name: string
  type: string
  size: number
  url: string
  uploadedBy: string
  createdAt: Date
}

db.tasks.createIndex({ boardId: 1 })
db.tasks.createIndex({ groupId: 1 })
db.tasks.createIndex({ assignees: 1 })
db.tasks.createIndex({ dueDate: 1 })
db.tasks.createIndex({ status: 1 })

interface Notification {
  _id: ObjectId
  userId: ObjectId          // Recipient
  senderId?: ObjectId       // Who triggered it
  type: "mention" | "assignment" | "update" | "comment" 
      | "automation" | "due_date" | "invitation"
  message: string
  taskId?: ObjectId
  boardId?: ObjectId
  read: boolean
  createdAt: Date
}

interface ActivityLogEntry {
  _id: ObjectId
  boardId?: ObjectId
  taskId?: ObjectId
  userId: ObjectId
  action: "created" | "updated" | "deleted" | "commented" 
        | "assigned" | "status_changed" | "moved" | "invited"
  details: string
  previousValue?: string
  newValue?: string
  createdAt: Date
}

interface Automation {
  _id: ObjectId
  boardId: ObjectId
  name: string
  trigger: {
    type: "status_change" | "date_arrives" | "item_created" 
        | "person_assigned" | "priority_change"
    config: {
      fromStatus?: string
      toStatus?: string
      fromPriority?: string
      toPriority?: string
      daysBeforeDue?: number
      groupId?: string
    }
  }
  actions: {
    type: "notify" | "change_status" | "assign_person" 
        | "move_to_group" | "create_item" | "send_email"
    config: {
      status?: string
      personId?: string
      groupId?: string
      message?: string
      notifyPersonIds?: string[]
    }
  }[]
  enabled: boolean
  runCount: number
  createdAt: Date
}