Build client quotes from one rate card instead of a spreadsheet

Build me an app for putting together client quotes off a rate card we maintain in one place, replacing the pricing spreadsheet we currently copy and mangle for every new deal. Sales and ops both work out of it. All the data lives in the General Input Database, the quote document is created and laid out in Google Docs, and the finished quote goes out through Gmail.

Data model. On first load, use Create or List Databases to get a database, then use Execute SQL to create the tables if they do not exist. rate_card_items: name, category, description, unit (hour, day, seat, project, licence and so on), unit_price, currency, is_active, updated_at. quotes: client_name, client_company, contact_email, client_context (free text notes about the client, their situation and what they asked for), status (draft, sent, accepted, lost), discount_percent, subtotal, discount_amount, total, google_doc_id, google_doc_url, narrative_status (none, running, ready, reviewed), narrative_reviewed_by, narrative_reviewed_at, created_at, sent_at, decided_at, owner. quote_line_items: quote_id, rate_card_item_id, name_snapshot, unit_snapshot, unit_price_snapshot, quantity, line_total. quote_status_events: quote_id, from_status, to_status, changed_by, note, changed_at. Also keep a small settings row for the discount threshold and the default terms text.

The snapshot columns are the whole point, so do not skip them. When a line item is added to a quote, copy the name, unit and unit price out of the rate card into the quote line row and do the maths from those copies. Later edits to the rate card must never change the numbers on a quote that already exists. On the rate card screen, editing a price should say plainly that it only affects future quotes.

Rate card view. A searchable catalog of services grouped by category, each showing the unit price, the unit, a short description and whether it is active. Let someone add an item, edit one, or retire one so it stops appearing in the picker without disturbing history. Show how many quotes each item currently appears on.

Quotes list view. Every quote with its client, owner, status, total value, discount percent and age in days (since creation for drafts, since it was sent for anything waiting on a decision). Filter by status and owner, sort by value or age. Anything discounted past the threshold gets a visible flag in the row, not a subtle colour change. Quotes that have been sitting in sent for too long get called out too. Changing status writes a row into quote_status_events.

Quote builder. Pick the client details and write the client context notes, then add line items from the rate card with a quantity each. Show live subtotal, discount amount and total as items go in. Setting a discount above the threshold shows a warning right there and marks the quote as needing approval before it can be sent. Saving calls a handler that writes the quotes row and the quote_line_items rows with Execute SQL in one go, snapshotting each price.

Rendering the document. A Generate document action creates the quote document with Google Docs Create Document, titled with the client name and date, then uses Batch Update Document to lay out the body: a header with your company and the client details, an optional cover and scope section, a table of line items with columns for service, unit, unit price, quantity and line total, then subtotal, discount and total rows, then the terms. Store the document id and link on the quote. Re-generating an existing quote should clear the previous body and rewrite it rather than piling a second copy underneath.

Background agent. Put a Draft the proposal narrative button on the quote. It kicks off a background agent that takes the selected line items and the client context saved on the quote, writes a tailored scope of work section and a covering section that reflects what was actually quoted, and inserts them into the document above the line item table using Batch Update Document. The agent writes the drafted text and narrative_status back onto the quotes row so the app can show progress and then the result: the quote screen shows running while it works and ready when it lands, with the drafted wording visible in the app next to a link to the document. The agent never emails anything.

Review gate. Nothing goes to a client without an explicit review step. The send action stays disabled until someone opens the review panel, reads the document and anything the agent drafted, and marks it reviewed, which records who reviewed it and when. Quotes over the discount threshold need an approval recorded as well. Sending then uses Gmail Send a Message to the contact email with a short covering note and the document link, flips the status to sent, stamps sent_at and logs the status event.

Win analysis view. Roll the quote line rows up by rate card item: how many quotes it appeared on, how many of those were accepted, the win rate, the average discount applied, and the revenue won. Let someone filter to a date range and hide items below a minimum number of quotes so a single lucky sale does not read as a 100 percent win rate. This is the view that tells us which services actually close.

General behaviour: format money properly with the currency, ask for confirmation before marking a quote lost, and keep everyone on the same shared data so sales and ops never disagree about a price.