Database Setup Guide

This guide details the steps required to set up the PostgreSQL database and initialize the application schema, including the critical, high-performance full-text search functionality.

1. Setup PostgreSQL Server (First-Time Setup)

This section covers the initial installation and configuration of the PostgreSQL server. You only need to do this once per machine.

1. Install PostgreSQL.

   sudo apt update
   sudo apt install postgresql postgresql-contrib
   sudo service postgresql start

2. Open the psql shell as the superuser.

   sudo -u postgres psql

3. Create your application database. (Replace <your_database_name>)

   CREATE DATABASE <your_database_name>;

4. Create a dedicated user for your application. (Replace <your_username> and <your_password>)

   CREATE USER <your_username> WITH ENCRYPTED PASSWORD '<your_password>';

5. Grant privileges to the new user.

   GRANT ALL PRIVILEGES ON DATABASE <your_database_name> TO <your_username>;
   ALTER USER <your_username> CREATEDB;

   After these commands, type \q to exit the psql prompt.

2. Configure the Application

1. Configure Environment Variables. Your application connects to the database using the DATABASE_URL. Create a .env file in the project root and add the following line, replacing the placeholders with the credentials from Step 1.

   # .env
   DATABASE_URL="postgresql://<your_username>:<your_password>@localhost:5432/<your_database_name>?schema=public"

3. Initialize Schema

1. Migrate the schema

   sudo npm run prisma:migrate:dev-reset

2. Push the schema

   sudo npm run prisma:push

4. Manually Add Full-Text Search

The application's search feature relies on advanced PostgreSQL functions that cannot be automatically generated by Prisma. You must manually execute custom SQL using postgresql.

1. Open the psql terminal.

   psql -d your_database_name -U your_username -W

2. Add the search vectors

Run this inside the psql terminal:

-- STEP 1: DEFINE THE UPDATE FUNCTION
-- This function will be called by the trigger automatically.
-- We use 'setweight' to give matches in 'name' a higher priority ('A')
-- than matches in 'slug' ('B'). This can be used later to rank search results.
CREATE OR REPLACE FUNCTION update_product_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('simple', coalesce(NEW.name, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(NEW.slug, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- STEP 2: CREATE THE TRIGGER
-- This trigger ensures the function runs automatically whenever a product is created or updated.
-- We drop the old one first to avoid errors.
DROP TRIGGER IF EXISTS tsvector_update ON "Product";

CREATE TRIGGER tsvector_update
BEFORE INSERT OR UPDATE ON "Product"
FOR EACH ROW
EXECUTE FUNCTION update_product_search_vector();


-- STEP 3: BACKFILL EXISTING DATA (ONE-TIME)
-- This updates all existing products in your table to have a search_vector.
-- It's crucial for making old products searchable.
UPDATE "Product"
SET search_vector = (
  setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
  setweight(to_tsvector('simple', coalesce(slug, '')), 'B')
)
WHERE search_vector IS NULL; -- Only update rows that haven't been processed


-- STEP 4: CREATE THE PERFORMANCE INDEX
-- This GIN index is what makes the full-text search fast.
-- Without it, searches will be very slow on a large table.
CREATE INDEX IF NOT EXISTS "Product_search_vector_idx" ON "Product" USING GIN(search_vector);

---

Last updated on September 20, 2025 by Ayman.