Database management

Database Management Skill

1. SQLAlchemy Base Class & Models

All database models (except system/internal models where not applicable) must inherit from BaseModel defined in server/app/models/base.py.

Automatically Handled by BaseModel

• Table Names: Automatically converted from PascalCase class names to snake_case (e.g., Todos -> todos, Attachments -> attachments).
• Primary Keys: Defined as a native PostgreSQL UUID using PG_UUID(as_uuid=True) with database-side generator server_default=text("gen_random_uuid()").
• Audit Fields: All models inherit these auditing and metadata fields:
  • id: Mapped[UUID] primary key.
  • is_active: Boolean status defaulting to true on the server.
  • is_deleted: Soft-delete status defaulting to false on the server.
  • created_at / updated_at: Timezone-aware UTC timestamps with server_default=func.now() (and onupdate=func.now() for updates).
  • created_by / updated_by: VARCHAR(100) auditing fields.

Best Practices for Custom Models

• Inheritance: Always subclass BaseModel.
• Use Database Defaults: Lean on PostgreSQL for default values as much as possible using server_default (e.g. server_default=text("true") rather than Python-level default=True).
• Type Annotations: Use SQLAlchemy 2.0 Mapped[...] and mapped_column() syntax.
• Foreign Keys:
  • Explicitly define ondelete behavior (e.g. ondelete="CASCADE").
  • Add index=True for foreign key columns to ensure performant joins.
• Timezones: Use timezone-aware datetime objects (TIMESTAMP(timezone=True)) or Pydantic UTC validation.
• Relationships: Define back-populates and lazy loading modes explicitly (e.g., lazy="selectin" for eager loading without Cartesian products).

---

2. Database Queries & Transactions

• Async execution: All database interactions must be executed asynchronously using AsyncSession.
• Eager Loading: Always declare eager relationships where expected to avoid N+1 queries. Specify eagers list on models if supported by the service repository.
• Optimistic Concurrency: Use auditing columns or version fields if concurrent updates are expected on highly mutated resources.

---

3. Migrations (Alembic)

• Autogeneration: Generate migrations via alembic revision --autogenerate -m "description".
• Review Migrations: Always review autogenerated migration scripts before applying them. Pay special attention to constraints, indexes, and type alterations.
• Reversible Migrations: Ensure all migrations implement both upgrade() and downgrade() functions.