# Database Guidelines

> Database patterns and conventions for this project.

---

## Overview

Current backend data layer is **SQLAlchemy Async ORM + Alembic**.

Evidence:

- Async engine/session: `backend/src/core/db/session.py`
- ORM base/mixins: `backend/src/core/db/base.py`
- Generic repository soft-delete behavior: `backend/src/core/db/base_repository.py`
- Migration entry: `backend/alembic/env.py`
- Migration files: `backend/alembic/versions/*.py`

Transaction boundary convention (actual implementation):

- **Repository** does query/flush only
- **Service** owns `commit()` / `rollback()`

Examples:

- `backend/src/v1/todo/repository.py` uses `flush()` and never commits
- `backend/src/v1/todo/service.py` commits/rolls back around repository calls
- `backend/src/v1/schedule_items/service.py` commits/rolls back in service methods

---

## Query Patterns

Observed query conventions:

1. Use SQLAlchemy expression API (`select`, `update`, `delete`) with async session.
2. Default to soft-delete-safe reads (`deleted_at IS NULL`) for models supporting soft delete.
3. Many repositories log and re-raise DB exceptions; service layer converts DB failures into stable API/domain errors.

Examples:

- `backend/src/core/db/base_repository.py`
  - `_apply_soft_delete_filter(...)`
  - `get_by_id(...)` and `get_one(...)` auto-apply `deleted_at IS NULL` when column exists
  - `update_by_id(...)`/`soft_delete_by_id(...)` use `flush()` and do not own commit/rollback
- `backend/src/v1/todo/repository.py`
  - `list_by_owner(...)` explicitly applies `.where(Todo.deleted_at.is_(None))`
- `backend/src/v1/schedule_items/repository.py`
  - `list_by_date_range(...)` combines owner filter + soft-delete filter + time window

---

## Migrations

Alembic is the schema migration source of truth.

Evidence:

- `backend/alembic/alembic.ini`
- `backend/alembic/env.py`
- `backend/alembic/versions/20260226_0001_initial_schema.py`

Project command entry points:

- `./infra/scripts/dev-migrate.sh migrate`
- `./infra/scripts/dev-migrate.sh init-data`
- `./infra/scripts/dev-migrate.sh bootstrap`

The script executes runtime CLI with `uv` and `PYTHONPATH=backend/src`.

---

## Naming Conventions

Observed naming patterns in models and migrations:

- Table names: plural snake_case (`todos`, `profiles`, `schedule_items`, `friendships`)
  - Examples in `backend/src/models/todos.py`, `backend/src/models/profile.py`, `backend/src/models/friendships.py`
- Common audit columns: `created_at`, `updated_at`, `deleted_at`
  - Mixins in `backend/src/core/db/base.py`
- Key/index/constraint names in Alembic are explicit and prefixed by type:
  - `ix_*` (index), e.g. `ix_llms_factory_id`
  - `fk_*` (foreign key), e.g. `fk_profiles_id`
  - `uq_*` (unique), e.g. `uq_automation_jobs_id_owner`
  - `chk_*` (check), e.g. `chk_automation_job_schedule_type`
  - Evidence in `backend/alembic/versions/20260226_0001_initial_schema.py`

---

## Anti-patterns / Forbidden Patterns

- Do not commit/rollback inside repository classes.
  - Repositories should only `flush` and raise (`backend/src/v1/todo/repository.py`).
- Do not bypass soft-delete filtering for regular read paths when model has `deleted_at`.
  - Base behavior exists in `backend/src/core/db/base_repository.py`.
- Do not introduce schema changes without Alembic migration.
  - Current schema lifecycle is Alembic-based (`backend/alembic/env.py`, `backend/alembic/versions/*`).

---

## Uncertainties (documented, not invented)

- There is no repository-wide automated check that every query path includes soft-delete filtering; this is a convention enforced by base repository usage and review.
- Some repository method signatures still use broad `dict` input (`backend/src/v1/schedule_items/repository.py`), and a stricter typed policy is not fully codified in docs yet.