BuildAI Database Query

Execute read-only SQL queries against the BuildAI PostgreSQL database (buildai_demo).

Usage

bash command:"cd /home/apoorvgarg/buildai/packages/engine/skills/buildai-database && bash query.sh 'SELECT * FROM projects LIMIT 5'"

Parameters

The script takes a single argument: a SQL query string.

Safety:

• Only SELECT and WITH (CTE) queries are allowed
• INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, GRANT, REVOKE, EXEC are rejected
• Results are returned as JSON
• Queries are limited to 30 seconds

Database Schema

Tables

Table	Description
projects	Construction projects with status, contract sums, dates, stakeholders
rfis	Requests for Information — open/closed/void, with priority and assignee
submittals	Material/shop drawing submittals with approval tracking
budget_line_items	Cost codes with original/revised/committed/actual budget breakdowns
change_orders	Change order packages with amounts and reasons
daily_logs	Daily site logs — weather, workforce, delays, safety incidents
punch_list_items	Punch list items with status and priority
insurance_certs	Vendor insurance certificates with expiration tracking
vendors	Subcontractors/vendors with contract amounts and payments
pay_applications	Payment applications (AIA G702/G703 style)

Pre-Built Views (prefer these for common queries)

View	Best For
v_project_dashboard	Project overview — open RFIs, pending submittals, expiring certs
v_overdue_rfis	Overdue RFIs with days overdue
v_expiring_insurance	Insurance certs expiring within 90 days
v_project_budget_summary	Budget totals with variance analysis

Key Columns

projects: id, name, address, city, state, status (active/completed/on_hold), contract_sum, start_date, projected_completion, project_type

rfis: id, project_id, number, subject, status (open/closed/void), priority (normal/urgent/critical), assigned_to, due_date, days_open

budget_line_items: id, project_id, cost_code, description, original_budget, revised_budget, committed_costs, actual_costs, projected_final, variance, variance_percent

Examples

-- Active projects overview
SELECT * FROM v_project_dashboard WHERE status = 'active';

-- Overdue RFIs
SELECT * FROM v_overdue_rfis ORDER BY days_overdue DESC;

-- Budget overruns
SELECT * FROM v_project_budget_summary WHERE total_variance < 0;

-- Expiring insurance in next 30 days
SELECT * FROM v_expiring_insurance WHERE days_until_expiration <= 30;

-- Workforce trends for a project
SELECT log_date, workforce_count, weather FROM daily_logs
WHERE project_id = 1 ORDER BY log_date DESC LIMIT 14;

Environment Variables

Variable	Default	Description
DB_HOST	/var/run/postgresql	PostgreSQL host
DB_PORT	5432	PostgreSQL port
DB_NAME	buildai_demo	Database name
DB_USER	$USER	Database user
DB_PASSWORD	(none)	Database password (optional for local peer auth)