[FEATURE] A question submission box (backend)

[ykdojo]

Description

This is the backend portion of #59.

[ykdojo]

The first step for this task: design the database schema.

[ykdojo]

I'm going to start working on this one soon.

[ykdojo]

Users table:

[ykdojo]

I'm going to un-assign myself since I'll be away - in case anyone else wants to take it. I'll assign myself when I start working on it again.

[ykdojo]

From my own comment on #59:

The required fields:

• Company (FAANG+, or a custom textbox)
• Position
• Location
• Date (how long ago was this asked?)
• Question (100 char max?)
• Question details
• Stay anonymous (checkbox)

[ykdojo]

For the question table, this setup should be replicated:

created_by is a foreign key to the auth.users table.

[ykdojo]

A rough plan for this feature:

• Assumption: a user is already logged in
• Frontend: the user submits a question
• It goes to an API function within Next.js / Vercel
• We verify that the user is logged in there (pass a JWT there)
• It then calls Supabase with admin access
• The question is inserted in the table.

This whole process may seem a bit complicated, but I think this is the right way to do it to make it as secure as possible.

I've even thought about switching to Firebase because it's complicated, but the workflow with it would be essentially the same.

There may be other ways of going about it (e.g., using row level security / RLS), but the above approach should be the simplest one. I'll document everything as I go here.

[ykdojo]

Documentation references:

• Next.js API routes
• Vercel serverless functions
• Supabase passing JWT
• Getting JWT

(Extra - thinking of sending a PR to update the Supabase doc here once I'm done with this whole process)

[ykdojo]

I did some more research on this.

• How to store JWT in a HTTP-only cookie securely (this is probably the right approach)
• A reference on React forms
• One more reference page on Next.js API

[ykdojo]

• Best header type for JWT: https://stackoverflow.com/questions/33265812/best-http-authorization-header-type-for-jwt
• Using fetch to set authorization header: https://reqbin.com/code/javascript/ricgaie0/javascript-fetch-api-example

[ykdojo]

update: verified that supabase.auth.session().access_token worked for getting the JWT.

next step: set up a Next.js API and send it there.

[ykdojo]

note: the current user can be retrieved with supabase.auth.user()

[ykdojo]

I'm going to unassign myself for now to work on https://github.com/ykdojo/defaang/issues/122 first.

[iShibi]

I can pick the work from here

[ykdojo]

@iShibi sure! That'd be great.

[ykdojo]

Note: we should add a position field to the table.

[ykdojo]

@iShibi any progress? If not, I was thinking of working on this one

[iShibi]

Didn't get time to start the work, you can take it @ykdojo

[ykdojo]

I'll let you know if/when I start working on it

[ykdojo]

updated schema:

[ykdojo]

reference: supabase insert

[ykdojo]

note on supabase service role: https://github.com/supabase/supabase/discussions/1284

[ykdojo]

After some more digging, I found that Supabase has a Next.js auth component - I'll look into this one next: https://github.com/supabase/auth-helpers/blob/main/packages/nextjs/README.md

[ykdojo]

I thought this might work, but it doesn't quite work the way it's described in the doc.

[ykdojo]

This one worked!

[ykdojo]

Note: I'm currently checking this example to see how they solved it.

[ykdojo]

I've found this comment to be helpful, too.

[subhoghoshX]

I wrote an SQL query to generate the above table automatically if anyone here doesn't want to do it manually every time. Just go to the SQL Editor tab in supabase and run the query.

If you find any mistake in the query below, please let me know.

-- Create a table for Public Questions
create table questions (
  id bigint generated by default as identity primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  created_by uuid references auth.users(id) not null,
  company text,
  location text,
  asked_date date,
  question text,
  question_details text,
  stay_anonymous boolean,
  is_approved boolean,
  position text
);

[ykdojo]

update: I'll be working off of this draft PR