Skip to main content

Database

Overview​

Database architecture domain: schema design, integrity rules, access patterns, and migration guidance.

Documents​

DocumentDescription
database-design.mdEntities, relationships, ERD, constraints, and access patterns

Record database decisions in 04-decisions and link them here as they are accepted (database engine, ORM, migration strategy).

Scope​

This domain covers:

  • Logical and physical schema design
  • Constraints and integrity rules
  • Indexing and access patterns
  • Data access control
  • Migration and evolution strategy

Requirements Coverage​

RequirementSchema Element(s)Notes
[FR-xxx-xx][table/column][How the schema satisfies it]
[NFR-Xnn][table/column/index][How the schema satisfies it]

Database Best Practices Applied​

Adapt this checklist to your engine (PostgreSQL shown as a common default).

Schema Design​

  • Every table has a primary key and created_at / updated_at timestamps.
  • Enums are stored as constrained values (check constraints or lookup tables).
  • Soft deletion is explicit (status column or deleted_at) where retention rules require it.

Indexing Strategy​

  • Index foreign keys and columns used in list filters.
  • Composite indexes follow query access patterns (most selective column first).
  • Review index usage after real query load appears.

Security & Access Control​

  • Application connects with least-privilege credentials.
  • Row-level access rules are enforced [at the database layer / in application queries].
  • Sensitive columns are identified and protected (see Security Architecture).

Data Integrity​

  • Foreign keys and NOT NULL constraints enforced at the database level.
  • Business invariants that span tables are enforced in the domain layer with transactional boundaries.

Connection Management​

  • Pooling configured per environment; pool sizes documented.
  • Connection limits aligned with the deployment platform constraints.

Source References​