Problem

Currently, OSPOS relies on a pre-built database.sql file that must be manually executed to set up the database schema. This creates several issues:

1. Docker complexity: Requires a separate sqlscript container to mount and execute the SQL file
2. Manual installation step: Users must manually import database.sql before the app works
3. Maintenance burden: database.sql is generated from tables.sql + constraints.sql via a gulp task, adding build complexity
4. Migration conflicts: When tests or fresh installs run migrations on top of database.sql, the migration history table doesn't reflect that migrations have already been applied, causing Duplicate column errors

Solution

Convert database.sql into an initial migration that:

1. Runs tables.sql + constraints.sql as the first migration
2. Only executes on fresh/empty databases - skips if tables already exist (preserves existing installs)
3. Marks itself as complete so subsequent migrations don't re-run

Changes Made

New Initial Migration

• Created app/Database/Migrations/20170501000000_initial_schema.php
• Loads app/Database/Migrations/sqlscripts/initial_schema.sql (tables.sql + constraints.sql combined)
• Checks if tables exist before running - skips on existing installations
• Marks migration as complete so it won't run again

Docker Simplification

• Removed sqlscript container from docker-compose.yml and docker/docker-mysql.yml
• Removed database.sql volume mount from docker-compose.dev.yml
• MySQL containers now only need to create empty database with credentials

Build Process

• Removed build-database gulp task from gulpfile.js
• Database schema is now version-controlled via migrations, not generated

CI/CD

• Removed Build database.sql step from .github/workflows/phpunit.yml
• Removed Initialize database step from .github/workflows/build-release.yml
• Tests now rely on migrations to set up schema

Documentation

• Updated INSTALL.md to reflect automatic schema creation via migrations
• Removed manual Execute the file app/Database/database.sql step

Benefits

1. One-step setup: Just create empty database, migrations handle the rest
2. Docker simplicity: No separate SQL container needed
3. Better migration support: Existing installs upgrade normally, fresh installs get full schema
4. No build-time SQL generation: Schema is versioned in source files
5. Consistent testing: CI tests use same migration path as production

Migration Path

• Fresh installs: Initial migration creates full schema, subsequent migrations are no-ops
• Existing installations: Migration sees tables exist, skips initial schema load, continues normally
• Upgrades: No change - existing migrations continue to work