Hearthstone Coffee Roasters =========================== Database schema: coffeeRoastery Platform: mariadb Tables: 9 Total rows: 5,852 ABOUT THE BUSINESS ------------------ Hearthstone Coffee Roasters is a specialty coffee roastery that buys unroasted green beans directly from farms and cooperatives around the world, roasts them in small batches at its facility, and sells the finished coffee under its own branded product line. Its customers fall into two broad groups: wholesale cafe accounts that order in volume, and individual consumers who either subscribe to recurring deliveries or place one-time retail purchases. The business prides itself on traceability — every bag of coffee can be traced back to the specific farm and lot it came from. The company manages a rotating catalog of roughly 260 active and historical product SKUs across different bag sizes, grind options, and roast levels. HOW THE BUSINESS OPERATES ------------------------- The journey begins when Hearthstone purchases a lot of green beans from a farm; that lot is logged with its weight, cost, and quality score and held in inventory. When the roasting team is ready, they pull a measured quantity from a lot and run a roast session, recording how much green coffee went in and how much finished roasted coffee came out. The roasted output from one or more sessions is then assigned to specific sellable products — a 250-gram bag of medium-roast whole bean, for example — and those bags are counted and cleared through quality control before being available for sale. Wholesale and retail customers then place orders that reference those products, and subscribers receive the same product on a repeating schedule without placing a new order each time. WHAT EACH TABLE HOLDS --------------------- farms (12 rows) One row per coffee-growing farm or cooperative that Hearthstone has a sourcing relationship with. These are the origin partners whose details appear on packaging and in marketing descriptions — country, growing region, altitude, and how they process their harvested cherries. greenBeans (260 rows) One row per discrete lot of unroasted beans purchased from a farm. A single farm may supply many lots over time, each with its own lot code, purchase date, bean variety, and professional quality cup score. The lot tracks how many kilograms were purchased and how many remain available for roasting. roastBatches (1,200 rows) One row per individual roast session run at the facility. Each session draws from exactly one green-bean lot, records how much green coffee was loaded and how much roasted coffee was produced, and notes the roast level and duration. The roaster can also attach tasting or process notes. coffeeProducts (260 rows) One row per sellable product SKU in Hearthstone's catalog. A SKU defines a specific combination of product name, bag size, grind option, and roast level, along with the retail and wholesale prices. The isActive flag distinguishes products currently offered from those that have been retired. batchProducts (1,200 rows) One row per pairing of a roast batch with a coffee product it yielded. Because a single roast session can be packaged into multiple bag formats, and because a given product SKU may be filled from several batches over time, this table records exactly how many bags of a particular product a particular batch produced and whether those bags passed quality inspection. customers (260 rows) One row per registered customer, whether that is a cafe buying wholesale, an individual with a subscription, a one-time retail buyer, or a corporate office account. The customerType field distinguishes these segments. wholesaleOrders (1,200 rows) One row per order placed with Hearthstone. This is the header record — it captures who ordered, when, how the shipment will travel, any discount applied, and where the order stands in the fulfillment process. The individual products within the order live in orderLineItems. orderLineItems (1,200 rows) One row per product line within an order. If a cafe orders three different SKUs in one purchase, that order has three line-item rows, each recording which product, how many bags, and the unit price agreed at the time of sale. subscriptions (260 rows) One row per subscription agreement between Hearthstone and a customer. A subscription locks in a specific coffee product, a delivery frequency, the number of bags per shipment, and the price per bag. It has its own lifecycle — it can be active, paused, pending payment, or cancelled — and always carries the date of the next scheduled shipment. HOW THE TABLES RELATE --------------------- Each green-bean lot is tied to one farm, so every roast batch — which draws from one lot — can be traced all the way back to its origin farm and harvest. Roast batches are then linked to the sellable product catalog through the batchProducts table, capturing exactly which bag formats a session produced and whether they cleared quality control. When customers place orders, each order header belongs to one customer and fans out into one or more line items, each referencing a specific product SKU. Subscriptions work similarly — each subscription belongs to one customer and is pinned to one product SKU, automating the delivery of that product on the agreed schedule without requiring a new order each time. - Each greenBeans row belongs to one farms, through greenBeans.farmId → farms.farmId. - Each roastBatches row belongs to one greenBeans, through roastBatches.greenBeanId → greenBeans.greenBeanId. - batchProducts resolves a many-to-many relationship: each row pairs one roastBatches with one other party via batchProducts.roastBatchId. - batchProducts resolves a many-to-many relationship: each row pairs one coffeeProducts with one other party via batchProducts.coffeeProductId. - Each wholesaleOrders row belongs to one customers, through wholesaleOrders.customerId → customers.customerId. - Each orderLineItems row belongs to one wholesaleOrders, through orderLineItems.wholesaleOrderId → wholesaleOrders.wholesaleOrderId. - Each orderLineItems row belongs to one coffeeProducts, through orderLineItems.coffeeProductId → coffeeProducts.coffeeProductId. - Each subscriptions row belongs to one customers, through subscriptions.customerId → customers.customerId. - Each subscriptions row belongs to one coffeeProducts, through subscriptions.coffeeProductId → coffeeProducts.coffeeProductId. DATA DICTIONARY --------------- farms farmId INT e.g. 1 Surrogate primary key for the farm. farmName VARCHAR(120) e.g. Lakebound Manufacturing Name of the farm or cooperative. country VARCHAR(60) e.g. United States Country where the farm is located. region VARCHAR(60) e.g. Oak Park Sub-national region or province of the farm. altitude INT e.g. 1571 The elevation in meters at which the farm's coffee is grown. Higher altitudes are associated with slower bean development and more complex flavors, and this figure often appears in product descriptions. processingMethod VARCHAR(60) e.g. Natural How the farm removes the fruit from the coffee cherry after harvest. The method — Washed, Natural, Honey, Wet-Hulled, or Anaerobic — significantly influences the flavor profile of the beans and is highlighted in Hearthstone's sourcing notes. certifications VARCHAR(60) e.g. Organic & Fair Trade Any third-party ethical or environmental certifications the farm holds, such as Organic or Fair Trade. These are used in marketing and influence which customer segments Hearthstone targets with a given origin. contactEmail VARCHAR(120) e.g. tessa.novak8@shop.example.net Email address for the farm's export contact, if known. greenBeans greenBeanId INT e.g. 1 Surrogate primary key for the green bean lot. farmId INT e.g. 4 Farm this lot was purchased from. lotCode VARCHAR(30) e.g. GB-HE-9601 A unique identifier the farm or Hearthstone assigns to a specific harvest batch. It allows the team to distinguish multiple purchases from the same farm and to trace any finished product back to its exact harvest. variety VARCHAR(60) e.g. Bourbon The botanical variety of the coffee plant, such as Gesha or Bourbon. Different varieties have distinct flavor tendencies and command different price points. purchaseDate DATE e.g. 2025-04-21 Date Hearthstone received and accepted the lot. weightKg DECIMAL(12,1) e.g. 743.7 The total weight of green beans in this lot at the time of purchase. remainingKg DECIMAL(12,1) e.g. 1201.2 How many kilograms of this lot have not yet been used in a roast session. This figure decreases each time a roast batch draws from the lot. costPerKg DECIMAL(12,2) e.g. 15.05 What Hearthstone paid per kilogram for this lot, used to calculate the raw material cost of any batch roasted from it. cupsScore DECIMAL(12,1) e.g. 80.6 A professional sensory quality score, typically on a 100-point scale, assigned after the green beans were evaluated by a trained taster. Higher scores indicate more desirable and complex flavor potential. roastBatches roastBatchId INT e.g. 1 Surrogate primary key for the roast batch. greenBeanId INT e.g. 25 Green bean lot consumed in this batch. batchCode VARCHAR(30) e.g. RB-5319-PH A unique reference code assigned to each roast session, used to label finished bags and maintain traceability from product back to roast event. roastDate DATETIME e.g. 2022-09-01 16:05:00 The date and time the roast session was run. greenWeightKg DECIMAL(12,2) e.g. 26.74 How many kilograms of green beans were loaded into the roaster for this session. This quantity is deducted from the corresponding lot's remainingKg. roastedWeightKg DECIMAL(12,2) e.g. 13.51 The weight of finished roasted coffee that came out of the roaster. Roasting always reduces weight through moisture loss, so this figure is always less than greenWeightKg. roastLevel VARCHAR(60) e.g. Medium-Dark Roast degree achieved, as assessed by the roaster. roastDurationMin DECIMAL(12,1) e.g. 12.3 How long the beans were in the roaster, in minutes. Duration interacts with temperature to produce the final roast level and flavor development. roasterNotes VARCHAR(255) e.g. Handled through the service d… Free-text observations the roasting technician recorded about the session — flavor impressions, process adjustments, or anything notable about the batch. coffeeProducts coffeeProductId INT e.g. 1 Surrogate primary key for the product SKU. productName VARCHAR(120) e.g. Clearance package Marketing name of the product, e.g. 'Yirgacheffe Sunrise'. sku VARCHAR(30) e.g. CP-EERN-87 A short alphanumeric code that uniquely identifies this product in Hearthstone's catalog and on invoices and shipping labels. bagSizeGrams VARCHAR(60) e.g. 1000 The net weight of coffee in each bag, offered in 150, 250, 500, or 1,000-gram sizes. grindOption VARCHAR(60) e.g. Fine Whether the beans are sold whole or pre-ground, and if ground, to what coarseness. This is set at the product level, so a single roast profile may appear as multiple SKUs for different grind preferences. roastLevel VARCHAR(60) e.g. Light Target roast level for this product. retailPrice DECIMAL(12,2) e.g. 56.96 The per-bag price charged to individual retail buyers. wholesalePrice DECIMAL(12,2) e.g. 19.27 The per-bag price charged to cafe or other wholesale accounts, which is lower than the retail price to reflect volume purchasing. isActive TINYINT(1) e.g. 0 Whether this product is currently being sold. Inactive products remain in the catalog for historical reporting but are not available for new orders. batchProducts batchProductId INT e.g. 1 Surrogate primary key for this batch-to-product assignment. roastBatchId INT e.g. 20 The roast batch that produced this product allocation. coffeeProductId INT e.g. 63 The product SKU fulfilled by this batch. bagsProduced INT e.g. 431 The number of bags of this product that were filled from this particular roast batch. qualityPassedQc TINYINT(1) e.g. 1 Whether the bags produced in this batch-product pairing passed Hearthstone's quality inspection and are cleared for sale. Batches that fail QC are not shipped to customers. customers customerId INT e.g. 1 Surrogate primary key for the customer. firstName VARCHAR(50) e.g. Bodhi Customer's given name. lastName VARCHAR(50) e.g. Ishikawa Customer's family name. email VARCHAR(120) e.g. bodhi.ishikawa1@example.com Primary email address used for invoices and communications. phone VARCHAR(20) e.g. (584) 559-4612 Customer's contact phone number. customerType VARCHAR(60) e.g. One-Time Retail The business relationship category the customer falls into: a Wholesale Cafe buying in volume, a Retail Subscriber on a recurring plan, a One-Time Retail buyer, or a Corporate Office account. city VARCHAR(60) e.g. Marquette City of the customer's primary delivery address. state VARCHAR(2) e.g. MO State or province of the customer's primary delivery address. joinDate DATE e.g. 2020-12-20 The date this customer was first registered in Hearthstone's system. wholesaleOrders wholesaleOrderId INT e.g. 1 Surrogate primary key for the order. customerId INT e.g. 4 Customer who placed the order. orderDate DATE e.g. 2021-03-31 Calendar date the order was submitted. status VARCHAR(60) e.g. Delivered Where this order stands in the fulfillment pipeline, from Pending through Confirmed, Roasting, Packed, Shipped, and finally Delivered. shippingMethod VARCHAR(60) e.g. Standard Ground How Hearthstone is sending the order — options range from Standard Ground to Overnight or Freight for large wholesale shipments, plus Local Pickup for nearby accounts. shippingCost DECIMAL(12,2) e.g. 30.71 The freight charge billed to the customer for this order. discountPercent DECIMAL(5,2) e.g. 0.21 Any percentage discount applied to this order at the header level, typically negotiated for large accounts or promotional purposes. notes VARCHAR(255) e.g. (null) Any free-text instructions or comments associated with this order, such as delivery access notes or special packaging requests. orderLineItems orderLineItemId INT e.g. 1 Surrogate primary key for the line item. wholesaleOrderId INT e.g. 62 Parent order this line belongs to. coffeeProductId INT e.g. 10 Product SKU ordered on this line. quantity INT e.g. 6 The number of bags of this product included in this line of the order. unitPrice DECIMAL(12,2) e.g. 48.15 The per-bag price agreed for this line at the time the order was placed. This may differ from the product's standard wholesale or retail price if a negotiated rate or promotion applied. lineTotal DECIMAL(12,2) e.g. 288.9 The total value of this line, calculated as quantity multiplied by unitPrice. subscriptions subscriptionId INT e.g. 1 Surrogate primary key for the subscription. customerId INT e.g. 106 Customer who holds this subscription. coffeeProductId INT e.g. 4 Default product SKU shipped on each renewal cycle. frequency VARCHAR(60) e.g. Every 6 Weeks How often a shipment goes out under this subscription — Weekly, Bi-Weekly, Monthly, or Every 6 Weeks. bagsPerShipment INT e.g. 8 How many bags the customer receives with each scheduled delivery. pricePerBag DECIMAL(12,2) e.g. 39.7 The per-bag rate the subscriber pays, which may differ from the standard retail or wholesale price depending on when and how the subscription was set up. shipmentTotal DECIMAL(12,2) e.g. 317.6 The total charge for each shipment under this subscription, calculated as bagsPerShipment multiplied by pricePerBag. startDate DATE e.g. 2021-11-07 The date this subscription agreement first went into effect. status VARCHAR(60) e.g. Paused The current state of the subscription — Active means shipments are going out on schedule; Paused means the customer has temporarily stopped deliveries; Cancelled means the agreement has ended; Pending Payment means a billing issue is holding shipments. nextShipDate DATE e.g. 2025-07-02 The calendar date on which the next scheduled shipment under this subscription is due to go out. PROVENANCE ---------- Every value in this dataset is synthetic. No real person, company, or record is represented. Generated by the Agoge dataset generator 1.0.0 with seed 7. The same brief and seed reproduce this data exactly.