The data model and build plan for a multi-tenant, resellable HVAC platform: tenancy & roles, recovery, the entity model, QuickBooks & payment terms, device fleet, auth & privacy, notifications & consent, tax, compliance records, the lead pipeline, reporting, file storage, testing, and phasing.
Decisions already made (council + owner) that the whole model serves.
Four levels. Every record below the platform carries a corporation_id (and, where it applies, a site_id) — the isolation key the whole system enforces.
Isolation rule: a query for one corporation can never return another corporation's rows — enforced at the datastore layer (row-level security on corporation_id), not just in app code. The only actor crossing the corporation boundary is the Super-Admin (the provider), and every such access is audited. Isolation model (confirmed Jun 2026): all tenants share one Postgres database, isolated by RLS on corporation_id — startup and year-1 cost match every other model (~$300/yr) and stay flat as tenants are added; schema-per-tenant adds ops overhead for no saving, and physical project-per-tenant (the only model that costs more) is reserved for a future customer whose compliance contract demands and funds it.
Shared vs. isolated within a corporation: operations are isolated per site; reference data and reporting are shared at the corporation.
Seats: each User holds Membership rows (corporation/site + role). Active memberships = billable seats, metered per corporation.
Six roles. Super-Admin is the provider (not a customer). Corp Admin is the customer's top role, corp-wide. Site Admin runs one site only.
| Capability | Super-Admin (provider) | Corp Admin | Site Admin | Office | Tech | Bookkeeper |
|---|---|---|---|---|---|---|
| Cross-corporation access / impersonate | ✓ | — | — | — | — | — |
| Manage seats, billing, devices, QBO connection | ✓ all | ✓ own corp | — | — | — | — |
| Create / manage sites | ✓ | ✓ | own site | — | — | — |
| Manage users & roles | ✓ | ✓ corp | site | — | — | — |
| Catalog & vendor pricing | ✓ | ✓ | request | — | — | — |
| Inventory: receive / adjust / transfer | ✓ | ✓ | ✓ | ✓ | use on job | — |
| Run jobs (Evaluator / Sidekick) | ✓ | ✓ | ✓ | ✓ | ✓ | — |
| Schedule / dispatch · leads | ✓ | ✓ | ✓ | ✓ | own only | — |
| Invoices & QuickBooks | ✓ | ✓ | site | create | — | ✓ |
| Restore / revert / audit | ✓ cross-tenant | own corp | — | — | — | — |
| Reporting scope | platform | corp roll-up | own site | limited | own | financial |
Recovery is split by who broke it and who fixes it.
deleted_at/deleted_by rather than being removed. Corp Admin restores within their corp; Super-Admin restores anywhere.AuditEvent: actor, role, corporation/site, action, entity, before/after, timestamp, and whether under impersonation. Retention: ~2 years hot, then archived (recoverable, cheaper storage).Grouped by domain. Scope: Platform Corp Site Device/local. Every Corp/Site entity implicitly carries corporation_id, soft-delete and audit fields.
| Entity | Scope | Key fields |
|---|---|---|
| Corporation | Platform | name, legalName, status, plan, qboConnectionId, cardSurchargePolicy (absorb | surcharge%, state-capped), priceBookId, mfaPolicy |
| Site | Corp | corporationId, name, address, phone, license, timezone |
| User | Platform | email, name, authId, status, mfaEnrolled |
| Membership (seat) | Corp | userId, corporationId, siteId?, role, active (billable) |
| Device | Corp | corporationId, assignedSiteId, assignedUserId, name/assetTag, corporationOwned (bool), status (active/lost/retired), trackingEligible, trustedForMfa, appPin |
| AuditEvent | Corp | actorUserId, role, siteId?, action, entity, before, after, impersonated, at |
| Entity | Key fields |
|---|---|
| Item (part/material) | sku, name, category, type, unit, defaultCost, defaultPrice, manufacturer, mpn |
| Service / price-book entry | name, flatPrice?, laborHours?, linkedItems[], taxCategory |
| Vendor / VendorPrice | vendor: name, contact, terms · price: vendorId, itemId, cost, leadTimeDays |
| Entity | Key fields |
|---|---|
| StockLocation | siteId, kind (shop / warehouse / truck), name, assignedUserId? (truck) |
| StockLevel | locationId, itemId, qtyOnHand, qtyReserved |
| StockTxn | type (receive/use/transfer/adjust/return), itemId, from/toLocationId, qty, jobId?, userId, at |
| ReorderRule · PurchaseOrder/POLine | min/reorder levels · vendorId, siteId, status, lines, receivedAt |
| Entity | Scope | Key fields |
|---|---|---|
| Customer | Corp | name, contacts[], billingAddress, parentCustomerId? (PM sub-customers), customerType, defaultTerms, taxStatus, poRequired, notToExceed, depositRule, qboCustomerId, deletable PII flag |
| Property | Corp | address, equipment[] — survives PII deletion; re-associates to next owner |
| Job | Site / local first | siteId, customerId, propertyId, type, techId, status, components[], readings, photos, safety |
| Proposal / Agreement (Evaluator) | Site | jobId, recommendation, pricing, signedAt |
| RepairRecord (Sidekick) | Site | jobId, diagnosis, partsUsed[→StockTxn], serviceCharge, repairTotal, signOff |
| ServiceAgreement | Corp | customerId, plan, cadence, includedVisits, price, autoRenew (default on), renewalNoticeAt, nextVisitDue |
| WarrantyRecord | Corp | propertyId, equipmentId, kind (equipment/labor), start, end, terms — retained |
| Callback | Site | originalJobId, returnJobId, reason — quality metric |
| Entity | Key fields |
|---|---|
| Appointment | siteId, customerId, propertyId, jobId?, window, type, status |
| Assignment | appointmentId, techUserId, status (offered/accepted/en-route/onsite/done) |
| TimeEntry | jobId, userId, clockIn, clockOut, gpsStampIn/Out, geofenceArrival? — feeds payroll & job costing |
| LocationBreadcrumb | userId, deviceId, points[], shift — company-owned devices only, on-shift, short retention, opt-in |
| Entity | Key fields |
|---|---|
| Invoice | siteId, customerId, jobId, lines[], terms, poNumber?, depositApplied?, taxAmount (from tax service), surchargeLine? (credit, disclosed), total, status (draft/sent/paid/overdue), qboInvoiceId |
| Deposit / retainer | customerId, amount, status (held/applied/refunded), qboDepositItemId — netted on final invoice |
| Payment (read-back) | invoiceId, amount, method, paidAt, qboPaymentId — from QuickBooks confirmation, not in-app |
| QuickBooksConnection · SyncLog | per corp: realmId, OAuth tokens, lastSyncAt · sync entity/direction/result |
| StateCardRule Platform | state, surchargeAllowed, maxSurchargePct, requiresDisclosure — platform-maintained reference |
| Entity | Scope | Key fields |
|---|---|---|
| ConsentRecord | Corp | customerId, channel (sms/email), purpose (transactional/marketing), grantedAt, wordingShown, revokedAt? |
| NotificationLog | Corp | customerId, channel, template, sentAt, status, provider |
| Lead | Corp | source (website/email-forward/facebook/instagram/ai/referral/manual), referrerId?, raw, contact, stage (new→contacted→quoted→won/lost), lostReason?, wonJobId? |
| Referrer · ReferralAgreement | Corp | referrer: person/partner, contact · agreement: type (flat/%/credit), trigger (on close/on paid) |
| ReferralPayout | Corp | referrerId, leadId, amountOwed, status (accrued/paid), paidAt |
| MediaFile | Corp | ownerEntity, storageKey, contentType, size, signedAccess — object storage, per-tenant |
| RefrigerantLog · LicenseInsurance | Site | see §11 Compliance records |
QuickBooks Online is the system of record for accounting (per-tenant OAuth). The suite is the system of record for the field work that feeds it — and for getting a complete, correctly-termed invoice out the door at the call.
defaultTerms map 1:1 to a QuickBooks Term. Same price book for everyone — the terms vary, not the number.
| Customer type | Typical terms | QBO term | At the call |
|---|---|---|---|
| Residential | Due on completion, or deposit-to-reserve + balance | Due on receipt | Collect now (link / card-on-file) |
| Property manager / multi-unit | Net 10 / 15 / 30 | Net 10/15/30 | Finalize + capture PO |
| Commercial | Net 30 / 45 / 60 | Net 30/45/60 | Finalize + capture PO / authorization |
taxStatus, poRequired, notToExceed ride on the profile; a tech can't exceed an NTE without captured authorization.
Residential deposit-to-reserve is taken at booking as a QBO deposit/retainer item; the final invoice nets the deposit and bills the balance, with refundability disclosed. A property manager is a parent customer with a sub-customer per property/unit (mirroring QBO), with optional consolidated monthly statements.
Finalizing the invoice is the gate to mark a job Done. Lines auto-assemble: parts from Quartermaster usage + flat-rate price book, terms preset by customer type, tax from the tax service (§10).
Two-way customers & items (incl. PM sub-customers); invoices pushed with terms/PO/deposit/tax/surcharge; payments collected in QBO and confirmed back (webhook/poll) to flip the invoice paid. Each corporation chooses card cost handling: absorb (in the price book) or surcharge a disclosed % line on credit only — applied as the lower of corp %, state cap (StateCardRule), and processor cost-of-acceptance; never debit; disclosed up front; auto-suppressed where disallowed.
StateCardRule is a platform-maintained reference to keep current and verify against law, card-network rules, and the processor's program.Company-owned devices are corp assets, managed at the corporation level.
corporationOwned, trackingEligible, trustedForMfa, appPin.corporationOwned === true — a personal phone can never enable it, even if the corp toggle is on.Sales-tax rates vary by state/county/city/special district, and HVAC has labor-vs-parts and repair-vs-new-construction quirks. We integrate a tax service (Avalara / TaxJar) rather than hand-maintain tables:
Every lead lands in one pipeline tagged with its source, tracked to won/lost, with referral payouts owed when a tagged lead closes. (Merges the former "CRM" and "referral attribution" items.)
leads@ inbox anyone forwards to — AI parses sender, forwarder, content into a Lead), social/AI (Facebook, Instagram, ChatGPT, other — most arriving via email or web form), manual.source and, if referred, a referrer.The numbers that prove the suite pays for itself and run the business — DSO, close rate, revenue per tech, AR aging, callback rate, plan attach/renewal, and job costing (labor from TimeEntry + parts from inventory vs. revenue). Scope rolls up Site → Corp Admin (corp-wide) → Super-Admin (cross-tenant platform metrics). Lands late (needs money + time + inventory data flowing first).
corporation_id and RLS forbids cross-tenant reads. Treated as a safety/integrity property, not just a query filter — cross-tenant bleed is a liability event, so RLS ships with a real isolation test (tenant A cannot see tenant B). Helper functions resolve the caller’s corporation + role; Super-Admin (provider) is the only cross-tenant actor and every access is audited; impersonation requires consent/cause + audit + a visible banner.Smallest correct step first; each phase shippable. Testing/environments, notifications/consent, and file storage are cross-cutting from Phase 1.
| Phase | What ships |
|---|---|
| 0 — Interface layer | Shared core.css/core.js + app shell + auth/role gate; apps converted |
| 1 — Tenancy spine | Corp / Site / User / Membership / Audit; device fleet; auth, MFA, privacy/retention; object storage; Super-Admin console; soft-delete+restore |
| 2 — Quartermaster | Catalog, vendors, stock locations/levels/txns, reorder; Sidekick part-use decrement; compliance records (refrigerant, license/insurance) |
| 3 — Scheduling | Appointments, assignments, dispatch; time tracking + GPS clock/geofence; continuous breadcrumb (corp device); customer comms (reminders / on-the-way); recurring agreements (auto-renew) |
| 4 — Money / QuickBooks | Per-corp QBO; customers/items/invoices sync; terms, deposits, surcharge; tax service; payment read-back; customer portal (pay/approve/history); review-ask comms; warranty + callback |
| 5 — Pipeline & reporting | Lead pipeline (email-forward + web first) + referral payouts; analytics/reporting (Site → Corp → platform) |
Backlog: financing; named social-channel integrations beyond email/web form.
StateCardRule, per-state disclosure wording, processor program alignment (verify against current law — not legal advice).fe: → site/corporation adoption on first sync (Empower = corp #1).