How to build a school SaaS: multi-tenant architecture with RLS

Who is the tenant in a school SaaS: the school, the brand or the campus?

In Turkey most private schools are not a single building but a group with several campuses. At ozelokul.com we therefore built a two-level hierarchy: brands (the school group) and campuses linked to it by a foreign key. The tenant boundary is the brand; the campus is a scope inside it.

The link between a person and an organisation lives in memberships: user, brand, role template and permission_overrides for per-tab fine-tuning. Campus scope sits in its own table, membership_campuses. One rule came straight from the design: a membership with no scope rows covers every campus of the brand. Convenient, and dangerous, as shown below.

Subscriptions face the same question: is a plan sold to the brand or to a campus? We did not hard-code the answer; subscriptions carries a scope: brand|campus column and supports both.

There is also a deliberate boundary: teachers are not users in this product. The design has no teacher login, so staff_members is a content record, with a nullable staff_members.user_id column ready in case a teacher panel is ever requested. There is no attendance or grading module; the product covers school profiles, parent requests and school operations.

Why roles should be permission keys, not an enum

The first migrations we inherited had an app_role enum (super_admin|parent_admin|school_admin) and a profiles.assigned_school_slug column that tied a user to a school by string. Decision 2 in the decision log removed both. Adding a role should not need a migration, and campus scope should be a foreign key.

The new model is three tables: permission_keys, role_templates and overrides on the membership. Effective permission is template permissions plus overrides, intersected with campus scope. The seed ships 7 templates: group admin, campus principal, admissions office, marketing, accounting, front desk, and a super admin template for the platform side.

There were 47 permission keys at the first install; today there are 56:

• 13 school panel tabs (such as veri, kampus, teklif, mesaj, yetki, fatura)
• 22 content sections (s1–s22)
• 3 publishing actions (content.publish, content.rollback, content.schedule)
• 18 platform permissions (such as platform.billing, platform.impersonate)

Every check goes through one function, private.has_perm(user, perm, brand, campus), and the order matters: a deleted account is always false, a platform admin is true, then brand and campus consistency, an active membership, the permission list and finally campus scope. Parents are simply account_type = parent with no membership, and the sign-up trigger grants nobody any privilege.

How tenant isolation is built in RLS, and where it breaks

Every table is born with RLS; application checks are the second line. Installing 67 migrations from scratch in file order, gave 149 tables, RLS enabled on 148 of 148, and 296 policies. Today there are 139 migrations.

We still hit three traps:

• EXECUTE grants. We revoked every function in the private schema from authenticated, but RLS expressions run with the privileges of the querying role. Every policy failed with permission denied for function can_campus, and the school panel and parent side were completely dead. The fix: EXECUTE only on the 9 functions referenced by policies.
• Policy recursion. Looking at membership_campuses from inside memberships_read made Postgres fail with 42P17: infinite recursion detected in policy. The rule: a policy that reads another RLS table must do so through a SECURITY DEFINER function.
• Inconsistent arguments. When both brand and campus were passed, has_perm did not check that the campus belonged to the brand. No call site used that form, but we fixed it anyway. An inconsistent pair now returns false.

The "empty scope means every campus" rule bit us once principals could delegate: the membership update ran in three steps, and between them the missing scope row briefly widened it to the whole brand. Now save_membership() writes membership and scope in one transaction, and nobody can grant outside their own scope or grant a permission they do not hold.

Why the free and premium difference has to reach RLS

There are three plans: Basic Listing (the free default), Growth, and Showcase & Premium. A plan is not an if (plan === "premium") in the code but rows in plan_features: gallery_max_photos is 5 on Basic and 40 on Growth; multi_admin_seats is 1 on Basic and 5 on Growth. Of Basic’s 20 feature keys, two are numeric limits and eighteen are disabled flags.

Whether a section appears on a public school profile is decided by one function, private.section_visible(campus, section). It requires three things together: the campus is published, the section is published and active, and the feature the section depends on is enabled in the plan. A locked section never comes back from the query at all. Hiding it in the front end would still ship the data to the browser.

In messaging this principle closed a gap. The plan said a free school cannot receive messages, yet the conversations policy only checked the two parties, so a parent could open a conversation with a free campus. private.has_feature(campus_id, 'messaging') was added to the with check clause. Reading was deliberately left open: if a school drops its plan, both parties still see the history; only opening a new conversation is blocked.

A mutation test proves it: deleting the plan branch from section_visible turned the entitlement suite red on 2 assertions.

RLS protects rows, not columns: how parent data is masked

A SELECT policy opens every column of a row. Schools must see the parent quote pool but not a parent’s contact details until revealed. The answer is a view, not a policy: schools get no SELECT on the base table and only query quote_requests_for_school, where contact columns are nulled with case when contact_revealed. The hidden-profile view does not include parent_id at all. Reviews use column-level grants: the verification code and moderation note are readable by no API role.

On the write side the same problem was sharper. During the authorisation audit we showed with a live attempt that a "users may update their own profile" policy opens the whole row: a parent account could set its own account_type to platform with one PATCH request. Five such places were found and closed with two layers:

• Column grants: UPDATE on profiles was revoked and only full_name, phone, avatar_url, locale, marketing_opt_in, last_seen_at were granted back.
• A trigger that rejects changes to privileged columns even if the grants are widened later.

The second layer earned its place: in the first fix the triggers were mistakenly declared security definer; in that context current_user returns the function owner, so the check always passed. profiles stayed protected anyway because the column grants held. The rule: fields like role, status, verifier and processed-at are never declared by the user, they are derived.

What KVKK needs in the schema: consent records, versioned texts, anonymisation

KVKK (Turkey’s data protection law) compliance is a traceable chain in the database, not a cookie banner. There are three pieces.

consent_records: for every form consent, the user or anonymous session id, the legal_text_id of the text agreed to, the context (hidden profile, quote, appointment, newsletter, contact, advisory, application, info request), a timestamp and a revocation time. A check constraint forces every record to belong either to a user or to an anonymous session.

legal_texts: (key, version) is unique, with a single is_current per key. A verification round found that a platform admin could edit a published text in place. Consents would then silently point at different content; now a published text cannot be changed by anyone, admins included, and only a new version can be published.

anonymize_user(): when an account is deleted, rows are not deleted but personal fields are cleared. The profile name becomes "Deleted user"; phone and neighbourhood on the parent profile and the name and special-needs note on the child record are wiped; favourites and comparisons are removed. The request record lists the 12 tables touched.

Anonymous forms hide one more Postgres detail: .insert().select() produces RETURNING, and RETURNING is subject to the SELECT policy. Because anon had no SELECT, every logged-out form was failing. We now generate the id on the server with crypto.randomUUID() and skip the read-back; granting SELECT would have let visitors read each other’s requests.

What turned out to be specific to the Turkish market

Location: 81 provinces and 973 districts are seeded, as foreign keys rather than text. The taxonomy has 411 terms and new ones can only be created from the platform panel. If a school enters a tag that is not in the list, publishing is blocked with a Turkish error instead of the tag being silently dropped.

Search: ilike does not handle Turkish. Searching kadikoy returned 0 results and kadıköy returned 1; anyone typing without diacritics found nothing and saw no error. A trigger-maintained search_text column with a trigram index was added, and the search term is normalised by the same rules.

PDF: jsPDF’s built-in Helvetica lacks ı, İ, ğ, Ğ, ş and Ş. Roboto was subset to the Turkish ranges, from 515 KB to 39 KB per style, and embedded; the library loads only on download.

Payments: there is no payment provider, by client decision. The flow is built on bank transfer (havale/EFT): the platform issues an invoice, the school reports the payment from its panel, and admin approval marks the invoice paid, activates the subscription and writes an audit record in one transaction.

Parent notifications: the queue is channel-agnostic (in_app, email, sms, whatsapp, push). No SMS or WhatsApp provider has been chosen yet; an unconfigured channel is not an error, the record simply stays queued. The dispatcher works in batches of 100 and tries a record at most 5 times.

How to test multi-tenant authorisation

Today supabase/tests/ holds 33 pgTAP files with 560 planned assertions in total: anonymous reads, plan differences, campus scope, masking, audit log, privileged columns, delegation and the application flow.

Two habits make them meaningful. First, test the test: the first two assertions of the privileged-column suite check that current_user = authenticated and that the privileged-writer check returns false. As a superuser, RLS is bypassed and everything passes for nothing. Second, mutation testing: deliberately breaking a policy and watching the suite turn red. Removing campus scope from has_perm failed the scope suite on 5 assertions.

Database tests do not cover server functions, so 80 mutation endpoints were mapped to their gates (28 behind the platform-admin gate, 20 behind campus permission) and 30 endpoints went through live exploit attempts with real JWTs. Four findings were exploited, among them an unmatched campus being able to answer a quote request and an invitation token not bound to the invitee’s email. All four were closed in both code and migrations.

Finally, the deployment itself is tested: migrations that had been applied incrementally were installed into an empty database in file-name order, and two release blockers that never showed up on the running database came out. Delivery means it installs from scratch.

A checklist for anyone building a similar school SaaS

What carries over from ozelokul.com:

• Make the tenant the organisation (brand) and the campus a foreign-keyed sub-scope. If you use an "empty scope means all" rule, write scope changes in one transaction.
• Define roles as permission keys and templates; route every check through one has_perm function and validate that its arguments are consistent with each other.
• Define plan differences as plan_features rows and enforce them down to RLS. Locked data should never reach the browser.
• Grant EXECUTE to the querying role on every function a policy references; wrap policies that read another RLS table in a SECURITY DEFINER function.
• For any "edit your own row" policy, review every column. Protect privileged columns with both column grants and a trigger.
• Mask sensitive columns such as parent contact details with views.
• For KVKK, link consent records to versioned, immutable legal texts; anonymise on account deletion and record which tables were cleared.
• For Turkish search, use a normalised column with a trigram index; if you generate PDFs, embed a font that has the Turkish characters.
• Verify inside the pgTAP tests which role they run as, run mutation tests, and reinstall the migration set from scratch on every change.

At AMG Software Studio we build multi-tenant SaaS products like this end to end, from the schema to the authorisation audit.