| 1 | -- One frozen invoice operation per enterprise. Keep an ambiguous operation |
| 2 | -- until reconciled; Stripe's temporary idempotency cache is not the record. |
| 3 | CREATE TABLE enterprise_invoice_attempts ( |
| 4 | account_id TEXT PRIMARY KEY, |
| 5 | id TEXT NOT NULL UNIQUE, |
| 6 | payload TEXT NOT NULL, |
| 7 | created_ms INTEGER NOT NULL, |
| 8 | invoice_id TEXT, |
| 9 | completed INTEGER NOT NULL DEFAULT 0 CHECK (completed IN (0, 1)) |
| 10 | ); |
| 11 | |
| 12 | -- Keep the owner/slug used in a frozen invoice stable until it is recorded. |
| 13 | -- This is transactional: a concurrent detach or rename cannot slip between |
| 14 | -- an application-level check and the invoice acquisition. |
| 15 | CREATE TRIGGER enterprise_attempt_member_update BEFORE UPDATE ON account_members |
| 16 | WHEN (NEW.workspace <> OLD.workspace OR NEW.account_id <> OLD.account_id) |
| 17 | AND EXISTS (SELECT 1 FROM enterprise_invoice_attempts a, json_each(a.payload,'$.lines') l |
| 18 | WHERE a.completed=0 AND json_extract(l.value,'$.workspace')=OLD.workspace) |
| 19 | BEGIN SELECT RAISE(ABORT,'Finish the pending enterprise invoice before moving this workspace'); END; |
| 20 | CREATE TRIGGER enterprise_attempt_member_delete BEFORE DELETE ON account_members |
| 21 | WHEN EXISTS (SELECT 1 FROM enterprise_invoice_attempts a, json_each(a.payload,'$.lines') l |
| 22 | WHERE a.completed=0 AND json_extract(l.value,'$.workspace')=OLD.workspace) |
| 23 | BEGIN SELECT RAISE(ABORT,'Finish the pending enterprise invoice before moving this workspace'); END; |