# 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](./TASK1.1.md) - Create tenant routing metadata in `kdsaidb`
- [x] [TASK 1.2](./TASK1.2.md) - Create tenant warehouse foundation abstractions
- [x] [TASK 1.3](./TASK1.3.md) - Add Redshift connectivity and configuration
- [ ] [TASK 1.4](./TASK1.4.md) - Add backend tenant warehouse smoke path

### Phase 2: Physical Tenant Database Bootstrap

- [x] [TASK 2.1](./TASK2.1.md) - Implement tenant database bootstrap workflow
- [x] [TASK 2.2](./TASK2.2.md) - Physically create internal warehouse structures and source-mirror tables

### Phase 3: Physical Curated Contract

- [x] [TASK 3.1](./TASK3.1.md) - Physically create source-mirror KDS curated tables (implemented as part of TASK 2.2)
- [x] [TASK 3.2](./TASK3.2.md) - Physically create source-mirror Aloha curated tables (implemented as part of TASK 2.2)
- [x] [TASK 3.3](./TASK3.3.md) - 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

| Session | Tasks | Description |
|---------|-------|-------------|
| 1 | 1.1, 1.2 | Metadata model and tenant-aware abstractions |
| 2 | 1.3, 1.4 | Redshift connectivity and backend smoke path |
| 3 | 2.1 | Tenant database bootstrap workflow |
| 4 | 2.2, 3.1, 3.2, 3.3 | Internal warehouse structures, source-mirror curated contract, and final validation |

---

## 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.