RLS Adoption Investigation

Ticket: TOP-5107 Date: 2026-05-22 Status: Investigation complete

---

1. Current State Assessment

Auth Architecture

Fauna (production today): Each tenant is a separate child database. The token cookie is a Fauna secret scoped to that database. Query.identity() resolves the user internally. Tenant isolation is physical — separate databases, no cross-tenant queries possible.

Postgres (migration in progress): Single shared database, all tenants coexist, isolated by tenantId column on every tenant-scoped table. authMiddleware must resolve identity and validate tenant membership explicitly at the application layer.

TenantBaseService RLS Extension

TenantBaseService (services/base/TenantBaseService.ts:35-47) wraps every Prisma query in a $transaction that calls set_config('request.jwt.claims', ...) before executing the query. This correctly solves Neon’s stateless HTTP adapter problem — both the config and the query run in the same transaction.

Usage across tenant services (277 total calls):

Accessor	Count	set_config fires?	Notes
this.prismaRLS	246	Yes	Correct path
this.prisma	25	No	Bypasses extension — FineService, StripeService, VehiclePurchaseService, UserTenants, SignatureRequestsService, UsersService, TenantCompanyService, SelfCheckInRequestsService, VehicleService:134
this.model	6	No	Inherited base methods (findById, findMany, create, update, delete, count, paginate) use the unextended client because super(prisma, modelName) runs before prismaRLS is created

tenantId Provenance

The tenantId that reaches service constructors is secure:

companyId cookie (user-controlled) — acts as selector, not credential
    ↓
authMiddleware(request, idToken, companyId)
    ├── Firebase verifyIdToken(idToken) → decodedToken (cryptographic proof of identity)
    └── user_tenants.findFirst({ userId: ..., tenantId: companyId })
          → null → throws "User settings not found" (rejected)
          → found → returns userTenant.tenantId (from DB row, not cookie)
    ↓
createTenantServices(validatedTenantId, validatedRole, rlsJwt)
    ↓
new VehicleService(tenantId, ...) — trusted

A user belonging to multiple tenants uses the cookie to select which tenant context they want. The DB lookup validates membership. If they claim a tenant they don’t belong to, the request fails.

All checked API routes (check-user-data.js, create-checkout-session.js, create-customer-portal-session.js) go through authMiddleware.

Gaps Found

#	Gap	Severity
1	authMiddleware user lookup uses decodedToken.uid (Firebase UID) to query users.id — but users.id is inconsistently populated (Firebase UID for unregistered-path users, snowflake for others). Should use email.	Blocker
2	RLSJwt type (services/types.ts:85-90) missing tenantId — policies can’t enforce tenant isolation without it	Required
3	App connects as neondb_owner (confirmed: ROOT_POSTGRES_URL=postgresql://neondb_owner:...). This is a superuser that silently bypasses all RLS policies.	Required
4	25 this.prisma + 6 this.model calls bypass the RLS extension	Leak
5	Neon already has authenticated role available but the app doesn’t use it	Opportunity

---

2. Prisma User Tables Are Incomplete

The Postgres user creation paths have never been fully exercised in production. Fauna handles real traffic; the Postgres path is gated behind feature flags (faunaMigrationUsers, faunaMigrationUserTenants).

User Creation Paths

Unregistered user (addUnregisteredUser.js):

1. Create child user in Fauna → createLocalUser
2. Create root user in Fauna → createRootUser (email, name, companyId, role)
3. Create Firebase account → createFirebaseUserIfNotExist
4. If faunaMigrationUsers: create Prisma users record with id = firebaseUser.uid and user_tenants record with userId = firebaseUser.uid

This path works structurally — Firebase UID is available and stored as users.id.

Registered user (addRegisteredUser.js):

1. Check root user exists in Fauna
2. Check local user doesn’t exist yet in tenant
3. If faunaMigrationUsers: call createNewUserTenant({ ...data, companyId }) — no uid passed
4. Create user_company link in Fauna root DB
5. No Prisma users record is ever created for already-registered users

This path is broken: user_tenants.userId is required but omitted, and no users row is created.

Inconsistent ID Storage

getUserFields in RootUsersService.ts:10-28:

id: userInput.uid || undefined,      // Firebase UID becomes users.id (overrides snowflake default)
userId: userInput.uid,               // Also stored redundantly

filterObject strips undefined values but keeps null. When uid is provided (unregistered path), users.id = Firebase UID. When uid is missing (registered path or future creation paths), users.id = snowflake default.

Conclusion: The current Prisma user tables cannot be treated as a stable foundation for RLS. The registered-user path needs fixing before RLS policies can reference these tables.

---

3. Schema Shape — Fauna vs Prisma Mismatch

Fauna Model (3 collections, 2 DB levels)

Collection	Database	Purpose
users	Root	Global account: { email, name, surname, phone }
user_company	Root	Tenant membership: { user (ref), companyId, role }
users	Child (per-tenant)	Tenant-local profile: { email, name, role, permissions, driverLicence, passport, calendarSettings, ... }

Prisma Model (2 tables, 1 DB)

Table	Maps to	Issues
users	Fauna root users	id inconsistently holds Firebase UID or snowflake; no FK relations
user_tenants	Fauna user_company + child users merged	userId required but not always available; triple-duty (membership + profile + account link)

User Lifecycle Gap

A tenant admin creates a driver (tenant profile) before the driver registers (creates an account). In Fauna, the child DB users collection is independent — no reference to root. In Postgres, user_tenants.userId is a required field but the account (users row) doesn’t exist yet.

Proposed Target Model Direction

Full schema evolution is a separate ticket. The investigation recommends this direction; RLS helper functions should be designed to survive the transition.

accounts (was: users)
├── id: snowflake (internal, always — never overridden with external IDs)
├── externalAuthId: string, unique, nullable  ← currently holds Firebase UID; provider-agnostic
├── email: string, unique                     ← stable cross-system link
├── registeredAt: nullable                    ← null = invited, not registered
└── name, surname, phone, active, isAdmin

tenant_members (was: user_tenants)
├── id: snowflake
├── accountId: FK → accounts.id, nullable ← null = pre-registration
├── tenantId: FK → tenants.id
├── email: string                        ← always present, used for matching on registration
├── role: ADMIN | OPERATOR | DRIVER | PARTNER
├── name, surname, phone                ← tenant-local profile (may differ from account)
├── permissions (billingVisible, calendarAccess, etc.)
├── driverLicence, passport, documents
└── partnerId

Key changes from current schema:

• accountId is nullable — supports pre-registration tenant members
• email is the stable link between tables; matched on registration to populate accountId
• externalAuthId replaces the current pattern of storing Firebase UID as users.id. Provider-agnostic name — holds Firebase UID today, would hold Auth0/Clerk user ID if the provider changes. Nullable for pre-registration accounts.
• Consider adding externalAuthProvider: string, nullable (e.g., “firebase”, “auth0”) if a gradual auth provider migration is planned. Not required for a hard cutover — all IDs simply get repopulated. Useful when users coexist across providers and the middleware needs to know which provider to verify against.
• Clear naming: “account” = global identity, “tenant member” = how a tenant sees that person

Table Inventory

53 tables with tenantId (need tenant isolation policies): orders, vehicles, clients, companies, payments, invoices, user_tenants, partners, fines, email_templates, settings, extra_services, insurances, audit_logs, access_logs, and 38 more.

23 tables without tenantId (need different strategies):

• Global/critical: users, tenants
• Reference data: cargos_citizenship, cargos_states, distances, sources
• System: integrations, root_settings, system
• Join tables: customers, orders_seasons_tariffs_matrix, etc.

1 table with @@map: SelfCheckInRequest → self_check_in_requests

---

4. Key Decisions with Rationale

Use email as primary identity link, externalAuthId for auth provider lookup

Decision: authMiddleware should look up users by decodedToken.email, not decodedToken.uid. The target schema should include a generic externalAuthId field (not a provider-specific firebaseUid column) to store the current Firebase UID and support future provider changes.

Why not a provider-specific firebaseUid column:

• Couples the DB to a specific auth provider. If the platform moves to Auth0, Clerk, or SSO, the column becomes dead weight or requires migration.
• The current code already stores Firebase UID inconsistently as users.id — overwriting the primary key is the worst form of coupling.
• Firebase UIDs are only available after registration. Pre-registration tenant members have no Firebase identity.

Why email as the primary link:

• Unique in Firebase, unique in users (has @unique constraint), present in user_tenants.
• Provider-agnostic — works with any auth system.
• Already available in every decoded token (decodedToken.email).

Why also include externalAuthId:

• The Firebase UID already exists in the current data (users.id for some records, user_tenants.userId for others). The migration needs a defined landing place for this data.
• Provides a fast unique-index lookup for the auth hot path — externalAuthId with a unique constraint is O(1).
• Provider-agnostic name — holds Firebase UID today, would hold Auth0/Clerk user ID after a provider switch.
• Nullable — pre-registration accounts have no auth provider ID yet.

Consider: externalAuthProvider field (e.g., “firebase”, “auth0”). Not required for a hard cutover where all users move at once — the IDs simply get repopulated. Useful if a gradual auth provider migration is planned where users coexist across providers and the middleware needs to know which provider to verify against.

Risk: Users can change their email. Mitigation: email changes must update both the auth provider and the DB atomically. This is rare in this business context (B2B vehicle rental management).

Use existing Neon authenticated role (not a new custom role)

Decision: The app connection should switch from neondb_owner to authenticated. No new PostgreSQL role needed.

Why: Neon already provides four roles:

• neondb_owner — superuser, owns everything, bypasses all RLS silently
• authenticator — connection-time role, switches to anonymous or authenticated
• anonymous — unauthenticated requests
• authenticated — authenticated requests, RLS policies apply

The authenticated role is exactly what RLS policies target (TO authenticated). Creating a custom app_user would duplicate functionality that’s already there.

Confirmed: ROOT_POSTGRES_URL connects as neondb_owner (verified from Neon branch sync output). This means any future RLS policies would be silently ignored until the connection role is changed.

What changes: Use authenticated role for the application connection string. Keep neondb_owner only for migrations, admin operations, and background jobs.

Defense-in-depth (RLS as safety net, not primary enforcement)

Decision: Keep app-level tenantId filtering in TenantBaseService. Add RLS as a second layer.

Why not make RLS primary:

• 53 tenant-scoped tables, 277+ service query calls, evolving schema during Fauna→Postgres migration.
• Ripping out all app-level filters and relying solely on DB policies is high-risk and high-effort.
• The codebase has 25 this.prisma leaks that bypass the extension — fixing all of these is a prerequisite for RLS-only enforcement.

What this means: TenantBaseService continues to add WHERE tenantId = X to every query. RLS catches what slips through — a missed filter, a raw query, a new service method that forgets to scope.

When to revisit: Once Fauna is fully decommissioned and the Postgres schema is stable, evaluate whether to make RLS the primary isolation mechanism and simplify the service layer.

Raw SQL in Prisma migrations for RLS policies (not Drizzle)

Decision: Define RLS policies via raw SQL in Prisma migration files, not via Drizzle’s pgPolicy().

Why not Drizzle:

• Running two ORMs on the same database creates schema drift risk — Drizzle doesn’t know about Prisma’s tables, Prisma doesn’t know about Drizzle’s policies.
• Migration ordering becomes a coordination problem.
• Cognitive overhead: two schema files, two migration systems, two mental models for the team.

Why raw SQL:

• Prisma supports creating empty migrations (prisma migrate dev --create-only) and writing arbitrary SQL in them. The SQL is tracked in Prisma’s migration history.
• RLS policies are pure SQL: CREATE POLICY, ALTER TABLE ... ENABLE ROW LEVEL SECURITY. They don’t need ORM-level diffing.
• Policy changes are infrequent — a new migration that DROP POLICY + CREATE POLICY is straightforward.

When to revisit: If managing 50+ policies in raw SQL becomes unwieldy, evaluate Drizzle as a policy management layer. The team already uses Drizzle for the RAG module (packages/ai-assistant-rag), so the skill exists.

SECURITY DEFINER helper functions for policy expressions

Decision: Create PostgreSQL functions with SECURITY DEFINER to abstract identity/role lookups for use in RLS policy expressions.

What SECURITY DEFINER means: A PostgreSQL function runs in one of two security contexts:

• SECURITY INVOKER (default) — runs with the caller’s privileges. If the caller is authenticated and RLS is enabled, the function’s queries are subject to RLS policies.
• SECURITY DEFINER — runs with the privileges of the function owner (typically the superuser who created it). The function’s internal queries bypass RLS.

Why needed: RLS policies need to know the current user’s role and tenant. That data lives in user_tenants, which itself has RLS policies. Without SECURITY DEFINER, reading the role to evaluate a policy would be blocked by the policy it’s trying to enforce — a circular dependency.

Functions needed:

• get_user_tenant() — reads current_setting('app.current_tenant_id') from the session context
• get_user_role() — reads role from user_tenants using JWT claims email + tenant ID
• Domain-specific helpers as needed (e.g., is_assigned_to_order() for DRIVER role)

Security: The function body is trusted code. The caller invokes it and receives a return value — they can’t influence the internal query. The function’s queries bypass RLS, but each function is small (5-10 lines) and auditable.

Decoupling benefit: If tables are renamed (e.g., user_tenants → tenant_members), only the helper functions need updating. All policies that reference get_user_role() or get_user_tenant() remain unchanged.

accountId nullable on tenant_members

Decision: The proposed tenant_members table should have a nullable accountId FK to accounts.

Why: Supports the pre-registration lifecycle. A tenant admin creates a driver profile before the driver has an account. accountId starts null, gets populated on registration when the system matches the tenant member to the new account by email.

Note: Existing FK constraints in the Prisma schema are limited (10 @relation directives, mostly on junction tables like customers, orders_seasons_tariffs_matrix). Adding FKs retroactively requires data validation to ensure no orphaned references. This is a separate concern from RLS.

---

5. Required Changes (ordered)

#	Change	Location	Notes
0	Fix authMiddleware user lookup — use email instead of decodedToken.uid	server/graphql/helpers/authMiddleware.js:42-54	Blocker for Postgres path correctness
1	Add tenantId to RLSJwt type and include it in set_config payload	services/types.ts:85-90, authMiddleware.js:19-24, createResolversContext.js:110-115	Required for policies to enforce tenant isolation
2	Switch app connection from neondb_owner to authenticated role	Neon console + ROOT_POSTGRES_URL env var	Without this, all RLS policies are silently bypassed
3	Migrate 25 this.prisma calls to this.prismaRLS	FineService, StripeService, VehiclePurchaseService, UserTenants, SignatureRequestsService, UsersService, TenantCompanyService, SelfCheckInRequestsService, VehicleService	Ensures set_config fires for all queries
4	Create SECURITY DEFINER helper functions	Raw SQL Prisma migration	get_user_role(), get_user_tenant()
5	Enable RLS + write tenant isolation policies for 53 tables	Raw SQL Prisma migration	USING (tenant_id = get_user_tenant())
6	Write role-based policies for global tables	Raw SQL Prisma migration	users, tenants: different pattern (user-scoped or admin-only)

---

6. Phased Rollout

Phase A — Fix Blockers (items 0-3)

No behavioral change, just correctness. Can be done independently of RLS:

• Fix the authMiddleware email lookup
• Add tenantId to RLSJwt
• Switch connection role to authenticated
• Fix the 25 this.prisma leaks

Phase B — Low-Risk Tables

Enable RLS on tables with simple access patterns: settings, email_templates, extra_services.

• Validate in dev/preview environments
• Write the helper functions
• Build confidence with the migration workflow

Phase C — Core Tables

Roll out to the main business tables: orders, vehicles, clients, payments, invoices, companies.

• Use per-tenant feature flags (Statsig) to test with specific tenants first
• Monitor query performance — tenant isolation policies are simple (tenantId = get_user_tenant()), but volume matters

Phase D — Global Tables + Role-Based Policies

More complex policy logic for users, tenants, and reference data tables.

• Role-based visibility (ADMIN sees all, DRIVER sees only own profile)
• Column-level security via views if PII masking is in scope

Phase E — Schema Evolution (separate ticket)

• Rename users → accounts, user_tenants → tenant_members
• Add proper FKs (accountId → accounts.id, tenantId → tenants.id)
• Fix user creation paths (registered-user flow)
• Update helper functions to reference new table names
• Policies remain unchanged (they use helper functions, not table names directly)

---

7. Key Risks and Mitigations

Risk	Impact	Mitigation
Superuser bypass	All RLS silently ignored if app connects as neondb_owner	Switch to authenticated role (Phase A). Verified: current connection IS neondb_owner.
Performance	Subquery-based policies (DRIVER/PARTNER role checks with JOINs to orders) may be slow on large tables	Start with simple tenant-isolation policies (index lookup). Benchmark complex role-based policies on dev before production.
Schema evolution breaks policies	Table renames invalidate hardcoded policy references	Helper functions (get_user_role(), get_user_tenant()) abstract table access. Only functions need updating on rename.
Background jobs bypass RLS	Jobs need full DB access for cross-tenant operations (billing, sync, notifications)	Jobs connect as neondb_owner. Clearly separate job connection strings from app connection strings.
Email-based identity + email changes	Changed email breaks identity resolution between Firebase and DB	Email changes must update Firebase + DB atomically. Rare in this B2B context.
Registered-user path is broken	Can’t create tenant members for already-registered users via Postgres path	Fix in Phase A (item 0) or defer to Phase E (schema evolution). Fauna handles this path in production today.
Dual-write inconsistency	Fauna and Postgres may drift during migration period	Feature flags (faunaMigrationUsers, faunaMigrationUserTenants) control which path is active. Dual-write ensures both stores receive updates when enabled.

---

8. Open Questions

1. Should Firebase custom claims carry tenantId? This would embed the active tenant in the signed JWT token, potentially simplifying the auth flow. Tradeoff: claims are cached until token refresh (up to 1 hour), and multi-tenant users still need a selector mechanism.

2. What connection string/role should background jobs use? QStash handlers, Upstash-triggered jobs, and cron tasks need cross-tenant access. They should connect as neondb_owner, but this needs explicit separation from the app connection.

3. Should column-level security (PII masking) be in scope? The SupabasePermessi reference project uses PostgreSQL views (clients_view) to mask sensitive fields (email, phone, driverLicence, passport) based on role. This would protect PII for DRIVER/PARTNER roles at the DB level. Adds complexity but provides defense-in-depth for personal data.

4. Schema evolution timing: Should the user table restructure (accounts/tenant_members rename, nullable accountId, proper FKs) happen before, during, or after initial RLS rollout? Helper functions decouple the two, but doing both at once adds risk.

5. How to handle the this.model inheritance problem? TenantBaseService base methods (findById, findMany, etc.) use this.model which bypasses the RLS extension. Options: (a) override all base methods in every service, (b) restructure TenantBaseService to pass prismaRLS to super(), (c) accept it since concrete services mostly override anyway.

---

References

• SupabasePermessi: private reference project demonstrating RLS with SECURITY DEFINER helpers, per-role policies, and column-level security via views. Available internally in the laflei organization.
• Neon Authorize documentation: built-in roles (neondb_owner, authenticator, anonymous, authenticated).
• Prisma RLS documentation: pris.ly/d/row-level-security