SQLModel Task Models

This skill providing guidance on defining a robust database schema using SQLModel for the Todo application.

Purpose

Defining a robust, type-safe, and async-compatible database schema for the Todo application using SQLModel, ensuring compatibility with Better Auth and optimized for PostgreSQL.

Capabilities

• User Model: Schema aligned with Better Auth requirements.
• Task Model: Full CRUD capability with relational mapping to Users.
• Relational Integrity: Proper foreign key constraints and back-references.
• Performance: Strategic indexing on

  user_id

  and

  completed

  fields.
• Safety: Automated timestamp management (

  created_at

  ,

  updated_at

  ).

Implementation Details

Models Definition

from sqlmodel import SQLModel, Field, Relationship
from typing import List, Optional
from datetime import datetime

class User(SQLModel, table=True):
    id: str = Field(primary_key=True)
    email: str = Field(unique=True, index=True)
    name: Optional[str] = None
    created_at: datetime = Field(default_factory=datetime.utcnow)
    tasks: List["Task"] = Relationship(back_populates="user")

class Task(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: str = Field(foreign_key="user.id", index=True)
    title: str
    description: Optional[str] = None
    completed: bool = Field(default=False, index=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    user: Optional[User] = Relationship(back_populates="tasks")

Best Practices

• Using

  table=True

  for models that map to database tables.
• Explicitly defining indexes for fields used in

  WHERE

  clauses (e.g.,

  user_id

  ,

  completed

  ).
• Using

  datetime.utcnow

  for consistent cross-region timestamping.
• Keeping

  user_id

  as a string to match Better Auth's UUID/ID format.