erd.md

Database Design

erDiagram
    USERS ||--o{ PROJECTS : owns
    PROJECTS ||--o{ TASKS : contains
    USERS ||--o{ PERSONAL_ACCESS_TOKENS : authenticates

    USERS {
        bigint id PK
        string name
        string email UK
        timestamp email_verified_at "nullable"
        string password
        string remember_token "nullable"
        timestamps created_at_updated_at
    }

    PROJECTS {
        bigint id PK
        bigint user_id FK "cascade on delete"
        string name "150"
        text description "nullable"
        string status "active | completed | archived"
        timestamps created_at_updated_at
        timestamp deleted_at "soft delete"
    }

    TASKS {
        bigint id PK
        bigint project_id FK "cascade on delete"
        string title "150"
        text description "nullable"
        string priority "low | medium | high"
        string status "todo | in_progress | done"
        datetime due_date "nullable"
        timestamps created_at_updated_at
        timestamp deleted_at "soft delete"
    }

    PERSONAL_ACCESS_TOKENS {
        bigint id PK
        string tokenable_type "morph"
        bigint tokenable_id "morph"
        string name
        string token UK
        timestamp last_used_at "nullable"
    }

Loading

Relations

Relation	Type	Notes
User → Project	hasMany	A project always belongs to exactly one owner.
Project → Task	hasMany	Tasks never exist outside a project.
User → Task	hasManyThrough	Used by the dashboard to aggregate without extra queries.

Design notes

• Enums live in PHP, columns are string. Adding a new status later is a code change, not a schema migration + table lock. Values are enforced by Rule::enum() on input and cast back to enums on read.
• Soft deletes on projects and tasks so a delete is recoverable and reports stay consistent. Deleting a user hard-deletes their projects and tasks through the FK cascade.
• No denormalized counters. Dashboard numbers are aggregated with indexed COUNT queries instead of denormalized columns, so they can never drift.

Indexes and why

Table	Index	Serves
projects	(user_id, status)	GET /projects and ?status= — the leftmost column also covers plain owner lookups.
projects	(user_id, created_at)	Default newest-first pagination without a filesort.
tasks	(project_id, status)	Task list and ?status= filter.
tasks	(project_id, priority)	?priority= filter.
tasks	(due_date, status)	Overdue count on the dashboard and the overdue queue job.
tasks	(title)	Prefix search (LIKE 'term%'). A trailing wildcard cannot use a B-tree, so full-text is the upgrade path if search grows.

Unique constraints: users.email, personal_access_tokens.token.