# RBAC Migration Spec: `users.role` → `tenant_memberships`

**Prerequisite for:** Customer 2 onboarding  
**Trigger:** When a second tenant is ready to onboard  
**Risk level:** Medium — touches auth, session, and every route guard; all in one migration  
**Estimated scope:** ~1 day of focused implementation + tests

---

## Context

The current implementation stores `role` and `tenant_id` directly on the `users` table. This works correctly for a single tenant but prevents the same email address from belonging to multiple tenants with different roles. The `tenant_memberships` table is the designed target state (see `docs/technical_spec.md §3.4`).

The `tenant_memberships` table does not exist yet. This spec covers building it, migrating data, and updating every layer that reads from `users.role` / `users.tenant_id`.

---

## Scope: files that change

### Schema and migrations
- `src/db/schema/users.ts` — remove `tenantId` and `role` columns
- `src/db/schema/memberships.ts` — new file, `tenantMemberships` table
- `src/db/schema/index.ts` — export `memberships`
- New Drizzle migration (generated)

### Auth layer
- `src/auth.ts` — `createUser` adapter + session callback
- `src/types/next-auth.d.ts` — no change (session shape is preserved)

### Role guard (central hub — highest priority)
- `src/lib/role-guard.ts` — switch all four functions from `users` JOIN to `tenant_memberships` JOIN

### Seeder
- `scripts/seed.ts` — write to `tenantMemberships` instead of `users.tenantId/role`

### API routes with direct `users.tenantId` / `users.role` lookups
These routes bypass `role-guard.ts` and query the `users` table directly:
- `src/app/api/tickets/route.ts:124` — selects `users.tenantId` for submitter lookup
- `src/app/api/tickets/[ticketId]/messages/route.ts:37` — selects `users.tenantId`
- `src/app/api/tickets/[ticketId]/poll/route.ts:19` — selects `users.tenantId`
- `src/app/api/admin/users/route.ts` — queries and inserts `users.tenantId/role`
- `src/app/api/admin/users/[userId]/route.ts` — queries and updates `users.tenantId/role`
- `src/app/api/dev-login/route.ts:22` — selects `users.role`

### Delete
- `src/app/api/users/register/route.ts` — dead code; replaced by invite guard (which predates this migration)

---

## 1. Database changes

### New table: `tenant_memberships`

Create `src/db/schema/memberships.ts`:

```ts
import { pgTable, uuid, text, boolean, timestamp } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

export const tenantMemberships = pgTable('tenant_memberships', {
  id: uuid('id').primaryKey().default(sql`gen_random_uuid()`),
  tenantId: uuid('tenant_id').notNull().references(() => tenants.id),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  role: text('role', { enum: ['line_manager', 'expert', 'admin'] }).notNull(),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().default(sql`now()`),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().default(sql`now()`),
})
```

Add a unique index on `(tenant_id, user_id)` — one membership row per tenant per user.

### Changes to `users` table

Remove from `src/db/schema/users.ts`:
- `tenantId: uuid('tenant_id').notNull().references(() => tenants.id)`
- `role: text('role', { enum: [...] }).notNull().default('line_manager')`

### Migration SQL (Drizzle generates this — verify before running)

```sql
-- Step 1: create the memberships table
CREATE TABLE tenant_memberships (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role        TEXT NOT NULL CHECK (role IN ('line_manager', 'expert', 'admin')),
  is_active   BOOLEAN NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, user_id)
);

-- Step 2: backfill from existing users
INSERT INTO tenant_memberships (tenant_id, user_id, role, is_active)
SELECT tenant_id, id, role, is_active
FROM users
WHERE tenant_id IS NOT NULL;

-- Step 3: drop columns from users
ALTER TABLE users DROP COLUMN tenant_id;
ALTER TABLE users DROP COLUMN role;
```

**Run steps 1 and 2 as a single transaction. Verify the membership count matches the user count before committing step 3.**

---

## 2. Auth layer (`src/auth.ts`)

### Session callback

Currently reads `tenantId` and `role` directly from `users`. After migration, resolves them from the user's single active membership:

```ts
async session({ session, user }) {
  const [membership] = await db
    .select({
      tenantId: tenantMemberships.tenantId,
      role: tenantMemberships.role,
    })
    .from(tenantMemberships)
    .where(
      and(
        eq(tenantMemberships.userId, user.id),
        eq(tenantMemberships.isActive, true),
      )
    )
    .limit(1)

  session.user.id = user.id
  session.user.tenantId = membership?.tenantId ?? ''
  session.user.role = membership?.role ?? 'line_manager'
  return session
}
```

The session shape exposed to the rest of the app (`tenantId`, `role`) does not change. All existing route and guard code continues to work without modification.

### `createUser` adapter

Currently grabs the first tenant with `.limit(1)` and sets `role: 'line_manager'`. After migration:

1. Look up the email in `tenant_memberships` indirectly — via the invite lists already stored in `tenant_configs` (`initialExpertInvites`, `initialDualAccessInvites`) and `initial_line_manager_invites` (once added to `tenant_configs`)
2. If found: create the `users` row (no `tenantId`, no `role`), then insert a `tenantMemberships` row for each matched tenant with the correct role
3. If not found: throw — login is rejected (invite guard behavior)

At this point `createUser` effectively merges with the invite guard. The invite guard added before Customer 1 should be refactored into this function rather than kept as a separate check.

---

## 3. Role guard (`src/lib/role-guard.ts`)

All four functions (`requireExpertUser`, `requireExpertApiUser`, `requireAdminUser`, `requireAdminApiUser`) currently do:

```ts
const [user] = await db
  .select({ id, tenantId: users.tenantId, role: users.role, isActive: users.isActive })
  .from(users)
  .where(eq(users.id, session.user.id))
```

Replace with a JOIN to `tenant_memberships` using `session.user.tenantId` to resolve the correct membership:

```ts
const [membership] = await db
  .select({
    id: users.id,
    tenantId: tenantMemberships.tenantId,
    role: tenantMemberships.role,
    isActive: tenantMemberships.isActive,
  })
  .from(users)
  .innerJoin(
    tenantMemberships,
    and(
      eq(tenantMemberships.userId, users.id),
      eq(tenantMemberships.tenantId, session.user.tenantId),
    )
  )
  .where(eq(users.id, session.user.id))
  .limit(1)
```

The returned shape (`{ id, tenantId, role }`) is identical. All callers are unaffected.

---

## 4. API routes with direct `users` lookups

These routes do their own DB lookup to get the user's `tenantId`, bypassing `role-guard`. After removing `tenantId` from `users`, they must read it from `tenant_memberships` via a JOIN, or use `session.user.tenantId` directly (simpler, since the session is already verified by the guard that precedes them).

**Recommended fix**: use `session.user.tenantId` from the already-validated session rather than re-querying the DB. The session has already been verified by the time these routes execute.

Files and what to do:

- `src/app/api/tickets/route.ts:124` — replace `select users.tenantId` lookup with `session.user.tenantId` from the session already loaded earlier in the same handler
- `src/app/api/tickets/[ticketId]/messages/route.ts:37` — same
- `src/app/api/tickets/[ticketId]/poll/route.ts:19` — same
- `src/app/api/dev-login/route.ts:22` — join to `tenantMemberships` to resolve role for redirect; or just redirect to `/submit` by default (dev only, low stakes)

### Admin users routes (require more care)

`src/app/api/admin/users/route.ts` — currently inserts new users with `tenantId` and `role` directly on the `users` row. After migration:
- `GET`: JOIN `tenant_memberships` to get the role for each user in the current tenant
- `POST`: insert into `users` (no `tenantId`/`role`), then insert into `tenantMemberships`

`src/app/api/admin/users/[userId]/route.ts` — currently patches `users.role` and checks `users.tenantId`. After migration:
- `PATCH`: update `tenantMemberships.role` for `(tenantId, userId)` — not `users.role`
- Auth check: verify membership row exists for this tenant, not `users.tenantId`

---

## 5. Seeder (`scripts/seed.ts`)

Replace all `db.insert(users).values({ tenantId, role, ... })` calls with a two-step insert:

```ts
// 1. Upsert global user (no tenantId or role)
const [user] = await db
  .insert(users)
  .values({ email, displayName: null, authProvider: 'magic_link' })
  .onConflictDoUpdate({ target: users.email, set: { updatedAt: new Date() } })
  .returning()

// 2. Upsert membership
await db
  .insert(tenantMemberships)
  .values({ tenantId, userId: user.id, role })
  .onConflictDoUpdate({
    target: [tenantMemberships.tenantId, tenantMemberships.userId],
    set: { role, updatedAt: new Date() },
  })
```

The seeder is idempotent — re-running it for a second tenant adds memberships for that tenant without touching the first.

---

## 6. Testing requirements

Per project rules, all server-side changes require tests in the same commit.

**Required new tests:**

- `tests/rbac-membership.integration.test.ts`
  - New user email in invite list → `users` row created (no `tenantId`/`role`), `tenant_memberships` row created with correct role
  - Unknown email → rejected, no rows created
  - Session callback: user with one membership → `tenantId` and `role` populated correctly

- `tests/role-guard.integration.test.ts` (update existing if present)
  - `requireExpertUser` with valid expert membership → passes
  - `requireExpertUser` with `line_manager` membership → redirects to `/submit`
  - `requireAdminUser` with `admin` membership → passes
  - Inactive membership (`is_active = false`) → treated as unauthorized

- Update `tests/admin-users.integration.test.ts` (or equivalent):
  - `POST /api/admin/users` creates `users` row + `tenantMemberships` row
  - `PATCH /api/admin/users/:id` updates `tenantMemberships.role` not `users.role`
  - User from different tenant cannot be patched

**Existing tests that must still pass without modification:**
- All ticket route integration tests — session shape is preserved
- `tests/tenant-config-loader.test.ts`
- `tests/ticket-state-machine.test.ts`

---

## 7. Migration order of operations

Do not attempt to run this under time pressure or while Customer 1 is actively using the system mid-session.

1. Write the Drizzle schema changes and generate the migration
2. Deploy to a staging environment (if available) and run the migration — verify Customer 1 sessions still resolve correctly
3. Run the migration on production (steps 1 and 2 of the SQL as a transaction; verify row counts; then step 3)
4. Deploy updated application code
5. Verify Customer 1 can log in and their session resolves `tenantId` and `role` correctly
6. Insert the new tenant row for Customer 2 and run the seeder for that tenant
7. Verify Customer 2 users can log in and see only their tenant's data

---

## 8. What does NOT change

- `session.user.tenantId` and `session.user.role` — same shape, same meaning, just sourced differently
- All route handlers that use `guard.user.tenantId` and `guard.user.role` — zero changes needed
- `tenant_id` columns on all data tables (`tickets`, `knowledge_entries`, `projects`, etc.) — untouched
- The invite-only login model — `createUser` continues to enforce it; the invite list lookup moves from `users` existence check to `tenant_memberships` / `tenant_configs` lookup
- The `TENANT_CONFIG_PATH` / config file strategy — unchanged