Local PostgreSQL and Prisma Environment Setup Guide (Ubuntu)

This guide documents the step-by-step process required to install, configure, and sync a multi-role Local PostgreSQL PostgreSQL database with a Prisma ORM backend on a local Ubuntu development machine.

Step 1: Install PostgreSQL Engine

First, install the PostgreSQL server package and its additional utilities through the native Ubuntu package manager.

sudo apt update
sudo apt install postgresql postgresql-contrib

Step 2: Initialize and Start the Database Service

Ensure the background service is actively running and configured to boot up automatically when the computer turns on.

# Force-start the engine processes
sudo systemctl start postgresql

# Enable it to run automatically on system boot
sudo systemctl enable postgresql

# Verify the operational status (should say "active (running)")
sudo systemctl status postgresql

Step 3: Configure Multi-Role Database Users and Databases

Access the core system account to set up the individual application roles and environment schemas required by the project layout.

Open the interactive PostgreSQL administration prompt:
```
sudo -u postgres psql
```

Execute the following SQL schema block sequentially to register credentials and allocate privileges:

-- Create the 3 main system accounts using explicit passwords
CREATE ROLE fresible_migrator WITH LOGIN PASSWORD 'local_migrator';
CREATE ROLE fresible_app WITH LOGIN PASSWORD 'local_app';
CREATE ROLE fresible_platform WITH LOGIN PASSWORD 'local_platform';

-- Elevate the migrator role with Superuser permissions to modify database structures
ALTER ROLE fresible_migrator CREATEDB SUPERUSER;

-- Provision the structural environments owned by the primary migration account
CREATE DATABASE fresible_dev OWNER fresible_migrator;
CREATE DATABASE fresible_test OWNER fresible_migrator;

-- Safely disconnect and close the admin prompt
\q

Step 4: Verify Local Project Environment Config

Navigate into the backend project root folder and verify that the target environment file exists and accurately mirrors the local database configurations.

cd ~/projects/PMS-BE

Ensure your configuration file (.env) explicitly includes the mapped local routing paths matching the users created above:

DATABASE_URL="postgresql://fresible_migrator:local_migrator@localhost:5432/fresible_dev?schema=public"
APP_DATABASE_URL="postgresql://fresible_app:local_app@localhost:5432/fresible_dev?schema=public"
PLATFORM_DATABASE_URL="postgresql://fresible_platform:local_platform@localhost:5432/fresible_dev?schema=public"
TEST_DATABASE_URL="postgresql://fresible_migrator:local_migrator@localhost:5432/fresible_test?schema=public"

Step 5: Sync Structural Table Blueprints (Migrations)

Push your Prisma structural definitions downstream to execute structural table formatting directly into your local database cluster.

pnpm prisma db push

Step 6: Launch Visual Administration Panel (Prisma Studio)

Launch the integrated database client layer to visually explore schemas, browse tables, and manipulate data rows through a graphical user interface directly inside the web browser.