Imported from hariinfo/agentic-code-template (.claude/skills/design-documents/SKILL.md). Install upstream with npx skills add hariinfo/agentic-code-template --skill design-documents. Copyright stays with the author.

Document Model Designer (design-documents)

Overview

This skill transforms an analyzed Oracle relational schema into a fully designed Cosmos DB NoSQL document model. Oracle → Cosmos DB is not a "port" — it's a complete redesign. Relational databases normalize data and join at query time. Cosmos DB denormalizes data and optimizes for access patterns at write time. This skill applies NoSQL design principles to create an optimal, implementable document model.

The design-documents skill is the intellectual core of the database modernization pipeline: it decides which tables embed together, how data denormalizes, where constraints move to the app layer, and how triggers become change feed handlers.

Important: This skill works with a scoped inventory from the analyze-schema skill. The inventory includes tables, constraints, and PLSQL code. References to sequences, triggers, views, and other objects are flagged in dependency_gaps and out_of_scope_refs arrays for design decisions without requiring full object definitions.

Inputs

You will receive a db-slug — the directory name under modernization-output/ containing:

• schema-analysis.md (required) — comprehensive Oracle schema analysis of provided DDL
• oracle-inventory.json (required) — machine-readable catalog of tables, constraints, and PLSQL procedures/functions/packages. May include dependency_gaps (referenced but not-yet-provided objects) and out_of_scope_refs (sequences, triggers, views, etc.)
• relationship-map.md (required) — entity relationships and suggested aggregates based on provided tables
• source-extracts.md (optional) — from code pipeline; reveals actual application query patterns
• techspec.md (optional) — from code pipeline; reveals transaction boundaries and caching

Process

Pre-Check — Validate Inventory Completeness

Before starting design work, verify the analysis is complete:

1. Read oracle-inventory.json and check for dependency_gaps:

   • If the dependency_gaps array is non-empty, this means tables or procedures referenced by the provided objects are not yet in the inventory
   • Warn: "⚠ Analysis is incomplete. {N} dependency gap(s) found. Some design decisions (esp. FK embedding strategies) may need revisiting once missing objects are analyzed. Recommend completing the analyze-schema step first."
   • Continue anyway (design can proceed with known objects), but flag assumptions

2. Read oracle-inventory.json and check for out_of_scope_refs:

   • These are references to sequences, triggers, views, jobs, synonyms, and other non-core objects
   • Note their locations for later design decisions (not an error — expected)
   • These will be handled in specific phases below

Phase 1 — Identify Access Patterns

Before designing documents, catalog every way the data is accessed. This is the foundation of all design decisions. NoSQL design is access-pattern-driven.

1. Extract Access Patterns from Artifacts

• From schema-analysis.md: Every SELECT, INSERT, UPDATE, DELETE in stored procedures, views, and PLSQL packages
• From oracle-inventory.json: Query patterns embedded in procedure bodies and view definitions
• From source-extracts.md (if available): Actual queries the application executes — far more reliable than what "might" be used
• From indexes: Existing indexes reveal query patterns the DBA optimized for (column order matters)
• From views: Views reveal common read patterns that became materialized for performance

2. Catalog Each Access Pattern

For every pattern identified, document:

• Operation type: Read / Write / Update / Delete / Batch
• Frequency: Real-time user-facing / Batch / Reporting / Admin (rare)
• Filter conditions: WHERE clauses → primary partition key candidates
• Sort requirements: ORDER BY clauses → secondary sort key candidates
• Tables involved: Which JOINs — determines embedding decisions
• Volume: Rows returned/affected → throughput estimation
• Latency requirement: Interactive (< 100ms) vs. acceptable delay

3. Identify Hot vs. Cold Access

• Hot patterns: High frequency, low latency requirement → single partition preferred
• Cold patterns: Batch, reporting, rare admin → cross-partition acceptable
• This informs partition key choice and denormalization strategy

Phase 2 — Design Document Model

Apply NoSQL design principles to transform the relational schema into an optimized document model.

4. Determine Aggregate Boundaries

Group tables that are always accessed together into single documents:

• 1:1 relationships → almost always embed in parent
• Parent-child 1:N with CASCADE delete → strong candidate for embedding
• 1:N where child is accessed independently → separate container with reference
• 1:N where N is very small (< 100) and always read with parent → embed
• 1:N where N is large or frequently filtered → separate container
• N:M relationships → embed on both sides (duplication) or use reference pattern

Review relationship-map.md for suggested aggregate boundaries — use as starting point.

5. Choose Containers

Cosmos DB containers are the logical equivalent of relational tables:

• Each major aggregate typically becomes one container
• Consider "single container with discriminator pattern" for entity hierarchies that share the same partition key
• Example: ORDERS + ORDER_HISTORY as separate document types in same orders container
• Target: minimize containers while maintaining clear, efficient access patterns

6. Choose Partition Keys — Most Critical Decision

The partition key determines throughput, latency, and cost:

• Must exist on every document in the container
• Should distribute evenly — avoid hot partitions (some partitions receiving 99% of traffic)
• Should align with primary access filter — queries within one partition are fast; cross-partition queries are expensive
• Candidates to evaluate: customer_id, tenant_id, region, order_date (year-month), category, facility_id
• For each container: evaluate 2-3 candidates with pros/cons documented

Pros/cons template:

• Candidate A: /customerId
  • ✓ Aligns with AP-002 (high-frequency query: get all orders for customer)
  • ✓ Good distribution (millions of distinct customers)
  • ✗ AP-003 (search by date range) becomes cross-partition expensive
  • Decision: ACCEPT

7. Design Document Schemas

For each container, define the JSON document structure:

• Map Oracle columns → JSON properties with type conversions

• Embed child entities as nested arrays/objects where appropriate

• Add discriminator field ("type": "order") if mixing entity types in one container

• Add partition key field if it's synthetic (non-natural business key)

• Preserve legacy IDs (e.g., Oracle ORDER_ID) as legacyOrderId for migration traceability

• Handle Oracle types:

  • NUMBER(p,s) → number (or string if p > 15 for precision)
  • DATE/TIMESTAMP → ISO-8601 string
  • CLOB → string (or Azure Blob Storage reference if > 2MB)
  • BLOB → Azure Blob Storage reference with URL
  • RAW → base64 string
  • User-defined types → nested objects
  • INTERVAL → ISO 8601 duration string
  • BOOLEAN/Y-N flags → boolean

• Add system metadata fields: _metadata: { createdAt, updatedAt, version } for optimistic concurrency

8. Handle Denormalization

NoSQL requires intentional duplication. Document every duplication:

• What gets duplicated? (e.g., customer name embedded in order)
• Why? (query needs it without separate lookup)
• Update strategy:
  • Change feed propagation (eventual consistency)
  • Snapshot at write time (never updated)
  • Acceptable staleness (< 1 minute? < 1 hour?)
• Flag high-change data — data that changes frequently may be better as reference than embedded

Create Denormalization Registry table: duplicated field → source → embedded in → update strategy → staleness tolerance

9. Handle Sequences → ID Generation

Oracle sequences have no equivalent in Cosmos DB. Identify sequence references from out_of_scope_refs in the inventory:

• Locate sequence references: Check out_of_scope_refs array for entries of type "sequence" and which tables/columns use them
• For each sequence reference, document:
  • Which table(s) and column(s) use this sequence
  • Current Oracle sequence behavior (start value, increment, cycle)
  • Default strategy: GUID (v4) — simple, collision-free, no coordination
  • Alternative: ULID (sortable, time-based, preserves insertion order)
  • Alternative: Snowflake IDs (time-ordered, sortable, generated app-side)
  • Recommendation: Use GUID for new IDs, preserve legacy Oracle sequence IDs as legacyId during migration
• If a sequence is not referenced in provided objects but exists in oracle-inventory.json, note it as unused

Sequences Table Example:

10. Handle Disappearing Constraints

Relational constraints become app-layer responsibilities:

• Foreign keys → app-layer referential integrity validation
• Unique constraints → Cosmos DB unique key policy (within partition) OR app-layer check (cross-partition)
• Check constraints (e.g., TOTAL > 0) → app-layer validation in service layer
• NOT NULL → app-layer validation (Cosmos DB is schema-free)
• Column defaults → app-layer defaults before insert

Document every constraint: Oracle constraint → Cosmos DB handling → which service layer enforces it

11. Handle Triggers

Oracle triggers contain invisible business logic. Trigger references come from out_of_scope_refs in the inventory (trigger bodies are not provided unless explicitly included):

Process:

1. Locate trigger references in out_of_scope_refs — identify which triggers are referenced by which tables or PLSQL objects
2. For each trigger reference, even without the full trigger body, document:
   • Trigger name and source table/event (AFTER INSERT/UPDATE/DELETE)
   • Inferred purpose from context: audit, cascade update, validation, computed column
   • Mapping to Cosmos DB:
     • Audit triggers → Change feed + Azure Function writes to audit container
     • Cascade update triggers → Change feed + Azure Function for propagation
     • Validation triggers → App-layer validation before write
     • Computed column triggers → App-layer computation before write
     • Enforcement triggers (referential integrity, uniqueness) → App-layer validation
   • Change feed processor name (e.g., orders-audit-processor)
   • Azure Function name (e.g., fn-order-audit)
   • Target action: what the trigger does
   • ⚠ Flag: If trigger body is unavailable → "Change feed handler needed — trigger body not available, logic TBD"

Trigger Mapping Example:

12. Design Indexing Policy

Cosmos DB indexes all paths by default (inclusive). Customize for performance:

• Default: "indexingMode": "consistent" — all queries always return up-to-date results
• For hot paths: Include in index (needed for WHERE, ORDER BY, JOIN)
• For cold paths: Exclude from index (not queried, saves cost)
• Composite indexes: For ORDER BY on multiple fields (e.g., /customerId + /orderDate)
• Range indexes: For numeric/string comparisons
• Spatial indexes: If geo queries (point/polygon)

Provide complete indexingPolicy JSON for each container.

13. Estimate Throughput (RU/s)

Request Units (RUs) estimate cost and latency:

• Read 1KB document = ~1 RU
• Write 1KB document = ~5-10 RU
• Cross-partition query = 1 RU + cost of items returned
• For each container: estimate read/write volume from access patterns
• Recommend: Autoscale (flexible), Serverless (pay per request), or Provisioned (fixed)

Example:

• AP-002 (get orders for customer): 1 RU point read × 1000/sec = 1000 RU/s reads
• Writes (new orders): ~10 RU × 100/sec = 1000 RU/s writes
• Recommendation: Autoscale 2000-6000 RU/s

Phase 3 — Validate Design

14. Verify Every Access Pattern is Satisfied

For each access pattern from Phase 1:

• Can it be served by the document model? (single partition / single query / multiple queries?)
• Does it require cross-partition query? (expensive — consider denormalization)
• Does it require multiple queries / JOINs? (no JOINs in Cosmos DB — may need further embedding)
• Document mitigation for expensive patterns

15. Verify Every Provided Object is Accounted For

Traceability is critical for migration. Work with the scoped inventory:

• Every provided table → mapped to container / document type (or explicitly excluded with reason)
• Every provided column → mapped to JSON property or excluded with reason
• Every provided constraint → mapped to Cosmos DB feature or app-layer enforcement
• Every provided PLSQL procedure/function/package → mapped to app-layer service or migration strategy
• Every out-of-scope trigger reference → mapped to change feed handler or app-layer logic with note on body availability
• Every out-of-scope view reference → mapped to denormalized document or query pattern (note: view body not available)
• Every out-of-scope sequence reference → mapped to ID strategy
• Every dependency gap (referenced but not-yet-provided) → flagged as requiring future analysis for final design decisions

Output Templates

Output 1 — document-model.md

# Cosmos DB Document Model: {schema-name}

**Database**: {db-name}
**Generated**: {today's date}
**Source**: {db-slug path}

## Design Summary

| Metric | Value |
|--------|-------|
| Containers | {count} |
| Document Types | {count} |
| Embedded Relationships | {count} |
| Reference Relationships | {count} |
| Denormalized Fields | {count} |
| Constraints Moved to App Layer | {count} |
| Triggers → Change Feed | {count} |

## Container: {container-name}

### Purpose
{What this container stores — e.g., "Core order and order item data with denormalized customer and product info"}

### Partition Key: `/{partition-key-path}`

**Rationale**: {Why this key was chosen based on access patterns}

**Alternatives Considered**:
- `/orderId`: Distributes evenly but breaks AP-002 (get orders for customer), would be cross-partition
- `/orderDate`: Aligns with AP-003 (date range) but hot in recent orders, cold for historical

**Expected Distribution**: {X million distinct values, roughly even distribution}

### Document Types

#### {DocumentType1} (discriminator: `"type": "{value}"`)

**Source Tables**: {ORACLE_TABLE (columns embedded), ORACLE_CHILD_TABLE (embedded as array)}

**Estimated Size**: {avg bytes} per document | **Estimated Count**: {total documents} | **Estimated Container Size**: {GB}

**JSON Schema**:
```json
{
  "id": "GUID",
  "type": "order",
  "customerId": "value — partition key",
  "orderNumber": "string — unique business key",
  "orderDate": "ISO-8601 — source: ORDERS.ORDER_DATE",
  "totalAmount": "number — source: ORDERS.TOTAL",
  "customerName": "string — denormalized from CUSTOMERS.NAME (change feed updated)",
  "items": [
    {
      "itemId": "ULID",
      "productId": "string",
      "productName": "string — snapshot at order time (never updated)",
      "quantity": "number",
      "unitPrice": "number"
    }
  ],
  "shippingAddressId": "string — reference to addresses container",
  "status": "string — enum: pending | confirmed | shipped | delivered",
  "_metadata": {
    "createdAt": "ISO-8601",
    "updatedAt": "ISO-8601",
    "version": 1
  }
}

Column Mapping (Oracle → Cosmos DB):

Constraint Migration:

Trigger → Change Feed Mapping:

Indexing Policy:

{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
    {"path": "/*"}
  ],
  "excludedPaths": [
    {"path": "/items[*]/internalNotes/?"}
  ],
  "compositeIndexes": [
    [
      {"path": "/customerId", "order": "ascending"},
      {"path": "/orderDate", "order": "descending"}
    ],
    [
      {"path": "/status", "order": "ascending"},
      {"path": "/orderDate", "order": "descending"}
    ]
  ]
}

Throughput Estimate:

• Reads: AP-001 (point read by ID) 500/sec × 1 RU = 500 RU; AP-002 (range query) 100/sec × 5 RU = 500 RU → ~1000 RU/s
• Writes: New orders 100/sec × 8 RU = 800 RU; Updates 50/sec × 5 RU = 250 RU → ~1050 RU/s
• Recommendation: Autoscale 2000–5000 RU/s

{Repeat Container and Document Type sections for each container}

Denormalization Registry

View → Query/Denormalization Mapping

For views referenced in out_of_scope_refs, map to Cosmos DB implementation without requiring full view bodies:

Sequence → ID Strategy

For sequences referenced in out_of_scope_refs, determine ID generation strategies without requiring full sequence definitions:

Cross-Partition Query Catalog

These patterns require cross-partition queries (expensive — use sparingly or denormalize):

Inventory Scope & Dependency Gaps

Analysis Completeness:

• Dependency gaps checked and documented (if any exist)
• Out-of-scope references (sequences, triggers, views) identified and addressed in design

Provided Objects Coverage:

• All provided tables mapped to containers/documents
• All provided PLSQL procedures/functions/packages mapped to services
• All provided constraints mapped to enforcement

Referenced Objects (out_of_scope_refs) Coverage:

• All referenced sequences mapped to ID strategies
• All referenced triggers mapped to change feed handlers with body availability notes
• All referenced views mapped to query/denormalization patterns
• Any other out-of-scope references (jobs, synonyms, types) noted for future consideration

Summary & Sign-Off

• All access patterns from Phase 1 verified as satisfied by the document model
• All provided Oracle objects accounted for and mapped
• All referenced out-of-scope objects (triggers, sequences, views) addressed with design strategies
• Dependency gaps (if any) documented with notes on design impacts
• Every constraint migration documented and enforcement responsibility assigned
• Partition key distribution confirmed as reasonable
• Denormalization strategy documented with update plans
• Cross-partition queries identified and mitigated
• Ready for migration codegen (gen-migration)

### Output 2 — `cosmos-config.json`

Machine-readable Cosmos DB configuration for Azure deployment:

```json
{
  "database": "{database-name}",
  "containers": [
    {
      "name": "orders",
      "partitionKey": "/customerId",
      "uniqueKeys": [
        ["/orderNumber"]
      ],
      "defaultTtl": -1,
      "changeFeedPolicy": {
        "retentionDuration": "P7D"
      },
      "indexingPolicy": {
        "indexingMode": "consistent",
        "automatic": true,
        "includedPaths": [{"path": "/*"}],
        "excludedPaths": [{"path": "/items[*]/internalNotes/?"}],
        "compositeIndexes": [...]
      },
      "throughput": {
        "type": "autoscale",
        "maxRUs": 5000
      }
    }
  ],
  "changeFeedProcessors": [
    {
      "name": "orders-audit-processor",
      "sourceContainer": "orders",
      "leaseContainer": "leases",
      "processorHandler": "fn-order-audit",
      "oracleTriggerMapped": "TRG_ORDER_AUDIT",
      "targetContainer": "audit-events"
    }
  ]
}

Output 3 — access-patterns.md

Catalog of every access pattern and how it's served:

# Access Pattern Catalog: {schema-name}

| # | Pattern Name | Frequency | Oracle Implementation | Cosmos DB Implementation | Container | Partition Key Aligned? | RU Estimate | Notes |
|---|--------------|-----------|---------------------|------------------------|-----------|----------------------|-----------|-------|
| AP-001 | Get order by ID | High (user dashboard) | SELECT * FROM ORDERS WHERE ORDER_ID = ? | Point read by id + partition key (must know customerId) | orders | Yes | 1 RU | Requires knowing customerId; redesign if only have orderId |
| AP-002 | Get all orders for customer | High (order history) | SELECT * FROM ORDERS WHERE CUSTOMER_ID = ? ORDER BY ORDER_DATE DESC | Query within partition (single RU charge) | orders | Yes | 5-10 RU | Single-partition, efficient |
| AP-003 | Search orders by date range | Medium (reporting) | SELECT * FROM ORDERS WHERE ORDER_DATE BETWEEN ? AND ? | Cross-partition query ⚠ | orders | No | 50-100+ RU | Consider: materialized time-partitioned container or Azure Synapse |
| AP-004 | Get order + customer details | Medium | SELECT O.*, C.NAME FROM ORDERS O JOIN CUSTOMERS C... | Point read returns embedded customerName | orders | Yes | 1 RU | customerName denormalized, refreshed via change feed |
| AP-005 | Get order items | Medium (detail view) | SELECT * FROM ORDER_ITEMS WHERE ORDER_ID = ? | Embedded array in order document — no separate query | orders | Yes | 1 RU | Items embedded; no JOIN needed |

Guidelines

1. Access Patterns Drive Design — every design decision traces back to a query pattern
2. Avoid Hot Partitions — uneven distribution kills throughput
3. Minimize Cross-Partition Queries — they're 10-100x more expensive; denormalize if frequent
4. Denormalization is Intentional — document every duplicate with update strategy
5. Constraints Are Yours Now — FKs, UQs, CHECKs all move to app layer; catalog them
6. Triggers Become Change Feeds — every trigger maps to a change feed processor
7. Test Partition Distribution — simulate write volume against partition key candidates
8. Version Your Documents — _metadata.version enables optimistic concurrency control
9. Preserve Legacy IDs — maintain Oracle IDs as legacy* fields for traceability during migration

Output Checklist

• Pre-check complete: dependency_gaps warnings issued if analysis is incomplete
• document-model.md created with all containers, document types, schemas, constraint mappings, trigger mappings
• All provided Oracle tables mapped to Cosmos DB containers/documents (with rationale for embedding decisions)
• All columns from provided tables mapped to JSON properties with type conversions documented
• All constraints from provided DDL migrated to app layer with enforcement responsibility documented
• All provided PLSQL procedures/functions/packages mapped to app-layer services
• All referenced triggers (from out_of_scope_refs) mapped to change feed processors with note on body availability
• All referenced sequences (from out_of_scope_refs) mapped to ID strategies
• All referenced views (from out_of_scope_refs) mapped to denormalization patterns or query strategies
• Partition keys chosen with 2-3 alternatives evaluated and pros/cons documented
• Denormalization registry complete (duplicated field → source → embedded in → update strategy)
• Access patterns verified as satisfiable by document model
• Indexing policy specified for each container
• Throughput estimates provided (RU/s) with autoscale recommendations
• cosmos-config.json created with machine-readable container configs
• access-patterns.md created cataloging all patterns and how they're served
• Cross-partition queries identified and mitigation strategies documented
• Dependency gaps documented as requiring future design revisits once missing objects are analyzed
• Ready for migration generation (gen-migration skill)