> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/Falasefemi2/companyflow/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

<Note>
  Migrations run automatically when you start the application with `go run main.go`. You don't need to run them manually.
</Note>

## 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`

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

## Included Migrations

CompanyFlow includes the following migrations:

<Steps>
  <Step title="000_create_migrations_table.sql">
    Creates the `schema_migrations` table for tracking migrations:

    ```sql theme={null}
    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
    );
    ```
  </Step>

  <Step title="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
  </Step>

  <Step title="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
  </Step>

  <Step title="003_create_department_designation_level.sql">
    Creates organizational structure tables:

    * `departments` - Organizational departments
    * `designations` - Job titles and positions
    * `levels` - Employee hierarchy levels
  </Step>

  <Step title="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
  </Step>

  <Step title="005_leave_management.sql">
    Creates the leave management system:

    * `leaves` - Leave request records
    * Leave types, statuses, and approval workflows
  </Step>

  <Step title="006_memos_and_approvals.sql">
    Creates internal communication and approval systems:

    * `memos` - Internal communications and announcements
    * `approvals` - Approval workflow records
  </Step>

  <Step title="007_audit_logs.sql">
    Creates the audit logging system:

    * `audit_logs` - System activity tracking for compliance
  </Step>
</Steps>

## Running Migrations

### Automatic Execution

Migrations run automatically when you start the application:

```bash theme={null}
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:

```go migrate.go theme={null}
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:

```bash theme={null}
go run migrate.go
```

## Creating New Migrations

<Steps>
  <Step title="Determine Next Number">
    Check the highest numbered migration file:

    ```bash theme={null}
    ls -1 database/migration/ | tail -1
    ```

    If the last file is `007_audit_logs.sql`, your next migration should start with `008_`.
  </Step>

  <Step title="Create Migration File">
    Create a new `.sql` file with a descriptive name:

    ```bash theme={null}
    touch database/migration/008_add_notifications.sql
    ```
  </Step>

  <Step title="Write SQL">
    Add your SQL statements to the file:

    ```sql database/migration/008_add_notifications.sql theme={null}
    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);
    ```
  </Step>

  <Step title="Test Migration">
    Restart the application to run the new migration:

    ```bash theme={null}
    go run main.go
    ```

    Verify the migration was successful:

    ```
    ✓ Migration executed: 008_add_notifications.sql
    ```
  </Step>
</Steps>

## Migration Best Practices

### Use CREATE IF NOT EXISTS

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

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
-- 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:

```sql theme={null}
status VARCHAR(50) DEFAULT 'active' 
    CHECK (status IN ('active', 'suspended', 'inactive')),
```

### Handle Foreign Keys Carefully

Consider the impact of deletes:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
-- 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:
   ```bash theme={null}
   tail -f /usr/local/var/log/postgresql@14.log
   ```

### 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:

```sql theme={null}
-- 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
   ```sql theme={null}
   -- 009_rollback_notifications.sql
   DROP TABLE IF EXISTS notifications;
   ```

2. **Option 2**: Restore from database backup
   ```bash theme={null}
   pg_restore -U your_user -d companyflow backup.sql
   ```

3. **Option 3**: Manually remove migration record and drop objects
   ```sql theme={null}
   DELETE FROM schema_migrations WHERE name = '008_add_notifications.sql';
   DROP TABLE notifications;
   ```

<Warning>
  Always backup your database before running migrations in production.
</Warning>

## Production Considerations

### Backup Before Migrations

Always backup before running migrations:

```bash theme={null}
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:

```sql theme={null}
-- 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](/guides/database-setup)
2. [Test the API](/guides/testing)
3. [Explore the API documentation](/api/auth/login)