# Database Validation Checks (SQL Server)

> **Reference for:** step-05-db-validation.md
> **Purpose:** Validate migrations, LINQ→SQL translation, multi-tenant isolation, seed data
> **Scope:** LocalDB availability, pending changes, migration application, integration tests, seed data config

---

## Check 1: LocalDB Availability

```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"
```

| Exit Code | Meaning | Action |
|-----------|---------|--------|
| 0 | No pending | PASS — continue |
| Non-zero | Changes not in migration | **FAIL — create migration** |

**If FAIL:**
```bash
dotnet ef migrations add {SuggestedName} \
  --project {InfraProject} \
  --startup-project {ApiProject} \
  -o Persistence/Migrations
```

---

## Check 3: Apply Migrations on Temp DB

```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 database update \
  --connection "$CONN_STRING" \
  --project "$INFRA_PROJECT" \
  --startup-project "$API_PROJECT"
```

**Common failures:**
- `Invalid column name` → migration ordering issue
- `There is already an object named` → duplicate migration
- `Cannot insert duplicate key` → seed data conflict

---

## Check 4: Integration Tests vs 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 translation on real SQL Server (not SQLite)
- Multi-tenant isolation (global query filters)
- Soft delete (IsDeleted filter)
- EF Core configuration (indexes, relationships, constraints)

**SQLite → SQL Server differences caught:**
- `LIKE` case sensitivity
- Date functions (`date('now')` vs `GETUTCDATE()`)
- String concatenation (`||` vs `+`)
- `LIMIT` vs `TOP`
- `AUTOINCREMENT` vs `IDENTITY`

---

## Check 5: Dev Seeding Configuration

```bash
# Check appsettings for EnableDevSeeding
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)

# Result: SEEDING_ENABLED = true/false
```

| Config | Meaning | Action |
|--------|---------|--------|
| Program.cs override | PASS | Active in Development |
| appsettings EnableDevSeeding = true | PASS | Active |
| appsettings FALSE + no override | WARNING | DevDataSeeder won't run |

**Fix:** 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 '"')

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

| Result | Status | Meaning |
|--------|--------|---------|
| COUNT > 0 | PASS | DefaultTenantId is valid |
| COUNT = 0 | FAIL | FK phantom: DevDataSeeder uses invalid TenantId → 500 errors |

**Fix:** Use a TenantId that is seeded by `InitializeSmartStackAsync()`

---

## Check 7: Seed Data Accessibility

```bash
# Start API with temp DB
dotnet run --project "$API_PROJECT" \
  --urls "http://localhost:5097" \
  -- --ConnectionStrings:DefaultConnection="$CONN_STRING" &
SEED_PID=$!

# Verify seed data via API
TOKEN=$(curl -s -X POST http://localhost:5097/api/auth/login -H "Content-Type: application/json" -d '{"email":"admin@smartstack.io","password":"Admin123!"}' | jq -r '.accessToken')

NAV_STATUS=$(curl -s -o /dev/null -w "%{http_code}" http://localhost:5097/api/navigation/modules \
  -H "Authorization: Bearer $TOKEN" \
  -H "X-Tenant-Id: 11111111-1111-1111-1111-111111111111")
```

| Status | Meaning |
|--------|---------|
| 200 | PASS — core seed data verified |
| 401 | WARNING — authentication issue, seed data check skipped |
| 404 | WARNING — API endpoint not available |

---

## Check 8: Cleanup

```bash
# Drop temp database
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
"
```

---

## Error Reference Table

| Error Pattern | Category | Fix |
|---------------|----------|-----|
| `Invalid column name '{Column}'` | Migration ordering | Reorder migration or split |
| `Cannot insert duplicate key` | Seed data conflict | Make seed data idempotent |
| `String or binary data would be truncated` | Column length | Increase MaxLength in EF config |
| `INSERT conflicted with FOREIGN KEY constraint` | Missing parent | Ensure parent entities seeded first |
| `NULL into column '{Column}'` | Missing default | Add default value or make nullable |
| `Login failed for user` | LocalDB auth | Run `sqllocaldb start MSSQLLocalDB` |
| `A network-related or instance-specific error` | LocalDB not running | Run `sqllocaldb start MSSQLLocalDB` |
| `DefaultTenantId does NOT exist in core.tenant_Tenants` | Phantom FK | Use valid TenantId from InitializeSmartStackAsync() |
| `EnableDevSeeding is FALSE` + empty tables | Seeding disabled | Add Program.cs override for Development |