apoorvgarg31/buildai-database

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) |