---
apiVersion: data-spec/v1
id: profile-heal-softdeleted-and-seed-usertype-20260417
description: "Heal any soft-deleted system Profiles (issue #322) and seed Profile.UserType for SF parity"
created: 2026-04-17
scope: production-only
status: pending
tables: [Profile, PermissionSet]
depends_on: []
---

# Profile Heal + UserType Seed — Data Spec

## Summary

GitHub issue #322: the server had **no `IsSystem` delete guard**. A local,
isolated TiDB experiment proved that once a native Profile was soft-deleted,
`cmd/setup apply` **did not restore it** — the row stayed `IsDeleted=1`, every
user with that `ProfileId` was cut off from all permissions, and `setup verify`
happily reported `✅ All assertions passed` while the tenant was effectively
locked out.

PR `fix/322-profile-protected-records-and-semantics` closes the window by:

1. Declaring `protectedRecords` in metadata → generic server-side delete guard
2. Adding `UserType VARCHAR(50)` as a real SF-faithful column on Profile
3. Tightening `assertSystemAdminProfileExists` to require `IsDeleted=0`
4. Correcting the frontend to drive the System/Custom flag off `IsSystem`
   (its real semantic) instead of `UserType` (an SF license enum)

This data-spec handles the one-shot production concerns:

- **Heal:** un-soft-delete any native Profile (and its owned PermissionSet)
  that was already deleted via the unguarded API before the guard shipped.
- **Seed:** backfill `UserType = 'Standard'` for existing Profile rows so the
  new column carries SF-compatible values for all current tenants. Fresh envs
  pick up the column default automatically via `ReconcileTableColumns`.

Both changes are read-safe and reversible. The schema change itself is not
done here — it flows from `system_tables.json` via `ReconcileTableColumns`.

## Changes

### 1. Heal soft-deleted native Profiles (idempotent)

```sql
UPDATE Profile
SET IsDeleted = 0,
    LastModifiedDate = NOW()
WHERE IsSystem = 1
  AND IsDeleted = 1;
```

**Idempotency:** re-running is a no-op once every row has `IsDeleted=0`. The
predicate only matches soft-deleted system rows, so custom (user-created)
profiles are never touched.

### 2. Heal backing PermissionSets of healed Profiles (idempotent)

```sql
UPDATE PermissionSet
SET IsDeleted = 0,
    LastModifiedDate = NOW()
WHERE IsOwnedByProfile = 1
  AND ProfileId IN (SELECT Id FROM Profile WHERE IsSystem = 1)
  AND IsDeleted = 1;
```

**Idempotency:** same shape as Change 1. `_System_PermissionSetPermission`
grant rows are not touched — the delete path never cascades to them, so they
are still intact and become reachable again once the PermissionSet is alive.

### 3. Seed Profile.UserType for existing rows (idempotent)

```sql
UPDATE Profile
SET UserType = 'Standard'
WHERE UserType IS NULL
   OR UserType = '';
```

**Idempotency:** the `UserType IS NULL OR = ''` guard means re-running does
nothing. New rows created via the app honour the column `DEFAULT 'Standard'`
declared in `system_tables.json`.

All 5 NexusCRM native profiles map to SF's `Standard` license. Future
UserType values (`PowerPartner`, `CsnOnly`, `Guest`, …) are valid picklist
entries and can be set per-row by future migrations without needing to touch
this seed.

## Rollback

Rollback for Change 1 + Change 2 is **not desirable** — reverting would
re-soft-delete system profiles and re-break the tenant. If the healing step
ever needs to be undone (e.g. a healed row was intentionally deleted after
the spec applied), do it by record ID, not wholesale.

Rollback for Change 3 (Seed) is **not a data operation** — the column is
declared `nullable: false DEFAULT 'Standard'` in `system_tables.json:619`, so
`UPDATE Profile SET UserType = NULL` would fail the NOT NULL constraint.

The only meaningful rollback for the seed is to drop the column itself:

1. Remove the `UserType` column block from `backend/internal/bootstrap/system_tables.json`
2. Regenerate code (`go run ./cmd/codegen`)
3. `ReconcileTableColumns` will issue the `ALTER TABLE ... DROP COLUMN UserType` on next `setup apply`

Seed data disappears with the column; no row-level UPDATE is required.

## Safety Notes

- No DELETE statements. No DROP. No TRUNCATE.
- All three changes operate on rows matched by narrow predicates.
- Changes 1 and 2 only affect `IsSystem=1` rows — never custom profiles.
- Change 3 only populates NULL / empty UserType values.
- Pre-apply row count check (see `data-spec-apply` SKILL) should report
  small numbers on a healthy tenant and zero on tenants that never
  experienced the issue-#322 condition.