# Read-only nutrition backend and mapping-workflow audit

Use this reference for source-only architecture audits before implementing a nutrition ingestion, classification, or manual mapping workflow in a sensitive health repository.

## Audit boundary

1. Prove the absolute repository path, branch, commit, and clean/dirty state first.
2. Do not open productive databases, exports, reports, secret files, or row contents. Derive schema from committed DDL and synthetic fixtures only.
3. Report table/column names, constraints, indexes, count categories, route contracts, and file paths. Never reproduce real health values, item/product names, notes, raw JSON, credentials, or source filenames.
4. End with unchanged-tree evidence. A read-only audit must not create plans, reports, caches, fixtures, or temporary files inside the repository.

## Schema inventory method

- Treat committed canonical DDL as the schema authority for the audit, but distinguish a schema snapshot from an executable migration.
- Materialize DDL only in an in-memory SQLite database when exact table/column/index counts are needed; inspect it with `sqlite_master` and `PRAGMA table_info`.
- Group nutrition structures rather than presenting an undifferentiated table list:
  - imported observations and nutrient rows;
  - item/meal/day aggregates;
  - mapping rules, aliases, item classifications, and review queue;
  - personal tolerance or experiment records;
  - derived recommendation/correlation tables;
  - legacy diary/state compatibility tables.
- Trace every table to readers, writers, migrations, and tests. A table present in DDL but with no writer is an architectural finding, not an implemented workflow.
- Check exact FK, uniqueness, status checks, indexes, timestamps, and whether aggregate defaults can collapse unknown into zero.

## End-to-end mapping trace

Trace all four boundaries independently:

1. **Importer:** Does an unknown classification create or update a bounded review item? Does it require an explicit database target? Does it assume schema exists? Are multiple importers using incompatible normalization or unit conventions?
2. **Read API:** Is there a dedicated bounded endpoint, or only a truncated static bundle? Is the displayed open count a true SQL count or merely the current page length? Are filtering, pagination, response size, authentication, no-store, and field allowlists enforced?
3. **Action queue:** Inventory exact POST routes and action versions. Confirm whether nutrition decisions are supported or the worker only understands another action class.
4. **Worker/mutation leaf:** Require independent payload validation, explicit DB selection, optimistic concurrency, durable idempotency, immutable audit, transactional queue/rule updates, affected-row reclassification, bounded aggregate recomputation, and truthful queued-versus-applied receipts.

## Minimal additive design pattern

Prefer extending an existing private queue/one-shot worker boundary over adding a second write service.

- Add one idempotent migration and update canonical DDL.
- Reuse the current review queue when its identity/status fields are adequate.
- Add a small immutable decision-audit table with an action idempotency key, review-row reference, decision enum, optional canonical target, expected queue revision/timestamp, bounded reason, and creation timestamp.
- The browser may select an existing canonical rule but must never submit the resulting score/classification as authoritative; the worker loads it from the DB.
- For ambiguous or composite items, support defer/ignore rather than classifying from display name alone. Ingredient decomposition is a separate reviewed scope.
- Add bounded read endpoints for queue pages and canonical mapping options. Use SQL filtering/keyset pagination before limits and return a separate true total.
- Add one versioned nutrition decision action to the existing queue. Revalidate in the worker and apply in one transaction.
- Recompute only affected item, meal, and day aggregates. Preserve unknown until every required mapping is complete.

## Required tests

- Idempotent migration, row preservation, FK/integrity checks.
- Importer queue upsert, occurrence bounds, unknown preservation, and mandatory explicit DB targeting.
- Read API allowlists, keyset pagination, true count, response limits, authentication, redaction, and read-only connection.
- POST exact shape, single values, CSRF/Origin, replay, stale revision, queue full, and receipt semantics.
- Worker owner/mode/symlink/size/duplicate-JSON checks, independent validation, idempotent replay, transaction rollback, and partial failure after commit but before regeneration.
- Mapping decision reclassification and affected aggregate recomputation with synthetic generic placeholders only.
- Missing-versus-explicit-zero regressions at item, meal, day, API, bundle, and correlation boundaries.
- Final clean-tree verification.

## Common audit findings

- Canonical schema exists but no deployable nutrition migration owns it.
- Review queue is displayed but never populated or mutated.
- Open count is derived from a truncated display page.
- Importers use fixed production paths instead of a mandatory `--db` target.
- Legacy and current importers disagree on scaling or normalization.
- Mapping decisions lack idempotency, audit, and concurrency control.
- A legacy renderer creates tables or writes reports while a newer architecture claims read-only behavior.