# Alder Table Bistro

A fictional neighborhood bistro with eight weeks of comparable supplier purchases, recipe planning, stock balances, menu sales and guest feedback.

All entities and transactions are fictional. Snapshot: 2026-09-08.

## Start with the linked model

1. Open operations.xlsx and review the Summary sheet. Source tables contain stable primary keys and declared references.
2. Change source values in the workbook to explore the calculated totals and editable chart.
3. Compare output values with expected-results.json. The JSON Schema checks types; primary-key uniqueness and joins also require the listed semantic checks.
4. Use invalid-records.json only in a local validation harness. Its three cases exercise duplicate keys, wrong types and an orphan reference.

## Included tables

| Table | Records | Primary key |
| --- | ---: | --- |
| purchases | 96 | purchase_id |
| suppliers | 2 | supplier_id |
| ingredients | 6 | ingredient_id |
| locations | 1 | location_id |
| menu | 4 | menu_item_id |
| recipes | 12 | recipe_line_id |
| sales | 56 | sales_id |
| inventory | 6 | count_id |
| reviews | 18 | review_id |
| modifiers | 8 | modifier_id |
| sales_modifiers | 12 | applied_id |
| refunds | 8 | refund_id |
| guests | 12 | guest_id |
| reservations | 12 | reservation_id |
| staff | 8 | employee_id |
| staffing_shifts | 56 | shift_id |
| inventory_movements | 48 | movement_id |
| menu_price_changes | 4 | price_change_id |

## Expected totals

- Supplier spend: 11,652.45 CAD. Sum purchases.total_price over the included records.
- Net menu sales: 15,780.80 CAD. Sum sales.net_sales over the included records.
- Menu items sold: 1,092.00 items. Sum sales.quantity over the included records.
- Guest reviews: 18.00 records. Count unique review_id values in reviews.

## Novus Restaurant imports

The purchase CSV, TSV and first worksheet of operations.xlsx share the supported purchase columns. Select one representation for a fresh organization. Purchase and review imports require a signed-in non-viewer account and confirmation that you hold the data rights.

Import purchases.csv first: 96 accepted rows, two suppliers and six items. Each supplier/item history contains eight dated observations. An equivalent TSV or XLSX replay is expected to add zero rows. Upload reviews.csv through the separate review-import action: 18 accepted reviews.

invalid-purchases.csv and invalid-reviews.csv contain four rejected rows each. An all-invalid purchase run currently returns HTTP 201 with a failed run status. Missing/non-numeric numeric fields have permissive legacy coercion in the current importer; these tests use explicit unsupported dates, missing required names and out-of-range amounts/ratings.

Menu, recipes, inventory, sales, locations and rendered charts have no current Novus Restaurant file-upload operation. Use those tables for manual setup, analytics testing or a future adapter. Location IDs are not preserved by the current purchase importer.

## Scope and assumptions

- Only the separate purchase and review files match Novus Restaurant file-upload contracts. Menu, recipe, inventory, sales and location records are planning examples; the current app has no file importer for those tables.
- Purchase uploads aggregate the organization and do not preserve location or purchase IDs. Replaying alternate formats must not create new purchase rows.
- Recipe quantities are illustrative menu-planning inputs, not food-safety, nutrition or production instructions.
- Recipes cover the purchased ingredient subset only; water, salt and minor seasonings are excluded. Used stock includes unlisted menu production during the eight-week purchase period; the sales table is the final fourteen days only.
- Supplier comparison requires matching item names, units and currency. Pack, grade and contract equivalence still require operator review.
- Modifiers and refunds are separate adjustments to base menu sales. Staffing records show base pay only. Reservations for counter-service formats model advance group/table or collection bookings, not an assertion that every format uses seated dining.