"The Borg": Spensara's unified ingestion tool. Architecture options and recommendation

2026-10-23 (ET). This builds on analysis.md, the Brink findings. The code facts there are based on file names and logs; see its §0.

1. Tom's leaning, as stated

The Borg gathers data from every source, then assimilates, understands, maps and plans. It normalizes everything into the curated format, and only then streams it securely into the customer's own siloed S3 bucket. Nothing uncurated reaches S3, and any source the Borg can't shape is blocked at the door. A generic, low-logic AWS process (timers plus ingestion, no transformation) then loads S3 into the curated Redshift tables on a schedule.

2. Options

• A. Edge-normalize (Tom's leaning). Curated-only in S3, and a "dumb" loader.
• B. Land raw, normalize later. This is roughly today's KDSAI design: raw goes to S3 and staging (SUPER), then normalize → curated, all in AWS.
• C. Hybrid (recommended). The Borg normalizes before the customer's bucket, and keeps a sealed, quarantined raw zone. The raw zone is write-once and time-limited, the loader never reads it, and it's used only for replay and audit.

3. Tradeoffs

Verdict: the core of Tom's idea is right: curate at the Borg, keep AWS dumb, siloed buckets, and block what can't be shaped. Doing it without a raw copy gives up replay and lineage, and turns every Borg bug into permanent data loss. Recommendation: Option C.

One correction to "no data manipulation" in the AWS loader: it still has to do a key-based MERGE, i.e. dedupe or upsert on a natural key. Re-pulled Brink days and late voids otherwise double-count. That counts as load semantics, not transformation, and it's generic: one stored procedure parameterized by table and key.

4. Architecture (Option C)

 Sources ──► BORG (container; runs in Spensara AWS per tenant, and/or on Edge 9550 for in-store sources)
             1 Connect   (connector per source type, creds from Secrets Manager)
             2 Discover  (sample + infer schema; fingerprint)
             3 Map       (match to a registered Mapping Contract vN → curated tables; unknown fingerprint = BLOCKED, alert, await approval)
             4 Normalize (types, time zones/business date, IDs, currency; PII scrub/hash per policy)
             5 Validate  (contract + row rules; failing rows → quarantine)
             6 Seal raw  → s3://spensara-raw-{tenant}/source/date/...   (Object Lock, KMS-tenant, TTL; never read by the loader)
             7 Publish   → s3://spensara-cur-{tenant}/{curated_table}/load_date=/batch=.parquet + _manifest.json
                                  (manifest: batch_id, row counts, checksum, contract_version, raw keys, watermark)
 AWS generic loader (no business logic):
   S3 manifest event / EventBridge Scheduler (5 min) ─► Step Functions ─► Redshift Data API:
      COPY → temp table → MERGE INTO curated.<table> ON <contract key> → write load_audit → ack manifest
   Failure → retry 3× → SNS alert; manifest stays pending (idempotent re-run by batch_id)
 Curated Redshift (per-tenant DB, as today's TenantWarehouseMapping) ─► Spensara API / analysis agents (read-only)

The control plane is a small registry: Sources, Contracts (versioned), Fingerprints, Approvals, Watermarks and the batch ledger. It can reuse KDSAI's IngestionBatch, IngestionCheckpoint and KdsCdcQuarantineRecord patterns.

5. What the Borg does per source type

(a) Brink API (pull, business-date model)

• Connect: per-location Brink token + service URL (Secrets Manager); reuse BrinkPosEndpointCatalog / BrinkPosEndpointExecutor.
• Plan: every 5–15 min, re-pull the current business date for each location (BrinkPosBusinessDateResolver / ScheduleTimeZoneResolver). Nightly, re-pull D-1/D-2. Respect rate limits, with backoff and jitter.
• Map: the contracts already exist in code: BrinkPosNormalizationCatalog + BrinkPosCuratedTargetCatalog → curated orders, items, modifiers, payments, discounts, voids, labor and settings. The Borg is largely a re-home of the existing BrinkPos stages, moving Normalize and StagingToCurated ahead of S3 instead of inside Redshift.
• Keys / dedupe: (location_id, business_date, order_id[, line_no]). Voids and refunds update the same key, and MERGE handles them.
• Drift: new or unknown Brink fields stay in the sealed raw zone, trigger an alert, and go to a contract bump plus backfill from raw. A type change on a mapped field quarantines that endpoint.
• Block at the door: an unknown endpoint or response shape gets no contract, so nothing publishes.

(b) Arbitrary customer S3 sources (push or drop; unknown shape)

• Connect: cross-account IAM role in the customer's account (ExternalId), read-only on the named prefix, with S3 event notification → Borg queue (or a poll on a schedule).
• Discover: detect the format (CSV/JSON/Parquet/XLSX), infer the schema, profile columns, and compute a fingerprint (column set + types).
• Map: an assisted mapping proposal (column → curated field, with confidence) that requires a person's approval to become Contract v1. Until then the source is blocked: raw is sealed, and nothing is published.
• Normalize: units, time zones, store/location ID crosswalk (customer store # → Spensara location), product and menu crosswalk.
• Incremental: object key + ETag ledger (exactly-once per object). For full-snapshot drops, diff against the previous snapshot by key.
• Drift: a fingerprint change stops that source only, alerts, and asks for a new contract version.
• Isolation: never co-mingle. One tenant's role can't read another tenant's prefixes, with per-tenant KMS keys.

6. Build sequence (tied to the Brink findings)

1. Decide the warehouse target and the bucket layout. Redshift Serverless per-tenant DB plus spensara-raw-{tenant} and spensara-cur-{tenant} buckets with KMS. Reconcile with Maciej's Redshift→S3/Athena PR. (1 wk, includes the review)
2. Generic loader first (from analysis.md B2/B5/B8: Step Functions + COPY→MERGE + manifest ledger + alarms). It works for any source once a curated Parquet file and manifest exist. (1.5 wk)
3. Borg v0 = Brink. Extract the BrinkPos Extract/Normalize/Curated stages into a Borg worker container that writes sealed raw plus curated Parquet and a manifest. The 5-min intraday and nightly re-pull schedules run from EventBridge Scheduler. This delivers Tom's original ask: real-time Brink into curated. (2–2.5 wk)
4. Control plane: contracts, fingerprints, approvals, quarantine and the watermark registry, plus a Bridge Admin page. (1.5 wk)
5. Borg v1 = customer S3 connector, with discovery, assisted mapping and approval gating. (2–3 wk)
6. Edge placement: run the same Borg container on the Intel 9550 Edge for in-store sources (KDS, printers, Aloha). It pushes curated data plus sealed raw over mTLS to the tenant buckets. (later phase)

That is about 9–11 engineer-weeks to step 5. Steps 1–3 (about 5 weeks) deliver automated Brink → curated with replay.

7. Assumptions

• Brink offers pull APIs only, with no webhook or stream, and a business-date retention window long enough for the nightly re-pull.
• Curated tables keep their current names and shapes. The Borg's output contract equals today's curated schema (11 tables).
• Tenant = customer, as in today's TenantWarehouseMapping.
• None of the code behavior was confirmed from file contents (see analysis.md §5).