---
name: step-05-db-validation
description: Validate database schema, migrations, seed data, and multi-tenant isolation against real SQL Server
prev_step: steps/step-04-api-smoke.md
next_step: null
---

# Step 5: Database Validation (SQL Server LocalDB)

> **Purpose:** Verify migrations apply correctly, seed data inserts, LINQ queries execute on SQL Server, multi-tenant isolation works on REAL SQL Server (not SQLite).

See `references/db-validation-checks.md` for detailed checks, error classification, and fixes.

---

## Procedure

### Check 1: LocalDB Available?

```bash
sqllocaldb info MSSQLLocalDB
```

If NOT available → Skip DB validation, set `DB_VALIDATION = SKIPPED`

### Check 2: Pending Model Changes?

```bash
dotnet ef migrations has-pending-model-changes --project "$INFRA_PROJECT" --startup-project "$API_PROJECT"
```

If exit code != 0 → Create the missing migration through the sanctioned CLI
(a raw `dotnet ef migrations add` is blocked by the `ef-guard` hook):
```bash
npx --prefer-offline tsx skills/efcore/cli/create/index.ts \
  --spec '{"cwd":"<projectRoot>","description":"{SuggestedName}"}'
```

### Check 3: Apply Migrations on Temp DB

A raw `dotnet ef database update` is blocked by `ef-guard`, and the sanctioned
`cli/apply` targets the appsettings DB — not a throwaway one. For the TEMP DB,
generate the idempotent script (read-only `dotnet ef` — allowed) and run it
with `sqlcmd`:

```bash
DB_NAME="SmartStack_Validate_$(date +%s)"
CONN_STRING="Server=(localdb)\\MSSQLLocalDB;Database=$DB_NAME;Integrated Security=true;TrustServerCertificate=true;Connect Timeout=120;"

dotnet ef migrations script --idempotent \
  --project "$INFRA_PROJECT" --startup-project "$API_PROJECT" \
  -o "validate-migrations.sql"

sqlcmd -S "(localdb)\MSSQLLocalDB" -Q "CREATE DATABASE [$DB_NAME]"
sqlcmd -S "(localdb)\MSSQLLocalDB" -d "$DB_NAME" -i "validate-migrations.sql"
```

If FAIL → Check for: migration ordering, duplicate migration, seed data conflict (see reference)

### Check 4: Integration Tests (SQL Server)

```bash
TEST_PROJECT=$(ls tests/*Tests.Integration*/*.csproj 2>/dev/null | head -1)
dotnet test "$TEST_PROJECT" --no-build --verbosity normal
```

Validates: LINQ→SQL, multi-tenant isolation, soft delete, EF configs, repositories on real SQL Server

### Check 5: Dev Seeding Enabled?

```bash
APPSETTINGS=$(ls appsettings.Development.json 2>/dev/null || ls appsettings.json 2>/dev/null)
PROGRAM_CS=$(find . -name "Program.cs" -path "*/Api/*" 2>/dev/null | head -1)

# Check for Program.cs override or appsettings EnableDevSeeding = true
```

If not enabled → Add to Program.cs:
```csharp
options.EnableDevSeeding = builder.Environment.IsDevelopment();
```

### Check 6: DefaultTenantId FK Validation

```bash
SEED_CONSTANTS=$(find . -path "*/SeedConstants.cs" 2>/dev/null | head -1)
TENANT_GUID=$(grep -oP 'DefaultTenantId\s*=\s*Guid\.Parse\("([^"]+)"\)' "$SEED_CONSTANTS" | grep -oP '"[^"]+"' | tr -d '"')

sqlcmd -S "(localdb)\MSSQLLocalDB" -d "$DB_NAME" -Q "
  SET NOCOUNT ON;
  SELECT COUNT(*) FROM core.tenant_Tenants WHERE Id = '$TENANT_GUID'
"
```

If COUNT = 0 → Use a TenantId seeded by `InitializeSmartStackAsync()`

### Check 7: Seed Data Accessible?

```bash
dotnet run --project "$API_PROJECT" --urls "http://localhost:5097" \
  -- --ConnectionStrings:DefaultConnection="$CONN_STRING" &

# Verify seed data via API endpoint
# (See reference for full commands)
```

### Check 8: Cleanup

```bash
sqlcmd -S "(localdb)\MSSQLLocalDB" -Q "
  IF DB_ID('$DB_NAME') IS NOT NULL
  BEGIN
    ALTER DATABASE [$DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$DB_NAME];
  END
"
```

---

## Validation Summary

```
## DB Validation: {EntityName}

| Check | Result | Details |
|-------|--------|---------|
| LocalDB available | PASS/SKIP | sqllocaldb info MSSQLLocalDB |
| Pending model changes | PASS/FAIL | dotnet ef migrations has-pending |
| Migrations apply cleanly | PASS/FAIL | idempotent script via sqlcmd on temp DB |
| Integration tests (SQL Server) | PASS/FAIL | dotnet test --filter Integration |
| EnableDevSeeding active | PASS/WARN | Program.cs override or appsettings |
| DefaultTenantId FK valid | PASS/FAIL | SeedConstants GUID exists in tenant_Tenants |
| Seed data accessible | PASS/WARN | API endpoints return seeded data |
| Temp DB cleanup | PASS/WARN | Database dropped |

DB Validation Result: {PASS / FAIL / SKIPPED}
```

**Interpretation:**
- **PASS** — All checks covered: migrations, SQL Server, LINQ→SQL, isolation, seeding config
- **FAIL** — Fix before committing
- **SKIPPED** — LocalDB unavailable; tests ran on SQLite only (partial coverage)

---

## Error Reference

See `references/db-validation-checks.md` for complete error classification and fixes including:
- Migration ordering issues
- Seed data conflicts
- ForeignKey constraint violations
- LocalDB connectivity
- EnableDevSeeding configuration
- DefaultTenantId phantom FK

---

## NEXT STEP:

DB validation complete. Review all step results and generate final report.