How the data file is put together
- What
- The shape of the "Gem Candies" Grist document — which tables exist, what one row of each represents, and how they connect. The data file guide tells you what to type in each field; this page tells you why the fields are arranged the way they are.
- Who it's for
- Anyone who has to answer "where does this go?" for something the guide doesn't cover — a new kind of cost, a new kind of sale, a new kind of product.
- Status
- This describes the target model. The ring-setting and ring-order tables are now Built and live in the file; anything still outstanding is marked To build.
- Also
- The exact column-by-column schema, for whoever is writing code against the file, is kept in
GRIST_DATA_MODEL.mdin the project repository. This page and that one describe the same model; if they ever disagree, the live Grist document settles it.
The one rule everything else follows
One row per real-world thing, and each fact recorded in exactly one place. A stone's carat weight lives on the stone. A client's address lives on the client. Everything else that needs those facts points at them rather than keeping its own copy — so when a fact changes, it changes once, and nothing anywhere is left saying the old thing.
Two consequences worth knowing before you read the map:
- A reference is a link, not a label. When you pick a vendor on a purchase order, Grist stores a pointer to that vendor's row. Rename the vendor and every purchase order follows. Type the name as text instead and you've created a second, unmaintained copy.
- A completed sale is the exception. Once a sale is recorded, its figures are frozen onto the sale line and stop following the stone. That's deliberate — see Why sold lines stop moving.
The map
Exceptions isn't shown: it's a standalone log that points at other rows by description rather than by link.Read left to right and it's the life of a stone: you buy it from a vendor on a purchase order, it becomes a stone, and it leaves as a sale line on a sale to a client. Everything above and below that spine is detail hanging off it — what the stone is (lookups), what proves it (certificates, photos), where it's advertised (listings), what it cost beyond the invoice (extra costs), and what the client paid (payments, receipts, invoices).
Where ring settings fit Built
Ring settings are not inventory. Nothing is ever bought in, stocked, counted or valued — a setting is made only once a client orders one. So they don't belong in Stones, and they don't get purchase orders. They have two tables of their own:
| Table | One row is | Why it's separate |
|---|---|---|
SettingModels | One setting design you offer. | A catalogue entry — the design, the metals it comes in, the price you charge, and later its Shopify product. It has no quantity, no status and no cost of its own, because nothing exists until it's ordered. |
RingOrders | One ring actually being made. | The production record: which stone (if it's ours), which setting design, which metal and size, which jeweller, and where it is in the process. This is what the jeweller's invoice attaches to. |
A ring sale therefore has two sale lines — one for the gemstone, one for the ring — which mirrors exactly how the two lines arrive linked in a Shopify cart. Only the ring line points at the ring order; the gemstone line points at the stone, as it always has. Each line is then costed from its own side, and the two are paired by sitting on the same sale — with the ring order itself naming the stone, so even a sale containing two rings stays unambiguous.
Stones row is created: it isn't our stone, it isn't our inventory, and it has no cost basis. The ring order records Stone source: client-supplied plus a plain description of her stone, and its stone link stays empty on purpose. That sale has one sale line, not two.Where a cost goes
Every cost beyond the price on the supplier's invoice is one ExtraCosts row — certification, inbound freight, duties, recutting, insurance, and now the jeweller's bill for making a ring. Each row attaches to exactly one of three things:
| Attach it to | When | Example |
|---|---|---|
| A stone | The cost exists because of that one stone. Counts toward that stone's cost basis. | Its certification fee. Its recutting. |
| A purchase order | The cost covers a whole parcel and you just want it recorded against the PO. Reference only as of 2026-09-06 — it does not reach any stone's cost basis. Split a shared cost by hand across the stones it actually covers instead, if it should count toward their value. | One shipment's freight for twenty stones. Customs on the parcel. |
| A ring order Built | The cost exists because a ring is being made. | The jeweller's invoice. Rush shipping on a ring. |
The jeweller's invoice is a third-party bill like any freight invoice, but it must not land on the stone. The stone's cost basis is what the stone cost you — it's also what values your inventory. Putting ring-making costs there would misstate both, and for a client's own stone there'd be nothing to attach it to at all.
A stone's cost basis is therefore two things added together: its own acquisition cost (or, as a last resort, the legacy $/ct figure), and the costs booked against it alone. Cost allocation across a purchase order's stones — where a shared invoice or a shared extra cost used to split automatically by carat weight — was removed 2026-09-06; enter each stone's own cost directly now.
The three shapes of a sale
Illustrative figures, to show which number comes from where. "Margin" below is not a stored field — there is no Margin column on Stones or SaleLines any more (removed 2026-09-06) — it's just Line total minus costs, shown here to explain how a sale's profit splits between a gemstone line and a ring line.
1 · A loose stone
One sale line. Margin is the stone's.
| Sale line — 3.39 ct rubellite | 2,712.00 |
| Less acquisition cost | −1,180.00 |
| Less certification, an extra cost on the stone | −95.00 |
| Margin | 1,437.00 |
2 · A ring made from one of our stones
Two sale lines and one ring order. Each line keeps its own margin.
| Sale line 1 — the gemstone | 2,712.00 |
| Less the stone's total cost basis | −1,275.00 |
| Sale line 2 — the ring | 790.00 |
| Less the jeweller's invoice, an extra cost on the ring order | −610.00 |
| Margin | 1,617.00 |
Split the way the ledger splits it: 1,437.00 on the stone, 180.00 on the ring. If the jeweller's invoice had been attached to the stone instead, the total would be identical but both line margins would be wrong.
3 · A ring made from the client's own stone
One sale line, one ring order, no stone anywhere in the ledger.
| Sale line — the ring | 790.00 |
| Less the jeweller's invoice | −610.00 |
| Margin | 180.00 |
Her stone is worth several thousand dollars and appears nowhere — correctly. It was never bought, never owned, and never sold.
Why sold lines stop moving Built
A sale line's figures are now frozen onto the line when it is created, so editing a stone no longer rewrites sales that already happened. Previously the line had no figures of its own — its carats, cost basis and line total were all calculated live from the stone, every time the file opened, which meant editing a stone silently rewrote sales that already happened.
Two ordinary edits did it. Recutting a stone changes its carat weight — and the guide correctly tells you to update the same row rather than create a new one — which changed the line total of a sale closed months ago. Adding a new stone to an old purchase order changes how that invoice divides up, which lowered the cost basis of every stone already on it, including ones long sold.
The three figures below are trigger formulas, captured once when the sale line is created and left alone afterwards:
- Carats — the weight at the moment of sale.
- Line total — what you actually charged.
- Cost basis — what the item had cost you by then.
They stay editable, so a genuine correction is still one click. What changes is that they no longer move on their own. Everything unsold keeps calculating live, exactly as now. There is no stored Margin figure any more (removed 2026-09-06, from both SaleLines and Stones) — Line total minus Cost basis, computed by hand, when you want it.
Table by table
The spine
| Table | One row is | Points at |
|---|---|---|
Vendors | One supplier, or the jeweller. | — |
PurchaseOrders | One supplier invoice, whether it covered one stone or twenty. | Vendors |
Stones | One physical stone we own. Recut, reset or re-certified, it stays the same row. Its status and location are not typed in — they follow from its movements in StoneEvents. | PurchaseOrders (whose own Vendor names the stone's vendor — no direct link since 2026-09-06), the L_ lookups |
SaleLines | One thing sold — a gemstone, or a ring. Its kind says which. | Sales, and then Stones or RingOrders — one or the other, never both |
Sales | One order, whatever it contained. | Clients |
Clients | One buyer. | — |
Making rings Built
| Table | One row is | Points at |
|---|---|---|
SettingModels | One setting design offered. | — |
RingOrders | One ring being made, from order to delivery. | SettingModels, Vendors (the jeweller), and a Stone unless the client supplied her own |
Money out
| Table | One row is | Points at |
|---|---|---|
ExtraCosts | One third-party bill beyond the purchase price. | Exactly one of: a Stone, a PurchaseOrder, a RingOrder |
Money in
| Table | One row is | Points at |
|---|---|---|
Payments | One payment received. Installments get one row each. | Sales |
Receipts | The receipt you handed the client, transcribed as printed. | Sales |
Invoices | A ring invoice raised through the reveal site. | Sales |
A receipt is stored separately from its sale on purpose. The sale holds what the figures should be; the receipt holds what was actually printed. Keeping them apart is what lets the file tell you when the two disagree — which is the whole point of transcribing a receipt you already suspect is wrong.
A payment, a receipt and an invoice each show a client of their own, but none is typed in: all read the client from the sale they belong to, so an invoice can never name a different person than the sale it was raised for. The sale is the one place that answer is decided.
Evidence about a stone
| Table | One row is | Points at |
|---|---|---|
Certificates | One lab report. A re-certified stone gains a second row rather than losing its first. | Stones |
StoneEvents | One thing that happened to a stone — ordered, received, reserved, sold, sent to the jeweller, shipped to the client. A stone's status and location are worked out from these, so this is the history behind every state the ledger shows. | Stones, and whichever of PurchaseOrders, Sales, RingOrders or Vendors caused the move |
Photos | One stone's photo set. | Stones |
Listings | One stone's Shopify product. | Stones |
Exceptions | One data problem needing a decision rather than a routine edit. | Nothing — it names rows in text |
Lookups
L_GemType, L_Shape, L_ColorNuance, L_Clarity, L_Treatment, L_Country, L_PaymentMethod, L_CostType — the permitted values for the reference fields elsewhere. A new value goes into the lookup table first, then gets picked. Typing a near-duplicate as free text breaks grouping and filtering everywhere that field is used. L_CostType now includes Ring making for jeweller invoices. Built
What is changing, and what isn't
| Change | Why |
|---|---|
Add SettingModels and RingOrders Built | Ring settings and the rings made from them had nowhere to live. The reveal-site invoice still records the setting as loose text; pointing it at a SettingModels row is a separate change. |
Let an ExtraCosts row attach to a ring order Built | Somewhere to put the jeweller's invoice. A Check column now flags a cost with no holder, or with more than one. |
| Count parcel-level costs Built, then removed | A cost attached to a purchase order used to reach no cost basis at all. Built 2026-08-23 so each stone on the PO picked up its carat-weight share automatically; removed 2026-09-06 (owner decision, "for the time being, just record the data") — a PO-held cost is recorded for reference again, same as before this feature existed, and a stone's own cost basis comes only from its own Acquisition cost. |
| Freeze carats, line total and cost basis on a sale line Built | So that editing a stone stops rewriting sales that already happened. See above. Margin was frozen too at first, then removed outright on 2026-09-06 rather than kept — there is no Margin field any more on either SaleLines or Stones. |
| Add a kind to each sale line Built | A line is now a gemstone or a ring, and the file says which rather than inferring it from whichever link happens to be filled. All 26 existing lines were set to Gemstone. A Check column flags a line whose links don't match its kind. |
One dollar figure per amount on Invoices Built | Invoice totals were previously held twice, in dollars and in cents. The cents columns were removed; one figure cannot disagree with itself. |
| Warn on duplicate SKUs and IDs Built | Grist can't refuse a duplicate, but it can flag one. A repeated SKU is how a stone gets sold twice. |
| Field notes move into the file To build | The rules in the data file guide should be visible in Grist while you're typing, not only in a browser tab you'd have to go and open. |
Not changing: stones stay one row per physical stone; purchase orders stay gemstone-only and keep dividing by carat weight, since nothing about a setting is ever purchased into stock; and every existing SKU, sale and certificate keeps its identity. Nothing about this is a migration you have to participate in.