Audience: engineers. This is the map of GoBuild's canonical relational schema — the
SQLAlchemy models under app/models/. Every table here is hub-owned (Postgres in the
tenant's own stack); OpenProject mirrors are called out where they exist. Entities are grouped
by product domain (CRM, Jobs, Finance, Field/HR, Platform/Tenancy), each with its purpose, the
load-bearing fields, and its foreign keys to the rest of the graph.
House terms: a Job is the project (table jobs); a Client is the homeowner (table
clients). The UI says "project/homeowner"; the schema mostly says job/client.
Naming note. The schedule "task" bar (
schedule_items, the Gantt) and the lightweight
to-do "Task" (tasks) are two different tables. Don't conflate them — see
Jobs.
Every business table carries an organization_id FK — the tenant. It's added by the
TenantMixin (app/models/base.py:40-45), which declares:
organization_id -> ForeignKey("organizations.id", ondelete="CASCADE"), index=True, nullable=False
The Organization is the builder's company (app/models/organization.py:18). Under the
whole-stack-per-client deployment there is physically one org per database, but the column
lives on every table so small clients can later be consolidated into a shared "pooled" hub
without a schema rewrite (app/models/base.py:1-7). Practical consequences:
organization_id. There is no global fallback; a row without auq_user_org_email, app/models/user.py:15), not globally.UUIDPrimaryKey (UUID id) and TimestampMixincreated_at / updated_at), both in app/models/base.py:24-37.ondelete="CASCADE").Soft-delete is not uniform — it's per-entity and flagged in each domain below. There is no
base soft-delete mixin.
The commercial heartbeat is Client → Lead → Proposal → Job, and off the Job hang the
schedule, financials, field logs, and documents. All FKs shown are the real columns in the
models; note that several are deliberately nullable (a Job can exist without a Client; a
Proposal can exist before a Lead is set) and several won/converted links are stored
one-way to avoid circular FKs.
The "land" loop in prose: a homeowner submits a form → a Lead is created
(app/models/sales.py:46) → the salesperson builds a Proposal (sales.py:147) → the
homeowner accepts it on the public token page → acceptance materializes a Job
(app/models/job.py:21) plus its first Estimate, ScheduleItems (from
Proposal.schedule) and PaymentMilestones (from Proposal.payment_schedule). The links are
recorded on both sides but as plain nullable FKs, not a mutual constraint:
Lead.won_job_id (sales.py:91), Proposal.job_id (sales.py:214), and the reverse
Job.lead_id / Job.client_id (job.py:36-42).
The control-plane and per-tenant infrastructure layer.
organizations (app/models/organization.py:18)The tenant (the builder's company) and the root of every cascade.
tier (starter/pro/enterprise), isolation (pooled = shared hub row;status (active/provisioning/suspended/deleted) — all VARCHAR-backedorganization.py:33-44).entitlements JSON holds per-org overrides only; resolved against tierhas_feature (organization.py:46-47).deleted_at marks the start of the purge grace window (organization.py:51).plan (VARCHAR "trial") is deprecated, superseded by tier; kept only so theinstance_reset wipe keeps working (organization.py:53-55).users, jobs (delete-orphan cascades).users (app/models/user.py:12)Staff at the builder (the hub owns auth). role ∈ owner/pm/field/office (UserRole,
base.py:51). Carries hashed_password + an optional numeric pin_hash for the field app.
person_id → people (unified identity; nullable, backfilled, SET NULL).uq_user_org_email). Subs and homeowners are nottenant_credentials (app/models/tenant_credential.py:19)Per-org BYO integration secrets (Twilio/Stripe/QBO/Xero/email), one row per provider
(uq_tenant_credential_org_provider). Secrets are Fernet-encrypted in ciphertext; safe
display bits in meta. This is the storage behind the credential wall-off model.
app/models/integration.py:25,43One OAuth connection per org (uq_qbo_conn_org) plus a polymorphic id-map
(local_type/local_id ↔ qbo_type/qbo_id) so syncs update rather than duplicate.
identity_map (app/models/identity_map.py:22)The cross-system identity surface: maps a canonical hub entity (entity_type ∈
job / schedule_item, EntityType in base.py:204) to its OpenProject id. Two unique
constraints keep the mapping 1:1 in both directions. Reconciliation walks this one table.
app/models/gcal.py:17,31One company-calendar OAuth connection per org; GCalEventMap ties a schedule_item to the
Google event it created (uq_gcal_map_item). Dormant until Google creds are configured.
app/models/audit.py:18) — polymorphic who/when/what per recordentity_type/entity_id, optional job_id); the per-record "History" timeline.app/models/job_event.py:14) — a narrower, job-scoped office-action logworkload_rebalance). Overlaps conceptually with AuditEvent — see open questions.app/models/entity_revision.py:19) — polymorphic version/draft/autosavepayload JSON) for the unified save system. AuditEvent records thatapp/models/data_transfer.py:17) — one row per bulk import/export/backup,created_ids in detail as the undo anchor.app/models/notification.py:17) — proactive alerts, idempotent viauq_notif_org_dedupe.app/models/notify_prefs.py:38,56) — per-userapp/models/report_meta.py) —app/models/ai_feedback.py:18) — logs every Keystone recommendation and itsapp/models/email_ops.py:16,27) and SmsSuppressionapp/models/sms_suppression.py:15) — outbound-comms control + audit + dead-number circuitpeople (app/models/person.py:38) — the identity spineWho someone is (name/email/phone/company). The role records — Lead, Client, Sub, Contact,
and staff Users — keep their role-specific fields and point back here via a nullable
person_id. One person can hold several roles at once (roles JSON list, person.py:50;
ROLE_* constants at person.py:26-35). This is additive, not a destructive merge — the
role tables are untouched, so the client portal, sub↔client wall, and pipeline keep working.
ensure_person (app/services/people.py) keeps it in sync and dedupes by normalized
email/phone within the org.
archived (soft-hide from directory). Deleting a person who backs a liveperson.py:53-55).fit_score / engagement_score / score (person.py:60-62).clients (app/models/client.py:10)The homeowner. Thin: name/email/phone/address + extra JSON. FK: person_id → people.
(SuiteCRM was dropped; hub-owned.)
subs (app/models/client.py:25)A subcontractor crew. company/trade/hourly_rate, an external_payroll_id (1099/T5018), an
optional openproject_user_id, and a tokenized clock_token for no-login mobile time-clock.
FK: person_id → people. Referenced widely (POs, shifts, tasks, bids, credentials).
contacts (app/models/contact.py:15)Key people on a Lead or a Job (main/billing/site). Hangs off either lead_id or
job_id (both nullable, CASCADE), plus person_id. Roles are free-form tags (JSON), not a
fixed dropdown.
addresses (app/models/address.py:29)Structured typed address. kind ∈ site/mailing/billing (AddressKind, base.py:190). Points
at exactly one owner via nullable FKs: site → lead_id/job_id (the property);
mailing/billing → person_id (follows the human). The legacy one-line Lead.address is kept
in sync with the site address.
leads (app/models/sales.py:46) — front of the funnelA sales-pipeline card. status ∈ new/contacted/estimating/won/lost (LeadStatus,
base.py:173); confidence (hot/warm/cold → win %); estimated_value_cents;
first-touch attribution fields (first_touch_channel, gclid, msclkid, visitor_anon_id)
stitched from the marketing capture layer; custom JSON keyed by LeadFieldDef.
owner_id → users (salesperson), won_job_id → jobs (set on conversion),person_id → people.activities (LeadActivity, timeline), addresses (site).sales.py:118) — org-defined custom-field definitions; values live inLead.custom.proposals (app/models/sales.py:147) — the e-signable magazineThe client-facing document. Self-contained lines JSON snapshot (leads have no Job yet), a
themed sections body, schedule + payment_schedule blueprints materialized on acceptance,
a public public_token, open-tracking, and an e-signature audit trail
(signed_ip/signed_user_agent).
lead_id → leads (SET NULL), job_id → jobs (set on accept),plan_document_id → documents.sales.py:233) — per-open/send/sign event with IP/UA (defensible proof).app/models/marketing.py) — the unified nurture suite: saved audiences, a node-graphapp/models/lead_followup.py) — the older per-lead follow-up + drip layer. Overlaps themarketing.py:44) — see open questions.app/models/campaign.py) — a marketing initiative thatapp/models/marketing_attr.py) — anonymous website-visitapp/models/telephony.py) — call-tracking numbers per channel andapp/models/ad_account.py, ad_campaign.py) — PPC scaffoldingad_account.py:5).app/models/social.py) — the managedapp/models/landing.py:16) — the org's public site and campaign landingkind = site/campaign), custom-domain state machine.app/models/custom_page.py) — operator-authored hand-codedapp/models/blog.py) — a blog attached to ablog_post_categories.app/models/communication.py:23) — the permanent per-contact email/SMSSET NULL so the record outlives the contact/lead/job it referenced.app/models/appointment.py:16) — GoBuild-native booking (replaces Cal.com);lead_id/person_id.app/models/email_template.py:14) — the branded reusable email libraryuq_email_template_org_slug).jobs (app/models/job.py:21) — the central entityEverything hangs off the Job. status ∈ lead/active/on_hold/complete/archived (JobStatus,
base.py:87); is_draft flags express-flow jobs hidden from lists until "promoted".
Financial-health overrides live here: contract_value_cents, percent_complete_override,
holdback_pct / substantial_completion_date (retainage), and margin_overrides (per-category
markup JSON). A shareable client_token gives the homeowner a no-login read-only view; geofence
fields (latitude/longitude/geofence_radius_m) anchor the crew time clock.
client_id → clients (SET NULL), lead_id → leads (SET NULL, reverse ofLead.won_job_id), openproject_project_id cached (authoritative copy in IdentityMap).schedule_items, daily_logs.status = archived, not a boolean.schedule_items (app/models/schedule.py:18) — the Gantt barOpenProject is authoritative for these; the hub mirrors a work package back for fast
portal/AI reads (schedule.py:1-8). openproject_wp_id + lock_version track the mirror
(uq_sched_org_wp). Hub-native sub-task hierarchy via parent_item_id (self-FK) with
rollup_override; budget_cents is the PM-entered planned Earned-Value budget, deliberately
independent of cost/billing (schedule.py:47-50). Per-item client/sub visibility flags and a
sub-RSVP confirmation.
job_id → jobs (CASCADE), assignee_sub_id → subs, requires_permit_id → permits.schedule.py:78) — WBS phase grouping (uq_job_phase_name), backed by anschedule.py:98) — predecessor→successor links; hub-nativepredecessor_item_id/successor_item_id are authoritative, from_wp_id/to_wp_id mirrorrelation_type ∈ FS/SS/FF/SF; the hub is the scheduler of record.schedule.py:122) — mirror of an OpenProject time entry.tasks (app/models/task.py:32) — the to-do layer (NOT the Gantt)A lightweight assignable to-do, separate from ScheduleItem (no OpenProject sync, no dependency
engine). job_id nullable (null = org-level "General" task). Multi-assignee (users and
subs, each with RSVP), a checklist, priority, recurrence.
job_id → jobs, schedule_item_id → schedule_items (the work it supports),created_by_user_id → users. A generic linked_type/linked_id pair points at aTaskAssignee (user_id or sub_id), TaskItem (checklist), TaskCommentmentions).app/models/planning.py:20) — a saved schedule + pricing blueprint a Job issource_job_id → jobs.planning.py:40) — a snapshot of the originally-promised schedulesnapshot JSON) for slippage tracking.app/models/document.py:26) — metadata row; bytes live in DO Spacesspaces_key). A single Document optionally attaches to many parents via nullable FKs:job_id, schedule_item_id, permit_id, po_id, change_order_id, task_id, folder_id,user_id (HR file), drawing_set_id, plus a self-FK supersedes_id for versioning.is_deleted + deleted_at. shared_with_client surfaces it in the portal.document.py:107) — folder tree (self-FK parent_id).document.py:121) — a named batch; the upload PDF is split into one Documentdocument.py:145) — overlay annotations for one page of one Document version;job_id → jobs, CASCADE)app/models/submittal.py) — review/answer loops with aging +ball_in_court; RFIs and punch items can drop a pin_x/pin_y on a drawing sheet.app/models/selection.py) — homeowner finish choices with anChangeOrder (change_order_id FK).chosen_option_id is a plain UUID (no FK) to dodge a circular constraint.app/models/punch.py:11) — closeout defect list; optionalschedule_item_id.app/models/warranty.py:11) — post-completion issues.app/models/meeting.py:16) — job meeting minutes; action_items as inline JSONapp/models/takeoff.py:17) — measured quantities from a plan (manual/AI),document_id → documents.app/models/bid.py) — sub bid solicitation; an awarded Bidpurchase_order_id, one-way UUID). awarded_bid_id is a plain UUID (no FK).app/models/permits.py) — job-scoped permitapp/models/catalog.py:16) — a reusable saved cost line; vendor_id → vendors.catalog.py:30) — a named group of items that expands into estimate linesAll financials are hub-owned — OpenProject's cost API is a read-only stub
(app/models/money.py:1-6, cost.py:1-12). Money is stored in integer cents columns
(*_cents) with @property dollar accessors; a few older tables (Estimate lines) use Float
dollars.
estimates / estimate_lines (money.py:36,117)The sell-side quote on a Job. Estimate.status ∈ draft/sent/approved/declined. Totals are
computed from lines (each line stores its own marked-up price; markup resolution order is
per-line → job per-category → estimate default, money.py:149-158).
Estimate.job_id → jobs (CASCADE). EstimateLine.vendor_id → vendors.invoices (money.py:165)Client-facing bill. status ∈ draft/sent/paid/void. amount_cents + a lines JSON snapshot,
a Stripe Checkout URL (stripe_url/stripe_session_id), a public token page, and Ontario
prompt-payment proper_invoice_date.
job_id → jobs (CASCADE), estimate_id → estimates (SET NULL).payment_milestones (money.py:211)A draw/deposit on the Job's payment schedule. Bills a slice by percent or fixed amount;
generates an Invoice when due (invoice_id, one-way to avoid a circular FK). Can be released
by the schedule: trigger_schedule_item_id (one task hits 100%) or trigger_phase (whole
phase completes).
job_id → jobs.change_orders / change_order_lines (money.py:243,330)Scope changes. Tracks both a client-price delta (cost_delta_cents) and a builder-cost
delta (cost_cents) with a markup between them. Can auto-invoice on approval
(invoice_on_approval → invoice_id) and can amend a PO (purchase_order_id +
applied_to_po guard). Reuses the PO dual-sign engine (signing_mode,
countersign_user_id).
job_id → jobs (CASCADE).vendors (app/models/cost.py:88)A supplier / anyone you receive a bill from (the contact entity OpenProject lacks). Carries
T5018 fields (business_number, is_subcontractor) and a performance rating.
cost_codes (app/models/cost.py:43)The org's chart-of-cost-codes (CSI-lite, seeded on signup). code + default category
(CostCategory, base.py:123: material/labor/subcontractor/equipment/permit/other). Costed
rows across the app carry a free-text cost_code string that references these — note there is
no DB FK from those rows to cost_codes (it's a soft string key). ⚠️ CostCode and
ForecastAdjustment are defined in cost.py but are NOT imported in app/models/__init__.py
— see orphans.
forecast_adjustments (app/models/cost.py:67)A PM's manual projected-cost nudge for one cost code on one Job (job_id → jobs), signed cents.
budget_lines (cost.py:107)Planned cost for a Job, bucketed by CostCategory. job_id → jobs.
cost.py:125,223,280Committed (not-yet-billed) cost to a vendor or a sub. status is a rich lifecycle
(POStatus, base.py:134: draft → released → sub_approved → draft_amended → billed, plus
legacy sent/received). PO_COMMITTED_STATUSES (base.py:158) defines which count toward
committed cost (a billed PO is excluded to avoid double-counting the bill). Per-line partial
receiving (received_quantity) and CO-amendment versioning (current_version + POVersion
snapshots). Vendor/sub e-sign via public_token + signature_request_id.
job_id → jobs (CASCADE), vendor_id → vendors, sub_id → subs,schedule_item_id → schedule_items (Gantt delivery marker). source_bid_id,signature_request_id, countersign_user_id are plain UUIDs (no FK).bills / bill_line_items (cost.py:310,414)An actual cost (AP). status ∈ inbox/approved/paid/void (BillStatus, base.py:164) — inbox
= captured but unreviewed. ai_extracted JSON holds the OCR guess. cost_breakdown
(cost.py:361) drives per-line job-costing attribution.
job_id → jobs (SET NULL — a captured bill may not be assigned yet),vendor_id → vendors, po_id → purchase_orders, document_id → documents. BillLineItempo_line_item_id for over-billing detection.lien_waiver_status/_token) in additionlien_waivers (cost.py:370)A Documenso-signed statutory waiver, tied to a bill_id (the sub/vendor waives lien rights) or
an invoice_id (we provide it to the owner), plus job_id. Four statutory types
(conditional/unconditional × progress/final). Self-contained Documenso link — deliberately
does not share the generic SignatureRequest machinery.
See Platform — QuickBooksMap mirrors invoices/bills/payments/
vendors/contacts out to QBO.
daily_logs (app/models/daily_log.py:15) — the wedge ritualThe core field-ops record; may be voice-sourced (transcript) and AI-structured (structured
JSON). photo_document_ids is a JSON list of Document ids.
job_id → jobs (CASCADE, and a delete-orphan child of Job), author_id → users,schedule_item_id → schedule_items.daily_log.py:42) — the AI-drafted homeowner-facing progress note.field_captures (app/models/field.py:21)The AI capture spine's inbox: every field capture (note + photos + GPS) lands here, Keystone
classifies it, and it files the real record. Also the offline-sync target and the owner
review queue (status new → reviewed). Actor is a User or a Sub, never the homeowner
(that's the wall). created_record_kind/_id track what was filed.
shifts (app/models/timeclock.py:17) — geofenced time clockOpened on clock-in (time + GPS), closed on clock-out; computes hours. in_geofence records
whether clock-in was inside the Job's radius. Timesheet approval (approved_at,
approved_by_user_id) and a derived cost_code for costing-by-code.
job_id → jobs (CASCADE), sub_id → subs or user_id → users (staff clock-in),openproject_time_entry_id cached.app/models/messaging.py:17) — homeowner ↔ contractor thread, per job_id.messaging.py:29) — the deliberately separate office ↔ sub/field-employeesub_id or person_id). A sub can only ever post here — this separation isportal_access_codes (app/models/portal_access.py:16)One-time SMS code gating a client (or sub) portal magic link. Exactly one of job_id /
sub_id set (each unique).
app/models/hr.py)hr.py:19) — the employee record for a User (1:1, uq on user_id); HRmanager_id,pto_policy_id FKs.hr.py:53,78) — a checklist run for a new hire/sub/vendorsubject_kind/subject_id — no FK).hr.py:94) — PTO requests (polymorphic subject).hr.py:116,134) — leave policies (US accrual vs Canadian__init__.py.hr.py:147+) — template-driven, self+manager reviews. ⚠️ Only PerformanceReview is__init__.py; the four supporting tables are not.app/models/equipment.py)equipment.py:18) — a tool/device/vehicle/PPE/key.equipment.py:36) — a checkout to a workforce member (polymorphicholder_kind/holder_id — no FK); open loan = returned_at null.equipment.py:58) — owned equipment put on a job_id at anapp/models/permits.py:91) — the contractor's own org-level compliance docs,permits.py:107) — a sub's / vendor's / staff member's license/insurance;sub_id / vendor_id / user_id set.app/models/safety.py) — logged safety meetings (attendeecountry/region snapshotted soapp/models/signature.py:33) — the generic e-sign registrydoc_type (SignDocType: proposal/invoice/contract/change_order/entity_id (no FK — a plain UUID to the target). SupportsA recurring pattern worth internalizing before writing a query:
| Pattern | Examples | Why |
|---|---|---|
| Nullable "exactly one of" owner FKs | Contact (lead_id|job_id), Address (person|lead|job), PortalAccessCode (job|sub), SubCredential (sub|vendor|user), CrewMessage (sub|person) |
One table serves multiple parents; the app enforces the "exactly one" invariant, not the DB. |
| Polymorphic type+id, NO FK | AuditEvent, EntityRevision, SignatureRequest (entity_type+entity_id), OnboardingRun/TimeOffRequest/EquipmentLoan (subject_kind/holder_kind + id), Task.linked_type/linked_id |
The target can be any of several tables; resolved in the service layer. Referential integrity is not enforced by Postgres. |
| One-way UUID link to dodge a circular FK | Lead.won_job_id ↔ Job.lead_id, Proposal.job_id, PaymentMilestone.invoice_id, Bid.purchase_order_id / BidRequest.awarded_bid_id, Selection.chosen_option_id, PO.source_bid_id/signature_request_id |
Two tables reference each other; storing both as real FKs would create a cycle, so one side is a plain Uuid column. |
| Soft string keys | cost_code (VARCHAR) on estimate/PO/bill/budget lines → cost_codes.code |
Costing rows reference the code by its string, not by cost_codes.id. |
| JSON id-lists | photo_document_ids, pano_document_ids, media_doc_ids, member_ids |
Sets of Document/Person ids stored inline as JSON arrays, not join tables. |
Things a maintainer should know are not what they seem:
CostCode + ForecastAdjustment not registered via __init__.py. Both are defined inapp/models/cost.py:43,67 but the from app.models.cost import (...) line inapp/models/__init__.py:11-13 does not list them. They do still register withBase.metadata (importing the module runs the class bodies), so Alembic sees the tables — but__all__, so from app.models import CostCode fails. Easy to trip over.PtoPolicy, PtoAdjustment, ReviewTemplate,ReviewQuestion, ReviewCycle, ReviewAnswer (hr.py) and EmailSetting, SentEmailemail_ops.py) are not in __init__.py's import list or __all__ — same situation asOrganization.plan — explicitly DEPRECATED, superseded by tierorganization.py:53-55). Retained only so instance_reset keeps working.Lead.address (one-line) — legacy; kept in sync with the structured site Address forsales.py:54-55).POStatus.sent / received — legacy enum values; sent reads as releasedbase.py:152).DripSequence/DripEnrollment/DripSendlead_followup.py) are the older linear nurture; the node-graph Workflow enginemarketing.py:44). Both sets of tables still exist.ad_account.py:3-6).lines JSON vs line_items relationship — POs (and Invoices) keep a legacy linesline_items/POLineItem rows; old rows render from JSONcost.py:149-151).| Entity | System of record | Notes |
|---|---|---|
| Everything financial (Estimate/Invoice/PO/Bill/CO/Budget) | Hub | OpenProject cost API is a read-only stub. |
| Job header | Hub | openproject_project_id is a cached mirror; IdentityMap is authoritative for the link. |
| ScheduleItem (Gantt) | OpenProject (mirrored to hub) | Hub mirrors WP back for portal/AI reads; reconciliation flags drift, never silently overwrites (schedule.py:1-8). But the hub is the scheduler of record for dependencies (schedule.py:98-105). |
| TimeEntry | OpenProject (mirror) | Shift → OP time entry is the write path. |
| Signed documents | Documenso (hub holds registry + status) | SignatureRequest / LienWaiver are registries; Documenso keeps the crypto cert. |
| QuickBooks entities | QBO (hub keeps a map) | QuickBooksMap prevents duplicate creation. |
| Person roles | Hub, additive | Person mirrors identity across role tables; role tables stay the source for role-specific fields. |