Running Migrations

Overview

CompanyFlow uses a custom SQL-based migration system. Migrations are stored as .sql files in the database/migration/ directory and are executed automatically when the application starts.

How Migrations Work

The migration system:

Creates a schema_migrations table to track executed migrations
Reads all .sql files from database/migration/
Sorts files alphabetically by name (using numeric prefixes like 000_, 001_, etc.)
Executes each migration that hasn’t been run yet
Records successful migrations in the schema_migrations table

Migrations run automatically when you start the application with go run main.go. You don’t need to run them manually.

Migration Naming Convention

Migration files follow this naming pattern:

{prefix}_{description}.sql

Examples:

000_create_migrations_table.sql
001_create_companies_and_tenants.sql
002_create_roles_and_permissions.sql
003_create_department_designation_level.sql

Always use a numeric prefix to ensure migrations run in the correct order. The system sorts files alphabetically.

Included Migrations

CompanyFlow includes the following migrations:

000_create_migrations_table.sql

Creates the schema_migrations table for tracking migrations:

CREATE TABLE IF NOT EXISTS schema_migrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL UNIQUE,
  executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

001_create_companies_and_tenants.sql

Creates the multi-tenant architecture tables:

companies - Company records with multi-tenant support
tenants - Subscription and plan management
Enables pgcrypto extension for UUID generation

002_create_roles_and_permissions.sql

Creates the RBAC (Role-Based Access Control) system:

roles - User roles (e.g., Admin, Manager, Employee)
permissions - Role permissions and access control

003_create_department_designation_level.sql

Creates organizational structure tables:

departments - Organizational departments
designations - Job titles and positions
levels - Employee hierarchy levels

004_create_employees.sql

Creates the employee management system:

employees - Employee records with authentication
Triggers for automatic updated_at timestamp updates
Foreign key relationships to departments, designations, levels

005_leave_management.sql

Creates the leave management system:

leaves - Leave request records
Leave types, statuses, and approval workflows

006_memos_and_approvals.sql

Creates internal communication and approval systems:

memos - Internal communications and announcements
approvals - Approval workflow records

007_audit_logs.sql

Creates the audit logging system:

audit_logs - System activity tracking for compliance

Automatic Execution

Migrations run automatically when you start the application:

go run main.go

You’ll see output like:

connecting to database
✓ Database connected successfully
Running migrations...
✓ Migration already ran: 000_create_migrations_table.sql
✓ Migration already ran: 001_create_companies_and_tenants.sql
✓ Migration executed: 002_create_roles_and_permissions.sql
✓ Migration executed: 003_create_department_designation_level.sql

✓ All migrations completed successfully!
✓ Server starting on :8080

Manual Execution

If you need to run migrations separately, you can create a migration-only command:

migrate.go

package main

import (
    "log"
    "github.com/falasefemi2/companyflowlow/config"
    "github.com/falasefemi2/companyflowlow/database"
)

func main() {
    pool, err := config.InitDB()
    if err != nil {
        log.Fatalf("Failed to initialize database: %v", err)
    }
    defer pool.Close()

    if err := database.RunMigrations(pool); err != nil {
        log.Fatalf("Migration failed: %v", err)
    }
}

Run it with:

go run migrate.go

Creating New Migrations

Determine Next Number

Check the highest numbered migration file:

ls -1 database/migration/ | tail -1

If the last file is 007_audit_logs.sql, your next migration should start with 008_.

Create Migration File

Create a new .sql file with a descriptive name:

touch database/migration/008_add_notifications.sql

Write SQL

Add your SQL statements to the file:

database/migration/008_add_notifications.sql

CREATE TABLE IF NOT EXISTS notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id UUID NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    read BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE INDEX idx_notifications_employee ON notifications(employee_id);
CREATE INDEX idx_notifications_read ON notifications(read);

Test Migration

Restart the application to run the new migration:

go run main.go

Verify the migration was successful:

✓ Migration executed: 008_add_notifications.sql

Migration Best Practices

Use CREATE IF NOT EXISTS

Always use CREATE TABLE IF NOT EXISTS to make migrations idempotent:

CREATE TABLE IF NOT EXISTS my_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

Add Indexes

Create indexes for foreign keys and frequently queried columns:

CREATE INDEX idx_employees_company ON employees(company_id);
CREATE INDEX idx_employees_email ON employees(company_id, email);

Include Comments

Document complex schemas with SQL comments:

-- Multi-tenant companies table
-- Each company is isolated by company_id
CREATE TABLE IF NOT EXISTS companies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug VARCHAR(255) UNIQUE NOT NULL, -- For subdomain routing
    name VARCHAR(255) NOT NULL
);

Use Constraints

Add appropriate constraints to maintain data integrity:

status VARCHAR(50) DEFAULT 'active' 
    CHECK (status IN ('active', 'suspended', 'inactive')),

Handle Foreign Keys Carefully

Consider the impact of deletes:

FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL,
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT

Migration Tracking

The schema_migrations table tracks executed migrations:

SELECT * FROM schema_migrations ORDER BY executed_at DESC;

Example output:

id	name	executed_at
uuid	007_audit_logs.sql	2026-03-03 10:30:15
uuid	006_memos_and_approvals.sql	2026-03-03 10:30:14
uuid	005_leave_management.sql	2026-03-03 10:30:13

Troubleshooting

Migration Already Executed

If a migration fails halfway through, it may be recorded as executed even if incomplete. To re-run:

-- Connect to database
psql -U your_user -d companyflow

-- Remove the failed migration record
DELETE FROM schema_migrations WHERE name = '008_problematic_migration.sql';

Then restart the application to re-run the migration.

Migration Failed Error

If you see “error executing migration”:

Check the SQL syntax in your migration file
Verify all referenced tables exist (check migration order)
Check PostgreSQL logs for detailed error messages:
```
tail -f /usr/local/var/log/[email protected]
```

File Not Found Error

If migrations aren’t found:

Verify you’re running the application from the project root
Check the database/migration/ directory exists
Ensure .sql files have the correct extension (not .txt)

Permission Denied

If you see permission errors:

-- Grant necessary privileges
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user;
GRANT USAGE, CREATE ON SCHEMA public TO your_user;

Rollback Strategy

CompanyFlow doesn’t have built-in rollback support. For rollbacks:

Option 1: Create a new migration that reverses changes

-- 009_rollback_notifications.sql
DROP TABLE IF EXISTS notifications;

Option 2: Restore from database backup

pg_restore -U your_user -d companyflow backup.sql

Option 3: Manually remove migration record and drop objects

DELETE FROM schema_migrations WHERE name = '008_add_notifications.sql';
DROP TABLE notifications;

Always backup your database before running migrations in production.

Production Considerations

Backup Before Migrations

Always backup before running migrations:

pg_dump -U your_user companyflow > backup_$(date +%Y%m%d_%H%M%S).sql

Test in Staging

Test migrations in a staging environment first:

Restore production data to staging
Run migrations
Verify application functionality
Then deploy to production

Monitor Migration Performance

Large migrations can take time. Monitor progress:

-- Check active queries
SELECT pid, now() - query_start as duration, query 
FROM pg_stat_activity 
WHERE state = 'active';

Use Transactions Carefully

Some operations can’t run in transactions (like CREATE INDEX CONCURRENTLY). The current migration system runs each file as a single query, so use multiple files if you need transactional control.

Next Steps

After running migrations:

Get Started

Core Concepts

Guides

Running Migrations

Overview

How Migrations Work

Migration Naming Convention

Included Migrations