A shop does not need an AI model to answer every question. “Which items should I reorder?” is a precise question about quantities. SQL is a good fit because you can describe the rule directly and inspect the rows that satisfy it.

This walkthrough builds a small stock-check table in OriginChainDB. You will add three fictional products, find the ones below their reorder point, record a delivery, and check that the report changes for the right reason. Use a separate demo namespace so these examples cannot be mistaken for your live inventory.

1. Start with the business rule

For this example, reorder an item when its quantity is lower than its reorder point. Both numbers count individual units. A box of twelve and one individual item must not share the same unit label; otherwise an apparently correct query can lead to a wrong purchase.

Open your running instance in the OriginChainDB console, choose the SQL workbench, and use an account permitted to create and update a demo table. Use a fresh namespace name if you have already run this example. Keep application credentials on your server, outside browser bundles and screenshots.

2. Create a small table

Run the statements separately so you can inspect each result:

CREATE SCHEMA stock_demo_20261006;

CREATE TABLE stock_demo_20261006.products (
  id TEXT PRIMARY KEY,
  name TEXT,
  quantity BIGINT,
  reorder_point BIGINT
);

The primary key identifies the product even if its display name changes. The two quantity columns are integers because this example counts whole items. A business selling material by weight needs an explicit measurement unit and an appropriate numeric representation instead.

INSERT INTO stock_demo_20261006.products
  (id, name, quantity, reorder_point)
VALUES
  ('p1', 'Notebook', 4, 10),
  ('p2', 'Blue pen', 18, 12),
  ('p3', 'Envelope pack', 2, 6);

Check the input before trusting a report:

SELECT id, name, quantity, reorder_point
FROM stock_demo_20261006.products
ORDER BY id;

You should see three products. This verification catches an incomplete insert or an accidentally selected namespace before either becomes a reporting problem.

3. Ask the operational question

SELECT id, name, quantity, reorder_point
FROM stock_demo_20261006.products
WHERE quantity < reorder_point
ORDER BY id;

The expected matches are Notebook and Envelope pack. Blue pen has eighteen units against a reorder point of twelve, so it should be absent. This query identifies a shortfall against the chosen rule; it does not calculate an economically optimal order quantity or account for supplier lead time.

4. Record a delivery and check again

Suppose six notebooks arrive. In this single-user demonstration, update the notebook quantity to ten:

UPDATE stock_demo_20261006.products
SET quantity = 10
WHERE id = 'p1';

Run the restock query again. Only Envelope pack should remain. Ten equals the threshold and our rule uses “less than,” not “less than or equal.” That boundary is a business decision; make it explicit before someone uses the report to place orders.

5. Turn the demonstration into a dependable routine

Save the read query in the workbench with a descriptive name. In an application, validate incoming quantities, establish who can make corrections, and record the source and time of each stock change. A live multi-user inventory needs a concurrency-safe update design; repeatedly replacing an absolute quantity from stale client data can overwrite another person’s change.

Test an item exactly at its threshold, an empty table, and an invalid quantity. Decide whether incomplete records should be rejected or placed in a separate data-quality report. Do not let a missing value quietly become “no reorder required.”

OriginChainDB’s SQL workflow gives you an inspectable starting point. Add search or a conversational interface only after the underlying numbers and rules are reliable. A useful daily dashboard begins with a question that a human can verify.

Read the SQL documentation and schema guide for the supported query and validation options.

Work through the boundary cases before building a dashboard

Use the first query as a test oracle. Initially, the expected ID set is {p1, p3}. After recording the delivery, it is {p3}. Compare sets by product ID rather than relying on the order in which a screen happens to display rows. A changed label should not change which product is being counted.

The boundary deserves its own experiment. In the disposable fixture, temporarily set p1 to nine, then ten, then eleven. At nine the item should appear; at ten and eleven it should not. Restore ten when finished. This three-value test is more useful than running the same happy-path query repeatedly because it exposes an accidental change from < to <=.

Add a separate data-quality query instead of hiding incomplete rows:

SELECT id, name, quantity, reorder_point
FROM stock_demo_20261006.products
WHERE quantity IS NULL OR reorder_point IS NULL
   OR quantity < 0 OR reorder_point < 0
ORDER BY id;

On the original three rows the result should be empty. A NULL comparison does not evaluate to true simply because the value is unknown. If a product has no threshold, the regular reorder report can omit it; the quality report makes that omission visible. In a live application, agree whether a negative quantity represents invalid input or a legitimate back-order state. This example deliberately treats it as a problem to investigate.

Separate stock movement from a stock snapshot

The update above is deliberately a single-user exercise. An absolute quantity is a snapshot, while a receipt, sale or correction is a movement. Suppose two staff members both read quantity four. One records a delivery of six; another records a sale of one. If they independently replace the value using stale reads, the last write can erase the other person’s change.

A production design needs a documented concurrency strategy and a recoverable record of each movement. For example, a movement record can carry a stable event ID, product ID, signed quantity, event type, recorded time and external receipt reference. That is a proposed application contract, not a claim that this tutorial configures an inventory ledger automatically. Choose a supported transaction or conditional-write design and verify it for the exact client route in use.

Decide what a retry means before connecting a scanner or supplier feed. Replaying a request that means “set quantity to ten” differs from replaying “add six.” An uncertain network response is not permission to increment again. Keep a record that allows the application to determine whether the intended movement was already accepted.

Make the report useful to the person ordering stock

Show the product ID, name, current quantity, threshold and measurement unit together. Show when the underlying stock was last reconciled. A report from yesterday may be internally correct and still be unsuitable for today’s purchase decision. Avoid presenting a reorder flag as an optimal purchase quantity: this fixture does not model supplier minimums, inbound deliveries, lead times or demand forecasts.

Before release, test an empty inventory, a renamed product, one missing threshold, and a failed correction. The UI should distinguish “nothing needs reordering,” “there are no products,” and “the report could not be loaded.” Those states lead to different actions. The completed workflow is a reliable operational question, a checkable SQL predicate, a visible quality report and a documented update process. SQL transactions and their supported boundaries are the next reference to review before adding concurrent writers.