By name: spec-writing-database description: Database schema specification using Mermaid ER diagrams, table structures, constraints, and indexes for multi-tenant applications.
Database Schema Specification Patterns
Use when designing database schemas with Mermaid ER diagrams, tables, constraints, and indexes for multi-tenant applications. IMPORTANT: Specify WHAT needs to be built (data structure, relationships, business rules), NOT HOW to build it (no Entity Framework entity classes or fluent configuration code).
When to use
- Creating ER diagrams for new features or schema documentation
- Defining table structures with columns, data types, defaults, and constraints
- Specifying CHECK, UNIQUE, and FOREIGN KEY constraints for data integrity
- Designing indexes for tenant-filtered queries, uniqueness, and performance
- Planning additive or breaking schema migrations
Core principles
- Use Mermaid erDiagram syntax with PK/FK/UK annotations and field descriptions
- All domain entities include TenantId FK; unique constraints scoped as (TenantId, BusinessKey)
- Indexes lead with TenantId; use INCLUDE for covering indexes; filter WHERE for active records
- Integer PKs for performance; GUID alternate keys for external APIs; audit timestamps (CreatedAt, UpdatedAt)
- CHECK constraints enforce business rules; FK cascade rules respect relationships (RESTRICT, CASCADE)
- Breaking changes require multi-step migrations with application deployments between steps
Resources
- Mermaid ER diagrams: patterns/mermaid-er-diagrams.md
- Table structures: patterns/table-structures.md
- Constraints: patterns/constraints.md
- Indexes: patterns/indexes.md
- Design principles: patterns/design-principles.md
- Schema migrations: patterns/schema-migrations.md
Default locations
- Specifications: docs/specs/{feature-name}.md (Database Schema section)
- Mermaid diagrams embedded in markdown specs
- Migration scripts referenced but not specified in detail
Validation checklist
- ER diagram shows all entities, relationships (||--o{, etc.), and field annotations (PK, FK, UK)
- Tables include TenantId FK, integer PK, GUID UK, audit timestamps (CreatedAt, UpdatedAt), and defaults
- Composite unique constraints: (TenantId, Name) or (TenantId, BusinessGuid)
- CHECK constraints validate business rules (capacity ranges, dates, prices, non-empty strings)
- FK constraints specify ON DELETE behavior (RESTRICT for parents, CASCADE for children)
- Indexes: TenantId first column, INCLUDE for projections, filtered WHERE for active records
- Design principles documented: tenant isolation, integer PKs, GUID UKs, audit fields, soft deletes
- Migration strategy specified: additive (backward-compatible) vs breaking (multi-step)