Architecture, Schema & Cost Model

Status: agreed for MVP · Last updated: 10 June 2026

Scope: trade services app: quoting, invoicing, expenses, and tax estimate. Decisions here are settled unless superseded; rationale lives in the build guide.

Stack

Layer	Choice	Notes
Tradesperson app	React Native + Expo (TypeScript)	EAS builds, OTA updates. Native needed for camera, push, share sheet
Client-facing pages	Plain web app (React/Next.js)	Quote view, accept, pay. No login, link-based access only
Backend logic	Python Azure Functions	PDF generation, receipt extraction, Stripe webhooks, chase scheduler
Database / auth / storage	Supabase (Postgres)	App talks to Supabase directly for auth + CRUD under RLS
Payments	Stripe Payment Links	GoCardless / open banking later for large invoices
Receipt extraction	Claude API (Haiku 4.5), vision	Confirm-not-type UX; no OCR pipeline built in-house
Chase messages	Email (Resend/SES) -> SMS (Twilio) escalation	WhatsApp Business API deferred
Push notifications	Expo push	Free

Principle: Functions exist only for work needing server-side trust or compute: secrets, signatures, rendering. Plain CRUD goes app -> Supabase under RLS. The MVP backend is roughly five functions.

Architecture

• Expo app -> Supabase directly: auth, quotes, items, expenses CRUD.
• Expo app -> Functions for PDF/OCR actions.
• Client web page -> locked-down security definer functions only: get_quote_by_token, accept_quote_by_token, decline_quote_by_token (quote link); get_invoice_by_token, choose_invoice_payment_by_token (invoice link), plus one endpoint that creates the Stripe Checkout session for card payment.
• Stripe -> webhook (signed) -> record_stripe_payment -> payments insert -> invoice status.
• Nightly timer trigger -> overdue query -> chase messages.
• Serverless + Postgres: always connect via Supabase pooler (Supavisor) or HTTP interface; never raw connections per invocation.

Schema conventions

• All money is integer pence. Format to pounds at the edge only. Stripe speaks minor units.
• profiles.id = auth.users.id, created by signup trigger. RLS everywhere is profile_id = auth.uid() (child tables via exists against parent).
• Offline sync depends on updated_at for incremental pulls and deleted_at for tombstones. New owner-owned app tables must include both from the start, with an updated_at trigger. Hard deletes are not a valid app-level delete shape unless a table is explicitly outside the offline mirror.
• Quote totals are snapshotted on send. quote_items are source of truth while drafting; on send, freeze total_pence and the rendered PDF. Accepted prices never drift.
• public_token (32 hex chars, unique) is the client’s only access path. No anon RLS policies on tables; anon role can execute the two definer functions and nothing else.
• payments.stripe_event_id unique = webhook idempotency. Manual payments (cash/bank transfer) are payment rows with method = 'manual' and a null event id. The constraint must be nullable-unique; see follow-up migration.
• cis_deduction_pence is a stored amount, not a flag: 20% registered, 30% unregistered, 0% gross. CIS applies to labour only, never materials; per-line kind enables the split.
• rate_cards model multiple rates (trades/workers) per business: label, day/hour unit, pence. quote_items.rate_card_id is a convenience reference; unit_price_pence remains the snapshot. Soft-delete via active. Rates are not team members; multi-user is out of scope.
• expenses.category is locked to self-assessment categories from day one: materials, tools, vehicle, phone, insurance, other. The accountant export is then a single query.
• reminders logs every chase: invoice, channel, sent_at. This prevents double-sends and shows history in the UI.
• Job photos: photo_paths jsonb on quotes pointing at Supabase storage.
• rebates carry one row per quote or invoice (XOR quote_id/invoice_id, unique per parent). amount_pence is authoritative; percent_bp is metadata for percentage rebates. Reason is a fixed enum (loyalty, volume, materials, goodwill, other) plus optional free-text note. Snapshotted into parent total_pence at send time. Use rebates when the reduction has a reason worth reporting on; update_invoice_draft_total remains for pure total corrections (materials estimate typo, etc.).
• Rebate + CIS ordering (deferred to RM-6, fixing the rule now to avoid ambiguity later): rebate applies first, then CIS on top. total_pence = subtotal_pence - rebate.amount_pence - cis_deduction_pence. CIS is still computed against the labour portion of the underlying quote items, not the rebated total — the rebate is a discount at the invoice level, not a per-line unit-price change. Reporting: gross revenue = subtotal, discounts = rebate, taxable turnover = subtotal - rebate.

Tables: profiles, rate_cards, clients, quotes, quote_items, invoices, payments, rebates, expenses, reminders. Full DDL: supabase/migrations/20260609000000_init.sql + follow-up migrations.

State machine

Quote: draft -> sent -> accepted | declined (expired derived, voided owner-initiated).

Invoice: draft -> sent -> paid | void. Payment is decoupled from acceptance. Accepting a quote takes no money and creates no invoice — it is a plain terms agreement. When the work is done the tradesperson raises an invoice off the accepted quote(s); the client then chooses how to pay (card or cash) on the invoice link.

Rules:

1. Each transition has exactly one writer: draft -> sent (app, RLS); sent -> accepted / declined (anon definer functions accept_quote_by_token / decline_quote_by_token, no payment side effects); invoice creation (create_invoice_draft, app, owner-only); draft -> sent (send_invoice, app); sent -> paid (Stripe webhook via service role, or record_manual_payment for cash); void (app). Rebates use draft-only writers on both parents: apply_quote_rebate / remove_quote_rebate / apply_invoice_rebate / remove_invoice_rebate; send_quote and send_invoice re-snapshot totals including the rebate. One writer per transition means no races and trivial audit.
2. The client’s payment-method choice lives on the invoice (invoices.payment_method), set by choose_invoice_payment_by_token at pay time — never on the quote. card redirects to Stripe Checkout; cash flags intent so the tradesperson knows to collect and record it.
3. expired is derived, not stored. accept_quote_by_token refuses past valid_until, which is the only place correctness matters. Display layers report expired when valid_until < current_date. Reviving a quote = updating valid_until.
4. overdue is derived (status = 'sent' and due_date < now()), never stored. Partial index idx_invoices_overdue serves the nightly chaser.
5. Paid and void are terminal. Nothing un-accepts a quote: buyer’s remorse before payment is a voided invoice plus a conversation, keeping accepted_at clean as terms-acceptance evidence.
6. Webhook is the only source of truth for card-paid. Stripe redirect pages render optimistically but never write state; redirects can be forged, webhooks are signed. Success page may poll briefly for the payment row.

Client page states

Quote link (/q/:token, get_quote_by_token):

1. Open quote -> accept or decline. Neither takes payment.
2. Accepted -> confirmation: “you’ll be invoiced when the work’s done”. Shows paid later if the eventual invoice settles.
3. Declined -> acknowledgement + nudge to ask for a revised quote.
4. Expired -> one-tap “ask for updated quote” re-engagement, inserting a notification and pushing to the tradesperson.

Invoice link (/i/:token, get_invoice_by_token):

1. Sent, outstanding -> choose Pay by card (Stripe Checkout) or Pay in cash (choose_invoice_payment_by_token('cash'), then “hand cash over” guidance).
2. Paid -> confirmation.

Payment routes

Payment happens at the invoice stage, after the work is done. At that point several routes are possible:

1. Card (Stripe Checkout) — client taps “Pay by card” on the invoice link; webhook is the source of truth (payments row with signed stripe_event_id).
2. Material cost adjustment — material costs changed between quote and completion (prices rose, scope changed, etc.); the tradesperson edits the draft invoice total before sending. The new total is what the client sees and pays.
3. Cash (or bank transfer) payment — client chooses “Pay in cash” / pays offline; tradesperson records a manual payment row (method = 'manual', null stripe_event_id) via record_manual_payment. No Stripe Checkout session is created.
4. Combination — part cash / part card, or a cost adjustment settled partly offline. Each settlement leg is its own payments row; the invoice moves to paid once the sum of confirmed payment rows covers total_pence.

Cost model

June 2026 prices.

Marginal cost per active user/month, assuming roughly 15 quotes, 25 invoices, 50 receipt scans, and 12 SMS:

Component	Per user / month
Claude API receipt extraction (Haiku 4.5, $1/$5 per MTok)	~£0.10
Twilio UK SMS (~$0.046/msg)	~£0.45
Email	~£0.01
Azure Functions	~£0 (free grant covers thousands of users)
Supabase share ($25/mo flat)	Pennies, shrinks with scale
Total marginal	~£0.55-0.70

Fixed floor is roughly £30-35/month: Supabase Pro, Apple Developer, Twilio number, domain, ICO registration. Three paying users cover it.

Per receipt scan: image roughly 1,050 tokens (resized 768 x 1024) + roughly 200 prompt + roughly 80 output = about 0.13p. Resize client-side to roughly 1024px long edge. Unresized photos hit the API cap around 1,600 tokens, roughly 0.18p, and upload slower. Caching/batch is not worth it at these sizes; model choice is an accuracy decision, not cost. Sonnet is roughly 0.4p/receipt.

Stripe fees, roughly 1.5% + 20p for UK cards, come out of the tradesperson’s payout, never our margin - standard across competitors. Open banking for large invoices is a “keep more of your money” feature.

SMS is the only behaviour-scaled cost: chase ladder is email -> email -> SMS, with a cap on automatic SMS per invoice.

Follow-up migration

Before client page ships:

1. Extend get_quote_by_token to return deposit invoice status + Stripe link; page routes states 2/3 off the invoice.
2. Make get_quote_by_token report expired when valid_until has passed. Stored status stays sent.
3. Loosen payments.stripe_event_id to nullable + unique for manual payments.