셀 수 있고, 순위를 매기고, 답할 수 있는 기능 요청 모으기

<form action="https://ikiform.com/f/frm_xxxxxxxxxxxxxxxx" method="post">
  <label for="problem">What are you trying to do?</label>
  <textarea id="problem" name="problem" rows="5" required minlength="30"
    placeholder="Send last month's invoices to our accountant in one go"></textarea>
  <label for="workaround">How do you do it today?</label>
  <textarea id="workaround" name="workaround" rows="3"></textarea>
  <label for="area">Which part of the product?</label>
  <select id="area" name="area" required>
    <option value="billing">Billing and invoices</option>
    <option value="reports">Reports</option>
    <option value="team">Team and permissions</option>
    <option value="integrations">Integrations and API</option>
    <option value="other">Something else</option>
  </select>
  <fieldset>
    <legend>How much does it matter?</legend>
    <label><input type="radio" name="impact" value="blocking" required /> It stops us using the product for something</label>
    <label><input type="radio" name="impact" value="slow" /> It costs us time every week</label>
    <label><input type="radio" name="impact" value="nice" /> It would be nice</label>
  </fieldset>
  <label for="email">Email</label>
  <input id="email" name="email" type="email" required />
  <input name="_gotcha" type="text" tabindex="-1" autocomplete="off" hidden />
  <button>Send request</button>
</form>

create table feature_requests (
  submission_id text primary key,
  area text not null,
  impact text not null,
  problem text not null,
  workaround text,
  email text not null,
  theme text,
  created_at timestamptz not null
);

import { verifyWebhook, WebhookVerificationError } from "@ikiform/sdk/webhooks";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const INSERT = `insert into feature_requests (submission_id, area, impact, problem, workaround, email, created_at)
values ($1, $2, $3, $4, $5, lower($6), $7)
on conflict (submission_id) do nothing`;

export async function POST(request: Request) {
  const payload = await request.text();
  let event;
  try {
    event = await verifyWebhook({
      payload,
      headers: request.headers,
      secret: process.env.IKIFORM_WEBHOOK_SECRET!,
    });
  } catch (error) {
    if (error instanceof WebhookVerificationError) return new Response(error.reason, { status: 400 });
    throw error;
  }
  if (event.type !== "submission.created") return new Response(null, { status: 204 });

  const { problem, workaround, area, impact, email } = event.data.fields;
  await pool.query(INSERT, [
    event.data.submission_id,
    area,
    impact,
    problem,
    workaround ?? null,
    email,
    event.data.created_at,
  ]);
  return new Response(null, { status: 204 });
}

update feature_requests
set theme = 'invoice-export'
where theme is null
  and area = 'billing'
  and problem ilike any (array['%export%', '%accountant%', '%download%']);

select coalesce(theme, 'untagged') as theme,
       count(*) as requests,
       count(distinct email) as people,
       count(*) filter (where impact = 'blocking') as blocking
from feature_requests
group by 1
order by blocking desc, people desc;

select distinct email
from feature_requests
where theme = 'invoice-export';