Testing Supabase access rules as real users

Sign in as real seeded users against a local Supabase and assert what each one can and cannot read or change. Use the service role only to arrange data, never to check visibility. Make the suite refuse to run against anything but localhost. Then finish with one end-to-end test through the path that makes the business money.

This is the last part of the build log of the ZS Tech CRM. Part 2 hid cost and margin with grants and views, and part 4 built per-module access. Both are only as good as the proof that they hold.

Three layers, 44 tests

The proportions are deliberate. Money maths is where bugs are cheap to catch and expensive to miss, so the calculation libraries were written test first. The database rules are few but each one guards a real promise to the business. One end-to-end run proves the pieces connect.

Refuse to run anywhere but local

An integration test creates and deletes deals. Pointed at production by a wrong environment file, it would do that to real clients. So the helper that every database test imports checks the URL before anything else:

const url = process.env.NEXT_PUBLIC_SUPABASE_URL!

if (!/127\.0\.0\.1|localhost/.test(url)) {
  throw new Error(`RLS tests only run against a local stack, got ${url}`)
}

It is two lines, and it turns a dangerous mistake into a loud error. The same project also keeps the default environment pointed at the local stack and copies the cloud settings in only when deploying.

Assert as the user, arrange as admin

There are two kinds of client. asUser() signs in with a seeded login through the public key, exactly like the browser does, so every policy applies. admin() uses the service role, which bypasses RLS entirely. The rule is strict: admin may create fixtures and clean up, but every assertion about what someone can see goes through asUser(). A test that checks visibility with the service role proves nothing.

export async function asUser(email: string, password = "password") {
  const c = anonClient()
  const { error } = await c.auth.signInWithPassword({ email, password })
  if (error) throw error
  return c
}

The seed, which only ever loads into the local database, creates an owner and a technician whose access is View on Pipeline and Edit on Projects, with no Money access.

What the access tests prove

it("tech (no money) sees deals but null cost/margin, no payments, no product_sources cost", async () => {
  const t = await asUser(TECH)

  const tot = await t.from("v_deal_totals").select("net, cost, margin").gt("net", 0).limit(1).single()
  expect(Number(tot.data?.net)).toBeGreaterThan(0)   // sees the deal's value
  expect(tot.data?.cost).toBeNull()                    // but not its cost
  expect(tot.data?.margin).toBeNull()                  // or margin

  const pay = await t.from("payments").select("id")
  expect(pay.data).toEqual([])                         // no payment rows at all

  const src = await t.from("product_sources").select("unit_cost").limit(1)
  expect(src.error).not.toBeNull()                     // the raw column is revoked
})

Read that test as a list of promises. The technician sees the deal's value, because they need it on site. They do not see its cost or margin. They see no payments. And asking the raw table for the cost column is an error, not an empty field, because the grant is gone. The mirror test signs in as the owner and checks that the same queries do return cost and payments, and a third checks that an anonymous client sees nothing anywhere.

Business rules are tests too

• Marking a deal lost without a reason fails with "lost reason required"; with a reason it succeeds; reopening clears the reason.
• A user with only View on Pipeline cannot move a deal: "no access to pipeline".
• Marking a deal won creates five project tasks, a deposit and a final instalment with due dates, and a quote number matching Q-YYYY-NNN.
• Handing over a project with open tasks fails with "finish all tasks before handover".
• Changing a product's preferred supplier re-prices lines on open deals and leaves every won deal's cost untouched.

That last one is the rule from part 1 that protects project profit. It loops over every line for the product and checks each one against its deal's stage, so a regression on any single deal fails the run.

One run through the money path

The end-to-end test does what a salesperson does on a good day: signs in, creates a contact, creates a deal from the contact page, adds a seeded package and checks it expanded into six lines, marks the deal won and checks the five tasks appeared, records a payment, and opens the dashboard. If any layer between the button and the database is broken, this is where it shows.

Rules that keep the suite honest

• Database test files run one at a time, because they share one database.
• Tests clean up the rows they create; the seed is the baseline every other test assumes.
• The end-to-end run does create data, so the database is reset when the seed matters again.
• Typecheck, lint, unit tests, and the database tests are green before every commit.

What this series built

Five parts, one system: a quote model whose costs freeze when a deal is won, cost and margin hidden by the database, a money model that keeps the plan and the payments apart, per-module access generated from one helper, and these tests that prove it. The finished product is the ZS Tech CRM. If you want the same for your company, start on the CRM and ERP page.