database-management

---
name: database-management
description: Standards for SQLAlchemy async models, PostgreSQL indexing, UTC datetimes, and Alembic migrations.
---

# 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.

More agent context in aps08/fullstack-clean-architecture

11 other files this repository gives its agents.