# Tenant Warehouse Schema Evolution

## Problem

Tenant Redshift warehouses are currently initialized through `ITenantWarehouseBootstrapper`.

This works well for first-time tenant setup, but it does not provide a safe lifecycle for schema evolution.

This is now a concrete issue for the Brink POS ETL flow because staging tables have changed:

- existing tables were reshaped
- new staging tables were added
- more changes are expected later

Today, bootstrap is triggered in two important places:

- during tenant warehouse setup / debug bootstrap flows
- when Brink POS credentials are added and ETL provisioning activates the tenant warehouse

The current bootstrap implementation executes embedded SQL artifacts directly against the tenant database, but it does not track schema version or migration history.

## Current State In Code

Current bootstrap path:

- `backend/Kds.Ai.Warehouse/Services/TenantWarehouseBootstrapper.cs`
- `backend/Kds.Ai.ApiService/Services/CustomerEtlProvisioningService.cs`
- `backend/Kds.Ai.ApiService/Jobs/CustomerWarehouseProvisioningJobs.cs`
- `backend/Kds.Ai.Cli/Commands/Warehouse/WarehouseCommand.cs`

Behavior today:

- ensure tenant database exists
- load embedded SQL artifacts by prefix
- execute bootstrap SQL directly
- activate tenant mapping

There is no built-in concept of:

- schema migration history
- pending vs applied changes
- forward-only upgrades
- re-running only the missing changes for already provisioned tenants

## Why Bootstrap Alone Is Not Enough

Bootstrap is suitable for:

- creating a brand new tenant warehouse
- establishing a baseline schema

Bootstrap is not suitable for ongoing schema evolution because it does not safely handle:

- adding columns to existing tables
- creating new staging tables for already active tenants
- data-preserving structural changes
- ordered upgrades across multiple releases
- visibility into which tenant is on which schema version

If schema evolution continues without a migration system, the likely outcomes are:

- schema drift between tenants
- fragile SQL scripts that must stay compatible forever
- accidental destructive changes during reprovisioning
- uncertainty about whether a tenant database is up to date

## Recommended Model

Split the concerns into two distinct mechanisms.

### 1. Bootstrap

Use bootstrap only for first-time tenant creation.

Responsibilities:

- create tenant database if missing
- create baseline schemas
- create baseline tables
- create migration metadata tables

### 2. Migrations

Use migrations for every later schema change.

Responsibilities:

- apply only not-yet-applied changes
- preserve existing tenant data where possible
- track applied versions in the tenant warehouse
- provide deterministic, ordered evolution of staging and curated schemas

## Recommended Warehouse Migration Design

Add a tenant-scoped migration runner for Redshift.

### Migration history table

Each tenant warehouse should contain a schema migration history table, for example:

- `_meta.schema_migrations`

Suggested columns:

- `migration_id`
- `migration_name`
- `checksum`
- `applied_at`

This table allows the system to determine which changes were already applied.

### Migration file model

Store forward-only SQL migration files as embedded resources, separate from bootstrap.

Example shape:

- bootstrap resources:
  - baseline only
- migration resources:
  - ordered incremental SQL files

Examples of future migration files:

- add `snapshot_date` to staging tables
- create `staging.brink_settings2_jobs`
- replace old employee staging layout with relational tables

### Runtime behavior

For a tenant database:

1. ensure bootstrap baseline exists
2. read applied migrations from `_meta.schema_migrations`
3. execute unapplied migration files in order
4. record each successful migration

## Recommended Application Flow

### On tenant provisioning

Provisioning should become:

1. ensure tenant mapping exists
2. create tenant database if missing
3. bootstrap only if the database is new
4. run migrations
5. activate tenant mapping

### On Brink POS credential add/update

Do not treat credential changes as a reason to re-bootstrap schema.

Instead:

- ensure provisioning is tracked
- if tenant warehouse is brand new: bootstrap + migrate
- if tenant warehouse already exists: migrate only

### On local debug startup

Avoid implicit destructive or schema-mutating bootstrap during normal debug application startup.

Prefer:

- explicit bootstrap command for brand new tenant warehouses
- explicit migrate command for existing tenant warehouses
- optional developer-only startup flag if automatic migration is ever needed

## API And Operational Entry Points

### Existing entry points

Current CLI commands:

- `warehouse setup-tenant-warehouse`
- `warehouse bootstrap-tenant`
- `warehouse ensure-tenant-warehouse`

Current API-side provisioning is indirect through `CustomerEtlProvisioningService` and background jobs.

### Recommended additions

Add a dedicated migration runner abstraction, for example:

- `ITenantWarehouseMigrator`

Add CLI commands such as:

- `warehouse migrate-tenant <tenantId>`
- `warehouse migrate-all-active-tenants`

Add an admin-triggerable API only if operationally needed.

Recommended API shape:

- admin-only
- asynchronous
- job-backed
- idempotent

Example endpoints:

- `POST /admin/customers/{customerId}/warehouse/migrate`
- `POST /admin/warehouse/tenants/{tenantId}/migrate`

This should trigger a background job, not a long synchronous HTTP request.

## Why This Is Better

This approach gives:

- safe upgrades for already provisioned tenants
- explicit version tracking
- repeatable operational behavior
- clearer support for future staging-schema changes
- less risk than re-running ever-growing bootstrap SQL

It also matches the current reality that ETL staging schemas will continue to evolve.

## Recommended Next Steps

1. Introduce a migration history table in tenant Redshift databases.
2. Add `ITenantWarehouseMigrator` and a Redshift implementation.
3. Keep `ITenantWarehouseBootstrapper` focused on first-time initialization only.
4. Update provisioning flow so existing tenants run migrations instead of re-bootstrap.
5. Add CLI migration commands first.
6. Add admin API/job entry points if operators need remote triggering.

## Suggested Rule Of Thumb

- New tenant database: bootstrap, then migrate.
- Existing tenant database: migrate only.
- Never use bootstrap as the long-term schema evolution mechanism.