---

name: postgres-schema-design description: PostgreSQL schema design with SQLModel. Use when designing database schemas, creating ERDs, defining models, planning migrations, or reviewing table structures. Triggers on tasks involving database tables, relationships, indexes, or data modeling for PostgreSQL.

PostgreSQL Schema Design (SQLModel)

Quick Start

Copyimport uuid
from datetime import datetime
from sqlmodel import Field, Relationship, SQLModel

class TimestampMixin(SQLModel):
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)

class User(TimestampMixin, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    email: str = Field(unique=True, index=True)
    name: str = Field(index=True)

class Post(TimestampMixin, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    title: str = Field(index=True)
    author_id: uuid.UUID = Field(foreign_key="user.id", index=True)
    author: User | None = Relationship(back_populates="posts")

Decision Guide

Primary Key: UUID vs Integer

Copy# UUID (recommended default)
id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)

# Integer auto-increment
id: int | None = Field(default=None, primary_key=True)

Table Names: Singular

SQLModel converts UserAccount → useraccount. Override if needed:

Copyclass UserAccount(SQLModel, table=True):
    __tablename__ = "user_account"

Always Index Foreign Keys

PostgreSQL does NOT auto-index FKs:

Copy# WRONG - no index on FK
author_id: uuid.UUID = Field(foreign_key="user.id")

# CORRECT - indexed FK
author_id: uuid.UUID = Field(foreign_key="user.id", index=True)

Relationship Patterns

One-to-Many

Copyclass Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    members: list["User"] = Relationship(back_populates="team")

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    team_id: int | None = Field(default=None, foreign_key="team.id", index=True)
    team: Team | None = Relationship(back_populates="members")

Many-to-Many

Copyclass UserRoleLink(SQLModel, table=True):
    user_id: uuid.UUID = Field(foreign_key="user.id", primary_key=True)
    role_id: uuid.UUID = Field(foreign_key="role.id", primary_key=True)

class User(SQLModel, table=True):
    roles: list["Role"] = Relationship(back_populates="users", link_model=UserRoleLink)

class Role(SQLModel, table=True):
    users: list[User] = Relationship(back_populates="roles", link_model=UserRoleLink)

Self-Referential (Tree)

Copyclass Category(SQLModel, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    parent_id: uuid.UUID | None = Field(default=None, foreign_key="category.id", index=True)
    parent: "Category | None" = Relationship(
        back_populates="children",
        sa_relationship_kwargs={"remote_side": "Category.id"}
    )
    children: list["Category"] = Relationship(back_populates="parent")

PostgreSQL-Specific Features

JSONB Column

Copyfrom sqlalchemy import Column
from sqlalchemy.dialects.postgresql import JSONB

class User(SQLModel, table=True):
    metadata: dict = Field(default={}, sa_column=Column(JSONB))

Composite/Partial Index

Copyfrom sqlalchemy import Index, text

class User(SQLModel, table=True):
    __table_args__ = (
        Index('idx_user_tenant_email', 'tenant_id', 'email', unique=True),
        Index('idx_user_active', 'id', postgresql_where=text('deleted_at IS NULL')),
    )

Check Constraint

Copyclass Order(SQLModel, table=True):
    __table_args__ = (
        CheckConstraint("status IN ('pending', 'shipped', 'delivered')"),
    )

Resources

Scripts

Generate SQLModel boilerplate:

Copypython scripts/generate_models.py --example --output models.py

Checklist (Critical Items)

• Every table has a primary key
• All foreign keys are indexed
• created_at/updated_at on mutable tables
• Unique constraints where business rules require
• No sensitive data in plain text
• Soft delete uses partial index for active records