Doctor Who Database Project

A full-stack Node.js, Express, and PostgreSQL application built to manage a comprehensive Doctor Who database. The platform features Supabase Authentication, OpenAI natural language querying, and complex relational PostgreSQL functions and views.

CMSC 4323 final project — 3-engineer team: Jonathan Muhire, Milton Adina, Magnani Fabiola.

Data Model

16 Sequelize models with 3 join tables for many-to-many relationships, derived directly from src/models/index.js:

erDiagram
    ACTOR ||--o{ DOCTOR : "plays"
    ACTOR ||--o{ COMPANION : "plays"

    WRITER ||--o{ SEASON : "showruns"
    WRITER ||--o{ EPISODE : "writes"
    DIRECTOR ||--o{ EPISODE : "directs"
    SEASON ||--o{ EPISODE : "contains"

    DOCTOR ||--o{ CHARACTER : "appears as"
    DOCTOR }o--|| ACTOR : "played by"
    DOCTOR }o--|| EPISODE : "first appearance"
    DOCTOR }o--|| EPISODE : "last appearance"
    DOCTOR ||--o{ DOCTOR_COMPANION : "travels with"
    COMPANION ||--o{ DOCTOR_COMPANION : "travels with"

    DOCTOR_COMPANION }o--|| EPISODE : "starts in"
    DOCTOR_COMPANION }o--|| EPISODE : "ends in"

    EPISODE ||--o{ EPISODE_APPEARANCE : "features"
    CHARACTER ||--o{ EPISODE_APPEARANCE : "appears in"
    EPISODE ||--o{ EPISODE_LOCATION : "visits"
    PLANET ||--o{ EPISODE_LOCATION : "visited by"
    EPISODE ||--o{ ENEMY_EPISODE : "features"
    ENEMY ||--o{ ENEMY_EPISODE : "in"

    PLANET ||--o{ SPECIES : "home of"
    PLANET ||--o{ ENEMY : "home of"
    PLANET ||--o{ COMPANION : "home of"
    SPECIES ||--o{ COMPANION : "is"
    SPECIES ||--o{ ENEMY : "is"
    SPECIES ||--o{ CHARACTER : "is"

    CHARACTER }o--o| ENEMY : "alignment"
    TARDIS }o--|| DOCTOR : "owned by"

    ACTOR {
        uuid id PK
        text name
        date born
        text nationality
    }
    DOCTOR {
        uuid id PK
        int incarnation_number
        uuid actor_id FK
        uuid first_episode_id FK
        uuid last_episode_id FK
    }
    COMPANION {
        uuid id PK
        text name
        uuid actor_id FK
        uuid species_id FK
        uuid home_planet_id FK
    }
    EPISODE {
        uuid id PK
        text title
        uuid season_id FK
        uuid writer_id FK
        uuid director_id FK
        date air_date
    }
    SEASON {
        uuid id PK
        int season_number
        uuid showrunner_id FK
    }
    PLANET {
        uuid id PK
        text name
        text galaxy
    }
    SPECIES {
        uuid id PK
        text name
        uuid home_planet_id FK
    }
    ENEMY {
        uuid id PK
        text name
        uuid species_id FK
        uuid home_planet_id FK
    }
    CHARACTER {
        uuid id PK
        text name
        uuid doctor_id FK
        uuid enemy_id FK
        uuid species_id FK
    }
    TARDIS {
        uuid id PK
        text designation
        uuid owner_doctor_id FK
    }
    DOCTOR_COMPANION {
        uuid id PK
        uuid doctor_id FK
        uuid companion_id FK
        uuid start_episode_id FK
        uuid end_episode_id FK
    }
    EPISODE_APPEARANCE {
        uuid id PK
        uuid episode_id FK
        uuid character_id FK
    }
    EPISODE_LOCATION {
        uuid id PK
        uuid episode_id FK
        uuid planet_id FK
    }
    ENEMY_EPISODE {
        uuid id PK
        uuid enemy_id FK
        uuid episode_id FK
    }
    WRITER { uuid id PK }
    DIRECTOR { uuid id PK }

Loading

Test evidence

Reproducible by running NODE_ENV=test npm test against this repo.

Architecture

This project is structured for a serverless Vercel deployment with a Supabase PostgreSQL backend.

• Frontend: Vanilla JS Single Page Application (SPA) located in /public.
• Backend: Node.js Express API.
• Database: PostgreSQL (Supabase) accessed via Sequelize ORM.
• Authentication: Supabase JWT.
• AI Integration: OpenAI GPT-3.5 API.

Requirements

• Node.js 18+
• Supabase Project (PostgreSQL)
• OpenAI API Key (Optional, for LLM querying)

Environment Setup

1. Copy the example environment file:

   cp .env.example .env

2. Populate the .env file with your credentials:
   • DB_HOST / DB_USER / DB_PASSWORD / DB_NAME / DB_PORT: Get this from your Supabase Dashboard -> Project Settings -> Database -> Connection string -> URI.
   • JWT_SECRET: Get this from your Supabase Dashboard -> Project Settings -> API -> JWT Secret. This is critical for authenticating mutating requests.
   • OPENAI_API_KEY: Your OpenAI platform key.

Installation & Database Initialization

1. Install dependencies:

   npm install

2. Initialize the database schema (Creates tables):

   npm run db:sync

3. Initialize PostgreSQL Functions and Views:

   npm run db:objects

4. Seed the database with sample data:

   npm run db:seed

5. Verify your setup:

   npm run verify

Running Locally

Start the development server:

npm run dev

The API is available at http://localhost:3000/api and the frontend UI is served at http://localhost:3000.

Testing

This project uses Jest and Supertest for integration testing.

Run the test suite:

npm test

Deployment (Vercel)

This application is ready for Vercel deployment.

1. Connect your GitHub repository to Vercel.
2. In the Vercel project settings, carefully add all the environment variables from your .env file (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT, OPENAI_API_KEY, JWT_SECRET).
3. Deploy! Vercel will natively use the vercel.json configuration and the serverless wrapper located at api/index.js to serve the application globally.