SQL Master

A modern SQL preparation platform: practice problems, real-world case studies, interview questions, and data-engineering system-design walkthroughs — each with a guided approach and complete solution. Built with the latest tech stack for a fast, delightful experience.

Features

500 Practice Problems — Easy / Medium / Hard, from basic SELECTs to advanced window functions, recursive CTEs, JSON, A/B tests, fraud detection, time-series, and more. Each has an in-browser SQL playground with real test cases.
100 Case Studies — Real business scenarios (e-commerce, SaaS, fintech, healthcare, marketplaces) with a "Thinking Process" and complete solution.
170 Interview Questions — Conceptual + code-answer prep on joins, indexes, transactions, isolation, replication, partitioning, MVCC, and everything else that comes up.
140 Data-Engineering System Design Problems — Full architectures for warehouses, streaming pipelines, ML platforms, and FAANG-tagged interview asks (DoorDash, Uber, Airbnb, TikTok, Snap, Spotify, etc.). Each includes clarifying questions, key parameters, and a complete reference architecture.
In-browser SQL Playground — Run queries against seeded SQLite databases via sql.js (WebAssembly). No backend needed.
Progress Tracking — Solved / attempted counts, streaks, activity log, and category-level progress bars in the dashboard.
Bookmarks — Save questions for revision.
Row Level Security — All user tables enforce per-user isolation via Supabase RLS.
Dark / Light Mode — Auto-follows system, manually toggleable.
Topic & Difficulty Filters — Quickly narrow down what you want to practice.

Tech Stack

Layer	Choice
Framework	Next.js 15 (App Router) + React 19
Language	TypeScript 5.7 (strict)
Styling	Tailwind CSS v3 + shadcn/ui
Database	Supabase Postgres
ORM	Drizzle ORM
Auth	Supabase Auth (email + magic link)
SQL Playground	`sql.js` (SQLite in WebAssembly)
Code Editor	Monaco Editor
Deployment	Vercel

Getting Started

1. Install dependencies

npm install

postinstall copies the sql.js WASM files into public/.

2. Create a Supabase project

Go to supabase.com and create a free project.
From Settings → API copy:
- Project URL
- anon public key
From Settings → Database → Connection string copy the Transaction Pooler URI (port 6543) — this is what you should use in production on Vercel to avoid exhausting the connection pool.

3. Configure environment variables

cp .env.example .env.local

Fill in:

NEXT_PUBLIC_SUPABASE_URL=https://YOUR-PROJECT.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key
DATABASE_URL=postgresql://postgres.YOUR-PROJECT:PASSWORD@aws-0-REGION.pooler.supabase.com:6543/postgres

DATABASE_URL is server-only. It bypasses RLS by design — API routes filter by user.id from auth.getUser() before returning rows. Never import lib/db from a client component.

4. Push the database schema

npm run db:push

Creates all tables (profiles, questions, bookmarks, progress, notes, streaks, activity_log) plus enums.

5. Apply Row Level Security policies (required)

npm run db:rls

Enables RLS on every table and installs per-user policies plus the on_auth_user_created trigger that auto-creates a profiles row on signup. Verify with:

npm run db:verify-rls

6. Seed the content into the database (required)

npm run db:seed

Upserts all ~910 MDX files (500 problems + 100 case studies + 170 interview + 140 system-design) into the questions table. The API routes look up questions by slug, so the app will 404 on Submit/Bookmark until this is run.

Re-run any time you add new content files.

7. Run the dev server

npm run dev

Open http://localhost:3000.

Project Structure

sql-prep/
├── app/                          # Next.js App Router
│   ├── problems/                 # Practice problems list + detail
│   ├── case-studies/             # Case studies list + detail
│   ├── interview/                # Interview questions list + detail
│   ├── system-design/            # System-design list + detail
│   ├── dashboard/                # User progress dashboard
│   ├── bookmarks/                # Saved questions
│   ├── sign-in/, sign-up/        # Auth pages
│   ├── auth/callback/            # OAuth/magic-link callback
│   ├── api/
│   │   ├── bookmarks/            # REST API for bookmarks
│   │   ├── progress/             # REST API for solve/review tracking
│   │   └── health/               # Health probe for uptime checks
│   ├── error.tsx                 # Route-level error boundary
│   ├── global-error.tsx          # Global error boundary
│   ├── not-found.tsx
│   ├── layout.tsx
│   └── page.tsx                  # Landing page
├── components/
│   ├── ui/                       # shadcn/ui primitives
│   ├── content-detail.tsx        # Detail page shell w/ tabs + playground
│   ├── sql-editor.tsx            # Monaco + sql.js playground
│   ├── mark-reviewed-button.tsx  # Progress marker for non-playground items
│   └── ...
├── content/
│   ├── problems/*.mdx            # 500 problems
│   ├── case-studies/*.mdx        # 100 case studies
│   ├── interview/*.mdx           # 170 interview questions
│   └── system-design/*.mdx       # 140 system-design problems
├── lib/
│   ├── db/                       # Drizzle schema + client
│   ├── supabase/                 # SSR + browser + middleware clients
│   ├── content.ts                # MDX loader & parser
│   ├── sql-runner.ts             # sql.js wrapper
│   ├── user-data.ts              # Server-side progress/stats helpers
│   └── utils.ts                  # Includes safeRedirectPath()
├── drizzle/
│   └── rls.sql                   # RLS policies + signup trigger
├── scripts/
│   ├── copy-wasm.mjs             # Runs on postinstall
│   ├── seed-questions.mjs        # Content -> DB
│   ├── apply-rls.mjs             # RLS bootstrap
│   ├── verify-rls.mjs            # RLS status check
│   └── add-system-design-enum.mjs
├── middleware.ts                 # Auth-gates all non-public routes
├── drizzle.config.ts
├── tailwind.config.ts
├── next.config.mjs               # Security headers included
└── package.json

Adding More Content

Each piece of content is a single MDX file. The frontmatter defines metadata; the body has three sections. After adding files, run npm run db:seed to sync to the database.

Problems (`content/problems/*.mdx`)

---
title: "Your Problem Title"
difficulty: easy | medium | hard
topic: "Joins"                    # any string, used for filtering
tags: ["INNER JOIN", "GROUP BY"]  # optional array
description: "Short one-liner"
schema: |                         # optional; enables the playground
  CREATE TABLE ...
  INSERT INTO ...
expectedOutput:                   # optional; enables Submit validation
  columns: [id, name]
  rows:
    - [1, "Alice"]
    - [2, "Bob"]
---

## Problem
The problem statement...

## Approach
1. First think about...
2. Then use...

## Solution
```sql
SELECT ...


### Case Studies (`content/case-studies/*.mdx`)

Same shape, tabs labeled "Thinking Process" and "Solution". Typically omit `difficulty` and playground schema.

### Interview Questions (`content/interview/*.mdx`)

Same shape, tabs labeled "Basic Approach" and "Full Answer". Playground is optional.

### System Design (`content/system-design/*.mdx`)

Same shape but the solution section is headed `## Architecture` (both are recognized). Playground is disabled; users mark items as Reviewed instead of Solved.

## Available Scripts

| Script                          | Purpose                                                            |
| ------------------------------- | ------------------------------------------------------------------ |
| `npm run dev`                   | Start dev server on port 3000                                      |
| `npm run build`                 | Production build                                                   |
| `npm run start`                 | Serve the production build                                         |
| `npm run lint`                  | Run ESLint                                                         |
| `npm run db:generate`           | Generate a Drizzle migration from schema.ts                        |
| `npm run db:push`               | Push schema directly to Supabase                                   |
| `npm run db:studio`             | Open Drizzle Studio (visual DB browser)                            |
| `npm run db:rls`                | Apply RLS policies + signup trigger                                |
| `npm run db:verify-rls`         | Verify RLS is enabled on every table                               |
| `npm run db:seed`               | Sync all MDX content into the `questions` table                    |
| `npm run db:add-system-design`  | One-off migration to add `system_design` to the content_type enum  |

## Deployment (Vercel)

1. Push to a GitHub repo.
2. Import the repo in Vercel.
3. In **Project Settings → Environment Variables**, add:
   - `NEXT_PUBLIC_SUPABASE_URL`
   - `NEXT_PUBLIC_SUPABASE_ANON_KEY`
   - `DATABASE_URL` — **use the Supabase Transaction Pooler URL (port 6543)** for serverless environments. The Session Pooler (5432) can exhaust connections quickly under a burst of cold starts.
4. Deploy.
5. From your local machine (with the same `.env.local`), run against the production database:
   ```bash
   npm run db:push        # if not already pushed
   npm run db:rls         # required
   npm run db:seed        # required — populates all 910 questions

Detail pages are declared export const dynamic = "force-dynamic" so user-specific "Solved / Reviewed" state is always fresh.

Security notes

All user tables have RLS enabled with per-user policies (see drizzle/rls.sql).
Every /api/* route calls supabase.auth.getUser() and scopes writes/reads by user.id.
The middleware redirects unauthenticated users to /sign-in for every non-public route.
Redirect params (?redirect=...) are validated by safeRedirectPath() in lib/utils.ts to prevent open-redirect attacks.
Markdown rendering escapes all user-supplied text; code-fence language attributes are strictly [a-zA-Z0-9_-].
API body payloads (slug, query) are length- and format-validated.
Security headers (X-Frame-Options, X-Content-Type-Options, Referrer-Policy, Permissions-Policy) are set in next.config.mjs.

Roadmap

500 problems / 100 case studies / 170 interview questions / 140 system-design walkthroughs
RLS on every table + signup trigger
Progress tracking (solve / review) + streaks + activity log
Local bookmarks with best-effort DB sync
Server-authoritative bookmarks (cross-device sync on read)
Rate limiting on /api/progress and /api/bookmarks
Full-text search across all content
PDF export of bookmarks for offline revision
Weekly challenges & leaderboards

License

MIT — see LICENSE.

Navigation Menu

Repository files navigation

SQL Master

Features

Tech Stack

Getting Started

1. Install dependencies

2. Create a Supabase project

3. Configure environment variables

4. Push the database schema

5. Apply Row Level Security policies (required)

6. Seed the content into the database (required)

7. Run the dev server

Project Structure

Adding More Content

Problems (`content/problems/*.mdx`)

Security notes

Roadmap

License

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
app		app
components		components
content		content
drizzle		drizzle
lib		lib
public		public
scripts		scripts
.dockerignore		.dockerignore
.env.example		.env.example
.eslintrc.json		.eslintrc.json
.gitignore		.gitignore
Dockerfile		Dockerfile
LICENSE		LICENSE
README.md		README.md
components.json		components.json
drizzle.config.ts		drizzle.config.ts
middleware.ts		middleware.ts
next.config.mjs		next.config.mjs
package-lock.json		package-lock.json
package.json		package.json
postcss.config.mjs		postcss.config.mjs
tailwind.config.ts		tailwind.config.ts
tsconfig.json		tsconfig.json

Folders and files

Latest commit

History

Repository files navigation

SQL Master

Features

Tech Stack

Getting Started

1. Install dependencies

2. Create a Supabase project

3. Configure environment variables

4. Push the database schema

5. Apply Row Level Security policies (required)

6. Seed the content into the database (required)

7. Run the dev server

Project Structure

Adding More Content

Problems (content/problems/*.mdx)

Security notes

Roadmap

License

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

Problems (`content/problems/*.mdx`)