Schema Application Workflow, Safety Guarantees, and Troubleshooting
Overview
platform/database/apply_schema.sh is the repository’s schema application helper for the MySQL persistence layer. It coordinates the database startup check, replays the base schema in a backwards-compatible way, runs migration directories, and finishes by verifying that the expected tables exist.
This script is the operational boundary between the SQL assets in platform/database/ and the running database container. Its purpose is to keep schema changes safe for fresh databases and existing environments while making failures visible early when the target container, migration directories, or database naming do not line up.
Architecture Overview
flowchart TB
Operator[Operator]
subgraph SchemaScript[platform database apply schema script]
Script[apply_schema.sh]
end
subgraph ComposeStack[Docker Compose stack]
Health[Health checks]
MySQL[MySQL container]
end
subgraph SqlAssets[SQL assets]
Base[Base schema SQL]
Migrations[Migration directories]
Verify[Final table verification]
end
Operator --> Script
Script --> Health
Health --> MySQL
Script --> Base
Base --> MySQL
Script --> Migrations
Migrations --> MySQL
Script --> Verify
Verify --> MySQL
Schema Application Runbook
apply_schema.sh
platform/database/apply_schema.sh
The helper runs the schema workflow in four ordered stages:
- Wait for the target database container to report healthy.
- Replay the base schema so the database starts from the canonical baseline.
- Execute SQL found in the migration directories.
- Verify that the expected tables are present at the end of the run.
Workflow summary
| Stage | What happens | Why it matters |
|---|---|---|
| Health wait | The script blocks until the database container is healthy. | Prevents schema SQL from running against a database that has not finished starting. |
| Base schema replay | The canonical base schema is applied before migrations. | Keeps fresh databases and existing databases aligned on the same starting point. |
| Migration execution | Migration directories are processed after the base schema. | Applies evolution SQL in the intended order. |
| Final verification | The resulting tables are checked after all SQL is applied. | Confirms that the schema is usable before the helper exits. |
Step 1: Wait for database health
The script first waits for the database container health state to become ready. This is the first safety gate in the run and the main protection against connecting too early to a container that has started but is not yet accepting schema work.
Step 2: Replay the base schema
The base schema is replayed before any migration directories run. This preserves backwards compatibility by ensuring the current schema run starts from the same baseline whether the database is brand new or already contains objects from an earlier deployment.
Step 3: Execute migration directories
[!IMPORTANT] The base schema replay is part of the normal path, not a special-case recovery step. It is used to keep the schema state aligned before later migration files are processed.
After the baseline is in place, the script processes the migration directories in the SQL assets tree. This is the point where schema evolution is applied on top of the base layout.
Step 4: Verify final tables
The workflow ends with a final table verification step. This confirms that the expected tables exist after the schema SQL has completed and surfaces incomplete or misdirected application runs immediately.
Safety Guarantees
| Guarantee | Mechanism in the workflow | Operational effect |
|---|---|---|
| Container readiness is required | Health wait before any SQL runs | Prevents applying schema work to an unready database. |
| Baseline consistency | Base schema replay before migrations | Makes the run usable for both fresh and existing databases. |
| Ordered schema evolution | Migration directories run after the baseline | Preserves the intended progression of schema changes. |
| Completion validation | Final table verification at the end | Confirms the schema state before the helper is treated as successful. |
Troubleshooting
Missing migration directories
If the script references a migration directory that is not present, the schema run cannot complete the directory scan or execution phase.
| Symptom | What to check | Fix |
|---|---|---|
| A referenced migration directory is absent | Directory path under platform/database/ |
Restore the directory or correct the path in the schema workflow inputs. |
| No migration files are executed | Directory contents | Confirm the expected SQL files are present and readable. |
Unhealthy containers
If the database container never becomes healthy, the script remains blocked at the wait stage.
| Symptom | What to check | Fix |
|---|---|---|
| Health wait does not complete | Container health status | Inspect the container healthcheck and startup logs. |
| Schema steps never begin | Compose service readiness | Restart the stack after fixing the container startup issue. |
Database name inconsistencies in SQL assets
The SQL assets surface mismatches when the database name used by the schema files does not match the runtime database name.
| Symptom | What to check | Fix |
|---|---|---|
| SQL targets the wrong database | CREATE DATABASE, USE, or schema-qualified statements in SQL assets |
Align the database name across the SQL assets and the running environment. |
| Objects appear in an unexpected schema | Runtime database configuration | Make the compose/database configuration and SQL asset references consistent. |
| Final table verification fails after apparent success | Database name used during execution | Re-run after correcting the database name mismatch. |
Final table verification fails
[!WARNING] The SQL assets in this repository surface database-name inconsistencies. Make sure the database name used by the SQL files matches the database name exposed by the running environment before applying the schema.
If the last verification step does not find the expected tables, the run completed without producing the expected schema state.
| Symptom | What to check | Fix |
|---|---|---|
| Tables are missing after the run | Earlier SQL execution and migration files | Correct the failing SQL file or restore the missing migration directory. |
| Partial schema state remains | Error output from the apply step | Resolve the earlier failure and rerun the helper from the start. |
Sequence of Execution
sequenceDiagram
participant O as Operator
participant S as apply_schema.sh
participant C as Compose stack
participant M as MySQL container
participant A as SQL assets
O->>S: Start schema application
S->>C: Wait for health
C-->>S: Container is healthy
S->>A: Replay base schema
A->>M: Execute base schema SQL
M-->>S: Base schema applied
S->>A: Run migration directories
A->>M: Execute migration SQL
M-->>S: Migration SQL applied
S->>M: Verify final tables
M-->>S: Tables verified
Dependencies
| Dependency | Role in the workflow |
|---|---|
platform/database/apply_schema.sh |
Orchestrates the schema application run. |
| Docker Compose | Provides the running database container and health status. |
| MySQL container | Receives the schema SQL and holds the resulting tables. |
SQL assets under platform/database/ |
Define the base schema and migration SQL executed by the helper. |
Key Classes Reference
| Class | Responsibility |
|---|---|
apply_schema.sh |
Waits for database health, replays the base schema, runs migration directories, and verifies the final tables. |
SQL assets |
Provide the base schema and migration SQL consumed by the schema application workflow. |