Typeform Callback Processing in Postgres

ddl.sql

-- table to store typeform responses
create table typeform (
  id bigserial primary key,
  created_at timestamptz default clock_timestamp(),
  data jsonb
);

-- API function (PostgREST) to process callback and save data.
-- In TypeForm's interfaces, you need to setup callback, using URL: http://your_nginx_address/rpc/typeform
create or replace function v1.typeform*(q json) returns json as $$
begin
  insert into public.typeform(data)
  values(q::jsonb);
  
  return '[1]'::json;
end;
$$ language plpgsql security definer;

nginx_postgrest.conf

# Use this nginx config to forward TypeForm callback requests to your PostgREST
# Important notes: 
#   1) TypeForm callbacks have a bug: you cannot use URLs with explicitly specified port
#   2) You need PostgREST version >= 0.4.* to make it work

location /rpc/typeform {
  proxy_set_header content-type "application/json";
  proxy_set_header prefer "params=single-object";
  proxy_pass http://postgrest.local:3410;
}
location / {
  proxy_pass http://postgrest.local:3410;
}

process_result.sql

-- This query can be used to retrieve knowledge from TypeForm callback's JSON data.
-- The problem is, that answers are separated from questions, and it's not easy to
-- quickly connections between them. Moreover, form field IDs can be changed, if you redesign
-- your typeform. This query solves this problem, returning result if form:
--   |  Field ID  |  Question Title  |  Answer Value

-- You can use the same query in your processing trigger, just omit the first CTE stage (`new as ..()`) and
-- both `from new` clauses.

with new as (
  select data from typeform order by created_at desc limit 1
), questions as (
  select r->>'id' as id, r->>'title' as question_title
  from (select json_array_elements(new.data->'form_response'->'definition'->'fields') as r from new) _
), answers as (
  select
    r->'field'->>'id' as id,
    case when r->>'type' = 'number' then r->>'number' else r->'choice'->>'label' end as answer_value
  from (select json_array_elements(new.data->'form_response'->'answers') as r from new) _
), res as (
  select * from questions join answers using (id)
)
select * from res;