# Profession vs project responsibility — split `project_teams.role`

Inventor: kepler · Date: 2026-05-07 · Issue: m/paliad#6 (t-paliad-148)
Branch: `mai/kepler/inventor-profession-vs`
Status: **READY FOR REVIEW** — awaits m's go on the 12 open questions before any coder shift.

---

## §0 TL;DR

`paliad.project_teams.role` does two jobs at once: it labels a user's
**career tier at the firm** (PA, Associate, Of Counsel) **and** it labels
their **responsibility on this project** (Lead, Observer). m's bug report
(2026-05-06): you don't redefine someone's profession when staffing them
on a matter. The team-add dropdown should let you pick *responsibility*
only; profession should come from the firm record.

This design splits the column into two:

1. **`paliad.users.profession`** — firm-wide career tier
   (`partner | of_counsel | associate | senior_pa | pa | paralegal | NULL`).
   Drives the t-138 approval ladder. NULL means "no firm tier" (external).
2. **`paliad.project_teams.responsibility`** — per-project responsibility
   (`lead | member | observer | external`). Default `member`. Drives a
   simple gate — `lead` and `member` open authority; `observer` and
   `external` close it. Replaces the team-add dropdown values m
   complained about.

Approval ladder migrates from `project_teams.role` to a tuple
**(profession, responsibility)** evaluated as: *level = profession_level
if responsibility ∈ {lead, member} else 0*. Policy grammar
(`required_role` single-value) stays unchanged from t-138.

Single migration 057. Backfills profession from the highest legacy
`project_teams.role` per user. project_teams.role kept as a deprecated
shadow column for one release, dropped in 058.

---

## §1 Problem & locked premises

### What m said (2026-05-06)

> "The Role should not be definable there. Whether a team member is PA
> or Associate etc is not defined when adding existing members. Roles
> for the project, maybe. But not the 'profession'."

### Locked decisions (m, 2026-05-06)

- **Profession is not redefined per project.** It comes from the user's
  firm-level record.
- **Project-level role is meaningful.** Stays editable per project, but
  with a smaller value set focused on responsibility.
- **Approval ladder must keep working** — t-138 just shipped. Whatever
  drives the ladder must still drive it.

### Three-axis principle (held since t-051)

> "Firm roles ≠ project roles ≠ tool roles."

Today's surfaces:

| Axis | Today's column | Today's values |
|---|---|---|
| Firm — display | `paliad.users.job_title` (free text) | "Counsel Knowledge Lawyer", "Junior Associate"… |
| Firm — tool admin | `paliad.users.global_role` | `standard \| global_admin` |
| Firm — partner-unit slot | `paliad.partner_unit_members.unit_role` | `lead \| attorney \| senior_pa \| pa \| paralegal` (per-unit, not firm-wide) |
| Project — staffing | `paliad.project_teams.role` | **mixed: profession + responsibility** ← the bug |

The split adds a fourth, missing axis — **firm career tier** as a
structured value that drives approval authority. It does not collapse
job_title (free-text label is still useful) or unit_role (per-unit slot
is still useful — see t-139 §11).

---

## §2 Verified live state (2026-05-07)

Probed `ydb` (paliad schema, port 11833) and current branch:

- `paliad.project_teams` CHECK on `role`: `lead, associate, pa,
  of_counsel, local_counsel, expert, observer, senior_pa` (from
  migration 054).
- `paliad.project_teams` row count: **3 rows, all `role='lead'`**.
- `paliad.partner_unit_members` CHECK on `unit_role`: `lead, attorney,
  senior_pa, pa, paralegal`. Row count: **20 rows, all
  `unit_role='attorney'`** (the default — nobody has overridden it
  yet).
- `paliad.users` columns include `job_title` (text NULL), `global_role`
  (text NOT NULL DEFAULT 'standard'). No profession column.
- `paliad.approval_role_level(text) RETURNS int IMMUTABLE` — strict
  ladder helper, used in 4 SQL sites in `approval_service.go`.
- `paliad.approval_role_from_unit_role(text) RETURNS text IMMUTABLE` —
  bridges unit_role → ladder values for derived authority.
- t-138 (commit `e2e1381`) and t-139 phases 1–3 all merged on `main`.
  Migration tracker at 56 (next is **057**).

**Implication of the live data**: backfill is essentially trivial. Three
project_teams rows. Twenty partner_unit_members rows. The risk surface
of the migration is the SQL rewiring, not the data movement.

### Inventory of references to migrate

| File | Site | What it reads |
|---|---|---|
| `internal/services/team_service.go:53,93,103,122,159` | INSERT/SELECT/validate | `pt.role` for read+write of project membership. |
| `internal/services/derivation_service.go:118,127,314,383,403` | EffectiveProjectRole + manage gate | `pt.role` for ancestor walk + `RoleLead` for project-lead-can-manage check. |
| `internal/services/approval_service.go:103,411,751,854` | canApprove + ListPending + bell badge + deadlock check | `paliad.approval_role_level(pt.role)` — 4 SQL sites. |
| `internal/services/reminder_service.go:317,330` | reminder digest filter | `pt.role = 'lead'` — project-responsibility check. |
| `internal/services/deadline_service.go:695` | legacy authority check | `pt.role IN ('admin', 'lead')` — `'admin'` is dead since t-051; this is half-broken already. |
| `internal/services/project_service.go:486` | creator-as-lead INSERT | `INSERT … role='lead'`. |
| `internal/services/approval_levels.go:70` | Go-side `levelOf()` | Mirror of SQL ladder. Must change with the SQL. |
| `internal/services/project_service.go:57-66` | `RoleLead` etc. constants | Used in 14 places across services. |
| `internal/db/migrations/055_hierarchy_aggregation.up.sql:84,92` | can_see_project body | `pt.role = 'lead'`. |
| `frontend/src/projects-detail.tsx:124-132` | team-add dropdown | The 7 mixed options m complained about. |
| `frontend/src/client/projects-detail.ts:1665,1720,1772,1856` | render + read of role | i18n `projects.team.role.*`. |
| `frontend/src/client/i18n.ts:1139-1145, 2949-2955` | role translations | DE+EN keys. |

This is a wide rewrite but it's mechanical — the column boundary is
clean, the call sites are narrow, and the live data is small.

---

## §3 Sub-design A — Profession axis  (Q1, Q2, Q3, Q12)

### Q1 — Where does profession live?  Recommendation: **(a) new `paliad.users.profession` column**

Three candidates from issue body:

(a) New `paliad.users.profession` column (firm-wide, simple).
(b) Reuse `paliad.partner_unit_members.unit_role` (already added by
    t-139 Phase 2; only set when the user is in a unit).
(c) New separate `paliad.user_professions(user_id, profession,
    valid_from)` table for history.

**Recommend (a).**

Rationale:

- (b) breaks for users not in a partner unit. Today: 31 users, ~20 in
  units. The other 11 (admins, externals, future hires) have no
  unit_role. Profession needs to be defined for everyone or the
  approval ladder gets gappy.
- (b) creates ambiguity if a user joins multiple units with different
  unit_roles (legal under the t-139 schema). Picking "the highest" or
  "the first" hides the data confusion. A firm-wide column is
  unambiguous by construction.
- (b) re-couples the per-unit axis to the firm-wide axis. t-139 §11
  explicitly kept `unit_role` per-unit to preserve the three-axis
  principle. Reusing it for firm-wide authority breaks that invariant.
- (c) overengineered for v1. Profession changes when an HR promotion
  fires — no audit, no time-slice. If history becomes a requirement,
  add the table later (out-of-scope per issue body).

(a) is one column, one CHECK, no joins on the read path, no per-unit
ambiguity. Drop-in replacement for the slot in the approval ladder.

**Schema:**

```sql
ALTER TABLE paliad.users
    ADD COLUMN profession text NULL
        CHECK (profession IS NULL OR profession IN (
            'partner', 'of_counsel', 'associate',
            'senior_pa', 'pa', 'paralegal'
        ));

CREATE INDEX users_profession_idx ON paliad.users (profession);
```

NULL is a valid value: it means "no firm career tier" (e.g. external
local counsel signed up via invitation, or admin accounts that aren't
practicing lawyers). NULL → ladder level 0 → ineligible to approve.

`job_title` (free-text display) and `global_role` (tool admin) remain
untouched. Three firm-axis columns:

| Column | Purpose | Approval-relevant? |
|---|---|---|
| `users.job_title` | Free-text display label ("Counsel Knowledge Lawyer") | No |
| `users.profession` | Structured career tier (drives ladder) | **Yes** |
| `users.global_role` | Tool admin gate (`standard \| global_admin`) | Override only |

### Q2 — Profession values  Recommendation: **`partner | of_counsel | associate | senior_pa | pa | paralegal`** (NULL = external)

The t-138 ladder defined 5 active levels. Today they are mixed
project-level + profession-level:

| Today | Level | Belongs on which axis? |
|---|---|---|
| `lead` | 5 | **project responsibility** (the lawyer in charge of THIS matter) |
| `of_counsel` | 4 | profession |
| `associate` | 3 | profession |
| `senior_pa` | 2 | profession |
| `pa` | 1 | profession |
| `local_counsel` | 0 | project responsibility (`external`) |
| `expert` | 0 | project responsibility (`external`) |
| `observer` | 0 | project responsibility |

Removing the project-axis values from the ladder leaves 4 profession
tiers (of_counsel, associate, senior_pa, pa). But "lead" was implicitly
"a partner is leading this matter", so profession needs **`partner`**
at level 5 to preserve the ceiling.

Add **`paralegal`** at level 0 (mirrors `partner_unit_members.unit_role`
which already has it; current `approval_role_from_unit_role` already
maps it to `observer`/level 0).

Final enum (6 values + NULL):

| Profession | Ladder level | Notes |
|---|---|---|
| `partner` | 5 | Replaces the project-level `lead` as the firm-tier ceiling. |
| `of_counsel` | 4 | unchanged |
| `associate` | 3 | unchanged; default for new firm members |
| `senior_pa` | 2 | unchanged |
| `pa` | 1 | unchanged |
| `paralegal` | 0 | New — present in unit_role; ineligible to approve. |
| NULL | 0 | "External / no firm tier." Approval-ineligible. |

**Why not include `senior_associate`, `counsel`, `trainee`, etc.** that
appear in the existing `i18n.team.role.*` keys (free-text user
directory): those values don't change the ladder level
(senior_associate = associate tier; counsel = of_counsel tier; trainee
= ineligible). Adding them inflates the enum without adding
authority-relevant distinctions. They live in `job_title` (free text)
where they belong. If HR later needs structured senior_associate vs
associate, the migration is one CHECK alter; the call sites are zero
because the ladder only sees levels.

**External roles (`local_counsel`, `expert`)** in the issue body are
project-only labels — they describe what a person *is on this matter*,
not a firm career tier. They land in §4 as `responsibility='external'`.
Their profession is NULL.

### Q3 — Onboarding flow  Recommendation: **required-on-invite, default suggestion = `associate`, admin-editable later**

Three options:

- (i) Auto-default to `associate` with admin-edit later.
- (ii) Required-on-invite: inviting colleague picks profession.
- (iii) User picks own profession on first login.

**Recommend (ii) with default = `associate`.**

Rationale:

- (i) recreates the bug m just complained about, in slow motion. Every
  PA invited gets shown as "associate" until someone notices and
  edits. The whole point of this work is "profession is real, set it
  honestly".
- (iii) is wrong: you don't redefine your own firm tier; HR/the firm
  does. Self-pick also breaks the audit (anyone could promote
  themselves).
- (ii) is one extra `<select>` on the existing invite form (already
  rebuilt for t-paliad-141). The inviter is a colleague — they know
  whether they're inviting a PA or an associate.
- Default `associate` makes the most common case one click. PAs and
  Of Counsels are explicit choices, not silent demotions.

**External invitees** (local counsel, expert): inviter sets
`responsibility='external'` on the project; profession defaults to
NULL (not asked) — the form hides the profession field when
responsibility=external. Admin can fill profession later if the
external collaborator becomes a paliad-tracked firm member.

### Q12 — Bulk add / invite-new flow  Recommendation: **profession capture on invite; NULL allowed; admin-edits later**

The existing invite-new-user flow (`team-user-invite-btn` →
`/api/team/invite-new`) accepts email + display_name today. After this
change:

- Invite form gains a profession `<select>` (6 values + "Extern (keine
  Profession)").
- Default selected: `associate`.
- Submit creates `paliad.users` row with the picked profession +
  `paliad.project_teams` row with the picked responsibility (default
  `member`).
- "Extern" sets `responsibility='external'` on the project_teams row,
  profession=NULL on users.

**No bulk-add UI exists today** — out of scope. If/when one ships, it
inherits the same per-row profession field.

**Admin re-edit**: `/admin/team` page (already shipped t-paliad-050)
gets a Profession column with inline-edit dropdown. Position next to
job_title. No last-admin guard needed (profession is not a tool gate).

---

## §4 Sub-design B — Project responsibility axis  (Q4, Q5, Q6, Q11)

### Q4 — Value set  Recommendation: **`lead | member | observer | external`**

Issue body suggests this set. Locking it.

| Value | Meaning | Edit/approve authority |
|---|---|---|
| `lead` | The responsible lawyer/partner for this matter. Also has project-management permissions (manage settings, attach partner units — already wired in derivation_service.go). | Full (subject to profession ceiling). |
| `member` | Staffed on this matter at their profession's level. | Full (subject to profession ceiling). |
| `observer` | Read-only awareness; no edit/approve authority. | None. |
| `external` | Non-firm collaborator (local counsel, expert witness). May edit per project policy, but cannot satisfy the firm-tier approval ladder. | Edit yes, approve no. |

**Why not collapse `external` into `observer`**: externals can actively
write (local counsel files briefs, experts upload reports). Observers
can't. The two are distinct read/write profiles and conflating them
loses information.

**Why not add `pa-on-this-project` etc.** — that's profession × project,
exactly what we just split. Once split, never re-mix.

### Q5 — Default value  Recommendation: **`member`**

m's intuition is right. Lock.

The team-add form's default selection is `member`. The project creator
is auto-added as `lead` (already coded in `project_service.go:486` —
just rename the inserted column from `role='lead'` to
`responsibility='lead'`).

### Q6 — Display  Recommendation: **3 columns: Name · Profession (read-only badge) · Responsibility (editable inline)**, plus the existing Herkunft column

Layout for the team table on `/projects/{id}` Tab:

```
| Name          | Profession      | Responsibility (edit) | Herkunft       | Aktion |
| Anna Schmidt  | [PA]            | [Lead ▾]              | direkt         |  🗑    |
| Max Mustermann| [Associate]     | [Member ▾]            | über X-Unit    |  🗑    |
| Carla Smith   | (extern)        | [External]            | direkt         |  🗑    |
```

- **Profession** column: read-only `.projekt-team-profession` pill (CSS
  variant of existing `.projekt-team-role`). Click for global_admin
  opens `/admin/team#user-{id}` for inline edit. For non-admins, a
  hover tooltip explains "Profession wird im Firmenprofil gepflegt".
- **Responsibility** column: existing inline-edit pattern (`.entity-row
  select`) — reuses the t-paliad-141 inline-edit affordance. Edit
  permission = project lead OR global_admin.
- NULL profession renders as `(extern)` or `(keine Profession)`
  depending on `responsibility`.

Inline prose elsewhere (Verlauf entries, inbox rows, email reminders):
**"Anna Schmidt (PA) — Lead"** — profession in parens, responsibility
after a dash. Explicit and parseable.

For the audit trail (`paliad.project_events`), emit
`team_member_added` with `metadata = {responsibility: 'member',
profession_at_time: 'pa'}` so historic rendering survives a profession
change.

### Q11 — Team table layout post-fix  Recommendation: **3-column tabular layout above; tooltip-only profession is rejected**

Two alternatives the issue posed:

- **Hover-only profession** ("Anna Schmidt — Lead", profession in
  tooltip badge): rejected. Profession is too important to hide. The
  whole point of the split is to make profession honestly visible.
- **3-column tabular**: chosen. Matches the existing `.entity-table`
  pattern; profession is glanceable.

Tooltip is still useful as *secondary* signal: hover the profession
badge → "PA — gesetzt im Firmenprofil. (Edit by global_admin only)".

The team-add form (the bug surface m complained about) loses the
mixed-axis dropdown. New form:

```
[ User autocomplete ▾ ]   ← picks Anna Schmidt
   Anna Schmidt (PA)       ← shown beneath, read from users.profession
[ Responsibility: Member ▾ ]   ← only dropdown left; default Member
[ Cancel ] [ Hinzufügen ]
```

**If the picked person has profession=NULL:** show a yellow warning
under the profession line: "*Anna hat keine Profession gesetzt — sie
kann keine 4-Augen-Genehmigungen erteilen. Admin im Firmenprofil
nachtragen.*" Doesn't block the add, just informs.

---

## §5 Sub-design C — Approval ladder rename + migration  (Q7, Q8, Q9, Q10)

### Q7 vs Q8 — Tuple-gated ladder  Recommendation: **Q7 (rename to profession) with project-responsibility as a binary gate**

Two views the issue posed:

- **Q7**: ladder migrates from `project_teams.role` → `users.profession`.
  Project responsibility goes elsewhere; the ladder is purely
  profession-driven.
- **Q8**: ladder becomes a tuple `(profession,
  project_responsibility)` — finer policies, e.g. "associate-level
  lawyer who is at least a member on this project".

**Recommend Q7-with-gate**: the ladder is profession-driven, and
project responsibility acts as a *binary gate* (open/closed) rather
than a separate dimension in the policy grammar.

Effective level for user U on project P:

```
profession_level = approval_role_level(U.profession)   -- 0 if NULL
responsibility   = project_teams.responsibility on P (direct or ancestor)
gate_open        = responsibility IN ('lead', 'member')

effective_level  = profession_level if gate_open else 0
```

For derived (partner-unit) authority (t-139):

```
derived_role     = approval_role_from_unit_role(unit_role)
                   when ppu.derive_grants_authority = true
effective_level  = max over all sources (direct, ancestor, derived)
```

(The "max" is operative because a user might be a `member` of one
project at profession=PA, AND derive-with-authority into the same
project via a partner-unit attachment with unit_role=senior_pa. Take
the higher.)

**Why not pure Q8?**

- Pure tuple-grammar means policies look like `required_role='associate'
  AND required_responsibility='lead'`. Fine for power users; nobody
  has asked. We can add the responsibility dimension to
  `approval_policies` later (one new nullable column) if the firm
  wants finer rules. v1 stays single-dimension, matching m's t-138
  Q3 lock ("per-(project, entity_type, lifecycle_event)
  required_role").
- Pure tuple also breaks Verlauf/audit phrasing — the audit currently
  reads "Genehmigung erforderlich: Associate-Tier oder höher", which
  stays clean under Q7-with-gate. Tuple grammar would need
  "Associate-Tier UND mindestens Mitglied".

**Why not pure Q7 without the gate?**

- Without the gate, an `observer` who happens to be a Partner could
  approve. That defeats the project-level call. The whole reason
  someone is set as observer is "you're not authoritative here, even
  though you're senior". The gate restores that semantics.
- `external` (local counsel) without a gate would also approve via
  their own firm tier — except their profession is NULL, so they're
  level 0 anyway. The gate is defense-in-depth there: if a future
  external is given profession=of_counsel by mistake, the
  responsibility=external still keeps them at level 0.

**Implementation site**: a new SQL function
`paliad.user_project_authority_level(_user_id uuid, _project_id uuid)
RETURNS int IMMUTABLE` encapsulates the (profession, responsibility,
derivation) computation. Replaces inline
`paliad.approval_role_level(pt.role)` at the 4
`approval_service.go` SQL sites. Plus a Go mirror
`UserProjectAuthorityLevel(ctx, userID, projectID) int` for callers
that need it without a SQL roundtrip (none today, but the
DerivationService.EffectiveProjectRole becomes a thin wrapper).

Policy grammar stays exactly as t-138 designed. `required_role` is a
profession value (`partner`, `of_counsel`, `associate`, `senior_pa`,
`pa`). The CHECK on `approval_policies.required_role` is updated to
the new enum (drop 'lead' — was the project-level value — and rename
nothing; the SQL ladder values are 1:1 except the ceiling). Existing
policy rows get backfilled `lead → partner` (the only mapping that
changes).

### Q9 — Backfill plan  Recommendation: **highest-tier-observed per user; `lead/of_counsel/associate/senior_pa/pa → matching profession`; `local_counsel/expert/observer → NULL`**

Backfill rules:

**Profession** (firm-wide, one row per user):

For each user with at least one `paliad.project_teams` row:

```
profession = highest tier among all (direct) project_teams rows
where:
  legacy 'lead'         → 'partner'   (level 5)
  legacy 'of_counsel'   → 'of_counsel'(level 4)
  legacy 'associate'    → 'associate' (level 3)
  legacy 'senior_pa'    → 'senior_pa' (level 2)
  legacy 'pa'           → 'pa'        (level 1)
  legacy 'local_counsel' → IGNORED
  legacy 'expert'        → IGNORED
  legacy 'observer'      → IGNORED
```

If after ignoring project-only labels the user has no firm-tier row →
profession = NULL.

For users with NO project_teams rows → profession = NULL too. Admin
edits at `/admin/team` if those users are firm members (the 11
unit-only users in current data).

**Tie-break**: pick the highest level. If a user is `lead` on Project A
and `pa` on Project B, profession = `partner` (level 5 > level 1).
This matches m's "highest-tier observed" rule from the issue body.

**Edge case — only `observer` rows**: the user has exactly one
`observer` row across all projects. Profession = NULL (no firm tier
inferable from the data). Admin will need to set it.

**Edge case — `local_counsel` rows only**: user is external. Profession
= NULL. Their project_teams.responsibility row will be 'external'
(see below).

**Responsibility** (per project_teams row):

```
legacy 'lead'          → 'lead'
legacy 'observer'      → 'observer'
legacy 'local_counsel' → 'external'
legacy 'expert'        → 'external'
legacy 'associate'     → 'member'
legacy 'pa'            → 'member'
legacy 'of_counsel'    → 'member'
legacy 'senior_pa'     → 'member'
```

This preserves m's stated rules:

- `lead` → `lead`
- `observer` → `observer`
- everything else (firm tier) → `member` (their authority is now
  encoded in their profession; the project row just says "they're
  staffed")

External labels (`local_counsel`, `expert`) get
`responsibility='external'`. Their profession remains NULL (the
backfill above ignores them for profession purposes).

**Live data sanity check**: today there are 3 project_teams rows, all
`role='lead'`. Backfill produces:

- 3 users get `profession='partner'`.
- 3 project_teams rows get `responsibility='lead'`.

All other users (28 of 31) get `profession=NULL` until admin edits
them at `/admin/team`. Acceptable — the firm has known they need an
audit pass over user records since t-051; this surfaces it cleanly.

### Q10 — Down-migration safety  Recommendation: **reversible with documented data loss on edge cases**

Down-migration steps (`057_down`):

1. Re-derive `project_teams.role` from `(responsibility, profession)`:

   ```sql
   UPDATE paliad.project_teams pt
      SET role = CASE
          WHEN pt.responsibility = 'lead'     THEN 'lead'
          WHEN pt.responsibility = 'observer' THEN 'observer'
          WHEN pt.responsibility = 'external' THEN 'local_counsel'
          WHEN pt.responsibility = 'member'  THEN COALESCE(
              (SELECT u.profession FROM paliad.users u WHERE u.id = pt.user_id),
              'associate'
          )
      END;
   ```

   - `external` always maps back to `local_counsel` (most common
     pre-split external label; `expert` is rarer and lossy).
   - `member` with profession=`partner` maps back to… ambiguous.
     Pre-split there was no firm-tier `partner` row in
     `project_teams`. Document data loss: maps to `of_counsel` (next
     highest legacy value). If the down is run, the partner re-appears
     as of_counsel on that project. Acceptable for a rollback.
   - `member` with profession=`paralegal` maps back to `pa` (closest
     legacy fit; `paralegal` was never a `project_teams.role` value).
   - `member` with profession=NULL maps back to `associate` (safe
     default, matches the legacy `RoleAssociate` default).

2. DROP COLUMN `paliad.users.profession`.
3. DROP COLUMN `paliad.project_teams.responsibility`.
4. Drop `paliad.user_project_authority_level` function.
5. Restore `approval_service.go` SQL sites to inline
   `approval_role_level(pt.role)`.

Down-migration is best-effort. Documented data loss in `057_down.sql`
comments. The Go code on `main` doesn't need to support both states
(paliad doesn't have multi-version-deployed history); a down is a
manual rollback path.

**Phasing**: `project_teams.role` stays on the table as a deprecated
shadow column for one release (migration 057 keeps it; migration 058 —
follow-up ticket — drops it after Go code is fully migrated). This
means even in the worst case, a fast down doesn't have to recompute
`role`; it just drops the new columns and keeps the old.

---

## §6 Migration plan — single migration 057

Filename: `internal/db/migrations/057_profession_vs_responsibility.up.sql`

Sections:

1. ADD `paliad.users.profession`.
2. ADD `paliad.project_teams.responsibility`.
3. CREATE `paliad.user_project_authority_level(user_id, project_id)`
   function.
4. UPDATE `paliad.approval_policies.required_role` CHECK to add
   `'partner'` and drop `'lead'`. Backfill `'lead'` → `'partner'` in
   any existing rows.
5. Backfill `users.profession` per Q9.
6. Backfill `project_teams.responsibility` per Q9.
7. UPDATE `paliad.can_see_project` body — replace `pt.role = 'lead'`
   with `pt.responsibility = 'lead'`. Function CASCADE-rebuild not
   needed (only function body changes).
8. UPDATE the comment on `paliad.approval_role_level` to point at
   `users.profession` instead of `project_teams.role`.

`project_teams.role` is **kept** in this migration (deprecated, not
read by any new code). Drop in follow-up migration 058 after Go code
fully migrates and is verified live.

### Service-layer migration (single PR alongside 057)

Files to edit:

- `internal/services/team_service.go` — INSERT/SELECT/validate the new
  `responsibility` column. `isValidRole` becomes
  `isValidResponsibility` with new enum.
- `internal/services/derivation_service.go` — `requireWritePermission`
  reads `pt.responsibility = 'lead'` instead of `pt.role = 'lead'`.
  `EffectiveProjectRole` (used by t-138 derived authority) replaced by
  `UserProjectAuthorityLevel` (returns int from the SQL function +
  source string). `ListAttachedUnits`, `ListDerivedMembers` unchanged
  (they don't touch the ladder column).
- `internal/services/approval_service.go` — 4 SQL sites switch from
  `paliad.approval_role_level(pt.role)` to
  `paliad.user_project_authority_level(pt.user_id, $project_id)`.
  Self-approval CHECK and policy lookup stay identical.
- `internal/services/approval_levels.go` — Go-side `levelOf()` becomes
  `professionLevel()`; new helper `responsibilityOpensGate()`.
  `RoleSeniorPA` constant stays (still a valid profession value,
  reused). New constants `ProfessionPartner`, `ProfessionOfCounsel`,
  `ProfessionAssociate`, `ProfessionSeniorPA`, `ProfessionPA`,
  `ProfessionParalegal`. New constants `ResponsibilityLead`,
  `ResponsibilityMember`, `ResponsibilityObserver`,
  `ResponsibilityExternal`.
- `internal/services/project_service.go:486` — INSERT writes
  `responsibility='lead'` (creator-as-lead). Old
  `RoleLead`/`RoleAssociate`/etc constants stay as aliases for one
  release to ease grep diffs; mark deprecated.
- `internal/services/reminder_service.go:317,330` —
  `pt.role = 'lead'` → `pt.responsibility = 'lead'`.
- `internal/services/deadline_service.go:695` —
  `pt.role IN ('admin', 'lead')` → `pt.responsibility = 'lead'`.
  (`'admin'` was already dead since t-051; this is also a small
  cleanup.)
- `internal/services/user_service.go` — onboarding/invite code
  accepts a `profession` arg, stores on insert.
- `internal/handlers/team.go` (and friends) — JSON shape change:
  `ProjectTeamMember` now exposes `responsibility` instead of `role`,
  embeds `User.Profession`.
- `internal/models/models.go` — `ProjectTeamMember.Role` → `.Responsibility`;
  `User` gains `.Profession *string`.

### Frontend migration (same PR)

- `frontend/src/projects-detail.tsx:124-132` — replace 7-option mixed
  dropdown with 4-option responsibility-only dropdown
  (`lead | member | observer | external`). Default `member`.
- `frontend/src/client/projects-detail.ts:1665,1720,1772,1856` — render
  3-column team table. New `.projekt-team-profession` CSS pill +
  i18n keys `projects.team.profession.partner` …
  `projects.team.profession.paralegal`. New i18n keys
  `projects.team.responsibility.lead` … `.external` (replace
  `projects.team.role.*`).
- `frontend/src/client/team.ts` — `/team` directory page: respect new
  profession column for grouping. Falls back to job_title when
  profession=NULL (existing free-text behaviour preserved for
  externals).
- `frontend/src/admin-team.tsx` + `client/admin-team.ts` — add
  Profession column with inline-edit dropdown.
- `frontend/src/onboarding.tsx` — invite flow gains a profession
  `<select>`.
- ~30 new i18n keys DE+EN.

### Tests

- `internal/services/team_service_test.go` — happy path on
  AddMember/RemoveMember with new responsibility values; reject
  invalid values.
- `internal/services/approval_service_test.go` — extend
  table-driven ladder tests to cover the
  (profession, responsibility) tuple. Cases: `partner+observer = 0`,
  `pa+lead = 1`, `null+member = 0`, derived+responsibility=external
  combinations.
- `internal/services/migration_057_test.go` — live-DB integration
  test (skipped without `TEST_DATABASE_URL`): apply migration on a
  seeded snapshot, assert backfill produces expected
  (profession, responsibility) pairs.

---

## §7 Implementation phasing

**Single PR, 6 commits** — the schema + service + frontend are tightly
coupled. Splitting risks half-broken intermediate states (the bug
report itself is about a half-broken intermediate state).

1. Migration 057 (schema + backfill + new SQL function). No code
   changes — server still reads `pt.role`. Verify backfill on live DB
   via BEGIN/ROLLBACK.
2. ApprovalService + DerivationService rewire. Tests updated. Build +
   test green. Server reads from new SQL function but writes still go
   to `pt.role` (will fix in commit 3).
3. TeamService + UserService rewire. INSERT writes
   `responsibility=...`. Reads return `responsibility`. Models
   updated. JSON schema change.
4. Frontend rewire — team-add dropdown, team table, admin-team,
   onboarding. New i18n keys.
5. Reminder + Deadline service touch-ups + can_see_project body
   refresh.
6. Lint + grep sweep — kill any remaining `pt.role` references that
   should have been migrated. Add a deprecation comment to the
   `RoleLead`/`RoleAssociate` Go constants pointing at the new ones.

**Follow-up ticket (out of scope for this PR)**: t-paliad-149 —
migration 058 to DROP COLUMN `project_teams.role` after one release of
soak time on main. Trivial when the time comes; just keeps this PR
clean.

**Recommended implementer**: any pattern-fluent coder. **NOT cronus**
(retired from paliad per memory directive). Sonnet work — 70% of the
diff is mechanical rename, 30% is the new SQL function + 4 ladder-site
rewrites + the new team-table layout. The substrate is well-trodden
(t-051 split established the pattern; t-138/t-139 left clean call
sites to migrate from).

---

## §8 Trade-offs flagged

1. **One migration touches both axes at once.** A pure-additive
   migration (add columns, leave `role`) would be safer-feeling, but
   then the team-add dropdown bug stays open (the UX lie m hates is
   still on screen until commit 4). I prefer one PR that ships the
   fix end-to-end, with `project_teams.role` deprecated-shadow for
   one release as the safety net.
2. **Profession=NULL semantics are load-bearing.** NULL means "no
   firm tier" → ladder level 0 → ineligible. If a developer later
   adds a fast-path that defaults NULL→`associate` for "convenience",
   externals would silently gain approval rights. Mitigation: explicit
   helper `professionLevel(*string) int` that returns 0 for NULL with
   a comment naming the trap. Add a unit test `TestProfessionLevel_NilIsZero`.
3. **`partner` is the new ceiling but `lead` is no longer a profession**.
   The mental jump for users: "Lead" was the highest in the dropdown;
   now "Partner" is. Renaming is honest but a moment of surprise.
   Mitigation: i18n keys carry over the lead-on-this-project sense via
   `projects.team.responsibility.lead` so the word "Lead" stays
   visible exactly where it should — the project axis. Profession's
   "Partner" appears in firm-context surfaces (admin/team, tooltips).
4. **Tuple-gated ladder vs pure-tuple grammar.** Choosing
   responsibility as a binary gate means a future "must be a member,
   not just having visibility" rule is easy. A future "must be lead
   AND of_counsel-tier or higher" rule needs a new dimension on
   `approval_policies` (new nullable column). Acceptable: zero
   policies today need it; cheap to add when one does.
5. **Backfill produces 28 NULL professions** out of 31 users (the
   ones not in any project_teams row). After ship, `/admin/team` will
   show a warning column "Profession nicht gesetzt" until admin
   completes the audit. This is honest visibility of pre-existing data
   debt rather than papering over with a guessed default.
6. **`approval_role_from_unit_role` doesn't change** but its callers
   (the derived-authority SQL branches in approval_service.go) need to
   move from "compare against `pt.role`" to "compare against
   `users.profession` of the project_teams row's user". Mechanical;
   listed in §6 file inventory.

---

## §9 Out of scope (v1)

- Replacing the partner-unit-derivation mechanism (t-139 Phase 2) —
  derivation stays exactly as designed.
- A full firm-roles / hierarchy / org-chart feature — this design adds
  one structured column (profession) and nothing more.
- Multi-profession (paralegal-turned-associate scenario). One
  profession per user; admin edits when promoted.
- Time-sliced profession history (who was a PA in 2024). Out per
  issue body.
- Adding a `responsibility` dimension to `approval_policies` (Q8 pure
  tuple grammar). Deferred to a future ticket if a real policy
  requires it.
- Bulk-add UI for project members. None exists today.
- Dropping `project_teams.role` itself. Deferred to follow-up
  migration 058 after one release of soak time.

---

## §10 12 Questions — Recommendation summary

| # | Question | Recommendation | Locked? |
|---|---|---|---|
| Q1 | Where does profession live? | (a) New `paliad.users.profession` text column | open — m sign-off |
| Q2 | Profession values | `partner \| of_counsel \| associate \| senior_pa \| pa \| paralegal` (NULL = external) | open — m sign-off |
| Q3 | Onboarding flow | Required-on-invite, default `associate`, admin-editable | open — m sign-off |
| Q4 | Project responsibility values | `lead \| member \| observer \| external` | open — m hinted yes |
| Q5 | Default value | `member` | open — m hinted yes |
| Q6 | Display | 3 columns: Name · Profession (badge) · Responsibility (inline-edit), plus existing Herkunft | open — m sign-off |
| Q7 vs Q8 | Ladder migration | Q7 (rename to profession) WITH project-responsibility as a binary gate (`responsibility ∈ {lead, member}` opens the gate) | open — main architectural call |
| Q9 | Backfill | Profession = highest legacy tier per user (`lead → partner`, `of_counsel → of_counsel`, …, externals → NULL); responsibility per single-row mapping (`lead → lead`, `observer → observer`, externals → `external`, others → `member`) | open — m sign-off |
| Q10 | Down-migration | Reversible with documented best-effort data loss; `project_teams.role` kept as deprecated shadow until follow-up 058 | open — m sign-off |
| Q11 | Team table layout | 3-column tabular (rejecting tooltip-only profession); inline-edit responsibility; profession edits live on `/admin/team` | open — m sign-off |
| Q12 | Bulk add / invite | Profession capture on invite (default `associate`, "Extern" hides field). No bulk-add v1. Admin re-edits via `/admin/team` | open — m sign-off |

---

## §11 Coordination with sibling work

- **t-138 (approvals)**: shipped 2026-05-06 (commit `e2e1381`). Migration
  054 sets up the ladder; this design extends it to read from
  `users.profession` instead of `project_teams.role`. Policy grammar
  unchanged. `required_role` enum gains `partner`, drops `lead`
  (renamed in backfill).
- **t-139 (hierarchy + derivation)**: all 3 phases shipped. Migration
  055 added `partner_unit_members.unit_role` and the
  `approval_role_from_unit_role` bridge. This design leaves the
  bridge untouched — `unit_role` values map 1:1 to the new profession
  enum (`lead → partner`, `attorney → associate`, `senior_pa →
  senior_pa`, `pa → pa`, `paralegal → paralegal`). Update the bridge's
  `lead → lead` row to `lead → partner` in migration 057.
- **t-144 (Custom Views)**: shipped. ViewService.runApprovalRequests
  uses ApprovalService.ListPendingForApprover, which reads the new
  ladder. Inherits the change automatically.
- **t-paliad-145 (local chat)**: parked. Not relevant.

No siblings are blocked by this work, and this work doesn't block any
sibling. Independent migration, independent merge.

---

## §12 Inventor parking

Inventor (kepler) parks here. Awaits m's pass through the 12 questions
in §10 + any course-correction. After m signs off, this design locks
and a fresh coder shift can pick up the single PR. Branch:
`mai/kepler/inventor-profession-vs`.

DESIGN READY FOR REVIEW.
