docs/data-model/metering.dbml

// Lawn Estimator — quote metering / usage
//
// Render at https://dbdiagram.io — render this file on its own (a `tenants` stub below lets it
// stand alone). tenants.dbml holds the full `tenants` table; don't concatenate the two files.
//
// STORAGE: LIVE via db.RunLedger over db.Database, which picks the backend at startup by
// DATABASE_URL (P1d): Postgres (Neon) in prod — durable across redeploys — else a local
// SQLite file (:memory: in tests). The `runs` table below replaced the in-memory
// MeteringStore, so the billing rule + per-tenant usage persist. One row per completed
// quote, so billing AND ops (plan §6.7) share one source of truth. The SQL is written once;
// Database papers over the SQLite/Postgres dialect (placeholders, id/timestamp types).

Project lawn_estimator_metering {
  database_type: 'PostgreSQL'
  Note: 'Per-tenant quote metering + run ledger. Anti-arbitrage: every surface records here.'
}

// `tenants` is defined FULLY in tenants.dbml. dbdiagram.io loads one file at a time, so this
// minimal stub lets THIS file render standalone. Don't paste both files together (that would
// double-define `tenants`).
Table tenants {
  id varchar [pk, note: 'Stub for standalone rendering — full definition in tenants.dbml.']
}

Table runs {
  id integer [pk, increment]
  tenant_id varchar [not null, ref: > tenants.id]
  surface varchar [not null, note: 'single (widget / single /quote / API) | batch']
  address varchar [not null, note: 'geocoder formatted address']
  address_norm varchar [not null, note: 'case/space-normalized address — the dedup key for single quotes']
  zip varchar [null, note: 'resolved 5-digit ZIP']
  billable boolean [not null, note: 'batch → always true (duplicates included); single → true only the first time this tenant quotes this address (a customer re-submit is free; becomes "cache miss" once the measurement cache lands)']
  method varchar [null, note: 'lidar+rgb | rgb-only (observability)']
  confidence varchar [null, note: 'high | medium (observability)']
  lawn_sqft decimal [null, note: 'measured lawn area — the original for a 2e edit, and a billing/analytics input']
  duration_s decimal [null, note: 'end-to-end pipeline seconds (observability / latency)']
  result_id varchar [null, note: 'links to the viz / overlay result; the 2e edit finds a run by this']
  outcome varchar [not null, default: 'ok', note: 'ok | rejected (client bad/out-of-area address) | error (our pipeline) — P3 observability']
  error_class varchar [null, note: 'the exception type on a failed run']
  created_at timestamp [not null, default: `now()`]

  indexes {
    (tenant_id, address_norm) [note: 'single-quote dedup + per-tenant usage rollup']
    (tenant_id, created_at) [note: 'usage within a billing period']
    (tenant_id, result_id) [note: 'measurement-edit lookup (2e)']
  }

  Note: 'One completed quote (out-of-area / failed quotes are NOT recorded yet — recording failures with an outcome/error column is a P3 observability add). Billable usage for a tenant = COUNT(*) WHERE billable within the billing window. Doubles as the run ledger (plan §6.7); add outcome / cost columns as observability + billing land.'
}

Table measurement_edits {
  id integer [pk, increment]
  tenant_id varchar [not null, ref: > tenants.id]
  result_id varchar [not null, note: 'the runs row this corrects; must be a surface=batch run (enforced at the endpoint — customer/widget quotes are LOCKED)']
  address varchar [null]
  original_sqft decimal [null, note: 'the measured area before the override (from runs.lawn_sqft)']
  edited_sqft decimal [not null, note: 'the operator-corrected area; the quote is re-priced on this']
  user_id integer [null, note: 'the operator (users.id — see dashboard-auth.dbml)']
  created_at timestamp [not null, default: `now()`]

  indexes {
    (tenant_id, created_at)
  }
  Note: 'Operator overrides of BATCH-quote measured areas (2e). Append-only — original vs edited is the §3.2 accuracy record. Customer/widget quotes are never editable.'
}

Table leads {
  id integer [pk, increment]
  tenant_id varchar [not null, ref: > tenants.id]
  source varchar [not null, note: 'widget (lead popup) | quote (a /quote carrying an email) | batch']
  address varchar [not null]
  name varchar [null]
  email varchar [null]
  phone varchar [null]
  zip varchar [null, note: 'resolved 5-digit ZIP (only when captured after geocode)']
  lawn_sqft decimal [null, note: 'null when captured before measurement (widget popup fires in parallel)']
  price_total decimal [null, note: 'bookable total quoted, when pricing was computed']
  confidence varchar [null, note: 'high | medium — the measurement confidence']
  result_id varchar [null, note: 'links to the viz / overlay result']
  created_at timestamp [not null, default: `now()`]

  indexes {
    (tenant_id, created_at) [note: 'the dashboard leads list, newest first (P2c)']
  }

  Note: 'Captured leads (P1b, db.LeadStore). APPEND-ONLY: a row is written the moment the widget collects contact (before measurement → sqft/price/zip null) AND when a /quote carries an email (after measurement → filled). Dedup/merge is a dashboard concern (P2c), not enforced here. The partner notification inbox stays the redundant durable store; this table is the queryable one.'
}