Entity Design

Guide for designing and documenting domain entities in a consistent, well-structured format.

When to Use

• •Designing new domain entities
• •Documenting existing entity models
• •Adding fields to existing entities
• •Reviewing entity relationships
• •Planning database schema changes

Entity Model Document Structure

Entity model documents should follow this structure:

1. Overview Section

## Overview

Brief description of what this entity model covers and its purpose.

2. EntityBase Section

All entities inherit common audit fields:

## EntityBase

All entities in the system inherit these audit fields:

| Field | Type | Description | Example |
|-------|------|-------------|---------|
| `CreatedAtUtc` | datetime | UTC timestamp when record was created | 2026-01-09T15:30:00Z |
| `CreatedBy` | string | User/system that created the record | "system" |
| `UpdatedAtUtc` | datetime | UTC timestamp when record was last updated | 2026-01-09T16:45:00Z |
| `UpdatedBy` | string | User/system that last updated the record | "admin" |
| `DeletedAtUtc` | datetime (nullable) | UTC timestamp when record was soft-deleted | 2026-01-10T10:00:00Z |
| `DeletedBy` | string (nullable) | User/system that soft-deleted the record | "admin" |

3. Individual Entity Sections

Each entity should follow this template:

### EntityName

**Purpose:** One-line description of what this entity represents

| Field | Type | Description | Example |
|-------|------|-------------|---------|
| `Id` | int | Primary key | 42 |
| `FieldName` | type | Description | example_value |

**Enums:**
- `EnumField`: VALUE_1, VALUE_2, VALUE_3

**Relationships:**
- EntityName 1 → N OtherEntity
- N → 1 ParentEntity

**Notes:**
- Important implementation notes
- Constraints or business rules

Field Naming Conventions

This project uses PascalCase for both C# properties and database columns (EF Core default behavior):

• •CreatedAtUtc - not created_at_utc
• •SenderId - not sender_id
• •IsDeleted - not is_deleted

Primary Keys

• •Use auto-increment integers: Id | int
• •Named simply Id (not EntityNameId)

Foreign Keys

• •Reference the full field name: ParentEntityId | int
• •Add "Foreign key to X" in description

Common Types

• •string - Text fields
• •string (nullable) - Optional text
• •int - Integers
• •decimal - Numbers with decimals
• •bool - True/false
• •datetime - Timestamps (always UTC unless Local suffix)
• •jsonb - JSON data (complex structures, arrays)
• •enum - Enumerated values (list in Enums section)

Nullable Fields

• •Append (nullable) to type: string (nullable)
• •Only mark nullable if null has semantic meaning

Relationship Notation

Use arrows to show cardinality:

• •1 → N - One-to-many
• •N → 1 - Many-to-one
• •1 → 1 - One-to-one
• •N ↔ N - Many-to-many (note the join entity)

Include context when helpful:

• •Email 1 → N Attachment
• •N → 1 Sender
• •MeetingRequest 1 → 1 CalendarEvent (optional)

Design Principles

1. Soft Deletes

Never hard delete records. Always use:

• •DeletedAtUtc - When deleted
• •DeletedBy - Who deleted

2. UTC Timestamps

• •Primary timestamps always UTC with Utc suffix
• •Local timestamps optional for display, use Local suffix
• •Store timezone separately if needed

3. Integer Primary Keys

• •Auto-increment integers for simplicity
• •Good SQLite performance
• •Easy to reference in logs and debugging

4. Audit Fields

Every entity inherits EntityBase audit fields automatically.

5. JSONB for Complex Data

Use JSONB for:

• •Variable-length arrays
• •Nested structures
• •Configuration objects
• •Avoid over-normalisation

Example Entity

### Order

**Purpose:** Customer order containing one or more line items

| Field | Type | Description | Example |
|-------|------|-------------|---------|
| `Id` | int | Primary key | 1234 |
| `CustomerId` | int | Foreign key to Customer | 42 |
| `OrderNumber` | string | Human-readable order number | "ORD-2026-00123" |
| `Status` | enum | Current order state | "CONFIRMED" |
| `TotalAmount` | decimal | Order total | 150.00 |
| `Currency` | string (ISO 4217) | Currency code | "USD" |
| `PlacedAtUtc` | datetime | When order was placed | 2026-01-15T14:00:00Z |
| `Notes` | string (nullable) | Customer notes | "Leave at door" |
| `Metadata` | jsonb (nullable) | Additional data | `{"source": "web"}` |

**Enums:**
- `Status`: DRAFT, CONFIRMED, PROCESSING, SHIPPED, DELIVERED, CANCELLED

**Relationships:**
- N → 1 Customer
- Order 1 → N OrderLineItem
- Order 1 → N OrderStatusHistory

**Notes:**
- Order number generated on confirmation, not creation
- Total calculated from line items, stored for performance

Key Design Decisions Section

Document major architectural decisions at the end:

## Key Design Decisions

### 1. Decision Name
- What was decided
- Why this approach
- Trade-offs considered

### 2. Another Decision
- What was decided
- Why this approach

Checklist

• • Overview section describes the entity model scope
• • EntityBase documented with audit fields
• • Each entity has Purpose, Fields table, Enums, Relationships
• • Field names use PascalCase
• • Primary keys are int named Id
• • Foreign keys are int referencing parent Id
• • Nullable fields explicitly marked
• • Enums listed with all values
• • Relationships use correct notation
• • Notes capture important constraints
• • Entity Relationship Summary provides overview
• • Key Design Decisions documented