skip to content
Lawson's Website
Loading listening status...
Table of Contents.

Introduction

In this post, I’ll walk you through how I built a full-stack Spotify Song Request System from scratch using Next.js 15 App Router, Discord OAuth, and PostgreSQL. The goal was to create a clean, modern request experience for viewers to submit songs — either through a web page or a Discord bot — while also giving moderators and streamers full control over what actually gets played.

It started out simple — I just wanted a form where users could paste in a Spotify track link. But very quickly I realized I needed to add moderation tools, authentication, rate limiting, and Spotify API token management. Eventually, I added full admin support with user roles, ban/unban controls, and auto-generated logging via the database.

This write-up will cover all the routes, logic, database schema, authentication flow, and UI features. I’ll also share the real issues I ran into along the way (including some nasty ones with Next.js and Vercel), and how I solved them.

If you’re interested in how to manage OAuth securely, structure API routes in Next.js, and build a dashboard-style app from scratch — keep reading.

Authentication System (Discord OAuth)

The authentication system in this project is built using Discord OAuth2. Users must log in through Discord to access any restricted parts of the application, such as submitting song requests or accessing the moderation dashboard.

How It Works

Upon successful login, the OAuth callback (/api/discord/callback) receives the access token and user information. This information is stored in an HTTP-only cookie named discord_user, which includes the user’s id and username.

const cookieStore = cookies();
cookieStore.set('discord_user', JSON.stringify({
  id: user.id,
  username: user.username
}), {
  httpOnly: true,
  path: '/'
});

This cookie is used across all authenticated routes and pages to determine whether the user has permission to perform actions like submitting a request or moderating other users.

Database Sync

Whenever a user interacts with any protected route, the backend inserts or updates the user’s role in the user_roles table if it doesn’t exist already. This ensures every user has a consistent role record across requests.

await db.insert(userRoles).values({
  id: user.id,
  username: user.username,
  isModerator: false,
  isBanned: false
}).onConflictDoUpdate({
  target: userRoles.id,
  set: { username: user.username }
});

Role Enforcement

APIs that require elevated privileges (such as moderation or approving song requests) check the user_roles table for isModerator or isBanned status. Unauthorized users receive a 403 Forbidden response.

if (!role || !role.isModerator) {
  return NextResponse.json({ error: 'Unauthorized (not allowed)' }, { status: 403 });
}

This tight integration ensures security across all sensitive routes, and allows flexible user management through the admin panel.

Database Schema and Design

Database Schema and Design

To support a scalable and permission-aware song request system, we use a PostgreSQL database with tables built using Drizzle ORM. Here’s how the schema is structured:

spotify_tokens

This table stores the current access and refresh tokens for the Spotify API, allowing us to queue songs without requiring the user to reauthenticate each time.

export const spotifyTokens = pgTable('spotify_tokens', {
  id: text('id').primaryKey(), // Always 'singleton'
  access_token: text('access_token').notNull(),
  refresh_token: text('refresh_token').notNull(),
  updated_at: timestamp('updated_at').notNull(),
});

song_requests

This table holds every Spotify track submitted through the request form. It contains metadata such as title, artist, requester, and approval status.

export const songRequests = pgTable('song_requests', {
  id: text('id').primaryKey().default(sql`gen_random_uuid()`),
  spotifyUri: text('spotify_uri').notNull(),
  title: text('title').notNull(),
  artist: text('artist').notNull(),
  requestedBy: text('requested_by').notNull(),
  approved: boolean('approved').default(false),
  rejected: boolean('rejected').default(false),
  createdAt: timestamp('created_at').defaultNow(),
});

track_requests

This was introduced to store raw links before approval. This lets moderators inspect the validity of the links before deciding to convert them into song_requests.

export const trackRequests = pgTable('track_requests', {
  id: text('id').primaryKey().default(sql`gen_random_uuid()`),
  link: text('link').notNull(),
  requestedBy: text('requested_by').notNull(),
  status: text('status').default('pending'), // pending | approved | rejected
  createdAt: timestamp('created_at').defaultNow(),
});

user_roles

This table manages roles and permissions. Each Discord user who interacts with the site is stored here, and their role (isModerator or isBanned) determines what parts of the system they can access.

export const userRoles = pgTable('user_roles', {
  id: text('id').primaryKey(), // Discord ID
  username: text('username'),
  isModerator: boolean('is_moderator').default(false),
  isBanned: boolean('is_banned').default(false),
  createdAt: timestamp('created_at').defaultNow(),
});

All these tables are indexed appropriately to ensure fast lookups during real-time UI updates and server-to-server validations.

User Role Management

To ensure secure access to moderation tools and prevent abuse of the request system, we implemented a robust user role management system using a user_roles table in the PostgreSQL database.

This table tracks whether each user is a moderator and whether they are banned from submitting songs. Every time a user interacts with the site (except on public routes), we check if their Discord ID exists in the database and insert or update it accordingly. This happens using a shared utility that reads the discord_user cookie.

Moderators are granted special access to moderation routes and panels, such as approving/rejecting song requests and accessing the /admin/users dashboard. Banned users are blocked from accessing the /request-song page and submitting new requests via the public API. These roles are enforced both in the frontend and backend API logic.

Here’s an example of how we check user roles inside a server route:

const cookieStore = await cookies();
const cookie = cookieStore.get('discord_user');
const user = cookie ? JSON.parse(cookie.value) : null;


const [role] = await db
  .select()
  .from(userRoles)
  .where(eq(userRoles.id, user.id))
  .limit(1);


if (!role?.isModerator) {
  return NextResponse.json({ error: 'Unauthorized' }, { status: 403 });
}

This consistent role-checking logic has helped us tightly control what users can do across the app.

Admin Panel Functionality

The admin panel (/admin/users) provides moderators with tools to manage users and their roles within the system. Only authenticated Discord users who are marked as isModerator: true in the database can access this page. This authorization logic is handled on both the client and server sides using cookies and role-checking API routes.

Upon accessing the admin panel, moderators are presented with a searchable and filterable table of users stored in the user_roles database table. Each row displays the Discord username, ID, moderator status, and ban status of a user.

Each user entry includes two interactive buttons:

  • Toggle Mod: This button allows an admin to promote or demote a user by toggling the isModerator boolean field.
  • Ban/Unban: This button toggles the isBanned flag, restricting banned users from accessing protected parts of the system like the /request-song page or submitting track requests.

All updates are handled via a PATCH request to /api/users/[id]/role, which checks that at least one valid role update field is provided and updates the corresponding user in the database. The system responds with a success or failure message that updates the UI accordingly.

Example client-side usage:

await fetch('/api/users/123456789/role', {
  method: 'PATCH',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ isModerator: true }),
});

This structured admin control system ensures only authorized users can manage user privileges and keep the submission system safe from abuse.

User Permissions & Moderation

Managing access is a central aspect of this project. We’ve implemented a robust permissions system using a PostgreSQL database to determine who can moderate requests and who is banned from submitting them.

Database Model

Permissions are stored in the user_roles table. Each entry is identified by the user’s Discord ID and contains flags for isModerator and isBanned.

export const userRoles = pgTable('user_roles', {
  id: text('id').primaryKey(), // Discord ID
  username: text('username'),
  isModerator: boolean('is_moderator').default(false),
  isBanned: boolean('is_banned').default(false),
  createdAt: timestamp('created_at').defaultNow(),
});

Automatic Insertion

When a user accesses any protected page or submits a song request, their data is automatically inserted or updated in the user_roles table. This ensures we always have an up-to-date record of users and can enforce bans or grant mod access in real-time.

Moderator Verification

For protected endpoints like approving or rejecting song requests, a check is made against the user’s isModerator field:

const [role] = await db
  .select()
  .from(userRoles)
  .where(eq(userRoles.id, user.id))
  .limit(1);


if (!role?.isModerator) {
  return NextResponse.json({ error: 'Unauthorized (not allowed)' }, { status: 403 });
}

This ensures that only authorized moderators can use moderation tools, whether through the frontend or an external client.

Admin UI Tools

Through the /admin/users panel, admins can toggle a user’s moderator status or ban/unban them using simple buttons. This updates the database instantly and reflects across the whole system.

All of this builds toward a smooth, self-maintaining ecosystem for managing permissions.

Admin Panel for User Roles

The admin panel, accessible at /admin/users, serves as the control center for user role management. This panel is restricted to users who are marked as isModerator: true in the userRoles table, ensuring only trusted individuals can manage access control.

When loaded, the admin panel fetches all registered users from the database using the GET /api/users endpoint. Each row in the table displays the user’s Discord username, ID, and their current moderator and banned status. If no username is available, the system displays "Unknown" to maintain layout consistency.

The interface features two key tools:

  • A Toggle Mod button that updates the user’s isModerator status.
  • A Ban/Unban button that toggles the isBanned status.

Under the hood, each action triggers a PATCH request to /api/users/[id]/role with a JSON body like:

{
  "isModerator": true
}

or

{
  "isBanned": true
}

The update is performed with Drizzle ORM, and proper validation ensures only valid fields are updated.

Search & Filter Features

To improve usability with large user datasets, the admin panel supports:

  • Search by username or ID (case-insensitive match)
  • Filter by role (e.g., show only banned users or moderators)

These features work client-side and do not require additional API calls, providing a snappy user experience.

This level of admin control was essential to replace the previous static .env environment variable-based permission system. Now, role control is dynamic, easily managed in the UI, and persists across user sessions.

API Routes Overview

Your project exposes a variety of RESTful API endpoints to handle everything from authentication and song submissions to role management and moderation workflows. Each route has clearly defined logic and is organized in the /api directory of your Next.js App Router project.

GET /api/user

Returns the currently authenticated Discord user based on the discord_user cookie. Used by client pages to determine identity and permissions.

{
  "id": "123456789",
  "username": "wthlaw"
}

POST /api/spotify/submit-link

Handles public song requests by parsing a Spotify URL, validating the track, and inserting a new record into the songRequests table. Includes rate limiting based on IP.

Payload

{
  "link": "https://open.spotify.com/track/xyz123",
  "requestedBy": "wthlaw"
}

Response

{ "success": true }

GET /api/spotify/requests

Returns all song requests from the database, ordered by creation date. Requires the user to be a moderator. If the user is not a moderator, the response is a 403 Unauthorized error.

PATCH /api/spotify/requests

Used by the moderation panel to approve or reject songs. When a track is approved, the server attempts to add it to the Spotify playback queue using the access token in the spotifyTokens table.

Payload

{
  "id": "uuid-of-request",
  "action": "approve" // or "reject"
}

Response

{ "success": true }

GET /api/users

Returns all users in the userRoles table. This route powers the admin panel’s list of users.

[
  {
    "id": "123456789",
    "username": "mod_user",
    "isModerator": true,
    "isBanned": false
  }
]

GET /api/users/[id]/role

Fetches a specific user’s role info by their Discord ID. Used throughout the UI to determine access permissions.

{
  "id": "123456789",
  "username": "mod_user",
  "isModerator": true,
  "isBanned": false
}

PATCH /api/users/[id]/role

Allows moderators or admins to update a user’s role. This includes toggling the isModerator or isBanned flags.

Payload

{
  "isModerator": true,
  "isBanned": false
}

Response

{ "success": true }

Each route in this system plays a part in maintaining a smooth full-stack workflow for public song requests and moderator/admin controls. The routes follow conventional REST principles, and errors are consistently handled with user-friendly messages or appropriate status codes.

Logging, Errors, and Rate Limits

In any full-stack application that interacts with third-party APIs and supports real-time user input, implementing robust logging, error handling, and request throttling is essential. Your Spotify song request system takes these needs seriously by introducing thoughtful logic at the API layer to prevent abuse and inform users when something goes wrong.

IP-Based Rate Limiting

To prevent spam submissions and abuse of the /api/spotify/submit-link route, a simple in-memory rate limiter is implemented using a JavaScript Map. This tracks IP addresses and timestamps, enforcing a 5-second cooldown between requests.

const rateLimitMap = new Map<string, number>();


function isRateLimited(ip: string): boolean {
  const now = Date.now();
  const last = rateLimitMap.get(ip) || 0;
  if (now - last < 5000) return true;
  rateLimitMap.set(ip, now);
  return false;
}

This ensures that casual users can’t flood the server with requests, while also providing basic protection against bots. While this approach works for single-instance deployments (like Vercel), scaling would require a distributed solution such as Redis.

Spotify Token Handling & Refresh Logic Your app makes authenticated calls to the Spotify Web API using access tokens stored in the spotifyTokens table. These tokens can expire, so your system includes logic to detect expired tokens (401 errors) and automatically refresh them.

if (err.response?.status === 401) {
  const newToken = await refreshAccessToken();
  if (!newToken) {
    return NextResponse.json({ error: 'Token refresh failed' }, { status: 401 });
  }
  return await POST(req); // Retry the request after refreshing
}

This retry pattern ensures that user requests aren’t rejected just because a token expired in the background. The system remains resilient and self-healing during runtime.

User Feedback on Errors Throughout the API layer, descriptive JSON error responses are returned. These include custom messages like:

“Too many requests. Please wait.” for rate limiting

“Invalid Spotify track link” for malformed URLs

“Failed to fetch track info” if the Spotify API returns an unexpected error

This consistent response format ensures that frontend pages (like /request-song or /requests) can display meaningful error states to users instead of generic “Something went wrong” alerts.

Console Logging for Debugging During local development, errors are logged to the console with console.error. For example:

console.error('Error fetching Spotify track:', err.message);

This helps surface Spotify API issues or database problems during debugging. If deployed in production, logs can be piped to external systems like Vercel Analytics, Logtail, or a custom webhook for alerting.

By combining rate limiting, retry logic, and structured error responses, your app provides a reliable and secure experience for both users and moderators. This layer of protection keeps your Spotify integration smooth and your moderation workflow clean.

Final Thoughts

Building this Spotify song request system has been a deep dive into full-stack integration, OAuth flows, real-time moderation, and custom streaming tools. Every feature—whether it’s role-based user permissions or track approval via a clean moderator interface—was built to solve real problems streamers face with song requests.

What started as a small form to drop Spotify links evolved into a fully permissioned system with:

  • Real Discord authentication via OAuth
  • Moderator-level control panels
  • Rate-limited, persistent song queues
  • Live Spotify API interactions
  • A clean and responsive UI powered by Tailwind and App Router
  • API routes that handle everything from inserting DB rows to pushing songs to your actual queue

Whether you’re running this for a Twitch stream, embedding it into a dashboard, or powering a custom Discord bot—this system gives you full control while keeping the backend logic solid and secure.

GitHub Repository

You can find the full source code here:

🔗 github.com/oyuh/streamthing

Feel free to fork it, star it, deploy it to Vercel, or customize it for your own community. All the environment variables, SQL setup scripts, and blog documentation are included so you can get started fast.

Deploy & Extend

Want to deploy your own version?

  1. Clone the repo
  2. Set up a Neon PostgreSQL database
  3. Add your .env variables for Discord and Spotify
  4. Deploy it to Vercel
  5. Done. You’re live.

Contributions Welcome

This project is evolving, and there are plenty of ideas to improve it—real-time song queues, listener analytics, or even a mobile-friendly controller for streamers. If you’ve got ideas, bug fixes, or feature requests, open an issue or PR on GitHub.

Thanks for reading, building, and shipping. See you on stream.