Superapp / Product requirements / explorer

Quarry Explorer — product requirements

Version 1.05 features17 requirements9 Must Open appJSONMarkdown

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

Goals

Out of scope

Contents

Sign-in and roles

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

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

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

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

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

QuestionA saved query (builder form or SQL) with a display type.
TruncatedTrue when the result had more rows than were returned.