docs/data-model/tenants.dbml

// Lawn Estimator — tenant / pricing / service-area data model
//
// Render at https://dbdiagram.io (paste this file) or with the dbml CLI.
//
// NOTE ON STORAGE: the pricing_configs/<tenant>.json files remain the SEED. As of P1c the
// config is loaded from the DB (db.TenantStore) — currently the `tenant_configs` table
// below: one row per tenant holding that SAME nested JSON (start simple), seeded from the
// files on first boot, then the read/write path the P2 self-serve editor uses. The
// normalized tables below (tenants / services / service_areas / …) are the *logical* model
// the JSON maps to and the eventual target once the config schema stabilizes (plan §3.2
// "start simple, scale later"). Keep in sync with tenants.py / pricing.py.
// Refs: docs/product-design-plan.md §2/§3.2, ROADMAP_APP.md.

Project lawn_estimator_tenants {
  database_type: 'PostgreSQL'
  Note: 'Multi-tenant pricing + ZIP service areas for the lawn-quoting SaaS.'
}

Table tenant_configs {
  tenant_id varchar [pk, ref: - tenants.id, note: 'API-key label = tenant id']
  config json [not null, note: 'The full tenant config as the nested JSON the pricing_configs/<id>.json file holds (company/currency/presentation/services/areas/service_area_zips/allowed_origins/email/branding). INTERIM storage (P1c) — normalizing into the tables below is the eventual target.']
  updated_at timestamp [not null, default: `now()`, note: 'Last edit (P2 self-serve editor writes here).']
  Note: 'Durable tenant config, db.TenantStore. Seeded from pricing_configs/<id>.json on first boot; a stored edit then wins over the file. Becomes durable in prod with P1d (Neon).'
}

Table tenants {
  id varchar [pk, note: 'Tenant id = the ALLOWED_API_KEYS label (the company). JSON file: pricing_configs/<id>.json']
  company varchar [not null]
  currency varchar [not null, default: 'USD']
  presentation varchar [not null, default: 'estimate', note: 'firm | estimate (labeled "starting at")']
  email_from varchar [null, note: 'Per-tenant sender, e.g. "The Lawn Standard <quotes@thelawnstandard.net>". Emails send FROM the tenant-owned (ESP-authenticated) domain; ESP provider+key stay global. NULL → global EMAIL_FROM env.']
  email_reply_to varchar [null]
  email_lead_notify varchar [null, note: 'Where new-lead notifications for this tenant go (their inbox).']
  brand_name varchar [null, note: 'Widget branding (config "branding" block, W6) — applied client-side so the embed looks like the business.']
  brand_logo varchar [null, note: 'Logo URL/data-URI shown in the widget header.']
  brand_accent varchar [null, note: 'Accent color (hex) — overrides the widget theme.']
  cta_label varchar [null, note: 'Call-to-action button label after the price.']
  cta_url varchar [null, note: 'Where the CTA links (the business booking page, until W5 checkout).']
  Note: 'One lawn-care business. Maps to tenants.Tenant; base package = its services rows with service_area_id NULL.'
}

Table api_keys {
  key varchar [pk, note: 'X-API-Key (token_urlsafe). kind=secret → full access; kind=publishable → widget, quote-only + domain-locked.']
  tenant_id varchar [not null, ref: > tenants.id]
  kind varchar [not null, default: 'publishable', note: 'publishable | secret']
  Note: 'IMPLEMENTED as the `api_keys` table (db.ApiKeyStore). /admin provisioning mints keys here at runtime (no restart); merged into the in-memory maps at startup ALONGSIDE the ALLOWED_API_KEYS / WIDGET_API_KEYS env entries (which still work). Was env-only before.'
}

Table tenant_origins {
  tenant_id varchar [not null, ref: > tenants.id]
  origin varchar [not null, note: 'Full origin, e.g. https://acme.com — where the tenant widget may call from.']
  indexes {
    (tenant_id, origin) [pk]
  }
  Note: 'Allowed browser origins for the tenant widget. A publishable-key /quote is rejected (403) unless the request Origin/Referer host matches a row here. Empty ⇒ no widget origin authorized. Today: the config JSON "allowed_origins" list.'
}

Table service_areas {
  id integer [pk, increment]
  tenant_id varchar [not null, ref: > tenants.id]
  name varchar [not null]
  travel_time_factor decimal [not null, default: 1.0, note: 'Scales every line item for jobs in this area (1.0 = no change).']
  Note: 'A named zone of the tenant service area (tenants.ServiceArea). NO rows for a tenant → it serves all of Coverage at base pricing. The union of the zips in this table forms the tenant overall service area (plan §2); a lead outside it is still captured but gets no bookable price. WS4: a tenant may ALSO have a map-drawn `service_area_polygon` (GeoJSON in the config JSON) that decides serve/don''t-serve by point-in-polygon on the geocoded address; pricing still resolves by ZIP as below.'
}

Table service_area_zips {
  service_area_id integer [not null, ref: > service_areas.id]
  zip varchar [not null, note: '5-digit ZIP (ZIP+4 normalized to the first 5).']

  indexes {
    (service_area_id, zip) [pk]
  }
}

Table services {
  id integer [pk, increment]
  tenant_id varchar [not null, ref: > tenants.id]
  service_area_id integer [null, ref: > service_areas.id, note: 'NULL = tenant base package; set = per-area override used for that area only.']
  service_id varchar [not null, note: 'Auto-generated slug of the label (WS2); stable once created so `requires` refs hold.']
  label varchar [not null]
  rate decimal [not null, note: 'The rate NUMBER; meaning depends on `unit`. Stored as rate_per_1000_sqft for back-compat.']
  unit varchar [not null, default: 'per_1000_sqft', note: 'per_1000_sqft | per_sqft | flat (WS2).']
  min_charge decimal [not null, default: 0]
  measure varchar [not null, default: 'lawn_sqft', note: 'Which pipeline measurement drives this service.']
  requires varchar [null, note: 'Another service_id this is always paired with (e.g. overseeding requires aeration).']
  Note: 'A priced service (pricing.Service). Line price per `unit`: per_1000_sqft = rate/1000×sqft; per_sqft = rate×sqft; flat = rate — then ×area.travel_time_factor, floored at min_charge.'
}

Table bundles {
  id varchar [pk, note: 'Auto-generated slug of the name (WS5).']
  tenant_id varchar [not null, ref: > tenants.id]
  name varchar [not null]
  mode varchar [not null, note: 'fixed (amount IS the bundle price) | percent (amount = % off the summed member services).']
  amount decimal [not null]
  cadence varchar [not null, default: 'one_time', note: 'one_time | weekly | biweekly | monthly | seasonal — recurring metadata until Stripe (D5).']
  Note: 'A named package of services bookable as one (pricing.Bundle). Member service_ids in bundle_services. Shown in the widget as a selectable package; a booked package is recorded on the lead (detail.bundle) → Overview "Top packages".'
}

Table bundle_services {
  bundle_id varchar [not null, ref: > bundles.id]
  service_id varchar [not null, note: 'Must reference a services.service_id for the same tenant.']
  indexes {
    (bundle_id, service_id) [pk]
  }
}

// Resolution (tenants.Tenant.pricing_for_zip): find the service_area whose zips contain
// the quote ZIP → use that area's services (or the tenant base if it has none) and its
// travel_time_factor. No matching area → tenant base package at factor 1.0.