database-modeling

# Database Modeling Skill > **Expertise:** PostgreSQL schema design, SQLAlchemy (async), query optimization, indexing, migrations (Alembic), safe schema changes. ## Schema Design Patterns ### Standard column set (all tables) ```python from sqlalchemy import Column, Integer, DateTime, func from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): pass class TimestampMixin: created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False ) class Order(TimestampMixin, Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False, index=True) status: Mapped[str] = mapped_column(String(20), nullable=False, default="pending") total_amount: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False) ``` ### Soft delete pattern ```python class SoftDeleteMixin: deleted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True) @property def is_deleted(self) -> bool: return self.deleted_at is not None # Always filter in repository, never expose deleted records by default class OrderRepository: async def list_active(self, session: AsyncSession): return await sess...