g1t/services/billing/migrations/0043_tax_and_card_fees.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Merge Stripe Tax, the card fee on card payments, and one free workspace per person | 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'); |
This file's history is long; its oldest lines are credited to the oldest commit read.