Database Schema

Overview

Pinley Mechanical uses PostgreSQL as its primary data store, accessed through GORM (Go ORM) with code-first migrations. The schema consists of 41 tables organized into 13 modules.

The canonical schema definition lives in schema.dbml (DBML format). Paste it into dbdiagram.io to generate visual ERD diagrams.

Design Decisions

Decision	Choice	Rationale
ID type	uint32 auto-increment	Matches existing User/Role/Permission models
Money fields	bigint (cents)	Avoids floating-point precision issues
Enum enforcement	VARCHAR + CHECK constraints	GORM-compatible, visible in schema
Array fields	PostgreSQL text[]	Simple arrays for endorsements, clauses
Complex nested data	jsonb	Bid parties, timeline metadata
Soft delete	GORM DeletedAt on mutable entities	Skipped on immutable: coi_revisions, audit_log_entries, compliance_issues
Audit log FKs	None (denormalized strings)	Must survive deletion of referenced entities
File storage	Centralized files table	All uploaded files reference files.id; URLs computed via GORM AfterFind hook
Locations	google_places table with FK references	Structured location data via Google Places API instead of free-text varchar fields
Party model	Unified Organization model with multi-role tagging	Any external party (subcontractor, vendor, client, broker, owner's rep, property manager, GC) is an Organization record tagged with one or more roles via organization_roles; a single company can hold multiple roles across projects

Module Overview

The database is organized into 12 logical modules:

graph LR
    Auth["Auth<br/>(9 tables)"]
    Files["Files<br/>(1 table)"]
    Projects["Projects<br/>(2 tables)"]
    Locations["Locations<br/>(1 table)"]
    Organizations["Organizations<br/>(4 tables)"]
    Insurance["COI / Insurance<br/>(3 tables)"]
    DocCompliance["Documents /<br/>Compliance<br/>(4 tables)"]
    DocMgmt["Document<br/>Management<br/>(2 tables)"]
    Indemnity["Indemnity<br/>(2 tables)"]
    Incidents["Incidents<br/>(5 tables)"]
    Bids["Bids<br/>(4 tables)"]
    Tasks["Tasks<br/>(1 table)"]
    BmsDirectory["BMS Directory<br/>(2 tables)"]
    AuditLog["Audit Log<br/>(1 table)"]

    Projects --> Organizations
    Projects --> Insurance
    Projects --> Locations
    Organizations --> Locations
    Insurance --> DocCompliance
    Projects --> DocMgmt
    Projects --> Indemnity
    Projects --> Incidents
    Incidents --> Locations
    Bids --> Projects
    Bids --> Locations
    Tasks --> Projects
    Tasks --> Incidents
    Tasks --> Bids
    Files --> DocCompliance
    Files --> DocMgmt
    Files --> Bids
    BmsDirectory --> Organizations

Auth Module (9 tables — existing)

Pre-existing tables from the monorepo template. Manages users, roles, permissions, and social auth.

Table	Purpose
users	Internal user accounts
roles	Named roles (Admin, PM, Compliance Officer, Field User, Viewer)
permissions	Granular permissions (32 defined)
role_permissions	Role ↔ permission mapping
user_roles	User ↔ role mapping
role_parents	Role inheritance hierarchy
auth_providers	OAuth provider registry
user_socials	User ↔ OAuth provider links
database_seeders	Migration/seed tracking

Files Module (1 table)

Centralized file storage reference. All entities that store uploaded files reference files.id instead of storing URLs directly.

erDiagram
    files {
        uint id PK
        int filesize "bytes"
        varchar original_name
        varchar disk_name "Azure Blob path"
        varchar thumbnail_disk_name "nullable"
        varchar mime_type
        uint uploaded_by FK
        timestamp created_at
        timestamp updated_at
        timestamp deleted_at
    }
    files ||--o{ documents : "file_id"
    files ||--o{ general_documents : "file_id"
    files ||--o{ bid_documents : "file_id"
    files }o--|| users : "uploaded_by"

Full URLs are computed via a GORM AfterFind hook using Azure Blob Storage configuration.

Locations Module (1 table)

Structured location data sourced from the Google Places API (new). Replaces free-text varchar location fields across the schema. Multiple entities reference google_places via nullable FKs: projects, organizations, incidents, and bids.

Implementation status (PIN-70, Jun 2026): GooglePlaceService (AddGooglePlace RPC) live. PlaceAutocomplete component wired to ProjectForm, AddBidInviteModal, VendorForm, and ClientForm. Uses Places API (new) REST endpoints directly — no Maps JS SDK required. Upserts by place_id (duplicate-safe). VITE_GOOGLE_PLACES_KEY env var required.

erDiagram
    google_places {
        uint id PK
        varchar place_id UK "Google Places API ID"
        varchar formatted_address
        numeric lat
        numeric lng
        varchar city
        varchar state
        varchar zip
        varchar country
        jsonb raw_json "full API response"
    }
    projects }o--o| google_places : "google_place_id"
    organizations }o--o| google_places : "google_place_id"
    incidents }o--o| google_places : "google_place_id"
    bids }o--o| google_places : "google_place_id"

Projects Module (2 tables)

erDiagram
    projects {
        uint id PK
        varchar name
        varchar status "active/completed/archived"
        uint project_manager_id FK
        uint google_place_id FK "nullable"
        uint contracting_counterparty_id FK "nullable"
        uint client_id FK "nullable"
        uint property_manager_id FK "nullable"
        uint owners_rep_id FK "nullable"
        date start_date
        int compliance_percentage "0-100"
        bool ready_for_mobilization
    }
    project_users {
        uint id PK
        uint project_id FK
        uint user_id FK
    }
    projects ||--o{ project_users : "has members"
    projects }o--|| users : "project_manager"
    projects }o--o| google_places : "location"
    projects }o--o| organizations : "contracting_counterparty"
    projects }o--o| organizations : "client"
    projects }o--o| organizations : "property_manager"
    projects }o--o| organizations : "owners_rep"

The four nullable FK fields (contracting_counterparty_id, client_id, property_manager_id, owners_rep_id) are denormalized shortcuts for the most common party lookups on a project. The full many-to-many relationship with role tracking lives in project_organizations.

Organizations Module (4 tables)

Unified model for all external parties. A single organizations record can represent a subcontractor, vendor, client, broker, owner's rep, property manager, general contractor, architect, or engineer. Roles are tagged at the company level via organization_roles and at the project level via project_organizations, allowing one company to hold multiple roles across projects.

erDiagram
    organizations {
        uint id PK
        varchar name "company name"
        varchar trade "nullable — primary trade/specialty"
        varchar email "company-level"
        varchar phone "company-level"
        varchar address "display text"
        uint google_place_id FK "nullable"
    }
    organization_contacts {
        uint id PK
        uint organization_id FK
        varchar name
        varchar email
        varchar phone
        varchar department "trade/department"
        bool is_primary
    }
    organization_roles {
        uint id PK
        uint organization_id FK
        varchar role "subcontractor/vendor/client/broker/etc"
    }
    project_organizations {
        uint id PK
        uint project_id FK
        uint organization_id FK
        varchar role "role on this project"
        varchar compliance_status "compliant/issues/missing/expired"
        varchar organization_status "pending/approved/po_issued/po_signed"
        date deadline
        bool upload_link_sent
    }
    organizations ||--o{ organization_contacts : "contacts"
    organizations ||--o{ organization_roles : "roles"
    organizations ||--o{ project_organizations : "assigned to"
    projects ||--o{ project_organizations : "has organizations"
    organizations }o--o| google_places : "location"

COI / Insurance Module (3 tables)

erDiagram
    cois {
        uint id PK
        uint project_id FK
        varchar name
    }
    coi_revisions {
        uint id PK
        uint coi_id FK
        int revision_number
        varchar change_note
        uint created_by FK
    }
    policy_requirements {
        uint id PK
        uint coi_revision_id FK
        varchar policy_type "gl/auto/umbrella/wc"
        bigint min_limit "cents"
        bigint aggregate_limit "cents"
    }

    projects ||--o{ cois : "has COIs"
    cois ||--o{ coi_revisions : "revisions"
    coi_revisions ||--o{ policy_requirements : "requirements"

No Insurance Templates

Insurance templates were removed for MVP. AI-powered compliance checking handles requirement validation directly when subcontractors upload COI documents, eliminating the need for manually configured templates.

Immutability Rule

coi_revisions are immutable — never update or delete an existing revision. Always create a new revision with an incremented revision_number. The table has no updated_at or deleted_at columns by design.

Documents / Compliance Module (4 tables)

erDiagram
    documents {
        uint id PK
        uint project_organization_id FK
        uint file_id FK
        varchar policy_type
        varchar carrier
        varchar policy_number
        date expiration_date
        bigint per_occurrence_limit "cents"
        varchar status "pending/approved/rejected/expired"
    }
    compliance_issues {
        uint id PK
        uint document_id FK
        varchar type
        varchar severity "error/warning"
        varchar message
    }
    risk_tags {
        uint id PK
        uint document_id FK
        varchar tag
        uint tagged_by FK
    }
    carrier_ratings {
        uint id PK
        varchar name UK
        varchar rating "A/B/C/D"
    }

    documents ||--o{ compliance_issues : "issues"
    documents ||--o{ risk_tags : "tags"
    project_organizations ||--o{ documents : "uploads"
    files ||--|| documents : "file_id"

Compliance Issues — AI-Generated

compliance_issues are generated by AI when a subcontractor uploads COI data. The table structure (type, severity, message, field, expected, actual) supports AI-generated output. Issues are regenerated on each compliance check — the table has no updated_at or deleted_at.

Document Management Module (2 tables)

General-purpose document storage with folder organization and optional SharePoint sync.

Table	Purpose
document_folders	Organizes documents by project; system or user-created. Scope: project or organization
general_documents	Files stored in folders with optional SharePoint sync. Optionally linked to an organization via organization_id

Indemnity Module (2 tables)

Upload-only model: PM creates an indemnity with a name and template PDF. Each organization on the project gets an indemnity_submission entry. The organization downloads the template, signs it, and uploads the signed copy as a general_document. Status tracks the workflow: pending → submitted → approved / rejected.

Table	Purpose
indemnities	Indemnity agreement definitions per project — name + template PDF only (no clause processing)
indemnity_submissions	Per-organization submission tracking with status workflow

Incidents Module (5 tables)

erDiagram
    incidents {
        uint id PK
        uint project_id FK
        uint organization_id FK "nullable"
        uint project_organization_id FK "nullable"
        varchar title
        varchar category
        date date
        varchar severity "low/medium/high/critical"
        uint google_place_id FK "nullable"
        varchar status "open/investigating/closed"
    }
    incident_involved_persons {
        uint id PK
        uint incident_id FK
        varchar name
        varchar role
        bool was_hospitalized
    }
    incident_claims {
        uint id PK
        uint incident_id FK
        varchar claim_type
        varchar claim_number
        bigint amount "cents"
        bigint settled_amount "cents"
    }
    incident_costs {
        uint id PK
        uint incident_id FK
        varchar category
        bigint amount "cents"
    }
    incident_timeline_entries {
        uint id PK
        uint incident_id FK
        varchar type
        text description
        jsonb metadata
    }

    incidents ||--o{ incident_involved_persons : "involved"
    incidents ||--o{ incident_claims : "claims"
    incidents ||--o{ incident_costs : "costs"
    incidents ||--o{ incident_timeline_entries : "timeline"
    projects ||--o{ incidents : "has incidents"
    incidents }o--o| google_places : "location"
    incidents }o--o| organizations : "organization"

Detailed Incident Model Retained

Client confirmed (March 10, 2026): the detailed incident model (claims + costs) is acceptable as a back-end feature. Incidents are infrequent, so the UI should be simple, but the data must be robust enough to generate historical reports on claims and final settlement amounts.

Timeline Entries

incident_timeline_entries is append-only — entries are never updated or deleted.

Bids Module (4 tables)

erDiagram
    bids {
        uint id PK
        varchar bid_number UK
        varchar project_name
        uint google_place_id FK "nullable"
        varchar status "new/reviewing/estimating/submitted/won/lost"
        bigint estimated_amount "cents"
        bigint contract_amount "cents"
        uint project_id FK "nullable - set on conversion"
    }
    bid_documents {
        uint id PK
        uint bid_id FK
        uint file_id FK
        varchar file_type "plans/specs/proposal/etc"
        bool ai_analyzed
    }
    bid_vendors {
        uint id PK
        uint bid_id FK
        varchar name
        varchar trade
        varchar status "pending/requested/received/selected"
        bigint quote_amount "cents"
        uint organization_id FK "nullable"
    }
    estimate_line_items {
        uint id PK
        uint bid_id FK
        varchar category
        numeric quantity
        bigint unit_cost "cents"
        bigint total "cents"
    }

    bids ||--o{ bid_documents : "documents"
    bids ||--o{ bid_vendors : "vendors"
    bids ||--o{ estimate_line_items : "line items"
    bids }o--o| google_places : "location"
    estimate_line_items }o--o| bid_vendors : "vendor_id"
    bid_vendors }o--o| organizations : "organization"

Bids exist independently until won, at which point project_id is set linking them to a created project.

Tasks Module (1 table)

Tasks are polymorphic — they can link to projects, incidents, bids, organizations, or documents via nullable FKs. They are created both manually and by the system (e.g., compliance failures generate tasks automatically).

Column	Purpose
source	manual (user-created) or system (auto-generated)
link_label / link_path	Deep link to related entity in the UI
project_id, incident_id, bid_id, organization_id, document_id	Nullable FKs for polymorphic linking

BMS Directory Module (2 tables)

Tracks which BMS (Building Management System) / controls vendor is installed at each building. BMS vendors are real Organization records tagged with the vendor role.

erDiagram
    building_bms {
        uint id PK
        varchar address
        bool is_multi_system "manual flag"
        text notes "nullable"
        timestamp deleted_at "soft delete"
    }
    building_bms_organizations {
        uint building_bms_id FK
        uint organization_id FK
    }
    organizations {
        uint id PK
        varchar name
    }

    building_bms ||--o{ building_bms_organizations : "vendors"
    building_bms_organizations }o--|| organizations : "organization"

Column	Notes
is_multi_system	Manual checkbox — not auto-computed from vendor count
building_bms_organizations	Junction table; CASCADE deletes when the Organization is removed

Audit Log Module (1 table)

erDiagram
    audit_log_entries {
        uint id PK
        uint project_id "NO FK - denormalized"
        varchar action
        varchar actor "user/system"
        uint actor_id "NO FK"
        varchar actor_name
        varchar target_type
        uint target_id
        varchar target_name
        text details
        jsonb metadata
        timestamp created_at
    }

Immutability Rule

audit_log_entries are immutable — no updates, no deletes, no foreign keys. All referenced data is denormalized (names stored as strings) so entries survive deletion of referenced entities.

Index Strategy

Foreign Key Indexes

Every foreign key column gets an index. GORM creates indexes automatically for belongs_to associations; explicit indexes are added for non-GORM-detected FKs.

Uniqueness Constraints

Table	Unique Index
project_users	(project_id, user_id)
organization_roles	(organization_id, role)
project_organizations	(project_id, organization_id, role)
coi_revisions	(coi_id, revision_number)
policy_requirements	(coi_revision_id, policy_type)
document_folders	(project_id, name, scope)
indemnity_submissions	(indemnity_id, project_organization_id)

Status / Filter Indexes

Table	Indexed Column(s)
projects	status
organization_contacts	organization_id
cois	project_id
project_organizations	(project_id, compliance_status)
documents	status, expiration_date, policy_type, (project_organization_id, status)
incidents	project_id, status, severity
bids	status
tasks	status, priority, category, (project_id, status), (assignee_id, status)
audit_log_entries	project_id, action, created_at, actor_id

Resolved Client Confirmations (March 10, 2026)

These items were confirmed during the sprint demo meeting with Conor (HVAC Construction Inc.):

• Unified Organization model — all external parties (subcontractors, vendors, clients, brokers, owner's reps, property managers, GCs) are Organization records tagged with roles; replaces separate party tables
• Incidents: keep full detail — claims + costs retained for historical reporting; UI kept simple
• Indemnity: upload-only — no clause processing, just name + template PDF + signed upload
• COI setup simplified — no insurance templates; AI handles compliance checking directly
• Organization contact model — multiple contacts per organization with primary/department support
• Google Places integration — structured location data replacing free-text varchar fields