Skip to main content
TaskForge API uses SQLAlchemy as its ORM layer backed by Azure SQL Database in production and SQLite for local development. The schema includes six tables connected through foreign keys and a many-to-many junction table.

​
User

The users table stores account credentials and profile information. Passwords are never stored in plaintext — they are hashed with bcrypt before being persisted.
The password_hash field is never included in API responses. Use to_dict() output shapes shown below to understand what the API returns.

​
Fields

​
id
integer
required
Auto-incremented primary key.
​
username
string
required
Unique username. Must be 3–80 characters and contain only letters, digits, underscores, and hyphens. Indexed for fast lookups.
​
email
string
required
Unique email address. Maximum 120 characters. Stored in lowercase. Indexed for fast lookups.
​
password_hash
string
bcrypt hash of the user’s password. Maximum 255 characters. Never returned in API responses.
​
first_name
string
Optional first name. Maximum 50 characters.
​
last_name
string
Optional last name. Maximum 50 characters.
​
is_active
boolean
Whether the account is active. Defaults to true. Inactive users cannot authenticate.
​
role_id
integer
required
Foreign key referencing roles.id. Every user must have a role.
​
created_at
string (ISO 8601 datetime)
UTC timestamp of account creation.
​
updated_at
string (ISO 8601 datetime)
UTC timestamp of the most recent update. Automatically updated by a database trigger on every UPDATE.

​
to_dict() output

The User model serializes to the following JSON shape. The role object is included by default and can be omitted when include_role=False. 
{
  "id": 1,
  "username": "johndoe",
  "email": "[email protected]",
  "first_name": "John",
  "last_name": "Doe",
  "full_name": "John Doe",
  "is_active": true,
  "created_at": "2024-01-15T10:30:00",
  "updated_at": "2024-06-01T08:00:00",
  "role": {
    "id": 2,
    "name": "user",
    "description": "Standard user role with limited access"
  }
}
The full_name field is a computed property. It returns "{first_name} {last_name}" when both are set, and falls back to username otherwise.

​
Task

The tasks table is the core of the API. Each task belongs to a user and may have zero or more tags attached through the task_tags junction table.

​
Fields

​
id
integer
required
Auto-incremented primary key.
​
title
string
required
Task title. Required. Maximum 200 characters.
​
description
string
Optional long-form description. Stored as TEXT (unlimited length in Azure SQL). Maximum 5,000 characters enforced at the application layer.
​
status
enum
required
Current status of the task. Defaults to pending.
​
priority
enum
required
Priority level. Defaults to medium.
​
due_date
string (ISO 8601 datetime)
Optional deadline. Must be provided in ISO 8601 format (e.g., 2024-12-31T23:59:59 or 2024-12-31T23:59:59Z).
​
completed_at
string (ISO 8601 datetime)
UTC timestamp of when the task status was set to completed. Set automatically when the task is marked complete and cleared if status changes back.
​
user_id
integer
required
Foreign key referencing users.id. Deletes cascade: removing a user removes all their tasks.
​
created_at
string (ISO 8601 datetime)
UTC timestamp of task creation.
​
updated_at
string (ISO 8601 datetime)
UTC timestamp of the most recent update. Automatically maintained by a database trigger.
​
tags
array
Array of Tag objects associated with this task via the task_tags junction table. See the Tag model for the shape of each object.

​
to_dict() output

By default, tags are included and the full user object is omitted. Pass include_user=True to embed the user. 
{
  "id": 42,
  "title": "Complete project documentation",
  "description": "Write comprehensive API documentation for all endpoints.",
  "status": "in_progress",
  "priority": "high",
  "due_date": "2024-12-31T23:59:59",
  "completed_at": null,
  "is_overdue": false,
  "is_completed": false,
  "created_at": "2024-11-01T09:00:00",
  "updated_at": "2024-11-15T14:30:00",
  "user_id": 1,
  "tags": [
    {
      "id": 1,
      "name": "Work",
      "color": "#FF5733",
      "description": "Work-related tasks",
      "task_count": 5,
      "created_at": "2024-01-01T00:00:00",
      "updated_at": "2024-01-01T00:00:00"
    }
  ]
}
is_overdue and is_completed are computed properties. is_overdue is true when due_date is in the past and the task is not yet completed.

​
Tag

The tags table stores reusable labels that can be attached to any number of tasks. Tags are global — they are not scoped to individual users.

​
Fields

​
id
integer
required
Auto-incremented primary key.
​
name
string
required
Unique tag name. Maximum 50 characters. Indexed. Names must be unique across the entire system.
​
color
string
Hex color code in #RRGGBB format (e.g., #FF5733). Defaults to #808080 (gray). Must match the pattern ^#[0-9A-Fa-f]{6}$.
​
description
string
Optional description of the tag’s purpose. Maximum 255 characters.
​
created_at
string (ISO 8601 datetime)
UTC timestamp of tag creation.
​
updated_at
string (ISO 8601 datetime)
UTC timestamp of the most recent update.

​
to_dict() output

{
  "id": 1,
  "name": "Work",
  "color": "#FF5733",
  "description": "Work-related tasks",
  "task_count": 12,
  "created_at": "2024-01-01T00:00:00",
  "updated_at": "2024-06-15T10:00:00"
}
task_count is a computed property that counts the number of tasks currently associated with the tag. Pass include_task_count=False to to_dict() to omit it.

​
Role

The roles table supports role-based access control (RBAC). Two roles are seeded by default.

​
Fields

​
id
integer
required
Auto-incremented primary key.
​
name
string
required
Unique role identifier. One of admin or user.
​
description
string
Human-readable description of the role’s permissions.
​
created_at
string (ISO 8601 datetime)
UTC timestamp of role creation.

​
Default roles

NameDescription
adminAdministrator role with full access to all resources
userStandard user role with access limited to their own resources
New users are always assigned the user role on registration. Only an existing admin can promote another user to admin via PUT /api/users/{user_id} with "role": "admin" in the request body.

​
RefreshToken

The refresh_tokens table persists JWT refresh tokens so they can be explicitly revoked. All refresh tokens for a user are automatically revoked when the user changes their password.

​
Fields

​
id
integer
required
Auto-incremented primary key.
​
user_id
integer
required
Foreign key referencing users.id. Deletes cascade: removing a user removes all their tokens.
​
token
string
required
The raw JWT refresh token string. Maximum 500 characters. Unique and indexed.
​
is_revoked
boolean
Whether the token has been explicitly revoked (e.g., by logging out or changing password). Defaults to false.
​
expires_at
string (ISO 8601 datetime)
required
UTC expiry datetime. Defaults to 30 days after creation (JWT_REFRESH_TOKEN_EXPIRES=2592000).
​
created_at
string (ISO 8601 datetime)
UTC timestamp of token creation.

​
Database schema

The following ER diagram shows all tables and their relationships. One-to-many relationships are denoted with < (many side). 
┌─────────────┐       ┌──────────────┐       ┌─────────────┐
│    roles    │       │    users     │       │    tasks    │
├─────────────┤       ├──────────────┤       ├─────────────┤
│ id (PK)     │───┐   │ id (PK)      │───┐   │ id (PK)     │
│ name        │   └──<│ role_id (FK) │   └──<│ user_id (FK)│
│ description │       │ username     │       │ title       │
└─────────────┘       │ email        │       │ description │
                      │ password     │       │ status      │
                      └──────────────┘       │ priority    │
                                             │ due_date    │
                      ┌──────────────┐       └─────────────┘
                      │refresh_tokens│              │
                      ├──────────────┤              │
                      │ id (PK)      │              │
                      │ user_id (FK) │         ┌────┴────┐
                      │ token        │         │task_tags│
                      │ expires_at   │         ├─────────┤
                      └──────────────┘         │task_id  │
                                               │tag_id   │
                      ┌─────────────┐          └────┬────┘
                      │    tags     │               │
                      ├─────────────┤               │
                      │ id (PK)     │───────────────┘
                      │ name        │
                      │ color       │
                      │ description │
                      └─────────────┘
The task_tags junction table implements the many-to-many relationship between tasks and tags. Both foreign keys participate in a composite primary key. Deletes cascade from both sides.

​
Paginated response format

All list endpoints (tasks, tags, users) wrap their results in a standard pagination envelope. The pagination object uses total_pages as the key (not pages). 
{
  "success": true,
  "data": {
    "tasks": [
      { "id": 1, "title": "First task" },
      { "id": 2, "title": "Second task" }
    ],
    "pagination": {
      "page": 1,
      "per_page": 10,
      "total": 42,
      "total_pages": 5,
      "has_next": true,
      "has_prev": false
    }
  }
}
​
page
integer
Current page number (1-indexed). Defaults to 1.
​
per_page
integer
Number of items per page. Defaults to 10 (DEFAULT_PAGE_SIZE). Maximum 100 (MAX_PAGE_SIZE).
​
total
integer
Total number of items matching the current filters.
​
total_pages
integer
Total number of pages. Computed as ceil(total / per_page).
​
has_next
boolean
true when there is at least one more page after the current one.
​
has_prev
boolean
true when the current page is greater than 1.

Build docs developers (and LLMs) love

Get started for freeTalk to us