When the Source Shrinks: Reconcile in the Importer

The revised JSON file arrived with 63 rows. The database, however, still held 65 art-studio entities from the previous MetroPortal CMS import. Nobody opened a SQL console to manually drop the extras.

The initial assumption was that the importer should simply ignore the missing rows. Leaving the database slightly out of sync seemed safer than risking accidental data loss, especially since manually created entries might exist without a source marker.

The reality was that the database was the deviating side. Deletion needed to be part of the importer contract, not a separate database administrator chore. The solution was a reconcile pass. Any entity in the category whose attributes.no (source row number) was absent from the new source JSON would be deleted during the same importer run.

This approach ensures the database aligns perfectly with the source. The rule relies on explicit source markers rather than guessing which rows are obsolete. Rows lacking the no marker entirely are never deleted, so manually created entities stay out of reach.

package main

import (
	"fmt"
	"os"
)

// Reconcile: hapus entitas kategori yang attributes.no absen dari JSON sumber.
func (im *Importer) ReconcileSanggar(catID int64, present map[int]bool, dryRun bool) {
	ids, err := im.sanggarOrphans(catID, present)
	if err != nil {
		fmt.Fprintln(os.Stderr, "reconcile:", err)
		os.Exit(1)
	}
	for _, id := range ids {
		if !dryRun {
			if _, err := im.db.Exec(`DELETE FROM entities WHERE id = ?`, id); err != nil {
				fmt.Fprintln(os.Stderr, "hapus entitas:", err)
				os.Exit(1)
			}
		}
		im.result.EntitiesDeleted++
	}
}

The Mechanics of Safe Deletion

A subtle bug class was prevented by a specific design choice: the idempotency key moved from the entity slug to the source number. Consider a scenario where a duplicate entry, such as source no. 27, is removed from the source JSON. If the importer deletes this duplicate, its natural slug frees up. A slug-based skip check could mistakenly re-insert a new row through that freed slug. Checking the source number first prevents this resurrection entirely.

Media management during this process is equally critical. Media rows attached to a deleted entity follow ON DELETE CASCADE [2]. This database-level guarantee ensures no orphaned media survives the purge. Orphaned rows typically exist only because no foreign key was present to refuse the delete, making rehearsed deletes and strict foreign key constraints the standard practice [4].

Furthermore, ON DELETE CASCADE is significantly faster than executing multi-statement explicit deletes on every tested engine except SQLite, as it eliminates per-statement network overhead [5]. Cascaded foreign key actions handle the cleanup automatically without activating additional triggers, provided the schema is designed correctly [6].

To mitigate operational risk, the dry-run mode was extended. It now predicts skip and delete counts in a read-only manner, allowing developers to compare counts before committing any changes [4].

Verification and Idempotency

The effectiveness of this pattern is measurable. In a controlled verification, the database was seeded with 65 entities. The first reconcile run reported exactly 0 inserted, 63 skipped, and 2 deleted.

The immediate next run reported 0 inserted, 0 skipped, and 0 deleted. This confirms pure idempotency. The orphaned media count remained at 0 throughout the process.

Result counters provide an audit trail that an untracked manual SQL statement can never offer. By embedding this logic directly into the import workflow, the system maintains its own integrity without requiring external cleanup scripts.

Sources

1. PostgreSQL Documentation, Constraints, ON DELETE referential actions
2. AxonBuild, Database Cleanup Checklist: Orphaned Rows, Dry Runs
3. Boris Kolpackov, Explicit SQL DELETE vs ON DELETE CASCADE
4. MySQL Reference Manual, FOREIGN KEY Constraints and Referential Actions