1-- Existing vaults retain their current behavior. Passwords are stored only as verifiers.2ALTER TABLE vaults ADD COLUMN max_ttl REAL3 CHECK (max_ttl IS NULL OR (max_ttl > 0 AND max_ttl <= 3650));4ALTER TABLE vaults ADD COLUMN require_password INTEGER NOT NULL DEFAULT 05 CHECK (require_password IN (0, 1));6ALTER TABLE vaults ADD COLUMN default_password_hash TEXT;78-- These guards also cover concurrent API operations and non-CLI writers.9-- Bodies use SELECT RAISE(...) WHERE ... rather than CASE ... END: D1's remote statement splitter10-- ends a trigger at the first END it sees, so a CASE expression inside a body breaks11-- `wrangler d1 migrations apply --remote` with "incomplete input". Keep BEGIN uppercase and LF endings.12CREATE TRIGGER IF NOT EXISTS sites_vault_policy_insert13BEFORE INSERT ON sites BEGIN14 SELECT RAISE(ABORT, 'vault requires password protection')15 WHERE (SELECT require_password FROM vaults WHERE slot = NEW.vault_slot) = 116 AND NEW.password_hash IS NULL;17 SELECT RAISE(ABORT, 'site expiry exceeds vault maximum TTL')18 WHERE EXISTS (SELECT 1 FROM vaults WHERE slot = NEW.vault_slot AND max_ttl IS NOT NULL19 AND (NEW.expires_at IS NULL OR NEW.expires_at > unixepoch() + round(max_ttl * 86400)));20END;2122CREATE TRIGGER IF NOT EXISTS sites_vault_policy_update23BEFORE UPDATE ON sites BEGIN24 SELECT RAISE(ABORT, 'vault requires password protection')25 WHERE (SELECT require_password FROM vaults WHERE slot = NEW.vault_slot) = 126 AND NEW.password_hash IS NULL;27 SELECT RAISE(ABORT, 'site expiry exceeds vault maximum TTL')28 WHERE EXISTS (SELECT 1 FROM vaults WHERE slot = NEW.vault_slot AND max_ttl IS NOT NULL29 AND (NEW.expires_at IS NULL OR NEW.expires_at > unixepoch() + round(max_ttl * 86400)));30END;3132CREATE TRIGGER IF NOT EXISTS vault_policy_insert33BEFORE INSERT ON vaults BEGIN34 SELECT RAISE(ABORT, 'vault default TTL exceeds maximum TTL')35 WHERE NEW.max_ttl IS NOT NULL36 AND (COALESCE(NEW.default_ttl, 14) = 0 OR COALESCE(NEW.default_ttl, 14) > NEW.max_ttl);37END;3839CREATE TRIGGER IF NOT EXISTS vault_policy_update40BEFORE UPDATE ON vaults BEGIN41 SELECT RAISE(ABORT, 'vault default TTL exceeds maximum TTL')42 WHERE NEW.max_ttl IS NOT NULL43 AND (COALESCE(NEW.default_ttl, 14) = 0 OR COALESCE(NEW.default_ttl, 14) > NEW.max_ttl);44 SELECT RAISE(ABORT, 'protect existing sites before requiring vault passwords')45 WHERE NEW.require_password = 146 AND EXISTS (SELECT 1 FROM sites WHERE vault_slot = OLD.slot AND password_hash IS NULL);47 SELECT RAISE(ABORT, 'shorten existing site expiry before tightening vault maximum TTL')48 WHERE NEW.max_ttl IS NOT NULL49 AND EXISTS (SELECT 1 FROM sites WHERE vault_slot = OLD.slot AND50 (expires_at IS NULL OR expires_at > unixepoch() + round(NEW.max_ttl * 86400)));51END;