Nail'd It | Master Database Schema

1. PostgreSQL (Core Relational Truth)

users Master Auth & Identity

Column	Type	Notes
id	uuid	Primary Key
company_id	uuid	FK -> companies.id
role	enum	'GC', 'SUB', 'SUPPLIER'
first_name	varchar(50)
last_name	varchar(50)
phone	varchar(20)	Used for SMS cascade
ecosystem_score	numeric(3,1)	Aggregate Trust Commons score
tier_status	integer	1, 2, or 3

companies Business Entities

Column	Type	Notes
id	uuid	Primary Key
name	varchar(100)	DBA Name
trade_category	varchar(50)	From Estimator matrix
tax_ein	varchar(20)	Encrypted
insurance_coi_url	text	S3 Bucket Link
insurance_exp_date	date	Triggers COI renewal alert
bonding_capacity	numeric	For Subs/GCs

projects Project Containers

Column	Type	Notes
id	uuid	Primary Key
gc_company_id	uuid	FK -> companies.id
name	varchar(100)	e.g., 'Oak Flow Subdivision'
address	text
latitude	numeric(10,8)	Crucial for hyper-local weather
longitude	numeric(11,8)	Crucial for hyper-local weather
status	enum	PRE_CON, ACTIVE, CLOSED
total_budget	numeric

trust_ratings Double-Blind Engine

Column	Type	Notes
id	uuid	Primary Key
project_id	uuid	FK -> projects.id
rater_company_id	uuid	Who is rating
target_company_id	uuid	Who is being rated
status	enum	PENDING, LOCKED, REVEALED
dimensions	jsonb	e.g., {"quality": 9, "pay": 10}
overall_score	numeric(3,1)	Calculated average
disputed_flag	boolean	Triggers dispute resolution

financial_draws A/R & Payments

Column	Type	Notes
id	uuid	Primary Key
project_id	uuid	FK -> projects.id
payee_company_id	uuid	Sub or Supplier
amount	numeric
status	enum	SUBMITTED, APPROVED, PAID, LATE
lien_waiver_status	enum	REQUIRED, SIGNED, EXEMPT
quickbooks_sync_id	varchar	External ERP mapping

2. Neo4j (Graph - Schedule DAG)

(Activity) Node

Properties:
• id: uuid (Maps to Postgres)
• project_id: uuid
• name: string (e.g., 'Slab Pour')
• duration_hours: int
• float_days: int (Calculated)
• is_critical_path: boolean
• weather_sensitivity: string ('CRITICAL', 'SENSITIVE', 'INDEPENDENT')
• state: string ('SCHEDULED', 'WEATHER_HOLD', 'CONFIRMED')

-[DEPENDS_ON]-> Edge

Properties:
• type: string ('FS' Finish-to-Start, 'SS' Start-to-Start)
• lag_hours: int (e.g., 48 hours for concrete curing before framing)

Graph Traversal Value: Instantly calculates cascading delays. When Node A is pushed 24H, Cypher query traverses all -[DEPENDS_ON]-> edges to update successors and identify float availability.

-[ASSIGNED_TO]-> Edge

Properties:
• company_id: uuid (Subcontractor mapping)
• confirmation_status: string ('PENDING', 'ACKNOWLEDGED')

Graph Traversal Value: Allows the cascade engine to instantly find all Subcontractors affected by a node shift, triggering the Twilio SMS routing.

Column	Type	Notes
time	timestamptz	Hyper-table partition key
project_id	uuid	Indexed
temp_f	numeric(4,1)
wind_speed_mph	numeric(4,1)
precip_probability	integer	0-100
disruption_magnitude	numeric(3,1)	Calculated (0.0 - 10.0)
forecast_confidence	integer	Decays over future horizons

1. PostgreSQL (Core Relational Truth)

2. Neo4j (Graph - Schedule DAG)

3. TimescaleDB (Weather Telemetry)