Filling in the Gem Candies data file

What

The "Gem Candies" Grist document — the single ledger behind the "SKU ledger" / "PO ledger" referenced throughout these procedures. One row per real-world thing: one vendor, one stone, one sale, one payment.

Where

docs.getgrist.com/27LRLgF2Q5hd/Gem-Candies

Powers

The internal tools site (services/gem-candies) — dashboard and data-entry forms — reads and writes this file directly. A bad row shows up there within seconds, not just here.

Before you start

• One row per real-world thing. A physical stone that gets recut, reset, or re-certified is still the same stone — update its existing Stones row, don't create a second one.
• Never edit a formula field. Grist greys these out, but if you're pasting a block of cells, make sure you don't paste over them. Anything in a table below tagged Formula is computed from other fields — fix the input, not the output.
• Match the ID formats already in use so cross-references and lookups stay predictable. Examples from the live data are given in each section below — follow them rather than inventing a new pattern.
• Unknown is a value, not a blank. If a stone's origin or clarity genuinely isn't known, pick "Unknown" from that field's list rather than leaving it empty — an empty reference field and a deliberate "Unknown" mean different things (not recorded yet, vs. recorded as unverifiable).
• New choice not in a dropdown? Add it to the underlying lookup table first (see Lookup tables below), then pick it — don't type a near-duplicate as free text.
• Review imported rows. The Dropbox importer adds Review status to Vendors, PurchaseOrders, Stones, Certificates, Clients, Sales, SaleLines, Receipts, Payments, ExtraCosts, and StoneEvents. Unconfirmed means a clear automatic result still needs human review; Dubious means evidence conflicted or matched ambiguously; only a person sets Confirmed. This is separate from whether ingestion is pending, applied, duplicate, failed, or archived. The Worker-owned Invoices table does not receive this field.

Recommended entry order

The tables are wired together with references and formulas, so filling them in this order means every total is already correct by the time you get to the last step — no separate reconciliation pass needed.

A new stone arrives

1. Vendor — check Vendors for the supplier; add a row only if they're new.
2. Purchase order — add one PurchaseOrders row per invoice, even for a single stone. Set Total PO cost to the full invoice amount as soon as you have it.
3. Stone — one Stones row per physical stone, linked to that purchase order. Leave Acquisition cost blank unless you know this specific stone's price separately from the rest of the invoice — with the PO's total cost and every stone's carat weight filled in, the per-stone cost allocates itself.
4. Certificate — if the stone is certified, one Certificates row per lab report.
5. Photos — one Photos row per stone, per Procedure 9's SKU-prefixed folder convention.
6. Extra costs — certification fees, duties, inbound shipping: one ExtraCosts row each, linked to the stone or the PO. These feed into the stone's total cost basis automatically.
7. Listing — once the Shopify product is published (Procedure 4), add a Listings row linking the two.

A sale happens

1. Client — check Clients; add a row only if they're new.
2. Sale — one Sales row per receipt/order, with commission, shipping, and tax as separate figures.
3. Sale line(s) — one SaleLines row per stone in that sale, linked to both the sale and the stone. This is what marks the stone sold in the ledger's cost/margin math. While Shopify is on standby, this Grist row — together with Stones.Status — is the authoritative "is it sold" flag; once Shopify is live, Shopify inventory takes over that role (Procedure 5/6).
4. Payment(s) — one Payments row per payment received. Most sales are one payment for the full amount; installments get one row each.

A ring is ordered

1. Sale and sale line(s) — as above. A ring bought with one of our stones has two lines: a Gemstone line linked to the stone, and a Ring line linked to the ring order below. A ring on the client's own stone has just the ring line.
2. Ring order — one RingOrders row, linked to the sale, the setting design and the jeweller. Set Stone source first: it decides whether a stone gets linked at all.
3. Custody — update the stone's Location as it actually moves, and the ring order's Status alongside it. If the client sent her own stone straight to the jeweller, there's nothing of ours in transit and nothing to update.
4. Jeweller's invoice — one ExtraCosts row of type Ring making, linked to the ring order, when his bill arrives. This is what turns the ring line's margin from a guess into a fact.
5. Close it — set the ring order to Delivered once the client has it.

Table by table

Vendors — suppliers you buy stones from

ID format in use: V-000001, V-000002, … Add a row before you can record a purchase order from a new supplier. Since 2026-09-03 the fields here are the same ones Shopify's Create supplier form asks for (Procedure S01), so a new supplier can be entered in both places from the one invoice — only the name is ever required.

Clients — buyers

ID format in use: C-000001, C-000002, … Add a row before recording a sale to a new client.

PurchaseOrders — one row per supplier invoice

ID format in use: PO-000001, PO-000002, … Every owned stone should trace back to one of these — a parcel PO for a multi-stone invoice, a one-stone PO for a single buy.

Stones — the core ledger, one row per physical stone

SKU format in use: GC-000001, GC-000002, … (six digits — confirmed as the format by the owner on 2026-08-24, and matched by Procedure 1). Assign the SKU and record intake per Procedure 1.

StoneEvents — what happened to each stone, and when

ID format: sequential SE-000001. One row per thing that happened to a stone: it was ordered, it arrived, it was reserved, sold, sent to the jeweller, shipped to the client. This is where a stone's Status and Location come from — both are worked out from the newest movement, so recording a movement is how you change them.

Nothing here is ever edited or deleted. It is a history: if a movement was recorded wrongly, record the right one after it, or add a Note explaining. Rewriting the past is exactly what this table exists to prevent.

Most movements are recorded for you. Selling a stone on the Sales form logs "Sold"; putting a stone on a purchase order logs "Received"; adding a gemstone logs its opening movement. What you log by hand is the physical side — the "Log a movement" box on the gemstone form, which is where "Sent to jeweller", "Shipped to client" and the rest belong.

One thing to watch: the most recently recorded movement is the one that counts, whatever date you give it. That is what you want when correcting a mistake — log what is true now and it takes effect. It also means entering history out of order has an effect: if you log last month's shipment after logging this month's, the stone ends up showing last month's. Catch up on history oldest-first, and finish with whatever is true today.

ExtraCosts — costs beyond the invoice price, per stone, per PO or per ring

ID format: sequential EC-000001, consistent with the other tables. Use Procedure 3's included-cost list — certification, inbound shipping, duties/customs, recutting, outbound shipping, insurance, payment processing — plus ring making. Never general overhead. Every row attaches to exactly one of a stone, a purchase order or a ring order. A stone or ring-order holder counts toward that stone's or ring's cost; a purchase-order holder (as of 2026-09-06) is reference only — it does not reach any stone.

Certificates — lab reports, one row per report

ID convention in use: the lab's own report number, e.g. AAP90739 — not a separate sequential ID. A stone that's recertified later gets a second row, not an overwrite.

Photos — one row per stone's photo set

ID format in use: PH-000001, PH-000002, … Create at intake per Procedure 1.

Listings — links a stone to its Shopify product

ID format in use: SHOP-000001, SHOP-000002, … Add a row when the product is published (Procedure 4).

Sales — one row per receipt/order

ID format in use: S-000001, S-000002, … (six digits, matching Stones' SKU width). What was actually printed on the client's receipt lives in Receipts, not here — this table holds what the figures should be.

SaleLines — one row per thing sold within a sale

ID format in use: S-000001-L1, S-000002-L1, S-000002-L2, … — the parent Sale ID plus a line number. A line is either a gemstone or a ring, never both: fill in the stone or the ring order, not the two together.

SettingModels — the ring settings you offer

A catalogue, not inventory. Nothing here is ever bought, stocked, counted or valued — a setting exists only once a client orders one. Add a row when you start offering a new design; there is nothing to do at intake, because there is no intake.

RingOrders — one row per ring being made

ID format: R-000001, R-000002, … Create the row when the order is placed, not when the ring is finished — this is what you track production against, and what the jeweller's invoice attaches to.

Payments — one row per payment received

ID format in use: P-000001, P-000002, … Most sales are a single payment for the full amount; only installments or deposits get more than one row per sale.

Receipts — what the client was actually handed

One row per sale. Kept apart from Sales on purpose: the sale holds what the figures should be, the receipt holds what was printed, and comparing the two is what catches a mistake.

Invoices — ring invoices raised through the reveal site

Written by the private reveal site when an invoice is approved, so it appears in the ledger alongside everything else — not filled in by hand, and not used for an ordinary Shopify sale.

IngestionItems — Dropbox ingestion audit and review

One row per source or derived logical document. This is the replay/audit trail: it records what exact Dropbox revision was evaluated, where the file came from and went, what business row was affected (or matched as a duplicate), and what still needs human review. It never stores raw documents or secrets. As of 2026-09-07 the repository migration is ready but intentionally not yet applied to the live document; inspection is the default and writing requires the explicit --apply mode.

Exceptions — the data-quality log

Not part of the normal entry workflow above — this is where you (or a future reconciliation pass, Procedure 8) log a problem spotted in the ledger that needs a decision rather than a routine edit. Currently holds mostly migration-era findings (e.g. listings left Active after their stone sold).

Lookup tables — L_GemType, L_Shape, L_ColorNuance, L_Clarity, L_Treatment, L_Country, L_PaymentMethod, L_CostType

These are what every Reference-tagged field above actually points to. Each has just a Value and a Notes column. If a new stone genuinely needs a gem type, shape, color, origin, etc. that isn't in the list yet, add one row to the relevant L_ table first, then select it from the Stones (or Sales/ExtraCosts) row — don't type a free-text near-duplicate into the reference field, since that breaks grouping and filtering everywhere else the field is used. Keep the wording consistent with existing entries (e.g. "Unknown / not stated" vs. "Unknown" — check what's already there before adding a variant). L_CostType now includes Ring making, for jeweller invoices.