Lovable Prompts for Building a Feedback Collection Tool

Build a Canny-style feedback collection tool using Vite + React + TypeScript + Tailwind CSS + shadcn/ui, backed by Supabase (Lovable Cloud). Here is the exact schema, RLS, routes, and components to scaffold:

**CRITICAL BEFORE STARTING:** The OpenAI API key MUST be in Cloud → Secrets as OPENAI_API_KEY (no VITE_ prefix). It must only be accessed via `Deno.env.get('OPENAI_API_KEY')` inside the edge function. Never reference OPENAI_API_KEY in any /src/ file and never add it as a VITE_ environment variable.

**Database migration (0001_feedback_schema.sql):**

Create five tables:

1. `profiles` — id uuid references auth.users pk, full_name text, role text default 'user'. RLS: per-user write own.

2. `boards` — id uuid pk, owner_id uuid references auth.users, slug text unique, name text, public_voting bool default true. RLS: public-read where public_voting=true, per-owner write.

3. `feedback` — id uuid pk default gen_random_uuid(), user_id uuid nullable references auth.users, submitter_email text nullable, submitter_name text nullable, page_url text, text text not null check (char_length(text) between 5 and 2000), sentiment_score float, sentiment_label text check (sentiment_label in ('positive','neutral','negative','urgent')), topic text, status text check (status in ('new','reviewing','planned','in_progress','shipped','wont_do')) default 'new', upvotes int default 0, board_id uuid nullable references boards, created_at timestamptz default now(). RLS: anon INSERT WITH CHECK (char_length(text) BETWEEN 5 AND 2000), NO SELECT for anon. Authenticated INSERT same check. Admin SELECT all via is_admin(). Submitter SELECT own where user_id=auth.uid().

4. `votes` — id uuid pk, feedback_id uuid references feedback, user_id uuid references auth.users, voted_at timestamptz default now(), unique (feedback_id, user_id). RLS: authenticated INSERT own (user_id=auth.uid()), public SELECT via count (not individual row access).

5. `status_changes` — id uuid pk, feedback_id uuid references feedback, actor_id uuid references auth.users, from_status text, to_status text, note text, created_at timestamptz default now(). RLS: admin write, admin + submitter read.

Create the is_admin() SECURITY DEFINER plpgsql function:
```sql
CREATE OR REPLACE FUNCTION is_admin() RETURNS boolean
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  RETURN (SELECT role FROM public.profiles WHERE id = auth.uid()) = 'admin';
END;
$$;
```

**Layouts:**
- `PublicLayout.tsx` — minimal layout for the widget (/widget) and roadmap (/roadmap/[slug]). No sidebar.
- `AdminLayout.tsx` — sidebar for admin: Inbox / Board / Roadmap / Boards / Settings. Wrap in AdminGuard.

**Pages and routes:**
- `/widget` — the embeddable feedback form: a shadcn Sheet (bottom drawer on mobile) with a multi-line Textarea, optional name + email fields, and a Submit button. This page is designed to be iframed on other sites.
- `/roadmap/[slug]` — public roadmap view: feedback grouped by status columns (Planned / In Progress / Shipped). Each card shows text snippet, topic label, upvote count. Signed-in users see an upvote button; anon users see the count only.
- `/admin/feedback` — admin inbox: table of all feedback sorted by created_at desc by default, with sort options (sentiment_score, upvotes, date). Each row shows: text snippet, sentiment chip (color-coded), topic, status, date. Click a row to open FeedbackDetail.
- `/admin/feedback/[id]` — detail view: full text, submitter info (if provided), sentiment score + label, topic, status workflow (dropdown to change status with optional note), status change audit log, and Upvote count.
- `/admin/boards` — list of feedback boards (for multi-product setups), create/edit.
- `/signin` — Supabase Auth form.

**Edge Function: submit-feedback**

Create supabase/functions/submit-feedback/index.ts:
1. Read the request body: { text, submitter_email, submitter_name, page_url, board_id }.
2. Validate: char_length(text) between 5 and 2000. Return 422 if invalid.
3. Insert into feedback: status='new', all fields. Get the inserted id.
4. Call OpenAI (gpt-5.4-mini) for sentiment + topic:
```typescript
const openaiRes = await fetch('https://api.openai.com/v1/chat/completions', {
  method: 'POST',
  headers: { 'Authorization': `Bearer ${Deno.env.get('OPENAI_API_KEY')}`, 'Content-Type': 'application/json' },
  body: JSON.stringify({
    model: 'gpt-5.4-mini',
    response_format: { type: 'json_object' },
    messages: [{
      role: 'user',
      content: `Analyze this feedback and return JSON with exactly these keys: sentiment_score (float -1 to 1), sentiment_label (one of: positive, neutral, negative, urgent), topic (3-word label). Feedback: "${text}"`
    }],
    max_tokens: 100,
  })
});
const aiData = await openaiRes.json();
const { sentiment_score, sentiment_label, topic } = JSON.parse(aiData.choices[0].message.content);
```
5. UPDATE feedback SET sentiment_score, sentiment_label, topic WHERE id = insertedId.
6. If sentiment_label is 'negative' or 'urgent' and SLACK_WEBHOOK_URL is set, fire a Slack webhook with the feedback text snippet.
7. Wrap the OpenAI call in try/catch — if it fails, log the error but DO NOT fail the submission. The feedback row already exists with NULL sentiment fields, which can be re-processed later.
8. Return { ok: true, feedback_id: insertedId }.

Always include CORS preflight:
```typescript
if (req.method === 'OPTIONS') return new Response('ok', { headers: { 'Access-Control-Allow-Origin': '*', 'Access-Control-Allow-Headers': 'authorization,content-type' } });
```

**Key components:**
- `FeedbackWidget.tsx` — the submission form: Textarea (5-2000 chars, character counter), optional name + email fields, Submit button with loading state. Calls the submit-feedback edge function.
- `FeedbackCard.tsx` — shows text snippet, SentimentChip, topic badge, status, upvote count in the admin board and roadmap.
- `StatusBadge.tsx` — color-coded status chip (new=gray, reviewing=blue, planned=purple, in_progress=amber, shipped=green, wont_do=red).
- `SentimentChip.tsx` — color-coded sentiment label (positive=green, neutral=slate, negative=red, urgent=orange).
- `RoadmapColumn.tsx` — one column per status in the public roadmap, with feedback cards inside.

**Styling:** friendly, low-friction widget — small footprint, single text area, rounded soft shadows; admin board uses Trello-like columns for the roadmap view; shadcn Sheet, Card, Badge, DropdownMenu, Textarea.

**Deliverables:** schema with anon-INSERT-only feedback RLS, the is_admin() function, the embeddable widget page, the submit-feedback edge function with OpenAI sentiment scoring, the admin inbox with sentiment-sorted table, status change workflow with audit log, and the public roadmap view.