Developer DocsDatabase & RLS

Database & RLS

The database is a self-hosted PostgreSQL (via Supabase). It is the enforcement point for multi-tenant isolation.

Conventions

  • Every table: id UUID PRIMARY KEY DEFAULT gen_random_uuid().
  • Every brand-scoped table: tenant_id UUID NOT NULL and brand_id UUID NOT NULL referencing brands(id).
  • Mutable tables: created_at + updated_at with an auto-update trigger.
  • Immutable streams (player_events, balance_transactions): created_at only — never updated or deleted.
  • RLS policies always include tenant/brand isolation.
  • Migrations are sequential, in supabase/migrations/, named NNN_description.sql, and added to the SQL test harness (supabase/tests/run.sh).

Migrations

Migrations are plain SQL applied in order. New ones are written to be idempotent where practical (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, CREATE OR REPLACE FUNCTION, DROP POLICY IF EXISTS before CREATE POLICY) so re-applying a batch is safe.

After a schema change, regenerate the DB types:

supabase gen types typescript --local > packages/db/src/types.ts

Row-Level Security

RLS is the core of isolation. Rather than repeating tenant/brand subqueries in every policy, policies call SECURITY DEFINER helper functions:

HelperReturns
auth_tenant_ids()Tenant ids the current user can see (all, for super_admin).
auth_brand_ids()Brand ids the current user can see (all, for super_admin).
auth_effective_permissions(user,brand)The user’s effective permissions on a brand.
auth_has_permission(perm, brand)Whether the user holds perm on a brand.
auth_has_tenant_permission(perm,tenant)Whether the user holds perm in a tenant (super-aware).

A typical policy:

CREATE POLICY "Users can view assigned brands' players"
  ON players FOR SELECT
  USING (brand_id IN (SELECT auth_brand_ids()));

Write policies additionally check a permission, e.g. WITH CHECK (auth_has_permission('players:write', brand_id)).

All helpers set a locked search_path and are SECURITY DEFINER so they can read the RBAC tables regardless of the caller’s own RLS.

The Platform Owner

auth_tenant_ids() / auth_brand_ids() return everything for a super_admin, and auth_has_tenant_permission short-circuits true for them — so the Platform Owner operates cross-tenant through the normal RLS-scoped app, with no separate admin bypass. See ADR-013 and RBAC & permissions.

Testing the data layer

supabase/tests/run.sh applies every migration + seed to a clean Postgres and runs SQL assertions for brand isolation, brand scoping, write permissions, RBAC, and per-user overrides — no Docker/Supabase stack required. Run it against a throwaway database:

DATABASE_URL=postgresql://user@host:port/db supabase/tests/run.sh

Add each new migration’s filename to the MIGRATIONS list in that script.

Monorepo & appsData model