Designing role-based access control for client-data platforms

Role-based access control (RBAC) is one of those architectural decisions that looks easy until it isn't. A three-tier RBAC system — advisors, branch managers, administrators — sounds like an afternoon's work. In practice, it touches the data model, the query layer, the API design, and the UI. Get it wrong and you either expose data that shouldn't be seen or build a system so locked down that real work is impossible.

This article documents the design decisions behind a production client-data platform we built for a Singapore insurer, where RBAC was the hardest part.

The access model

The platform had three user tiers with distinct access patterns:

Advisors — see and edit only the clients assigned to them. Can view current fund prices for client portfolios. Cannot see clients belonging to other advisors.

Branch managers — see all clients within their branch (across all advisors). Read-write on branch clients. Can view aggregated branch metrics. Cannot see clients in other branches.

Administrators — full platform access. Can create, deactivate, and reassign advisors. Can generate cross-branch reports. Can access the data pipeline logs.

The naive implementation — check the user's role in the application layer before returning data — has a critical flaw: it depends on every query being correctly filtered. One missed WHERE clause and an advisor sees another's clients.

Row-level security: the right architecture

The production implementation uses Supabase Row Level Security (RLS) — PostgreSQL policies that enforce access rules at the database layer, regardless of how the query is written.

The core pattern:

-- Advisors see only their clients
CREATE POLICY "advisors_own_clients" ON clients
  FOR ALL
  USING (
    auth.uid() = advisor_id
    OR EXISTS (
      SELECT 1 FROM users
      WHERE id = auth.uid()
      AND role IN ('branch_manager', 'admin')
    )
  );

For branch managers:

-- Branch managers see all clients in their branch
CREATE POLICY "branch_manager_branch_clients" ON clients
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM users u
      WHERE u.id = auth.uid()
      AND u.role = 'branch_manager'
      AND u.branch_id = clients.branch_id
    )
  );

The advantage: even if the application layer has a bug and omits a WHERE clause, the database enforces the access boundary. An advisor querying SELECT * FROM clients gets only their clients, always.

The audit trail requirement

Financial-services platforms need an audit trail: who accessed what, when, and from which IP. The implementation:

1. A client_access_log table: user_id, client_id, action, timestamp, ip_address
2. A Postgres trigger that inserts a log row on every SELECT and UPDATE against the clients table
3. A daily job that compresses and archives logs older than 90 days

The trigger approach means logging is enforced at the database layer — the application can't bypass it.

The ingestion pipeline and access control

The platform's data pipeline accepts HTML exports from the insurer's legacy system, parses client records, and loads them into the database. The access-control challenge here: the ingested data includes a advisor_code field that maps to the platform's advisor accounts.

The import job:

1. Parses the HTML export into structured records
2. Looks up the advisor_code → advisor_id mapping
3. Inserts records with the correct advisor_id
4. Logs the import: record count, timestamp, processing duration, any unmapped advisor codes

Unmapped advisor codes (advisors in the legacy system not yet in the platform) are queued for manual review rather than being silently dropped.

What went wrong the first time

Our first implementation put the branch-manager filter in the API route handlers — a server-side check before querying the database. It worked, but it had two problems:

1. Inconsistency: some routes checked the role explicitly, others relied on Supabase's auth.uid() in queries. The inconsistency meant an audit found three routes that would have returned cross-branch data for a branch manager with a modified JWT.
2. Testability: testing access control required spinning up test users with specific roles and running real queries. With RLS, you can write a SQL test that runs as a specific user role and asserts the result set.

The rewrite to pure RLS policies eliminated the inconsistency and made the access model auditable in SQL — one source of truth.

The takeaway for client-data platforms

If you're building a client-data platform with multiple access tiers:

• Enforce access at the database layer with row-level security, not just the application layer
• Build an audit trail into the schema from day one — retrofitting it is painful
• Test RBAC with role-switching queries, not just application-level tests
• Treat the import pipeline as part of the access model — what it assigns determines what gets seen

The access model is not a feature you add after the data model is built. It shapes the data model. Design it first.

---

Built on Supabase + Next.js + TypeScript. The platform described is a production system delivered for a Singapore insurer; details have been anonymised.