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) |
1---2name: buildai-database3description: Query the BuildAI PostgreSQL database (read-only). Returns results as JSON for construction project data — projects, RFIs, submittals, budgets, change orders, daily logs, punch lists, insurance, vendors, pay applications.4---56# BuildAI Database Query78Execute read-only SQL queries against the BuildAI PostgreSQL database (`buildai_demo`).910## Usage1112```bash13bash command:"cd /home/apoorvgarg/buildai/packages/engine/skills/buildai-database && bash query.sh 'SELECT * FROM projects LIMIT 5'"14```1516## Parameters1718The script takes a single argument: a SQL query string.1920**Safety:**21- Only SELECT and WITH (CTE) queries are allowed22- INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, GRANT, REVOKE, EXEC are rejected23- Results are returned as JSON24- Queries are limited to 30 seconds2526## Database Schema2728### Tables2930| Table | Description |31|-------|-------------|32| `projects` | Construction projects with status, contract sums, dates, stakeholders |33| `rfis` | Requests for Information — open/closed/void, with priority and assignee |34| `submittals` | Material/shop drawing submittals with approval tracking |35| `budget_line_items` | Cost codes with original/revised/committed/actual budget breakdowns |36| `change_orders` | Change order packages with amounts and reasons |37| `daily_logs` | Daily site logs — weather, workforce, delays, safety incidents |38| `punch_list_items` | Punch list items with status and priority |39| `insurance_certs` | Vendor insurance certificates with expiration tracking |40| `vendors` | Subcontractors/vendors with contract amounts and payments |41| `pay_applications` | Payment applications (AIA G702/G703 style) |4243### Pre-Built Views (prefer these for common queries)4445| View | Best For |46|------|----------|47| `v_project_dashboard` | Project overview — open RFIs, pending submittals, expiring certs |48| `v_overdue_rfis` | Overdue RFIs with days overdue |49| `v_expiring_insurance` | Insurance certs expiring within 90 days |50| `v_project_budget_summary` | Budget totals with variance analysis |5152### Key Columns5354**projects:** id, name, address, city, state, status (active/completed/on_hold), contract_sum, start_date, projected_completion, project_type5556**rfis:** id, project_id, number, subject, status (open/closed/void), priority (normal/urgent/critical), assigned_to, due_date, days_open5758**budget_line_items:** id, project_id, cost_code, description, original_budget, revised_budget, committed_costs, actual_costs, projected_final, variance, variance_percent5960## Examples6162```sql63-- Active projects overview64SELECT * FROM v_project_dashboard WHERE status = 'active';6566-- Overdue RFIs67SELECT * FROM v_overdue_rfis ORDER BY days_overdue DESC;6869-- Budget overruns70SELECT * FROM v_project_budget_summary WHERE total_variance < 0;7172-- Expiring insurance in next 30 days73SELECT * FROM v_expiring_insurance WHERE days_until_expiration <= 30;7475-- Workforce trends for a project76SELECT log_date, workforce_count, weather FROM daily_logs77WHERE project_id = 1 ORDER BY log_date DESC LIMIT 14;78```7980## Environment Variables8182| Variable | Default | Description |83|----------|---------|-------------|84| `DB_HOST` | `/var/run/postgresql` | PostgreSQL host |85| `DB_PORT` | `5432` | PostgreSQL port |86| `DB_NAME` | `buildai_demo` | Database name |87| `DB_USER` | `$USER` | Database user |88| `DB_PASSWORD` | (none) | Database password (optional for local peer auth) |
FDU-INS