Full-Stack Data Table: Raw-SQL API, React Rendering, Row Flags and Pagination

You are given a small full-stack starter project: a SQL database that is already created and seeded, a minimal backend (you choose a Python backend or a Next.js backend), and a React frontend. Build a feature in three steps. The backend must query the database with raw SQL rather than an ORM; the queries needed are basic `SELECT` statements. None of the steps is hard, but all three must be finished in the time box, so fluency with the stack matters. The table and the condition were not reported; the schema below is an illustrative assumption that makes the parts concrete. ```text inventory_items (illustrative) id INTEGER PRIMARY KEY name TEXT NOT NULL category TEXT quantity INTEGER NOT NULL reorder_level INTEGER NOT NULL updated_at TEXT -- ISO-8601 timestamp ``` ### Clarifying Questions - Which database engine does the starter use, and how does the backend get a connection? - Which columns should the table show, in what order, and how should empty values be displayed? - What exactly is the condition for marking a row, and should it be evaluated in SQL, in the backend, or in the browser? - Should pagination happen on the server or in the browser, with what page size, and does the UI need the total number of rows or pages? - What is the default sort order? ### Part 1 — API and table Implement a backend endpoint that reads the rows from the database with raw SQL and returns them as JSON. Then fetch that endpoint from the React frontend and render the rows in a table. ```hint Contract first Agree on the JSON shape the endpoint returns before writing either side, and decide what the table shows while the request is in flight and after it fails. ``` #### What This Part Should Cover - A read endpoint with an explicit column list, a deterministic order and a clear JSON shape - A React component that fetches once, renders rows with stable keys, and handles loading, empty and error states - Connection handling and error responses on the server ### Part 2 — Mark rows that meet a condition For each row of the fetched data, check a condition and show a marker on every table row that meets it. With the illustrative schema, mark items whose `quantity` is below their `reorder_level` with a "Low stock" badge. ```hint One place for the rule Decide which layer owns the condition, so that the rule is defined once and stays correct when the data arrives one page at a time. ``` #### Clarifying Questions for this Part - Is the rule fixed, or will its threshold change, for example per category? - Does the marker only need to be displayed, or will users also filter or sort by it? #### What This Part Should Cover - Where the condition is evaluated, and why that layer - Boundary values and missing values in the compared columns - A marker that is readable without relying on colour alone ### Part 3 — Pagination Add pagination, so that the table shows one page of rows at a time with controls to move between pages. ```hint Stable pages Think about what guarantees that each row appears on exactly one page, and what the client needs from the server to know that it has reached the last page. ``` #### What This Part Should Cover - Server-side paging with a deterministic `ORDER BY` and validated page parameters - A response that tells the client the total or whether more pages exist - Frontend page state, disabled controls at either end, and responses that arrive out of order ### What a Strong Answer Covers - Settles the schema, the marking rule and the pagination contract with the interviewer before coding - Parameterized raw SQL, never queries built by string concatenation of request values - One API contract shared by the backend and the frontend - Marking logic defined once and tested at its boundary - Pagination with a stable order, validated inputs and correct last-page behavior - Steady progress through all three parts within the time box, with working code after each step ### Follow-up Questions - The table grows to millions of rows and users jump deep into the list. What breaks with `OFFSET`, and what would you change? - Rows are inserted while a user is paging. How can a row be shown twice or skipped, and how do you prevent it? - Users want to see only the marked rows. Where does that filter go, and how does it interact with the page count? - How would you test the endpoint and the table without the real database?