# Supabase migration

<!-- Generated by tools/docs/generate-supabase-migration.mjs from @openreceive/core. Never edit it by hand: change supabasePaymentsMigrationSql, then run npm run build:docs. -->

This is the SQL that `npx openreceive scaffold payments --supabase` writes,
for [Supabase over HTTPS](/guides/supabase.md#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](/guides/supabase.md#2-write-openreceive_on_paid).

```sql
-- 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';
```