# Spensara: how it works, onboarding, tools, workflows and gaps

Prepared 2026-10-23 from the KDSAI codebase (`Bematechus/KDSAI`, `main` at `f2962c7`, 2026-06-24, plus 13 branches; the newest activity is `feature/fleet-import`, committed 2026-10-23 05:25 ET). Code was read through the xstore Gitea pull mirror, which was in sync with GitHub this morning. Nothing in GitHub or the mirror was changed.

"Spensara" is the product name. In code it is `KDSAI` / `Kds.Ai.*`. Do not rename the repo; the mirror depends on the name.

---

## 1. What Spensara is

A restaurant monitoring and anomaly detection platform driven by large language models. A manager describes what to watch in plain English ("watch prep times by hour, flag strange slowdowns"). The system turns that into a versioned, executable **MonitorSpec**, runs it on a schedule against the restaurant's own warehouse data, double-checks its own conclusions, and emits alerts (`Name`, `Summary`, `Content` in Markdown, `Severity` High/Medium/Low). Managers can also chat with their data.

Thirty example monitor specs ship in `backend/Kds.Ai.Data/MonitorSpecs/` (prep time, slow kitchen, station bottleneck, order surge, forgotten items, waste reduction, menu profitability, star and plowhorse items, demand and seasonality forecasts, customer segments and churn, pricing recommendations, inventory levels).

## 2. Architecture and components

| Layer | Component | Technology |
|---|---|---|
| Orchestration (dev) | `Kds.Ai.AppHost` | .NET Aspire 13: wires Postgres, Redis, Keycloak, MailPit, API, front end, simulator |
| API and workers | `Kds.Ai.ApiService` | ASP.NET Core (.NET 10), minimal APIs, OpenAPI/Scalar, hosted services |
| Job scheduler | TickerQ (EF Core store, Redis cache, dashboard) | Cron jobs per customer credential and per monitor |
| Agents | `Kds.Ai.AgentsCommon`, `Kds.Ai.AnomalyDetector`, `Kds.Ai.KnowledgeBase` | Microsoft Agent Framework + Microsoft model-abstraction libraries; models on Amazon Bedrock |
| App database | `kdsaidb` | PostgreSQL + pgvector (customers, users, credentials, specs, runs, alerts, ETL batches, checkpoints, embeddings) |
| Warehouse | `Kds.Ai.Warehouse`, `Kds.Ai.WarehouseDataModel` | Amazon Redshift Serverless, one database per tenant, schemas `staging` / `curated` / `serving` (Postgres stands in locally) |
| Data lake | S3 | `raw/`, `load/` (Parquet), `curated/` (Parquet via Redshift `UNLOAD`), `failed/` |
| Source adapters | `Kds.Ai.Integrations`, `Kds.Ai.Integrations.BrinkPos` | ETL source adapter framework; Brink POS (SOAP), KDS CDC (Debezium over cross-account SQS FIFO), Fleet file import (branch) |
| Identity | Keycloak | OIDC; roles; bootstrap via `Kds.Ai.Cli keycloak` |
| Front end | `Front/` | Laravel 13 / PHP 8.3, Blade + Tailwind; backend-for-frontend only, no own database. A Vue dashboard is in progress on `kd/vue` and `kd/dashboard-vue` |
| Notifications | `Kds.Ai.Notifications` | Email (MailKit), SMS (Twilio) |
| Simulator | `backend/simulator` | Traffic simulator and Blazor (MudBlazor) web app producing demo data |
| CLI | `Kds.Ai.Cli` | `anomaly-detector`, `knowledge-base`, `schema-migration`, `warehouse`, `keycloak` commands |
| CI/CD | `.github/workflows/` | GitHub Actions: build and test; build images, push to ECR, roll ECS task definitions (backend, front, simulator, C4 diagrams) using an assumed AWS role |
| Observability | OpenTelemetry | Traces/metrics from API, HTTP and TickerQ |

Not in this repo: AWS infrastructure as code. CI deploys to ECS services that already exist; the infrastructure appears to live in separate mirrored repos (`KDS.AWS.Devops.Infrastructure`, `KDS.AWS.Devops.Infrastructure.Core`, `KDS.Devops`), which were not reviewed here.

### Agent roles (docs/README.md)
Orchestrator, Spec Builder (talks to the manager), KB / Semantic agent (what data exists and what it means), Data Access agent (the only component that queries data, read-only and tenant-scoped), Runtime Detector (runs the spec as a runbook: preflight, broad scan, drilldowns), Critic / Verifier (blocks weak alerts), Alert Emitter (dedupe and send).

### Data platform (docs/ETL.md, docs/flows/etl-ingestion.md)
Four ingestion modes (file upload, API push, API pull, CDC) converge into one path:

`source -> S3 raw (immutable) -> normalize to Parquet (S3 load) -> Redshift COPY into tenant staging -> SQL transform into curated -> serving views -> UNLOAD curated Parquet to S3 -> Athena for cross-tenant queries`

Rules: the pipeline owns correctness (agents never fix data at query time); checkpoints advance only after a batch succeeds end to end; failed packages are kept for replay; each tenant has its own Redshift database.

## 3. Brink POS path today (summary; the separate Brink pipeline analysis goes deeper)

- Endpoints: `Sales2.GetOrders`, `Labor.GetShifts`, `Settings2.GetItems`, `Settings2.GetEmployees`, `Settings2.GetJobs` (`GetDestinations` used as a lookup).
- Stages: `extract_raw` -> `normalize_load` -> `load_to_staging` -> `staging_to_curated` -> `curated_to_s3` (export of `pos_orders`, `pos_order_items`).
- About 31 staging tables; 7 curated tables (`pos_orders`, `pos_order_items`, `pos_menu_items`, `employees`, `pos_jobs`, `labor_shifts`, `pos_destinations`).
- Scheduling exists in code: `BrinkCronSyncService` registers one TickerQ cron per enabled Brink credential. Default is `0 0 1 * * *` (01:00 America/New_York) for business date minus 1, with a 2-day lookback (maximum 7). So the code is batch daily, not real time. Where it feels manual today, the likely causes are deployment and configuration (credential disabled, schedule off, worker not running in the deployed environment), not missing code. That needs confirming in AWS.

## 4. How to onboard a customer (every step)

From the code (`AdminCustomerEndpoints`, `CustomerEtlProvisioningService`, `TenantWarehouseBootstrapper`, the `CustomerCredentialTriggeredProvisioning` spec) and the docs:

1. **Commercial and data prerequisites.** Signed agreement; confirm which sources the store runs (Brink, Logic Controls KDS, Aloha, LabPOS, files). Check the franchise agreement for data and third-party software restrictions (see the tracker's Franchise Tech & Data tab).
2. **Collect source access.** For Brink: access token, location token(s), service URL, per location. For KDS CDC: cross-account SQS FIFO and Debezium connector on the customer side. For files: Fleet landing access and a manifest contract.
3. **Create the customer** (admin API / admin UI): company, solutions enabled (`PUT /customers/{id}/solutions`). This creates the tenant id.
4. **Create users and roles in Keycloak** and link them to the customer (membership drives tenant scoping; the front end never sets tenant headers itself).
5. **Add the source credential** (`POST /customers/{id}/api-keys`). Credentials are encrypted at rest (data-protection envelope). Optional custom headers and a schedule (time zone, cron, lag, lookback, enabled) are stored with it.
6. **Provisioning runs.** Saving a credential provisions the tenant warehouse: register the Redshift database, then bootstrap `staging` tables only for enabled sources plus all `curated` and `serving` objects. On `main` this blocks customer create/update for about 30 seconds; the spec moves it to an asynchronous TickerQ job with visible status.
7. **Schedule registration.** On startup and on change, the cron sync service creates or updates the TickerQ cron for the credential.
8. **Backfill.** Run the ETL for historical business dates (lookback is capped at 7 days per run; longer history needs repeated or replay runs).
9. **Verify data.** Row counts per staging and curated table, freshness, failed packages empty, curated Parquet present in S3.
10. **Knowledge base.** Run KB generation (`knowledge-base` CLI): capabilities manifest (tables, joins, units, freshness), KPI catalog from the restaurant's manuals and SOPs, document index.
11. **Monitors.** Seed or author MonitorSpecs with the manager; backtest; approve and enable; set cron.
12. **Alerts.** Configure recipients (email, SMS); confirm a test alert arrives.
13. **Hand over.** Manager login, short training, support contact.

Not yet automated or productized: self-service onboarding, a tenant admin control plane (designed on branch `tenat-admin`, not built), store groups and multi-store access, Fleet schema approval (branch `feature/fleet-import`), and a documented runbook for offboarding.

## 5. Tools used to process data

| Purpose | Tool |
|---|---|
| Source extraction | Brink SOAP client (Polly resilience), Debezium CDC over AWS SQS FIFO, Fleet S3 file landing |
| Raw and lake storage | Amazon S3 (AWSSDK.S3) |
| Normalization | Parquet.Net (XML/JSON to Parquet), System.IO.Hashing for checksums |
| Warehouse load | Redshift `COPY` from S3 Parquet; Npgsql in Redshift compatibility mode |
| Transform | Embedded SQL (`DELETE` then `INSERT` per tenant and date, inside one transaction) |
| Export | Redshift `UNLOAD` to S3 Parquet |
| Cross-tenant query | Athena + Glue catalog (designed, not built) |
| Scheduling | TickerQ cron and time tickers, Cronos |
| Schema | EF Core migrations (app DB, 26+), `efcpt` reverse-engineered warehouse model, warehouse bootstrap SQL |
| Knowledge base | MarkItDown, Markdig, Microsoft.Extensions.DataIngestion, tokenizers, pgvector |
| Models | Amazon Bedrock via the Microsoft model-abstraction libraries |
| Local stand-ins | Postgres for Redshift, LocalStack for S3/SQS (used on spensara-sandbox) |
| API testing | Bruno collections (`backend/bruno`) |

## 6. Workflows

1. **KB generation** (`docs/flows/kb-generation.md`): schemas and vendor docs -> profiler -> join mapper -> capabilities manifest; manuals -> KPI extractor -> KPI catalog and document index.
2. **Monitor authoring** (`monitor-authoring.md`): manager prompt -> Spec Builder -> feasibility check against the KB -> backtest -> revise -> approve and enable.
3. **Runtime execution** (`runtime-execution.md`): cron -> load spec -> preflight freshness -> broad scan -> drilldowns -> critic -> emit alert -> record run history and dedupe.
4. **ETL ingestion** (`etl-ingestion.md`): the five stages above, per tenant, with checkpoints, failed packages and replay.
5. **KDS CDC** (`docs/features/ETL/kds-cdc/`): SQS micro-batches -> raw -> Parquet -> staging -> curated -> export, with quarantine and a replay coordinator.
6. **Fleet pre-ingestion** (branch): customer uploads one file plus a manifest -> schema version checked against an approved contract registry -> validated -> published to `raw/source_type=fleet` -> standard ETL. This is the closest existing design to the "Borg" idea (shape and approve before anything enters the pipeline).
7. **Customer onboarding and provisioning** (section 4).
8. **CI/CD**: pull request build and test -> merge to `main` -> images to ECR -> ECS rollout.
9. **Development method**: work is driven by `specs/<Feature>/TASK*.md` files and agent guidance (`AGENTS.md`, `.agents/skills/`). Euvic builds with coding agents and leaves the spec trail in the repo, which makes handover easier.

## 7. Gap analysis: what remains for a fully finished product

Severity: **P1** blocks paying customers, **P2** needed for scale, **P3** polish.

| # | Gap | Sev | Evidence |
|---|---|---|---|
| 1 | No Brink data running on a schedule in production as Tom sees it; batch is daily (D-1), not near real time | P1 | Cron exists in code; deployed state unverified; no intraday or polling mode |
| 2 | `main` has been static since 2026-06-24 while 5 branches hold large unmerged work (`life-cycle`, `extract-raw-xml`, `kds_cdc_improvments` marked "TO REVIEW", `feature/fleet-import`, `tenat-admin`) | P1 | Branch tips; ~330 branch-only doc files |
| 3 | Onboarding blocks for ~30 s and has no status or retry UI; async provisioning spec not merged | P1 | `CustomerCredentialTriggeredProvisioning` spec |
| 4 | Curated model incomplete: payment method, store id/name, category names, currency, surrogate keys and SCD2 history are NULL; only 2 of 7 curated tables exported to S3 | P1 | `brink-pos-first-integration.md` limitations |
| 5 | Only Brink is live as a POS source; Aloha, LabPOS and KDS staging models are documented but not wired end to end; KDS CDC is code-complete on `main` but needs a production SQS link | P2 | warehouse-data docs, kds-cdc docs |
| 6 | Cross-tenant layer (Athena, Glue, store groups, multi-store reads) designed only | P2 | `ETL.md` cross-tenant sections |
| 7 | Tenant admin control plane (clients, tenants, systems) designed only | P2 | `tenat-admin` branch |
| 8 | Infrastructure as code is outside this repo and not reviewed; environment reproducibility unknown | P2 | No Terraform/CDK in KDSAI |
| 9 | Front end is a basic Laravel/Blade admin and chat; the Vue dashboard is unfinished; no branding to Tom's design; README is the stock Laravel one | P2 | `Front/`, `kd/*` branches |
| 10 | Operational hardening: alarms, DLQ handling, freshness SLAs, cost guards on Redshift and Bedrock, runbooks, on-call | P2 | `ETL.md` operational requirements vs code |
| 11 | Security: secrets management in deployed env, credential rotation, audit of read-only enforcement in the Data Access tools, tenant isolation tests | P2 | auth docs |
| 12 | Upstream bugs found in sandbox testing (Redshift `dev` database default, `SUPER` type in local migrations) not yet reported or fixed | P3 | `SESSION_LOG.md` in Tom's Spensara folder |
| 13 | Root `README.md` is empty; no customer onboarding runbook, no offboarding or data deletion procedure | P3 | repo root |
| 14 | Edge appliance (Debian, Apache 2.4 + PHP 8.4 FPM, MariaDB, Cockpit on the Intel 9550) has no counterpart in this codebase; the store-side collector and its sync to the cloud ingestion API are undefined | P2 | not present in KDSAI |