1-- Visitor feedback is opt-in per site and only served behind the site's password gate.2-- Existing sites keep feedback off. Notes cascade away with their site, so an address that3-- is later reallocated never inherits another site's feedback.4ALTER TABLE sites ADD COLUMN feedback INTEGER NOT NULL DEFAULT 0 CHECK (feedback IN (0, 1));5ALTER TABLE sites ADD COLUMN feedback_requested_at INTEGER;6ALTER TABLE sites ADD COLUMN feedback_submitted_at INTEGER;78CREATE TABLE IF NOT EXISTS site_feedback (9 address INTEGER NOT NULL REFERENCES sites(address) ON DELETE CASCADE,10 item TEXT NOT NULL,11 text TEXT NOT NULL,12 seq INTEGER NOT NULL,13 updated_at INTEGER NOT NULL,14 PRIMARY KEY (address, item)15);1617-- Defense in depth for concurrent writers. Bodies use SELECT RAISE(...) WHERE ... (no CASE/END)18-- so D1's remote statement splitter keeps each trigger whole. Keep BEGIN uppercase and LF endings.19CREATE TRIGGER IF NOT EXISTS sites_feedback_policy_insert20BEFORE INSERT ON sites BEGIN21 SELECT RAISE(ABORT, 'feedback requires password protection')22 WHERE NEW.feedback = 1 AND NEW.password_hash IS NULL;23END;2425CREATE TRIGGER IF NOT EXISTS sites_feedback_policy_update26BEFORE UPDATE ON sites BEGIN27 SELECT RAISE(ABORT, 'feedback requires password protection')28 WHERE NEW.feedback = 1 AND NEW.password_hash IS NULL;29END;3031CREATE TRIGGER IF NOT EXISTS site_feedback_item_cap32BEFORE INSERT ON site_feedback BEGIN33 SELECT RAISE(ABORT, 'feedback item limit reached')34 WHERE (SELECT COUNT(*) FROM site_feedback WHERE address = NEW.address) >= 20035 AND NOT EXISTS (SELECT 1 FROM site_feedback WHERE address = NEW.address AND item = NEW.item);36END;