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:

  1. Wait for the target database container to report healthy.
  2. Replay the base schema so the database starts from the canonical baseline.
  3. Execute SQL found in the migration directories.
  4. 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.