AdventureWorks

# AdventureWorks E-Commerce - AI Agent Instructions

## GitHub Source Control Policy

**Do not commit, push, or check in any file changes to GitHub on behalf of the user.** All Git operations (commits, pushes, pull requests, branch creation, etc.) must be performed manually by the user.

## Architecture Overview

This is a **3-tier Azure application** demonstrating enterprise patterns with passwordless authentication:

- **Frontend** (`app/`): React + TypeScript + Vite SPA deployed as Azure Static Web App
- **Backend API** (`api/`): Microsoft Data API Builder (DAB) providing GraphQL + REST, running in Azure Container Apps
- **Serverless Functions** (`api-functions/`): .NET 10 Azure Functions in Container Apps for custom business logic
- **Database**: Azure SQL with AdventureWorks schema using Entra ID authentication
- **Infrastructure** (`infra/`): Bicep modules with modular service definitions

### Data Flow Pattern

```
User → Static Web App → GraphQL (DAB) → Azure SQL
                     ↘ Azure Functions → Azure SQL
                     ↕ Web PubSub (real-time push)
```

All services authenticate via **Managed Identity** (passwordless). No connection strings in code.

**Real-time push**: Azure Web PubSub (Free tier) pushes events from Azure Functions to all three frontends via WebSocket. The `WebPubSubService` singleton fires events after each mutation. Clients use the `useWebPubSub` hook + `useRealTimeUpdates` dispatcher to invalidate React Query caches.

### No Polling / No Manual Refresh Policy

**Do NOT use `refetchInterval` or manual polling on any page where Web PubSub real-time updates apply.** All data freshness must come from server-pushed cache invalidation via the `useRealTimeUpdates` hook. React Query's default window-focus refetch provides a natural fallback when users return to the tab.

When adding a new query that needs live updates:

1. Register the query key in the appropriate group handler in `app-manufacturing/src/hooks/useRealTimeUpdates.ts` (or the equivalent hook in `app/` or `app-admin/`)
2. Ensure the server-side mutation calls `WebPubSubService.SendToGroupAsync` for the relevant group
3. Do NOT add `refetchInterval` — the Web PubSub event-driven invalidation replaces polling entirely

## Critical Development Workflows

### Local Development Setup

**IMPORTANT**: DO NOT remove the Azure Infrastructure or Azure resources while developing locally. The app relies on Azure-hosted services for authentication and data. Specifically: do not run "azd down".

**Environment variables required:**

Testin is done against the Azure-hosted services, so the following environment variables should have been set automatically once the azd up has been run be set and available in your local environment:

- AI_AGENT_MCP_ENDPOINT - the MCP service URL
- AI_AGENT_MODEL - the ChatGPT model deployment name
- AI_AGENT_OPENAI_ENDPOINT - the Azure OpenAI endpoint
- API_FUNCTIONS_URL - the Azure Functions URL
- API_MCP_URL - the MCP service URL
- API_URL - the GraphQL API URL
- APPINSIGHTS_CONNECTIONSTRING - the App Insights connection string
- APPINSIGHTS_INSTRUMENTATIONKEY - the App Insights instrumentation key
- APPLICATIONINSIGHTS_CONNECTION_STRING - the App Insights connection string
- APP_REDIRECT_URI - the app redirect URI
- AZURE_CLIENT_ID - the Azure client ID
- AZURE_CONTAINER_REGISTRY_ENDPOINT - the Azure Container Registry endpoint
- AZURE_LOCATION - the Azure location
- AZURE_OPENAI_ACCOUNT_NAME - the Azure OpenAI account name
- AZURE_OPENAI_ENDPOINT - the Azure OpenAI endpoint
- AZURE_RESOURCE_GROUP - the Azure resource group
- AZURE_STATIC_WEB_APP_DEPLOYMENT_TOKEN - the Azure Static Web App deployment token
- AZURE_SUBSCRIPTION_ID - the Azure subscription ID
- COMMUNICATION_SERVICE_ENDPOINT - the Communication Service endpoint
- COMMUNICATION_SERVICE_NAME - the Communication Service name
- EMAIL_SENDER_DOMAIN - the email sender domain
- MCP_SERVICE_URL - the MCP service URL
- SERVICE_API_FUNCTIONS_IMAGE_NAME - the API Functions image name
- SERVICE_API_FUNCTIONS_NAME - the API Functions service name
- SERVICE_API_IMAGE_NAME - the API image name
- SERVICE_API_MCP_IMAGE_NAME - the API MCP image name
- SERVICE_API_MCP_NAME - the API MCP service name
- SERVICE_API_NAME - the API service name
- SERVICE_API_RESOURCE_EXISTS - indicates if the API resource exists
- SERVICE_APP_NAME - the Static Web App service name
- SQL_ADMIN_PASSWORD - the SQL admin password
- SQL_ADMIN_USER - the SQL admin user
- SQL_DATABASE_NAME - the SQL database name
- SQL_SERVER_NAME - the SQL server name
- STORAGE_ACCOUNT_NAME - the Storage Account name
- TENANT_ID - the Azure tenant ID
- USER_MANAGED_IDENTITY_NAME - the User Managed Identity name
- VITE_API_FUNCTIONS_URL - the Azure Functions URL
- VITE_API_URL - the GraphQL API URL
- WEB_PUBSUB_HOST_NAME - the Azure Web PubSub host name (e.g. av-wps-xxx.webpubsub.azure.com)
- chatGptDeploymentVersion - the ChatGPT deployment version
- chatGptModelName - the ChatGPT model name
- chatGptSkuName - the ChatGPT SKU name
- cognitiveservicesLocation - the Cognitive Services location
- embeddingDeploymentModelName - the embedding deployment model name
- embeddingDeploymentSkuName - the embedding deployment SKU name
- embeddingDeploymentVersion - the embedding deployment version
- imageDeploymentModelName - the image deployment model name
- imageDeploymentSkuName - the image deployment SKU name
- imageDeploymentVersion - the image deployment version
- imageModelFormat - the image model format

**For Azure Functions (api-functions) local development:**

The Functions project requires `MCP_SERVICE_URL` to connect to the Model Context Protocol server for AI agent capabilities. **Use the Azure-hosted MCP service** rather than running it locally:

````bash
# Get the MCP service URL from Azure
azd env get-values | grep MCP_SERVICE_URL



### Azure Deployment (azd)

The app uses **azd lifecycle hooks** for automated deployment orchestration:

```bash
azd up --no-prompt  # Full deploy: preup → provision → deploy → postdeploy
                    # Total time: ~29 minutes (21 min infrastructure + 8 min seed-job)
```

**Important:** Always use `--no-prompt` to prevent the deployment from stalling. The `azd` preflight validation may warn about AI model catalog entries and prompt for confirmation, which causes the process to hang. `--no-prompt` auto-accepts non-destructive warnings.`

**Hook execution order:**

1. `preup.ps1` - Creates Entra ID app registrations, discovers OpenAI models
2. `azd provision` - Deploys Bicep infrastructure (~21 minutes)
3. `postprovision.sh` - Configures SQL database roles, deploys seed-job for data import (~2-3 min + seed-job runs ~8 min asynchronously)
4. `azd deploy` - Builds containers via ACR remote build, deploys to Container Apps
5. `postdeploy.ps1` - Updates redirect URIs, sets runtime CORS config

**Key distinction**: `api/` and `api-functions/` build with **remote build** in ACR (see `azure.yaml`). The `app/` builds locally then deploys to Static Web Apps.

**Note:** The seed-job runs asynchronously in the background. Monitor its progress with:

```bash
az containerapp job execution list --name <seed-job-name> --resource-group <resource-group>
```

For complete details on the seed-job architecture and data loading process, see [seed-job/README.md](../seed-job/README.md).

### Getting Azure Resource Information

All deployed Azure resource details (URLs, resource groups, connection strings, etc.) are available via:

```bash
azd env get-values
```

Key values include:

- `API_URL` - DAB GraphQL/REST API endpoint in Azure Container Apps
- `FUNCTION_URL` - Azure Functions endpoint in Container Apps
- `AZURE_RESOURCE_GROUP` - Resource group name
- `DATABASE_CONNECTION_STRING` - SQL connection string with Managed Identity auth

**Important**: The DAB API always paginates results at **100 items**. When querying large datasets, use filters or check for multiple pages:

```graphql
# This returns maximum 100 items even if more exist
query {
  products {
    items {
      ProductID
      Name
    }
  }
}

# Use filters to check for additional records
query {
  products(filter: { ProductID: { gt: 100 } }) {
    items {
      ProductID
    }
  }
}
```

### VS Code Tasks

Use built-in tasks for development (accessible via `Cmd/Ctrl+Shift+B`):

- `func: host start` - Run Azure Functions locally (depends on build task)
- `start frontend` - Run React dev server
- `start api (DAB)` - Run local DAB server
- `build all` - Compile .NET projects

## Project-Specific Conventions

### GraphQL API (DAB Naming)

**Critical**: DAB auto-generates schema from database tables with specific naming rules:

```typescript
// Database: ProductCategory → GraphQL: productCategories (camelCase + plural)
// Database: Person → GraphQL: people (irregular plural)
// Always use .items for list queries:
const { productCategories } = await client.request(gql`
  query {
    productCategories {
      items {
        ProductCategoryID
        Name
      }
    }
  }
`);
```

See [docs/DAB_NAMING_CONVENTIONS.md](docs/DAB_NAMING_CONVENTIONS.md) for complete rules.

### Configuration Files Pattern

The project uses **environment-based config pairs**:

- `api/dab-config.json` (Azure demo deployment - CORS all origins, no auth)
- `app/.env` (local API URL - not normally needed due to azd env injection)
- `app/public/config.js` (runtime config injection for Azure)

**Never hardcode URLs** - always use environment variables or runtime config.

### Azure Functions Structure

Functions follow **isolated worker model** with dependency injection:

```csharp
// Program.cs registers services
builder.Services.AddScoped<AddressService>(sp => {
    var connectionString = configuration["SQL_CONNECTION_STRING"];
    return new AddressService(connectionString);
});

// Functions receive via constructor injection
public AddressFunctions(ILogger<AddressFunctions> logger, AddressService service)
```

Connection string uses **Active Directory Default** authentication (Managed Identity in Azure, Azure CLI locally).

### Loading Skeletons (app-manufacturing)

**Every page that fetches async data must show a loading skeleton** while data is in-flight. This is a hard convention for the manufacturing app.

**Available skeleton components** — import from `@/components/LoadingSkeletons`:

| Component             | Use for                                          |
| --------------------- | ------------------------------------------------ |
| `TableSkeleton`       | Any table/list view (configurable `rows`/`cols`) |
| `CardGridSkeleton`    | Grid of cards (configurable `count`)             |
| `KpiSkeleton`         | KPI metric cards (configurable `count`)          |
| `DetailPageSkeleton`  | Detail pages: header + stats + table             |
| `SidebarListSkeleton` | Sidebar navigation lists                         |
| `ChartSkeleton`       | Chart/graph containers                           |
| `DashboardSkeleton`   | Full dashboard layout                            |
| `ScheduleSkeleton`    | Schedule/calendar layouts                        |
| `ShopFloorSkeleton`   | Shop floor operation card grids                  |

The base `Skeleton` primitive is also available from `@/components/ui/skeleton` for custom layouts.

**Pattern to follow:**

```tsx
import { DetailPageSkeleton, TableSkeleton } from '@/components/LoadingSkeletons';

const MyPage = () => {
  const { data, isLoading } = useQuery({ ... });

  // Early-return skeleton for full-page loading states
  if (isLoading) return <DetailPageSkeleton />;

  // For partial/panel loading states, use inline branches:
  // {isLoading ? <TableSkeleton rows={6} cols={4} /> : <table>...</table>}
  return (...);
};
```

- **Full-page loads**: early-return the appropriate skeleton before the main JSX
- **Panel/section loads**: use a ternary in the JSX — `{isLoading ? <Skeleton> : <Content>}`
- **Multiple queries**: derive a combined flag — `const isLoading = queryA.isLoading || queryB.isLoading`
- Static pages (login, 404, settings with no async data) do not need skeletons
- Pages that only display a selection prompt before data loads (e.g. "select a product first") should show a skeleton only after the user has made a selection and data is actively loading

### Real-Time Push Pattern (Web PubSub)

All three frontends connect to Azure Web PubSub via the `useWebPubSub` hook. When server-side mutations occur, the `WebPubSubService` singleton pushes JSON events to topic groups. The `useRealTimeUpdates` dispatcher hook maps events to `queryClient.invalidateQueries()` calls, triggering React Query to refetch.

**Server-side pattern** — inject `WebPubSubService`, call `SendToGroupAsync` after mutations:
```csharp
await _webPubSub.SendToGroupAsync("manufacturing-ops", new { @event = "wo-completed", workOrderId });
```

**Client-side pattern** — `useRealTimeUpdates()` is mounted in each app's root component. Individual pages keep a slow `refetchInterval` (60–120s) as a fallback.

**Groups**: `manufacturing-agent`, `manufacturing-ops`, `warehouse`, `supply-chain`, `orders`, `shopping-simulator`, `reviews`

**Negotiate endpoint**: `GET /api/webpubsub/negotiate?groups=manufacturing-agent,warehouse,...`

### Bicep Infrastructure Patterns

Infrastructure uses **modular decomposition** in `infra/modules/`:

```bicep
// main.bicep orchestrates, modules handle individual services
module identity 'modules/identity.bicep' = { ... }
module database 'modules/database.bicep' = { params: { identityId: identity.outputs.id } }
```

**Key parameters** injected by azd from environment:

- `revisionSuffix` - unique per deployment (avoids Container App conflicts)
- `chatGptModelName`, `embeddingModelName` - discovered in preup.ps1
- `aadAdminObjectId` - current user's Entra ID for SQL admin

### Infrastructure-as-Code Policy

All Azure Storage resources (queues, tables, blob containers) are provisioned **exclusively** by `infra/modules/storage.bicep` via `azd up`. Application code must **never** call `CreateIfNotExistsAsync`, `CreateIfNotExists`, or `CreateTableIfNotExistsAsync` — it must assume all infrastructure already exists at runtime.

- **New queue or table needed?** Add it to the `queueServices.queues` or `tableServices.tables` array in `storage.bicep`.
- **New blob container needed?** Add it to `blobServices.containers` in `storage.bicep`.
- **Startup health check**: `Program.cs` verifies all expected storage resources exist at cold-start using lightweight `GetPropertiesAsync()` calls and logs `ILogger.LogWarning` to App Insights for any missing resource. It does not block startup or create resources.
- **Why**: Each `CreateIfNotExistsAsync` adds an unnecessary HTTP round-trip (HEAD + conditional PUT). During simulation workloads that send hundreds of queue messages per minute, this overhead is significant.

## Common Integration Points

### Frontend ↔ GraphQL API

Uses `graphql-request` library with React Query for caching:

```typescript
// app/src/hooks/useProducts.ts
export const useProducts = () =>
  useQuery({
    queryKey: ["products"],
    queryFn: async () => {
      const { products } = await graphqlClient.request(GET_PRODUCTS);
      return products.items;
    },
    staleTime: 2 * 60 * 1000, // 2 min cache
  });
```

**All list queries** return `{ items: [] }` structure - never access data directly without `.items`.

### Database Schema Context

Uses **AdventureWorks schema** with namespaced tables:

- `Production.Product`, `Production.ProductCategory`
- `Person.Person`, `Person.Address`
- `Sales.SalesOrderHeader`

DAB entities map to these via `dab-config.json` source definitions:

```json
"Product": { "source": "Production.Product" }
```

**Note**: `Person.Address` is **excluded from DAB** because the `SpatialLocation` column uses the SQL `geography` type which DAB does not support. Address data is accessed exclusively via the `AddressFunctions` Azure Function.

### Managed Identity Authentication Flow

All Azure resources use passwordless auth:

1. **Container Apps** get system-assigned MI at deployment
2. **SQL Database** grants roles in `postprovision.sh`:
   ```sql
   CREATE USER [mi-name] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [mi-name];
   ```
3. **Connection strings** use `Authentication=Active Directory Default`
4. **Local dev** inherits from `az login` credentials

## Testing & Debugging

### Test Scripts

The project includes several test scripts in root:

- `test-signup.sh` - Validates user registration flow
- `test-password-functions.sh` - Tests password hashing and verification
- `test-password-reset-flow.sh` - Tests complete password reset flow (request, validate, complete)
- `test-discounts.sh` - Tests special offers integration
- `test-inventory.js` - Node script for inventory checks

**Run functions locally** with the `func: host start` task, which auto-builds before starting.

### Verifying GraphQL Endpoints

```bash
cd api && ./test-graphql-endpoints.sh  # Tests all entity queries
```

### Examining the Azure Database

**Direct SQL access from dev container usually fails** due to Entra ID authentication requirements. Instead, use the deployed DAB API:

```bash
# Get the API URL
API_URL=$(azd env get-values | grep API_URL | cut -d'=' -f2 | tr -d '"')

# Query via GraphQL
curl -X POST $API_URL/graphql -H "Content-Type: application/json" \
  -d '{"query": "{ products { items { ProductID Name } } }"}'

# Or use REST API
curl "$API_URL/api/Product"
```

**Remember**: API results are paginated at 100 items. To verify full table counts, use filters to check for records beyond the first page.

### Application Insights Integration

DAB and Functions auto-send telemetry. Check logs:

```bash
az monitor app-insights query --app <app-name> --analytics-query "requests | top 50 by timestamp desc"
```

## Common Gotchas

1. **CORS during local dev**: Always use `dab-config.json` (not `dab-config.prod.json`) locally
2. **GraphQL query failures**: Check for `.items` in response - DAB wraps all lists
3. **API pagination limits**: DAB API returns maximum 100 items per query - use filters or pagination to access larger datasets
4. **DAB 1.7 OData pagination parameter**: Use `$first` instead of `$top` for limiting results in REST calls (e.g. `?$first=10`). DAB 1.7+ replaced `$top` with `$first`. `$skip` is also unsupported — use `$after` for cursor-based pagination.
5. **Address entity not in DAB**: `Person.Address` is excluded from DAB because the `SpatialLocation` (`geography`) column is unsupported. Use the `AddressFunctions` Azure Function for address data instead.
6. **Direct SQL access**: Connecting to Azure SQL from dev container typically fails due to Entra ID auth - use the DAB API instead to query the database
7. **Build failures**: Functions require restore before build - use `restore (functions)` task first
8. **Connection errors**: Ensure `az login` is fresh - tokens expire after hours
9. **Missing env vars**: DAB reads from `@env()` placeholders - check azd environment with `azd env get-values`
10. **Shopping Simulator AI requirement**: The `no-order-customer`, `cart-recovery`, and `b2b-store` order modes require `AI_AGENT_ORDER_ID` to be configured. Without it, those messages will hard-fail to the poison queue with full diagnostics. The `new-persona` and `existing-repeat` modes fall back to random generation if AI is unavailable.
11. **Shopping Simulator auto-stop**: The simulator always auto-stops after the configured `durationHours` (default 24h, max 72h). It never runs forever — this is a cost protection feature checked every timer tick (1 minute).
12. **Order Status=7 is "Delivered"**: Orders are automatically promoted from Shipped (5) to Delivered (7) by the hourly `OrderDelivery_Timer` function. Terminal statuses are now 4 (Rejected), 6 (Cancelled), and 7 (Delivered) — Status=5 (Shipped) is no longer a terminal state. Delivery windows are configurable per order type via `PUT /api/orders/pipeline/config`.
13. **Order notification emails disabled by default**: `ORDER_NOTIFICATIONS_EMAIL_ENABLED` must be explicitly set to `"true"` to send Shipped/Delivered emails via Azure Communication Services. When unset or `"false"`, the intended email content is logged at Info level instead (look for `[EmailNotifications disabled]` in Application Insights). This prevents the Shopping Simulator from generating email spam. The flag is hardcoded to `"false"` in the Bicep infra — enabling it requires a manual Azure Portal or CLI override. See [docs/features/email/ORDER_NOTIFICATIONS.md](docs/features/email/ORDER_NOTIFICATIONS.md).
14. **Web PubSub Free tier limits**: 20 concurrent connections, 20K messages/day — sufficient for a single-user demo. All three frontend apps share one instance via groups. If `WEB_PUBSUB_HOST_NAME` is empty or the negotiate endpoint fails, apps fall back to slow polling (60–120s) automatically.
15. **No runtime infra creation**: Application code must not call `CreateIfNotExistsAsync` or `CreateIfNotExists` on storage resources (queues, tables, containers). All storage infrastructure is provisioned by `infra/modules/storage.bicep` via `azd up`. If you need a new queue or table, add it to the Bicep file. A startup health check in `Program.cs` logs App Insights warnings for any missing resources.

### Shopping Simulator

The shopping simulator (`POST /api/shopping-simulator/start`) generates continuous AI-driven orders. Configuration:

- **Duration**: 1–72 hours (default 24). Auto-stops to prevent runaway costs.
- **Order types**: Consumer (B2C) and/or B2B store orders. At least one must be enabled.
- **Consumer mix**: Existing top-spenders, new random personas, no-order customers drawn to sales, and abandoned-cart recoveries.
- **B2B stores**: AI generates representative replenishment orders based on each store's purchase history and current inventory.
- **State**: Persisted in Azure Table Storage (`shoppingSimulator` table). Queue: `simulation-order-queue`.
- **Admin UI**: `app-admin/src/pages/ShoppingSimulatorPage.tsx` — sliders, toggles, live stats, and results feed.

## Documentation Map

- [QUICKSTART.md](QUICKSTART.md) - Local development walkthrough
- [app/LOCAL_DEVELOPMENT.md](app/LOCAL_DEVELOPMENT.md) - Frontend-specific setup
- [api/README.md](api/README.md) - DAB deployment details
- [MIGRATION_SUMMARY.md](MIGRATION_SUMMARY.md) - GraphQL integration history
- [docs/DAB_NAMING_CONVENTIONS.md](docs/DAB_NAMING_CONVENTIONS.md) - GraphQL schema rules
````