Permanent Delete Without Orphan Media Rows or Files
The database rows disappear with the record, but the files stay on the server. A permanent delete collects its references before any row is dropped.
TL;DR
Database cascades only delete rows, never files, leaving orphaned uploads on disk. The fix collects upload URLs before deletion, then counts references across all URL-bearing columns to decide what is truly unused, removing files best-effort afterward with strict path validation. Failures are logged, not fatal, and tests force the orphan scenario to verify cleanup.
During a QA pass on the media admin module I found finding R4-5: the database rows were gone, but the physical files were still sitting in the server uploads directory.
My first assumption was that the database foreign key cascade would handle the cleanup automatically, or that a cleanup pass could be scheduled later.
The reference trail turns out to disappear the moment the row is deleted. Candidates have to be collected before the delete runs. The decision about whether a URL is still in use belongs in one place, with a maintenance contract next to it. Files are removed best-effort after the database delete succeeds, behind path validation before any filesystem removal call.
Architecture: Collecting References Before the Delete
In this change I implemented mediausage.Unused, which runs a COUNT census across the 7 columns that can hold an upload URL: media.url, announcement_media.url, entities.featured_image_url, announcements.cover_image, menus.image_url, users.avatar_url, and settings.setting_value. It returns true when the reference count is zero.
RemoveLocal only touches URLs under the /uploads/ directory, and only after validation through uploads.ValidDatedRel or uploads.ValidMediaRel. If removal fails, the system logs it with slog.Warn while os.IsNotExist is tolerated without stopping the flow. os.Remove returns a *PathError when something goes wrong, and os.RemoveAll returns nil if the path does not exist [1].
On the repository side, MediaURLsForDelete collects URLs before the main row delete, using a UNION query between announcement_media.url and announcements.cover_image. ReleaseMedia then removes orphan rows in the media table with sqlx.In for a parameterized IN clause, and returns the URLs whose mediausage.Unused count is zero.
The same pattern is mirrored in the entityadmin module (repository.go, service.go) and wired up in api/cmd/server/main.go, with the uploads directory passed into NewService. The whole flow is covered by delete_permanent_media_test.go (+116 lines in announcementadmin, +122 lines in entityadmin) plus service and integration adjustments. The change touches 17 files with a +472/-18 line diff.
A new maintenance contract is also documented in the mediausage package comment: every migration that adds an upload-URL column must add a COUNT call inside Unused. One place for the decision, instead of scattering it across callers.
The DeletePermanent Flow: Collect, Delete, Then Clean Up
In service.go, DeletePermanent calls MediaURLsForDelete first to gather gallery URLs (announcement_media) and cover_image from announcements. Once the announcements row is gone, the cover_image reference cannot be traced anymore. Only then does repo.DeletePermanent run, the public cache gets busted, and cleanupMedia takes over: detach orphan media rows through ReleaseMedia, then delete local files that nothing references anymore.
Failed file removals or leftover rows are only logged with slog.Warn. The main delete already succeeded, and data integrity comes before files (R4-5). Failure here is non-fatal by design.
You can verify it yourself: go test ./internal/announcementadmin/... and go test ./internal/entityadmin/.... The two new test files force the orphan-media scenario. After DeletePermanent, SELECT COUNT(*) FROM media WHERE url = ? must be 0 and the file in the uploads directory must be gone. Those tests cannot catch a migration that forgets to add its COUNT, and that kind of damage silently makes the census lie. The contract therefore lives in the package comment, where a developer reads it exactly when opening the file.
Three Reasons Behind the Decision
The shape of this architecture makes sense for several basic reasons. First, MySQL referential actions are row-level and table-to-table only [2]. A cascade moves rows; it never deletes files on disk [1]. Foreign keys only relate tables, keeping the related data consistent [5].
Second, the census of URL-bearing columns must come from the live schema rather than migration history. The COLUMNS table provides definitive information about the columns in a table [3].
Third, turning a stored URL into a filesystem path is the classic path traversal shape when it is not neutralized properly [4]. Separating reference collection, row deletion, and file removal into that order is what guarantees data integrity during a permanent delete.
Sources: