{"ns":"explorer","title":"Quarry Explorer","version":"1.0","summary":"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.","personas":[{"name":"Analyst","need":"Ask questions of the data by grouping, filtering and pivoting, and get complete, correct results."},{"name":"SQL power user","need":"Run a SELECT query and get the same numbers as the builder, with a safe cap on result size."},{"name":"Viewer","need":"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."],"features":[{"id":"AUTH","title":"Sign-in and roles","summary":"A cookie session guards every data endpoint.","reqs":[{"id":"FR-AUTH-01","weight":"Must","text":"Every data endpoint requires a signed-in session; bad credentials are rejected.","accept":["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"]},{"id":"FR-AUTH-02","weight":"Should","text":"The Viewer role is read-only: it may list saved questions but any attempt to save one is refused.","accept":["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"]}]},{"id":"TBL","title":"Table catalog","summary":"The tables available to question, with their columns and the values in them.","reqs":[{"id":"FR-TBL-01","weight":"Should","text":"The catalog lists four tables with typed columns and row counts.","accept":["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\""]},{"id":"FR-TBL-02","weight":"Should","text":"Distinct values of a column are returned sorted, for filter pickers; unknown columns are not found.","accept":["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\""]}]},{"id":"DAT","title":"Notebook questions","summary":"Aggregate questions: filters, dimensions, measures, sort, time buckets and pivots.","reqs":[{"id":"FR-DAT-01","weight":"Must","text":"Grouping by a dimension with measures and a sort returns one row per group, and totals cover every row.","accept":["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"]},{"id":"FR-DAT-02","weight":"Must","text":"Filters apply before grouping and also to the totals.","accept":["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"]},{"id":"FR-DAT-03","weight":"Should","text":"A derived month dimension buckets dated rows by calendar month.","accept":["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"]},{"id":"FR-DAT-04","weight":"Must","text":"A two-dimension pivot returns every combination of the two dimensions, and the cells reconcile to the totals.","accept":["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"]},{"id":"FR-DAT-05","weight":"Should","text":"A calculated margin is available as a field and other tables aggregate the same way.","accept":["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)"]},{"id":"FR-DAT-06","weight":"Could","text":"With no dimensions or measures a question returns raw rows, honouring a limit.","accept":["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"]},{"id":"FR-DAT-07","weight":"Should","text":"Unknown tables, columns and measures are rejected with 422 and a specific message.","accept":["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"]}]},{"id":"SQL","title":"SQL editor","summary":"Read-only SQL over the warehouse with a capped result size.","reqs":[{"id":"FR-SQL-01","weight":"Must","text":"A SELECT returns its columns and rows, agreeing with the question builder.","accept":["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"]},{"id":"FR-SQL-02","weight":"Must","text":"Results are capped by the requested limit and never exceed 2,000 rows; capped results are flagged as truncated.","accept":["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"]},{"id":"FR-SQL-03","weight":"Must","text":"Only SELECT statements run; writes and broken SQL are rejected and data never changes.","accept":["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"]}]},{"id":"QST","title":"Saved questions and collections","summary":"Questions are saved into collections, found again and re-run.","reqs":[{"id":"FR-QST-01","weight":"Could","text":"Saved questions live in collections and can be searched and filtered by collection.","accept":["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"]},{"id":"FR-QST-02","weight":"Must","text":"A saved question can be fetched and its query re-run to give the same result.","accept":["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\"]"]}]}],"nfr":[{"id":"NFR-01","weight":"Must","text":"The warehouse is deterministic and read-only: the same question always returns the same numbers and no endpoint modifies table data."}],"glossary":[{"term":"Question","meaning":"A saved query (builder form or SQL) with a display type."},{"term":"Truncated","meaning":"True when the result had more rows than were returned."}],"outOfScope":["Question builder and chart user interface","Dashboards","Writes to the warehouse"]}