# ASDM DDL Context Analysis

## Overview

This document provides a comprehensive analysis of the DDL (Data Definition Language) context within the ASDM workspace framework. It examines the Context Builder toolset's approach to DDL context generation, the spec templates that define DDL structure, and the patterns for documenting database schemas, entity definitions, relationships, and migration strategies for AI model consumption.

---

## 1. ASDM Context Builder Architecture

### 1.1 Toolset Identity

| Property | Value |
|----------|-------|
| **Toolset ID** | `context-builder` |
| **Toolset Name** | Context Builder |
| **Version** | 0.0.2 |
| **Updated Date** | 2026-01-19 |
| **Description** | A toolset for building context for a workspace |

### 1.2 Context Builder Workflow

```mermaid
graph LR
    A[Analyze Workspace] --> B[Language Detection]
    B --> C[Generate index.md]
    C --> D[On-Demand Generation]
    D --> E[data-models.md]
    D --> F[architecture.md]
    D --> G[api.md]
    D --> H[deployment.md]
    D --> I[standard-project-structure.md]
    D --> J[standard-coding-style.md]
    E --> K[Context Injection]
    F --> K
    G --> K
    H --> K
    I --> K
    J --> K
    K --> L[Other Toolsets / AI Models]
```

### 1.3 Key Design Principles

- **Phased Generation**: Context files are generated one at a time to manage token usage and quality
- **Language Detection First**: Before any generation, detect the environment's response language (en/zh/etc.)
- **Spec-Driven**: Templates in `spec/` directory serve as starting points, customized per workspace analysis
- **Mermaid Diagrams**: Entity relationship, class, sequence, deployment, and architecture diagrams
- **Context Injection**: Generated context feeds other toolsets (PRD Builder, Prototype Builder, etc.)

---

## 2. DDL-Related Spec Templates

### 2.1 Spec Template Inventory

The Context Builder provides the following spec templates, with DDL-relevant content highlighted:

| Spec File | DDL Relevance | Key DDL Elements |
|-----------|--------------|-------------------|
| `data-models.md` | **Primary** | CREATE TABLE, CREATE INDEX, ER Diagrams, Migration SQL, TypeScript interfaces, Validation rules, Sample data |
| `architecture.md` | **High** | CREATE INDEX, PARTITION, MATERIALIZED VIEW, Migration patterns, Repository pattern with SQL queries |
| `deployment.md` | **Medium** | Database optimization (indexes, partitioning), Backup SQL scripts, pg_stat_statements queries |
| `api.md` | **Low** | No direct DDL, but defines API endpoints that interact with data models |
| `index.md` | **Low** | Links to DDL context files, technology stack (database type) |
| `standard-project-structure.md` | **Low** | Defines where DDL/migration files should reside in project structure |
| `standard-coding-style.md` | **Low** | No direct DDL, but defines naming conventions applicable to schema objects |

### 2.2 data-models.md Spec - DDL Structure Analysis

This is the **primary DDL context template**. Its structure includes:

```
data-models.md
├── Overview
├── Database Schema
│   ├── Overview Diagram (Mermaid erDiagram)
│   └── Entity Definitions
│       ├── TypeScript interfaces (entity representation)
│       ├── Enums (status/type definitions)
│       └── Purpose annotations
├── Data Relationships
│   ├── One-to-Many
│   ├── Many-to-Many
│   └── Self-Referencing
├── Data Flow Diagrams
│   ├── Sequence Diagrams (process flow)
│   └── State Diagrams (entity lifecycle)
├── Data Validation Rules
│   └── TypeScript validation rule objects
├── Data Migration Strategies
│   ├── CREATE TABLE (new tables)
│   ├── INSERT INTO ... SELECT (data migration)
│   ├── ALTER TABLE (schema evolution)
│   ├── CREATE TABLE + UNIQUE constraint (new entities)
│   └── CREATE INDEX (performance optimization)
├── Sample Data (JSON)
├── Data Access Patterns
│   ├── Repository Interfaces (TypeScript)
│   └── Query Optimization (CREATE INDEX)
└── Data Security
    ├── Encryption rules
    ├── Access Control (RBAC)
    └── Data Retention policies
```

#### DDL Patterns in data-models.md

**Pattern 1 - Entity Definition (CREATE TABLE)**:
```sql
CREATE TABLE user_profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  avatar_url VARCHAR(500),
  phone_number VARCHAR(20),
  address TEXT,
  date_of_birth DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

**Pattern 2 - Data Migration (INSERT INTO ... SELECT)**:
```sql
INSERT INTO user_profiles (user_id, phone_number, address)
SELECT id, phone, address FROM users WHERE phone IS NOT NULL OR address IS NOT NULL;
```

**Pattern 3 - Schema Evolution (ALTER TABLE)**:
```sql
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN address;
```

**Pattern 4 - New Entity with Constraints**:
```sql
CREATE TABLE product_attributes (
  id SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  attribute_name VARCHAR(100) NOT NULL,
  attribute_value TEXT NOT NULL,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(product_id, attribute_name)
);
```

**Pattern 5 - Performance Index**:
```sql
CREATE INDEX idx_product_attributes_product_id ON product_attributes(product_id);
```

**Pattern 6 - ER Diagram (Mermaid)**:
```mermaid
erDiagram
    USERS ||--o{ ORDERS : places
    USERS {
        int id PK
        string name
        string email UK
        string password_hash
        datetime created_at
        datetime updated_at
        boolean is_active
    }
    ORDERS ||--|{ ORDER_ITEMS : contains
    ORDERS {
        int id PK
        int user_id FK
        string order_number UK
        decimal total_amount
        string status
        datetime order_date
        datetime shipped_date
    }
```

### 2.3 architecture.md Spec - DDL Structure Analysis

**Pattern 7 - Composite Index**:
```sql
CREATE INDEX idx_orders_user_id_status ON orders(user_id, status);
```

**Pattern 8 - Table Partitioning**:
```sql
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
```

**Pattern 9 - Materialized View**:
```sql
CREATE MATERIALIZED VIEW product_sales_summary AS
SELECT 
  product_id,
  COUNT(*) as total_orders,
  SUM(quantity) as total_quantity,
  SUM(subtotal) as total_revenue
FROM order_items
GROUP BY product_id;
```

**Pattern 10 - Repository Pattern with SQL**:
```typescript
class PostgreSQLUserRepository implements UserRepository {
  async findById(id: number): Promise<User | null> {
    const result = await this.connection.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0] || null;
  }
}
```

**Pattern 11 - Migration Class (TypeScript)**:
```typescript
class AddUserProfileMigration {
  async up(connection: DatabaseConnection): Promise<void> {
    await connection.query(`CREATE TABLE user_profiles (...)`);
    await connection.query(`INSERT INTO user_profiles ...`);
  }
  async down(connection: DatabaseConnection): Promise<void> {
    await connection.query('DROP TABLE user_profiles');
  }
}
```

### 2.4 deployment.md Spec - DDL Structure Analysis

**Pattern 12 - Performance Optimization Indexes**:
```sql
CREATE INDEX idx_orders_user_id_status ON orders(user_id, status);
CREATE INDEX idx_products_category_id_price ON products(category_id, price);
```

**Pattern 13 - Partitioning for Large Tables**:
```sql
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
```

**Pattern 14 - Database Monitoring Queries**:
```sql
-- Check slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Check cache hit ratio
SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
```

---

## 3. DDL Context Taxonomy

### 3.1 DDL Statement Categories

The ASDM context-builder framework covers the following DDL statement categories:

| Category | SQL DDL Statements | Spec Source | AI Model Usage |
|----------|-------------------|-------------|----------------|
| **Table Definition** | CREATE TABLE, ALTER TABLE, DROP TABLE | data-models.md, architecture.md | Understand schema, design modifications |
| **Constraint Definition** | PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT | data-models.md | Enforce data integrity rules |
| **Index Definition** | CREATE INDEX (single, composite) | data-models.md, architecture.md, deployment.md | Optimize query performance |
| **View Definition** | CREATE MATERIALIZED VIEW | architecture.md | Understand derived data |
| **Partitioning** | CREATE TABLE ... PARTITION OF | architecture.md, deployment.md | Handle large table strategies |
| **Data Migration** | INSERT INTO ... SELECT, ALTER TABLE DROP COLUMN | data-models.md, architecture.md | Schema evolution lifecycle |
| **Monitoring** | pg_stat_statements, pg_statio_user_tables | deployment.md | Performance troubleshooting |

### 3.2 DDL Representation Formats

The framework uses multiple representation formats for DDL context:

```mermaid
graph TB
    subgraph "DDL Representation Formats"
        SQL[SQL DDL Statements]
        MERMAID_ER[Mermaid ER Diagram]
        MERMAID_SEQ[Mermaid Sequence Diagram]
        MERMAID_STATE[Mermaid State Diagram]
        TS_INT[TypeScript Interfaces]
        TS_ENUM[TypeScript Enums]
        TS_VALID[TypeScript Validation Rules]
        TS_REPO[TypeScript Repository Interfaces]
        TS_MIG[TypeScript Migration Classes]
        JSON_SAMPLE[JSON Sample Data]
    end

    subgraph "AI Model Consumption"
        UNDERSTAND[Understand Schema]
        DESIGN[Design Modifications]
        IMPLEMENT[Implement Changes]
        VALIDATE[Validate Data]
    end

    SQL --> UNDERSTAND
    MERMAID_ER --> UNDERSTAND
    TS_INT --> DESIGN
    TS_ENUM --> DESIGN
    TS_VALID --> VALIDATE
    TS_REPO --> IMPLEMENT
    TS_MIG --> IMPLEMENT
    JSON_SAMPLE --> UNDERSTAND
    MERMAID_SEQ --> UNDERSTAND
    MERMAID_STATE --> UNDERSTAND
```

| Format | Purpose | When Used |
|--------|---------|-----------|
| **SQL DDL** | Direct schema definition | CREATE TABLE, indexes, constraints, migrations |
| **Mermaid erDiagram** | Visual entity relationships | Overview section of data-models.md |
| **Mermaid sequenceDiagram** | Data flow between services | Data Flow Diagrams section |
| **Mermaid stateDiagram** | Entity lifecycle states | Data Flow Diagrams section |
| **TypeScript Interface** | Application-level entity representation | Entity Definitions section |
| **TypeScript Enum** | Status/type enumeration | Within Entity Definitions |
| **TypeScript Validation** | Data validation rules | Data Validation Rules section |
| **TypeScript Repository** | Data access pattern abstraction | Data Access Patterns section |
| **TypeScript Migration Class** | Schema evolution with up/down | Migration Strategies section (architecture.md) |
| **JSON Sample Data** | Concrete data examples | Sample Data section |

---

## 4. DDL Context Generation Process

### 4.1 Generation Flow

```mermaid
sequenceDiagram
    participant User
    participant AI as AI Model
    participant Spec as Spec Templates
    participant WS as Workspace Analysis
    participant CTX as .asdm/contexts/

    User->>AI: /asdm-context-build
    AI->>AI: Language Detection
    AI->>WS: Analyze Workspace
    WS-->>AI: Technology Stack, DB Schema, Code Patterns
    AI->>Spec: Load data-models.md spec
    Spec-->>AI: Template Structure & Examples
    AI->>CTX: Generate index.md
    User->>AI: Request data-models.md
    AI->>WS: Deep Analysis (DB schemas, models)
    AI->>Spec: Customize spec with real data
    AI->>CTX: Generate data-models.md
    User->>AI: Request architecture.md
    AI->>WS: Deep Analysis (architecture, patterns)
    AI->>Spec: Customize spec with real data
    AI->>CTX: Generate architecture.md
```

### 4.2 DDL Context Quality Criteria

When generating DDL context, the following quality criteria should be met:

| Criterion | Description | Verification Method |
|-----------|-------------|---------------------|
| **Completeness** | All tables, columns, constraints, indexes documented | Cross-reference with actual schema |
| **Accuracy** | DDL matches actual database implementation | Validate against source code/migrations |
| **Consistency** | Entity names/types consistent across all formats | TypeScript interfaces align with SQL columns |
| **Relationship Coverage** | All FK relationships documented with cardinality | Mermaid ER diagram includes all edges |
| **Migration History** | Schema evolution documented with up/down paths | Version-to-version migration scripts included |
| **Sample Data** | Representative JSON examples for each entity | Matches TypeScript interface shape |
| **Performance Context** | Indexes and optimization strategies documented | CREATE INDEX statements present |
| **Language Consistency** | All content in detected language | No mixed-language content |

---

## 5. DDL Context Integration Points

### 5.1 Context Injection Architecture

```mermaid
graph TB
    subgraph "Context Builder"
        CTX_FILES[.asdm/contexts/*.md]
    end

    subgraph "Consuming Toolsets"
        PRD[PRD Builder]
        PROTO[Prototype Builder]
        BASIC[Basic Tools]
    end

    subgraph "AI Model"
        AI_ENGINE[Agentic Engine]
    end

    CTX_FILES --> AI_ENGINE
    AI_ENGINE --> PRD
    AI_ENGINE --> PROTO
    AI_ENGINE --> BASIC
    PRD --> AI_ENGINE
    PROTO --> AI_ENGINE
    BASIC --> AI_ENGINE
```

### 5.2 How DDL Context Serves Other Toolsets

| Toolset | DDL Context Usage | Example |
|---------|-------------------|---------|
| **PRD Builder** | Understand data requirements for feature planning | Feature requires new table -> reference data-models.md |
| **Prototype Builder** | Design UI based on data structure | Form fields match entity interfaces |
| **Basic Tools** | Generate migration scripts for schema changes | ALTER TABLE based on data-models.md |
| **AI Models (General)** | Ground all code generation in actual schema | Generated code references correct table/column names |

### 5.3 Provider Integration

| Provider | Entry Point | Command Format | Frontmatter |
|----------|-------------|---------------|-------------|
| **Claude Code** | `.claude/commands/` | `/asdm-context-build` | YAML frontmatter with description |
| **GitHub Copilot** | `.github/prompts/` | `.prompt.md` files | YAML frontmatter with agent description |
| **Tencent CodeBuddy** | `.codebuddy/commands/` | `/asdm-context-build` | No frontmatter (direct copy) |

---

## 6. Current Workspace DDL State

### 6.1 Workspace Assessment

| Aspect | Status | Details |
|--------|--------|---------|
| **Project Source Code** | None | This is a "workspace-without-repo" - no actual code |
| **DDL Files (.sql/.ddl)** | None | No standalone DDL or SQL files present |
| **Database Schemas** | None | No actual schema definitions |
| **Data Models** | Template only | Example entities in spec templates (Users, Orders, Products, Categories) |
| **Context Files** | Empty | `.asdm/contexts/` directory exists but has no files |
| **Prototypes** | Empty | `.asdm/prototypes/` directory exists but has no files |
| **Features** | Empty | `.asdm/workspace/features/` directory exists but has no files |

### 6.2 Available DDL Context Sources

Since no actual project source code exists, the DDL context can only be derived from:

1. **Spec Template Examples** - The template/example DDL in `data-models.md` and `architecture.md` specs
2. **ASDM Framework Structure** - The workspace organization and toolset definitions
3. **Future Project Code** - Once a project is added to this workspace, real DDL can be analyzed

### 6.3 Workspace File Tree

```
/var/jenkins_home/workspace/asdm-workspace-exec/asdm-workspace-without-repo/
├── .asdm/                          # ASDM configuration and toolsets
│   ├── contexts/                   # EMPTY - no context files built yet
│   ├── prototypes/                 # EMPTY - no prototypes created yet
│   ├── workspace/
│   │   └── features/               # EMPTY - no features defined yet
│   └── toolsets/
│       ├── basic-tools/            # Git operations and project startup
│       ├── context-builder/        # Context generation for AI models
│       │   ├── INSTALL.md          # Installation instructions
│       │   ├── README.md           # Toolset documentation
│       │   ├── actions/
│       │   │   ├── asdm-context-build.md    # Build context instruction
│       │   │   └── asdm-context-update.md   # Update context instruction
│       │   └── specs/
│       │       ├── index.md                  # Index template
│       │       ├── data-models.md            # DDL + data models template
│       │       ├── architecture.md           # Architecture + DDL template
│       │       ├── api.md                    # API template
│       │       ├── deployment.md             # Deployment + DB optimization template
│       │       ├── standard-project-structure.md # Project structure template
│       │       └── standard-coding-style.md  # Coding style template
│       ├── prd-builder/            # PRD planning and execution
│       └── prototype-builder/     # UI/prototype generation
│           ├── tools/
│           │   ├── prototype-generator.js   # Prototype generation script
│           │   └── stack-installer.js       # Technology stack installer
│           └── ... (specs, actions)
├── .codebuddy/                     # Tencent CodeBuddy configuration
│   └── commands/                   # Slash command shortcuts (10 commands)
├── .workspace/                     # EMPTY - target for generated files
└── (no other files)               # Bare workspace - no project source code
```

---

## 7. DDL Context Generation Recommendations

### 7.1 When Project Source Code Exists

Once a project with actual database schemas is added to this workspace, the DDL context generation should follow this process:

1. **Scan for DDL sources**: Search for SQL files, migration scripts, ORM entity definitions, schema configuration files
2. **Extract entity definitions**: Parse CREATE TABLE statements, JPA annotations, TypeScript model classes
3. **Map relationships**: Identify foreign keys, join tables, self-references, polymorphic associations
4. **Generate ER diagram**: Create Mermaid erDiagram covering all entities and their cardinality
5. **Document constraints**: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT values
6. **Catalog indexes**: Single-column, composite, unique, partial indexes with rationale
7. **Trace migration history**: Document schema evolution from initial version to current state
8. **Provide sample data**: Generate representative JSON examples for each entity
9. **Define validation rules**: Map constraints to TypeScript/application-level validation
10. **Describe access patterns**: Repository interfaces with common query patterns

### 7.2 DDL Context File Naming Convention

Generated DDL context files should follow the ASDM naming convention:

| File | Location | Content |
|------|----------|---------|
| `asdm.data-models.md` | `.asdm/contexts/` | Primary DDL context (tables, relationships, ER diagrams, migrations) |
| `asdm.architecture.md` | `.asdm/contexts/` | Architecture DDL context (partitioning, materialized views, indexes) |
| `asdm.deployment.md` | `.asdm/contexts/` | Deployment DDL context (DB optimization, monitoring queries, backup) |
| `asdm.index.md` | `.asdm/contexts/` | Links to all DDL context files |

### 7.3 Update Triggers for DDL Context

DDL context should be updated when:

- New tables or columns are added/removed
- Constraints or indexes are modified
- Migration scripts are created or altered
- ORM entity definitions change
- Database technology changes (e.g., PostgreSQL to MySQL)
- Partitioning or materialized view strategies are added
- Data validation rules are updated
- Sample data patterns change significantly

---

## 8. Summary

The ASDM Context Builder provides a structured framework for generating DDL context that AI models can consume. The framework:

- **Defines 7 spec templates** covering data models, architecture, deployment, APIs, project structure, coding style, and index
- **Uses multiple representation formats** (SQL DDL, Mermaid diagrams, TypeScript interfaces, JSON sample data) to convey DDL information
- **Follows a phased generation process** with language detection as the first step
- **Supports 3 AI providers** (Claude Code, GitHub Copilot, Tencent CodeBuddy) with provider-specific command integration
- **Enables context injection** where generated DDL context serves other toolsets (PRD Builder, Prototype Builder)
- **Currently in a bare state** with no actual project source code - only template/example DDL content exists

The primary DDL context file (`data-models.md`) covers the full lifecycle: entity definition, relationship mapping, validation rules, migration strategies, access patterns, and security considerations. The architecture and deployment specs add advanced DDL patterns like partitioning, materialized views, and performance monitoring queries.

---

*This DDL context analysis document is based on the ASDM Context Builder toolset (version 0.0.2). When actual project source code is added to the workspace, this analysis should be updated using `/asdm-context-update` to reflect real DDL structures.*