Allow chestshops to have a price of $0

Ideally this is done via a separate mechanism to ChestShop we can revisit this with staff team approval

Triage: impact scoping for $0 chestshop pricing + penny workaround

Data window: entire chestshop_sale history, 2026-05-30 → 2026-06-11 (~12 days), 531,128 sales.

Penny-pricing workaround is in heavy use

• 37,887 sales priced at exactly unit_price = 0.01 = 7.1% of all sales, 169,786 units moved.
• Spread across 91 distinct shop accounts / 40 firms (plus the large player-owned, non-firm cohort below).
• On the shop side: 374 active shop rows carry a 0.01 buy/sell price, across 82 shop accounts / 37 firms.
• unit_price = 0 sales: 0 (the $0 ban is fully effective today — the penny is the only escape hatch, confirming the ticket's premise).

Who is doing it

• The bulk is player-owned shops (shop_firm_id NULL): 33,926 penny sales (90%) across 51 accounts / 69 materials — i.e. this is a broad community workaround, not just one department.
• Top firm offenders: Thaw (2017, 1,253), Papillon (166, 479), UMTree (1909, 305), Reagill (2098, 269), ColonneRealty (2043, 239), Evonima (1612, 178).
• Top materials by penny-sale count: GLASS_BOTTLE (12,408), STICK (10,547), SUNFLOWER (1,808), PAPER (1,791), HONEY_BOTTLE (1,228 / 12,662 units), FISHING_ROD (1,191). Consistent with "give-away / internal-transfer" goods (e.g. the DOH doctor-supply case in the ticket).

The price-skew is real and measurable

• There is no materialized market-price table in the prod schema (information_schema has no %price%/%market% table) — market/average price is computed on the fly from chestshop_sale.unit_price. So "exclude penny/zero from aggregation" is a query-level filter, not a backfill.
• Global avg unit_price 34.95 with penny rows vs 38.21 excluding them — penny sales understate the headline average by ~8.5%. 87 of 870 traded materials are contaminated by penny rows, so the skew is concentrated and per-material it can be far worse than the global figure.

Root cause / design note

A $0 ban with no exclusion path forced users into a $0.01 proxy; those rows are economically meaningless transfers (the ticket: DOH charges/pays doctors a cent) but currently count as real trades in price aggregation. The ask = (a) allow price = 0 and (b) exclude both 0 and the legacy 0.01 proxy rows from market price-aggregation so averages aren't skewed.

Recommended next step

Per @paradaux's earlier comment, gate the price=0 allowance behind staff approval and implement via a separate mechanism, not core ChestShop pricing. For the aggregation half (no remediation SQL needed — read-only data, nothing to fix in the DB):

• When (a) ships, add the predicate unit_price > 0.01 (covers both the future genuine $0 and the legacy penny proxy) to every market-price aggregation query — primarily the explorer's chestshop price views. Sanity-check filter:

-- rows that SHOULD be excluded from price aggregation
SELECT material, COUNT(*) FROM chestshop_sale
WHERE unit_price <= 0.01
GROUP BY material ORDER BY COUNT(*) DESC;

• Open question for whoever picks this up: should excluded rows still count toward volume/quantity stats (units genuinely moved) while being dropped only from price averages? Recommend yes — drop from price, keep in volume.

classification: context-added (feature request, scoping data attached; no DB defect to remediate).