bee:pre-dev-data-model

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:

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

3. Create db-standards-ref.md with converted names + mapping table
4. 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:

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"

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

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

1. ✅ Lock data model - entity structure is now reference
2. 🎯 Use model as input for Dependency Map (bee: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.

---

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