# Database Management

> Standards for SQLAlchemy async models, PostgreSQL indexing, UTC datetimes, and Alembic migrations.

- Skill: `aps08/database-management` (Agent Skill)
- Install (CLI): `npx skillmds@latest add aps08/database-management`
- Raw SKILL.md: https://api.skillmd.com/api/skills/aps08/database-management/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: aps08 (https://skillmd.com/u/aps08)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/aps08/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.