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.