Spensara Spensara Knowledge Base & Documentation Repository
System Overview Brink & Borg Master Index (722 Docs) Spensara Platform Project Tracker

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)

Categories

Implementation Specs (157)

Detailed engineering and feature implementation specifications.
implementation-specs/specs/RedshiftFoundation/TASK1.1.md 4,394 bytes · Updated Oct 23, 2026 10:44 AM EDT
Raw File

TASK 1.1: Create Tenant Routing Metadata in kdsaidb

Summary

Create the minimal operational metadata needed to support tenant-aware Redshift routing in kdsaidb.

This task establishes the service-owned metadata model for:

  • tenant warehouse mapping,
  • ingestion batch skeleton,
  • checkpoint skeleton.

It also establishes the first explicit administrative operation for the new warehouse foundation: tenant registration in metadata.

The registration step should create more than a bare key-value mapping. It should persist lightweight control-plane metadata that later admin/API onboarding can reuse.

Registration should be idempotent where possible, but it should not activate the tenant warehouse yet.

This is a foundation task. It is not the final ETL runtime model.

Dependencies

  • None (first task)

Files to Create

1. Metadata entities/configurations/migration files in backend/Kds.Ai.Data

Create the entity and EF configuration set needed to persist:

  • tenant warehouse mapping,
  • ingestion batch skeleton,
  • checkpoint skeleton.

Recommended logical entity split:

  • TenantWarehouseMapping
  • IngestionBatch
  • IngestionCheckpoint

The exact names may differ, but the concepts must be clearly represented.

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

Create an administrative command equivalent to register-tenant that:

  • accepts tenant_id,
  • accepts tenant_database,
  • upserts the tenant warehouse mapping in kdsaidb,
  • initializes lightweight control-plane metadata such as active state and timestamps.

Registration safety rule:

  • if tenant_id does not exist, create the mapping,
  • if tenant_id exists with the same tenant_database, allow idempotent confirmation/update of lightweight metadata,
  • if tenant_id exists with a different tenant_database, fail clearly instead of silently changing the warehouse target.

Files to Modify

3. backend/Kds.Ai.Data/KdsAiDbContext.cs

Register the new metadata sets in the application database context.

Required Model Shape

Tenant warehouse mapping

Minimum logical fields:

  • tenant_id
  • tenant_database
  • is_active
  • created_at
  • updated_at

For phase 1, this mapping is the lightweight control-plane state for tenant warehouse registration.

Registration rule:

  • newly registered tenants should start with is_active = false,
  • is_active should only become true after successful bootstrap.

Ingestion batch skeleton

Minimum logical fields:

  • batch_id
  • tenant_id
  • tenant_database
  • ingestion_mode
  • source_type
  • status
  • received_at
  • load_started_at
  • load_completed_at
  • raw_s3_path nullable
  • load_s3_path nullable

Checkpoint skeleton

Minimum logical fields:

  • tenant_id
  • source_type
  • checkpoint_key
  • checkpoint_value
  • updated_at
  • last_successful_batch_id nullable

Acceptance Criteria

  • [x] Metadata entities are created in Kds.Ai.Data
  • [x] EF configurations follow project conventions
  • [x] DbSets are registered in KdsAiDbContext
  • [x] A migration is created for the new metadata tables
  • [x] tenant_id -> tenant_database can be persisted in kdsaidb
  • [x] Tenant registration persists is_active, created_at, and updated_at
  • [x] Batch skeleton metadata can be persisted in kdsaidb
  • [x] Checkpoint skeleton metadata can be persisted in kdsaidb
  • [x] A CLI command exists to register or update tenant warehouse mapping metadata
  • [x] Tenant registration does not silently change tenant_database for an existing tenant
  • [x] New tenant registration initializes is_active = false
  • [x] Re-running tenant registration for the same tenant_id and tenant_database is idempotent
  • [x] Solution builds without errors

Notes

  • Keep the metadata intentionally small and extensible.
  • Do not over-model full ETL runtime concerns yet.
  • This task creates the authoritative metadata store for later tenant routing.
  • Tenant registration should be modeled as a separate administrative operation from physical bootstrap.
  • The registration command should behave like a lightweight control-plane entry point, not just a raw mapping writer.
  • Repointing a tenant to a different warehouse database should require a separate later operation, not normal registration flow.
  • Registration should not imply readiness for warehouse use; bootstrap success is the activation boundary.