João Victor Santos Dumont

Posted on Jun 25

Building a LGPD-Compliant Healthcare System with Next.js and Supabase Row Level Security

#supabase #nextjs #webdev #security

When I was hired to build a clinic management system for a medical practice in Brazil, I quickly realized that "just add authentication" wasn't going to cut it. The system needed to handle sensitive patient records, digital prescriptions, and telemedicine sessions — all under Brazil's data protection law, the Lei Geral de Proteção de Dados (LGPD), which is the Brazilian equivalent of GDPR.

The core challenge: multiple users with different roles (doctors, receptionists, and psychologists) needed to access the same database — but with strictly isolated data. A receptionist should never see a patient's medical history. A doctor should only see their own patients' records. And everything needed to be auditable.

In this article, I'll walk through how I used Next.js, Supabase, and Row Level Security (RLS) to build a healthcare platform that's both developer-friendly and legally compliant.

What is Row Level Security (RLS)?

Row Level Security is a PostgreSQL feature that lets you define policies controlling which rows a user can read, insert, update, or delete — at the database level, not the application level.

This distinction matters enormously in healthcare applications. If you enforce access control only in your API layer, a single bug in your middleware can expose every patient record in your database. With RLS, even if your application logic has a flaw, the database itself refuses to return unauthorized data.

Supabase makes RLS straightforward to configure, and it integrates naturally with Next.js through the Supabase client.

System Overview

The clinic had three user roles:

Doctor — full access to their own patients' records, appointments, and prescriptions
Psychologist — same as doctor, but scoped to their own specialty
Receptionist — can view and manage appointments and basic patient info, but cannot access medical records or prescriptions

Here's the high-level architecture:

Next.js (App Router)
    └── Supabase Client (server-side)
            └── PostgreSQL with RLS Policies
                    ├── patients
                    ├── medical_records
                    ├── appointments
                    └── prescriptions

Setting Up the Database Schema

Let's start with the core tables. I'll focus on patients and medical_records since they contain the most sensitive data.

-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- User profiles table (extends Supabase auth.users)
create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  full_name text not null,
  role text not null check (role in ('doctor', 'psychologist', 'receptionist')),
  specialty text,
  created_at timestamptz default now()
);

-- Patients table
create table public.patients (
  id uuid default uuid_generate_v4() primary key,
  full_name text not null,
  date_of_birth date not null,
  cpf text unique not null, -- Brazilian taxpayer ID
  email text,
  phone text,
  created_by uuid references public.profiles(id),
  created_at timestamptz default now()
);

-- Medical records (prontuário eletrônico)
create table public.medical_records (
  id uuid default uuid_generate_v4() primary key,
  patient_id uuid references public.patients(id) on delete cascade,
  doctor_id uuid references public.profiles(id),
  notes text not null,
  diagnosis text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Appointments
create table public.appointments (
  id uuid default uuid_generate_v4() primary key,
  patient_id uuid references public.patients(id),
  professional_id uuid references public.profiles(id),
  scheduled_at timestamptz not null,
  status text default 'scheduled' check (status in ('scheduled', 'completed', 'cancelled')),
  created_at timestamptz default now()
);

Enabling and Configuring RLS

By default, Supabase tables have RLS disabled. Once you enable it, all access is denied unless you explicitly create policies. This is exactly what you want for healthcare data — deny by default, allow by exception.

-- Enable RLS on all sensitive tables
alter table public.patients enable row level security;
alter table public.medical_records enable row level security;
alter table public.appointments enable row level security;
alter table public.profiles enable row level security;

Now let's create the policies. The key is using auth.uid() — Supabase's built-in function that returns the ID of the currently authenticated user.

Profiles Policies

-- Users can read their own profile
create policy "Users can view own profile"
  on public.profiles for select
  using (auth.uid() = id);

-- Receptionists can view all professional profiles (to display appointment info)
create policy "Receptionists can view all profiles"
  on public.profiles for select
  using (
    exists (
      select 1 from public.profiles
      where id = auth.uid() and role = 'receptionist'
    )
  );

Patients Policies

This is where it gets interesting. Receptionists can see basic patient info, but doctors and psychologists can only see patients they've treated.

-- Doctors and psychologists: only see their own patients
create policy "Professionals see own patients"
  on public.patients for select
  using (
    exists (
      select 1 from public.profiles p
      where p.id = auth.uid()
        and p.role in ('doctor', 'psychologist')
        and (
          -- Patient was created by this professional
          public.patients.created_by = auth.uid()
          or
          -- Or this professional has a medical record for them
          exists (
            select 1 from public.medical_records mr
            where mr.patient_id = public.patients.id
              and mr.doctor_id = auth.uid()
          )
          or
          -- Or has an appointment with them
          exists (
            select 1 from public.appointments a
            where a.patient_id = public.patients.id
              and a.professional_id = auth.uid()
          )
        )
    )
  );

-- Receptionists: can see all patients (basic info only — enforced at app layer)
create policy "Receptionists see all patients"
  on public.patients for select
  using (
    exists (
      select 1 from public.profiles
      where id = auth.uid() and role = 'receptionist'
    )
  );

-- Only professionals can insert patients
create policy "Professionals can insert patients"
  on public.patients for insert
  with check (
    exists (
      select 1 from public.profiles
      where id = auth.uid() and role in ('doctor', 'psychologist', 'receptionist')
    )
  );

Medical Records Policies

Medical records are the most sensitive part. Receptionists should never touch these.

-- Only the doctor who created the record can read it
create policy "Doctors see own medical records"
  on public.medical_records for select
  using (doctor_id = auth.uid());

-- Only doctors and psychologists can insert
create policy "Professionals can insert medical records"
  on public.medical_records for insert
  with check (
    exists (
      select 1 from public.profiles
      where id = auth.uid() and role in ('doctor', 'psychologist')
    )
    and doctor_id = auth.uid()
  );

-- Professionals can update only their own records
create policy "Professionals update own records"
  on public.medical_records for update
  using (doctor_id = auth.uid())
  with check (doctor_id = auth.uid());

Integrating RLS with Next.js

With the database policies in place, let's wire this up to a Next.js application using the App Router and Supabase's server-side client.

Setting Up Supabase Clients

Supabase requires two separate clients in Next.js: one for server components (using the service role or user session) and one for client components.

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export function createClient() {
  const cookieStore = cookies()

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return cookieStore.get(name)?.value
        },
        set(name: string, value: string, options) {
          cookieStore.set({ name, value, ...options })
        },
        remove(name: string, options) {
          cookieStore.set({ name, value: '', ...options })
        },
      },
    }
  )
}

// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'

export function createClient() {
  return createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
}

Fetching Data with RLS in a Server Component

Here's the beauty of RLS: your data fetching code stays clean. You don't manually filter by user ID — the database handles it automatically based on the authenticated session.

// app/dashboard/patients/page.tsx
import { createClient } from '@/lib/supabase/server'
import { redirect } from 'next/navigation'

export default async function PatientsPage() {
  const supabase = createClient()

  const { data: { user } } = await supabase.auth.getUser()

  if (!user) {
    redirect('/login')
  }

  // RLS automatically filters this query based on the user's role and relationships
  // A receptionist gets all patients; a doctor gets only their own patients
  const { data: patients, error } = await supabase
    .from('patients')
    .select('id, full_name, date_of_birth, phone, email')
    .order('full_name')

  if (error) {
    console.error('Error fetching patients:', error)
    return <div>Failed to load patients.</div>
  }

  return (
    <div>
      <h1>Patients</h1>
      <ul>
        {patients.map((patient) => (
          <li key={patient.id}>{patient.full_name}</li>
        ))}
      </ul>
    </div>
  )
}

No manual where user_id = currentUser.id. The database enforces it. This means even if a developer forgets to add a filter in a new query, the data is still protected.

Protecting Routes with Middleware

RLS handles the data layer, but you also want to protect routes at the Next.js level to prevent unauthorized users from even reaching certain pages.

// middleware.ts
import { createServerClient } from '@supabase/ssr'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  let response = NextResponse.next({ request })

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name) { return request.cookies.get(name)?.value },
        set(name, value, options) { response.cookies.set({ name, value, ...options }) },
        remove(name, options) { response.cookies.set({ name, value: '', ...options }) },
      },
    }
  )

  const { data: { user } } = await supabase.auth.getUser()

  // Protect all dashboard routes
  if (request.nextUrl.pathname.startsWith('/dashboard') && !user) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  // Protect medical records routes — receptionists cannot access these
  if (request.nextUrl.pathname.startsWith('/dashboard/medical-records')) {
    const { data: profile } = await supabase
      .from('profiles')
      .select('role')
      .eq('id', user?.id)
      .single()

    if (profile?.role === 'receptionist') {
      return NextResponse.redirect(new URL('/dashboard/unauthorized', request.url))
    }
  }

  return response
}

export const config = {
  matcher: ['/dashboard/:path*']
}

LGPD-Specific Considerations

Beyond RLS, LGPD requires a few additional implementations that are worth highlighting.

Data Export (Right of Access)

LGPD gives patients the right to export all their personal data. I implemented this as a server action that generates a complete PDF of the patient's record:

// app/actions/export-patient-data.ts
'use server'

import { createClient } from '@/lib/supabase/server'

export async function exportPatientData(patientId: string) {
  const supabase = createClient()

  const { data: { user } } = await supabase.auth.getUser()
  if (!user) throw new Error('Unauthorized')

  // RLS ensures this only returns data the doctor is allowed to see
  const { data: records } = await supabase
    .from('medical_records')
    .select(`
      *,
      patients (full_name, date_of_birth, cpf, email, phone)
    `)
    .eq('patient_id', patientId)

  // Pass to PDF generation service (e.g., Puppeteer or PDFKit)
  return generatePatientPDF(records)
}

Audit Logging

LGPD requires you to be able to demonstrate who accessed what data and when. I used a PostgreSQL trigger to log every access to medical records:

-- Audit log table
create table public.audit_logs (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references auth.users(id),
  action text not null,
  table_name text not null,
  record_id uuid,
  created_at timestamptz default now()
);

-- Trigger function
create or replace function log_medical_record_access()
returns trigger as $$
begin
  insert into public.audit_logs (user_id, action, table_name, record_id)
  values (auth.uid(), TG_OP, TG_TABLE_NAME, NEW.id);
  return NEW;
end;
$$ language plpgsql security definer;

-- Attach trigger to medical_records
create trigger medical_records_audit
  after select or insert or update or delete
  on public.medical_records
  for each row execute function log_medical_record_access();

Testing Your RLS Policies

One of the most useful Supabase features for healthcare apps is the ability to test RLS policies directly in the SQL editor by impersonating users:

-- Test as a specific user (replace with actual user UUID)
set local role authenticated;
set local request.jwt.claims to '{"sub": "doctor-uuid-here", "role": "authenticated"}';

-- This query should only return records where doctor_id = 'doctor-uuid-here'
select * from medical_records;

-- Reset
reset role;

This lets you verify your policies are working before shipping to production — critical in healthcare where a misconfiguration isn't just a bug, it's a compliance violation.

Key Takeaways

Building a LGPD-compliant system taught me that security has to be layered:

Database layer (RLS): Enforces access control at the data source — the last line of defense, always active regardless of application bugs.
API/middleware layer: Prevents unauthorized users from even reaching sensitive endpoints.
Application layer: Hides UI elements that users don't have access to, improving UX while adding a third layer of protection.

RLS with Supabase made this layered approach actually practical for a small team. Instead of writing complex authorization logic spread across dozens of API routes, the core rules live in the database and apply automatically to every query.

If you're building anything with sensitive user data in Brazil — or anywhere with strict data protection laws — this combination of Next.js, Supabase RLS, and proper audit logging gives you a solid, auditable foundation without drowning in boilerplate.

João Victor Dumont is a Full Stack Developer based in Rio de Janeiro, specializing in Java, Spring Boot, React, and Next.js. He builds production systems for healthcare and automation, including Taskborne — an AI automation agency for US health practitioners.

DEV Community