TaskFlow Production Data Model

TaskFlow Production Data Model Assemble the first coherent relational model used by later phases

Phase 10 - Relational Data Modeling Volume 03 - Database Engineering

Architecture milestone

PostgreSQL + Prisma infrastructure

↓

Production-ready relational model

↓

Persistent TaskFlow CRUD

Phase 10 decides what the durable data should mean before Phase 11 writes application behavior against it.

The model we are committing to Figure 1 - TaskFlow production relational model

User

↓

Membership

↓

Workspace

↓

Project

↓

Task

The visual is simplified: Membership connects User <-> Workspace; Workspace owns Projects; Project owns Tasks; Task may reference an assignee User.

Model overview Model Core responsibility

User Application identity/profile reference

Workspace Tenant/collaboration boundary

Membership User participation and workspace-scoped role

Project Container of related work inside workspace

Task Work item inside project

Prisma schema snapshot enum MembershipRole { OWNER ADMIN MEMBER }

enum TaskStatus { TODO IN_PROGRESS DONE CANCELED }

enum TaskPriority { LOW

MEDIUM HIGH URGENT }

model User { id String @id @default(uuid()) email String @unique displayName String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

memberships Membership[] assignedTasks Task[] @relation("TaskAssignee") }

model Workspace { id String @id @default(uuid()) name String slug String @unique createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

memberships Membership[] projects Project[] }

model Membership { id String @id @default(uuid()) userId String workspaceId String role MembershipRole @default(MEMBER) joinedAt DateTime @default(now())

user User @relation(fields: [userId], references: [id], onDelete: Cascade) workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade)

@@unique([workspaceId, userId])
@@index([userId])
}

model Project { id String @id @default(uuid()) workspaceId String name String description String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade) tasks Task[]

@@index([workspaceId])
}

model Task { id String @id @default(uuid()) projectId String assigneeId String? title String description String? status TaskStatus @default(TODO) priority TaskPriority @default(MEDIUM) dueAt DateTime? completedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

project Project @relation(fields: [projectId], references: [id], onDelete: Cascade) assignee User? @relation("TaskAssignee", fields: [assigneeId], references: [id], onDelete: SetNull)

@@index([projectId, status])

@@index([assigneeId, status])
}

Important service-layer invariant The schema can prove that assigneeId references a real User, but it does not prove that the user belongs to the task project's workspace. Later Todos/Tasks service logic must verify workspace membership before assignment. This is a perfect example of database integrity plus application business rules working together.

Why User exists before Supabase Auth integration Supabase Auth identity will arrive later. TaskFlow still benefits from an application-facing User/Profile model that can participate in relational data. Phase 17 will define how authenticated identity maps to this model without trusting browser-supplied user IDs. The production model is intentionally capable but not maximal. Comments, attachments, activity events, labels, invitations, notifications and audit logs will be added only when later product phases create concrete requirements.