Build TaskFlow Database Models & Migrations

Build the TaskFlow Database Layer

You are building the complete database layer for TaskFlow -- a production task management REST API. By the end of this lab you will have async SQLAlchemy models, Pydantic validation schemas, Alembic migrations, and a database seed script.

Stack: SQLAlchemy 2.0.46 | asyncpg | Alembic 1.18.4 | PostgreSQL 18.2 | Pydantic 2.12.x

---

Part 1: Async Database Session (15 points)

Create app/database.py with the async database engine and session factory.

Requirements:

• Use create_async_engine with the postgresql+asyncpg driver
• Read the database URL from an environment variable DATABASE_URL
• Create an async_sessionmaker with expire_on_commit=False
• Implement a get_db async generator that yields an AsyncSession
• Define a Base class using DeclarativeBase for all models to inherit from

# Expected imports
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase

---

Part 2: SQLAlchemy 2.0 Models (30 points)

Create app/models.py with four models using SQLAlchemy 2.0 Mapped type annotations.

User Model

Column	Type	Constraints
id	int	Primary key
email	str(255)	Unique, indexed, not null
hashed_password	str(255)	Not null
full_name	str(100)	Not null
is_active	bool	Default True
created_at	datetime	Default utcnow
updated_at	datetime	Default utcnow, onupdate utcnow

Relationships: owned_projects, assigned_tasks, memberships

Project Model

Column	Type	Constraints
id	int	Primary key
name	str(200)	Not null
description	str(1000)	Nullable
owner_id	int	Foreign key to users.id, not null
created_at	datetime	Default utcnow
updated_at	datetime	Default utcnow, onupdate utcnow

Relationships: owner, tasks, members

Task Model

Column	Type	Constraints
id	int	Primary key
title	str(300)	Not null
description	str(2000)	Nullable
status	TaskStatus enum	Default "todo", not null
priority	TaskPriority enum	Default "medium", not null
project_id	int	Foreign key to projects.id, not null
assignee_id	int	Foreign key to users.id, nullable
due_date	datetime	Nullable
created_at	datetime	Default utcnow
updated_at	datetime	Default utcnow, onupdate utcnow

Relationships: project, assignee

Enums:

• TaskStatus: todo, in_progress, done
• TaskPriority: low, medium, high

ProjectMember Model

Column	Type	Constraints
id	int	Primary key
project_id	int	Foreign key to projects.id, not null
user_id	int	Foreign key to users.id, not null
role	MemberRole enum	Default "member", not null
joined_at	datetime	Default utcnow

Enum:

• MemberRole: owner, admin, member

Constraint: Unique together on (project_id, user_id) -- a user can only have one role per project.

All models must:

• Use Mapped[type] and mapped_column() (SQLAlchemy 2.0 style)
• Inherit from the shared Base
• Define __tablename__ explicitly
• Use relationship() with back_populates for bidirectional links

---

Part 3: Pydantic v2 Schemas (25 points)

Create app/schemas.py with Create, Update, and Response schemas for each model.

Requirements:

• All Response schemas must set model_config = ConfigDict(from_attributes=True)
• Use EmailStr for email validation in UserCreate
• Update schemas should make all fields Optional (partial updates)
• Use Python enum.Enum for status/priority/role fields
• Add field constraints where appropriate (e.g., min_length=1 for title)

Expected schemas:

# User schemas
class UserCreate(BaseModel):       # email, password, full_name
class UserUpdate(BaseModel):       # full_name (optional), is_active (optional)
class UserResponse(BaseModel):     # id, email, full_name, is_active, created_at, updated_at

# Project schemas
class ProjectCreate(BaseModel):    # name, description (optional)
class ProjectUpdate(BaseModel):    # name (optional), description (optional)
class ProjectResponse(BaseModel):  # id, name, description, owner_id, created_at, updated_at

# Task schemas
class TaskCreate(BaseModel):       # title, description (optional), status, priority, project_id, assignee_id (optional), due_date (optional)
class TaskUpdate(BaseModel):       # all fields optional
class TaskResponse(BaseModel):     # all fields including id, created_at, updated_at

# ProjectMember schemas
class ProjectMemberCreate(BaseModel):   # user_id, role
class ProjectMemberResponse(BaseModel): # id, project_id, user_id, role, joined_at

---

Part 4: Alembic Setup & Initial Migration (15 points)

Set up Alembic for async migrations and generate the initial migration.

Steps:

1. Initialize Alembic with alembic init alembic
2. Configure alembic/env.py for async operation:
   • Import your Base.metadata as the target_metadata
   • Use run_async with connectable = create_async_engine()
   • Configure context.configure() with render_as_batch=True for SQLite compatibility during tests
3. Update alembic.ini with the correct sqlalchemy.url
4. Generate the initial migration:

   alembic revision --autogenerate -m "create users projects tasks project_members tables"
   

5. The generated migration must include upgrade() and downgrade() functions that create and drop all four tables

Provide the complete alembic/env.py file showing async configuration.

---

Part 5: Database Seed Script (15 points)

Create app/seed.py that populates the database with realistic test data.

Requirements:

• Create at least 3 users with hashed passwords (use a placeholder hash)
• Create at least 2 projects, each owned by a different user
• Create at least 5 tasks across the projects with varied statuses and priorities
• Add project members with different roles
• Use the async session to insert all data
• The script must be runnable with python -m app.seed
• Include proper error handling (skip if data already exists)

# Expected structure
async def seed_database():
    async with async_session() as session:
        # Check if data already exists
        # Create users
        # Create projects
        # Create project members
        # Create tasks
        await session.commit()

if __name__ == "__main__":
    import asyncio
    asyncio.run(seed_database())

---

What to Submit

Your submission should contain 5 file sections in the editor below. Each section begins with a # FILE N: header.

---