max / makenotwork

2.9 KB · 73 lines History Blame Raw

1	-- Outbox table for transaction-completion side effects.
2	--
3	-- The Stripe webhook handler flips the underlying row (transaction, tip,
4	-- subscription) to "completed" inside one DB transaction; it ALSO inserts one
5	-- `completion_effects` row per side-effect (bundle grant, license key, revenue
6	-- splits, emails, etc.) in that same transaction. A background worker drains
7	-- the outbox with FOR UPDATE SKIP LOCKED, retrying each effect independently.
8	--
9	-- This decouples "the sale is recorded" (atomic, exactly-once) from "all
10	-- derived effects have run" (eventually consistent, individually retryable).
11	-- Before this table existed, a retry of a completed webhook silently
12	-- abandoned every effect because `complete_transaction` returned `Ok(None)`
13	-- on the second call and the handler short-circuited.
14
15	CREATE TABLE completion_effects (
16	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
17
18	-- Polymorphic parent: exactly one of these is set per row.
19	transaction_id UUID REFERENCES transactions(id) ON DELETE CASCADE,
20	tip_id UUID REFERENCES tips(id) ON DELETE CASCADE,
21	subscription_id UUID REFERENCES subscriptions(id) ON DELETE CASCADE,
22
23	-- Discriminator for the dispatcher's switch.
24	kind VARCHAR(64) NOT NULL,
25
26	-- JSON-serialised inputs needed to re-execute the effect.
27	payload JSONB NOT NULL DEFAULT '{}'::jsonb,
28
29	-- pending \| succeeded \| failed (terminal after max_attempts)
30	status VARCHAR(16) NOT NULL DEFAULT 'pending',
31	attempt INT NOT NULL DEFAULT 0,
32	last_error TEXT,
33
34	scheduled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
35	completed_at TIMESTAMPTZ,
36	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
37
38	CHECK (
39	(transaction_id IS NOT NULL)::int +
40	(tip_id IS NOT NULL)::int +
41	(subscription_id IS NOT NULL)::int = 1
42	)
43	);
44
45	CREATE INDEX idx_completion_effects_pending
46	ON completion_effects (scheduled_at)
47	WHERE status = 'pending';
48
49	CREATE INDEX idx_completion_effects_transaction
50	ON completion_effects (transaction_id)
51	WHERE transaction_id IS NOT NULL;
52
53	CREATE INDEX idx_completion_effects_tip
54	ON completion_effects (tip_id)
55	WHERE tip_id IS NOT NULL;
56
57	CREATE INDEX idx_completion_effects_subscription
58	ON completion_effects (subscription_id)
59	WHERE subscription_id IS NOT NULL;
60
61	-- Effect-level idempotency: don't double-enqueue the same effect for the same
62	-- parent (e.g. a duplicate webhook delivery races the first one between the
63	-- "row already completed?" check and the outbox INSERT).
64	CREATE UNIQUE INDEX uq_completion_effects_transaction_kind
65	ON completion_effects (transaction_id, kind)
66	WHERE transaction_id IS NOT NULL;
67	CREATE UNIQUE INDEX uq_completion_effects_tip_kind
68	ON completion_effects (tip_id, kind)
69	WHERE tip_id IS NOT NULL;
70	CREATE UNIQUE INDEX uq_completion_effects_subscription_kind
71	ON completion_effects (subscription_id, kind)
72	WHERE subscription_id IS NOT NULL;
73