# COW — knowledge graph

COW ("Core Warehouse", also called Assessment-as-a-Service / PWS) is Pearson's assessment-data warehouse and extract platform.
It pulls test results out of ADAM, lands them in a Redshift Data Vault (one database per admin code), and builds customer
extract files (SDF, IRF, Summative, Summary, growth, forensics…) from materialized views. It then splits, zips and delivers
those files to Family Portal, ACT, ADAM, SFTP and internal users. The graph covers 16 repos. `source` paths without a prefix
are in **cow-deployment**; every other path starts with its repo name (`cow-etl-as-a-service/jobs/...`).

## Start here
1. [[run-extract-order]] — the end-to-end extract trace across five repos. Read this first.
2. [[redshift-warehouse]] + [[wh-data-vault-conventions]] + [[test-attempt]] — the warehouse model.
3. [[etl-extract-catalog]] and [[glue-redshift-extract-jobs]] — what extracts exist and how they are built from the `mv-*` views.
4. [[reporting-api]] / [[reporting-workers]] / [[workflow-service]] — orders, documents (Doc Depot) and the workflow conductor.
5. [[extract-interface-backend]] + [[xi-authz]] — the current ordering UI (successor to the legacy [[depotui-lambdas]]).
6. [[admin-spa-backend]] — the ops console: ingest, loads, Redshift tools, extract permissions.
7. [[ingest-adam-to-warehouse]] — how data gets in.
8. [[tenant]] + [[cdklibs]] + [[platform-deploy-service-to-tenants]] — the per-tenant deployment model every CDK service uses.
9. [[order]], [[pws]], [[cow]] — glossary for the most overloaded words.

## Main flows
- Extracts: [[run-extract-order]] (sub-flows: [[xi-start-extract-order]], [[xi-scheduled-extract-run]], [[xi-deliver-completed-order]], [[xi-download-documents]], [[xi-adam-manifest-download]]), [[route-uploaded-document]], [[send-abbi-digest]].
- Data in: [[ingest-adam-to-warehouse]], [[ingest-stm]] (legacy), [[ingest-pdfs-to-doc-depot]], [[export-pdfs-to-family-portal]], [[load-scoring-categories]], [[load-es-objectives]], [[load-pr-growth]], [[forensics-data-refresh]].
- Access: [[grant-extract-permission]], [[xi-spa-sign-in]], [[platform-onboard-tenant]].
- Release and ops: [[release-container-service]] (GitHub Actions + terragrunt, for the ECS apps), [[promote-cdk-service]] (Jenkins + CDK, for the Lambda/Glue services), [[roll-out-redshift-migration]], [[alert-job-failure-to-slack]].

## Layout
**cow-deployment** (primary) holds the Terraform modules and terragrunt `live-infrastructure` for the ECS-hosted apps: Admin SPA,
Extract Interface, the flyway and refresh-views tasks, alerting, network and WAF. It also holds the Jenkins job DSL that
promotes the CDK services. **cow-manifest** pins every deployed version. The Java CDK "as-a-service" repos (etl, data,
reporting, workflow, batch) each deploy one stack per tenant through [[cdklibs]] onto the API Gateway, authorizer and ECS
cluster that **cow-sacoreservtenant** provisions. ETL logic is PySpark Glue jobs plus a Java ECS worker
(`cow-etl-as-a-service/jobs`, `ecs-task-image`). The warehouse schema lives in **cow-etl-as-a-service-flyway**. Reporting
(Doc Depot) is Java API lambdas plus Python task lambdas. The UIs are the Go + Vue apps **cow-extract-interface** and
**cow-admin-spa**, plus the legacy Python-Lambda + Vue **cow-depotui**. **cow-forensics-dashboard** is a separate FastAPI +
React + DuckDB pilot. **cow-validation-as-a-service** is dead.

## Conventions for implementers
- **New warehouse column/table:** add `redshift/migrations/V<n>__desc.sql` in cow-etl-as-a-service-flyway, using the next number above the highest existing one. Shape it per [[wh-data-vault-conventions]], with md5 hash keys over the business key + `datasource_id` and `ingest_dts`. If an extract MV needs it, also update the MV DDL and `refresh_mvs` ([[wh-stored-procedures]]). Rollout: [[roll-out-redshift-migration]]. A new database needs the `meta-data/*-db-list.txt` entry and an image rebuild.
- **New Glue job:** add the `.py` under `jobs/` **and** register it in `ETLStack.java` ([[etl-infrastructure]]). Layout columns in `jobs/resources` must match DataFrame column names exactly ([[extract-layouts]]).
- **Routes:** Extract Interface `backend/cmd/server/main.go`, with handlers in `internal/handler` and permissions in `internal/authz/actions.go`. Admin SPA `backend/routes/routes.go`. Reporting/workflow/batch routes are wired in their CDK stacks (`AaasReportingStack`, `AaasWorkflowStack`, `BatchApi`); keep the `src/static/api/v1/openapi.json` files in sync (they have drifted).
- **Contracts that change together:** Extract Interface backend models ↔ `frontend/src/types`, and forensics `schemas.py` ↔ `frontend/src/lib/types.ts`.
- **Tests:**
  - cow-etl-as-a-service: `pytest --cov=jobs`; `./gradlew build` for the CDK.
  - Go backends: `go test ./...` (Extract Interface: `make test`).
  - Extract Interface e2e: `npm run e2e`, which runs an offline Moto + mock-PWS stack ([[xi-e2e-suite]]).
  - Java CDK repos: `./gradlew build`.
  - Forensics: `pytest` under `backend/`.
- Environment values come from `env/<env>.json` (CDK) or terragrunt tfvars / SSM ([[deploy-ssm-env-params]]). Never hard-code them.

## Hazards (verify before relying on these; found by reading code)
- **Cross-tenant API access.** The Cognito PWSAuth pool allows self sign-up, and [[lambda-authorizer]] checks only `custom:restApiId`. Verify the pool settings in the console.
- **Unauthenticated tenant management.** `POST /tenants` and `POST /services` on [[platform-tenant-management-api]] require no auth.
- **Production ADAM from nonprd.** ADAM DB secrets are production-replica secrets in both accounts, so nonprd ingest reads production ADAM ([[adam]], [[ingest-adam-to-warehouse]]).
- **Arbitrary Lambda invocation.** Extract Interface `POST /api/workflows/trigger` invokes any Lambda name it is given ([[extract-interface-api]]).
- **Admin SPA SQL injection, reachable by readonly users.** Readonly users can inject SQL via `org-tree` and `test-attempts`, and `POST /api/database/query` runs arbitrary SQL for admins ([[admin-redshift-ops]]). Client-credential logins are always `admin`.
- **Redshift grants to PUBLIC.** Flyway `V2__permissions.sql` grants DML/DDL to PUBLIC ([[redshift-flyway]]).
- **Over-broad GitHub OIDC deploy role.** It trusts any branch of any `cow-*` repo and carries `iam:*` ([[release-container-service]]).
- **Test callback URL in workflow definitions.** `workflowStatusCallbackUrl` is httpbin.org in several places ([[run-extract-order]]).
- **CDK prune of hand-staged layouts.** A CDK BucketDeployment may prune hand-staged files under `instructions/` in [[etl-bucket]].
- **Weekly/Monthly ABBI subscriptions never send.** The ABBI digest matches only Hourly/Daily, but the Extract Interface lets users pick Weekly or Monthly ([[send-abbi-digest]]).

## Status notes
- **Legacy:** DepotUI (replaced by the Extract Interface), STM ingest (its target tables were dropped by V11/V15), one-off Glue jobs, and the `dev` per-schema migration mode.
- **Dead:** Validation as a Service, the VSR and summary-performance MVs, the split-zip EFS volume ([[batch-efs]]) and the never-built data records API ([[data-records-api]]).
- **Planned (code present, never deployed):** the reporting `s3BatchCopy`/`s3Inventory` lambdas ([[reporting-wip-copy-lambdas]]).
- **Permissions:** see `permissions.md`.