Velozity Assignment Db Schema

I designed the relational database for the Velozity Assignment project with clear entity definitions, UUID primary keys, and foreign‑key relationships that match the ER diagram. The schema supports role‑based access, task management, activity logging, notifications, and secure token handling.

USER {
  string id PK
  string email
  string passwordHash
  string name
  string role
  datetime createdAt
  datetime updatedAt
}

CLIENT {
  string id PK
  string name
  string email
  string company
  datetime createdAt
  datetime updatedAt
}

PROJECT {
  string id PK
  string name
  string description
  string clientId FK
  string pmId FK
  datetime createdAt
  datetime updatedAt
}

TASK {
  string id PK
  int taskNumber
  string title
  string description
  string status
  string priority
  datetime dueDate
  boolean isOverdue
  string projectId FK
  string assignedToId FK
  datetime createdAt
  datetime updatedAt
}

ACTIVITY_LOG {
  string id PK
  string taskId FK
  string userId FK
  string action
  string previousStatus
  string newStatus
  string details
  datetime timestamp
}

NOTIFICATION {
  string id PK
  string userId FK
  string title
  string message
  string taskId FK
  boolean isRead
  datetime createdAt
}

REFRESH_TOKEN {
  string id PK
  string token
  string userId FK
  datetime expiresAt
  boolean revoked
  datetime createdAt
}

• Key indexing decisions:

The USER.id column is defined as a UUID primary key.

The CLIENT.id column is defined as a UUID primary key.

The PROJECT.id column is defined as a UUID primary key.

The TASK.id column is defined as a UUID primary key.

The REFRESH_TOKEN.id column is defined as a UUID primary key.

For authentication, I use short‑lived JWT access tokens (15 minutes) stored only in React memory, and long‑lived HttpOnly SameSite=Lax refresh tokens (7 days) with 40‑byte entropy persisted in the RefreshToken table. The Axios interceptor automatically refreshes tokens on 401 responses.

• Index on Task(projectId, assignedToId) – enables fast role‑based task lookups.
• Index on Task(status, priority) – powers the UI filter bar and status dashboard.
• Index on Task(dueDate, isOverdue) – optimises the overdue‑cron query.
• Index on ActivityLog(timestamp DESC, taskId) – retrieves the latest 20 events efficiently.
• Composite index on Notification(userId, isRead) – speeds counting unread notifications.
• Index on RefreshToken(token) – provides O(1) token validation and revocation. These nine indexes align with the most frequent queries across the application.