mysql

A scope-aware rental package CRUD: nil versus empty slice for fail-closed, five domain errors, one mapError translating them to HTTP.

TL;DR: Rental package CRUD centralizes authorization in a service layer instead of scattered handler checks, returning five domain errors mapped to precise HTTP codes. A sentinel pair makes it fail-closed: nil means global access, empty slice means deny-all. Tests verify status codes and deny-by-default behavior, so unrecognized departments never slip through.

Three public failures in one night: a filter that never matched, search exploding to 500, and a 403 echoing input.

TL;DR: A broken anchored LIKE pattern made the amenity filter always return empty rows, fixed by switching to substring matching with wildcards escaped. An unsanitized search term crashed MySQL's full-text parser via a single asterisk, fixed by stripping operator characters at the boundary. A 403 response echoed user input back to attackers, fixed by replacing it with a static message while logging the raw value.

Checkpoint v56 was built on an untested theory. Two down-file bugs, errors 1553 and 1364, surfaced the day down-to-0 ran again.

TL;DR: Migrations down to zero failed below checkpoint 56, disproving the untested ENUM theory behind it. Two rotted down files broke: a missing slug column and a dropped index that a foreign key depended on. A roundtrip test now proves the chain reversibility, replacing theory with measurable guarantees.

The 409 guard never fired because CASCADE removed the content first. Migration 000067 flips the order: RESTRICT refuses at the database, then the guard speaks.

TL;DR: A delete endpoint removed a citizen account plus all its content because the foreign keys used ON DELETE CASCADE, letting InnoDB wipe child rows before the app's ErrHasContent guard could check them. Migration 000067 switched those constraints to RESTRICT, MySQL's default, so the database refuses first. Five integration tests verify both up and down migrations.

The down chain breaks mid-way: an old down migration inserts an ENUM value that no longer exists there. Checkpoint 56 is the tested safe window.

TL;DR: The migration roundtrip test stops at version 56 instead of unwinding to zero, because a cross-dependency between migrations 45 and 51 breaks any deeper descent. Version 56 is the deepest proven-safe checkpoint, verified with spot-checks on 8 tables and 12 columns. A separate test pins the dev-only login route to return 404 everywhere except development.

Integration tests prove the review moderation machine: a wrong 9.9/999 seed, Remove filling the audit trail, Restore clearing it, aggregates recomputed both ways.

TL;DR: Integration tests inject absurd aggregates and verify Remove triggers real recalculation through production logic, not test-only updates. The moderation flow atomically flips approved to removed, fills four audit columns, enforces whitelisted reasons, and blocks double removal with ErrNotFound, while restore reverses everything. Table-driven tests covering boundaries, duplication, and pagination turn state transitions into a guaranteed contract.

A LIKE pattern over serialized JSON made an integration assertion flaky: object 1 caught object 19 through a substring collision.

TL;DR: A LIKE pattern meant to find JSON object 1 also matched objects 16 and 19, since substring matching treats them as prefixes. The flake hid because fixture ordering kept the right row first until data shifted. Fix by comparing parsed values or using JSON functions like JSON_SEARCH instead of loose string matching.

Integration tests lock the repository contract at the SQL line: mapping error 1062 to a Go sentinel, soft-delete visibility, and pagination clamps on MySQL.

TL;DR: Binding a time function as a string parameter silently failed in MySQL because parameter markers only accept data values, not SQL expressions, so the fix splits inserts into two branches calling the function inline. Integration tests now lock the repository contract, mapping driver error 1062 to ErrSlugTaken via errors.Is. repository_lifecycle_test.go covers visibility, soft deletes, sorting, and pagination clamps.

Service types carry metadata ENUMs cannot: category bindings, active windows, stable codes.

TL;DR: Lookup tables beat ENUMs for service types since adding values needs a plain INSERT instead of a schema change. The design uses a nullable category_id with ON DELETE SET NULL so deleting a category never destroys rows. Go validates first, the FK backs it up, and INSERT ...