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.
  • Phase Progression: UI_UX -> DESIGN_APPROVAL -> CI_CD -> DEVELOPMENT -> TESTING -> COMPLETED.
  • AI Fields: securityLevel (Low/Med/High), category (Functional/Non-Functional).
  • Assets: Stores designUrl and repoUrl (submitted by developers during specific phases).

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 Review Tasks page by ADMIN, SUPER_ADMIN, or MANAGER roles (Create, Update, Delete).
  • 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

Wiping Data

To reset the database while preserving User credentials, run the custom wipe script. This helps clear off old projects, requirements, tasks, and logs without losing user accounts. 

node prisma/wipe.js

Next: Workflows & Lifecycle ➡️