HealthShadow Backend Application

REST API server for the HealthShadow platform, supporting multi-user healthcare applications with role-based access for students and professionals.

Tech Stack

• Backend: Kotlin + Ktor framework
• Database: PostgreSQL with SQLDelight for type-safe queries
• Deployment: Docker Compose with containerized services
• Build Tool: Gradle

Architecture Overview

Self-Hosted Deployment Architecture

This application uses a single-server Docker Compose setup for self-hosting:

Internet
    ↓
Your Home Server
├── Traefik Reverse Proxy Container
│   ├── Automatic SSL/HTTPS with Let's Encrypt
│   ├── Routes traffic to application containers
│   ├── Service discovery via Docker labels*
│   └── Serves on ports 80/443

*Service Discovery: Traefik automatically finds containers by reading their Docker labels, eliminating the need for manual configuration files
├── Application Container (Ktor)
│   ├── Runs on internal port 8080
│   └── Only accessible through proxy
└── PostgreSQL Container
    ├── Database with persistent volumes
    └── Only accessible to application

Why This Architecture?

Docker Compose Benefits:

• Container Orchestration: Manages multiple services as one application
• Container Networking: Services communicate by name (no IP management)
• Dependency Management: Database starts before application
• Environment Variables: Shared configuration across containers
• Volume Management: Data persistence across container restarts
• One Command Deployment: docker-compose up starts entire stack

Reverse Proxy Benefits:

• SSL/HTTPS: Automatic certificate management
• Security: Application never directly exposed to internet
• Port Management: Public ports (80/443) → Internal app port (8080)
• Domain Routing: Routes domain name to application
• Static File Serving: Efficient serving of assets
• Rate Limiting: Built-in DDoS protection

Port Flow Example:

User: https://myapp.com (port 443)
  ↓
Server: Port 443 → Proxy Container
  ↓
Proxy: Internal request to app:8080
  ↓
App: Processes request, responds via port 8080
  ↓
Proxy: Returns HTTPS response to user

Database Schema

User System Design

The application uses a role-based user system with relational database design:

Tables:

users - Base authentication table

• id (Primary Key)
• email (Unique)
• password_hash
• user_type (student, professional, admin)
• created_at, updated_at, is_active

student - Student-specific data

• id (Primary Key)
• user_id (Foreign Key → users.id, CASCADE DELETE)
• first_name, last_name, phone
• student_id, major, year_level, gpa
• created_at, updated_at

professional - Healthcare professional data

• id (Primary Key)
• user_id (Foreign Key → users.id, CASCADE DELETE)
• first_name, last_name, phone
• license_number, specialization, years_experience
• organization, title, bio, verified
• created_at, updated_at

Foreign Key Benefits:

• Data Integrity: CASCADE DELETE ensures no orphaned records
• Relationship Enforcement: Each student/professional must have a user account
• Query Efficiency: JOIN operations to get complete user profiles

Database Connection Architecture

This application uses HikariCP DataSource with SQLDelight 2.0 for database connectivity, following the recommended approach from SQLDelight documentation.

Why HikariCP DataSource?

• Connection Pooling: Efficiently manages database connections
• Performance: HikariCP is one of the fastest connection pools available
• SQLDelight Integration: Native support via .asJdbcDriver() extension
• Production Ready: Handles connection lifecycle, timeouts, and recovery

Configuration:

// DatabaseFactory uses HikariCP with these settings:
maximumPoolSize = 10
isAutoCommit = false
transactionIsolation = "TRANSACTION_REPEATABLE_READ"

This approach provides better performance and reliability compared to direct JDBC connections, especially under load.

Traefik Reverse Proxy

What is Traefik?

Traefik is a modern reverse proxy that automatically discovers and routes traffic to your Docker containers. Unlike traditional proxies (nginx, Apache), Traefik requires zero manual configuration - it reads Docker labels to understand how to route traffic.

Why Traefik for Self-Hosting?

Automatic Configuration:

• No editing config files when adding/removing services
• Containers declare their own routing rules via labels
• Dynamic updates without proxy restarts

Built-in SSL/HTTPS:

• Automatic Let's Encrypt certificate provisioning
• Certificate renewal handled automatically
• HTTPS redirects configured by default

Docker Integration:

• Reads Docker socket to discover containers
• Understands Docker networks and service names
• Works seamlessly with Docker Compose

How It Works in This Project

Container Labels Define Routing:

app:
  labels:
    - "traefik.enable=true"                                    # Make this container discoverable
    - "traefik.http.routers.hsc-app.rule=Host(`myapp.com`)"   # Route myapp.com to this container
    - "traefik.http.routers.hsc-app.entrypoints=websecure"    # Use HTTPS (port 443)
    - "traefik.http.routers.hsc-app.tls.certresolver=letsencrypt" # Get SSL cert from Let's Encrypt
    - "traefik.http.services.hsc-app.loadbalancer.server.port=8080" # Container listens on port 8080

Traffic Flow:

User: https://myapp.com
  ↓
Traefik: Reads Host header, finds matching container label
  ↓ 
Traefik: Routes to hsc-app container on port 8080
  ↓
App: Processes request, returns response
  ↓
Traefik: Returns HTTPS response with auto-managed SSL certificate

Traefik Configuration Files

The project requires one static configuration file to enable Docker discovery and SSL:

traefik/traefik.yml - Main configuration

• Enables Docker provider for service discovery
• Configures Let's Encrypt for automatic SSL certificates
• Sets up entrypoints (HTTP port 80, HTTPS port 443)

Important Commands

# Start full stack with Traefik
docker-compose up -d

# View Traefik dashboard (shows discovered services)
open http://localhost:8081

# Check Traefik logs (useful for SSL certificate issues)
docker logs hsc-traefik

# Test SSL certificate provisioning
curl -v https://yourdomain.com

# Force SSL certificate renewal (if needed)
docker exec hsc-traefik rm /acme/acme.json
docker restart hsc-traefik

Environment Variables

Required for Production:

• DOMAIN=yourdomain.com - Your actual domain name
• POSTGRES_PASSWORD=secure_password - Database password

Local Development:

• Uses localhost defaults for testing
• SSL disabled for local development

SSL Certificate Storage

Traefik stores Let's Encrypt certificates in a Docker volume:

volumes:
  traefik-acme:  # Persistent storage for SSL certificates

Important: This volume persists certificates across container restarts. Losing this volume means re-requesting certificates from Let's Encrypt (rate limited).

Development Setup

Prerequisites

• JDK 11 or higher
• Docker and Docker Compose (for database)

Local Development

First-Time Setup: Build the Application Image

On a fresh machine, you need to build both the frontend and backend before running the application.

1. Build the React Frontend

The application serves a React frontend that must be built before the backend can serve it.

# Navigate to the React frontend directory
cd react-web
npm install 
npm run build 
cd ..

What this does:

• npm install downloads packages listed in package.json to node_modules/
• npm run build compiles TypeScript → JavaScript, bundles React components, minifies code, and outputs to react-web/build/

Development vs Production:

• Development: npm run dev starts a local server at localhost:3002 with hot-reload
• Production: npm run build creates optimized files in react-web/build/ for deployment

2. Build the Docker Image

Once the frontend is built, create the Docker image for the Ktor backend:

docker build -t hsc-http:latest .

Why this is needed:

• docker-compose expects a pre-built image named hsc-http:latest
• The Dockerfile copies everything needed from the app into the docker image
• Without this step, you get the "repository does not exist" error

Troubleshooting:

• If docker-compose up fails with "repository does not exist", you forgot this step
• If the frontend doesn't load, rebuild the React app first

Setup PostgreSQL Database

# Start PostgreSQL container for development
docker-compose -f docker-compose.dev.yml up -d

# Check container status
docker-compose -f docker-compose.dev.yml ps

# View PostgreSQL logs (useful for troubleshooting)
docker-compose -f docker-compose.dev.yml logs postgres

# Stop PostgreSQL when done
docker-compose -f docker-compose.dev.yml down

Command Explanation:

• -f docker-compose.dev.yml = Use this specific file (default is docker-compose.yml)
• up -d = Start containers in background (detached mode)
• ps = Show running containers and their status
• logs postgres = Show PostgreSQL startup messages and errors
• down = Stop and remove all containers

Database Connection:

• Host: localhost
• Port: 5432
• Database: healthshadow_dev
• Username: hsc_user
• Password: hsc_dev_password

Build and Run Application

# Build and test
./gradlew build

# Run locally (development mode)
./gradlew run

# Application runs on http://localhost:8080

Container Management

Rebuild and Restart Containers:

# Most common - rebuild image and recreate all containers
docker-compose -f docker-compose.dev.yml up -d --build

# Alternative: rebuild image first, then recreate
docker build -t hsc-http . && docker-compose -f docker-compose.dev.yml up -d --force-recreate

# Force recreate without rebuilding image (use existing image)
docker-compose -f docker-compose.dev.yml up -d --force-recreate

# Recreate specific service only
docker-compose -f docker-compose.dev.yml up -d --force-recreate app

# Nuclear option - stop, remove everything, rebuild, restart
docker-compose -f docker-compose.dev.yml down
docker build -t hsc-http .
docker-compose -f docker-compose.dev.yml up -d

When to Use:

• --build - When you've changed application code
• --force-recreate - When you've changed docker-compose.yml configuration
• Both - When you've changed both code and configuration

Testing the API

Direct Access (Bypasses Traefik):

# Test root endpoint
curl http://localhost:8080/

# Get all students  
curl http://localhost:8080/students

# Add a student
curl -X POST http://localhost:8080/students \
  -H "Content-Type: application/json" \
  -d '{"id":"1","firstName":"John","lastName":"Doe","email":"john@example.com","phone":"555-1234"}'

Through Traefik (Production-like):

# Test HTTPS access (ignores SSL certificate warnings)
curl -k -H "Host: hsc-http.localhost" https://localhost/

# Test HTTP redirect (should return 301 redirect)
curl -H "Host: hsc-http.localhost" http://localhost/

# Test API endpoints through Traefik
curl -k -H "Host: hsc-http.localhost" https://localhost/students

Deployment

Production Deployment (Self-Hosted)

Server Requirements:

• Linux server (Ubuntu/Debian recommended)
• Docker and Docker Compose installed
• Domain name pointing to server IP
• Ports 80 and 443 accessible from internet

Deployment Process:

1. Clone repository to server
2. Configure environment variables
3. Run docker-compose up -d
4. SSL certificates automatically provisioned

Data Persistence:

• Database data stored in Docker volumes
• Survives container restarts and updates
• Backup strategy: Volume snapshots

Project History

Previous State: Heroku-deployed application with basic student management

Current Modernization:

• ✅ Updated dependencies (Kotlin 1.9.25, Ktor 2.3.12, SQLDelight 2.0.2)
• ✅ Migrated from Gradle 7.2 to 8.5
• ✅ Redesigned database schema for multi-user system
• ✅ Fixed localhost binding for browser development
• ✅ Docker containerization for self-hosting
• ✅ Authentication utilities with bcrypt password hashing
• 🔄 In Progress: Web authentication UI (login/logout pages)
• 📋 Planned: SSL setup, monitoring, comprehensive testing

Future Development

Platform Expansion (Multi-Service Architecture)

One of the key advantages of this Traefik-based setup is the ability to host multiple services for the HealthShadow platform on the same server using different subdomains. Each service gets its own SSL certificate and container, while sharing the same infrastructure.

How Multi-Service Hosting Works:

The HTTP Host header identifies which service each request is intended for:

User Request: https://api.shadowconnects.com
├── DNS resolves to: 73.43.190.195 (your home IP)
├── Router forwards: Port 443 → Server:443
├── Traefik reads: Host: api.shadowconnects.com
└── Routes to: API container based on Docker labels

Potential Services Architecture:

# Main API (Current)
api.shadowconnects.com → Kotlin/Ktor REST API

# Frontend Application  
app.shadowconnects.com → React/Vue.js SPA

# Admin Dashboard
admin.shadowconnects.com → Admin interface

# Student Portal
student.shadowconnects.com → Student-specific features

# Professional Portal  
professional.shadowconnects.com → Healthcare professional tools

# Documentation/Help
docs.shadowconnects.com → API documentation, user guides

# File Storage
files.shadowconnects.com → Document/image upload service

# Real-time Features
ws.shadowconnects.com → WebSocket server for chat/notifications

# Analytics Dashboard
analytics.shadowconnects.com → Usage metrics, reporting

Implementation Example:

# docker-compose.yml - Multiple services
services:
  # Current API service
  api:
    labels:
      - "traefik.http.routers.api.rule=Host(`api.shadowconnects.com`)"
      
  # Frontend application
  frontend:
    image: nginx:alpine
    labels:
      - "traefik.http.routers.frontend.rule=Host(`app.shadowconnects.com`)"
      
  # Admin dashboard
  admin:
    image: admin-dashboard:latest
    labels:
      - "traefik.http.routers.admin.rule=Host(`admin.shadowconnects.com`)"

Benefits of This Architecture:

• Single IP Address: All services use the same public IP (73.43.190.195)
• Automatic SSL: Each subdomain gets its own Let's Encrypt certificate
• Independent Deployment: Services can be updated/restarted independently
• Resource Efficiency: Shared database, shared reverse proxy
• Easy Scaling: Add new services by adding containers + labels
• Professional Appearance: Clean subdomain structure

Future Service Ideas:

• Matching Service: Algorithm to connect students with professionals
• Scheduling System: Appointment booking and calendar management
• Communication Platform: Secure messaging between users
• Content Management: Educational resources and documentation
• Payment Processing: Subscription management for premium features
• Mobile API Gateway: Specialized endpoints for mobile applications

This architecture allows the HealthShadow platform to grow organically - start with the core API, then add specialized services as user needs become clear.

Authentication System

• ✅ Password hashing (bcrypt)
• ✅ Session management utilities
• 🔄 Login/logout web pages
• 📋 Route protection middleware
• 📋 User registration flow

API Enhancements

• User registration/authentication endpoints
• Role-based access control
• Professional verification workflow
• Student-professional matching system

Infrastructure

• Automated backups
• Monitoring and logging
• CI/CD pipeline
• Health checks
• Code linting and formatting (ktlint)

Testing Strategy

This project follows industry-standard testing practices with clear separation of concerns:

Unit Tests - Test single components in isolation, no external dependencies

• Pure unit tests: Zero dependencies (e.g., validation logic)
• Unit tests with mocks: Isolated business logic with mocked dependencies

Integration Tests - Test components working together with external dependencies

• Repository tests: Use in-memory H2 database for fast, isolated database testing
• End-to-end tests: Full application flow testing

Current Test Coverage:

• ✅ Authentication layer: Password validation, auth service, user repository
• 📋 Planned: Route handlers, session management, full auth flow

Contributing

1. Ensure tests pass: ./gradlew test
2. Follow existing code conventions
3. Write tests for new functionality (unit tests preferred)
4. Update this README for architectural changes
5. Commit messages should be descriptive and concise

Configuration

Environment Variables

• PORT - Application port (default: 8080)
• DATABASE_URL - PostgreSQL connection string
• JWT_SECRET - Secret key for JWT tokens (when implemented)
• ANTHROPIC_API_KEY - API key for Claude AI chatbot integration (required for chat feature)

Application Configuration

• Local Development: app/src/main/resources/application.conf
• Production: Environment variables override configuration

AI Chatbot Integration

The application includes an AI-powered chatbot using Claude (Anthropic's AI assistant) to provide users with guidance and answer questions.

Features

• Real-time Streaming Responses: Messages stream in character-by-character for a natural conversation feel
• Floating Chat Widget: Non-intrusive chat button in the bottom-right corner
• Modern UI: Built with React and Tailwind CSS, matches the site's design

Setup

1. Get an API Key:

   • Sign up at Anthropic Console
   • Generate an API key from your account settings
   • Free tier available with generous limits

2. Set Environment Variable:

   # For local development
   export ANTHROPIC_API_KEY="your-api-key-here"
   
   # Or add to your shell profile (~/.bashrc, ~/.zshrc, etc.)
   echo 'export ANTHROPIC_API_KEY="your-api-key-here"' >> ~/.bashrc
   source ~/.bashrc

3. For Docker Deployment:

   # In docker-compose.yml
   services:
     app:
       environment:
         - ANTHROPIC_API_KEY=${ANTHROPIC_API_KEY}

   Then set the variable on your host:

   export ANTHROPIC_API_KEY="your-api-key-here"
   docker-compose up -d

Usage

• Click the chat button in the bottom-right corner
• Type your question and press Enter or click Send
• Responses stream in real-time from Claude

API Endpoint

• POST /api/chat - Send messages to the chatbot
• Request body: {"message": "your question here"}
• Response: Server-Sent Events (SSE) stream

Cost

Claude API pricing (as of 2024):

• Claude 3.5 Sonnet: ~$3 per million input tokens, ~$15 per million output tokens
• Typical chat message: 100-500 tokens
• Very affordable for personal/small business use

CORS (Cross-Origin Resource Sharing)

What is CORS?

CORS is a browser security feature that controls which websites can make requests to your server. By default, browsers block requests from one domain to another (e.g., frontend.com trying to call api.backend.com).

Example of CORS in Action:

Your React app at:     https://app.shadowconnects.com
Tries to fetch from:   https://api.shadowconnects.com/students

Browser checks: "Does api.shadowconnects.com allow requests from app.shadowconnects.com?"
- If YES (CORS configured): Request proceeds
- If NO (CORS not configured): Request blocked, console shows CORS error

Why Does CORS Exist?

Without CORS, any malicious website could make requests to your bank's API using your logged-in session. CORS ensures only trusted origins can access your server.

When Do You Need CORS?

CORS is Required When:

• Frontend and backend are on different domains/subdomains
• Frontend runs on localhost:3002 and backend on localhost:8080
• Mobile apps making API requests
• Third-party services accessing your API

CORS is NOT Required When:

• Frontend and backend served from the same domain (e.g., both at shadowconnects.com)
• Server-to-server communication (no browser involved)

CORS Configuration in This Project

Located in app/src/main/kotlin/com/shadowconnect/plugins/CORS.kt:

fun Application.configureCORS() {
    install(CORS) {
        allowMethod(HttpMethod.Get)       // Allow GET requests
        allowMethod(HttpMethod.Post)      // Allow POST requests
        allowHeader(HttpHeaders.ContentType)  // Allow Content-Type header
        allowCredentials = true           // Allow cookies/sessions

        // Allow specific origins
        allowHost("localhost:3002", listOf("http", "https"))
        allowHost("shadowconnects.com", listOf("https"))
    }
}

Common CORS Errors and Fixes

Error: Access to fetch has been blocked by CORS policy

• Cause: Your frontend's domain is not in allowHost() list
• Fix: Add the domain to CORS configuration

Error: The 'Access-Control-Allow-Origin' header contains multiple values

• Cause: Multiple CORS configurations or conflicting settings
• Fix: Ensure only one CORS configuration in your application

Error: Credential is not supported if the CORS header 'Access-Control-Allow-Origin' is '*'

• Cause: Using anyHost() with allowCredentials = true
• Fix: Use specific allowHost() entries instead of anyHost()

SEO (Search Engine Optimization)

What is SEO?

SEO is the practice of optimizing your website to rank higher in search engine results (Google, Bing, etc.). When someone searches for "clinical shadowing opportunities," you want your site to appear on the first page of results.

Why SEO Matters:

• Discoverability: 93% of online experiences begin with a search engine
• Organic Traffic: Free, sustainable traffic vs. paying for ads
• Credibility: Higher rankings = more trust from users
• Target Audience: Reach students actively searching for shadowing opportunities

SEO Crash Course

How Search Engines Work:

1. Crawling: Bots discover your pages by following links
2. Indexing: Pages are analyzed and stored in a database
3. Ranking: Algorithm determines which pages to show for each search query

Three Pillars of SEO:

1. Technical SEO: Site structure, speed, mobile-friendliness
2. On-Page SEO: Content, meta tags, keywords, headings
3. Off-Page SEO: Backlinks, social signals, domain authority

Multi-Page Architecture for SEO

The Problem with Single-Page Apps (SPAs):

Traditional SPAs serve all content from one URL with anchor links (#about, #features). This is bad for SEO because:

• Google sees only one page to index
• Can't target different keywords per "page"
• Poor internal linking structure
• Difficult to share specific sections

Our Solution: Client-Side Routing with React Router

We use React Router to create distinct URLs for different content:

/ → Homepage (clinical shadowing opportunities)
/how-it-works → Process explanation
/opportunities → Browse opportunities
/about → Mission and team
/for-students → Student-focused content
/for-professionals → Professional-focused content

Each route:

• Has a unique URL Google can index
• Targets specific keywords
• Has custom meta tags and descriptions
• Provides shareable links

Technical SEO Implementation

1. Multi-Page Routing

• Technology: React Router DOM
• Location: react-web/src/App.tsx
• Benefit: Each URL is a distinct page for search engines

2. Dynamic Meta Tags

• Technology: react-helmet-async
• Location: react-web/src/components/SEOHead.tsx
• What We Include:
  • Unique <title> tags per page
  • Meta descriptions (150-160 characters)
  • Open Graph tags (social media sharing)
  • Twitter Card tags
  • Canonical URLs (prevent duplicate content)

Example from HomePage:

<SEOHead
  title="Clinical Shadowing Opportunities"
  description="Connect with healthcare professionals and gain valuable clinical experience..."
  path="/"
/>

3. SPA Fallback Routing

• Location: app/src/main/kotlin/com/shadowconnect/plugins/Routing.kt
• Purpose: Serve index.html for all non-API routes
• How It Works:

  // Catch-all route serves React app for client-side routing
  get("{...}") {
      call.respondFile(indexFile)
  }

• Why Needed: Allows users to refresh on /about or directly visit /opportunities without 404 errors

4. Semantic HTML Structure

• Proper heading hierarchy (H1 → H2 → H3)
• Semantic tags (<header>, <main>, <section>, <footer>)
• Descriptive link text (avoid "click here")

5. Mobile Responsiveness

• Technology: Tailwind CSS with responsive breakpoints
• Why It Matters: Google uses mobile-first indexing

Current SEO Optimizations

✅ Implemented:

• Multi-page routing with React Router
• Unique meta tags per page (title, description, Open Graph)
• Canonical URLs to prevent duplicate content
• Mobile-responsive design
• Fast page loads (Vite bundler)
• Semantic HTML structure
• Internal linking (header, footer, CTAs)
• Backend SPA fallback for proper routing

Future SEO Improvements

📋 Planned:

1. sitemap.xml

   • XML file listing all pages
   • Helps search engines discover content
   • Location: /sitemap.xml

2. robots.txt

   • Guides search engine crawlers
   • Specifies what to crawl/not crawl
   • Location: /robots.txt

3. Structured Data (Schema.org)

   • JSON-LD markup for rich search results
   • Types: Organization, BreadcrumbList, FAQPage
   • Enables features like knowledge panels

4. Content Strategy

   • Blog section at /blog/ for keyword-rich articles
   • FAQ page with structured data
   • Success stories and testimonials

5. Performance Optimization

   • Image optimization and lazy loading
   • Code splitting for faster initial loads
   • CDN for static assets

6. Analytics & Monitoring

   • Google Search Console (track indexing)
   • Google Analytics 4 (user behavior)
   • Core Web Vitals monitoring

7. Backlink Building

   • Partnerships with medical schools
   • Directory listings (healthcare education)
   • Guest posts on healthcare blogs

SEO Best Practices for Contributors

When adding new pages or content:

1. Create Unique Pages: Add new routes in App.tsx for distinct content
2. Add SEO Tags: Use <SEOHead> component with unique title/description
3. Use Keywords Naturally: Include "clinical shadowing," "healthcare," "medical students"
4. Optimize Headers: One H1 per page, logical H2/H3 hierarchy
5. Write Descriptive Links: Use meaningful anchor text ("Browse Opportunities" not "Click Here")
6. Mobile First: Test all pages on mobile devices
7. Page Speed: Keep images optimized, avoid heavy libraries

Measuring SEO Success

Key Metrics to Track:

• Organic search traffic (Google Analytics)
• Keyword rankings ("clinical shadowing opportunities")
• Click-through rate (Google Search Console)
• Page indexing status
• Core Web Vitals (LCP, FID, CLS)
• Backlink quantity and quality

Tools:

• Google Search Console (free)
• Google Analytics 4 (free)
• Google PageSpeed Insights (free)
• Ahrefs or SEMrush (paid, advanced)

Development DB Commands

Connect to PostgreSQL Database

# Connect to development database
docker exec -it hsc-postgres psql -U hsc_user -d healthshadow_dev

# Alternative: Connect from host machine (if psql installed locally)
psql -h localhost -p 5432 -U hsc_user -d healthshadow_dev

Common PostgreSQL Commands

View Tables:

-- List all tables
\dt

-- Show table structure
\d users
\d student
\d professional

-- Show all columns with types
\d+ users

Query Tables:

-- View all users (shows test logins)
SELECT id, email, user_type, is_active, created_at FROM users;

-- View students with user info
SELECT u.email, u.user_type, s.first_name, s.last_name, s.major 
FROM users u 
JOIN student s ON u.id = s.user_id;

-- View professionals with user info
SELECT u.email, u.user_type, p.first_name, p.last_name, p.specialization 
FROM users u 
JOIN professional p ON u.id = p.user_id;

Update Entries:

-- Update user password (use actual bcrypt hash)
UPDATE users SET password_hash = '$2a$10$actual.bcrypt.hash.here' WHERE email = 'student@example.com';

-- Activate/deactivate user
UPDATE users SET is_active = false WHERE email = 'student@example.com';
UPDATE users SET is_active = true WHERE email = 'student@example.com';

-- Update student info
UPDATE student SET gpa = 3.85, year_level = 4 WHERE user_id = 1;

-- Verify professional
UPDATE professional SET verified = true WHERE user_id = 2;

Exit Database:

-- Exit psql
\q

Test Login Credentials

The database is initialized with these test accounts (passwords need proper bcrypt hashing):

• Student: student@example.com
• Professional: professional@example.com
• Admin: admin@example.com

passwords are all "password"