Database Setup

Configure PostgreSQL for CompanyFlow API

Overview

CompanyFlow uses PostgreSQL 14 or higher as its primary database. The application uses the pgx/v5 driver for database connectivity and includes a custom SQL-based migration runner.

Prerequisites

Before setting up the database, ensure you have:

• PostgreSQL 14 or higher installed
• Database user with appropriate privileges
• Network access to the PostgreSQL server

Installation

1. Install PostgreSQL

   Install PostgreSQL on your system:

   macOS (Homebrew)

   brew install postgresql@14
   brew services start postgresql@14
   
2. Create Database

   Create a new database for CompanyFlow:

   # Connect to PostgreSQL
   psql -U postgres
   
   # Create database
   CREATE DATABASE companyflow;
   
   # Create user (optional)
   CREATE USER companyflow_user WITH PASSWORD 'your_secure_password';
   
   # Grant privileges
   GRANT ALL PRIVILEGES ON DATABASE companyflow TO companyflow_user;
   
   # Exit psql
   \q
   

   For production environments, use a strong password and limit privileges to only what’s necessary.

3. Enable Required Extensions

   CompanyFlow requires the pgcrypto extension for UUID generation:

   -- Connect to your database
   psql -U postgres -d companyflow
   
   -- Enable extension
   CREATE EXTENSION IF NOT EXISTS "pgcrypto";
   

   This extension is automatically enabled when you run migrations, but you can enable it manually if needed.

4. Verify Connection

   Test your database connection:

   psql -U companyflow_user -d companyflow -h localhost -p 5432
   

   If successful, you should see the PostgreSQL prompt.

Database Configuration

Connection String Format

CompanyFlow uses the following connection string format:

postgresql://[user]:[password]@[host]:[port]/[dbname]?sslmode=[mode]

SSL Mode Options

sslmode · string · default: "disable"

Controls SSL/TLS encryption for the database connection:

• disable - No SSL (development only)
• require - SSL required, no certificate verification
• verify-ca - SSL required, verify CA certificate
• verify-full - SSL required, verify CA and hostname

Always use sslmode=require or higher in production environments to encrypt database traffic.

Connection Pooling

CompanyFlow uses pgxpool for connection pooling. The default pool settings are managed by the pgx library, which typically creates:

• Maximum connections: Based on system resources
• Minimum connections: 0 (connections created on demand)
• Connection lifetime: Automatic cleanup of idle connections

Custom Pool Configuration

To customize connection pool settings, you can modify the connection string in config/config.go:27:

connStr := fmt.Sprintf(
    "postgresql://%s:%s@%s:%s/%s?sslmode=%s&pool_max_conns=20&pool_min_conns=5",
    user, password, host, port, dbname, sslmode,
)

Schema Overview

The CompanyFlow database includes the following main tables:

• companies - Multi-tenant company records
• tenants - Subscription and plan information
• employees - Employee records with authentication
• departments - Organizational departments
• designations - Job titles and positions
• levels - Employee hierarchy levels
• roles - RBAC roles
• permissions - Role-based permissions
• leaves - Leave request records
• memos - Internal communications
• approvals - Approval workflow records
• audit_logs - System activity tracking
• schema_migrations - Migration tracking table

Testing Database Setup

Create Test Database

For running tests, create a separate test database:

psql -U postgres
CREATE DATABASE companyflow_test;
GRANT ALL PRIVILEGES ON DATABASE companyflow_test TO companyflow_user;

Configure Test Environment

Set test database variables in your environment or .env file:

TEST_DB_HOST=localhost
TEST_DB_PORT=5432
TEST_DB_USER=companyflow_user
TEST_DB_PASSWORD=your_password
TEST_DB_NAME=companyflow_test
TEST_DB_SSLMODE=disable

Troubleshooting

Connection Refused

If you see “connection refused” errors:

1. Check if PostgreSQL is running:

   # macOS/Linux
   pg_isready -h localhost -p 5432
   
   # Check service status
   sudo systemctl status postgresql
   

2. Verify PostgreSQL is listening on the correct port:

   sudo netstat -plunt | grep postgres
   

Authentication Failed

If authentication fails:

1. Check pg_hba.conf configuration file
2. Ensure password authentication is enabled for your connection type
3. Verify username and password are correct

Permission Denied

If you see permission errors:

-- Grant all privileges on database
GRANT ALL PRIVILEGES ON DATABASE companyflow TO companyflow_user;

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO companyflow_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO companyflow_user;

Next Steps

After setting up your database:

1. Configure environment variables
2. Run database migrations
3. Test your API setup