social-app/.trellis/spec/backend/database-guidelines.md

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.