g1t/services/billing/migrations/0043_tax_and_card_fees.sql
| 1 | -- Stripe Tax on every payment, and the card processing fee on every card |
| 2 | -- payment. |
| 3 | -- |
| 4 | -- Prices are shown and kept excluding tax. Stripe works the tax out on |
| 5 | -- every Checkout page, subscription, invoice and off-session charge |
| 6 | -- (`automatic_tax`, tax code `txcd_10103001`, tax behavior `exclusive`). |
| 7 | -- What reaches a workspace's balance is the payment less its tax and its |
| 8 | -- card fee; neither is revenue. Each is kept here, one row per payment and |
| 9 | -- kind, so the statement shows them as their own lines and sudo's Costs |
| 10 | -- shows tax collected apart from cash. |
| 11 | |
| 12 | CREATE TABLE IF NOT EXISTS tax_and_fees ( |
| 13 | -- `<reference>/tax` or `<reference>/card_fee`. A refund's share is |
| 14 | -- negative, under the refund's reference `refund/<charge>/<refunded>`. |
| 15 | id TEXT PRIMARY KEY, |
| 16 | -- The workspace, or for an enterprise's invoice its billing account. |
| 17 | workspace TEXT NOT NULL, |
| 18 | -- tax or card_fee. |
| 19 | kind TEXT NOT NULL, |
| 20 | -- Positive when collected, negative when refunded. |
| 21 | amount_micros INTEGER NOT NULL, |
| 22 | -- The payment: an invoice, a Checkout page or a PaymentIntent. |
| 23 | reference TEXT NOT NULL, |
| 24 | -- The PaymentIntent that took the money, so a refund finds its tax. |
| 25 | payment_intent TEXT, |
| 26 | -- Stripe Tax's transaction for an off-session charge (auto-reload), |
| 27 | -- which a refund reverses. |
| 28 | tax_transaction TEXT, |
| 29 | created_at TEXT NOT NULL |
| 30 | ); |
| 31 | CREATE INDEX IF NOT EXISTS tax_and_fees_by_workspace ON tax_and_fees (workspace, created_at); |
| 32 | CREATE INDEX IF NOT EXISTS tax_and_fees_by_day ON tax_and_fees (created_at); |
| 33 | CREATE INDEX IF NOT EXISTS tax_and_fees_by_intent ON tax_and_fees (payment_intent); |
| 34 | |
| 35 | -- A workspace invoice's card fee and tax, apart from the usage it pays for. |
| 36 | ALTER TABLE workspace_invoices ADD COLUMN fee_micros INTEGER NOT NULL DEFAULT 0; |
| 37 | ALTER TABLE workspace_invoices ADD COLUMN tax_micros INTEGER NOT NULL DEFAULT 0; |
| 38 | |
| 39 | -- Set when Stripe Tax could not work out where the customer is (no |
| 40 | -- billing address), so g1t did not charge: the Billing page asks an owner |
| 41 | -- for the address, and saving it clears this. |
| 42 | ALTER TABLE accounts ADD COLUMN tax_address_needed_at TEXT; |
| 43 | |
| 44 | -- The card processing fee is on for every card payment, not only AI |
| 45 | -- credit: the setting's note says so. Never on invoiced or enterprise |
| 46 | -- payments, or bank transfers. |
| 47 | INSERT OR IGNORE INTO cost_settings (key, value, updated_at, updated_by) VALUES |
| 48 | ('card_fee', 'on', '2026-10-08T00:00:00Z', 'migration'); |
| 49 | |
| 50 | INSERT OR IGNORE INTO price_changes (id, meter, old_cost_micros, new_cost_micros, markup_percent, reason, created_at) VALUES |
| 51 | ('prc_card_fee_all_cards', 'card_fee_percent', 29000, 29000, 0, 'Stripe''s card fee (2.9% + $0.30) is passed on as its own line on every card payment: the plan, Security and quality, prepaying, AI credit and invoices charged to a card. None on bank transfers or invoiced (enterprise) billing. Prices exclude tax; tax is added where it applies', '2026-10-08T00:00:00Z'); |