data-model

Data Model Design Skill

Interactive workflow for designing database schemas, generating migrations, and creating repository interfaces for multi-tenant SaaS applications.

Workflow

Step 1: Identify Entities and Relationships

Analyze the feature requirements to identify:

• Entities: Core data objects (e.g., User, Project, Invoice)
• Relationships: One-to-one, one-to-many, many-to-many
• Aggregate roots: Which entities are accessed independently vs. through a parent?
• Value objects: Embedded data that doesn't have its own lifecycle

Output: Entity relationship diagram in Mermaid format.

erDiagram
    TENANT ||--o{ USER : has
    TENANT ||--o{ PROJECT : has
    PROJECT ||--o{ TASK : contains
    USER }o--o{ PROJECT : "member of"

Step 2: Design Tables with Multi-Tenant Columns

For each entity, design the table following .claude/rules/data-patterns.md:

Every table MUST include the base entity fields:

• id (UUID, primary key)
• tenant_id (UUID, NOT NULL, foreign key to tenants)
• created_at (TIMESTAMPTZ, NOT NULL, DEFAULT now())
• updated_at (TIMESTAMPTZ, NOT NULL, DEFAULT now())
• deleted_at (TIMESTAMPTZ, nullable for soft delete)
• created_by (UUID, foreign key to users)
• updated_by (UUID, foreign key to users)

Plus entity-specific columns with proper types, constraints, and defaults.

Output: Complete table definitions with column types and constraints.

Step 3: Define Indexes and Constraints

For each table, define:

• Primary key: UUID (default gen_random_uuid())
• Foreign keys: All relationships with ON DELETE behavior
• Unique constraints: Business uniqueness rules (e.g., UNIQUE(tenant_id, email))
• Indexes: Foreign keys, common query patterns, search fields
• Check constraints: Enum validation, range validation
• Partial indexes: Active-only queries (WHERE deleted_at IS NULL)

Rules:

• Every foreign key MUST have an index (Postgres doesn't auto-create them)
• Composite indexes: most selective column first
• Use CREATE INDEX CONCURRENTLY for large tables