Running Migrations

Database migration system for CompanyFlow API

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:

1. Creates a schema_migrations table to track executed migrations
2. Reads all .sql files from database/migration/
3. Sorts files alphabetically by name (using numeric prefixes like 000_, 001_, etc.)
4. Executes each migration that hasn’t been run yet
5. 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:

1. 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
   );
   
2. 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
3. 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
4. 003_create_department_designation_level.sql

   Creates organizational structure tables:

   • departments - Organizational departments
   • designations - Job titles and positions
   • levels - Employee hierarchy levels
5. 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
6. 005_leave_management.sql

   Creates the leave management system:

   • leaves - Leave request records
   • Leave types, statuses, and approval workflows
7. 006_memos_and_approvals.sql

   Creates internal communication and approval systems:

   • memos - Internal communications and announcements
   • approvals - Approval workflow records
8. 007_audit_logs.sql

   Creates the audit logging system:

   • audit_logs - System activity tracking for compliance

Running Migrations

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

1. 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_.

2. Create Migration File

   Create a new .sql file with a descriptive name:

   touch database/migration/008_add_notifications.sql
   
3. 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);
   
4. 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”:

1. Check the SQL syntax in your migration file
2. Verify all referenced tables exist (check migration order)
3. Check PostgreSQL logs for detailed error messages:
   tail -f /usr/local/var/log/[e-mail]
   

File Not Found Error

If migrations aren’t found:

1. Verify you’re running the application from the project root
2. Check the database/migration/ directory exists
3. 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:

1. Option 1: Create a new migration that reverses changes

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

2. Option 2: Restore from database backup

   pg_restore -U your_user -d companyflow backup.sql
   

3. 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:

1. Restore production data to staging
2. Run migrations
3. Verify application functionality
4. 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:

1. Verify your database setup
2. Test the API
3. Explore the API documentation