`db/migrations/005_users_roles.sql`

145 lines
-- 005 · Users / Roles / Installer-Members split (Phase 1, additive only)
--
-- Architectural intent:
--   The `installers` table is currently doing four jobs at once — identity
--   (email + password_hash + last_login_at), directory entity (slug + bio +
--   service area), subscription holder (tier + Stripe IDs), and session actor
--   (req.session.installerId). RBAC for ops staff (verification queue, COI
--   review, license review) has nowhere to live: ops staff aren't installers
--   and shouldn't have a row in `installers`.
--
-- This migration introduces three tables:
--
--   users               — pure identity (email, password, login bookkeeping)
--   roles               — N-to-N: which roles a user holds globally
--                         (admin / ops / installer_owner / installer_member)
--   installer_members   — N-to-N: which installer rows a user can act on,
--                         and in what capacity ('owner' or 'member')
--
-- Phase 1 (this file) is ADDITIVE ONLY:
--   - Existing routes still read installers.email / installers.password_hash.
--   - Existing sessions still set req.session.installerId.
--   - New tables are populated via the backfill block at the bottom so the
--     two worlds stay in sync.
--
-- Phase 2 (later) will:
--   - Switch /login + /signup to write users/roles/installer_members.
--   - Switch req.session to set userId (not installerId).
--   - Drop installers.email + installers.password_hash + installers.last_login_at.
--
-- This file is idempotent — re-runnable. Apply manually:
--   psql -d national_paper_hangers -f db/migrations/005_users_roles.sql

BEGIN;

-- =======================================================================
-- USERS — pure identity
-- =======================================================================

CREATE TABLE IF NOT EXISTS users (
  id              SERIAL PRIMARY KEY,
  email           TEXT UNIQUE NOT NULL,
  password_hash   TEXT NOT NULL,
  name            TEXT,
  created_at      TIMESTAMPTZ DEFAULT now(),
  last_login_at   TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- =======================================================================
-- ROLES — global capabilities a user holds
-- =======================================================================
--
-- 'admin'             — full superuser (Steve)
-- 'ops'               — verification ops staff (COI / W-9 / license queue)
-- 'installer_owner'   — controls one or more installer profiles
-- 'installer_member'  — works under an installer profile (e.g. crew lead)

CREATE TABLE IF NOT EXISTS roles (
  user_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role      TEXT NOT NULL,
  granted_at TIMESTAMPTZ DEFAULT now(),
  granted_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  PRIMARY KEY (user_id, role),
  CHECK (role IN ('admin','ops','installer_owner','installer_member'))
);

CREATE INDEX IF NOT EXISTS idx_roles_role ON roles(role);

-- =======================================================================
-- INSTALLER_MEMBERS — N-to-N user ↔ installer
-- =======================================================================
--
-- The `role` column here is local to the installer (NOT the global role
-- list above). Values: 'owner' | 'member'. An owner can edit the installer
-- profile, manage subscription, invite/remove members. A member can manage
-- bookings and calendar but not billing.

CREATE TABLE IF NOT EXISTS installer_members (
  installer_id  INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role          TEXT NOT NULL DEFAULT 'owner',
  added_at      TIMESTAMPTZ DEFAULT now(),
  added_by      INTEGER REFERENCES users(id) ON DELETE SET NULL,
  PRIMARY KEY (installer_id, user_id),
  CHECK (role IN ('owner','member'))
);

CREATE INDEX IF NOT EXISTS idx_installer_members_user ON installer_members(user_id);
CREATE INDEX IF NOT EXISTS idx_installer_members_installer ON installer_members(installer_id);

-- Single-owner lock (v1 decision, 2026-05-05).
--
-- v1 NPH assumes one operator per installer studio — the claim flow is
-- domain-restricted to a single email per studio website, billing assumes
-- one Stripe customer per installer, and the dashboard has a single user
-- view. Enforcing this at the DB layer prevents the entire class of bugs
-- where two "owners" disagree about subscription state, profile edits,
-- or who receives booking notifications.
--
-- If/when v2 needs co-owners (e.g. an agency operating 5 studios), drop
-- this index and replace with a richer `delegations` table — easier than
-- retrofitting consistency on top of dual-owner data later.
CREATE UNIQUE INDEX IF NOT EXISTS uq_installer_members_one_owner
  ON installer_members (installer_id)
  WHERE role = 'owner';

-- =======================================================================
-- BACKFILL — copy current installers identity into the new tables
-- =======================================================================
--
-- For every existing installers row that has email + password_hash, ensure:
--   1. A users row exists (matched on email).
--   2. That user holds the 'installer_owner' global role.
--   3. The user is linked to the installer via installer_members as 'owner'.
--
-- Idempotent: ON CONFLICT DO NOTHING on every insert. Safe to re-run after
-- new self-signups during the dual-write window.

INSERT INTO users (email, password_hash, name, created_at, last_login_at)
SELECT i.email, i.password_hash, COALESCE(i.contact_name, i.business_name),
       i.created_at, i.last_login_at
  FROM installers i
 WHERE i.email IS NOT NULL
   AND i.password_hash IS NOT NULL
ON CONFLICT (email) DO NOTHING;

INSERT INTO roles (user_id, role)
SELECT u.id, 'installer_owner'
  FROM users u
  JOIN installers i ON i.email = u.email
ON CONFLICT (user_id, role) DO NOTHING;

INSERT INTO installer_members (installer_id, user_id, role)
SELECT i.id, u.id, 'owner'
  FROM installers i
  JOIN users u ON u.email = i.email
ON CONFLICT (installer_id, user_id) DO NOTHING;

COMMIT;

-- NOTE: We are NOT dropping installers.email or installers.password_hash here.
-- Phase 2 will, once routes/auth.js, lib/auth.js, and routes/admin.js have
-- been switched to read from users + installer_members. See ARCHITECTURE_PHASE2.md.