Data model

Schema reference for a SlowBooks Pro company database: one SQLite file per company on the desktop and Server Edition, or a PostgreSQL database. 105 tables on a double-entry accounting foundation (Alembic head 95209e8d293c in 2.31.4: bank_rules.class_id and bank_rules.job_id, after 4810bd59e481). For migration history, see the files under migrations/versions/; for model code, see app/models/. The construction and job-cost tables are described in their own sections below the table.

jobs (Customer:Job / Projects)

job_id (nullable FK) lives on: transactions, transaction_lines, invoices, invoice_lines, bills, bill_lines, estimates, estimate_lines, purchase_orders, purchase_order_lines, credit_memos, credit_memo_lines, vendor_credits, vendor_credit_lines, recurring_invoices, memorized_transactions and their lines, in_kind_gifts and their lines, allocation_rule_targets, time_entries. class_id rides transactions and every document header and line that posts (transaction_lines, invoice_lines, bill_lines, credit_memo_lines, the estimate, purchase-order, vendor-credit, memorized and in-kind lines); customer_id rides transaction_lines too, for a deposit split among several payers (#606). Attribution rule for reports: coalesce(line.job_id, transaction.job_id). Migration e9f0a1b2c3d4_add_jobs.

cost_codes (job-costing chart)

cost_code_id (nullable FK) on transaction_lines, bill_lines, invoice_lines, estimate_lines, purchase_order_lines, vendor_credit_lines, time_entries. A nullable cost_type (String(20)) rides every costable line — transaction_lines, bill_lines, estimate_lines, purchase_order_lines, vendor_credit_lines — chosen per line, falling back to the code's default when blank. is_billable + billed_invoice_line_id (FK invoice_lines) on transaction_lines and bill_lines. purchase_orders.job_id / purchase_order_lines.job_id feed committed cost. Migrations f0a1b2c3d4e5_add_cost_codes, c8e4a2f1b3d5_division_section_cost_codes (#550: level, the four line cost_type columns, trade packages).

Trade packages (#550)

A package is what a sub bids; a section is what costs post to. Seeded by POST /api/construction/setup when the company has none.

Construction estimating (#551)

estimate_lines gains the bid shape: trade_package_id (nullable FK trade_packages, SET NULL — the bid package the line belongs to; null groups under "Unassigned"), line_kind (String(12), NOT NULL default base — one of base · allowance · alternate · unit_price), unit (String(20), nullable — SF, LF, EA, CY…) and alternate_accepted (Boolean, NOT NULL default false). estimates gains the four compounding markups gc_pct, insurance_pct, fee_pct, bond_pct (Numeric(7,4), NOT NULL default 0 — percent values, 5.5 = 5.5%), markup_allowances (Boolean, NOT NULL default false — the markups also build on allowance lines when true) and accepted_at (DateTime, nullable). job_budgets.locked (Boolean, NOT NULL default false) marks the rows an accepted estimate owns — only a change order moves them. invoice_lines gains cost_type (String(20)) and unit_cost (Numeric(12,4)), both nullable, so a converted estimate carries its cost basis onto the invoice. The bid math (lines at cost; the markup base, the four markups compounding in order, and their total; the subtotal of the included lines; the pre-tax bid total as subtotal + markups, with tax covering the lines and markups together; priced-direct and allowance totals; the package, alternate and unit-price breakdowns) is computed, not stored — app/services/estimating.py. Migration d9f6b2c4e7a1_construction_estimating (additive and nullable/defaulted; downgrade reverses).

The contract on the job (#552)

jobs gains the contract terms, all nullable (a job with no contract_type behaves exactly as before): contract_type (String(12) — lump_sum · gmp · cost_plus), original_contract_amount (Numeric(15,2) — the sum when the contract was first set, stamped once; contract_amount stays the current / revised sum so #554 shows original + changes = revised), fee_pct (Numeric(7,4)) and fee_amount (Numeric(15,2) — a fixed fee, which wins when both are set), cap_amount (Numeric(15,2) — the GMP maximum or a cost-plus not-to-exceed), contingency_amount (Numeric(15,2) — owner contingency inside the sum), retainage_pct (Numeric(5,2) — held per pay application, #555), contract_date (Date) and contract_estimate_id (FK estimates, SET NULL).

New table sov_lines — the owner's schedule of values:

Unique (job_id, line_no). A scheduled_value may be negative (a deduct alternate nets the sum down). A line's billed / stored / retainage figures are not stored on it — a pay application bills it (#555, below), carrying those columns on pay_application_lines and reading previous from history. The contract sum and the schedule are pre-tax (tax is added per invoice). The contract summary (changes, fee, cap remaining, contingency remaining, SOV total and balance, billed to date) is computed, not stored — app/services/contracts.py. Migration e2a4c6d8f0b1_construction_contract (additive and nullable/defaulted; downgrade reverses).

Buyout and commitments (#553)

A commitment is a purchase order — no new table. Additive columns:

• purchase_orders.commitment_type String(12) NOT NULL default po (po | subcontract), trade_package_id (FK trade_packages.id, SET NULL), retainage_pct Numeric(5,2) (held on every bill from the commitment), scope_notes Text.
• purchase_order_lines.billed_amount Numeric(15,2) NOT NULL default 0 — how much of the line (whose amount is its scheduled value) has been billed; remaining = amount − billed_amount. received_qty is left untouched.
• bills.retainage_held Numeric(15,2) NOT NULL default 0 (credited to Retainage Payable, 2050, not A/P; balance_due is the net), and lien_waiver_status String(16) (not_required | pending | conditional | unconditional).
• bill_payments.coi_override / coi_override_note: set when a Sub with an expired COI was paid by explicit override (#553).
• bill_lines.purchase_order_line_id (FK purchase_order_lines.id, SET NULL) — the back link partial billing tallies against.

committed_cost is now Σ (amount − billed_amount) over non-draft, non-closed commitment lines (the remaining open value); commitment_figures adds committed_total (Σ amount of non-draft commitments) and billed_to_date (Σ billed) — app/services/jobs_service.py. Those two are whole-commitment figures, so in a date-ranged job_cost_tree they are all-time; the totals carry commitments_all_time: true to say so. Retainage is computed on the pre-tax subtotal (tax is payable in full); the shared vendor_compliance helper (app/services/compliance.py) feeds both the bills responses and the buyout rows. The buyout table (bid vs bought per package) is computed in app/services/buyout.py; vendor_compliance (Sub W-9 / COI flags) is computed from the vendor. Migration f3b5d7e9a2c4_construction_buyout (additive; backfills a closed PO's billed lines from its bill; downgrade reverses). Retainage release (a debit back to 2050) is a release bill — see Progress billing and retainage (#555).

Change orders (#554)

How a locked budget, its contract and its commitments move. Two new tables:

• change_orders — the document: job_id (FK, CASCADE), number String(80) unique (per job and kind: <job number>-CO-001 / -PCO-001 / -SCO-001, a PCO revision adds -R2; older CO-0001 rows keep theirs), kind String(8) (pco | owner | sub), status String(10) (draft | pending | approved | rejected | void | promoted | superseded), title, description, date, funding String(12) (contract | allowance | contingency), allowance_sov_line_id (FK sov_lines.id, SET NULL — required when funding = allowance), markup_pct Numeric(7,4) (the CO's default markup, null → the job's co_markup_pct), commitment_id (FK purchase_orders.id, SET NULL — required for a sub CO), owner_change_order_id (FK self, SET NULL), source_pco_id (FK self, SET NULL), apply_markup Boolean (default true; the API defaults it off for allowance/contingency funding), revision Integer (default 1) and supersedes_id (FK self, SET NULL — the PCO revision this one replaced), promoted_to_id (FK self, SET NULL — the owner CO a PCO was rolled into; several PCOs may share one), approved_at, voided_at.
• change_order_lines — change_order_id (FK, CASCADE), line_order, description, cost_type String(20) NOT NULL (validated against cost_types), cost_code_id, trade_package_id, quantity, unit, unit_cost, amount (cost = qty × unit_cost, server-computed), markup_pct (per-line override) and sell_amount (cost × (1 + markup/100), server- computed; = cost under allowance funding or on a sub CO).

Additive columns: jobs.co_markup_pct Numeric(7,4) (the default markup on owner change orders), and sov_lines.change_order_id (FK change_orders.id, SET NULL — the owner CO that owns a change_order-kind SOV line; change_order is added to SOV_KINDS). The math and the apply/reverse effects live in app/services/change_orders.py; the append-only commitment helper is in app/routes/purchase_orders.py. On approve an owner CO writes job_budgets rows with source = change, locked = True (the one path that may move a locked budget), which the job-cost tree reads into its Changes column. Migrations a7c9e1f3b5d6_construction_change_orders and b8d0f2a4c6e8_change_order_revisions_markup_flag (additive; downgrade reverses). A billed change_order SOV line can no longer be voided (409 co_billed, filled by #555); the change-order log report is #556.

Progress billing and retainage (#555)

The owner's progress bill from the schedule of values. Two new tables:

• pay_applications — job_id (FK jobs, CASCADE), application_no Integer (1, 2, … per job, unique (job_id, application_no)), number String(20) unique (PA-<job>-<no:02>), period_to Date, date Date, status String(10) (draft | submitted | approved | void), retainage_pct Numeric(5,2) (copied from the job at creation, editable while draft), release_retainage Numeric(15,2) default 0 (retainage billed back this application), deposit_applied Numeric(15,2) default 0 (owner deposit applied to the current due), lien_waiver_status String(16), invoice_id (FK invoices, SET NULL — the invoice approve created; null for an application that only moves stored material into the work), tax_rate Numeric(7,6), charge_tax Boolean default true (#576), deposit_payment_id (FK payments, SET NULL — the payment that drew the owner deposit down), contract_sum_original / contract_sum_to_date Numeric(15,2) nullable (the contract sums as of approval), approved_at, voided_at, notes. Retainage to date is net of releases.
• lien_waivers (#576) — payment_id (FK payments, CASCADE), job_id, pay_application_id (FK, SET NULL), waiver_type String(24) (conditional_progress | unconditional_progress | conditional_final | unconditional_final; unique per payment and type), amount, through_date, signed_by, notes.
• pay_application_lines — pay_application_id (FK, CASCADE), sov_line_id (FK sov_lines.id, SET NULL), line_no, item_no, description, scheduled_value, this_period and stored (Numeric(15,2); previous and completed / retainage are computed from history, not stored).

New table job_deposits — owner deposits: job_id (FK, CASCADE), date, amount, deposit_to_account_id (FK accounts — the bank), memo, transaction_id (FK), voided_at. A deposit posts DR bank / CR Customer Deposits (2210, source_type job_deposit); a pay application's deposit_applied draws it down with a payment to 2210 on approve.

Additive columns: invoices.retainage_held and invoices.retainage_released (Numeric(15,2) NOT NULL default 0) and invoices.pay_application_id (FK pay_applications.id, SET NULL); bills.retainage_released and purchase_orders.retainage_released (Numeric(15,2) NOT NULL default 0). The pure math and the history helpers live in app/services/pay_applications.py; approve builds the invoice and its retainage journal (A/R net, Retainage Receivable 1150 held / released, income, tax) in app/routes/pay_applications.py, with the sub side (schedule and the 2050 release bill) in app/routes/purchase_orders.py and the deposits in app/routes/jobs.py. Migration e5c9a1f7b2d4_construction_progress_billing (additive and nullable/defaulted; downgrade reverses). The WIP schedule, cost-to-complete and the other job reports are #556.

Job cost model (milestone 3)

Migration a1b2c3d4e5f6_job_cost_model.