Full Next.js + Node.js + PostgreSQL Interview Task Setup

RIs for flexibility.

api-gateway/src/config/database.ts

import { Sequelize } from 'sequelize';
import dotenv from 'dotenv';

dotenv.config();

const sequelize = new Sequelize({
  dialect: 'postgres',
  host: process.env.DB_HOST || 'localhost',
  port: Number(process.env.DB_PORT) || 5432,
  database: process.env.DB_NAME || 'org_management',
  username: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || '',
  logging: process.env.NODE_ENV === 'development' ? console.log : false,
  pool: {
    max: 10,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
});

export default sequelize;

Why this choice: Connection pooling is configured to handle concurrent requests efficiently. Logging is disabled in production to reduce noise and improve performance.

3. Model Definition and Migration Strategy

Define models that reflect the domain. Use sequelize-cli to generate migrations, ensuring schema changes are version-controlled.

Generate Model:

npx sequelize-cli model:generate --name Member --attributes username:string,email:string,password_hash:string,role:enum

api-gateway/src/models/member.ts

import { Model, DataTypes, Optional } from 'sequelize';
import sequelize from '../config/database';

interface MemberAttributes {
  id: number;
  username: string;
  email: string;
  password_hash: string;
  role: 'admin' | 'member';
}

interface MemberCreationAttributes extends Optional<MemberAttributes, 'id'> {}

class Member extends Model<MemberAttributes, MemberCreationAttributes> implements MemberAttributes {
  public id!: number;
  public username!: string;
  public email!: string;
  public password_hash!: string;
  public role!: 'admin' | 'member';
}

Member.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
    username: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: { isEmail: true },
    },
    password_hash: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    role: {
      type: DataTypes.ENUM('admin', 'member'),
      defaultValue: 'member',
    },
  },
  {
    sequelize,
    modelName: 'Member',
    tableName: 'members',
    timestamps: true,
  }
);

export default Member;

Architecture Decision: The model uses interfaces for TypeScript support. Validation is defined at the model level to catch data integrity issues early. The password_hash naming convention explicitly indicates that plaintext passwords are never stored.

4. Authentication Middleware and Controllers

Implement a token-based guard and a controller that handles credential verification securely.

api-gateway/src/middleware/tokenGuard.ts

import { Request, Response, NextFunction } from 'express';
import jwt from 'jsonwebtoken';

interface AuthRequest extends Request {
  member?: { id: number; role: string };
}

export const verifyToken = (req: AuthRequest, res: Response, next: NextFunction) => {
  const authHeader = req.headers.authorization;
  const token = authHeader?.startsWith('Bearer ') ? authHeader.split(' ')[1] : null;

  if (!token) {
    return res.status(401).json({ error: 'Access denied. No token provided.' });
  }

  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET as string) as { id: number; role: string };
    req.member = decoded;
    next();
  } catch (error) {
    res.status(403).json({ error: 'Invalid or expired token.' });
  }
};

api-gateway/src/controllers/sessionController.ts

import { Request, Response } from 'express';
import bcrypt from 'bcryptjs';
import jwt from 'jsonwebtoken';
import Member from '../models/member';

export const authenticate = async (req: Request, res: Response) => {
  try {
    const { email, password } = req.body;
    const member = await Member.findOne({ where: { email } });

    if (!member) {
      return res.status(400).json({ error: 'Account not found.' });
    }

    const isMatch = await bcrypt.compare(password, member.password_hash);
    if (!isMatch) {
      return res.status(400).json({ error: 'Invalid credentials.' });
    }

    const token = jwt.sign(
      { id: member.id, role: member.role },
      process.env.JWT_SECRET as string,
      { expiresIn: '1h' }
    );

    res.json({ token, member: { id: member.id, username: member.username, role: member.role } });
  } catch (error) {
    res.status(500).json({ error: 'Authentication service unavailable.' });
  }
};

Best Practice: The JWT payload includes only essential claims (id, role). The expiration is set to 1 hour to limit the window of exposure if a token is compromised. bcrypt.compare is used asynchronously to prevent blocking the event loop.

5. Frontend API Client and Routing

The frontend must use Next.js App Router patterns. Client-side routing libraries are incompatible with the App Router's server-first architecture.

org-portal/lib/api-client.ts

import axios from 'axios';

const apiClient = axios.create({
  baseURL: process.env.NEXT_PUBLIC_API_URL,
  headers: { 'Content-Type': 'application/json' },
});

apiClient.interceptors.request.use((config) => {
  if (typeof window !== 'undefined') {
    const token = localStorage.getItem('auth_token');
    if (token) {
      config.headers.Authorization = `Bearer ${token}`;
    }
  }
  return config;
});

export default apiClient;

org-portal/app/dashboard/page.tsx

import { redirect } from 'next/navigation';
import apiClient from '@/lib/api-client';

export default async function DashboardPage() {
  // In a real app, verify session via httpOnly cookie or secure storage
  const token = 'server-side-session-check'; 
  
  if (!token) {
    redirect('/login');
  }

  return (
    <div className="p-6">
      <h1 className="text-2xl font-bold">Organization Dashboard</h1>
      <p>Secure content loaded via Server Component.</p>
    </div>
  );
}

Critical Correction: The source material incorrectly suggested react-router-dom for Next.js. This rewrite uses next/navigation and file-based routing (app/dashboard/page.tsx), which is the correct pattern for Next.js 13+. This ensures proper SEO, caching, and server-side rendering.

Pitfall Guide

Pitfall Name	Explanation	Fix
Routing Schism	Mixing react-router-dom with Next.js App Router causes hydration errors and breaks server components.	Use next/navigation for programmatic navigation and file-based routing for structure. Remove react-router-dom entirely.
Secret Sprawl	Hardcoding JWT_SECRET or DB passwords in source code leads to immediate security failures in reviews.	Use .env files exclusively. Commit .env.example with placeholders. Add .env to .gitignore. Validate env vars on startup.
Plaintext Credentials	Storing passwords directly in the database allows credential theft if the DB is breached.	Always hash passwords using bcryptjs with a salt round of at least 10. Store only the hash.
CORS Wildcards	Setting origin: '*' in Express CORS allows any website to make requests to your API, enabling CSRF attacks.	Configure CORS with a whitelist of allowed origins: origin: process.env.FRONTEND_URL.
Migration Blindness	Running migrations without a rollback strategy leaves the database in an inconsistent state during failures.	Implement db:migrate:undo scripts. Write idempotent migrations where possible. Test migrations in a staging environment.
Token Storage Vulnerability	Storing JWTs in localStorage exposes them to XSS attacks.	For production, use httpOnly cookies. For interview tasks, acknowledge the risk and implement XSS mitigation (e.g., CSP headers).
N+1 Query Problem	Fetching a list of members and then querying their logs individually causes performance degradation.	Use Sequelize include for eager loading or write raw SQL joins. Monitor query counts in development logging.

Production Bundle

Action Checklist

• Environment Validation: Ensure all required .env variables are defined and non-empty before starting the server.
• Database Seeding: Create a seed script to populate the database with a default admin user for testing.
• CORS Verification: Test API access from the frontend URL and verify that unauthorized origins are rejected.
• Migration Integrity: Run npx sequelize-cli db:migrate:status to confirm all migrations are applied.
• Auth Flow Test: Verify login returns a token, protected routes reject requests without tokens, and logout clears client state.
• Error Handling: Ensure the API returns consistent error JSON structures (e.g., { error: "message" }) rather than stack traces.
• Type Safety: Run tsc --noEmit in both frontend and backend to catch type mismatches before runtime.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Rapid Prototyping	Sequelize ORM	Fast development, built-in validation, migration management.	Slight performance overhead vs raw SQL.
Complex Analytics	Raw SQL / Views	Fine-grained control over query execution plans and aggregations.	Higher development time, requires SQL expertise.
Public-Facing API	JWT + Refresh Tokens	Stateless authentication scales horizontally; refresh tokens mitigate short-lived access token risks.	Increased client-side complexity for token rotation.
Internal Admin Tool	Session / HttpOnly Cookies	Simpler security model; tokens are not exposed to JavaScript, reducing XSS risk.	Requires server-side session storage or signed cookies.
Multi-Tenant SaaS	Schema-per-tenant or Row-level Security	Data isolation is critical; RLS in PostgreSQL offers strong security with lower operational overhead.	Schema-per-tenant increases DB resource usage.

Configuration Template

docker-compose.yml Use Docker to standardize the development environment and eliminate "works on my machine" issues.

version: '3.8'
services:
  db:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: org_management
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: dev_password_123
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  api:
    build: ./api-gateway
    ports:
      - "3001:3001"
    environment:
      DB_HOST: db
      DB_NAME: org_management
      DB_USER: postgres
      DB_PASSWORD: dev_password_123
      JWT_SECRET: super_secret_key_for_dev
    depends_on:
      - db

volumes:
  pgdata:

.env.example

# Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=org_management
DB_USER=postgres
DB_PASSWORD=your_secure_password

# Security
JWT_SECRET=generate_a_long_random_string_here
NODE_ENV=development

# Frontend
NEXT_PUBLIC_API_URL=http://localhost:3001/api

Quick Start Guide

1. Initialize Infrastructure:

   docker compose up -d db
   

2. Setup Backend:

   cd api-gateway
   npm install
   cp .env.example .env
   npx sequelize-cli db:migrate
   npm run dev
   

3. Setup Frontend:

   cd org-portal
   npm install
   cp .env.example .env
   npm run dev
   

4. Verify Integration: Open http://localhost:3000 in your browser. The frontend should connect to the backend API running on port 3001. Use a tool like Postman or the frontend login form to test the authentication flow.

This scaffold provides a defensible, production-aligned foundation for technical assessments. It demonstrates mastery of framework conventions, security best practices, and database management, positioning the candidate as a senior engineer capable of delivering robust systems.