Skip to main content
TaskFlow Pro is backed by a PostgreSQL database with four core tables. Knex.js manages the schema through versioned migration files. All queries use raw SQL via the pg driver — there is no ORM.

​
Entity relationship overview

schema
USERS
  id_usuario (PK)
  nombre, email (UNIQUE), password, rol, activo
  created_at, update_at
       │
       ├──── PROJECTS  (owner_id → USERS)
       │       id_proyecto (PK)
       │       nombre, descripcion, estado
       │       created_at, update_at
       │            │
       │            └──── TASKS  (project_id → PROJECTS, CASCADE DELETE)
       │                    id_tarea (PK)
       │                    titulo, descripcion, estado, prioridad, due_date
       │                    created_at
       │                    assigned_to → USERS
       │                    created_by  → USERS
       │                         │
       │                         └──── COMMENTS  (task_id → TASKS, CASCADE DELETE)
       │                                 id_comentario (PK)
       │                                 contenido
       │                                 user_id → USERS

​
Users

The users table stores all platform accounts. Role and account status are managed here.
ColumnTypeConstraintsDescription
id_usuariointegerPK, auto-incrementUnique user identifier
nombrevarchar(100)NOT NULLDisplay name
emailvarchar(150)UNIQUELogin email address
passwordvarchar(150)NOT NULLArgon2id password hash
rolintegerdefault 1Role: 1 Developer, 2 Project Manager, 3 Admin
activobooleandefault trueSoft delete flag — false disables login
created_attimestampdefault now()Account creation time
update_attimestampnullableLast profile update time
Users are never hard-deleted. Setting activo = false disables the account while preserving all associated tasks, comments, and project ownership. Only an Admin can change this flag via PUT /usuario/estado.

​
Projects

The projects table represents work containers that group related tasks.
ColumnTypeConstraintsDescription
id_proyectointegerPK, auto-incrementUnique project identifier
nombrevarchar(100)NOT NULLProject name
descripcionvarchar(100)NOT NULLShort description
estadovarchar(50)default 'ACTIVO'Project state: ACTIVO or ARCHIVADO
owner_idintegerNOT NULL, FK → users.id_usuarioThe PM or Admin who created the project
created_attimestampdefault now()Creation time
update_attimestampnullableLast edit time
owner_id is used for ownership-scoped authorization. A Project Manager can only archive or view their own projects — those where owner_id matches their id_usuario. This check is enforced in the service layer, not just by role.

​
Tasks

The tasks table is the core entity of the platform. Each task belongs to one project and is assigned to one user.
ColumnTypeConstraintsDescription
id_tareaintegerPK, auto-incrementUnique task identifier
titulovarchar(50)NOT NULLTask title
descripcionvarchar(150)NOT NULLTask description
estadovarchar(50)default 'TODO'Workflow state: TODO, IN_PROGRESS, IN_REVIEW, DONE
prioridadvarchar(50)NOT NULLPriority level (e.g., ALTA, MEDIA, BAJA)
due_datedateNOT NULLTarget completion date
project_idintegerNOT NULL, FK → projects.id_proyecto, CASCADE DELETEOwning project
assigned_tointegerNOT NULL, FK → users.id_usuarioUser responsible for completing the task
created_byintegerNOT NULL, FK → users.id_usuarioUser who created the task
created_attimestampdefault now()Creation time
When a project is deleted, all of its tasks are deleted automatically via ON DELETE CASCADE. This is permanent — there is no soft delete on tasks.

​
Comments

The comments table stores discussion attached to individual tasks.
ColumnTypeConstraintsDescription
id_comentariointegerPK, auto-incrementUnique comment identifier
contenidovarchar(150)NOT NULLComment body text
task_idintegerNOT NULL, FK → tasks.id_tarea, CASCADE DELETEThe task this comment belongs to
user_idintegerNOT NULL, FK → users.id_usuarioThe user who posted the comment
The comments data model is fully implemented in the database. A frontend UI for comments is planned but not yet available — see Architecture trade-offs.

​
Cascade delete behavior

Deletion propagates down the ownership chain:
cascade chain
Delete PROJECT  →  all TASKS in that project are deleted
                       └─  all COMMENTS on those tasks are deleted

Delete TASK     →  all COMMENTS on that task are deleted
Users are excluded from cascade deletes. Deleting or deactivating a user does not delete their projects, tasks, or comments. References (owner_id, assigned_to, created_by, user_id) remain intact.

Build docs developers (and LLMs) love

Get started for freeTalk to us