## Tenant Control Plane Data Model

## Purpose

This document proposes the PoC data model and migration direction for the tenant control-plane described in:

- `feature-proposal.md`
- `api-contract.md`

Scope:
- `Clients`
- `Tenants`
- tenant systems storage
- relation to existing `TenantWarehouseMappings`
- migration direction from the current model

This is a design document only. No implementation details or code are included.

## Naming Conventions Alignment

The proposed model should follow the naming style already used in `backend/Kds.Ai.Data`.

Current repository conventions:
- entity class names are singular PascalCase, for example `MonitorSpec`, `TenantMonitorSpec`, `TenantWarehouseMapping`
- `DbSet` names and table names are plural PascalCase, for example `MonitorSpecs`, `TenantMonitorSpecs`, `TenantWarehouseMappings`
- CLR property names are PascalCase
- database schema name is `backend`, but table names themselves do not include the schema prefix in model naming

Recommended application of those conventions for this feature:
- entity: `Client`, table: `Clients`
- entity: `Tenant`, table: `Tenants`
- entity: `TenantSystem`, table: `TenantSystems`

Important alignment note:
- the existing warehouse model already uses the name `TenantDatabase` in `TenantWarehouseMapping` and warehouse services
- to stay consistent with `backend/Kds.Ai.Data` and `backend/Kds.Ai.Warehouse`, the persistence model should prefer `TenantDatabase`
- API contracts may still expose `databaseName` as response/request field naming, with mapping done at the API boundary

## Design Goals

1. Keep `tenant` as the technical term already used in backend code.
2. Make `tenant` represent a restaurant/store.
3. Introduce `client` as the customer organization.
4. Keep one Redshift database per tenant.
5. Keep bootstrap as a separate workflow.
6. Favor flexible system configuration over rigid source classification.
7. Avoid over-modeling integrations in the PoC.

## Responsibility Split

This data model is shared by both API surfaces.

For the PoC:
- admin manages `Clients`
- client manages its own `Tenants`
- client bootstrap operates only on client-owned tenants

The storage model does not change between surfaces. Only request scoping changes.

## Proposed Tables

## 1. Clients

Entity: `Client`

Table: `Clients`

### Columns

- `ClientId` - `uuid`, primary key
- `Name` - `varchar(...)`, required
- `Code` - `varchar(...)`, required, unique, immutable
- `IsActive` - `boolean`, required, default `true`
- `CreatedAt` - `timestamp with time zone`, required
- `UpdatedAt` - `timestamp with time zone`, required

### Constraints

- primary key on `ClientId`
- unique index on `Code`

### Rules

- `Code` is generated from `Name`
- `Code` is immutable after insert
- `Name` is editable

## 2. Tenants

Entity: `Tenant`

Table: `Tenants`

### Columns

- `TenantId` - `uuid`, primary key
- `ClientId` - `uuid`, required, foreign key to `Clients`
- `Name` - `varchar(...)`, required
- `Code` - `varchar(...)`, required, unique, immutable
- `TenantDatabase` - `varchar(...)`, required, unique, immutable
- `Status` - `varchar(...)` or enum-backed string, required
- `IsActive` - `boolean`, required, default `false`
- `CreatedAt` - `timestamp with time zone`, required
- `UpdatedAt` - `timestamp with time zone`, required

### Constraints

- primary key on `TenantId`
- foreign key `ClientId -> Clients.ClientId`
- unique index on `Code`
- unique index on `TenantDatabase`
- unique index on `(ClientId, Name)`

### Rules

- `Name` must be unique within a client
- `Code` is generated from `Name`
- `Code` is globally unique across the whole system
- `Code` is immutable after insert
- `TenantDatabase` is derived from `Code`
- `TenantDatabase` must comply with Redshift naming rules
- `TenantDatabase` is immutable after insert
- `Name` is editable
- editing `Name` does not recalculate `Code`
- editing `Name` does not recalculate `TenantDatabase`

### Tenant status

For the PoC:
- `PendingBootstrap`
- `Active`
- `BootstrapFailed`

Default state after creation:
- `Status = PendingBootstrap`
- `IsActive = false`

## 3. Tenant systems

There are two viable storage options.

For the PoC, the recommended option is a separate child table.

Reason:
- API shape stays flexible as `systems: string[]`
- relational storage stays normalized
- deduplication is easier
- querying and validation are simpler than with a JSON blob

### Recommended option: `TenantSystems`

Entity: `TenantSystem`

Table: `TenantSystems`

### Columns

- `TenantId` - `uuid`, required, foreign key to `Tenants`
- `SystemName` - `varchar(...)`, required
- `NormalizedSystemName` - `varchar(...)`, required
- `CreatedAt` - `timestamp with time zone`, required

### Primary key options

Recommended:
- composite primary key: `(TenantId, NormalizedSystemName)`

This prevents duplicates like:
- `Brink`
- `brink`
- ` BRINK `

### Rules

- `SystemName` stores the display value or canonical input value
- `NormalizedSystemName` stores the normalized comparison key
- normalization should be case-insensitive
- trim whitespace before normalization
- duplicate systems for the same tenant are not allowed
- order is not significant
- tenants may exist with zero systems before bootstrap
- bootstrap must be blocked when a tenant has zero systems

### Normalization recommendation

Recommended normalization for `NormalizedSystemName`:
- trim leading/trailing whitespace
- collapse repeated internal spaces if desired
- convert to lowercase invariant

Examples:
- `Brink` -> `brink`
- ` brink ` -> `brink`
- `Logic Controls` -> `logic controls`

Important note:
- do not over-normalize into a slug unless it is clearly needed
- for the PoC, simple trimmed lowercase matching is enough

### Why separate table is better than JSON for the PoC

- easier uniqueness enforcement
- easier querying by tenant/system
- easier future migration into richer integration records
- cleaner relational design

## 4. Existing TenantWarehouseMappings

Entity: `TenantWarehouseMapping`

Table: `TenantWarehouseMappings`

Current role in code:
- technical mapping from `tenantId` to Redshift database
- activation flag for warehouse runtime

Recommended future role:
- infrastructure/runtime support table only
- not the primary business table for tenant administration

### Recommended columns after alignment

- `TenantId` - `uuid`, primary key, foreign key to `Tenants`
- `TenantDatabase` - `varchar(...)`, unique
- `IsActive` - `boolean`
- `CreatedAt`
- `UpdatedAt`

Optional future columns if operationally useful:
- `LastBootstrapAttemptAt`
- `LastBootstrapError`

### Important alignment change

Today this table still uses string `TenantId` in warehouse-related code.

The target model should align it with the rest of the application:
- change `TenantId` from `string` to `uuid`

## Recommended Relationships

### Client -> Tenants

- one client has many tenants
- one tenant belongs to exactly one client

### Tenant -> TenantSystems

- one tenant has zero or many systems
- one system entry belongs to exactly one tenant

### Tenant -> TenantWarehouseMappings

- one tenant has exactly one warehouse mapping
- warehouse mapping belongs to exactly one tenant

## Suggested Entity Boundaries

From a domain perspective:

- `Client` is the customer organization aggregate root
- `Tenant` is the restaurant/store aggregate root for operational onboarding
- `TenantSystem` is a child collection of `Tenant`
- `TenantWarehouseMapping` is an infrastructure projection/support record

This keeps the business write model centered on `Tenant`, not on the warehouse mapping.

## API To Storage Mapping

### Create client

`POST /admin/clients`

Writes:
- one row in `Clients`

### Create tenant

`POST /client/tenants`

Writes:
- one row in `Tenants`
- zero or more rows in `TenantSystems`
- one row in `TenantWarehouseMappings`

Recommended initial warehouse mapping values:
- `TenantId = new tenant id`
- `TenantDatabase = Tenant.TenantDatabase`
- `IsActive = false`

This keeps the current bootstrap flow compatible with existing warehouse resolution logic.

### Bootstrap tenant

`POST /client/tenants/{tenantId}/bootstrap`

Reads:
- `Tenants`
- `TenantSystems`
- `TenantWarehouseMappings`

Precondition:
- at least one `TenantSystems` row must exist for the tenant

Writes on success:
- `Tenants.Status = Active`
- `Tenants.IsActive = true`
- `Tenants.UpdatedAt = now`
- `TenantWarehouseMappings.IsActive = true`
- `TenantWarehouseMappings.UpdatedAt = now`

Writes on failure:
- `Tenants.Status = BootstrapFailed`
- `Tenants.IsActive = false`
- `Tenants.UpdatedAt = now`

## Migration Plan

## Phase 1. Add new business tables

Add:
- `Clients`
- `Tenants`
- `TenantSystems`

Keep existing warehouse tables and flows working.

## Phase 2. Align warehouse mapping with new tenant model

Update `TenantWarehouseMappings`:
- change `TenantId` to `uuid`
- connect it to `Tenants`
- keep `TenantDatabase` unique

## Phase 3. Update backend service assumptions

Align warehouse-related services and models that still use string tenant ids:
- repository interfaces
- bootstrap request/response models
- tenant warehouse descriptor models
- runtime resolution logic
- ingestion metadata if those records still store string tenant ids

## Phase 4. Move admin reads/writes to business tables

API surfaces should read and write through:
- `Clients`
- `Tenants`
- `TenantSystems`

with `TenantWarehouseMappings` used only as a technical support table.

## Index Recommendations

### Clients

- unique index on `Code`

### Tenants

- unique index on `Code`
- unique index on `TenantDatabase`
- unique index on `(ClientId, Name)`
- index on `ClientId`
- index on `Status`
- index on `IsActive`

### TenantSystems

- primary key or unique index on `(TenantId, NormalizedSystemName)`
- index on `NormalizedSystemName` if searching by system across tenants will be needed soon

### TenantWarehouseMappings

- unique index on `TenantDatabase`
- index on `IsActive`

## Integrity Rules Summary

1. client code is unique and immutable
2. tenant code is unique and immutable
3. tenant database name is unique and immutable
4. tenant name is unique within a client
5. tenant systems are deduplicated per tenant using normalized comparison
6. one tenant maps to one warehouse database
7. warehouse mapping cannot exist without a tenant

## Why this model is a good fit

This model fits the current product direction because:
- it preserves the existing technical `tenant` concept
- it introduces the missing business layer `client`
- it matches the ETL architecture of one database per tenant
- it supports admin-managed clients and client-managed tenants without changing persistence shape
- it keeps bootstrap independent from tenant creation
- it allows very flexible tenant system combinations
- it does not force premature classification of source systems

## Minimal PoC Recommendation

If the team wants the smallest correct relational model, use exactly:

1. `Clients`
2. `Tenants`
3. `TenantSystems`
4. aligned `TenantWarehouseMappings`

Do not add yet:
- user access tables
- group access tables
- integration secret tables
- typed integration configuration tables
- advanced provisioning audit tables

Those can be added later without invalidating this model.