Database Schema Setup

Once the database service is running, you must create the schema before using the backend. ⚠️ This step is mandatory. The backend will not function until the schema is loaded.

This document explains how to set up the Beneficiary Backend Database Schema in PostgreSQL.

Schema File

The schema is defined in: schema.pgsql

This file contains:

• Table definitions

• Indexes

• Constraints

• Relationships

Applying the Schema

You can either use pgAdmin or psql CLI tool to import. Ensure to create a blank database named uba_beneficiary_mw before trying one of the options.

Option A - Using pgAdmin

1. Open pgAdmin at http://localhost:8082

2. Login with credentials from .env

   • Email: PGADMIN_DEFAULT_EMAIL

   • Password: PGADMIN_DEFAULT_PASSWORD

3. Connect to the Postgres server

4. Right-click on the database (uba_beneficiary_mw) → Query Tool

5. Load and execute schema.pgsql

Option B - Using psql CLI

Run the following command (from the project root):

docker exec -i <postgres_container_name> psql -U <db-user-name> -d uba_beneficiary_mw < schema.pgsql

Replace <postgres_container_name> with the container name (Use docker ps to get DB container name).

⚠️ Note

• In the schema.pgsql file, replace:

  Owner: <replace_with_your_db_user>

  with your actual DB username.

Verifying Schema

After running the script:

• Tables should be visible in pgAdmin under uba_beneficiary_mw → Schemas → public → Tables

• Indexes can be found under each table’s Indexes section

Schema Updates

If schema changes are required:

1. Update schema.pgsql file

2. Re-run the script as described above