Skip to content

🗄️ 4. Database & Schema

TaskNexus uses a PostgreSQL database managed by Prisma ORM.

🧬 Schema Overview (prisma/schema.prisma)

The schema is the single source of truth for our data models.

Primary Models

1. User

The central entity.

  • Roles: SUPER_ADMIN, ADMIN, MANAGER, EMPLOYEE, CUSTOMER.
  • Relationships: Owns Projects, Submitted Requirements, Assigned Jobs/Tasks.
  • Security: Stores violationCount and mustChangePassword flags.

2. Project

A container for work, managed by a Manager.

  • Contains multiple Requirements.

3. Requirement

A feature request from a Customer.

  • Status Workflow: DRAFT -> PENDING_REVIEW -> APPROVED -> REJECTED.
  • AI Fields: securityLevel (Low/Med/High), category (Functional/Non-Functional).

4. Job

A technical unit of work derived from a Requirement.

  • e.g., "Implement Login API".
  • Assigned to a single Employee.
  • Has an estimatedEffort (hours).

5. Task

A granular step within a Job.

  • e.g., "Write SQL Query", "Create Unit Test".
  • Tracked on the Kanban board.
  • Relationships: Linked to a Job and optionally a Sprint.
  • Status: BACKLOG, TODO, IN_PROGRESS, REVIEW, DONE.

6. Sprint

A time-boxed period for executing tasks.

  • Managed in the Scrum Manager.
  • Fields: startDate, endDate, status (PLANNING, ACTIVE, COMPLETED).
  • Workflow: Tasks are assigned to a Sprint during planning and tracked until closure.

Audit Models

  • AuditLog: Immutable history of actions.
    • { actor: "UserA", action: "DELETED_PROJECT", target: "ProjectB" }
  • ViolationLog: Records of failed AI safety checks.
  • AccountSuspension: Active and past suspension records.

🛠️ Prisma Workflows

Making Schema Changes

  1. Modify prisma/schema.prisma.
  2. Push Changes (Development): 
    npx prisma db push
    Warning: This may reset data if there are breaking changes.
  3. Generate Client: 
    npx prisma generate
    Next.js requires restarting the dev server after this.

Viewing Data

Use Prisma Studio, a built-in GUI. 

npx prisma studio
Opens on http://localhost:5555.

Seeding Data

We use a seed script to populate default users (Super Admin) and generic data. Check package.json for the seed command, or run: 

node prisma/seed.js
# or
npx ts-node prisma/seed.ts

Next: Workflows & Lifecycle ➡️