SparkBiz — Database Entity Relationship Diagram

erDiagram %% ── CORE / MULTI-TENANT ──────────────────────────────────────────────── TENANTS { string id PK string business_name string abn string email UK string phone text address string website text logo_url string brand_color string ui_theme string email_footer_logo_layout string email_footer_note string timezone string bsb string account_number string account_name string payid string bpay_biller string bpay_ref string plan datetime plan_expires_at datetime trial_invoice_until datetime trial_variation_until datetime trial_esign_until boolean has_invoice boolean has_variation boolean has_esign string stripe_customer_id string stripe_subscription_id string invoice_line_item_mode string invoice_gst_mode string variation_gst_mode string variation_line_item_mode text admin_notes boolean is_active datetime suspended_at datetime created_at } USERS { string id PK string tenant_id FK string email UK string hashed_password string full_name boolean is_active boolean is_admin boolean is_verified string email_send_method text gmail_access_token text gmail_refresh_token datetime gmail_token_expiry string gmail_connected_email text microsoft_access_token text microsoft_refresh_token datetime microsoft_token_expiry string microsoft_connected_email string smtp_host integer smtp_port string smtp_username text smtp_password_enc string smtp_from_name datetime created_at } TENANTS ||--o{ USERS : "has" %% ── INVOICE MODULE ────────────────────────────────────────────────────── CLIENTS { string id PK string tenant_id FK string name string email string phone text address string company text notes boolean is_active datetime created_at } INVOICES { string id PK string tenant_id FK string client_id FK string invoice_number string status datetime issue_date datetime due_date numeric subtotal numeric gst_amount numeric total_amount string line_item_mode string gst_mode string email_subject string cc_emails text notes text pdf_url datetime sent_at datetime viewed_at datetime paid_at numeric paid_amount datetime created_at datetime updated_at } INVOICE_LINE_ITEMS { string id PK string invoice_id FK string description numeric quantity numeric unit_price numeric amount } INVOICE_PAYMENTS { string id PK string invoice_id FK numeric amount datetime paid_at string note datetime created_at } BANK_TRANSACTIONS { string id PK string tenant_id FK datetime transaction_date text description numeric amount string bank_reference string source string matched_invoice_id FK integer match_confidence boolean is_confirmed datetime created_at } TENANTS ||--o{ CLIENTS : "has" TENANTS ||--o{ INVOICES : "has" TENANTS ||--o{ BANK_TRANSACTIONS : "has" CLIENTS ||--o{ INVOICES : "billed via" INVOICES ||--o{ INVOICE_LINE_ITEMS : "contains" INVOICES ||--o{ INVOICE_PAYMENTS : "settled by" INVOICES ||--o| BANK_TRANSACTIONS : "matched by" %% ── VARIATIONS MODULE ─────────────────────────────────────────────────── PROJECTS { string id PK string tenant_id FK string project_name text address string homeowner_name string homeowner_email string homeowner_phone string builder_reference string status boolean is_active datetime created_at } VARIATIONS { string id PK string project_id FK string tenant_id FK string variation_number text description numeric original_price numeric new_price numeric price_difference numeric new_price_gst string status string approval_token UK datetime token_expires_at text notes boolean hide_price string gst_mode string line_item_mode string email_subject string cc_emails datetime sent_at datetime created_at datetime updated_at } VARIATION_LINE_ITEMS { string id PK string variation_id FK string description numeric quantity numeric unit_price_before numeric unit_price_after numeric total_before numeric total_after } VARIATION_APPROVALS { string id PK string variation_id FK "unique" string decision datetime approved_at string ip_address text user_agent string homeowner_name text decline_reason } VARIATION_ATTACHMENTS { string id PK string variation_id FK string filename string content_type string s3_key datetime uploaded_at } TENANTS ||--o{ PROJECTS : "has" PROJECTS ||--o{ VARIATIONS : "has" VARIATIONS ||--o| VARIATION_APPROVALS : "has one" VARIATIONS ||--o{ VARIATION_ATTACHMENTS : "has" VARIATIONS ||--o{ VARIATION_LINE_ITEMS : "contains (optional)" %% ── E-SIGN MODULE ─────────────────────────────────────────────────────── ESIGN_DOCUMENTS { string id PK string tenant_id FK string created_by_user_id FK string document_number string title string original_filename string source_pdf_key integer page_count string status string final_pdf_key text decline_reason string email_subject datetime sent_at datetime completed_at datetime declined_at datetime cancelled_at datetime created_at datetime updated_at } ESIGN_RECIPIENTS { string id PK string document_id FK string name string email string role "signer or cc" integer sort_order string color_hex string status string access_token UK datetime token_expires_at datetime viewed_at datetime completed_at datetime declined_at string ip_address text user_agent text decline_reason datetime consented_at integer group_number datetime activated_at datetime created_at } ESIGN_FIELDS { string id PK string document_id FK string recipient_id FK string field_type integer page_number float x float y float width float height boolean required string radio_group boolean group_exclusive text value_text datetime filled_at datetime created_at } ESIGN_TEMPLATES { string id PK string tenant_id FK string created_by_user_id FK string name text description string original_filename string source_pdf_key integer page_count string default_email_subject datetime created_at datetime updated_at } ESIGN_TEMPLATE_ROLES { string id PK string template_id FK string label string role "signer or cc" string color_hex integer sort_order integer group_number } ESIGN_TEMPLATE_FIELDS { string id PK string template_id FK string template_role_id FK string field_type integer page_number float x float y float width float height boolean required string radio_group boolean group_exclusive } TENANTS ||--o{ ESIGN_DOCUMENTS : "has" USERS ||--o{ ESIGN_DOCUMENTS : "created by" ESIGN_DOCUMENTS ||--o{ ESIGN_RECIPIENTS : "has" ESIGN_DOCUMENTS ||--o{ ESIGN_FIELDS : "has" ESIGN_RECIPIENTS ||--o{ ESIGN_FIELDS : "owns" TENANTS ||--o{ ESIGN_TEMPLATES : "has" USERS ||--o{ ESIGN_TEMPLATES : "created by" ESIGN_TEMPLATES ||--o{ ESIGN_TEMPLATE_ROLES : "has" ESIGN_TEMPLATES ||--o{ ESIGN_TEMPLATE_FIELDS : "has" ESIGN_TEMPLATE_ROLES ||--o{ ESIGN_TEMPLATE_FIELDS : "owns" %% ── AUTH / SECURITY ───────────────────────────────────────────────────── PASSWORD_RESET_TOKENS { string id PK string user_id FK string token UK datetime expires_at boolean used datetime created_at } EMAIL_VERIFICATION_TOKENS { string id PK string user_id FK string token UK datetime expires_at boolean used datetime created_at } USERS ||--o{ PASSWORD_RESET_TOKENS : "requests" USERS ||--o{ EMAIL_VERIFICATION_TOKENS : "requests" %% ── SHARED / CROSS-MODULE ─────────────────────────────────────────────── ITEM_DESCRIPTIONS { string id PK string tenant_id FK string module string description datetime created_at } FOOTER_LOGOS { string id PK string tenant_id FK text image_data int sort_order datetime created_at } EMAIL_SUBJECT_OVERRIDES { string id PK string tenant_id FK string email_type string subject_template datetime created_at datetime updated_at } REQUEST_TEMPLATES { string id PK string tenant_id FK string name string subject text body datetime created_at datetime updated_at } REQUEST_LOGS { string id PK string tenant_id FK string module string recipient_id "no FK — Client/Project" string recipient_name string recipient_email string template_name string subject datetime sent_at } EMAIL_SEND_LOG { string id PK string tenant_id FK string module string recipient string subject string provider boolean success text error datetime sent_at } SES_EVENTS { string id PK string event_type string bounce_type string bounce_subtype string complaint_feedback_type string recipient string source_email string tenant_id FK "nullable, best-effort match" string module text raw_payload datetime received_at } SUPPRESSED_EMAILS { string id PK string tenant_id FK string email "stored lower-cased" string reason "hard_bounce / complaint / repeated_soft_bounce" string module boolean is_active integer soft_bounce_count string ses_event_id "no FK — snapshot" text detail datetime created_at datetime updated_at datetime cleared_at string cleared_by } TENANTS ||--o{ ITEM_DESCRIPTIONS : "has" TENANTS ||--o{ EMAIL_SUBJECT_OVERRIDES : "has" TENANTS ||--o{ FOOTER_LOGOS : "has" TENANTS ||--o{ REQUEST_TEMPLATES : "has" TENANTS ||--o{ REQUEST_LOGS : "has" TENANTS ||--o{ EMAIL_SEND_LOG : "has" TENANTS |o--o{ SES_EVENTS : "resolved (optional)" TENANTS ||--o{ SUPPRESSED_EMAILS : "has" %% ── ADMIN ──────────────────────────────────────────────────────────────── ADMIN_AUDIT_LOG { string id PK string admin_user_id "no FK — snapshotted" string admin_email string action string tenant_id "no FK — snapshotted" string tenant_business_name text detail datetime created_at } PLATFORM_SETTINGS { string key PK text value datetime updated_at }