← Open estimating workspaceCrewTend Estimating Engine — implementation guide
Part 1: Database schema and API structure
The implementation is in the existing CrewTend application. D1/SQLite stores structured records; private R2 objects hold documents. The generated Drizzle migration is drizzle/0004_lying_drax.sql; the source definitions are in db/schema.ts. Existing payment and work-order tables are unchanged.
| Model | Responsibility |
|---|
| estimate_guest_sessions | Random guest session, hashed token, creation and seven-day expiry. The raw token exists only in a Secure, HttpOnly, SameSite=Lax cookie. |
| estimate_projects | Guest or authenticated owner, sector, title, current normalized snapshot, revision and analysis lease. |
| estimate_bid_phases | Phase names, sort order and completion, keyed by project and revision. |
| estimate_line_items | CSI division, units, quantities scaled to thousandths, nullable integer-cent cost categories, wage flag and source record. |
| estimate_pricebook | Account-owned material, non-union and union rates, unit and provenance. |
| estimate_documents | Private object key, MIME type, byte count, SHA-256, original source link and project owner relationship. |
| estimate_events | Revision history, AI model/response metadata, source hashes and saved estimate snapshot. |
| estimate_quotas | Atomic daily preview and cost limits. |
| estimate_oauth / estimate_oauth_states | Encrypted Google refresh tokens and expiring, single-use OAuth state with PKCE. |
API prefix: /api/estimating. Mutations require same-origin requests and X-CrewTend-Estimate: 1. Authenticated identity comes from Sites' trusted user header, not a client-supplied account ID. These are private account-owned estimates; a shared company-membership and role API is a separate extension, not assumed from a browser company selector.
| Method and route | Behavior |
|---|
| GET /session | Availability, sign-in state and the current account/guest project list. |
| POST /session | Start an anonymous session with a private cookie. |
| POST /projects | Create a blank or explicitly labeled sample project. |
| GET /projects/:id | Authorized project, documents and recent revision history. |
| PUT /projects/:id | Validate and save using the expected revision; stale saves return 409. |
| POST /projects/:id/claim | Move a guest estimate into the signed-in account; revoke anonymous access. |
| POST /projects/:id/documents | Upload verified PDF/JPEG/PNG/WebP bytes. |
| GET or DELETE /projects/:id/documents/:document | Authorized attachment download or removal. |
| POST /projects/:id/link | Resolve a permitted public file/page; read linked PDFs where available. |
| POST /projects/:id/analyze | Budget-limited, structured AI analysis with an exclusive lease. |
| GET or POST /pricebook | Read or save the account's reusable rates. |
| POST /google/connect | Start Google OAuth with drive.file scope. |
| GET /google/callback | Verify state, exchange code and encrypt the refresh token. |
| POST /google/export | Convert the saved branded proposal into an editable Google Doc. |
Part 2: Public guest sandbox
- On the homepage, drop plans or paste a public link. The dedicated workspace is
/#ai-estimator. - Start a private guest session, choose a sector, and create a project.
- Upload files or import a link. Limits: five documents and 20 MB total per estimate; 10 MB per file.
- Edit scope, line items and markups. Changes remain editable before login. Use Save changes to persist them.
- Select Save to my account. Sign in with the site's ChatGPT account flow; the same project is claimed after returning. The server verifies both guest possession and authenticated identity.
- Return to My estimates to reopen account-owned projects. Guest access lasts seven days. Hourly-on-activity cleanup and the scheduled handler remove expired guest projects and private files.
Guest tokens are not localStorage bearer tokens or public estimate IDs. Knowing a project URL alone does not allow reading its files. The user can download estimates without signing in. Account claiming does not duplicate the project.
Part 3: Ingestion and structured AI pipeline
server/estimate-ingestion.js accepts PDF/JPEG/PNG/WebP by actual file signature, not an upload label. Downloads use attachment disposition and nosniff. Objects are private and use random names. Public HTTPS URL support includes Google Drive, Google Docs, Dropbox, government sites and common public cloud object storage. Restricted portals and login-required files must be downloaded and uploaded manually. Additional trusted hostnames can be configured; redirects are checked at every hop. No user credentials or session cookies are forwarded to source websites.
server/estimate-ai.js sends PDF pages, images or extracted page text to the configured vision-capable model through OpenAI Responses. The strict schema requires phases, line items, source document/page/excerpt and assumptions. The prompt treats all documents as untrusted input and never gives them tool-execution authority. Unknown prices and quantities remain null. Dimensional inference requires a known scale or supplied dimensions. No model-generated total is trusted: the shared calculator recomputes it.
AI drafts are preliminary. Industrial and governmental entries support two-digit CSI classification, prevailing-wage review flags, separate union/non-union rates, shift differential and safety allowance. They do not establish a wage determination, certify a bid or issue an official AIA document. Supplier quotations and pricebook entries must be supplied and reviewed; there is no automatic live supplier-price feed.
The endpoint returns an explicit connection error when AI is not configured. It does not generate a fabricated “AI result.” The sample project is visibly fictional. Initial guest limits are three analyses per day, with a global daily cap of 50. Configurable limits are bounded server-side. Analysis holds a two-minute lease and preserves conflicting newer saves.
Part 4: Review and calculation
The three tabs are Bid schedule / summary, Takeoff breakdown, and Assumptions & sources.
- Line total = quantity × (material + selected labor × (1 + shift %) + equipment + subcontract + other).
- Blank costs are unpriced, while explicit zero means not applicable. Unpriced lines are excluded and counted visibly.
- Direct cost includes the safety allowance. Overhead, general liability and contingency are percentages of direct cost.
- Profit is a markup on the resulting cost subtotal, not a target profit margin.
- Phased values distribute the final total proportionally, with cent rounding reconciled to the last phase.
- Amounts use integer cents; quantities normalize to thousandths. Unsafe numerical totals are rejected.
- Save uses a compare-and-swap revision. D1 batches gate phase, line and event inserts on that mutation ID, preventing stale edits from partially replacing a project.
Part 5: Export modules
dist/estimating-exports.js lazily loads locally hosted ExcelJS, docx and pdf-lib. No document content is sent to an external converter.
- Excel: Summary, Detailed Takeoffs, Schedule of Values and Pricing Sources. Cost formulas, cached totals, phase values, completion and balances recalculate on opening. USD is the supported currency.
- Word: branded scope, line items, summary, phased values, assumptions, source audit and an unsigned acceptance block.
- PDF: paginated proposal containing the same audit content and an unsigned acceptance block. The current PDF font targets English/Latin text; use Word for full multilingual typography.
- Google Docs: the saved estimate becomes escaped HTML, uploaded through Drive with conversion to the Google Docs MIME type. OAuth uses drive.file scope, PKCE, one-time state and an AES-GCM-encrypted refresh token. Each export creates a new document in the connected user's Drive. The account must approve Google access.
A downloadable proposal is not automatically sent, signed or accepted. Digital signatures and payment actions remain in CrewTend's existing document workflow. SOV exports are generic formats, not licensed AIA G702/G703 forms.
Part 6: Activation
| Setting | Purpose |
|---|
| OPENAI_API_KEY | Server-side OpenAI key, never exposed to clients. |
| ESTIMATE_AI_ENABLED=true | Explicitly enable public plan analysis after configuring budgets. |
| ESTIMATE_AI_MODEL | Optional compatible model override; current default gpt-4.1. |
| ESTIMATE_DAILY_LIMIT | Global analysis request cap; default 50, maximum 500. |
| ESTIMATE_ALLOWED_HOSTS | Optional comma-separated, administrator-reviewed source hosts. |
| GOOGLE_ESTIMATE_CLIENT_ID | Google OAuth web-client ID. |
| GOOGLE_ESTIMATE_CLIENT_SECRET | Google OAuth secret, server-only. |
| GOOGLE_ESTIMATE_TOKEN_KEY | Base64url encoding of a random 32-byte AES key. Keep stable while stored tokens are in use. |
Register https://crewtend.com/api/estimating/google/callback as the Google redirect URI, enable Drive API, and configure the OAuth consent screen/test users. The native site hostname needs its own registered callback if Google export is used there. Set secrets through the hosting environment, never in committed files. For OpenAI key setup, enable the OpenAI Developers connection so the owner can approve the key.
Without those external connections, guest editing, private storage, account claiming, manual calculations, reusable pricebook entries, and Excel/Word/PDF exports work independently. AI parsing and Google Docs export stay visibly unavailable until connected.
Part 7: Verification and operating boundaries
tests/estimating53.mjs runs migrations in SQLite and exercises private sessions, ownership transfer, cross-account denial, stale revisions, MIME checks, URL restrictions, unpriced arithmetic, the structured AI response adapter with a fixture, and reopening XLSX/DOCX/PDF exports. Existing catalog, industry, billing and voice tests also run.
Production AI-provider calls, Google consent/export, and iPhone microphone behavior require connected accounts and device verification. Public upload storage and AI usage need monitored budgets; the implemented quotas are a first bounded release. This module is not a certified estimating, wage-compliance or engineering takeoff service.
Primary implementation references