specs/002-fullstack-ui-integration/data-model.md

Data Model: Full-Stack Integration & UI Experience

Feature: 002-fullstack-ui-integration Date: 2026-01-09 Status: Reference Only (No New Entities)

Overview

This feature does not introduce new data entities. It integrates and polishes existing functionality from Specs 1 (Task CRUD) and 2 (Authentication & API Security). This document references the existing data model for completeness.

Existing Entities

User (from Spec 2: Authentication & API Security)

Purpose: Represents an authenticated user with task management capabilities

Attributes:

• id (integer, primary key): Unique identifier for the user
• email (string, unique, required): User's email address for authentication
• name (string, required): User's display name
• password_hash (string, required): Bcrypt-hashed password (never exposed in API)
• created_at (datetime, auto): Timestamp of account creation
• updated_at (datetime, auto): Timestamp of last profile update

Relationships:

• One-to-Many with Task: A user can have multiple tasks

Validation Rules:

• Email must be valid RFC 5322 format
• Email must be unique across all users
• Password must be at least 8 characters with uppercase, lowercase, and number
• Name must be 1-100 characters

Security:

• Password is hashed with bcrypt (cost factor 12) before storage
• Password hash is never returned in API responses
• User ID is extracted from JWT token for all authenticated requests

Database Table: users

Indexes:

• Primary key on id
• Unique index on email

Source: backend/src/models/user.py

---

Task (from Spec 1: Task CRUD)

Purpose: Represents a todo item belonging to a specific user

Attributes:

• id (integer, primary key): Unique identifier for the task
• user_id (integer, foreign key, required): Owner of the task (references User.id)
• title (string, required): Task title (max 200 characters)
• description (string, optional): Task description (max 1000 characters)
• completed (boolean, default false): Completion status
• created_at (datetime, auto): Timestamp of task creation
• updated_at (datetime, auto): Timestamp of last task update

Relationships:

• Many-to-One with User: Each task belongs to exactly one user

Validation Rules:

• Title is required and must be 1-200 characters
• Description is optional, max 1000 characters
• Completed defaults to false
• User ID must reference an existing user

Business Rules:

• Users can only access their own tasks (enforced by JWT authentication)
• Tasks are automatically filtered by authenticated user_id in all queries
• Deleting a user cascades to delete all their tasks

Database Table: tasks

Indexes:

• Primary key on id
• Index on user_id (for filtering by user)
• Index on completed (for filtering by status)
• Composite index on (user_id, completed) (for combined filtering)
• Index on created_at (for sorting)

Source: backend/src/models/task.py

---

AuthSession (Frontend Only - from Spec 2)

Purpose: Client-side session state for authenticated users

Attributes:

• token (string, nullable): JWT token from backend
• user (object, nullable): User profile information
  • id (integer): User ID
  • email (string): User email
  • name (string): User display name

Storage: Browser localStorage (key: auth_session)

Lifecycle:

• Created on successful signin (POST /api/auth/signin)
• Persisted across page refreshes
• Cleared on signout or 401 Unauthorized response
• Expires when JWT token expires (7 days)

Security:

• Token is included in Authorization header for all API requests
• Session is cleared on any authentication error
• No sensitive data stored (password never stored client-side)

Source: frontend/src/lib/auth.ts

---

Entity Relationships

User (1) ----< (Many) Task
  |
  | JWT Token (stateless)
  |
  v
AuthSession (Frontend)

Relationship Details:

1. User → Task (One-to-Many):

   • A user can have zero or more tasks
   • Each task belongs to exactly one user
   • Foreign key: Task.user_id references User.id
   • Cascade delete: Deleting a user deletes all their tasks

2. User → AuthSession (Stateless):

   • JWT token contains user_id and email
   • No server-side session storage
   • Frontend stores token and user profile in localStorage
   • Token is verified on every API request

Data Flow

Authentication Flow

1. User signs up/signs in
   ↓
2. Backend creates JWT token with user_id
   ↓
3. Frontend stores token + user profile in AuthSession
   ↓
4. Frontend includes token in Authorization header
   ↓
5. Backend verifies token and extracts user_id
   ↓
6. Backend filters all queries by user_id

Task Management Flow

1. User creates/updates/deletes task
   ↓
2. Frontend sends request with JWT token
   ↓
3. Backend verifies token → extracts user_id
   ↓
4. Backend performs operation (filtered by user_id)
   ↓
5. Backend returns result
   ↓
6. Frontend updates UI (optimistic or after response)

Data Isolation

Critical Security Requirement: All task queries MUST be filtered by authenticated user_id

Implementation:

• JWT token contains user_id in 'sub' claim
• get_current_user() dependency extracts user_id from token
• All task endpoints use current_user_id = Depends(get_current_user)
• SQLModel queries include .where(Task.user_id == current_user_id)

Verification:

• User A cannot access User B's tasks
• API returns 404 (not 403) for unauthorized task access
• No data leakage through error messages

State Transitions

Task State Transitions

[New Task]
    ↓
[Active] ←→ [Completed]
    ↓
[Deleted]

Transitions:

• New → Active: Task created with completed=false
• Active → Completed: User marks task as done (completed=true)
• Completed → Active: User marks task as not done (completed=false)
• Any → Deleted: User deletes task (hard delete from database)

No Soft Deletes: Tasks are permanently deleted (no deleted_at field)

User State Transitions

[New User]
    ↓
[Active]
    ↓
[Deleted] (future - not implemented)

Current Implementation:

• New → Active: User signs up successfully
• No user deletion implemented yet (out of scope)

Schema Migrations

Existing Migrations:

1. 001_initial.py: Created users and tasks tables (Spec 1)
2. 002_add_user_password.py: Added password_hash to users table (Spec 2)

No New Migrations Required: This feature does not modify the database schema

Data Validation

Backend Validation (Pydantic Schemas)

User Validation (backend/src/schemas/auth.py):

• Email: RFC 5322 format validation
• Password: Min 8 chars, uppercase, lowercase, number
• Name: 1-100 characters

Task Validation (backend/src/schemas/task.py):

• Title: Required, 1-200 characters
• Description: Optional, max 1000 characters
• Completed: Boolean (defaults to false)

Frontend Validation

Client-Side Validation:

• Email format validation (regex)
• Password strength validation (min 8 chars, complexity)
• Form field required/optional indicators
• Inline error messages

Note: Backend validation is authoritative - frontend validation is for UX only

Performance Considerations

Indexes (already implemented):

• users.email (unique): Fast user lookup during signin
• tasks.user_id: Fast filtering of user's tasks
• tasks.completed: Fast filtering by completion status
• tasks.(user_id, completed): Fast combined filtering
• tasks.created_at: Fast sorting by creation date

Query Patterns:

• Most common: Get all tasks for user (filtered by user_id)
• Second most common: Get active/completed tasks for user
• Sorting: By created_at or updated_at

No N+1 Queries: All queries are direct (no nested loops)

Summary

This feature reuses the existing data model from Specs 1 and 2:

• User: Authentication and ownership
• Task: Todo items with user isolation
• AuthSession: Frontend session state

No new entities, relationships, or migrations are required. The focus is on UI integration and polish rather than data model changes.