Database Setup

Overview

CONFOR uses PostgreSQL 15+ with PostGIS extensions for geospatial data processing. This guide covers database setup using Docker Compose or manual installation.

Prerequisites

PostgreSQL 15+

Relational database engine

PostGIS Extension

Spatial database capabilities

Redis 7+

Caching and session storage

Node.js 20+

For running Prisma CLI

Quick Start with Docker

The fastest way to get started is using Docker Compose.

1. Start Database Services

# Start PostgreSQL and Redis
docker compose up -d

# Verify services are running
docker compose ps

# Expected output:
NAME                IMAGE                PORTS
postgres            postgres:15-alpine   0.0.0.0:5432->5432/tcp
redis               redis:7-alpine       0.0.0.0:6379->6379/tcp

2. Docker Compose Configuration

The docker-compose.yml file defines the database services:

docker-compose.yml

services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: app_dev
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  redis:
    image: redis:7-alpine
    command: redis-server --appendonly yes
    ports:
      - "6379:6379"
    volumes:
      - redisdata:/data

volumes:
  pgdata:
  redisdata:

Data is persisted in Docker volumes (pgdata and redisdata). Containers can be safely stopped and restarted without data loss.

Manual PostgreSQL Installation

If you prefer not to use Docker:

Windows
macOS
Linux (Ubuntu/Debian)
Linux (RHEL/CentOS)

Download PostgreSQL 15+ from postgresql.org
Run the installer and include Stack Builder
In Stack Builder, select and install PostGIS extension
Set password for postgres user during installation
Verify installation:

psql --version
# Should show: psql (PostgreSQL) 15.x

# Install PostgreSQL with Homebrew
brew install postgresql@15
brew install postgis

# Start PostgreSQL service
brew services start postgresql@15

# Create database
createdb app_dev

# Verify installation
psql --version

# Add PostgreSQL repository
sudo apt-get install -y postgresql-common
sudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Install PostgreSQL and PostGIS
sudo apt-get update
sudo apt-get install -y postgresql-15 postgresql-15-postgis-3

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Create database
sudo -u postgres createdb app_dev

# Install PostgreSQL repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module
sudo dnf -qy module disable postgresql

# Install PostgreSQL and PostGIS
sudo dnf install -y postgresql15-server postgresql15-contrib postgis33_15

# Initialize and start
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl start postgresql-15
sudo systemctl enable postgresql-15

Prisma Setup

1. Install Dependencies

npm install

2. Configure Database URL

Create a .env file in the project root:

.env

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/app_dev"
NEXTAUTH_SECRET="your-secret-key-min-32-characters"

3. Generate Prisma Client

npm run db:generate

This generates the Prisma client based on your schema.prisma file.

4. Run Migrations

# Apply all pending migrations
npm run db:migrate

# This will:
# - Create database tables
# - Install PostGIS and other extensions
# - Set up partitioning for audit_logs
# - Create indexes and constraints

5. Seed Database

npm run db:seed

This creates:

Default organization (default-org)
System roles: SUPER_ADMIN, ADMIN, MANAGER, USER
Permissions for all modules
Admin user: [email protected] / Admin1234
Base system configuration

Change the default admin password immediately after first login in production environments.

Database Schema Overview

Core Tables

Table	Purpose
`User`	User accounts and authentication
`Organization`	Multi-tenant organization data
`Role`	Role definitions
`Permission`	Granular permissions
`Module`	System modules (users, dashboard, etc.)
`audit_logs`	Activity audit trail (partitioned by month)

Forest Management Tables

Table	Purpose
`PatrimonyLevel2`	Level 2 forest units (finca, predio, hato)
`PatrimonyLevel3`	Level 3 forest units (lote, zona, bloque)
`PatrimonyLevel4`	Level 4 forest units (rodal) - includes geometry
`BiologicalAsset`	Forest biological assets
`Species`	Tree and plant species
`ManagementScheme`	Forest management schemes
`LandUseType`	Land use classifications

Geospatial Tables

Table	Purpose
`GeospatialImportJob`	Tracks shapefile import jobs
`GeospatialRecalcJob`	Surface recalculation jobs
`GeospatialVariationJob`	Geometry variation jobs
`PolygonGeometry`	Stores WKB geometry data

PostGIS Extensions

The first migration installs required PostgreSQL extensions:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";     -- UUID generation
CREATE EXTENSION IF NOT EXISTS "postgis";        -- Spatial data support
CREATE EXTENSION IF NOT EXISTS "pg_trgm";        -- Trigram text search
CREATE EXTENSION IF NOT EXISTS "btree_gist";     -- Indexing support

Audit Log Partitioning

CONFOR uses monthly table partitioning for the audit_logs table to improve performance.

Automatic Partition Creation

A trigger automatically creates new partitions when inserting audit logs:

CREATE OR REPLACE FUNCTION create_audit_partition()
RETURNS TRIGGER AS $$
DECLARE
  partition_date TEXT;
  partition_name TEXT;
  start_date DATE;
  end_date DATE;
BEGIN
  partition_date := TO_CHAR(NEW.created_at, 'YYYY_MM');
  partition_name := 'audit_logs_' || partition_date;
  start_date := DATE_TRUNC('month', NEW.created_at);
  end_date := start_date + INTERVAL '1 month';
  
  -- Create partition if it doesn't exist
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF audit_logs
     FOR VALUES FROM (%L) TO (%L)',
    partition_name, start_date, end_date
  );
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Manual Partition Management

You can also create partitions manually using the provided script:

npx tsx scripts/create-audit-partition.ts

Database Maintenance

Backup Database

# Backup to SQL file
docker compose exec postgres pg_dump -U postgres app_dev > backup_$(date +%Y%m%d).sql

# Backup with compression
docker compose exec postgres pg_dump -U postgres app_dev | gzip > backup_$(date +%Y%m%d).sql.gz

Restore Database

# Restore from SQL file
docker compose exec -T postgres psql -U postgres app_dev < backup.sql

# Restore from compressed backup
gunzip -c backup.sql.gz | docker compose exec -T postgres psql -U postgres app_dev

Vacuum and Analyze

Regularly optimize database performance:

-- Full vacuum (reclaim storage)
VACUUM FULL;

-- Update statistics for query planner
ANALYZE;

-- Both operations
VACUUM FULL ANALYZE;

-- Auto-vacuum specific table
VACUUM ANALYZE audit_logs;

Troubleshooting

Connection refused to PostgreSQL

Symptoms: ECONNREFUSED or Connection refusedSolutions:

# Check if PostgreSQL is running
docker compose ps postgres
# or for manual installation
sudo systemctl status postgresql

# Check port is listening
netstat -an | grep 5432
# or
ss -tulpn | grep 5432

# Restart service
docker compose restart postgres
# or
sudo systemctl restart postgresql

PostGIS extension not found

Symptoms: ERROR: extension "postgis" is not availableSolutions:

# For Docker (should be included in postgres image)
docker compose exec postgres psql -U postgres -d app_dev -c "CREATE EXTENSION postgis;"

# For manual installation, install PostGIS package
# Ubuntu/Debian:
sudo apt-get install postgresql-15-postgis-3

# Then enable in database:
psql -U postgres -d app_dev -c "CREATE EXTENSION postgis;"

Migration fails with permission error

Symptoms: permission denied to create extensionSolutions:

# Grant superuser to your database user
psql -U postgres -c "ALTER USER your_user WITH SUPERUSER;"

# Or connect as postgres user and run migrations
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/app_dev" npx prisma migrate deploy

Prisma client out of sync

Symptoms: PrismaClientInitializationError: Prisma Client could not locate the Query EngineSolutions:

# Regenerate Prisma client
npm run db:generate

# Clear node_modules and reinstall
rm -rf node_modules .next
npm install
npm run db:generate

Audit log partition missing

Symptoms: ERROR: no partition of relation "audit_logs" foundSolutions:

# Manually create partition for current month
npx tsx scripts/create-audit-partition.ts

# Or create via SQL
psql -U postgres -d app_dev <<SQL
CREATE TABLE IF NOT EXISTS audit_logs_2026_03 PARTITION OF audit_logs
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
SQL

Slow queries on large tables

Solutions:

-- Check missing indexes
SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- Analyze table statistics
ANALYZE VERBOSE PatrimonyLevel4;

-- Check slow queries
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Consider adding indexes:

CREATE INDEX CONCURRENTLY idx_level4_org ON "PatrimonyLevel4"("organizationId");
CREATE INDEX CONCURRENTLY idx_level4_level3 ON "PatrimonyLevel4"("level3Id");

Next Steps

Environment Variables

Configure all environment variables

Worker Setup

Set up geospatial processing workers

Forest Patrimony

Forest hierarchy data models

Installation Guide

Complete installation instructions

Get Started

Core Features

User Guides

Configuration

Database Setup

Overview

Prerequisites

PostgreSQL 15+

PostGIS Extension

Redis 7+

Node.js 20+

Quick Start with Docker

1. Start Database Services

2. Docker Compose Configuration

Manual PostgreSQL Installation

Prisma Setup

1. Install Dependencies

2. Configure Database URL

3. Generate Prisma Client

4. Run Migrations

5. Seed Database

Database Schema Overview

Core Tables

Forest Management Tables

Geospatial Tables

PostGIS Extensions

Audit Log Partitioning

Automatic Partition Creation

Manual Partition Management

Database Maintenance

Backup Database

Restore Database

Vacuum and Analyze

Troubleshooting

Next Steps

Environment Variables

Worker Setup

Forest Patrimony

Installation Guide

Build docs developers (and LLMs) love

Get Started

Core Features

User Guides

Configuration

​Overview

​Prerequisites

PostgreSQL 15+

PostGIS Extension

Redis 7+

Node.js 20+

​Quick Start with Docker

​1. Start Database Services

​2. Docker Compose Configuration

​Manual PostgreSQL Installation

​Prisma Setup

​1. Install Dependencies

​2. Configure Database URL

​3. Generate Prisma Client

​4. Run Migrations

​5. Seed Database

​Database Schema Overview

​Core Tables

​Forest Management Tables

​Geospatial Tables

​PostGIS Extensions

​Audit Log Partitioning

​Automatic Partition Creation

​Manual Partition Management

​Database Maintenance

​Backup Database

​Restore Database

​Vacuum and Analyze

​Troubleshooting

​Next Steps

Environment Variables

Worker Setup

Forest Patrimony

Installation Guide

Build docs developers (and LLMs) love

Overview

Prerequisites

Quick Start with Docker

1. Start Database Services

2. Docker Compose Configuration

Manual PostgreSQL Installation

Prisma Setup

1. Install Dependencies

2. Configure Database URL

3. Generate Prisma Client

4. Run Migrations

5. Seed Database

Database Schema Overview

Core Tables

Forest Management Tables

Geospatial Tables

PostGIS Extensions

Audit Log Partitioning

Automatic Partition Creation

Manual Partition Management

Database Maintenance

Backup Database

Restore Database

Vacuum and Analyze

Troubleshooting

Next Steps