Responses to each question from your gist. The updated schema doc reflects all decisions below.
Yes, 100%. This is one of Chad's top needs and nothing else on the market is meeting it for him. The flow: collect quotes from subs (1-4 per budget category), assemble into a client-facing proposal, and upon acceptance, promote winning quotes directly into the working budget.
We haven't built this yet (the current app started after Chad's project was already underway), but he has a giant binder of printed quotes that'll be our seed data. The quote table has been added to the schema for this.
That said, this isn't a CRM. It's not about managing lists of leads. It's about the real potential projects you're already investing effort into getting, and giving the contractor visibility into how much time/money/energy goes into that pre-commitment phase.
Schema change: Added quote table (project-scoped, per-budget-line vendor bids with status tracking). Added 'proposal' to project.status lifecycle. Project lifecycle is now: proposal → active → complete → archived.
Far-future: contracts and e-signature as a document type linked to the proposal → active transition.
Contractors and PMs. Homeowners are a real but secondary use case (DIY renos, or projects with low-tech GCs where the homeowner enters all data). Pricing model: not yet decided, deliberately. Once Chad is actively using it and we have real feedback, we'll have much better signal on pricing and value.
Schema change: Added a "Product scope" section to the doc making this explicit.
No. CPA-legible, not CPA-grade. It's not an accounting tool. Eventually we'd want integration with or export to QuickBooks (or whatever's standard in this industry), so the data structures should be shaped with that in mind. But the bar is "your CPA can follow what happened," not "your CPA uses this as their system of record."
That said, the structures that look "accountant-grade" (payment_applications, credit_applications, margin lines) aren't really about accounting sophistication. They're about not losing track of money. "Where did that $500 go?" is a question homeowners and contractors ask constantly. We're keeping all of them.
Schema change: Noted in the Product scope section. Added soft reconciliation warning on invoice.invoice_total vs transaction sums (warns but doesn't block).
Day zero. Chad needs access yesterday. The grant/snapshot/projection layer stays as designed.
Schema change: None needed, already in the schema.
Photos are a real future need, though so far the focus has been more on sharing PDFs for plans and bids. Forget-me-nots are the most photo-heavy entity currently. A more photo-forward gallery structure makes sense if it can work seamlessly with the document-attachment patterns we already have.
Schema change: Added kind column to attachment (nullable text: 'document', 'photo', 'plan'). Cheap future-proofing for gallery filtering without MIME parsing. Full gallery model (EXIF, chronological views, etc.) added to "Things deferred."
Long-tail read is a real use case (warranty lookups, "what paint did we use," resale disclosures, tax records, insurance claims). Projects can be archived (hidden from active dashboards) and unarchived (toggle back to add a new phase years later without starting fresh).
For the "who keeps what after the working relationship ends" question: rather than forking the project data (expensive, complex), we're considering a self-contained export (JSON + attachment ZIP). Since every record has jsonb snapshots baked in, the export is self-describing and can be reimported into a new project later using the same schema. This avoids keeping stale completed-project data live in the DB forever while preserving full portability.
Schema change: Documented archive semantics on the project table. Export/import added to "Things deferred."
Your "not" list was close but needed some corrections based on where the product is heading:
Confirmed NOT:
- Not a timeclock (hourly workers don't log in/out here)
- Not a PO system for internal supplies (no hammers-and-printer-paper tracking)
- Not a vendor marketplace or directory with ratings
- Not a full document editor (PDF annotation eventually, but not editing)
- Not an accounting tool (CPA-legible, not CPA-grade)
Actually IS (future, not v1):
- A collaboration surface: in-context discussion on budgets, quotes, plans, tasks. Task-based back-and-forth between contractor and client. "Create a task from this screenshot/URL." Related: GitHub Discussion #9.
- A schedule communicator: "plumbing rough-in happens Tuesday." Milestone-level, not timeclock-level.
- A PDF annotation tool: clients marking up quote PDFs with questions anchored to specific spots.
Schema change: Added "Product scope" section with the corrected IS/IS NOT lists. Added comments/discussion, project scheduling, task dependencies, and PDF annotation to "Things deferred."
Keeping polymorphic with app-enforced integrity for v1. Noted the 2-junction alternative (invoice_attachment + transaction_attachment with real FKs) as a pragmatic middle ground to revisit if orphaned attachments surface in practice.
Accepted. Replaced margin_for_id + margin_line_id on transaction with a transaction_margin junction table. Unique constraint on base_transaction_id enforces one margin per base at the DB level.
Accepted. All snapshot columns are now jsonb (e.g., vendor_snapshot jsonb instead of vendor_name_snapshot text, vendor_url_snapshot text, ...). Matches the "frozen historical blob" semantic. Public projection changes don't require migrations. Live FK handles current-state reads; jsonb snapshot is only for historical fallback.
Accepted. Added an event table: append-only, project-scoped or org-scoped, with event_type and payload jsonb. Captures transfers, grant changes, quote selections, archival.
Confirmed. role is the source of truth for capabilities, computed in app code (canEditBudget(role), etc.). scope is a coarse read/read-write gate. Permission checks go through role-based helpers, never hardcoded against scope. Documented on the grant table.
Not preempting. Adding a nullable column and backfilling later is a trivial migration even on a large table. Shipping the column now risks misleading developers into thinking it's wired up.
Credits are project-scoped. A goodwill credit across projects would be modeled as a new credit on the affected project or an out-of-band payment, not as a cross-project credit_application. Documented as an explicit constraint.
Stored is correct (matches the physical document). Added a soft reconciliation warning at write time: flags when stored total doesn't match attached transaction sum, but doesn't block saves (real invoices legitimately don't match due to rounding, tax adjustments, partial billing).
Can you write a one-pager of the product vision, and does it match what the schema is quietly asserting?
The schema doc now has a "Product scope" section that serves as this one-pager. The schema was slightly ahead of the vision in two areas (the vision has caught up):
- Proposal-as-first-class-concept was implied by the mutable
owner_org_idbut not named. Now named and backed by thequotetable. - Collaboration features (comments, scheduling, annotations) were absent from the schema because the vision hadn't been articulated yet. Now explicitly deferred with enough context to inform future design.
The schema is not ahead of the vision in any area that would warrant simplification. Everything in the schema has a user story behind it.