Sandbox: Trivia App

Description of the Trivia Application

This tutorial sets up a trivia app that gives users a set of three daily trivia questions based on some selected preferences. It then allows the user to answer the questions, and save their favorite questions.

The application is deployed online here, if you want a closer look. The user homepage looks like the following:

The full-stack application architecture is set up as the following:

It uses the Minimum Viable Product Stack that we introduce in this course.

The application uses the NextJS framework and uses Supabase for the database and authentication. It also uses the OpenAI API to generate the trivia questions. You’ll learn more when you do the practice exercises for this sandbox!

The setup here will require you to create your own Supabase and OpenAI accounts to get your own API keys. For best security practices, I am not giving out my API keys for OpenAI and Supabase! These set-up steps will help you understand the applcation in a more hands-on way!

Installation Instructions

Prerequisites

You should have set up Node and Git by following the instructions in the Getting Started section.

• Cursor (or your favorite AI-powered IDE)
• Node.js
• Git
• Supabase account
• OpenAI account

MVP Sandbox Trivia App

Step 1: Project Setup

Clone the Github Repository (if you haven’t done this yet)!

git clone https://github.com/karenacai/CurioCity.git
cd CurioCity/Sandbox/trivia-app

Step 2: Supabase Setup

1. Create a Supabase account at supabase.com

2. Create a new project. Don’t worry about this using one of your two free Supabase projects! You can delete the project as soon as you are done with the Sandbox!

3. Note down the API keys for your Supabase project on your project dashboard. You can find this by going to your project dashboard, clicking the ‘Connect’ button, and clicking on the tab ‘App Frameworks’.

4. Set up the Supabase database tables (that are required for this app):

   • Go to SQL Editor
   • Add the following sql code to the command prompt (and then click Run Query):

   

   This SQL command will set up the database tables required for this project. It also sets up the role-level security properties of the tables (i.e. defines which tables users can access and update).

-- Create trivia_questions table
CREATE TABLE trivia_questions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  difficulty INTEGER CHECK (difficulty BETWEEN 1 AND 10),
  question TEXT NOT NULL,
  choices TEXT[] NOT NULL,
  answer TEXT NOT NULL,
  category TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  user_id UUID REFERENCES auth.users(id) NOT NULL
);

-- Create user_trivia_history table
CREATE TABLE user_trivia_history (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  trivia_id UUID REFERENCES trivia_questions(id) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- Create favorites table
CREATE TABLE favorites (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  trivia_id UUID REFERENCES trivia_questions(id) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  -- Add unique constraint to prevent duplicate favorites
  UNIQUE(user_id, trivia_id)
);

-- Create indexes for better query performance
CREATE INDEX idx_trivia_user_id ON trivia_questions(user_id);
CREATE INDEX idx_trivia_category ON trivia_questions(category);
CREATE INDEX idx_history_user_id ON user_trivia_history(user_id);
CREATE INDEX idx_history_trivia_id ON user_trivia_history(trivia_id);
CREATE INDEX idx_favorites_user_id ON favorites(user_id);
CREATE INDEX idx_favorites_trivia_id ON favorites(trivia_id);

-- Enable Row Level Security (RLS)
ALTER TABLE trivia_questions ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_trivia_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE favorites ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
CREATE POLICY "Users can view all trivia questions"
  ON trivia_questions FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can create their own trivia questions"
  ON trivia_questions FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can view their own history"
  ON user_trivia_history FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create their own history"
  ON user_trivia_history FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can view their own favorites"
  ON favorites FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

CREATE POLICY "Users can manage their own favorites"
  ON favorites FOR ALL
  TO authenticated
  USING (auth.uid() = user_id);

-- Create users_answers table
CREATE TABLE users_answers (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) NOT NULL,
  trivia_id UUID REFERENCES trivia_questions(id) NOT NULL,
  selected_answer TEXT NOT NULL,
  is_correct BOOLEAN NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  -- Add unique constraint to prevent duplicate answers for the same question
  UNIQUE(user_id, trivia_id)
);

-- Enable Row Level Security
ALTER TABLE users_answers ENABLE ROW LEVEL SECURITY;

-- Create policy for inserting - users can only insert their own answers
CREATE POLICY users_answers_insert_policy ON users_answers
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Create policy for selecting - users can only view their own answers
CREATE POLICY users_answers_select_policy ON users_answers
  FOR SELECT
  USING (auth.uid() = user_id);

-- Create policy for updating - users cannot update their answers
CREATE POLICY users_answers_update_policy ON users_answers
  FOR UPDATE
  USING (false);

-- Create policy for deleting - users cannot delete their answers
CREATE POLICY users_answers_delete_policy ON users_answers
  FOR DELETE
  USING (false);

-- Add comment to explain table purpose
COMMENT ON TABLE users_answers IS 'Stores user answers to trivia questions';

ALTER TABLE trivia_questions ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_trivia_history ENABLE ROW LEVEL SECURITY;

-- Create policy for inserting trivia_questions (rate limit to 3 per day)
CREATE POLICY "Rate limit trivia question creation"
ON trivia_questions
FOR INSERT
TO authenticated
WITH CHECK (
  -- Check if user has created fewer than 3 questions in the last 24 hours
  (
    SELECT COUNT(*)
    FROM trivia_questions
    WHERE user_id = auth.uid()
    AND created_at > (CURRENT_TIMESTAMP - INTERVAL '1 day')
  ) < 3
);

-- Create policy for inserting user_trivia_history (rate limit to 3 per day)
CREATE POLICY "Rate limit trivia history creation"
ON user_trivia_history
FOR INSERT
TO authenticated
WITH CHECK (
  -- Check if user has created fewer than 3 history entries in the last 24 hours
  (
    SELECT COUNT(*)
    FROM user_trivia_history
    WHERE user_id = auth.uid()
    AND created_at > (CURRENT_TIMESTAMP - INTERVAL '1 day')
  ) < 3
);

5. Verify the table was created successfully and looks like the following. To access the Schema Visualizer, click Databases -> Schema Visualizer, and you should see something like:

6. You will also need to update the URL properties in your account. To access the URL configuration settings, go to Authentication -> URL Configuration. This configuration is meant to help your application know which URL to redirect the user to when they do certain parts of the user authentication process (like password confirmation or resetting their passwords).

Step 3: OpenAI Setup

1. Create an OpenAI account at openai.com
2. Navigate to API settings (or go to this link) and generate an API key.
3. Add the API key to your .env file (as the OPEN_API_KEY variable)

Step 4: Environment Setup

Open up your favorite IDE (we recommend Cursor), and set up the .env file for the project.

• Create a .env file in the root directory of this project.
• Add your API keys (format provided below)

NEXT_PUBLIC_SUPABASE_URL=your_supabase_url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_supabase_anon_key
OPENAI_API_KEY=your_openai_api_key

Step 5: Installing Project Dependencies

# Install project dependencies
npm install

Step 6: Launch the Application

You will want to

npm run dev

Note, you will have to run this command in the root of your project folder, where the package.json file is located.

Learning Challenges

Phase 0: Playing around with the app

1. Run the application with the command ‘npm run dev’. Explore the application by clicking on buttons and trying out the different features. *Note, every time you generate trivia questions, you will be spending some of your OpenAI credits (money).

   a. Authentication:

   • Go through the process of signing up for an account. Check your Supabase Users table (in your web browser) to see if the sign up was successful!
   • Now that you have created an account, go ahead and sign in.
   • Try resetting your user account password. Did that work? If not, what happened?

   b. Trivia question generation:

   • Generate a set of trivia questions! What happens?
   • Can you check that the trivia_questions table and the user_trivia_questions table were updated in Supabase?
   • What happens if you try to generate trivia questions again?

   c. Favorites page:

   • Favorite one or two of the questions on the home page.
   • Do they appear in the favorites table in Supabase?
   • Check the favorites page and see if the questions show up.
   • Does the unfavoriting functionality work?

   d. History page:

   • Check the history page, and make sure the user’s past history questions appear.

Phase 1: Coding with AI open-exploration

Tip

💡 Pro-tip: Some tips for feature exploration:

• Leverage your favorite LLM (ChatGPT, Gemini, or Claude) for some tips on how you would implement this feature. For some context, you’re working in a NextJS project with a Supabase backend.
• Don’t be afraid to give Cursor context to whole folders, and ask Cursor how things work and which files will be helpful in this implementation.
• Use ‘Thinking’ mode for the most advanced help from Cursor.
• If you want to restore your code to a previous version, use the ‘Restore Checkpoint’ button near the prompt you entered.

1. (Feature 1: Cosmetic Change) Can you get Cursor to increase the text size of the trivia category buttons?

   a. What learning/research/discovery did you do prior to writing and implementing a Cursor prompt (with LLMs and/or looking )?

   b. Which files did Cursor edit/modify to implement this feature? Where does this file live?

   c. What are some bugs, issues, or limitations you encountered during this process?

   d. Were you successful in implementing the feature? If so, show a screenshot or GIF of it working.

2. (Feature 2: Countdown Timer) Add a timer that counts down as soon as the user clicks the button to generate trivia questions. As soon as the timer expires, can you have a UI/UX display that says ‘your time has run out’, and prevent the user from answering questions?

   a. What learning/research/discovery did you do prior to writing and implementing a Cursor prompt (with LLMs and/or looking )?

   b. Which files did Cursor edit/modify to implement this feature? Where do these files live?

   c. What are some bugs, issues, or limitations you encountered during this process?

   d. Were you successful in implementing the feature? If so, show a screenshot or GIF of it working.

Sandbox Feature Tips

3. (Feature 3: Prompt Modification) Modifying the prompt to take in additional user preference (for example, add a field for spiciness, goofiness, mind-bendiness, or whatever you can think of). Make sure that the prompt to the OpenAI API takes this user input into consideration when generating the trivia questions.

   a. What learning/research/discovery did you do prior to writing and implementing a Cursor prompt (with LLMs and/or looking )?

   b. Which files did Cursor edit/modify to implement this feature? Where do these files live?

   c. What are some bugs, issues, or limitations you encountered during this process?

   d. Were you successful in implementing the feature? If so, show a screenshot or GIF of it working.

Phase 2: Coding with AI directed exploration (to learn about project structure/files)

Tip

💡 Pro-tip: Leverage Cursor or your favorite LLMs for help with exploring this! Alternatively, you could also try to use Claude Code!

1. Can you find out where the UI/UX for the /sign-in and /sign-up forms live? You can either inspect the files/folders manually or use Cursor for guidance if you need help!

   a. What do you notice about the route (i.e. website.com/sign-in) and where the file lives in the code repository? What is the pattern?

2. You have gone through the process of signing up for an account. Can you get Cursor to help you map where in the code this whole user-flow is encoded? Can you draw out a diagram to show what code files/functions are hit?

3. Let’s explore the OpenAI API integration.

   a. What code is being called between the user hitting the ‘Generate Trivia Questions’ button and the questions being generated and appearing on the page?

   b. Where is the route where the OpenAI API is called? What is happening in this code file? Can you walk through and describe the general functionality.

4. Can you find and list all instances where the Supabase tables are being updated or modified?

   a. For each of these instances, can you label whether the Supabase client is being called on the server or client-side and why?

   b. What is the difference between server vs. client-side?

Reflections on Coding with AI

1. What are your initial thoughts about coding with AI? Any surprises? Challenges?

2. When did you feel like you were collaborating with AI vs. just receiving instructions from it?

3. How helpful was AI in resolving bugs or identifying problems?

4. Did you ever get stuck in trying to fix a problem? Or in an infinite cycle of errors? What happened and what did you try?

5. Did you notice your prompts improving over time? How did your approach evolve?

6. On a scale of 1-10, how confident are you in knowing which files should change if I asked you to implement a specific feature (like the ones in phase 1 of the exercise)?