The problem
Zorg Intelligence is care administration software for self-employed carers and small care providers in the Netherlands. All organisations share one application and one database. The data is sensitive: client records, care plans and the citizen service number (BSN) of clients and staff.
That gives three requirements that cannot depend on application code. One organisation must never see another’s rows. A BSN must not sit in the database as plain text. And it must be possible to answer the question “who opened this record?” afterwards.
The approach
The stack is Next.js with Supabase (Postgres, Auth, Row Level Security and Storage). The rules are in the database, so a list, a search, a report and a CSV export all pass through the same gate.
Row Level Security. Every table in the public schema has RLS on, and every row belongs to exactly one organisation. A user is linked to an organisation through a membership with one of four roles: owner, admin, staff or reader. The policies do not repeat that logic. They call a few small helper functions (is this user a member, does this user have one of these roles, may this user write) that read the memberships of the signed-in user. A reader writes nothing.
Details that turned out to matter:
- Foreign keys to a table with an organisation column are composite, so a reference cannot point at a row of another organisation.
- In policies, the call that returns the current user is wrapped in a subquery. Postgres then computes it once per query instead of once per row.
- An organisation can switch on caseload restriction. Staff and readers then only see the clients assigned to them. Because that lives in RLS, it applies to every screen and export without extra code.
- The platform operator (Roosterspecialisten) sees no care data. Looking along for support only works with temporary consent from the organisation itself. Such a session is read-only, limited in time and logged.
To keep this from eroding, the repository has a fingerprint of all policies and functions with a test that compares against it, plus a repeatable isolation test with 15 scenarios.
Encrypted BSN. The BSN is encrypted per field with pgcrypto. The key comes from Supabase Vault and is not in the code or in git. A trigger encrypts the value on insert and update, keeps the last four digits for a masked display and clears the plain text column. The application never gets the full number from a normal query. It has to call one function that checks membership, writes the access log line first and only then decrypts.
Access log. The first version wrote a “viewed” line into the general audit log. In the current schema it is a table of its own with the time (taken from the database), organisation, person, role at that moment, client, kind of object, action, a count for exports and searches, and the session. It never holds free text, a search term, a file name or an IP address.
There is one way in: a function that checks for itself whether the caller may see the record, and derives the organisation and the client from the record instead of from its parameters. Lines cannot be changed or deleted, also not with the service role. The only exception is the cleanup after the retention period of 5 years. The log fails closed: if a line cannot be written, the app shows nothing, and for a BSN, a download or an export the server only returns data once the line exists. Owners and admins read all lines of their organisation, staff only their own.
The trade-offs
- The app logs, the database checks. Someone who reads the database API directly with their own valid token still only gets the rows RLS allows, but leaves no access line. The BSN is the exception, because it always goes through the database function. The client list itself (names) is not logged.
- No copy of the log outside the database yet. It is an open point.
- Helper functions run with elevated rights, otherwise a policy could not read the memberships table. Each one is small and listed in the fingerprint.
- Two-step verification is optional by default. Every user can turn on an authenticator app, and an owner can make it mandatory for the whole organisation. When it is mandatory, support sessions are not possible.
- No certificate. These are technical measures. The product is not NEN 7510 certified and an external penetration test still has to be done.
- Policies on every table cost time per migration: each new table needs its policies, a trial run and an updated fingerprint.
More about the product is on the Zorg Intelligence project page.