Seed Migration and Status Mapping for Government Facility Data Import

When the Category Doesn't Exist in the Database

Cultural facility data from PPKD (Pusat Pengembangan Kebudayaan Daerah) was ready to import: 8 facilities, 4 existing and 4 planned. One problem — the "Sarana Budaya" category didn't exist in the categories table. Without it, the importer had no target to write data to.

First option: create the category manually via SQL, then run the importer. This fails on reproducibility — anyone setting up a new environment would need to know the exact execution order. The chosen option: a seed migration as part of the official migration chain, version-controlled, idempotent, and safe to re-run without side effects.

Idempotent Seed Migration

Migration 000075 creates the Sarana Budaya category with a single INSERT statement protected by ON DUPLICATE KEY UPDATE. If the category already exists (the migration was run before), the statement only updates columns that are allowed to change — never creating a duplicate.

INSERT INTO categories (name, slug, module, sort_order) VALUES
  ('Sarana Budaya', 'sarana-budaya', 'BUDAYA', 5)
ON DUPLICATE KEY UPDATE
  module = VALUES(module),
  sort_order = VALUES(sort_order);

This pattern ensures the migration is safe to run in any environment , fresh database, staging, or production. No ambiguous IF NOT EXISTS, no manual checks before INSERT. The database determines whether a new row is created or an existing one is updated.

MySQL documents this behavior explicitly: when a row to be inserted would cause a duplicate on a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs rather than an error. The down migration uses DELETE FROM categories WHERE slug = 'sarana-budaya' , symmetric and safe because only one row is affected.

Status-to-Publish Mapping

The eight facilities in the source data carry two statuses: "Eksisting" (already operating) and "Rencana" (still in planning). This distinction translates directly into publication status , existing facilities go straight to published, while planned facilities enter as drafts.

func publishStatus(source string) string {
    switch strings.ToLower(strings.TrimSpace(source)) {
    case "eksisting":
        return "published"
    default:
        return "draft"
    }
}

Planned facilities also carry notes (catatan) from the source explaining why they're still in draft. These notes are stored in entity attributes, not in main columns , the primary data stays clean for public rendering while verification context remains available to admins.

This approach separates data ready for public consumption from data still under verification. Portal visitors only see existing, validated facilities. The management team can still view planned facilities under review through the admin dashboard.

The Import Pattern with Dry-Run

Every data-writing CLI in this project follows the same pattern: a --dry-run flag that predicts what would happen without writing to the database. Dry-run displays entities that would be inserted, skipped (because they already exist), and deleted (during reconciliation). This enables validation before actual execution.

The APP_ENV=development guard adds a protection layer. CLIs may only run when the environment variable explicitly equals development. The config loader's default value of development isn't enough , the guard checks string equality, not value existence. This fail-closed pattern prevents inherently destructive data imports from accidentally running in production.

Importer idempotency is guaranteed by the combination of slug and attributes.no as deduplication keys. Running the importer twice produces a full skip on the second run. No duplicates are created, no errors thrown. Each source row is matched against the normalized combination of category slug and attribute number. If that pair already exists in the database, the row is skipped without modification.

This combination of seed migration for categories and importer for data forms a sequence that can be run from scratch in any environment. The pattern isn't specific to cultural facilities , whenever a project needs a new category with initial data, the same sequence applies: migration to create the category, importer to populate data, and guards to ensure the process only runs in the right environment. Consistency in this pattern reduces cognitive load for anyone joining the team , all importers look and behave the same way.