---
name: design-schema
description: Plans Dataverse table/column architecture before creation. Checks OOB tables, validates naming conventions, plans relationships, and outputs a schema design for user approval. Use when creating new tables or significantly restructuring existing schema.
argument-hint: "[environment-name] [solution-name]"
---

# Design Dataverse Schema

Plan table and column architecture before creating anything. This skill prevents wrong architectural decisions — like creating `prefix_contact` instead of using the OOB `contact` table, choosing wrong column types, or missing relationship planning.

## Arguments

- `$ARGUMENTS[0]` — Dataverse environment name (from `dataverse_list_environments`)
- `$ARGUMENTS[1]` — Solution unique name to add components to

## When to Use

- Before creating ANY new tables
- When a user asks for a "Customer table" or "Ticket system" or any entity that might map to OOB
- When designing a schema with 3+ related tables
- When restructuring or extending existing schema significantly

## Pre-Flight

1. Confirm environment and solution with user (if not provided as arguments)
2. Query the environment for existing tables: `dataverse_list_tables` — specifically check for OOB tables that might fit the user's needs
3. If extending existing tables: `dataverse_list_columns` and `dataverse_list_relationships` for each table being extended

## Phase 1: Understand Requirements

Gather from the user (or from a build specification / ADO work items):
- What data entities are needed?
- What are the relationships between them?
- Who will use this data? (affects ownership type and security design)
- Is this extending an existing app or building from scratch?

## Phase 2: OOB Table Check

For EACH entity the user needs, check against the OOB table catalog:

| User Asks For | Check This OOB Table | Key Question |
|---------------|---------------------|--------------|
| Company, Organization, Vendor, Client | `account` | Does the existing Account table + custom columns cover the need? |
| Person, Customer, Contact, Employee | `contact` | Is this a person who needs to be tracked as a Contact? |
| Case, Ticket, Issue, Request, Incident | `incident` | Does the case management functionality of Incident fit? |
| Deal, Opportunity, Proposal, Quote | `opportunity` | Is this a sales/pipeline scenario? |
| Prospect, Lead, Inquiry | `lead` | Is this a pre-qualified entity that may convert to Account/Contact? |
| Task, To-Do, Action Item, Follow-Up | `task` (activity) | Is this an activity that should appear in the activity timeline? |
| Meeting, Call, Email | `appointment`, `phonecall`, `email` | Activity entities with built-in tracking and timeline integration |

**If OOB fits:** Recommend using OOB and extending with custom columns. Explain the benefits (built-in views, forms, relationships, platform integrations like activity timelines, duplicate detection, merge capabilities).

**If OOB doesn't fit:** Proceed with custom table design. Document WHY the OOB table was rejected (e.g., "Account is for external organizations but this tracks internal departments with different ownership semantics").

## Phase 3: Custom Table Design

For each custom table:

1. **Table name:** Must use the publisher prefix. Format: `{prefix}_{entityname}` (singular, lowercase, no spaces). Example: `contoso_servicerequest`
2. **Display name:** Singular and plural forms. Example: "Service Request" / "Service Requests"
3. **Primary name field:** The main text column that identifies records. Choose carefully — this appears in lookups and views by default. It does NOT have to be called "Name" — choose what makes sense (e.g., "Request Number", "Title", "Case ID")
4. **Ownership type:**
   - `UserOwned` — records are owned by individual users or teams. Supports row-level security. **Use this for most business data.**
   - `OrganizationOwned` — records are org-wide, no row-level security. Use for reference/config data.
   - `ActivityOwned` — only for activity entities (tasks, appointments, etc.)
5. **Column plan:** List all columns with:
   - Logical name: `{prefix}_{columnname}`
   - Type: String, Integer, Decimal, Currency, DateTime, Boolean, Choice, Lookup, etc.
   - Required level: Required, Recommended, Optional
   - For Choice columns: list the option values
   - For Lookup columns: identify the target table and relationship type (1:N or N:N)

## Phase 4: Relationship Mapping

Map all relationships:

1. **One-to-Many (1:N):** Parent table has many child records. Creates a lookup column on the child table. Use `dataverse_create_one_to_many`.
   - Example: Account (1) → Contact (N) — each Contact has a parent Account
2. **Many-to-Many (N:N):** Both tables can have many of each other. Creates an intersect table. Use `dataverse_create_many_to_many`.
   - Example: Contact (N) ↔ Project (N) — contacts can be on many projects, projects have many contacts

**Relationship naming:** Use descriptive names. `{prefix}_{parenttable}_{childtable}` for 1:N. `{prefix}_{table1}_{table2}` for N:N.

**Cascade behavior:** Default cascade is usually fine. Specify non-default only when needed (e.g., restrict delete if child records should prevent parent deletion).

## Phase 5: Present Design for Approval

Present the complete schema design to the user in a clear format:

```
### Schema Design: [App Name]

**Tables:**
1. [OOB] account — extended with columns: ...
2. [Custom] prefix_servicerequest — new table for ...
3. ...

**Relationships:**
1. account (1) → prefix_servicerequest (N) via prefix_account_servicerequest
2. ...

**Column Details:**
[Table-by-table column listing with types and required levels]
```

**Wait for user approval before creating anything.**

## Phase 6: Execute

Once approved:
1. Create custom tables (or skip for OOB tables that already exist)
2. Create columns on each table
3. Create relationships (which creates lookup columns automatically)
4. Targeted publish on all created components

## Guardrails

- **NEVER create a table without checking OOB first.** Even if the user explicitly says "create a Contact table," clarify that `contact` already exists.
- **Schema names are permanent.** Double-check naming conventions before creation. There is no rename operation.
- **Column types are permanent.** Confirm the type is correct before creation. A String cannot become an Integer later.
- **Lookup columns are created via relationships**, not via `dataverse_create_column`. Always use `dataverse_create_one_to_many` for lookups.
- **All components must be added to the solution.** Always pass `solution_name` on every create operation.

## Validation

After creating the schema:
- Verify all tables exist: `dataverse_list_tables` filtered by solution
- Verify columns on each table: `dataverse_list_columns` for each table
- Verify relationships: `dataverse_list_relationships` for tables with lookups
- Confirm targeted publish was performed
- Report: tables created (OOB extended vs custom), column count per table, relationships created