Film Production Database Management System (FPDMS)

A modern, full-stack desktop application designed for film-production companies to manage their entire catalogue of movies, cast members, crew staff, and financial records from a single unified interface.

Built using Electron as the application shell and a PostgreSQL database hosted on Neon (Serverless Postgres) as the persistent data store.

---

🌟 Key Features

• Dual Access Modes:
  • Admin Role: Full CRUD (Create, Read, Update, Delete) capabilities to manage movies, cast, crew, and financial records.
  • Spectator Role: Read-only access to view filmographies, casts, crew directories, and aggregated financials.
• Encapsulated Database Logic: All business logic, validations, and mathematical derivations are fully encapsulated inside advanced PostgreSQL views and PL/pgSQL stored procedures.
• Real-time Budget Sync: Automatic calculation and synchronization of a movie's budget based on active producer investments using database triggers.
• Automated Profit Calculation: Auto-generated columns compute net profits (box_office_revenue - production_cost - marketing_cost) and executive profit shares dynamically.
• Advanced Analytics & Performance Metrics: Deep insights into genre performance, movie ROIs, actor earnings ranking, director filmography analytics, and budget-overrun detection.
• Safe Desktop Shell Integration: Utilizes a secure Electron context bridge to handle IPC (Inter-Process Communication) without exposing sensitive Node.js APIs to the frontend layer.
• Premium User Interface: Features a beautiful, responsive, dark terminal-aesthetic layout designed to enhance focus and user workflow.

---

🏗️ Architecture Overview

The system follows a modular multi-tier architecture that completely decouples user interaction from data persistence:

+-----------------------------------------------------------+
|                      Electron Main                        |
|                     (src/app.js)                          |
+-----------------------------+-----------------------------+
                              | (IPC Channels)
                              v
+-----------------------------+-----------------------------+
|                      Preload Script                       |
|                  (src/UI/preload.cjs)                     |
+-----------------------------+-----------------------------+
                              | (Context Bridge API)
                              v
+-----------------------------+-----------------------------+
|                     Renderer Process                      |
|                (src/UI/renderer.js / .html)               |
+-----------------------------+-----------------------------+
                              | (Method Invocation)
                              v
+-----------------------------+-----------------------------+
|                   Controller Modules                      |
|                 (src/controllers/*.js)                    |
+-----------------------------+-----------------------------+
                              | (SQL queries / PL/pgSQL)
                              v
+-----------------------------+-----------------------------+
|                   Neon Serverless Client                  |
|                 (src/services/neonClient.js)              |
+-----------------------------+-----------------------------+
                              | (Serverless Postgres Protocol)
                              v
+-----------------------------------------------------------+
|                   Neon PostgreSQL Database                |
+-----------------------------------------------------------+

1. UI / Presentation Layer: Responsive single-page interface (index.html) styled with a clean terminal layout (styles.css). JavaScript actions are processed asynchronously in the renderer context (renderer.js).
2. Preload Layer: A secure context bridge (preload.cjs) that exposes a safe, restricted IPC gateway (window.electronAPI) to the renderer process.
3. App Main Process: The core Electron process (app.js) handling system events, IPC registrations, window lifecycle, and serverless database requests.
4. Controllers Layer: Highly optimized controller modules (src/controllers/*.js) representing each system entity. They act as thin JS wrappers that invoke PL/pgSQL functions using the Neon client.
5. Database Client Layer: A centralized singleton client (neonClient.js) that establishes high-speed serverless HTTP communication with Neon Postgres.
6. Database Engine Layer: A cloud-hosted PostgreSQL server running schema migrations, views, and PL/pgSQL procedures to enforce data integrity.

---

📊 Database Design & Relations

Entity-Relationship (ER) Model

erDiagram
    Movie ||--o{ Movie_Actor : features
    Actor ||--o{ Movie_Actor : acts_in
    Movie ||--o{ Movie_CrewMember : employs
    CrewMember ||--o{ Movie_CrewMember : works_on
    Movie ||--o{ Movie_Director : directed_by
    Director ||--o{ Movie_Director : directs
    Movie ||--o{ Movie_Producer : funded_by
    Producer ||--o{ Movie_Producer : invests_in
    Movie ||--|| MovieFinance : has
    Director ||--o{ DirectorFinance : receives
    Movie ||--o{ DirectorFinance : details
    Producer ||--o{ ProducerFinance : receives
    Movie ||--o{ ProducerFinance : details

Loading

Table Schema Breakdown

• Core Entities:
  • Movie: Stores film details (title, genre, topic, release year, status: draft or published).
  • Actor: Stores actor bios (name, birth year, gender).
  • Director: Stores director bios (name, birth year).
  • Producer: Stores producer bios (name, birth year, gender).
  • CrewMember: Stores technical crew bios (name, birth year, gender).
• Junction Tables:
  • Movie_Actor: Assigns actors to movies with custom character roles and per-movie salaries.
  • Movie_CrewMember: Assigns crew to movies with specific job titles and salaries.
  • Movie_Director: Maps directors to movies.
  • Movie_Producer: Maps producers to movies alongside their dynamic investment capitals.
• Financial Tables:
  • MovieFinance: Aggregates film production budgets (from producer investments), production costs, marketing costs, and box-office revenues. Automatically calculates the net_profit using a database generated column.
  • DirectorFinance & ProducerFinance: Store contract models, commissions, bonuses, base fees, and total payouts per film. Entitlements are processed using custom ENUM types:
    • commission_type: 'gross', 'net', or 'backend'
    • contract_type: 'flat', 'percentage', or 'hybrid'

---

🛠️ Advanced Database Objects (DDL)

1. Database Triggers

• sync_movie_budget: Listens for inserts, updates, and deletes on Movie_Producer and automatically keeps the MovieFinance.budget synchronized by calculating SUM(investment) for that specific movie in real-time.

2. SQL Views

• v_movie_financial_overview: Computes standard financial metrics per movie, including dynamic ROI percentages ((net_profit / budget) * 100).
• v_movie_full_cast: Generates a consolidated roster of all actors, character roles, and salaries per movie.
• v_director_filmography: Displays directory details, total movies directed, average production budgets, and average net profits.
• v_top_paid_actors: Ranks actors globally based on their accumulated salaries across all movies.
• v_genre_performance: Aggregates budgets, total box-office revenues, average net profits, and total ROIs grouped by movie genre.
• v_crew_roster: Consolidates technical crew lists, job roles, and salaries per movie.
• v_profitable_movies: Ranks all released movies that made a positive net profit using PostgreSQL window ranking functions (RANK() OVER (...)).

3. PL/pgSQL Stored Functions

• get_movie_total_spend(movie_id): Aggregates all actor payrolls, crew payrolls, production costs, and marketing costs to compute the actual total spend for a given film.
• get_movie_full_roster(movie_id): Returns every cast member, crew member, director, and producer assigned to a movie, showing their role details and specific financial values (salaries, investments, or fees) within a single unified list.
• get_movies_by_actor(actor_name): Performs a case-insensitive partial match (ILIKE) to search for movies starring an actor.
• get_most_profitable_genre(): Dynamically identifies the single most profitable genre based on net profit averages.
• flag_over_budget_movies(): Identifies films where the actual production cost exceeded the approved investment budget and calculates both absolute and percentage overruns.
• get_director_roi(director_id): Aggregates the minimum, maximum, and average ROI across all films directed by a specific director.

---

🚀 Installation & Running Guide

1. Prerequisites

Ensure you have the following installed on your machine:

• Node.js (v16 or higher)
• An active cloud database instance on Neon or a local PostgreSQL database server.

2. Repository Setup

Clone this repository and navigate to the project root directory:

git clone https://github.com/22314825/Film-Production-Database-Management-System.git
cd Film-Production-Database-Management-System

3. Install Dependencies

Install all required Node and Electron dependencies:

npm install

4. Configure Environment Variables

Create a .env file in the root directory by duplicating the example template:

cp .env.example .env

Open .env in your text editor and specify your Neon Postgres connection string alongside your preferred admin credentials:

DATABASE_URL='postgresql://your_user:your_password@your_neon_host/neondb?sslmode=require'
ADMIN_USERNAME='admin'
ADMIN_PASSWORD='your_secure_password'

5. Run Database Migrations

Run the initial setup scripts and apply all schema migrations, tables, seed data, and PL/pgSQL procedures to your database in sequential order:

# 1. Run structural migrations (Tables, connections, triggers, views, and functions)
npm run migrate

# 2. (Optional) Run the verification script to verify schema integrity
npm run setup

Note: To wipe the database and reset the schema back to its clean state at any time, execute:

npm run reset

6. Launch the Application

Start the Electron desktop application window:

npm start

7. Run Automated Tests

Execute the full integration test suite to verify that all controller methods successfully communicate with your Neon PostgreSQL instance:

npm test

---

📂 Repository Structure

Path	Description
migrations/	Contains SQL migration scripts (001-006) and Node migration runners.
├── migrate.js	Runs database migrations sequentially.
├── reset.js	Wipes existing tables and resets the schema.
└── setup.js	Runs validation checks to ensure tables and functions exist.
src/	Main application code.
├── app.js	Electron main process (lifecycle, IPC channels, and db endpoints).
├── controllers/	Thin wrapper controllers communicating with the DB client.
├── services/	Backend service integrations.
│ └── neonClient.js	Centralized database connection singleton using Neon Serverless SDK.
├── test/	Automated integration tests.
│ └── runAllTests.js	Main runner executing CRUD and query controller tests.
└── UI/	Frontend assets.
├── index.html	Application HTML shell.
├── preload.cjs	Secure Context Bridge exposing API endpoints.
├── renderer.js	Handles view logic, UI actions, state, and IPC events.
└── styles.css	CSS styles defining the custom terminal theme.
package.json	Project manifest specifying scripts and dependencies.
.env.example	Boilerplate file for database URL and credentials setup.