Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Implementation Specs (157)

Detailed engineering and feature implementation specifications.

implementation-specs/specs/RedshiftFoundation/TASK2.1.md 5,277 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

TASK 2.1: Implement Tenant Database Bootstrap Workflow

Summary

Implement the repeatable workflow that creates or validates a tenant-scoped Redshift database using the standard schema-level template.

This task is responsible for implementing the reusable bootstrap workflow and the schema-level base that later tasks extend with internal structures and curated tables.

The implementation must be exposed through:

a reusable bootstrap service,
a CLI command that invokes that service explicitly.

The CLI command in this task should assume that tenant routing metadata has already been registered through the separate registration flow from TASK 1.1.

Bootstrap should follow validate-or-create semantics rather than fail-fast semantics.

The final bootstrap-tenant command should run as one full tenant template operation after TASK 2.2 and TASK 3.x add their SQL artifacts. In this task, implement the workflow so later artifacts are executed by the same bootstrap path rather than by separate manual steps.

Dependencies

TASK 1.2 (tenant warehouse foundation abstractions)
TASK 1.3 (Redshift connectivity and configuration)

Files to Create

1. Bootstrap implementation and schema-level SQL/resources

Create the implementation that can create or validate a tenant Redshift database with:

staging
curated
serving
etl_meta

The implementation may use:

SQL scripts,
a CLI command,
a service,
a migration-style bootstrap workflow.

For phase 1, the tenant bootstrap contract should live in explicit SQL files inside Kds.Ai.Warehouse.

2. CLI command in `backend/Kds.Ai.Cli`

Create an administrative command equivalent to bootstrap-tenant that:

accepts tenant_id,
resolves the registered tenant database from metadata,
invokes the bootstrap service for that tenant.

Files to Modify

3. Foundation/bootstrap wiring as needed

Connect the bootstrap implementation to the abstractions created in TASK 1.2.

4. `backend/Kds.Ai.Cli`

Wire the CLI command to the bootstrap service exposed by Kds.Ai.Warehouse.

Required Behavior

Create or validate a tenant database for a resolved tenant_id
Physically create the required schemas
Treat etl_meta as a physically created part of the standard template
Support repeatable execution without corrupting already bootstrapped tenants
Support explicit bootstrap execution through CLI
Fail clearly when tenant metadata has not been registered yet
Validate existing objects and create only missing ones
Execute all currently registered bootstrap SQL artifacts in one command
Establish the minimal warehouse access boundary required for future curated-first runtime reads
Leave the workflow ready for later tasks to add internal structures and curated tables without adding new bootstrap commands
Mark the tenant as active only after the bootstrap service has executed all phase-1 artifacts available at that point

Acceptance Criteria

[x] Tenant database bootstrap workflow is implemented
[x] staging schema is physically created
[x] curated schema is physically created
[x] serving schema is physically created
[x] etl_meta schema is physically created as part of the standard template
[x] Bootstrap can run repeatably for the same tenant database
[x] A CLI command exists to invoke the bootstrap service explicitly
[x] The CLI bootstrap command resolves the tenant database from previously registered metadata
[x] Re-running bootstrap for the same tenant validates existing objects and creates only missing ones
[x] Bootstrap workflow includes creation of the minimal warehouse access boundary required by phase 1
[x] One successful bootstrap command executes all bootstrap artifacts registered in Kds.Ai.Warehouse
[x] The workflow is extensible so TASK 2.2 and TASK 3.x curated artifacts become part of the same bootstrap command
[x] Successful bootstrap updates tenant metadata so is_active = true only after all currently registered phase-1 artifacts complete
[x] Bootstrap SQL artifacts exist as explicit SQL files in Kds.Ai.Warehouse
[x] Solution builds without errors

Notes

This task establishes the reusable workflow and schema-level bootstrap base.
Subsequent tasks extend the same bootstrap workflow with the concrete internal structures and curated tables required for the complete phase-1 template.
The CLI command is an execution adapter for the bootstrap service, not the primary business abstraction.
Bootstrap should not create or infer tenant mapping implicitly; that belongs to the separate registration step.
Validate-or-create behavior should be the default contract because later admin/API onboarding will need safe retry semantics.
Keep DDL readable and reviewable as SQL artifacts rather than embedding the full contract in C# strings.
Do not add staged bootstrap switches in phase 1 unless a concrete need emerges later.
Activation of the tenant for warehouse use should happen after successful execution of all bootstrap artifacts known to the bootstrap service, not during registration.
Current implementation treats successful execution of all currently registered bootstrap SQL artifacts as the activation boundary for phase 1.