Redshift Foundation - Phase 1 Implementation Tasks

Overview

Implementation of the phase-1 Redshift warehouse foundation for tenant-scoped warehouse usage.

This task plan is derived from the documentation set in:

• ../../docs/features/ETL/slices/01-redshift-foundation/index.md
• ../../docs/features/ETL/slices/01-redshift-foundation/source-task.md
• ../../docs/features/ETL/slices/01-redshift-foundation/foundation-spec.md
• ../../docs/features/ETL/slices/01-redshift-foundation/serving-mapping.md

This task does not migrate the current simulator or anomaly detection runtime to Redshift. Instead, it creates the reusable foundation/framework that later tasks will consume.

The phase-1 outcome is:

• tenant-per-store Redshift database routing,
• a new engine-agnostic warehouse module,
• reusable Redshift connectivity and abstractions,
• a smoke-testable backend path from tenant_id to the resolved Redshift tenant database,
• reusable tenant warehouse bootstrap service,
• CLI commands for explicit tenant registration and tenant bootstrap execution,
• minimal routing metadata in kdsaidb,
• repeatable tenant database bootstrap,
• physical staging, curated, serving, and etl_meta schemas,
• physical source-mirror staging.<system>_<table> tables,
• physical source-mirror curated.<system>_<table> tables,
• an intentionally empty serving schema reserved for optional future convenience views.

Source Documents

• ../../docs/features/ETL/slices/01-redshift-foundation/index.md
• ../../docs/features/ETL/slices/01-redshift-foundation/source-task.md
• ../../docs/features/ETL/slices/01-redshift-foundation/foundation-spec.md
• ../../docs/features/ETL/slices/01-redshift-foundation/serving-mapping.md
• ../../docs/features/ETL/slices/01-redshift-foundation/follow-ups.md
• ../../docs/ETL.md

Spec Name

The canonical documentation slice is named 01-redshift-foundation.

This implementation spec should be referred to as Redshift Foundation - Phase 1.

Fixed Decisions

• tenant_id is per store.
• Use one Redshift database per tenant.
• Tenant routing metadata lives in kdsaidb.
• Tenant registration should persist lightweight control-plane metadata, not only a bare mapping.
• Metadata and administrative operations should be idempotent wherever reasonably possible.
• Phase 1 staging must stay source-aligned as staging.<system>_<table>.
• Phase 1 curated must stay source-mirror as curated.<system>_<table>.
• Redshift source prefixes use kds, aloha, and labpos; do not use kdsdev in Redshift object names.
• Phase 1 does not preserve canonical runtime tables such as curated.orders, curated.order_items, or curated.payments.
• Tenant bootstrap DDL and view definitions should live as SQL files in Kds.Ai.Warehouse.
• Phase 1 CLI should use separate administrative commands for tenant registration and tenant bootstrap.
• bootstrap-tenant should use validate-or-create semantics and be safe to re-run.
• register-tenant should not silently change tenant_database for an existing tenant_id.
• bootstrap-tenant should execute full tenant template bootstrap, not staged partial bootstrap.
• is_active should become true after successful bootstrap, not immediately on registration.
• Backend code must have a smoke-testable path for tenant_id -> kdsaidb mapping -> Redshift tenant connection.
• Agent integration is out of scope for this phase.
• Simulator migration is out of scope for this phase.
• ETL runtime implementation (COPY, UNLOAD, S3 pipeline) is out of scope for this phase.

Scope

This task includes:

• defining and implementing metadata needed for tenant routing,
• introducing a Redshift-oriented foundation layer,
• adding Redshift connectivity and configuration,
• adding a backend smoke path that exercises tenant resolution and Redshift connection,
• adding an explicit tenant registration command for metadata onboarding,
• implementing a repeatable tenant database bootstrap mechanism,
• adding an explicit tenant bootstrap command that uses previously registered metadata,
• physically creating the tenant database schema template,
• physically creating the initial internal warehouse structures,
• physically creating the initial source-mirror staging and curated tables for supported systems.

In short, phase 1 ends when a tenant can be registered, fully bootstrapped, marked active, and left with a complete standard warehouse template plus a minimal warehouse access boundary.

Out of Scope

The following items are explicitly excluded from this task:

• anomaly detector integration with Redshift,
• updates to WarehouseQueryToolProvider,
• updates to DataAccessPromptComposer,
• simulator migration to Redshift,
• monitor migration from source schemas to curated tables,
• ETL worker implementation,
• ingestion API implementation,
• S3 raw/load/curated implementation,
• Redshift COPY and UNLOAD logic,
• Athena and Glue integration,
• source onboarding logic,
• seed data creation,
• production hardening for retries, replay, DLQ, and observability.

Task Checklist

Phase 1: Metadata and Foundation

• [x] TASK 1.1 - Create tenant routing metadata in kdsaidb
• [x] TASK 1.2 - Create tenant warehouse foundation abstractions
• [x] TASK 1.3 - Add Redshift connectivity and configuration
• [ ] TASK 1.4 - Add backend tenant warehouse smoke path

Phase 2: Physical Tenant Database Bootstrap

• [x] TASK 2.1 - Implement tenant database bootstrap workflow
• [x] TASK 2.2 - Physically create internal warehouse structures and source-mirror tables

Phase 3: Physical Curated Contract

• [x] TASK 3.1 - Physically create source-mirror KDS curated tables (implemented as part of TASK 2.2)
• [x] TASK 3.2 - Physically create source-mirror Aloha curated tables (implemented as part of TASK 2.2)
• [x] TASK 3.3 - Physically create source-mirror Labpos curated tables (implemented as part of TASK 2.2)

Dependency Graph

Phase 1 (Metadata and Foundation)
    |-- 1.1 Tenant routing metadata
    |-- 1.2 Tenant warehouse foundation abstractions <-- (depends on 1.1)
    |-- 1.3 Redshift connectivity and configuration <-- (depends on 1.2)
    +-- 1.4 Backend tenant warehouse smoke path <-- (depends on 1.1, 1.2, 1.3)

Phase 2 (Physical Bootstrap) <-- depends on Phase 1
    |-- 2.1 Tenant database bootstrap workflow <-- (depends on 1.2, 1.3)
    +-- 2.2 Internal warehouse structures and source-mirror tables <-- (depends on 2.1)

Phase 3 (Curated Contract)
    +-- 3.1, 3.2, 3.3 folded into 2.2 so bootstrap creates the full phase-1 source-mirror surface in one pass

Recommended Session Order

Key Design Decisions

Foundation First

This task intentionally stops at the framework/foundation boundary. It creates the platform building blocks, but does not yet switch existing application behavior to Redshift.

Engine-Agnostic Warehouse Module

The new foundation should live in a new backend module named Kds.Ai.Warehouse.

The module represents warehouse capabilities in an engine-agnostic way. Redshift is the phase-1 implementation engine, but the project boundary should not assume Redshift is the permanent warehouse engine forever.

Platform-Owned Tenant Routing

Tenant-to-database routing is owned by the platform and stored in service-owned metadata in kdsaidb. It must not depend on agent-generated SQL.

Backend Integration Seam

Phase 1 must include a smoke-testable backend path that proves current backend code can route from a tenant_id to the correct Redshift tenant database without using the legacy shared PostgreSQL KDSContext.

The smoke path should:

• resolve tenant_id -> tenant_database from kdsaidb,
• reject inactive or missing mappings,
• open the resolved tenant Redshift database through Kds.Ai.Warehouse.

This is not anomaly detector integration and must not switch existing simulator or runtime behavior to Redshift.

Idempotent Administrative Operations

Wherever reasonably possible, the phase-1 administrative operations should be idempotent.

That applies especially to:

• tenant registration,
• tenant bootstrap,
• repeated execution after partial or successful prior runs.

Bootstrap Service First

Tenant warehouse bootstrap must be implemented first as a reusable service in the Redshift foundation layer.

Phase 1 also includes explicit CLI administration commands for:

• tenant registration in metadata,
• tenant warehouse bootstrap execution.

Admin API onboarding is expected later, but it is not part of this phase.

Separate Administrative Operations

Phase 1 should not collapse metadata registration and physical bootstrap into one command.

It should model them as two separate administrative operations so that the later admin API can follow the same split:

• register-tenant
• bootstrap-tenant

The registration step should persist lightweight control-plane metadata for the tenant warehouse mapping, including active state and timestamps, so the later admin API can build on the same model.

For safety, the registration flow should treat tenant_database as create-only mapping data. Repointing an existing tenant to a different warehouse database should require a separate later operation.

Registration should not mark the tenant active by itself. Activation should happen only after the warehouse bootstrap succeeds.

Idempotent Bootstrap

The phase-1 bootstrap operation should follow validate-or-create semantics.

That means:

• existing tenant warehouse objects should be validated,
• missing tenant warehouse objects should be created,
• rerunning bootstrap for the same tenant should be safe and expected.

Full Bootstrap Only

The phase-1 bootstrap command should produce a complete tenant warehouse template in one execution.

It should not expose staged partial bootstrap modes yet. After a successful run, the tenant should have:

• schemas,
• internal backing structures,
• source-mirror staging.<system>_<table> tables,
• source-mirror curated.<system>_<table> tables,
• the minimal warehouse access boundary needed for future curated-first runtime reads.

At that point, is_active = true should mean the tenant is ready to use as a phase-1 warehouse template, not merely that some DDL completed successfully.

Curated as a Source Mirror in Phase 1

The branch-aligned phase-1 curated layer is expressed through source-mirror objects:

• curated.kds_<table>
• curated.aloha_<table>
• curated.labpos_<table>

Phase 1 does not define canonical monitor-facing tables such as curated.orders, curated.order_items, or curated.payments.

Optional Serving Layer

serving no longer carries phase-1 runtime responsibilities.

• it may be added later as a convenience projection over curated data,
• it should remain physically present but empty in phase 1,
• it should not drive the initial runtime contract or smoke validation.

For phase 1, the bootstrap should mirror the current source schemas into Redshift without treating that source shape as the long-term canonical platform shape.

Explicit Tenant Database Mapping

tenant_database should be treated as explicit administrative control-plane data.

• it is provided during tenant registration,
• it is not derived from a hardcoded naming convention,
• changing it later requires a separate control-plane operation.

Physical Bootstrap in Phase 1

Phase 1 includes physical creation of the tenant schema template and source-mirror tables. It is not only a documentation/design exercise.

The bootstrap contract should be stored as explicit SQL artifacts in Kds.Ai.Warehouse, not hidden inside C#-generated DDL strings.

No Agent Integration Yet

This task must not change the anomaly detector query path. Any integration with WarehouseQueryToolProvider, DataAccessPromptComposer, or runtime Redshift reads belongs to a later task.