Reno Postgres schema sketch (updated after review)

reno-schema.md

Reno Postgres schema sketch

First-pass schema design for the Reno migration. Derived from the existing TypeScript models in src/lib/models/ (already decoupled from Airtable's shape) and the org/project/grant model we worked through in #57.

Key concepts, decided

• Org is the identity/billing/ownership layer. Every user belongs to one home Org. A household is an Org. A contractor's firm is an Org. A solo DIY-er is a one-person Org.
• Project is the Reno-specific ownership root. A project is owned by exactly one Org at a time (mutable, to support placeholder-and-transfer). All reno domain data (phases, transactions, invoices, ...) is scoped to a project.
• Grant gives one Org access to another Org's project, with a role (homeowner, contractor, accountant, architect, collaborator, ...). Cross-Org data visibility flows through grants, not through users belonging to multiple Orgs.
• Org-scoped reference data (vendors, contacts, payment methods, payments) lives at the Org level because it spans projects. A contractor's vendor list is one list, reused across 20 client projects.
• Public projection of an Org-scoped entity is the column subset visible to grantees when a reference crosses the Org boundary. Defined in code per entity type; Org-configurable is a future option (forward-compat wiring described below).
• Snapshot-on-write: project-scoped records that reference Org-scoped records also store a jsonb snapshot of the public projection at write time. Live FK stays for current-state reads; the jsonb snapshot guarantees the historical record is complete even if the grant is later revoked, the reference is deleted, or the owning Org renames the entity. Using jsonb (not flat columns) means projection changes don't require migrations.

Product scope

Reno is a contractor's project management and client-collaboration tool, with a secondary homeowner-solo mode. The schema encodes these commitments:

What Reno IS:

• A system of record for budgets, invoices, payments, and vendor relationships
• A client-acquisition surface: contractors build proposals with real quotes, present to clients, promote to working budgets on acceptance
• A collaboration workspace between contractor and client (and their accountants, architects, etc.)
• A project scheduler: communicating what work happens when (milestones, phase timelines)
• A discussion venue: in-context conversation about budgets, quotes, plans, decisions
• A long-tail archive: completed projects stay accessible for warranty lookups, tax records, resale disclosures

What Reno is NOT:

• A timeclock (hourly workers don't log in/out here)
• A PO system for internal supplies (no hammers-and-printer-paper tracking)
• A vendor marketplace or directory with ratings
• A full document editor (PDF annotation yes, editing no)
• An accounting tool (CPA-legible, not CPA-grade; future QB/accounting export, not replacement)

Primary buyer: Contractors and project managers. Homeowners are a real but secondary use case (DIY renos, or projects with low-tech GCs where the homeowner enters data).

Pricing model: Not yet decided. Will be informed by real usage data once Chad is actively using the product. Schema doesn't encode pricing assumptions.

Decisions baked in

• Single Postgres, single schema, row-level scoping via org_id and/or project_id on owned tables.
• org_id and/or project_id are NOT NULL on every owned table. Enforced at the application layer (no RLS, at least initially).
• UUIDs for primary keys (let Postgres generate).
• Timestamps as timestamptz, dates as date.
• Monetary amounts as numeric(14,2), not float, not cents-as-bigint. Matches human-readable accounting.
• Drizzle schemas eventually, but this doc stays platform-agnostic.

Identity and access

org

Top-level account.

column	type	notes
id	uuid pk
name	text	"Silber-Katz household," "Lavoie Architecture"
created_at	timestamptz

user

Login identity. Belongs to one home Org.

column	type	notes
id	uuid pk
org_id	uuid fk → org	home org
email	text unique
google_sub	text unique, nullable	from Better Auth / Google SSO
name	text
created_at	timestamptz

Multiple users can share an org_id (multi-user Orgs work immediately). One user belonging to multiple Orgs is deferred: for now, cross-Org work flows through grants between Orgs rather than through users belonging to several Orgs.

Auth enforcement: Better Auth gives locals.user. Every query resolves the user's home org_id plus any Orgs they reach via active grants, and scopes reads/writes accordingly.

project

The ownership root for all Reno domain data.

column	type	notes
id	uuid pk
owner_org_id	uuid fk → org	mutable; supports placeholder-and-transfer
name	text	"Silber Kitchen Reno"
address	text nullable
status	text nullable	lifecycle: 'proposal', 'active', 'complete', 'archived'
created_at	timestamptz

Project ownership can transfer. Contractor creates a project under their firm-Org with a placeholder client. Client later signs up, project transfers to client-Org (UPDATE project SET owner_org_id = ...). Grants for everyone involved are preserved; the contractor issues themselves a grant at transfer time so they don't lose access.

Project lifecycle: A project starts in 'proposal' status while the contractor collects sub quotes, assembles a bid, and presents it to the client. Upon client acceptance (signing a contract, verbal agreement, or whatever trigger fits), the project moves to 'active' and quoted amounts promote into the working budget. 'complete' marks the reno as finished; 'archived' hides it from active dashboards while preserving the full record for long-tail reference (warranty lookups, "what paint did we use," resale disclosures, tax records, insurance claims). Archival is toggleable: a project can be unarchived to add a new phase years later without starting fresh.

grant

Cross-Org access into a project. Issued by the project's owning Org to some grantee Org.

column	type	notes
id	uuid pk
project_id	uuid fk
grantee_org_id	uuid fk → org	the Org receiving access
role	text	'homeowner', 'contractor', 'accountant', 'architect', 'collaborator'
scope	text	'read', 'read-write'; default 'read-write'
expires_at	timestamptz nullable	null = indefinite
created_at	timestamptz
created_by_user_id	uuid fk → user	who issued the grant

Unique constraint on (project_id, grantee_org_id): one active grant per project+org pair.

Access rule (enforced at app layer): a user can see a project if they belong to its owner_org_id, or if they belong to an Org that has an active (non-expired) grant for that project.

Scope vs role: scope is a coarse gate ('read' or 'read-write'). role is the source of truth for fine-grained capabilities, computed in app code (e.g. canEditBudget(role), canRecordPayment(role), canSelectQuote(role)). Permission checks must always go through role-based helpers, never hardcode against scope === 'read-write' directly. This keeps scope expansion additive rather than a breaking change.

Ownership transfer

Project ownership can move between Orgs without migrating data, because ownership is a single mutable column (project.owner_org_id) and everything else is either scope-based (project_id) or reference-based (FK + snapshot). No cascade deletes, no repointing of child records.

Placeholder → homeowner (contractor created project before client signed up)

1. Create homeowner's Org and User.
2. UPDATE project SET owner_org_id = <new_org> where id = <project>.
3. Issue a grant back to contractor's firm-Org so they retain working access.
4. Existing grants (architect, accountant, ...) stay intact because they reference project_id, not owner_org_id.

Contractor → contractor (Chad hands project to John's firm)

Same shape. UPDATE owner_org_id. Chad's grant is replaced by John's (or Chad retains a read-only "previous GC" grant by arrangement). Data in Chad's Org (vendors, contacts, payments Chad issued) stays put; snapshots on transactions/invoices/tasks preserve the public projection for John going forward. If Chad's grant stays live, John reads current public projections; if Chad cuts ties, snapshots remain the readable record.

Contractor-led → homeowner-led (scope change mid-project)

Same mechanism. Homeowner takes owner_org_id, contractor keeps or loses a grant based on the arrangement.

User leaves firm (home-Org change)

UPDATE user SET org_id = <new_org>. User loses membership-based access to the old Org. Anything they created (vendors, payments, grants they issued) stays with old Org, because those rows reference org_id, not user_id.

Snapshot-alone vs copy-on-transfer (deferred)

v1 uses snapshot-alone: cross-Org references remain as FKs into the previous-owner Org, with snapshot columns preserving historical values. A later enhancement could copy-on-transfer: at the moment a project changes Orgs, any vendor/contact owned by the old Org that is actively referenced by project records gets duplicated into the new Org, giving the new owner editable copies. Adds editability at the cost of sync complexity. Defer until real usage demands it.

Project-scoped domain data

Every table below has project_id NOT NULL and inherits access from the project.

phase

Named timeline segments within a project. "Kitchen," "Primary Bath," "Exterior."

column	type	notes
id	uuid pk
project_id	uuid fk
name	text
status	text	"Planning," "In Progress," "Done." Text for flexibility; phase_status enum later if values stabilize
sort_order	integer
notes	text nullable
start_date	date nullable
end_date	date nullable

taskIds[], areaIds[], permitIds[] from the existing model become reverse lookups (child tables' phase_id column).

Phases are a single set per project in v1, matching the existing Airtable model. A future extension would let Orgs define phase "perspectives" (contractor-view: Quoting/Bidding/Active/Punchlist; homeowner-view: Kitchen/Bath/Exterior) over the same underlying project, with child records belonging to multiple phases via junction. See "Things deferred" for the sketch.

work_area

Room/zone within a phase. "Primary Bath," "Kitchen Pantry."

column	type	notes
id	uuid pk
project_id	uuid fk
phase_id	uuid fk nullable	area may not be phase-attached
name	text
zone	text nullable
plans_status	text nullable
notes	text nullable

Computed fields from the model (hasForgetMeNots, documentCount) become query-level aggregates.

forget_me_not

Mini-TODOs attached to work areas. "Remember to caulk the back edge."

column	type	notes
id	uuid pk
project_id	uuid fk
work_area_id	uuid fk
text	text
done	boolean default false
sort_order	integer

reno_task

Project to-dos (distinct from personal tasks on daily-dashboard).

column	type	notes
id	uuid pk
project_id	uuid fk
phase_id	uuid fk nullable
lead_contact_id	uuid fk nullable	who's responsible; references Org-scoped contact
contact_snapshot	jsonb nullable	public projection frozen at write-time
name	text
status	text nullable
priority	text nullable
start_date	date nullable
due_date	date nullable

quote

Vendor bids collected during the proposal phase. Each budget line item may have 1-4 competing quotes from different vendors; one gets selected and its amount promotes into the budget when the project goes active.

column	type	notes
id	uuid pk
project_id	uuid fk
budget_line_item_id	uuid fk nullable → budget_line_item	the budget category this quote is for
vendor_id	uuid fk nullable	who submitted the quote; references Org-scoped vendor
vendor_snapshot	jsonb nullable	public projection frozen at write-time
amount	numeric(14,2)
status	text	'received', 'selected', 'rejected', 'expired'
description	text nullable
received_date	date nullable
valid_until	date nullable	expiry of the quote
notes	text nullable

When a quote is marked 'selected', the associated budget_line_item.quoted_amount is set to the quote's amount and budget_line_item.status updates accordingly. Non-selected quotes stay as historical record ("we could have gone with X for $Y"). Attachments (the PDF quote document) link via the attachment table with owner_type = 'quote'.

budget_line_item

Hierarchical budget structure. Self-referencing parent.

column	type	notes
id	uuid pk
project_id	uuid fk
parent_id	uuid fk nullable → budget_line_item	self-ref for nesting
phase_id	uuid fk nullable
name	text
quoted_amount	numeric(14,2)
status	text nullable
allocate_to_sub_budgets	boolean default false
sort_order	integer nullable
notes	text nullable

Computed fields (spent, remaining, effectivePhaseId, transactionIds, childIds) become queries/aggregates.

Cycle prevention is app-layer: no parent can become a descendant of its own child. Getting it wrong doesn't break the schema, just the display logic.

transaction

Individual spending records. Can live under an invoice or be standalone (pending expense).

column	type	notes
id	uuid pk
project_id	uuid fk
description	text nullable
amount	numeric(14,2)
date	date nullable	standalone transactions use this; invoice-attached ones typically inherit the invoice date
vendor_id	uuid fk nullable	who did the work; references Org-scoped vendor
vendor_snapshot	jsonb nullable	public projection frozen at write-time (name, url, phone, address)
billed_through_id	uuid fk nullable → vendor	who billed us (pass-through)
billed_through_snapshot	jsonb nullable	public projection frozen at write-time
cost_type	text nullable
budget_line_item_id	uuid fk nullable
invoice_id	uuid fk nullable	null for pending/standalone
type	text nullable
offset	boolean default false	documented but excluded from budget math

Margin relationships (contractor markup on a sub's line item) are tracked in the transaction_margin junction table rather than as reciprocal columns on transaction. See below.

transaction_margin (junction)

Links a base transaction to its margin (markup) transaction. Enforces one margin line per base at the DB level, removing a consistency invariant the app would otherwise have to maintain forever.

column	type	notes
id	uuid pk
project_id	uuid fk
base_transaction_id	uuid fk → transaction	the sub's line item
margin_transaction_id	uuid fk → transaction	the contractor's markup

Unique constraint on (base_transaction_id): one margin per base.

invoice

Groupings of transactions that share an invoice document.

column	type	notes
id	uuid pk
project_id	uuid fk
invoice_number	text nullable
invoice_date	date nullable
invoice_total	numeric(14,2)
vendor_id	uuid fk nullable	references Org-scoped vendor
vendor_snapshot	jsonb nullable	public projection frozen at write-time

Computed fields (amountPaid, creditApplied, offsetTotal, outstanding, status) become queries/aggregates against transaction, payment_application, and credit_application.

invoice_total is stored (matches the physical document), not derived from transaction sums. A soft reconciliation warning at write time flags mismatches between the stored total and the sum of attached transactions, but doesn't block the save (real invoices sometimes don't match their line items due to rounding, tax adjustments, or partial billing).

payment_application (junction)

A single payment can apply to multiple invoices; an invoice can be covered by multiple payments. Payments themselves are Org-scoped (the Org that issued the check); this junction is project-scoped (it pins a specific invoice within a project).

column	type	notes
id	uuid pk
project_id	uuid fk
payment_id	uuid fk → payment
invoice_id	uuid fk → invoice
amount_applied	numeric(14,2)	split-payment support

Unique constraint on (payment_id, invoice_id) to prevent dupes.

credit_application

Credits applied between invoices. Credits are scoped to a single project: a goodwill credit for a screwup on Project A would be modeled as a new credit on Project A (or an out-of-band payment on Project B), not as a cross-project credit_application.

column	type	notes
id	uuid pk
project_id	uuid fk
credit_source_id	uuid fk nullable → invoice
applied_to_id	uuid fk nullable → invoice
amount	numeric(14,2)
applied_date	date nullable
notes	text nullable

Org-scoped reference data

Tables at the Org level because they span projects. A contractor's vendor list is one list; a household's saved contacts outlive a single reno.

public marks columns included in the public projection (returned when a grantee reads the entity via a cross-Org FK reference from within a project they have access to).

vendor

Company or person being paid.

column	type	notes
id	uuid pk
org_id	uuid fk
name	text	public
url	text nullable	public
address	text nullable	public
phone	text nullable	public
internal_notes	text nullable	private
standard_rate	numeric(14,2) nullable	private
created_at	timestamptz

contact

Person at a vendor. "Who do I email at Lavoie Architecture."

column	type	notes
id	uuid pk
org_id	uuid fk
vendor_id	uuid fk nullable
first_name	text nullable	public
last_name	text nullable	public
full_name	text	public; computed at query time
title	text nullable	public
email	text nullable	public
phone	text nullable	public
sms_ok	boolean default false	private
internal_notes	text nullable	private

payment

Money going out, issued by some Org. A single payment can apply to multiple invoices across multiple projects.

column	type	notes
id	uuid pk
org_id	uuid fk	the Org that issued the payment
date	date nullable	public
amount	numeric(14,2)	public
method_id	uuid fk nullable → payment_method	public (resolved through payment_method public projection)
check_number	text nullable	public
type	text nullable	private
vendor_id	uuid fk nullable	private (internal bookkeeping)
direct_vendor_id	uuid fk nullable	private
earmarked_for	text nullable	private

payment_method

Bank account or card.

column	type	notes
id	uuid pk
org_id	uuid fk
name	text	public
last4	text nullable	public
type	text nullable	private
active	boolean default true	private
internal_notes	text nullable	private

Attachments

attachment

Generic attachment table, linked by (owner_type, owner_id) to avoid per-parent columns. Scope follows the owner: project-scoped if owner is a project-scoped entity, Org-scoped if owner is an Org-scoped entity.

column	type	notes
id	uuid pk
project_id	uuid fk nullable	null if owner is Org-scoped
org_id	uuid fk nullable	null if owner is project-scoped
owner_type	text	'invoice', 'transaction', 'work_area', 'forget_me_not', 'vendor', 'contact', 'quote'
owner_id	uuid	no FK constraint due to polymorphic shape; enforce in app
filename	text
url	text	Vercel Blob URL
size	integer nullable
mime_type	text nullable
kind	text nullable	'document', 'photo', 'plan'; future gallery views filter on this without parsing MIME
created_at	timestamptz

CHECK constraint: exactly one of (project_id, org_id) is NOT NULL.

Blob storage: Vercel Blob for stack consistency. File goes to Blob, URL stays in Postgres.

The polymorphic owner_type + owner_id pattern avoids N columns of FK but loses DB-level referential integrity. Alternative: one junction per owner (invoice_attachment, transaction_attachment, ...) with real FKs. Current attachment volume is overwhelmingly invoices and transactions; if orphan risk becomes a real concern, a 2-junction version (invoice + transaction) with a polymorphic fallback for the rest is a pragmatic middle ground. For v1, polymorphic with app-enforced integrity is acceptable; revisit if orphaned attachments surface in practice.

Audit log

event

Append-only timeline of significant actions. Cheap to add now, expensive to backfill later. Provides: project-transfer auditing, grant issuance/revocation history, quote selection records, and a foundation for a future activity feed.

column	type	notes
id	uuid pk
project_id	uuid fk nullable	null for Org-level events
org_id	uuid fk nullable	null for project-level events; CHECK: at least one is NOT NULL
actor_user_id	uuid fk → user	who did this
event_type	text	'project.transfer', 'grant.create', 'grant.revoke', 'quote.select', 'project.archive', ...
payload	jsonb	event-specific details (old/new values, affected entity IDs, etc.)
occurred_at	timestamptz

Append-only by convention (never UPDATE or DELETE event rows). No indexes on payload in v1; query by project_id + event_type + occurred_at.

Cross-Org reference handling

Because vendors, contacts, and payment methods live at the Org level but are referenced by project-scoped records, cross-Org FKs are possible whenever a project has grants to non-owning Orgs. Two rules govern this:

1. Public projection on read. When a grantee reads a cross-Org reference (e.g. a client reads a transaction that references their GC's firm's vendor), the query layer returns only the public columns of the referenced entity. Caller's Org is compared against the entity's org_id; same-Org returns the full row, cross-Org returns the public projection.
2. Snapshot on write. Any project-scoped record that references an Org-scoped entity also stores a jsonb snapshot column containing the public projection at write time. If the grant is later revoked, the reference is deleted, or the owning Org renames the entity, the historical record still contains the public projection as it was at write time. This is audit-immutable, like a paper invoice that literally contains the vendor name rather than a pointer to it.

Why jsonb for snapshots: Snapshots are frozen historical blobs by definition. Storing them as jsonb (e.g. vendor_snapshot jsonb rather than vendor_name_snapshot text, vendor_url_snapshot text, ...) means public projection changes don't require migrations. Tradeoff: filtering on snapshot fields requires jsonb operators (vendor_snapshot->>'name'), but those queries should be rare since the live FK handles current-state reads and the snapshot is only for historical fallback.

The public projection is defined in code per entity type (a central constant map). Org-configurable projections (Chad picks what his firm exposes per-client) are deferred.

Forward-compat wiring done now to preserve the option:

• One central function publicProjection(entityType, row) handles field filtering.
• One snapshot helper per entity type (vendorSnapshotFor(vendor), contactSnapshotFor(contact), ...) returns a jsonb object ready to store.
• Read paths go through one query helper that detects cross-Org callers and applies the projection.

When/if Org-configurable becomes a real need, we add an org_visibility_config table, swap the constant lookup for a config read, and touch a small number of files.

Templates and tags (deferred)

Two related but distinct mechanisms for reducing data re-entry across projects. Named separately because they have different data-model commitments.

Tags (v1.5: live-reference collections)

Org-scoped labels applied to reference data (vendors, contacts, payment methods). Live-reference semantics: one vendor row, accessible through many tag groupings; edits to the vendor propagate everywhere. A project references tags for access grouping ("this project sees all vendors tagged 'residential'").

tag              { id, org_id, name, description, color }
tag_assignment   { id, tag_id, entity_type, entity_id }     -- polymorphic
project_tag_access { id, project_id, tag_id }                -- "project sees what's tagged X"

Templates (v2: seed-on-apply blueprints)

Org-scoped blueprints instantiated into real project records. Copy-on-apply semantics: instantiate once, project diverges after. No live sync by default.

Applies to: phase, work_area, reno_task, budget_line_item.

template              { id, org_id, kind, name, description }
template_phase_item   { id, template_id, name, sort_order, default_status, default_notes }
template_work_area_item { id, template_id, name, sort_order, default_zone }
template_task_item    { id, template_id, name, sort_order, default_status, default_priority }
template_budget_item  { id, template_id, parent_id (self-ref), name, sort_order, default_quoted_amount }

On instantiation: template items get copied into project-scoped rows (new UUIDs, new project_id). Optional source_template_item_id column on each instantiated row enables later drift detection ("your template changed since you used it — apply updates to this project?") without forcing sync.

Why they're distinct

Tag = one row, many views (cardinality stays 1). Template = one blueprint, many copies (cardinality multiplies on use). Converting between them later is expensive: tags-to-templates means backfilling duplicate rows; templates-to-tags means deduplicating divergent per-project customizations. Picking the right pattern per entity at design time matters.

Indexes (first-pass)

• Every table: index on scoping column(s). org_id for Org-scoped, project_id for project-scoped.
• grant: (project_id), (grantee_org_id).
• user: (org_id).
• project: (owner_org_id).
• quote: (budget_line_item_id), (vendor_id).
• budget_line_item: (parent_id), (phase_id).
• transaction: (invoice_id), (budget_line_item_id), (vendor_id).
• transaction_margin: (base_transaction_id) unique, (margin_transaction_id).
• payment_application: (payment_id, invoice_id) unique.
• credit_application: (credit_source_id), (applied_to_id).
• attachment: (owner_id, owner_type).
• reno_task: (phase_id), (lead_contact_id).
• work_area: (phase_id).
• forget_me_not: (work_area_id).
• event: (project_id, event_type), (org_id, event_type).

Things deferred (not yet in scope)

• permit. Phase model mentions permitIds[] but there's no Permit model yet. Future entity.
• Multi-user Orgs with internal roles. Schema supports multi-user immediately (several user rows with the same org_id), but within-Org role/permission tables are deferred.
• One user belonging to multiple Orgs simultaneously. Cross-Org work flows through Org-to-Org grants for now; per-user multi-Org membership is a future addition if needed.
• Phases as Org-scoped perspectives. v1 uses a single phase set per project. Later, phases could become perspective-scoped: a phase_group (owned by an Org within a project) contains its own phase rows, and child records (reno_tasks, budget_line_items) belong to multiple phases via junction — one per perspective. Migration: add phase_group table, add phase_group_id to phase (nullable → backfill default group per project → NOT NULL). The Templates mechanism could deliver this: a phase-group template, instantiated per perspective per project.
• Budget perspectives. Same concept applied to the budget tree: contractor-view rollup vs homeowner-view rollup of the same project's spending. v1 has one budget tree per project. Extension would parallel phase perspectives.
• Org-configurable public projections. Forward-compat wiring (above) keeps the path open.
• Comments and discussion. In-context conversation attached to any entity (budgets, invoices, quotes, tasks, work areas). Threaded or flat TBD. Includes cross-party back-and-forth (contractor and client discussing a specific quote or budget line). Related: GitHub Discussion #9.
• PDF annotation. Marking up quote PDFs, plans, spec sheets with comments anchored to page regions. Likely its own annotation entity linked to an attachment + a discussion thread.
• Project scheduling. Milestone-level timeline ("plumbing rough-in: May 12-14") visible to client. May be as simple as enriching existing start_date/end_date on phases and start_date/due_date on reno_tasks, or may need a dedicated schedule_event table. Distinct from personal calendars.
• Task dependencies and cross-party assignment. Dependent tasks, tasks or questions assignable between contractor and client, questionnaire flows. Extension of reno_task.
• Contracts and signing. Far-future: contracts as a document type with e-signature workflow, linked to project acceptance (proposal → active transition).
• Project export/import for archival. Self-contained JSON + attachment ZIP export of a completed project. Can be reimported into a new project since it uses our own schema. Alternative to keeping completed-project data live in the DB forever. Particularly useful if either party wants a portable copy after the working relationship ends.
• Photo gallery. First-class photo management (before/progress/after shots, EXIF, chronological gallery view) beyond the current generic attachment table. The attachment.kind column provides a minimal foundation. FMNs (forget-me-nots) are the most photo-heavy entity currently.
• SaaS-specific tables (subscriptions, charges). Separate base, not part of Reno.
• HSA / Meds / Meals / Carpool etc. Stay on Airtable (daily-dashboard), not included here.

Open questions

1. Generated columns. Postgres supports GENERATED ALWAYS AS (...) STORED for computed columns like contact.full_name. Worth using, or just compute in SELECT? Slight preference for SELECT-time: keeps schema declarative, avoids a future "the formula changed" migration.
2. Enums vs text. phase.status, reno_task.status, grant.role, etc. are free-text-ish. Real enums (cleaner, require migrations to change) or text with CHECK constraints? Lean text for flexibility during early usage.
3. Soft delete. None of the current models has a deleted_at concept. Lean hard-delete for simplicity; add soft-delete if we hit a real "oops" event.

Resolved questions

• Project transfer auditing. Resolved: yes. The event audit log table captures transfers, grant changes, and other significant actions. Append-only, cheap, already in schema.
• Grant revocation vs expiry. Resolved: delete the row. The event log captures the revocation history.
• Payment_applications across a project transfer. Resolved: they stay (historical integrity). Snapshot/public-projection handles visibility for the new owner. Worth writing a test for.
• Margin line reciprocal columns. Resolved: replaced with transaction_margin junction table. Unique constraint on base_transaction_id enforces "one margin per base" at the DB level.
• Snapshot column proliferation. Resolved: use jsonb snapshot columns instead of N flat columns per entity. Projection changes don't require migrations.
• Grant scope vs role. Resolved: role is the source of truth for fine-grained capabilities. scope is a coarse read/read-write gate. Permission checks go through role-based helpers.
• Cross-project credits. Resolved: credits are project-scoped. Cross-project goodwill credits are modeled as separate entries, not cross-project credit_applications.
• Invoice total stored vs derived. Resolved: stored (matches physical document). Soft reconciliation warning at write time when stored total doesn't match transaction sum.
• Phase phase_group_id preemption. Resolved: don't preempt. Adding a nullable column and backfilling later is trivial. Shipping it now risks misleading developers into thinking it's wired up.
• Polymorphic attachments. Resolved for v1: keep polymorphic with app-enforced integrity. Revisit with 2-junction approach (invoice + transaction with real FKs) if orphaned attachments surface.