R3 — Supabase Multi-Tenancy for the WaaSel BFF

Historical (pre-rebrand, 2026-10): written when the product was called WaaSel and the hosts were waasel.* / bouskoura.online. The product is now WAFINE: production hosts wafine.zawia.tech, api.wafine.zawia.tech, docs.wafine.zawia.tech; bouskoura.online is the testing playground. Names and hosts below are kept as written.

Status: research artifact · Date: 2026-10-08 Repo: openwa-oci-infra @ mo/e2e-encryption-scaffold Design under review: docs/04-e2e-encryption.md §7 (Database schema) and §4.2 (Request flow) Supabase project: ai4xedu org, region eu-west-3 (Paris), linked locally (supabase/.temp/) Deployment target: arm64 Docker image on OCI Ampere A1, connecting over the Supavisor session pooler (TLS 5432) with stack/certs/supabase-ca.crt

Scope note. The design puts a new schema waas alongside OpenWA's existing openwa schema in the same Supabase project (docs/02-architecture.md decision #2/#3). Everything below is written against that fact: one database, two application schemas, one Postgres instance, one shared Supavisor pooler.

0. Executive summary (the answers, compressed)Copy Anchor Link

1. Tenant isolation: RLS-with-JWT-claim vs schema-per-tenant vs app predicatesCopy Anchor Link

1.1 What Supabase's own docs say the controls areCopy Anchor Link

Supabase's security model has two independent layers, and the docs are emphatic that they are independent:

"Postgres runs two checks before a client touches a table. Grants decide whether a role can run an operation on the table at all. Policies decide which rows that operation applies to. Set both for every table you expose." — Row Level Security

"Grants control whether a role can access an object. RLS controls which rows the role can access." — Securing your API

The critical framing sentence, which decides this whole question:

"RLS is a Postgres primitive, it also protects your data when it is reached through third-party tooling, which is what makes it defense in depth." — Row Level Security

RLS is a defense-in-depth control for the case where untrusted clients reach Postgres directly. Its value is proportional to the number of distinct roles touching the data. In this design there is exactly one DB client (the BFF) and it holds the only credentials.

1.2 (a) RLS with a tenant_id claim in the JWTCopy Anchor Link

How it works in Supabase. A JWT carries claims; auth.jwt() exposes them in a policy:

create policy "tenant isolation" on waas.messages
  for select to authenticated
  using ( tenant_id = ( (select auth.jwt()) ->> 'tenant_id' )::uuid );

The docs' canonical multi-tenant pattern is exactly this shape, using app_metadata (not user_metadata, which the user can mutate):

"raw_app_meta_data — cannot be updated by the user, so it's a good place to store authorization data." "Keep in mind that a JWT is not always up-to-date. … even if you remove a user from a team and update the app_metadata field, that will not be reflected using auth.jwt() until the user's JWT is refreshed." — Row Level Security

Why it does not fit here. Three structural mismatches:

1. The design explicitly rejects Supabase Auth. docs/04-e2e-encryption.md §4.3: "The BFF uses custom auth — not Supabase Auth." There is therefore no Supabase-issued JWT, no authenticator role switch, and no auth.uid(). auth.jwt() reads request.jwt.claims, which is a PostgREST-set GUC — it is not populated on a raw libpq connection from a container. So a policy written against auth.jwt() would evaluate to NULL and deny everything (or, worse, be written as coalesce(...) and allow everything). UNVERIFIED: whether Supavisor forwards request.jwt.claims set by the client — it does not, because that GUC is set by PostgREST's connection pool, not by the pooler.

2. JWT staleness is a real revocation gap. The docs warn the claim is only as fresh as the token. With 15-minute BFF JWTs (§4.3 step 3), a revoked tenant membership stays valid for up to 15 minutes at the database layer, where you cannot intervene.

3. The BFF would need to impersonate. To make RLS-with-claims work over a direct connection you would have to SET LOCAL request.jwt.claims = '...' per transaction and SET LOCAL ROLE authenticated. That is a hand-rolled reimplementation of PostgREST's connection handling, and it puts the tenant claim in a session GUC that a buggy code path can forget to set. A forgotten SET does not fail closed — it fails open if the policy is written permissively, or fails closed (500s) if written strictly.

Where RLS-with-claims would be correct: if the Next.js frontend talked to Postgres through the Data API with a publishable key. It does not — §2 of the design routes everything through the BFF.

1.3 (b) One schema per tenantCopy Anchor Link

Supabase supports custom schemas (Using Custom Schemas) but the docs describe them as a single extra schema (myschema), exposed via API settings. There is no per-tenant schema provisioning primitive.

Concrete problems for this project:

• Supavisor pool sizing is per user+database+mode. "Pool size refers to the maximum number of direct connections the pooler can maintain per unique user, database, and mode combination." (Supavisor FAQ) A schema-per-tenant design does not create new pool combinations if all tenants share one role — but if you gave each tenant its own role (the natural pairing), you would multiply the pool budget by the tenant count and blow the compute size's connection ceiling.
• Migrations fan out linearly. supabase db push applies a timestamp-ordered list of files to one database (Database Migrations). N tenants ⇒ N× DDL, and CREATE TABLE/ALTER TABLE lock acquisition per schema. The docs already warn about lock contention: "If a lock timeout error occurs, in your migration file, consider increasing your lock_timeout setting."
• Cross-tenant queries become impossible. The design needs tenant_themes lookup by slug (§5.2) and an audit log spanning tenants (§7 audit_log). Both become N-way UNION across schemas.
• Connection search_path becomes tenant-dependent state. The existing openwa migration pins search_path on the role (alter role openwa set search_path = openwa, public) precisely because "pinning it on the role as well keeps raw migration DDL in openwa even if a pooler drops that parameter." Per-tenant schemas would require per-tenant roles to pin per-tenant search_path — reintroducing the pool-multiplication problem.
• The Data API exposure list is global. Exposing N schemas in API settings is an N-entry allowlist that must be kept in sync with tenant creation. One missed removal is a cross-tenant read path.

Verdict: rejected. Schema-per-tenant is the right tool when tenants need physical isolation (different retention, different encryption keys at rest, regulatory separation). Here the tenants already share a Supabase project and a pooler, so the isolation is cosmetic while the operational cost is real.

1.4 (c) Application-enforced predicates + least-privilege role — the fitCopy Anchor Link

This is what the design already does implicitly, and it is the correct choice. Make it explicit and auditable.

The argument. A BFF-as-single-chokepoint changes the RLS calculus in one specific way: RLS's stated benefit — "protects your data when it is reached through third-party tooling" — is only realised if third-party tooling can reach the data. If the waas schema is:

• not in the Data API's exposed schemas,
• granted to no role except waas_bff,
• and waas_bff's credentials exist only inside the container,

…then there is no second path to defend. The threat model collapses to "the BFF's own code has a bug", and RLS cannot fix a bug in the layer that supplies the tenant ID to the query in the first place. If the BFF passes tenant_id from a request header into the WHERE clause, an RLS policy reading the same header-derived value is a tautology.

What the docs support here. Supabase is explicit that direct connections are a first-class, differently-shaped security model:

"Direct database connections: Connect to Postgres with a connection string from trusted servers, workers, or tools. Keep database credentials secret and use the right connection method for your environment. You can disable the Data API if your app only uses direct connections." — Securing your data

And on roles:

"Generally you wouldn't use these roles for your own application — they are mostly for configuring system access to your database. If you want to configure application access, then you should use Row Level Security (RLS)." — Postgres Roles

That last sentence is about the built-in anon/authenticated/service_role roles. A custom role is the documented escape hatch: the docs recommend "create a new user for every service that you want to give access too. This will also help you with debugging — you can see every query that each role is executing in your database within pg_stat_statements." (same page).

The concrete design. Enforce tenant scoping in three places, in this order:

1. Type system (compile time). Every repository method takes a TenantId value object, not a raw string. The Drizzle schema declares tenant_id as not null on every tenant-scoped table. A query that forgets the predicate is a type error, not a runtime leak.

2. Repository layer (runtime, single place). One scoped() query builder wraps every table access and injects eq(table.tenantId, tenantId). This is the chokepoint — the BFF's hexagonal infrastructure/db/repositories/ directory (§4.1).

3. Database (defense in depth). ENABLE ROW LEVEL SECURITY on every waas table with no policies. Per the docs: "Once RLS is enabled, no data is accessible through the API when using a publishable key, until you create policies." (RLS). With RLS enabled and zero policies, the table is readable only by a role with BYPASSRLS or by the table owner. This means: if someone later exposes waas in the Data API by mistake, or grants anon/authenticated by accident, the tables are still dark.

   Caveat, and it matters: the table owner bypasses RLS unless FORCE ROW LEVEL SECURITY is set. If waas_bff owns the tables, RLS-with-no-policies does nothing for it. Two options: (i) own the tables as postgres and grant waas_bff only DML, or (ii) own them as waas_bff and add ALTER TABLE ... FORCE ROW LEVEL SECURITY. UNVERIFIED: whether Supabase's supautils extension blocks FORCE ROW LEVEL SECURITY on managed projects — it is not a superuser-only command per the Postgres docs, but Supabase restricts some DDL. Test it before relying on it.

Recommended policy shape if you later add a second reader (e.g. a read-only analytics role). Use the to <role> clause and wrap the function in select:

create policy "waas_bff reads own tenant" on waas.messages
  for select to waas_bff
  using ( tenant_id = ( select current_setting('waas.tenant_id', true) )::uuid );

The docs mandate both of these micro-optimisations:

"Always name the role a policy applies to, using the to clause. … This prevents the policy … from running for any anon users, since the execution stops at the to authenticated step." "Wrapping the function causes an initPlan to be run by the Postgres optimizer, which allows it to 'cache' the results per-statement, rather than calling the function on each row." — Row Level Security

And index the filter column — the docs call this a rule that "belongs with the policy itself":

"A column counts as indexed only when it comes first in a btree index. Postgres can't use a multi-column index to filter on a column that isn't the leading one." — RLS, RLS performance

The design's idx_messages_tenant_sent ON messages(tenant_id, sent_at DESC) (§7) already leads with tenant_id. Good — keep that invariant for every tenant-scoped index.

1.5 Decision tableCopy Anchor Link

Chosen: (c), with RLS enabled-and-policyless as the backstop. Document the residual risk honestly: a bug in the BFF's scoped() builder is a cross-tenant read. That is the price of the single-chokepoint architecture, and it is the same price every BFF pays. Mitigate with a pgTAP test that asserts cross-tenant reads return zero rows (see §1.6).

1.6 Test the invariant, don't assume itCopy Anchor Link

The docs are unusually direct about this:

"Create a .sql file under supabase/tests/ that asserts allow and deny for select, insert, update, and delete … Until the suite passes, you don't know whether the policies do what you intended." — Row Level Security

Run with supabase test db (pgTAP). For this design the highest-value test is not an RLS policy test — it is a repository-layer test: seed two tenants, call every use case with tenant A's context, assert zero rows of tenant B's data appear. Put it in the BFF's integration suite (testcontainers, per §9 Phase 2), because that is where the enforcement actually lives.

2. Session pooler (5432) vs transaction pooler (6543)Copy Anchor Link

2.1 What Supabase's docs say the difference isCopy Anchor Link

The current docs page is unambiguous about which mode a long-lived container should use:

"A persistent backend on an IPv4-only network → Shared pooler, session mode. The shared pooler is IPv4-only on every plan." "Direct connection … Use it for persistent backends, such as virtual machines (VMs) and long-running containers." — Connecting to your database

And the FAQ:

"When deploying to static architecture, such as long-standing containers or VMs, application-side poolers are satisfactory on their own." "In session mode, once the pooler assigns a direct connection, it stays with that client until voluntarily surrendered. This behavior mirrors a direct connection… the primary benefit is that it is IPv4 compatible. Also, unlike transaction mode, it supports prepared statements." — Supavisor FAQ

2.2 What transaction mode breaks — the docs' own listCopy Anchor Link

From Connecting to your database § Transaction mode limitations:

"Transaction mode returns your connection to the pool after each transaction, so anything that depends on session state doesn't survive between transactions. Four things are affected."

That last row is the sharpest one for this stack: the BFF is Bun + Hono + Drizzle, and Drizzle's documented Supabase setup uses postgres.js. The docs' own Drizzle quickstart carries the comment:

// Disable prefetch as it is not supported for "Transaction" pool mode export const client = postgres(connectionString, { prepare: false }) — Connecting with Drizzle

So even the supported transaction-mode path requires disabling two features the driver enables by default.

2.3 Why session state is non-negotiable for this designCopy Anchor Link

The design's §7 schema and §4.2 request flow imply session-scoped state in at least three places:

1. search_path. The existing openwa migration pins it on the role (alter role openwa set search_path = openwa, public) and passes it via libpq options — the comment says the role pin exists "even if a pooler drops that parameter". Supavisor in transaction mode is exactly the case where a startup parameter can be lost, because the client's connection is not the server's connection. The waas schema needs the same treatment.
2. SET LOCAL for RLS claim propagation (if you adopt §1.2's hybrid). Transaction mode scopes SET LOCAL correctly, but session GUCs set outside a transaction are lost. Mixing the two is a footgun.
3. Advisory locks for key-rotation jobs. §3.5 and the key_rotation_jobs table describe a resumable, single-writer job. A session-level advisory lock (pg_advisory_lock) is the natural guard. The docs list it as broken in transaction mode.

Additionally: LISTEN/NOTIFY. If the BFF ever wants a lightweight cross-replica signal (e.g. "tenant X's KEK rotated, drop the cache"), that is a session-level feature. The design's §4.3 uses a 5-minute TTL cache instead, so this is a future concern — but it is a one-way door, because adopting transaction mode forecloses it.

2.4 The cost of session mode, honestlyCopy Anchor Link

Session mode is not free. From the FAQ:

"In session mode, once the pooler assigns a direct connection, it stays with that client until voluntarily surrendered. This behavior mirrors a direct connection, allowing greedy clients to monopolize the pool." "In session mode, it will immediately create a new direct connection for a new client unless the 'Pool Size' is reached." "In session mode, the connection will be immediately closed." (when the client disconnects)

So each BFF replica holds pool_size backend connections permanently. With OPENWA_MEM_LIMIT=8g on a 12 GB A1 and OpenWA already consuming connections, the budget is tight. The FAQ's guidance:

"if you're using the DB REST API or multiple app-based 'user+db+mode' combinations, try to keep the pooler's usage under 40% of available connections. Otherwise, you can cautiously increase usage to around 80%."

Sizing recommendation for the BFF: application-side pool max: 5 per replica, Supavisor pool size left at the project default, and one BFF replica on the A1 initially. The docs' rule of thumb for a persistent backend is that the application-side pooler is enough on its own — Supavisor session mode is there for the IPv4 reachability, not for multiplexing.

2.5 Why not the direct connection?Copy Anchor Link

The direct connection is the docs' first recommendation for persistent backends, but it is IPv6-only unless the project has the IPv4 add-on:

"Direct connection db.[PROJECT-REF].supabase.co:5432 — Free: IPv6, Paid: IPv6, Paid + IPv4 add-on: IPv4" "The IPv4 add-on is not dual-stack: enabling it swaps the project's IPv6 (AAAA) DNS record for an IPv4 (A) record, so the project endpoint becomes reachable only over IPv4." — Connecting to your database

The OCI VM's security list has no inbound TCP/UDP and the VM is IPv4-only in practice (docs/02-architecture.md). docs/02-architecture.md decision #4 already records this: "OCI VMs are IPv4, and direct DB hosts are IPv6." So the direct connection is out unless you buy the IPv4 add-on, which then makes the endpoint IPv4-only — a one-way migration with no dual-stack fallback.

Verdict: session pooler on 5432, sslmode=verify-full with stack/certs/supabase-ca.crt. This matches the existing OpenWA decision and keeps one connection story for the whole VM.

2.6 TLS detail worth pinning downCopy Anchor Link

The docs are explicit that require is not enough:

"require encrypts the connection but doesn't verify the server, so it doesn't stop a man-in-the-middle attack. To verify as well as encrypt, download your server root certificate … with sslmode=verify-full and sslrootcert." — Connecting to your database

stack/preflight.sh already does openssl s_client -starttls postgres -CAfile "$CA" -verify_hostname "$DATABASE_HOST" -verify_return_error. Reuse that check for the BFF's DATABASE_URL in the same preflight. Note that postgres.js maps sslmode=verify-full to ssl: { rejectUnauthorized: true, ca: <pem> } — UNVERIFIED whether postgres.js parses sslrootcert from a URL string; pass the CA as an explicit option instead of relying on URL parsing.

3. Which role should the BFF connect as?Copy Anchor Link

3.1 service_role / secret key: what it actually isCopy Anchor Link

The docs define it precisely:

"service_role — For elevated access. This role is used by the API (PostgREST) to bypass Row Level Security." — Postgres Roles

"Secret keys authorize access to your project's data through the built-in service_role Postgres role. By design, this role has full access to your project's data. It also has the BYPASSRLS attribute, so it skips every Row Level Security policy you attach." "A leaked secret key exposes all of your project's data." — API keys

Two things follow, and both disqualify it:

1. BYPASSRLS is global, not per-schema. It skips every policy in the database. That includes any RLS you ever add to openwa, storage.objects, or auth. The waas schema's RLS backstop (§1.4) becomes inert the moment the BFF connects as service_role.
2. "Full access to your project's data" is the whole project. The design's entire premise is that the BFF is not trusted with OpenWA's data — §1 non-goals say OpenWA's datastore is untouched, and §8.3 says the BFF talks to OpenWA only over HTTP with X-API-Key. Giving the BFF a credential that can SELECT OpenWA's main tables, auth.users, and storage.buckets contradicts that boundary.

Also note the deprecation timeline, which is a maintenance cost even if you wanted it:

"Supabase is deprecating the anon and service_role keys by the end of 2026. Use the publishable (sb_publishable_xxx) and secret (sb_secret_xxx) keys instead." — API keys

And a subtlety that would bite a BFF: "A secret key doesn't work in a browser. Supabase matches on the User-Agent header and returns HTTP 401." Irrelevant for a container, but it shows the key is a Data API credential, not a Postgres credential. The BFF does not speak PostgREST — it speaks libpq over Supavisor. Using a Data API key for a libpq connection is a category error.

3.2 The custom least-privilege roleCopy Anchor Link

Create waas_bff with NOLOGIN in the migration (matching the existing openwa pattern), then enable login out-of-band:

-- supabase/migrations/<ts>_waas_schema.sql
create schema if not exists waas;

do $$
begin
  if not exists (select 1 from pg_roles where rolname = 'waas_bff') then
    create role waas_bff nologin connection limit 10;
  end if;
end $$;

-- Deliberately NOT: bypassrls, createdb, createrole, superuser.
alter role waas_bff nobypassrls;

grant usage on schema waas to waas_bff;
grant select, insert, update, delete on all tables in schema waas to waas_bff;
grant usage, select on all sequences in schema waas to waas_bff;

-- Nobody else, ever.
revoke all on schema waas from public, anon, authenticated, service_role;

-- Pin session state on the role, exactly as the openwa migration does.
alter role waas_bff set search_path = waas, public;
alter role waas_bff set timezone = 'UTC';
-- Optional: cap runaway queries.
alter role waas_bff set statement_timeout = '30s';
alter role waas_bff set idle_in_transaction_session_timeout = '60s';

Then, out of band (never committed):

alter role waas_bff with login password '<generated>';

Why nobypassrls is the load-bearing line. It is what makes §1.4's RLS backstop real. Per the docs: "You can also create new Postgres Roles which can bypass Row Level Security using the 'bypass RLS' privilege … Never share login credentials for any Postgres Role with this privilege." (RLS). NOBYPASSRLS is the default for CREATE ROLE, but state it explicitly so a future ALTER is a visible diff.

Why revoke ... from service_role. The default-privileges machinery is the trap the docs keep flagging:

"On existing projects, a new table in public starts with every privilege already granted to all three roles … Adding policies doesn't take those grants back." — Row Level Security

That is public-specific, but the same ALTER DEFAULT PRIVILEGES machinery can be pointed at any schema. The existing openwa migration already does revoke all on schema openwa from public, anon, authenticated — do the same for waas, and add service_role to the list. Belt and braces: also run

alter default privileges for role postgres in schema waas
  revoke all on tables from anon, authenticated, service_role;
alter default privileges for role postgres in schema waas
  revoke all on functions from anon, authenticated, service_role;

This mirrors the docs' Revoke default privileges recipe, inverted for a private schema.

3.3 Table ownership — the RLS trapCopy Anchor Link

If waas_bff creates the tables, it owns them, and owners bypass RLS by default unless FORCE ROW LEVEL SECURITY is set. Two clean options:

Option A is what the openwa migration already does in spirit (migrations run as postgres; the openwa role gets usage, create so OpenWA can create its own tables). For waas, the BFF does not need CREATE — Drizzle migrations are applied by supabase db push, not at container boot. So grant only DML. This is a meaningful difference from the openwa role and worth calling out in the runbook.

UNVERIFIED: whether Supabase's supautils permits ALTER TABLE ... FORCE ROW LEVEL SECURITY on managed projects. Test with a throwaway table before choosing Option B.

3.4 Consequences, side by sideCopy Anchor Link

Verdict: custom waas_bff. The only argument for service_role is convenience, and the design's whole thesis is that convenience is what you give up to get the confidentiality guarantee.

3.5 One more role: who runs migrations?Copy Anchor Link

supabase db push connects as postgres (the docs' supabase link prompt asks for "your database password", which is the postgres role's). That role has admin privileges and BYPASSRLS. It must never be in the container. Keep it in CI secrets only, and prefer supabase db push --linked with the CLI's own auth over putting the password in a shell variable.

4. MigrationsCopy Anchor Link

4.1 The two optionsCopy Anchor Link

Option 1 — supabase db push (CLI). The docs' canonical flow:

"Supabase tracks which migrations have been applied on each database in a table called supabase_migrations.schema_migrations. When you run supabase db push, it compares your local supabase/migrations folder against that table and runs only the ones not yet applied, in order." "Git tracks your migration files. Supabase tracks what's been applied to each database. These are two separate systems that need to stay in sync." — Database Migrations

Flags that matter (CLI reference):

Option 2 — a migration runner in CI (Drizzle Kit / node-pg-migrate / raw SQL). You own the ledger table, the ordering, and the connection. Full control, but you reimplement supabase_migrations.schema_migrations and lose supabase db diff/db pull interop.

4.2 RecommendationCopy Anchor Link

Use supabase db push from CI, as the single writer, gated by --dry-run. Reasons:

1. The repo already has supabase/migrations/20261007120000_openwa_datastore.sql and a linked project (supabase/.temp/). Switching runners now means migrating the ledger.
2. supabase db push is the only tool that understands Supabase's own managed schemas (auth, storage) and their diff behaviour.
3. The docs explicitly bless the CI pattern: "For a more automated deployment approach, consider using Supabase Branching or a CI/CD pipeline that runs supabase db push on merge to your main branch." (Database Migrations)

But — and this is the part the design should adopt — generate the SQL with Drizzle Kit, apply it with supabase db push. Drizzle owns the TypeScript schema (§4.1 infrastructure/db/schema/), drizzle-kit generate emits timestamped SQL into supabase/migrations/, and supabase db push applies it. You get type-safe schema definitions and Supabase's ledger. Do not run drizzle-kit migrate against the remote — that creates a second, competing ledger.

CI shape:

- run: supabase link --project-ref "$SUPABASE_PROJECT_REF" --password "$SUPABASE_DB_PASSWORD"
- run: supabase db push --dry-run          # gate: prints the plan, applies nothing
- run: supabase db push --linked           # apply
- run: supabase test db --linked           # pgTAP suite (see §1.6)

The docs' team rules, which CI should enforce mechanically:

"The golden rule: never change the remote database directly. Once you're using migrations, all schema changes — even small ones — should go through migration files. Using the Dashboard's SQL editor or Table Editor on your remote database bypasses the migration history, and db push will start failing with sync errors." "One person deploys to remote. Coordinate so only one person runs db push at a time. Migration files are applied in timestamp order, so concurrent pushes from different machines can cause conflicts."

Enforce with a CI concurrency group (concurrency: { group: supabase-migrations, cancel-in-progress: false }) and a branch protection rule that requires the --dry-run job to pass.

4.3 Gotchas specific to a non-public schemaCopy Anchor Link

G1 — search_path is not set for you. supabase db push runs each migration file as postgres, whose search_path is public (plus Supabase's own defaults). A migration containing create table messages (...) lands in public, not waas. Every object in a waas migration must be schema-qualified, or the file must begin with set search_path = waas, public;. The existing openwa migration qualifies everything (create schema if not exists openwa;, grant usage, create on schema openwa to openwa;) — follow that. Note the openwa migration also pins search_path on the role for exactly this reason, with the comment: "OpenWA sets search_path through the libpq options startup parameter; pinning it on the role as well keeps raw migration DDL in openwa even if a pooler drops that parameter." Do the same for waas_bff.

G2 — db diff and db pull do not see non-public schemas by default. The docs describe db pull capturing "your customizations automatically" for pg-delta, and for the legacy migra engine: "the initial pull's pg_dump skips those schemas, but the diff pass that follows appends your triggers and RLS policies there to the same migration." The escape hatch is explicit schema selection:

supabase db pull --schema waas

The docs show this pattern for auth and storage: "If you see that message, capture existing customizations once with explicit pulls: supabase db pull --schema auth." UNVERIFIED: whether --schema waas is accepted for an arbitrary custom schema on the current CLI version (v2.120.0644 per supabase/.temp/). Test it locally first. If it fails, the fallback is to never use db pull for waas and treat the migration files as the sole source of truth.

G3 — the diff engine changes what gets captured. The docs are specific:

"If it contains an [experimental.pgdelta] section with enabled = true, your project uses pg-delta. If the section is missing or enabled = false, your project uses the legacy migra engine." — Database Migrations

Pin this explicitly in supabase/config.toml so CI and local agree. A silent engine switch changes which objects db diff emits.

G4 — custom roles need supabase/roles.sql + --include-roles. The waas_bff role is a role, not a table. supabase db push has a dedicated flag: --include-roles: Include custom roles from supabase/roles.sql. (CLI reference). UNVERIFIED: whether a CREATE ROLE inside a normal migration file is applied by db push (it should be — it is just SQL) or whether Supabase's tooling filters role DDL out of migrations. The existing openwa migration does put create role openwa nologin connection limit 20; inside a migration file and the runbook says it works, so the in-migration path is proven for this project. Keep doing that, and treat roles.sql as the alternative.

G5 — search_path and security definer. If any waas function is SECURITY DEFINER, the docs are absolute:

"Set search_path = '' on every security definer function and schema-qualify the names inside it. Without a pinned search_path, a caller can point an unqualified name at their own object and run it with the function owner's privileges." — Row Level Security

The design has no SECURITY DEFINER functions today. If blind-index lookup ever becomes a function, this applies.

G6 — supabase db reset does not know about waas unless the migrations create it. Local dev resets replay supabase/migrations/ then supabase/seed.sql. Since the waas schema is created by a migration, this works — but any object created by hand in the local Studio will vanish on reset. Expected, but worth a README line.

G7 — lock timeouts on a live database. The docs: "If a lock timeout error occurs, in your migration file, consider increasing your lock_timeout setting." The waas schema is new and empty, so initial migrations are safe. Later ALTER TABLE on messages (a hot, append-heavy table) is not. Adopt the expand/contract pattern from day one: add nullable columns, backfill, then add constraints in a separate migration.

5. Postgres-specific correctness trapsCopy Anchor Link

5.1 Partial unique indexes for idempotencyCopy Anchor Link

The design needs idempotency on at least two paths: (i) a retried POST /messages must not create two rows, and (ii) a redelivered OpenWA webhook (§6.2) must not double-store. The natural key for (ii) is the WhatsApp message ID; for (i) it is a client-supplied idempotency key.

Partial unique index — the Postgres semantics. From the docs:

"A third possible use for partial indexes does not require the index to be used in queries at all. The idea here is to create a unique index over a subset of a table … This enforces uniqueness among the rows that satisfy the index predicate, without constraining those that do not." "It is also possible to allow only one null in a column by creating a unique partial index with an IS NULL restriction." — Partial Indexes

The IS NULL sentence is the useful one here. A plain UNIQUE (tenant_id, wa_message_id) would reject every row with a NULL wa_message_id after the first — because in SQL, NULL != NULL, but a unique index treats two NULLs in the same position as equal. Wait: Postgres unique indexes treat NULLs as distinct by default (NULLS DISTINCT), so multiple NULLs are allowed. UNVERIFIED for the exact case — the safe, explicit form is a partial index:

-- Idempotency for outbound sends: only enforce when the client supplied a key.
create unique index messages_idem_key_uniq
  on waas.messages (tenant_id, idempotency_key)
  where idempotency_key is not null;

-- Idempotency for inbound webhooks: only enforce when we have a WA message id.
create unique index messages_wa_id_uniq
  on waas.messages (tenant_id, wa_message_id)
  where wa_message_id is not null;

Why partial rather than a plain composite unique: it keeps the index small (most rows may lack one of the two keys), and it makes the intent legible — "uniqueness applies to rows that have this key". The docs' Example 11.3 is exactly this shape.

The index must lead with tenant_id. Two tenants can legitimately share a WhatsApp message ID only if they are different WhatsApp accounts — but the same tenant must not. Leading with tenant_id also lets the index serve the tenant-scoped lookup. This is the same "leading column" rule the RLS docs state for policy filters.

5.2 ON CONFLICT behaviourCopy Anchor Link

Rule 1 — a partial unique index requires an index_predicate in the conflict target. From the INSERT docs:

"index_predicate — Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred." "If an attempt at inference is unsuccessful, an error is raised." — INSERT

So this fails:

-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
insert into waas.messages (tenant_id, idempotency_key, ...)
values ($1, $2, ...)
on conflict (tenant_id, idempotency_key) do nothing;

The correct form repeats the predicate:

insert into waas.messages (tenant_id, idempotency_key, ...)
values ($1, $2, ...)
on conflict (tenant_id, idempotency_key) where idempotency_key is not null
do nothing
returning message_id;

Rule 2 — DO UPDATE requires a conflict target; DO NOTHING does not.

"For ON CONFLICT DO NOTHING, it is optional to specify a conflict_target; when omitted, conflicts with all usable constraints (and unique indexes) are handled. For ON CONFLICT DO UPDATE, a conflict_target must be provided." — INSERT

For idempotency, prefer DO NOTHING + RETURNING. The trap: DO NOTHING returns zero rows on conflict, so the caller must then SELECT the existing row. That is a second round trip and a race window unless wrapped in a transaction. The docs note the atomic alternative:

"ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT."

So the robust idempotent insert is DO UPDATE SET ... WHERE false — it takes the update path, returns the existing row via RETURNING, and changes nothing:

insert into waas.messages (tenant_id, idempotency_key, ciphertext, ...)
values ($1, $2, $3, ...)
on conflict (tenant_id, idempotency_key) where idempotency_key is not null
do update set idempotency_key = excluded.idempotency_key
where false
returning message_id, created_at;

UNVERIFIED: whether DO UPDATE ... WHERE false is idiomatic or whether a DO NOTHING + follow-up SELECT ... FOR SHARE inside one transaction is preferable. Both are correct; the first is one round trip.

Rule 3 — RETURNING only returns rows actually written.

"Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an ON CONFLICT DO UPDATE ... WHERE clause condition was not satisfied, the row will not be returned." — INSERT

This is why the DO UPDATE ... WHERE false trick needs care: the WHERE false means the row is not returned. Re-read that sentence — it says the row will not be returned. So the trick above does not work as written. The correct idempotent-upsert that returns the row is DO UPDATE SET <something harmless> with no WHERE, e.g. do update set updated_at = waas.messages.updated_at (a self-assignment, which is a real update and therefore returns the row). This is exactly the kind of trap worth a test.

Rule 4 — CREATE INDEX CONCURRENTLY breaks concurrent ON CONFLICT.

"While CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is running on a unique index, INSERT ... ON CONFLICT statements on the same table may unexpectedly fail with a unique violation." — INSERT

Relevant because the docs recommend inference over ON CONSTRAINT precisely so that a CONCURRENTLY index swap keeps working:

"It is often preferable to use unique index inference rather than naming a constraint directly using ON CONFLICT ON CONSTRAINT constraint_name. Inference will continue to work correctly when the underlying index is replaced by another more or less equivalent index in an overlapping way, for example when using CREATE UNIQUE INDEX ... CONCURRENTLY before dropping the index being replaced."

Use inference, not ON CONSTRAINT. But schedule index builds for a maintenance window anyway, because of the concurrent-insert failure mode.

Rule 5 — deterministic statement. "INSERT with an ON CONFLICT DO UPDATE clause is a 'deterministic' statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises." Batch inserts of the same idempotency key in one statement will error. Insert one row at a time (which the design does anyway — §4.4 forbids bulk send).

Rule 6 — privileges. "all forms of ON CONFLICT also require SELECT privilege on any column whose values are read. This includes any column mentioned in conflict_target." The waas_bff grant must include SELECT on tenant_id and idempotency_key — trivially true if you grant table-level SELECT, but a column-level grant scheme (which the design does not use) would need care.

5.3 Cursor pagination for the messages tableCopy Anchor Link

Do not use OFFSET. The design's idx_messages_tenant_sent ON messages(tenant_id, sent_at DESC) is a keyset index; OFFSET throws that away and degrades linearly. Worse, the RLS docs warn that policy cost "scales with the rows a query scans. This matters most for queries that scan every row in a table, like many select operations, including those using limit, offset, and ordering." (RLS performance).

Keyset (seek) pagination:

select message_id, sent_at, ciphertext
from waas.messages
where tenant_id = $1
  and (sent_at, message_id) < ($2, $3)   -- row-value comparison
order by sent_at desc, message_id desc
limit $4;

(sent_at, message_id) < ($2, $3) is a row constructor comparison; Postgres evaluates it lexicographically and can use the composite index. The tiebreaker is mandatory — sent_at alone is not unique (two messages in the same millisecond), and without message_id as a tiebreaker the cursor can skip or repeat rows. The index must therefore be (tenant_id, sent_at DESC, message_id DESC), not (tenant_id, sent_at DESC). This is a concrete correction to §7's DDL.

Server-side cursors (DECLARE ... CURSOR) are the other option, and they interact with the pooler. From the Postgres docs:

"Unless WITH HOLD is specified, the cursor created by this command can only be used within the current transaction. Thus, DECLARE without WITH HOLD is useless outside a transaction block … Use BEGIN and COMMIT (or ROLLBACK) to define a transaction block." "If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session." — DECLARE

And Supabase's transaction-mode limitation:

"Cursors work inside a single transaction only. A with hold cursor is meant to outlive its transaction, and it doesn't survive the connection returning to the pool." — Connecting to your database

This is the second independent reason to use session mode (§2). A WITH HOLD cursor for a large export (e.g. key rotation scanning messages in batches, §3.5) needs a session that survives between transactions. On 6543 it does not.

Recommendation: keyset pagination for the API surface (stateless, cacheable, no server state); WITH HOLD cursors only for the rotation job, on a dedicated session-mode connection.

Ordering stability under concurrent writes. A new message arriving mid-pagination has a newer sent_at, so it sorts before the cursor and is simply not seen until the next refresh — correct behaviour for a descending feed. A backdated message (a webhook with an old timestamp) would insert behind the cursor and be missed. If that matters, add a created_at-based secondary feed or accept the eventual-consistency window. Document the choice.

5.4 Storing ciphertext: bytea vs textCopy Anchor Link

Use bytea. The design already does (§7: ciphertext BYTEA NOT NULL). The Postgres docs justify it:

"A binary string is a sequence of octets (or bytes). Binary strings are distinguished from character strings in two ways. First, binary strings specifically allow storing octets of value zero and other 'non-printable' octets … Character strings disallow zero octets, and also disallow any other octet values and sequences of octet values that are invalid according to the database's selected character set encoding. Second, operations on binary strings process the actual bytes, whereas the processing of character strings depends on locale settings." — Binary Data Types

XChaCha20-Poly1305 output is uniformly random bytes. It will contain 0x00 and it will contain byte sequences that are invalid UTF-8. text would reject both — the insert would fail with invalid byte sequence for encoding "UTF8". bytea is the only correct choice.

The output-format trap. "The output format depends on the configuration parameter bytea_output; the default is hex." Hex format is \xDEADBEEF. This matters at the driver boundary: postgres.js returns bytea as a Node Buffer (binary), but pg (node-postgres) historically returned the hex string \x... and needed a type parser. UNVERIFIED for the current postgres.js version — verify the round-trip in a test before trusting it. A silent string-vs-buffer mismatch would corrupt every ciphertext.

Size and TOAST. bytea storage is "1 or 4 bytes plus the actual binary string". Large ciphertexts (media metadata, long messages) go to TOAST out-of-line automatically. The design keeps media in R2 and only metadata in Postgres, so this is mostly moot — but secretstream_header and any inline small payloads are fine.

Do not use text with base64. It is 33% larger, and it invites a class of bugs where a +///= gets mangled by a URL-encoding layer. The design's §3.3 blind index already returns raw bytes; keep everything binary end-to-end.

5.5 Blind indexesCopy Anchor Link

The design's §3.3:

blind_index = HMAC-SHA256(HKDF(IK, "idx/<col>/v1"), normalize(value))[:16]

Storage type. bytea is correct — same reasoning as §5.4, and it keeps the [:16] truncation exact. The design's §7 declares phone_blind_index BYTEA and jid_blind_index BYTEA. Good.

The [:16] truncation is a collision-budget decision, and it should be documented as one. A 128-bit truncated HMAC has a birthday bound of ~2^64 values before a 50% collision chance. For a per-tenant contact table that is astronomically safe. But it is a security parameter, not a size optimisation: the truncation is what makes the index a "blind" index rather than a searchable hash. Write the reasoning into the migration comment so nobody "optimises" it to 8 bytes.

Uniqueness. Should (tenant_id, jid_blind_index) be unique? Yes — one contact row per JID per tenant. And it should be a partial unique index if jid_blind_index can be null:

create unique index contacts_tenant_jid_uniq
  on waas.contacts (tenant_id, jid_blind_index)
  where jid_blind_index is not null;

This also gives ON CONFLICT a usable arbiter for contact upsert — with the index_predicate repeated in the conflict target (§5.2).

Determinism and normalize(). The blind index is only useful if the same input always produces the same bytes. normalize(value) must be pinned: Unicode NFC (not NFD), E.164 for phone numbers, lowercased JIDs. Any drift between the write path and the search path (§6.3) silently returns zero rows — a false negative, which is the worst failure mode for a search feature because it looks like "no results" rather than an error. Put normalize() in one place and test it against a fixture table.

Key rotation invalidates the index. §3.5 rotates the KEK and mentions IK (identity key) in the hierarchy. If IK rotates, every blind_index in the table is stale and must be recomputed — which requires the plaintext, which the BFF does not have. This is a design gap. Either (a) IK is derived from a key that never rotates, (b) the rotation job decrypts-and-reindexes while it has plaintext in RAM (which conflicts with the "plaintext only in RAM during a request" guarantee), or (c) blind indexes are recomputed lazily on next write, with a dual-index lookup during the soak window. Flag this as an open decision — it is not a Postgres question, but it is a schema question, and the schema's index definition depends on the answer.

The index does not leak ordering. HMAC-SHA256 output is uniformly distributed, so a btree index on blind_index reveals nothing about the plaintext distribution. This is the property that distinguishes a blind index from order-preserving encryption (which the design correctly rejects, §3.3: "No server-side FTS, no OPE, no deterministic encryption of payloads").

Index the lookup column with tenant_id leading. §7 has idx_contacts_tenant_phone ON contacts(tenant_id, phone_blind_index) and idx_contacts_tenant_jid ON contacts(tenant_id, jid_blind_index). Correct — and it matches the RLS docs' leading-column rule, which matters even if you never write an RLS policy, because it is also the rule for a tenant-scoped query plan.

5.6 Two more schema-level trapsCopy Anchor Link

tenant_id has no foreign key to a tenants table. §7's messages, contacts, media_objects, audit_log, key_rotation_jobs and user_recovery all declare tenant_id UUID NOT NULL with no REFERENCES. Only devices has one (REFERENCES tenant_keys(tenant_id)). This means a bug can write a row for a tenant that does not exist, and it will never be caught. Either add a waas.tenants table with a FK from every tenant-scoped table, or document that tenant_id is an opaque identifier validated in the BFF. The FK is cheap and catches an entire bug class. Note the Supabase advisor 0001 unindexed foreign keys will flag the missing indexes on those FKs — add them.

timestamptz, never timestamp. §7 uses TIMESTAMPTZ throughout. Correct. The openwa migration pins alter role openwa set timezone = 'UTC' because OpenWA refuses to boot otherwise; do the same for waas_bff. A timestamp column would silently drop the offset and break cursor pagination across DST.

Partitioning messages — not yet. Supabase's guidance is explicit:

"There is no real threshold to determine when you should use partitions. Partitions introduce complexity, and complexity should be avoided until it's needed." "If you are considering performance, avoid partitions until you see performance degradation on non-partitioned tables." — Partitioning tables

And the constraint that would bite later: "The column that you are partitioning with must be included in any unique index." If you ever partition messages by sent_at, the primary key becomes (sent_at, message_id) and the idempotency unique indexes must also include sent_at — which breaks the idempotency semantics (the same idempotency key at a different timestamp would be a different row). Partitioning messages by time is therefore incompatible with the idempotency design. If you need retention management, use pg_partman on a separate archive table, or delete by range. Flag this now so nobody reaches for PARTITION BY RANGE (sent_at) later.

6. Consolidated recommendationsCopy Anchor Link

1. Isolation: application-enforced tenant_id predicates in a single scoped() repository builder, backed by a least-privilege waas_bff role and RLS-enabled-with-no-policies on every waas table. Own tables as postgres (Option A) so the RLS backstop is not bypassed by ownership.
2. Pooler: Supavisor session mode, port 5432, sslmode=verify-full, CA at stack/certs/supabase-ca.crt. Application-side pool max: 5. One BFF replica initially.
3. Role: custom waas_bff, NOBYPASSRLS, DML-only grants on waas, search_path = waas, public pinned on the role, timezone = UTC, statement_timeout = 30s. Never service_role, never the postgres password in the container.
4. Migrations: Drizzle Kit generates → supabase db push applies, from CI, single writer, --dry-run gate, supabase test db after. Schema-qualify every object in waas migrations.
5. Schema corrections to §7:
   • idx_messages_tenant_sent → (tenant_id, sent_at DESC, message_id DESC) for keyset pagination.
   • Add partial unique indexes for idempotency_key and wa_message_id.
   • Add a waas.tenants table and FKs from every tenant-scoped table.
   • Add idempotency_key and wa_message_id columns (not in §7 today).
   • Document the blind-index truncation and normalize() contract in migration comments.
   • Resolve the IK-rotation-vs-blind-index gap before implementing §3.5.

7. Open questions / UNVERIFIED itemsCopy Anchor Link

8. SourcesCopy Anchor Link

Supabase official docs

• Connecting to your database — connection-method selection table, session vs transaction mode, transaction-mode limitations, SSL/verify-full
• Connection pooling and limits — shared vs dedicated pooler, pool size, client vs backend connections
• Supavisor FAQ — session vs transaction semantics, user+db+mode pool sizing, static-architecture guidance
• Supavisor troubleshooting — pooler error catalogue
• Row Level Security — grants vs policies, auth.jwt(), app_metadata vs user_metadata, BYPASSRLS, security definer, pgTAP tests, leading-column index rule
• Row Level Security performance — policy cost, select-wrapped functions, avoiding joins
• Column Level Security — column grants (noted as not recommended)
• Postgres Roles — built-in roles, service_role, custom-role guidance, password handling
• Roles, superuser access and unsupported operations — no superuser; COPY ... FROM PROGRAM and ALTER USER ... SUPERUSER unsupported
• API keys — publishable vs secret, service_role BYPASSRLS, deprecation timeline, handling rules
• Securing your API — grants/RLS layers, default privileges, revoke recipes, pre-request functions
• Securing your data — Data API vs Edge Functions vs direct connections vs frontend
• Using Custom Schemas — creating and exposing a non-public schema
• Custom Claims & RBAC — the JWT-claim pattern, custom access token hook
• Database Migrations — db push, supabase_migrations.schema_migrations, db diff/db pull, diff engines, team workflow
• CLI: supabase db push — flags incl. --dry-run, --include-all, --include-roles
• Partitioning tables — when to partition, unique-index constraint
• Event Triggers — auto-enable RLS on new tables
• Advisors — check catalogue (0001 unindexed foreign keys, 0013 rls disabled in public, 0011 function search path mutable)
• Connecting with Drizzle — prepare: false for transaction mode

PostgreSQL 17 official docs

• INSERT / ON CONFLICT — arbiter inference, index_predicate, DO NOTHING vs DO UPDATE, RETURNING semantics, CONCURRENTLY warning, deterministic statement
• Partial Indexes — partial unique indexes, IS NULL restriction, predicate matching
• Binary Data Types — bytea, hex vs escape format, bytea_output
• DECLARE — cursor lifetime, WITH HOLD, transaction requirement
• Transaction Isolation — Read Committed default, Serializable unique-violation caveat
• SELECT — row-value comparison, LIMIT/OFFSET

In-repo

• docs/04-e2e-encryption.md §4.2, §4.3, §7
• docs/02-architecture.md decisions #2, #3, #4
• supabase/migrations/20261007120000_openwa_datastore.sql — the role/schema/search_path pattern to mirror
• stack/preflight.sh — the TLS verification check to reuse
• stack/certs/README.md — CA provenance