Database Configuration

This guide covers the database setup for the Rhea Agripad backend, including PostgreSQL configuration and multi-tenant setup.

PostgreSQL Setup

Installing PostgreSQL

If PostgreSQL is not already installed, install it using:

sudo apt update
sudo apt install postgresql postgresql-contrib

Creating a Database

1. Start PostgreSQL command line interface:

sudo -u postgres psql

2. Create a new database:

CREATE DATABASE rheaagripad;

3. Create a user:

CREATE USER rheaagripad WITH ENCRYPTED PASSWORD 'postgres';

4. Grant privileges:

GRANT ALL PRIVILEGES ON DATABASE rheaagripad TO rheaagripad;

5. Grant schema privileges:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rheaagripad;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rheaagripad;
GRANT postgres TO rheaagripad;

6. Exit PostgreSQL:

\q

Additional PostgreSQL Configurations

If you encounter permission issues, you may need to run these additional commands:

ALTER SCHEMA public OWNER TO rheaagripad;

-- Grant USAGE on the public schema
GRANT USAGE ON SCHEMA public TO rheaagripad;

-- Grant all privileges on all existing tables in the public schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rheaagripad;

-- Grant all privileges on all existing sequences in the public schema
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO rheaagripad;

-- Set default privileges for future tables created in the public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO rheaagripad;

-- Set default privileges for future sequences created in the public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO rheaagripad;

-- Grant connect privileges
GRANT CONNECT ON DATABASE rheaagripad TO rheaagripad;
GRANT ALL PRIVILEGES ON DATABASE rheaagripad TO rheaagripad;

Multi-Tenant Setup

The Rhea Agripad backend uses a multi-tenant architecture to isolate data between different clients.

Running Migrations

1. Make migrations:

python manage.py makemigrations

2. Migrate shared schemas:

python manage.py migrate_schemas --shared

3. For tenant-specific migrations:

python manage.py makemigrations
python manage.py migrate_schemas

Creating Tenants

Using the Shell

1. Create the public tenant (base tenant):

python manage.py shell

from main.models import Client, Domain

tenant = Client(schema_name="public", name="Public")
tenant.save()

domain = Domain(domain="localhost", tenant=tenant, is_primary=True)
domain.save()

exit()

Using Management Commands

You can create tenants using the management command:

# Public Client
python manage.py create_tenant --domain-domain=localhost --schema=public --name=Public

# Main Client
python manage.py create_tenant --domain-domain=main.localhost --schema=main --name=Main

# Rhea Client
python manage.py create_tenant --domain-domain=rhea.localhost --schema=rhea --name=Rhea

# Kilimo Client
python manage.py create_tenant --domain-domain=kilimo.localhost --schema=kilimo --name=Kilimo

Using Admin Dashboard

1. Create a superuser:

python manage.py createsuperuser

2. Log in to the admin dashboard and create clients:

   • Client 1:

     • Schema name: rhea
     • Name: Rhea
     • Devices: DEVICE_001, DEVICE_002 (comma separated)
     • Fill in other required information

   • Client 2:

     • Schema name: kilimo
     • Name: Kilimo
     • Devices: DEVICE_003, DEVICE_004 (comma separated)
     • Fill in other required information

   • Client 3 (Main):

     • Schema name: main
     • Name: Main Dashboard
     • Devices: ALL (comma separated)
     • Fill in other required information

3. Create domains for each client:

   • Client 1 Domain:

     • Domain: rhea.localhost
     • Tenant: Select rhea tenant
     • Is Primary: True

   • Client 2 Domain:

     • Domain: kilimo.localhost
     • Tenant: Select kilimo tenant
     • Is Primary: True

   • Client 3 Domain:

     • Domain: main.localhost
     • Tenant: Select main tenant
     • Is Primary: True