# Quarry Explorer — Product Requirements (v1.0)

A self-service business-intelligence service. Signed-in analysts browse a catalog of tables, build aggregate questions (filters, groups, measures, time buckets, pivots), run read-only SQL and save questions into collections. This release exposes the service as an HTTP API over a fixed demo warehouse of orders, customers, products and app users.

**Problem.** Analysts need trustworthy numbers without asking engineering for a query. Every figure must match across the question builder and SQL, totals must cover every row, and nobody may change the warehouse by accident.

## Users
- **Analyst** — Ask questions of the data by grouping, filtering and pivoting, and get complete, correct results.
- **SQL power user** — Run a SELECT query and get the same numbers as the builder, with a safe cap on result size.
- **Viewer** — Read saved questions without being able to change anything.

## Goals
- Totals always include every row that matches, whatever is shown in the result window.
- Results from the question builder and from SQL agree for the same question.
- The warehouse is read-only: nothing a user sends can change data.
- Invalid input fails with a specific, actionable message.

## Sign-in and roles (AUTH)

A cookie session guards every data endpoint.

- **FR-AUTH-01** (Must) Every data endpoint requires a signed-in session; bad credentials are rejected.
  - A request to the table catalog without a session cookie answers 401
  - Signing in with a wrong password answers 401 "Invalid credentials"
  - A correct sign-in answers 200 with the user (for the demo analyst: Dana Data, role Admin) and sets a session cookie
  - The current-user endpoint with the cookie returns that user name
- **FR-AUTH-02** (Should) The Viewer role is read-only: it may list saved questions but any attempt to save one is refused.
  - Signing in as the Viewer returns role Viewer
  - Listing questions answers 200 with the 4 seeded questions
  - Saving a question answers 403 and nothing is stored

## Table catalog (TBL)

The tables available to question, with their columns and the values in them.

- **FR-TBL-01** (Should) The catalog lists four tables with typed columns and row counts.
  - Orders (5,978 rows, 13 columns), customers (420 rows, 6 columns), products (24 rows, 4 columns) and App users (3,200 rows, 6 columns)
  - In orders, date is "date"; units, revenue and cost are "number"; returned is "boolean"; the rest are "text"
- **FR-TBL-02** (Should) Distinct values of a column are returned sorted, for filter pickers; unknown columns are not found.
  - orders.region returns ["APAC","EMEA","LATAM","North America"]
  - orders.channel returns Mobile, Partner, Retail, Web; orders.category returns Hardware, Services, Software, Support
  - users.platform returns ["Android","Web","iOS"]
  - An unknown column answers 404 "No column orders.nope"

## Notebook questions (DAT)

Aggregate questions: filters, dimensions, measures, sort, time buckets and pivots.

- **FR-DAT-01** (Must) Grouping by a dimension with measures and a sort returns one row per group, and totals cover every row.
  - Revenue and order count by region sorted by revenue descending: North America 20,311,610.13 / 2,389; EMEA 14,699,095.82 / 1,733; APAC 12,285,668.66 / 1,318; LATAM 4,251,167.66 / 538
  - Totals: sum(revenue) 51,547,542.27; count 5,978; rows 5,978
  - The groups always add up to the totals
- **FR-DAT-02** (Must) Filters apply before grouping and also to the totals.
  - Region = EMEA by channel by revenue: Web 5,947,018.18 / 697; Mobile 3,839,953.05 / 432; Partner 3,066,579.33 / 366; Retail 1,845,545.26 / 238
  - Totals equal the EMEA row of the unfiltered region question: 14,699,095.82 and 1,733
  - Filtering returned = true and counting gives 181
- **FR-DAT-03** (Should) A derived month dimension buckets dated rows by calendar month.
  - Order count by month gives 12 rows from 2025-10 (330) to 2026-09 (606), peaking at 2025-12 (577) and 2026-06 (719)
  - The monthly counts sum to 5,978
- **FR-DAT-04** (Must) A two-dimension pivot returns every combination of the two dimensions, and the cells reconcile to the totals.
  - Region by channel with sum(units) returns 16 rows (4 regions x 4 channels) and total 16
  - sum(units) total is 15,378 and equals the sum of all 16 cells
  - Web cells: APAC 1,410; EMEA 1,734; LATAM 487; North America 2,510
- **FR-DAT-05** (Should) A calculated margin is available as a field and other tables aggregate the same way.
  - By category, sum(revenue) and avg(margin): Hardware 11,240,884.75 / 2,140.84; Services 16,524,733.30 / 5,953.63; Software 14,696,417.53 / 5,254.29; Support 9,085,506.69 / 6,859.95
  - App users by platform: Android 1,082; Web 888; iOS 1,230 (total 3,200)
- **FR-DAT-06** (Could) With no dimensions or measures a question returns raw rows, honouring a limit.
  - Products with limit 3 returns columns id, name, category, listPrice and three rows starting PRD-101 "Edge Gateway X2" Hardware 1890
  - totals.rows is 24, the full table size
- **FR-DAT-07** (Should) Unknown tables, columns and measures are rejected with 422 and a specific message.
  - Unknown table: 422 Unknown table "nope"
  - A column not on the table: 422 Unknown column "month" on customers (it has no date column)
  - An unsupported measure: 422 Unknown measure, listing count, sum, avg, min, max and distinct

## SQL editor (SQL)

Read-only SQL over the warehouse with a capped result size.

- **FR-SQL-01** (Must) A SELECT returns its columns and rows, agreeing with the question builder.
  - Orders per region, highest first: North America 2389, EMEA 1733, APAC 1318, LATAM 538, truncated false
  - Top three customers by revenue: Tyrell Dynamics 781266.45, Massive Networks 718734.84, Wonka Holdings 702141.58; a trailing semicolon is accepted
  - MIN and MAX of the order date are 2025-10-01 and 2026-09-30
- **FR-SQL-02** (Must) Results are capped by the requested limit and never exceed 2,000 rows; capped results are flagged as truncated.
  - limit 10 returns 10 rows, total 5978 and truncated true
  - No limit returns 2000 rows, total 5978 and truncated true
  - A limit of 50000 still returns 2000 rows
- **FR-SQL-03** (Must) Only SELECT statements run; writes and broken SQL are rejected and data never changes.
  - DELETE: 422 read_only "Only SELECT statements are allowed"
  - An unknown column: 400 sql_error "no such column: nope"
  - Blank SQL: 422 "sql is required"
  - COUNT(*) on orders afterwards is still 5978

## Saved questions and collections (QST)

Questions are saved into collections, found again and re-run.

- **FR-QST-01** (Could) Saved questions live in collections and can be searched and filtered by collection.
  - Collections: COL-0001 Sales, COL-0002 Product, COL-0003 Our analytics
  - 4 seeded questions: Q-0001 Revenue by region (bar), Q-0002 Monthly orders (line), Q-0003 Signups by platform (pie), Q-0004 Top customers (SQL) (table, with sql set and query null)
  - Searching "region" returns only Q-0001; the Sales collection returns 2
- **FR-QST-02** (Must) A saved question can be fetched and its query re-run to give the same result.
  - Saving "EMEA by channel" answers 201 with a new id Q-NNNN
  - Re-running its query gives Web 697, Mobile 432, Partner 366, Retail 238
  - Saving without a query answers 422 with missing ["query"]

## Non-functional requirements

- **NFR-01** (Must) The warehouse is deterministic and read-only: the same question always returns the same numbers and no endpoint modifies table data.

## Glossary and rules

- **Question** — A saved query (builder form or SQL) with a display type.
- **Truncated** — True when the result had more rows than were returned.

## Out of scope

- Question builder and chart user interface
- Dashboards
- Writes to the warehouse