Data Modeling - Defining Data Structures

Foundational Principle

Data structures, relationships, and ownership must be defined before database technology selection.

Jumping to database-specific schemas without modeling creates:

• Inconsistent data structures across services

• Unclear data ownership and authority

• Schema conflicts discovered during development

• Migration nightmares when requirements change

The Data Model answers: WHAT data exists, HOW entities relate, WHO owns what data? The Data Model never answers: WHICH database technology or HOW to implement storage.

Phase 0: Database Field Naming Strategy (MANDATORY)

Before defining schemas, determine how to name database fields.

Step 1: Check if Gate 4 API standards exist

Check if docs/pre-dev/{feature-name}/api-standards-ref.md exists:

ls docs/pre-dev/{feature-name}/api-standards-ref.md

Step 2: Ask user about database field naming

Use AskUserQuestion tool:

If api-standards-ref.md EXISTS:

Question: "Gate 4 defined API field names (e.g., userId , createdAt ). How should database fields be named?"

• Header: "DB Field Naming"

• multiSelect: false

• Options:

• "Convert to snake_case (Recommended)" (description: "API: userId → DB: user_id (PostgreSQL/MySQL standard)")

• "Keep same as API (camelCase)" (description: "API: userId → DB: userId (MongoDB/document DB)")

• "Different standards - provide DB dictionary" (description: "I have a separate database naming standards document")

• "Define manually for this feature" (description: "No standards, I'll specify field names")

If api-standards-ref.md DOES NOT EXIST:

Question: "No API standards were defined in Gate 4. How should database fields be named?"

• Header: "DB Field Naming"

• multiSelect: false

• Options:

• "Use snake_case (Recommended)" (description: "Standard for PostgreSQL/MySQL: user_id, created_at")

• "Use camelCase" (description: "Standard for MongoDB/document DBs: userId, createdAt")

• "Load from standards document" (description: "I have a database naming standards document (URL or file)")

• "Define manually" (description: "No standards, I'll specify per feature")

Step 3: Process user selection

Option 1 Selected: "Convert to snake_case"

• Load api-standards-ref.md from Gate 4

• Apply automatic conversion rules:

API Field (camelCase) DB Column (snake_case) Rule

userId user_id Split on capital letters, join with underscore

createdAt created_at Split on capital letters, join with underscore

isActive is_active Preserve boolean prefix

phoneNumber phone_number Split on capital letters within words

userID user_id Collapse consecutive capitals

• Create db-standards-ref.md with converted names + mapping table

• Document conversion rule: "Automatic camelCase → snake_case conversion"

Option 2 Selected: "Keep same as API"

• Load api-standards-ref.md from Gate 4

• Copy field names without modification

• Create db-standards-ref.md with same names

• Document strategy: "Database uses same field names as API (camelCase)"

Option 3 Selected: "Different standards - provide DB dictionary"

• Ask for URL or file path (same as Gate 4 Phase 0)

• Load and extract database-specific standards

• If conflicts with API standards: create mapping table

• Document source and differences

Additionally extract for database context:

Database-Specific Element What to Extract

Table naming Singular vs plural (user vs users )

Column naming snake_case, camelCase, PascalCase

Primary key naming id , {table}_id , uuid

Foreign key naming {table}_id , {table}_uuid

Timestamp columns created_at /updated_at vs createdAt /updatedAt

Boolean prefixes is_ , has_ , no prefix

Junction table naming user_role vs users_roles vs user_roles

Option 4 Selected: "Define manually"

• Proceed without standards reference

• Document in db-standards-ref.md : "No organizational standards, fields defined per-feature"

Step 4: Generate db-standards-ref.md

Output to: docs/pre-dev/{feature-name}/db-standards-ref.md

If conversion from API standards (Option 1 or 2):

Database Standards Reference - {Feature Name}

Source: Converted from api-standards-ref.md (Gate 4) Conversion: {camelCase → snake_case / same as API} Generated: {ISO 8601 timestamp}

Field Naming Convention

Database pattern: {snake_case / camelCase} Source: API standards (Gate 4) with automatic conversion

Standard Fields

API to Database Mapping

Conversion Rules

• camelCase → snake_case: Insert underscore before capitals, lowercase all
• Consecutive capitals: Treat as acronym (userID → user_id, not user_i_d)
• Boolean prefixes: Preserve (isActive → is_active, hasPermission → has_permission)

If loaded from separate dictionary (Option 3):

Follow shared-patterns/standards-discovery.md workflow, including database-specific extractions.

Mandatory Workflow

Phase Activities

0. Database Field Naming Strategy Check if Gate 4 API standards exist; ask user: reuse with conversion (snake_case/camelCase), load separate DB dictionary, or define manually; generate db-standards-ref.md with mapping if applicable

1. Data Analysis Load approved API Design (Gate 4), TRD (Gate 3), Feature Map (Gate 2), PRD (Gate 1); extract entities from contracts; identify relationships

2. Data Modeling Define entities, specify attributes, model relationships, assign ownership, define constraints, plan lifecycle, design access patterns, consider data quality; apply field naming strategy from Phase 0

3. Gate 5 Validation Verify all checkboxes before proceeding to Dependency Map

Explicit Rules

✅ DO Include

Entity definitions (conceptual data objects), attributes with types, constraints (required, unique, ranges), relationships (1:1, 1:N, M:N), data ownership (authoritative component), primary identifiers, lifecycle rules (soft delete, archival), access patterns, data quality rules, referential integrity

❌ NEVER Include

Database products (PostgreSQL, MongoDB, Redis), table/collection names, index definitions, SQL/query language, ORM frameworks (Prisma, TypeORM), storage engines, partitioning/sharding, replication/backup, database-specific types (JSONB, BIGSERIAL)

Abstraction Rules

Element Abstract (✅) Database-Specific (❌)

Entity "User" "users table"

Attribute "emailAddress: String (email format)" "email VARCHAR(255)"

Relationship "User has many Orders" "foreign key user_id"

Identifier "Unique identifier" "UUID primary key"

Constraint "Must be unique" "UNIQUE INDEX"

Rationalization Table

Excuse Reality

"We know it's PostgreSQL, just use PG types" Database choice comes later. Model abstractly now.

"Table design is data modeling" Tables are implementation. Entities are concepts. Stay conceptual.

"We need indexes for performance" Indexes are optimization. Model data first, optimize later.

"ORMs require specific schemas" ORMs adapt to models. Don't let tooling drive design.

"Foreign keys define relationships" Relationships exist conceptually. FKs are implementation.

"SQL examples help clarity" Abstract models are clearer. SQL is implementation detail.

"NoSQL doesn't need relationships" All systems have data relationships. Model them regardless of DB type.

"This is just ERD" ERD is visualization tool. Data model is broader (ownership, lifecycle, etc).

"We can skip this for simple CRUD" Even CRUD needs clear entity design. Don't skip.

"Microservices mean no relationships" Services interact via data. Model entities per service.

Red Flags - STOP

If you catch yourself writing any of these in Data Model, STOP:

• Database product names (Postgres, MySQL, Mongo, Redis)

• SQL keywords (CREATE TABLE, ALTER TABLE, SELECT, JOIN)

• Database-specific types (SERIAL, JSONB, VARCHAR, TEXT)

• Index commands (CREATE INDEX, UNIQUE INDEX)

• ORM code (Prisma schema, TypeORM decorators)

• Storage details (partitioning, sharding, replication)

• Query optimization (EXPLAIN plans, index hints)

• Backup/recovery strategies

When you catch yourself: Replace DB detail with abstract concept. "users table" → "User entity"

Gate 5 Validation Checklist

Category Requirements

Entity Completeness All entities from PRD/Feature Map modeled; clear consistent names; defined purpose; boundaries align with TRD components

Attribute Specification All types specified; required vs optional explicit; constraints documented; defaults where relevant; computed fields identified

Relationship Modeling All relationships documented; cardinality specified (1:1, 1:N, M:N); optional vs required clear; referential integrity to be documented; circular deps resolved

Data Ownership Each entity owned by exactly one component; read/write permissions documented; cross-component access via APIs only; no shared database anti-pattern

Data Quality Validation rules specified; normalization level appropriate; denormalization justified; consistency strategy defined

Lifecycle Management Creation rules; update patterns; deletion strategy (hard/soft); archival/retention policies; audit trail needs

Access Patterns Primary patterns documented; query needs identified; write patterns documented; consistency requirements specified

Technology Agnostic No database products; no SQL/NoSQL specifics; no table/index definitions; implementable in any DB

Gate Result: ✅ PASS (all checked) → Dependency Map | ⚠️ CONDITIONAL (remove DB specifics) | ❌ FAIL (incomplete/poor ownership)

Data Model Template Structure

Output to (path depends on topology.structure):

• single-repo: docs/pre-dev/{feature-name}/data-model.md

• monorepo/multi-repo: {backend.path}/docs/pre-dev/{feature-name}/data-model.md

Section Content

Overview API Design/TRD/Feature Map references, status, last updated

Data Ownership Map Table: Entity, Owning Component, Read Access, Write Access

Per-Entity Structure

Field Content

Purpose What this entity represents

Owned By Component from TRD

Primary Identifier Unique identifier field and format

Attributes Table: Attribute, Type, Required, Unique, Constraints, Description

Nested Types Embedded types (e.g., OrderItem within Order, Address value object)

Relationships Cardinality notation: Entity (1) ──< has many >── (*) OtherEntity

Constraints Business rules, status transitions, referential integrity

Lifecycle Creation (via which API), updates, deletion strategy, archival

Access Patterns Lookup patterns by frequency (primary, secondary, rare)

Data Quality Normalization rules, validation

Additional Sections

Section Content

Relationship Diagram ASCII/text diagram showing entity relationships with cardinality legend

Cross-Component Access Per scenario: data flow steps, rules (no direct DB access, API only)

Consistency Strategy Strong consistency (immediate): auth, payments, inventory; Eventual (delay OK): analytics, search

Validation Rules Per-entity and cross-entity validation

Lifecycle Policies Retention periods table, soft delete strategy, audit trail requirements

Privacy & Compliance PII fields table with handling, GDPR compliance, encryption needs (algorithm TBD)

Access Pattern Analysis High/medium/low frequency patterns with req/sec estimates, optimization notes for later

Data Quality Standards Normalization rules, validation approach, integrity enforcement

Migration Strategy Schema evolution (additive, non-breaking, breaking), versioning approach

Gate 5 Validation Date, validator, checklist, approval status

Common Violations

Violation Wrong Correct

Database Schema CREATE TABLE users (id UUID PRIMARY KEY, email VARCHAR(255) UNIQUE)

Entity User with attributes table: userId (Identifier, Unique), email (EmailAddress, Unique)

ORM Code TypeScript with @Entity(), @PrimaryGeneratedColumn('uuid'), @Column decorators Entity User with primary identifier, attributes list, constraints description

Technology in Relationships "Foreign key user_id references users.id; Join table user_roles" "User (1:N) Order; User (M:N) Role" with cardinality descriptions

Confidence Scoring

Factor Points Criteria

Entity Coverage 0-30 All entities: 30, Most: 20, Gaps: 10

Relationship Clarity 0-25 All documented: 25, Most clear: 15, Ambiguous: 5

Data Ownership 0-25 Clear boundaries: 25, Minor overlaps: 15, Unclear: 5

Constraint Completeness 0-20 All rules: 20, Common cases: 12, Minimal: 5

Action: 80+ autonomous generation | 50-79 present options | <50 ask clarifying questions

Document Placement

data-model.md is a backend document - it defines entity structures owned by backend services.

Structure data-model.md Location

single-repo docs/pre-dev/{feature}/data-model.md

monorepo {backend.path}/docs/pre-dev/{feature}/data-model.md

multi-repo {backend.path}/docs/pre-dev/{feature}/data-model.md

Why backend path? Data models are:

• Implemented as database schemas by backend engineers

• Define entities owned by backend components

• Versioned with backend database migrations

Directory creation for multi-module:

Read topology from research.md frontmatter

backend_path="${topology_modules_backend_path:-"."}" mkdir -p "${backend_path}/docs/pre-dev/{feature}"

After Approval

• ✅ Lock data model - entity structure is now reference

• 🎯 Use model as input for Dependency Map (ring:pre-dev-dependency-map )

• 🚫 Never add database specifics retroactively

• 📋 Keep technology-agnostic until Dependency Map

The Bottom Line

If you wrote SQL schemas or ORM code, delete it and model abstractly.

Data modeling is conceptual. Period. No database products. No SQL. No ORMs.

Database technology goes in Dependency Map. That's the next phase. Wait for it.

Model the data. Stay abstract. Choose database later.

Standards Loading (MANDATORY)

This skill is a data modeling skill and does NOT require WebFetch of language-specific standards.

Purpose: Data Model defines WHAT data exists at a conceptual level. Database-specific patterns and ORM standards are irrelevant at this stage—they apply during Dependency Map (Gate 6) and implementation.

However, MUST load API Design (Gate 4) and TRD (Gate 3) artifacts to ensure data model aligns with component boundaries and API contracts.

Blocker Criteria - STOP and Report

Condition Action Severity

API Design (Gate 4) not validated STOP and complete Gate 4 first CRITICAL

Database-specific syntax in model STOP and abstract to conceptual entities HIGH

Entity ownership unclear (multiple owners) STOP and define single owner per entity HIGH

Data Model conflicts with API contracts STOP and reconcile with Gate 4 API Design HIGH

Relationship cardinality ambiguous STOP and specify 1:1, 1:N, or M:N MEDIUM

db-standards-ref.md cannot be created STOP and resolve field naming strategy MEDIUM

Cannot Be Overridden

These requirements are NON-NEGOTIABLE:

• MUST NOT include database product names (PostgreSQL, MongoDB, etc.)

• MUST NOT include SQL syntax or ORM decorators

• MUST NOT include table names or index definitions

• MUST define entity ownership (exactly one component per entity)

• MUST specify relationship cardinality for all relationships

• MUST complete Phase 0 (DB Field Naming Strategy) before modeling

• CANNOT proceed to Gate 6 with database-specific content

Severity Calibration

Severity Definition Example

CRITICAL Cannot proceed with data modeling Gate 4 not validated, no API contracts to reference

HIGH Model contains forbidden content SQL syntax, table definitions, ORM code

MEDIUM Model incomplete Missing ownership for some entities

LOW Minor quality issues Inconsistent naming conventions

Pressure Resistance

User Says Your Response

"We know it's PostgreSQL, just use PG types" "Cannot use database-specific types. Model abstractly now. PostgreSQL selection happens in Gate 6."

"Add the CREATE TABLE statements" "Cannot include SQL. Tables are implementation. Define entities conceptually, implement later."

"Include the foreign key constraints" "Cannot include FK syntax. Model relationships as 'User has many Orders'. FK is implementation."

"Skip Phase 0, naming is obvious" "Cannot skip field naming strategy. Mismatch between API and DB names causes bugs. Define strategy now."

"ORMs require specific schemas" "Cannot include ORM code. ORMs adapt to models, not vice versa. Model first, ORM later."

Anti-Rationalization

Rationalization Why It's WRONG Required Action

"We know it's PostgreSQL, just use PG types" Database choice comes later. Model abstractly now Use abstract types (String, Integer, Timestamp)

"Table design IS data modeling" Tables are implementation. Entities are concepts Model entities, not tables

"We need indexes for performance" Indexes are optimization. Model data first Skip indexes, add in implementation

"ORMs require specific schemas" ORMs adapt to models. Don't let tooling drive design Keep model ORM-agnostic

"Foreign keys define relationships" Relationships exist conceptually. FKs are implementation Model relationships, not FKs

"NoSQL doesn't need relationships" All systems have data relationships. Model regardless Model all entity relationships

When This Skill Is Not Needed

• Small Track workflow (skip to Task Breakdown)

• No persistent data in feature (skip to Dependency Map)

• API Design (Gate 4) not validated (complete Gate 4 first)

• Data Model already exists and is validated

• Pure API changes with no data impact

• UI-only features with no backend data changes