Facilitator Attribution Layer — Feature Spec¶

Status: Draft — pending SD review Author: Claude (Opus, planning role) — 2026-05-03 Target path: docs/specs/facilitator-attribution-layer-feature.md Related issue: #615 (D13) Blocks: #617 (commission ledger / D4), #618 (referral links / D5) Related epic: #244 (portal completion epic) Related ADR: ADR-0018 — Multi-Tenancy + 7-Actor Platform Architecture Companion spec: docs/specs/facilitator-portal-frontend-feature.md §5.4b + §10 D13 Predecessor specs: - docs/specs/multi-tenancy-phase3-coordinators-facilitators-impl.md (Phase 3 — shipped) - docs/specs/admin-portal-users-tenants-feature.md (admin tenant CRUD shape — shipped)

1. Goal + Non-Goals¶

Goal¶

Ship the structured backend attribution layer that lets the platform answer "which facilitator generated this patient/case?" — independently of post-hoc delegation (CaseShare). Concretely:

1. New facilitators table on tenant tenant-curaway-facilitators (one row per real-world facilitator entity).
2. New nullable FK Patient.referred_by_facilitator_id → facilitators.id.
3. New nullable FK Case.referred_by_facilitator_id (denormalized at case creation) → facilitators.id.
4. CRUD admin endpoints to create/list/update facilitators (super_admin only).
5. New "sourced cases" endpoint scoped to the JWT-resolved facilitator: GET /api/v1/facilitator/sourced-cases.
6. Two new permission codes, granted via Alembic seed.
7. Reversible Alembic migration; backfill rule referred_by_facilitator_id = NULL for the 41 existing patients (treated as "direct signup").
8. Repository + service layer in line with BaseRepository / BaseUnscopedRepository per ADR-0016.

After this work: - D5 (#618) can wire the referral-link → patient registration cookie loop with somewhere to write the attribution. - D4 (#617) can build a commission ledger that joins commissions.facilitator_id ←→ facilitators.id ←→ cases.referred_by_facilitator_id. - The facilitator portal's DelegatedCases page (mislabelled today per portal spec §5.4b) can grow a sibling tab "Sourced cases" (US-1 sister story).

Non-Goals (deferred)¶

• Commission model + ledger — that's #617 / D4 and lives in its own spec. Attribution must land first; commissions are pure consumers of the FK shipped here.
• Referral link infrastructure — #618 / D5; same logic. The cookie reader at signup will use the new Patient.referred_by_facilitator_id field shipped here, but the link table + redirect handler are out of scope for this spec.
• Facilitator user management UI in the admin portal — slice 2 of admin-portal work. This spec ships the API surface so the UI work is unblocked.
• Multi-user-per-facilitator (e.g. an agency with two staff sharing one facilitator entity) — out of scope; v1 is one Clerk user per facilitator, like coordinators.
• Migrating existing Patient.referral_source free-text values — kept as informational legacy column. We do not attempt to parse "google_search" strings into facilitator FKs. See §6 / DP-D.
• Auto-creating a CaseShare row when a facilitator-sourced case is created — sourcing ≠ delegation by design. The facilitator's read access still requires explicit patient consent through the existing /consent/facilitator/grant flow.
• Public facilitator profile pages / marketing surfaces — different domain, marketing repo.
• Analytics aggregations (e.g. "top 10 facilitators by case volume") — out of scope; trivial follow-up once the FK exists.
• GDPR Article 17 erasure for facilitators themselves — handled by the existing erasure cascade once the model lands; no special design work required beyond adding facilitators to the cascade plan (covered in §13).
• Neo4j attribution edges (e.g. (:Patient)-[:REFERRED_BY]->(:Facilitator)) — Postgres-only for v1; mirror to graph in a follow-up if matching engine grows facilitator-aware features.

2. Why Now¶

2.1 Sequencing¶

SD's Phase 4 (per docs/specs/multi-tenancy-phase3-coordinators-facilitators-impl.md:48-51) wants Commissions + Referral Links live behind feature flags, then progressively turned on. Audit in portal spec §5.4b proved that neither feature has anywhere to write its data today:

D13 (this spec, #615)  →  D5 (#618 referral links)  →  D4 (#617 commissions)
        │                       │                              │
        │                       └─ writes Patient.refby_fac_id │
        │                                                      │
        └─────────── adds the column they both need ───────────┘

Without D13, #617's Commission.facilitator_id has nothing to FK to (the roles[code='facilitator'] row + CaseShare.actor_id Clerk user_id is not a database identity — it's a string with no FK and no commission_pct). Without D13, #618's referral-link redirect handler can write a value into a non-existent column.

2.2 Why this is a real gap, not paper architecture¶

Audit findings, fresh 2026-05-03:

$ grep -rli FacilitatorPartnership /Users/srikanthdonthi/Code/Curaway/curaway_src/app /Users/srikanthdonthi/Code/Curaway/curaway_src/alembic
# (no output)

The only thing in the codebase that smells like it: Patient.referral_source: str(100) (free-text) at app/models/patient.py:80. There is no facilitators table. There is no FK from Patient or Case to a structured facilitator record. ADR-0018 line 20 promises "row-level by facilitator_id" — the column doesn't exist.

2.3 Why decoupling from CaseShare matters¶

CaseShare with actor_type='facilitator' is post-hoc delegation (app/models/case_share.py:61-143). It records "the patient granted this facilitator read access to an existing case." That is a fundamentally different relationship from "this case originated because the facilitator referred the patient." Conflating the two is what produced the portal-spec mislabelling: today the DelegatedCases page can answer "which cases am I authorized to see?" but cannot answer "which cases did I generate?" — and commission payouts depend on the latter.

2.4 ADR-0018 inconsistency uncovered during this audit¶

The portal spec and issue #615 both claim ADR-0018 line 253 declares FacilitatorPartnership.commission_pct. Re-reading the ADR (lines 249-256), that block actually defines hospital_recovery_partnerships:

hospital_recovery_partnerships (new)
  id, surgical_provider_id, recovery_provider_id, tenant_id,
  partnership_type: ENUM(hospital_recommended, curaway_partnered, marketplace),
  status: ENUM(active, suspended, expired),
  commission_pct: DECIMAL,
  ...

i.e. the only commission_pct field in ADR-0018 is on a recovery-provider partnership, not a facilitator entity. ADR-0018 line 20 does mention "commission" abstractly for the facilitator actor row but never formalizes a schema. This spec is therefore the first concrete schema for facilitator commission_pct — flagging so reviewers know we are not duplicating an existing design, only extending the implicit one. Capture as a one-line ADR-0018 amendment when this spec ships.

3. Audit of Existing Data (verified 2026-05-03)¶

Mirrors the portal spec §5.4b table format, re-verified against main HEAD (commit 9aa62e7).

3.1 What captures source-attribution-ish data today¶

3.2 Patient row count (production)¶

41 patient rows on tenant-curaway-patients per #524 cutover. All of them were created before facilitator infrastructure existed. Default backfill rule Patient.referred_by_facilitator_id = NULL applies to all 41 (DP-D, default).

3.3 What is not in the database today¶

• No facilitators table — confirmed by grep -rli FacilitatorPartnership returning empty.
• No commissions table — grep -li commission app/ returns only app/seed_graph.py (Neo4j relationship label, not application logic).
• No referral_links table.
• No Patient.referred_by_facilitator_id, no Case.referred_by_facilitator_id.
• No model for commission_pct anywhere in app/models/ (the only commission_pct declared in ADR-0018 line 253 is on hospital_recovery_partnerships, see §2.4).

3.4 What patterns exist that we should mirror, not invent¶

• Tenant scoping: BaseRepository._scoped_query(tenant_id) (app/repositories/base.py:54-62) raises TenantIsolationViolation on empty tenant_id.
• Cross-tenant catalog: BaseUnscopedRepository (app/repositories/base.py:189-237) — used by RoleRepository (app/repositories/role_repository.py:19-37).
• Audit emission: BaseRepository._audit(...) (app/repositories/base.py:82-103) — see usage in RoleRepository.assign_role at app/repositories/role_repository.py:89-117.
• Idempotent seed migration: 824b672ec7a4_grant_portal_permissions_to_roles.py:88-152 (JSONB || append guarded by NOT (permissions @> '...'), downgrade via permissions - '...').
• Idempotent table create: cab789eed1ca_add_phase0_rbac_and_case_shares.py:96-220 (CREATE TABLE IF NOT EXISTS, ALTER TABLE … ENABLE ROW LEVEL SECURITY, INSERT … ON CONFLICT DO NOTHING).
• Soft-delete column convention: Patient.is_active + Patient.is_deleted (app/models/patient.py:83-84); BaseRepository._active_only(query) adds WHERE is_deleted=False automatically.
• Permission decorator + grants table: @require_permission(...) (app/middleware/require_permission.py:31-100) + _GRANTS tuple in 824b672ec7a4_grant_portal_permissions_to_roles.py:88-113.
• Admin CRUD route shape: app/routers/admin_tenants.py:1-80 (regex-validated path params, super_admin / platform_admin perms, standard APIResponse envelope).

4. Proposed Schema¶

4.1 facilitators table¶

4.1.1 Indexes (explicit list)¶

• ix_facilitators_tenant_id — every BaseRepository query starts with WHERE tenant_id = :t. Non-unique.
• ux_facilitators_email_lower — partial unique index, LOWER(email) WHERE is_deleted = FALSE. Prevents two active facilitators sharing an email; allows re-creating after soft-delete.
• ux_facilitators_clerk_user_id — partial unique index, WHERE clerk_user_id IS NOT NULL. Prevents two facilitator entities mapping to the same Clerk user once they sign up.
• (Optional, see §11) ix_facilitators_active_tenant covering index on (tenant_id, is_active, is_deleted) if list-active queries dominate.

4.1.2 RLS policy¶

Single-scope (mirrors user_roles, NOT the case_shares dual-scope pattern):

ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON facilitators
  USING (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

4.2 Patient.referred_by_facilitator_id (new column)¶

4.3 Case.referred_by_facilitator_id (denormalization)¶

4.3.1 Justification: ON¶

• Commission queries are case-centric, not patient-centric. SELECT … FROM cases WHERE referred_by_facilitator_id = :f AND status = 'forwarded' is the canonical commission accrual query in #617. JOIN through Patient would require either (a) a cross-tenant join (Patient on tenant-curaway-patients, Case on the patient's tenant) or (b) the matching ORM session to set both tenants — both fragile.
• Immutability matches DP-C semantics: even if Patient.referred_by_facilitator_id is later corrected by admin, the case's attribution stays frozen at creation time. Case.referred_by_facilitator_id is the audit trail.
• Cost is low: one extra column per case row (UUID = 36 bytes). No write-amplification because cases are created once.
• Read perf: index lookup is O(log n); JOIN-through-Patient is O(n) without an index pair on both sides.

4.3.2 Alternative considered: OFF¶

• Pros: less denormalization; one fewer column to maintain.
• Cons: every commission query is a JOIN; immutability requires a separate commission_attribution_audit table (more complex). Cross-tenant JOIN risk.
• Rejected.

4.4 Audit + soft-delete strategy¶

• Every create/update/delete on facilitators emits an AuditLog row via _audit(...) (mirrors RoleRepository.assign_role at app/repositories/role_repository.py:89-117).
• Soft-delete (is_deleted=true) preserves history for commissions: a deleted facilitator's past commissions remain queryable.
• Hard-delete reserved for GDPR Article 17 erasure (docs/runbook/erasure.md cascade — to be amended by this PR per §13).

5. API Surface¶

All under /api/v1/. Standard APIResponse envelope per app/schemas/response.py. All requests require X-Tenant-ID + Clerk JWT. X-Correlation-ID auto-generated.

5.1 Admin endpoints (super_admin only, until DP-G expands)¶

GET /api/v1/admin/facilitators¶

• Permission: facilitator_admin:manage (new perm — see §6).
• Query params:
• is_active: bool | None (filter — default unfiltered)
• q: str | None (case-insensitive name/email substring; minimum 2 chars)
• page: int = 1, page_size: int = 20 (max 100)
• Tenant context: X-Tenant-ID must be tenant-curaway-facilitators OR caller has tenant:override (super_admin) for cross-tenant inspection.
• Response shape: APIResponse[list[FacilitatorRead]] (paginated wrapper or list — match admin_users.py pattern, TBD in implementation).
• Returns: facilitators ordered by created_at DESC. Soft-deleted excluded by default.

POST /api/v1/admin/facilitators¶

• Permission: facilitator_admin:manage.
• Body (FacilitatorCreate): name (1-200), email (RFC 5322), phone (optional, ≤50), commission_pct (0..1, ≤4dp), currency_code (ISO 4217 alpha-3), notes (optional), metadata_json (optional). clerk_user_id is not accepted at create (DP-A default — populated later by webhook).
• Tenant context: request X-Tenant-ID must equal tenant-curaway-facilitators.
• Idempotency: standard X-Idempotency-Key per CLAUDE.md "API Conventions". email is the natural dedup key — duplicate active email → 409 with error_code=FACILITATOR_DUPLICATE_EMAIL.
• Response: APIResponse[FacilitatorRead], 201 Created.

PATCH /api/v1/admin/facilitators/{facilitator_id}¶

• Permission: facilitator_admin:manage.
• Path param regex: ^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$ (UUIDv4).
• Body (FacilitatorUpdate): any subset of: name, email, phone, commission_pct, currency_code, is_active, notes, metadata_json. Cannot mutate tenant_id, id, clerk_user_id (the last is set by Clerk webhook only).
• Behavior on commission_pct change: new value applies to new cases only. Historical Case.referred_by_facilitator_id rows + future commissions on them remain attributed to the same facilitator, but the commission_pct used for any historical commission is whatever was on the facilitator row at the time the commission was accrued (D4/#617 freezes it on the commission row, not here). DP-C consistency.
• Audit: facilitator.update with {"before": {...}, "after": {...}} payload.
• Response: APIResponse[FacilitatorRead].

DELETE /api/v1/admin/facilitators/{facilitator_id}¶

• Permission: facilitator_admin:manage (delete) AND admin:force (super_admin only) if facilitator has any cases attributed.
• Behavior: soft-delete (is_active=false, is_deleted=true). Hard-delete reserved for GDPR cascade.
• Guard: if Patient.referred_by_facilitator_id or Case.referred_by_facilitator_id references this facilitator and admin:force is missing → 409 FACILITATOR_HAS_ATTRIBUTED_RECORDS. Mirrors the active-cases guard on tenant deactivate (app/services/admin_tenant_service.py).

5.2 Facilitator-facing endpoint (the JWT user themselves)¶

GET /api/v1/facilitator/sourced-cases¶

• Permission: facilitator:sourced-cases:read (new perm — granted to facilitator role).
• Tenant context: X-Tenant-ID = tenant-curaway-facilitators.
• Resolution path: request.state.user_id → facilitators row WHERE clerk_user_id = :user_id AND is_deleted=false AND is_active=true → facilitator_id → query cases WHERE referred_by_facilitator_id = :facilitator_id.
• If no facilitator row matches: return APIResponse[[]] (empty), NOT 404. The user is authenticated but not a known facilitator entity; treat as "no sourced cases yet."
• Cross-tenant query: cases table is on the patient's tenant (tenant-curaway-patients), facilitator is on tenant-curaway-facilitators. Use BaseUnscopedRepository for the cases query — same pattern match_service.get_doctors_for_procedure uses post-#604. Filter strictly by referred_by_facilitator_id. The target_tenant_id filter that protects delegated-cases doesn't apply here (sourcing is not a CaseShare relationship).
• Response shape: APIResponse[list[SourcedCaseItem]]:

  SourcedCaseItem {
    case_id: str            // Case.id
    case_number: str        // Case.case_number (human-readable)
    procedure_name: str | None
    status: str             // Case.status
    source_tenant_id: str   // = patient's tenant (always tenant-curaway-patients today)
    referred_at: str        // = Case.created_at, ISO 8601 UTC
  }
  

• What it deliberately does NOT return: patient name, patient email, clinical detail, anything PHI/PII. Sourcing is a billing/attribution concern, not a clinical-access concern. The facilitator gets clinical access only through CaseShare (the existing delegated-cases flow).
• Pagination: page, page_size — default 20, max 100. Default ordering: Case.created_at DESC.
• Why separate from delegated-cases: semantics differ — sourced ≠ delegated. A facilitator may have sourced 50 cases but only 20 of them have an active consent CaseShare; both numbers must be independently visible.

5.3 Webhook (Clerk → backend) — populates clerk_user_id (DP-A consequence)¶

• Endpoint: existing Clerk webhook (app/routers/webhooks_clerk.py if extant, or grow this work into a separate handler — verify in implementation).
• Trigger: Clerk organizationMembership.created for the facilitator org.
• Action: lookup facilitators row by email (case-insensitive); if found and clerk_user_id IS NULL, set it. If the email isn't found or the row already has a different clerk_user_id → log WARNING + Telegram alert per app/services/alerting.py. Do NOT silently overwrite.

5.4 Patient registration — accepts optional referred_by_facilitator_id¶

• Endpoint: existing POST /api/v1/patients/register (app/routers/patients.py:58-).
• Body (PatientRegister extension): add optional referred_by_facilitator_id: str | None (UUID validation when present).
• Behavior: if present, validate via BaseUnscopedRepository.get_by_id against facilitators (cross-tenant read; facilitator must be is_active=true AND is_deleted=false); else 422 with error_code=FACILITATOR_NOT_FOUND. On success, persist into Patient.referred_by_facilitator_id.
• Permission: unchanged — patient self-registration. Tenant: tenant-curaway-patients.
• No backwards-incompatible change: the field is optional, defaults to NULL. Existing patient-app code that doesn't send the field continues to work.

5.5 Case creation — propagates to Case.referred_by_facilitator_id¶

• Where: app/services/patient_service.py (or wherever Case rows are first inserted — app/services/case_service.py if applicable; verify in impl).
• Behavior: at case creation, copy Patient.referred_by_facilitator_id into Case.referred_by_facilitator_id. Frozen thereafter — DP-C.
• Edge case: if patient has NULL referred_by_facilitator_id at case-creation time but admin later attaches one, new cases pick up the new value; existing cases keep their original NULL. This is the DP-C immutability default.

5.6 Permissions matrix¶

Two new permission codes, two grant rows in the seed migration (mirrors 824b672ec7a4_grant_portal_permissions_to_roles.py).

Update both config/rbac_defaults.yaml (the canonical YAML at lines 17-77) AND emit a JSONB seed via Alembic per the _GRANTS tuple pattern at 824b672ec7a4_grant_portal_permissions_to_roles.py:88-113.

6. Service + Repository Layout¶

Follow ADR-0016 dependency direction: Models → Repositories → Services → Routers. Repos flush, never commit.

6.1 Repositories (new files)¶

app/repositories/facilitator_repository.py¶

• Class FacilitatorRepository(BaseRepository), model = Facilitator.
• Tenant-scoped (every query starts from _scoped_query(tenant_id)).
• Methods:
• get_by_id(facilitator_id, tenant_id) -> Facilitator | None
• get_by_email(email_lower, tenant_id) -> Facilitator | None — case-insensitive
• get_by_clerk_user_id(clerk_user_id, tenant_id) -> Facilitator | None
• list_active(tenant_id, q=None, is_active=True, page=1, page_size=20) -> tuple[list, int]
• create(facilitator, tenant_id, actor_id) -> Facilitator — emits facilitator.create audit
• update(facilitator, tenant_id, actor_id, changes) -> Facilitator — emits facilitator.update with before/after
• soft_delete(facilitator_id, tenant_id, actor_id) -> bool — emits facilitator.soft_delete
• attach_clerk_user_id(facilitator_id, clerk_user_id, tenant_id, actor_id="webhook:clerk") -> Facilitator — emits facilitator.clerk_link. Rejects overwrite of an already-set clerk_user_id.
• Audit pattern mirrors RoleRepository.assign_role at app/repositories/role_repository.py:89-117.

app/repositories/facilitator_unscoped_repository.py (or fold into the file above)¶

• Class FacilitatorUnscopedRepository(BaseUnscopedRepository), model = Facilitator.
• For the cross-tenant lookup at patient registration: get_by_id_any_tenant(facilitator_id) -> Facilitator | None. Mirrors ProviderRepository. Active+not-deleted filter applied.
• For Clerk webhook: get_by_email_any_tenant(email_lower).
• Logged in audit_log with actor_type='system' per the BaseUnscopedRepository docstring (app/repositories/base.py:189-198).

6.2 Services (new files)¶

app/services/facilitator_service.py¶

• Pure orchestration over the repository. No DB queries directly.
• Functions:
• create_facilitator(db, data, actor_id) -> Facilitator — validates uniqueness, persists, audits.
• get_facilitator(db, facilitator_id, tenant_id) -> Facilitator | None
• list_facilitators(db, tenant_id, q, is_active, page, page_size)
• update_facilitator(db, facilitator_id, data, tenant_id, actor_id) -> Facilitator
• soft_delete_facilitator(db, facilitator_id, tenant_id, actor_id, force=False) — applies the active-cases guard.
• attach_clerk_user_id_via_webhook(db, email_lower, clerk_user_id) -> Facilitator | None
• get_sourced_cases(db, facilitator_id, page, page_size) — uses BaseUnscopedRepository for the cases query (cross-tenant read).
• Errors: FacilitatorNotFoundError, FacilitatorDuplicateEmailError, FacilitatorHasAttributedRecordsError — raised, mapped to 4xx in router.

6.3 Schemas (new file)¶

app/schemas/facilitator_admin.py¶

• FacilitatorCreate(BaseModel) — see §5.1.
• FacilitatorUpdate(BaseModel) — partial update, all fields Optional.
• FacilitatorRead(BaseModel) — full read shape (excludes is_deleted from the wire).
• SourcedCaseItem(BaseModel) — see §5.2.
• Distinct file from existing app/schemas/facilitator.py (which holds the consent schemas) — no cross-import. Keeps the domain boundary clean per CLAUDE.md "Domain Boundaries."

6.4 Routers (new file)¶

app/routers/admin_facilitators.py¶

• Mirrors app/routers/admin_tenants.py:1-80 shape: regex path validation, super_admin/platform_admin perms via @require_permission, APIResponse envelope, structured error mapping.
• Routes: GET /api/v1/admin/facilitators, POST …, PATCH /admin/facilitators/{id}, DELETE /admin/facilitators/{id}.

Extend app/routers/facilitator_portal.py¶

• Add GET /api/v1/facilitator/sourced-cases route, gated by facilitator:sourced-cases:read.
• Reuses _check_facilitator_enabled(tenant_id) if SD wants the same FF gate; else gate behind a new facilitator_sourced_cases_enabled flag (DP-F, default OFF until §11 §11.4 cutover).

6.5 Model file¶

app/models/facilitator.py (new)¶

• Class Facilitator(Base, UUIDPrimaryKeyMixin, TenantScopedMixin, TimestampMixin).
• All columns per §4.1.
• __repr__ returns <Facilitator id={...} email={...}>. Do NOT log raw email (per CLAUDE.md "No PII in logs"). Truncate or mask in logging.

6.6 No cross-domain imports¶

Per CLAUDE.md "Domain Boundaries": facilitator_service.get_sourced_cases reads cases via repository injection — not by importing case_service. The cases table is read with BaseUnscopedRepository because the read crosses tenants.

7. Alembic Migration Shape¶

7.1 Single migration vs multi-step¶

Recommendation: TWO migrations, sequenced. Single migration would be reversible but couples three otherwise-independent concerns; splitting matches the per-PR review surface and lowers downgrade risk.

Migration A (this spec, one PR): <rev>_add_facilitators_table_and_attribution_fks.py 1. CREATE TABLE IF NOT EXISTS facilitators (…) per §4.1. 2. Indexes (3 mandatory + 1 optional). 3. Insert one tenants row for tenant-curaway-facilitators (idempotent ON CONFLICT (id) DO NOTHING) — required so the tenant_id FK on facilitators can be satisfied. Mirrors 5d49162011a5_add_tenant_curaway_patients_row.py. 4. ALTER TABLE patients ADD COLUMN referred_by_facilitator_id VARCHAR(36) NULL REFERENCES facilitators(id) ON DELETE SET NULL; + index. 5. ALTER TABLE cases ADD COLUMN referred_by_facilitator_id VARCHAR(36) NULL REFERENCES facilitators(id) ON DELETE SET NULL; + index. 6. ALTER TABLE facilitators ENABLE ROW LEVEL SECURITY; + CREATE POLICY tenant_isolation … (single-scope). 7. GRANT SELECT, INSERT, UPDATE, DELETE ON facilitators TO curaway_app; (DO $$ guarded — see cab789eed1ca_add_phase0_rbac_and_case_shares.py:210-220). 8. Permission grant: _add_perm('facilitator', 'facilitator:sourced-cases:read'), _add_perm('platform_admin', 'facilitator_admin:manage'), _add_perm('super_admin', 'facilitator_admin:manage'). Same JSONB-append pattern as 824b672ec7a4_grant_portal_permissions_to_roles.py:116-127.

down_revision: most recent head (currently c8d3f2a91b4e per alembic/versions/c8d3f2a91b4e_enable_rls_on_llm_usage.py). Verify at PR time.

Migration B (D5/#618 spec): seeds the actual referral-link cookie reader. Out of scope here. Migration C (D4/#617 spec): commissions table referencing facilitators.id. Out of scope here.

7.2 Reversibility¶

downgrade() reverses in order: 1. _remove_perm for both new permission rows. 2. Revoke curaway_app privileges. 3. DROP POLICY IF EXISTS tenant_isolation ON facilitators; + ALTER TABLE … DISABLE ROW LEVEL SECURITY;. 4. ALTER TABLE cases DROP COLUMN IF EXISTS referred_by_facilitator_id; 5. ALTER TABLE patients DROP COLUMN IF EXISTS referred_by_facilitator_id; 6. DROP TABLE IF EXISTS facilitators; 7. DELETE FROM tenants WHERE id = 'tenant-curaway-facilitators'; (only if no rows on that tenant).

Mirrors cab789eed1ca_add_phase0_rbac_and_case_shares.py:248-273 shape.

7.3 Migration safety¶

• Adding a NULLable column to patients and cases is safe online (no rewrite, no lock escalation on Postgres ≥11). Verified pattern: e8f9a0b1c2d3_add_channel_preference_to_patients.py.
• Adding the FK with NOT VALID then VALIDATE CONSTRAINT is the zero-downtime variant if either table grows large; not required at 41 patients.
• tenant-curaway-facilitators row insert must run BEFORE facilitators table is created (Postgres FK checks fire at row-insert time when the constraint is non-deferred). Order strictly: tenants row → facilitators table → FK columns on patients/cases. All DDL + seeds run inside the single Alembic upgrade transaction — no op.execute("COMMIT") calls scattered through the migration; if any statement fails, the entire upgrade rolls back atomically.

8. Backfill Strategy¶

8.1 Default (DP-D recommended)¶

All 41 existing patient rows: referred_by_facilitator_id = NULL ("direct signup").

Rationale: - They signed up before facilitator infra existed — claiming any of them was facilitator-sourced is fabrication. - NULL is the schema default; no SQL needed beyond the DDL. - Aligns with how Phase 0 RBAC handled the legacy "no role assigned yet" state — leave NULL, let the new feature land cleanly. - Forward-compatible: admin can manually set referred_by_facilitator_id per patient via PATCH /api/v1/admin/patients/{id} (existing endpoint — verify) once facilitator records exist, with audit trail. No backfill SQL needed.

8.2 Alternative considered¶

Admin-driven retroactive tagging via a new POST /api/v1/admin/facilitators/{id}/attribute-existing-patients endpoint that takes a list of patient_ids. Useful only if SD has out-of-band knowledge that specific facilitators sent specific pre-attribution patients. Not recommended for v1; ship the simpler default and grow this only on demand.

8.3 Existing Patient.referral_source (free-text) — what happens?¶

Nothing. Leave as-is. It is informational legacy data; the column stays in the schema. Do NOT attempt to parse "google_search" / "doctor_referral" strings into facilitator FKs — no facilitator entity exists to map them to, and we do not want a brittle string-matching layer. The two columns coexist:

• referral_source: str | None — free-text marketing source ("google", "doctor_referral", "instagram_ad").
• referred_by_facilitator_id: str | None FK — structured facilitator entity (this spec).

Both are nullable. Both can be set independently. A patient can have both (source="instagram_ad", referred_by="<facilitator-id>") — meaning "they came through an Instagram ad placed by this facilitator."

8.4 Cases backfill¶

• All 41 patients' existing cases: Case.referred_by_facilitator_id = NULL per the DP-D rule (NULL on patient → NULL on case).
• No SQL needed; the column defaults NULL.

8.5 Validation post-migration¶

-- Should be exactly 0 attributed patients:
SELECT COUNT(*) FROM patients WHERE referred_by_facilitator_id IS NOT NULL;
-- Should be exactly 0 attributed cases:
SELECT COUNT(*) FROM cases WHERE referred_by_facilitator_id IS NOT NULL;
-- Should be exactly 0 facilitator rows (until first POST /admin/facilitators):
SELECT COUNT(*) FROM facilitators;

9. Decision Points¶

9.1 SD's 4 explicit DPs¶

9.2 Additional DPs surfaced during this audit¶

9.3 Decision Points consolidated count¶

12 decisions documented (DP-A → DP-L), exceeding the "DP-A through DP-D + at least 4 more" requirement.

Note on DP-E and DP-J: these are discipline confirmations, not genuinely contestable choices — DP-E ("enable RLS like every other tenant-scoped table") is dictated by ADR-0018's defense-in-depth posture, and DP-J ("keep tenant_id even with one facilitator tenant") is dictated by the modular-monolith no-special-case rule. Kept in the table for traceability so the next reviewer doesn't ask "did anyone consider…?" The genuinely contestable DPs are DP-A, DP-B, DP-C, DP-D, DP-F, DP-G, DP-H, DP-I, DP-K, DP-L (10/12).

10. Edge Cases (15+ entries — mandatory)¶

27 edge cases documented; 15+ requirement met.

11. Validation Plan¶

11.1 Unit tests (pytest, tests/unit/)¶

• tests/unit/repositories/test_facilitator_repository.py
• get_by_id (happy + tenant-mismatch returns None)
• get_by_email_lower (case-insensitive)
• create with audit emission (assert AuditLog row added)
• update with before/after audit payload
• soft_delete sets is_deleted+is_active correctly
• attach_clerk_user_id rejects overwrite
• tests/unit/services/test_facilitator_service.py
• create rejects duplicate email (409 mapping)
• delete with attributed records + no force → raises FacilitatorHasAttributedRecordsError
• delete with attributed + admin:force → soft-deletes
• tests/unit/schemas/test_facilitator_admin_schemas.py
• commission_pct out of [0,1] → ValidationError
• currency_code regex
• email RFC 5322

11.2 Integration tests (pytest, tests/integration/)¶

• tests/integration/routers/test_admin_facilitators.py
• super_admin can POST/PATCH/GET/DELETE
• platform_admin can POST/PATCH/GET/DELETE
• facilitator (the role) gets 403 on /admin/facilitators
• patient role gets 403
• PATCH idempotency via X-Idempotency-Key
• X-Tenant-ID validation (must be tenant-curaway-facilitators for non-super-admins)
• tests/integration/routers/test_facilitator_sourced_cases.py
• facilitator with 0 cases → empty list
• facilitator with 3 cases → 3 items, ordered DESC by Case.created_at
• facilitator without clerk_user_id resolution → empty list (EC-8)
• non-facilitator role on the endpoint → 403
• feature flag OFF → 403 FEATURE_DISABLED
• pagination (page=2, page_size=2)
• tests/integration/test_patient_register_with_facilitator_attribution.py
• register with valid referred_by_facilitator_id → patient has FK
• register with non-existent UUID → 422
• register with soft-deleted facilitator → 422 (EC-1)
• register with cross-tenant facilitator → 422 (EC-15)
• tests/integration/test_case_creation_propagates_attribution.py
• create case for attributed patient → Case.referred_by_facilitator_id matches
• mutate Patient.referred_by_facilitator_id then create new case → new value (DP-L)
• mutate after old case exists → old case unchanged (DP-C)

11.3 Migration tests¶

• tests/migrations/test_facilitators_migration.py (new file in this PR's scope)
• upgrade on fresh DB: assert table exists, RLS enabled, perms granted
• upgrade is idempotent (run twice, no error)
• downgrade on populated DB: assert tables/columns/perms removed
• assert tenant-curaway-facilitators row inserted
• assert 41 patients still have NULL referred_by_facilitator_id post-upgrade

11.4 Permission grant coverage¶

• tests/test_permission_grant_coverage.py (existing, per 824b672ec7a4_…:67-70) — extend its scanner to assert facilitator_admin:manage and facilitator:sourced-cases:read are each granted to ≥1 role.
• tests/test_route_access_scanner.py — verify GET /admin/facilitators* and GET /facilitator/sourced-cases are gated by @require_permission (no exemption needed; no {case_id} path).

11.5 Zero-data-leakage validation on the existing 41 patients¶

Run as the cutover gate:

-- 1. Migration applied without disturbing existing rows:
SELECT COUNT(*) AS attributed_patients
  FROM patients
  WHERE referred_by_facilitator_id IS NOT NULL;
-- Expected: 0

-- 2. Migration applied without disturbing existing cases:
SELECT COUNT(*) AS attributed_cases
  FROM cases
  WHERE referred_by_facilitator_id IS NOT NULL;
-- Expected: 0

-- 3. The new tenant row exists exactly once:
SELECT COUNT(*) FROM tenants WHERE id = 'tenant-curaway-facilitators';
-- Expected: 1

-- 4. The new facilitators table is empty until first POST:
SELECT COUNT(*) FROM facilitators;
-- Expected: 0

-- 5. Permission grants applied:
SELECT code, permissions FROM roles
  WHERE code IN ('facilitator', 'platform_admin', 'super_admin');
-- Expected: facilitator includes "facilitator:sourced-cases:read";
--           platform_admin + super_admin include "facilitator_admin:manage"

-- 6. RLS active on facilitators:
SELECT relname, relrowsecurity FROM pg_class WHERE relname = 'facilitators';
-- Expected: relrowsecurity = TRUE

Run on staging first, then prod. Capture output to deploy log.

11.6 Code-review subagent gates (per .claude/rules/definition-of-done.md)¶

• /code-review — spec compliance + code quality (Tier 3 mandatory)
• compliance-reviewer.md — confirm GDPR cascade hook updated for facilitators table
• architecture-reviewer.md — Tier 3 feature
• platform-integrity-checker.md — multi-tenancy + auth verification

11.7 Manual smoke test (cutover step)¶

1. Apply migration on staging.
2. POST /api/v1/admin/facilitators with SD's gmail → expect 201, row visible in /admin/facilitators GET.
3. PATCH /api/v1/admin/facilitators/{id} setting commission_pct=0.15 → expect 200, audit row emitted.
4. Register a fresh test patient (tenant-curaway-patients) with referred_by_facilitator_id=<the new id> → expect 201, patient row has FK populated.
5. Trigger case creation for that patient → expect new Case.referred_by_facilitator_id populated.
6. Sign in as SD on facilitator.curaway.ai (post-Clerk-org provisioning per portal spec) → call GET /facilitator/sourced-cases → expect 1 item.
7. DELETE /api/v1/admin/facilitators/{id} without admin:force → expect 409 (because patient/case attached).
8. DELETE with super_admin (admin:force in scope) → expect 200, is_deleted=true. Patient/Case FKs preserved (soft-delete doesn't fire ON DELETE SET NULL).

12. Implementation Tier Breakdown (Opus / Sonnet / Haiku)¶

Per .claude/rules/definition-of-done.md.

12.1 Opus¶

• This spec itself (architecture, schema, decisions).
• ADR-0018 amendment (~10 lines clarifying FacilitatorPartnership was always implicit; this spec formalizes the schema).
• Permission grant migration design (security boundary).
• Cross-tenant FK + RLS interaction review (EC-15, EC-26).
• Code review on every Tier-3 PR.

12.2 Sonnet¶

• app/models/facilitator.py (new model)
• app/repositories/facilitator_repository.py + unscoped variant (mechanical from spec)
• app/services/facilitator_service.py (orchestration)
• app/schemas/facilitator_admin.py (Pydantic)
• app/routers/admin_facilitators.py (mirrors admin_tenants.py)
• Extension to app/routers/facilitator_portal.py for GET /sourced-cases
• Extension to app/routers/patients.py registration (add optional field)
• Extension to app/services/patient_service.py (validation + propagation to Case)
• Pytest units + integration tests per §11
• Alembic migration A (single file, ~150 lines)

12.3 Sonnet or Haiku¶

• config/rbac_defaults.yaml — add 2 perm strings to platform_admin + facilitator + super_admin
• config/feature_flags.yaml — add facilitator_sourced_cases_enabled
• Run scripts/sync_flagsmith.py --dry-run
• docs/CHANGELOG.md API changelog entry
• docs/api/error-codes.md — add FACILITATOR_* error codes
• Update docs/runbook/erasure.md GDPR cascade to include facilitators
• README touch-ups in app/repositories/ if a domain index exists

12.4 Recommended PR sequencing¶

1. PR 1 (Opus): this spec merged + DPs resolved (DP-A through DP-L).
2. PR 2 (Sonnet): model + migration + unscoped+scoped repos + unit tests. Migration runs in staging.
3. PR 3 (Sonnet): service + admin router + admin integration tests. Permission grants live.
4. PR 4 (Sonnet): patient registration extension + Case propagation + integration tests. Backwards compatible (field optional).
5. PR 5 (Sonnet): facilitator-portal /sourced-cases endpoint + integration tests. FF gated.
6. PR 6 (Haiku): docs sync, error codes, GDPR runbook update.

Each PR is independently revertable — no PR depends on a later PR for correctness.

13. Migration / Rollout (zero-downtime)¶

13.1 Pre-cutover (no UI exposure)¶

1. PR 1 (this spec) — merged. SD signs DPs.
2. PR 2 — migration applied on staging via alembic upgrade head with DATABASE_URL_ADMIN. Run §11.5 SQL checks.
3. PR 2 — migration applied on prod during a low-traffic window. NULL-able columns + IF NOT EXISTS make this online-safe at 41 patients. Verify §11.5 SQL on prod.
4. PR 3-4 — admin endpoints live; SD POSTs first facilitator (themselves) for end-to-end smoke. No frontend exposure yet.
5. PR 5 — /sourced-cases endpoint live; FF facilitator_sourced_cases_enabled defaults OFF for tenant-curaway-facilitators. Existing endpoints continue to work unchanged.

13.2 No breaking changes to existing endpoints¶

• POST /api/v1/patients/register — adds OPTIONAL field; existing callers (patient-app) continue to omit it.
• GET /api/v1/facilitator/delegated-cases — completely unchanged.
• POST/GET/DELETE /api/v1/consent/facilitator/* — completely unchanged.
• All admin routers — no shape changes to existing endpoints.

13.3 First facilitator (validation user)¶

• SD invites their own email (srikanth.donthi@gmail.com per CLAUDE.md context) into the tenant-curaway-facilitators Clerk org as org:admin.
• Backend admin POSTs the corresponding facilitators row in advance (DP-A decoupled flow). Email matches.
• Clerk webhook fires on signup → clerk_user_id populated.
• SD signs into facilitator.curaway.ai → portal-spec PortalOrgGate passes → /cases empty → manually flip facilitator_sourced_cases_enabled for tenant-curaway-facilitators ON → register a test patient with referred_by_facilitator_id set to SD's facilitator id → refresh /sourced-cases → row appears.

13.4 Rollback plan¶

• Frontend break: doesn't apply — this spec is backend-only. Portal already ships separately.
• Backend break:
• Soft: flip facilitator_sourced_cases_enabled OFF → /sourced-cases returns 403 FEATURE_DISABLED. Admin endpoints stay live.
• Harder: revert the admin_facilitators router via deploy rollback. Migration stays applied (NULL columns are harmless).
• Hardest: alembic downgrade -1 if data corruption emerges. Reversible per §7.2. Caveat: if any patient has a non-NULL referred_by_facilitator_id at downgrade time, the column drop will lose that data — accept the risk or hand-export first.

13.5 GDPR Article 17 cascade update¶

• docs/runbook/erasure.md (verify exists) — append facilitators to the cascade plan. Hard-delete order:
• Reverse soft-delete (set is_deleted=true)
• Wait for retention window (per CLAUDE.md "Audit logs are append-only — facilitator audit rows MUST NOT be deleted, but the facilitator entity itself can be hard-erased after retention")
• Hard-delete the row → ON DELETE SET NULL cascades to Patient.referred_by_facilitator_id and Case.referred_by_facilitator_id.

Critical distinction: soft-delete (is_deleted=true) preserves the FK on patients/cases — historical commissions stay queryable. Only hard-delete fires ON DELETE SET NULL. This is intentional (EC-13 + EC-14): admins routinely soft-delete; only the GDPR pipeline ever hard-deletes. - Update the deletion-certificate template to include facilitator-attribution NULLification.

13.6 Observability¶

• Telegram alerts on:
• Clerk webhook failures attaching clerk_user_id (EC-6, EC-7, EC-27)
• FacilitatorHasAttributedRecordsError raised (potential admin error or compliance event)
• DB unique index violations during admin POST (rare, indicates UI race)
• Langfuse: not applicable (no LLM calls in this spec).
• Metabase: add a tile "Active facilitators by tenant" once data exists. Out of scope but trivial follow-up.
• Grafana: no new external dependencies; no health panel needed.

14. Out of Scope / Future Work¶

15. References¶

Backend models¶

• app/models/patient.py:1-92 — Patient model; line 80 = referral_source: str(100); lines 27-29 = external_auth_id
• app/models/case.py:1-124 — Case model; line 67 = patient_id FK string
• app/models/case_share.py:1-144 — actor_type='facilitator' (line 84), dual-tenant scope (lines 75-80)
• app/models/role.py:1-139 — RoleCode enum; line 35 = FACILITATOR = "facilitator"
• app/models/tenant.py:1-113 — Tenant + TenantOrgMapping + TenantSettings
• app/models/audit.py:1-65 — AuditLog (append-only)
• app/models/base.py:1-72 — TenantScopedMixin, UUIDPrimaryKeyMixin, TimestampMixin, FlexibleJSON

Repositories¶

• app/repositories/base.py:38-186 — BaseRepository (_scoped_query, _audit, _active_only)
• app/repositories/base.py:189-237 — BaseUnscopedRepository
• app/repositories/role_repository.py:19-37 — RoleRepository(BaseUnscopedRepository) reference
• app/repositories/role_repository.py:71-117 — assign_role audit emission pattern
• app/repositories/patient_repository.py:1-90 — Patient repo CRUD shape

Services¶

• app/services/patient_service.py:30-75 — register_patient — extension point for new field
• app/services/facilitator_consent_service.py:211-245 — get_delegated_cases (post-#614 fix (closes issue #614); target_tenant_id filter)

Routers¶

• app/routers/patients.py:58-100 — POST /patients/register extension point
• app/routers/facilitator_portal.py:148-181 — existing delegated-cases endpoint to add sourced-cases next to
• app/routers/admin_tenants.py:1-80 — admin CRUD shape to mirror

Middleware¶

• app/middleware/require_permission.py:31-100 — perm decorator
• app/middleware/rbac_middleware.py:59-77 — _ORG_TENANT_MAP_FALLBACK; tenant-curaway-facilitators not yet present

Migrations¶

• alembic/versions/cab789eed1ca_add_phase0_rbac_and_case_shares.py:96-273 — full migration shape (CREATE, RLS, GRANT, seed, downgrade)
• alembic/versions/824b672ec7a4_grant_portal_permissions_to_roles.py:88-152 — JSONB perm-append idempotent shape
• alembic/versions/5d49162011a5_add_tenant_curaway_patients_row.py — tenant-row insert pattern
• alembic/versions/c8d3f2a91b4e_enable_rls_on_llm_usage.py — current head (verify on PR)

Config¶

• config/rbac_defaults.yaml:17-23 — facilitator role definition
• config/feature_flags.yaml:269-272 — facilitator_consent_enabled
• config/feature_flags.yaml:298-302 — ratings/CSAT flag

ADRs¶

• docs/adr/0018-multi-tenancy-platform-architecture.md:11 — 7-actor model
• docs/adr/0018-multi-tenancy-platform-architecture.md:20 — facilitator row "1 shared, row-level by facilitator_id"
• docs/adr/0018-multi-tenancy-platform-architecture.md:34 — "Reference (live, patient-scoped) … Consent-gated"
• docs/adr/0018-multi-tenancy-platform-architecture.md:42 — facilitators vs coordinators distinction
• docs/adr/0018-multi-tenancy-platform-architecture.md:249-256 — hospital_recovery_partnerships.commission_pct (NOT FacilitatorPartnership)
• docs/adr/0018-multi-tenancy-platform-architecture.md:454 — facilitator portal Phase 3

Specs¶

• docs/specs/facilitator-portal-frontend-feature.md §5.4b — original audit (this spec formalizes the fix)
• docs/specs/facilitator-portal-frontend-feature.md §10 D13 — decision-point this spec implements
• docs/specs/multi-tenancy-phase3-coordinators-facilitators-impl.md:48-51 — Phase 4 deferral context
• docs/specs/per-provider-tenants-feature.md:1-90 — pre-flight ground-truth pattern this spec mirrors

Project context¶

• CLAUDE.md — "All money in USD cents", "Multi-Tenancy + Per-Resource Authorization from Day 1", "Feature Flags Everywhere", "No PII in logs"
• .claude/rules/coding-principles.md — simplicity, surgical changes
• .claude/rules/backend-services.md — DAO pattern, multi-tenancy, no cross-domain imports
• .claude/rules/definition-of-done.md — Tier 3 feature checklist

Last updated: 2026-05-04.