Adapter Pattern

The Problem

SMTA’s RLS policies need to know who the current user is. But “who is the current user” is answered differently by each platform — Supabase reads from a JWT claim, while Payload CMS and better-auth both use a PostgreSQL session variable set per-request. Without an abstraction, the core SQL would be littered with platform-specific calls.

The Solution: Function Stubs

@smta/core defines a stub function:

CREATE OR REPLACE FUNCTION core.get_current_user_id()
RETURNS UUID AS $$
BEGIN
  RAISE EXCEPTION 'core.get_current_user_id() not implemented. Deploy an adapter (supabase or payload).';
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = core;

This stub raises an exception if called without an adapter — it is a deployment guard, not a silent fallback. Every RLS policy in @smta/core calls this function. The stub is replaced by the adapter implementation after core deploys.

A second pair of stubs handles secret storage:

CREATE OR REPLACE FUNCTION core.store_secret_impl(p_secret TEXT, p_name TEXT DEFAULT NULL)
RETURNS TEXT AS $$
BEGIN
  RAISE EXCEPTION 'core.store_secret_impl() not implemented. Deploy an adapter.';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = core;

CREATE OR REPLACE FUNCTION core.delete_secret_impl(p_secret_ref TEXT)
RETURNS VOID AS $$
BEGIN
  RAISE EXCEPTION 'core.delete_secret_impl() not implemented. Deploy an adapter.';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = core;

Adapter Implementations

Supabase adapter (@smta/supabase) replaces all three stubs:

-- auth_supabase_impl.sql
create or replace function core.get_current_user_id()
returns uuid language sql stable security definer as $$
  select auth.uid()
$$;

-- secrets_supabase_impl.sql
CREATE OR REPLACE FUNCTION core.store_secret_impl(p_secret TEXT, p_name TEXT DEFAULT NULL)
RETURNS TEXT AS $$
DECLARE
  v_vault_id UUID;
BEGIN
  SELECT vault.create_secret(p_secret, p_name) INTO v_vault_id;
  RETURN v_vault_id::TEXT;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = core, vault, public;

Payload adapter (@smta/payload) replaces the auth stub and the secret stubs:

-- auth_payload_impl.sql
create or replace function core.get_current_user_id()
returns uuid language sql stable security definer as $$
  select nullif(current_setting('app.current_user_id', true), '')::uuid
$$;

Its secrets_pgcrypto_impl.sql replaces all three secret stubs with a pgcrypto-backed implementation (values encrypted into core.encrypted_secrets, keyed by the app.secrets_key GUC). See the Payload adapter page.

better-auth adapter (@smta/better-auth) replaces the auth stub and the secret stubs, and ships in two id-mode variants selected by --better-auth-ids (uuid casts the session value to UUID; mapped resolves a string id through core.user_identities):

-- auth_impl_uuid.sql (uuid mode)
CREATE OR REPLACE FUNCTION core.get_current_user_id()
RETURNS UUID AS $$
  SELECT NULLIF(current_setting('app.current_user_id', true), '')::UUID;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = core, public;

It also adds a trigger on better-auth’s user table to auto-create core.users_meta rows on signup — the equivalent of Supabase’s auth.users foreign key, implemented via trigger rather than a platform API — and replaces the secret stubs with the same pgcrypto implementation Payload uses. See the better-auth adapter page.

Deployment Order

The combined SQL scripts always deploy in this order:

1. @smta/core    — deploys stubs + all core SQL
2. @smta/<adapter> — replaces stubs with real implementations

This ordering is safe because PostgreSQL resolves function calls by name at runtime, not at definition time. RLS policies defined during step 1 will call the real implementation defined in step 2 as soon as step 2 completes.

Package Boundaries

Package	Contains
@smta/core	All adapter-agnostic SQL — loads on stock PostgreSQL 18 (57 files)
@smta/supabase	Role mapping + auth (Vault) + secrets + auth.users FK constraints + signup trigger + pg_graphql disable (6 files)
@smta/payload	Role wiring + auth impl + pgcrypto secrets (3 files)
@smta/better-auth	Role wiring + auth impl + new-user trigger + pgcrypto secrets, per id mode (4 files)