docs/data-model/dashboard-auth.dbml

// Lawn Estimator — admin dashboard: users, roles, audit, config draft/publish (P2a/2b)
//
// 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.
//
// SECURITY POSTURE (owner directive: "ironclad, don't expose ourselves"):
//   - IDENTITY is delegated to Clerk (managed auth). Clerk owns passwords, MFA, reset flows,
//     and sessions. We NEVER store a password or password hash. Our DB stores only the mapping
//     Clerk-user -> tenant + role.
//   - AUTHORIZATION is ours: every dashboard request verifies the Clerk session, resolves the
//     `users` row, and scopes EVERY query to that user's tenant_id. A tenant_id is never taken
//     from the client.
//   - AUDIT: every mutating action appends to `audit_log` (append-only) — the record we defend
//     ourselves with.
//   - Roadmap: ROADMAP_APP.md P2a/2b. Login = password + MFA for MVP; magic-link / passkeys are
//     a documented later option (Clerk supports both — a config change, no schema change).

Project lawn_estimator_dashboard {
  database_type: 'PostgreSQL'
  Note: 'Admin dashboard identity (via Clerk), tenant-scoped authorization, and audit.'
}

// `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 users {
  id integer [pk, increment]
  tenant_id varchar [not null, ref: > tenants.id, note: 'The one company this user administers. All their dashboard queries are scoped to this.']
  clerk_user_id varchar [not null, unique, note: 'The Clerk user id. Clerk holds the credential (password/MFA/session); we hold NO password. This is the whole identity link.']
  email varchar [not null, note: 'Mirror of the Clerk email for display/audit; Clerk is the source of truth.']
  role varchar [not null, note: 'owner | staff. owner-only: config publish (2b), billing, user/role management. staff: view + batch quoting + batch-measurement edits.']
  status varchar [not null, default: 'invited', note: 'invited (Clerk invite sent, not yet accepted) | active | disabled']
  invited_by integer [null, ref: > users.id, note: 'Who sent the invite (invite-only onboarding for MVP — no open signup).']
  created_at timestamp [not null, default: `now()`]

  indexes {
    tenant_id
    clerk_user_id [unique]
  }
  Note: 'A dashboard operator. Onboarding is INVITE-ONLY for MVP: we (or a tenant owner) create the row + Clerk invite; open self-serve signup is v2 (Coverage-gated).'
}

Table audit_log {
  id integer [pk, increment]
  tenant_id varchar [null, ref: > tenants.id, note: 'null for platform/system actions']
  user_id integer [null, ref: > users.id, note: 'null for automated/system actions']
  action varchar [not null, note: 'dotted verb: config.publish | config.draft.save | lead.view | batch.measurement.edit | user.invite | user.role.change | login | logout | ...']
  target_type varchar [null, note: 'config | lead | measurement | user | ...']
  target_id varchar [null]
  before json [null, note: 'prior state (edits) — the defend-ourselves history']
  after json [null, note: 'new state (edits)']
  ip varchar [null, note: 'request IP (behind the proxy, best-effort)']
  created_at timestamp [not null, default: `now()`]

  indexes {
    (tenant_id, created_at)
    (user_id, created_at)
  }
  Note: 'EVERY mutating dashboard action appends one row (owner directive: log everything). APPEND-ONLY — never updated or deleted. Retention/policy TBD.'
}

// Config draft/publish (2b). The LIVE config stays in tenants.tenant_configs (the widget reads
// it). Editing happens on a DRAFT; PUBLISH validates the draft, promotes it to live, and appends
// a version + an audit_log row. Validation is a SECURITY boundary (tenant config renders in the
// public widget): reject javascript: in logo/cta_url, hex-only accent, non-negative + capped
// prices, 5-digit ZIPs (warn if outside Coverage), well-formed origins. See ROADMAP_APP.md 2b.
Table tenant_config_drafts {
  tenant_id varchar [pk, ref: - tenants.id]
  draft json [not null, note: 'Working copy (may be incomplete). Not served to the widget until published.']
  updated_by integer [null, ref: > users.id]
  updated_at timestamp [not null, default: `now()`]
  Note: 'One in-progress draft per tenant. Publish -> validate -> write tenant_configs (live) + append tenant_config_versions + audit_log; then clear/keep the draft.'
}

Table tenant_config_versions {
  id integer [pk, increment]
  tenant_id varchar [not null, ref: > tenants.id]
  config json [not null, note: 'The full config as published (snapshot).']
  published_by integer [null, ref: > users.id]
  published_at timestamp [not null, default: `now()`]

  indexes {
    (tenant_id, published_at)
  }
  Note: 'Append-only publish history → change record + rollback. The live config = newest version (mirrored into tenants.tenant_configs for the widget fast-read path).'
}