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

python
1from sqlmodel import SQLModel, Field, Relationship
2from typing import List, Optional
3from datetime import datetime
4
5class User(SQLModel, table=True):
6    id: str = Field(primary_key=True)
7    email: str = Field(unique=True, index=True)
8    name: Optional[str] = None
9    created_at: datetime = Field(default_factory=datetime.utcnow)
10    tasks: List["Task"] = Relationship(back_populates="user")
11
12class Task(SQLModel, table=True):
13    id: Optional[int] = Field(default=None, primary_key=True)
14    user_id: str = Field(foreign_key="user.id", index=True)
15    title: str
16    description: Optional[str] = None
17    completed: bool = Field(default=False, index=True)
18    created_at: datetime = Field(default_factory=datetime.utcnow)
19    updated_at: datetime = Field(default_factory=datetime.utcnow)
20    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.