# "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
| Concern | A Edge-normalize | B Raw-then-normalize | C Hybrid |
|---|---|---|---|
| Replay / reprocessing (a mapping bug, a new curated column) | **Weak.** The raw data is gone, so you must re-pull from the source. Brink can be re-pulled by business date, but only within its retention window. A customer's S3 data may have been overwritten. | Strong | Strong: replay from sealed raw |
| Audit / lineage ("where did this number come from?") | Only curated rows plus Borg logs | Full | Full: each curated file carries `raw_object_key`, `contract_version`, `borg_version` |
| Schema evolution | Clean: the contract is enforced in one place. A new field is dropped until mapped, which is safe but loses data. | Flexible but messy (drift seeps into staging) | Enforced contract, and unmapped fields are kept in raw for later backfill |
| Failure blast radius | A Borg bug corrupts curated directly, with nothing to fall back to. The loader is trivial, so its failures are small. | Bad SQL in the warehouse affects every tenant at once | A Borg bug → re-run from raw. Bad input is stopped at the gate for **that source only**. |
| Customer isolation | **Strong:** one bucket/KMS key per customer holds only curated data. That's a good story for franchise and data-ownership covenants (see the Franchise tab: brands own the data, and less raw PII leaves the store). | Weaker unless raw is also siloed | Strong if raw is also per-customer, KMS-encrypted, with a short TTL (e.g. 30–90 d), or stays on the Edge device |
| Cost | Lowest storage and the cheapest Redshift (COPY only) | More storage and more Redshift compute (transform in-warehouse) | Little extra cost: S3 IA/Glacier for raw |
| Latency | Good: one hop | Extra hop | Same as A, since raw is written in parallel |
| Ops simplicity of the AWS side | **Best:** Scheduler → COPY/MERGE | Complex | Same as A |

**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).