A full-stack web application for exploring, reviewing, and managing art collections. Users can browse artwork, write reviews, save personal notes, mark favorites, and track their art viewing history.

This project was developed for Introduction to Databases, CS-UY 3083 at New York University (NYU).

• User Authentication - Secure signup/login with JWT tokens
• Art Gallery - Browse and explore artwork with infinite scroll pagination
• Reviews & Notes - Write public reviews and private personal notes
• Favorites - Save and manage favorite artworks
• Search - Search for artworks
• User Profiles - View and manage user profiles
• Art Tracking - Track which artworks you've seen

• React 18 - UI framework
• Vite - Build tool and dev server
• React Router - Client-side routing
• CSS Modules - Component styling

• Node.js - Runtime environment
• Express 5 - Web framework
• PostgreSQL - Database
• JWT - Authentication tokens
• bcrypt - Password hashing
• Winston - Logging

• PostgreSQL - Relational database
• Supabase - Database hosting platform

Before you begin, ensure you have the following installed:

• Node.js (v14 or higher)
• npm (v6 or higher)
• Chromium-based browser (Chrome, Edge, or Chromium) - The website was tested on these browsers

git clone https://github.com/TheChosenO1/DBProject.git
cd DBProject

Note: On some systems, you may need to use sudo for npm commands.

cd backend
npm install

Create a .env file in the backend directory with the following variables:

DATABASE_URL=postgresql://postgres.jkgxskamswvokavjljsm:AaravIshitaAdel@aws-0-us-east-2.pooler.supabase.com:6543/postgres
JWT_SECRET=AaravIshitaAdel
JWT_EXPIRES_IN=15d
PORT=4000

Important Notes:

• The DATABASE_URL connects to a PostgreSQL database hosted on Supabase
• JWT_SECRET is used to sign authentication tokens
• JWT_EXPIRES_IN can be adjusted (e.g., 1h, 7d, 30d)
• PORT is optional (defaults to 4000)

cd ../frontend
npm install

The project requires two separate terminals to run both the backend and frontend servers.

cd backend
npm run dev

The backend server will start on http://localhost:4000

cd frontend
npm run dev

The frontend server will start on http://localhost:5173 (Vite default port)

Open your Chromium-based browser (Edge, Chrome, or Chromium) and navigate to:

http://localhost:5173

Important: If you have any processes running on port 4000 or port 5173, the website may not work as expected due to CORS configuration. Check and kill any processes on these ports before starting.

On macOS/Linux:

# Find processes on ports
lsof -i :4000
lsof -i :5173

# Kill process (replace PID with actual process ID)
kill -9 PID

On Windows:

# Find processes on ports
netstat -ano | findstr :4000
netstat -ano | findstr :5173

# Kill process (replace PID with actual process ID)
taskkill /PID PID /F

ChateauNYC/
├── backend/
│   ├── src/
│   │   ├── controllers/     # Request handlers
│   │   ├── db/              # Database connection pool
│   │   ├── middleware/      # Authentication middleware
│   │   ├── routes/          # API route definitions
│   │   ├── services/        # Business logic layer
│   │   ├── utils/           # Utilities (JWT, hashing, logging)
│   │   ├── scripts/         # Utility scripts
│   │   ├── __tests__/       # Test files
│   │   └── index.js         # Server entry point
│   └── package.json
├── frontend/
│   ├── src/
│   │   ├── components/      # Reusable React components
│   │   ├── context/         # React Context (Auth state)
│   │   ├── pages/           # Page components
│   │   ├── services/        # API service layer
│   │   ├── styles/          # Global CSS styles
│   │   ├── App.jsx          # Main app component with routing
│   │   └── main.jsx         # React entry point
│   └── package.json
└── README.md

Note: Some directories may be empty or partially organized as the project was restructured during development.

We are hosting our PostgreSQL database server on Supabase. The database connection is configured through the DATABASE_URL environment variable in the backend .env file.

The database uses PostgreSQL triggers and functions to automatically maintain data consistency and update related tables when entries are added or deleted.

When a user adds a new favorite, this trigger automatically updates the favorites count in the Popularity table.

CREATE OR REPLACE FUNCTION increment_fav_count()
RETURNS TRIGGER AS $$
BEGIN
   UPDATE public.Popularity
   SET Favourites_Count = Favourites_Count + 1
   WHERE ArtworkID = NEW.ArtworkID;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_increment_fav_count
AFTER INSERT ON public.Favorites
FOR EACH ROW
EXECUTE FUNCTION increment_fav_count();

When a new artwork is added, this trigger automatically creates a corresponding entry in the Popularity table with initialized values.

CREATE OR REPLACE FUNCTION insert_into_popularity()
RETURNS TRIGGER AS $$
BEGIN
   INSERT INTO public.Popularity (ArtworkID, View_Count, Favourites_Count, Average_Rating)
   VALUES (NEW.ArtworkID, 0, 0, NULL);
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_add_popularity_entry
AFTER INSERT ON public.Artworks
FOR EACH ROW
EXECUTE FUNCTION insert_into_popularity();

When a review is inserted, updated, or deleted, this trigger recalculates and updates the average rating for the artwork.

CREATE OR REPLACE FUNCTION update_average_rating()
RETURNS trigger AS $$
BEGIN
   UPDATE public.Popularity p
   SET Average_Rating = sub.avg_rating
   FROM (
       SELECT ArtworkID, AVG(Rating) AS avg_rating
       FROM public.Reviews
       GROUP BY ArtworkID
   ) AS sub
   WHERE p.ArtworkID = sub.ArtworkID;
   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_avg_rating
AFTER INSERT OR UPDATE OR DELETE ON public.Reviews
FOR EACH ROW
EXECUTE FUNCTION update_average_rating();

When an artwork is marked as seen, this trigger increments the view count in the Popularity table.

CREATE OR REPLACE FUNCTION increment_view_count()
RETURNS TRIGGER AS $$
BEGIN
   UPDATE public.Popularity
   SET View_Count = View_Count + 1
   WHERE ArtworkID = NEW.ArtworkID;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_increment_view_count
AFTER INSERT ON public.Artwork_seen
FOR EACH ROW
EXECUTE FUNCTION increment_view_count();

When a user removes an artwork from favorites, this trigger decrements the favorites count.

CREATE OR REPLACE FUNCTION decrement_fav_count()
RETURNS TRIGGER AS $$
BEGIN
   UPDATE public.Popularity
   SET Favourites_Count = Favourites_Count - 1
   WHERE ArtworkID = OLD.ArtworkID;
   RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_decrement_fav_count
AFTER DELETE ON public.Favorites
FOR EACH ROW
EXECUTE FUNCTION decrement_fav_count();

Row-Level Security (RLS) is enabled on all tables to ensure that only authenticated users can access the data. The security is designed for end users - there are no admin accounts as all additions/deletions are user-initiated based on their real-world art interactions.

RLS is enabled on the following tables:

• users
• artists
• artworks
• gallery
• museum
• reviews
• artwork_seen
• favorites
• personal_notes
• popularity

-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.artists ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.artworks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.gallery ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.museum ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.reviews ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.artwork_seen ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.favorites ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.personal_notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.popularity ENABLE ROW LEVEL SECURITY;

-- Drop existing policies to avoid errors
DROP POLICY IF EXISTS reviews_view_policy ON public.Reviews;
DROP POLICY IF EXISTS reviews_insert_policy ON public.Reviews;
DROP POLICY IF EXISTS artworks_view_policy ON public.Artworks;
DROP POLICY IF EXISTS artists_view_policy ON public.artists;
DROP POLICY IF EXISTS gallery_view_policy ON public.gallery;
DROP POLICY IF EXISTS museum_view_policy ON public.museum;
DROP POLICY IF EXISTS popularity_view_policy ON public.popularity;
DROP POLICY IF EXISTS personal_notes_policy ON public.personal_notes;

-- Reviews: All authenticated users can view and insert reviews
CREATE POLICY reviews_view_policy ON public.Reviews
FOR SELECT
USING (auth.role() = 'authenticated');

CREATE POLICY reviews_insert_policy ON public.Reviews
FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

-- Artworks: Authenticated users can view artwork data
CREATE POLICY artworks_view_policy ON public.Artworks
FOR SELECT
USING (auth.role() = 'authenticated');

-- Artists: Authenticated users can view artist information
CREATE POLICY artists_view_policy ON public.artists
FOR SELECT
USING (auth.role() = 'authenticated');

-- Gallery: Authenticated users can view gallery information
CREATE POLICY gallery_view_policy ON public.gallery
FOR SELECT
USING (auth.role() = 'authenticated');

-- Museum: Authenticated users can view museum information
CREATE POLICY museum_view_policy ON public.museum
FOR SELECT
USING (auth.role() = 'authenticated');

-- Popularity: Authenticated users can view popularity data
CREATE POLICY popularity_view_policy ON public.popularity
FOR SELECT
USING (auth.role() = 'authenticated');

-- Personal Notes: Authenticated users can view, insert, update, and delete their notes
CREATE POLICY personal_notes_policy ON public.personal_notes
FOR ALL
USING (auth.role() = 'authenticated');

We use JSON Web Tokens (JWT) for secure user authentication. Tokens are signed using a secret key stored in the .env file and expire after 15 days (configurable via JWT_EXPIRES_IN).

const jwt = require('jsonwebtoken');
const SECRET = process.env.JWT_SECRET;
const EXPIRES = process.env.JWT_EXPIRES_IN || '15d';

exports.sign = (payload) => jwt.sign(payload, SECRET, { expiresIn: EXPIRES });
exports.verify = (token) => jwt.verify(token, SECRET);

Tokens are verified on protected routes using middleware that checks for a valid Bearer token in the Authorization header.

User passwords are hashed using bcrypt before storage in the database. This ensures that even if the database is compromised, raw passwords are not exposed.

const bcrypt = require('bcrypt');
exports.hash = async (password) => await bcrypt.hash(password, 10);
exports.compare = async (password, hash) => await bcrypt.compare(password, hash);

Sensitive backend routes are protected by JWT token verification middleware. If tokens are invalid or missing, requests are denied with appropriate error responses.

const authHeader = req.headers['authorization'];
const token = authHeader && authHeader.split(' ')[1]; // Expect "Bearer <token>"

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

try {
  const user = verify(token);
  req.user = user;
  next();
} catch (error) {
  logger.error(`Auth Error: ${error.message}`);
  return res.status(403).json({ error: 'Invalid or expired token' });
}

Sensitive information (JWT secret, database URL) is stored in a .env file that is excluded from version control via .gitignore. This prevents sensitive credentials from being exposed in the repository.

The backend implements an explicit CORS policy that only allows requests from specific frontend URLs and HTTP methods.

app.use(cors({
    origin: ['http://localhost:5173', 'http://localhost:3000', 'http://127.0.0.1:5173', 'http://127.0.0.1:3000'],
    credentials: true,
    methods: ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS'],
    allowedHeaders: ['Content-Type', 'Authorization', 'Accept', 'Origin', 'X-Requested-With']
}));

Requests that don't fulfill the CORS policy are denied.

While currently deployed over HTTP for local development, the application is designed to support HTTPS in production to ensure encrypted data communication between server and client.

• POST /api/auth/signup - Create new user account
• POST /api/auth/login - Login user
• GET /api/auth/me - Get current user (protected)

• GET /api/art/carousel - Get paginated artwork carousel
  • Query params: page, limit

• GET /api/search - Search artworks
  • Query params: q (search term)

• GET /api/users/:userId/profile - Get user profile (protected)

• POST /api/details - Get full artwork details (protected)
  • Body: { artworkid: number }

• POST /api/upload/artseen - Mark artwork as seen
• POST /api/upload/review - Submit review
• POST /api/upload/note - Add personal note
• POST /api/upload/fav - Add to favorites
• PUT /api/upload/note/edit - Edit personal note
• DELETE /api/upload/artseen/delete - Remove art seen
• DELETE /api/upload/review/delete - Delete review
• DELETE /api/upload/note/delete - Delete note
• DELETE /api/upload/fav/delete - Remove favorite

• GET /api/health - Health check endpoint

Note: Protected routes require a JWT token in the Authorization header:

Authorization: Bearer <token>

• npm start - Start production server
• npm run dev - Start development server with nodemon
• npm test - Run tests
• npm run test:watch - Run tests in watch mode

• npm run dev - Start development server
• npm run build - Build for production
• npm run preview - Preview production build
• npm run lint - Run ESLint

Backend logs are saved to the backend/logs/ directory:

• error.log - Error-level logs
• combined.log - All logs

• Some directories may be empty or partially organized as the project was restructured during development
• The application is optimized for Chromium-based browsers (Chrome, Edge, Chromium)
• JWT tokens are stored in browser localStorage
• The application runs locally - backend on port 4000, frontend on port 5173