Database Schema Change Management

Standard ID: DATABASE-SCHEMA-CHANGE Version: 1.0 Effective: 2026-04-03 Owner: CDO

1. Scope

Applies to all database schema changes across:

• MySQL — existing services (Pay-In, Pay-Out, Remittance, Cards).
• SurrealDB — new services and greenfield development.

2. Core Principles

1. Forward-only migrations — never edit or delete a migration file once committed.
2. Backward compatibility — every schema change must be compatible with the previous application version. This enables safe rollback.
3. No downtime — migrations must not lock tables or block queries on production traffic.

3. Migration Tooling

Database	Tool	Format
MySQL	Flyway	SQL migration files
SurrealDB	Native SurrealQL migrations	.surql files

4. Migration File Naming

MySQL (Flyway)

V{timestamp}__{description}.sql

Examples:

V20260403120000__add_idempotency_key_column.sql
V20260404090000__create_webhook_delivery_table.sql
V20260405150000__add_index_on_merchant_ref.sql

SurrealDB

{timestamp}__{description}.surql

Same timestamp and description conventions as MySQL.

5. Review and Approval

Change Type	Reviewer	Approver
Single-service schema change	Tech Lead	Standard PR process
Shared database change (cross-product)	Tech Lead + affected product owners	CDO
Index changes	Tech Lead + DBA	Standard PR process
Data migrations	Tech Lead	CDO

All schema change PRs must include: - The migration file. - A rollback procedure (even if manual). - Impact assessment for shared databases.

6. Testing

1. Migration tested on Test environment using an anonymised clone of production data.
2. Verify forward migration — migration applies cleanly.
3. Verify rollback procedure — rollback executes without data loss.
4. Performance benchmark — for index changes, compare query plans before and after.
5. Application compatibility — deploy previous application version against new schema to confirm backward compatibility.

7. Backward-Compatible Changes (Safe)

These may be applied in a single release:

• Adding a new table.
• Adding a nullable column.
• Adding an index (non-blocking, using ALGORITHM=INPLACE on MySQL).
• Adding a new SurrealDB field with a default value.

8. Breaking Changes (3-Step Migration)

Column removal, type changes, renaming, and constraint changes require a 3-step migration across 2+ releases:

Step 1 — Release N: Prepare

• Add the new column/table.
• Application dual-writes to both old and new locations.
• Old column remains the source of truth for reads.

Step 2 — Release N+1: Migrate

• Backfill historical data from old to new column.
• Switch reads to new column.
• Continue dual-writing for safety.

Step 3 — Release N+2: Clean Up

• Stop writing to old column.
• Remove old column via migration.
• Remove dual-write code.

Minimum gap between releases: 1 week (to confirm stability).

9. Index Changes

• Always benchmark query performance before and after on Test with production-like data volumes.
• Use EXPLAIN / EXPLAIN ANALYZE output in the PR description.
• Large table index creation on MySQL must use ALGORITHM=INPLACE, LOCK=NONE.
• Monitor query latency post-deployment for 24 hours.

10. Data Migrations

Large data migrations (backfills, transformations) must:

• Run as background jobs, not within the migration transaction.
• Be idempotent — safe to re-run on failure.
• Be throttled — limit batch size to avoid saturating the database.
• Include progress logging — log batch progress to OpenTelemetry.
• Have a kill switch — ability to pause/stop mid-migration.

11. Shared Database Risk

Simpaisa products (Pay-In, Pay-Out, Remittance, Cards) share MySQL databases in some configurations. Any change to a shared database must:

1. Be reviewed by all affected product teams.
2. Include a cross-product impact assessment in the PR.
3. Be deployed during a low-traffic window (not during peak hours for any market: PK, BD, NP, IQ, EG).
4. Have CDO approval.

12. Audit

All migrations are logged with:

Field	Description
Migration ID	File name / version
Timestamp	When the migration ran
Author	Who wrote the migration (git blame)
Duration	Execution time
Result	Success or failure
Environment	Which environment (Dev/Test/Prod)

Flyway maintains this automatically in flyway_schema_history. SurrealDB migrations must log to an equivalent audit table.

13. Emergency Schema Changes

For P1 incidents requiring immediate schema changes:

1. Apply the change with CDO verbal approval.
2. Create the migration file and PR within 2 hours.
3. Complete full review process within 24 hours post-deployment.
4. Document in the incident post-mortem.