logo
ExamplesEditorPricingDocs
37.1kSign in

Freelance invoicing and billing database schema

How a freelance and small-business invoicing app models its data in MySQL: clients and projects, tracked time, invoices with per-line tax, partial payments, credit notes, recurring templates, reminder schedules and multi-currency with the rate snapshotted at issue.

Invoicing App ER diagram

Freelance invoicing and billing database schema n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 clients idBIGINT client_numberVARCHAR company_nameVARCHAR contact_nameVARCHAR emailVARCHAR phoneVARCHAR billing_addressTEXT country_codeCHAR tax_numberVARCHAR default_currencyCHAR payment_terms_daysSMALLINT default_tax_rate_…BIGINT notesTEXT statusVARCHAR created_atDATETIME projects idBIGINT client_idBIGINT nameVARCHAR project_codeVARCHAR billing_typeVARCHAR hourly_rate_minorBIGINT currency_codeCHAR budget_minorBIGINT budget_hoursDECIMAL starts_onDATE ends_onDATE statusVARCHAR created_atDATETIME time_entries idBIGINT project_idBIGINT invoice_line_idBIGINT performed_byVARCHAR entry_dateDATE duration_minutesINTEGER descriptionVARCHAR hourly_rate_minorBIGINT is_billableBOOLEAN billed_atDATETIME created_atDATETIME updated_atDATETIME tax_rates idBIGINT nameVARCHAR codeVARCHAR rate_percentDECIMAL tax_typeVARCHAR jurisdictionVARCHAR is_compoundBOOLEAN is_activeBOOLEAN effective_fromDATE effective_toDATE invoices idBIGINT client_idBIGINT project_idBIGINT recurring_templat…BIGINT invoice_numberVARCHAR statusVARCHAR issue_dateDATE due_dateDATE currency_codeCHAR exchange_rateDECIMAL base_currency_codeCHAR subtotal_minorBIGINT tax_total_minorBIGINT total_minorBIGINT amount_paid_minorBIGINT amount_credited_m…BIGINT balance_due_minorBIGINT total_base_minorBIGINT purchase_order_nu…VARCHAR notesTEXT payment_termsTEXT sent_atDATETIME viewed_atDATETIME paid_atDATETIME voided_atDATETIME created_atDATETIME invoice_lines idBIGINT invoice_idBIGINT project_idBIGINT tax_rate_idBIGINT line_numberSMALLINT item_typeVARCHAR descriptionVARCHAR quantityDECIMAL unit_price_minorBIGINT discount_minorBIGINT tax_rate_percentDECIMAL tax_amount_minorBIGINT line_total_minorBIGINT payments idBIGINT invoice_idBIGINT payment_referenceVARCHAR amount_minorBIGINT currency_codeCHAR exchange_rateDECIMAL amount_base_minorBIGINT methodVARCHAR processor_fee_min…BIGINT statusVARCHAR received_onDATE external_referenceVARCHAR noteVARCHAR created_atDATETIME credit_notes idBIGINT invoice_idBIGINT credit_note_numberVARCHAR reasonVARCHAR amount_minorBIGINT tax_amount_minorBIGINT currency_codeCHAR statusVARCHAR issued_onDATE applied_onDATE notesTEXT created_atDATETIME recurring_templates idBIGINT client_idBIGINT project_idBIGINT nameVARCHAR frequencyVARCHAR interval_countSMALLINT day_of_monthSMALLINT currency_codeCHAR payment_terms_daysSMALLINT next_run_onDATE starts_onDATE ends_onDATE occurrence_limitSMALLINT occurrences_creat…SMALLINT auto_sendBOOLEAN statusVARCHAR created_atDATETIME recurring_template_… idBIGINT template_idBIGINT tax_rate_idBIGINT line_numberSMALLINT descriptionVARCHAR quantityDECIMAL unit_price_minorBIGINT item_typeVARCHAR reminder_rules idBIGINT client_idBIGINT nameVARCHAR trigger_typeVARCHAR offset_daysSMALLINT channelVARCHAR subject_templateVARCHAR body_templateTEXT stop_when_paidBOOLEAN max_sendsSMALLINT is_activeBOOLEAN created_atDATETIME invoice_reminders idBIGINT invoice_idBIGINT rule_idBIGINT scheduled_onDATE statusVARCHAR channelVARCHAR recipient_emailVARCHAR sent_atDATETIME opened_atDATETIME failure_reasonVARCHAR created_atDATETIME exchange_rates idBIGINT base_currencyCHAR quote_currencyCHAR rateDECIMAL rate_dateDATE sourceVARCHAR fetched_atDATETIME

Related database schemas

PostgreSQL
Fintech

Banking ledger database schema (double-entry)

A double-entry bank ledger: accounts in a single currency each, balanced journal entries that are never updated, card authorizations that hold funds, transfers kept separate from postings, and frozen statements.

PostgreSQL
Fintech

Crypto exchange order book database schema

How a centralised crypto exchange models its data: trading pairs, limit orders resting on the book, maker and taker trades, available versus locked balances, on-chain deposits and withdrawals, fee tiers and order book snapshots.

SQLite
Fintech

Expense tracker & personal finance database schema

A local-first SQLite personal finance database: accounts, transactions in integer minor units, a nested category tree, per-period budgets with rollover, recurring rules, statement imports with auto-categorisation, receipts, and transfers modelled as a linked pair of legs.

Oracle
Fintech

Consumer lending and loan servicing database schema

How a consumer lender models its data in Oracle: applications and verified income, credit decisions with ranked adverse-action reasons, loans, the amortisation schedule as rows, repayments allocated across fees, interest and principal, delinquency buckets and collections.