SparkBiz — What Each Table Does

This guide explains every table in the SparkBiz database in plain English — no technical background needed. Think of each table as a filing cabinet drawer that stores one specific type of information for your business. Last regenerated 2026-08-27, updated 2026-08-31 (E-Sign templates, automatic bounce/complaint suppression, platform settings), updated 2026-09-04 (Payslip module removed), updated 2026-09-14 (Invoice partial payments — new spkbz_invoice_payments table), updated 2026-09-30 (email footer — new spkbz_footer_logos table + two footer settings on spkbz_tenants) against backend/db/database.py — see also the structural Entity Relationship Diagram.

Quick Summary — 30 Tables, 7 Groups

Core (2 tables) Businesses using SparkBiz, and their login accounts
Invoice module (5 tables) Customers, invoices, line items, payment records, and matched bank transactions
Variations module (5 tables) Building projects, variation requests, optional line items, homeowner approvals, and file attachments
E-Sign module (6 tables) Uploaded documents, signers, the signature/date/checkbox boxes placed on them, plus reusable templates (a saved PDF + role slots + field layout)
Auth & Security (2 tables) One-time password reset and email verification links
Shared / cross-module (8 tables) Saved descriptions, custom email subjects, email-footer logos, E-Request, email-delivery logging, and the automatic block-list for bounced / complained addresses
Admin (2 tables) Audit trail of sensitive admin-panel actions, and platform-wide operator settings
🏢
Core — Every Business on SparkBiz Shared by all modules
These two tables are the foundation. Every other table links back to one of these.
Table: spkbz_tenants
The Business
Like a folder for each company signed up to SparkBiz
One row per business that signs up. Stores everything about the business itself — name, ABN, website, contact details, logo, brand colour and overall colour theme used on PDFs and in the app, bank/PayID/BPAY payment details, and its own timezone (for correctly labelling signing timestamps). Also stores which of the three modules are unlocked, trial end dates, subscription billing details, and per-module preferences like how invoice/variation numbers are formatted and whether GST is added on top, backed out, or not charged at all.
Key things stored
  • Business name, ABN, email, phone, address, website
  • Brand colour and UI colour theme (8 presets)
  • Timezone (for Australia-wide correct AEST/AEDT/AWST timestamps)
  • Bank BSB, account number/name, PayID, BPAY details
  • Three access flags — Invoice, Variations, E-Sign (each is independently on/off; the old "plan" label is now just a display name, not the actual gate)
  • Plan expiry date, trial end dates — one per module
  • Stripe customer & subscription IDs (for billing)
  • Document numbering settings — a custom prefix, digit padding, starting number, and optional year-reset for each module's document numbers
  • Whether Variations numbering should be driven by each project's own Builder reference instead of the shared Variation sequence (on by default)
  • Whether Invoice numbering should be driven by each client's own Customer reference instead of the shared Invoice sequence (on by default)
  • GST handling and pricing-format defaults for Invoice and Variations
  • Email footer settings — whether footer logos sit side by side or one per row, and the footer note (e.g. "HIA Membership No. 123456") shown at the bottom of every customer email
  • Internal admin notes (only SparkBiz owner sees these)
Table: spkbz_users
The Login Account
Like an employee pass — who is allowed to log in
One row per person who logs into SparkBiz. Each user belongs to one business (tenant). Stores their email, encrypted password, whether they've verified their email address yet (required before a self-registered account can log in), and which email method they use to send invoices and other documents (SparkBiz's own email, Gmail, Microsoft, or Custom SMTP). Also stores the OAuth tokens for Gmail/Microsoft, and encrypted SMTP credentials for custom mail servers.
Key things stored
  • Email address and encrypted password
  • Full name, active/inactive status
  • Email verified flag (self-registered accounts can't log in until they click the verification link)
  • Is admin flag (SparkBiz owner/staff only)
  • Gmail connection tokens (if using Gmail to send)
  • Microsoft connection tokens (if using Outlook to send)
  • SMTP server, port, username, from name, encrypted password (if using custom SMTP)
  • Preferred email sending method: sparkbiz / gmail / microsoft / smtp
🧾
Invoice Module Access flag: has_invoice
Covers everything from creating an invoice to tracking when it gets paid.
Table: spkbz_clients
The Customer
Like your address book — who you invoice
Stores details about every customer (client) that the business invoices. A customer is uniquely identified by the combination of their name, email, and company — so the same person working at two different companies creates two separate client records. All text fields are automatically tidied up (extra spaces removed, consistent capitalisation handling).
Key things stored
  • Customer name, email address, phone number
  • Company name and street address
  • Customer reference — optional, free-text; when the tenant-wide "Use each client's Customer reference for numbering" setting is on, this also drives that client's own invoice numbering sequence (see spkbz_tenants above)
  • Internal notes about the customer
  • Active/inactive (deleted customers are kept for history)
Table: spkbz_invoices
The Invoice
Like the actual invoice document in a filing cabinet
One row per invoice. Tracks the full life of an invoice from draft through to paid. Stores the dollar amounts (ex-GST subtotal, GST, and total), key dates, and the current status. Each invoice remembers its own pricing format (quantity × unit price, or a flat amount) and GST mode (added on top, backed out of the price, or none) exactly as it was when created — changing the business's default later never rewrites an already-created invoice. A tenant can also override the email subject line and add extra CC recipients for that one invoice.
Key things stored
  • Invoice number (custom format per business), issue date, due date
  • Status: Draft → Sent → Viewed → Paid (or Overdue / Cancelled)
  • Subtotal (ex-GST), GST amount, total amount
  • Pricing format and GST mode (snapshotted at creation)
  • Custom email subject line and extra CC email addresses, if set
  • Notes, PDF file location
  • Dates: sent, viewed by client, paid, created, last updated
Table: spkbz_invoice_line_items
The Invoice Lines
Like the individual rows on a printed invoice — "2 x Labour @ $500"
Every invoice can have multiple line items — each one is a separate row in this table. For example, one invoice might have three lines: materials, labour, and travel. Each line has its own description, quantity, and unit price. SparkBiz multiplies them together automatically to get the line total.
Key things stored
  • Description of the work or product
  • Quantity (e.g. 3 hours, 2 units)
  • Unit price (ex-GST)
  • Line total (quantity × unit price, calculated automatically)
Table: spkbz_invoice_payments
The Payment Receipts
Like a stack of receipt stubs clipped to the invoice — one per amount actually handed over, however many instalments it took
Built 2026-09-14 so an invoice can be paid across several partial payments (e.g. a $2,000 deposit now, $3,000 later on completion) instead of only ever one all-or-nothing "mark paid." Every payment received — whether it's a small deposit or the final amount that settles the invoice — gets its own row here. SparkBiz adds them up to work out how much of the invoice has actually been paid and how much is still owing; the invoice only becomes fully "Paid" once they add up to the total.
Key things stored
  • Which invoice this payment belongs to
  • The dollar amount received
  • The date it was received
  • An optional note (e.g. "Deposit", "Bank reconciliation")
Table: spkbz_bank_transactions
The Bank Statement Rows
Like importing your bank statement and ticking off which payment matches which invoice
When you upload a bank CSV (from CBA, ANZ, NAB, Westpac, or Macquarie), each transaction row is saved here. SparkBiz then tries to automatically match each bank payment to an outstanding invoice using the amount, date, and description. You review the matches and confirm them — confirmed matches mark the invoice as Paid.
Key things stored
  • Transaction date, description, and dollar amount
  • Which invoice it was matched to (if any)
  • Confidence score of the match (0–100)
  • Whether the match has been confirmed by the user
  • Which bank the CSV came from
🏗
Variations Module Access flag: has_variation
Handles building variation requests — from logging a change through to the homeowner signing off.
Table: spkbz_projects
The Building Project
Like a job folder — one folder per house or construction site
Every variation belongs to a project. A project represents one building job — it holds the site address, the homeowner's contact details, and the builder's own reference number. All variations for that job are grouped under the one project.
Key things stored
  • Project name and site address
  • Homeowner name, email address, and phone
  • Builder's own reference number — optional, and free-text; when the tenant-wide "Use each project's Builder reference for numbering" setting is on, this also drives that project's own variation numbering sequence (see spkbz_tenants above)
  • Status: Active or Completed
Table: spkbz_variations
The Variation Request
Like a change-order form — "we need to add $3,000 for extra drainage"
One row per variation request. Stores what the change is, the original price, the new price, and the difference — either as a single before/after price, or as a full list of priced scope items (see the line items table below). Can be saved as a Draft before sending, or sent immediately. When sent, a unique secret link is generated and emailed to the homeowner so they can approve or decline without needing a login; the link expires after 7 days. A builder can also hide the price from the homeowner-facing page, and can set a custom email subject line for the approval request.
Key things stored
  • Variation number (custom format per business), description of the change
  • Original price, new price, price difference, GST portion of the new price
  • Pricing format (single price vs. itemised) and GST mode (snapshotted at creation)
  • Price display mode — "full" breakdown or "difference only" (snapshotted at creation from the tenant's default); independent of, and overridden by, "hide the price" below
  • Status: Draft → Pending → Approved or Declined (or Cancelled)
  • Secret approval link token and its expiry date
  • Whether the price is hidden from the homeowner
  • Custom email subject line and extra CC email addresses, if set
  • Notes from the builder
  • Date the approval request was first emailed (for the daily activity summary; not changed on a resend)
Table: spkbz_variation_line_items
The Variation Scope Breakdown
Like an itemised quote instead of one lump-sum figure
Optional. When a variation uses the "itemised" pricing format instead of a single before/after price, each priced item in the scope of work gets its own row here — with both a before-price and an after-price per line, so the homeowner can see exactly what changed item by item, not just the bottom-line total.
Key things stored
  • Description of the item
  • Quantity
  • Unit price before and after the change
  • Line total before and after (calculated automatically)
Table: spkbz_variation_attachments
The Supporting Files
Like photos or documents clipped to a change-order form
Stores files attached to a variation — photos of the site, drawings, or PDFs that support the change request. Up to 5 files per variation (max 3 MB total). Files are sent to the homeowner with the approval request email, and kept on file permanently afterward regardless of whether the variation is approved, declined, or cancelled.
Key things stored
  • Original filename and file type (JPEG, PNG, WebP, PDF, Word)
  • A reference to the file's location in secure cloud storage (the actual file contents live there, not in the database itself)
  • Date and time the file was uploaded
Table: spkbz_variation_approvals
The Homeowner's Decision
Like a signed approval form — proof that the homeowner said yes or no
Created the moment a homeowner clicks Approve or Decline on their email link. This is the legal audit trail — it records exactly who approved it, when, and from which device. On approval, a signed work-order PDF is automatically generated and emailed to both the builder and the homeowner as proof of agreement; on decline, a reason can be recorded.
Key things stored
  • Decision: Approved or Declined
  • Exact date and time of the decision
  • Homeowner's IP address (for dispute resolution)
  • Device/browser information (audit trail)
  • Homeowner's name as entered at approval time
  • Reason given, if declined
✍️
E-Sign Module Access flag: has_esign
A scoped DocuSign — upload any existing PDF and get one or more people to sign it electronically.
Table: spkbz_esign_documents
The Document
Like the folder holding both the blank contract and the fully signed one
One row per uploaded document — a tax return, contract, or any PDF that needs signing. The original uploaded PDF and the final signed PDF (with everyone's signatures flattened in, plus an appended certificate-of-completion page) are both kept in secure cloud storage permanently, even after the document is fully signed. Tracks status from draft, through sent, to completed (everyone signed) or declined (any one signer said no).
Key things stored
  • Document number (custom format per business), title, original filename
  • Reference to the original and final signed PDF in cloud storage
  • Page count, status: Draft → Sent → Completed or Declined (or Cancelled)
  • Reason given, if declined
  • Custom email subject line for the send-invite email, if set
  • Dates: sent, completed, declined, cancelled, created
Table: spkbz_esign_recipients
The Signer
Like each individual's own copy of the contract, with their own signing link
One row per person attached to a document. Most are signers — each gets their own secret link (valid 30 days) to view and sign only their own fields, without a login. A recipient can instead be marked "receives a copy" (CC): no link, no fields, nothing to sign — they're just emailed the final signed PDF once everyone has signed. By default signers can sign in any order; a document can optionally require signing sequence groups (e.g. builder and architect first, then homeowners, then the bank) — a signer in a later group can't sign until their group activates. Records the signer's consent, IP address, and device for the audit trail.
Key things stored
  • Name, email address, colour used to identify them in the designer
  • Role: signer, or "receives a copy" (CC)
  • Status: Pending → Viewed → Completed (or Declined)
  • Secret signing link token and its expiry date
  • Optional signing sequence-group number, and when that group became active
  • Consent, viewed, completed, declined timestamps
  • IP address and device/browser information (audit trail)
  • Reason given, if declined
Table: spkbz_esign_fields
The Signature Boxes
Like the little sticky "sign here" tabs stuck on a paper contract
One row per box placed on the document — a signature, initials, date, free text, or checkbox, positioned on a specific page for one specific signer. Checkboxes can optionally be grouped (e.g. a Yes/No pair, or a "tick all that apply" checklist) so the form can require at least one box in the group to be checked without forcing every single one. Once a signer fills a box, the captured value (a signature image, typed text, or a checked mark) is saved here.
Key things stored
  • Field type: signature, initials, date, text, or checkbox
  • Which page, and its position/size on that page
  • Whether it's required before the signer can finish
  • Optional checkbox group name, and whether that group is pick-one or pick-any
  • The captured value once filled in, and when
Table: spkbz_esign_templates
The Reusable Template
Like a rubber stamp of a whole document setup — the PDF, the "sign here" tabs, and who signs where — ready to reuse
A saved E-Sign setup for a document a business sends repeatedly to different people — e.g. the same engagement letter to each new client. It holds its own copy of the PDF, a set of placeholder "roles" (people slots), and the full field layout. Created only by choosing "Save as template" on an existing document; using it produces a fresh draft document with the roles ready to be filled in with real names and emails.
Key things stored
  • Template name and optional description
  • Its own reference to the stored PDF in cloud storage, and the page count
  • An optional default email subject line for documents made from it
  • Date created and last updated
Table: spkbz_esign_template_roles
The Template's People Slots
Like the blank name lines on a form — "Signer 1", "Home Owner", "Bank" — filled in later
One row per placeholder person on a template. Carries everything about a recipient except the actual name and email (which are supplied when a real document is created from the template) — whether they sign or just receive a copy, their colour in the designer, their order, and their signing sequence group if any.
Key things stored
  • The role's label (e.g. "Home Owner")
  • Whether this slot is a signer or a "receives a copy" recipient
  • Identifying colour, display order
  • Optional signing sequence-group number
Table: spkbz_esign_template_fields
The Template's Signature Boxes
Like the "sign here" tabs drawn on the template, waiting to be assigned to whoever fills each slot
One row per box placed on a template — identical in shape to a normal signature box (type, page, position, size, required, checkbox grouping), except it points at a template role slot rather than a specific person. When a document is created from the template, each of these becomes a real signature box owned by the recipient assigned to that slot.
Key things stored
  • Field type: signature, initials, date, text, or checkbox
  • Which role slot it belongs to
  • Which page, and its position/size on that page
  • Whether it's required, and any checkbox grouping
🔐
Security Behind the scenes
Keeps accounts secure. Users never see these tables directly.
Table: spkbz_password_reset_tokens
The "Forgot Password" Link
Like a temporary key that self-destructs after use
When a user clicks "Forgot password", SparkBiz generates a unique secret code, saves it here, and emails it as a link. When the user clicks the link, the code is checked against this table. It only works once and expires after 1 hour — after that it's permanently marked as used and can never be replayed.
Key things stored
  • The secret one-time code (long random string)
  • Which user requested the reset
  • Expiry date and time (1 hour)
  • Whether it has already been used
Table: spkbz_email_verification_tokens
The "Confirm Your Email" Link
Like a temporary key proving you own the email address you signed up with
When someone self-registers for SparkBiz, they can't log in until they click a verification link sent to their email — this proves the address is real and theirs. Works the same way as the password reset token, just with a longer, 24-hour window since signing up isn't as time-sensitive as regaining account access. Accounts created directly by an admin skip this entirely, since the admin already vouches for the address.
Key things stored
  • The secret one-time code (long random string)
  • Which user needs to verify
  • Expiry date and time (24 hours)
  • Whether it has already been used
🔗
Shared / Cross-Module Utilities used across modules
Not tied to one single module — used to support features that span Invoice, Variations, and E-Sign.
Table: spkbz_item_descriptions
Saved Line-Item Descriptions
Like autocomplete suggestions remembering what you typed before
A personal library of reusable line-item descriptions the business has typed before — suggested as autocomplete when adding a new invoice or variation line. Invoice and Variations each keep their own separate library, so a description saved on an invoice never suggests itself on a variation.
Key things stored
  • The saved description text
  • Which module it belongs to (invoice or variation)
  • Date first saved
Table: spkbz_email_subject_overrides
Custom Email Subject Lines
Like editing the default subject line template once, for every future email of that type
Lets a business set its own default subject line for any of the system-triggered emails (invoice sent, invoice paid, variation approval request/confirmation/decline, E-Sign send/"your turn") instead of the built-in wording — using placeholders like the invoice number or business name that get filled in automatically when the email actually sends. This is the tenant-wide default; a specific invoice, variation, or document can still override it for just that one send.
Key things stored
  • Which email type this applies to
  • The custom subject line template (with placeholders)
  • Date created and last updated
Table: spkbz_footer_logos
Email Footer Logos
Like the row of membership badges printed at the bottom of a company letterhead
Extra logos a business adds to the signature at the bottom of every email it sends to customers — for example HIA, Master Builders or licence badges — shown after the business's own logo and footer note. Each business can have up to six. The images are stored here and embedded directly into each outgoing email, so they display without the reader having to click "download pictures". Whether they sit side by side or one per row is a setting on the business itself (spkbz_tenants).
Key things stored
  • Which business it belongs to
  • The logo image itself
  • Display order
  • Date added
Table: spkbz_request_templates
E-Request — Saved Templates
Like a folder of form-letter drafts ready to send to anyone
Reusable subject + message templates for the E-Request feature — a way to send a free-text email (a checklist, a form request, a reminder) to a batch of customers or homeowners at once. A template isn't tied to one module; the same one can be reused to message any audience, and its text stays editable at send time.
Key things stored
  • An internal label for the template
  • Subject line and message body
  • Date created and last updated
Table: spkbz_request_logs
E-Request — Send History
Like a sent-mail folder showing who you've already messaged this year
One row per E-Request actually sent, so the business can see who they've already contacted (and when) before sending the same request again — e.g. avoiding messaging the same customer twice for an annual document request.
Key things stored
  • Which module the recipient came from (invoice or variation)
  • Recipient's name and email address at the time it was sent
  • Which saved template was used, if any
  • Subject line sent, and the date/time
Table: spkbz_email_send_log
Which Provider Actually Sent This Email
Like a delivery receipt showing which courier company handled a parcel
SparkBiz's default email sending uses AWS SES, and automatically falls back to a second provider (Resend) if SES can't deliver. This table records which one actually handled each send for invoice, variation, and e-sign emails, so the business (or SparkBiz support) doesn't have to check two separate dashboards to see what happened to a particular email.
Key things stored
  • Which module and recipient the email was for
  • Subject line
  • Which provider handled it (SES or Resend)
  • Whether it succeeded, and the error message if it didn't
  • Date and time sent
Table: spkbz_ses_events
Bounce & Complaint Log
Like a "returned to sender" or "marked as spam" notice from the post office
When AWS reports that an email bounced (bad address, full inbox, etc.) or was marked as spam by the recipient, that raw report is logged here. It's the underlying event feed — what SparkBiz then does with it (blocking that address) is tracked in the separate block-list table below.
Key things stored
  • Event type: bounce or complaint, and its subtype
  • The affected recipient's email address
  • Which business/module it was likely for (best-effort match, not always known)
  • The full original report, for troubleshooting
  • Date and time received
Table: spkbz_suppressed_emails
The Bounced / Complained Block-List
Like a "do not post to this address" note, added automatically after mail comes back undelivered
When an address hard-bounces, is marked as spam, or soft-bounces repeatedly (3 times), SparkBiz automatically stops sending to it — every send path checks this table first. The business is emailed a heads-up, and can see a warning badge next to that customer or homeowner and clear the block themselves once the address is fixed (a spam-complaint block needs an explicit "I confirm they want our emails" tick to clear). Cleared entries are kept as history, not deleted. Editing a contact's email address clears a bounce block on the old one automatically.
Key things stored
  • The blocked email address and which business it belongs to
  • Reason: hard bounce, spam complaint, or repeated soft bounces
  • Whether the block is currently active, and the soft-bounce count so far
  • A link back to the bounce/complaint event that caused it
  • When it was created, and if/when/by whom it was cleared
🕵️
Admin SparkBiz staff only
Not visible to any business — used internally to keep the admin panel accountable.
Table: spkbz_admin_audit_log
Who Did What
Like a security camera log for the admin panel
Records every sensitive action taken from the SparkBiz admin panel — impersonating a business's account, editing a tenant's plan or details, suspending/reactivating accounts (including bulk actions on many at once), and sending mass emails to tenants — along with which admin did it and exactly what changed, so there's a record to look back on.
Key things stored
  • Which admin performed the action (their email, snapshotted)
  • What kind of action it was
  • Which business it affected, if any
  • A plain-text description of what changed
  • Date and time
Table: spkbz_platform_settings
Operator Settings
Like a small settings page for SparkBiz itself, not for any one business
A simple name/value list of platform-wide settings the SparkBiz operator can change from the admin panel without a code change — currently: which address gets operator emails, whether to send a heads-up when a new account verifies, and whether to send the daily activity summary email. Not tied to any business.
Key things stored
  • A setting name (key) and its value
  • When it was last changed

🔗 How the Tables Connect to Each Other

Invoice flow

Business → has many Customers
Customer → receives many Invoices
Invoice → has many Line Items (the rows)
Invoice → can be matched by a Bank Transaction

Variations flow

Business → has many Projects
Project → has many Variations
Variation → optionally has many Line Items (itemised scope)
Variation → has many Attachments (files sent with the request)
Variation → has one Approval (once homeowner responds)

E-Sign flow

Business → has many Documents
Document → has many Signers (independent, in signing groups, or "receives a copy")
Document → has many Signature Boxes
Signer → owns the Signature Boxes assigned to them
Business → has many Templates → each has Role Slots & Template Boxes

Security flow

Business → has many Users (login accounts)
User → can request a Password Reset Token
User → gets an Email Verification Token when self-registering

Shared / cross-module

Business → has its own Saved Descriptions, Email Subject Overrides, Email Footer Logos, E-Request Templates & Send History, email delivery/bounce logs, and its bounced-address block-list — each independent of one another, none tied to a single module

SparkBiz · Internal documentation · All data is isolated per business — no business can see another's data.