steve
78a04672b5
Build image / build-and-push (push) Successful in 8s
Co-authored-by: Steve Dudenhoeffer <[email protected]>
54 lines
2.8 KiB
SQL
54 lines
2.8 KiB
SQL
-- Seed provenance and inventory (#50): where "German Red Garlic" came from, and
|
|
-- how much of it is left.
|
|
--
|
|
-- Two modelling calls worth stating, because both look like omissions otherwise:
|
|
--
|
|
-- 1. The plant catalog stays FLAT. No species→variety hierarchy. A row in your
|
|
-- catalog just is the thing you plant — "German Red Garlic" is a plant row,
|
|
-- and the built-in "Garlic" is a generic starting point you clone. A
|
|
-- hierarchy buys taxonomy nobody asked for and complicates every join.
|
|
--
|
|
-- 2. Inventory belongs to a PURCHASE, not to a variety. You might buy the same
|
|
-- garlic from two vendors in two years at two prices and two germination
|
|
-- rates. Quantity columns on plants would collapse all of that into one
|
|
-- number and lose it, so lots get their own table.
|
|
--
|
|
-- Nothing here is destructive: plants gains two defaulted columns, the built-ins
|
|
-- keep working with empty strings, and plantings gains a nullable link.
|
|
ALTER TABLE plants ADD COLUMN source_url TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE plants ADD COLUMN vendor TEXT NOT NULL DEFAULT '';
|
|
|
|
-- owner_id sits on the LOT rather than being inherited from the plant, because a
|
|
-- lot may reference a built-in plant (you can buy generic Garlic from Johnny's)
|
|
-- while still being nobody's business but yours. Lots are never shared along
|
|
-- with a garden.
|
|
CREATE TABLE seed_lots (
|
|
id INTEGER PRIMARY KEY,
|
|
owner_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
plant_id INTEGER NOT NULL REFERENCES plants (id) ON DELETE CASCADE,
|
|
vendor TEXT NOT NULL DEFAULT '',
|
|
source_url TEXT NOT NULL DEFAULT '',
|
|
sku TEXT NOT NULL DEFAULT '',
|
|
lot_code TEXT NOT NULL DEFAULT '',
|
|
purchased_at TEXT, -- 'YYYY-MM-DD'
|
|
packed_for_year INTEGER,
|
|
quantity REAL NOT NULL DEFAULT 0,
|
|
unit TEXT NOT NULL CHECK (unit IN ('seeds','grams','ounces','packets','bulbs','plants')),
|
|
cost_cents INTEGER,
|
|
germination_pct REAL,
|
|
notes TEXT NOT NULL DEFAULT '',
|
|
version INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
|
|
);
|
|
|
|
CREATE INDEX idx_seed_lots_owner ON seed_lots (owner_id);
|
|
CREATE INDEX idx_seed_lots_plant ON seed_lots (plant_id);
|
|
|
|
-- Which lot a planting came out of. ON DELETE SET NULL rather than CASCADE:
|
|
-- retiring a lot must never destroy planting history — the plants really were in
|
|
-- the ground, whatever happened to the record of the packet.
|
|
ALTER TABLE plantings ADD COLUMN seed_lot_id INTEGER REFERENCES seed_lots (id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX idx_plantings_seed_lot ON plantings (seed_lot_id) WHERE seed_lot_id IS NOT NULL;
|