--- status: shipped --- # Database-backed auth for all three dashboards > **Status: shipped.** PRs 1 to 11 of § 3 are built: both dashboards authorize from > their membership tables, which own every role, and CFX operators author role sets and roles in > admin-dashboard (§ 2.4). The shared activity feed (PR 9) is #3983. It implements the database-side phases > of [COGNITO_AUTH_MIGRATION.md](COGNITO_AUTH_MIGRATION.md) ahead of Cognito (§ 5). > > **Since 2026-10-02 Cognito is the identity provider on all three dashboards and PropelAuth is > out of the code**: its client, webhook sync and backfills are removed, and invites are rows > accepted at Cognito sign-in. Where this document says PropelAuth authenticates, feeds a sync or > receives an invite, it describes the state before that. The `propelauth_*` columns named below > are no longer read and are pending a drop. Current status is in > [COGNITO_AUTH_MIGRATION.md](COGNITO_AUTH_MIGRATION.md)'s phase status lines. > > **Since 2026-10-03 a workspace is its own row** > ([DASHBOARD_WORKSPACE_TABLES.md](DASHBOARD_WORKSPACE_TABLES.md)), which reverses two decisions > below. § 2.1 says there is deliberately no workspace table: there are now > `b2b.customer_dashboard_workspace` and `bank.bank_dashboard_workspace`, holding the role set > and, for a bank, its dashboard domain, theme and an `enabled` flag, with `workspace_id` on the > tenant. Every membership, invite, SSO connection and activity row names only its workspace: > the `customer_id` / `bank_id` columns the tables below list are dropped. And the bank > dashboard no longer takes its bank from the request's host: the session acts in one active > bank chosen from the caller's memberships, and a bank's own host can only narrow to that bank. ## Summary The customer, bank and admin dashboards each get their own auth tables in their own pg schema (`b2b` for customer, reusing the tables its PropelAuth mirror already fills; a new `bank`; and `admin`) and their own services over them. The tables are hand-written per schema and the services are written per dashboard: nothing is generic over a table set, and nothing branches on which dashboard is calling. One invariant governs the design: > **The identity provider authenticates the session and nothing else.** Users, workspaces, > membership, roles, permissions, invites and activity are rows in our database, and every > authorization decision is a join over them. When this was written PropelAuth (customer, bank) kept authenticating, and admin had moved from Cloudflare Access to Cognito at [COGNITO_AUTH_MIGRATION.md](COGNITO_AUTH_MIGRATION.md) Phase 5. The plan was for Cognito to replace only the authentication and the sync source, which it now has on both dashboards. ## 1. Today ### 1.1 What exists per dashboard | | Customer | Bank | Admin | | ------------------ | -------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------- | | Authentication | PropelAuth session | PropelAuth session | Cloudflare Access JWT, verified in `proxy.ts` ([AUTH.md](../AUTH.md)) | | Workspace | PropelAuth org, linked by `b2b.customer.propelauth_workspace_id` | PropelAuth org carrying `bank-dashboard:r`; the bank itself comes from `-bank:r` permissions ∩ host | none (single tenant) | | User rows | `b2b.customer_dashboard_user`, a mirror keyed on `propelauth_id` | none | `admin.dashboard_user`, keyed on `cf_access_sub` | | Membership, role | PropelAuth per-org role; `b2b.customer_dashboard_membership` holds only email preferences per (customer, user) | PropelAuth per-org role | `admin.dashboard_user.role_id`, one role per user | | Roles, permissions | PropelAuth console, one role mapping per org (§ 2.3); `AuthPermission` union of 56 keys in `lib/auth.ts` | PropelAuth console, one role mapping per bank org; `AuthPermission` union of 12 keys | `admin.dashboard_role`, `dashboard_permission`, `dashboard_role_permission`, seeded by migrations | | Invites | PropelAuth | PropelAuth | none | | Activity | `b2b.customer_dashboard_user_activity`; `settings/company-security/_components/UserActivityTable.tsx` | none | `admin.dashboard_user_activity`; `app/(private)/_components/ActivityHistory.tsx` | | Services | `CustomerDashboardUser{,Admin,Activity}Service` in `customer-dashboard/src/lib`; `dashboard-auth-services` | `dashboard-auth-services` | `AdminDashboard{User,UserActivity,Role,Permission}Service` in `admin-dashboard-services` | | Sync | PropelAuth → Hookdeck (`customer-dashboard/pulumi/hookdeck.ts`) → `app/api/propelauth-events/route.ts` | none of its own | first-request upsert in `AdminDashboardUserService.upsert` | ### 1.2 What the split costs | Symptom | Where | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | | Every member search pages the whole org out of PropelAuth into memory, then filters, sorts and slices | `DashboardWorkspaceMemberService.ts:118`, deleted in PR 8 | | "Who may assign which role" is PropelAuth's `inheritedRolesPlusCurrentRole`, fetched from PropelAuth on every service call by `dashboardUserId` and `workspaceId`; page gates still read the session's token claims, the budget [AUTH.md](../AUTH.md) says was outgrown | `resolveWorkspaceMembership` in `dashboard-auth-services/src/internal.ts`, deleted in PR 8; `lib/auth.ts` in each dashboard | | A redelivered `user.created` gets `DUPLICATE`, the route throws and returns 500, and Hookdeck makes up to five attempts an hour apart; any activity rows after the throw are never written | `propelauth-events/route.ts:100`, `:80`; `DEFAULT_RETRY_RULE` in `pulumi-templates/src/hookdeck/rules.ts` | | `createOne` receives a transaction but inserts with `this.db`, so neither `create` nor `createMultiple` is atomic, whatever the doc comment on `createMultiple` says | `CustomerDashboardUserActivityService.ts:223` | | `AdminDashboardUserActivityService.create` takes no transaction, so domain services bypass it with `tx.insert(adminDashboardUserActivityTable)` inside their own | `TokenConfigAdminService`, `ChainConfigAdminService`, `BackofficeWalletConfigAdminService`, `MaintenanceAdminService`, and the admin user and role services | | Three gate implementations export the same names over two permission sources | `lib/auth.ts` in each dashboard | ## 2. Proposal ### 2.1 One schema per dashboard, hand-written tables | pg schema | Dashboard | Workspace is | Tables | | ------------ | --------- | --------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `b2b` | customer | a customer (`b2b.customer`) | the existing `customer_dashboard_user`, `customer_dashboard_membership` and `customer_dashboard_user_activity`, extended; a new `customer_dashboard_invite`; the RBAC catalog | | `bank` (new) | bank | a bank (`b2b.bank`) | new `dashboard_*` tables | | `admin` | admin | none: single tenant | `dashboard_user`, `dashboard_role`, `dashboard_permission`, `dashboard_role_permission`, `dashboard_user_activity`, unchanged | **Copies, not a factory.** Each schema's tables are hand-written Drizzle files, one per table, named for it: `packages/db/src/drizzle/schema/customer_dashboard_*.ts` for `b2b`, `bank_dashboard_*.ts` for `bank`, `admin/` for admin. There is no shared table type and no service generic over a table set; a service imports its own schema's tables. The four RBAC tables began as a factory, `dashboardAuthTables(schema)`, which the customer PR replaced with copies whose generated DDL is byte-identical, so no migration came with it. Exports follow admin's naming (`customerDashboardRoleTable`, `bankDashboardRoleTable`, `adminDashboardRoleTable`). `b2b` and `bank` table names carry their dashboard's prefix (`customer_dashboard_*`, `bank_dashboard_*`), constraints and indexes with them; admin's `dashboard_*` names stay put. Every table calls `.enableRLS()`, like every table today. **The customer dashboard reuses `b2b`'s tables.** The PropelAuth consumer already fills a user row per PropelAuth user and a row per customer and user, so the customer set adds only what those lack. The migration renames the RBAC catalog and the membership table to carry the prefix, retypes and drops nothing, and every added column is nullable or defaulted, so the PropelAuth-era inserts keep working unchanged. The membership was `dashboard_customer_user`; a view under that name, over its original columns, keeps pre-rename code reading and writing it until the dashboard cutover drops it. No deployed code reads the catalog tables, so they need no view. | `b2b` table | Added | Holds | | --------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------- | | `customer_dashboard_user` | nothing | the user, keyed by `propelauth_id`; `data` holds email and first/last name | | `customer` | `dashboard_role_set_id`, nullable | the customer's role set, `Default` from onboarding (PR 10). No workspace table then; one holds it today (DASHBOARD_WORKSPACE_TABLES.md) | | `customer_dashboard_membership` | `role_id`, `status` (default `ACTIVE`), `role_assigned_by_user_id`, `role_assigned_at`, `deleted_at` | the membership, one row per `(customer_id, user_id)`; `data` keeps the member's email preferences | | `customer_dashboard_user_activity` | nothing | the activity feed (§ 2.6) | | `customer_dashboard_invite` (new) | `customer_id`, `email`, `role_id`, `invited_by_user_id`, `propelauth_invite_id`, `status`, `deleted_at`; one live PENDING row per `(customer_id, lower(email))` | PropelAuth pending invites | | `customer_dashboard_{role_set,role,permission,role_permission}` | the RBAC catalog, below | PropelAuth role mappings, roles and permission config | The bank set is new tables in `bank`, plus two columns on `b2b.bank`. As built there was no `dashboard_workspace` table: a bank's own workspace identity was `b2b.bank` itself, the way a customer's was `b2b.customer`. Both have a workspace table now ([DASHBOARD_WORKSPACE_TABLES.md](DASHBOARD_WORKSPACE_TABLES.md)). | Table | Columns (beyond the integer key and timestamps) | Replaces | | ----------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- | | `dashboard_user` | `propelauth_id` (unique), `data` (email, first/last name) — like `b2b.customer_dashboard_user` | nothing: the bank dashboard keeps no user rows today | | `dashboard_membership` | `bank_id` (→ `b2b.bank`), `user_id`, `role_id`, `status`, `role_assigned_by_user_id`, `role_assigned_at`, `deleted_at`; one live row per `(bank_id, user_id)` | PropelAuth's per-org role | | `dashboard_invite` | `bank_id`, `email`, `role_id`, `invited_by_user_id`, `propelauth_invite_id`, `status`, `deleted_at`; one live PENDING row per `(bank_id, lower(email))` | PropelAuth pending invites | | `dashboard_user_activity` | `user_id`, `bank_id`, `visibility`, `type` (the 14 `user.*` events), `object_id`, `data`, `metadata` | nothing: the bank dashboard has no feed today | | `bank_dashboard_{role_set,role,permission,role_permission}` | from PR 2, renamed | PropelAuth role mappings, roles and permission config | | `b2b.bank` column | Added | Holds | | ----------------------- | ------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------- | | `propelauth_org_id` | nullable, unique | the bank's own PropelAuth org: joining it grants the bank's membership, and PropelAuth writes (removal, invite) target it | | `dashboard_role_set_id` | nullable, → `bank.bank_dashboard_role_set` | the bank's role set — the cross-schema counterpart of `b2b.customer.dashboard_role_set_id` | Until PR 10 the sync resolved a bank's own org and role set lazily, by matching the org's name against `b2b.bank.name`. Both are operator-owned rows now; nothing derives them. **The user's own id is its key.** A `b2b` service names a user by `customer_dashboard_user.external_id` (`cdu_…`; bank `bdu_…`), so a member is addressed by customer and user id, and integer keys never leave a service. `propelauth_id` and `cognito_sub` are nullable provider links beside it. Memberships and invites are soft-deleted (`status = 'DELETED'` with `deleted_at`), so a removed member or revoked invite stays readable; a membership row is one per customer and user, so a returning member revives theirs. `cf_access_sub` sits on `admin.dashboard_user`, so the Cognito switch adds a `cognito_sub` column beside each provider column for its dual-accept window and re-keys nothing. There is no identity table. **Renames behind a view.** Nothing the running apps read changes shape: `b2b` gains columns and one table, the one renamed table they read keeps its old name as a view, and `bank`'s tables are new, so every PR deploys on its own. ### 2.2 The new `bank` schema Migration-created objects land ungranted ([rules/database.md](../../rules/database.md)): dbmate runs as `cfx_ci`, and default privileges are keyed on `cfx_owner`. The schema follows `admin`'s path exactly: - `packages/aurora/pulumi/index.ts` declares a `postgresql.Schema` named `bank`, owned by `cfx_owner`, beside `adminSchema`. It must apply before the first migration that touches `bank`, or dbmate creates the schema as `cfx_ci`. - `createAdminGroupRoles` generalizes to `createDashboardSchemaGroupRoles`, one call per schema, yielding `cfx_bank_read_write` and `cfx_bank_read_only`: `USAGE` on `bank`, `b2b`, `extensions`, DML on `bank`, and default privileges for `cfx_owner`. Postgres grants no `USAGE` on a new schema to `PUBLIC`, so every other workload stays out. - In `workloadIdentities.ts`, `bank-dashboard-rw` and `bank-dashboard-ro` add the bank group to `inRoles` and take `bank,b2b,extensions` as their search path, as `admin-dashboard-rw` does with `admin,b2b,extensions`. Today `bank_dashboard_rw` can write only `b2b.bank.theme` and `updated_at`. - The migration carries `CREATE SCHEMA IF NOT EXISTS bank` for local and test containers, and ends with the guarded `ALTER TABLE … OWNER TO cfx_owner` for each table. ### 2.3 Permission model Admin's model, applied per schema: | Concern | Admin today | All three dashboards | | --------------------- | ------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------- | | Catalog | `admin.dashboard_permission`, seeded by migration (`20260911212046_positive_pay_permissions.sql`) | the same, per schema | | Role sets | none | one per PropelAuth role mapping; a customer or bank workspace subscribes to one, and roles are named within a set | | Role → permissions | `dashboard_role_permission`; system roles seeded once, UI-mutable after | the same | | Assignment | `dashboard_user.role_id` | the membership's `role_id` (`customer_dashboard_membership` in `b2b`), from the workspace's role set | | Who may assign a role | `admin/users:w` | the schema's assign permission (`team/role:w` for customer and bank) and `target.rank <= actor.rank` | | Hidden role | none | `is_internal`: the role and its members are invisible to actors without an internal role | | Owner | `owner` short-circuits to every key in `lib/auth.ts`; `LAST_OWNER` guard | short-circuit stays admin-only; the guard runs per workspace on the top-rank role | | Gates | `mustHaveAuthPermissions`, ``, `serverAction({ permissions })` | same names in each dashboard, each reading its own `*DashboardMembershipService.searchOwn` | `rank` orders assignment and nothing else: a role inherits no permissions from a lower one, so [AUTH.md](../AUTH.md)'s out-of-scope "role hierarchy / inheritance" still holds. **CFX staff hold the internal role, and only they do.** A user whose email PropelAuth has confirmed and whose domain is exactly `cfx.to` or `cfxlabs.com` (`isCfxStaffEmail` in `dashboard-auth-services`) holds their workspace role set's `is_internal` role, CFX Admin, whatever else they were assigned; nobody else may hold it, and an invite to it for any other address is refused. A role set with no internal role gives CFX staff no role at all rather than a guessed one. What the current unions become: | Today | Becomes | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- | | Customer's 56 `AuthPermission` keys, configured in PropelAuth | rows in `b2b.customer_dashboard_permission`; the union stays a TS subset with admin's drift warning | | `propelauth::can_view_other_members` / `can_invite` / `can_change_roles` / `can_remove_users` aliased to `team:*` in `permissions.ts` | ordinary `team:*` rows; nothing reads PropelAuth's built-ins since PR 10 deleted `resolveDashboardPermissions` | | Bank `bank-dashboard:r` | membership in any `bank` workspace; being in the schema is the grant | | Bank `dart-bank:r`, `fib-bank:r`, `burling-bank:r` and `BANK_PERMISSION_TO_IDS` | membership in that bank's workspace; `getAuthBankIds` is now the one active bank, from memberships | | Bank's other 8 keys | rows in `bank.bank_dashboard_permission` | | Keys PropelAuth grants that no union reads: `accounts:r`, `documents/{ach,rtp,wire}-instruction:r` (customer); `accounts/export:r`, `deposit/ach-debit:w`, `documents:r`, `documents:w` (bank) | not seeded; bank `documents:w` was added later as a new key (`20261010010200`) | #### What the seed transcribed The matrix exists only in the PropelAuth console, and PropelAuth's API returns a mapping's name and org count but not its roles. `20260913214001_dashboard_rbac_seed.sql` therefore reads it from member data: for every member of every org, the org's `customRoleMappingName`, the member's role, `userInheritedRolesPlusCurrentRole` and `userPermissions`. Read from prod on 2026-09-13. Customer and bank share one PropelAuth project per environment, and dev agrees with prod on every mapping and role both have members in. No member holds an additional role. | Schema | Role sets (prod orgs) | Seeded | | ------ | --------------------------------------------------------------------------------- | ------------------------------------ | | `b2b` | Default (115), Physical Cards Enabled (5), Tether (5), Cicada (1), Off Ramp (1) | 56 permissions, 28 roles, 883 grants | | `bank` | Burling Bank, CFX Bank Viewer, Dart Bank, First Independence Bank Viewer (1 each) | 8 permissions, 24 roles, 58 grants | - **The same role name grants different permissions per set.** Tether's Viewer lacks most of Default's; Cicada's Viewer may send US wires. Collapsing onto one set would change permissions for every member of the 12 non-Default customer orgs. - **Rank.** CFX Admin 60, Owner 50, Admin 40, Member 30, Viewer 20, Legal 10; CFX Admin is the one `is_internal` role. In PropelAuth, CFX Admin and Owner are siblings that cannot assign each other. With rank, CFX staff can assign Owners, which is the one assignment change. - **`workspace:w`** was created for bank Owners and CFX Admins and had no holders yet; the seed grants it to both in every bank set. - **Roles with no members** have no grants to read and seed with none, so they fail closed. Physical Cards Enabled Member is read from dev. These still need transcribing from the console before any cutover: Cicada Member and Legal; Off Ramp Admin, Viewer and Legal; Burling Bank Owner (it has `workspace:w`), Admin, Member, Viewer and Legal; CFX Bank Viewer Admin, Member, Viewer and Legal; Dart Bank and First Independence Bank Viewer, Viewer and Legal. - **Bank access is membership.** `bank-dashboard:r` and `-bank:r` are not seeded. Today a CFX Admin in the Dart or First Independence org also holds `burling-bank:r`, and the CFX Bank Viewer org's members hold two or three banks. Under membership, each of those becomes a membership in each bank's workspace, which the step-two backfill has to write. Until PR 10 the backfill proved the transcription: for every member it asserted that PropelAuth's `orgIdToOrgInfo[org].userPermissions`, restricted to the union, equalled the seeded set for their role in their workspace's role set. PR 10 dropped that assertion with every other read of PropelAuth's roles; each role set also names a `default_role_id`, `Member` in every seeded set, for a member who joins without an invite. ### 2.4 Services **One dashboard per service.** Each dashboard has its own services over its own schema's tables: `CustomerDashboard*Service` in `packages/dashboard-auth-services/src/customer/` (the `./customer` subpath), `BankDashboard*Service` in `src/bank/` (`./bank`), and admin's `AdminDashboard*Service` in `admin-dashboard-services`. Nothing branches on which dashboard is calling, and no constructor takes an argument only one dashboard passes. Code duplicated between the customer and bank copies is accepted; only true primitives with no per-dashboard behavior are shared. Each service takes `{ db }`, plus a required `propelAuthClient` where it writes to PropelAuth, and follows [SERVICE.md](../SERVICE.md): `get` is a one-row `search`, `search` returns `{ items, hasNext, total }`, `delete` is a soft delete, every foreign-key id carries its `Name` and every enum its `Description`. Operations a customer's own member performs are `*Service`s; cross-customer and system work is `*AdminService`. Customer-scoped methods name the caller as `{ dashboardUserId, customerId }` (the user's PropelAuth id and the customer's id; bank names its bank) and authorize inside the query by joining the caller's ACTIVE membership of that customer to `dashboard_role_permission`. **One service per concern:** no two services cover the same purpose over the same table. The keys that gate each method (`team:r`, `team/invite:w`, `team/role:w`, `team/remove:w`) are constants in the service. Write methods accept `tx?: B2bPgTransaction`. | Service (customer; bank mirrors each) | Methods | Replaces | | ----------------------------------------- | --------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `CustomerDashboardMembershipService` | `search`, `searchOwn`, `searchRoles`, `get`, `update`, `delete` | `DashboardWorkspaceMemberService`; the caller's own rename in `DashboardUserService`. `searchOwn` is the dashboard's per-request principal, the source of every gate's keys. `search` also returns `assignableRoles`, replacing PropelAuth's `inheritedRolesPlusCurrentRole`. `searchRoles` returns the caller's role set with each role's grants, for the team page's read-only Roles reference (`TeamRolesReference`, `packages/ui/src/components/team-roles.tsx`) | | `CustomerDashboardInviteService` | `search`, `get`, `create`, `delete` | `DashboardWorkspaceInviteService` | | `CustomerDashboardPermissionAdminService` | `search`, `get` | the b2b catalog, for operators | | `CustomerDashboardRoleSetAdminService` | `search`, `searchWorkspaces`, `get`, `create`, `update`, `delete`, `assign` | new: role sets were seeded by migration only. `searchWorkspaces` reads each customer's set straight from `customer.dashboard_role_set_id`, set-less customers included, with member and pending-invite counts. `create` with `copyFromRoleSetId` copies every role, grant and the default; `update` sets `defaultRoleId`, never the internal role; `delete` is a hard delete, refused (`ROLE_SET_IN_USE`) while a customer subscribes or any membership or invite, live or past, references one of its roles; `assign` switches a customer's set (below) | | `CustomerDashboardRoleAdminService` | `search`, `get`, `create`, `update`, `delete` | new. `permissionKeys` is a full replacement; an unknown key fails the write (`ROLE_UNKNOWN_PERMISSION`). The internal role is seeded and never created, deleted or reranked; a system role keeps its name; `delete` refuses the set's default and any role a membership or invite references (`ROLE_IN_USE`) | | `CustomerDashboardMembershipAdminService` | `search`, `searchUsers`, `update`, `delete`, `sync`, `disable`, `prune` | `CustomerDashboardUserAdminService`, folded in: the consumer's only membership write path (§ 2.5), and the backfill's prune. `update` and `delete` are an operator's role change and removal: no rank rule, but `LAST_OWNER` holds. `searchUsers` is the operators' cross-workspace user directory, each user with every membership | | `CustomerDashboardInviteAdminService` | `search`, `create`, `delete`, `close` | onboarding's inline owner-invite insert, and the backfill's inline invite close (§ 2.5); `search` and `delete` list and revoke invites across customers for operators | | `CustomerDashboardBackfillAdminService` | `run` | the one-off PropelAuth walk and the reconciliation check (§ 2.5) | | `CustomerDashboardUserActivityService` | `create`, `search` | its own `create` and `createMultiple` (§ 2.6) | | `CustomerDashboardUserService` | `get`, `search` | unchanged in purpose: users by id, including authors who have left, whom the roster no longer lists | | `DashboardUserSessionService` | `delete` | unchanged: ends every PropelAuth session for the user | | `DashboardWorkspaceService` | none | deleted: the workspace name is `customer.name` / `bank.name`, owned in our DB and changed by CFX admins, so neither dashboard renames a PropelAuth org | The rules every write enforces: an actor reaches only roles and members ranked at or below their own; an `is_internal` role and its members are invisible to an actor without one; the last ACTIVE holder of the workspace's highest-rank non-internal role cannot be demoted, disabled or removed (`LAST_OWNER`). A role change writes only the row: PropelAuth holds no dashboard role since PR 10. A removal calls PropelAuth first, since org presence is still PropelAuth's, then writes the row, so the row never records a removal the provider refused. A write that takes an owner off the top role runs inside one transaction: the holders are locked `FOR UPDATE` and re-checked before anything is written, so a concurrent demotion waits and sees the true count. Every writer that resolves a role against the workspace's set locks the workspace row (`b2b.customer` / `b2b.bank`) `FOR SHARE` first (`lockWorkspaces` and `roleInCurrentSetSql` in each `internal.ts`), and `*RoleSetAdminService.assign` locks it `FOR UPDATE`, so a role write and a set switch serialize: a write that picked its role before the switch waits, then finds the role gone. `*RoleAdminService` keeps a set's ranks sound: a custom role ranks from 1 up to, not including, the internal role, and no create or rerank may displace or tie the set's top non-internal role, so `LAST_OWNER` always guards the role owners actually hold. Admin keeps its own services; the admin PR changes only how activity is recorded (§ 2.6). `BankDashboard*Service` mirrors the table by name — `BankDashboardMembershipService`, `BankDashboardInviteService`, `BankDashboardPermissionAdminService`, `BankDashboardRoleSetAdminService`, `BankDashboardRoleAdminService`, `BankDashboardMembershipAdminService` (no `prune`: the bank backfill removes nothing), `BankDashboardInviteAdminService` (`search`, `create`, `delete`, `close`; its only `create` caller is admin-dashboard), `BankDashboardBackfillAdminService`, `BankDashboardUserActivityService` — minus `CustomerDashboardUserService` and `DashboardUserSessionService`, which the bank PR does not add: the bank dashboard has no legacy PropelAuth-era user service to fold, and no session-termination surface yet. `BankDashboardMembershipService.delete` removes a member from the bank's own org (`b2b.bank.propelauth_org_id`) in PropelAuth only when they are in it; a member reached through another org holds a cross-bank grant that exists only in our tables, so removing them writes the row alone. The `*MembershipAdminService` and `*InviteAdminService` classes serve callers with no dashboard user: `PropelAuthEventConsumer`, the backfills, onboarding's owner invite and CFX operators in admin-dashboard. The sync is `*MembershipAdminService.sync`, over the pure PropelAuth-to-membership rules in `src/membershipSync.ts`. The `*BackfillAdminService` classes of both dashboards live in `backoffice-api` (`src/controllers/propelauth/`), beside `PropelAuthEventConsumer`, and write only through `*MembershipAdminService` and `*InviteAdminService.close`. #### Operator authoring - **Authoring is admin-only.** CFX operators author role sets and roles in admin-dashboard. Workspace owners only assign existing roles on their Team page, which lists the set's roles and grants read-only through `searchRoles`. - **Permission keys stay migration-managed.** They are code-bound (`AUTH_PERMISSIONS` in each dashboard), so the UI edits grants and never the catalog. - **`assign` remaps by name and internal flag.** Moving a workspace to another set moves every live membership and PENDING invite to the role of the same name and `is_internal` in the new set, in one transaction. A held role with no counterpart refuses the move with `ROLE_SET_MISSING_ROLES`, naming the roles in `missingRoleNames`, and writes nothing; assigning the current set is a no-op. - **Surfaces**, gated by `dashboard-roles:r|w` and `dashboard-members:r|w` ([AUTH.md](../AUTH.md#customer-and-bank-dashboard-access)): | Route | What it does | | --------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- | | `/role-sets` | role sets per audience (customer, bank) | | `/role-sets/[audience]/[id]` | the permission × role matrix with a blast-radius confirm, the set's roles, who uses it, history | | `/role-sets/new`, `/role-sets/[audience]/[id]/edit` | create (blank or copied) and edit a set | | `/role-sets/[audience]/[id]/roles/new`, `…/roles/[roleId]/edit` | create and edit a role | | `/customers/[id]/team`, `/banks/[id]/team` | one workspace's members and invites, and switching its role set (`assign`) | | `/dashboard-users` | dashboard users across every workspace, with a side panel | - **Audit lives in admin.** The authoring services record no activity; admin-dashboard records each operator write in `admin.dashboard_user_activity` (`dashboardRoleSet.*`, `dashboardRole.*`, `dashboardMember.*`, `dashboardInvite.*`), because `customer_dashboard_user_activity` and `bank.dashboard_user_activity` require a dashboard-user actor. - **Infra.** `admin_dashboard_rw` inherits `cfx_bank_read_write` and searches `admin,b2b,bank,extensions` (`packages/aurora/pulumi/workloadIdentities.ts`). Removals and invites call PropelAuth, so admin-dashboard requires `PROPELAUTH_AUTH_URL` and `PROPELAUTH_API_KEY` from Doppler project `cfx-admin-dashboard`. ### 2.5 Sync while PropelAuth is the identity provider - **Our tables own every role; PropelAuth owns identity and org presence.** Since PR 10 nothing reads a PropelAuth role, permission or role mapping, and nothing writes a DB role name to PropelAuth. A role change writes only its row. An invite writes its row with the real role and asks PropelAuth to send the email with one fixed role, `Member`, which PropelAuth's default roles and every seeded role set both carry. A removal calls `removeUserFromOrg` first, then writes the row; a bank member reached through another org is removed from the row alone. If a row write fails after PropelAuth accepted a removal, the webhook repairs it; the backfill closes an invite PropelAuth no longer lists (no invite webhook exists). - **`*MembershipAdminService` is the consumer's only membership write path, for both dashboards.** `PropelAuthEventConsumer` in `backoffice-api` applies every event through `CustomerDashboardMembershipAdminService.sync` and, beside it, `BankDashboardMembershipAdminService.sync` (`disable` on a 404) — one webhook stream serves both, since a subject can belong to either or both. Each sync writes the same rows a dashboard-specific writer would and records the joins and removals it applied in its own transaction. The consumer then records the event's other activity rows from the syncs' reports, and never queries the tables itself. The customer sync runs first, then the bank sync, and both before any other activity, since every activity row references the user row its own sync writes. Either failing throws — a bank failure is never swallowed — so Hookdeck redelivers the event; both syncs converge, so a retry repeats nothing already applied. `backoffice_api_rw` inherits `cfx_bank_read_write` (`packages/aurora/pulumi/workloadIdentities.ts`) for this. - **The consumer applies state, not deltas.** Every event reads the user with `fetchUserWithOrgs`, which answers `null` only on a 404, and takes only the profile (email, names, `emailConfirmed`), `enabled` / `locked` and the ids of the orgs the user is in; it reads no org and no role. `sync` then upserts the user, merging the profile into `data` so other keys survive, and: - **Customer**: resolves each org to a customer through `customer.propelauth_workspace_id`. Joining a customer's org creates the membership, or revives a removed one, with the role of the PENDING invite for that customer and address, which it marks ACCEPTED, else the role set's `default_role_id`. The invite counts only once PropelAuth has confirmed the address; until then the joiner gets the default and the invite stays PENDING. A live membership that already holds a role keeps it. A live membership with no role never gets the default: only a confirmed address's PENDING invite fills it. Leaving the org soft-deletes the membership. - **Bank**: an org is a bank's own when it equals that bank's `propelauth_org_id`. Joining it creates or revives that bank's membership the same way. Leaving it soft-deletes that membership, on the `user.removed_from_org` event once the live read confirms the user is gone. A membership in a bank the user reaches any other way, such as a cross-bank viewer grant, exists only in our tables, and no snapshot removes it. Membership status follows the user's `enabled` and `locked` state for every membership the sync manages; a 404 disables every membership in both. A redelivered or out-of-order event converges on the same rows. - **Joins fail closed.** A customer or bank with no role set, or a role set with no default role, leaves the joined membership with no role, which grants nothing, and the consumer logs it at warn. - **Both syncs apply the CFX staff rule (§ 2.3) over the DB's role, and nothing else ever moves a held role.** A confirmed staff email holds the role set's CFX Admin, on a join and over a held role. Nobody else may hold an internal role, so an internal invite role, or a held CFX Admin once the email leaves the staff domains, stores no role and is reported as unresolved. - **Some writes never pass through our services.** Customer orgs are created with `enableAutoJoiningByDomain: true` (`customerOnboardingActivities.ts`), and the PropelAuth console adds and removes members directly. The webhook is the only path for those, which is why the consumer reads state; such a member joins on the default role. - **Activity.** Each sync records `user.addedToWorkspace` for a join, naming the role the DB gave, and `user.removedFromWorkspace`, with the member as its subject, for each membership it removed: a removal made in PropelAuth. Both land in the sync's transaction, so a replayed snapshot records nothing. A dashboard removal deletes its row before the webhook arrives and records itself, so it is not recorded twice. A PropelAuth role change records nothing. - **A backfill per dashboard.** `CustomerDashboardBackfillAdminService` (`packages/backoffice-api/scripts/backfillCustomerDashboardAuth.ts`) walks each customer row's org, and `BankDashboardBackfillAdminService` (`packages/backoffice-api/scripts/backfillBankDashboardAuth.ts`) each bank's `propelauth_org_id`. Both read the org's members and pending invites, run every member through the same sync, and close each PENDING invite PropelAuth no longer lists: ACCEPTED when the address is now a member, REVOKED otherwise. The customer backfill also soft-deletes memberships of users an org no longer lists. The bank backfill removes nothing, since a cross-bank grant looks the same as a departed member. Neither creates an invite or writes a role from PropelAuth. `--check` runs the identical write path inside a transaction that is always rolled back, so write and check can never drift from each other. A rerun writes nothing, so an interrupted run resumes by restarting. - **Drift.** `--check` is each backfill's reconciliation job: it reports exactly what a write run would change, so a check straight after a write finds nothing. `mfaEnabled` and `lastActiveAt` are carried by none of the events either consumer parses, so the team page keeps reading those two live from PropelAuth until Cognito; a `last_seen_at` column arrives with its writer, the cutover's `lib/auth.ts`. Gates fail closed: a customer-dashboard active org with no membership is `/forbidden`. The bank dashboard needs no PropelAuth active org: the host, the last bank and the chooser pick the bank among the caller's memberships. ### 2.6 Activity - **One service per dashboard, one shape.** `CustomerDashboardUserActivityService.create({ events, tx? })` writes every event into `customer_dashboard_user_activity` in one multi-row `INSERT`, on the caller's transaction when given one; it replaces `create` and `createMultiple`, whose inserts escaped their transaction. An event is a descriptor: the per-`type` payload, `userId`, `customerId`, `visibility` and the request metadata. `search` returns `{ items, hasNext, total }`, each item with the actor's `userName` from the user join and `typeDescription` / `visibilityDescription`. `AdminDashboardUserActivityService` takes the same shape in the admin PR, where domain services return descriptors instead of inserting into the activity table themselves. - **The customer feed stays where it is.** Nothing is copied. The notification core's lookup of who created an object reads `customer_dashboard_user_activity` inside `CustomerNotificationService.getRecipients`'s recipient query. That service and `CustomerDashboardUserNotificationPreferencesService` live in `dashboard-auth-services` (`src/customer/`), which already depends on `customer-services`. - **Metadata is already identical.** Every activity table carries the same `ipAddress`, `geoLocation` and `userAgent` jsonb shape. - **One feed component** in `@cfxlabsinc/ui` replaces `UserActivityTable` and `ActivityHistory` in a later PR, and gives the bank dashboard its first feed. Each dashboard passes a per-type renderer, since the `type` unions differ. ## 3. Migration and sequencing PRs 1 to 8, 10 and 11 are built. PR 1 had to apply before PR 2's migration, which raises on a cloud database that has no `bank` schema rather than creating one owned by `cfx_ci`. PRs 3 to 5 add tables and nullable or defaulted columns and change nothing the running apps read, so each deploys on its own: 4 stacks on 3, and 5 branches from main. The cutover PRs then move one dashboard's readers at a time, and cleanup deletes what they left unread. | # | PR | Contents | Verifies | | --- | ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 1 | `bank` schema and grants | Built. § 2.2's Pulumi changes in `cfx-db`; no migration | `cfx-db` preview shows no revoked memberships; on dev, `has_schema_privilege` for `bank_dashboard_rw` and `cfx_owner` rows in `pg_default_acl` for `bank`. Proved from the grants, never by attempting a write | | 2 | RBAC catalog | Built. Role sets, roles, permissions and grants for `b2b` and `bank` (then from a factory); the seed from § 2.3 | dbmate on an empty database; seed counts, ranks and `workspace:w` holders asserted in `packages/db/test/dashboardRbacSeed.integration.test.ts` | | 3 | Customer | Built. The RBAC factory becomes hand-written `b2b` and `bank` copies; the generic `DashboardRoleService` and `DashboardPermissionService` are deleted; `20260921180000_customer_dashboard_auth_tables.sql` prefixes every catalog table and the membership (a view keeps the old membership name for the deploy window), and adds § 2.1's `b2b` columns and `customer_dashboard_invite`; the `CustomerDashboard*` services and admin services of § 2.4; the sync as the consumer's only write path, with `CustomerDashboardUserAdminService` folded in; the backfill | dbmate up and down on an empty database; the PropelAuth-era insert column lists still land; a `getTestDb()` suite per service with a cross-customer negative on every method; a replayed snapshot writes nothing; a backfill rerun writes nothing and `--check` then finds no drift; `has_table_privilege` on `dashboard_invite` once deployed | | 4 | Bank | Built. `20260921190000_bank_dashboard_auth_tables.sql` creates `bank.dashboard_{user,membership,invite,user_activity}` and adds `b2b.bank.propelauth_org_id` / `dashboard_role_set_id` — new tables, no rename, since `bank` had only the RBAC catalog; adapting only what is genuinely bank: a bank's own workspace is `b2b.bank` itself (no `dashboard_workspace`), an org carrying `bank-dashboard:r` resolves its own bank by name match, and each `-bank:r` permission it or any other bank org grants is a separate membership; `BankDashboard*Service`s under `./bank`; `backoffice_api_rw` gains `cfx_bank_read_write` | as PR 3, for bank; `bankDashboardAuthTables.integration.test.ts` for the new tables' shape and grants | | 5 | Admin | Built. Admin's tables stay as they are. `AdminDashboardUserActivityService.create({ events, tx? })` and `search` with `actor`; domain services return activity descriptors, which the admin-dashboard actions record | each touched package's tests; `rg` finds no domain service inserting into `adminDashboardUserActivityTable` | | 6 | Customer cutover | Built (#3968). `customer-dashboard/src/lib/auth.ts` reads membership and the caller's grants, through `CustomerDashboardPermissionService` at the time and `CustomerDashboardMembershipService.searchOwn` since; team, invite and settings actions move to the new services; `last_seen_at` is added with its writer | a parity script on dev comparing PropelAuth claims with DB permissions for every member, empty before merge; `next build` | | 7 | Bank cutover | Built (#3966). `getAuthBankIds` from membership; `BANK_PERMISSION_TO_IDS` deleted; bank actions record activity | the parity script for bank; an operator with only a `dart` membership gets `/forbidden` on another bank's host; `next build` | | 8 | Cleanup | Built. Deletes what the cutovers left unread: the PropelAuth-backed `DashboardWorkspaceInviteService`, `DashboardWorkspaceMemberService` and `DashboardUserService`, and `DashboardWorkspaceService`, its `update` included: the workspace name is `customer.name` / `bank.name`, owned in our DB and changed by CFX admins, so neither dashboard renames a PropelAuth org. `resolveDashboardPermissions` moves to `backoffice-api` beside `cfxStaffRole.ts`, since the backfills still translate live PropelAuth permissions | `rg` for the deleted names across `.ts` finds nothing, and finds `resolveDashboardPermissions` only in `backoffice-api` | | 9 | Shared feed | One feed component in `@cfxlabsinc/ui`; all three dashboards switched | `next build` for each dashboard | | 10 | DB owns authorization | Built. The syncs still mirror identity from PropelAuth — email, names, `emailConfirmed`, `enabled` / `locked` — and org presence: joining a customer's org or a bank's own org creates the membership with a confirmed address's PENDING invite role or the role set's new `default_role_id`, and leaving it removes the membership. They no longer read a role, a permission or a role mapping, never move a role set, name-match a bank or touch a cross-bank grant, and only the CFX staff rule moves a held role. Role changes write only the DB, invites send PropelAuth the fixed `Member`, neither dashboard reads token roles or permissions, and `20260925190000_dashboard_role_set_default_role.sql` adds the default role and puts role-set-less customers on `Default`, and the PropelAuth console cleanup this unblocked ran on 2026-09-28; its runbook went when PropelAuth was removed from the code base | the migration round-trips down and up; a sync test for every join path and for a default-only snapshot deleting no bank membership; both backfills converge with PropelAuth answering default roles only | | 11 | Operator authoring | Built. `*RoleSetAdminService` and `*RoleAdminService` for both dashboards, `*MembershipAdminService.update` / `delete`, `*InviteAdminService.search` / `delete` (and bank `create`); role writers lock the workspace `FOR SHARE`; the admin-dashboard surfaces and `admin.dashboard_user_activity` types of § 2.4 "Operator authoring"; `20260926224812_admin_dashboard_access_permissions.sql` adds `dashboard-roles:r` / `:w` and `dashboard-members:r` / `:w`; `admin_dashboard_rw` gains `cfx_bank_read_write`; each dashboard's Team page gains the read-only Roles reference | a `getTestDb()` suite per new service, including `assign` remapping only the target workspace, refusing `ROLE_SET_MISSING_ROLES` with nothing written, and refusing a concurrent role write or invite that picked a role from the old set; the rank tests that no role unseats or ties the top role; `next build` for all three dashboards | ## 4. Risks and open questions | Risk | Mitigation | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- | | **RLS.** Every table enables RLS and no policy exists, so only owners and `BYPASSRLS` roles read anything. `AuroraWorkloadUser` defaults `bypassRls` to true; a role created with `false` sees empty tables | Every table file calls `.enableRLS()`; new roles keep the default; this RFC adds no policies | | **Grants for the new schema.** A migration run before Pulumi creates `bank` leaves a `cfx_ci`-owned schema, and a table without the ownership reassignment breaks only at read time | PR 1 deploys before PR 2, whose migration raises when `bank` is missing on a database that has `cfx_owner`; PR 2 verifies with `has_table_privilege` | | **Role membership edits.** Changing `inRoles` re-runs the provider's membership reconciliation, which has revoked memberships it did not list (the `cfx_ci` note in `packages/aurora/pulumi/database.ts`) | Read the `cfx-db` preview for revokes before applying | | **PropelAuth's role hierarchy against `rank`.** The hierarchy is console config, not code, and CFX Admin and Owner are incomparable in it | Transcribed in the seed with CFX Admin above Owner (§ 2.3); the parity scripts catch any other assignable-role change | | **Roles no member holds** have no grants to read, so the seed grants them nothing | Transcribe the § 2.3 list from the console before cutover; until then a member placed on one is denied, not over-granted | | **Multi-workspace users.** The session still names the active org through PropelAuth's `getActiveOrg()` | Permissions resolve for that workspace's membership only; a missing membership fails closed | | **Drift locks users out.** A membership the consumer missed is a `/forbidden`, not a stale permission | State-based consumer, the reconciliation job, and the parity scripts before each cutover | | **Rows from before the backfill.** `customer_dashboard_membership` has a row only where a user saved preferences; each reads ACTIVE with no role, and some name users who have since left | A role-less row grants nothing; the backfill gives each listed member a role and soft-deletes the rest; the cutover waits for a clean `--check` | | **Backfill cost.** One `searchOrgUsers` and one `searchPendingInvites` per org; PropelAuth's rate limits are not recorded in the repo | Run on dev first; restart-safe | | **An authorize-by-join query that drops the join** leaks across workspaces, and no type catches it | A cross-workspace negative test on every method, the per-route discipline COGNITO_AUTH_MIGRATION.md asks for | Open questions: 1. ~~**Who creates custom roles** for customer and bank.~~ Resolved: CFX operators, in admin-dashboard only (PR 11, § 2.4 "Operator authoring"). Workspace owners assign existing roles and see their set read-only; permission keys stay migration-managed. 2. **Reconciliation cadence and runner.** ## 5. Relationship to COGNITO_AUTH_MIGRATION.md This RFC builds that RFC's per-audience RBAC tables, multi-workspace membership and DB-backed `lib/auth.ts` port (ENG-3067, ENG-3070) while PropelAuth still authenticates. | That RFC says | This RFC | Effect on that plan | | ----------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------- | | Data model: three RBAC table sets "keyed by Cognito `sub`" | keyed by the provider column; a Cognito `sub` is a `cognito_sub` column beside it | **Conflict, resolved here.** A single key cannot hold both identities through Phase 1's dual-accept window; two columns can | | Phase 1 provisions users and workspaces into Cognito and the RBAC tables | the RBAC tables are populated and live before Phase 1 | Phase 1 shrinks to the pool, the Pre-Token Generation Lambda and the login flip; provisioning reads our tables | | Phase 2 removes the `propelauth-events` route | the route is gone; `PropelAuthEventConsumer` lives in `backoffice-api` | Phase 2 removes `PropelAuthEventConsumer` and its Hookdeck connection instead | | Phase 4: bank scope from `getAuthBankIds()` (permission strings plus host) | bank membership rows are the reachable set | The Pre-Token Lambda's bank `customer_id` claim reads `bank.bank_dashboard_membership` | | Phase 5 re-keys `admin.dashboard_user` to Cognito `sub`; "roles and permissions tables are untouched" | admin's tables stay as they are | Phase 5 adds a `cognito_sub` column beside `cf_access_sub` rather than re-keying (built; `cf_access_sub` is now nullable) | | Admin is single-role | `admin.dashboard_user.role_id`, unchanged | No change | | The Pre-Token Generation Lambda reads the RBAC tables | the tables span `b2b`, `bank` and `admin` | Its database role needs `USAGE` and `SELECT` on all three, and `BYPASSRLS` | | `api_token` rows hold a role assignment in the customer RBAC | `b2b.customer_dashboard_role` | No change; the FK target exists |