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:
  1. •"Convert to snake_case (Recommended)" (description: "API: userId → DB: user_id (PostgreSQL/MySQL standard)")
  2. •"Keep same as API (camelCase)" (description: "API: userId → DB: userId (MongoDB/document DB)")
  3. •"Different standards - provide DB dictionary" (description: "I have a separate database naming standards document")
  4. •"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:
  1. •"Use snake_case (Recommended)" (description: "Standard for PostgreSQL/MySQL: user_id, created_at")
  2. •"Use camelCase" (description: "Standard for MongoDB/document DBs: userId, createdAt")
  3. •"Load from standards document" (description: "I have a database naming standards document (URL or file)")
  4. •"Define manually" (description: "No standards, I'll specify per feature")

Step 3: Process user selection

Option 1 Selected: "Convert to snake_case"

1. •Load api-standards-ref.md from Gate 4
2. •Apply automatic conversion rules:

1. •Create db-standards-ref.md with converted names + mapping table
2. •Document conversion rule: "Automatic camelCase → snake_case conversion"

Option 2 Selected: "Keep same as API"

1. •Load api-standards-ref.md from Gate 4
2. •Copy field names without modification
3. •Create db-standards-ref.md with same names
4. •Document strategy: "Database uses same field names as API (camelCase)"

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

1. •Ask for URL or file path (same as Gate 4 Phase 0)
2. •Load and extract database-specific standards
3. •If conflicts with API standards: create mapping table
4. •Document source and differences

Additionally extract for database context:

Option 4 Selected: "Define manually"

1. •Proceed without standards reference
2. •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

| DB Column | API Field | Type | Example |
|-----------|-----------|------|---------|
| user_id | userId | uuid | "550e8400-e29b-41d4-a716-446655440000" |
| email | email | varchar(254) | "user@example.com" |
| created_at | createdAt | timestamptz | "2026-01-23T10:30:00Z" |
| is_active | isActive | boolean | true |

## API to Database Mapping

| API Field | DB Column | Type Conversion |
|-----------|-----------|-----------------|
| userId (string) | user_id (uuid) | Parse UUID string → uuid type |
| createdAt (ISO 8601 string) | created_at (timestamptz) | Parse ISO → timestamptz |
| isActive (boolean) | is_active (boolean) | Direct 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

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

Rationalization Table

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

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

Data Model Template Structure

Output to docs/pre-dev/{feature-name}/data-model.md with these sections:

Per-Entity Structure

Additional Sections

Common Violations

Confidence Scoring

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

After Approval

1. •✅ Lock data model - entity structure is now reference
2. •🎯 Use model as input for Dependency Map (ring:pre-dev-dependency-map)
3. •🚫 Never add database specifics retroactively
4. •📋 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.