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/RedshiftSyntheticDataFeed/TASK.md 8,270 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Redshift Synthetic Data Feed - Implementation Tasks

Overview

Implementation of the Redshift Synthetic Data Feed delivery slice.

This task plan is derived from the documentation set in:

../../docs/features/ETL/slices/02-redshift-synthetic-data-feed/index.md
../../docs/features/ETL/slices/02-redshift-synthetic-data-feed/synthetic-data-feed-spec.md
../../docs/features/ETL/slices/02-redshift-synthetic-data-feed/follow-ups.md
../../docs/features/ETL/slices/01-redshift-foundation/serving-mapping.md
../../docs/ETL.md

This task creates a temporary bridge from the existing PostgreSQL simulator data to tenant-scoped Redshift curated data.

It does not build the full ETL worker, S3 flow, Redshift COPY, Redshift UNLOAD, Athena, source adapters, anomaly detector runtime cutover, or monitor prompt migration.

The slice outcome is:

configured synthetic tenant_id and source store_guid,
a reusable synthetic KDS feed service,
source readers for current PostgreSQL kdsdev.orders, kdsdev.items, and kdsdev.item_bumps,
source-mirror mapping into the phase-1 curated.kds_orders, curated.kds_items, and curated.kds_item_bumps shape,
tenant-resolved Redshift write into curated,
idempotent rerun behavior through checkpoints or deterministic full refresh,
an explicit CLI sync command,
Redshift curated.kds_orders, curated.kds_items, and curated.kds_item_bumps validation.

Source Documents

../../docs/features/ETL/slices/02-redshift-synthetic-data-feed/index.md
../../docs/features/ETL/slices/02-redshift-synthetic-data-feed/synthetic-data-feed-spec.md
../../docs/features/ETL/slices/02-redshift-synthetic-data-feed/follow-ups.md
../../docs/features/ETL/slices/01-redshift-foundation/index.md
../../docs/features/ETL/slices/01-redshift-foundation/serving-mapping.md
../../docs/ETL.md

Spec Name

The canonical documentation slice is named 02-redshift-synthetic-data-feed.

This implementation spec should be referred to as Redshift Synthetic Data Feed.

Fixed Decisions

Implement this slice as a sync/feed from existing PostgreSQL simulator data to tenant Redshift.
Do not rewrite the simulator as the default implementation path.
The existing PostgreSQL simulator write path may remain available.
The feed writes to Redshift curated.
The synthetic feed writes to the source-mirror KDS curated tables from slice 01.
The synthetic feed does not write any canonical curated.orders, curated.order_items, or curated.payments objects.
Do not introduce persistent source-specific staging tables.
If intermediate tables are needed, use session-local temp tables or private implementation details.
Target Redshift database is resolved from tenant_id through platform-owned metadata.
store_guid is only a source filter, not a Redshift target selector.
The feed must be idempotent and safe to rerun.
Minimal checkpointing or deterministic full refresh is sufficient.
S3, Redshift COPY, Redshift UNLOAD, Athena, Glue, source adapters, and production replay/backfill are out of scope.
Runtime read-path migration remains in slice 03.
Monitor prompt migration remains in slice 04.

Scope

This task includes:

adding synthetic feed options and request/result contracts,
adding a reusable synthetic feed service,
reading current simulator-generated rows from PostgreSQL kdsdev source tables,
mapping those rows into source-mirror Redshift curated.kds_* models,
writing/upserting source-mirror rows into the resolved tenant Redshift database,
recording minimal batch/checkpoint state where available,
adding an explicit CLI sync command,
validating that synthetic rows are visible through curated.kds_orders, curated.kds_items, and curated.kds_item_bumps.

In short, the slice ends when a registered and bootstrapped tenant can receive current synthetic KDS order data in Redshift and expose it through source-mirror curated tables.

Out of Scope

The following items are explicitly excluded from this task:

anomaly detector integration with Redshift,
updates to WarehouseQueryToolProvider,
updates to DataAccessPromptComposer,
monitor spec migration from kdsdev. to curated.,
simulator rewrite,
ETL worker implementation,
ingestion API implementation,
S3 raw/load/curated implementation,
Redshift COPY and UNLOAD logic,
Athena and Glue integration,
production source adapters,
production replay/backfill/DLQ tooling,
non-KDS runtime domains.

Task Checklist

Phase 1: Feed Contract and Source Data

[x] TASK 1.1 - Add synthetic feed configuration and service contract
[x] TASK 1.2 - Implement PostgreSQL KDS source reader
[x] TASK 1.3 - Implement source-to-source-mirror curated mapping

Phase 2: Tenant Redshift Write Path

[x] TASK 2.1 - Implement tenant-resolved Redshift curated writer
[x] TASK 2.2 - Add idempotency, checkpoint, and full-refresh behavior

Phase 3: Execution and Validation

[x] TASK 3.1 - Add CLI command for synthetic KDS sync
[x] TASK 3.2 - Add curated validation and smoke checks

Dependency Graph

Prerequisite
    +-- Redshift Foundation complete
        +-- tenant routing metadata
        +-- tenant warehouse connection factory
        +-- bootstrapped tenant database
        +-- curated KDS source-mirror structures
        +-- curated KDS source-mirror tables

Phase 1 (Feed Contract and Source Data)
    |-- 1.1 Synthetic feed configuration and service contract
    |-- 1.2 PostgreSQL KDS source reader <-- (depends on 1.1)
    +-- 1.3 Source-to-source-mirror curated mapping <-- (depends on 1.2)

Phase 2 (Tenant Redshift Write Path)
    |-- 2.1 Tenant-resolved Redshift curated writer <-- (depends on 1.1, 1.3)
    +-- 2.2 Idempotency, checkpoint, and full-refresh behavior <-- (depends on 2.1)

Phase 3 (Execution and Validation)
    |-- 3.1 CLI synthetic KDS sync command <-- (depends on 2.2)
    +-- 3.2 Curated validation and smoke checks <-- (depends on 3.1)

Recommended Session Order

Session	Tasks	Description
1	1.1	Options, request/result models, feed service contract
2	1.2, 1.3	Source reader and source-mirror mapper
3	2.1	Tenant-resolved Redshift writer/upsert
4	2.2	Idempotency, checkpointing, full refresh
5	3.1, 3.2	CLI sync and curated validation

Key Design Decisions

Bridge, Not Final ETL

This slice creates a temporary bridge from the current simulator data to Redshift. It must be clearly named as synthetic/bootstrap infrastructure and remain easy to replace with the real ETL runtime.

PostgreSQL Remains the Simulator Source

The existing simulator may keep writing through KDSContext into PostgreSQL. This slice reads that data and mirrors the KDS source subset into Redshift.

Redshift Target Is Source-Mirror Curated

The feed writes source-mirror rows into curated.kds_orders, curated.kds_items, and curated.kds_item_bumps.

No Persistent Staging

This slice should not create long-lived source-specific staging objects. If temporary staging is technically useful for batch upsert, keep it session-local or private to the implementation.

Tenant Routing Is Platform-Owned

The target database is resolved from tenant_id using the warehouse foundation from slice 01. The feed does not derive database names or accept tenant database names as normal user input.

Idempotent Reruns

The feed must be safe to rerun for the same source window. Use deterministic keys:

guid for orders,
guid for items,
guid for item bumps.

Repeated runs must update existing rows rather than duplicate them.

Minimal Operational State

Use the ingestion batch and checkpoint skeletons from slice 01 where useful. Do not expand them into the full ETL runtime model.

Curated Validation Is Required

Success is measured through Redshift curated. The final smoke validation must prove that curated.kds_orders, curated.kds_items, and curated.kds_item_bumps return synthetic rows.