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 Supabase (JWT) and Payload CMS (session variable). Without an abstraction, the core SQL would be littered with Supabase-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 only the auth stub:

-- 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
$$;

The secret stubs remain as exception-raising guards for Payload — calling store_secret_impl() or delete_secret_impl() without an implementation will raise a database exception. Implement your own vault adapter if you need per-tenant secrets under Payload.

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 (56 files)
@smta/supabase	Auth + secrets impl + auth.users FK constraints (3 files)
@smta/payload	Auth impl only (1 file)