Siapa yang mengubah database?
Apa yang berubah?
Kapan berubah?
Di environment mana berubah?
Mengapa berubah?
Siapa yang menyetujui?
Artifact mana yang dijalankan?
Apakah artifact itu sama dengan yang direview?
Apakah migration berhasil?
Apa evidence-nya?
Apa dampaknya ke data dan aplikasi?
Bagaimana recovery plan-nya?

Level 0: verbal claim
Level 1: log line
Level 2: CI job result
Level 3: immutable artifact + checksum
Level 4: database history table + release metadata
Level 5: independently retained evidence bundle

installed_rank
version
description
type
script
checksum
installed_by
installed_on
execution_time
success

SELECT installed_rank,
       version,
       description,
       type,
       script,
       checksum,
       installed_by,
       installed_on,
       execution_time,
       success
FROM flyway_schema_history
ORDER BY installed_rank;

SELECT *
FROM flyway_schema_history
WHERE success = false
ORDER BY installed_rank DESC;

SELECT *
FROM flyway_schema_history
ORDER BY installed_rank DESC
LIMIT 1;

id
author
filename/dateexecuted/orderexecuted/exectype/md5sum/description/comments/tag/liquibase/contexts/labels/deployment_id

SELECT id,
       author,
       filename,
       dateexecuted,
       orderexecuted,
       exectype,
       md5sum,
       description,
       comments,
       tag,
       deployment_id
FROM databasechangelog
ORDER BY orderexecuted;

SELECT id,
       locked,
       lockgranted,
       lockedby
FROM databasechangeloglock;

SELECT deployment_id,
       count(*) AS changeset_count,
       min(dateexecuted) AS first_change,
       max(dateexecuted) AS last_change
FROM databasechangelog
GROUP BY deployment_id
ORDER BY max(dateexecuted) DESC;

{
  "event": "database_migration_started",
  "service": "case-management",
  "environment": "production",
  "database": "case_db",
  "schema": "public",
  "tool": "flyway",
  "toolVersion": "...",
  "commitSha": "8b9f...",
  "buildId": "github-actions-123456",
  "releaseVersion": "2026.06.28.1",
  "changeRequestId": "CHG-10452",
  "operator": "migration-runner",
  "riskClass": "high"
}

{
  "event": "database_migration_completed",
  "service": "case-management",
  "environment": "production",
  "status": "success",
  "durationMs": 43821,
  "migrationsExecuted": 3,
  "lastVersion": "20260628.1400",
  "postChecksPassed": true
}

{
  "event": "database_migration_failed",
  "service": "case-management",
  "environment": "production",
  "status": "failed",
  "failedMigration": "V20260628_1400__add_case_priority.sql",
  "errorClass": "lock_timeout",
  "durationMs": 5000,
  "recoveryMode": "roll_forward_or_retry_after_lock_clear",
  "incidentId": "INC-8821"
}

service
environment
database
schema
tenant
tool
release_version
migration_version
risk_class

span: db.migration.run
attributes:
  db.system=postgresql
  db.name=case_db
  migration.tool=flyway
  migration.release_version=2026.06.28.1
  migration.change_request_id=CHG-10452
  migration.risk_class=high

db.migration.validate
db.migration.migrate
db.migration.post_check
db.migration.backfill_batch
db.migration.evidence_upload

-- No failed Flyway migration
SELECT count(*) = 0 AS ok
FROM flyway_schema_history
WHERE success = false;

-- No null after backfill
SELECT count(*) AS null_priority_count
FROM case_file
WHERE priority IS NULL;

-- FK candidates are valid before validation
SELECT count(*) AS orphan_count
FROM case_file cf
LEFT JOIN customer c ON c.id = cf.customer_id
WHERE cf.customer_id IS NOT NULL
  AND c.id IS NULL;

-- Feature readiness marker
SELECT value
FROM system_setting
WHERE key = 'case_priority_backfill_completed';

migration-evidence/
  metadata.json
  change-request.md
  reviewed-files.txt
  git-diff.patch
  artifact-digest.txt
  flyway-info-before.json
  flyway-info-after.json
  liquibase-status-before.txt
  liquibase-history-after.csv
  generated-sql.sql
  rollback-or-rollforward-plan.md
  pre-check-results.json
  post-check-results.json
  migration-run.log.jsonl
  metrics-summary.json
  approval-records.json
  incident-link.txt

{
  "service": "case-management",
  "environment": "production",
  "database": "case_db",
  "schema": "public",
  "releaseVersion": "2026.06.28.1",
  "commitSha": "8b9f...",
  "artifactDigest": "sha256:...",
  "tool": "flyway",
  "toolVersion": "...",
  "changeRequestId": "CHG-10452",
  "riskClass": "high",
  "approvedBy": ["db-reviewer", "service-owner"],
  "executedBy": "migration-runner",
  "startedAt": "2026-06-28T10:10:00Z",
  "completedAt": "2026-06-28T10:10:44Z",
  "status": "success"
}

# Database Change: Add case priority

## Intent
Add `case_file.priority` to support priority-based triage.

## Change Type
Expand migration: additive nullable column + backfill worker.

## Compatibility
Old app can ignore the new column.
New app can read/write it after feature flag enabled.

## Risk
Medium: backfill touches 18M rows in batches.

## Pre-checks
- column does not exist
- severity values are within known mapping
- no long-running transaction on target table

## Execution
- apply DDL
- deploy app with dual-write
- run backfill
- verify no null priority

## Roll-forward
- fix mapping defects with new migration
- rerun backfill for failed rows

## Rollback
- disable feature flag
- app ignores column
- column drop deferred to contract release

## Verification
- null priority count = 0
- API smoke test passed
- dashboard healthy

PR author cannot be sole approver
production migration uses service account
manual SQL requires emergency change record
artifact digest must match approved build

git commit SHA
repository URL
branch/tag
build ID
container image digest
migration artifact checksum
Flyway/Liquibase tool version
generated SQL checksum
approval timestamp
execution timestamp

We ran the script from someone's laptop.

Artifact sha256:abc was built from commit 8b9f, reviewed in PR #492,
approved in CHG-10452, executed by migration-runner in job #8931,
and target DB history table shows checksum X applied successfully at timestamp Y.

CREATE TABLE ddl_audit_log (
    id bigserial PRIMARY KEY,
    occurred_at timestamptz NOT NULL DEFAULT now(),
    username text NOT NULL,
    command_tag text NOT NULL,
    object_identity text
);

CREATE TABLE migration_job_progress (
    job_name varchar(128) PRIMARY KEY,
    status varchar(32) NOT NULL,
    last_processed_id varchar(128),
    rows_processed bigint NOT NULL DEFAULT 0,
    rows_failed bigint NOT NULL DEFAULT 0,
    started_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz
);

backfill_rows_processed_total
backfill_rows_failed_total
backfill_remaining_rows
backfill_batch_duration_seconds
backfill_throttle_sleep_seconds
backfill_checkpoint_age_seconds

{
  "event": "migration_backfill_batch_completed",
  "job": "case_priority_backfill",
  "batchSize": 1000,
  "rowsProcessed": 1000,
  "lastProcessedId": "case-123456",
  "durationMs": 812,
  "remainingEstimate": 420000
}

SELECT status, rows_processed, rows_failed, completed_at
FROM migration_job_progress
WHERE job_name = 'case_priority_backfill';

public final class AuditedFlywayRunner {

    private final Flyway flyway;
    private final MigrationEvidenceWriter evidence;
    private final MigrationMetrics metrics;

    public void run(MigrationMetadata metadata) {
        evidence.writeMetadata(metadata);
        metrics.started(metadata);

        long start = System.nanoTime();
        try {
            evidence.writePreInfo(flyway.info());
            flyway.validate();
            MigrateResult result = flyway.migrate();
            evidence.writeMigrateResult(result);
            runPostChecks();
            evidence.writePostInfo(flyway.info());
            metrics.completed(metadata, elapsedSeconds(start));
        } catch (Exception ex) {
            evidence.writeFailure(ex);
            metrics.failed(metadata, ex);
            throw ex;
        } finally {
            evidence.close();
        }
    }
}

public final class AuditedLiquibaseRunner {

    public void run(Connection connection, MigrationMetadata metadata) throws Exception {
        evidence.writeMetadata(metadata);
        evidence.writeGeneratedSql(generateUpdateSql(connection));

        Database database = DatabaseFactory.getInstance()
                .findCorrectDatabaseImplementation(new JdbcConnection(connection));

        Liquibase liquibase = new Liquibase(
                metadata.changelogFile(),
                new ClassLoaderResourceAccessor(),
                database);

        try {
            liquibase.validate();
            evidence.writeDatabaseChangeLogBefore(connection);
            liquibase.update(new Contexts(metadata.contexts()), new LabelExpression(metadata.labels()));
            runPostChecks(connection);
            evidence.writeDatabaseChangeLogAfter(connection);
        } catch (Exception ex) {
            evidence.writeFailure(ex);
            throw ex;
        }
    }
}

migration:
  id: DB-20260628-001
  riskClass: high
  destructive: false
  touchesSensitiveData: true
  requiresBackfill: true
  requiresDowntime: false
  rollbackClass: roll-forward-only

V20260628_1400__CHG_10452_add_case_priority_column.sql
V20260628_1430__CHG_10452_backfill_case_priority.sql
R__case_priority_reporting_view.sql

- changeSet:
    id: CHG-10452-20260628-1400-add-case-priority
    author: platform-db
    comments: "Add case priority column for triage release 2026.06.28.1"

MUST include change/ticket reference for production-impacting migration.
SHOULD include concise intent in filename/description.
MUST NOT include secrets, customer names, or sensitive data in filenames.

Auditors and incident responders should be able to retrieve evidence by:
- service name
- database name
- release version
- change request ID
- migration version
- execution date

migration identity
failure timestamp
target environment
database/schema/tenant
error message
partial state
history table status
objects created/changed before failure
operator action
repair/roll-forward plan
customer impact
incident ID
final closure evidence

Fixed manually.

Failure caused by duplicate email values blocking unique index creation.
Migration halted before index creation.
Duplicates identified by query X.
Remediation migration V20260628_1530 merged and applied.
Post-check duplicate count = 0.
Unique index created successfully.
Incident INC-8821 closed with evidence bundle link.

MUST:
- Every production migration has owner, ticket, risk class, and artifact identity.
- Every production migration is traceable to reviewed source control changes.
- Migration runner emits structured logs.
- Tool history/changelog table is captured before/after for high-risk changes.
- Post-checks are executed and stored.
- Failures link to incident/change records.
- Manual hotfixes are reconciled into migration source.

SHOULD:
- Generate evidence bundle automatically.
- Emit migration metrics to observability platform.
- Store generated SQL for review.
- Use immutable build artifacts and image digests.
- Enforce reviewer/author separation for high-risk migrations.
- Run scheduled drift detection.

MAY:
- Require change advisory board for critical database migrations.
- Attach database audit log excerpts.
- Capture before/after schema snapshots.

Could an auditor reconstruct what happened without asking the deployer?
Could an incident responder know exactly what state the database is in?
Could a future engineer understand whether a manual hotfix occurred?