1 files changed, 47 insertions, 0 deletions
diff --git a/crates/server/migrations/0017_merch.sql b/crates/server/migrations/0017_merch.sql
new file mode 100644
index 0000000..9aba955
--- /dev/null
+++ b/crates/server/migrations/0017_merch.sql
@@ -0,0 +1,47 @@
+- Physical goods: shirts, mugs, deskmats.
+--
+- These differ from cosmetics in three ways that the schema has to carry. They
+- have a size or colour to choose. They need a shipping address. And nothing
+- is granted on payment: an order is recorded for someone to pack and post.
+CREATE TABLE merch (
+    id TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    description TEXT,
+    price_cents INTEGER NOT NULL,
+    - 'shirt', 'mug', 'deskmat'. Groups the store display.
+    kind TEXT NOT NULL,
+    - Sizes or colours. Empty means the item has only one form.
+    variants TEXT[] NOT NULL DEFAULT '{}',
+    - Postage, charged once per order rather than per item.
+    shipping_cents INTEGER NOT NULL DEFAULT 0,
+    available BOOLEAN NOT NULL DEFAULT TRUE,
+    sort_order INTEGER NOT NULL DEFAULT 0
+);
+
+INSERT INTO merch (id, name, description, price_cents, kind, variants, shipping_cents, sort_order) VALUES
+    ('shirt-mark', 'Mark T-Shirt', 'The TyperPunk mark, printed small on the left chest.', 2600, 'shirt',
+     ARRAY['S','M','L','XL','2XL'], 600, 1),
+    ('shirt-layout', 'Home Row T-Shirt', 'ASDF JKL; across the chest, set in the same face the app types in.', 2800, 'shirt',
+     ARRAY['S','M','L','XL','2XL'], 600, 2),
+    ('mug-prompt', 'Prompt Mug', 'The mark on one side, a blinking block cursor on the other. 11oz.', 1600, 'mug',
+     '{}', 700, 3),
+    ('mug-wpm', 'WPM Mug', 'Reads "measured in words per minute, drunk in cups per hour". 11oz.', 1600, 'mug',
+     '{}', 700, 4),
+    ('deskmat-track', 'Race Deskmat', 'The race track across 900x400mm, stitched edge, rubber base.', 3800, 'deskmat',
+     '{}', 900, 5),
+    ('deskmat-mark', 'Mark Deskmat', 'The mark bottom-right on plain black. 900x400mm, stitched edge.', 3600, 'deskmat',
+     '{}', 900, 6);
+
+- Orders. A purchase row already records payment; this records what has to be
+- packed and where it goes. The address comes back from the processor, which
+- collected it during checkout, so it is never typed into this site.
+ALTER TABLE purchases DROP CONSTRAINT IF EXISTS purchases_kind_check;
+ALTER TABLE purchases ADD CONSTRAINT purchases_kind_check
+    CHECK (kind IN ('cosmetic', 'supporter', 'bundle', 'merch'));
+ALTER TABLE purchases ADD COLUMN merch_id TEXT REFERENCES merch(id) ON DELETE SET NULL;
+ALTER TABLE purchases ADD COLUMN merch_variant TEXT;
+- Set once the item is posted. NULL means it is still waiting to be packed.
+ALTER TABLE purchases ADD COLUMN shipped_at TEXT;
+ALTER TABLE purchases ADD COLUMN shipping_address TEXT;
+
+CREATE INDEX idx_purchases_unshipped ON purchases(kind, status, shipped_at);