TLDR: Supabase auto-enables RLS on every new table. Without a policy, Postgres returns zero rows — silently. The fix is one line, but only after you've stopped blaming your sync.

What We Were Building

An ecommerce business OS supply chain app tracks inventory lot movements — when product arrives, how it depletes, what's left.

We needed a dedicated table for that: lot_transactions.

New migration, CREATE TABLE, the usual routine.

The Wall

Queries started coming back completely empty.

Not an error. Not a warning. Just… nothing.

{ data: [], error: null } — that's what supabase-js (the Supabase JavaScript client) handed back. Clean as a whistle.

The Wrong Rabbit Hole

So naturally I went chasing the data.

Had the sync even run? Were rows actually getting written? Was the Shopify/Recharge ingest broken?

Checked the ingest logs. Checked the upsert logic. Checked everything except the obvious thing.

That was the expensive part.

The Actual Problem (Two Silences, Stacked)

Here's what was actually happening.

Supabase auto-enables Row-Level Security (RLS, a Postgres feature that filters which rows a query can see) the moment CREATE TABLE runs. Not when you ask it to. The instant the table exists, pg_class.relrowsecurity flips to true.

With RLS enabled but NO policy in place, Postgres defaults to deny-all.

Every row. Every query. Gone.

And supabase-js doesn't raise an error for this. It just returns an empty array.

Two silences stacked on each other: the auto-enable you didn't see, and the error that never came.

The Fix That Worked

Migration 039/040: explicit RLS policy on lot_transactions granting the authenticated role SELECT access on rows matching the appropriate condition.

One migration. Fixed immediately.

While I was in there, I also noticed the opposite problem on some existing functions — too open instead of too locked. Migration 043, the next day: revoked the blanket EXECUTE grants that had been handed to the anon and authenticated roles on functions that never needed them. Same migration audit, opposite failure mode.

The Pattern (Write It Down)

Every new table in a Supabase project that serves PostgREST-exposed data needs this right after CREATE TABLE:

  1. ALTER TABLE "your_table" ENABLE ROW LEVEL SECURITY; — explicit, even though Supabase already did it automatically. Makes the intent visible in the migration file.
  2. A policy. Even a permissive FOR ALL USING (true) placeholder if you're still designing access rules. No policy = deny-all.
  3. A UI check, not just SQL. A SELECT * in the editor runs as the table owner and bypasses RLS. Open the actual app page. If it shows data, you're good. If not, now you know where to look.

The Supabase linter flags rls_disabled_in_public as an ERROR — but it won't catch the inverse: RLS enabled with no policy. That one's on you.

Don't let it cost you an hour of sync debugging. It's just a missing policy.

P.S. If you're building on Supabase: treat "CREATE TABLE → add policy" as one atomic unit. They're not two separate steps. They're one step that happens to be written in two lines.