Supabase migration

This is the SQL that npx openreceive scaffold payments --supabase writes, for Supabase over HTTPS. Use this page when you cannot run the scaffold, for example from Lovable, whose agent has no terminal. It is functions version 1, for @openreceive/http 0.4.23 or newer.

Save it, unchanged, as one new migration (supabase/migrations/<timestamp>_openreceive.sql), or paste it into the Supabase SQL Editor and run it. It is safe to run again. It creates OpenReceive’s two tables with row level security on, revokes every grant from anon and authenticated, and creates the functions the server calls to write the tables. It also creates openreceive_on_paid as a placeholder that refuses every settlement, unless your project already has one. Then replace that placeholder, in a migration of your own, with the SQL that marks your order paid: see Write openreceive_on_paid.

-- OpenReceive payment storage for Supabase, used over Supabase's HTTPS API.
-- Generated by `openreceive scaffold payments --supabase`. Apply it with your
-- normal migration workflow. Edit only public.openreceive_on_paid, in a later
-- migration of your own; the rest belongs to OpenReceive.

CREATE TABLE IF NOT EXISTS openreceive_payments (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  reference TEXT NOT NULL,
  payment_hash TEXT NOT NULL UNIQUE,
  status TEXT NOT NULL DEFAULT 'pending',
  status_reason TEXT,
  paid_at BIGINT,
  expires_at BIGINT NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL,
  inserted_at BIGINT NOT NULL,
  checkout_data TEXT NOT NULL,
  swap_data TEXT,
  client_ip TEXT,
  CHECK (status IN ('pending', 'settled', 'expired', 'failed', 'attention')),
  CHECK (payment_hash ~ '^[0-9a-f]{64}$')
);
CREATE INDEX IF NOT EXISTS openreceive_payments_reference_created_idx ON openreceive_payments (reference, created_at);
CREATE INDEX IF NOT EXISTS openreceive_payments_status_created_idx ON openreceive_payments (status, created_at);
CREATE INDEX IF NOT EXISTS openreceive_payments_client_ip_inserted_idx ON openreceive_payments (client_ip, inserted_at);
CREATE TABLE IF NOT EXISTS openreceive_meta (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  rev BIGINT NOT NULL DEFAULT 0
);
INSERT INTO openreceive_meta (key, value, rev) VALUES ('schema_version', '1', 0) ON CONFLICT (key) DO NOTHING;

-- The Data API must never reach these tables: swap_data is server-only, and
-- whoever can write them can mark an order paid. Only the server key can.
alter table public.openreceive_payments enable row level security;
alter table public.openreceive_meta enable row level security;
revoke all on table public.openreceive_payments, public.openreceive_meta from public, anon, authenticated;

create or replace function public.openreceive_reference_snapshot(p_reference text)
returns jsonb language sql stable set search_path = '' as $$
  select coalesce(
    jsonb_agg(
      jsonb_build_object('payment_hash', payment_hash, 'status', status, 'status_reason', status_reason)
      order by payment_hash
    ),
    '[]'::jsonb
  )
  from public.openreceive_payments
  where reference = p_reference
$$;

create or replace function public.openreceive_commit_attempt(
  p_reference text, p_snapshot jsonb, p_supersede text[], p_attempt jsonb, p_now bigint
) returns void language plpgsql set search_path = '' as $$
begin
  perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtextextended(p_reference, 8210223));
  if public.openreceive_reference_snapshot(p_reference) is distinct from p_snapshot then
    raise exception 'openreceive: this reference changed; read it again' using errcode = '40001';
  end if;
  -- Marked, not closed: a superseded invoice stays payable until a wallet scan closes it.
  update public.openreceive_payments
     set status_reason = 'superseded', updated_at = p_now
   where reference = p_reference and payment_hash = any(p_supersede) and status = 'pending';
  insert into public.openreceive_payments (
    reference, payment_hash, status, paid_at, expires_at, created_at, updated_at,
    inserted_at, checkout_data, swap_data, client_ip
  ) values (
    p_reference, p_attempt->>'payment_hash', 'pending', null,
    (p_attempt->>'expires_at')::bigint, (p_attempt->>'created_at')::bigint, p_now, p_now,
    p_attempt->>'checkout_data', p_attempt->>'swap_data', p_attempt->>'client_ip'
  );
end
$$;

create or replace function public.openreceive_record_settlement(
  p_reference text, p_payment_hash text, p_snapshot jsonb, p_paid_at bigint,
  p_first boolean, p_now bigint
) returns boolean language plpgsql set search_path = '' as $$
begin
  perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtextextended(p_reference, 8210223));
  if public.openreceive_reference_snapshot(p_reference) is distinct from p_snapshot then
    raise exception 'openreceive: this reference changed; read it again' using errcode = '40001';
  end if;
  update public.openreceive_payments
     set status = 'settled',
         status_reason = case when p_first then null else 'duplicate_settlement' end,
         paid_at = p_paid_at, updated_at = p_now
   where reference = p_reference and payment_hash = p_payment_hash and status = 'pending';
  if not found then
    raise exception 'openreceive: this reference changed; read it again' using errcode = '40001';
  end if;
  -- The host's fulfillment, in this transaction: if it raises, nothing is recorded.
  -- It runs with the search path the SQL Editor uses, so unqualified table names
  -- in it resolve as they do there; this function's own path returns on exit.
  if p_first then
    perform pg_catalog.set_config('search_path', 'public, extensions', true);
    perform public.openreceive_on_paid(p_reference, p_payment_hash, p_paid_at);
  end if;
  return p_first;
end
$$;

create or replace function public.openreceive_record_reconciliation(
  p_payment_hash text, p_status text, p_reason text, p_observed_at bigint
) returns void language plpgsql set search_path = '' as $$
declare
  v_reference text;
begin
  if p_status not in ('expired', 'failed', 'attention') then
    raise exception 'openreceive: reconciliation cannot set status %', p_status;
  end if;
  select reference into v_reference
    from public.openreceive_payments where payment_hash = p_payment_hash;
  if v_reference is null then
    return;
  end if;
  perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtextextended(v_reference, 8210223));
  -- Only while pending: a settled attempt is never overwritten.
  update public.openreceive_payments
     set status = p_status, status_reason = p_reason, updated_at = p_observed_at
   where payment_hash = p_payment_hash and status = 'pending';
end
$$;

-- What the server checks before it serves: versions, the host's
-- openreceive_on_paid, and that the Data API's roles reach nothing here.
create or replace function public.openreceive_supabase_status()
returns jsonb language plpgsql stable set search_path = '' as $$
declare
  v_exposed text[] := '{}';
  v_unprotected text[] := '{}';
  v_missing text[] := '{}';
  v_on_paid regprocedure;
  v_role text;
  v_table text;
  v_function record;
  v_signature text;
begin
  foreach v_table in array array['openreceive_payments', 'openreceive_meta'] loop
    if not exists (
      select 1 from pg_catalog.pg_class
       where oid = pg_catalog.to_regclass('public.' || v_table) and relrowsecurity
    ) then
      v_unprotected := v_unprotected || v_table;
    end if;
    foreach v_role in array array['anon', 'authenticated'] loop
      if pg_catalog.to_regclass('public.' || v_table) is not null and (
        pg_catalog.has_any_column_privilege(v_role, 'public.' || v_table, 'SELECT, INSERT, UPDATE, REFERENCES')
        or pg_catalog.has_table_privilege(v_role, 'public.' || v_table, 'DELETE, TRUNCATE, TRIGGER')
      ) then
        v_exposed := v_exposed || (v_role || ' on table ' || v_table);
      end if;
    end loop;
  end loop;
  for v_function in
    select p.oid, p.proname from pg_catalog.pg_proc p
     where p.pronamespace = 'public'::regnamespace and p.proname like 'openreceive\_%'
  loop
    foreach v_role in array array['anon', 'authenticated'] loop
      if pg_catalog.has_function_privilege(v_role, v_function.oid, 'EXECUTE') then
        v_exposed := v_exposed || (v_role || ' on function ' || v_function.proname);
      end if;
    end loop;
  end loop;
  foreach v_signature in array array['public.openreceive_reference_snapshot(text)', 'public.openreceive_commit_attempt(text, jsonb, text[], jsonb, bigint)', 'public.openreceive_record_settlement(text, text, jsonb, bigint, boolean, bigint)', 'public.openreceive_record_reconciliation(text, text, text, bigint)', 'public.openreceive_supabase_status()'] loop
    if pg_catalog.to_regprocedure(v_signature) is null then
      v_missing := v_missing || v_signature;
    end if;
  end loop;
  v_on_paid := pg_catalog.to_regprocedure('public.openreceive_on_paid(text, text, bigint)');
  return jsonb_build_object(
    'functions_version', 1,
    'schema_version', (select value from public.openreceive_meta where key = 'schema_version'),
    'on_paid', case
      when v_on_paid is null then 'missing'
      when (select prosrc from pg_catalog.pg_proc where oid = v_on_paid) like '%OPENRECEIVE_ON_PAID_NOT_IMPLEMENTED%' then 'stub'
      else 'defined'
    end,
    'exposed', to_jsonb(v_exposed),
    'unprotected_tables', to_jsonb(v_unprotected),
    'missing_functions', to_jsonb(v_missing)
  );
end
$$;

-- Your fulfillment. This stub refuses every settlement, so an attempt stays
-- pending (and is retried) until you replace it in a migration of your own.
-- A payment is never lost while it raises. Applying this file again never
-- overwrites your version.
do $$
begin
  if pg_catalog.to_regprocedure('public.openreceive_on_paid(text, text, bigint)') is null then
    create function public.openreceive_on_paid(
      p_reference text, p_payment_hash text, p_paid_at bigint
    ) returns void language plpgsql set search_path = '' as $fn$
    begin
      raise exception 'OPENRECEIVE_ON_PAID_NOT_IMPLEMENTED: replace public.openreceive_on_paid with your fulfillment';
    end
    $fn$;
  end if;
end
$$;

-- Only the server key may call these. Whoever can call openreceive_on_paid can
-- mark an order paid.
revoke execute on function public.openreceive_reference_snapshot(text), public.openreceive_commit_attempt(text, jsonb, text[], jsonb, bigint), public.openreceive_record_settlement(text, text, jsonb, bigint, boolean, bigint), public.openreceive_record_reconciliation(text, text, text, bigint), public.openreceive_supabase_status(), public.openreceive_on_paid(text, text, bigint) from public, anon, authenticated;
grant execute on function public.openreceive_reference_snapshot(text), public.openreceive_commit_attempt(text, jsonb, text[], jsonb, bigint), public.openreceive_record_settlement(text, text, jsonb, bigint, boolean, bigint), public.openreceive_record_reconciliation(text, text, text, bigint), public.openreceive_supabase_status(), public.openreceive_on_paid(text, text, bigint) to service_role;

notify pgrst, 'reload schema';