Database Schema and Table Definitions

This project uses SQLModel to define its database schema, which allows the same classes to serve as both SQLAlchemy ORM models and Pydantic data validation schemas. The core entities are defined in backend/app/models.py.

The architecture follows a pattern of inheritance where "Base" classes define shared fields, while the final "Table" classes (marked with table=True) represent the actual database structure.

The User Entity

The User entity is the central model for authentication and ownership. It is split into several classes to handle different API and database requirements, but the core schema is defined by UserBase and User.

UserBase

UserBase contains the fields shared across creation, updates, and database storage.

class UserBase(SQLModel):
    email: EmailStr = Field(unique=True, index=True, max_length=255)
    is_active: bool = True
    is_superuser: bool = False
    full_name: str | None = Field(default=None, max_length=255)

User (Table Model)

The User class inherits from UserBase and adds database-specific fields like the primary key and hashed password.

class User(UserBase, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    hashed_password: str
    created_at: datetime | None = Field(
        default_factory=get_datetime_utc,
        sa_type=DateTime(timezone=True),  # type: ignore
    )
    items: list["Item"] = Relationship(back_populates="owner", cascade_delete=True)

• UUID Primary Keys: The project uses uuid.UUID for IDs instead of auto-incrementing integers, providing better security and decentralization.
• Timestamps: The created_at field uses a helper function get_datetime_utc to ensure consistent timezone-aware timestamps across the application.

The Item Entity

The Item entity represents resources owned by users. Similar to the User entity, it uses a base class for shared properties.

ItemBase

class ItemBase(SQLModel):
    title: str = Field(min_length=1, max_length=255)
    description: str | None = Field(default=None, max_length=255)

Item (Table Model)

The Item class links back to the User via a foreign key.

class Item(ItemBase, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    created_at: datetime | None = Field(
        default_factory=get_datetime_utc,
        sa_type=DateTime(timezone=True),  # type: ignore
    )
    owner_id: uuid.UUID = Field(
        foreign_key="user.id", nullable=False, ondelete="CASCADE"
    )
    owner: User | None = Relationship(back_populates="items")

Relationships and Cascading Deletes

The relationship between User and Item is a standard One-to-Many mapping. This is implemented using SQLModel's Relationship and SQLAlchemy's foreign key constraints.

1. Foreign Key: Item.owner_id references user.id.
2. Back-population: User.items and Item.owner allow bidirectional navigation between entities.
3. Cascade Deletes: The schema is configured to clean up orphaned items.
   • In User: cascade_delete=True ensures that when a User object is deleted via the ORM, its associated Item objects are also removed.
   • In Item: ondelete="CASCADE" is set on the foreign key to ensure the database itself handles the deletion if a user record is removed at the SQL level.

Model Registration and Initialization

For SQLModel to correctly map relationships (especially forward references like list["Item"]), all models must be imported before the database engine is initialized. This is handled in backend/app/core/db.py:

# backend/app/core/db.py
from app.models import User, UserCreate

# ... engine initialization ...

def init_db(session: Session) -> None:
    # This works because the models are already imported and registered from app.models
    # SQLModel.metadata.create_all(engine)
    
    # Check for existing superuser and create if missing
    user = session.exec(
        select(User).where(User.email == settings.FIRST_SUPERUSER)
    ).first()
    # ...

Usage in CRUD Operations

The models are used throughout backend/app/crud.py to validate data and perform database operations. The model_validate method is frequently used to convert Pydantic-style input schemas (like UserCreate or ItemCreate) into the actual database table models.

Example of creating a user with a hashed password:

def create_user(*, session: Session, user_create: UserCreate) -> User:
    db_obj = User.model_validate(
        user_create, update={"hashed_password": get_password_hash(user_create.password)}
    )
    session.add(db_obj)
    session.commit()
    session.refresh(db_obj)
    return db_obj

This pattern ensures that the database logic remains decoupled from the API schemas while maintaining strict type safety.