# Data Model: Platform Provider Identity Standardization

## 1. ProviderConnection

**Purpose**: Tenant-owned record that models the tenant’s Microsoft connection state without storing centrally managed platform secrets.

### Fields

| Field | Type | Required | Notes |
|---|---|---:|---|
| `id` | bigint | yes | Existing primary key |
| `workspace_id` | bigint FK | yes | Existing workspace ownership |
| `tenant_id` | bigint FK | yes | Existing tenant ownership |
| `provider` | string | yes | Existing, remains `microsoft` for this feature scope |
| `entra_tenant_id` | string | yes | Existing target tenant directory ID |
| `display_name` | string | yes | Existing operator-visible label |
| `is_default` | boolean | yes | Existing default invariant per tenant/provider |
| `connection_type` | string | yes | New; `platform` or `dedicated` |
| `status` | string | yes | Existing backward-compatible summary; no longer canonical for consent or verification |
| `consent_status` | string | yes | New; `unknown`, `required`, `granted`, `failed`, `revoked` |
| `consent_granted_at` | timestamp nullable | no | New |
| `consent_last_checked_at` | timestamp nullable | no | New |
| `consent_error_code` | string nullable | no | New; sanitized identity-provider or platform reason code |
| `consent_error_message` | string nullable | no | New; sanitized operator-safe message |
| `verification_status` | string | yes | New; `unknown`, `pending`, `healthy`, `degraded`, `blocked`, `error` |
| `health_status` | string | yes | Existing operational summary retained for compatibility |
| `last_health_check_at` | timestamp nullable | no | Existing |
| `last_error_reason_code` | string nullable | no | Existing, remains runtime-oriented |
| `last_error_message` | string nullable | no | Existing, sanitized |
| `scopes_granted` | jsonb | yes | Existing; may continue to hold observed permission snapshots |
| `metadata` | jsonb | yes | Existing; used for migration-review flags and display-only hints such as effective app metadata |
| `created_at` / `updated_at` | timestamps | yes | Existing |

### Validation Rules

- `provider` must remain within supported provider types and is `microsoft` for this feature.
- `connection_type` must be one of `platform` or `dedicated`.
- `consent_status` must be one of the defined consent states.
- `verification_status` must be one of the defined verification states.
- `workspace_id` and `tenant_id` must match the resolved tenant scope.
- `platform` connections must not require a `ProviderCredential` row.
- `dedicated` connections may not be marked operationally ready without a valid credential row.

### Relationships

- Belongs to one `Tenant`
- Belongs to one `Workspace`
- Has zero or one `ProviderCredential`
- Emits many `AuditLog` entries indirectly via resource or service actions

### State Transitions

#### Standard platform flow

1. Create connection
   - `connection_type=platform`
   - `consent_status=required`
   - `verification_status=unknown`
   - `status=needs_consent`
2. Consent granted
   - `consent_status=granted`
   - `consent_granted_at` set
   - `verification_status` remains `unknown` or moves to `pending` when verification starts
3. Verification succeeds
   - `verification_status=healthy`
   - `health_status=healthy` or existing canonical success value
   - `status=connected`
4. Verification fails after granted consent
   - `consent_status` stays `granted`
   - `verification_status=degraded`, `blocked`, or `error`
   - `status` remains a compatibility summary, not the source of truth

#### Dedicated flow

1. Create dedicated connection
   - `connection_type=dedicated`
   - `consent_status=required` or `unknown`
   - `verification_status=unknown`
2. Dedicated credential added or rotated
   - Connection remains non-ready until consent and verification succeed
3. Dedicated credential missing or invalid
   - `verification_status=blocked`
   - runtime reason code indicates dedicated credential problem

#### Migration review flow

- `connection_type` is still set to `platform` or `dedicated`
- `metadata.legacy_identity_review_required=true`
- audit and UI show the record needs explicit operator review before final cleanup

## 2. ProviderCredential

**Purpose**: Tenant-owned credential material used only for explicit dedicated connections.

### Fields

| Field | Type | Required | Notes |
|---|---|---:|---|
| `id` | bigint | yes | Existing primary key |
| `provider_connection_id` | bigint FK | yes | Existing one-to-one relationship |
| `type` | string | yes | Existing legacy type; retained temporarily for backward compatibility |
| `credential_kind` | string | no | New; `client_secret`, later `certificate` or `federated` |
| `source` | string | no | New; `dedicated_manual`, `dedicated_imported`, `legacy_migrated` |
| `payload` | encrypted array | yes | Existing encrypted payload, only for dedicated credentials |
| `created_at` / `updated_at` | timestamps | yes | Existing |

### Validation Rules

- `provider_connection_id` must reference a `ProviderConnection` with `connection_type=dedicated`.
- `payload` must contain the fields required by `credential_kind`.
- `client_secret` payloads must include a non-empty `client_id` and `client_secret`.
- `tenant_id` or authority hints inside the payload, if present, must match the parent connection’s target tenant.

### Relationships

- Belongs to one `ProviderConnection`

## 3. PlatformProviderIdentity

**Purpose**: Virtual central identity returned by the platform resolver. Not stored in tenant-owned tables.

### Fields

| Field | Type | Required | Notes |
|---|---|---:|---|
| `provider` | string | yes | `microsoft` |
| `client_id` | string | yes | Central multitenant app ID |
| `credential_mode` | string | yes | Initially `client_secret` |
| `client_secret` or `secret_reference` | string | yes | Resolved centrally; never copied into tenant tables |
| `authority_tenant` | string | yes | Usually platform home tenant or `organizations` routing metadata |
| `redirect_uri` | string | yes | Canonical callback route |
| `redirect_source` | string | yes | Metadata for display and debugging |

### Validation Rules

- `client_id` must be present for all platform flows.
- A secret or secret reference must be resolvable for runtime use.
- Redirect URI must match the canonical consent callback route.

## 4. ProviderIdentityResolution

**Purpose**: Virtual service result consumed by `ProviderGateway`, verification, and consent generation.

### Fields

| Field | Type | Required | Notes |
|---|---|---:|---|
| `connection_type` | string | yes | `platform` or `dedicated` |
| `effective_client_id` | string | yes | App identity used for consent and runtime |
| `credential_source` | string | yes | `platform_config` or dedicated credential source |
| `tenant_context` | string | yes | Target customer tenant for Graph calls |
| `resolved` | boolean | yes | Whether runtime resolution succeeded |
| `reason_code` | string nullable | no | Stable blocker when not resolved |
| `message` | string nullable | no | Sanitized operator-safe explanation |

## 5. MigrationClassificationResult

**Purpose**: Explicit migration outcome for existing provider connections.

### Fields

| Field | Type | Required | Notes |
|---|---|---:|---|
| `provider_connection_id` | bigint | yes | Record being classified |
| `suggested_connection_type` | string | yes | `platform` or `dedicated` |
| `review_required` | boolean | yes | True for contradictory hybrid cases |
| `signals` | array | yes | Evidence such as tenant app fields, credential payload, consent/runtime mismatch |
| `source` | string | yes | `migration_scan`, `operator_override`, or similar |

## 6. AuditLog Impact

This feature does not add a new audit table. It extends audit usage for:

- connection created
- connection type changed
- consent started
- consent succeeded or failed
- consent revoked or missing detected
- verification succeeded or failed
- dedicated credential created, rotated, or deleted
- migration classification applied
- migration review required flagged or resolved