bvandeusenandClaude Opus 5.5
1d4ef1a23f
Rewrites the internal/db/queries comments to rule 32 and regenerates dbq, whose doc comments and embedded SQL carry them. The bugs behind the tiered mixes, the md5 order seed, the score bands and the round-robin arms are restated as the constraint each one enforces; counts from one library and stale cross-references are gone. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
120 lines
4.0 KiB
SQL
120 lines
4.0 KiB
SQL
-- name: CreateRequest :one
|
|
INSERT INTO requests (
|
|
user_id, kind,
|
|
artist_mbid, album_mbid, track_mbid,
|
|
artist_name, album_title, track_title
|
|
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
|
|
RETURNING *;
|
|
|
|
-- name: GetRequestByID :one
|
|
SELECT * FROM requests WHERE id = $1;
|
|
|
|
-- name: ListRequestsForUser :many
|
|
SELECT * FROM requests
|
|
WHERE user_id = $1
|
|
ORDER BY requested_at DESC
|
|
LIMIT $2;
|
|
|
|
-- name: ListRequestsByStatus :many
|
|
SELECT * FROM requests
|
|
WHERE status = $1
|
|
ORDER BY requested_at DESC
|
|
LIMIT $2;
|
|
|
|
-- name: ListApprovedRequestsForReconcile :many
|
|
SELECT * FROM requests
|
|
WHERE status = 'approved'
|
|
ORDER BY decided_at ASC
|
|
LIMIT $1;
|
|
|
|
-- name: ApproveRequest :one
|
|
UPDATE requests
|
|
SET status = 'approved',
|
|
decided_at = now(),
|
|
decided_by = $2,
|
|
updated_at = now()
|
|
WHERE id = $1 AND status = 'pending'
|
|
RETURNING *;
|
|
|
|
-- name: RejectRequest :one
|
|
UPDATE requests
|
|
SET status = 'rejected',
|
|
notes = $2,
|
|
decided_at = now(),
|
|
decided_by = $3,
|
|
updated_at = now()
|
|
WHERE id = $1 AND status = 'pending'
|
|
RETURNING *;
|
|
|
|
-- name: CancelRequest :one
|
|
-- $2 serves as both the ownership guard (user_id = $2) and the
|
|
-- decided_by audit field. The generated parameter struct names it
|
|
-- DecidedBy — callers must pass the cancelling user's UUID, and
|
|
-- a wrong UUID will silently no-op (zero rows updated) rather than
|
|
-- erroring at the SQL layer.
|
|
UPDATE requests
|
|
SET status = 'rejected',
|
|
notes = 'cancelled by user',
|
|
decided_at = now(),
|
|
decided_by = $2,
|
|
updated_at = now()
|
|
WHERE id = $1 AND user_id = $2 AND status = 'pending'
|
|
RETURNING *;
|
|
|
|
-- name: CompleteRequest :one
|
|
-- Reconciler transitions an approved request to completed when its
|
|
-- target track/album/artist has appeared in the library. Callers
|
|
-- should set ONLY the matched_*_id corresponding to the request's
|
|
-- kind and pass pgtype.UUID{} (Valid: false) for the others —
|
|
-- per-kind one-of-three is the contract, NULL for the others
|
|
-- preserves auditability if the matched row is later evicted.
|
|
UPDATE requests
|
|
SET status = 'completed',
|
|
matched_track_id = $2,
|
|
matched_album_id = $3,
|
|
matched_artist_id = $4,
|
|
completed_at = now(),
|
|
updated_at = now()
|
|
WHERE id = $1 AND status = 'approved'
|
|
RETURNING *;
|
|
|
|
-- name: HasNonTerminalRequestForMBID :one
|
|
-- Whether something is already on its way: a pending, approved or completed
|
|
-- request at the given level, an open want for the release group, or a
|
|
-- follow of the artist. Sets the `requested` flag on catalogue search.
|
|
-- Terminal-status (rejected, failed) requests do not count. MBIDs are unique
|
|
-- per entity type, so checking one id against every column cannot
|
|
-- false-match.
|
|
SELECT (EXISTS (
|
|
SELECT 1 FROM requests r
|
|
WHERE r.status IN ('pending', 'approved', 'completed')
|
|
AND ((r.kind = 'artist' AND r.artist_mbid = @mbid)
|
|
OR (r.kind = 'album' AND r.album_mbid = @mbid)
|
|
OR (r.kind = 'track' AND r.track_mbid = @mbid))
|
|
) OR EXISTS (
|
|
SELECT 1 FROM wants w WHERE w.release_group_mbid = @mbid AND w.fulfilled_at IS NULL
|
|
) OR EXISTS (
|
|
SELECT 1 FROM follows f WHERE f.artist_mbid = @mbid
|
|
))::bool AS requested;
|
|
|
|
-- name: GetNonTerminalRequestForMBID :one
|
|
-- Returns the oldest non-terminal (pending/approved/completed) request
|
|
-- whose own-kind MBID matches @mbid, or pgx.ErrNoRows when none. Create()
|
|
-- uses this to dedupe: a re-request for something already in flight
|
|
-- returns the existing row instead of inserting a duplicate.
|
|
SELECT * FROM requests
|
|
WHERE status IN ('pending', 'approved', 'completed')
|
|
AND ((kind = 'artist' AND artist_mbid = @mbid)
|
|
OR (kind = 'album' AND album_mbid = @mbid)
|
|
OR (kind = 'track' AND track_mbid = @mbid))
|
|
ORDER BY requested_at ASC
|
|
LIMIT 1;
|
|
|
|
-- name: RepointRequestsAlbumMbid :exec
|
|
-- A want moved from a release id to its group takes the requests
|
|
-- that name it along, so the reconciler finds it.
|
|
UPDATE requests
|
|
SET album_mbid = sqlc.arg(new_mbid),
|
|
updated_at = now()
|
|
WHERE album_mbid = sqlc.arg(old_mbid);
|